public List <GeneralAssumptionEntity> ReadFromExcel()
{
var dataList = new List <GeneralAssumptionEntity>();
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[11]; //Assumption_EAD Sheet
int rows = worksheet.Dimension.Rows;
for (int i = 2; i <= rows; i++)
{
var AffiliateId = worksheet.Cells[i, 1].Value;
var conversionFactorObe = worksheet.Cells[i, 2].Value;
var prepaymentFactor = worksheet.Cells[i, 3].Value;
if (AffiliateId == null)
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else if (string.IsNullOrWhiteSpace(AffiliateId.ToString()))
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else
{
var data_conversionFactorObe = new GeneralAssumptionEntity();
try { data_conversionFactorObe.AffiliateId = Convert.ToInt64(AffiliateId); } catch { data_conversionFactorObe.AffiliateId = -1; }
data_conversionFactorObe.Key = AssumptionKey.KEY_ConversionFactorOBE;
try { data_conversionFactorObe.Value = Convert.ToDouble(conversionFactorObe); } catch { data_conversionFactorObe.Value = 0.0; }
dataList.Add(data_conversionFactorObe);
var data_prepaymentFactor = new GeneralAssumptionEntity();
try { data_prepaymentFactor.AffiliateId = Convert.ToInt64(AffiliateId); } catch { data_prepaymentFactor.AffiliateId = -1; }
data_prepaymentFactor.Key = AssumptionKey.KEY_PrePaymentFactor;
try { data_prepaymentFactor.Value = Convert.ToDouble(prepaymentFactor); } catch { data_prepaymentFactor.Value = 0.0; }
dataList.Add(data_prepaymentFactor);
}
}
}
return(dataList);
}
public List <GeneralAssumptionEntity> ReadFromExcel()
{
var dataList = new List <GeneralAssumptionEntity>();
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[10]; //CollateralType Sheet
int rows = worksheet.Dimension.Rows;
for (int i = 2; i <= rows; i++)
{
var AffiliateId = worksheet.Cells[i, 1].Value;
var collateralType = worksheet.Cells[i, 2].Value;
var ttrYears = worksheet.Cells[i, 3].Value;
if (AffiliateId == null)
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else if (string.IsNullOrWhiteSpace(AffiliateId.ToString()) || string.IsNullOrWhiteSpace(collateralType.ToString()))
{
//Console.WriteLine("Row is empty: " + i.ToString());
}
else
{
var data = new GeneralAssumptionEntity();
try { data.AffiliateId = Convert.ToInt64(AffiliateId); } catch { data.AffiliateId = -1; }
try {
data.Key = collateralType.ToString().ToLower().Contains("plant") ? "Plant & Equipment" :
(collateralType.ToString().ToLower().Contains("residential") ? "Residential Property" :
(collateralType.ToString().ToLower().Contains("commercial") ? "Commercial Property" : collateralType.ToString()));
} catch { data.Key = ""; }
try { data.Value = Convert.ToDouble(ttrYears); } catch { data.Value = 0.0; }
dataList.Add(data);
}
}
}
return(dataList);
}
public static string UpdateEadInputAssumptionByKey(GeneralAssumptionEntity input)
{
return($"Update [EadInputAssumptions] " +
$" set [Value] = {input.Value} " +
$" where [Key] = '{input.Key}' and [OrganizationUnitId] = {input.AffiliateId};");
}
public static string UpdateLgdCollateralTypeAssumption(GeneralAssumptionEntity input)
{
return($"Update [LgdInputAssumptions] set [Value] = {input.Value} where [LgdGroup] = 8 and [InputName] = '{input.Key}' and [OrganizationUnitId] = {input.AffiliateId}; ");
}