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