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;
 }
示例#4
0
 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;
 }