public AddressRatesController(WashingtonStateContext context)
{
_context = context;
// Disable to improve read-only performance and reduce memory consumption.
_context.ChangeTracker.AutoDetectChangesEnabled = false;
_context.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
}
public Ingest(ITestOutputHelper output)
{
this.output = output;
var config = new ConfigurationBuilder()
.AddJsonFile("appsettings.json", optional: false)
.Build();
var contextOptions = new DbContextOptionsBuilder <WashingtonStateContext>()
.UseSqlite(config.GetConnectionString("WashingtonStateContext"))
.Options;
db = new WashingtonStateContext(contextOptions);
configuration = config;
}
public static async Task <bool> TryIngestShortZipCodesAsync(string url, WashingtonStateContext db)
{
try
{
var pathtoFile = await url.DownloadFileAsync(AppContext.BaseDirectory);
var fileName = string.Empty;
// https://stackoverflow.com/questions/47973286/get-the-filename-of-a-file-that-was-created-through-zipfile-extracttodirectory
//open archive
using (var archive = ZipFile.OpenRead(pathtoFile))
{
//since there is only one entry grab the first
var entry = archive.Entries.FirstOrDefault();
//the relative path of the entry in the zip archive
fileName = entry.FullName;
}
var pathToCSV = Path.Combine(AppContext.BaseDirectory, fileName);
if (!File.Exists(pathToCSV))
{
ZipFile.ExtractToDirectory(pathtoFile, AppContext.BaseDirectory);
}
db.ChangeTracker.AutoDetectChangesEnabled = false;
var config = new CsvConfiguration(CultureInfo.InvariantCulture)
{
HasHeaderRecord = false,
Delimiter = ","
};
using (var reader = new StreamReader(pathToCSV))
using (var csv = new CsvReader(reader, config))
{
var rowId = 0;
csv.Context.RegisterClassMap <ShortZipMap>();
using var transaction = await db.Database.BeginTransactionAsync();
await foreach (var row in csv.GetRecordsAsync <ShortZip>())
{
var command = db.Database.GetDbConnection().CreateCommand();
command.CommandText =
$"insert into ZipCodes (ShortZipId, Zip, Plus4LowerBound, Plus4UpperBound, LocationCode, State, Local, TotalRate, EffectiveStartDate, EffectiveEndDate) values ($ShortZipId, $Zip, $Plus4LowerBound, $Plus4UpperBound, $LocationCode, $State, $Local, $TotalRate, $EffectiveStartDate, $EffectiveEndDate);";
var parameterShortZipId = command.CreateParameter();
parameterShortZipId.ParameterName = "$ShortZipId";
command.Parameters.Add(parameterShortZipId);
parameterShortZipId.Value = rowId++;
var parameterZip = command.CreateParameter();
parameterZip.ParameterName = "$Zip";
command.Parameters.Add(parameterZip);
parameterZip.Value = string.IsNullOrWhiteSpace(row?.Zip) ? DBNull.Value : row.Zip;
var parameterPlus4LowerBound = command.CreateParameter();
parameterPlus4LowerBound.ParameterName = "$Plus4LowerBound";
command.Parameters.Add(parameterPlus4LowerBound);
parameterPlus4LowerBound.Value = string.IsNullOrWhiteSpace(row?.Plus4LowerBound) ? DBNull.Value : row.Plus4LowerBound;
var parameterPlus4UpperBound = command.CreateParameter();
parameterPlus4UpperBound.ParameterName = "$Plus4UpperBound";
command.Parameters.Add(parameterPlus4UpperBound);
parameterPlus4UpperBound.Value = string.IsNullOrWhiteSpace(row?.Plus4UpperBound) ? DBNull.Value : row.Plus4UpperBound;
var parameterLocationCode = command.CreateParameter();
parameterLocationCode.ParameterName = "$LocationCode";
command.Parameters.Add(parameterLocationCode);
parameterLocationCode.Value = row.LocationCode;
var parameterState = command.CreateParameter();
parameterState.ParameterName = "$State";
command.Parameters.Add(parameterState);
parameterState.Value = string.IsNullOrWhiteSpace(row?.State) ? DBNull.Value : row.State;
var parameterLocal = command.CreateParameter();
parameterLocal.ParameterName = "$Local";
command.Parameters.Add(parameterLocal);
parameterLocal.Value = string.IsNullOrWhiteSpace(row?.Local) ? DBNull.Value : row.Local;
var parameterTotalRate = command.CreateParameter();
parameterTotalRate.ParameterName = "$TotalRate";
command.Parameters.Add(parameterTotalRate);
parameterTotalRate.Value = string.IsNullOrWhiteSpace(row?.TotalRate) ? DBNull.Value : row.TotalRate;
var parameterEffectiveStartDate = command.CreateParameter();
parameterEffectiveStartDate.ParameterName = "$EffectiveStartDate";
command.Parameters.Add(parameterEffectiveStartDate);
parameterEffectiveStartDate.Value = row.EffectiveStartDate;
var parameterEffectiveEndDate = command.CreateParameter();
parameterEffectiveEndDate.ParameterName = "$EffectiveEndDate";
command.Parameters.Add(parameterEffectiveEndDate);
parameterEffectiveEndDate.Value = row.EffectiveEndDate;
await command.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
return(true);
}
catch (Exception ex)
{
Log.Fatal("[Ingest] Failed to load Short Zip Codes into the database.");
Log.Fatal(ex.Message);
Log.Fatal(ex.StackTrace);
return(false);
}
}
public static async Task <bool> TryIngestAddressesAsync(string url, WashingtonStateContext db)
{
try
{
if (await db.AddressRanges.AnyAsync())
{
return(true);
}
var pathtoFile = await url.DownloadFileAsync(AppContext.BaseDirectory);
var pathToCSV = Path.Combine(AppContext.BaseDirectory, Path.GetFileNameWithoutExtension(pathtoFile) + ".txt");
var fileTypes = new string[] { ".txt", ".csv" };
// If a file with the same name already exists it will break the downloading process, so we need to make sure they are deleted.
foreach (var type in fileTypes)
{
var filePath = Path.Combine(AppContext.BaseDirectory, Path.GetFileNameWithoutExtension(pathtoFile) + type);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
if (!File.Exists(pathToCSV))
{
ZipFile.ExtractToDirectory(pathtoFile, AppContext.BaseDirectory);
}
//db.ChangeTracker.AutoDetectChangesEnabled = false;
using (var reader = new StreamReader(pathToCSV))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
var rowId = 0;
csv.Context.RegisterClassMap <AddressRangeMap>();
using var transaction = await db.Database.BeginTransactionAsync();
await foreach (var row in csv.GetRecordsAsync <AddressRange>())
{
var command = db.Database.GetDbConnection().CreateCommand();
command.CommandText =
$"insert into AddressRanges (AddressRangeId, AddressRangeLowerBound, AddressRangeUpperBound, OddOrEven, Street, State, ZipCode, ZipCodePlus4, Period, LocationCode, RTA, PTBAName, CEZName) values ($AddressRangeId, $AddressRangeLowerBound, $AddressRangeUpperBound, $OddOrEven, $Street, $State, $ZipCode, $ZipCodePlus4, $Period, $LocationCode, $RTA, $PTBAName, $CEZName);";
var parameterId = command.CreateParameter();
parameterId.ParameterName = "$AddressRangeId";
command.Parameters.Add(parameterId);
parameterId.Value = rowId++;
var parameterLowerBound = command.CreateParameter();
parameterLowerBound.ParameterName = "$AddressRangeLowerBound";
command.Parameters.Add(parameterLowerBound);
parameterLowerBound.Value = row?.AddressRangeLowerBound is null ? DBNull.Value : row.AddressRangeLowerBound;
var parameterUpperBound = command.CreateParameter();
parameterUpperBound.ParameterName = "$AddressRangeUpperBound";
command.Parameters.Add(parameterUpperBound);
parameterUpperBound.Value = row?.AddressRangeUpperBound is null ? DBNull.Value : row.AddressRangeUpperBound;
var parameterOddOrEven = command.CreateParameter();
parameterOddOrEven.ParameterName = "$OddOrEven";
command.Parameters.Add(parameterOddOrEven);
parameterOddOrEven.Value = row.OddOrEven;
var parameterStreet = command.CreateParameter();
parameterStreet.ParameterName = "$Street";
command.Parameters.Add(parameterStreet);
parameterStreet.Value = string.IsNullOrWhiteSpace(row?.Street) ? DBNull.Value : row.Street;
var parameterState = command.CreateParameter();
parameterState.ParameterName = "$State";
command.Parameters.Add(parameterState);
parameterState.Value = string.IsNullOrWhiteSpace(row?.State) ? DBNull.Value : row.State;
var parameterZipCode = command.CreateParameter();
parameterZipCode.ParameterName = "$ZipCode";
command.Parameters.Add(parameterZipCode);
parameterZipCode.Value = string.IsNullOrWhiteSpace(row?.ZipCode) ? DBNull.Value : row.ZipCode;
var parameterZipCodePlus4 = command.CreateParameter();
parameterZipCodePlus4.ParameterName = "$ZipCodePlus4";
command.Parameters.Add(parameterZipCodePlus4);
parameterZipCodePlus4.Value = string.IsNullOrWhiteSpace(row?.ZipCodePlus4) ? DBNull.Value : row.ZipCodePlus4;
var parameterPeriod = command.CreateParameter();
parameterPeriod.ParameterName = "$Period";
command.Parameters.Add(parameterPeriod);
parameterPeriod.Value = string.IsNullOrWhiteSpace(row?.Period) ? DBNull.Value : row.Period;
var parameterLocationCode = command.CreateParameter();
parameterLocationCode.ParameterName = "$LocationCode";
command.Parameters.Add(parameterLocationCode);
parameterLocationCode.Value = row.LocationCode;
var parameterRTA = command.CreateParameter();
parameterRTA.ParameterName = "$RTA";
command.Parameters.Add(parameterRTA);
parameterRTA.Value = row.RTA;
var parameterPTBAName = command.CreateParameter();
parameterPTBAName.ParameterName = "$PTBAName";
command.Parameters.Add(parameterPTBAName);
parameterPTBAName.Value = string.IsNullOrWhiteSpace(row?.PTBAName) ? DBNull.Value : row.PTBAName;
var parameterCEZName = command.CreateParameter();
parameterCEZName.ParameterName = "$CEZName";
command.Parameters.Add(parameterCEZName);
parameterCEZName.Value = string.IsNullOrWhiteSpace(row?.CEZName) ? DBNull.Value : row.CEZName;
await command.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
return(true);
}
catch (Exception ex)
{
Log.Fatal("[Ingest] Failed to load Addresses into the database.");
Log.Fatal(ex.Message);
Log.Fatal(ex.StackTrace);
return(false);
}
}
public static async Task <bool> TryIngestTaxRatesAsync(string url, WashingtonStateContext db)
{
try
{
var pathtoFile = await url.DownloadFileAsync(AppContext.BaseDirectory);
var pathToCSV = Path.Combine(AppContext.BaseDirectory, Path.GetFileNameWithoutExtension(pathtoFile) + ".csv");
var fileTypes = new string[] { ".txt", ".csv" };
// If a file with the same name already exists it will break the downloading process, so we need to make sure they are deleted.
foreach (var type in fileTypes)
{
var filePath = Path.Combine(AppContext.BaseDirectory, Path.GetFileNameWithoutExtension(pathtoFile) + type);
if (File.Exists(filePath))
{
File.Delete(filePath);
}
}
if (!File.Exists(pathToCSV))
{
ZipFile.ExtractToDirectory(pathtoFile, AppContext.BaseDirectory);
}
db.ChangeTracker.AutoDetectChangesEnabled = false;
using (var reader = new StreamReader(pathToCSV))
using (var csv = new CsvReader(reader, CultureInfo.InvariantCulture))
{
csv.Context.RegisterClassMap <TaxRateMap>();
using var transaction = await db.Database.BeginTransactionAsync();
await foreach (var row in csv.GetRecordsAsync <TaxRate>())
{
var command = db.Database.GetDbConnection().CreateCommand();
command.CommandText =
$"insert into TaxRates (Name, LocationCode, State, Local, RTA, Rate, EffectiveDate, ExpirationDate) values ($Name, $LocationCode, $State, $Local, $RTA, $Rate, $EffectiveDate, $ExpirationDate);";
var parameterName = command.CreateParameter();
parameterName.ParameterName = "$Name";
command.Parameters.Add(parameterName);
parameterName.Value = string.IsNullOrWhiteSpace(row?.Name) ? DBNull.Value : row.Name;
var parameterLocationCode = command.CreateParameter();
parameterLocationCode.ParameterName = "$LocationCode";
command.Parameters.Add(parameterLocationCode);
parameterLocationCode.Value = row.LocationCode;
var parameterState = command.CreateParameter();
parameterState.ParameterName = "$State";
command.Parameters.Add(parameterState);
parameterState.Value = row.State;
var parameterLocal = command.CreateParameter();
parameterLocal.ParameterName = "$Local";
command.Parameters.Add(parameterLocal);
parameterLocal.Value = row.Local;
var parameterRTA = command.CreateParameter();
parameterRTA.ParameterName = "$RTA";
command.Parameters.Add(parameterRTA);
parameterRTA.Value = row.RTA;
var parameterRate = command.CreateParameter();
parameterRate.ParameterName = "$Rate";
command.Parameters.Add(parameterRate);
parameterRate.Value = row.Rate;
var parameterEffectiveDate = command.CreateParameter();
parameterEffectiveDate.ParameterName = "$EffectiveDate";
command.Parameters.Add(parameterEffectiveDate);
parameterEffectiveDate.Value = row.EffectiveDate;
var parameterExpirationDate = command.CreateParameter();
parameterExpirationDate.ParameterName = "$ExpirationDate";
command.Parameters.Add(parameterExpirationDate);
parameterExpirationDate.Value = row.ExpirationDate;
await command.ExecuteNonQueryAsync();
}
await transaction.CommitAsync();
}
return(true);
}
catch (Exception ex)
{
Log.Fatal("[Ingest] Failed to load Tax Rates into the database.");
Log.Fatal(ex.Message);
Log.Fatal(ex.StackTrace);
return(false);
}
}
