PHP vtws_query函数代码示例

 function process(Mobile_API_Request $request)
 {
     $current_user = $this->getActiveUser();
     $query = $request->get('query');
     $nextPage = 0;
     $queryResult = false;
     if (preg_match("/(.*) LIMIT[^;]+;/i", $query)) {
         $queryResult = vtws_query($query, $current_user);
     } else {
         // Implicit limit and paging
         $query = rtrim($query, ";");
         $currentPage = intval($request->get('page', 0));
         $FETCH_LIMIT = Mobile::config('API_RECORD_FETCH_LIMIT');
         $startLimit = $currentPage * $FETCH_LIMIT;
         $queryWithLimit = sprintf("%s LIMIT %u,%u;", $query, $startLimit, $FETCH_LIMIT + 1);
         $queryResult = vtws_query($queryWithLimit, $current_user);
         // Determine paging
         $hasNextPage = count($queryResult) > $FETCH_LIMIT;
         if ($hasNextPage) {
             array_pop($queryResult);
             // Avoid sending next page record now
             $nextPage = $currentPage + 1;
         }
     }
     $records = array();
     if (!empty($queryResult)) {
         foreach ($queryResult as $recordValues) {
             $records[] = $this->processQueryResultRecord($recordValues, $current_user);
         }
     }
     $result = array('records' => $records, 'nextPage' => $nextPage);
     $response = new Mobile_API_Response();
     $response->setResult($result);
     return $response;
 }

