/// <summary>
/// Clear the AuditLog table.
/// Implemented using a proc for raw speed.
/// </summary>
public static void ClearAuditLogTable()
{
string vendorImportProc = "pClearAuditLogTable";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
using (var cmd = new SqlCommand(vendorImportProc, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
}
private void ProjectCodeImportJson(int socrataDataMartRawJsonImportID)
{
Logger.Info($"Starting '{JobName}' ProjectCodeImportJson");
string vendorImportProc = "dbo.pProjectCodeImportJson";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand(vendorImportProc, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SocrataDataMartRawJsonImportID", socrataDataMartRawJsonImportID);
cmd.ExecuteNonQuery();
}
}
Logger.Info($"Ending '{JobName}' ProjectCodeImportJson");
}
private void ClearGrantAllocationExpenditureTables(int bienniumFiscalYear)
{
Logger.Info($"Starting '{JobName}' ClearGrantAllocationExpenditureTables");
string vendorImportProc = "pClearGrantAllocationExpenditureTables";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
using (var cmd = new SqlCommand(vendorImportProc, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@bienniumFiscalYear", bienniumFiscalYear);
cmd.ExecuteNonQuery();
}
}
Logger.Info($"Ending '{JobName}' ClearGrantAllocationExpenditureTables");
}
/// <summary>
/// Clears *ALL* entries in the table.
/// </summary>
public static void ClearSocrataDataMartRawJsonImportsTable()
{
ILog logger = LogManager.GetLogger(typeof(SocrataDataMartUpdateBackgroundJob));
logger.Info($"Starting pClearSocrataDataMartRawJsonImportsTable");
string vendorImportProc = "pClearSocrataDataMartRawJsonImportsTable";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
using (var cmd = new SqlCommand(vendorImportProc, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
}
logger.Info($"Ending pClearSocrataDataMartRawJsonImportsTable");
}
//
/// <summary>
/// Clears only *OLD* entries in the Socrata imports table
/// </summary>
public static void ClearOutdatedSocrataDataMartRawJsonImportsTableEntries()
{
ILog logger = LogManager.GetLogger(typeof(SocrataDataMartUpdateBackgroundJob));
logger.Info($"Starting pClearOutdatedSocrataDataMartRawJsonImports({StaleEntriesDayCutoff} days)");
string vendorImportProc = "pClearOutdatedSocrataDataMartRawJsonImports";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
using (var cmd = new SqlCommand(vendorImportProc, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@daysOldToRemove", StaleEntriesDayCutoff);
cmd.ExecuteNonQuery();
}
}
logger.Info($"Ending pClearOutdatedSocrataDataMartRawJsonImports({StaleEntriesDayCutoff} days)");
}
public static int GetBienniumFiscalYearForDate(DateTime dateToEvaluate)
{
Logger.Info($"Starting GetBienniumFiscalYearForDate");
string fGetFiscalYearBienniumForDate = "dbo.fGetFiscalYearBienniumForDate";
int bienniumFiscalYear;
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
SqlCommand cmd = new SqlCommand();
cmd.CommandText = $"Select {fGetFiscalYearBienniumForDate}('{dateToEvaluate}')";
cmd.Connection = sqlConnection;
object executeScalarResult = cmd.ExecuteScalar();
bienniumFiscalYear = System.Convert.ToInt32(executeScalarResult);
}
Logger.Info($"Ending GetBienniumFiscalYearForDate");
return(bienniumFiscalYear);
}
public void MarkJsonImportStatus(int socrataDataMartRawJsonImportID, JsonImportStatusType jsonImportStatusType)
{
// Because these objects are so huge, we try to avoid bringing them into memory directly, hence
// the proc to keep it at arm's length.
Logger.Info($"Starting '{JobName}' MarkJsonImportStatus");
string markJsonImportStatus = "dbo.pMarkJsonImportStatus";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
Logger.Info($"'{JobName}' MarkJsonImportStatus - Marking socrataDataMartRawJsonImportID {socrataDataMartRawJsonImportID} as JsonImportStatusType {jsonImportStatusType.JsonImportStatusTypeName}");
using (SqlCommand cmd = new SqlCommand(markJsonImportStatus, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SocrataDataMartRawJsonImportID", socrataDataMartRawJsonImportID);
cmd.Parameters.AddWithValue("@JsonImportStatusTypeID", jsonImportStatusType.JsonImportStatusTypeID);
cmd.ExecuteNonQuery();
}
}
Logger.Info($"Ending '{JobName}' MarkJsonImportStatus");
}
public SuccessfulJsonImportInfo LatestSuccessfulJsonImportInfoForBienniumAndImportTableType(int socrataDataMartRawJsonImportTableTypeID, int?optionalBienniumFiscalYear)
{
// Because these objects are so huge, we try to avoid bringing them into memory directly, hence
// the proc to keep it at arm's length.
Logger.Info($"Starting '{JobName}' LatestSuccessfulJsonImportInfoForBienniumAndImportTableType");
string vendorImportProc = "dbo.pLatestSuccessfulJsonImportInfoForBienniumAndImportTableType";
using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection())
{
using (SqlCommand cmd = new SqlCommand(vendorImportProc, sqlConnection))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@SocrataDataMartRawJsonImportTableTypeID", socrataDataMartRawJsonImportTableTypeID);
cmd.Parameters.AddWithValue("@OptionalBienniumFiscalYear", optionalBienniumFiscalYear);
using (SqlDataReader dataReader = cmd.ExecuteReader())
{
SuccessfulJsonImportInfo importInfo = new SuccessfulJsonImportInfo();
bool queryReturnedData = dataReader.Read();
if (!queryReturnedData)
{
return(null);
}
importInfo.SocrataDataMartRawJsonImportTableTypeID = (int)dataReader["SocrataDataMartRawJsonImportTableTypeID"];
if (dataReader["BienniumFiscalYear"] != System.DBNull.Value)
{
importInfo.BienniumFiscalYear = (int?)dataReader["BienniumFiscalYear"];
}
importInfo.JsonImportDate = (DateTime)dataReader["JsonImportDate"];
importInfo.FinanceApiLastLoadDate = (DateTime)dataReader["FinanceApiLastLoadDate"];
Logger.Info($"Ending '{JobName}' pLatestSuccessfulJsonImportInfoForBienniumAndImportTableType");
return(importInfo);
}
}
}
}