/**
* Creates opportunity pipeline image as a VERTICAL accumlated bar graph for multiple users.
* param $datax- the month data to display in the x-axis
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc..
* All Rights Reserved..
* Contributor(s): ______________________________________..
*/
function gen_xml($date_start = '1971-10-15', $date_end = '2010-10-15', $user_id = array('1'), $cache_file_name = 'a_file', $refresh = false, $current_module_strings)
{
global $app_strings, $app_list_strings, $charset, $lang, $barChartColors, $current_user;
$kDelim = $current_user->getPreference('num_grp_sep');
global $timedate;
if (!file_exists($cache_file_name) || $refresh == true) {
Log::debug("date_start is: {$date_start}");
Log::debug("date_end is: {$date_end}");
Log::debug("user_id is: ");
Log::debug($user_id);
Log::debug("cache_file_name is: {$cache_file_name}");
$where = "";
//build the where clause for the query that matches $user
$count = count($user_id);
$id = array();
if ($count > 0) {
foreach ($user_id as $the_id) {
$id[] = "'" . $the_id . "'";
}
$ids = join(",", $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 = new Opportunity();
//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') . " AND opportunities.deleted=0";
$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 ";
$query .= "WHERE " . $where;
$query .= " GROUP BY sales_stage," . db_convert('opportunities.date_closed', 'date_format', array("'%Y-%m'"), array("'YYYY-MM'")) . "ORDER BY m";
//Now do the db queries
//query for opportunity data that matches $datay and $user
//_pp($query);
$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'];
$other = $current_module_strings['LBL_LEAD_SOURCE_OTHER'];
$rowTotalArr = array();
$rowTotalArr[] = 0;
global $current_user;
$salesStages = array("Closed Lost" => $app_list_strings['sales_stage_dom']["Closed Lost"], "Closed Won" => $app_list_strings['sales_stage_dom']["Closed Won"], "Other" => $other);
if ($current_user->getPreference('currency')) {
$currency = new Currency();
$currency->retrieve($current_user->getPreference('currency'));
$div = $currency->conversion_rate;
$symbol = $currency->symbol;
}
$months = array();
$monthArr = array();
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 ($row['sales_stage'] == 'Closed Won' || $row['sales_stage'] == 'Closed Lost') {
$salesStage = $row['sales_stage'];
$salesStageT = $app_list_strings['sales_stage_dom'][$row['sales_stage']];
} else {
$salesStage = "Other";
$salesStageT = $other;
}
$months[$row['m']] = $row['m'];
if (!isset($monthArr[$row['m']]['row_total'])) {
$monthArr[$row['m']]['row_total'] = 0;
}
$monthArr[$row['m']][$salesStage]['opp_count'][] = $row['opp_count'];
$monthArr[$row['m']][$salesStage]['total'][] = $sum;
$monthArr[$row['m']]['outcome'][$salesStage] = $salesStageT;
$monthArr[$row['m']]['row_total'] += $sum;
$total += $sum;
}
$fileContents = ' <xData length="20">' . "\n";
if (!empty($months)) {
foreach ($months as $month) {
$rowTotalArr[] = $monthArr[$month]['row_total'];
if ($monthArr[$month]['row_total'] > 100) {
$monthArr[$month]['row_total'] = round($monthArr[$month]['row_total']);
}
$fileContents .= ' <dataRow title="' . $month . '" endLabel="' . currency_format_number($monthArr[$month]['row_total'], array('currency_symbol' => true)) . '">' . "\n";
arsort($salesStages);
foreach ($salesStages as $outcome => $outcome_translation) {
if (isset($monthArr[$month][$outcome])) {
$fileContents .= ' <bar id="' . $outcome . '" totalSize="' . array_sum($monthArr[$month][$outcome]['total']) . '" altText="' . $month . ': ' . format_number(array_sum($monthArr[$month][$outcome]['opp_count']), 0, 0) . ' ' . $current_module_strings['LBL_OPPS_WORTH'] . ' ' . currency_format_number(array_sum($monthArr[$month][$outcome]['total']), array('currency_symbol' => true)) . $current_module_strings['LBL_OPP_THOUSANDS'] . ' ' . $current_module_strings['LBL_OPPS_OUTCOME'] . ' ' . $outcome_translation . '" url="index.php?module=Opportunities&action=index&date_closed=' . $month . '&sales_stage=' . urlencode($outcome) . '&query=true&searchFormTab=advanced_search"/>' . "\n";
}
}
$fileContents .= ' </dataRow>' . "\n";
}
} else {
$fileContents .= ' <dataRow title="" endLabel="">' . "\n";
$fileContents .= ' <bar id="" totalSize="0" altText="" url=""/>' . "\n";
$fileContents .= ' </dataRow>' . "\n";
$rowTotalArr[] = 1000;
}
$fileContents .= ' </xData>' . "\n";
$max = get_max($rowTotalArr);
$fileContents .= ' <yData min="0" max="' . $max . '" length="10" prefix="' . $symbol . '" suffix="" kDelim="' . $kDelim . '" defaultAltText="' . $current_module_strings['LBL_ROLLOVER_DETAILS'] . '"/>' . "\n";
$fileContents .= ' <colorLegend status="on">' . "\n";
$i = 0;
asort($salesStages);
foreach ($salesStages as $outcome => $outcome_translation) {
$color = generate_graphcolor($outcome, $i);
$fileContents .= ' <mapping id="' . $outcome . '" name="' . $outcome_translation . '" color="' . $color . '"/>' . "\n";
$i++;
}
$fileContents .= ' </colorLegend>' . "\n";
$fileContents .= ' <graphInfo>' . "\n";
$fileContents .= ' <![CDATA[' . $current_module_strings['LBL_DATE_RANGE'] . " " . $dateStartDisplay . " " . $current_module_strings['LBL_DATE_RANGE_TO'] . " " . $dateEndDisplay . "<br/>" . $current_module_strings['LBL_OPP_SIZE'] . ' ' . $symbol . '1' . $current_module_strings['LBL_OPP_THOUSANDS'] . ']]>' . "\n";
$fileContents .= ' </graphInfo>' . "\n";
$fileContents .= ' <chartColors ';
foreach ($barChartColors as $key => $value) {
$fileContents .= ' ' . $key . '=' . '"' . $value . '" ';
}
$fileContents .= ' />' . "\n";
$fileContents .= '</graphData>' . "\n";
$total = round($total, 2);
$title = '<graphData title="' . $current_module_strings['LBL_TOTAL_PIPELINE'] . currency_format_number($total, array('currency_symbol' => true)) . $app_strings['LBL_THOUSANDS_SYMBOL'] . '">' . "\n";
$fileContents = $title . $fileContents;
//echo $fileContents;
save_xml_file($cache_file_name, $fileContents);
}
$return = create_chart('vBarF', $cache_file_name);
return $return;
}
/**
* Creates PIE CHART image of opportunities by lead_source.
* param $datax- the sales stage data to display in the x-axis
* param $datay- the sum of opportunity amounts for each opportunity in each sales stage
* to display in the y-axis
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc..
* All Rights Reserved..
* Contributor(s): ______________________________________..
*/
function gen_xml($legends = array('foo', 'bar'), $user_id = array('1'), $cache_file_name = 'a_file', $refresh = true, $current_module_strings)
{
global $app_strings, $charset, $lang, $pieChartColors, $current_user;
require_once 'modules/Currencies/Currency.php';
$kDelim = $current_user->getPreference('num_grp_sep');
if (!file_exists($cache_file_name) || $refresh == true) {
$GLOBALS['log']->debug("starting pipeline chart");
$GLOBALS['log']->debug("legends is:");
$GLOBALS['log']->debug($legends);
$GLOBALS['log']->debug("user_id is: ");
$GLOBALS['log']->debug($user_id);
$GLOBALS['log']->debug("cache_file_name is: {$cache_file_name}");
$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 = count($user_id);
$id = array();
if ($count > 0 && !empty($user_id)) {
foreach ($user_id as $the_id) {
$id[] = "'" . $the_id . "'";
}
$ids = join(",", $id);
$where .= "opportunities.assigned_user_id IN ({$ids}) ";
}
if (!empty($where)) {
$where .= 'AND';
}
//build the where clause for the query that matches $datax
$count = count($legends);
$legendItem = array();
if ($count > 0 && !empty($legends)) {
foreach ($legends as $key => $value) {
$legendItem[] = "'" . $key . "'";
}
$legendItems = join(",", $legendItem);
$where .= " opportunities.lead_source IN\t({$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";
//build pipeline by lead source 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;
}
$subtitle = $current_module_strings['LBL_OPP_SIZE'] . ' ' . $symbol . '1' . $current_module_strings['LBL_OPP_THOUSANDS'];
$fileContents = '';
$fileContents .= ' <pie defaultAltText="' . $current_module_strings['LBL_ROLLOVER_WEDGE_DETAILS'] . '" legendStatus="on">' . "\n";
$result = $opp->db->query($query) or sugar_die("Error selecting sugarbean: " . mysql_error());
$leadSourceArr = array();
while ($row = $opp->db->fetchByAssoc($result, -1, false)) {
if ($row['lead_source'] == '') {
$leadSource = $current_module_strings['NTC_NO_LEGENDS'];
} else {
$leadSource = $row['lead_source'];
}
if ($row['total'] * $div <= 100) {
$sum = round($row['total'] * $div, 2);
} else {
$sum = round($row['total'] * $div);
}
$leadSourceArr[$leadSource]['opp_count'] = $row['opp_count'];
$leadSourceArr[$leadSource]['sum'] = $sum;
}
$i = 0;
foreach ($legends as $lead_source_key => $translation) {
if ($lead_source_key == '') {
$lead_source_key = $current_module_strings['NTC_NO_LEGENDS'];
$translation = $current_module_strings['NTC_NO_LEGENDS'];
}
if (!isset($leadSourceArr[$lead_source_key])) {
$leadSourceArr[$lead_source_key] = $lead_source_key;
$leadSourceArr[$lead_source_key]['sum'] = 0;
}
$color = generate_graphcolor($lead_source_key, $i);
$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";
if (isset($leadSourceArr[$lead_source_key])) {
$total += $leadSourceArr[$lead_source_key]['sum'];
}
$i++;
}
$fileContents .= ' </pie>' . "\n";
$fileContents .= ' <graphInfo>' . "\n";
$fileContents .= ' <![CDATA[]]>' . "\n";
$fileContents .= ' </graphInfo>' . "\n";
$fileContents .= ' <chartColors ';
foreach ($pieChartColors as $key => $value) {
$fileContents .= ' ' . $key . '=' . '"' . $value . '" ';
}
$fileContents .= ' />' . "\n";
$fileContents .= '</graphData>' . "\n";
$total = round($total, 2);
$title = $current_module_strings['LBL_TOTAL_PIPELINE'] . currency_format_number($total, array('currency_symbol' => true)) . $app_strings['LBL_THOUSANDS_SYMBOL'];
$fileContents = '<graphData title="' . $title . '" subtitle="' . $subtitle . '">' . "\n" . $fileContents;
$GLOBALS['log']->debug("total is: {$total}");
if ($total == 0) {
return $current_module_strings['ERR_NO_OPPS'];
}
save_xml_file($cache_file_name, $fileContents);
}
$return = create_chart('pieF', $cache_file_name);
return $return;
}
/**
* Creates lead_source_by_outcome pipeline image as a HORIZONAL accumlated bar graph for multiple users.
* param $datay- the lead source data to display in the x-axis
* param $ids - list of assigned users of opps to find
* param $cache_file_name - file name to write image to
* param $refresh - boolean whether to rebuild image if exists
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc..
* All Rights Reserved..
* Contributor(s): ______________________________________..
*/
function gen_xml($datay = array('foo', 'bar'), $user_id = array('1'), $cache_file_name = 'a_file', $refresh = false, $current_module_strings)
{
global $app_strings, $charset, $lang, $barChartColors, $app_list_strings, $current_user;
$kDelim = $current_user->getPreference('num_grp_sep');
if (!file_exists($cache_file_name) || $refresh == true) {
$GLOBALS['log']->debug("datay is:");
$GLOBALS['log']->debug($datay);
$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();
if ($count > 0) {
foreach ($user_id as $the_id) {
$id[] = "'" . $the_id . "'";
}
$ids = join(",", $id);
$where .= "opportunities.assigned_user_id IN ({$ids}) ";
}
//build the where clause for the query that matches $datay
$count = count($datay);
$datayArr = array();
if ($count > 0) {
foreach ($datay as $key => $value) {
$datayArr[] = "'" . $key . "'";
}
$datayArr = join(",", $datayArr);
$where .= "AND opportunities.lead_source IN\t({$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";
//Now do the db queries
//query for opportunity data that matches $datay and $user
$result = $opp->db->query($query) or sugar_die("Error selecting sugarbean: " . mysql_error());
//build pipeline by sales stage data
$total = 0;
$div = 1;
global $sugar_config;
$symbol = $sugar_config['default_currency_symbol'];
$other = $current_module_strings['LBL_LEAD_SOURCE_OTHER'];
$rowTotalArr = array();
$rowTotalArr[] = 0;
global $current_user;
$salesStages = array("Closed Lost" => $app_list_strings['sales_stage_dom']["Closed Lost"], "Closed Won" => $app_list_strings['sales_stage_dom']["Closed Won"], "Other" => $other);
if ($current_user->getPreference('currency')) {
$currency = new Currency();
$currency->retrieve($current_user->getPreference('currency'));
$div = $currency->conversion_rate;
$symbol = $currency->symbol;
}
$fileContents = ' <yData defaultAltText="' . $current_module_strings['LBL_ROLLOVER_DETAILS'] . '">' . "\n";
$leadSourceArr = array();
while ($row = $opp->db->fetchByAssoc($result, -1, false)) {
if ($row['total'] * $div <= 100) {
$sum = round($row['total'] * $div, 2);
} else {
$sum = round($row['total'] * $div);
}
if ($row['lead_source'] == '') {
$row['lead_source'] = $current_module_strings['NTC_NO_LEGENDS'];
}
if ($row['sales_stage'] == 'Closed Won' || $row['sales_stage'] == 'Closed Lost') {
$salesStage = $row['sales_stage'];
$salesStageT = $app_list_strings['sales_stage_dom'][$row['sales_stage']];
} else {
$salesStage = "Other";
$salesStageT = $other;
}
if (!isset($leadSourceArr[$row['lead_source']]['row_total'])) {
$leadSourceArr[$row['lead_source']]['row_total'] = 0;
}
$leadSourceArr[$row['lead_source']][$salesStage]['opp_count'][] = $row['opp_count'];
$leadSourceArr[$row['lead_source']][$salesStage]['total'][] = $sum;
$leadSourceArr[$row['lead_source']]['outcome'][$salesStage] = $salesStageT;
$leadSourceArr[$row['lead_source']]['row_total'] += $sum;
$total += $sum;
}
foreach ($datay as $key => $translation) {
if ($key == '') {
$key = $current_module_strings['NTC_NO_LEGENDS'];
$translation = $current_module_strings['NTC_NO_LEGENDS'];
}
if (!isset($leadSourceArr[$key])) {
$leadSourceArr[$key] = $key;
}
if (isset($leadSourceArr[$key]['row_total'])) {
$rowTotalArr[] = $leadSourceArr[$key]['row_total'];
}
if (isset($leadSourceArr[$key]['row_total']) && $leadSourceArr[$key]['row_total'] > 100) {
$leadSourceArr[$key]['row_total'] = round($leadSourceArr[$key]['row_total']);
}
$fileContents .= ' <dataRow title="' . $translation . '" endLabel="' . currency_format_number($leadSourceArr[$key]['row_total'], array('currency_symbol' => true)) . '">' . "\n";
if (is_array($leadSourceArr[$key]['outcome'])) {
foreach ($leadSourceArr[$key]['outcome'] as $outcome => $outcome_translation) {
$fileContents .= ' <bar id="' . $outcome . '" totalSize="' . array_sum($leadSourceArr[$key][$outcome]['total']) . '" altText="' . format_number(array_sum($leadSourceArr[$key][$outcome]['opp_count']), 0, 0) . ' ' . $current_module_strings['LBL_OPPS_WORTH'] . ' ' . currency_format_number(array_sum($leadSourceArr[$key][$outcome]['total']), array('currency_symbol' => true)) . $current_module_strings['LBL_OPP_THOUSANDS'] . ' ' . $current_module_strings['LBL_OPPS_OUTCOME'] . ' ' . $outcome_translation . '" url="index.php?module=Opportunities&action=index&lead_source=' . $key . '&sales_stage=' . urlencode($outcome) . '&query=true&searchFormTab=advanced_search"/>' . "\n";
}
}
$fileContents .= ' </dataRow>' . "\n";
}
$fileContents .= ' </yData>' . "\n";
$max = get_max($rowTotalArr);
$fileContents .= ' <xData min="0" max="' . $max . '" length="10" kDelim="' . $kDelim . '" prefix="' . $symbol . '" suffix=""/>' . "\n";
$fileContents .= ' <colorLegend status="on">' . "\n";
$i = 0;
foreach ($salesStages as $outcome => $outcome_translation) {
$color = generate_graphcolor($outcome, $i);
$fileContents .= ' <mapping id="' . $outcome . '" name="' . $outcome_translation . '" color="' . $color . '"/>' . "\n";
$i++;
}
$fileContents .= ' </colorLegend>' . "\n";
$fileContents .= ' <graphInfo>' . "\n";
$fileContents .= ' <![CDATA[' . $current_module_strings['LBL_OPP_SIZE'] . ' ' . $symbol . '1' . $current_module_strings['LBL_OPP_THOUSANDS'] . ']]>' . "\n";
$fileContents .= ' </graphInfo>' . "\n";
$fileContents .= ' <chartColors ';
foreach ($barChartColors as $key => $value) {
$fileContents .= ' ' . $key . '=' . '"' . $value . '" ';
}
$fileContents .= ' />' . "\n";
$fileContents .= '</graphData>' . "\n";
$total = round($total, 2);
$title = '<graphData title="' . $current_module_strings['LBL_ALL_OPPORTUNITIES'] . currency_format_number($total, array('currency_symbol' => true)) . $app_strings['LBL_THOUSANDS_SYMBOL'] . '">' . "\n";
$fileContents = $title . $fileContents;
save_xml_file($cache_file_name, $fileContents);
}
$return = create_chart('hBarF', $cache_file_name);
return $return;
}
/**
* Creates opportunity pipeline image as a HORIZONTAL accumlated BAR GRAPH for multiple users.
* param $datax- the sales stage data to display in the x-axis
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc..
* All Rights Reserved..
* Contributor(s): ______________________________________..
*/
function gen_xml_pipeline_by_sales_stage($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;
$kDelim = $current_user->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\t({$dataxArr}) ";
}
//build the where clause for the query that matches $date_start and $date_end
$where .= "\tAND opportunities.date_closed >= " . db_convert("'" . $date_start . "'", 'date') . "\n\t\t\t\t\t\tAND opportunities.date_closed <= " . db_convert("'" . $date_end . "'", 'date');
$where .= "\tAND 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 = "\tSELECT opportunities.sales_stage,\n\t\t\t\t\t\t\tusers.user_name,\n\t\t\t\t\t\t\topportunities.assigned_user_id,\n\t\t\t\t\t\t\tcount( * ) AS opp_count,\n\t\t\t\t\t\t\tsum(amount_usdollar/1000) AS total\n\t\t\t\t\t\tFROM 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 = ' <yData defaultAltText="' . $current_module_strings['LBL_ROLLOVER_DETAILS'] . '">' . "\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 .= ' <dataRow title="' . $translation . '" endLabel="';
if (isset($stageArr[$key]['row_total'])) {
$fileContents .= $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 .= ' <bar id="' . $nameKey . '" totalSize="' . $stageArr[$key][$nameKey]['total'] . '" altText="' . $nameValue . ': ' . $stageArr[$key][$nameKey]['opp_count'] . ' ' . $current_module_strings['LBL_OPPS_WORTH'] . ' ' . currency_format_number($stageArr[$key][$nameKey]['total'], array('currency_symbol' => true)) . $current_module_strings['LBL_OPP_THOUSANDS'] . ' ' . $current_module_strings['LBL_OPPS_IN_STAGE'] . ' ' . $translation . '" url="index.php?module=Opportunities&action=index&assigned_user_id[]=' . $nameKey . '&sales_stage=' . urlencode($key) . '&date_start=' . $date_start . '&date_closed=' . $date_end . '&query=true&searchFormTab=advanced_search"/>' . "\n";
}
}
$fileContents .= ' </dataRow>' . "\n";
}
$fileContents .= ' </yData>' . "\n";
$max = get_max($rowTotalArr);
if ($chart_size == 'hBarF') {
$length = "10";
} else {
$length = "4";
}
$fileContents .= ' <xData min="0" max="' . $max . '" length="' . $length . '" kDelim="' . $kDelim . '" prefix="' . $symbol . '" suffix=""/>' . "\n";
$fileContents .= ' <colorLegend status="on">' . "\n";
$i = 0;
asort($new_ids);
foreach ($new_ids as $key => $value) {
$color = generate_graphcolor($key, $i);
$fileContents .= ' <mapping id="' . $key . '" name="' . $value . '" color="' . $color . '"/>' . "\n";
$i++;
}
$fileContents .= ' </colorLegend>' . "\n";
$fileContents .= ' <graphInfo>' . "\n";
$fileContents .= ' <![CDATA[' . $current_module_strings['LBL_DATE_RANGE'] . ' ' . $dateStartDisplay . ' ' . $current_module_strings['LBL_DATE_RANGE_TO'] . ' ' . $dateEndDisplay . '<BR/>' . $current_module_strings['LBL_OPP_SIZE'] . ' ' . $symbol . '1' . $current_module_strings['LBL_OPP_THOUSANDS'] . ']]>' . "\n";
$fileContents .= ' </graphInfo>' . "\n";
$fileContents .= ' <chartColors ';
foreach ($barChartColors as $key => $value) {
$fileContents .= ' ' . $key . '=' . '"' . $value . '" ';
}
$fileContents .= ' />' . "\n";
$fileContents .= '</graphData>' . "\n";
$total = $total;
$title = '<graphData title="' . $current_module_strings['LBL_TOTAL_PIPELINE'] . currency_format_number($total, array('currency_symbol' => true)) . $app_strings['LBL_THOUSANDS_SYMBOL'] . '">' . "\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 newaiCharts()
{
global $html_etc, $tablename, $common_html, $custom_type;
global $db, $return_sql_line, $columns;
global $_POST, $_GET, $returnmodel, $primarykey_index;
global $action_submit, $merge, $form_attribute;
global $tabletitle;
global $showlistfieldlist, $showlistfieldfilter, $showlistfieldtype;
$showlistfieldlistArray = explode(',', $showlistfieldlist);
$showlistfieldfilterArray = explode(',', $showlistfieldfilter);
$showlistfieldtypeArray = explode(',', $showlistfieldtype);
//获取系统显示的色彩信息
$ColorArray = returnColorArray();
//获取系统求和字段信息
global $sum_index, $UserUnitFunctionIndex;
if ($sum_index != "") {
$sum_sql_index = " ,sum({$sum_index}) as sum ";
} else {
$sum_sql_index = "";
}
//报表统计主体部分开始
for ($k = 0; $k < sizeof($showlistfieldlistArray); $k++) {
$fieldIndex = $showlistfieldlistArray[$k];
$fieldName = $columns[$fieldIndex];
$fieldText = $html_etc[$tablename][$fieldName];
$mode = $showlistfieldfilterArray[$k];
$modeArray = explode(':', $mode);
//print_R($modeArray);
$modeIndex = $modeArray[0];
$Mode = "";
switch ($modeIndex) {
case '':
break;
case 'tablefilter':
case 'tablefiltercolor':
case 'radiofilter':
case 'radiofiltercolor':
if ($modeArray[1] == "month" && $modeArray[2] == "") {
$Mode = "Month";
$SQL = "select Date_Format({$fieldName},'%c') AS {$fieldName},Sum({$sum_index}) as sum,Count({$fieldName}) as num from {$tablename} group by {$fieldName}";
} else {
if ($modeArray[1] == "year" && $modeArray[2] == "") {
$Mode = "Year";
$SQL = "select Date_Format({$fieldName},'%Y') AS {$fieldName},Sum({$sum_index}) as sum,Count({$fieldName}) as num from {$tablename} group by {$fieldName}";
} else {
$Mode = "";
$SQL = "select {$fieldName},Count({$fieldName}) as num {$sum_sql_index} from {$tablename} group by {$fieldName}";
}
}
$rs = $db->CacheExecute(150, $SQL);
$rs_array = $rs->GetArray();
//print_R($rs_array);
//父表结构部分
$tablenameIndex = $modeArray[1];
$ColumnsIndex = returntablecolumn($tablenameIndex);
$html_etcIndex = returnsystemlang($tablenameIndex, $tablenameIndex);
$WhatIndex = $ColumnsIndex[(string) $modeArray[2]];
$ReturnIndex = $ColumnsIndex[(string) $modeArray[3]];
//本表操作部分--数据处理部分 --形成FLASH要处理的数据类型
$TotalNumberIndex = 0;
$TotalSumIndex = 0;
$Array = array();
for ($i = 0; $i < sizeof($rs_array); $i++) {
$ResultNumber = $rs_array[$i]['num'];
$ResultSum = $rs_array[$i]['sum'];
$ResultFieldCode = $rs_array[$i][$fieldName];
switch ($Mode) {
case 'Month':
$ResultFieldName = $ResultFieldCode . "" . $common_html['common_html']['month'];
break;
case 'Year':
$ResultFieldName = $ResultFieldCode . "" . $common_html['common_html']['year'];
break;
default:
$ResultFieldName = returntablefield($tablenameIndex, $WhatIndex, $ResultFieldCode, $ReturnIndex);
if ($ResultFieldName == "") {
$ResultFieldName = $ResultFieldCode;
}
break;
}
$TotalNumberIndex += $ResultNumber;
$TotalSumIndex += $ResultSum;
$Array_Statistic_Value = $sum_index != "" ? $ResultSum : $ResultNumber;
$Array['XData'][$i]['Name'] = $ResultFieldName;
$Array['XData'][$i]['Value'] = $Array_Statistic_Value;
$Array['XData'][$i]['Dir'] = $ResultFieldName;
$Array['XData'][$i]['AltText'] = $ResultFieldName;
//$Array['XData'][$i]['Url'] = "?action=init_customer&$fieldName=$ResultFieldCode";
$ColorArray[$i] == "" ? $ColorArray[$i] = "0xCC0000" : '';
$Array['XData'][$i]['Color'] = $ColorArray[$i];
$Array['Dir'][$i]['Name'] = $ResultFieldName;
$Array['Dir'][$i]['Color'] = $ColorArray[$i];
$Array['YData']['AltText'] = "移动查看详细信息";
$Array['YData']['Value'] < $Array_Statistic_Value ? $Array['YData']['Value'] = $Array_Statistic_Value : '';
}
$Array['title'] = $html_etc[$tablename][$tabletitle] . "[" . $html_etc[$tablename][$fieldName] . "]";
$array_graphInfo = $Array['title'] . "[" . date("Y-m-d H:i:s") . "] " . $common_html['common_html']['totalrecords'] . ": " . $TotalNumberIndex;
if ($sum_index != "") {
$array_graphInfo .= " " . $common_html['common_html']['allnumbers'] . ": " . $TotalSumIndex . " " . $UserUnitFunctionIndex . "\n";
}
$Array['graphInfo'] = $array_graphInfo;
//print_R($FlashFileName);
//本表操作部分--FALSH图表显示部分--显示FLASH图表的结果
table_begin("650");
$ChartMode = $showlistfieldtypeArray[$k];
//采用的图表类型选择
//print $tablename;
switch ($ChartMode) {
case 'vBarF':
$FlashFileName = WriteXmlFilevBarF($Array, $tablename, $k);
//Flash XML 数据写入文件区
$create_chart = create_chart("vBarF", $FlashFileName);
//FLASH 图表读入XML数据处理以后的显示区
break;
case 'hBarF':
$FlashFileName = WriteXmlFilehBarF($Array, $tablename, $k);
//Flash XML 数据写入文件区
$create_chart = create_chart("hBarF", $FlashFileName);
//FLASH 图表读入XML数据处理以后的显示区
break;
case 'pieF':
$FlashFileName = WriteXmlFilePieF($Array, $tablename, $k);
//Flash XML 数据写入文件区
$create_chart = create_chart("pieF", $FlashFileName);
//FLASH 图表读入XML数据处理以后的显示区
break;
default:
$FlashFileName = WriteXmlFilevBarF($Array, $tablename, $k);
//Flash XML 数据写入文件区
$create_chart = create_chart("vBarF", $FlashFileName);
//FLASH 图表读入XML数据处理以后的显示区
break;
}
//HTML文体显示部分
print "<TR class=TableData>";
print "<TD noWrap width=100% align=center colspan=40>";
print "{$create_chart} </TD>";
print "</TR>";
//本文信息显示部分
//print_title($html_etc[$tablename][$tabletitle]."<font color=green>[".$html_etc[$tablename][$fieldName]."]</font>",40);
print_title($html_etc[$tablename][$tabletitle] . "[" . $html_etc[$tablename][$fieldName] . "]", 6);
for ($i = 0; $i < sizeof($rs_array); $i++) {
$ResultNumber = $rs_array[$i][num];
$ResultSum = $rs_array[$i][sum];
$ResultFieldCode = $rs_array[$i][$fieldName];
$ResultFieldName = returntablefield($tablenameIndex, $WhatIndex, $ResultFieldCode, $ReturnIndex);
if ($ResultFieldName == "") {
$ResultFieldName = $ResultFieldCode;
}
print "<TR class=TableData>";
print "<TD noWrap width=15%>统计类别名称 </TD>";
print "<TD width=35%>" . $ResultFieldName . " </TD>";
if ($sum_index != "") {
print "<TD noWrap width=15%>统计额度 </TD>";
print "<TD noWrap width=10%><font color=red>" . $ResultSum . " " . $UserUnitFunctionIndex . "</font></TD>";
} else {
//print "<TD noWrap width=15%>统计类别代码 </TD>";
//print "<TD noWrap width=10%>".$ResultFieldCode." </TD>";
}
print "<TD width=15%>统计记录数 </TD>";
print "<TD noWrap width=10%><font color=red>" . $ResultNumber . " 条</font></TD>";
print "</TR>";
}
print "<TR class=TableData>";
print "<TD noWrap colspan = 40>\n";
print $common_html['common_html']['totalrecords'] . ": <font color=red>{$TotalNumberIndex} 条</font>\n";
if ($sum_index != "") {
print $common_html['common_html']['allnumbers'] . ": <font color=red>{$TotalSumIndex} " . $UserUnitFunctionIndex . "</font>\n";
}
print "</TD>";
print "</TR>";
table_end();
print "<BR>";
break;
}
}
}
/**
* Creates opportunity pipeline image as a VERTICAL accumlated bar graph for multiple users.
* param $datax- the month data to display in the x-axis
* Portions created by SugarCRM are Copyright (C) SugarCRM, Inc..
* All Rights Reserved..
* Contributor(s): ______________________________________..
*/
function campaign_response_by_activity_type($datay = array(), $targets = array(), $campaign_id, $cache_file_name = 'a_file', $refresh = false, $marketing_id = '')
{
global $app_strings, $mod_strings, $charset, $lang, $barChartColors, $app_list_strings;
if (!file_exists($cache_file_name) || $refresh == true) {
$GLOBALS['log']->debug("datay is:");
$GLOBALS['log']->debug($datay);
$GLOBALS['log']->debug("user_id is: ");
$GLOBALS['log']->debug("cache_file_name is: {$cache_file_name}");
$focus = new Campaign();
$query = "SELECT activity_type,target_type, count(*) hits ";
$query .= " FROM campaign_log ";
$query .= " WHERE campaign_id = '{$campaign_id}' AND archived=0 AND deleted=0";
//if $marketing id is specified, then lets filter the chart by the value
if (!empty($marketing_id)) {
$query .= " AND marketing_id ='{$marketing_id}'";
}
$query .= " GROUP BY activity_type, target_type";
$query .= " ORDER BY activity_type, target_type";
$result = $focus->db->query($query);
$leadSourceArr = array();
$total = 0;
$total_targeted = 0;
$rowTotalArr = array();
$rowTotalArr[] = 0;
while ($row = $focus->db->fetchByAssoc($result, -1, false)) {
if (!isset($leadSourceArr[$row['activity_type']]['row_total'])) {
$leadSourceArr[$row['activity_type']]['row_total'] = 0;
}
$leadSourceArr[$row['activity_type']][$row['target_type']]['hits'][] = $row['hits'];
$leadSourceArr[$row['activity_type']][$row['target_type']]['total'][] = $row['hits'];
$leadSourceArr[$row['activity_type']]['outcome'][$row['target_type']] = $row['target_type'];
$leadSourceArr[$row['activity_type']]['row_total'] += $row['hits'];
if (!isset($leadSourceArr['all_activities'][$row['target_type']])) {
$leadSourceArr['all_activities'][$row['target_type']] = array('total' => 0);
}
$leadSourceArr['all_activities'][$row['target_type']]['total'] += $row['hits'];
$total += $row['hits'];
if ($row['activity_type'] == 'targeted') {
$targeted[$row['target_type']] = $row['hits'];
$total_targeted += $row['hits'];
}
}
$fileContents = ' <yData defaultAltText="' . $mod_strings['LBL_ROLLOVER_VIEW'] . '">' . "\n";
foreach ($datay as $key => $translation) {
if ($key == '') {
//$key = $mod_strings['NTC_NO_LEGENDS'];
$key = 'None';
$translation = $mod_strings['NTC_NO_LEGENDS'];
}
if (!isset($leadSourceArr[$key])) {
$leadSourceArr[$key] = $key;
}
if (is_array($leadSourceArr[$key]) && isset($leadSourceArr[$key]['row_total'])) {
$rowTotalArr[] = $leadSourceArr[$key]['row_total'];
}
if (is_array($leadSourceArr[$key]) && isset($leadSourceArr[$key]['row_total']) && $leadSourceArr[$key]['row_total'] > 100) {
$leadSourceArr[$key]['row_total'] = round($leadSourceArr[$key]['row_total']);
}
$fileContents .= ' <dataRow title="' . $translation . '" endLabel="' . $leadSourceArr[$key]['row_total'] . '">' . "\n";
if (is_array($leadSourceArr[$key]['outcome'])) {
foreach ($leadSourceArr[$key]['outcome'] as $outcome => $outcome_translation) {
//create alternate text.
$alttext = ' ';
if (isset($targeted) && isset($targeted[$outcome]) && !empty($targeted[$outcome])) {
$alttext = $targets[$outcome] . ': ' . $mod_strings['LBL_TARGETED'] . ' ' . $targeted[$outcome] . ', ' . $mod_strings['LBL_TOTAL_TARGETED'] . ' ' . $total_targeted . ".";
}
if ($key != 'targeted') {
$alttext .= " {$translation} " . array_sum($leadSourceArr[$key][$outcome]['hits']);
}
$fileContents .= ' <bar id="' . $outcome . '" totalSize="' . array_sum($leadSourceArr[$key][$outcome]['total']) . '" altText="' . $alttext . '" url="#' . $key . '"/>' . "\n";
}
}
$fileContents .= ' </dataRow>' . "\n";
}
$fileContents .= ' </yData>' . "\n";
$max = get_max($rowTotalArr);
$fileContents .= ' <xData min="0" max="' . $max . '" length="10" prefix="' . '' . '" suffix=""/>' . "\n";
$fileContents .= ' <colorLegend status="on">' . "\n";
$i = 0;
foreach ($targets as $outcome => $outcome_translation) {
$color = generate_graphcolor($outcome, $i);
$fileContents .= ' <mapping id="' . $outcome . '" name="' . $outcome_translation . '" color="' . $color . '"/>' . "\n";
$i++;
}
$fileContents .= ' </colorLegend>' . "\n";
$fileContents .= ' <graphInfo>' . "\n";
$fileContents .= ' <![CDATA[' . ' ' . ']]>' . "\n";
$fileContents .= ' </graphInfo>' . "\n";
$fileContents .= ' <chartColors ';
foreach ($barChartColors as $key => $value) {
$fileContents .= ' ' . $key . '=' . '"' . $value . '" ';
}
$fileContents .= ' />' . "\n";
$fileContents .= '</graphData>' . "\n";
$total = round($total, 2);
$title = '<graphData title="' . $mod_strings['LBL_CAMPAIGN_RESPONSE_BY_RECIPIENT_ACTIVITY'] . '">' . "\n";
$fileContents = $title . $fileContents;
save_xml_file($cache_file_name, $fileContents);
}
$return = create_chart('hBarF', $cache_file_name);
return $return;
}
