public CalibrationResult_LGD_RecoveryRate GetLGDRecoveryRateData(Guid eclId, EclType eclType)
{
string qry = Queries.GetLGDRecoveryRateData(eclId, eclType.ToString());
var dt = DataAccess.i.GetData(qry);
if (dt.Rows.Count == 0)
{
return(new CalibrationResult_LGD_RecoveryRate {
Corporate_RecoveryRate = 1, Commercial_RecoveryRate = 1, Consumer_RecoveryRate = 1, Overall_RecoveryRate = 1
});
}
DataRow dr = dt.Rows[0];
var itm = new CalibrationResult_LGD_RecoveryRate();
try { itm.Corporate_RecoveryRate = double.Parse(dr["Corporate_RecoveryRate"].ToString().Trim()); } catch { itm.Corporate_RecoveryRate = 1; }
try { itm.Commercial_RecoveryRate = double.Parse(dr["Commercial_RecoveryRate"].ToString().Trim()); } catch { itm.Commercial_RecoveryRate = 1; }
try { itm.Consumer_RecoveryRate = double.Parse(dr["Consumer_RecoveryRate"].ToString().Trim()); } catch { itm.Consumer_RecoveryRate = 1; }
try { itm.Overall_RecoveryRate = double.Parse(dr["Overall_RecoveryRate"].ToString().Trim()); } catch { itm.Overall_RecoveryRate = 1; }
return(itm);
}
public bool ProcessCalibration(Guid calibrationId, long affiliateId)
{
var baseAffPath = Path.Combine(Util.AppSettings.CalibrationModelPath, affiliateId.ToString());
if (!Directory.Exists(baseAffPath))
{
Directory.CreateDirectory(baseAffPath);
}
var qry = Queries.CalibrationInput_RecoveryRate(calibrationId);
var _dt = DataAccess.i.GetData(qry);
//DataView dv = _dt.DefaultView;
//dv.Sort = "Account_No,Contract_No,Date_Of_Recovery";
var dt = _dt;// dv.ToTable();
var rowCount = dt.Rows.Count + 1;
if (dt.Rows.Count == 0)
{
return(true);
}
var counter = Util.AppSettings.GetCounter(dt.Rows.Count);
var path = $"{Path.Combine(Util.AppSettings.CalibrationModelPath, counter.ToString(), "LGD_Recovery_Rate.xlsx")}";
var path1 = $"{Path.Combine(baseAffPath, $"{Guid.NewGuid().ToString()}LGD_Recovery_Rate.xlsx")}";
if (File.Exists(path1))
{
try { File.Delete(path1); } catch { };
}
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo(path)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[1];//.FirstOrDefault();
// get number of rows in the sheet
int rows = worksheet.Dimension.Rows; // 10
worksheet.DeleteRow(dt.Rows.Count + 2, rows - (dt.Rows.Count + 2));
// loop through the worksheet rows
package.Workbook.CalcMode = ExcelCalcMode.Automatic;
for (int i = 0; i < dt.Rows.Count; i++)// DataRow dr in dt.Rows)
{
Log4Net.Log.Info(i);
DataRow dr = dt.Rows[i];
var itm = DataAccess.i.ParseDataToObject(new Calibration_LGD_RecoveryRate(), dr);
if (string.IsNullOrEmpty(itm.Account_No) && string.IsNullOrEmpty(itm.Contract_No) && itm.Date_Of_Recovery == null)
{
continue;
}
try { worksheet.Cells[i + 2, 1].Value = itm.Customer_No; } catch { }
try { worksheet.Cells[i + 2, 2].Value = itm.Account_No; } catch { }
try {
itm.Account_Name = itm.Account_Name ?? "";
worksheet.Cells[i + 2, 3].Value = itm.Account_Name.Trim();
} catch { }
try {
itm.Contract_No = itm.Contract_No ?? "";
worksheet.Cells[i + 2, 4].Value = itm.Contract_No.Trim();
} catch { }
try { worksheet.Cells[i + 2, 5].Value = itm.Segment; } catch { }
try { worksheet.Cells[i + 2, 6].Value = itm.Product_Type; } catch { }
try { worksheet.Cells[i + 2, 7].Value = itm.Days_Past_Due; } catch { }
try { worksheet.Cells[i + 2, 8].Value = itm.Classification; } catch { }
try { worksheet.Cells[i + 2, 9].Value = itm.Default_Date; } catch { }
try { worksheet.Cells[i + 2, 10].Value = itm.Outstanding_Balance_Lcy; } catch { }
try { worksheet.Cells[i + 2, 11].Value = itm.Contractual_Interest_Rate; } catch { }
try { worksheet.Cells[i + 2, 12].Value = itm.Amount_Recovered; } catch { }
try
{ worksheet.Cells[i + 2, 13].Value = itm.Date_Of_Recovery; }
catch { }
try
{
itm.Type_Of_Recovery = itm.Type_Of_Recovery ?? "";
worksheet.Cells[i + 2, 14].Value = itm.Type_Of_Recovery.Trim();
}
catch { }
}
var fi = new FileInfo(path1);
package.SaveAs(fi);
}
string txtLocation = Path.GetFullPath(path1);
object _missingValue = System.Reflection.Missing.Value;
Application excel = new Application();
var theWorkbook = excel.Workbooks.Open(txtLocation,
_missingValue,
false,
_missingValue,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
_missingValue);
//Sort
Worksheet calculationSheet = theWorkbook.Sheets[2];
Range sortRange = calculationSheet.Range["A2", "N" + rowCount.ToString()];
sortRange.Sort(sortRange.Columns[10], XlSortOrder.xlDescending, DataOption1: XlSortDataOption.xlSortTextAsNumbers); //Outstanding balance
//sortRange.Sort(sortRange.Columns[9], XlSortOrder.xlAscending, DataOption1: XlSortDataOption.xlSortTextAsNumbers); //Default date
sortRange.Sort(sortRange.Columns[4], XlSortOrder.xlAscending, DataOption1: XlSortDataOption.xlSortTextAsNumbers); // Contract no
//Temp fix for #REF error after deleting rows
Range tempRange = calculationSheet.Range["A" + (rowCount + 1).ToString(), "N" + (rowCount + 1).ToString()];
tempRange.EntireRow.Delete();
//refresh and calculate to modify
theWorkbook.RefreshAll();
excel.Calculate();
Worksheet worksheet1 = theWorkbook.Sheets[1];
var r = new CalibrationResult_LGD_RecoveryRate();
r.Overall_Exposure_At_Default = 0;
try { r.Overall_Exposure_At_Default = worksheet1.Cells[2, 2].Value; } catch { }
r.Overall_Exposure_At_Default = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_Exposure_At_Default) ? 0 : r.Overall_Exposure_At_Default;
r.Overall_PvOfAmountReceived = 0;
try { r.Overall_PvOfAmountReceived = worksheet1.Cells[3, 2].Value; } catch { }
r.Overall_PvOfAmountReceived = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_PvOfAmountReceived) ? 0 : r.Overall_PvOfAmountReceived;
r.Overall_Count = 0;
try { r.Overall_Count = worksheet1.Cells[4, 2].Value; } catch { }
r.Overall_Count = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_Count) ? 0 : r.Overall_Count;
r.Overall_RecoveryRate = 0;
try { r.Overall_RecoveryRate = worksheet1.Cells[5, 2].Value; } catch { }
r.Overall_RecoveryRate = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_RecoveryRate) ? 0 : r.Overall_RecoveryRate;
r.Corporate_Exposure_At_Default = 0;
try { r.Corporate_Exposure_At_Default = worksheet1.Cells[2, 3].Value; } catch { }
r.Corporate_Exposure_At_Default = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Corporate_Exposure_At_Default) ? 0 : r.Corporate_Exposure_At_Default;
r.Corporate_PvOfAmountReceived = 0;
try { r.Corporate_PvOfAmountReceived = worksheet1.Cells[3, 3].Value; } catch { }
r.Corporate_PvOfAmountReceived = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Corporate_PvOfAmountReceived) ? 0 : r.Corporate_PvOfAmountReceived;
r.Corporate_Count = 0;
try { r.Corporate_Count = worksheet1.Cells[4, 3].Value; } catch { }
r.Corporate_Count = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Corporate_Count) ? 0 : r.Corporate_Count;
r.Corporate_RecoveryRate = 0;
try { r.Corporate_RecoveryRate = worksheet1.Cells[5, 3].Value; } catch { }
r.Corporate_RecoveryRate = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Corporate_RecoveryRate) ? 0 : r.Corporate_RecoveryRate;
r.Commercial_Exposure_At_Default = 0;
try { r.Commercial_Exposure_At_Default = worksheet1.Cells[2, 4].Value; } catch { }
r.Commercial_Exposure_At_Default = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Commercial_Exposure_At_Default) ? 0 : r.Commercial_Exposure_At_Default;
r.Commercial_PvOfAmountReceived = 0;
try { r.Commercial_PvOfAmountReceived = worksheet1.Cells[3, 4].Value; } catch { }
r.Commercial_PvOfAmountReceived = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Commercial_PvOfAmountReceived) ? 0 : r.Commercial_PvOfAmountReceived;
r.Commercial_Count = 0;
try { r.Commercial_Count = worksheet1.Cells[4, 4].Value; } catch { }
r.Commercial_Count = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Commercial_Count) ? 0 : r.Commercial_Count;
r.Commercial_RecoveryRate = 0;
try { r.Commercial_RecoveryRate = worksheet1.Cells[5, 4].Value; } catch { }
r.Commercial_RecoveryRate = ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Commercial_RecoveryRate) ? 0 : r.Commercial_RecoveryRate;
r.Consumer_Exposure_At_Default = 0;
try { r.Consumer_Exposure_At_Default = worksheet1.Cells[2, 5].Value; } catch { }
r.Consumer_PvOfAmountReceived = 0;
try { r.Consumer_PvOfAmountReceived = worksheet1.Cells[3, 5].Value; } catch { }
r.Consumer_Count = 0;
try { r.Consumer_Count = worksheet1.Cells[4, 5].Value; } catch { }
r.Consumer_RecoveryRate = 0;
try { r.Consumer_RecoveryRate = worksheet1.Cells[5, 5].Value; } catch { }
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Corporate_RecoveryRate))
{
r.Overall_RecoveryRate = 0;
//r.Corporate_RecoveryRate=new Random().Next(10, 100);
//if(r.Corporate_RecoveryRate== 0)
// r.Corporate_RecoveryRate = r.Corporate_RecoveryRate * 0.01;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Commercial_RecoveryRate))
{
r.Commercial_RecoveryRate = 0;// = new Random().Next(10, 100);
// r.Commercial_RecoveryRate = r.Commercial_RecoveryRate * 0.01;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Consumer_RecoveryRate))
{
r.Consumer_RecoveryRate = 0;// = new Random().Next(10, 100);
//r.Consumer_RecoveryRate = r.Consumer_RecoveryRate * 0.01;
}
if (ECLNonStringConstants.i.ExcelDefaultValue.Contains(r.Overall_RecoveryRate))
{
r.Overall_RecoveryRate = 0;// = new Random().Next(10, 100);
//r.Consumer_RecoveryRate = r.Consumer_RecoveryRate * 0.01;
}
theWorkbook.Save();
theWorkbook.Close(true);
excel.Quit();
//File.Delete(path1);
qry = Queries.CalibrationResult_LGD_RecoveryRate_Update(calibrationId, r.Overall_Exposure_At_Default, r.Overall_PvOfAmountReceived, r.Overall_Count, r.Overall_RecoveryRate, r.Corporate_Exposure_At_Default, r.Corporate_PvOfAmountReceived, r.Corporate_Count, r.Corporate_RecoveryRate, r.Commercial_Exposure_At_Default, r.Commercial_PvOfAmountReceived, r.Commercial_Count, r.Commercial_RecoveryRate, r.Consumer_Exposure_At_Default, r.Consumer_PvOfAmountReceived, r.Consumer_Count, r.Consumer_RecoveryRate);
DataAccess.i.ExecuteQuery(qry);
return(true);
}
