// fill aggregate values into data column
private static void FillGridAggrFMinMax(DataTable data, bool max, AvrViewColumn aggrColumn)
{
foreach (DataRow row in data.Rows)
{
row[aggrColumn.UniquePath] = FillGridAggrFMinMax(row, max, aggrColumn);
}
}
// returns one aggregate value
public static object FillAggregateColumn(DataTable data, int rowIndex, AvrViewColumn col, ref Dictionary <int, object> dict)
{
switch (col.AggregateFunction)
{
case (long)ViewAggregateFunction.CumulativePercent:
return(FillGridAggrFCumProcent(data, rowIndex, col, ref dict));
case (long)ViewAggregateFunction.PercentOfColumn:
return(FillGridAggrFColProcent(data, rowIndex, col, ref dict));
case (long)ViewAggregateFunction.PercentOfRow:
return(FillGridAggrFRowProcent(data.Rows[rowIndex], col));
case (long)ViewAggregateFunction.Proportion:
return(FillGridAggrFProportion(data.Rows[rowIndex], col.SourceViewColumn, col.DenominatorViewColumn));
case (long)ViewAggregateFunction.Ratio:
return(FillGridAggrFRatio(data.Rows[rowIndex], col));
case (long)ViewAggregateFunction.MaxOfRow:
return(FillGridAggrFMinMax(data.Rows[rowIndex], true, col));
case (long)ViewAggregateFunction.MinOfRow:
return(FillGridAggrFMinMax(data.Rows[rowIndex], false, col));
}
return(DBNull.Value);
}
// create in grid(control) the child - column of current band
private static void AddToGrid(AvrViewColumn obj, MVCxGridViewColumnCollection Columns, PdfExportHelper pdfHelper)
{
Columns.Add(column =>
{
LayoutViewHelper.SetNewColumn(column, obj, pdfHelper);
});
}
// fill aggregate values into data column
private static void FillGridAggrFCumProcent(DataTable data, AvrViewColumn aggrColumn, string sort)
{
if (!bv.common.Core.Utils.IsEmpty(aggrColumn.SourceViewColumn) && data.Columns.Contains(aggrColumn.SourceViewColumn))
{
var view = aggrColumn.GetView();
var rowCols = view.GetVisibleRowAdminColumns(true, null, false);
// get Sum of refColumn
double sumFull = AvrViewHelper.GetColumnSum(data, rowCols.Count, view.GrandTotalSuffix, view.TotalSuffix, aggrColumn.SourceViewColumn);
if (sumFull != 0)
{
double sum = 0;
DataView dv = new DataView(data);
dv.Sort = sort;
foreach (DataRowView row in dv)
{
if (!AvrViewHelper.GetRowTotalStatus(row.Row, rowCols.Count, view.GrandTotalSuffix, view.TotalSuffix)) //if regular value (not total)
{
object cur = row[aggrColumn.SourceViewColumn];
if (bv.common.Core.Utils.IsEmpty(cur))
{
continue;
}
double dCur;
if (double.TryParse(cur.ToString(), out dCur))
{
sum += dCur;
row[aggrColumn.UniquePath] = sum / sumFull;
}
}
}
}
}
}
// fill aggregate values into data column
private static void FillGridAggrFProportion(DataTable data, AvrViewColumn aggrColumn)
{
foreach (DataRow row in data.Rows)
{
row[aggrColumn.UniquePath] = FillGridAggrFProportion(row, aggrColumn.SourceViewColumn, aggrColumn.DenominatorViewColumn);
}
}
// fill aggregate values into data column
private static void FillGridAggrFRatio(DataTable data, AvrViewColumn aggrColumn)
{
foreach (DataRow row in data.Rows)
{
row[aggrColumn.UniquePath] = FillGridAggrFRatio(row, aggrColumn);
}
}
// returns one aggregate value
private static object FillGridAggrFRowProcent(DataRow row, AvrViewColumn aggrColumn)
{
object ret = DBNull.Value;
if (!bv.common.Core.Utils.IsEmpty(aggrColumn.SourceViewColumn) && row.Table.Columns.Contains(aggrColumn.SourceViewColumn))
{
double sum = 0;
double dCol;
var view = aggrColumn.GetView();
object cur = row[aggrColumn.SourceViewColumn];
if (!bv.common.Core.Utils.IsEmpty(cur) && double.TryParse(cur.ToString(), out dCol))
{
// get Sum of rowIndex row inside parent band
aggrColumn.Owner.GetAllColumns().ToList().FindAll(col => !col.IsToDelete && !col.IsAggregate && col.IsVisible && col.FieldType.IsNumeric() &&
!col.OriginalName.EndsWith(view.GrandTotalSuffix) && !col.OriginalName.EndsWith(view.TotalSuffix)).ForEach(c =>
{
double dCur;
cur = row[c.UniquePath];
if (!bv.common.Core.Utils.IsEmpty(cur) && double.TryParse(cur.ToString(), out dCur))
{
sum += dCur;
}
});
if (sum != 0)
{
ret = dCol / sum;
}
}
}
return(ret);
}
// returns one aggregate value
private static object FillGridAggrFColProcent(DataTable data, int rowIndex, AvrViewColumn aggrColumn, ref Dictionary <int, object> dict)
{
object ret = DBNull.Value;
if (!bv.common.Core.Utils.IsEmpty(aggrColumn.SourceViewColumn) && data.Columns.Contains(aggrColumn.SourceViewColumn))
{
double dCur;
object cur = data.Rows[rowIndex][aggrColumn.SourceViewColumn];
if (!bv.common.Core.Utils.IsEmpty(cur) && double.TryParse(cur.ToString(), out dCur))
{
var view = aggrColumn.GetView();
var rowCols = view.GetVisibleRowAdminColumns(true, null, false);
// get Sum of refColumn
double sumFull = AvrViewHelper.GetColumnSum(data, rowCols.Count, view.GrandTotalSuffix, view.TotalSuffix, aggrColumn.SourceViewColumn, ref dict);
if (sumFull != 0)
{
ret = dCur / sumFull;
}
}
}
return(ret);
}
public void EmptyColumnBandViewSerializeTest()
{
var view = new AvrView(-1, "layoutName", -1);
string viewXml = AvrViewSerializer.Serialize(view);
Assert.IsNotNull(viewXml);
var bandSerializer = new EidssSerializer <AvrViewBand>();
var band = new AvrViewBand();
string bandXml = bandSerializer.Serialize(band);
Assert.IsNotNull(bandXml);
var colSerializer = new EidssSerializer <AvrViewColumn>();
var column = new AvrViewColumn();
column.FieldType = typeof(string);
string columnXml = colSerializer.Serialize(column);
Assert.IsNotNull(columnXml);
AvrViewColumn newColumn = colSerializer.Deserialize(columnXml);
Assert.AreEqual(column.FieldType, newColumn.FieldType);
}
// fill aggregate values into data column
private static void FillGridAggrFRowProcent(DataTable data, AvrViewColumn aggrColumn)
{
if (!bv.common.Core.Utils.IsEmpty(aggrColumn.SourceViewColumn) && data.Columns.Contains(aggrColumn.SourceViewColumn))
{
foreach (DataRow row in data.Rows)
{
row[aggrColumn.UniquePath] = FillGridAggrFRowProcent(row, aggrColumn);
}
}
}
private static void SetNewColumn(MVCxGridViewColumn column, AvrViewColumn col, PdfExportHelper pdfHelper)
{
column.Name = col.UniquePath;
column.FieldName = col.UniquePath;
column.HeaderStyle.CssClass = "gridColumnHeader";
// link to datasource
if (!col.IsAggregate)
{
if (col.FieldType == typeof(DateTime))
{
column.ColumnType = MVCxGridViewColumnType.DateEdit;
column.PropertiesEdit.DisplayFormatString = "d"; //col.PrecisionStringWeb;
}
else //if (col.FieldType == typeof(string))
{
column.ColumnType = MVCxGridViewColumnType.TextBox;
if (col.FieldType.IsNumeric() && !String.IsNullOrEmpty(col.PrecisionStringWeb))
{
column.PropertiesEdit.DisplayFormatString = col.PrecisionStringWeb;
}
}
// sorting
if (col.SortOrder.HasValue && col.SortOrder.Value >= 0)
{
column.SortOrder = col.IsSortAscending ? ColumnSortOrder.Ascending : ColumnSortOrder.Descending;
column.SortIndex = col.SortOrder.Value;
}
}
else
{
// field type
column.ColumnType = MVCxGridViewColumnType.TextBox;
column.PropertiesEdit.DisplayFormatString = String.IsNullOrEmpty(col.PrecisionStringWeb) ? "" : col.PrecisionStringWeb;
// sorting
// aggregate columns are unsortable
column.Settings.AllowSort = DefaultBoolean.False;
}
column.Caption = pdfHelper.ProcessString(col.DisplayText);
column.ReadOnly = true;
// filtration
column.Settings.HeaderFilterMode = HeaderFilterMode.List;//CheckedList
// visability
column.Visible = col.IsVisible;
// freesing
column.FixedStyle = col.IsFreezed ? GridViewColumnFixedStyle.Left : GridViewColumnFixedStyle.None;
// width
column.MinWidth = 30;
column.Width = Unit.Pixel(col.ColumnWidth);
column.CellStyle.HorizontalAlign = HorizontalAlign.Center;
}
public ActionResult AggregateColumnDlgSave([Bind] long layoutId, [Bind] string uniquePath, [Bind] string displayName, [Bind] long?cbAggregate, [Bind] int?Precision, [Bind] string cbSourceColumn, [Bind] string cbDenominator)
//public ActionResult AggregateColumnDlgSave( long layoutId, string uniquePath, string displayName,
// string cbAggregate, string precision,
// string cbSourceColumn, string cbDenominator )
{
string error;
var viewModel = GetModelFromSession(layoutId, out error);
if (viewModel == null)
{
return(new JsonResult
{
JsonRequestBehavior = JsonRequestBehavior.AllowGet,
Data = new { result = "error", error = error }
});
}
if (uniquePath == null)
{
uniquePath = viewModel.ViewHeader.SelectedColBand;
}
AvrViewColumn col = viewModel.ViewHeader.GetColumnByOriginalName(uniquePath);
if (col != null)
{
col.DisplayText = displayName;
if (col.IsAggregate)
{
col.AggregateFunction = bv.common.Core.Utils.ToNullableLong(cbAggregate);
col.Precision = bv.common.Core.Utils.ToNullableInt(Precision);
col.SourceViewColumn = cbSourceColumn;
col.DenominatorViewColumn = cbDenominator;
AggregateCasheWeb.FillAggregateColumn(viewModel.ViewData, col, viewModel.ViewHeader.GetSortExpression());
}
}
else
{
AvrViewBand band = viewModel.ViewHeader.GetBandByOriginalName(uniquePath);
if (band != null)
{
band.DisplayText = displayName;
}
}
viewModel.ViewHeader.GridViewSettings = null;
ViewBag.LayoutId = layoutId;
return(RedirectToAction("ViewLayout", new { layoutId = layoutId }));
//return new JsonResult
//{
// JsonRequestBehavior = JsonRequestBehavior.AllowGet,
// Data = new { result = "refresh", function = "ViewLayout?layoutId=" + layoutId }
//};
}
// returns one aggregate value
private static object FillGridAggrFMinMax(DataRow row, bool max, AvrViewColumn aggrColumn)
{
object ret = DBNull.Value;
var view = aggrColumn.GetView();
DateTime?mmDt = null;
double? mmN = null;
foreach (var col in aggrColumn.Owner.GetAllColumns())
{
if (col.IsToDelete || col.IsAggregate || !col.IsVisible || !col.FieldType.IsNumeric() || col.OriginalName.EndsWith(view.GrandTotalSuffix) || col.OriginalName.EndsWith(view.TotalSuffix))
{
continue;
}
object cur = row[col.UniquePath];
if (bv.common.Core.Utils.IsEmpty(cur))
{
continue;
}
double c;
DateTime cd;
if (double.TryParse(cur.ToString(), out c))
{
if (mmN == null || (max && mmN < c) || (!max && mmN > c))
{
mmN = c;
}
}
else if (DateTime.TryParse(cur.ToString(), out cd))
{
if (mmDt == null || (max && mmDt < cd) || (!max && mmDt > cd))
{
mmDt = cd;
}
}
else
{
}
}
if (mmDt != null && mmN == null)
{
ret = mmDt;
}
else if (mmN != null && mmDt == null)
{
ret = mmN;
}
return(ret);
}
public static ColumnElement SelectedColBandObject(this AvrView obj)
{
AvrViewColumn col = obj.GetColumnByOriginalName(obj.SelectedColBand);
if (col != null)
{
return(new ColumnElement(col));
}
AvrViewBand band = obj.GetBandByOriginalName(obj.SelectedColBand);
if (band != null)
{
return(new ColumnElement(band));
}
return(new ColumnElement());
}
public ColumnElement(AvrViewColumn col)
{
this.uniquePath = col.UniquePath;
this.fullPath = col.FullPath;
this.displayText = col.DisplayText;
this.isColumn = true;
this.info = col.IsAggregate ?
EidssMessages.Get("AggregateColumn") + (col.AggregateFunction.HasValue ? ": " + col.AggregateFunctionName() : "") :
EidssMessages.Get("OrdinaryColumn");
this.isAggregate = col.IsAggregate;
if (col.IsAggregate)
{
this.aggrFunction = col.AggregateFunction;
this.precision = col.Precision;
this.sourceColumn = col.SourceViewColumn;
this.denominatorColumn = col.DenominatorViewColumn;
}
}
// fill aggregate values into data column
public static void FillAggregateColumn(DataTable data, AvrViewColumn aggrCol, string sort)
{
if (data.Columns.Contains(aggrCol.UniquePath))
{
data.Columns.Remove(aggrCol.UniquePath);
}
aggrCol.SetAggrColumnTypeWeb();
data.Columns.Add(aggrCol.UniquePath, aggrCol.FieldType);
switch (aggrCol.AggregateFunction)
{
case (long)ViewAggregateFunction.CumulativePercent:
FillGridAggrFCumProcent(data, aggrCol, sort);
break;
case (long)ViewAggregateFunction.PercentOfColumn:
FillGridAggrFColProcent(data, aggrCol);
break;
case (long)ViewAggregateFunction.PercentOfRow:
FillGridAggrFRowProcent(data, aggrCol);
break;
case (long)ViewAggregateFunction.Proportion:
FillGridAggrFProportion(data, aggrCol);
break;
case (long)ViewAggregateFunction.Ratio:
FillGridAggrFRatio(data, aggrCol);
break;
case (long)ViewAggregateFunction.MaxOfRow:
FillGridAggrFMinMax(data, true, aggrCol);
break;
case (long)ViewAggregateFunction.MinOfRow:
FillGridAggrFMinMax(data, false, aggrCol);
break;
}
}
// returns one aggregate value
private static object FillGridAggrFRatio(DataRow row, AvrViewColumn col)
{
object ret = DBNull.Value;
if (!bv.common.Core.Utils.IsEmpty(col.SourceViewColumn) && !bv.common.Core.Utils.IsEmpty(col.DenominatorViewColumn) && row.Table.Columns.Contains(col.SourceViewColumn) && row.Table.Columns.Contains(col.DenominatorViewColumn))
{
double dRef;
object cur = row[col.SourceViewColumn];
if (!bv.common.Core.Utils.IsEmpty(cur) && double.TryParse(cur.ToString(), out dRef))
{
cur = row[col.DenominatorViewColumn];
double dDen;
if (!bv.common.Core.Utils.IsEmpty(cur) && double.TryParse(cur.ToString(), out dDen))
{
ret = AvrViewHelper.GetRatio(dRef, dDen, col.Precision);
}
}
}
return(ret);
}
// fill aggregate values into data column
private static void FillGridAggrFColProcent(DataTable data, AvrViewColumn aggrColumn)
{
if (!bv.common.Core.Utils.IsEmpty(aggrColumn.SourceViewColumn) && data.Columns.Contains(aggrColumn.SourceViewColumn))
{
var view = aggrColumn.GetView();
var rowCols = view.GetVisibleRowAdminColumns(true, null, false);
// get Sum of refColumn
double sumFull = AvrViewHelper.GetColumnSum(data, rowCols.Count, view.GrandTotalSuffix, view.TotalSuffix, aggrColumn.SourceViewColumn);
if (sumFull != 0)
{
double dCur;
foreach (DataRow row in data.Rows)
{
object cur = row[aggrColumn.SourceViewColumn];
if (!bv.common.Core.Utils.IsEmpty(cur) && double.TryParse(cur.ToString(), out dCur))
{
row[aggrColumn.UniquePath] = dCur / sumFull;
}
}
}
}
}
// returns one aggregate value
private static object FillGridAggrFCumProcent(DataTable data, int rowIndex, AvrViewColumn aggrColumn, ref Dictionary <int, object> dict)
{
object ret = DBNull.Value;
if (!bv.common.Core.Utils.IsEmpty(aggrColumn.SourceViewColumn) && data.Columns.Contains(aggrColumn.SourceViewColumn))
{
var view = aggrColumn.GetView();
var rowCols = view.GetVisibleRowAdminColumns(true, null, false);
double sum = 0;
for (int i = 0; i <= rowIndex; i++)
{
if (!AvrViewHelper.GetRowTotalStatus(data.Rows[i], rowCols.Count, view.GrandTotalSuffix, view.TotalSuffix)) //if regular value (not total)
{
object cur = data.Rows[i][aggrColumn.SourceViewColumn];
if (bv.common.Core.Utils.IsEmpty(cur))
{
continue;
}
double dCur;
if (double.TryParse(cur.ToString(), out dCur))
{
sum += dCur;
}
}
}
// get Sum of refColumn
double sumFull = AvrViewHelper.GetColumnSum(data, rowCols.Count, view.GrandTotalSuffix, view.TotalSuffix, aggrColumn.SourceViewColumn, ref dict);
if (sumFull != 0)
{
ret = sum / sumFull;
}
}
return(ret);
}
public static string AggregateFunctionName(this AvrViewColumn obj)
{
return(LookupCache.GetLookupValue(obj.AggregateFunction, EIDSS.LookupTables.AggregateFunction, "AggregateFunctionName"));
}
public static object GetValue(string sessionID, long layoutId, string keyFieldName, int rowIndex, DataTable data, AvrViewColumn col)
{
object result;
var columnsCache = ColumnsCache(sessionID, layoutId);
if (!columnsCache.ContainsKey(keyFieldName))
{
AddColumn(sessionID, layoutId, keyFieldName);
}
var colCashe = (Dictionary <int, object>)columnsCache[keyFieldName];
if (colCashe.TryGetValue(rowIndex, out result))
{
return(result);
}
object val = FillAggregateColumn(data, rowIndex, col, ref colCashe);
colCashe.Add(rowIndex, val);
return(val);
}
