using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FrameworkDAL.Query.Menu { public class MenuQB { public const string GET_MENU_TREE_FOR_USER_MODULE = @"( Select " + "\r\n" + " uservsmenu.MenuId as MenuId," + "\r\n" + " menu.MenuCode as MenuCode," + "\r\n" + " menu.MenuName as MenuName," + "\r\n" + " menu.MenuNature as MenuMenuNature," + "\r\n" + " menu.MenuType as MenuMenuType," + "\r\n" + " menu.ModuleId as ModuleId," + "\r\n" + " mod.ModuleCode as ModuleCode," + "\r\n" + " mod.ModuleName as ModuleName," + "\r\n" + " CASE WHEN ISNULL(A.MenuDisplayName,'') ='' Then menu.MenuDisplayName else A.MenuDisplayName end as MenuDisplayName," + "\r\n" + " CASE WHEN ISNULL(A.FILENAME,'NONE') ='NONE' Then objectmodelmenu.FILENAME else A.FILENAME end as MenuObjectModelName," + "\r\n" + " CASE WHEN ISNULL(A.FILENAME,'NONE') ='NONE' Then objectmodelmenu.FILELOCATION else A.FILELOCATION end as MenuObjectModelFileLocation," + "\r\n" + //" menu.MenuDisplayName as MenuDisplayName," + "\r\n" + " menu.Section as MenuSection," + "\r\n" + " menu.ReportId as ReportId," + "\r\n" + " rep.ReportName as ReportName," + "\r\n" + " menu.EntityId as EntityId," + "\r\n" + " ent.EntityCode as EntityCode," + "\r\n" + " ent.EntityName as EntityName," + "\r\n" + " menu.BizTransactionClassId as MenuBizTransactionClassId," + "\r\n" + " bizclass.BizTransactionClassName as MenuBizTransactionClassName," + "\r\n" + " modvsmen.Levels as MenuLevel," + "\r\n" + " menu.WebFormId as WebFormId," + "\r\n" + " webf.WebFormName as WebFormName," + "\r\n" + " webf.WebFormURL as WebFormURL," + "\r\n" + " webf.WebFormSecondURL as WebFormSecondURL," + "\r\n" + " menu.MenuDescription as MenuDescription," + "\r\n" + " menu.GcmTypeId as GcmTypeId," + "\r\n" + " gcmtype.GcmTypeCode as GcmTypeCode," + "\r\n" + " gcmtype.GcmTypeDescription as GcmTypeName," + "\r\n" + " menu.HelpUrl as HelpUrl," + "\r\n" + " menu.ImageId as ImageId," + "\r\n" + " img.ImageName as ImageName," + "\r\n" + " img.ImageLinkURL as ImageLinkURL," + "\r\n" + " menu.SortOrder as MenuSortOrder," + "\r\n" + " -5 as ParentId," + "\r\n" + //My Favorite " 'My Favorite' as ParentCode," + "\r\n" + " 'My Favorite' as ParentName" + "\r\n" + " ,inc.UserId as InchargeId" + "\r\n" + " ,inc.UserCode as InchargeCode" + "\r\n" + " ,inc.UserName as InchargeName" + "\r\n" + " ,menu.GetWebServiceId as AnalysisWebServiceId" + "\r\n" + " ,menu.ListMenuId as ListMenuId" + "\r\n" + " ,uservsmenu.SLNO as SortOrder" + "\r\n" + " ,uservsmenu.MENUID as UserVsMenuId" + "\r\n" + " ,defmnu.MENUID as DefaultMenuId" + "\r\n" + " ,defmnu.MENUCODE as DefaultMenuCode" + "\r\n" + " ,defmnu.MENUNAME as DefaultMenuName" + "\r\n" + " ,defmnu.MenuNature as DefaultMenuMenuNature" + "\r\n" + " ,defmnu.MenuType as DefaultMenuMenuType" + "\r\n" + " ,defmnu.Section as DefaultMenuSection" + "\r\n" + " ,defmnu.ReportId as DefaultMenuReportId" + "\r\n" + " ,defmnu.EntityId as DefaultMenuEntityId" + "\r\n" + " ,defmnu.BizTransactionClassId as DefaultMenuBizTransactionClassId" + "\r\n" + " ,defclass.BIZTRANSACTIONCLASSNAME as DefaultMenuBizTransactionClassName" + "\r\n" + " ,defmnu.GCMTYPEID as DefaultMenuGCMTypeId" + "\r\n" + " ,defmnu.WebFormId as DefaultMenuWebFormId" + "\r\n" + " ,defwebfrm.WEBFORMNAME as DefaultMenuWebFormName" + "\r\n" + " ,defwebfrm.WEBFORMURL as DefaultMenuWebFormUrl" + "\r\n" + " ,defmnu.HelpUrl as DefaultMenuHelpUrl" + "\r\n" + " ,defmnu.ImageId as DefaultMenuImageId" + "\r\n" + " ,webf.OBJECTMODELIDS as MenuWebFormObjectModelIds" + "\r\n" + " ,defwebfrm.OBJECTMODELIDS as DefaultWebFormObjectModelIds" + "\r\n" + " ,menu.PageId as PageId" + "\r\n" + " ,pg.PageCode as PageCode" + "\r\n" + " ,pg.PageName as PageName" + "\r\n" + " ,menu.TARGETTYPE as TargetType" + "\r\n" + " ,menu.TARGETID as TargetId" + "\r\n" + " ,menu.ICONNAME as MenuIconName" + "\r\n" + " from " + "\r\n" + " MUSERVSMENU uservsmenu,MMENU menu, MMODULE mod,MUSER muser,MENTITY ent,MREPORT rep,MBIZTransactionClass bizclass,mmodulevsmenu modvsmen" + "\r\n" + " left outer join( Select menuset.MODULEID,menusetdetail.MENUID,menusetdetail.MENUDISPLAYNAME,menusetdetail.SORTORDER,objectmodel.FILENAME,objectmodel.FILELOCATION " + "\r\n" + " from " + "\r\n" + " MClientModule clientmodule, MMenuSet menuset,MMenusetDetail menusetdetail,MFILE objectmodel" + "\r\n" + " where 1=1 " + "\r\n" + " and clientmodule.ClientId =@clientid " + "\r\n" + //" and clientmodule.ModuleId =@moduleid " + "\r\n" + " and clientmodule.ModuleId = menuset.ModuleId " + "\r\n" + " and clientmodule.MenuSetId = menuset.MenuSetId " + "\r\n" + " and menuset.MenuSetId = menusetdetail.MenuSetId " + "\r\n" + " and menusetdetail.ISVISIBLE = 0" + "\r\n" + //Added by parthiban D on 26Mar2019 " and menusetdetail.OBJECTMODELID = objectmodel.fileid " + "\r\n" + " ) A on ( modvsmen.Moduleid = A.ModuleId and modvsmen.MenuId = A.MenuId) " + "\r\n" + " ,MWEBFORM webf" + "\r\n" + " ,MGCMTYPE gcmtype,MIMAGE img,MUSER inc,MMENU defmnu,MBIZTRANSACTIONCLASS defclass,MWEBFORM defwebfrm,MFILE objectmodelmenu" + "\r\n" + " ,MPAGE pg" + "\r\n" + " where 1=1 " + "\r\n" + " and uservsmenu.UserId =@userid " + "\r\n" + //" and mod.ModuleId =@moduleid " + "\r\n" + //Commented on 21Sep2022 " and uservsmenu.MENUID = menu.MENUID" + "\r\n" + " and menu.MODULEID = mod.MODULEID" + "\r\n" + " and uservsmenu.USERID = muser.USERID" + "\r\n" + " and menu.EntityId = ent.EntityId" + "\r\n" + " and menu.ReportId = rep.ReportId" + "\r\n" + " and menu.BizTransactionClassId = bizclass.BizTransactionClassId" + "\r\n" + " and modvsmen.ModuleId = mod.ModuleId" + "\r\n" + " and modvsmen.Menuid = menu.menuid" + "\r\n" + " and webf.WEBFORMID = menu.WEBFORMID" + "\r\n" + " and menu.GcmTypeId = gcmtype.GcmTypeId" + "\r\n" + " and menu.IMAGEID = img.IMAGEID" + "\r\n" + " and inc.UserId = menu.CreatedById" + "\r\n" + " and menu.defaultmenuid = defmnu.MENUID" + "\r\n" + " and defmnu.BIZTRANSACTIONCLASSID = defclass.BIZTRANSACTIONCLASSID" + "\r\n" + " and defmnu.WEBFORMID = defwebfrm.WEBFORMID" + "\r\n" + " and menu.OBJECTMODELID = objectmodelmenu.FILEID" + "\r\n" + //" and menu.FromVersion >=@feversion" + "\r\n" + // Added for gb4.5 //" and menu.ToVersion <@feversion" + "\r\n" + // Added for gb4.5 " and @feversion between ( menu.FROMVERSION ) and (menu.TOVERSION)" + "\r\n" + // Added for gb4.5 " and menu.PAGEID = pg.PAGEID" + "\r\n" + " UNION ALL " + "\r\n" + " Select" + "\r\n" + " -5 as MenuId," + "\r\n" + " 'My Favorite' as MenuCode," + "\r\n" + " 'My Favorite' as MenuName," + "\r\n" + " 2 as MenuMenuNature," + "\r\n" + " 3 as MenuMenuType," + "\r\n" + " -1 as ModuleId," + "\r\n" + " 'NONE' as ModuleCode," + "\r\n" + " 'NONE' as ModuleName," + "\r\n" + " 'My Favorite' as MenuDisplayName," + "\r\n" + " '' as MenuObjectModelName," + "\r\n" + " '' as MenuObjectModelFileLocation," + "\r\n" + " 'Favorite' as MenuSection," + "\r\n" + " -1 as ReportId," + "\r\n" + " 'NONE' as ReportName," + "\r\n" + " -1 as EntityId," + "\r\n" + " 'NONE' as EntityCode," + "\r\n" + " 'NONE' as EntityName," + "\r\n" + " -1 as MenuBizTransactionClassId," + "\r\n" + " 'NONE' as MenuBizTransactionClassName," + "\r\n" + " 1 as MenuLevel," + "\r\n" + " -1 as WebFormId," + "\r\n" + " 'NONE' as WebFormName," + "\r\n" + " 'NONE' as WebFormURL," + "\r\n" + " 'NONE' as WebFormSecondURL," + "\r\n" + " 'My Favorite' as MenuDescription," + "\r\n" + " -1 as GcmTypeId," + "\r\n" + " 'NONE' as GcmTypeCode," + "\r\n" + " 'NONE' as GcmTypeName," + "\r\n" + " 'Favorite' as HelpUrl," + "\r\n" + " -1 as ImageId," + "\r\n" + " 'NONE' as ImageName," + "\r\n" + " 'NONE' as ImageLinkURL," + "\r\n" + " cast(0 as smallint) as MenuSortOrder," + "\r\n" + " -1 as ParentId," + "\r\n" + " 'NONE' as ParentCode," + "\r\n" + " 'NONE' as ParentName" + "\r\n" + " ,-1 as InchargeId" + "\r\n" + " ,'NONE' as InchargeCode," + "\r\n" + " 'NONE' as InchargeName" + "\r\n" + " ,-1 as AnalysisWebServiceId" + "\r\n" + " ,-1 as ListMenuId" + "\r\n" + " ,cast(-1 as smallint) as SortOrder" + "\r\n" + " ,-1 as UserVsMenuId" + "\r\n" + " ,-1 as DefaultMenuId" + "\r\n" + " ,'NONE' as DefaultMenuCode" + "\r\n" + " ,'NONE' as DefaultMenuName" + "\r\n" + " ,2 as DefaultMenuMenuNature" + "\r\n" + " ,3 as DefaultMenuMenuType" + "\r\n" + " ,'NONE' as DefaultMenuSection" + "\r\n" + " ,-1 as DefaultMenuReportId" + "\r\n" + " ,-1 as DefaultMenuEntityId" + "\r\n" + " ,-1 as DefaultMenuBizTransactionClassId" + "\r\n" + " ,'NONE' as DefaultMenuBizTransactionClassName" + "\r\n" + " ,-1 as DefaultMenuGCMTypeId" + "\r\n" + " ,-1 as DefaultMenuWebFormId" + "\r\n" + " ,'NONE' as DefaultMenuWebFormName" + "\r\n" + " ,'NONE' as DefaultMenuWebFormUrl" + "\r\n" + " ,'NONE' as DefaultMenuHelpUrl" + "\r\n" + " ,-1 as DefaultMenuImageId" + "\r\n" + " ,'-1' as MenuWebFormObjectModelIds" + "\r\n" + " ,'-1' as DefaultWebFormObjectModelIds" + "\r\n" + " ,-1 as PageId" + "\r\n" + " ,'NONE' as PageCode" + "\r\n" + " ,'NONE' as PageName" + "\r\n" + " ,cast(0 as tinyint) as TargetType" + "\r\n" + " ,-1 as TargetId" + "\r\n" + " ,'' as MenuIconName" + "\r\n" + //@replace " + "\r\n" + " Union all" + "\r\n" + " select " + "\r\n" + " modulevsmenu.MenuId as MenuId," + "\r\n" + " menu.MenuCode as MenuCode," + "\r\n" + " menu.MenuName as MenuName," + "\r\n" + " menu.MenuNature as MenuMenuNature," + "\r\n" + " menu.MenuType as MenuMenuType," + "\r\n" + " modulevsmenu.ModuleId as ModuleId," + "\r\n" + " module.ModuleCode as ModuleCode," + "\r\n" + " module.ModuleName as ModuleName," + "\r\n" + " CASE WHEN ISNULL(A.MenuDisplayName,'') ='' Then menu.MenuDisplayName else A.MenuDisplayName end as MenuDisplayName," + "\r\n" + " CASE WHEN ISNULL(A.FILENAME,'NONE') ='NONE' Then objectmodelmenu.FILENAME else A.FILENAME end as MenuObjectModelName," + "\r\n" + " CASE WHEN ISNULL(A.FILENAME,'NONE') ='NONE' Then objectmodelmenu.FILELOCATION else A.FILELOCATION end as MenuObjectModelFileLocation," + "\r\n" + //" menu.MenuDisplayName as MenuDisplayName," + "\r\n" + " menu.Section as MenuSection," + "\r\n" + " menu.ReportId as ReportId," + "\r\n" + " report.ReportName as ReportName," + "\r\n" + " menu.EntityId as EntityId," + "\r\n" + " entity.EntityCode as EntityCode," + "\r\n" + " entity.EntityName as EntityName," + "\r\n" + " menu.BizTransactionClassId as MenuBizTransactionClassId," + "\r\n" + " biztransactionclass.BizTransactionClassName as MenuBizTransactionClassName," + "\r\n" + " modulevsmenu.Levels as MenuLevel," + "\r\n" + " menu.WebFormId as WebFormId," + "\r\n" + " webform.WebFormName as WebFormName," + "\r\n" + " webform.WebFormURL as WebFormURL," + "\r\n" + " webform.WebFormSecondURL as WebFormSecondURL," + "\r\n" + " menu.MenuDescription as MenuDescription," + "\r\n" + " menu.GcmTypeId as GcmTypeId," + "\r\n" + " gcmtype.GcmTypeCode as GcmTypeCode," + "\r\n" + " gcmtype.GcmTypeDescription as GcmTypeName," + "\r\n" + " menu.HelpUrl as HelpUrl," + "\r\n" + " menu.ImageId as ImageId," + "\r\n" + " image.ImageName as ImageName," + "\r\n" + " image.ImageLinkURL as ImageLinkURL," + "\r\n" + " menu.SortOrder as MenuSortOrder," + "\r\n" + " modulevsmenu.ParentId as ParentId," + "\r\n" + " parentmenu.MenuCode as ParentCode," + "\r\n" + " parentmenu.MenuName as ParentName" + "\r\n" + " ,Incharge.UserId as InchargeId" + "\r\n" + " ,Incharge.UserCode as InchargeCode," + "\r\n" + " Incharge.UserName as InchargeName" + "\r\n" + " ,menu.GetWebServiceId as AnalysisWebServiceId" + "\r\n" + " ,menu.ListMenuId as ListMenuId" + "\r\n" + " ,A.SortOrder as SortOrder" + "\r\n" + //Added newly on 10Dec2018 by Parthiban D " ,isnull(usermenu.USERVSMENUID,-1) as UserVsMenuId" + "\r\n" + " ,defaultmenu.MENUID as DefaultMenuId" + "\r\n" + " ,defaultmenu.MENUCODE as DefaultMenuCode" + "\r\n" + " ,defaultmenu.MENUNAME as DefaultMenuName" + "\r\n" + " ,defaultmenu.MenuNature as DefaultMenuMenuNature" + "\r\n" + " ,defaultmenu.MenuType as DefaultMenuMenuType" + "\r\n" + " ,defaultmenu.Section as DefaultMenuSection" + "\r\n" + " ,defaultmenu.ReportId as DefaultMenuReportId" + "\r\n" + " ,defaultmenu.EntityId as DefaultMenuEntityId" + "\r\n" + " ,defaultmenu.BizTransactionClassId as DefaultMenuBizTransactionClassId" + "\r\n" + " ,defmnubizclass.BIZTRANSACTIONCLASSNAME as DefaultMenuBizTransactionClassName" + "\r\n" + " ,defaultmenu.GCMTYPEID as DefaultMenuGCMTypeId" + "\r\n" + " ,defaultmenu.WebFormId as DefaultMenuWebFormId" + "\r\n" + " ,defmnuwebform.WEBFORMNAME as DefaultMenuWebFormName" + "\r\n" + " ,defmnuwebform.WEBFORMURL as DefaultMenuWebFormUrl" + "\r\n" + " ,defaultmenu.HelpUrl as DefaultMenuHelpUrl" + "\r\n" + " ,defaultmenu.ImageId as DefaultMenuImageId" + "\r\n" + " ,webform.OBJECTMODELIDS as MenuWebFormObjectModelIds" + "\r\n" + " ,defmnuwebform.OBJECTMODELIDS as DefaultWebFormObjectModelIds" + "\r\n" + " ,menu.PageId as PageId" + "\r\n" + " ,pg.PageCode as PageCode" + "\r\n" + " ,pg.PageName as PageName" + "\r\n" + " ,menu.TARGETTYPE as TargetType" + "\r\n" + " ,menu.TARGETID as TargetId" + "\r\n" + " ,menu.ICONNAME as MenuIconName" + "\r\n" + " from" + "\r\n" + " MRoleVsModules rolevsmodules,MModuleVsMenu modulevsmenu " + "\r\n" + " left outer join( Select menuset.MODULEID,menusetdetail.MENUID,menusetdetail.MENUDISPLAYNAME,menusetdetail.SORTORDER,OBJECTMODEL.FILENAME,OBJECTMODEL.FILELOCATION " + "\r\n" + " from " + "\r\n" + " MClientModule clientmodule, MMenuSet menuset,MMenusetDetail menusetdetail,MFILE OBJECTMODEL" + "\r\n" + " where 1=1 " + "\r\n" + " and clientmodule.ClientId =@clientid " + "\r\n" + " and clientmodule.ModuleId =@moduleid " + "\r\n" + " and clientmodule.ModuleId = menuset.ModuleId " + "\r\n" + " and clientmodule.MenuSetId = menuset.MenuSetId " + "\r\n" + " and menuset.MenuSetId = menusetdetail.MenuSetId " + "\r\n" + " and menusetdetail.ISVISIBLE = 0" + "\r\n" + //Added by parthiban D on 26Mar2019 " and menusetdetail.OBJECTMODELID = OBJECTMODEL.FILEID " + "\r\n" + " ) A on ( modulevsmenu.Moduleid = A.ModuleId and modulevsmenu.MenuId = A.MenuId), " + "\r\n" + " MUser muser,MMenu menu Left outer join MUSERVSMENU usermenu on( menu.menuid = usermenu.menuid and usermenu.UserId =@userid),MModule module,MEntity entity,MRoleVsMenu rolevsmenu," + "\r\n" + " MMenu parentmenu,MReport report,MBIZTransactionClass biztransactionclass," + "\r\n" + " MWebForm webform,MGcmType gcmtype,MImage image,MRole role,Muser Incharge," + "\r\n" + " MMENU defaultmenu,MBIZTRANSACTIONCLASS defmnubizclass,MWEBFORM defmnuwebform,MFILE objectmodelmenu," + "\r\n" + " MPAGE pg" + "\r\n" + " where 1=1" + "\r\n" + " and muser.UserId =@userid " + "\r\n" + " and muser.RoleId = role.RoleId" + "\r\n" + " and rolevsmodules.RoleId = muser.RoleId " + "\r\n" + " and rolevsmodules.ModuleId =@moduleid " + "\r\n" + " and rolevsmodules.RoleId = role.RoleId" + "\r\n" + " and rolevsmodules.ModuleId = modulevsmenu.ModuleId" + "\r\n" + " and modulevsmenu.ModuleId = module.ModuleId" + "\r\n" + " and modulevsmenu.MenuId = menu.MenuId" + "\r\n" + //" and menu.Module.Id = module.Id" + "\r\n" + //Accross module menus may come under one logical module in MModuleVsMenus. - So, this condition not required - by parthiban D " and menu.EntityId = entity.EntityId" + "\r\n" + " and rolevsmenu.MenuId = menu.MenuId" + "\r\n" + " and rolevsmenu.RoleId = role.RoleId" + "\r\n" + //" and (rolevsmenu.Allow like '%0%')" + "\r\n" + // Commented and added by parthiban D on 26Apr2017 - Redmine 17098 " and (SUBSTRING(rolevsmenu.Allow,1,1) ='0')" + "\r\n" + " and menu.ReportId = report.ReportId" + "\r\n" + " and menu.BizTransactionClassId = biztransactionclass.BizTransactionClassId" + "\r\n" + " and menu.WebFormId = webform.WebFormId" + "\r\n" + " and menu.GcmTypeId = gcmtype.GcmTypeId" + "\r\n" + " and menu.ImageId = image.ImageId" + "\r\n" + " and (menu.Status in (0 , 1))" + "\r\n" + " and menu.MENUTYPE <> 4 " + "\r\n" + // Not 4.BOT " and modulevsmenu.ParentId = parentmenu.MenuId" + "\r\n" + //Added by Vikash on 11 Sep 2014 Against Task #5718 " and Incharge.UserId = menu.CreatedById" + "\r\n" + //Till This " and menu.defaultmenuid = defaultmenu.MENUID" + "\r\n" + " and defaultmenu.BIZTRANSACTIONCLASSID = defmnubizclass.BIZTRANSACTIONCLASSID" + "\r\n" + " and defaultmenu.WEBFORMID = defmnuwebform.WEBFORMID" + "\r\n" + " and MENU.OBJECTMODELID = objectmodelmenu.FILEID" + "\r\n" + //" and menu.FromVersion >=@feversion" + "\r\n" + // Added for gb4.5 //" and menu.ToVersion <@feversion" + "\r\n" + // Added for gb4.5 //" and @feversion between ( menu.FROMVERSION ) and (menu.TOVERSION)" + "\r\n" + // Added for gb4.5 " and menu.PAGEID = pg.PAGEID" + "\r\n" + // Added by Parthiban D on 07May2014 For Advance User Rights checking " and menu.BizTransactionClassId NOT IN " + "\r\n" + " (select distinct a.BIZTRANSACTIONCLASSID from MBIZTRANSACTIONSUBCLASS a,MBIZTRANSACTION b,MADVANCERIGHTS c" + "\r\n" + " where a.BIZTRANSACTIONSUBCLASSID = b.BIZTRANSACTIONSUBCLASSID" + "\r\n" + " and b.BIZTRANSACTIONID = c.BIZTRANSACTIONID" + "\r\n" + " and c.ADVANCERIGHTSID <> -1" + "\r\n" + " and c.UserId =@userid " + "\r\n" + " and c.ALLOW ='11111' and a.BIZTRANSACTIONCLASSID NOT IN (" + "\r\n" + " select distinct a.BIZTRANSACTIONCLASSID from MBIZTRANSACTIONSUBCLASS a,MBIZTRANSACTION b,MADVANCERIGHTS c" + "\r\n" + " where a.BIZTRANSACTIONSUBCLASSID = b.BIZTRANSACTIONSUBCLASSID" + "\r\n" + " and b.BIZTRANSACTIONID = c.BIZTRANSACTIONID" + "\r\n" + " and c.ADVANCERIGHTSID <> -1" + "\r\n" + " and c.UserId =@userid " + "\r\n" + " and c.ALLOW like '%0%'" + "\r\n" + " ))" + "\r\n" + //Till This " ) " + "\r\n" + //" Order by A.SortOrder" + "\r\n"; //Commented and added by parthiban D on 10Dec2018 " Order by SortOrder" + "\r\n"; public const string GET_MENU_TREE_FOR_USER_MODULE_POSTGRE = @" ( SELECT uservsmenu.MenuId as MenuId, menu.MenuCode as MenuCode, menu.MenuName as MenuName, menu.MenuNature as MenuMenuNature, menu.MenuType as MenuMenuType, menu.ModuleId as ModuleId, mod.ModuleCode as ModuleCode, mod.ModuleName as ModuleName, CASE WHEN COALESCE(A.MenuDisplayName,'') = '' THEN menu.MenuDisplayName ELSE A.MenuDisplayName END as MenuDisplayName, CASE WHEN COALESCE(A.FILENAME,'NONE') = 'NONE' THEN objectmodelmenu.FILENAME ELSE A.FILENAME END as MenuObjectModelName, CASE WHEN COALESCE(A.FILENAME,'NONE') = 'NONE' THEN objectmodelmenu.FILELOCATION ELSE A.FILELOCATION END as MenuObjectModelFileLocation, menu.Section as MenuSection, menu.ReportId as ReportId, rep.ReportName as ReportName, menu.EntityId as EntityId, ent.EntityCode as EntityCode, ent.EntityName as EntityName, menu.BizTransactionClassId as MenuBizTransactionClassId, bizclass.BizTransactionClassName as MenuBizTransactionClassName, modvsmen.Levels as MenuLevel, menu.WebFormId as WebFormId, webf.WebFormName as WebFormName, webf.WebFormURL as WebFormURL, webf.WebFormSecondURL as WebFormSecondURL, menu.MenuDescription as MenuDescription, menu.GcmTypeId as GcmTypeId, gcmtype.GcmTypeCode as GcmTypeCode, gcmtype.GcmTypeDescription as GcmTypeName, menu.HelpUrl as HelpUrl, menu.ImageId as ImageId, img.ImageName as ImageName, img.ImageLinkURL as ImageLinkURL, menu.SortOrder as MenuSortOrder, -5 as ParentId, 'My Favorite' as ParentCode, 'My Favorite' as ParentName, inc.UserId as InchargeId, inc.UserCode as InchargeCode, inc.UserName as InchargeName, menu.GetWebServiceId as AnalysisWebServiceId, menu.ListMenuId as ListMenuId, uservsmenu.SLNO as SortOrder, uservsmenu.MENUID as UserVsMenuId, defmnu.MENUID as DefaultMenuId, defmnu.MENUCODE as DefaultMenuCode, defmnu.MENUNAME as DefaultMenuName, defmnu.MenuNature as DefaultMenuMenuNature, defmnu.MenuType as DefaultMenuMenuType, defmnu.Section as DefaultMenuSection, defmnu.ReportId as DefaultMenuReportId, defmnu.EntityId as DefaultMenuEntityId, defmnu.BizTransactionClassId as DefaultMenuBizTransactionClassId, defclass.BIZTRANSACTIONCLASSNAME as DefaultMenuBizTransactionClassName, defmnu.GCMTYPEID as DefaultMenuGCMTypeId, defmnu.WebFormId as DefaultMenuWebFormId, defwebfrm.WEBFORMNAME as DefaultMenuWebFormName, defwebfrm.WEBFORMURL as DefaultMenuWebFormUrl, defmnu.HelpUrl as DefaultMenuHelpUrl, defmnu.ImageId as DefaultMenuImageId, webf.OBJECTMODELIDS as MenuWebFormObjectModelIds, defwebfrm.OBJECTMODELIDS as DefaultWebFormObjectModelIds, menu.PageId as PageId, pg.PageCode as PageCode, pg.PageName as PageName, menu.TARGETTYPE as TargetType, menu.TARGETID as TargetId, menu.ICONNAME as MenuIconName FROM MUSERVSMENU uservsmenu, MMENU menu, MMODULE mod, MUSER muser, MENTITY ent, MREPORT rep, MBIZTransactionClass bizclass, mmodulevsmenu modvsmen LEFT OUTER JOIN ( SELECT menuset.MODULEID, menusetdetail.MENUID, menusetdetail.MENUDISPLAYNAME, menusetdetail.SORTORDER, objectmodel.FILENAME, objectmodel.FILELOCATION FROM MClientModule clientmodule, MMenuSet menuset, MMenusetDetail menusetdetail, MFILE objectmodel WHERE 1=1 AND clientmodule.ClientId = @clientid AND clientmodule.ModuleId = menuset.ModuleId AND clientmodule.MenuSetId = menuset.MenuSetId AND menuset.MenuSetId = menusetdetail.MenuSetId AND menusetdetail.ISVISIBLE = 0 --Added by parthiban D on 26Mar2019 AND menusetdetail.OBJECTMODELID = objectmodel.fileid ) A ON (modvsmen.Moduleid = A.ModuleId AND modvsmen.MenuId = A.MenuId), MWEBFORM webf, MGCMTYPE gcmtype, MIMAGE img, MUSER inc, MMENU defmnu, MBIZTRANSACTIONCLASS defclass, MWEBFORM defwebfrm, MFILE objectmodelmenu, MPAGE pg WHERE 1=1 AND uservsmenu.UserId = @userid AND uservsmenu.MENUID = menu.MENUID AND menu.MODULEID = mod.MODULEID AND uservsmenu.USERID = muser.USERID AND menu.EntityId = ent.EntityId AND menu.ReportId = rep.ReportId AND menu.BizTransactionClassId = bizclass.BizTransactionClassId AND modvsmen.ModuleId = mod.ModuleId AND modvsmen.Menuid = menu.menuid AND webf.WEBFORMID = menu.WEBFORMID AND menu.GcmTypeId = gcmtype.GcmTypeId AND menu.IMAGEID = img.IMAGEID AND inc.UserId = menu.CreatedById AND menu.defaultmenuid = defmnu.MENUID AND defmnu.BIZTRANSACTIONCLASSID = defclass.BIZTRANSACTIONCLASSID AND defmnu.WEBFORMID = defwebfrm.WEBFORMID AND menu.OBJECTMODELID = objectmodelmenu.FILEID AND @feversion BETWEEN (menu.FROMVERSION) AND (menu.TOVERSION) -- Added for gb4.5 AND menu.PAGEID = pg.PAGEID UNION ALL SELECT -5 as MenuId, 'My Favorite' as MenuCode, 'My Favorite' as MenuName, 2 as MenuMenuNature, 3 as MenuMenuType, -1 as ModuleId, 'NONE' as ModuleCode, 'NONE' as ModuleName, 'My Favorite' as MenuDisplayName, '' as MenuObjectModelName, '' as MenuObjectModelFileLocation, 'Favorite' as MenuSection, -1 as ReportId, 'NONE' as ReportName, -1 as EntityId, 'NONE' as EntityCode, 'NONE' as EntityName, -1 as MenuBizTransactionClassId, 'NONE' as MenuBizTransactionClassName, 1 as MenuLevel, -1 as WebFormId, 'NONE' as WebFormName, 'NONE' as WebFormURL, 'NONE' as WebFormSecondURL, 'My Favorite' as MenuDescription, -1 as GcmTypeId, 'NONE' as GcmTypeCode, 'NONE' as GcmTypeName, 'Favorite' as HelpUrl, -1 as ImageId, 'NONE' as ImageName, 'NONE' as ImageLinkURL, CAST(0 as smallint) as MenuSortOrder, -1 as ParentId, 'NONE' as ParentCode, 'NONE' as ParentName, -1 as InchargeId, 'NONE' as InchargeCode, 'NONE' as InchargeName, -1 as AnalysisWebServiceId, -1 as ListMenuId, CAST(-1 as smallint) as SortOrder, -1 as UserVsMenuId, -1 as DefaultMenuId, 'NONE' as DefaultMenuCode, 'NONE' as DefaultMenuName, 2 as DefaultMenuMenuNature, 3 as DefaultMenuMenuType, 'NONE' as DefaultMenuSection, -1 as DefaultMenuReportId, -1 as DefaultMenuEntityId, -1 as DefaultMenuBizTransactionClassId, 'NONE' as DefaultMenuBizTransactionClassName, -1 as DefaultMenuGCMTypeId, -1 as DefaultMenuWebFormId, 'NONE' as DefaultMenuWebFormName, 'NONE' as DefaultMenuWebFormUrl, 'NONE' as DefaultMenuHelpUrl, -1 as DefaultMenuImageId, '-1' as MenuWebFormObjectModelIds, '-1' as DefaultWebFormObjectModelIds, -1 as PageId, 'NONE' as PageCode, 'NONE' as PageName, cast(0 as smallint) as TargetType, -1 as TargetId, '' as MenuIconName UNION ALL SELECT modulevsmenu.MenuId as MenuId, menu.MenuCode as MenuCode, menu.MenuName as MenuName, menu.MenuNature as MenuMenuNature, menu.MenuType as MenuMenuType, modulevsmenu.ModuleId as ModuleId, module.ModuleCode as ModuleCode, module.ModuleName as ModuleName, CASE WHEN COALESCE(A.MenuDisplayName,'') = '' THEN menu.MenuDisplayName ELSE A.MenuDisplayName END as MenuDisplayName, CASE WHEN COALESCE(A.FILENAME,'NONE') = 'NONE' THEN objectmodelmenu.FILENAME ELSE A.FILENAME END as MenuObjectModelName, CASE WHEN COALESCE(A.FILENAME,'NONE') = 'NONE' THEN objectmodelmenu.FILELOCATION ELSE A.FILELOCATION END as MenuObjectModelFileLocation, menu.Section as MenuSection, menu.ReportId as ReportId, report.ReportName as ReportName, menu.EntityId as EntityId, entity.EntityCode as EntityCode, entity.EntityName as EntityName, menu.BizTransactionClassId as MenuBizTransactionClassId, biztransactionclass.BizTransactionClassName as MenuBizTransactionClassName, modulevsmenu.Levels as MenuLevel, menu.WebFormId as WebFormId, webform.WebFormName as WebFormName, webform.WebFormURL as WebFormURL, webform.WebFormSecondURL as WebFormSecondURL, menu.MenuDescription as MenuDescription, menu.GcmTypeId as GcmTypeId, gcmtype.GcmTypeCode as GcmTypeCode, gcmtype.GcmTypeDescription as GcmTypeName, menu.HelpUrl as HelpUrl, menu.ImageId as ImageId, image.ImageName as ImageName, image.ImageLinkURL as ImageLinkURL, menu.SortOrder as MenuSortOrder, modulevsmenu.ParentId as ParentId, parentmenu.MenuCode as ParentCode, parentmenu.MenuName as ParentName, Incharge.UserId as InchargeId, Incharge.UserCode as InchargeCode, Incharge.UserName as InchargeName, menu.GetWebServiceId as AnalysisWebServiceId, menu.ListMenuId as ListMenuId, A.SortOrder as SortOrder, --Added newly on 10Dec2018 by Parthiban D COALESCE(usermenu.USERVSMENUID,-1) as UserVsMenuId, defaultmenu.MENUID as DefaultMenuId, defaultmenu.MENUCODE as DefaultMenuCode, defaultmenu.MENUNAME as DefaultMenuName, defaultmenu.MenuNature as DefaultMenuMenuNature, defaultmenu.MenuType as DefaultMenuMenuType, defaultmenu.Section as DefaultMenuSection, defaultmenu.ReportId as DefaultMenuReportId, defaultmenu.EntityId as DefaultMenuEntityId, defaultmenu.BizTransactionClassId as DefaultMenuBizTransactionClassId, defmnubizclass.BIZTRANSACTIONCLASSNAME as DefaultMenuBizTransactionClassName, defaultmenu.GCMTYPEID as DefaultMenuGCMTypeId, defaultmenu.WebFormId as DefaultMenuWebFormId, defmnuwebform.WEBFORMNAME as DefaultMenuWebFormName, defmnuwebform.WEBFORMURL as DefaultMenuWebFormUrl, defaultmenu.HelpUrl as DefaultMenuHelpUrl, defaultmenu.ImageId as DefaultMenuImageId, webform.OBJECTMODELIDS as MenuWebFormObjectModelIds, defmnuwebform.OBJECTMODELIDS as DefaultWebFormObjectModelIds, menu.PageId as PageId, pg.PageCode as PageCode, pg.PageName as PageName, menu.TARGETTYPE as TargetType, menu.TARGETID as TargetId, menu.ICONNAME as MenuIconName FROM MRoleVsModules rolevsmodules, MModuleVsMenu modulevsmenu LEFT OUTER JOIN ( SELECT menuset.MODULEID, menusetdetail.MENUID, menusetdetail.MENUDISPLAYNAME, menusetdetail.SORTORDER, OBJECTMODEL.FILENAME, OBJECTMODEL.FILELOCATION FROM MClientModule clientmodule, MMenuSet menuset, MMenusetDetail menusetdetail, MFILE OBJECTMODEL WHERE 1=1 AND clientmodule.ClientId = @clientid AND clientmodule.ModuleId = @moduleid AND clientmodule.ModuleId = menuset.ModuleId AND clientmodule.MenuSetId = menuset.MenuSetId AND menuset.MenuSetId = menusetdetail.MenuSetId AND menusetdetail.ISVISIBLE = 0 --Added by parthiban D on 26Mar2019 AND menusetdetail.OBJECTMODELID = OBJECTMODEL.FILEID ) A ON (modulevsmenu.Moduleid = A.ModuleId AND modulevsmenu.MenuId = A.MenuId), MUser muser, MMenu menu LEFT OUTER JOIN MUSERVSMENU usermenu ON (menu.menuid = usermenu.menuid AND usermenu.UserId = @userid), MModule module, MEntity entity, MRoleVsMenu rolevsmenu, MMenu parentmenu, MReport report, MBIZTransactionClass biztransactionclass, MWebForm webform, MGcmType gcmtype, MImage image, MRole role, Muser Incharge, MMENU defaultmenu, MBIZTRANSACTIONCLASS defmnubizclass, MWEBFORM defmnuwebform, MFILE objectmodelmenu, MPAGE pg WHERE 1=1 AND muser.UserId = @userid AND muser.RoleId = role.RoleId AND rolevsmodules.RoleId = muser.RoleId AND rolevsmodules.ModuleId = @moduleid AND rolevsmodules.RoleId = role.RoleId AND rolevsmodules.ModuleId = modulevsmenu.ModuleId AND modulevsmenu.ModuleId = module.ModuleId AND modulevsmenu.MenuId = menu.MenuId AND menu.EntityId = entity.EntityId AND rolevsmenu.MenuId = menu.MenuId AND rolevsmenu.RoleId = role.RoleId AND (SUBSTRING(rolevsmenu.Allow,1,1) = '0') -- Commented and added by parthiban D on 26Apr2017 - Redmine 17098 AND menu.ReportId = report.ReportId AND menu.BizTransactionClassId = biztransactionclass.BizTransactionClassId AND menu.WebFormId = webform.WebFormId AND menu.GcmTypeId = gcmtype.GcmTypeId AND menu.ImageId = image.ImageId AND (menu.Status IN (0, 1)) AND menu.MENUTYPE <> 4 -- Not 4.BOT AND modulevsmenu.ParentId = parentmenu.MenuId AND Incharge.UserId = menu.CreatedById --Added by Vikash on 11 Sep 2014 Against Task #5718 AND menu.defaultmenuid = defaultmenu.MENUID AND defaultmenu.BIZTRANSACTIONCLASSID = defmnubizclass.BIZTRANSACTIONCLASSID AND defaultmenu.WEBFORMID = defmnuwebform.WEBFORMID AND MENU.OBJECTMODELID = objectmodelmenu.FILEID AND menu.PAGEID = pg.PAGEID AND menu.BizTransactionClassId NOT IN ( SELECT DISTINCT a.BIZTRANSACTIONCLASSID FROM MBIZTRANSACTIONSUBCLASS a, MBIZTRANSACTION b, MADVANCERIGHTS c WHERE a.BIZTRANSACTIONSUBCLASSID = b.BIZTRANSACTIONSUBCLASSID AND b.BIZTRANSACTIONID = c.BIZTRANSACTIONID AND c.ADVANCERIGHTSID <> -1 AND c.UserId = @userid AND c.ALLOW = '11111' AND a.BIZTRANSACTIONCLASSID NOT IN ( SELECT DISTINCT a.BIZTRANSACTIONCLASSID FROM MBIZTRANSACTIONSUBCLASS a, MBIZTRANSACTION b, MADVANCERIGHTS c WHERE a.BIZTRANSACTIONSUBCLASSID = b.BIZTRANSACTIONSUBCLASSID AND b.BIZTRANSACTIONID = c.BIZTRANSACTIONID AND c.ADVANCERIGHTSID <> -1 AND c.UserId = @userid AND c.ALLOW LIKE '%0%' ) ) -- Added by Parthiban D on 07May2014 For Advance User Rights checking ) ORDER BY SortOrder"; public static string GET_MENU = @"SELECT M.MENUID AS MenuId, M.MENUCODE AS MenuCode, M.MENUNAME AS MenuName, M.MODULEID AS ModuleId, MOD.MODULECODE AS ModuleCode, MOD.MODULENAME AS ModuleName, M.GCMTYPEID AS GcmTypeId, GCT.GCMTYPECODE AS GcmTypeCode, M.SECTION AS MenuSection, M.ISPARENTMENU AS MenuIsParentMenu, M.PARENTID AS ParentId, PM.MENUCODE AS ParentCode, PM.MENUNAME AS ParentName, M.PARENTIDS AS MenuParentIds, M.LEVELS AS MenuLevel, M.REMARKS AS MenuRemarks, M.REPORTID AS ReportId, RPT.REPORTCODE AS ReportCode, RPT.REPORTNAME AS ReportName, M.WEBFORMID AS WebFormId, WF.WEBFORMNAME AS WebFormName, WF.WEBFORMURL AS WebFormURL, M.MENUTYPE AS MenuMenuType, M.MENUNATURE AS MenuMenuNature, M.IMAGEID AS ImageId, IMG.IMAGENAME AS ImageName, IMG.IMAGELINKURL AS ImageLinkURL, M.PAGEID AS PageId, PG.PAGECODE AS PageCode, PG.PAGENAME AS PageName, M.MENUDISPLAYNAME AS MenuDisplayName, M.MENUDESCRIPTION AS MenuDescription, M.MENUTOOLTIP AS MenuToolTip, M.MENUCAPTION AS MenuCaption, M.SAVEWEBSERVICEID AS SaveWebServiceId, SWF.WEBSERVICENAME AS SaveWebServiceName, M.GETWEBSERVICEID AS GetWebServiceId, GWF.WEBSERVICENAME AS GetWebServiceName, M.DELETEWEBSERVICEID AS DeleteWebServiceId, DWF.WEBSERVICENAME AS DeleteWebServiceName, M.BIZTRANSACTIONCLASSID AS MenuBizTransactionClassId, BTC.BIZTRANSACTIONCLASSNAME AS MenuBizTransactionClassName, M.ENTITYID AS EntityId, ENT.ENTITYCODE AS EntityCode, ENT.ENTITYNAME AS EntityName, M.HELPURL AS HelpUrl, M.SORTORDER AS MenuSortOrder, M.STATUS AS MenuStatus, M.VERSION AS MenuVersion, M.SOURCETYPE AS MenuSourceType, M.CREATEDBYID AS MenuCreatedById, CBY.USERNAME AS MenuCreatedByName, M.CREATEDON AS MenuCreatedOn, M.MODIFIEDBYID AS MenuModifiedById, MBY.USERNAME AS MenuModifiedByName, M.MODIFIEDON AS MenuModifiedOn, M.DEFAULTREPORTFORMATID AS ReportFormatId, -- RF.REPORTFORMATFILE AS ReportFormatReportFile, -- RF.FORMATNAME AS ReportFormatName, M.URIPARAMETERVALUE AS UriParameterValue, M.LISTMENUID AS ListMenuId, LM.MENUCODE AS ListMenuCode, LM.MENUNAME AS ListMenuName, M.ANALYSISQUERYID AS MenuAnalysisQueryId, AQ.QUERYNAME AS MenuAnalysisQueryName, M.DEFAULTMENUID AS DefaultMenuId, DM.MENUCODE AS DefaultMenuCode, DM.MENUNAME AS DefaultMenuName, M.FROMVERSION AS FromVersion, M.TOVERSION AS ToVersion, M.SECTIONID AS GcmSectionId, -- GS.SECTIONCODE AS GcmSectionCode, -- GS.SECTIONNAME AS GcmSectionName, M.OBJECTMODELID AS ObjectModelId, OM.FILENAME AS ObjectModelName, OM.FILELOCATION AS ObjectModelLocation FROM DBO.MMENU M JOIN DBO.MMODULE MOD ON M.MODULEID = MOD.MODULEID JOIN DBO.MGCMTYPE GCT ON M.GCMTYPEID = GCT.GCMTYPEID JOIN DBO.MMENU PM ON M.PARENTID = PM.MENUID JOIN DBO.MREPORT RPT ON M.REPORTID = RPT.REPORTID JOIN DBO.MWEBFORM WF ON M.WEBFORMID = WF.WEBFORMID JOIN DBO.MIMAGE IMG ON M.IMAGEID = IMG.IMAGEID JOIN DBO.MPAGE PG ON M.PAGEID = PG.PAGEID JOIN DBO.MWEBSERVICE SWF ON M.SAVEWEBSERVICEID = SWF.WEBSERVICEID JOIN DBO.MWEBSERVICE GWF ON M.GETWEBSERVICEID = GWF.WEBSERVICEID JOIN DBO.MWEBSERVICE DWF ON M.DELETEWEBSERVICEID = DWF.WEBSERVICEID JOIN DBO.MBIZTRANSACTIONCLASS BTC ON M.BIZTRANSACTIONCLASSID = BTC.BIZTRANSACTIONCLASSID JOIN DBO.MENTITY ENT ON M.ENTITYID = ENT.ENTITYID JOIN DBO.MUSER CBY ON M.CREATEDBYID = CBY.USERID JOIN DBO.MUSER MBY ON M.MODIFIEDBYID = MBY.USERID JOIN DBO.MREPORTFORMAT RF ON M.DEFAULTREPORTFORMATID = RF.REPORTFORMATID JOIN DBO.MMENU LM ON M.LISTMENUID = LM.MENUID JOIN DBO.MANALYSISQUERY AQ ON M.ANALYSISQUERYID = AQ.ANALYSISQUERYID JOIN DBO.MMENU DM ON M.DEFAULTMENUID = DM.MENUID -- JOIN DBO.gc GS -- ON M.SECTIONID = GS.SECTIONID JOIN DBO.MFILE OM ON M.OBJECTMODELID = OM.FILEID WHERE M.MENUID = @menuid; "; public const string GET_MENU_FOR_REPORT_CONSIOLIDATED = @"SELECT menu.MENUID AS MenuId, menu.MENUCODE AS MenuCode, menu.MENUNAME AS MenuName, CASE menu.MenuType WHEN 0 THEN 'Master' WHEN 1 THEN 'Transaction' WHEN 2 THEN 'Reports' WHEN 3 THEN 'Others' END AS MenuType, menu.ReportId AS MenuReportId, menu.MENUNATURE AS MenuNature, report.REPORTNAME AS ReportName, report.REPORTURI AS ReportUri, menu.WebFormId AS WebFormId, webform.WEBFORMNAME AS WebFormName, webform.WEBFORMURL AS WebFormURL, webform.WEBFORMSECONDURL AS WebFormSecondURL, webform.Template AS WebFormTemplate, report.WebServiceId AS WebServiceId, webservice.WEBSERVICENAME AS WebServiceName, webservice.PERIODFILTERTYPE AS PeriodType, REPLACE( REPLACE(webservice.UriTemplate, '{BaseURI}', ':baseuri'), '{PartyBranchId}', ':partybranchid' ) AS WebServiceUriTemplate, REPLACE( REPLACE(webservice.SecondURITemplate, '{BaseURI}', ':baseuri'), '{PartyBranchId}', ':partybranchid' ) AS SecondURITemplate, webservice.MethodType AS WebServiceMethodType, CASE webservice.MethodType WHEN 0 THEN 'GET' WHEN 1 THEN 'POST' WHEN 2 THEN 'DELETE' END AS MethodType, menu.BizTransactionClassId AS BizTransactionClassId, CASE WHEN msd.DEFAULTREPORTFORMATID <> -1 THEN msd.DEFAULTREPORTFORMATID ELSE menu.DEFAULTREPORTFORMATID END AS DefaultReportFormatId, reportformat.ReportFile AS ReportFile, reportformat.REPORTFORMATNAME AS ReportFormatName, reportformat.DataSourceFileName AS PortletDataSourceFileName, reportformat.RepPath AS DefaultReportFormatRepPath, menu.HelpUrl AS HelpUrl, menu.GcmTypeId AS GcmTypeId, menu.URIParameterValue AS URIParameterValue, webservice.ResponseSchemaId AS EntityId, entity.EntityCode AS EntityCode, entity.ENTITYNAME AS EntityName, CASE WHEN msd.DEFAULTREPORTVIEWID <> -1 THEN msd.DEFAULTREPORTVIEWID ELSE menu.DEFAULTREPORTVIEWID END AS DefaultReportViewId, menu.AnalysisQueryId AS AnalysisQueryId, CASE WHEN msd.MENUDISPLAYNAME IS NULL THEN menu.MenuDisplayName ELSE msd.MENUDISPLAYNAME END AS MenuDisplayName, CASE WHEN objectmodel.FILENAME IS NULL THEN OBJECTMODELMENU.FILENAME ELSE objectmodel.FILENAME END AS MenuObjectModelName, -- 2nd Report Criteria menu.ReportId AS ReportCriteriaReportId, webservicecriteria.SlNo AS WebServiceCriteriaSlNo, webservicecriteria.DisplaySlNo AS WebServiceCriteriaDisplaySlNo, webservicecriteria.WEBSERVICECRITERIAID AS WebServiceCriteriaId, webservicecriteria.DisplayName AS WebServiceCriteriaDisplayName, webservicecriteria.FieldName AS WebServiceCriteriaFieldName, webservicecriteria.PicklistFieldName AS WebServiceCriteriaPicklistFieldName, webservicecriteria.DependPicklistName AS WebServiceCriteriaDependPicklistName, webservicecriteria.DependFieldName AS WebServiceCriteriaDependFieldName, webservicecriteria.CriteriaAttributeId AS WebServiceCriteriaAttributeId, criteriaattribute.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, webservicecriteria.IsCompulsory AS IsCompulsory, webservicecriteria.IsHeader AS IsHeader, criteriaattribute.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CASE criteriaattribute.CRITERIAATTRIBUTETYPE WHEN 0 THEN 'PICKLIST' WHEN 1 THEN 'CHECK' WHEN 2 THEN 'RADIO' WHEN 3 THEN 'LIST' WHEN 4 THEN 'DATE' WHEN 5 THEN 'NUMERICENTRY' WHEN 6 THEN 'TEXTENTRY' END AS CriteriaAttributeTypeName, section.gcmname AS CriteriaAttributeSection, criteriaattribute.PicklistId AS CriteriaAttributePicklistId, picklist.PICKLISTCODE AS CriteriaAttributePicklistCode, picklist.DisplayFieldName AS CriteriaAttributePicklistDisplayFieldName, picklist.SelectionFieldName AS CriteriaAttributePicklistSelectionFieldName, picklist.displayfieldtextbox AS CriteriaAttributePicklistDisplayTextBox, picklist.PICKLISTTITLE AS CriteriaAttributePicklistTitle, REPLACE( REPLACE( REPLACE( REPLACE(picklist.PICKLISTURI, '{BaseURI}', ':baseuri'), '{PartyBranchId}', '0' ), '{FirstNumber}', '1' ), '{MaxResult}', '100' ) AS CriteriaAttributePicklistURI, REPLACE( REPLACE( REPLACE( REPLACE(picklist.PICKLISTSECONDURI, '{BaseURI}', ':baseuri'), '{PartyBranchId}', '0' ), '{FirstNumber}', '1' ), '{MaxResult}', '100' ) AS CriteriaAttributePicklistSecondURI, case when picklist.PICKLISTSECONDURI is not null then 'GB5' when picklist.PICKLISTSECONDURI ='' then 'GB5' when picklist.PICKLISTSECONDURI ='NONE' then 'GB5' else 'GB4' end as PicklistURLFrom, picklist.SearchFields AS CriteriaAttributeSearchFields, criteriaattribute.ApplicableValues AS CriteriaAttributeApplicableValues, criteriaattribute.VariableField AS CriteriaAttributeVariableField, criteriaattribute.ApplicableOperation AS CriteriaAttributeApplicableOperation, criteriaattribute.DefaultOperation AS CriteriaAttributeDefaultOperation, criteriaattribute.GcmTypeId AS CriteriaAttributeGcmTypeId, criteriaattribute.ContextValueFieldName AS CriteriaAttributeContextValueFieldName, picklist.ISMULTISELECT AS CriteriaAttributePicklistIsMultiSelect, -- 4th report vs formats menu.ReportId AS ReportvsFormatReportId, reportvsformats.ReportVsFormatsId AS ReportVsFormatsId, reportvsformats.SlNo AS ReportVsFormatsSlNo, reportvsformats.ReportFormatId AS ReportFormatId, reportformat.DataSourceFileName AS ReportFormatDataSourceFileName, reportformat.Purpose AS ReportFormatPurpose, reportformat.RepPath AS ReportFormatRepPath, reportformat.Remarks AS ReportFormatRemarks, -- Report Views reportview.reportviewId AS ReportViewId, reportview.reportviewName AS ReportViewName, reportview.Remarks AS ReportViewRemarks, menu.ReportId AS ReportViewReportId, reportview.ReportId AS ReportId, reportview.IsSystemView AS IsSystemView, reportview.IsDefaultView AS IsDefaultView, reportview.ReportViewType AS ReportViewType, reportview.TemplateId AS TemplateId, reportview.SourceType AS SourceType, mfile.FileName AS TemplateName, mfile.FileLocation AS TemplateLocation, reportview.SecondTemplateId AS SecondTemplateId, secfile.FileName AS SecondTemplateName, secfile.FileLocation AS SecondTemplateLocation, reportview.ColumnFreeze AS ColumnFreeze, reportview.PivotFormat AS PivotFormat, reportview.PageId AS PageId, reportview.IsDataPivot AS IsDataPivot, mpage.PageCode AS PageCode, mpage.PageName AS PageName, mpage.PageLayout AS PageLayout, ISNULL(usermnu.USERVSMENUID, -1) AS UserVsMenuId FROM MMenu AS menu LEFT JOIN MUSERVSMENU AS usermnu ON menu.MENUID = usermnu.MENUID AND usermnu.USERID = @userid LEFT JOIN MMENUSETDETAIL AS msd ON menu.MENUID = msd.MENUID LEFT JOIN mfile AS objectmodel ON msd.objectmodelID = objectmodel.FILEID INNER JOIN MREPORT AS report ON menu.ReportId = report.REPORTID LEFT JOIN MReportView AS reportview ON reportview.ApplicableMenuIds LIKE '%' + CAST(@menuid AS NVARCHAR) + '%' AND report.REPORTID = reportview.REPORTID --and menu.DEFAULTREPORTVIEWID=reportview.reportviewId AND reportview.isvisible = 0 LEFT JOIN mFile AS mfile ON reportview.TemplateId = mfile.fileId LEFT JOIN mFile AS secfile ON reportview.SecondTemplateId = secfile.fileId LEFT JOIN MPAGE AS mpage ON mpage.PAGEID = reportview.PageId INNER JOIN MWebForm AS webform ON menu.WebFormId = webform.WEBFORMID INNER JOIN MWebService AS webservice ON report.WebServiceId = webservice.WEBSERVICEID LEFT JOIN MWEBSERVICECRITERIA AS webservicecriteria ON webservice.WEBSERVICEID = webservicecriteria.WEBSERVICEID LEFT JOIN Mcriteriaattribute AS criteriaattribute ON webservicecriteria.CriteriaAttributeId = criteriaattribute.CRITERIAATTRIBUTEID left join mgcm section on (section.gcmid=criteriaattribute.sectionid) LEFT JOIN MPicklist AS picklist ON criteriaattribute.PicklistId = picklist.PICKLISTID JOIN MRoleVsMenu AS rolevsmenu ON rolevsmenu.MENUID = menu.menuId JOIN MUser AS muser ON muser.userId = @userid JOIN MRole AS mrole ON muser.RoleId = mrole.ROLEID AND rolevsmenu.RoleId = mrole.ROLEID JOIN MEntity AS entity ON webservice.ResponseSchemaId = entity.ENTITYID JOIN mfile AS OBJECTMODELMENU ON menu.OBJECTMODELID = OBJECTMODELMENU.FILEID JOIN MReportFormat AS reportformat ON reportformat.REPORTFORMATID = CASE WHEN ISNULL(msd.DEFAULTREPORTFORMATID, -1) = -1 THEN menu.DEFAULTREPORTFORMATID ELSE msd.DEFAULTREPORTFORMATID END LEFT JOIN MReportVsFormats AS reportvsformats ON reportvsformats.ApplicableMenuIds LIKE '%' + CAST(@menuid AS NVARCHAR) + '%' AND reportvsformats.ReportFormatId = reportformat.ReportFormatId WHERE menu.MENUID = @menuid AND muser.userId = @userid AND rolevsmenu.Allow LIKE '%0%' AND menu.Status IN (0, 1) ORDER BY menu.MENUID, menu.ReportId, report.WebServiceId, webservicecriteria.DisplaySlNo; "; public const string GET_MENU_FOR_REPORT_CONSIOLIDATED_DRILLDOWN_ARRAY = @" select a.DRILLDOWNID as DrillDownId, a.DRILLDOWNCODE as DrillDownCode, a.DRILLDOWNDESCRIPTION as DrillDownDescription, a.FROMMENUID as MenuId, a.TOMENUID as ToMenuId, a.FROMREPORTID as MenuReportId, a.TOREPORTID as ToReportId, a.DRILLTYPE as DrillDownType, a.FROMTYPE as FromType, a.TOTYPE as ToType, a.FROMPORTLETID as FromPortletId, a.TOPORTLETID as ToPortletId, a.TOWEBFORMID as ToWebFormId, a.BIZTRANSACTIONTYPEFIELD as BIZTransactionTypeField, a.MODULEID as ModuleId, --b.REPORTCODE as FromReportCode, b.REPORTNAME as FromReportName, --c.REPORTCODE as ToReportCode, c.REPORTNAME as ToReportName, d.MENUCODE as MenuCode, d.MENUNAME as MenuName, d.MENUDISPLAYNAME as FromMenuDisplayName, d.MENUTYPE as FromMenuType, e.MENUCODE as ToMenuCode, e.MENUNAME as ToMenuName, e.MENUDISPLAYNAME as ToMenuDisplayName, e.MENUTYPE as ToMenuType, f.WEBFORMNAME as ToWebFormName, f.WEBFORMURL as FromMenuWebFormURL, md.ASSIGNTYPE as AssignType, md.MAPTYPE as MapType, md.SLNO as SlNo, md.ATTRIBUTEID as AttributeId, mc.CRITERIAATTRIBUTENAME as AttributeName, md.SOURCEFIELD as SourceField, md.FILTERVALUE as FilterValue, md.OPERATIONTYPE as OperationType, md.JOINTYPE as JoinType from MDRILLDOWN a,MREPORT b,MREPORT c,MMENU d,MMENU e,MWEBFORM f ,MROLEVSMENU g,MUSER u, MDRILLDOWNDETAIL md,MCRITERIAATTRIBUTE mc where 1=1 and a.DRILLDOWNID =md.DRILLDOWNID and a.FROMMENUID =d.MENUID and a.TOMENUID =e.MENUID and a.TOWEBFORMID =f.WEBFORMID and a.FROMREPORTID =b.REPORTID and a.TOREPORTID =c.REPORTID and a.FROMMENUID =g.MENUID and md.ATTRIBUTEID=mc.criteriaattributeid and g.Allow like '%0%' and u.ROLEID =g.ROLEID and a.FROMMENUID=@menuid and u.USERID =@userid"; public const string GET_REPORTVIEWFIELDS_FOR_MENU_AND_USER_SQL = @" SELECT menu.ReportId AS MenuReportId, reportviewfields.ReportVsFieldsId AS ReportViewFieldsId, reportviewfields.ReportViewId AS ReportViewId, reportview.REPORTVIEWNAME AS ReportViewName, reportviewfields.ReportVsFieldsId AS ReportVsFieldsId, reportviewfields.SlNo AS ReportVsFieldsSlNo, reportvsfields.FieldName AS ReportVsFieldsFieldName, reportviewfields.FieldTitle AS ReportVsFieldsFieldTitle, reportvsfields.FieldType AS ReportVsFieldsFieldType, reportviewfields.FieldWidth AS ReportVsFieldsFieldWidth, reportviewfields.IsDisplay AS ReportVsFieldsIsDisplay, reportviewfields.IsMergeColumn AS ReportVsFieldsIsMergeColumn, reportviewfields.IsGroupcolumn AS ReportVsFieldsIsGroupColumn, reportviewfields.IsSubTotal AS ReportVsFieldsIsSubtotalColumn, reportviewfields.IsGrandTotal AS ReportVsFieldsIsGrandtotalColumn, reportviewfields.CalFormat AS ReportVsFieldsCalFormat, reportviewfields.DisplaySlNo AS ReportVsFieldsDisplaySlNo, ISNULL(criteriaatt.CRITERIAATTRIBUTEID, -1) AS CriteriaAttributeId, reportvsfields.alignment as ReportvsFieldAlignment, reportviewfields.alignment as ReportViewFieldsAlignment FROM MReport report, MMenu menu, MReportViewFields reportviewfields, MReportVsFields reportvsfields LEFT OUTER JOIN MCRITERIAATTRIBUTE criteriaatt ON criteriaatt.CRITERIAATTRIBUTENAME = reportvsfields.FIELDNAME AND criteriaatt.FILTERTYPE = 0 -- 0 For Addon Type , MReportView reportview, MRoleVsMenu rolevsmenu, MUser user1, MRole role WHERE 1 = 1 AND menu.MENUId = @menuid AND user1.UserId = @userid -- Adjusting LIKE for SQL Server syntax AND reportview.ApplicableMenuIds LIKE '%' + CAST(@menuid AS VARCHAR) + '%' AND user1.RoleId = role.ROLEID AND rolevsmenu.ROLEID = role.ROLEID AND rolevsmenu.MENUID = menu.MENUID AND rolevsmenu.Allow LIKE '%0%' AND menu.ReportId = report.REPORTID AND report.REPORTID = reportvsfields.ReportId AND reportvsfields.ReportVsFieldsId = reportviewfields.ReportVsFieldsId AND reportvsfields.ReportId = reportviewfields.ReportId AND reportviewfields.ReportViewId = reportview.REPORTVIEWID ORDER BY menu.ReportId, reportviewfields.DisplaySlNo;"; public const string GET_CRITERIA_CONFIG_FOR_MENU_AND_USER = @"SELECT criteriaco0_.CRITERIACONFIGID AS CriteriaConfigId, criteriaco0_.CRITERIACONFIGNAME AS CriteriaConfigName, criteriaco0_.VERSION AS CriteriaConfigVersion, criteriaco0_.CRITERIAMENUID AS MenuId, menu6_.MENUCODE AS MenuCode, menu6_.MENUNAME AS MenuName, criteriaco0_.PERIODFILTER AS PeriodFilter, criteriaco0_.CRITERIAOUID AS CriteriaOUId, criteriaco1_.CRITERIACONFIGSECTIONID AS CriteriaConfigSectionId, criteriaco1_.SLNO AS CriteriaConfigSectionSlNo, criteriaco1_.SECTIONJOIN AS CriteriaConfigSectionJoin, criteriaco2_.CRITERIACONFIGATTRIBUTEID AS CriteriaConfigAttributeId, criteriaco2_.SLNO AS CriteriaConfigAttributeSlNo, criteriaco2_.CRITERIAATTRIBUTEID AS CriteriaAttributeId, criteriaat3_.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, CASE WHEN webservice13_.ISCOMPULSORY IS NULL THEN ISNULL(webservice5_.ISCOMPULSORY, 0) ELSE webservice13_.ISCOMPULSORY END AS IsCompulsory, CASE WHEN webservice13_.ISHEADER IS NULL THEN ISNULL(webservice5_.ISHEADER, 0) ELSE webservice13_.ISHEADER END AS IsHeader, criteriaat3_.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, criteriaco2_.OPERATIONTYPE AS CriteriaConfigAttributeOperationType, criteriaco2_.CRITERIAFIELDVALUE AS CriteriaConfigAttributeFieldValue, criteriaco2_.CRITERIAFIELDVALUEIN AS CriteriaConfigAttributeFieldValueIn, criteriaco2_.CRITERIAJOIN AS CriteriaConfigAttributeJoin, criteriaco2_.CRITERIAFIELDDISPLAYVALUE AS CriteriaConfigAttributeFieldDisplayValue, criteriaco2_.CRITERIAVARIABLEFIELD AS CriteriaConfigAttributeVariableField, ISNULL(criteriaat3_.FILTERTYPE, 1) AS CriteriaConfigAttributeFilterType, criteriaat3_.ApplicableOperation AS ApplicableOperation, criteriaat3_.DefaultOperation AS DefaultOperation, criteriaco0_.RECORDSPERPAGE AS CriteriaConfigRecordsPerPage, criteriaco0_.ReportFormatId AS CriteriaConfigReportFormatId, criteriaco0_.DefaultReportViewId AS CriteriaConfigDefaultReportViewId, CASE WHEN webservice13_.ISCHANGEABLE IS NULL THEN 0 ELSE webservice13_.ISCHANGEABLE END AS IsChangeable FROM TCRITERIACONFIG criteriaco0_ INNER JOIN MMENU menu6_ ON menu6_.MENUID = criteriaco0_.CRITERIAMENUID AND menu6_.MENUID = @menuid INNER JOIN MROLEVSMENU rolevsmenu9_ ON rolevsmenu9_.MENUID = menu6_.MENUID AND rolevsmenu9_.ROLEID = @roleid AND rolevsmenu9_.ALLOW LIKE '%0%' INNER JOIN TCRITERIACONFIGSECTION criteriaco1_ ON criteriaco1_.CRITERIACONFIGID = criteriaco0_.CRITERIACONFIGID INNER JOIN TCRITERIACONFIGATTRIBUTE criteriaco2_ ON criteriaco2_.CRITERIASECTIONID = criteriaco1_.CRITERIACONFIGSECTIONID INNER JOIN MCRITERIAATTRIBUTE criteriaat3_ ON criteriaat3_.CRITERIAATTRIBUTEID = criteriaco2_.CRITERIAATTRIBUTEID INNER JOIN MWEBSERVICE webservice4_ ON webservice4_.WEBSERVICEID = criteriaco0_.CRITERIASERVICEID LEFT OUTER JOIN MWEBSERVICECRITERIA webservice5_ ON webservice5_.WEBSERVICEID = webservice4_.WEBSERVICEID AND webservice5_.CRITERIAATTRIBUTEID = criteriaat3_.CRITERIAATTRIBUTEID LEFT OUTER JOIN MWEBSERVICESETTING webservice11_ ON webservice11_.MENUID = @menuid AND webservice11_.WEBSERVICEID = webservice4_.WEBSERVICEID LEFT OUTER JOIN MWEBSERVICESETTINGDETAIL webservice13_ ON webservice13_.WEBSERVICECRITERIAID = webservice5_.WEBSERVICECRITERIAID AND webservice13_.WEBSERVICESETTINGID = webservice11_.WEBSERVICESETTINGID WHERE criteriaco0_.CRITERIAUSERID = @userid AND criteriaco0_.CRITERIAOUID = @workouid AND criteriaco0_.CRITERIACONFIGTYPE = 2 AND (webservice13_.ISVISIBLE IS NULL OR webservice13_.ISVISIBLE = 0 OR webservice13_.CRITERIAFIELDVALUE <> 'NONE');"; public const string GET_WEB_SERVICE_SETTING = @"SELECT wss.WEBSERVICESETTINGID AS WebServiceSettingId, wss.WebServiceId AS WebServiceId, ws.WEBSERVICENAME AS WebServiceName, wss.SettingType AS WebServiceSettingSettingType, wss.ReportId AS ReportId, r.REPORTNAME AS ReportName, wss.MenuId AS MenuId, m.MENUCODE AS MenuCode, m.MENUNAME AS MenuName, wssd.WEBSERVICESETTINGDETAILID AS WebServiceSettingDetailId, wssd.SLNO AS WebServiceSettingDetailSlno, ca.CRITERIAATTRIBUTEID AS CriteriaAttributeId, ca.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, ca.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, ca.ContextValueFieldName AS CriteriaAttributeContextValueFieldName, wssd.IsCompulsory AS WebServiceSettingDetailIsCompulsory, wssd.IsHeader AS WebServiceSettingDetailIsHeader, wssd.IsVisible AS WebServiceSettingDetailIsVisible, wssd.IsChangeable AS WebServiceSettingDetailIsChangeable, wssd.OperationType AS WebServiceSettingDetailOperationType, CASE WHEN (wssd.CriteriaFieldValue = 'null' OR wssd.CriteriaFieldValue IS NULL) THEN wssd.CriteriaFieldValue ELSE REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(wssd.CriteriaFieldValue, '{WorkPartyBranchID}', '@workpartybranchid'), '{WorkOUID}', '@workouid'), '{WorkStoreId}', '@workstoreid'), '{WorkPeriodId}', '@workperiodid'), '{TodayFrom}', '@todayfrom'), '{TodayTo}', '@todayto'), '{TodayTimeStamp}', '@todaytimestamp'), '{YesterdayFrom}', '@yesterdayfrom'), '{YesterdayTo}', '@yesterdayto'), '{YesterdayTimeStamp}', '@yesterdaytimestamp'), '{WeekFrom}', '@weekfrom'), '{MonthFrom}', '@monthfrom'), '{YearFrom}', '@yearfrom'), '{CurrentMonthFrom}', '@currentmonthfrom'), '{CurrentYearFrom}', '@currentyearfrom'), '{CurrentYearTo}', '@currentyearto') END AS WebServiceSettingDetailCriteriaFieldValue, wssd.CriteriaJoin AS WebServiceSettingDetailCriteriaJoin, wssd.DisplayName AS WebServiceSettingDetailDisplayName, wsc.WEBSERVICECRITERIAID AS WebServiceCriteriaId, wsc.FieldName AS WebServiceCriteriaFieldName, '' as WebServiceSettingDetailCriteriaFieldValueName, 'NONE' as WebServiceSettingDetailCriteriaOriginalFieldValue FROM MWEBSERVICESETTING wss JOIN mWebService ws ON wss.WebServiceId = ws.WEBSERVICEID JOIN mMenu m ON wss.MenuId = m.MENUID JOIN mReport r ON wss.ReportId = r.REPORTID JOIN mWebServiceSettingDetail wssd ON wssd.WEBSERVICESETTINGID = wss.WEBSERVICESETTINGID JOIN mWebServiceCriteria wsc ON wssd.WebServiceCriteriaId = wsc.WEBSERVICECRITERIAID JOIN mCriteriaAttribute ca ON wsc.CriteriaAttributeId = ca.CRITERIAATTRIBUTEID WHERE wss.WebServiceId = @webserviceid AND wss.MenuId = @menuid AND wss.ReportId = @reportid"; public const string GET_SELECTLIST_MENU = @" SELECT DISTINCT menu.MENUID AS Id, menu.MENUCODE AS Code, menu.MENUNAME AS Name, menu.SOURCETYPE AS SourceType, CAST(menu.MENUID AS VARCHAR(20)) + menu.PARENTIDS AS ParentIds, CASE WHEN menu.MENUNATURE = 2 THEN 'Parent' ELSE 'Child' END AS Nature FROM MMENU menu JOIN MMENUSETDETAIL menusetdetail ON menusetdetail.MENUID = menu.MENUID JOIN MMENUSET menuset ON menuset.MENUSETID = menusetdetail.MENUSETID WHERE menuset.CLIENTID = @ClientId AND (@ModuleId IS NULL OR menu.MODULEID = @ModuleId) AND (@Code IS NULL OR menu.MENUCODE LIKE '%' + @Code + '%') AND (@Name IS NULL OR menu.MENUNAME LIKE '%' + @Name + '%') "; public const string GET_MENU_LIST_BASED_MENUSET_TREE = @" SELECT DISTINCT menu.MENUID AS MenuId, menu.MENUCODE AS MenuCode, menu.MENUNAME AS MenuName, menu.MENUNATURE AS MenuMenuNature, menu.MENUTYPE AS MenuMenuType, modulevsmenu.MODULEID AS ModuleId, module.MODULECODE AS ModuleCode, module.MODULENAME AS ModuleName, CASE WHEN ISNULL(menusetdetail.MENUDISPLAYNAME,'') = '' THEN menu.MENUDISPLAYNAME ELSE menusetdetail.MENUDISPLAYNAME END AS MenuDisplayName, menu.SECTION AS MenuSection, menu.SORTORDER AS MenuSortOrder, menu.REPORTID AS ReportId, report.REPORTNAME AS ReportName, menu.BIZTRANSACTIONCLASSID AS MenuBizTransactionClassId, bizclass.BIZTRANSACTIONCLASSNAME AS MenuBizTransactionClassName, modulevsmenu.LEVELS AS MenuLevel, menu.WEBFORMID AS WebFormId, webform.WEBFORMNAME AS WebFormName, webform.WEBFORMURL AS WebFormURL, webform.WEBFORMSECONDURL AS WebFormSecondURL, menu.MENUDESCRIPTION AS MenuDescription, menu.GCMTYPEID AS GcmTypeId, gcmtype.GCMTYPECODE AS GcmTypeCode, gcmtype.GCMTYPEDESCRIPTION AS GcmTypeName, modulevsmenu.PARENTID AS ParentId, parentmenu.MENUCODE AS ParentCode, parentmenu.MENUNAME AS ParentName, ISNULL(menusetdetail.SORTORDER, menu.SORTORDER) AS SortOrder, -1 AS UserVsMenuId, -1 AS DefaultMenuId, 'NONE' AS DefaultMenuCode, 'NONE' AS DefaultMenuName, 0 AS DefaultMenuMenuNature, 0 AS DefaultMenuMenuType, 'NONE' AS DefaultMenuSection, -1 AS DefaultMenuReportId, -1 AS DefaultMenuEntityId, -1 AS DefaultMenuBizTransactionClassId, 'NONE' AS DefaultMenuBizTransactionClassName, -1 AS DefaultMenuGCMTypeId, -1 AS DefaultMenuWebFormId, 'NONE' AS DefaultMenuWebFormName, 'NONE' AS DefaultMenuWebFormUrl, 'NONE' AS DefaultMenuHelpUrl, -1 AS DefaultMenuImageId, menu.HELPURL AS HelpUrl, -1 AS ImageId, 'NONE' AS ImageName, 'NONE' AS ImageLinkURL, -1 AS InchargeId, 'NONE' AS InchargeCode, 'NONE' AS InchargeName, -1 AS AnalysisWebServiceId, menu.LISTMENUID AS ListMenuId, menu.ENTITYID AS EntityId, 'NONE' AS EntityCode, 'NONE' AS EntityName, menu.PAGEID AS PageId, 'NONE' AS PageCode, 'NONE' AS PageName, 'NONE' AS MenuObjectModelName, 'NONE' AS MenuObjectModelFileLocation, '-1' AS MenuWebFormObjectModelIds, 'NONE' AS MenuWebFormObjectModelNames, '-1' AS DefaultWebFormObjectModelIds, 'NONE' AS DefaultWebFormObjectModelNames, '' AS MenuIconName FROM MClientModule clientmodule JOIN MMenuSet menuset ON clientmodule.MenuSetId = menuset.MenuSetId AND clientmodule.ModuleId = menuset.ModuleId JOIN MMenuSetDetail menusetdetail ON menuset.MenuSetId = menusetdetail.MenuSetId AND menusetdetail.ISVISIBLE IN (0, 2) JOIN MMENU menu ON menusetdetail.MENUID = menu.MENUID JOIN MMODULEVSMENU modulevsmenu ON modulevsmenu.MENUID = menu.MENUID AND modulevsmenu.MODULEID = clientmodule.ModuleId JOIN MMODULE module ON module.MODULEID = modulevsmenu.MODULEID JOIN MREPORT report ON report.REPORTID = menu.REPORTID JOIN MBIZTRANSACTIONCLASS bizclass ON bizclass.BIZTRANSACTIONCLASSID = menu.BIZTRANSACTIONCLASSID JOIN MWEBFORM webform ON webform.WEBFORMID = menu.WEBFORMID JOIN MGCMTYPE gcmtype ON gcmtype.GCMTYPEID = menu.GCMTYPEID JOIN MMENU parentmenu ON parentmenu.MENUID = modulevsmenu.PARENTID WHERE clientmodule.ClientId = @clientid AND clientmodule.ModuleId = @moduleid AND (@SearchKeyword IS NULL OR menu.MENUNAME LIKE '%' + @SearchKeyword + '%' OR menu.MENUCODE LIKE '%' + @SearchKeyword + '%') ORDER BY modulevsmenu.MODULEID, menu.SORTORDER"; // GET_MENU_ADMIN — column list/aliases match MenuAdminDTO exactly (same shape Save/Update // bind to), deliberately not reusing GET_MENU/MenuDTO's joined report-style aliases // (e.g. MenuSection, MenuIsParentMenu, MenuParentIds, MenuLevel) which would silently // fail to patch several MenuAdminDTO-named form fields when editing an existing menu. public const string GET_MENU_ADMIN = @" SELECT MENUID AS MenuId, MENUCODE AS MenuCode, MENUNAME AS MenuName, MODULEID AS ModuleId, GCMTYPEID AS GcmTypeId, SECTION AS Section, ISPARENTMENU AS IsParentMenu, PARENTID AS ParentId, PARENTIDS AS ParentIds, LEVELS AS Levels, REMARKS AS Remarks, REPORTID AS ReportId, WEBFORMID AS WebFormId, MENUTYPE AS MenuType, MENUNATURE AS MenuNature, IMAGEID AS ImageId, PAGEID AS PageId, TARGETTYPE AS TargetType, TARGETID AS TargetId, MENUDISPLAYNAME AS MenuDisplayName, MENUDESCRIPTION AS MenuDescription, MENUTOOLTIP AS MenuToolTip, MENUCAPTION AS MenuCaption, SAVEWEBSERVICEID AS SaveWebServiceId, GETWEBSERVICEID AS GetWebServiceId, DELETEWEBSERVICEID AS DeleteWebServiceId, BIZTRANSACTIONCLASSID AS BizTransactionClassId, ENTITYID AS EntityId, HELPURL AS HelpUrl, SORTORDER AS SortOrder, STATUS AS Status, VERSION AS Version, SOURCETYPE AS SourceType, CREATEDBYID AS CreatedById, CREATEDON AS CreatedOn, MODIFIEDBYID AS ModifiedById, MODIFIEDON AS ModifiedOn, DEFAULTREPORTFORMATID AS DefaultReportFormatId, URIPARAMETERVALUE AS UriParameterValue, DEFAULTREPORTVIEWID AS DefaultReportViewId, LISTMENUID AS ListMenuId, ANALYSISQUERYID AS AnalysisQueryId, DEFAULTMENUID AS DefaultMenuId, FROMVERSION AS FromVersion, TOVERSION AS ToVersion, SECTIONID AS SectionId, OBJECTMODELID AS ObjectModelId, ICONNAME AS IconName FROM MMENU WHERE MENUID = @MenuId"; // ── MMENU CRUD — MMENU never had a Save/Update/Delete path before this follow-up // (Get/List only). Column list matches the confirmed-live INSERT in // DB/Migrations/20260722_RAM_ScanPipeline_MenuSeed_SqlServer.sql exactly. Binds to // MenuAdminDTO, not the report-shaped MenuDTO used by GET_MENU/GET_MENU_TREE_*. public const string INSERT_MENU = @" INSERT INTO MMENU (MENUID, MENUCODE, MENUNAME, MODULEID, GCMTYPEID, SECTION, ISPARENTMENU, PARENTID, PARENTIDS, LEVELS, REMARKS, REPORTID, WEBFORMID, MENUTYPE, MENUNATURE, IMAGEID, PAGEID, TARGETTYPE, TARGETID, MENUDISPLAYNAME, MENUDESCRIPTION, MENUTOOLTIP, MENUCAPTION, SAVEWEBSERVICEID, GETWEBSERVICEID, DELETEWEBSERVICEID, BIZTRANSACTIONCLASSID, ENTITYID, HELPURL, SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, DEFAULTREPORTFORMATID, URIPARAMETERVALUE, DEFAULTREPORTVIEWID, LISTMENUID, ANALYSISQUERYID, DEFAULTMENUID, FROMVERSION, TOVERSION, SECTIONID, OBJECTMODELID, ICONNAME) VALUES (@MenuId, @MenuCode, @MenuName, @ModuleId, @GcmTypeId, @Section, @IsParentMenu, @ParentId, @ParentIds, @Levels, @Remarks, @ReportId, @WebFormId, @MenuType, @MenuNature, @ImageId, @PageId, @TargetType, @TargetId, @MenuDisplayName, @MenuDescription, @MenuToolTip, @MenuCaption, @SaveWebServiceId, @GetWebServiceId, @DeleteWebServiceId, @BizTransactionClassId, @EntityId, @HelpUrl, @SortOrder, @Status, @Version, @SourceType, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @DefaultReportFormatId, @UriParameterValue, @DefaultReportViewId, @ListMenuId, @AnalysisQueryId, @DefaultMenuId, @FromVersion, @ToVersion, @SectionId, @ObjectModelId, @IconName)"; public const string UPDATE_MENU = @" UPDATE MMENU SET MENUCODE = @MenuCode, MENUNAME = @MenuName, MODULEID = @ModuleId, GCMTYPEID = @GcmTypeId, SECTION = @Section, ISPARENTMENU = @IsParentMenu, PARENTID = @ParentId, PARENTIDS = @ParentIds, LEVELS = @Levels, REMARKS = @Remarks, REPORTID = @ReportId, WEBFORMID = @WebFormId, MENUTYPE = @MenuType, MENUNATURE = @MenuNature, IMAGEID = @ImageId, PAGEID = @PageId, TARGETTYPE = @TargetType, TARGETID = @TargetId, MENUDISPLAYNAME = @MenuDisplayName, MENUDESCRIPTION = @MenuDescription, MENUTOOLTIP = @MenuToolTip, MENUCAPTION = @MenuCaption, SAVEWEBSERVICEID = @SaveWebServiceId, GETWEBSERVICEID = @GetWebServiceId, DELETEWEBSERVICEID = @DeleteWebServiceId, BIZTRANSACTIONCLASSID = @BizTransactionClassId, ENTITYID = @EntityId, HELPURL = @HelpUrl, SORTORDER = @SortOrder, STATUS = @Status, VERSION = VERSION + 1, SOURCETYPE = @SourceType, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, DEFAULTREPORTFORMATID = @DefaultReportFormatId, URIPARAMETERVALUE = @UriParameterValue, DEFAULTREPORTVIEWID = @DefaultReportViewId, LISTMENUID = @ListMenuId, ANALYSISQUERYID = @AnalysisQueryId, DEFAULTMENUID = @DefaultMenuId, FROMVERSION = @FromVersion, TOVERSION = @ToVersion, SECTIONID = @SectionId, OBJECTMODELID = @ObjectModelId, ICONNAME = @IconName WHERE MENUID = @MenuId"; public const string DELETE_MENU = "DELETE FROM MMENU WHERE MENUID = @MenuId"; } }