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

namespace PayRollDAL.Query.PayConfiguration
{
    public static class PayConfigurationQB
    {
        public const string GET_SELECT_LIST_PAYCONFIGURATION = @"
WITH OrderedPayConfiguration AS
(
    SELECT
        PAYCONFIGURATIONID AS Id,
        PAYCONFIGURATIONCODE AS Code,
        PAYCONFIGURATIONNAME AS Name,
        ISPARTIALPROCESSREQUIRED AS IsPartialProcessRequired,
        ROW_NUMBER() OVER (ORDER BY PAYCONFIGURATIONID) AS RowNum
    FROM MPAYCONFIGURATION
    WHERE
        PAYCONFIGURATIONID <> -1
        AND (@Code IS NULL OR PAYCONFIGURATIONCODE LIKE '%' + @Code + '%')
        AND (@Name IS NULL OR PAYCONFIGURATIONNAME LIKE '%' + @Name + '%')
)
SELECT
    Id, Code, Name, IsPartialProcessRequired
FROM OrderedPayConfiguration
WHERE
    (@Offset = -1 AND @PageSize = -1)
    OR
    (RowNum BETWEEN @Offset + 1 AND @Offset + @PageSize)
ORDER BY RowNum;
";

        public const string GET_SELECT_LIST_PAYCONFIGURATION_COUNT = @"
SELECT COUNT(1)
FROM MPAYCONFIGURATION
WHERE
    PAYCONFIGURATIONID <> -1
    AND (@Code IS NULL OR PAYCONFIGURATIONCODE LIKE '%' + @Code + '%')
    AND (@Name IS NULL OR PAYCONFIGURATIONNAME LIKE '%' + @Name + '%');
";

        public const string GetPayConfigurationById = @" SELECT
    -- Parent: PayConfiguration
    PC.PAYCONFIGURATIONID AS PayConfigurationId,
    PC.PAYCONFIGURATIONCODE AS PayConfigurationCode,
    PC.PAYCONFIGURATIONNAME AS PayConfigurationName,
    PC.SALARYACCOUNTID AS SalaryAccountId,
    SA.ACCOUNTCODE AS SalaryAccountCode,
    SA.ACCOUNTNAME AS SalaryAccountName,
    PC.SORTORDER AS PayConfigurationSortOrder,
    PC.STATUS AS PayConfigurationStatus,
    PC.VERSION AS PayConfigurationVersion,
    PC.SOURCETYPE AS PayConfigurationSourceType,
    PC.CREATEDBYID AS PayConfigurationCreatedById,
    CBU.USERNAME AS PayConfigurationCreatedByName,
    PC.CREATEDON AS PayConfigurationCreatedOn,
    PC.MODIFIEDBYID AS PayConfigurationModifiedById,
    MBU.USERNAME AS PayConfigurationModifiedByName,
    PC.MODIFIEDON AS PayConfigurationModifiedOn,
    PC.ISPARTIALPROCESSREQUIRED AS PayConfigurationIsPartialProcessRequired,
    PC.TOTALEARNINGFORMULAID AS TotalEarningFormulaId,
    TEF.FORMULACODE AS TotalEarningFormulaCode,
    TEF.FORMULANAME AS TotalEarningFormulaName,
    PC.TOTALDEDUCTIONFORMULAID AS TotalDeductionFormulaId,
    TDF.FORMULACODE AS TotalDeductionFormulaCode,
    TDF.FORMULANAME AS TotalDeductionFormulaName,
    PC.NETSALARYFORMULAID AS NetSalaryFormulaId,
    NSF.FORMULACODE AS NetSalaryFormulaCode,
    NSF.FORMULANAME AS NetSalaryFormulaName,
    PC.C2CFORMULAID AS C2CFormulaId,
    C2F.FORMULACODE AS C2CFormulaCode,
    C2F.FORMULANAME AS C2CFormulaName,
    PC.REPORTFORMATID AS ReportFormatId,
    RF.REPORTFORMATNAME AS ReportFormatName,

