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

namespace FrameworkDAL.Query.SecurityGroup
{
    public static class SecurityGroupQB
    {
        public const string GET_SECURITY_GROUP = @"SELECT
                                                    SG.SECURITYGROUPID AS SecurityGroupId,
                                                    SG.SECURITYGROUPCODE AS SecurityGroupCode,
                                                    SG.SECURITYGROUPNAME AS SecurityGroupName,
                                                    SG.GROUPTYPE AS SecurityGroupGroupType,
                                                    SG.REMARKS AS SecurityGroupRemarks,
                                                    SG.SORTORDER AS SecurityGroupSortOrder,
                                                    SG.STATUS AS SecurityGroupStatus,
                                                    SG.VERSION AS SecurityGroupVersion,
                                                    SG.CREATEDBYID AS SecurityGroupCreatedById,
                                                    SG.CREATEDON AS SecurityGroupCreatedOn,
                                                    SG.MODIFIEDBYID AS SecurityGroupModifiedById,
                                                    SG.MODIFIEDON AS SecurityGroupModifiedOn,
                                                    SG.SOURCETYPE AS SecurityGroupSourceType,
                                                    SG.TENANTID AS SecurityGroupTenantId,
                                                    SGD.SECURITYGROUPDETAILID AS SecurityGroupDetailId,
                                                    SGD.SECURITYGROUPID AS SecurityGroupId,
                                                    SGD.SLNO AS SecurityGroupDetailSlNo,
                                                    SGD.MARKCODE AS SecurityGroupDetailMarkCode,
                                                    SGD.MARKNAME AS SecurityGroupDetailMarkName,
                                                    SGD.HIERERACHYLEVEL AS SecurityGroupDetailHiererachyLevel,
                                                    SGD.PARTICULARS AS SecurityGroupDetailParticulars

                                                FROM MSECURITYGROUP SG
                                                LEFT JOIN MSECURITYGROUPDETAIL SGD ON SGD.SECURITYGROUPID = SG.SECURITYGROUPID
                                                WHERE
                                                SG.SECURITYGROUPID = @securitygroupid;";



        public const string DELETE_SECURITY_GROUP_DETAIL = @"DELETE FROM MSECURITYGROUPDETAIL WHERE SECURITYGROUPID = @securitygroupid";

        public const string DELETE_SECURITY_GROUP = @"DELETE FROM MSECURITYGROUP WHERE SECURITYGROUPID = @securitygroupid";

        public const string GET_SELECTLIST_SECUIRTYGROUP = @"WITH PagedSecurityGroup AS
                                                        (
                                                            SELECT 
                                                                    SG.SECURITYGROUPID     AS Id,
                                                                    SG.SECURITYGROUPCODE   AS Code,
                                                                    SG.SECURITYGROUPNAME   AS Name,
                                                                 ROW_NUMBER() OVER (ORDER BY SG.SECURITYGROUPNAME) AS RowNum
                                                                FROM MSECURITYGROUP SG
                                                        )
                                                        SELECT 
                                                            Id,
                                                            Code,
                                                            Name
                                                        FROM PagedSecurityGroup
                                                        WHERE 
                                                         (@firstnumber = -1 AND @maxresult = -1)
                                                         OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}