ꦫꦣꦺꦤ꧀ꦄꦤ꧀ꦠꦱꦺꦤ

PATH: home / letacommog / crmleta / include / QueryGenerator

<?php
/*+************************************************************************************
 * The contents of this file are subject to the vtiger CRM Public License Version 1.0
 * ("License"); You may not use this file except in compliance with the License
 * The Original Code is: vtiger CRM Open Source
 * The Initial Developer of the Original Code is vtiger.
 * Portions created by vtiger are Copyright (C) vtiger.
 * All Rights Reserved.
 **************************************************************************************/

require_once 'include/QueryGenerator/QueryGenerator.php';

class EnhancedQueryGenerator extends QueryGenerator {

public function __construct($module, $user) {
		parent::__construct($module, $user);
		$this->tableIndexList = array();
		$this->addTableIndexList($this->meta->getEntityTableIndexList());

$this->getModuleFields();
		$this->referenceFieldList = array_keys($this->referenceFieldInfoList);
	}

function addTableIndexList($tablesList) {
		if ($tablesList) {
			$this->tableIndexList = array_merge($this->tableIndexList, $tablesList);
		}
	}

function getTableIndexList() {
		return $this->tableIndexList;
	}

public function getModuleFields() {
		if ($this->moduleFields == null) {
			$moduleFields = parent::getModuleFields();

//add reference fields also in the list
			foreach ($moduleFields as $fieldName => $fieldModel) {
				if ($fieldModel->getFieldDataType() == 'reference') {
					$referenceModules = $fieldModel->getReferenceList();
					$this->referenceFieldInfoList[$fieldName] = $referenceModules;
					foreach ($referenceModules as $referenceModule) {
						if ($referenceModule == 'Users')
							continue; // ignore users module for now
						$referenceModuleMeta = $this->getMeta($referenceModule);
						//webserviceField does not know if its a entity name field
						$nameFields = $referenceModuleMeta->getNameFields();
						$nameFields = explode(',', $nameFields);
						// update tablenames with their indexes for reference modules
						$this->addTableIndexList($referenceModuleMeta->getEntityTableIndexList());

$referenceModuleFields = $referenceModuleMeta->getModuleFields();
						foreach ($referenceModuleFields as $referenceFieldName => $referenceFieldModel) {
							$newReferenceFieldModel = clone $referenceFieldModel;
							$name = "($fieldName ; ($referenceModule) $referenceFieldName)";
							$fieldModel->moduleName = $referenceModule;
							$newReferenceFieldModel->parentReferenceField = clone $fieldModel;
							$newReferenceFieldModel->referenceFieldName = $name;
							$newReferenceFieldModel->referenceFieldLabel = $fieldModel->getFieldLabelKey().'-'.$newReferenceFieldModel->getFieldLabelKey();
							$moduleFields[$name] = $newReferenceFieldModel;
							//update the referenceList for prefetching names
							$newReferenceFieldDataType = $newReferenceFieldModel->getFieldDataType();
							if ($newReferenceFieldDataType == 'reference') {
								$this->referenceFieldInfoList[$name] = $newReferenceFieldModel->getReferenceList();
							}
							if ($newReferenceFieldDataType == 'owner') {
								array_push($this->ownerFields, $name);
							}

// webserviceField does not have info about its modulename and name fields, so setting them here to use in listviews
							if (in_array($referenceFieldName, $nameFields)) {
								$newReferenceFieldModel->isNameField = true;
								$newReferenceFieldModel->moduleName = $referenceModule;
							}
						}
					}
				}
			}
			$this->moduleFields = $moduleFields;
		}
		return $this->moduleFields;
	}

public function parseAdvFilterList($advFilterList, $glue = '') {
		if ($glue) {
			$this->addConditionGlue($glue);
		}
		$customView = new CustomView($this->module);
		$dateSpecificConditions = $customView->getStdFilterConditions();
		$specialDateTimeConditions = Vtiger_Functions::getSpecialDateTimeCondtions();
		foreach ($advFilterList as $groupindex => $groupcolumns) {
			$filtercolumns = $groupcolumns['columns'];
			if (count($filtercolumns) > 0) {
				$this->startGroup('');
				foreach ($filtercolumns as $index => $filter) {
					//If comparator is "e" or "n" then do not escapeSqlString.
					if (!in_array($filter['comparator'], array('e', 'n'))) {
						$filter['value'] = Vtiger_Util_Helper::escapeSqlString($filter['value']);
					}
					$nameComponents = explode(':', $filter['columnname']);
					// For Events "End Date & Time" field datatype should be DT. But, db will give D for due_date field
					if ($nameComponents[2] == 'due_date' && $nameComponents[3] == 'Events_End_Date_&_Time')
						$nameComponents[4] = 'DT';
					if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') {
						$name = $this->getSQLColumn('id');
					} else {
						$name = $nameComponents[2];
					}
					if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) {
						$filter['stdfilter'] = $filter['comparator'];
						$valueComponents = explode(',', $filter['value']);
						if ($filter['comparator'] == 'custom') {
							if ($nameComponents[4] == 'DT') {
								$startDateTimeComponents = explode(' ', $valueComponents[0]);
								$endDateTimeComponents = explode(' ', $valueComponents[1]);
								$filter['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]);
								$filter['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]);
							} else {
								$filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
								$filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
							}
						}
						$dateFilterResolvedList = $customView->resolveDateFilterValue($filter);
						// If datatype is DT then we should append time also
						if ($nameComponents[4] == 'DT') {
							$startdate = explode(' ', $dateFilterResolvedList['startdate']);
							if ($startdate[1] == '')
								$startdate[1] = '00:00:00';
							$dateFilterResolvedList['startdate'] = $startdate[0].' '.$startdate[1];

$enddate = explode(' ', $dateFilterResolvedList['enddate']);
							if ($enddate[1] == '')
								$enddate[1] = '23:59:59';
							$dateFilterResolvedList['enddate'] = $enddate[0].' '.$enddate[1];
						}
						$value = array();
						$value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['startdate']);
						$value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false);
						$this->addCondition($name, $value, 'BETWEEN');
					} else if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $specialDateTimeConditions)) {
						$values = self::getSpecialDateConditionValue($filter['comparator'], $filter['value'], $nameComponents[4], true);
						$this->addCondition($name, $values['date'], $values['comparator']);
					} else if ($nameComponents[4] == 'DT' && ($filter['comparator'] == 'e' || $filter['comparator'] == 'n')) {
						$filter['stdfilter'] = $filter['comparator'];
						$dateTimeComponents = explode(' ', $filter['value']);
						$filter['startdate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]);
						$filter['enddate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]);

$startDate = $this->fixDateTimeValue($name, $filter['startdate']);
						$endDate = $this->fixDateTimeValue($name, $filter['enddate'], false);

$value = array();
						$start = explode(' ', $startDate);
						if ($start[1] == "")
							$startDate = $start[0].' '.'00:00:00';

$end = explode(' ', $endDate);
						if ($end[1] == "")
							$endDate = $end[0].' '.'23:59:59';

$value[] = $startDate;
						$value[] = $endDate;
						if ($filter['comparator'] == 'n') {
							$this->addCondition($name, $value, 'NOTEQUAL');
						} else {
							$this->addCondition($name, $value, 'BETWEEN');
						}
					} else if ($nameComponents[4] == 'DT' && ($filter['comparator'] == 'a' || $filter['comparator'] == 'b')) {
						$dateTime = explode(' ', $filter['value']);
						$date = DateTimeField::convertToDBFormat($dateTime[0]);
						$value = array();
						$value[] = $this->fixDateTimeValue($name, $date, true);
						// Still fixDateTimeValue returns only date value, we need to append time because it is DT type
						for ($i = 0; $i < count($value); $i++) {
							$values = explode(' ', $value[$i]);
							if ($values[1] == '') {
								$values[1] = '00:00:00';
							}
							$value[$i] = $values[0].' '.$values[1];
						}
						$this->addCondition($name, $value, $filter['comparator']);
					} elseif ($nameComponents[2] == 'time_start' || $nameComponents[2] == 'time_end') {
						if ($nameComponents[2] == 'time_end' && $filter['value'] == '') {
							$filter['value'] = '';
						} else {
							$filter['value'] = DateTimeField::convertToDBTimeZone($filter['value'])->format('H:i:s');
						}
						$this->addCondition($nameComponents[2], $filter['value'], $filter['comparator']);
					} else {
						$this->addCondition($name, $filter['value'], $filter['comparator']);
					}
					$columncondition = $filter['column_condition'];
					if ($columncondition) {
						$this->addConditionGlue($columncondition);
					}
				}
				$this->endGroup();
				$groupConditionGlue = $groupcolumns['condition'];
				if ($groupConditionGlue) {
					$this->addConditionGlue($groupConditionGlue);
				}
			}
		}
	}

