AMS - VB

DECLARE

@p_AUTH_STATUS        varchar(50)  = 'A',

@p_CHECKER_ID        varchar(120)  = 'admin',

@p_NOTES            nvarchar(max)  = '',

@p_APPROVE_DT        VARCHAR(20) = '28/07/2023'

BEGIN

DECLARE @var1 VARCHAR(20)

DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR

        SELECT BCR.BUDC_RENT_ID

        FROM dbo.BUD_CONTRACT_RENT BCR

OPEN cur

BEGIN TRANSACTION

FETCH NEXT FROM cur INTO @var1

WHILE @@FETCH_STATUS = 0 

BEGIN

    DECLARE @p_BUDC_RENT_CODE VARCHAR(100) = (SELECT BUDC_RENT_CODE FROM BUD_CONTRACT_RENT WHERE BUDC_RENT_ID = @var1)

                --insert master

                UPDATE BUD_CONTRACT_RENT SET [AUTH_STATUS] = @p_AUTH_STATUS,[CHECKER_ID] = @p_CHECKER_ID,[APPROVE_DT] = CONVERT(DATETIME, @p_APPROVE_DT, 103)

              ,IS_SEND_APPR = NULL, SEND_APPR_DT = NULL

                WHERE  BUDC_RENT_ID= @var1

        DECLARE @l_BUDC_MOVE_ID VARCHAR(20),@ToDay DATE = CONVERT(DATE, @p_APPROVE_DT, 103), @ADJUST_CONDITION VARCHAR(10), @PRICE_BY_PERIOD_ADJUST DECIMAL = 0

        , @TOTAL_AMT DECIMAL = 0, @ADJACENT_AMT DECIMAL =0, @ADJUST_PRICE_RATE DECIMAL =0 , @ADJACENT_ID VARCHAR(20), @ADJACENT_YEAR_AMT DECIMAL, @ADJACENT_YEAR_ID VARCHAR(20)

        DECLARE @TYPE_PROCESS VARCHAR(10) = '', @START DATE, @END DATE, @PERIOD_ADJUST DECIMAL, @END_HD DATE, @RATE_UNIT VARCHAR(15), @IS_AUTO VARCHAR(1), @TANGGIAM VARCHAR(15)

        SELECT @PERIOD_ADJUST = CAST(BCR.PERIOD_ADJUST AS DECIMAL), @END_HD = BCR.END_DT, @ADJUST_CONDITION = BCR.ADJUST_CONDITION,

                @TOTAL_AMT = BCR.TOTAL_AMT, @ADJUST_PRICE_RATE = BCR.ADJUST_PRICE_RATE, @RATE_UNIT = BCR.RATE_UNIT, @IS_AUTO = BCR.IS_AUTO_ADJUST,

                @TANGGIAM = BCR.ADJUST_PRICE_RANGE

        FROM BUD_CONTRACT_RENT BCR WHERE BCR.BUDC_RENT_ID = @var1

            DECLARE @LASTMOVE DATETIME, @NEXT_DT DATE

            SELECT @LASTMOVE = DATEADD(MONTH, KEEP_PRICE_TIME, START_PAYMENT_DT),

            @NEXT_DT = NEXT_DT

            FROM BUD_CONTRACT_RENT WHERE BUDC_RENT_ID= @var1

        ----------- THÊM vào lịch sử biến động ----------------

        IF(NOT EXISTS(SELECT * FROM BUD_CONTRACT_RENT_MOVEMENT BCR WHERE BCR.BUDC_RENT_ID = @var1))

        BEGIN

            -- Nếu có thời gian sửa chữa miễn fí thì thêm biến động 0 giá trị

            IF(EXISTS(SELECT 1 FROM BUD_CONTRACT_RENT BCR WHERE BCR.BUDC_RENT_ID = @var1 AND BCR.FREE_FIX_TIME > 0))

            BEGIN

                EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_RENT_MOVEMENT', @l_BUDC_MOVE_ID OUT

                SELECT @START = BCR.START_DT, @END = DATEADD(DAY, -1, START_PAYMENT_DT) FROM BUD_CONTRACT_RENT BCR WHERE BCR.BUDC_RENT_ID = @var1

                IF(CONVERT(DATE, @p_APPROVE_DT, 103) >= CONVERT(DATE, @START, 103) AND CONVERT(DATE, @p_APPROVE_DT, 103) <= CONVERT(DATE, @END, 103)) 

                    SET @TYPE_PROCESS = 'C'

                ELSE

                IF(CONVERT(DATE, @p_APPROVE_DT, 103) < CONVERT(DATE, @START, 103)) SET @TYPE_PROCESS = 'U'

                ELSE

                IF(CONVERT(DATE, @p_APPROVE_DT, 103) > CONVERT(DATE, @END, 103)) SET @TYPE_PROCESS = 'P'

                INSERT INTO BUD_CONTRACT_RENT_MOVEMENT (BUDC_MOVE_ID, BUDC_RENT_ID, TYPE_PROCESS, PRICE_CONTRACT, 

                                                        START_DT, END_DT, NEXT_DT, 

                                                        NOTES, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, IS_USE)

                SELECT @l_BUDC_MOVE_ID, @var1, @TYPE_PROCESS

                                                        ,0

                                                        ,DELIVERY_DT

                                                        ,DATEADD(DAY, -1, START_PAYMENT_DT)

                                                        ,NEXT_DT_NEWUP

                                                        ,@p_NOTES

                                                        ,'1',MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, '0'

                FROM BUD_CONTRACT_RENT        WHERE  BUDC_RENT_ID= @var1

            END

            EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_RENT_MOVEMENT', @l_BUDC_MOVE_ID OUT

                SELECT @START = BCR.START_PAYMENT_DT, 

                @END = IIF(DATEADD(MONTH, KEEP_PRICE_TIME, START_PAYMENT_DT) > @END_HD, DATEADD(DAY, 1, @END_HD), DATEADD(MONTH, KEEP_PRICE_TIME, START_PAYMENT_DT))

                FROM BUD_CONTRACT_RENT BCR WHERE BCR.BUDC_RENT_ID = @var1

                IF(CONVERT(DATE, @p_APPROVE_DT, 103) >= CONVERT(DATE, @START, 103) AND CONVERT(DATE, @p_APPROVE_DT, 103) <= CONVERT(DATE, @END, 103)) 

                    SET @TYPE_PROCESS = 'C'

                ELSE

                IF(CONVERT(DATE, @p_APPROVE_DT, 103) < CONVERT(DATE, @START, 103)) SET @TYPE_PROCESS = 'U'

                ELSE

                IF(CONVERT(DATE, @p_APPROVE_DT, 103) > CONVERT(DATE, @END, 103)) SET @TYPE_PROCESS = 'P'

            INSERT INTO BUD_CONTRACT_RENT_MOVEMENT (BUDC_MOVE_ID, BUDC_RENT_ID, TYPE_PROCESS, PRICE_CONTRACT, 

                                                    START_DT, END_DT, NEXT_DT, 

                                                    NOTES, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, IS_USE)

                SELECT @l_BUDC_MOVE_ID, @var1, @TYPE_PROCESS

                                                        ,TOTAL_AMT

                                                        ,START_PAYMENT_DT

                                                        ,DATEADD(DAY, -1, @END)

                                                        ,NEXT_DT_NEWUP

                                                        ,@p_NOTES

                                                        ,'1',MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, '0'

                FROM BUD_CONTRACT_RENT        WHERE  BUDC_RENT_ID= @var1

            SELECT @LASTMOVE = DATEADD(MONTH, KEEP_PRICE_TIME, START_PAYMENT_DT)

            FROM BUD_CONTRACT_RENT WHERE BUDC_RENT_ID= @var1

            IF(@IS_AUTO = 'N')

            BEGIN

                EXEC SYS_CodeMasters_Gen 'BUD_CONTRACT_RENT_MOVEMENT', @l_BUDC_MOVE_ID OUT

                    INSERT INTO BUD_CONTRACT_RENT_MOVEMENT (BUDC_MOVE_ID, BUDC_RENT_ID, TYPE_PROCESS, PRICE_CONTRACT, 

                                                        START_DT, END_DT, NEXT_DT, 

                                                        NOTES, RECORD_STATUS, MAKER_ID, CREATE_DT, AUTH_STATUS, CHECKER_ID, APPROVE_DT, IS_USE)

                SELECT @l_BUDC_MOVE_ID, @var1, 'C'

                                                        ,TOTAL_AMT

                                                        ,@END

                                                        ,@END_HD

                                                        ,NULL

                                                        ,N'Hệ thống tự động'

                                                        ,'1','', CONVERT(DATETIME, @p_APPROVE_DT, 103), AUTH_STATUS, '', CONVERT(DATETIME, @p_APPROVE_DT, 103), '1'

                FROM BUD_CONTRACT_RENT        WHERE  BUDC_RENT_ID= @var1

            END

                END

        FETCH NEXT FROM cur INTO @var1

END

CLOSE cur

DEALLOCATE cur

COMMIT TRANSACTION

SELECT '0' as Result, @var1  BUDC_RENT_ID, '' ErrorDesc

END