/**
* Method to get graph deal information
* @param $type type of deal to filter by, values 'stage','status'
* @param $access_type to search by 'company','team','member'
* @param $access_id the id of the $member_type to search by
*/
public function getGraphDeals($type = null, $access_type = null, $access_id = null)
{
$query = $this->db->getQuery(true);
//search by type
if ($type == 'stage') {
$query->select("count(*) AS value, stage.name AS title")->from("#__deals AS d")->leftJoin("#__stages AS stage ON stage.id = d.stage_id");
}
if ($type == 'status') {
$query->select("count(*) AS value, status.name AS title")->from("#__deals AS d")->leftJoin("#__deal_status AS status ON status.id = d.status_id");
}
//if user is not an executive then there are limitations
if ($access_type != 'company') {
//team sorting
if ($access_type == 'team') {
//get team members
$team_members = UsersHelper::getTeamUsers($access_id);
$members = array();
$members[] = 0;
foreach ($team_members as $key => $member) {
$members[] = $member['id'];
}
$query->where("d.owner_id IN (" . implode(",", $members) . ")");
}
//member sorting
if ($access_type == 'member') {
$query->where("d.owner_id={$access_id}");
}
}
//grouping
if ($type == 'stage') {
$query->where("d.stage_id <> 0 AND d.stage_id = stage.id");
$query->group("d.stage_id");
}
if ($type == 'status') {
$query->where("d.status_id <> 0 AND d.status_id = status.id");
$query->group("d.status_id");
}
if (!is_null($this->archived)) {
$query->where("d.archived=" . $this->archived);
}
$query->where("d.published=" . $this->published);
$results = $this->db->setQuery($query)->loadAssocList();
if (count($results) > 0) {
$max = 0;
// get max value
foreach ($results as $stage) {
if ($stage['value'] > $max) {
$max = (int) $stage['value'];
}
}
//clean results and force datatypes for graph rendering
foreach ($results as $key => $stage) {
// $results[$key]['y'] = (int) $stage['y'];
// $results[$key]['data'] = array((int) $stage['y']);
$results[$key]['value'] = (int) $stage['value'];
$results[$key]['color'] = '#' . CobaltHelper::percent2Color($stage['value'], 200, $max);
$results[$key]['highlight'] = '#' . CobaltHelper::percent2Color($stage['value'], 220, $max);
}
}
return $results;
}