echo "{$query}<br>"; testquery($query); $current_user = $hold_user; echo "<h2>Query as individual parts</h2>"; echo "<b>We can get the different parts of the query individually so we can construct specific queries easily</b><br>"; $queryGenerator = new QueryGenerator($moduleName, $current_user); $queryGenerator->setFields(array('id', 'cf_681', 'accountname')); echo "<b>Full query:</b><br>"; $query = $queryGenerator->getQuery(); echo "{$query}<br>"; echo "<b>SELECT:</b><br>"; echo $queryGenerator->getSelectClauseColumnSQL(); echo "<br><b>FROM:</b><br>"; echo $queryGenerator->getFromClause(); echo "<br><b>WHERE:</b><br>"; echo $queryGenerator->getWhereClause(); echo "<br>"; testquery($query); echo "<h2>Query with conditions</h2>"; echo "<b>Supported operators:</b><br>"; echo " 'e' = = value (equals)<br>"; echo " 'n' = <> value (not equal)<br>"; echo " 's' = LIKE {$value}% (starts with)<br>"; echo " 'ew' = LIKE %{$value} (ends with)<br>"; echo " 'c' = LIKE %{$value}% (contains)<br>"; echo " 'k' = NOT LIKE %{$value}% (does not contain)<br>"; echo " 'l' = < value (less than)<br>"; echo " 'b' = < value (before, only for dates)<br>"; echo " 'g' = > value (greater than)<br>"; echo " 'a' = > value (after, only for dates)<br>"; echo " 'm' = <= value (less or equal)<br>";
$queryGenerator->startGroup('OR'); } foreach ($Module_Status_Fields[$evstat] as $condition) { $queryGenerator->addCondition($condition['field'], $condition['value'], $condition['operator'], $condition['glue']); } $queryGenerator->endGroup(); } } if ($encompas_group) { $queryGenerator->endGroup(); } } } $list_query = $queryGenerator->getQuery(); $userNameSql = getSqlForNameInDisplayFormat(array('first_name' => 'vtiger_users.first_name', 'last_name' => 'vtiger_users.last_name'), 'Users'); $list_query = "SELECT distinct vtiger_crmentity.crmid, vtiger_groups.groupname, {$userNameSql} as user_name, " . $queryGenerator->getSelectClauseColumnSQL() . $queryGenerator->getFromClause() . $queryGenerator->getWhereClause(); $list_array = array(); } else { $list_query = getCalendar4YouListQuery($userid, $invites); if ($record != "") { $list_query .= " AND vtiger_crmentity.crmid = '" . $record . "'"; } else { $list_query .= " AND vtiger_activity.date_start <= '" . $end_date . "'"; $list_query .= " AND vtiger_activity.due_date >= '" . $start_date . "'"; } if (!$invites) { $list_query .= " AND vtiger_crmentity.smownerid = ?"; $list_query .= " AND vtiger_activity.activitytype = ?"; $list_array = array($userid, $activitytype); } if (count($Event_Status) > 0) {
function execute($fieldnames, $pagingModel = false) { if ($this->moduleName == 'Project') { // Custom View include_once 'modules/CustomView/CustomView.php'; include_once 'include/QueryGenerator/QueryGenerator.php'; include_once 'modules/Mobile/api/ws/Controller.php'; include_once 'include/DatabaseUtil.php'; $customView = new CustomView($this->moduleName); $viewid = $customView->getViewId($this->moduleName); $customview_html = $customView->getCustomViewCombo($viewid); $viewinfo = $customView->getCustomViewByCvid($viewid); global $current_user; // Required for vtws_update API $userid = $_SESSION['_authenticated_user_id']; $current_user = CRMEntity::getInstance('Users'); $current_user = $current_user->retrieveCurrentUserInfoFromFile($userid); $queryGenerator = new QueryGenerator($this->moduleName, $current_user); if ($viewid != "0") { $queryGenerator->initForCustomViewById($viewid); } else { $queryGenerator->initForDefaultCustomView(); } $selectClause = sprintf("SELECT %s", implode(',', $fieldnames) . ",vtiger_project.projectid"); $fromClause = $queryGenerator->getFromClause(); $whereClause = $queryGenerator->getWhereClause(); $orderClause = ""; $groupClause = ""; $limitClause = $pagingModel ? " LIMIT {$pagingModel->currentCount()},{$pagingModel->limit()}" : ""; if (!empty($this->criterias)) { $_sortCriteria = $this->criterias['_sort']; if (!empty($_sortCriteria)) { $orderClause = $_sortCriteria; } } $query = sprintf("%s %s %s %s %s %s;", $selectClause, $fromClause, $whereClause, $orderClause, $groupClause, $limitClause); global $adb; $result = $adb->pquery($query, array()); $noofrows = $adb->num_rows($result); $lstresult = array(); for ($i = 0; $i < $noofrows; $i++) { $lstresult[$i]['firstname'] = $adb->query_result($result, $i, 'projectname'); $lstresult[$i]['id'] = "31x" . $adb->query_result($result, $i, 'projectid'); } return $lstresult; } else { $selectClause = sprintf("SELECT %s", implode(',', $fieldnames)); $fromClause = sprintf("FROM %s", $this->moduleName); $whereClause = ""; $orderClause = ""; $groupClause = ""; $limitClause = $pagingModel ? " LIMIT {$pagingModel->currentCount()},{$pagingModel->limit()}" : ""; if (!empty($this->criterias)) { $_sortCriteria = $this->criterias['_sort']; if (!empty($_sortCriteria)) { $orderClause = $_sortCriteria; } } $query = sprintf("%s %s %s %s %s %s;", $selectClause, $fromClause, $whereClause, $orderClause, $groupClause, $limitClause); return vtws_query($query, $this->getUser()); } }
public function addSearchConditions($query, $searchParams, $related_module) { if (!empty($searchParams)) { $currentUserModel = Users_Record_Model::getCurrentUserModel(); $queryGenerator = new QueryGenerator($related_module, $currentUserModel); $queryGenerator->parseAdvFilterList($searchParams); $where = $queryGenerator->getWhereClause(true); $query .= $where; } return $query; }
/** * * @param mixed $value * @param String $operator * @param WebserviceField $field */ private function getConditionValue($value, $operator, $field) { $operator = strtolower($operator); $db = PearDatabase::getInstance(); $noncommaSeparatedFieldTypes = array('currency', 'percentage', 'double', 'integer', 'number'); if (in_array($field->getFieldDataType(), $noncommaSeparatedFieldTypes)) { if (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } // if ($field->getFieldDataType() == 'multipicklist' && in_array($operator, array('e', 'n'))) { // $valueArray = getCombinations($valueArray); // foreach ($valueArray as $key => $value) { // $valueArray[$key] = ltrim($value, ' |##| '); // } // } } elseif (is_string($value)) { $valueArray = explode(',', $value); } elseif (is_array($value)) { $valueArray = $value; } else { $valueArray = array($value); } $sql = array(); if ($operator == 'exists') { global $current_user, $adb; $mid = getTabModuleName($field->getTabId()); $qg = new QueryGenerator($mid, $current_user); $qg->addCondition($field->getFieldName(), $value, 'e'); $sql[] = 'SELECT EXISTS(SELECT 1 ' . $qg->getFromClause() . $qg->getWhereClause() . ')'; return $sql; } if ($operator == 'i' or $operator == 'in' or $operator == 'ni' or $operator == 'nin') { $vals = array_map(array($db, 'quote'), $valueArray); $sql[] = (($operator == 'ni' or $operator == 'nin') ? ' NOT ' : '') . 'IN (' . implode(',', $vals) . ')'; return $sql; } 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())) { $valueArray[0] = getValidDBInsertDateTimeValue($valueArray[0]); $valueArray[1] = getValidDBInsertDateTimeValue($valueArray[1]); } $sql[] = "BETWEEN " . $db->quote($valueArray[0]) . " AND " . $db->quote($valueArray[1]); } return $sql; } $yes = strtolower(getTranslatedString('yes')); $no = strtolower(getTranslatedString('no')); foreach ($valueArray as $value) { if (!$this->isStringType($field->getFieldDataType())) { $value = trim($value); } if ($operator == 'empty' || $operator == 'y') { $sql[] = sprintf("IS NULL OR %s = ''", $this->getSQLColumn($field->getFieldName())); continue; } if ($operator == 'ny') { $sql[] = sprintf("IS NOT NULL AND %s != ''", $this->getSQLColumn($field->getFieldName())); continue; } if (strtolower(trim($value)) == 'null' || trim($value) == '' && !$this->isStringType($field->getFieldDataType()) && ($operator == 'e' || $operator == 'n')) { if ($operator == 'e') { $sql[] = "IS NULL"; continue; } $sql[] = "IS NOT NULL"; continue; } elseif ($field->getFieldDataType() == 'boolean') { $value = strtolower($value); if ($value == 'yes' or $value == $yes) { $value = 1; } elseif ($value == 'no' or $value == $no) { $value = 0; } } elseif ($this->isDateType($field->getFieldDataType())) { $value = getValidDBInsertDateTimeValue($value); if (empty($value)) { $sql[] = 'IS NULL or ' . $field->getTableName() . '.' . $field->getFieldName() . " = ''"; return $sql; } } elseif ($field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist') { if (!isValueInPicklist($value, $field->getFieldName())) { $value = getTranslationKeyFromTranslatedValue($this->module, $value); } } else { if ($field->getFieldDataType() === 'currency') { $uiType = $field->getUIType(); if ($uiType == 72) { $value = CurrencyField::convertToDBFormat($value, null, true); } elseif ($uiType == 71) { $value = CurrencyField::convertToDBFormat($value, $this->user); } } } if ($field->getFieldName() == 'birthday' && !$this->isRelativeSearchOperators($operator)) { $value = "DATE_FORMAT(" . $db->quote($value) . ", '%m%d')"; } else { $value = $db->sql_escape_string($value); } if (trim($value) == '' && ($operator == 's' || $operator == 'ew' || $operator == 'c') && ($this->isStringType($field->getFieldDataType()) || $field->getFieldDataType() == 'picklist' || $field->getFieldDataType() == 'multipicklist')) { $sql[] = "LIKE ''"; continue; } if (trim($value) == '' && $operator == 'k' && $this->isStringType($field->getFieldDataType())) { $sql[] = "NOT LIKE ''"; continue; } switch ($operator) { case 'e': $sqlOperator = "="; break; case 'n': $sqlOperator = "<>"; break; case 's': $sqlOperator = "LIKE"; $value = "{$value}%"; break; case 'ew': $sqlOperator = "LIKE"; $value = "%{$value}"; break; case 'c': $sqlOperator = "LIKE"; $value = "%{$value}%"; break; case 'k': $sqlOperator = "NOT LIKE"; $value = "%{$value}%"; break; case 'l': $sqlOperator = "<"; break; case 'g': $sqlOperator = ">"; break; case 'm': $sqlOperator = "<="; break; case 'h': $sqlOperator = ">="; break; case 'a': $sqlOperator = ">"; break; case 'b': $sqlOperator = "<"; break; } if (!$this->isNumericType($field->getFieldDataType()) && ($field->getFieldName() != 'birthday' || $field->getFieldName() == 'birthday' && $this->isRelativeSearchOperators($operator))) { $value = "'{$value}'"; } if ($this->isNumericType($field->getFieldDataType()) && empty($value)) { $value = '0'; } $sql[] = "{$sqlOperator} {$value}"; } return $sql; }