function GetLocationsByDayByType($date, $type)
{
dprint("GetLocationsByDayByType() Start.");
try {
$db = new DatabaseTool();
if ($date == "") {
$date = date("Y-m-d");
}
// create the query
$query = 'SELECT DISTINCT itemid,event,fulladdress,lat,lng FROM incidents JOIN eventtypes ON incidents.event = eventtypes.eventtype WHERE pubdate = ? AND lat <> "" AND lng <> "" AND eventtypes.eventtypeid = ? GROUP BY itemid ORDER BY pubtime DESC;';
$mysqli = $db->Connect();
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $date, $type);
// bind the varibale
$results = $db->Execute($stmt);
// create an array to put our results into
$incidents = array();
// decode the rows
foreach ($results as $result) {
$incident = (object) array('itemid' => $result['itemid'], 'incident' => $result['event'], 'fulladdress' => $result['fulladdress'], 'lat' => $result['lat'], 'lng' => $result['lng']);
$incidents[] = $incident;
}
// close our DB connection
$db->Close($mysqli, $stmt);
} catch (Exception $e) {
dprint("Caught exception: " . $e->getMessage());
}
dprint("GetLocationsByDayByType() Done.");
return $incidents;
}
function GetTotalStatusTypes()
{
dprint("GetTotalStatusTypes() Start.");
try {
$db = new DatabaseTool();
// create the query
$query = "SELECT COUNT(*) AS count FROM statustypes";
$mysqli = $db->Connect();
$stmt = $mysqli->prepare($query);
$results = $db->Execute($stmt);
$count = $results[0]["count"];
// close our DB connection
$db->Close($mysqli, $stmt);
} catch (Exception $e) {
dprint("Caught exception: " . $e->getMessage());
}
dprint("GetTotalStatusTypes() Done.");
// return the count
return $count;
}
function GetHourlyCountsByEventId($eventtypeid, $date)
{
dprint("GetHourlyCountsByEventId() Start.");
try {
$db = new DatabaseTool();
// get the eventtype via the id
$eventtype = $this->GetEventTextFromID($eventtypeid);
if ($date == "") {
$date = date("Y-m-d");
}
// genrate the query
$query = 'SELECT COUNT(DISTINCT itemid) as count, pubtime FROM incidents WHERE LOWER(event)= ? AND pubdate = ? GROUP BY HOUR(pubtime)';
$mysqli = $db->Connect();
$stmt = $mysqli->prepare($query);
$stmt->bind_param("ss", $eventtype, $date);
// bind the varibale
$results = $db->Execute($stmt);
dprint("Processing " . count($results) . " Results ...");
// create an array to place the events into
$events = array();
// populate the array of event counts
foreach ($results as $result) {
$event = new Event();
$event->pubtime = $result['pubtime'];
$event->count = $result['count'];
$events[] = $event;
}
// create an array to put the counts into
$counts = array();
// calculate the counts for each hour
for ($hour = 0; $hour < 24; $hour++) {
$count = 0;
// decode the rows
foreach ($events as $event) {
// decode the hour of the incident
$pubhour = (int) substr($event->pubtime, 0, 2);
// if the current hour is the published hour, then add to the count
if ($pubhour == $hour) {
$count = $count + $event->count;
}
}
// add the count to the array
$counts[] = $count;
}
// close our DB connection
$db->Close($mysqli, $stmt);
} catch (Exception $e) {
dprint("Caught exception: " . $e->getMessage());
}
dprint("GetHourlyCountsByEventId() Done.");
return $counts;
}
function GetIncidentCountByAgencyIDAndDate($agencyid, $date)
{
dprint("GetIncidentCountByAgencyIDAndDate() Start.");
try {
$db = new DatabaseTool();
if ($date == "") {
$date = date("Y-m-d");
}
// create the query
$query = 'SELECT COUNT(DISTINCT itemid) as count FROM incidents WHERE agencyid = ? AND pubdate = ?';
$mysqli = $db->Connect();
$stmt = $mysqli->prepare($query);
$stmt->bind_param("is", $agencyid, $date);
// bind the variable
$results = $db->Execute($stmt);
// get the count
$count = $results[0]['count'];
// close our DB connection
$db->Close($mysqli, $stmt);
} catch (Exception $e) {
dprint("Caught exception: " . $e->getMessage());
}
dprint("GetIncidentCountByAgencyIDAndDate() Done.");
return $count;
}
function GetAllTimeSum()
{
dprint("GetAllTimeSum() Start.");
try {
$db = new DatabaseTool();
// create the query
$query = 'select tmp.pubdate as date, count(tmp.zeecount) as count from (select DISTINCT itemid, pubdate, count(incidentid) as zeecount from incidents group by itemid order by pubdate) as tmp group by pubdate;';
$mysqli = $db->Connect();
$stmt = $mysqli->prepare($query);
$results = $db->Execute($stmt);
dprint("Processing " . count($results) . " Results ...");
// close our DB connection
$db->Close($mysqli, $stmt);
} catch (Exception $e) {
dprint("Caught exception: " . $e->getMessage());
}
dprint("GetAllTimeSum() Done.");
return $results;
}
function ValidAgencyByShortName($agencyshortname)
{
dprint("ValidAgencyByShortName() Start.");
try {
$db = new DatabaseTool();
// create the query
$query = 'SELECT COUNT(*) as count FROM agencies WHERE shortname = ?';
$mysqli = $db->Connect();
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $agencyshortname);
// bind the varibale
$results = $db->Execute($stmt);
dprint("Processing " . count($results) . " Results ...");
// see if the shortname exists
if ($results[0]['count'] == "0") {
$valid = false;
} else {
$valid = true;
}
// close our DB connection
$db->Close($mysqli, $stmt);
} catch (Exception $e) {
dprint("Caught exception: " . $e->getMessage());
}
dprint("ValidAgencyByShortName() Done.");
// return our created object
return $valid;
}