public async Task <IActionResult> UpdateMetaReportAttributes(long id, [FromBody] MetaReportForAttributeUpdateDto metaReportForAttributeUpdate) { if (metaReportForAttributeUpdate == null) { ModelState.AddModelError("Message", "Unable to locate payload for new request"); } var metaReportFromRepo = await _metaReportRepository.GetAsync(f => f.Id == id); if (metaReportFromRepo == null) { return(NotFound()); } if (ModelState.IsValid) { PrepareMetaDefinitionForAttribute(metaReportForAttributeUpdate, metaReportFromRepo); _metaReportRepository.Update(metaReportFromRepo); await _unitOfWork.CompleteAsync(); return(Ok()); } return(BadRequest(ModelState)); }
/// <summary> /// Prepare the sql query needed to produce the summary report /// </summary> /// <param name="metaReportForAttributeUpdate">The meta report attribute payload</param> /// <returns></returns> private string PrepareSummaryQueryForPublication(MetaReportForAttributeUpdateDto metaReportForAttributeUpdate) { string sql = ""; string fcriteria = ""; // from string jcriteria = ""; // joins string scriteria = ""; // selects string gcriteria = ""; // groups string ocriteria = ""; // orders string wcriteria = ""; // wheres var metaTable = _unitOfWork.Repository <MetaTable>() .Queryable() .SingleOrDefault(mt => mt.TableName == metaReportForAttributeUpdate.CoreEntity); // FROM switch ((MetaTableTypes)metaTable.TableType.Id) { case MetaTableTypes.Core: fcriteria = "[Meta" + metaTable.TableName + "] P"; break; case MetaTableTypes.CoreChild: fcriteria = "[Meta" + metaTable.TableName + "] C"; break; case MetaTableTypes.Child: fcriteria = "[Meta" + metaTable.TableName + "] P"; break; case MetaTableTypes.History: fcriteria = "[Meta" + metaTable.TableName + "] C"; break; default: break; } // JOINS MetaDependency metaDependency; switch ((MetaTableTypes)metaTable.TableType.Id) { case MetaTableTypes.Child: case MetaTableTypes.Core: // do nothing break; case MetaTableTypes.History: case MetaTableTypes.CoreChild: // get parent metaDependency = _unitOfWork.Repository <MetaDependency>() .Queryable() .SingleOrDefault(md => md.ReferenceTable.TableName == metaReportForAttributeUpdate.CoreEntity); jcriteria += String.Format(" LEFT JOIN [Meta{0}] P ON P.{1} = C.{2} ", metaDependency.ParentTable.TableName, metaDependency.ParentColumnName, metaDependency.ReferenceColumnName); break; } // FIELDS var fc = 0; foreach (var strat in metaReportForAttributeUpdate.Attributes) { fc += 1; scriteria += "cast(" + strat.AttributeName + " as varchar)" + " as 'Col" + fc.ToString() + "', "; gcriteria += strat.AttributeName + ", "; ocriteria += strat.AttributeName + ", "; } scriteria = !String.IsNullOrWhiteSpace(scriteria) ? scriteria.Substring(0, scriteria.Length - 2) : ""; gcriteria = !String.IsNullOrWhiteSpace(gcriteria) ? gcriteria.Substring(0, ocriteria.Length - 2) : ""; ocriteria = !String.IsNullOrWhiteSpace(ocriteria) ? ocriteria.Substring(0, ocriteria.Length - 2) : ""; // FILTERS var filc = 0; foreach (var filter in metaReportForAttributeUpdate.Filters) { filc += 1; wcriteria += String.Format("{0} ({1} {2} %{3})", filter.Relation, filter.AttributeName, filter.Operator, filc.ToString()); } sql = String.Format(@" select {0}, CAST(COUNT(*) as varchar) AS Col{6} from {4} {1} where 1 = 1 {5} GROUP BY {2} ORDER BY {3} ", scriteria, jcriteria, gcriteria, ocriteria, fcriteria, wcriteria, (fc + 1).ToString()); return(sql); }
/// <summary> /// Prepare the sql query needed to produce the list report /// </summary> /// <param name="metaReportForAttributeUpdate">The meta report attribute payload</param> /// <returns></returns> private string PrepareListQueryForPublication(MetaReportForAttributeUpdateDto metaReportForAttributeUpdate) { string sql = ""; string fcriteria = ""; // from string jcriteria = ""; // joins string scriteria = ""; // selects string ocriteria = ""; // orders string wcriteria = ""; // wheres var metaTable = _metaTableRepository.Get(mt => mt.TableName == metaReportForAttributeUpdate.CoreEntity, new string[] { "TableType" }); // FROM switch ((MetaTableTypes)metaTable.TableType.Id) { case MetaTableTypes.Core: fcriteria = "[Meta" + metaTable.TableName + "] P"; break; case MetaTableTypes.CoreChild: fcriteria = "[Meta" + metaTable.TableName + "] C"; break; case MetaTableTypes.Child: fcriteria = "[Meta" + metaTable.TableName + "] P"; break; case MetaTableTypes.History: fcriteria = "[Meta" + metaTable.TableName + "] C"; break; default: break; } // JOINS MetaDependency metaDependency; switch ((MetaTableTypes)metaTable.TableType.Id) { case MetaTableTypes.Child: case MetaTableTypes.Core: // do nothing break; case MetaTableTypes.History: case MetaTableTypes.CoreChild: // get parent metaDependency = _metaDependencyRepository.Get(md => md.ReferenceTable.TableName == metaReportForAttributeUpdate.CoreEntity, new string[] { "ParentTable" }); jcriteria += String.Format(" LEFT JOIN [Meta{0}] P ON P.{1} = C.{2} ", metaDependency.ParentTable.TableName, metaDependency.ParentColumnName, metaDependency.ReferenceColumnName); break; } // FIELDS var fc = 0; foreach (var list in metaReportForAttributeUpdate.Attributes) { fc += 1; scriteria += "cast(" + list.AttributeName + " as varchar)" + " as 'Col" + fc.ToString() + "', "; ocriteria += list.AttributeName + ", "; } scriteria = !String.IsNullOrWhiteSpace(scriteria) ? scriteria.Substring(0, scriteria.Length - 2) : ""; ocriteria = !String.IsNullOrWhiteSpace(ocriteria) ? ocriteria.Substring(0, ocriteria.Length - 2) : ""; // FILTERS fc = 0; foreach (var filter in metaReportForAttributeUpdate.Filters) { fc += 1; wcriteria += String.Format("{0} ({1} {2} %{3})", filter.Relation, filter.AttributeName, filter.Operator, fc.ToString()); } sql = String.Format(@" select {0} from {3} {1} where 1 = 1 {4} ORDER BY {2} ", scriteria, jcriteria, ocriteria, fcriteria, wcriteria); return(sql); }
/// <summary> /// Prepare an XML definition containing details of the report (including attributes) /// </summary> /// <param name="metaReportForAttributeUpdate">The meta report attribute payload</param> /// <param name="metaReport">The meta report being updated</param> /// <returns></returns> private void PrepareMetaDefinitionForAttribute(MetaReportForAttributeUpdateDto metaReportForAttributeUpdate, MetaReport metaReport) { // Prepare XML XmlDocument meta = new XmlDocument(); var ns = ""; // urn:pvims-org:v3 XmlNode rootNode = null; XmlNode mainNode = null; XmlNode subNode = null; XmlAttribute attrib; XmlDeclaration xmlDeclaration = meta.CreateXmlDeclaration("1.0", "UTF-8", null); meta.AppendChild(xmlDeclaration); rootNode = meta.CreateElement("MetaReport", ns); attrib = meta.CreateAttribute("Type"); attrib.InnerText = metaReportForAttributeUpdate.ReportType.ToString(); rootNode.Attributes.Append(attrib); attrib = meta.CreateAttribute("CoreEntity"); attrib.InnerText = metaReportForAttributeUpdate.CoreEntity.ToString(); rootNode.Attributes.Append(attrib); if (metaReportForAttributeUpdate.ReportType == MetaReportTypes.Summary) { mainNode = meta.CreateElement("Summary", ns); foreach (var attribute in metaReportForAttributeUpdate.Attributes) { subNode = meta.CreateElement("SummaryItem", ns); attrib = meta.CreateAttribute("DisplayName"); attrib.InnerText = attribute.DisplayName; subNode.Attributes.Append(attrib); attrib = meta.CreateAttribute("Aggregate"); attrib.InnerText = attribute.Aggregate; subNode.Attributes.Append(attrib); attrib = meta.CreateAttribute("AttributeName"); attrib.InnerText = attribute.AttributeName; subNode.Attributes.Append(attrib); mainNode.AppendChild(subNode); } rootNode.AppendChild(mainNode); } else { mainNode = meta.CreateElement("List", ns); foreach (var attribute in metaReportForAttributeUpdate.Attributes) { subNode = meta.CreateElement("ListItem", ns); attrib = meta.CreateAttribute("DisplayName"); attrib.InnerText = attribute.DisplayName; subNode.Attributes.Append(attrib); attrib = meta.CreateAttribute("Aggregate"); attrib.InnerText = string.Empty; subNode.Attributes.Append(attrib); attrib = meta.CreateAttribute("AttributeName"); attrib.InnerText = attribute.AttributeName; subNode.Attributes.Append(attrib); mainNode.AppendChild(subNode); } rootNode.AppendChild(mainNode); } mainNode = meta.CreateElement("Filter", ns); foreach (var filter in metaReportForAttributeUpdate.Filters) { subNode = meta.CreateElement("FilterItem", ns); attrib = meta.CreateAttribute("AttributeName"); attrib.InnerText = filter.AttributeName; subNode.Attributes.Append(attrib); attrib = meta.CreateAttribute("Operator"); attrib.InnerText = filter.Operator; subNode.Attributes.Append(attrib); attrib = meta.CreateAttribute("Relation"); attrib.InnerText = filter.Relation; subNode.Attributes.Append(attrib); mainNode.AppendChild(subNode); } rootNode.AppendChild(mainNode); meta.AppendChild(rootNode); metaReport.MetaDefinition = meta.InnerXml; string sql = string.Empty; if (metaReportForAttributeUpdate.ReportType == MetaReportTypes.Summary) { sql = PrepareSummaryQueryForPublication(metaReportForAttributeUpdate); } else { sql = PrepareListQueryForPublication(metaReportForAttributeUpdate); } metaReport.SqlDefinition = sql; }