using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace GB5Shared.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, 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_ADDRESS_FOR_REPORT = @" SELECT A.ADDRESSID AS AddressId, A.OBJECTTYPEID AS AddressObjectTypeId, A.OBJECTID AS AddressObjectId, A.ADDRESSTYPE AS AddressAddressType, A.ADDRESSNATURE AS AddressAddressNature, A.ADDRESSLINE1 AS AddressLine1, A.ADDRESSLINE2 AS AddressLine2, A.ADDRESSLINE3 AS AddressLine3, A.ADDRESSLINE4 AS AddressLine4, A.ADDRESSLINE5 AS AddressLine5, A.ZIPCODE AS AddressZipCode, A.PHONE AS AddressPhone, A.MOBILE AS AddressMobile, A.FAX AS AddressFax, A.WEB AS AddressWeb, A.GEOCODE AS AddressGeoCode, A.MAIL AS AddressMail, A.GSTIN AS AddressGSTIn, A.PAN AS AddressPAN, A.TAN AS AddressTAN, A.VATTINNUMBER AS AddressVATTINNumber, A.VATCSTNUMBER AS AddressVATCSTNumber, A.SERVICETAXNUMBER AS AddressServiceTaxNumber, A.TDSCIRCLE AS AddressTDSCircle, A.EXCISEDIVISION AS AddressExciseDivision, A.EXCISERANGE AS AddressExciseRange, A.EXCISECODENUMBER AS AddressExciseCodeNumber, A.EXCISENOTIFICATION AS AddressExciseNotification, A.EXCISECIRCLE AS AddressExciseCircle, A.EXCISECOLLECTRATE AS AddressExciseCollectrate, A.EXCISEREGISTERNUMBER AS AddressExciseRegisterNumber, A.SORTORDER AS AddressSortOrder, A.VERSION AS AddressVersion, A.STATUS AS AddressStatus, A.SOURCETYPE AS AddressSourceType, S.STATEID AS StateId, S.STATECODE AS StateCode, S.STATENAME AS StateName, S.GSTSTATECODE AS StateGSTStateCode, S.TINSTART AS StateTINStart, C.CITYID AS CityId, C.CITYCODE AS CityCode, C.CITYNAME AS CityName, CO.COUNTRYID AS CountryId, CO.COUNTRYCODE AS CountryCode, CO.COUNTRYNAME AS CountryName, CO.ISDCODE AS CountryISDCode, L.LOCATIONID AS LocationId, L.LOCATIONCODE AS LocationCode, L.LOCATIONNAME AS LocationName FROM MADDRESS A LEFT JOIN MSTATE S ON S.STATEID = A.STATEID LEFT JOIN MCITY C ON C.CITYID = A.CITYID LEFT JOIN MCOUNTRY CO ON CO.COUNTRYID = A.COUNTRYID LEFT JOIN MLOCATION L ON L.LOCATIONID = A.LOCATIONID WHERE A.OBJECTTYPEID = @ObjectTypeId AND A.OBJECTID = @ObjectId ORDER BY A.SORTORDER;"; 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 MCLIENT 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, PAGEFORMAT AS ReportViewPageFormat, PAGEORIENTATION AS ReportViewPageOrientation 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;"; // Computed/derived fields for a view -- MREPORTVSFIELDS rows with a non-empty // COMPUTEEXPRESSION, joined through MREPORTVIEWFIELDS so only fields actually configured // on THIS view are returned (a computed field needs an MREPORTVIEWFIELDS row to be // rendered at all, so scoping by view rather than by report costs nothing extra). Consumed // by GB5Shared/Export/ComputedFieldCatalog.cs -- see ComputedFieldEvaluator.cs for the // evaluation engine. public const string GET_COMPUTED_FIELDS_BY_REPORTVIEW = @" SELECT MRF.FIELDNAME AS FieldName, MRF.FIELDTYPE AS FieldType, MRF.COMPUTEEXPRESSION AS Expression, MR.DISPLAYSLNO AS DisplaySlNo FROM MReportViewFields MR, MREPORTVSFIELDS MRF WHERE MR.REPORTVIEWID = @ReportViewId AND MR.REPORTVSFIELDSID = MRF.REPORTVSFIELDSID AND MRF.COMPUTEEXPRESSION IS NOT NULL AND MRF.COMPUTEEXPRESSION <> '' ORDER BY MR.DISPLAYSLNO;"; } }