function _buildListArray(&$lists, $state)
{
$user = JFactory::getUser();
$db =& JFactory::getDbo();
$dateFormat = HelpdeskProHelper::getConfigValue('date_format');
if (!$user->authorise('core.admin')) {
$managedCategoryIds = HelpdeskProHelper::getTicketCategoryIds($user->get('username'));
if (count($managedCategoryIds)) {
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE id IN (" . implode(',', $managedCategoryIds) . ") AND published=1 ORDER BY ordering";
} elseif (!$user->get('id')) {
$userId = 0;
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE published=1 ORDER BY ordering";
} else {
$userId = $user->get('id');
$email = $user->get('email');
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE published=1 AND id IN (SELECT DISTINCT category_id FROM #__helpdeskpro_tickets AS t WHERE t.user_id={$userId} OR t.email='{$email}') ORDER BY ordering";
}
} else {
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE published=1 ORDER BY ordering";
}
$db->setQuery($sql);
$rows = $db->loadObjectList();
$children = array();
if ($rows) {
// first pass - collect children
foreach ($rows as $v) {
$pt = $v->parent_id;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $v);
$children[$pt] = $list;
}
}
$list = JHTML::_('menu.treerecurse', 0, '', array(), $children, 9999, 0, 0);
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_CATEGORIES'));
foreach ($list as $listItem) {
$options[] = JHTML::_('select.option', $listItem->id, ' ' . $listItem->treename);
}
$lists['cat'] = '<ul class="nav menu">';
foreach ($list as $listItem) {
if ($listItem->id == $state->category_id && isset($_GET['category_id'])) {
$active = ' class="active"';
} else {
$active = '';
}
$lists['cat'] .= '<li' . $active . '><a href="' . JRoute::_('index.php?option=com_helpdeskpro&view=tickets&category_id=' . $listItem->id) . '">' . $listItem->treename . '</a></li>';
}
$lists['cat'] .= '</ul>';
$lists['category_id'] = JHTML::_('select.genericlist', $options, 'category_id', array('option.text.toHtml' => false, 'option.text' => 'text', 'option.value' => 'value', 'list.attr' => 'class="inputbox" ', 'list.select' => $state->category_id));
$options = array();
$options[] = JHtml::_('select.option', -1, JText::_('HDP_SELECT'), 'id', 'title');
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_STATUSES'), 'id', 'title');
$sql = 'SELECT id, title FROM #__helpdeskpro_statuses WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowStatuses = $db->loadObjectList();
$options = array_merge($options, $rowStatuses);
$lists['status_id'] = JHTML::_('select.genericlist', $options, 'status_id', array('option.text.toHtml' => false, 'option.text' => 'title', 'option.key' => 'id', 'list.attr' => 'class="inputbox" onchange="submit();"', 'list.select' => $state->status_id));
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_PRIORITIES'), 'id', 'title');
$sql = 'SELECT id, title FROM #__helpdeskpro_priorities WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowPriorities = $db->loadObjectList();
$options = array_merge($options, $rowPriorities);
$lists['priority_id'] = JHTML::_('select.genericlist', $options, 'priority_id', array('option.text.toHtml' => false, 'option.text' => 'title', 'option.key' => 'id', 'list.attr' => 'class="inputbox" ', 'list.select' => $state->priority_id));
$statusList = array();
foreach ($rowStatuses as $status) {
$statusList[$status->id] = $status->title;
}
$priorityList = array();
foreach ($rowPriorities as $priority) {
$priorityList[$priority->id] = $priority->title;
}
$sql = 'SELECT title FROM #__helpdeskpro_categories WHERE id=' . $state->category_id;
$db->setQuery($sql);
$categoryTitle = $db->loadResult();
$sql = "SELECT config_value FROM #__helpdeskpro_configs WHERE config_key='home_page_text'";
$db->setQuery($sql);
$home_page_text = $db->loadResult();
$this->home_page_text = $home_page_text;
$this->dateFormat = $dateFormat;
$this->statusList = $statusList;
$this->priorityList = $priorityList;
if (isset($_GET['category_id'])) {
$this->category_id = $state->category_id;
} else {
$this->category_id = 0;
}
$this->categoryTitle = $categoryTitle;
return true;
}
function _buildListArray(&$lists, $item)
{
$layout = $this->getLayout();
if ($layout == 'form') {
$this->_buildListArrayForm($lists, $item);
return;
}
$db = JFactory::getDbo();
$config = HelpdeskProHelper::getConfig();
$sql = 'SELECT a.*, b.name FROM #__helpdeskpro_messages AS a LEFT JOIN #__users AS b ON a.user_id=b.id WHERE ticket_id=' . $item->id . ' ORDER BY a.id DESC';
$db->setQuery($sql);
$messages = $db->loadObjectList();
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories";
$db->setQuery($sql);
$rows = $db->loadObjectList();
$children = array();
if ($rows) {
// first pass - collect children
foreach ($rows as $v) {
$pt = $v->parent_id;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $v);
$children[$pt] = $list;
}
}
$categories = JHTML::_('menu.treerecurse', 0, '', array(), $children, 9999, 0, 0);
//Get all custom fields
$sql = 'SELECT * FROM #__helpdeskpro_fields WHERE published=1 AND (category_id = -1 OR id IN (SELECT field_id FROM #__helpdeskpro_field_categories WHERE category_id=' . $item->category_id . ')) ORDER BY ordering ';
$db->setQuery($sql);
$fields = $db->loadObjectList();
$sql = 'SELECT title FROM #__helpdeskpro_statuses WHERE id=' . $item->status_id;
$db->setQuery($sql);
$item->status = $db->loadResult();
$sql = 'SELECT title FROM #__helpdeskpro_priorities WHERE id=' . $item->priority_id;
$db->setQuery($sql);
$item->priority = $db->loadResult();
//Get all custom fields value belong to this support ticket
$sql = 'SELECT field_id, field_value FROM #__helpdeskpro_field_value WHERE ticket_id=' . $item->id;
$db->setQuery($sql);
$rows = $db->loadObjectList();
$fieldValues = array();
if (count($rows)) {
foreach ($rows as $row) {
$fieldValues[$row->field_id] = $row->field_value;
}
}
$sql = 'SELECT id, title FROM #__helpdeskpro_statuses WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowStatuses = $db->loadObjectList();
$sql = 'SELECT id, title FROM #__helpdeskpro_priorities WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowPriorities = $db->loadObjectList();
$dateFormat = HelpdeskProHelper::getConfigValue('date_format');
//Process plugin here
JPluginHelper::importPlugin('helpdeskpro');
$dispatcher =& JDispatcher::getInstance();
//Trigger plugins
$results = $dispatcher->trigger('onViewTicket', array($item));
$this->fields = $fields;
$this->messages = $messages;
$this->fieldValues = $fieldValues;
$this->rowStatuses = $rowStatuses;
$this->rowPriorities = $rowPriorities;
$this->categories = $categories;
$this->dateFormat = $dateFormat;
$this->config = $config;
$this->results = $results;
return true;
}
function _buildListArray(&$lists, $state)
{
$db =& JFactory::getDbo();
$user = JFactory::getUser();
$dateFormat = HelpdeskProHelper::getConfigValue('date_format');
if (!$user->authorise('core.admin')) {
$managedCategoryIds = HelpdeskProHelper::getTicketCategoryIds($user->get('username'));
if (count($managedCategoryIds)) {
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE id IN (" . implode(',', $managedCategoryIds) . ") AND published=1 ORDER BY ordering";
} else {
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE published=1 ORDER BY ordering";
}
} else {
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE published=1 ORDER BY ordering";
}
$db->setQuery($sql);
$rows = $db->loadObjectList();
$children = array();
if ($rows) {
// first pass - collect children
foreach ($rows as $v) {
$pt = $v->parent_id;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $v);
$children[$pt] = $list;
}
}
$list = JHTML::_('menu.treerecurse', 0, '', array(), $children, 9999, 0, 0);
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_CATEGORIES'));
foreach ($list as $listItem) {
$options[] = JHTML::_('select.option', $listItem->id, ' ' . $listItem->treename);
}
$lists['category_id'] = JHTML::_('select.genericlist', $options, 'category_id', array('option.text.toHtml' => false, 'option.text' => 'text', 'option.value' => 'value', 'list.attr' => 'class="inputbox" onchange="submit();"', 'list.select' => $state->category_id));
$options = array();
$options[] = JHtml::_('select.option', -1, JText::_('HDP_SELECT'), 'id', 'title');
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_STATUSES'), 'id', 'title');
$sql = 'SELECT id, title FROM #__helpdeskpro_statuses WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowStatuses = $db->loadObjectList();
$options = array_merge($options, $rowStatuses);
$lists['status_id'] = JHTML::_('select.genericlist', $options, 'status_id', array('option.text.toHtml' => false, 'option.text' => 'title', 'option.key' => 'id', 'list.attr' => 'class="inputbox" onchange="submit();" ', 'list.select' => $state->status_id));
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_PRIORITIES'), 'id', 'title');
$sql = 'SELECT id, title FROM #__helpdeskpro_priorities WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowPriorities = $db->loadObjectList();
$options = array_merge($options, $rowPriorities);
$lists['priority_id'] = JHTML::_('select.genericlist', $options, 'priority_id', array('option.text.toHtml' => false, 'option.text' => 'title', 'option.key' => 'id', 'list.attr' => 'class="inputbox" onchange="submit();" ', 'list.select' => $state->priority_id));
$statusList = array();
foreach ($rowStatuses as $status) {
$statusList[$status->id] = $status->title;
}
$priorityList = array();
foreach ($rowPriorities as $priority) {
$priorityList[$priority->id] = $priority->title;
}
$this->dateFormat = $dateFormat;
$this->statusList = $statusList;
$this->priorityList = $priorityList;
return true;
}
function display($tpl = null)
{
$layout = $this->getLayout();
if ($layout == 'form') {
$this->_displayForm($tpl);
return;
}
$db = JFactory::getDbo();
$user = JFactory::getUser();
$item = $this->get('Data');
if (!$item->id) {
$app = JFactory::getApplication();
$app->redirect('index.php', JText::_('HDP_INVALID_TICKET'));
}
$canAccess = HelpdeskProHelper::checkTicketAccess($item);
$app = JFactory::getApplication();
$pathway = $app->getPathway();
$state = $this->get('State');
$this->state = $state;
$lists = array();
$config = HelpdeskProHelper::getConfig();
$sql = 'SELECT a.*, b.name FROM #__helpdeskpro_messages AS a LEFT JOIN #__users AS b ON a.user_id=b.id WHERE ticket_id=' . $item->id . ' ORDER BY a.id DESC';
$db->setQuery($sql);
$messages = $db->loadObjectList();
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories";
$db->setQuery($sql);
$rows = $db->loadObjectList();
$children = array();
if ($rows) {
// first pass - collect children
foreach ($rows as $v) {
$pt = $v->parent_id;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $v);
$children[$pt] = $list;
}
}
$categories = JHTML::_('menu.treerecurse', 0, '', array(), $children, 9999, 0, 0);
//Get all custom fields
$sql = 'SELECT * FROM #__helpdeskpro_fields WHERE published=1 AND (category_id = -1 OR id IN (SELECT field_id FROM #__helpdeskpro_field_categories WHERE category_id=' . $item->category_id . ')) ORDER BY ordering ';
$db->setQuery($sql);
$fields = $db->loadObjectList();
$sql = 'SELECT title FROM #__helpdeskpro_statuses WHERE id=' . $item->status_id;
$db->setQuery($sql);
$item->status = $db->loadResult();
$sql = 'SELECT title FROM #__helpdeskpro_priorities WHERE id=' . $item->priority_id;
$db->setQuery($sql);
$item->priority = $db->loadResult();
//Get all custom fields value belong to this support ticket
$sql = 'SELECT field_id, field_value FROM #__helpdeskpro_field_value WHERE ticket_id=' . $item->id;
$db->setQuery($sql);
$rows = $db->loadObjectList();
$fieldValues = array();
if (count($rows)) {
foreach ($rows as $row) {
$fieldValues[$row->field_id] = $row->field_value;
}
}
$sql = 'SELECT id, title FROM #__helpdeskpro_statuses WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowStatuses = $db->loadObjectList();
$sql = 'SELECT id, title FROM #__helpdeskpro_priorities WHERE published=1 ORDER BY ordering';
$db->setQuery($sql);
$rowPriorities = $db->loadObjectList();
$dateFormat = HelpdeskProHelper::getConfigValue('date_format');
if ($user->id == $item->user_id || $item->is_ticket_code) {
$isCustomer = 1;
} else {
$isCustomer = 0;
}
if ($isCustomer && $item->status_id == $config->closed_ticket_status) {
$canComment = false;
} else {
$canComment = true;
}
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE published=1 ORDER BY ordering";
$db->setQuery($sql);
$list = $db->loadObjectList();
$lists['cat'] = '<ul class="nav menu">';
foreach ($list as $listItem) {
$lists['cat'] .= '<li><a href="' . JRoute::_('index.php?option=com_helpdeskpro&view=tickets&category_id=' . $listItem->id) . '">' . $listItem->title . '</a></li>';
}
$lists['cat'] .= '</ul>';
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_CATEGORIES'));
foreach ($list as $listItem) {
$options[] = JHTML::_('select.option', $listItem->id, ' ' . $listItem->treename);
}
$lists['category_id'] = JHTML::_('select.genericlist', $options, 'category_id', array('option.text.toHtml' => false, 'option.text' => 'text', 'option.value' => 'value', 'list.attr' => 'class="inputbox" ', 'list.select' => $state->category_id));
$Itemid = JRequest::getInt('Itemid');
$this->fields = $fields;
$this->messages = $messages;
$this->fieldValues = $fieldValues;
$this->rowStatuses = $rowStatuses;
$this->rowPriorities = $rowPriorities;
$this->categories = $categories;
$this->dateFormat = $dateFormat;
$this->config = $config;
$this->item = $item;
$this->lists['cat'] = $lists['cat'];
$this->isCustomer = $isCustomer;
$this->canAccess = $canAccess;
$this->canComment = $canComment;
$this->Itemid = $Itemid;
$sql = 'SELECT title FROM #__helpdeskpro_categories WHERE id=' . $item->category_id;
$db->setQuery($sql);
$categoryTitle = $db->loadResult();
$pathway->addItem($categoryTitle, 'index.php?option=com_helpdeskpro&category_id=' . $item->category_id);
$pathway->addItem($this->item->subject, '');
/* FORM FUNCTION STARTS HERE */
$db =& JFactory::getDBO();
$sql = "SELECT id, parent_id, title FROM #__helpdeskpro_categories WHERE published=1 AND access IN (" . implode(',', $user->getAuthorisedViewLevels()) . ')';
$db->setQuery($sql);
$rows = $db->loadObjectList();
$children = array();
if ($rows) {
// first pass - collect children
foreach ($rows as $v) {
$pt = $v->parent_id;
$list = @$children[$pt] ? $children[$pt] : array();
array_push($list, $v);
$children[$pt] = $list;
}
}
$list = JHTML::_('menu.treerecurse', 0, '', array(), $children, 9999, 0, 0);
$options = array();
$options[] = JHtml::_('select.option', 0, JText::_('HDP_ALL_CATEGORIES'));
foreach ($list as $listItem) {
$options[] = JHTML::_('select.option', $listItem->id, ' ' . $listItem->treename);
}
$lists['category_id'] = JHTML::_('select.genericlist', $options, 'category_id', array('option.text.toHtml' => false, 'option.text' => 'text', 'option.value' => 'value', 'list.attr' => 'class="inputbox" onchange="showFields(this.form);" ', 'list.select' => 0));
$lists['priority_id'] = JHTML::_('select.genericlist', $options, 'priority_id', array('option.text.toHtml' => false, 'option.text' => 'title', 'option.key' => 'id', 'list.attr' => 'class="inputbox" ', 'list.select' => $config->default_ticket_priority_id));
$categoryId = 0;
$jcFields = new JCFields();
if ($jcFields->getTotal()) {
$customField = true;
$fieldArray = JCFields::getAssoc();
$fieldJs = "fields = new Array();\n";
foreach ($fieldArray as $catId => $fieldList) {
$fieldJs .= ' fields[' . $catId . '] = new Array("' . implode('","', $fieldList) . '");' . "\n";
}
$this->assign('fieldJs', $fieldJs);
$fields = $jcFields->renderCustomFields($categoryId, $fieldArray);
$validations = $jcFields->renderJSValidation();
$this->assign('fields', $fields);
$this->assign('validations', $validations);
} else {
$customField = false;
}
$this->assign('customField', $customField);
$this->lists = $lists;
$this->Itemid = $Itemid;
$this->config = $config;
$this->userId = $userId;
$this->lists['category_id'] = $lists['category_id'];
// PARENT IS A PART OF BOTH FUNCTIONS
parent::display($tpl);
}
/**
* Export support tickets into CSV format
*/
function csv_export()
{
$db = JFactory::getDbo();
$dateFormat = HelpdeskProHelper::getConfigValue('date_format');
$categoryId = JRequest::getInt('category_id');
$statusId = JRequest::getInt('status_id');
$priorityId = JRequest::getInt('priority_id');
//Get list statues and id
$sql = 'SElECT id, title FROM #__helpdeskpro_statuses ';
$db->setQuery($sql);
$rowStatuses = $db->loadObjectList();
$statusList = array();
foreach ($rowStatuses as $status) {
$statusList[$status->id] = $status->title;
}
$sql = 'SElECT id, title FROM #__helpdeskpro_priorities ';
$db->setQuery($sql);
$rowPriorities = $db->loadObjectList();
$priorityList = array();
foreach ($rowPriorities as $priority) {
$priorityList[$priority->id] = $priority->title;
}
$where = array();
if ($categoryId > 0) {
$where[] = ' a.category_id = ' . $categoryId;
}
if ($statusId > 0) {
$where[] = ' a.status_id = ' . $statusId;
}
if ($priorityId > 0) {
$where[] = ' a.priority_id = ' . $priorityId;
}
$sql = 'SELECT a.*, b.title AS category_title, c.username AS username FROM #__helpdeskpro_tickets AS a ' . ' LEFT JOIN #__helpdeskpro_categories AS b ' . ' ON a.category_id= b.id ' . ' LEFT JOIN #__users AS c ' . ' ON a.user_id = c.id ' . (count($where) > 0 ? ' WHERE ' . implode(' AND ', $where) : '') . ' ORDER BY id ';
$db->setQuery($sql);
$rows = $db->loadObjectList();
if (count($rows)) {
$results_arr = array();
$results_arr[] = JText::_('HDP_TITLE');
$results_arr[] = JText::_('HDP_MESSAGE');
$results_arr[] = JText::_('HDP_CATEGORY');
$results_arr[] = JText::_('HDP_USER');
$results_arr[] = JText::_('HDP_CREATED_DATE');
$results_arr[] = JText::_('HDP_MODIFIED_DATE');
$results_arr[] = JText::_('HDP_STATUS');
$results_arr[] = JText::_('HDP_PRIORITY');
$results_arr[] = JText::_('HDP_ID');
$csv_output = implode(",", $results_arr);
foreach ($rows as $row) {
$results_arr = array();
$results_arr[] = $row->subject;
$results_arr[] = $row->message;
$results_arr[] = $row->subject;
$results_arr[] = $row->category_title;
if ($row->username) {
$results_arr[] = $row->name . '(' . $row->username . ')';
} else {
$results_arr[] = $row->name;
}
$results_arr[] = $row->email;
$results_arr[] = JHtml::_('date', $row->created_date, $dateFormat);
$results_arr[] = JHtml::_('date', $row->modified_date, $dateFormat);
$results_arr[] = @$statusList[$row->status_id];
$results_arr[] = @$priorityList[$row->priority_id];
$results_arr[] = $row->id;
$csv_output .= "\n\"" . implode("\",\"", $results_arr) . "\"";
}
$csv_output .= "\n";
if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
$UserBrowser = "Opera";
} elseif (ereg('MSIE ([0-9].[0-9]{1,2})', $_SERVER['HTTP_USER_AGENT'])) {
$UserBrowser = "IE";
} else {
$UserBrowser = '';
}
$mime_type = $UserBrowser == 'IE' || $UserBrowser == 'Opera' ? 'application/octetstream' : 'application/octet-stream';
$filename = "tickets";
@ob_end_clean();
ob_start();
header('Content-Type: ' . $mime_type);
header('Expires: ' . gmdate('D, d M Y H:i:s') . ' GMT');
if ($UserBrowser == 'IE') {
header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
header('Cache-Control: must-revalidate, post-check=0, pre-check=0');
header('Pragma: public');
} else {
header('Content-Disposition: attachment; filename="' . $filename . '.csv"');
header('Pragma: no-cache');
}
echo $csv_output;
exit;
}
}
function saveRating($data)
{
$closedStatusId = HelpdeskProHelper::getConfigValue('closed_ticket_status');
$row = JTable::getInstance('helpdeskpro', 'ticket');
$row->load($data['id']);
$row->rating = $data['new_value'];
$row->status_id = $closedStatusId;
$row->store();
return true;
}
