/**
* Does different searches depending on the indexes of the $query
*
* @query['address'] Will do a search in CRM and a search for addresses in Master Address
* @query['street'] Will do a search in CRM and a search for streets in Master Address
* @query['text'] Will only do a search in CRM
*
* Addresses will be return as $array['location text'] = $flag
* where $flag is true if returned by AddressService
*
* @param array $query
* @return array
*/
public static function search($query)
{
$results = array();
$crm_query = '';
if (isset($query['address']) && $query['address']) {
$crm_query = $query['address'];
} elseif (isset($query['text']) && $query['text']) {
$crm_query = $query['text'];
}
if ($crm_query) {
$zend_db = Database::getConnection();
$sql = "select location, addressId, city,\n\t\t\t\t\t'database' as source,\n\t\t\t\t\tcount(*) as ticketCount\n\t\t\t\t\tfrom tickets where location like ?\n\t\t\t\t\tgroup by location, addressId, city";
$q = $zend_db->query($sql)->execute(["{$crm_query}%"]);
foreach ($q as $row) {
$results[$row['location']] = $row;
}
}
if (isset($query['address']) && $query['address']) {
foreach (AddressService::searchAddresses($query['address']) as $location => $data) {
if (!isset($results[$location])) {
$results[$location] = $data;
} else {
$results[$location] = array_merge($results[$location], $data);
}
$results[$location]['source'] = 'Master Address';
}
} elseif (isset($query['street']) && $query['street']) {
foreach (AddressService::searchStreets($query['street']) as $location => $street_id) {
$results[$location]['addressId'] = $street_id;
$results[$location]['source'] = 'Master Address';
}
}
return $results;
}
/**
* Populates the object with data
*
* Passing in an associative array of data will populate this object without
* hitting the database.
*
* Passing in a scalar will load the data from the database.
* This will load all fields in the table as properties of this class.
* You may want to replace this with, or add your own extra, custom loading
*
* @param int|string|array $id (ID, email, username)
*/
public function __construct($id = null)
{
if ($id) {
if (is_array($id)) {
$this->exchangeArray($id);
} else {
$zend_db = Database::getConnection();
if (ActiveRecord::isId($id)) {
$sql = 'select * from people where id=?';
} elseif (false !== strpos($id, '@')) {
$sql = 'select * from people where email=?';
} else {
$sql = 'select * from people where username=?';
}
$result = $zend_db->createStatement($sql)->execute([$id]);
if (count($result)) {
$this->exchangeArray($result->current());
} else {
throw new \Exception('people/unknownPerson');
}
}
} else {
// This is where the code goes to generate a new, empty instance.
// Set any default values for properties that need it here
$this->setAuthenticationMethod('local');
}
}
/**
* Populates the object with data
*
* Passing in an associative array of data will populate this object without
* hitting the database.
*
* Passing in a scalar will load the data from the database.
* This will load all fields in the table as properties of this class.
* You may want to replace this with, or add your own extra, custom loading
*
* @param int|array $id
*/
public function __construct($id = null)
{
if ($id) {
if (is_array($id)) {
$this->exchangeArray($id);
} else {
$zend_db = Database::getConnection();
if (ActiveRecord::isId($id)) {
$sql = 'select * from media where id=?';
} else {
// Media internalFilenames include the original file extensions
// However, the filename being requested may be for a generated thumbnail
// We need to chop off the extension and do a wildcard search
$filename = preg_replace('/[^.]+$/', '', $id);
$id = "{$filename}%";
$sql = 'select * from media where internalFilename like ?';
}
$result = $zend_db->createStatement($sql)->execute([$id]);
if (count($result)) {
$this->exchangeArray($result->current());
} else {
throw new \Exception('media/unknownMedia');
}
}
} else {
// This is where the code goes to generate a new, empty instance.
// Set any default values for properties that need it here
$this->setUploaded('now');
if (isset($_SESSION['USER'])) {
$this->setPerson($_SESSION['USER']);
}
}
}
/**
* Removes this record from the database
*/
protected function delete()
{
if ($this->getId()) {
$sql = new Sql(Database::getConnection(), $this->tablename);
$delete = $sql->delete()->where(['id' => $this->getId()]);
$sql->prepareStatementForSqlObject($delete)->execute();
}
}
public function save()
{
if ($this->isDefault()) {
$zend_db = Database::getConnection();
$zend_db->query('update substatus set isDefault=0 where status=?', $this->getStatus());
}
parent::save();
}
public function testUpdateTicketClusters()
{
$ticket = new Ticket($this->testTicketId);
GeoCluster::updateTicketClusters($ticket);
$zend_db = Database::getConnection();
$result = $zend_db->query('select * from ticket_geodata where ticket_id=?')->execute([$this->testTicketId]);
$row = $result->current();
for ($i = 0; $i <= 6; $i++) {
$this->assertGreaterThan(0, $row["cluster_id_{$i}"]);
}
}
public function testLatLngShouldNotAllowZeros()
{
$ticket = new Ticket();
$ticket->handleAdd(array('description' => 'Testing', 'category_id' => $this->testCategoryId, 'latitude' => 0, 'longitude' => 0));
$id = $ticket->getId();
$zend_db = Database::getConnection();
$result = $zend_db->query('select latitude,longitude from tickets where id=?')->execute([$id]);
$row = $result->current();
$this->assertNull($row['latitude']);
$this->assertNull($row['longitude']);
}
/**
* Populates the object with data
*
* Passing in an associative array of data will populate this object without
* hitting the database.
*
* Passing in a scalar will load the data from the database.
* This will load all fields in the table as properties of this class.
* You may want to replace this with, or add your own extra, custom loading
*
* @param int|array $id
*/
public function __construct($id = null)
{
if ($id) {
if (is_array($id)) {
$this->exchangeArray($id);
} else {
$zend_db = Database::getConnection();
$sql = ActiveRecord::isId($id) ? 'select * from contactMethods where id=?' : 'select * from contactMethods where name=?';
$result = $zend_db->createStatement($sql)->execute([$id]);
if (count($result)) {
$this->exchangeArray($result->current());
} else {
throw new \Exception('contactMethods/unknownContactMethod');
}
}
} else {
// This is where the code goes to generate a new, empty instance.
// Set any default values for properties that need it here
}
}
/**
* Populates the object with data
*
* Passing in an associative array of data will populate this object without
* hitting the database.
*
* Passing in a scalar will load the data from the database.
* This will load all fields in the table as properties of this class.
* You may want to replace this with, or add your own extra, custom loading
*
* @param int|array $id
*/
public function __construct($id = null)
{
if ($id) {
if (is_array($id)) {
$this->exchangeArray($id);
} else {
$sql = 'select * from bookmarks where id=?';
$zend_db = Database::getConnection();
$result = $zend_db->createStatement($sql)->execute([$id]);
if (count($result)) {
$this->exchangeArray($result->current());
} else {
throw new \Exception('bookmarks/unknownBookmark');
}
}
} else {
// This is where the code goes to generate a new, empty instance.
// Set any default values for properties that need it here
$this->setPerson_id($_SESSION['USER']->getId());
}
}
/**
* Populates the object with data
*
* Passing in an associative array of data will populate this object without
* hitting the database.
*
* Passing in a scalar will load the data from the database.
* This will load all fields in the table as properties of this class.
* You may want to replace this with, or add your own extra, custom loading
*
* @param int|array $id
*/
public function __construct($id = null)
{
if ($id) {
if (is_array($id)) {
$this->exchangeArray($id);
} else {
$zend_db = Database::getConnection();
$sql = 'select * from peopleAddresses where id=?';
$result = $zend_db->createStatement($sql)->execute([$id]);
if (count($result)) {
$this->exchangeArray($result->current());
} else {
throw new \Exception('addresses/unknownAddress');
}
}
} else {
// This is where the code goes to generate a new, empty instance.
// Set any default values for properties that need it here
$this->setLabel('Home');
}
}
/**
* The thumbnails should be created automatically when first requested
*
* This is kind of like the Apache 404 trick, except we're sending
* all traffic to index.php, so it's handled there, instead of a 404
*/
public function testAutogenerateThumbnailsByURL()
{
$temp = __DIR__ . "/temp.png";
$zend_db = Database::getConnection();
$result = $zend_db->query("select * from media where media_type='image' limit 1")->execute();
if (count($result)) {
$row = $result->current();
$media = new Media($row);
$image = new Image($media);
$image->clearCache();
$this->assertFalse(file_exists($image->getFullPathForSize($this->testSize)));
$url = $image->getURL($this->testSize);
$request = curl_init($image->getURL($this->testSize));
curl_setopt($request, CURLOPT_RETURNTRANSFER, true);
curl_setopt($request, CURLOPT_BINARYTRANSFER, true);
file_put_contents($temp, curl_exec($request));
$this->assertTrue(file_exists($temp));
$info = getimagesize($temp);
$this->assertTrue($info[0] == $this->testSize || $info[1] == $this->testSize);
#if (file_exists($temp)) { unlink($temp); }
}
}
/**
* @param array $data
*/
public function __construct($id = null)
{
if ($id) {
if (is_array($id)) {
$this->exchangeArray($id);
} else {
$zend_db = Database::getConnection();
$sql = "select * from {$this->tablename} where id=?";
$result = $zend_db->createStatement($sql)->execute([$id]);
if (count($result)) {
$this->exchangeArray($result->current());
} else {
throw new \Exception('history/unknownHistory');
}
}
} else {
// This is where the code goes to generate a new, empty instance.
// Set any default values for properties that need it here
$this->setEnteredDate('now');
$this->setActionDate('now');
}
}
<?php
/**
* @copyright 2012-2014 City of Bloomington, Indiana
* @license http://www.gnu.org/licenses/agpl.txt GNU/AGPL, see LICENSE.txt
* @author Cliff Ingham <*****@*****.**>
*/
use Application\Models\Search;
use Application\Models\Ticket;
use Blossom\Classes\Database;
include '../../configuration.inc';
$search = new Search();
$search->solrClient->deleteByQuery('*:*');
$search->solrClient->commit();
$sql = 'select * from tickets';
$zend_db = Database::getConnection();
$result = $zend_db->query($sql)->execute();
$count = count($result);
foreach ($result as $c => $row) {
$ticket = new Ticket($row);
$search->add($ticket);
echo "{$c}/{$count}: {$ticket->getId()}\n";
}
echo "Committing\n";
$search->solrClient->commit();
echo "Optimizing\n";
$search->solrClient->optimize();
echo "Done\n";
public static function volumeByCategory($get, $department_id)
{
$options = self::volumeOptions($get);
$options = $options ? "and {$options}" : '';
$zend_db = Database::getConnection();
$sql = "select p.name, count(*) as count\n from categories p\n join tickets t on p.id=t.category_id\n where p.department_id=?\n {$options}\n group by p.name order by count desc";
$result = $zend_db->query($sql)->execute([$department_id]);
return ['result' => $result];
}
/**
* @param string $table The name of the database table
* @param string $class The class model to use as a resultSetPrototype
*
* You must pass in the fully namespaced classname. We do not assume
* any particular namespace for the models.
*/
public function __construct($table, $class)
{
$this->resultSetPrototype = new ResultSet();
$this->resultSetPrototype->setArrayObjectPrototype(new $class());
$this->tableGateway = new ZendTableGateway($table, Database::getConnection(), null, $this->resultSetPrototype);
}
/**
* @return array
*/
public function getHistory()
{
if (!count($this->history)) {
$zend_db = Database::getConnection();
$sql = 'select * from issueHistory where issue_id=?';
$r = $zend_db->query($sql)->execute([$this->getId()]);
foreach ($r as $row) {
$this->history[] = new IssueHistory($row);
}
}
return $this->history;
}
/**
* @return bool
*/
public function isSafeToDelete()
{
if (!$this->getId()) {
return false;
}
// Because getCategories and getPeople filter out
// categories and people that the user is not permitted to see,
// we cannot use those functions for this test.
// We need to absolutely know if there is any foreign key
// violation before we delete.
$zend_db = Database::getConnection();
$result = $zend_db->query('select count(*) as c from categories where department_id=?')->execute([$this->getId()]);
$row = $result->current();
if ($row['c']) {
return false;
}
$result = $zend_db->query('select count(*) as c from department_categories where department_id=?')->execute([$this->getId()]);
$row = $result->current();
if ($row['c']) {
return false;
}
$result = $zend_db->query('select count(*) as c from people where department_id=?')->execute([$this->getId()]);
$row = $result->current();
if ($row['c']) {
return false;
}
return true;
}
/**
* Finds or creates a cluster for the given Ticket
*
* If there is already a cluster within a certain distance,
* this will return the ID for that cluster.
* If it doesn't find a nearby cluster, this will add a
* new cluster to the database and return the new ID.
*
* @param Ticket $ticket
* @param int $level
*/
public static function assignClusterIdForLevel(Ticket $ticket, $level)
{
$lat = $ticket->getLatitude();
$lng = $ticket->getLongitude();
$dist = 0.01 * pow(2, $level * 2);
$minX = $lng - $dist;
$maxX = $lng + $dist;
$minY = $lat - $dist;
$maxY = $lat + $dist;
// Geocluster center points are in the database, so we can just look
// them up. However, MySQL spatial functions only allow bounding box
// queries, not points inside a circle, which is what we want.
// So, here, we're still calculating the haversine distance
$sql = "\n\t\tSELECT id,x(center) as longitude, y(center) as latitude,\n\t\t\t( SELECT\n\t\t\t\t(ACOS(SIN(RADIANS(y(center))) * SIN(RADIANS({$lat}))\n\t\t\t\t + COS(RADIANS(y(center))) * COS(RADIANS({$lat}))\n\t\t\t\t * COS(RADIANS(x(center) - {$lng}))) * 6371.0)\n\t\t\t) as distance\n\t\tFROM geoclusters\n\t\tWHERE level=?\n\t\t and (ACOS(SIN(RADIANS(y(center))) * SIN(RADIANS({$lat}))\n\t\t\t + COS(RADIANS(y(center))) * COS(RADIANS({$lat}))\n\t\t\t * COS(RADIANS(x(center) - {$lng}))) * 6371.0) < {$dist}\n\t\torder by distance\n\t\tLIMIT 1\n\t\t";
$zend_db = Database::getConnection();
$result = $zend_db->query($sql)->execute([$level]);
if (count($result)) {
$row = $result->current();
return $row['id'];
} else {
$cluster = new GeoCluster();
$cluster->setLevel($level);
$cluster->setLatitude($lat);
$cluster->setLongitude($lng);
$cluster->save();
return $cluster->getId();
}
}
/**
* Transfers all data from a person, then deletes that person
*
* This person will end up containing all information from both people
* I took care to make sure to update the search index as well
* as the database.
*
* @param Person $person
*/
public function mergeFrom(Person $person)
{
if ($this->getId() && $person->getId()) {
if ($this->getId() == $person->getId()) {
// can not merge same person throw exception
throw new \Exception('mergerNotAllowed');
}
$zend_db = Database::getConnection();
// Look up all the tickets we're about to modify
// We need to remember them so we can update the search
// index after we've updated the database
$id = (int) $person->getId();
$sql = "select distinct t.id from tickets t\n\t\t\t\t\tleft join ticketHistory th on t.id=th.ticket_id\n\t\t\t\t\tleft join issues i on t.id= i.ticket_id\n\t\t\t\t\tleft join issueHistory ih on i.id=ih.issue_id\n\t\t\t\t\tleft join media m on i.id= m.issue_id\n\t\t\t\t\tleft join responses r on i.id= r.issue_id\n\t\t\t\t\twhere ( t.enteredByPerson_id={$id} or t.assignedPerson_id={$id} or t.referredPerson_id={$id})\n\t\t\t\t\t or (th.enteredByPerson_id={$id} or th.actionPerson_id={$id})\n\t\t\t\t\t or ( i.enteredByPerson_id={$id} or i.reportedByPerson_id={$id})\n\t\t\t\t\t or (ih.enteredByPerson_id={$id} or ih.actionPerson_id={$id})\n\t\t\t\t\t or m.person_id={$id} or r.person_id={$id}";
$result = $zend_db->query($sql)->execute();
$ticketIds = [];
foreach ($result as $row) {
$ticketIds[] = $row['id'];
}
$zend_db->getDriver()->getConnection()->beginTransaction();
try {
// These are all the database fields that hit the Solr index
$zend_db->query('update responses set person_id=? where person_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update media set person_id=? where person_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update issueHistory set enteredByPerson_id=? where enteredByPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update issueHistory set actionPerson_id=? where actionPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update issues set enteredByPerson_id=? where enteredByPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update issues set reportedByPerson_id=? where reportedByPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update ticketHistory set enteredByPerson_id=? where enteredByPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update ticketHistory set actionPerson_id=? where actionPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update tickets set enteredByPerson_id=? where enteredByPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update tickets set assignedPerson_id=? where assignedPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update tickets set referredPerson_id=? where referredPerson_id=?')->execute([$this->getId(), $person->getId()]);
// Fields that don't hit the Solr index
$zend_db->query('update clients set contactPerson_id=? where contactPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update departments set defaultPerson_id=? where defaultPerson_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update peopleAddresses set person_id=? where person_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update peoplePhones set person_id=? where person_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('update peopleEmails set person_id=? where person_id=?')->execute([$this->getId(), $person->getId()]);
$zend_db->query('delete from people where id=?')->execute([$person->getId()]);
} catch (Exception $e) {
$zend_db->getDriver()->getConnection()->rollback();
throw $e;
}
$zend_db->getDriver()->getConnection()->commit();
foreach ($ticketIds as $id) {
$search = new Search();
$ticket = new Ticket($id);
$search->add($ticket);
}
}
}
/**
* Does all the database work for TicketController::add
*
* Saves the ticket, the issue, and creates history entries
* for the open and assignment actions.
*
* This function calls save() as needed. After using this function,
* there's no need to make an additional save() call.
*
* @param array $post
*/
public function handleAdd($post)
{
$zend_db = Database::getConnection();
$zend_db->getDriver()->getConnection()->beginTransaction();
try {
$this->handleUpdate($post);
// We must add an issue to the ticket for validation to pass
$issue = new Issue();
$issue->handleUpdate($post);
$this->issues = array($issue);
if (!$this->getEnteredByPerson_id() && $issue->getReportedByPerson_id()) {
$this->setEnteredByPerson_id($issue->getReportedByPerson_id());
}
$this->save();
$issue->setTicket($this);
$issue->save();
// Create the entry in the history log
$history = new TicketHistory();
$history->setTicket($this);
$history->setAction(new Action('open'));
if ($this->getEnteredByPerson_id()) {
$history->setEnteredByPerson_id($this->getEnteredByPerson_id());
}
$history->save();
$history = new TicketHistory();
$history->setTicket($this);
$history->setAction(new Action('assignment'));
$history->setActionPerson_id($this->getAssignedPerson_id());
if (!empty($post['notes'])) {
$history->setNotes($post['notes']);
}
if ($this->getEnteredByPerson_id()) {
$history->setEnteredByPerson_id($this->getEnteredByPerson_id());
}
$history->save();
$this->getCategory()->onTicketAdd($this);
} catch (\Exception $e) {
$zend_db->getDriver()->getConnection()->rollback();
$search = new Search();
$search->delete($this);
$search->solrClient->commit();
throw $e;
}
$zend_db->getDriver()->getConnection()->commit();
$history->sendNotification($this);
}
/**
* Returns the most recent lastModified date from all categories
*
* @param string $format
* @param DateTimeZone $timezone
* @return string
*/
public static function getGlobalLastModifiedDate($format = null, \DateTimeZone $timezone = null)
{
$zend_db = Database::getConnection();
$result = $zend_db->query('select max(lastModified) as lastModified from categories')->execute();
$row = $result->current();
if ($format) {
$date = new \DateTime($row['lastModified']);
if ($timezone) {
$date->setTimezone($timezone);
}
return $date->format($format);
} else {
return $row['lastModified'];
}
}
public function delete()
{
$person = $this->getPerson();
parent::delete();
// If we delete the only email used for notifications,
// we need to mark one of the other email addresses.
$zend_db = Database::getConnection();
$result = $zend_db->query('select count(*) as c from peopleEmails where usedForNotifications=1 and person_id=?')->execute([$person->getId()]);
$row = $result->current();
if (!$row['c']) {
$list = $person->getEmails();
if (count($list)) {
$e = $list->current();
$e->setUsedForNotifications(true);
$e->save();
}
}
}