Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- public void InsertOffender(Offender offender)
- {
- using (MySqlCommand command = new MySqlCommand())
- {
- string[] probationTermArray = offender.currentConviction.ParseConvictionProbation(offender);
- command.Connection = this.connection;
- // ---- inserting alias ------
- command.CommandText = String.Format("INSERT INTO alias (offenders_id, first_name, middle_initial, last_name, updated_by) VALUES ("{0}", "{1}", "{2}", "{3}", {4})", "NY" + offender.offenderId, offender.firstName, offender.middleName, offender.lastName, 5);
- command.ExecuteNonQuery();
- // ------ inserting current conviction ------
- command.CommandText = String.Format("INSERT INTO sentencing_info (offenders_id, conviction_date, probation, jail, suprv_agency, arresting_agency) VALUES ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}")", "NY" + offender.offenderId, offender.currentConviction.GetParseableConvictionDate() != null ? DateTime.ParseExact(offender.currentConviction.GetParseableConvictionDate(), "MMM-dd-yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") : "", probationTermArray[1], probationTermArray[0], offender.supervisingInfo, offender.currentConviction.arrestingAgency);
- command.ExecuteNonQuery();
- // ----- inserting pervious convictions -----
- if (offender.perviousConvictions != null)
- foreach (Conviction conviction in offender.perviousConvictions)
- {
- string[] preprobationTermArray = conviction.ParseConvictionProbation(offender);
- command.CommandText = String.Format("INSERT INTO sentencing_info (offenders_id, conviction_date, probation, jail, suprv_agency, arresting_agency, Conviction_Type) VALUES ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "p")", "NY" + offender.offenderId, conviction.GetParseableConvictionDate() != null ? DateTime.ParseExact(conviction.GetParseableConvictionDate(), "MMM-dd-yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") : "", preprobationTermArray[1], preprobationTermArray[0], offender.supervisingInfo, conviction.arrestingAgency);
- command.ExecuteNonQuery();
- }
- // ------ inserting picture link -----
- string possiblePhotoDate = offender.GetParseablePhotoDate();
- command.CommandText = String.Format("INSERT INTO offender_pictures (offenders_id, image_name, updated_by, PhotoDate) VALUES ("{0}", "{1}", "{2}", "{3}")", "NY" + offender.offenderId, offender.linkToPic, "5", possiblePhotoDate == null ? "Unknown" : DateTime.Parse(possiblePhotoDate).ToString("yyyy-MM-dd"));
- command.ExecuteNonQuery();
- // ---- inserting markings -----
- string MarkingsTemp = "";
- if (offender.markings.Length >= 1)
- {
- foreach (string s in offender.markings)
- {
- MarkingsTemp = MarkingsTemp + s + "/";
- }
- MarkingsTemp = MarkingsTemp.Substring(0, MarkingsTemp.Length - 1);
- }
- else
- MarkingsTemp = "NONE REPORTED";
- command.CommandText = String.Format("INSERT INTO marks (offenders_id, description) VALUES ("{0}", "{1}")", "NY" + offender.offenderId, MarkingsTemp);
- command.ExecuteNonQuery();
- // ----- inserting addresses -----
- //----------------------------------------- I wrote this today, code above of this line was yesterday and it stop working--------------------------------------
- foreach (Address addr in offender.address)
- {
- string[] splitAddress = addr.GetComparbleAddress();
- if (addr.type.Contains("EMP") && splitAddress != null)
- {
- command.CommandText = String.Format("INSERT INTO employers (offenders_id, street, city, zip, state_id) VALUES ("{0}", "{1}", "{2}", "{3}", {4})", "NY" + offender.offenderId, splitAddress[0], splitAddress[1], splitAddress[2], 36);
- command.ExecuteNonQuery();
- }
- else
- {
- if (addr.type.Contains("RES") && addr.type.Contains("Primary"))
- {
- string haircolor;
- Constants.HairConstants.TryGetValue(offender.hairColor.ToUpper().Trim(), out haircolor);
- int eyecolor;
- Constants.EyeColorConstants.TryGetValue(offender.eyeColor.ToUpper().Trim(), out eyecolor);
- int glasses;
- Constants.GlassesConstants.TryGetValue(offender.CorrectiveLens.ToUpper().Trim(), out glasses);
- int ethnicityid;
- string ethnicityString = Address.RemoveSpecialCharacters(offender.ethnicity.Trim().ToUpper());
- Constants.EthnicityConstants.TryGetValue(ethnicityString, out ethnicityid);
- int raceid = 1;
- string raceString = Address.RemoveSpecialCharacters(offender.ethnicity.Replace(" ", "").ToUpper());
- if (!raceString.Equals(""))
- Constants.RaceConstants.TryGetValue(Constants.RaceConstants.Keys.First(x => x[1].Equals(raceString)), out raceid);
- if (splitAddress == null)
- {
- string toSend = (String.Format("INSERT INTO offenders (dob, sex, first_name, date_created, hair, eyes, glasses, middle_name, last_name, address_line1, city, height, weight, OffDetail, risk_level, special_condition, offender_id, date_modified, designation, victim_relationship, weapon_used, force_used, nysid, scrape_state, state, zipcode, ethnicity_id, county_id, race_id, isgeocoded, created_by_user_id, computer_used, porn_involved, approved_for_web) VALUES ("{0}", "{1}", "{2}", "{3}", "{4}","{5}", "{6}","{7}", "{8}","{9}", "{10}","{11}", "{12}","{13}", "{14}","{15}", "{16}","{17}", "{18}","{19}", "{20}","{21}", "{22}","{23}", "{24}","{25}", "{26}","{27}", "{28}","{29}", "{30}","{31}","{32}","{33}")",
- offender.GetParseableDoB() != null ? DateTime.ParseExact(offender.GetParseableDoB(), "MMM-dd-yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") : "", offender.sex.First(), offender.firstName, DateTime.Now.ToString("yyyy-MM-dd"), haircolor, eyecolor, glasses, offender.middleName, offender.lastName, "",
- "", offender.height.Replace("'", "ft").Replace(""", "in"), offender.weight, offender.isWanted ? "Warrant" : offender.currentPlacement, offender.riskLevel, offender.conditions, "NY" + offender.offenderId, DateTime.Now.ToString("yyyy-MM-dd"), offender.designation, offender.currentConviction.relationship, offender.currentConviction.weaponsUsed, offender.currentConviction.forceUsed, offender.offenderId, 36, 36, "", ethnicityid, 360000 + offender.countyid, raceid, (offender.address[0].geoX != null && !offender.address[0].geoX.Equals("")) ? 1 : 0, 5, offender.currentConviction.computerUsed.Contains("No") ? 0 : 1, offender.currentConviction.pornInvolved.Contains("No") ? 0 : 1, 1));
- command.CommandText = toSend;
- command.ExecuteNonQuery();
- }
- else
- {
- string toSend = (String.Format("INSERT INTO offenders (dob, sex, first_name, date_created, hair, eyes, glasses, middle_name, last_name, address_line1, city, height, weight, OffDetail, risk_level, special_condition, offender_id, date_modified, designation, victim_relationship, weapon_used, force_used, nysid, scrape_state, state, zipcode, ethnicity_id, county_id, race_id, isgeocoded, created_by_user_id, computer_used, porn_involved, approved_for_web) VALUES ("{0}", "{1}", "{2}", "{3}", "{4}","{5}", "{6}","{7}", "{8}","{9}", "{10}","{11}", "{12}","{13}", "{14}","{15}", "{16}","{17}", "{18}","{19}", "{20}","{21}", "{22}","{23}", "{24}","{25}", "{26}","{27}", "{28}","{29}", "{30}","{31}","{32}","{33}")",
- offender.GetParseableDoB() != null ? DateTime.ParseExact(offender.GetParseableDoB(), "MMM-dd-yyyy", CultureInfo.InvariantCulture).ToString("yyyy-MM-dd") : "", offender.sex.First(), offender.firstName, DateTime.Now.ToString("yyyy-MM-dd"), haircolor, eyecolor, glasses, offender.middleName, offender.lastName, splitAddress[0],
- splitAddress[1], offender.height.Replace("'", "ft").Replace(""", "in"), offender.weight, offender.currentPlacement, offender.riskLevel, offender.conditions, "NY" + offender.offenderId, DateTime.Now.ToString("yyyy-MM-dd"), offender.designation, offender.currentConviction.relationship, offender.currentConviction.weaponsUsed, offender.currentConviction.forceUsed, offender.offenderId, 36, 36, splitAddress[2], ethnicityid, 360000 + offender.countyid, raceid, (offender.address[0].geoX != null && !offender.address[0].geoX.Equals("")) ? 1 : 0, 5, offender.currentConviction.computerUsed.Contains("No") ? 0 : 1, offender.currentConviction.pornInvolved.Contains("No") ? 0 : 1, 1));
- command.CommandText = toSend;
- command.ExecuteNonQuery();
- }
- }
- else
- {
- if (splitAddress != null)
- {
- Console.WriteLine(addr.type + " " + offender.offenderId);
- command.CommandText = String.Format("INSERT INTO sec_addr (offender_id, nysid, address_line1, city, state, zipcode, risk_level, TypeAddr) VALUES ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", "{6}", "RES-SECONDARY")", "NY" + offender.offenderId, offender.offenderId, splitAddress[0], splitAddress[1], 36, splitAddress[2], offender.riskLevel);
- command.ExecuteNonQuery();
- }
- }
- }
- }
- //------ inserting vehicles ------------
- //------------------------ this little bit was also wrote yesterday and worked---------------------------
- Vehicle[] vehicles = offender.currentVehicles;
- if (vehicles != null)
- foreach (Vehicle v in vehicles)
- {
- string[] makeModelSplit = v.makeModel.Split(' ');
- int stateid = 66;
- Constants.StateConstants.TryGetValue((v.state.ToUpper().Replace(" ", "")), out stateid);
- command.CommandText = String.Format("INSERT INTO vehicles (offenders_id, vehicle_license_plate, vehicle_year, vehicle_make, vehicle_color, vehicle_model, state_id) VALUES ("{0}", "{1}", "{2}", "{3}", "{4}", "{5}", {6})", "NY" + offender.offenderId, v.plate, v.year, makeModelSplit.Length > 1 ? makeModelSplit[0] : v.makeModel, v.color, makeModelSplit.Length > 1 ? makeModelSplit[1] : "", stateid);
- command.ExecuteNonQuery();
- }
- }
- }
- public static readonly string StatesDirectoryLocation = Path.Combine(Environment.CurrentDirectory, "NY_Offender_OUT");
- public static Dictionary<String[], int> RaceConstants;
- public static Dictionary<int, int[]> EmailIds;
- public static Dictionary<String, String> HairConstants;
- public static Dictionary<String, int> GlassesConstants;
- public static Dictionary<string, int> EyeColorConstants;
- public static Dictionary<string, int> EthnicityConstants;
- public static Dictionary<String, int> StateConstants;
- public static Dictionary<String, int> CountyContants;
- public static void loadSqlConstants()
- {
- MySqlUtils utils = new MySqlUtils();
- EthnicityConstants = utils.LoadEthnicityInformation();
- EyeColorConstants = utils.LoadEyeColorInformation();
- RaceConstants = utils.LoadRaceInformation();
- EmailIds = utils.LoadEmailAlertZipcodes();
- HairConstants = utils.LoadHairInformation();
- GlassesConstants = utils.LoadGlassesInformation();
- StateConstants = utils.LoadStateInformation();
- CountyContants = utils.LoadCountyId();
- utils.close();
- }
- }
- public Dictionary<string, string> LoadHairInformation()
- {
- Dictionary<string, string> record = new Dictionary<string, string>();
- using (MySqlCommand command = new MySqlCommand())
- {
- MySqlDataReader rdr = null;
- command.Connection = this.connection;
- string strQ = String.Format("SELECT * FROM haircolor");
- command.CommandText = strQ;
- command.Prepare();
- rdr = command.ExecuteReader();
- while (rdr.Read())
- {
- record.Add(rdr.GetString(2).ToUpper(), rdr.GetString(1));
- }
- rdr.Close();
- }
- return record;
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement