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

namespace PayRollDAL.Query.EmiChangeRequest
{
    public static class EmiChangeRequestQB
    {
        public const string GET_EMICHANGEREQUEST = @"SELECT
    A.EMICHANGEREQUESTID AS EmiChangeRequestId,

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

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

    A.PERIODID AS EmiChangeRequestPeriodId,
    A.EMICHANGEREQUESTNUMBER AS EmiChangeRequestNumber,
    A.EMICHANGEREQUESTDATE AS EmiChangeRequestDate,

    A.ADVANCEID AS AdvanceId,
    AD.ADVANCENUMBER AS AdvanceAdvanceNumber,
    AD.ADVANCEDATE AS AdvanceAdvanceDate,
    AD.AMOUNTREMAINING AS AdvanceAmountRemaining,

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

    A.PAYPERIODID AS PayPeriodId,
    PP.PAYPERIODCODE AS PayPeriodCode,
    PP.PAYPERIODNAME AS PayPeriodName,

    A.INSTALLMENTAMOUNT AS EmiChangeRequestInstallmentAmount,
    A.REQUESTEDAMOUNT AS EmiChangeRequestRequestedAmount,
    A.APPROVEDAMOUNT AS EmiChangeRequestApprovedAmount,
    A.REMARKS AS EmiChangeRequestRemarks,

    A.STATUS AS EmiChangeRequestStatus,
    A.VERSION AS EmiChangeRequestVersion,

    A.CREATEDBYID AS EmiChangeRequestCreatedbyId,
    CB.USERNAME AS EmiChangeRequestCreatedbyName,
    A.CREATEDON AS EmiChangeRequestCreatedOn,
    A.MODIFIEDBYID AS EmiChangeRequestModifiedbyId,
    MB.USERNAME AS EmiChangeRequestModifiedbyName,
    A.MODIFIEDON AS EmiChangeRequestModifiedOn

FROM TEMICHANGEREQUEST A
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON A.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID
LEFT JOIN MORGANIZATIONUNIT OU    ON A.OUID = OU.OUID
LEFT JOIN TADVANCE AD             ON A.ADVANCEID = AD.ADVANCEID
LEFT JOIN MEMPLOYEE E             ON A.EMPLOYEEID = E.EMPLOYEEID
LEFT JOIN MPAYPERIOD PP           ON A.PAYPERIODID = PP.PAYPERIODID
LEFT JOIN MUSER CB                ON A.CREATEDBYID = CB.USERID
LEFT JOIN MUSER MB                ON A.MODIFIEDBYID = MB.USERID
WHERE A.EMICHANGEREQUESTID = @emichangerequestid;
";

        public const string GET_SELECTLIST_EMICHANGEREQUEST = @"
WITH PagedEmiChangeRequest AS (
    SELECT
        A.EMICHANGEREQUESTID AS Id,
        A.EMICHANGEREQUESTNUMBER AS Number,
        A.EMICHANGEREQUESTDATE AS Date,
        E.EMPLOYEECODE AS EmployeeCode,
        E.EMPLOYEENAME AS EmployeeName,
        GCM.GCMNAME AS AdvanceName,
        ROW_NUMBER() OVER (ORDER BY A.EMICHANGEREQUESTID) AS RowNum
    FROM TEMICHANGEREQUEST A
    INNER JOIN MEMPLOYEE E  ON A.EMPLOYEEID = E.EMPLOYEEID
    INNER JOIN TADVANCE AD  ON A.ADVANCEID = AD.ADVANCEID
    INNER JOIN MGCM GCM     ON AD.ADVANCETYPEID = GCM.GCMID
    WHERE (@ouid = -1 OR A.OUID = @ouid)
      AND (@payperiodid = -1 OR A.PAYPERIODID = @payperiodid)
      AND (@biztransactiontypeid = -1 OR A.BIZTRANSACTIONTYPEID = @biztransactiontypeid)
      AND (@number = '' OR A.EMICHANGEREQUESTNUMBER LIKE '%' + @number + '%')
      AND (@employeename = '' OR E.EMPLOYEENAME LIKE '%' + @employeename + '%' OR A.EMICHANGEREQUESTNUMBER LIKE '%' + @employeename + '%')
)
SELECT
    Id,
    Number,
    Date,
    EmployeeCode,
    EmployeeName,
    AdvanceName
FROM PagedEmiChangeRequest
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR
    (RowNum BETWEEN @firstnumber AND @maxresult);
";

