using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.PayRevision
{
    public static class PayRevisionQB
    {
        public const string GET_PAYREVISION = @"
SELECT
    pr.PAYREVISIONID            AS PayRevisionId,
    pr.BIZTRANSACTIONTYPEID     AS BizTransactionTypeId,
    btt.BIZTRANSACTIONTYPECODE  AS BizTransactionTypeCode,
    btt.BIZTRANSACTIONTYPENAME  AS BizTransactionTypeName,

    pr.PAYGROUPID               AS PayGroupId,
    pg.PAYGROUPCODE             AS PayGroupCode,
    pg.PAYGROUPNAME             AS PayGroupName,

    pr.PAYREVISIONNUMBER        AS PayRevisionPayRevisionNumber,
    pr.REVISIONDATE             AS PayRevisionRevisionDate,
    pr.PAYREFERENCENUMBER       AS PayRevisionPayReferenceNumber,
    pr.REFERENCEDATE            AS PayRevisionReferenceDate,
    pr.APPLICABLE               AS PayRevisionApplicable,

    pr.EMPLOYEEID               AS EmployeeId,
    e.EMPLOYEECODE              AS EmployeeCode,
    e.EMPLOYEENAME              AS EmployeeName,

    pr.OUID                     AS OUId,
    ou.ORGANIZATIONUNITCODE     AS OUCode,
    ou.ORGANIZATIONUNITNAME     AS OUName,

    pr.PAYCONFIGURATIONID       AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE     AS PayConfigurationCode,
    pc.PAYCONFIGURATIONNAME     AS PayConfigurationName,

    pr.DAGROUPID                AS DAGroupId,
    dag.DAPOINTGROUPCODE        AS DAGroupCode,
    dag.DAPOINTGROUPNAME        AS DAGroupName,

    pr.EFFECTIVEFROM            AS PayRevisionEffectiveFrom,
    pr.EFFECTIVETO              AS PayRevisionEffectiveTo,
    pr.REMARKS                  AS PayRevisionRemarks,
    pr.NATURE                   AS PayRevisionNature,

    pr.C2C                      AS C2C,
    pr.COSTPERHOUR              AS CostPerHour,
    pr.RATEPERHOUR              AS RatePerHour,

    pr.STATUS                   AS PayRevisionStatus,
    pr.VERSION                  AS PayRevisionVersion,

    pr.CREATEDBYID              AS PayRevisionCreatedById,
    cb.USERNAME                 AS PayRevisionCreatedByName,
    pr.CREATEDON                AS PayRevisionCreatedOn,

    pr.MODIFIEDBYID             AS PayRevisionModifiedById,
    mb.USERNAME                 AS PayRevisionModifiedByName,
    pr.MODIFIEDON               AS PayRevisionModifiedOn

FROM TPAYREVISION pr
LEFT JOIN MBIZTRANSACTIONTYPE  btt ON pr.BIZTRANSACTIONTYPEID  = btt.BIZTRANSACTIONTYPEID
LEFT JOIN MPAYGROUP            pg  ON pr.PAYGROUPID            = pg.PAYGROUPID
LEFT JOIN MEMPLOYEE            e   ON pr.EMPLOYEEID            = e.EMPLOYEEID
LEFT JOIN MORGANIZATIONUNIT    ou  ON pr.OUID                  = ou.OUID
LEFT JOIN MPAYCONFIGURATION    pc  ON pr.PAYCONFIGURATIONID    = pc.PAYCONFIGURATIONID
LEFT JOIN MDAPOINTGROUP        dag ON pr.DAGROUPID             = dag.DAPOINTGROUPID
LEFT JOIN MUSER                cb  ON pr.CREATEDBYID           = cb.USERID
LEFT JOIN MUSER                mb  ON pr.MODIFIEDBYID          = mb.USERID
WHERE pr.PAYREVISIONID = @PayRevisionId;";


        public const string SAVE_PAYREVISION = @"
INSERT INTO TPAYREVISION
(
    PAYREVISIONID,
    BIZTRANSACTIONTYPEID,
    PAYREVISIONNUMBER,
    REVISIONDATE,
    PAYREFERENCENUMBER,
    REFERENCEDATE,
    APPLICABLE,
    EMPLOYEEID,
    OUID,
    PAYCONFIGURATIONID,
    DAGROUPID,
    EFFECTIVEFROM,
    EFFECTIVETO,
    REMARKS,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    PAYGROUPID,
    NATURE,
    C2C,
    COSTPERHOUR,
    RATEPERHOUR
)
VALUES
(
    @PayRevisionId,
    @BizTransactionTypeId,
    @PayRevisionPayRevisionNumber,
    @PayRevisionRevisionDate,
    @PayRevisionPayReferenceNumber,
    @PayRevisionReferenceDate,
    @PayRevisionApplicable,
    @EmployeeId,
    @OUId,
    @PayConfigurationId,
    @DAGroupId,
    @PayRevisionEffectiveFrom,
    @PayRevisionEffectiveTo,
    @PayRevisionRemarks,
    @PayRevisionStatus,
    @PayRevisionVersion,
    @PayRevisionCreatedById,
    @PayRevisionCreatedOn,
    @PayRevisionModifiedById,
    @PayRevisionModifiedOn,
    @PayGroupId,
    @PayRevisionNature,
    @C2C,
    @CostPerHour,
    @RatePerHour
);";

        // ✅ FIXED: Removed FUNCTIONALREPORTINGTOID and ADMINREPORTINGTOID
        public const string UPDATE_PAYREVISION = @"
UPDATE TPAYREVISION
SET
    BIZTRANSACTIONTYPEID  = @BizTransactionTypeId,
    PAYREVISIONNUMBER     = @PayRevisionPayRevisionNumber,
    REVISIONDATE          = @PayRevisionRevisionDate,
    PAYREFERENCENUMBER    = @PayRevisionPayReferenceNumber,
    REFERENCEDATE         = @PayRevisionReferenceDate,
    APPLICABLE            = @PayRevisionApplicable,
    EMPLOYEEID            = @EmployeeId,
    OUID                  = @OUId,
    PAYCONFIGURATIONID    = @PayConfigurationId,
    DAGROUPID             = @DAGroupId,
    EFFECTIVEFROM         = @PayRevisionEffectiveFrom,
    EFFECTIVETO           = @PayRevisionEffectiveTo,
    REMARKS               = @PayRevisionRemarks,
    STATUS                = @PayRevisionStatus,
    VERSION               = @PayRevisionVersion,
    MODIFIEDBYID          = @PayRevisionModifiedById,
    MODIFIEDON            = @PayRevisionModifiedOn,
    PAYGROUPID            = @PayGroupId,
    NATURE                = @PayRevisionNature,
    C2C                   = @C2C,
    COSTPERHOUR           = @CostPerHour,
    RATEPERHOUR           = @RatePerHour
WHERE PAYREVISIONID = @PayRevisionId;";


        public const string DELETE_PAYREVISION = @"
DELETE FROM TPAYREVISION
WHERE PAYREVISIONID = @PayRevisionId;";


        public const string DELETE_PAYREVISION_ADDON = @"
DELETE FROM TPAYREVISIONADDON
WHERE PAYREVISIONID = @PayRevisionId;";


        // Returns the addon row for a single PayRevision as a JSON string (FeAddon).
        // NULL when no addon row exists — caller must handle gracefully.
        public const string GET_PAYREVISION_ADDON = @"
SELECT (
    SELECT TOP 1 *
    FROM   TPAYREVISIONADDON
    WHERE  PAYREVISIONID = @PayRevisionId
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) AS FeAddon;";


        // TPAYREVISIONADDON is a fixed wide table (SALARY, FOODALLOW, LTA, PFLIMIT, ... — see
        // AddonColumnTypes in PayRevisionDAL) — it has no single JSON/blob column. The real
        // upsert SQL is built dynamically in PayRevisionDAL.SavePayRevisionAddon from the
        // whitelisted column set present in the caller's FeAddon JSON. This constant only
        // covers the case where the client sends no addon fields at all — it just ensures a
        // placeholder row exists (matching GB4's one-row-per-revision behavior) without
        // touching any column values, so it needs no dynamic column list.
        // Live column discovery for the dynamic upsert in PayRevisionDAL.SavePayRevisionAddon —
        // cached briefly there so this doesn't run on every save. Excludes PAYREVISIONID, which
        // is always supplied separately as the key, never as a client-editable addon field.
        public const string GET_PAYREVISIONADDON_COLUMNS = @"
SELECT COLUMN_NAME              AS ColumnName,
       DATA_TYPE                AS DataType,
       CHARACTER_MAXIMUM_LENGTH AS CharacterMaximumLength
FROM   INFORMATION_SCHEMA.COLUMNS
WHERE  TABLE_NAME = 'TPAYREVISIONADDON'
AND    COLUMN_NAME <> 'PAYREVISIONID'
ORDER BY ORDINAL_POSITION;";

        public const string INSERT_PAYREVISION_ADDON_PLACEHOLDER = @"
IF NOT EXISTS (SELECT 1 FROM TPAYREVISIONADDON WHERE PAYREVISIONID = @PayRevisionId)
BEGIN
    INSERT INTO TPAYREVISIONADDON (PAYREVISIONID) VALUES (@PayRevisionId)
END;";


        public const string GET_SELECTLIST_PAYREVISION = @"
WITH PagedPayRevision AS
(
    SELECT
        pr.PAYREVISIONID      AS Id,
        pr.PAYREVISIONNUMBER  AS PayRevisionNumber,
        pr.REVISIONDATE       AS RevisionDate,
        pr.PAYREFERENCENUMBER AS PayReferenceNumber,
        pr.REFERENCEDATE      AS ReferenceDate,
        ROW_NUMBER() OVER (ORDER BY pr.PAYREVISIONID) AS RowNum
    FROM TPAYREVISION pr
)
SELECT Id, PayRevisionNumber, RevisionDate, PayReferenceNumber, ReferenceDate
FROM PagedPayRevision
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";


        public const string GET_PAYREVISION_LIST = @"
SELECT
    pr.PAYREVISIONID            AS PayRevisionId,
    pr.BIZTRANSACTIONTYPEID     AS BizTransactionTypeId,
    btt.BIZTRANSACTIONTYPECODE  AS BizTransactionTypeCode,
    btt.BIZTRANSACTIONTYPENAME  AS BizTransactionTypeName,

    pr.PAYGROUPID               AS PayGroupId,
    pg.PAYGROUPCODE             AS PayGroupCode,
    pg.PAYGROUPNAME             AS PayGroupName,

    pr.PAYREVISIONNUMBER        AS PayRevisionPayRevisionNumber,
    pr.REVISIONDATE             AS PayRevisionRevisionDate,
    pr.PAYREFERENCENUMBER       AS PayRevisionPayReferenceNumber,
    pr.REFERENCEDATE            AS PayRevisionReferenceDate,
    pr.APPLICABLE               AS PayRevisionApplicable,

    pr.EMPLOYEEID               AS EmployeeId,
    e.EMPLOYEECODE              AS EmployeeCode,
    e.EMPLOYEENAME              AS EmployeeName,

    pr.OUID                     AS OUId,
    ou.ORGANIZATIONUNITCODE     AS OUCode,
    ou.ORGANIZATIONUNITNAME     AS OUName,

    pr.PAYCONFIGURATIONID       AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE     AS PayConfigurationCode,
    pc.PAYCONFIGURATIONNAME     AS PayConfigurationName,

    pr.DAGROUPID                AS DAGroupId,
    dag.DAPOINTGROUPCODE        AS DAGroupCode,
    dag.DAPOINTGROUPNAME        AS DAGroupName,

    pr.EFFECTIVEFROM            AS PayRevisionEffectiveFrom,
    pr.EFFECTIVETO              AS PayRevisionEffectiveTo,
    pr.REMARKS                  AS PayRevisionRemarks,
    pr.NATURE                   AS PayRevisionNature,

    pr.C2C                      AS C2C,
    pr.COSTPERHOUR              AS CostPerHour,
    pr.RATEPERHOUR              AS RatePerHour,

    pr.STATUS                   AS PayRevisionStatus,
    pr.VERSION                  AS PayRevisionVersion,

    pr.CREATEDBYID              AS PayRevisionCreatedById,
    cb.USERNAME                 AS PayRevisionCreatedByName,
    pr.CREATEDON                AS PayRevisionCreatedOn,

    pr.MODIFIEDBYID             AS PayRevisionModifiedById,
    mb.USERNAME                 AS PayRevisionModifiedByName,
    pr.MODIFIEDON               AS PayRevisionModifiedOn

FROM TPAYREVISION pr
LEFT JOIN MBIZTRANSACTIONTYPE  btt ON pr.BIZTRANSACTIONTYPEID  = btt.BIZTRANSACTIONTYPEID
LEFT JOIN MPAYGROUP            pg  ON pr.PAYGROUPID            = pg.PAYGROUPID
LEFT JOIN MEMPLOYEE            e   ON pr.EMPLOYEEID            = e.EMPLOYEEID
LEFT JOIN MORGANIZATIONUNIT    ou  ON pr.OUID                  = ou.OUID
LEFT JOIN MPAYCONFIGURATION    pc  ON pr.PAYCONFIGURATIONID    = pc.PAYCONFIGURATIONID
LEFT JOIN MDAPOINTGROUP        dag ON pr.DAGROUPID             = dag.DAPOINTGROUPID
LEFT JOIN MUSER                cb  ON pr.CREATEDBYID           = cb.USERID
LEFT JOIN MUSER                mb  ON pr.MODIFIEDBYID          = mb.USERID
ORDER BY pr.PAYREVISIONID;";


        // ── UpdatePreviousRevision ────────────────────────────────────────────────
        // Sets EffectiveTo and marks status=4 (expired) on a prior revision
        // when a newer revision takes effect from a later date.
        public const string UPDATE_PREVIOUS_REVISION = @"
UPDATE TPAYREVISION
SET    EFFECTIVETO = @EffectiveTo,
       STATUS      = 4,
       MODIFIEDON  = GETUTCDATE()
WHERE  PAYREVISIONID = @PayRevisionId;";


        // ── GetPreviousRevisionByScope ────────────────────────────────────────────
        // Base query for finding prior active revisions of the same scope.
        // The DAL method appends one SCOPE_FILTER_* constant based on dto.Applicable.
        public const string GET_PREVIOUS_REVISION_BASE = @"
SELECT PAYREVISIONID   AS PayRevisionId,
       EFFECTIVEFROM   AS PayRevisionEffectiveFrom,
       EFFECTIVETO     AS PayRevisionEffectiveTo
FROM   TPAYREVISION
WHERE  APPLICABLE  = @Applicable
AND    NATURE      = @Nature
AND    STATUS     != 4
AND    EFFECTIVETO > @EffectiveFrom";

        // Scope-specific WHERE fragments (one appended to GET_PREVIOUS_REVISION_BASE in DAL)
        // Applicable = 5 → Employee-level
        public const string SCOPE_FILTER_EMPLOYEE  = " AND EMPLOYEEID = @EmployeeId AND PAYCONFIGURATIONID = @PayConfigurationId";
        // Applicable = 4 → DAGroup-level
        public const string SCOPE_FILTER_DAGROUP   = " AND DAGROUPID = @DAGroupId AND OUID = @OUId";
        // Applicable = 3 → PayGroup-level
        public const string SCOPE_FILTER_PAYGROUP  = " AND PAYGROUPID = @PayGroupId AND OUID = @OUId";
        // Applicable = 2 → PayConfiguration-level
        public const string SCOPE_FILTER_PAYCONFIG = " AND PAYCONFIGURATIONID = @PayConfigurationId";
        // Applicable = 1 → OU-level
        public const string SCOPE_FILTER_OU        = " AND OUID = @OUId";
        // Applicable = 0 → Overall: no additional scope filter


        // ── ArrearCheck ───────────────────────────────────────────────────────────
        // Finds existing revisions in the same scope/nature (excluding self) that
        // may conflict with a new revision's EffectiveFrom. DAL appends a
        // SCOPE_FILTER_* fragment based on dto.PayRevisionApplicable, then
        // CHECK_ARREAR_OVERLAP_ORDER. If the latest (last, by EFFECTIVEFROM) row
        // returned has an EffectiveFrom on/after the new revision's EffectiveFrom,
        // it's reported back as an overlapping revision.
        public const string CHECK_ARREAR_OVERLAP_BASE = @"
SELECT
    pr.PAYREVISIONID            AS PayRevisionId,
    pr.BIZTRANSACTIONTYPEID     AS BizTransactionTypeId,
    btt.BIZTRANSACTIONTYPECODE  AS BizTransactionTypeCode,
    btt.BIZTRANSACTIONTYPENAME  AS BizTransactionTypeName,

    pr.PAYGROUPID               AS PayGroupId,
    pg.PAYGROUPCODE             AS PayGroupCode,
    pg.PAYGROUPNAME             AS PayGroupName,

    pr.PAYREVISIONNUMBER        AS PayRevisionPayRevisionNumber,
    pr.REVISIONDATE             AS PayRevisionRevisionDate,
    pr.PAYREFERENCENUMBER       AS PayRevisionPayReferenceNumber,
    pr.REFERENCEDATE            AS PayRevisionReferenceDate,
    pr.APPLICABLE               AS PayRevisionApplicable,

    pr.EMPLOYEEID               AS EmployeeId,
    e.EMPLOYEECODE              AS EmployeeCode,
    e.EMPLOYEENAME              AS EmployeeName,

    pr.OUID                     AS OUId,
    ou.ORGANIZATIONUNITCODE     AS OUCode,
    ou.ORGANIZATIONUNITNAME     AS OUName,

    pr.PAYCONFIGURATIONID       AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE     AS PayConfigurationCode,
    pc.PAYCONFIGURATIONNAME     AS PayConfigurationName,

    pr.DAGROUPID                AS DAGroupId,
    dag.DAPOINTGROUPCODE        AS DAGroupCode,
    dag.DAPOINTGROUPNAME        AS DAGroupName,

    pr.EFFECTIVEFROM            AS PayRevisionEffectiveFrom,
    pr.EFFECTIVETO              AS PayRevisionEffectiveTo,
    pr.REMARKS                  AS PayRevisionRemarks,
    pr.NATURE                   AS PayRevisionNature,

    pr.C2C                      AS C2C,
    pr.COSTPERHOUR              AS CostPerHour,
    pr.RATEPERHOUR              AS RatePerHour,

    pr.STATUS                   AS PayRevisionStatus,
    pr.VERSION                  AS PayRevisionVersion
FROM TPAYREVISION pr
LEFT JOIN MBIZTRANSACTIONTYPE  btt ON pr.BIZTRANSACTIONTYPEID  = btt.BIZTRANSACTIONTYPEID
LEFT JOIN MPAYGROUP            pg  ON pr.PAYGROUPID            = pg.PAYGROUPID
LEFT JOIN MEMPLOYEE            e   ON pr.EMPLOYEEID            = e.EMPLOYEEID
LEFT JOIN MORGANIZATIONUNIT    ou  ON pr.OUID                  = ou.OUID
LEFT JOIN MPAYCONFIGURATION    pc  ON pr.PAYCONFIGURATIONID    = pc.PAYCONFIGURATIONID
LEFT JOIN MDAPOINTGROUP        dag ON pr.DAGROUPID             = dag.DAPOINTGROUPID
WHERE  pr.APPLICABLE     = @Applicable
AND    pr.NATURE         = @Nature
AND    pr.PAYREVISIONID != @PayRevisionId";

        // After appending a SCOPE_FILTER_* fragment based on Applicable:
        public const string CHECK_ARREAR_OVERLAP_ORDER = @"
ORDER BY pr.EFFECTIVEFROM ASC, pr.CREATEDON ASC;";


        // ── CheckPayProcessExists ─────────────────────────────────────────────────
        // Guards delete: returns count of pay-processing records for this revision.
        public const string CHECK_PAYPROCESS_EXISTS = @"
SELECT COUNT(1)
FROM   TPAYPROCESS
WHERE  PAYREVISIONID = @PayRevisionId;";


        // ── GetPayRevisionByScope ─────────────────────────────────────────────────
        // Returns the latest revision per employee within a given scope/nature.
        // Used by payroll processing to resolve the effective salary structure.
        // Caller supplies @FieldId and the appropriate scope column via the
        // SCOPE_FILTER_* fragments above.
        public const string GET_PAYREVISION_BY_SCOPE_BASE = @"
SELECT
    pr.PAYREVISIONID            AS PayRevisionId,
    pr.PAYREVISIONNUMBER        AS PayRevisionPayRevisionNumber,
    pr.REVISIONDATE             AS PayRevisionRevisionDate,
    pr.APPLICABLE               AS PayRevisionApplicable,
    pr.NATURE                   AS PayRevisionNature,
    pr.EFFECTIVEFROM            AS PayRevisionEffectiveFrom,
    pr.EFFECTIVETO              AS PayRevisionEffectiveTo,
    pr.EMPLOYEEID               AS EmployeeId,
    e.EMPLOYEECODE              AS EmployeeCode,
    e.EMPLOYEENAME              AS EmployeeName,
    pr.PAYCONFIGURATIONID       AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE     AS PayConfigurationCode,
    pr.C2C                      AS C2C,
    pr.COSTPERHOUR              AS CostPerHour,
    pr.RATEPERHOUR              AS RatePerHour
FROM   TPAYREVISION pr
JOIN   MEMPLOYEE         e  ON pr.EMPLOYEEID         = e.EMPLOYEEID
LEFT JOIN MPAYCONFIGURATION pc ON pr.PAYCONFIGURATIONID = pc.PAYCONFIGURATIONID
WHERE  pr.NATURE     = @Nature
AND    pr.APPLICABLE = @Applicable
AND    pr.STATUS    != 4
AND    pr.PAYREVISIONID IN
(
    SELECT MAX(p2.PAYREVISIONID)
    FROM   TPAYREVISION p2
    WHERE  p2.NATURE     = @Nature
    AND    p2.APPLICABLE = @Applicable";

        // After appending GET_PAYREVISION_BY_SCOPE_BASE + scope fragment, close with:
        public const string GET_PAYREVISION_BY_SCOPE_TAIL = @"
    GROUP BY p2.EMPLOYEEID
)";


        // ── Delete chain guards ───────────────────────────────────────────────────

        // Returns count of non-expired revisions for the same scope with a later
        // effective date than the revision being deleted.
        // If > 0, the target is a middle revision and deletion must be blocked.
        // DAL appends a SCOPE_FILTER_* constant based on dto.Applicable.
        public const string CHECK_NEWER_REVISION_EXISTS_BASE = @"
SELECT COUNT(1)
FROM   TPAYREVISION
WHERE  APPLICABLE      = @Applicable
AND    NATURE          = @Nature
AND    STATUS         != 4
AND    EFFECTIVEFROM   > @EffectiveFrom
AND    PAYREVISIONID  != @PayRevisionId";


        // Returns the revision with the highest EffectiveFrom that is still less
        // than the revision being deleted — i.e. the direct predecessor.
        // Includes STATUS=4 records so an already-expired predecessor is found.
        // DAL appends SCOPE_FILTER_* then GET_PREDECESSOR_REVISION_ORDER.
        public const string GET_PREDECESSOR_REVISION_BASE = @"
SELECT PAYREVISIONID    AS PayRevisionId,
       EFFECTIVEFROM    AS PayRevisionEffectiveFrom,
       EFFECTIVETO      AS PayRevisionEffectiveTo
FROM   TPAYREVISION
WHERE  APPLICABLE      = @Applicable
AND    NATURE          = @Nature
AND    EFFECTIVEFROM   < @EffectiveFrom
AND    PAYREVISIONID  != @PayRevisionId";

        public const string GET_PREDECESSOR_REVISION_ORDER = " ORDER BY EFFECTIVEFROM DESC";


        // Re-activates a predecessor revision after its successor is deleted.
        // Sets EffectiveTo to open-ended and status back to Active (1).
        public const string RESTORE_PREVIOUS_REVISION = @"
UPDATE TPAYREVISION
SET    EFFECTIVETO = '9999-12-31',
       STATUS      = 1,
       MODIFIEDON  = GETUTCDATE()
WHERE  PAYREVISIONID = @PayRevisionId;";
        // Add this to PayRollDAL/Query/PayRevision/PayRevisionQB.cs

        // PayRollDAL/Query/PayRevision/PayRevisionQB.cs

       public const string GET_PAYREVISION_BY_FIELD = @"
-- Get the latest revision for each employee matching the criteria
WITH LatestRevisions AS (
    SELECT 
        EMPLOYEEID,
        MAX(PAYREVISIONID) AS MaxRevisionId
    FROM TPAYREVISION
    WHERE NATURE = @Nature
    AND OUID = @OUId
    AND STATUS = 4  -- Only Active records
    GROUP BY EMPLOYEEID
)
SELECT 
    pr.PAYREVISIONID            AS PayRevisionId,
    pr.BIZTRANSACTIONTYPEID     AS BizTransactionTypeId,
    btt.BIZTRANSACTIONTYPECODE  AS BizTransactionTypeCode,
    btt.BIZTRANSACTIONTYPENAME  AS BizTransactionTypeName,
    pr.PAYGROUPID               AS PayGroupId,
    pg.PAYGROUPCODE             AS PayGroupCode,
    pg.PAYGROUPNAME             AS PayGroupName,
    pr.PAYREVISIONNUMBER        AS PayRevisionPayRevisionNumber,
    pr.REVISIONDATE             AS PayRevisionRevisionDate,
    pr.PAYREFERENCENUMBER       AS PayRevisionPayReferenceNumber,
    pr.REFERENCEDATE            AS PayRevisionReferenceDate,
    pr.APPLICABLE               AS PayRevisionApplicable,
    pr.EMPLOYEEID               AS EmployeeId,
    e.EMPLOYEECODE              AS EmployeeCode,
    e.EMPLOYEENAME              AS EmployeeName,
    pr.OUID                     AS OUId,
    ou.ORGANIZATIONUNITCODE     AS OUCode,
    ou.ORGANIZATIONUNITNAME     AS OUName,
    pr.PAYCONFIGURATIONID       AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE     AS PayConfigurationCode,
    pc.PAYCONFIGURATIONNAME     AS PayConfigurationName,
    pr.DAGROUPID                AS DAGroupId,
    dag.DAPOINTGROUPCODE        AS DAGroupCode,
    dag.DAPOINTGROUPNAME        AS DAGroupName,
    pr.EFFECTIVEFROM            AS PayRevisionEffectiveFrom,
    pr.EFFECTIVETO              AS PayRevisionEffectiveTo,
    pr.REMARKS                  AS PayRevisionRemarks,
    pr.NATURE                   AS PayRevisionNature,
    pr.C2C                      AS C2C,
    pr.COSTPERHOUR              AS CostPerHour,
    pr.RATEPERHOUR              AS RatePerHour,
    pr.STATUS                   AS PayRevisionStatus,
    pr.VERSION                  AS PayRevisionVersion,
    pr.CREATEDBYID              AS PayRevisionCreatedById,
    cb.USERNAME                 AS PayRevisionCreatedByName,
    pr.CREATEDON                AS PayRevisionCreatedOn,
    pr.MODIFIEDBYID             AS PayRevisionModifiedById,
    mb.USERNAME                 AS PayRevisionModifiedByName,
    pr.MODIFIEDON               AS PayRevisionModifiedOn,
    CASE 
        WHEN pr.APPLICABLE = 5 THEN 'EMPLOYEELEVEL'
        ELSE 'UNKNOWN'
    END AS PayRevisionApplicableValue,
    CASE 
        WHEN pr.NATURE = 0 THEN 'Fixed'
        WHEN pr.NATURE = 1 THEN 'Variable'
        ELSE 'Unknown'
    END AS PayRevisionNatureValue,
    0 AS PayRevisionSourceType,
    0 AS PayRevisionType,
    0 AS TotalEarnings,
    0 AS TotalDeductions,
    0 AS NetSalary,
    NULL AS FunctionReportingToMailId,
    NULL AS FunctionReportingToMobileNumber,
    NULL AS AdminReportingToMailId,
    NULL AS AdminReportingToMobileNumber
FROM TPAYREVISION pr
INNER JOIN LatestRevisions lr ON pr.PAYREVISIONID = lr.MaxRevisionId
LEFT JOIN MBIZTRANSACTIONTYPE  btt ON pr.BIZTRANSACTIONTYPEID  = btt.BIZTRANSACTIONTYPEID
LEFT JOIN MPAYGROUP            pg  ON pr.PAYGROUPID            = pg.PAYGROUPID
LEFT JOIN MEMPLOYEE            e   ON pr.EMPLOYEEID            = e.EMPLOYEEID
LEFT JOIN MORGANIZATIONUNIT    ou  ON pr.OUID                  = ou.OUID
LEFT JOIN MPAYCONFIGURATION    pc  ON pr.PAYCONFIGURATIONID    = pc.PAYCONFIGURATIONID
LEFT JOIN MDAPOINTGROUP        dag ON pr.DAGROUPID             = dag.DAPOINTGROUPID
LEFT JOIN MUSER                cb  ON pr.CREATEDBYID           = cb.USERID
LEFT JOIN MUSER                mb  ON pr.MODIFIEDBYID          = mb.USERID
WHERE pr.NATURE = @Nature
AND pr.OUID = @OUId
AND pr.STATUS = 4
AND (
    (@FieldName = 'Employee' AND pr.EMPLOYEEID = @FieldId)
    OR 
    (@FieldName = 'PayGroup' AND pr.PAYGROUPID = @FieldId)
    OR 
    (@FieldName = 'PayConfiguration' AND pr.PAYCONFIGURATIONID = @FieldId)
)
ORDER BY pr.EMPLOYEEID, pr.PAYREVISIONID DESC;";
        public const string GET_PAYREVISION_BY_FIELD_ACTIVE = @"
-- Get only active revisions (Status 4) for employees matching the criteria
WITH LatestRevisions AS (
    SELECT 
        EMPLOYEEID,
        MAX(PAYREVISIONID) AS MaxRevisionId
    FROM TPAYREVISION
    WHERE NATURE = @Nature
    AND OUID = @OUId
    AND STATUS = 4  -- Only active records
    GROUP BY EMPLOYEEID
)
SELECT 
    pr.PAYREVISIONID            AS PayRevisionId,
    pr.BIZTRANSACTIONTYPEID     AS BizTransactionTypeId,
    btt.BIZTRANSACTIONTYPECODE  AS BizTransactionTypeCode,
    btt.BIZTRANSACTIONTYPENAME  AS BizTransactionTypeName,
    pr.PAYGROUPID               AS PayGroupId,
    pg.PAYGROUPCODE             AS PayGroupCode,
    pg.PAYGROUPNAME             AS PayGroupName,
    pr.PAYREVISIONNUMBER        AS PayRevisionPayRevisionNumber,
    pr.REVISIONDATE             AS PayRevisionRevisionDate,
    pr.PAYREFERENCENUMBER       AS PayRevisionPayReferenceNumber,
    pr.REFERENCEDATE            AS PayRevisionReferenceDate,
    pr.APPLICABLE               AS PayRevisionApplicable,
    pr.EMPLOYEEID               AS EmployeeId,
    e.EMPLOYEECODE              AS EmployeeCode,
    e.EMPLOYEENAME              AS EmployeeName,
    pr.OUID                     AS OUId,
    ou.ORGANIZATIONUNITCODE     AS OUCode,
    ou.ORGANIZATIONUNITNAME     AS OUName,
    pr.PAYCONFIGURATIONID       AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE     AS PayConfigurationCode,
    pc.PAYCONFIGURATIONNAME     AS PayConfigurationName,
    pr.DAGROUPID                AS DAGroupId,
    dag.DAPOINTGROUPCODE        AS DAGroupCode,
    dag.DAPOINTGROUPNAME        AS DAGroupName,
    pr.EFFECTIVEFROM            AS PayRevisionEffectiveFrom,
    pr.EFFECTIVETO              AS PayRevisionEffectiveTo,
    pr.REMARKS                  AS PayRevisionRemarks,
    pr.NATURE                   AS PayRevisionNature,
    pr.C2C                      AS C2C,
    pr.COSTPERHOUR              AS CostPerHour,
    pr.RATEPERHOUR              AS RatePerHour,
    pr.STATUS                   AS PayRevisionStatus,
    pr.VERSION                  AS PayRevisionVersion,
    pr.CREATEDBYID              AS PayRevisionCreatedById,
    cb.USERNAME                 AS PayRevisionCreatedByName,
    pr.CREATEDON                AS PayRevisionCreatedOn,
    pr.MODIFIEDBYID             AS PayRevisionModifiedById,
    mb.USERNAME                 AS PayRevisionModifiedByName,
    pr.MODIFIEDON               AS PayRevisionModifiedOn
FROM TPAYREVISION pr
INNER JOIN LatestRevisions lr ON pr.PAYREVISIONID = lr.MaxRevisionId
LEFT JOIN MBIZTRANSACTIONTYPE  btt ON pr.BIZTRANSACTIONTYPEID  = btt.BIZTRANSACTIONTYPEID
LEFT JOIN MPAYGROUP            pg  ON pr.PAYGROUPID            = pg.PAYGROUPID
LEFT JOIN MEMPLOYEE            e   ON pr.EMPLOYEEID            = e.EMPLOYEEID
LEFT JOIN MORGANIZATIONUNIT    ou  ON pr.OUID                  = ou.OUID
LEFT JOIN MPAYCONFIGURATION    pc  ON pr.PAYCONFIGURATIONID    = pc.PAYCONFIGURATIONID
LEFT JOIN MDAPOINTGROUP        dag ON pr.DAGROUPID             = dag.DAPOINTGROUPID
LEFT JOIN MUSER                cb  ON pr.CREATEDBYID           = cb.USERID
LEFT JOIN MUSER                mb  ON pr.MODIFIEDBYID          = mb.USERID
WHERE pr.NATURE = @Nature
AND pr.OUID = @OUId
AND pr.STATUS = 4  -- Only active records
AND (
    (@FieldName = 'Employee' AND pr.EMPLOYEEID = @FieldId)
    OR 
    (@FieldName = 'PayGroup' AND pr.PAYGROUPID = @FieldId)
    OR 
    (@FieldName = 'PayConfiguration' AND pr.PAYCONFIGURATIONID = @FieldId)
)
ORDER BY pr.EMPLOYEEID, pr.PAYREVISIONID DESC;";


        public const string GET_SELECTLIST_PAYREVISION_EMPLOYEE_BASED = @"
SELECT
    pr.PAYREVISIONID           AS PayRevisionId,
    pr.PAYREVISIONNUMBER       AS PayRevisionPayRevisionNumber,
    e.EMPLOYEEID               AS EmployeeId,
    e.EMPLOYEECODE             AS EmployeeCode,
    e.EMPLOYEENAME             AS EmployeeName,
    pr.REVISIONDATE            AS PayRevisionRevisionDate,
    pr.EFFECTIVEFROM           AS PayRevisionEffectiveFrom,
    pc.PAYCONFIGURATIONID      AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE    AS PayConfigurationCode,
    pc.PAYCONFIGURATIONNAME    AS PayConfigurationName
FROM TPAYREVISION pr
JOIN MEMPLOYEE e          ON pr.EMPLOYEEID         = e.EMPLOYEEID
JOIN MPAYCONFIGURATION pc ON pr.PAYCONFIGURATIONID = pc.PAYCONFIGURATIONID
WHERE
    pr.BIZTRANSACTIONTYPEID = @biztransactiontypeid
    AND pr.APPLICABLE = @applicable
    AND pr.NATURE = @nature
    AND (
        pr.PAYREVISIONNUMBER LIKE '%' + @payrevisionpayrevisionnumber + '%'
        OR e.EMPLOYEECODE LIKE '%' + @employeecode + '%'
        OR e.EMPLOYEENAME LIKE '%' + @employeename + '%'
    )
ORDER BY pr.PAYREVISIONNUMBER;";

        public const string GET_SELECTLIST_PAYREVISION_SCOPE_BASED = @"
SELECT
    pr.PAYREVISIONID           AS PayRevisionId,
    pr.PAYREVISIONNUMBER       AS PayRevisionPayRevisionNumber,
    pr.REVISIONDATE            AS PayRevisionRevisionDate,
    pr.EFFECTIVEFROM           AS PayRevisionEffectiveFrom,
    pc.PAYCONFIGURATIONID      AS PayConfigurationId,
    pc.PAYCONFIGURATIONCODE    AS PayConfigurationCode,
    pc.PAYCONFIGURATIONNAME    AS PayConfigurationName
FROM TPAYREVISION pr
JOIN MPAYCONFIGURATION pc ON pr.PAYCONFIGURATIONID = pc.PAYCONFIGURATIONID
WHERE
    pr.BIZTRANSACTIONTYPEID = @biztransactiontypeid
    AND pr.APPLICABLE = @applicable
    AND pr.NATURE = @nature
    AND pr.PAYREVISIONNUMBER LIKE '%' + @payrevisionpayrevisionnumber + '%'
ORDER BY pr.PAYREVISIONNUMBER;";
    }
}