--USE [MinovaES_Rohto_Prod]
--GO
--/****** Object:  StoredProcedure [dbo].[WFLeaveBlockDetail]    Script Date: 26/07/2022 15:49:44 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
----USE [MinovaHR_ESS_Indomobil_Production]
----GO
----/****** Object:  StoredProcedure [dbo].[Wf_leave_block_detail]    Script Date: 02/27/2017 19:01:31 ******/
----SET ANSI_NULLS ON
----GO
----SET QUOTED_IDENTIFIER ON
----GO


--ALTER PROCEDURE [dbo].[WFLeaveBlockDetail]
--    (
--      @empId AS VARCHAR(8) ,
--      @stLeave AS VARCHAR(8) ,
--      @endLeave AS VARCHAR(8) ,
--      @absenceType AS VARCHAR(8) ,
--      @regId AS INT ,
--      @langid AS VARCHAR(8) ,
--      @leaveBlockList AS VARCHAR(MAX)
--    )
--AS 

DECLARE @empId AS VARCHAR(8) = '00000058'
DECLARE @stLeave AS VARCHAR(8) = '20220720'
DECLARE @endLeave AS VARCHAR(8) = '20220720'
DECLARE @absenceType AS VARCHAR(8) = '1000'
DECLARE @instance AS VARCHAR(5) = '00000'
DECLARE @regId AS VARCHAR(10) = '0'
DECLARE @langid AS VARCHAR(10) = 'EN'


