|
Server : Apache System : Linux srv.rainic.com 4.18.0-553.47.1.el8_10.x86_64 #1 SMP Wed Apr 2 05:45:37 EDT 2025 x86_64 User : rainic ( 1014) PHP Version : 7.4.33 Disable Function : exec,passthru,shell_exec,system Directory : /home/akaindir/www/crm/include/QueryGenerator/ |
Upload File : |
<?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.
$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();
$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 '';
}
}
}