public function getWhereClause($onlyWhereQuery = false) { $current_user = vglobal('current_user'); if (!empty($this->query) || !empty($this->whereClause)) { return $this->whereClause; } $deletedQuery = $this->meta->getEntityDeletedQuery(); $sql = ''; if (!empty($deletedQuery) && !$onlyWhereQuery) { $sql .= " WHERE {$deletedQuery}"; } if ($this->conditionInstanceCount > 0) { $sql .= ' AND '; } elseif (empty($deletedQuery)) { $sql .= ' WHERE '; } $baseModule = $this->getModule(); $moduleFieldList = $this->getModuleFields(); $baseTable = $this->meta->getEntityBaseTable(); $moduleTableIndexList = $this->meta->getEntityTableIndexList(); $baseTableIndex = $moduleTableIndexList[$baseTable]; $groupSql = $this->groupInfo; $fieldSqlList = array(); foreach ($this->conditionals as $index => $conditionInfo) { $fieldName = $conditionInfo['name']; $field = $moduleFieldList[$fieldName]; if (empty($field) || $conditionInfo['operator'] == 'None') { continue; } $fieldSql = '('; $fieldGlue = ''; $valueSqlList = $this->getConditionValue($conditionInfo['value'], $conditionInfo['operator'], $field); $operator = strtolower($conditionInfo['operator']); if ($operator == 'between' && $this->isDateType($field->getFieldDataType())) { $start = explode(' ', $conditionInfo['value'][0]); if (count($start) == 2) { $conditionInfo['value'][0] = getValidDBInsertDateTimeValue($start[0] . ' ' . $start[1]); } $end = explode(' ', $conditionInfo['values'][1]); // Dates will be equal for Today, Tomorrow, Yesterday. if (count($end) == 2) { if ($start[0] == $end[0]) { $dateTime = new DateTime($conditionInfo['value'][0]); $nextDay = $dateTime->modify('+1 days'); $nextDay = $nextDay->format('Y-m-d H:i:s'); $values = explode(' ', $nextDay); $conditionInfo['value'][1] = getValidDBInsertDateTimeValue($values[0]) . ' ' . $values[1]; } else { $end = $conditionInfo['value'][1]; $dateObject = new DateTimeField($end); $conditionInfo['value'][1] = $dateObject->getDBInsertDateTimeValue(); } } } if (!is_array($valueSqlList)) { $valueSqlList = array($valueSqlList); } foreach ($valueSqlList as $valueSql) { if (in_array($fieldName, $this->referenceFieldList)) { if ($conditionInfo['operator'] == 'y') { $columnName = $field->getColumnName(); $tableName = $field->getTableName(); // We are checking for zero since many reference fields will be set to 0 if it doest not have any value $fieldSql .= "{$fieldGlue} {$tableName}.{$columnName} {$valueSql} OR {$tableName}.{$columnName} = '0'"; $fieldGlue = ' OR'; } else { $moduleList = $this->referenceFieldInfoList[$fieldName]; foreach ($moduleList as $module) { $nameFields = $this->moduleNameFields[$module]; $nameFieldList = explode(',', $nameFields); $meta = $this->getMeta($module); $columnList = array(); foreach ($nameFieldList as $column) { if ($module == 'Users') { $instance = CRMEntity::getInstance($module); $referenceTable = $instance->table_name; if (count($this->ownerFields) > 0 || $this->getModule() == 'Quotes') { $referenceTable .= $fieldName; } } else { $referenceField = $meta->getFieldByColumnName($column); $referenceTable = $referenceField->getTableName() . $fieldName; } if (isset($moduleTableIndexList[$referenceTable])) { $referenceTable = "{$referenceTable}{$fieldName}"; } $columnList[$column] = "{$referenceTable}.{$column}"; } if (count($columnList) > 1) { $columnSql = getSqlForNameInDisplayFormat($columnList, $module); } else { $columnSql = implode('', $columnList); } $fieldSql .= "{$fieldGlue} trim({$columnSql}) {$valueSql}"; $fieldGlue = ' OR'; } } } elseif (in_array($fieldName, $this->ownerFields)) { if ($conditionInfo['operator'] == 'om') { $fieldSql .= $fieldGlue . $field->getTableName() . '.' . $field->getColumnName() . " {$valueSql}"; } elseif ($fieldName == 'created_user_id') { $concatSql = getSqlForNameInDisplayFormat(array('first_name' => "vtiger_users{$fieldName}.first_name", 'last_name' => "vtiger_users{$fieldName}.last_name"), 'Users'); $fieldSql .= "{$fieldGlue} (trim({$concatSql}) {$valueSql})"; } else { $entityFields = Vtiger_Functions::getEntityModuleInfoFieldsFormatted('Users'); if (count($entityFields['fieldname']) > 1) { $columns = []; foreach ($entityFields['fieldname'] as $i => $fieldname) { $columns[$fieldname] = $entityFields['tablename'] . '.' . $fieldname; } $concatSql = getSqlForNameInDisplayFormat($columns, 'Users'); $fieldSql .= "{$fieldGlue} (trim({$concatSql}) {$valueSql} OR " . "vtiger_groups.groupname {$valueSql})"; } else { $columnSql = $entityFields['tablename'] . '.' . $entityFields['fieldname']; $fieldSql .= "{$fieldGlue} (trim({$columnSql}) {$valueSql} OR " . "vtiger_groups.groupname {$valueSql})"; } } } elseif ($field->getFieldDataType() == 'date' && ($baseModule == 'Events' || $baseModule == 'Calendar') && ($fieldName == 'date_start' || $fieldName == 'due_date')) { $value = $conditionInfo['value']; $operator = $conditionInfo['operator']; if ($fieldName == 'date_start') { $dateFieldColumnName = 'vtiger_activity.date_start'; $timeFieldColumnName = 'vtiger_activity.time_start'; } else { $dateFieldColumnName = 'vtiger_activity.due_date'; $timeFieldColumnName = 'vtiger_activity.time_end'; } if ($operator == 'bw') { $values = explode(',', $value); $startDateValue = explode(' ', $values[0]); $endDateValue = explode(' ', $values[1]); if (count($startDateValue) == 2 && count($endDateValue) == 2) { $fieldSql .= " CAST(CONCAT({$dateFieldColumnName},' ',{$timeFieldColumnName}) AS DATETIME) {$valueSql}"; } else { $fieldSql .= "{$dateFieldColumnName} {$valueSql}"; } } else { if (is_array($value)) { $value = $value[0]; } $values = explode(' ', $value); if (count($values) == 2) { $fieldSql .= "{$fieldGlue} CAST(CONCAT({$dateFieldColumnName},' ',{$timeFieldColumnName}) AS DATETIME) {$valueSql} "; } else { $fieldSql .= "{$fieldGlue} {$dateFieldColumnName} {$valueSql}"; } } } elseif ($field->getFieldDataType() == 'datetime') { $value = $conditionInfo['value']; $operator = strtolower($conditionInfo['operator']); if ($operator == 'bw') { $values = explode(',', $value); $startDateValue = explode(' ', $values[0]); $endDateValue = explode(' ', $values[1]); if ($startDateValue[1] == '00:00:00' && ($endDateValue[1] == '00:00:00' || $endDateValue[1] == '23:59:59')) { $fieldSql .= "{$fieldGlue} CAST(" . $field->getTableName() . '.' . $field->getColumnName() . " AS DATE) {$valueSql}"; } else { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } } elseif ($operator == 'between' || $operator == 'notequal' || $operator == 'a' || $operator == 'b') { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } else { $values = explode(' ', $value); if ($values[1] == '00:00:00') { $fieldSql .= "{$fieldGlue} CAST(" . $field->getTableName() . '.' . $field->getColumnName() . " AS DATE) {$valueSql}"; } else { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } } } else { if (($baseModule == 'Events' || $baseModule == 'Calendar') && ($field->getColumnName() == 'status' || $field->getColumnName() == 'eventstatus')) { $otherFieldName = 'eventstatus'; if ($field->getColumnName() == 'eventstatus') { $otherFieldName = 'taskstatus'; } $otherField = $moduleFieldList[$otherFieldName]; $specialCondition = ''; $specialConditionForOtherField = ''; $conditionGlue = ' OR '; if ($conditionInfo['operator'] == 'n' || $conditionInfo['operator'] == 'k' || $conditionInfo['operator'] == 'y') { $conditionGlue = ' AND '; if ($conditionInfo['operator'] == 'n') { $specialCondition = ' OR ' . $field->getTableName() . '.' . $field->getColumnName() . ' IS NULL '; if (!empty($otherField)) { $specialConditionForOtherField = ' OR ' . $otherField->getTableName() . '.' . $otherField->getColumnName() . ' IS NULL '; } } } $otherFieldValueSql = $valueSql; if ($conditionInfo['operator'] == 'ny' && !empty($otherField)) { $otherFieldValueSql = "IS NOT NULL AND " . $otherField->getTableName() . '.' . $otherField->getColumnName() . " != ''"; } $fieldSql .= "{$fieldGlue} ((" . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql . " {$specialCondition}) "; if (!empty($otherField)) { $fieldSql .= $conditionGlue . '(' . $otherField->getTableName() . '.' . $otherField->getColumnName() . ' ' . $otherFieldValueSql . ' ' . $specialConditionForOtherField . '))'; } else { $fieldSql .= ')'; } } else { if ($fieldName == 'birthday' && !$this->isRelativeSearchOperators($conditionInfo['operator'])) { $fieldSql .= "{$fieldGlue} DATE_FORMAT(" . $field->getTableName() . '.' . $field->getColumnName() . ",'%m%d') " . $valueSql; } else { $fieldSql .= "{$fieldGlue} " . $field->getTableName() . '.' . $field->getColumnName() . ' ' . $valueSql; } } } if (($conditionInfo['operator'] == 'n' || $conditionInfo['operator'] == 'k') && ($field->getFieldDataType() == 'owner' || $field->getFieldDataType() == 'picklist')) { $fieldGlue = ' AND'; } else { $fieldGlue = ' OR'; } } $fieldSql .= ')'; $fieldSqlList[$index] = $fieldSql; } foreach ($this->manyToManyRelatedModuleConditions as $index => $conditionInfo) { $relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(), $conditionInfo['relatedModule']); $relationInfo = $relatedModuleMeta->getRelationMeta(); $relatedModule = $this->meta->getTabName(); $fieldSql = "(" . $relationInfo['relationTable'] . '.' . $relationInfo[$conditionInfo['column']] . $conditionInfo['SQLOperator'] . $conditionInfo['value'] . ")"; $fieldSqlList[$index] = $fieldSql; } // This is added to support reference module fields if ($this->referenceModuleField) { foreach ($this->referenceModuleField as $index => $conditionInfo) { $handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user); $meta = $handler->getMeta(); $fieldName = $conditionInfo['fieldName']; $fields = $meta->getModuleFields(); $fieldObject = $fields[$fieldName]; $columnName = $fieldObject->getColumnName(); $tableName = $fieldObject->getTableName(); $valueSQL = $this->getConditionValue($conditionInfo['value'], $conditionInfo['SQLOperator'], $fieldObject); $fieldSql = "(" . $tableName . $conditionInfo['referenceField'] . '.' . $columnName . ' ' . $valueSQL[0] . ")"; $fieldSqlList[$index] = $fieldSql; } } // This is needed as there can be condition in different order and there is an assumption in makeGroupSqlReplacements API // that it expects the array in an order and then replaces the sql with its the corresponding place ksort($fieldSqlList); $groupSql = $this->makeGroupSqlReplacements($fieldSqlList, $groupSql); if ($this->conditionInstanceCount > 0) { $this->conditionalWhere = $groupSql; $sql .= $groupSql; } if (!$onlyWhereQuery) { $sql .= " AND {$baseTable}.{$baseTableIndex} > 0"; $instance = CRMEntity::getInstance($baseModule); $sql .= $instance->getUserAccessConditionsQuerySR($baseModule, $current_user); } $this->whereClause = $sql; return $sql; }
static function getSqlForNameInDisplayFormat($input, $module, $glue = ' ') { $entity_field_info = Vtiger_Functions::getEntityModuleInfoFieldsFormatted($module); $fieldsName = $entity_field_info['fieldname']; if (is_array($fieldsName)) { foreach ($fieldsName as $key => $value) { $formattedNameList[] = $input[$value]; } $formattedNameListString = implode(",'" . $glue . "',", $formattedNameList); } else { $formattedNameListString = $input[$fieldsName]; } $sqlString = "CONCAT(" . $formattedNameListString . ")"; return $sqlString; }
/** * this function returns the entity information for a given module; for e.g. for Contacts module * it returns the information of tablename, modulename, fieldsname and id gets from vtiger_entityname * @param string $module - the module name * @return array $data - the entity information for the module */ function getEntityFieldNames($module) { return Vtiger_Functions::getEntityModuleInfoFieldsFormatted($module); }