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

namespace PayRollDAL.Query.AdvanceRepayment
{
    public static class AdvanceRepaymentQB
    {
        public const string GET_ADVANCEREPAYMENT = @"SELECT
    AR.REPAYMENTID AS AdvanceRepaymentId,
    AR.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    AR.ADVANCEID AS AdvanceId,
    A.ADVANCENUMBER AS AdvanceAdvanceNumber,

    AR.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,

    AR.REPAYMENTNUMBER AS AdvanceRepaymentRepaymentNumber,
    AR.REPAYMENTDATE AS AdvanceRepaymentRepaymentDate,
    AR.NOOFINSTALLMENTSREPAID AS AdvanceRepaymentBalanceInstallment,
    AR.AMOUNTREPAID AS AdvanceRepaymentAmountRepaid,

    AR.PAYMENTTYPEID AS PaymentTypeId,
    PT.INSTRUMENTNAME AS PaymentTypeName,

    AR.CHEQUENO AS AdvanceRepaymentChequeNo,
    AR.CHEQUEDATE AS AdvanceRepaymentChequeDate,
    AR.NATURE AS AdvanceRepaymentNature,
    AR.REMARKS AS AdvanceRepaymentRemarks,

    AR.BANKID AS BankId,
    B.GCMCODE AS BankCode,
    B.GCMNAME AS BankName,

    AR.STATUS AS AdvanceRepaymentStatus,
    AR.VERSION AS AdvanceRepaymentVersion,

    AR.CREATEDBYID AS AdvanceRepaymentCreatedById,
    AR.CREATEDON AS AdvanceRepaymentCreatedOn,
    AR.MODIFIEDBYID AS AdvanceRepaymentModifiedById,
    AR.MODIFIEDON AS AdvanceRepaymentModifiedOn

FROM TADVANCEREPAYMENT AR
LEFT JOIN MORGANIZATIONUNIT OU     ON AR.OUID = OU.OUID
LEFT JOIN TADVANCE A                ON AR.ADVANCEID = A.ADVANCEID
LEFT JOIN MBIZTRANSACTIONTYPE BTT  ON AR.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MINSTRUMENT PT           ON AR.PAYMENTTYPEID = PT.INSTRUMENTID
LEFT JOIN MGCM B                    ON AR.BANKID = B.GCMID
WHERE AR.REPAYMENTID = @advancerepaymentid;
";

        // GetAdvanceRepaymentList: modern equivalent of the legacy GB4
        // Criteria.svc/List ObjectCode=ADVANCEREPAYMENT contract. Callers post a
        // CriteriaDTO (SectionCriteriaList/AttributesCriteriaList); AdvanceRepaymentDAL
        // remaps whitelisted FieldNames (see _filterableFields) before
        // CriteriaBuilder.Build fills in /*CRITERIA*/.
        public const string GET_ADVANCEREPAYMENT_LIST = @"SELECT
    AR.REPAYMENTID AS AdvanceRepaymentId,
    AR.OUID AS OUId,
    OU.ORGANIZATIONUNITCODE AS OUCode,
    OU.ORGANIZATIONUNITNAME AS OUName,

    AR.ADVANCEID AS AdvanceId,
    A.ADVANCENUMBER AS AdvanceAdvanceNumber,
    A.EMPLOYEEID AS EmployeeId,
    A.ADVANCEDATE AS AdvanceDate,

    AR.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
    BTT.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,

    AR.REPAYMENTNUMBER AS AdvanceRepaymentRepaymentNumber,
    AR.REPAYMENTDATE AS AdvanceRepaymentRepaymentDate,
    AR.NOOFINSTALLMENTSREPAID AS AdvanceRepaymentBalanceInstallment,
    AR.AMOUNTREPAID AS AdvanceRepaymentAmountRepaid,

    AR.PAYMENTTYPEID AS PaymentTypeId,
    PT.INSTRUMENTNAME AS PaymentTypeName,

    AR.CHEQUENO AS AdvanceRepaymentChequeNo,
    AR.CHEQUEDATE AS AdvanceRepaymentChequeDate,
    AR.NATURE AS AdvanceRepaymentNature,
    AR.REMARKS AS AdvanceRepaymentRemarks,

    AR.BANKID AS BankId,
    B.GCMCODE AS BankCode,
    B.GCMNAME AS BankName,

    AR.STATUS AS AdvanceRepaymentStatus,
    AR.VERSION AS AdvanceRepaymentVersion,

