2 if(!defined('sugarEntry') || !sugarEntry) die('Not A Valid Entry Point');
3 /*********************************************************************************
4 * SugarCRM Community Edition is a customer relationship management program developed by
5 * SugarCRM, Inc. Copyright (C) 2004-2012 SugarCRM Inc.
7 * This program is free software; you can redistribute it and/or modify it under
8 * the terms of the GNU Affero General Public License version 3 as published by the
9 * Free Software Foundation with the addition of the following permission added
10 * to Section 15 as permitted in Section 7(a): FOR ANY PART OF THE COVERED WORK
11 * IN WHICH THE COPYRIGHT IS OWNED BY SUGARCRM, SUGARCRM DISCLAIMS THE WARRANTY
12 * OF NON INFRINGEMENT OF THIRD PARTY RIGHTS.
14 * This program is distributed in the hope that it will be useful, but WITHOUT
15 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
16 * FOR A PARTICULAR PURPOSE. See the GNU Affero General Public License for more
19 * You should have received a copy of the GNU Affero General Public License along with
20 * this program; if not, see http://www.gnu.org/licenses or write to the Free
21 * Software Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
24 * You can contact SugarCRM, Inc. headquarters at 10050 North Wolfe Road,
25 * SW2-130, Cupertino, CA 95014, USA. or at email address contact@sugarcrm.com.
27 * The interactive user interfaces in modified source and object code versions
28 * of this program must display Appropriate Legal Notices, as required under
29 * Section 5 of the GNU Affero General Public License version 3.
31 * In accordance with Section 7(b) of the GNU Affero General Public License version 3,
32 * these Appropriate Legal Notices must retain the display of the "Powered by
33 * SugarCRM" logo. If the display of the logo is not reasonably feasible for
34 * technical reasons, the Appropriate Legal Notices must display the words
35 * "Powered by SugarCRM".
36 ********************************************************************************/
38 /*********************************************************************************
40 * Description: Class defining queries of predefined charts.
41 * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
42 * All Rights Reserved.
43 * Contributor(s): ______________________________________.
44 ********************************************************************************/
48 class PredefinedChart{
49 var $params = array();
51 function PredefinedChart(){
54 function predefinedChartQuery($chart, $params=array()){
56 case 'pipeline_by_sales_stage':
57 case 'pipeline_by_sales_stage_funnel':
58 return $this->pipelineBySalesStageQuery();
59 case 'lead_source_by_outcome':
60 return $this->leadSourceByOutcomeQuery($params);
61 case 'outcome_by_month':
62 return $this->outcomeByMonthQuery();
63 case 'pipeline_by_lead_source':
64 return $this->pipelineByLeadSourceQuery($params);
65 case 'my_modules_used_last_30_days':
66 return $this->myModuleUsageLast30Days();
68 return $this->customChartQuery($chart);
73 function pipelineBySalesStageQuery(){
78 global $app_list_strings;
80 //get the dates to display
81 $user_date_start = $current_user->getPreference('pbss_date_start');
83 if (!empty($user_date_start) && !isset($_REQUEST['pbss_date_start'])) {
84 $date_start = $timedate->to_display_date($user_date_start, false);
85 $GLOBALS['log']->debug("USER PREFERENCES['pbss_date_start'] is:");
86 $GLOBALS['log']->debug($user_date_start);
88 elseif (isset($_REQUEST['pbss_date_start']) && $_REQUEST['pbss_date_start'] != '') {
89 $date_start = $_REQUEST['pbss_date_start'];
90 $ds = $timedate->to_db_date($date_start, false);
91 $current_user->setPreference('pbss_date_start', $ds);
92 $GLOBALS['log']->debug("_REQUEST['pbss_date_start'] is:");
93 $GLOBALS['log']->debug($_REQUEST['pbss_date_start']);
94 $GLOBALS['log']->debug("USER PREFERENCES['pbss_date_start'] is:");
95 $GLOBALS['log']->debug($current_user->getPreference('pbss_date_start'));
98 $date_start = $timedate->nowDate();
101 $user_date_end = $current_user->getPreference('pbss_date_end');
102 if (!empty($user_date_end) && !isset($_REQUEST['pbss_date_end'])) {
103 $date_end = $timedate->to_display_date($user_date_end, false);
104 $GLOBALS['log']->debug("USER PREFERENCES['pbss_date_end'] is:");
105 $GLOBALS['log']->debug($user_date_end);
107 elseif (isset($_REQUEST['pbss_date_end']) && $_REQUEST['pbss_date_end'] != '') {
108 $date_end = $_REQUEST['pbss_date_end'];
109 $de = $timedate->to_db_date($date_end, false);
110 $current_user->setPreference('pbss_date_end', $de);
111 $GLOBALS['log']->debug("_REQUEST['pbss_date_end'] is:");
112 $GLOBALS['log']->debug($_REQUEST['pbss_date_end']);
113 $GLOBALS['log']->debug("USER PREFERENCES['pbss_date_end'] is:");
114 $GLOBALS['log']->debug( $current_user->getPreference('pbss_date_end'));
117 $date_end = $timedate->asUserDate($timedate->fromString("2010-01-01"));
118 $GLOBALS['log']->debug("USER PREFERENCES['pbss_date_end'] not found. Using: ".$date_end);
123 $datax_selected= array();
124 $user_tempx = $current_user->getPreference('pbss_sales_stages');
125 //get list of sales stage keys to display
126 if (!empty($user_tempx) && count($user_tempx) > 0 && !isset($_REQUEST['pbss_sales_stages'])) {
127 $tempx = $user_tempx ;
128 $GLOBALS['log']->debug("USER PREFERENCES['pbss_sales_stages'] is:");
129 $GLOBALS['log']->debug($user_tempx );
131 elseif (isset($_REQUEST['pbss_sales_stages']) && count($_REQUEST['pbss_sales_stages']) > 0) {
132 $tempx = $_REQUEST['pbss_sales_stages'];
133 $current_user->setPreference('pbss_sales_stages', $_REQUEST['pbss_sales_stages']);
134 $GLOBALS['log']->debug("_REQUEST['pbss_sales_stages'] is:");
135 $GLOBALS['log']->debug($_REQUEST['pbss_sales_stages']);
136 $GLOBALS['log']->debug("USER PREFERENCES['pbss_sales_stages'] is:");
137 $GLOBALS['log']->debug($current_user->getPreference('pbss_sales_stages'));
140 //set $datax using selected sales stage keys
141 if (count($tempx) > 0) {
142 foreach ($tempx as $key) {
143 $datax[$key] = $app_list_strings['sales_stage_dom'][$key];
144 array_push($datax_selected,$key);
148 $datax = $app_list_strings['sales_stage_dom'];
149 $datax_selected = array_keys($app_list_strings['sales_stage_dom']);
151 $GLOBALS['log']->debug("datax is:");
152 $GLOBALS['log']->debug($datax);
156 $user_ids = $current_user->getPreference('pbss_ids');
157 //get list of user ids for which to display data
158 if (!empty($user_ids) && count($user_ids) != 0 && !isset($_REQUEST['pbss_ids'])) {
161 $GLOBALS['log']->debug("USER PREFERENCES['pbss_ids'] is:");
162 $GLOBALS['log']->debug($user_ids);
164 elseif (isset($_REQUEST['pbss_ids']) && count($_REQUEST['pbss_ids']) > 0) {
165 $ids = $_REQUEST['pbss_ids'];
166 $current_user->setPreference('pbss_ids', $_REQUEST['pbss_ids']);
167 $GLOBALS['log']->debug("_REQUEST['pbss_ids'] is:");
168 $GLOBALS['log']->debug($_REQUEST['pbss_ids']);
169 $GLOBALS['log']->debug("USER PREFERENCES['pbss_ids'] is:");
170 $GLOBALS['log']->debug($current_user->getPreference('pbss_ids'));
173 $ids = get_user_array(false);
174 $ids = array_keys($ids);
179 $opp = new Opportunity;
181 //build the where clause for the query that matches $user
182 $count = count($user_id);
184 $user_list = get_user_array(false);
185 foreach ($user_id as $key) {
186 $new_ids[$key] = $user_list[$key];
189 foreach ($new_ids as $the_id=>$the_name) {
190 $id[] = "'".$the_id."'";
192 $ids = join(",",$id);
193 $where .= "opportunities.assigned_user_id IN ($ids) ";
196 //build the where clause for the query that matches $datax
197 $count = count($datax);
201 foreach ($datax as $key=>$value) {
202 $dataxArr[] = "'".$key."'";
204 $dataxArr = join(",",$dataxArr);
205 $where .= "AND opportunities.sales_stage IN ($dataxArr) ";
208 $date_start = $timedate->swap_formats($date_start, $timedate->get_date_format(), $timedate->dbDayFormat);
209 $date_end = $timedate->swap_formats($date_end, $timedate->get_date_format(), $timedate->dbDayFormat);
210 //build the where clause for the query that matches $date_start and $date_end
211 $where .= " AND opportunities.date_closed >= ". db_convert("'".$date_start."'",'date'). "
212 AND opportunities.date_closed <= ".db_convert("'".$date_end."'",'date') ;
213 $where .= " AND opportunities.assigned_user_id = users.id AND opportunities.deleted=0 ";
215 //Now do the db queries
216 //query for opportunity data that matches $datax and $user
217 $query = " SELECT opportunities.sales_stage,
219 opportunities.assigned_user_id,
220 count( * ) AS opp_count,
221 sum(amount_usdollar/1000) AS total
222 FROM users,opportunities ";
223 $query .= "WHERE " .$where;
224 $query .= " GROUP BY opportunities.sales_stage";
226 $additional_params = array( 'date_start' => $date_start, 'date_closed' => $date_end, );
228 $this->params = $additional_params;
233 function leadSourceByOutcomeQuery($filters){
236 global $current_user;
237 global $app_list_strings;
241 $selected_datax = array();
242 //get list of sales stage keys to display
244 $tempx = $filters['lsbo_lead_sources'];
245 if (!empty($lsbo_lead_sources) && count($lsbo_lead_sources) > 0 && !isset($_REQUEST['lsbo_lead_sources'])) {
246 $GLOBALS['log']->fatal("user->getPreference('lsbo_lead_sources') is:");
247 $GLOBALS['log']->fatal($tempx);
249 elseif (isset($_REQUEST['lsbo_lead_sources']) && count($_REQUEST['lsbo_lead_sources']) > 0) {
250 $tempx = $_REQUEST['lsbo_lead_sources'];
251 $current_user->setPreference('lsbo_lead_sources', $_REQUEST['lsbo_lead_sources']);
252 $GLOBALS['log']->fatal("_REQUEST['lsbo_lead_sources'] is:");
253 $GLOBALS['log']->fatal($_REQUEST['lsbo_lead_sources']);
254 $GLOBALS['log']->fatal("user->getPreference('lsbo_lead_sources') is:");
255 $GLOBALS['log']->fatal($current_user->getPreference('lsbo_lead_sources'));
257 //set $datax using selected sales stage keys
258 if (!empty($tempx) && sizeof($tempx) > 0) {
259 foreach ($tempx as $key) {
260 $datax[$key] = $app_list_strings['lead_source_dom'][$key];
261 array_push($selected_datax,$key);
265 $datax = $app_list_strings['lead_source_dom'];
266 $selected_datax = array_keys($app_list_strings['lead_source_dom']);
271 $ids = $filters['lsbo_ids'];
272 //get list of user ids for which to display data
273 if (!empty($ids) && count($ids) != 0 && !isset($_REQUEST['lsbo_ids'])) {
274 $GLOBALS['log']->debug("_SESSION['lsbo_ids'] is:");
275 $GLOBALS['log']->debug($ids);
277 elseif (isset($_REQUEST['lsbo_ids']) && count($_REQUEST['lsbo_ids']) > 0) {
278 $ids = $_REQUEST['lsbo_ids'];
279 $current_user->setPreference('lsbo_ids', $_REQUEST['lsbo_ids']);
280 $GLOBALS['log']->debug("_REQUEST['lsbo_ids'] is:");
281 $GLOBALS['log']->debug($_REQUEST['lsbo_ids']);
282 $GLOBALS['log']->debug("user->getPreference('lsbo_ids') is:");
283 $GLOBALS['log']->debug($current_user->getPreference('lsbo_ids'));
286 $ids = get_user_array(false);
287 $ids = array_keys($ids);
292 $opp = new Opportunity();
294 //build the where clause for the query that matches $user
295 $count = count($user_id);
298 foreach ($user_id as $the_id) {
299 $id[] = "'".$the_id."'";
301 $ids = join(",",$id);
302 $where .= "opportunities.assigned_user_id IN ($ids) ";
306 //build the where clause for the query that matches $datay
307 $count = count($datay);
311 foreach ($datay as $key=>$value) {
312 $datayArr[] = "'".$key."'";
314 $datayArr = join(",",$datayArr);
315 $where .= "AND opportunities.lead_source IN ($datayArr) ";
317 $query = "SELECT lead_source,sales_stage,sum(amount_usdollar/1000) as total,count(*) as opp_count FROM opportunities ";
318 $query .= "WHERE " .$where." AND opportunities.deleted=0 ";
319 $query .= " GROUP BY sales_stage,lead_source ORDER BY lead_source,sales_stage";
324 function outcomeByMonthQuery(){
327 global $current_user;
330 $user_date_start = $current_user->getPreference('obm_date_start');
331 if (!empty($user_date_start) && !isset($_REQUEST['obm_date_start'])) {
332 $date_start =$user_date_start;
333 $GLOBALS['log']->debug("USER PREFERENCES['obm_date_start'] is:");
334 $GLOBALS['log']->debug($user_date_start);
336 elseif (isset($_REQUEST['obm_year']) && $_REQUEST['obm_year'] != '') {
337 $date_start = $_REQUEST['obm_year'].'-01-01';
338 $current_user->setPreference('obm_date_start', $date_start);
339 $GLOBALS['log']->debug("_REQUEST['obm_date_start'] is:");
340 $GLOBALS['log']->debug($_REQUEST['obm_date_start']);
341 $GLOBALS['log']->debug("_SESSION['obm_date_start'] is:");
342 $GLOBALS['log']->debug($current_user->getPreference('obm_date_start'));
345 $date_start = date('Y').'-01-01';
347 $user_date_end = $current_user->getPreference('obm_date_end');
348 if (!empty($user_date_end) && !isset($_REQUEST['obm_date_end'])) {
349 $date_end =$user_date_end;
350 $GLOBALS['log']->debug("USER PREFERENCES['obm_date_end'] is:");
351 $GLOBALS['log']->debug($date_end);
353 elseif (isset($_REQUEST['obm_year']) && $_REQUEST['obm_year'] != '') {
354 $date_end = $_REQUEST['obm_year'].'-12-31';
355 $current_user->setPreference('obm_date_end', $date_end );
356 $GLOBALS['log']->debug("_REQUEST['obm_date_end'] is:");
357 $GLOBALS['log']->debug($_REQUEST['obm_date_end']);
358 $GLOBALS['log']->debug("USER PREFERENCES['obm_date_end'] is:");
359 $GLOBALS['log']->debug($current_user->getPreference('obm_date_end'));
362 $date_end = date('Y').'-12-31';
366 //get list of user ids for which to display data
367 $user_ids = $current_user->getPreference('obm_ids');
368 if (!empty($user_ids) && count($user_ids) != 0 && !isset($_REQUEST['obm_ids'])) {
370 $GLOBALS['log']->debug("USER PREFERENCES['obm_ids'] is:");
371 $GLOBALS['log']->debug($user_ids);
373 elseif (isset($_REQUEST['obm_ids']) && count($_REQUEST['obm_ids']) > 0) {
374 $ids = $_REQUEST['obm_ids'];
375 $current_user->setPreference('obm_ids', $_REQUEST['obm_ids']);
376 $GLOBALS['log']->debug("_REQUEST['obm_ids'] is:");
377 $GLOBALS['log']->debug($_REQUEST['obm_ids']);
378 $GLOBALS['log']->debug("USER PREFRENCES['obm_ids'] is:");
379 $GLOBALS['log']->debug($current_user->getPreference('obm_ids'));
382 $ids = get_user_array(false);
383 $ids = array_keys($ids);
389 //build the where clause for the query that matches $user
390 $count = count($user_id);
393 foreach ($user_id as $the_id) {
394 $id[] = "'".$the_id."'";
396 $ids = join(",",$id);
397 $where .= "opportunities.assigned_user_id IN ($ids) ";
401 // cn: adding user-pref date handling
402 $dateStartDisplay = $timedate->asUserDate($timedate->fromString($date_start));
403 $dateEndDisplay = $timedate->asUserDate($timedate->fromString($date_end));
405 $opp = new Opportunity();
406 //build the where clause for the query that matches $date_start and $date_end
407 $where .= "AND opportunities.date_closed >= ".db_convert("'".$date_start."'",'date')." AND opportunities.date_closed <= ".db_convert("'".$date_end."'",'date')." AND opportunities.deleted=0";
408 $query = "SELECT sales_stage,".db_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 ";
409 $query .= "WHERE ".$where;
410 $query .= " GROUP BY sales_stage,".db_convert('opportunities.date_closed','date_format',array("'%Y-%m'"),array("'YYYY-MM'"))."ORDER BY m";
414 function pipelineByLeadSourceQuery($filters){
417 global $current_user;
418 global $app_list_strings;
422 $selected_datax = array();
424 //get list of sales stage keys to display
425 $user_tempx = $filters['pbls_lead_sources'];
426 if (!empty($user_tempx) && count($user_tempx) > 0 && !isset($_REQUEST['pbls_lead_sources'])) {
427 $tempx = $user_tempx;
428 $GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
429 $GLOBALS['log']->debug($user_tempx);
431 elseif (isset($_REQUEST['pbls_lead_sources']) && count($_REQUEST['pbls_lead_sources']) > 0) {
432 $tempx = $_REQUEST['pbls_lead_sources'];
433 $current_user->setPreference('pbls_lead_sources', $_REQUEST['pbls_lead_sources']);
434 $GLOBALS['log']->debug("_REQUEST['pbls_lead_sources'] is:");
435 $GLOBALS['log']->debug($_REQUEST['pbls_lead_sources']);
436 $GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
437 $GLOBALS['log']->debug($current_user->getPreference('pbls_lead_sources'));
440 //set $datax using selected sales stage keys
441 if (count($tempx) > 0) {
442 foreach ($tempx as $key) {
443 $datax[$key] = $app_list_strings['lead_source_dom'][$key];
444 array_push($selected_datax,$key);
448 $datax = $app_list_strings['lead_source_dom'];
449 $selected_datax = array_keys($app_list_strings['lead_source_dom']);
455 $user_ids = $filters['pbls_ids'];
456 //get list of user ids for which to display data
457 if (!empty($user_ids) && count($user_ids) > 0){
461 $ids = get_user_array(false);
462 $ids = array_keys($ids);
466 $opp = new Opportunity;
467 //Now do the db queries
468 //query for opportunity data that matches $legends and $user
470 //build the where clause for the query that matches $user
472 $count = count($user_id);
474 if ($count > 0 && !empty($user_id)) {
475 foreach ($user_id as $the_id) {
476 $id[] = "'".$the_id."'";
478 $ids = join(",",$id);
479 $where .= "opportunities.assigned_user_id IN ($ids) ";
482 if(!empty($where)) $where .= 'AND';
483 //build the where clause for the query that matches $datax
484 $count = count($legends);
485 $legendItem = array();
486 if ($count > 0 && !empty($legends)) {
488 foreach ($legends as $key=>$value) {
489 $legendItem[] = "'".$key."'";
491 $legendItems = join(",",$legendItem);
492 $where .= " opportunities.lead_source IN ($legendItems) ";
494 $query = "SELECT lead_source,sum(amount_usdollar/1000) as total,count(*) as opp_count FROM opportunities ";
495 $query .= "WHERE ".$where." AND opportunities.deleted=0 ";
496 $query .= "GROUP BY lead_source ORDER BY total DESC";
501 function myModuleUsageLast30Days() {
502 global $current_user;
503 $dateValue = db_convert("'".$timedate->getNow()->modify("-30 days")->asDb()."'" ,"datetime");
505 $query = "SELECT tracker.module_name as module_name ";
506 $query .= ",COUNT(*) count FROM tracker ";
507 $query .= "WHERE tracker.user_id = '$current_user->id' AND tracker.module_name != 'UserPreferences' AND tracker.date_modified > $dateValue ";
508 $query .= "GROUP BY tracker.module_name ORDER BY count DESC";
514 // This function will grab a query from the custom directory to be used for charting
515 function customChartQuery($chart){
516 if (file_exists('custom/Charts/' . $chart . '.php')){
517 require_once('custom/Charts/' . $chart . '.php');
518 return customChartQuery();