PHP QueryGenerator::getFromClause方法代码示例

$queryGenerator->setFields(array('id', 'cf_681', 'acname'));
$query = $queryGenerator->getQuery();
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 "&nbsp;'e'&nbsp;= = value  (equals)<br>";
echo "&nbsp;'n'&nbsp;= <> value  (not equal)<br>";
echo "&nbsp;'s'&nbsp;= LIKE {$value}%  (starts with)<br>";
echo "&nbsp;'ew'&nbsp;= LIKE %{$value}  (ends with)<br>";
echo "&nbsp;'c'&nbsp;= LIKE %{$value}%  (contains)<br>";
echo "&nbsp;'k'&nbsp;= NOT LIKE %{$value}% (does not contain)<br>";
echo "&nbsp;'l'&nbsp;= &lt; value (less than)<br>";
echo "&nbsp;'b'&nbsp;= &lt; value (before, only for dates)<br>";
echo "&nbsp;'g'&nbsp;= &gt; value  (greater than)<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());
     }
 }

//.........这里部分代码省略.........
    if (!$maxModifiedTime) {
        $maxModifiedTime = $datetime;
    }
    foreach ($accessableModules as $elementType) {
        $handler = vtws_getModuleHandlerFromName($elementType, $user);
        $moduleMeta = $handler->getMeta();
        $deletedQueryCondition = $moduleMeta->getEntityDeletedQuery();
        preg_match_all("/(?:\\s+\\w+[ \t\n\r]+)?([^=]+)\\s*=([^\\s]+|'[^']+')/", $deletedQueryCondition, $deletedFieldDetails);
        $fieldNameDetails = $deletedFieldDetails[1];
        $deleteFieldValues = $deletedFieldDetails[2];
        $deleteColumnNames = array();
        foreach ($fieldNameDetails as $tableName_fieldName) {
            $fieldComp = explode(".", $tableName_fieldName);
            $deleteColumnNames[$tableName_fieldName] = $fieldComp[1];
        }
        $params = array($moduleMeta->getTabName(), $datetime, $maxModifiedTime);
        $queryGenerator = new QueryGenerator($elementType, $user);
        $fields = array();
        $moduleFields = $moduleMeta->getModuleFields();
        $moduleFieldNames = getSelectClauseFields($elementType, $moduleMeta, $user);
        $moduleFieldNames[] = 'id';
        $queryGenerator->setFields($moduleFieldNames);
        $selectClause = "SELECT " . $queryGenerator->getSelectClauseColumnSQL();
        // adding the fieldnames that are present in the delete condition to the select clause
        // since not all fields present in delete condition will be present in the fieldnames of the module
        foreach ($deleteColumnNames as $table_fieldName => $columnName) {
            if (!in_array($columnName, $moduleFieldNames)) {
                $selectClause .= ", " . $table_fieldName;
            }
        }
        if ($elementType == "Emails") {
            $fromClause = vtws_getEmailFromClause();
        } else {
            $fromClause = $queryGenerator->getFromClause();
        }
        $fromClause .= " INNER JOIN (select modifiedtime, crmid,deleted,setype FROM {$baseCRMTable} WHERE setype=? and modifiedtime >? and modifiedtime<=?";
        if (!$applicationSync) {
            $fromClause .= 'and smownerid IN(' . generateQuestionMarks($ownerIds) . ')';
            $params = array_merge($params, $ownerIds);
        }
        $fromClause .= ' ) vtiger_ws_sync ON (vtiger_crmentity.crmid = vtiger_ws_sync.crmid)';
        $q = $selectClause . " " . $fromClause;
        $result = $adb->pquery($q, $params);
        $recordDetails = array();
        $deleteRecordDetails = array();
        while ($arre = $adb->fetchByAssoc($result)) {
            $key = $arre[$moduleMeta->getIdColumn()];
            if (vtws_isRecordDeleted($arre, $deleteColumnNames, $deleteFieldValues)) {
                if (!$moduleMeta->hasAccess()) {
                    continue;
                }
                $output["deleted"][] = vtws_getId($moduleMeta->getEntityId(), $key);
            } else {
                if (!$moduleMeta->hasAccess() || !$moduleMeta->hasPermission(EntityMeta::$RETRIEVE, $key)) {
                    continue;
                }
                try {
                    $output["updated"][] = DataTransform::sanitizeDataWithColumn($arre, $moduleMeta);
                } catch (WebServiceException $e) {
                    //ignore records the user doesn't have access to.
                    continue;
                } catch (Exception $e) {
                    throw new WebServiceException(WebServiceErrorCode::$INTERNALERROR, "Unknown Error while processing request");
                }
            }
        }

 /**
  *
  * @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) {
//.........这里部分代码省略.........