Beispiel #1
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="wsSheet"></param>
        /// <param name="startRow"></param>
        /// <param name="movementsModel"></param>
        /// <param name="categories"></param>
        /// <param name="year"></param>
        /// <param name="justExtrations"></param>
        /// <returns></returns>
        public static ExcelTable CreateCategoriesMonthsAveragetest(ExcelWorksheet wsSheet, int startRow, List <MovementsViewModel> movementsModel, IEnumerable <string> categories,
                                                                   int year = 0, bool justExtrations = true)
        {
            categories = justExtrations ? ModelOperation.GetExtractionCategories(categories, movementsModel) : ModelOperation.GetIncomsCategories(categories, movementsModel);
            var newAverageColumn = new List <string>();

            var startTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, startRow, 0);

            categories = categories.OrderBy(c => c);
            var columns = new[] { "Month" };

            if (year > 0)
            {
                newAverageColumn.Add($"Year({year})");
            }
            else
            {
                newAverageColumn.Add("Years");
                var years = movementsModel.Select(mov => mov.DateTime.Year).Distinct();
                newAverageColumn.AddRange(years.Select(selectedYear => $"Year({selectedYear})"));
            }

            var newExcelColumn = columns.Concat(categories);
            // Calculate size of the table
            var excelColumns = newExcelColumn as string[] ?? newExcelColumn.ToArray();
            var endTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, excelColumns.Count() - 1);

            //give table Name
            var tableName = "CategoriesMonthAverage1";
            // Add table Headers
            var excelTable = CreateExcelTable(wsSheet, tableName, excelColumns, startTableCell, endTableCell, true);

            for (int month = 1; month <= 12; month++)
            {
                for (int column = 0; column < excelColumns.Count(); column++)
                {
                    var tableCell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, column);
                    if (excelColumns[column] == "Month")
                    {
                        var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
                        excelTable.WorkSheet.Cells[tableCell].Value = monthName;
                    }
                    else
                    {
                        excelTable.WorkSheet.Cells[tableCell].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");

                        var average = ModelOperation.AverageforCategory(movementsModel, excelColumns[column], year, month, justExtrations);
                        excelTable.WorkSheet.Cells[tableCell].Value = average;
                    }
                }
            }
            return(excelTable);
        }
Beispiel #2
0
        public static double AverageforCategory(IEnumerable <MovementsViewModel> movements, string category, int?year = null, int?month = null, bool justExtrations = true)
        {
            var monthAndYaerMovements = ModelOperation.GetMovementByMonthYear(movements, year == 0 ? null : year, month == 0 ? null : month);

            double average = 0;

            //TODO get average for year / month for the category
            var movementsByCategory = monthAndYaerMovements.Where(mov => mov.Category == category && Math.Abs(mov.Amount) > 0);

            movementsByCategory = ModelConverter.GetMovementsViewModelsByType(justExtrations, movementsByCategory);

            if (movementsByCategory != null && movementsByCategory.Any())
            {
                //Years average
                if (month == null && year == null)
                {
                    var yearResult = movementsByCategory.GroupBy(mv => mv.DateTime.Year).Select(mov => new { Year = mov.Key, sum = mov.Sum(p => p.Amount) });
                    average = !yearResult.Any() ? 0 : yearResult.Average(r => r.sum);
                }

                // Get the months average
                if (month == 0)
                {
                    var monthResult = movementsByCategory.GroupBy(mv => mv.DateTime.Month).Select(mov => new { Month = mov.Key, sum = mov.Sum(p => p.Amount) });
                    average = !monthResult.Any() ? 0 : monthResult.Average(r => r.sum);
                }
                // Get the average of a specific month
                if (month != 0 && month != null)
                {
                    var monthResult = movementsByCategory.GroupBy(mv => mv.DateTime.Month).Select(mov => new { Month = mov.Key, sum = mov.Sum(p => p.Amount) });
                    average = !monthResult.Any() ? 0 : monthResult.Average(r => r.sum);
                }

                //if (year != null && month == 0)
                //{
                //    var monthResult = movementsByCategory.GroupBy(mv => mv.DateTime.Day).Select(mov => new { Month = mov.Key, sum = mov.Sum(p => p.Amount) });
                //    average = !monthResult.Any() ? 0 : monthResult.Average(r => r.sum);
                //}


                if (year != null && (month != null && month != 0))
                {
                    average = movementsByCategory.Average(m => m.Amount);
                }
            }
            //return Math.Abs(average);
            return(average);
        }
