/// <summary>
/// Read Apprenticeship Standards and populate ApprenticeshipStandard objects
/// </summary>
/// <returns></returns>
public IEnumerable <ApprenticeshipStandard> ReadStandardsFromFile(XSSFWorkbook jobProfileWorkbook)
{
var apprenticeshipStandardList = new List <ApprenticeshipStandard>();
using (var reader = new StreamReader(@"SeedData\ApprenticeshipStandards.xlsx"))
{
var workbook = new XSSFWorkbook(reader.BaseStream);
var sheet = workbook.GetSheet("ApprenticeshipStandards");
var nameIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Name").ColumnIndex;
var referenceIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Reference").ColumnIndex;
var levelIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Level").ColumnIndex;
var maximumFundingIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Maximum Funding (£)").ColumnIndex;
//Duration is in months
var typicalDurationIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Typical Duration").ColumnIndex;
var larsCodeIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "LARS code for providers only").ColumnIndex;
var routeIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Route").ColumnIndex;
var statusIndex = sheet.GetRow(0).Cells.Single(x => x.StringCellValue == "Status").ColumnIndex;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var status = row.GetCell(statusIndex).StringCellValue;
//What to do if a Job Profile has a standard that's not approved? -- Will not associate at the moment
if (status.Equals("Approved for delivery", StringComparison.InvariantCultureIgnoreCase))
{
var name = this.CleanseStandardName(row.GetCell(nameIndex).StringCellValue);
var reference = row.GetCell(referenceIndex).StringCellValue;
var level = row.GetCell(levelIndex).CellType == CellType.Numeric ? row.GetCell(levelIndex).NumericCellValue : 0;
var maximumFunding = row.GetCell(maximumFundingIndex).CellType == CellType.Numeric ? row.GetCell(maximumFundingIndex).NumericCellValue : 0;
var typicalDuration = row.GetCell(typicalDurationIndex).NumericCellValue;
var larsCode = row.GetCell(larsCodeIndex).CellType == CellType.Numeric ? row.GetCell(larsCodeIndex).NumericCellValue : 0;
var route = row.GetCell(routeIndex).StringCellValue;
if (_processAll || _apprenticeshipStandardsRefList.Contains(reference))
{
var apprenticeshipStandard = new ApprenticeshipStandard
{
Name = name,
Reference = reference,
Level = Convert.ToInt32(level),
MaximumFunding = Convert.ToInt32(maximumFunding),
Duration = Convert.ToInt32(typicalDuration),
LARSCode = Convert.ToInt32(larsCode),
Route = route.Split(',').Select(z => $"{char.ToUpper(z.Trim()[0]) + z.Trim().Substring(1)}").ToArray(),
Type = "Standard"
};
apprenticeshipStandardList.Add(apprenticeshipStandard);
}
}
}
}
BuildFrameworks(jobProfileWorkbook, apprenticeshipStandardList);
return(apprenticeshipStandardList);
}
/// <summary>
/// Add frameworks into the Standards collection
/// </summary>
/// <param name="apprenticeshipStandardList"></param>
private static void BuildFrameworks(XSSFWorkbook workbook, List <ApprenticeshipStandard> apprenticeshipStandardList)
{
//Load frameworks where standards are empty
var sheet = workbook.GetSheet("JobProfileSoc");
var apprenticeshipStandardsIndex = sheet.GetRow(0).Cells
.Single(x => x.StringCellValue == "apprenticeshipstandards").ColumnIndex;
var apprenticeshipFrameworksIndex = sheet.GetRow(0).Cells
.Single(x => x.StringCellValue == "apprenticeshipframeworks").ColumnIndex;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
var row = sheet.GetRow(i);
var standards = row.GetCell(apprenticeshipStandardsIndex).StringCellValue;
var frameworks = row.GetCell(apprenticeshipFrameworksIndex).StringCellValue;
if (string.IsNullOrWhiteSpace(standards) && !string.IsNullOrWhiteSpace(frameworks))
{
var splitFrameworks = frameworks.Split(",");
foreach (string framework in splitFrameworks)
{
var apprenticeshipStandard = new ApprenticeshipStandard {
Name = framework, Type = "Framework"
};
//Only add frameworks that don't already exist in the list
if (!apprenticeshipStandardList.Any(x => x.Type == "Framework" && x.Name == framework))
{
apprenticeshipStandardList.Add(apprenticeshipStandard);
}
}
}
}
}
