function &getTicketSearch() { FST_Ticket_Helper::getAdminPermissions(); $mainframe = JFactory::getApplication(); $limit = $mainframe->getUserStateFromRequest('global.list.limit_ticket', 'limit', FST_Helper::getUserSetting('per_page'), 'int'); $limitstart = JRequest::getVar('limitstart', 0, '', 'int'); $limitstart = $limit != 0 ? floor($limitstart / $limit) * $limit : 0; $db = JFactory::getDBO(); if (empty($this->_tickets)) { $query = "SELECT t.*, s.title as status, s.color, u.name, au.name as assigned, u.email as useremail, u.username as username, au.email as handleremail, au.username as handlerusername, "; $query .= " dept.title as department, cat.title as category, prod.title as product, pri.title as priority, pri.color as pricolor, "; $query .= " grp.groupname as groupname, grp.id as group_id "; $query .= " , pri.translation as ptl, dept.translation as dtr, s.translation as str, cat.translation as ctr, prod.translation as prtr"; $query .= " FROM #__fst_ticket_ticket as t "; $query .= " LEFT JOIN #__fst_ticket_status as s ON t.ticket_status_id = s.id "; $query .= " LEFT JOIN #__users as u ON t.user_id = u.id "; $query .= " LEFT JOIN #__fst_user as a ON t.admin_id = a.id "; $query .= " LEFT JOIN #__users as au ON a.user_id = au.id "; $query .= " LEFT JOIN #__fst_ticket_dept as dept ON t.ticket_dept_id = dept.id "; $query .= " LEFT JOIN #__fst_ticket_cat as cat ON t.ticket_cat_id = cat.id "; $query .= " LEFT JOIN #__fst_prod as prod ON t.prod_id = prod.id "; $query .= " LEFT JOIN #__fst_ticket_pri as pri ON t.ticket_pri_id = pri.id "; $query .= " LEFT JOIN (SELECT group_id, user_id FROM #__fst_ticket_group_members GROUP BY user_id) as mem ON t.user_id = mem.user_id "; $query .= " LEFT JOIN #__fst_ticket_group as grp ON grp.id = mem.group_id "; $searchtype = JRequest::getVar('searchtype', 'basic'); $ticketids = array(); $ticketids[0] = 0; $ticketid_matchall = 0; $tags = JRequest::getVar('tags', ''); $tags = trim($tags, ';'); if ($tags) { $tags_ = explode(";", $tags); $tags = array(); foreach ($tags_ as $tag) { if ($tag) { $tags[$tag] = $tag; } } if (count($tags) > 0) { foreach ($tags as $tag) { $ticketid_matchall++; $qry = "SELECT ticket_id FROM #__fst_ticket_tags WHERE tag = '" . FSTJ3Helper::getEscaped($db, $tag) . "'"; $db->setQuery($qry); //echo $qry."<br>"; $rows = $db->loadAssocList("ticket_id"); foreach ($rows as $row) { $ticketid = $row['ticket_id']; if (array_key_exists($ticketid, $ticketids)) { $ticketids[$ticketid]++; } else { $ticketids[$ticketid] = 1; } } } } } if ($searchtype == "basic") { $search = JRequest::getVar('search', ''); $wherebits = array(); // store tag match ids in separate array, as we want to AND them, not OR $tagids = $ticketids; $ticketids = array(); $ticketids[0] = 0; if ($search != "") { $wherebits[] = " t.title LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%' "; $wherebits[] = " t.reference = '" . FSTJ3Helper::getEscaped($db, $search) . "' "; // search custom fields that are set to be searched $fields = FSTCF::GetAllCustomFields(true); /*echo "<pre>"; print_r($fields); echo "</pre>";*/ foreach ($fields as $field) { if (!$field['basicsearch']) { continue; } $ticketid_matchall++; $fieldid = $field['id']; $qry = "SELECT ticket_id FROM #__fst_ticket_field WHERE field_id = '" . FSTJ3Helper::getEscaped($db, $fieldid) . "' AND value LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%'"; $db->setQuery($qry); //echo $qry."<br>"; $moreids = $db->loadAssoclist(); //print_r($moreids); foreach ($moreids as $row) { if (array_key_exists($row['ticket_id'], $ticketids)) { $ticketids[$row['ticket_id']]++; } else { $ticketids[$row['ticket_id']] = 1; } } } // basic search optional fields if (FST_Settings::get('support_basic_name')) { $wherebits[] = " u.name LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%' "; $wherebits[] = " unregname LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%' "; } if (FST_Settings::get('support_basic_username')) { $wherebits[] = " u.username LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%' "; } if (FST_Settings::get('support_basic_email')) { $wherebits[] = " u.email LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%' "; $wherebits[] = " t.email LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%' "; } if (FST_Settings::get('support_basic_messages')) { $ticketid_matchall++; $fieldid = $field['id']; $qry = "SELECT ticket_ticket_id as ticket_id FROM #__fst_ticket_messages WHERE subject LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%' OR body LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%'"; $db->setQuery($qry); //echo $qry."<br>"; $moreids = $db->loadAssoclist(); //print_r($moreids); foreach ($moreids as $row) { if (array_key_exists($row['ticket_id'], $ticketids)) { $ticketids[$row['ticket_id']]++; } else { $ticketids[$row['ticket_id']] = 1; } } } } if (count($ticketids) > 1) { $tids = array(); foreach ($ticketids as $id => $rec) { $tids[] = $id; } $ticketids = $tids; unset($tids); } if (count($ticketids) > 1) { $wherebits[] = "t.id IN (" . implode(",", $ticketids) . ")"; } if (count($wherebits) == 0) { $wherebits[] = "1"; } $query .= " WHERE (" . implode(" OR ", $wherebits) . ")"; // add ticket tag ids if (count($tagids) > 1) { $tids = array(); foreach ($tagids as $id => $rec) { $tids[] = $id; } $tagids = $tids; unset($tids); $query .= " AND t.id IN (" . implode(",", $tagids) . ")"; } //echo $query . "<br>"; } else { if ($searchtype == "advanced") { $search = JRequest::getVar('search', ''); $wherebits = array(); $subject = JRequest::getVar('subject', ''); if ($subject) { $wherebits[] = " t.title LIKE '%" . FSTJ3Helper::getEscaped($db, $subject) . "%' "; } $reference = JRequest::getVar('reference', ''); if ($reference) { $wherebits[] = " t.reference = '" . FSTJ3Helper::getEscaped($db, $reference) . "' "; } $username = JRequest::getVar('username', ''); if ($username) { $wherebits[] = " u.username LIKE '%" . FSTJ3Helper::getEscaped($db, $username) . "%' "; } $useremail = JRequest::getVar('useremail', ''); if ($useremail) { $wherebits[] = " ( u.email LIKE '%" . FSTJ3Helper::getEscaped($db, $useremail) . "%' OR t.email LIKE '%" . FSTJ3Helper::getEscaped($db, $useremail) . "%' ) "; } $userfullname = JRequest::getVar('userfullname', ''); if ($userfullname) { $wherebits[] = " ( u.name LIKE '%" . FSTJ3Helper::getEscaped($db, $userfullname) . "%' OR unregname LIKE '%" . FSTJ3Helper::getEscaped($db, $userfullname) . "%' ) "; } $content = JRequest::getVar('content', ''); if ($content) { $q = " t.id IN "; $q .= "( SELECT ticket_ticket_id FROM #__fst_ticket_messages WHERE body LIKE '%" . FSTJ3Helper::getEscaped($db, $content) . "%' )"; $wherebits[] = $q; } $handler = JRequest::getVar('handler', ''); if ($handler) { if ($handler == -1 || $handler == -2) { // need to find my handler id $user = JFactory::getUser(); $qry = "SELECT * FROM #__fst_user WHERE user_id = '" . FSTJ3Helper::getEscaped($db, $user->id) . "'"; $db->setQuery($qry); $fstuser = $db->loadObject(); if ($handler == -1) { $wherebits[] = " t.admin_id = '" . FSTJ3Helper::getEscaped($db, $fstuser->id) . "' "; } else { $wherebits[] = " t.admin_id != '" . FSTJ3Helper::getEscaped($db, $fstuser->id) . "' "; $wherebits[] = " t.admin_id != 0 "; } } else { if ($handler == -3) { $wherebits[] = " t.admin_id = 0"; } else { $wherebits[] = " t.admin_id = '" . FSTJ3Helper::getEscaped($db, $handler) . "' "; } } } $status = JRequest::getVar('status', ''); if ($status) { $wherebits[] = " t.ticket_status_id = '" . FSTJ3Helper::getEscaped($db, $status) . "' "; } $product = JRequest::getVar('product', ''); if ($product) { $wherebits[] = " t.prod_id = '" . FSTJ3Helper::getEscaped($db, $product) . "' "; } $department = JRequest::getVar('department', ''); if ($department) { $wherebits[] = " t.ticket_dept_id = '" . FSTJ3Helper::getEscaped($db, $department) . "' "; } $cat = JRequest::getVar('cat', ''); if ($cat) { $wherebits[] = " t.ticket_cat_id = '" . FSTJ3Helper::getEscaped($db, $cat) . "' "; } $pri = JRequest::getVar('priority', ''); if ($pri) { $wherebits[] = " t.ticket_pri_id = '" . FSTJ3Helper::getEscaped($db, $pri) . "' "; } $group = JRequest::getVar('group', ''); if ($group > 0) { $wherebits[] = " t.user_id IN (SELECT user_id FROM #__fst_ticket_group_members WHERE group_id = '" . FSTJ3Helper::getEscaped($db, $group) . "' GROUP BY user_id)"; } $date_from = $this->DateValidate(JRequest::getVar('date_from', '')); $date_to = $this->DateValidate(JRequest::getVar('date_to', '')); /*if ($date_from && $date_to) { // got both date, need a ticket with } else*/ if ($date_from) { $wherebits[] = " t.lastupdate > DATE_SUB('" . FSTJ3Helper::getEscaped($db, $date_from) . "',INTERVAL 1 DAY) "; } /*else*/ if ($date_to) { $wherebits[] = " t.opened < DATE_ADD('" . FSTJ3Helper::getEscaped($db, $date_to) . "',INTERVAL 1 DAY) "; } // search custom fields that are set to be searched $fields = FSTCF::GetAllCustomFields(true); /*echo "<pre>"; print_r($fields); echo "</pre>";*/ foreach ($fields as $field) { if (!$field['advancedsearch']) { continue; } $search = JRequest::getVar('custom_' . $field['id'], ""); //echo "Field : {$field['id']} = $search<br>"; if ($search != "") { $ticketid_matchall++; $fieldid = $field['id']; if ($field['type'] == "checkbox") { if ($search == "1") { $qry = "SELECT ticket_id FROM #__fst_ticket_field WHERE field_id = '" . FSTJ3Helper::getEscaped($db, $fieldid) . "' AND value = 'on'"; } else { $qry = "SELECT ticket_id FROM #__fst_ticket_field WHERE field_id = '" . FSTJ3Helper::getEscaped($db, $fieldid) . "' AND value = ''"; } } elseif ($field['type'] == "radio" || $field['type'] == "combo") { $qry = "SELECT ticket_id FROM #__fst_ticket_field WHERE field_id = '" . FSTJ3Helper::getEscaped($db, $fieldid) . "' AND value = '" . FSTJ3Helper::getEscaped($db, $search) . "'"; } else { $qry = "SELECT ticket_id FROM #__fst_ticket_field WHERE field_id = '" . FSTJ3Helper::getEscaped($db, $fieldid) . "' AND value LIKE '%" . FSTJ3Helper::getEscaped($db, $search) . "%'"; } $db->setQuery($qry); //echo $qry."<br>"; $moreids = $db->loadAssoclist(); //print_r($moreids); foreach ($moreids as $row) { if (array_key_exists($row['ticket_id'], $ticketids)) { $ticketids[$row['ticket_id']]++; } else { $ticketids[$row['ticket_id']] = 1; } } } } if ($ticketid_matchall > 0) { unset($ticketids[0]); $tids = array(); if (count($ticketids) > 0) { foreach ($ticketids as $id => $rec) { if ($id == 0) { continue; } if ($rec == $ticketid_matchall) { $tids[] = $id; } } $ticketids = $tids; unset($tids); } if (count($ticketids) > 0) { $wherebits[] = "t.id IN (" . implode(",", $ticketids) . ")"; } else { $wherebits[] = "0"; } } if (count($wherebits) == 0) { $wherebits[] = "1"; } $query .= " WHERE " . implode(" AND ", $wherebits); } else { $query .= " WHERE 1 "; } } $query .= FST_Ticket_Helper::$_perm_where; $order = array(); if (FST_Helper::getUserSetting("group_products")) { $order[] = "prod.ordering"; } if (FST_Helper::getUserSetting("group_departments")) { $order[] = "dept.title"; } if (FST_Helper::getUserSetting("group_cats")) { $order[] = "cat.title"; } if (FST_Helper::getUserSetting("group_pri")) { $order[] = "pri.ordering DESC"; } if (FST_Helper::getUserSetting("group_group")) { $order[] = "case when grp.groupname is null then 1 else 0 end"; $order[] = "grp.groupname"; } $order[] = "lastupdate DESC"; $query .= " ORDER BY " . implode(", ", $order); //echo "<br>$query<br>"; $db->setQuery($query); $db->query(); $this->_ticketcount = $db->getNumRows(); $db->setQuery($query, $limitstart, $limit); $this->_tickets = $db->loadAssocList('id'); } /*echo "<pre>"; print_r($result['tickets']); echo "</pre>";*/ $result['pagination'] = new JPaginationJs($this->_ticketcount, $limitstart, $limit); $result['count'] =& $this->_ticketcount; $result['tickets'] =& $this->_tickets; return $result; }
static function &GetTicketValues($ticketid, $ticket) { if (empty(FSTCF::$_ticketvalues)) { FSTCF::$_ticketvalues = array(); } if (!array_key_exists($ticketid, FSTCF::$_ticketvalues)) { $allfields = FSTCF::GetAllCustomFields(true); $db = JFactory::getDBO(); $qry = "SELECT * FROM #__fst_ticket_field WHERE ticket_id ='" . FSTJ3Helper::getEscaped($db, $ticketid) . "'"; $db->setQuery($qry); $values = $db->loadAssocList('field_id'); $values2 = FSTCF::GetUserValues($ticket['user_id'], $ticket['id']); foreach ($values2 as $id => $value) { if (array_key_exists($id, $allfields) && $allfields[$id]['peruser']) { $values[$id] = $value; } } FSTCF::$_ticketvalues[$ticketid] = $values; } return FSTCF::$_ticketvalues[$ticketid]; }
static function &ParseTemplate($template, &$ticket, $subject, $body, $ishtml) { $handler = FST_EMail::GetHandler($ticket['admin_id']); $custrec = FST_EMail::GetUser($ticket['user_id']); $subject = trim(str_ireplace("re:", "", $subject)); $vars[] = FST_EMail::BuildVar('subject', $subject); /*if ($ishtml) { $body = str_replace("\n","<br />\n",$body); }*/ $body = FST_Helper::ParseBBCode($body); $vars[] = FST_EMail::BuildVar('body', $body); $vars[] = FST_EMail::BuildVar('reference', $ticket['reference']); $vars[] = FST_EMail::BuildVar('password', $ticket['password']); if ($ticket['user_id'] == 0) { $vars[] = FST_EMail::BuildVar('user_name', $ticket['unregname']); $vars[] = FST_EMail::BuildVar('user_username', JText::_("UNREGISTERED")); $vars[] = FST_EMail::BuildVar('user_email', $ticket['email']); } else { $vars[] = FST_EMail::BuildVar('user_name', $custrec['name']); $vars[] = FST_EMail::BuildVar('user_username', $custrec['username']); $vars[] = FST_EMail::BuildVar('user_email', $custrec['email']); } $vars[] = FST_EMail::BuildVar('handler_name', $handler['name']); $vars[] = FST_EMail::BuildVar('handler_username', $handler['username']); $vars[] = FST_EMail::BuildVar('handler_email', $handler['email']); $vars[] = FST_EMail::BuildVar('ticket_id', $ticket['id']); $vars[] = FST_EMail::BuildVar('status', FST_EMail::GetStatus($ticket['ticket_status_id'])); $vars[] = FST_EMail::BuildVar('priority', FST_EMail::GetPriority($ticket['ticket_pri_id'])); $vars[] = FST_EMail::BuildVar('category', FST_EMail::GetCategory($ticket['ticket_cat_id'])); $vars[] = FST_EMail::BuildVar('department', FST_EMail::GetDepartment($ticket['ticket_dept_id'])); $vars[] = FST_EMail::BuildVar('product', FST_EMail::GetProduct($ticket['prod_id'])); if (strpos($template['body'], "{messagehistory}") > 0) { //echo "Get message history<br>"; $messages = FST_EMail::GetMessageHist($ticket['id']); // need to load in the messagerow template and parse it $text = FST_EMail::ParseMessageRows($messages, $ishtml); $vars[] = FST_EMail::BuildVar('messagehistory', $text); //print_p($messages); } $uri = JURI::getInstance(); $baseUrl = $uri->toString(array('scheme', 'host', 'port')); $vars[] = FST_EMail::BuildVar('ticket_link', $baseUrl . FSTRoute::_('index.php?option=com_fst&view=ticket&ticketid=' . $ticket['id'], false)); $vars[] = FST_EMail::BuildVar('admin_link', $baseUrl . FSTRoute::_('index.php?option=com_fst&view=admin&layout=support&ticketid=' . $ticket['id'], false)); $config = JFactory::getConfig(); if (FSTJ3Helper::IsJ3()) { $sitename = $config->get('sitename'); } else { $sitename = $config->getValue('sitename'); } if (FST_Settings::get('support_email_site_name') != "") { $sitename = FST_Settings::get('support_email_site_name'); } $vars[] = FST_EMail::BuildVar('websitetitle', $sitename); // need to add the tickets custom fields to the output here $fields = FSTCF::GetAllCustomFields(true); $values = FSTCF::GetTicketValues($ticket['id'], $ticket); foreach ($fields as $fid => &$field) { $name = "custom_" . $fid; $value = ""; if (array_key_exists($fid, $values)) { $value = $values[$fid]['value']; } //echo "$name -> $value<br>"; $fieldvalues = array(); $fieldvalues[0]['field_id'] = $fid; $fieldvalues[0]['value'] = $value; // only do area output processing if we are in html mode if ($field['type'] != "area" || $ishtml) { $value = FSTCF::FieldOutput($field, $fieldvalues, ''); } $vars[] = FST_EMail::BuildVar($name, $value); } $email['subject'] = FST_EMail::ParseText($template['subject'], $vars); $email['body'] = FST_EMail::ParseText($template['body'], $vars); //print_p($vars); //print_p($email); if ($template['ishtml']) { //$email['subject'] = str_replace("\n","<br />\n",$email['subject']); $email['body'] = FST_EMail::MaxLineLength($email['body']); } else { // strip bbcode out of subject or parse it to html depending on template type $email['body'] = str_replace("<br />", "\n", $email['body']); $email['body'] = html_entity_decode($email['body']); $email['body'] = preg_replace_callback("/(&#[0-9]+;)/", array($this, "email_decode_utf8"), $email['body']); $email['body'] = strip_tags($email['body']); } return $email; }