function GenerateReport($outputformat, $filtersql, $directOutput = false, $startLimit = false, $endLimit = false)
{
global $adb, $current_user, $php_max_execution_time;
global $modules, $app_strings;
global $mod_strings;
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 Reference fields list list
$referencefieldres = $adb->pquery("SELECT tabid, fieldlabel, uitype from vtiger_field WHERE uitype in (10,101)", array());
if ($referencefieldres) {
foreach ($referencefieldres as $referencefieldrow) {
$uiType = $referencefieldrow['uitype'];
$modprefixedlabel = getTabModuleName($referencefieldrow['tabid']) . ' ' . $referencefieldrow['fieldlabel'];
$modprefixedlabel = str_replace(' ', '__', $modprefixedlabel);
if ($uiType == 10 && !in_array($modprefixedlabel, $this->ui10_fields)) {
$this->ui10_fields[] = $modprefixedlabel;
} elseif ($uiType == 101 && !in_array($modprefixedlabel, $this->ui101_fields)) {
$this->ui101_fields[] = $modprefixedlabel;
}
}
}
if ($outputformat == "HTML") {
$sSQL = $this->sGetSQLforReport($this->reportid, $filtersql, $outputformat, false, $startLimit, $endLimit);
$sSQL .= " LIMIT 0, " . (self::$HTMLVIEW_MAX_ROWS + 1);
// Pull a record more than limit
$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);
$moduleLabel = '';
if (in_array($mod_name[0], $modules_selected)) {
$moduleLabel = getTranslatedString($mod_name[0], $mod_name[0]);
}
if (!empty($this->secondarymodule)) {
if ($moduleLabel != '') {
$headerLabel_tmp = $moduleLabel . " " . getTranslatedString($mod_name[1], $mod_name[0]);
} else {
$headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
}
} else {
if ($moduleLabel != '') {
$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;
$fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i);
//check for Roll based pick list
$temp_val = $fld->name;
if ($fieldvalue == "") {
$fieldvalue = "-";
} else {
if ($fld->name == $this->primarymodule . '__LBL_ACTION' && $fieldvalue != '-') {
$fieldvalue = "<a href='index.php?module={$this->primarymodule}&action=DetailView&record={$fieldvalue}' target='_blank'>" . getTranslatedString('LBL_VIEW_DETAILS', 'Reports') . "</a>";
}
}
if ($lastvalue == $fieldvalue && $this->reporttype == "summary") {
if ($this->reporttype == "summary") {
$valtemplate .= "<td class='rptEmptyGrp'> </td>";
} else {
$valtemplate .= "<td class='rptData'>" . $fieldvalue . "</td>";
}
} else {
if ($secondvalue === $fieldvalue && $this->reporttype == "summary") {
if ($lastvalue === $newvalue) {
$valtemplate .= "<td class='rptEmptyGrp'> </td>";
} else {
$valtemplate .= "<td class='rptGrpHead'>" . $fieldvalue . "</td>";
}
} else {
if ($thirdvalue === $fieldvalue && $this->reporttype == "summary") {
if ($secondvalue === $snewvalue) {
$valtemplate .= "<td class='rptEmptyGrp'> </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: Provide feedback on export option if required
// NOTE: We should make sure to pull at-least 1 row more than max-limit for this to work.
if ($noofrows > self::$HTMLVIEW_MAX_ROWS) {
// Performance Optimization: Output directly
if ($directOutput) {
echo '</tr></table><br><table width="100%" cellpading="0" cellspacing="0"><tr>';
echo sprintf('<td colspan="%s" align="right"><span class="genHeaderGray">%s</span></td>', $y, getTranslatedString('Only') . " " . self::$HTMLVIEW_MAX_ROWS . "+ " . getTranslatedString('records found') . ". " . getTranslatedString('Export to') . " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('ReportsAjax&file=CreateCSV',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/csv-file.png'></a> /" . " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('CreateXL',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/xls-file.jpg'></a>");
} else {
$valtemplate .= '</tr></table><br><table width="100%" cellpading="0" cellspacing="0"><tr>';
$valtemplate .= sprintf('<td colspan="%s" align="right"><span class="genHeaderGray">%s</span></td>', $y, getTranslatedString('Only') . " " . self::$HTMLVIEW_MAX_ROWS . " " . getTranslatedString('records found') . ". " . getTranslatedString('Export to') . " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('ReportsAjax&file=CreateCSV',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/csv-file.png'></a> /" . " <a href=\"javascript:;\" onclick=\"goToURL(CrearEnlace('CreateXL',{$this->reportid}));\"><img style='vertical-align:text-top' src='themes/images/xls-file.jpg'></a>");
}
}
// Performance Optimization
if ($directOutput) {
$totalDisplayString = $noofrows;
if ($noofrows > self::$HTMLVIEW_MAX_ROWS) {
$totalDisplayString = self::$HTMLVIEW_MAX_ROWS . "+";
}
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='{$totalDisplayString}';</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, $filtersql, $outputformat, false, $startLimit, $endLimit);
$result = $adb->pquery($sSQL, array());
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);
$fld_type = $column_definitions[$i]->type;
list($module, $fieldLabel) = explode('__', $fld->name, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
$fieldType = null;
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
$fieldType = $field->getFieldDataType();
}
if (!empty($fieldInfo)) {
$translatedLabel = getTranslatedString($field->getFieldLabelKey(), $module);
} else {
$fieldLabel = str_replace("__", " ", $fieldLabel);
$translatedLabel = getTranslatedString($fieldLabel, $module);
}
/*STRING TRANSLATION starts */
$moduleLabel = '';
if (in_array($module, $modules_selected)) {
$moduleLabel = getTranslatedString($module, $module);
}
if (empty($translatedLabel)) {
$translatedLabel = getTranslatedString(str_replace('__', " ", $fld->name), $module);
}
$headerLabel = $translatedLabel;
if (!empty($this->secondarymodule)) {
if ($moduleLabel != '') {
$headerLabel = $moduleLabel . " " . $translatedLabel;
}
}
// Check for role based pick list
$temp_val = $fld->name;
$fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i);
if ($fld->name == $this->primarymodule . '__LBL_ACTION' && $fieldvalue != '-') {
$fieldvalue = "<a href='index.php?module={$this->primarymodule}&view=Detail&record={$fieldvalue}' target='_blank'>" . getTranslatedString('LBL_VIEW_DETAILS', 'Reports') . "</a>";
}
$arraylists[$headerLabel] = $fieldvalue;
}
$arr_val[] = $arraylists;
set_time_limit($php_max_execution_time);
} while ($custom_field_values = $adb->fetch_array($result));
$data['data'] = $arr_val;
$data['count'] = $noofrows;
return $data;
}
} elseif ($outputformat == "TOTALXLS") {
$escapedchars = array('__SUM', '__AVG', '__MIN', '__MAX');
$totalpdf = array();
$sSQL = $this->sGetSQLforReport($this->reportid, $filtersql, "COLUMNSTOTOTAL");
if (isset($this->totallist)) {
if ($sSQL != "") {
$result = $adb->query($sSQL);
$y = $adb->num_fields($result);
$custom_field_values = $adb->fetch_array($result);
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 = getTabModuleName($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, $module_name) . " " . getTranslatedString($fieldlabel, $module_name);
} else {
$field = getTranslatedString($fieldlabel);
}
}
// Since there are duplicate entries for this table
if ($fieldlist[1] == 'vtiger_inventoryproductrel') {
$module_name = $this->primarymodule;
}
$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];
}
$rowcount = 0;
foreach ($totclmnflds as $key => $value) {
$col_header = trim(str_replace($modules, " ", $value));
$fld_name_1 = $this->primarymodule . "__" . trim($value);
$fld_name_2 = $this->secondarymodule . "__" . trim($value);
if ($uitype_arr[$key] == 71 || $uitype_arr[$key] == 72 || in_array($fld_name_1, $this->append_currency_symbol_to_value) || 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;
}
$value = trim($key);
$arraykey = $value . '__SUM';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$arraykey = $value . '__AVG';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$arraykey = $value . '__MIN';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$arraykey = $value . '__MAX';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$rowcount++;
}
}
}
return $totalpdf;
} elseif ($outputformat == "TOTALHTML") {
$escapedchars = array('__SUM', '__AVG', '__MIN', '__MAX');
$sSQL = $this->sGetSQLforReport($this->reportid, $filtersql, "COLUMNSTOTOTAL");
static $modulename_cache = array();
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);
$module_name = NULL;
$cachekey = $fieldlist[1] . ":" . $fieldlist[2];
if (!isset($modulename_cache[$cachekey])) {
$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 = getTabModuleName($adb->query_result($mod_query, 0, 'tabid'));
$modulename_cache[$cachekey] = $module_name;
}
} else {
$module_name = $modulename_cache[$cachekey];
}
if ($module_name) {
$fieldlabel = trim(str_replace($escapedchars, " ", $fieldlist[3]));
$fieldlabel = str_replace("__", " ", $fieldlabel);
$field = getTranslatedString($module_name, $module_name) . " " . getTranslatedString($fieldlabel, $module_name);
} else {
$field = 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[$key] == 71 || $uitype_arr[$key] == 72 || in_array($fld_name_1, $this->append_currency_symbol_to_value) || 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 = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </td>';
}
$arraykey = $value . '__AVG';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </td>';
}
$arraykey = $value . '__MIN';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </td>';
}
$arraykey = $value . '__MAX';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </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, $filtersql, $outputformat);
$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 - 1; $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);
$moduleLabel = '';
if (in_array($mod_name[0], $modules_selected)) {
$moduleLabel = getTranslatedString($mod_name[0], $mod_name[0]);
}
if (!empty($this->secondarymodule)) {
if ($moduleLabel != '') {
$headerLabel_tmp = $moduleLabel . " " . getTranslatedString($mod_name[1], $mod_name[0]);
} else {
$headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
}
} else {
if ($moduleLabel != '') {
$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 - 1; $i++) {
$fld = $adb->field_name($result, $i);
$fld_type = $column_definitions[$i]->type;
$fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i);
if ($lastvalue == $fieldvalue && $this->reporttype == "summary") {
if ($this->reporttype == "summary") {
$valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>";
} else {
$valtemplate .= "<td>" . $fieldvalue . "</td>";
}
} else {
if ($secondvalue == $fieldvalue && $this->reporttype == "summary") {
if ($lastvalue == $newvalue) {
$valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>";
} else {
$valtemplate .= "<td>" . $fieldvalue . "</td>";
}
} else {
if ($thirdvalue == $fieldvalue && $this->reporttype == "summary") {
if ($secondvalue == $snewvalue) {
$valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </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, $filtersql, "COLUMNSTOTOTAL");
if (isset($this->totallist)) {
if ($sSQL != "") {
$result = $adb->query($sSQL);
$y = $adb->num_fields($result);
$custom_field_values = $adb->fetch_array($result);
$coltotalhtml .= "<br /><table align='center' width='60%' cellpadding='3' cellspacing='0' border='1' class='printReport'><tr><td class='rptCellLabel'>" . $mod_strings['Totals'] . "</td><td><b>" . $mod_strings['SUM'] . "</b></td><td><b>" . $mod_strings['AVG'] . "</b></td><td><b>" . $mod_strings['MIN'] . "</b></td><td><b>" . $mod_strings['MAX'] . "</b></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 = getTabModuleName($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, $module_name) . " " . getTranslatedString($fieldlabel, $module_name);
} else {
$field = 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">';
$col_header = getTranslatedString(trim(str_replace($modules, " ", $value)));
$fld_name_1 = $this->primarymodule . "__" . trim($value);
$fld_name_2 = $this->secondarymodule . "__" . trim($value);
if ($uitype_arr[$key] == 71 || $uitype_arr[$key] == 72 || in_array($fld_name_1, $this->append_currency_symbol_to_value) || 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 = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </td>";
}
$arraykey = $value . '__AVG';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </td>";
}
$arraykey = $value . '__MIN';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </td>";
}
$arraykey = $value . '__MAX';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </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;
}
}
function replaceReport($content, $reportid)
{
session_start();
require_once "modules/Reports/ReportRun.php";
require_once "modules/Reports/Reports.php";
require_once 'include/utils/utils.php';
$current_language = Users_Record_Model::getCurrentUserModel()->get('language');
include "modules/OSSPdf/language/" . $current_language . ".lang.php";
$language = $_SESSION['authenticated_user_language'] . '.lang.php';
require_once "include/language/{$language}";
$db = PearDatabase::getInstance();
$oReport = new Reports($reportid);
//Code given by Csar Rodrguez for Rwport Filter
$filtercolumn = $_REQUEST["stdDateFilterField"];
$filter = $_REQUEST["stdDateFilter"];
$oReportRun = new ReportRun($reportid);
$startdate = $_REQUEST['startdate'];
$enddate = $_REQUEST['enddate'];
if (!empty($startdate) && !empty($enddate) && $startdate != "0000-00-00" && $enddate != "0000-00-00") {
$date = new DateTimeField($_REQUEST['startdate']);
$endDate = new DateTimeField($_REQUEST['enddate']);
$startdate = $date->getDBInsertDateValue();
//Convert the user date format to DB date format
$enddate = $endDate->getDBInsertDateValue();
//Convert the user date format to DB date format
}
$filterlist = $oReportRun->RunTimeFilter($filtercolumn, $filter, $startdate, $enddate);
############///////////////////////////////#########################
$sql = $oReportRun->sGetSQLforReport($reportid, $filterlist);
$result = $db->query($sql, true);
$modules_selected = array();
$modules_selected[] = $oReportRun->primarymodule;
if (!empty($oReportRun->secondarymodule)) {
$sec_modules = split(":", $oReportRun->secondarymodule);
for ($i = 0; $i < count($sec_modules); $i++) {
$modules_selected[] = $sec_modules[$i];
}
}
$y = $db->num_fields($result);
$report = '<table border="1" cellpadding="2"><tr width="500px" bgcolor="lightgrey">';
$arrayHeaders = array();
for ($x = 0; $x < $y; $x++) {
$fld = $db->field_name($result, $x);
if (in_array($oReportRun->getLstringforReportHeaders($fld->name), $arrayHeaders)) {
$headerLabel = str_replace("_", " ", $fld->name);
$arrayHeaders[] = $headerLabel;
} else {
$headerLabel = str_replace($modules, " ", $oReportRun->getLstringforReportHeaders($fld->name));
$headerLabel = str_replace("_", " ", $oReportRun->getLstringforReportHeaders($fld->name));
$arrayHeaders[] = $headerLabel;
}
/*STRING TRANSLATION starts */
$mod_name = split(' ', $headerLabel, 2);
$moduleLabel = '';
if (in_array($mod_name[0], $modules_selected)) {
$moduleLabel = getTranslatedString($mod_name[0], $mod_name[0]);
}
if (!empty($oReportRun->secondarymodule)) {
if ($moduleLabel != '') {
$headerLabel_tmp = getTranslatedString($mod_name[1], $mod_name[0]);
} else {
$headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]);
}
} else {
if ($moduleLabel != '') {
$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 */
$report .= "<td class='rptCellLabel'><small><b>" . $headerLabel . "</b></small></td>";
// Performance Optimization: If direct output is required
// END
}
$report .= '</tr>';
// END
$noofrows = $db->num_rows($result);
$custom_field_values = $db->fetch_array($result);
$groupslist = $oReportRun->getGroupingList($oReportRun->reportid);
$column_definitions = $db->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 = "-";
}
$report .= '<tr width="500px">';
// Performance Optimization
// END
for ($i = 0; $i < $y; $i++) {
$fld = $db->field_name($result, $i);
$fld_type = $column_definitions[$i]->type;
$fieldvalue = getReportFieldValue($oReportRun, $picklistarray, $fld, $custom_field_values, $i);
//check for Roll based pick list
$temp_val = $fld->name;
if ($fieldvalue == "") {
$fieldvalue = "-";
} else {
if ($fld->name == 'LBL_ACTION') {
}
}
if ($lastvalue == $fieldvalue && $oReportRun->reporttype == "summary") {
if ($report->reporttype == "summary") {
$valtemplate .= "<td class='rptEmptyGrp'> </td>";
} else {
$report .= "<td class='rptData'>" . $fieldvalue . "</td>";
}
} else {
if ($secondvalue === $fieldvalue && $oReportRun->reporttype == "summary") {
if ($lastvalue === $newvalue) {
$report .= "<td class='rptEmptyGrp'> </td>";
} else {
$report .= "<td class='rptGrpHead'>" . $fieldvalue . "</td>";
}
} else {
if ($thirdvalue === $fieldvalue && $oReportRun->reporttype == "summary") {
if ($secondvalue === $snewvalue) {
$report .= "<td class='rptEmptyGrp'> </td>";
} else {
$report .= "<td class='rptGrpHead'>" . $fieldvalue . "</td>";
}
} else {
if ($oReportRun->reporttype == "tabular") {
$report .= "<td class='rptData'><small>" . $fieldvalue . "</small></td>";
} else {
$report .= "<td class='rptGrpHead'><small>" . $fieldvalue . "</small></td>";
}
}
}
}
// Performance Optimization: If direct output is required
// END
}
$report .= "</tr>";
// Performance Optimization: If direct output is required
// echo $valtemplate;
// END
$lastvalue = $newvalue;
$secondvalue = $snewvalue;
$thirdvalue = $tnewvalue;
$arr_val[] = $arraylists;
set_time_limit($php_max_execution_time);
} while ($custom_field_values = $db->fetch_array($result));
$report .= "</table></br>";
$content = str_replace("#report_tag#", $report, $content);
############///////////////////////////////#########################
return $content;
}
function GenerateReport($outputformat, $filtersql, $directOutput = false, &$returnfieldinfo = array())
{
global $adb, $current_user, $php_max_execution_time;
global $modules, $app_strings, $mod_strings, $current_language;
require 'user_privileges/user_privileges_' . $current_user->id . '.php';
$picklistarray = array();
$modules_selected = array();
$modules_selected[] = $this->primarymodule;
if (!empty($this->secondarymodule)) {
$sec_modules = explode(":", $this->secondarymodule);
for ($i = 0; $i < count($sec_modules); $i++) {
$modules_selected[] = $sec_modules[$i];
}
}
// Update Reference fields list list
$referencefieldres = $adb->pquery("SELECT tabid, fieldlabel, uitype from vtiger_field WHERE uitype in (10,101)", array());
if ($referencefieldres) {
foreach ($referencefieldres as $referencefieldrow) {
$uiType = $referencefieldrow['uitype'];
$modprefixedlabel = getTabModuleName($referencefieldrow['tabid']) . ' ' . $referencefieldrow['fieldlabel'];
$modprefixedlabel = str_replace(' ', '_', $modprefixedlabel);
if ($uiType == 10 && !in_array($modprefixedlabel, $this->ui10_fields)) {
$this->ui10_fields[] = $modprefixedlabel;
} elseif ($uiType == 101 && !in_array($modprefixedlabel, $this->ui101_fields)) {
$this->ui101_fields[] = $modprefixedlabel;
}
}
}
if ($outputformat == "HTML") {
$sSQL = $this->sGetSQLforReport($this->reportid, $filtersql, $outputformat);
$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);
$noofrows = $adb->num_rows($result);
$custom_field_values = $adb->fetch_array($result);
$groupslist = $this->getGroupingList($this->reportid);
$column_definitions = $adb->getFieldsDefinition($result);
$arrayHeaders = array();
$header = '';
for ($x = 0; $x < $y; $x++) {
$fld = $adb->field_name($result, $x);
$fld_type = $column_definitions[$x]->type;
list($module, $fieldLabel) = explode('_', $fld->name, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
}
if (!empty($fieldInfo)) {
$headerLabel = getTranslatedString($field->getFieldLabelKey(), $module);
} else {
$headerLabel = getTranslatedString(str_replace('_', " ", $fieldLabel), $module);
}
/*STRING TRANSLATION starts */
$moduleLabel = '';
if (in_array($module, $modules_selected)) {
$moduleLabel = getTranslatedString($module, $module);
}
if (empty($headerLabel)) {
$headerLabel = getTranslatedString(str_replace('_', " ", $fld->name));
}
if (!empty($this->secondarymodule)) {
if ($moduleLabel != '') {
$headerLabel = $moduleLabel . " " . $headerLabel;
}
}
$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>';
}
$valtemplate = '';
$lastvalue = '';
$secondvalue = '';
$thirdvalue = '';
$sHTML = '';
do {
$arraylists = array();
$newvalue = '';
$snewvalue = '';
$tnewvalue = '';
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 = '';
}
for ($i = 0; $i < $y; $i++) {
$fld = $adb->field_name($result, $i);
$fld_type = $column_definitions[$i]->type;
$fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i);
if ($fieldvalue == '') {
$fieldvalue = "-";
} else {
if ($fld->name == 'LBL_ACTION' && $fieldvalue != '-') {
$fieldvalue = "<a href='index.php?module={$this->primarymodule}&action=DetailView&record={$fieldvalue}' target='_blank'>" . getTranslatedString('LBL_VIEW_DETAILS') . "</a>";
}
}
if ($lastvalue == $fieldvalue && $this->reporttype == "summary") {
if ($this->reporttype == "summary") {
$valtemplate .= "<td class='rptEmptyGrp'> </td>";
} else {
$valtemplate .= "<td class='rptData'>" . $fieldvalue . "</td>";
}
} else {
if ($secondvalue === $fieldvalue && $this->reporttype == "summary") {
if ($lastvalue === $newvalue) {
$valtemplate .= "<td class='rptEmptyGrp'> </td>";
} else {
$valtemplate .= "<td class='rptGrpHead'>" . $fieldvalue . "</td>";
}
} else {
if ($thirdvalue === $fieldvalue && $this->reporttype == "summary") {
if ($secondvalue === $snewvalue) {
$valtemplate .= "<td class='rptEmptyGrp'> </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 = '';
}
}
$valtemplate .= "</tr>";
// Performance Optimization: If direct output is required
if ($directOutput) {
echo $valtemplate;
$valtemplate = '';
}
$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, $filtersql);
$result = $adb->pquery($sSQL, array());
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 - 1; $i++) {
$fld = $adb->field_name($result, $i);
$fld_type = $column_definitions[$i]->type;
list($module, $fieldLabel) = explode('_', $fld->name, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
}
if (!empty($fieldInfo)) {
$headerLabel = getTranslatedString($field->getFieldLabelKey(), $module);
} else {
$headerLabel = getTranslatedString(str_replace('_', " ", $fieldLabel), $module);
}
/*STRING TRANSLATION starts */
$moduleLabel = '';
if (in_array($module, $modules_selected)) {
$moduleLabel = getTranslatedString($module, $module);
}
if (empty($headerLabel)) {
$headerLabel = getTranslatedString(str_replace('_', " ", $fld->name));
}
if (!empty($this->secondarymodule)) {
if ($moduleLabel != '') {
$headerLabel = $moduleLabel . " " . $headerLabel;
}
}
$fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i);
if (empty($returnfieldinfo[$headerLabel])) {
$returnfieldinfo[$headerLabel] = $field;
}
$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 == "TOTALXLS") {
$escapedchars = array('_SUM', '_AVG', '_MIN', '_MAX');
$totalpdf = array();
$sSQL = $this->sGetSQLforReport($this->reportid, $filtersql, "COLUMNSTOTOTAL");
if (isset($this->totallist) and count($this->totallist) > 0) {
if ($sSQL != '') {
$result = $adb->query($sSQL);
$y = $adb->num_fields($result);
$custom_field_values = $adb->fetch_array($result);
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 = getTabModuleName($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, $module_name) . " " . getTranslatedString($fieldlabel, $module_name);
} else {
$field = 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];
}
$rowcount = 0;
foreach ($totclmnflds as $key => $value) {
$col_header = trim(str_replace($modules, " ", $value));
$fld_name_1 = $this->primarymodule . "_" . trim($value);
$fld_name_2 = $this->secondarymodule . "_" . trim($value);
if ($uitype_arr[$key] == 71 || $uitype_arr[$key] == 72 || in_array($fld_name_1, $this->append_currency_symbol_to_value) || 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;
}
$value = decode_html(trim($key));
$arraykey = $value . '_SUM';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$arraykey = $value . '_AVG';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$arraykey = $value . '_MIN';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$arraykey = $value . '_MAX';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$totalpdf[$rowcount][$arraykey] = $conv_value;
} else {
$totalpdf[$rowcount][$arraykey] = '';
}
$rowcount++;
}
}
}
return $totalpdf;
} elseif ($outputformat == "TOTALHTML") {
$escapedchars = array('_SUM', '_AVG', '_MIN', '_MAX');
$sSQL = $this->sGetSQLforReport($this->reportid, $filtersql, "COLUMNSTOTOTAL");
$coltotalhtml = '';
if (isset($this->totallist) and count($this->totallist) > 0) {
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 = '';
}
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 = getTabModuleName($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, $module_name) . " " . getTranslatedString($fieldlabel, $module_name);
} else {
$field = 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[$key] == 71 || $uitype_arr[$key] == 72 || in_array($fld_name_1, $this->append_currency_symbol_to_value) || 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 = decode_html(trim($key));
$arraykey = $value . '_SUM';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
if (substr($arraykey, 0, 21) == 'Timecontrol_TotalTime' or substr($arraykey, 0, 18) == 'TCTotals_TotalTime') {
$conv_value = $keyhdr[$arraykey];
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </td>';
}
$arraykey = $value . '_AVG';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
if (substr($arraykey, 0, 21) == 'Timecontrol_TotalTime' or substr($arraykey, 0, 18) == 'TCTotals_TotalTime') {
$conv_value = $keyhdr[$arraykey];
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </td>';
}
$arraykey = $value . '_MIN';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
if (substr($arraykey, 0, 21) == 'Timecontrol_TotalTime' or substr($arraykey, 0, 18) == 'TCTotals_TotalTime') {
$conv_value = $keyhdr[$arraykey];
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </td>';
}
$arraykey = $value . '_MAX';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
if (substr($arraykey, 0, 21) == 'Timecontrol_TotalTime' or substr($arraykey, 0, 18) == 'TCTotals_TotalTime') {
$conv_value = $keyhdr[$arraykey];
}
$coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>';
} else {
$coltotalhtml .= '<td class="rptTotal"> </td>';
}
$coltotalhtml .= '<tr>';
// Performation Optimization: If Direct output is desired
if ($directOutput) {
echo $coltotalhtml;
$coltotalhtml = '';
}
}
$coltotalhtml .= "</table>";
// Performation Optimization: If Direct output is desired
if ($directOutput) {
echo $coltotalhtml;
$coltotalhtml = '';
}
}
}
return $coltotalhtml;
} elseif ($outputformat == "PRINT") {
$sSQL = $this->sGetSQLforReport($this->reportid, $filtersql);
$result = $adb->query($sSQL);
if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) {
$picklistarray = $this->getAccessPickListValues();
}
if ($result) {
$noofrows = $adb->num_rows($result);
$custom_field_values = $adb->fetch_array($result);
$groupslist = $this->getGroupingList($this->reportid);
$column_definitions = $adb->getFieldsDefinition($result);
$y = $adb->num_fields($result);
$arrayHeaders = array();
$header = '';
for ($x = 0; $x < $y - 1; $x++) {
$fld = $adb->field_name($result, $x);
$fld_type = $column_definitions[$x]->type;
list($module, $fieldLabel) = explode('_', $fld->name, 2);
$fieldInfo = getFieldByReportLabel($module, $fieldLabel);
if (!empty($fieldInfo)) {
$field = WebserviceField::fromArray($adb, $fieldInfo);
}
if (!empty($fieldInfo)) {
$headerLabel = getTranslatedString($field->getFieldLabelKey(), $module);
} else {
$headerLabel = getTranslatedString(str_replace('_', " ", $fieldLabel), $module);
}
/*STRING TRANSLATION starts */
$moduleLabel = '';
if (in_array($module, $modules_selected)) {
$moduleLabel = getTranslatedString($module, $module);
}
if (empty($headerLabel)) {
$headerLabel = getTranslatedString(str_replace('_', " ", $fld->name));
}
if (!empty($this->secondarymodule)) {
if ($moduleLabel != '') {
$headerLabel = $moduleLabel . " " . $headerLabel;
}
}
$header .= "<th>" . $headerLabel . "</th>";
}
$valtemplate = '';
$lastvalue = '';
$secondvalue = '';
$thirdvalue = '';
do {
$arraylists = array();
$newvalue = '';
$snewvalue = '';
$tnewvalue = '';
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 - 1; $i++) {
$fld = $adb->field_name($result, $i);
$fld_type = $column_definitions[$i]->type;
$fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i);
if ($lastvalue == $fieldvalue && $this->reporttype == "summary") {
if ($this->reporttype == "summary") {
$valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>";
} else {
$valtemplate .= "<td>" . $fieldvalue . "</td>";
}
} else {
if ($secondvalue == $fieldvalue && $this->reporttype == "summary") {
if ($lastvalue == $newvalue) {
$valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>";
} else {
$valtemplate .= "<td>" . $fieldvalue . "</td>";
}
} else {
if ($thirdvalue == $fieldvalue && $this->reporttype == "summary") {
if ($secondvalue == $snewvalue) {
$valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </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, $filtersql, "COLUMNSTOTOTAL");
$coltotalhtml = '';
if (isset($this->totallist) and count($this->totallist) > 0) {
if ($sSQL != "") {
$result = $adb->query($sSQL);
$y = $adb->num_fields($result);
$custom_field_values = $adb->fetch_array($result);
$coltotalhtml = "<br /><table align='center' width='60%' cellpadding='3' cellspacing='0' border='1' class='printReport'><tr><td class='rptCellLabel'>" . $mod_strings['Totals'] . "</td><td><b>" . $mod_strings['SUM'] . "</b></td><td><b>" . $mod_strings['AVG'] . "</b></td><td><b>" . $mod_strings['MIN'] . "</b></td><td><b>" . $mod_strings['MAX'] . "</b></td></tr>";
// Performation Optimization: If Direct output is desired
if ($directOutput) {
echo $coltotalhtml;
$coltotalhtml = '';
}
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 = getTabModuleName($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, $module_name) . " " . getTranslatedString($fieldlabel, $module_name);
} else {
$field = 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">';
$col_header = getTranslatedString(trim(str_replace($modules, " ", $value)));
$fld_name_1 = $this->primarymodule . "_" . trim($value);
$fld_name_2 = $this->secondarymodule . "_" . trim($value);
if ($uitype_arr[$key] == 71 || $uitype_arr[$key] == 72 || in_array($fld_name_1, $this->append_currency_symbol_to_value) || 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 = decode_html(trim($key));
$arraykey = $value . '_SUM';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </td>";
}
$arraykey = $value . '_AVG';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </td>";
}
$arraykey = $value . '_MIN';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </td>";
}
$arraykey = $value . '_MAX';
if (isset($keyhdr[$arraykey])) {
if ($convert_price) {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]);
} else {
$conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true);
}
$coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>';
} else {
$coltotalhtml .= "<td class='rptTotal'> </td>";
}
$coltotalhtml .= '</tr>';
// Performation Optimization: If Direct output is desired
if ($directOutput) {
echo $coltotalhtml;
$coltotalhtml = '';
}
}
$coltotalhtml .= "</table>";
// Performation Optimization: If Direct output is desired
if ($directOutput) {
echo $coltotalhtml;
$coltotalhtml = '';
}
}
}
return $coltotalhtml;
}
}