public function getSQLColumn($name, $fieldObject = false) {
		if ($name == 'id') {
			$baseTable = $this->meta->getEntityBaseTable();
			$moduleTableIndexList = $this->getTableIndexList();
			$baseTableIndex = $moduleTableIndexList[$baseTable];
			return $baseTable.'.'.$baseTableIndex;
		}

$moduleFields = $this->getModuleFields();
		$field = $moduleFields[$name];

$referenceField = '';
		if ($fieldObject && isset($fieldObject->referenceFieldName)) {
			// if its a reference field then we need to add the fieldname to table name
			preg_match('/(\w+) ; $(\w+)$ (\w+)/', $fieldObject->referenceFieldName, $matches);
			if (count($matches) != 0) {
				list($full, $referenceField, $referenceModule, $fieldname) = $matches;
			}
			$field = $fieldObject;
		}

$column = $field->getColumnName();
		return $field->getTableName().$referenceField.'.'.$column;
	}

public function getSelectClauseColumnSQL() {
		$columns = array();
		$moduleFields = $this->getModuleFields();
		$accessibleFieldList = array_keys($moduleFields);

$moduleFields = $this->getModuleFields();

$accessibleFieldList[] = 'id';
		$this->fields = array_intersect($this->fields, $accessibleFieldList);
		foreach ($this->fields as $field) {
			// handle for reference field
			preg_match('/(\w+) ; $(\w+)$ (\w+)/', $field, $matches);
			if (count($matches) != 0) {
				list($full, $referenceField, $referenceModule, $fieldname) = $matches;
				$parentReferenceFieldModel = null;
				$parentReferenceFieldModel = $moduleFields[$field];
				if ($parentReferenceFieldModel) {
					$columns[] = $parentReferenceFieldModel->getTableName().$referenceField.'.'.$parentReferenceFieldModel->getColumnName() .
							' AS '.$referenceField.$fieldname;

//if the field is related to reference module's field, then we might need id of that record for example emails field in listviews
					$referenceModuleModelMeta = $this->getMeta($referenceModule);
					$referenceModuleTableIndex = $referenceModuleModelMeta->getEntityTableIndexList();
					$columns[] = $parentReferenceFieldModel->getTableName().$referenceField.'.'.$referenceModuleTableIndex[$parentReferenceFieldModel->getTableName()] .
							' AS '.$referenceField.$fieldname.'_id';
				}
			} else {
				$columns[] = $this->getSQLColumn($field);
			}

//To merge date and time fields
			if ($this->meta->getEntityName() == 'Calendar' && ($field == 'date_start' || $field == 'due_date' || $field == 'taskstatus' || $field == 'eventstatus')) {
				if ($field == 'date_start') {
					$timeField = 'time_start';
					$sql = $this->getSQLColumn($timeField);
				} else if ($field == 'due_date') {
					$timeField = 'time_end';
					$sql = $this->getSQLColumn($timeField);
				} else if ($field == 'taskstatus' || $field == 'eventstatus') {
					//In calendar list view, Status value = Planned is not displaying
					$sql = "CASE WHEN (vtiger_activity.status not like '') THEN vtiger_activity.status ELSE vtiger_activity.eventstatus END AS ";
					if ($field == 'taskstatus') {
						$sql .= "status";
					} else {
						$sql .= $field;
					}
				}
				$columns[] = $sql;
			}
		}
		$this->columns = implode(', ', $columns);
		return $this->columns;
	}

