/** * 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; }