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