private void FillTargetAndSpent(ExcelWorksheet budgetReport, int[] totalYears, YearlyBudgetAndCost budgetAndCost,
string[] budgetTypes, List <YearlyDataModel> yearlyInvestment)
{
var currencyFormat = "_-$* #,##0.00_-;-$* #,##0.00_-;_-$* \"-\"??_-;_-@_-";
var totalYearsCount = totalYears.Count();
DataTable viewTable = new DataTable();
viewTable.Columns.Add();
viewTable.Columns.Add();
DataRow totalTargetRow = viewTable.NewRow();
DataRow totalSpentRow = viewTable.NewRow();
var lastRow = budgetReport.Dimension.End.Row;
for (int i = 0; i < totalYearsCount; i++)
{
viewTable.Columns.Add($"{i + 2}", typeof(double));
totalTargetRow[i + 2] = 0;
totalSpentRow[i + 2] = 0;
}
totalTargetRow[0] = "Total";
totalTargetRow[1] = "Target";
totalSpentRow[0] = "Total";
totalSpentRow[1] = "Spent";
foreach (var budget in budgetTypes)
{
// creating row for target
DataRow targetRow = viewTable.NewRow();
targetRow[0] = budget;
targetRow[1] = "Target";
var amounts = yearlyInvestment.Where(_ => _.BudgetName == budget).Select(p => p.Amount);
int count = 2;
foreach (var amount in amounts)
{
targetRow[count] = amount;
totalTargetRow[count] = amount + (double)totalTargetRow[count];
count++;
}
budgetReport.InsertRow(lastRow + 1, 1);
viewTable.Rows.Add(targetRow);
budgetReport.Cells[lastRow + 1, 1].LoadFromDataTable(viewTable, false);
budgetReport.Cells[lastRow + 1, 1, lastRow + 1, totalYearsCount + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
budgetReport.Cells[lastRow + 1, 1, lastRow + 1, totalYearsCount + 2].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
// creating row for spend amount
DataRow spentRow = viewTable.NewRow();
spentRow[0] = budget;
spentRow[1] = "Spent";
var budgetOvershoot = new List <int>();
for (int i = 0; i < totalYearsCount; i++)
{
var sumOfCosts = budgetAndCost.CostDetails.Where(_ => _.Years == totalYears[i] && _.Budget == budget)
.Select(p => p.Cost).Sum();
var target = targetRow[i + 2] != null ? (double)targetRow[i + 2] : 0;
if (sumOfCosts > target)
{
budgetOvershoot.Add(i);
}
spentRow[i + 2] = sumOfCosts;
totalSpentRow[i + 2] = sumOfCosts + (double)totalSpentRow[i + 2];
}
budgetReport.InsertRow(lastRow + 1, 1);
viewTable.Rows.Remove(targetRow);
viewTable.Rows.Add(spentRow);
budgetReport.Cells[lastRow + 1, 1].LoadFromDataTable(viewTable, false);
void setColor(int overShoot)
{
budgetReport.Cells[lastRow + 1, overShoot + 3].Style.Font.Color.SetColor(Color.Red);
}
budgetOvershoot.ForEach(setColor);
viewTable.Rows.Remove(spentRow);
}
budgetReport.InsertRow(lastRow + 1, 2);
viewTable.Rows.Add(totalTargetRow);
viewTable.Rows.Add(totalSpentRow);
budgetReport.Cells[lastRow + 1, 1].LoadFromDataTable(viewTable, false);
budgetReport.Cells[lastRow + 1, 1, lastRow + 2, totalYearsCount + 2].Style.Fill.PatternType = ExcelFillStyle.Solid;
budgetReport.Cells[lastRow + 1, 1, lastRow + 2, totalYearsCount + 2].Style.Fill.BackgroundColor.SetColor(Color.LightGray);
for (int i = 0; i < totalYearsCount; i++)
{
if ((double)totalSpentRow.ItemArray[i + 2] > (double)totalTargetRow.ItemArray[i + 2])
{
budgetReport.Cells[lastRow + 2, i + 3].Style.Font.Color.SetColor(Color.Red);
}
}
budgetReport.Cells[lastRow + 1, 3, budgetReport.Dimension.End.Row, totalYearsCount + 2].Style.Numberformat.Format = currencyFormat;
budgetReport.Cells.AutoFitColumns();
}
public YearlyBudgetAndCost GetData(SimulationModel data, string[] budgetTypes)
{
var budgetYearView = new Hashtable();
var select =
"SELECT Years, Budget, Cost_ " +
" FROM Report_" + data.NetworkId
+ "_" + data.SimulationId + " WHERE BUDGET is not null";
try
{
var rawQueryForData = db.Database.SqlQuery <BudgetModel>(select).AsQueryable();
double sum = 0;
foreach (var row in rawQueryForData)
{
double.TryParse(row.Cost_.Value.ToString(), out double cost);
Hashtable yearView;
//[NOTE].. Filling up the hash table. Data is coming from the database in such a way
// that an Hashtable cannot be filled in one go and the checks are necessary.
if (!budgetYearView.Contains(row.Budget))
{
yearView = new Hashtable();
budgetYearView.Add(row.Budget, yearView);
}
else
{
yearView = (Hashtable)budgetYearView[row.Budget];
}
if (yearView.Contains(row.Years))
{
sum = (double)yearView[row.Years];
yearView.Remove(row.Years);
}
sum += cost;
yearView.Add(row.Years, sum);
costs.Add(new CostDetails
{
Cost = row.Cost_.Value,
Years = row.Years,
Budget = row.Budget
});
}
foreach (string item in budgetYearView.Keys)
{
if (!budgetTypes.Contains(item))
{
budgetYearView.Remove(item);
}
}
}
catch (SqlException ex)
{
HandleException.SqlError(ex, "Network or Simulation");
}
catch (OutOfMemoryException ex)
{
HandleException.OutOfMemoryError(ex);
}
var budgetReportDetails = new YearlyBudgetAndCost
{
BudgetForYear = budgetYearView,
CostDetails = costs
};
return(budgetReportDetails);
}
