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