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