using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace FrameworkDAL.Query.Report { public static class ReportQB { public const string GETREPORTFORMAT = @"SELECT REPORTFORMATID AS ReportFormatId, REPORTFILE AS ReportFile, REPORTFORMATNAME AS ReportFormatName, DATASOURCEFILENAME AS DataSourceFileName, PURPOSE AS Purpose, MODULEID AS ModuleId, WEBSERVICEID AS WebServiceId, REPPATH AS RepPath, REMARKS AS Remarks, SORTORDER AS SortOrder, STATUS AS Status, VERSION AS Version, CREATEDBYID AS CreatedById, CREATEDON AS CreatedOn, MODIFIEDBYID AS ModifiedById, MODIFIEDON AS ModifiedOn, ISPRINTLOGO AS IsPrintLogo, REPORTFORMATCODE AS ReportFormatCode, SECTION AS Section, SOURCETYPE AS SourceType, ISOREFERENCENUMBER AS IsoReferenceNumber, CLIENTID AS ClientId, SECTIONID AS SectionId, DISPLAYFILENAME AS DisplayFileName FROM MREPORTFORMAT WHERE REPORTFORMATID = @ReportFormatId; "; public const string GETREPORT = @"SELECT REPORTID AS ReportId, REPORTNAME AS ReportName, REPORTTYPE AS ReportType, MODULEID AS ModuleId, REPORTURI AS ReportUri, REMARKS AS Remarks, PURPOSE AS Purpose, WEBSERVICEID AS WebServiceId, DEFAULTREPORTFORMATID AS DefaultReportFormatId, SORTORDER AS SortOrder, STATUS AS Status, VERSION AS Version, SOURCETYPE AS SourceType, CREATEDBYID AS CreatedById, CREATEDON AS CreatedOn, MODIFIEDBYID AS ModifiedById, MODIFIEDON AS ModifiedOn, REPORTVIEWTYPE AS ReportViewType, REPORTCODE AS ReportCode, SECTION AS Section, ISDYNAMICFIELD AS IsDynamicField, SECTIONID AS SectionId, SECONDWEBSERVICEID AS SecondWebServiceId FROM MREPORT WHERE REPORTID = @ReportId; "; public const string GETREPORTVIEW = @"SELECT a.REPORTHEADERID AS ReportHeaderId, c.ISHEADERREQUIRED AS IsHeaderRequired, c.REPORTVIEWTYPE AS ReportViewType, b.SLNO AS SlNo, b.FROMROWNUMBER AS FromRowNumber, b.TOROWNUMBER AS ToRowNumber, b.ROWHEIGHT AS RowHeight, b.FROMCOLUMNNUMBER AS FromColumnNumber, b.TOCOLUMNNUMBER AS ToColumnNumber, b.CELLWIDTH AS CellWidth, b.EXPRESSION AS Expression, b.ALIGNMENT AS Alignment, b.FONT AS Font, b.FONTSIZE AS FontSize, b.COLOR AS Color, b.STYLE AS Style, b.ISEMPTYSUPPRESS AS IsEmptySuppress FROM Mreportheader a INNER JOIN Mreportheaderdetail b ON a.REPORTHEADERID = b.REPORTHEADERID INNER JOIN Mreportview c ON a.REPORTHEADERID = c.REPORTHEADERID WHERE c.Reportviewid = @reportviewid AND a.REPORTHEADERID <> -1 ORDER BY b.SLNO;"; public const string GET_STANDARD_FIELDS_FOR_REPORT = @"SELECT c.COMPANYID AS CompanyId, c.COMPANYCODE AS CompanyCode, c.COMPANYNAME AS CompanyName, c.COMPANYLOGO AS CompanyLogo1, c.COMPANYLOGO2 AS CompanyLogo2, c.CINNUMBER AS CINNumber, c.IMPORTEXPORTCODE AS ImportExportCode, c.MSMEREGNO AS MSMERegNo, b.BRANCHID AS BranchId, b.BRANCHCODE AS BranchCode, b.BRANCHNAME AS BranchName, d.DIVISIONID AS DivisionId, d.DIVISIONCODE AS DivisionCode, d.DIVISIONNAME AS DivisionName, ou.OUID AS OuId, ou.ORGANIZATIONUNITCODE AS OuCode, ou.ORGANIZATIONUNITSHORTNAME AS OuName, ou.OULOGO1 AS OULogo1, ou.OULOGO2 AS OULogo2, ou.DISPLAYNAME AS OUDisplayName, ou.ORGANIZATIONUNITSHORTNAME AS OUShortName, REPLACE(COALESCE(ppbr_lf.FILELOCATION, pbr_lf.FILELOCATION, ''), '{BaseURI}', @baseuri) AS PartnerLogo1 FROM MOrganizationUnit ou INNER JOIN MCompany c ON c.COMPANYID = ou.CompanyId INNER JOIN MBranch b ON b.BRANCHID = ou.BranchId INNER JOIN MDivision d ON d.DIVISIONID = ou.DivisionId LEFT JOIN MCLIENTDETAILS cd ON cd.CLIENTID = @clientid LEFT JOIN TPARTNERPRODUCT pp ON pp.PARTNERPRODUCTID = cd.PARTNERPRODUCTID LEFT JOIN TPARTNER prt ON prt.PARTNERID = pp.PARTNERID LEFT JOIN TPARTNERBRAND pbr ON pbr.PARTNERID = prt.PARTNERID LEFT JOIN TPARTNERPRODUCTBRAND ppbr ON ppbr.PARTNERPRODUCTID = pp.PARTNERPRODUCTID LEFT JOIN MFILE pbr_lf ON pbr_lf.FILEID = pbr.LOGOFILEID LEFT JOIN MFILE ppbr_lf ON ppbr_lf.FILEID = ppbr.LOGOFILEID WHERE ou.OUID = @ouid;"; public const string GET_REPORT_VIEW_META = @" SELECT REPORTVIEWID AS ReportViewId, REPORTVIEWTYPE AS ReportViewType, ISDATAPIVOT AS IsDataPivot, ISHEADERREQUIRED AS IsHeaderRequired, REPORTHEADERID AS ReportHeaderId FROM MREPORTVIEW WHERE REPORTVIEWID = @ReportViewId;"; public const string GET_PIVOT_FIELD_CONFIG = @" SELECT MR.REPORTVSFIELDSID AS ReportVsFieldsId, MRF.FIELDNAME AS FieldName, MR.FIELDTITLE AS FieldTitle, MRF.FIELDTYPE AS FieldType, MR.DISPLAYTYPE AS DisplayType, MR.AGGREGATIONTYPE AS AggregationType, MR.PIVOTLEVEL AS PivotLevel, MR.ISGRANDTOTAL AS IsGrandTotal, MR.ISCOLUMNTOTAL AS IsColumnTotal, MR.ISROWTOTAL AS IsRowTotal, MR.SUPPRESSIFEMPTY AS SuppressIfEmpty, MR.DISPLAYSLNO AS DisplaySlNo FROM MREPORTVIEWFIELDS MR INNER JOIN MREPORTVSFIELDS MRF ON MRF.REPORTVSFIELDSID = MR.REPORTVSFIELDSID WHERE MR.REPORTVIEWID = @ReportViewId AND MR.ISDISPLAY = 0 ORDER BY MR.DISPLAYSLNO;"; public const string GET_REPORT_VIEW_FIELD = @" SELECT MR.REPORTVIEWFIELDSID AS ReportViewFieldsId, MR.REPORTID AS ReportId, MR.REPORTVIEWID AS ReportViewId, MR.REPORTVSFIELDSID AS ReportVsFieldsId, MRF.FIELDNAME as ReportVsFieldsFieldName, MR.SLNO AS ReportViewFieldsSlNo, MR.FIELDTITLE AS ReportViewFieldsFieldTitle, MRF.FieldType as ReportVsFieldsFieldType, MR.FIELDWIDTH AS ReportViewFieldsFieldWidth, MR.ISDISPLAY AS ReportViewFieldsIsDisplay, MR.ISMERGECOLUMN AS ReportViewFieldsIsMergeColumn, MR.ISGROUPCOLUMN AS ReportViewFieldsIsGroupcolumn, MR.ISSUBTOTAL AS ReportViewFieldsIsSubTotal, MR.ISGRANDTOTAL AS ReportViewFieldsIsGrandTotal, MR.CALFORMAT AS ReportViewFieldsCalFormat, MR.DISPLAYSLNO AS ReportViewFieldsDisplaySlNo, MR.ALIGNMENT AS ReportViewFieldsAlignment, MR.DISPLAYTYPE AS ReportViewFieldsDisplayType, MR.AGGREGATIONTYPE AS ReportViewFieldsAggregationType, MR.ISCOLUMNTOTAL AS ReportViewFieldsIsColumnTotal, MR.ISROWTOTAL AS ReportViewFieldsIsRowTotal, MR.PIVOTLEVEL AS ReportViewFieldsPivotLevel, MR.SUPPRESSIFEMPTY AS ReportViewFieldsSuppressIfEmpty, MR.DIMENSIONFIELDID AS DimensionFieldId, MR.ROWLEVEL AS ReportViewFieldsRowLevel, MRF.COMPUTEEXPRESSION AS ReportVsFieldsComputeExpression FROM MReportViewFields MR ,MREPORTVSFIELDS MRF WHERE MR.REPORTVIEWID =@ReportViewId AND MR.REPORTVSFIELDSID = MRF.REPORTVSFIELDSID AND (@FieldIsDisplay = 1 OR MR.ISDISPLAY = 0) ORDER BY MR.DISPLAYSLNO;"; } }