/**
* Get yearly commission data for user
* @param int $id to search for
* @return mixed $results
*/
public function getYearlyCommissionData($id)
{
//get current year and months to loop through
$current_year = DateHelper::formatDBDate(date('Y-01-01 00:00:00'));
$month_names = DateHelper::getMonthNames();
$months = DateHelper::getMonthDates();
//get stage id to filter deals by
$won_stage_ids = DealHelper::getWonStages();
//gen query
$results = array();
foreach ($months as $month) {
$start_date = $month['date'];
$end_date = DateHelper::formatDBDate(date('Y-m-d 00:00:00', strtotime("{$start_date} + 1 months")));
//flush the query
$query = $this->db->getQuery(true)->select("d.owner_id,d.modified,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 Yearly Revenue
* @param $access_type we wish to filter by 'member','team','company'
* @param $access_id the id of the $access_type we wish to filter by
* @return mixed $results
*/
public function getYearlyRevenue($access_type = null, $access_id = null)
{
//get db
$db = JFactory::getDBO();
$query = $db->getQuery(true);
//get current year and months to loop through
$current_year = DateHelper::formatDBDate(date('Y-01-01 00:00:00'));
$month_names = DateHelper::getMonthNames();
$months = DateHelper::getMonthDates();
//get stage id to filter deals by
$won_stage_ids = DealHelper::getWonStages();
//gen query
$results = array();
foreach ($months as $month) {
$start_date = $month['date'];
$end_date = DateHelper::formatDBDate(date('Y-m-d 00:00:00', strtotime("{$start_date} + 1 months")));
//flush the query
$query = $db->getQuery(true);
//generate query string
$query->select("d.modified,SUM(d.amount) AS y");
$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 access 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}");
}
}
//get results and assign to month
$db->setQuery($query);
$totals = $db->loadAssoc();
if (!$totals) {
$totals = array('y' => 0);
}
$totals['y'] = (int) $totals['y'];
$results[] = $totals;
}
//return
return $results;
}