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

namespace FrameworkDAL.Query.UserGroup
{
    public static class UserGroupQB
    {
        public const string GET_USERGROUP_ON_USERID = @"SELECT 
                                            ug.USERGROUPID    AS UserGroupId,
                                            ug.USERGROUPCODE  AS UserGroupCode,
                                            ug.USERGROUPNAME  AS UserGroupName
                                        FROM 
                                            MUSERGROUPDETAIL ugd
                                        JOIN 
                                            MUSERGROUP ug ON ugd.USERGROUPID = ug.USERGROUPID
                                        WHERE 
                                            ugd.USERID = @UserId;";

        public const string GET_USERGROUP = @"SELECT
                                                    UG.USERGROUPID      AS UserGroupId,
                                                    UG.USERGROUPCODE    AS UserGroupCode,
                                                    UG.USERGROUPNAME    AS UserGroupName,
                                                    UG.SORTORDER        AS UserGroupSortOrder,
                                                    UG.STATUS           AS UserGroupStatus,
                                                    UG.VERSION          AS UserGroupVersion,
                                                    UG.SOURCETYPE       AS UserGroupSourceType,
                                                    UG.CREATEDBYID      AS UserGroupCreatedById,
                                                    UG.CREATEDON        AS UserGroupCreatedOn,
                                                    CBU.USERNAME        AS UserGroupCreatedByName,  
                                                    UG.MODIFIEDBYID     AS UserGroupModifiedById,
                                                    UG.MODIFIEDON       AS UserGroupModifiedOn,
                                                    MBU.USERNAME        AS UserGroupModifiedByName, 
                                                            UGD.USERGROUPDETAILID AS UserGroupDetailId,
                                                            UGD.USERGROUPID       AS UserGroupId,
                                                            UG.USERGROUPCODE      AS UserGroupCode,        
                                                            UG.USERGROUPNAME      AS UserGroupName,         
                                                            UGD.SLNO              AS UserGroupDetailSlNo,
                                                            U.USERID              AS UserId,
                                                            U.USERCODE            AS UserCode,
                                                            U.USERNAME            AS UserName

                                                FROM MUSERGROUP UG
                                                LEFT JOIN MUSERGROUPDETAIL UGD ON UG.USERGROUPID = UGD.USERGROUPID
                                                LEFT JOIN MUSER U ON UGD.USERID = U.USERID
                                                LEFT JOIN MUSER CBU ON UG.CREATEDBYID = CBU.USERID
                                                LEFT JOIN MUSER MBU ON UG.MODIFIEDBYID = MBU.USERID
                                                WHERE UG.USERGROUPID = @usergroupid;";

        public const string SAVE_USERGROUP = @"INSERT INTO MUSERGROUP
                                                (
                                                    USERGROUPID,
                                                    USERGROUPCODE,
                                                    USERGROUPNAME,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES
                                                (
                                                    @UserGroupId,
                                                    @UserGroupCode,
                                                    @UserGroupName,
                                                    @UserGroupSortOrder,
                                                    @UserGroupStatus,
                                                    @UserGroupVersion,
                                                    @UserGroupSourceType,
                                                    @UserGroupCreatedById,
                                                    @UserGroupCreatedOn,
                                                    @UserGroupModifiedById,
                                                    @UserGroupModifiedOn
                                                );";

        public const string SAVE_USERGROUP_DETAIL = @"INSERT INTO MUSERGROUPDETAIL
                                                    (
                                                        USERGROUPDETAILID,
                                                        USERGROUPID,
                                                        SLNO,
                                                        USERID
                                                    )
                                                    VALUES
                                                    (
                                                        @UserGroupDetailId,
                                                        @UserGroupId,
                                                        @UserGroupDetailSlNo,
                                                        @UserId
                                                    );";


        public const string UPDATE_USERGROUP = @"UPDATE MUSERGROUP
                                                SET
                                                    USERGROUPCODE     = @UserGroupCode,
                                                    USERGROUPNAME     = @UserGroupName,
                                                    SORTORDER         = @UserGroupSortOrder,
                                                    STATUS            = @UserGroupStatus,
                                                    VERSION           = @UserGroupVersion,
                                                    SOURCETYPE        = @UserGroupSourceType,
                                                    MODIFIEDBYID      = @UserGroupModifiedById,
                                                    MODIFIEDON        = @UserGroupModifiedOn
                                                WHERE
                                                    USERGROUPID = @UserGroupId;";

        public const string UPDATE_USERGROUP_DETAIL = @"UPDATE MUSERGROUPDETAIL
                                                        SET
                                                            USERGROUPID = @UserGroupId,
                                                            SLNO        = @UserGroupDetailSlNo,
                                                            USERID      = @UserId
                                                        WHERE   
                                                            USERGROUPDETAILID = @UserGroupDetailId;";

        public const string DELETE_USERGROUP = @"DELETE FROM MUSERGROUP
                                                WHERE USERGROUPID = @usergroupid;";

        public const string DELETE_USERGROUP_DETAIL = @"DELETE FROM MUSERGROUPDETAIL
                                                        WHERE USERGROUPID = @usergroupid;";

        public const string GET_SELECTLIST_USERGROUP = @"SELECT 
                                                        USERGROUPID      AS UserGroupPicklistId,
                                                        USERGROUPCODE    AS UserGroupPicklistCode,
                                                        USERGROUPNAME    AS UserGroupPicklistName
                                                    FROM MUSERGROUP
                                                    WHERE STATUS = 1
                                                    ORDER BY USERGROUPNAME;";

        public const string GET_USERGROUP_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MUSERGROUP
                                                    WHERE
                                                        (
                                                            @UserGroupId <> -1 AND USERGROUPID = @UserGroupId
                                                        )
                                                        AND USERGROUPID <> @UserGroupId";
    }        
}