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);
}