----00006322	20140226
----19940006	20140301
----20040016	20140101
----20050010	20140301



    DECLARE @quota_taken AS INT
    DECLARE @quota_taken_ori AS INT
    DECLARE @quota_type AS VARCHAR(8)
    DECLARE @sdt VARCHAR(8)
    DECLARE @edt VARCHAR(8)
    DECLARE @sded VARCHAR(8)
    DECLARE @eded VARCHAR(8)
    DECLARE @seql VARCHAR(8)
    DECLARE @oquot INT
    DECLARE @rquot INT
    DECLARE @rquot_total INT
    DECLARE @lvlcont INT
    DECLARE @qtaken INT
    DECLARE @ws_type AS VARCHAR(20) = ''
 /*declare all get data for frontend*/
    DECLARE @main_leave_quota AS VARCHAR(20) = ''
    DECLARE @main_remaining_quota AS VARCHAR(20) = ''
    DECLARE @main_remaining_quota_prorate AS VARCHAR(20) = ''
    DECLARE @main_quota_type AS VARCHAR(20) = ''
    DECLARE @main_quota_start_date AS VARCHAR(20) = ''
    DECLARE @main_quota_end_date AS VARCHAR(20) = ''
    DECLARE @main_quota_seq AS VARCHAR(20) = ''
    DECLARE @main_quota_sded AS VARCHAR(20) = ''
    DECLARE @main_quota_eded AS VARCHAR(20) = ''
    DECLARE @main_quota_lb_cont AS VARCHAR(20) = ''
    DECLARE @main_total_remaining_quota INT 
    DECLARE @main_total_remaining_quota_prorate INT = 0
    DECLARE @main_quota_taken INT
    DECLARE @main_leave_quota_2 AS VARCHAR(20) = ''
    DECLARE @main_remaining_quota_2 AS VARCHAR(20) = ''
    DECLARE @main_remaining_quota_prorate_2 AS VARCHAR(20) = ''
    DECLARE @main_quota_type_2 AS VARCHAR(20) = ''
    DECLARE @main_quota_start_date_2 AS VARCHAR(20) = ''
    DECLARE @main_quota_end_date_2 AS VARCHAR(20) = ''
    DECLARE @main_quota_seq_2 AS VARCHAR(20) = ''
    DECLARE @main_quota_sded_2 AS VARCHAR(20) = ''
    DECLARE @main_quota_eded_2 AS VARCHAR(20) = ''
    DECLARE @main_quota_lb_cont_2 AS VARCHAR(20) = ''
    DECLARE @main_total_remaining_quota_2 AS INT
    DECLARE @main_total_remaining_quota_prorate_2 AS INT
    DECLARE @main_quota_taken_2 INT
    DECLARE @Warning_Type AS VARCHAR(1) = 0
    DECLARE @Warning_Msg AS VARCHAR(MAX) = ''


    SELECT  @ws_type = md25.WorkScheduleType
    FROM    PHRPA0025 md25
    WHERE   md25.EmployeeID = @empId
            AND md25.StartDate <= @stLeave
            AND md25.EndDate >= @stLeave
    IF ( @ws_type != '' ) 
        BEGIN
            SET @main_total_remaining_quota = 0
            SET @main_quota_taken = 0
            SET @main_total_remaining_quota_2 = 0
            SET @main_quota_taken_2 = 0
            SELECT  @quota_taken = COUNT(EmployeeID)
            FROM    PHRPA0025 md25 ,
                    dbo.PHRTMWSCAL c
            WHERE   md25.EmployeeID = @empId
                    AND md25.StartDate <= @stLeave
                    AND md25.EndDate >= @stLeave
                    AND c.WorkScheduleType = md25.WorkScheduleType
                    AND c.DateSpecified >= @stLeave
                    AND c.DateSpecified <= @endLeave
                    AND 'WORK' = ( SELECT TOP ( 1 )
                                            Flag
                                   FROM     dbo.PHRTMDAYTYP t
                                   WHERE    t.DayType = c.DayType
                                 )         				
            SET @quota_taken_ori = @quota_taken
            DECLARE @aq AS TABLE
                (
                  id INT IDENTITY(1, 1) ,
                  sdate VARCHAR(8) ,
                  end_date VARCHAR(8) ,
                  start_deduction VARCHAR(8) ,
                  end_deduction VARCHAR(8) ,
                  seq VARCHAR(8) ,
                  original_quota INT ,
                  remaining_quota INT ,
                  lv_block_continue INT
                )
            SELECT  @quota_type = qt.AbsenceQuotaType
            FROM    PHRTMABSTYP qt
            WHERE   qt.AbsenceType = @absenceType
            DECLARE @list_regID AS TABLE ( regid INT )
            DECLARE @rbizData AS TABLE
                (
                  regid INT ,
                  fid VARCHAR(100) ,
                  val VARCHAR(100)
                )
            INSERT  INTO @list_regID
                    SELECT  dt.RegID
                    FROM    dbo.PCMWFRUNMAIN AS main WITH ( NOLOCK ) ,
                            dbo.PCMWFRUNBIZDATA dt WITH ( NOLOCK )
                    WHERE   main.RegID = dt.RegID
                            AND dt.RegID != @regId
                            AND EmpIDAs = @empId
                            AND ( main.BizflowID LIKE '%MD_Leave%' )
                            AND main.WorkflowStatus <> 'F'
                            AND dt.FieldID = 'AbsenceQuotaType'
                            AND dt.Value = @quota_type
                            AND dt.Sequence = ( SELECT  MAX(dt2.Sequence)
                                                FROM    dbo.PCMWFRUNBIZDATA dt2
                                                WHERE   dt2.RegID = dt.RegID
                                                        AND dt2.FieldID = dt.FieldID
                                              )
            INSERT  INTO @rbizData
                    SELECT  dt.RegID ,
                            dt.FieldID ,
                            dt.Value
                    FROM    @list_regID AS lr ,
                            dbo.PCMWFRUNBIZDATA dt WITH ( NOLOCK )
                    WHERE   lr.regid = dt.RegID
                            AND dt.Sequence = ( SELECT  MAX(dt2.Sequence)
                                                FROM    dbo.PCMWFRUNBIZDATA dt2
                                                WHERE   dt2.RegID = dt.RegID
                                                        AND dt2.FieldID = dt.FieldID
                                              )
            INSERT  INTO @aq
                    SELECT  StartDate ,
                            EndDate ,
                            StartDeduction ,
                            EndDeduction ,
                            Sequence ,
                            OriginalQuota ,
                            RemainQuota ,
                            0 AS lvblk
                    FROM    dbo.PHRPA0017
                    WHERE   EmployeeID = @empId
                            AND StartDeduction <= @stLeave
                            AND EndDeduction >= @stLeave
                            AND AbsenceQuotaType = @quota_type
            DECLARE @aq_count AS INT
            DECLARE @intFlag AS INT = 1
            SELECT  @aq_count = COUNT(start_deduction)
            FROM    @aq
            DECLARE @lv_taken AS INT

            WHILE ( @intFlag <= @aq_count ) 
                BEGIN
                    SET @lv_taken = 0
                    SELECT  @sdt = sdate ,
                            @edt = end_date ,
                            @sded = start_deduction ,
                            @eded = end_deduction ,
                            @seql = seq ,
                            @oquot = original_quota ,
                            @rquot = remaining_quota
                    FROM    @aq
                    WHERE   id = @intFlag
                    DECLARE @valwf INT
                    DECLARE @list_1 AS TABLE ( regid INT )
                    DECLARE @list_2 AS TABLE ( regid INT )
                    SET @lvlcont = 0; 
                    DECLARE @cuti_taken_meded AS INT = 0
                    IF ( @eded < @endLeave
                         AND @intFlag = 1
                         AND @aq_count > 1
                       ) 
                        BEGIN
                            SELECT  @cuti_taken_meded = COUNT(EmployeeID)
                            FROM    dbo.PHRPA0025 md25 ,
                                    dbo.PHRTMWSCAL c
                            WHERE   md25.EmployeeID = @empId
                                    AND md25.StartDate <= @stLeave
                                    AND md25.EndDate >= @stLeave
                                    AND c.WorkScheduleType = md25.WorkScheduleType
                                    AND c.DateSpecified >= @stLeave
                                    AND c.DateSpecified <= @eded
                                    AND 'WORK' = ( SELECT TOP ( 1 )
                                                            Flag
                                                   FROM     dbo.PHRTMDAYTYP t
                                                   WHERE    t.DayType = c.DayType
                                                 )                        
                            SET @quota_taken_ori = @quota_taken
                        END
        
                    DECLARE @value AS TABLE ( val INT )
                    INSERT  INTO @list_1
                            SELECT  rb.regid
                            FROM    @rbizData rb
                            WHERE   rb.fid = 'QuotaStartDate'
                                    AND rb.val = @sdt
                    INSERT  INTO @value
                            SELECT  rb.val
                            FROM    @rbizData rb ,
                                    @list_1 lr
                            WHERE   rb.regid = lr.regid
                                    AND rb.fid = 'QuotaTaken'
                    INSERT  INTO @list_2
                            SELECT  rb.regid
                            FROM    @rbizData rb
                            WHERE   rb.fid = 'QuotaStartDate2'
                                    AND rb.val = @sdt
                    INSERT  INTO @value
                            SELECT  rb.val
                            FROM    @rbizData rb ,
                                    @list_2 lr
                            WHERE   rb.regid = lr.regid
                                    AND rb.fid = 'QuotaTaken2'
                    IF ( ( SELECT   *
                           FROM     @value
                           WHERE    val >= 5
                         ) IS NOT NULL ) 
                        BEGIN
                            SET @lvlcont = 1
                        END
                    DECLARE @num_blk AS INT
                    SELECT  @num_blk = COUNT(y.StartDate)
                    FROM    ( SELECT    *
                              FROM      ( SELECT    md18.StartDate ,
                                                    md18.EndDate ,
                                                    ( SELECT TOP ( 1 )
                                                              COUNT(c.DateSpecified)
                                                      FROM    dbo.PHRPA0025 md25 ,
                                                              dbo.PHRTMWSCAL c
                                                      WHERE   md25.EmployeeID = @empId
                                                              AND md25.StartDate <= md18.StartDate
                                                              AND md25.EndDate >= md18.StartDate
                                                              AND c.WorkScheduleType = md25.WorkScheduleType
                                                              AND c.DateSpecified >= md18.StartDate
                                                              AND c.DateSpecified <= md18.EndDate
                                                              AND 'WORK' = ( SELECT TOP ( 1 )
                                                              Flag
                                                              FROM
                                                              dbo.PHRTMDAYTYP t
                                                              WHERE
                                                              t.DayType = c.DayType
                                                              )
                                                    ) AS c_wday
                                          FROM      dbo.PHRPA0018 md18 ,
                                                    dbo.PHRTMABSTYP at
                                          WHERE     md18.EmployeeID = @empId
                                                    AND md18.StartDate >= @sdt
                                                    AND md18.StartDate <= @edt
                                                    AND at.AbsenceQuotaType = @quota_type
                                                    AND at.AbsenceType = md18.AbsenceType
                                        ) x
                              WHERE     x.c_wday >= 5
                            ) y
                    IF ( @num_blk > 0 ) 
                        BEGIN
                            SET @lvlcont = 1
                        END 
                    IF ( ( SELECT   COUNT(val)
                           FROM     @value
                         ) > 0 ) 
                        BEGIN
                            SELECT  @valwf = SUM(val)
                            FROM    @value
                            SET @rquot = @rquot - @valwf
                        END
            
                    SET @rquot_total = @rquot
                    IF ( @intFlag = @aq_count ) 
                        BEGIN
                            SET @rquot_total = @rquot_total - @quota_taken
                            SET @qtaken = @quota_taken
                        END
                    ELSE 
                        IF ( @rquot_total > @quota_taken ) 
                            BEGIN					
                                IF ( @cuti_taken_meded > 0 ) 
                                    BEGIN
                                        SET @rquot_total = @rquot_total
                                            - @cuti_taken_meded
                                        SET @qtaken = @cuti_taken_meded
                                        SET @quota_taken = @quota_taken
                                            - @cuti_taken_meded
                                    END
                                ELSE 
                                    BEGIN
                                        SET @rquot_total = @rquot_total
                                            - @quota_taken
                                        SET @qtaken = @quota_taken
                                        SET @quota_taken = 0
                                    END
                    
                            END
                        ELSE 
                            IF ( @rquot_total < @quota_taken ) 
                                BEGIN
                                    IF ( @rquot_total >= @cuti_taken_meded
                                         AND @cuti_taken_meded > 0
                                       ) 
                                        BEGIN
                                            SET @rquot_total = @rquot_total
                                                - @cuti_taken_meded
                                            SET @qtaken = @cuti_taken_meded
                                            SET @quota_taken = @quota_taken
                                                - @cuti_taken_meded
                                        END
                                    ELSE 
                                        IF ( @rquot_total < @cuti_taken_meded
                                             AND @cuti_taken_meded > 0
                                           ) 
                                            BEGIN								
                                                SET @qtaken = @rquot_total
                                                SET @quota_taken = @quota_taken
                                                    - @rquot_total
                                                SET @rquot_total = 0
                                            END
                                        ELSE 
                                            BEGIN						
                                                SET @quota_taken = @quota_taken
                                                    - @rquot_total
                                                SET @qtaken = @rquot_total
                                                SET @rquot_total = 0
                                            END
                                END
                            ELSE 
                                IF ( @rquot_total = @quota_taken ) 
                                    BEGIN
                                        IF ( @rquot_total > @cuti_taken_meded
                                             AND @cuti_taken_meded > 0
                                           ) 
                                            BEGIN
                                                SET @rquot_total = @rquot_total
                                                    - @cuti_taken_meded
                                                SET @qtaken = @cuti_taken_meded
                                                SET @quota_taken = @quota_taken
                                                    - @cuti_taken_meded
                                            END
                                        ELSE 
                                            BEGIN
                                                SET @qtaken = @quota_taken
                                                SET @quota_taken = 0
                                                SET @rquot_total = 0
                                            END
                                    END
                    DELETE  FROM @value
                    DELETE  FROM @list_1
                    DELETE  FROM @list_2
                    IF ( @intFlag = 1 ) 
                        BEGIN
                            SET @main_leave_quota = @oquot
                            SET @main_remaining_quota = @rquot
                            SET @main_quota_type = @quota_type
                            SET @main_quota_start_date = @sdt
                            SET @main_quota_end_date = @edt
                            SET @main_quota_seq = @seql
                            SET @main_quota_sded = @sded
                            SET @main_quota_eded = @eded
                            IF ( @absenceType = '1000'
                                 OR @absenceType = '1100'
                                 OR @absenceType = '1200'
                               ) 
                                BEGIN
                                    SET @main_quota_lb_cont = @lvlcont
                                END
                            ELSE 
                                BEGIN
                                    SET @main_quota_lb_cont = '1'
                                END
				
                
                            SET @main_total_remaining_quota = @rquot_total
                            SET @main_quota_taken = @qtaken
                        END
                    ELSE 
                        BEGIN
                            SET @main_leave_quota_2 = @oquot
                            SET @main_remaining_quota_2 = @rquot
                            SET @main_quota_type_2 = @quota_type
                            SET @main_quota_start_date_2 = @sdt
                            SET @main_quota_end_date_2 = @edt
                            SET @main_quota_seq_2 = @seql
                            SET @main_quota_sded_2 = @sded
                            SET @main_quota_eded_2 = @eded
                            IF ( @absenceType = '1000'
                                 OR @absenceType = '1100'
                                 OR @absenceType = '1200'
                               ) 
                                BEGIN
                                    SET @main_quota_lb_cont_2 = @lvlcont
                                END
                            ELSE 
                                BEGIN
                                    SET @main_quota_lb_cont_2 = '1'
                                END
                
                            SET @main_total_remaining_quota_2 = @rquot_total
                            SET @main_quota_taken_2 = @qtaken
                        END
                    SET @intFlag = @intFlag + 1
                END
        END 
    IF ( @main_leave_quota = '' ) 
        BEGIN 
            SET @Warning_Type = 1
            SET @Warning_Msg = ( SELECT MessageID
                                 FROM   dbo.SMESSAGECATALOG
                                 WHERE  MessageID = 'TMWSCEK'
                                        AND LangId = @langid
                               )
        END    
