/** * 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 " '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>";
$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 vtws_sync($mtime, $elementType, $syncType, $user) { global $adb, $recordString, $modifiedTimeString; $numRecordsLimit = 100; $ignoreModules = array("Users"); $typed = true; $dformat = "Y-m-d H:i:s"; $datetime = date($dformat, $mtime); $setypeArray = array(); $setypeData = array(); $setypeHandler = array(); $setypeNoAccessArray = array(); $output = array(); $output["updated"] = array(); $output["deleted"] = array(); $applicationSync = false; if (is_object($syncType) && $syncType instanceof Users) { $user = $syncType; } else { if ($syncType == 'application') { $applicationSync = true; } else { if ($syncType == 'userandgroup') { $userAndGroupSync = true; } } } if ($applicationSync && !is_admin($user)) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Only admin users can perform application sync"); } $ownerIds = array($user->id); // To get groupids in which this user exist if ($userAndGroupSync) { $groupresult = $adb->pquery("select groupid from vtiger_users2group where userid=?", array($user->id)); $numOfRows = $adb->num_rows($groupresult); if ($numOfRows > 0) { for ($i = 0; $i < $numOfRows; $i++) { $ownerIds[count($ownerIds)] = $adb->query_result($groupresult, $i, "groupid"); } } } // End if (!isset($elementType) || $elementType == '' || $elementType == null) { $typed = false; } $adb->startTransaction(); $accessableModules = array(); $entityModules = array(); $modulesDetails = vtws_listtypes(null, $user); $moduleTypes = $modulesDetails['types']; $modulesInformation = $modulesDetails["information"]; foreach ($modulesInformation as $moduleName => $entityInformation) { if ($entityInformation["isEntity"]) { $entityModules[] = $moduleName; } } if (!$typed) { $accessableModules = $entityModules; } else { if (!in_array($elementType, $entityModules)) { throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to perform the operation is denied"); } $accessableModules[] = $elementType; } $accessableModules = array_diff($accessableModules, $ignoreModules); if (count($accessableModules) <= 0) { $output['lastModifiedTime'] = $mtime; $output['more'] = false; return $output; } if ($typed) { $handler = vtws_getModuleHandlerFromName($elementType, $user); $moduleMeta = $handler->getMeta(); $entityDefaultBaseTables = $moduleMeta->getEntityDefaultTableList(); //since there will be only one base table for all entities $baseCRMTable = $entityDefaultBaseTables[0]; if ($elementType == "Calendar" || $elementType == "Events") { $baseCRMTable = getSyncQueryBaseTable($elementType); } } else { $baseCRMTable = " vtiger_crmentity "; } //modifiedtime - next token $q = "SELECT modifiedtime FROM {$baseCRMTable} WHERE modifiedtime>? and setype IN(" . generateQuestionMarks($accessableModules) . ") "; $params = array($datetime); foreach ($accessableModules as $entityModule) { if ($entityModule == "Events") { $entityModule = "Calendar"; } $params[] = $entityModule; } if (!$applicationSync) { $q .= ' and smownerid IN(' . generateQuestionMarks($ownerIds) . ')'; $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; } 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"); } } } } $q = "SELECT crmid FROM {$baseCRMTable} WHERE modifiedtime>? and setype IN(" . generateQuestionMarks($accessableModules) . ")"; $params = array($maxModifiedTime); foreach ($accessableModules as $entityModule) { if ($entityModule == "Events") { $entityModule = "Calendar"; } $params[] = $entityModule; } if (!$applicationSync) { $q .= 'and smownerid IN(' . generateQuestionMarks($ownerIds) . ')'; $params = array_merge($params, $ownerIds); } $result = $adb->pquery($q, $params); if ($adb->num_rows($result) > 0) { $output['more'] = true; } else { $output['more'] = false; } if (!$maxModifiedTime) { $modifiedtime = $mtime; } else { $modifiedtime = vtws_getSeconds($maxModifiedTime); } if (is_string($modifiedtime)) { $modifiedtime = intval($modifiedtime); } $output['lastModifiedTime'] = $modifiedtime; $error = $adb->hasFailedTransaction(); $adb->completeTransaction(); if ($error) { throw new WebServiceException(WebServiceErrorCode::$DATABASEQUERYERROR, vtws_getWebserviceTranslatedString('LBL_' . WebServiceErrorCode::$DATABASEQUERYERROR)); } VTWS_PreserveGlobal::flush(); return $output; }
/** * 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; }