public void StandardizeStagingAddresses(int fileID)
{
List<StagingAddress> standardizeCandidates = new List<StagingAddress>();
// Call SP that will get standardization candidates
StoredProcedure proc = new StoredProcedure("[ETL].[spStagingAddressStandardizationCandidates]", this.ConnectionString);
proc.Parameters.AddWithValue("@FileID", fileID);
DataTable t = proc.ExecuteReader();
foreach (DataRow row in t.Rows)
{
standardizeCandidates.Add(new StagingAddress(row));
}
// Standardize addresses here
foreach (StagingAddress a in standardizeCandidates)
{
try
{
StagingAddress address = a.Clone();
this.StagingAddressStandardize(ref address);
this.StagingAddressUpdateStandardized(ref address);
}
catch (NullReferenceException)
{
// don't do anything for these just yet
}
}
}
public List<string> GetServiceAreaZipCodes()
{
var result = new List<string>();
StoredProcedure proc = new StoredProcedure(SQLResource.GetServiceAreas, this.ConnectionString);
DataTable t = proc.ExecuteReader();
foreach (DataRow row in t.Rows)
{
result.Add(row.Field<string>(0));
}
return result;
}
public List<HealthSystem> HealthSystemList()
{
List<HealthSystem> systems = new List<HealthSystem>();
StoredProcedure proc = new StoredProcedure(SQLResource.GetHealthSystemList, ConnectionString);
DataTable t = proc.ExecuteReader();
if (t.Rows.Count == 0)
{
throw new DataException("Could not Get Health System List");
}
foreach (DataRow row in t.Rows)
{
HealthSystem h = new HealthSystem(row);
systems.Add(h);
}
return systems;
}
public int GetHealthcareSystemId(string ClientPrefix)
{
StoredProcedure proc = new StoredProcedure("GetHealthcareSystemIDByName", this.ConnectionString);
proc.Parameters.AddWithValue("@ClientName", ClientPrefix);
DataTable t = proc.ExecuteReader();
if (t.Rows.Count > 0)
{
return t.Rows[0].Field<int>("HealthSystemID");
}
// default to demo database if we cannot find correct value
return 999;
}
public DataTable ProcedureCodesSelectAll()
{
StoredProcedure proc = new StoredProcedure(SQLResource.GetProcedureCodes, this.ConnectionString);
return proc.ExecuteReader();
}
public HealthSystemSettings HealthSystemSettingsLoadByID(int healthSystemID)
{
HealthSystemSettings result = null;
StoredProcedure proc = new StoredProcedure(SQLResource.HealthSystemSettingsLoadByID, this.ConnectionString);
proc.Parameters.AddWithValue("@HealthSystemID", healthSystemID);
DataTable t = proc.ExecuteReader();
if (t.Rows.Count == 0)
{
throw new DataException(String.Format("Unable to find settings for HealthSystemID {0}", healthSystemID));
}
result = new HealthSystemSettings(t.Rows[0]);
return result;
}
public HealthSystem HealthSystemLoadByID(int healthSystemID)
{
HealthSystem result = null;
StoredProcedure proc = new StoredProcedure(SQLResource.GetHealthSystemInformation, this.ConnectionString);
proc.Parameters.AddWithValue("@HealthSystemID", healthSystemID);
DataTable t = proc.ExecuteReader();
if (t.Rows.Count == 0)
{
throw new DataException(String.Format("Could not Get Health System Information for ID {0}", healthSystemID));
}
result = new HealthSystem(t.Rows[0]);
return result;
}
public List<ImportedFileStatus> ImportedFileStatusLoadByTypeStatusFileType(ProcessType processType, ProcessStatus processStatus, ProcessFileType processFileType)
{
List<ImportedFileStatus> result = new List<ImportedFileStatus>();
StoredProcedure proc = new StoredProcedure(SQLResource.ImportedFileStatusSelectByTypeStatusFileType, ConnectionString);
proc.Parameters.AddWithValue("@ProcessTypeID", processType);
proc.Parameters.AddWithValue("@ProcessStatusID", processStatus);
proc.Parameters.AddWithValue("@ProcessFileTypeID", processFileType);
DataTable table = proc.ExecuteReader();
foreach (DataRow row in table.Rows)
{
ImportedFileStatus file = new ImportedFileStatus(row);
result.Add(file);
}
return result;
}
public ImportedFileStatus ImportedFileStatusLoadByID(int importedFileStatusID)
{
ImportedFileStatus result = null;
log.DebugFormat("Loading ImportedFileStatusID: {0}", importedFileStatusID);
StoredProcedure proc = new StoredProcedure(SQLResource.ImportedFileStatusSelect, ConnectionString);
proc.Parameters.AddWithValue("@ImportedFileStatusID", importedFileStatusID);
DataTable table = proc.ExecuteReader();
result = new ImportedFileStatus(table.Rows[0]);
return result;
}
public void AddressFind(ref Address address)
{
if (address.AddressID > 0)
{
return;
}
byte[] hash = address.ComputedHash();
if (hash != null)
{
StoredProcedure proc = new StoredProcedure(SQLResource.FindAddress, ConnectionString);
SqlParameter param = new SqlParameter()
{
ParameterName = "@AddressHash",
SqlDbType = SqlDbType.VarBinary,
Value = hash
};
proc.Parameters.Add(param);
DataTable t = proc.ExecuteReader();
if (t.Rows.Count > 0)
{
address.AddressID = t.Rows[0].Field<int>("AddressID");
}
}
}
public ClientOrganization ClientOrganizationLoadByID(int clientOrganizationID)
{
StoredProcedure proc = new StoredProcedure("[App].[spClientOrganizationSelect]",this.ConnectionString);
proc.Parameters.AddWithValue("@ClientOrganizationID", clientOrganizationID);
DataTable t = proc.ExecuteReader();
if (t.Rows.Count > 0)
{
ClientOrganization result = new ClientOrganization(t.Rows[0]);
return result;
}
return null;
}
public List<ClientOrganization> ClientOrganizationsList()
{
List<ClientOrganization> results = new List<ClientOrganization>();
StoredProcedure proc = new StoredProcedure("[App].[spClientOrganizationSelectAll]", this.ConnectionString);
DataTable t = proc.ExecuteReader();
foreach (DataRow row in t.Rows)
{
results.Add(new ClientOrganization(row));
}
return results;
}