/// <summary>
///
/// </summary>
/// <param name="uwRelationshipId"></param>
/// <returns>Name of the file generated</returns>
public async Task <string> GenerateAsync(int Id)
{
try
{
if (!this.ReloadTemplate("MODEL"))
{
throw new Exception("Template could not be loaded :(");
}
/* Start Your Sheet Creation Code Here */
var standardStyle = new XSSFNPoiStyle()
{
Border = CellBorder.All, BorderStyle = BorderStyle.Thin, FontColor = IndexedColors.Red.AsXSSFColor(), BackgroundColor = IndexedColors.Green.AsXSSFColor()
};
var boldStyle = new XSSFNPoiStyle()
{
FillPattern = FillPattern.SolidForeground, FillForegroundColor = IndexedColors.PaleBlue.AsXSSFColor(), IsBold = true, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left, WrapText = true
};
string sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_Relationship] WHERE BidPoolId=@p0;SELECT GETDATE() as ThisDate, 'SQL LITERAL' as ThisString;";
var retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, Id);
DataTable firstResultSet = retDataSet.Tables[0];
foreach (DataRow row in firstResultSet.Rows)
{
sheet = workbook.CloneSheet(this.workbook.GetSheetIndex("MODEL"));
workbook.SetSheetName(workbook.NumberOfSheets - 1, row["RelationshipName"].ToString().AsSheetName());
sheet.SetCellValue(0, "D", "@DR1->");
sheet.SetCellValue(0, "E", row, "uwRelationshipId").SetCellStyle(standardStyle);
sheet.SetCellValue(1, "D", "@DR2->");
sheet.SetCellValue(1, "E", row, "Underwriter").SetCellStyle(standardStyle);
sheet.SetCellValue(2, "D", "@DR2->").SetCellStyle(standardStyle);;
sheet.SetCellValue(2, "E", row, "UPBSum").SetCellStyle(standardStyle.SetFormatStyle(FormatStyle.Currency));
sheet.SetCellValue(3, "D", "@DR3->").SetCellStyle(standardStyle.SetFormatStyle(FormatStyle.Default));
sheet.SetCellValue(3, "E", row, "CurrentStatus");
sheet.SetCellValue(4, "D", "@DR4->").SetCellStyle(boldStyle);
sheet.SetCellValue(4, "E", row, "ProFormaStatus").SetCellStyle(boldStyle);
sheet.SetCellValue(5, "D", "@DR5->").SetCellStyle(boldStyle);
sheet.SetCellValue(5, "E", row, "ExitStrategyText").SetCellStyle(boldStyle);
sheet.GetRow(5).Height = 1540;
sheet.SetColumnWidth("E", 9800);
}
workbook.RemoveSheetAt(this.workbook.GetSheetIndex("MODEL"));
SaveToFile(this.GeneratedFileName);
return(this.GeneratedFileName);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// This will generate a Business Asset Report for a BidPool or Relationship
/// </summary>
/// <param name="BidPoolId">If this is 0, then we will assume that we are going to use uwRelationshipId</param>
/// <param name="uwRelationshipId">If this is zero, then we will assume that we are going top use BidPoolId</param>
/// <returns>Name of the file generated</returns>
public async Task <string> GenerateAsync(int BidPoolId, int uwRelationshipId)
{
try
{
var ModelSheetName = "1";
if (!this.ReloadTemplate(ModelSheetName))
{
throw new Exception("Template could not be loaded :(");
}
// Generate a Sheet for each relationship. If uwRelationshipId >0 then onl one sheet is needed.
string sSQL1 = "";
if (uwRelationshipId == 0)
{
sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT COUNT(*) AS TabCnt FROM UW.tbl_Relationship WHERE BidPoolId =@p0;";
}
else
{
sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT 1 AS TabCnt ;";
}
var retTabCnt = await MarsDb.QueryAsDataSetAsync(sSQL1, BidPoolId);
System.Data.DataTable aResultSet = retTabCnt.Tables[0];
var iTabCnt = 0;
foreach (System.Data.DataRow a in aResultSet.Rows)
{
iTabCnt = (int)a["TabCnt"];
}
for (int x = 2; x < iTabCnt + 1; x++)
{
sheet = workbook.CloneSheet(this.workbook.GetSheetIndex(ModelSheetName));
workbook.SetSheetName(workbook.NumberOfSheets - 1, x.ToString().AsSheetName());
}
// Return to sheet "1"
this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(ModelSheetName));
// Get Dataset for report using ADO; If uwRelationshipId <> 0 use uwRelationshipId else use BidPoolId
string sSQL2 = "";
DataSet retDataSet = null;
if (uwRelationshipId == 0)
{
sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_Loans] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwLoanId ASC;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId);
}
else
{
sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_Loans] WHERE [uwRelationshipId]=@p0 ORDER BY uwRelationshipId ASC, uwLoanId ASC;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, uwRelationshipId);
}
System.Data.DataTable firstResultSet = retDataSet.Tables[0];
var iRow = 1;
var iRel = 0;
var iLnCnt = 1;
foreach (System.Data.DataRow row in firstResultSet.Rows)
{
if (iRow == 1)
{
iRel = (int)row["uwRelationshipId"];
}
else if (iRel != (int)row["uwRelationshipId"])
{
iSheet++;
this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(iSheet.ToString()));
iRow = 1;
iLnCnt = 1;
iRel = (int)row["uwRelationshipId"];
}
var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)";
var LnCellStyle = new XSSFNPoiStyle()
{
Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left
};
sheet.SetCellValue(2, "B", row, "RptHeader");
sheet.CreateRow(iRow + 5);
LnCellStyle.WrapText = true;
sheet.SetCellValue(iRow + 5, "B", row, "LoanShortName").SetCellStyle(LnCellStyle);
sheet.SetCellValue(iRow + 5, "C", row, "LoanDescriptionTxt").SetCellStyle(LnCellStyle);
sheet.SetCellValue(iRow + 5, "D", row, "BorrowerTxt").SetCellStyle(LnCellStyle);
sheet.SetCellValue(iRow + 5, "E", row, "GuarantorTxt").SetCellStyle(LnCellStyle);
LnCellStyle.CellFormat = "mm/dd/yyy";
sheet.SetCellValue(iRow + 5, "F", row, "OriginationDate").SetCellStyle(LnCellStyle);
if ((row["MaturityDateText"] == System.DBNull.Value) || ((string)row["MaturityDateText"] == ""))
{
LnCellStyle.CellFormat = "mm/dd/yyy";
sheet.SetCellValue(iRow + 5, "G", row, "MaturityDate").SetCellStyle(LnCellStyle);
}
else
{
LnCellStyle.CellFormat = "@";
sheet.SetCellValue(iRow + 5, "G", row, "MaturityDatetext").SetCellStyle(LnCellStyle);
}
LnCellStyle.CellFormat = "#,##0.00";
sheet.SetCellValue(iRow + 5, "H", row, "OriginalUPB").SetCellStyle(LnCellStyle);
sheet.SetCellValue(iRow + 5, "I", row, "UPB").SetCellStyle(LnCellStyle);
if ((row["InterestRateText"] == System.DBNull.Value) || ((string)row["InterestRateText"] == ""))
{
LnCellStyle.CellFormat = "0.0%";
sheet.SetCellValue(iRow + 5, "J", row, "InterestRate").SetCellStyle(LnCellStyle);
}
else
{
LnCellStyle.CellFormat = "@";
sheet.SetCellValue(iRow + 5, "J", row, "InterestRateText").SetCellStyle(LnCellStyle);
}
LnCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 5, "K", row, "SIMValueLoan").SetCellStyle(LnCellStyle);
if (iLnCnt == (int)row["LoansCnt"])
{
sheet.CreateRow(iRow + 7);
LnCellStyle.IsBold = true;
sheet.SetCellValue(iRow + 6, "C", "Totals:").SetCellStyle(LnCellStyle);
LnCellStyle.CellFormat = "#,##0.00";
sheet.SetCellValue(iRow + 6, "H", 0.0).SetCellStyle(LnCellStyle).SetCellFormula(string.Format("SUM(H7:H{0})", (6 + iRow)));
sheet.SetCellValue(iRow + 6, "I", 0.0).SetCellStyle(LnCellStyle).SetCellFormula(string.Format("SUM(I7:I{0})", (6 + iRow)));
LnCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 6, "K", 0.0).SetCellStyle(LnCellStyle).SetCellFormula(string.Format("SUM(K7:K{0})", (6 + iRow)));
LnCellStyle.IsBold = false;
}
iRow++;
iLnCnt++;
}
SaveToFile(this.GeneratedFileName);
return(this.GeneratedFileName);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// This will generate a Business Asset Report for a BidPool or Relationship
/// </summary>
/// <param name="BidPoolId">If this is 0, then we will assume that we are going to use uwRelationshipId</param>
/// <param name="uwRelationshipId">If this is zero, then we will assume that we are going top use BidPoolId</param>
/// <returns>Name of the file generated</returns>
public async Task <string> GenerateAsync(int BidPoolId, int uwRelationshipId)
{
try
{
var ModelSheetName = "1";
if (!this.ReloadTemplate(ModelSheetName))
{
throw new Exception("Template could not be loaded :(");
}
// Generate a Sheet for each relationship that has real estate collateral.
string sSQL1 = "";
if (uwRelationshipId == 0)
{
sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT COUNT(*) AS TabCnt FROM (SELECT DISTINCT r.uwRelationshipId FROM UW.tbl_Relationship AS r INNER JOIN UW.tbl_CollateralRE AS c ON r.uwRelationshipId = c.uwRelationshipId WHERE r.BidPoolId =@p0) AS a;";
}
else
{
sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT 1 AS TabCnt ;";
}
var retTabCnt = await MarsDb.QueryAsDataSetAsync(sSQL1, BidPoolId);
System.Data.DataTable aResultSet = retTabCnt.Tables[0];
var iTabCnt = 0;
foreach (System.Data.DataRow a in aResultSet.Rows)
{
iTabCnt = (int)a["TabCnt"];
}
// Get Dataset for report using ADO; If uwRelationshipId <> 0 use uwRelationshipId else use BidPoolId
string sSQL2 = "";
DataSet retDataSet = null;
var id = 0;
if (uwRelationshipId == 0)
{
sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralRE] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwRECollateralId ASC;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId);
id = BidPoolId;
}
else
{
sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralRe] WHERE [uwRelationshipId]=@p0 ORDER BY uwRelationshipId ASC, uwRECollateralId ASC;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, uwRelationshipId);
id = uwRelationshipId;
}
for (int x = 2; x < iTabCnt + 1; x++)
{
sheet = workbook.CloneSheet(this.workbook.GetSheetIndex(ModelSheetName));
workbook.SetSheetName(workbook.NumberOfSheets - 1, x.ToString().AsSheetName());
}
// Return to sheet "1"
this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(ModelSheetName));
//string sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralRE] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwRECollateralId ASC;";
//var retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId);
System.Data.DataTable firstResultSet = retDataSet.Tables[0];
var iRow = 1;
var iRel = 0;
var iColCnt = 1;
foreach (System.Data.DataRow row in firstResultSet.Rows)
{
if (iRow == 1)
{
iRel = (int)row["uwRelationshipId"];
}
else if (iRel != (int)row["uwRelationshipId"])
{
iSheet++;
this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(iSheet.ToString()));
iRow = 1;
iColCnt = 1;
iRel = (int)row["uwRelationshipId"];
}
var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)";
var RECellStyle = new XSSFNPoiStyle()
{
Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left
};
RECellStyle.CellFormat = "@";
sheet.SetCellValue(2, "B", row, "RptHeader");
sheet.CreateRow(iRow + 6);
RECellStyle.WrapText = true;
sheet.SetCellValue(iRow + 6, "B", row, "CollateralDescriptionTxt").SetCellStyle(RECellStyle);
RECellStyle.CellFormat = "#,##0.00";
sheet.SetCellValue(iRow + 6, "C", row, "Size").SetCellStyle(RECellStyle);
RECellStyle.CellFormat = "@";
sheet.SetCellValue(iRow + 6, "D", row, "SizeMetricDesc").SetCellStyle(RECellStyle);
sheet.SetCellValue(iRow + 6, "E", row, "CollateralFullAddress").SetCellStyle(RECellStyle);
sheet.SetCellValue(iRow + 6, "F", row, "Comments").SetCellStyle(RECellStyle);
RECellStyle.CellFormat = "mm/dd/yyy";
RECellStyle.WrapText = false;
sheet.SetCellValue(iRow + 6, "G", row, "MRAppraisalDate").SetCellStyle(RECellStyle);
RECellStyle.CellFormat = "#,##0.00";
sheet.SetCellValue(iRow + 6, "H", row, "MRAppraisalValue").SetCellStyle(RECellStyle);
sheet.SetCellValue(iRow + 6, "I", row, "MRAppraisalValuetoMetric").SetCellStyle(RECellStyle);
RECellStyle.CellFormat = "#,##0.00";
sheet.SetCellValue(iRow + 6, "J", row, "BPOValueCRE").SetCellStyle(RECellStyle);
sheet.SetCellValue(iRow + 6, "K", row, "BPOValueCREtoMetric").SetCellStyle(RECellStyle);
sheet.SetCellValue(iRow + 6, "L", row, "SIMValue").SetCellStyle(RECellStyle);
sheet.SetCellValue(iRow + 6, "M", row, "SIMValuetoMetric").SetCellStyle(RECellStyle);
if (iColCnt == (int)row["CollateralRECnt"])
{
//sheet.CreateRow(18 + iRow);
//sheet.SetCellValue(18 + iRow, "C", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(C18:C{0})", (18 + iRow - 2)));
sheet.CreateRow(iRow + 7);
RECellStyle.IsBold = true;
sheet.SetCellValue(iRow + 7, "C", "Totals:").SetCellStyle(RECellStyle);
sheet.SetCellValue(iRow + 7, "H", 0.0).SetCellStyle(RECellStyle).SetCellFormula(string.Format("SUM(H8:H{0})", (7 + iRow)));
sheet.SetCellValue(iRow + 7, "J", 0.0).SetCellStyle(RECellStyle).SetCellFormula(string.Format("SUM(J8:J{0})", (7 + iRow)));
sheet.SetCellValue(iRow + 7, "L", 0.0).SetCellStyle(RECellStyle).SetCellFormula(string.Format("SUM(L8:L{0})", (7 + iRow)));
RECellStyle.IsBold = false;
}
iRow++;
iColCnt++;
}
SaveToFile(this.GeneratedFileName);
return(this.GeneratedFileName);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Special function that gives much more flexibility in applying style with different formats
/// </summary>
/// <param name="cell">Cell to apply this on</param>
/// <param name="npoiStyle">NPoi Object previously created</param>
/// <returns></returns>
public static ICell SetCellStyle(this ICell cell, XSSFNPoiStyle npoiStyle)
{
cell.CellStyle = npoiStyle.Render(cell);
return(cell);
}
public static CellRangeAddress SetRangeStyle(this CellRangeAddress range, ISheet sheet, XSSFNPoiStyle npoiStyle)
{
npoiStyle.ApplyBorderToRange(sheet, range);
return(range);
}
/// <summary>
///
/// </summary>
/// <param name="BidPoolId"></param>
/// <returns>Name of the file generated</returns>
public async Task <string> GenerateAsync(int BidPoolId)
{
try
{
if (!this.ReloadTemplate("DS"))
{
throw new Exception("Template could not be loaded :(");
}
string sSQL = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_DeanSheet] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC;SELECT * FROM [UW].[vw_DeanSheet_Totals] WHERE [BidPoolId]=@p0";
var retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL, BidPoolId);
System.Data.DataTable resultSet = retDataSet.Tables[0];
var iRow = 1;
var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)";
var DSCellStyle = new XSSFNPoiStyle()
{
Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr
};
foreach (System.Data.DataRow row in resultSet.Rows)
{
if (iRow == 1) // Populate Bid Pool Header
{
sheet.SetCellValue(1, "B", row, "BidPool");
}
sheet.CreateRow(iRow + 3);
DSCellStyle.CellFormat = "@";
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Left;
sheet.SetCellValue(iRow + 3, "B", row, "RelationshipName").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "C", row, "BidSubPoolName").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "D", row, "UW").SetCellStyle(DSCellStyle);
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 3, "E", row, "LoanCount").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 3, "F", row, "UPBSum").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "G", row, "BidAmount").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
sheet.SetCellValue(iRow + 3, "H", row, "BidUPB").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "I", row, "DiscountRate").SetCellStyle(DSCellStyle);
if ((double)row["TrailConC"] == -1)
{
DSCellStyle.CellFormat = "@";
//DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
sheet.SetCellValue(iRow + 3, "J", "N/A").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
}
else
{
sheet.SetCellValue(iRow + 3, "J", row, "TrailConC").SetCellStyle(DSCellStyle);
}
if ((double)row["ProjConC"] == -1)
{
DSCellStyle.CellFormat = "@";
//DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
sheet.SetCellValue(iRow + 3, "K", "N/A").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
}
else
{
sheet.SetCellValue(iRow + 3, "K", row, "ProjConC").SetCellStyle(DSCellStyle);
}
sheet.SetCellValue(iRow + 3, "L", row, "Recovery").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###.00";
sheet.SetCellValue(iRow + 3, "M", row, "MOIC").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "mm/dd/yyyy";
sheet.SetCellValue(iRow + 3, "N", row, "AppraisalDate").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 3, "O", row, "AppraisalValue").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "P", row, "BusinessAssets").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "Q", row, "BankTotal").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "R", row, "BPOValue").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "S", row, "SIMValue").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
if ((double)row["BidAppr"] == -1)
{
DSCellStyle.CellFormat = "@";
//DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
sheet.SetCellValue(iRow + 3, "T", "N/A").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
}
else
{
sheet.SetCellValue(iRow + 3, "T", row, "BidAppr").SetCellStyle(DSCellStyle);
}
if ((double)row["BidBPO"] == -1)
{
DSCellStyle.CellFormat = "@";
//DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
sheet.SetCellValue(iRow + 3, "U", "N/A").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
}
else
{
sheet.SetCellValue(iRow + 3, "U", row, "BidBPO").SetCellStyle(DSCellStyle);
}
if ((double)row["BidSIMValue"] == -1)
{
DSCellStyle.CellFormat = "@";
//DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
sheet.SetCellValue(iRow + 3, "V", "N/A").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
}
else
{
sheet.SetCellValue(iRow + 3, "V", row, "BidSIMValue").SetCellStyle(DSCellStyle);
}
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 3, "W", row, "PHLast3mth").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "X", row, "PHLast6mth").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "Y", row, "PHLast9mth").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 3, "Z", row, "PHLast12mth").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "@";
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Center;
sheet.SetCellValue(iRow + 3, "AA", row, "Recourse").SetCellStyle(DSCellStyle);
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Left;
sheet.SetCellValue(iRow + 3, "AB", row, "PrimaryCollateralType").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#";
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
sheet.SetCellValue(iRow + 3, "AC", row, "YearBuilt").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "@";
sheet.SetCellValue(iRow + 3, "AD", row, "REUnit").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 3, "AE", row, "REBasis").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "@";
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Left;
sheet.SetCellValue(iRow + 3, "AF", row, "PrimaryLocation").SetCellStyle(DSCellStyle);
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Center;
sheet.SetCellValue(iRow + 3, "AG", row, "Eyes").SetCellStyle(DSCellStyle);
iRow++;
}
resultSet = retDataSet.Tables[1];
foreach (System.Data.DataRow row in resultSet.Rows)
{
sheet.CreateRow(iRow + 4);
DSCellStyle.HorizontalAlignment = HorizontalAlignment.Right;
DSCellStyle.IsBold = true;
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 4, "C", "Totals:").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "D", "").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 4, "E", row, "LoanCount").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 4, "F", row, "UPBSum").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "G", row, "BidAmount").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
sheet.SetCellValue(iRow + 4, "H", row, "BidUPB").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "I", row, "DiscountRate").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "J", row, "TrailConC").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "K", row, "ProjConC").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "L", row, "Recovery").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###.00";
sheet.SetCellValue(iRow + 4, "M", row, "MOIC").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "N", "").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 4, "O", row, "AppraisalValue").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "P", row, "BusinessAssets").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "Q", row, "BankTotal").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "R", row, "BPOValue").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "S", row, "SIMValue").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "0.0%";
sheet.SetCellValue(iRow + 4, "T", row, "BidAppr").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "U", row, "BidBPO").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "V", row, "BidSIMValue").SetCellStyle(DSCellStyle);
DSCellStyle.CellFormat = "#,###";
sheet.SetCellValue(iRow + 4, "W", row, "PHLast3mth").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "X", row, "PHLast6mth").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "Y", row, "PHLast9mth").SetCellStyle(DSCellStyle);
sheet.SetCellValue(iRow + 4, "Z", row, "PHLast12mth").SetCellStyle(DSCellStyle);
DSCellStyle.IsBold = false;
}
SaveToFile(this.GeneratedFileName);
return(this.GeneratedFileName);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// This will generate a Business Asset Report for a BidPool and Relationship
/// </summary>
/// <param name="BidPoolId">If this is 0, then we will assume that we are going to use uwRelationshipId</param>
/// <param name="uwRelationshipId">If this is zero, then we will assume that we are going top use BidPoolId</param>
/// <returns>Name of the file generated</returns>
private async Task <string> GenerateAsync(int BidPoolId, int uwRelationshipId)
{
try
{
var ModelSheetName = "1";
if (!this.ReloadTemplate(ModelSheetName))
{
throw new Exception("Template could not be loaded :(");
}
// Generate a Sheet for each relationship. If uwRelationshipId <> 0 then only 1 sheet is needed.
string sSQL1 = "";
if (uwRelationshipId == 0)
{
sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT COUNT(*) AS TabCnt FROM (SELECT DISTINCT r.uwRelationshipId FROM UW.tbl_Relationship AS r INNER JOIN UW.tbl_CollateralNRE AS c ON r.uwRelationshipId = c.uwRelationshipId WHERE r.BidPoolId =@p0) AS a;";
}
else
{
sSQL1 = @"SET ANSI_WARNINGS OFF; SELECT 1 AS TabCnt ;";
}
var retTabCnt = await MarsDb.QueryAsDataSetAsync(sSQL1, BidPoolId);
System.Data.DataTable aResultSet = retTabCnt.Tables[0];
var iTabCnt = 0;
foreach (System.Data.DataRow a in aResultSet.Rows)
{
iTabCnt = (int)a["TabCnt"];
}
for (int x = 2; x < iTabCnt + 1; x++)
{
sheet = workbook.CloneSheet(this.workbook.GetSheetIndex(ModelSheetName));
workbook.SetSheetName(workbook.NumberOfSheets - 1, x.ToString().AsSheetName());
}
// Return to sheet "1"
this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(ModelSheetName));
// Get Dataset for report using ADO; If uwRelationshipId <> 0 use uwRelationshipId else use BidPoolId
string sSQL2 = "";
DataSet?retDataSet = null;
if (uwRelationshipId == 0)
{
sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralNRE] WHERE [BidPoolId]=@p0 ORDER BY uwRelationshipId ASC, uwNRECollateralId ASC;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, BidPoolId);
}
else
{
sSQL2 = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralNRE] WHERE [uwRelationshipId]=@p0 ORDER BY uwRelationshipId ASC, uwNRECollateralId ASC;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL2, uwRelationshipId);
}
System.Data.DataTable firstResultSet = retDataSet.Tables[0];
var iRow = 1;
var iRel = 0;
var iNRECnt = 1;
foreach (System.Data.DataRow row in firstResultSet.Rows)
{
if (iRow == 1)
{
iRel = (int)row["uwRelationshipId"];
}
else if (iRel != (int)row["uwRelationshipId"])
{
iSheet++;
this.sheet = this.workbook.GetSheetAt(this.workbook.GetSheetIndex(iSheet.ToString()));
iRow = 1;
iNRECnt = 1;
iRel = (int)row["uwRelationshipId"];
}
var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)";
var BACellStyle = new XSSFNPoiStyle()
{
Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr, VerticalAlignment = VerticalAlignment.Top, HorizontalAlignment = HorizontalAlignment.Left
};
sheet.SetCellValue(2, "B", row, "RptHeader");
sheet.CreateRow(iRow + 5);
BACellStyle.WrapText = true;
sheet.SetCellValue(iRow + 5, "B", row, "NREItemLabel").SetCellStyle(BACellStyle);
sheet.SetCellValue(iRow + 5, "C", row, "NREItemComments").SetCellStyle(BACellStyle);
BACellStyle.CellFormat = "#,###.00";
sheet.SetCellValue(iRow + 5, "D", row, "NREItemBookVal").SetCellStyle(BACellStyle);
BACellStyle.CellFormat = "0.0%";
sheet.SetCellValue(iRow + 5, "E", row, "NREItemCollPcnt").SetCellStyle(BACellStyle);
BACellStyle.CellFormat = "#,###.00";
sheet.SetCellValue(iRow + 5, "F", row, "NRESIM").SetCellStyle(BACellStyle);
if (iNRECnt == (int)row["CollateralNRECnt"])
{
//sheet.CreateRow(18 + iRow);
//sheet.SetCellValue(18 + iRow, "C", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(C18:C{0})", (18 + iRow - 2)));
sheet.CreateRow(iRow + 7);
BACellStyle.IsBold = true;
sheet.SetCellValue(iRow + 6, "C", "Totals:").SetCellStyle(BACellStyle);
BACellStyle.CellFormat = "#,###.00";
sheet.SetCellValue(iRow + 6, "D", 0.0).SetCellStyle(BACellStyle).SetCellFormula(string.Format("SUM(D6:D{0})", (6 + iRow)));
sheet.SetCellValue(iRow + 6, "F", 0.0).SetCellStyle(BACellStyle).SetCellFormula(string.Format("SUM(F6:F{0})", (6 + iRow)));
BACellStyle.IsBold = false;
}
iRow++;
iNRECnt++;
}
SaveToFile(this.GeneratedFileName);
return(this.GeneratedFileName);
}
catch (Exception)
{
throw;
}
}
private bool GenerateSheetForRelationship(ISheet sheet, DataRow uwRelItem, DataTable _relCFdata)
{
try
{
sheet.SetCellValue(1, "B", uwRelItem, "uwRelationshipId");
sheet.SetCellValue(1, "E", uwRelItem, "Underwriter");
sheet.SetCellValue(1, "G", uwRelItem, "PrimaryCollateralType");
sheet.SetCellValue(2, "B", uwRelItem, "RelationshipName");
sheet.SetCellValue(2, "E", uwRelItem, "SiteVisitFlag");
sheet.SetCellValue(2, "G", uwRelItem, "RecourseFlag");
sheet.SetCellValue(2, "I", uwRelItem, "TotalSIM");
sheet.SetCellValue(2, "K", uwRelItem, "TotalApprBook");
sheet.SetCellValue(2, "M", uwRelItem, "UPBSum");
sheet.SetCellValue(2, "N", uwRelItem, "BidAmount");
sheet.SetCellValue(2, "O", uwRelItem, "BidUPB");
sheet.SetCellValue(2, "P", uwRelItem, "BidSIM");
sheet.SetCellValue(4, "B", uwRelItem, "BidPoolName");
sheet.SetCellValue(4, "G", uwRelItem, "YearBuilt");
sheet.SetCellValue(5, "B", uwRelItem, "BidSubPoolName");
sheet.SetCellValue(5, "G", uwRelItem, "BPOValueCRE");
sheet.SetCellValue(5, "I", uwRelItem, "SIMValue");
sheet.SetCellValue(5, "J", uwRelItem, "AppraisalDate");
sheet.SetCellValue(5, "K", uwRelItem, "AppraisalValue");
sheet.SetCellValue(5, "M", uwRelItem, "DiscountRate");
sheet.SetCellValue(5, "N", uwRelItem, "Recovery");
sheet.SetCellValue(5, "O", uwRelItem, "MOIC");
sheet.SetCellValue(5, "P", uwRelItem, "WAL");
sheet.SetCellValue(6, "B", uwRelItem, "PrimaryCity");
sheet.SetCellValue(6, "D", uwRelItem, "PrimaryCounty");
sheet.SetCellValue(6, "F", uwRelItem, "PrimaryState");
sheet.SetCellValue(7, "B", uwRelItem, "CollateralDescText");
sheet.SetCellValue(8, "I", 0.0);
sheet.SetCellValue(8, "M", uwRelItem, "CashOnCash");
sheet.SetCellValue(8, "N", uwRelItem, "GrossCashFlow");
sheet.SetCellValue(8, "O", uwRelItem, "NetCashFlow");
sheet.SetCellValue(8, "P", uwRelItem, "LegalGross");
sheet.SetCellValue(9, "B", uwRelItem, "PrimaryLienPoisition");
sheet.SetCellValue(9, "E", uwRelItem, "ExitTypeDesc");
sheet.SetCellValue(10, "B", uwRelItem, "CurrentStatus");
sheet.SetCellValue(10, "E", uwRelItem, "ExitStrategyText");
sheet.SetCellValue(11, "B", uwRelItem, "ProFormaStatus");
sheet.SetCellValue(11, "M", uwRelItem, "PHLast3mth");
sheet.SetCellValue(11, "N", uwRelItem, "PHLast6mth");
sheet.SetCellValue(11, "O", uwRelItem, "PHLast9mth");
sheet.SetCellValue(11, "P", uwRelItem, "PHLast12mth");
sheet.SetCellValue(12, "B", uwRelItem, "PerformingRate");
sheet.SetCellValue(12, "I", uwRelItem, "Size");
sheet.SetCellValue(12, "J", uwRelItem, "SizeMetricDesc");
sheet.SetCellValue(12, "K", uwRelItem, "BidMetric");
sheet.SetCellValue(12, "L", uwRelItem, "SIMMetric");
sheet.SetCellValue(16, "E", uwRelItem, "MiscIncome3Label");
sheet.SetCellValue(16, "F", uwRelItem, "MiscIncome4Label");
sheet.SetCellValue(16, "G", uwRelItem, "MiscIncome5Label");
sheet.SetCellValue(16, "H", uwRelItem, "MiscIncome6Label");
var formatStr = @"_(* #,##0_);_(* (#,##0);_(* "" - ""??_);_(@_)";
var cashFlowCellStyle = new XSSFNPoiStyle()
{
Border = CellBorder.All, BorderStyle = BorderStyle.Thin, CellFormat = formatStr
};
var iRow = 0;
var relCFdata = _relCFdata.Select(string.Format("uwRelationshipId={0}", int.Parse(uwRelItem["uwRelationshipId"].ToString())));
foreach (var cashFlowItem in relCFdata)
{
sheet.CreateRow(17 + iRow);
if ((int)cashFlowItem["MatyFlag"] == 1)
{
cashFlowCellStyle.IsBold = true;
cashFlowCellStyle.FillPattern = FillPattern.SolidForeground;
cashFlowCellStyle.FillForegroundColor = IndexedColors.LightYellow.AsXSSFColor();
}
else
{
cashFlowCellStyle.IsBold = false;
cashFlowCellStyle.FillPattern = FillPattern.SolidForeground;
cashFlowCellStyle.FillForegroundColor = IndexedColors.White.AsXSSFColor();
}
sheet.SetCellValue(17 + iRow, "A", cashFlowItem, "CashFlowDate").SetCellFormat("mmm-yy");
sheet.SetCellValue(17 + iRow, "B", (double)(iRow + 1)).SetCellFormat("0");;
sheet.SetCellValue(17 + iRow, "C", cashFlowItem, "Principal").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "D", cashFlowItem, "Interest").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "E", cashFlowItem, "MiscIncome3").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "F", cashFlowItem, "MiscIncome4").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "G", cashFlowItem, "MiscIncome5").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "H", cashFlowItem, "MiscIncome6").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "I", (double)(iRow + 1)).SetCellFormat("0");;
sheet.SetCellValue(17 + iRow, "J", cashFlowItem, "BackTaxes").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "K", cashFlowItem, "Legal").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "L", cashFlowItem, "Travel").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "M", cashFlowItem, "BrokerFee").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "N", cashFlowItem, "REOTax").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "O", cashFlowItem, "REOins").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "P", cashFlowItem, "CapEx").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "Q", cashFlowItem, "TiLc").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "R", cashFlowItem, "Environ").SetCellStyle(cashFlowCellStyle);
sheet.SetCellValue(17 + iRow, "S", cashFlowItem, "Misc").SetCellStyle(cashFlowCellStyle);
iRow++;
}
iRow++;
sheet.CreateRow(17 + iRow);
sheet.SetCellValue(17 + iRow, "C", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(C18:C{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "D", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(D18:D{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "E", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(E18:E{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "F", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(F18:F{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "G", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(G18:G{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "H", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(H18:H{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "J", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(I18:I{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "K", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(J18:J{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "L", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(K18:K{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "M", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(L18:L{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "N", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(M18:M{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "O", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(N18:N{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "P", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(O18:O{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "Q", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(P18:P{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "R", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(Q18:Q{0})", (18 + iRow - 2)));
sheet.SetCellValue(17 + iRow, "S", 0.0).SetCellFormat(formatStr).SetCellFormula(string.Format("SUM(R18:R{0})", (18 + iRow - 2)));
iRow = iRow + 17 + 3; //line up past the model last row and the last row of all the generated rows
var titleStyle = new XSSFNPoiStyle()
{
IsBold = true, FontHeightInPoints = 30
};
var notesStyle = new XSSFNPoiStyle()
{
Border = CellBorder.All, BorderStyle = BorderStyle.Thin, HorizontalAlignment = HorizontalAlignment.Left, VerticalAlignment = VerticalAlignment.Top, WrapText = true
};
// Remove Notes from Report (12/18/2019)
// The are going to add a Special Notes doc to eacj relationship.
//sheet.SetCellValue(iRow, "A", "Asset Notes").SetCellStyle(titleStyle);
//iRow++;
//this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle);
//sheet.SetCellValue(iRow, "A", uwRelItem, "AssetNotes").SetCellStyle(notesStyle);
//iRow = iRow + 22;
//sheet.SetCellValue(iRow, "A", "Collateral Valuation Notes").SetCellStyle(titleStyle);
//iRow++;
//this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle);
//sheet.SetCellValue(iRow, "A", uwRelItem, "CollateralValuationNotes").SetCellStyle(notesStyle);
//iRow = iRow + 22;
//sheet.SetCellValue(iRow, "A", "Title UCC Notes").SetCellStyle(titleStyle);
//iRow++;
//this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle);
//sheet.SetCellValue(iRow, "A", uwRelItem, "TitleUCCNotes").SetCellStyle(notesStyle);
//iRow = iRow + 22;
//sheet.SetCellValue(iRow, "A", "Environmental Notes").SetCellStyle(titleStyle);
//iRow++;
//this.sheet.MergeCellsRange(iRow, iRow + 21, "A", "Z").SetRangeStyle(this.sheet, notesStyle);
//sheet.SetCellValue(iRow, "A", uwRelItem, "EnvironmentalNotes").SetCellStyle(notesStyle);
//iRow = iRow + 22;
}
catch (Exception)
{
throw;
}
return(true);
}