EOFFICE - GD4

SELECT * FROM QRTZ_TRIGGERS

DECLARE @REQ_ID VARCHAR
SET @REQ_ID=(SELECT REQ_ID FROM PL_REQUEST_DOC WHERE REQ_CODE ='0073/2024/TTr-0690405')
UPDATE PL_REQUEST_DOC SET PROCESS_ID ='HDQT' WHERE REQ_ID =@REQ_ID
UPDATE PL_REQUEST_PROCESS SET STATUS ='P', CHECKER_ID ='toailc',APPROVE_DT =GETDATE, NOTES =N'Thư Ký VP HĐQT đã phê duyệt' WHERE REQ_ID =@REQ_ID AND PROCESS_ID ='TKHDQT'
UPDATE PL_REQUEST_PROCESS SET STATUS ='C' WHERE REQ_ID =@REQ_ID AND PROCESS_ID ='HDQT'
EXEC dbo.PL_REQUEST_PROCESS_App @p_REQ_ID = @REQ_ID, -- varchar(15)
@p_AUTH_STATUS = 'A', -- varchar(1)
@p_CHECKER_ID = 'taila', -- varchar(15)
@p_APPROVE_DT = '21/11/2024', -- datetime
@p_ROLE_LOGIN = 'HDQT', -- varchar(50)
@p_BRANCH_LOGIN = 'DV0001', -- varchar(15)
@p_PROCESS_DESC = N'Phê duyệt' , -- nvarchar(max)
@p_IS_AUTHORITY =0

CREATE PROCEDURE dbo.TR_CONTRACT_App
@p_CONTRACT_ID VARCHAR,
@p_AUTH_STATUS varchar(50) = NULL,
@p_CHECKER_ID varchar(12) = NULL,
@p_APPROVE_DT VARCHAR = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @p_BRANCH_ID_CONTRACT VARCHAR = (
SELECT BRANCH_ID FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID
)
----LUCTV 23062023_SECRETKEY - FIX LỖI NẾU CONTRACT CODE NULL KHÔNG HIỂN THỊ VALIDATE
DECLARE @p_CONTRACT_CODE VARCHAR = (SELECT ISNULL FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID)
IF)
BEGIN
SELECT '-1' as Result,
N'Phê duyệt thất bại. Hợp đồng ' + @p_CONTRACT_CODE + N' đang bị trả về' ErrorDesc
RETURN '-1'
END
IF)
BEGIN
SELECT '-1' as Result,
N'Phê duyệt thất bại. Hợp đồng ' + @p_CONTRACT_CODE + N' đã được phê duyệt trước đó' ErrorDesc
RETURN '-1'
END

