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;
}
