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

namespace PayRollDAL.Query.EmployeeQualification
{
    public static class EmployeeQualificationQB
    {
        public const string SAVE_EMPLOYEEQUALIFICATION = @"INSERT 
                            INTO MEMPLOYEEQUALIFICATION (
                                 EMPLOYEEQUALIFICATIONID,
                                 EMPLOYEEID,
                                 SLNO,
                                 COURSE,
                                 INSTITUTE,
                                 DURATION,
                                 FROMYEAR,
                                 TOYEAR,
                                 GRADE,
                                 REMARKS
                            ) VALUES (
                                 @EmployeeQualificationId,
                                 @EmployeeId,
                                 @EmployeeQualificationSlNo,
                                 @EmployeeQualificationCourse,
                                 @EmployeeQualificationInstitute,
                                 @EmployeeQualificationDuration,
                                 @EmployeeQualificationFromYear,
                                 @EmployeeQualificationToYear,
                                 @EmployeeQualificationGrade,
                                 @EmployeeQualificationRemarks
                            );
                            ";

        public const string UPDATE_EMPLOYEEQUALIFICATION = @"UPDATE 
                            MEMPLOYEEQUALIFICATION SET
                            EMPLOYEEID = @EmployeeId,
                            SLNO = @EmployeeQualificationSlNo,
                            COURSE = @EmployeeQualificationCourse,
                            INSTITUTE = @EmployeeQualificationInstitute,
                            DURATION = @EmployeeQualificationDuration,
                            FROMYEAR = @EmployeeQualificationFromYear,
                            TOYEAR = @EmployeeQualificationToYear,
                            GRADE = @EmployeeQualificationGrade,
                            REMARKS = @EmployeeQualificationRemarks
                            WHERE EMPLOYEEQUALIFICATIONID = @EmployeeQualificationId;
                            ";

        public const string DELETE_EMPLOYEEQUALIFICATION = @"DELETE
                            FROM MEMPLOYEEQUALIFICATION
                            WHERE EMPLOYEEID = @employeeid;
                            ";

        public const string GET_SELECTLIST_EMPLOYEEQUALIFICATION = @"
                            WITH PagedEmployeeQualification AS (
                            SELECT
                            EQ.EMPLOYEEQUALIFICATIONID AS Id,
                            EQ.EMPLOYEEID AS EmployeeId,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            EQ.SLNO AS SlNo,
                            EQ.COURSE AS Course,
                            EQ.INSTITUTE AS Institute,
                            EQ.DURATION AS Duration,
                            EQ.FROMYEAR AS FromYear,
                            EQ.TOYEAR AS ToYear,
                            EQ.GRADE AS Grade,
                            EQ.REMARKS AS Remarks,
                            ROW_NUMBER() OVER (ORDER BY EQ.EMPLOYEEQUALIFICATIONID) AS RowNum
                            FROM MEMPLOYEEQUALIFICATION EQ
                            LEFT JOIN MEMPLOYEE E ON EQ.EMPLOYEEID = E.EMPLOYEEID
                            )
                            SELECT
                            Id,
                            EmployeeId,
                            EmployeeCode,
                            EmployeeName,
                            SlNo,
                            Course,
                            Institute,
                            Duration,
                            FromYear,
                            ToYear,
                            Grade,
                            Remarks
                            FROM PagedEmployeeQualification
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}