function getAdvancedFilterList($reportid) { global $adb; global $modules; global $log; global $current_user; $advft_criteria = array(); $sql = 'SELECT * FROM vtiger_relcriteria_grouping WHERE queryid = ? ORDER BY groupid'; $groupsresult = $adb->pquery($sql, array($reportid)); $i = 1; $j = 0; while ($relcriteriagroup = $adb->fetch_array($groupsresult)) { $groupId = $relcriteriagroup["groupid"]; $groupCondition = $relcriteriagroup["group_condition"]; $ssql = 'select vtiger_relcriteria.* from vtiger_report inner join vtiger_relcriteria on vtiger_relcriteria.queryid = vtiger_report.queryid left join vtiger_relcriteria_grouping on vtiger_relcriteria.queryid = vtiger_relcriteria_grouping.queryid and vtiger_relcriteria.groupid = vtiger_relcriteria_grouping.groupid'; $ssql .= " where vtiger_report.reportid = ? AND vtiger_relcriteria.groupid = ? order by vtiger_relcriteria.columnindex"; $result = $adb->pquery($ssql, array($reportid, $groupId)); $noOfColumns = $adb->num_rows($result); if ($noOfColumns <= 0) { continue; } while ($relcriteriarow = $adb->fetch_array($result)) { $columnIndex = $relcriteriarow["columnindex"]; $criteria = array(); $criteria['columnname'] = $relcriteriarow["columnname"]; $criteria['comparator'] = $relcriteriarow["comparator"]; $advfilterval = $relcriteriarow["value"]; $col = explode(":", $relcriteriarow["columnname"]); $moduleFieldLabel = $col[2]; $fieldName = $col[3]; list($module, $fieldLabel) = explode('__', $moduleFieldLabel, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($adb, $fieldInfo); $fieldType = $field->getFieldDataType(); } if ($fieldType == 'currency') { if ($field->getUIType() == '71') { $advfilterval = CurrencyField::convertToUserFormat($advfilterval, $current_user); } else { if ($field->getUIType() == '72') { $advfilterval = CurrencyField::convertToUserFormat($advfilterval, $current_user, true); } } } $temp_val = explode(",", $relcriteriarow["value"]); if ($col[4] == 'D' || $col[4] == 'T' && $col[1] != 'time_start' && $col[1] != 'time_end' || $col[4] == 'DT') { $val = array(); for ($x = 0; $x < count($temp_val); $x++) { if ($col[4] == 'D') { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDisplayDate(); } elseif ($col[4] == 'DT') { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDisplayDateTimeValue(); } else { $date = new DateTimeField(trim($temp_val[$x])); $val[$x] = $date->getDisplayTime(); } } $advfilterval = implode(",", $val); } //In vtiger6 report filter conditions, if the value has "(double quotes) then it is failed. $criteria['value'] = Vtiger_Util_Helper::toSafeHTML(decode_html($advfilterval)); $criteria['column_condition'] = $relcriteriarow["column_condition"]; $advft_criteria[$relcriteriarow['groupid']]['columns'][$j] = $criteria; $advft_criteria[$relcriteriarow['groupid']]['condition'] = $groupCondition; $j++; } $i++; } // Clear the condition (and/or) for last group, if any. if (!empty($advft_criteria[$i - 1]['condition'])) { $advft_criteria[$i - 1]['condition'] = ''; } $this->advft_criteria = $advft_criteria; $log->info("Reports :: Successfully returned getAdvancedFilterList"); return true; }
function getEscapedColumns($selectedfields) { $tableName = $selectedfields[0]; $columnName = $selectedfields[1]; $moduleFieldLabel = $selectedfields[2]; $fieldName = $selectedfields[3]; list($moduleName, $fieldLabel) = explode('_', $moduleFieldLabel, 2); $fieldInfo = getFieldByReportLabel($moduleName, $fieldLabel); $moduleFieldName = $moduleName . "_" . $fieldName; if ($moduleName == 'ModComments' && $fieldName == 'creator') { $concatSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_usersModComments.first_name', 'last_name' => 'vtiger_usersModComments.last_name'), 'Users'); $queryColumn = "trim(case when (vtiger_usersModComments.user_name not like '' and vtiger_crmentity.crmid!='') then {$concatSql} end) as 'ModComments_Creator'"; } elseif (($fieldInfo['uitype'] == '10' || isReferenceUIType($fieldInfo['uitype'])) && $fieldInfo['uitype'] != '52' && $fieldInfo['uitype'] != '53') { $fieldSqlColumns = $this->getReferenceFieldColumnList($moduleName, $fieldInfo); if (count($fieldSqlColumns) > 0) { $queryColumn = "(CASE WHEN {$tableName}.{$columnName} NOT LIKE '' THEN (CASE"; foreach ($fieldSqlColumns as $columnSql) { $queryColumn .= " WHEN {$columnSql} NOT LIKE '' THEN {$columnSql}"; } $queryColumn .= " ELSE '' END) ELSE '' END) AS {$moduleFieldName}"; $this->queryPlanner->addTable($tableName); } } return $queryColumn; }
/** Function to convert the Report Header Names into i18n * @param $fldname: Type Varchar * Returns Language Converted Header Strings **/ function getLstringforReportHeaders($fldname) { global $modules, $current_user, $app_strings; $rep_header = ltrim($fldname); $rep_header = decode_html($rep_header); $labelInfo = explode('__', $rep_header); $rep_module = $labelInfo[0]; if (is_array($this->labelMapping) && !empty($this->labelMapping[$rep_header])) { $rep_header = $this->labelMapping[$rep_header]; } else { if ($rep_module == 'LBL') { $rep_module = ''; } array_shift($labelInfo); $fieldLabel = decode_html(implode("__", $labelInfo)); $rep_header_temp = preg_replace("/\\s+/", "__", $fieldLabel); $rep_header = "{$rep_module} {$fieldLabel}"; } $curr_symb = ""; $fieldLabel = ltrim(str_replace($rep_module, '', $rep_header), '__'); $fieldInfo = getFieldByReportLabel($rep_module, $fieldLabel); if ($fieldInfo['uitype'] == '71') { $curr_symb = " (" . $app_strings['LBL_IN'] . " " . $current_user->currency_symbol . ")"; } $rep_header .= $curr_symb; return $rep_header; }
/** * * @global Users $current_user * @param ReportRun $report * @param Array $picklistArray * @param ADOFieldObject $dbField * @param Array $valueArray * @param String $fieldName * @return String */ function getReportFieldValue($report, $picklistArray, $dbField, $valueArray, $fieldName) { global $current_user, $default_charset; $db = PearDatabase::getInstance(); $value = $valueArray[$fieldName]; $fld_type = $dbField->type; list($module, $fieldLabel) = explode('__', $dbField->name, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; $fieldvalue = $value; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($db, $fieldInfo); $fieldType = $field->getFieldDataType(); } if ($fieldType == 'currency' && $value != '') { // Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion if ($field->getUIType() == '72') { $curid_value = explode("::", $value); $currency_id = $curid_value[0]; $currency_value = $curid_value[1]; $cur_sym_rate = getCurrencySymbolandCRate($currency_id); if ($value != '') { if ($dbField->name == 'Products_Unit_Price') { // need to do this only for Products Unit Price if ($currency_id != 1) { $currency_value = (double) $cur_sym_rate['rate'] * (double) $currency_value; } } $formattedCurrencyValue = CurrencyField::convertToUserFormat($currency_value, null, true); $fieldvalue = CurrencyField::appendCurrencySymbol($formattedCurrencyValue, $cur_sym_rate['symbol']); } } else { $currencyField = new CurrencyField($value); $fieldvalue = $currencyField->getDisplayValue(); } } elseif ($dbField->name == "PriceBooks_Currency") { if ($value != '') { $fieldvalue = getTranslatedCurrencyString($value); } } elseif (in_array($dbField->name, $report->ui101_fields) && !empty($value)) { $entityNames = getEntityName('Users', $value); $fieldvalue = $entityNames[$value]; } elseif ($fieldType == 'date' && !empty($value)) { if ($module == 'Calendar' && $field->getFieldName() == 'due_date') { $endTime = $valueArray['calendar_end_time']; if (empty($endTime)) { $recordId = $valueArray['calendar_id']; $endTime = getSingleFieldValue('vtiger_activity', 'time_end', 'activityid', $recordId); } $date = new DateTimeField($value . ' ' . $endTime); $fieldvalue = $date->getDisplayDate(); } else { if (!($field->getUIType() == '5')) { $date = new DateTimeField($fieldvalue); $fieldvalue = $date->getDisplayDateTimeValue(); } } } elseif ($fieldType == "datetime" && !empty($value)) { $date = new DateTimeField($value); $fieldvalue = $date->getDisplayDateTimeValue(); } elseif ($fieldType == 'time' && !empty($value) && $field->getFieldName() != 'duration_hours') { if ($field->getFieldName() == "time_start" || $field->getFieldName() == "time_end") { $date = new DateTimeField($value); $fieldvalue = $date->getDisplayTime(); } else { $userModel = Users_Privileges_Model::getCurrentUserModel(); if ($userModel->get('hour_format') == '12') { $value = Vtiger_Time_UIType::getTimeValueInAMorPM($value); } $fieldvalue = $value; } } elseif ($fieldType == "picklist" && !empty($value)) { if (is_array($picklistArray)) { if (is_array($picklistArray[$dbField->name]) && $field->getFieldName() != 'activitytype' && !in_array($value, $picklistArray[$dbField->name])) { $fieldvalue = $app_strings['LBL_NOT_ACCESSIBLE']; } else { $fieldvalue = getTranslatedString($value, $module); } } else { $fieldvalue = getTranslatedString($value, $module); } } elseif ($fieldType == "multipicklist" && !empty($value)) { if (is_array($picklistArray[1])) { $valueList = explode(' |##| ', $value); $translatedValueList = array(); foreach ($valueList as $value) { if (is_array($picklistArray[1][$dbField->name]) && !in_array($value, $picklistArray[1][$dbField->name])) { $translatedValueList[] = $app_strings['LBL_NOT_ACCESSIBLE']; } else { $translatedValueList[] = getTranslatedString($value, $module); } } } if (!is_array($picklistArray[1]) || !is_array($picklistArray[1][$dbField->name])) { $fieldvalue = str_replace(' |##| ', ', ', $value); } else { implode(', ', $translatedValueList); } } elseif ($fieldType == 'double') { if ($current_user->truncate_trailing_zeros == true) { $fieldvalue = decimalFormat($fieldvalue); } } elseif ($fieldType == 'boolean') { if (strtolower($value) === 'yes' || strtolower($value) === 'on' || $value == 1) { $fieldvalue = vtranslate('LBL_YES'); } else { $fieldvalue = vtranslate('LBL_NO'); } } elseif ($field && $field->getUIType() == 117 && $value != '') { if ($value != '0') { $currencyList = Settings_Currency_Record_Model::getAll(); $fieldvalue = $currencyList[$value]->getName() . ' (' . $currencyList[$value]->get('currency_symbol') . ')'; } else { $fieldvalue = '-'; } } if ('vtiger_crmentity' == $dbField->table && false != strpos($dbField->name, 'Share__with__users')) { if ($value) { $listId = explode(',', $value); $usersSqlFullName = getSqlForNameInDisplayFormat(['first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'], 'Users'); $getListUserSql = "select {$usersSqlFullName} as uname from vtiger_users WHERE id IN (" . generateQuestionMarks($listId) . ') '; $getListUserResult = $db->pquery($getListUserSql, array($listId), TRUE); $fieldvalue = ''; $finalList = array(); $listUsers = $getListUserResult->GetAll(); for ($i = 0; $i < count($listUsers); $i++) { $finalList[] = $listUsers[$i][0]; } $fieldvalue = implode(', ', $finalList); } } if ($fieldvalue == "") { return "-"; } $fieldvalue = str_replace("<", "<", $fieldvalue); $fieldvalue = str_replace(">", ">", $fieldvalue); $fieldvalue = decode_html($fieldvalue); if (stristr($fieldvalue, "|##|") && empty($fieldType)) { $fieldvalue = str_ireplace(' |##| ', ', ', $fieldvalue); } elseif ($fld_type == "date" && empty($fieldType)) { $fieldvalue = DateTimeField::convertToUserFormat($fieldvalue); } elseif ($fld_type == "datetime" && empty($fieldType)) { $date = new DateTimeField($fieldvalue); $fieldvalue = $date->getDisplayDateTimeValue(); } // Added to render html tag for description fields if ($fieldInfo['uitype'] == '19' && ($module == 'Documents' || $module == 'Emails')) { return $fieldvalue; } return htmlentities($fieldvalue, ENT_QUOTES, $default_charset); }
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; } }
/** * Function saves Reports Filter information */ function saveAdvancedFilters() { $db = PearDatabase::getInstance(); $reportId = $this->getId(); $advancedFilter = $this->get('advancedFilter'); if (!empty($advancedFilter)) { $db->pquery('DELETE FROM vtiger_relcriteria WHERE queryid = ?', array($reportId)); $db->pquery('DELETE FROM vtiger_relcriteria_grouping WHERE queryid = ?', array($reportId)); foreach ($advancedFilter as $groupIndex => $groupInfo) { if (empty($groupInfo)) { continue; } $groupColumns = $groupInfo['columns']; $groupCondition = $groupInfo['condition']; foreach ($groupColumns as $columnIndex => $columnCondition) { if (empty($columnCondition)) { continue; } $advFilterColumn = $columnCondition["columnname"]; $advFilterComparator = $columnCondition["comparator"]; $advFilterValue = $columnCondition["value"]; $advFilterColumnCondition = $columnCondition["column_condition"]; $columnInfo = explode(":", $advFilterColumn); $moduleFieldLabel = $columnInfo[2]; list($module, $fieldLabel) = explode('__', $moduleFieldLabel, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($db, $fieldInfo); $fieldType = $field->getFieldDataType(); } if ($fieldType == 'currency') { if ($field->getUIType() == '72') { // Some of the currency fields like Unit Price, Totoal , Sub-total - doesn't need currency conversion during save $advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue, null, true); } else { $advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue); } } $tempVal = explode(",", $advFilterValue); if (($columnInfo[4] == 'D' || $columnInfo[4] == 'T' && $columnInfo[1] != 'time_start' && $columnInfo[1] != 'time_end' || $columnInfo[4] == 'DT') && ($columnInfo[4] != '' && $advFilterValue != '')) { $val = array(); for ($i = 0; $i < count($tempVal); $i++) { if (trim($tempVal[$i]) != '') { $date = new DateTimeField(trim($tempVal[$i])); if ($columnInfo[4] == 'D') { $val[$i] = DateTimeField::convertToDBFormat(trim($tempVal[$i])); } elseif ($columnInfo[4] == 'DT') { /** * While generating query to retrieve report, for date time fields we are only taking * date field and appending '00:00:00' for correct results depending on time zone. * If you save the time also here by converting to db format, while showing in edit * view it was changing the date selected. */ $values = explode(' ', $tempVal[$i]); $date = new DateTimeField($values[0]); $val[$i] = $date->getDBInsertDateValue(); } else { $val[$i] = $date->getDBInsertTimeValue(); } } } $advFilterValue = implode(",", $val); } $db->pquery('INSERT INTO vtiger_relcriteria (queryid, columnindex, columnname, comparator, value, groupid, column_condition) VALUES (?,?,?,?,?,?,?)', array($reportId, $columnIndex, $advFilterColumn, $advFilterComparator, $advFilterValue, $groupIndex, $advFilterColumnCondition)); // Update the condition expression for the group to which the condition column belongs $groupConditionExpression = ''; if (!empty($advancedFilter[$groupIndex]["conditionexpression"])) { $groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"]; } $groupConditionExpression = $groupConditionExpression . ' ' . $columnIndex . ' ' . $advFilterColumnCondition; $advancedFilter[$groupIndex]["conditionexpression"] = $groupConditionExpression; } $groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"]; if (empty($groupConditionExpression)) { continue; } // Case when the group doesn't have any column criteria $db->pquery("INSERT INTO vtiger_relcriteria_grouping(groupid, queryid, group_condition, condition_expression) VALUES (?,?,?,?)", array($groupIndex, $reportId, $groupCondition, $groupConditionExpression)); } } }
/** * * @global Users $current_user * @param ReportRun $report * @param Array $picklistArray * @param ADOFieldObject $dbField * @param Array $valueArray * @param String $fieldName * @return String */ function getReportFieldValue($report, $picklistArray, $dbField, $valueArray, $fieldName) { global $current_user, $default_charset; $db = PearDatabase::getInstance(); $value = $valueArray[$fieldName]; $fld_type = $dbField->type; list($module, $fieldLabel) = explode('_', $dbField->name, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; $fieldvalue = $value; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($db, $fieldInfo); $fieldType = $field->getFieldDataType(); } if ($fieldType == 'currency' && $value != '') { // Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion if ($field->getUIType() == '72') { $curid_value = explode("::", $value); $currency_id = $curid_value[0]; $currency_value = $curid_value[1]; $cur_sym_rate = getCurrencySymbolandCRate($currency_id); if ($value != '') { if ($dbField->name == 'Products_Unit_Price') { // need to do this only for Products Unit Price if ($currency_id != 1) { $currency_value = (double) $cur_sym_rate['rate'] * (double) $currency_value; } } $formattedCurrencyValue = CurrencyField::convertToUserFormat($currency_value, null, true); $fieldvalue = CurrencyField::appendCurrencySymbol($formattedCurrencyValue, $cur_sym_rate['symbol']); } } else { $currencyField = new CurrencyField($value); $fieldvalue = $currencyField->getDisplayValue(); } } elseif ($dbField->name == "PurchaseOrder_Currency" || $dbField->name == "SalesOrder_Currency" || $dbField->name == "Invoice_Currency" || $dbField->name == "Quotes_Currency" || $dbField->name == "PriceBooks_Currency") { if ($value != '') { $fieldvalue = getTranslatedCurrencyString($value); } } elseif (in_array($dbField->name, $report->ui101_fields) && !empty($value)) { $entityNames = getEntityName('Users', $value); $fieldvalue = $entityNames[$value]; } elseif ($fieldType == 'date' && !empty($value)) { if ($module == 'Calendar' && $field->getFieldName() == 'due_date') { $endTime = $valueArray['calendar_end_time']; if (empty($endTime)) { $recordId = $valueArray['calendar_id']; $endTime = getSingleFieldValue('vtiger_activity', 'time_end', 'activityid', $recordId); } $date = new DateTimeField($value . ' ' . $endTime); $fieldvalue = $date->getDisplayDate(); } else { $fieldvalue = DateTimeField::convertToUserFormat($value); } } elseif ($fieldType == "datetime" && !empty($value)) { $date = new DateTimeField($value); $fieldvalue = $date->getDisplayDateTimeValue(); } elseif ($fieldType == 'time' && !empty($value) && $field->getFieldName() != 'duration_hours') { if ($field->getFieldName() == "time_start" || $field->getFieldName() == "time_end") { $date = new DateTimeField($value); $fieldvalue = $date->getDisplayTime(); } else { $fieldvalue = $value; } } elseif ($fieldType == "picklist" && !empty($value)) { if (is_array($picklistArray)) { if (is_array($picklistArray[$dbField->name]) && $field->getFieldName() != 'activitytype' && !in_array($value, $picklistArray[$dbField->name])) { $fieldvalue = $app_strings['LBL_NOT_ACCESSIBLE']; } else { $fieldvalue = getTranslatedString($value, $module); } } else { $fieldvalue = getTranslatedString($value, $module); } } elseif ($fieldType == "multipicklist" && !empty($value)) { if (is_array($picklistArray[1])) { $valueList = explode(' |##| ', $value); $translatedValueList = array(); foreach ($valueList as $value) { if (is_array($picklistArray[1][$dbField->name]) && !in_array($value, $picklistArray[1][$dbField->name])) { $translatedValueList[] = $app_strings['LBL_NOT_ACCESSIBLE']; } else { $translatedValueList[] = getTranslatedString($value, $module); } } } if (!is_array($picklistArray[1]) || !is_array($picklistArray[1][$dbField->name])) { $fieldvalue = str_replace(' |##| ', ', ', $value); } else { implode(', ', $translatedValueList); } } elseif ($fieldType == 'double') { if ($current_user->truncate_trailing_zeros == true) { $fieldvalue = decimalFormat($fieldvalue); } } if ($fieldvalue == "") { return "-"; } $fieldvalue = str_replace("<", "<", $fieldvalue); $fieldvalue = str_replace(">", ">", $fieldvalue); $fieldvalue = decode_html($fieldvalue); if (stristr($fieldvalue, "|##|") && empty($fieldType)) { $fieldvalue = str_ireplace(' |##| ', ', ', $fieldvalue); } elseif ($fld_type == "date" && empty($fieldType)) { $fieldvalue = DateTimeField::convertToUserFormat($fieldvalue); } elseif ($fld_type == "datetime" && empty($fieldType)) { $date = new DateTimeField($fieldvalue); $fieldvalue = $date->getDisplayDateTimeValue(); } // Added to render html tag for description fields if ($fieldInfo['uitype'] == '19' && ($module == 'Documents' || $module == 'Emails')) { return $fieldvalue; } return htmlentities($fieldvalue, ENT_QUOTES, $default_charset); }
$idelrelcriteriagroupsql = "delete from vtiger_relcriteria_grouping where queryid=?"; $idelrelcriteriagroupsqlresult = $adb->pquery($idelrelcriteriagroupsql, array($reportid)); foreach ($advft_criteria as $column_index => $column_condition) { if (empty($column_condition)) { continue; } $adv_filter_column = $column_condition["columnname"]; $adv_filter_comparator = $column_condition["comparator"]; $adv_filter_value = $column_condition["value"]; $adv_filter_column_condition = $column_condition["columncondition"]; $adv_filter_groupid = $column_condition["groupid"]; $column_info = explode(":", $adv_filter_column); $moduleFieldLabel = $column_info[2]; $fieldName = $column_info[3]; list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($adb, $fieldInfo); $fieldType = $field->getFieldDataType(); } if ($fieldType == 'currency' or $fieldType == 'double') { $flduitype = $fieldInfo['uitype']; if ($flduitype == '72' or $flduitype == 9 or $flduitype == 7) { $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value, null, true); } else { $adv_filter_value = CurrencyField::convertToDBFormat($adv_filter_value); } } $temp_val = explode(",", $adv_filter_value); if (($column_info[4] == 'D' || $column_info[4] == 'T' && $column_info[1] != 'time_start' && $column_info[1] != 'time_end' || $column_info[4] == 'DT') && ($column_info[4] != '' && $adv_filter_value != '')) {
/** * Function saves Reports Filter information */ function saveAdvancedFilters() { $db = PearDatabase::getInstance(); $reportId = $this->getId(); $advancedFilter = $this->get('advancedFilter'); if (!empty($advancedFilter)) { $db->pquery('DELETE FROM vtiger_relcriteria WHERE queryid = ?', array($reportId)); $db->pquery('DELETE FROM vtiger_relcriteria_grouping WHERE queryid = ?', array($reportId)); foreach ($advancedFilter as $groupIndex => $groupInfo) { if (empty($groupInfo)) { continue; } $groupColumns = $groupInfo['columns']; $groupCondition = $groupInfo['condition']; foreach ($groupColumns as $columnIndex => $columnCondition) { if (empty($columnCondition)) { continue; } $advFilterColumn = $columnCondition["columnname"]; $advFilterComparator = $columnCondition["comparator"]; $advFilterValue = $columnCondition["value"]; $advFilterColumnCondition = $columnCondition["column_condition"]; $columnInfo = explode(":", $advFilterColumn); $moduleFieldLabel = $columnInfo[2]; list($module, $fieldLabel) = explode('_', $moduleFieldLabel, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($db, $fieldInfo); $fieldType = $field->getFieldDataType(); } if ($fieldType == 'currency') { if ($field->getUIType() == '71') { $advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue, null, true); } else { $advFilterValue = Vtiger_Currency_UIType::convertToDBFormat($advFilterValue); } } $tempVal = explode(",", $advFilterValue); if (($columnInfo[4] == 'D' || $columnInfo[4] == 'T' && $columnInfo[1] != 'time_start' && $columnInfo[1] != 'time_end' || $columnInfo[4] == 'DT') && ($columnInfo[4] != '' && $advFilterValue != '')) { $val = array(); for ($i = 0; $i < count($tempVal); $i++) { if (trim($tempVal[$i]) != '') { $date = new DateTimeField(trim($tempVal[$i])); if ($columnInfo[4] == 'D') { $val[$i] = DateTimeField::convertToDBFormat(trim($tempVal[$i])); } elseif ($columnInfo[4] == 'DT') { $val[$i] = $date->getDBInsertDateTimeValue(); } else { $val[$i] = $date->getDBInsertTimeValue(); } } } $advFilterValue = implode(",", $val); } $db->pquery('INSERT INTO vtiger_relcriteria (queryid, columnindex, columnname, comparator, value, groupid, column_condition) VALUES (?,?,?,?,?,?,?)', array($reportId, $columnIndex, $advFilterColumn, $advFilterComparator, $advFilterValue, $groupIndex, $advFilterColumnCondition)); // Update the condition expression for the group to which the condition column belongs $groupConditionExpression = ''; if (!empty($advancedFilter[$groupIndex]["conditionexpression"])) { $groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"]; } $groupConditionExpression = $groupConditionExpression . ' ' . $columnIndex . ' ' . $advFilterColumnCondition; $advancedFilter[$groupIndex]["conditionexpression"] = $groupConditionExpression; } $groupConditionExpression = $advancedFilter[$groupIndex]["conditionexpression"]; if (empty($groupConditionExpression)) { continue; } // Case when the group doesn't have any column criteria $db->pquery("INSERT INTO vtiger_relcriteria_grouping(groupid, queryid, group_condition, condition_expression) VALUES (?,?,?,?)", array($groupIndex, $reportId, $groupCondition, $groupConditionExpression)); } } }
function getReport(Vtiger_Request $request) { $viewer = $this->getViewer($request); $moduleName = $request->getModule(); $record = $request->get('record'); $page = $request->get('page'); //$data = $this->reportData; //TODO $calculation = $this->calculationFields; //if(empty($data)){ $reportModel = Reports_Record_Model::getInstanceById($record); $reportModel->setModule('Reports'); $reportType = $reportModel->get('reporttype'); $pagingModel = new Vtiger_Paging_Model(); $pagingModel->set('page', $page); $pagingModel->set('limit', self::REPORT_LIMIT + 1); $reportData = $reportModel->getReportData($pagingModel); $data = $reportData['data']; $dataRaw = $reportData['data_raw']; $calculation = $reportModel->getReportCalulationData(); $advFilterSql = $reportModel->getAdvancedFilterSQL(); $query = $reportModel->getReportSQL($advFilterSql, 'PDF'); $countQuery = $reportModel->generateCountQuery($query); $this->count = $reportModel->getReportsCount($countQuery); //} /*готовим данные в нужный нам формат для новой таблицы*/ /*получим поле для группировки*/ global $adb; $reportRun = ReportRun::getInstance($record); $fieldRawArr = $reportRun->getGroupingList(); foreach ($fieldRawArr as $value) { $fieldRaw = explode('_', $value, 2); break; } $groupModule = str_replace('`', '', $fieldRaw[0]); $groupLabel = explode(' ', $fieldRaw[1], 2); $groupLabel = str_replace('`', '', $groupLabel[0]); //это системное имя, нужен перевод $fieldInfo = getFieldByReportLabel($groupModule, $groupLabel); $fieldType = null; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($adb, $fieldInfo); $fieldType = $field->getFieldDataType(); } if (!empty($fieldInfo)) { $translatedLabel = getTranslatedString($field->getFieldLabelKey(), $groupModule); } else { $translatedLabel = getTranslatedString($groupLabel, $groupModule); $translatedLabel = str_replace("_", " ", $translatedLabel); } $groupLabel = $translatedLabel; /*STRING TRANSLATION starts TODO*/ /*$moduleLabel =''; if(in_array($module,$modules_selected)) $moduleLabel = getTranslatedString($module,$module); if(empty($translatedLabel)) { $translatedLabel = getTranslatedString(str_replace('_', " ", $fld->name), $module); }*/ /*if(!empty($this->secondarymodule)) { if($moduleLabel != '') { $headerLabel = $moduleLabel." ". $translatedLabel; } }*/ /*формируем нужный массив*/ $dataGrouped = array(); foreach ($data as $index => $row) { $groupIndex = $row[$groupLabel]; if (!isset($dataGrouped[$groupIndex])) { $dataGrouped[$groupIndex] = array('rows' => array(), 'summary' => array()); } $dataGrouped[$groupIndex]['rows'][] = $row; foreach ($row as $reportField => $value) { $dataRawVal = explode('::', $dataRaw[$index][$reportField]); $dataRawType = empty($dataRawVal[1]) ? 'undefined' : $dataRawVal[1]; $dataRawVal = $dataRawVal[0]; if (!isset($dataGrouped[$groupIndex]['summary'][$reportField])) { if ($dataRawType == 'currency') { $dataRawVal = $dataRawVal + 0; } if (empty($dataRawType)) { $dataRawVal = (string) $dataRawVal; } $dataGrouped[$groupIndex]['summary'][$reportField] = $dataRawVal; } elseif ($dataRawType == 'currency') { /*можно позже добавить другие типы данных*/ $summed = $dataGrouped[$groupIndex]['summary'][$reportField] + $dataRawVal; $dataGrouped[$groupIndex]['summary'][$reportField] = $summed; /*todo переделать в пользовательский формат*/ } } } /*end*/ /*убираем ненужные столбцы*/ $columns = array('SUM' => false, 'AVG' => false, 'MIN' => false, 'MAX' => false); foreach ($calculation as $metriks) { foreach ($metriks as $index => $metrik) { $postfix = substr($index, -3, 3); if ($columns[$postfix]) { continue; } if (!empty($metrik)) { $columns[$postfix] = true; } } } $viewer->assign('COLUMNS', $columns); /*end*/ $viewer->assign('CALCULATION_FIELDS', $calculation); $viewer->assign('DATA', $data); $viewer->assign('DATAGROUPED', $dataGrouped); $viewer->assign('RECORD_ID', $record); $viewer->assign('PAGING_MODEL', $pagingModel); $viewer->assign('COUNT', $this->count); $viewer->assign('MODULE', $moduleName); $viewer->assign('REPORT_RUN_INSTANCE', ReportRun::getInstance($record)); if (count($data) > self::REPORT_LIMIT) { $viewer->assign('LIMIT_EXCEEDED', true); } $viewer->view('ReportContents.tpl', $moduleName); }