public function getFromClause() {
		global $current_user;
		if ($this->query || $this->fromClause) {
			return $this->fromClause;
		}
		$baseModule = $this->getModule();
		$moduleFields = $this->getModuleFields();
		$tableList = array();
		$tableJoinMapping = array();
		$tableJoinCondition = array();
		$referenceFieldTableList = array();
		$i = 1;

$modulebaseTable = $this->meta->getEntityBaseTable();
		$moduleTableIndexList = $this->getTableIndexList();
		$fieldList = $this->fields;
		foreach ($fieldList as $fieldName) {
			if ($fieldName == 'id') {
				continue;
			}
			$field = $moduleFields[$fieldName];
			if (!$field)
				continue;

$baseFieldName = $fieldName;
			$referenceParentFieldName = '';
			// for reference field do not add the table names to the list
			preg_match('/(\w+) ; $(\w+)$ (\w+)/', $fieldName, $matches);
			if (count($matches) != 0) {
				list($full, $referenceParentFieldName, $referenceModuleName, $fieldName) = $matches;
			}

$fieldType = $field->getFieldDataType();
			$tableName = $field->getTableName();

if (empty($referenceParentFieldName)) {
				// for normal base module fields
				if ($fieldType == 'owner') {
					$tableList['vtiger_users'] = 'vtiger_users';
					$tableList['vtiger_groups'] = 'vtiger_groups';
					$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
					$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
				}
				$tableList[$field->getTableName()] = $field->getTableName();
				$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());

if ($fieldName == 'roleid' && $baseModule == 'Users') {
					$tableJoinMapping['vtiger_role'] = 'INNER JOIN';
					$tableList['vtiger_role'] = 'vtiger_role';
				}
			} else {
				// handling reference fields joins
				$referenceParentFieldModel = $field->parentReferenceField;
				$referenceParentFieldModuleName = $referenceParentFieldModel->moduleName;
				$referenceParentFieldModuleMeta = $this->getMeta($referenceParentFieldModuleName);
				$moduleColumnIndex = $referenceParentFieldModuleMeta->getEntityTableIndexList();
				$referenceParentFieldTable = $referenceParentFieldModel->getTableName();
				$fieldTableName = $field->getTableName();
				$tableAlias = $fieldTableName.$referenceParentFieldName;

if ($fieldType == 'reference') {
					if (!in_array($referenceParentFieldTable, $tableList)) {
						$tableList[$referenceParentFieldTable] = $referenceParentFieldTable;
						$tableJoinMapping[$referenceParentFieldTable] = $this->meta->getJoinClause($referenceParentFieldTable);
					}

$moduleList = $field->getReferenceList();

foreach ($moduleList as $module) {
						$meta = $this->getMeta($module);
						$nameFields = $meta->getNameFields();
						$nameFieldList = explode(',', $nameFields);
						foreach ($nameFieldList as $index => $column) {
							$referenceField = $meta->getFieldByColumnName($column);
							$referenceTable = $referenceField->getTableName();
							$tableIndexList = $meta->getEntityTableIndexList();
							$referenceTableIndex = $tableIndexList[$referenceTable];

$tableAlias = $fieldTableName.$referenceParentFieldName;
							if (!array_key_exists($tableAlias, $tableJoinMapping)) {
								$tableJoinMapping[$tableAlias] = 'LEFT JOIN '.$fieldTableName.' AS';
								$tableJoinCondition[$referenceParentFieldName.$fieldName][$tableAlias] = $tableAlias.'.'.$moduleColumnIndex[$fieldTableName].' = ' .
										$referenceParentFieldTable.'.'.$referenceParentFieldModel->getColumnName();
							}
						}
					}
				} else if ($fieldType == 'owner') {
					$tableAlias = $fieldTableName.$referenceParentFieldName;
					$tableJoinMapping[$tableAlias] = 'LEFT JOIN '.$fieldTableName.' AS ';
					$tableJoinCondition[$referenceParentFieldName.$fieldName][$tableAlias] = $tableAlias.'.'.$moduleColumnIndex[$fieldTableName].' = ' .
							$referenceParentFieldModel->getTableName().'.'.$referenceParentFieldModel->getColumnName();
				} else {
					// if the reference field does not belong to base table but belongs to custom field table then we need to join it
					if (!array_key_exists($referenceParentFieldTable, $tableJoinMapping)) {
						$tableList[$referenceParentFieldTable] = $referenceParentFieldTable;
						$tableJoinMapping[$referenceParentFieldTable] = $this->meta->getJoinClause($referenceParentFieldTable);
					}

if (!array_key_exists($fieldTableName.$referenceParentFieldName, $tableJoinMapping)) {
						$tableJoinMapping[$fieldTableName.$referenceParentFieldName] = 'LEFT JOIN '.$fieldTableName.' AS ';
						$tableJoinCondition[$referenceParentFieldName.$fieldName][$fieldTableName.$referenceParentFieldName] = $fieldTableName.$referenceParentFieldName.".".$moduleColumnIndex[$fieldTableName].' = ' .
								$referenceParentFieldTable.'.'.$referenceParentFieldModel->getColumnName();
					}
				}
			}
		}

