public async Task StoreAsJsonAsync <T>(T data, string fileName, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
using (var fileStream = await _fileService.OpenWriteStreamAsync(fileName, ncsJobContextMessage.DctContainer, cancellationToken))
{
_jsonSerializationService.Serialize(data, fileStream);
}
}
public async Task ExecuteAsync(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
Debug.WriteLine("Entered Funding Task");
var fundingYearStartDate = CalculateFundingYearStart(ncsJobContextMessage);
var dssData = await _dssDataRetrievalService.GetDataForTouchpoint(ncsJobContextMessage.TouchpointId, ncsJobContextMessage.ReportEndDate, fundingYearStartDate);
if (dssData.Any())
{
_logger.LogInfo($"Retrieved {dssData.Count()} records from DSS for TouchpointId {ncsJobContextMessage.TouchpointId}");
var ncsSubmission = BuildNcsSubmission(dssData, ncsJobContextMessage);
var fundingValues = await _fundingService.CalculateFundingAsync(ncsSubmission, ncsJobContextMessage, cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
await _persistenceService.PersistSubmissionAndFundingValuesAsync(ncsSubmission, fundingValues, ncsJobContextMessage, cancellationToken);
var fileName = _filenameService.GetFilename(ncsJobContextMessage.Ukprn, ncsJobContextMessage.JobId, FileNameConstants.sourceData, ncsJobContextMessage.DssTimeStamp, OutputTypes.Json);
await _storageService.StoreAsJsonAsync(dssData, fileName, ncsJobContextMessage, cancellationToken);
}
else
{
_logger.LogInfo($"0 records retrieved from DSS for TouchpointId {ncsJobContextMessage.TouchpointId}, exiting funding task");
}
}
public async Task ClearFundingDataAsync(INcsJobContextMessage ncsJobContextMessage, SqlConnection sqlConnection, SqlTransaction sqlTransaction, CancellationToken cancellationToken)
{
using (SqlCommand sqlCommand = new SqlCommand(BuildDeleteFundingDataSql(ncsJobContextMessage), sqlConnection, sqlTransaction))
{
await sqlCommand.ExecuteNonQueryAsync(cancellationToken);
}
}
public async Task <IEnumerable <FundingValue> > GetFundingValuesAsync(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
using (var context = _ncsContext())
{
return(await context.FundingValues
.Where(fv => fv.TouchpointId.Equals(ncsJobContextMessage.TouchpointId) && fv.CollectionYear.Equals(ncsJobContextMessage.CollectionYear))
.ToListAsync(cancellationToken));
}
}
public async Task <IEnumerable <NcsSubmission> > GetNcsSubmissionsAsync(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
using (var context = _ncsContext())
{
return(await context.NcsSubmissions
.Where(ns => ns.TouchpointId.Equals(ncsJobContextMessage.TouchpointId) && ns.CollectionYear.Equals(ncsJobContextMessage.CollectionYear))
.ToListAsync(cancellationToken));
}
}
private async Task CopyZipToDss(string dctZipName, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
using (var fileStream = await _dssFileService.OpenWriteStreamAsync(ncsJobContextMessage.ReportFileName, ncsJobContextMessage.DssContainer, cancellationToken))
{
using (var readStream = await _dctFileService.OpenReadStreamAsync(dctZipName, ncsJobContextMessage.DctContainer, cancellationToken))
{
await readStream.CopyToAsync(fileStream, 8096, cancellationToken);
}
}
}
public IDictionary <string, string> BuildHeaderData(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
var lastNcsSubmissionDate = _sourceQueryService.GetLastNcsSubmissionDate(ncsJobContextMessage, cancellationToken);
var providerName = _orgDataService.GetProviderName(ncsJobContextMessage.Ukprn, cancellationToken);
var lastNcsUpdate = lastNcsSubmissionDate != null?lastNcsSubmissionDate.Value.ToString("dd/MM/yyyy hh:mm:ss") : "Unknown";
return(new Dictionary <string, string>()
{
{ FundingSummaryReportConstants.ProviderName, providerName },
{ FundingSummaryReportConstants.TouchpointId, ncsJobContextMessage.TouchpointId },
{ FundingSummaryReportConstants.LastNcsUpdate, lastNcsUpdate },
{ FundingSummaryReportConstants.SecurityClassification, FundingSummaryReportConstants.OfficialSensitive }
});
}
public async Task PublishNcsMessage(string status, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
var message = new DssPublishMessageModel
{
Status = status,
JobId = ncsJobContextMessage.DssJobId
};
await _queuePublishService.PublishAsync(message);
_logger.LogInfo($"{status} message sent to Ncs");
}
private DateTime CalculateFundingYearStart(INcsJobContextMessage ncsJobContextMessage)
{
if (ncsJobContextMessage.CollectionYear == CollectionYearConstants.CollectionYear1920)
{
return(CollectionYearConstants.FundingYear1920StartDate);
}
if (ncsJobContextMessage.CollectionYear == CollectionYearConstants.CollectionYear2021)
{
return(CollectionYearConstants.FundingYear2021StartDate);
}
throw new Exception($"Collection year:{ncsJobContextMessage.CollectionYear} unknown.");
}
public async Task <bool> CallbackAsync(IEnumerable <INcsDataTask> ncsTasks, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
_logger.LogInfo("NCS callback invoked");
foreach (var task in ncsTasks)
{
_logger.LogInfo($"NCS Service Task : {task.TaskName} Starting");
await task.ExecuteAsync(ncsJobContextMessage, cancellationToken);
_logger.LogInfo($"NCS Service Task : {task.TaskName} Finished");
}
await _messageService.PublishNcsMessage(MessageConstants.Success, ncsJobContextMessage, cancellationToken);
return(true);
}
public DateTime?GetLastNcsSubmissionDate(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
DateTime?lastNcsSubmissionDate;
cancellationToken.ThrowIfCancellationRequested();
using (var context = _ncsContext())
{
lastNcsSubmissionDate = context.Sources
.Where(s => s.TouchpointId.Equals(ncsJobContextMessage.TouchpointId))
.OrderByDescending(s => s.SubmissionDate)
.FirstOrDefault()
?.SubmissionDate;
}
return(lastNcsSubmissionDate);
}
public async Task PersistSourceDataAsync(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
var sourceData = new Source()
{
UKPRN = ncsJobContextMessage.Ukprn,
TouchpointId = ncsJobContextMessage.TouchpointId,
SubmissionDate = ncsJobContextMessage.DssTimeStamp,
DssJobId = ncsJobContextMessage.DssJobId,
CreatedOn = _dateTimeProvider.GetNowUtc()
};
using (var context = _ncsContext())
{
context.Sources.Add(sourceData);
await context.SaveChangesAsync(cancellationToken);
_logger.LogInfo("NCS Source data persisted Successfully.");
}
}
public FundingSummaryColumnHeaders BuildColumnHeaders(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
var startYear = ncsJobContextMessage.CollectionYear.ToString().Substring(0, 2);
var endYear = ncsJobContextMessage.CollectionYear.ToString().Substring(2, 2);
return(new FundingSummaryColumnHeaders()
{
April = $"{FundingSummaryReportConstants.April}-{startYear}",
May = $"{FundingSummaryReportConstants.May}-{startYear}",
June = $"{FundingSummaryReportConstants.June}-{startYear}",
July = $"{FundingSummaryReportConstants.July}-{startYear}",
August = $"{FundingSummaryReportConstants.August}-{startYear}",
September = $"{FundingSummaryReportConstants.September}-{startYear}",
October = $"{FundingSummaryReportConstants.October}-{startYear}",
November = $"{FundingSummaryReportConstants.November}-{startYear}",
December = $"{FundingSummaryReportConstants.December}-{startYear}",
January = $"{FundingSummaryReportConstants.January}-{endYear}",
February = $"{FundingSummaryReportConstants.February}-{endYear}",
March = $"{FundingSummaryReportConstants.March}-{endYear}"
});
}
private void PopulateWorkbook(Workbook workbook, INcsJobContextMessage ncsJobContextMessage, IEnumerable <ReportDataModel> data, CancellationToken cancellationToken)
{
var headerData = _builder.BuildHeaderData(ncsJobContextMessage, cancellationToken);
var columnHeaders = _builder.BuildColumnHeaders(ncsJobContextMessage, cancellationToken);
var priorityGroupRows = _builder.BuildPriorityGroupRows(data);
var nonPriorityGroupRows = _builder.BuildNonPriorityGroupRows(data);
var footerData = _builder.BuildFooterData(cancellationToken);
Worksheet sheet = workbook.Worksheets[0];
// Header
RenderDictionary(sheet, 0, headerData);
// Body
RenderColumnHeaders(sheet, 5, columnHeaders);
_excelService.WriteExcelRows(sheet, priorityGroupRows, 7);
_excelService.WriteExcelRows(sheet, nonPriorityGroupRows, 13);
// Footer
RenderDictionary(sheet, 21, footerData);
workbook.CalculateFormula();
}
public async Task ExecuteAsync(INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
cancellationToken.ThrowIfCancellationRequested();
Debug.WriteLine("Entered Reporting Task");
try
{
var submissionData = await _ncsSubmissionQueryService.GetNcsSubmissionsAsync(ncsJobContextMessage, cancellationToken);
var fundingData = await _fundingValueQueryService.GetFundingValuesAsync(ncsJobContextMessage, cancellationToken);
var reportData = BuildReportData(submissionData, fundingData);
await _reportingController.ProduceReportsAsync(reportData, ncsJobContextMessage, cancellationToken);
await _persistenceService.PersistSourceDataAsync(ncsJobContextMessage, cancellationToken);
}
catch (Exception ex)
{
_logger.LogError("Reporting Task Failed", ex);
throw;
}
}
private ICollection <NcsSubmission> BuildNcsSubmission(IEnumerable <DssDataModel> dssData, INcsJobContextMessage ncsJobContextMessage)
{
return(dssData.Select(item => new NcsSubmission
{
ActionPlanId = item.ActionPlanId,
AdviserName = item.AdviserName,
CustomerId = item.CustomerID,
DateOfBirth = item.DateOfBirth,
HomePostCode = item.HomePostCode,
OutcomeEffectiveDate = item.OutcomeEffectiveDate,
OutcomeId = item.OutcomeId,
OutcomePriorityCustomer = item.OutcomePriorityCustomer,
OutcomeType = item.OutcomeType,
SubContractorId = item.SubContractorId,
SessionDate = item.SessionDate,
UKPRN = ncsJobContextMessage.Ukprn,
TouchpointId = ncsJobContextMessage.TouchpointId,
DssJobId = ncsJobContextMessage.DssJobId,
DssTimestamp = ncsJobContextMessage.DssTimeStamp,
CollectionYear = ncsJobContextMessage.CollectionYear,
CreatedOn = _dateTimeProvider.GetNowUtc()
}).ToList());
}
private IEnumerable <OccupancyReportModel> GetOccupancyReportModel(IEnumerable <ReportDataModel> data, INcsJobContextMessage ncsJobContextMessage)
{
return(data
.Where(d => d.OutcomeEffectiveDate <= ncsJobContextMessage.ReportEndDate)
.OrderBy(d => d.CustomerId)
.ThenBy(d => d.ActionPlanId)
.ThenBy(d => d.OutcomeId)
.Select(d => new OccupancyReportModel()
{
CustomerId = d.CustomerId,
DateOfBirth = d.DateOfBirth,
HomePostCode = d.HomePostCode,
ActionPlanId = d.ActionPlanId,
SessionDate = d.SessionDate,
SubContractorId = d.SubContractorId,
AdviserName = d.AdviserName,
OutcomeId = d.OutcomeId,
OutcomeType = _outcomeTypesDictionary[d.OutcomeType],
OutcomeEffectiveDate = d.OutcomeEffectiveDate,
OutcomePriorityCustomer = d.OutcomePriorityCustomer,
PeriodValues = BuildPeriodValues(d)
}));
}
public async Task <IEnumerable <FundingValue> > CalculateFundingAsync(IEnumerable <NcsSubmission> ncsSubmissions, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
var priorityCommunityRate = await _outcomeRateService.GetOutcomeRatesByPriorityAsync(OutcomeRatesConstants.Priority, cancellationToken);
var nonPriorityCommunityRate = await _outcomeRateService.GetOutcomeRatesByPriorityAsync(OutcomeRatesConstants.NonPriority, cancellationToken);
var fundingValues = new List <FundingValue>();
foreach (var submission in ncsSubmissions)
{
FundingValue fundingValue;
switch (submission.OutcomePriorityCustomer)
{
case OutcomeRatesConstants.Priority:
fundingValue = BuildFundingValue(submission, priorityCommunityRate);
break;
case OutcomeRatesConstants.NonPriority:
fundingValue = BuildFundingValue(submission, nonPriorityCommunityRate);
break;
default:
throw new Exception($"The outcome priority customer {submission.OutcomePriorityCustomer}, is not a valid value");
}
fundingValues.Add(fundingValue);
}
return(fundingValues);
}
public async Task PersistSubmissionAndFundingValuesAsync(IEnumerable <NcsSubmission> ncsSubmissions, IEnumerable <FundingValue> fundingValues, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
using (SqlConnection ncsConnection = new SqlConnection(_dataServiceConfiguration.NcsDbConnection))
{
await ncsConnection.OpenAsync(cancellationToken);
cancellationToken.ThrowIfCancellationRequested();
_logger.LogDebug("Starting NCS submission and funding data Transaction");
using (SqlTransaction ncsTransaction = ncsConnection.BeginTransaction())
{
try
{
await _clearService.ClearSubmissionDataAsync(ncsJobContextMessage, ncsConnection, ncsTransaction, cancellationToken);
await _bulkInsert.Insert(DatabaseConstants.NcsSubmissionTable, ncsSubmissions, ncsConnection, ncsTransaction, cancellationToken);
await _clearService.ClearFundingDataAsync(ncsJobContextMessage, ncsConnection, ncsTransaction, cancellationToken);
await _bulkInsert.Insert(DatabaseConstants.FundingValuesTable, fundingValues, ncsConnection, ncsTransaction, cancellationToken);
ncsTransaction.Commit();
}
catch (Exception ex)
{
ncsTransaction.Rollback();
_logger.LogError($"NCS Transaction failed rolling back - {ex.Message}");
throw;
}
_logger.LogDebug("NCS Transaction complete");
}
}
}
public async Task ProduceReportsAsync(IEnumerable <ReportDataModel> reportData, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
var reportOutputFileNames = new List <string>();
foreach (var report in _ncsReports)
{
var reportsGenerated = await report.GenerateReport(reportData, ncsJobContextMessage, cancellationToken);
reportOutputFileNames.AddRange(reportsGenerated);
}
var zipName = _filenameService.GetZipName(ncsJobContextMessage.Ukprn, ncsJobContextMessage.JobId, ReportConstants.DctZipName);
await _zipService.CreateZipAsync(zipName, reportOutputFileNames, ncsJobContextMessage.DctContainer, cancellationToken);
await CopyZipToDss(zipName, ncsJobContextMessage, cancellationToken);
}
public ClassMap <OccupancyReportModel> Build(INcsJobContextMessage ncsJobContextMessage)
{
return(new OccupancyReportMapper(ncsJobContextMessage.CollectionYear));
}
private string BuildDeleteFundingDataSql(INcsJobContextMessage ncsJobContextMessage)
{
return($@"DELETE FROM {DatabaseConstants.FundingValuesTable}
WHERE TouchpointId = '{ncsJobContextMessage.TouchpointId}'
AND CollectionYear = {ncsJobContextMessage.CollectionYear}");
}
public async Task <string[]> GenerateReport(IEnumerable <ReportDataModel> data, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
_logger.LogInfo("Generating Occupancy Report");
var fileName = _filenameService.GetFilename(ncsJobContextMessage.Ukprn, ncsJobContextMessage.JobId, ReportNameConstants.Occupancy, ncsJobContextMessage.DssTimeStamp, OutputTypes.Csv);
var reportData = GetOccupancyReportModel(data, ncsJobContextMessage);
var mapper = _classMapFactory.Build(ncsJobContextMessage);
await _csvFileService.WriteAsync(reportData, fileName, ncsJobContextMessage.DctContainer, cancellationToken, classMap : mapper);
_logger.LogInfo("Occupancy Report generated");
return(new[] { fileName });
}
public async Task <string[]> GenerateReport(IEnumerable <ReportDataModel> data, INcsJobContextMessage ncsJobContextMessage, CancellationToken cancellationToken)
{
_logger.LogInfo("Generating Funding Summary Report");
var fileName = _filenameService.GetFilename(ncsJobContextMessage.Ukprn, ncsJobContextMessage.JobId, ReportNameConstants.FundingSummary, ncsJobContextMessage.DssTimeStamp, OutputTypes.Excel);
var manifestResourceStream = _streamProviderService.GetStreamFromTemplate(ReportTemplateConstants.FundingSummaryReport);
var reportData = GetReportData(data, ncsJobContextMessage);
Workbook workbook = new Workbook(manifestResourceStream);
PopulateWorkbook(workbook, ncsJobContextMessage, reportData, cancellationToken);
await _excelService.SaveWorkbookAsync(workbook, fileName, ncsJobContextMessage.DctContainer, cancellationToken);
_logger.LogInfo("Funding Summary Report generated");
return(new[] { fileName });
}
private IEnumerable <ReportDataModel> GetReportData(IEnumerable <ReportDataModel> data, INcsJobContextMessage ncsJobContextMessage)
{
return(data.Where(d => d.OutcomeEffectiveDate <= ncsJobContextMessage.ReportEndDate));
}