    AR.CREATEDBYID AS AdvanceRepaymentCreatedById,
    AR.CREATEDON AS AdvanceRepaymentCreatedOn,
    AR.MODIFIEDBYID AS AdvanceRepaymentModifiedById,
    AR.MODIFIEDON AS AdvanceRepaymentModifiedOn

FROM TADVANCEREPAYMENT AR
LEFT JOIN MORGANIZATIONUNIT OU     ON AR.OUID = OU.OUID
LEFT JOIN TADVANCE A                ON AR.ADVANCEID = A.ADVANCEID
LEFT JOIN MBIZTRANSACTIONTYPE BTT  ON AR.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MINSTRUMENT PT           ON AR.PAYMENTTYPEID = PT.INSTRUMENTID
LEFT JOIN MGCM B                    ON AR.BANKID = B.GCMID
WHERE 1 = 1
/*CRITERIA*/
ORDER BY AR.REPAYMENTID;
";

        public const string GET_SELECTLIST_ADVANCEREPAYMENT = @"
                            WITH PagedAdvanceRepayment AS (
                            SELECT
                            AR.REPAYMENTID AS Id,
                            AR.REPAYMENTNUMBER AS RepaymentNumber,
                            AR.REPAYMENTDATE AS RepaymentDate,
                            A.ADVANCENUMBER AS AdvanceNumber,
                            A.ADVANCEDATE AS AdvanceDate,
                            A.NUMBEROFINSTALLMENT AS NumberofInstallment,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            AR.AMOUNTREPAID AS AmountPaid,
                            A.MONTHDEDUCTION AS MonthDeduction,
                            CAST(AR.NATURE AS VARCHAR(20)) AS Nature,
                            ROW_NUMBER() OVER (ORDER BY AR.REPAYMENTID) AS RowNum
                            FROM TADVANCEREPAYMENT AR
                            LEFT JOIN TADVANCE A    ON AR.ADVANCEID = A.ADVANCEID
                            LEFT JOIN MEMPLOYEE E   ON A.EMPLOYEEID = E.EMPLOYEEID
                            )
                            SELECT
                            Id,
                            RepaymentNumber,
                            RepaymentDate,
                            AdvanceNumber,
                            AdvanceDate,
                            NumberofInstallment,
                            EmployeeCode,
                            EmployeeName,
                            AmountPaid,
                            MonthDeduction,
                            Nature
                            FROM PagedAdvanceRepayment
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

        public const string SAVE_ADVANCEREPAYMENT = @"INSERT INTO TADVANCEREPAYMENT
(
    REPAYMENTID,
    OUID,
    ADVANCEID,
    BIZTRANSACTIONTYPEID,
    REPAYMENTNUMBER,
    REPAYMENTDATE,
    NOOFINSTALLMENTSREPAID,
    AMOUNTREPAID,
    PAYMENTTYPEID,
    CHEQUENO,
    CHEQUEDATE,
    NATURE,
    REMARKS,
    BANKID,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @AdvanceRepaymentId,
    @OUId,
    @AdvanceId,
    @BizTransactionTypeId,
    @AdvanceRepaymentRepaymentNumber,
    @AdvanceRepaymentRepaymentDate,
    @AdvanceRepaymentBalanceInstallment,
    @AdvanceRepaymentAmountRepaid,
    @PaymentTypeId,
    @AdvanceRepaymentChequeNo,
    @AdvanceRepaymentChequeDate,
    @AdvanceRepaymentNature,
    @AdvanceRepaymentRemarks,
    @BankId,
    @AdvanceRepaymentStatus,
    @AdvanceRepaymentVersion,
    @AdvanceRepaymentCreatedById,
    @AdvanceRepaymentCreatedOn,
    @AdvanceRepaymentModifiedById,
    @AdvanceRepaymentModifiedOn
);";

        public const string UPDATE_ADVANCEREPAYMENT = @"UPDATE TADVANCEREPAYMENT
    SET
        OUID                  = @OUId,
        ADVANCEID             = @AdvanceId,
        BIZTRANSACTIONTYPEID  = @BizTransactionTypeId,
        REPAYMENTNUMBER       = @AdvanceRepaymentRepaymentNumber,
        REPAYMENTDATE         = @AdvanceRepaymentRepaymentDate,
        NOOFINSTALLMENTSREPAID = @AdvanceRepaymentBalanceInstallment,
        AMOUNTREPAID          = @AdvanceRepaymentAmountRepaid,
        PAYMENTTYPEID         = @PaymentTypeId,
        CHEQUENO              = @AdvanceRepaymentChequeNo,
        CHEQUEDATE            = @AdvanceRepaymentChequeDate,
        NATURE                = @AdvanceRepaymentNature,
        REMARKS               = @AdvanceRepaymentRemarks,
        BANKID                = @BankId,
        STATUS                = @AdvanceRepaymentStatus,
        VERSION               = @AdvanceRepaymentVersion,
        MODIFIEDBYID          = @AdvanceRepaymentModifiedById,
        MODIFIEDON            = @AdvanceRepaymentModifiedOn
    WHERE
        REPAYMENTID = @AdvanceRepaymentId;";

        public const string DELETE_ADVANCEREPAYMENT = @"DELETE FROM TADVANCEREPAYMENT
                                            WHERE REPAYMENTID = @AdvanceRepaymentId;";
    }
}