function &getTickets() { $db = JFactory::getDBO(); $user = JFactory::getUser(); $userid = $user->get('id'); $uidlist = $this->getUIDS($userid); $tidlist = $this->getTIDS($userid); $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 #__fss_ticket_ticket as t "; $query .= " LEFT JOIN #__fss_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 #__users as au ON t.admin_id = au.id "; $query .= " LEFT JOIN #__fss_ticket_dept as dept ON t.ticket_dept_id = dept.id "; $query .= " LEFT JOIN #__fss_ticket_cat as cat ON t.ticket_cat_id = cat.id "; $query .= " LEFT JOIN #__fss_prod as prod ON t.prod_id = prod.id "; $query .= " LEFT JOIN #__fss_ticket_pri as pri ON t.ticket_pri_id = pri.id "; $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 "; $query .= " LEFT JOIN #__fss_ticket_group as grp ON grp.id = mem.group_id "; // add product, department and category $query .= " WHERE ( t.user_id IN (" . implode(", ", $uidlist) . ") OR t.id IN (" . implode(", ", $tidlist) . ") ) "; $query .= " AND " . SupportSource::user_list_sql(); $tickets = FSS_Input::getCmd('tickets', 'open'); if (FSS_Settings::get('support_simple_userlist_tabs')) { $tickets = "all"; } if (FSS_Input::getCmd('search_all')) { $tickets = ""; } if ($tickets == 'open') { $allopen = FSS_Ticket_Helper::GetStatusIDs("is_closed", true); // tickets that arent closed $query .= " AND ticket_status_id IN ( " . implode(", ", $allopen) . ") "; } if ($tickets == '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 $query .= " AND ticket_status_id IN ( " . implode(", ", $allopen) . ") "; } else { if ($tickets > 0) { $statuss = SupportHelper::getStatuss(false); $status_list = array(); $status_list[] = (int) $tickets; foreach ($statuss as $status) { if ($status->combine_with == (int) $tickets) { $status_list[] = $status->id; } } $query .= " AND ticket_status_id IN (" . implode(", ", $status_list) . ")"; } } $search = FSS_Input::getString('search'); if ($search != "") { FSS_Helper::AllowCache(); // We have the nearly full query here, so use it to get a list of ticket ids $db->setQuery($query); $recs = $db->loadObjectList(); $ids = array(); $ids[] = 0; foreach ($recs as $rec) { $ids[] = $rec->id; } $mode = ""; if (FSS_Helper::contains($search, array('*', '+', '-', '<', '>', '(', ')', '~', '"'))) { $mode = "IN BOOLEAN MODE"; } $msgsrch = "SELECT ticket_ticket_id FROM #__fss_ticket_messages WHERE ticket_ticket_id IN (" . implode(", ", $ids) . ") AND admin < 3 AND "; $msgsrch .= " MATCH (body) AGAINST ('" . $db->escape($search) . "' {$mode}) "; $db->setQuery($msgsrch); $results = $db->loadObjectList(); $ids = array(); $ids[] = 0; foreach ($results as $rec) { $ids[] = $rec->ticket_ticket_id; } // search custom fields that are set to be searched $fields = FSSCF::GetAllCustomFields(true); foreach ($fields as $field) { if (!$field["basicsearch"]) { continue; } if ($field['permissions'] > 1 && $field['permissions'] < 5) { continue; } $fieldid = $field['id']; if ($field['type'] == "checkbox") { if ($search == "1") { $search = "on"; } else { $search = ""; } } if ($field['peruser']) { continue; } if ($field['type'] == "plugin") { // try to do a plugin based search $data = array(); foreach ($field['values'] as $item) { list($key, $value) = explode("=", $item, 2); $data[$key] = $value; } if (array_key_exists("plugin", $data)) { $plugins = FSSCF::get_plugins(); if (array_key_exists($data['plugin'], $plugins)) { $po = $plugins[$data['plugin']]; if (method_exists($po, "Search")) { $res = $po->Search($data['plugindata'], $search, false, false); if ($res !== false) { foreach ($res as $item) { $ids[] = (int) $item->ticket_id; } continue; } } } } } $qry = "SELECT ticket_id FROM #__fss_ticket_field WHERE field_id = '" . FSSJ3Helper::getEscaped($db, $fieldid) . "' AND value LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%'"; $db->setQuery($qry); $data = $db->loadObjectList(); foreach ($data as $item) { $id = (int) $item->ticket_id; if ($id > 0) { $ids[] = $id; } } } //"MATCH (question, answer) AGAINST ('" . $db->escape($search) . "')" $query .= " AND ( t.id IN (" . implode(", ", $ids) . ") OR MATCH (t.title) AGAINST ('" . $db->escape($search) . "' {$mode}) OR t.reference LIKE '%" . $db->escape($search) . "%' ) "; } $order = FSS_Input::getCmd('order'); $order_dir = FSS_Input::getCmd('order_dir', 'asc'); $order_dir_allowed = array('asc', 'desc'); if (!in_array($order_dir, $order_dir_allowed)) { $order_dir = 'asc'; } $order_allowed = array('t.title', 'lastupdate', 'status', 'assigned', 'lastupdate', 'u.name'); if (!in_array($order, $order_allowed)) { $order = ''; } if ($order != "") { $query .= " ORDER BY {$order} {$order_dir}"; } else { $query .= " ORDER BY lastupdate DESC "; } $mainframe = JFactory::getApplication(); $limit = $mainframe->getUserStateFromRequest('global.list.limit_ticket', 'limit', FSS_Settings::Get('ticket_per_page'), 'int'); $limitstart = FSS_Input::getInt('limitstart'); $limitstart = $limit != 0 ? floor($limitstart / $limit) * $limit : 0; $db->setQuery($query); $db->query(); //echo $query . "<br>"; $count = $db->getNumRows(); $result['pagination'] = new JPaginationJs($count, $limitstart, $limit); $db->setQuery($query, $limitstart, $limit); $result['tickets'] = $db->loadObjectList(); foreach ($result['tickets'] as &$ticket) { $fields = FSSCF::GetCustomFields($ticket->id, $ticket->prod_id, $ticket->ticket_dept_id); $values = FSSCF::GetTicketValues($ticket->id, $ticket); $ticket->fields = array(); foreach ($fields as &$field) { $ticket->fields[$field['id']] = array(); $ticket->fields[$field['id']]['name'] = $field['description']; $ticket->fields[$field['id']]['value'] = ''; if (isset($values[$field['id']])) { $ticket->fields[$field['id']]['value'] = $values[$field['id']]['value']; } } } return $result; }
private function searchFields(&$wherebits) { // search custom fields that are set to be searched $fields = FSSCF::GetAllCustomFields(true); foreach ($fields as $field) { if (!$field['basicsearch']) { continue; } if (!$field['peruser']) { continue; } $fieldid = $field['id']; $search = FSS_Input::getString('search'); if ($field['type'] == "checkbox") { if ($search == "1") { $search = "on"; } else { $search = ""; } } if ($field['type'] == "plugin") { // try to do a plugin based search $data = array(); foreach ($field['values'] as $item) { list($key, $value) = explode("=", $item, 2); $data[$key] = $value; } if (array_key_exists("plugin", $data)) { $plugins = FSSCF::get_plugins(); if (array_key_exists($data['plugin'], $plugins)) { $po = $plugins[$data['plugin']]; if (method_exists($po, "Search")) { $res = $po->Search($data['plugindata'], $search, true); if ($res !== false) { $wherebits[] = $this->IDsToWhere($res, "id", "user_id") . " /* Per User Plugin - " . $field['id'] . " */"; continue; } } } } } $qry = "SELECT user_id FROM #__fss_ticket_user_field WHERE field_id = '" . FSSJ3Helper::getEscaped($db, $fieldid) . "' AND value LIKE '%" . FSSJ3Helper::getEscaped($db, $search) . "%'"; $db->setQuery($qry); $res = $db->loadObjectList(); $wherebits[] = $this->IDsToWhere($res, "id", "user_id") . " /* Per User CF - " . $field['id'] . " */"; } }
function display($tpl = null) { $this->comments = new FSS_Comments(null, null); if (JRequest::getString('task') == "prods") { return $this->displayProds(); } if (JRequest::getString('task') == "depts") { return $this->displayDepts(); } if (JRequest::getString('task') == "plugin_form") { return $this->pluginForm(); } $field = $this->get('Data'); $isNew = $field->id < 1; $db = JFactory::getDBO(); $text = $isNew ? JText::_("NEW") : JText::_("EDIT"); JToolBarHelper::title(JText::_("FIELD") . ': <small><small>[ ' . $text . ' ]</small></small>', 'fss_customfields'); JToolBarHelper::custom('translate', 'translate', 'translate', 'Translate', false); JToolBarHelper::spacer(); JToolBarHelper::apply(); JToolBarHelper::save(); JToolBarHelper::save2new(); if ($isNew) { JToolBarHelper::cancel(); } else { // for existing items the button is renamed `close` JToolBarHelper::cancel('cancel', 'Close'); } FSSAdminHelper::DoSubToolbar(); // User product selection $this->assign('allprod', JHTML::_('select.booleanlist', 'allprods', array('class' => "inputbox", 'size' => "1", 'onclick' => "DoAllProdChange();"), intval($field->allprods))); $query = "SELECT * FROM #__fss_prod ORDER BY title"; $db->setQuery($query); $products = $db->loadObjectList(); $query = "SELECT * FROM #__fss_field_prod WHERE field_id = " . FSSJ3Helper::getEscaped($db, $field->id); $db->setQuery($query); $selprod = $db->loadAssocList('prod_id'); $this->assign('allprods', $field->allprods); $prodcheck = ""; foreach ($products as $product) { $checked = false; if (array_key_exists($product->id, $selprod)) { $prodcheck .= "<input type='checkbox' name='prod_" . $product->id . "' checked />" . $product->title . "<br>"; } else { $prodcheck .= "<input type='checkbox' name='prod_" . $product->id . "' />" . $product->title . "<br>"; } } $this->products = $prodcheck; // field permissions $fieldperms[] = JHTML::_('select.option', '0', JText::_("USER_CAN_SEE_AND_EDIT"), 'id', 'title'); $fieldperms[] = JHTML::_('select.option', '1', JText::_("USER_CAN_SEE_ONLY_ADMIN_CAN_EDIT"), 'id', 'title'); $fieldperms[] = JHTML::_('select.option', '2', JText::_("ONLY_ADMIN_CAN_SEE_AND_EDIT"), 'id', 'title'); $fieldperms[] = JHTML::_('select.option', '4', JText::_("ONLY_SHOW_ON_TICKET_CREATE"), 'id', 'title'); $fieldperms[] = JHTML::_('select.option', '5', JText::_("USER_CAN_CREATE_AND_SEE_ADMIN_CAN_EDIT"), 'id', 'title'); $this->fieldperm = JHTML::_('select.genericlist', $fieldperms, 'permissions', 'class="inputbox" size="1"', 'id', 'title', $field->permissions); $whichusers[] = JHTML::_('select.option', '0', JText::_("All Users"), 'id', 'title'); $whichusers[] = JHTML::_('select.option', '1', JText::_("Registered Only"), 'id', 'title'); $whichusers[] = JHTML::_('select.option', '2', JText::_("Unregistered Only"), 'id', 'title'); $this->whichusers = JHTML::_('select.genericlist', $whichusers, 'reghide', 'class="inputbox" size="1"', 'id', 'title', $field->reghide); // User department selection $this->assign('alldept', JHTML::_('select.booleanlist', 'alldepts', array('class' => "inputbox", 'size' => "1", 'onclick' => "DoAllDeptChange();"), intval($field->alldepts))); $query = "SELECT * FROM #__fss_ticket_dept ORDER BY title"; $db->setQuery($query); $departments = $db->loadObjectList(); $query = "SELECT * FROM #__fss_field_dept WHERE field_id = " . FSSJ3Helper::getEscaped($db, $field->id); $db->setQuery($query); $seldept = $db->loadAssocList('ticket_dept_id'); $this->assign('alldepts', $field->alldepts); $deptcheck = ""; foreach ($departments as $department) { $checked = false; if (array_key_exists($department->id, $seldept)) { $deptcheck .= "<input type='checkbox' name='dept_" . $department->id . "' checked />" . $department->title . "<br>"; } else { $deptcheck .= "<input type='checkbox' name='dept_" . $department->id . "' />" . $department->title . "<br>"; } } $this->departments = $deptcheck; // get field values $query = "SELECT value, data FROM #__fss_field_values WHERE field_id = " . FSSJ3Helper::getEscaped($db, $field->id); $db->setQuery($query); $values = $db->loadObjectList(); if ($field->type == "radio" || $field->type == "combo") { $res = ""; foreach ($values as $value) { $value = $value->value; if (strpos($value, "|") == 2) { $value = substr($value, 3); } elseif (strpos($value, "|") == 3) { $value = substr($value, 4); } $res .= $value . "\n"; } $this->values = $res; } else { $this->assign('values', ''); } $area_width = 60; $area_height = 4; $text_min = 0; $text_max = 60; $text_size = 40; $plugin_name = ''; $plugin_data = ''; foreach ($values as $value) { $bits = explode("=", $value->value, 2); if (count($bits == 2)) { if ($bits[0] == "area_width") { $area_width = $bits[1]; } if ($bits[0] == "area_height") { $area_height = $bits[1]; } if ($bits[0] == "text_min") { $text_min = $bits[1]; } if ($bits[0] == "text_max") { $text_max = $bits[1]; } if ($bits[0] == "text_size") { $text_size = $bits[1]; } if ($bits[0] == "plugin") { $plugin_name = $bits[1]; } if ($bits[0] == "plugindata") { $plugin_data = $bits[1]; } } if ($value->data) { $plugin_data = $value->data; } } $this->field = $field; $this->area_width = $area_width; $this->area_height = $area_height; $this->text_min = $text_min; $this->text_max = $text_max; $this->text_size = $text_size; // load plugin list $plugins = array(); $this->plugins = FSSCF::get_plugins(); $pllist = array(); $pllist[] = JHTML::_('select.option', '', JText::_("Select a plugin"), 'id', 'title'); foreach ($this->plugins as $id => &$plugins) { $pllist[] = JHTML::_('select.option', $id, $plugins->name, 'id', 'title'); } $this->pllist = JHTML::_('select.genericlist', $pllist, 'plugin', 'id="plugin" class="inputbox" size="1" onchange="plugin_changed()"', 'id', 'title', $plugin_name); $this->plugin_form = ""; if ($plugin_name != '') { if (array_key_exists($plugin_name, $this->plugins)) { $plugin = $this->plugins[$plugin_name]; $this->plugin_form = $plugin->DisplaySettings($plugin_data); } } $idents = array(); $idents[] = JHTML::_('select.option', '0', JText::_("TICKETS"), 'id', 'title'); $idents[] = JHTML::_('select.option', '999', JText::_("ALL_COMMENTS"), 'id', 'title'); $db = JFactory::getDBO(); foreach ($this->comments->handlers as $handler) { $idents[] = JHTML::_('select.option', $handler->ident, $handler->GetLongDesc(), 'id', 'title'); } $this->ident = JHTML::_('select.genericlist', $idents, 'ident', ' class="inputbox" size="1" onchange="ident_changed()"', 'id', 'title', $field->ident); parent::display($tpl); }