function __FQNExtendedQueryGetQuery($q, $user)
{
global $adb, $log;
$moduleRegex = "/[fF][rR][Oo][Mm]\\s+([^\\s;]+)(.*)/";
preg_match($moduleRegex, $q, $m);
$mainModule = trim($m[1]);
// pickup meta data of module
$webserviceObject = VtigerWebserviceObject::fromName($adb, $mainModule);
$handlerPath = $webserviceObject->getHandlerPath();
$handlerClass = $webserviceObject->getHandlerClass();
require_once $handlerPath;
$handler = new $handlerClass($webserviceObject, $user, $adb, $log);
$meta = $handler->getMeta();
$mainModule = $meta->getTabName();
// normalize module name
// check modules
if (!$meta->isModuleEntity()) {
throw new WebserviceException('INVALID_MODULE', "Given main module ({$mainModule}) cannot be found");
}
// check permission on module
$entityName = $meta->getEntityName();
$types = vtws_listtypes(null, $user);
if (!in_array($entityName, $types['types'])) {
throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to perform the operation on module ({$mainModule}) is denied");
}
if (!$meta->hasReadAccess()) {
throw new WebServiceException(WebServiceErrorCode::$ACCESSDENIED, "Permission to read module is denied");
}
// user has enough permission to start process
$fieldcolumn = $meta->getFieldColumnMapping();
$queryGenerator = new QueryGenerator($mainModule, $user);
$queryColumns = trim(substr($q, 6, stripos($q, ' from ') - 5));
$queryColumns = explode(',', $queryColumns);
$queryColumns = array_map(trim, $queryColumns);
$countSelect = $queryColumns == array('count(*)');
$queryRelatedModules = array();
foreach ($queryColumns as $k => $field) {
if (strpos($field, '.') > 0) {
list($m, $f) = explode('.', $field);
if (!isset($queryRelatedModules[$m])) {
$relhandler = vtws_getModuleHandlerFromName($m, $user);
$relmeta = $relhandler->getMeta();
$mn = $relmeta->getTabName();
// normalize module name
$queryRelatedModules[$mn] = $relmeta;
if ($m != $mn) {
$queryColumns[$k] = $mn . '.' . $f;
}
}
}
}
$queryColumns[] = 'id';
// add ID column to follow REST interface behaviour
$queryGenerator->setFields($queryColumns);
// take apart conditionals
$queryConditions = trim($m[2], ' ;');
$moduleRegex = "/[fF][rR][Oo][Mm]\\s+([^\\s;]+)(.*)/";
preg_match($moduleRegex, $q, $m);
$queryConditions = trim($m[2], ' ;');
if (strtolower(substr($queryConditions, 0, 5)) == 'where') {
$queryConditions = substr($queryConditions, 6);
}
$orderbyCond = "/([oO][rR][dD][eE][rR]\\s+[bB][yY]\\s+)+(.*)/";
preg_match($orderbyCond, $queryConditions, $ob);
$obflds = isset($ob[2]) ? $ob[2] : '';
if (stripos($obflds, ' limit ') > 0) {
$obflds = substr($obflds, 0, stripos($obflds, ' limit '));
}
$limitCond = "/([lL][iI][mM][iI][tT]\\s+)+(.*)/";
preg_match($limitCond, $queryConditions, $lm);
$lmoc = isset($lm[2]) ? $lm[2] : '';
if (stripos($lmoc, ' order ') > 0) {
$lmoc = substr($lmoc, 0, stripos($lmoc, ' order '));
}
if (stripos($queryConditions, ' order ') > 0) {
$queryConditions = substr($queryConditions, 0, stripos($queryConditions, ' order '));
}
if (stripos($queryConditions, ' limit ') > 0) {
$queryConditions = substr($queryConditions, 0, stripos($queryConditions, ' limit '));
}
$qcst = strtolower(substr(trim($queryConditions), 0, 5));
if ($qcst == 'order' or $qcst == 'limit') {
$queryConditions = '';
}
// $queryConditions has all the where conditions
// $obflds has the list of order by fields
// $limit is the full correct limit SQL part
// transform REST ids
$relatedCond = "/=\\s*'*\\d+x(\\d+)'*/";
$afterwhere = preg_replace($relatedCond, ' = $1 ', $afterwhere);
// where
if (strlen($queryConditions) > 0) {
$queryGenerator->startGroup();
$qc = trim($queryConditions);
if (substr($qc, 0, 1) == '(') {
$queryGenerator->startGroup();
$qc = substr($qc, 1);
}
$inopRegex = "/\\s+in\\s+\\(/";
$posand = stripos($qc, ' and ');
$posor = stripos($qc, ' or ');
$glue = '';
while ($posand > 0 or $posor > 0 or strlen($qc)) {
$endgroup = false;
preg_match($inopRegex, $qc, $qcop);
$inop = count($qcop) > 0;
$lasttwo = '';
if ($inop) {
$lasttwo = str_replace(' ', '', $qc);
$lasttwo = substr($lasttwo, -2);
}
if ($posand == 0 and $posor == 0) {
if (!$inop and substr($qc, -1) == ')' or $inop and $lasttwo == '))') {
$qc = substr($qc, 0, strlen($qc) - 1);
$endgroup = true;
}
__FQNExtendedQueryAddCondition($queryGenerator, $qc, $glue, $mainModule, $fieldcolumn, $user);
$qc = '';
} elseif ($posand == 0 or $posand > $posor and $posor != 0) {
$qcond = trim(substr($qc, 0, $posor));
if (!$inop and substr($qcond, -1) == ')' or $inop and $lasttwo == '))') {
$qcond = substr($qcond, 0, strlen($qcond) - 1);
$endgroup = true;
}
__FQNExtendedQueryAddCondition($queryGenerator, $qcond, $glue, $mainModule, $fieldcolumn, $user);
$glue = $queryGenerator::$OR;
$qc = trim(substr($qc, $posor + 4));
} else {
$qcond = trim(substr($qc, 0, $posand));
if (!$inop and substr($qcond, -1) == ')' or $inop and $lasttwo == '))') {
$qcond = substr($qcond, 0, strlen($qcond) - 1);
$endgroup = true;
}
__FQNExtendedQueryAddCondition($queryGenerator, $qcond, $glue, $mainModule, $fieldcolumn, $user);
$glue = $queryGenerator::$AND;
$qc = trim(substr($qc, $posand + 5));
}
if ($endgroup) {
$queryGenerator->endGroup();
}
if (substr($qc, 0, 1) == '(') {
$queryGenerator->startGroup($glue);
$glue = '';
$qc = substr($qc, 1);
}
$posand = stripos($qc, ' and ');
$posor = stripos($qc, ' or ');
}
$queryGenerator->endGroup();
}
$query = 'select ';
if ($countSelect) {
$query .= 'count(*) ';
} else {
$query .= $queryGenerator->getSelectClauseColumnSQL() . ' ';
}
$query .= $queryGenerator->getFromClause() . ' ';
$query .= $queryGenerator->getWhereClause() . ' ';
// limit and order
if (!empty($obflds)) {
$obflds = trim($obflds);
if (strtolower(substr($obflds, -3)) == 'asc') {
$dir = ' asc ';
$obflds = trim(substr($obflds, 0, strlen($obflds) - 3));
} elseif (strtolower(substr($obflds, -4)) == 'desc') {
$dir = ' desc ';
$obflds = trim(substr($obflds, 0, strlen($obflds) - 4));
} else {
$dir = '';
}
$obflds = explode(',', $obflds);
foreach ($obflds as $k => $field) {
$obflds[$k] = __FQNExtendedQueryField2Column($field, $mainModule, $fieldcolumn, $user);
}
$query .= ' order by ' . implode(',', $obflds) . $dir . ' ';
}
if (!empty($lmoc)) {
$query .= " limit {$lmoc} ";
}
return array($query, $queryRelatedModules);
}
/**
* Function to search emails for send email
* @param <String> $searchValue
* @return <Array> Result of searched emails
*/
public function searchEmails($searchValue)
{
$emailsResult = array();
$db = PearDatabase::getInstance();
$currentUserModel = Users_Record_Model::getCurrentUserModel();
$emailSupportedModulesList = $this->getEmailRelatedModules();
foreach ($emailSupportedModulesList as $moduleName) {
$searchFields = array();
$moduleModel = Vtiger_Module_Model::getInstance($moduleName);
$emailFieldModels = $moduleModel->getFieldsByType('email');
foreach ($emailFieldModels as $fieldName => $fieldModel) {
if ($fieldModel->isViewable()) {
$searchFields[] = $fieldName;
}
}
$emailFields = $searchFields;
$nameFields = $moduleModel->getNameFields();
foreach ($nameFields as $fieldName) {
$fieldModel = Vtiger_Field_Model::getInstance($fieldName, $moduleModel);
if ($fieldModel->isViewable()) {
$searchFields[] = $fieldName;
}
}
if ($emailFields) {
$moduleInstance = CRMEntity::getInstance($moduleName);
$queryGenerator = new QueryGenerator($moduleName, $currentUserModel);
$listFields = $searchFields;
$listFields[] = 'id';
$queryGenerator->setFields($listFields);
//Opensource fix for showing up deleted records on email search
$queryGenerator->startGroup("");
foreach ($searchFields as $key => $emailField) {
$queryGenerator->addCondition($emailField, trim($searchValue), 'c', 'OR');
}
$queryGenerator->endGroup();
$result = $db->pquery($queryGenerator->getQuery(), array());
$numOfRows = $db->num_rows($result);
for ($i = 0; $i < $numOfRows; $i++) {
$row = $db->query_result_rowdata($result, $i);
foreach ($emailFields as $emailField) {
$emailFieldValue = $row[$emailField];
if ($emailFieldValue) {
$recordLabel = getEntityFieldNameDisplay($moduleName, $nameFields, $row);
if (strpos($emailFieldValue, $searchValue) !== false || strpos($recordLabel, $searchValue) !== false) {
$emailsResult[vtranslate($moduleName, $moduleName)][$row[$moduleInstance->table_index]][] = array('value' => $emailFieldValue, 'label' => $recordLabel . ' <b>(' . $emailFieldValue . ')</b>');
}
}
}
}
}
}
return $emailsResult;
}
/**
* Function to display the Search Results
* @param Vtiger_Request $request
*/
function showSearchResults(Vtiger_Request $request)
{
$db = PearDatabase::getInstance();
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$advFilterList = $request->get('advfilterlist');
//used to show the save modify filter option
$isAdvanceSearch = false;
$matchingRecords = array();
if (is_array($advFilterList) && count($advFilterList) > 0) {
$isAdvanceSearch = true;
$user = Users_Record_Model::getCurrentUserModel();
$queryGenerator = new QueryGenerator($moduleName, $user);
$queryGenerator->setFields(array('id'));
vimport('~~/modules/CustomView/CustomView.php');
$customView = new CustomView($moduleName);
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advFilterList as $groupindex => $groupcolumns) {
$filtercolumns = $groupcolumns['columns'];
if (count($filtercolumns) > 0) {
$queryGenerator->startGroup('');
foreach ($filtercolumns as $index => $filter) {
$nameComponents = explode(':', $filter['columnname']);
if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') {
$name = $queryGenerator->getSQLColumn('id');
} else {
$name = $nameComponents[2];
}
if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) {
$filter['stdfilter'] = $filter['comparator'];
$valueComponents = explode(',', $filter['value']);
if ($filter['comparator'] == 'custom') {
$filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
$filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
}
$dateFilterResolvedList = $customView->resolveDateFilterValue($filter);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['startdate']);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false);
$queryGenerator->addCondition($name, $value, 'BETWEEN');
} else {
$queryGenerator->addCondition($name, $filter['value'], $filter['comparator']);
}
$columncondition = $filter['column_condition'];
if (!empty($columncondition) && array_key_exists($index + 1, $filtercolumns)) {
$queryGenerator->addConditionGlue($columncondition);
}
}
$queryGenerator->endGroup();
$groupConditionGlue = $groupcolumns['condition'];
if (!empty($groupConditionGlue)) {
$queryGenerator->addConditionGlue($groupConditionGlue);
}
}
}
$query = $queryGenerator->getQuery();
//Remove the ordering for now to improve the speed
//$query .= ' ORDER BY createdtime DESC';
$result = $db->pquery($query, array());
$rows = $db->num_rows($result);
for ($i = 0; $i < $rows; ++$i) {
$row = $db->query_result_rowdata($result, $i);
$recordInstance = Vtiger_Record_Model::getInstanceById($row[0]);
$moduleName = $recordInstance->getModuleName();
$matchingRecords[$moduleName][$row[0]] = $recordInstance;
}
$viewer->assign('SEARCH_MODULE', $moduleName);
} else {
$searchKey = $request->get('value');
$searchModule = false;
if ($request->get('searchModule')) {
$searchModule = $request->get('searchModule');
}
$viewer->assign('SEARCH_KEY', $searchKey);
$viewer->assign('SEARCH_MODULE', $searchModule);
$matchingRecords = Vtiger_Record_Model::getSearchResult($searchKey, $searchModule);
}
$matchingRecordsList = array();
if ($matchingRecords[$moduleName]) {
$matchingRecordsList[$moduleName] = $matchingRecords[$moduleName];
}
foreach ($matchingRecords as $module => $recordModelsList) {
$matchingRecordsList[$module] = $recordModelsList;
}
$viewer->assign('MODULE', $moduleName);
$viewer->assign('MATCHING_RECORDS', $matchingRecordsList);
$viewer->assign('IS_ADVANCE_SEARCH', $isAdvanceSearch);
echo $viewer->view('UnifiedSearchResults.tpl', '', true);
}
$queryGenerator->setFields(array('id', 'cf_681', 'accountname'));
$queryGenerator->addCondition('accountname', 'EDFG', 'c');
$queryGenerator->addCondition('employees', '4', 'g', 'or');
$query = $queryGenerator->getQuery();
echo "<b>**INCORRECT:** This next query is incorrect because the parenthesis is not enclosing the OR so it returns not expected results, although it is syntactically correct. The next query is the correct version.</b><br>{$query}<br>";
testquery($query);
$queryGenerator = new QueryGenerator('Project', $current_user);
$queryGenerator->setFields(array('id', 'projectname', 'startdate', 'targetenddate'));
$queryGenerator->addCondition('startdate', '2015-04-16', 'b');
$queryGenerator->addCondition('targetenddate', '2015-06-16', 'a', 'OR');
$query = $queryGenerator->getQuery();
echo "{$query}<br>";
testquery($query);
$queryGenerator = new QueryGenerator($moduleName, $current_user);
$queryGenerator->setFields(array('id', 'cf_681', 'accountname'));
$queryGenerator->startGroup();
// parenthesis to enclose our OR condition between the two groups
$queryGenerator->startGroup();
// start first group
$queryGenerator->addCondition('accountname', 'EDFG', 'c');
$queryGenerator->addCondition('employees', '4', 'g', 'or');
$queryGenerator->endGroup();
// end first group
$queryGenerator->startGroup('or');
// start second group joining with OR glue
$queryGenerator->addCondition('accountname', '3m', 'c');
$queryGenerator->addCondition('employees', '4', 'l', 'or');
$queryGenerator->endGroup();
// end second groupd
$queryGenerator->endGroup();
// end enclosing parenthesis
}
if (!empty($stfields['etime'])) {
$queryFields[] = $stfields['etime'];
}
if (isset($stfields['subject'])) {
$descflds = explode(',', $stfields['subject']);
foreach ($descflds as $dfld) {
$queryFields[] = $dfld;
}
}
$queryGenerator->setFields($queryFields);
if ($record != "") {
$queryGenerator->addCondition('id', $record, 'e', $queryGenerator::$AND);
} else {
$dtflds = getDateFieldsOfModule($modtab[$activitytypeid]);
$queryGenerator->startGroup();
foreach ($dtflds as $field) {
$queryGenerator->addCondition($field, array(0 => $start_date, 1 => $end_date), 'bw', $queryGenerator::$OR);
}
$queryGenerator->startGroup('OR');
$queryGenerator->addCondition($stfields['start'], $start_date, 'b');
$queryGenerator->addCondition($stfields['end'], $end_date, 'a', $queryGenerator::$AND);
$queryGenerator->endGroup();
$queryGenerator->endGroup();
$queryGenerator->addCondition('assigned_user_id', getUserFullName($userid), 'e', $queryGenerator::$AND);
if (count($Event_Status) > 0) {
$evuniq = array_diff(array('Held', 'Not Held', 'Planned'), array_unique($Event_Status));
$encompas_group = false;
foreach ($evuniq as $evstat) {
if (isset($Module_Status_Fields[$evstat])) {
if (!$encompas_group) {
/**
* Function to display the Search Results
* @param Vtiger_Request $request
*/
function showSearchResults(Vtiger_Request $request)
{
$db = PearDatabase::getInstance();
$viewer = $this->getViewer($request);
$moduleName = $request->getModule();
$advFilterList = $request->get('advfilterlist');
//used to show the save modify filter option
$isAdvanceSearch = false;
$matchingRecords = array();
if (is_array($advFilterList) && count($advFilterList) > 0) {
$isAdvanceSearch = true;
$user = Users_Record_Model::getCurrentUserModel();
$queryGenerator = new QueryGenerator($moduleName, $user);
$queryGenerator->setFields(['id']);
vimport('~modules/CustomView/CustomView.php');
$customView = new CustomView($moduleName);
$dateSpecificConditions = $customView->getStdFilterConditions();
foreach ($advFilterList as $groupindex => $groupcolumns) {
$filtercolumns = $groupcolumns['columns'];
if (count($filtercolumns) > 0) {
$queryGenerator->startGroup('');
foreach ($filtercolumns as $index => $filter) {
$nameComponents = explode(':', $filter['columnname']);
if (empty($nameComponents[2]) && $nameComponents[1] == 'crmid' && $nameComponents[0] == 'vtiger_crmentity') {
$name = $queryGenerator->getSQLColumn('id');
} else {
$name = $nameComponents[2];
}
if (($nameComponents[4] == 'D' || $nameComponents[4] == 'DT') && in_array($filter['comparator'], $dateSpecificConditions)) {
$filter['stdfilter'] = $filter['comparator'];
$valueComponents = explode(',', $filter['value']);
if ($filter['comparator'] == 'custom') {
$filter['startdate'] = DateTimeField::convertToDBFormat($valueComponents[0]);
$filter['enddate'] = DateTimeField::convertToDBFormat($valueComponents[1]);
}
$dateFilterResolvedList = $customView->resolveDateFilterValue($filter);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['startdate']);
$value[] = $queryGenerator->fixDateTimeValue($name, $dateFilterResolvedList['enddate'], false);
$queryGenerator->addCondition($name, $value, 'BETWEEN');
} else {
$queryGenerator->addCondition($name, $filter['value'], $filter['comparator']);
}
$columncondition = $filter['column_condition'];
if (!empty($columncondition) && array_key_exists($index + 1, $filtercolumns)) {
$queryGenerator->addConditionGlue($columncondition);
}
}
$queryGenerator->endGroup();
$groupConditionGlue = $groupcolumns['condition'];
if (!empty($groupConditionGlue)) {
$queryGenerator->addConditionGlue($groupConditionGlue);
}
}
}
$query = $queryGenerator->getQuery();
//Remove the ordering for now to improve the speed
//$query .= ' ORDER BY createdtime DESC';
$result = $db->query($query);
while ($row = $db->fetch_array($result)) {
$recordInstance = Vtiger_Record_Model::getInstanceById(current($row));
$moduleName = $recordInstance->getModuleName();
$recordInstance->set('permitted', true);
$matchingRecords[$moduleName][current($row)] = $recordInstance;
}
$viewer->assign('SEARCH_MODULE', $moduleName);
} else {
$searchKey = $request->get('value');
$searchModule = false;
if ($request->get('searchModule')) {
$searchModule = $request->get('searchModule');
}
$viewer->assign('SEARCH_KEY', $searchKey);
$viewer->assign('SEARCH_MODULE', $searchModule);
$matchingRecords = Vtiger_Record_Model::getSearchResult($searchKey, $searchModule, $request->get('limit'));
}
$recordsList = $matchingRecordsList = [];
if ($matchingRecords[$moduleName]) {
$matchingRecordsList[$moduleName] = $matchingRecords[$moduleName];
}
foreach ($matchingRecords as $module => $recordModelsList) {
$matchingRecordsList[$module] = $recordModelsList;
}
if ($request->get('html') == 'true') {
$viewer->assign('MODULE', $moduleName);
$viewer->assign('MATCHING_RECORDS', $matchingRecordsList);
$viewer->assign('IS_ADVANCE_SEARCH', $isAdvanceSearch);
echo $viewer->view('UnifiedSearchResults.tpl', '', true);
} else {
foreach ($matchingRecordsList as $module => $modules) {
foreach ($modules as $recordID => $recordModel) {
$label = decode_html($recordModel->getName());
$label .= ' (' . Vtiger_Functions::getOwnerRecordLabel($recordModel->get('smownerid')) . ')';
if (!$recordModel->get('permitted')) {
$label .= ' <span class="glyphicon glyphicon-warning-sign" aria-hidden="true"></span>';
}
$recordsList[] = ['id' => $recordID, 'module' => $module, 'category' => vtranslate($module, $module), 'label' => $label, 'permitted' => $recordModel->get('permitted')];
}
}
$response = new Vtiger_Response();
$response->setResult($recordsList);
$response->emit();
}
}
