Example #1
0
/**	Function used to get all the price details for different currencies which are associated to the given product
 *	@param int $productid - product id to which we want to get all the associated prices
 *  @param decimal $unit_price - Unit price of the product
 *  @param string $available - available or available_associated where as default is available, if available then the prices in the currencies which are available now will be returned, otherwise if the value is available_associated then prices of all the associated currencies will be retruned
 *	@return array $price_details - price details as a array with productid, curid, curname
 */
function getPriceDetailsForProduct($productid, $unit_price, $available = 'available', $itemtype = 'Products')
{
    global $log, $adb;
    $log->debug("Entering into function getPriceDetailsForProduct({$productid})");
    if ($productid != '') {
        $product_currency_id = getProductBaseCurrency($productid, $itemtype);
        $product_base_conv_rate = getBaseConversionRateForProduct($productid, 'edit', $itemtype);
        // Detail View
        if ($available == 'available_associated') {
            $query = "select vtiger_currency_info.*, vtiger_productcurrencyrel.converted_price, vtiger_productcurrencyrel.actual_price\n\t\t\t\t\tfrom vtiger_currency_info\n\t\t\t\t\tinner join vtiger_productcurrencyrel on vtiger_currency_info.id = vtiger_productcurrencyrel.currencyid\n\t\t\t\t\twhere vtiger_currency_info.currency_status = 'Active' and vtiger_currency_info.deleted=0\n\t\t\t\t\tand vtiger_productcurrencyrel.productid = ? and vtiger_currency_info.id != ?";
            $params = array($productid, $product_currency_id);
        } else {
            // Edit View
            $query = "select vtiger_currency_info.*, vtiger_productcurrencyrel.converted_price, vtiger_productcurrencyrel.actual_price\n\t\t\t\t\tfrom vtiger_currency_info\n\t\t\t\t\tleft join vtiger_productcurrencyrel\n\t\t\t\t\ton vtiger_currency_info.id = vtiger_productcurrencyrel.currencyid and vtiger_productcurrencyrel.productid = ?\n\t\t\t\t\twhere vtiger_currency_info.currency_status = 'Active' and vtiger_currency_info.deleted=0";
            $params = array($productid);
        }
        //Postgres 8 fixes
        if ($adb->dbType == "pgsql") {
            $query = fixPostgresQuery($query, $log, 0);
        }
        $res = $adb->pquery($query, $params);
        for ($i = 0; $i < $adb->num_rows($res); $i++) {
            $price_details[$i]['productid'] = $productid;
            $price_details[$i]['currencylabel'] = $adb->query_result($res, $i, 'currency_name');
            $price_details[$i]['currencycode'] = $adb->query_result($res, $i, 'currency_code');
            $price_details[$i]['currencysymbol'] = $adb->query_result($res, $i, 'currency_symbol');
            $currency_id = $adb->query_result($res, $i, 'id');
            $price_details[$i]['curid'] = $currency_id;
            $price_details[$i]['curname'] = 'curname' . $adb->query_result($res, $i, 'id');
            $cur_value = $adb->query_result($res, $i, 'actual_price');
            // Get the conversion rate for the given currency, get the conversion rate of the product currency to base currency.
            // Both together will be the actual conversion rate for the given currency.
            $conversion_rate = $adb->query_result($res, $i, 'conversion_rate');
            $actual_conversion_rate = $product_base_conv_rate * $conversion_rate;
            if ($cur_value == null || $cur_value == '') {
                $price_details[$i]['check_value'] = false;
                if ($unit_price != null) {
                    $cur_value = convertFromMasterCurrency($unit_price, $actual_conversion_rate);
                } else {
                    $cur_value = '0';
                }
            } else {
                $price_details[$i]['check_value'] = true;
            }
            $price_details[$i]['curvalue'] = CurrencyField::convertToUserFormat($cur_value, null, true);
            $price_details[$i]['conversionrate'] = $actual_conversion_rate;
            $is_basecurrency = false;
            if ($currency_id == $product_currency_id) {
                $is_basecurrency = true;
            }
            $price_details[$i]['is_basecurrency'] = $is_basecurrency;
        }
    } else {
        if ($available == 'available') {
            // Create View
            global $current_user;
            $user_currency_id = fetchCurrency($current_user->id);
            $query = "select vtiger_currency_info.* from vtiger_currency_info\n\t\t\t\t\twhere vtiger_currency_info.currency_status = 'Active' and vtiger_currency_info.deleted=0";
            $params = array();
            $res = $adb->pquery($query, $params);
            for ($i = 0; $i < $adb->num_rows($res); $i++) {
                $price_details[$i]['currencylabel'] = $adb->query_result($res, $i, 'currency_name');
                $price_details[$i]['currencycode'] = $adb->query_result($res, $i, 'currency_code');
                $price_details[$i]['currencysymbol'] = $adb->query_result($res, $i, 'currency_symbol');
                $currency_id = $adb->query_result($res, $i, 'id');
                $price_details[$i]['curid'] = $currency_id;
                $price_details[$i]['curname'] = 'curname' . $adb->query_result($res, $i, 'id');
                // Get the conversion rate for the given currency, get the conversion rate of the product currency(logged in user's currency) to base currency.
                // Both together will be the actual conversion rate for the given currency.
                $conversion_rate = $adb->query_result($res, $i, 'conversion_rate');
                $user_cursym_convrate = getCurrencySymbolandCRate($user_currency_id);
                $product_base_conv_rate = 1 / $user_cursym_convrate['rate'];
                $actual_conversion_rate = $product_base_conv_rate * $conversion_rate;
                $price_details[$i]['check_value'] = false;
                $price_details[$i]['curvalue'] = '0';
                $price_details[$i]['conversionrate'] = $actual_conversion_rate;
                $is_basecurrency = false;
                if ($currency_id == $user_currency_id) {
                    $is_basecurrency = true;
                }
                $price_details[$i]['is_basecurrency'] = $is_basecurrency;
            }
        } else {
            $log->debug("Product id is empty. we cannot retrieve the associated prices.");
        }
    }
    $log->debug("Exit from function getPriceDetailsForProduct({$productid})");
    return $price_details;
}
Example #2
0
 function getTotalsHTML($to_totals_res)
 {
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getTotalsHTML");
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getTotalsHTML");
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getTotalsHTML");
     global $log;
     $log->debug("Entering ./modules/ITS4YouReports/GenerateObj.php::getTotalsHTML");
     global $mod_strings;
     if (!isset($mod_strings) || empty($mod_strings)) {
         global $currentModule;
         global $current_language;
         if (empty($current_language)) {
             $current_language = 'en_us';
         }
         $mod_strings = return_module_language($current_language, $this->getCurrentModule4You());
     }
     if ($this->outputformat == "XLS") {
         $coltotalhtml = array();
         $coltotal_ri = 0;
         $coltotalhtml[$coltotal_ri][] = $mod_strings["Totals"];
         $coltotalhtml[$coltotal_ri][] = $mod_strings["SUM"];
         $coltotalhtml[$coltotal_ri][] = $mod_strings["AVG"];
         $coltotalhtml[$coltotal_ri][] = $mod_strings["MIN"];
         $coltotalhtml[$coltotal_ri][] = $mod_strings["MAX"];
         $coltotal_ri++;
     } else {
         $header_style = $this->header_style;
         $coltotalhtml .= "<table align='center' cellpadding='3' cellspacing='0'  border='1' style='border-collapse: collapse' class='rpt4youTable' style='min-width:30%;'>";
         $coltotalhtml .= "<tr>";
         $coltotalhtml .= "<td class='rpt4youCellLabel' style='min-width:28%;{$header_style}' nowrap >" . $mod_strings["Totals"] . "</td>";
         $coltotalhtml .= "<td class='rpt4youCellLabel' style='min-width:18%;{$header_style}' nowrap >" . $mod_strings["SUM"] . "</td>";
         $coltotalhtml .= "<td class='rpt4youCellLabel' style='min-width:18%;{$header_style}' nowrap >" . $mod_strings["AVG"] . "</td>";
         $coltotalhtml .= "<td class='rpt4youCellLabel' style='min-width:18%;{$header_style}' nowrap >" . $mod_strings["MIN"] . "</td>";
         $coltotalhtml .= "<td class='rpt4youCellLabel' style='min-width:18%;{$header_style}' nowrap >" . $mod_strings["MAX"] . "</td>";
         $coltotalhtml .= "</tr>";
     }
     if (!empty($to_totals_res)) {
         $k_i = 0;
         foreach ($to_totals_res as $key => $totals_array) {
             if ($this->outputformat != "XLS") {
                 $coltotalhtml .= '<tr valign="middle">';
             }
             if (isset($totals_array["label"])) {
                 $col_header = $totals_array["label"];
             } else {
                 $col_header = $key;
             }
             /* if ($uitype_arr[$value]==71 || in_array($fld_name_1, $this->convert_currency) || in_array($fld_name_1, $this->append_currency_symbol_to_value)
                || in_array($fld_name_2, $this->convert_currency) || in_array($fld_name_2, $this->append_currency_symbol_to_value)) {
                $col_header .= " (".$app_strings['LBL_IN']." ".$current_user->currency_symbol.")";
                $convert_price = true;
                } else {
                $convert_price = false;
                } */
             if ($this->outputformat == "XLS") {
                 $coltotalhtml[$coltotal_ri][] = $col_header;
             } else {
                 $coltotalhtml .= '<td class="rpt4youGrpHead" nowrap >' . $col_header . '</td>';
             }
             /*
               if ($k_i==0) {
               $td_class = "rpt4youCellLabel";
               } else {
               $td_class = "rpt4youGrpHead";
               }
               $coltotalhtml .= '<td class="rpt4youGrpHead" style="background-color:#737373;color:#F6F6F6;font-weight:bold;font-size:11px;" align="center" nowrap >'.$k_i." " . $col_header . '</td>';
             */
             $value = trim($key);
             $to_display = array();
             $arraykey = 'SUM';
             if (in_array($this->report_obj->primarymodule, ITS4YouReports::$inventory_modules) && isset($totals_array[$arraykey])) {
                 ksort($totals_array[$arraykey]);
                 foreach ($totals_array[$arraykey] as $currency_id => $totals_value) {
                     if ($convert_price) {
                         $conv_value = convertFromMasterCurrency($totals_value, $current_user->conv_rate);
                     } else {
                         $conv_value = $totals_value;
                     }
                     $conv_value = number_format($conv_value, 2, ".", "");
                     $to_display[] = $this->getFldNumberFormat($key, $conv_value, $currency_id);
                 }
                 // dokoncit VIAC currencies !!! v totals
                 $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . implode("<br>", $to_display) . '</td>';
             } elseif (isset($totals_array[$arraykey])) {
                 if ($convert_price) {
                     $conv_value = convertFromMasterCurrency($totals_array[$arraykey], $current_user->conv_rate);
                 } else {
                     $conv_value = $totals_array[$arraykey];
                 }
                 $conv_value = number_format($conv_value, 2, ".", "");
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = $this->getFldNumberFormat($key, $conv_value);
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . $this->getFldNumberFormat($key, $conv_value) . '</td>';
                 }
             } else {
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = " ";
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal"> </td>';
                 }
             }
             $to_display = array();
             $arraykey = 'AVG';
             $count_key = "COUNT";
             if (in_array($this->report_obj->primarymodule, ITS4YouReports::$inventory_modules) && isset($totals_array[$arraykey]) && isset($totals_array[$count_key])) {
                 ksort($totals_array[$arraykey]);
                 foreach ($totals_array[$arraykey] as $currency_id => $totals_value) {
                     $conv_value = $totals_value / count($totals_array[$count_key][$currency_id]);
                     if ($convert_price) {
                         $conv_value = convertFromMasterCurrency($conv_value, $current_user->conv_rate);
                     }
                     $conv_value = number_format($conv_value, 2, ".", "");
                     $to_display[] = $this->getFldNumberFormat($key, $conv_value, $currency_id);
                 }
                 // dokoncit VIAC currencies !!! v totals
                 $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . implode("<br>", $to_display) . '</td>';
             } elseif (isset($totals_array[$arraykey]) && isset($totals_array[$count_key])) {
                 $conv_value = $totals_array[$arraykey] / $totals_array[$count_key];
                 if ($convert_price) {
                     $conv_value = convertFromMasterCurrency($conv_value, $current_user->conv_rate);
                 }
                 $conv_value = number_format($conv_value, 2, ".", "");
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = $this->getFldNumberFormat($key, $conv_value);
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . $this->getFldNumberFormat($key, $conv_value) . '</td>';
                 }
             } else {
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = " ";
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal"> </td>';
                 }
             }
             $to_display = array();
             $arraykey = 'MIN';
             if (in_array($this->report_obj->primarymodule, ITS4YouReports::$inventory_modules) && isset($totals_array[$arraykey])) {
                 ksort($totals_array[$arraykey]);
                 foreach ($totals_array[$arraykey] as $currency_id => $totals_value) {
                     if ($convert_price) {
                         $conv_value = convertFromMasterCurrency($totals_value, $current_user->conv_rate);
                     } else {
                         $conv_value = $totals_value;
                     }
                     $conv_value = number_format($conv_value, 2, ".", "");
                     $to_display[] = $this->getFldNumberFormat($key, $conv_value, $currency_id);
                 }
                 // dokoncit VIAC currencies !!! v totals
                 $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . implode("<br>", $to_display) . '</td>';
             } elseif (isset($totals_array[$arraykey])) {
                 if ($convert_price) {
                     $conv_value = convertFromMasterCurrency($totals_array[$arraykey], $current_user->conv_rate);
                 } else {
                     $conv_value = $totals_array[$arraykey];
                 }
                 $conv_value = number_format($conv_value, 2, ".", "");
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = $this->getFldNumberFormat($key, $conv_value);
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . $this->getFldNumberFormat($key, $conv_value) . '</td>';
                 }
             } else {
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = " ";
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal"> </td>';
                 }
             }
             $to_display = array();
             $arraykey = 'MAX';
             if (in_array($this->report_obj->primarymodule, ITS4YouReports::$inventory_modules) && isset($totals_array[$arraykey])) {
                 ksort($totals_array[$arraykey]);
                 foreach ($totals_array[$arraykey] as $currency_id => $totals_value) {
                     if ($convert_price) {
                         $conv_value = convertFromMasterCurrency($totals_value, $current_user->conv_rate);
                     } else {
                         $conv_value = $totals_value;
                     }
                     $conv_value = number_format($conv_value, 2, ".", "");
                     $to_display[] = $this->getFldNumberFormat($key, $conv_value, $currency_id);
                 }
                 // dokoncit VIAC currencies !!! v totals
                 $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . implode("<br>", $to_display) . '</td>';
             } elseif (isset($totals_array[$arraykey])) {
                 if ($convert_price) {
                     $conv_value = convertFromMasterCurrency($totals_array[$arraykey], $current_user->conv_rate);
                 } else {
                     $conv_value = $totals_array[$arraykey];
                 }
                 $conv_value = number_format($conv_value, 2, ".", "");
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = $this->getFldNumberFormat($key, $conv_value);
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal" nowrap >' . $this->getFldNumberFormat($key, $conv_value) . '</td>';
                 }
             } else {
                 if ($this->outputformat == "XLS") {
                     $coltotalhtml[$coltotal_ri][] = " ";
                 } else {
                     $coltotalhtml .= '<td class="rpt4youTotal"> </td>';
                 }
             }
             if ($this->outputformat == "XLS") {
                 $coltotal_ri++;
             } else {
                 $coltotalhtml .= '</tr>';
             }
             // Performation Optimization: If Direct output is desired
             if ($directOutput) {
                 echo $coltotalhtml;
                 $coltotalhtml = '';
             }
             // END
             $k_i++;
         }
     }
     if ($this->outputformat == "XLS") {
         $sHTML = $coltotalhtml;
     } else {
         $coltotalhtml .= "</table>";
         $sHTML .= $coltotalhtml;
     }
     return $sHTML;
 }
    function GenerateReport($outputformat, $filterlist, $directOutput = false)
    {
        global $adb, $current_user, $php_max_execution_time;
        global $modules, $app_strings;
        global $mod_strings, $current_language;
        require 'user_privileges/user_privileges_' . $current_user->id . '.php';
        $modules_selected = array();
        $modules_selected[] = $this->primarymodule;
        if (!empty($this->secondarymodule)) {
            $sec_modules = split(":", $this->secondarymodule);
            for ($i = 0; $i < count($sec_modules); $i++) {
                $modules_selected[] = $sec_modules[$i];
            }
        }
        // Update Currency Field list
        $currencyfieldres = $adb->pquery("SELECT tabid, fieldlabel, uitype from vtiger_field WHERE uitype in (71,72,10)", array());
        if ($currencyfieldres) {
            foreach ($currencyfieldres as $currencyfieldrow) {
                $modprefixedlabel = getTabModuleName($currencyfieldrow['tabid']) . ' ' . $currencyfieldrow['fieldlabel'];
                $modprefixedlabel = str_replace(' ', '_', $modprefixedlabel);
                if ($currencyfieldrow['uitype'] != 10) {
                    if (!in_array($modprefixedlabel, $this->convert_currency) && !in_array($modprefixedlabel, $this->append_currency_symbol_to_value)) {
                        $this->convert_currency[] = $modprefixedlabel;
                    }
                } else {
                    if (!in_array($modprefixedlabel, $this->ui10_fields)) {
                        $this->ui10_fields[] = $modprefixedlabel;
                    }
                }
            }
        }
        if ($outputformat == "HTML") {
            $sSQL = $this->sGetSQLforReport($this->reportid, $filterlist);
            $result = $adb->query($sSQL);
            $error_msg = $adb->database->ErrorMsg();
            if (!$result && $error_msg != '') {
                // Performance Optimization: If direct output is requried
                if ($directOutput) {
                    echo getTranslatedString('LBL_REPORT_GENERATION_FAILED', $currentModule) . "<br>" . $error_msg;
                    $error_msg = false;
                }
                // END
                return $error_msg;
            }
            // Performance Optimization: If direct output is required
            if ($directOutput) {
                echo '<table cellpadding="5" cellspacing="0" align="center" class="rptTable"><tr>';
            }
            // END
            if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) {
                $picklistarray = $this->getAccessPickListValues();
            }
            if ($result) {
                $y = $adb->num_fields($result);
                $arrayHeaders = array();
                for ($x = 0; $x < $y; $x++) {
                    $fld = $adb->field_name($result, $x);
                    if (in_array($this->getLstringforReportHeaders($fld->name), $arrayHeaders)) {
                        $headerLabel = str_replace("_", " ", $fld->name);
                        $arrayHeaders[] = $headerLabel;
                    } else {
                        $headerLabel = str_replace($modules, " ", $this->getLstringforReportHeaders($fld->name));
                        $headerLabel = str_replace("_", " ", $this->getLstringforReportHeaders($fld->name));
                        $arrayHeaders[] = $headerLabel;
                    }
                    /*STRING TRANSLATION starts */
                    $mod_name = split(' ', $headerLabel, 2);
                    $module = '';
                    if (in_array($mod_name[0], $modules_selected)) {
                        $module = getTranslatedString($mod_name[0], $mod_name[0]);
                    }
                    if (!empty($this->secondarymodule)) {
                        if ($module != '') {
                            $headerLabel_tmp = $module . " " . getTranslatedString($mod_name[1], $mod_name[0]);
                        } else {
                            $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
                        }
                    } else {
                        if ($module != '') {
                            $headerLabel_tmp = getTranslatedString($mod_name[1], $mod_name[0]);
                        } else {
                            $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
                        }
                    }
                    if ($headerLabel == $headerLabel_tmp) {
                        $headerLabel = getTranslatedString($headerLabel_tmp);
                    } else {
                        $headerLabel = $headerLabel_tmp;
                    }
                    /*STRING TRANSLATION ends */
                    $header .= "<td class='rptCellLabel'>" . $headerLabel . "</td>";
                    // Performance Optimization: If direct output is required
                    if ($directOutput) {
                        echo $header;
                        $header = '';
                    }
                    // END
                }
                // Performance Optimization: If direct output is required
                if ($directOutput) {
                    echo '</tr><tr>';
                }
                // END
                $noofrows = $adb->num_rows($result);
                $custom_field_values = $adb->fetch_array($result);
                $groupslist = $this->getGroupingList($this->reportid);
                $column_definitions = $adb->getFieldsDefinition($result);
                do {
                    $arraylists = array();
                    if (count($groupslist) == 1) {
                        $newvalue = $custom_field_values[0];
                    } elseif (count($groupslist) == 2) {
                        $newvalue = $custom_field_values[0];
                        $snewvalue = $custom_field_values[1];
                    } elseif (count($groupslist) == 3) {
                        $newvalue = $custom_field_values[0];
                        $snewvalue = $custom_field_values[1];
                        $tnewvalue = $custom_field_values[2];
                    }
                    if ($newvalue == "") {
                        $newvalue = "-";
                    }
                    if ($snewvalue == "") {
                        $snewvalue = "-";
                    }
                    if ($tnewvalue == "") {
                        $tnewvalue = "-";
                    }
                    $valtemplate .= "<tr>";
                    // Performance Optimization
                    if ($directOutput) {
                        echo $valtemplate;
                        $valtemplate = '';
                    }
                    // END
                    for ($i = 0; $i < $y; $i++) {
                        $fld = $adb->field_name($result, $i);
                        $fld_type = $column_definitions[$i]->type;
                        if (in_array($fld->name, $this->convert_currency)) {
                            if ($custom_field_values[$i] != '') {
                                $fieldvalue = convertFromMasterCurrency($custom_field_values[$i], $current_user->conv_rate);
                            } else {
                                $fieldvalue = getTranslatedString($custom_field_values[$i]);
                            }
                        } elseif (in_array($fld->name, $this->append_currency_symbol_to_value)) {
                            $curid_value = explode("::", $custom_field_values[$i]);
                            $currency_id = $curid_value[0];
                            $currency_value = $curid_value[1];
                            $cur_sym_rate = getCurrencySymbolandCRate($currency_id);
                            if ($custom_field_values[$i] != '') {
                                $fieldvalue = $cur_sym_rate['symbol'] . " " . $currency_value;
                            } else {
                                $fieldvalue = getTranslatedString($custom_field_values[$i]);
                            }
                        } elseif ($fld->name == "PurchaseOrder_Currency" || $fld->name == "SalesOrder_Currency" || $fld->name == "Invoice_Currency" || $fld->name == "Quotes_Currency") {
                            if ($custom_field_values[$i] != '') {
                                $fieldvalue = getCurrencyName($custom_field_values[$i]);
                            } else {
                                $fieldvalue = getTranslatedString($custom_field_values[$i]);
                            }
                        } elseif (in_array($fld->name, $this->ui10_fields) && !empty($custom_field_values[$i])) {
                            $type = getSalesEntityType($custom_field_values[$i]);
                            $tmp = getEntityName($type, $custom_field_values[$i]);
                            foreach ($tmp as $key => $val) {
                                $fieldvalue = $val;
                                break;
                            }
                        } else {
                            if ($custom_field_values[$i] != '') {
                                $fieldvalue = getTranslatedString($custom_field_values[$i]);
                            } else {
                                $fieldvalue = getTranslatedString($custom_field_values[$i]);
                            }
                        }
                        $fieldvalue = str_replace("<", "&lt;", $fieldvalue);
                        $fieldvalue = str_replace(">", "&gt;", $fieldvalue);
                        //check for Roll based pick list
                        $temp_val = $fld->name;
                        if (is_array($picklistarray)) {
                            if (array_key_exists($temp_val, $picklistarray)) {
                                if (!in_array($custom_field_values[$i], $picklistarray[$fld->name]) && $custom_field_values[$i] != '') {
                                    $fieldvalue = $app_strings['LBL_NOT_ACCESSIBLE'];
                                }
                            }
                        }
                        if (is_array($picklistarray[1])) {
                            if (array_key_exists($temp_val, $picklistarray[1])) {
                                $temp = explode(",", str_ireplace(' |##| ', ',', $fieldvalue));
                                $temp_val = array();
                                foreach ($temp as $key => $val) {
                                    if (!in_array(trim($val), $picklistarray[1][$fld->name]) && trim($val) != '') {
                                        $temp_val[] = $app_strings['LBL_NOT_ACCESSIBLE'];
                                    } else {
                                        $temp_val[] = $val;
                                    }
                                }
                                $fieldvalue = is_array($temp_val) ? implode(", ", $temp_val) : '';
                            }
                        }
                        if ($fieldvalue == "") {
                            $fieldvalue = "-";
                        } else {
                            if (stristr($fieldvalue, "|##|")) {
                                $fieldvalue = str_ireplace(' |##| ', ', ', $fieldvalue);
                            } else {
                                if ($fld_type == "date" || $fld_type == "datetime") {
                                    $fieldvalue = getDisplayDate($fieldvalue);
                                }
                            }
                        }
                        if ($lastvalue == $fieldvalue && $this->reporttype == "summary") {
                            if ($this->reporttype == "summary") {
                                $valtemplate .= "<td class='rptEmptyGrp'>&nbsp;</td>";
                            } else {
                                $valtemplate .= "<td class='rptData'>" . $fieldvalue . "</td>";
                            }
                        } else {
                            if ($secondvalue === $fieldvalue && $this->reporttype == "summary") {
                                if ($lastvalue === $newvalue) {
                                    $valtemplate .= "<td class='rptEmptyGrp'>&nbsp;</td>";
                                } else {
                                    $valtemplate .= "<td class='rptGrpHead'>" . $fieldvalue . "</td>";
                                }
                            } else {
                                if ($thirdvalue === $fieldvalue && $this->reporttype == "summary") {
                                    if ($secondvalue === $snewvalue) {
                                        $valtemplate .= "<td class='rptEmptyGrp'>&nbsp;</td>";
                                    } else {
                                        $valtemplate .= "<td class='rptGrpHead'>" . $fieldvalue . "</td>";
                                    }
                                } else {
                                    if ($this->reporttype == "tabular") {
                                        $valtemplate .= "<td class='rptData'>" . $fieldvalue . "</td>";
                                    } else {
                                        $valtemplate .= "<td class='rptGrpHead'>" . $fieldvalue . "</td>";
                                    }
                                }
                            }
                        }
                        // Performance Optimization: If direct output is required
                        if ($directOutput) {
                            echo $valtemplate;
                            $valtemplate = '';
                        }
                        // END
                    }
                    $valtemplate .= "</tr>";
                    // Performance Optimization: If direct output is required
                    if ($directOutput) {
                        echo $valtemplate;
                        $valtemplate = '';
                    }
                    // END
                    $lastvalue = $newvalue;
                    $secondvalue = $snewvalue;
                    $thirdvalue = $tnewvalue;
                    $arr_val[] = $arraylists;
                    set_time_limit($php_max_execution_time);
                } while ($custom_field_values = $adb->fetch_array($result));
                // Performance Optimization
                if ($directOutput) {
                    echo "</tr></table>";
                    echo "<script type='text/javascript' id='__reportrun_directoutput_recordcount_script'>\n\t\t\t\t\t\tif(\$('_reportrun_total')) \$('_reportrun_total').innerHTML={$noofrows};</script>";
                } else {
                    $sHTML = '<table cellpadding="5" cellspacing="0" align="center" class="rptTable">
					<tr>' . $header . '<!-- BEGIN values -->
					<tr>' . $valtemplate . '</tr>
					</table>';
                }
                //<<<<<<<<construct HTML>>>>>>>>>>>>
                $return_data[] = $sHTML;
                $return_data[] = $noofrows;
                $return_data[] = $sSQL;
                return $return_data;
            }
        } elseif ($outputformat == "PDF") {
            $sSQL = $this->sGetSQLforReport($this->reportid, $filterlist);
            $result = $adb->query($sSQL);
            if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) {
                $picklistarray = $this->getAccessPickListValues();
            }
            if ($result) {
                $y = $adb->num_fields($result);
                $noofrows = $adb->num_rows($result);
                $custom_field_values = $adb->fetch_array($result);
                $column_definitions = $adb->getFieldsDefinition($result);
                do {
                    $arraylists = array();
                    for ($i = 0; $i < $y; $i++) {
                        $fld = $adb->field_name($result, $i);
                        if (in_array($fld->name, $this->convert_currency)) {
                            $fieldvalue = convertFromMasterCurrency($custom_field_values[$i], $current_user->conv_rate);
                        } elseif (in_array($fld->name, $this->append_currency_symbol_to_value)) {
                            $curid_value = explode("::", $custom_field_values[$i]);
                            $currency_id = $curid_value[0];
                            $currency_value = $curid_value[1];
                            $cur_sym_rate = getCurrencySymbolandCRate($currency_id);
                            $fieldvalue = $cur_sym_rate['symbol'] . " " . $currency_value;
                        } elseif ($fld->name == "PurchaseOrder_Currency" || $fld->name == "SalesOrder_Currency" || $fld->name == "Invoice_Currency" || $fld->name == "Quotes_Currency") {
                            $fieldvalue = getCurrencyName($custom_field_values[$i]);
                        } elseif (in_array($fld->name, $this->ui10_fields) && !empty($custom_field_values[$i])) {
                            $type = getSalesEntityType($custom_field_values[$i]);
                            $tmp = getEntityName($type, $custom_field_values[$i]);
                            foreach ($tmp as $key => $val) {
                                $fieldvalue = $val;
                                break;
                            }
                        } else {
                            $fieldvalue = getTranslatedString($custom_field_values[$i]);
                        }
                        $append_cur = str_replace($fld->name, "", decode_html($this->getLstringforReportHeaders($fld->name)));
                        $headerLabel = str_replace("_", " ", $fld->name);
                        /*STRING TRANSLATION starts */
                        $mod_name = split(' ', $headerLabel, 2);
                        $module = '';
                        if (in_array($mod_name[0], $modules_selected)) {
                            $module = getTranslatedString($mod_name[0], $mod_name[0]);
                        }
                        if (!empty($this->secondarymodule)) {
                            if ($module != '') {
                                $headerLabel_tmp = $module . " " . getTranslatedString($mod_name[1], $mod_name[0]);
                            } else {
                                $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
                            }
                        } else {
                            if ($module != '') {
                                $headerLabel_tmp = getTranslatedString($mod_name[1], $mod_name[0]);
                            } else {
                                $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
                            }
                        }
                        if ($headerLabel == $headerLabel_tmp) {
                            $headerLabel = getTranslatedString($headerLabel_tmp);
                        } else {
                            $headerLabel = $headerLabel_tmp;
                        }
                        /*STRING TRANSLATION starts */
                        if (trim($append_cur) != "") {
                            $headerLabel .= $append_cur;
                        }
                        $fieldvalue = str_replace("<", "&lt;", $fieldvalue);
                        $fieldvalue = str_replace(">", "&gt;", $fieldvalue);
                        // Check for role based pick list
                        $temp_val = $fld->name;
                        if (is_array($picklistarray)) {
                            if (array_key_exists($temp_val, $picklistarray)) {
                                if (!in_array($custom_field_values[$i], $picklistarray[$fld->name]) && $custom_field_values[$i] != '') {
                                    $fieldvalue = $app_strings['LBL_NOT_ACCESSIBLE'];
                                }
                            }
                        }
                        if (is_array($picklistarray[1])) {
                            if (array_key_exists($temp_val, $picklistarray[1])) {
                                $temp = explode(",", str_ireplace(' |##| ', ',', $fieldvalue));
                                $temp_val = array();
                                foreach ($temp as $key => $val) {
                                    if (!in_array(trim($val), $picklistarray[1][$fld->name]) && trim($val) != '') {
                                        $temp_val[] = $app_strings['LBL_NOT_ACCESSIBLE'];
                                    } else {
                                        $temp_val[] = $val;
                                    }
                                }
                                $fieldvalue = is_array($temp_val) ? implode(", ", $temp_val) : '';
                            }
                        }
                        if ($fieldvalue == "") {
                            $fieldvalue = "-";
                        } else {
                            if (stristr($fieldvalue, "|##|")) {
                                $fieldvalue = str_ireplace(' |##| ', ', ', $fieldvalue);
                            } else {
                                if ($fld_type == "date" || $fld_type == "datetime") {
                                    $fieldvalue = getDisplayDate($fieldvalue);
                                }
                            }
                        }
                        if (array_key_exists($this->getLstringforReportHeaders($fld->name), $arraylists)) {
                            $arraylists[$headerLabel] = $fieldvalue;
                        } else {
                            $arraylists[$headerLabel] = $fieldvalue;
                        }
                    }
                    $arr_val[] = $arraylists;
                    set_time_limit($php_max_execution_time);
                } while ($custom_field_values = $adb->fetch_array($result));
                return $arr_val;
            }
        } elseif ($outputformat == "TOTALHTML") {
            $escapedchars = array('_SUM', '_AVG', '_MIN', '_MAX');
            $sSQL = $this->sGetSQLforReport($this->reportid, $filterlist, "COLUMNSTOTOTAL");
            if (isset($this->totallist)) {
                if ($sSQL != "") {
                    $result = $adb->query($sSQL);
                    $y = $adb->num_fields($result);
                    $custom_field_values = $adb->fetch_array($result);
                    $coltotalhtml .= "<table align='center' width='60%' cellpadding='3' cellspacing='0' border='0' class='rptTable'><tr><td class='rptCellLabel'>" . $mod_strings[Totals] . "</td><td class='rptCellLabel'>" . $mod_strings[SUM] . "</td><td class='rptCellLabel'>" . $mod_strings[AVG] . "</td><td class='rptCellLabel'>" . $mod_strings[MIN] . "</td><td class='rptCellLabel'>" . $mod_strings[MAX] . "</td></tr>";
                    // Performation Optimization: If Direct output is desired
                    if ($directOutput) {
                        echo $coltotalhtml;
                        $coltotalhtml = '';
                    }
                    // END
                    foreach ($this->totallist as $key => $value) {
                        $fieldlist = explode(":", $key);
                        $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid, uitype as uitype from vtiger_field where tablename = ? and columnname=?", array($fieldlist[1], $fieldlist[2]));
                        if ($adb->num_rows($mod_query) > 0) {
                            $module_name = getTabName($adb->query_result($mod_query, 0, 'tabid'));
                            $fieldlabel = trim(str_replace($escapedchars, " ", $fieldlist[3]));
                            $fieldlabel = str_replace("_", " ", $fieldlabel);
                            if ($module_name) {
                                $field = getTranslatedString($module_name) . " " . getTranslatedString($fieldlabel, $module_name);
                            } else {
                                $field = getTranslatedString($module_name) . " " . getTranslatedString($fieldlabel);
                            }
                        }
                        $uitype_arr[str_replace($escapedchars, " ", $module_name . "_" . $fieldlist[3])] = $adb->query_result($mod_query, 0, "uitype");
                        $totclmnflds[str_replace($escapedchars, " ", $module_name . "_" . $fieldlist[3])] = $field;
                    }
                    for ($i = 0; $i < $y; $i++) {
                        $fld = $adb->field_name($result, $i);
                        $keyhdr[$fld->name] = $custom_field_values[$i];
                    }
                    foreach ($totclmnflds as $key => $value) {
                        $coltotalhtml .= '<tr class="rptGrpHead" valign=top>';
                        $col_header = trim(str_replace($modules, " ", $value));
                        $fld_name_1 = $this->primarymodule . "_" . trim($value);
                        $fld_name_2 = $this->secondarymodule . "_" . trim($value);
                        if ($uitype_arr[$value] == 71 || in_array($fld_name_1, $this->convert_currency) || in_array($fld_name_1, $this->append_currency_symbol_to_value) || in_array($fld_name_2, $this->convert_currency) || in_array($fld_name_2, $this->append_currency_symbol_to_value)) {
                            $col_header .= " (" . $app_strings['LBL_IN'] . " " . $current_user->currency_symbol . ")";
                            $convert_price = true;
                        } else {
                            $convert_price = false;
                        }
                        $coltotalhtml .= '<td class="rptData">' . $col_header . '</td>';
                        $value = trim($key);
                        $arraykey = $value . '_SUM';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td class="rptTotal">&nbsp;</td>';
                        }
                        $arraykey = $value . '_AVG';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td class="rptTotal">&nbsp;</td>';
                        }
                        $arraykey = $value . '_MIN';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td class="rptTotal">&nbsp;</td>';
                        }
                        $arraykey = $value . '_MAX';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td class="rptTotal">&nbsp;</td>';
                        }
                        $coltotalhtml .= '<tr>';
                        // Performation Optimization: If Direct output is desired
                        if ($directOutput) {
                            echo $coltotalhtml;
                            $coltotalhtml = '';
                        }
                        // END
                    }
                    $coltotalhtml .= "</table>";
                    // Performation Optimization: If Direct output is desired
                    if ($directOutput) {
                        echo $coltotalhtml;
                        $coltotalhtml = '';
                    }
                    // END
                }
            }
            return $coltotalhtml;
        } elseif ($outputformat == "PRINT") {
            $sSQL = $this->sGetSQLforReport($this->reportid, $filterlist);
            $result = $adb->query($sSQL);
            if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) {
                $picklistarray = $this->getAccessPickListValues();
            }
            if ($result) {
                $y = $adb->num_fields($result);
                $arrayHeaders = array();
                for ($x = 0; $x < $y; $x++) {
                    $fld = $adb->field_name($result, $x);
                    if (in_array($this->getLstringforReportHeaders($fld->name), $arrayHeaders)) {
                        $headerLabel = str_replace("_", " ", $fld->name);
                        $arrayHeaders[] = $headerLabel;
                    } else {
                        $headerLabel = str_replace($modules, " ", $this->getLstringforReportHeaders($fld->name));
                        $arrayHeaders[] = $headerLabel;
                    }
                    /*STRING TRANSLATION starts */
                    $mod_name = split(' ', $headerLabel, 2);
                    $module = '';
                    if (in_array($mod_name[0], $modules_selected)) {
                        $module = getTranslatedString($mod_name[0], $mod_name[0]);
                    }
                    if (!empty($this->secondarymodule)) {
                        if ($module != '') {
                            $headerLabel_tmp = $module . " " . getTranslatedString($mod_name[1], $mod_name[0]);
                        } else {
                            $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
                        }
                    } else {
                        if ($module != '') {
                            $headerLabel_tmp = getTranslatedString($mod_name[1], $mod_name[0]);
                        } else {
                            $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
                        }
                    }
                    if ($headerLabel == $headerLabel_tmp) {
                        $headerLabel = getTranslatedString($headerLabel_tmp);
                    } else {
                        $headerLabel = $headerLabel_tmp;
                    }
                    /*STRING TRANSLATION ends */
                    $header .= "<th>" . $headerLabel . "</th>";
                }
                $noofrows = $adb->num_rows($result);
                $custom_field_values = $adb->fetch_array($result);
                $groupslist = $this->getGroupingList($this->reportid);
                $column_definitions = $adb->getFieldsDefinition($result);
                do {
                    $arraylists = array();
                    if (count($groupslist) == 1) {
                        $newvalue = $custom_field_values[0];
                    } elseif (count($groupslist) == 2) {
                        $newvalue = $custom_field_values[0];
                        $snewvalue = $custom_field_values[1];
                    } elseif (count($groupslist) == 3) {
                        $newvalue = $custom_field_values[0];
                        $snewvalue = $custom_field_values[1];
                        $tnewvalue = $custom_field_values[2];
                    }
                    if ($newvalue == "") {
                        $newvalue = "-";
                    }
                    if ($snewvalue == "") {
                        $snewvalue = "-";
                    }
                    if ($tnewvalue == "") {
                        $tnewvalue = "-";
                    }
                    $valtemplate .= "<tr>";
                    for ($i = 0; $i < $y; $i++) {
                        $fld = $adb->field_name($result, $i);
                        if (in_array($fld->name, $this->convert_currency)) {
                            $fieldvalue = convertFromMasterCurrency($custom_field_values[$i], $current_user->conv_rate);
                        } elseif (in_array($fld->name, $this->append_currency_symbol_to_value)) {
                            $curid_value = explode("::", $custom_field_values[$i]);
                            $currency_id = $curid_value[0];
                            $currency_value = $curid_value[1];
                            $cur_sym_rate = getCurrencySymbolandCRate($currency_id);
                            $fieldvalue = $cur_sym_rate['symbol'] . " " . $currency_value;
                        } elseif ($fld->name == "PurchaseOrder_Currency" || $fld->name == "SalesOrder_Currency" || $fld->name == "Invoice_Currency" || $fld->name == "Quotes_Currency") {
                            $fieldvalue = getCurrencyName($custom_field_values[$i]);
                        } elseif (in_array($fld->name, $this->ui10_fields) && !empty($custom_field_values[$i])) {
                            $type = getSalesEntityType($custom_field_values[$i]);
                            $tmp = getEntityName($type, $custom_field_values[$i]);
                            foreach ($tmp as $key => $val) {
                                $fieldvalue = $val;
                                break;
                            }
                        } else {
                            $fieldvalue = getTranslatedString($custom_field_values[$i]);
                        }
                        $fieldvalue = str_replace("<", "&lt;", $fieldvalue);
                        $fieldvalue = str_replace(">", "&gt;", $fieldvalue);
                        //Check For Role based pick list
                        $temp_val = $fld->name;
                        if (is_array($picklistarray)) {
                            if (array_key_exists($temp_val, $picklistarray)) {
                                if (!in_array($custom_field_values[$i], $picklistarray[$fld->name]) && $custom_field_values[$i] != '') {
                                    $fieldvalue = $app_strings['LBL_NOT_ACCESSIBLE'];
                                }
                            }
                        }
                        if (is_array($picklistarray[1])) {
                            if (array_key_exists($temp_val, $picklistarray[1])) {
                                $temp = explode(",", str_ireplace(' |##| ', ',', $fieldvalue));
                                $temp_val = array();
                                foreach ($temp as $key => $val) {
                                    if (!in_array(trim($val), $picklistarray[1][$fld->name]) && trim($val) != '') {
                                        $temp_val[] = $app_strings['LBL_NOT_ACCESSIBLE'];
                                    } else {
                                        $temp_val[] = $val;
                                    }
                                }
                                $fieldvalue = is_array($temp_val) ? implode(", ", $temp_val) : '';
                            }
                        }
                        if ($fieldvalue == "") {
                            $fieldvalue = "-";
                        } else {
                            if (stristr($fieldvalue, "|##|")) {
                                $fieldvalue = str_ireplace(' |##| ', ', ', $fieldvalue);
                            } else {
                                if ($fld_type == "date" || $fld_type == "datetime") {
                                    $fieldvalue = getDisplayDate($fieldvalue);
                                }
                            }
                        }
                        if ($lastvalue == $fieldvalue && $this->reporttype == "summary") {
                            if ($this->reporttype == "summary") {
                                $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'>&nbsp;</td>";
                            } else {
                                $valtemplate .= "<td>" . $fieldvalue . "</td>";
                            }
                        } else {
                            if ($secondvalue == $fieldvalue && $this->reporttype == "summary") {
                                if ($lastvalue == $newvalue) {
                                    $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'>&nbsp;</td>";
                                } else {
                                    $valtemplate .= "<td>" . $fieldvalue . "</td>";
                                }
                            } else {
                                if ($thirdvalue == $fieldvalue && $this->reporttype == "summary") {
                                    if ($secondvalue == $snewvalue) {
                                        $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'>&nbsp;</td>";
                                    } else {
                                        $valtemplate .= "<td>" . $fieldvalue . "</td>";
                                    }
                                } else {
                                    if ($this->reporttype == "tabular") {
                                        $valtemplate .= "<td>" . $fieldvalue . "</td>";
                                    } else {
                                        $valtemplate .= "<td>" . $fieldvalue . "</td>";
                                    }
                                }
                            }
                        }
                    }
                    $valtemplate .= "</tr>";
                    $lastvalue = $newvalue;
                    $secondvalue = $snewvalue;
                    $thirdvalue = $tnewvalue;
                    $arr_val[] = $arraylists;
                    set_time_limit($php_max_execution_time);
                } while ($custom_field_values = $adb->fetch_array($result));
                $sHTML = '<tr>' . $header . '</tr>' . $valtemplate;
                $return_data[] = $sHTML;
                $return_data[] = $noofrows;
                return $return_data;
            }
        } elseif ($outputformat == "PRINT_TOTAL") {
            $escapedchars = array('_SUM', '_AVG', '_MIN', '_MAX');
            $sSQL = $this->sGetSQLforReport($this->reportid, $filterlist, "COLUMNSTOTOTAL");
            if (isset($this->totallist)) {
                if ($sSQL != "") {
                    $result = $adb->query($sSQL);
                    $y = $adb->num_fields($result);
                    $custom_field_values = $adb->fetch_array($result);
                    $coltotalhtml .= '<table width="100%" border="0" cellpadding="5" cellspacing="0" align="center" class="printReport" ><tr><th>' . $mod_strings[Totals] . '</th><th>' . $mod_strings[SUM] . '</th><th>' . $mod_strings[AVG] . '</th><th>' . $mod_strings[MIN] . '</th><th>' . $mod_strings[MAX] . '</th></tr>';
                    foreach ($this->totallist as $key => $value) {
                        $fieldlist = explode(":", $key);
                        $totclmnflds[str_replace($escapedchars, " ", $fieldlist[3])] = str_replace($escapedchars, " ", $fieldlist[3]);
                    }
                    for ($i = 0; $i < $y; $i++) {
                        $fld = $adb->field_name($result, $i);
                        $keyhdr[$fld->name] = $custom_field_values[$i];
                    }
                    foreach ($totclmnflds as $key => $value) {
                        $coltotalhtml .= '<tr valign=top>';
                        $col_header = getTranslatedString(trim(str_replace($modules, " ", $value)));
                        $fld_name_1 = $this->primarymodule . "_" . trim($value);
                        $fld_name_2 = $this->secondarymodule . "_" . trim($value);
                        if (in_array($fld_name_1, $this->convert_currency) || in_array($fld_name_1, $this->append_currency_symbol_to_value) || in_array($fld_name_2, $this->convert_currency) || in_array($fld_name_2, $this->append_currency_symbol_to_value)) {
                            $col_header .= " (" . $app_strings['LBL_IN'] . " " . $current_user->currency_symbol . ")";
                            $convert_price = true;
                        } else {
                            $convert_price = false;
                        }
                        $coltotalhtml .= '<td>' . $col_header . '</td>';
                        $arraykey = trim($value) . '_SUM';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td>' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td>&nbsp;</td>';
                        }
                        $arraykey = trim($value) . '_AVG';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td>' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td>&nbsp;</td>';
                        }
                        $arraykey = trim($value) . '_MIN';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td>' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td>&nbsp;</td>';
                        }
                        $arraykey = trim($value) . '_MAX';
                        if (isset($keyhdr[$arraykey])) {
                            if ($convert_price) {
                                $conv_value = convertFromMasterCurrency($keyhdr[$arraykey], $current_user->conv_rate);
                            } else {
                                $conv_value = $keyhdr[$arraykey];
                            }
                            $coltotalhtml .= '<td>' . $conv_value . '</td>';
                        } else {
                            $coltotalhtml .= '<td>&nbsp;</td>';
                        }
                        $coltotalhtml .= '<tr>';
                    }
                    $coltotalhtml .= "</table>";
                }
            }
            return $coltotalhtml;
        }
    }