/*START PRORATE CUTI MNC BY WARA 20170309*/
    DECLARE @periodMonthLeave AS VARCHAR(2)= ( SELECT   DATEPART(MM, @endLeave)
                                             )
    DECLARE @periodYearLeave AS VARCHAR(2)= ( SELECT    DATEPART(YYYY,
                                                              @stLeave)
                                            )
    DECLARE @adadata AS INT = ( SELECT  CAST(@main_leave_quota AS INT)
                                        - ( SELECT  CAST(@main_remaining_quota AS INT)
                                          )
                              )
    DECLARE @adadata2 AS INT = ( SELECT CAST(@main_leave_quota_2 AS INT)
                                        - ( SELECT  CAST(@main_remaining_quota_2 AS INT)
                                          )
                               )
    DECLARE @takeQuota AS VARCHAR(8)= ( DATEDIFF(yyyy, @endLeave,
                                                 @main_quota_eded) )
    SET @main_remaining_quota_prorate_2 = ( SELECT  @main_remaining_quota_2
                                          )
--SELECT  @main_quota_eded AS main_quota_eded
--SELECT  @main_quota_eded_2 AS main_quota_eded_2
--SELECT  @main_quota_sded AS main_quota_sded
--SELECT  @main_quota_sded_2 AS main_quota_sded_2
--SELECT  @endLeave AS tglcuti 
--SELECT  @takeQuota AS takeQuota
    IF ( (@main_quota_sded_2 <> ''
         AND @main_quota_eded_2 <> '')
       ) 
        BEGIN
            SET @main_remaining_quota_prorate = ( SELECT    @main_remaining_quota
                                                )
            SET @main_remaining_quota_prorate_2 = ( SELECT  AccumulateAbsence
                                                    FROM    dbo.CPHRTMABSPRT
                                                    WHERE   AbsenceQuotaType = @main_quota_type_2
                                                            AND Month = @periodMonthLeave
                                                  ) - @adadata2
        END
    ELSE 
        BEGIN
            IF ( @takeQuota = '0' ) 
                SET @main_remaining_quota_prorate = ( SELECT  AccumulateAbsence
                                                      FROM    dbo.CPHRTMABSPRT
                                                      WHERE   AbsenceQuotaType = @main_quota_type
                                                              AND Month = '13'
                                                    ) - @adadata
            ELSE 
                SET @main_remaining_quota_prorate = ( SELECT  AccumulateAbsence
                                                      FROM    dbo.CPHRTMABSPRT
                                                      WHERE   AbsenceQuotaType = @main_quota_type
                                                              AND Month = @periodMonthLeave
                                                    ) - @adadata
        END                                          
    SET @main_total_remaining_quota_prorate = ( SELECT  @main_remaining_quota_prorate
                                                        - @main_quota_taken
                                              )	  
    SET @main_total_remaining_quota_prorate_2 = ( SELECT    @main_remaining_quota_prorate_2
                                                            - @main_quota_taken_2
                                                )												   
