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

namespace FrameworkDAL.Query.RoleVsPortlet
{
    public static class RoleVsPortletQB
    {
        public const string GET_ROLEVSPORTLET = @"SELECT
                            RVP.ROLEVSPORTLETID AS RoleVsPortletId,
                            RVP.ROLEID AS RoleId,
                            RO.ROLECODE AS RoleCode,
                            RO.ROLENAME AS RoleName,
                            RVP.PAGEID AS PageId,
                            P.PAGECODE AS PageCode,
                            P.PAGENAME AS PageName,
                            RVP.PORTLETID AS PortletId,
                            PO.PORTLETCODE AS PortletCode,
                            PO.PORTLETNAME AS PortletName,
                            PO.WIDTH AS PortletWidth,
                            PO.HEIGHT AS PortletHeight,
                            PO.MENUID AS PortletMenuId,
                            RVP.CRITERIACONFIGID AS CriteriaConfigId,
                            CC.CRITERIACONFIGNAME AS CriteriaConfigName,
                            RVP.SLNO AS RoleVsPortletSlNo,
                            RVP.PORTLETROW AS RoleVsPortletPortletRow,
                            RVP.PORTLETCOLUMN AS RoleVsPortletPortletColumn
                            FROM MROLEVSPORTLET RVP
                            LEFT JOIN MROLE RO ON RVP.ROLEID = RO.ROLEID
                            LEFT JOIN MPAGE P ON RVP.PAGEID = P.PAGEID
                            LEFT JOIN MPORTLET PO ON RVP.PORTLETID = PO.PORTLETID
                            LEFT JOIN TCRITERIACONFIG CC ON RVP.CRITERIACONFIGID = CC.CRITERIACONFIGID
                            WHERE RVP.ROLEID = @roleid;
                            ";

        public const string SAVE_ROLEVSPORTLET = @"INSERT 
                            INTO MROLEVSPORTLET (
                                 ROLEVSPORTLETID,
                                 ROLEID,
                                 PAGEID,
                                 PORTLETID,
                                 CRITERIACONFIGID,
                                 SLNO,
                                 PORTLETROW,
                                 PORTLETCOLUMN
                            ) VALUES (
                                 @RoleVsPortletId,
                                 @RoleId,
                                 @PageId,
                                 @PortletId,
                                 @CriteriaConfigId,
                                 @RoleVsPortletSlNo,
                                 @RoleVsPortletPortletRow,
                                 @RoleVsPortletPortletColumn
                            );
                            ";

        public const string UPDATE_ROLEVSPORTLET = @"UPDATE 
                            MROLEVSPORTLET SET
                            ROLEID = @RoleId,
                            PAGEID = @PageId,
                            PORTLETID = @PortletId,
                            CRITERIACONFIGID = @CriteriaConfigId,
                            SLNO = @RoleVsPortletSlNo,
                            PORTLETROW = @RoleVsPortletPortletRow,
                            PORTLETCOLUMN = @RoleVsPortletPortletColumn
                            WHERE ROLEVSPORTLETID = @RoleVsPortletId;
                            ";

        public const string DELETE_ROLEVSPORTLET = @"DELETE 
                            FROM MROLEVSPORTLET
                            WHERE ROLEID = @roleid;
                            ";

        public const string GET_ROLEVSPORTLET_WITH_ROLE_AND_PAGE = @"SELECT
                            RVP.ROLEVSPORTLETID AS RoleVsPortletId,
                            RVP.ROLEID AS RoleId,
                            RO.ROLECODE AS RoleCode,
                            RO.ROLENAME AS RoleName,
                            RVP.PAGEID AS PageId,
                            P.PAGECODE AS PageCode,
                            P.PAGENAME AS PageName,
                            RVP.PORTLETID AS PortletId,
                            PO.PORTLETCODE AS PortletCode,
                            PO.PORTLETNAME AS PortletName,
                            PO.WIDTH AS PortletWidth,
                            PO.HEIGHT AS PortletHeight,
                            PO.MENUID AS PortletMenuId,
                            RVP.CRITERIACONFIGID AS CriteriaConfigId,
                            CC.CRITERIACONFIGNAME AS CriteriaConfigName,
                            RVP.SLNO AS RoleVsPortletSlNo,
                            RVP.PORTLETROW AS RoleVsPortletPortletRow,
                            RVP.PORTLETCOLUMN AS RoleVsPortletPortletColumn
                            FROM MROLEVSPORTLET RVP
                            LEFT JOIN MROLE RO ON RVP.ROLEID = RO.ROLEID
                            LEFT JOIN MPAGE P ON RVP.PAGEID = P.PAGEID
                            LEFT JOIN MPORTLET PO ON RVP.PORTLETID = PO.PORTLETID
                            LEFT JOIN TCRITERIACONFIG CC ON RVP.CRITERIACONFIGID = CC.CRITERIACONFIGID
                            WHERE RVP.ROLEID = @roleid AND RVP.PAGEID = @pageid
                            AND (
                                @search IS NULL OR @search = ''
                                OR PO.PORTLETCODE LIKE '%'+@search+'%'
                                OR PO.PORTLETNAME LIKE '%'+@search+'%'
                            );
                            ";

