PHP QueryGenerator::getSelectClauseColumnSQL方法代码示例

 /**
  * Function returns the Query for the relationhips
  * @param <Vtiger_Record_Model> $recordModel
  * @param type $actions
  * @return <String>
  */
 public function getQuery($recordModel, $actions = false)
 {
     $parentModuleModel = $this->getParentModuleModel();
     $relatedModuleModel = $this->getRelationModuleModel();
     $relatedModuleName = $relatedModuleModel->get('name');
     $parentModuleName = $parentModuleModel->get('name');
     $functionName = $this->get('name');
     $focus = CRMEntity::getInstance($parentModuleName);
     $focus->id = $recordModel->getId();
     if (method_exists($parentModuleModel, $functionName)) {
         $query = $parentModuleModel->{$functionName}($recordModel, $relatedModuleModel);
     } else {
         $result = $focus->{$functionName}($recordModel->getId(), $parentModuleModel->getId(), $relatedModuleModel->getId(), $actions);
         $query = $result['query'];
     }
     //modify query if any module has summary fields, those fields we are displayed in related list of that module
     $relatedListFields = $relatedModuleModel->getConfigureRelatedListFields();
     if (count($relatedListFields) > 0) {
         $currentUser = Users_Record_Model::getCurrentUserModel();
         $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser);
         $queryGenerator->setFields($relatedListFields);
         $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL();
         $newQuery = spliti('FROM', $query);
         $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql;
     }
     if ($functionName == ('get_pricebook_products' || 'get_pricebook_services')) {
         $selectColumnSql = $selectColumnSql . ', vtiger_pricebookproductrel.listprice';
     }
     $query = $selectColumnSql . ' FROM ' . $newQuery[1];
     return $query;
 }

 /**
  * Function returns the Query for the relationhips
  * @param <Vtiger_Record_Model> $recordModel
  * @param type $actions
  * @return <String>
  */
 public function getQuery($recordModel, $actions = false, $relationListView_Model = false)
 {
     $parentModuleModel = $this->getParentModuleModel();
     $relatedModuleModel = $this->getRelationModuleModel();
     $relatedModuleName = $relatedModuleModel->get('name');
     $parentModuleName = $parentModuleModel->get('name');
     $functionName = $this->get('name');
     $focus = CRMEntity::getInstance($parentModuleName);
     $focus->id = $recordModel->getId();
     if (method_exists($parentModuleModel, $functionName)) {
         $query = $parentModuleModel->{$functionName}($recordModel, $relatedModuleModel);
     } else {
         $result = $focus->{$functionName}($recordModel->getId(), $parentModuleModel->getId(), $relatedModuleModel->getId(), $actions);
         $query = $result['query'];
     }
     //modify query if any module has summary fields, those fields we are displayed in related list of that module
     $relatedListFields = $this->getRelationFields(true, true);
     if (count($relatedListFields) == 0) {
         $relatedListFields = $relatedModuleModel->getConfigureRelatedListFields();
     }
     if (count($relatedListFields) > 0) {
         $currentUser = Users_Record_Model::getCurrentUserModel();
         $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser);
         $queryGenerator->setFields($relatedListFields);
         $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL();
         $newQuery = explode('FROM', $query);
         $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid, ' . $selectColumnSql;
     }
     if ($functionName == 'get_product_pricebooks') {
         $selectColumnSql = $selectColumnSql . ' ,vtiger_pricebookproductrel.listprice, vtiger_pricebook.currency_id, vtiger_products.unit_price';
     }
     if ($functionName == 'get_service_pricebooks') {
         $selectColumnSql = $selectColumnSql . ' ,vtiger_pricebookproductrel.listprice, vtiger_pricebook.currency_id, vtiger_service.unit_price';
     }
     if ($selectColumnSql && $newQuery[1]) {
         $query = $selectColumnSql . ' FROM ' . $newQuery[1];
     }
     if ($relationListView_Model) {
         $searchParams = $relationListView_Model->get('search_params');
         $this->addSearchConditions($query, $searchParams, $relatedModuleName);
     }
     return $query;
 }

 /**
  * Function to get relation query for particular module with function name
  * @param <record> $recordId
  * @param <String> $functionName
  * @param Vtiger_Module_Model $relatedModule
  * @return <String>
  */
 public function getRelationQuery($recordId, $functionName, $relatedModule)
 {
     $relatedModuleName = $relatedModule->getName();
     $focus = CRMEntity::getInstance($this->getName());
     $focus->id = $recordId;
     $result = $focus->{$functionName}($recordId, $this->getId(), $relatedModule->getId());
     $query = $result['query'] . ' ' . $this->getSpecificRelationQuery($relatedModuleName);
     $nonAdminQuery = $this->getNonAdminAccessControlQueryForRelation($relatedModuleName);
     //modify query if any module has summary fields, those fields we are displayed in related list of that module
     $relatedListFields = $relatedModule->getConfigureRelatedListFields();
     if ($relatedModuleName == 'Documents') {
         $relatedListFields['filelocationtype'] = 'filelocationtype';
         $relatedListFields['filestatus'] = 'filestatus';
     }
     if (count($relatedListFields) > 0) {
         $currentUser = Users_Record_Model::getCurrentUserModel();
         $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser);
         $queryGenerator->setFields($relatedListFields);
         $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL();
         $newQuery = spliti('FROM', $query);
         $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql;
         $query = $selectColumnSql . ' FROM ' . $newQuery[1];
     }
     if ($nonAdminQuery) {
         $query = appendFromClauseToQuery($query, $nonAdminQuery);
     }
     return $query;
 }

 /**
  * Function to get relation query for particular module with function name
  * @param <record> $recordId
  * @param <String> $functionName
  * @param Vtiger_Module_Model $relatedModule
  * @return <String>
  */
 public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
 {
     $relatedModuleName = $relatedModule->getName();
     $focus = CRMEntity::getInstance($this->getName());
     $focus->id = $recordId;
     $result = $focus->{$functionName}($recordId, $this->getId(), $relatedModule->getId());
     $query = $result['query'] . ' ' . $this->getSpecificRelationQuery($relatedModuleName);
     //modify query if any module has summary fields, those fields we are displayed in related list of that module
     $relatedListFields = array();
     if ($relationModel) {
         $relatedListFields = $relationModel->getRelationFields(true, true);
     }
     if (count($relatedListFields) == 0) {
         $relatedListFields = $relatedModule->getConfigureRelatedListFields();
         if ($relatedModuleName == 'Documents') {
             $relatedListFields['filelocationtype'] = 'filelocationtype';
             $relatedListFields['filestatus'] = 'filestatus';
         }
     }
     if (count($relatedListFields) > 0) {
         $currentUser = Users_Record_Model::getCurrentUserModel();
         $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser);
         $queryGenerator->setFields($relatedListFields);
         $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL();
         $newQuery = spliti('FROM', $query);
         $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql;
         $query = $selectColumnSql . ' FROM ' . $newQuery[1];
     }
     $instance = CRMEntity::getInstance($relatedModuleName);
     $securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId);
     if ($securityParameter != '') {
         $query .= $securityParameter;
     }
     return $query;
 }

