public async Task <CSVFileData> HandleAsync(GetMemberDetailsCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
string message = string.Format("Compliance year cannot be \"{0}\".", request.ComplianceYear);
throw new ArgumentException(message);
}
var result = await context.StoredProcedures.SpgCSVDataBySchemeComplianceYearAndAuthorisedAuthority(
request.ComplianceYear, request.IncludeRemovedProducer, request.IncludeBrandNames, request.SchemeId, request.CompetentAuthorityId);
csvWriter.DefineColumn(@"PCS name", i => i.SchemeName);
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(@"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(@"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);
if (request.IncludeRemovedProducer)
{
csvWriter.DefineColumn(@"Removed from scheme", i => i.RemovedFromScheme);
}
if (request.IncludeBrandNames)
{
var outOfRangeProducerBrandNames = result
.Where(r => r.BrandNames.Length > MaxBrandNamesLength)
.Select(r => r.ProducerName);
if (outOfRangeProducerBrandNames.Any())
{
throw new Exception(
string.Format("The following producers have brand names exceeding the maximum allowed length: {0}.", string.Join(", ", outOfRangeProducerBrandNames)));
}
csvWriter.DefineColumn("Brand names", i => i.BrandNames);
}
string fileContent = csvWriter.Write(result);
var fileName = string.Format("{0} - producerdetails_{1:ddMMyyyy_HHmm}.csv",
request.ComplianceYear,
DateTime.UtcNow);
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetSubmissionChangesCsv message)
{
authorization.EnsureCanAccessInternalArea();
var memberUpload = await dataAccess.GetMemberUpload(message.MemberUploadId);
if (memberUpload == null)
{
throw new InvalidOperationException("A member upload with the specified ID could not be found.");
}
if (!memberUpload.IsSubmitted)
{
throw new InvalidOperationException("The member upload must be submitted.");
}
csvWriter.DefineColumn("Producer name", i => i.ProducerName);
csvWriter.DefineColumn("PRN", i => i.ProducerRegistrationNumber);
csvWriter.DefineColumn("Compliance year", i => i.ComplianceYear);
csvWriter.DefineColumn("Date / Time (GMT) of submission", i => i.SubmittedDate);
csvWriter.DefineColumn("Changed type", i => i.ChangeType);
csvWriter.DefineColumn("Producer type", i => string.IsNullOrEmpty(i.CompanyName) ? "Partnership" : "Registered company");
csvWriter.DefineColumn(@"Company registration number", i => i.CompanyNumber);
csvWriter.DefineColumn(@"Partnership names", i => i.Partners);
csvWriter.DefineColumn(@"Trading name", i => i.TradingName);
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);
csvWriter.DefineColumn("Brand names", i => i.BrandNames);
var items = await dataAccess.GetSubmissionChanges(message.MemberUploadId);
var outOfRangeProducerBrandNames = items
.Where(r => r.BrandNames.Length > MaxBrandNamesLength)
.Select(r => r.ProducerName);
if (outOfRangeProducerBrandNames.Any())
{
throw new Exception(
string.Format("The following producers have brand names exceeding the maximum allowed length: {0}.", string.Join(", ", outOfRangeProducerBrandNames)));
}
var fileContent = csvWriter.Write(items);
var fileName = string.Format("{0}_{1}_memberchanges_{2:ddMMyyyy_HHmm}.csv",
memberUpload.ComplianceYear, memberUpload.Scheme.ApprovalNumber,
memberUpload.SubmittedDate);
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
public async Task <CSVFileData> HandleAsync(GetDataReturnSubmissionEeeChangesCsv message)
{
authorization.EnsureCanAccessInternalArea();
var changes = await dataAccess.GetChanges(message.CurrentDataReturnVersionId, message.PreviousDataReturnVersionId);
csvWriter.DefineColumn("Producer name", i => i.ProducerName);
csvWriter.DefineColumn("PRN", i => i.ProducerRegistrationNumber);
csvWriter.DefineColumn("Compliance year", i => i.ComplianceYear);
csvWriter.DefineColumn("Date / Time (GMT) of submission", i => i.SubmissionDate);
csvWriter.DefineColumn("Quarter", i => i.Quarter);
csvWriter.DefineColumn("Change type", i => i.ChangeType);
csvWriter.DefineColumn("Cat1 B2C (t)", i => i.Cat1B2C);
csvWriter.DefineColumn("Cat2 B2C (t)", i => i.Cat2B2C);
csvWriter.DefineColumn("Cat3 B2C (t)", i => i.Cat3B2C);
csvWriter.DefineColumn("Cat4 B2C (t)", i => i.Cat4B2C);
csvWriter.DefineColumn("Cat5 B2C (t)", i => i.Cat5B2C);
csvWriter.DefineColumn("Cat6 B2C (t)", i => i.Cat6B2C);
csvWriter.DefineColumn("Cat7 B2C (t)", i => i.Cat7B2C);
csvWriter.DefineColumn("Cat8 B2C (t)", i => i.Cat8B2C);
csvWriter.DefineColumn("Cat9 B2C (t)", i => i.Cat9B2C);
csvWriter.DefineColumn("Cat10 B2C (t)", i => i.Cat10B2C);
csvWriter.DefineColumn("Cat11 B2C (t)", i => i.Cat11B2C);
csvWriter.DefineColumn("Cat12 B2C (t)", i => i.Cat12B2C);
csvWriter.DefineColumn("Cat13 B2C (t)", i => i.Cat13B2C);
csvWriter.DefineColumn("Cat14 B2C (t)", i => i.Cat14B2C);
csvWriter.DefineColumn("Cat1 B2B (t)", i => i.Cat1B2B);
csvWriter.DefineColumn("Cat2 B2B (t)", i => i.Cat2B2B);
csvWriter.DefineColumn("Cat3 B2B (t)", i => i.Cat3B2B);
csvWriter.DefineColumn("Cat4 B2B (t)", i => i.Cat4B2B);
csvWriter.DefineColumn("Cat5 B2B (t)", i => i.Cat5B2B);
csvWriter.DefineColumn("Cat6 B2B (t)", i => i.Cat6B2B);
csvWriter.DefineColumn("Cat7 B2B (t)", i => i.Cat7B2B);
csvWriter.DefineColumn("Cat8 B2B (t)", i => i.Cat8B2B);
csvWriter.DefineColumn("Cat9 B2B (t)", i => i.Cat9B2B);
csvWriter.DefineColumn("Cat10 B2B (t)", i => i.Cat10B2B);
csvWriter.DefineColumn("Cat11 B2B (t)", i => i.Cat11B2B);
csvWriter.DefineColumn("Cat12 B2B (t)", i => i.Cat12B2B);
csvWriter.DefineColumn("Cat13 B2B (t)", i => i.Cat13B2B);
csvWriter.DefineColumn("Cat14 B2B (t)", i => i.Cat14B2B);
var fileContent = csvWriter.Write(changes.CsvData);
var fileName =
string.Format("{0}_Q{1}_{2}_EEEDataChanges_{3:ddMMyyyy_HHmm}.csv",
changes.ComplianceYear, changes.Quarter, changes.SchemeApprovalNumber,
changes.CurrentSubmissionDate);
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
