using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using static GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT;
using static GB5Shared.GB5Constant.Constant.LEAVETYPECONSTANT;

namespace PayRollDAL.Query.PayProcess
{
    // Required indexes:
    //   TPAYPROCESS: (EMPLOYEEID, NETSALARY) INCLUDE (PAYPERIODID, PAYCONFIGID, PAYSLIPSTATUS)
    //   Mpayperiod: (PAYPERIODID) INCLUDE (PAYPERIODCODE, PAYPERIODNAME, DISPLAYNAME, PERIODTYPE, PERIODID)
    //   Mpayperiod: (PERIODID, ISVISIBLE, PERIODTYPE) WHERE PAYPERIODID <> -1
    //   MPAYCONFIGURATION: (PAYCONFIGURATIONID) INCLUDE (PAYCONFIGURATIONNAME, REPORTFORMATID)
    //   tCRITERIACONFIG: (CRITERIASERVICEID, CRITERIAUSERID, CRITERIACONFIGTYPE, CRITERIAMENUID) INCLUDE (REPORTFORMATID)
    public static class PayProcessQB
    {
        public const string USER_OU_ACCESS_RIGHTS_REPORT = @"
                                    SELECT
                                        A.OUId AS OUId
                                    FROM MUSERACCESSRIGHTS A
                                    WHERE 1 = 1
                                      AND A.USERID IN (@userid)
                                ";
        public const string USER_OU_ACCESS_RIGHTS_REPORT_PG = @"
                                   SELECT
                                    A.OUId AS ""OUId""
                                FROM MUSERACCESSRIGHTS A
                                WHERE 1 = 1
                                  AND A.USERID = @userid;
                                ";

        public const string PAY_PROCESS_REPORT = @"SELECT
                            /* ========= TPAYPROCESS ========= */
                            TP.PAYPROCESSID                 AS PayProcessId,
                            TP.EMPLOYEEID                   AS EmployeeId,
                            TP.PAYPERIODID                  AS PayPeriodId,
                            TP.OUID                          AS OuId,
                            TP.PAYCONFIGID                  AS PayConfigId,

                            TP.TOTALDAYS                    AS TotalDays,
                            TP.WORKINGDAYS                  AS WorkingDays,
                            TP.PRESENTDAYS                  AS PresentDays,
                            TP.WORKEDDAYS                   AS WorkedDays,
                            TP.TOTALWORKEDDAYS              AS TotalWorkedDays,

                            TP.WEEKLYOFFDAYS                AS WeeklyOffDays,
                            TP.TOTALWEEKLYOFFDAYS           AS TotalWeeklyOffDays,
                            TP.HOLIDAYS                     AS Holidays,
                            TP.TOTALHOLIDAYS                AS TotalHolidays,

                            TP.LEAVEDAYS                    AS LeaveDays,
                            TP.TOTALLEAVEDAYS               AS TotalLeaveDays,
                            TP.ABSENTDAYS                   AS AbsentDays,
                            TP.TOTALABSENTDAYS              AS TotalAbsentDays,

                            TP.PAIDDAYS                     AS PaidDays,
                            TP.WORKEDHOURS                  AS WorkedHours,
                            TP.TOTALWORKEDHOURS             AS TotalWorkedHours,
                            TP.LEAVEHOURS                   AS LeaveHours,
                            TP.PAIDHOURS                    AS PaidHours,

                            TP.OFFICIALPERMISSIONHOURS      AS OfficialPermissionHours,
                            TP.PERSONALPERMISSIONHOURS      AS PersonalPermissionHours,
                            TP.EARLYIN                      AS EarlyIn,
                            TP.EARLYOUT                     AS EarlyOut,
                            TP.LATEIN                       AS LateIn,
                            TP.LATEOUT                      AS LateOut,

                            TP.SHORTAGEHOURS                AS ShortageHours,
                            TP.EXTRAHOURS                   AS ExtraHours,
                            TP.GRACEHOURS                   AS GraceHours,
                            TP.TOTALGRACEHOURS              AS TotalGraceHours,

                            TP.ACTUALHOURS                  AS ActualHours,
                            TP.OTHOURS                      AS OtHours,
                            TP.OTFACTORHOURS                AS OtFactorHours,

                            TP.COMPENSATORYWORK             AS CompensatoryWork,
                            TP.TOTALCOMPENSATORYWORK        AS TotalCompensatoryWork,
                            TP.COMPENSATORYLEAVE            AS CompensatoryLeave,
                            TP.TOTALCOMPENSATORYLEAVE       AS TotalCompensatoryLeave,

                            TP.PAYMENTBATCH                 AS PaymentBatch,
                            TP.PAIDON                       AS PaidOn,
                            TP.PAIDBY                       AS PaidBy,
                            TP.PAYSLIPSTATUS                AS PaySlipStatus,
                            TP.REASON                       AS Reason,
                            TP.REMARKS                      AS Remarks,
                            TP.PAYMODE                      AS PayMode,

                            TP.CONTRACTDETAILID             AS ContractDetailId,
                            TP.WAGERULEID                   AS WageRuleId,
                            TP.RULEID                       AS RuleId,
                            TP.WORKTYPEID                   AS WorkTypeId,

                            TP.PFEMPLOYERPERCENTAGE         AS PfEmployerPercentage,
                            TP.PFEMPLOYERACTUALPERCENTAGE   AS PfEmployerActualPercentage,
                            TP.FPFEMPLOYERPERCENTAGE        AS FpfEmployerPercentage,
                            TP.FPFEMPLOYERACTUALPERCENTAGE  AS FpfEmployerActualPercentage,
                            TP.DLIPERCENTAGE                AS DliPercentage,

                            TP.PFEMPLOYEEPERCENTAGE         AS PfEmployeePercentage,
                            TP.PFEMPLOYEEACTUALPERCENTAGE   AS PfEmployeeActualPercentage,
                            TP.FPFEMPLOYEEACTUALPERCENTAGE  AS FpfEmployeeActualPercentage,

                            TP.ESIEMPLOYERPERCENTAGE        AS EsiEmployerPercentage,
                            TP.ESIEMPLOYEEPERCENTAGE        AS EsiEmployeePercentage,

                            TP.KMETERS                      AS KmEters,
                            TP.EMPLOYEEKMCHARGES            AS EmployeeKmCharges,
                            TP.EMPLOYERKMCHARGES            AS EmployerKmCharges,
                            TP.KMCHARGES                    AS KmCharges,

                            TP.TOTALEARNINGS                AS TotalEarnings,
                            TP.TOTALDEDUCTIONS              AS TotalDeductions,
                            TP.NETSALARY                    AS NetSalary,

                            TP.EMPLOYEEACCOUNTID            AS EmployeeAccountId,
                            TP.STATUS                       AS Status,
                            TP.VERSION                      AS Version,

                            TP.CREATEDBYID                  AS CreatedById,
                            TP.CREATEDON                    AS CreatedOn,
                            TP.MODIFIEDBYID                 AS ModifiedById,
                            TP.MODIFIEDON                   AS ModifiedOn,

                            TP.ISACCOUNTPOST                AS IsAccountPost,
                            TP.VOUCHERID                    AS VoucherId,

                            TP.C2C                          AS C2C,
                            TP.ISPFAPPLICABLE               AS IsPfApplicable,
                            TP.ISESIAPPLICABLE              AS IsEsiApplicable,

                            TP.PAYMENTID                    AS PaymentId,
                            TP.PAYMENTNUMBER                AS PaymentNumber,
                            TP.BIZTRANSACTIONTYPEID         AS BizTransactionTypeId,

                            TP.PIHOURS                      AS PiHours,

                            TP.PREVIOUSHOLIDAYS             AS PreviousHolidays,
                            TP.PREVIOUSWEEKLYOFFDAYS        AS PreviousWeeklyOffDays,
                            TP.ATTENDANCEDAYS               AS AttendanceDays,
                            TP.PREVIOUSPRESENTDAYS          AS PreviousPresentDays,

                            TP.MMDETAILID                   AS MmDetailId,
                            TP.WORKSUBTYPEID                AS WorkSubTypeId,

                            TP.WAGEUOM                      AS WageUom,
                            TP.WAGECURRENCYID               AS WageCurrencyId,
                            TP.WAGEPAYMENTCURRENCYID        AS WagePaymentCurrencyId,

                            TP.CURRENCYCONVERSION           AS CurrencyConversion,
                            TP.WAGERATE                     AS WageRate,
                            TP.PAYREVISIONID                AS PayRevisionId,

                            TP.ATTENDANCEFROMDATE           AS AttendanceFromDate,
                            TP.ATTENDANCETODATE             AS AttendanceToDate,

                            TP.WAGEPAYFROMACCOUNTID         AS WagePayFromAccountId,

                            TP.BILLDAYS                     AS BillDays,
                            TP.OTDAYS                       AS OtDays,
                            TP.TOTALADDITIONS               AS TotalAdditions,
                            TP.PAYFORDAYS                   AS PayForDays,

                            TP.ACTUALPAYPERIODID            AS ActualPayPeriodId,

                            TP.NUMBEROFLATEIN               AS NumberOfLateIn,
                            TP.NUMBEROFEARLYOUT             AS NumberOfEarlyOut,
                            TP.NUMBEROFEARLYIN              AS NumberOfEarlyIn,
                            TP.NUMBEROFLATEOUT              AS NumberOfLateOut,

                            TP.NUMBEROFPERMISSION           AS NumberOfPermission,
                            TP.NUMBEROFWORKEDHOLIDAY        AS NumberOfWorkedHoliday,
                            TP.NUMBEROFWORKEDWEEKLYOFF      AS NumberOfWorkedWeeklyOff,
                            TP.ACTUALPRESENTDAYS            AS ActualPresentDays,

                            TP.NUMBEROFSHORT                AS NumberOfShort,
                            TP.NUMBEROFEXTRA                AS NumberOfExtra,
                            TP.NUMBEROFOT                   AS NumberOfOt,

                            TP.NUMBEROFBUSINESSPERMISSION   AS NumberOfBusinessPermission,
                            TP.NUMBEROFPERSONALPERMISSION   AS NumberOfPersonalPermission,
                            TP.NUMBEROFPI                   AS NumberOfPi,
                            TP.NUMBEROFADJUSTMENT           AS NumberOfAdjustment,

                            TP.HOLIDAYWORKINGHOURS          AS HolidayWorkingHours,
                            TP.WEEKLYOFFWORKINGHOURS        AS WeeklyOffWorkingHours,
                            TP.TOTALLEAVEHOURS              AS TotalLeaveHours,

                            TP.ISINTERNAL                   AS IsInternal,
                            TP.PAYSLIPVOUCHERID             AS PaySlipVoucherId,
                            TP.PAYPRODUCTIONVALUE           AS PayProductionValue,

                            /* ========= MPAYPERIOD ========= */
                            MP.PAYPERIODID                  AS PayPeriodId,
                            MP.PAYPERIODCODE                AS PayPeriodCode,
                            MP.PAYPERIODNAME                AS PayPeriodName

                        FROM
                            TPAYPROCESS TP,
                            MPAYPERIOD MP
                        WHERE
                            TP.PAYPERIODID = MP.PAYPERIODID
                            AND TP.ISINTERNAL = 1";

        public const string PAY_PROCESS_REPORT_PG = @"
                            SELECT
                                TP.PAYPROCESSID               AS ""PayProcessId"",
                                TP.EMPLOYEEID                 AS ""EmployeeId"",
                                TP.PAYPERIODID                AS ""PayPeriodId"",
                                TP.OUID                       AS ""OuId"",
                                TP.PAYCONFIGID                AS ""PayConfigId"",

                                TP.TOTALDAYS                  AS ""TotalDays"",
                                TP.WORKINGDAYS                AS ""WorkingDays"",
                                TP.PRESENTDAYS                AS ""PresentDays"",
                                TP.WORKEDDAYS                 AS ""WorkedDays"",
                                TP.TOTALWORKEDDAYS            AS ""TotalWorkedDays"",

                                TP.WEEKLYOFFDAYS              AS ""WeeklyOffDays"",
                                TP.TOTALWEEKLYOFFDAYS         AS ""TotalWeeklyOffDays"",
                                TP.HOLIDAYS                   AS ""Holidays"",
                                TP.TOTALHOLIDAYS              AS ""TotalHolidays"",

                                TP.LEAVEDAYS                  AS ""LeaveDays"",
                                TP.TOTALLEAVEDAYS             AS ""TotalLeaveDays"",
                                TP.ABSENTDAYS                 AS ""AbsentDays"",
                                TP.TOTALABSENTDAYS            AS ""TotalAbsentDays"",

                                TP.PAIDDAYS                   AS ""PaidDays"",
                                TP.WORKEDHOURS                AS ""WorkedHours"",
                                TP.TOTALWORKEDHOURS           AS ""TotalWorkedHours"",
                                TP.LEAVEHOURS                 AS ""LeaveHours"",
                                TP.PAIDHOURS                  AS ""PaidHours"",

                                TP.OFFICIALPERMISSIONHOURS    AS ""OfficialPermissionHours"",
                                TP.PERSONALPERMISSIONHOURS    AS ""PersonalPermissionHours"",
                                TP.EARLYIN                    AS ""EarlyIn"",
                                TP.EARLYOUT                   AS ""EarlyOut"",
                                TP.LATEIN                     AS ""LateIn"",
                                TP.LATEOUT                    AS ""LateOut"",

                                TP.SHORTAGEHOURS              AS ""ShortageHours"",
                                TP.EXTRAHOURS                 AS ""ExtraHours"",
                                TP.GRACEHOURS                 AS ""GraceHours"",
                                TP.TOTALGRACEHOURS            AS ""TotalGraceHours"",

                                TP.ACTUALHOURS                AS ""ActualHours"",
                                TP.OTHOURS                    AS ""OtHours"",
                                TP.OTFACTORHOURS              AS ""OtFactorHours"",

                                TP.COMPENSATORYWORK           AS ""CompensatoryWork"",
                                TP.TOTALCOMPENSATORYWORK      AS ""TotalCompensatoryWork"",
                                TP.COMPENSATORYLEAVE          AS ""CompensatoryLeave"",
                                TP.TOTALCOMPENSATORYLEAVE     AS ""TotalCompensatoryLeave"",

                                TP.PAYMENTBATCH               AS ""PaymentBatch"",
                                TP.PAIDON                     AS ""PaidOn"",
                                TP.PAIDBY                     AS ""PaidBy"",
                                TP.PAYSLIPSTATUS              AS ""PaySlipStatus"",
                                TP.REASON                     AS ""Reason"",
                                TP.REMARKS                    AS ""Remarks"",
                                TP.PAYMODE                    AS ""PayMode"",

                                TP.CONTRACTDETAILID           AS ""ContractDetailId"",
                                TP.WAGERULEID                 AS ""WageRuleId"",
                                TP.RULEID                     AS ""RuleId"",
                                TP.WORKTYPEID                 AS ""WorkTypeId"",

                                TP.TOTALEARNINGS              AS ""TotalEarnings"",
                                TP.TOTALDEDUCTIONS            AS ""TotalDeductions"",
                                TP.NETSALARY                  AS ""NetSalary"",

                                TP.CREATEDBYID                AS ""CreatedById"",
                                TP.CREATEDON                  AS ""CreatedOn"",
                                TP.MODIFIEDBYID               AS ""ModifiedById"",
                                TP.MODIFIEDON                 AS ""ModifiedOn"",

                                TP.ISINTERNAL                 AS ""IsInternal"",
                                TP.PAYSLIPVOUCHERID           AS ""PaySlipVoucherId"",

                                MP.PAYPERIODID                AS ""PayPeriodId"",
                                MP.PAYPERIODCODE              AS ""PayPeriodCode"",
                                MP.PAYPERIODNAME              AS ""PayPeriodName""

                            FROM TPAYPROCESS TP
                            JOIN MPAYPERIOD MP
                              ON TP.PAYPERIODID = MP.PAYPERIODID
                            WHERE TP.ISINTERNAL = 1
                            ";


        public const string PAY_PROCESS_REPORT_FOR_RESIGNED_EMPLOYEE = @"SELECT
                                    /* ========= TPAYPROCESS ========= */
                                    tp.PAYPROCESSID                AS PayProcessId,
                                    tp.EMPLOYEEID                  AS EmployeeId,
                                    tp.PAYPERIODID                 AS PayPeriodId,
                                    tp.OUID                        AS OuId,
                                    tp.PAYCONFIGID                 AS PayConfigId,

