public async Task GetAllAatfSentOnDataCsvHandler_GivenAuthorityParameter_FileNameIsCorrect()
{
SystemTime.Freeze(new DateTime(2019, 8, 27, 11, 30, 1));
var authority = fixture.Create <UKCompetentAuthority>();
var request = new GetAllAatfSentOnDataCsv(ComplianceYear, null, authority.Id, null);
A.CallTo(() => commonDataAccess.FetchCompetentAuthorityById(authority.Id)).Returns(authority);
var data = await handler.HandleAsync(request);
data.FileName.Should().Be($"2019_{authority.Abbreviation}_AATF WEEE sent on for treatment_27082019_1130.csv");
}
public async Task HandleAsync_GivenMandatoryParametersAndAuthority_FileNameShouldBeCorrect()
{
UKCompetentAuthority ca = fixture.Create <UKCompetentAuthority>();
GetAatfAeDetailsCsv request = new GetAatfAeDetailsCsv(fixture.Create <int>(), fixture.Create <ReportFacilityType>(), ca.Id, null, null, false);
A.CallTo(() => commonDataAccess.FetchCompetentAuthorityById(request.AuthorityId.Value)).Returns(ca);
DateTime date = new DateTime(2019, 05, 18, 11, 12, 0);
SystemTime.Freeze(date);
CSVFileData data = await handler.HandleAsync(request);
data.FileName.Should().Be($"{request.ComplianceYear}_{ca.Abbreviation}_{request.FacilityType.ToString().ToUpper()}_AATF-AE-PCS-organisation details_{date:ddMMyyyy_HHmm}.csv");
SystemTime.Unfreeze();
}
public async Task HandleAsync_GivenMandatoryParametersAndAuthority_FileNameShouldBeCorrect(string expectedText, bool includeResubmissions)
{
var request = new GetAatfAeReturnDataCsv(fixture.Create <int>(), fixture.Create <int>(), fixture.Create <FacilityType>(), null, fixture.Create <Guid>(), null, null, fixture.Create <string>(), includeResubmissions);
var ca = fixture.Create <EA.Weee.Domain.UKCompetentAuthority>();
A.CallTo(() => commonDataAccess.FetchCompetentAuthorityById(request.AuthorityId.Value)).Returns(ca);
var date = new DateTime(2019, 05, 18, 11, 12, 0);
SystemTime.Freeze(date);
var data = await handler.HandleAsync(request);
data.FileName.Should().Be($"{request.ComplianceYear}_Q{request.Quarter}_{expectedText}_{request.FacilityType.ToString().ToUpper()}_{ca.Abbreviation}_Summary of AATF-AE returns to date_{date:ddMMyyyy_HHmm}.csv");
SystemTime.Unfreeze();
}
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(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(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
});
}
public async Task <CSVFileData> HandleAsync(GetAllAatfSentOnDataCsv request)
{
authorization.EnsureCanAccessInternalArea();
if (request.ComplianceYear == 0)
{
var message = $"Compliance year cannot be \"{request.ComplianceYear}\".";
throw new ArgumentException(message);
}
var obligatedData = await weeContext.StoredProcedures.GetAllAatfSentOnDataCsv(request.ComplianceYear, request.ObligationType, request.AuthorityId, request.PanArea);
//Remove the Id columns
if (obligatedData.Tables.Count > 0 && obligatedData.Tables[0] != null)
{
if (obligatedData.Tables[0].Columns.Contains("AatfId"))
{
obligatedData.Tables[0].Columns.Remove("AatfId");
}
if (obligatedData.Tables[0].Columns.Contains("ReturnId"))
{
obligatedData.Tables[0].Columns.Remove("ReturnId");
}
if (obligatedData.Tables[0].Columns.Contains("Q"))
{
obligatedData.Tables[0].Columns.Remove("Q");
}
if (obligatedData.Tables[0].Columns.Contains("CategoryId"))
{
obligatedData.Tables[0].Columns.Remove("CategoryId");
}
if (obligatedData.Tables[0].Columns.Contains("TonnageType"))
{
obligatedData.Tables[0].Columns.Remove("TonnageType");
}
}
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;
}
if (!string.IsNullOrEmpty(request.ObligationType))
{
fileName += "_" + request.ObligationType;
}
fileName += $"_AATF WEEE sent on for treatment_{SystemTime.UtcNow:ddMMyyyy_HHmm}.csv";
var fileContent = string.Empty;
if (obligatedData.Tables.Count > 0)
{
fileContent = obligatedData.Tables[0].DataSetSentOnToCsv(obligatedData.Tables[1]);
}
obligatedData.Dispose();
return(new CSVFileData
{
FileContent = fileContent,
FileName = fileName
});
}
