namespace PayRollDAL.Query.Perq
{
    public static class PerqQB
    {
        public const string GET_PERQ = @"SELECT
    P.PERQID AS PerqId,

    P.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    P.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName,

    P.PERIODID AS PeriodId,
    PR.PERIODCODE AS PeriodCode,
    PR.PERIODNAME AS PeriodName,
    PR.FROMDATE AS PeriodFromDate,
    PR.TODATE AS PeriodToDate,

    P.PERQNUMBER AS PerqNumber,
    P.PERQDATE AS PerqPerqDate,
    P.REFERENCENUMBER AS PerqReferenceNumber,
    P.REFERENCEDATE AS PerqReferenceDate,

    P.EMPLOYEEID AS EmployeeId,
    E.EMPLOYEECODE AS EmployeeCode,
    E.EMPLOYEENAME AS EmployeeName,

    P.PREVIOUSEMPLOYERID AS PreviousEmployerId,
    TC.PAN AS PreviousEmployerCode,
    TC.NAME AS PreviousEmployerName,

    P.TYPE AS PerqType,
    P.PERIODFROM AS PerqPeriodFrom,
    P.PERIODTO AS PerqPeriodTo,
    P.REMARKS AS PerqRemarks,
    P.SALARYOTHERTHANPERQ AS PerqSalaryOtherThanPerq,
    P.ISDIRECTOR AS PerqIsDirector,
    P.PROFITINLIEUOFSALARY AS PerqProfitInLiueOfSalary,
    P.TDSDEDUCTED AS PerqTdsDeducted,
    P.TDSEMPLOYER AS PerqTdsEmployer,
    P.TDSPAID AS PerqTdsPaid,
    P.DATEOFPAYMENT AS PerqDateOfPayment,
    P.INCHARGE AS PerqIncharge,
    P.DESIGNATION AS PerqDesignation,
    P.TOTALPERQVALUE AS PerqTotalPerqValue,

    P.STATUS AS PerqStatus,
    P.VERSION AS PerqVersion,

    P.CREATEDBYID AS PerqCreatedById,
    P.CREATEDON AS PerqCreatedOn,
    P.MODIFIEDBYID AS PerqModifiedById,
    P.MODIFIEDON AS PerqModifiedOn

FROM TPERQ P
LEFT JOIN MORGANIZATIONUNIT OU     ON P.OUID = OU.OUID
LEFT JOIN MBIZTRANSACTIONTYPE BTT  ON P.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MPERIOD PR                ON P.PERIODID = PR.PERIODID
LEFT JOIN MEMPLOYEE E               ON P.EMPLOYEEID = E.EMPLOYEEID
LEFT JOIN MTDSCONTACT TC            ON P.PREVIOUSEMPLOYERID = TC.TDSCONTACTID
WHERE P.PERQID = @perqid;
";

        public const string GET_PERQDETAIL = @"SELECT
    PD.PERQDETAILID AS PerqDetailId,
    PD.SLNO AS PerqDetailSlNo,

    PD.PERQNATUREID AS PerqNatureId,
    G.GCMCODE AS PerqNatureCode,
    G.GCMNAME AS PerqNatureName,

    PD.PERQVALUE AS PerqDetailPerqValue,
    PD.CHARGED AS PerqDetailCharged,
    PD.PERQNETVALUE AS PerqDetailPerqNetValue,
    PD.REMARKS AS PerqDetailRemarks

FROM TPERQDETAIL PD
LEFT JOIN MGCM G ON PD.PERQNATUREID = G.GCMID
WHERE PD.PERQID = @perqid
ORDER BY PD.SLNO;
";

        public const string GET_SELECTLIST_PERQ = @"
                            WITH PagedPerq AS (
                            SELECT
                            P.PERQID AS Id,
                            P.PERQNUMBER AS Number,
                            P.PERQDATE AS PerqDate,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            P.TYPE AS Type,
                            P.TOTALPERQVALUE AS TotalPerqValue,
                            P.STATUS AS Status,
                            ROW_NUMBER() OVER (ORDER BY P.PERQID) AS RowNum
                            FROM TPERQ P
                            LEFT JOIN MEMPLOYEE E ON P.EMPLOYEEID = E.EMPLOYEEID
                            )
                            SELECT
                            Id,
                            Number,
                            PerqDate,
                            EmployeeCode,
                            EmployeeName,
                            Type,
                            TotalPerqValue,
                            Status
                            FROM PagedPerq
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

