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