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