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

namespace PayRollDAL.Query.Declaration
{
    public static class DeclarationQB
    {
        public const string GET_DECLARATION = @"SELECT
    D.DECLARATIONID AS DeclarationId,
    D.EMPLOYEEID AS EmployeeId,
    E.EMPLOYEECODE AS EmployeeCode,
    E.EMPLOYEENAME AS EmployeeName,
    E.PANNUMBER AS PanNumber,
    D.PERIODID AS PeriodId,
    P.PERIODCODE AS PeriodCode,
    P.PERIODNAME AS PeriodName,
    P.FROMDATE AS PeriodFromDate,
    P.TODATE AS PeriodToDate,
    D.PLANNUMBER AS DeclarationPlanNumber,
    D.SUBMITTEDON AS DeclarationSubmittedOn,
    D.PLANSTATUS AS DeclarationPlanstatus,
    D.PLANDATE AS DeclarationPlanDate,
    D.REMARKS AS DeclarationRemarks,
    D.LOCKSTATUS AS DeclarationLockStatus,
    D.VERSION AS DeclarationVersion,
    D.STATUS AS DeclarationStatus,
    D.CREATEDBYID AS DeclarationCreatedById,
    D.CREATEDON AS DeclarationCreatedOn,
    D.MODIFIEDBYID AS DeclarationModifiedById,
    D.MODIFIEDON AS DeclarationModifiedOn,
    DEP.DEPARTMENTCODE AS DepartmentCode,
    DEP.DEPARTMENTNAME AS DepartmentName,
    D.CALCULATIONMETHOD AS DeclarationCalculationMethod,
    D.NOOFEDUCATION AS DeclarationNoOfEducation,
    D.NOOFHOSTEL AS DeclarationNoOfHostel,
    D.PREVIOUSSALARY AS DeclarationPreviousSalary,
    D.PREVIOUSTDS AS DeclarationPreviousTDS,
    D.PREVIOUSPT AS DeclarationPreviousPT,
    D.PREVIOUSPF AS DeclarationPreviousPF,

    DD.DECLARATIONDETAILID AS DeclarationDetailId,
    DD.DECLARATIONID AS DeclarationId,
    DD.SLNO AS DeclarationDetailSlno,
    DD.DECLARATIONTYPE AS DeclarationDetailType,
    DD.TYPESLNO AS DeclarationDetailTypeSlno,
    DD.PARTICULARS AS DeclarationDetailParticulars,
    DD.PRINCIPLE AS DeclarationDetailPrinciple,
    DD.INTEREST AS DeclarationDetailInterest,
    DD.TDSCONTACTID AS TDSContactId,
    TDC.NAME AS TDSContactName,
    TDC.PAN AS TDSContactPAN,
    DD.DETAILREMARKS AS DeclarationDetailDetailRemarks,
    DD.DETAILSTATUS AS DeclarationDetailDetailStatus,
    DD.FEEDBACK AS DeclarationDetailFeedback,
    DD.APPROVEDAMOUNT AS DeclarationDetailApprovedAmount,
    DD.FROMPERIOD AS DeclarationDetailFromPeriod,
    DD.TOPERIOD AS DeclarationDetailToPeriod,
    DD.RENTRECEIVED AS DeclarationDetailRentReceived,
    DD.LOCALTAX AS DeclarationDetailLocalTax,
    DD.STANDARDDEDUCTION AS DeclarationDetailStandardDeduction,
    DD.NETINCOME AS DeclarationDetailNetIncome,
    DD.TDSSECTIONDETAILID AS TDSSectionDetailId,
    TDS.REMARKS AS TDSSectionDetailRemarks,
    TDS.SECTIONDETAIL AS TDSSectionDetailSectionDetail,
    DD.ISMETRO AS DeclarationDetailIsMetro,
    DD.DECLAREDAMOUNT AS DeclarationDetailDeclaredAmount,
    DD.APPROVERID AS ApproverId,
    U.EMPLOYEECODE AS ApproverCode,
    U.EMPLOYEENAME AS ApproverName,
    DD.APPROVEDON AS DeclarationDetailApprovedOn
    FROM TDECLARATION D
    LEFT JOIN MEMPLOYEE E ON D.EMPLOYEEID = E.EMPLOYEEID
    LEFT JOIN MPERIOD P ON D.PERIODID = P.PERIODID
    LEFT JOIN MDEPARTMENT DEP ON E.DEPARTMENTID = DEP.DEPARTMENTID
    LEFT JOIN TDECLARATIONDETAIL DD ON D.DECLARATIONID = DD.DECLARATIONID
    LEFT JOIN MTDSCONTACT TDC ON DD.TDSCONTACTID = TDC.TDSCONTACTID
    LEFT JOIN MTDSSECTIONDETAIL TDS ON DD.TDSSECTIONDETAILID = TDS.TDSSECTIONDETAILID
    LEFT JOIN MEMPLOYEE U ON DD.APPROVERID = U.EMPLOYEEID
     
