Newer
Older
<?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 'data/CRMEntity.php';
require_once 'modules/CustomView/CustomView.php';
require_once 'include/Webservices/Utils.php';
require_once 'include/Webservices/RelatedModuleMeta.php';
/**
* Description of QueryGenerator
*
* @author MAK
*/
#[\AllowDynamicProperties]
protected $module;
protected $customViewColumnList;
protected $stdFilterList;
protected $conditionals;
protected $manyToManyRelatedModuleConditions;
protected $groupType;
protected $whereFields;
protected $user;
protected $advFilterList;
protected $fields;
protected $referenceModuleMetaInfo;
protected $moduleNameFields;
protected $referenceFieldInfoList;
protected $referenceFieldList;
protected $ownerFields;
protected $columns;
protected $fromClause;
protected $whereClause;
protected $query;
protected $groupInfo;
public static $AND = 'AND';
public static $OR = 'OR';
public function __construct($module, $user) {
$db = PearDatabase::getInstance();
$this->module = $module;
$this->customViewColumnList = null;
$this->stdFilterList = null;
$this->conditionals = array();
$this->user = $user;
$this->advFilterList = null;
$this->fields = array();
$this->referenceModuleMetaInfo = array();
$this->moduleNameFields = array();
$this->whereFields = array();
$this->groupType = self::$AND;
$this->meta = $this->getMeta($module);
$this->moduleNameFields[$module] = $this->meta->getNameFields();
$this->referenceFieldInfoList = $this->meta->getReferenceFieldDetails();
$this->referenceFieldList = array_keys($this->referenceFieldInfoList);
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
$this->ownerFields = $this->meta->getOwnerFields();
$this->columns = null;
$this->fromClause = null;
$this->whereClause = null;
$this->query = null;
$this->conditionalWhere = null;
$this->groupInfo = '';
$this->manyToManyRelatedModuleConditions = array();
$this->conditionInstanceCount = 0;
$this->customViewFields = array();
}
/**
*
* @param String:ModuleName $module
* @return EntityMeta
*/
public function getMeta($module) {
$db = PearDatabase::getInstance();
if (empty($this->referenceModuleMetaInfo[$module])) {
$handler = vtws_getModuleHandlerFromName($module, $this->user);
$meta = $handler->getMeta();
$this->referenceModuleMetaInfo[$module] = $meta;
$this->moduleNameFields[$module] = $meta->getNameFields();
}
return $this->referenceModuleMetaInfo[$module];
}
public function reset() {
$this->fromClause = null;
$this->whereClause = null;
$this->columns = null;
$this->query = null;
}
public function setFields($fields) {
$this->fields = $fields;
}
public function getCustomViewFields() {
return $this->customViewFields;
}
public function getFields() {
return $this->fields;
}
public function getWhereFields() {
return $this->whereFields;
}
public function addWhereField($fieldName) {
$this->whereFields[] = $fieldName;
}
public function getOwnerFieldList() {
return $this->ownerFields;
}
public function getModuleNameFields($module) {
return $this->moduleNameFields[$module];
}
public function getReferenceFieldList() {
return $this->referenceFieldList;
}
public function getReferenceFieldInfoList() {
return $this->referenceFieldInfoList;
}
public function getModule () {
return $this->module;
}
public function getModuleFields() {
$moduleFields = $this->meta->getModuleFields();
$module = $this->getModule();
if($module == 'Calendar') {
$eventmoduleMeta = $this->getMeta('Events');
$eventModuleFieldList = $eventmoduleMeta->getModuleFields();
$moduleFields = array_merge($moduleFields, $eventModuleFieldList);
}
return $moduleFields;
}
public function getConditionalWhere() {
return $this->conditionalWhere;
}
public function getDefaultCustomViewQuery() {
$customView = new CustomView($this->module);
$viewId = $customView->getViewId($this->module);
return $this->getCustomViewQueryById($viewId);
}
public function initForDefaultCustomView() {
$customView = new CustomView($this->module);
$viewId = $customView->getViewId($this->module);
$this->initForCustomViewById($viewId);
}
public function initForCustomViewById($viewId) {
$customView = new CustomView($this->module);
$this->customViewColumnList = $customView->getColumnsListByCvid($viewId);
if ($this->customViewColumnList && is_array($this->customViewColumnList)) {
foreach ($this->customViewColumnList as $customViewColumnInfo) {
$details = explode(':', $customViewColumnInfo);
if(empty($details[2]) && $details[1] == 'crmid' && $details[0] == 'vtiger_crmentity') {
$name = 'id';
$this->customViewFields[] = $name;
} else {
$this->fields[] = $details[2];
$this->customViewFields[] = $details[2];
}
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
}
}
if($this->module == 'Calendar' && !in_array('activitytype', $this->fields)) {
$this->fields[] = 'activitytype';
}
if($this->module == 'Documents') {
if(in_array('filename', $this->fields)) {
if(!in_array('filelocationtype', $this->fields)) {
$this->fields[] = 'filelocationtype';
}
if(!in_array('filestatus', $this->fields)) {
$this->fields[] = 'filestatus';
}
}
}
$this->fields[] = 'id';
$this->stdFilterList = $customView->getStdFilterByCvid($viewId);
$this->advFilterList = $customView->getAdvFilterByCvid($viewId);
if(is_array($this->stdFilterList)) {
$value = array();
if(!empty($this->stdFilterList['columnname'])) {
$this->startGroup('');
$name = explode(':',$this->stdFilterList['columnname']);
$name = $name[2];
$value[] = $this->fixDateTimeValue($name, $this->stdFilterList['startdate']);
$value[] = $this->fixDateTimeValue($name, $this->stdFilterList['enddate'], false);
$this->addCondition($name, $value, 'BETWEEN');
}
}
if($this->conditionInstanceCount <= 0 && is_array($this->advFilterList) && php7_count($this->advFilterList) > 0) {
} elseif($this->conditionInstanceCount > 0 && is_array($this->advFilterList) && php7_count($this->advFilterList) > 0) {
if(is_array($this->advFilterList) && php7_count($this->advFilterList) > 0) {
$this->parseAdvFilterList($this->advFilterList);
}
if($this->conditionInstanceCount > 0) {
$this->endGroup();
}
}
public function parseAdvFilterList($advFilterList, $glue=''){
if(!empty($glue)) $this->addConditionGlue($glue);
$customView = new CustomView($this->module);
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advFilterList as $groupindex=>$groupcolumns) {
$filtercolumns = $groupcolumns['columns'];
if(php7_count($filtercolumns) > 0) {
$this->startGroup('');
foreach ($filtercolumns as $index=>$filter) {
$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] == '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';
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, false);
// Still fixDateTimeValue returns only date value, we need to append time because it is DT type
for($i=0;$i<php7_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']);
} else{
$this->addCondition($name, $filter['value'], $filter['comparator']);
}
$columncondition = $filter['column_condition'];
if(!empty($columncondition)) {
$this->addConditionGlue($columncondition);
}
}
$this->endGroup();
$groupConditionGlue = $groupcolumns['condition'];
if(!empty($groupConditionGlue))
$this->addConditionGlue($groupConditionGlue);
}
}
}
public function getCustomViewQueryById($viewId) {
$this->initForCustomViewById($viewId);
return $this->getQuery();
}
public function getQuery() {
if(empty($this->query)) {
$conditionedReferenceFields = array();
$allFields = array_merge($this->fields, (array)$this->whereFields);
foreach ($allFields as $fieldName) {
if(in_array($fieldName,$this->referenceFieldList)) {
$moduleList = $this->referenceFieldInfoList[$fieldName];
foreach ($moduleList as $module) {
if(empty($this->moduleNameFields[$module])) {
$meta = $this->getMeta($module);
}
}
} elseif(in_array($fieldName, $this->ownerFields )) {
$meta = $this->getMeta('Users');
$meta = $this->getMeta('Groups');
}
}
$query = "SELECT ";
$query .= $this->getSelectClauseColumnSQL();
$query .= $this->getFromClause();
$query .= $this->getWhereClause();
$this->query = $query;
return $query;
} else {
return $this->query;
}
}
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
if ($name == 'id') {
$baseTable = $this->meta->getEntityBaseTable();
$moduleTableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $moduleTableIndexList[$baseTable];
return $baseTable.'.'.$baseTableIndex;
}
$moduleFields = $this->getModuleFields();
$field = $moduleFields[$name];
$sql = '';
//TODO optimization to eliminate one more lookup of name, incase the field refers to only
//one module or is of type owner.
$column = $field->getColumnName();
return $field->getTableName().'.'.$column;
}
public function getSelectClauseColumnSQL(){
$columns = array();
$moduleFields = $this->getModuleFields();
$accessibleFieldList = array_keys($moduleFields);
$accessibleFieldList[] = 'id';
$this->fields = array_intersect($this->fields, $accessibleFieldList);
foreach ($this->fields as $field) {
$sql = $this->getSQLColumn($field);
$columns[] = $sql;
//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(!empty($this->query) || !empty($this->fromClause)) {
return $this->fromClause;
}
$baseModule = $this->getModule();
$moduleFields = $this->getModuleFields();
$tableList = array();
$tableJoinMapping = array();
$tableJoinCondition = array();
$i =1;
$moduleTableIndexList = $this->meta->getEntityTableIndexList();
foreach ($this->fields as $fieldName) {
if ($fieldName == 'id') {
continue;
}
$field = $moduleFields[$fieldName];
$baseTable = $field->getTableName();
$tableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $tableIndexList[$baseTable];
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] = $this->meta->getJoinClause($field->getTableName());
if($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
foreach($moduleList as $module) {
if($module == 'Users' && $baseModule != 'Users') {
if($fieldName == 'created_user_id' || $fieldName == 'modifiedby') {
$tableJoinCondition[$fieldName]['vtiger_users'.$fieldName] = $field->getTableName().
".".$field->getColumnName()." = vtiger_users".$fieldName.".id";
$tableJoinMapping['vtiger_users'.$fieldName] = 'LEFT JOIN vtiger_users AS';
$i++;
} else {
$tableJoinCondition[$fieldName]['vtiger_users'.$fieldName] = $field->getTableName().
".".$field->getColumnName()." = vtiger_users".$fieldName.".id";
$tableJoinCondition[$fieldName]['vtiger_groups'.$fieldName] = $field->getTableName().
".".$field->getColumnName()." = vtiger_groups".$fieldName.".groupid";
$tableJoinMapping['vtiger_users'.$fieldName] = 'LEFT JOIN vtiger_users AS';
$tableJoinMapping['vtiger_groups'.$fieldName] = 'LEFT JOIN vtiger_groups AS';
$i++;
}
if($fieldName == 'roleid' && $baseModule == 'Users') {
$tableJoinMapping['vtiger_role'] = 'INNER JOIN';
$tableList['vtiger_role'] = 'vtiger_role';
}
} elseif($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
if($fieldName == "created_user_id"){
$tableJoinCondition[$fieldName]['vtiger_users'.$fieldName] = $field->getTableName().
".".$field->getColumnName()." = vtiger_users".$fieldName.".id";
$tableJoinMapping['vtiger_users'.$fieldName] = 'LEFT JOIN vtiger_users AS';
}
}
}
$baseTable = $this->meta->getEntityBaseTable();
$baseTableIndex = $moduleTableIndexList[$baseTable];
foreach ($this->whereFields as $fieldName) {
if(empty($fieldName)) {
continue;
}
$field = $moduleFields[$fieldName];
if(empty($field)) {
// not accessible field.
continue;
}
$baseTable = $field->getTableName();
// 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[$baseTable])) {
if($fieldName == 'tags') {
$tableList['vtiger_freetagged_objects'] = 'vtiger_freetagged_objects';
$tableJoinMapping['vtiger_freetagged_objects'] = 'INNER JOIN';
}else{
$tableList[$baseTable] = $field->getTableName();
$tableJoinMapping[$baseTable] = $this->meta->getJoinClause($field->getTableName());
}
}
if($field->getFieldDataType() == 'reference') {
$moduleList = $this->referenceFieldInfoList[$fieldName];
// This is special condition as the data is not stored in the base table,
// If empty search is performed on this field then it fails to retrieve any information.
if($fieldName == 'parent_id' && $field->getTableName() == 'vtiger_seactivityrel') {
$tableJoinMapping[$field->getTableName()] = 'LEFT JOIN';
} else if($fieldName == 'contact_id' && $field->getTableName() == 'vtiger_cntactivityrel') {
$tableJoinMapping[$field->getTableName()] = "LEFT JOIN";
} else {
$tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
}
foreach($moduleList as $module) {
$meta = $this->getMeta($module);
$nameFields = $this->moduleNameFields[$module];
$nameFieldList = explode(',',$nameFields);
foreach ($nameFieldList as $index=>$column) {
$referenceField = $meta->getFieldByColumnName($column);
$referenceTable = $referenceField->getTableName();
$tableIndexList = $meta->getEntityTableIndexList();
$referenceTableIndex = $tableIndexList[$referenceTable];
$referenceTableName = "$referenceTable $referenceTable$fieldName";
$referenceTable = "$referenceTable$fieldName";
//should always be left join for cases where we are checking for null
//reference field values.
if(!array_key_exists($referenceTable, $tableJoinMapping)) { // table already added in from clause
$tableJoinMapping[$referenceTableName] = 'LEFT JOIN';
$tableJoinCondition[$fieldName][$referenceTableName] = $baseTable.'.'.
$field->getColumnName().' = '.$referenceTable.'.'.$referenceTableIndex;
}
}
}
if($fieldName == 'roleid' && $baseModule == 'Users') {
$tableJoinMapping['vtiger_role'] = 'INNER JOIN';
$tableList['vtiger_role'] = 'vtiger_role';
}
} elseif($field->getFieldDataType() == 'owner') {
$tableList['vtiger_users'] = 'vtiger_users';
$tableList['vtiger_groups'] = 'vtiger_groups';
$tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
$tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
} else {
$tableList[$field->getTableName()] = $field->getTableName();
$tableJoinMapping[$field->getTableName()] =
$this->meta->getJoinClause($field->getTableName());
}
}
$defaultTableList = $this->meta->getEntityDefaultTableList();
foreach ($defaultTableList as $table) {
if(!in_array($table, $tableList)) {
$tableList[$table] = $table;
$tableJoinMapping[$table] = 'INNER JOIN';
}
}
$ownerFields = $this->meta->getOwnerFields();
if (php7_count($ownerFields) > 0) {
}
$baseTable = $this->meta->getEntityBaseTable();
$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";
$tableCondition = $tableName.'.'.$moduleTableIndexList[$tableName];
if(Vtiger_Functions::isUserSpecificFieldTable($tableName, $this->getModule())) {
$tableCondition.= ' AND '.$tableName.'.userid='.$this->user->id;
}
$sql .= " $tableJoinMapping[$tableName] $tableName ON $baseTable.".
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
}
}
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(!empty($tableList[$tableName])) {
$tableNameAlias = $tableName.'2';
$condition = str_replace($tableName, $tableNameAlias, $condition);
} else {
$tableNameAlias = '';
}
$sql .= " $tableJoinMapping[$tableName] $tableName $tableNameAlias ON $condition";
}
}
foreach ($this->manyToManyRelatedModuleConditions as $conditionInfo) {
$relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(),
$conditionInfo['relatedModule']);
$relationInfo = $relatedModuleMeta->getRelationMeta();
$relatedModule = $this->meta->getTabName();
$sql .= ' INNER JOIN '.$relationInfo['relationTable']." ON ".
$relationInfo['relationTable'].".$relationInfo[$relatedModule]=".
"$baseTable.$baseTableIndex";
}
// Adding support for conditions on reference module fields
if($this->referenceModuleField) {
$referenceFieldTableList = array();
foreach ($this->referenceModuleField as $index=>$conditionInfo) {
$handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user);
$meta = $handler->getMeta();
$tableList = $meta->getEntityTableIndexList();
$fieldName = $conditionInfo['fieldName'];
$referenceFieldObject = $moduleFields[$conditionInfo['referenceField']];
$fields = $meta->getModuleFields();
$fieldObject = $fields[$fieldName];
if(empty($fieldObject)) continue;
$tableName = $fieldObject->getTableName();
if(!in_array($tableName, $referenceFieldTableList)) {
if($referenceFieldObject->getFieldName() == 'parent_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$sql .= ' LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid ';
}
//TODO : this will create duplicates, need to find a better way
if($referenceFieldObject->getFieldName() == 'contact_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
$sql .= ' LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid ';
}
$sql .= " LEFT JOIN ".$tableName.' AS '.$tableName.$conditionInfo['referenceField'].' ON
'.$tableName.$conditionInfo['referenceField'].'.'.$tableList[$tableName].'='.
$referenceFieldObject->getTableName().'.'.$referenceFieldObject->getColumnName();
$referenceFieldTableList[] = $tableName;
}
}
}
$sql .= $this->meta->getEntityAccessControlQuery();
$this->fromClause = $sql;
return $sql;
}
public function getWhereClause() {
global $current_user;
if(!empty($this->query) || !empty($this->whereClause)) {
return $this->whereClause;
}
$deletedQuery = $this->meta->getEntityDeletedQuery();
$sql = '';
if(!empty($deletedQuery)) {
$sql .= " WHERE $deletedQuery";
}
if($this->conditionInstanceCount > 0) {
$sql .= ' AND ';
} elseif(empty($deletedQuery)) {
$sql .= ' WHERE ';
}
$baseModule = $this->getModule();
$moduleFieldList = $this->getModuleFields();
$baseTable = $this->meta->getEntityBaseTable();
$moduleTableIndexList = $this->meta->getEntityTableIndexList();
$baseTableIndex = $moduleTableIndexList[$baseTable];
$groupSql = $this->groupInfo;
$fieldSqlList = array();
$fieldName = $conditionInfo['name'];
$field = $moduleFieldList[$fieldName];
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(php7_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(php7_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(php7_count($this->ownerFields) > 0 ||
$this->getModule() == 'Quotes' || $this->getModule() == 'PriceBooks') {
$referenceTable .= $fieldName;
}
} else {
$referenceField = $meta->getFieldByColumnName($column);
$referenceTable = $referenceField->getTableName().$fieldName;
}
if(isset($moduleTableIndexList[$referenceTable])) {
$referenceTable = "$referenceTable$fieldName";
}
$columnList[] = "$referenceTable.$column";
}
if(php7_count($columnList) > 1) {
if ($module == "Users") {
// Special case
$columnSql = getSqlForNameInDisplayFormat(array('first_name'=>$columnList[0],'last_name'=>$columnList[1]),'Users');
} else {
// Leads or contacts
$columnSql = getSqlForNameInDisplayFormat(array('firstname'=>$columnList[0],'lastname'=>$columnList[1]), $module);
}
} else {
$columnSql = implode('', $columnList);
}
$fieldSql .= "$fieldGlue trim($columnSql) $valueSql";
$fieldGlue = ' OR';
}
if ($fieldName == 'roleid'){
$columnSql = 'vtiger_role.rolename';
$fieldSql .= "$fieldGlue trim($columnSql) $valueSql";
$fieldGlue = ' OR';
}
}
} elseif (in_array($fieldName, $this->ownerFields)) {
if($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{
$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(php7_count($startDateValue) == 2 && php7_count($endDateValue) == 2) {
Akshath
committed
$fieldSql .= " CONCAT($dateFieldColumnName,' ',$timeFieldColumnName) $valueSql";
} else {
$fieldSql .= "$dateFieldColumnName $valueSql";
}
} else {
if(is_array($value)){
$value = $value[0];
}
$values = explode(' ', $value);
if(php7_count($values) == 2) {
Akshath
committed
$fieldSql .= "$fieldGlue CONCAT($dateFieldColumnName,' ',$timeFieldColumnName) $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')
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
&& ($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 (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(".$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;
}
}
Prasad
committed
$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;
}
/**
*
* @param mixed $value
* @param String $operator
* @param WebserviceField $field
*/
protected function getConditionValue($value, $operator, $field) {
$operator = strtolower($operator);
$db = PearDatabase::getInstance();
$commaSeparatedFieldTypes = array('picklist', 'multipicklist', 'owner', 'date', 'datetime', 'time');
if(in_array($operator, array('s', 'ew', 'c', 'k')) || in_array($field->getFieldDataType(), $commaSeparatedFieldTypes) || ($field->getFieldName() == 'salutationtype') || ($field->getFieldDataType()=='reference' && in_array('Users', $field->getReferenceList()))) {
$valueArray = explode(',' , $value);
if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) {
$valueArray = getCombinations($valueArray);
foreach ($valueArray as $key => $value) {
$valueArray[$key] = ltrim($value, ' |##| ');
}
}
} else {
$valueArray = array($value);
}
} elseif(is_array($value)) {
$valueArray = $value;
} else{
$valueArray = array($value);
}
$sql = array();
if($operator == 'between' || $operator == 'bw' || $operator == 'notequal') {
if($field->getFieldName() == 'birthday') {
$valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
$valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
$sql[] = "BETWEEN DATE_FORMAT(".$db->quote($valueArray[0]).", '%m%d') AND ".
"DATE_FORMAT(".$db->quote($valueArray[1]).", '%m%d')";
} else {
if($this->isDateType($field->getFieldDataType())) {
$start = explode(' ', $valueArray[0]);
$end = explode(' ',$valueArray[1]);
if($operator == 'between' && php7_count($start) == 2 && php7_count($end) == 2){
$valueArray[0] = getValidDBInsertDateTimeValue($start[0].' '.$start[1]);
if($start[0] == $end[0]){