        public const string CHECK_SAVE_UPDATION_POSSIBLE = @"
SELECT COUNT(A.PAYPERIODID)
FROM TPAYPROCESS A
INNER JOIN MPAYPERIOD B ON A.PAYPERIODID = B.PAYPERIODID
INNER JOIN MEMPLOYEE C  ON A.EMPLOYEEID = C.EMPLOYEEID
WHERE A.OUID = @ouid
  AND A.OUID = C.WORKOUID
  AND A.PAYPERIODID = @payperiodid
  AND A.EMPLOYEEID = @employeeid
  AND B.PERIODTYPE = 0
  AND A.PAYCONFIGID = C.PAYCONFIGURATIONID;
";

        public const string GET_PERIOD_DATE_RANGE = @"
SELECT FROMDATE AS FromDate, TODATE AS ToDate
FROM MPERIOD
WHERE PERIODID = @periodid;
";

        public const string SAVE_EMICHANGEREQUEST = @"INSERT INTO TEMICHANGEREQUEST
(
    EMICHANGEREQUESTID,
    BIZTRANSACTIONTYPEID,
    OUID,
    PERIODID,
    EMICHANGEREQUESTNUMBER,
    EMICHANGEREQUESTDATE,
    ADVANCEID,
    EMPLOYEEID,
    PAYPERIODID,
    INSTALLMENTAMOUNT,
    REQUESTEDAMOUNT,
    APPROVEDAMOUNT,
    REMARKS,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @EmiChangeRequestId,
    @BIZTransactionTypeId,
    @OUId,
    @EmiChangeRequestPeriodId,
    @EmiChangeRequestNumber,
    @EmiChangeRequestDate,
    @AdvanceId,
    @EmployeeId,
    @PayPeriodId,
    @EmiChangeRequestInstallmentAmount,
    @EmiChangeRequestRequestedAmount,
    @EmiChangeRequestApprovedAmount,
    @EmiChangeRequestRemarks,
    @EmiChangeRequestStatus,
    @EmiChangeRequestVersion,
    @EmiChangeRequestCreatedbyId,
    @EmiChangeRequestCreatedOn,
    @EmiChangeRequestModifiedbyId,
    @EmiChangeRequestModifiedOn
);";

        public const string UPDATE_EMICHANGEREQUEST = @"UPDATE TEMICHANGEREQUEST
SET
    BIZTRANSACTIONTYPEID = @BIZTransactionTypeId,
    OUID                 = @OUId,
    PERIODID             = @EmiChangeRequestPeriodId,
    EMICHANGEREQUESTNUMBER = @EmiChangeRequestNumber,
    EMICHANGEREQUESTDATE   = @EmiChangeRequestDate,
    ADVANCEID            = @AdvanceId,
    EMPLOYEEID           = @EmployeeId,
    PAYPERIODID          = @PayPeriodId,
    INSTALLMENTAMOUNT    = @EmiChangeRequestInstallmentAmount,
    REQUESTEDAMOUNT      = @EmiChangeRequestRequestedAmount,
    APPROVEDAMOUNT       = @EmiChangeRequestApprovedAmount,
    REMARKS              = @EmiChangeRequestRemarks,
    STATUS               = @EmiChangeRequestStatus,
    VERSION              = @EmiChangeRequestVersion,
    MODIFIEDBYID         = @EmiChangeRequestModifiedbyId,
    MODIFIEDON           = @EmiChangeRequestModifiedOn
WHERE
    EMICHANGEREQUESTID = @EmiChangeRequestId;";

        public const string DELETE_EMICHANGEREQUEST = @"DELETE FROM TEMICHANGEREQUEST
WHERE EMICHANGEREQUESTID = @emichangerequestid;";
    }
}