Example #4
0
 /**
  * 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 pipeline_by_lead_source($legends = array('foo', 'bar'), $user_id = array('1'), $cache_file_name = 'a_file', $refresh = true, $width = 900, $height = 500)
 {
     global $log, $current_user;
     $log->debug("Entering pipeline_by_lead_source(" . $legends . ") method ...");
     global $app_strings, $lang_crm, $current_module_strings, $log, $charset, $tmp_dir;
     global $theme;
     include_once 'Image/Graph.php';
     include_once 'Image/Canvas.php';
     $font = calculate_font_name($lang_crm);
     if (!file_exists($cache_file_name) || !file_exists($cache_file_name . '.map') || $refresh == true) {
         $log =& LoggerManager::getLogger('opportunity charts');
         $log->debug("starting pipeline chart");
         $log->debug("legends is:");
         $log->debug($legends);
         $log->debug("user_id is: ");
         $log->debug($user_id);
         $log->debug("cache_file_name is: {$cache_file_name}");
         //Now do the db queries
         //query for opportunity data that matches $legends and $user
         $where = "";
         //build the where clause for the query that matches $datax
         $count = count($legends);
         if ($count > 0) {
             $where .= " leadsource in ( ";
             $ls_i = 0;
             foreach ($legends as $key => $value) {
                 if ($ls_i != 0) {
                     $where .= ", ";
                 }
                 $where .= "'" . addslashes($key) . "'";
                 $ls_i++;
             }
             $where .= ")";
         }
         $opp = new Potentials();
         $opp_list = $opp->get_full_list("vtiger_potential.amount DESC, vtiger_potential.closingdate DESC", $where);
         //build pipeline by lead source data
         $total = 0;
         $count = array();
         $sum = array();
         if (isset($opp_list)) {
             foreach ($opp_list as $record) {
                 if (!isset($sum[$record->column_fields['leadsource']])) {
                     $sum[$record->column_fields['leadsource']] = 0;
                 }
                 if (isset($record->column_fields['amount']) && isset($record->column_fields['leadsource'])) {
                     // Strip all non numbers from this string.
                     $amount = convertFromMasterCurrency(preg_replace('/[^0-9]/', '', floor($record->column_fields['amount'])), $current_user->conv_rate);
                     $sum[$record->column_fields['leadsource']] = $sum[$record->column_fields['leadsource']] + $amount / 1000;
                     if (isset($count[$record->column_fields['leadsource']])) {
                         $count[$record->column_fields['leadsource']]++;
                     } else {
                         $count[$record->column_fields['leadsource']] = 1;
                     }
                     $total = $total + $amount / 1000;
                 }
             }
         }
         $visible_legends = array();
         $data = array();
         $aTargets = array();
         $aAlts = array();
         foreach ($legends as $lead_source_key => $lead_source_translation) {
             if (isset($sum[$lead_source_key])) {
                 array_push($data, $sum[$lead_source_key]);
                 if ($lead_source_key != '') {
                     array_push($visible_legends, $lead_source_translation);
                 } else {
                     // put none in if the vtiger_field is blank.
                     array_push($visible_legends, $current_module_strings['NTC_NO_LEGENDS']);
                 }
                 $cvid = getCvIdOfAll("Potentials");
                 array_push($aTargets, "index.php?module=Potentials&action=ListView&leadsource=" . urlencode($lead_source_key) . "&query=true&type=dbrd&viewname=" . $cvid);
                 array_push($aAlts, $count[$lead_source_key] . " " . $current_module_strings['LBL_OPPS_IN_LEAD_SOURCE'] . " {$lead_source_translation}\t");
             }
         }
         $log->debug("sum is:");
         $log->debug($sum);
         $log->debug("count is:");
         $log->debug($count);
         $log->debug("total is: {$total}");
         if ($total == 0) {
             $log->debug("Exiting pipeline_by_lead_source method ...");
             return $current_module_strings['ERR_NO_OPPS'];
         }
         if ($theme == "blue") {
             $font_color = "#212473";
         } else {
             $font_color = "#000000";
         }
         $canvas =& Image_Canvas::factory('png', array('width' => $width, 'height' => $height, 'usemap' => true));
         $imagemap = $canvas->getImageMap();
         $graph =& Image_Graph::factory('graph', $canvas);
         $font =& $graph->addNew('font', calculate_font_name($lang_crm));
         // set the font size to 11 pixels
         $font->setSize(8);
         $font->setColor($font_color);
         $graph->setFont($font);
         // create the plotarea layout
         $title =& Image_Graph::factory('title', array('Test', 10));
         $plotarea =& Image_Graph::factory('plotarea', array('category', 'axis'));
         $footer =& Image_Graph::factory('title', array('Footer', 8));
         $graph->add(Image_Graph::vertical($title, Image_Graph::vertical($plotarea, $footer, 90), 5));
         // Generate colours
         $colors = color_generator(count($visible_legends), '#33CCFF', '#3322FF');
         $index = 0;
         $dataset =& Image_Graph::factory('dataset');
         $fills =& Image_Graph::factory('Image_Graph_Fill_Array');
         foreach ($visible_legends as $legend) {
             $dataset->addPoint($legend, $data[$index], array('url' => $aTargets[$index], 'alt' => $aAlts[$index]));
             $fills->addColor($colors[$index]);
             $log->debug('point =' . $legend . ',' . $data[$index]);
             $index++;
         }
         // create the pie chart and associate the filling colours
         $gbplot =& $plotarea->addNew('pie', $dataset);
         $plotarea->hideAxis();
         $gbplot->setFillStyle($fills);
         // Setup title
         $titlestr = $current_module_strings['LBL_TOTAL_PIPELINE'] . $current_user->currency_symbol . $total . $app_strings['LBL_THOUSANDS_SYMBOL'];
         //$titlestr = $current_module_strings['LBL_TOTAL_PIPELINE'].$current_user->currency_symbol.$total;
         $title->setText($titlestr);
         // format the data values
         $valueproc =& Image_Graph::factory('Image_Graph_DataPreprocessor_Formatted', $current_user->currency_symbol . "%d");
         // set markers
         $marker =& $graph->addNew('value_marker', IMAGE_GRAPH_VALUE_Y);
         $marker->setDataPreprocessor($valueproc);
         $marker->setFillColor('#FFFFFF');
         $marker->setBorderColor($font_color);
         $marker->setFontColor($font_color);
         $marker->setFontSize(8);
         $pointingMarker =& $graph->addNew('Image_Graph_Marker_Pointing_Angular', array(20, &$marker));
         $gbplot->setMarker($pointingMarker);
         // set legend
         $legend_box =& $plotarea->addNew('legend');
         $legend_box->setPadding(array('top' => 20, 'bottom' => 0, 'left' => 0, 'right' => 0));
         $legend_box->setFillColor('#F5F5F5');
         $legend_box->showShadow();
         $subtitle = $current_module_strings['LBL_OPP_SIZE'] . $current_user->currency_symbol . $current_module_strings['LBL_OPP_SIZE_VALUE'];
         $footer->setText($subtitle);
         $footer->setAlignment(IMAGE_GRAPH_ALIGN_TOP_LEFT);
         $imgMap = $graph->done(array('tohtml' => true, 'border' => 0, 'filename' => $cache_file_name, 'filepath' => './', 'urlpath' => ''));
         //$imgMap = htmlspecialchars($output);
         save_image_map($cache_file_name . '.map', $imgMap);
     } else {
         $imgMap_fp = fopen($cache_file_name . '.map', "rb");
         $imgMap = fread($imgMap_fp, filesize($cache_file_name . '.map'));
         fclose($imgMap_fp);
     }
     $fileModTime = filemtime($cache_file_name . '.map');
     $return = "\n{$imgMap}";
     $log->debug("Exiting pipeline_by_lead_source method ...");
     return $return;
 }