public void Create(int batchNumber, IEnumerable <CertificateResponse> certificates)
{
_aggregateLogger.LogInfo("Creating Excel Spreadsheet ....");
var memoryStream = new MemoryStream();
var certificateResponses = certificates as CertificateResponse[] ?? certificates.ToArray();
_certificates = certificateResponses;
var utcNow = DateTime.UtcNow;
var gmtNow = utcNow.UtcToTimeZoneTime(TimezoneNames.GmtStandardTimeZone);
var fileName = $"IFA-Certificate-{gmtNow:MMyy}-{batchNumber.ToString().PadLeft(3, '0')}.xlsx";
using (var package = new ExcelPackage(memoryStream))
{
CreateWorkBook(package);
CreateWorkSheet(batchNumber, package, certificateResponses);
package.Save();
_fileTransferClient.Send(memoryStream, fileName);
memoryStream.Close();
}
_aggregateLogger.LogInfo("Completed Excel Spreadsheet ....");
}
public async Task Create(int batchNumber, IEnumerable <CertificateResponse> certificates)
{
_aggregateLogger.LogInfo("Created Excel Spreadsheet ....");
var memoryStream = new MemoryStream();
var certificateResponses = certificates as CertificateResponse[] ?? certificates.ToArray();
_certificates = certificateResponses;
var uuid = Guid.NewGuid();
var fileName = $"IFA-Certificate-{GetMonthYear()}-{batchNumber.ToString().PadLeft(9, '0')}.xlsx";
using (var package = new ExcelPackage(memoryStream))
{
CreateWorkBook(package);
CreateWorkSheet(package, certificateResponses);
package.Save();
await _fileTransferClient.Send(memoryStream, fileName);
await WriteCopyOfMergedDocumentToBlob(fileName, memoryStream);
memoryStream.Close();
}
_aggregateLogger.LogInfo("Completed Excel Spreadsheet ....");
}
public static void StartUp(TraceWriter functionLogger, ExecutionContext context)
{
var configuration = ConfigurationHelper.GetConfiguration();
_logger = new AggregateLogger("das-assessor-service-func-epodatasync", functionLogger, context);
_logger.LogInfo("Initialising bootstrapper ....");
_logger.LogInfo("Config Received");
Container = new Container(configure =>
{
configure.Scan(x =>
{
x.TheCallingAssembly();
x.WithDefaultConventions();
});
configure.For <IAggregateLogger>().Use(_logger).Singleton();
configure.For <IWebConfiguration>().Use(configuration).Singleton();
configure.For <IProviderEventServiceApi>().Use <ProviderEventsServiceApi>().Singleton();
configure.For <IDbConnection>().Use(c => new SqlConnection(configuration.SqlConnectionString));
configure.For <IIlrsRefresherService>().Use <IlrsRefresherService>().Singleton();
});
var language = "en-GB";
System.Threading.Thread.CurrentThread.CurrentCulture =
new System.Globalization.CultureInfo(language);
}
public async Task Send(IEnumerable <CertificateResponse> certificateResponses)
{
var emailTemplate = await _assessorServiceApi.GetEmailTemplate(EMailTemplateNames.PrivatelyFundedCertificatesApprovals);
var personalisation = CreatePersonalisationTokens(certificateResponses);
_aggregateLogger.LogInfo("Send Email");
var recipients = emailTemplate.Recipients.Split(';').Select(x => x.Trim());
foreach (var recipient in recipients)
{
var email = new Email
{
RecipientsAddress = recipient,
TemplateId = emailTemplate.TemplateId,
ReplyToAddress = "*****@*****.**",
Subject = "Test Subject",
SystemId = "PrivatelyFundedCertificatesApprovals",
Tokens = personalisation
};
await _notificationsApi.SendEmail(email);
}
}
public async Task Send(int batchNumber, List <CertificateResponse> certificateResponses, string certificatesFileName)
{
var emailTemplate = await _assessorServiceApi.GetEmailTemplate(EMailTemplateNames.PrintAssessorCoverLetters);
var personalisation = CreatePersonalisationTokens(certificateResponses, certificatesFileName);
_aggregateLogger.LogInfo("Send Email");
var recipients = emailTemplate.Recipients.Split(';').Select(x => x.Trim());
foreach (var recipient in recipients)
{
var email = new Email
{
RecipientsAddress = recipient,
TemplateId = emailTemplate.TemplateId,
ReplyToAddress = "*****@*****.**",
Subject = "Test Subject",
SystemId = "PrintAssessorCoverLetters",
Tokens = personalisation
};
await _notificationsApi.SendEmail(email);
}
}
public void StartUp(string source, TraceWriter functionLogger, ExecutionContext context)
{
_logger = new AggregateLogger(source, functionLogger, context);
var configuration = ConfigurationHelper.GetConfiguration();
_logger.LogInfo("Config Received");
Container = new Container(configure =>
{
configure.Scan(x =>
{
x.TheCallingAssembly();
x.WithDefaultConventions();
});
configure.For <IAggregateLogger>().Use(_logger).Singleton();
configure.For <IWebConfiguration>().Use(configuration).Singleton();
configure.For <ICertificatesRepository>().Use <CertificatesRepository>().Singleton();
configure.For <SftpClient>().Use <SftpClient>("SftpClient",
c => new SftpClient(configuration.Sftp.RemoteHost, Convert.ToInt32(configuration.Sftp.Port), configuration.Sftp.Username, configuration.Sftp.Password));
configure.AddRegistry <NotificationsRegistry>();
configure.AddRegistry <HttpRegistry>();
});
}
public async Task <IEnumerable <CertificateResponse> > GetCertificatesToBePrinted()
{
var response = await _httpClient.GetAsync(
"/api/v1/certificates?status=Submitted");
var certificates = response.Deserialise <List <CertificateResponse> >();
if (response.IsSuccessStatusCode)
{
_aggregateLogger.LogInfo($"Status code returned: {response.StatusCode}. Content: {response.Content.ReadAsStringAsync().Result}");
}
else
{
_aggregateLogger.LogInfo($"Status code returned: {response.StatusCode}. Content: {response.Content.ReadAsStringAsync().Result}");
}
return(certificates);
}
public async Task Execute()
{
try
{
_aggregateLogger.LogInfo("Privately Funded Certificate Approval Notification Started");
_aggregateLogger.LogInfo($"Process Environment = {EnvironmentVariableTarget.Process}");
await _assessorServiceApi.UpdatePrivatelyFundedCertificateRequestsToBeApproved();
var certificatesToBeApproved = await _assessorServiceApi.GetCertificatesToBeApproved();
await _notificationService.Send(certificatesToBeApproved);
}
catch (Exception e)
{
_aggregateLogger.LogError("Function Errored", e);
throw;
}
}
public static List <CertificateResponse> Sanitise(this List <CertificateResponse> certificateResponses, IAggregateLogger logger)
{
var sanitisedCertificateResponse = new List <CertificateResponse>();
foreach (var certificateResponse in certificateResponses)
{
var errorFlag = false;
logger.LogInfo($"Sanitising Certificate - {certificateResponse.CertificateReference} ...");
var certificateData = certificateResponse.CertificateData;
if (string.IsNullOrEmpty(certificateData.ContactAddLine1))
{
errorFlag = true;
}
if (string.IsNullOrEmpty(certificateData.ContactPostCode))
{
errorFlag = true;
}
if (errorFlag)
{
if (!string.IsNullOrEmpty(certificateData.LearnerGivenNames) &&
!string.IsNullOrEmpty(certificateData.LearnerFamilyName))
{
logger.LogInfo(
$"Unprintable Certificate -> Given Names = {certificateData.LearnerGivenNames} Family Name = {certificateData.LearnerFamilyName} - Cannot be processed");
}
else
{
logger.LogInfo($"Unprintable Certificate - Cannot be processed");
}
}
else
{
sanitisedCertificateResponse.Add(certificateResponse);
}
}
return(sanitisedCertificateResponse);
}
public async Task Create(int batchNumber, IEnumerable <CertificateResponse> certificates)
{
var documentTemplateDataStream = await _documentTemplateDataStream.Get();
await CleanMergedDocumentContainer();
var certificateResponses = certificates as CertificateResponse[] ?? certificates.ToArray();
var groupedCertificates = certificateResponses.ToArray().GroupBy(
x => new
{
x.CertificateData.ContactOrganisation,
x.CertificateData.Department,
x.CertificateData.ContactName,
x.CertificateData.ContactPostCode
},
(key, group) => new
{
key1 = key.ContactOrganisation,
key2 = key.Department,
key3 = key.ContactName,
key4 = key.ContactPostCode,
Result = group.ToList()
});
var sequenceNumber = 0;
foreach (var groupedCertificate in groupedCertificates)
{
sequenceNumber++;
var certificate = groupedCertificate.Result[0];
var wordDocumentFileName = $"IFA-Certificate-{GetMonthYear()}-{batchNumber.ToString().PadLeft(9,'0')}-{certificate.CertificateData.ContactOrganisation}-{sequenceNumber}.docx";
_aggregateLogger.LogInfo($"Processing Certificate for Cover Letter - {certificate.CertificateReference} - {wordDocumentFileName}");
var wordStream = await CreateWordDocumentStream(wordDocumentFileName, certificate.CertificateData, documentTemplateDataStream);
_aggregateLogger.LogInfo($"converted certifcate data - Contact Name = {certificate.CertificateData.ContactName}");
await _fileTransferClient.Send(wordStream, wordDocumentFileName);
wordStream.Close();
}
documentTemplateDataStream.Close();
}
public async Task Execute()
{
try
{
_aggregateLogger.LogInfo("Function Started");
var webConfig = ConfigurationHelper.GetConfiguration();
_aggregateLogger.LogInfo("Config Received");
var token = _tokenService.GetToken();
_aggregateLogger.LogInfo("Token Received");
using (var client = new HttpClient())
{
client.DefaultRequestHeaders.Authorization = new System.Net.Http.Headers.AuthenticationHeaderValue("Bearer", token);
client.DefaultRequestHeaders.Add("accept", "application/json");
var response = await client.PostAsync($"{webConfig.ClientApiAuthentication.ApiBaseAddress}/api/v1/register-import", new StringContent(""));
var content = response.Content.ReadAsStringAsync().Result;
if (response.IsSuccessStatusCode)
{
_aggregateLogger.LogInfo($"Status code returned: {response.StatusCode}. Content: {content}");
}
else
{
_aggregateLogger.LogInfo($"Status code returned: {response.StatusCode}. Content: {content}");
}
}
}
catch (Exception e)
{
_aggregateLogger.LogError("Function Errored", e);
throw;
}
}
public async Task Execute()
{
try
{
_aggregateLogger.LogInfo("Function Started");
_aggregateLogger.LogInfo("Print Function Flow Started");
_aggregateLogger.LogInfo("Accessing Environment variables");
var customSetting =
Environment.GetEnvironmentVariable("CustomSetting", EnvironmentVariableTarget.Process);
_aggregateLogger.LogInfo($"Process Environment = {EnvironmentVariableTarget.Process}");
if (await AnythingToProcess())
{
var batchNumber = await _certificatesRepository.GenerateBatchNumber();
var certificates = (await _certificatesRepository.GetCertificatesToBePrinted()).ToList();
await _coverLetterService.Create(batchNumber, certificates);
await _ifaCertificateService.Create(batchNumber, certificates);
await _notificationService.Send();
await _certificatesRepository.ChangeStatusToPrinted(batchNumber.ToString(), certificates);
}
else
{
_aggregateLogger.LogInfo("Nothing to Process");
}
}
catch (Exception e)
{
_aggregateLogger.LogError("Function Errored", e);
throw;
}
}
public async Task <MemoryStream> Get()
{
var containerName = "printfunctionflow";
var container = await _initialiseContainer.GetContainer(containerName);
var blob = container.GetBlockBlobReference(TemplateFile);
var memoryStream = new MemoryStream();
blob.DownloadToStream(memoryStream);
_aggregateLogger.LogInfo($"Downloaded memory stream length = {blob.Properties.Length}");
return(memoryStream);
}
public ProviderEventsServiceApi(IAggregateLogger aggregateLogger)
{
_aggregateLogger = aggregateLogger;
var configuration = ConfigurationHelper.GetConfiguration();
if (configuration.ProviderEventsClientConfiguration != null && configuration.ProviderEventsSubmissionClientConfig != null)
{
Initialise(out _httpLearnerClient, configuration.ProviderEventsClientConfiguration.ApiBaseUrl, configuration.ProviderEventsClientConfiguration.ClientToken, configuration.ProviderEventsClientConfiguration.ApiVersion);
Initialise(out _httpSubmissionClient, configuration.ProviderEventsSubmissionClientConfig.ApiBaseUrl,
configuration.ProviderEventsSubmissionClientConfig.ClientToken, null);
}
else
{
_aggregateLogger.LogInfo($"Failed to read configurations.");
}
}
private async Task DownloadAndDeleteCertificatePrinterResponses()
{
var fileList = await _fileTransferClient.GetListOfDownloadedFiles();
// printResponse-MMYY-XXXXXX.json where XXX = 001, 002... 999999 etc
const string pattern = @"^[Pp][Rr][Ii][Nn][Tt][Rr][Ee][Ss][Pp][Oo][Nn][Ss][Ee]-[0-9]{4}-[0-9]{1,6}.json";
var certificateResponseFiles = fileList.Where(f => Regex.IsMatch(f, pattern));
var filesToProcesses = certificateResponseFiles as string[] ?? certificateResponseFiles.ToArray();
if (!filesToProcesses.Any())
{
_aggregateLogger.LogInfo("No certificate responses to process");
return;
}
foreach (var fileToProcess in filesToProcesses)
{
await ProcessEachFileToUploadThenDelete(fileToProcess);
}
}
public async Task <List <SubmissionEvent> > GetLatestLearnerEventForStandards(long uln, long sinceEventId = 0)
{
var url = $"/api/learners?uln={uln}";
if (sinceEventId > 0)
{
url += $"&sinceEventId={sinceEventId}";
}
using (var request = new HttpRequestMessage(HttpMethod.Get, url))
{
request.Headers.Add("Accept", "application/json");
using (var response = _httpLearnerClient.SendAsync(request))
{
var result = await response;
switch (result.StatusCode)
{
case HttpStatusCode.OK:
{
var json = await result.Content.ReadAsStringAsync();
_aggregateLogger.LogInfo($"Status code returned: {result.StatusCode} for Endpoint {url}");
return(await Task.Factory.StartNew(() => JsonConvert.DeserializeObject <List <SubmissionEvent> >(json, _jsonSettings)));
}
case HttpStatusCode.NotFound when result.IsSuccessStatusCode:
case HttpStatusCode.NotFound:
_aggregateLogger.LogInfo($"Status code returned: {result.StatusCode} for Endpoint {url}");
break;
}
}
return(null);
}
}
public async Task Execute()
{
_aggregateLogger.LogInfo("Standard Collation Gathering Function Started");
_aggregateLogger.LogInfo($"Process Environment = {EnvironmentVariableTarget.Process}");
await _assessorServiceApi.GatherStandards();
}
public void Send(MemoryStream memoryStream, string fileName)
{
_fileName = fileName;
lock (_lock)
{
using (var sftpClient = new SftpClient(_webConfiguration.Sftp.RemoteHost,
Convert.ToInt32(_webConfiguration.Sftp.Port),
_webConfiguration.Sftp.Username,
_webConfiguration.Sftp.Password))
{
sftpClient.Connect();
memoryStream.Position = 0; // ensure memory stream is set to begining of stream
_aggregateLogger.LogInfo($"Uploading file ... {_webConfiguration.Sftp.UploadDirectory}/{fileName}");
sftpClient.UploadFile(memoryStream, $"{_webConfiguration.Sftp.UploadDirectory}/{fileName}");
_aggregateLogger.LogInfo(
$"Validating Upload length of file ... {_webConfiguration.Sftp.UploadDirectory}/{fileName} = {memoryStream.Length}");
ValidateUpload(sftpClient, fileName, memoryStream.Length);
_aggregateLogger.LogInfo($"Validated the upload ...");
}
}
}
public void Create(int batchNumber, List <CertificateResponse> certificates, string fileName)
{
var printOutput = new PrintOutput
{
Batch = new BatchData()
{
BatchNumber = batchNumber,
BatchDate = DateTime.UtcNow
},
PrintData = new List <PrintData>()
};
printOutput.Batch.TotalCertificateCount = certificates.Count;
var groupedByRecipient = certificates.GroupBy(c =>
new
{
c.CertificateData.ContactName,
c.CertificateData.ContactOrganisation,
c.CertificateData.Department,
c.CertificateData.ContactAddLine1,
c.CertificateData.ContactAddLine2,
c.CertificateData.ContactAddLine3,
c.CertificateData.ContactAddLine4,
c.CertificateData.ContactPostCode
}).ToList();
printOutput.Batch.PostalContactCount = groupedByRecipient.Count;
groupedByRecipient.ForEach(g =>
{
var contactName = string.Empty;
if (g.Key.ContactName != null)
{
contactName = g.Key.ContactName.Replace("\t", " ");
}
var printData = new PrintData
{
PostalContact = new PostalContact
{
Name = contactName,
Department = g.Key.Department,
EmployerName = g.Key.ContactOrganisation,
AddressLine1 = g.Key.ContactAddLine1,
AddressLine2 = g.Key.ContactAddLine2,
AddressLine3 = g.Key.ContactAddLine3,
AddressLine4 = g.Key.ContactAddLine4,
Postcode = g.Key.ContactPostCode,
CertificateCount = g.Count()
},
CoverLetter = new CoverLetter
{
ChairName = "The Chair",
ChairTitle = "Chair of the board"
},
Certificates = new List <PrintCertificate>()
};
g.ToList().ForEach(c =>
{
var learnerName =
!string.IsNullOrEmpty(c.CertificateData.FullName)
? c.CertificateData.FullName
: $"{c.CertificateData.LearnerGivenNames} {c.CertificateData.LearnerFamilyName}";
var gradeText = string.Empty;
var grade = string.Empty;
if (!string.IsNullOrWhiteSpace(c.CertificateData.OverallGrade) && c.CertificateData.OverallGrade != "No grade awarded")
{
gradeText = "Achieved grade ";
grade = c.CertificateData.OverallGrade;
}
printData.Certificates.Add(new PrintCertificate
{
CertificateNumber = c.CertificateReference,
ApprenticeName =
learnerName.NameCase(),
LearningDetails = new LearningDetails()
{
StandardTitle = c.CertificateData.StandardName,
Level = $"LEVEL {c.CertificateData.StandardLevel}",
Option = string.IsNullOrWhiteSpace(c.CertificateData?.CourseOption) ? string.Empty: $"({c.CertificateData.CourseOption}):",
GradeText = gradeText,
Grade = grade,
AchievementDate = $"{c.CertificateData.AchievementDate.Value:dd MMMM yyyy}",
}
});
});
printOutput.PrintData.Add(printData);
});
_aggregateLogger.LogInfo("Completed Certificates to print Json ....");
var serializedPrintOutput = JsonConvert.SerializeObject(printOutput);
byte[] array = Encoding.ASCII.GetBytes(serializedPrintOutput);
using (var mystream = new MemoryStream(array))
{
_aggregateLogger.LogInfo("Sending Certificates to print Json ....");
_fileTransferClient.Send(mystream, fileName);
}
}
public async Task UpdateIlRsTable()
{
_aggregateLogger.LogInfo(@"Starting to update IlRs Table From Submissions ....");
var changedRecordsUlnCache = new List <long>();
var totalNumbersEffected = 0L;
var ilrResult =
await _connection.QuerySingleAsync <Ilr>(
$"Select distinct Max(EventId) as EventId from Ilrs where EventId is not null");
if (ilrResult != null)
{
var apiResults = await _eventServiceApi.GetSubmissionsEventsByEventId(ilrResult.EventId.GetValueOrDefault(), 1);
var numberOfPages = apiResults.TotalNumberOfPages;
for (var pageNumber = 1; pageNumber <= numberOfPages; pageNumber++)
{
if (pageNumber > 1)
{
apiResults =
await _eventServiceApi.GetSubmissionsEventsByEventId(ilrResult.EventId.GetValueOrDefault(), pageNumber);
}
if (apiResults?.Items == null || !apiResults.Items.Any())
{
continue;
}
totalNumbersEffected = await ProcessFromSubmissions(totalNumbersEffected, apiResults, changedRecordsUlnCache);
_aggregateLogger.LogInfo(
$"Rows effected for page number {pageNumber} : {totalNumbersEffected}");
}
}
_aggregateLogger.LogInfo(
$"Finished inserting into Ilrs table. Number of updates {totalNumbersEffected}");
await ProcessFromLearners(changedRecordsUlnCache);
//Clean up records with null names
await CleanupAnyIlrsRecordsWithNullNames();
}