/**
* Function returns Leads grouped by Status
* @param type $data
* @return <Array>
*/
public function getLeadsByStatus($owner, $dateFilter)
{
$db = PearDatabase::getInstance();
$vtigerModel = new Vtiger_Module_Model();
$ownerSql = $vtigerModel->getOwnerWhereConditionForDashBoards($owner);
$currentUser = Users_Record_Model::getCurrentUserModel();
$module = 'Leads';
$instance = CRMEntity::getInstance($module);
$securityParameter = $instance->getUserAccessConditionsQuerySR($module, $currentUser);
$leadsClosed = Settings_MarketingProcesses_Module_Model::getConfig('lead');
if (!empty($ownerSql)) {
$ownerSql = ' AND ' . $ownerSql;
}
$response = [];
$params = array();
if (!empty($dateFilter)) {
$dateFilterSql = ' AND createdtime BETWEEN ? AND ? ';
//client is not giving time frame so we are appending it
$params[] = $dateFilter['start'] . ' 00:00:00';
$params[] = $dateFilter['end'] . ' 23:59:59';
}
$sql = 'SELECT COUNT(*) as count, CASE WHEN vtiger_leadstatus.leadstatus IS NULL OR vtiger_leadstatus.leadstatus = "" THEN "" ELSE
vtiger_leadstatus.leadstatus END AS leadstatusvalue
FROM vtiger_leaddetails
INNER JOIN vtiger_crmentity
ON vtiger_leaddetails.leadid = vtiger_crmentity.crmid
AND deleted=0 AND converted = 0 ' . $ownerSql . ' ' . $dateFilterSql . 'INNER JOIN vtiger_leadstatus ON vtiger_leaddetails.leadstatus = vtiger_leadstatus.leadstatus ';
if ($securityParameter != '') {
$sql .= $securityParameter;
}
if (!empty($leadsClosed['status'])) {
$leadStatusSearch = implode("','", $leadsClosed['status']);
$sql .= " AND vtiger_leaddetails.leadstatus NOT IN ('{$leadStatusSearch}')";
}
$sql .= ' GROUP BY leadstatusvalue ORDER BY vtiger_leadstatus.sortorderid ';
$result = $db->pquery($sql, $params);
$response = array();
$numRows = $db->num_rows($result);
if ($numRows > 0) {
for ($i = 0; $i < $numRows; $i++) {
$row = $db->query_result_rowdata($result, $i);
$data[$i]['label'] = vtranslate($row['leadstatusvalue'], 'Leads');
$ticks[$i][0] = $i;
$ticks[$i][1] = vtranslate($row['leadstatusvalue'], 'Leads');
$data[$i]['data'][0][0] = $i;
$data[$i]['data'][0][1] = $row['count'];
$name[] = $row['leadstatusvalue'];
}
$response['chart'] = $data;
$response['ticks'] = $ticks;
$response['name'] = $name;
}
return $response;
}
/**
* Function returns Tickets grouped by Status
* @param type $data
* @return <Array>
*/
public function getTicketsByStatus($owner)
{
$db = PearDatabase::getInstance();
$vtigerModel = new Vtiger_Module_Model();
$ownerSql = $vtigerModel->getOwnerWhereConditionForDashBoards($owner);
$ticketStatus = Settings_SupportProcesses_Module_Model::getTicketStatusNotModify();
$params = array();
$currentUser = Users_Record_Model::getCurrentUserModel();
$module = 'HelpDesk';
$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;
}