DECLARE MENU_PERMISSON NVARCHAR(500) = 'Pages.Administration.TradeTRContractList'
  --start 04072023_secretkey hieuhm
  IF(NOT EXISTS(SELECT TLNANME FROM TL_USER WHERE TLNANME = @p_CHECKER_ID AND (TLSUBBRID = @p_BRANCH_ID_CONTRACT OR TLSUBBRID = 'DV0001'))
    AND NOT EXISTS(SELECT A.RoleDisplayName 
                    FROM SYS_PERMISSIONS_PAGE_FOR_USER A 
                    WHERE A.TLNAME = @p_CHECKER_ID AND A.BRANCH_ID = @p_BRANCH_ID_CONTRACT 
                    AND (DATEDIFF(DAY, CONVERT(DATE, A.EffectiveDate, 103) , CONVERT(DATE, GETDATE(), 103)) >= 0 OR A.EffectiveDate IS NULL OR A.EffectiveDate = '')
                    AND (DATEDIFF(DAY, CONVERT(DATE, A.ExpirationDate, 103) , CONVERT(DATE, GETDATE(), 103)) <= 0 OR A.ExpirationDate IS NULL OR A.ExpirationDate = '')
                    AND A.AUTH_STATUS = 'A' AND A.RECORD_STATUS = '1'
                    AND EXISTS(SELECT DT.PER_PAGE_FOR_USER_DT_ID FROM SYS_PERMISSIONS_PAGE_FOR_USER_DT DT WHERE DT.PER_PAGE_FOR_USER_ID = A.PER_PAGE_FOR_USER_ID AND DT.MENU_PERMISSION = @MENU_PERMISSON))
    )
  BEGIN
      SELECT '-1' as Result, 
              N'Phê duyệt thất bại. Hợp đồng ' + @p_CONTRACT_CODE + N' của Hội sở' ErrorDesc
      RETURN '-1'
  END
  --end 04072023_secretkey hieuhm
          --DECLARE @tmp_tbl TABLE(
          --    GOODS_NAME    nvarchar(MAX)  ,
          --    QUANTITY_USE DECIMAL
          --)
          --INSERT INTO @tmp_tbl
          --SELECT B.GD_NAME AS GOODS_NAME, C.QUANTITY_USE FROM TR_CONTRACT_DT A
          --LEFT JOIN CM_GOODS B ON A.GOODS_ID = B.GD_ID
          --LEFT JOIN (
          --    select GOODS_ID, CONTRACT_ID, SUM(QUANTITY)  AS QUANTITY_USE FROM TR_PO_DETAIL A
          --    LEFT JOIN TR_PO_MASTER B ON A.PO_ID = B.PO_ID
          --    WHERE B.CONTRACT_ID = @p_CONTRACT_ID AND B.AUTH_STATUS = 'A'
          --    GROUP BY GOODS_ID, CONTRACT_ID
          --) C on A.GOODS_ID = C.GOODS_ID
          --WHERE C.QUANTITY_USE > A.QUANTITY AND A.CONTRACT_ID = @p_CONTRACT_ID
          --IF EXISTS(SELECT * FROM @tmp_tbl)
          --BEGIN
          --    DECLARE @message NVARCHAR(MAX)
          --    SELECT @message = COALESCE(@message + '\n ', '') + (N'Hàng hóa ' + GOODS_NAME + N' số lượng đã gọi là ' + CONVERT(NVARCHAR(50),(QUANTITY_USE)))
          --    -- + N' (đã duyệt ' + CONVERT(NVARCHAR(50),QUANTITY_USE) + N', chờ duyệt ' + CONVERT(NVARCHAR(50),QUANTITY) + N')')
          --    FROM @tmp_tbl
          --    SELECT '-1' Result, ''  PO_ID, '' PO_CODE, N'Số lượng trong hợp đồng không được nhỏ hơn số lượng đã mua: \n' + @message ErrorDesc 
          --    RETURN '0'
          --END
          --DECLARE @TOTAL_PO DECIMAL, @TOTAL_CONTRACT DECIMAL, @l_CONTRACT_NAME NVARCHAR(MAX)
          --SELECT @TOTAL_PO = SUM(A.TOTAL_AMT) FROM TR_PO_DETAIL A
          --LEFT JOIN TR_PO_MASTER B ON A.PO_ID = B.PO_ID
          --WHERE B.CONTRACT_ID = @p_CONTRACT_ID AND B.AUTH_STATUS = 'A' 
          --SELECT @TOTAL_CONTRACT = SUM(PRICE * QUANTITY) FROM TR_CONTRACT_DT WHERE CONTRACT_ID = @p_CONTRACT_ID
          --SET @l_CONTRACT_NAME = (SELECT [CONTRACT_NAME] FROM TR_CONTRACT WHERE CONTRACT_ID = @p_CONTRACT_ID)
          --IF @TOTAL_PO > @TOTAL_CONTRACT
          --BEGIN
          --    DECLARE @message1 NVARCHAR(MAX)
          --    SELECT @message1 = COALESCE(@message + '\n ', '') + (N'Hợp đồng ' + @l_CONTRACT_NAME + N' giá trị đã gọi là ' + CONVERT(NVARCHAR(50),(@TOTAL_PO)))
          --    SELECT '-1' Result, ''  PO_ID, '' PO_CODE, N'Giá trị trong hợp đồng không được nhỏ hơn giá trị đã mua: \n' + @message1 ErrorDesc 
          --    RETURN '0'
          --END
  BEGIN TRANSACTION
          --insert master
          UPDATE TR_CONTRACT SET [AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103)
          WHERE  CONTRACT_ID= @p_CONTRACT_ID
          IF @Error <> 0 GOTO ABORT
          PRINT 'UPDATE MASTER SUCCESS'
          --insert asset detail tương đương như NỘI DUNG trong bảng contract detail
      -- NGUYENTD 12.12.2024 HỆ THỐNG GHI NHẬN LẠI LỊCH SỬ GIÁ MỖI LẦN DUYỆT HỢP ĐỒNG
      DECLARE MAKER_ID VARCHAR(50), @PRICE DECIMAL(18,2),@TOTAL_VAT DECIMAL(18,2), @TOTAL_AMT DECIMAL(18,2),@PRICE_VAT DECIMAL(18,2), @VAT DECIMAL(18,2),@PRICE_BANK_ID VARCHAR(15)
      DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
      SELECT B.MAKER_ID,A.PRICE, A.TOTAL_VAT,B.TOTAL_AMT,A.PRICE_VAT,A.VAT, A.HH_DT_ID
      FROM TR_CONTRACT_DT A
      LEFT JOIN TR_CONTRACT B ON A.CONTRACT_ID = B.CONTRACT_ID
      WHERE A.CONTRACT_ID = @p_CONTRACT_ID
      OPEN cur
      FETCH NEXT FROM cur INTO @MAKER_ID,@PRICE,@TOTAL_VAT,@TOTAL_AMT,@PRICE_VAT,@VAT,@PRICE_BANK_ID
      WHILE @FETCH_STATUS = 0
      BEGIN
          DECLARE @l_HISTORY_ID VARCHAR
          EXEC SYS_CodeMasters_Gen 'CM_PRICE_BANK_HIST', @l_HISTORY_ID OUT
          DECLARE  @PRICE_OLD DECIMAL,@TOTAL_VAT_OLD DECIMAL, @TOTAL_AMT_OLD DECIMAL,@PRICE_VAT_OLD DECIMAL, @VAT_OLD DECIMAL
          SELECT TOP 1 @PRICE_VAT_OLD = PRICE_VAT, @PRICE_OLD = PRICE, @TOTAL_VAT_OLD = TOTAL_VAT, @TOTAL_AMT_OLD = TOTAL_AMT,@VAT_OLD = VAT FROM CM_PRICE_BANK_HIST WHERE PRICE_BANK_ID = @PRICE_BANK_ID ORDER BY HISTORY_ID DESC
          IF OR NOT EXISTS) 
          BEGIN
            INSERT INTO CM_PRICE_BANK_HIST (HISTORY_ID, CONTRACT_ID, UPDATE_DT, MAKER_ID, CREATE_DT, CHECKER_ID, APPROVE_DT, PRICE, TOTAL_VAT, TOTAL_AMT, PRICE_VAT, VAT, PRICE_BANK_ID)
            VALUES (@l_HISTORY_ID,@p_CONTRACT_ID,GETDATE,@MAKER_ID,GETDATE,@p_CHECKER_ID,GETDATE,@PRICE,@TOTAL_VAT,@TOTAL_AMT,@PRICE_VAT,@VAT,@PRICE_BANK_ID);
          END

FETCH NEXT FROM cur INTO @MAKER_ID,@PRICE,@TOTAL_VAT,@TOTAL_AMT,@PRICE_VAT,@VAT,@PRICE_BANK_ID
      END
      CLOSE cur
      DEALLOCATE cur

------------------BAODNQ 24/2/2022 INSERT lưu lịch sử xử lý------------------
          INSERT INTO dbo.PL_PROCESS(REQ_ID,PROCESS_ID,CHECKER_ID,APPROVE_DT,PROCESS_DESC,NOTES)
          VALUES(@p_CONTRACT_ID,'APPROVE',@p_CHECKER_ID,GETDATE(),N'Trưởng đơn vị duyệt thông tin hợp đồng thành công' ,N'Trưởng đơn vị duyệt thông tin hợp đồng')

COMMIT TRANSACTION
  SELECT '0' as Result, @P_CONTRACT_ID  CONTRACT_ID, '' ErrorDesc
  RETURN '0'
  ABORT:
  BEGIN        
          ROLLBACK TRANSACTION
          SELECT '-1' as Result, ''  CONTRACT_ID, ERROR_MESSAGE() ErrorDesc
          RETURN '-1'
  End
END
GO