mypipelinebysalesstagedashlet.php

SugarCRM5.1 开源PHP客户关系管理系统

        $selected_datax = array();
        //get list of sales stage keys to display
        $user_sales_stage = $this->mypbss_sales_stages;
        $tempx = $user_sales_stage;
        
        //set $datax using selected sales stage keys
        if (count($tempx) > 0) {
            foreach ($tempx as $key) {
                $datax[$key] = $app_list_strings['sales_stage_dom'][$key];
                array_push($selected_datax, $key);
            }
        }
        else {
            $datax = $app_list_strings['sales_stage_dom'];
            $selected_datax = array_keys($app_list_strings['sales_stage_dom']);
        }
        $GLOBALS['log']->debug("datax is:");
        $GLOBALS['log']->debug($datax);

        $ids = array($current_user->id);
        //create unique prefix based on selected users for image files
        $id_hash = '1';
        if (isset($ids)) {
            sort($ids);
            $id_hash = crc32(implode('',$ids));
            if($id_hash < 0)
            {
                $id_hash = $id_hash * -1;
            }
        }
        $GLOBALS['log']->debug("ids is:");
        $GLOBALS['log']->debug($ids);
        $id_md5 = substr(md5($current_user->id),0,9);
        $seps               = array("-", "/");
        $dates              = array($dateStartDisplay, $dateEndDisplay);
        $dateFileNameSafe   = str_replace($seps, "_", $dates);
        $cache_file_name = $current_user->getUserPrivGuid()."_".$theme."_my_pipeline_".$dateFileNameSafe[0]."_".$dateFileNameSafe[1].".xml";
        
        $GLOBALS['log']->debug("cache file name is: $cache_file_name");
        
        if (file_exists($sugar_config['tmp_dir'].$cache_file_name)) {
            $file_date = date($timedate->get_date_format()." ".$timedate->get_time_format(), filemtime($sugar_config['tmp_dir'].$cache_file_name));
        }
        else {
            $file_date = '';
        }

		require_once('include/Sugar_Smarty.php');
		require_once('include/SugarCharts/SugarChart.php');
		
		$sugar_smarty = new Sugar_Smarty();
	
		$charts = array();
	
		$sugarChart = new SugarChart();
		
		$sugarChart->base_url = array( 	'module' => 'Opportunities',
								'action' => 'index',
								'query' => 'true',
								'searchFormTab' => 'advanced_search',
							 );
		$sugarChart->url_params = array( 'assigned_user_id' => $current_user->id );		

		$sugarChart->group_by = $this->constructGroupBy();
		$query = $this->constructQuery($datax, $dateXml[0], $dateXml[1], $ids, $sugar_config['tmp_dir'].$cache_file_name, $refresh,'hBarS',$current_module_strings);

		$total = format_number($sugarChart->getTotal(), 0, 0);
		
		$currency_symbol = $sugar_config['default_currency_symbol'];
		
		if ($current_user->getPreference('currency')){
		    require_once('modules/Currencies/Currency.php');
            $currency = new Currency();
            $currency->retrieve($current_user->getPreference('currency'));
            $currency_symbol = $currency->symbol;
        }		
        
        $sugarChart->is_currency = true;
        $sugarChart->thousands_symbol = translate('LBL_OPP_THOUSANDS', 'Charts');
        
        $subtitle = translate('LBL_OPP_SIZE', 'Charts') . " " . $currency_symbol . "1" . translate('LBL_OPP_THOUSANDS', 'Charts');
		



			$dataset = $this->constructCEChartData($this->getChartData($query));
			$sugarChart->setData($dataset);
			$total = format_number($this->getHorizBarTotal($dataset), 0, 0);		
			$pipeline_total_string = translate('LBL_TOTAL_PIPELINE', 'Charts') . $sugarChart->currency_symbol . $total . $sugarChart->thousands_symbol;
			$sugarChart->setProperties($pipeline_total_string, $subtitle, 'horizontal bar chart');









			
		$xmlFile = $sugar_config['tmp_dir']. $current_user->id . '_' . $this->id . '.xml';
	
		$sugarChart->saveXMLFile($xmlFile, $sugarChart->generateXML());
		$returnStr .= $sugarChart->display($this->id, $xmlFile, '100%', '480', false);       
	        
	    return $this->getTitle('') . '<div align="center">' .$returnStr . '</div><br />';		
    }

	// awu: Bug 16794 - this function is a hack to get the correct sales stage order until i can clean it up later     
    function getChartData($query){
    	global $app_list_strings, $current_user, $sugar_config;
    	
    	$data = array();
    	$temp_data = array();
    	$selected_datax = array();
    	
    	$user_sales_stage = $this->mypbss_sales_stages;
        $tempx = $user_sales_stage;
        
        //set $datax using selected sales stage keys
        if (count($tempx) > 0) {
            foreach ($tempx as $key) {
                $datax[$key] = $app_list_strings['sales_stage_dom'][$key];
                array_push($selected_datax, $key);
            }
        }
        else {
            $datax = $app_list_strings['sales_stage_dom'];
            $selected_datax = array_keys($app_list_strings['sales_stage_dom']);
        }
        
        $db = &PearDatabase::getInstance();
        
        $result = $db->query($query);
        $row = $db->fetchByAssoc($result, -1, false);
        
        while($row != null){
        	array_push($temp_data, $row);
        	$row = $db->fetchByAssoc($result, -1, false);
        }

		// reorder and set the array based on the order of selected_datax        
        foreach($selected_datax as $sales_stage){
        	foreach($temp_data as $key => $value){
        		if ($value['sales_stage'] == $sales_stage){
        			//$value['total'] = $symbol . $value['total'];
        			$value['sales_stage'] = $app_list_strings['sales_stage_dom'][$value['sales_stage']];
        			array_push($data, $value);
        			unset($temp_data[$key]);
        		}
        	}
        }
        return $data;
    }
    
    function getHorizBarTotal($dataset){
    	$total = 0;
    	foreach($dataset as $value){
    		$total += $value;
    	}
    	
    	return $total;
    }
    
    function constructCEChartData($dataset){
    	$newData = array();
    	foreach($dataset as $key=>$value){
    		$newData[$value['sales_stage']] = $value['total'];
    	}
    	return $newData;	
    }
       
    function constructQuery($datax=array('foo','bar'), $date_start='2071-10-15', $date_end='2071-10-15', $user_id=array('1'), $cache_file_name='a_file', $refresh=false,$chart_size='hBarF',$current_module_strings) {
        global $app_strings, $charset, $lang, $barChartColors, $current_user, $theme;
        require_once('themes/' . $theme . '/layout_utils.php');
        require_once('modules/Currencies/Currency.php');
        require_once('modules/Opportunities/Opportunity.php');
        $kDelim = $current_user->getPreference('num_grp_sep');
        global $timedate;

        $opp = new Opportunity;
        $where="";
        //build the where clause for the query that matches $user
        $count = count($user_id);
        $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 = join(",",$id);
            $where .= "opportunities.assigned_user_id IN ($ids) ";

        }

        //build the where clause for the query that matches $datax
        $count = count($datax);
        $dataxArr = array();
        if ($count>0) {

            foreach ($datax as $key=>$value) {
                $dataxArr[] = "'".$key."'";
            }
            $dataxArr = join(",",$dataxArr);
            $where .= "AND opportunities.sales_stage IN ($dataxArr) ";
        }

        //build the where clause for the query that matches $date_start and $date_end
        $where .= " AND opportunities.date_closed >= ". db_convert("'".$date_start."'",'datetime'). " 
                    AND opportunities.date_closed <= ".db_convert("'".$date_end."'",'datetime') ;
        $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";
        $query .= ",users.user_name,opportunities.assigned_user_id";

		return $query;
    }
    
    function constructGroupBy(){
    	$groupBy = array('sales_stage');
    	



    		array_push($groupBy, 'user_name');



    	return $groupBy; 
    }
}

?>