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