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));
}