                                    tp.TOTALDAYS                   AS TotalDays,
                                    tp.WORKINGDAYS                 AS WorkingDays,
                                    tp.PRESENTDAYS                 AS PresentDays,
                                    tp.WORKEDDAYS                  AS WorkedDays,
                                    tp.TOTALWORKEDDAYS             AS TotalWorkedDays,

                                    tp.WEEKLYOFFDAYS               AS WeeklyOffDays,
                                    tp.TOTALWEEKLYOFFDAYS          AS TotalWeeklyOffDays,
                                    tp.HOLIDAYS                    AS Holidays,
                                    tp.TOTALHOLIDAYS               AS TotalHolidays,

                                    tp.LEAVEDAYS                   AS LeaveDays,
                                    tp.TOTALLEAVEDAYS              AS TotalLeaveDays,
                                    tp.ABSENTDAYS                  AS AbsentDays,
                                    tp.TOTALABSENTDAYS             AS TotalAbsentDays,

                                    tp.PAIDDAYS                    AS PaidDays,
                                    tp.WORKEDHOURS                 AS WorkedHours,
                                    tp.TOTALWORKEDHOURS            AS TotalWorkedHours,
                                    tp.LEAVEHOURS                  AS LeaveHours,
                                    tp.PAIDHOURS                   AS PaidHours,

                                    tp.PAYSLIPSTATUS               AS PaySlipStatus,
                                    tp.ISINTERNAL                  AS IsInternal,

                                    /* ========= MPAYPERIOD ========= */
                                    mp.PAYPERIODID                 AS MPayPeriodId,
                                    mp.PAYPERIODCODE               AS PayPeriodCode,
                                    mp.PAYPERIODNAME               AS PayPeriodName,

                                    /* ========= TRELIEVING ========= */
                                    tr.RELIEVINGID                 AS RelievingId,
                                    tr.SETTLEMENTTYPE              AS SettlementType,
                                    tr.SEPARATIONDATE              AS SeparationDate

                                FROM TPAYPROCESS tp
                                INNER JOIN MPAYPERIOD mp
                                    ON tp.PAYPERIODID = mp.PAYPERIODID

                                INNER JOIN TRELIEVING tr
                                    ON tr.EMPLOYEEID = tp.EMPLOYEEID

                                WHERE tp.ISINTERNAL = 1
                                  AND tr.SETTLEMENTTYPE IN (1, 2)
                                  AND tr.SEPARATIONDATE BETWEEN mp.FROMDATE AND mp.TODATE";

        public const string PAY_PROCESS_REPORT_FOR_RESIGNED_EMPLOYEE_PG = @"
                        SELECT
                            /* ========= TPAYPROCESS ========= */
                            tp.PAYPROCESSID      AS ""PayProcessId"",
                            tp.EMPLOYEEID        AS ""EmployeeId"",
                            tp.PAYPERIODID       AS ""PayPeriodId"",
                            tp.OUID              AS ""OuId"",
                            tp.PAYCONFIGID       AS ""PayConfigId"",

                            tp.TOTALDAYS         AS ""TotalDays"",
                            tp.WORKINGDAYS       AS ""WorkingDays"",
                            tp.PRESENTDAYS       AS ""PresentDays"",
                            tp.WORKEDDAYS        AS ""WorkedDays"",
                            tp.TOTALWORKEDDAYS   AS ""TotalWorkedDays"",

                            tp.WEEKLYOFFDAYS     AS ""WeeklyOffDays"",
                            tp.TOTALWEEKLYOFFDAYS AS ""TotalWeeklyOffDays"",
                            tp.HOLIDAYS          AS ""Holidays"",
                            tp.TOTALHOLIDAYS     AS ""TotalHolidays"",

                            tp.LEAVEDAYS         AS ""LeaveDays"",
                            tp.TOTALLEAVEDAYS    AS ""TotalLeaveDays"",
                            tp.ABSENTDAYS        AS ""AbsentDays"",
                            tp.TOTALABSENTDAYS   AS ""TotalAbsentDays"",

                            tp.PAIDDAYS          AS ""PaidDays"",
                            tp.WORKEDHOURS       AS ""WorkedHours"",
                            tp.TOTALWORKEDHOURS  AS ""TotalWorkedHours"",
                            tp.LEAVEHOURS        AS ""LeaveHours"",
                            tp.PAIDHOURS         AS ""PaidHours"",

                            tp.PAYSLIPSTATUS     AS ""PaySlipStatus"",
                            tp.ISINTERNAL        AS ""IsInternal"",

                            /* ========= MPAYPERIOD ========= */
                            mp.PAYPERIODID       AS ""MPayPeriodId"",
                            mp.PAYPERIODCODE     AS ""PayPeriodCode"",
                            mp.PAYPERIODNAME     AS ""PayPeriodName"",

                            /* ========= TRELIEVING ========= */
                            tr.RELIEVINGID       AS ""RelievingId"",
                            tr.SETTLEMENTTYPE    AS ""SettlementType"",
                            tr.SEPARATIONDATE    AS ""SeparationDate""

                        FROM TPAYPROCESS tp
                        INNER JOIN MPAYPERIOD mp
                            ON tp.PAYPERIODID = mp.PAYPERIODID

                        INNER JOIN TRELIEVING tr
                            ON tr.EMPLOYEEID = tp.EMPLOYEEID

                        WHERE tp.ISINTERNAL = 1
                          AND tr.SETTLEMENTTYPE IN (1, 2)
                          AND tr.SEPARATIONDATE BETWEEN mp.FROMDATE AND mp.TODATE
                        ";

        public const string GET_SELECT_LIST_PAYPERIOD = @"
                    SELECT 
                        PAYPERIODID AS Id,
                        PAYPERIODCODE AS Code,
                        PAYPERIODNAME AS Name,
                        APPLICABLEOUIDS AS ApplicableOuIds,
                        APPLICABLEPAYCONFIGIDS AS ApplicablePayConfigIds,
                        DISPLAYNAME AS DisplayName
                    FROM 
                        Mpayperiod
                    WHERE 
                        1 = 1
                        AND (APPLICABLEOUIDS = '-1' OR APPLICABLEOUIDS LIKE '%:ouid%')
                        AND (APPLICABLEPAYCONFIGIDS LIKE '%:payconfigid%')
                        AND PayperiodId <> -1
                        AND IsVisible = 0";

        public const string GET_SELECT_LIST_PAYPERIOD_EMPLOYEE = @"
                        SELECT 
                            PAYPERIODID AS Id,
                            PAYPERIODCODE AS Code,
                            PAYPERIODNAME AS Name,
                            APPLICABLEOUIDS AS ApplicableOuIds,
                            APPLICABLEPAYCONFIGIDS AS ApplicablePayConfigIds,
                            DISPLAYNAME AS DisplayName,
                            ATTENDANCETYPE AS AttendanceType,
                            FROMDATE AS PeriodFromDate,
                            TODATE AS PeriodToDate
                        FROM 
                            Mpayperiod
                        WHERE 
                            (APPLICABLEOUIDS = '-1' OR APPLICABLEOUIDS LIKE '%:ouid%')
                            AND (APPLICABLEPAYCONFIGIDS = '-1' OR APPLICABLEPAYCONFIGIDS LIKE '%:payconfigid%')
                            AND PAYPERIODID <> -1
                            AND ISVISIBLE = 0
                            AND PERIODID = :periodid
                            AND PAYPERIODSTATUS <> 2;
                        ";


        public const string GET_SELECT_LIST_PAYPERIOD_WITH_ARREAR_EMPLOYEE = @"
                    SELECT 
                        Mpayperiod.PAYPERIODID AS Id,
                        Mpayperiod.PAYPERIODCODE AS Code,
                        Mpayperiod.PAYPERIODNAME AS Name,
                        Mpayperiod.APPLICABLEOUIDS AS ApplicableOuIds,
                        Mpayperiod.APPLICABLEPAYCONFIGIDS AS ApplicablePayConfigIds,
                        Mpayperiod.DISPLAYNAME AS DisplayName,
                        Mpayperiod.FROMDATE AS PeriodFromDate,
                        Mpayperiod.TODATE AS PeriodToDate
                    FROM 
                        Mpayperiod
                    INNER JOIN 
                        TPAYPROCESS ON Mpayperiod.PAYPERIODID = TPAYPROCESS.PAYPERIODID
                    WHERE 
                        TPAYPROCESS.EMPLOYEEID = :employeeid
                        AND TPAYPROCESS.NETSALARY > 0
                        AND (Mpayperiod.APPLICABLEOUIDS = '-1' OR Mpayperiod.APPLICABLEOUIDS LIKE '%:ouid%')
                        AND (Mpayperiod.APPLICABLEPAYCONFIGIDS = '-1' OR Mpayperiod.APPLICABLEPAYCONFIGIDS LIKE '%:payconfigid%')
                        AND Mpayperiod.PAYPERIODID <> -1
                        AND Mpayperiod.periodType IN (0, 1)
                        AND Mpayperiod.ISVISIBLE = 0
                        AND Mpayperiod.PERIODID = :periodid;
                    ";


        public const string GET_SELECT_LIST_PAYPERIOD_FROM_TPAYPROCESS_EMPLOYEE_PAGED = @"
WITH OrderedPayPeriods AS
(
    SELECT 
        Mpayperiod.PAYPERIODID AS Id,
        Mpayperiod.PAYPERIODCODE AS Code,
        Mpayperiod.PAYPERIODNAME AS Name,
        C.PAYCONFIGURATIONID AS PayConfigId,
        C.PAYCONFIGURATIONNAME AS PayConfigName,
        Mpayperiod.DISPLAYNAME AS DisplayName,
        D.ReportFormatId AS ReportFormatId,
        REPLACE(D.DATASOURCEFILENAME, '.jasper', '') AS PathFileName,
        ISNULL(repfor.ReportId, -1) AS ReportId,
        REPLACE(
            REPLACE(
                REPLACE(
                    REPLACE(F.URITEMPLATE, 'http://{BaseURI}', ''),
                'https://{BaseURI}', ''),
            '{FirstNumber}', '1'),
        '{MaxResult}', '50') AS WebServiceUriTemplate,
        CASE TPAYPROCESS.PAYSLIPSTATUS
            WHEN 0 THEN 'Prepared'
            WHEN 1 THEN 'Paid'
            WHEN 2 THEN 'Blocked'
            WHEN 3 THEN 'UnPaid'
            WHEN 4 THEN 'Confirmed'
            ELSE ''
        END AS PaySlipStatus,
        TPAYPROCESS.NETSALARY AS NetSalary,
        ROW_NUMBER() OVER (ORDER BY paydate DESC, periodtype) AS RowNum
    FROM Mpayperiod
    INNER JOIN TPAYPROCESS 
        ON Mpayperiod.PAYPERIODID = TPAYPROCESS.PAYPERIODID
    INNER JOIN MPAYCONFIGURATION C
        ON TPAYPROCESS.PAYCONFIGID = C.PAYCONFIGURATIONID
    OUTER APPLY (
        SELECT config.REPORTFORMATID
        FROM tCRITERIACONFIG config
        WHERE config.CRITERIASERVICEID = -1399994118
          AND config.CRITERIAUSERID = TPAYPROCESS.EMPLOYEEID
          AND config.CRITERIACONFIGTYPE = 0
          AND config.criteriamenuid = -1
    ) config
    INNER JOIN MREPORTFORMAT D
        ON ISNULL(config.REPORTFORMATID, C.REPORTFORMATID) = D.REPORTFORMATID
    OUTER APPLY (
        SELECT TOP (1) repfor.ReportId
        FROM MREPORTVSFORMATS repfor
        WHERE repfor.reportformatid = D.reportformatid
    ) repfor
    INNER JOIN MREPORT E
        ON ISNULL(repfor.REPORTID, -1) = E.REPORTID
    INNER JOIN MWEBSERVICE F
        ON E.WEBSERVICEID = F.WEBSERVICEID
    WHERE
        TPAYPROCESS.EMPLOYEEID = @EmployeeId
        AND TPAYPROCESS.NETSALARY > 0
        AND Mpayperiod.PAYPERIODID <> -1
        AND Mpayperiod.periodType IN (0,1)
        AND Mpayperiod.ISVISIBLE = 0
        AND Mpayperiod.PERIODID = @PeriodId

        -- ✅ SAFE STRING FILTERS
        AND (@Name IS NULL OR Mpayperiod.PAYPERIODNAME LIKE '%' + @Name + '%')
        AND (@Code IS NULL OR Mpayperiod.PAYPERIODCODE LIKE '%' + @Code + '%')
        AND (@DisplayName IS NULL OR Mpayperiod.DISPLAYNAME LIKE '%' + @DisplayName + '%')
)
SELECT
    Id,
    Code,
    Name,
    PayConfigId,
    PayConfigName,
    DisplayName,
    ReportFormatId,
    PathFileName,
    ReportId,
    WebServiceUriTemplate,
    PaySlipStatus,
    NetSalary,
    RowNum
FROM OrderedPayPeriods
WHERE RowNum BETWEEN @Offset + 1 AND @Offset + @PageSize
ORDER BY RowNum;
";


        public const string GET_SELECT_LIST_PAYPERIOD_PLAIN = @"
WITH OrderedPayPeriods AS
(
    SELECT
        PAYPERIODID AS Id,
        PAYPERIODCODE AS Code,
        PAYPERIODNAME AS Name,
        DISPLAYNAME AS DisplayName,
        SORTORDER AS SortOrder,
        ROW_NUMBER() OVER (ORDER BY FROMDATE ASC) AS RowNum
    FROM MPAYPERIOD
    WHERE
        PAYPERIODID <> -1
        AND (@PeriodId IS NULL OR PERIODID = @PeriodId)
        AND (@Code IS NULL OR PAYPERIODCODE LIKE '%' + @Code + '%')
        AND (@Name IS NULL OR PAYPERIODNAME LIKE '%' + @Name + '%')
        AND (@DisplayName IS NULL OR DISPLAYNAME LIKE '%' + @DisplayName + '%')
)
SELECT
    Id, Code, Name, DisplayName, SortOrder
FROM OrderedPayPeriods
WHERE
    (@Offset = -1 AND @PageSize = -1)
    OR
    (RowNum BETWEEN @Offset + 1 AND @Offset + @PageSize)
ORDER BY RowNum;
";

        public const string GET_SELECT_LIST_PAYPERIOD_PLAIN_COUNT = @"
SELECT COUNT(1)
FROM MPAYPERIOD
WHERE
    PAYPERIODID <> -1
    AND (@PeriodId IS NULL OR PERIODID = @PeriodId)
    AND (@Code IS NULL OR PAYPERIODCODE LIKE '%' + @Code + '%')
    AND (@Name IS NULL OR PAYPERIODNAME LIKE '%' + @Name + '%')
    AND (@DisplayName IS NULL OR DISPLAYNAME LIKE '%' + @DisplayName + '%');
";

        public const string GET_ORGANIZATIONUNIT_NAMES_BY_IDS = @"
SELECT OUID AS Id, ORGANIZATIONUNITNAME AS Name
FROM MORGANIZATIONUNIT
WHERE OUID IN @Ids;
";

        public const string GET_PAYCONFIGURATION_NAMES_BY_IDS = @"
SELECT PAYCONFIGURATIONID AS Id, PAYCONFIGURATIONNAME AS Name
FROM MPAYCONFIGURATION
WHERE PAYCONFIGURATIONID IN @Ids;
";

        public const string CHECK_TPAYPROCESS_EXISTS_FOR_PAYPERIOD = @"
SELECT COUNT(1) FROM TPAYPROCESS WHERE PAYPERIODID = @PayPeriodId;
";

        public const string CHECK_PAYPERIOD_EXISTS = @"
SELECT COUNT(1) FROM MPAYPERIOD WHERE PAYPERIODID = @PayPeriodId;
";

