modules/Charts/code/Chart_pipeline_by_lead_source.php

   1 <?php
   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-2011 SugarCRM Inc.
   6  *
   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.
  13  *
  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
  17  * details.
  18  *
  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
  22  * 02110-1301 USA.
  23  *
  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.
  26  *
  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.
  30  *
  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  ********************************************************************************/
  37
  38 /*********************************************************************************
  39
  40  * Description:  returns HTML for client-side image map.
  41  * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc.
  42  * All Rights Reserved.
  43  * Contributor(s): ______________________________________..
  44  ********************************************************************************/
  45
  46
  47
  48 require_once('include/charts/Charts.php');
  49
  50
  51
  52 class Chart_pipeline_by_lead_source
  53 {
  54         var $order = 0;
  55         var $modules = array('Opportunities');
  56
  57 function Chart_pipeline_by_lead_source()
  58 {
  59 }
  60
  61 function draw($extra_tools)
  62 {
  63
  64
  65 global $app_list_strings, $current_language, $ids, $sugar_config ,$theme;
  66 $current_module_strings = return_module_language($current_language, 'Charts');
  67
  68
  69 if (isset($_REQUEST['pbls_refresh'])) { $refresh = $_REQUEST['pbls_refresh']; }
  70 else { $refresh = false; }
  71
  72 $tempx = array();
  73 $datax = array();
  74 $selected_datax = array();
  75 //get list of sales stage keys to display
  76 global $current_user;
  77 $user_tempx = $current_user->getPreference('pbls_lead_sources');
  78 if (!empty($user_tempx) && count($user_tempx) > 0 && !isset($_REQUEST['pbls_lead_sources'])) {
  79         $tempx = $user_tempx;
  80         $GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
  81         $GLOBALS['log']->debug($user_tempx);
  82 }
  83 elseif (isset($_REQUEST['pbls_lead_sources']) && count($_REQUEST['pbls_lead_sources']) > 0) {
  84         $tempx = $_REQUEST['pbls_lead_sources'];
  85         $current_user->setPreference('pbls_lead_sources', $_REQUEST['pbls_lead_sources']);
  86         $GLOBALS['log']->debug("_REQUEST['pbls_lead_sources'] is:");
  87         $GLOBALS['log']->debug($_REQUEST['pbls_lead_sources']);
  88         $GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
  89         $GLOBALS['log']->debug($current_user->getPreference('pbls_lead_sources'));
  90 }
  91
  92 //set $datax using selected sales stage keys
  93 if (count($tempx) > 0) {
  94         foreach ($tempx as $key) {
  95                 $datax[$key] = $app_list_strings['lead_source_dom'][$key];
  96                 array_push($selected_datax,$key);
  97         }
  98 }
  99 else {
 100         $datax = $app_list_strings['lead_source_dom'];
 101         $selected_datax = array_keys($app_list_strings['lead_source_dom']);
 102 }
 103 $GLOBALS['log']->debug("datax is:");
 104 $GLOBALS['log']->debug($datax);
 105
 106 $ids = array();
 107 $user_ids = $current_user->getPreference('pbls_ids');
 108 //get list of user ids for which to display data
 109 if (!empty($user_ids) && count($user_ids) != 0 && !isset($_REQUEST['pbls_ids'])) {
 110         if(isset($_SESSION['pbls_ids'])) {$ids = $_SESSION['pbls_ids'];}
 111         $GLOBALS['log']->debug("USER PREFERENCES['pbls_ids'] is:");
 112         $GLOBALS['log']->debug($user_ids);
 113 }
 114 elseif (isset($_REQUEST['pbls_ids']) && count($_REQUEST['pbls_ids']) > 0) {
 115         $ids = $_REQUEST['pbls_ids'];
 116         $current_user->setPreference('pbls_ids', $ids);
 117         $GLOBALS['log']->debug("_REQUEST['pbls_ids'] is:");
 118         $GLOBALS['log']->debug($_REQUEST['pbls_ids']);
 119         $GLOBALS['log']->debug("USER PREFERENCES['pbls_ids'] is:");
 120         $GLOBALS['log']->debug($current_user->getPreference('pbls_ids'));
 121 }
 122 else {
 123         $ids = get_user_array(false);
 124         $ids = array_keys($ids);
 125 }
 126
 127 //create unique prefix based on selected users for image files
 128 $id_hash = '1';
 129 if (isset($ids) && is_array($ids)) {
 130         sort($ids);
 131         $id_hash = crc32(implode('',$ids));
 132         if($id_hash < 0)
 133         {
 134         $id_hash = $id_hash * -1;
 135         }
 136 }
 137 $GLOBALS['log']->debug("ids is:");
 138 $GLOBALS['log']->debug($ids);
 139 $id_md5 = substr(md5($current_user->id),0,9);
 140
 141
 142 $seps                           = array("-", "/");
 143 $dates                          = array(date($GLOBALS['timedate']->dbDayFormat), $GLOBALS['timedate']->dbDayFormat);
 144 $dateFileNameSafe       = str_replace($seps, "_", $dates);
 145 $cache_file_name        = sugar_cached("xml/").$current_user->getUserPrivGuid()."_pipeline_by_lead_source_".$dateFileNameSafe[0]."_".$dateFileNameSafe[1].".xml";
 146
 147 $GLOBALS['log']->debug("cache file name is: $cache_file_name");
 148 global $currentModule,$action;
 149 $tools='<div align="right"><a href="index.php?module='.$currentModule.'&action='. $action .'&pbls_refresh=true" class="tabFormAdvLink">'.SugarThemeRegistry::current()->getImage('refresh','border="0" align="absmiddle"',null,null,'.gif',$mod_strings['LBL_REFRESH']).'&nbsp;'.$current_module_strings['LBL_REFRESH'].'</a>&nbsp;&nbsp;<a href="javascript: toggleDisplay(\'pbls_edit\');" class="tabFormAdvLink">'.SugarThemeRegistry::current()->getImage('edit','border="0"  align="absmiddle"',null,null,'.gif',$mod_strings['LBL_EDIT']).'&nbsp;'. $current_module_strings['LBL_EDIT'].'</a>&nbsp;&nbsp;'.$extra_tools.'</div>';
 150 ?>
 151
 152 <?php
 153 echo '<span onmouseover="this.style.cursor=\'move\'" id="chart_handle_' . $this->order . '">' . get_form_header($current_module_strings['LBL_LEAD_SOURCE_FORM_TITLE'],$tools,false) . '</span>';
 154 if (empty($_SESSION['pbls_lead_sources'])) $_SESSION['pbls_lead_sources'] = "";
 155 if (empty($_SESSION['pbls_ids'])) $_SESSION['pbls_ids'] = "";
 156 ?>
 157
 158 <p>
 159 <div id='pbls_edit' style='display: none;'>
 160 <form action="index.php" method="post" >
 161 <input type="hidden" name="module" value="<?php echo $currentModule;?>">
 162 <input type="hidden" name="action" value="<?php echo $action;?>">
 163 <input type="hidden" name="pbls_refresh" value="true">
 164 <table cellpadding="0" cellspacing="0" border="0" class="edit view" align="center">
 165 <tr>
 166         <td valign='top' nowrap><b><?php echo $current_module_strings['LBL_LEAD_SOURCES'];?></b></td>
 167         <td valign='top'><select name="pbls_lead_sources[]" multiple size='3'><?php echo get_select_options_with_id($app_list_strings['lead_source_dom'],$selected_datax); ?></select></td>
 168         <td valign='top' nowrap><b><?php echo $current_module_strings['LBL_USERS'];?></b></td>
 169         <td valign='top'><select name="pbls_ids[]" multiple size='3'><?php $allUsers = get_user_array(false); echo get_select_options_with_id($allUsers,$ids); ?></select></td>
 170 <?php
 171 global $app_strings;
 172 ?>
 173         <td align="right" valign="top"><input class="button" type="submit" title="<?php echo $app_strings['LBL_SELECT_BUTTON_TITLE']; ?>" accessKey="<?php echo $app_strings['LBL_SELECT_BUTTON_KEY']; ?>" value="<?php echo $app_strings['LBL_SELECT_BUTTON_LABEL']?>" /><input class="button" onClick="javascript: toggleDisplay('pbls_edit');" type="button" title="<?php echo $app_strings['LBL_CANCEL_BUTTON_TITLE']; ?>" accessKey="<?php echo $app_strings['LBL_CANCEL_BUTTON_KEY'];?>" value="<?php echo $app_strings['LBL_CANCEL_BUTTON_LABEL']?>"/></td>
 174 </tr>
 175 </table>
 176 </form>
 177 </div>
 178 </p>
 179 <?php
 180 // draw table
 181 echo "<p align='center'>".$this->gen_xml($datax, $ids, $cache_file_name, $refresh,$current_module_strings)."</p>";
 182 echo "<P align='center'><span class='chartFootnote'>".$current_module_strings['LBL_LEAD_SOURCE_FORM_DESC']."</span></P>";
 183
 184         if (file_exists($cache_file_name)) {
 185 global $timedate;
 186                 $file_date = $timedate->asUser($timedate->fromTimestamp(filemtime($cache_file_name)));
 187         }
 188         else {
 189                 $file_date = '';
 190         }
 191 ?>
 192 <span class='chartFootnote'>
 193 <p align="right"><i><?php  echo $current_module_strings['LBL_CREATED_ON'].' '.$file_date; ?></i></p>
 194 </span>
 195 <?Php
 196 }
 197
 198
 199
 200         /**
 201         * Creates PIE CHART image of opportunities by lead_source.
 202         * param $datax- the sales stage data to display in the x-axis
 203         * param $datay- the sum of opportunity amounts for each opportunity in each sales stage
 204         * to display in the y-axis
 205         * Portions created by SugarCRM are Copyright (C) SugarCRM, Inc..
 206         * All Rights Reserved..
 207         * Contributor(s): ______________________________________..
 208         */
 209         function gen_xml($legends=array('foo','bar'), $user_id=array('1'), $cache_file_name='a_file', $refresh=true,$current_module_strings) {
 210                 global $app_strings, $charset, $lang, $pieChartColors, $current_user;
 211
 212                 $kDelim = $current_user->getPreference('num_grp_sep');
 213
 214                 if (!file_exists($cache_file_name) || $refresh == true) {
 215                         ;
 216                         $GLOBALS['log']->debug("starting pipeline chart");
 217                         $GLOBALS['log']->debug("legends is:");
 218                         $GLOBALS['log']->debug($legends);
 219                         $GLOBALS['log']->debug("user_id is: ");
 220                         $GLOBALS['log']->debug($user_id);
 221                         $GLOBALS['log']->debug("cache_file_name is: $cache_file_name");
 222
 223                         $opp = new Opportunity;
 224                         //Now do the db queries
 225                         //query for opportunity data that matches $legends and $user
 226                         $where="";
 227                         //build the where clause for the query that matches $user
 228
 229                         $count = count($user_id);
 230                         $id = array();
 231                         if ($count > 0 && !empty($user_id)) {
 232                                 foreach ($user_id as $the_id) {
 233                                         $id[] = "'".$the_id."'";
 234                                 }
 235                                 $ids = join(",",$id);
 236                                 $where .= "opportunities.assigned_user_id IN ($ids) ";
 237
 238                         }
 239                         if(!empty($where)) $where .= 'AND';
 240                         //build the where clause for the query that matches $datax
 241                         $count = count($legends);
 242                         $legendItem = array();
 243                         if ($count > 0 && !empty($legends)) {
 244
 245                                 foreach ($legends as $key=>$value) {
 246                                         $legendItem[] = "'".$key."'";
 247                                 }
 248                                 $legendItems = join(",",$legendItem);
 249                                 $where .= " opportunities.lead_source IN        ($legendItems) ";
 250                         }
 251                         $query = "SELECT lead_source,sum(amount_usdollar/1000) as total,count(*) as opp_count FROM opportunities ";
 252                         $query .= "WHERE ".$where." AND opportunities.deleted=0 ";
 253                         $query .= "GROUP BY lead_source ORDER BY total DESC";
 254
 255                         //build pipeline by lead source data
 256                         $total = 0;
 257                         $div = 1;
 258                         global $sugar_config;
 259                         $symbol = $sugar_config['default_currency_symbol'];
 260                         global $current_user;
 261                         if($current_user->getPreference('currency') ) {
 262                                 $currency = new Currency();
 263                                 $currency->retrieve($current_user->getPreference('currency'));
 264                                 $div = $currency->conversion_rate;
 265                                 $symbol = $currency->symbol;
 266                         }
 267                         $subtitle = $current_module_strings['LBL_OPP_SIZE'].' '.$symbol.'1'.$current_module_strings['LBL_OPP_THOUSANDS'];
 268                         $fileContents = '';
 269                         $fileContents .= '     <pie defaultAltText="'.$current_module_strings['LBL_ROLLOVER_WEDGE_DETAILS'].'" legendStatus="on">'."\n";
 270                         $result = $opp->db->query($query, true);
 271                         $leadSourceArr =  array();
 272                         while($row = $opp->db->fetchByAssoc($result, false))
 273                         {
 274                                 if($row['lead_source'] == ''){
 275                                         $leadSource = $current_module_strings['NTC_NO_LEGENDS'];
 276                                 } else {
 277                                         $leadSource = $row['lead_source'];
 278                                 }
 279                                 if($row['total']*$div<=100){
 280                                         $sum = round($row['total']*$div, 2);
 281                                 } else {
 282                                         $sum = round($row['total']*$div);
 283                                 }
 284
 285                                 $leadSourceArr[$leadSource]['opp_count'] = $row['opp_count'];
 286                                 $leadSourceArr[$leadSource]['sum'] = $sum;
 287                         }
 288                         $i=0;
 289                         foreach ($legends as $lead_source_key=>$translation) {
 290                                 if ($lead_source_key == '') {
 291                                         $lead_source_key = $current_module_strings['NTC_NO_LEGENDS'];
 292                                         $translation = $current_module_strings['NTC_NO_LEGENDS'];
 293                                 }
 294                                 if(!isset($leadSourceArr[$lead_source_key])) {
 295                                         $leadSourceArr[$lead_source_key] = $lead_source_key;
 296                                         $leadSourceArr[$lead_source_key]['sum'] = 0;
 297                                 }
 298                                 $color = generate_graphcolor($lead_source_key,$i);
 299                                 $fileContents .= '          <wedge title="'.$translation.'" kDelim="'.$kDelim.'" value="'.$leadSourceArr[$lead_source_key]['sum'].'" color="'.$color.'" labelText="'.currency_format_number($leadSourceArr[$lead_source_key]['sum'], array('currency_symbol' => true)).'" url="index.php?module=Opportunities&action=index&lead_source='.urlencode($lead_source_key).'&query=true&searchFormTab=advanced_search" altText="'.format_number($leadSourceArr[$lead_source_key]['opp_count'], 0, 0).' '.$current_module_strings['LBL_OPPS_IN_LEAD_SOURCE'].' '.$translation.'"/>'."\n";
 300                                 if(isset($leadSourceArr[$lead_source_key])){$total += $leadSourceArr[$lead_source_key]['sum'];}
 301                                 $i++;
 302                         }
 303
 304                         $fileContents .= '     </pie>'."\n";
 305                         $fileContents .= '     <graphInfo>'."\n";
 306                         $fileContents .= '          <![CDATA[]]>'."\n";
 307                         $fileContents .= '     </graphInfo>'."\n";
 308                         $fileContents .= '     <chartColors ';
 309                         foreach ($pieChartColors as $key => $value) {
 310                                 $fileContents .= ' '.$key.'='.'"'.$value.'" ';
 311                         }
 312                         $fileContents .= ' />'."\n";
 313                         $fileContents .= '</graphData>'."\n";
 314                         $total = round($total, 2);
 315                         $title = $current_module_strings['LBL_TOTAL_PIPELINE'].currency_format_number($total, array('currency_symbol' => true)).$app_strings['LBL_THOUSANDS_SYMBOL'];
 316                         $fileContents = '<graphData title="'.$title.'" subtitle="'.$subtitle.'">'."\n" . $fileContents;
 317                         $GLOBALS['log']->debug("total is: $total");
 318                         if ($total == 0) {
 319                                 return ($current_module_strings['ERR_NO_OPPS']);
 320                         }
 321
 322                         save_xml_file($cache_file_name, $fileContents);
 323                 }
 324
 325                 $return = create_chart('pieF',$cache_file_name);
 326                 return $return;
 327
 328         }
 329
 330         function constructQuery(){
 331                 global $current_user;
 332                 global $app_list_strings;
 333
 334                 $tempx = array();
 335                 $datax = array();
 336                 $selected_datax = array();
 337                 //get list of sales stage keys to display
 338                 global $current_user;
 339                 $user_tempx = $current_user->getPreference('pbls_lead_sources');
 340                 if (!empty($user_tempx) && count($user_tempx) > 0 && !isset($_REQUEST['pbls_lead_sources'])) {
 341                         $tempx = $user_tempx;
 342                         $GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
 343                         $GLOBALS['log']->debug($user_tempx);
 344                 }
 345                 elseif (isset($_REQUEST['pbls_lead_sources']) && count($_REQUEST['pbls_lead_sources']) > 0) {
 346                         $tempx = $_REQUEST['pbls_lead_sources'];
 347                         $current_user->setPreference('pbls_lead_sources', $_REQUEST['pbls_lead_sources']);
 348                         $GLOBALS['log']->debug("_REQUEST['pbls_lead_sources'] is:");
 349                         $GLOBALS['log']->debug($_REQUEST['pbls_lead_sources']);
 350                         $GLOBALS['log']->debug("USER PREFERENCES['pbls_lead_sources'] is:");
 351                         $GLOBALS['log']->debug($current_user->getPreference('pbls_lead_sources'));
 352                 }
 353
 354                 //set $datax using selected sales stage keys
 355                 if (count($tempx) > 0) {
 356                         foreach ($tempx as $key) {
 357                                 $datax[$key] = $app_list_strings['lead_source_dom'][$key];
 358                                 array_push($selected_datax,$key);
 359                         }
 360                 }
 361                 else {
 362                         $datax = $app_list_strings['lead_source_dom'];
 363                         $selected_datax = array_keys($app_list_strings['lead_source_dom']);
 364                 }
 365
 366                 $legends = $datax;
 367
 368                 $ids = array();
 369                 $user_ids = $current_user->getPreference('pbls_ids');
 370                 //get list of user ids for which to display data
 371                 if (!empty($user_ids) && count($user_ids) != 0 && !isset($_REQUEST['pbls_ids'])) {
 372                         if(isset($_SESSION['pbls_ids'])) {$ids = $_SESSION['pbls_ids'];}
 373                         $GLOBALS['log']->debug("USER PREFERENCES['pbls_ids'] is:");
 374                         $GLOBALS['log']->debug($user_ids);
 375                 }
 376                 elseif (isset($_REQUEST['pbls_ids']) && count($_REQUEST['pbls_ids']) > 0) {
 377                         $ids = $_REQUEST['pbls_ids'];
 378                         $current_user->setPreference('pbls_ids', $ids);
 379                         $GLOBALS['log']->debug("_REQUEST['pbls_ids'] is:");
 380                         $GLOBALS['log']->debug($_REQUEST['pbls_ids']);
 381                         $GLOBALS['log']->debug("USER PREFERENCES['pbls_ids'] is:");
 382                         $GLOBALS['log']->debug($current_user->getPreference('pbls_ids'));
 383                 }
 384                 else {
 385                         $ids = get_user_array(false);
 386                         $ids = array_keys($ids);
 387                 }
 388
 389                 $user_id = $ids;
 390
 391                 $opp = new Opportunity;
 392                 //Now do the db queries
 393                 //query for opportunity data that matches $legends and $user
 394                 $where="";
 395                 //build the where clause for the query that matches $user
 396
 397                 $count = count($user_id);
 398                 $id = array();
 399                 if ($count > 0 && !empty($user_id)) {
 400                         foreach ($user_id as $the_id) {
 401                                 $id[] = "'".$the_id."'";
 402                         }
 403                         $ids = join(",",$id);
 404                         $where .= "opportunities.assigned_user_id IN ($ids) ";
 405
 406                 }
 407                 if(!empty($where)) $where .= 'AND';
 408                 //build the where clause for the query that matches $datax
 409                 $count = count($legends);
 410                 $legendItem = array();
 411                 if ($count > 0 && !empty($legends)) {
 412
 413                         foreach ($legends as $key=>$value) {
 414                                 $legendItem[] = "'".$key."'";
 415                         }
 416                         $legendItems = join(",",$legendItem);
 417                         $where .= " opportunities.lead_source IN        ($legendItems) ";
 418                 }
 419                 $query = "SELECT lead_source,sum(amount_usdollar/1000) as total,count(*) as opp_count FROM opportunities ";
 420                 $query .= "WHERE ".$where." AND opportunities.deleted=0 ";
 421                 $query .= "GROUP BY lead_source ORDER BY total DESC";
 422
 423                 return $query;
 424         }
 425
 426         function constructGroupBy(){
 427                 return array( 'lead_source', );
 428         }
 429 }
 430 ?>