protected function createProcessStatusChartData($filter) { $seed_processes = BeanFactory::newBean('pmse_Project'); $qp = new SugarQuery(); $qp->from($seed_processes); $qp->select->field('id'); $qp->select->field('name'); $processes = $qp->execute(); $process_map = array(); for ($i = 0; $i < sizeof($processes); $i++) { $processes[$i]['total'] = 0; $processes[$i]['status'] = array('IN PROGRESS' => 0, 'COMPLETED' => 0, 'CANCELLED' => 0, 'ERROR' => 0, 'TERMINATED' => 0); $process_map[$processes[$i]['id']] = $i; } $seed = BeanFactory::newBean('pmse_Inbox'); // creating the sugar query object $q = new SugarQuery(); // adding the seed bean $q->from($seed); // joining the users table $q->joinRaw('INNER JOIN pmse_bpmn_process ON pmse_bpmn_process.id=pmse_inbox.pro_id'); $q->select->field("cas_status"); $q->select->fieldRaw("COUNT(*) as total"); $q->select->fieldRaw("prj_id"); $q->groupByRaw('pro_id, cas_status'); if ($filter !== 'all') { $q->where()->addRaw("pmse_project.id = '" . $filter . "'"); } $data_bean = $q->execute(); foreach ($data_bean as $row) { $index = $process_map[$row['prj_id']]; $processes[$index]['status'][$row['cas_status']] = (int) $row['total']; $processes[$index]['total'] += $row['total']; } $labels = array(); $values = array(); $in_progress = array(); $completed = array(); $cancelled = array(); $terminated = array(); $error = array(); for ($i = 0; $i < sizeof($processes); $i++) { $labels[] = array("group" => $i + 1, "l" => $processes[$i]['name']); $values[] = array("group" => $i + 1, "t" => $processes[$i]['total']); $in_progress[] = array("series" => 0, "x" => $i + 1, "y" => $processes[$i]['status']['IN PROGRESS']); $completed[] = array("series" => 1, "x" => $i + 1, "y" => $processes[$i]['status']['COMPLETED']); $cancelled[] = array("series" => 2, "x" => $i + 1, "y" => $processes[$i]['status']['CANCELLED']); $terminated[] = array("series" => 3, "x" => $i + 1, "y" => $processes[$i]['status']['TERMINATED']); $error[] = array("series" => 4, "x" => $i + 1, "y" => $processes[$i]['status']['ERROR']); } return array("properties" => array("labels" => $labels, "values" => $values), "data" => array(array("key" => translate("LBL_PMSE_IN_PROGESS_STATUS"), "type" => "bar", "color" => '#176de5', "values" => $in_progress), array("key" => translate("LBL_PMSE_COMPLETED_STATUS"), "type" => "bar", "color" => '#33800d', "values" => $completed), array("key" => translate("LBL_PMSE_CANCELLED_STATUS"), "type" => "bar", "color" => '#e5a117', "values" => $cancelled), array("key" => translate("LBL_PMSE_TERMINATED_STATUS"), "type" => "bar", "color" => '#6d17e5', "values" => $terminated), array("key" => translate("LBL_PMSE_ERROR_STATUS"), "type" => "bar", "color" => '#E61718', "values" => $error))); }