    WHERE D.DECLARATIONID = @declarationid;";
        public const string GET_DECLARATION_PG = @"SELECT
                            d.declarationid as ""DeclarationId"",
                            d.employeeid as ""EmployeeId"",
                            e.employeecode as ""EmployeeCode"",
                            e.employeename as ""EmployeeName"",
                            e.pannumber as ""PanNumber"",
                            d.periodid as ""PeriodId"",
                            p.periodcode as ""PeriodCode"",
                            p.periodname as ""PeriodName"",
                            p.fromdate::timestamp as ""PeriodFromDate"",
                            p.todate::timestamp as ""PeriodToDate"",
                            d.plannumber as ""DeclarationPlanNumber"",
                            d.submittedon as ""DeclarationSubmittedOn"",
                            d.planstatus as ""DeclarationPlanstatus"",
                            d.plandate::timestamp as ""DeclarationPlanDate"",
                            d.remarks as ""DeclarationRemarks"",
                            d.lockstatus as ""DeclarationLockStatus"",
                            d.version as ""DeclarationVersion"",
                            d.status as ""DeclarationStatus"",
                            d.createdbyid as ""DeclarationCreatedById"",
                            d.createdon as ""DeclarationCreatedOn"",
                            d.modifiedbyid as ""DeclarationModifiedById"",
                            d.modifiedon as ""DeclarationModifiedOn"",
                            dep.departmentcode as ""DepartmentCode"",
                            dep.departmentname as ""DepartmentName"",
                            d.calculationmethod as ""DeclarationCalculationMethod"",
                            d.noofeducation as ""DeclarationNoOfEducation"",
                            d.noofhostel as ""DeclarationNoOfHostel"",
                            d.previoussalary as ""DeclarationPreviousSalary"",
                            d.previoustds as ""DeclarationPreviousTDS"",
                            d.previouspt as ""DeclarationPreviousPT"",
                            d.previouspf as ""DeclarationPreviousPF"",

                            dd.declarationdetailid as ""DeclarationDetailId"",
                            dd.declarationid as ""DeclarationId"",
                            dd.slno as ""DeclarationDetailSlno"",
                            dd.declarationtype as ""DeclarationDetailType"",
                            dd.typeslno as ""DeclarationDetailTypeSlno"",
                            dd.particulars as ""DeclarationDetailParticulars"",
                            dd.principle as ""DeclarationDetailPrinciple"",
                            dd.interest as ""DeclarationDetailInterest"",
                            dd.tdscontactid as ""TDSContactId"",
                            tdc.name as ""TDSContactName"",
                            tdc.pan as ""TDSContactPAN"",
                            dd.detailremarks as ""DeclarationDetailDetailRemarks"",
                            dd.detailstatus as ""DeclarationDetailDetailStatus"",
                            dd.feedback as ""DeclarationDetailFeedback"",
                            dd.approvedamount as ""DeclarationDetailApprovedAmount"",
                            dd.fromperiod::timestamp as ""DeclarationDetailFromPeriod"",
                            dd.toperiod::timestamp as ""DeclarationDetailToPeriod"",
                            dd.rentreceived as ""DeclarationDetailRentReceived"",
                            dd.localtax as ""DeclarationDetailLocalTax"",
                            dd.standarddeduction as ""DeclarationDetailStandardDeduction"",
                            dd.netincome as ""DeclarationDetailNetIncome"",
                            dd.tdssectiondetailid as ""TDSSectionDetailId"",
                            tds.remarks as ""TDSSectionDetailRemarks"",
                            tds.sectiondetail as ""TDSSectionDetailSectionDetail"",
                            dd.ismetro as ""DeclarationDetailIsMetro"",
                            dd.declaredamount as ""DeclarationDetailDeclaredAmount"",
                            dd.approverid as ""ApproverId"",
                            u.employeecode as ""ApproverCode"",
                            u.employeename as ""ApproverName"",
                            dd.approvedon as ""DeclarationDetailApprovedOn""

                            from tdeclaration d
                            left join memployee e on d.employeeid = e.employeeid
                            left join mperiod p on d.periodid = p.periodid
                            left join mdepartment dep on e.departmentid = dep.departmentid
                            left join tdeclarationdetail dd on d.declarationid = dd.declarationid
                            left join mtdscontact tdc on dd.tdscontactid = tdc.tdscontactid
                            left join mtdssectiondetail tds on dd.tdssectiondetailid = tds.tdssectiondetailid
                            left join memployee u on dd.approverid = u.employeeid
                            where d.declarationid = @declarationid;
                            ";
        public const string GET_DECLARATION_ORACLE = @"";
        public const string GET_DECLARATION_MYSQL = @"";
        public const string SAVE_DECLARATION_ORACLE = @"";
        public const string SAVE_DECLARATION_MYSQL = @"";
        public const string SAVE_DECLARATIONDETAIL_ORACLE = @"";
        public const string SAVE_DECLARATIONDETAIL_MYSQL = @"";
        public const string UPDATE_DECLARATION_ORACLE = @"";
        public const string UPDATE_DECLARATION_MYSQL = @"";
        public const string UPDATE_DECLARATIONDETAIL_ORACLE = @"";
        public const string UPDATE_DECLARATIONDETAIL_MYSQL = @"";
        public const string GET_DECLARATION_COUNT_ORACLE = @"";
        public const string GET_DECLARATION_COUNT_MYSQL = @"";

