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 int PullHouseholdMarketDataForZip(string zipCode, int jobID)
{
StoredProcedure proc = new StoredProcedure(SQLResource.PullHouseholdMarketDataForZip, this.ConnectionString);
proc.Parameters.AddWithValue("@ZipCode", zipCode);
proc.Parameters.AddWithValue("@JobID", jobID);
proc.Timeout = (60 * 120); // 120 minutes
int affected = proc.ExecuteNonQuery();
return affected;
}
public void AccountSave(ref Account account)
{
StoredProcedure proc = new StoredProcedure(SQLResource.ProcessTwilioAccount, this.ConnectionString);
proc.Parameters.AddWithValue("@AccountSID", account.Sid);
proc.Parameters.AddWithValue("@FriendlyName", account.FriendlyName);
proc.Parameters.AddWithValue("@DateCreated", account.DateCreated);
proc.Parameters.AddWithValue("@DateUpdate", account.DateUpdated);
proc.ExecuteNonQuery();
}
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 void IncomingPhoneNumberSave(ref IncomingPhoneNumber incomingPhone)
{
StoredProcedure proc = new StoredProcedure(SQLResource.ProcessTwilioPhoneNumber, this.ConnectionString);
proc.Parameters.AddWithValue("@PhoneNumberSID", incomingPhone.Sid);
proc.Parameters.AddWithValue("@AccountSID", incomingPhone.AccountSid);
proc.Parameters.AddWithValue("@FriendlyName", incomingPhone.FriendlyName);
proc.Parameters.AddWithValue("@PhoneNumber", incomingPhone.PhoneNumber);
proc.Parameters.AddWithValue("@DateCreated", incomingPhone.DateCreated);
proc.Parameters.AddWithValue("@DateUpdated", incomingPhone.DateUpdated);
proc.ExecuteNonQuery();
}
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 void CallSave(ref Call phoneCall)
{
StoredProcedure proc = new StoredProcedure(SQLResource.ProcessTwilioCall, this.ConnectionString);
proc.Parameters.AddWithValue("@CallSID", phoneCall.Sid);
proc.Parameters.AddWithValue("@ParentCallSID", phoneCall.ParentCallSid ?? string.Empty);
proc.Parameters.AddWithValue("@PhoneNumberSID", phoneCall.PhoneNumberSid);
proc.Parameters.AddWithValue("@To", phoneCall.To);
proc.Parameters.AddWithValue("@From", phoneCall.From);
proc.Parameters.AddWithValue("@Status", phoneCall.Status);
proc.Parameters.AddWithValue("@StartTime", phoneCall.StartTime);
proc.Parameters.AddWithValue("@EndTime", phoneCall.EndTime);
proc.Parameters.AddWithValue("@Duration", phoneCall.Duration);
proc.Parameters.AddWithValue("@Direction", phoneCall.Direction);
proc.Parameters.AddWithValue("@DateCreated", phoneCall.DateCreated);
proc.Parameters.AddWithValue("@DateUpdated", phoneCall.DateUpdated);
proc.ExecuteNonQuery();
}
/// <summary>
/// Calls the Stored Procedures needed to copy data from staging tables into production
/// </summary>
/// <param name="fileID"></param>
public void LoadDataIntoProduction(int fileID)
{
try
{
using (TransactionScope scope = new TransactionScope())
{
StoredProcedure proc = new StoredProcedure("[ETL].[spEncounterFileLoadIntoProduction]", this.ConnectionString);
proc.Parameters.AddWithValue("@FileID", fileID);
proc.Timeout = (60 * 120); // wait 120 minutes before timing out
proc.ExecuteNonQuery();
scope.Complete();
}
}
catch (SqlException ex)
{
log.LogSqlException(ex);
throw;
}
}
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 void ValidateImportedFile(int fileID, int totalRows, bool hasErrors, string errors)
{
StoredProcedure proc = new StoredProcedure(SQLResource.ValidateImportedFile, this.ConnectionString);
proc.Parameters.AddWithValue("@ImportedFileStatusID", fileID);
proc.Parameters.AddWithValue("@TotalRows", totalRows);
proc.Parameters.AddWithValue("@HasErrors", hasErrors);
proc.Parameters.AddWithValue("@Errors", errors);
proc.ExecuteNonQuery();
}
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 void TruncateCommunityStagingTables()
{
StoredProcedure proc = new StoredProcedure(SQLResource.ClearNHStagingTables, this.ConnectionString);
proc.ExecuteNonQuery();
}
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 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;
}
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 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 void PopulateExplorerSummary()
{
StoredProcedure proc = new StoredProcedure(SQLResource.PopulateExplorerSummary, this.ConnectionString);
proc.ExecuteNonQuery();
}
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 void ProcessROI()
{
StoredProcedure proc = new StoredProcedure(SQLResource.ProcessROI, this.ConnectionString);
proc.ExecuteNonQuery();
}
public DataTable ProcedureCodesSelectAll()
{
StoredProcedure proc = new StoredProcedure(SQLResource.GetProcedureCodes, this.ConnectionString);
return proc.ExecuteReader();
}
/// <summary>
/// Calls the Stored Procedures needed to perform Patient Matching and Income Lookups
/// </summary>
/// <param name="fileID"></param>
public void PerformPostProcessing(int fileID)
{
try
{
using (TransactionScope scope = new TransactionScope())
{
StoredProcedure proc = new StoredProcedure("[ETL].[spEncounterFilePostProcessing]", this.ConnectionString)
{
Timeout = (60 * 30) /* wait 30 minutes before timing out*/
};
proc.Parameters.AddWithValue("@FileID", fileID);
proc.ExecuteNonQuery();
scope.Complete();
}
}
catch (SqlException ex)
{
log.LogSqlException(ex);
throw;
}
}
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;
}
// protected methods...
protected int ExecuteStoredProcedure(string procedureName, Dictionary<string, object> parameters = null)
{
StoredProcedure proc = new StoredProcedure(procedureName, SystemDataProvider.ClientDataProvider.ConnectionString);
if (parameters != null)
{
foreach (KeyValuePair<string, object> entry in parameters)
{
proc.Parameters.AddWithValue(entry.Key, entry.Value);
}
}
proc.Timeout = 90;
return proc.ExecuteNonQuery();
}
