private void LoadVatPeriodAccruals(WSCashFlow ws)
{
short yearNumber = 0;
curCol = firstCol;
var vat_accruals = dataContext.VatPeriodAccruals;
foreach (vwFlowVatPeriodAccrual vat_period in vat_accruals)
{
if (yearNumber == 0)
{
yearNumber = vat_period.YearNumber;
}
if (yearNumber != vat_period.YearNumber)
{
yearNumber = vat_period.YearNumber;
curCol++;
}
ws.Cells[curRow, curCol].Value = (decimal)ws.Cells[curRow, curCol].Value + vat_period?.HomeSales;
ws.Cells[curRow + 1, curCol].Value = (decimal)ws.Cells[curRow + 1, curCol].Value + vat_period?.HomePurchases;
ws.Cells[curRow + 2, curCol].Value = (decimal)ws.Cells[curRow + 2, curCol].Value + vat_period?.ExportSales;
ws.Cells[curRow + 3, curCol].Value = (decimal)ws.Cells[curRow + 3, curCol].Value + vat_period?.ExportPurchases;
ws.Cells[curRow + 4, curCol].Value = (decimal)ws.Cells[curRow + 4, curCol].Value + vat_period?.HomeSalesVat;
ws.Cells[curRow + 5, curCol].Value = (decimal)ws.Cells[curRow + 5, curCol].Value + vat_period?.HomePurchasesVat;
ws.Cells[curRow + 6, curCol].Value = (decimal)ws.Cells[curRow + 6, curCol].Value + vat_period?.ExportSalesVat;
ws.Cells[curRow + 7, curCol].Value = (decimal)ws.Cells[curRow + 7, curCol].Value + vat_period?.ExportPurchasesVat;
ws.Cells[curRow + 8, curCol].Value = (decimal)ws.Cells[curRow + 8, curCol].Value + vat_period?.VatDue;
curCol++;
}
}
private void VatDetailCodes(WSCashFlow ws, bool includeAdjustments = true)
{
int row = curRow;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatHomeSales;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatHomePurchases;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatExportSales;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatExportPurchases;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatHomeSalesVat;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatHomePurchasesVat;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatExportSalesVat;
ws.Cells[++row, 1].Value = Properties.Resources.TextVatExportPurchasesVat;
if (includeAdjustments)
{
ws.Cells[++row, 1].Value = Properties.Resources.TextVatAdjustment;
}
ws.Cells[++row, 1].Value = Properties.Resources.TextVatDue;
ws.Cells[row, 1].EntireRow.Font.Bold = true;
ws.Cells[row, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[row, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
ws.Cells[row, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[row, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
}
private void LoadBalanceSheet(WSCashFlow ws)
{
var balance_sheet = dataContext.BalanceSheet;
if (balance_sheet.Count() == 0)
{
return;
}
curRow += 2;
ws.Cells[curRow, 1].Value = Properties.Resources.TextBalanceSheet;
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
if (!Greyscale)
{
ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color = Color.Red;
ws.Cells[curRow, 1].EntireRow.Cells.Font.Color = Color.White;
ws.Cells[curRow, 3].Font.Color = Color.White;
}
int startRow = curRow;
int offset = 1;
int yearCol = firstCol;
string assetName = string.Empty;
foreach (var entry in balance_sheet)
{
if (entry.AssetName != assetName)
{
if (assetName != string.Empty)
{
ws.Cells[curRow, curCol].Formula = $"={Column(curCol - offset)}{curRow}";
}
curRow++;
assetName = entry.AssetName;
ws.Cells[curRow, 1].Value = entry.AssetCode;
ws.Cells[curRow, 2].Value = entry.AssetName;
curCol = firstCol;
yearCol = firstCol;
}
if (((curCol - yearCol) % 12 == 0) && (curCol != yearCol))
{
ws.Cells[curRow, curCol].Formula = $"={Column(curCol - offset)}{curRow}";
curCol++;
yearCol = curCol;
}
ws.Cells[curRow, curCol].Value = entry.Balance;
curCol++;
}
ws.Cells[curRow, curCol].Formula = $"={Column(curCol - offset)}{curRow}";
curRow++;
ws.Cells[curRow, 1].Value = Properties.Resources.TextCapital;
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
ws.Cells[curRow, 1].EntireRow.Locked = true;
yearCol = firstCol;
for (int curCol = firstCol; curCol <= lastCol + 1; curCol++)
{
if (((curCol - yearCol) % 12 == 0) && (curCol != yearCol))
{
ws.Cells[curRow, curCol].Formula = $"={Column(curCol - offset)}{curRow}";
yearCol = curCol + 1;
}
else
{
ws.Cells[curRow, curCol].Formula = $"=SUM({Column(curCol)}{startRow + 1}:{Column(curCol)}{curRow - 1})";
}
}
}
private void LoadVatPeriodTotals(WSCashFlow ws, bool includeActivePeriods = false, bool includeTaxAccruals = false)
{
curRow += 2;
ws.Cells[curRow, 1].Value = $"{Properties.Resources.TextVatDueTitle} {Properties.Resources.TextTotals}";
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
if (!Greyscale)
{
ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color = Color.Orchid;
ws.Cells[curRow, 1].EntireRow.Cells.Font.Color = Color.Black;
ws.Cells[curRow, 3].Font.Color = Color.White;
}
VatDetailCodes(ws, false);
int startCol = firstCol;
short yearNumber = 0;
curRow++;
var vat_totals = dataContext.VatPeriodTotals;
foreach (vwFlowVatPeriodTotal vat_period in vat_totals)
{
if (yearNumber == 0)
{
curCol = startCol;
yearNumber = vat_period.YearNumber;
}
if (yearNumber != vat_period.YearNumber)
{
for (int offset = 0; offset < 9; offset++)
{
ws.Cells[curRow + offset, curCol].Formula = $"=SUM({Column(startCol)}{curRow + offset}:{Column(curCol - 1)}{curRow + offset})";
}
yearNumber = vat_period.YearNumber;
curCol++;
startCol = curCol;
}
if (includeActivePeriods || (vat_period.StartOn < ActiveDate))
{
ws.Cells[curRow, curCol].Value = vat_period?.HomeSales;
ws.Cells[curRow + 1, curCol].Value = vat_period?.HomePurchases;
ws.Cells[curRow + 2, curCol].Value = vat_period?.ExportSales;
ws.Cells[curRow + 3, curCol].Value = vat_period?.ExportPurchases;
ws.Cells[curRow + 4, curCol].Value = vat_period?.HomeSalesVat;
ws.Cells[curRow + 5, curCol].Value = vat_period?.HomePurchasesVat;
ws.Cells[curRow + 6, curCol].Value = vat_period?.ExportSalesVat;
ws.Cells[curRow + 7, curCol].Value = vat_period?.ExportPurchasesVat;
ws.Cells[curRow + 8, curCol].Value = vat_period?.VatDue;
}
else
{
ws.Range[$"{Column(curCol)}{curRow}:{Column(curCol)}{curRow + 8}"].Value = 0;
}
curCol++;
}
for (int offset = 0; offset < 9; offset++)
{
ws.Cells[curRow + offset, curCol].Formula = $"=SUM({Column(startCol)}{curRow + offset}:{Column(curCol - 1)}{curRow + offset})";
}
if (includeTaxAccruals)
{
LoadVatPeriodAccruals(ws);
}
curRow += 8;
}
private void LoadBankBalances(WSCashFlow ws)
{
var bankAccounts = dataContext.BankAccounts;
if (bankAccounts.Count() == 0)
{
return;
}
curRow += 2;
ws.Cells[curRow, 1].Value = Properties.Resources.TextClosingBalances;
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
if (!Greyscale)
{
ws.Cells[curRow, 1].EntireRow.Cells.Interior.Color = Color.Black;
ws.Cells[curRow, 1].EntireRow.Cells.Font.Color = Color.White;
ws.Cells[curRow, 3].Font.Color = Color.White;
}
int startRow = curRow;
foreach (Data.vwBankAccount bankAccount in bankAccounts)
{
short yearNumber = 0;
curRow++;
ws.Cells[curRow, 1].Value = bankAccount.CashAccountName;
var balances = dataContext.BankBalances(bankAccount.CashAccountCode);
foreach (Data.fnFlowBankBalancesResult balance in balances)
{
if (yearNumber == 0)
{
curCol = firstCol;
yearNumber = balance.YearNumber;
}
if (yearNumber != balance.YearNumber)
{
ws.Cells[curRow, curCol].Formula = $"={Column(curCol-1)}{curRow}";
yearNumber = balance.YearNumber;
curCol++;
}
if (balance.Balance != null)
{
ws.Cells[curRow, curCol].Value = balance?.Balance;
curCol++;
}
else
{
curCol++;
}
}
ws.Cells[curRow, curCol].Formula = $"={Column(curCol - 1)}{curRow}";
ws.Cells[curRow, 1].EntireColumn.Locked = true;
}
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
curRow++;
ws.Cells[curRow, 1].Value = Properties.Resources.TextCompanyBalance;
ws.Cells[curRow, 1].EntireRow.Font.Bold = true;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlThin;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
ws.Cells[curRow, 1].EntireRow.Cells.Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThick;
ws.Cells[curRow, 1].EntireRow.Locked = true;
for (int curCol = firstCol; curCol <= lastCol; curCol++)
{
ws.Cells[curRow, curCol].Formula = $"=SUM({Column(curCol)}{startRow + 1}:{Column(curCol)}{curRow - 1})";
}
}
