FastFrame: Ticket.php Source File

00001 <?php
00003 // {{{ license
00004 
00005 // +----------------------------------------------------------------------+
00006 // | FastFrame Application Framework                                      |
00007 // +----------------------------------------------------------------------+
00008 // | Copyright (c) 2002-2006 The Codejanitor Group                        |
00009 // +----------------------------------------------------------------------+
00010 // | This source file is subject to the GNU Lesser Public License (LGPL), |
00011 // | that is bundled with this package in the file LICENSE, and is        |
00012 // | available at through the world-wide-web at                           |
00013 // | http://www.fsf.org/copyleft/lesser.html                              |
00014 // | If you did not receive a copy of the LGPL and are unable to          |
00015 // | obtain it through the world-wide-web, you can get it by writing the  |
00016 // | Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, |
00017 // | MA 02111-1307, USA.                                                  |
00018 // +----------------------------------------------------------------------+
00019 // | Authors: Jason Rust <jrust@codejanitor.com>                          |
00020 // +----------------------------------------------------------------------+
00021 
00022 // }}}
00023 // {{{ requires
00024 
00025 require_once FASTFRAME_ROOT . 'lib/FastFrame/DataAccess.php';
00026 
00027 // }}}
00028 // {{{ constants
00029 
00030 // The filters for the list page
00031 define('TICKETS_FILTER_ALL', 1);
00032 
00033 // }}}
00034 // {{{ class FF_DataAccess_Ticket_mysql 
00035 
00046 // }}}
00047 class FF_DataAccess_Ticket_mysql extends FF_DataAccess {
00048     // {{{ properties
00049 
00054     var $emptySearch = false;
00055 
00060     var $fullText = false;
00061 
00062     // }}}
00063     // {{{ constructor
00064 
00071     function FF_DataAccess_Ticket_mysql()
00072     {
00073         FF_DataAccess::FF_DataAccess();
00074         $this->table = $this->o_registry->getConfigParam('data/tickets_table');
00075     }
00076 
00077     // }}}
00078     // {{{ update()
00079 
00088     function update($in_data)
00089     {
00090         $in_data['due_date'] = $this->timestampToISODate($in_data['due_date']);
00091         $in_data['last_updated'] = $this->timestampToISODate(time());
00092         if (empty($in_data['create_date'])) {
00093             unset($in_data['create_date']);
00094         }
00095         else {
00096             $in_data['create_date'] = $this->timestampToISODate($in_data['create_date']);
00097         }
00098 
00099         unset($in_data['num_attachments']);
00100         unset($in_data['reporter_name']);
00101         unset($in_data['reporter_phone']);
00102         unset($in_data['reporter_dept']);
00103         unset($in_data['reporter_email']);
00104         unset($in_data['extra_values']);
00105         $o_result = parent::update($in_data);
00106         if ($o_result->isSuccess()) {
00107             $this->updateFullText($in_data['id']);
00108         }
00109 
00110         return $o_result;
00111     }
00112 
00113     // }}}
00114     // {{{ add()
00115 
00124     function add($in_data)
00125     {
00126         $in_data['create_date'] = $in_data['last_updated'] = $in_data['last_comment_update'] = $this->timestampToISODate(time());
00127         $in_data['due_date'] = $this->timestampToISODate($in_data['due_date']);
00128         unset($in_data['num_attachments']);
00129         unset($in_data['reporter_name']);
00130         unset($in_data['reporter_phone']);
00131         unset($in_data['reporter_dept']);
00132         unset($in_data['reporter_email']);
00133         unset($in_data['extra_values']);
00134         $o_result = parent::add($in_data);
00135         if ($o_result->isSuccess()) {
00136             $this->updateFullText($in_data['id']);
00137         }
00138 
00139         return $o_result;
00140     }
00141 
00142     // }}}
00143     // {{{ getDataByPrimaryKey()
00144 
00154     function getDataByPrimaryKey($in_id, $in_fields = '*')
00155     {
00156         $s_query = $this->_getSelectQuery() . 'WHERE t.id = ' . $this->o_data->quoteSmart($in_id);
00157         $a_result = $this->o_data->getAll($s_query);
00158         return @$a_result[0];
00159     }
00160 
00161     // }}}
00162     // {{{ getListData()
00163 
00175     function getListData($in_where, $in_orderByField, $in_orderByDir, $in_fields = '*')
00176     {
00177         if (!$this->emptySearch) {
00178             $s_extra = 'l.name AS location_name';
00179             if ($this->fullText) {
00180                 if ($in_orderByField == 'score') {
00181                     $in_orderByDir = 'DESC';
00182                 }
00183 
00184                 $s_extra .= ", $this->fullText AS score";
00185             }
00186 
00187             $s_query = $this->_getSelectQuery(null, $s_extra);
00188             $s_locTable = $this->o_registry->getConfigParam('data/locations_table');
00189             // Add the ability to search locations
00190             $s_query .= "LEFT JOIN $s_locTable AS l ON t.location_id = l.id
00191                          WHERE $in_where GROUP BY t.id";
00192         }
00193         else {
00194             $s_query = $this->_getSelectQuery();
00195             $s_query .= "WHERE $in_where";
00196         }
00197 
00198         $s_query .= " ORDER BY $in_orderByField " .  $this->_getOrderByDirection($in_orderByDir);
00199 
00200         return $this->o_data->query($s_query);
00201     }
00202 
00203     // }}}
00204     // {{{ getSummaryData()
00205 
00216     function getSummaryData(&$in_blockObj, $in_sortField, $in_sortOrder)
00217     {
00218         $s_statusTable = $this->o_registry->getConfigParam('data/statuses_table');
00219         $s_profileTable = $this->o_registry->getConfigParam('data/table', 'profile', 'profile');
00220         $s_notifyTable = $this->o_registry->getConfigParam('data/ticket_comment_notification_table');
00221         $s_catTable = $this->o_registry->getConfigParam('data/categories_table');
00222         if (is_a($in_blockObj, 'FF_Model_Filter')) {
00223             $s_where = $this->buildFilter($in_blockObj);
00224         }
00225         else {
00226             $s_where = sprintf('category_id = %s', $this->o_data->quoteSmart($in_blockObj->getId()));
00227         }
00228 
00229         $s_query = $this->_getSelectQuery('t.id, UNIX_TIMESTAMP(t.last_comment_update) AS last_comment_update, t.summary, t.points, t.description, t.handler_id, t.category_id, t.status_id, t.num_attachments') . 
00230             " WHERE $s_where ORDER BY $in_sortField " .
00231             $this->_getOrderByDirection($in_sortOrder);
00232         return $this->o_data->query($s_query);
00233     }
00234 
00235     // }}}
00236     // {{{ getNavigationIds()
00237 
00248     function getNavigationIds($in_id, &$in_blockObj)
00249     {
00250         if (is_a($in_blockObj, 'FF_Model_Filter')) {
00251             $s_where = $this->buildFilter($in_blockObj);
00252             $s_orderField = $in_blockObj->getSortField();
00253             $s_orderDir = $this->_getOrderByDirection($in_blockObj->getSortOrder());
00254         }
00255         else {
00256             $s_where = sprintf('category_id = %s', $this->o_data->quoteSmart($in_blockObj->getId()));
00257             $s_orderField = 'points';
00258             $s_orderDir = 'DESC';
00259         }
00260 
00261         $s_statusTable = $this->o_registry->getConfigParam('data/statuses_table');
00262         $s_profileTable = $this->o_registry->getConfigParam('data/table', 'profile', 'profile');
00263         $s_notifyTable = $this->o_registry->getConfigParam('data/ticket_comment_notification_table');
00264         $s_userId = FF_Auth::getCredential('userId');
00265         $s_userId = empty($s_userId) ? 0 : (int) $s_userId;
00266         // Have to join the tables that could be used in a filter
00267         $s_query = "SELECT t.id, (n.view_time IS NULL OR t.last_comment_update > n.view_time), s.name AS status_name
00268                     FROM $this->table AS t
00269                     LEFT JOIN $s_profileTable AS p2 ON t.reporter_id = p2.id
00270                     LEFT JOIN $s_notifyTable AS n ON n.ticket_id = t.id AND n.profile_id = $s_userId
00271                     LEFT JOIN $s_statusTable AS s ON t.status_id = s.id 
00272                     WHERE $s_where ORDER BY $s_orderField $s_orderDir";
00273 
00274         if (PEAR::isError(($o_result = $this->o_data->query($s_query)))) {
00275             return array();
00276         }
00277 
00278         $s_prevId = null;
00279         $s_nextId = null;
00280         $s_nextUnread = null;
00281         $s_prevUnread = null;
00282         $b_getNext = false;
00283         $b_updatePrev = true;
00284         $b_getUnread = false;
00285         while ($a_row = $o_result->fetchRow(DB_FETCHMODE_ORDERED)) {
00286             if ($b_getNext) {
00287                 $s_nextId = $a_row[0];
00288                 $b_getUnread = true;
00289                 $b_getNext = false;
00290             }
00291 
00292             if ($b_getUnread) {
00293                 if ($a_row[1]) {
00294                     $s_nextUnread = $a_row[0];
00295                     break;
00296                 }
00297 
00298                 continue;
00299             }
00300 
00301             if ($a_row[0] == $in_id) {
00302                 $b_getNext = true;
00303                 $b_updatePrev = false;
00304             }
00305 
00306             if ($b_updatePrev) {
00307                 $s_prevId = $a_row[0];
00308                 if ($a_row[1]) {
00309                     $s_prevUnread = $a_row[0];
00310                 }
00311             }
00312         }
00313 
00314         // If we couldn't find the id at all, unset prev
00315         if ($b_updatePrev) {
00316             $s_prevUnread = null;
00317             $s_prevId = null;
00318         }
00319 
00320         if (is_null($s_prevId) && is_null($s_nextId) && is_null($s_prevUnread) && is_null($s_nextUnread)) {
00321             return array();
00322         }
00323         else {
00324             return array($s_prevUnread, $s_prevId, $s_nextId, $s_nextUnread);
00325         }
00326     }
00327 
00328     // }}}
00329     // {{{ getListFilter()
00330 
00343     function getListFilter($in_searchString, $in_searchFields, $in_filter, $in_filterData)
00344     {
00345         static $s_filter;
00346 
00347         if (!is_null($s_filter)) {
00348             return $s_filter;
00349         }
00350 
00351         $this->emptySearch = FastFrame::isEmpty($in_searchString);
00352         $s_filter = '';
00353         if (!empty($in_filterData['category_id']) && $in_filterData['category_id'] != '__all') {
00354             $s_filter .= sprintf('category_id = %s AND ', $this->o_data->quoteSmart($in_filterData['category_id']));
00355         }
00356 
00357         if (!empty($in_filterData['filter_id'])) {
00358             require_once dirname(__FILE__) . '/../../Model/Filter.php';
00359             $o_filter =& new FF_Model_Filter();
00360             if ($o_filter->fillById($in_filterData['filter_id'])) {
00361                 $s_filter .= '(' . $this->buildFilter($o_filter) . ') AND ';
00362             }
00363         }
00364 
00365         $s_filter .= '(' . parent::getListFilter($in_searchString, $in_searchFields, $in_filter, $in_filterData);
00366 
00367         // Add full text search when searching all fields
00368         if (!$this->emptySearch && count($in_searchFields) > 1) {
00369             $this->fullText = 'MATCH(`search_body`) AGAINST(' . $this->o_data->quoteSmart($in_searchString) . ' IN BOOLEAN MODE)';
00370             $s_filter .= " OR $this->fullText";
00371         }
00372 
00373         $s_filter .= ')';
00374 
00375         // Because WHERE clauses don't support aliases, we have to replace it with the literal expression
00376         // We only use a % at the end of the name to make the query speedier
00377         // Remove date searches
00378         $s_filter = preg_replace('/`handler_name` BETWEEN .*? OR/', '', $s_filter);
00379         $s_filter = preg_replace('/`reporter_name` BETWEEN .*? OR/', '', $s_filter);
00380         $s_filter = preg_replace('/`handler_name` LIKE \'%(.*?)%\'/', 
00381                 'p.firstname LIKE \'\\1%\' OR p.lastname LIKE \'\\1%\'', $s_filter);
00382         $s_filter = preg_replace('/`reporter_name` LIKE \'%(.*?)%\'/', 
00383                 'p2.firstname LIKE \'\\1%\' OR p2.lastname LIKE \'\\1%\'', $s_filter);
00384         $s_filter = str_replace('`category_name`', 'c.name', $s_filter);
00385         $s_filter = str_replace('`create_date`', 't.create_date', $s_filter);
00386         $s_filter = str_replace('`status_name`', 's.name', $s_filter);
00387         $s_filter = str_replace('`location_name`', 'l.name', $s_filter);
00388 
00389         return $s_filter;
00390     }
00391 
00392     // }}}
00393     // {{{ getTicketsMatchingFilter()
00394 
00403     function getTicketsMatchingFilter(&$in_filter)
00404     {
00405         $s_profileTable = $this->o_registry->getConfigParam('data/table', 'profile', 'profile');
00406         $s_query = "SELECT t.id FROM $this->table AS t 
00407                     LEFT JOIN $s_profileTable AS p2 ON t.reporter_id=p2.id 
00408                     WHERE " . $this->buildFilter($in_filter);
00409 
00410         return $this->o_data->getCol($s_query, 0);
00411     }
00412 
00413     // }}}
00414     // {{{ getNextId()
00415 
00422     function getNextId()
00423     {
00424         // change sequence name since we give it the full table name
00425         $this->o_data->setOption('seqname_format', '%s');
00426         return $this->o_data->nextId($this->o_registry->getConfigParam('data/sequence_table'));
00427     }
00428 
00429     // }}}
00430     // {{{ changePoints()
00431 
00441     function changePoints($in_id, $in_points)
00442     {
00443         return $this->o_data->autoExecute($this->table, 
00444                 array('points' => $in_points, 'last_action' => ACTION_EDIT_SUBMIT), 
00445                 DB_AUTOQUERY_UPDATE, 'id = ' . $this->o_data->quoteSmart($in_id));
00446     }
00447 
00448     // }}}
00449     // {{{ changeHandler()
00450 
00460     function changeHandler($in_id, $in_userId)
00461     {
00462         return $this->o_data->autoExecute($this->table, 
00463                 array('handler_id' => $in_userId, 'last_action' => ACTION_EDIT_SUBMIT), 
00464                 DB_AUTOQUERY_UPDATE, 'id = ' . $this->o_data->quoteSmart($in_id));
00465     }
00466 
00467     // }}}
00468     // {{{ buildFilter()
00469 
00478     function buildFilter(&$in_filter)
00479     {
00480         $s_filter = '';
00481         $s_op = $in_filter->getOperator() == FILTER_OR ? ' OR ' : ' AND ';
00482         // {{{ text matching
00483         $a_options = array(
00484                 'summary' => 'getSummary',
00485                 'description' => 'getDescription',
00486                 'p2.username' => 'getReporter');
00487         foreach ($a_options as $s_key => $s_method) {
00488             if (!FastFrame::isEmpty($in_filter->$s_method())) {
00489                 $tmp_method = $s_method . 'Type';
00490                 switch($in_filter->$tmp_method()) {
00491                     case TEXT_EQUALS:
00492                         $s_text = ' LIKE ' . $this->o_data->quoteSmart($in_filter->$s_method());
00493                     break;
00494                     case TEXT_BEGINS_WITH:
00495                         $s_text = ' LIKE ' . $this->o_data->quoteSmart($in_filter->$s_method() . '%');
00496                     break;
00497                     case TEXT_ENDS_WITH:
00498                         $s_text = ' LIKE ' . $this->o_data->quoteSmart('%' . $in_filter->$s_method());
00499                     break;
00500                     case TEXT_MATCHES:
00501                         $s_text = ' REGEXP ' . $this->o_data->quoteSmart($in_filter->$s_method());
00502                     break;
00503                     case TEXT_CONTAINS:
00504                     default:
00505                         $s_text = ' LIKE ' . $this->o_data->quoteSmart('%' . $in_filter->$s_method() . '%');
00506                     break;
00507                 }
00508 
00509                 $tmp_method = $s_method . 'Operator';
00510                 $tmp_op = !$in_filter->$tmp_method() ? ' NOT ' : '';
00511                 $s_filter .= $s_key . $tmp_op . $s_text . $s_op . "\n";
00512 
00513             }
00514         }
00515 
00516         // }}}
00517         // {{{ date matching
00518         $a_options = array(
00519                 'last_updated' => 'getLastUpdated',
00520                 'create_date' => 'getCreateDate',
00521                 'due_date' => 'getDueDate');
00522         foreach ($a_options as $s_key => $s_method) {
00523             $tmp_method = $s_method . 'Num';
00524             $s_num = $in_filter->$tmp_method();
00525             if (!empty($s_num)) {
00526                 $tmp_method = $s_method . 'Operator';
00527                 if ($s_key == 'due_date') {
00528                     $tmp_func = 'DATE_ADD';
00529                     $tmp_op = $in_filter->$tmp_method() == DATE_LESS ? '<' : '>';
00530                 }
00531                 else {
00532                     $tmp_func = 'DATE_SUB';
00533                     $tmp_op = $in_filter->$tmp_method() == DATE_LESS ? '>' : '<';
00534                 }
00535 
00536                 $tmp_method = $s_method . 'Type';
00537                 switch ($in_filter->$tmp_method()) {
00538                     case DATE_MONTHS:
00539                         $s_type = 'MONTH';
00540                     break;
00541                     case DATE_MINUTES:
00542                         $s_type = 'MINUTE';
00543                     break;
00544                     case DATE_HOURS:
00545                         $s_type = 'HOUR';
00546                     break;
00547                     case DATE_DAYS:
00548                     default:
00549                         $s_type = 'DAY';
00550                     break;
00551                 }
00552 
00553                 $s_filter .= "($s_key != 0 AND $s_key $tmp_op $tmp_func(NOW(), INTERVAL $s_num $s_type)) $s_op\n";
00554             }
00555         }
00556 
00557         // }}}
00558         // {{{ multiple option matching
00559         $a_options = array(
00560                 'resolution_id' => 'getResolutions',
00561                 'location_id' => 'getLocations',
00562                 'category_id' => 'getCategories',
00563                 'status_id' => 'getStatuses',
00564                 'handler_id' => 'getHandlers',
00565                 'points' => 'getPoints',
00566                 'last_action' => 'getLastAction');
00567         foreach ($a_options as $s_field => $s_method) {
00568             if (count($a_vals = $in_filter->$s_method()) > 0) {
00569                 $s_filter .= '(';
00570                 foreach ($a_vals as $s_val) {
00571                     if ($s_field == 'points') {
00572                         list($tmp_begin, $tmp_end) = explode('-', $s_val);
00573                         $s_filter .= "($s_field >= " . $this->o_data->quoteSmart($tmp_begin) . 
00574                                      " AND $s_field <= " . $this->o_data->quoteSmart($tmp_end) . ') OR ';
00575                     }
00576                     else {
00577                         $s_filter .= $s_field . '=' . $this->o_data->quoteSmart($s_val) . ' OR ';
00578                     }
00579                 }
00580 
00581                 $s_filter .= "0=1) $s_op\n";
00582             }
00583         }
00584 
00585         // }}}
00586         // {{{ new comments matching
00587         
00588         if ($in_filter->getNewComments() == COMMENTS_NEW) {
00589             $s_filter .= '(n.view_time IS NULL OR last_comment_update > n.view_time)' . $s_op;
00590         }
00591         elseif ($in_filter->getNewComments() == COMMENTS_NOT_NEW) {
00592             $s_filter .= '(n.view_time IS NOT NULL AND n.view_time > last_comment_update)' . $s_op;
00593         }
00594 
00595         // }}}
00596         $s_filter .= $in_filter->getOperator() == FILTER_OR ? '0=1' : '1=1';
00597         return $s_filter;
00598     }
00599 
00600     // }}}
00601     // {{{ resetLastAction()
00602 
00611     function resetLastAction($in_ids)
00612     {
00613         return $this->o_data->autoExecute($this->table, array('last_action' => ''), 
00614                 DB_AUTOQUERY_UPDATE, 'id = ' . implode(' OR id = ', $in_ids));
00615     }
00616 
00617     // }}}
00618     // {{{ updateFullText()
00619 
00631     function updateFullText($in_ticketId)
00632     {
00633         $s_sql = "SELECT summary, description FROM $this->table WHERE id = ?";
00634         $tmp_data = $this->o_data->getAll($s_sql, array($in_ticketId));
00635         $a_ticketData = array(0 => $tmp_data[0]['summary'], 1 => $tmp_data[0]['description']);
00636 
00637         $s_comTable = $this->o_registry->getConfigParam('data/ticket_comments_table');
00638         $s_sql = "SELECT comment FROM $s_comTable WHERE ticket_id = ?";
00639         $a_ticketData = array_merge($a_ticketData, $this->o_data->getCol($s_sql, 0, $in_ticketId));
00640 
00641         // Get rid of punctuation so it doesn't mess up search queries
00642         foreach (array_keys($a_ticketData) as $s_key) {
00643             $a_ticketData[$s_key] = preg_replace('/[[:punct:]]/', '', $a_ticketData[$s_key]);
00644         }
00645 
00646         $s_sql = "UPDATE $this->table SET search_body=? WHERE id=?";
00647         $this->o_data->query($s_sql, array(implode("\n", $a_ticketData), $in_ticketId));
00648     }
00649 
00650     // }}}
00651     // {{{ updateCommentTime()
00652 
00662     function updateCommentTime($in_id)
00663     { 
00664         $s_stmt = $this->o_data->prepare("UPDATE $this->table SET last_updated = NOW(), last_comment_update = NOW() WHERE id = ?");
00665         $this->o_data->execute($s_stmt, array($in_id));
00666     }
00667 
00668     // }}}
00669     // {{{ updateNumAttachments()
00670 
00671     function updateNumAttachments($in_ticketId, $in_num) 
00672     {
00673         $s_sql = "UPDATE $this->table SET num_attachments=? WHERE id=?";
00674         $this->o_data->query($s_sql, array($in_num, $in_ticketId));
00675     }
00676 
00677     // }}}
00678     // {{{ _getSelectQuery()
00679 
00690     function _getSelectQuery($in_fields = null, $in_extra = null)
00691     {
00692         $s_statusTable = $this->o_registry->getConfigParam('data/statuses_table');
00693         $s_profileTable = $this->o_registry->getConfigParam('data/table', 'profile', 'profile');
00694         $s_notifyTable = $this->o_registry->getConfigParam('data/ticket_comment_notification_table');
00695         $s_catTable = $this->o_registry->getConfigParam('data/categories_table');
00696         $in_fields = empty($in_fields) ?  't.id, UNIX_TIMESTAMP(t.create_date) AS create_date, UNIX_TIMESTAMP(t.last_updated) AS last_updated, UNIX_TIMESTAMP(last_comment_update) AS last_comment_update, t.handler_id, t.reporter_id, t.location_id, t.status_id, t.resolution_id, t.category_id, t.summary, t.description, t.points, t.last_action, UNIX_TIMESTAMP(t.due_date) as due_date, t.progress, t.num_attachments' : $in_fields;
00697         $in_fields = is_null($in_extra) ? $in_fields : $in_fields . ', ' . $in_extra;
00698         $s_userId = FF_Auth::getCredential('userId');
00699         $s_userId = empty($s_userId) ? 0 : (int) $s_userId;
00700         $s_unknown = $this->o_data->quoteSmart(_('Unknown User'));
00701         return "SELECT $in_fields, s.name AS status_name, s.color AS status_color, c.name AS category_name, 
00702                   t.location_id, UNIX_TIMESTAMP(t.create_date) AS create_date, t.reporter_id,
00703                   IF(p2.id IS NULL, $s_unknown, CONCAT(p.firstname, ' ', p.lastname)) AS handler_name,
00704                   IF(p2.id IS NULL, $s_unknown, CONCAT(p2.firstname, ' ', p2.lastname)) AS reporter_name,
00705                   p2.phone AS reporter_phone, p2.department AS reporter_dept, p2.email AS reporter_email,
00706                   UNIX_TIMESTAMP(n.view_time) AS last_view_time
00707                 FROM $this->table AS t
00708                 LEFT JOIN $s_statusTable AS s ON t.status_id = s.id 
00709                 LEFT JOIN $s_catTable AS c ON t.category_id = c.id
00710                 LEFT JOIN $s_profileTable AS p ON t.handler_id = p.id
00711                 LEFT JOIN $s_profileTable AS p2 ON t.reporter_id = p2.id
00712                 LEFT JOIN $s_notifyTable AS n ON n.ticket_id = t.id AND n.profile_id = $s_userId ";
00713     }
00714 
00715     // }}}
00716 }
00717 ?>
DataAccess/mysql/Ticket.php