// 5 is a normal user that does not have access to cf_681
$queryGenerator = new QueryGenerator($moduleName, $user);
$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>";

                         $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) {

//.........这里部分代码省略.........
        $params = array_merge($params, $ownerIds);
    }
    $q .= " order by modifiedtime limit {$numRecordsLimit}";
    $result = $adb->pquery($q, $params);
    $modTime = array();
    for ($i = 0; $i < $adb->num_rows($result); $i++) {
        $modTime[] = $adb->query_result($result, $i, 'modifiedtime');
    }
    if (!empty($modTime)) {
        $maxModifiedTime = max($modTime);
    }
    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;

 /**
  * Function to get relation query for particular module with function name
  * @param <record> $recordId
  * @param <String> $functionName
  * @param Vtiger_Module_Model $relatedModule
  * @return <String>
  */
 public function getRelationQuery($recordId, $functionName, $relatedModule, $relationModel = false)
 {
     $relatedModuleName = $relatedModule->getName();
     $focus = CRMEntity::getInstance($this->getName());
     $focus->id = $recordId;
     switch ($functionName) {
         case 'get_many_to_many':
             $query = $this->getRelationQueryM2M($recordId, $relatedModule, $relationModel);
             break;
         case 'get_activities':
             $query = $this->getRelationQueryForActivities($recordId, $relatedModule, $relationModel);
             break;
         default:
             $result = $focus->{$functionName}($recordId, $this->getId(), $relatedModule->getId());
             $query = $result['query'] . ' ' . $this->getSpecificRelationQuery($relatedModuleName);
             break;
     }
     //modify query if any module has summary fields, those fields we are displayed in related list of that module
     $relatedListFields = [];
     if ($relationModel) {
         $relatedListFields = $relationModel->getRelationFields(true, true);
     }
     if (count($relatedListFields) == 0) {
         $relatedListFields = $relatedModule->getConfigureRelatedListFields();
         if ($relatedModuleName == 'Documents') {
             $relatedListFields['filelocationtype'] = 'filelocationtype';
             $relatedListFields['filestatus'] = 'filestatus';
         }
     }
     if (count($relatedListFields) > 0) {
         $currentUser = Users_Record_Model::getCurrentUserModel();
         $queryGenerator = new QueryGenerator($relatedModuleName, $currentUser);
         $queryGenerator->setFields($relatedListFields);
         if ($relationModel->showCreatorDetail()) {
             $queryGenerator->setCustomColumn('rel_created_user');
             $queryGenerator->setCustomColumn('rel_created_time');
         }
         if ($relationModel->showComment()) {
             $queryGenerator->setCustomColumn('rel_comment');
         }
         $selectColumnSql = $queryGenerator->getSelectClauseColumnSQL();
         $query = str_replace('FROM', 'from', $query);
         $newQuery = explode('from', $query);
         $selectColumnSql = 'SELECT DISTINCT vtiger_crmentity.crmid,' . $selectColumnSql;
         $query = $selectColumnSql . ' FROM ' . $newQuery[1];
     }
     $instance = CRMEntity::getInstance($relatedModuleName);
     $securityParameter = $instance->getUserAccessConditionsQuerySR($relatedModuleName, false, $recordId);
     if ($securityParameter != '') {
         $query .= $securityParameter;
     }
     return $query;
 }