private FundingSummaryHeaderModel PopulateReportHeader(
SourceFileModel sourceFile,
IEnumerable <ILRFileDetailsModel> fileData,
int ukPrn,
CancellationToken cancellationToken)
{
var ukPrnRow =
new List <string> {
ukPrn.ToString(), string.Empty, string.Empty
};
var contractReferenceNumberRow =
new List <string> {
sourceFile.ConRefNumber, string.Empty, string.Empty, "ILR File :"
};
var supplementaryDataFileRow =
new List <string> {
sourceFile.FileName.Contains("/") ? sourceFile.FileName.Substring(sourceFile.FileName.IndexOf("/", StringComparison.Ordinal) + 1) : sourceFile.FileName, string.Empty, string.Empty, "Last ILR File Update :"
};
var lastSupplementaryDataFileUpdateRow =
new List <string> {
sourceFile.SuppliedDate?.ToString("dd/MM/yyyy hh:mm:ss"), string.Empty, string.Empty, "File Preparation Date :"
};
foreach (var model in fileData)
{
var preparationDate = FileNameHelper.GetPreparedDateFromILRFileName(model.FileName);
var secondYear = FileNameHelper.GetSecondYearFromReportYear(model.Year);
ukPrnRow.Add(string.Empty);
ukPrnRow.Add($"{model.Year}/{secondYear}");
contractReferenceNumberRow.Add(model.FileName.Substring(model.FileName.Contains("/") ? model.FileName.IndexOf("/", StringComparison.Ordinal) + 1 : 0));
contractReferenceNumberRow.Add(string.Empty);
supplementaryDataFileRow.Add(model.LastSubmission?.ToString("dd/MM/yyyy hh:mm:ss"));
supplementaryDataFileRow.Add(string.Empty);
lastSupplementaryDataFileUpdateRow.Add(preparationDate);
lastSupplementaryDataFileUpdateRow.Add(string.Empty);
}
var header = new FundingSummaryHeaderModel
{
ProviderName = _referenceDataCache.GetProviderName(ukPrn, cancellationToken),
Ukprn = ukPrnRow.ToArray(),
ContractReferenceNumber = contractReferenceNumberRow.ToArray(),
SupplementaryDataFile = supplementaryDataFileRow.ToArray(),
LastSupplementaryDataFileUpdate = lastSupplementaryDataFileUpdateRow.ToArray()
};
return(header);
}
private Workbook GetWorkbookReport(
Workbook workbook,
Worksheet sheet,
FundingSummaryHeaderModel fundingSummaryHeaderModel,
IEnumerable <FundingSummaryModel> fundingSummaryModels,
FundingSummaryFooterModel fundingSummaryFooterModel)
{
WriteExcelRecords(sheet, new FundingSummaryHeaderMapper(), new List <FundingSummaryHeaderModel> {
fundingSummaryHeaderModel
}, _cellStyles[5], _cellStyles[5], true);
foreach (var fundingSummaryModel in fundingSummaryModels)
{
if (string.IsNullOrEmpty(fundingSummaryModel.Title))
{
WriteBlankRow(sheet);
continue;
}
CellStyle excelHeaderStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelHeaderStyle);
if (fundingSummaryModel.HeaderType == HeaderType.TitleOnly)
{
WriteTitleRecord(sheet, fundingSummaryModel.Title, excelHeaderStyle, _reportWidth);
continue;
}
if (fundingSummaryModel.HeaderType == HeaderType.All)
{
_fundingSummaryMapper.MemberMaps.Single(x => x.Data.Index == 0).Name(fundingSummaryModel.Title);
_cachedHeaders[0] = fundingSummaryModel.Title;
WriteRecordsFromArray(sheet, _fundingSummaryMapper, _cachedHeaders, excelHeaderStyle);
continue;
}
CellStyle excelRecordStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelRecordStyle);
WriteExcelRecordsFromModelProperty(sheet, _fundingSummaryMapper, _cachedModelProperties, fundingSummaryModel, excelRecordStyle);
}
WriteExcelRecords(sheet, new FundingSummaryFooterMapper(), new List <FundingSummaryFooterModel> {
fundingSummaryFooterModel
}, _cellStyles[5], _cellStyles[5], true);
return(workbook);
}
public async Task GenerateReport(
SupplementaryDataWrapper supplementaryDataWrapper,
SourceFileModel sourceFile,
ZipArchive archive,
CancellationToken cancellationToken)
{
var ukPrn = Convert.ToInt32(sourceFile.UKPRN);
var sourceFiles = await _supplementaryDataService.GetImportFiles(sourceFile.UKPRN, cancellationToken);
var supplementaryData =
await _supplementaryDataService.GetSupplementaryData(sourceFiles, cancellationToken);
var ilrYearlyFileData = await _ilrService.GetIlrFileDetails(ukPrn, cancellationToken);
var fm70YearlyData = (await _ilrService.GetYearlyIlrData(ukPrn, cancellationToken)).ToList();
FundingSummaryHeaderModel fundingSummaryHeaderModel =
PopulateReportHeader(sourceFile, ilrYearlyFileData, ukPrn, cancellationToken);
var workbook = new Workbook();
workbook.Worksheets.Clear();
foreach (var file in sourceFiles)
{
var fundingYear = FileNameHelper.GetFundingYearFromFileName(file.FileName);
var thisYearsFm70Data = fm70YearlyData.Where(d => d.FundingYear == fundingYear);
var fundingSummaryModels = PopulateReportData(thisYearsFm70Data, supplementaryData[file.SourceFileId]).ToList();
ApplyFundingYearToEmptyFundingYears(fundingSummaryModels, fundingYear);
FundingSummaryFooterModel fundingSummaryFooterModel = PopulateReportFooter(cancellationToken);
FundingSummaryModel rowOfData = fundingSummaryModels.FirstOrDefault(x => x.DeliverableCode == "ST01" && x.YearlyValues.Any());
var yearAndDataLengthModels = new List <YearAndDataLengthModel>();
if (rowOfData != null)
{
int valCount = rowOfData.YearlyValues.Sum(x => x.Values.Length);
_reportWidth = valCount + rowOfData.Totals.Count + 2;
foreach (FundingSummaryReportYearlyValueModel fundingSummaryReportYearlyValueModel in
rowOfData.YearlyValues)
{
yearAndDataLengthModels.Add(new YearAndDataLengthModel(
fundingSummaryReportYearlyValueModel.FundingYear,
fundingSummaryReportYearlyValueModel.Values.Length));
}
}
_cachedHeaders = GetHeaderEntries(yearAndDataLengthModels);
_cellStyles = _excelStyleProvider.GetFundingSummaryStyles(workbook);
Worksheet sheet = workbook.Worksheets.Add(file.ConRefNumber);
workbook = GetWorkbookReport(workbook, sheet, fundingSummaryHeaderModel, fundingSummaryModels, fundingSummaryFooterModel);
ApplyAdditionalFormatting(workbook, rowOfData);
}
string externalFileName = GetExternalFilename(sourceFile.UKPRN, sourceFile.JobId ?? 0, sourceFile.SuppliedDate ?? DateTime.MinValue);
string fileName = GetFilename(sourceFile.UKPRN, sourceFile.JobId ?? 0, sourceFile.SuppliedDate ?? DateTime.MinValue);
using (var ms = new MemoryStream())
{
workbook.Save(ms, SaveFormat.Xlsx);
await _storage.SaveAsync($"{externalFileName}.xlsx", ms, cancellationToken);
await WriteZipEntry(archive, $"{fileName}.xlsx", ms, cancellationToken);
}
}
public async Task <string> GenerateReport(
IEsfJobContext esfJobContext,
ISourceFileModel sourceFile,
SupplementaryDataWrapper wrapper,
CancellationToken cancellationToken)
{
var ukPrn = esfJobContext.UkPrn;
var conRefNumbers = await _referenceDataService.GetContractAllocationsForUkprn(ukPrn, cancellationToken);
if (!conRefNumbers.Any())
{
conRefNumbers = new List <string> {
NotApplicable
};
}
var collectionYear = Convert.ToInt32($"20{esfJobContext.CollectionYear.ToString().Substring(0, 2)}");
var sourceFiles = await _supplementaryDataService.GetImportFiles(esfJobContext.UkPrn.ToString(), cancellationToken);
_logger.LogDebug($"{sourceFiles.Count} esf files found for ukprn {ukPrn} and collection year 20{esfJobContext.CollectionYear.ToString().Substring(0, 2)}.");
var supplementaryData = await _supplementaryDataService.GetSupplementaryData(collectionYear, sourceFiles, cancellationToken);
var ilrYearlyFileData = (await _ilrService.GetIlrFileDetails(ukPrn, collectionYear, cancellationToken)).ToList();
var fm70YearlyData = (await _ilrService.GetYearlyIlrData(ukPrn, esfJobContext.CollectionName, collectionYear, esfJobContext.ReturnPeriod, cancellationToken)).ToList();
var workbook = new Workbook();
workbook.Worksheets.Clear();
foreach (var conRefNumber in conRefNumbers)
{
var file = sourceFiles.FirstOrDefault(sf => sf.ConRefNumber.CaseInsensitiveEquals(conRefNumber));
FundingSummaryHeaderModel fundingSummaryHeaderModel =
PopulateReportHeader(file, ilrYearlyFileData, ukPrn, conRefNumber, cancellationToken);
var fm70YearlyDataForConRef = new List <FM70PeriodisedValuesYearly>();
var supplementaryDataYearlyModels = new List <SupplementaryDataYearlyModel>();
supplementaryData.TryGetValue(conRefNumber, out var suppData);
foreach (var fm70Data in fm70YearlyData)
{
var periodisedValuesPerConRef = fm70Data.Fm70PeriodisedValues.Where(x => conRefNumber.CaseInsensitiveEquals(x.ConRefNumber)).ToList();
fm70YearlyDataForConRef.Add(new FM70PeriodisedValuesYearly()
{
Fm70PeriodisedValues = periodisedValuesPerConRef,
FundingYear = fm70Data.FundingYear
});
supplementaryDataYearlyModels.Add(new SupplementaryDataYearlyModel
{
FundingYear = fm70Data.FundingYear,
SupplementaryData = suppData?.FirstOrDefault(x => x.FundingYear == fm70Data.FundingYear)?.SupplementaryData ?? new List <SupplementaryDataModel>()
});
}
var fundingSummaryModels = PopulateReportData(collectionYear, fm70YearlyDataForConRef, supplementaryDataYearlyModels).ToList();
ReplaceConRefNumInTitle(fundingSummaryModels, conRefNumber);
FundingSummaryFooterModel fundingSummaryFooterModel = await PopulateReportFooter(cancellationToken);
FundingSummaryModel rowOfData = fundingSummaryModels.FirstOrDefault(x => x.DeliverableCode == "ST01" && x.YearlyValues.Any());
var yearAndDataLengthModels = new List <YearAndDataLengthModel>();
if (rowOfData != null)
{
int valCount = rowOfData.YearlyValues.Sum(x => x.Values.Count);
_reportWidth = valCount + rowOfData.Totals.Count + 2;
foreach (FundingSummaryReportYearlyValueModel fundingSummaryReportYearlyValueModel in
rowOfData.YearlyValues)
{
yearAndDataLengthModels.Add(new YearAndDataLengthModel(
fundingSummaryReportYearlyValueModel.FundingYear,
fundingSummaryReportYearlyValueModel.Values.Count));
}
}
_cachedHeaders = GetHeaderEntries(collectionYear, yearAndDataLengthModels);
_cellStyles = _excelStyleProvider.GetFundingSummaryStyles(workbook);
Worksheet sheet = workbook.Worksheets.Add(conRefNumber);
sheet.Cells.StandardWidth = 19;
sheet.Cells.Columns[0].Width = 63.93;
sheet.IsGridlinesVisible = false;
AddImageToReport(sheet);
workbook = GetWorkbookReport(workbook, sheet, fundingSummaryHeaderModel, fundingSummaryModels, fundingSummaryFooterModel);
}
string externalFileName = GetExternalFilename(ukPrn.ToString(), esfJobContext.JobId, sourceFile?.SuppliedDate ?? DateTime.MinValue, _excelExtension);
await WriteExcelFile(esfJobContext, externalFileName, workbook, cancellationToken);
return(externalFileName);
}
private Workbook GetWorkbookReport(
Workbook workbook,
Worksheet sheet,
FundingSummaryHeaderModel fundingSummaryHeaderModel,
IEnumerable <FundingSummaryModel> fundingSummaryModels,
FundingSummaryFooterModel fundingSummaryFooterModel)
{
{
WriteExcelRecords(sheet, new FundingSummaryHeaderMapper(), new List <FundingSummaryHeaderModel> {
fundingSummaryHeaderModel
}, _cellStyles[7], _cellStyles[7], true);
var firstFutureColumn = GetFirstFutureColumn();
int lastOperatedRow;
// number of columns minus number of static columns (3)
var endColumn = _cachedHeaders.Count() - 3;
foreach (var fundingSummaryModel in fundingSummaryModels)
{
if (string.IsNullOrEmpty(fundingSummaryModel.Title))
{
WriteBlankRow(sheet);
continue;
}
CellStyle excelHeaderStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelHeaderStyle);
if (fundingSummaryModel.HeaderType == HeaderType.TitleOnly)
{
WriteTitleRecord(sheet, fundingSummaryModel.Title, excelHeaderStyle, _reportWidth);
continue;
}
if (fundingSummaryModel.HeaderType == HeaderType.All)
{
// Align data to the Right
excelHeaderStyle.Style.HorizontalAlignment = TextAlignmentType.Right;
excelHeaderStyle.StyleFlag.HorizontalAlignment = true;
_fundingSummaryMapper.MemberMaps.Single(x => x.Data.Index == 0).Name(fundingSummaryModel.Title);
_cachedHeaders[0] = fundingSummaryModel.Title;
// this line is the month/year header
WriteRecordsFromArray(sheet, _fundingSummaryMapper, _cachedHeaders, excelHeaderStyle);
lastOperatedRow = GetCurrentRow(sheet) - 1;
// ItaliciseFutureData(sheet, firstFutureColumn, lastOperatedRow, endColumn);
continue;
}
CellStyle excelRecordStyle = _excelStyleProvider.GetCellStyle(_cellStyles, fundingSummaryModel.ExcelRecordStyle);
// Align data to the Right
excelRecordStyle.Style.HorizontalAlignment = TextAlignmentType.Right;
excelRecordStyle.StyleFlag.HorizontalAlignment = true;
// this line is subtotals below the month/ year header
WriteExcelRecordsFromModelProperty(sheet, _fundingSummaryMapper, _cachedModelProperties, fundingSummaryModel, excelRecordStyle);
lastOperatedRow = GetCurrentRow(sheet) - 1;
// ItaliciseFutureData(sheet, firstFutureColumn, lastOperatedRow, endColumn);
}
for (int i = 0; i < workbook.Worksheets.Count; i++)
{
AlignWorkSheetColumnData(workbook.Worksheets[i], 0, TextAlignmentType.Left);
}
WriteExcelRecords(sheet, new FundingSummaryFooterMapper(), new List <FundingSummaryFooterModel> {
fundingSummaryFooterModel
}, _cellStyles[7], _cellStyles[7], true);
return(workbook);
}
}
private FundingSummaryHeaderModel PopulateReportHeader(
SourceFileModel sourceFile,
IEnumerable <ILRFileDetails> fileData,
int ukPrn,
string conRefNumber,
CancellationToken cancellationToken)
{
var ukPrnRow =
new List <string> {
ukPrn.ToString(), null, null
};
var contractReferenceNumberRow =
new List <string> {
conRefNumber, null, null, "ILR File :"
};
var supplementaryDataFileRow =
new List <string> {
sourceFile?.FileName?.Contains("/") ?? false?sourceFile.FileName.Substring(sourceFile.FileName.IndexOf("/", StringComparison.Ordinal) + 1) : sourceFile?.FileName, null, null, "Last ILR File Update :"
};
var lastSupplementaryDataFileUpdateRow =
new List <string> {
sourceFile?.SuppliedDate?.ToString("dd/MM/yyyy hh:mm:ss"), null, null, "File Preparation Date :"
};
var securityClassificationRow =
new List <string> {
"OFFICIAL-SENSITIVE", null, null, null
};
foreach (var model in fileData)
{
var preparationDate = GetPreparedDateFromILRFileName(model.FileName);
var secondYear = GetSecondYearFromReportYear(model.Year);
ukPrnRow.Add(null);
ukPrnRow.Add($"{model.Year}/{secondYear}");
contractReferenceNumberRow.Add(model.FileName?.Substring(model.FileName.Contains("/") ? model.FileName.IndexOf("/", StringComparison.Ordinal) + 1 : 0));
contractReferenceNumberRow.Add(null);
supplementaryDataFileRow.Add(preparationDate);
supplementaryDataFileRow.Add(null);
lastSupplementaryDataFileUpdateRow.Add(model.FilePreparationDate?.ToString("dd/MM/yyyy hh:mm:ss"));
lastSupplementaryDataFileUpdateRow.Add(null);
if (model.Equals(fileData.Last()))
{
continue;
}
securityClassificationRow.Add("(most recent closed collection for year)");
securityClassificationRow.Add(null);
}
var header = new FundingSummaryHeaderModel
{
ProviderName = _referenceDataService.GetProviderName(ukPrn, cancellationToken),
Ukprn = ukPrnRow.ToArray(),
ContractReferenceNumber = contractReferenceNumberRow.ToArray(),
SupplementaryDataFile = supplementaryDataFileRow.ToArray(),
LastSupplementaryDataFileUpdate = lastSupplementaryDataFileUpdateRow.ToArray(),
SecurityClassification = securityClassificationRow.ToArray()
};
return(header);
}
