/// <summary>
/// This will generate a Report with the Real Estate Comment.
/// </summary>
/// <param name="uwRECollateralId">uwRECollateralId</param>
/// <returns>Name of the file generated</returns>
public async Task <string> GenerateAsync(int uwRECollateralId)
{
try
{
if (!ReloadTemplate())
{
throw new Exception("Template could not be loaded :(");
}
string sSQL = "";
DataSet retDataSet = null;
sSQL = @"SET ANSI_WARNINGS OFF; SELECT * FROM [UW].[vw_CollateralRE] WHERE [uwRECollateralId] = @p0;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL, uwRECollateralId);
if ((retDataSet.Tables.Count == 1) && (retDataSet.Tables[0].Rows.Count == 1))
{
var data = retDataSet.Tables[0].Rows[0];
Document.ReplaceFieldValue(data, "RptHeader");
Document.ReplaceFieldValue(data, "OneLineAddress");
Document.ReplaceFieldValue(data, "SIMValue", "C0");
Document.ReplaceFieldValue(data, "Comments");
SaveToFile(GeneratedFileName);
return(GeneratedFileName);
}
return("No records found");
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// This works around a strange NPOI copy quirk that copies the formulas incorrect. This gives the option to force this object to write directly to the sheet
/// </summary>
/// <param name="BidPoolId"></param>
/// <param name="uwRelationshipId"></param>
/// <param name="sheet"></param>
/// <returns></returns>
public async Task <DataSet> FetchDataAsync(int BidPoolId, int uwRelationshipId, ISheet sheet)
{
DataSet retDataSet = null;
if ((BidPoolId == 0) && (uwRelationshipId > 0))
{
string sSQL = @"SET ANSI_WARNINGS OFF;
SELECT * FROM [UW].[vw_Relationship] WHERE uwRelationshipId = @p0;
SELECT cf.* FROM [UW].[vw_RelationshipCashFlow] cf
WHERE uwRelationshipId = @p0 ORDER BY CashFlowDate;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL, uwRelationshipId);
}
else if ((BidPoolId > 0) && (uwRelationshipId == 0))
{
string sSQL = @"SET ANSI_WARNINGS OFF;
SELECT * FROM [UW].[vw_Relationship] WHERE BidPoolId = @p0 ORDER BY RelationshipName;
SELECT cf.* FROM [UW].[vw_RelationshipCashFlow] cf
INNER JOIN [UW].[vw_Relationship] r
ON cf.uwRelationshipId = r.uwRelationshipId
WHERE r.BidPoolId = @p0 ORDER BY r.RelationshipName, CashFlowDate;";
retDataSet = await MarsDb.QueryAsDataSetAsync(sSQL, BidPoolId);
}
else
{
throw new Exception(string.Format("Invalid call to GenerateAsync is invalid. We need one or the other to be greater than zero. BidPoolId={0} uwRelationshipId={1}", BidPoolId, uwRelationshipId));
}
if (sheet != null)
{
GenerateSheetForRelationship(sheet, retDataSet.Tables[0].Rows[0], retDataSet.Tables[1]);
}
return(retDataSet);
}
/// <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>
/// Execute a Query and return a Data Reader, make sure you close it
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static async Task <DataSet> QueryAsDataSetAsync(string sql, List <SqlParameter> parmList)
{
DataSet dataset = new DataSet();
using (var conn = await MarsDb.ConnectionAsync())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
foreach (var parm in parmList)
{
cmd.Parameters.Add(parm);
}
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dataset);
}
}
return(dataset);
}
/// <summary>
/// Execute a stored procedure and have it return the result set as an objects
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static async Task <IEnumerable <T> > Procedure <T>(string sql, List <SqlParameter> parmList) where T : new()
{
var retList = new List <T>();
using (var conn = await MarsDb.ConnectionAsync())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
foreach (var parm in parmList)
{
cmd.Parameters.Add(parm);
}
using (var reader = await cmd.ExecuteReaderAsync())
{
retList = reader.GetObjects <T>().ToList();
}
}
}
return(retList);
}
/// <summary>
/// Execute a qeury and have it return the result set as an objects
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql"></param>
/// <returns></returns>
public static string QueryAsJson(string sql, List <SqlParameter> parmList)
{
var retList = "";
using (var conn = MarsDb.Connection())
{
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.CommandType = CommandType.Text;
foreach (var parm in parmList)
{
cmd.Parameters.Add(parm);
}
using (var reader = cmd.ExecuteReader())
{
retList = reader.ToJson();
}
}
}
return(retList);
}
/// <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>
///
/// </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;
}
}