        public const string SAVE_PAYPERIOD = @"
INSERT INTO MPAYPERIOD
(PAYPERIODID, PAYPERIODCODE, PAYPERIODNAME, FROMDATE, TODATE, PAYDATE, PERIODTYPE, ATTENDANCETYPE, OVERTIMETYPE,
 WORKINGDAYS, REMARKS, ACCOUNTDATE, TOTALDAYS, ISPAYROLL2TAXPOST, ISTAX2PAYROLLPOST, PAYFORDAYS, ISWITHOUTATTENDANCE,
 SORTORDER, STATUS, VERSION, SOURCETYPE, LOCKDATE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON,
 PERIODID, APPLICABLEOUIDS, APPLICABLEPAYCONFIGIDS, ISVISIBLE, DISPLAYNAME, ISREGISTERCREATION,
 ARREARPERIODFROM, ARREARPERIODTO, PAYPERIODSTATUS, ADVANCEPOSTTYPE)
VALUES
(@PayPeriodId, @PayPeriodCode, @PayPeriodName, @PayPeriodFromDate, @PayPeriodToDate, @PayPeriodPayDate, @PayPeriodPeriodType, @PayPeriodAddendanceType, @PayPeriodOvertimeType,
 @PayPeriodWorkingDays, @PayPeriodRemarks, @PayPeriodAccountDate, @PayPeriodTotalDays, @PayPeriodIsPayroll2TaxPost, @PayPeriodIsTax2PayrollPost, @PayPeriodPayForDays, @PayPeriodIsWithoutAttendance,
 @PayPeriodSortOrder, @PayPeriodStatus, @PayPeriodVersion, @PayPeriodSourceType, @PayPeriodLockDate, @PayPeriodCreatedById, @PayPeriodCreatedOn, @PayPeriodModifiedById, @PayPeriodModifiedOn,
 @PeriodId, @PayPeriodApplicableOuIds, @PayPeriodApplicablePayConfigIds, @PayPeriodIsVisible, @PayPeriodDisplayName, @PayPeriodIsRegister,
 @PayPeriodArrearPeriodFrom, @PayPeriodArrearPeriodTo, @PayPeriodTypeStatus, @PayPeriodAdvancePostType);
";

        public const string UPDATE_PAYPERIOD = @"
UPDATE MPAYPERIOD SET
    PAYPERIODCODE = @PayPeriodCode,
    PAYPERIODNAME = @PayPeriodName,
    FROMDATE = @PayPeriodFromDate,
    TODATE = @PayPeriodToDate,
    PAYDATE = @PayPeriodPayDate,
    PERIODTYPE = @PayPeriodPeriodType,
    ATTENDANCETYPE = @PayPeriodAddendanceType,
    OVERTIMETYPE = @PayPeriodOvertimeType,
    WORKINGDAYS = @PayPeriodWorkingDays,
    REMARKS = @PayPeriodRemarks,
    ACCOUNTDATE = @PayPeriodAccountDate,
    TOTALDAYS = @PayPeriodTotalDays,
    ISPAYROLL2TAXPOST = @PayPeriodIsPayroll2TaxPost,
    ISTAX2PAYROLLPOST = @PayPeriodIsTax2PayrollPost,
    PAYFORDAYS = @PayPeriodPayForDays,
    ISWITHOUTATTENDANCE = @PayPeriodIsWithoutAttendance,
    SORTORDER = @PayPeriodSortOrder,
    STATUS = @PayPeriodStatus,
    VERSION = @PayPeriodVersion,
    SOURCETYPE = @PayPeriodSourceType,
    LOCKDATE = @PayPeriodLockDate,
    MODIFIEDBYID = @PayPeriodModifiedById,
    MODIFIEDON = @PayPeriodModifiedOn,
    PERIODID = @PeriodId,
    APPLICABLEOUIDS = @PayPeriodApplicableOuIds,
    APPLICABLEPAYCONFIGIDS = @PayPeriodApplicablePayConfigIds,
    ISVISIBLE = @PayPeriodIsVisible,
    DISPLAYNAME = @PayPeriodDisplayName,
    ISREGISTERCREATION = @PayPeriodIsRegister,
    ARREARPERIODFROM = @PayPeriodArrearPeriodFrom,
    ARREARPERIODTO = @PayPeriodArrearPeriodTo,
    PAYPERIODSTATUS = @PayPeriodTypeStatus,
    ADVANCEPOSTTYPE = @PayPeriodAdvancePostType
WHERE PAYPERIODID = @PayPeriodId;
";


        public const string GET_PAYSLIP_REPORT_ADDON_DETAIL = @"SELECT
                            E.EMPLOYEEID                                  AS EmployeeId,
                            E.EMPLOYEECODE                                AS EmployeeCode,

                            PCD.SLNO                                     AS PayConfigurationDetailSlNo,
                            PCD.FIELDTYPE                                AS PayConfigurationDetailFieldType,
                            PCD.OPERATIONTYPE                            AS PayConfigurationDetailOperationType,

                            /* ================= Field Name ================= */
                            CASE 
                                WHEN PCD.FIELDTYPE = 0 THEN ADDDED.FIELDNAME
                                WHEN PCD.FIELDTYPE = 1 THEN F.FORMULANAME
                                WHEN PCD.FIELDTYPE = 2 THEN PCD.FIELDCODE
                                WHEN PCD.FIELDTYPE = 3 THEN ADV.GCMNAME
                                ELSE PCD.FIELDCODE
                            END                                          AS FieldName,

                            /* ================= Display Name ================= */
                            CASE 
                                WHEN PCD.FIELDTYPE = 0 THEN ADDDED.DISPLAYNAME
                                WHEN PCD.FIELDTYPE = 1 THEN F.PRINTINGNAME
                                WHEN PCD.FIELDTYPE = 2 THEN PCD.ACTUALFIELDNAME
                                WHEN PCD.FIELDTYPE = 3 THEN ADV.GCMNAME
                                ELSE PCD.FIELDCODE
                            END                                          AS DisplayName,

                            PCD.PRINTREQUIRED                             AS PrintRequired,

                            /* ================= Dynamic Parts ================= */
                            @AddOnValuesReplacePart,
                            @actualreplacement

                        FROM TPAYPROCESS PP
                        INNER JOIN TPAYPROCESSADDON PPA
                                ON PP.PAYPROCESSID = PPA.PAYPROCESSID

                        INNER JOIN MPAYCONFIGURATIONDETAIL PCD
                                ON PP.PAYCONFIGID = PCD.PAYCONFIGURATIONID

                        LEFT JOIN MADDITIONDEDUCTION ADDDED
                               ON PCD.FIELDCODE = ADDDED.FIELDCODE
                              AND PCD.FIELDTYPE = 0

                        LEFT JOIN MFORMULA F
                               ON PCD.FIELDCODE = F.FORMULACODE
                              AND PCD.FIELDTYPE = 1

                        LEFT JOIN MGCM ADV
                               ON PCD.FIELDCODE = ADV.GCMCODE
                              AND ADV.GCMTYPEID = -1399999983
                              AND PCD.FIELDTYPE = 3

                        INNER JOIN MEMPLOYEE E
                                ON PP.EMPLOYEEID = E.EMPLOYEEID

                        INNER JOIN MPAYPERIOD PPY
                                ON PP.PAYPERIODID = PPY.PAYPERIODID

                        INNER JOIN MUSER U
                                ON PP.PAIDBY = U.USERID

                        INNER JOIN MDEPARTMENT D
                                ON D.DEPARTMENTID = E.DEPARTMENTID

                        INNER JOIN MORGANIZATIONUNIT OU
                                ON OU.OUID = PP.OUID

                        INNER JOIN MGCM GCM
                                ON E.EMPLOYEETYPEID = GCM.GCMID

                        WHERE 1 = 1
                          AND PP.PAYPERIODID = @payperiodid
                          AND PP.PAYCONFIGID = @payconfigid
                          AND PP.ISINTERNAL = 1
                        ";


        public const string GET_PAYPERIOD = @"SELECT
                            /* ================= PayPeriod ================= */
                            PP.PAYPERIODID                  AS PayPeriodId,
                            PP.PAYPERIODCODE                AS PayPeriodCode,
                            PP.PAYPERIODNAME                AS PayPeriodName,
                            PP.FROMDATE                     AS PayPeriodFromDate,
                            PP.TODATE                       AS PayPeriodToDate,
                            PP.PAYDATE                      AS PayPeriodPayDate,
                            PP.PERIODTYPE                   AS PayPeriodPeriodType,
                            PP.ATTENDANCETYPE               AS PayPeriodAddendanceType,
                            PP.OVERTIMETYPE                 AS PayPeriodOvertimeType,
                            PP.WORKINGDAYS                  AS PayPeriodWorkingDays,
                            PP.REMARKS                      AS PayPeriodRemarks,
                            PP.ACCOUNTDATE                  AS PayPeriodAccountDate,
                            PP.TOTALDAYS                    AS PayPeriodTotalDays,

                            PP.ISPAYROLL2TAXPOST            AS PayPeriodIsPayroll2TaxPost,
                            PP.ISTAX2PAYROLLPOST            AS PayPeriodIsTax2PayrollPost,
                            PP.PAYFORDAYS                   AS PayPeriodPayForDays,
                            PP.ISWITHOUTATTENDANCE          AS PayPeriodIsWithoutAttendance,

                            /* ================= Base ================= */
                            PP.SORTORDER                    AS PayPeriodSortOrder,
                            PP.STATUS                       AS PayPeriodStatus,
                            PP.VERSION                      AS PayPeriodVersion,
                            PP.SOURCETYPE                   AS PayPeriodSourceType,
                            PP.LOCKDATE                     AS PayPeriodLockDate,

                            /* ================= Standard ================= */
                            PP.CREATEDBYID                  AS PayPeriodCreatedById,
                            PP.CREATEDON                    AS PayPeriodCreatedOn,
                            PP.MODIFIEDBYID                 AS PayPeriodModifiedById,
                            PP.MODIFIEDON                   AS PayPeriodModifiedOn,

                            /* ================= Additional ================= */
                            PP.PERIODID                     AS PeriodId,
                            PP.APPLICABLEOUIDS              AS PayPeriodApplicableOuIds,
                            PP.APPLICABLEPAYCONFIGIDS       AS PayPeriodApplicablePayConfigIds,
                            PP.ISVISIBLE                    AS PayPeriodIsVisible,
                            PP.DISPLAYNAME                  AS PayPeriodDisplayName,
                            PP.ISREGISTERCREATION           AS PayPeriodIsRegister,
                            PP.ARREARPERIODFROM             AS PayPeriodArrearPeriodFrom,
                            PP.ARREARPERIODTO               AS PayPeriodArrearPeriodTo,
                            PP.PAYPERIODSTATUS              AS PayPeriodTypeStatus,
                            PP.ADVANCEPOSTTYPE              AS PayPeriodAdvancePostType

                        FROM MPAYPERIOD PP
                        WHERE PP.PAYPERIODID = @payperiodid;
                        ";

        public static readonly string LEAVE_STATUS_REPORT_PAY_SLIP =
                           $@"
                        SELECT
                                emp.EmployeeId,
                                emp.EmployeeCode,
                                emp.EmployeeName,
                                emp.SortOrder,
                                leave.LeaveCode,
                                {{LeaveTakenFields}}      AS TakenLeave,
                                {{LeaveOpeningFields}}   AS Opening,
                                MAX(subquery.AllotedLeave) AS AllotedLeave
                        FROM
                                TMONTHLYATTENDANCE            monthattend,
                                TMONTHLYATTENDANCEADDON       monthattendaddon,
                                MLEAVE                        leave
                                    LEFT OUTER JOIN MEMPLOYEE emp
                                        ON (1 = 1 AND leave.LeaveType <> 5)

                                LEFT OUTER JOIN
                                (
                                    SELECT
                                            leavedet.EMPLOYEEID,
                                            mleave.LEAVECODE,
                                            SUM(
                                                CASE
                                                    WHEN biztype.BIZTRANSACTIONCLASSID = {LEAVECREDIT}
                                                        THEN leavedet.NUMBEROFDAYS
                                                    ELSE
                                                        -1 * leavedet.NUMBEROFDAYS
                                                END
                                            ) AS AllotedLeave
                                    FROM
                                            TLEAVE                leave,
                                            TLEAVEDETAIL          leavedet,
                                            MLEAVE                mleave,
                                            MBIZTRANSACTIONTYPE   biztype
                                    WHERE
                                            leave.LEAVEID                 = leavedet.LEAVEID
                                        AND mleave.LEAVEID                = leavedet.LEAVETYPEID
                                        AND leave.BIZTRANSACTIONTYPEID   = biztype.BIZTRANSACTIONTYPEID
                                        AND biztype.BIZTRANSACTIONCLASSID IN
                                            (
                                                {LEAVECREDIT},
                                                {LEAVEDEBIT},
                                                {LEAVE}
                                            )
                                        AND leavedet.LEAVEDATE < @fromdate
                                        AND leave.STATUS IN (0, 1)
                                    GROUP BY
                                            mleave.LEAVECODE,
                                            leavedet.EMPLOYEEID
                                ) subquery
                                    ON  subquery.EMPLOYEEID = emp.EMPLOYEEID
                                    AND leave.LEAVECODE     = subquery.LEAVECODE,

                                MPAYPERIOD payperiod
                        WHERE
                                    1 = 1
                                AND emp.EMPLOYEEID                     = monthattend.EMPLOYEEID
                                AND monthattend.MONTHLYATTENDANCEID   = monthattendaddon.MONTHLYATTENDANCEID
                                AND leave.LEAVEID                     <> -1
                                AND payperiod.PAYPERIODID              = monthattend.PAYPERIODID
                               
                                AND payperiod.TODATE                  <= @todate
                                AND payperiod.FromDate                >= @fromdate
                                AND emp.STATUS                         = 1
                                AND emp.employeestatus                 = 0
                                @replaceispaidfield
                        GROUP BY
                                emp.EmployeeId,
                                leave.LEAVECODE,
                                emp.EmployeeCode,
                                emp.EmployeeName,
                                emp.SortOrder
                        ORDER BY
                                emp.SortOrder,
                                leave.LEAVECODE
                        ";
        public static readonly string LEAVE_STATUS_REPORT_PAY_SLIP_PG = $@"
SELECT
    emp.EmployeeId        AS ""EmployeeId"",
    emp.EmployeeCode      AS ""EmployeeCode"",
    emp.EmployeeName      AS ""EmployeeName"",
    emp.SortOrder         AS ""SortOrder"",
    leave.LeaveCode       AS ""LeaveCode"",
    {{LeaveTakenFields}}    AS ""TakenLeave"",
    {{LeaveOpeningFields}}  AS ""Opening"",
    MAX(subquery.AllotedLeave) AS ""AllotedLeave""

FROM TMONTHLYATTENDANCE monthattend
JOIN TMONTHLYATTENDANCEADDON monthattendaddon
    ON monthattend.MONTHLYATTENDANCEID = monthattendaddon.MONTHLYATTENDANCEID

JOIN MPAYPERIOD payperiod
    ON payperiod.PAYPERIODID = monthattend.PAYPERIODID

JOIN MEMPLOYEE emp
    ON emp.EMPLOYEEID = monthattend.EMPLOYEEID

JOIN MLEAVE leave
    ON leave.LEAVEID <> -1

LEFT JOIN
(
    SELECT
        leavedet.EMPLOYEEID,
        mleave.LEAVECODE,
        SUM(
            CASE
                WHEN biztype.BIZTRANSACTIONCLASSID = {LEAVECREDIT}
                    THEN leavedet.NUMBEROFDAYS
                ELSE -1 * leavedet.NUMBEROFDAYS
            END
        ) AS AllotedLeave
    FROM TLEAVE leave
    JOIN TLEAVEDETAIL leavedet
        ON leave.LEAVEID = leavedet.LEAVEID
    JOIN MLEAVE mleave
        ON mleave.LEAVEID = leavedet.LEAVETYPEID
    JOIN MBIZTRANSACTIONTYPE biztype
        ON leave.BIZTRANSACTIONTYPEID = biztype.BIZTRANSACTIONTYPEID
    WHERE biztype.BIZTRANSACTIONCLASSID IN
        ({LEAVECREDIT}, {LEAVEDEBIT}, {LEAVE})
      AND leavedet.LEAVEDATE < @fromdate
      AND leave.STATUS IN (0, 1)
    GROUP BY
        mleave.LEAVECODE,
        leavedet.EMPLOYEEID
) subquery
    ON subquery.EMPLOYEEID = emp.EMPLOYEEID
   AND leave.LEAVECODE = subquery.LEAVECODE

WHERE 1 = 1
  AND payperiod.TODATE <= @todate
  AND payperiod.FromDate >= @fromdate
  AND emp.STATUS = 1
  AND emp.employeestatus = 0
  @replaceispaidfield

GROUP BY
    emp.EmployeeId,
    leave.LeaveCode,
    emp.EmployeeCode,
    emp.EmployeeName,
    emp.SortOrder

ORDER BY
    emp.SortOrder,
    leave.LeaveCode
";


