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

namespace PayRollDAL.Query.DAPointGroup
{
    public class DAPointGroupQB
    {
        public const string GET_DAPOINTGROUP = @"SELECT
                                                DAPOINTGROUPID           AS DAPointGroupId,
                                                DAPOINTGROUPCODE         AS DAPointGroupCode,
                                                DAPOINTGROUPNAME         AS DAPointGroupName,
                                                DAPOINTGROUPSET          AS DAPointGroupDAPointGroupSet,
                                                BASEINDEX                AS DAPointGroupBaseIndex,
                                                RATEPERPOINT             AS DAPointGroupRatePerPoint,
                                                SORTORDER                AS DAPointGroupSortOrder,
                                                STATUS                   AS DAPointGroupStatus,
                                                VERSION                  AS DAPointGroupVersion,
                                                CREATEDBYID              AS DAPointGroupCreatedById,
                                                CREATEDON                AS DAPointGroupCreatedOn,
                                                MODIFIEDBYID             AS DAPointGroupModifiedById,
                                                MODIFIEDON               AS DAPointGroupModifiedOn,
                                                SOURCETYPE               AS DAPointGroupSourceType,
                                                TENANTID                 AS DAPointGroupTenantId
                                            FROM MDAPOINTGROUP 
                                            WHERE DAPOINTGROUPID = @dapointgroupid;";

        public const string SAVE_DAPOINTGROUP = @"INSERT INTO MDAPOINTGROUP
                                                    (
                                                        DAPOINTGROUPID,
                                                        DAPOINTGROUPCODE,
                                                        DAPOINTGROUPNAME,
                                                        DAPOINTGROUPSET,
                                                        BASEINDEX,
                                                        RATEPERPOINT,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        SOURCETYPE,
                                                        TENANTID
                                                    )
                                                    VALUES
                                                    (
                                                        @DAPointGroupId,
                                                        @DAPointGroupCode,
                                                        @DAPointGroupName,
                                                        @DAPointGroupDAPointGroupSet,
                                                        @DAPointGroupBaseIndex,
                                                        @DAPointGroupRatePerPoint,
                                                        @DAPointGroupSortOrder,
                                                        @DAPointGroupStatus,
                                                        @DAPointGroupVersion,
                                                        @DAPointGroupCreatedById,
                                                        @DAPointGroupCreatedOn,
                                                        @DAPointGroupModifiedById,
                                                        @DAPointGroupModifiedOn,
                                                        @DAPointGroupSourceType,
                                                        @DAPointGroupTenantId
                                                    );";

        public const string UPDATE_DAPOINTGROUP = @"UPDATE MDAPOINTGROUP
                                                    SET
                                                        DAPOINTGROUPCODE = @DAPointGroupCode,
                                                        DAPOINTGROUPNAME = @DAPointGroupName,
                                                        DAPOINTGROUPSET = @DAPointGroupDAPointGroupSet,
                                                        BASEINDEX = @DAPointGroupBaseIndex,
                                                        RATEPERPOINT = @DAPointGroupRatePerPoint,
                                                        SORTORDER = @DAPointGroupSortOrder,
                                                        STATUS = @DAPointGroupStatus,
                                                        VERSION = @DAPointGroupVersion,
                                                        MODIFIEDBYID = @DAPointGroupModifiedById,
                                                        MODIFIEDON = @DAPointGroupModifiedOn,
                                                        SOURCETYPE = @DAPointGroupSourceType,
                                                        TENANTID = @DAPointGroupTenantId    
                                                    WHERE
                                                        DAPOINTGROUPID = @DAPointGroupId;";

        public const string DELETE_DAPOINTGROUP = @"DELETE FROM MDAPOINTGROUP
                                                    WHERE DAPOINTGROUPID = @dapointgroupid;";


        public const string GET_SELECTLIST_DAPOINTGROUP = @"WITH PagedDAPointGroup AS
                                                        (
                                                            SELECT
                                                                DG.DAPOINTGROUPID     AS Id,
                                                                DG.DAPOINTGROUPCODE   AS Code,
                                                                DG.DAPOINTGROUPNAME   AS Name,
                                                                ROW_NUMBER() OVER (ORDER BY DG.DAPOINTGROUPNAME) AS RowNum
                                                            FROM MDAPOINTGROUP DG
                                                            WHERE
                                                                DG.STATUS = 1
                                                        )
                                                        SELECT
                                                            Id,
                                                            Code,
                                                            Name
                                                        FROM PagedDAPointGroup
                                                        WHERE 
                                                        (@firstnumber = -1 AND @maxresult = -1)
                                                        OR (RowNum BETWEEN @firstnumber AND @maxresult);";


    }
}