Esempio n. 1
0
        //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);
                }
            }
        }
Esempio n. 2
0
        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);
                }
            }
        }
Esempio n. 3
0
        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);
            }
        }