//CR: replace in-line string expressions with constants
public static void AddSummaryRows(DataTable summary, QueryXml query, string mode = MODE_ProfitAndLoss)
{
//CR: I know you don't need braces around single line if statements,
// but it can make the code harder to read, only the first statement
// will be executed which can cause a bug if there are supposed to be several
//if (summary.Rows.Count == 0)
//{
// return;
//}
//CR: invert the logic of the if statement for clarity
if (summary.Rows.Count > 0)
{
//CR: replace in-line string expressions with constants
//var rgx = new Regex("[a-zA-Z]");
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(summary.Columns, query);
//CR: move this into a testable method
var listOfDefinitions = GetListOfDefinitions(mode);
foreach (var row in listOfDefinitions)
{
var newRow = summary.NewRow();
newRow[0] = row.Name;
var lettersToProcess = Regex.Matches(row.Calculation, REGEX_Alpha.ToString()).Cast <Match>().ToArray();
foreach (DataColumn column in columnsThatCanBeCalculated)
{
if (ColumnIsValidInQuery(query, column.ColumnName, allowDelta: true))
{
var newCalc = String.Copy(row.Calculation);
foreach (var letter in lettersToProcess)
{
// get me index of letter in alphabet - word
//CR: you can simplify this expression
// var alphaIndex = (int)char.ToUpper(Convert.ToChar(letter.Value)) - 65;
var alphaIndex = (int)Convert.ToChar(letter.Value.ToUpper()) - 65;
//CR: move repeated actions into a shared method
//var computedValue = summary.Compute($"sum([{column.ColumnName}])", $"[{CODE_PROPERTY_NAME}] = '{row.SubstituteValues[alphaIndex]}'");
var computedValue = ComputeSum <string>(summary, column.ColumnName, CODE_PROPERTY_NAME, row.SubstituteValues[alphaIndex]);
//CR: computedValue is the string representation of a double, so at worst it will already be "0" - this expression is redundant
//if (String.IsNullOrEmpty(computedValue.ToString()))
// computedValue = 0;
//CR: Letter.Value is already a string
//CR: ComputedValue is already a string and will not be null - you can simplify this expression
//newCalc = newCalc.Replace(char.ToString(Convert.ToChar(letter.Value)), computedValue.ToString() ?? "0");
newCalc = newCalc.Replace(letter.Value, computedValue);
}
newRow[column.ColumnName] = 0.0;
try
{
var summaryRowValue = Convert.ToDouble(new NCalc.Expression(newCalc).Evaluate());
newRow[column.ColumnName] = summaryRowValue;
}
catch {
//CR: it is a good idea to try to catch a specific type of exception
//CR: it never hurts to do some logging in a catch block - it can help you spot potential issues during testing
}
}
if (ColumnIsValidInQuery(query, column.ColumnName, allowPercentageVariance: true, allowDelta: true))
{
QueryXml.Column compareValue = null;
QueryXml.Column varianceValue = null;
//CR: this feels like it could be simplified... but it depends on how query.Columns work
var col = query.Columns.Where(x => x.PercentageVariance == true && x.Header == column.ColumnName).First();
if (col.IsStreamVariance)
{
compareValue = query.Columns.Where(x => x.Group == col.Group && x.IsStreamCurrentActual).First();
varianceValue = query.Columns.Where(x => x.Group == col.Group && x.IsStreamVarianceActual).First();
}
else
{
compareValue = query.Columns.Where(x => x.Group == col.Group && x.IsPreviousActual).First();
varianceValue = query.Columns.Where(x => x.Group == col.Group && x.IsCurrentVarianceActual).First();
}
var varianceDouble = (double)newRow[varianceValue.Header];
var compareDouble = (double)newRow[compareValue.Header];
newRow[column.ColumnName] = 0;
if (varianceDouble != 0 && compareDouble != 0)
{
newRow[column.ColumnName] = (varianceDouble / Math.Abs(compareDouble)) * 100;
}
}
}
summary.Rows.Add(newRow);
}
}
}
public static void AddSummaryRows(DataTable summary, QueryXml query, string mode = MODE_ProfitAndLoss)
{
if (summary.Rows.Count > 0)
{
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(summary.Columns, query);
var listOfDefinitions = GetListOfDefinitions(mode);
foreach (var row in listOfDefinitions)
{
var newRow = summary.NewRow();
newRow[0] = row.Name;
var lettersToProcess = Regex.Matches(row.Calculation, REGEX_Alpha.ToString()).Cast <Match>().ToArray();
foreach (DataColumn column in columnsThatCanBeCalculated)
{
if (ColumnIsValidInQuery(query, column.ColumnName, allowDelta: true))
{
var newCalc = String.Copy(row.Calculation);
foreach (var letter in lettersToProcess)
{
var alphaIndex = (int)Convert.ToChar(letter.Value.ToUpper()) - 65;
var computedValue = ComputeSum <string>(summary, column.ColumnName, CODE_PROPERTY_NAME, row.SubstituteValues[alphaIndex]);
newCalc = newCalc.Replace(letter.Value, computedValue);
}
newRow[column.ColumnName] = 0.0;
try
{
var summaryRowValue = Convert.ToDouble(new NCalc.Expression(newCalc).Evaluate());
newRow[column.ColumnName] = summaryRowValue;
}
catch
{
}
}
if (ColumnIsValidInQuery(query, column.ColumnName, allowPercentageVariance: true, allowDelta: true))
{
QueryXml.Column compareValue = null;
QueryXml.Column varianceValue = null;
var col = query.Columns.Where(x => x.PercentageVariance == true && x.Header == column.ColumnName).First();
if (col.IsStreamVariance)
{
compareValue = query.Columns.Where(x => x.Group == col.Group && x.IsStreamCurrentActual).First();
varianceValue = query.Columns.Where(x => x.Group == col.Group && x.IsStreamVarianceActual).First();
}
else
{
compareValue = query.Columns.Where(x => x.Group == col.Group && x.IsPreviousActual).First();
varianceValue = query.Columns.Where(x => x.Group == col.Group && x.IsCurrentVarianceActual).First();
}
var varianceDouble = (double)newRow[varianceValue.Header];
var compareDouble = (double)newRow[compareValue.Header];
newRow[column.ColumnName] = 0;
if (varianceDouble != 0 && compareDouble != 0)
{
newRow[column.ColumnName] = (varianceDouble / Math.Abs(compareDouble)) * 100;
}
}
}
summary.Rows.Add(newRow);
}
}
}
public static void AddSummaryRows(DataTable summary, QueryXml query, string mode = "Profit and Loss")
{
if (summary.Rows.Count == 0)
{
return;
}
var rgx = new Regex("[a-zA-Z]");
var listOfDefinitions = new List <SummaryRow>();
switch (mode)
{
case FinancialStatements.FinancePAndLMode:
listOfDefinitions = SummaryRowDefinitions.ProfitAndLoss;
break;
case FinancialStatements.FinanceBalanceSheetMode:
listOfDefinitions = SummaryRowDefinitions.BalanceSheet;
break;
default:
listOfDefinitions = SummaryRowDefinitions.CashFlow;
break;
}
foreach (var row in listOfDefinitions)
{
var newRow = summary.NewRow();
newRow[0] = row.Name;
var lettersToProcess = Regex.Matches(row.Calculation, rgx.ToString()).Cast <Match>().ToArray();
foreach (DataColumn column in summary.Columns)
{
if (!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName) && query.Columns.Where(x => x.PercentageVariance == false && x.Header == column.ColumnName).Any())
{
var newCalc = String.Copy(row.Calculation);
foreach (var letter in lettersToProcess)
{
// get me index of letter in alphabet - word
var alphaIndex = (int)char.ToUpper(Convert.ToChar(letter.Value)) - 65;
var computedValue = summary.Compute($"sum([{column.ColumnName}])", $"[{CODE_PROPERTY_NAME}] = '{row.SubstituteValues[alphaIndex]}'");
if (String.IsNullOrEmpty(computedValue.ToString()))
{
computedValue = 0;
}
newCalc = newCalc.Replace(char.ToString(Convert.ToChar(letter.Value)), computedValue.ToString() ?? "0");
}
newRow[column.ColumnName] = 0.0;
try
{
var summaryRowValue = Convert.ToDouble(new NCalc.Expression(newCalc).Evaluate());
newRow[column.ColumnName] = summaryRowValue;
}
catch { }
}
if ((!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName)) && query.Columns.Where(x => x.PercentageVariance == true && x.Header == column.ColumnName).Any())
{
QueryXml.Column compareValue = null;
QueryXml.Column varianceValue = null;
var col = query.Columns.Where(x => x.PercentageVariance == true && x.Header == column.ColumnName).First();
if (col.IsStreamVariance)
{
compareValue = query.Columns.Where(x => x.Group == col.Group && x.IsStreamCurrentActual).First();
varianceValue = query.Columns.Where(x => x.Group == col.Group && x.IsStreamVarianceActual).First();
}
else
{
compareValue = query.Columns.Where(x => x.Group == col.Group && x.IsPreviousActual).First();
varianceValue = query.Columns.Where(x => x.Group == col.Group && x.IsCurrentVarianceActual).First();
}
var varianceDouble = (double)newRow[varianceValue.Header];
var compareDouble = (double)newRow[compareValue.Header];
newRow[column.ColumnName] = 0;
if (varianceDouble != 0 && compareDouble != 0)
{
newRow[column.ColumnName] = (varianceDouble / Math.Abs(compareDouble)) * 100;
}
}
}
summary.Rows.Add(newRow);
}
}