        public const string SAVE_DECLARATION_PG = @"INSERT INTO tdeclaration
(
    declarationid,
    employeeid,
    periodid,
    plannumber,
    submittedon,
    planstatus,
    plandate,
    remarks,
    lockstatus,
    version,
    status,
    createdbyid,
    createdon,
    modifiedbyid,
    modifiedon,
    calculationmethod,
    noofeducation,
    noofhostel,
    previoussalary,
    previoustds,
    previouspt,
    previouspf
)
VALUES
(
    @DeclarationId,
    @EmployeeId,
    @PeriodId,
    @DeclarationPlanNumber,
    @DeclarationSubmittedOn,
    @DeclarationPlanstatus,
    @DeclarationPlanDate,
    @DeclarationRemarks,
    @DeclarationLockStatus,
    @DeclarationVersion,
    @DeclarationStatus,
    @DeclarationCreatedById,
    @DeclarationCreatedOn,
    @DeclarationModifiedById,
    NOW(),
    @DeclarationCalculationMethod,
    @DeclarationNoOfEducation,
    @DeclarationNoOfHostel,
    @DeclarationPreviousSalary,
    @DeclarationPreviousTDS,
    @DeclarationPreviousPT,
    @DeclarationPreviousPF
);
";
        public const string SAVE_DECLARATIONDETAIL_PG = @"
INSERT INTO tdeclarationdetail
(
    declarationdetailid,
    declarationid,
    slno,
    declarationtype,
    typeslno,
    particulars,
    principle,
    interest,
    tdscontactid,
    detailremarks,
    detailstatus,
    feedback,
    approvedamount,
    fromperiod,
    toperiod,
    rentreceived,
    localtax,
    standarddeduction,
    netincome,
    tdssectiondetailid,
    ismetro,
    declaredamount,
    approverid,
    approvedon
)
VALUES
(
    @DeclarationDetailId,
    @DeclarationId,
    @DeclarationDetailSlno,
    @DeclarationDetailType,
    @DeclarationDetailTypeSlno,
    @DeclarationDetailParticulars,
    @DeclarationDetailPrinciple,
    @DeclarationDetailInterest,
    @TdsContactId,
    @DeclarationDetailDetailRemarks,
    @DeclarationDetailDetailStatus,
    @DeclarationDetailFeedback,
    @DeclarationDetailApprovedAmount,
    @DeclarationDetailFromPeriod,
    @DeclarationDetailToPeriod,
    @DeclarationDetailRentReceived,
    @DeclarationDetailLocalTax,
    @DeclarationDetailStandardDeduction,
    @DeclarationDetailNetIncome,
    @TdsSectionDetailId,
    @DeclarationDetailIsMetro,
    @DeclarationDetailDeclaredAmount,
    @ApproverId,
    @DeclarationDetailApprovedOn
);
";
        public const string UPDATE_DECLARATION_PG = @"UPDATE tdeclaration
SET
    employeeid = @EmployeeId,
    periodid = @PeriodId,
    plannumber = @DeclarationPlanNumber,
    submittedon = @DeclarationSubmittedOn,
    planstatus = @DeclarationPlanstatus,
    plandate = @DeclarationPlanDate,
    remarks = @DeclarationRemarks,
    lockstatus = @DeclarationLockStatus,
    version = @DeclarationVersion,
    status = @DeclarationStatus,
    modifiedbyid = @DeclarationModifiedById,
    modifiedon = NOW(),
    calculationmethod = @DeclarationCalculationMethod,
    noofeducation = @DeclarationNoOfEducation,
    noofhostel = @DeclarationNoOfHostel,
    previoussalary = @DeclarationPreviousSalary,
    previoustds = @DeclarationPreviousTDS,
    previouspt = @DeclarationPreviousPT,
    previouspf = @DeclarationPreviousPF
WHERE declarationid = @DeclarationId;

";
        public const string UPDATE_DECLARATIONDETAIL_PG = @"
UPDATE tdeclarationdetail
SET
    slno = @DeclarationDetailSlno,
    declarationtype = @DeclarationDetailType,
    typeslno = @DeclarationDetailTypeSlno,
    particulars = @DeclarationDetailParticulars,
    principle = @DeclarationDetailPrinciple,
    interest = @DeclarationDetailInterest,
    tdscontactid = @TdsContactId,
    detailremarks = @DeclarationDetailDetailRemarks,
    detailstatus = @DeclarationDetailDetailStatus,
    feedback = @DeclarationDetailFeedback,
    approvedamount = @DeclarationDetailApprovedAmount,
    fromperiod = @DeclarationDetailFromPeriod,
    toperiod = @DeclarationDetailToPeriod,
    rentreceived = @DeclarationDetailRentReceived,
    localtax = @DeclarationDetailLocalTax,
    standarddeduction = @DeclarationDetailStandardDeduction,
    netincome = @DeclarationDetailNetIncome,
    tdssectiondetailid = @TdsSectionDetailId,
    ismetro = @DeclarationDetailIsMetro,
    declaredamount = @DeclarationDetailDeclaredAmount,
    approverid = @ApproverId,
    approvedon = @DeclarationDetailApprovedOn
WHERE declarationdetailid = @DeclarationDetailId;
";
        public const string SAVE_DECLARATION_SQL = @"
INSERT INTO TDECLARATION
(
    DECLARATIONID,
    EMPLOYEEID,
    PERIODID,
    PLANNUMBER,
    SUBMITTEDON,
    PLANSTATUS,
    PLANDATE,
    REMARKS,
    LOCKSTATUS,
    VERSION,
    STATUS,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    CALCULATIONMETHOD,
    NOOFEDUCATION,
    NOOFHOSTEL,
    PREVIOUSSALARY,
    PREVIOUSTDS,
    PREVIOUSPT,
    PREVIOUSPF
)
VALUES
(
    @DeclarationId,
    @EmployeeId,
    @PeriodId,
    @DeclarationPlanNumber,
    @DeclarationSubmittedOn,
    @DeclarationPlanstatus,
    @DeclarationPlanDate,
    @DeclarationRemarks,
    @DeclarationLockStatus,
    @DeclarationVersion,
    @DeclarationStatus,
    @DeclarationCreatedById,
    @DeclarationCreatedOn,
    @DeclarationModifiedById,
    @DeclarationModifiedOn,
    @DeclarationCalculationMethod,
    @DeclarationNoOfEducation,
    @DeclarationNoOfHostel,
    @DeclarationPreviousSalary,
    @DeclarationPreviousTDS,
    @DeclarationPreviousPT,
    @DeclarationPreviousPF
);
";
        public const string UPDATE_DECLARATION_SQL = @"UPDATE TDECLARATION
SET
    EMPLOYEEID = @EmployeeId,
    PERIODID = @PeriodId,
    PLANNUMBER = @DeclarationPlanNumber,
    SUBMITTEDON = @DeclarationSubmittedOn,
    PLANSTATUS = @DeclarationPlanstatus,
    PLANDATE = @DeclarationPlanDate,
    REMARKS = @DeclarationRemarks,
    LOCKSTATUS = @DeclarationLockStatus,
    VERSION = @DeclarationVersion,
    STATUS = @DeclarationStatus,
    MODIFIEDBYID = @DeclarationModifiedById,
    MODIFIEDON = @DeclarationModifiedOn,
    CALCULATIONMETHOD = @DeclarationCalculationMethod,
    NOOFEDUCATION = @DeclarationNoOfEducation,
    NOOFHOSTEL = @DeclarationNoOfHostel,
    PREVIOUSSALARY = @DeclarationPreviousSalary,
    PREVIOUSTDS = @DeclarationPreviousTDS,
    PREVIOUSPT = @DeclarationPreviousPT,
    PREVIOUSPF = @DeclarationPreviousPF
WHERE DECLARATIONID = @DeclarationId;";
        public const string SAVE_DECLARATIONDETAIL_SQL = @"
INSERT INTO dbo.TDECLARATIONDETAIL
(
    DECLARATIONDETAILID,
    DECLARATIONID,
    SLNO,
    DECLARATIONTYPE,
    TYPESLNO,
    PARTICULARS,
    PRINCIPLE,
    INTEREST,
    TDSCONTACTID,
    DETAILREMARKS,
    DETAILSTATUS,
    FEEDBACK,
    APPROVEDAMOUNT,
    FROMPERIOD,
    TOPERIOD,
    RENTRECEIVED,
    LOCALTAX,
    STANDARDDEDUCTION,
    NETINCOME,
    TDSSECTIONDETAILID,
    ISMETRO,
    DECLAREDAMOUNT,
    APPROVERID,
    APPROVEDON
)
VALUES
(
    @DeclarationDetailId,
    @DeclarationId,
    @DeclarationDetailSlno,
    @DeclarationDetailType,
    @DeclarationDetailTypeSlno,
    @DeclarationDetailParticulars,
    @DeclarationDetailPrinciple,
    @DeclarationDetailInterest,
    @TdsContactId,
    @DeclarationDetailDetailRemarks,
    @DeclarationDetailDetailStatus,
    @DeclarationDetailFeedback,
    @DeclarationDetailApprovedAmount,
    @DeclarationDetailFromPeriod,
    @DeclarationDetailToPeriod,
    @DeclarationDetailRentReceived,
    @DeclarationDetailLocalTax,
    @DeclarationDetailStandardDeduction,
    @DeclarationDetailNetIncome,
    @TdsSectionDetailId,
    @DeclarationDetailIsMetro,
    @DeclarationDetailDeclaredAmount,
    @ApproverId,
    @DeclarationDetailApprovedOn
);
";
        public const string UPDATE_DECLARATIONDETAIL_SQL = @"
UPDATE dbo.TDECLARATIONDETAIL
SET
    SLNO = @DeclarationDetailSlno,
    DECLARATIONTYPE = @DeclarationDetailType,
    TYPESLNO = @DeclarationDetailTypeSlno,
    PARTICULARS = @DeclarationDetailParticulars,
    PRINCIPLE = @DeclarationDetailPrinciple,
    INTEREST = @DeclarationDetailInterest,
    TDSCONTACTID = @TdsContactId,
    DETAILREMARKS = @DeclarationDetailDetailRemarks,
    DETAILSTATUS = @DeclarationDetailDetailStatus,
    FEEDBACK = @DeclarationDetailFeedback,
    APPROVEDAMOUNT = @DeclarationDetailApprovedAmount,
    FROMPERIOD = @DeclarationDetailFromPeriod,
    TOPERIOD = @DeclarationDetailToPeriod,
    RENTRECEIVED = @DeclarationDetailRentReceived,
    LOCALTAX = @DeclarationDetailLocalTax,
    STANDARDDEDUCTION = @DeclarationDetailStandardDeduction,
    NETINCOME = @DeclarationDetailNetIncome,
    TDSSECTIONDETAILID = @TdsSectionDetailId,
    ISMETRO = @DeclarationDetailIsMetro,
    DECLAREDAMOUNT = @DeclarationDetailDeclaredAmount,
    APPROVERID = @ApproverId,
    APPROVEDON = @DeclarationDetailApprovedOn 
WHERE DECLARATIONDETAILID = @DeclarationDetailId;
";
        public const string GET_DECLARATION_COUNT_SQL = @"
SELECT COUNT(1)
FROM dbo.TDECLARATION
WHERE EMPLOYEEID = @EmployeeId
  AND PERIODID = @PeriodId
  AND PLANNUMBER = @DeclarationPlanNumber
  AND DECLARATIONID <> @DeclarationId;
";
        public const string GET_DECLARATION_COUNT_PG = @"
SELECT COUNT(1)
FROM tdeclaration
WHERE employeeid = @EmployeeId
  AND periodid = @PeriodId
  AND plannumber = @DeclarationPlanNumber
  AND declarationid <> @DeclarationId;
";
        public const string GET_NEXT_DECLARATIONDETAILID_SQL =@"SELECT ISNULL(MAX(DECLARATIONDETAILID),0) + 1 FROM TDECLARATIONDETAIL";
        public const string GET_NEXT_DECLARATIONDETAILID_PG = @"SELECT COALESCE(MAX(declarationdetailid),0) + 1 FROM tdeclarationdetail";
        // Delete DeclarationDetail queries (SINGULAR, not plural)
        public const string DELETE_DECLARATIONDETAIL_SQL = @" DELETE FROM TDECLARATIONDETAIL WHERE DeclarationId = @DeclarationId";
        public const string DELETE_DECLARATIONDETAIL_PG = @" DELETE FROM tdeclarationdetail WHERE declarationid = @DeclarationId";
        public const string DELETE_DECLARATIONDETAIL_ORACLE = @" DELETE FROM TDECLARATIONDETAIL WHERE DECLARATIONID = :DeclarationId";
        public const string DELETE_DECLARATIONDETAIL_MYSQL = @"  DELETE FROM TDECLARATIONDETAIL WHERE DeclarationId = @DeclarationId";
        //Delete DeclarationDetail queries (DeclarationDetailId)
        public const string DELETEDECLARATIONDETAIL_SQL = @"DELETE FROM TDECLARATIONDETAIL WHERE DECLARATIONDETAILID = @DeclarationDetailId";
        public const string DELETEDECLARATIONDETAIL_PG = @"
DELETE FROM tdeclarationdetail 
WHERE declarationdetailid = @DeclarationDetailId";

