private bool UpdateTable(OfficerKeyTablesEntity data, string tableName)
{
List <UCodeEntity> agencyCodeData = GetOdysseyCodeList("61");
List <UCodeEntity> locationCodeData = GetOdysseyCodeList("163");
UpdateConfigurationInTable(data, tableName, agencyCodeData, locationCodeData);
return(true);
}
public override void Run()
{
Logger.WriteToLog("Beginning Run Method", LogLevel.Basic);
// TODO: Update File Transformation Logic
if (Directory.GetFiles(Context.Parameters.InputFilePath, "*.csv").Count() > 0)
{
foreach (string fileName in Directory.GetFiles(Context.Parameters.InputFilePath, "*.csv"))
{
try
{
// 1. Determine which file is being retrieved.
string tableName = "";
switch (Path.GetFileName(fileName))
{
case "OFKYCTCL_UPDATE.csv":
tableName = "CookAutoHearingCallConfiguration";
break;
case "OFKYOFCR_UPDATE.csv":
tableName = "CookAutoHearingOfficerConfiguration";
break;
case "OFKYOFKD_UPDATE.csv":
tableName = "CookAutoHearingOfficerKeyConfiguration";
break;
case "OFKYOFKN_UPDATE.csv":
tableName = "xCookAutoHearingOfficerKeyConfigurationDate";
break;
default:
string errorMesage = "The file: " + fileName + " is not recognized by the program.";
Logger.WriteToLog(errorMesage, LogLevel.Basic);
break;
}
// 2. Extract data and update tables with respective data.
OfficerKeyTablesEntity data = ExtractDataFromFile(fileName, tableName);
UpdateTable(data, tableName);
// 3. Move File to Procesed Folder with a timestamp.
string targetFileName = fileName.Replace(Context.Parameters.InputFilePath, Context.Parameters.ProcessedFilePath);
if (File.Exists(targetFileName))
{
File.Delete(targetFileName);
}
File.Move(fileName, targetFileName);
}
catch (Exception e)
{
Context.Errors.Add(new BaseCustomException(e.Message));
}
}
}
// TODO: Handle errors we've collected during the job run.
if (Context.Errors.Count > 0)
{
// Add a message to the job indicating that something went wrong.
AddInformationToJob();
// Collect errors, write them to a file, and attach the file to the job.
LogErrors();
}
ContinueWithProcessing("Job Completed Successfully");
}
// SQL
private bool UpdateConfigurationInTable(OfficerKeyTablesEntity data, string tableName, List <UCodeEntity> agencyCodeData, List <UCodeEntity> locationCodeData)
{
string queryStringDelete = "DELETE FROM [justice].[StateIllinois].[" + tableName + "]" + " " + "WHERE" + " ";
int deleteCounter = 0;
string queryStringAdd = "INSERT INTO [justice].[StateIllinois].[" + tableName + "]" + " ";
int addCounter = 0;
switch (tableName)
{
case "CookAutoHearingCallConfiguration":
queryStringAdd = queryStringAdd + "(HearingLocationID, CallNumber, Time) VALUES ";
// Build DELETE Query string based off of table
foreach (CookAutoHearingCallConfigurationEntity configurationEntity in data.CookAutoHearingCallConfiguration)
{
if (configurationEntity.Action == "DELETE")
{
if (deleteCounter > 0)
{
queryStringDelete = queryStringDelete + "OR" + " ";
}
queryStringDelete = queryStringDelete + "(HearingLocationID = " + GetCodeIDFromCode(configurationEntity.HearingLocationCode, locationCodeData) + " " +
"AND CallNumber = " + configurationEntity.CallNumber + " " +
"AND Time = '1900-01-01 " + configurationEntity.Time + ":00.000')" + " ";
deleteCounter++;
}
else if (configurationEntity.Action == "ADD")
{
if (addCounter > 0)
{
queryStringAdd = queryStringAdd + "," + " ";
}
queryStringAdd = queryStringAdd +
"(" +
GetCodeIDFromCode(configurationEntity.HearingLocationCode, locationCodeData) + "," +
configurationEntity.CallNumber + "," +
"'1900-01-01 " + configurationEntity.Time + ":00.000'" +
")" + " ";
addCounter++;
}
else
{
Logger.WriteToLog("Could not determine record action.", LogLevel.Verbose);
}
}
break;
case "CookAutoHearingOfficerConfiguration":
queryStringAdd = queryStringAdd + "(NodeID, AgencyID, OfficerID, OfficerKey, HearingLocationID, CallNumber, IsMajorOffenseAssignment) VALUES ";
foreach (CookAutoHearingOfficerConfigurationEntity configurationEntity in data.CookAutoHearingOfficerConfiguration)
{
if (configurationEntity.Action == "DELETE")
{
if (deleteCounter > 0)
{
queryStringDelete = queryStringDelete + "OR" + " ";
}
queryStringDelete = queryStringDelete + "(NodeID = " + configurationEntity.NodeID + " " +
"AND AgencyID = " + GetCodeIDFromCode(configurationEntity.AgencyCode, agencyCodeData) + " " +
"AND OfficerID = " + configurationEntity.OfficerID + " " +
"AND OfficerKey = '" + configurationEntity.OfficerKey + "' " +
"AND HearingLocationID = " + GetCodeIDFromCode(configurationEntity.HearingLocationCode, locationCodeData) + " " +
"AND CallNumber = " + configurationEntity.CallNumber + " " +
"AND IsMajorOffenseAssignment = '" + configurationEntity.IsMajorOffenseAssignment + "') ";
deleteCounter++;
}
else if (configurationEntity.Action == "ADD")
{
if (addCounter > 0)
{
queryStringAdd = queryStringAdd + "," + " ";
}
queryStringAdd = queryStringAdd +
"(" +
configurationEntity.NodeID + "," +
GetCodeIDFromCode(configurationEntity.AgencyCode, agencyCodeData) + "," +
configurationEntity.OfficerID + "," +
"'" + configurationEntity.OfficerKey + "'," +
GetCodeIDFromCode(configurationEntity.HearingLocationCode, locationCodeData) + "," +
configurationEntity.CallNumber + "," +
"'" + configurationEntity.IsMajorOffenseAssignment + "'" +
")" + " ";
addCounter++;
}
else
{
Logger.WriteToLog("Could not determine record action.", LogLevel.Verbose);
}
}
break;
case "CookAutoHearingOfficerKeyConfiguration":
queryStringAdd = queryStringAdd + "(NodeID, OfficerKey) VALUES ";
foreach (CookAutoHearingOfficerKeyConfigurationEntity configurationEntity in data.CookAutoHearingOfficerKeyConfiguration)
{
if (configurationEntity.Action == "DELETE")
{
if (deleteCounter > 0)
{
queryStringDelete = queryStringDelete + "OR" + " ";
}
queryStringDelete = queryStringDelete + "(NodeID = " + configurationEntity.NodeID + " " +
"AND OfficerKey = '" + configurationEntity.OfficerKey + "' ";
deleteCounter++;
}
else if (configurationEntity.Action == "ADD")
{
if (addCounter > 0)
{
queryStringAdd = queryStringAdd + "," + " ";
}
queryStringAdd = queryStringAdd +
"(" +
configurationEntity.NodeID + "," +
configurationEntity.OfficerKey +
")" + " ";
addCounter++;
}
else
{
Logger.WriteToLog("Could not determine record action.", LogLevel.Verbose);
}
}
break;
case "xCookAutoHearingOfficerKeyConfigurationDate":
queryStringAdd = queryStringAdd + "(NodeID, OfficerKey, Date) VALUES ";
foreach (xCookAutoHearingOfficerKeyConfigurationDateEntity configurationEntity in data.XCookAutoHearingOfficerKeyConfigurationDate)
{
if (configurationEntity.Action == "DELETE")
{
// Date Cleanup
DateTime cleanDateTime = DateTime.ParseExact(configurationEntity.Date, "yyyy-mm-dd", System.Globalization.CultureInfo.CurrentCulture);
if (deleteCounter > 0)
{
queryStringDelete = queryStringDelete + "OR" + " ";
}
queryStringDelete = queryStringDelete + "(NodeID = " + configurationEntity.NodeID + " " +
"AND OfficerKey = '" + configurationEntity.OfficerKey + "' " +
"AND Date = '" + configurationEntity.Date + "' " + cleanDateTime.ToString() + "' ";
deleteCounter++;
}
else if (configurationEntity.Action == "ADD")
{
if (addCounter > 0)
{
queryStringAdd = queryStringAdd + "," + " ";
}
queryStringAdd = queryStringAdd +
"(" +
configurationEntity.NodeID + "," +
configurationEntity.OfficerKey + "," +
configurationEntity.Date +
")" + " ";
addCounter++;
}
else
{
Logger.WriteToLog("Could not determine record action.", LogLevel.Verbose);
}
}
break;
default:
break;
}
bool validCheckDelete = false;
bool validCheckAdd = false;
// Try to run it if there are values in the data set.
if (deleteCounter > 0)
{
validCheckDelete = UpdateTable(queryStringDelete);
}
if (addCounter > 0)
{
validCheckAdd = UpdateTable(queryStringAdd);
}
return(validCheckDelete && validCheckAdd);
}
private OfficerKeyTablesEntity ExtractDataFromFile(string fileName, string tableName)
{
OfficerKeyTablesEntity inputFileList = new OfficerKeyTablesEntity();
inputFileList.CookAutoHearingCallConfiguration = new List <CookAutoHearingCallConfigurationEntity>();
inputFileList.CookAutoHearingOfficerConfiguration = new List <CookAutoHearingOfficerConfigurationEntity>();
inputFileList.CookAutoHearingOfficerKeyConfiguration = new List <CookAutoHearingOfficerKeyConfigurationEntity>();
inputFileList.XCookAutoHearingOfficerKeyConfigurationDate = new List <xCookAutoHearingOfficerKeyConfigurationDateEntity>();
using (StreamReader reader = new StreamReader(fileName))
{
Logger.WriteToLog("Processing File: " + fileName, LogLevel.Basic);
int counter = 0;
int dataRowPosition = 1;
// File mapping and extraction
while (!reader.EndOfStream)
{
string line = reader.ReadLine();
string[] lineValues = Regex.Split(line, ",(?=(?:[^']*'[^']*')*[^']*$)");
if (counter > 0)
{
Logger.WriteToLog("Validating record: " + counter, LogLevel.Verbose);
bool valid = ValidateRecord(lineValues);
if (valid)
{
if (tableName == "CookAutoHearingCallConfiguration")
{
CookAutoHearingCallConfigurationEntity inputFileRecord = new CookAutoHearingCallConfigurationEntity(
lineValues[0].Trim() // action
, lineValues[1].Trim() // hearingLocationCode
, lineValues[2].Trim() // callNumber
, DateTime.Parse(lineValues[3].Trim(), CultureInfo.InvariantCulture).ToString("HH:mm") // time
);
// Exclude adding header to data list
if (counter >= dataRowPosition)
{
inputFileList.CookAutoHearingCallConfiguration.Add(inputFileRecord);
}
}
else if (tableName == "CookAutoHearingOfficerConfiguration")
{
CookAutoHearingOfficerConfigurationEntity inputFileRecord = new CookAutoHearingOfficerConfigurationEntity(
lineValues[0].Trim() // action
, lineValues[1].Trim() // nodeID
, lineValues[2].Trim() // agencyCode
, lineValues[3].Trim() // officerID
, lineValues[4].Trim() // officerKey
, lineValues[5].Trim() // hearingLocationCode
, lineValues[6].Trim() // callNumber
, lineValues[7].Trim() // isMajorOffenseAssignment
, lineValues[8].Trim() // badgeNumber
);
// Exclude adding header to data list
if (counter >= dataRowPosition)
{
inputFileList.CookAutoHearingOfficerConfiguration.Add(inputFileRecord);
}
}
else if (tableName == "CookAutoHearingOfficerKeyConfiguration")
{
CookAutoHearingOfficerKeyConfigurationEntity inputFileRecord = new CookAutoHearingOfficerKeyConfigurationEntity(
lineValues[0].Trim() // action
, lineValues[1].Trim() // nodeID
, lineValues[2].Trim() // officerKey
);
// Exclude adding header to data list
if (counter >= dataRowPosition)
{
inputFileList.CookAutoHearingOfficerKeyConfiguration.Add(inputFileRecord);
}
}
else if (tableName == "xCookAutoHearingOfficerKeyConfigurationDate")
{
xCookAutoHearingOfficerKeyConfigurationDateEntity inputFileRecord = new xCookAutoHearingOfficerKeyConfigurationDateEntity(
lineValues[0].Trim() // action
, lineValues[1].Trim() // nodeID
, lineValues[2].Trim() // officerKey
, lineValues[3].Trim() // date
);
// Exclude adding header to data list
if (counter >= dataRowPosition)
{
inputFileList.XCookAutoHearingOfficerKeyConfigurationDate.Add(inputFileRecord);
}
}
}
counter++;
}
else
{
Logger.WriteToLog("Skipping Header Record.", LogLevel.Verbose);
counter++;
}
}
}
return(inputFileList);
}