/**
* 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;
}
