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;
}
public function getWhereClause()
{
if (!empty($this->query) || !empty($this->whereClause)) {
return $this->whereClause;
}
$deletedQuery = $this->meta->getEntityDeletedQuery();
$sql = '';
if (!empty($deletedQuery)) {
$sql .= " WHERE {$deletedQuery}";
}
if ($this->conditionInstanceCount > 0) {
$sql .= ' AND ';
} elseif (empty($deletedQuery)) {
$sql .= ' WHERE ';
}
$moduleFieldList = $this->meta->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)) {
continue;
}
$fieldSql = '(';
$fieldGlue = '';
$valueSqlList = $this->getConditionValue($conditionInfo['value'], $conditionInfo['operator'], $field);
if (!is_array($valueSqlList)) {
$valueSqlList = array($valueSqlList);
}
foreach ($valueSqlList as $valueSql) {
if (in_array($fieldName, $this->referenceFieldList)) {
$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 .= '2';
}
} else {
$referenceField = $meta->getFieldByColumnName($column);
$referenceTable = $referenceField->getTableName();
}
if (isset($moduleTableIndexList[$referenceTable])) {
$referenceTable = "{$referenceTable}{$fieldName}";
}
$columnList[] = "{$referenceTable}.{$column}";
}
if (count($columnList) > 1) {
$columnSql = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users');
} else {
$columnSql = implode('', $columnList);
}
$fieldSql .= "{$fieldGlue} trim({$columnSql}) {$valueSql}";
$fieldGlue = ' OR';
}
} elseif (in_array($fieldName, $this->ownerFields)) {
$concatSql = getSqlForNameInDisplayFormat(array('first_name' => "vtiger_users.first_name", 'last_name' => "vtiger_users.last_name"), 'Users');
$fieldSql .= "{$fieldGlue} trim({$concatSql}) {$valueSql} or " . "vtiger_groups.groupname {$valueSql}";
} 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;
}
}
$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;
}
$groupSql = $this->makeGroupSqlReplacements($fieldSqlList, $groupSql);
if ($this->conditionInstanceCount > 0) {
$this->conditionalWhere = $groupSql;
$sql .= $groupSql;
}
$sql .= " AND {$baseTable}.{$baseTableIndex} > 0";
$this->whereClause = $sql;
return $sql;
}
public function getWhereClause()
{
global $current_user;
if (!empty($this->query) || !empty($this->whereClause)) {
return $this->whereClause;
}
$deletedQuery = $this->meta->getEntityDeletedQuery();
$sql = '';
if (!empty($deletedQuery)) {
$sql .= " WHERE {$deletedQuery}";
}
if ($this->conditionInstanceCount > 0) {
$sql .= ' AND ';
} elseif (empty($deletedQuery)) {
$sql .= ' WHERE ';
}
$baseModule = $this->getModule();
$moduleFieldList = $this->meta->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);
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[] = "{$referenceTable}.{$column}";
}
if (count($columnList) > 1) {
$columnSql = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users');
} else {
$columnSql = implode('', $columnList);
}
$fieldSql .= "{$fieldGlue} trim({$columnSql}) {$valueSql}";
$fieldGlue = ' OR';
}
}
} elseif (in_array($fieldName, $this->ownerFields)) {
$concatSql = getSqlForNameInDisplayFormat(array('first_name' => "vtiger_users.first_name", 'last_name' => "vtiger_users.last_name"), 'Users');
$fieldSql .= "{$fieldGlue} (trim({$concatSql}) {$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') {
$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 ($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' && ($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;
}
$sql .= " AND {$baseTable}.{$baseTableIndex} > 0";
$this->whereClause = $sql;
return $sql;
}
public function getWhereClause()
{
global $current_user;
if (!empty($this->query) || !empty($this->whereClause)) {
return $this->whereClause;
}
$deletedQuery = $this->meta->getEntityDeletedQuery();
$sql = '';
if (!empty($deletedQuery)) {
$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'];
if ($fieldName == 'id') {
switch ($conditionInfo['operator']) {
case 'e':
$sqlOperator = "=";
break;
case 'n':
$sqlOperator = "<>";
break;
case 'l':
$sqlOperator = "<";
break;
case 'g':
$sqlOperator = ">";
break;
case 'm':
$sqlOperator = "<=";
break;
case 'h':
$sqlOperator = ">=";
break;
default:
$sqlOperator = "=";
}
$value = $conditionInfo['value'];
if (empty($value)) {
$value = '0';
}
$fieldSqlList[$index] = "({$baseTable}.{$baseTableIndex} {$sqlOperator} '{$value}')";
continue;
}
$field = $moduleFieldList[$fieldName];
if (empty($field) || $conditionInfo['operator'] == 'None') {
continue;
}
$fieldSql = '(';
$fieldGlue = '';
$valueSqlList = $this->getConditionValue($conditionInfo['value'], $conditionInfo['operator'], $field);
if ($conditionInfo['operator'] == 'exists') {
$fieldSqlList[$index] = '(' . $valueSqlList[0] . ')';
continue;
}
if (!is_array($valueSqlList)) {
$valueSqlList = array($valueSqlList);
}
foreach ($valueSqlList as $valueSql) {
if (in_array($fieldName, $this->referenceFieldList)) {
$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') {
$referenceTable = "vtiger_users" . $fieldName;
} else {
$referenceField = $meta->getFieldByColumnName($column);
if (!$referenceField) {
continue;
}
$referenceTable = $referenceField->getTableName();
}
if (isset($moduleTableIndexList[$referenceTable])) {
$referenceTable = "{$referenceTable}{$fieldName}";
}
$columnList[] = "{$referenceTable}.{$column}";
}
if (count($columnList) > 1) {
$columnSql = getSqlForNameInDisplayFormat(array('first_name' => $columnList[0], 'last_name' => $columnList[1]), 'Users');
} else {
$columnSql = implode('', $columnList);
}
$fieldSql .= "{$fieldGlue} trim({$columnSql}) {$valueSql}";
$fieldGlue = ' OR';
}
} elseif (in_array($fieldName, $this->ownerFields)) {
$concatSql = getSqlForNameInDisplayFormat(array('first_name' => "vtiger_users.first_name", 'last_name' => "vtiger_users.last_name"), 'Users');
$fieldSql .= "{$fieldGlue} (trim({$concatSql}) {$valueSql} or " . "vtiger_groups.groupname {$valueSql})";
} 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 (isset($this->referenceModuleField)) {
foreach ($this->referenceModuleField as $index => $conditionInfo) {
$handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user);
$meta = $handler->getMeta();
$fieldName = $conditionInfo['fieldName'];
$fields = $meta->getModuleFields();
if ($fieldName == 'id') {
switch ($conditionInfo['SQLOperator']) {
case 'e':
$sqlOperator = "=";
break;
case 'n':
$sqlOperator = "<>";
break;
case 'l':
$sqlOperator = "<";
break;
case 'g':
$sqlOperator = ">";
break;
case 'm':
$sqlOperator = "<=";
break;
case 'h':
$sqlOperator = ">=";
break;
default:
$sqlOperator = "=";
}
$value = $conditionInfo['value'];
if (!empty($value)) {
$fname = $meta->getObectIndexColumn();
$bTable = $meta->getEntityBaseTable();
if ($bTable == 'vtiger_users') {
$fieldSqlList[$index] = "(vtiger_users.id {$sqlOperator} '{$value}' or vtiger_groups.groupid {$sqlOperator} '{$value}')";
} else {
$fieldSqlList[$index] = "({$bTable}" . $conditionInfo['referenceField'] . ".{$fname} {$sqlOperator} '{$value}')";
}
}
continue;
}
if (empty($fields[$fieldName])) {
continue;
}
$fieldObject = $fields[$fieldName];
$columnName = $fieldObject->getColumnName();
$tableName = $fieldObject->getTableName();
$valueSQL = $this->getConditionValue($conditionInfo['value'], $conditionInfo['SQLOperator'], $fieldObject);
if ($conditionInfo['SQLOperator'] == 'exists') {
$fieldSqlList[$index] = '(' . $valueSQL[0] . ')';
continue;
}
if ($tableName == 'vtiger_users') {
$reffield = $moduleFieldList[$conditionInfo['referenceField']];
if ($reffield->getUIType() == '101') {
$fieldSql = "(" . $tableName . $conditionInfo['referenceField'] . '.' . $columnName . ' ' . $valueSQL[0] . ")";
} else {
$fieldSql = "(" . $tableName . '.' . $columnName . ' ' . $valueSQL[0] . ")";
}
} else {
$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;
}
$sql .= " AND {$baseTable}.{$baseTableIndex} > 0";
$this->whereClause = $sql;
return $sql;
}