foreach ($this->whereFields as $fieldName) {
			if (empty($fieldName))
				continue;

$field = $moduleFields[$fieldName];
			if (empty($field))
				continue; // not accessible field.

$fieldType = $field->getFieldDataType();
			$fieldTable = $field->getTableName();

if (empty($referenceParentFieldName)) {
				// When a field is included in Where Clause, but not is Select Clause, and the field table is not base table,
				// The table will not be present in tablesList and hence needs to be added to the list.
				if (empty($tableList[$fieldTable])) {
					$tableList[$fieldTable] = $fieldTable;
					$tableJoinMapping[$fieldTable] = $this->meta->getJoinClause($fieldTable);
				}

if ($fieldType == 'reference') {
					$moduleList = $field->getReferenceList();
					foreach ($moduleList as $module) {
						if ($module == 'Users' && $baseModule != 'Users') {
							$tableJoinMapping['vtiger_users'.$fieldName] = 'LEFT JOIN vtiger_users AS';
							$tableJoinCondition[$fieldName]['vtiger_users'.$fieldName] = $fieldTable.'.'.$field->getColumnName().' = vtiger_users'.$fieldName.'.id';
						} else if ($module == 'Currency') {
							$tableJoinMapping['vtiger_currency_info'.$fieldName] = 'LEFT JOIN vtiger_currency_info AS';
							$tableJoinCondition[$fieldName]['vtiger_currency_info'.$fieldName] = $fieldTable.'.'.$field->getColumnName().' = vtiger_currency_info'.$fieldName.'.id';
						} else {
							$tableJoinMapping['vtiger_crmentity'.$fieldName] = 'LEFT JOIN vtiger_crmentity AS';
							$tableJoinCondition[$fieldName]['vtiger_crmentity'.$fieldName] = 'vtiger_crmentity'.$fieldName.'.crmid' .
									' = '.$fieldTable.'.'.$field->getColumnName();
						}
					}
					if ($fieldName == 'roleid' && $baseModule == 'Users') {
						$tableJoinMapping['vtiger_role'] = 'INNER JOIN';
						$tableList['vtiger_role'] = 'vtiger_role';
					}
				} else if ($fieldType == 'owner') {
					$tableList['vtiger_users'] = 'vtiger_users';
					$tableList['vtiger_groups'] = 'vtiger_groups';
					$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
					$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
				}

// if the field name is tags then we need to join with specific table 
				if ($fieldName == 'tags') {
					$tableList['vtiger_freetagged_objects'] = 'vtiger_freetagged_objects';
					$tableJoinMapping['vtiger_freetagged_objects'] = 'INNER JOIN';
				}
			} else {
				$referenceParentFieldModel = $field->parentReferenceField;
				$referenceParentFieldModuleMeta = $this->getMeta($referenceParentFieldModel->moduleName);
				$referenceModuleColumnIndex = $referenceParentFieldModuleMeta->getEntityTableIndexList();
				$referenceParentFieldTable = $referenceParentFieldModel->getTableName();

if ($fieldType == 'owner') {
					// Need to join with vtiger_crmentity table
					if (!array_key_exists($fieldTable.$referenceParentFieldName, $tableJoinMapping)) {
						$tableJoinMapping[$fieldTable.$referenceParentFieldName] = 'LEFT JOIN '.$fieldTable.' AS ';
						$tableJoinCondition[$referenceParentFieldName.$fieldName][$fieldTable.$referenceParentFieldName] = $fieldTable.$referenceParentFieldName.'.'.$referenceModuleColumnIndex[$fieldTable].' = ' .
								$referenceParentFieldTable.'.'.$referenceParentFieldModel->getColumnName();
					}

$tableJoinMapping['vtiger_users'.$referenceParentFieldName.$fieldName] = 'LEFT JOIN vtiger_users AS ';
					$tableJoinCondition[$referenceParentFieldName.$fieldName]['vtiger_users'.$referenceParentFieldName.$fieldName] = 'vtiger_users'.$referenceParentFieldName.$fieldName.'.id = '.$fieldTable.$referenceParentFieldName.'.'.$field->getColumnName();

$tableJoinMapping['vtiger_groups'.$referenceParentFieldName.$fieldName] = 'LEFT JOIN vtiger_groups AS ';
					$tableJoinCondition[$referenceParentFieldName.$fieldName]['vtiger_groups'.$referenceParentFieldName.$fieldName] = 'vtiger_groups'.$referenceParentFieldName.$fieldName.'.groupid = '.$fieldTable.$referenceParentFieldName.'.'.$field->getColumnName();
				} else if ($fieldType == 'reference') {
					$moduleList = $field->getReferenceList();
					foreach ($moduleList as $module) {
						if ($module == 'Users' && $baseModule != 'Users') {// && ($fieldName == 'created_user_id' || $fieldName == 'modifiedby')) {
							//if the reference field belong to custom table then we need to add that join too
							if (!array_key_exists($referenceParentFieldTable, $tableJoinMapping)) {
								$tableList[$referenceParentFieldTable] = $referenceParentFieldTable;
								$tableJoinMapping[$referenceParentFieldTable] = $this->meta->getJoinClause($referenceParentFieldTable);
							}
							// Need to join with vtiger_crmentity table if its not joined earlier
							if (!array_key_exists($fieldTable.$referenceParentFieldName, $tableJoinMapping)) {
								$tableJoinMapping[$fieldTable.$referenceParentFieldName] = 'LEFT JOIN '.$fieldTable.' AS ';
								$tableJoinCondition[$referenceParentFieldName.$fieldName][$fieldTable.$referenceParentFieldName] = $fieldTable.$referenceParentFieldName.'.'.$referenceModuleColumnIndex[$fieldTable].' = '.$referenceParentFieldTable.'.'.$referenceParentFieldModel->getColumnName();
							}
							$tableJoinMapping['vtiger_users'.$referenceParentFieldName.$fieldName] = 'LEFT JOIN vtiger_users AS ';
							$tableJoinCondition[$referenceParentFieldName.$fieldName]['vtiger_users'.$referenceParentFieldName.$fieldName] = 'vtiger_users'.$referenceParentFieldName.$fieldName.'.id = '.$fieldTable.$referenceParentFieldName.'.'.$field->getColumnName();
						} else if ($module == 'Currency') {
							if (!array_key_exists($fieldTable.$referenceParentFieldName, $tableJoinMapping)) {
								$tableJoinMapping[$fieldTable.$referenceParentFieldName] = 'LEFT JOIN '.$fieldTable.' AS ';
								$tableJoinCondition[$referenceParentFieldName.$fieldName][$fieldTable.$referenceParentFieldName] = $fieldTable.$referenceParentFieldName.'.'.$referenceModuleColumnIndex[$fieldTable].' = '.$referenceParentFieldTable.'.'.$referenceParentFieldModel->getColumnName();
							}

$tableJoinMapping['vtiger_currency_info'.$referenceParentFieldName.$fieldName] = 'LEFT JOIN vtiger_currency_info AS';
							$tableJoinCondition[$fieldName]['vtiger_currency_info'.$referenceParentFieldName.$fieldName] = $fieldTable.$referenceParentFieldName.'.'.$field->getColumnName().' = vtiger_currency_info'.$referenceParentFieldName.$fieldName.'.id';
						} else {
							if (!array_key_exists($fieldTable.$referenceParentFieldName, $tableJoinMapping)) {
								$tableJoinMapping[$fieldTable.$referenceParentFieldName] = 'LEFT JOIN '.$fieldTable.' AS ';
								$tableJoinCondition[$referenceParentFieldName.$fieldName][$fieldTable.$referenceParentFieldName] = $fieldTable.$referenceParentFieldName.'.'.$referenceModuleColumnIndex[$fieldTable].' = ' .
										$referenceParentFieldTable.'.'.$referenceParentFieldModel->getColumnName();
							}

$tableAlias = 'vtiger_crmentity'.$referenceParentFieldName.$fieldName;
							if (!array_key_exists($tableAlias, $tableJoinMapping)) {
								$tableJoinMapping[$tableAlias] = 'LEFT JOIN vtiger_crmentity AS ';
								$tableJoinCondition[$referenceParentFieldName.$fieldName][$tableAlias] = $tableAlias.'.crmid = '.$fieldTable.$referenceParentFieldName.'.'.$field->getColumnName();
							}
						}
					}
				} else {
					// if the reference field does not belong to base table but belongs to custom field table then we need to join it
					if (!array_key_exists($referenceParentFieldTable, $tableJoinMapping)) {
						$tableList[$referenceParentFieldTable] = $referenceParentFieldTable;
						$tableJoinMapping[$referenceParentFieldTable] = $this->meta->getJoinClause($referenceParentFieldTable);
					}

if (!array_key_exists($fieldTable.$referenceParentFieldName, $tableJoinMapping)) {
						$tableJoinMapping[$fieldTable.$referenceParentFieldName] = 'LEFT JOIN '.$fieldTable.' AS ';
						$tableJoinCondition[$referenceParentFieldName.$fieldName][$fieldTable.$referenceParentFieldName] = $fieldTable.$referenceParentFieldName.".".$referenceModuleColumnIndex[$fieldTable].' = ' .
								$referenceParentFieldTable.'.'.$referenceParentFieldModel->getColumnName();
					}
				}
			}
		}

