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

namespace PayRollDAL.Query.EmployeeExperience
{
    public static class EmployeeExperienceQB
    {

        public const string SAVE_EMPLOYEEEXPERIENCE = @"INSERT 
                            INTO MEMPLOYEEEXPERIENCE (
                                 EMPLOYEEEXPERIENCEID,
                                 EMPLOYEEID,
                                 SLNO,
                                 EMPLOYER,
                                 CITY,
                                 FROMMONTHYEAR,
                                 TOMONTHYEAR,
                                 NUMBEROFYEAR,
                                 DESIGNATION,
                                 SALARYDRAWN,
                                 REASONFORLEAVING
                            ) VALUES (
                                 @EmployeeExperienceId,
                                 @EmployeeId,
                                 @EmployeeExperienceSlno,
                                 @EmployeeExperienceEmployer,
                                 @EmployeeExperienceCity,
                                 @EmployeeExperienceFromMonthYear,
                                 @EmployeeExperienceToMonthYear,
                                 @EmployeeExperienceNumberOfYear,
                                 @EmployeeExperienceDesignation,
                                 @EmployeeExperienceSalaryDrawn,
                                 @EmployeeExperienceReasonForLeaving
                            );
                            ";

        public const string UPDATE_EMPLOYEEEXPERIENCE = @"UPDATE 
                            MEMPLOYEEEXPERIENCE SET
                            EMPLOYEEID = @EmployeeId,
                            SLNO = @EmployeeExperienceSlno,
                            EMPLOYER = @EmployeeExperienceEmployer,
                            CITY = @EmployeeExperienceCity,
                            FROMMONTHYEAR = @EmployeeExperienceFromMonthYear,
                            TOMONTHYEAR = @EmployeeExperienceToMonthYear,
                            NUMBEROFYEAR = @EmployeeExperienceNumberOfYear,
                            DESIGNATION = @EmployeeExperienceDesignation,
                            SALARYDRAWN = @EmployeeExperienceSalaryDrawn,
                            REASONFORLEAVING = @EmployeeExperienceReasonForLeaving
                            WHERE EMPLOYEEEXPERIENCEID = @EmployeeExperienceId;
                            ";

        public const string DELETE_EMPLOYEEEXPERIENCE = @"DELETE
                            FROM MEMPLOYEEEXPERIENCE
                            WHERE EMPLOYEEID = @employeeid;
                            ";

        // Migrated from GB4 POST /cs/Criteria.svc/List/?ObjectCode=EMPLOYEEEXPERIENCE
        // (generic metadata-driven list service, resolved via the EmployeeExperience.hbm.xml
        // mapping onto MEMPLOYEEEXPERIENCE).
        // @firstnumber/@maxresult are absolute row bounds; -1/-1 returns every row.
        //
        // {DYNAMIC_WHERE} sits INSIDE the CTE on purpose: criteria must filter before
        // ROW_NUMBER() is assigned, otherwise paging would number the unfiltered set and
        // return the wrong page. It is substituted in the DAL via CriteriaBuilder.Build —
        // NOT via the QueryWithCriteriaAsync extension, whose derived-table wrapping
        // (SELECT * FROM (...) AS __T) is invalid around a WITH clause and would also
        // apply the filter after paging.
        //
        // Criteria FieldName uses the SELECT alias (e.g. "EmployeeId"), which
        // CriteriaBuilder maps back to EE.EMPLOYEEID. GB4 used the NHibernate path
        // "Employee.Id" for the same filter — clients must send the GB5 form.
        public const string GET_SELECTLIST_EMPLOYEEEXPERIENCE = @"
                            WITH PagedEmployeeExperience AS (
                            SELECT
                            EE.EMPLOYEEEXPERIENCEID AS Id,
                            EE.EMPLOYEEID AS EmployeeId,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            EE.SLNO AS SlNo,
                            EE.EMPLOYER AS Employer,
                            EE.CITY AS City,
                            EE.FROMMONTHYEAR AS FromMonthYear,
                            EE.TOMONTHYEAR AS ToMonthYear,
                            EE.NUMBEROFYEAR AS NumberOfYear,
                            EE.DESIGNATION AS Designation,
                            EE.SALARYDRAWN AS SalaryDrawn,
                            EE.REASONFORLEAVING AS ReasonForLeaving,
                            ROW_NUMBER() OVER (ORDER BY EE.EMPLOYEEEXPERIENCEID) AS RowNum
                            FROM MEMPLOYEEEXPERIENCE EE
                            LEFT JOIN MEMPLOYEE E ON EE.EMPLOYEEID = E.EMPLOYEEID
                            WHERE 1=1
                            {DYNAMIC_WHERE}
                            )
                            SELECT
                            Id,
                            EmployeeId,
                            EmployeeCode,
                            EmployeeName,
                            SlNo,
                            Employer,
                            City,
                            FromMonthYear,
                            ToMonthYear,
                            NumberOfYear,
                            Designation,
                            SalaryDrawn,
                            ReasonForLeaving
                            FROM PagedEmployeeExperience
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}