function loadTicketsBySearch()
{
$db = JFactory::getDBO();
$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, \n";
$query .= " dept.title as department, cat.title as category, prod.title as product, pri.title as priority, pri.color as pricolor, \n";
$query .= " grp.groupname as groupname, grp.id as group_id \n";
$query .= " , pri.translation as ptl, dept.translation as dtr, s.translation as str, cat.translation as ctr, prod.translation as prtr\n";
$query .= " FROM #__fss_ticket_ticket as t \n";
$query .= " LEFT JOIN #__fss_ticket_status as s ON t.ticket_status_id = s.id \n";
$query .= " LEFT JOIN #__users as u ON t.user_id = u.id \n";
$query .= " LEFT JOIN #__users as au ON t.admin_id = au.id \n";
$query .= " LEFT JOIN #__fss_ticket_dept as dept ON t.ticket_dept_id = dept.id \n";
$query .= " LEFT JOIN #__fss_ticket_cat as cat ON t.ticket_cat_id = cat.id \n";
$query .= " LEFT JOIN #__fss_prod as prod ON t.prod_id = prod.id \n";
$query .= " LEFT JOIN #__fss_ticket_pri as pri ON t.ticket_pri_id = pri.id \n";
$query .= " LEFT JOIN (SELECT group_id, user_id FROM #__fss_ticket_group_members GROUP BY user_id) as mem ON t.user_id = mem.user_id \n";
$query .= " LEFT JOIN #__fss_ticket_group as grp ON grp.id = mem.group_id \n";
// add custom fields to the sql
foreach (FSSCF::GetAllCustomFields() as $field) {
if (!$field['inlist']) {
continue;
}
$id = $field['id'];
if ($field['peruser']) {
$query .= " LEFT JOIN #__fss_ticket_user_field as cf{$id} ON cf{$id}.user_id = t.user_id AND cf{$id}.field_id = {$id} \n";
} else {
$query .= " LEFT JOIN #__fss_ticket_field as cf{$id} ON cf{$id}.ticket_id = t.id AND cf{$id}.field_id = {$id} \n";
}
}
$searchtype = FSS_Input::getCmd('searchtype', 'basic');
$wherebits = array();
if ($searchtype == "basic") {
$search = FSS_Input::getString('search', '');
if ($search != "") {
$mode = "";
if (FSS_Helper::contains($search, array('*', '+', '-', '<', '>', '(', ')', '~', '"'))) {
$mode = "IN BOOLEAN MODE";
}
//$wherebits[] = " t.title LIKE '%".FSSJ3Helper::getEscaped($db, $search)."%' ";
$wherebits[] = " MATCH (t.title) AGAINST ('" . $db->escape($search) . "' {$mode}) /* Title */ ";
$wherebits[] = " t.reference LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%' /* Reference */ ";
// search custom fields that are set to be searched
$this->searchFields($wherebits, "basicsearch");
// basic search optional fields
if (FSS_Settings::get('support_basic_name')) {
$wherebits[] = " u.name LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%' /* Name */ ";
$wherebits[] = " unregname LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%' /* UnReg Name */ ";
}
if (FSS_Settings::get('support_basic_username')) {
$wherebits[] = " u.username LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%' /* Username */ ";
}
if (FSS_Settings::get('support_basic_email')) {
$wherebits[] = " u.email LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%' /* User email */ ";
$wherebits[] = " t.email LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%' /* Unreg Email */ ";
}
if (FSS_Settings::get('support_basic_messages')) {
$qry = "SELECT ticket_ticket_id FROM #__fss_ticket_messages WHERE subject LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%' OR ";
$qry .= " MATCH (body) AGAINST ('" . $db->escape($search) . "' {$mode}) GROUP BY ticket_ticket_id";
$qry .= " AND admin IN (0, 1, 2, 4) ";
$db->setQuery($qry);
$wherebits[] = $this->TicketIDsToWhere($db->loadObjectList(), "ticket_ticket_id") . " /* Messages */ ";
}
}
if (count($wherebits) == 0) {
$wherebits[] = "1 /* Catch All */";
}
$query .= "\n WHERE (" . implode("\n OR ", $wherebits) . ")";
} else {
if ($searchtype == "advanced") {
$search = FSS_Input::getString('search', '');
$wherebits = array();
$subject = FSS_Input::getString('subject', '');
if ($subject) {
$wherebits[] = " t.title LIKE '%" . FSSJ3Helper::getEscaped($db, $subject) . "%' /* Title */ ";
}
$reference = FSS_Input::getString('reference', '');
if ($reference) {
$wherebits[] = " t.reference LIKE '%" . FSSJ3Helper::getEscaped($db, $reference) . "%' /* Reference */ ";
}
$username = FSS_Input::getString('username', '');
if ($username) {
$wherebits[] = " u.username LIKE '%" . FSSJ3Helper::getEscaped($db, $username) . "%' /* Username */ ";
}
$useremail = FSS_Input::getString('useremail', '');
if ($useremail) {
$wherebits[] = " ( u.email LIKE '%" . FSSJ3Helper::getEscaped($db, $useremail) . "%' OR t.email LIKE '%" . FSSJ3Helper::getEscaped($db, $useremail) . "%' ) /* EMail */";
}
$userfullname = FSS_Input::getString('userfullname', '');
if ($userfullname) {
$wherebits[] = " ( u.name LIKE '%" . FSSJ3Helper::getEscaped($db, $userfullname) . "%' OR unregname LIKE '%" . FSSJ3Helper::getEscaped($db, $userfullname) . "%' ) /* Name */";
}
$content = FSS_Input::getString('content', '');
if ($content) {
$mode = "";
if (FSS_Helper::contains($search, array('*', '+', '-', '<', '>', '(', ')', '~', '"'))) {
$mode = "IN BOOLEAN MODE";
}
$qry = "SELECT ticket_ticket_id FROM #__fss_ticket_messages WHERE subject LIKE '%" . FSSJ3Helper::getEscaped($db, $content) . "%' OR ";
$qry .= " MATCH (body) AGAINST ('" . $db->escape($content) . "' {$mode})";
$qry .= " AND admin IN (0, 1, 2, 4) ";
$db->setQuery($qry);
$wherebits[] = $this->TicketIDsToWhere($db->loadObjectList(), "ticket_ticket_id") . " /* Messages */ ";
}
$handler = FSS_Input::getInt('handler', '');
if ($handler) {
$user = JFactory::getUser();
if ($handler == -5) {
$wherebits[] = " t.admin_id = '" . $user->id . "' /* Handler -1 */ ";
} else {
if ($handler == -2) {
$wherebits[] = " t.admin_id != '" . $user->id . "' /* Handler -2 */ ";
$wherebits[] = " t.admin_id != 0 /* Handler -2 */";
} else {
if ($handler == -3) {
$wherebits[] = " t.admin_id = 0 /* Handler -3 */";
} elseif ($handler == -4) {
$temp_where[] = " t.admin_id = '" . $db->escape($user->id) . "' ";
$qry = "SELECT ticket_id FROM #__fss_ticket_cc WHERE isadmin = 1 AND user_id = " . $db->escape($user->id);
$db->setQuery($qry);
$wherebits[] = $this->TicketIDsToWhere($db->loadObjectList(), "ticket_id") . " /* Handler -4 */";
} else {
// handler
if ($handler == -1) {
$handler = JFactory::getUser()->id;
}
$qry = "SELECT * FROM #__fss_ticket_cc WHERE isadmin = 1 AND user_id = " . $db->escape($handler);
$db->setQuery($qry);
$temp_where[] = $this->TicketIDsToWhere($db->loadObjectList(), "ticket_id");
$temp_where[] = " t.admin_id = '" . $db->escape($handler) . "' ";
$wherebits[] = " ( " . implode(" OR ", $temp_where) . " ) /* Handler specified */ ";
//$wherebits[] = " t.admin_id = '".FSSJ3Helper::getEscaped($db, $handler)."' ";
}
}
}
}
$status = FSS_Input::getCmd('status', '');
if ($status) {
if ($status == "open") {
$open = FSS_Ticket_Helper::GetStatusIDs("def_open");
// tickets that arent closed
$wherebits[] = " t.ticket_status_id IN ( " . implode(", ", $open) . ") /* Def open */ ";
} elseif ($status == 'allopen') {
$allopen = FSS_Ticket_Helper::GetStatusIDs("is_closed", true);
// tickets that arent closed
$wherebits[] = " t.ticket_status_id IN ( " . implode(", ", $allopen) . ") /* All open */ ";
} elseif ($status == 'closed') {
$allopen = FSS_Ticket_Helper::GetStatusIDs("is_closed");
// remove the archived tickets from the list to deal with
$def_archive = FSS_Ticket_Helper::GetStatusID('def_archive');
foreach ($allopen as $offset => $value) {
if ($value == $def_archive) {
unset($allopen[$offset]);
}
}
// tickets that are closed
$wherebits[] = " t.ticket_status_id IN ( " . implode(", ", $allopen) . ") /* all closed */";
} elseif ($status == 'all') {
// need all tickets that arent archived
$allopen = FSS_Ticket_Helper::GetStatusIDs("def_archive", true);
$wherebits[] = " t.ticket_status_id IN ( " . implode(", ", $allopen) . " ) /* all (not archived) */ ";
} elseif ($status == 'archived') {
// need all tickets that arent archived
$allopen = FSS_Ticket_Helper::GetStatusIDs("def_archive");
$wherebits[] = " t.ticket_status_id IN ( " . implode(", ", $allopen) . " ) /* archived */ ";
} else {
$wherebits[] = " t.ticket_status_id = " . (int) FSSJ3Helper::getEscaped($db, $status) . " /* specified status */";
}
}
$product = FSS_Input::getInt('product', '');
if ($product) {
$wherebits[] = " t.prod_id = '" . FSSJ3Helper::getEscaped($db, $product) . "' /* Product */";
}
$department = FSS_Input::getInt('department', '');
if ($department) {
$wherebits[] = " t.ticket_dept_id = '" . FSSJ3Helper::getEscaped($db, $department) . "' /* Department */ ";
}
$cat = FSS_Input::getInt('cat', '');
if ($cat) {
$wherebits[] = " t.ticket_cat_id = '" . FSSJ3Helper::getEscaped($db, $cat) . "' /* Category */";
}
$pri = FSS_Input::getInt('priority', '');
if ($pri) {
$wherebits[] = " t.ticket_pri_id = '" . FSSJ3Helper::getEscaped($db, $pri) . "' /* Priority */";
}
$group = FSS_Input::getInt('group', '');
if ($group > 0) {
$wherebits[] = " t.user_id IN (SELECT user_id FROM #__fss_ticket_group_members WHERE group_id = '" . FSSJ3Helper::getEscaped($db, $group) . "' GROUP BY user_id) /* Ticket Group */";
}
$date_from = FSS_Helper::DateValidate(FSS_Input::getString('date_from', ''));
$date_to = FSS_Helper::DateValidate(FSS_Input::getString('date_to', ''));
if ($date_from) {
$wherebits[] = " t.lastupdate > DATE_SUB('" . FSSJ3Helper::getEscaped($db, $date_from) . "',INTERVAL 1 DAY) /* Date From */";
}
if ($date_to) {
$wherebits[] = " t.opened < DATE_ADD('" . FSSJ3Helper::getEscaped($db, $date_to) . "',INTERVAL 1 DAY) /* Date To */";
}
$this->searchFields($wherebits, "advancedsearch");
if (count($wherebits) == 0) {
$wherebits[] = "1 /* Catch All */";
}
$query .= "\n WHERE " . implode("\n AND ", $wherebits);
} else {
$query .= " WHERE 1 /* Catch All */";
}
}
$query .= "\n AND " . $this->getTagFilter() . " /* Tag Filter */";
$query .= "\n AND " . SupportUsers::getAdminWhere() . " /* getAdminWhere */";
$query .= "\n AND " . SupportSource::admin_show_sql() . " /* admin_show_sql */";
$order = array();
if (SupportUsers::getSetting("group_products")) {
$order[] = "prod.ordering";
}
if (SupportUsers::getSetting("group_departments")) {
$order[] = "dept.title";
}
if (SupportUsers::getSetting("group_cats")) {
$order[] = "cat.title";
}
if (SupportUsers::getSetting("group_pri")) {
$order[] = "pri.ordering DESC";
}
if (SupportUsers::getSetting("group_group")) {
$order[] = "case when grp.groupname is null then 1 else 0 end";
$order[] = "grp.groupname";
}
$ordering = JFactory::getApplication()->getUserStateFromRequest("fss_admin.ordering", "ordering", "");
if ($ordering) {
$order = array();
$ordering = str_replace(".asc", " ASC", $ordering);
$ordering = str_replace(".desc", " DESC", $ordering);
$order[] = $ordering;
} else {
$order[] = "lastupdate DESC";
}
$query .= "\n ORDER BY " . implode(", ", $order);
$session = JFactory::getSession();
$session->set("last_admin_query", (string) $query);
$session->set("last_admin_list", $_SERVER['REQUEST_URI']);
$session->set("last_admin_post", $_POST);
//echo "<br>".str_replace("\n", "<br>", $query)."<br>";
$db->setQuery($query);
$db->query();
$this->ticket_count = $db->getNumRows();
$db->setQuery($query, $this->limitstart, $this->limit);
$this->LoadFromRows($db->loadObjectList());
}
