public bool ProcessFrameworkResult(string filename, FrameworkParameters input)
{
try
{
var frameworkResult = FileSystemStorage <ResultDetailDataMore> .ReadCsvData(filename);
var c = new ResultDetailDataMore();
Type myObjOriginalType = c.GetType();
PropertyInfo[] myProps = myObjOriginalType.GetProperties();
var dt = new System.Data.DataTable();
for (int i = 0; i < myProps.Length; i++)
{
dt.Columns.Add(myProps[i].Name, myProps[i].PropertyType);
}
dt.Columns.Add($"{input.EclType}EclId", typeof(Guid));
//var lstContractNoLog = new List<string>();
foreach (var _d in frameworkResult)
{
//if (lstContractNoLog.Any(o => o == _d.ContractNo))
// continue;
//lstContractNoLog.Add(_d.ContractNo);
try { c.OriginalOutstandingBalance = 0.0; } catch { }
//(double)batchContracts.FirstOrDefault(o => o.ContractNo == c.ContractNo).OutstandingBalanceLCY; } catch { }
var Id = Guid.NewGuid();
dt.Rows.Add(new object[]
{
Id, _d.Stage, _d.Outstanding_Balance, _d.ECL_Best_Estimate, _d.ECL_Optimistic, _d.ECL_Downturn, _d.Impairment_ModelOutput,
_d.Overrides_Stage, _d.Overrides_TTR_Years, _d.Overrides_FSV, _d.Overrides_Overlay, _d.Overrides_ECL_Best_Estimate, _d.Overrides_ECL_Optimistic, _d.Overrides_ECL_Downturn, _d.Overrides_Impairment_Manual, _d.ContractNo, _d.AccountNo,
_d.CustomerNo, _d.Segment, _d.ProductType, _d.Sector, _d.OriginalOutstandingBalance, input.EclId
});
}
//Save to Report Detail
var r = DataAccess.i.ExecuteBulkCopy(dt, ECLStringConstants.i.EclFramworkReportDetail(input.EclType));
return(true);
}
catch (Exception ex)
{
Log4Net.Log.Error(ex);
return(false);
}
}
private void ExtractAndSaveResult(List <Loanbook_Data> batchContracts, string filePath, Guid eclId, EclType eclType)
{
var frameworkResult = new List <ResultDetailDataMore>();
var c = new ResultDetailDataMore();
//ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
//using (var package = new ExcelPackage(new FileInfo(filePath)))
//{
// ExcelWorksheet worksheet = package.Workbook.Worksheets[6];//.FirstOrDefault();
// Log4Net.Log.Info("Read Result File");
// // get number of rows in the sheet
// int rows = worksheet.Dimension.Rows;
// package.Workbook.CalcMode = ExcelCalcMode.Automatic;
// for (int i = 10; i <= rows; i++)
// {
// if (worksheet.Cells[i, 3].Value == null)
// continue;
// try
// {
// c.ContractNo = Convert.ToString(worksheet.Cells[i, 3].Value);
// c.AccountNo = worksheet.Cells[i, 4].Value != null ? Convert.ToString(worksheet.Cells[i, 4].Value) : "";
// c.CustomerNo = worksheet.Cells[i, 5].Value != null ? Convert.ToString(worksheet.Cells[i, 5].Value) : "";
// c.Segment = worksheet.Cells[i, 6].Value != null ? Convert.ToString(worksheet.Cells[i, 6].Value) : "";
// c.ProductType = worksheet.Cells[i, 7].Value != null ? Convert.ToString(worksheet.Cells[i, 7].Value) : "";
// c.Sector = worksheet.Cells[i, 8].Value != null ? Convert.ToString(worksheet.Cells[i, 8].Value) : "";
// c.Stage = worksheet.Cells[i, 9].Value != null ? (int)worksheet.Cells[i, 9].Value : 0;
// c.Outstanding_Balance = worksheet.Cells[i, 10].Value != null ? (double)worksheet.Cells[i, 10].Value : 0.0;
// c.ECL_Best_Estimate = worksheet.Cells[i, 11].Value != null ? (double)worksheet.Cells[i, 11].Value : 0.0;
// c.ECL_Optimistic = worksheet.Cells[i, 12].Value != null ? (double)worksheet.Cells[i, 12].Value : 0.0;
// c.ECL_Downturn = worksheet.Cells[i, 12].Value != null ? (double)worksheet.Cells[i, 13].Value : 0.0;
// c.Impairment_ModelOutput = worksheet.Cells[i, 14].Value != null ? (double)worksheet.Cells[i, 14].Value : 0.0;
// c.Overrides_Stage = worksheet.Cells[i, 15].Value != null ? (int)worksheet.Cells[i, 15].Value : 0;
// c.Overrides_TTR_Years = worksheet.Cells[i, 16].Value != null ? (double)worksheet.Cells[i, 16].Value : 0.0;
// c.Overrides_FSV = worksheet.Cells[i, 17].Value != null ? (double)worksheet.Cells[i, 16].Value : 0.0;
// c.Overrides_Overlay = worksheet.Cells[i, 18].Value != null ? (double)worksheet.Cells[i, 18].Value : 0.0;
// c.Overrides_ECL_Best_Estimate = worksheet.Cells[i, 19].Value != null ? (double)worksheet.Cells[i, 18].Value : 0.0;
// c.Overrides_ECL_Optimistic = worksheet.Cells[i, 20].Value != null ? (double)worksheet.Cells[i, 20].Value : 0.0;
// c.Overrides_ECL_Downturn = worksheet.Cells[i, 21].Value != null ? (double)worksheet.Cells[i, 21].Value : 0.0;
// c.Overrides_Impairment_Manual = worksheet.Cells[i, 22].Value != null ? (double)worksheet.Cells[i, 22].Value : 0.0;
// try { c.OriginalOutstandingBalance = (double)batchContracts.FirstOrDefault(o => o.ContractNo == c.ContractNo).OutstandingBalanceLCY; } catch { }
// frameworkResult.Add(c);
// }
// catch(Exception ex)
// {
// }
// }
//}
string txtLocation = Path.GetFullPath(filePath);
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);
try
{
Worksheet worksheet = theWorkbook.Sheets[7];
worksheet.Unprotect(AppSettings.SheetPassword);
var rows = worksheet.Rows;
for (int i = 10; i <= 1020; i++)
{
int bc = 1;
if (worksheet.Cells[i, bc + 2].Value == null)
{
continue;
}
try
{
c = new ResultDetailDataMore();
c.ContractNo = Convert.ToString(worksheet.Cells[i, bc + 2].Value);
c.AccountNo = worksheet.Cells[i, bc + 3].Value != null?Convert.ToString(worksheet.Cells[i, bc + 3].Value) : "";
c.CustomerNo = worksheet.Cells[i, bc + 4].Value != null?Convert.ToString(worksheet.Cells[i, bc + 4].Value) : "";
c.Segment = worksheet.Cells[i, bc + 5].Value != null?Convert.ToString(worksheet.Cells[i, bc + 5].Value) : "";
c.ProductType = worksheet.Cells[i, bc + 6].Value != null?Convert.ToString(worksheet.Cells[i, bc + 6].Value) : "";
c.Sector = worksheet.Cells[i, bc + 7].Value != null?Convert.ToString(worksheet.Cells[i, bc + 7].Value) : "";
c.Stage = worksheet.Cells[i, bc + 8].Value != null?Convert.ToInt32(worksheet.Cells[i, bc + 8].Value) : 0;
c.Outstanding_Balance = worksheet.Cells[i, bc + 9].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 9].Value) : 0.0;
c.ECL_Best_Estimate = worksheet.Cells[i, bc + 10].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 10].Value) : 0.0;
c.ECL_Optimistic = worksheet.Cells[i, bc + 11].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 11].Value) : 0.0;
c.ECL_Downturn = worksheet.Cells[i, bc + 12].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 12].Value) : 0.0;
c.Impairment_ModelOutput = worksheet.Cells[i, bc + 13].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 13].Value) : 0.0;
c.Overrides_Stage = worksheet.Cells[i, bc + 14].Value != null?Convert.ToInt32(worksheet.Cells[i, bc + 14].Value) : 0;
try { c.Overrides_TTR_Years = worksheet.Cells[i, bc + 15].Value != null?Convert.ToInt32(worksheet.Cells[i, bc + 15].Value) : 0.0; } catch { c.Overrides_TTR_Years = 0.0; }
try { c.Overrides_FSV = worksheet.Cells[i, bc + 16].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 16].Value) : 0.0; } catch { c.Overrides_FSV = 0.0; }
try { c.Overrides_Overlay = worksheet.Cells[i, bc + 17].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 17].Value) : 0.0; } catch { c.Overrides_Overlay = 0.0; }
c.Overrides_ECL_Best_Estimate = worksheet.Cells[i, bc + 18].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 18].Value) : 0.0;
c.Overrides_ECL_Optimistic = worksheet.Cells[i, bc + 19].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 19].Value) : 0.0;
c.Overrides_ECL_Downturn = worksheet.Cells[i, bc + 20].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 20].Value) : 0.0;
c.Overrides_Impairment_Manual = worksheet.Cells[i, bc + 21].Value != null?Convert.ToDouble(worksheet.Cells[i, bc + 21].Value) : 0.0;
try { c.OriginalOutstandingBalance = (double)batchContracts.FirstOrDefault(o => o.ContractNo == c.ContractNo).OutstandingBalanceLCY; } catch { }
if (!c.ContractNo.Contains(AppSettings.DumbContract))
{
frameworkResult.Add(c);
}
}
catch (Exception ex)
{
}
}
theWorkbook.Save();
theWorkbook.Close(true);
}
catch (Exception ex)
{
theWorkbook.Close(true);
excel.Quit();
Console.WriteLine(ex);
}
finally
{
excel.Quit();
}
//return true;
Type myObjOriginalType = c.GetType();
PropertyInfo[] myProps = myObjOriginalType.GetProperties();
var dt = new System.Data.DataTable();
for (int i = 0; i < myProps.Length; i++)
{
dt.Columns.Add(myProps[i].Name, myProps[i].PropertyType);
}
dt.Columns.Add($"{eclType}EclId", typeof(Guid));
//var lstContractNoLog = new List<string>();
foreach (var _d in frameworkResult)
{
//if (lstContractNoLog.Any(o => o == _d.ContractNo))
// continue;
//lstContractNoLog.Add(_d.ContractNo);
var Id = Guid.NewGuid();
dt.Rows.Add(new object[]
{
Id, _d.Stage, _d.Outstanding_Balance, _d.ECL_Best_Estimate, _d.ECL_Optimistic, _d.ECL_Downturn, _d.Impairment_ModelOutput,
_d.Overrides_Stage, _d.Overrides_TTR_Years, _d.Overrides_FSV, _d.Overrides_Overlay, _d.Overrides_ECL_Best_Estimate, _d.Overrides_ECL_Optimistic, _d.Overrides_ECL_Downturn, _d.Overrides_Impairment_Manual, _d.ContractNo, _d.AccountNo,
_d.CustomerNo, _d.Segment, _d.ProductType, _d.Sector, _d.OriginalOutstandingBalance, eclId
});
}
//Save to Report Detail
var r = DataAccess.i.ExecuteBulkCopy(dt, ECLStringConstants.i.EclFramworkReportDetail(eclType));
}
public bool ExecuteFrameworkMacro(string fullName)
{
try
{
var basePath = new FileInfo(fullName).DirectoryName;
var inputFileText = File.ReadAllText(Path.Combine(basePath, AppSettings.ModelInputFileEto));
var input = JsonConvert.DeserializeObject <FrameworkParameters>(inputFileText);
string txtLocation = fullName;
var csvFileName = fullName.Replace(AppSettings.processing_, AppSettings.complete_).Replace(AppSettings.xlsb, AppSettings.xcsv);
object _missingValue = System.Reflection.Missing.Value;
Application excel = new Application();
excel.DisplayAlerts = false;
var theWorkbook = excel.Workbooks.Open(txtLocation,
_missingValue,
false,
_missingValue,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
_missingValue);
try
{
theWorkbook.Unprotect(AppSettings.SheetPassword);
Worksheet startSheet = theWorkbook.Sheets[3];
//startSheet.Unprotect(AppSettings.SheetPassword);
var reportPath = Path.Combine(basePath, AppSettings.Report);
if (!File.Exists(reportPath))
{
Directory.CreateDirectory(reportPath);
}
if (!fullName.ToLower().Contains("override"))
{
startSheet.Cells[6, 4] = input.ReportDate.ToString("dd MMMM yyyy");
startSheet.Cells[9, 4] = reportPath;
startSheet.Cells[10, 4] = $"{AppSettings.complete_}{input.PdFileName}";
startSheet.Cells[11, 4] = Path.Combine(basePath, $"{AppSettings.complete_}{input.PdFileName}");
startSheet.Cells[12, 4] = $"{AppSettings.complete_}{input.LgdFile}";
startSheet.Cells[13, 4] = Path.Combine(basePath, $"{AppSettings.complete_}{input.LgdFile}");
startSheet.Cells[14, 4] = $"{AppSettings.complete_}{input.EadFileName}";
startSheet.Cells[15, 4] = Path.Combine(basePath, $"{AppSettings.complete_}{input.EadFileName}");
}
if (txtLocation.Contains(AppSettings.override_))
{
excel.Run("override_formulas");
excel.Calculate();
}
else
{
excel.Run("calculate_ecl");
}
theWorkbook.Close(true);
excel.Quit();
}
catch (Exception ex)
{
Log4Net.Log.Error(ex);
theWorkbook.Close(true);
excel.Quit();
return(false);
}
excel = new Application();
excel.DisplayAlerts = false;
theWorkbook = excel.Workbooks.Open(txtLocation,
_missingValue,
false,
_missingValue,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
true,
_missingValue,
_missingValue,
_missingValue);
try
{
Worksheet worksheet = theWorkbook.Sheets[7];
worksheet.Unprotect(AppSettings.SheetPassword);
worksheet.Columns.NumberFormat = "0.00";
worksheet.SaveAs(csvFileName, XlFileFormat.xlCSV);
File.Copy(csvFileName, csvFileName.Replace(AppSettings.xcsv, AppSettings.csv), true);
//theWorkbook.Save();
// Garbage collecting
GC.Collect();
GC.WaitForPendingFinalizers();
theWorkbook.Close(true, Missing.Value, Missing.Value);
excel.Quit();
Marshal.FinalReleaseComObject(theWorkbook);
Marshal.FinalReleaseComObject(excel);
}
catch (Exception ex)
{
Log4Net.Log.Error(ex);
// Garbage collecting
GC.Collect();
GC.WaitForPendingFinalizers();
theWorkbook.Close(true, Missing.Value, Missing.Value);
excel.Quit();
Marshal.FinalReleaseComObject(theWorkbook);
Marshal.FinalReleaseComObject(excel);
return(false);
}
var csvLines = File.ReadAllLines(csvFileName.Replace(AppSettings.xcsv, AppSettings.csv));
var frameworkResult = new List <ResultDetailDataMore>();
for (int i = 9; i <= AppSettings.BatchSize + 20; i++)
{
try
{
var csvCells = csvLines[i].Split(',');
int bc = 0;
if (string.IsNullOrEmpty(csvCells[bc + 2]))
{
continue;
}
var c = new ResultDetailDataMore();
c.ContractNo = Convert.ToString(csvCells[bc + 2].Replace(".00", string.Empty));
c.AccountNo = csvCells[bc + 3] != null ? csvCells[bc + 3].Replace(".00", string.Empty) : "";
c.CustomerNo = csvCells[bc + 4] != null ? csvCells[bc + 4].Replace(".00", string.Empty) : "";
c.Segment = csvCells[bc + 5] != null ? csvCells[bc + 5].Replace(".00", string.Empty) : "";
c.ProductType = csvCells[bc + 6] != null ? csvCells[bc + 6].Replace(".00", string.Empty) : "";
c.Sector = csvCells[bc + 7] != null ? csvCells[bc + 7].Replace(".00", string.Empty) : "";
c.Stage = csvCells[bc + 8] != null?Convert.ToInt32(csvCells[bc + 8].Replace(".00", string.Empty)) : 0;
c.Outstanding_Balance = csvCells[bc + 9] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 9])) : 0.0;
c.ECL_Best_Estimate = csvCells[bc + 10] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 10])) : 0.0;
c.ECL_Optimistic = csvCells[bc + 11] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 11])) : 0.0;
c.ECL_Downturn = csvCells[bc + 12] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 12])) : 0.0;
c.Impairment_ModelOutput = csvCells[bc + 13] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 13])) : 0.0;
c.Overrides_Stage = csvCells[bc + 14] != null?Convert.ToInt32(StringHelper.RemoveSpecialCharacters(csvCells[bc + 14].Replace(".00", string.Empty))) : 0;
try { c.Overrides_TTR_Years = csvCells[bc + 15] != null?Convert.ToInt32(StringHelper.RemoveSpecialCharacters(csvCells[bc + 15])) : 0.0; } catch { c.Overrides_TTR_Years = 0.0; }
try { c.Overrides_FSV = csvCells[bc + 16] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 16])) : 0.0; } catch { c.Overrides_FSV = 0.0; }
try { c.Overrides_Overlay = csvCells[bc + 17] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 17])) : 0.0; } catch { c.Overrides_Overlay = 0.0; }
c.Overrides_ECL_Best_Estimate = csvCells[bc + 18] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 18])) : 0.0;
c.Overrides_ECL_Optimistic = csvCells[bc + 19] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 19])) : 0.0;
c.Overrides_ECL_Downturn = csvCells[bc + 20] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 20])) : 0.0;
c.Overrides_Impairment_Manual = csvCells[bc + 21] != null?Convert.ToDouble(StringHelper.RemoveSpecialCharacters(csvCells[bc + 21])) : 0.0;
c.OriginalOutstandingBalance = 0.0;
frameworkResult.Add(c);
}
catch (Exception ex)
{
Log4Net.Log.Error(ex);
}
}
return(FileSystemStorage <ResultDetailDataMore> .WriteCsvData(fullName.Replace(AppSettings.processing_, AppSettings.complete_).Replace(AppSettings.xlsb, AppSettings.csv), frameworkResult));
}
catch (Exception ex)
{
Log4Net.Log.Error(ex);
Log4Net.Log.Info(DateTime.Now);
return(false);
}
}
