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

namespace FrameworkDAL.Query.RoleVsModule
{
    public static class RoleVsModulesQB
    {
        public const string GET_ROLEVSMODULES_BY_ROLEID = @"SELECT
                            RVM.ROLEVSMODULESID AS RoleVsModulesId,
                            RVM.SLNO AS RoleVsModulesSlNo,
                            RVM.ROLEID AS RoleId,
                            RO.ROLECODE AS RoleCode,
                            RO.ROLENAME AS RoleName,
                            RVM.MODULEID AS ModuleId,
                            MO.MODULECODE AS ModuleCode,
                            MO.MODULENAME AS ModuleName,
                            RVM.DEFAULTMENUID AS DefaultMenuId,
                            ME.MENUCODE AS DefaultMenuCode,
                            ME.MENUNAME AS DefaultMenuName
                            FROM MROLEVSMODULES RVM
                            LEFT JOIN MROLE RO ON RVM.ROLEID = RO.ROLEID
                            LEFT JOIN MMODULE MO ON RVM.MODULEID = MO.MODULEID
                            LEFT JOIN MMENU ME ON RVM.DEFAULTMENUID = ME.MENUID
                            WHERE RVM.ROLEID = @roleid;
                            ";

        public const string GET_ROLEVSMODULES = @"SELECT DISTINCT
                            MO.MODULEID AS Id,
                            MO.MODULECODE AS Code,
                            MO.MODULENAME AS Name
                            FROM MROLEVSMODULES RVM
                            LEFT JOIN MMODULE MO ON RVM.MODULEID = MO.MODULEID
                            WHERE RVM.ROLEID = @roleid
                            AND (@moduleid = -1 OR RVM.MODULEID = @moduleid)
                            AND (@modulecode IS NULL OR MO.MODULECODE LIKE '%'+@modulecode+'%')
                            AND (@modulename IS NULL OR MO.MODULENAME LIKE '%'+@modulename+'%');
                            ";

        public const string GET_USERROLEVSMENU = @"SELECT DISTINCT
                            ME.MENUID AS Id,
                            ME.MENUCODE AS Code,
                            ME.MENUNAME AS Name,
                            ME.PARENTIDS AS ParentIds,
                            ME.MENUTYPE AS SourceType,
                            ME.MENUNATURE AS Nature
                            FROM MROLEVSMODULES RVM
                            INNER JOIN MMODULEVSMENU MMV ON RVM.MODULEID = MMV.MODULEID
                            INNER JOIN MMENU ME ON MMV.MENUID = ME.MENUID
                            WHERE RVM.ROLEID = @roleid
                            AND (@moduleid = -1 OR RVM.MODULEID = @moduleid);
                            ";

        public const string GET_EXISTING_ROLEVSMODULES_FOR_ROLE = @"
SELECT ROLEVSMODULESID AS RoleVsModulesId, MODULEID AS ModuleId
FROM MROLEVSMODULES
WHERE ROLEID = @RoleId";

        public const string SAVE_ROLEVSMODULES = @"INSERT INTO MROLEVSMODULES (
                            ROLEVSMODULESID,
                            SLNO,
                            ROLEID,
                            MODULEID,
                            DEFAULTMENUID
                            ) VALUES (
                            @RoleVsModulesId,
                            @RoleVsModulesSlNo,
                            @RoleId,
                            @ModuleId,
                            @DefaultMenuId
                            );
                            ";

        public const string UPDATE_ROLEVSMODULES = @"UPDATE MROLEVSMODULES SET
                            SLNO = @RoleVsModulesSlNo,
                            ROLEID = @RoleId,
                            MODULEID = @ModuleId,
                            DEFAULTMENUID = @DefaultMenuId
                            WHERE ROLEVSMODULESID = @RoleVsModulesId;
                            ";

        public const string DELETE_ROLEVSMODULES_BY_ROLEID = @"DELETE FROM MROLEVSMODULES WHERE ROLEID = @roleid;";

        public const string DELETE_ROLEVSMODULES_BY_MODULEID_ROLEID = @"DELETE FROM MROLEVSMODULES WHERE MODULEID = @moduleid AND ROLEID = @roleid;";
    }
}