private void SaveDefinitionForAttribute(MetaReportAttributeModel model, MetaReport metaReport)
{
try
{
// 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 = model.ReportType.ToString();
rootNode.Attributes.Append(attrib);
attrib = meta.CreateAttribute("CoreEntity");
attrib.InnerText = model.CoreEntity.ToString();
rootNode.Attributes.Append(attrib);
if (model.ReportType == 2)
{
mainNode = meta.CreateElement("Summary", ns);
foreach (MetaReportAttributeModel.ListItem strat in model.StratifyItems)
{
subNode = meta.CreateElement("SummaryItem", ns);
attrib = meta.CreateAttribute("MetaColumnId");
attrib.InnerText = strat.MetaColumnId.ToString();
subNode.Attributes.Append(attrib);
attrib = meta.CreateAttribute("DisplayName");
attrib.InnerText = strat.DisplayName;
subNode.Attributes.Append(attrib);
attrib = meta.CreateAttribute("AttributeName");
attrib.InnerText = strat.AttributeName;
subNode.Attributes.Append(attrib);
mainNode.AppendChild(subNode);
}
rootNode.AppendChild(mainNode);
}
else
{
mainNode = meta.CreateElement("List", ns);
foreach (MetaReportAttributeModel.ListItem list in model.ListItems)
{
subNode = meta.CreateElement("ListItem", ns);
attrib = meta.CreateAttribute("MetaColumnId");
attrib.InnerText = list.MetaColumnId.ToString();
subNode.Attributes.Append(attrib);
attrib = meta.CreateAttribute("DisplayName");
attrib.InnerText = list.DisplayName;
subNode.Attributes.Append(attrib);
attrib = meta.CreateAttribute("AttributeName");
attrib.InnerText = list.AttributeName;
subNode.Attributes.Append(attrib);
mainNode.AppendChild(subNode);
}
rootNode.AppendChild(mainNode);
}
mainNode = meta.CreateElement("Filter", ns);
foreach (MetaReportAttributeModel.FilterItem filter in model.FilterItems)
{
subNode = meta.CreateElement("FilterItem", ns);
attrib = meta.CreateAttribute("MetaColumnId");
attrib.InnerText = filter.MetaColumnId.ToString();
subNode.Attributes.Append(attrib);
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 (model.ReportType == 2)
{
sql = PrepareSummaryQueryForPublication(model);
}
else
{
sql = PrepareListQueryForPublication(model);
}
metaReport.SQLDefinition = sql;
_unitOfWork.Repository <MetaReport>().Update(metaReport);
_unitOfWork.Complete();
}
catch (Exception ex)
{
ModelState.AddModelError("ReportName", String.Format("<li>ERROR: {0}...</li>", ex.Message));
}
}
private void ExtractMetaDataForAttribute(MetaReport metaReport, MetaReportAttributeModel model)
{
int tempi;
XmlDocument meta = new XmlDocument();
meta.LoadXml(metaReport.MetaDefinition);
// Unpack structures
XmlNode rootNode = meta.SelectSingleNode("//MetaReport");
XmlAttribute typeAttr = rootNode.Attributes["Type"];
XmlAttribute entityAttr = rootNode.Attributes["CoreEntity"];
var coreId = entityAttr != null?int.TryParse(entityAttr.Value, out tempi) ? Convert.ToInt32(entityAttr.Value) : 0 : 0;
model.CoreEntity = coreId;
model.CoreEntityDisplay = _unitOfWork.Repository <MetaTable>().Queryable().Single(mt => mt.Id == coreId).TableName;
var typeId = typeAttr != null?int.TryParse(typeAttr.Value, out tempi) ? Convert.ToInt32(typeAttr.Value) : 0 : 0;
model.ReportType = typeId;
model.ReportTypeDisplay = typeId == 1 ? "List" : "Summary";
XmlNode mainNode;
// List or summary
if (typeId == 1)
{
mainNode = rootNode.SelectSingleNode("//List");
if (mainNode != null)
{
foreach (XmlNode subNode in mainNode.ChildNodes)
{
MetaReportAttributeModel.ListItem list = new MetaReportAttributeModel.ListItem();
list.MetaColumnId = Convert.ToInt32(subNode.Attributes["MetaColumnId"].Value);
list.AttributeName = subNode.Attributes.GetNamedItem("AttributeName").Value;
list.DisplayName = subNode.Attributes.GetNamedItem("DisplayName").Value;
model.ListItems.Add(list);
}
}
}
else
{
mainNode = rootNode.SelectSingleNode("//Summary");
if (mainNode != null)
{
foreach (XmlNode subNode in mainNode.ChildNodes)
{
MetaReportAttributeModel.ListItem strat = new MetaReportAttributeModel.ListItem();
strat.MetaColumnId = Convert.ToInt32(subNode.Attributes["MetaColumnId"].Value);
strat.AttributeName = subNode.Attributes.GetNamedItem("AttributeName").Value;
strat.DisplayName = subNode.Attributes.GetNamedItem("DisplayName").Value;
model.StratifyItems.Add(strat);
}
}
}
// filter
mainNode = rootNode.SelectSingleNode("//Filter");
if (mainNode != null)
{
foreach (XmlNode subNode in mainNode.ChildNodes)
{
MetaReportAttributeModel.FilterItem filter = new MetaReportAttributeModel.FilterItem();
filter.MetaColumnId = Convert.ToInt32(subNode.Attributes["MetaColumnId"].Value);
filter.AttributeName = subNode.Attributes.GetNamedItem("AttributeName").Value;
filter.Operator = subNode.Attributes.GetNamedItem("Operator").Value;
filter.Relation = subNode.Attributes.GetNamedItem("Relation").Value;
model.FilterItems.Add(filter);
}
}
}
public ActionResult RemoveMetaColumn(long metaReportId, long metaColumnId, Models.ViewType viewType)
{
ViewBag.MenuItem = CurrentMenuItem;
var metaReport = _unitOfWork.Repository <MetaReport>()
.Queryable()
.SingleOrDefault(r => r.Id == metaReportId);
var model = new MetaReportAttributeModel()
{
MetaReportId = metaReport.Id,
ReportDefinition = metaReport.ReportDefinition,
ReportName = metaReport.ReportName,
ViewType = viewType
};
ExtractMetaDataForAttribute(metaReport, model);
XmlDocument meta = new XmlDocument();
meta.LoadXml(metaReport.MetaDefinition);
// Get node to be removed
XmlNode rootNode = meta.SelectSingleNode("//MetaReport");
XmlNode typeNode = null;
XmlNode removeNode = null;
switch (viewType)
{
case Models.ViewType.List:
typeNode = rootNode.SelectSingleNode("//List");
removeNode = typeNode.SelectSingleNode(String.Format("ListItem[@MetaColumnId='{0}']", metaColumnId));
typeNode.RemoveChild(removeNode);
break;
case Models.ViewType.Summary:
typeNode = rootNode.SelectSingleNode("//Summary");
removeNode = typeNode.SelectSingleNode(String.Format("SummaryItem[@MetaColumnId='{0}']", metaColumnId));
typeNode.RemoveChild(removeNode);
break;
case Models.ViewType.Filter:
typeNode = rootNode.SelectSingleNode("//Filter");
removeNode = typeNode.SelectSingleNode(String.Format("FilterItem[@MetaColumnId='{0}']", metaColumnId));
typeNode.RemoveChild(removeNode);
break;
default:
break;
}
metaReport.MetaDefinition = meta.InnerXml;
string sql = string.Empty;
if (model.ReportType == 2)
{
sql = PrepareSummaryQueryForPublication(model);
}
else
{
sql = PrepareListQueryForPublication(model);
}
metaReport.SQLDefinition = sql;
_unitOfWork.Repository <MetaReport>().Update(metaReport);
_unitOfWork.Complete();
HttpCookie cookie = new HttpCookie("PopUpMessage");
cookie.Value = "Column removed successfully";
Response.Cookies.Add(cookie);
return(Redirect("/Reports/ReportAttributeItem?metaReportId=" + metaReportId.ToString() + "&viewType=" + viewType.ToString()));
}
public ActionResult ReportAttributeItem(MetaReportAttributeModel model)
{
ViewBag.MenuItem = CurrentMenuItem;
switch (model.ViewType)
{
case Models.ViewType.List:
if (!String.IsNullOrWhiteSpace(model.DisplayForList))
{
if (Regex.Matches(model.DisplayForList, @"[a-zA-Z ']").Count < model.DisplayForList.Length)
{
ModelState.AddModelError("DisplayForList", "Display contains invalid characters(Enter A-Z, a-z, space).");
}
}
break;
case Models.ViewType.Summary:
if (!String.IsNullOrWhiteSpace(model.DisplayForSummary))
{
if (Regex.Matches(model.DisplayForSummary, @"[a-zA-Z ']").Count < model.ReportName.Length)
{
ModelState.AddModelError("DisplayForSummary", "Display contains invalid characters(Enter A-Z, a-z, space).");
}
}
break;
case Models.ViewType.Filter:
if (model.MetaColumnForFilterId == 0)
{
ModelState.AddModelError("MetaColumnForFilterId", "Column must be selected.");
}
if (String.IsNullOrWhiteSpace(model.Operator))
{
ModelState.AddModelError("Operator", "Operator must be selected.");
}
if (String.IsNullOrWhiteSpace(model.Relation))
{
ModelState.AddModelError("Relation", "Relation must be selected.");
}
break;
default:
break;
}
if (ModelState.IsValid)
{
var metaReport = _unitOfWork.Repository <MetaReport>()
.Queryable()
.SingleOrDefault(r => r.Id == model.MetaReportId);
ExtractMetaDataForAttribute(metaReport, model);
// Add new item to relevant list
switch (model.ViewType)
{
case Models.ViewType.List:
if (model.MetaColumnForListId > 0)
{
MetaReportAttributeModel.ListItem list = new MetaReportAttributeModel.ListItem();
list.MetaColumnId = model.MetaColumnForListId;
list.AttributeName = _unitOfWork.Repository <MetaColumn>().Queryable().Single(mc => mc.Id == model.MetaColumnForListId).ColumnName;
list.DisplayName = String.IsNullOrWhiteSpace(model.DisplayForList) ? list.AttributeName : model.DisplayForList;
model.ListItems.Add(list);
}
// Now save final definition
SaveDefinitionForAttribute(model, metaReport);
break;
case Models.ViewType.Summary:
if (model.MetaColumnForSummaryId > 0)
{
MetaReportAttributeModel.ListItem strat = new MetaReportAttributeModel.ListItem();
strat.MetaColumnId = model.MetaColumnForSummaryId;
strat.AttributeName = _unitOfWork.Repository <MetaColumn>().Queryable().Single(mc => mc.Id == model.MetaColumnForSummaryId).ColumnName;
strat.DisplayName = String.IsNullOrWhiteSpace(model.DisplayForSummary) ? strat.AttributeName : model.DisplayForSummary;
model.StratifyItems.Add(strat);
}
// Now save final definition
SaveDefinitionForAttribute(model, metaReport);
break;
case Models.ViewType.Filter:
if (model.MetaColumnForFilterId > 0)
{
MetaReportAttributeModel.FilterItem filter = new MetaReportAttributeModel.FilterItem();
filter.MetaColumnId = model.MetaColumnForFilterId;
filter.AttributeName = _unitOfWork.Repository <MetaColumn>().Queryable().Single(mc => mc.Id == model.MetaColumnForFilterId).ColumnName;
filter.Operator = model.Operator.ToString();
filter.Relation = model.Relation.ToString();
model.FilterItems.Add(filter);
}
// Now save final definition
SaveDefinitionForAttribute(model, metaReport);
break;
default:
break;
}
HttpCookie cookie = new HttpCookie("PopUpMessage");
cookie.Value = "Column added successfully";
Response.Cookies.Add(cookie);
return(Redirect("/Reports/ReportAttributeItem?metaReportId=" + model.MetaReportId.ToString() + "&viewType=" + model.ViewType.ToString()));
}
IOrderedQueryable <MetaColumn> metaColumns = _unitOfWork.Repository <MetaColumn>().Queryable()
.Where(mc => mc.Table.Id == model.CoreEntity)
.OrderBy(mc => mc.ColumnName);
List <SelectListItem> listMetaColumns = new List <SelectListItem>();
// Add new item to relevant list
switch (model.ViewType)
{
case Models.ViewType.List:
foreach (MetaColumn metaColumn in metaColumns)
{
// ensure not selected
if (!model.ListItems.Any(li => li.MetaColumnId == metaColumn.Id))
{
listMetaColumns.Add(new SelectListItem()
{
Text = metaColumn.ColumnName, Value = metaColumn.Id.ToString()
});
}
}
if (listMetaColumns.Count == 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- ALL COLUMNS ASSIGNED --", Value = "0"
});
}
break;
case Models.ViewType.Summary:
foreach (MetaColumn metaColumn in metaColumns)
{
// ensure not selected
if (!model.StratifyItems.Any(li => li.MetaColumnId == metaColumn.Id))
{
listMetaColumns.Add(new SelectListItem()
{
Text = metaColumn.ColumnName, Value = metaColumn.Id.ToString()
});
}
}
if (listMetaColumns.Count == 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- ALL COLUMNS ASSIGNED --", Value = "0"
});
}
break;
case Models.ViewType.Filter:
if (metaColumns.Count() > 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- Please select a column --", Value = "0"
});
foreach (MetaColumn metaColumn in metaColumns)
{
// ensure not selected
if (!model.FilterItems.Any(li => li.MetaColumnId == metaColumn.Id))
{
listMetaColumns.Add(new SelectListItem()
{
Text = metaColumn.ColumnName, Value = metaColumn.Id.ToString()
});
}
}
}
if (listMetaColumns.Count == 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- ALL COLUMNS ASSIGNED --", Value = "0"
});
}
break;
default:
break;
}
ViewBag.MetaColumns = listMetaColumns;
ViewBag.Relationships = new[]
{
new SelectListItem {
Value = "And", Text = "And", Selected = true
},
new SelectListItem {
Value = "Or", Text = "Or"
}
};
ViewBag.Operators = new[]
{
new SelectListItem {
Value = "", Text = "-- Please select an operator --", Selected = true
}
};
return(View(model));
}
public ActionResult ReportAttributeItem(long metaReportId, Models.ViewType viewType)
{
ViewBag.MenuItem = CurrentMenuItem;
ViewBag.PopUpMessage = PreparePopUpMessage();
var metaReport = _unitOfWork.Repository <MetaReport>()
.Queryable()
.SingleOrDefault(r => r.Id == metaReportId);
var model = new MetaReportAttributeModel()
{
MetaReportId = metaReport.Id,
ReportDefinition = metaReport.ReportDefinition,
ReportName = metaReport.ReportName,
ViewType = viewType
};
if (metaReport != null)
{
ExtractMetaDataForAttribute(metaReport, model);
IOrderedQueryable <MetaColumn> metaColumns = _unitOfWork.Repository <MetaColumn>().Queryable()
.Where(mc => mc.Table.Id == model.CoreEntity)
.OrderBy(mc => mc.ColumnName);
List <SelectListItem> listMetaColumns = new List <SelectListItem>();
// Add new item to relevant list
switch (model.ViewType)
{
case Models.ViewType.List:
foreach (MetaColumn metaColumn in metaColumns)
{
// ensure not selected
if (!model.ListItems.Any(li => li.MetaColumnId == metaColumn.Id))
{
listMetaColumns.Add(new SelectListItem()
{
Text = metaColumn.ColumnName, Value = metaColumn.Id.ToString()
});
}
}
if (listMetaColumns.Count == 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- ALL COLUMNS ASSIGNED --", Value = "0"
});
}
break;
case Models.ViewType.Summary:
foreach (MetaColumn metaColumn in metaColumns)
{
// ensure not selected
if (!model.StratifyItems.Any(li => li.MetaColumnId == metaColumn.Id))
{
listMetaColumns.Add(new SelectListItem()
{
Text = metaColumn.ColumnName, Value = metaColumn.Id.ToString()
});
}
}
if (listMetaColumns.Count == 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- ALL COLUMNS ASSIGNED --", Value = "0"
});
}
break;
case Models.ViewType.Filter:
if (metaColumns.Count() > 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- Please select a column --", Value = "0"
});
foreach (MetaColumn metaColumn in metaColumns)
{
// ensure not selected
if (!model.FilterItems.Any(li => li.MetaColumnId == metaColumn.Id))
{
listMetaColumns.Add(new SelectListItem()
{
Text = metaColumn.ColumnName, Value = metaColumn.Id.ToString()
});
}
}
}
if (listMetaColumns.Count == 0)
{
listMetaColumns.Add(new SelectListItem()
{
Text = "-- ALL COLUMNS ASSIGNED --", Value = "0"
});
}
break;
default:
break;
}
ViewBag.MetaColumns = listMetaColumns;
}
ViewBag.Relationships = new[]
{
new SelectListItem {
Value = "And", Text = "And", Selected = true
},
new SelectListItem {
Value = "Or", Text = "Or"
}
};
ViewBag.Operators = new[]
{
new SelectListItem {
Value = "", Text = "-- Please select an operator --", Selected = true
}
};
ViewData.Model = model;
return(View());
}
private string PrepareSummaryQueryForPublication(MetaReportAttributeModel model)
{
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.Id == model.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.Id == model.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 (MetaReportAttributeModel.ListItem strat in model.StratifyItems)
{
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 (MetaReportAttributeModel.FilterItem filter in model.FilterItems)
{
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);
}
