public byte[] GenerateReport(LoanOffer loanOffer, bool isExcel, bool isShowDetails, string header)
{
var worksheet = _workbook.Worksheets[_workbook.Worksheets.ActiveSheetIndex];
worksheet.Name = "Loan Offer";
int row = 6;
int column = 1;
_workbook.ChangePalette(Color.FromArgb(197, 197, 197), 55);
_workbook.ChangePalette(Color.FromArgb(221, 221, 221), 54);
_workbook.ChangePalette(Color.FromArgb(123, 178, 36), 53);
HeaderReportGenerator.CreateHeader(worksheet, header, column - 1, column, 7);
worksheet.Cells.SetColumnWidth(0, 1);
worksheet.Cells.SetColumnWidth(1, 16);
worksheet.Cells.SetColumnWidth(2, 15);
CreateXlsHeader(worksheet, row, column);
var i = 0;
foreach (var item in loanOffer.Schedule)
{
row++;
i++;
worksheet.Cells[row, column].PutValue(item.Date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
worksheet.Cells[row, column + 1].PutValue("£" + FormattingUtils.FormatMiddle(item.LoanRepayment));
worksheet.Cells[row, column + 2].PutValue("£" + FormattingUtils.FormatMiddle(item.Interest));
worksheet.Cells[row, column + 3].PutValue(FormattingUtils.FormatMiddle(item.InterestRate * 100) + "%");
var fee = loanOffer.SetupFee > 0 && i == 1 ? loanOffer.SetupFee : 0;
if (item.Fees > 0)
{
fee += item.Fees;
}
var res = fee != 0 ? "£" + FormattingUtils.FormatMiddle(fee) : "-";
var res1 = loanOffer.SetupFee > 0 && i == 1 ? "*" : string.Empty;
worksheet.Cells[row, column + 4].PutValue(res + res1);
//worksheet.Cells.Merge(row, column + 5, 1, 3);
worksheet.Cells[row, column + 5].PutValue("£" + FormattingUtils.FormatMiddle(item.AmountDue));
SetCellStyle(worksheet, row, column, false);
}
row = CreateTotalBlock(loanOffer, row, column, worksheet);
if (loanOffer.Details.IsModified)
{
row++;
worksheet.Cells.Merge(row, column, 1, 7);
worksheet.Cells[row, column].PutValue("Offer was manually modified");
worksheet.Cells[row, column].Style.Font.IsBold = true;
}
if (isShowDetails)
{
CreateDetails(loanOffer.Details, row, column, worksheet);
}
return(ConvertFormat(_workbook, isExcel ? FileFormatType.Excel2003 : FileFormatType.Pdf));
}
public byte[] GenerateReport(LoanDetails loanDetails, bool isExcell, bool withErrors, string header)
{
int row = 6;
const int column = 1;
var worksheet = _workbook.Worksheets[_workbook.Worksheets.ActiveSheetIndex];
worksheet.Name = "Payment Schedule";
HeaderReportGenerator.CreateHeader(worksheet, header, column, column, 9);
CreateXlsHeader(worksheet, row, column, isExcell);
var filePath = System.Web.HttpContext.Current.Server.MapPath("~/Content/img/payment-to-customer.png");
foreach (var transaction in loanDetails.PacnetTransactions)
{
row++;
worksheet.Pictures.Add(row, column, filePath, 65, 25);
worksheet.Cells[row, column + 1].PutValue(transaction.PostDate.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
worksheet.Cells[row, column + 2].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Amount));
worksheet.Cells[row, column + 3].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Interest));
worksheet.Cells[row, column + 4].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Fees));
worksheet.Cells[row, column + 5].PutValue("-");
worksheet.Cells[row, column + 6].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Amount));
worksheet.Cells[row, column + 7].PutValue(transaction.StatusDescription);
worksheet.Cells[row, column + 8].PutValue(transaction.Description);
SetCellStyle(worksheet, row, column, false, isExcell);
}
filePath = System.Web.HttpContext.Current.Server.MapPath("~/Content/img/wizard-mark-completed.png");
foreach (var transaction in loanDetails.Transactions)
{
if (!withErrors && transaction.Status != "Done")
{
continue;
}
row++;
worksheet.Pictures.Add(row, column, filePath, 102, 30);
worksheet.Cells[row, column + 1].PutValue(transaction.PostDate.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
worksheet.Cells[row, column + 2].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.LoanRepayment));
worksheet.Cells[row, column + 3].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Interest));
worksheet.Cells[row, column + 4].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Fees + transaction.Rollover));
worksheet.Cells[row, column + 5].PutValue("-");
worksheet.Cells[row, column + 6].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Amount));
worksheet.Cells[row, column + 7].PutValue(transaction.StatusDescription);
worksheet.Cells[row, column + 8].PutValue(transaction.Description);
SetCellStyle(worksheet, row, column, false, isExcell);
}
filePath = System.Web.HttpContext.Current.Server.MapPath("~/Content/img/two_arrows.png");
foreach (var transaction in loanDetails.Rollovers)
{
row++;
worksheet.Pictures.Add(row, column, filePath, 130, 50);
worksheet.Cells[row, column + 1].PutValue(transaction.ExpiryDate);
worksheet.Cells[row, column + 2].PutValue("-");
worksheet.Cells[row, column + 3].PutValue("-");
worksheet.Cells[row, column + 4].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Payment));
worksheet.Cells[row, column + 5].PutValue("-");
worksheet.Cells[row, column + 6].PutValue("-");
worksheet.Cells[row, column + 7].PutValue("Roll over");
worksheet.Cells[row, column + 8].PutValue("");
SetCellStyle(worksheet, row, column, false, isExcell);
}
foreach (var transaction in loanDetails.Charges.Where(transaction => transaction.State != "Paid"))
{
row++;
worksheet.Pictures.Add(row, column, filePath, 130, 50);
worksheet.Cells[row, column + 1].PutValue(transaction.Date.ToString("dd/MM/yyyy", CultureInfo.InvariantCulture));
worksheet.Cells[row, column + 2].PutValue("-");
worksheet.Cells[row, column + 3].PutValue("-");
worksheet.Cells[row, column + 4].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Amount));
worksheet.Cells[row, column + 5].PutValue("-");
worksheet.Cells[row, column + 6].PutValue("-");
worksheet.Cells[row, column + 7].PutValue(transaction.State);
worksheet.Cells[row, column + 8].PutValue(transaction.Description);
SetCellStyle(worksheet, row, column, false, isExcell);
}
filePath = System.Web.HttpContext.Current.Server.MapPath("~/Content/img/calendarx32.png");
foreach (var transaction in loanDetails.Schedule.Where(transaction => (transaction.Status != "PaidEarly") && (transaction.Status != "PaidOnTime") && (transaction.Status != "Paid")))
{
row++;
worksheet.Pictures.Add(row, column, filePath, 120, 45);
worksheet.Cells[row, column + 1].PutValue(transaction.Date.ToString("dd/MM/yyyy",
CultureInfo.InvariantCulture));
worksheet.Cells[row, column + 2].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.LoanRepayment));
worksheet.Cells[row, column + 3].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.Interest));
worksheet.Cells[row, column + 4].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.LateCharges));
worksheet.Cells[row, column + 5].PutValue("-");
worksheet.Cells[row, column + 6].PutValue(FormattingUtils.FormatPoundsWidhDash(transaction.AmountDue));
worksheet.Cells[row, column + 7].PutValue(transaction.StatusDescription);
worksheet.Cells[row, column + 8].PutValue("");
SetCellStyle(worksheet, row, column, false, isExcell);
}
worksheet.Cells.SetColumnWidth(column - 1, 1);
worksheet.Cells.SetColumnWidth(column, 6);
worksheet.Cells.SetColumnWidth(column + 1, 16);
for (var item = 3; item < 10; ++item)
{
worksheet.AutoFitColumn(item);
}
return(ConvertFormat(_workbook, isExcell ? FileFormatType.Excel2003 : FileFormatType.Pdf));
}
