public static void AdjustSigns(DataTable table, QueryXml query, string lookupColumn)
{
if (!table.Columns.Contains(lookupColumn))
{
return;
}
var rowsToReverse = GetRowsToReverse(query);
foreach (var rowLookup in rowsToReverse)
{
foreach (var row in table.AsEnumerable().Where(x => x[lookupColumn].ToString() == rowLookup))
{
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(table.Columns, query);
foreach (DataColumn column in columnsThatCanBeCalculated)
{
// Don't reverse variance % columns as Custom mode does not adjust the value. (like Variance mode does)
if (ColumnIsValidInQuery(query, column.ColumnName, allowPercentageVariance: true, allowDelta: true))
{
if (row[column.ColumnName] != DBNull.Value)
{
row[column.ColumnName] = (double)row[column.ColumnName] * -1;
}
}
}
}
}
}
//CR: It would be a good idea to move all of these private methods into a new static class where they can be tested
private static List <string> ColumnsToIgnoreForCalculations(QueryXml query = null)
{
var listToIgnore = new List <string> {
CODE_PROPERTY_NAME
};
//CR: MaxFinanceLevels isn't defined anywhere, is it a constant?
for (var level = 1; level < MaxFinanceLevels; level++)
{
listToIgnore.Add(level.ToString());
}
if (query != null)
{
foreach (var propertyLists in query.View.PropertyLists)
{
foreach (var property in propertyLists.Properties)
{
listToIgnore.Add(property.Name);
}
}
}
return(listToIgnore);
}
private static List <string> GetRowsToReverse(QueryXml query)
{
if (query.IsBalanceSheet())
{
return(FinancialStatements.PAndLRowsToReverseSign);
}
else if (query.IsProfitAndLoss())
{
return(FinancialStatements.BalanceSheetRowsToReverseSign);
}
return(FinancialStatements.CashFlowRowsReverseSign);
}
public static void AdjustSigns(DataTable table, QueryXml query, string lookupColumn)
{
if (!table.Columns.Contains(lookupColumn))
{
return;
}
//CR: this would be more testable in a seperate method
//List<string> rowsToReverse = null;
//
//if (query.IsProfitAndLoss())
// rowsToReverse = FinancialStatements.PAndLRowsToReverseSign;
//else if (query.IsBalanceSheet())
// rowsToReverse = FinancialStatements.BalanceSheetRowsToReverseSign;
//else
// rowsToReverse = FinancialStatements.CashFlowRowsReverseSign;
var rowsToReverse = GetRowsToReverse(query);
foreach (var rowLookup in rowsToReverse)
{
//CR: this doesn't look right can you add a comment explaning the logic
foreach (var row in table.AsEnumerable().Where(x => x[lookupColumn].ToString() == rowLookup))
{
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(table.Columns, query);
foreach (DataColumn column in columnsThatCanBeCalculated)
{
// Don't reverse variance % columns as Custom mode does not adjust the value. (like Variance mode does)
//CR: replace this logic with a method call
// if (!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName))
if (ColumnIsValidInQuery(query, column.ColumnName, allowPercentageVariance: true, allowDelta: true))
{
if (row[column.ColumnName] != DBNull.Value)
{
row[column.ColumnName] = (double)row[column.ColumnName] * -1;
}
}
}
}
}
}
public static void AdjustSigns(DataTable table, QueryXml query, string lookupColumn)
{
List <string> rowsToReverse = null;
if (!table.Columns.Contains(lookupColumn))
{
return;
}
if (query.IsProfitAndLoss())
{
rowsToReverse = FinancialStatements.PAndLRowsToReverseSign;
}
else if (query.IsBalanceSheet())
{
rowsToReverse = FinancialStatements.BalanceSheetRowsToReverseSign;
}
else
{
rowsToReverse = FinancialStatements.CashFlowRowsReverseSign;
}
foreach (var rowLookup in rowsToReverse)
{
foreach (var row in table.AsEnumerable().Where(x => x[lookupColumn].ToString() == rowLookup))
{
foreach (DataColumn column in table.Columns)
{
// Don't reverse variance % columns as Custom mode does not adjust the value. (like Variance mode does)
if (!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName))
{
if (row[column.ColumnName] != DBNull.Value)
{
row[column.ColumnName] = (double)row[column.ColumnName] * -1;
}
}
}
}
}
}
private static bool ColumnIsValidInQuery(QueryXml query, string columnName, bool allowPercentageVariance = false, bool allowDelta = false)
{
return(query.Columns.Any(x => x.Header == columnName && (allowPercentageVariance || !x.PercentageVariance) && (allowDelta || !columnName.Contains(UNICODE_DELTA))));
}
private static List <DataColumn> ColumnsThatCanBeCalculated(DataColumnCollection columns, QueryXml query = null)
{
var columnsToIgnoreForCalculations = ColumnsToIgnoreForCalculations(query);
var columnsThatCanBeCalculated = new List <DataColumn>();
foreach (DataColumn column in columns)
{
if (!columnsToIgnoreForCalculations.Contains(column.ColumnName))
{
columnsThatCanBeCalculated.Add(column);
}
}
return(columnsThatCanBeCalculated);
}
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 AddPercentageContribution(DataTable details, DataTable summary, QueryXml query, int levelCount = 1)
{
//CR: cache a set of columns that can be calculated
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(details.Columns, query);
//CR: iterate through the set of calcuable columns
foreach (DataColumn column in columnsThatCanBeCalculated)
{
//CR: you no longer need to test that the column is not ignored
// if (!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName) && query.Columns.Any(x => x.Header == column.ColumnName && !x.PercentageVariance && !column.ColumnName.Contains(UNICODE_DELTA)))
if (ColumnIsValidInQuery(query, column.ColumnName))
{
// CR: move repeated actions into a shared method
// you can replace all of this with a single method call
//var period = column.ColumnName.Substring(0, column.ColumnName.IndexOf("|"));
//var columnName = column.ColumnName.Substring(column.ColumnName.IndexOf("|") + 1);
//var newColumnName = $"{period}|{"% Cont"} ({columnName})";
//CR: replace in-line string expressions with constants
var newColumnName = ComputeNewColumnName(column.ColumnName, PROPERTY_NAME_PercentCont);
foreach (DataRow summaryRow in summary.Rows)
{
//CR: you can declare summaryAmount and set the value at the same time
//var summaryAmount = 0.0;
//CR: replace in-line numbers with constants
summaryRow[newColumnName] = FinancialStatements.PAndLConstants.SummaryRows.Contains(summaryRow[CODE_PROPERTY_NAME]) ? PL_MIN : PL_MAX;
var summaryAmount = (double)summaryRow[column.ColumnName];
foreach (DataRow detailRow in details.Rows)
{
if (DetailRowIsPartOfSummaryRowGroup(detailRow, summaryRow, levelCount))
{
detailRow[newColumnName] = (double)detailRow[column.ColumnName] == 0 ? 0 : (double)detailRow[column.ColumnName] / summaryAmount * 100;
}
}
}
}
}
}
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);
}
}
//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 AddPercentageRevenue(DataTable details, DataTable summary, QueryXml query, string lookupColumn)
{
//CR: It would be quicker to cache the set of acceptable columns first and then we could simplify that if clause
//var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(details.Columns, query);
//foreach (DataColumn column in details.Columns)
//{
// if (!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName) && query.Columns.Any(c => c.Header == column.ColumnName && !c.PercentageVariance && !column.ColumnName.Contains(UNICODE_DELTA)))
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(details.Columns, query);
foreach (DataColumn column in details.Columns)
{
if (ColumnIsValidInQuery(query, column.ColumnName))
{
var newColumnName = ComputeNewColumnName(column.ColumnName, PROPERTY_NAME_PercentRev);
var revenue = ComputeSum <double>(summary, column.ColumnName, lookupColumn, PROPERTY_NAME_Revenue);
foreach (DataRow row in summary.Rows)
{
//CR: use an in line expression to set the value
row[newColumnName] = revenue == 0 ? 0 : (double)row[column.ColumnName] / (double)revenue * 100;
}
//CR: this code block has been duplicated - are you sure this is what you wanted to do?
//foreach (DataRow row in details.Rows)
//{
// row[newColumnName] = default(double);
// if ((double)revenue != 0)
// row[newColumnName] = (double)row[column.ColumnName] / (double)revenue * 100;
//}
}
}
}
public static void AddPercentageRevenue(DataTable details, DataTable summary, QueryXml query, string lookupColumn)
{
foreach (DataColumn column in details.Columns)
{
if (!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName) && query.Columns.Any(c => c.Header == column.ColumnName && !c.PercentageVariance && !column.ColumnName.Contains(UNICODE_DELTA)))
{
var period = column.ColumnName.Substring(0, column.ColumnName.IndexOf("|"));
var columnName = column.ColumnName.Substring(column.ColumnName.IndexOf("|") + 1);
var newColumnName = $"{period}|{"% Rev"} ({columnName})";
var revenue = summary.Compute($"sum([{column.ColumnName}])", $"[{lookupColumn}] = 'Revenue' ");
foreach (DataRow row in summary.Rows)
{
row[newColumnName] = default(double);
if ((double)revenue != 0)
{
row[newColumnName] = (double)row[column.ColumnName] / (double)revenue * 100;
}
}
foreach (DataRow row in details.Rows)
{
row[newColumnName] = default(double);
if ((double)revenue != 0)
{
row[newColumnName] = (double)row[column.ColumnName] / (double)revenue * 100;
}
}
}
}
}
public static void AddPercentageContribution(DataTable details, DataTable summary, QueryXml query, int levelCount = 1)
{
foreach (DataColumn column in details.Columns)
{
if (!ColumnsToIgnoreForCalculations(query).Contains(column.ColumnName) && query.Columns.Any(x => x.Header == column.ColumnName && !x.PercentageVariance && !column.ColumnName.Contains(UNICODE_DELTA)))
{
var period = column.ColumnName.Substring(0, column.ColumnName.IndexOf("|"));
var columnName = column.ColumnName.Substring(column.ColumnName.IndexOf("|") + 1);
var newColumnName = $"{period}|{"% Cont"} ({columnName})";
foreach (DataRow summaryRow in summary.Rows)
{
var summaryAmount = 0.0;
summaryRow[newColumnName] = FinancialStatements.PAndLConstants.SummaryRows.Contains(summaryRow[CODE_PROPERTY_NAME]) ? 0 : 100;
summaryAmount = (double)summaryRow[column.ColumnName];
foreach (DataRow detailRow in details.Rows)
{
if (DetailRowIsPartOfSummaryRowGroup(detailRow, summaryRow, levelCount))
{
detailRow[newColumnName] = (double)detailRow[column.ColumnName] == 0 ? 0 : (double)detailRow[column.ColumnName] / summaryAmount * 100;
}
}
}
}
}
}
public static void AddPercentageContribution(DataTable details, DataTable summary, QueryXml query, int levelCount = 1)
{
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(details.Columns, query);
foreach (DataColumn column in columnsThatCanBeCalculated)
{
if (ColumnIsValidInQuery(query, column.ColumnName))
{
var newColumnName = ComputeNewColumnName(column.ColumnName, PROPERTY_NAME_PercentCont);
foreach (DataRow summaryRow in summary.Rows)
{
summaryRow[newColumnName] = FinancialStatements.PAndLConstants.SummaryRows.Contains(summaryRow[CODE_PROPERTY_NAME]) ? PL_MIN : PL_MAX;
var summaryAmount = (double)summaryRow[column.ColumnName];
foreach (DataRow detailRow in details.Rows)
{
if (DetailRowIsPartOfSummaryRowGroup(detailRow, summaryRow, levelCount))
{
detailRow[newColumnName] = (double)detailRow[column.ColumnName] == 0 ? 0 : (double)detailRow[column.ColumnName] / summaryAmount * 100;
}
}
}
}
}
}
public static void AddPercentageRevenue(DataTable details, DataTable summary, QueryXml query, string lookupColumn)
{
var columnsThatCanBeCalculated = ColumnsThatCanBeCalculated(details.Columns, query);
foreach (DataColumn column in details.Columns)
{
if (ColumnIsValidInQuery(query, column.ColumnName))
{
var newColumnName = ComputeNewColumnName(column.ColumnName, PROPERTY_NAME_PercentRev);
var revenue = ComputeSum <double>(summary, column.ColumnName, lookupColumn, PROPERTY_NAME_Revenue);
foreach (DataRow row in summary.Rows)
{
row[newColumnName] = revenue == 0 ? 0 : (double)row[column.ColumnName] / (double)revenue * 100;
}
}
}
}
public SampleCallingClass(DataTable summaryTable, DataTable detailsTable, QueryXml query)
{
SummaryTable = summaryTable;
DetailsTable = detailsTable;
Query = query;
}
