예제 #1
0
 /**
  * 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');
     }
 }
예제 #3
0
 /**
  * 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']);
         }
     }
 }
예제 #4
0
 /**
  * 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();
     }
 }
예제 #5
0
 public function save()
 {
     if ($this->isDefault()) {
         $zend_db = Database::getConnection();
         $zend_db->query('update substatus set isDefault=0 where status=?', $this->getStatus());
     }
     parent::save();
 }
예제 #6
0
 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}"]);
     }
 }
예제 #7
0
 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']);
 }
예제 #8
0
 /**
  * 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
     }
 }
예제 #9
0
 /**
  * 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());
     }
 }
예제 #10
0
 /**
  * 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');
     }
 }
예제 #11
0
 /**
  * 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); }
     }
 }
예제 #12
0
 /**
  * @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');
     }
 }
예제 #13
0
<?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";
예제 #14
0
 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);
 }
예제 #16
0
 /**
  * @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;
 }
예제 #17
0
 /**
  * @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;
 }
예제 #18
0
 /**
  * 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();
     }
 }
예제 #19
0
 /**
  * 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);
         }
     }
 }
예제 #20
0
 /**
  * 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);
 }
예제 #21
0
 /**
  * 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'];
     }
 }
예제 #22
0
 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();
         }
     }
 }