public static IEnumerable <ExtractFile> CreateParcelFiles(ExtractContext context)
{
var extractItems = context
.ParcelExtract
.AsNoTracking();
var parcelProjectionState = context
.ProjectionStates
.AsNoTracking()
.Single(m => m.Name == typeof(ParcelExtractProjections).FullName);
var extractMetadata = new Dictionary <string, string>
{
{ ExtractMetadataKeys.LatestEventId, parcelProjectionState.Position.ToString() }
};
yield return(ExtractBuilder.CreateDbfFile <ParcelDbaseRecord>(
ExtractController.ZipName,
new ParcelDbaseSchema(),
extractItems.OrderBy(m => m.CaPaKey).Select(org => org.DbaseRecord),
extractItems.Count));
yield return(ExtractBuilder.CreateMetadataDbfFile(
ExtractController.ZipName,
extractMetadata));
}
public static ExtractFile CreateStreetNameFile(ExtractContext context, SyndicationContext syndicationContext)
{
var extractItems = context
.StreetNameExtract
.AsNoTracking()
.Where(x => x.Complete)
.OrderBy(x => x.StreetNamePersistentLocalId);
var cachedMunicipalities = syndicationContext.MunicipalityLatestItems.AsNoTracking().ToList();
byte[] TransformRecord(StreetNameExtractItem r)
{
var item = new StreetNameDbaseRecord();
item.FromBytes(r.DbaseRecord, DbfFileWriter <StreetNameDbaseRecord> .Encoding);
var municipality = cachedMunicipalities.First(x => x.NisCode == item.gemeenteid.Value);
switch (municipality.PrimaryLanguage)
{
case null:
default:
item.straatnm.Value = r.NameUnknown;
item.homoniemtv.Value = r.HomonymUnknown ?? string.Empty;
break;
case Taal.NL:
item.straatnm.Value = r.NameDutch;
item.homoniemtv.Value = r.HomonymDutch ?? string.Empty;
break;
case Taal.FR:
item.straatnm.Value = r.NameFrench;
item.homoniemtv.Value = r.HomonymFrench ?? string.Empty;
break;
case Taal.DE:
item.straatnm.Value = r.NameGerman;
item.homoniemtv.Value = r.HomonymGerman ?? string.Empty;
break;
case Taal.EN:
item.straatnm.Value = r.NameEnglish;
item.homoniemtv.Value = r.HomonymEnglish ?? string.Empty;
break;
}
return(item.ToBytes(DbfFileWriter <StreetNameDbaseRecord> .Encoding));
}
return(ExtractBuilder.CreateDbfFile <StreetNameExtractItem, StreetNameDbaseRecord>(
ExtractController.ZipName,
new StreetNameDbaseSchema(),
extractItems,
extractItems.Count,
TransformRecord));
}
public static ExtractFile CreatePostalFile(ExtractContext context)
{
var extractItems = context
.PostalInformationExtract
.AsNoTracking();
return(ExtractBuilder.CreateDbfFile <PostalDbaseRecord>(
ExtractController.ZipName,
new PostalDbaseSchema(),
extractItems.OrderBy(x => x.PostalCode).Select(org => org.DbaseRecord),
extractItems.Count));
}
public static ExtractFile CreateParcelFile(ExtractContext context)
{
var extractItems = context
.ParcelExtract
.AsNoTracking();
return(ExtractBuilder.CreateDbfFile <ParcelDbaseRecord>(
ExtractController.ZipName,
new ParcelDbaseSchema(),
extractItems.OrderBy(m => m.CaPaKey).Select(org => org.DbaseRecord),
extractItems.Count));
}
public static ExtractFile CreateMunicipalityFile(ExtractContext context)
{
var extractItems = context
.MunicipalityExtract
.OrderBy(x => x.NisCode)
.AsNoTracking();
return(ExtractBuilder.CreateDbfFile <MunicipalityDbaseRecord>(
ExtractController.ZipName,
new MunicipalityDbaseSchema(),
extractItems.OrderBy(x => x.NisCode).Select(org => org.DbaseRecord),
extractItems.Count));
}
public static ExtractFile CreateAddressSubaddressIdFile(ExtractContext context)
{
var extractItems = context
.AddressCrabSubaddressIdExtract
.AsNoTracking()
.Where(m => m.CrabSubaddressId.HasValue && m.PersistentLocalId.HasValue)
.OrderBy(m => m.PersistentLocalId);
return(ExtractBuilder.CreateDbfFile <AddressCrabSubaddressIdDbaseRecord>(
ExtractFileNames.CrabSubadresId,
new AddressCrabSubaddressIdDbaseSchema(),
extractItems.Select(x => x.DbaseRecord),
extractItems.Count));
}
public ExtractFile CreateLinkedParcelAddressFiles()
{
var extractItems =
from extractItem in _syndicationContext.AddressParcelLinkExtract
join parcel in _syndicationContext.ParcelAddressMatchLatestItems
on extractItem.ParcelId equals parcel.ParcelId
where extractItem.AddressComplete && !parcel.IsRemoved
select extractItem.DbaseRecord;
return(ExtractBuilder.CreateDbfFile <AddressLinkDbaseRecord>(
ExtractFileNames.ParcelLinks,
new AddressDbaseSchema(),
extractItems,
extractItems.Count));
}
public ExtractFile CreateLinkedBuildingUnitAddressFiles()
{
var extractItems =
from extractItem in _syndicationContext.AddressBuildingUnitLinkExtract
join buildingUnit in _syndicationContext.BuildingUnitAddressMatchLatestItems
on extractItem.BuildingUnitId equals buildingUnit.BuildingUnitId
where extractItem.AddressComplete && !buildingUnit.IsRemoved && buildingUnit.IsComplete && buildingUnit.IsBuildingComplete
select extractItem.DbaseRecord;
return(ExtractBuilder.CreateDbfFile <AddressLinkDbaseRecord>(
ExtractFileNames.BuildingUnitLinks,
new AddressDbaseSchema(),
extractItems,
extractItems.Count));
}
public async Task <ExtractFile> CreateLinkedParcelAddressFiles(CancellationToken cancellationToken)
{
string BuildCommandText(string select)
{
return($"SELECT {select} FROM [{Schema.Syndication}].[{AddressParcelLinkExtractItemConfiguration.TableName}] [apl] " +
$"INNER JOIN [{Schema.Syndication}].[{ParcelAddressLatestItemConfiguration.TableName}] [pal] on [pal].ParcelId = [apl].ParcelId and [pal].AddressId = [apl].AddressId " +
"WHERE [apl].AddressComplete = 1 AND [pal].IsRemoved = 0");
}
IEnumerable <byte[]> GetDbaseRecordBytes()
{
using (var dbaseRecordConnection = new SqlConnection(_connectionString))
using (var dbaseRecordsCommand = new SqlCommand(BuildCommandText("DbaseRecord"), dbaseRecordConnection))
{
dbaseRecordConnection.Open();
var reader = dbaseRecordsCommand.ExecuteReader(CommandBehavior.SequentialAccess);
if (reader.HasRows)
{
while (reader.Read())
{
yield return(reader.GetSqlBytes(0).Value);
}
}
}
}
int count;
using (var countConnection = new SqlConnection(_connectionString))
{
await countConnection.OpenAsync(cancellationToken);
using (var countCommand = new SqlCommand(BuildCommandText("COUNT(1)"), countConnection))
count = (int)await countCommand.ExecuteScalarAsync(cancellationToken);
}
return(ExtractBuilder.CreateDbfFile <AddressLinkDbaseRecord>(
ExtractFileNames.ParcelLinks,
new AddressLinkDbaseSchema(),
GetDbaseRecordBytes(),
() => count));
}
public static IEnumerable <ExtractFile> CreateAddressFiles(ExtractContext context, SyndicationContext syndicationContext)
{
var extractItems = context
.AddressExtract
.AsNoTracking()
.Where(m => m.Complete)
.OrderBy(m => m.AddressPersistentLocalId);
var cachedMunicipalities = syndicationContext.MunicipalityLatestItems.AsNoTracking().ToList();
var cachedStreetNames = syndicationContext.StreetNameLatestItems.AsNoTracking().ToList();
byte[] TransformRecord(AddressExtractItem r)
{
var item = new AddressDbaseRecord();
item.FromBytes(r.DbaseRecord, DbfFileWriter <AddressDbaseRecord> .Encoding);
// update streetname, municipality
var streetName = cachedStreetNames.First(x => x.StreetNameId == r.StreetNameId);
var municipality = cachedMunicipalities.First(x => x.NisCode == streetName.NisCode);
item.straatnmid.Value = streetName.PersistentLocalId;
switch (municipality.PrimaryLanguage)
{
case null:
case Taal.NL:
default:
item.gemeentenm.Value = municipality.NameDutch;
item.straatnm.Value = streetName.NameDutch;
break;
case Taal.FR:
item.gemeentenm.Value = municipality.NameFrench;
item.straatnm.Value = streetName.NameFrench;
break;
case Taal.DE:
item.gemeentenm.Value = municipality.NameGerman;
item.straatnm.Value = streetName.NameGerman;
break;
case Taal.EN:
item.gemeentenm.Value = municipality.NameEnglish;
item.straatnm.Value = streetName.NameEnglish;
break;
}
return(item.ToBytes(DbfFileWriter <AddressDbaseRecord> .Encoding));
}
yield return(ExtractBuilder.CreateDbfFile <AddressExtractItem, AddressDbaseRecord>(
ExtractFileNames.Address,
new AddressDbaseSchema(),
extractItems,
extractItems.Count,
TransformRecord));
var boundingBox = new BoundingBox3D(
extractItems.Where(x => x.MinimumX > 0).Min(record => record.MinimumX),
extractItems.Where(x => x.MinimumY > 0).Min(record => record.MinimumY),
extractItems.Where(x => x.MaximumX > 0).Max(record => record.MaximumX),
extractItems.Where(x => x.MaximumY > 0).Max(record => record.MaximumY),
0,
0,
double.NegativeInfinity,
double.PositiveInfinity);
yield return(ExtractBuilder.CreateShapeFile <PointShapeContent>(
ExtractFileNames.Address,
ShapeType.Point,
extractItems.Select(x => x.ShapeRecordContent),
ShapeContent.Read,
extractItems.Select(x => x.ShapeRecordContentLength),
boundingBox));
yield return(ExtractBuilder.CreateShapeIndexFile(
ExtractFileNames.Address,
ShapeType.Point,
extractItems.Select(x => x.ShapeRecordContentLength),
extractItems.Count,
boundingBox));
yield return(ExtractBuilder.CreateProjectedCoordinateSystemFile(
ExtractFileNames.Address,
ProjectedCoordinateSystem.Belge_Lambert_1972));
}
// Only use in staging
public static IEnumerable <ExtractFile> CreateStreetNameFilesV2(ExtractContext context,
SyndicationContext syndicationContext)
{
var extractItems = context
.StreetNameExtractV2
.AsNoTracking()
.Where(x => x.Complete)
.OrderBy(x => x.StreetNamePersistentLocalId);
var streetNameProjectionState = context
.ProjectionStates
.AsNoTracking()
.Single(m => m.Name == typeof(StreetNameExtractProjectionsV2).FullName);
var extractMetadata = new Dictionary <string, string>
{
{ ExtractMetadataKeys.LatestEventId, streetNameProjectionState.Position.ToString() }
};
var cachedMunicipalities = syndicationContext.MunicipalityLatestItems.AsNoTracking().ToList();
byte[] TransformRecord(StreetNameExtractItemV2 r)
{
var item = new StreetNameDbaseRecord();
item.FromBytes(r.DbaseRecord, DbfFileWriter <StreetNameDbaseRecord> .Encoding);
var municipality = cachedMunicipalities.First(x => x.NisCode == item.gemeenteid.Value);
switch (municipality.PrimaryLanguage)
{
case Taal.NL:
item.straatnm.Value = r.NameDutch;
item.homoniemtv.Value = r.HomonymDutch ?? string.Empty;
break;
case Taal.FR:
item.straatnm.Value = r.NameFrench;
item.homoniemtv.Value = r.HomonymFrench ?? string.Empty;
break;
case Taal.DE:
item.straatnm.Value = r.NameGerman;
item.homoniemtv.Value = r.HomonymGerman ?? string.Empty;
break;
case Taal.EN:
item.straatnm.Value = r.NameEnglish;
item.homoniemtv.Value = r.HomonymEnglish ?? string.Empty;
break;
}
return(item.ToBytes(DbfFileWriter <StreetNameDbaseRecord> .Encoding));
}
yield return(ExtractBuilder.CreateDbfFile <StreetNameExtractItemV2, StreetNameDbaseRecord>(
ExtractController.ZipName,
new StreetNameDbaseSchema(),
extractItems,
extractItems.Count,
TransformRecord));
yield return(ExtractBuilder.CreateMetadataDbfFile(
ExtractController.ZipName,
extractMetadata));
}
