ON (status.id = ticket.status_id) ' . ' LEFT JOIN ' . TABLE_PREFIX . 'ticket__cdata cdata ON (cdata.ticket_id = ticket.ticket_id)' . ' LEFT JOIN ' . DEPT_TABLE . ' dept ON (ticket.dept_id=dept.dept_id) ' . ' LEFT JOIN ' . TICKET_COLLABORATOR_TABLE . ' collab ON (collab.ticket_id = ticket.ticket_id AND collab.user_id =' . $thisclient->getId() . ' )'; $qwhere = sprintf(' WHERE ( ticket.user_id=%d OR collab.user_id=%d )', $thisclient->getId(), $thisclient->getId()); $states = array('open' => 'open', 'closed' => 'closed'); if ($status && isset($states[$status])) { $qwhere .= ' AND status.state=' . db_input($states[$status]); } $search = $_REQUEST['a'] == 'search' && $_REQUEST['q']; if ($search) { $qs += array('a' => $_REQUEST['a'], 'q' => $_REQUEST['q']); if (is_numeric($_REQUEST['q'])) { $qwhere .= " AND ticket.`number` LIKE '{$queryterm}%'"; } else { //Deep search! $queryterm = db_real_escape($_REQUEST['q'], false); //escape the term ONLY...no quotes. $qwhere .= ' AND ( ' . " cdata.subject LIKE '%{$queryterm}%'" . " OR thread.body LIKE '%{$queryterm}%'" . ' ) '; $deep_search = true; //Joins needed for search $qfrom .= ' LEFT JOIN ' . TICKET_THREAD_TABLE . ' thread ON (' . 'ticket.ticket_id=thread.ticket_id AND thread.thread_type IN ("M","R"))'; } } TicketForm::ensureDynamicDataView(); $total = db_count('SELECT count(DISTINCT ticket.ticket_id) ' . $qfrom . ' ' . $qwhere); $page = $_GET['p'] && is_numeric($_GET['p']) ? $_GET['p'] : 1; $pageNav = new Pagenate($total, $page, PAGE_LIMIT); $qstr = '&' . Http::build_query($qs); $qs += array('sort' => $_REQUEST['sort'], 'order' => $_REQUEST['order']); $pageNav->setURL('tickets.php', $qs); //more stuff...
Released under the GNU General Public License WITHOUT ANY WARRANTY. See LICENSE.TXT for details. vim: expandtab sw=4 ts=4 sts=4: $Id: $ **********************************************************************/ require 'staff.inc.php'; $nav->setTabActive('directory'); $nav->addSubMenu(array('desc' => 'Staff Members', 'href' => 'directory.php', 'iconclass' => 'staff')); $WHERE = ' WHERE isvisible=1 '; $sql = ' SELECT staff.staff_id,staff.dept_id, firstname,lastname,email,phone,phone_ext,mobile,dept_name,onvacation ' . ' FROM ' . STAFF_TABLE . ' staff LEFT JOIN ' . DEPT_TABLE . ' USING(dept_id)'; if ($_POST && $_POST['a'] == 'search') { $searchTerm = $_POST['query']; if ($searchTerm) { $query = db_real_escape($searchTerm, false); //escape the term ONLY...no quotes. if (is_numeric($searchTerm)) { $WHERE .= " AND staff.phone LIKE '%{$query}%'"; } elseif (strpos($searchTerm, '@') && Validator::is_email($searchTerm)) { $WHERE .= " AND staff.email='{$query}'"; } else { $WHERE .= " AND ( staff.email LIKE '%{$query}%'" . " OR staff.lastname LIKE '%{$query}%'" . " OR staff.firstname LIKE '%{$query}%'" . ' ) '; } } if ($_POST['dept'] && is_numeric($_POST['dept'])) { $WHERE .= ' AND staff.dept_id=' . db_input($_POST['dept']); } } $users = db_query("{$sql} {$WHERE} ORDER BY lastname,firstname"); //Render the page.
$where = ''; //make sure the search query is 3 chars min...defaults to no query with warning message if ($_REQUEST['a'] == 'search') { if (!$_REQUEST['query'] || strlen($_REQUEST['query']) < 3) { $errors['err'] = 'Search term must be more than 3 chars'; } else { //Do the search $search = true; $qstr .= '&a=' . urlencode($_REQUEST['a']); $qstr .= '&query=' . urlencode($_REQUEST['query']); $searchTerm = trim($_REQUEST['query']); if (strpos($searchTerm, '@') && Validator::is_email($searchTerm)) { //pulling all tricks! $where = ' WHERE email=' . db_input($searchTerm); } else { $where = ' WHERE email LIKE \'%' . db_real_escape($searchTerm, false) . '%\''; } } } //I admit this crap sucks...but who cares?? $sortOptions = array('date' => 'added', 'email' => 'email'); $orderWays = array('DESC' => 'DESC', 'ASC' => 'ASC'); //Sorting options... if ($_REQUEST['sort']) { $order_column = $sortOptions[$_REQUEST['sort']]; } if ($_REQUEST['order']) { $order = $orderWays[$_REQUEST['order']]; } $order_column = $order_column ? $order_column : 'added'; $order = $order ? $order : 'DESC';
*/ if(!($cfg->showAssignedTickets() || $thisstaff->showAssignedTickets())) { $qwhere.=' AND ticket.staff_id=0 '; //XXX: NOT factoring in team assignments - only staff assignments. $showassigned=false; //Not showing Assigned To column since assigned tickets are not part of open queue } } //Search?? Somebody...get me some coffee $deep_search=false; $order_by=$order=null; if($search): $qs += array('a' => $_REQUEST['a'], 't' => $_REQUEST['t']); //query if($searchTerm){ $qs += array('query' => $searchTerm); $queryterm=db_real_escape($searchTerm,false); //escape the term ONLY...no quotes. if (is_numeric($searchTerm)) { $qwhere.=" AND ticket.`number` LIKE '$queryterm%'"; } elseif (strpos($searchTerm,'@') && Validator::is_email($searchTerm)) { //pulling all tricks! # XXX: What about searching for email addresses in the body of # the thread message $qwhere.=" AND email.address='$queryterm'"; } else {//Deep search! //This sucks..mass scan! search anything that moves! require_once(INCLUDE_DIR.'ajax.tickets.php'); $tickets = TicketsAjaxApi::_search(array('query'=>$queryterm)); if (count($tickets)) { $ticket_ids = implode(',',db_input($tickets)); $qwhere .= ' AND ticket.ticket_id IN ('.$ticket_ids.')';
function _search($req) { global $thisstaff, $cfg; $result = array(); $select = 'SELECT ticket.ticket_id'; $from = ' FROM ' . TICKET_TABLE . ' ticket '; //Access control. $where = ' WHERE ( (ticket.staff_id=' . db_input($thisstaff->getId()) . ' AND ticket.status="open" )'; if (($teams = $thisstaff->getTeams()) && count(array_filter($teams))) { $where .= ' OR (ticket.team_id IN (' . implode(',', db_input(array_filter($teams))) . ' ) AND ticket.status="open")'; } if (!$thisstaff->showAssignedOnly() && ($depts = $thisstaff->getDepts())) { $where .= ' OR ticket.dept_id IN (' . implode(',', db_input($depts)) . ')'; } $where .= ' ) '; //Department if ($req['deptId']) { $where .= ' AND ticket.dept_id=' . db_input($req['deptId']); } //Help topic if ($req['topicId']) { $where .= ' AND ticket.topic_id=' . db_input($req['topicId']); } //Status switch (strtolower($req['status'])) { case 'open': $where .= ' AND ticket.status="open" '; break; case 'answered': $where .= ' AND ticket.status="open" AND ticket.isanswered=1 '; break; case 'overdue': $where .= ' AND ticket.status="open" AND ticket.isoverdue=1 '; break; case 'closed': $where .= ' AND ticket.status="closed" '; break; } //Assignee if (isset($req['assignee']) && strcasecmp($req['status'], 'closed')) { $id = preg_replace("/[^0-9]/", "", $req['assignee']); $assignee = $req['assignee']; $where .= ' AND ( ( ticket.status="open" '; if ($assignee[0] == 't') { $where .= ' AND ticket.team_id=' . db_input($id); } elseif ($assignee[0] == 's') { $where .= ' AND ticket.staff_id=' . db_input($id); } elseif (is_numeric($id)) { $where .= ' AND ticket.staff_id=' . db_input($id); } $where .= ')'; if ($req['staffId'] && !$req['status']) { //Assigned TO + Closed By $where .= ' OR (ticket.staff_id=' . db_input($req['staffId']) . ' AND ticket.status="closed") '; } elseif (isset($req['staffId'])) { // closed by any $where .= ' OR ticket.status="closed" '; } $where .= ' ) '; } elseif ($req['staffId']) { $where .= ' AND (ticket.staff_id=' . db_input($req['staffId']) . ' AND ticket.status="closed") '; } //dates $startTime = $req['startDate'] && strlen($req['startDate']) >= 8 ? strtotime($req['startDate']) : 0; $endTime = $req['endDate'] && strlen($req['endDate']) >= 8 ? strtotime($req['endDate']) : 0; if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) { $startTime = $endTime = 0; } if ($startTime) { $where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')'; } if ($endTime) { $where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')'; } //Query $joins = array(); if ($req['query']) { $queryterm = db_real_escape($req['query'], false); // Setup sets of joins and queries $joins[] = array('from' => 'LEFT JOIN ' . TICKET_THREAD_TABLE . ' thread ON (ticket.ticket_id=thread.ticket_id )', 'where' => "thread.title LIKE '%{$queryterm}%' OR thread.body LIKE '%{$queryterm}%'"); $joins[] = array('from' => 'LEFT JOIN ' . FORM_ENTRY_TABLE . ' tentry ON (tentry.object_id = ticket.ticket_id AND tentry.object_type="T") LEFT JOIN ' . FORM_ANSWER_TABLE . ' tans ON (tans.entry_id = tentry.id AND tans.value_id IS NULL)', 'where' => "tans.value LIKE '%{$queryterm}%'"); $joins[] = array('from' => 'LEFT JOIN ' . FORM_ENTRY_TABLE . ' uentry ON (uentry.object_id = ticket.user_id AND uentry.object_type="U") LEFT JOIN ' . FORM_ANSWER_TABLE . ' uans ON (uans.entry_id = uentry.id AND uans.value_id IS NULL) LEFT JOIN ' . USER_TABLE . ' user ON (ticket.user_id = user.id) LEFT JOIN ' . USER_EMAIL_TABLE . ' uemail ON (user.id = uemail.user_id)', 'where' => "uemail.address LIKE '%{$queryterm}%' OR user.name LIKE '%{$queryterm}%' OR uans.value LIKE '%{$queryterm}%'"); } // Dynamic fields $cdata_search = false; foreach (TicketForm::getInstance()->getFields() as $f) { if (isset($req[$f->getFormName()]) && ($val = $req[$f->getFormName()])) { $name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id'); if ($f->getImpl()->hasIdValue() && is_numeric($val)) { $cwhere = "cdata.`{$name}_id` = " . db_input($val); } else { $cwhere = "cdata.`{$name}` LIKE '%" . db_real_escape($val) . "%'"; } $where .= ' AND (' . $cwhere . ')'; $cdata_search = true; } } if ($cdata_search) { $from .= 'LEFT JOIN ' . TABLE_PREFIX . 'ticket__cdata ' . " cdata ON (cdata.ticket_id = ticket.ticket_id)"; } $sections = array(); foreach ($joins as $j) { $sections[] = "{$select} {$from} {$j['from']} {$where} AND ({$j['where']})"; } if (!$joins) { $sections[] = "{$select} {$from} {$where}"; } $sql = implode(' union ', $sections); if (!($res = db_query($sql))) { return TicketForm::dropDynamicDataView(); } $tickets = array(); while ($row = db_fetch_row($res)) { $tickets[] = $row[0]; } return $tickets; }
function db_input($param, $quote = true) { //is_numeric doesn't work all the time...9e8 is considered numeric..which is correct...but not expected. if ($param && preg_match("/^\\d+(\\.\\d+)?\$/", $param)) { return $param; } if ($param && is_array($param)) { reset($param); while (list($key, $value) = each($s)) { $param[$key] = db_input($value, $quote); } return $param; } return db_real_escape($param, $quote); }
function db_input($var, $quote = true) { if (is_array($var)) { return array_map('db_input', $var, array_fill(0, count($var), $quote)); } elseif ($var && preg_match("/^(?:\\d+\\.\\d+|[1-9]\\d*)\$/S", $var)) { return $var; } return db_real_escape($var, $quote); }
function _search($req) { global $thisstaff, $cfg, $ost; $result = array(); $criteria = array(); $select = 'SELECT ticket.ticket_id'; $from = ' FROM ' . TICKET_TABLE . ' ticket LEFT JOIN ' . TICKET_STATUS_TABLE . ' status ON (status.id = ticket.status_id) '; //Access control. $where = ' WHERE ( (ticket.staff_id=' . db_input($thisstaff->getId()) . ' AND status.state="open" )'; if (($teams = $thisstaff->getTeams()) && count(array_filter($teams))) { $where .= ' OR (ticket.team_id IN (' . implode(',', db_input(array_filter($teams))) . ' ) AND status.state="open" )'; } if (!$thisstaff->showAssignedOnly() && ($depts = $thisstaff->getDepts())) { $where .= ' OR ticket.dept_id IN (' . implode(',', db_input($depts)) . ')'; } $where .= ' ) '; //Department if ($req['deptId']) { $where .= ' AND ticket.dept_id=' . db_input($req['deptId']); $criteria['dept_id'] = $req['deptId']; } //Help topic if ($req['topicId']) { $where .= ' AND ticket.topic_id=' . db_input($req['topicId']); $criteria['topic_id'] = $req['topicId']; } // Status if ($req['statusId'] && ($status = TicketStatus::lookup($req['statusId']))) { $where .= sprintf(' AND status.id="%d" ', $status->getId()); $criteria['status_id'] = $status->getId(); } // Flags if ($req['flag']) { switch (strtolower($req['flag'])) { case 'answered': $where .= ' AND ticket.isanswered =1 '; $criteria['isanswered'] = 1; $criteria['state'] = 'open'; $where .= ' AND status.state="open" '; break; case 'overdue': $where .= ' AND ticket.isoverdue =1 '; $criteria['isoverdue'] = 1; $criteria['state'] = 'open'; $where .= ' AND status.state="open" '; break; } } //Assignee if ($req['assignee'] && strcasecmp($req['status'], 'closed')) { # assigned-to $id = preg_replace("/[^0-9]/", "", $req['assignee']); $assignee = $req['assignee']; $where .= ' AND ( ( status.state="open" '; if ($assignee[0] == 't') { $where .= ' AND ticket.team_id=' . db_input($id); $criteria['team_id'] = $id; } elseif ($assignee[0] == 's' || is_numeric($id)) { $where .= ' AND ticket.staff_id=' . db_input($id); $criteria['staff_id'] = $id; } $where .= ')'; if ($req['staffId'] && !$req['status']) { //Assigned TO + Closed By $where .= ' OR (ticket.staff_id=' . db_input($req['staffId']) . ' AND status.state IN("closed")) '; } elseif ($req['staffId']) { // closed by any $where .= ' OR status.state IN("closed") '; } $where .= ' ) '; } elseif ($req['staffId']) { # closed-by $where .= ' AND (ticket.staff_id=' . db_input($req['staffId']) . ' AND status.state IN("closed")) '; $criteria['state__in'] = array('closed'); $criteria['staff_id'] = $req['staffId']; } //dates $startTime = $req['startDate'] && strlen($req['startDate']) >= 8 ? strtotime($req['startDate']) : 0; $endTime = $req['endDate'] && strlen($req['endDate']) >= 8 ? strtotime($req['endDate']) : 0; if ($endTime) { // $endTime should be the last second of the day, not the first like $startTime $endTime += 60 * 60 * 24 - 1; } if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) { $startTime = $endTime = 0; } if ($startTime) { $where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')'; $criteria['created__gte'] = $startTime; } if ($endTime) { $where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')'; $criteria['created__lte'] = $startTime; } // Dynamic fields $cdata_search = false; foreach (TicketForm::getInstance()->getFields() as $f) { if (isset($req[$f->getFormName()]) && ($val = $req[$f->getFormName()])) { $name = $f->get('name') ? $f->get('name') : 'field_' . $f->get('id'); if (is_array($val)) { $cwhere = '(' . implode(' OR ', array_map(function ($k) use($name) { return sprintf('FIND_IN_SET(%s, `%s`)', db_input($k), $name); }, $val)) . ')'; $criteria["cdata.{$name}"] = $val; } else { $cwhere = "cdata.`{$name}` LIKE '%" . db_real_escape($val) . "%'"; $criteria["cdata.{$name}"] = $val; } $where .= ' AND (' . $cwhere . ')'; $cdata_search = true; } } if ($cdata_search) { $from .= 'LEFT JOIN ' . TABLE_PREFIX . 'ticket__cdata ' . " cdata ON (cdata.ticket_id = ticket.ticket_id)"; } //Query $joins = array(); if ($req['query']) { // Setup sets of joins and queries if ($s = $ost->searcher) { return $s->find($req['query'], $criteria, 'Ticket'); } } $sections = array(); foreach ($joins as $j) { $sections[] = "{$select} {$from} {$j['from']} {$where} AND ({$j['where']})"; } if (!$joins) { $sections[] = "{$select} {$from} {$where}"; } $sql = implode(' union ', $sections); if (!($res = db_query($sql))) { return TicketForm::dropDynamicDataView(); } $tickets = array(); while ($row = db_fetch_row($res)) { $tickets[] = $row[0]; } return $tickets; }
function search() { global $thisstaff, $cfg; $result = array(); $select = 'SELECT count( DISTINCT ticket.ticket_id) as tickets '; $from = ' FROM ' . TICKET_TABLE . ' ticket '; $where = ' WHERE 1 '; //Access control. $where .= ' AND ( ticket.staff_id=' . db_input($thisstaff->getId()); if (($teams = $thisstaff->getTeams()) && count(array_filter($teams))) { $where .= ' OR ticket.team_id IN(' . implode(',', db_input(array_filter($teams))) . ')'; } if (!$thisstaff->showAssignedOnly() && ($depts = $thisstaff->getDepts())) { $where .= ' OR ticket.dept_id IN (' . implode(',', db_input($depts)) . ')'; } $where .= ' ) '; //Department if ($_REQUEST['deptId']) { $where .= ' AND ticket.dept_id=' . db_input($_REQUEST['deptId']); } //Help topic if ($_REQUEST['topicId']) { $where .= ' AND ticket.topic_id=' . db_input($_REQUEST['topicId']); } //Status switch (strtolower($_REQUEST['status'])) { case 'open': $where .= ' AND ticket.status="open" '; break; case 'answered': $where .= ' AND ticket.status="open" AND ticket.isanswered=1 '; break; case 'overdue': $where .= ' AND ticket.status="open" AND ticket.isoverdue=1 '; break; case 'closed': $where .= ' AND ticket.status="closed" '; break; } //Assignee if (isset($_REQUEST['assignee']) && strcasecmp($_REQUEST['status'], 'closed')) { $id = preg_replace("/[^0-9]/", "", $_REQUEST['assignee']); $assignee = $_REQUEST['assignee']; $where .= ' AND ( ( ticket.status="open" '; if ($assignee[0] == 't') { $where .= ' AND ticket.team_id=' . db_input($id); } elseif ($assignee[0] == 's') { $where .= ' AND ticket.staff_id=' . db_input($id); } elseif (is_numeric($id)) { $where .= ' AND ticket.staff_id=' . db_input($id); } $where .= ')'; if ($_REQUEST['staffId'] && !$_REQUEST['status']) { //Assigned TO + Closed By $where .= ' OR (ticket.staff_id=' . db_input($_REQUEST['staffId']) . ' AND ticket.status="closed") '; } elseif (isset($_REQUEST['staffId'])) { // closed by any $where .= ' OR ticket.status="closed" '; } $where .= ' ) '; } elseif ($_REQUEST['staffId']) { $where .= ' AND (ticket.staff_id=' . db_input($_REQUEST['staffId']) . ' AND ticket.status="closed") '; } //dates $startTime = $_REQUEST['startDate'] && strlen($_REQUEST['startDate']) >= 8 ? strtotime($_REQUEST['startDate']) : 0; $endTime = $_REQUEST['endDate'] && strlen($_REQUEST['endDate']) >= 8 ? strtotime($_REQUEST['endDate']) : 0; if ($startTime && $startTime > time() or $startTime > $endTime && $endTime > 0) { $startTime = $endTime = 0; } if ($startTime) { $where .= ' AND ticket.created>=FROM_UNIXTIME(' . $startTime . ')'; } if ($endTime) { $where .= ' AND ticket.created<=FROM_UNIXTIME(' . $endTime . ')'; } //Query if ($_REQUEST['query']) { $queryterm = db_real_escape($_REQUEST['query'], false); $from .= ' LEFT JOIN ' . TICKET_THREAD_TABLE . ' thread ON (ticket.ticket_id=thread.ticket_id )'; $where .= " AND ( ticket.email LIKE '%{$queryterm}%'" . " OR ticket.name LIKE '%{$queryterm}%'" . " OR ticket.subject LIKE '%{$queryterm}%'" . " OR thread.title LIKE '%{$queryterm}%'" . " OR thread.body LIKE '%{$queryterm}%'" . ' )'; } $sql = "{$select} {$from} {$where}"; if ($tickets = db_result(db_query($sql))) { $result['success'] = sprintf("Search criteria matched %s - <a href='tickets.php?%s'>view</a>", $tickets > 1 ? "{$tickets} tickets" : "{$tickets} ticket", str_replace(array('&', '&'), array('&', '&'), $_SERVER['QUERY_STRING'])); } else { $result['fail'] = 'No tickets found matching your search criteria.'; } return $this->json_encode($result); }