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