using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace AnalyticsDAL.Query.Analysis { public static class AnalysisQB { public const string GET_ANALYSIS_ID = @"SELECT A.ANALYSISID AS AnalysisId, A.ANALYSISCODE AS AnalysisCode, A.ANALYSISNAME AS AnalysisName, A.DESCRIPTION AS AnalysisDescription, A.BASEDBOBJECTID AS BaseObjectId, A.SOURCETYPE AS AnalysisSourceType, A.SORTORDER AS AnalysisSortOrder, A.STATUS AS AnalysisStatus, A.VERSION AS AnalysisVersion, A.CREATEDBYID AS AnalysisCreatedById, A.CREATEDON AS AnalysisCreatedOn, A.MODIFIEDBYID AS AnalysisModifiedById, A.MODIFIEDON AS AnalysisModifiedOn, A.WORKSPACEID AS WorkspaceId FROM MANALYSIS A WHERE A.ANALYSISID = @analysisid AND A.STATUS <> 2; "; // Lightweight lookup so AnalysisBLL.DynamicOutput can resolve an execution route // (AnalysisExecutionRouteResolver needs an AnalysisId) before calling into the DAL's // full DynamicOutput, without duplicating GET_ANALYSISQUERY's whole column set. public const string GET_ANALYSISID_FOR_QUERY = @" SELECT ANALYSISID FROM MANALYSISQUERY WHERE ANALYSISQUERYID = @AnalysisQueryId"; public const string DELETE_ANALYSIS = @" UPDATE MANALYSIS SET STATUS = 2, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE() WHERE ANALYSISID = @AnalysisId AND TENANTID = @TenantId"; // Unscoped by design (no TENANTID filter) — used only by the ownership visibility // guard; see WorkspaceQB.GET_WORKSPACE_TENANTID for why a tenant-scoped query can't be // reused here (it would never find a shared TENANTID = -1 analysis for another caller). public const string GET_ANALYSIS_TENANTID = @" SELECT A.TENANTID FROM MANALYSIS A WHERE A.ANALYSISID = @AnalysisId AND A.STATUS <> 2"; public const string GET_SELECTLIST_ANALYSIS = @"WITH PagedAnalysis AS ( SELECT MANALYSIS.ANALYSISID AS Id, MANALYSIS.ANALYSISCODE AS Code, MANALYSIS.ANALYSISNAME AS Name, ROW_NUMBER() OVER (ORDER BY MANALYSIS.ANALYSISID) AS RowNum FROM MANALYSIS ) SELECT Id, Code, Name FROM PagedAnalysis WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_ANALYSISQUERY = @"SELECT ANALYSISQUERYID AS AnalysisQueryId, ANALYSISID AS AnalysisId, QUERYNAME AS AnalysisQueryName, QUERYTYPE AS AnalysisQueryType, QUERY AS AnalysisQuery, ORDERBY AS AnalysisQueryOrderBy FROM mAnalysisQuery where ANALYSISQUERYID =@analysisqueryid"; public const string GET_ANALYSISQUERY_FIELDS = @"SELECT a.ANALYSISID AS AnalysisId, b.SLNO AS SlNo, b.DISPLAYSLNO AS DisplaySlNo, b.DISPLAYTYPE AS DisplayType, b.FILTERCONDITION AS FilterCondition, c.DBFIELDNAME AS DbFieldName, e.FILTEREXPRESSION AS Filterexpression, e.VARIABLES AS Variables, -- case when e.FieldType=1 then e.Expression else c.EXPRESSION end AS Exression, CASE WHEN Isnull(VV.DIMENSIONFIELDID, -1)= -1 THEN CASE e.FieldType WHEN 0 THEN case when g.PARENTOBJECTID=-1 then c.EXPRESSION else REPLACE( c.EXPRESSION,H.DBOBJECTNAME+'.',G.DBOBJECTNAME+'.') end -- Data WHEN 1 THEN e.Expression ELSE e.Expression END ELSE VV.Expression END AS Exression, e.FieldType as FieldType, -- case when e.FieldType=1 then e.DisplayName else c.DISPLAYNAME end AS DisplayName b.DisplayName AS DisplayName, b.AGGREGATIONTYPE AS AggregationType, b.PERIODTYPE AS Periodtype, f.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, ISNULL(a.ORDERBY,'') AS AnalysisQueryOrderBy FROM MANALYSISQUery a, MANALYSISQUERYFIELDS b OUTER apply(SELECT reportviewfield.DIMENSIONFIELDID, dd.DBOBJECTNAME + '.' + cc.DBFIELDNAME AS Expression FROM MREPORTVIEWFIELDS reportviewfield, MREPORTVSFIELDS reportvsfield, DBOBJECTFIELDS cc, DBOBJECT dd WHERE reportviewfield.REPORTVIEWID =@reportviewid AND reportviewfield.REPORTVSFIELDSID = reportvsfield.REPORTVSFIELDSID AND reportvsfield.FIELDNAME = b.DISPLAYNAME AND cc.DBOBJECTID = dd.DBOBJECTID AND reportviewfield.DIMENSIONFIELDID = cc.DBOBJECTFIELDID) VV, DBOBJECTFIELDS c, MANALYSIS d, MANALYSISFIELDS e, MCRITERIAATTRIBUTE f, MANALYSISOBJECT obj, DBOBJECT g, DBOBJECT h WHERE 1 = 1 AND a.ANALYSISQUERYID = b.ANALYSISQUERYID AND e.DBOBJECTFIELDID = c.DBOBJECTFIELDID AND a.ANALYSISID = d.ANALYSISID AND a.ANALYSISID = e.ANALYSISID AND b.ANALYSISFIELDID = e.ANALYSISFIELDID AND a.ANALYSISQUERYID = @analysisqueryid and b.CRITERIAATTRIBUTEID=f.CRITERIAATTRIBUTEID and e.ANALYSISOBJECTID=OBJ.ANALYSISOBJECTID and OBJ.DBOBJECTID=g.DBOBJECTID and g.PARENTOBJECTID=h.DBOBJECTID ORDER BY b.DISPLAYSLNO ASC" + "\r\n"; public const string GET_ANALYSISFILTER_FIELDS = @"SELECT d.ANALYSISID AS AnalysisId, a.SLNO AS SlNo, a.DISPLAYSLNO AS DisplaySlNo, -- c.Expression AS FilterCondition, -- c.DBFIELDNAME AS DbFieldName, case when e.FieldType=1 then e.Expression else c.EXPRESSION end AS FilterCondition, a.DisplayName AS DbFieldName , e.FILTEREXPRESSION AS Filterexpression, e.VARIABLES AS Variables, f.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType, f.CRITERIAATTRIBUTENAME AS CriteriaAttributeName FROM MANALYSISQUeryFields a, MANALYSISQUery b, DBOBJECTFIELDS c, MANALYSIS d, MANALYSISFIELDS e, MCRITERIAATTRIBUTE f WHERE 1 = 1 AND a.ANALYSISQUERYID = b.ANALYSISQUERYID AND b.Analysisid = d.Analysisid AND a.AnalysisFieldId = e.AnalysisFieldId AND e.DBOBJECTFIELDID = c.DBOBJECTFIELDID AND a.ANALYSISQUERYID = @analysisqueryid AND d.Analysisid = e.Analysisid and a.CRITERIAATTRIBUTEID=f.CRITERIAATTRIBUTEID and (a.ISFILTER=0 or a.DISPLAYTYPE=4) ORDER BY a.DISPLAYSLNO ASC" + "\r\n"; public const string FINAL_DYNAMIC_QUERY = "select *:otherfields from #dynamicanalysisQuery"; public const string GET_DBJOIN_BASED_ON_FROMDBOBJECTID = @"SELECT JOINID AS DBJoinId, FROMDBOBJECTID AS FromDBObjectId, TODBOBJECTID AS ToObjectId, DESCRIPTION AS DBJoinDescription, RELATIONTYPE AS DBJoinRelationType, JOINTYPE AS DBJoinJoinType, EXPRESSION AS DBJoinExpression, INDIRECTFROMDBOBJECTID AS IndirectFromDbobjectId, INDIRECTTODBOBJECTID AS IndirectToDbobjectId, SOURCETYPE AS DBJoinSourceType, SORTORDER AS DBJoinSortOrder, STATUS AS DBJoinStatus, VERSION AS DBJoinVersion, CREATEDBYID AS DBJoinCreatedById, CREATEDON AS DBJoinCreatedOn, MODIFIEDBYID AS DBJoinModifiedById, MODIFIEDON AS DBJoinModifiedOn FROM DBJOIN WHERE FROMDBOBJECTID IN @DbObjectIds OR TODBOBJECTID IN @DbObjectIds"; public const string GET_REPORTVIEW_VISIBLE_FIELDS_BASED_ON_REPORTVIEWID = @" 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 FROM MReportViewFields MR ,MREPORTVSFIELDS MRF WHERE MR.REPORTVIEWID =@ReportViewId AND MR.REPORTVSFIELDSID = MRF.REPORTVSFIELDSID AND MR.ISDISPLAY = 0 ORDER BY MR.DISPLAYSLNO;"; public const string GENERATE_DYNAMIC_QUERY = @" SELECT @dynamicselect into #dynamicanalysisQuery FROM @dynamicfrom WHERE 1 = 1 @dynamiccondition @dynamicfiltercondition @groupby @groupvalues" + "\r\n"; public const string GET_TABLE_DETAILS_BASED_ON_ANALYSISID_WITH_SAME_TABLE_MULTIPLE_TIME = @"SELECT b.DBOBJECTNAME as DBObjectName, b.DBOBJECTID as DBObjectId, ISNULL(a.FILTEREXPRESSION,'') as FilterExpression, c.DBOBJECTID as ParentObjectId, c.DBOBJECTNAME as ParentObjectName FROM MANALYSISOBJECT a, DBOBJECT b, DBOBJECT c WHERE 1 = 1 AND ANALYSISID =@analysisid AND a.DBOBJECTID = b.DBOBJECTID AND b.PARENTOBJECTID=c.DBOBJECTID ORDER BY SLNO" + "\r\n"; public const string SAVE_ANALYSIS = @" INSERT INTO MANALYSIS ( ANALYSISID, ANALYSISCODE, ANALYSISNAME, DESCRIPTION, BASEDBOBJECTID, VERSION, STATUS, SOURCETYPE, SORTORDER, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON ) VALUES ( @AnalysisId, @AnalysisCode, @AnalysisName, @AnalysisDescription, @BaseObjectId, @AnalysisVersion, @AnalysisStatus, @AnalysisSourceType, @AnalysisSortOrder, @AnalysisCreatedById, @AnalysisCreatedOn, @AnalysisModifiedById, @AnalysisModifiedOn );"; public const string UPDATE_ANALYSIS = @" UPDATE MANALYSIS SET ANALYSISCODE = @AnalysisCode, ANALYSISNAME = @AnalysisName, DESCRIPTION = @AnalysisDescription, BASEDBOBJECTID = @BaseObjectId, VERSION = @AnalysisVersion, STATUS = @AnalysisStatus, SOURCETYPE = @AnalysisSourceType, SORTORDER = @AnalysisSortOrder, MODIFIEDBYID = @AnalysisModifiedById, MODIFIEDON = @AnalysisModifiedOn WHERE ANALYSISID = @AnalysisId;"; public const string GET_ANALYSIS_OBJECTS = @"SELECT AO.ANALYSISOBJECTID AS AnalysisObjectId, AO.ANALYSISID AS AnalysisId, A.ANALYSISCODE AS AnalysisCode, A.ANALYSISNAME AS AnalysisName, AO.SLNO AS AnalysisObjectSlNo, AO.DBOBJECTID AS DBObjectId, DB.DBOBJECTNAME AS DBObjectName, AO.FILTEREXPRESSION AS AnalysisObjectFilterExpression, AO.VARIABLES AS AnalysisObjectVariables FROM MANALYSISOBJECT AO LEFT JOIN MANALYSIS A ON AO.ANALYSISID = A.ANALYSISID LEFT JOIN DBOBJECT DB ON AO.DBOBJECTID = DB.DBOBJECTID WHERE AO.ANALYSISID = @AnalysisId"; public const string GET_ANALYSIS_FIELDS = @"SELECT AF.ANALYSISFIELDID AS AnalysisFieldsId, AF.ANALYSISID AS AnalysisId, A.ANALYSISCODE AS AnalysisCode, A.ANALYSISNAME AS AnalysisName, AF.SLNO AS AnalysisFieldsSlNo, AF.DBOBJECTFIELDID AS DBObjectFieldsId, DBF.DBFIELDNAME AS DBObjectFieldsName, DB.DBOBJECTID AS DBObjectId, DB.DBOBJECTNAME AS DBObjectName, AF.FILTEREXPRESSION AS AnalysisFieldsFilterExpression, AF.VARIABLES AS AnalysisFieldsVariables, AF.FIELDTYPE AS AnalysisFieldsFieldType, AF.EXPRESSION AS AnalysisFieldsExpression, AF.DISPLAYNAME AS AnalysisFieldsDisplayName, AF.SORTORDER AS AnalysisFieldsSortOrder, AF.SOURCETYPE AS AnalysisFieldsSourceType, AF.STATUS AS AnalysisFieldsStatus, AF.VERSION AS AnalysisFieldsVersion, AF.CREATEDBYID AS AnalysisFieldsCreatedById, AF.CREATEDON AS AnalysisFieldsCreatedOn, AF.MODIFIEDBYID AS AnalysisFieldsModifiedById, AF.MODIFIEDON AS AnalysisFieldsModifiedOn, CU.USERNAME AS AnalysisFieldsCreatedByName, MU.USERNAME AS AnalysisModifiedByName, AF.ANALYSISOBJECTID AS AnalysisObjectId, AF.CRITERIAATTRIBUTEID AS CriteriaAttributeId, CA.CRITERIAATTRIBUTENAME AS CriteriaAttributeName, CASE WHEN AF.CRITERIAATTRIBUTEID > 0 THEN CAST(1 AS TINYINT) ELSE CAST(0 AS TINYINT) END AS IsFilter, AF.TENANTID AS AnalysisFieldsTenantId, AF.WORKSPACEID AS AnalyifledsWorkSpaceId FROM MANALYSISFIELDS AF LEFT JOIN MANALYSIS A ON AF.ANALYSISID = A.ANALYSISID LEFT JOIN DBOBJECTFIELDS DBF ON AF.DBOBJECTFIELDID = DBF.DBOBJECTFIELDID LEFT JOIN DBOBJECT DB ON DBF.DBOBJECTID = DB.DBOBJECTID LEFT JOIN MCRITERIAATTRIBUTE CA ON AF.CRITERIAATTRIBUTEID = CA.CRITERIAATTRIBUTEID LEFT JOIN MUSER CU ON AF.CREATEDBYID = CU.USERID LEFT JOIN MUSER MU ON AF.MODIFIEDBYID = MU.USERID WHERE AF.ANALYSISID = @AnalysisId"; } }