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 }); }