        public const string DELETEDECLARATIONDETAIL_ORACLE = @"
DELETE FROM TDECLARATIONDETAIL 
WHERE DECLARATIONDETAILID = :DeclarationDetailId";

        public const string DELETEDECLARATIONDETAIL_MYSQL = @"
DELETE FROM TDECLARATIONDETAIL 
WHERE DECLARATIONDETAILID = @DeclarationDetailId";

        // Delete Declaration queries
        public const string DELETE_DECLARATION_SQL = @"
    DELETE FROM TDECLARATION WHERE DeclarationId = @DeclarationId";

        public const string DELETE_DECLARATION_PG = @"
    DELETE FROM tdeclaration WHERE declarationid = @DeclarationId";

        public const string DELETE_DECLARATION_ORACLE = @"
    DELETE FROM TDECLARATION WHERE DECLARATIONID = :DeclarationId";

        public const string DECLARATIONBULKUPDATE = @"UPDATE TDECLARATION
                                    SET LOCKSTATUS = @LockStatus
                                    WHERE DECLARATIONID = @DeclarationId";

        public const string DECLARATIONDETAILBULKUPDATE_SQL = @"
            UPDATE TDECLARATIONDETAIL
            SET    DETAILSTATUS   = @DeclarationDetailDetailStatus,
                   FEEDBACK       = @DeclarationDetailFeedback,
                   APPROVEDAMOUNT = @DeclarationDetailApprovedAmount,
                   APPROVERID     = @ApproverId,
                   APPROVEDON     = GETDATE()
            WHERE  DECLARATIONDETAILID = @DeclarationDetailId";

