Example #1
0
 /**
  * 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;
 }
Example #2
0
 /**
  * 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;
 }