/*END*/ 
	       
    SELECT  @quota_taken_ori TotalAbsenceTaken ,
            @main_leave_quota OriginalQuota ,
            @main_leave_quota_2 OriginalQuota2 ,
            @main_remaining_quota RemainQuota ,
            @main_remaining_quota_prorate RemainingQuotaProRate ,
            @main_remaining_quota_2 RemainQuota2 ,
            @main_remaining_quota_prorate_2 RemainingQuotaProRate2 ,
            @main_quota_type AbsenceQuotaType ,
            @main_quota_type_2 AbsenceQuotaType2 ,
            @main_quota_start_date QuotaStartDate ,
            @main_quota_start_date_2 QuotaStartDate2 ,
            @main_quota_end_date QuotaEndDate ,
            @main_quota_end_date_2 QuotaEndDate2 ,
            @main_quota_seq QuotaSeq ,
            @main_quota_seq_2 QuotaSeq2 ,
            @main_quota_sded QuotaStDate ,
            @main_quota_sded_2 QuotaStDate2 ,
            @main_quota_eded QuotaEnDate ,
            @main_quota_eded_2 QuotaEnDate2 ,
            @main_quota_lb_cont QuotaBlockCont ,
            @main_quota_lb_cont_2 QuotaBlockCont2 ,
            @main_total_remaining_quota TotalRemainingQuota ,
            @main_total_remaining_quota_prorate TotalRemainingQuotaProRate ,
            @main_total_remaining_quota_2 TotalRemainingQuota2 ,
            @main_total_remaining_quota_prorate_2 TotalRemainingQuotaProRate2 ,
            @main_quota_taken QuotaTaken ,
            @main_quota_taken_2 QuotaTaken2 ,
            @Warning_Type WarningType ,
            @Warning_Msg WarningText