    -- Child: PayConfigurationDetail (must come after all parent columns)
    PCD.PAYCONFIGURATIONDETAILID AS PayConfigurationDetailId,
    PCD.PAYCONFIGURATIONID AS PayConfigurationId,
    PCD.SLNO AS PayConfigurationDetailSlNo,
    PCD.FIELDTYPE AS PayConfigurationDetailFieldType,
    PCD.FIELDCODE AS PayConfigurationDetailFieldCode,
    PCD.OPERATIONTYPE AS PayConfigurationDetailOperationType,
    PCD.ISSEPARATEACCOUNT AS PayConfigurationDetailIsSeprateAccount,
    PCD.SALARYACCOUNTID AS SalaryAccountId,
    DSA.ACCOUNTCODE AS SalaryAccountCode,
    DSA.ACCOUNTNAME AS SalaryAccountName,
    PCD.ISEMPLOYEEACCOUNT AS PayConfigurationDetailIsEmployeeAccount,
    PCD.CONTRAACCOUNTID AS ContraAccountId,
    CA.ACCOUNTCODE AS ContraAccountCode,
    CA.ACCOUNTNAME AS ContraAccountName,
    PCD.PAYELEMENTID AS PayelementId,
    PE.PAYELEMENTCODE AS PayelementCode,
    PE.PAYELEMENTNAME AS PayelementName,
    PCD.ISCONTRAACCOUNTMERGE AS PayConfigurationDetailIsContraAccountMerge,
    PCD.ISSALARYACCOUNTMERGE AS PayConfigurationDetailIsSalaryAccountMerge,
    PCD.ACTUALFIELDTYPE AS PayConfigurationDetailActualFieldType,
    PCD.ACTUALFIELDNAME AS PayConfigurationDetailActualFieldName,
    PCD.ISSALARYCC AS PayConfigurationDetailIsSalaryCostCenter,
    PCD.ISCONTRACC AS PayConfigurationDetailIsContraCostCenter,
    PCD.CLAIMTYPE AS PayConfigurationDetailClaimType,
    PCD.CLAIMHEADID AS ClaimHeadId,
    PCD.PRINTREQUIRED AS PayConfigurationDetailPrintRequired

FROM MPAYCONFIGURATION PC
LEFT JOIN MPAYCONFIGURATIONDETAIL PCD ON PC.PAYCONFIGURATIONID = PCD.PAYCONFIGURATIONID
LEFT JOIN MACCOUNT SA ON PC.SALARYACCOUNTID = SA.ACCOUNTID
LEFT JOIN MACCOUNT DSA ON PCD.SALARYACCOUNTID = DSA.ACCOUNTID
LEFT JOIN MACCOUNT CA ON PCD.CONTRAACCOUNTID = CA.ACCOUNTID
LEFT JOIN MPAYELEMENT PE ON PCD.PAYELEMENTID = PE.PAYELEMENTID
LEFT JOIN MFORMULA TEF ON PC.TOTALEARNINGFORMULAID = TEF.FORMULAID
LEFT JOIN MFORMULA TDF ON PC.TOTALDEDUCTIONFORMULAID = TDF.FORMULAID
LEFT JOIN MFORMULA NSF ON PC.NETSALARYFORMULAID = NSF.FORMULAID
LEFT JOIN MFORMULA C2F ON PC.C2CFORMULAID = C2F.FORMULAID
LEFT JOIN MREPORTFORMAT RF ON PC.REPORTFORMATID = RF.REPORTFORMATID
LEFT JOIN MUSER CBU ON PC.CREATEDBYID = CBU.USERID
LEFT JOIN MUSER MBU ON PC.MODIFIEDBYID = MBU.USERID
WHERE PC.PAYCONFIGURATIONID = @payconfigurationid;

