CREATE OR ALTER PROCEDURE dbo.MW_TRANSFER_PRIVATE_App
  @p_TRANFER_PRIVATE_ID VARCHAR(15),
  @p_AUTH_STATUS VARCHAR(1),
  @p_CHECKER_ID VARCHAR(200),
  @p_APPROVE_DT VARCHAR(20)
AS

--Validation is
	IF (EXISTS(SELECT mtp.AUTH_STATUS FROM MW_TRANFER_PRIVATE mtp WHERE mtp.TRANFER_PRIVATE_ID = @p_TRANFER_PRIVATE_ID AND mtp.AUTH_STATUS = 'A' AND mtp.PROCESS_ID = 'DONE'))
	BEGIN
		SELECT '0' as Result, '' ErrorDesc
		RETURN 0
	END

BEGIN TRANSACTION
     
     	DECLARE  @ROLE_LOGIN TABLE(ROLE_USER VARCHAR(20), BRANCH_ID VARCHAR(20), DEP_ID VARCHAR(20));
       DECLARE @p_DEP_ID VARCHAR(15)
      ,@p_BRANCH_ID VARCHAR(15)
      ,@WARE_MASTER_ID VARCHAR(15)
      ,@ROLE_DV VARCHAR(20)
       ,@DEP_ROLE VARCHAR(20),
       @IS_SEND_MAIL VARCHAR(1) = '0'

       SELECT @WARE_MASTER_ID = mtp.WARE_ID FROM MW_TRANFER_PRIVATE mtp WHERE  mtp.TRANFER_PRIVATE_ID = @p_TRANFER_PRIVATE_ID 

       SELECT @ROLE_DV = crt.ROLE_RECEIVE,
               @DEP_ROLE = crt.DEP_RECEIVE
       FROM CM_REQ_TYPE crt 
       WHERE crt.WARE_TYPE = @WARE_MASTER_ID

      
         
     

      INSERT INTO @ROLE_LOGIN
      SELECT tugr.ROLE_ID, tugr.BRANCH_ID, tugr.DEPT_ID FROM dbo.TL_USER_GET_ROLES(@p_CHECKER_ID) tugr
            
               SELECT @p_DEP_ID = DEP_ID,
                       @p_BRANCH_ID = BRANCH_ID
                        FROM MW_REQUEST_PROCESS
                        WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'ADDNEW' 

    IF (EXISTS(SELECT 1 FROM @ROLE_LOGIN 
                            WHERE (BRANCH_ID = 'DV0001' 
                                 AND ISNULL(DEP_ID,'') = ISNULL(@DEP_ROLE ,'')))                             
          AND EXISTS(SELECT 1 
                          FROM MW_TRANFER_PRIVATE mtp 
                          LEFT JOIN CM_WARE cw ON mtp.WARE_ID = cw.WARE_ID
                          WHERE mtp.TRANFER_PRIVATE_ID = @p_TRANFER_PRIVATE_ID AND cw.WARE_CODE IN ('02TE','03CT'))
       )         
      BEGIN
          -- Cập nhật số lượng lô vật liệu
             
             
              -- cộng kho điều chuyển
              
                DECLARE @MW_MAST_BAL_Temp TABLE(MAST_BAL_ID VARCHAR(15),TRANSFER_PRIVATE_DT_ID VARCHAR(15),TRANSFER_QUANTITY DECIMAL(18,2),PROMOTION_TRANFER_ID VARCHAR(15), PRICE_ID VARCHAR(15), BRANCH_ID VARCHAR(15), DEPT_ID VARCHAR(15), QTY_BALANCE DECIMAL(18,2), QTY_REAL DECIMAL(18,2), QTY_TEMP DECIMAL(18,2)
                , QTY_DAMAGED DECIMAL(18,2), QTY_LOSS DECIMAL(18,2), MATERIAL_ID VARCHAR(15), TOTAL_AMT DECIMAL(18,2), PRICE DECIMAL(18,2), PROMOTION_ID VARCHAR(50), WARE_ID VARCHAR(50))
                
         
                INSERT INTO @MW_MAST_BAL_Temp
                SELECT  mmb.MAST_BAL_ID,mtpd.TRANFER_PRIVATE_DT_ID,mtpd.TRANFER_QUANTITY,mtpd.PROMOTION_TRANFER_ID, mmb.PRICE_ID , mmb.BRANCH_ID , mmb.DEPT_ID , mmb.QTY_BALANCE, mmb.QTY_REAL , mmb.QTY_TEMP 
                , mmb.QTY_DAMAGED, mmb.QTY_LOSS , mmb.MATERIAL_ID , mmb.TOTAL_AMT , mmb.PRICE , mmb.PROMOTION_ID , mmb.WARE_ID
                FROM MW_MAST_BAL mmb 
                JOIN MW_TRANFER_PRIVATE_DT mtpd ON mtpd.MAST_BAL_ID = mmb.MAST_BAL_ID
                WHERE mtpd.TRANFER_PRIVATE_ID = @p_TRANFER_PRIVATE_ID AND mmb.WARE_ID = mtpd.DEFAULT_WARE_ID
        
                -- loop insert value
                 
                DECLARE 
                @MAST_BAL_ID VARCHAR(15), 
                @TRANSFER_PRIVATE_DT_ID VARCHAR(15),
                @TRANSFER_QUANTITY DECIMAL(18,2),
                @PROMOTION_TRANFER_ID VARCHAR(15), 
                @PRICE_ID VARCHAR(15), 
                @BRANCH_ID VARCHAR(15), 
                @DEPT_ID VARCHAR(15), 
                @QTY_BALANCE DECIMAL(18,2), 
                @QTY_REAL DECIMAL(18,2), 
                @QTY_TEMP DECIMAL(18,2),
                @QTY_DAMAGED DECIMAL(18,2), 
                @QTY_LOSS DECIMAL(18,2), 
                @MATERIAL_ID VARCHAR(15), 
                @TOTAL_AMT DECIMAL(18,2),
                @PRICE DECIMAL(18,2), 
                @PROMOTION_ID VARCHAR(50), 
                @WARE_ID VARCHAR(50)


                DECLARE PASS_DATA CURSOR FOR
                SELECT MAST_BAL_ID,TRANSFER_PRIVATE_DT_ID,TRANSFER_QUANTITY,PROMOTION_TRANFER_ID, PRICE_ID, BRANCH_ID, DEPT_ID , QTY_BALANCE , QTY_REAL , QTY_TEMP 
                , QTY_DAMAGED , QTY_LOSS, MATERIAL_ID , TOTAL_AMT, PRICE , PROMOTION_ID , WARE_ID 
                FROM @MW_MAST_BAL_Temp;
                
                -- Declare variables
                DECLARE @Done BIT = 0;
               
                  
                -- Declare handlers for cursor
                DECLARE @ErrorMessage NVARCHAR(4000);
                DECLARE @ErrorSeverity INT;
                DECLARE @ErrorState INT;
                DECLARE @l_MAST_BAL_ID VARCHAR(15)
                DECLARE @WARE_TRANFER_NEW_ID VARCHAR(15)
                DECLARE @l_MAST_BAL_STMT_ID VARCHAR(15)
                DECLARE @t_MAST_BAL_STMT_ID VARCHAR(15)
                DECLARE @Mast_Bal_NEW_ID VARCHAR(15)
                BEGIN
                    OPEN PASS_DATA;
                
                    FETCH NEXT FROM PASS_DATA INTO @MAST_BAL_ID, 
                                                    @TRANSFER_PRIVATE_DT_ID,
                                                    @TRANSFER_QUANTITY,
                                                    @PROMOTION_TRANFER_ID,
                                                     @PRICE_ID , 
                                                    @BRANCH_ID , 
                                                    @DEPT_ID , 
                                                    @QTY_BALANCE, 
                                                    @QTY_REAL, 
                                                    @QTY_TEMP,
                                                    @QTY_DAMAGED, 
                                                    @QTY_LOSS , 
                                                    @MATERIAL_ID , 
                                                    @TOTAL_AMT ,
                                                    @PRICE , 
                                                    @PROMOTION_ID, 
                                                    @WARE_ID
                
                    WHILE @@FETCH_STATUS = 0
                    BEGIN
                     -- trừ kho đầu
                     UPDATE mmb 
                            SET mmb.QTY_BALANCE = mmb.QTY_BALANCE - @TRANSFER_QUANTITY,
                                mmb.QTY_REAL = QTY_REAL - @TRANSFER_QUANTITY,
                                mmb.TOTAL_AMT = (mmb.QTY_BALANCE - @TRANSFER_QUANTITY) * mmb.PRICE
                                FROM MW_MAST_BAL mmb 
                                WHERE mmb.MAST_BAL_ID = @MAST_BAL_ID
                        -- set ID kho được chuyển qua
                        SET @WARE_TRANFER_NEW_ID = (SELECT mtpd.WARE_TRANFER_ID 
                                                                FROM 
                                                                MW_TRANFER_PRIVATE_DT mtpd
                                                                 WHERE mtpd.TRANFER_PRIVATE_DT_ID = @TRANSFER_PRIVATE_DT_ID)
                         -- nếu trong lô vật liệu không có kho được chuyển thì add thêm mới
                        IF(NOT EXISTS(SELECT * FROM
                                      MW_MAST_BAL mmb 
                                      WHERE mmb.WARE_ID = @WARE_TRANFER_NEW_ID AND mmb.MATERIAL_ID = @MATERIAL_ID AND mmb.PRICE_ID = @PRICE_ID AND mmb.PROMOTION_ID = @PROMOTION_TRANFER_ID))
                                           
                        BEGIN
               

                        		EXEC SYS_CodeMasters_Gen 'MW_MAST_BAL', @l_MAST_BAL_ID out
                          	IF @l_MAST_BAL_ID='' OR @l_MAST_BAL_ID IS NULL GOTO ABORT

                            INSERT INTO MW_MAST_BAL(MAST_BAL_ID, PRICE_ID, BRANCH_ID, DEPT_ID , QTY_BALANCE , QTY_REAL , QTY_TEMP 
                                        , QTY_DAMAGED , QTY_LOSS, MATERIAL_ID , TOTAL_AMT, PRICE , PROMOTION_ID , WARE_ID )
                              VALUES(@l_MAST_BAL_ID,@PRICE_ID,@BRANCH_ID,@DEPT_ID,@TRANSFER_QUANTITY, @TRANSFER_QUANTITY, @QTY_TEMP
                                    , @QTY_DAMAGED, @QTY_LOSS, @MATERIAL_ID, @TRANSFER_QUANTITY * @PRICE, @PRICE, @PROMOTION_TRANFER_ID, @WARE_TRANFER_NEW_ID)

                           
                        END
                             
                          -- cật nhật số lượng lô vật liệu ở kho được chuyển
                         ELSE 
                         BEGIN
                            UPDATE mmb
                            SET mmb.QTY_BALANCE = mmb.QTY_BALANCE + @TRANSFER_QUANTITY,
                                mmb.QTY_REAL = mmb.QTY_REAL + @TRANSFER_QUANTITY
                            FROM MW_MAST_BAL mmb 
                            WHERE mmb.WARE_ID = @WARE_TRANFER_NEW_ID AND mmb.MATERIAL_ID = @MATERIAL_ID AND mmb.PRICE_ID = @PRICE_ID AND mmb.PROMOTION_ID = @PROMOTION_TRANFER_ID
                         END
                      
                

                          -- thêm vào Mast_bal_stmt
                          EXEC SYS_CodeMasters_Gen 'MW_MAST_BAL_STMT', @l_MAST_BAL_STMT_ID out
                          IF @l_MAST_BAL_STMT_ID='' OR @l_MAST_BAL_STMT_ID IS NULL GOTO ABORT
                          
                          INSERT INTO MW_MAST_BAL_STMT( STMT_ID, REF_ID, MAST_BAL_ID, TRN_DATE, TRN_TIME, TRN_TYPE, CRDR, QTY, PRICE, TRN_DESC, TOTAL_AMT)
                          VALUES(@l_MAST_BAL_STMT_ID,@TRANSFER_PRIVATE_DT_ID, @MAST_BAL_ID,CONVERT(DATETIME, GETDATE(), 103), CONVERT(DATETIME, GETDATE(), 120),'PT','D',@TRANSFER_QUANTITY,@PRICE,'',@TRANSFER_QUANTITY * @PRICE )
                            
                         EXEC SYS_CodeMasters_Gen 'MW_MAST_BAL_STMT', @t_MAST_BAL_STMT_ID out
                          IF @t_MAST_BAL_STMT_ID='' OR @t_MAST_BAL_STMT_ID IS NULL GOTO ABORT
                          

                          SELECT @Mast_Bal_NEW_ID = mmb.MAST_BAL_ID
                          FROM  MW_MAST_BAL mmb
                          WHERE mmb.PRICE_ID = @PRICE_ID AND mmb.WARE_ID = @WARE_TRANFER_NEW_ID AND mmb.MATERIAL_ID = @MATERIAL_ID AND mmb.PROMOTION_ID = @PROMOTION_TRANFER_ID

                          INSERT INTO MW_MAST_BAL_STMT( STMT_ID, REF_ID, MAST_BAL_ID, TRN_DATE, TRN_TIME, TRN_TYPE, CRDR, QTY, PRICE, TRN_DESC, TOTAL_AMT)
                          VALUES(@t_MAST_BAL_STMT_ID,@TRANSFER_PRIVATE_DT_ID,@Mast_Bal_NEW_ID,CONVERT(DATETIME, GETDATE(), 103), CONVERT(DATETIME, GETDATE(), 120),'PT','C',@TRANSFER_QUANTITY,@PRICE,'',@TRANSFER_QUANTITY * @PRICE )

                        FETCH NEXT FROM PASS_DATA INTO @MAST_BAL_ID, 
                                                        @TRANSFER_PRIVATE_DT_ID,
                                                        @TRANSFER_QUANTITY,
                                                        @PROMOTION_TRANFER_ID,
                                                          @PRICE_ID , 
                                                          @BRANCH_ID , 
                                                          @DEPT_ID , 
                                                          @QTY_BALANCE, 
                                                          @QTY_REAL, 
                                                          @QTY_TEMP,
                                                          @QTY_DAMAGED, 
                                                          @QTY_LOSS , 
                                                          @MATERIAL_ID , 
                                                          @TOTAL_AMT ,
                                                          @PRICE , 
                                                          @PROMOTION_ID, 
                                                          @WARE_ID
                    END;
                
                    CLOSE PASS_DATA;
                    DEALLOCATE PASS_DATA;
                END;
                
             
              

              -- Cập nhật trạng thái 
              UPDATE MW_TRANFER_PRIVATE 
              SET AUTH_STATUS = @p_AUTH_STATUS,
                  APPROVE_DT = GETDATE(), 
                  CHECKER_ID = @p_CHECKER_ID,
                  PROCESS_ID = 'DONE' 
              WHERE TRANFER_PRIVATE_ID = @p_TRANFER_PRIVATE_ID	
              
              IF @@Error <> 0 GOTO ABORT
              
             -- Cập nhật lịch sử xử lý
              
             -- thêm xử lý mới
            INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,PROCESS_TYPE,DEP_ID, CHECKER_ID,RECEPTION_DT,NOTES)
            VALUES(@p_TRANFER_PRIVATE_ID,'TDVDMDD',N'ĐƠN VỊ ĐẦU MỐI DUYỆT','','P',@p_BRANCH_ID,'DVDMTN','Approve',@p_DEP_ID, @p_CHECKER_ID,GETDATE(), N'Đơn vị đầu mối đã duyệt')
              -- Câp nhật trạng thái yêu cầu
            UPDATE MW_REQUEST_PROCESS 
            SET STATUS = 'P'
            WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'DVDMTN'

            IF(EXISTS (SELECT STATUS FROM MW_REQUEST_PROCESS WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'SENDAPP' AND STATUS = 'C'))
            BEGIN
                 UPDATE MW_REQUEST_PROCESS 
                SET STATUS = 'P'
                WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'SENDAPP'
            END

            IF(NOT EXISTS(SELECT * FROM MW_REQUEST_PROCESS mrp WHERE mrp.STATUS = 'C' AND mrp.REQ_ID = @p_TRANFER_PRIVATE_ID))
            BEGIN
              INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,PROCESS_TYPE,DEP_ID, CHECKER_ID,RECEPTION_DT,NOTES)
              VALUES(@p_TRANFER_PRIVATE_ID,'DONE',N'HOÀN THÀNH PHIẾU','','P',@p_BRANCH_ID,'TDVDMDD','Approve',@p_DEP_ID, @p_CHECKER_ID,DATEADD(SECOND,1,GETDATE()), N'Hoàn thành phiếu')
            END
      END
    ELSE
    BEGIN  
          	IF(EXISTS(SELECT * FROM MW_REQUEST_PROCESS mrp WHERE mrp.PROCESS_ID = 'SENDAPP' AND mrp.STATUS = 'C' AND mrp.REQ_ID = @p_TRANFER_PRIVATE_ID))
          BEGIN                 
                IF(EXISTS(SELECT * FROM MW_REQUEST_PROCESS mrp WHERE mrp.CHECKER_ID = @p_CHECKER_ID AND mrp.REQ_ID = @p_TRANFER_PRIVATE_ID AND mrp.STATUS <> 'F'))
                BEGIN
                    GOTO ABORT3
                END 


              UPDATE MW_TRANFER_PRIVATE 
              SET 
                  AUTH_STATUS = @p_AUTH_STATUS,
                  CHECKER_ID = @p_CHECKER_ID,
                  PROCESS_ID = 'DVDMTN' 
              WHERE TRANFER_PRIVATE_ID = @p_TRANFER_PRIVATE_ID	IF @@Error <> 0 GOTO ABORT
             -- Cập nhật lịch sử xử lý
       
             -- thêm xử lý mới
            INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,PROCESS_TYPE,DEP_ID, CHECKER_ID,RECEPTION_DT,NOTES)
            VALUES(@p_TRANFER_PRIVATE_ID,'DVDMTN',  N'TRƯỞNG ĐƠN VỊ DUYỆT','DVDM','C',@p_BRANCH_ID,'SENDAPP','APPROVE',@p_DEP_ID, @p_CHECKER_ID,GETDATE(), N'Trưởng đơn vị duyệt phiếu')
              -- Câp nhật trạng thái yêu cầu
            UPDATE MW_REQUEST_PROCESS 
            SET STATUS = 'P'
            WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'SENDAPP'AND STATUS = 'C'
            -- GỬI MAIL CHECK
            SET @IS_SEND_MAIL = '1'
          END
          ELSE 
             BEGIN
                IF EXISTS(SELECT * FROM MW_REQUEST_PROCESS mrp WHERE mrp.PROCESS_ID = 'DVDMTN' AND  mrp.STATUS = 'C' AND mrp.REQ_ID = @p_TRANFER_PRIVATE_ID)
                BEGIN
                	   

              -- Cập nhật trạng thái 
              UPDATE MW_TRANFER_PRIVATE 
              SET AUTH_STATUS = @p_AUTH_STATUS,
                  APPROVE_DT = GETDATE(), 
                  CHECKER_ID = @p_CHECKER_ID,
                  PROCESS_ID = 'DONE' 
              WHERE TRANFER_PRIVATE_ID = @p_TRANFER_PRIVATE_ID	
              
              IF @@Error <> 0 GOTO ABORT
              
             -- Cập nhật lịch sử xử lý
              
             -- thêm xử lý mới
            INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,PROCESS_TYPE,DEP_ID, CHECKER_ID,RECEPTION_DT,NOTES)
            VALUES(@p_TRANFER_PRIVATE_ID,'TDVDMDD',N'ĐƠN VỊ ĐẦU MỐI DUYỆT','','P',@p_BRANCH_ID,'DVDMTN','Approve',@p_DEP_ID, @p_CHECKER_ID,GETDATE(), N'Đơn vị đầu mối đã duyệt')
              -- Câp nhật trạng thái yêu cầu
            UPDATE MW_REQUEST_PROCESS 
            SET STATUS = 'P'
            WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'DVDMTN'

            IF(EXISTS (SELECT STATUS FROM MW_REQUEST_PROCESS WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'SENDAPP' AND STATUS = 'C'))
            BEGIN
                 UPDATE MW_REQUEST_PROCESS 
                SET STATUS = 'P'
                WHERE REQ_ID = @p_TRANFER_PRIVATE_ID AND PROCESS_ID = 'SENDAPP'
            END

            IF(NOT EXISTS(SELECT * FROM MW_REQUEST_PROCESS mrp WHERE mrp.STATUS = 'C' AND mrp.REQ_ID = @p_TRANFER_PRIVATE_ID))
            BEGIN
              INSERT INTO MW_REQUEST_PROCESS(REQ_ID,[PROCESS_ID],PROCESS_NAME,ROLE_USER,[STATUS],BRANCH_ID,PARENT_PROCESS_ID,PROCESS_TYPE,DEP_ID, CHECKER_ID,RECEPTION_DT,NOTES)
              VALUES(@p_TRANFER_PRIVATE_ID,'DONE',N'HOÀN THÀNH PHIẾU','','P',@p_BRANCH_ID,'TDVDMDD','Approve',@p_DEP_ID, @p_CHECKER_ID,DATEADD(SECOND,1,GETDATE()), N'Hoàn thành phiếu')
            END
                END
             END
    END

COMMIT TRANSACTION
SELECT '0' as Result, '' ErrorDesc, @IS_SEND_MAIL SEND_MAIL
RETURN '0'
ABORT:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, '' ErrorDesc
		RETURN '-1'
End

ABORT2:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, N'Không thể duyệt, phiếu chưa đến bước xử lý này' ErrorDesc
		RETURN '-1'
End

ABORT3:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, N'Không thể duyệt phiếu này' ErrorDesc
		RETURN '-1'
END
ABORT4:
BEGIN
		ROLLBACK TRANSACTION
		SELECT '-1' as Result, N'Có lỗi xảy ra vui lòng thử lại' ErrorDesc
		RETURN '-1'
End