        public const string DELETE_ROLEVSPORTLET_WITH_ROLE_AND_PAGE = @"DELETE 
                            FROM MROLEVSPORTLET
                            WHERE ROLEID = @roleid AND PAGEID = @pageid;
                            ";

        public const string GET_PAGE_FOR_ROLEMENU = @"SELECT DISTINCT
                            RVP.PAGEID AS Id,
                            P.PAGECODE AS Code,
                            P.PAGENAME AS Name
                            FROM MROLEVSPORTLET RVP
                            LEFT JOIN MPAGE P ON RVP.PAGEID = P.PAGEID
                            WHERE 1 = 1;
                            ";

        public const string GET_ROLEVSPORTLET_FOR_UPDATE_USERVSPORTLET = @"SELECT
                            U.USERID AS UserId,
                            U.USERCODE AS UserCode,
                            U.USERNAME AS UserName,
                            RVP.PAGEID AS PageId,
                            P.PAGECODE AS PageCode,
                            P.PAGENAME AS PageName,
                            RVP.PORTLETID AS PortletId,
                            PO.PORTLETCODE AS PortletCode,
                            PO.PORTLETNAME AS PortletName,
                            RVP.CRITERIACONFIGID AS CriteriaConfigId,
                            CC.CRITERIACONFIGNAME AS CriteriaConfigName,
                            PO.HEIGHT AS UserVsPortletHeight,
                            PO.WIDTH AS UserVsPortletWidth,
                            RVP.SLNO AS UserVsPortletSlNo,
                            RVP.PORTLETROW AS UserVsPortletPortletRow,
                            RVP.PORTLETCOLUMN AS UserVsPortletPortletColumn
                            FROM MUSER U
                            LEFT JOIN MROLEVSPORTLET RVP ON U.ROLEID = RVP.ROLEID
                            LEFT JOIN MPORTLET PO ON RVP.PORTLETID = PO.PORTLETID
                            LEFT JOIN MPAGE P ON RVP.PAGEID = P.PAGEID
                            LEFT JOIN TCRITERIACONFIG CC ON RVP.CRITERIACONFIGID = CC.CRITERIACONFIGID
                            WHERE RVP.PORTLETID NOT IN (
                                SELECT DISTINCT UVP.PORTLETID
                                FROM MUSERVSPORTLET UVP
                                WHERE UVP.PAGEID = RVP.PAGEID
                                AND UVP.PORTLETID = RVP.PORTLETID
                                AND UVP.USERID = U.USERID
                            ) AND U.USERID IN (
                                SELECT DISTINCT UVP.USERID
                                FROM MUSERVSPORTLET UVP
                                WHERE UVP.USERID = U.USERID
                            ) AND RVP.ROLEID = @roleid;
                            ";

        public const string GET_USERVSPORTLET_FOR_DELETE_USERVSPORTLET = @"SELECT
                            UVP.USERVSPORTLETID AS UserVsPortletId,
                            UVP.USERID AS UserId,
                            U.USERCODE AS UserCode,
                            U.USERNAME AS UserName,
                            UVP.PAGEID AS PageId,
                            P.PAGECODE AS PageCode,
                            P.PAGENAME AS PageName,
                            UVP.PORTLETID AS PortletId,
                            PO.PORTLETCODE AS PortletCode,
                            PO.PORTLETNAME AS PortletName,
                            UVP.CRITERIACONFIGID AS CriteriaConfigId,
                            CC.CRITERIACONFIGNAME AS CriteriaConfigName,
                            UVP.HEIGHT AS UserVsPortletHeight,
                            UVP.WIDTH AS UserVsPortletWidth,
                            UVP.SLNO AS UserVsPortletSlNo,
                            UVP.PORTLETROW AS UserVsPortletPortletRow,
                            UVP.PORTLETCOLUMN AS UserVsPortletPortletColumn
                            FROM MUSERVSPORTLET UVP
                            LEFT JOIN MUSER U ON UVP.USERID = U.USERID
                            LEFT JOIN MPORTLET PO ON UVP.PORTLETID = PO.PORTLETID
                            LEFT JOIN MPAGE P ON UVP.PAGEID = P.PAGEID
                            LEFT JOIN TCRITERIACONFIG CC ON UVP.CRITERIACONFIGID = CC.CRITERIACONFIGID
                            WHERE UVP.PORTLETID NOT IN (
                                SELECT DISTINCT RVP.PORTLETID
                                FROM MROLEVSPORTLET RVP
                                WHERE RVP.PAGEID = UVP.PAGEID
                                AND RVP.PORTLETID = UVP.PORTLETID
                            ) AND UVP.USERID IN (
                                SELECT DISTINCT U.USERID
                                FROM MUSER U
                                WHERE U.USERID = UVP.USERID
                                AND U.ROLEID = @roleid
                            );
                            ";
    }
}