                    ";

        public const string GetPayConfigurationById_pg = @"
SELECT
    -- =========================
    -- Parent: PayConfiguration
    -- =========================
    PC.PAYCONFIGURATIONID AS ""PayConfigurationId"",
    PC.PAYCONFIGURATIONCODE AS ""PayConfigurationCode"",
    PC.PAYCONFIGURATIONNAME AS ""PayConfigurationName"",
    PC.SALARYACCOUNTID AS ""SalaryAccountId"",
    SA.ACCOUNTCODE AS ""SalaryAccountCode"",
    SA.ACCOUNTNAME AS ""SalaryAccountName"",
    PC.SORTORDER AS ""PayConfigurationSortOrder"",
    PC.STATUS AS ""PayConfigurationStatus"",
    PC.VERSION AS ""PayConfigurationVersion"",
    PC.SOURCETYPE AS ""PayConfigurationSourceType"",
    PC.CREATEDBYID AS ""PayConfigurationCreatedById"",
    CBU.USERNAME AS ""PayConfigurationCreatedByName"",
    PC.CREATEDON AS ""PayConfigurationCreatedOn"",
    PC.MODIFIEDBYID AS ""PayConfigurationModifiedById"",
    MBU.USERNAME AS ""PayConfigurationModifiedByName"",
    PC.MODIFIEDON AS ""PayConfigurationModifiedOn"",
    PC.ISPARTIALPROCESSREQUIRED AS ""PayConfigurationIsPartialProcessRequired"",
    PC.TOTALEARNINGFORMULAID AS ""TotalEarningFormulaId"",
    TEF.FORMULACODE AS ""TotalEarningFormulaCode"",
    TEF.FORMULANAME AS ""TotalEarningFormulaName"",
    PC.TOTALDEDUCTIONFORMULAID AS ""TotalDeductionFormulaId"",
    TDF.FORMULACODE AS ""TotalDeductionFormulaCode"",
    TDF.FORMULANAME AS ""TotalDeductionFormulaName"",
    PC.NETSALARYFORMULAID AS ""NetSalaryFormulaId"",
    NSF.FORMULACODE AS ""NetSalaryFormulaCode"",
    NSF.FORMULANAME AS ""NetSalaryFormulaName"",
    PC.C2CFORMULAID AS ""C2CFormulaId"",
    C2F.FORMULACODE AS ""C2CFormulaCode"",
    C2F.FORMULANAME AS ""C2CFormulaName"",
    PC.REPORTFORMATID AS ""ReportFormatId"",
    RF.REPORTFORMATNAME AS ""ReportFormatName"",

    -- =========================
    -- CHILD SPLIT COLUMN (MUST BE FIRST)
    -- =========================
    PCD.PAYCONFIGURATIONDETAILID AS ""PayConfigurationDetailId"",