        public const string DECLARATIONDETAILBULKUPDATE_PG = @"
            UPDATE tdeclarationdetail
            SET    detailstatus   = @DeclarationDetailDetailStatus,
                   feedback       = @DeclarationDetailFeedback,
                   approvedamount = @DeclarationDetailApprovedAmount,
                   approverid     = @ApproverId,
                   approvedon     = NOW()
            WHERE  declarationdetailid = @DeclarationDetailId";

        public const string DELETE_DECLARATION_MYSQL = @"
    DELETE FROM TDECLARATION WHERE DeclarationId = @DeclarationId";
       
        public const string GET_SELECTLIST_DECLARATIONVIEW_ORACLE = @"";
        public const string GET_SELECTLIST_DECLARATIONVIEW_MYSQL = @"";
        public const string GET_DECLARATION_FROM_EMPLOYEE_ORACLE = @"";
        public const string GET_DECLARATION_FROM_EMPLOYEE_MYSQL = @"";

        public const string GET_SELECTLIST_DECLARATIONVIEW_SQL = @"
WITH RankedDeclarations AS (
    SELECT
        DECL.DECLARATIONID,
        EMP.EMPLOYEECODE,
        ROW_NUMBER() OVER (ORDER BY EMP.EMPLOYEECODE) AS RowNum
    FROM TDECLARATION DECL
    INNER JOIN MEMPLOYEE EMP ON DECL.EMPLOYEEID = EMP.EMPLOYEEID
    LEFT  JOIN MDEPARTMENT DEP ON EMP.DEPARTMENTID = DEP.DEPARTMENTID
    WHERE 1 = 1
    {CRITERIA_PLACEHOLDER}
),
FilteredDeclarations AS (
    SELECT DECLARATIONID
    FROM RankedDeclarations
    WHERE (@firstnumber = -1 AND @maxresult = -1)
       OR RowNum BETWEEN @firstnumber AND (@firstnumber + @maxresult - 1)
)
SELECT
    -- Parent: TDECLARATION
    DECL.DECLARATIONID                              AS DeclarationId,
    DECL.EMPLOYEEID                                 AS EmployeeId,
    EMP.EMPLOYEECODE                                AS EmployeeCode,
    EMP.EMPLOYEENAME                                AS EmployeeName,
    EMP.PANNUMBER                                   AS PanNumber,
    EMP.THUMBNAIL                                   AS EmployeeThumbNail,
    EMP.DEPARTMENTID                                AS DepartmentId,
    DEP.DEPARTMENTCODE                              AS DepartmentCode,
    DEP.DEPARTMENTNAME                              AS DepartmentName,
    DECL.PERIODID                                   AS PeriodId,
    PER.PERIODCODE                                  AS PeriodCode,
    PER.PERIODNAME                                  AS PeriodName,
    DECL.PLANNUMBER                                 AS DeclarationPlanNumber,
    DECL.SUBMITTEDON                                AS DeclarationSubmittedOn,
    DECL.PLANSTATUS                                 AS DeclarationPlanstatus,
    DECL.PLANDATE                                   AS DeclarationPlanDate,
    DECL.REMARKS                                    AS DeclarationRemarks,
    DECL.LOCKSTATUS                                 AS DeclarationLockStatus,
    DECL.CALCULATIONMETHOD                          AS DeclarationCalculationMethod,
    DECL.NOOFEDUCATION                              AS DeclarationNoOfEducation,
    DECL.NOOFHOSTEL                                 AS DeclarationNoOfHostel,
    DECL.VERSION                                    AS DeclarationVersion,
    DECL.STATUS                                     AS DeclarationStatus,
    DECL.CREATEDBYID                                AS DeclarationCreatedById,
    DECL.CREATEDON                                  AS DeclarationCreatedOn,
    DECL.MODIFIEDBYID                               AS DeclarationModifiedById,
    DECL.MODIFIEDON                                 AS DeclarationModifiedOn,
    CAST(DECL.PREVIOUSSALARY AS DOUBLE PRECISION)   AS DeclarationPreviousSalary,
    CAST(DECL.PREVIOUSTDS    AS DOUBLE PRECISION)   AS DeclarationPreviousTDS,
    CAST(DECL.PREVIOUSPT     AS DOUBLE PRECISION)   AS DeclarationPreviousPT,
    CAST(DECL.PREVIOUSPF     AS DOUBLE PRECISION)   AS DeclarationPreviousPF,

