namespace PayRollDAL.Query.EmployeeSalary
{
    public static class EmployeeSalaryQB
    {
        public const string GET_EMPLOYEESALARY = @"SELECT
    ES.EMPLOYEESALARYID AS EmployeeSalaryId,

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

    ES.SLNO AS EmployeeSalarySlNo,
    ES.TYPE AS EmployeeSalaryType,
    ES.MONTH AS EmployeeSalaryMonth,
    ES.YEAR AS EmployeeSalaryYear,
    ES.FROMDATE AS EmployeeSalaryFromDate,
    ES.TODATE AS EmployeeSalaryToDate,
    ES.TOTALEARNINGS AS EmployeeSalaryTotalEarnings,
    ES.TOTALDEDUCTION AS EmployeeSalaryTotalDeduction,
    ES.NETPAY AS EmployeeSalaryNetPay,
    ES.C2C AS EmployeeSalaryC2C,
    ES.GRATUITYDAYS AS EmployeeSalaryGratuityDays,
    ES.BONUSONAMOUNT AS EmployeeSalaryBonusOnAmount

FROM MEMPLOYEESALARY ES
LEFT JOIN MEMPLOYEE E ON ES.EMPLOYEEID = E.EMPLOYEEID
WHERE ES.EMPLOYEEID = @employeeid
ORDER BY ES.EMPLOYEESALARYID;
";

        public const string GET_EMPLOYEESALARY_LIST = @"
                            WITH PagedEmployeeSalary AS (
                            SELECT
                            ES.EMPLOYEESALARYID AS EmployeeSalaryId,
                            ES.EMPLOYEEID AS EmployeeId,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            ES.SLNO AS EmployeeSalarySlNo,
                            ES.TYPE AS EmployeeSalaryType,
                            ES.MONTH AS EmployeeSalaryMonth,
                            ES.YEAR AS EmployeeSalaryYear,
                            ES.FROMDATE AS EmployeeSalaryFromDate,
                            ES.TODATE AS EmployeeSalaryToDate,
                            ES.TOTALEARNINGS AS EmployeeSalaryTotalEarnings,
                            ES.TOTALDEDUCTION AS EmployeeSalaryTotalDeduction,
                            ES.NETPAY AS EmployeeSalaryNetPay,
                            ES.C2C AS EmployeeSalaryC2C,
                            ES.GRATUITYDAYS AS EmployeeSalaryGratuityDays,
                            ES.BONUSONAMOUNT AS EmployeeSalaryBonusOnAmount,
                            ROW_NUMBER() OVER (ORDER BY ES.EMPLOYEESALARYID) AS RowNum
                            FROM MEMPLOYEESALARY ES
                            LEFT JOIN MEMPLOYEE E ON ES.EMPLOYEEID = E.EMPLOYEEID
                            )
                            SELECT
                            EmployeeSalaryId,
                            EmployeeId,
                            EmployeeCode,
                            EmployeeName,
                            EmployeeSalarySlNo,
                            EmployeeSalaryType,
                            EmployeeSalaryMonth,
                            EmployeeSalaryYear,
                            EmployeeSalaryFromDate,
                            EmployeeSalaryToDate,
                            EmployeeSalaryTotalEarnings,
                            EmployeeSalaryTotalDeduction,
                            EmployeeSalaryNetPay,
                            EmployeeSalaryC2C,
                            EmployeeSalaryGratuityDays,
                            EmployeeSalaryBonusOnAmount
                            FROM PagedEmployeeSalary
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}