using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FrameworkDAL.Query.ReportViewer { public static class ReportViewQB { public const string GET_SELECTLIST_REPORTVIEW = @"SELECT REPORTVIEWID AS Id, REPORTVIEWNAME AS Name, SOURCETYPE AS SourceType FROM MREPORTVIEW"; public const string GET_SELECTLIST_REPORTVIEW_WITH_FIELD = @"SELECT REPORTVIEWID AS Id, REPORTVIEWNAME AS Name, SOURCETYPE AS SourceType FROM MREPORTVIEW a Where 1=1"; public const string GET_SELECTLIST_REPORTVIEWFIELDS = @" SELECT a.REPORTVIEWFIELDSID as ReportViewFieldsId, a.SLNO as ReportViewFieldsSlNo, d.FIELDNAME as ReportVsFieldsFieldName, a.FIELDTITLE as ReportViewFieldsFieldTitle, a.FIELDWIDTH as ReportViewFieldsFieldWidth, a.ISDISPLAY as ReportViewFieldsIsDisplay, a.ISMERGECOLUMN as ReportViewFieldsIsMergeColumn, a.ISGROUPCOLUMN as ReportViewFieldsIsGroupcolumn, a.ISSUBTOTAL as ReportViewFieldsIsSubTotal, a.ISGRANDTOTAL as ReportViewFieldsIsGrandTotal, a.CALFORMAT as ReportViewFieldsCalFormat, a.DISPLAYSLNO as ReportViewFieldsDisplaySlNo, a.ALIGNMENT as ReportViewFieldsAlignment, a.DISPLAYTYPE as ReportViewFieldsDisplayType, a.AGGREGATIONTYPE as ReportViewFieldsAggregationType, a.ISCOLUMNTOTAL as ReportViewFieldsIsColumnTotal, a.ISROWTOTAL as ReportViewFieldsIsRowTotal, a.PIVOTLEVEL as ReportViewFieldsPivotLevel, a.SUPPRESSIFEMPTY as ReportViewFieldsSuppressIfEmpty, a.DIMENSIONFIELDID as DimensionFieldId, '' as DimensionFieldName, a.REPORTID as ReportId, b.reportname as ReportName, a.REPORTVIEWID as ReportViewId, c.REPORTVIEWNAME as ReportViewName, c.PAGEFORMAT ReportViewPageFormat , c.PAGEORIENTATION as ReportViewPageOrientation, c.ORDERBYFIELDS as ReportViewOrderbyFields, a.REPORTVSFIELDSID as ReportvsfieldsId, c.REPORTVIEWTYPE as ReportViewType, CASE c.REPORTVIEWTYPE WHEN 0 THEN 'Grid' WHEN 1 THEN 'Template' WHEN 2 THEN 'Preview' WHEN 3 THEN 'HTML' WHEN 4 THEN 'Page' END AS ReportViewTypeName, c.SECONDTEMPLATEID AS SecondTemplateId, ISNULL(ST.FILENAME, 'None') AS SecondTemplateName, c.TEMPLATEID AS TemplateId, ISNULL(T.FILENAME, 'None') AS TemplateName, T.FileLocation AS TemplateLocation, ST.FileLocation AS SecondTemplateLocation, d.alignment ReportvsFieldAlignment FROM MREPORTVIEWFIELDS a,mreport b,mreportview c,mreportvsfields d ,mfile t,mfile st WHERE 1=1 and a.REPORTID =b.reportid and a.REPORTVIEWID =c.REPORTVIEWID and a.reportvsfieldsid =d.reportvsfieldsid and b.reportid=d.reportid and a.reportid=d.reportid and a.REPORTID =c.REPORTID and c.TEMPLATEID = t.FILEID and c.SECONDTEMPLATEID = st.FILEID and a.REPORTVIEWID in(:reportviewid) "; // ── CRUD constants added during GB5 migration ───────────────────────── // Single view detail joined to MREPORT for display name public const string GET_REPORTVIEW = @"SELECT RV.APPLICABLEMENUIDS AS ApplicableMenuIds, NULL AS ApplicableMenuIdsName, RV.CLIENTIDS AS ClientIds, '' AS ClientIdsName, 1 AS IsSaveToSystem, P.PAGECODE AS PageCode, RV.PAGEID AS PageId, P.PAGENAME AS PageName, '' AS Password, REPORTHEADERCODE AS ReportHeaderCode, RV.REPORTHEADERID AS ReportHeaderId, REPORTHEADERNAME AS ReportHeaderName, R.REPORTID AS ReportId, R.REPORTNAME AS ReportName, RV.REMARKS AS ReportViewRemarks, RV.COLUMNFREEZE AS ReportViewColumnFreeze, RV.CREATEDBYID AS CreatedById, NULL AS CreatedByName, RV.CREATEDON AS CreatedOn, RV.DYNAMICFOOTER AS DynamicFooter, NULL AS ReportViewFieldsDTOs, RV.REPORTVIEWID AS ReportViewId, RV.ISDATAPIVOT AS ReportViewIsDataPivot, CASE RV.ISDATAPIVOT WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' END AS ReportViewIsDataPivotName, RV.ISDEFAULTVIEW AS IsDefaultView, CASE RV.ISDEFAULTVIEW WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' END AS ReportViewIsDefaultViewName, RV.ISHEADERREQUIRED AS ReportViewIsHeaderRequired, CASE RV.ISHEADERREQUIRED WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' END AS ReportViewIsHeaderRequiredName, RV.ISSYSTEMVIEW AS ReportViewIsSystemView, CASE RV.ISSYSTEMVIEW WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' END AS ReportViewIsSystemViewName, RV.ISVISIBLE AS IsVisible, CASE RV.ISVISIBLE WHEN 0 THEN 'YES' WHEN 1 THEN 'NO' END AS ReportViewIsVisibleName, RV.MODIFIEDBYID AS ModifiedById, NULL AS ModifiedByName, RV.MODIFIEDON AS ModifiedOn, RV.REPORTVIEWNAME AS ReportViewName, RV.ORDERBYFIELDS AS OrderbyFields, RV.PAGEFORMAT AS ReportViewPageFormat, RV.PAGEORIENTATION AS ReportViewPageOrientation, RV.PIVOTFORMAT AS ReportViewPivotFormat, RV.REMARKS AS ReportViewRemarks, RV.REPORTTITLE AS ReportTitle, RV.SORTORDER AS SortOrder, RV.SOURCETYPE AS SourceType, RV.STATUS AS ReportViewStatus, RV.REPORTVIEWTYPE AS ReportViewType, CASE RV.REPORTVIEWTYPE WHEN 0 THEN 'Grid' WHEN 1 THEN 'Template' WHEN 2 THEN 'Preview' WHEN 3 THEN 'HTML' WHEN 4 THEN 'Page' END AS ReportViewTypeName, RV.VERSION AS ReportViewVersion, RV.SECONDTEMPLATEID AS SecondTemplateId, ISNULL(ST.FILENAME, 'None') AS SecondTemplateName, RV.TEMPLATEID AS TemplateId, ISNULL(T.FILENAME, 'None') AS TemplateName, '' AS UserCode FROM MREPORTVIEW RV INNER JOIN MREPORT R ON RV.REPORTID = R.REPORTID LEFT JOIN MREPORTHEADER RH ON RV.REPORTHEADERID = RH.REPORTHEADERID LEFT JOIN MFILE T ON RV.TEMPLATEID = T.FILEID LEFT JOIN MFILE ST ON RV.SECONDTEMPLATEID = ST.FILEID LEFT JOIN MPAGE P ON RV.PAGEID = P.PAGEID WHERE rv.REPORTVIEWID = @ReportViewId"; // All non-deleted views for a report, ordered by sort position public const string GET_REPORTVIEW_LIST = @" SELECT rv.REPORTVIEWID AS ReportViewId, rv.REPORTID AS ReportId, r.REPORTNAME AS ReportName, rv.REPORTVIEWNAME AS ReportViewName, rv.REPORTVIEWTYPE AS ReportViewType, rv.ISSYSTEMVIEW AS ReportViewIsSystemView, rv.ISDEFAULTVIEW AS ReportViewIsDefaultView, rv.APPLICABLEMENUIDS AS ApplicableMenuIds, rv.CLIENTIDS AS ClientIds, rv.PAGEFORMAT AS ReportViewPageFormat, rv.PAGEORIENTATION AS ReportViewPageOrientation, rv.COLUMNFREEZE AS ReportViewColumnFreeze, rv.PIVOTFORMAT AS ReportViewPivotFormat, rv.ISDATAPIVOT AS ReportViewIsDataPivot, rv.ORDERBYFIELDS AS OrderByFields, rv.DYNAMICFOOTER AS DynamicFooter, rv.REPORTTITLE AS ReportTitle, rv.TEMPLATEID AS TemplateId, rv.SECONDTEMPLATEID AS SecondTemplateId, rv.PAGEID AS PageId, rv.REPORTHEADERID AS ReportHeaderId, rv.ISHEADERREQUIRED AS ReportViewIsHeaderRequired, rv.ISVISIBLE AS IsVisible, rv.SOURCETYPE AS ReportViewSourceType, rv.STATUS AS ReportViewStatus, rv.VERSION AS ReportViewVersion, rv.SORTORDER AS SortOrder, rv.CREATEDBYID AS CreatedById, rv.CREATEDON AS CreatedOn, rv.MODIFIEDBYID AS ModifiedById, rv.MODIFIEDON AS ModifiedOn FROM MREPORTVIEW rv JOIN MREPORT r ON r.REPORTID = rv.REPORTID WHERE rv.REPORTID = @ReportId AND rv.STATUS <> 2 ORDER BY rv.SORTORDER, rv.REPORTVIEWNAME"; // Paged list for admin management screens public const string GET_REPORTVIEW_PAGED = @" SELECT rv.REPORTVIEWID AS ReportViewId, rv.REPORTID AS ReportId, r.REPORTNAME AS ReportName, rv.REPORTVIEWNAME AS ReportViewName, rv.REPORTVIEWTYPE AS ReportViewType, rv.ISSYSTEMVIEW AS ReportViewIsSystemView, rv.ISDEFAULTVIEW AS ReportViewIsDefaultView, rv.SOURCETYPE AS ReportViewSourceType, rv.STATUS AS ReportViewStatus, rv.SORTORDER AS SortOrder FROM MREPORTVIEW rv JOIN MREPORT r ON r.REPORTID = rv.REPORTID WHERE rv.REPORTID = @ReportId AND rv.STATUS <> 2 ORDER BY rv.SORTORDER, rv.REPORTVIEWNAME OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY; SELECT COUNT(1) FROM MREPORTVIEW rv WHERE rv.REPORTID = @ReportId AND rv.STATUS <> 2"; // Filter by menu — APPLICABLEMENUIDS is stored as CSV e.g. ',1,2,5,' public const string GET_REPORTVIEW_BY_MENU = @" SELECT rv.REPORTVIEWID AS ReportViewId, rv.REPORTID AS ReportId, r.REPORTNAME AS RepoartName, rv.REMARKS AS ReportViewRemarks, rv.REPORTVIEWNAME AS ReportViewName, rv.REPORTVIEWTYPE AS ReportViewType, rv.ISSYSTEMVIEW AS ReportViewIsSystemView, rv.ISDEFAULTVIEW AS ReportViewIsDefaultView, rv.SOURCETYPE AS ReportViewSourceType, rv.ISDATAPIVOT AS ReportViewIsDataPivot, rv.REPORTHEADERID AS ReportHeaderId, rv.ISHEADERREQUIRED AS ReportViewIsHeaderRequired, rv.PAGEFORMAT AS ReportViewPageFormat, rv.PAGEORIENTATION AS ReportViewPageOrientation, rv.ORDERBYFIELDS AS OrderByFields, rv.PIVOTFORMAT AS ReportViewPivotFormat FROM MREPORTVIEW rv INNER JOIN MREPORT R ON RV.REPORTID = R.REPORTID WHERE rv.REPORTID = @ReportId AND rv.STATUS <> 2 AND ( rv.APPLICABLEMENUIDS = CAST(@MenuId AS VARCHAR(20)) OR rv.APPLICABLEMENUIDS LIKE CAST(@MenuId AS VARCHAR(20)) + ',%' OR rv.APPLICABLEMENUIDS LIKE '%,' + CAST(@MenuId AS VARCHAR(20)) OR rv.APPLICABLEMENUIDS LIKE '%,' + CAST(@MenuId AS VARCHAR(20)) + ',%' ) ORDER BY rv.SORTORDER"; // Lightweight 3-column lookup used by rendering engine public const string GET_REPORTVIEW_LITE = @" SELECT rv.REPORTVIEWID AS ReportViewId, rv.REPORTVIEWNAME AS ReportViewName, rv.ISDATAPIVOT AS ReportViewIsDataPivot FROM MREPORTVIEW rv WHERE rv.REPORTVIEWID = @ReportViewId AND rv.STATUS <> 2"; // Field settings for a view, ordered by display position public const string GET_REPORTVIEW_FIELDS_FOR_VIEW = @" SELECT f.REPORTVIEWFIELDSID AS ReportViewFieldsId, f.REPORTID AS ReportId, r.REPORTNAME AS ReportName, f.REPORTVIEWID AS ReportViewId, rv.REPORTVIEWNAME AS ReportViewName, rv.PAGEFORMAT AS ReportViewPageFormat, rv.PAGEORIENTATION AS ReportViewPageOrientation, rv.ORDERBYFIELDS AS ReportViewOrderbyFields, f.REPORTVSFIELDSID AS ReportVsFieldsId, rvf.FIELDNAME AS ReportVsFieldsFieldName, f.SLNO AS ReportViewFieldsSlNo, f.FIELDTITLE AS ReportViewFieldsFieldTitle, f.FIELDWIDTH AS ReportViewFieldsFieldWidth, f.ISDISPLAY AS ReportViewFieldsIsDisplay, f.ISMERGECOLUMN AS ReportViewFieldsIsMergeColumn, f.ISGROUPCOLUMN AS ReportViewFieldsIsGroupcolumn, f.ISSUBTOTAL AS ReportViewFieldsIsSubTotal, f.ISGRANDTOTAL AS ReportViewFieldsIsGrandTotal, f.CALFORMAT AS ReportViewFieldsCalFormat, f.DISPLAYSLNO AS ReportViewFieldsDisplaySlNo, f.ALIGNMENT AS ReportViewFieldsAlignment, f.DISPLAYTYPE AS ReportViewFieldsDisplayType, f.AGGREGATIONTYPE AS ReportViewFieldsAggregationType, f.ISCOLUMNTOTAL AS ReportViewFieldsIsColumnTotal, f.ISROWTOTAL AS ReportViewFieldsIsRowTotal, f.PIVOTLEVEL AS ReportViewFieldsPivotLevel, f.SUPPRESSIFEMPTY AS ReportViewFieldsSuppressIfEmpty, f.DIMENSIONFIELDID AS DimensionFieldId, '' AS DimensionFieldName, f.ROWLEVEL AS ReportViewFieldsRowLevel, rvf.COMPUTEEXPRESSION AS ReportVsFieldsComputeExpression FROM MREPORTVIEWFIELDS f JOIN MREPORTVIEW rv ON rv.REPORTVIEWID = f.REPORTVIEWID JOIN MREPORT r ON r.REPORTID = f.REPORTID JOIN MREPORTVSFIELDS rvf ON rvf.REPORTVSFIELDSID = f.REPORTVSFIELDSID WHERE f.REPORTVIEWID = @ReportViewId ORDER BY f.DISPLAYSLNO, f.SLNO"; // Duplicate name check: returns 1 if name already taken by another active view public const string CHECK_DUPLICATE_VIEWNAME = @" SELECT COUNT(1) FROM MREPORTVIEW WHERE REPORTVIEWNAME = @Name AND STATUS <> 2 AND (@ReportViewId = 0 OR REPORTVIEWID <> @ReportViewId)"; public const string INSERT_REPORTVIEW = @"INSERT INTO MREPORTVIEW ( REPORTVIEWID, REPORTID, REPORTVIEWNAME, ISSYSTEMVIEW, ISDEFAULTVIEW, SORTORDER, STATUS, VERSION, SOURCETYPE, REMARKS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, REPORTVIEWTYPE, TEMPLATEID, APPLICABLEMENUIDS, PAGEFORMAT, PAGEORIENTATION, COLUMNFREEZE, PIVOTFORMAT, PAGEID, DYNAMICFOOTER, ORDERBYFIELDS, ISDATAPIVOT, CLIENTIDS, ISVISIBLE, REPORTTITLE, REPORTHEADERID, SECONDTEMPLATEID, ISHEADERREQUIRED ) VALUES ( @ReportViewId, @ReportId, @ReportViewName, @ReportViewIsSystemView, @ReportViewIsDefaultView, @SortOrder, @ReportViewStatus, @ReportViewVersion, @ReportViewSourceType, @ReportViewRemarks, @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn, @ReportViewType, @TemplateId, @ApplicableMenuIds, @ReportViewPageFormat, @ReportViewPageOrientation, @ReportViewColumnFreeze, @ReportViewPivotFormat, @PageId, @DynamicFooter, @OrderByFields, @ReportViewIsDataPivot, @ClientIds, @IsVisible, @ReportTitle, @ReportHeaderId, @SecondTemplateId, @ReportViewIsHeaderRequired );"; public const string UPDATE_REPORTVIEW = @"UPDATE MREPORTVIEW SET REPORTID = @ReportId, REPORTVIEWNAME = @ReportViewName, ISSYSTEMVIEW = @ReportViewIsSystemView, ISDEFAULTVIEW = @ReportViewIsDefaultView, SORTORDER = @SortOrder, STATUS = @ReportViewStatus, VERSION = @ReportViewVersion, SOURCETYPE = @ReportViewSourceType, REMARKS = @ReportViewRemarks, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, REPORTVIEWTYPE = @ReportViewType, TEMPLATEID = @TemplateId, APPLICABLEMENUIDS = @ApplicableMenuIds, PAGEFORMAT = @ReportViewPageFormat, PAGEORIENTATION = @ReportViewPageOrientation, COLUMNFREEZE = @ReportViewColumnFreeze, PIVOTFORMAT = @ReportViewPivotFormat, PAGEID = @PageId, DYNAMICFOOTER = @DynamicFooter, ORDERBYFIELDS = @OrderByFields, ISDATAPIVOT = @ReportViewIsDataPivot, CLIENTIDS = @ClientIds, ISVISIBLE = @IsVisible, REPORTTITLE = @ReportTitle, REPORTHEADERID = @ReportHeaderId, SECONDTEMPLATEID = @SecondTemplateId, ISHEADERREQUIRED = @ReportViewIsHeaderRequired WHERE REPORTVIEWID = @ReportViewId;"; // Soft delete — preserves row for audit; STATUS=2 means Deleted public const string SOFT_DELETE_REPORTVIEW = @"UPDATE MREPORTVIEW SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = @ModifiedOn, VERSION = VERSION + 1 WHERE REPORTVIEWID = @ReportViewId"; // Hard delete used only by developer-admin cascade (HardDeleteReportView) public const string HARD_DELETE_REPORTVIEW = @"DELETE FROM MREPORTVIEW WHERE REPORTVIEWID = @ReportViewId"; // Clears the default flag on all other views when one is promoted to default public const string RESET_DEFAULT_VIEW = @" UPDATE MREPORTVIEW SET ISDEFAULTVIEW = 0 WHERE REPORTID = @ReportId AND REPORTVIEWID <> @ReportViewId"; // Wipe all field rows before re-inserting (replace semantics on SaveReportViewWithFields) public const string DELETE_VIEW_FIELDS = @" DELETE FROM MREPORTVIEWFIELDS WHERE REPORTVIEWID = @ReportViewId"; public const string INSERT_REPORTVIEWFIELDS = @" INSERT INTO MREPORTVIEWFIELDS ( REPORTVIEWFIELDSID, REPORTID, REPORTVIEWID, REPORTVSFIELDSID, SLNO, FIELDTITLE, FIELDWIDTH, ISDISPLAY, ISMERGECOLUMN, ISGROUPCOLUMN, ISSUBTOTAL, ISGRANDTOTAL, CALFORMAT, DISPLAYSLNO, ALIGNMENT, DISPLAYTYPE, AGGREGATIONTYPE, ISCOLUMNTOTAL, ISROWTOTAL, PIVOTLEVEL, SUPPRESSIFEMPTY, DIMENSIONFIELDID, ROWLEVEL) VALUES ( @ReportViewFieldsId, @ReportId, @ReportViewId, @ReportVsFieldsId, @ReportViewFieldsSlNo, @ReportViewFieldsFieldTitle, @ReportViewFieldsFieldWidth, @ReportViewFieldsIsDisplay, @ReportViewFieldsIsMergeColumn, @ReportViewFieldsIsGroupcolumn, @ReportViewFieldsIsSubTotal, @ReportViewFieldsIsGrandTotal, @ReportViewFieldsCalFormat, @ReportViewFieldsDisplaySlNo, @ReportViewFieldsAlignment, @ReportViewFieldsDisplayType, @ReportViewFieldsAggregationType, @ReportViewFieldsIsColumnTotal, @ReportViewFieldsIsRowTotal, @ReportViewFieldsPivotLevel, @ReportViewFieldsSuppressIfEmpty, @DimensionFieldId, @ReportViewFieldsRowLevel)"; public const string GET_SELECTLIST_REPORTVSFORMATS = @"SELECT RVF.REPORTVSFORMATSID AS ReportVsFormatsId, RVF.REPORTID AS ReportId, R.REPORTNAME AS ReportName, RVF.SLNO AS SlNo, RF.REPORTFORMATID AS ReportFormatId, RF.REPORTFILE AS ReportFormatReportFile, RF.REPORTFORMATNAME AS ReportFormatName, RF.DATASOURCEFILENAME AS ReportFormatDataSourceFileName, RVF.APPLICABLEMENUIDS AS ApplicableMenuIds, NULL AS ApplicableMenuIdsName, RVF.ISOREFERENCENUMBER AS ReportVsFormatsISOReferenceNumber, RVF.ISVISIBLE AS ReportVsFormatsIsVisible, RVF.DISPLAYNAME AS ReportVsFormatsDisplayName FROM MREPORTVSFORMATS RVF INNER JOIN MREPORT R ON R.REPORTID = RVF.REPORTID INNER JOIN MREPORTFORMAT RF ON RF.REPORTFORMATID = RVF.REPORTFORMATID WHERE RVF.REPORTID = @reportid AND RVF.APPLICABLEMENUIDS LIKE '%,' + CAST(@applicablemenuid AS NVARCHAR(20)) + '%' AND R.STATUS = 1 AND RF.STATUS = 1 ORDER BY RVF.SLNO;"; } }