$referenceTableList = array();
		$baseTable = $this->meta->getEntityBaseTable();
		$baseTableIndex = $moduleTableIndexList[$baseTable];

$defaultTableList = $this->meta->getEntityDefaultTableList();
		foreach ($defaultTableList as $table) {
			$tableList[$table] = $table;
			$tableJoinMapping[$table] = 'INNER JOIN';
		}
		$ownerFields = $this->meta->getOwnerFields();
		if (count($ownerFields) > 0) {
			$ownerField = $ownerFields[0];
		}

$sqlTablesList = array();
		$sql = " FROM $baseTable ";
		unset($tableList[$baseTable]);
		foreach ($defaultTableList as $tableName) {
			$sql .= " $tableJoinMapping[$tableName] $tableName ON $baseTable." .
					"$baseTableIndex = $tableName.$moduleTableIndexList[$tableName]";
			unset($tableList[$tableName]);
		}

foreach ($tableList as $tableName) {
			if ($tableName == 'vtiger_users') {
				$field = $moduleFields[$ownerField];
				$sql .= " $tableJoinMapping[$tableName] $tableName ON ".$field->getTableName()."." .
						$field->getColumnName()." = $tableName.id";
			} elseif ($tableName == 'vtiger_groups') {
				$field = $moduleFields[$ownerField];
				$sql .= " $tableJoinMapping[$tableName] $tableName ON ".$field->getTableName()."." .
						$field->getColumnName()." = $tableName.groupid";
			} elseif ($tableName == 'vtiger_freetagged_objects') {
				$sql .= " $tableJoinMapping[$tableName] $tableName ON $baseTable.$baseTableIndex = $tableName.object_id " .
						"INNER JOIN vtiger_freetags ON $tableName.tag_id = vtiger_freetags.id ";
			} elseif ($tableName == 'vtiger_role') {
				$sql .= " $tableJoinMapping[$tableName] $tableName ON vtiger_role.roleid = vtiger_user2role.roleid";
			} else {
				$tableCondition = $tableName.'.'.$moduleTableIndexList[$tableName];
				if (Vtiger_Functions::isUserSpecificFieldTable($tableName, $this->getModule())) {
					$tableCondition.= ' AND '.$tableName.'.userid='.$this->user->id;
				}
				$sql .= " $tableJoinMapping[$tableName] $tableName ON $baseTable." .
						"$baseTableIndex = $tableCondition";
			}
			$sqlTablesList[] = $tableName;
		}

