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

namespace PayRollDAL.Query.Payelement
{
    public static class PayelementQB
    {
        public const string GET_PAYELEMENT = @"SELECT
                            P.PAYELEMENTID AS PayelementId,
                            P.PAYELEMENTCODE AS PayelementCode,
                            P.PAYELEMENTNAME AS PayelementName,
                            P.PERIODTYPE AS PayelementPeriodType,
                            P.COSTTYPE AS PayelementCostType,
                            P.SALARYTYPE AS PayelementSalaryType,
                            P.SORTORDER AS PayelementSortOrder,
                            P.STATUS AS PayelementStatus,
                            P.VERSION AS PayelementVersion,
                            P.CREATEDBYID AS PayelementCreatedById,
                            P.CREATEDON AS PayelementCreatedOn,
                            P.MODIFIEDBYID AS PayelementModifiedById,
                            P.MODIFIEDON AS PayelementModifiedOn,
                            P.SOURCETYPE AS PayelementSourceType
                            FROM MPAYELEMENT P
                            WHERE P.PAYELEMENTID = @payelementid;
                            ";

        public const string GET_SELECTLIST_PAYELEMENT = @"
                            WITH PagedPayelement AS (
                            SELECT 
                            P.PAYELEMENTID AS Id,
                            P.PAYELEMENTCODE AS Code,
                            P.PAYELEMENTNAME AS Name,
                            ROW_NUMBER() OVER (ORDER BY P.PAYELEMENTID) AS RowNum
                            FROM MPAYELEMENT P
                            )
                            SELECT
                            Id,
                            Code,
                            Name
                            FROM PagedPayelement
                            WHERE 
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

        public const string SAVE_PAYELEMENT = @"INSERT 
                            INTO MPAYELEMENT (
                                 PAYELEMENTID,
                                 PAYELEMENTCODE,
                                 PAYELEMENTNAME,
                                 PERIODTYPE,
                                 COSTTYPE,
                                 SALARYTYPE,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON
                            ) VALUES (
                                 @PayelementId,
                                 @PayelementCode,
                                 @PayelementName,
                                 @PayelementPeriodType,
                                 @PayelementCostType,
                                 @PayelementSalaryType,
                                 @PayelementStatus,
                                 @PayelementVersion,
                                 @PayelementCreatedById,
                                 @PayelementCreatedOn,
                                 @PayelementModifiedById,
                                 @PayelementModifiedOn
                            );
                            ";

        public const string UPDATE_PAYELEMENT = @"UPDATE 
                            MPAYELEMENT SET
                            PAYELEMENTCODE = @PayelementCode,
                            PAYELEMENTNAME = @PayelementName,
                            PERIODTYPE = @PayelementPeriodType,
                            COSTTYPE = @PayelementCostType,
                            SALARYTYPE = @PayelementSalaryType,
                            STATUS = @PayelementStatus,
                            VERSION = @PayelementVersion,
                            MODIFIEDBYID = @PayelementModifiedById,
                            MODIFIEDON = @PayelementModifiedOn
                            WHERE PAYELEMENTID = @PayelementId;
                            ";

        public const string DELETE_PAYELEMENT = @"DELETE 
                            FROM MPAYELEMENT
                            WHERE PAYELEMENTID = @payelementid;
                            ";
    }
}