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