Beispiel #3
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="wsSheet"></param>
        /// <param name="excelPackage"></param>
        /// <param name="movementsModel"></param>
        /// <param name="categories"></param>
        /// <param name="sheetYear"></param>
        /// <param name="sheetTableName"></param>
        /// <param name="justExtrations"></param>
        /// <param name="startCell"></param>
        /// <returns></returns>
        public static List <ExcelTable> CreateExcelMonthSummaryTableFromMovementsViewModel(ExcelWorksheet wsSheet, List <MovementsViewModel> movementsModel, IEnumerable <string> categories,
                                                                                           int sheetYear = 0, string sheetTableName = null, bool justExtrations = true, string startCell = null)
        {
            int minYear;
            int maxYear;

            if (sheetYear > 0)
            {
                minYear = sheetYear;
                maxYear = sheetYear;
            }
            else
            {
                minYear = movementsModel.Min(mov => mov.DateTime.Year);
                maxYear = movementsModel.Max(mov => mov.DateTime.Year);
            }

            IEnumerable <string> newColumns = new[] { "Month", "Total" };

            categories = justExtrations ? ModelOperation.GetExtractionCategories(categories, movementsModel) : ModelOperation.GetIncomsCategories(categories, movementsModel);
            categories = categories.OrderBy(c => c);

            // and the new columns to the category
            categories = newColumns.Concat(categories);
            var categoriesUpdated = categories as string[] ?? categories.ToArray();

            string startTableCell = startCell ?? _startCell;

            // Create Excel table Header
            var endTableCellAddress       = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, categoriesUpdated.Count() - 1);
            var tableName                 = sheetTableName ?? "Tanble-";
            List <ExcelTable> excelTables = new List <ExcelTable>();

            for (int year = minYear; year <= maxYear; year++)
            {
                //give table Name
                tableName = string.Concat(tableName, year);

                //calculate Table sizes
                endTableCellAddress = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12, categoriesUpdated.Count() - 1);
                var excelTable = CreateExcelTable(wsSheet, tableName, categoriesUpdated, startTableCell, endTableCellAddress, true);

                // Set Excel table content
                for (int month = 1; month <= 12; month++)
                {
                    for (int i = 0; i < categoriesUpdated.Length; i++)
                    {
                        switch (categoriesUpdated[i])
                        {
                        case "Month":
                            var monthName = string.Concat(DateTimeFormatInfo.CurrentInfo.GetMonthName(month));
                            excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i)].Value = monthName;
                            break;

                        case "Total":
                            double totalCategory = ModelConverter.CategoriesMonthYearTotal(movementsModel, year, month, justExtrations);
                            excelTable.WorkSheet.Cells[ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i)].Value = totalCategory;
                            break;

                        default:
                            //Get summ for category
                            var    tablecell      = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, month, i);
                            double totalCategory1 = ModelOperation.GetTotalforCategory(movementsModel, categoriesUpdated[i], year, month, justExtrations);
                            excelTable.WorkSheet.Cells[tablecell].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");
                            //add value tu excel cell
                            wsSheet.Cells[tablecell].Value = totalCategory1;
                            //AddExcelCellValue(row, tableStartColumn, totalCategory1, wsSheet);
                            break;
                        }
                    }
                }
                startTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(startTableCell, 12 + 5, 0);
                excelTable.WorkSheet.Cells[wsSheet.Dimension.Address].AutoFitColumns();
                excelTables.Add(excelTable);
            }
            return(excelTables);
        }
Beispiel #4
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="movementsModel"></param>
        /// <param name="wsSheet"></param>
        /// <param name="categories"></param>
        /// <param name="year"></param>
        /// <param name="month"></param>
        /// <param name="justExtrations"></param>
        /// <returns></returns>
        public static ExcelTable CreateAverageForYearMonthDay(List <MovementsViewModel> movementsModel, ExcelWorksheet wsSheet, IEnumerable <string> categories,
                                                              int year = 0, int?month = 0, bool justExtrations = true)
        {
            categories = justExtrations ? ModelOperation.GetExtractionCategories(categories, movementsModel) : ModelOperation.GetIncomsCategories(categories, movementsModel);

            categories = categories.OrderBy(c => c);
            var columns = new[] { "Type" };

            var newAverageColumn = new List <string>();

            //to get the month average we need to specify if is jus one year or all the years
            IEnumerable <int> years = null;

            if (year > 0)
            {
                newAverageColumn.Add($"Year({year})");
            }
            else
            {
                newAverageColumn.Add("Years");
                years = movementsModel.Select(mov => mov.DateTime.Year).Distinct();
                newAverageColumn.AddRange(years.Select(selectedYear => $"Year({selectedYear})"));
            }

            newAverageColumn.Add("Month");
            newAverageColumn.Add("Day");

            var newExcelColumn = columns.Concat(categories);
            // Calculate size of the table

            var    excelColumns = newExcelColumn as string[] ?? newExcelColumn.ToArray();
            string endTableCell = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, newAverageColumn.Count(), excelColumns.Count() - 1);

            //give table Name
            var tableName = "CategoriesYearMonthDayAverage";
            // Add table Headers

            var excelTable = CreateExcelTable(wsSheet, tableName, excelColumns, _startCell, endTableCell, true);

            for (int row = 0; row < newAverageColumn.Count(); row++)
            {
                var rowValue = newAverageColumn[row];
                for (int column = 0; column < excelColumns.Count(); column++)
                {
                    var    category        = excelColumns[column];
                    string cellAdress      = ExcelHelpers.AddRowAndColumnToCellAddress(_startCell, row + 1, column);
                    double categoryAverage = 0;

                    if (category == "Type")
                    {
                        excelTable.WorkSheet.Cells[cellAdress].Value = newAverageColumn[row];
                    }
                    else
                    {
                        if (rowValue == "Years")
                        {
                            categoryAverage = ModelOperation.AverageforCategory(movementsModel, category, null, null, justExtrations);
                        }
                        else if (rowValue == "Month")
                        {
                            categoryAverage = years != null?ModelOperation.AverageforCategory(movementsModel, category, null, 0, justExtrations) :
                                                  ModelOperation.AverageforCategory(movementsModel, category, year, 0, justExtrations);
                        }
                        else
                        {
                            if (years != null)
                            {
                                foreach (var selectedYear in years)
                                {
                                    if (rowValue == $"Year({selectedYear})")
                                    {
                                        categoryAverage = ModelOperation.GetTotalforCategory(movementsModel, category, selectedYear, null, justExtrations);
                                    }
                                }
                            }
                            else
                            {
                                categoryAverage = ModelOperation.GetTotalforCategory(movementsModel, category, year, null, justExtrations);
                            }
                        }

                        excelTable.WorkSheet.Cells[cellAdress].Style.Numberformat.Format = ExcelHelpers.SetFormatToCell("Amount");

                        excelTable.WorkSheet.Cells[cellAdress].Value = categoryAverage;
                    }
                }
            }
            return(excelTable);
        }