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;
}
