get_date_format();
$current_module_strings = return_module_language($current_language, 'Charts');
if (isset($_REQUEST['pbss_refresh'])) { $refresh = $_REQUEST['pbss_refresh']; }
else { $refresh = false; }
//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);
}
// cn: format date_start|end to user's preferred
$dateDisplayStart = strftime($timedate->get_user_date_format(), strtotime($date_start));
$dateDisplayEnd = strftime($timedate->get_user_date_format(), strtotime($date_end));
$seps = array("-", "/");
$dates = array($date_start, $date_end);
$dateFileNameSafe = str_replace($seps, "_", $dates);
$dateXml[0] = $timedate->swap_formats($date_start, $user_dateFormat, $timedate->dbDayFormat);
$dateXml[1] = $timedate->swap_formats($date_end, $user_dateFormat, $timedate->dbDayFormat);
$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) && count($user_tempx) > 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']) && count($_REQUEST['pbss_sales_stages']) > 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 (count($tempx) > 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) && count($user_ids) != 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']) && count($_REQUEST['pbss_ids']) > 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);
}
//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);
$cache_file_name = sugar_cached("xml/").$current_user->getUserPrivGuid()."_lead_source_by_outcome_".$dateFileNameSafe[0]."_".$dateFileNameSafe[1].".xml";
$GLOBALS['log']->debug("cache file name is: $cache_file_name");
$tools='
'.SugarThemeRegistry::current()->getImage('refresh','border="0" align="absmiddle"',null,null,'.gif',$mod_strings['LBL_REFRESH']).' '.$current_module_strings['LBL_REFRESH'].' '.SugarThemeRegistry::current()->getImage('edit','border="0" align="absmiddle"',null,null,'.gif',$mod_strings['LBL_EDIT']).' '. $current_module_strings['LBL_EDIT'].' '.$extra_tools.'
';
echo '' . get_form_header($current_module_strings['LBL_SALES_STAGE_FORM_TITLE'], $tools , false) . ''; ?>
get_cal_date_format();
if (empty($_SESSION['pbss_sales_stages'])) $_SESSION['pbss_sales_stages'] = "";
if (empty($_SESSION['pbss_ids'])) $_SESSION['pbss_ids'] = "";
// set populate values
$puser_date_start = $current_user->getPreference('user_date_start');
?>
".$this->gen_xml($datax, $dateXml[0], $dateXml[1], $ids, $cache_file_name, $refresh,'hBarF',$current_module_strings)."";
echo "";
if (file_exists($cache_file_name)) {
$file_date = $timedate->asUser($timedate->fromTimestamp(filemtime($cache_file_name)));
}
else {
$file_date = '';
}
?>
getPreference('num_grp_sep');
global $timedate;
if (!file_exists($cache_file_name) || $refresh == true) {
$GLOBALS['log']->debug("starting pipeline chart");
$GLOBALS['log']->debug("datax is:");
$GLOBALS['log']->debug($datax);
$GLOBALS['log']->debug("user_id is: ");
$GLOBALS['log']->debug($user_id);
$GLOBALS['log']->debug("cache_file_name is: $cache_file_name");
$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."'",'date'). "
AND opportunities.date_closed <= ".db_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,users.user_name,opportunities.assigned_user_id";
$result = $opp->db->query($query, true);
//build pipeline by sales stage data
$total = 0;
$div = 1;
global $sugar_config;
$symbol = $sugar_config['default_currency_symbol'];
global $current_user;
if($current_user->getPreference('currency') ){
$currency = new Currency();
$currency->retrieve($current_user->getPreference('currency'));
$div = $currency->conversion_rate;
$symbol = $currency->symbol;
}
// cn: adding user-pref date handling
$dateStartDisplay = $timedate->asUserDate($timedate->fromString($date_start));
$dateEndDisplay = $timedate->asUserDate($timedate->fromString($date_end));
$fileContents = ' '."\n";
$stageArr = array();
$usernameArr = array();
$rowTotalArr = array();
$rowTotalArr[] = 0;
while($row = $opp->db->fetchByAssoc($result, false))
{
if($row['total']*$div<=100){
$sum = round($row['total']*$div, 2);
} else {
$sum = round($row['total']*$div);
}
if(!isset($stageArr[$row['sales_stage']]['row_total'])) {$stageArr[$row['sales_stage']]['row_total']=0;}
$stageArr[$row['sales_stage']][$row['assigned_user_id']]['opp_count'] = $row['opp_count'];
$stageArr[$row['sales_stage']][$row['assigned_user_id']]['total'] = $sum;
$stageArr[$row['sales_stage']]['people'][$row['assigned_user_id']] = $row['user_name'];
$stageArr[$row['sales_stage']]['row_total'] += $sum;
$usernameArr[$row['assigned_user_id']] = $row['user_name'];
$total += $sum;
}
foreach ($datax as $key=>$translation) {
if(isset($stageArr[$key]['row_total'])){$rowTotalArr[]=$stageArr[$key]['row_total'];}
if(isset($stageArr[$key]['row_total']) && $stageArr[$key]['row_total']>100) {
$stageArr[$key]['row_total'] = round($stageArr[$key]['row_total']);
}
$fileContents .= ' '."\n";
if(isset($stageArr[$key]['people'])){
asort($stageArr[$key]['people']);
reset($stageArr[$key]['people']);
foreach ($stageArr[$key]['people'] as $nameKey=>$nameValue) {
$fileContents .= ' '."\n";
}
}
$fileContents .= ' '."\n";
}
$fileContents .= ' '."\n";
$max = get_max($rowTotalArr);
if($chart_size=='hBarF'){
$length = "10";
}else{
$length = "4";
}
$fileContents .= ' '."\n";
$fileContents .= ' '."\n";
$i=0;
asort($new_ids);
foreach ($new_ids as $key=>$value) {
$color = generate_graphcolor($key,$i);
$fileContents .= ' '."\n";
$i++;
}
$fileContents .= ' '."\n";
$fileContents .= ' '."\n";
$fileContents .= ' '.$current_module_strings['LBL_OPP_SIZE'].' '.$symbol.'1'.$current_module_strings['LBL_OPP_THOUSANDS'].']]>'."\n";
$fileContents .= ' '."\n";
$fileContents .= ' $value) {
$fileContents .= ' '.$key.'='.'"'.$value.'" ';
}
$fileContents .= ' />'."\n";
$fileContents .= ''."\n";
$total = $total;
$title = ''."\n";
$fileContents = $title.$fileContents;
save_xml_file($cache_file_name, $fileContents);
}
if($chart_size=='hBarF'){
$width = "800";
$height = "400";
} else {
$width = "350";
$height = "400";
}
$return = create_chart($chart_size,$cache_file_name,$width,$height);
return $return;
}
function constructQuery(){
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) && count($user_tempx) > 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']) && count($_REQUEST['pbss_sales_stages']) > 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 (count($tempx) > 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) && count($user_ids) != 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']) && count($_REQUEST['pbss_ids']) > 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 = 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."'",'date'). "
AND opportunities.date_closed <= ".db_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,users.user_name,opportunities.assigned_user_id";
return $query;
}
function constructGroupBy(){
return array( 'sales_stage', 'user_name' );
}
}
?>