        public const string GET_PAYSLIP_ADVANCE_DETAILS = @"
                        SELECT
                            E.EMPLOYEEID           AS EmployeeId,
                            E.EMPLOYEECODE         AS EmployeeCode,
                            gcm.GCMNAME              AS AdvanceName,

                            adv.NUMBEROFINSTALLMENT  AS NumberOfInstallment,
                            adv.INSTALLMENTREMAINING AS NumberOfInstallmentRemaining,
                            adv.AMOUNTREQUIRED       AS AmountRequired,
                            adv.AMOUNTSANCTIONED     AS AmountSanctioned,
                            adv.AMOUNTREMAINING      AS AmountRemaining

                        FROM TADVANCE adv
                        INNER JOIN MEMPLOYEE E
                                ON E.EMPLOYEEID = adv.EMPLOYEEID

                        INNER JOIN MGCM gcm
                                ON gcm.GCMID = adv.ADVANCETYPEID

                        INNER JOIN MGCMTYPE gcmtype
                                ON gcmtype.GCMTYPEID = gcm.GCMTYPEID
                               AND gcmtype.GCMTYPEID = -1399999983   -- Advance Type

                        INNER JOIN TPAYPROCESS PP
                                ON PP.EMPLOYEEID = E.EMPLOYEEID
                               AND PP.PAYPERIODID = @payperiodid
                               AND PP.PAYCONFIGID = @payconfigid
                               AND PP.IsInternal = 1

                        WHERE adv.PAYMENTSTATUS <> 5   -- Closed
                        ";

        public const string GET_PAYSLIP_ADVANCE_DETAILS_PG = @"
SELECT
    e.employeeid           AS ""EmployeeId"",
    e.employeecode         AS ""EmployeeCode"",
    gcm.gcmname            AS ""AdvanceName"",
    adv.numberofinstallment  AS ""NumberOfInstallment"",
    adv.installmentremaining AS ""NumberOfInstallmentRemaining"",
    adv.amountrequired       AS ""AmountRequired"",
    adv.amountsanctioned     AS ""AmountSanctioned"",
    adv.amountremaining      AS ""AmountRemaining""

FROM tadvance adv
INNER JOIN memployee e
    ON e.employeeid = adv.employeeid

INNER JOIN mgcm gcm
    ON gcm.gcmid = adv.advancetypeid

INNER JOIN mgcmtype gcmtype
    ON gcmtype.gcmtypeid = gcm.gcmtypeid
   AND gcmtype.gcmtypeid = -1399999983

INNER JOIN tpayprocess pp
    ON pp.employeeid = e.employeeid
   AND pp.payperiodid = @payperiodid
   AND pp.payconfigid = @payconfigid
   AND pp.isinternal = 1

WHERE adv.paymentstatus <> 5
";

        public static readonly string GET_VALID_TILL_DATE_LEAVE = $@"
SELECT
    ld.EMPLOYEEID AS EmployeeId,
    ld.LEAVETYPEID AS LeaveTypeId,
    ld.VALIDTILL AS ValidTill,
    SUM(ld.NUMBEROFDAYS) AS ValidTillNumberOfDays
INTO #TempValidTillLeave
FROM TLEAVE l
INNER JOIN TLEAVEDETAIL ld ON l.LEAVEID = ld.LEAVEID
INNER JOIN MBIZTRANSACTIONTYPE bt ON l.BIZTRANSACTIONTYPEID = bt.BIZTRANSACTIONTYPEID
WHERE bt.BIZTRANSACTIONCLASSID IN ({GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVECREDIT})  -- Credit Only
  AND l.LEAVEDATE >= DATEADD(YEAR, DATEDIFF(YEAR, 0, @periodfrom), 0)
  {{leavereplace}}  -- Dynamic OU filter
  AND l.STATUS IN (0, 1)
GROUP BY ld.EMPLOYEEID, ld.LEAVETYPEID, ld.VALIDTILL;

SELECT
    ROW_NUMBER() OVER(
        PARTITION BY EmployeeId, LeaveTypeId
        ORDER BY ValidTill, EmployeeId, LeaveTypeId
    ) AS SlNo,
    EmployeeId,
    LeaveTypeId,
    ValidTill,
    ValidTillNumberOfDays
INTO #TempValidTillLeaveNumbered
FROM #TempValidTillLeave;
";

        public static readonly string GET_VALID_TILL_DATE_LEAVE_PG = $@"CREATE TEMP TABLE TempValidTillLeave AS
SELECT
    ROW_NUMBER() OVER
    (
        PARTITION BY 
            ld.employeeid,
            ld.leavetypeid
        ORDER BY
            ld.validtill,
            ld.employeeid,
            ld.leavetypeid
    ) AS SlNo,

    ld.validtill       AS ValidTill,
    ld.employeeid      AS EmployeeId,
    ld.leavetypeid     AS LeaveTypeId,
    SUM(ld.numberofdays) AS ValidTillNumberOfDays

FROM tleave l
INNER JOIN tleavedetail ld
        ON l.leaveid = ld.leaveid

INNER JOIN mbiztransactiontype bt
        ON l.biztransactiontypeid = bt.biztransactiontypeid

WHERE bt.biztransactionclassid = {GB5Shared.GB5Constant.Constant.BIZTRANSACTIONCLASSCONSTANT.LEAVECREDIT}
  AND l.leavedate >= date_trunc('year', @periodfrom::timestamp)
  AND l.ouid = ANY(string_to_array(@ouid, ',')::bigint[])
  AND l.status IN (0,1)

GROUP BY
    ld.validtill,
    ld.employeeid,
    ld.leavetypeid;";
        public static readonly string GET_LEAVE_STATUS_REPORT_WITH_OR_WITHOUT_DEBIT_CLASS_USED_IN_LEAVE_STATUS_REPORT = $@"
SELECT
    emp.EMPLOYEEID               AS EmployeeId,
    emp.EMPLOYEECODE             AS EmployeeCode,
    emp.EMPLOYEENAME             AS EmployeeName,
    org.ORGANIZATIONUNITCODE     AS OUCode,
    org.ORGANIZATIONUNITNAME     AS OUName,

    ISNULL(MAX(opening.opening), 0) AS Opening,

    -- Credit
    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVECREDIT} AND a.STATUS = 1
            THEN b.NUMBEROFDAYS ELSE 0 END) AS AllotedLeave,

    SUM(
    ISNULL(openexp.BeforeDateExpiredLeave,0) +
    ISNULL(openexp.LeaveDateCreditLeave,0)
) AS OpeningExpiredLeave,

SUM(
    ISNULL(exp.BeforeDateExpiredLeave,0) +
    ISNULL(exp.LeaveDateCreditLeave,0)
) AS ExpiredLeave,


    -- Debit
    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVEDEBIT}
            THEN b.NUMBEROFDAYS ELSE 0 END) AS DebitLeave,

    -- Availed
    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVE}
             AND b.LEAVEDATE <= @allotedtodateto
            THEN b.NUMBEROFDAYS ELSE 0 END) AS AvailedLeave,

    -- Applied
    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVE}
             AND b.LEAVEDATE > @allotedtodateto
            THEN b.NUMBEROFDAYS ELSE 0 END) AS AppliedLeave,

    -- Taken
    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID IN ({LEAVEDEBIT},{LEAVE})
            THEN b.NUMBEROFDAYS ELSE 0 END) AS TakenLeave,

    -- Balance
    ISNULL(MAX(opening.opening),0)
    + SUM(CASE WHEN bt.BIZTRANSACTIONCLASSID = {LEAVECREDIT} THEN b.NUMBEROFDAYS ELSE 0 END)
    - SUM(CASE WHEN bt.BIZTRANSACTIONCLASSID IN ({LEAVEDEBIT},{LEAVE}) THEN b.NUMBEROFDAYS ELSE 0 END)
      AS LeaveBalance,

    e.LEAVECODE,
    e.LEAVENAME,
    e.LEAVETYPE,
    e.LEAVEID,

    MAX(vv.con) AS ApprovalPending,

    d.DEPARTMENTID,
    d.DEPARTMENTCODE,
    d.DEPARTMENTNAME

FROM MEMPLOYEE emp
JOIN MORGANIZATIONUNIT org ON emp.WorkOUID = org.OUID
JOIN MDEPARTMENT d ON emp.DEPARTMENTID = d.DEPARTMENTID
JOIN MLEAVE e
  ON e.LEAVETYPE = {PAIDLEAVE}


LEFT JOIN TLEAVEDETAIL b
       ON emp.EMPLOYEEID = b.EMPLOYEEID
      AND e.LEAVEID = b.LEAVETYPEID
      AND b.LEAVEDATE >= @leaveexpiredperiodfrom
      AND b.LEAVEDATE <= DATEADD(YEAR, DATEDIFF(YEAR,0,@cutoffdate)+1,-1)

LEFT JOIN TLEAVE a
       ON a.LEAVEID = b.LEAVEID
      AND a.STATUS IN (0,1)

LEFT JOIN MBIZTRANSACTIONTYPE bt
       ON bt.BIZTRANSACTIONTYPEID = a.BIZTRANSACTIONTYPEID

OUTER APPLY (
    SELECT SUM(
        CASE
            WHEN bt2.BIZTRANSACTIONCLASSID = {LEAVECREDIT}
            THEN d2.NUMBEROFDAYS
            ELSE -d2.NUMBEROFDAYS END
    ) AS opening
    FROM TLEAVEDETAIL d2
    JOIN TLEAVE l2 ON l2.LEAVEID = d2.LEAVEID
    JOIN MBIZTRANSACTIONTYPE bt2 ON bt2.BIZTRANSACTIONTYPEID = l2.BIZTRANSACTIONTYPEID
    WHERE d2.EMPLOYEEID = emp.EMPLOYEEID
      AND d2.LEAVETYPEID = e.LEAVEID
      AND d2.LEAVEDATE < @leaveexpiredperiodfrom
      AND l2.STATUS IN (0,1)
) opening

OUTER APPLY (
    SELECT ISNULL(SUM(lvdtl.NUMBEROFDAYS),0) AS con
    FROM TLEAVEDETAIL lvdtl
    JOIN TLEAVE lv ON lv.LEAVEID = lvdtl.LEAVEID
    JOIN MLEAVE ml ON ml.LEAVEID = lvdtl.LEAVETYPEID
    WHERE lvdtl.EMPLOYEEID = emp.EMPLOYEEID
      AND lv.STATUS = 0
      AND lvdtl.LEAVEDATE BETWEEN @periodfrom AND @periodto
      AND ml.LEAVETYPE = {PAIDLEAVE}
      AND ml.LEAVEID = e.LEAVEID
) vv

LEFT JOIN #openingexpired1 openexp
       ON openexp.EMPLOYEEID = emp.EMPLOYEEID
      AND openexp.LEAVETYPEID = e.LEAVEID

LEFT JOIN #withindateexpired1 exp
       ON exp.EMPLOYEEID = emp.EMPLOYEEID
      AND exp.LEAVETYPEID = e.LEAVEID

WHERE emp.BASICON <> 4
  AND e.LEAVETYPE = {PAIDLEAVE}
  @replacefilter
";

        public static readonly string GET_LEAVE_STATUS_REPORT_WITH_OR_WITHOUT_DEBIT_CLASS_USED_IN_LEAVE_STATUS_REPORT_PG = $@"

SELECT
    emp.""employeeid""           AS ""EmployeeId"",
    emp.""employeecode""         AS ""EmployeeCode"",
    emp.""employeename""         AS ""EmployeeName"",
    org.""organizationunitcode"" AS ""OUCode"",
    org.""organizationunitname"" AS ""OUName"",

    COALESCE(MAX(opening.opening),0) AS ""Opening"",

    SUM(CASE
            WHEN bt.""biztransactionclassid"" = {LEAVECREDIT}
                 AND a.""status"" = 1
         THEN b.""numberofdays"" ELSE 0
        END) AS ""AllotedLeave"",

    SUM(COALESCE(openexp.""BeforeDateExpiredLeave"",0) +
        COALESCE(openexp.""LeaveDateCreditLeave"",0)) AS ""OpeningExpiredLeave"",

    SUM(COALESCE(exp.""BeforeDateExpiredLeave"",0) +
        COALESCE(exp.""LeaveDateCreditLeave"",0)) AS ""ExpiredLeave"",

    SUM(CASE
            WHEN bt.""biztransactionclassid"" = {LEAVEDEBIT}
         THEN b.""numberofdays"" ELSE 0
        END) AS ""DebitLeave"",

    SUM(CASE
            WHEN bt.""biztransactionclassid"" = {LEAVE}
             AND b.""leavedate"" <= @allotedtodateto
         THEN b.""numberofdays"" ELSE 0
        END) AS ""AvailedLeave"",

    SUM(CASE
            WHEN bt.""biztransactionclassid"" = {LEAVE}
             AND b.""leavedate"" > @allotedtodateto
         THEN b.""numberofdays"" ELSE 0
        END) AS ""AppliedLeave"",

    SUM(CASE
            WHEN bt.""biztransactionclassid""
                 IN ({LEAVEDEBIT},{LEAVE})
         THEN b.""numberofdays"" ELSE 0
        END) AS ""TakenLeave"",

    COALESCE(MAX(opening.opening),0)
    + SUM(CASE WHEN bt.""biztransactionclassid"" = {LEAVECREDIT}
               THEN b.""numberofdays"" ELSE 0 END)
    - SUM(CASE WHEN bt.""biztransactionclassid""
               IN ({LEAVEDEBIT},{LEAVE})
               THEN b.""numberofdays"" ELSE 0 END)
      AS ""LeaveBalance"",

    e.""leavecode"",
    e.""leavename"",
    e.""leavetype"",
    e.""leaveid"",
    d.""departmentid"",
    d.""departmentcode"",
    d.""departmentname""

FROM ""memployee"" emp
JOIN ""morganizationunit"" org ON emp.""workouid"" = org.""ouid""
JOIN ""mdepartment"" d ON emp.""departmentid"" = d.""departmentid""
JOIN ""mleave"" e ON e.""leavetype"" = {PAIDLEAVE}

LEFT JOIN ""tleavedetail"" b
    ON emp.""employeeid"" = b.""employeeid""
   AND e.""leaveid"" = b.""leavetypeid""
   AND b.""leavedate"" >= @leaveexpiredperiodfrom

LEFT JOIN ""tleave"" a
    ON a.""leaveid"" = b.""leaveid""
   AND a.""status"" IN (0,1)

@withorwithoutdebitclassfilter

LEFT JOIN LATERAL (
    SELECT SUM(
           CASE
             WHEN bt2.""biztransactionclassid"" = {LEAVECREDIT}
             THEN d2.""numberofdays""
             ELSE -d2.""numberofdays""
           END
    ) AS opening
    FROM ""tleavedetail"" d2
    JOIN ""tleave"" l2 ON l2.""leaveid"" = d2.""leaveid""
    JOIN ""mbiztransactiontype"" bt2
         ON bt2.""biztransactiontypeid"" = l2.""biztransactiontypeid""
    WHERE d2.""employeeid"" = emp.""employeeid""
      AND d2.""leavetypeid"" = e.""leaveid""
      AND d2.""leavedate"" < @leaveexpiredperiodfrom
      AND l2.""status"" IN (0,1)
) opening ON TRUE

LEFT JOIN ""openingexpired1"" openexp
    ON openexp.""EMPLOYEEID"" = emp.""employeeid""
   AND openexp.""LEAVETYPEID"" = e.""leaveid""

LEFT JOIN ""withindateexpired1"" exp
    ON exp.""EMPLOYEEID"" = emp.""employeeid""
   AND exp.""LEAVETYPEID"" = e.""leaveid""

WHERE emp.""basicon"" <> 4
  AND e.""leavetype"" = {PAIDLEAVE}
  @replacefilter

