public AdhocCategoryReportCreation(AdhocReportQueryData response) { int classificationId = OracleHelper.GetClassificationID(response.ClassificationName); int classificationUnionType = OracleHelper.GetClassificationIsUnion(response.ClassificationName); if (classificationId != 0) { if (classificationUnionType == 0) { this.Levels = OracleHelper.GetCategoryLevelsAllData(classificationId); } else { AdhocUnionReportCreation UnionReport = new AdhocUnionReportCreation(response); this.ReportData = UnionReport.ReportData; } } if (this.Levels != null) { string JoinType = GetJoinType(response.JoinType); if (JoinType != null) { this.graphTraversal = new GraphTraversal(this.Levels, JoinType); AddEffectedTablesToGraph(response); string query = GenerateReportQuery(response); this.ReportData = OracleHelper.ExecuteAdhocReportQuery(query, response.SelectClauses); } } }
public AdhocUnionReportCreation(AdhocReportQueryData response) { int classificationId = _context.GetClassificationID(response.ClassificationName); if (classificationId != 0) { this.Categories = _context.GetClassificationCategories(response.ClassificationName); if (this.Categories != null) { GenerateAllQueryData(response); ParsingReportData(); } } }
private void AddEffectedTablesToGraph(AdhocReportQueryData response) { List <string> tables = new List <string>(); foreach (SelectClause selectedLevel in response.SelectClauses) { tables.Add(selectedLevel.TableName); } foreach (WhereClause filterLevel in response.WhereClauses) { tables.Add(filterLevel.TableName); } this.graphTraversal.AddTablesToGraph(tables); }
private void GenerateAllQueryData(AdhocReportQueryData response) { foreach (CategoryModelView cat in Categories) { int subClassificationId = _context.GetClassificationID(cat.Name); this.Levels = _context.GetCategoryLevelsAllData(subClassificationId); AdhocReportQueryData subResponse = new AdhocReportQueryData(); subResponse.ClassificationName = cat.Name; subResponse.SelectClauses = GetSelectClauses(response.SelectClauses); subResponse.WhereClauses = GetWhereClauses(response.WhereClauses); AdhocCategoryReportCreation subData = null; if (subResponse.SelectClauses != null) { subData = new AdhocCategoryReportCreation(subResponse); } if (subData != null) { if (subData.ReportData != null) { subReportData.Add(subData.ReportData); } } } }
private string GenerateReportQuery(AdhocReportQueryData response) { string join = ""; List <string> junctionTables = this.graphTraversal.GenerateJoinClause(ref join); string query = "SELECT DISTINCT "; bool firstSelect = true; foreach (SelectClause select in response.SelectClauses) { if (firstSelect) { query += (select.TableName + "." + select.ColumnName + " "); firstSelect = false; } else { query += (" , " + select.TableName + "." + select.ColumnName + " "); } } query += " FROM " + join; bool firstWhere = true; if (response.WhereClauses != null) { foreach (WhereClause where in response.WhereClauses) { if (firstWhere == true) { query += (" WHERE " + where.TableName + "." + where.ColumnName + " IN ( "); firstWhere = false; } else { query += (" AND " + where.TableName + "." + where.ColumnName + " IN ( "); } bool InValueFirstTime = true; string InValues = ""; foreach (string value in where.Values) { if (InValueFirstTime) { InValues += (" '" + value + "' "); InValueFirstTime = false; } else { InValues += ", '" + value + "'"; } } query += (InValues + " ) "); } } if (junctionTables != null && junctionTables.Count > 0) { foreach (string table in junctionTables) { if (firstWhere == true) { query += (" WHERE " + table + ".RETIRE_DATE IS NULL "); firstWhere = false; } else { query += (" AND " + table + ".RETIRE_DATE IS NULL "); } } } return(query); }