    -- Child: TDECLARATIONDETAIL
    DD.DECLARATIONDETAILID                          AS DeclarationDetailId,
    DD.DECLARATIONID                                AS DeclarationId,
    DD.SLNO                                         AS DeclarationDetailSlno,
    DD.DECLARATIONTYPE                              AS DeclarationDetailType,
    DD.TYPESLNO                                     AS DeclarationDetailTypeSlno,
    DD.PARTICULARS                                  AS DeclarationDetailParticulars,
    DD.PRINCIPLE                                    AS DeclarationDetailPrinciple,
    DD.INTEREST                                     AS DeclarationDetailInterest,
    DD.TDSCONTACTID                                 AS TDSContactId,
    COALESCE(TDC.NAME,          '')                 AS TDSContactName,
    COALESCE(TDC.PAN,           '')                 AS TDSContactPAN,
    DD.DETAILREMARKS                                AS DeclarationDetailDetailRemarks,
    DD.DETAILSTATUS                                 AS DeclarationDetailDetailStatus,
    DD.FEEDBACK                                     AS DeclarationDetailFeedback,
    DD.APPROVEDAMOUNT                               AS DeclarationDetailApprovedAmount,
    DD.FROMPERIOD                                   AS DeclarationDetailFromPeriod,
    DD.TOPERIOD                                     AS DeclarationDetailToPeriod,
    DD.RENTRECEIVED                                 AS DeclarationDetailRentReceived,
    DD.LOCALTAX                                     AS DeclarationDetailLocalTax,
    DD.STANDARDDEDUCTION                            AS DeclarationDetailStandardDeduction,
    DD.NETINCOME                                    AS DeclarationDetailNetIncome,
    DD.TDSSECTIONDETAILID                           AS TDSSectionDetailId,
    COALESCE(TDS.REMARKS,       '')                 AS TDSSectionDetailRemarks,
    COALESCE(TDS.SECTIONDETAIL, '')                 AS TDSSectionDetailSectionDetail,
    CASE 
        WHEN DD.TDSSECTIONDETAILID = -1 
          OR DD.TDSSECTIONDETAILID IS NULL 
        THEN COALESCE(DD.PARTICULARS, '')           -- fallback: use DD.PARTICULARS when no TDS link
        ELSE COALESCE(TDS.PARTICULARS, '')          -- use TDS.PARTICULARS when valid link exists
    END                                             AS TDSParticular,
    DD.ISMETRO                                      AS DeclarationDetailIsMetro,
    DD.DECLAREDAMOUNT                               AS DeclarationDetailDeclaredAmount,
    DD.APPROVERID                                   AS ApproverId,
    COALESCE(U.EMPLOYEECODE,    '')                 AS ApproverCode,
    COALESCE(U.EMPLOYEENAME,    '')                 AS ApproverName,
    DD.APPROVEDON                                   AS DeclarationDetailApprovedOn

FROM FilteredDeclarations FD
INNER JOIN TDECLARATION       DECL  ON DECL.DECLARATIONID       = FD.DECLARATIONID
INNER JOIN MEMPLOYEE          EMP   ON EMP.EMPLOYEEID            = DECL.EMPLOYEEID
INNER JOIN MPERIOD            PER   ON PER.PERIODID              = DECL.PERIODID
LEFT  JOIN MDEPARTMENT        DEP   ON DEP.DEPARTMENTID          = EMP.DEPARTMENTID
LEFT  JOIN TDECLARATIONDETAIL DD    ON DD.DECLARATIONID          = DECL.DECLARATIONID
LEFT  JOIN MTDSCONTACT        TDC   ON TDC.TDSCONTACTID          = DD.TDSCONTACTID
LEFT  JOIN MTDSSECTIONDETAIL  TDS   ON TDS.TDSSECTIONDETAILID    = DD.TDSSECTIONDETAILID
LEFT  JOIN MEMPLOYEE          U     ON U.EMPLOYEEID              = DD.APPROVERID

ORDER BY EMP.EMPLOYEECODE, DD.DECLARATIONDETAILID
";
        public const string GET_SELECTLIST_DECLARATIONVIEW_PG = @"
WITH RankedDeclarations AS (
    SELECT
        decl.declarationid,
        emp.employeecode,
        ROW_NUMBER() OVER (ORDER BY emp.employeecode) AS row_num
    FROM tdeclaration decl
    INNER JOIN memployee emp ON decl.employeeid = emp.employeeid
    LEFT  JOIN mdepartment dep ON emp.departmentid = dep.departmentid
    WHERE 1 = 1
    {CRITERIA_PLACEHOLDER}
),
FilteredDeclarations AS (
    SELECT declarationid
    FROM RankedDeclarations
    WHERE (@firstnumber = -1 AND @maxresult = -1)
       OR row_num BETWEEN @firstnumber AND (@firstnumber + @maxresult - 1)
)
SELECT
    -- Parent: tdeclaration
    decl.declarationid                                  AS ""DeclarationId"",
    decl.employeeid                                     AS ""EmployeeId"",
    emp.employeecode                                    AS ""EmployeeCode"",
    emp.employeename                                    AS ""EmployeeName"",
    emp.pannumber                                       AS ""PanNumber"",
    emp.thumbnail                                       AS ""EmployeeThumbNail"",
    emp.departmentid                                    AS ""DepartmentId"",
    dep.departmentcode                                  AS ""DepartmentCode"",
    dep.departmentname                                  AS ""DepartmentName"",
    decl.periodid                                       AS ""PeriodId"",
    per.periodcode                                      AS ""PeriodCode"",
    per.periodname                                      AS ""PeriodName"",
    decl.plannumber                                     AS ""DeclarationPlanNumber"",
    decl.submittedon                                    AS ""DeclarationSubmittedOn"",
    decl.planstatus                                     AS ""DeclarationPlanstatus"",
    decl.plandate                                       AS ""DeclarationPlanDate"",
    decl.remarks                                        AS ""DeclarationRemarks"",
    decl.lockstatus                                     AS ""DeclarationLockStatus"",
    decl.calculationmethod                              AS ""DeclarationCalculationMethod"",
    decl.noofeducation                                  AS ""DeclarationNoOfEducation"",
    decl.noofhostel                                     AS ""DeclarationNoOfHostel"",
    decl.version                                        AS ""DeclarationVersion"",
    decl.status                                         AS ""DeclarationStatus"",
    decl.createdbyid                                    AS ""DeclarationCreatedById"",
    decl.createdon                                      AS ""DeclarationCreatedOn"",
    decl.modifiedbyid                                   AS ""DeclarationModifiedById"",
    decl.modifiedon                                     AS ""DeclarationModifiedOn"",
    CAST(decl.previoussalary AS DOUBLE PRECISION)       AS ""DeclarationPreviousSalary"",
    CAST(decl.previoustds    AS DOUBLE PRECISION)       AS ""DeclarationPreviousTDS"",
    CAST(decl.previouspt     AS DOUBLE PRECISION)       AS ""DeclarationPreviousPT"",
    CAST(decl.previouspf     AS DOUBLE PRECISION)       AS ""DeclarationPreviousPF"",