";
        public static readonly string WIHIN_DATE_EXPIRED_LEAVE = $@"
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY b.EMPLOYEEID, b.LEAVETYPEID
        ORDER BY b.LEAVEDATE
    ) AS SLNO,
    b.LEAVEDATE AS FROMDATE,
    b.VALIDTILL,
    b.EMPLOYEEID,
    b.LEAVETYPEID,

    CASE WHEN b.VALIDTILL < @leaveexpiredperiodto
         THEN b.NUMBEROFDAYS ELSE 0 END AS BeforeDateExpiredLeave,

    CASE WHEN @leaveexpiredperiodto BETWEEN b.LEAVEDATE AND b.VALIDTILL
         THEN b.NUMBEROFDAYS ELSE 0 END AS LeaveDateCreditLeave

INTO #withindateexpired
FROM TLEAVE l
JOIN TLEAVEDETAIL b ON l.LEAVEID = b.LEAVEID
JOIN MBIZTRANSACTIONTYPE c ON c.BIZTRANSACTIONTYPEID = l.BIZTRANSACTIONTYPEID
WHERE c.BIZTRANSACTIONCLASSID = {LEAVECREDIT}
  AND l.STATUS IN (0,1)
  {{leavereplace}}
  AND b.VALIDTILL >= @leaveexpiredperiodfrom
  AND b.VALIDTILL <  @leaveexpiredperiodto;

SELECT
    a.*,
    ISNULL(
        CASE WHEN a.VALIDTILL > DATEADD(DAY,-1,b.FROMDATE)
             THEN DATEADD(DAY,-1,b.FROMDATE)
             ELSE a.VALIDTILL END,
        a.VALIDTILL
    ) AS FINALVALIDTILL
INTO #withindateexpired1
FROM #withindateexpired a
LEFT JOIN #withindateexpired b
       ON a.EMPLOYEEID = b.EMPLOYEEID
      AND a.LEAVETYPEID = b.LEAVETYPEID
      AND b.SLNO = a.SLNO + 1;
";

        public static readonly string WIHIN_DATE_EXPIRED_LEAVE_PG = $@"

DROP TABLE IF EXISTS ""withindateexpired"";
DROP TABLE IF EXISTS ""withindateexpired1"";

CREATE TEMP TABLE ""withindateexpired"" AS
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY b.""employeeid"", b.""leavetypeid""
        ORDER BY b.""leavedate""
    ) AS ""SLNO"",
    b.""leavedate""   AS ""FROMDATE"",
    b.""validtill""   AS ""VALIDTILL"",
    b.""employeeid""  AS ""EMPLOYEEID"",
    b.""leavetypeid"" AS ""LEAVETYPEID"",
    CASE 
        WHEN b.""validtill"" < @leaveexpiredperiodto
        THEN b.""numberofdays"" ELSE 0 
    END AS ""BeforeDateExpiredLeave"",
    CASE 
        WHEN @leaveexpiredperiodto BETWEEN b.""leavedate"" AND b.""validtill""
        THEN b.""numberofdays"" ELSE 0 
    END AS ""LeaveDateCreditLeave""
FROM ""tleave"" l
JOIN ""tleavedetail"" b ON l.""leaveid"" = b.""leaveid""
JOIN ""mbiztransactiontype"" c ON c.""biztransactiontypeid"" = l.""biztransactiontypeid""
WHERE c.""biztransactionclassid"" = {LEAVECREDIT}
  AND l.""status"" IN (0,1)
  {{leavereplace}}
  AND b.""validtill"" >= @leaveexpiredperiodfrom
  AND b.""validtill"" < @leaveexpiredperiodto;

CREATE TEMP TABLE ""withindateexpired1"" AS
SELECT
    a.*,
    COALESCE(
        CASE 
            WHEN a.""VALIDTILL"" > (b.""FROMDATE"" - INTERVAL '1 day')
            THEN (b.""FROMDATE"" - INTERVAL '1 day')
            ELSE a.""VALIDTILL""
        END,
        a.""VALIDTILL""
    ) AS ""FINALVALIDTILL""
FROM ""withindateexpired"" a
LEFT JOIN ""withindateexpired"" b
       ON a.""EMPLOYEEID"" = b.""EMPLOYEEID""
      AND a.""LEAVETYPEID"" = b.""LEAVETYPEID""
      AND b.""SLNO"" = a.""SLNO"" + 1;
";
        public static readonly string OPENING_EXPIRED_LEAVE = $@"
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY b.EMPLOYEEID, b.LEAVETYPEID
        ORDER BY b.LEAVEDATE
    ) AS SLNO,
    b.LEAVEDATE AS FROMDATE,
    b.VALIDTILL,
    b.EMPLOYEEID,
    b.LEAVETYPEID,

    CASE WHEN b.VALIDTILL < @leaveexpiredperiodfrom
         THEN b.NUMBEROFDAYS ELSE 0 END AS BeforeDateExpiredLeave,

    CASE WHEN @leaveexpiredperiodfrom BETWEEN b.LEAVEDATE AND b.VALIDTILL
         THEN b.NUMBEROFDAYS ELSE 0 END AS LeaveDateCreditLeave

INTO #openingexpired
FROM TLEAVE l
JOIN TLEAVEDETAIL b ON l.LEAVEID = b.LEAVEID
JOIN MBIZTRANSACTIONTYPE c ON c.BIZTRANSACTIONTYPEID = l.BIZTRANSACTIONTYPEID
WHERE c.BIZTRANSACTIONCLASSID = {LEAVECREDIT}
  AND l.STATUS IN (0,1)
  {{leavereplace}}
  AND b.VALIDTILL < @leaveexpiredperiodfrom;

SELECT
    a.*,
    ISNULL(
        CASE WHEN a.VALIDTILL > DATEADD(DAY,-1,b.FROMDATE)
             THEN DATEADD(DAY,-1,b.FROMDATE)
             ELSE a.VALIDTILL END,
        a.VALIDTILL
    ) AS FINALVALIDTILL
INTO #openingexpired1
FROM #openingexpired a
LEFT JOIN #openingexpired b
       ON a.EMPLOYEEID = b.EMPLOYEEID
      AND a.LEAVETYPEID = b.LEAVETYPEID
      AND b.SLNO = a.SLNO + 1;
";

        public static readonly string OPENING_EXPIRED_LEAVE_PG = $@"

DROP TABLE IF EXISTS ""openingexpired"";
DROP TABLE IF EXISTS ""openingexpired1"";

CREATE TEMP TABLE ""openingexpired"" AS
SELECT
    ROW_NUMBER() OVER (
        PARTITION BY b.""employeeid"", b.""leavetypeid""
        ORDER BY b.""leavedate""
    ) AS ""SLNO"",
    b.""leavedate""   AS ""FROMDATE"",
    b.""validtill""   AS ""VALIDTILL"",
    b.""employeeid""  AS ""EMPLOYEEID"",
    b.""leavetypeid"" AS ""LEAVETYPEID"",
    CASE 
        WHEN b.""validtill"" < @leaveexpiredperiodfrom 
        THEN b.""numberofdays"" ELSE 0 
    END AS ""BeforeDateExpiredLeave"",
    CASE 
        WHEN @leaveexpiredperiodfrom BETWEEN b.""leavedate"" AND b.""validtill""
        THEN b.""numberofdays"" ELSE 0 
    END AS ""LeaveDateCreditLeave""
FROM ""tleave"" l
JOIN ""tleavedetail"" b ON l.""leaveid"" = b.""leaveid""
JOIN ""mbiztransactiontype"" c ON c.""biztransactiontypeid"" = l.""biztransactiontypeid""
WHERE c.""biztransactionclassid"" = {LEAVECREDIT}
  AND l.""status"" IN (0,1)
  {{leavereplace}}
  AND b.""validtill"" < @leaveexpiredperiodfrom;

CREATE TEMP TABLE ""openingexpired1"" AS
SELECT
    a.*,
    COALESCE(
        CASE 
            WHEN a.""VALIDTILL"" > (b.""FROMDATE"" - INTERVAL '1 day')
            THEN (b.""FROMDATE"" - INTERVAL '1 day')
            ELSE a.""VALIDTILL""
        END,
        a.""VALIDTILL""
    ) AS ""FINALVALIDTILL""
FROM ""openingexpired"" a
LEFT JOIN ""openingexpired"" b
       ON a.""EMPLOYEEID"" = b.""EMPLOYEEID""
      AND a.""LEAVETYPEID"" = b.""LEAVETYPEID""
      AND b.""SLNO"" = a.""SLNO"" + 1;
";
        public const string GET_LEAVE_STATUS_REPORT_GROUP_BY = @"
                                GROUP BY
                                    emp.EMPLOYEEID,
                                    emp.EMPLOYEECODE,
                                    emp.EMPLOYEENAME,
                                    E.LeaveCode,
                                    E.LEAVENAME,
                                    E.LEAVETYPE,
                                    E.LEAVEID,
                                    org.ORGANIZATIONUNITCODE,
                                    org.ORGANIZATIONUNITNAME,
                                    d.DEPARTMENTID,
                                    d.DEPARTMENTCODE,
                                    d.DEPARTMENTNAME
                            ";
        public const string GET_LEAVE_STATUS_REPORT_GROUP_BY_PG = @"
                        group by
                            emp.""employeeid"",
                            emp.""employeecode"",
                            emp.""employeename"",
                            e.""leavecode"",
                            e.""leavename"",
                            e.""leavetype"",
                            e.""leaveid"",
                            org.""organizationunitcode"",
                            org.""organizationunitname"",
                            d.""departmentid"",
                            d.""departmentcode"",
                            d.""departmentname""
                        ";
        public const string PAY_REVISON_EFFECTIVE_FROM = @"
                        SELECT
                            payrevision.EmployeeId      AS EmployeeId,
                            payrevision.EffectiveFrom    AS PayRevisionEffectiveFrom,
                            payrevision.EffectiveTo      AS PayRevisionEffectiveTo
                        FROM
                            tPayRevision payrevision
                        WHERE
                            1 = 1
                            -- AND payrevision.EmployeeId = @employeeid
                            AND (payrevision.OUId = @ouid OR @ouid = -1)
                            AND payrevision.PayConfigurationId = @payconfigid
                            AND payrevision.EffectiveFrom <=@fromdate
                            AND payrevision.EffectiveTo   >= @todate
                            AND payrevision.Nature = 0
                        ORDER BY
                            payrevision.EffectiveFrom DESC
                        ";

        public const string PAY_REVISON_EFFECTIVE_FROM_PG = @"
                        SELECT
                            payrevision.""employeeid""        AS ""EmployeeId"",
                            payrevision.""effectivefrom""     AS ""PayRevisionEffectiveFrom"",
                            payrevision.""effectiveto""       AS ""PayRevisionEffectiveTo""
                        FROM ""tpayrevision"" payrevision
                        WHERE
                              (payrevision.""ouid"" = @ouid OR @ouid = -1)
                          AND payrevision.""payconfigurationid"" = @payconfigid
                          AND payrevision.""effectivefrom"" <= @fromdate
                          AND payrevision.""effectiveto""   >= @todate
                          AND payrevision.""nature"" = 0
                        ORDER BY
                            payrevision.""effectivefrom"" DESC
                        ";


        public const string ALL_PAYPROCSS_FIELDS = @"
                        SELECT
                            a.name AS DBObjectFieldsName
                        FROM
                            sys.columns a,
                            sys.tables  b
                        WHERE
                            a.object_id = b.object_id
                            AND b.name = b.name
                            AND b.name = 'Tpayprocessaddon'
                            AND a.name <> 'PAYPROCESSID'
                        ";
        public const string ALL_PAYPROCSS_FIELDS_PG = @"
                    SELECT
                        column_name AS ""DBObjectFieldsName""
                    FROM information_schema.columns
                    WHERE table_name = 'tpayprocessaddon'
                      AND column_name <> 'payprocessid'
                    ORDER BY ordinal_position
                    ";
        public const string CREATE_TEMP_PAYPROCESS_ADDON = @"
        IF OBJECT_ID('tempdb..#TPAYPROCESSADDON') IS NOT NULL
            DROP TABLE #TPAYPROCESSADDON;

        CREATE TABLE #TPAYPROCESSADDON
        (
            PayProcessId BIGINT,
            SlNo INT,
            PaySlipStatus INT
        );
    ";

        public const string CREATE_TEMP_PAYPROCESS_ADDON_PG = @"
                DROP TABLE IF EXISTS ""TPAYPROCESSADDON_TEMP"";

                CREATE TEMP TABLE ""TPAYPROCESSADDON_TEMP""
                (
                    ""PayProcessId""   BIGINT,
                    ""SlNo""           INT,
                    ""PaySlipStatus""  INT
                ) ON COMMIT PRESERVE ROWS;
                ";

        public const string INSERT_INTO_TABLE = @"
        INSERT INTO #TPAYPROCESSADDON (PayProcessId, SlNo, PaySlipStatus)
        VALUES (:payprocessId, :slno, :payslipstatus);
    ";
        public const string INSERT_INTO_TABLE_PG = @"
                INSERT INTO ""TPAYPROCESSADDON_TEMP"" 
                    (""PayProcessId"", ""SlNo"", ""PaySlipStatus"")
                VALUES 
                    (:payprocessId, :slno, :payslipstatus);
                ";
        public const string GET_DETAIL_FROM_PAYPROCESS = @"
        SELECT 
            a.PayProcessId
            :fields
        FROM TPayProcessAddon a
        INNER JOIN #TPAYPROCESSADDON b
            ON a.PayProcessId = b.PayProcessId
        ORDER BY b.SlNo
    ";
        public const string GET_DETAIL_FROM_PAYPROCESS_PG = @"
SELECT 
    a.""payprocessid""
    :fields
FROM ""tpayprocessaddon"" a
INNER JOIN ""TPAYPROCESSADDON_TEMP"" b
    ON a.""payprocessid"" = b.""PayProcessId""
