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

namespace PayRollDAL.Query.EmployeeNominee
{
    public static class EmployeeNomineeQB
    {
        public const string SAVE_EMPLOYEENOMINEE = @"INSERT 
                            INTO MEMPLOYEENOMINEE (
                                 EMPLOYEENOMINEEID,
                                 EMPLOYEEID,
                                 NOMINEETYPE,
                                 SLNO,
                                 NOMINEENAME,
                                 RELATION,
                                 DOB,
                                 ADDRESS,
                                 PERCENTAGE,
                                 GUARDIAN,
                                 GUARDIANADDRESS,
                                 AADHARNUMBER
                            ) VALUES (
                                 @EmployeeNomineeId,
                                 @EmployeeId,
                                 @EmployeeNomineeNomineeType,
                                 @EmployeeNomineeSlNo,
                                 @EmployeeNomineeNomineeName,
                                 @EmployeeNomineeRelation,
                                 @EmployeeNomineeDOB,
                                 @EmployeeNomineeAddress,
                                 @EmployeeNomineePercentage,
                                 @EmployeeNomineeGuardian,
                                 @EmployeeNomineeGuardianAddress,
                                 @EmployeeNomineeAadharNumber
                            );
                            ";

        public const string UPDATE_EMPLOYEENOMINEE = @"UPDATE 
                            MEMPLOYEENOMINEE SET
                            EMPLOYEEID = @EmployeeId,
                            NOMINEETYPE = @EmployeeNomineeNomineeType,
                            SLNO = @EmployeeNomineeSlNo,
                            NOMINEENAME = @EmployeeNomineeNomineeName,
                            RELATION = @EmployeeNomineeRelation,
                            DOB = @EmployeeNomineeDOB,
                            ADDRESS = @EmployeeNomineeAddress,
                            PERCENTAGE = @EmployeeNomineePercentage,
                            GUARDIAN = @EmployeeNomineeGuardian,
                            GUARDIANADDRESS = @EmployeeNomineeGuardianAddress,
                            AADHARNUMBER = @EmployeeNomineeAadharNumber
                            WHERE EMPLOYEENOMINEEID = @EmployeeNomineeId;
                            ";

        public const string DELETE_EMPLOYEENOMINEE_EMPLOYEE = @"DELETE 
                            FROM MEMPLOYEENOMINEE
                            WHERE EMPLOYEEID = @employeeid AND NOMINEETYPE = @nomineetype;
                            ";

        public const string DELETE_EMPLOYEENOMINEE = @"DELETE
                            FROM MEMPLOYEENOMINEE
                            WHERE EMPLOYEENOMINEEID = @employeenomineeid;
                            ";

        public const string GET_SELECTLIST_EMPLOYEENOMINEE = @"
                            WITH PagedEmployeeNominee AS (
                            SELECT
                            EN.EMPLOYEENOMINEEID AS Id,
                            EN.EMPLOYEEID AS EmployeeId,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            EN.NOMINEETYPE AS NomineeType,
                            EN.SLNO AS SlNo,
                            EN.NOMINEENAME AS NomineeName,
                            EN.RELATION AS Relation,
                            EN.DOB AS DOB,
                            EN.ADDRESS AS Address,
                            EN.PERCENTAGE AS Percentage,
                            EN.GUARDIAN AS Guardian,
                            EN.GUARDIANADDRESS AS GuardianAddress,
                            EN.AADHARNUMBER AS AadharNumber,
                            ROW_NUMBER() OVER (ORDER BY EN.EMPLOYEENOMINEEID) AS RowNum
                            FROM MEMPLOYEENOMINEE EN
                            LEFT JOIN MEMPLOYEE E ON EN.EMPLOYEEID = E.EMPLOYEEID
                            )
                            SELECT
                            Id,
                            EmployeeId,
                            EmployeeCode,
                            EmployeeName,
                            NomineeType,
                            SlNo,
                            NomineeName,
                            Relation,
                            DOB,
                            Address,
                            Percentage,
                            Guardian,
                            GuardianAddress,
                            AadharNumber
                            FROM PagedEmployeeNominee
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}