public CsvWriter <CsvResult> CreateWriter(ObligationType obligationType, IEnumerable <string> aatfLocations, IEnumerable <string> aaeLocations)
{
CsvWriter <CsvResult> writer = csvWriterFactory.Create <CsvResult>();
writer.DefineColumn("Scheme name", x => x.SchemeName);
writer.DefineColumn("Scheme approval No", x => x.SchemeApprovalNumber);
writer.DefineColumn("Quarter", x => string.Format("Q{0}", x.QuarterType));
writer.DefineColumn("Category", x => categories[x.Category]);
writer.DefineColumn("DCF (t)", x => x.Dcf);
if (obligationType == ObligationType.B2C)
{
writer.DefineColumn("Distributors (t)", x => x.Distributors);
writer.DefineColumn("Final holders (t)", x => x.FinalHolders);
}
writer.DefineColumn("Total AATF/AE (t)", x => x.TotalDelivered);
foreach (string aatfLocation in aatfLocations)
{
var columnTitle = string.Format("{0} (t)", aatfLocation);
writer.DefineColumn(columnTitle, x => x.AatfTonnage[aatfLocation]);
}
foreach (string aaeLocation in aaeLocations)
{
var columnTitle = string.Format("{0} (t)", aaeLocation);
writer.DefineColumn(columnTitle, x => x.AeTonnage[aaeLocation]);
}
return(writer);
}
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)));
}
private async Task <string> GetProducersEeeHistoryCsvContent(string prn)
{
var items = await context.StoredProcedures.SpgProducerEeeHistoryCsvData(prn);
CsvWriter <EeeHistoryCsvResult> csvWriter =
csvWriterFactory.Create <EeeHistoryCsvResult>();
IEnumerable <EeeHistoryCsvResult> csvResults = CreateResults(items);
csvWriter.DefineColumn(@"PRN", i => i.PRN);
csvWriter.DefineColumn(@"PCS name", i => i.SchemeName);
csvWriter.DefineColumn(@"PCS approval number", i => i.ApprovalNumber);
csvWriter.DefineColumn(@"Compliance year", i => i.ComplianceYear);
csvWriter.DefineColumn(@"Date and time (GMT) data submitted", i => i.SubmittedDate);
csvWriter.DefineColumn(@"Quarter", i => i.Quarter);
csvWriter.DefineColumn(@"Latest data", i => i.LatestData);
foreach (int category in Enumerable.Range(1, 14))
{
string title = string.Format("Cat{0} B2C", category);
string columnName = string.Format("Cat{0}B2C", category);
csvWriter.DefineColumn(title, i => i.GetType().GetProperty(columnName).GetValue(i));
}
foreach (int category in Enumerable.Range(1, 14))
{
string title = string.Format("Cat{0} B2B", category);
string columnName = string.Format("Cat{0}B2B", category);
csvWriter.DefineColumn(title, i => i.GetType().GetProperty(columnName).GetValue(i));
}
string fileContent = csvWriter.Write(csvResults);
return(fileContent);
}
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 void CsvWriter_WithExcelSanitizer_SanitizesValues()
{
// Arrange
IExcelSanitizer sanitizer = A.Fake <IExcelSanitizer>();
A.CallTo(() => sanitizer.IsThreat("Bad String")).Returns(true);
A.CallTo(() => sanitizer.Sanitize("Bad String")).Returns("Sanitized Bad String");
CsvWriter <string> writer = new CsvWriter <string>(sanitizer);
writer.DefineColumn("Column 1", x => x);
List <string> data = new List <string>()
{
"Good String", "Bad String"
};
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1" + Environment.NewLine +
"Good String" + Environment.NewLine +
"Sanitized Bad String" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
public void CsvWriter_WithFuncColumn_UsesFuncToRetrieveValues()
{
// Arrange
CsvWriter <int> writer = new CsvWriter <int>(A.Dummy <IExcelSanitizer>());
writer.DefineColumn("Column 1", x => x * x);
List <int> data = new List <int>()
{
1, 2, 3, 4
};
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1" + Environment.NewLine +
"1" + Environment.NewLine +
"4" + Environment.NewLine +
"9" + Environment.NewLine +
"16" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
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 <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 <ActionResult> DownloadErrorsAndWarnings(Guid pcsId, Guid memberUploadId)
{
using (var client = apiClient())
{
IEnumerable <ErrorData> errors =
(await client.SendAsync(User.GetAccessToken(), new GetMemberUploadData(pcsId, memberUploadId)))
.OrderByDescending(e => e.ErrorLevel);
var schemePublicInfo = await cache.FetchSchemePublicInfo(pcsId);
CsvWriter <ErrorData> csvWriter = csvWriterFactory.Create <ErrorData>();
csvWriter.DefineColumn("Type", e => (int)e.ErrorLevel >= 5 ? "Error" : "Warning");
csvWriter.DefineColumn("Description", e => e.Description);
string csv = csvWriter.Write(errors);
var csvFilename = string.Format("{0}_memberregistration_errors_warnings_{1}.csv", schemePublicInfo.ApprovalNo, DateTime.Now.ToString("ddMMyyyy_HHmm"));
byte[] fileContent = new UTF8Encoding().GetBytes(csv);
return(File(fileContent, "text/csv", CsvFilenameFormat.FormatFileName(csvFilename)));
}
}
public void CsvWriter_WithThreeDefinedColumns_WritesValuesInOrder()
{
// Arrange
CsvWriter <object> writer = new CsvWriter <object>(A.Dummy <IExcelSanitizer>());
writer.DefineColumn("Column 1", x => "Value 1");
writer.DefineColumn("Column 2", x => "Value 2");
writer.DefineColumn("Column 3", x => "Value 3");
object[] data = new object[2];
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1,Column 2,Column 3" + Environment.NewLine +
"Value 1,Value 2,Value 3" + Environment.NewLine +
"Value 1,Value 2,Value 3" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
public void CsvWriter_WithThreeDefinedColumns_WritesValuesInOrder()
{
// Arrange
CsvWriter<object> writer = new CsvWriter<object>(A.Dummy<IExcelSanitizer>());
writer.DefineColumn("Column 1", x => "Value 1");
writer.DefineColumn("Column 2", x => "Value 2");
writer.DefineColumn("Column 3", x => "Value 3");
object[] data = new object[2];
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1,Column 2,Column 3" + Environment.NewLine +
"Value 1,Value 2,Value 3" + Environment.NewLine +
"Value 1,Value 2,Value 3" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
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 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 void CsvWriter_WithFormatAsText_EscapesValuesAndWritesAsFormula()
{
// Arrange
CsvWriter <string> writer = new CsvWriter <string>(A.Dummy <IExcelSanitizer>());
writer.DefineColumn("Column 1", x => x, true);
List <string> data = new List <string>()
{
"01234 555 555", "The man said \"Hello World\" to the dog."
};
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1" + Environment.NewLine +
"=\"01234 555 555\"" + Environment.NewLine +
"=\"The man said \"\"Hello World\"\" to the dog.\"" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
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 void CsvWriter_WithExcelSanitizer_SanitizesValues()
{
// Arrange
IExcelSanitizer sanitizer = A.Fake<IExcelSanitizer>();
A.CallTo(() => sanitizer.IsThreat("Bad String")).Returns(true);
A.CallTo(() => sanitizer.Sanitize("Bad String")).Returns("Sanitized Bad String");
CsvWriter<string> writer = new CsvWriter<string>(sanitizer);
writer.DefineColumn("Column 1", x => x);
List<string> data = new List<string>() { "Good String", "Bad String" };
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1" + Environment.NewLine +
"Good String" + Environment.NewLine +
"Sanitized Bad String" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
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(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 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 void CsvWriter_WithFuncColumn_UsesFuncToRetrieveValues()
{
// Arrange
CsvWriter<int> writer = new CsvWriter<int>(A.Dummy<IExcelSanitizer>());
writer.DefineColumn("Column 1", x => x * x);
List<int> data = new List<int>() { 1, 2, 3, 4 };
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1" + Environment.NewLine +
"1" + Environment.NewLine +
"4" + Environment.NewLine +
"9" + Environment.NewLine +
"16" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
public void CsvWriter_WithFormatAsText_EscapesValuesAndWritesAsFormula()
{
// Arrange
CsvWriter<string> writer = new CsvWriter<string>(A.Dummy<IExcelSanitizer>());
writer.DefineColumn("Column 1", x => x, true);
List<string> data = new List<string>() { "01234 555 555", "The man said \"Hello World\" to the dog." };
// Act
string csv = writer.Write(data);
// Assert
string expectedValue =
"Column 1" + Environment.NewLine +
"=\"01234 555 555\"" + Environment.NewLine +
"=\"The man said \"\"Hello World\"\" to the dog.\"" + Environment.NewLine;
Assert.Equal(expectedValue, csv);
}
