private void CalculateByPeriods(ForecastColumn calculationColumn, IEnumerable <ForecastColumn> referenceColumns)
{
var formula = FormulaUtilities.DeserializeFormula(calculationColumn);
foreach (var period in Periods)
{
var cells = new List <Cell>();
ICollection <Cell> currentCells = CellRepository
.GetCells(Sheet, new[] { period }, referenceColumns).ToList();
var groupedCells = currentCells.GroupBy(cell => cell.RowId);
foreach (var group in groupedCells)
{
Cell calculatedCell = null;
try {
calculatedCell = GetCalculatedCell(group.Select(a => a), formula);
} catch (Exception ex) {
LogCalculatedCellException(calculationColumn, period, ex);
}
if (calculatedCell != null)
{
calculatedCell.PeriodId = period.Id;
calculatedCell.ColumnId = calculationColumn.Id;
cells.Add(calculatedCell);
}
}
SaveCells(cells);
}
}
private IEnumerable <FunctionInfo> GetFormulaFunctions(ForecastColumn column)
{
var settings = column.GetColumnSettings <FormulaSetting>();
var formula = settings.SummaryValue ?? settings.Value ?? new FormulaItem[0];
var functions = FormulaUtilities.GetFunctions(formula);
return(functions);
}
private void UpdateColumn(ForecastColumn column)
{
var update = new Update(UserConnection, "ForecastColumn")
.Set("Position", Column.Parameter(column.Position))
.Where("Id").IsEqual(Column.Parameter(new Guid(column.Code))) as Update;
update.Execute();
}
private bool HasReference(ForecastColumn formulaColumn, ForecastColumn parentColumn)
{
var formulaItems = GetFormulaItems(formulaColumn);
return(formulaItems
.Where(item => item.Type.Equals(FormulaItemType.Column))
.Any(item => item.Value.Equals(parentColumn.Id.ToString())));
}
private decimal CalculateValueFromCells(IEnumerable <Cell> recordsCells, ForecastColumn groupEditColumn,
Guid periodId)
{
var cellsToSum = recordsCells.Where(c =>
c.ColumnId == groupEditColumn.Id && c.PeriodId == periodId);
decimal value = cellsToSum.Sum(c => c.Value);
return(value);
}
private void CalcFunctionCell(IEnumerable <TableCell> columnValues, IEnumerable <TableCell> summaryCells,
ForecastColumn funcColumn, FormulaItem function)
{
TableCell summaryCell = GetSummaryCell(summaryCells, funcColumn);
string functionCode = function.Value;
summaryCell.FunctionsValueMap = summaryCell.FunctionsValueMap ?? new Dictionary <string, double>();
double value = CalcColumnSummary(columnValues, funcColumn, functionCode);
summaryCell.FunctionsValueMap[functionCode] = value;
}
private ColumnSetting GetForecastColumnSettings()
{
ForecastColumn column = ColumnRepository.GetColumns(ForecastSheet.Id)
.FirstOrDefault();
if (column == null || column.Settings.IsNullOrWhiteSpace())
{
return(new ColumnSetting());
}
return(Json.Deserialize <ColumnSetting>(column.Settings));
}
private void CalcFormulaColumnSummary(ForecastColumn column, IEnumerable <TableCell> columnValues,
IEnumerable <TableCell> summaryCells)
{
var functions = GetFormulaFunctions(column);
foreach (FunctionInfo functionInfo in functions)
{
ForecastColumn funcColumn = GetColumnByFunction(functionInfo);
CalcFunctionCell(columnValues, summaryCells, funcColumn, functionInfo.Function);
}
}
/// <summary>
/// Gets the forecast column settings data from forecast column.
/// </summary>
/// <param name="userConnection">User connection instance.</param>
/// <param name="column">Forecast column.</param>
/// <returns>Column settings data</returns>
public ColumnSettingsData GetForecastColumnSettingsData(UserConnection userConnection,
ForecastColumn column)
{
column.CheckArgumentNull(nameof(column));
if (column.Settings.IsNullOrWhiteSpace())
{
return(null);
}
ColumnSettingsData settings = FillSettings(userConnection, column);
return(settings);
}
/// <summary>
/// Returns the reference columns.
/// </summary>
/// <param name="columns">The columns.</param>
/// <param name="targetColumn">The target column.</param>
/// <returns>
/// Collection of reference columns.
/// </returns>
public IEnumerable <ForecastColumn> GetReferenceColumns(IEnumerable <ForecastColumn> columns,
ForecastColumn targetColumn)
{
if (!targetColumn.TypeId.Equals(ForecastConsts.FormulaColumnTypeId))
{
return(Enumerable.Empty <ForecastColumn>());
}
var formulaItems = GetFormulaItems(targetColumn);
var columnItems = formulaItems?.Where(item => item.Type.Equals(FormulaItemType.Column));
return(columns.Where(column =>
columnItems.Any(a =>
a.Value.Equals(column.Id.ToString())
)
));
}
/// <summary>
/// Iterates formula columns in their dependency order.
/// </summary>
/// <param name="columns">All columns collection.</param>
/// <param name="iterateFn">Iterate function.</param>
public void IterateFormulaColumns(IEnumerable <ForecastColumn> columns, Action <ForecastColumn> iterateFn)
{
List <Guid> calculatedColumns = new List <Guid>();
var formulaColumns = GetFormulaColumns(columns);
ForecastColumn calculationColumn = NextCalculationColumn(formulaColumns, calculatedColumns);
while (calculationColumn != null)
{
if (CheckCanCalculate(columns, calculationColumn, calculatedColumns))
{
iterateFn?.Invoke(calculationColumn);
calculatedColumns.Add(calculationColumn.Id);
}
calculationColumn = NextCalculationColumn(formulaColumns, calculatedColumns,
calculationColumn);
}
}
private static FormulaItem[] BuildSUMSelfFormula(ForecastColumn column)
{
return(new [] {
new FormulaItem {
Type = FormulaItemType.Function,
Caption = "SUM",
Value = "SUM",
},
OpenBrace(),
new FormulaItem {
Type = FormulaItemType.Column,
Caption = column.Name,
Value = column.Id.ToString(),
},
ClosedBrace(),
});
}
private bool CheckCanCalculate(IEnumerable <ForecastColumn> allColumns, ForecastColumn formulaColumn,
IEnumerable <Guid> calculatedColumns)
{
var result = true;
var referenceColumns = GetReferenceColumns(allColumns, formulaColumn);
foreach (var referenceColumn in referenceColumns)
{
if (referenceColumn.TypeId.Equals(ForecastConsts.FormulaColumnTypeId))
{
if (calculatedColumns.Contains(referenceColumn.Id))
{
continue;
}
result = false;
}
break;
}
return(result);
}
private IEnumerable <TableCell> CalculateValuesForColumn(ForecastColumn calculationColumn,
IEnumerable <TableCell> cells, Guid recordId)
{
var calculatedCells = new List <TableCell>();
var formula = FormulaUtilities.DeserializeFormula(calculationColumn);
foreach (var period in Periods)
{
var values = GetFormulaValues(formula, period, cells);
decimal value = FormulaUtilities.Calculate(formula, values);
calculatedCells.Add(new TableCell {
GroupCode = period,
ColumnCode = calculationColumn.Code,
Value = (double)value,
RecordId = recordId,
Id = calculationColumn.Id
});
}
return(calculatedCells);
}
public ConfigurationServiceResponse GetIsValidColumn(Guid columnTypeId, string settings, FormulaSettingType type = FormulaSettingType.Default)
{
var response = new ConfigurationServiceResponse();
if (columnTypeId.Equals(ForecastConsts.FormulaColumnTypeId))
{
var forecastColumn = new ForecastColumn {
Settings = settings
};
var formulaSetting = forecastColumn.GetColumnSettings <FormulaSetting>();
var isValid = IsFormulaInColumnValid(formulaSetting, type);
response.Success = isValid;
if (!isValid)
{
var message = UserConnection.GetLocalizableString(LczResourcesSchemaName, "FormulaNotValidMessage");
response.ErrorInfo = new ErrorInfo {
Message = message
};
}
}
return(response);
}
public ConfigurationServiceResponse DeleteColumn(Guid columnId, Guid sheetId)
{
var response = new ConfigurationServiceResponse();
var isSuccess = false;
IEnumerable <ForecastColumn> columns = ColumnRepository.GetColumns(sheetId);
ForecastColumn column = columns.FirstOrDefault(c => c.Id == columnId);
if (column != null)
{
try {
var formulaColumns = FormulaUtilities.GetDependColumns(columns, column);
if (!formulaColumns.Any())
{
var sheet = SheetRepository.GetSheet(sheetId);
isSuccess = ColumnRepository.Delete(sheet, columnId);
}
else
{
var messageTemplate =
UserConnection.GetLocalizableString("ForecastResources", "ColumnUsedInFormula");
var message = string.Format(messageTemplate, formulaColumns.First().Name);
response.ErrorInfo = new ErrorInfo {
Message = message
};
}
} catch (Exception ex) {
response.Exception = ex;
}
}
else
{
response.Exception = new KeyNotFoundException(columnId.ToString());
}
response.Success = isSuccess;
return(response);
}
private IEnumerable <TableCell> GetCellsByColumn(IEnumerable <TableCell> columnValues, ForecastColumn column)
{
var cellsToCalc = columnValues.Where(cell =>
cell.ColumnCode.StartsWith(column.Id.ToString()));
return(cellsToCalc);
}
protected virtual ColumnSettingsData FillSettings(UserConnection userConnection, ForecastColumn column)
{
ColumnSetting deserialized = Json.Deserialize <ColumnSetting>(column.Settings);
Core.Entities.EntitySchema schema = userConnection.EntitySchemaManager.GetInstanceByUId(deserialized.SourceUId);
ColumnSettingsData settings = new ColumnSettingsData {
SourceEntityName = schema.Name,
ReferenceColumnName = deserialized.RefColumnPath ?? GetColumnName(schema, deserialized.RefColumnId),
PeriodColumnName = deserialized.PeriodColumnPath ?? GetColumnName(schema, deserialized.PeriodColumnId),
FuncColumnName = deserialized.FuncColumnPath ?? GetColumnName(schema, deserialized.FuncColumnId),
FuncCode = deserialized.FuncCode,
PercentOperand = deserialized.PercentOperand,
FilterData = deserialized.FilterData.IsNotNullOrEmpty() ?
Json.Deserialize <Filters>(deserialized.FilterData) : null
};
return(settings);
}
/// <summary>
/// Deserializes the formula.
/// </summary>
/// <param name="column">The formula column.</param>
/// <returns>
/// Collection of <see cref="FormulaItem" />
/// </returns>
public IEnumerable <FormulaItem> DeserializeFormula(ForecastColumn column)
{
return(GetFormulaItems(column));
}
/// <summary>
/// Returns the depend columns.
/// </summary>
/// <param name="columns">The columns.</param>
/// <param name="parentColumn">The parent column.</param>
/// <returns>
/// Collection of depend columns.
/// </returns>
public virtual IEnumerable <ForecastColumn> GetDependColumns(IEnumerable <ForecastColumn> columns, ForecastColumn parentColumn)
{
var result = new List <ForecastColumn>();
var formulaColumns = columns.Where(item => item.TypeId.Equals(ForecastConsts.FormulaColumnTypeId));
foreach (var formulaColumn in formulaColumns)
{
if (HasReference(formulaColumn, parentColumn))
{
result.Add(formulaColumn);
}
}
return(result);
}
private bool IsFormulaColumn(ForecastColumn column)
{
return(column.TypeId == ForecastConsts.FormulaColumnTypeId);
}
private ForecastColumn NextCalculationColumn(IEnumerable <ForecastColumn> formulaColumns,
IEnumerable <Guid> calculatedColumns, ForecastColumn lastColumn = null)
{
return(formulaColumns.FirstOrDefault(column =>
!calculatedColumns.Contains(column.Id) && !column.Id.Equals(lastColumn?.Id)));
}
private double CalcColumnSummary(IEnumerable <TableCell> columnValues, ForecastColumn column, string functionCode)
{
var cellsToCalc = GetCellsByColumn(columnValues, column);
return(CalcCellsByFunction(functionCode, cellsToCalc));
}
private TableCell GetSummaryCell(IEnumerable <TableCell> cells, ForecastColumn column)
{
string summaryColumnCode = FormSummaryColumnCode(column);
return(cells.First(cell => cell.ColumnCode == summaryColumnCode));
}
private IEnumerable <FormulaItem> GetFormulaItems(ForecastColumn column)
{
return(column.GetColumnSettings <FormulaSetting>().Value ?? new FormulaItem[0]);
}
private void LogCalculatedCellException(ForecastColumn calculationColumn, Period period, Exception ex)
{
_log.Error($"Error during forecast calculation in column with name \"{calculationColumn?.Name}\" " +
$"and id \"{calculationColumn?.Id.ToString()}\" during period calculation with name \"{period?.Name}\" " +
$"and id \"{period?.Id.ToString()}\"", ex);
}
private string FormSummaryColumnCode(ForecastColumn column)
{
return($"{column.Code}_{SummaryCode}");
}
private static bool IsFormulaColumn(ForecastColumn column)
{
return(column.TypeId == Terrasoft.Configuration.ForecastConsts.FormulaColumnTypeId);
}
