public function process(Vtiger_Request $request)
{
$log = vglobal('log');
$log->debug("Entering Settings_SupportProcesses_Index_View::process() method ...");
$qualifiedModule = $request->getModule(false);
$viewer = $this->getViewer($request);
$ticketStatus = Settings_SupportProcesses_Module_Model::getTicketStatus();
$ticketStatusNotModify = Settings_SupportProcesses_Module_Model::getTicketStatusNotModify();
$viewer->assign('TICKETSTATUSNOTMODIFY', $ticketStatusNotModify);
$viewer->assign('TICKETSTATUS', $ticketStatus);
$viewer->assign('QUALIFIED_MODULE', $request->getModule(false));
$viewer->view('Index.tpl', $qualifiedModule);
$log->debug("Exiting Settings_SupportProcesses_Index_View::process() method ...");
}
/**
* Function returns Tickets grouped by Status
* @param type $data
* @return <Array>
*/
public function getOpenTickets()
{
$db = PearDatabase::getInstance();
$ticketStatus = Settings_SupportProcesses_Module_Model::getTicketStatusNotModify();
$currentUser = Users_Record_Model::getCurrentUserModel();
$module = 'HelpDesk';
$instance = CRMEntity::getInstance($module);
$securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser);
$usersSqlFullName = getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users');
$sql = 'SELECT count(*) AS count, case when (' . $usersSqlFullName . ' not like "") then
' . $usersSqlFullName . ' else vtiger_groups.groupname end as name,
case when (' . $usersSqlFullName . ' not like "") then
vtiger_users.cal_color else vtiger_groups.color end as color, smownerid as id
FROM vtiger_troubletickets
INNER JOIN vtiger_crmentity ON vtiger_troubletickets.ticketid = vtiger_crmentity.crmid
LEFT JOIN vtiger_users ON vtiger_users.id=vtiger_crmentity.smownerid
LEFT JOIN vtiger_groups on vtiger_groups.groupid=vtiger_crmentity.smownerid
WHERE vtiger_crmentity.deleted = 0';
if ($securityParameter != '') {
$sql .= $securityParameter;
}
if (!empty($ticketStatus)) {
$ticketStatusSearch = implode("','", $ticketStatus);
$sql .= " AND vtiger_troubletickets.status NOT IN ('{$ticketStatusSearch}')";
}
$sql .= ' GROUP BY smownerid';
$result = $db->pquery($sql, array());
$moduleModel = Vtiger_Module_Model::getInstance('HelpDesk');
$listViewUrl = $moduleModel->getListViewUrl();
$chartData = array();
for ($i = 0; $i < $db->num_rows($result); $i++) {
$row = $db->query_result_rowdata($result, $i);
$data['id'] = $row['id'];
$data['label'] = trim($row['name']);
$data['data'] = $row['count'];
$data['color'] = $row['color'];
$data['links'] = $listViewUrl . $this->getSearchParams($row['name']);
$chartData[] = $data;
}
return $chartData;
}
/**
* Function returns Tickets grouped by Status
* @param type $data
* @return <Array>
*/
public function getTicketsByStatus($owner)
{
$db = PearDatabase::getInstance();
$module = 'HelpDesk';
$moduleModel = Vtiger_Module_Model::getInstance($module);
$ownerSql = $moduleModel->getOwnerWhereConditionForDashBoards($owner);
$ticketStatus = Settings_SupportProcesses_Module_Model::getTicketStatusNotModify();
$params = array();
$currentUser = Users_Record_Model::getCurrentUserModel();
$instance = CRMEntity::getInstance($module);
$securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser);
$sql = 'SELECT COUNT(*) as count
, priority, vtiger_ticketpriorities.color,
CASE WHEN vtiger_troubletickets.status IS NULL OR vtiger_troubletickets.status = "" THEN "" ELSE vtiger_troubletickets.status END AS statusvalue
FROM
vtiger_troubletickets
INNER JOIN vtiger_crmentity
ON vtiger_troubletickets.ticketid = vtiger_crmentity.crmid AND vtiger_crmentity.deleted=0
INNER JOIN vtiger_ticketstatus
ON vtiger_troubletickets.status = vtiger_ticketstatus.ticketstatus
INNER JOIN vtiger_ticketpriorities
ON vtiger_ticketpriorities.`ticketpriorities` = vtiger_troubletickets.`priority`
WHERE
vtiger_crmentity.`deleted` = 0';
if (!empty($ownerSql)) {
$sql .= ' AND ' . $ownerSql;
}
if (!empty($ticketStatus)) {
$ticketStatusSearch = implode("','", $ticketStatus);
$sql .= " AND vtiger_troubletickets.status NOT IN ('{$ticketStatusSearch}')";
}
if ($securityParameter != '') {
$sql .= $securityParameter;
}
$sql .= ' GROUP BY
statusvalue, priority
ORDER BY
vtiger_ticketstatus.sortorderid';
$result = $db->query($sql);
$response = array();
$priorities = [];
$status = [];
$counter = 0;
$colors = [];
$numRows = $db->num_rows($result);
for ($i = 0; $i < $numRows; $i++) {
$row = $db->query_result_rowdata($result, $i);
$tickets[$row['statusvalue']][$row['priority']] = $row['count'];
if (!array_key_exists($row['priority'], $priorities)) {
$priorities[$row['priority']] = $counter++;
$colors[$row['priority']] = $row['color'];
}
if (!in_array($row['statusvalue'], $status)) {
$status[] = $row['statusvalue'];
}
}
if ($numRows > 0) {
$counter = 0;
$result = array();
foreach ($tickets as $ticketKey => $ticketValue) {
foreach ($priorities as $priorityKey => $priorityValue) {
$result[$priorityValue]['data'][$counter][0] = $counter;
$result[$priorityValue]['label'] = vtranslate($priorityKey, 'HelpDesk');
$result[$priorityValue]['color'] = $colors[$priorityKey];
if ($ticketValue[$priorityKey]) {
$result[$priorityValue]['data'][$counter][1] = $ticketValue[$priorityKey];
} else {
$result[$priorityValue]['data'][$counter][1] = 0;
}
}
$counter++;
}
$ticks = [];
foreach ($status as $key => $value) {
$newArray = [$key, vtranslate($value, 'HelpDesk')];
array_push($ticks, $newArray);
$name[] = $value;
}
$response['chart'] = $result;
$response['ticks'] = $ticks;
$response['name'] = $name;
}
return $response;
}