ORDER BY b.""SlNo""
";
        public static readonly string GET_LEAVETYPE = $@"SELECT
                                    LEAVEID AS LeaveId,
                                    LEAVECODE AS LeaveCode,
                                    LEAVENAME AS LeaveName,
                                    ISENCASHABLE AS LeaveIsEncashable,
                                    ISBALANCEMAINTAINED AS LeaveIsBalanceMaintained,
                                    ISCARRIEDOVER AS LeaveIsCarriedOver,
                                    CREDITMETHOD AS LeaveCreditMethod,
                                    LEAVETYPE AS LeaveLeaveType,
                                    LEAVESALARYPERCENT AS LeaveLeaveSalaryPercent,
                                    INCLUDEHOLIDAY AS LeaveIncludeHoliday,
                                    SURROUNDINGDAY AS LeaveSurroundingDay,
                                    SORTORDER AS LeaveSortOrder,
                                    STATUS AS LeaveStatus,
                                    VERSION AS LeaveVersion,
                                    CREATEDBYID AS LeaveCreatedById,
                                    CREATEDON AS LeaveCreatedOn,
                                    MODIFIEDBYID AS LeaveModifiedById,
                                    MODIFIEDON AS LeaveModifiedOn,
                                    SOURCETYPE AS LeaveSourceType,
                                    TENANTID AS LeaveTenantId,
                                    LEAVECREDITFORMULAID AS LeaveCreditFormulaId,
                                    ISAPPLICABLEFORLEAVEAPPLICATION AS LeaveIsApplicable,
                                    LEAVESALARYFORMULAID AS LeaveSalaryFormulaId,
                                    DISPLAYCODE AS LeaveDisplayCode,
                                    ISTRACKINGREQUIRED AS LeaveIsTrackingRequired,
                                    LTALEAVEIDS AS LeaveLTALeaveIds,
                                    VALIDPERIOD AS LeaveValidPeriod
                                FROM MLEAVE
                                WHERE LEAVETYPE = {PAIDLEAVE};";

        public static readonly string GET_LEAVETYPE_PG = $@"
                                SELECT 
                                    LEAVEID AS ""LeaveId"",
                                    LEAVECODE AS ""LeaveCode"",
                                    LEAVENAME AS ""LeaveName"",
                                    ISENCASHABLE AS ""LeaveIsEncashable"",
                                    ISBALANCEMAINTAINED AS ""LeaveIsBalanceMaintained"",
                                    ISCARRIEDOVER AS ""LeaveIsCarriedOver"",
                                    CREDITMETHOD AS ""LeaveCreditMethod"",
                                    LEAVETYPE AS ""LeaveLeaveType"",
                                    LEAVESALARYPERCENT AS ""LeaveLeaveSalaryPercent"",
                                    INCLUDEHOLIDAY AS ""LeaveIncludeHoliday"",
                                    SURROUNDINGDAY AS ""LeaveSurroundingDay"",
                                    SORTORDER AS ""LeaveSortOrder"",
                                    STATUS AS ""LeaveStatus"",
                                    VERSION AS ""LeaveVersion"",
                                    CREATEDBYID AS ""LeaveCreatedById"",
                                    CREATEDON AS ""LeaveCreatedOn"",
                                    MODIFIEDBYID AS ""LeaveModifiedById"",
                                    MODIFIEDON AS ""LeaveModifiedOn"",
                                    SOURCETYPE AS ""LeaveSourceType"",
                                    TENANTID AS ""LeaveTenantId"",
                                    LEAVECREDITFORMULAID AS ""LeaveCreditFormulaId"",
                                    ISAPPLICABLEFORLEAVEAPPLICATION AS ""LeaveIsApplicable"",
                                    LEAVESALARYFORMULAID AS ""LeaveSalaryFormulaId"",
                                    DISPLAYCODE AS ""LeaveDisplayCode"",
                                    ISTRACKINGREQUIRED AS ""LeaveIsTrackingRequired"",
                                    LTALEAVEIDS AS ""LeaveLTALeaveIds"",
                                    VALIDPERIOD AS ""LeaveValidPeriod""
                                FROM MLEAVE
                                WHERE LEAVETYPE = {PAIDLEAVE};
                                ";

        public const string GET_YEAR_START_AND_END_DATE = @"
        SELECT 
            DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS PeriodFromDate,
            DATEADD(dd, -1, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS PeriodToDate
        FROM MPERIOD
        WHERE PERIODID = @periodid";


        public static readonly string GET_LEAVE_STATUS_REPORT = $@"
SELECT
    emp.EMPLOYEEID               AS EmployeeId,
    emp.EMPLOYEECODE             AS EmployeeCode,
    emp.EMPLOYEENAME             AS EmployeeName,
    org.ORGANIZATIONUNITCODE     AS OUCode,
    org.ORGANIZATIONUNITNAME     AS OUName,

    ISNULL(MAX(opening.opening), 0) AS Opening,

    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVECREDIT}
             AND a.STATUS = 1
        THEN b.NUMBEROFDAYS ELSE 0 END) AS AllotedLeave,

    ISNULL(MAX(openexp.BeforeDateExpiredLeave),0)
    + ISNULL(MAX(openexp.LeaveDateCreditLeave),0) AS OpeningExpiredLeave,

    ISNULL(MAX(exp.BeforeDateExpiredLeave),0)
    + ISNULL(MAX(exp.LeaveDateCreditLeave),0) AS ExpiredLeave,

    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVEDEBIT}
        THEN b.NUMBEROFDAYS ELSE 0 END) AS DebitLeave,

    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVE}
             AND b.LEAVEDATE <= @AllotedToDateTo
        THEN b.NUMBEROFDAYS ELSE 0 END) AS AvailedLeave,

    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID = {LEAVE}
             AND b.LEAVEDATE > @AllotedToDateTo
        THEN b.NUMBEROFDAYS ELSE 0 END) AS AppliedLeave,

    SUM(CASE
            WHEN bt.BIZTRANSACTIONCLASSID IN ({LEAVEDEBIT},{LEAVE})
        THEN b.NUMBEROFDAYS ELSE 0 END) AS TakenLeave,

    ISNULL(MAX(opening.opening),0)
    + SUM(CASE WHEN bt.BIZTRANSACTIONCLASSID = {LEAVECREDIT} THEN b.NUMBEROFDAYS ELSE 0 END)
    - SUM(CASE WHEN bt.BIZTRANSACTIONCLASSID IN ({LEAVEDEBIT},{LEAVE}) THEN b.NUMBEROFDAYS ELSE 0 END)
    AS LeaveBalance,

    e.LEAVECODE,
    e.LEAVENAME,
    e.LEAVETYPE,
    e.LEAVEID,

    ISNULL(MAX(approval.ApprovalPending),0) AS ApprovalPending,

    d.DEPARTMENTID,
    d.DEPARTMENTCODE,
    d.DEPARTMENTNAME

FROM MEMPLOYEE emp
JOIN MORGANIZATIONUNIT org 
    ON emp.WorkOUID = org.OUID

JOIN MDEPARTMENT d 
    ON emp.DEPARTMENTID = d.DEPARTMENTID

JOIN MLEAVE e
    ON e.LEAVETYPE = {PAIDLEAVE}

LEFT JOIN TLEAVEDETAIL b
    ON emp.EMPLOYEEID = b.EMPLOYEEID
   AND e.LEAVEID = b.LEAVETYPEID
   AND b.LEAVEDATE >= @LeaveExpiredPeriodFrom
   AND b.LEAVEDATE <= DATEADD(YEAR, DATEDIFF(YEAR,0,@CutOffDate)+1,-1)

LEFT JOIN TLEAVE a
    ON a.LEAVEID = b.LEAVEID
   AND a.STATUS IN (0,1)

LEFT JOIN MBIZTRANSACTIONTYPE bt
    ON bt.BIZTRANSACTIONTYPEID = a.BIZTRANSACTIONTYPEID
   @DEBITCLASSFILTER

/* 🔹 Opening Balance */
OUTER APPLY (
    SELECT
        SUM(CASE
                WHEN bt2.BIZTRANSACTIONCLASSID = {LEAVECREDIT}
                THEN d2.NUMBEROFDAYS
                ELSE -d2.NUMBEROFDAYS
            END) AS opening
    FROM TLEAVEDETAIL d2
    JOIN TLEAVE l2 ON l2.LEAVEID = d2.LEAVEID
    JOIN MBIZTRANSACTIONTYPE bt2
        ON bt2.BIZTRANSACTIONTYPEID = l2.BIZTRANSACTIONTYPEID
    WHERE d2.EMPLOYEEID = emp.EMPLOYEEID
      AND d2.LEAVETYPEID = e.LEAVEID
      AND d2.LEAVEDATE < @LeaveExpiredPeriodFrom
      AND l2.STATUS IN (0,1)
) opening

/* 🔹 Opening Expired */
OUTER APPLY (
    SELECT
        SUM(CASE WHEN bt3.BIZTRANSACTIONCLASSID = {LEAVECREDIT} THEN d3.NUMBEROFDAYS ELSE 0 END) AS BeforeDateExpiredLeave,
        SUM(CASE WHEN bt3.BIZTRANSACTIONCLASSID = {LEAVE} THEN d3.NUMBEROFDAYS ELSE 0 END) AS LeaveDateCreditLeave
    FROM TLEAVEDETAIL d3
    JOIN TLEAVE l3 ON l3.LEAVEID = d3.LEAVEID
    JOIN MBIZTRANSACTIONTYPE bt3
        ON bt3.BIZTRANSACTIONTYPEID = l3.BIZTRANSACTIONTYPEID
    WHERE d3.EMPLOYEEID = emp.EMPLOYEEID
      AND d3.LEAVETYPEID = e.LEAVEID
      AND d3.LEAVEDATE < @LeaveExpiredPeriodFrom
      AND l3.STATUS IN (0,1)
) openexp

/* 🔹 Within Period Expired */
OUTER APPLY (
    SELECT
        SUM(CASE WHEN bt4.BIZTRANSACTIONCLASSID = {LEAVECREDIT} THEN d4.NUMBEROFDAYS ELSE 0 END) AS BeforeDateExpiredLeave,
        SUM(CASE WHEN bt4.BIZTRANSACTIONCLASSID = {LEAVE} THEN d4.NUMBEROFDAYS ELSE 0 END) AS LeaveDateCreditLeave
    FROM TLEAVEDETAIL d4
    JOIN TLEAVE l4 ON l4.LEAVEID = d4.LEAVEID
    JOIN MBIZTRANSACTIONTYPE bt4 
        ON bt4.BIZTRANSACTIONTYPEID = l4.BIZTRANSACTIONTYPEID
    WHERE d4.EMPLOYEEID = emp.EMPLOYEEID
      AND d4.LEAVETYPEID = e.LEAVEID
      AND d4.LEAVEDATE BETWEEN @LeaveExpiredPeriodFrom AND @LeaveExpiredPeriodTo
      AND l4.STATUS IN (0,1)
) exp

/* 🔹 Approval Pending */
OUTER APPLY (
    SELECT 
        ISNULL(SUM(lvdtl.NUMBEROFDAYS),0) AS ApprovalPending
    FROM TLEAVEDETAIL lvdtl
    JOIN TLEAVE lv ON lv.LEAVEID = lvdtl.LEAVEID
    WHERE lvdtl.EMPLOYEEID = emp.EMPLOYEEID
      AND lv.STATUS = 0
      AND lvdtl.LEAVEDATE BETWEEN @PeriodFrom AND @PeriodTo
      AND lvdtl.LEAVETYPEID = e.LEAVEID
) approval

WHERE emp.BASICON <> 4
@EMPLOYEEFILTER
@OUFILTER
@EXTRAFILTERS
GROUP BY
    emp.EMPLOYEEID,
    emp.EMPLOYEECODE,
    emp.EMPLOYEENAME,
    e.LEAVECODE,
    e.LEAVENAME,
    e.LEAVETYPE,
    e.LEAVEID,
    org.ORGANIZATIONUNITCODE,
    org.ORGANIZATIONUNITNAME,
    d.DEPARTMENTID,
    d.DEPARTMENTCODE,
    d.DEPARTMENTNAME;
";

        // ── PayProcess/AdditionDeductionPicklist (paged, dual dialect) ──────
        // GB4 parity: PayProcessBLL.GetAdditionDeductionPicklist → PayProcessQueryBuilder.GET_ADDITION_DEDUCTION_PICKLIST

        public const string GET_ADDITION_DEDUCTION_PICKLIST_SQL = @"
            WITH Paged AS (
                SELECT DISTINCT
                       B.FIELDID     AS Id,
                       B.DISPLAYCODE AS Code,
                       B.DISPLAYNAME AS Name,
                       ROW_NUMBER() OVER (ORDER BY B.DISPLAYNAME) AS RowNum
                FROM   TADDITIONDEDUCTIONDETAIL A
                LEFT OUTER JOIN MADDITIONDEDUCTION B ON A.FIELDCODE = B.DISPLAYCODE
                WHERE  A.FIELDCODE <> 'NONE'
                AND    (@id IS NULL OR CAST(B.FIELDID AS VARCHAR(50)) LIKE @id)
                AND    (@searchtext IS NULL OR B.DISPLAYCODE LIKE @searchtext OR B.DISPLAYNAME LIKE @searchtext)
            )
            SELECT Id, Code, Name
            FROM   Paged
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_ADDITION_DEDUCTION_PICKLIST_PG = @"
            WITH Paged AS (
                SELECT DISTINCT
                       B.""FIELDID""     AS ""Id"",
                       B.""DISPLAYCODE"" AS ""Code"",
                       B.""DISPLAYNAME"" AS ""Name"",
                       ROW_NUMBER() OVER (ORDER BY B.""DISPLAYNAME"") AS ""RowNum""
                FROM   ""TADDITIONDEDUCTIONDETAIL"" A
                LEFT OUTER JOIN ""MADDITIONDEDUCTION"" B ON A.""FIELDCODE"" = B.""DISPLAYCODE""
                WHERE  A.""FIELDCODE"" <> 'NONE'
                AND    (:id IS NULL OR CAST(B.""FIELDID"" AS VARCHAR(50)) LIKE :id)
                AND    (:searchtext IS NULL OR B.""DISPLAYCODE"" LIKE :searchtext OR B.""DISPLAYNAME"" LIKE :searchtext)
            )
            SELECT ""Id"", ""Code"", ""Name""
            FROM   Paged
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_ADDITION_DEDUCTION_PICKLIST_COUNT_SQL = @"
            SELECT COUNT(DISTINCT B.FIELDID)
            FROM   TADDITIONDEDUCTIONDETAIL A
            LEFT OUTER JOIN MADDITIONDEDUCTION B ON A.FIELDCODE = B.DISPLAYCODE
            WHERE  A.FIELDCODE <> 'NONE'
            AND    (@id IS NULL OR CAST(B.FIELDID AS VARCHAR(50)) LIKE @id)
            AND    (@searchtext IS NULL OR B.DISPLAYCODE LIKE @searchtext OR B.DISPLAYNAME LIKE @searchtext);";

        public const string GET_ADDITION_DEDUCTION_PICKLIST_COUNT_PG = @"
            SELECT COUNT(DISTINCT B.""FIELDID"")
            FROM   ""TADDITIONDEDUCTIONDETAIL"" A
            LEFT OUTER JOIN ""MADDITIONDEDUCTION"" B ON A.""FIELDCODE"" = B.""DISPLAYCODE""
            WHERE  A.""FIELDCODE"" <> 'NONE'
            AND    (:id IS NULL OR CAST(B.""FIELDID"" AS VARCHAR(50)) LIKE :id)
            AND    (:searchtext IS NULL OR B.""DISPLAYCODE"" LIKE :searchtext OR B.""DISPLAYNAME"" LIKE :searchtext);";

        // ── PayProcess/Addon/Selectlist (dual dialect) ──────────────────────
        // GB4 parity: PayProcessBLL.PayprocessAddonPicklist — distinct field codes from a
        // PayConfiguration's detail rows; Name is prefixed F_/A_ for Formula/Advance field types.

        public const string GET_PAYPROCESS_ADDON_PICKLIST_SQL = @"
            SELECT DISTINCT
                   D.FIELDCODE AS Code,
                   CASE D.FIELDTYPE
                       WHEN 1 THEN 'F_' + D.FIELDCODE
                       WHEN 3 THEN 'A_' + D.FIELDCODE
                       ELSE D.FIELDCODE
                   END AS Name
            FROM   MPAYCONFIGURATIONDETAIL D
            WHERE  D.PAYCONFIGURATIONID = @payconfigid
            ORDER BY Code;";

        public const string GET_PAYPROCESS_ADDON_PICKLIST_PG = @"
            SELECT DISTINCT
                   D.""FIELDCODE"" AS ""Code"",
                   CASE D.""FIELDTYPE""
                       WHEN 1 THEN 'F_' || D.""FIELDCODE""
                       WHEN 3 THEN 'A_' || D.""FIELDCODE""
                       ELSE D.""FIELDCODE""
                   END AS ""Name""
            FROM   ""MPAYCONFIGURATIONDETAIL"" D
            WHERE  D.""PAYCONFIGURATIONID"" = :payconfigid
            ORDER BY ""Code"";";

        // ── PayPeriod/SelectList/Descending (paged) ─────────────────────────
        // GB4 parity: PayPeriodBLL.GetSelectListPayPeriodDescendingOrder →
        // GetGenericCriteriaLoadFieldsOrderBy<Poco.PayPeriod>("Id,Code,Name,DisplayName,SortOrder", "FromDate_desc", ...)

        public const string GET_SELECT_LIST_PAYPERIOD_DESCENDING = @"
WITH OrderedPayPeriods AS
(
    SELECT
        PAYPERIODID AS Id,
        PAYPERIODCODE AS Code,
        PAYPERIODNAME AS Name,
        DISPLAYNAME AS DisplayName,
        SORTORDER AS SortOrder,
        ROW_NUMBER() OVER (ORDER BY FROMDATE DESC) AS RowNum
    FROM MPAYPERIOD
    WHERE
        PAYPERIODID <> -1
        AND (@Code IS NULL OR PAYPERIODCODE LIKE '%' + @Code + '%')
        AND (@Name IS NULL OR PAYPERIODNAME LIKE '%' + @Name + '%')
        AND (@DisplayName IS NULL OR DISPLAYNAME LIKE '%' + @DisplayName + '%')
)
SELECT
    Id, Code, Name, DisplayName, SortOrder
FROM OrderedPayPeriods
WHERE
    (@Offset = -1 AND @PageSize = -1)
    OR
    (RowNum BETWEEN @Offset + 1 AND @Offset + @PageSize)
ORDER BY RowNum;
";

        // ── PayPeriod/SelectList/PayPeriod/ApplicableOu/DependMultiPayConfig ─
        // GB4 parity: PayPeriodBLL.GetSelectListPayPeriodApplicableOuDependMultiPayConfig —
        // matches when the period's comma-separated APPLICABLEPAYCONFIGIDS list overlaps the
        // caller-supplied comma-separated PayConfigurationId list (GB4 used comma2row/CROSS APPLY;
        // translated here to STRING_SPLIT).

        public const string GET_SELECT_LIST_PAYPERIOD_DEPENDS_MULTI_PAYCONFIG = @"
