/**
* Get monthly commission data for member
* @param $id to search for
* @return mixed $results
*/
public function getMonthlyCommissionData($id)
{
//get current month
$current_month = DateHelper::formatDBDate(date('Y-m-01 00:00:00'));
//get weeks in month
$weeks = DateHelper::getWeeksInMonth($current_month);
//get stage id to filter deals by
$won_stage_ids = DealHelper::getWonStages();
//gen query
$results = array();
foreach ($weeks as $week) {
$start_date = $week['start_date'];
$end_date = $week['end_date'];
//flush query
$query = $this->db->getQuery(true)->select("d.owner_id, SUM(d.amount) AS y")->from("#__deals AS d")->where("d.stage_id IN (" . implode(',', $won_stage_ids) . ")")->where("d.modified >= '{$start_date}'")->where("d.modified < '{$end_date}'")->where("d.modified IS NOT NULL")->where("d.owner_id={$id}")->group("d.owner_id")->where("d.published>0");
$results[] = $this->db->setQuery($query)->loadAssoc();
}
//clean data for commission rate
foreach ($results as $key => $result) {
$commission_rate = UsersHelper::getCommissionRate($result['owner_id']);
$results[$key]['y'] = (int) $result['y'] * ($commission_rate / 100);
}
return $results;
}
/**
* Get Monthly Revenue
* @param $access_type we want to filter by 'member','team','company'
* @param $access_id the id of the $access_type we want to filter by
* @return mixed $results
*/
public function getMonthlyRevenue($access_type = null, $access_id = null)
{
//get db
$db = JFactory::getDBO();
$query = $db->getQuery(true);
//get current month
$current_month = DateHelper::formatDBDate(date('Y-m-01 00:00:00'));
//get weeks in month
$weeks = DateHelper::getWeeksInMonth($current_month);
//get stage id to filter deals by
$won_stage_ids = DealHelper::getWonStages();
//gen query
$data = new \stdClass();
$data->labels = array();
$data->datasets = array();
$totals = array();
foreach ($weeks as $week) {
$start_date = $week['start_date'];
$end_date = $week['end_date'];
$weekDate = new \DateTime($start_date . ' +2 day');
$data->labels[] = TextHelper::_('COBALT_WEEK') . ' ' . $weekDate->format("W");
//flush query
$query = $db->getQuery(true);
//gen query string
$query->select("SUM(d.amount)");
$query->from("#__deals AS d");
$query->where("d.stage_id IN (" . implode(',', $won_stage_ids) . ")");
$query->where("d.modified >= '{$start_date}'");
$query->where("d.modified < '{$end_date}'");
$query->where("d.modified IS NOT NULL");
//sort by published deals
$query->where("d.published > 0");
//filter by owner type
if ($access_type != 'company') {
//team sorting
if ($access_type == 'team') {
//get team members
$team_members = UsersHelper::getTeamUsers($access_id);
$query .= " AND d.owner_id IN (";
//loop to make string
foreach ($team_members as $key => $member) {
$query .= "'" . $member['id'] . "',";
}
$query = substr($query, 0, -1);
$query .= ") ";
}
//member filter
if ($access_type == 'member') {
$query->where("d.owner_id = {$access_id}");
}
}
//return results
$db->setQuery($query);
$total = (int) $db->loadResult();
if (!$total) {
$total = 0;
}
$totals[] = $total;
}
$data->datasets[0] = new \stdClass();
$data->datasets[0]->data = $totals;
$data->datasets[0]->label = '';
$data->datasets[0]->fillColor = "rgba(151,187,205,0.5)";
$data->datasets[0]->strokeColor = "rgba(151,187,205,0.8)";
$data->datasets[0]->pointColor = "rgba(151,187,205,0.75)";
$data->datasets[0]->pointStrokeColor = "rgba(151,187,205,1)";
//return results
return $data;
}