    -- Child: tdeclarationdetail
    dd.declarationdetailid                              AS ""DeclarationDetailId"",
    dd.declarationid                                    AS ""DeclarationId"",
    dd.slno                                             AS ""DeclarationDetailSlno"",
    dd.declarationtype                                  AS ""DeclarationDetailType"",
    dd.typeslno                                         AS ""DeclarationDetailTypeSlno"",
    dd.particulars                                      AS ""DeclarationDetailParticulars"",
    dd.principle                                        AS ""DeclarationDetailPrinciple"",
    dd.interest                                         AS ""DeclarationDetailInterest"",
    dd.tdscontactid                                     AS ""TDSContactId"",
    COALESCE(tdc.name,          '')                     AS ""TDSContactName"",
    COALESCE(tdc.pan,           '')                     AS ""TDSContactPAN"",
    dd.detailremarks                                    AS ""DeclarationDetailDetailRemarks"",
    dd.detailstatus                                     AS ""DeclarationDetailDetailStatus"",
    dd.feedback                                         AS ""DeclarationDetailFeedback"",
    dd.approvedamount                                   AS ""DeclarationDetailApprovedAmount"",
    dd.fromperiod                                       AS ""DeclarationDetailFromPeriod"",
    dd.toperiod                                         AS ""DeclarationDetailToPeriod"",
    dd.rentreceived                                     AS ""DeclarationDetailRentReceived"",
    dd.localtax                                         AS ""DeclarationDetailLocalTax"",
    dd.standarddeduction                                AS ""DeclarationDetailStandardDeduction"",
    dd.netincome                                        AS ""DeclarationDetailNetIncome"",
    dd.tdssectiondetailid                               AS ""TDSSectionDetailId"",
    COALESCE(tds.remarks,       '')                     AS ""TDSSectionDetailRemarks"",
    COALESCE(tds.sectiondetail, '')                     AS ""TDSSectionDetailSectionDetail"",
    CASE
        WHEN dd.tdssectiondetailid = -1
          OR dd.tdssectiondetailid IS NULL
        THEN COALESCE(dd.particulars, '')               -- fallback to DD.PARTICULARS when no TDS link
        ELSE COALESCE(tds.particulars, '')              -- use TDS.PARTICULARS when valid link exists
    END                                                 AS ""TDSParticular"",
    dd.ismetro                                          AS ""DeclarationDetailIsMetro"",
    dd.declaredamount                                   AS ""DeclarationDetailDeclaredAmount"",
    dd.approverid                                       AS ""ApproverId"",
    COALESCE(u.employeecode,    '')                     AS ""ApproverCode"",
    COALESCE(u.employeename,    '')                     AS ""ApproverName"",
    dd.approvedon                                       AS ""DeclarationDetailApprovedOn""

FROM FilteredDeclarations fd
INNER JOIN tdeclaration       decl  ON decl.declarationid       = fd.declarationid
INNER JOIN memployee          emp   ON emp.employeeid            = decl.employeeid
INNER JOIN mperiod            per   ON per.periodid              = decl.periodid
LEFT  JOIN mdepartment        dep   ON dep.departmentid          = emp.departmentid
LEFT  JOIN tdeclarationdetail dd    ON dd.declarationid          = decl.declarationid
LEFT  JOIN mtdscontact        tdc   ON tdc.tdscontactid          = dd.tdscontactid
LEFT  JOIN mtdssectiondetail  tds   ON tds.tdssectiondetailid    = dd.tdssectiondetailid
LEFT  JOIN memployee          u     ON u.employeeid              = dd.approverid

ORDER BY emp.employeecode, dd.declarationdetailid
";
        public const string GET_DECLARATION_FROM_EMPLOYEE_SQL = @"
SELECT
    0                        AS DeclarationId,
    EMP.EMPLOYEEID           AS EmployeeId,
    EMP.EMPLOYEECODE         AS EmployeeCode,
    EMP.EMPLOYEENAME         AS EmployeeName,
    EMP.PANNUMBER            AS PanNumber,
    EMP.DEPARTMENTID         AS DepartmentId,
    DEP.DEPARTMENTCODE       AS DepartmentCode,
    DEP.DEPARTMENTNAME       AS DepartmentName,
    {0}                      AS PeriodId,
    PER.PERIODCODE           AS PeriodCode,
    PER.PERIODNAME           AS PeriodName,
    ''                       AS DeclarationRemarks
FROM MEMPLOYEE EMP
LEFT  JOIN MDEPARTMENT DEP ON EMP.DEPARTMENTID = DEP.DEPARTMENTID
INNER JOIN MPERIOD     PER ON PER.PERIODID = {0}
WHERE NOT EXISTS (
    SELECT 1
    FROM TDECLARATION AA
    WHERE AA.EMPLOYEEID = EMP.EMPLOYEEID
      AND AA.PERIODID = {0}
)
";
        public const string GET_DECLARATION_FROM_EMPLOYEE_PG = @"
SELECT
    0                       AS ""DeclarationId"",
    emp.employeeid          AS ""EmployeeId"",
    emp.employeecode        AS ""EmployeeCode"",
    emp.employeename        AS ""EmployeeName"",
    emp.departmentid        AS ""DepartmentId"",
    emp.pannumber           AS ""PanNumber"",
    dep.departmentcode      AS ""DepartmentCode"",
    dep.departmentname      AS ""DepartmentName"",
    {0}                     AS ""PeriodId"",
    per.periodcode          AS ""PeriodCode"",
    per.periodname          AS ""PeriodName"",
    ''                      AS ""DeclarationRemarks""
FROM memployee emp
LEFT  JOIN mdepartment dep ON emp.departmentid = dep.departmentid
INNER JOIN mperiod     per ON per.periodid = {0}
WHERE NOT EXISTS (
    SELECT 1
    FROM tdeclaration aa
    WHERE aa.employeeid = emp.employeeid
      AND aa.periodid = {0}
)
";
    }
}