protected function getTableFieldList($tableName) { $tableFieldList = array(); $factory = WebserviceField::fromArray($this->pearDB, array('tablename' => $tableName)); $dbTableFields = $factory->getTableFields(); foreach ($dbTableFields as $dbField) { if ($dbField->primaryKey) { if ($this->idColumn === null) { $this->idColumn = $dbField->name; } else { throw new WebServiceException(WebServiceErrorCode::$UNKOWNENTITY, "Entity table with multi column primary key is not supported"); } } $field = $this->getFieldArrayFromDBField($dbField, $tableName); $webserviceField = WebserviceField::fromArray($this->pearDB, $field); $fieldDataType = $this->getFieldType($dbField, $tableName); if ($fieldDataType === null) { $fieldDataType = $this->getFieldDataTypeFromDBType($dbField->type); } $webserviceField->setFieldDataType($fieldDataType); if (strcasecmp($fieldDataType, 'reference') === 0) { $webserviceField->setReferenceList($this->getReferenceList($dbField, $tableName)); } array_push($tableFieldList, $webserviceField); } return $tableFieldList; }
function retrieveMetaForTables() { $db = PearDatabase::getInstance(); foreach ($this->metaTableList as $tableName) { $sql = "SELECT * FROM {$tableName} WHERE deleted=0"; $params = array(); $result = $db->pquery($sql, $params); if (!empty($result)) { $it = new SqlResultIterator($db, $result); foreach ($it as $row) { $fieldArray = $this->getFieldArrayFromTaxRow($row, $tableName, $this->metaTablePrefix[$tableName]); $webserviceField = WebserviceField::fromArray($db, $fieldArray); $webserviceField->setDefault($row->percentage); $this->moduleFields[$webserviceField->getFieldName()] = $webserviceField; } } } }
protected function getTableFieldList($tableName) { $tableFieldList = array(); $factory = WebserviceField::fromArray($this->pearDB, array('tablename' => $tableName)); $dbTableFields = $factory->getTableFields(); foreach ($dbTableFields as $dbField) { if ($dbField->primaryKey) { if ($this->idColumn === null) { $this->idColumn = $dbField->name; } else { throw new WebServiceException(WebServiceErrorCode::$UNKOWNENTITY, "Entity table with multi column primary key is not supported"); } } $field = $this->getFieldArrayFromDBField($dbField, $tableName); if (preg_match('/tax\\d+/', $dbField->name) != 0) { $taxLabel = $this->getTaxLabelFromName($dbField->name); if (!empty($taxLabel)) { $field['fieldlabel'] = $taxLabel; } } $webserviceField = WebserviceField::fromArray($this->pearDB, $field); $fieldDataType = $this->getFieldType($dbField, $tableName); if ($fieldDataType === null) { $fieldDataType = $this->getFieldDataTypeFromDBType($dbField->type); } $webserviceField->setFieldDataType($fieldDataType); if (strcasecmp($fieldDataType, 'reference') === 0) { if ($webserviceField->getFieldName() == 'parent_id') { $webserviceField->setReferenceList(getInventoryModules()); } else { $webserviceField->setReferenceList(array('Products', 'Services')); } } array_push($tableFieldList, $webserviceField); } return $tableFieldList; }
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 != '')) { $val = array(); for ($x = 0; $x < count($temp_val); $x++) { if (trim($temp_val[$x]) != '') {
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); $inEqualityFieldTypes = ['currency', 'percentage', 'double', 'integer', 'number']; if (is_string($value) && $this->ignoreComma == false) { $commaSeparatedFieldTypes = array('picklist', 'multipicklist', 'owner', 'date', 'datetime', 'time', 'tree'); if (in_array($field->getFieldDataType(), $commaSeparatedFieldTypes)) { $valueArray = explode(',', $value); if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) { $valueArray = getCombinations($valueArray); foreach ($valueArray as $key => $value) { $valueArray[$key] = ltrim($value, ' |##| '); } } } else { $valueArray = array($value); } } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } $sql = array(); if ($operator == 'between' || $operator == 'bw' || $operator == 'notequal') { if ($field->getFieldName() == 'birthday') { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')"; } else { if ($this->isDateType($field->getFieldDataType())) { $start = explode(' ', $valueArray[0]); $end = explode(' ', $valueArray[1]); if ($operator == 'between' && count($start) == 2 && count($end) == 2) { $valueArray[0] = getValidDBInsertDateTimeValue($start[0] . ' ' . $start[1]); if ($start[0] == $end[0]) { $dateTime = new DateTime($valueArray[0]); $nextDay = $dateTime->modify('+1 days'); $nextDay = strtotime($nextDay->format('Y-m-d H:i:s')) - 1; $nextDay = date('Y-m-d H:i:s', $nextDay); $values = explode(' ', $nextDay); $valueArray[1] = getValidDBInsertDateTimeValue($values[0]) . ' ' . $values[1]; } else { $end = $valueArray[1]; $dateObject = new DateTimeField($end); $valueArray[1] = $dateObject->getDBInsertDateTimeValue(); } } else { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $dateTimeStart = explode(' ', $valueArray[0]); if ($dateTimeStart[1] == '00:00:00' && $operator != 'between') { $valueArray[0] = $dateTimeStart[0]; } $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $dateTimeEnd = explode(' ', $valueArray[1]); if ($dateTimeEnd[1] == '00:00:00' || $dateTimeEnd[1] == '23:59:59') { $valueArray[1] = $dateTimeEnd[0]; } } } if ($operator == 'notequal') { $sql[] = "NOT BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } else { $sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } } return $sql; } foreach ($valueArray as $value) { if (!$this->isStringType($field->getFieldDataType())) { $value = trim($value); } if ($operator == 'empty' || $operator == 'y') { $sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName())); continue; } if ($operator == 'ny') { $sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName())); continue; } if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) { if ($operator == 'e') { $sql[] = "IS NULL"; continue; } $sql[] = "IS NOT NULL"; continue; } elseif ($field->getFieldDataType() == 'boolean') { $value = strtolower($value); if ($value == 'yes') { $value = 1; } elseif ($value == 'no') { $value = 0; } } elseif ($this->isDateType($field->getFieldDataType())) { // For "after" and "before" conditions $values = explode(' ', $value); if (($operator == 'a' || $operator == 'b') && count($values) == 2) { if ($operator == 'a') { // for after comparator we should check the date after the given $dateTime = new DateTime($value); $modifiedDate = $dateTime->modify('+1 days'); $nextday = $modifiedDate->format('Y-m-d H:i:s'); $temp = strtotime($nextday) - 1; $date = date('Y-m-d H:i:s', $temp); $value = getValidDBInsertDateTimeValue($date); } else { $dateTime = new DateTime($value); $prevday = $dateTime->format('Y-m-d H:i:s'); $temp = strtotime($prevday) - 1; $date = date('Y-m-d H:i:s', $temp); $value = getValidDBInsertDateTimeValue($date); } } else { $value = getValidDBInsertDateTimeValue($value); $dateTime = explode(' ', $value); if ($dateTime[1] == '00:00:00') { $value = $dateTime[0]; } } } else { if (in_array($field->getFieldDataType(), $inEqualityFieldTypes)) { if ($operator == 'g' || $operator == 'l') { $value = substr($value, 4); } else { if ($operator == 'h' || $operator == 'm') { $value = substr($value, 5); } } } else { if ($field->getFieldDataType() === 'currency') { $uiType = $field->getUIType(); if ($uiType == 72) { $value = CurrencyField::convertToDBFormat($value, null, true); } elseif ($uiType == 71) { $value = CurrencyField::convertToDBFormat($value); } } } } if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) { $value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')"; } else { $value = $db->sql_escape_string($value); } if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) { $sql[] = "LIKE ''"; continue; } if (trim($value) == '' && $operator == 'om' && in_array($field->getFieldName(), $this->ownerFields)) { $sql[] = " = '" . Users_Record_Model::getCurrentUserModel()->get('id') . "'"; continue; } if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) { $sql[] = "NOT LIKE ''"; continue; } switch ($operator) { case 'e': $sqlOperator = "="; break; case 'n': $sqlOperator = "<>"; break; case 's': $sqlOperator = "LIKE"; $value = "{$value}%"; break; case 'ew': $sqlOperator = "LIKE"; $value = "%{$value}"; break; case 'c': $sqlOperator = "LIKE"; $value = "%{$value}%"; break; case 'k': $sqlOperator = "NOT LIKE"; $value = "%{$value}%"; break; case 'l': $sqlOperator = "<"; break; case 'g': $sqlOperator = ">"; break; case 'm': $sqlOperator = "<="; break; case 'h': $sqlOperator = ">="; break; case 'a': $sqlOperator = ">"; break; case 'b': $sqlOperator = "<"; break; } if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) { $value = "'{$value}'"; } if ($this->isNumericType($field->getFieldDataType()) && empty($value)) { $value = '0'; } $sql[] = "{$sqlOperator} {$value}"; } return $sql; }
function getReferenceFieldColumnList($moduleName, $fieldInfo) { $adb = PearDatabase::getInstance(); $columnsSqlList = array(); $fieldInstance = WebserviceField::fromArray($adb, $fieldInfo); $referenceModuleList = $fieldInstance->getReferenceList(); $reportSecondaryModules = explode(':', $this->secondarymodule); if ($moduleName != $this->primarymodule && in_array($this->primarymodule, $referenceModuleList)) { $entityTableFieldNames = getEntityFieldNames($this->primarymodule); $entityTableName = $entityTableFieldNames['tablename']; $entityFieldNames = $entityTableFieldNames['fieldname']; $columnList = array(); if (is_array($entityFieldNames)) { foreach ($entityFieldNames as $entityColumnName) { $columnList["{$entityColumnName}"] = "{$entityTableName}.{$entityColumnName}"; } } else { $columnList[] = "{$entityTableName}.{$entityFieldNames}"; } if (count($columnList) > 1) { $columnSql = getSqlForNameInDisplayFormat($columnList, $this->primarymodule); } else { $columnSql = implode('', $columnList); } $columnsSqlList[] = $columnSql; } else { foreach ($referenceModuleList as $referenceModule) { $entityTableFieldNames = getEntityFieldNames($referenceModule); $entityTableName = $entityTableFieldNames['tablename']; $entityFieldNames = $entityTableFieldNames['fieldname']; $referenceTableName = ''; $dependentTableName = ''; if ($moduleName == 'HelpDesk' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountRelHelpDesk'; } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsRelHelpDesk'; } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Products') { $referenceTableName = 'vtiger_productsRel'; } elseif ($moduleName == 'HelpDesk' && $referenceModule == 'Project') { $referenceTableName = 'vtiger_projectRelHelpDesk'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Leads') { $referenceTableName = 'vtiger_leaddetailsRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Potentials') { $referenceTableName = 'vtiger_potentialRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Invoice') { $referenceTableName = 'vtiger_invoiceRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Quotes') { $referenceTableName = 'vtiger_quotesRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'PurchaseOrder') { $referenceTableName = 'vtiger_purchaseorderRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'SalesOrder') { $referenceTableName = 'vtiger_salesorderRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'HelpDesk') { $referenceTableName = 'vtiger_troubleticketsRelCalendar'; } elseif ($moduleName == 'Calendar' && $referenceModule == 'Campaigns') { $referenceTableName = 'vtiger_campaignRelCalendar'; } elseif ($moduleName == 'Contacts' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountContacts'; } elseif ($moduleName == 'Contacts' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsContacts'; } elseif ($moduleName == 'Accounts' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountAccounts'; } elseif ($moduleName == 'Campaigns' && $referenceModule == 'Products') { $referenceTableName = 'vtiger_productsCampaigns'; } elseif ($moduleName == 'Faq' && $referenceModule == 'Products') { $referenceTableName = 'vtiger_productsFaq'; } elseif ($moduleName == 'Invoice' && $referenceModule == 'SalesOrder') { $referenceTableName = 'vtiger_salesorderInvoice'; } elseif ($moduleName == 'Invoice' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsInvoice'; } elseif ($moduleName == 'Invoice' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountInvoice'; } elseif ($moduleName == 'Potentials' && $referenceModule == 'Campaigns') { $referenceTableName = 'vtiger_campaignPotentials'; } elseif ($moduleName == 'Products' && $referenceModule == 'Vendors') { $referenceTableName = 'vtiger_vendorRelProducts'; } elseif ($moduleName == 'PurchaseOrder' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsPurchaseOrder'; } elseif ($moduleName == 'PurchaseOrder' && $referenceModule == 'Vendors') { $referenceTableName = 'vtiger_vendorRelPurchaseOrder'; } elseif ($moduleName == 'Quotes' && $referenceModule == 'Potentials') { $referenceTableName = 'vtiger_potentialRelQuotes'; } elseif ($moduleName == 'Quotes' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountQuotes'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Potentials') { $referenceTableName = 'vtiger_potentialRelSalesOrder'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountSalesOrder'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsSalesOrder'; } elseif ($moduleName == 'SalesOrder' && $referenceModule == 'Quotes') { $referenceTableName = 'vtiger_quotesSalesOrder'; } elseif ($moduleName == 'Potentials' && $referenceModule == 'Contacts') { $referenceTableName = 'vtiger_contactdetailsPotentials'; } elseif ($moduleName == 'Potentials' && $referenceModule == 'Accounts') { $referenceTableName = 'vtiger_accountPotentials'; } elseif ($moduleName == 'ModComments' && $referenceModule == 'Users') { $referenceTableName = 'vtiger_usersModComments'; } elseif (in_array($referenceModule, $reportSecondaryModules)) { $referenceTableName = "{$entityTableName}Rel{$referenceModule}"; $dependentTableName = "vtiger_crmentityRel{$referenceModule}{$fieldInstance->getFieldId()}"; } elseif (in_array($moduleName, $reportSecondaryModules)) { $referenceTableName = "{$entityTableName}Rel{$moduleName}"; $dependentTableName = "vtiger_crmentityRel{$moduleName}{$fieldInstance->getFieldId()}"; } else { $referenceTableName = "{$entityTableName}Rel{$moduleName}{$fieldInstance->getFieldId()}"; $dependentTableName = "vtiger_crmentityRel{$moduleName}{$fieldInstance->getFieldId()}"; $this->queryPlanner->addCustomTable(array('reference' => $referenceTableName, 'field' => $fieldInstance->getColumnName(), 'table' => $fieldInfo['tablename'], 'refTable' => $entityTableFieldNames['tablename'], 'refIndex' => $entityTableFieldNames['entityidfield'])); } $this->queryPlanner->addTable($referenceTableName); if (isset($dependentTableName)) { $this->queryPlanner->addTable($dependentTableName); } $columnList = array(); if (is_array($entityFieldNames)) { foreach ($entityFieldNames as $entityColumnName) { $columnList["{$entityColumnName}"] = "{$referenceTableName}.{$entityColumnName}"; } } else { $columnList[] = "{$referenceTableName}.{$entityFieldNames}"; } if (count($columnList) > 1) { $columnSql = getSqlForNameInDisplayFormat($columnList, $referenceModule); } else { $columnSql = implode('', $columnList); } /*if ($referenceModule == 'DocumentFolders' && $fieldInstance->getFieldName() == 'folderid') { $columnSql = 'vtiger_attachmentsfolder.foldername'; $this->queryPlanner->addTable("vtiger_attachmentsfolder"); }*/ if ($referenceModule == 'Currency' && $fieldInstance->getFieldName() == 'currency_id') { $columnSql = "vtiger_currency_info{$moduleName}.currency_name"; $this->queryPlanner->addTable("vtiger_currency_info{$moduleName}"); } $columnsSqlList[] = "trim({$columnSql})"; } } return $columnsSqlList; }
/** * * @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); }
public function getPDFMakerFieldValue($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 = $this->getFieldByPDFMakerLabel($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, $this->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 = $this->getTranslatedString($value, $module); } } else { $fieldvalue = $this->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[] = $this->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); }
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); }
/** * 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)); } } }
function initialize() { global $adb, $current_user; if ($this->moduleMeta === null) { $moduleHandler = vtws_getModuleHandlerFromName($this->parent->getModuleName(), $current_user); $this->moduleMeta = $moduleHandler->getMeta(); } if ($this->parent->getModuleName() == 'Products' and $this->parent->getFieldName() == 'imagename') { $sql = "select *, '0' as readonly from vtiger_field where vtiger_field.tabid=14 and fieldname='imagename'"; $result = $adb->pquery($sql, array()); $webserviceField = WebserviceField::fromQueryResult($adb, $result, 0); $this->fieldInfo = $webserviceField; } else { $moduleFields = $this->moduleMeta->getModuleFields(); $this->fieldInfo = $moduleFields[$this->parent->getFieldName()]; } }
/** * Function to get the Webservice Field Object for the current Field Object * @return WebserviceField instance */ public function getWebserviceFieldObject() { if ($this->webserviceField == false) { $db = PearDatabase::getInstance(); $row = array(); $row['uitype'] = $this->get('uitype'); $row['block'] = $this->get('block'); $row['tablename'] = $this->get('table'); $row['columnname'] = $this->get('column'); $row['fieldname'] = $this->get('name'); $row['fieldlabel'] = $this->get('label'); $row['displaytype'] = $this->get('displaytype'); $row['masseditable'] = $this->get('masseditable'); $row['typeofdata'] = $this->get('typeofdata'); $row['presence'] = $this->get('presence'); $row['tabid'] = $this->getModuleId(); $row['fieldid'] = $this->get('id'); $row['readonly'] = !$this->getProfileReadWritePermission(); $row['defaultvalue'] = $this->get('defaultvalue'); $this->webserviceField = WebserviceField::fromArray($db, $row); } return $this->webserviceField; }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); if (is_string($value) && $this->ignoreComma == false) { $valueArray = explode(',', $value); if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) { $valueArray = getCombinations($valueArray); foreach ($valueArray as $key => $value) { $valueArray[$key] = ltrim($value, ' |##| '); } } } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } $sql = array(); if ($operator == 'between' || $operator == 'bw' || $operator == 'notequal') { if ($field->getFieldName() == 'birthday') { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')"; } else { if ($this->isDateType($field->getFieldDataType())) { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $dateTimeStart = explode(' ', $valueArray[0]); if ($dateTimeStart[1] == '00:00:00' && $operator != 'between') { $valueArray[0] = $dateTimeStart[0]; } $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $dateTimeEnd = explode(' ', $valueArray[1]); if ($dateTimeEnd[1] == '00:00:00') { $valueArray[1] = $dateTimeEnd[0]; } } if ($operator == 'notequal') { $sql[] = "NOT BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } else { $sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } } return $sql; } foreach ($valueArray as $value) { if (!$this->isStringType($field->getFieldDataType())) { $value = trim($value); } if ($operator == 'empty' || $operator == 'y') { $sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName())); continue; } if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) { if ($operator == 'e') { $sql[] = "IS NULL"; continue; } $sql[] = "IS NOT NULL"; continue; } elseif ($field->getFieldDataType() == 'boolean') { $value = strtolower($value); if ($value == 'yes') { $value = 1; } elseif ($value == 'no') { $value = 0; } } elseif ($this->isDateType($field->getFieldDataType())) { $value = getValidDBInsertDateTimeValue($value); $dateTime = explode(' ', $value); if ($dateTime[1] == '00:00:00') { $value = $dateTime[0]; } } if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) { $value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')"; } else { $value = $db->sql_escape_string($value); } if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) { $sql[] = "LIKE ''"; continue; } if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) { $sql[] = "NOT LIKE ''"; continue; } switch ($operator) { case 'e': $sqlOperator = "="; break; case 'n': $sqlOperator = "<>"; break; case 's': $sqlOperator = "LIKE"; $value = "{$value}%"; break; case 'ew': $sqlOperator = "LIKE"; $value = "%{$value}"; break; case 'c': $sqlOperator = "LIKE"; $value = "%{$value}%"; break; case 'k': $sqlOperator = "NOT LIKE"; $value = "%{$value}%"; break; case 'l': $sqlOperator = "<"; break; case 'g': $sqlOperator = ">"; break; case 'm': $sqlOperator = "<="; break; case 'h': $sqlOperator = ">="; break; case 'a': $sqlOperator = ">"; break; case 'b': $sqlOperator = "<"; break; } if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) { $value = "'{$value}'"; } if ($this->isNumericType($field->getFieldDataType()) && empty($value)) { $value = '0'; } $sql[] = "{$sqlOperator} {$value}"; } return $sql; }
/** * 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)); } } }
/** * * @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; $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 != '') { $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)) { if (is_numeric($value)) { $entityNames = getEntityName('Users', $value); $fieldvalue = $entityNames[$value]; } else { $fieldvalue = $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' && $field->getFieldName() != 'totaltime') { $date = new DateTimeField($value); $fieldvalue = $date->getDisplayTime(); } 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); } } 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(); } return $fieldvalue; }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); $noncommaSeparatedFieldTypes = array('currency', 'percentage', 'double', 'integer', 'number'); if (in_array($field->getFieldDataType(), $noncommaSeparatedFieldTypes)) { if (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } // if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) { // $valueArray = getCombinations($valueArray); // foreach ($valueArray as $key => $value) { // $valueArray[$key] = ltrim($value, ' |##| '); // } // } } elseif (is_string($value)) { $valueArray = explode(',', $value); } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } $sql = array(); if ($operator == 'exists') { global $current_user, $adb; $mid = getTabModuleName($field->getTabId()); $qg = new QueryGenerator($mid, $current_user); $qg->addCondition($field->getFieldName(), $value, 'e'); $sql[] = 'SELECT EXISTS(SELECT 1 ' . $qg->getFromClause() . $qg->getWhereClause() . ')'; return $sql; } if ($operator == 'i' or $operator == 'in' or $operator == 'ni' or $operator == 'nin') { $vals = array_map(array($db, 'quote'), $valueArray); $sql[] = (($operator == 'ni' or $operator == 'nin') ? ' NOT ' : '') . 'IN (' . implode(',', $vals) . ')'; return $sql; } if ($operator == 'between' || $operator == 'bw' || $operator == 'notequal') { if ($field->getFieldName() == 'birthday') { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')"; } else { if ($this->isDateType($field->getFieldDataType())) { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); } $sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } return $sql; } $yes = strtolower(getTranslatedString('yes')); $no = strtolower(getTranslatedString('no')); foreach ($valueArray as $value) { if (!$this->isStringType($field->getFieldDataType())) { $value = trim($value); } if ($operator == 'empty' || $operator == 'y') { $sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName())); continue; } if ($operator == 'ny') { $sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName())); continue; } if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) { if ($operator == 'e') { $sql[] = "IS NULL"; continue; } $sql[] = "IS NOT NULL"; continue; } elseif ($field->getFieldDataType() == 'boolean') { $value = strtolower($value); if ($value == 'yes' or $value == $yes) { $value = 1; } elseif ($value == 'no' or $value == $no) { $value = 0; } } elseif ($this->isDateType($field->getFieldDataType())) { $value = getValidDBInsertDateTimeValue($value); if (empty($value)) { $sql[] = 'IS NULL or ' . $field->getTableName() . '.' . $field->getFieldName() . " = ''"; return $sql; } } elseif ($field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist') { if (!isValueInPicklist($value, $field->getFieldName())) { $value = getTranslationKeyFromTranslatedValue($this->module, $value); } } else { if ($field->getFieldDataType() === 'currency') { $uiType = $field->getUIType(); if ($uiType == 72) { $value = CurrencyField::convertToDBFormat($value, null, true); } elseif ($uiType == 71) { $value = CurrencyField::convertToDBFormat($value, $this->user); } } } if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) { $value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')"; } else { $value = $db->sql_escape_string($value); } if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) { $sql[] = "LIKE ''"; continue; } if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) { $sql[] = "NOT LIKE ''"; continue; } switch ($operator) { case 'e': $sqlOperator = "="; break; case 'n': $sqlOperator = "<>"; break; case 's': $sqlOperator = "LIKE"; $value = "{$value}%"; break; case 'ew': $sqlOperator = "LIKE"; $value = "%{$value}"; break; case 'c': $sqlOperator = "LIKE"; $value = "%{$value}%"; break; case 'k': $sqlOperator = "NOT LIKE"; $value = "%{$value}%"; break; case 'l': $sqlOperator = "<"; break; case 'g': $sqlOperator = ">"; break; case 'm': $sqlOperator = "<="; break; case 'h': $sqlOperator = ">="; break; case 'a': $sqlOperator = ">"; break; case 'b': $sqlOperator = "<"; break; } if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) { $value = "'{$value}'"; } if ($this->isNumericType($field->getFieldDataType()) && empty($value)) { $value = '0'; } $sql[] = "{$sqlOperator} {$value}"; } return $sql; }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); if (is_string($value)) { $valueArray = explode(',', $value); } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } $sql = array(); if ($operator == 'between' || $operator == 'bw') { if ($field->getFieldName() == 'birthday') { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')"; } else { if ($this->isDateType($field->getFieldDataType())) { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); } $sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } return $sql; } foreach ($valueArray as $value) { if (!$this->isStringType($field->getFieldDataType())) { $value = trim($value); } if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) { if ($operator == 'e') { $sql[] = "IS NULL"; continue; } $sql[] = "IS NOT NULL"; continue; } elseif ($field->getFieldDataType() == 'boolean') { $value = strtolower($value); if ($value == 'yes') { $value = 1; } elseif ($value == 'no') { $value = 0; } } elseif ($this->isDateType($field->getFieldDataType())) { $value = getValidDBInsertDateTimeValue($value); } if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) { $value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')"; } else { $value = $db->sql_escape_string($value); } if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) { $sql[] = "LIKE ''"; continue; } if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) { $sql[] = "NOT LIKE ''"; continue; } switch ($operator) { case 'e': $sqlOperator = "="; break; case 'n': $sqlOperator = "<>"; break; case 's': $sqlOperator = "LIKE"; $value = "{$value}%"; break; case 'ew': $sqlOperator = "LIKE"; $value = "%{$value}"; break; case 'c': $sqlOperator = "LIKE"; $value = "%{$value}%"; break; case 'k': $sqlOperator = "NOT LIKE"; $value = "%{$value}%"; break; case 'l': $sqlOperator = "<"; break; case 'g': $sqlOperator = ">"; break; case 'm': $sqlOperator = "<="; break; case 'h': $sqlOperator = ">="; break; case 'a': $sqlOperator = ">"; break; case 'b': $sqlOperator = "<"; break; } if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) { $value = "'{$value}'"; } $sql[] = "{$sqlOperator} {$value}"; } return $sql; }
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; }
private function retrieveMetaForBlock($block) { global $adb; $tabid = $this->getTabId(); require 'user_privileges/user_privileges_' . $this->user->id . '.php'; if ($is_admin == true || $profileGlobalPermission[1] == 0 || $profileGlobalPermission[2] == 0) { $sql = "select *, '0' as readonly from vtiger_field where tabid =? and block in (" . generateQuestionMarks($block) . ") and displaytype in (1,2,3,4)"; $params = array($tabid, $block); } else { $profileList = getCurrentUserProfileList(); if (count($profileList) > 0) { $sql = "SELECT vtiger_field.*, vtiger_profile2field.readonly\n\t\t\t\t\t\tFROM vtiger_field\n\t\t\t\t\t\tINNER JOIN vtiger_profile2field\n\t\t\t\t\t\tON vtiger_profile2field.fieldid = vtiger_field.fieldid\n\t\t\t\t\t\tINNER JOIN vtiger_def_org_field\n\t\t\t\t\t\tON vtiger_def_org_field.fieldid = vtiger_field.fieldid\n\t\t\t\t\t\tWHERE vtiger_field.tabid =? AND vtiger_profile2field.visible = 0 \n\t\t\t\t\t\tAND vtiger_profile2field.profileid IN (" . generateQuestionMarks($profileList) . ")\n\t\t\t\t\t\tAND vtiger_def_org_field.visible = 0 and vtiger_field.block in (" . generateQuestionMarks($block) . ") and vtiger_field.displaytype in (1,2,3,4) and vtiger_field.presence in (0,2) group by columnname"; $params = array($tabid, $profileList, $block); } else { $sql = "SELECT vtiger_field.*, vtiger_profile2field.readonly\n\t\t\t\t\t\tFROM vtiger_field\n\t\t\t\t\t\tINNER JOIN vtiger_profile2field\n\t\t\t\t\t\tON vtiger_profile2field.fieldid = vtiger_field.fieldid\n\t\t\t\t\t\tINNER JOIN vtiger_def_org_field\n\t\t\t\t\t\tON vtiger_def_org_field.fieldid = vtiger_field.fieldid\n\t\t\t\t\t\tWHERE vtiger_field.tabid=? \n\t\t\t\t\t\tAND vtiger_profile2field.visible = 0 \n\t\t\t\t\t\tAND vtiger_def_org_field.visible = 0 and vtiger_field.block in (" . generateQuestionMarks($block) . ") and vtiger_field.displaytype in (1,2,3,4) and vtiger_field.presence in (0,2) group by columnname"; $params = array($tabid, $block); } } // Bulk Save Mode: Group by is not required!? if (CRMEntity::isBulkSaveMode()) { $sql = preg_replace("/group by [^ ]*/", " ", $sql); } // END $result = $adb->pquery($sql, $params); $noofrows = $adb->num_rows($result); $referenceArray = array(); $knownFieldArray = array(); for ($i = 0; $i < $noofrows; $i++) { $fieldname = $adb->query_result($result, $i, "fieldname"); if (strcasecmp($fieldname, 'imagename') === 0) { continue; } $webserviceField = WebserviceField::fromQueryResult($adb, $result, $i); $this->moduleFields[$webserviceField->getFieldName()] = $webserviceField; } }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); $inEqualityFieldTypes = ['currency', 'percentage', 'double', 'integer', 'number']; if (is_string($value) && $this->ignoreComma == false) { $commaSeparatedFieldTypes = ['picklist', 'multipicklist', 'owner', 'date', 'datetime', 'time', 'tree', 'sharedOwner', 'sharedOwner']; if (in_array($field->getFieldDataType(), $commaSeparatedFieldTypes)) { $valueArray = explode(',', $value); if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, ['e', 'n'])) { $valueArray = getCombinations($valueArray); foreach ($valueArray as $key => $value) { $valueArray[$key] = ltrim($value, ' |##| '); } } } else { if ($field->getFieldDataType() == 'multiReferenceValue') { $valueArray = explode(',', $value); foreach ($valueArray as $key => $value) { $valueArray[$key] = '|#|' . $value . '|#|'; } } else { $valueArray = [$value]; } } } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = [$value]; } $sql = array(); if ($operator == 'between' || $operator == 'bw' || $operator == 'notequal') { if ($field->getFieldName() == 'birthday') { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $sql[] = "BETWEEN DATE_FORMAT(" . $db->quote($valueArray[0]) . ", '%m%d') AND " . "DATE_FORMAT(" . $db->quote($valueArray[1]) . ", '%m%d')"; } else { if ($this->isDateType($field->getFieldDataType())) { $start = explode(' ', $valueArray[0]); $end = explode(' ', $valueArray[1]); if ($operator == 'between' && count($start) == 2 && count($end) == 2) { $valueArray[0] = getValidDBInsertDateTimeValue($start[0] . ' ' . $start[1]); if ($start[0] == $end[0]) { $dateTime = new DateTime($valueArray[0]); $nextDay = $dateTime->modify('+1 days'); $nextDay = strtotime($nextDay->format('Y-m-d H:i:s')) - 1; $nextDay = date('Y-m-d H:i:s', $nextDay); $values = explode(' ', $nextDay); $valueArray[1] = getValidDBInsertDateTimeValue($values[0]) . ' ' . $values[1]; } else { $end = $valueArray[1]; $dateObject = new DateTimeField($end); $valueArray[1] = $dateObject->getDBInsertDateTimeValue(); } } else { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $dateTimeStart = explode(' ', $valueArray[0]); if ($dateTimeStart[1] == '00:00:00' && $operator != 'between') { $valueArray[0] = $dateTimeStart[0]; } $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); $dateTimeEnd = explode(' ', $valueArray[1]); if ($dateTimeEnd[1] == '00:00:00' || $dateTimeEnd[1] == '23:59:59') { $valueArray[1] = $dateTimeEnd[0]; } } } if ($operator == 'notequal') { $sql[] = "NOT BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } else { $sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } } return $sql; } foreach ($valueArray as $value) { if (!$this->isStringType($field->getFieldDataType())) { $value = trim($value); } if ($operator == 'empty' || $operator == 'y') { $sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName())); continue; } if ($operator == 'ny') { $sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName())); continue; } if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) { if ($operator == 'e') { $sql[] = "IS NULL"; continue; } $sql[] = "IS NOT NULL"; continue; } elseif ($field->getFieldDataType() == 'boolean') { $value = strtolower($value); if ($value == 'yes') { $value = 1; } elseif ($value == 'no') { $value = 0; } } elseif ($this->isDateType($field->getFieldDataType())) { // For "after" and "before" conditions $values = explode(' ', $value); if (($operator == 'a' || $operator == 'b') && count($values) == 2) { if ($operator == 'a') { // for after comparator we should check the date after the given $dateTime = new DateTime($value); $modifiedDate = $dateTime->modify('+1 days'); $nextday = $modifiedDate->format('Y-m-d H:i:s'); $temp = strtotime($nextday) - 1; $date = date('Y-m-d H:i:s', $temp); $value = getValidDBInsertDateTimeValue($date); } else { $dateTime = new DateTime($value); $prevday = $dateTime->format('Y-m-d H:i:s'); $temp = strtotime($prevday) - 1; $date = date('Y-m-d H:i:s', $temp); $value = getValidDBInsertDateTimeValue($date); } } else { $value = getValidDBInsertDateTimeValue($value); $dateTime = explode(' ', $value); if ($dateTime[1] == '00:00:00') { $value = $dateTime[0]; } } } else { if (in_array($field->getFieldDataType(), $inEqualityFieldTypes)) { $table = get_html_translation_table(HTML_ENTITIES, ENT_COMPAT, vglobal('default_charset')); $chars = implode('', array_keys($table)); if (preg_match("/[{$chars}]+/", $value) === 1) { if ($operator == 'g' || $operator == 'l') { $value = substr($value, 4); } else { if ($operator == 'h' || $operator == 'm') { $value = substr($value, 5); } } } } else { if ($field->getFieldDataType() === 'currency') { $uiType = $field->getUIType(); if ($uiType == 72) { $value = CurrencyField::convertToDBFormat($value, null, true); } elseif ($uiType == 71) { $value = CurrencyField::convertToDBFormat($value); } } } } if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) { $value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')"; } else { $value = $db->sql_escape_string($value, true); } if ($field->getFieldDataType() == 'multiReferenceValue' && in_array($operator, ['e', 's', 'ew', 'c'])) { $sql[] = "LIKE '%{$value}%'"; continue; } else { if ($field->getFieldDataType() == 'multiReferenceValue' && in_array($operator, ['n', 'k'])) { $sql[] = "NOT LIKE '%{$value}%'"; continue; } } if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) { $sql[] = "LIKE ''"; continue; } if (trim($value) == '' && $operator == 'om' && in_array($field->getFieldName(), $this->ownerFields)) { $sql[] = " = '" . Users_Record_Model::getCurrentUserModel()->get('id') . "'"; continue; } if ($field->getUIType() == 120) { $shownersTable = Vtiger_SharedOwner_UIType::getShownerTable($this->getModule()); if ($operator == 'om') { $sql[] = 'vtiger_crmentity.crmid IN (SELECT DISTINCT crmid FROM ' . $shownersTable . ' WHERE userid = ' . Users_Record_Model::getCurrentUserModel()->get('id') . ')'; } else { if (in_array($operator, ['e', 's', 'ew', 'c'])) { $sql[] = 'vtiger_crmentity.crmid IN (SELECT DISTINCT crmid FROM ' . $shownersTable . ' WHERE userid = ' . $value . ')'; } else { if (in_array($operator, ['n', 'k'])) { $sql[] = 'vtiger_crmentity.crmid NOT IN (SELECT DISTINCT crmid FROM ' . $shownersTable . ' WHERE userid = ' . $value . ')'; } } } continue; } if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) { $sql[] = "NOT LIKE ''"; continue; } $sqlOperatorData = $this->getSqlOperator($operator, $value); $sqlOperator = $sqlOperatorData[0]; $value = $sqlOperatorData[1]; if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) { $value = "'{$value}'"; } if ($this->isNumericType($field->getFieldDataType()) && empty($value)) { $value = '0'; } $sql[] = "{$sqlOperator} {$value}"; } return $sql; }
function GenerateReport($outputformat, $filtersql, $directOutput = false) { global $adb, $current_user, $php_max_execution_time; global $modules, $app_strings; global $mod_strings, $current_language; require 'user_privileges/user_privileges_' . $current_user->id . '.php'; $modules_selected = array(); $modules_selected[] = $this->primarymodule; if (!empty($this->secondarymodule)) { $sec_modules = split(":", $this->secondarymodule); for ($i = 0; $i < count($sec_modules); $i++) { $modules_selected[] = $sec_modules[$i]; } } // Update 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); $arrayHeaders = array(); for ($x = 0; $x < $y; $x++) { $fld = $adb->field_name($result, $x); if (in_array($this->getLstringforReportHeaders($fld->name), $arrayHeaders)) { $headerLabel = str_replace("_", " ", $fld->name); $arrayHeaders[] = $headerLabel; } else { $headerLabel = str_replace($modules, " ", $this->getLstringforReportHeaders($fld->name)); $headerLabel = str_replace("_", " ", $this->getLstringforReportHeaders($fld->name)); $arrayHeaders[] = $headerLabel; } /*STRING TRANSLATION starts */ $mod_name = split(' ', $headerLabel, 2); $moduleLabel = ''; if (in_array($mod_name[0], $modules_selected)) { $moduleLabel = getTranslatedString($mod_name[0], $mod_name[0]); } if (!empty($this->secondarymodule)) { if ($moduleLabel != '') { $headerLabel_tmp = $moduleLabel . " " . getTranslatedString($mod_name[1], $mod_name[0]); } else { $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]); } } else { if ($moduleLabel != '') { $headerLabel_tmp = getTranslatedString($mod_name[1], $mod_name[0]); } else { $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]); } } if ($headerLabel == $headerLabel_tmp) { $headerLabel = getTranslatedString($headerLabel_tmp); } else { $headerLabel = $headerLabel_tmp; } /*STRING TRANSLATION ends */ $header .= "<td class='rptCellLabel'>" . $headerLabel . "</td>"; // Performance Optimization: If direct output is required if ($directOutput) { echo $header; $header = ''; } // END } // Performance Optimization: If direct output is required if ($directOutput) { echo '</tr><tr>'; } // END $noofrows = $adb->num_rows($result); $custom_field_values = $adb->fetch_array($result); $groupslist = $this->getGroupingList($this->reportid); $column_definitions = $adb->getFieldsDefinition($result); do { $arraylists = array(); if (count($groupslist) == 1) { $newvalue = $custom_field_values[0]; } elseif (count($groupslist) == 2) { $newvalue = $custom_field_values[0]; $snewvalue = $custom_field_values[1]; } elseif (count($groupslist) == 3) { $newvalue = $custom_field_values[0]; $snewvalue = $custom_field_values[1]; $tnewvalue = $custom_field_values[2]; } if ($newvalue == "") { $newvalue = "-"; } if ($snewvalue == "") { $snewvalue = "-"; } if ($tnewvalue == "") { $tnewvalue = "-"; } $valtemplate .= "<tr>"; // Performance Optimization if ($directOutput) { echo $valtemplate; $valtemplate = ''; } // END for ($i = 0; $i < $y; $i++) { $fld = $adb->field_name($result, $i); $fld_type = $column_definitions[$i]->type; $fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i); //check for Roll based pick list $temp_val = $fld->name; if ($fieldvalue == "") { $fieldvalue = "-"; } else { if ($fld->name == 'LBL_ACTION') { $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 = ''; } // END } $valtemplate .= "</tr>"; // Performance Optimization: If direct output is required if ($directOutput) { echo $valtemplate; $valtemplate = ''; } // END $lastvalue = $newvalue; $secondvalue = $snewvalue; $thirdvalue = $tnewvalue; $arr_val[] = $arraylists; set_time_limit($php_max_execution_time); } while ($custom_field_values = $adb->fetch_array($result)); // Performance Optimization if ($directOutput) { echo "</tr></table>"; echo "<script type='text/javascript' id='__reportrun_directoutput_recordcount_script'>\r\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->query($sSQL); if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) { $picklistarray = $this->getAccessPickListValues(); } if ($result) { $y = $adb->num_fields($result); $noofrows = $adb->num_rows($result); $custom_field_values = $adb->fetch_array($result); $column_definitions = $adb->getFieldsDefinition($result); do { $arraylists = array(); for ($i = 0; $i < $y; $i++) { $fld = $adb->field_name($result, $i); $fld_type = $column_definitions[$i]->type; list($module, $fieldLabel) = explode('_', $fld->name, 2); $fieldInfo = getFieldByReportLabel($module, $fieldLabel); $fieldType = null; if (!empty($fieldInfo)) { $field = WebserviceField::fromArray($adb, $fieldInfo); $fieldType = $field->getFieldDataType(); } if (!empty($fieldInfo)) { $translatedLabel = getTranslatedString($field->getFieldLabelKey(), $module); } else { $translatedLabel = getTranslatedString(str_replace('_', " ", $fieldLabel), $module); } /*STRING TRANSLATION starts */ $moduleLabel = ''; if (in_array($module, $modules_selected)) { $moduleLabel = getTranslatedString($module, $module); } if (empty($translatedLabel)) { $translatedLabel = getTranslatedString(str_replace('_', " ", $fld->name)); } $headerLabel = $translatedLabel; if (!empty($this->secondarymodule)) { if ($moduleLabel != '') { $headerLabel = $moduleLabel . " " . $translatedLabel; } } // Check for role based pick list $temp_val = $fld->name; $fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i); $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)) { 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 = 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"); if (isset($this->totallist)) { if ($sSQL != "") { $result = $adb->query($sSQL); $y = $adb->num_fields($result); $custom_field_values = $adb->fetch_array($result); $coltotalhtml .= "<table align='center' width='60%' cellpadding='3' cellspacing='0' border='0' class='rptTable'><tr><td class='rptCellLabel'>" . $mod_strings[Totals] . "</td><td class='rptCellLabel'>" . $mod_strings[SUM] . "</td><td class='rptCellLabel'>" . $mod_strings[AVG] . "</td><td class='rptCellLabel'>" . $mod_strings[MIN] . "</td><td class='rptCellLabel'>" . $mod_strings[MAX] . "</td></tr>"; // Performation Optimization: If Direct output is desired if ($directOutput) { echo $coltotalhtml; $coltotalhtml = ''; } // END foreach ($this->totallist as $key => $value) { $fieldlist = explode(":", $key); $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid, uitype as uitype from vtiger_field where tablename = ? and columnname=?", array($fieldlist[1], $fieldlist[2])); if ($adb->num_rows($mod_query) > 0) { $module_name = 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 = trim($key); $arraykey = $value . '_SUM'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>'; } else { $coltotalhtml .= '<td class="rptTotal"> </td>'; } $arraykey = $value . '_AVG'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>'; } else { $coltotalhtml .= '<td class="rptTotal"> </td>'; } $arraykey = $value . '_MIN'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>'; } else { $coltotalhtml .= '<td class="rptTotal"> </td>'; } $arraykey = $value . '_MAX'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= '<td class="rptTotal">' . $conv_value . '</td>'; } else { $coltotalhtml .= '<td class="rptTotal"> </td>'; } $coltotalhtml .= '<tr>'; // Performation Optimization: If Direct output is desired if ($directOutput) { echo $coltotalhtml; $coltotalhtml = ''; } // END } $coltotalhtml .= "</table>"; // Performation Optimization: If Direct output is desired if ($directOutput) { echo $coltotalhtml; $coltotalhtml = ''; } // END } } return $coltotalhtml; } elseif ($outputformat == "PRINT") { $sSQL = $this->sGetSQLforReport($this->reportid, $filtersql); $result = $adb->query($sSQL); if ($is_admin == false && $profileGlobalPermission[1] == 1 && $profileGlobalPermission[2] == 1) { $picklistarray = $this->getAccessPickListValues(); } if ($result) { $y = $adb->num_fields($result); $arrayHeaders = array(); for ($x = 0; $x < $y; $x++) { $fld = $adb->field_name($result, $x); if (in_array($this->getLstringforReportHeaders($fld->name), $arrayHeaders)) { $headerLabel = str_replace("_", " ", $fld->name); $arrayHeaders[] = $headerLabel; } else { $headerLabel = str_replace($modules, " ", $this->getLstringforReportHeaders($fld->name)); $arrayHeaders[] = $headerLabel; } /*STRING TRANSLATION starts */ $mod_name = split(' ', $headerLabel, 2); $moduleLabel = ''; if (in_array($mod_name[0], $modules_selected)) { $moduleLabel = getTranslatedString($mod_name[0], $mod_name[0]); } if (!empty($this->secondarymodule)) { if ($moduleLabel != '') { $headerLabel_tmp = $moduleLabel . " " . getTranslatedString($mod_name[1], $mod_name[0]); } else { $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]); } } else { if ($moduleLabel != '') { $headerLabel_tmp = getTranslatedString($mod_name[1], $mod_name[0]); } else { $headerLabel_tmp = getTranslatedString($mod_name[0] . " " . $mod_name[1]); } } if ($headerLabel == $headerLabel_tmp) { $headerLabel = getTranslatedString($headerLabel_tmp); } else { $headerLabel = $headerLabel_tmp; } /*STRING TRANSLATION ends */ $header .= "<th>" . $headerLabel . "</th>"; } $noofrows = $adb->num_rows($result); $custom_field_values = $adb->fetch_array($result); $groupslist = $this->getGroupingList($this->reportid); $column_definitions = $adb->getFieldsDefinition($result); do { $arraylists = array(); if (count($groupslist) == 1) { $newvalue = $custom_field_values[0]; } elseif (count($groupslist) == 2) { $newvalue = $custom_field_values[0]; $snewvalue = $custom_field_values[1]; } elseif (count($groupslist) == 3) { $newvalue = $custom_field_values[0]; $snewvalue = $custom_field_values[1]; $tnewvalue = $custom_field_values[2]; } if ($newvalue == "") { $newvalue = "-"; } if ($snewvalue == "") { $snewvalue = "-"; } if ($tnewvalue == "") { $tnewvalue = "-"; } $valtemplate .= "<tr>"; for ($i = 0; $i < $y; $i++) { $fld = $adb->field_name($result, $i); $fld_type = $column_definitions[$i]->type; $fieldvalue = getReportFieldValue($this, $picklistarray, $fld, $custom_field_values, $i); if ($lastvalue == $fieldvalue && $this->reporttype == "summary") { if ($this->reporttype == "summary") { $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; } else { $valtemplate .= "<td>" . $fieldvalue . "</td>"; } } else { if ($secondvalue == $fieldvalue && $this->reporttype == "summary") { if ($lastvalue == $newvalue) { $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; } else { $valtemplate .= "<td>" . $fieldvalue . "</td>"; } } else { if ($thirdvalue == $fieldvalue && $this->reporttype == "summary") { if ($secondvalue == $snewvalue) { $valtemplate .= "<td style='border-top:1px dotted #FFFFFF;'> </td>"; } else { $valtemplate .= "<td>" . $fieldvalue . "</td>"; } } else { if ($this->reporttype == "tabular") { $valtemplate .= "<td>" . $fieldvalue . "</td>"; } else { $valtemplate .= "<td>" . $fieldvalue . "</td>"; } } } } } $valtemplate .= "</tr>"; $lastvalue = $newvalue; $secondvalue = $snewvalue; $thirdvalue = $tnewvalue; $arr_val[] = $arraylists; set_time_limit($php_max_execution_time); } while ($custom_field_values = $adb->fetch_array($result)); $sHTML = '<tr>' . $header . '</tr>' . $valtemplate; $return_data[] = $sHTML; $return_data[] = $noofrows; return $return_data; } } elseif ($outputformat == "PRINT_TOTAL") { $escapedchars = array('_SUM', '_AVG', '_MIN', '_MAX'); $sSQL = $this->sGetSQLforReport($this->reportid, $filtersql, "COLUMNSTOTOTAL"); if (isset($this->totallist)) { if ($sSQL != "") { $result = $adb->query($sSQL); $y = $adb->num_fields($result); $custom_field_values = $adb->fetch_array($result); $coltotalhtml .= "<br /><table align='center' width='60%' cellpadding='3' cellspacing='0' border='1' class='printReport'><tr><td class='rptCellLabel'>" . $mod_strings['Totals'] . "</td><td><b>" . $mod_strings['SUM'] . "</b></td><td><b>" . $mod_strings['AVG'] . "</b></td><td><b>" . $mod_strings['MIN'] . "</b></td><td><b>" . $mod_strings['MAX'] . "</b></td></tr>"; // Performation Optimization: If Direct output is desired if ($directOutput) { echo $coltotalhtml; $coltotalhtml = ''; } // END foreach ($this->totallist as $key => $value) { $fieldlist = explode(":", $key); $mod_query = $adb->pquery("SELECT distinct(tabid) as tabid, uitype as uitype from vtiger_field where tablename = ? and columnname=?", array($fieldlist[1], $fieldlist[2])); if ($adb->num_rows($mod_query) > 0) { $module_name = getTabModuleName($adb->query_result($mod_query, 0, 'tabid')); $fieldlabel = trim(str_replace($escapedchars, " ", $fieldlist[3])); $fieldlabel = str_replace("_", " ", $fieldlabel); if ($module_name) { $field = getTranslatedString($module_name, $module_name) . " " . getTranslatedString($fieldlabel, $module_name); } else { $field = getTranslatedString($fieldlabel); } } $uitype_arr[str_replace($escapedchars, " ", $module_name . "_" . $fieldlist[3])] = $adb->query_result($mod_query, 0, "uitype"); $totclmnflds[str_replace($escapedchars, " ", $module_name . "_" . $fieldlist[3])] = $field; } for ($i = 0; $i < $y; $i++) { $fld = $adb->field_name($result, $i); $keyhdr[$fld->name] = $custom_field_values[$i]; } foreach ($totclmnflds as $key => $value) { $coltotalhtml .= '<tr class="rptGrpHead">'; $col_header = getTranslatedString(trim(str_replace($modules, " ", $value))); $fld_name_1 = $this->primarymodule . "_" . trim($value); $fld_name_2 = $this->secondarymodule . "_" . trim($value); if ($uitype_arr[$key] == 71 || $uitype_arr[$key] == 72 || in_array($fld_name_1, $this->append_currency_symbol_to_value) || in_array($fld_name_2, $this->append_currency_symbol_to_value)) { $col_header .= " (" . $app_strings['LBL_IN'] . " " . $current_user->currency_symbol . ")"; $convert_price = true; } else { $convert_price = false; } $coltotalhtml .= '<td class="rptData">' . $col_header . '</td>'; $value = trim($key); $arraykey = $value . '_SUM'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>'; } else { $coltotalhtml .= "<td class='rptTotal'> </td>"; } $arraykey = $value . '_AVG'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>'; } else { $coltotalhtml .= "<td class='rptTotal'> </td>"; } $arraykey = $value . '_MIN'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>'; } else { $coltotalhtml .= "<td class='rptTotal'> </td>"; } $arraykey = $value . '_MAX'; if (isset($keyhdr[$arraykey])) { if ($convert_price) { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey]); } else { $conv_value = CurrencyField::convertToUserFormat($keyhdr[$arraykey], null, true); } $coltotalhtml .= "<td class='rptTotal'>" . $conv_value . '</td>'; } else { $coltotalhtml .= "<td class='rptTotal'> </td>"; } $coltotalhtml .= '</tr>'; // Performation Optimization: If Direct output is desired if ($directOutput) { echo $coltotalhtml; $coltotalhtml = ''; } // END } $coltotalhtml .= "</table>"; // Performation Optimization: If Direct output is desired if ($directOutput) { echo $coltotalhtml; $coltotalhtml = ''; } // END } } return $coltotalhtml; } }