WITH Matched AS (
    SELECT
        A.PAYPERIODID AS Id,
        A.PAYPERIODCODE AS Code,
        A.PAYPERIODNAME AS Name,
        A.APPLICABLEOUIDS AS ApplicableOuIds,
        A.APPLICABLEPAYCONFIGIDS AS ApplicablePayConfigIds,
        A.DISPLAYNAME AS DisplayName,
        ROW_NUMBER() OVER (ORDER BY A.FROMDATE DESC, A.PERIODTYPE) AS RowNum
    FROM MPAYPERIOD A
    WHERE
        A.PAYPERIODID <> -1
        AND A.ISVISIBLE = 0
        AND (A.APPLICABLEOUIDS = '-1' OR A.APPLICABLEOUIDS LIKE '%' + CAST(@OuId AS VARCHAR(50)) + '%')
        AND EXISTS (
            SELECT 1
            FROM STRING_SPLIT(A.APPLICABLEPAYCONFIGIDS, ',') PC
            INNER JOIN STRING_SPLIT(@PayConfigurationIds, ',') REQ
                ON LTRIM(RTRIM(PC.value)) = LTRIM(RTRIM(REQ.value))
        )
        AND (@Code IS NULL OR A.PAYPERIODCODE LIKE '%' + @Code + '%')
        AND (@Name IS NULL OR A.PAYPERIODNAME LIKE '%' + @Name + '%')
        AND (@DisplayName IS NULL OR A.DISPLAYNAME LIKE '%' + @DisplayName + '%')
)
SELECT Id, Code, Name, ApplicableOuIds, ApplicablePayConfigIds, DisplayName
FROM Matched
WHERE
    (@Offset = -1 AND @PageSize = -1)
    OR
    (RowNum BETWEEN @Offset + 1 AND @Offset + @PageSize)
ORDER BY RowNum;
";

        // ════════════════════════════════════════════════════════════════
        // PayProcessForSepration (GB4 spelling preserved in comments/route;
        // GB5 method named PayProcessForSeparation) — GB4 PayProcessBLL.cs
        // lines 20327-21108, PayProcessQueryBuilder.cs lines 10444-10880.
        //
        // SEPARATION_PAY_PERIOD_ID = -4 is a literal sentinel GB4 hardcodes
        // into TPAYPROCESS.PAYPERIODID / TPAYPROCESSADDON's parent row for
        // every separation-originated pay-process record, decoupled from the
        // employee's real PayPeriodId (used only for date/context lookups).
        // ════════════════════════════════════════════════════════════════

        public const int SEPARATION_PAY_PERIOD_ID = -4;

        // GB4: TOTAL_NUMBER_OF_RECORDS_COUNT_WITH_FOR_SEPARATION
        public const string SEPARATION_DUPLICATE_CHECK_COUNT = @"
SELECT COUNT(1) FROM TPAYPROCESS
WHERE OUID = @OuId
  AND PAYPERIODID = @PayPeriodId
  AND PAYCONFIGID = @PayConfigurationId
  AND EMPLOYEEID = @EmployeeId;";

        // GB4: UNDO_TPAYPROCESSADDON_SEPARATION
        public const string SEPARATION_UNDO_PAYPROCESSADDON = @"
DELETE FROM TPAYPROCESSADDON
WHERE PAYPROCESSID IN (
    SELECT A.PAYPROCESSID FROM TPAYPROCESS A
    WHERE A.OUID = @OuId
      AND A.PAYPERIODID = @PayPeriodId
      AND A.PAYCONFIGID = @PayConfigurationId
      AND A.EMPLOYEEID = @EmployeeId
);";

        // GB4: PAY_PROCESSID_TO_MINUS_1_IN_TRELIEVINGSETTLEMENT
        // Reuses TRELIEVINGSETTLEMENT / PAYPROCESSID naming already established
        // by the completed SaveRelievingSettlement migration (RelievingQB.cs).
        public const string SEPARATION_UNLINK_RELIEVINGSETTLEMENT = @"
UPDATE TRELIEVINGSETTLEMENT SET PAYPROCESSID = -1
WHERE PAYPROCESSID IN (
    SELECT A.PAYPROCESSID FROM TPAYPROCESS A
    WHERE A.OUID = @OuId
      AND A.PAYPERIODID = @PayPeriodId
      AND A.PAYCONFIGID = @PayConfigurationId
      AND A.EMPLOYEEID = @EmployeeId
);";

        // GB4: UNDO_TPAYPROCES_SEPARATION
        public const string SEPARATION_UNDO_PAYPROCESS = @"
DELETE FROM TPAYPROCESS
WHERE PAYPROCESSID IN (
    SELECT A.PAYPROCESSID FROM TPAYPROCESS A
    WHERE A.OUID = @OuId
      AND A.PAYPERIODID = @PayPeriodId
      AND A.PAYCONFIGID = @PayConfigurationId
      AND A.EMPLOYEEID = @EmployeeId
);";

        // GB4: PAYPROCESS_INSERT_QUERY_FIELDS_SEPARATION_SQL + PAYPROCESS_INSERT_QUERY_FOR_SEPARATION_SQL
        // combined into one parameterized statement. Note the literal -4 written into
        // PayPeriodId in the SELECT list (SEPARATION_PAY_PERIOD_ID), while @PayPeriodId
        // (the real period) is used only to join MPayPeriod / TPAYREVISION for date context.
        // GB4 hardcodes ACTUALPRESENTDAYS=26, PREVIOUSWEEKLYOFFDAYS=4 (comment: "As Per Arul
        // sir on 30 July 2022") — preserved verbatim below.
        public const string SEPARATION_INSERT_PAYPROCESS_HEADER = @"
INSERT INTO TPAYPROCESS
(PAYPROCESSID, EMPLOYEEID, PAYPERIODID, PAYCONFIGID, OUID, TOTALDAYS, WORKINGDAYS, PRESENTDAYS, PAYFORDAYS,
 WORKEDDAYS, TOTALWORKEDDAYS, WEEKLYOFFDAYS, TOTALWEEKLYOFFDAYS, HOLIDAYS, TOTALHOLIDAYS, LEAVEDAYS, TOTALLEAVEDAYS,
 ABSENTDAYS, TOTALABSENTDAYS, PAIDDAYS, WORKEDHOURS, TOTALWORKEDHOURS, LEAVEHOURS, PAIDHOURS, OFFICIALPERMISSIONHOURS, PERSONALPERMISSIONHOURS,
 EARLYIN, EARLYOUT, LATEIN, LATEOUT, SHORTAGEHOURS, EXTRAHOURS, GRACEHOURS, TOTALGRACEHOURS, ACTUALHOURS, OTHOURS,
 OTFACTORHOURS, COMPENSATORYWORK, TOTALCOMPENSATORYWORK, COMPENSATORYLEAVE, TOTALCOMPENSATORYLEAVE, PAYMENTBATCH, PAIDON, PAIDBY, PAYSLIPSTATUS, REASON, REMARKS, PAYMODE,
 CONTRACTDETAILID, WAGERULEID, RULEID, WORKTYPEID, PFEMPLOYERPERCENTAGE, PFEMPLOYERACTUALPERCENTAGE, FPFEMPLOYERPERCENTAGE,
 FPFEMPLOYERACTUALPERCENTAGE, DLIPERCENTAGE, PFEMPLOYEEPERCENTAGE, PFEMPLOYEEACTUALPERCENTAGE, FPFEMPLOYEEACTUALPERCENTAGE, ESIEMPLOYERPERCENTAGE, ESIEMPLOYEEPERCENTAGE,
 KMETERS, EMPLOYEEKMCHARGES, EMPLOYERKMCHARGES, KMCHARGES, TOTALEARNINGS, TOTALDEDUCTIONS, NETSALARY, EMPLOYEEACCOUNTID,
 CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, STATUS, VERSION, ISPFAPPLICABLE, ISESIAPPLICABLE, PIHOURS, MMDETAILID, ACTUALPRESENTDAYS, PREVIOUSWEEKLYOFFDAYS)
SELECT
    (@StartNumber + ROW_NUMBER() OVER (ORDER BY EMPLOYEE.EMPLOYEEID)) AS Id,
    EMPLOYEE.EMPLOYEEID AS EmployeeId,
    -4 AS PayPeriodId,
    @PayConfigurationId AS PayConfigurationId,
    @OuId AS OUID,
    @TotalDays AS TotalDays, @NumberOfDays AS WorkingDays, @NumberOfDays AS PresentDays, PAYPERIOD.PAYFORDAYS,
    @NumberOfDays AS WorkedDays, @NumberOfDays AS TotalWorkedDays,
    @WeeklyOff AS WeeklyOffDays, 0 AS TotalWeeklyOffDays,
    0 AS Holidays, 0 AS TotalHolidays,
    0 AS LeaveDays, 0 AS TotalLeaveDays,
    0 AS AbsentDays, 0 AS TotalAbsentDays,
    0 AS PaidDays, 0 AS WorkedHours, 0 AS TotalWorkedHours, 0 AS LeaveHours,
    0 AS PaidHours, 0 AS OfficialPermissionHours, 0 AS PersonalPermissionHours,
    0 AS EarlyIn, 0 AS EarlyOut, 0 AS LateIn, 0 AS LateOut,
    0 AS ShortageHours, 0 AS ExtraHours,
    0 AS GraceHours, 0 AS TotalGraceHours,
    0.00 AS ActualHours, 0.00 AS OtHours, 0.00 AS OtFactorHours,
    0.00 AS CompensatoryWork,
    0 AS TotalCompensatoryWork, 0 AS CompensatoryLeave, 0 AS TotalCompensatoryLeave,
    0.00 AS PaymentBatch, CONVERT(DATETIME, '1899-01-01 00:00:00', 20) AS PaidOn, -1 AS PaidBy, 0 AS PaySlipStatus, 'NONE' AS Reason, '' AS Remarks,
    0 AS PayMode, -1 AS ContractDetailId, -1 AS WageRuleId, -1 AS RuleId,
    EMPLOYEE.WORKTYPEID AS WorkTypeId,
    0 AS PfEmployerPercentage, EMPLOYEE.EMPLOYERPFPERCENTAGE AS PfEmployerActualPercentage,
    0.00 AS FpfEmployerPercentage, 0.00 AS FpfEmployerActualPercentage,
    0.00 AS DliPercentage,
    0 AS PfEmployeePercentage, EMPLOYEE.EMPLOYEEPFPERCENTAGE AS PfEmployeeActualPercentage,
    0.00 AS FpfEmployeeActualPercentage,
    EMPLOYEE.EMPLOYERESIPERCENTAGE AS EsiEmployerPercentage,
    EMPLOYEE.EMPLOYEEESIPERCENTAGE AS EsiEmployeePercentage,
    0.00 AS KmEters, 0.00 AS EmployeeKmCharges, 0.00 AS EmployerKmCharges, 0.00 AS KmCharges, 0.00 AS TotalEarnings, 0.00 AS TotalDeductions,
    0.00 AS NetSalary, EMPLOYEE.ACCOUNTID AS EmployeeAccountId,
    EMPLOYEE.CREATEDBYID AS CreatedById, GETDATE() AS CreatedOn, EMPLOYEE.MODIFIEDBYID AS ModifiedById,
    GETDATE() AS ModifiedOn, EMPLOYEE.STATUS AS Status, EMPLOYEE.VERSION AS Version, EMPLOYEE.ISPFAPPLICABLE, EMPLOYEE.ISESIAPPLICABLE,
    0, -1, 26 AS ActualPresentDays, 4 AS PreviousWeeklyOffDays -- hardcoded per GB4 (Arul sir, 30-Jul-2022)
FROM MEMPLOYEE EMPLOYEE
, MPAYPERIOD PAYPERIOD
, (
    SELECT A.* FROM TPAYREVISION A,
    (SELECT EMPLOYEEID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
     WHERE PAYCONFIGURATIONID = @PayConfigurationId AND OUID = @OuId AND NATURE = 0
       AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate
     GROUP BY EMPLOYEEID) B
    WHERE A.PAYREVISIONID = B.PAYREVISIONID
  ) REVISION
WHERE EMPLOYEE.EMPLOYEEID <> -1
  AND PAYPERIOD.PAYPERIODID = @PayPeriodId
  AND EMPLOYEE.EMPLOYEEID = REVISION.EMPLOYEEID
  AND REVISION.PAYCONFIGURATIONID = @PayConfigurationId
  AND REVISION.EFFECTIVETO >= @FromDate AND REVISION.EFFECTIVEFROM <= @FromDate
  AND REVISION.NATURE = 0
  AND EMPLOYEE.EMPLOYEEID = @EmployeeId;";

        // GB4: UPDATE_DAILYATTENDANCE_SUM_OF_PRESENT_DAY_FIELD_TO_PAYPROCESS_DURING_SEPARATION
        // GB4 hardcodes WHERE PAYPERIODID=-4 literally (not the real period) — preserved.
        public const string SEPARATION_UPDATE_DAILYATTENDANCE_SUM = @"
UPDATE TPAYPROCESS
SET PREVIOUSHOLIDAYS = 0,
    PREVIOUSWEEKLYOFFDAYS = WEEKLYOFFDAYS,
    ATTENDANCEDAYS = TOTALDAYS,
    PREVIOUSPRESENTDAYS = PRESENTDAYS
WHERE PAYPERIODID = -4 AND OUID = @OuId AND PAYCONFIGID = @PayConfigurationId AND EMPLOYEEID = @EmployeeId;";

        // GB4: GET_FIXED_PAY_BASED_ON_CONDITION — MAdditionDeduction rows Nature IN (0,5) (Fixed / Fixed-Others)
        public const string GET_FIXED_ADDITIONDEDUCTION_FIELDS = @"
SELECT FIELDCODE AS Code, APPLICABLETYPE AS ApplicableType, NATURE AS Nature, FIELDDATATYPE AS FieldDataType
FROM MADDITIONDEDUCTION
WHERE ADDITIONDEDUCTIONID <> -1 AND NATURE IN (0, 5) AND FIELDDATATYPE <> 0;";

        // GB4: GET_VARIABLE_PAY_BASED_ON_CONDITION — MAdditionDeduction rows Nature = 1 (Variable/Periodic)
        public const string GET_VARIABLE_ADDITIONDEDUCTION_FIELDS = @"
SELECT FIELDCODE AS Code, APPLICABLETYPE AS ApplicableType, NATURE AS Nature, FIELDDATATYPE AS FieldDataType
FROM MADDITIONDEDUCTION
WHERE ADDITIONDEDUCTIONID <> -1 AND NATURE = 1 AND FIELDDATATYPE <> 0;";

        // GB4: LoadAllRecordsFromTable<Leave> filtered Id!=-1 && LeaveType==1 (Credit-type leaves)
        public const string GET_CREDIT_LEAVE_CODES = @"
SELECT LEAVECODE AS Code FROM MLEAVE WHERE LEAVEID <> -1 AND LEAVETYPE = 1;";

        // GB4: PAYPROCESS_ADDON_INSERT_QUERY_FIELD (fixed column prefix) + dynamic Leave/Fixed/Variable
        // columns appended by BLL. The fixed prefix below matches TPAYPROCESSADDON's mandatory columns.
        public const string SEPARATION_ADDON_INSERT_FIELD_PREFIX =
            "INSERT INTO TPAYPROCESSADDON (PAYPROCESSID";

        // GB4: PAYPROCESS_INSERT_QUERY_ADDON_FOR_SEPARATION — the massive multi-join SELECT that
        // supplies dynamic addon values (Fixed/Variable/Leave) for every separation-processed
        // employee. @DynamicColumns / @DynamicValues are built by the BLL from DB-driven metadata
        // (MAdditionDeduction.FIELDCODE) validated through an identifier-safety allowlist before
        // being spliced in — these are SQL column-reference expressions, not literal user input,
        // so cannot be bound as Dapper parameters (mirrors GB4's own dynamic-SQL precedent).
        // Literal -4 (SEPARATION_PAY_PERIOD_ID) is used for payperiodid joins against tperiodic/
        // TPAYREVISION EXCEPT where GB4 explicitly used the real period for revision-window dating
        // (@FromDate) — preserved exactly as GB4 wrote it.
        public static string BuildSeparationAddonInsertSql(string dynamicColumns, string dynamicValues) => @"
