public void TableCheck()
{
//MessageBox.Show("Checking Table");
ChargeTable chargeTbl = new ChargeTable();
chargeTbl.ChargeList = new List <ChargeRecord>();
EmployeeTable employeeTbl = new EmployeeTable();
employeeTbl.EmployeeList = new List <EmployeeRecord>();
MeterFreeDateTable meterDateTbl = new MeterFreeDateTable();
meterDateTbl.MeterFreeDateList = new List <MeterFreeDateRecord>();
OfficerTable officerTbl = new OfficerTable();
officerTbl.OfficerList = new List <OfficerRecord>();
TagCategoryTable tagCatTbl = new TagCategoryTable();
tagCatTbl.TagCategoryList = new List <TagCategoryRecord>();
ParkingMeterTable parkMeterTbl = new ParkingMeterTable();
parkMeterTbl.ParkingMeterList = new List <ParkingMeter>();
DateTime LastUpdated = LastUpdateDate();
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://coaworks/api/publicsafety/parkingmetersync/table?"
+ "date=" + LastUpdated.ToShortDateString());
//HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://parking/api/syncapi/"
// + "?date=" + LastUpdated.ToShortDateString()
// + "&user="******"GET";
request.Timeout = 10000;
request.Credentials = CredentialCache.DefaultCredentials;
HttpWebResponse response;
Stream responseStream;
StreamReader reader;
try
{
response = request.GetResponse() as HttpWebResponse;
responseStream = response.GetResponseStream();
reader = new StreamReader(responseStream);
string responseText = reader.ReadToEnd();
JObject o = JObject.Parse(responseText);
#region Charges
JContainer chargesContainer = null;
if (o["charges"] != null && o["charges"].Type != JTokenType.Null)
{
chargesContainer = (JContainer)o["charges"];
List <ChargeRecord> chrgList = new List <ChargeRecord>();
for (int i = 0; i < chargesContainer.Count(); i++)
{
JToken chrg = chargesContainer[i];
ChargeRecord cr = new ChargeRecord();
if (chrg["Charge1"] != null && chrg["Charge1"].Type != JTokenType.Null)
{
cr.Charge = (string)chrg["Charge1"];
}
if (chrg["ChargeCode"] != null && chrg["ChargeCode"].Type != JTokenType.Null)
{
cr.ChargeCode = (string)chrg["ChargeCode"];
}
if (chrg["LocalOrd"] != null && chrg["LocalOrd"].Type != JTokenType.Null)
{
cr.LocalOrd = (string)chrg["LocalOrd"];
}
if (chrg["ViolationAmount"] != null && chrg["ViolationAmount"].Type != JTokenType.Null)
{
cr.ViolationAmount = (decimal)chrg["ViolationAmount"];
}
chrgList.Add(cr);
}
chargeTbl.ChargeList = chrgList;
}
else
{
chargesContainer = null;
}
#endregion
#region Employees
JContainer employeesContainer = null;
if (o["employees"] != null && o["employees"].Type != JTokenType.Null)
{
employeesContainer = (JContainer)o["employees"];
List <EmployeeRecord> empList = new List <EmployeeRecord>();
for (int i = 0; i < employeesContainer.Count(); i++)
{
JToken emp = employeesContainer[i];
EmployeeRecord er = new EmployeeRecord();
if (emp["EmployeeID"] != null && emp["EmployeeID"].Type != JTokenType.Null)
{
er.EmployeeID = (string)emp["EmployeeID"];
}
if (emp["EmployeeName"] != null && emp["EmployeeName"].Type != JTokenType.Null)
{
er.EmployeeName = (string)emp["EmployeeName"];
}
if (emp["UserName"] != null && emp["UserName"].Type != JTokenType.Null)
{
er.UserName = (string)emp["UserName"];
}
if (emp["Status"] != null && emp["Status"].Type != JTokenType.Null)
{
er.Status = (int)emp["Status"];
}
if (er.Status == 1)
{
empList.Add(er);
}
}
employeeTbl.EmployeeList = empList;
}
else
{
employeesContainer = null;
}
#endregion
#region MeterFreeDates
JContainer meterFreeDatesContainer = null;
if (o["meterFreeDates"] != null && o["meterFreeDates"].Type != JTokenType.Null)
{
meterFreeDatesContainer = (JContainer)o["meterFreeDates"];
List <MeterFreeDateRecord> mfDateList = new List <MeterFreeDateRecord>();
for (int i = 0; i < meterFreeDatesContainer.Count(); i++)
{
JToken freeDate = meterFreeDatesContainer[i];
MeterFreeDateRecord mfdr = new MeterFreeDateRecord();
if (freeDate["Date"] != null && freeDate["Date"].Type != JTokenType.Null)
{
mfdr.Date = (string)freeDate["Date"];
}
mfDateList.Add(mfdr);
}
meterDateTbl.MeterFreeDateList = mfDateList;
}
else
{
meterFreeDatesContainer = null;
}
#endregion
#region Officers
JContainer officersContainer = null;
if (o["officers"] != null && o["officers"].Type != JTokenType.Null)
{
officersContainer = (JContainer)o["officers"];
List <OfficerRecord> offRecList = new List <OfficerRecord>();
for (int i = 0; i < officersContainer.Count(); i++)
{
JToken officer = officersContainer[i];
OfficerRecord offR = new OfficerRecord();
if (officer["OfficerBadge"] != null && officer["OfficerBadge"].Type != JTokenType.Null)
{
offR.OfficerBadge = (string)officer["OfficerBadge"];
}
if (officer["EmployeeID"] != null && officer["EmployeeID"].Type != JTokenType.Null)
{
offR.EmployeeID = (string)officer["EmployeeID"];
}
if (officer["Rank"] != null && officer["Rank"].Type != JTokenType.Null)
{
offR.Rank = (string)officer["Rank"];
}
offRecList.Add(offR);
}
officerTbl.OfficerList = offRecList;
}
else
{
officersContainer = null;
}
#endregion
#region TagCategories
JContainer tagCategoriesContainer = null;
if (o["tagCategories"] != null && o["tagCategories"].Type != JTokenType.Null)
{
tagCategoriesContainer = (JContainer)o["tagCategories"];
List <TagCategoryRecord> tCatList = new List <TagCategoryRecord>();
for (int i = 0; i < tagCategoriesContainer.Count(); i++)
{
JToken tagCat = tagCategoriesContainer[i];
TagCategoryRecord tcr = new TagCategoryRecord();
if (tagCat["Description"] != null && tagCat["Description"].Type != JTokenType.Null)
{
tcr.Description = (string)tagCat["Description"];
}
if (tagCat["TagCategory1"] != null && tagCat["TagCategory1"].Type != JTokenType.Null)
{
tcr.TagCategory = (string)tagCat["TagCategory1"];
}
tCatList.Add(tcr);
}
tagCatTbl.TagCategoryList = tCatList;
}
else
{
tagCategoriesContainer = null;
}
#endregion
try
{
request.Abort();
response.Close();
responseStream.Close();
reader.Close();
}
catch (Exception ex) { }
}
catch (Exception ex)
{
request.Abort();
}
//This is away from the rest of the table downloading, mainly due to not wanting
//to interfere with the HttpWebResponse and HttpWebRequest
#region ParkingMeters
string connStr = "Data Source=atlas;Initial Catalog=Edits;";
using (SqlConnection sqlconn = new SqlConnection(connStr))
{
string queryStr = "SELECT * FROM Edits.dataloader.PARKINGMETERS_EVW WHERE LifecycleStatus = 'ACTIVE'";
SqlCommand comm = new SqlCommand(queryStr, sqlconn);
sqlconn.Open();
SqlDataReader sqlreader = comm.ExecuteReader();
try
{
while (sqlreader.Read())
{
string Location = sqlreader[2].ToString();
string facilityID = sqlreader[3].ToString();
parkMeterTbl.ParkingMeterList.Add(new ParkingMeter {
Location = Location, FacilityID = facilityID
});
}
}
finally
{
sqlreader.Close();
}
}
#endregion
SqlCeConnection cn = new SqlCeConnection(ConnectionString);
cn.Open();
if (cn.State == ConnectionState.Open)
{
try
{
bool NeedsUpdate = false;
#region Insert Charges
if (chargeTbl.ChargeList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM Charges ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (ChargeRecord CR in chargeTbl.ChargeList)
{
try
{
string cmdTxt2 = "Insert into Charges "
+ "(ChargeCode, Charge, LocalOrd, ViolationAmount)"
+ "values (@chargecode, @charge, @localord, @violationamount)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@chargecode", CR.ChargeCode);
cmd2.Parameters.AddWithValue("@charge", CR.Charge);
cmd2.Parameters.AddWithValue("@localord", CR.LocalOrd);
cmd2.Parameters.AddWithValue("@violationamount", CR.ViolationAmount);
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert Employees
if (employeeTbl.EmployeeList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM Employees ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (EmployeeRecord ER in employeeTbl.EmployeeList)
{
try
{
string cmdTxt2 = "Insert into Employees "
+ "(EmployeeID, EmployeeName, UserName, Status)"
+ "values (@employeeid, @employeename, @username, @status)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@employeeid", ER.EmployeeID);
cmd2.Parameters.AddWithValue("@employeename", ER.EmployeeName);
cmd2.Parameters.AddWithValue("@username", ER.UserName);
cmd2.Parameters.AddWithValue("@status", ER.Status);
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert MeterFreeDates
if (meterDateTbl.MeterFreeDateList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM MeterFreeDates ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (MeterFreeDateRecord MFDR in meterDateTbl.MeterFreeDateList)
{
try
{
string cmdTxt2 = "Insert into MeterFreeDates "
+ "(Date)"
+ "values (@date)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@date", MFDR.Date);
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert Officers
if (officerTbl.OfficerList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM Officers ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (OfficerRecord OR in officerTbl.OfficerList)
{
try
{
string cmdTxt2 = "Insert into Officers "
+ "(OfficerBadge, EmployeeID, Rank)"
+ "values (@officerbadge, @employeeid, @rank)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@officerbadge", OR.OfficerBadge);
cmd2.Parameters.AddWithValue("@employeeid", OR.EmployeeID);
if (OR.Rank == null)
{
cmd2.Parameters.AddWithValue("@rank", DBNull.Value);
}
else
{
cmd2.Parameters.AddWithValue("@rank", OR.Rank);
}
cmd2.ExecuteNonQuery();
}
catch (Exception ex)
{
}
}
}
#endregion
#region Insert TagCategories
if (tagCatTbl.TagCategoryList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM TagCategory ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (TagCategoryRecord TCR in tagCatTbl.TagCategoryList)
{
string cmdTxt2 = "Insert into TagCategory "
+ "(TagCategory, Description)"
+ "values (@tagcategory, @description)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@tagcategory", TCR.TagCategory);
cmd2.Parameters.AddWithValue("@description", TCR.Description);
cmd2.ExecuteNonQuery();
}
}
#endregion
#region Insert Parking Meters
if (parkMeterTbl.ParkingMeterList.Count > 0)
{
NeedsUpdate = true;
string cmdTxt = "DELETE FROM ParkingMeters ";
SqlCeCommand cmd = new SqlCeCommand(cmdTxt, cn);
cmd.ExecuteNonQuery();
foreach (ParkingMeter prkMeter in parkMeterTbl.ParkingMeterList)
{
string cmdTxt2 = "Insert into ParkingMeters "
+ "(Location, FacilityID)"
+ "values (@location, @facilityid)";
SqlCeCommand cmd2 = new SqlCeCommand(cmdTxt2, cn);
cmd2.Parameters.AddWithValue("@location", prkMeter.Location);
cmd2.Parameters.AddWithValue("@facilityid", prkMeter.FacilityID);
cmd2.ExecuteNonQuery();
}
}
#endregion
//MessageBox.Show("Needs Update is " + NeedsUpdate.ToString());
if (NeedsUpdate)
{
UpdateLastUpdateDate();
}
}
catch (Exception ex)
{
PopUpForm pf = new PopUpForm(ex.ToString());
pf.ShowDialog();
}
finally
{
cn.Close();
}
}
}
private void TicketService()
{
Ticket.IsOffline = false;
List <Violation> vioList = new List <Violation>();
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://coaworks/api/publicsafety/parkingticket/search?"
+ "tagState=" + Vehicle.TagState
+ "&tagNumber=" + Vehicle.TagNumber
+ "&meter=" + Ticket.MeterNumber);//MeterTxt.Text);
//HttpWebRequest request = (HttpWebRequest)WebRequest.Create("http://parking/api/tag/search/"
// + Vehicle.TagState + "/" + Vehicle.TagNumber
// + "?meter=" + MeterTxt.Text
// + "&tagCategory=" + Ticket.VehicleTagCategory
// + "&user="******"GET";
request.Timeout = 10000;
request.Credentials = CredentialCache.DefaultCredentials;
HttpWebResponse response;
Stream responseStream;
StreamReader reader;
try
{
response = request.GetResponse() as HttpWebResponse;
responseStream = response.GetResponseStream();
reader = new StreamReader(responseStream);
string responseText = reader.ReadToEnd();
#region JSON
JObject o = JObject.Parse(responseText);
#region Owner
JContainer ownerContainer = null;
if (o["owner"] != null && o["owner"].Type != JTokenType.Null)
{
ownerContainer = (JContainer)o["owner"];
ParkingApp_Tablet.Owner.Name = (string)ownerContainer["name"];
ParkingApp_Tablet.Owner.ProfileID = (string)ownerContainer["profileID"];
Ticket.ProfileID = ParkingApp_Tablet.Owner.ProfileID;
}
else
{
ownerContainer = null;
}
#endregion
#region Vehicle
JContainer vehicleContainer = null;
if (o["vehicle"] != null && o["vehicle"].Type != JTokenType.Null)
{
vehicleContainer = (JContainer)o["vehicle"];
Vehicle.Description = (string)vehicleContainer["description"];
Vehicle.TowEligible = (bool)vehicleContainer["towEligible"];
#region Meter Violation
JContainer eligibleMeterViolationContainer = null;
if (vehicleContainer["eligibleMeterViolation"] != null && vehicleContainer["eligibleMeterViolation"].Type != JTokenType.Null)
{
eligibleMeterViolationContainer = (JContainer)vehicleContainer["eligibleMeterViolation"];
Vehicle.MeterViolationCharge = (string)eligibleMeterViolationContainer["charge"];
Vehicle.MeterViolationChargeCode = (string)eligibleMeterViolationContainer["chargeCode"];
Vehicle.MeterViolationAmount = (string)eligibleMeterViolationContainer["amount"];
}
else
{
eligibleMeterViolationContainer = null;
Vehicle.MeterViolationCharge = "";
}
JContainer lastMeterViolationContainer = null;
if (vehicleContainer["lastMeterViolation"] != null && vehicleContainer["lastMeterViolation"].Type != JTokenType.Null)
{
lastMeterViolationContainer = (JContainer)vehicleContainer["lastMeterViolation"];
Vehicle.LastMeterViolationCharge = (string)lastMeterViolationContainer["charge"];
Vehicle.LastMeterViolationChargeCode = (string)lastMeterViolationContainer["chargeCode"];
//string lastMeterAmount = (string)lastMeterViolationContainer["amount"];
}
else
{
lastMeterViolationContainer = null;
Vehicle.LastMeterViolationCharge = "";
Vehicle.LastMeterViolationChargeCode = "";
}
#endregion
#region 2 Hour Violation
JContainer eligibleTwoHourViolationContainer = null;
if (vehicleContainer["eligibleTwoHourViolation"] != null && vehicleContainer["eligibleTwoHourViolation"].Type != JTokenType.Null)
{
eligibleTwoHourViolationContainer = (JContainer)vehicleContainer["eligibleTwoHourViolation"];
Vehicle.TwoHourViolationCharge = (string)eligibleTwoHourViolationContainer["charge"];
Vehicle.TwoHourViolationChargeCode = (string)eligibleTwoHourViolationContainer["chargeCode"];
Vehicle.TwoHourViolationAmount = (string)eligibleTwoHourViolationContainer["amount"];
}
else
{
eligibleTwoHourViolationContainer = null;
Vehicle.TwoHourViolationCharge = "";
}
JContainer lastTwoHourViolationContainer = null;
if (vehicleContainer["lastTwoHourViolation"] != null && vehicleContainer["lastTwoHourViolation"].Type != JTokenType.Null)
{
lastTwoHourViolationContainer = (JContainer)vehicleContainer["lastTwoHourViolation"];
Vehicle.LastTwoHourViolationCharge = (string)lastTwoHourViolationContainer["charge"];
Vehicle.LastTwoHourViolationChargeCode = (string)lastTwoHourViolationContainer["chargeCode"];
//string lastTwoHourAmount = (string)lastTwoHourViolationContainer["amount"];
}
else
{
lastTwoHourViolationContainer = null;
Vehicle.LastTwoHourViolationCharge = "";
Vehicle.LastTwoHourViolationChargeCode = "";
}
#endregion
}
else
{
vehicleContainer = null;
}
#endregion
#region Meter
JContainer meterContainer;
if (o["meter"] != null && o["meter"].Type != JTokenType.Null)
{
meterContainer = (JContainer)o["meter"];
}
else
{
meterContainer = null;
}
//if (MeterTxt.Text != "")
// Ticket.MeterNumber = MeterTxt.Text.ToUpper();
//if (LocationTxt.Text != "")
// Ticket.ViolationLocation = LocationTxt.Text;
//else
// Ticket.ViolationLocation = meter[0];
#endregion
#region Violation
JContainer violationContainer;
if (o["outstanding"] != null && o["outstanding"].Type != JTokenType.Null)
{
violationContainer = (JContainer)o["outstanding"];
Violations.TotalBalance = (string)violationContainer["total"];
}
else
{
violationContainer = null;
}
if (violationContainer != null)
{
JContainer violations = (JContainer)violationContainer["violations"];
for (int i = 0; i < violations.Count(); i++)
{
JToken job = violations[i];
vioList.Add(new Violation()
{
Amount = (string)job["amount"],
Charge = (string)job["charge"],
Date = (string)job["date"],
Ticket = (string)job["ticketNumber"]
});
}
Violations.ViolationList = vioList;
}
#endregion
//If Vehicle can be towed
if (Vehicle.TowEligible)
{
PopUpForm pf = new PopUpForm("This vehicle is eligible for towing.");
pf.ShowDialog();
}
if (Ticket.MeterNumber != "")
{
if (dbh.CheckDate())
{
//If Vehicle has reached the limit of meter violations for today
if (Vehicle.MeterViolationCharge == "" && Vehicle.LastMeterViolationChargeCode == "03")
{
PopUpForm pf = new PopUpForm("Meter Violation Limit Reached. \nNot Eligible For New Meter Violation");
pf.ShowDialog();
reset = true;
}
//if Vehicle has received a meter violation within the last two hours.
else if (Vehicle.MeterViolationCharge == "")
{
PopUpForm pf = new PopUpForm("Previous Meter Violation Issued Within Two Hours. \nNot Eligible For New Meter Violation");
pf.ShowDialog();
reset = true;
}
}
else
{
PopUpForm pf = new PopUpForm("Meter Violations Can Only Be Issued Between 8AM and 5PM Monday - Friday");
pf.ShowDialog();
reset = true;
}
}
else
{
if (Vehicle.TwoHourViolationCharge == "" && Vehicle.LastTwoHourViolationChargeCode == "14")
{
PopUpForm pf = new PopUpForm("Two Hour Violation Limit Reached. \nNot Eligible For New Two Hour Violation");
pf.ShowDialog();
}
else if (Vehicle.TwoHourViolationCharge == "")
{
PopUpForm pf = new PopUpForm("Previous Two Hour Violation Issued Within Two Hours. \nNot Eligible For New Two Hour Violation");
pf.ShowDialog();
}
}
#endregion
try
{
request.Abort();
response.Close();
responseStream.Close();
reader.Close();
}
catch (Exception ex) { }
}
catch (Exception execpt)
{
request.Abort();
TicketOffline();
}
}