public void LoadAnnualSummary()
{
LoadCarSummary();
LoadAndUpdateCurrentAccount();
_SheetAnnualSummary = Package.Workbook.Worksheets[eWorkSheetLabels.AnnualSummary];
var chExpences = Common.GetColumnHeaders(_SheetAnnualSummary, 1, eDescriptionKeys.AnnualSummary.Expenses, 2);
var chInvoices = Common.GetColumnHeaders(_SheetAnnualSummary, 1, eDescriptionKeys.AnnualSummary.Invoices);
#region Get the expences
int ExpenseStartRownumber = Common.GetRownumberForKey(_SheetAnnualSummary, eDescriptionKeys.AnnualSummary.Expenses, 1) + 3;
int ExpenseTotalRowNumer = Common.GetRownumberForKey(_SheetAnnualSummary, eDescriptionKeys.Totals, 2, ExpenseStartRownumber);
List <SummaryExpense> localExpense = new List <SummaryExpense>();
if (ExpenseTotalRowNumer - ExpenseStartRownumber != 0)
{
for (int rownum = ExpenseStartRownumber; rownum < ExpenseTotalRowNumer; rownum++)
{
SummaryExpense expense = new SummaryExpense(_SheetAnnualSummary, chExpences, rownum);
if (!string.IsNullOrEmpty(expense.Description.Value))
{
localExpense.Add(expense);
}
}
}
Expenses = localExpense;
#endregion
#region get the invoices
int InvoiceStartRownumber = Common.GetRownumberForKey(_SheetAnnualSummary, eDescriptionKeys.AnnualSummary.Invoices, 1) + 2;
int InvoiceTotalRowNumer = Common.GetRownumberForKey(_SheetAnnualSummary, eDescriptionKeys.Totals, 2, InvoiceStartRownumber + 1);
List <SummaryInvoice> localInvoices = new List <SummaryInvoice>();
if (InvoiceTotalRowNumer - InvoiceStartRownumber != 0)
{
for (int rownum = InvoiceStartRownumber; rownum < InvoiceTotalRowNumer; rownum++)
{
localInvoices.Add(new SummaryInvoice(_SheetAnnualSummary, chInvoices, rownum));
}
}
Invoices = localInvoices;
#endregion
}
private void UpsertAnnualSummary()
{
string stylenameHyperlink = "HyperLink";
SummaryExpense FirstExpense = null;
if (Expenses.Any())
{
FirstExpense = Expenses.First();
}
SummaryInvoice FirstInvoice = Invoices.First();
Dictionary <string, ColumnHeader> chExpences = Common.GetColumnHeaders(_SheetAnnualSummary, 1, eDescriptionKeys.AnnualSummary.Expenses, 2);
Dictionary <string, ColumnHeader> chInvoices = Common.GetColumnHeaders(_SheetAnnualSummary, 1, eDescriptionKeys.AnnualSummary.Invoices);
Dictionary <string, ColumnHeader> chSummary = new Dictionary <string, ColumnHeader>();
int LastExpenseColumnNumber = Common.GetLastColumnNumber(chExpences);
int LastInvoiceColumnNumber = Common.GetLastColumnNumber(FirstInvoice);
int rownum = 4;
Common.DeleteRows(_SheetAnnualSummary, 4);
#region Add Summary
var caRows = CurrentAccountRows.Where(w => !w.IsDivider &&
!w.IsMonthlySummary &&
!w.IsStartingBalence &&
!w.IsInvoicePaid &&
!w.IsDontMap).ToList();
caRows.Sort(delegate(CurrentAccount x, CurrentAccount y)
{
if (x.Description == null && y.Description == null)
{
return(0);
}
else if (x.Description == null)
{
return(-1);
}
else if (y.Description == null)
{
return(1);
}
else
{
return(x.Description.Value.CompareTo(y.Description.Value));
}
});
#region Build summary Headers lookup
int summaryColumnHeaderNumber = 0;
List <string> SummaryHeaders = "Date,Payee,Reconsiliation,Total Spent,VAT,Dividends,Salary,Expenses,PAYE".Split(',').ToList();
List <string> SummaryHeaders2 = new List <string>();
foreach (CreditCard item in CreditCardRows.Where(w => !SummaryHeaders.Any(a => a.Equals(w.Category.Value, StringComparison.CurrentCultureIgnoreCase))))
{
if (item.Category == null || string.IsNullOrEmpty(item.Category.Value))
{
if (!SummaryHeaders2.Contains(UnknonwnCategory))
{
SummaryHeaders2.Add(UnknonwnCategory);
}
}
else if (!SummaryHeaders2.Contains(item.Category.Value))
{
SummaryHeaders2.Add(item.Category.Value);
}
}
foreach (CurrentAccount item in caRows.Where(w => !SummaryHeaders.Any(a => a.Equals(w.Category.Value, StringComparison.CurrentCultureIgnoreCase))))
{
if (item.Description != null &&
item.Description.Value.Equals(Resources.Category_CommercialCard, StringComparison.CurrentCultureIgnoreCase))
{
continue;
}
if (item.Category == null || string.IsNullOrEmpty(item.Category.Value))
{
if (!SummaryHeaders2.Contains(UnknonwnCategory))
{
SummaryHeaders2.Add(UnknonwnCategory);
}
item.Category = new ColumnString()
{
Value = UnknonwnCategory
};
}
else if (!SummaryHeaders2.Contains(item.Category.Value))
{
SummaryHeaders2.Add(item.Category.Value);
}
}
SummaryHeaders2.Sort();
SummaryHeaders.AddRange(SummaryHeaders2);
foreach (string item in SummaryHeaders)
{
summaryColumnHeaderNumber++;
chSummary.Add(item.Trim(), new ColumnHeader()
{
Header = item.Trim(), ColumnNumber = summaryColumnHeaderNumber
});
}
#endregion
//add summary headers to sheet
Common.SetHeaders(_SheetAnnualSummary, rownum, chSummary);
#region add summary
int summaryColumnsCount = chSummary.Count();
foreach (CurrentAccount currentAccount in caRows.Where(w => !w.IsDivider &&
!w.IsMonthlySummary &&
!w.IsStartingBalence &&
!w.IsInvoicePaid &&
!w.IsDontMap &&
w.Debit.Value != 0))
{
rownum++;
Common.UpdateCellDate(_SheetAnnualSummary, rownum, new ColumnDateTime()
{
ColumnNumber = 1, Value = currentAccount.Date.Value
});
Common.UpdateCellString(_SheetAnnualSummary, rownum, new ColumnString()
{
ColumnNumber = 2, Value = currentAccount.Description.Value
});
if (!currentAccount.IsCreditCard)
{
int colnum = chSummary.Single(w => w.Key.Equals(currentAccount.Category.Value, StringComparison.CurrentCultureIgnoreCase)).Value.ColumnNumber;
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, new ColumnDecimal()
{
ColumnNumber = colnum, Value = currentAccount.Debit.Value
});
}
else if (currentAccount.CreditCardTransactionSummary != null)
{
foreach (TransactionSummary ts in currentAccount.CreditCardTransactionSummary)
{
if (ts.Description == null || string.IsNullOrEmpty(ts.Description))
{
ts.Description = UnknonwnCategory;
}
int colnum = chSummary.Single(w => w.Key.Equals(ts.Description, StringComparison.CurrentCultureIgnoreCase)).Value.ColumnNumber;
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, new ColumnDecimal()
{
ColumnNumber = colnum, Value = ts.Value
});
}
}
Common.AddFormulaDecimal(_SheetAnnualSummary, rownum, 3, $"ABS(D{rownum})-ABS({currentAccount.Debit.Value})");
Common.AddSumFormula(_SheetAnnualSummary, rownum, 4, rownum, 5, rownum, summaryColumnsCount);
}
#endregion
//add summary Totals
rownum++;
for (int i = 3; i <= chSummary.Count(); i++)
{
Common.SetTotal(_SheetAnnualSummary, rownum, 5, i);
}
Common.SetRowColour(_SheetAnnualSummary, rownum, chSummary.Count(), Common.Colours.TotalsColour, true);
#endregion
#region Add expenses
rownum += 3;
//Add header
Common.UpdateCellString(_SheetAnnualSummary, rownum,
new ColumnString()
{
ColumnNumber = 1, Value = eDescriptionKeys.AnnualSummary.Expenses
},
"",
true);
//////
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum,
new ColumnString()
{
ColumnNumber = chExpences[Resources.ColumnHeader_VAT].ColumnNumber,
Value = Resources.IfApplicable
},
"",
false);
Common.SetRowColour(_SheetAnnualSummary, rownum, LastExpenseColumnNumber, Common.Colours.HeaderColour, true);
//////
rownum++;
int firstExpenseRow = rownum;
Common.SetHeaders(_SheetAnnualSummary, rownum, chExpences);
foreach (SummaryExpense expense in Expenses)
{
rownum++;
Common.UpdateCellDate(_SheetAnnualSummary, rownum, expense.Date);
Common.UpdateCellString(_SheetAnnualSummary, rownum, expense.Description);
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, expense.Value);
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, expense.VAT);
var SumAddress = new ExcelAddress(rownum, 3, rownum, 3);
if (expense.IsExpenseRefund)
{
_SheetAnnualSummary.Cells[SumAddress.Address].Value = 0;
}
else
{
var SumRange = new ExcelAddress(rownum, 4, rownum, chExpences.Count());
_SheetAnnualSummary.Cells[SumAddress.Address].Formula = $"SUM({SumRange.Address})";
}
}
rownum += 2;
//Expenses Total row
_SheetAnnualSummary.Cells[rownum, chExpences[Resources.ColumnHeader_Description].ColumnNumber].Value = eDescriptionKeys.Totals;
for (int i = 3; i <= chExpences.Count(); i++)
{
Common.SetTotal(_SheetAnnualSummary, rownum, firstExpenseRow, i);
}
ExcelAddress TotalOwedAddress = new ExcelAddress(rownum, 4, rownum, 4);
ExcelAddress TotalExpenseAddress = new ExcelAddress(rownum, 3, rownum, 3);
string totalOwedFormula = $"{TotalExpenseAddress}+{_SheetAnnualSummary.Cells[TotalOwedAddress.Address].Formula}";
_SheetAnnualSummary.Cells[TotalOwedAddress.Address].Formula = totalOwedFormula;
Common.SetRowColour(_SheetAnnualSummary, rownum, LastExpenseColumnNumber, Common.Colours.TotalsColour, true);
rownum += 2;
#endregion
#region Add Invoices
Common.UpdateCellString(_SheetAnnualSummary, rownum,
new ColumnString()
{
ColumnNumber = 1, Value = eDescriptionKeys.AnnualSummary.Invoices
},
"",
true);
rownum++;
//Set the Invoice header
Common.SetHeaders(_SheetAnnualSummary, rownum, chInvoices);
//set the expense data
int FirstInvoiceRow = rownum + 1;
foreach (SummaryInvoice invoice in Invoices)
{
if (string.IsNullOrEmpty(invoice.Customer.Value))
{
continue;
}
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, invoice.Customer);
Common.UpdateHyperLink(_SheetAnnualSummary, rownum, invoice.InvoiceName, invoice.InvoiceNameHyperLink, stylenameHyperlink, Package.File.DirectoryName);
Common.UpdateCellDate(_SheetAnnualSummary, rownum, invoice.InvoiceDate);
Common.UpdateCellDate(_SheetAnnualSummary, rownum, invoice.InvoicePaid);
Common.UpdateCellInt(_SheetAnnualSummary, rownum, invoice.DaysToPay);
Common.UpdateCellInt(_SheetAnnualSummary, rownum, invoice.HoursInvoiced);
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, invoice.DaysInvoiced);
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, invoice.InvoiceAmount);
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, invoice.TotalPaid);
Common.UpdateCellDecimal(_SheetAnnualSummary, rownum, invoice.DayRate);
}
rownum += 2;
////Invoice Total Row
_SheetAnnualSummary.Cells[rownum, chInvoices[Resources.ColumnHeader_Customer].ColumnNumber].Value = eDescriptionKeys.Totals;
Common.SetTotal(_SheetAnnualSummary, rownum, FirstInvoiceRow, chInvoices[SalesInvoicesColumnheaderText.HoursInvoiced].ColumnNumber);
Common.SetTotal(_SheetAnnualSummary, rownum, FirstInvoiceRow, chInvoices[Resources.ColumnHeader_DaysInvoiced].ColumnNumber);
Common.SetTotal(_SheetAnnualSummary, rownum, FirstInvoiceRow, chInvoices[Resources.ColumnHeader_InvoiceAmount].ColumnNumber);
Common.SetTotal(_SheetAnnualSummary, rownum, FirstInvoiceRow, chInvoices[Resources.ColumnHeader_TotalPaid].ColumnNumber);
Common.SetRowColour(_SheetAnnualSummary, rownum, LastInvoiceColumnNumber, Common.Colours.TotalsColour, true);
#endregion
#region Add Travel Subsitance Summary
int colText = chInvoices[SalesInvoicesColumnheaderText.HoursInvoiced].ColumnNumber;
int colValue = chInvoices[SalesInvoicesColumnheaderText.DaysInvoiced].ColumnNumber;
string colLetterValue = chInvoices[SalesInvoicesColumnheaderText.DaysInvoiced].GetColumnLetter();
int availableDays = 252;
int subsistanceDays = caRows.Where(w => w.Category.Value.Equals(Resources.Summary_Subsistence)).GroupBy(g => g.Date).ToArray().Count();
rownum += 2;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_InvoicedDays);
Common.AddFormulaDecimal(_SheetAnnualSummary, rownum, colValue, $"={colLetterValue}{rownum - 2}");
int invoicedDaysRow = rownum;
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_AvailableDays);
Common.UpdateCellInt(_SheetAnnualSummary, rownum, colValue, availableDays, false, 0);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_PercentageWorked);
Common.AddFormulaPercentage(_SheetAnnualSummary, rownum, colValue
, rownum - 2, colLetterValue
, rownum - 1, colLetterValue);
rownum += 2;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_DaysDriven);
Common.AddFormula(_SheetAnnualSummary, rownum, colValue, $"='{Resources.WorkSheetLabel_CarMilage}'!L4", 0);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_PercentageDriven);
Common.AddFormulaPercentage(_SheetAnnualSummary, rownum, colValue
, rownum - 1, colLetterValue
, invoicedDaysRow, colLetterValue);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_DaysTrain);
Common.AddFormula(_SheetAnnualSummary, rownum, colValue, $"=RoundUp({colLetterValue}{invoicedDaysRow} - {colLetterValue}{rownum - 2},0)", 0);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_PercentageTrain);
Common.AddFormulaPercentage(_SheetAnnualSummary, rownum, colValue
, rownum - 1, colLetterValue
, invoicedDaysRow, colLetterValue);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_DaysTraveled);
Common.AddFormula(_SheetAnnualSummary, rownum, colValue, $"={colLetterValue}{rownum - 2} + {colLetterValue}{rownum - 4}", 0);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_PercentageTraveled);
Common.AddFormulaPercentage(_SheetAnnualSummary, rownum, colValue
, rownum - 1, colLetterValue
, invoicedDaysRow, colLetterValue);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_DaysSubsistanceCard);
Common.UpdateCellInt(_SheetAnnualSummary, rownum, colValue, subsistanceDays, false, 0);
rownum++;
Common.UpdateCellString(_SheetAnnualSummary, rownum, colText, Resources.Summary_DaysSubsistanceCash);
Common.AddFormula(_SheetAnnualSummary, rownum, colValue, $"=RoundUp({colLetterValue}{invoicedDaysRow} - {colLetterValue}{rownum - 1},0)", 0);
#endregion
}