    -- =========================
    -- Child: PayConfigurationDetail
    -- =========================
    PCD.PAYCONFIGURATIONID AS ""DetailPayConfigurationId"",
    PCD.SLNO AS ""PayConfigurationDetailSlNo"",
    PCD.FIELDTYPE AS ""PayConfigurationDetailFieldType"",
    PCD.FIELDCODE AS ""PayConfigurationDetailFieldCode"",
    PCD.OPERATIONTYPE AS ""PayConfigurationDetailOperationType"",
    PCD.ISSEPARATEACCOUNT AS ""PayConfigurationDetailIsSeprateAccount"",
    PCD.SALARYACCOUNTID AS ""DetailSalaryAccountId"",
    DSA.ACCOUNTCODE AS ""DetailSalaryAccountCode"",
    DSA.ACCOUNTNAME AS ""DetailSalaryAccountName"",
    PCD.ISEMPLOYEEACCOUNT AS ""PayConfigurationDetailIsEmployeeAccount"",
    PCD.CONTRAACCOUNTID AS ""ContraAccountId"",
    CA.ACCOUNTCODE AS ""ContraAccountCode"",
    CA.ACCOUNTNAME AS ""ContraAccountName"",
    PCD.PAYELEMENTID AS ""PayelementId"",
    PE.PAYELEMENTCODE AS ""PayelementCode"",
    PE.PAYELEMENTNAME AS ""PayelementName"",
    PCD.ISCONTRAACCOUNTMERGE AS ""PayConfigurationDetailIsContraAccountMerge"",
    PCD.ISSALARYACCOUNTMERGE AS ""PayConfigurationDetailIsSalaryAccountMerge"",
    PCD.ACTUALFIELDTYPE AS ""PayConfigurationDetailActualFieldType"",
    PCD.ACTUALFIELDNAME AS ""PayConfigurationDetailActualFieldName"",
    PCD.ISSALARYCC AS ""PayConfigurationDetailIsSalaryCostCenter"",
    PCD.ISCONTRACC AS ""PayConfigurationDetailIsContraCostCenter"",
    PCD.CLAIMTYPE AS ""PayConfigurationDetailClaimType"",
    PCD.CLAIMHEADID AS ""ClaimHeadId"",
    PCD.PRINTREQUIRED AS ""PayConfigurationDetailPrintRequired""

FROM MPAYCONFIGURATION PC
LEFT JOIN MPAYCONFIGURATIONDETAIL PCD 
       ON PC.PAYCONFIGURATIONID = PCD.PAYCONFIGURATIONID
LEFT JOIN MACCOUNT SA 
       ON PC.SALARYACCOUNTID = SA.ACCOUNTID
LEFT JOIN MACCOUNT DSA 
       ON PCD.SALARYACCOUNTID = DSA.ACCOUNTID
LEFT JOIN MACCOUNT CA 
       ON PCD.CONTRAACCOUNTID = CA.ACCOUNTID
LEFT JOIN MPAYELEMENT PE 
       ON PCD.PAYELEMENTID = PE.PAYELEMENTID
LEFT JOIN MFORMULA TEF 
       ON PC.TOTALEARNINGFORMULAID = TEF.FORMULAID
LEFT JOIN MFORMULA TDF 
       ON PC.TOTALDEDUCTIONFORMULAID = TDF.FORMULAID
LEFT JOIN MFORMULA NSF 
       ON PC.NETSALARYFORMULAID = NSF.FORMULAID
LEFT JOIN MFORMULA C2F 
       ON PC.C2CFORMULAID = C2F.FORMULAID
LEFT JOIN MREPORTFORMAT RF 
       ON PC.REPORTFORMATID = RF.REPORTFORMATID
LEFT JOIN MUSER CBU 
       ON PC.CREATEDBYID = CBU.USERID
LEFT JOIN MUSER MBU 
       ON PC.MODIFIEDBYID = MBU.USERID
WHERE PC.PAYCONFIGURATIONID = @payconfigurationid;
";


        public const string SAVE_PAYCONFIGURATION = @"INSERT INTO MPAYCONFIGURATION
(
    PAYCONFIGURATIONID,
    PAYCONFIGURATIONCODE,
    PAYCONFIGURATIONNAME,
    SALARYACCOUNTID,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    ISPARTIALPROCESSREQUIRED,
    TOTALEARNINGFORMULAID,
    TOTALDEDUCTIONFORMULAID,
    NETSALARYFORMULAID,
    C2CFORMULAID,
    REPORTFORMATID,
    STDSALARYGROSSFORMULAID,
    STDONEDAYSALARYFORMULAID,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @PayConfigurationId,
    @PayConfigurationCode,
    @PayConfigurationName,
    @SalaryAccountId,
    @PayConfigurationSortOrder,
    @PayConfigurationStatus,
    @PayConfigurationVersion,
    @PayConfigurationSourceType,
    @PayConfigurationIsPartialProcessRequired,
    @TotalEarningFormulaId,
    @TotalDeductionFormulaId,
    @NetSalaryFormulaId,
    @C2CFormulaId,
    @ReportFormatId,
    @StdSalaryGrossFormulaId,
    @StdOneDaySalaryFormulaId,
    @PayConfigurationCreatedById,
    @PayConfigurationCreatedOn,
    @PayConfigurationModifiedById,
    @PayConfigurationModifiedOn
);";

