public async Task <CSVFileData> HandleAsync(FetchInvoiceRunCsv message)
{
authorization.EnsureCanAccessInternalArea();
var items = await context.StoredProcedures.SpgInvoiceRunChargeBreakdown(message.InvoiceRunId);
var csvWriter = csvWriterFactory.Create <PCSChargesCSVData>();
csvWriter.DefineColumn(@"Scheme name", i => i.SchemeName);
csvWriter.DefineColumn(@"Compliance year", i => i.ComplianceYear);
csvWriter.DefineColumn(@"Submission date and time (GMT)", i => i.SubmissionDate.ToString("dd/MM/yyyy HH:mm:ss"));
csvWriter.DefineColumn(@"Producer name", i => i.ProducerName);
csvWriter.DefineColumn(@"PRN", i => i.PRN);
csvWriter.DefineColumn(@"Charge value (GBP)", i => i.ChargeValue);
csvWriter.DefineColumn(@"Charge band", i => i.ChargeBandType);
csvWriter.DefineColumn(@"Reg. Off. or PPoB country", i => i.RegOfficeOrPBoBCountry);
csvWriter.DefineColumn(@"Includes annual charge", i => i.HasAnnualCharge);
string fileContent = csvWriter.Write(items);
var invoiceRun = await commonDataAccess.FetchInvoiceRunAsync(message.InvoiceRunId);
string fileName = string.Format("invoicerun_{0}_{1}.csv", invoiceRun.CompetentAuthority.Abbreviation, invoiceRun.IssuedDate.ToString("ddMMyyyy"));
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <ActionResult> DownloadErrorsAndWarnings(Guid pcsId, Guid dataReturnUploadId)
{
SchemePublicInfo scheme = await cache.FetchSchemePublicInfo(pcsId);
DataReturnForSubmission dataReturn = await FetchDataReturnUpload(pcsId, dataReturnUploadId);
CsvWriter <IErrorOrWarning> csvWriter = csvWriterFactory.Create <IErrorOrWarning>();
csvWriter.DefineColumn("Type", e => e.TypeName);
csvWriter.DefineColumn("Description", e => e.Description);
List <IErrorOrWarning> errorsAndWarnings = new List <IErrorOrWarning>();
errorsAndWarnings.AddRange(dataReturn.Errors);
errorsAndWarnings.AddRange(dataReturn.Warnings);
string csv = csvWriter.Write(errorsAndWarnings);
string filename = string.Format(
"{0}_{1}{2}_data_return_errors_and_warnings_{3}.csv",
scheme.ApprovalNo,
dataReturn.Year,
dataReturn.Quarter,
DateTime.Now.ToString("ddMMyyyy_HHmm"));
byte[] fileContent = Encoding.UTF8.GetBytes(csv);
return(File(fileContent, "text/csv", CsvFilenameFormat.FormatFileName(filename)));
}
public CsvWriter <ProducerEeeCsvData> CreateWriter(string obligationType)
{
CsvWriter <ProducerEeeCsvData> csvWriter = csvWriterFactory.Create <ProducerEeeCsvData>();
csvWriter.DefineColumn(@"Scheme name", i => i.SchemeName);
csvWriter.DefineColumn(@"Scheme approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn(@"PRN", i => i.PRN);
csvWriter.DefineColumn(@"Producer name", i => i.ProducerName);
csvWriter.DefineColumn(@"Producer country", i => i.ProducerCountry);
string totalEEEtitle = string.Format("Total EEE {0} (t)", obligationType);
csvWriter.DefineColumn(totalEEEtitle, i => i.TotalTonnage);
foreach (int category in Enumerable.Range(1, 14))
{
foreach (int quarterType in Enumerable.Range(1, 4))
{
string title = string.Format("Cat{0} {1} Q{2} (t)", category, obligationType, quarterType);
string columnName = string.Format("Cat{0}Q{1}", category, quarterType);
csvWriter.DefineColumn(title, i => i.GetType().GetProperty(columnName).GetValue(i));
}
}
return(csvWriter);
}
public async Task <CSVFileData> HandleAsync(GetUkNonObligatedWeeeReceivedDataCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
var message = $"Compliance year cannot be \"{request.ComplianceYear}\".";
throw new ArgumentException(message);
}
var items = await context.StoredProcedures.GetUkNonObligatedWeeeReceivedByComplianceYear(request.ComplianceYear);
var csvWriter = csvWriterFactory.Create <UkNonObligatedWeeeReceivedData>();
csvWriter.DefineColumn(ReportConstants.QuarterColumnHeading, i => i.Quarter);
csvWriter.DefineColumn(ReportConstants.CategoryColumnHeading, i => i.Category);
csvWriter.DefineColumn(ReportConstants.NonObligatedColumnHeading, i => i.TotalNonObligatedWeeeReceived);
csvWriter.DefineColumn(ReportConstants.NonObligatedDcfColumnHeading, i => i.TotalNonObligatedWeeeReceivedFromDcf);
var fileContent = csvWriter.Write(items);
var fileName = $"{request.ComplianceYear}_UK non-obligated WEEE received at AATFs_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetReturnNonObligatedCsv request)
{
var @return = await dataAccess.GetById <Return>(request.ReturnId);
authorization.EnsureOrganisationAccess(@return.Organisation.Id);
var items = await weeContext.StoredProcedures.GetReturnNonObligatedCsvData(@return.Id);
var fileName =
$"{@return.Quarter.Year}_{@return.Quarter.Q}_{@return.Organisation.OrganisationName}_Non-obligated return data_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
var csvWriter = csvWriterFactory.Create <NonObligatedWeeeReceivedCsvData>();
csvWriter.DefineColumn(@"Compliance year", i => i.Year);
csvWriter.DefineColumn(@"Quarter", i => i.Quarter);
csvWriter.DefineColumn(@"Submitted by", i => i.SubmittedBy);
csvWriter.DefineColumn(@"Submitted date (GMT)", i => i.SubmittedDate);
csvWriter.DefineColumn(@"Name of operator", i => i.OrganisationName);
csvWriter.DefineColumn(@"Category", i => i.Category);
csvWriter.DefineColumn(@"Total non-obligated WEEE received (t)", i => i.TotalNonObligatedWeeeReceived);
if (await SelectedDcf(@return.Id))
{
csvWriter.DefineColumn(@"Non-obligated WEEE kept / retained by DCFs (t)", i => i.TotalNonObligatedWeeeReceivedFromDcf);
}
var fileContent = csvWriter.Write(items);
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public void Create_BuildsInstance()
{
var f = new CsvWriterFactory();
var config = new Configuration();
var tw = Mock.Of <TextWriter>();
var instance = f.Create(tw, config);
Assert.NotNull(instance);
}
public CsvWriter <MissingProducerDataCsvData> CreateWriter()
{
CsvWriter <MissingProducerDataCsvData> csvWriter = csvWriterFactory.Create <MissingProducerDataCsvData>();
csvWriter.DefineColumn(@"PCS name", i => i.SchemeName);
csvWriter.DefineColumn(@"PCS approval no.", i => i.ApprovalNumber);
csvWriter.DefineColumn(@"Producer name", i => i.ProducerName);
csvWriter.DefineColumn(@"Producer PRN", i => i.PRN);
csvWriter.DefineColumn(@"Obligation type", i => i.ObligationType);
csvWriter.DefineColumn(@"Quarter", i => i.Quarter);
csvWriter.DefineColumn(@"Date registered", i => i.DateRegistered.ToString("dd/MM/yyyy HH:mm:ss"));
return(csvWriter);
}
public async Task <CSVFileData> HandleAsync(GetAllAatfReuseSitesCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
var message = $"Compliance year cannot be \"{request.ComplianceYear}\".";
throw new ArgumentException(message);
}
var reuseSitesData = await weeContext.StoredProcedures.GetAllAatfReuseSitesCsvData(request.ComplianceYear, request.AuthorityId, request.PanArea);
var csvWriter = csvWriterFactory.Create <AatfReuseSitesData>();
csvWriter.DefineColumn(@"Appropriate authority", i => i.Abbreviation);
csvWriter.DefineColumn(@"WROS Pan Area Team", i => i.PanName);
csvWriter.DefineColumn(@"EA Area", i => i.LaName);
csvWriter.DefineColumn(@"Compliance year", i => i.ComplianceYear);
csvWriter.DefineColumn(@"Quarter", i => i.Quarter);
csvWriter.DefineColumn(@"Submitted by", i => i.SubmittedBy);
csvWriter.DefineColumn(@"Date submitted (GMT)", i => i.SubmittedDate);
csvWriter.DefineColumn(@"Name of AATF", i => i.Name);
csvWriter.DefineColumn(@"Approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn(@"Organisation name", i => i.OrgName);
csvWriter.DefineColumn(@"Reuse site name", i => i.SiteName);
csvWriter.DefineColumn(@"Reuse site address", i => i.SiteAddress);
var fileContent = csvWriter.Write(reuseSitesData);
var fileName = $"{request.ComplianceYear}";
if (request.AuthorityId.HasValue)
{
var authority = await commonDataAccess.FetchCompetentAuthorityById(request.AuthorityId.Value);
fileName += "_" + authority.Abbreviation;
}
if (request.PanArea.HasValue)
{
var panArea = await commonDataAccess.FetchLookup <PanArea>(request.PanArea.Value);
fileName += "_" + panArea.Name;
}
fileName += $"_AATF using reuse sites_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetProducerPublicRegisterCSV request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
string message = string.Format("Compliance year cannot be \"{0}\".", request.ComplianceYear);
throw new ArgumentException(message);
}
var items = await context.StoredProcedures.SpgProducerPublicRegisterCSVDataByComplianceYear(
request.ComplianceYear);
CsvWriter <ProducerPublicRegisterCSVData> csvWriter =
csvWriterFactory.Create <ProducerPublicRegisterCSVData>();
csvWriter.DefineColumn(@"Producer name", i => i.ProducerName);
csvWriter.DefineColumn(@"Producer trading name", i => i.TradingName);
csvWriter.DefineColumn(@"Obligation type", i => i.ObligationType);
csvWriter.DefineColumn(@"Registered office address / principal place of business", i => !string.IsNullOrEmpty(i.CompanyName) ?
ConcatAddress(new[]
{
i.ROAPrimaryName, i.ROASecondaryName, i.ROAStreet, i.ROATown, i.ROALocality, i.ROAAdministrativeArea, i.ROACountry, i.ROAPostCode
})
: ConcatAddress(new[]
{
i.PPOBPrimaryName, i.PPOBSecondaryName, i.PPOBStreet, i.PPOBTown, i.PPOBLocality, i.PPOBAdministrativeArea, i.PPOBCountry, i.PPOBPostcode
}));
csvWriter.DefineColumn(@"Registered office phone number", i => !string.IsNullOrEmpty(i.CompanyName) ? i.ROATelephone : string.Empty, true);
csvWriter.DefineColumn(@"Registered office email address", i => !string.IsNullOrEmpty(i.CompanyName) ? i.ROAEmail : string.Empty);
csvWriter.DefineColumn(@"Producer registration number (PRN)", i => i.PRN);
csvWriter.DefineColumn(@"Producer compliance scheme (PCS) name", i => i.SchemeName);
csvWriter.DefineColumn(@"PCS operator name", i => i.SchemeOperator);
csvWriter.DefineColumn(@"PCS registered office", i => ConcatAddress(new[] { i.CSROAAddress1, i.CSROAAddress2, i.CSROATownOrCity, i.CSROACountyOrRegion, i.CSROACountry, i.CSROAPostcode }));
csvWriter.DefineColumn(@"Overseas producer name and address", i => ConcatAddress(new[] { i.OPNAName, i.OPNAPrimaryName, i.OPNASecondaryName, i.OPNAStreet, i.OPNATown, i.OPNALocality, i.OPNAAdministrativeArea, i.OPNACountry, i.OPNAPostCode }));
csvWriter.DefineColumn(@"Compliance year", i => i.ComplianceYear.ToString());
string fileContent = csvWriter.Write(items);
var fileName = string.Format("{0}_producerpublicregister_{1:ddMMyyyy_HHmm}.csv",
request.ComplianceYear,
DateTime.UtcNow);
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetPcsAatfComparisonData request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
var message = $"Compliance year cannot be \"{request.ComplianceYear}\".";
throw new ArgumentException(message);
}
var items = await context.StoredProcedures.GetPcsAatfComparisonDataCsvData(request.ComplianceYear, request.Quarter, request.ObligationType);
var csvWriter = csvWriterFactory.Create <PcsAatfComparisonDataCsvData>();
csvWriter.DefineColumn(@"Compliance year", i => i.ComplianceYear);
csvWriter.DefineColumn(@"Quarter", i => i.QuarterValue);
csvWriter.DefineColumn(@"Obligation type", i => i.ObligationType);
csvWriter.DefineColumn(@"Category", i => i.Category);
csvWriter.DefineColumn(@"PCS name", i => i.SchemeNameValue);
csvWriter.DefineColumn(@"PCS approval number", i => i.PcsApprovalNumber);
csvWriter.DefineColumn(@"PCS appropriate authority", i => i.PcsAbbreviation);
csvWriter.DefineColumn(@"AATF name", i => i.AatfName);
csvWriter.DefineColumn(@"AATF approval number", i => i.AatfApprovalNumber);
csvWriter.DefineColumn(@"AATF appropriate authority", i => i.AatfAbbreviation);
csvWriter.DefineColumn(@"PCS report (t)", i => i.PcsTonnage);
csvWriter.DefineColumn(@"AATF report(t)", i => i.AatfTonnage);
csvWriter.DefineColumn(@"Discrepancy between PCS and AATF reports (t)", i => i.DifferenceTonnage);
var fileContent = csvWriter.Write(items);
var fileNameParameters = string.Empty;
if (request.Quarter != null)
{
fileNameParameters += $"_Q{request.Quarter}";
}
if (!string.IsNullOrWhiteSpace(request.ObligationType))
{
fileNameParameters += $"_{request.ObligationType}";
}
var fileName = $"{request.ComplianceYear}{fileNameParameters}_PCS v AATF WEEE data comparison_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <FileInfo> HandleAsync(FetchIssuedChargesCsv message)
{
authorization.EnsureCanAccessInternalArea();
UKCompetentAuthority authority = await dataAccess.FetchCompetentAuthority(message.Authority);
IEnumerable <ProducerSubmission> results = await dataAccess.FetchInvoicedProducerSubmissionsAsync(authority, message.ComplianceYear, message.SchemeId);
CsvWriter <ProducerSubmission> csvWriter = csvWriterFactory.Create <ProducerSubmission>();
csvWriter.DefineColumn("Scheme name", ps => ps.RegisteredProducer.Scheme.SchemeName);
csvWriter.DefineColumn("Compliance year", ps => ps.RegisteredProducer.ComplianceYear);
csvWriter.DefineColumn("Submission date and time (GMT)", ps => ps.MemberUpload.SubmittedDate.Value.ToString("dd/MM/yyyy HH:mm:ss"));
csvWriter.DefineColumn("Producer name", ps => ps.OrganisationName);
csvWriter.DefineColumn("PRN", ps => ps.RegisteredProducer.ProducerRegistrationNumber);
csvWriter.DefineColumn("Charge value (GBP)", ps => ps.ChargeThisUpdate);
csvWriter.DefineColumn("Charge band", ps => ps.ChargeBandAmount.ChargeBand);
csvWriter.DefineColumn("Issued date", ps => ps.MemberUpload.InvoiceRun.IssuedDate.ToString("dd/MM/yyyy HH:mm:ss"));
csvWriter.DefineColumn(@"Reg. Off. or PPoB country", ps => ps.RegOfficeOrPBoBCountry);
csvWriter.DefineColumn(@"Includes annual charge", ps => ps.HasAnnualCharge);
string content = csvWriter.Write(results);
byte[] data = Encoding.UTF8.GetBytes(content);
string schemeApprovalNumber = string.Empty;
string fileName = string.Empty;
if (message.SchemeId.HasValue)
{
//get approval number for scheme to display in the filename.
Domain.Scheme.Scheme scheme = await dataAccess.FetchSchemeAsync(message.SchemeId);
schemeApprovalNumber = scheme.ApprovalNumber.Replace("/", string.Empty);
fileName = string.Format(
"{0}_{1}_issuedcharges_{2:ddMMyyyy_HHmm}.csv",
message.ComplianceYear,
schemeApprovalNumber,
SystemTime.UtcNow);
}
else
{
fileName = string.Format(
"{0}_issuedcharges_{1:ddMMyyyy_HHmm}.csv",
message.ComplianceYear,
SystemTime.UtcNow);
}
return(new FileInfo(fileName, data));
}
public CsvWriter <SchemeObligationCsvData> CreateWriter()
{
CsvWriter <SchemeObligationCsvData> csvWriter = csvWriterFactory.Create <SchemeObligationCsvData>();
csvWriter.DefineColumn(@"Scheme approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn(@"Scheme name", i => i.SchemeName);
csvWriter.DefineColumn(@"PRN", i => i.PRN);
csvWriter.DefineColumn(@"Producer name", i => i.ProducerName);
csvWriter.DefineColumn(@"Obligation type previous compliance year", i => i.ObligationTypeForPreviousYear);
csvWriter.DefineColumn(@"Obligation type compliance year selected", i => i.ObligationTypeForSelectedYear);
foreach (int category in Enumerable.Range(1, 14))
{
string title = string.Format("Cat{0} B2C (t)", category);
string columnName = string.Format("Cat{0}B2CTotal", category);
csvWriter.DefineColumn(title, i => i.GetType().GetProperty(columnName).GetValue(i));
}
return(csvWriter);
}
public async Task <ActionResult> DownloadCSV(Guid schemeId, int year, Guid memberUploadId, DateTime submissionDateTime)
{
using (var client = apiClient())
{
IEnumerable <ErrorData> errors =
(await client.SendAsync(User.GetAccessToken(), new GetMemberUploadData(schemeId, memberUploadId)))
.OrderByDescending(e => e.ErrorLevel);
CsvWriter <ErrorData> csvWriter = csvWriterFactory.Create <ErrorData>();
csvWriter.DefineColumn("Description", e => e.Description);
var schemePublicInfo = await cache.FetchSchemePublicInfo(schemeId);
var csvFileName = string.Format("{0}_memberregistration_{1}_warnings_{2}.csv", schemePublicInfo.ApprovalNo, year, submissionDateTime.ToString("ddMMyyyy_HHmm"));
string csv = csvWriter.Write(errors);
byte[] fileContent = new UTF8Encoding().GetBytes(csv);
return(File(fileContent, "text/csv", CsvFilenameFormat.FormatFileName(csvFileName)));
}
}
public async Task <ProducerCSVFileData> HandleAsync(GetProducerCSV request)
{
authorization.EnsureInternalOrOrganisationAccess(request.OrganisationId);
var organisation = await context.Organisations.FindAsync(request.OrganisationId);
var scheme = await context.Schemes.SingleAsync(s => s.OrganisationId == request.OrganisationId);
if (organisation == null)
{
string message = string.Format("An organisation could not be found with ID \"{0}\".", request.OrganisationId);
throw new ArgumentException(message);
}
var items = await context.StoredProcedures.SpgCSVDataByOrganisationIdAndComplianceYear(
request.OrganisationId,
request.ComplianceYear);
CsvWriter <ProducerCsvData> csvWriter = csvWriterFactory.Create <ProducerCsvData>();
csvWriter.DefineColumn("Producer name", i => i.OrganisationName);
csvWriter.DefineColumn("Trading name", i => i.TradingName);
csvWriter.DefineColumn("Producer registration number", i => i.RegistrationNumber);
csvWriter.DefineColumn("Company registration number", i => i.CompanyNumber);
csvWriter.DefineColumn("Charge band", i => i.ChargeBand);
csvWriter.DefineColumn("Obligation Type", i => i.ObligationType);
csvWriter.DefineColumn("Date & time (GMT) registered", i => i.DateRegistered.ToString("dd/MM/yyyy HH:mm:ss"));
csvWriter.DefineColumn("Date & time (GMT) last updated", i => (i.DateRegistered.ToString("dd/MM/yyyy HH:mm:ss").Equals(i.DateAmended.ToString("dd/MM/yyyy HH:mm:ss")) ? string.Empty : i.DateAmended.ToString("dd/MM/yyyy HH:mm:ss")));
csvWriter.DefineColumn("Authorised representative", i => i.AuthorisedRepresentative);
csvWriter.DefineColumn("Overseas producer", i => i.OverseasProducer);
string fileContent = csvWriter.Write(items);
var fileName = string.Format("{0}_fullmemberlist_{1}_{2}.csv", scheme.ApprovalNumber, request.ComplianceYear, DateTime.Now.ToString("ddMMyyyy_HHmm"));
return(new ProducerCSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetUkNonObligatedWeeeReceivedAtAatfsDataCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
var message = $"Compliance year cannot be \"{request.ComplianceYear}\".";
throw new ArgumentException(message);
}
var items = await context.StoredProcedures.GetNonObligatedWeeeReceivedAtAatf(request.ComplianceYear, request.AatfName);
var csvWriter = csvWriterFactory.Create <NonObligatedWeeeReceivedCsvData>();
csvWriter.DefineColumn(ReportConstants.YearColumnHeading, i => i.Year);
csvWriter.DefineColumn(ReportConstants.QuarterColumnHeading, i => i.Quarter);
csvWriter.DefineColumn(ReportConstants.SubmittedByColumnHeading, i => i.SubmittedBy);
csvWriter.DefineColumn(ReportConstants.DateSubmittedColumnHeading, i => i.SubmittedDate);
csvWriter.DefineColumn(ReportConstants.OrganisationColumnHeading, i => i.OrganisationName);
csvWriter.DefineColumn(ReportConstants.CategoryColumnHeading, i => i.Category);
csvWriter.DefineColumn(ReportConstants.NonObligatedColumnHeading, i => i.TotalNonObligatedWeeeReceived);
csvWriter.DefineColumn(ReportConstants.NonObligatedDcfColumnHeading, i => i.TotalNonObligatedWeeeReceivedFromDcf);
var fileContent = csvWriter.Write(items);
var fileNameParameters = string.Empty;
if (!string.IsNullOrWhiteSpace(request.AatfName))
{
fileNameParameters += $"_{request.AatfName}";
}
var fileName = $"{request.ComplianceYear}{fileNameParameters}_AATF non-obligated WEEE data_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetUkEeeDataCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
string message = string.Format("Compliance year cannot be \"{0}\".", request.ComplianceYear);
throw new ArgumentException(message);
}
var items = await context.StoredProcedures.SpgUKEEEDataByComplianceYear(
request.ComplianceYear);
CsvWriter <UkEeeCsvData> csvWriter = csvWriterFactory.Create <UkEeeCsvData>();
csvWriter.DefineColumn(@"Category", i => i.Category);
csvWriter.DefineColumn(@"Total B2B EEE (t)", i => i.TotalB2BEEE);
csvWriter.DefineColumn(@"Q1 B2B EEE (t)", i => i.Q1B2BEEE);
csvWriter.DefineColumn(@"Q2 B2B EEE (t)", i => i.Q2B2BEEE);
csvWriter.DefineColumn(@"Q3 B2B EEE (t)", i => i.Q3B2BEEE);
csvWriter.DefineColumn(@"Q4 B2B EEE (t)", i => i.Q4B2BEEE);
csvWriter.DefineColumn(@"Total B2C EEE (t)", i => i.TotalB2CEEE);
csvWriter.DefineColumn(@"Q1 B2C EEE (t)", i => i.Q1B2CEEE);
csvWriter.DefineColumn(@"Q2 B2C EEE (t)", i => i.Q2B2CEEE);
csvWriter.DefineColumn(@"Q3 B2C EEE (t)", i => i.Q3B2CEEE);
csvWriter.DefineColumn(@"Q4 B2C EEE (t)", i => i.Q4B2CEEE);
var fileContent = csvWriter.Write(items);
var fileName = string.Format("{0}_UK_EEE_{1:ddMMyyyy_HHmm}.csv",
request.ComplianceYear,
DateTime.UtcNow);
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public CsvWriter <DataReturnSummaryCsvData> CreateWriter()
{
CsvWriter <DataReturnSummaryCsvData> writer = csvWriterFactory.Create <DataReturnSummaryCsvData>();
writer.DefineColumn("Quarter", r => string.Format("Q{0}", r.Quarter));
writer.DefineColumn("EEE or WEEE (tonnes)", r => descriptions[new Tuple <int, int?>(r.Type, r.Source)]);
writer.DefineColumn("Obligation type", r => r.ObligationType);
writer.DefineColumn("Cat 1 (t)", r => string.Format("{0:F3}", r.Category1));
writer.DefineColumn("Cat 2 (t)", r => string.Format("{0:F3}", r.Category2));
writer.DefineColumn("Cat 3 (t)", r => string.Format("{0:F3}", r.Category3));
writer.DefineColumn("Cat 4 (t)", r => string.Format("{0:F3}", r.Category4));
writer.DefineColumn("Cat 5 (t)", r => string.Format("{0:F3}", r.Category5));
writer.DefineColumn("Cat 6 (t)", r => string.Format("{0:F3}", r.Category6));
writer.DefineColumn("Cat 7 (t)", r => string.Format("{0:F3}", r.Category7));
writer.DefineColumn("Cat 8 (t)", r => string.Format("{0:F3}", r.Category8));
writer.DefineColumn("Cat 9 (t)", r => string.Format("{0:F3}", r.Category9));
writer.DefineColumn("Cat 10 (t)", r => string.Format("{0:F3}", r.Category10));
writer.DefineColumn("Cat 11 (t)", r => string.Format("{0:F3}", r.Category11));
writer.DefineColumn("Cat 12 (t)", r => string.Format("{0:F3}", r.Category12));
writer.DefineColumn("Cat 13 (t)", r => string.Format("{0:F3}", r.Category13));
writer.DefineColumn("Cat 14 (t)", r => string.Format("{0:F3}", r.Category14));
return(writer);
}
public async Task <CSVFileData> HandleAsync(GetAatfAeDetailsCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
var message = $"Compliance year cannot be \"{request.ComplianceYear}\".";
throw new ArgumentException(message);
}
var facilityType = request.FacilityType != null ? (int)request.FacilityType : 4;
List <AatfAeDetailsData> items = await context.StoredProcedures.GetAatfAeDetailsCsvData(
request.ComplianceYear, facilityType,
request.AuthorityId, request.LocalArea, request.PanArea);
string type = request.FacilityType.ToString().ToUpper();
var csvWriter = csvWriterFactory.Create <AatfAeDetailsData>();
csvWriter.DefineColumn($"Compliance year", i => i.ComplianceYear);
csvWriter.DefineColumn($"Appropriate authority", i => i.AppropriateAuthorityAbbr);
if (!request.IsPublicRegister)
{
csvWriter.DefineColumn($"WROS Pan Area Team", i => i.PanAreaTeam);
csvWriter.DefineColumn($"EA Area", i => i.EaArea);
csvWriter.DefineColumn($"AATF, AE or PCS?", i => i.RecordType);
csvWriter.DefineColumn($"Name", i => i.Name);
csvWriter.DefineColumn($"Approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn($"Status", i => i.Status);
csvWriter.DefineColumn($"AATF / AE address1", i => i.Address1);
csvWriter.DefineColumn($"AATF / AE address2", i => i.Address2);
csvWriter.DefineColumn($"AATF / AE town or city", i => i.TownCity);
csvWriter.DefineColumn($"AATF / AE county or region", i => i.CountyRegion);
csvWriter.DefineColumn($"AATF / AE postcode", i => i.PostCode);
csvWriter.DefineColumn($"AATF / AE country", i => i.Country);
csvWriter.DefineColumn($"PCS billing reference", i => i.IbisCustomerReference);
csvWriter.DefineColumn($"PCS obligation type", i => i.ObligationType);
csvWriter.DefineColumn($"AATF / AE date of approval", i => i.ApprovalDateString);
csvWriter.DefineColumn($"AATF / AE size", i => i.Size);
csvWriter.DefineColumn($"Contact first name", i => i.FirstName);
csvWriter.DefineColumn($"Contact last name", i => i.LastName);
csvWriter.DefineColumn($"Contact position", i => i.ContactPosition);
csvWriter.DefineColumn($"Contact address1", i => i.ContactAddress1);
csvWriter.DefineColumn($"Contact address2", i => i.ContactAddress2);
csvWriter.DefineColumn($"Contact town or city", i => i.ContactTownCity);
csvWriter.DefineColumn($"Contact county or region", i => i.ContactCountyRegion);
csvWriter.DefineColumn($"Contact postcode", i => i.ContactPostcode);
csvWriter.DefineColumn($"Contact country", i => i.ContactCountry);
csvWriter.DefineColumn($"Contact phone number", i => i.ContactPhone);
csvWriter.DefineColumn($"Contact email", i => i.ContactEmail);
csvWriter.DefineColumn($"Organisation type", i => i.OrganisationTypeString);
csvWriter.DefineColumn($"Organisation name", i => i.OperatorName);
csvWriter.DefineColumn($"Organisation business trading name", i => i.OperatorTradingName);
csvWriter.DefineColumn($"Organisation company registration number", i => i.CompanyRegistrationNumber);
csvWriter.DefineColumn($"Organisation address1", i => i.OrganisationAddress1);
csvWriter.DefineColumn($"Organisation address2", i => i.OrganisationAddress2);
csvWriter.DefineColumn($"Organisation town or city", i => i.OrganisationTownCity);
csvWriter.DefineColumn($"Organisation county or region", i => i.OrganisationCountyRegion);
csvWriter.DefineColumn($"Organisation postcode", i => i.OrganisationPostcode);
csvWriter.DefineColumn($"Organisation country", i => i.OrganisationCountry);
csvWriter.DefineColumn($"Organisation telephone", i => i.OrganisationTelephone);
csvWriter.DefineColumn($"Organisation email", i => i.OrganisationEmail);
}
else
{
csvWriter.DefineColumn($"Name of {type}", i => i.Name);
csvWriter.DefineColumn($"{type} address", i => i.AatfAddress);
csvWriter.DefineColumn($"{type} postcode", i => i.PostCode);
csvWriter.DefineColumn($"{type} country", i => i.Country);
csvWriter.DefineColumn($"EA Area for the {type}", i => i.EaArea);
csvWriter.DefineColumn($"{type} approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn($"Date of approval", i => i.ApprovalDateString);
csvWriter.DefineColumn($"{type} size", i => i.Size);
csvWriter.DefineColumn($"{type} status", i => i.Status);
csvWriter.DefineColumn($"Name of operator", i => i.OperatorName);
csvWriter.DefineColumn($"Business trading name of operator", i => i.OperatorTradingName);
csvWriter.DefineColumn($"Operator address ", i => i.OperatorAddress);
csvWriter.DefineColumn($"Operator postcode", i => i.OrganisationPostcode);
csvWriter.DefineColumn($"Operator country", i => i.OrganisationCountry);
}
var fileContent = csvWriter.Write(items);
var additionalParameters = string.Empty;
var additionalText = string.Empty;
var facilityText = string.Empty;
if (request.FacilityType.HasValue)
{
facilityText = $"_{request.FacilityType.ToString().ToUpper()}";
}
if (request.AuthorityId.HasValue)
{
additionalParameters += $"_{(await commonDataAccess.FetchCompetentAuthorityById(request.AuthorityId.Value)).Abbreviation}";
}
if (request.PanArea.HasValue)
{
additionalParameters += $"_{(await commonDataAccess.FetchLookup<PanArea>(request.PanArea.Value)).Name}";
}
if (request.IsPublicRegister)
{
additionalText = " public register";
}
else
{
additionalText = "_AATF-AE-PCS-organisation details";
}
var fileName =
$"{request.ComplianceYear}{additionalParameters}{facilityText}{additionalText}_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetProducerAmendmentsHistoryCSV request)
{
authorization.EnsureCanAccessInternalArea();
if (string.IsNullOrEmpty(request.PRN))
{
throw new ArgumentException("PRN is required.");
}
var items = await context.StoredProcedures.SpgProducerAmendmentsCSVDataByPRN(request.PRN);
CsvWriter <ProducerAmendmentsHistoryCSVData> csvWriter = csvWriterFactory.Create <ProducerAmendmentsHistoryCSVData>();
csvWriter.DefineColumn(@"Compliance Year", i => i.ComplianceYear);
csvWriter.DefineColumn(@"Date & time (GMT) registered", i => (i.DateRegistered.ToString("dd/MM/yyyy HH:mm:ss").Equals(i.DateAmended.ToString("dd/MM/yyyy HH:mm:ss")) ? i.DateRegistered.ToString("dd/MM/yyyy HH:mm:ss") : string.Empty));
csvWriter.DefineColumn(@"Date & time (GMT) updated", i => (i.DateRegistered.ToString("dd/MM/yyyy HH:mm:ss").Equals(i.DateAmended.ToString("dd/MM/yyyy HH:mm:ss")) ? string.Empty : i.DateAmended.ToString("dd/MM/yyyy HH:mm:ss")));
csvWriter.DefineColumn(@"PCS name", i => i.PCSName);
csvWriter.DefineColumn(@"PCS approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn(@"Producer name", i => i.ProducerName);
csvWriter.DefineColumn(@"Producer type", i => i.ProducerType);
csvWriter.DefineColumn(@"Company registration number", i => i.CompanyNumber);
csvWriter.DefineColumn(@"Partnership names", i => i.Partners);
csvWriter.DefineColumn(@"Trading name", i => i.TradingName);
csvWriter.DefineColumn(@"PRN", i => i.PRN);
csvWriter.DefineColumn(@"Charge band", i => i.ChargeBandType);
csvWriter.DefineColumn(@"VAT registered", (i => i.VATRegistered ? "Yes" : "No"));
csvWriter.DefineColumn(@"Annual turnover", i => i.AnnualTurnover.HasValue ? i.AnnualTurnover.Value.ToString(CultureInfo.InvariantCulture) : string.Empty);
csvWriter.DefineColumn(@"Annual turnover band", i => i.AnnualTurnoverBandType);
csvWriter.DefineColumn(@"EEE placed on market", i => i.EEEPlacedOnMarketBandType);
csvWriter.DefineColumn(@"Obligation Type", i => i.ObligationType);
csvWriter.DefineColumn(@"SIC codes", i => i.SICCodes);
csvWriter.DefineColumn(@"Selling technique", i => i.SellingTechniqueType);
csvWriter.DefineColumn(@"Date ceased to exist", i => i.CeaseToExist.HasValue ? i.CeaseToExist.Value.ToString("dd/MM/yyyy") : string.Empty);
//correspondences of notices details
csvWriter.DefineColumn(@"Correspondent for notices title", i => i.CNTitle);
csvWriter.DefineColumn(@"Correspondent for notices forename", i => i.CNForename);
csvWriter.DefineColumn(@"Correspondent for notices surname", i => i.CNSurname);
csvWriter.DefineColumn(@"Correspondent for notices telephone", i => i.CNTelephone, true);
csvWriter.DefineColumn(@"Correspondent for notices mobile", i => i.CNMobile, true);
csvWriter.DefineColumn(@"Correspondent for notices fax", i => i.CNFax, true);
csvWriter.DefineColumn(@"Correspondent for notices email", i => i.CNEmail);
//address
csvWriter.DefineColumn(@"Correspondent for notices address line1", i => i.CNPrimaryName);
csvWriter.DefineColumn(@"Correspondent for notices address line2", i => i.CNSecondaryName);
csvWriter.DefineColumn(@"Correspondent for notices street", i => i.CNStreet);
csvWriter.DefineColumn(@"Correspondent for notices town", i => i.CNTown);
csvWriter.DefineColumn(@"Correspondent for notices locality", i => i.CNLocality);
csvWriter.DefineColumn(@"Correspondent for notices administrative area", i => i.CNAdministrativeArea);
csvWriter.DefineColumn(@"Correspondent for notices post code", i => i.CNPostcode);
csvWriter.DefineColumn(@"Correspondent for notices country", i => i.CNCountry);
//company or partnership details based on organisation type
csvWriter.DefineColumn(@"Reg. Off. or PPoB title", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactTitle : i.PPOBContactTitle);
csvWriter.DefineColumn(@"Reg. Off. or PPoB forename", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactForename : i.PPOBContactForename);
csvWriter.DefineColumn(@"Reg. Off. or PPoB surname", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactSurname : i.PPOBContactSurname);
csvWriter.DefineColumn(@"Reg. Off. or PPoB telephone", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactTelephone : i.PPOBContactTelephone, true);
csvWriter.DefineColumn(@"Reg. Off. or PPoB mobile", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactMobile : i.PPOBContactMobile, true);
csvWriter.DefineColumn(@"Reg. Off. or PPoB fax", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactFax : i.PPOBContactFax, true);
csvWriter.DefineColumn(@"Reg. Off. or PPoB email", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactEmail : i.PPOBContactEmail);
//address
csvWriter.DefineColumn(@"Reg. Off. or PPoB address line1", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactPrimaryName : i.PPOBContactPrimaryName);
csvWriter.DefineColumn(@"Reg. Off. or PPoB address line2", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactSecondaryName : i.PPOBContactSecondaryName);
csvWriter.DefineColumn(@"Reg. Off. or PPoB street", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactStreet : i.PPOBContactStreet);
csvWriter.DefineColumn(@"Reg. Off. or PPoB town", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactTown : i.PPOBContactTown);
csvWriter.DefineColumn(@"Reg. Off. or PPoB locality", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactLocality : i.PPOBContactLocality);
csvWriter.DefineColumn(@"Reg. Off. or PPoB administrative area", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactAdministrativeArea : i.PPOBContactAdministrativeArea);
csvWriter.DefineColumn(@"Reg. Off. or PPoB post code", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactPostcode : i.PPOBContactPostcode);
csvWriter.DefineColumn(@"Reg. Off. or PPoB country", i => !string.IsNullOrEmpty(i.CompanyName) ? i.CompanyContactCountry : i.PPOBContactCountry);
//overseas producer details
csvWriter.DefineColumn(@"Overseas producer name", i => i.OverseasProducerName);
csvWriter.DefineColumn(@"Overseas producer title", i => i.OverseasContactTitle);
csvWriter.DefineColumn(@"Overseas producer forename", i => i.OverseasContactForename);
csvWriter.DefineColumn(@"Overseas producer surname", i => i.OverseasContactSurname);
csvWriter.DefineColumn(@"Overseas producer telephone", i => i.OverseasContactTelephone, true);
csvWriter.DefineColumn(@"Overseas producer mobile", i => i.OverseasContactMobile, true);
csvWriter.DefineColumn(@"Overseas producer fax", i => i.OverseasContactFax, true);
csvWriter.DefineColumn(@"Overseas producer email", i => i.OverseasContactEmail);
//address
csvWriter.DefineColumn(@"Overseas producer address line1", i => i.OverseasContactPrimaryName);
csvWriter.DefineColumn(@"Overseas producer address line2", i => i.OverseasContactSecondaryName);
csvWriter.DefineColumn(@"Overseas producer street", i => i.OverseasContactStreet);
csvWriter.DefineColumn(@"Overseas producer town", i => i.OverseasContactTown);
csvWriter.DefineColumn(@"Overseas producer locality", i => i.OverseasContactLocality);
csvWriter.DefineColumn(@"Overseas producer administrative area", i => i.OverseasContactAdministrativeArea);
csvWriter.DefineColumn(@"Overseas producer post code", i => i.OverseasContactPostcode);
csvWriter.DefineColumn(@"Overseas producer country", i => i.OverseasContactCountry);
var outOfRangeProducerBrandAmendments = items
.Where(r => r.BrandNames.Length > MaxBrandNamesLength)
.Select(r => r.DateRegistered.ToString("dd/MM/yyyy HH:mm:ss"));
if (outOfRangeProducerBrandAmendments.Any())
{
throw new Exception(
string.Format("The producer '{0}' has brand names exceeding the maximum allowed length for the following registrations: {1}.",
request.PRN, string.Join(", ", outOfRangeProducerBrandAmendments)));
}
csvWriter.DefineColumn("Brand names", i => i.BrandNames);
string fileContent = csvWriter.Write(items);
var fileName = string.Format("{0}_registrationdetailsandchanges_{1:ddMMyyyy_HHmm}.csv",
request.PRN,
DateTime.UtcNow);
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetAatfAeReturnDataCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
var message = $"Compliance year cannot be \"{request.ComplianceYear}\".";
throw new ArgumentException(message);
}
var items = await context.StoredProcedures.GetAatfAeReturnDataCsvData(
request.ComplianceYear, request.Quarter, (int)request.FacilityType,
request.ReturnStatus.HasValue?(int)request.ReturnStatus : (int?)null, request.AuthorityId, request.LocalArea, request.PanArea, request.IncludeReSubmissions);
foreach (var item in items)
{
item.AatfDataUrl = $@" =HYPERLINK(""""{request.AatfDataUrl}{item.AatfId}#data"""", """"View AATF / AE data"""")";
}
var csvWriter = csvWriterFactory.Create <AatfAeReturnData>();
csvWriter.DefineColumn(@"Name of AATF / AE", i => i.Name);
csvWriter.DefineColumn(@"Approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn(@"Organisation name", i => i.OrganisationName);
csvWriter.DefineColumn(@"Submission status", i => i.ReturnStatus);
csvWriter.DefineColumn(@"Date created (GMT)", i => i.CreatedDate);
csvWriter.DefineColumn(@"Date submitted (GMT)", i => i.SubmittedDate);
csvWriter.DefineColumn(@"Submitted by", i => i.SubmittedBy);
csvWriter.DefineColumn(@"Appropriate authority", i => i.CompetentAuthorityAbbr);
csvWriter.DefineColumn(@"First submission / resubmission", i => i.ReSubmission);
csvWriter.DefineColumn(@" ", i => i.AatfDataUrl);
var fileContent = csvWriter.Write(items);
//Trim the space before equals in =Hyperlink
fileContent = fileContent.Replace(" =HYPERLINK", "=HYPERLINK");
var excludeResubmissions = request.IncludeReSubmissions ? "Include resubmissions" : "Exclude resubmissions";
var additionalParameters = string.Empty;
if (request.ReturnStatus.HasValue)
{
additionalParameters = $"_{EnumHelper.GetDisplayName(request.ReturnStatus.Value)}";
}
if (request.AuthorityId.HasValue)
{
additionalParameters += $"_{(await commonDataAccess.FetchCompetentAuthorityById(request.AuthorityId.Value)).Abbreviation}";
}
if (request.PanArea.HasValue)
{
additionalParameters += $"_{(await commonDataAccess.FetchLookup<PanArea>(request.PanArea.Value)).Name}";
}
var fileName =
$"{request.ComplianceYear}_Q{request.Quarter}_{excludeResubmissions}_{request.FacilityType.ToString().ToUpper()}{additionalParameters}_Summary of AATF-AE returns to date_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}