508 lines
23 KiB
PHP
Executable File
508 lines
23 KiB
PHP
Executable File
<?php
|
|
if (!defined('sugarEntry') || !sugarEntry) {
|
|
die('Not A Valid Entry Point');
|
|
}
|
|
/**
|
|
*
|
|
* SugarCRM Community Edition is a customer relationship management program developed by
|
|
* SugarCRM, Inc. Copyright (C) 2004-2013 SugarCRM Inc.
|
|
*
|
|
* SuiteCRM is an extension to SugarCRM Community Edition developed by SalesAgility Ltd.
|
|
* Copyright (C) 2011 - 2018 SalesAgility Ltd.
|
|
*
|
|
* This program is free software; you can redistribute it and/or modify it under
|
|
* the terms of the GNU Affero General Public License version 3 as published by the
|
|
* Free Software Foundation with the addition of the following permission added
|
|
* to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
|
|
* IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
|
|
* OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
|
|
*
|
|
* This program is distributed in the hope that it will be useful, but WITHOUT
|
|
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
|
|
* FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
|
|
* details.
|
|
*
|
|
* You should have received a copy of the GNU Affero General Public License along with
|
|
* this program; if not, see http://www.gnu.org/licenses or write to the Free
|
|
* Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
|
|
* 02110-1301 USA.
|
|
*
|
|
* You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
|
|
* SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
|
|
*
|
|
* The interactive user interfaces in modified source and object code versions
|
|
* of this program must display Appropriate Legal Notices, as required under
|
|
* Section 5 of the GNU Affero General Public License version 3.
|
|
*
|
|
* In accordance with Section 7(b) of the GNU Affero General Public License version 3,
|
|
* these Appropriate Legal Notices must retain the display of the "Powered by
|
|
* SugarCRM" logo and "Supercharged by SuiteCRM" logo. If the display of the logos is not
|
|
* reasonably feasible for technical reasons, the Appropriate Legal Notices must
|
|
* display the words "Powered by SugarCRM" and "Supercharged by SuiteCRM".
|
|
*/
|
|
|
|
/**
|
|
|
|
* Description: Class defining queries of predefined charts.
|
|
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
|
|
* All Rights Reserved.
|
|
* Contributor(s): ______________________________________.
|
|
*/
|
|
|
|
|
|
|
|
#[\AllowDynamicProperties]
|
|
class PredefinedChart
|
|
{
|
|
public $params = array();
|
|
|
|
public function __construct()
|
|
{
|
|
}
|
|
|
|
|
|
|
|
|
|
public function predefinedChartQuery($chart, $params=array())
|
|
{
|
|
switch ($chart) {
|
|
case 'pipeline_by_sales_stage':
|
|
case 'pipeline_by_sales_stage_funnel':
|
|
return $this->pipelineBySalesStageQuery();
|
|
case 'lead_source_by_outcome':
|
|
return $this->leadSourceByOutcomeQuery($params);
|
|
case 'outcome_by_month':
|
|
return $this->outcomeByMonthQuery();
|
|
case 'pipeline_by_lead_source':
|
|
return $this->pipelineByLeadSourceQuery($params);
|
|
case 'my_modules_used_last_30_days':
|
|
return $this->myModuleUsageLast30Days();
|
|
default:
|
|
return $this->customChartQuery($chart);
|
|
}
|
|
return;
|
|
}
|
|
|
|
public function pipelineBySalesStageQuery()
|
|
{
|
|
global $current_user;
|
|
global $timedate;
|
|
global $app_list_strings;
|
|
|
|
//get the dates to display
|
|
$user_date_start = $current_user->getPreference('pbss_date_start');
|
|
|
|
if (!empty($user_date_start) && !isset($_REQUEST['pbss_date_start'])) {
|
|
$date_start = $timedate->to_display_date($user_date_start, false);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_date_start'] is:");
|
|
$GLOBALS['log']->debug($user_date_start);
|
|
} elseif (isset($_REQUEST['pbss_date_start']) && $_REQUEST['pbss_date_start'] != '') {
|
|
$date_start = $_REQUEST['pbss_date_start'];
|
|
$ds = $timedate->to_db_date($date_start, false);
|
|
$current_user->setPreference('pbss_date_start', $ds);
|
|
$GLOBALS['log']->debug("_REQUEST['pbss_date_start'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['pbss_date_start']);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_date_start'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('pbss_date_start'));
|
|
} else {
|
|
$date_start = $timedate->nowDate();
|
|
}
|
|
|
|
$user_date_end = $current_user->getPreference('pbss_date_end');
|
|
if (!empty($user_date_end) && !isset($_REQUEST['pbss_date_end'])) {
|
|
$date_end = $timedate->to_display_date($user_date_end, false);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_date_end'] is:");
|
|
$GLOBALS['log']->debug($user_date_end);
|
|
} elseif (isset($_REQUEST['pbss_date_end']) && $_REQUEST['pbss_date_end'] != '') {
|
|
$date_end = $_REQUEST['pbss_date_end'];
|
|
$de = $timedate->to_db_date($date_end, false);
|
|
$current_user->setPreference('pbss_date_end', $de);
|
|
$GLOBALS['log']->debug("_REQUEST['pbss_date_end'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['pbss_date_end']);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_date_end'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('pbss_date_end'));
|
|
} else {
|
|
$date_end = $timedate->asUserDate($timedate->fromString("2010-01-01"));
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_date_end'] not found. Using: ".$date_end);
|
|
}
|
|
|
|
$tempx = array();
|
|
$datax = array();
|
|
$datax_selected= array();
|
|
$user_tempx = $current_user->getPreference('pbss_sales_stages');
|
|
//get list of sales stage keys to display
|
|
if (!empty($user_tempx) && (is_countable($user_tempx) ? count($user_tempx) : 0) > 0 && !isset($_REQUEST['pbss_sales_stages'])) {
|
|
$tempx = $user_tempx ;
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_sales_stages'] is:");
|
|
$GLOBALS['log']->debug($user_tempx);
|
|
} elseif (isset($_REQUEST['pbss_sales_stages']) && (is_countable($_REQUEST['pbss_sales_stages']) ? count($_REQUEST['pbss_sales_stages']) : 0) > 0) {
|
|
$tempx = $_REQUEST['pbss_sales_stages'];
|
|
$current_user->setPreference('pbss_sales_stages', $_REQUEST['pbss_sales_stages']);
|
|
$GLOBALS['log']->debug("_REQUEST['pbss_sales_stages'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['pbss_sales_stages']);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_sales_stages'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('pbss_sales_stages'));
|
|
}
|
|
|
|
//set $datax using selected sales stage keys
|
|
if ((is_countable($tempx) ? count($tempx) : 0) > 0) {
|
|
foreach ($tempx as $key) {
|
|
$datax[$key] = $app_list_strings['sales_stage_dom'][$key];
|
|
array_push($datax_selected, $key);
|
|
}
|
|
} else {
|
|
$datax = $app_list_strings['sales_stage_dom'];
|
|
$datax_selected = array_keys($app_list_strings['sales_stage_dom']);
|
|
}
|
|
$GLOBALS['log']->debug("datax is:");
|
|
$GLOBALS['log']->debug($datax);
|
|
|
|
$ids = array();
|
|
$new_ids = array();
|
|
$user_ids = $current_user->getPreference('pbss_ids');
|
|
//get list of user ids for which to display data
|
|
if (!empty($user_ids) && (is_countable($user_ids) ? count($user_ids) : 0) != 0 && !isset($_REQUEST['pbss_ids'])) {
|
|
$ids = $user_ids;
|
|
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_ids'] is:");
|
|
$GLOBALS['log']->debug($user_ids);
|
|
} elseif (isset($_REQUEST['pbss_ids']) && (is_countable($_REQUEST['pbss_ids']) ? count($_REQUEST['pbss_ids']) : 0) > 0) {
|
|
$ids = $_REQUEST['pbss_ids'];
|
|
$current_user->setPreference('pbss_ids', $_REQUEST['pbss_ids']);
|
|
$GLOBALS['log']->debug("_REQUEST['pbss_ids'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['pbss_ids']);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbss_ids'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('pbss_ids'));
|
|
} else {
|
|
$ids = get_user_array(false);
|
|
$ids = array_keys($ids);
|
|
}
|
|
|
|
$user_id = $ids;
|
|
$opp = new Opportunity;
|
|
$where="";
|
|
//build the where clause for the query that matches $user
|
|
$count = is_countable($user_id) ? count($user_id) : 0;
|
|
$id = array();
|
|
$user_list = get_user_array(false);
|
|
foreach ($user_id as $key) {
|
|
$new_ids[$key] = $user_list[$key];
|
|
}
|
|
if ($count>0) {
|
|
foreach ($new_ids as $the_id=>$the_name) {
|
|
$id[] = "'".$the_id."'";
|
|
}
|
|
$ids = implode(",", $id);
|
|
$where .= "opportunities.assigned_user_id IN ($ids) ";
|
|
}
|
|
//build the where clause for the query that matches $datax
|
|
$count = is_countable($datax) ? count($datax) : 0;
|
|
$dataxArr = array();
|
|
if ($count>0) {
|
|
foreach ($datax as $key=>$value) {
|
|
$dataxArr[] = "'".$key."'";
|
|
}
|
|
$dataxArr = implode(",", $dataxArr);
|
|
$where .= "AND opportunities.sales_stage IN ($dataxArr) ";
|
|
}
|
|
|
|
$date_start = $timedate->swap_formats($date_start, $timedate->get_date_format(), $timedate->dbDayFormat);
|
|
$date_end = $timedate->swap_formats($date_end, $timedate->get_date_format(), $timedate->dbDayFormat);
|
|
//build the where clause for the query that matches $date_start and $date_end
|
|
$where .= " AND opportunities.date_closed >= ". DBManagerFactory::getInstance()->convert("'".$date_start."'", 'date'). "
|
|
AND opportunities.date_closed <= ".DBManagerFactory::getInstance()->convert("'".$date_end."'", 'date') ;
|
|
$where .= " AND opportunities.assigned_user_id = users.id AND opportunities.deleted=0 ";
|
|
|
|
//Now do the db queries
|
|
//query for opportunity data that matches $datax and $user
|
|
$query = " SELECT opportunities.sales_stage,
|
|
users.user_name,
|
|
opportunities.assigned_user_id,
|
|
count( * ) AS opp_count,
|
|
sum(amount_usdollar/1000) AS total
|
|
FROM users,opportunities ";
|
|
$query .= "WHERE " .$where;
|
|
$query .= " GROUP BY opportunities.sales_stage";
|
|
|
|
$additional_params = array( 'date_start' => $date_start, 'date_closed' => $date_end, );
|
|
|
|
$this->params = $additional_params;
|
|
|
|
return $query;
|
|
}
|
|
|
|
public function leadSourceByOutcomeQuery($filters)
|
|
{
|
|
global $current_user;
|
|
global $app_list_strings;
|
|
|
|
$tempx = array();
|
|
$datax = array();
|
|
$selected_datax = array();
|
|
//get list of sales stage keys to display
|
|
|
|
$tempx = $filters['lsbo_lead_sources'];
|
|
if (!empty($lsbo_lead_sources) && (is_countable($lsbo_lead_sources) ? count($lsbo_lead_sources) : 0) > 0 && !isset($_REQUEST['lsbo_lead_sources'])) {
|
|
$GLOBALS['log']->fatal("user->getPreference('lsbo_lead_sources') is:");
|
|
$GLOBALS['log']->fatal($tempx);
|
|
} elseif (isset($_REQUEST['lsbo_lead_sources']) && (is_countable($_REQUEST['lsbo_lead_sources']) ? count($_REQUEST['lsbo_lead_sources']) : 0) > 0) {
|
|
$tempx = $_REQUEST['lsbo_lead_sources'];
|
|
$current_user->setPreference('lsbo_lead_sources', $_REQUEST['lsbo_lead_sources']);
|
|
$GLOBALS['log']->fatal("_REQUEST['lsbo_lead_sources'] is:");
|
|
$GLOBALS['log']->fatal($_REQUEST['lsbo_lead_sources']);
|
|
$GLOBALS['log']->fatal("user->getPreference('lsbo_lead_sources') is:");
|
|
$GLOBALS['log']->fatal($current_user->getPreference('lsbo_lead_sources'));
|
|
}
|
|
//set $datax using selected sales stage keys
|
|
if (!empty($tempx) && (is_countable($tempx) ? count($tempx) : 0) > 0) {
|
|
foreach ($tempx as $key) {
|
|
$datax[$key] = $app_list_strings['lead_source_dom'][$key];
|
|
array_push($selected_datax, $key);
|
|
}
|
|
} else {
|
|
$datax = $app_list_strings['lead_source_dom'];
|
|
$selected_datax = array_keys($app_list_strings['lead_source_dom']);
|
|
}
|
|
|
|
$datay = $datax;
|
|
|
|
$ids = $filters['lsbo_ids'];
|
|
//get list of user ids for which to display data
|
|
if (!empty($ids) && (is_countable($ids) ? count($ids) : 0) != 0 && !isset($_REQUEST['lsbo_ids'])) {
|
|
$GLOBALS['log']->debug("_SESSION['lsbo_ids'] is:");
|
|
$GLOBALS['log']->debug($ids);
|
|
} elseif (isset($_REQUEST['lsbo_ids']) && (is_countable($_REQUEST['lsbo_ids']) ? count($_REQUEST['lsbo_ids']) : 0) > 0) {
|
|
$ids = $_REQUEST['lsbo_ids'];
|
|
$current_user->setPreference('lsbo_ids', $_REQUEST['lsbo_ids']);
|
|
$GLOBALS['log']->debug("_REQUEST['lsbo_ids'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['lsbo_ids']);
|
|
$GLOBALS['log']->debug("user->getPreference('lsbo_ids') is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('lsbo_ids'));
|
|
} else {
|
|
$ids = get_user_array(false);
|
|
$ids = array_keys($ids);
|
|
}
|
|
|
|
$user_id = $ids;
|
|
|
|
$opp = BeanFactory::newBean('Opportunities');
|
|
$where="";
|
|
//build the where clause for the query that matches $user
|
|
$count = is_countable($user_id) ? count($user_id) : 0;
|
|
$id = array();
|
|
if ($count>0) {
|
|
foreach ($user_id as $the_id) {
|
|
$id[] = "'".$the_id."'";
|
|
}
|
|
$ids = implode(",", $id);
|
|
$where .= "opportunities.assigned_user_id IN ($ids) ";
|
|
}
|
|
|
|
//build the where clause for the query that matches $datay
|
|
$count = is_countable($datay) ? count($datay) : 0;
|
|
$datayArr = array();
|
|
if ($count>0) {
|
|
foreach ($datay as $key=>$value) {
|
|
$datayArr[] = "'".$key."'";
|
|
}
|
|
$datayArr = implode(",", $datayArr);
|
|
$where .= "AND opportunities.lead_source IN ($datayArr) ";
|
|
}
|
|
$query = "SELECT lead_source,sales_stage,sum(amount_usdollar/1000) as total,count(*) as opp_count FROM opportunities ";
|
|
$query .= "WHERE " .$where." AND opportunities.deleted=0 ";
|
|
$query .= " GROUP BY sales_stage,lead_source ORDER BY lead_source,sales_stage";
|
|
|
|
return $query;
|
|
}
|
|
|
|
public function outcomeByMonthQuery()
|
|
{
|
|
global $current_user;
|
|
global $timedate;
|
|
|
|
$user_date_start = $current_user->getPreference('obm_date_start');
|
|
if (!empty($user_date_start) && !isset($_REQUEST['obm_date_start'])) {
|
|
$date_start =$user_date_start;
|
|
$GLOBALS['log']->debug("USER PREFERENCES['obm_date_start'] is:");
|
|
$GLOBALS['log']->debug($user_date_start);
|
|
} elseif (isset($_REQUEST['obm_year']) && $_REQUEST['obm_year'] != '') {
|
|
$date_start = $_REQUEST['obm_year'].'-01-01';
|
|
$current_user->setPreference('obm_date_start', $date_start);
|
|
$GLOBALS['log']->debug("_REQUEST['obm_date_start'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['obm_date_start']);
|
|
$GLOBALS['log']->debug("_SESSION['obm_date_start'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('obm_date_start'));
|
|
} else {
|
|
$date_start = date('Y').'-01-01';
|
|
}
|
|
$user_date_end = $current_user->getPreference('obm_date_end');
|
|
if (!empty($user_date_end) && !isset($_REQUEST['obm_date_end'])) {
|
|
$date_end =$user_date_end;
|
|
$GLOBALS['log']->debug("USER PREFERENCES['obm_date_end'] is:");
|
|
$GLOBALS['log']->debug($date_end);
|
|
} elseif (isset($_REQUEST['obm_year']) && $_REQUEST['obm_year'] != '') {
|
|
$date_end = $_REQUEST['obm_year'].'-12-31';
|
|
$current_user->setPreference('obm_date_end', $date_end);
|
|
$GLOBALS['log']->debug("_REQUEST['obm_date_end'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['obm_date_end']);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['obm_date_end'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('obm_date_end'));
|
|
} else {
|
|
$date_end = date('Y').'-12-31';
|
|
}
|
|
|
|
$ids = array();
|
|
//get list of user ids for which to display data
|
|
$user_ids = $current_user->getPreference('obm_ids');
|
|
if (!empty($user_ids) && (is_countable($user_ids) ? count($user_ids) : 0) != 0 && !isset($_REQUEST['obm_ids'])) {
|
|
$ids = $user_ids;
|
|
$GLOBALS['log']->debug("USER PREFERENCES['obm_ids'] is:");
|
|
$GLOBALS['log']->debug($user_ids);
|
|
} elseif (isset($_REQUEST['obm_ids']) && (is_countable($_REQUEST['obm_ids']) ? count($_REQUEST['obm_ids']) : 0) > 0) {
|
|
$ids = $_REQUEST['obm_ids'];
|
|
$current_user->setPreference('obm_ids', $_REQUEST['obm_ids']);
|
|
$GLOBALS['log']->debug("_REQUEST['obm_ids'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['obm_ids']);
|
|
$GLOBALS['log']->debug("USER PREFRENCES['obm_ids'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('obm_ids'));
|
|
} else {
|
|
$ids = get_user_array(false);
|
|
$ids = array_keys($ids);
|
|
}
|
|
|
|
$user_id = $ids;
|
|
|
|
$where = "";
|
|
//build the where clause for the query that matches $user
|
|
$count = is_countable($user_id) ? count($user_id) : 0;
|
|
$id = array();
|
|
if ($count>0) {
|
|
foreach ($user_id as $the_id) {
|
|
$id[] = "'".$the_id."'";
|
|
}
|
|
$ids = implode(",", $id);
|
|
$where .= "opportunities.assigned_user_id IN ($ids) ";
|
|
}
|
|
|
|
// cn: adding user-pref date handling
|
|
$dateStartDisplay = $timedate->asUserDate($timedate->fromString($date_start));
|
|
$dateEndDisplay = $timedate->asUserDate($timedate->fromString($date_end));
|
|
|
|
$opp = BeanFactory::newBean('Opportunities');
|
|
//build the where clause for the query that matches $date_start and $date_end
|
|
$where .= "AND opportunities.date_closed >= ".DBManagerFactory::getInstance()->convert("'".$date_start."'", 'date')." AND opportunities.date_closed <= ".DBManagerFactory::getInstance()->convert("'".$date_end."'", 'date')." AND opportunities.deleted=0";
|
|
$query = "SELECT sales_stage,".DBManagerFactory::getInstance()->convert('opportunities.date_closed', 'date_format', array("'%Y-%m'"), array("'YYYY-MM'"))." as m, sum(amount_usdollar/1000) as total, count(*) as opp_count FROM opportunities ";
|
|
$query .= "WHERE ".$where;
|
|
$query .= " GROUP BY sales_stage,".DBManagerFactory::getInstance()->convert('opportunities.date_closed', 'date_format', array("'%Y-%m'"), array("'YYYY-MM'"))."ORDER BY m";
|
|
return $query;
|
|
}
|
|
|
|
public function pipelineByLeadSourceQuery($filters)
|
|
{
|
|
global $current_user;
|
|
global $app_list_strings;
|
|
|
|
$tempx = array();
|
|
$datax = array();
|
|
$selected_datax = array();
|
|
|
|
//get list of sales stage keys to display
|
|
$user_tempx = $filters['pbls_lead_sources'];
|
|
if (!empty($user_tempx) && (is_countable($user_tempx) ? count($user_tempx) : 0) > 0 && !isset($_REQUEST['pbls_lead_sources'])) {
|
|
$tempx = $user_tempx;
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
|
|
$GLOBALS['log']->debug($user_tempx);
|
|
} elseif (isset($_REQUEST['pbls_lead_sources']) && (is_countable($_REQUEST['pbls_lead_sources']) ? count($_REQUEST['pbls_lead_sources']) : 0) > 0) {
|
|
$tempx = $_REQUEST['pbls_lead_sources'];
|
|
$current_user->setPreference('pbls_lead_sources', $_REQUEST['pbls_lead_sources']);
|
|
$GLOBALS['log']->debug("_REQUEST['pbls_lead_sources'] is:");
|
|
$GLOBALS['log']->debug($_REQUEST['pbls_lead_sources']);
|
|
$GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
|
|
$GLOBALS['log']->debug($current_user->getPreference('pbls_lead_sources'));
|
|
}
|
|
|
|
//set $datax using selected sales stage keys
|
|
if ((is_countable($tempx) ? count($tempx) : 0) > 0) {
|
|
foreach ($tempx as $key) {
|
|
$datax[$key] = $app_list_strings['lead_source_dom'][$key];
|
|
array_push($selected_datax, $key);
|
|
}
|
|
} else {
|
|
$datax = $app_list_strings['lead_source_dom'];
|
|
$selected_datax = array_keys($app_list_strings['lead_source_dom']);
|
|
}
|
|
|
|
$legends = $datax;
|
|
|
|
$ids = array();
|
|
$user_ids = $filters['pbls_ids'];
|
|
//get list of user ids for which to display data
|
|
if (!empty($user_ids) && (is_countable($user_ids) ? count($user_ids) : 0) > 0) {
|
|
$ids = $user_ids;
|
|
} else {
|
|
$ids = get_user_array(false);
|
|
$ids = array_keys($ids);
|
|
}
|
|
|
|
$user_id = $ids;
|
|
$opp = new Opportunity;
|
|
//Now do the db queries
|
|
//query for opportunity data that matches $legends and $user
|
|
$where="";
|
|
//build the where clause for the query that matches $user
|
|
|
|
$count = is_countable($user_id) ? count($user_id) : 0;
|
|
$id = array();
|
|
if ($count > 0 && !empty($user_id)) {
|
|
foreach ($user_id as $the_id) {
|
|
$id[] = "'".$the_id."'";
|
|
}
|
|
$ids = implode(",", $id);
|
|
$where .= "opportunities.assigned_user_id IN ($ids) ";
|
|
}
|
|
if (!empty($where)) {
|
|
$where .= 'AND';
|
|
}
|
|
//build the where clause for the query that matches $datax
|
|
$count = is_countable($legends) ? count($legends) : 0;
|
|
$legendItem = array();
|
|
if ($count > 0 && !empty($legends)) {
|
|
foreach ($legends as $key=>$value) {
|
|
$legendItem[] = "'".$key."'";
|
|
}
|
|
$legendItems = implode(",", $legendItem);
|
|
$where .= " opportunities.lead_source IN ($legendItems) ";
|
|
}
|
|
$query = "SELECT lead_source,sum(amount_usdollar/1000) as total,count(*) as opp_count FROM opportunities ";
|
|
$query .= "WHERE ".$where." AND opportunities.deleted=0 ";
|
|
$query .= "GROUP BY lead_source ORDER BY total DESC";
|
|
|
|
return $query;
|
|
}
|
|
|
|
public function myModuleUsageLast30Days()
|
|
{
|
|
global $current_user, $timedate;
|
|
$dateValue = DBManagerFactory::getInstance()->convert("'".$timedate->getNow()->modify("-30 days")->asDb()."'", "datetime");
|
|
|
|
$query = "SELECT tracker.module_name as module_name ";
|
|
$query .= ",COUNT(*) count FROM tracker ";
|
|
$query .= "WHERE tracker.user_id = '$current_user->id' AND tracker.module_name != 'UserPreferences' AND tracker.date_modified > $dateValue ";
|
|
$query .= "GROUP BY tracker.module_name ORDER BY count DESC";
|
|
|
|
return $query;
|
|
}
|
|
|
|
|
|
// This function will grab a query from the custom directory to be used for charting
|
|
public function customChartQuery($chart)
|
|
{
|
|
if (file_exists('custom/Charts/' . $chart . '.php')) {
|
|
require_once('custom/Charts/' . $chart . '.php');
|
|
return customChartQuery();
|
|
} else {
|
|
return false;
|
|
}
|
|
}
|
|
}
|