if ($this->meta->getTabName() == 'Documents') {
			$tableJoinCondition['folderid'] = array(
				'vtiger_attachmentsfolderfolderid' => "$baseTable.folderid = vtiger_attachmentsfolderfolderid.folderid"
			);
			$tableJoinMapping['vtiger_attachmentsfolderfolderid'] = 'INNER JOIN vtiger_attachmentsfolder';
		}

foreach ($tableJoinCondition as $fieldName => $conditionInfo) {
			foreach ($conditionInfo as $tableName => $condition) {
				if ($tableList[$tableName]) {
					$tableNameAlias = $tableName.'2';
					$condition = str_replace($tableName, $tableNameAlias, $condition);
				} else {
					$tableNameAlias = '';
				}
				if (!in_array($tableName, $sqlTablesList)) {
					$sql .= " $tableJoinMapping[$tableName] $tableName $tableNameAlias ON $condition";
					$sqlTablesList[] = $tableName;
				}
			}
		}

$sql .= $this->meta->getEntityAccessControlQuery();
		$this->fromClause = $sql;

return $sql;
	}

public function getWhereClause() {
		global $current_user;
		if ($this->query || $this->whereClause) {
			return $this->whereClause;
		}
		$deletedQuery = $this->meta->getEntityDeletedQuery();
		$sql = '';
		if ($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) {
			$parentReferenceField = '';
			$baseFieldName = $fieldName = $conditionInfo['name'];
			$field = $moduleFieldList[$fieldName];

// if its a reference field then we need to add the fieldname to table name
			preg_match('/(\w+) ; $(\w+)$ (\w+)/', $baseFieldName, $matches);
			if (count($matches) != 0) {
				list($full, $parentReferenceField, $referenceModule, $fieldName) = $matches;
			}

if (empty($field) || $conditionInfo['operator'] == 'None') {
				continue;
			}

$tableName = $field->getTableName().$parentReferenceField;
			$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($baseFieldName, $this->referenceFieldList)) {
					if ($conditionInfo['operator'] == 'y') {
						$columnName = $field->getColumnName();
						// 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[$baseFieldName];
						if (in_array('Users', $moduleList)) {
							$columnSqlTable = 'vtiger_users'.$parentReferenceField.$fieldName;
							$columnSql = getSqlForNameInDisplayFormat(array('first_name' => $columnSqlTable.'.first_name',
								'last_name' => $columnSqlTable.'.last_name'), 'Users');
						} else if (in_array('DocumentFolders', $moduleList)) {
							$columnSql = "vtiger_attachmentsfolder".$fieldName.".foldername";
						} else if (in_array('Currency', $moduleList)) {
							$columnSql = "vtiger_currency_info$parentReferenceField$fieldName.currency_name";
						} else if ($baseFieldName == 'roleid') {
							$columnSql = 'vtiger_role.rolename';
						} else {
							$columnSql = 'vtiger_crmentity'.$parentReferenceField.$fieldName.'.label';
						}
						$fieldSql .= "$fieldGlue trim($columnSql) $valueSql";
						$fieldGlue = ' OR';
					}
				} elseif (in_array($baseFieldName, $this->ownerFields)) {
					if ($parentReferenceField)
						$ownerTableName = $parentReferenceField.$fieldName;
					else
						$ownerTableName = '';
					$concatSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users'.$ownerTableName.'.first_name',
						'last_name' => 'vtiger_users'.$ownerTableName.'.last_name'), 'Users');
					if ($conditionInfo['operator'] == 'y') {
						//if both user name and group name empty, then only should list in isempty condition
						$fieldSql .= "$fieldGlue ((trim($concatSql) $valueSql) AND (vtiger_groups$ownerTableName.groupname $valueSql))";
					} else {
						$fieldSql .= "$fieldGlue (trim($concatSql) $valueSql or vtiger_groups$ownerTableName.groupname $valueSql)";
					}
				} elseif ($field->getFieldDataType() == 'date' && ($baseModule == 'Events' || $baseModule == 'Calendar') && ($fieldName == 'date_start' || $fieldName == 'due_date')) {
					$value = $conditionInfo['value'];
					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'];
					if ($operator == 'bw') {
						$values = explode(',', $value);
						$startDateValue = explode(' ', $values[0]);
						$endDateValue = explode(' ', $values[1]);
						if (empty($startDateValue[1]) && empty($endDateValue[1])) {
							$fieldSql .= "$fieldGlue date(".$tableName.'.'.$field->getColumnName().') '.$valueSql;
						} else {
							$fieldSql .= "$fieldGlue ".$tableName.'.'.$field->getColumnName().' '.$valueSql;
						}
					} elseif ($operator == 'between' || $operator == 'notequal' || $operator == 'a' || $operator == 'b') {
						$fieldSql .= "$fieldGlue ".$tableName.'.'.$field->getColumnName().' '.$valueSql;
					} else {
						$values = explode(' ', $value);
						if ($values[1] == '00:00:00') {
							$fieldSql .= "$fieldGlue CAST(".$$tableName.'.'.$field->getColumnName()." AS DATE) $valueSql";
						} else {
							$fieldSql .= "$fieldGlue ".$tableName.'.'.$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 '.$tableName.'.'.$field->getColumnName().' IS NULL ';
							if ($otherField) {
								$specialConditionForOtherField = ' OR '.$otherField->getTableName().'.'.$otherField->getColumnName().' IS NULL ';
							}
						}
					}

$otherFieldValueSql = $valueSql;
					if ($conditionInfo['operator'] == 'ny' && $otherField) {
						$otherFieldValueSql = "IS NOT NULL AND ".$otherField->getTableName().'.'.$otherField->getColumnName()." != ''";
					}

$fieldSql .= "$fieldGlue ((".$tableName.'.'.$field->getColumnName().' '.$valueSql." $specialCondition) ";
					if ($otherField) {
						$fieldSql .= $conditionGlue.'('.$otherField->getTableName().'.'.$otherField->getColumnName().' '.$otherFieldValueSql.' '.$specialConditionForOtherField.'))';
					} else {
						$fieldSql .= ')';
					}
				}else if (Vtiger_Functions::isUserSpecificFieldTable($field->getTableName(), getTabModuleName($field->getTabId())) && $fieldName == "starred" && $conditionInfo['value'] != 1) {
					// since not for all records you will have entry in starred field table. So for disabled (value 0) we need to check both 0 and null
					$fieldSql .= "$fieldGlue (".$field->getTableName().'.'.$field->getColumnName().' '.$valueSql.' OR ';
					$fieldSql .= $field->getTableName().'.'.$field->getColumnName().' IS NULL)';
				} else if ($fieldName == "tags") {
					$fieldSql .= " $fieldGlue ( vtiger_freetags.id ".$valueSql.' AND ' .
							'( vtiger_freetagged_objects.tagger_id = '.$this->user->id.' OR vtiger_freetags.visibility = "public")) ';
				} else {
					if ($fieldName == 'birthday' && !$this->isRelativeSearchOperators($conditionInfo['operator'])) {
						$fieldSql .= "$fieldGlue DATE_FORMAT(".$tableName.'.' .
								$field->getColumnName().",'%m%d') ".$valueSql;
					} else {
						if ($conditionInfo['operator'] == 'n' && $field->getFieldDataType() == 'multipicklist') {
							$specialCondition = ' OR '.$tableName.'.'.$field->getColumnName().' IS NULL ';
							$fieldSql .= "$fieldGlue ".$tableName.'.' .
									$field->getColumnName().' '.$valueSql.$specialCondition;
						} else {
							$fieldSql .= "$fieldGlue ".$tableName.'.' .
									$field->getColumnName().' '.$valueSql;
						}
					}
				}
				if (($conditionInfo['operator'] == 'n' || $conditionInfo['operator'] == 'k') && ($field->getFieldDataType() == 'owner' ||
						$field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) {
					$fieldGlue = ' AND';
				} else {
					$fieldGlue = ' OR';
				}
			}
			$tmpTableName = 'vtiger_crmentity'.$parentReferenceField;
			if ($tmpTableName == $tableName && $referenceModule) {
				$fieldSql .= " and ".$tmpTableName.".setype = '".$referenceModule."'";
			}
			$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;
		}

