/**
  * 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 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;
}
 /**
  * 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;
 }
Exemplo n.º 5
0
 /**
  * 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;
 }