INSERT INTO TPAYPROCESSADDON (PAYPROCESSID" + dynamicColumns + @")
SELECT
    (@StartNumber + ROW_NUMBER() OVER (ORDER BY EMPLOYEE.EMPLOYEEID))" + dynamicValues + @"
FROM
    (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT EMPLOYEEID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE PAYCONFIGURATIONID = @PayConfigurationId AND OUID = @OuId AND NATURE = 0
           AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate
         GROUP BY EMPLOYEEID) B
        WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) REVISION,
    MEMPLOYEE EMPLOYEE
    LEFT OUTER JOIN TPERIODIC EmployeePeriodic
        ON (EmployeePeriodic.PAYPERIODID = " + SEPARATION_PAY_PERIOD_ID + @" AND EmployeePeriodic.APPLICABLE = 5 AND EmployeePeriodic.EMPLOYEEID = EMPLOYEE.EMPLOYEEID)
    LEFT OUTER JOIN TPERIODICADDON EmployeePeriodicaddonon ON (EmployeePeriodic.PERIODICID = EmployeePeriodicaddonon.PERIODICID)
    LEFT OUTER JOIN TPERIODIC employeegroupperiodic
        ON (employeegroupperiodic.PAYPERIODID = " + SEPARATION_PAY_PERIOD_ID + @" AND employeegroupperiodic.APPLICABLE = 3 AND employeegroupperiodic.PAYGROUPID = EMPLOYEE.PAYGROUPID)
    LEFT OUTER JOIN TPERIODICADDON employeegroupperiodicaddon ON (employeegroupperiodic.PERIODICID = employeegroupperiodicaddon.PERIODICID)
    LEFT OUTER JOIN TPERIODIC dagroupperiodic
        ON (dagroupperiodic.PAYPERIODID = " + SEPARATION_PAY_PERIOD_ID + @" AND dagroupperiodic.APPLICABLE = 4 AND dagroupperiodic.DAGROUPID = EMPLOYEE.DAPOINTGROUPID)
    LEFT OUTER JOIN TPERIODICADDON dagroupperiodicaddon ON (dagroupperiodic.PERIODICID = dagroupperiodicaddon.PERIODICID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT EMPLOYEEID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE PAYCONFIGURATIONID = @PayConfigurationId AND OUID = @OuId
           AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 0
         GROUP BY EMPLOYEEID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) employeepayrevision ON (employeepayrevision.EMPLOYEEID = EMPLOYEE.EMPLOYEEID AND employeepayrevision.APPLICABLE = 5 AND employeepayrevision.NATURE = 0)
    LEFT OUTER JOIN TPAYREVISIONADDON employeepayrevisionaddon ON (employeepayrevision.PAYREVISIONID = employeepayrevisionaddon.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT DAGROUPID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE OUID = @OuId AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 0 AND APPLICABLE = 4
         GROUP BY DAGROUPID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) dagrouppayrevision ON (dagrouppayrevision.DAGROUPID = EMPLOYEE.DAPOINTGROUPID AND dagrouppayrevision.APPLICABLE = 4)
    LEFT OUTER JOIN TPAYREVISIONADDON dagrouppayrevisionaddon ON (dagrouppayrevision.PAYREVISIONID = dagrouppayrevisionaddon.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT PAYGROUPID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 0 AND APPLICABLE = 3
         GROUP BY PAYGROUPID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) employeegrouppayrevision ON (employeegrouppayrevision.PAYGROUPID = EMPLOYEE.PAYGROUPID AND employeegrouppayrevision.APPLICABLE = 3)
    LEFT OUTER JOIN TPAYREVISIONADDON employeegrouppayrevisionaddon ON (employeegrouppayrevision.PAYREVISIONID = employeegrouppayrevisionaddon.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT PAYCONFIGURATIONID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE OUID = @OuId AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 0 AND APPLICABLE = 2
         GROUP BY PAYCONFIGURATIONID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) payconfigurationpayrevision ON (payconfigurationpayrevision.PAYCONFIGURATIONID = @PayConfigurationId AND payconfigurationpayrevision.APPLICABLE = 2)
    LEFT OUTER JOIN TPAYREVISIONADDON payconfigurationpayrevisionaddon ON (payconfigurationpayrevision.PAYREVISIONID = payconfigurationpayrevisionaddon.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT OUID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE OUID = @OuId AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 0 AND APPLICABLE = 1
         GROUP BY OUID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) oulevelpayrevision ON (oulevelpayrevision.OUID = @OuId AND oulevelpayrevision.APPLICABLE = 1)
    LEFT OUTER JOIN TPAYREVISIONADDON oulevelpayrevisionaddon ON (oulevelpayrevision.PAYREVISIONID = oulevelpayrevisionaddon.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 0 AND APPLICABLE = 0
        ) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) overallpayrevision ON (overallpayrevision.APPLICABLE = 0)
    LEFT OUTER JOIN TPAYREVISIONADDON overallpayrevisionaddon ON (overallpayrevision.PAYREVISIONID = overallpayrevisionaddon.PAYREVISIONID)

    -- FixedOthers (Nature=1) mirror joins
    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT EMPLOYEEID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE PAYCONFIGURATIONID = @PayConfigurationId AND OUID = @OuId
           AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 1
         GROUP BY EMPLOYEEID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) employeepayrevisionfo ON (employeepayrevisionfo.EMPLOYEEID = EMPLOYEE.EMPLOYEEID AND employeepayrevisionfo.APPLICABLE = 5 AND employeepayrevisionfo.NATURE = 1)
    LEFT OUTER JOIN TPAYREVISIONADDON employeepayrevisionaddonfo ON (employeepayrevisionfo.PAYREVISIONID = employeepayrevisionaddonfo.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT DAGROUPID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE OUID = @OuId AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 1 AND APPLICABLE = 4
         GROUP BY DAGROUPID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) dagrouppayrevisionfo ON (dagrouppayrevisionfo.DAGROUPID = EMPLOYEE.DAPOINTGROUPID AND dagrouppayrevisionfo.APPLICABLE = 4)
    LEFT OUTER JOIN TPAYREVISIONADDON dagrouppayrevisionaddonfo ON (dagrouppayrevisionfo.PAYREVISIONID = dagrouppayrevisionaddonfo.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT PAYGROUPID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 1 AND APPLICABLE = 3
         GROUP BY PAYGROUPID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) employeegrouppayrevisionfo ON (employeegrouppayrevisionfo.PAYGROUPID = EMPLOYEE.PAYGROUPID AND employeegrouppayrevisionfo.APPLICABLE = 3)
    LEFT OUTER JOIN TPAYREVISIONADDON employeegrouppayrevisionaddonfo ON (employeegrouppayrevisionfo.PAYREVISIONID = employeegrouppayrevisionaddonfo.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT PAYCONFIGURATIONID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE PAYCONFIGURATIONID = @PayConfigurationId AND OUID = @OuId AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 1 AND APPLICABLE = 2
         GROUP BY PAYCONFIGURATIONID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) payconfigurationpayrevisionfo ON (payconfigurationpayrevisionfo.PAYCONFIGURATIONID = @PayConfigurationId AND payconfigurationpayrevisionfo.APPLICABLE = 2)
    LEFT OUTER JOIN TPAYREVISIONADDON payconfigurationpayrevisionaddonfo ON (payconfigurationpayrevisionfo.PAYREVISIONID = payconfigurationpayrevisionaddonfo.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT OUID, MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE OUID = @OuId AND CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 1 AND APPLICABLE = 1
         GROUP BY OUID) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) oulevelpayrevisionfo ON (oulevelpayrevisionfo.OUID = @OuId AND oulevelpayrevisionfo.APPLICABLE = 1)
    LEFT OUTER JOIN TPAYREVISIONADDON oulevelpayrevisionaddonfo ON (oulevelpayrevisionfo.PAYREVISIONID = oulevelpayrevisionaddonfo.PAYREVISIONID)

    LEFT OUTER JOIN (
        SELECT A.* FROM TPAYREVISION A,
        (SELECT MAX(PAYREVISIONID) PAYREVISIONID FROM TPAYREVISION
         WHERE CONVERT(date, EFFECTIVETO) >= @FromDate AND CONVERT(date, EFFECTIVEFROM) <= @FromDate AND NATURE = 1 AND APPLICABLE = 0
        ) B WHERE A.PAYREVISIONID = B.PAYREVISIONID
    ) overallpayrevisionfo ON (overallpayrevisionfo.APPLICABLE = 0)
    LEFT OUTER JOIN TPAYREVISIONADDON overallpayrevisionaddonfo ON (overallpayrevisionfo.PAYREVISIONID = overallpayrevisionaddonfo.PAYREVISIONID)

    , MPAYPERIOD payperiod
    LEFT OUTER JOIN TPERIODIC OverallPeriodic ON (OverallPeriodic.PAYPERIODID = payperiod.PAYPERIODID AND OverallPeriodic.APPLICABLE = 0)
    LEFT OUTER JOIN TPERIODICADDON OverallPeriodicaddon ON (OverallPeriodic.PERIODICID = OverallPeriodicaddon.PERIODICID)
    LEFT OUTER JOIN TPERIODIC oulevelperiodic ON (oulevelperiodic.PAYPERIODID = payperiod.PAYPERIODID AND oulevelperiodic.APPLICABLE = 1 AND oulevelperiodic.OUID = @OuId)
    LEFT OUTER JOIN TPERIODICADDON oulevelperiodicaddon ON (oulevelperiodic.PERIODICID = oulevelperiodicaddon.PERIODICID)
    LEFT OUTER JOIN TPERIODIC payconfigurationperiodic ON (payconfigurationperiodic.PAYPERIODID = payperiod.PAYPERIODID AND payconfigurationperiodic.APPLICABLE = 2 AND payconfigurationperiodic.PAYCONFIGURATIONID = @PayConfigurationId)
    LEFT OUTER JOIN TPERIODICADDON payconfigurationPeriodicaddon ON (payconfigurationperiodic.PERIODICID = payconfigurationPeriodicaddon.PERIODICID)

WHERE EMPLOYEE.EMPLOYEEID <> -1
  AND payperiod.PAYPERIODID = @PayPeriodId
  AND EMPLOYEE.EMPLOYEEID = REVISION.EMPLOYEEID
  AND REVISION.PAYCONFIGURATIONID = @PayConfigurationId
  AND REVISION.EFFECTIVETO >= @FromDate AND REVISION.EFFECTIVEFROM <= @FromDate
  AND REVISION.NATURE = 0
  AND EMPLOYEE.EMPLOYEEID = @EmployeeId;";

        // ── Formula/phase infrastructure (GB4 Payparsing) ──────────────────
        // GB4: GET_FORMULA_BASED_ON_CONDITION — MFormula rows for Module=PAYPROCESS(-1499999788), Phase>=0
        public const string GET_FORMULA_FIELDS = @"
SELECT FORMULACODE AS Code, PHASE AS Phase, FORMULATYPE AS FormulaType, EXPRESSION AS Expression,
       FUNCTIONNAME AS FunctionName, DATATYPE AS DataType
FROM MFORMULA
WHERE PHASE >= 0 AND MODULEID = -1499999788
ORDER BY PHASE;";

        // GB4: GET_MAX_PHASE_FROM_FORMULA
        public const string GET_MAX_FORMULA_PHASE = @"SELECT MAX(PHASE) FROM MFORMULA;";

        // GB4: UPDATE_TPAYPROCESSADDON_BASED_ON_FORMULA — @StrUpdate is a validated
        // "F_CODE = ISNULL(expr,default), ..." fragment built from MFormula metadata,
        // never end-user input (mirrors GB4's own dynamic-SQL precedent).
        public static string BuildFormulaUpdateSql(string strUpdate) => @"
UPDATE TPAYPROCESSADDON SET " + strUpdate + @"
FROM TPAYPROCESSADDON, TPAYPROCESS, MEMPLOYEE, MEMPLOYEEADDON
WHERE TPAYPROCESSADDON.PAYPROCESSID = TPAYPROCESS.PAYPROCESSID
  AND TPAYPROCESS.PAYPERIODID = @PayPeriodId
  AND TPAYPROCESS.EMPLOYEEID = MEMPLOYEE.EMPLOYEEID
  AND MEMPLOYEE.EMPLOYEEID = MEMPLOYEEADDON.EMPLOYEEID
  AND TPAYPROCESS.PAYCONFIGID = @PayConfigurationId
  AND TPAYPROCESS.EMPLOYEEID = @EmployeeId;";

        // GB4: UPDATE_TPAYPROCESS_TOTALEARNING_DEDUCTION — @TotalEarning/@TotalDeduction/
        // @TotalAddition are SQL sum expressions built from MPayConfigurationDetail metadata
        // (e.g. "ISNULL(FIELD,0)+ISNULL(FIELD2,0)+0"), not end-user input.
        public static string BuildTotalEarningDeductionSql(string totalEarningExpr, string totalDeductionExpr, string totalAdditionExpr) => @"
UPDATE TPAYPROCESS
SET TOTALEARNINGS = ROUND(ADDON.Addition, 2), TOTALDEDUCTIONS = ROUND(ADDON.Deduction, 2), TOTALADDITIONS = ROUND(ADDON.TotalAddition, 2)
FROM (
    SELECT PAYPROCESSID, SUM(" + totalEarningExpr + @") AS Addition, SUM(" + totalDeductionExpr + @") AS Deduction, SUM(" + totalAdditionExpr + @") AS TotalAddition
    FROM TPAYPROCESSADDON GROUP BY PAYPROCESSID
) ADDON
WHERE ADDON.PAYPROCESSID = TPAYPROCESS.PAYPROCESSID
  AND TPAYPROCESS.PAYCONFIGID = @PayConfigurationId
  AND TPAYPROCESS.PAYPERIODID = @PayPeriodId
  AND TPAYPROCESS.OUID = @OuId;";

        // GB4: UPDATE_TPAYPROCESS_NETSALARY
        public const string SEPARATION_UPDATE_NETSALARY = @"
UPDATE TPAYPROCESS
SET NETSALARY = CASE MOULEVELSETTING.NETSALARYROUNDOFF
        WHEN 0 THEN ROUND((TOTALEARNINGS - TOTALDEDUCTIONS), MOULEVELSETTING.NETSALARYROUNDOFF)
        WHEN 1 THEN ROUND((TOTALEARNINGS - TOTALDEDUCTIONS), MOULEVELSETTING.NETSALARYROUNDOFF)
        WHEN 2 THEN ROUND((TOTALEARNINGS - TOTALDEDUCTIONS), MOULEVELSETTING.NETSALARYROUNDOFF)
        WHEN 3 THEN ROUND((TOTALEARNINGS - TOTALDEDUCTIONS), MOULEVELSETTING.NETSALARYROUNDOFF)
        WHEN 4 THEN ROUND((TOTALEARNINGS - TOTALDEDUCTIONS), MOULEVELSETTING.NETSALARYROUNDOFF)
        WHEN 5 THEN CEILING(TOTALEARNINGS - TOTALDEDUCTIONS)
        WHEN 6 THEN FLOOR(TOTALEARNINGS - TOTALDEDUCTIONS)
        ELSE ROUND((TOTALEARNINGS - TOTALDEDUCTIONS), 0)
    END
FROM MOULEVELSETTING
WHERE TPAYPROCESS.PAYCONFIGID = @PayConfigurationId
  AND TPAYPROCESS.PAYPERIODID = @PayPeriodId
  AND TPAYPROCESS.OUID = MOULEVELSETTING.OUID
  AND TPAYPROCESS.OUID = @OuId;";

        // GB4: UPDATE_TPAYPROCESS_C2C — @ReplaceFields is a SQL sum expression over
        // MPayConfigurationDetail metadata field codes (e.g. "ISNULL(FIELD,0)+...+0").
        public static string BuildC2CUpdateSql(string replaceFieldsExpr) => @"
UPDATE TPAYPROCESS SET C2C = SUB.C2C
FROM (
    SELECT A.PAYPROCESSID, (" + replaceFieldsExpr + @") AS C2C
    FROM TPAYPROCESS A, TPAYPROCESSADDON B
    WHERE A.PAYPROCESSID = B.PAYPROCESSID
      AND A.PAYCONFIGID = @PayConfigurationId
      AND A.PAYPERIODID = @PayPeriodId
      AND A.OUID = @OuId
) SUB
WHERE TPAYPROCESS.PAYPROCESSID = SUB.PAYPROCESSID;";
    }
}