        public const string UPDATE_PAYCONFIGURATION = @"UPDATE MPAYCONFIGURATION
SET
    PAYCONFIGURATIONCODE     = @PayConfigurationCode,
    PAYCONFIGURATIONNAME     = @PayConfigurationName,
    SALARYACCOUNTID          = @SalaryAccountId,
    SORTORDER                = @PayConfigurationSortOrder,
    STATUS                   = @PayConfigurationStatus,
    VERSION                  = @PayConfigurationVersion,
    SOURCETYPE               = @PayConfigurationSourceType,
    ISPARTIALPROCESSREQUIRED = @PayConfigurationIsPartialProcessRequired,
    TOTALEARNINGFORMULAID    = @TotalEarningFormulaId,
    TOTALDEDUCTIONFORMULAID  = @TotalDeductionFormulaId,
    NETSALARYFORMULAID       = @NetSalaryFormulaId,
    C2CFORMULAID             = @C2CFormulaId,
    REPORTFORMATID           = @ReportFormatId,
    STDSALARYGROSSFORMULAID  = @StdSalaryGrossFormulaId,
    STDONEDAYSALARYFORMULAID = @StdOneDaySalaryFormulaId,
    MODIFIEDBYID             = @PayConfigurationModifiedById,
    MODIFIEDON               = @PayConfigurationModifiedOn
WHERE
    PAYCONFIGURATIONID = @PayConfigurationId;";

        public const string DELETE_PAYCONFIGURATIONDETAIL_BY_HEADER = @"DELETE FROM MPAYCONFIGURATIONDETAIL
WHERE PAYCONFIGURATIONID = @payconfigurationid;";

        public const string SAVE_PAYCONFIGURATIONDETAIL = @"INSERT INTO MPAYCONFIGURATIONDETAIL
(
    PAYCONFIGURATIONDETAILID,
    PAYCONFIGURATIONID,
    SLNO,
    FIELDTYPE,
    FIELDCODE,
    OPERATIONTYPE,
    ISSEPARATEACCOUNT,
    SALARYACCOUNTID,
    ISEMPLOYEEACCOUNT,
    CONTRAACCOUNTID,
    PAYELEMENTID,
    ISCONTRAACCOUNTMERGE,
    ISSALARYACCOUNTMERGE,
    ACTUALFIELDTYPE,
    ACTUALFIELDNAME,
    ISSALARYCC,
    ISCONTRACC,
    CLAIMTYPE,
    CLAIMHEADID,
    PRINTREQUIRED
)
VALUES
(
    @PayConfigurationDetailId,
    @PayConfigurationId,
    @PayConfigurationDetailSlNo,
    @PayConfigurationDetailFieldType,
    @PayConfigurationDetailFieldCode,
    @PayConfigurationDetailOperationType,
    @PayConfigurationDetailIsSeprateAccount,
    @SalaryAccountId,
    @PayConfigurationDetailIsEmployeeAccount,
    @ContraAccountId,
    @PayelementId,
    @PayConfigurationDetailIsContraAccountMerge,
    @PayConfigurationDetailIsSalaryAccountMerge,
    @PayConfigurationDetailActualFieldType,
    @PayConfigurationDetailActualFieldName,
    @PayConfigurationDetailIsSalaryCostCenter,
    @PayConfigurationDetailIsContraCostCenter,
    @PayConfigurationDetailClaimType,
    @ClaimHeadId,
    @PayConfigurationDetailPrintRequired
);";
    }
}