[Summary view] [Print] [Text view]

   1  <?php
   2  /*+*******************************************************************************
   3   *  The contents of this file are subject to the vtiger CRM Public License Version 1.0
   4   * ("License"); You may not use this file except in compliance with the License
   5   * The Original Code is:  vtiger CRM Open Source
   6   * The Initial Developer of the Original Code is vtiger.
   7   * Portions created by vtiger are Copyright (C) vtiger.
   8   * All Rights Reserved.
   9   *
  10   *********************************************************************************/
  11  
  12  require_once  'data/CRMEntity.php';
  13  require_once  'modules/CustomView/CustomView.php';
  14  require_once  'include/Webservices/Utils.php';
  15  require_once  'include/Webservices/RelatedModuleMeta.php';
  16  
  17  /**
  18   * Description of QueryGenerator
  19   *
  20   * @author MAK
  21   */
  22  class QueryGenerator {
  23      private $module;
  24      private $customViewColumnList;
  25      private $stdFilterList;
  26      private $conditionals;
  27      private $manyToManyRelatedModuleConditions;
  28      private $groupType;
  29      private $whereFields;
  30      /**
  31       *
  32       * @var VtigerCRMObjectMeta
  33       */
  34      private $meta;
  35      /**
  36       *
  37       * @var Users
  38       */
  39      private $user;
  40      private $advFilterList;
  41      private $fields;
  42      private $referenceModuleMetaInfo;
  43      private $moduleNameFields;
  44      private $referenceFieldInfoList;
  45      private $referenceFieldList;
  46      private $ownerFields;
  47      private $columns;
  48      private $fromClause;
  49      private $whereClause;
  50      private $query;
  51      private $groupInfo;
  52      public $conditionInstanceCount;
  53      private $conditionalWhere;
  54      public static $AND = 'AND';
  55      public static $OR = 'OR';
  56      private $customViewFields;
  57      /**
  58       * Import Feature
  59       */
  60      private $ignoreComma;
  61  	public function __construct($module, $user) {
  62          $db = PearDatabase::getInstance();
  63          $this->module = $module;
  64          $this->customViewColumnList = null;
  65          $this->stdFilterList = null;
  66          $this->conditionals = array();
  67          $this->user = $user;
  68          $this->advFilterList = null;
  69          $this->fields = array();
  70          $this->referenceModuleMetaInfo = array();
  71          $this->moduleNameFields = array();
  72          $this->whereFields = array();
  73          $this->groupType = self::$AND;
  74          $this->meta = $this->getMeta($module);
  75          $this->moduleNameFields[$module] = $this->meta->getNameFields();
  76          $this->referenceFieldInfoList = $this->meta->getReferenceFieldDetails();
  77          $this->referenceFieldList = array_keys($this->referenceFieldInfoList);;
  78          $this->ownerFields = $this->meta->getOwnerFields();
  79          $this->columns = null;
  80          $this->fromClause = null;
  81          $this->whereClause = null;
  82          $this->query = null;
  83          $this->conditionalWhere = null;
  84          $this->groupInfo = '';
  85          $this->manyToManyRelatedModuleConditions = array();
  86          $this->conditionInstanceCount = 0;
  87          $this->customViewFields = array();
  88      }
  89  
  90      /**
  91       *
  92       * @param String:ModuleName $module
  93       * @return EntityMeta
  94       */
  95  	public function getMeta($module) {
  96          $db = PearDatabase::getInstance();
  97          if (empty($this->referenceModuleMetaInfo[$module])) {
  98              $handler = vtws_getModuleHandlerFromName($module, $this->user);
  99              $meta = $handler->getMeta();
 100              $this->referenceModuleMetaInfo[$module] = $meta;
 101              $this->moduleNameFields[$module] = $meta->getNameFields();
 102          }
 103          return $this->referenceModuleMetaInfo[$module];
 104      }
 105  
 106  	public function reset() {
 107          $this->fromClause = null;
 108          $this->whereClause = null;
 109          $this->columns = null;
 110          $this->query = null;
 111      }
 112  
 113  	public function setFields($fields) {
 114          $this->fields = $fields;
 115      }
 116  
 117  	public function getCustomViewFields() {
 118          return $this->customViewFields;
 119      }
 120  
 121  	public function getFields() {
 122          return $this->fields;
 123      }
 124  
 125  	public function getWhereFields() {
 126          return $this->whereFields;
 127      }
 128  
 129      public function addWhereField($fieldName) {
 130          $this->whereFields[] = $fieldName;
 131      }
 132  
 133  	public function getOwnerFieldList() {
 134          return $this->ownerFields;
 135      }
 136  
 137  	public function getModuleNameFields($module) {
 138          return $this->moduleNameFields[$module];
 139      }
 140  
 141  	public function getReferenceFieldList() {
 142          return $this->referenceFieldList;
 143      }
 144  
 145  	public function getReferenceFieldInfoList() {
 146          return $this->referenceFieldInfoList;
 147      }
 148  
 149  	public function getModule () {
 150          return $this->module;
 151      }
 152  
 153      public function getModuleFields() {
 154          $moduleFields = $this->meta->getModuleFields();
 155  
 156          $module = $this->getModule();
 157          if($module == 'Calendar') {
 158              $eventmoduleMeta = $this->getMeta('Events');
 159              $eventModuleFieldList = $eventmoduleMeta->getModuleFields();
 160              $moduleFields = array_merge($moduleFields, $eventModuleFieldList);
 161          }
 162          return $moduleFields;
 163      }
 164  
 165  	public function getConditionalWhere() {
 166          return $this->conditionalWhere;
 167      }
 168  
 169  	public function getDefaultCustomViewQuery() {
 170          $customView = new CustomView($this->module);
 171          $viewId = $customView->getViewId($this->module);
 172          return $this->getCustomViewQueryById($viewId);
 173      }
 174  
 175  	public function initForDefaultCustomView() {
 176          $customView = new CustomView($this->module);
 177          $viewId = $customView->getViewId($this->module);
 178          $this->initForCustomViewById($viewId);
 179      }
 180  
 181  	public function initForCustomViewById($viewId) {
 182          $customView = new CustomView($this->module);
 183          $this->customViewColumnList = $customView->getColumnsListByCvid($viewId);
 184          foreach ($this->customViewColumnList as $customViewColumnInfo) {
 185              $details = explode(':', $customViewColumnInfo);
 186              if(empty($details[2]) && $details[1] == 'crmid' && $details[0] == 'vtiger_crmentity') {
 187                  $name = 'id';
 188                  $this->customViewFields[] = $name;
 189              } else {
 190                  $this->fields[] = $details[2];
 191                  $this->customViewFields[] = $details[2];
 192              }
 193          }
 194  
 195          if($this->module == 'Calendar' && !in_array('activitytype', $this->fields)) {
 196              $this->fields[] = 'activitytype';
 197          }
 198  
 199          if($this->module == 'Documents') {
 200              if(in_array('filename', $this->fields)) {
 201                  if(!in_array('filelocationtype', $this->fields)) {
 202                      $this->fields[] = 'filelocationtype';
 203                  }
 204                  if(!in_array('filestatus', $this->fields)) {
 205                      $this->fields[] = 'filestatus';
 206                  }
 207              }
 208          }
 209          $this->fields[] = 'id';
 210  
 211          $this->stdFilterList = $customView->getStdFilterByCvid($viewId);
 212          $this->advFilterList = $customView->getAdvFilterByCvid($viewId);
 213  
 214          if(is_array($this->stdFilterList)) {
 215              $value = array();
 216              if(!empty($this->stdFilterList['columnname'])) {
 217                  $this->startGroup('');
 218                  $name = explode(':',$this->stdFilterList['columnname']);
 219                  $name = $name[2];
 220                  $value[] = $this->fixDateTimeValue($name, $this->stdFilterList['startdate']);
 221                  $value[] = $this->fixDateTimeValue($name, $this->stdFilterList['enddate'], false);
 222                  $this->addCondition($name, $value, 'BETWEEN');
 223              }
 224          }
 225          if($this->conditionInstanceCount <= 0 && is_array($this->advFilterList) && count($this->advFilterList) > 0) {
 226              $this->startGroup('');
 227          } elseif($this->conditionInstanceCount > 0 && is_array($this->advFilterList) && count($this->advFilterList) > 0) {
 228              $this->addConditionGlue(self::$AND);
 229          }
 230          if(is_array($this->advFilterList) && count($this->advFilterList) > 0) {
 231              $this->parseAdvFilterList($this->advFilterList);
 232          }
 233          if($this->conditionInstanceCount > 0) {
 234              $this->endGroup();
 235          }
 236      }
 237  
 238  	public function parseAdvFilterList($advFilterList, $glue=''){
 239          if(!empty($glue)) $this->addConditionGlue($glue);
 240  
 241          $customView = new CustomView($this->module);
 242          $dateSpecificConditions = $customView->getStdFilterConditions();
 243          foreach ($advFilterList as $groupindex=>$groupcolumns) {
 244              $filtercolumns = $groupcolumns['columns'];
 245              if(count($filtercolumns) > 0) {
 246                  $this->startGroup('');
 247                  foreach ($filtercolumns as $index=>$filter) {
 248                      $nameComponents = explode(':',$filter['columnname']);
 249                      // For Events "End Date & Time" field datatype should be DT. But, db will give D for due_date field
 250                      if($nameComponents[2] == 'due_date' && $nameComponents[3] == 'Events_End_Date_&_Time')
 251                          $nameComponents[4] = 'DT';
 252                      if(empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') {
 253                          $name = $this->getSQLColumn('id');
 254                      } else {
 255                          $name = $nameComponents[2];
 256                      }
 257                      if(($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) {
 258                          $filter['stdfilter'] = $filter['comparator'];
 259                          $valueComponents = explode(',',$filter['value']);
 260                          if($filter['comparator'] == 'custom') {
 261                              if($nameComponents[4] == 'DT') {
 262                                  $startDateTimeComponents = explode(' ',$valueComponents[0]);
 263                                  $endDateTimeComponents = explode(' ',$valueComponents[1]);
 264                                  $filter['startdate'] = DateTimeField::convertToDBFormat($startDateTimeComponents[0]);
 265                                  $filter['enddate'] = DateTimeField::convertToDBFormat($endDateTimeComponents[0]);
 266                              } else {
 267                                  $filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
 268                                  $filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
 269                              }
 270                          }
 271                          $dateFilterResolvedList = $customView->resolveDateFilterValue($filter);
 272                          // If datatype is DT then we should append time also
 273                          if($nameComponents[4] == 'DT'){
 274                              $startdate = explode(' ', $dateFilterResolvedList['startdate']);
 275                              if($startdate[1] == '')
 276                                  $startdate[1] = '00:00:00';
 277                              $dateFilterResolvedList['startdate'] = $startdate[0].' '.$startdate[1];
 278  
 279                              $enddate = explode(' ',$dateFilterResolvedList['enddate']);
 280                              if($enddate[1] == '')
 281                                  $enddate[1] = '23:59:59';
 282                              $dateFilterResolvedList['enddate'] = $enddate[0].' '.$enddate[1];
 283                          }
 284                          $value = array();
 285                          $value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['startdate']);
 286                          $value[] = $this->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false);
 287                          $this->addCondition($name, $value, 'BETWEEN');
 288                      } else if($nameComponents[4] == 'DT' && ($filter['comparator'] == 'e' || $filter['comparator'] == 'n')) {
 289                          $filter['stdfilter'] = $filter['comparator'];
 290                          $dateTimeComponents = explode(' ',$filter['value']);
 291                          $filter['startdate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]);
 292                          $filter['enddate'] = DateTimeField::convertToDBFormat($dateTimeComponents[0]);
 293  
 294                          $startDate = $this->fixDateTimeValue($name, $filter['startdate']);
 295                          $endDate = $this->fixDateTimeValue($name, $filter['enddate'],false);
 296  
 297                          $value = array();
 298                          $start = explode(' ', $startDate);
 299                          if($start[1] == "")
 300                              $startDate = $start[0].' '.'00:00:00';
 301  
 302                          $end = explode(' ',$endDate);
 303                          if($end[1] == "")
 304                              $endDate = $end[0].' '.'23:59:59';
 305  
 306                          $value[] = $startDate;
 307                          $value[] = $endDate;
 308                          if($filter['comparator'] == 'n') {
 309                              $this->addCondition($name, $value, 'NOTEQUAL');
 310                          } else {
 311                              $this->addCondition($name, $value, 'BETWEEN');
 312                          }
 313                      } else if($nameComponents[4] == 'DT' && ($filter['comparator'] == 'a' || $filter['comparator'] == 'b')) {
 314                          $dateTime = explode(' ', $filter['value']);
 315                          $date = DateTimeField::convertToDBFormat($dateTime[0]);
 316                          $value = array();
 317                          $value[] = $this->fixDateTimeValue($name, $date, false);
 318                          // Still fixDateTimeValue returns only date value, we need to append time because it is DT type
 319                          for($i=0;$i<count($value);$i++){
 320                              $values = explode(' ', $value[$i]);
 321                              if($values[1] == ''){
 322                                  $values[1] = '00:00:00';
 323                              }
 324                              $value[$i] = $values[0].' '.$values[1];
 325                          }
 326                          $this->addCondition($name, $value, $filter['comparator']);
 327                      } else{
 328                          $this->addCondition($name, $filter['value'], $filter['comparator']);
 329                      }
 330                      $columncondition = $filter['column_condition'];
 331                      if(!empty($columncondition)) {
 332                          $this->addConditionGlue($columncondition);
 333                      }
 334                  }
 335                  $this->endGroup();
 336                  $groupConditionGlue = $groupcolumns['condition'];
 337                  if(!empty($groupConditionGlue))
 338                      $this->addConditionGlue($groupConditionGlue);
 339              }
 340          }
 341      }
 342  
 343  	public function getCustomViewQueryById($viewId) {
 344          $this->initForCustomViewById($viewId);
 345          return $this->getQuery();
 346      }
 347  
 348  	public function getQuery() {
 349          if(empty($this->query)) {
 350              $conditionedReferenceFields = array();
 351              $allFields = array_merge($this->whereFields,$this->fields);
 352              foreach ($allFields as $fieldName) {
 353                  if(in_array($fieldName,$this->referenceFieldList)) {
 354                      $moduleList = $this->referenceFieldInfoList[$fieldName];
 355                      foreach ($moduleList as $module) {
 356                          if(empty($this->moduleNameFields[$module])) {
 357                              $meta = $this->getMeta($module);
 358                          }
 359                      }
 360                  } elseif(in_array($fieldName, $this->ownerFields )) {
 361                      $meta = $this->getMeta('Users');
 362                      $meta = $this->getMeta('Groups');
 363                  }
 364              }
 365  
 366              $query = "SELECT ";
 367              $query .= $this->getSelectClauseColumnSQL();
 368              $query .= $this->getFromClause();
 369              $query .= $this->getWhereClause();
 370              $this->query = $query;
 371              return $query;
 372          } else {
 373              return $this->query;
 374          }
 375      }
 376  
 377  	public function getSQLColumn($name) {
 378          if ($name == 'id') {
 379              $baseTable = $this->meta->getEntityBaseTable();
 380              $moduleTableIndexList = $this->meta->getEntityTableIndexList();
 381              $baseTableIndex = $moduleTableIndexList[$baseTable];
 382              return $baseTable.'.'.$baseTableIndex;
 383          }
 384  
 385          $moduleFields = $this->getModuleFields();
 386          $field = $moduleFields[$name];
 387          $sql = '';
 388          //TODO optimization to eliminate one more lookup of name, incase the field refers to only
 389          //one module or is of type owner.
 390          $column = $field->getColumnName();
 391          return $field->getTableName().'.'.$column;
 392      }
 393  
 394  	public function getSelectClauseColumnSQL(){
 395          $columns = array();
 396          $moduleFields = $this->getModuleFields();
 397          $accessibleFieldList = array_keys($moduleFields);
 398          $accessibleFieldList[] = 'id';
 399          $this->fields = array_intersect($this->fields, $accessibleFieldList);
 400          foreach ($this->fields as $field) {
 401              $sql = $this->getSQLColumn($field);
 402              $columns[] = $sql;
 403  
 404              //To merge date and time fields
 405              if($this->meta->getEntityName() == 'Calendar' && ($field == 'date_start' || $field == 'due_date' || $field == 'taskstatus' || $field == 'eventstatus')) {
 406                  if($field=='date_start') {
 407                      $timeField = 'time_start';
 408                      $sql = $this->getSQLColumn($timeField);
 409                  } else if ($field == 'due_date') {
 410                      $timeField = 'time_end';
 411                      $sql = $this->getSQLColumn($timeField);
 412                  } else if ($field == 'taskstatus' || $field == 'eventstatus') {
 413                      //In calendar list view, Status value = Planned is not displaying
 414                      $sql = "CASE WHEN (vtiger_activity.status not like '') THEN vtiger_activity.status ELSE vtiger_activity.eventstatus END AS ";
 415                      if ( $field == 'taskstatus') {
 416                          $sql .= "status";
 417                      } else {
 418                          $sql .= $field;
 419                      }
 420                  }
 421                  $columns[] = $sql;
 422              }
 423          }
 424          $this->columns = implode(', ',$columns);
 425          return $this->columns;
 426      }
 427  
 428  	public function getFromClause() {
 429          global $current_user;
 430          if(!empty($this->query) || !empty($this->fromClause)) {
 431              return $this->fromClause;
 432          }
 433          $baseModule = $this->getModule();
 434          $moduleFields = $this->getModuleFields();
 435          $tableList = array();
 436          $tableJoinMapping = array();
 437          $tableJoinCondition = array();
 438          $i =1;
 439  
 440          $moduleTableIndexList = $this->meta->getEntityTableIndexList();
 441          foreach ($this->fields as $fieldName) {
 442              if ($fieldName == 'id') {
 443                  continue;
 444              }
 445  
 446              $field = $moduleFields[$fieldName];
 447              $baseTable = $field->getTableName();
 448              $tableIndexList = $this->meta->getEntityTableIndexList();
 449              $baseTableIndex = $tableIndexList[$baseTable];
 450              if($field->getFieldDataType() == 'reference') {
 451                  $moduleList = $this->referenceFieldInfoList[$fieldName];
 452                  $tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
 453                  foreach($moduleList as $module) {
 454                      if($module == 'Users' && $baseModule != 'Users') {
 455                          $tableJoinCondition[$fieldName]['vtiger_users'.$fieldName] = $field->getTableName().
 456                                  ".".$field->getColumnName()." = vtiger_users".$fieldName.".id";
 457                          $tableJoinCondition[$fieldName]['vtiger_groups'.$fieldName] = $field->getTableName().
 458                                  ".".$field->getColumnName()." = vtiger_groups".$fieldName.".groupid";
 459                          $tableJoinMapping['vtiger_users'.$fieldName] = 'LEFT JOIN vtiger_users AS';
 460                          $tableJoinMapping['vtiger_groups'.$fieldName] = 'LEFT JOIN vtiger_groups AS';
 461                          $i++;
 462                      }
 463                  }
 464              } elseif($field->getFieldDataType() == 'owner') {
 465                  $tableList['vtiger_users'] = 'vtiger_users';
 466                  $tableList['vtiger_groups'] = 'vtiger_groups';
 467                  $tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
 468                  $tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
 469                  if($fieldName == "created_user_id"){
 470                      $tableJoinCondition[$fieldName]['vtiger_users'.$fieldName] = $field->getTableName().
 471                              ".".$field->getColumnName()." = vtiger_users".$fieldName.".id";
 472                      $tableJoinCondition[$fieldName]['vtiger_groups'.$fieldName] = $field->getTableName().
 473                              ".".$field->getColumnName()." = vtiger_groups".$fieldName.".groupid";
 474                      $tableJoinMapping['vtiger_users'.$fieldName] = 'LEFT JOIN vtiger_users AS';
 475                          $tableJoinMapping['vtiger_groups'.$fieldName] = 'LEFT JOIN vtiger_groups AS';
 476                  }
 477              }
 478              $tableList[$field->getTableName()] = $field->getTableName();
 479                  $tableJoinMapping[$field->getTableName()] =
 480                          $this->meta->getJoinClause($field->getTableName());
 481          }
 482          $baseTable = $this->meta->getEntityBaseTable();
 483          $baseTableIndex = $moduleTableIndexList[$baseTable];
 484          foreach ($this->whereFields as $fieldName) {
 485              if(empty($fieldName)) {
 486                  continue;
 487              }
 488              $field = $moduleFields[$fieldName];
 489              if(empty($field)) {
 490                  // not accessible field.
 491                  continue;
 492              }
 493              $baseTable = $field->getTableName();
 494              // When a field is included in Where Clause, but not is Select Clause, and the field table is not base table,
 495              // The table will not be present in tablesList and hence needs to be added to the list.
 496              if(empty($tableList[$baseTable])) {
 497                  $tableList[$baseTable] = $field->getTableName();
 498                  $tableJoinMapping[$baseTable] = $this->meta->getJoinClause($field->getTableName());
 499              }
 500              if($field->getFieldDataType() == 'reference') {
 501                  $moduleList = $this->referenceFieldInfoList[$fieldName];
 502                  // This is special condition as the data is not stored in the base table, 
 503                                  // If empty search is performed on this field then it fails to retrieve any information. 
 504                                  if ($fieldName == 'parent_id' && $field->getTableName() == 'vtiger_seactivityrel') {
 505                                      $tableJoinMapping[$field->getTableName()] = 'LEFT JOIN';
 506                                  } else if ($fieldName == 'contact_id' && $field->getTableName() == 'vtiger_cntactivityrel') {
 507                                      $tableJoinMapping[$field->getTableName()] = "LEFT JOIN";
 508                                  } else {
 509                                      $tableJoinMapping[$field->getTableName()] = 'INNER JOIN';
 510                                  }
 511                  foreach($moduleList as $module) {
 512                      $meta = $this->getMeta($module);
 513                      $nameFields = $this->moduleNameFields[$module];
 514                      $nameFieldList = explode(',',$nameFields);
 515                      foreach ($nameFieldList as $index=>$column) {
 516                          $referenceField = $meta->getFieldByColumnName($column);
 517                          $referenceTable = $referenceField->getTableName();
 518                          $tableIndexList = $meta->getEntityTableIndexList();
 519                          $referenceTableIndex = $tableIndexList[$referenceTable];
 520  
 521                          $referenceTableName = "$referenceTable $referenceTable$fieldName";
 522                          $referenceTable = "$referenceTable$fieldName";
 523                          //should always be left join for cases where we are checking for null
 524                          //reference field values.
 525                          if(!array_key_exists($referenceTable, $tableJoinMapping)) {        // table already added in from clause
 526                              $tableJoinMapping[$referenceTableName] = 'LEFT JOIN';
 527                              $tableJoinCondition[$fieldName][$referenceTableName] = $baseTable.'.'.
 528                                  $field->getColumnName().' = '.$referenceTable.'.'.$referenceTableIndex;
 529                          }
 530                      }
 531                  }
 532              } elseif($field->getFieldDataType() == 'owner') {
 533                  $tableList['vtiger_users'] = 'vtiger_users';
 534                  $tableList['vtiger_groups'] = 'vtiger_groups';
 535                  $tableJoinMapping['vtiger_users'] = 'LEFT JOIN';
 536                  $tableJoinMapping['vtiger_groups'] = 'LEFT JOIN';
 537              } else {
 538                  $tableList[$field->getTableName()] = $field->getTableName();
 539                  $tableJoinMapping[$field->getTableName()] =
 540                          $this->meta->getJoinClause($field->getTableName());
 541              }
 542          }
 543  
 544          $defaultTableList = $this->meta->getEntityDefaultTableList();
 545          foreach ($defaultTableList as $table) {
 546              if(!in_array($table, $tableList)) {
 547                  $tableList[$table] = $table;
 548                  $tableJoinMapping[$table] = 'INNER JOIN';
 549              }
 550          }
 551          $ownerFields = $this->meta->getOwnerFields();
 552          if (count($ownerFields) > 0) {
 553              $ownerField = $ownerFields[0];
 554          }
 555          $baseTable = $this->meta->getEntityBaseTable();
 556          $sql = " FROM $baseTable ";
 557          unset($tableList[$baseTable]);
 558          foreach ($defaultTableList as $tableName) {
 559              $sql .= " $tableJoinMapping[$tableName] $tableName ON $baseTable.".
 560                      "$baseTableIndex = $tableName.$moduleTableIndexList[$tableName]";
 561              unset($tableList[$tableName]);
 562          }
 563          foreach ($tableList as $tableName) {
 564              if($tableName == 'vtiger_users') {
 565                  $field = $moduleFields[$ownerField];
 566                  $sql .= " $tableJoinMapping[$tableName] $tableName ON ".$field->getTableName().".".
 567                      $field->getColumnName()." = $tableName.id";
 568              } elseif($tableName == 'vtiger_groups') {
 569                  $field = $moduleFields[$ownerField];
 570                  $sql .= " $tableJoinMapping[$tableName] $tableName ON ".$field->getTableName().".".
 571                      $field->getColumnName()." = $tableName.groupid";
 572              } else {
 573                  $sql .= " $tableJoinMapping[$tableName] $tableName ON $baseTable.".
 574                      "$baseTableIndex = $tableName.$moduleTableIndexList[$tableName]";
 575              }
 576          }
 577  
 578          if( $this->meta->getTabName() == 'Documents') {
 579              $tableJoinCondition['folderid'] = array(
 580                  'vtiger_attachmentsfolderfolderid'=>"$baseTable.folderid = vtiger_attachmentsfolderfolderid.folderid"
 581              );
 582              $tableJoinMapping['vtiger_attachmentsfolderfolderid'] = 'INNER JOIN vtiger_attachmentsfolder';
 583          }
 584  
 585          foreach ($tableJoinCondition as $fieldName=>$conditionInfo) {
 586              foreach ($conditionInfo as $tableName=>$condition) {
 587                  if(!empty($tableList[$tableName])) {
 588                      $tableNameAlias = $tableName.'2';
 589                      $condition = str_replace($tableName, $tableNameAlias, $condition);
 590                  } else {
 591                      $tableNameAlias = '';
 592                  }
 593                  $sql .= " $tableJoinMapping[$tableName] $tableName $tableNameAlias ON $condition";
 594              }
 595          }
 596  
 597          foreach ($this->manyToManyRelatedModuleConditions as $conditionInfo) {
 598              $relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(),
 599                      $conditionInfo['relatedModule']);
 600              $relationInfo = $relatedModuleMeta->getRelationMeta();
 601              $relatedModule = $this->meta->getTabName();
 602              $sql .= ' INNER JOIN '.$relationInfo['relationTable']." ON ".
 603              $relationInfo['relationTable'].".$relationInfo[$relatedModule]=".
 604                  "$baseTable.$baseTableIndex";
 605          }
 606  
 607          // Adding support for conditions on reference module fields
 608          if($this->referenceModuleField) {
 609              $referenceFieldTableList = array();
 610              foreach ($this->referenceModuleField as $index=>$conditionInfo) {
 611  
 612                  $handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user);
 613                  $meta = $handler->getMeta();
 614                  $tableList = $meta->getEntityTableIndexList();
 615                  $fieldName = $conditionInfo['fieldName'];
 616                  $referenceFieldObject = $moduleFields[$conditionInfo['referenceField']];
 617                  $fields = $meta->getModuleFields();
 618                  $fieldObject = $fields[$fieldName];
 619  
 620                  if(empty($fieldObject)) continue;
 621  
 622                  $tableName = $fieldObject->getTableName();
 623                  if(!in_array($tableName, $referenceFieldTableList)) {
 624                      if($referenceFieldObject->getFieldName() == 'parent_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
 625                          $sql .= ' LEFT JOIN vtiger_seactivityrel ON vtiger_seactivityrel.activityid = vtiger_activity.activityid ';
 626                      }
 627                      //TODO : this will create duplicates, need to find a better way
 628                      if($referenceFieldObject->getFieldName() == 'contact_id' && ($this->getModule() == 'Calendar' || $this->getModule() == 'Events')) {
 629                          $sql .= ' LEFT JOIN vtiger_cntactivityrel ON vtiger_cntactivityrel.activityid = vtiger_activity.activityid ';
 630                      }
 631                      $sql .= " LEFT JOIN ".$tableName.' AS '.$tableName.$conditionInfo['referenceField'].' ON
 632                              '.$tableName.$conditionInfo['referenceField'].'.'.$tableList[$tableName].'='.
 633                          $referenceFieldObject->getTableName().'.'.$referenceFieldObject->getColumnName();
 634                      $referenceFieldTableList[] = $tableName;
 635                  }
 636              }
 637          }
 638  
 639          $sql .= $this->meta->getEntityAccessControlQuery();
 640          $this->fromClause = $sql;
 641          return $sql;
 642      }
 643  
 644  	public function getWhereClause() {
 645          global $current_user;
 646          if(!empty($this->query) || !empty($this->whereClause)) {
 647              return $this->whereClause;
 648          }
 649          $deletedQuery = $this->meta->getEntityDeletedQuery();
 650          $sql = '';
 651          if(!empty($deletedQuery)) {
 652              $sql .= " WHERE $deletedQuery";
 653          }
 654          if($this->conditionInstanceCount > 0) {
 655              $sql .= ' AND ';
 656          } elseif(empty($deletedQuery)) {
 657              $sql .= ' WHERE ';
 658          }
 659          $baseModule = $this->getModule();
 660          $moduleFieldList = $this->getModuleFields();
 661          $baseTable = $this->meta->getEntityBaseTable();
 662          $moduleTableIndexList = $this->meta->getEntityTableIndexList();
 663          $baseTableIndex = $moduleTableIndexList[$baseTable];
 664          $groupSql = $this->groupInfo;
 665          $fieldSqlList = array();
 666          foreach ($this->conditionals as $index=>$conditionInfo) {
 667              $fieldName = $conditionInfo['name'];
 668              $field = $moduleFieldList[$fieldName];
 669              if(empty($field) || $conditionInfo['operator'] == 'None') {
 670                  continue;
 671              }
 672              $fieldSql = '(';
 673              $fieldGlue = '';
 674              $valueSqlList = $this->getConditionValue($conditionInfo['value'],
 675                  $conditionInfo['operator'], $field);
 676              $operator = strtolower($conditionInfo['operator']);
 677              if($operator == 'between'&& $this->isDateType($field->getFieldDataType())){
 678                  $start = explode(' ', $conditionInfo['value'][0]);
 679                  if(count($start) == 2)
 680                      $conditionInfo['value'][0] = getValidDBInsertDateTimeValue($start[0].' '.$start[1]);
 681  
 682                  $end = explode(' ', $conditionInfo['values'][1]);
 683                  // Dates will be equal for Today, Tomorrow, Yesterday.
 684                  if(count($end) == 2){
 685                      if($start[0] == $end[0]){
 686                          $dateTime = new DateTime($conditionInfo['value'][0]);
 687                          $nextDay = $dateTime->modify('+1 days');
 688                          $nextDay = $nextDay->format('Y-m-d H:i:s');
 689                          $values = explode(' ', $nextDay);
 690                          $conditionInfo['value'][1] = getValidDBInsertDateTimeValue($values[0]).' '.$values[1];
 691                      }else{
 692                          $end = $conditionInfo['value'][1];
 693                          $dateObject = new DateTimeField($end);
 694                          $conditionInfo['value'][1] = $dateObject->getDBInsertDateTimeValue();
 695                      }
 696                  }
 697  
 698              }
 699              if(!is_array($valueSqlList)) {
 700                  $valueSqlList = array($valueSqlList);
 701              }
 702              foreach ($valueSqlList as $valueSql) {
 703                  if (in_array($fieldName, $this->referenceFieldList)) {
 704                      if($conditionInfo['operator'] == 'y'){
 705                          $columnName = $field->getColumnName();
 706                          $tableName = $field->getTableName();
 707                          // We are checking for zero since many reference fields will be set to 0 if it doest not have any value
 708                          $fieldSql .= "$fieldGlue $tableName.$columnName $valueSql OR $tableName.$columnName = '0'";
 709                          $fieldGlue = ' OR';
 710                      }else{
 711                          $moduleList = $this->referenceFieldInfoList[$fieldName];
 712                          foreach($moduleList as $module) {
 713                              $nameFields = $this->moduleNameFields[$module];
 714                              $nameFieldList = explode(',',$nameFields);
 715                              $meta = $this->getMeta($module);
 716                              $columnList = array();
 717                              foreach ($nameFieldList as $column) {
 718                                  if($module == 'Users') {
 719                                      $instance = CRMEntity::getInstance($module);
 720                                      $referenceTable = $instance->table_name;
 721                                      if(count($this->ownerFields) > 0 ||
 722                                              $this->getModule() == 'Quotes') {
 723                                          $referenceTable .= $fieldName;
 724                                      }
 725                                  } else {
 726                                      $referenceField = $meta->getFieldByColumnName($column);
 727                                      $referenceTable = $referenceField->getTableName().$fieldName;
 728                                  }
 729                                  if(isset($moduleTableIndexList[$referenceTable])) {
 730                                      $referenceTable = "$referenceTable$fieldName";
 731                                  }
 732                                  $columnList[] = "$referenceTable.$column";
 733                              }
 734                              if(count($columnList) > 1) {
 735                                  $columnSql = getSqlForNameInDisplayFormat(array('first_name'=>$columnList[0],'last_name'=>$columnList[1]),'Users');
 736                              } else {
 737                                  $columnSql = implode('', $columnList);
 738                              }
 739  
 740                              $fieldSql .= "$fieldGlue trim($columnSql) $valueSql";
 741                              $fieldGlue = ' OR';
 742                          }
 743                      }
 744                  } elseif (in_array($fieldName, $this->ownerFields)) {
 745                      if($fieldName == 'created_user_id'){
 746                          $concatSql = getSqlForNameInDisplayFormat(array('first_name'=>"vtiger_users$fieldName.first_name",'last_name'=>"vtiger_users$fieldName.last_name"), 'Users');
 747                          $fieldSql .= "$fieldGlue (trim($concatSql) $valueSql)";
 748                      }else{
 749                          $concatSql = getSqlForNameInDisplayFormat(array('first_name'=>"vtiger_users.first_name",'last_name'=>"vtiger_users.last_name"), 'Users');
 750                          $fieldSql .= "$fieldGlue (trim($concatSql) $valueSql or "."vtiger_groups.groupname $valueSql)";
 751                      }
 752                  } elseif($field->getFieldDataType() == 'date' && ($baseModule == 'Events' || $baseModule == 'Calendar') && ($fieldName == 'date_start' || $fieldName == 'due_date')) {
 753                      $value = $conditionInfo['value'];
 754                      $operator = $conditionInfo['operator'];
 755                      if($fieldName == 'date_start') {
 756                          $dateFieldColumnName = 'vtiger_activity.date_start';
 757                          $timeFieldColumnName = 'vtiger_activity.time_start';
 758                      } else {
 759                          $dateFieldColumnName = 'vtiger_activity.due_date';
 760                          $timeFieldColumnName = 'vtiger_activity.time_end';
 761                      }
 762                      if($operator == 'bw') {
 763                          $values = explode(',', $value);
 764                          $startDateValue = explode(' ', $values[0]);
 765                          $endDateValue = explode(' ', $values[1]);
 766                          if(count($startDateValue) == 2 && count($endDateValue) == 2) {
 767                              $fieldSql .= " CAST(CONCAT($dateFieldColumnName,' ',$timeFieldColumnName) AS DATETIME) $valueSql";
 768                          } else {
 769                              $fieldSql .= "$dateFieldColumnName $valueSql";
 770                          }
 771                      } else {
 772                                                  if(is_array($value)){
 773                                                      $value = $value[0];
 774                                                  }
 775                                                  $values = explode(' ', $value);
 776                                                  if(count($values) == 2) {
 777                                                          $fieldSql .= "$fieldGlue CAST(CONCAT($dateFieldColumnName,' ',$timeFieldColumnName) AS DATETIME) $valueSql ";
 778                                                  } else {
 779                                                          $fieldSql .= "$fieldGlue $dateFieldColumnName $valueSql";
 780                                                  }
 781                      }
 782                  } elseif($field->getFieldDataType() == 'datetime') {
 783                      $value = $conditionInfo['value'];
 784                      $operator = strtolower($conditionInfo['operator']);
 785                      if($operator == 'bw') {
 786                          $values = explode(',', $value);
 787                          $startDateValue = explode(' ', $values[0]);
 788                          $endDateValue = explode(' ', $values[1]);
 789                          if($startDateValue[1] == '00:00:00' && ($endDateValue[1] == '00:00:00' || $endDateValue[1] == '23:59:59')) {
 790                              $fieldSql .= "$fieldGlue CAST(".$field->getTableName().'.'.$field->getColumnName()." AS DATE) $valueSql";
 791                          } else {
 792                              $fieldSql .= "$fieldGlue ".$field->getTableName().'.'.$field->getColumnName().' '.$valueSql;
 793                          }
 794                      } elseif($operator == 'between' || $operator == 'notequal' || $operator == 'a' || $operator == 'b') {
 795                          $fieldSql .= "$fieldGlue ".$field->getTableName().'.'.$field->getColumnName().' '.$valueSql;
 796                      } else {
 797                          $values = explode(' ', $value);
 798                          if($values[1] == '00:00:00') {
 799                              $fieldSql .= "$fieldGlue CAST(".$field->getTableName().'.'.$field->getColumnName()." AS DATE) $valueSql";
 800                          } else {
 801                              $fieldSql .= "$fieldGlue ".$field->getTableName().'.'.$field->getColumnName().' '.$valueSql;
 802                          }
 803                      }
 804                  } else if (($baseModule == 'Events' || $baseModule == 'Calendar') 
 805                          && ($field->getColumnName() == 'status' || $field->getColumnName() == 'eventstatus')) {
 806                      $otherFieldName = 'eventstatus';
 807                      if($field->getColumnName() == 'eventstatus'){
 808                          $otherFieldName = 'taskstatus';
 809                      }
 810                      $otherField = $moduleFieldList[$otherFieldName];
 811                      
 812                      $specialCondition = '';
 813                      $specialConditionForOtherField='';
 814                      $conditionGlue = ' OR ';
 815                      if($conditionInfo['operator'] == 'n' || $conditionInfo['operator'] == 'k' || $conditionInfo['operator'] == 'y') {
 816                         $conditionGlue = ' AND '; 
 817                         if($conditionInfo['operator'] == 'n') {
 818                             $specialCondition = ' OR '.$field->getTableName().'.'.$field->getColumnName().' IS NULL ';
 819                             if(!empty($otherField))
 820                             $specialConditionForOtherField = ' OR '.$otherField->getTableName().'.'.$otherField->getColumnName().' IS NULL ';
 821                      }
 822                      }
 823                      
 824                      $otherFieldValueSql = $valueSql;
 825                      if($conditionInfo['operator'] == 'ny' && !empty($otherField)){
 826                          $otherFieldValueSql = "IS NOT NULL AND ".$otherField->getTableName().'.'.$otherField->getColumnName()." != ''";
 827                      }
 828                      
 829                      $fieldSql .= "$fieldGlue ((". $field->getTableName().'.'.$field->getColumnName().' '.$valueSql." $specialCondition) ";
 830                      if(!empty($otherField))
 831                          $fieldSql .= $conditionGlue .'('.$otherField->getTableName().'.'.$otherField->getColumnName() . ' '. $otherFieldValueSql .' '.$specialConditionForOtherField .'))';
 832                      else
 833                          $fieldSql .= ')';
 834                  }
 835                  else {
 836                      if($fieldName == 'birthday' && !$this->isRelativeSearchOperators(
 837                              $conditionInfo['operator'])) {
 838                          $fieldSql .= "$fieldGlue DATE_FORMAT(".$field->getTableName().'.'.
 839                          $field->getColumnName().",'%m%d') ".$valueSql;
 840                      } else {
 841                          $fieldSql .= "$fieldGlue ".$field->getTableName().'.'.
 842                          $field->getColumnName().' '.$valueSql;
 843                      }
 844                  }
 845                  if(($conditionInfo['operator'] == 'n' || $conditionInfo['operator'] == 'k') && ($field->getFieldDataType() == 'owner' || $field->getFieldDataType() == 'picklist') ) {
 846                      $fieldGlue = ' AND';
 847                  } else {
 848                      $fieldGlue = ' OR';
 849                  }
 850              }
 851              $fieldSql .= ')';
 852              $fieldSqlList[$index] = $fieldSql;
 853          }
 854          foreach ($this->manyToManyRelatedModuleConditions as $index=>$conditionInfo) {
 855              $relatedModuleMeta = RelatedModuleMeta::getInstance($this->meta->getTabName(),
 856                      $conditionInfo['relatedModule']);
 857              $relationInfo = $relatedModuleMeta->getRelationMeta();
 858              $relatedModule = $this->meta->getTabName();
 859              $fieldSql = "(".$relationInfo['relationTable'].'.'.
 860              $relationInfo[$conditionInfo['column']].$conditionInfo['SQLOperator'].
 861              $conditionInfo['value'].")";
 862              $fieldSqlList[$index] = $fieldSql;
 863          }
 864  
 865          // This is added to support reference module fields
 866          if($this->referenceModuleField) {
 867              foreach ($this->referenceModuleField as $index=>$conditionInfo) {
 868                  $handler = vtws_getModuleHandlerFromName($conditionInfo['relatedModule'], $current_user);
 869                  $meta = $handler->getMeta();
 870                  $fieldName = $conditionInfo['fieldName'];
 871                  $fields = $meta->getModuleFields();
 872                  $fieldObject = $fields[$fieldName];
 873                  $columnName = $fieldObject->getColumnName();
 874                  $tableName = $fieldObject->getTableName();
 875                  $valueSQL = $this->getConditionValue($conditionInfo['value'], $conditionInfo['SQLOperator'], $fieldObject);
 876                  $fieldSql = "(".$tableName.$conditionInfo['referenceField'].'.'.$columnName.' '.$valueSQL[0].")";
 877                  $fieldSqlList[$index] = $fieldSql;
 878              }
 879          }
 880          // This is needed as there can be condition in different order and there is an assumption in makeGroupSqlReplacements API
 881          // that it expects the array in an order and then replaces the sql with its the corresponding place
 882          ksort($fieldSqlList);
 883          $groupSql = $this->makeGroupSqlReplacements($fieldSqlList, $groupSql);
 884          if($this->conditionInstanceCount > 0) {
 885              $this->conditionalWhere = $groupSql;
 886              $sql .= $groupSql;
 887          }
 888          $sql .= " AND $baseTable.$baseTableIndex > 0";
 889          $this->whereClause = $sql;
 890          return $sql;
 891      }
 892  
 893      /**
 894       *
 895       * @param mixed $value
 896       * @param String $operator
 897       * @param WebserviceField $field
 898       */
 899  	private function getConditionValue($value, $operator, $field) {
 900  
 901          $operator = strtolower($operator);
 902          $db = PearDatabase::getInstance();
 903  
 904          if(is_string($value) && $this->ignoreComma == false) {
 905              $valueArray = explode(',' , $value);
 906              if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) {
 907                  $valueArray = getCombinations($valueArray);
 908                  foreach ($valueArray as $key => $value) {
 909                      $valueArray[$key] = ltrim($value, ' |##| ');
 910                  }
 911              }
 912          } elseif(is_array($value)) {
 913              $valueArray = $value;
 914          } else{
 915              $valueArray = array($value);
 916          }
 917          $sql = array();
 918          if($operator == 'between' || $operator == 'bw' || $operator == 'notequal') {
 919              if($field->getFieldName() == 'birthday') {
 920                  $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
 921                  $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
 922                  $sql[] = "BETWEEN DATE_FORMAT(".$db->quote($valueArray[0]).", '%m%d') AND ".
 923                          "DATE_FORMAT(".$db->quote($valueArray[1]).", '%m%d')";
 924              } else {
 925                  if($this->isDateType($field->getFieldDataType())) {
 926                      $start = explode(' ', $valueArray[0]);
 927                      $end = explode(' ',$valueArray[1]);
 928                      if($operator == 'between' && count($start) == 2 && count($end) == 2){
 929                              $valueArray[0] = getValidDBInsertDateTimeValue($start[0].' '.$start[1]);
 930  
 931                              if($start[0] == $end[0]){
 932                                  $dateTime = new DateTime($valueArray[0]);
 933                                  $nextDay = $dateTime->modify('+1 days');
 934                                  $nextDay = strtotime($nextDay->format('Y-m-d H:i:s'))-1;
 935                                  $nextDay = date('Y-m-d H:i:s', $nextDay);
 936                                  $values = explode(' ', $nextDay);
 937                                  $valueArray[1] = getValidDBInsertDateTimeValue($values[0]).' '.$values[1];
 938                              }else{
 939                                  $end = $valueArray[1];
 940                                  $dateObject = new DateTimeField($end);
 941                                  $valueArray[1] = $dateObject->getDBInsertDateTimeValue();
 942                              }
 943                      }else{
 944                          $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]);
 945                          $dateTimeStart = explode(' ',$valueArray[0]);
 946                          if($dateTimeStart[1] == '00:00:00' && $operator != 'between') {
 947                              $valueArray[0] = $dateTimeStart[0];
 948                          }
 949                          $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]);
 950                          $dateTimeEnd = explode(' ', $valueArray[1]);
 951                          if($dateTimeEnd[1] == '00:00:00' || $dateTimeEnd[1] == '23:59:59') {
 952                              $valueArray[1] = $dateTimeEnd[0];
 953                          }
 954                      }
 955                  }
 956  
 957                  if($operator == 'notequal') {
 958                      $sql[] = "NOT BETWEEN ".$db->quote($valueArray[0])." AND ".
 959                              $db->quote($valueArray[1]);
 960                  } else {
 961                      $sql[] = "BETWEEN ".$db->quote($valueArray[0])." AND ".
 962                              $db->quote($valueArray[1]);
 963                  }
 964              }
 965              return $sql;
 966          }
 967          foreach ($valueArray as $value) {
 968              if(!$this->isStringType($field->getFieldDataType())) {
 969                  $value = trim($value);
 970              }
 971              if ($operator == 'empty' || $operator == 'y') {
 972                  $sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName()));
 973                  continue;
 974              }
 975              if($operator == 'ny'){
 976                  $sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName()));
 977                  continue;
 978              }
 979              if((strtolower(trim($value)) == 'null') ||
 980                      (trim($value) == '' && !$this->isStringType($field->getFieldDataType())) &&
 981                              ($operator == 'e' || $operator == 'n')) {
 982                  if($operator == 'e'){
 983                      $sql[] = "IS NULL";
 984                      continue;
 985                  }
 986                  $sql[] = "IS NOT NULL";
 987                  continue;
 988              } elseif($field->getFieldDataType() == 'boolean') {
 989                  $value = strtolower($value);
 990                  if ($value == 'yes') {
 991                      $value = 1;
 992                  } elseif($value == 'no') {
 993                      $value = 0;
 994                  }
 995              } elseif($this->isDateType($field->getFieldDataType())) {
 996                  // For "after" and "before" conditions
 997                  $values = explode(' ',$value);
 998                  if(($operator == 'a' || $operator == 'b') && count($values) == 2){
 999                      if($operator == 'a'){
1000                          // for after comparator we should check the date after the given
1001                          $dateTime = new DateTime($value);
1002                          $modifiedDate = $dateTime->modify('+1 days');
1003                          $nextday = $modifiedDate->format('Y-m-d H:i:s');
1004                          $temp = strtotime($nextday)-1;
1005                          $date = date('Y-m-d H:i:s', $temp);
1006                          $value = getValidDBInsertDateTimeValue($date);
1007                      }else{
1008                          $dateTime = new DateTime($value);
1009                          $prevday = $dateTime->format('Y-m-d H:i:s');
1010                          $temp = strtotime($prevday)-1;
1011                          $date = date('Y-m-d H:i:s', $temp);
1012                          $value = getValidDBInsertDateTimeValue($date);
1013                      }
1014                  }else{
1015                      $value = getValidDBInsertDateTimeValue($value);
1016                      $dateTime = explode(' ', $value);
1017                      if($dateTime[1] == '00:00:00') {
1018                          $value = $dateTime[0];
1019                      }
1020                  }
1021              }
1022  
1023              if($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators(
1024                      $operator)) {
1025                  $value = "DATE_FORMAT(".$db->quote($value).", '%m%d')";
1026              } else {
1027                  $value = $db->sql_escape_string($value);
1028              }
1029  
1030              if(trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c')
1031                      && ($this->isStringType($field->getFieldDataType()) ||
1032                      $field->getFieldDataType() == 'picklist' ||
1033                      $field->getFieldDataType() == 'multipicklist')) {
1034                  $sql[] = "LIKE ''";
1035                  continue;
1036              }
1037  
1038              if(trim($value) == '' && ($operator == 'k') &&
1039                      $this->isStringType($field->getFieldDataType())) {
1040                  $sql[] = "NOT LIKE ''";
1041                  continue;
1042              }
1043  
1044              switch($operator) {
1045                  case 'e': $sqlOperator = "=";
1046                      break;
1047                  case 'n': $sqlOperator = "<>";
1048                      break;
1049                  case 's': $sqlOperator = "LIKE";
1050                      $value = "$value%";
1051                      break;
1052                  case 'ew': $sqlOperator = "LIKE";
1053                      $value = "%$value";
1054                      break;
1055                  case 'c': $sqlOperator = "LIKE";
1056                      $value = "%$value%";
1057                      break;
1058                  case 'k': $sqlOperator = "NOT LIKE";
1059                      $value = "%$value%";
1060                      break;
1061                  case 'l': $sqlOperator = "<";
1062                      break;
1063                  case 'g': $sqlOperator = ">";
1064                      break;
1065                  case 'm': $sqlOperator = "<=";
1066                      break;
1067                  case 'h': $sqlOperator = ">=";
1068                      break;
1069                  case 'a': $sqlOperator = ">";
1070                      break;
1071                  case 'b': $sqlOperator = "<";
1072                      break;
1073              }
1074              if(!$this->isNumericType($field->getFieldDataType()) &&
1075                      ($field->getFieldName() != 'birthday' || ($field->getFieldName() == 'birthday'
1076                              && $this->isRelativeSearchOperators($operator)))){
1077                  $value = "'$value'";
1078              }
1079              if($this->isNumericType($field->getFieldDataType()) && empty($value)) {
1080                  $value = '0';
1081              }
1082              $sql[] = "$sqlOperator $value";
1083          }
1084          return $sql;
1085      }
1086  
1087  	private function makeGroupSqlReplacements($fieldSqlList, $groupSql) {
1088          $pos = 0;
1089          $nextOffset = 0;
1090          foreach ($fieldSqlList as $index => $fieldSql) {
1091              $pos = strpos($groupSql, $index.'', $nextOffset);
1092              if($pos !== false) {
1093                  $beforeStr = substr($groupSql,0,$pos);
1094                  $afterStr = substr($groupSql, $pos + strlen($index));
1095                  $nextOffset = strlen($beforeStr.$fieldSql);
1096                  $groupSql = $beforeStr.$fieldSql.$afterStr;
1097              }
1098          }
1099          return $groupSql;
1100      }
1101  
1102  	private function isRelativeSearchOperators($operator) {
1103          $nonDaySearchOperators = array('l','g','m','h');
1104          return in_array($operator, $nonDaySearchOperators);
1105      }
1106  	private function isNumericType($type) {
1107          return ($type == 'integer' || $type == 'double' || $type == 'currency');
1108      }
1109  
1110  	private function isStringType($type) {
1111          return ($type == 'string' || $type == 'text' || $type == 'email' || $type == 'reference');
1112      }
1113  
1114  	private function isDateType($type) {
1115          return ($type == 'date' || $type == 'datetime');
1116      }
1117  
1118  	public function fixDateTimeValue($name, $value, $first = true) {
1119          $moduleFields = $this->getModuleFields();
1120          $field = $moduleFields[$name];
1121          $type = $field ? $field->getFieldDataType() : false;
1122          if($type == 'datetime') {
1123              if(strrpos($value, ' ') === false) {
1124                  if($first) {
1125                      return $value.' 00:00:00';
1126                  }else{
1127                      return $value.' 23:59:59';
1128                  }
1129              }
1130          }
1131          return $value;
1132      }
1133  
1134  	public function addCondition($fieldname,$value,$operator,$glue= null,$newGroup = false,
1135          $newGroupType = null, $ignoreComma = false) {
1136          $conditionNumber = $this->conditionInstanceCount++;
1137          if($glue != null && $conditionNumber > 0)
1138              $this->addConditionGlue ($glue);
1139  
1140          $this->groupInfo .= "$conditionNumber ";
1141          $this->whereFields[] = $fieldname;
1142          $this->ignoreComma = $ignoreComma;
1143          $this->reset();
1144          $this->conditionals[$conditionNumber] = $this->getConditionalArray($fieldname,
1145                  $value, $operator);
1146      }
1147  
1148  	public function addRelatedModuleCondition($relatedModule,$column, $value, $SQLOperator) {
1149          $conditionNumber = $this->conditionInstanceCount++;
1150          $this->groupInfo .= "$conditionNumber ";
1151          $this->manyToManyRelatedModuleConditions[$conditionNumber] = array('relatedModule'=>
1152              $relatedModule,'column'=>$column,'value'=>$value,'SQLOperator'=>$SQLOperator);
1153      }
1154  
1155  	public function addReferenceModuleFieldCondition($relatedModule, $referenceField, $fieldName, $value, $SQLOperator, $glue=null) {
1156          $conditionNumber = $this->conditionInstanceCount++;
1157          if($glue != null && $conditionNumber > 0)
1158              $this->addConditionGlue($glue);
1159  
1160          $this->groupInfo .= "$conditionNumber ";
1161          $this->referenceModuleField[$conditionNumber] = array('relatedModule'=> $relatedModule,'referenceField'=> $referenceField,'fieldName'=>$fieldName,'value'=>$value,
1162              'SQLOperator'=>$SQLOperator);
1163      }
1164  
1165  	private function getConditionalArray($fieldname,$value,$operator) {
1166          if(is_string($value)) {
1167              $value = trim($value);
1168          } elseif(is_array($value)) {
1169              $value = array_map(trim, $value);
1170          }
1171          return array('name'=>$fieldname,'value'=>$value,'operator'=>$operator);
1172      }
1173  
1174  	public function startGroup($groupType) {
1175          $this->groupInfo .= " $groupType (";
1176      }
1177  
1178  	public function endGroup() {
1179          $this->groupInfo .= ')';
1180      }
1181  
1182  	public function addConditionGlue($glue) {
1183          $this->groupInfo .= " $glue ";
1184      }
1185  
1186  	public function addUserSearchConditions($input) {
1187          global $log,$default_charset;
1188          if($input['searchtype']=='advance') {
1189  
1190              $json = new Zend_Json();
1191              $advft_criteria = $_REQUEST['advft_criteria'];
1192              if(!empty($advft_criteria))    $advft_criteria = $json->decode($advft_criteria);
1193              $advft_criteria_groups = $_REQUEST['advft_criteria_groups'];
1194              if(!empty($advft_criteria_groups))    $advft_criteria_groups = $json->decode($advft_criteria_groups);
1195  
1196              if(empty($advft_criteria) || count($advft_criteria) <= 0) {
1197                  return ;
1198              }
1199  
1200              $advfilterlist = getAdvancedSearchCriteriaList($advft_criteria, $advft_criteria_groups, $this->getModule());
1201  
1202              if(empty($advfilterlist) || count($advfilterlist) <= 0) {
1203                  return ;
1204              }
1205  
1206              if($this->conditionInstanceCount > 0) {
1207                  $this->startGroup(self::$AND);
1208              } else {
1209                  $this->startGroup('');
1210              }
1211              foreach ($advfilterlist as $groupindex=>$groupcolumns) {
1212                  $filtercolumns = $groupcolumns['columns'];
1213                  if(count($filtercolumns) > 0) {
1214                      $this->startGroup('');
1215                      foreach ($filtercolumns as $index=>$filter) {
1216                          $name = explode(':',$filter['columnname']);
1217                          if(empty($name[2]) && $name[1] == 'crmid' && $name[0] == 'vtiger_crmentity') {
1218                              $name = $this->getSQLColumn('id');
1219                          } else {
1220                              $name = $name[2];
1221                          }
1222                          $this->addCondition($name, $filter['value'], $filter['comparator']);
1223                          $columncondition = $filter['column_condition'];
1224                          if(!empty($columncondition)) {
1225                              $this->addConditionGlue($columncondition);
1226                          }
1227                      }
1228                      $this->endGroup();
1229                      $groupConditionGlue = $groupcolumns['condition'];
1230                      if(!empty($groupConditionGlue))
1231                          $this->addConditionGlue($groupConditionGlue);
1232                  }
1233              }
1234              $this->endGroup();
1235          } elseif($input['type']=='dbrd') {
1236              if($this->conditionInstanceCount > 0) {
1237                  $this->startGroup(self::$AND);
1238              } else {
1239                  $this->startGroup('');
1240              }
1241              $allConditionsList = $this->getDashBoardConditionList();
1242              $conditionList = $allConditionsList['conditions'];
1243              $relatedConditionList = $allConditionsList['relatedConditions'];
1244              $noOfConditions = count($conditionList);
1245              $noOfRelatedConditions = count($relatedConditionList);
1246              foreach ($conditionList as $index=>$conditionInfo) {
1247                  $this->addCondition($conditionInfo['fieldname'], $conditionInfo['value'],
1248                          $conditionInfo['operator']);
1249                  if($index < $noOfConditions - 1 || $noOfRelatedConditions > 0) {
1250                      $this->addConditionGlue(self::$AND);
1251                  }
1252              }
1253              foreach ($relatedConditionList as $index => $conditionInfo) {
1254                  $this->addRelatedModuleCondition($conditionInfo['relatedModule'],
1255                          $conditionInfo['conditionModule'], $conditionInfo['finalValue'],
1256                          $conditionInfo['SQLOperator']);
1257                  if($index < $noOfRelatedConditions - 1) {
1258                      $this->addConditionGlue(self::$AND);
1259                  }
1260              }
1261              $this->endGroup();
1262          } else {
1263              if(isset($input['search_field']) && $input['search_field'] !="") {
1264                  $fieldName=vtlib_purify($input['search_field']);
1265              } else {
1266                  return ;
1267              }
1268              if($this->conditionInstanceCount > 0) {
1269                  $this->startGroup(self::$AND);
1270              } else {
1271                  $this->startGroup('');
1272              }
1273              $moduleFields = $this->getModuleFields();
1274              $field = $moduleFields[$fieldName];
1275              $type = $field->getFieldDataType();
1276              if(isset($input['search_text']) && $input['search_text']!="") {
1277                  // search other characters like "|, ?, ?" by jagi
1278                  $value = $input['search_text'];
1279                  $stringConvert = function_exists(iconv) ? @iconv("UTF-8",$default_charset,$value)
1280                          : $value;
1281                  if(!$this->isStringType($type)) {
1282                      $value=trim($stringConvert);
1283                  }
1284  
1285                  if($type == 'picklist') {
1286                      global $mod_strings;
1287                      // Get all the keys for the for the Picklist value
1288                      $mod_keys = array_keys($mod_strings, $value);
1289                      if(sizeof($mod_keys) >= 1) {
1290                          // Iterate on the keys, to get the first key which doesn't start with LBL_      (assuming it is not used in PickList)
1291                          foreach($mod_keys as $mod_idx=>$mod_key) {
1292                              $stridx = strpos($mod_key, 'LBL_');
1293                              // Use strict type comparision, refer strpos for more details
1294                              if ($stridx !== 0) {
1295                                  $value = $mod_key;
1296                                  break;
1297                              }
1298                          }
1299                      }
1300                  }
1301                  if($type == 'currency') {
1302                      // Some of the currency fields like Unit Price, Total, Sub-total etc of Inventory modules, do not need currency conversion
1303                      if($field->getUIType() == '72') {
1304                          $value = CurrencyField::convertToDBFormat($value, null, true);
1305                      } else {
1306                          $currencyField = new CurrencyField($value);
1307                          $value = $currencyField->getDBInsertedValue();
1308                      }
1309                  }
1310              }
1311              if(!empty($input['operator'])) {
1312                  $operator = $input['operator'];
1313              } elseif(trim(strtolower($value)) == 'null'){
1314                  $operator = 'e';
1315              } else {
1316                  if(!$this->isNumericType($type) && !$this->isDateType($type)) {
1317                      $operator = 'c';
1318                  } else {
1319                      $operator = 'h';
1320                  }
1321              }
1322              $this->addCondition($fieldName, $value, $operator);
1323              $this->endGroup();
1324          }
1325      }
1326  
1327  	public function getDashBoardConditionList() {
1328          if(isset($_REQUEST['leadsource'])) {
1329              $leadSource = $_REQUEST['leadsource'];
1330          }
1331          if(isset($_REQUEST['date_closed'])) {
1332              $dateClosed = $_REQUEST['date_closed'];
1333          }
1334          if(isset($_REQUEST['sales_stage'])) {
1335              $salesStage = $_REQUEST['sales_stage'];
1336          }
1337          if(isset($_REQUEST['closingdate_start'])) {
1338              $dateClosedStart = $_REQUEST['closingdate_start'];
1339          }
1340          if(isset($_REQUEST['closingdate_end'])) {
1341              $dateClosedEnd = $_REQUEST['closingdate_end'];
1342          }
1343          if(isset($_REQUEST['owner'])) {
1344              $owner = vtlib_purify($_REQUEST['owner']);
1345          }
1346          if(isset($_REQUEST['campaignid'])) {
1347              $campaignId = vtlib_purify($_REQUEST['campaignid']);
1348          }
1349          if(isset($_REQUEST['quoteid'])) {
1350              $quoteId = vtlib_purify($_REQUEST['quoteid']);
1351          }
1352          if(isset($_REQUEST['invoiceid'])) {
1353              $invoiceId = vtlib_purify($_REQUEST['invoiceid']);
1354          }
1355          if(isset($_REQUEST['purchaseorderid'])) {
1356              $purchaseOrderId = vtlib_purify($_REQUEST['purchaseorderid']);
1357          }
1358  
1359          $conditionList = array();
1360          if(!empty($dateClosedStart) && !empty($dateClosedEnd)) {
1361  
1362              $conditionList[] = array('fieldname'=>'closingdate', 'value'=>$dateClosedStart,
1363                  'operator'=>'h');
1364              $conditionList[] = array('fieldname'=>'closingdate', 'value'=>$dateClosedEnd,
1365                  'operator'=>'m');
1366          }
1367          if(!empty($salesStage)) {
1368              if($salesStage == 'Other') {
1369                  $conditionList[] = array('fieldname'=>'sales_stage', 'value'=>'Closed Won',
1370                      'operator'=>'n');
1371                  $conditionList[] = array('fieldname'=>'sales_stage', 'value'=>'Closed Lost',
1372                      'operator'=>'n');
1373              } else {
1374                  $conditionList[] = array('fieldname'=>'sales_stage', 'value'=> $salesStage,
1375                      'operator'=>'e');
1376              }
1377          }
1378          if(!empty($leadSource)) {
1379              $conditionList[] = array('fieldname'=>'leadsource', 'value'=>$leadSource,
1380                      'operator'=>'e');
1381          }
1382          if(!empty($dateClosed)) {
1383              $conditionList[] = array('fieldname'=>'closingdate', 'value'=>$dateClosed,
1384                      'operator'=>'h');
1385          }
1386          if(!empty($owner)) {
1387              $conditionList[] = array('fieldname'=>'assigned_user_id', 'value'=>$owner,
1388                      'operator'=>'e');
1389          }
1390          $relatedConditionList = array();
1391          if(!empty($campaignId)) {
1392              $relatedConditionList[] = array('relatedModule'=>'Campaigns','conditionModule'=>
1393                  'Campaigns','finalValue'=>$campaignId, 'SQLOperator'=>'=');
1394          }
1395          if(!empty($quoteId)) {
1396              $relatedConditionList[] = array('relatedModule'=>'Quotes','conditionModule'=>
1397                  'Quotes','finalValue'=>$quoteId, 'SQLOperator'=>'=');
1398          }
1399          if(!empty($invoiceId)) {
1400              $relatedConditionList[] = array('relatedModule'=>'Invoice','conditionModule'=>
1401                  'Invoice','finalValue'=>$invoiceId, 'SQLOperator'=>'=');
1402          }
1403          if(!empty($purchaseOrderId)) {
1404              $relatedConditionList[] = array('relatedModule'=>'PurchaseOrder','conditionModule'=>
1405                  'PurchaseOrder','finalValue'=>$purchaseOrderId, 'SQLOperator'=>'=');
1406          }
1407          return array('conditions'=>$conditionList,'relatedConditions'=>$relatedConditionList);
1408      }
1409  
1410  	public function initForGlobalSearchByType($type, $value, $operator='s') {
1411          $fieldList = $this->meta->getFieldNameListByType($type);
1412          if($this->conditionInstanceCount <= 0) {
1413              $this->startGroup('');
1414          } else {
1415              $this->startGroup(self::$AND);
1416          }
1417          $nameFieldList = explode(',',$this->getModuleNameFields($this->module));
1418          foreach ($nameFieldList as $nameList) {
1419              $field = $this->meta->getFieldByColumnName($nameList);
1420              $this->fields[] = $field->getFieldName();
1421          }
1422          foreach ($fieldList as $index => $field) {
1423              $fieldName = $this->meta->getFieldByColumnName($field);
1424              $this->fields[] = $fieldName->getFieldName();
1425              if($index > 0) {
1426                  $this->addConditionGlue(self::$OR);
1427              }
1428              $this->addCondition($fieldName->getFieldName(), $value, $operator);
1429          }
1430          $this->endGroup();
1431          if(!in_array('id', $this->fields)) {
1432                  $this->fields[] = 'id';
1433          }
1434      }
1435  
1436  }
1437  ?>