$currentUserModel = Users_Record_Model::getCurrentUserModel();
		if(($baseModule == 'Calendar' || $baseModule == 'Events') && !$currentUserModel->isAdminUser()) {
			$moduleFocus = CRMEntity::getInstance('Calendar');
			$condition = $moduleFocus->buildWhereClauseConditionForCalendar();

if($condition) {
				if($this->conditionInstanceCount > 0) {
					$sql .= $condition.' AND ';
				}else {
					$sql .= ' AND '.$condition;
				}
			}
		}

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

/**
	 * Function returns table column for the given sort field name
	 * @param <String> $fieldName
	 * @return <String> columnname
	 */
	function getOrderByColumn($fieldName) {
		$fieldList = $this->getModuleFields();
		$orderByFieldModel = $fieldList[$fieldName];

$parentReferenceField = '';
		preg_match('/(\w+) ; $(\w+)$ (\w+)/', $fieldName, $matches);
		if (count($matches) != 0) {
			list($full, $parentReferenceField, $referenceModule, $fieldName) = $matches;
		}
		if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == 'reference') {
			$referenceModules = $orderByFieldModel->getReferenceList();
			if (in_array('DocumentFolders', $referenceModules)) {
				$orderByColumn = "vtiger_attachmentsfolder".$orderByFieldModel->getFieldName().".foldername";
			} else if (in_array('Currency', $referenceModules)) {
				if ($parentReferenceField) {
					$orderByColumn = 'vtiger_currency_info'.$parentReferenceField.$orderByFieldModel->getFieldName().'.currency_name';
				} else {
					$orderByColumn = 'vtiger_currency_info'.$fieldName.'.currency_name';
				}
			} else if (in_array('Users', $referenceModules)) {
				$columnSqlTable = 'vtiger_users'.$parentReferenceField.$fieldName;
				$orderByColumn = getSqlForNameInDisplayFormat(array('first_name' => $columnSqlTable.'.first_name',
					'last_name' => $columnSqlTable.'.last_name'), 'Users');
			} else {
				$orderByColumn = 'vtiger_crmentity'.$parentReferenceField.$orderByFieldModel->getFieldName().'.label'; //.$fieldModel->get('column');
			}
		} else if ($orderByFieldModel && $orderByFieldModel->getFieldDataType() == 'owner') {
			if ($parentReferenceField) {
				$userTableName = 'vtiger_users'.$parentReferenceField.$orderByFieldModel->getFieldName();
				$groupTableName = 'vtiger_groups'.$parentReferenceField.$orderByFieldModel->getFieldName();
				$orderByColumn = "COALESCE(CONCAT($userTableName.first_name,$userTableName.last_name),$groupTableName.groupname)";
			} else {
				$orderByColumn = 'COALESCE(CONCAT(vtiger_users.first_name,vtiger_users.last_name),vtiger_groups.groupname)';
			}
		} else if (($orderByFieldModel->getFieldName() == 'taskstatus' || $orderByFieldModel->getFieldName() == 'eventstatus') && $this->module == 'Calendar') {
			$orderByColumn = 'status';
		} else if ($orderByFieldModel) {
			$orderByColumn = $orderByFieldModel->getTableName().$parentReferenceField.'.'.$orderByFieldModel->getColumnName();
		}
		return $orderByColumn;
	}

