Esempio n. 1
0
 /**
  * Get sources for return on investment reports
  * @param none
  * @return mixed $results
  */
 public function getRoiSources()
 {
     //get DBO
     $db = JFactory::getDBO();
     $query = $db->getQuery(true);
     //construct query string
     $query->select("s.id,s.name,count(d.id) as number_of_deals,sum(d.amount) as revenue,s.type,s.cost");
     $query->select("IF ( s.type <> 'per', ( ( ( ( sum(d.amount) - s.cost ) / s.cost ) * 100 ) ), ( ( sum(d.amount) - ( s.cost * count(d.id) ) ) / ( s.cost * count(d.id) ) * 100 ) ) AS roi");
     $query->from("#__sources AS s");
     //left join data
     $won_stage_ids = DealHelper::getWonStages();
     $query->leftJoin("#__deals AS d ON d.source_id = s.id AND d.stage_id IN (" . implode(',', $won_stage_ids) . ") AND d.published=1 AND d.archived=0");
     $query->leftJoin("#__users AS u ON u.id = d.owner_id");
     //set our sorting direction if set via post
     $query->order($this->getState('Source.filter_order') . ' ' . $this->getState('Source.filter_order_Dir'));
     //group data
     $query->group("s.id");
     if ($this->_id) {
         if (is_array($this->_id)) {
             $query->where("s.id IN (" . implode(',', $this->_id) . ")");
         } else {
             $query->where("s.id={$this->_id}");
         }
     }
     //filter based on member access roles
     $user_id = UsersHelper::getUserId();
     $member_role = UsersHelper::getRole();
     $team_id = UsersHelper::getTeamId();
     if ($member_role != 'exec') {
         if ($member_role == 'manager') {
             $query->where("u.team_id={$team_id}");
         } else {
             $query->where("(d.owner_id={$user_id})");
         }
     }
     //set query and load results
     $db->setQuery($query);
     $results = $db->loadAssocList();
     return $results;
 }
Esempio n. 2
0
 /**
  * Get lead sources from database where we have associated won deals and grab amount from deals
  * @param $access_type to filter by 'member','team','company'
  * @param $access_id id of $access_type to filter by
  * @return mixed $results
  */
 public function getLeadSources($access_type = null, $access_id = null)
 {
     //get won stage id so we know what stage to filter by for the deals
     $won_stage_ids = DealHelper::getWonStages();
     $query = $this->db->getQuery(true);
     //gen query
     $query->select("s.name,SUM(d.amount) as y");
     $query->from("#__people AS p");
     //left join people that have won deals
     $query->leftJoin("#__sources AS s ON s.id = p.source_id");
     $query->leftJoin("#__people_cf AS dpcf ON dpcf.person_id = p.id AND dpcf.association_type='deal'");
     $query->leftJoin("#__deals AS d ON d.id = dpcf.association_id AND d.published>0");
     //filter where we have associated source ids and the person is a lead contact
     $query->where("p.source_id <> 0");
     $query->where("p.type='lead'");
     //filter by won deals
     $won_stage_ids[] = 0;
     $query->where("d.stage_id IN(" . implode(',', $won_stage_ids) . ")");
     $query->where("p.published=" . $this->published);
     //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
             $query .= "0,";
             foreach ($team_members as $key => $member) {
                 $query .= "'" . $member['id'] . "',";
             }
             $query = substr($query, 0, -1);
             $query .= ") ";
         }
         //member sorting
         if ($access_type == 'member') {
             $query .= " AND d.owner_id={$access_id} ";
         }
     }
     //group by source ids
     $query .= " GROUP BY s.id";
     $results = $this->db->setQuery($query)->loadAssocList();
     if (count($results) > 0) {
         foreach ($results as $key => $source) {
             $results[$key]['y'] = (int) $source['y'];
             $results[$key]['data'] = array((int) $source['y']);
         }
     }
     return $results;
 }
Esempio n. 3
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;
 }
Esempio n. 4
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;
 }
Esempio n. 5
0
 /**
  * Get leaderboard info
  * @param  mixed $leaderboard leaderboard object
  * @return mixed $results leaderboard information
  */
 public function leaderboardInfo($leaderboard)
 {
     //get db
     $db = JFactory::getDBO();
     $query = $db->getQuery(true);
     //get won stage id
     $won_stage_ids = DealHelper::getWonStages();
     //assign start_date and end_date
     $start_date = $leaderboard['start_date'];
     $end_date = $leaderboard['end_date'];
     //select members from database and join essential data
     //get members
     $query->select("u.id,u.first_name,u.last_name");
     $query->from("#__users AS u");
     //left join data
     //win_cash
     if ($leaderboard['goal_type'] == 'win_cash') {
         $query->select("SUM(d.amount) AS cash_won");
         $query->leftJoin("#__deals AS d ON d.owner_id = u.id AND d.stage_id IN (" . implode(",", $won_stage_ids) . ") AND d.modified >= '{$start_date}' AND d.modified <= '{$end_date}' AND d.published>0");
         $query->order('SUM(d.amount) desc');
     }
     //win_deals
     if ($leaderboard['goal_type'] == 'win_deals') {
         $query->select("COUNT(d.id) AS deals_won");
         $query->leftJoin("#__deals AS d ON d.owner_id = u.id AND d.stage_id IN (" . implode(",", $won_stage_ids) . ") AND d.modified >= '{$start_date}' AND d.modified <= '{$end_date}' AND d.published>0");
         $query->order('COUNT(d.id) desc');
     }
     //move_deals
     if ($leaderboard['goal_type'] == 'move_deals') {
         $query->select("COUNT(d.id) AS deals_moved");
         $query->leftJoin("#__deals AS d ON d.owner_id = u.id AND d.stage_id=" . $leaderboard['stage_id'] . " AND d.modified >= '{$start_date}' AND d.modified <= '{$end_date}' AND d.published>0");
         $query->order('COUNT(d.id) desc');
     }
     //complete_tasks
     if ($leaderboard['goal_type'] == 'complete_tasks') {
         $query->select("COUNT(e.id) AS tasks_completed");
         $query->leftJoin("#__events AS e ON e.assignee_id = u.id AND e.completed=1 AND e.category_id=" . $leaderboard['category_id'] . " AND e.modified >= '{$start_date}' AND e.modified <= '{$end_date}' AND e.published>0");
         $query->order('COUNT(e.id) desc');
     }
     //write_notes
     if ($leaderboard['goal_type'] == 'write_notes') {
         $query->select("COUNT(n.id) AS notes_written");
         $query->leftJoin("#__notes AS n ON n.owner_id = u.id AND n.category_id=" . $leaderboard['category_id'] . " AND n.created >= '{$start_date}' AND n.created <= '{$end_date}' AND n.published>0");
         $query->order('COUNT(n.id) desc');
     }
     //create_deals
     if ($leaderboard['goal_type'] == 'create_deals') {
         $query->select("COUNT(d.id) AS deals_created");
         $query->leftJoin("#__deals AS d ON d.owner_id = u.id AND d.created >= '{$start_date}' AND d.created <= '{$end_date}' AND d.published>0");
         $query->order('COUNT(d.id) desc');
     }
     //switch depending on leaderboard type
     switch ($leaderboard['assigned_type']) {
         case "team":
             $query->where("u.team_id=" . $leaderboard['assigned_id']);
             break;
         case "member":
             $query->where("u.id=" . $leaderboard['assigned_id']);
             break;
     }
     //return results
     $query->group("u.id");
     $db->setQuery($query);
     $results = $db->loadAssocList();
     return $results;
 }