using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FrameworkDAL.Query.PortLet { public static class PortletQB { public const string GET_PORTLET = @"SELECT P.PORTLETID AS PortletId, P.PORTLETCODE AS PortletCode, P.PORTLETNAME AS PortletName, P.PORTLETTYPEID AS PortletTypeId, PT.PORTLETTYPECODE AS PortletTypeCode, PT.PORTLETTYPENAME AS PortletTypeName, P.MODULEID AS ModuleId, M.MODULECODE AS ModuleCode, M.MODULENAME AS ModuleName, P.SECTION AS PortletSection, P.URL AS PortletUrl, P.ISCHANGEABLE AS PortletIsChangeable, P.REPORTID AS ReportId, R.REPORTNAME AS ReportName, P.PROGRAMID AS ProgramId, MP.PROGRAMNAME AS ProgramName, P.MENUID AS MenuId, MM.MENUCODE AS MenuCode, MM.MENUNAME AS MenuName, P.ISMAXIMIZE AS PortletIsMaximize, P.ISRESIZE AS PortletIsResize, P.ISPRINT AS PortletIsPrint, P.ISEXPORT AS PortletIsExport, P.ISSETTINGSID AS PortletIsSettings, P.ISCHANGESETTING AS PortletIsChangeSetting, P.SETTINGSID AS PortletSettingsId, P.SORTORDER AS PortletSortOrder, P.STATUS AS PortletStatus, P.VERSION AS PortletVersion, P.SOURCETYPE AS PortletSourceType, P.PUBLISHCHANNELNAME AS PortletPublishChannelName, P.PUBLISHFIELDNAME AS PortletPublishFieldName, P.SUBSCRIBECHANNELNAME AS PortletSubscribeChannelName, P.DEFAULTCRITERIACONFIGID AS DefaultCriteriaConfigId, P.OVERRIDEPAGECRITERIA AS OverridePageCriteria, P.SUBSCRIBEFIELDNAME AS PortletSubscribeFieldName, P.CHARTTYPE AS PortletChartType, P.CHARTXAXIS AS PortletChartXAxis, P.CHARTYAXIS AS PortletChartYAxis, P.CHARTVALUE AS PortletChartValue, P.REPORTFORMATID AS ReportFormatId, RF.REPORTFILE AS ReportFormatReportFile, RF.REPORTFORMATNAME AS ReportFormatName, RF.DATASOURCEFILENAME AS ReportFormatDataSourceFileName, P.ISPAGENATIONREQUIRED AS PortletIsPagenationRequired, P.NUMBEROFRECORDS AS PortletNumberOfRecords, P.XAXISDATEFORMAT AS PortletXaxisDateFormat, P.YAXISDATEFORMAT AS PortletYaxisDateFormat, P.HEIGHT AS PortletHeight, P.WIDTH AS PortletWidth, P.CREATEDBYID AS PortletCreatedById, P.CREATEDON AS PortletCreatedOn, P.MODIFIEDBYID AS PortletModifiedById, P.MODIFIEDON AS PortletModifiedOn, P.WEBSERVICESETTINGID AS WebServiceSettingId, P.BIVIEWID AS BIViewId, P.KPIID AS KPIId, P.KPILISTID AS KPIListId, P.PORTLETKPIOUID AS PortletKPIOUId, P.FILEID AS FileId, MF.FILENAME AS FileName, MF.FILELOCATION AS FileLocation, P.FILECONTENT AS FileContent, P.CONTENTATTACHMENTID AS ContentAttachmentId, P.FORMLINKMENUID AS FormLinkMenuId, MME.MENUCODE AS FormLinkMenuCode, MME.MENUNAME AS FormLinkMenuName, P.THUMBNAIL AS PortletThumbNail, P.DESCRIPTION AS PortletDescription FROM MPORTLET P LEFT JOIN MPORTLETTYPE PT ON P.PORTLETTYPEID = PT.PORTLETTYPEID LEFT JOIN MMODULE M ON P.MODULEID = M.MODULEID LEFT JOIN MREPORT R ON P.REPORTID = R.REPORTID LEFT JOIN MPROGRAM MP ON P.PROGRAMID = MP.PROGRAMID LEFT JOIN MMENU MM ON P.MENUID = MM.MENUID LEFT JOIN MREPORTFORMAT RF ON P.REPORTFORMATID = RF.REPORTFORMATID LEFT JOIN MMENU MME ON P.FORMLINKMENUID = MME.MENUID LEFT JOIN MFILE MF ON P.FILEID = MF.FILEID WHERE P.PORTLETID = @portletid; "; public const string SAVE_PORTLET = @"INSERT INTO MPORTLET ( PORTLETID, PORTLETCODE, PORTLETNAME, PORTLETTYPEID, MODULEID, SECTION, URL, ISCHANGEABLE, REPORTID, PROGRAMID, MENUID, ISMAXIMIZE, ISRESIZE, ISPRINT, ISEXPORT, ISSETTINGSID, ISCHANGESETTING, SETTINGSID, SORTORDER, STATUS, VERSION, SOURCETYPE, PUBLISHCHANNELNAME, PUBLISHFIELDNAME, SUBSCRIBECHANNELNAME, DEFAULTCRITERIACONFIGID, OVERRIDEPAGECRITERIA, SUBSCRIBEFIELDNAME, CHARTTYPE, CHARTXAXIS, CHARTYAXIS, CHARTVALUE, REPORTFORMATID, ISPAGENATIONREQUIRED, NUMBEROFRECORDS, XAXISDATEFORMAT, YAXISDATEFORMAT, HEIGHT, WIDTH, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, WEBSERVICESETTINGID, BIVIEWID, KPIID, KPILISTID, PORTLETKPIOUID, FILEID, FILECONTENT, CONTENTATTACHMENTID, FORMLINKMENUID, THUMBNAIL, DESCRIPTION ) VALUES ( @PortletId, @PortletCode, @PortletName, @PortletTypeId, @ModuleId, @PortletSection, @PortletUrl, @PortletIsChangeable, @ReportId, @ProgramId, @MenuId, @PortletIsMaximize, @PortletIsResize, @PortletIsPrint, @PortletIsExport, @PortletIsSettings, @PortletIsChangeSetting, @PortletSettingsId, @PortletSortOrder, @PortletStatus, @PortletVersion, @PortletSourceType, @PortletPublishChannelName, @PortletPublishFieldName, @PortletSubscribeChannelName, @DefaultCriteriaConfigId, @OverridePageCriteria, @PortletSubscribeFieldName, @PortletChartType, @PortletChartXAxis, @PortletChartYAxis, @PortletChartValue, @ReportFormatId, @PortletIsPagenationRequired, @PortletNumberOfRecords, @PortletXaxisDateFormat, @PortletYaxisDateFormat, @PortletHeight, @PortletWidth, @PortletCreatedById, @PortletCreatedOn, @PortletModifiedById, @PortletModifiedOn, @WebServiceSettingId, @BIViewId, @KPIId, @KPIListId, @PortletKPIOUId, @FileId, @FileContent, @ContentAttachmentId, @FormLinkMenuId, @PortletThumbNail, @PortletDescription ); "; public const string UPDATE_PORTLET = @"UPDATE MPORTLET SET PORTLETCODE = @PortletCode, PORTLETNAME = @PortletName, PORTLETTYPEID = @PortletTypeId, MODULEID = @ModuleId, SECTION = @PortletSection, URL = @PortletUrl, ISCHANGEABLE = @PortletIsChangeable, REPORTID = @ReportId, PROGRAMID = @ProgramId, MENUID = @MenuId, ISMAXIMIZE = @PortletIsMaximize, ISRESIZE = @PortletIsResize, ISPRINT = @PortletIsPrint, ISEXPORT = @PortletIsExport, ISSETTINGSID = @PortletIsSettings, ISCHANGESETTING = @PortletIsChangeSetting, SETTINGSID = @PortletSettingsId, SORTORDER = @PortletSortOrder, STATUS = @PortletStatus, VERSION = @PortletVersion, SOURCETYPE = @PortletSourceType, PUBLISHCHANNELNAME = @PortletPublishChannelName, PUBLISHFIELDNAME = @PortletPublishFieldName, SUBSCRIBECHANNELNAME = @PortletSubscribeChannelName, DEFAULTCRITERIACONFIGID = @DefaultCriteriaConfigId, OVERRIDEPAGECRITERIA = @OverridePageCriteria, SUBSCRIBEFIELDNAME = @PortletSubscribeFieldName, CHARTTYPE = @PortletChartType, CHARTXAXIS = @PortletChartXAxis, CHARTYAXIS = @PortletChartYAxis, CHARTVALUE = @PortletChartValue, REPORTFORMATID = @ReportFormatId, ISPAGENATIONREQUIRED = @PortletIsPagenationRequired, NUMBEROFRECORDS = @PortletNumberOfRecords, XAXISDATEFORMAT = @PortletXaxisDateFormat, YAXISDATEFORMAT = @PortletYaxisDateFormat, HEIGHT = @PortletHeight, WIDTH = @PortletWidth, MODIFIEDBYID = @PortletModifiedById, MODIFIEDON = @PortletModifiedOn, WEBSERVICESETTINGID = @WebServiceSettingId, BIVIEWID = @BIViewId, KPIID = @KPIId, KPILISTID = @KPIListId, PORTLETKPIOUID = @PortletKPIOUId, FILEID = @FileId, FILECONTENT = @FileContent, CONTENTATTACHMENTID = @ContentAttachmentId, FORMLINKMENUID = @FormLinkMenuId, THUMBNAIL = @PortletThumbNail, DESCRIPTION = @PortletDescription WHERE PORTLETID = @PortletId; "; public const string DELETE_PORTLET = @"DELETE FROM MPORTLET WHERE PORTLETID = @portletid; "; public const string GET_SELECTLIST_PORTLET = @"SELECT PORTLETID AS Id, PORTLETCODE AS Code, PORTLETNAME AS Name FROM MPORTLET "; public const string GET_USER_PORTLETLIST_WITH_CRITERIA = @"SELECT UVP.USERVSPORTLETID AS UserVsPortletId, UVP.PAGEID AS UserVsPortletPageId, PG.PAGECODE AS PageCode, PG.PAGENAME AS PageName, PG.DEFAULTCRITERIACONFIGID AS PageDefaultCriteriaConfigId, PG.OVERRIDEDASHBOARDCRITERIA AS PageOverrideDashboardCriteria, P.OVERRIDEPAGECRITERIA AS PortletOverridePageCriteria, UVP.PORTLETID AS UserVsPortletPortletId, P.PORTLETCODE AS PortletCode, P.PORTLETNAME AS PortletName, P.PORTLETTYPEID AS PortletTypeId, PT.PORTLETTYPECODE AS PortletTypeCode, PT.PORTLETTYPENAME AS PortletTypeName, PT.ISMAXIMIZE AS PortletTypeIsMaximize, PT.ISRESIZE AS PortletTypeIsResize, PT.ISPRINT AS PortletTypeIsPrint, PT.ISEXPORT AS PortletTypeIsExport, PT.ISSETTINGSID AS PortletTypeIsSettingsId, PT.ISCHANGESETTING AS PortletTypeIsChangeSettings, P.URL AS PortletUrl, P.ISCHANGEABLE AS PortletIsChangeable, P.MENUID AS PortletMenuId, M.MENUCODE AS MenuCode, M.MENUNAME AS MenuName, CASE M.MENUTYPE WHEN 0 THEN 'Master' WHEN 1 THEN 'Transaction' WHEN 2 THEN 'Reports' WHEN 3 THEN 'Others' WHEN 4 THEN 'Bot' WHEN 5 THEN 'Dashboard' END AS MenuType, M.REPORTID AS MenuReportId, R.REPORTNAME AS ReportName, R.REPORTURI AS ReportUri, R.WEBSERVICEID AS WebServiceId, WS.WEBSERVICENAME AS WebServiceName, REPLACE( REPLACE(WS.URITEMPLATE, '{BaseURI}', ':baseuri'), '{PartyBranchId}', ':partybranchid' ) AS WebServiceUriTemplate, WS.METHODTYPE AS WebServiceMethodType, CASE WS.METHODTYPE WHEN 0 THEN 'GET' WHEN 1 THEN 'POST' WHEN 2 THEN 'DELETE' END AS MethodType, M.WEBFORMID AS WebFormId, WF.WEBFORMNAME AS WebFormName, WF.WEBFORMURL AS WebFormURL, WF.TEMPLATE AS WebFormTemplate, M.BIZTRANSACTIONCLASSID AS BizTransactionClassId, BC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode, BC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName, M.HELPURL AS HelpUrl, M.URIPARAMETERVALUE, P.ISMAXIMIZE AS PortletIsMaximize, P.ISRESIZE AS PortletIsResize, P.ISPRINT AS PortletIsPrint, P.ISEXPORT AS PortletIsExport, P.ISSETTINGSID AS PortletIsSettings, P.ISCHANGESETTING AS PortletIsChangeSetting, P.SETTINGSID AS PortletSettingsId, P.PUBLISHCHANNELNAME AS PortletPublishChannelName, P.PUBLISHFIELDNAME AS PortletPublishFieldName, P.SUBSCRIBECHANNELNAME AS PortletSubscribeChannelName, P.SUBSCRIBEFIELDNAME AS PortletSubscribeFieldName, P.CHARTTYPE AS PortletChartType, P.CHARTXAXIS AS PortletChartXAxis, P.CHARTYAXIS AS PortletChartYAxis, P.CHARTVALUE AS PortletChartValue, P.BIVIEWID AS BIViewId, P.KPIID AS KPIId, P.KPILISTID AS KPIListId, P.PORTLETKPIOUID AS PortletKPIOUId, P.XAXISDATEFORMAT AS PortletXaxisDateFormat, P.YAXISDATEFORMAT AS PortletYaxisDateFormat, P.DESCRIPTION AS PortletDescription, P.REPORTFORMATID AS PortletReportFormatId, RF.REPORTFORMATNAME AS ReportFormatName, RF.REPORTFILE AS ReportFile, RF.DATASOURCEFILENAME AS PortletDataSourceFileName, P.ISPAGENATIONREQUIRED AS PortletIsPagenationRequired, P.NUMBEROFRECORDS AS PortletNumberOfRecords, P.FORMLINKMENUID AS FormLinkMenuId, FM.MENUCODE AS FormLinkMenuCode, FM.MENUNAME AS FormLinkMenuName, UVP.HEIGHT AS UserVsPortletHeight, UVP.WIDTH AS UserVsPortletWidth, UVP.SLNO AS UserVsPortletSlNo, 'USER' AS SourceType, UVP.PORTLETROW AS PortletRow, UVP.PORTLETCOLUMN AS PortletColumn, WSS.WEBSERVICESETTINGID AS WebServiceSettingId, WSS.WEBSERVICEID AS WebServiceSettingWebServiceId, WSS.MENUID AS WebServiceSettingMenuId, WSS.REPORTID AS WebServiceSettingReportId, MF.FILEID AS FileId, MF.FILENAME AS FileName, MF.FILELOCATION AS FileLocation, P.FILECONTENT AS FileContent, P.CONTENTATTACHMENTID AS ContentAttachmentId, WSC.WEBSERVICECRITERIAID AS WebServiceCriteriaId, WSC.SLNO AS WebServiceCriteriaSlNo, WSC.DISPLAYNAME AS WebServiceCriteriaDisplayName, WSC.FIELDNAME AS WebServiceCriteriaFieldName, WSC.PICKLISTFIELDNAME AS WebServiceCriteriaPicklistFieldName, WSC.CRITERIAATTRIBUTEID AS WebServiceCriteriaAttributeId, WSC.ISCOMPULSORY AS IsCompulsory, WSC.ISHEADER AS IsHeader, CA.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, CA.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CASE CA.CRITERIAATTRIBUTETYPE WHEN 0 THEN 'PickList' WHEN 1 THEN 'Check' WHEN 2 THEN 'Radio' WHEN 3 THEN 'List' WHEN 4 THEN 'Date' WHEN 5 THEN 'Numeric Entry' WHEN 6 THEN 'Text Entry' END AS CriteriaAttributeTypeName, CA.PICKLISTID AS CriteriaAttributePicklistId, PI.PICKLISTCODE AS CriteriaAttributePicklistCode, PI.DISPLAYFIELDNAME AS CriteriaAttributePicklistDisplayFieldName, PI.SELECTIONFIELDNAME AS CriteriaAttributePicklistSelectionFieldName, PI.PICKLISTURI AS CriteriaAttributePicklistURI, PI.SEARCHFIELDS AS CriteriaAttributeSearchFields, CA.APPLICABLEVALUES AS CriteriaAttributeApplicableValues, CA.VARIABLEFIELD AS CriteriaAttributeVariableField, CA.APPLICABLEOPERATION AS CriteriaAttributeApplicableOperation, CA.DEFAULTOPERATION AS CriteriaAttributeDefaultOperation, RVF.REPORTID AS MenuReportId, RVF.REPORTVSFIELDSID AS ReportVsFieldsId, RVF.SLNO AS ReportVsFieldsSlNo, RVF.FIELDNAME AS ReportVsFieldsFieldName, RVF.FIELDTITLE AS ReportVsFieldsFieldTitle, RVF.FIELDTYPE AS ReportVsFieldsFieldType, RVF.FIELDWIDTH AS ReportVsFieldsFieldWidth, RVF.ISDISPLAY AS ReportVsFieldsIsDisplay, RVF.ISMERGECOLUMN AS ReportVsFieldsIsMergeColumn, RVF.ISGROUPCOLUMN AS ReportVsFieldsIsGroupColumn, RVF.ISSUBTOTAL AS ReportVsFieldsIsSubtotalColumn, RVF.ISGRANDTOTAL AS ReportVsFieldsIsGrandtotalColumn, RVF.CALFORMAT AS ReportVsFieldsCalFormat, UVP.PORTLETID AS UserVsPortletPortletId, CC.CRITERIACONFIGID AS UserVsPortletCriteriaConfigId, CC.CRITERIACONFIGNAME AS CriteriaConfigName, CC.VERSION AS CriteriaConfigVersion, CCS.CRITERIACONFIGSECTIONID AS CriteriaConfigSectionId, CCS.SLNO AS CriteriaConfigSectionSlNo, CCS.SECTIONJOIN AS CriteriaConfigSectionJoin, CCS.CRITERIACONFIGID AS UserVsPortletCriteriaConfigId, CCA.CRITERIACONFIGATTRIBUTEID AS CriteriaConfigAttributeId, CCA.SLNO AS CriteriaConfigAttributeSlNo, CCA.CRITERIAATTRIBUTEID AS CriteriaAttributeId, MCA.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, MWSC.ISCOMPULSORY AS IsCompulsory, MWSC.ISHEADER AS IsHeader, MCA.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CCA.OPERATIONTYPE AS CriteriaConfigAttributeOperationType, CCA.CRITERIAFIELDVALUE AS CriteriaConfigAttributeFieldValue, CCA.CRITERIAFIELDVALUEIN AS CriteriaConfigAttributeFieldValueIn, CCA.CRITERIAJOIN AS CriteriaConfigAttributeJoin, CCA.CRITERIAFIELDDISPLAYVALUE AS CriteriaConfigAttributeFieldDisplayValue, CCA.CRITERIAVARIABLEFIELD AS CriteriaConfigAttributeVariableField, MCA.APPLICABLEOPERATION AS ApplicableOperation, MCA.DEFAULTOPERATION AS DefaultOperation, UVP.USERVSPORTLETID AS UserVsPortletId, UVP.PORTLETID AS UserVsPortletPortletId, P.PORTLETCODE AS PortletCode, P.PORTLETNAME AS PortletName, UVP.SLNO AS SlNo, P.MENUID AS MenuId, M.MENUCODE AS MenuCode, M.MENUNAME AS MenuName, M.MENUDESCRIPTION AS MenuDescription, M.MENUTOOLTIP AS MenuToolTip, M.MENUCAPTION AS MenuCaption, M.MENUTYPE AS MenuType, M.ISPARENTMENU AS IsParentMenu, M.IMAGEID AS MenuImageId, I.IMAGELINKURL AS MenuImageURL, M.MENUNATURE AS MenuNature, M.PAGEID AS MenuPageId, MP.PAGECODE AS PageCode, MP.PAGENAME AS PageName, MP.PAGELAYOUT AS PageLayOut, M.REPORTID AS MenuReportId, M.WEBFORMID AS MenuWebFormId, WF.WEBFORMNAME AS WebFormName, WF.WEBFORMURL AS WebFormURL, WF.TEMPLATE AS WebFormTemplate, M.BIZTRANSACTIONCLASSID AS BizTransactionClassId, BC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode, BC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName, M.HELPURL AS HelpUrl, M.GCMTYPEID AS GcmTypeId, UVP.USERVSPORTLETID AS UserVsPortletId, UVP.PORTLETID AS UserVsPortletPortletId, UVP.PAGEID AS UserVsPortletPageId, DR.DRILLDOWNID AS DrillDownId, DR.DRILLDOWNCODE AS DrillDownCode, DR.DRILLDOWNDESCRIPTION AS DrillDownDescription, DR.DRILLTYPE AS DrillDownType, DR.FROMTYPE AS FromType, DR.FROMMENUID AS FromMenuId, FDM.MENUCODE AS FromMenuCode, FDM.MENUNAME AS FromMenuName, DR.FROMPORTLETID AS FromPortletId, FDP.PORTLETCODE AS FromPortletCode, FDP.PORTLETNAME AS FromPortletName, DR.TOTYPE AS ToType, DR.TOMENUID AS ToMenuId, TDM.MENUCODE AS ToMenuCode, TDM.MENUNAME AS ToMenuName, DR.TOPORTLETID AS ToPortletId, TDP.PORTLETCODE AS ToPortletCode, TDP.PORTLETNAME AS ToPortletName, DRD.DRILLDOWNDETAILLINEID AS DrillDownDetailId, DRD.DRILLDOWNID AS DrillDownId, DRD.MAPTYPE AS MapType, DRD.SLNO AS SlNo, DRD.ATTRIBUTEID AS AttributeId, DCA.CRITERIAATTRIBUTENAME AS AttributeName, DRD.ASSIGNTYPE AS AssignType, DRD.SOURCEFIELD AS SourceField, DRD.FILTERVALUE AS FilterValue, DRD.OPERATIONTYPE AS OperationType, DRD.JOINTYPE AS JoinType, WSS.WEBSERVICESETTINGID AS WebServiceSettingId, WSS.WEBSERVICEID AS WebServiceId, MWS.WEBSERVICENAME AS WebServiceName, MWS.METHODTYPE AS WebServiceSettingSettingType, WSS.REPORTID AS ReportId, WSR.REPORTNAME AS ReportName, WSS.MENUID AS MenuId, WSM.MENUCODE AS MenuCode, WSM.MENUNAME AS MenuName, WSS.CREATEDBYID AS WebServiceSettingCreatedById, WSS.CREATEDON AS WebServiceSettingCreatedOn, WSS.MODIFIEDBYID AS WebServiceSettingModifiedById, WSS.MODIFIEDON AS WebServiceSettingModifiedOn, WSS.SORTORDER AS WebServiceSettingSortOrder, WSS.STATUS AS WebServiceSettingStatus, WSS.VERSION AS WebServiceSettingVersion, WSS.SOURCETYPE AS WebServiceSettingSourceType, WSD.WEBSERVICESETTINGDETAILID AS WebServiceSettingDetailId, WSD.WEBSERVICESETTINGID AS ServiceSettingId, WSD.SLNO AS WebServiceSettingDetailSlno, WCD.CRITERIAATTRIBUTEID AS CriteriaAttributeId, CAWD.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, CAWD.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CAWD.CONTEXTVALUEFIELDNAME AS CriteriaAttributeContextValueFieldName, WSD.ISCOMPULSORY AS WebServiceSettingDetailIsCompulsory, WSD.ISHEADER AS WebServiceSettingDetailIsHeader, WSD.ISVISIBLE AS WebServiceSettingDetailIsVisible, WSD.ISCHANGEABLE AS WebServiceSettingDetailIsChangeable, WSD.OPERATIONTYPE AS WebServiceSettingDetailOperationType, WSD.CRITERIAFIELDVALUE AS WebServiceSettingDetailCriteriaFieldValue, WSD.DISPLAYNAME AS WebServiceSettingDetailDisplayName, WSD.CRITERIAJOIN AS WebServiceSettingDetailCriteriaJoin, WSD.WEBSERVICECRITERIAID AS WebServiceCriteriaId, WCD.FIELDNAME AS WebServiceCriteriaFieldName FROM MUSERVSPORTLET UVP LEFT JOIN MPORTLET P ON UVP.PORTLETID = P.PORTLETID LEFT JOIN MPORTLETTYPE PT ON P.PORTLETTYPEID = PT.PORTLETTYPEID LEFT JOIN MPAGE PG ON UVP.PAGEID = PG.PAGEID LEFT JOIN MMENU M ON P.MENUID = M.MENUID LEFT JOIN MREPORT R ON M.REPORTID = R.REPORTID LEFT JOIN MWEBSERVICE WS ON R.WEBSERVICEID = WS.WEBSERVICEID LEFT JOIN MWEBFORM WF ON M.WEBFORMID = WF.WEBFORMID LEFT JOIN MREPORTFORMAT RF ON P.REPORTFORMATID = RF.REPORTFORMATID LEFT JOIN MUSER U ON UVP.USERID = U.USERID LEFT JOIN MROLE RO ON U.ROLEID = RO.ROLEID LEFT JOIN MROLEVSMENU RM ON RO.ROLEID = RM.ROLEID AND M.MENUID = RM.MENUID LEFT JOIN MBIZTRANSACTIONCLASS BC ON M.BIZTRANSACTIONCLASSID = BC.BIZTRANSACTIONCLASSID LEFT JOIN MMENU FM ON P.FORMLINKMENUID = FM.MENUID LEFT JOIN MWEBSERVICESETTING WSS ON P.WEBSERVICESETTINGID = WSS.WEBSERVICESETTINGID LEFT JOIN MFILE MF ON P.FILEID = MF.FILEID LEFT JOIN MWEBSERVICECRITERIA WSC ON WS.WEBSERVICEID = WSC.WEBSERVICEID LEFT JOIN MCRITERIAATTRIBUTE CA ON WSC.CRITERIAATTRIBUTEID = CA.CRITERIAATTRIBUTEID LEFT JOIN MPICKLIST PI ON CA.PICKLISTID = PI.PICKLISTID LEFT JOIN MREPORTVSFIELDS RVF ON M.REPORTID = RVF.REPORTID LEFT JOIN TCRITERIACONFIG CC ON UVP.CRITERIACONFIGID = CC.CRITERIACONFIGID LEFT JOIN TCRITERIACONFIGSECTION CCS ON CC.CRITERIACONFIGID = CCS.CRITERIACONFIGID LEFT JOIN TCRITERIACONFIGATTRIBUTE CCA ON CCS.CRITERIACONFIGSECTIONID = CCA.CRITERIASECTIONID LEFT JOIN MCRITERIAATTRIBUTE MCA ON CCA.CRITERIAATTRIBUTEID = MCA.CRITERIAATTRIBUTEID LEFT JOIN MWEBSERVICECRITERIA MWSC ON MCA.CRITERIAATTRIBUTEID = MWSC.CRITERIAATTRIBUTEID LEFT JOIN MIMAGE I ON M.IMAGEID = I.IMAGEID LEFT JOIN MPAGE MP ON M.PAGEID = MP.PAGEID LEFT JOIN MDRILLDOWN DR ON M.MODULEID = DR.MODULEID LEFT JOIN MMENU FDM ON DR.FROMMENUID = FDM.MENUID LEFT JOIN MPORTLET FDP ON DR.FROMPORTLETID = FDP.PORTLETID LEFT JOIN MMENU TDM ON DR.TOMENUID = TDM.MENUID LEFT JOIN MPORTLET TDP ON DR.TOPORTLETID = TDP.PORTLETID LEFT JOIN MDRILLDOWNDETAIL DRD ON DR.DRILLDOWNID = DRD.DRILLDOWNID LEFT JOIN MCRITERIAATTRIBUTE DCA ON DRD.ATTRIBUTEID = DCA.CRITERIAATTRIBUTEID LEFT JOIN MWEBSERVICE MWS ON WSS.WEBSERVICEID = MWS.WEBSERVICEID LEFT JOIN MREPORT WSR ON WSS.REPORTID = WSR.REPORTID LEFT JOIN MMENU WSM ON WSS.MENUID = WSM.MENUID LEFT JOIN MWEBSERVICESETTINGDETAIL WSD ON WSS.WEBSERVICESETTINGID = WSD.WEBSERVICESETTINGID LEFT JOIN MWEBSERVICECRITERIA WCD ON WSD.WEBSERVICECRITERIAID = WCD.WEBSERVICECRITERIAID LEFT JOIN MCRITERIAATTRIBUTE CAWD ON WCD.CRITERIAATTRIBUTEID = CAWD.CRITERIAATTRIBUTEID WHERE UVP.USERID = @userid AND UVP.PAGEID = @pageid ORDER BY UVP.PORTLETID; "; //public const string GET_USER_PORTLETLIST_WITH_CRITERIA = @"SELECT // UVP.USERVSPORTLETID AS UserVsPortletId, // UVP.PAGEID AS UserVsPortletPageId, // PG.PAGECODE AS PageCode, // PG.PAGENAME AS PageName, // UVP.PORTLETID AS UserVsPortletPortletId, // P.PORTLETCODE AS PortletCode, // P.PORTLETNAME AS PortletName, // P.PORTLETTYPEID AS PortletTypeId, // PT.PORTLETTYPECODE AS PortletTypeCode, // PT.PORTLETTYPENAME AS PortletTypeName, // PT.ISMAXIMIZE AS PortletTypeIsMaximize, // PT.ISRESIZE AS PortletTypeIsResize, // PT.ISPRINT AS PortletTypeIsPrint, // PT.ISEXPORT AS PortletTypeIsExport, // PT.ISSETTINGSID AS PortletTypeIsSettingsId, // PT.ISCHANGESETTING AS PortletTypeIsChangeSettings, // P.URL AS PortletUrl, // P.ISCHANGEABLE AS PortletIsChangeable, // P.MENUID AS PortletMenuId, // M.MENUCODE AS MenuCode, // M.MENUNAME AS MenuName, // CASE M.MENUTYPE // WHEN 0 THEN 'Master' // WHEN 1 THEN 'Transaction' // WHEN 2 THEN 'Reports' // WHEN 3 THEN 'Others' // WHEN 4 THEN 'Bot' // WHEN 5 THEN 'Dashboard' // END AS MenuType, // M.REPORTID AS MenuReportId, // R.REPORTNAME AS ReportName, // R.REPORTURI AS ReportUri, // R.WEBSERVICEID AS WebServiceId, // WS.WEBSERVICENAME AS WebServiceName, // WS.URITEMPLATE AS WebServiceUriTemplate, // WS.METHODTYPE AS WebServiceMethodType, // CASE WS.METHODTYPE // WHEN 0 THEN 'GET' // WHEN 1 THEN 'POST' // WHEN 2 THEN 'DELETE' // END AS MethodType, // M.WEBFORMID AS WebFormId, // WF.WEBFORMNAME AS WebFormName, // WF.WEBFORMURL AS WebFormURL, // WF.TEMPLATE AS WebFormTemplate, // M.BIZTRANSACTIONCLASSID AS BizTransactionClassId, // BC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode, // BC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName, // M.HELPURL AS HelpUrl, // P.ISMAXIMIZE AS PortletIsMaximize, // P.ISRESIZE AS PortletIsResize, // P.ISPRINT AS PortletIsPrint, // P.ISEXPORT AS PortletIsExport, // P.ISSETTINGSID AS PortletIsSettings, // P.ISCHANGESETTING AS PortletIsChangeSetting, // P.SETTINGSID AS PortletSettingsId, // P.PUBLISHCHANNELNAME AS PortletPublishChannelName, // P.PUBLISHFIELDNAME AS PortletPublishFieldName, // P.SUBSCRIBECHANNELNAME AS PortletSubscribeChannelName, // P.SUBSCRIBEFIELDNAME AS PortletSubscribeFieldName, // P.CHARTTYPE AS PortletChartType, // P.CHARTXAXIS AS PortletChartXAxis, // P.CHARTYAXIS AS PortletChartYAxis, // P.CHARTVALUE AS PortletChartValue, // P.XAXISDATEFORMAT AS PortletXaxisDateFormat, // P.YAXISDATEFORMAT AS PortletYaxisDateFormat, // P.DESCRIPTION AS PortletDescription, // P.REPORTFORMATID AS PortletReportFormatId, // RF.REPORTFORMATNAME AS ReportFormatName, // RF.REPORTFILE AS ReportFile, // RF.DATASOURCEFILENAME AS PortletDataSourceFileName, // P.ISPAGENATIONREQUIRED AS PortletIsPagenationRequired, // P.NUMBEROFRECORDS AS PortletNumberOfRecords, // P.FORMLINKMENUID AS FormLinkMenuId, // FM.MENUCODE AS FormLinkMenuCode, // FM.MENUNAME AS FormLinkMenuName, // UVP.HEIGHT AS UserVsPortletHeight, // UVP.WIDTH AS UserVsPortletWidth, // UVP.SLNO AS UserVsPortletSlNo, // UVP.PORTLETROW AS PortletRow, // UVP.PORTLETCOLUMN AS PortletColumn, // WSS.WEBSERVICESETTINGID AS WebServiceSettingId, // WSS.WEBSERVICEID AS WebServiceSettingWebServiceId, // WSS.MENUID AS WebServiceSettingMenuId, // WSS.REPORTID AS WebServiceSettingReportId, // MF.FILEID AS FileId, // MF.FILENAME AS FileName, // MF.FILELOCATION AS FileLocation // FROM MUSERVSPORTLET UVP // LEFT JOIN MPORTLET P ON UVP.PORTLETID = P.PORTLETID // LEFT JOIN MPORTLETTYPE PT ON P.PORTLETTYPEID = PT.PORTLETTYPEID // LEFT JOIN MPAGE PG ON UVP.PAGEID = PG.PAGEID // LEFT JOIN MMENU M ON P.MENUID = M.MENUID // LEFT JOIN MREPORT R ON M.REPORTID = R.REPORTID // LEFT JOIN MWEBSERVICE WS ON R.WEBSERVICEID = WS.WEBSERVICEID // LEFT JOIN MWEBFORM WF ON M.WEBFORMID = WF.WEBFORMID // LEFT JOIN MREPORTFORMAT RF ON P.REPORTFORMATID = RF.REPORTFORMATID // LEFT JOIN MUSER U ON UVP.USERID = U.USERID // LEFT JOIN MROLE RO ON U.ROLEID = RO.ROLEID // LEFT JOIN MROLEVSMENU RM ON RO.ROLEID = RM.ROLEID AND M.MENUID = RM.MENUID // LEFT JOIN MBIZTRANSACTIONCLASS BC ON M.BIZTRANSACTIONCLASSID = BC.BIZTRANSACTIONCLASSID // LEFT JOIN MMENU FM ON P.FORMLINKMENUID = FM.MENUID // LEFT JOIN MWEBSERVICESETTING WSS ON P.WEBSERVICESETTINGID = WSS.WEBSERVICESETTINGID // LEFT JOIN MFILE MF ON P.FILEID = MF.FILEID // WHERE UVP.USERID = @userid AND UVP.PAGEID = @pageid // ORDER BY UVP.PORTLETID; // "; public const string GET_ROLE_PORTLETLIST_WITH_CRITERIA = @"SELECT RVP.ROLEVSPORTLETID AS UserVsPortletId, RVP.PAGEID AS UserVsPortletPageId, PG.PAGECODE AS PageCode, PG.PAGENAME AS PageName, PG.DEFAULTCRITERIACONFIGID AS PageDefaultCriteriaConfigId, PG.OVERRIDEDASHBOARDCRITERIA AS PageOverrideDashboardCriteria, P.OVERRIDEPAGECRITERIA AS PortletOverridePageCriteria, RVP.PORTLETID AS UserVsPortletPortletId, P.PORTLETCODE AS PortletCode, P.PORTLETNAME AS PortletName, P.PORTLETTYPEID AS PortletTypeId, PT.PORTLETTYPECODE AS PortletTypeCode, PT.PORTLETTYPENAME AS PortletTypeName, PT.ISMAXIMIZE AS PortletTypeIsMaximize, PT.ISRESIZE AS PortletTypeIsResize, PT.ISPRINT AS PortletTypeIsPrint, PT.ISEXPORT AS PortletTypeIsExport, PT.ISSETTINGSID AS PortletTypeIsSettingsId, PT.ISCHANGESETTING AS PortletTypeIsChangeSettings, P.URL AS PortletUrl, P.ISCHANGEABLE AS PortletIsChangeable, P.MENUID AS PortletMenuId, M.MENUCODE AS MenuCode, M.MENUNAME AS MenuName, CASE M.MENUTYPE WHEN 0 THEN 'Master' WHEN 1 THEN 'Transaction' WHEN 2 THEN 'Reports' WHEN 3 THEN 'Others' WHEN 4 THEN 'Bot' WHEN 5 THEN 'Dashboard' END AS MenuType, M.REPORTID AS MenuReportId, R.REPORTNAME AS ReportName, R.REPORTURI AS ReportUri, R.WEBSERVICEID AS WebServiceId, WS.WEBSERVICENAME AS WebServiceName, REPLACE( REPLACE(WS.URITEMPLATE, '{BaseURI}', ':baseuri'), '{PartyBranchId}', ':partybranchid' ) AS WebServiceUriTemplate, WS.METHODTYPE AS WebServiceMethodType, CASE WS.METHODTYPE WHEN 0 THEN 'GET' WHEN 1 THEN 'POST' WHEN 2 THEN 'DELETE' END AS MethodType, M.WEBFORMID AS WebFormId, WF.WEBFORMNAME AS WebFormName, WF.WEBFORMURL AS WebFormURL, WF.TEMPLATE AS WebFormTemplate, M.BIZTRANSACTIONCLASSID AS BizTransactionClassId, BC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode, BC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName, M.HELPURL AS HelpUrl, M.URIPARAMETERVALUE, P.ISMAXIMIZE AS PortletIsMaximize, P.ISRESIZE AS PortletIsResize, P.ISPRINT AS PortletIsPrint, P.ISEXPORT AS PortletIsExport, P.ISSETTINGSID AS PortletIsSettings, P.ISCHANGESETTING AS PortletIsChangeSetting, P.SETTINGSID AS PortletSettingsId, P.PUBLISHCHANNELNAME AS PortletPublishChannelName, P.PUBLISHFIELDNAME AS PortletPublishFieldName, P.SUBSCRIBECHANNELNAME AS PortletSubscribeChannelName, P.SUBSCRIBEFIELDNAME AS PortletSubscribeFieldName, P.CHARTTYPE AS PortletChartType, P.CHARTXAXIS AS PortletChartXAxis, P.CHARTYAXIS AS PortletChartYAxis, P.CHARTVALUE AS PortletChartValue, P.BIVIEWID AS BIViewId, P.KPIID AS KPIId, P.KPILISTID AS KPIListId, P.PORTLETKPIOUID AS PortletKPIOUId, P.XAXISDATEFORMAT AS PortletXaxisDateFormat, P.YAXISDATEFORMAT AS PortletYaxisDateFormat, P.DESCRIPTION AS PortletDescription, P.REPORTFORMATID AS PortletReportFormatId, RF.REPORTFORMATNAME AS ReportFormatName, RF.REPORTFILE AS ReportFile, RF.DATASOURCEFILENAME AS PortletDataSourceFileName, P.ISPAGENATIONREQUIRED AS PortletIsPagenationRequired, P.NUMBEROFRECORDS AS PortletNumberOfRecords, P.FORMLINKMENUID AS FormLinkMenuId, FM.MENUCODE AS FormLinkMenuCode, FM.MENUNAME AS FormLinkMenuName, 0 AS UserVsPortletHeight, 0 AS UserVsPortletWidth, RVP.SLNO AS UserVsPortletSlNo, 'ROLE' AS SourceType, RVP.PORTLETROW AS PortletRow, RVP.PORTLETCOLUMN AS PortletColumn, WSS.WEBSERVICESETTINGID AS WebServiceSettingId, WSS.WEBSERVICEID AS WebServiceSettingWebServiceId, WSS.MENUID AS WebServiceSettingMenuId, WSS.REPORTID AS WebServiceSettingReportId, MF.FILEID AS FileId, MF.FILENAME AS FileName, MF.FILELOCATION AS FileLocation, P.FILECONTENT AS FileContent, P.CONTENTATTACHMENTID AS ContentAttachmentId, WSC.WEBSERVICECRITERIAID AS WebServiceCriteriaId, WSC.SLNO AS WebServiceCriteriaSlNo, WSC.DISPLAYNAME AS WebServiceCriteriaDisplayName, WSC.FIELDNAME AS WebServiceCriteriaFieldName, WSC.PICKLISTFIELDNAME AS WebServiceCriteriaPicklistFieldName, WSC.CRITERIAATTRIBUTEID AS WebServiceCriteriaAttributeId, WSC.ISCOMPULSORY AS IsCompulsory, WSC.ISHEADER AS IsHeader, CA.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, CA.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CASE CA.CRITERIAATTRIBUTETYPE WHEN 0 THEN 'PickList' WHEN 1 THEN 'Check' WHEN 2 THEN 'Radio' WHEN 3 THEN 'List' WHEN 4 THEN 'Date' WHEN 5 THEN 'Numeric Entry' WHEN 6 THEN 'Text Entry' END AS CriteriaAttributeTypeName, CA.PICKLISTID AS CriteriaAttributePicklistId, PI.PICKLISTCODE AS CriteriaAttributePicklistCode, PI.DISPLAYFIELDNAME AS CriteriaAttributePicklistDisplayFieldName, PI.SELECTIONFIELDNAME AS CriteriaAttributePicklistSelectionFieldName, PI.PICKLISTURI AS CriteriaAttributePicklistURI, PI.SEARCHFIELDS AS CriteriaAttributeSearchFields, CA.APPLICABLEVALUES AS CriteriaAttributeApplicableValues, CA.VARIABLEFIELD AS CriteriaAttributeVariableField, CA.APPLICABLEOPERATION AS CriteriaAttributeApplicableOperation, CA.DEFAULTOPERATION AS CriteriaAttributeDefaultOperation, RVF.REPORTID AS MenuReportId, RVF.REPORTVSFIELDSID AS ReportVsFieldsId, RVF.SLNO AS ReportVsFieldsSlNo, RVF.FIELDNAME AS ReportVsFieldsFieldName, RVF.FIELDTITLE AS ReportVsFieldsFieldTitle, RVF.FIELDTYPE AS ReportVsFieldsFieldType, RVF.FIELDWIDTH AS ReportVsFieldsFieldWidth, RVF.ISDISPLAY AS ReportVsFieldsIsDisplay, RVF.ISMERGECOLUMN AS ReportVsFieldsIsMergeColumn, RVF.ISGROUPCOLUMN AS ReportVsFieldsIsGroupColumn, RVF.ISSUBTOTAL AS ReportVsFieldsIsSubtotalColumn, RVF.ISGRANDTOTAL AS ReportVsFieldsIsGrandtotalColumn, RVF.CALFORMAT AS ReportVsFieldsCalFormat, RVP.PORTLETID AS UserVsPortletPortletId, CC.CRITERIACONFIGID AS UserVsPortletCriteriaConfigId, CC.CRITERIACONFIGNAME AS CriteriaConfigName, CC.VERSION AS CriteriaConfigVersion, CCS.CRITERIACONFIGSECTIONID AS CriteriaConfigSectionId, CCS.SLNO AS CriteriaConfigSectionSlNo, CCS.SECTIONJOIN AS CriteriaConfigSectionJoin, CCS.CRITERIACONFIGID AS UserVsPortletCriteriaConfigId, CCA.CRITERIACONFIGATTRIBUTEID AS CriteriaConfigAttributeId, CCA.SLNO AS CriteriaConfigAttributeSlNo, CCA.CRITERIAATTRIBUTEID AS CriteriaAttributeId, MCA.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, MWSC.ISCOMPULSORY AS IsCompulsory, MWSC.ISHEADER AS IsHeader, MCA.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CCA.OPERATIONTYPE AS CriteriaConfigAttributeOperationType, CCA.CRITERIAFIELDVALUE AS CriteriaConfigAttributeFieldValue, CCA.CRITERIAFIELDVALUEIN AS CriteriaConfigAttributeFieldValueIn, CCA.CRITERIAJOIN AS CriteriaConfigAttributeJoin, CCA.CRITERIAFIELDDISPLAYVALUE AS CriteriaConfigAttributeFieldDisplayValue, CCA.CRITERIAVARIABLEFIELD AS CriteriaConfigAttributeVariableField, MCA.APPLICABLEOPERATION AS ApplicableOperation, MCA.DEFAULTOPERATION AS DefaultOperation, RVP.ROLEVSPORTLETID AS UserVsPortletId, RVP.PORTLETID AS UserVsPortletPortletId, P.PORTLETCODE AS PortletCode, P.PORTLETNAME AS PortletName, RVP.SLNO AS SlNo, P.MENUID AS MenuId, M.MENUCODE AS MenuCode, M.MENUNAME AS MenuName, M.MENUDESCRIPTION AS MenuDescription, M.MENUTOOLTIP AS MenuToolTip, M.MENUCAPTION AS MenuCaption, M.MENUTYPE AS MenuType, M.ISPARENTMENU AS IsParentMenu, M.IMAGEID AS MenuImageId, I.IMAGELINKURL AS MenuImageURL, M.MENUNATURE AS MenuNature, M.PAGEID AS MenuPageId, MP.PAGECODE AS PageCode, MP.PAGENAME AS PageName, MP.PAGELAYOUT AS PageLayOut, M.REPORTID AS MenuReportId, M.WEBFORMID AS MenuWebFormId, WF.WEBFORMNAME AS WebFormName, WF.WEBFORMURL AS WebFormURL, WF.TEMPLATE AS WebFormTemplate, M.BIZTRANSACTIONCLASSID AS BizTransactionClassId, BC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode, BC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName, M.HELPURL AS HelpUrl, M.GCMTYPEID AS GcmTypeId, RVP.ROLEVSPORTLETID AS UserVsPortletId, RVP.PORTLETID AS UserVsPortletPortletId, RVP.PAGEID AS UserVsPortletPageId, DR.DRILLDOWNID AS DrillDownId, DR.DRILLDOWNCODE AS DrillDownCode, DR.DRILLDOWNDESCRIPTION AS DrillDownDescription, DR.DRILLTYPE AS DrillDownType, DR.FROMTYPE AS FromType, DR.FROMMENUID AS FromMenuId, FDM.MENUCODE AS FromMenuCode, FDM.MENUNAME AS FromMenuName, DR.FROMPORTLETID AS FromPortletId, FDP.PORTLETCODE AS FromPortletCode, FDP.PORTLETNAME AS FromPortletName, DR.TOTYPE AS ToType, DR.TOMENUID AS ToMenuId, TDM.MENUCODE AS ToMenuCode, TDM.MENUNAME AS ToMenuName, DR.TOPORTLETID AS ToPortletId, TDP.PORTLETCODE AS ToPortletCode, TDP.PORTLETNAME AS ToPortletName, DRD.DRILLDOWNDETAILLINEID AS DrillDownDetailId, DRD.DRILLDOWNID AS DrillDownId, DRD.MAPTYPE AS MapType, DRD.SLNO AS SlNo, DRD.ATTRIBUTEID AS AttributeId, DCA.CRITERIAATTRIBUTENAME AS AttributeName, DRD.ASSIGNTYPE AS AssignType, DRD.SOURCEFIELD AS SourceField, DRD.FILTERVALUE AS FilterValue, DRD.OPERATIONTYPE AS OperationType, DRD.JOINTYPE AS JoinType, WSS.WEBSERVICESETTINGID AS WebServiceSettingId, WSS.WEBSERVICEID AS WebServiceId, MWS.WEBSERVICENAME AS WebServiceName, MWS.METHODTYPE AS WebServiceSettingSettingType, WSS.REPORTID AS ReportId, WSR.REPORTNAME AS ReportName, WSS.MENUID AS MenuId, WSM.MENUCODE AS MenuCode, WSM.MENUNAME AS MenuName, WSS.CREATEDBYID AS WebServiceSettingCreatedById, WSS.CREATEDON AS WebServiceSettingCreatedOn, WSS.MODIFIEDBYID AS WebServiceSettingModifiedById, WSS.MODIFIEDON AS WebServiceSettingModifiedOn, WSS.SORTORDER AS WebServiceSettingSortOrder, WSS.STATUS AS WebServiceSettingStatus, WSS.VERSION AS WebServiceSettingVersion, WSS.SOURCETYPE AS WebServiceSettingSourceType, WSD.WEBSERVICESETTINGDETAILID AS WebServiceSettingDetailId, WSD.WEBSERVICESETTINGID AS ServiceSettingId, WSD.SLNO AS WebServiceSettingDetailSlno, WCD.CRITERIAATTRIBUTEID AS CriteriaAttributeId, CAWD.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, CAWD.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CAWD.CONTEXTVALUEFIELDNAME AS CriteriaAttributeContextValueFieldName, WSD.ISCOMPULSORY AS WebServiceSettingDetailIsCompulsory, WSD.ISHEADER AS WebServiceSettingDetailIsHeader, WSD.ISVISIBLE AS WebServiceSettingDetailIsVisible, WSD.ISCHANGEABLE AS WebServiceSettingDetailIsChangeable, WSD.OPERATIONTYPE AS WebServiceSettingDetailOperationType, WSD.CRITERIAFIELDVALUE AS WebServiceSettingDetailCriteriaFieldValue, WSD.DISPLAYNAME AS WebServiceSettingDetailDisplayName, WSD.CRITERIAJOIN AS WebServiceSettingDetailCriteriaJoin, WSD.WEBSERVICECRITERIAID AS WebServiceCriteriaId, WCD.FIELDNAME AS WebServiceCriteriaFieldName FROM MROLEVSPORTLET RVP LEFT JOIN MPORTLET P ON RVP.PORTLETID = P.PORTLETID LEFT JOIN MPORTLETTYPE PT ON P.PORTLETTYPEID = PT.PORTLETTYPEID LEFT JOIN MPAGE PG ON RVP.PAGEID = PG.PAGEID LEFT JOIN MMENU M ON P.MENUID = M.MENUID LEFT JOIN MREPORT R ON M.REPORTID = R.REPORTID LEFT JOIN MWEBSERVICE WS ON R.WEBSERVICEID = WS.WEBSERVICEID LEFT JOIN MWEBFORM WF ON M.WEBFORMID = WF.WEBFORMID LEFT JOIN MREPORTFORMAT RF ON P.REPORTFORMATID = RF.REPORTFORMATID LEFT JOIN MROLE RO ON RVP.ROLEID = RO.ROLEID LEFT JOIN MROLEVSMENU RM ON RO.ROLEID = RM.ROLEID AND M.MENUID = RM.MENUID LEFT JOIN MBIZTRANSACTIONCLASS BC ON M.BIZTRANSACTIONCLASSID = BC.BIZTRANSACTIONCLASSID LEFT JOIN MMENU FM ON P.FORMLINKMENUID = FM.MENUID LEFT JOIN MWEBSERVICESETTING WSS ON P.WEBSERVICESETTINGID = WSS.WEBSERVICESETTINGID LEFT JOIN MFILE MF ON P.FILEID = MF.FILEID LEFT JOIN MWEBSERVICECRITERIA WSC ON WS.WEBSERVICEID = WSC.WEBSERVICEID LEFT JOIN MCRITERIAATTRIBUTE CA ON WSC.CRITERIAATTRIBUTEID = CA.CRITERIAATTRIBUTEID LEFT JOIN MPICKLIST PI ON CA.PICKLISTID = PI.PICKLISTID LEFT JOIN MREPORTVSFIELDS RVF ON M.REPORTID = RVF.REPORTID LEFT JOIN TCRITERIACONFIG CC ON RVP.CRITERIACONFIGID = CC.CRITERIACONFIGID LEFT JOIN TCRITERIACONFIGSECTION CCS ON CC.CRITERIACONFIGID = CCS.CRITERIACONFIGID LEFT JOIN TCRITERIACONFIGATTRIBUTE CCA ON CCS.CRITERIACONFIGSECTIONID = CCA.CRITERIASECTIONID LEFT JOIN MCRITERIAATTRIBUTE MCA ON CCA.CRITERIAATTRIBUTEID = MCA.CRITERIAATTRIBUTEID LEFT JOIN MWEBSERVICECRITERIA MWSC ON MCA.CRITERIAATTRIBUTEID = MWSC.CRITERIAATTRIBUTEID LEFT JOIN MIMAGE I ON M.IMAGEID = I.IMAGEID LEFT JOIN MPAGE MP ON M.PAGEID = MP.PAGEID LEFT JOIN MDRILLDOWN DR ON M.MODULEID = DR.MODULEID LEFT JOIN MMENU FDM ON DR.FROMMENUID = FDM.MENUID LEFT JOIN MPORTLET FDP ON DR.FROMPORTLETID = FDP.PORTLETID LEFT JOIN MMENU TDM ON DR.TOMENUID = TDM.MENUID LEFT JOIN MPORTLET TDP ON DR.TOPORTLETID = TDP.PORTLETID LEFT JOIN MDRILLDOWNDETAIL DRD ON DR.DRILLDOWNID = DRD.DRILLDOWNID LEFT JOIN MCRITERIAATTRIBUTE DCA ON DRD.ATTRIBUTEID = DCA.CRITERIAATTRIBUTEID LEFT JOIN MWEBSERVICE MWS ON WSS.WEBSERVICEID = MWS.WEBSERVICEID LEFT JOIN MREPORT WSR ON WSS.REPORTID = WSR.REPORTID LEFT JOIN MMENU WSM ON WSS.MENUID = WSM.MENUID LEFT JOIN MWEBSERVICESETTINGDETAIL WSD ON WSS.WEBSERVICESETTINGID = WSD.WEBSERVICESETTINGID LEFT JOIN MWEBSERVICECRITERIA WCD ON WSD.WEBSERVICECRITERIAID = WCD.WEBSERVICECRITERIAID LEFT JOIN MCRITERIAATTRIBUTE CAWD ON WCD.CRITERIAATTRIBUTEID = CAWD.CRITERIAATTRIBUTEID WHERE RVP.PAGEID = @pageid ORDER BY RVP.PORTLETID; "; public const string GET_PAGE_WITH_MENU_PAGE_LIST = @"SELECT DISTINCT RVP.PAGEID AS PageId, P.PAGECODE AS PageCode, P.PAGENAME AS PageName, P.PAGELAYOUT AS PageLayOut, ISNULL(M.MENUID, -1) AS MenuId FROM MROLEVSPORTLET RVP JOIN MPAGE P ON P.PAGEID = RVP.PAGEID LEFT JOIN MMENU M ON M.PAGEID = P.PAGEID LEFT JOIN MROLEVSMENU RVM ON RVM.MENUID = M.MENUID AND RVM.ROLEID = RVP.ROLEID WHERE RVP.ROLEID = @roleid AND P.PAGETYPE = 0 AND (M.MENUID IS NULL OR RVM.ALLOW LIKE '%0%'); "; public const string GET_PAGE_WITH_MENU_PAGE_LIST_POSTGRE = @"SELECT DISTINCT RVP.PAGEID AS PageId, P.PAGECODE AS PageCode, P.PAGENAME AS PageName, P.PAGELAYOUT AS PageLayOut, COALESCE(M.MENUID, -1) AS MenuId FROM MROLEVSPORTLET RVP JOIN MPAGE P ON P.PAGEID = RVP.PAGEID LEFT JOIN MMENU M ON M.PAGEID = P.PAGEID LEFT JOIN MROLEVSMENU RVM ON RVM.MENUID = M.MENUID AND RVM.ROLEID = RVP.ROLEID WHERE RVP.ROLEID = @roleid AND P.PAGETYPE = 0 AND (M.MENUID IS NULL OR RVM.ALLOW LIKE '%0%'); "; public const string GET_PAGELIST = @"SELECT DISTINCT UVP.PAGEID AS PageId, P.PAGECODE AS PageCode, P.PAGENAME AS PageName, P.PAGELAYOUT AS PageLayOut, P.IMAGEID AS ImageId, I.IMAGENAME AS ImageName, I.IMAGELINKURL AS ImageLinkURL, UVP.SLNO AS SlNo FROM MUSERVSPORTLET UVP LEFT JOIN MPAGE P ON UVP.PAGEID = P.PAGEID LEFT JOIN MIMAGE I ON P.IMAGEID = I.IMAGEID WHERE UVP.USERID = @userid AND P.PAGETYPE = 0; "; public const string GET_PORTLETLIST = @"SELECT UVP.USERVSPORTLETID AS UserVsPortletId, UVP.PORTLETID AS UserVsPortletPortletId, PO.PORTLETCODE AS PortletCode, PO.PORTLETNAME AS PortletName, PO.PORTLETTYPEID AS PortletTypeId, POT.PORTLETTYPECODE AS PortletTypeCode, POT.PORTLETTYPENAME AS PortletTypeName, POT.ISMAXIMIZE AS PortletTypeIsMaximize, POT.ISRESIZE AS PortletTypeIsResize, POT.ISPRINT AS PortletTypeIsPrint, POT.ISEXPORT AS PortletTypeIsExport, POT.ISSETTINGSID AS PortletTypeIsSettingsId, POT.ISCHANGESETTING AS PortletTypeIsChangeSettings, PO.URL AS PortletUrl, PO.ISCHANGEABLE AS PortletIsChangeable, PO.MENUID AS PortletMenuId, M.MENUCODE AS MenuCode, M.MENUNAME AS MenuName, CASE M.MENUTYPE WHEN 0 THEN 'Master' WHEN 1 THEN 'Transaction' WHEN 2 THEN 'Reports' WHEN 3 THEN 'Others' WHEN 4 THEN 'Bot' WHEN 5 THEN 'Dashboard' END AS MenuType, M.REPORTID AS MenuReportId, R.REPORTNAME AS ReportName, R.REPORTURI AS ReportUri, M.WEBFORMID AS WebFormId, WF.WEBFORMNAME AS WebFormName, WF.WEBFORMURL AS WebFormURL, WF.TEMPLATE AS WebFormTemplate, PO.ISMAXIMIZE AS PortletIsMaximize, PO.ISRESIZE AS PortletIsResize, PO.ISPRINT AS PortletIsPrint, PO.ISEXPORT AS PortletIsExport, PO.ISSETTINGSID AS PortletIsSettings, PO.ISCHANGESETTING AS PortletIsChangeSetting, PO.SETTINGSID AS PortletSettingsId, PO.PUBLISHCHANNELNAME AS PortletPublishChannelName, PO.PUBLISHFIELDNAME AS PortletPublishFieldName, PO.SUBSCRIBECHANNELNAME AS PortletSubscribeChannelName, PO.SUBSCRIBEFIELDNAME AS PortletSubscribeFieldName, PO.CHARTTYPE AS PortletChartType, PO.CHARTXAXIS AS PortletChartXAxis, PO.CHARTYAXIS AS PortletChartYAxis, PO.CHARTVALUE AS PortletChartValue, PO.XAXISDATEFORMAT AS PortletXaxisDateFormat, PO.YAXISDATEFORMAT AS PortletYaxisDateFormat, PO.REPORTFORMATID AS PortletReportFormatId, RF.REPORTFORMATNAME AS ReportFormatName, RF.REPORTFILE AS ReportFile, RF.DATASOURCEFILENAME AS PortletDataSourceFileName, PO.ISPAGENATIONREQUIRED AS PortletIsPagenationRequired, PO.NUMBEROFRECORDS AS PortletNumberOfRecords, PO.DESCRIPTION AS PortletDescription, UVP.PAGEID AS UserVsPortletPageId, P.PAGECODE AS PageCode, P.PAGENAME AS PageName, UVP.HEIGHT AS UserVsPortletHeight, UVP.WIDTH AS UserVsPortletWidth, UVP.SLNO AS UserVsPortletSlNo, M.BIZTRANSACTIONCLASSID AS BizTransactionClassId, BC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode, BC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName, M.HELPURL AS HelpUrl FROM MUSERVSPORTLET UVP LEFT JOIN MPAGE P ON UVP.PAGEID = P.PAGEID LEFT JOIN MPORTLET PO ON UVP.PORTLETID = PO.PORTLETID LEFT JOIN MPORTLETTYPE POT ON PO.PORTLETTYPEID = POT.PORTLETTYPEID LEFT JOIN MMENU M ON PO.MENUID = M.MENUID LEFT JOIN MREPORT R ON M.REPORTID = R.REPORTID LEFT JOIN MWEBFORM WF ON M.WEBFORMID = WF.WEBFORMID LEFT JOIN MREPORTFORMAT RF ON PO.REPORTFORMATID = RF.REPORTFORMATID LEFT JOIN MBIZTRANSACTIONCLASS BC ON M.BIZTRANSACTIONCLASSID = BC.BIZTRANSACTIONCLASSID WHERE UVP.USERID = @userid AND UVP.PAGEID = @pageid; "; public const string GET_PORTLETLIST_FOR_USER = @"SELECT UVP.USERVSPORTLETID AS UserVsPortletId, UVP.PORTLETID AS UserVsPortletPortletId, PO.PORTLETCODE AS PortletCode, PO.PORTLETNAME AS PortletName, PO.PORTLETTYPEID AS PortletTypeId, POT.PORTLETTYPECODE AS PortletTypeCode, POT.PORTLETTYPENAME AS PortletTypeName, POT.ISMAXIMIZE AS PortletTypeIsMaximize, POT.ISRESIZE AS PortletTypeIsResize, POT.ISPRINT AS PortletTypeIsPrint, POT.ISEXPORT AS PortletTypeIsExport, POT.ISSETTINGSID AS PortletTypeIsSettingsId, POT.ISCHANGESETTING AS PortletTypeIsChangeSettings, PO.URL AS PortletUrl, PO.ISCHANGEABLE AS PortletIsChangeable, PO.MENUID AS PortletMenuId, M.MENUCODE AS MenuCode, M.MENUNAME AS MenuName, CASE M.MENUTYPE WHEN 0 THEN 'Master' WHEN 1 THEN 'Transaction' WHEN 2 THEN 'Reports' WHEN 3 THEN 'Others' WHEN 4 THEN 'Bot' WHEN 5 THEN 'Dashboard' END AS MenuType, M.REPORTID AS MenuReportId, R.REPORTNAME AS ReportName, R.REPORTURI AS ReportUri, M.WEBFORMID AS WebFormId, WF.WEBFORMNAME AS WebFormName, WF.WEBFORMURL AS WebFormURL, WF.TEMPLATE AS WebFormTemplate, PO.ISMAXIMIZE AS PortletIsMaximize, PO.ISRESIZE AS PortletIsResize, PO.ISPRINT AS PortletIsPrint, PO.ISEXPORT AS PortletIsExport, PO.ISSETTINGSID AS PortletIsSettings, PO.ISCHANGESETTING AS PortletIsChangeSetting, PO.SETTINGSID AS PortletSettingsId, PO.PUBLISHCHANNELNAME AS PortletPublishChannelName, PO.PUBLISHFIELDNAME AS PortletPublishFieldName, PO.SUBSCRIBECHANNELNAME AS PortletSubscribeChannelName, PO.SUBSCRIBEFIELDNAME AS PortletSubscribeFieldName, PO.CHARTTYPE AS PortletChartType, PO.CHARTXAXIS AS PortletChartXAxis, PO.CHARTYAXIS AS PortletChartYAxis, PO.CHARTVALUE AS PortletChartValue, PO.XAXISDATEFORMAT AS PortletXaxisDateFormat, PO.YAXISDATEFORMAT AS PortletYaxisDateFormat, PO.REPORTFORMATID AS PortletReportFormatId, RF.REPORTFORMATNAME AS ReportFormatName, RF.REPORTFILE AS ReportFile, RF.DATASOURCEFILENAME AS PortletDataSourceFileName, PO.ISPAGENATIONREQUIRED AS PortletIsPagenationRequired, PO.NUMBEROFRECORDS AS PortletNumberOfRecords, PO.DESCRIPTION AS PortletDescription, UVP.PAGEID AS UserVsPortletPageId, P.PAGECODE AS PageCode, P.PAGENAME AS PageName, UVP.HEIGHT AS UserVsPortletHeight, UVP.WIDTH AS UserVsPortletWidth, UVP.SLNO AS UserVsPortletSlNo, M.BIZTRANSACTIONCLASSID AS BizTransactionClassId, BC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode, BC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName, M.HELPURL AS HelpUrl FROM MUSERVSPORTLET UVP LEFT JOIN MPAGE P ON UVP.PAGEID = P.PAGEID LEFT JOIN MPORTLET PO ON UVP.PORTLETID = PO.PORTLETID LEFT JOIN MPORTLETTYPE POT ON PO.PORTLETTYPEID = POT.PORTLETTYPEID LEFT JOIN MMENU M ON PO.MENUID = M.MENUID LEFT JOIN MREPORT R ON M.REPORTID = R.REPORTID LEFT JOIN MWEBFORM WF ON M.WEBFORMID = WF.WEBFORMID LEFT JOIN MREPORTFORMAT RF ON PO.REPORTFORMATID = RF.REPORTFORMATID LEFT JOIN MBIZTRANSACTIONCLASS BC ON M.BIZTRANSACTIONCLASSID = BC.BIZTRANSACTIONCLASSID WHERE UVP.USERID = @userid; "; // ── Phase 3: Page-level / Dashboard-level filter tiers ────────────── // // Deliberately NOT joined inline into GET_USER_PORTLETLIST_WITH_CRITERIA / // GET_ROLE_PORTLETLIST_WITH_CRITERIA — those queries already join a large // number of tables and adding two more 3-level criteria-config join chains // there would multiply the row count further. Instead PortletDAL resolves // the page/dashboard tier via one small, separate query each (a single // CriteriaConfigId's tree is typically a handful of rows), keyed off the // PageDefaultCriteriaConfigId/PortletOverridePageCriteria/PageOverrideDashboardCriteria // columns added directly above (from tables — PG, P — already in the FROM // clause, so those three columns cost nothing extra). public const string GET_DASHBOARD_DEFAULT_CRITERIA_CONFIG_ID = @" SELECT DEFAULTCRITERIACONFIGID FROM MDASHBOARD WHERE DASHBOARDID = @dashboardid;"; // Same shape as the inline CC/CCS/CCA/MCA/MWSC block above, parameterized // by a single @criteriaconfigid instead of joining transitively via // UVP/RVP — reused for both the Page tier and the Dashboard tier. public const string GET_CRITERIA_CONFIG_TREE_BY_ID = @"SELECT CC.CRITERIACONFIGID AS UserVsPortletCriteriaConfigId, CC.CRITERIACONFIGNAME AS CriteriaConfigName, CC.VERSION AS CriteriaConfigVersion, CCS.CRITERIACONFIGSECTIONID AS CriteriaConfigSectionId, CCS.SLNO AS CriteriaConfigSectionSlNo, CCS.SECTIONJOIN AS CriteriaConfigSectionJoin, CCS.CRITERIACONFIGID AS UserVsPortletCriteriaConfigId, CCA.CRITERIACONFIGATTRIBUTEID AS CriteriaConfigAttributeId, CCA.SLNO AS CriteriaConfigAttributeSlNo, CCA.CRITERIAATTRIBUTEID AS CriteriaAttributeId, MCA.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, MWSC.ISCOMPULSORY AS IsCompulsory, MWSC.ISHEADER AS IsHeader, MCA.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, CCA.OPERATIONTYPE AS CriteriaConfigAttributeOperationType, CCA.CRITERIAFIELDVALUE AS CriteriaConfigAttributeFieldValue, CCA.CRITERIAFIELDVALUEIN AS CriteriaConfigAttributeFieldValueIn, CCA.CRITERIAJOIN AS CriteriaConfigAttributeJoin, CCA.CRITERIAFIELDDISPLAYVALUE AS CriteriaConfigAttributeFieldDisplayValue, CCA.CRITERIAVARIABLEFIELD AS CriteriaConfigAttributeVariableField, MCA.APPLICABLEOPERATION AS ApplicableOperation, MCA.DEFAULTOPERATION AS DefaultOperation FROM TCRITERIACONFIG CC LEFT JOIN TCRITERIACONFIGSECTION CCS ON CC.CRITERIACONFIGID = CCS.CRITERIACONFIGID LEFT JOIN TCRITERIACONFIGATTRIBUTE CCA ON CCS.CRITERIACONFIGSECTIONID = CCA.CRITERIASECTIONID LEFT JOIN MCRITERIAATTRIBUTE MCA ON CCA.CRITERIAATTRIBUTEID = MCA.CRITERIAATTRIBUTEID LEFT JOIN MWEBSERVICECRITERIA MWSC ON MCA.CRITERIAATTRIBUTEID = MWSC.CRITERIAATTRIBUTEID WHERE CC.CRITERIACONFIGID = @criteriaconfigid; "; // Used by PageBLL.SavePage / PortletBLL.SavePortlet / DashboardBLL.SaveDashboard // to validate a supplied DefaultCriteriaConfigId before saving (a pre-existing // gap — this check did not previously exist for MPORTLET.DefaultCriteriaConfigId either). public const string CHECK_CRITERIACONFIG_ACTIVE = @" SELECT COUNT(1) FROM TCRITERIACONFIG WHERE CRITERIACONFIGID = @criteriaconfigid AND STATUS = 1;"; } }