        public const string SAVE_PERQ = @"INSERT INTO TPERQ
(
    PERQID,
    OUID,
    BIZTRANSACTIONTYPEID,
    PERIODID,
    PERQNUMBER,
    PERQDATE,
    REFERENCENUMBER,
    REFERENCEDATE,
    EMPLOYEEID,
    PREVIOUSEMPLOYERID,
    TYPE,
    PERIODFROM,
    PERIODTO,
    REMARKS,
    SALARYOTHERTHANPERQ,
    ISDIRECTOR,
    PROFITINLIEUOFSALARY,
    TDSDEDUCTED,
    TDSEMPLOYER,
    TDSPAID,
    DATEOFPAYMENT,
    INCHARGE,
    DESIGNATION,
    TOTALPERQVALUE,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @PerqId,
    @OUId,
    @BIZTransactionTypeId,
    @PeriodId,
    @PerqNumber,
    @PerqPerqDate,
    @PerqReferenceNumber,
    @PerqReferenceDate,
    @EmployeeId,
    @PreviousEmployerId,
    @PerqType,
    @PerqPeriodFrom,
    @PerqPeriodTo,
    @PerqRemarks,
    @PerqSalaryOtherThanPerq,
    @PerqIsDirector,
    @PerqProfitInLiueOfSalary,
    @PerqTdsDeducted,
    @PerqTdsEmployer,
    @PerqTdsPaid,
    @PerqDateOfPayment,
    @PerqIncharge,
    @PerqDesignation,
    @PerqTotalPerqValue,
    @PerqStatus,
    @PerqVersion,
    @PerqCreatedById,
    @PerqCreatedOn,
    @PerqModifiedById,
    @PerqModifiedOn
);";

        public const string UPDATE_PERQ = @"UPDATE TPERQ
    SET
        OUID                    = @OUId,
        BIZTRANSACTIONTYPEID    = @BIZTransactionTypeId,
        PERIODID                = @PeriodId,
        REFERENCENUMBER         = @PerqReferenceNumber,
        REFERENCEDATE           = @PerqReferenceDate,
        EMPLOYEEID              = @EmployeeId,
        PREVIOUSEMPLOYERID      = @PreviousEmployerId,
        TYPE                    = @PerqType,
        PERIODFROM              = @PerqPeriodFrom,
        PERIODTO                = @PerqPeriodTo,
        REMARKS                 = @PerqRemarks,
        SALARYOTHERTHANPERQ     = @PerqSalaryOtherThanPerq,
        ISDIRECTOR              = @PerqIsDirector,
        PROFITINLIEUOFSALARY    = @PerqProfitInLiueOfSalary,
        TDSDEDUCTED             = @PerqTdsDeducted,
        TDSEMPLOYER             = @PerqTdsEmployer,
        TDSPAID                 = @PerqTdsPaid,
        DATEOFPAYMENT           = @PerqDateOfPayment,
        INCHARGE                = @PerqIncharge,
        DESIGNATION             = @PerqDesignation,
        TOTALPERQVALUE          = @PerqTotalPerqValue,
        STATUS                  = @PerqStatus,
        VERSION                 = @PerqVersion,
        MODIFIEDBYID            = @PerqModifiedById,
        MODIFIEDON              = @PerqModifiedOn
    WHERE
        PERQID = @PerqId;";

        public const string SAVE_PERQDETAIL = @"INSERT INTO TPERQDETAIL
(
    PERQDETAILID,
    PERQID,
    PERQNATUREID,
    SLNO,
    PERQVALUE,
    CHARGED,
    PERQNETVALUE,
    REMARKS
)
VALUES
(
    @PerqDetailId,
    @PerqId,
    @PerqNatureId,
    @PerqDetailSlNo,
    @PerqDetailPerqValue,
    @PerqDetailCharged,
    @PerqDetailPerqNetValue,
    @PerqDetailRemarks
);";

        public const string DELETE_PERQDETAIL_BY_PERQID = @"DELETE FROM TPERQDETAIL
                                            WHERE PERQID = @perqid;";
    }
}