public void RemoveModel(ReportModel model)
{
foreach (ReportView view in Views)
{
checkRemoveModel(view, model);
}
if (Models.Count == 1) throw new Exception("Unable to remove the model: The report must contain at least one Model.");
Models.Remove(model);
}
void checkRemoveModel(ReportView view, ReportModel model)
{
foreach (var childView in view.Views)
{
if (view.Views.Exists(i => i.ModelGUID == model.GUID)) throw new Exception(string.Format("The model '{0}' is already used by a view.", model.Name));
checkRemoveModel(childView, model);
}
}
private void buildTables(ReportModel model)
{
ResultCell[] headerPageValues = GetHeaderCells(PivotPosition.Page, model);
ResultCell[] headerRowValues = GetHeaderCells(PivotPosition.Row, model);
ResultCell[] headerColumnValues = GetHeaderCells(PivotPosition.Column, model);
ResultCell[] headerDataValues = GetHeaderCells(PivotPosition.Data, model);
if (headerDataValues.Length == 0 && headerRowValues.Length > 0 && headerColumnValues.Length > 0) Report.LogMessage("WARNING for Model '{0}': Row and Column elements are set but no Data element is specified. Please add a Data element in your model.", model.Name);
//Summary table headers
model.SummaryTable = new ResultTable();
model.SummaryTable.Lines.Add(headerPageValues);
foreach (ResultPage page in model.Pages)
{
if (Report.Cancel) break;
//Summary table values
model.SummaryTable.Lines.Add(page.Pages);
//Page table
page.PageTable = new ResultTable();
page.PageTable.Lines.Add(headerPageValues);
page.PageTable.Lines.Add(page.Pages);
//Data table
page.DataTable = new ResultTable();
//Calculate line width
int width = headerRowValues.Length + Math.Max(headerColumnValues.Length, headerDataValues.Length * Math.Max(1, page.Columns.Count));
ResultCell[] line;
//First line, only if column values
if (headerColumnValues.Length > 0)
{
line = new ResultCell[width];
if (headerDataValues.Length == 1) line[0] = headerDataValues[0]; //Case 1 Data, title in first cell
for (int i = 0; i < headerColumnValues.Length; i++) line[headerRowValues.Length + i] = headerColumnValues[i];
//case cols, no rows, one data, add data title
if (headerColumnValues.Length > 0 && headerRowValues.Length == 0 && headerDataValues.Length == 1 && headerColumnValues.Length < width) line[headerColumnValues.Length] = headerDataValues[0];
//Fill empty cells
for (int i = 0; i < width; i++) if (line[i] == null) line[i] = new ResultCell() { IsTitle = true };
page.DataTable.Lines.Add(line);
}
//Intermediate lines, set columns values
for (int i = 0; i < headerColumnValues.Length + 1; i++)
{
line = new ResultCell[width];
if (i < headerColumnValues.Length)
{
//column values
for (int j = 0; j < page.Columns.Count; j++) line[headerRowValues.Length + headerDataValues.Length * j] = page.Columns[j][i];
}
else
{
//headers for rows
for (int j = 0; j < headerRowValues.Length; j++) line[j] = headerRowValues[j];
//headers for data
for (int j = 0; j < Math.Max(1, page.Columns.Count); j++)
{
int index = headerDataValues.Length * j;
foreach (var header in headerDataValues) line[headerRowValues.Length + index++] = header;
}
}
if (headerColumnValues.Length > 0 && headerDataValues.Length <= 1 && i == headerColumnValues.Length - 1)
{
//Case 1 Data with at least 1 column, or no data with 1 row and a cloumn, one line less as the titles is already set in first cell
//headers for rows
for (int j = 0; j < headerRowValues.Length; j++) line[j] = headerRowValues[j];
i++;
}
//Fill empty cells
for (int j = 0; j < width; j++) if (line[j] == null) line[j] = new ResultCell() { IsTitle = (j < headerRowValues.Length) };
page.DataTable.Lines.Add(line);
}
//Set start row and column
page.DataTable.BodyStartRow = page.DataTable.Lines.Count;
page.DataTable.BodyStartColumn = headerRowValues.Length;
//Finally row and data values
if (page.Rows.Count == 0 && page.Datas.Count > 0)
{
//Case no column, force one row to display data
page.Rows.Add(new ResultCell[0]);
}
foreach (var row in page.Rows)
{
if (Report.Cancel) break;
line = new ResultCell[width];
//Row values
for (int i = 0; i < row.Length; i++) line[i] = row[i];
//Data values
List<ResultData> datas = null;
if (row.Length == 0 && page.Datas.Count > 0)
{
//Case no rows
datas = new List<ResultData>();
foreach (var data0 in page.Datas) datas.AddRange(data0.Value);
}
//normal case
else if (page.Datas.ContainsKey(row)) datas = page.Datas[row];
if (datas != null)
{
foreach (var data in datas)
{
//find the index of the column values
int columnIndex = 0;
if (data.Column.Length > 0) columnIndex = FindDimension(data.Column, page.Columns);
for (int i = 0; i < data.Data.Length; i++) line[headerRowValues.Length + headerDataValues.Length * columnIndex + i] = data.Data[i];
}
}
page.DataTable.Lines.Add(line);
for (int i = 0; i < width; i++) if (line[i] == null) line[i] = new ResultCell() { };
}
//Set end row
page.DataTable.BodyEndRow = page.DataTable.Lines.Count;
}
}
private void buildTotals(ReportModel model)
{
var colTotalElements = model.GetElements(PivotPosition.Data).Where(e => e.ShowTotal == ShowTotal.Column || e.ShowTotal == ShowTotal.RowColumn || e.CalculationOption == CalculationOption.PercentageColumn);
var rowTotalElements = model.GetElements(PivotPosition.Data).Where(e => e.ShowTotal == ShowTotal.Row || e.ShowTotal == ShowTotal.RowColumn || e.CalculationOption == CalculationOption.PercentageRow || e.CalculationOption == CalculationOption.PercentageAll);
var totalElements = colTotalElements.Union(rowTotalElements);
Dictionary<string, string> compilationKeys = new Dictionary<string, string>();
foreach (ResultPage page in model.Pages)
{
if (Report.Cancel) break;
//First calculate the total of total cells for each element
page.DataTable.TotalCells = new List<ResultTotalCell>();
foreach (var element in totalElements)
{
ResultTotalCell totalTotalCell = new ResultTotalCell() { Element = element, IsTotal = true, IsTotalTotal = true };
foreach (var rowLine in page.DataTable.Lines)
{
foreach (var cell in rowLine.Where(i => i.Element == element && !i.IsTitle && !i.IsTotal))
{
totalTotalCell.Cells.Add(cell);
}
}
totalTotalCell.Calculate();
page.DataTable.TotalCells.Add(totalTotalCell);
}
//Totals per columns
if (colTotalElements.Count() > 0 && page.DataTable.Lines.Count > 0)
{
//We add first one/several final lines (one per element)
ResultCell[] totalLine = new ResultCell[page.DataTable.Lines[0].Length];
for (int i = 0; i < page.DataTable.Lines[0].Length; i++)
{
if (Report.Cancel) break;
foreach (var element in colTotalElements)
{
ResultTotalCell totalCell = new ResultTotalCell() { Element = element, IsTotal = true, IsTotalTotal = true };
for (int j = 0; j < page.DataTable.Lines.Count; j++)
{
ResultCell cell = page.DataTable.Lines[j][i];
if (cell != null && !cell.IsTitle && element == cell.Element) totalCell.Cells.Add(cell);
}
totalCell.Calculate();
if (element.ShowTotal == ShowTotal.Column || element.ShowTotal == ShowTotal.RowColumn)
{
//Add titles if not a value
if (totalCell.Cells.Count == 0)
{
if (i == 0)
{
totalCell.IsTitle = true;
totalCell.Value = Report.Translate("Total");
}
}
totalLine[i] = totalCell;
}
//Handle calculation
if (!totalCell.IsTitle)
{
if (element.CalculationOption == CalculationOption.PercentageColumn)
{
foreach (ResultCell cell in totalCell.Cells) cell.Value = cell.DoubleValue / totalCell.DoubleValue;
totalCell.Value = 1;
}
}
//This cell is ok for one element, can break
if (totalCell.Cells.Count > 0) break;
}
}
//Add line only if a total is set (not only calculation options)
if (model.GetElements(PivotPosition.Data).Count(e => e.ShowTotal == ShowTotal.Column || e.ShowTotal == ShowTotal.RowColumn) > 0) page.DataTable.Lines.Add(totalLine);
}
//Totals per rows
if (rowTotalElements.Count() > 0)
{
//We add first one cell for each line (one per element) -> actually we add columns
for (int i = page.DataTable.Lines.Count - 1; i >= 0; i--)
{
if (Report.Cancel) break;
bool isTotalTitleSet = false;
var rowLine = page.DataTable.Lines[i];
//Calculate the row total
foreach (var element in rowTotalElements)
{
ResultTotalCell totalCell = new ResultTotalCell() { Element = element, IsTotal = true, IsTotalTotal = (i == page.DataTable.Lines.Count - 1) };
bool isHeaderLine = false;
foreach (var cell in rowLine)
{
if (cell != null && !cell.IsTitle && element == cell.Element) totalCell.Cells.Add(cell);
else if (cell != null && cell.IsTitle && element == cell.Element) isHeaderLine = true;
}
totalCell.Calculate();
//Add the cell
if (element.ShowTotal == ShowTotal.Row || element.ShowTotal == ShowTotal.RowColumn)
{
Array.Resize<ResultCell>(ref rowLine, rowLine.Length + 1);
//Add titles if not a value
if (totalCell.Cells.Count == 0)
{
string value = "";
if (i == 0 && !isTotalTitleSet)
{
if (!isHeaderLine) isTotalTitleSet = true;
value = Report.Translate("Total");
}
if (isHeaderLine && rowTotalElements.Count() > 1) Helper.AddValue(ref value, " ", Report.Repository.TranslateElement(element, element.DisplayNameEl));
if (!string.IsNullOrEmpty(value)) totalCell.IsTitle = true;
totalCell.Value = value;
}
rowLine[rowLine.Length - 1] = totalCell;
page.DataTable.Lines[i] = rowLine;
}
//Handle calculations
if (!totalCell.IsTitle)
{
if (element.CalculationOption == CalculationOption.PercentageRow)
{
foreach (ResultCell cell in totalCell.Cells) cell.Value = cell.DoubleValue / totalCell.DoubleValue;
totalCell.Value = 1;
}
else if (element.CalculationOption == CalculationOption.PercentageAll)
{
ResultTotalCell totalTotalCell = page.DataTable.TotalCells.FirstOrDefault(c => c.Element == element);
if (totalTotalCell != null)
{
foreach (ResultCell cell in totalCell.Cells) cell.Value = cell.DoubleValue / totalTotalCell.DoubleValue;
if (totalCell != totalTotalCell) totalCell.Value = totalCell.DoubleValue / totalTotalCell.DoubleValue;
}
}
if (i == page.DataTable.Lines.Count - 1 && element.CalculationOption != CalculationOption.No)
{
//case of total of total cell with calc options, set value to 1
totalCell.Value = 1;
}
}
}
}
}
//Set total totals to 1 if calculation options.
foreach (ResultTotalCell cell in page.DataTable.TotalCells.Where(i => i.Element.CalculationOption != CalculationOption.No)) cell.Value = 1;
//Add totals for Page and Summary tables
if (page.PageTable != null && page.PageTable.Lines.Count == 2 && page.PageTable.Lines[0].Length > 0)
{
//Add totals for pages
foreach (ResultCell cell in page.DataTable.TotalCells.Where(i => i.Element.ShowTotal != ShowTotal.No))
{
ResultCell[] page0 = page.PageTable.Lines[0];
Array.Resize<ResultCell>(ref page0, page0.Length + 1);
page0[page0.Length - 1] = new ResultTotalCell() { Element = cell.Element, IsTotal = true, IsTitle = true, Value = cell.Element.DisplayNameEl };
page.PageTable.Lines[0] = page0;
ResultCell[] page1 = page.PageTable.Lines[1];
Array.Resize<ResultCell>(ref page1, page1.Length + 1);
page1[page1.Length - 1] = cell;
page.PageTable.Lines[1] = page1;
}
}
}
//Add totals for summary table
if (model.Pages.Count > 1)
{
List<ResultTotalCell> tttCells = new List<ResultTotalCell>();
//First titles line
var line0 = model.SummaryTable.Lines[0];
foreach (ResultCell cell in model.Pages[0].DataTable.TotalCells.Where(i => i.Element.ShowTotal != ShowTotal.No))
{
Array.Resize<ResultCell>(ref line0, line0.Length + 1);
ResultTotalCell totalCell = new ResultTotalCell() { Element = cell.Element, IsTotal = true, IsTitle = true, Value = cell.Element.DisplayNameEl };
line0[line0.Length - 1] = totalCell;
}
model.SummaryTable.Lines[0] = line0;
//Then value per page
int index = 1;
foreach (ResultPage page in model.Pages)
{
var line = model.SummaryTable.Lines[index];
foreach (ResultCell cell in page.DataTable.TotalCells.Where(i => i.Element.ShowTotal != ShowTotal.No))
{
ResultTotalCell tttCell = tttCells.FirstOrDefault(i => i.Element == cell.Element);
if (tttCell == null)
{
tttCell = new ResultTotalCell() { Element = cell.Element, IsTotal = true, IsTotalTotal = true };
tttCells.Add(tttCell);
}
Array.Resize<ResultCell>(ref line, line.Length + 1);
line[line.Length - 1] = cell;
tttCell.Cells.Add(cell);
}
model.SummaryTable.Lines[index] = line;
index++;
if (index >= model.SummaryTable.Lines.Count) break;
}
//Final line: Total of total of total !
ResultCell[] tttLine = new ResultCell[model.SummaryTable.Lines[0].Length];
for (int i = 0; i < line0.Length; i++)
{
if (!line0[i].IsTotal)
{
//empty cell
tttLine[i] = new ResultTotalCell() { Element = line0[i].Element, IsTotal = true, Value = "" };
}
else
{
ResultTotalCell tttCell = tttCells.FirstOrDefault(cell => cell.Element == line0[i].Element);
if (tttCell != null)
{
tttCell.Calculate();
tttLine[i] = tttCell;
}
}
}
model.SummaryTable.Lines.Add(tttLine);
}
}
private void buildPages(ReportModel model)
{
model.Pages.Clear();
if (model.ResultTable == null) return;
ResultCell[] currentPageValues = null;
ResultPage currentPage = null;
//Build pages
foreach (DataRow row in model.ResultTable.Rows)
{
if (Report.Cancel) break;
ResultCell[] pageValues = GetResultCells(PivotPosition.Page, row, model);
ResultCell[] rowValues = GetResultCells(PivotPosition.Row, row, model);
ResultCell[] columnValues = GetResultCells(PivotPosition.Column, row, model);
ResultCell[] dataValues = GetResultCells(PivotPosition.Data, row, model);
bool createPage = false;
if (currentPageValues == null)
{
createPage = true;
}
else
{
createPage = IsDifferent(currentPageValues, pageValues);
}
currentPageValues = pageValues;
if (createPage)
{
//Build new page
currentPage = new ResultPage() { Report = Report };
//Create Page table
currentPage.Pages = pageValues;
model.Pages.Add(currentPage);
}
//Set values in page
if (rowValues.Length > 0)
{
int rowIndex = 0;
if (columnValues.Length == 0)
{
currentPage.Rows.Add(rowValues);
rowIndex = currentPage.Rows.Count - 1;
}
else
{
//At least a column, we have to find the current row
rowIndex = FindDimension(rowValues, currentPage.Rows);
}
rowValues = currentPage.Rows[rowIndex];
}
int columnIndex = 0;
if (columnValues.Length > 0)
{
columnIndex = FindDimension(columnValues, currentPage.Columns);
columnValues = currentPage.Columns[columnIndex];
}
if (dataValues.Length > 0)
{
ResultData data = new ResultData() { Row = rowValues, Column = columnValues, Data = dataValues };
if (!currentPage.Datas.ContainsKey(rowValues))
{
currentPage.Datas.Add(rowValues, new List<ResultData>());
}
currentPage.Datas[rowValues].Add(data);
}
}
}
private void buildSeries(ReportModel model)
{
foreach (ResultPage page in model.Pages)
{
if (Report.Cancel) break;
foreach (List<ResultData> datas in page.Datas.Values)
{
if (Report.Cancel) break;
foreach (ResultData data in datas)
{
if (Report.Cancel) break;
ResultCell[] xPrimaryDimensions = GetXSerieCells(AxisType.Primary, data.Row, data.Column, model);
int primaryIndex = FindDimension(xPrimaryDimensions, page.PrimaryXDimensions);
xPrimaryDimensions = page.PrimaryXDimensions[primaryIndex];
ResultCell[] xSecondaryDimensions = GetXSerieCells(AxisType.Secondary, data.Row, data.Column, model);
int secondaryIndex = FindDimension(xSecondaryDimensions, page.SecondaryXDimensions);
xSecondaryDimensions = page.SecondaryXDimensions[secondaryIndex];
string primarySplitterValues = Helper.ConcatCellValues(GetSplitterSerieCells(AxisType.Primary, data.Row, data.Column, model), ",");
string secondarySplitterValues = Helper.ConcatCellValues(GetSplitterSerieCells(AxisType.Secondary, data.Row, data.Column, model), ",");
foreach ( var dataCell in data.Data.Where(i => i.Element.IsSerie))
{
var serieElement = dataCell.Element;
ResultCell[] xValues = (serieElement.XAxisType == AxisType.Primary ? xPrimaryDimensions : xSecondaryDimensions);
string splitterValue = (serieElement.XAxisType == AxisType.Primary ? primarySplitterValues : secondarySplitterValues);
ResultSerie serie = page.Series.FirstOrDefault(i => i.Element == serieElement && i.SplitterValues == splitterValue);
if (serie == null)
{
serie = new ResultSerie() { Element = serieElement, SplitterValues = splitterValue };
page.Series.Add(serie);
}
ResultSerieValue serieValue = serie.Values.FirstOrDefault(i => i.XDimensionValues == xValues);
if (serieValue == null)
{
serieValue = new ResultSerieValue() { XDimensionValues = xValues };
serieValue.Yvalue = new ResultTotalCell() { Element = serieElement, IsSerie = true };
serie.Values.Add(serieValue);
}
serieValue.Yvalue.Cells.Add(new ResultCell() { Element = serieElement, Value = dataCell.Value, ContextRow = dataCell.ContextRow, ContextCol = dataCell.ContextCol });
}
}
}
}
//Calculate the serie values
foreach (ResultPage page in model.Pages)
{
if (Report.Cancel) break;
foreach (var serie in page.Series)
{
foreach (var serieValue in serie.Values)
{
//Classic calculation
serieValue.Yvalue.Calculate();
if (!string.IsNullOrEmpty(serieValue.Yvalue.Element.CellScript))
{
//Cell script option
serieValue.Yvalue.ProcessContext();
serieValue.Yvalue.ContextTable = page.DataTable;
serieValue.Yvalue.ContextPage = page;
serieValue.Yvalue.ContextModel = model;
executeCellScript(serieValue.Yvalue);
}
}
}
}
}
void handleCustomScripts(ReportModel model, ResultPage page, ResultTable table)
{
for (int row = 0; row < table.Lines.Count; row++)
{
var line = table.Lines[row];
for (int col = 0; col < line.Length; col++)
{
var cell = line[col];
if (cell.Element != null && !string.IsNullOrWhiteSpace(cell.Element.CellScript))
{
cell.ContextRow = row;
cell.ContextCol = col;
cell.ContextTable = table;
cell.ContextPage = page;
cell.ContextModel = model;
executeCellScript(cell);
}
}
}
}
private void handleFinalScript(ReportModel model)
{
model.ExecuteLoadScript(model.FinalScript, "Final Script", model);
}
ResultCell[] GetXSerieCells(AxisType xAxisType, ResultCell[] row, ResultCell[] col, ReportModel model)
{
ReportElement[] elements = model.GetXElements(xAxisType).ToArray();
return GetResultCells(elements, row, col);
}
private void handleCellScript(ReportModel model)
{
_cellCompilationKeys = new Dictionary<string, string>();
foreach (ResultPage page in model.Pages)
{
if (Report.Cancel) break;
handleCustomScripts(model, page, page.DataTable);
//We do not handle Page table as calculations will be done in the summary table, as the cells are shared amongst Page and Summary
//handleCustomScripts(model, page, page.PageTable, _compilationKeys);
}
handleCustomScripts(model, null, model.SummaryTable);
}
ResultCell[] GetResultCells(PivotPosition position, DataRow row, ReportModel model)
{
ReportElement[] elements = model.Elements.Where(i => i.PivotPosition == position).ToArray();
return GetResultCells(elements, row);
}
ResultCell[] GetHeaderCells(PivotPosition position, ReportModel model)
{
return
(from element in model.Elements
where element.PivotPosition == position
select new ResultCell() { Element = element, IsTitle = true, Value = element.DisplayNameEl }).ToArray();
}
private void finalSort(ReportModel model)
{
foreach (var page in model.Pages)
{
//If we have rows and columns OR if there are cell scripts, we have to resort the rows and columns as the SQL cannot do that directly...
if ((page.Rows.Count > 0 && page.Columns.Count > 0) || model.Elements.Count(e => !string.IsNullOrEmpty(e.CellScript) && e.IsSorted) > 0)
{
page.Rows.Sort(ResultCell.CompareCells);
page.Columns.Sort(ResultCell.CompareCells);
}
//Sort also series axis
if (model.HasSerie)
{
page.PrimaryXDimensions.Sort(ResultCell.CompareCells);
page.SecondaryXDimensions.Sort(ResultCell.CompareCells);
}
}
}
public string CheckSQL(string sql, List<MetaTable> tables, ReportModel model)
{
string result = "";
if (!string.IsNullOrEmpty(sql))
{
try
{
DbConnection connection = Connection.GetOpenConnection();
Helper.ExecutePrePostSQL(connection, PreSQL, this, this.IgnorePrePostError);
if (tables != null) foreach (var table in tables) Helper.ExecutePrePostSQL(connection, table.PreSQL, table, table.IgnorePrePostError);
if (model != null) Helper.ExecutePrePostSQL(connection, model.PreSQL, model, model.IgnorePrePostError);
var command = connection.CreateCommand();
command.CommandText = sql;
command.ExecuteReader();
if (model != null) Helper.ExecutePrePostSQL(connection, model.PostSQL, model, model.IgnorePrePostError);
if (tables != null) foreach (var table in tables) Helper.ExecutePrePostSQL(connection, table.PostSQL, table, table.IgnorePrePostError);
Helper.ExecutePrePostSQL(connection, PostSQL, this, this.IgnorePrePostError);
command.Connection.Close();
}
catch (Exception ex)
{
result = ex.Message;
}
}
return result;
}
private void initialSort(ReportModel model)
{
foreach (var page in model.Pages)
{
//If we have rows and columns ...
if (page.Rows.Count > 0 && page.Columns.Count > 0)
{
page.Rows.Sort(ResultCell.CompareCells);
page.Columns.Sort(ResultCell.CompareCells);
}
}
}