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 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 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 void TruncateCommunityStagingTables()
{
StoredProcedure proc = new StoredProcedure(SQLResource.ClearNHStagingTables, this.ConnectionString);
proc.ExecuteNonQuery();
}
/// <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 void ProcessROI()
{
StoredProcedure proc = new StoredProcedure(SQLResource.ProcessROI, this.ConnectionString);
proc.ExecuteNonQuery();
}
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 PopulateExplorerSummary()
{
StoredProcedure proc = new StoredProcedure(SQLResource.PopulateExplorerSummary, this.ConnectionString);
proc.ExecuteNonQuery();
}
// 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();
}