/**
	 * Function get the date value and comparator
	 * @param <string> $comparator - date comparator
	 * @param <integer> $value - date value
	 * @param <string> $type - date type D or DT
	 * @param <boolean> $queryGenerator - for querygenerator the format is different
	 * @return <array> with comparator and calculated date values
	 */
	static function getSpecialDateConditionValue($comparator, $value, $type, $queryGenerator = false) {
		switch ($comparator) {
			case 'lessthandaysago' : $days = $value;
				$olderDate = date('Y-m-d', strtotime('-'.$days.' days'));
				$today = date('Y-m-d');
				if ($queryGenerator) {
					return array('comparator' => 'bw', 'date' => $olderDate.",".$today);
				}

return array('comparator' => 'bw', 'date' => array($olderDate, $today));

case 'morethandaysago' : $days = $value - 1;
				$olderDate = date('Y-m-d', strtotime('-'.$days.' days'));
				return array('comparator' => 'l', 'date' => $olderDate);

case 'inlessthan' : $days = $value;
				$today = date('Y-m-d');
				$futureDate = date('Y-m-d', strtotime('+'.$days.' days'));
				if ($queryGenerator) {
					return array('comparator' => 'bw', 'date' => $today.",".$futureDate);
				}

return array('comparator' => 'bw', 'date' => array($today, $futureDate));

case 'inmorethan' : $days = $value - 1;
				$futureDate = date('Y-m-d', strtotime('+'.$days.' days'));
				return array('comparator' => 'g', 'date' => $futureDate);

case 'daysago' : $olderDate = date('Y-m-d', strtotime('-'.$value.' days'));
				if ($type == 'DT') {
					return array('comparator' => 'c', 'date' => $olderDate);
				}

return array('comparator' => 'e', 'date' => $olderDate);

case 'dayslater' : $futureDate = date('Y-m-d', strtotime('+'.$value.' days'));
				if ($type == 'DT') {
					return array('comparator' => 'c', 'date' => $futureDate);
				}

return array('comparator' => 'e', 'date' => $futureDate);

case 'lessthanhoursbefore' : $currentTime = date('Y-m-d H:i:s');
				$olderDateTime = date('Y-m-d H:i:s', strtotime('-'.$value.' hours'));
				if ($queryGenerator) {
					// convert to user format
					$currentDateTimeInstance = new DateTimeField($currentTime);
					$currentTime = $currentDateTimeInstance->getDisplayDateTimeValue();
					$olderDateTimeInstance = new DateTimeField($olderDateTime);
					$olderDateTime = $olderDateTimeInstance->getDisplayDateTimeValue();

return array('comparator' => 'bw', 'date' => $olderDateTime.",".$currentTime);
				}

return array('comparator' => 'bw', 'date' => array($olderDateTime, $currentTime));

case 'lessthanhourslater' : $currentTime = date('Y-m-d H:i:s');
				$futureDateTime = date('Y-m-d H:i:s', strtotime('+'.$value.' hours'));
				if ($queryGenerator) {
					// convert to user format
					$currentDateTimeInstance = new DateTimeField($currentTime);
					$currentTime = $currentDateTimeInstance->getDisplayDateTimeValue();
					$futureDateTimeInstance = new DateTimeField($futureDateTime);
					$futureDateTime = $futureDateTimeInstance->getDisplayDateTimeValue();

return array('comparator' => 'bw', 'date' => $currentTime.",".$futureDateTime);
				}

return array('comparator' => 'bw', 'date' => array($currentTime, $futureDateTime));

case 'morethanhoursbefore' : $olderDateTime = date('Y-m-d H:i:s', strtotime('-'.$value.' hours'));
				if ($queryGenerator) {
					// convert to user format
					$olderDateTimeInstance = new DateTimeField($olderDateTime);
					$olderDateTime = $olderDateTimeInstance->getDisplayDateTimeValue();
				}

return array('comparator' => 'l', 'date' => $olderDateTime);

case 'morethanhourslater' : $futureDateTime = date('Y-m-d H:i:s', strtotime('+'.$value.' hours'));
				if ($queryGenerator) {
					// convert to user format
					$futureDateTimeInstance = new DateTimeField($futureDateTime);
					$futureDateTime = $futureDateTimeInstance->getDisplayDateTimeValue();
				}

return array('comparator' => 'g', 'date' => $futureDateTime);

default : return '';
		}
	}

/**
	 * Function to get the condition query for special date condtions
	 * @param <string> $comparator
	 * @param <date> or <array> $date - date value or array of dates for between condition 
	 * @return <string> condition query
	 */
	static function getSpecialDateConditionQuery($comparator, $date) {
		switch ($comparator) {
			case 'bw' : return " BETWEEN '$date[0]' AND '$date[1]' ";

case 'l' : return " < '$date' ";

case 'g' : return " > '$date' ";

case 'e' : return " = '$date' ";

case 'c' : return " LIKE '%$date%' ";

default : return '';
		}
	}

}

[+] ..
[-] QueryGenerator.php [edit]
[-] EnhancedQueryGenerator.php [edit]