public List <LgdCorAssumption> ReadFromExcel()
{
var dataList = new List <LgdCorAssumption>();
var filePath = $"{Path.Combine(_dataAccess.GetFilePath(), "AssumptionTemplate.xlsx")}";
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[9]; //Assumption_LGD_COR Sheet
int rows = worksheet.Dimension.Rows;
for (int i = 2; i <= rows; i++)
{
var AffiliateId = worksheet.Cells[i, 1].Value;
var costOfRecovery = worksheet.Cells[i, 2].Value;
var collateralValue = worksheet.Cells[i, 3].Value;
var debenture = worksheet.Cells[i, 4].Value;
var cash = worksheet.Cells[i, 5].Value;
var inventory = worksheet.Cells[i, 6].Value;
var plantEquipment = worksheet.Cells[i, 7].Value;
var residential = worksheet.Cells[i, 8].Value;
var commercial = worksheet.Cells[i, 9].Value;
var receivables = worksheet.Cells[i, 10].Value;
var shares = worksheet.Cells[i, 11].Value;
var vehicle = worksheet.Cells[i, 12].Value;
if (AffiliateId == null)
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else if (string.IsNullOrWhiteSpace(AffiliateId.ToString()) && string.IsNullOrWhiteSpace(costOfRecovery.ToString()) && (costOfRecovery.ToString().Contains(">") || costOfRecovery.ToString().Contains("<")))
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else
{
var data = new LgdCorAssumption();
try { data.AffiliateId = Convert.ToInt64(AffiliateId); } catch { data.AffiliateId = -1; }
data.LgdGroup = costOfRecovery.ToString().Contains(">") ? 3 : 4;
try { data.CollateralValue = Convert.ToDouble(collateralValue); } catch { data.CollateralValue = 0.0; }
try { data.Debenture = Convert.ToDouble(debenture); } catch { data.Debenture = 0.0; }
try { data.Cash = Convert.ToDouble(cash); } catch { data.Cash = 0.0; }
try { data.Inventory = Convert.ToDouble(inventory); } catch { data.Inventory = 0.0; }
try { data.PlantEquipment = Convert.ToDouble(plantEquipment); } catch { data.PlantEquipment = 0.0; }
try { data.ResidentialProperty = Convert.ToDouble(residential); } catch { data.ResidentialProperty = 0.0; }
try { data.CommercialProperty = Convert.ToDouble(commercial); } catch { data.CommercialProperty = 0.0; }
try { data.Receivables = Convert.ToDouble(receivables); } catch { data.Receivables = 0.0; }
try { data.Shares = Convert.ToDouble(shares); } catch { data.Shares = 0.0; }
try { data.Vehicle = Convert.ToDouble(vehicle); } catch { data.Vehicle = 0.0; }
dataList.Add(data);
}
}
}
return(dataList);
}
public static string UpdateLgdCollateralGrowthAssumption(LgdCorAssumption input)
{
return($"Update [LgdInputAssumptions] set [Value] = {input.Debenture} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Debenture' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Cash} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Cash' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Inventory} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Inventory' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.PlantEquipment} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Plant & Equipment' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.ResidentialProperty} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Residential Property' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.CommercialProperty} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Commercial Property' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Receivables} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Receivables' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Shares} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Shares' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Vehicle} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Vehicle' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"\n");
}
public static string UpdateLgdCorAssumption(LgdCorAssumption input)
{
var collateralValueKey = input.LgdGroup == 3 ? "CostOfRecoveryHighCollateralValue" : "CostOfRecoveryLowCollateralValue";
return($"Update [LgdInputAssumptions] set [Value] = {input.CollateralValue} where [LgdGroup] = {input.LgdGroup} and [Key] = '{collateralValueKey}' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Debenture} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Debenture' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Cash} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Cash' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Inventory} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Inventory' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.PlantEquipment} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Plant & Equipment' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.ResidentialProperty} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Residential Property' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.CommercialProperty} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Commercial Property' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Receivables} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Receivables' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Shares} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Shares' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"Update [LgdInputAssumptions] set [Value] = {input.Vehicle} where [LgdGroup] = {input.LgdGroup} and [InputName] = 'Vehicle' and [OrganizationUnitId] = {input.AffiliateId}; " +
$"\n");
}
public List <LgdCorAssumption> ReadFromExcel()
{
var dataList = new List <LgdCorAssumption>();
var filePath = $"{Path.Combine(_dataAccess.GetFilePath(), "AssumptionTemplate.xlsx")}";
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(new FileInfo(filePath)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[8]; //CollateralGrowth Sheet
int rows = worksheet.Dimension.Rows;
for (int i = 2; i <= rows; i++)
{
var AffiliateId = worksheet.Cells[i, 1].Value;
var scenario = worksheet.Cells[i, 2].Value;
var debenture = worksheet.Cells[i, 3].Value;
var cash = worksheet.Cells[i, 4].Value;
var inventory = worksheet.Cells[i, 5].Value;
var plantEquipment = worksheet.Cells[i, 6].Value;
var residential = worksheet.Cells[i, 7].Value;
var commercial = worksheet.Cells[i, 8].Value;
var receivables = worksheet.Cells[i, 9].Value;
var shares = worksheet.Cells[i, 10].Value;
var vehicle = worksheet.Cells[i, 11].Value;
if (AffiliateId == null)
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else if (string.IsNullOrWhiteSpace(AffiliateId.ToString()) && string.IsNullOrWhiteSpace(scenario.ToString()) &&
(scenario.ToString().ToLower().Contains("best") || scenario.ToString().ToLower().Contains("optimistic") || scenario.ToString().ToLower().Contains("downturn")))
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else
{
var data = new LgdCorAssumption();
try { data.AffiliateId = Convert.ToInt64(AffiliateId); } catch { data.AffiliateId = -1; }
data.LgdGroup = scenario.ToString().ToLower().Contains("best") ? 5 : (scenario.ToString().ToLower().Contains("optimistic") ? 6 : 7);
try { data.Debenture = Convert.ToDouble(debenture); } catch { data.Debenture = 0.0; }
try { data.Cash = Convert.ToDouble(cash); } catch { data.Cash = 0.0; }
try { data.Inventory = Convert.ToDouble(inventory); } catch { data.Inventory = 0.0; }
try { data.PlantEquipment = Convert.ToDouble(plantEquipment); } catch { data.PlantEquipment = 0.0; }
try { data.ResidentialProperty = Convert.ToDouble(residential); } catch { data.ResidentialProperty = 0.0; }
try { data.CommercialProperty = Convert.ToDouble(commercial); } catch { data.CommercialProperty = 0.0; }
try { data.Receivables = Convert.ToDouble(receivables); } catch { data.Receivables = 0.0; }
try { data.Shares = Convert.ToDouble(shares); } catch { data.Shares = 0.0; }
try { data.Vehicle = Convert.ToDouble(vehicle); } catch { data.Vehicle = 0.0; }
dataList.Add(data);
if (data.LgdGroup == 5)
{
var dataOptimitic = new LgdCorAssumption();
try { dataOptimitic.AffiliateId = Convert.ToInt64(AffiliateId); } catch { dataOptimitic.AffiliateId = -1; }
dataOptimitic.LgdGroup = 6;
dataOptimitic.Debenture = data.Debenture;
dataOptimitic.Cash = data.Cash;
dataOptimitic.Inventory = data.Inventory;
dataOptimitic.PlantEquipment = data.PlantEquipment;
dataOptimitic.ResidentialProperty = data.ResidentialProperty;
dataOptimitic.CommercialProperty = data.CommercialProperty;
dataOptimitic.Receivables = data.Receivables;
dataOptimitic.Shares = data.Shares;
dataOptimitic.Vehicle = data.Vehicle;
dataList.Add(dataOptimitic);
var dataDownturn = new LgdCorAssumption();
try { dataDownturn.AffiliateId = Convert.ToInt64(AffiliateId); } catch { dataDownturn.AffiliateId = -1; }
dataDownturn.LgdGroup = 7;
dataDownturn.Debenture = dataOptimitic.Debenture * 0.92 - 0.08;
dataDownturn.Cash = dataOptimitic.Cash * 0.92 - 0.08;
dataDownturn.Inventory = dataOptimitic.Inventory * 0.92 - 0.08;
dataDownturn.PlantEquipment = dataOptimitic.PlantEquipment * 0.92 - 0.08;
dataDownturn.ResidentialProperty = dataOptimitic.ResidentialProperty * 0.92 - 0.08;
dataDownturn.CommercialProperty = dataOptimitic.CommercialProperty * 0.92 - 0.08;
dataDownturn.Receivables = dataOptimitic.Receivables * 0.92 - 0.08;
dataDownturn.Shares = dataOptimitic.Shares * 0.92 - 0.08;
dataDownturn.Vehicle = dataOptimitic.Vehicle * 0.92 - 0.08;
dataList.Add(dataDownturn);
}
}
}
}
return(dataList);
}
