private void InsertRecordIntoAddressBase(CsvUploadRecord addressBaseRecord)
{
DbConnection.Execute(
"INSERT INTO dbo.address_base (uprn,parent_uprn,udprn,usrn,toid,classification_code,easting,northing,latitude,longitude,rpc,last_update_date,single_line_address,po_box,organisation,sub_building,building_name,building_number,street_name,locality,town_name,post_town,change_code,island,postcode,delivery_point_suffix,gss_code)" +
"VALUES (@uprn,@parent_uprn,@udprn,@usrn,@toid,@classification_code,@easting,@northing,@latitude,@longitude,@rpc,@last_update_date,@single_line_address,@po_box,@organisation,@sub_building,@building_name,@building_number,@street_name,@locality,@town_name,@post_town,@change_code,@island,@postcode,@delivery_point_suffix,@gss_code)",
addressBaseRecord);
}
private static Address MapToExpectedAddressRecord(CsvUploadRecord addressBaseRecord)
{
var usage = addressBaseRecord.classification_code.First() switch
{
'R' => "Residential",
'C' => "commercial",
'P' => "Parent Shell",
'L' => "Land",
'X' => "Dual Use",
_ => "Unclassified"
};
var addressLines = addressBaseRecord.single_line_address.Split(',');
var expectedNationalAddress = new Address
{
blpu_end_date = 0,
blpu_last_update_date = 0,
blpu_start_date = 0,
lpi_start_date = 0,
lpi_end_date = 0,
lpi_last_update_date = 0,
blpu_class = addressBaseRecord.classification_code.Substring(0, 4),
building_number = addressBaseRecord.building_number,
easting = addressBaseRecord.easting,
gazetteer = addressBaseRecord.gss_code == _hackneyGssCode ? "local" : "national",
latitude = addressBaseRecord.latitude,
line1 = addressLines.ElementAt(0),
line2 = addressLines.Length > 1 ? addressLines.ElementAt(1) : null,
line3 = addressLines.Length > 2 ? addressLines.ElementAt(2) : null,
line4 = null,
locality = addressBaseRecord.locality,
longitude = addressBaseRecord.longitude,
lpi_logical_status = "Approved Preferred",
neverexport = false,
northing = addressBaseRecord.northing,
organisation = addressBaseRecord.organisation,
pao_text = addressBaseRecord.building_name,
parent_uprn = (long?)addressBaseRecord.parent_uprn,
planning_use_class = "",
postcode = addressBaseRecord.postcode,
postcode_nospace = addressBaseRecord.postcode.Replace(" ", ""),
property_shell = addressBaseRecord.classification_code.First() == 'P',
sao_text = addressBaseRecord.sub_building,
street_description = addressBaseRecord.street_name,
town = addressBaseRecord.town_name,
uprn = (long)addressBaseRecord.uprn,
usage_description = usage,
usage_primary = usage,
usrn = (int)addressBaseRecord.usrn,
ward = "",
};
return(expectedNationalAddress);
}
