/// <summary>
/// Connect to the Classic RMS database and look for any violations that belong to the Agency using it's ORI.
/// Load any Agency Violations into the Web RMS System.
/// </summary>
private static void ImportAgencyViolationsFromClassicRms(RMSSystem rmsSystem, Guid agencyId, string classicRmsConnectionString)
{
// call Safe Get(), eat exception and set default value of UCR.
var agencyOri = rmsSystem.GetAgency(agencyId).Jurisdiction.Ori;
using (var conn = new SqlConnection(classicRmsConnectionString))
{
conn.Open();
using (var ucrCodesData = new DataSet())
{
string violationSelect = @"SELECT DISTINCT [VC].[Violation_Code_Link], [VC].[Code], [VC].[Description], [VC].[Statute], [VC].[Statute_Description], " +
"ISNULL([VC].[UCR_Code],'') UCR_Code, IsNull([VC].[UCR_Category],'') UCR_Category, IsNull([UC].[Description],'') UCR_Description, [VC].[Type_Statute], [VC].[Level_Degree], [VC].[State_Code], [VC].[NCIC_Code], " +
"[VC].[Effective_Date],[VC].[Repeal_Date], ISNULL([VC].[Citation],0) Citation, ISNULL([VC].[Warrant],0) Warrant, ISNULL([VC].[Arrest],0) Arrest, " +
" ISNULL([VC].[Violation_Hierarchy],0) Violation_Hierarchy, ISNULL([VC].[Incident],0) Incident, ISNULL([VC].[Jail],0) Jail," +
" ISNULL([VC].[Inactive],0) Inactive, ISNULL([VC].[Reportable],0) Reportable " +
" FROM [VSI_SYSTEM].[dbo].[Violation_Code] [VC] " +
"LEFT OUTER JOIN [VSI_SYSTEM].[dbo].[UCR_Code] [UC] " +
"ON [VC].[UCR_Code] = [UC].[UCR_Code] " +
" WHERE ISNULL([VC].[JURISDICTION],'ALL') = '" + agencyOri + "' ";
using (var adapter = new SqlDataAdapter(violationSelect, conn))
adapter.Fill(ucrCodesData, "ViolationCodes");
Log.Info("Importing {0} Agency Violation Codes into {1}", ucrCodesData.Tables["ViolationCodes"].Rows.Count, agencyOri);
foreach (DataRow violationCodeRow in ucrCodesData.Tables["ViolationCodes"].Rows)
{
// Find or create UCR Code record
var classicId = Convert.ToInt32(violationCodeRow["Violation_Code_Link"].ToString());
var code = violationCodeRow["Code"] as string;
var violationDescription = violationCodeRow["Description"] as string;
var violationHierarchy = violationCodeRow["Violation_Hierarchy"].ToString();
var statuteCode = violationCodeRow["Statute"] as string;
var statuteDescription = violationCodeRow["Statute_Description"] as string;
var ucrCode = violationCodeRow["UCR_Code"] as string;
var ucrCategory = violationCodeRow["UCR_Category"] as string;
var ucrDescription = violationCodeRow["UCR_Description"] as string;
var typeStatute = violationCodeRow["Type_Statute"] as string;
var levelDegree = violationCodeRow["Level_Degree"] as string;
var stateCode = violationCodeRow["State_Code"] as string;
var ncicCode = violationCodeRow["NCIC_Code"] as string;
var effectiveDatestr = violationCodeRow["Effective_Date"] as string;
var effectiveDate = String.IsNullOrEmpty(effectiveDatestr) ? DateTime.Now.AddYears(-100) : Convert.ToDateTime(effectiveDatestr);
var repealDatestr = violationCodeRow["Repeal_Date"] as string;
var repealDate = String.IsNullOrEmpty(repealDatestr) ? DateTime.Now.AddYears(100) : Convert.ToDateTime(repealDatestr);
var citation = Convert.ToBoolean(violationCodeRow["Citation"].ToString());
var warrant = Convert.ToBoolean(violationCodeRow["Warrant"].ToString());
var arrest = Convert.ToBoolean(violationCodeRow["Arrest"].ToString());
var incident = Convert.ToBoolean(violationCodeRow["Incident"].ToString());
var jail = Convert.ToBoolean(violationCodeRow["Jail"].ToString());
var inactive = Convert.ToBoolean(violationCodeRow["Inactive"].ToString());
var reportable = Convert.ToBoolean(violationCodeRow["Reportable"].ToString());
statuteCode = String.IsNullOrEmpty(statuteCode) ? "" : statuteCode;
statuteDescription = String.IsNullOrEmpty(statuteDescription) ? "" : statuteDescription;
ucrCode = String.IsNullOrEmpty(ucrCode) ? "" : ucrCode;
ucrDescription = String.IsNullOrEmpty(ucrDescription) ? "" : ucrDescription;
code = String.IsNullOrEmpty(code) ? "" : code;
var statuteDefinition = String.Format("{0} {1} {2} {3} {4}", statuteCode, statuteDescription, ucrCode, ucrDescription, code);
var filterCode = String.IsNullOrEmpty(ucrCode) ? "" : ucrCode;
var filterDesc = String.IsNullOrEmpty(ucrDescription) ? "" : ucrDescription;
var filter = new CodeValue {
Code = filterCode, Description = filterDesc
};
var existingViolation = rmsSystem.ViolationCodes.FirstOrDefault(x => x.ClassicId == classicId);
if (existingViolation != null)
{
existingViolation.AgencyId = agencyId;
existingViolation.Arrest = arrest;
existingViolation.Citation = citation;
existingViolation.Code = code;
existingViolation.EffectiveDate = effectiveDate;
existingViolation.Filter = filter;
existingViolation.Inactive = inactive;
existingViolation.Incident = incident;
existingViolation.Jail = jail;
existingViolation.LevelOrDegree = levelDegree;
existingViolation.NcicCode = ncicCode;
existingViolation.RepealDate = repealDate;
existingViolation.Reportable = reportable;
existingViolation.StateCode = stateCode;
existingViolation.StatuteCode = statuteCode;
existingViolation.StatuteDefinition = statuteDefinition;
existingViolation.StatuteDescription = statuteDescription;
existingViolation.Type = typeStatute;
existingViolation.UcrCategory = ucrCategory;
existingViolation.UcrCode = ucrCode;
existingViolation.ViolationDescription = violationDescription;
existingViolation.ViolationHierarchy = violationHierarchy;
existingViolation.Warrant = warrant;
}
else
{
rmsSystem.CreateViolationCode(agencyId, classicId, code, violationDescription, reportable, ucrCode,
ucrCategory, statuteCode, statuteDescription, violationHierarchy, typeStatute, levelDegree,
stateCode, ncicCode, effectiveDate, repealDate, inactive, incident, citation,
arrest, warrant, jail, statuteDefinition, filter);
}
}
}
}
}