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