function vtws_query_related($query, $id, $relatedLabel, $user, $filterClause = null)
{
    global $log, $adb;
    $webserviceObject = VtigerWebserviceObject::fromId($adb, $id);
    $handlerPath = $webserviceObject->getHandlerPath();
    $handlerClass = $webserviceObject->getHandlerClass();
    require_once $handlerPath;
    $handler = new $handlerClass($webserviceObject, $user, $adb, $log);
    $meta = $handler->getMeta();
    $entityName = $meta->getObjectEntityName($id);
    // Extract related module name from query.
    $relatedType = null;
    if (preg_match("/FROM\\s+([^\\s]+)/i", $query, $m)) {
        $relatedType = trim($m[1]);
    }
    // Check for presence of expected relation.
    $found = false;
    $relatedTypes = vtws_relatedtypes($entityName, $user);
    foreach ($relatedTypes['information'] as $label => $information) {
        if ($label == $relatedLabel && $information['name'] == $relatedType) {
            $found = true;
            break;
        }
    }
    if (!$found) {
        throw new WebServiceException(WebServiceErrorCode::$UNKOWNENTITY, "Relation specified is incorrect");
    }
    vtws_preserveGlobal('currentModule', $entityName);
    // Fetch related record IDs - so we can further retrieve complete information using vtws_query
    $relatedWebserviceObject = VtigerWebserviceObject::fromName($adb, $relatedType);
    $relatedHandlerPath = $relatedWebserviceObject->getHandlerPath();
    $relatedHandlerClass = $relatedWebserviceObject->getHandlerClass();
    require_once $relatedHandlerPath;
    $relatedHandler = new $relatedHandlerClass($relatedWebserviceObject, $user, $adb, $log);
    $relatedIds = $handler->relatedIds($id, $relatedType, $relatedLabel, $relatedHandler);
    // Initialize return value
    $relatedRecords = array();
    // Rewrite query and extract related records if there at least one.
    if (!empty($relatedIds)) {
        $relatedIdClause = "id IN ('" . implode("','", $relatedIds) . "')";
        if (stripos($query, 'WHERE') == false) {
            $query .= " WHERE " . $relatedIdClause;
        } else {
            $queryParts = explode('WHERE', $query);
            $query = $queryParts[0] . " WHERE " . $relatedIdClause;
            $query .= " AND " . $queryParts[1];
        }
        if (!empty($filterClause)) {
            $query .= " " . $filterClause;
        }
        $query .= ";";
        $relatedRecords = vtws_query($query, $user);
    }
    VTWS_PreserveGlobal::flush();
    return $relatedRecords;
}

 /**
  * Function to load record
  * @param <Number> $recordId
  * @return <Vtiger_Record_Model>
  */
 protected function loadRecord($recordId)
 {
     $moduleName = $this->module->getName();
     // Preparation to pull required tool-tip field values.
     $referenceFields = array();
     $fieldNames = array();
     foreach ($this->fields as $fieldModel) {
         $fieldType = $fieldModel->getFieldDataType();
         $fieldName = $fieldModel->get('name');
         $fieldNames[] = $fieldName;
         if ($fieldType == 'reference' || $fieldType == 'owner') {
             $referenceFields[] = $fieldName;
         }
     }
     $wsid = vtws_getWebserviceEntityId($moduleName, $recordId);
     $q = sprintf("SELECT %s FROM %s WHERE id='%s' LIMIT 1;", implode(',', $fieldNames), $moduleName, $wsid);
     // Retrieves only required fields of the record with permission check.
     try {
         $data = array_shift(vtws_query($q, Users_Record_Model::getCurrentUserModel()));
         if ($data) {
             // De-transform the webservice ID to CRM ID.
             foreach ($data as $key => $value) {
                 if (in_array($key, $referenceFields)) {
                     $value = array_pop(explode('x', $value));
                 }
                 $data[$key] = $value;
             }
         }
         $this->record = Vtiger_Record_Model::getCleanInstance($moduleName);
         $this->record->setData($data);
     } catch (WebServiceException $wex) {
         // Error retrieving information !
     }
     return $this;
 }

 function execute($fieldnames, $pagingModel = false)
 {
     $selectClause = sprintf("SELECT %s", implode(',', $fieldnames));
     $fromClause = sprintf("FROM %s", $this->moduleName);
     $whereClause = $this->prepareWhereClause(false);
     $orderClause = "";
     $groupClause = "";
     $limitClause = $pagingModel ? " LIMIT {$pagingModel->currentCount()},{$pagingModel->limit()}" : "";
     $query = sprintf("%s %s %s %s %s %s;", $selectClause, $fromClause, $whereClause, $orderClause, $groupClause, $limitClause);
     return vtws_query($query, $this->getUser());
 }

 function fetchRecordsWithId($module, $idlist, $user)
 {
     if (empty($idlist)) {
         return array();
     }
     $wsresult = vtws_query(sprintf("SELECT * FROM {$module} WHERE id IN ('%s');", implode("','", $idlist)), $user);
     if (!empty($wsresult)) {
         $resolvedRecords = array();
         foreach ($wsresult as $record) {
             $this->resolveRecordValues($record, $user);
             $resolvedRecords[] = $this->transformRecordWithGrouping($record, $module, false);
         }
     }
     return $resolvedRecords;
 }

 public function queryForRecords($query, $onlymine = true)
 {
     $user = Users_Record_Model::getCurrentUserModel();
     if ($onlymine) {
         $groupIds = $this->getGroupsIdsForUsers($user->getId());
         $groupWsIds = array();
         foreach ($groupIds as $groupId) {
             $groupWsIds[] = vtws_getWebserviceEntityId('Groups', $groupId);
         }
         $userwsid = vtws_getWebserviceEntityId('Users', $user->getId());
         $userAndGroupIds = array_merge(array($userwsid), $groupWsIds);
         $query .= " AND assigned_user_id IN ('" . implode("','", $userAndGroupIds) . "')";
     }
     // TODO take care of pulling 100+ records
     return vtws_query($query . ';', $user);
 }

 function execute($fieldnames, $pagingModel = false)
 {
     $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());
 }

 function process(Mobile_API_Request $request)
 {
     $current_user = $this->getActiveUser();
     //$module = $request->get('module');
     $module = $this->detectModuleName($request->get('record'));
     $record = $this->processRetrieve($request);
     $this->resolveRecordValues($record, $current_user);
     $response = new Mobile_API_Response();
     $ret_arr = array('record' => $record);
     if ($request->get('module')) {
         $module = $request->get('module');
         $moduleWSFieldNames = Mobile_WS_Utils::getEntityFieldnames($module);
         foreach ($moduleWSFieldNames as $key => $value) {
             $relatedlistcontent[$key] = $record[$value];
         }
         $relatedlistcontent['id'] = $record['id'];
         $ret_arr['relatedlistcontent'] = $relatedlistcontent;
     } elseif (vtlib_isModuleActive('ModComments') and $module != 'HelpDesk') {
         include_once 'include/Webservices/Query.php';
         $comments = vtws_query("SELECT * FROM ModComments WHERE related_to = '" . $record['id'] . "' ORDER BY createdtime DESC LIMIT 5;", $current_user);
         if (count($comments) > 0) {
             foreach ($comments as &$comment) {
                 $comment['assigned_user_id'] = vtws_getName($comment['assigned_user_id'], $current_user);
                 $comment['createdtime'] = DateTimeField::convertToUserFormat($comment['createdtime']);
             }
             $ret_arr['comments'] = $comments;
         } else {
             $ret_arr['comments'] = array();
         }
     } elseif ($module == 'HelpDesk') {
         //there is currently no vtws service for ticket comments
         $comments = Mobile_WS_Utils::getTicketComments($record);
         if (!empty($comments)) {
             foreach ($comments as &$comment) {
                 $comment['assigned_user_id'] = vtws_getName($comment['assigned_user_id'], $current_user);
                 $comment['createdtime'] = DateTimeField::convertToUserFormat($comment['createdtime']);
             }
             $ret_arr['comments'] = $comments;
         } else {
             $ret_arr['comments'] = array();
         }
     }
     $response->setResult($ret_arr);
     return $response;
 }

 static function lookupMailInVtiger($searchTerm, $user)
 {
     $handler = vtws_getModuleHandlerFromName('Emails', $user);
     $meta = $handler->getMeta();
     $moduleFields = $meta->getModuleFields();
     $parentIdFieldInstance = $moduleFields['parent_id'];
     $referenceModules = $parentIdFieldInstance->getReferenceList();
     $filteredResult = array();
     foreach ($referenceModules as $referenceModule) {
         $referenceModuleHandler = vtws_getModuleHandlerFromName($referenceModule, $user);
         $referenceModuleMeta = $referenceModuleHandler->getMeta();
         $referenceModuleEmailFields = $referenceModuleMeta->getEmailFields();
         $referenceModuleEntityFields = $referenceModuleMeta->getNameFields();
         $referenceModuleEntityFieldsArray = explode(',', $referenceModuleEntityFields);
         $searchFieldList = array_merge($referenceModuleEmailFields, $referenceModuleEntityFieldsArray);
         if (!empty($searchFieldList) && !empty($referenceModuleEmailFields)) {
             $searchFieldListString = implode(',', $referenceModuleEmailFields);
             $where = null;
             for ($i = 0; $i < count($searchFieldList); $i++) {
                 if ($i == count($searchFieldList) - 1) {
                     $where .= sprintf($searchFieldList[$i] . " like '%s'", $searchTerm);
                 } else {
                     $where .= sprintf($searchFieldList[$i] . " like '%s' or ", $searchTerm);
                 }
             }
             if (!empty($where)) {
                 $where = "WHERE {$where}";
             }
             $result = vtws_query("select {$searchFieldListString} from {$referenceModule} {$where};", $user);
             foreach ($result as $record) {
                 foreach ($searchFieldList as $searchField) {
                     if (!empty($record[$searchField])) {
                         $filteredResult[] = array('id' => $record[$searchField], 'name' => $record[$searchField] . " - " . getTranslatedString($referenceModule), 'record' => $record['id']);
                     }
                 }
             }
         }
     }
     return $filteredResult;
 }

 function process(Mobile_API_Request $request)
 {
     $current_user = $this->getActiveUser();
     $current_user_wsid = sprintf("%sx%s", Mobile_WS_Utils::getEntityModuleWSId("Users"), $current_user->id);
     $module = $request->get('module');
     $lastSyncTime = $request->get('syncToken', 0);
     $currentPage = intval($request->get('page', 0));
     $isPrivateMode = $this->isModePrivate($request);
     $FETCH_LIMIT = Mobile::config('API_RECORD_FETCH_LIMIT');
     $startLimit = $currentPage * $FETCH_LIMIT;
     // Keep track of sync-token for futher reference
     $maxSyncTime = $lastSyncTime;
     $describeInfo = vtws_describe($module, $current_user);
     $this->cacheDescribeInfo($describeInfo);
     $hasAssignedToField = false;
     foreach ($describeInfo['fields'] as $fieldinfo) {
         if ($fieldinfo['name'] == 'assigned_user_id') {
             $hasAssignedToField = true;
             break;
         }
     }
     /////////////////////////////
     // MODIFIED RECORDS TRACKING
     /////////////////////////////
     if (empty($lastSyncTime)) {
         // No previous state information available? Lookup records recently modified
         if ($hasAssignedToField && $isPrivateMode) {
             $queryActive = sprintf("SELECT * FROM %s WHERE assigned_user_id = '%s' ORDER BY modifiedtime DESC", $module, $current_user_wsid);
         } else {
             $queryActive = sprintf("SELECT * FROM %s ORDER BY modifiedtime DESC", $module);
         }
     } else {
         // Attempt to lookup records from previous state
         if ($hasAssignedToField && $isPrivateMode) {
             $queryActive = sprintf("SELECT * FROM %s WHERE assigned_user_id = '%s' AND modifiedtime > '%s'", $module, $current_user_wsid, date("Y-m-d H:i:s", $lastSyncTime));
         } else {
             $queryActive = sprintf("SELECT * FROM %s WHERE modifiedtime > '%s'", $module, date("Y-m-d H:i:s", $lastSyncTime));
         }
     }
     // Try to fetch record with paging (one extra record fetch is attempted to determine presence of next page)
     $activeQuery = sprintf("%s LIMIT %u,%u;", $queryActive, $startLimit, $FETCH_LIMIT + 1);
     $activeResult = vtws_query($activeQuery, $current_user);
     // Determine paging
     $hasNextPage = count($activeResult) > $FETCH_LIMIT;
     // Special case handling merge Events records
     if ($module == 'Calendar') {
         $activeResult2 = vtws_query(str_replace('Calendar', 'Events', $activeQuery), $current_user);
         if (!empty($activeResult2)) {
             $activeResult = array_merge($activeResult, $activeResult2);
             if (!$hasNextPage) {
                 // If there was not Calendar next-page of records - check with Events
                 $hasNextPage = count($activeResult) > $FETCH_LIMIT;
             }
         }
         // Indicator that we fetched both Calendar+Events
         $FETCH_LIMIT *= 2;
     }
     $nextPage = 0;
     if ($hasNextPage) {
         array_pop($activeResult);
         // Avoid sending next page record now
         $nextPage = $currentPage + 1;
     }
     // Resolved record details
     $resolvedModifiedRecords = array();
     $resolvedDeletedRecords = array();
     if (!empty($activeResult)) {
         foreach ($activeResult as $recordValues) {
             $this->resolveRecordValues($recordValues, $current_user);
             $transformedRecord = $this->transformRecordWithGrouping($recordValues, $module);
             // Update entity fieldnames
             $transformedRecord['labelFields'] = $this->cachedEntityFieldnames($module);
             $resolvedModifiedRecords[] = $transformedRecord;
             $modifiedTimeInSeconds = strtotime($recordValues['modifiedtime']);
             if ($maxSyncTime < $modifiedTimeInSeconds) {
                 $maxSyncTime = $modifiedTimeInSeconds;
             }
         }
     }
     ////////////////////////////
     // DELETED RECORDS TRACKING
     ////////////////////////////
     // Only when there is previous state information and is first page
     if (!empty($lastSyncTime) && $currentPage === 0) {
         $adb = PearDatabase::getInstance();
         $queryDeletedParameters = array($module, date('Y-m-d H:i:s', $lastSyncTime));
         $andsmowneridequal = "";
         if ($hasAssignedToField) {
             if ($isPrivateMode) {
                 $queryDeletedParameters[] = $current_user->id;
                 $andsmowneridequal = " AND vtiger_crmentity.smownerid=?";
             } else {
                 $andsmowneridequal = Mobile_WS_Utils::querySecurityFromSuffix($module, $current_user);
             }
         }
         // Since Calendar and Events are merged
         if ($module == 'Calendar') {
             $queryDeleted = $adb->pquery("SELECT activityid as crmid, activitytype as setype FROM vtiger_activity\n\t\t\t\t\tINNER JOIN vtiger_crmentity ON vtiger_activity.activityid=vtiger_crmentity.crmid\n\t\t\t\t\tAND vtiger_crmentity.deleted=1 AND vtiger_crmentity.setype=? AND vtiger_crmentity.modifiedtime > ?\n\t\t\t\t\tLEFT JOIN vtiger_users ON vtiger_users.id = vtiger_crmentity.smownerid\n\t\t\t\t\tLEFT JOIN vtiger_groups ON vtiger_groups.groupid = vtiger_crmentity.smownerid WHERE 1=1\t{$andsmowneridequal} ", $queryDeletedParameters);
         } else {
             if ($module == 'Leads') {
//.........这里部分代码省略.........

 /**
  * Returns the List of Matching records with the Email Address
  * @global Users Instance $current_user
  * @param String $module
  * @param Email Address $email
  * @return Array
  */
 function lookupModuleRecordsWithEmail($module, $email, $msguid)
 {
     global $current_user;
     $query = $this->buildSearchQuery($module, $email, 'EMAIL');
     $qresults = vtws_query($query, $current_user);
     $describe = $this->ws_describe($module);
     $labelFields = $describe['labelFields'];
     switch ($module) {
         case 'HelpDesk':
             $labelFields = 'ticket_title';
             break;
         case 'Documents':
             $labelFields = 'notes_title';
             break;
     }
     $labelFields = explode(',', $labelFields);
     $results = array();
     foreach ($qresults as $qresult) {
         $labelValues = array();
         foreach ($labelFields as $fieldname) {
             if (isset($qresult[$fieldname])) {
                 $labelValues[] = $qresult[$fieldname];
             }
         }
         $ids = vtws_getIdComponents($qresult['id']);
         $linkedto = MailManager::isEMailAssociatedWithCRMID($msguid, $ids[1]);
         $results[] = array('wsid' => $qresult['id'], 'id' => $ids[1], 'label' => implode(' ', $labelValues), 'linked' => $linkedto);
     }
     return $results;
 }

 function getDraftMail($request)
 {
     global $adb, $current_user;
     $handler = vtws_getModuleHandlerFromName('Emails', $current_user);
     $meta = $handler->getMeta();
     if (!$meta->hasReadAccess()) {
         return false;
     }
     $id = vtws_getWebserviceEntityId('Emails', $request->get('id'));
     $draftMail = vtws_query("SELECT * FROM Emails where id = {$id};", $current_user);
     $emailId = vtws_getIdComponents($id);
     $draftMail['attachments'] = $this->getAttachmentDetails($emailId[1]);
     $draftMail[0]['id'] = $request->get('id');
     return $draftMail;
 }

 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 getDraftMail($request)
 {
     $db = PearDatabase::getInstance();
     $currentUserModel = Users_Record_Model::getCurrentUserModel();
     $handler = vtws_getModuleHandlerFromName('Emails', $currentUserModel);
     $meta = $handler->getMeta();
     if (!$meta->hasReadAccess()) {
         return false;
     }
     $id = vtws_getWebserviceEntityId('Emails', $request->get('id'));
     $draftMail = vtws_query("SELECT * FROM Emails where id = {$id};", $currentUserModel);
     $emailId = vtws_getIdComponents($id);
     $draftMail['attachments'] = $this->getAttachmentDetails($emailId[1]);
     $draftMail[0]['id'] = $request->get('id');
     return $draftMail;
 }

 /**
  * vtws_query wrap.
  * @param type $query
  * @return type
  */
 public function query($query)
 {
     return vtws_query($query, $this->restUser);
 }