QueryGenerator::getQueryInvField() - Code Metrics - Inspection of "Added the ability to filter by fields from the adv..." - YetiForceCompany/YetiForceCRM - Measure and Improve Code Quality continuously with Scrutinizer

Passed
Push — developer ( 762b64...a1cff0 )

by Radosław
created 2023-04-04 13:10 UTC
QueryGenerator::getQueryInvField() A

↳ Parent: QueryGenerator
Complexity

Conditions	3
Paths	3
Size

Total Lines	15
Code Lines	9
Duplication

Lines	0
Ratio	0 %
Code Coverage

Tests	3
CRAP Score	3
Importance

Changes
Metric	Value
eloc	9
dl	0
loc	15
ccs	3
cts	3
cp	1
rs	9.9666
c	0
b	0
f	0
cc	3
nc	3
nop	1
crap	3
<?php
/**
 * Query generator file.
 *
 * @package App
 *
 * @copyright YetiForce S.A.
 * @license   YetiForce Public License 5.0 (licenses/LicenseEN.txt or yetiforce.com)
 * @author    Mariusz Krzaczkowski <[email protected]>
 * @author    Radosław Skrzypczak <[email protected]>
 */

namespace App;

/**
 * Query generator class.
 */
class QueryGenerator
{
	const STRING_TYPE = ['string', 'text', 'email', 'reference'];
	const NUMERIC_TYPE = ['integer', 'double', 'currency', 'currencyInventory'];
	const DATE_TYPE = ['date', 'datetime'];
	const EQUALITY_TYPES = ['currency', 'percentage', 'double', 'integer', 'number'];
	const COMMA_TYPES = ['picklist', 'multipicklist', 'owner', 'date', 'datetime', 'time', 'tree', 'sharedOwner', 'sharedOwner'];

	/**
	 * State records to display
	 * 0 - Active
	 * 1 - Trash
	 * 2 - Archived.
	 *
	 * @var int|null
	 */
	private $stateCondition = 0;

	/** @var bool Permissions conditions */
	public $permissions = true;

	/** @var string Module name */
	private $moduleName;

	/** @var \App\Db\Query */
	private $query;

	/** @var \App\Db\Query */
	private $buildedQuery;
	private $fields = [];
	private $referenceFields = [];
	private $ownerFields = [];
	private $customColumns = [];
	private $advFilterList;
	private $conditions;

	/** @var array Advanced conditions */
	private $advancedConditions = [];

	/** @var array Search fields for duplicates. */
	private $searchFieldsForDuplicates = [];

	/** @var array Joins */
	private $joins = [];

	/** @var string[] Tables list */
	private $tablesList = [];
	private $queryFields = [];
	private $order = [];
	private $group = [];
	private $sourceRecord;
	private $concatColumn = [];
	private $relatedFields = [];
	private $relatedQueryFields = [];

	/**
	 * @var bool
	 */
	private $ignoreComma = false;

	/**
	 * @var array Required conditions
	 */
	private $conditionsAnd = [];

	/**
	 * @var array Optional conditions
	 */
	private $conditionsOr = [];

	/**
	 * @var \Vtiger_Module_Model
	 */
	private $moduleModel;

	/**
	 * @var Vtiger_Field_Model[]
	 */
	private $fieldsModel;

	/**
	 * @var Vtiger_Field_Model[]
	 */
	private $relatedFieldsModel;

	/**
	 * @var \CRMEntity
	 */
	private $entityModel;

	/** @var User */
	private $user;

	/** @var int|null Limit */
	private $limit;

	/** @var int|null Offset */
	private $offset;

	/** @var string|null Distinct field */
	private $distinct;

	/**
	 * QueryGenerator construct.
	 *
	 * @param string $moduleName
	 * @param mixed  $userId
	 */
	public function __construct($moduleName, $userId = false)
	{
		$this->moduleName = $moduleName;
		$this->moduleModel = \Vtiger_Module_Model::getInstance($moduleName);
		$this->entityModel = \CRMEntity::getInstance($moduleName);
		$this->user = User::getUserModel($userId ?: User::getCurrentUserId());
	}

	/**
	 * Get module name.
	 *
	 * @return string
	 */
	public function getModule()
	{
		return $this->moduleName;
	}

	/**
	 * Get module model.
	 *
	 * @return \Vtiger_Module_Model
	 */
	public function getModuleModel()
	{
		return $this->moduleModel;
	}

	/**
	 * Get query fields.
	 *
	 * @return string[]
	 */
	public function getFields()
	{
		return $this->fields;
	}

	/**
	 * Get list view query fields.
	 *
	 * @return \Vtiger_Field_Model[]
	 */
	public function getListViewFields(): array
	{
		$headerFields = [];
		foreach ($this->getFields() as $fieldName) {
			if ($model = $this->getModuleField($fieldName)) {
				$headerFields[$fieldName] = $model;
				if ($field = $this->getQueryField($fieldName)->getListViewFields()) {
					$headerFields[$field->getName()] = $field;
					$this->fields[] = $field->getName();
				}
			}
		}
		return $headerFields;
	}

	/**
	 * Sets conditions from ConditionBuilder.
	 *
	 * @param array $conditions
	 *
	 * @return $this
	 */
	public function setConditions(array $conditions)
	{
		$this->conditions = $conditions;

		return $this;
	}

	/**
	 * Set query fields.
	 *
	 * @param string[] $fields
	 *
	 * @return \self
filter:
    dependency_paths: ["lib/*"]
	 */
	public function setFields(array $fields)
	{
		$this->fields = [];
		foreach ($fields as $fieldName) {
			$this->setField($fieldName);
		}

		return $this;

	}

	/**
	 * Set query offset.
	 *
	 * @param int $offset
	 *
	 * @return \self
	 */
	public function setOffset($offset)
	{
		$this->offset = $offset;
		return $this;

	}

	/**
	 * Set query limit.
	 *
	 * @param int $limit
	 *
	 * @return \self
	 */
	public function setLimit($limit)
	{
		$this->limit = $limit;
		return $this;

	}

	/**
	 * Get query limit.
	 */
	public function getLimit()
	{
		return $this->limit;
	}

	/**
	 * Set distinct column.
	 *
	 * @param string $columnName
	 *
	 * @return \self
	 */
	public function setDistinct($columnName)
	{
		$this->distinct = $columnName;
		return $this;

	}

	/**
	 * Get distinct column.
	 *
	 * @return string|null
	 */
	public function getDistinct(): ?string
	{
		return $this->distinct;
	}

	/**
	 * Returns related fields.
	 *
	 * @return array
	 */
	public function getRelatedFields()
	{
		return $this->relatedFields;
	}

	/**
	 * Set query field.
	 *
	 * @param string $fieldName
	 *
	 * @return \self
	 */
	public function setField(string $fieldName): self
	{
		if (false !== strpos($fieldName, ':')) {
			[$relatedFieldName, $relatedModule, $sourceField] = array_pad(explode(':', $fieldName), 3, null);
			$this->addRelatedField([
				'sourceField' => $sourceField,
				'relatedModule' => $relatedModule,
				'relatedField' => $relatedFieldName
			]);
		} else {
			$this->fields[] = $fieldName;
		}

		return $this;

	}

	/**
	 * Clear fields.
	 *
	 * @return self
	 */
	public function clearFields(): self
	{
		$this->fields = ['id'];
		$this->relatedFields = [];
		$this->customColumns = [];
		return $this;
	}

	/**
	 * Load base module list fields.
	 */
	public function loadListFields()
	{
		$listFields = $this->entityModel->list_fields_name;
		$listFields[] = 'id';
		$this->fields = $listFields;
	}

	/**
	 * Set custom column.
	 *
	 * @param string|string[] $columns
	 *
	 * @return \self
	 */
	public function setCustomColumn($columns): self
	{
		if (\is_array($columns)) {
			foreach ($columns as $key => $column) {
				if (is_numeric($key)) {
					$this->customColumns[] = $column;
				} else {
					$this->customColumns[$key] = $column;
				}
			}
		} else {
			$this->customColumns[] = $columns;
		}
		return $this;

	}

	/**
	 * Set concat column.
	 *
	 * @param string $fieldName
	 * @param string $concat
	 *
	 * @return \self
	 */
	public function setConcatColumn(string $fieldName, string $concat)
	{
		$this->concatColumn[$fieldName] = $concat;

		return $this;

	}

	/**
	 * Get CRMEntity Model.
	 *
	 * @return \CRMEntity
	 */
	public function getEntityModel()
	{
		return $this->entityModel;
	}

	/**
	 * Get reference fields.
	 *
	 * @param string $fieldName
	 *
	 * @return array
	 */
	public function getReference($fieldName)
	{
		return $this->referenceFields[$fieldName];
	}

	/**
	 * Add a mandatory condition.
	 *
	 * @param array $condition
	 * @param bool  $groupAnd
	 */
	public function addNativeCondition($condition, $groupAnd = true)
	{
		if ($groupAnd) {
			$this->conditionsAnd[] = $condition;
		} else {
			$this->conditionsOr[] = $condition;
		}
		return $this;
	}

	/**
	 * Returns related fields for section SELECT.
	 *
	 * @return array
	 */
	public function loadRelatedFields()
	{
		$fields = $checkIds = [];
		foreach ($this->relatedFields as $field) {
			$joinTableName = $this->getModuleField($field['sourceField'])->getTableName();
			$moduleTableIndexList = $this->entityModel->tab_name_index;
			$baseTable = $this->entityModel->table_name;
			if ($joinTableName !== $baseTable) {
				$this->addJoin(['INNER JOIN', $joinTableName, "{$baseTable}.{$moduleTableIndexList[$baseTable]} = {$joinTableName}.{$moduleTableIndexList[$joinTableName]}"]);
			}
			$relatedFieldModel = $this->addRelatedJoin($field);
			$fields["{$field['sourceField']}{$field['relatedModule']}{$relatedFieldModel->getName()}"] = "{$relatedFieldModel->getTableName()}{$field['sourceField']}.{$relatedFieldModel->getColumnName()}";
			if (!isset($checkIds[$field['sourceField']][$field['relatedModule']])) {
				$checkIds[$field['sourceField']][$field['relatedModule']] = $field['relatedModule'];
				$fields["{$field['sourceField']}{$field['relatedModule']}id"] = $relatedFieldModel->getTableName() . $field['sourceField'] . '.' . \Vtiger_CRMEntity::getInstance($field['relatedModule'])->tab_name_index[$relatedFieldModel->getTableName()];
			}
		}
		return $fields;
	}

	/**
	 * Set related field.
	 *
	 * @param string[] $field
	 *
	 * @return \self
	 */
	public function addRelatedField($field)
	{
		if (!\in_array($field, $this->relatedFields)) {
			$this->relatedFields[] = $field;
		}
		return $this;

	}

	/**
	 * Set source record.
	 *
	 * @param int $sourceRecord
	 *
	 * @return $this
	 */
	public function setSourceRecord(int $sourceRecord)
	{
		$this->sourceRecord = $sourceRecord;
		return $this;
	}

	/**
	 * Appends a JOIN part to the query.
	 *
	 * @example ['INNER JOIN', 'vtiger_user2role', 'vtiger_user2role.userid = vtiger_users.id']
	 *
	 * @param array $join
	 *
	 * @return $this
	 */
	public function addJoin($join)
	{
		if (!isset($this->joins[$join[1]])) {
			$this->joins[$join[1]] = $join;
		}
		return $this;
	}

	/**
	 * Add table to query.
	 *
	 * @param string $tableName
	 */
	public function addTableToQuery($tableName)
	{
		$this->tablesList[$tableName] = $tableName;
		return $this;
	}

	/**
	 * Set ignore comma.
	 *
	 * @param bool $val
	 */
	public function setIgnoreComma($val)
	{
		$this->ignoreComma = $val;
	}

	/**
	 * Get ignore comma.
	 *
	 * @return bool
	 */
	public function getIgnoreComma()
	{
		return $this->ignoreComma;
	}

	/**
	 * Set order.
	 *
	 * @param string $fieldName
	 * @param string $order     ASC/DESC
	 *
	 * @return \self
	 */
	public function setOrder($fieldName, $order = false)
	{
		$queryField = $this->getQueryField($fieldName);
		$this->order = array_merge($this->order, $queryField->getOrderBy($order));
		return $this;

	}

	/**
	 * Set group.
	 *
	 * @param string $fieldName
	 *
	 * @return \self
	 */
	public function setGroup($fieldName)
	{
		$queryField = $this->getQueryField($fieldName);
		$this->group[] = $queryField->getColumnName();
		return $this;

	}

	/**
	 * Set custom group.
	 *
	 * @param array|string $groups
	 *
	 * @return \self
	 */
	public function setCustomGroup($groups)
	{
		if (\is_array($groups)) {
			foreach ($groups as $key => $group) {
				if (is_numeric($key)) {
					$this->group[] = $group;
				} else {
					$this->group[$key] = $group;
				}
			}
		} else {
			$this->group[] = $groups;
		}
		return $this;

	}

	/**
	 * Function sets the field for which the duplicated values will be searched.
	 *
	 * @param string   $fieldName
	 * @param bool|int $ignoreEmptyValue
	 */
	public function setSearchFieldsForDuplicates($fieldName, $ignoreEmptyValue = true)
	{
		$field = $this->getModuleField($fieldName);
		if ($field && !isset($this->tablesList[$field->getTableName()])) {
			$this->tablesList[$field->getTableName()] = $field->getTableName();
		}
		$this->searchFieldsForDuplicates[$fieldName] = $ignoreEmptyValue;
	}

	/**
	 * Get fields module.
	 *
	 * @return array
	 */
	public function getModuleFields()
	{
		if ($this->fieldsModel) {

			return $this->fieldsModel;
		}
		$moduleFields = $this->moduleModel->getFields();
		foreach ($moduleFields as $fieldName => &$fieldModel) {
			if ($fieldModel->isReferenceField()) {
				$this->referenceFields[$fieldName] = $fieldModel->getReferenceList();
			}
			if ('owner' === $fieldModel->getFieldDataType()) {
				$this->ownerFields[] = $fieldName;
			}
		}
		return $this->fieldsModel = $moduleFields;

	}

	/**
	 * Get fields module.
	 *
	 * @param string $moduleName
	 *
	 * @return \Vtiger_Field_Model[]
	 */
	public function getRelatedModuleFields(string $moduleName)
	{
		if (isset($this->relatedFieldsModel[$moduleName])) {
			return $this->relatedFieldsModel[$moduleName];

		}
		return $this->relatedFieldsModel[$moduleName] = \Vtiger_Module_Model::getInstance($moduleName)->getFields();
	}

	/**
	 * Get field module.
	 *
	 * @param string $fieldName
	 *
	 * @return \Vtiger_Field_Model|bool
	 */
	public function getModuleField(string $fieldName)
	{
		if (!$this->fieldsModel) {

			$this->getModuleFields();
		}
		if (isset($this->fieldsModel[$fieldName])) {
			return $this->fieldsModel[$fieldName];
		}
		return false;
	}

	/**
	 *  Get field in related module.
	 *
	 * @param string $fieldName
	 * @param string $moduleName
	 *
	 * @return \Vtiger_Field_Model|bool
	 */
	public function getRelatedModuleField(string $fieldName, string $moduleName)
	{
		return $this->getRelatedModuleFields($moduleName)[$fieldName] ?? null;
	}

	/**
	 * Get default custom view query.
	 *
	 * @return \App\Db\Query
	 */
	public function getDefaultCustomViewQuery()
	{
		$customView = CustomView::getInstance($this->moduleName, $this->user);
		$viewId = $customView->getViewId();
		if (empty($viewId) || 0 === $viewId) {
			return false;

		}
		return $this->getCustomViewQueryById($viewId);
	}

	/**
	 * Init function for default custom view.
	 *
	 * @param bool $noCache
	 * @param bool $onlyFields
	 *
	 * @return mixed
	 */
	public function initForDefaultCustomView($noCache = false, $onlyFields = false)
	{
		$customView = CustomView::getInstance($this->moduleName, $this->user);
		$viewId = $customView->getViewId($noCache);
		if (empty($viewId) || 0 === $viewId) {
			return false;
		}
		$this->initForCustomViewById($viewId, $onlyFields);
		return $viewId;
	}

	/**
	 * Get custom view query by id.
	 *
	 * @param int|string $viewId
	 *
	 * @return \App\Db\Query
	 */
	public function getCustomViewQueryById($viewId)
	{
		$this->initForCustomViewById($viewId);
		return $this->createQuery();
	}

	/**
	 * Add custom view fields from column.
	 *
	 * @param string[] $cvColumn
	 */
	private function addCustomViewFields(array $cvColumn)
	{
		$fieldName = $cvColumn['field_name'];
		$sourceFieldName = $cvColumn['source_field_name'];
		if (empty($sourceFieldName)) {
			if ('id' !== $fieldName) {
				$this->fields[] = $fieldName;
			}
		} else {
			$this->addRelatedField([
				'sourceField' => $sourceFieldName,
				'relatedModule' => $cvColumn['module_name'],
				'relatedField' => $fieldName,
			]);
		}
	}

	/**
	 * Get advanced conditions.
	 *
	 * @return array
	 */
	public function getAdvancedConditions(): array
	{
		return $this->advancedConditions;
	}

	/**
	 * Set advanced conditions.
	 *
	 * @param array $advancedConditions
	 *
	 * @return $this
	 */
	public function setAdvancedConditions(array $advancedConditions)
	{
		$this->advancedConditions = $advancedConditions;
		return $this;
	}

	/**
	 * Get custom view by id.
	 *
	 * @param mixed $viewId
	 * @param bool  $onlyFields
	 *
	 * @return $this
	 */
	public function initForCustomViewById($viewId, $onlyFields = false)
	{
		$this->fields[] = 'id';
		$customView = CustomView::getInstance($this->moduleName, $this->user);
		foreach ($customView->getColumnsListByCvid($viewId) as $cvColumn) {
			$this->addCustomViewFields($cvColumn);
		}
		foreach (CustomView::getDuplicateFields($viewId) as $fields) {
			$this->setSearchFieldsForDuplicates($fields['fieldname'], (bool) $fields['ignore']);
		}
		if ('Calendar' === $this->moduleName && !\in_array('activitytype', $this->fields)) {
			$this->fields[] = 'activitytype';
		} elseif ('Documents' === $this->moduleName && \in_array('filename', $this->fields)) {
			if (!\in_array('filelocationtype', $this->fields)) {
				$this->fields[] = 'filelocationtype';
			}
			if (!\in_array('filestatus', $this->fields)) {
				$this->fields[] = 'filestatus';
			}
		} elseif ('EmailTemplates' === $this->moduleName && !\in_array('sys_name', $this->fields)) {
			$this->fields[] = 'sys_name';
		}
		if (!$onlyFields) {
			$this->conditions = CustomView::getConditions($viewId);
			if (($customView = \App\CustomView::getCustomViewById($viewId)) && $customView['advanced_conditions']) {
				$this->setAdvancedConditions($customView['advanced_conditions']);
				$this->setDistinct('id');
			}
		}
		return $this;
	}

	/**
	 * Parse conditions to section where in query.
	 *
	 * @param array|null $conditions
	 *
	 * @throws \App\Exceptions\AppException
	 *
	 * @return array
	 */
	private function parseConditions(?array $conditions): array
	{
		if (empty($conditions)) {
			return [];
		}
		$where = [$conditions['condition']];
		foreach ($conditions['rules'] as $rule) {
			if (isset($rule['condition'])) {
				$where[] = $this->parseConditions($rule);
			} else {
				[$fieldName, $moduleName, $sourceFieldName] = array_pad(explode(':', $rule['fieldname']), 3, false);
				if ('INVENTORY' === $sourceFieldName) {
					$condition = $this->getInvCondition($fieldName, $rule['value'], $rule['operator']);
				} elseif (!empty($sourceFieldName)) {
					$condition = $this->getRelatedCondition([
						'relatedModule' => $moduleName,
						'relatedField' => $fieldName,
						'sourceField' => $sourceFieldName,
						'value' => $rule['value'],
						'operator' => $rule['operator'],
					]);
				} else {
					$condition = $this->getCondition($fieldName, $rule['value'], $rule['operator']);
				}
				if ($condition) {
					$where[] = $condition;
				}
			}
		}
		return $where;
	}

	/**
	 * Parsing advanced filters conditions.
	 *
	 * @param mixed $advFilterList
	 *
	 * @return $this
	 */
	public function parseAdvFilter($advFilterList = false)
	{
		if (!$advFilterList) {
			$advFilterList = $this->advFilterList;
		}
		if (!$advFilterList) {
			return $this;
		}
		foreach ($advFilterList as $group => &$filters) {
			$and = ('and' === $group || 1 === (int) $group);
			if (isset($filters['columns'])) {
				$filters = $filters['columns'];
			}
			foreach ($filters as &$filter) {
				if (isset($filter['columnname'])) {
					[$tableName, $columnName, $fieldName] = array_pad(explode(':', $filter['columnname']), 3, false);
					if (empty($fieldName) && 'crmid' === $columnName && 'vtiger_crmentity' === $tableName) {
						$fieldName = $this->getColumnName('id');
					}
					$this->addCondition($fieldName, $filter['value'], $filter['comparator'], $and);
				} else {
					if (!empty($filter['source_field_name'])) {
						$this->addRelatedCondition([
							'sourceField' => $filter['source_field_name'],
							'relatedModule' => $filter['module_name'],
							'relatedField' => $filter['field_name'],
							'value' => $filter['value'],
							'operator' => $filter['comparator'],
							'conditionGroup' => $and,
						]);
					} elseif (0 === strpos($filter['field_name'], 'relationColumn_') && preg_match('/(^relationColumn_)(\d+)$/', $filter['field_name'], $matches)) {
						if (\in_array($matches[2], $this->advancedConditions['relationColumns'] ?? [])) {
							$this->advancedConditions['relationColumnsValues'][$matches[2]] = $filter;
						}
					} else {
						$this->addCondition($filter['field_name'], $filter['value'], $filter['comparator'], $and);
					}
				}
			}
		}
		return $this;
	}

	/**
	 * Create query.
	 *
	 * @param mixed $reBuild
	 *
	 * @return \App\Db\Query
	 */
	public function createQuery($reBuild = false): Db\Query
	{
		if (!$this->buildedQuery || $reBuild) {
			$this->query = new Db\Query();
			$this->loadSelect();
			$this->loadFrom();
			$this->loadWhere();
			$this->loadOrder();
			$this->loadJoin();
			$this->loadGroup();
			if (!empty($this->limit)) {
				$this->query->limit($this->limit);
			}
			if (!empty($this->offset)) {
				$this->query->offset($this->offset);
			}
			if (isset($this->distinct)) {
				$this->query->distinct($this->distinct);
			}
			$this->buildedQuery = $this->query;
		}
		return $this->buildedQuery;
	}

	/**
	 * Sets the SELECT part of the query.
	 */
	public function loadSelect()
	{
		$allFields = array_keys($this->getModuleFields());
		$allFields[] = 'id';
		$this->fields = array_intersect($this->fields, $allFields);
		$columns = [];
		foreach ($this->fields as &$fieldName) {
			if (isset($this->concatColumn[$fieldName])) {
				$columns[$fieldName] = new \yii\db\Expression($this->concatColumn[$fieldName]);
			} else {
				$columns[$fieldName] = $this->getColumnName($fieldName);
			}
		}
		foreach ($this->customColumns as $key => $customColumn) {
			if (is_numeric($key)) {
				$columns[] = $customColumn;
			} else {
				$columns[$key] = $customColumn;
			}
		}
		$this->query->select(array_merge($columns, $this->loadRelatedFields()));
	}

	/**
	 * Get column name by field name.
	 *
	 * @param string $fieldName
	 *
	 * @return string
	 */
	public function getColumnName($fieldName)
	{
		if ('id' === $fieldName) {
			$baseTable = $this->entityModel->table_name;
			return $baseTable . '.' . $this->entityModel->tab_name_index[$baseTable];
		}
		$field = $this->getModuleField($fieldName);
		return $field->getTableName() . '.' . $field->getColumnName();
	}

	/**
	 * Sets the FROM part of the query.
	 */
	public function loadFrom()
	{
		$this->query->from($this->entityModel->table_name);
	}

	/**
	 * Sets the JOINs part of the query.
	 */
	public function loadJoin()
	{
		$tableJoin = [];
		$moduleTableIndexList = $this->entityModel->tab_name_index;
		$baseTable = $this->entityModel->table_name;
		$baseTableIndex = $moduleTableIndexList[$baseTable];
		foreach ($this->fields as $fieldName) {
			if ('id' === $fieldName) {
				continue;
			}
			$field = $this->getModuleField($fieldName);
			if ('reference' === $field->getFieldDataType()) {
				$tableJoin[$field->getTableName()] = 'INNER JOIN';
				foreach ($this->referenceFields[$fieldName] as $moduleName) {
					if ('Users' === $moduleName && 'Users' !== $this->moduleName) {
						$this->addJoin(['LEFT JOIN', 'vtiger_users vtiger_users' . $fieldName, "{$field->getTableName()}.{$field->getColumnName()} = vtiger_users{$fieldName}.id"]);
						$this->addJoin(['LEFT JOIN', 'vtiger_groups vtiger_groups' . $fieldName, "{$field->getTableName()}.{$field->getColumnName()} = vtiger_groups{$fieldName}.groupid"]);
					}
				}
			}
			if (!isset($this->tablesList[$field->getTableName()])) {
				$this->tablesList[$field->getTableName()] = $field->getTableName();
				$tableJoin[$field->getTableName()] = $this->entityModel->getJoinClause($field->getTableName());
			}
		}
		foreach ($this->getEntityDefaultTableList() as $table) {
			if (!isset($this->tablesList[$table])) {
				$this->tablesList[$table] = $table;
			}
			$tableJoin[$table] = 'INNER JOIN';
		}
		if ($this->ownerFields) {

			//there are more than one field pointing to the users table, the real one is the one called assigned_user_id if there is one, otherwise pick the first
			if (\in_array('assigned_user_id', $this->ownerFields)) {
				$ownerField = 'assigned_user_id';
			} else {
				$ownerField = $this->ownerFields[0];
			}
		}
		foreach ($this->getEntityDefaultTableList() as $tableName) {
			$this->query->join($tableJoin[$tableName], $tableName, "$baseTable.$baseTableIndex = $tableName.{$moduleTableIndexList[$tableName]}");
			unset($this->tablesList[$tableName]);
		}
		unset($this->tablesList[$baseTable]);
		foreach ($this->tablesList as $tableName) {
			$joinType = $tableJoin[$tableName] ?? $this->entityModel->getJoinClause($tableName);
			if ('vtiger_users' === $tableName) {
				$field = $this->getModuleField($ownerField);

				$this->addJoin([$joinType, $tableName, "{$field->getTableName()}.{$field->getColumnName()} = $tableName.id"]);
			} elseif ('vtiger_groups' == $tableName) {
				$field = $this->getModuleField($ownerField);
				$this->addJoin([$joinType, $tableName, "{$field->getTableName()}.{$field->getColumnName()} = $tableName.groupid"]);
			} elseif (isset($moduleTableIndexList[$tableName])) {
				$this->addJoin([$joinType, $tableName, "$baseTable.$baseTableIndex = $tableName.$moduleTableIndexList[$tableName]"]);
			}
		}
		if ($this->searchFieldsForDuplicates) {

			$duplicateCheckClause = [];
			$queryGenerator = new self($this->moduleName, $this->user->getId());
			$queryGenerator->permissions = $this->permissions;
			$queryGenerator->setFields(array_keys($this->searchFieldsForDuplicates));
			foreach ($this->searchFieldsForDuplicates as $fieldName => $ignoreEmptyValue) {
				if ($ignoreEmptyValue) {
					$queryGenerator->addCondition($fieldName, '', 'ny');
				}
				$queryGenerator->setGroup($fieldName);
				$fieldModel = $this->getModuleField($fieldName);
				$duplicateCheckClause[] = $fieldModel->getTableName() . '.' . $fieldModel->getColumnName() . ' = duplicates.' . $fieldModel->getFieldName();
				$duplicateCheckClause[] = $fieldModel->getTableName() . '.' . $fieldModel->getColumnName() . ' = duplicates.' . /** @scrutinizer ignore-deprecated */ $fieldModel->getFieldName();
			}
			$subQuery = $queryGenerator->createQuery();
			$subQuery->andHaving((new \yii\db\Expression('COUNT(1) > 1')));
			$this->joins['duplicates'] = ['INNER JOIN', ['duplicates' => $subQuery], implode(' AND ', $duplicateCheckClause)];
		}
		uksort($this->joins, fn ($a, $b) => (int) (!isset($moduleTableIndexList[$a]) && isset($moduleTableIndexList[$b])));
		foreach ($this->joins as $join) {
			$on = $join[2] ?? '';
			$params = $join[3] ?? [];
			$this->query->join($join[0], $join[1], $on, $params);
		}
	}

	/**
	 * Get entity default table list.
	 *
	 * @return type
filter:
    dependency_paths: ["lib/*"]
	 */
	public function getEntityDefaultTableList()
	{
		if (isset($this->entityModel->tab_name_index['vtiger_crmentity'])) {
			return ['vtiger_crmentity'];

		}
		return [];

	}

	/**
	 * Sets the WHERE part of the query.
	 */
	public function loadWhere()
	{
		if (null !== $this->stateCondition) {
			$this->query->andWhere($this->getStateCondition());
		}
		if ($this->advancedConditions) {

			$this->loadAdvancedConditions();
		}
		$this->query->andWhere(['and', array_merge(['and'], $this->conditionsAnd), array_merge(['or'], $this->conditionsOr)]);
		$this->query->andWhere($this->parseConditions($this->conditions));
		if ($this->permissions) {
			if (\App\Config::security('CACHING_PERMISSION_TO_RECORD') && 'Users' !== $this->moduleName) {
				$userId = $this->user->getId();
				$this->query->andWhere(['like', 'vtiger_crmentity.users', ",$userId,"]);
			} else {
				PrivilegeQuery::getConditions($this->query, $this->moduleName, $this->user, $this->sourceRecord);
				PrivilegeQuery::getConditions($this->query, $this->moduleName, /** @scrutinizer ignore-type */ $this->user, $this->sourceRecord);
			}
		}
	}

	/**
	 * Load advanced conditions to section where in query.
	 *
	 * @return void
	 */
	private function loadAdvancedConditions(): void
	{
		if (!empty($this->advancedConditions['relationId']) && ($relationModel = \Vtiger_Relation_Model::getInstanceById($this->advancedConditions['relationId']))) {
			$typeRelationModel = $relationModel->getTypeRelationModel();
			if (!method_exists($typeRelationModel, 'loadAdvancedConditionsByRelationId')) {
				$className = \get_class($typeRelationModel);
				Log::error("The relationship relationId: {$this->advancedConditions['relationId']} does not support advanced conditions | No function in the class: $className | Module: " . $this->getModule());
				throw new \App\Exceptions\AppException("ERR_FUNCTION_NOT_FOUND_IN_CLASS||loadAdvancedConditionsByRelationId|$className|" . $this->getModule());
			}
			$typeRelationModel->loadAdvancedConditionsByRelationId($this);
		}
		if (!empty($this->advancedConditions['relationColumnsValues'])) {
			foreach ($this->advancedConditions['relationColumnsValues'] as $relationId => $value) {
				if ($relationModel = \Vtiger_Relation_Model::getInstanceById($relationId)) {
					$typeRelationModel = $relationModel->getTypeRelationModel();
					if (!method_exists($typeRelationModel, 'loadAdvancedConditionsByColumns')) {
						$className = \get_class($typeRelationModel);
						Log::error("The relationship relationId: {$relationId} does not support advanced conditions | No function in the class: $className | Module: " . $this->getModule());
						throw new \App\Exceptions\AppException("ERR_FUNCTION_NOT_FOUND_IN_CLASS|loadAdvancedConditionsByColumns|$className|" . $this->getModule());
					}
					$typeRelationModel->loadAdvancedConditionsByColumns($this, $value);
				}
			}
		}
	}

	/**
	 * Get records state.
	 *
	 * @return string
	 */
	public function getState(): string
	{
		if (null === $this->stateCondition) {
			return 'All';
		}
		switch ($this->stateCondition) {
			default:
			case 0:
				$stateCondition = 'Active';
				break;
			case 1:
				$stateCondition = 'Trash';
				break;
			case 2:
				$stateCondition = 'Archived';
				break;
		}
		return $stateCondition;
	}

	/**
	 * Get conditions for records state.
	 *
	 * @return array|string
	 */
	private function getStateCondition()
	{
		$condition = ['vtiger_crmentity.deleted' => $this->stateCondition];
		switch ($this->moduleName) {
			case 'Leads':
				$condition += ['vtiger_leaddetails.converted' => 0];
				break;
			case 'Users':
				$condition = [];
				break;
			default:
				break;
		}
		return $condition;
	}

	/**
	 * Set state condition.
	 *
	 * @param string $state
	 *
	 * @return $this
	 */
	public function setStateCondition($state)
	{
		switch ($state) {
			default:
			case 'Active':
				$this->stateCondition = 0;
				break;
			case 'Trash':
				$this->stateCondition = 1;
				break;
			case 'Archived':
				$this->stateCondition = 2;
				break;
			case 'All':
				$this->stateCondition = null;
				break;
		}
		return $this;
	}

	/**
	 * Returns condition for field in this module.
	 *
	 * @param string $fieldName
	 * @param mixed  $value
	 * @param string $operator
	 * @param bool   $userFormat
	 *
	 * @throws \App\Exceptions\AppException
	 *
	 * @return array|bool
	 */
	private function getCondition(string $fieldName, $value, string $operator, bool $userFormat = false)
	{
		$queryField = $this->getQueryField($fieldName);
		if ($userFormat && $queryField->getField()) {
			$value = $queryField->getField()->getUITypeModel()->getDbConditionBuilderValue($value, $operator);
		}
		$queryField->setValue($value);
		$queryField->setOperator($operator);
		$condition = $queryField->getCondition();
		if ($condition && ($field = $this->getModuleField($fieldName)) && !isset($this->tablesList[$field->getTableName()])) {
			$this->tablesList[$field->getTableName()] = $field->getTableName();
		}
		return $condition;
	}

	/**
	 * Gets conditions for inventory field.
	 *
	 * @param string $fieldName
	 * @param mixed  $value
	 * @param string $operator
	 * @param bool   $userFormat
	 *
	 * @return array
	 */
	private function getInvCondition(string $fieldName, $value, string $operator, bool $userFormat = false): array
	{
		$parseData = [];
		if ($this->getModuleModel()->isInventory()) {
			$queryField = $this->getQueryInvField($fieldName);
			if ($userFormat) {
				$value = $queryField->getDbConditionBuilderValue($value, $operator);
    $value = $queryField->getDbConditionBuilderValue($value, $operator);
			}
			$queryField->setValue($value);
			$queryField->setOperator($operator);
			$parseData = $queryField->getCondition();
			$invTableName = $this->getModuleModel()->getInventoryModel()->getDataTableName();
			$tableName = $this->getModuleModel()->getBaseTableName();
			$tableIndex = $this->getModuleModel()->getBaseTableIndex();
			$this->addJoin(['LEFT JOIN', $invTableName, "{$tableName}.{$tableIndex} = {$invTableName}.crmid"]);
			if (null === $this->distinct) {
				$this->setDistinct('id');
			}
		}

		return $parseData;

	}

	/**
	 * Returns condition for field in related module.
	 *
	 * @param array $condition
	 *
	 * @throws \App\Exceptions\AppException
	 *
	 * @return array|bool
	 */
	private function getRelatedCondition(array $condition)
	{
		$field = $this->addRelatedJoin($condition);
		if (!$field) {
			Log::error('Not found source field', __METHOD__);
			return false;
		}
		$queryField = $this->getQueryRelatedField($condition, $field);
		$queryField->setValue($condition['value']);
		$queryField->setOperator($condition['operator']);
		return $queryField->getCondition();
	}

	/**
	 * Set condition.
	 *
	 * @param string $fieldName
	 * @param mixed  $value
	 * @param string $operator
	 * @param mixed  $groupAnd
	 * @param bool   $userFormat
	 *
	 * @see Condition::ADVANCED_FILTER_OPTIONS
	 * @see Condition::DATE_OPERATORS
	 *
	 * @return $this
	 */
	public function addCondition($fieldName, $value, $operator, $groupAnd = true, $userFormat = false)
	{
		$condition = $this->getCondition($fieldName, $value, $operator, $userFormat);
		if ($condition) {
			if ($groupAnd) {
				$this->conditionsAnd[] = $condition;
			} else {
				$this->conditionsOr[] = $condition;
			}
		} else {
			Log::error('Wrong condition');
		}
		return $this;
	}

	/**
	 * Get query field instance.
	 *
	 * @param string $fieldName
	 *
	 * @throws \App\Exceptions\AppException
	 *
	 * @return \App\Conditions\QueryFields\BaseField
	 */
	public function getQueryField($fieldName)
	{
		if (isset($this->queryFields[$fieldName])) {
			return $this->queryFields[$fieldName];
		}
		if ('id' === $fieldName) {
			$queryField = new Conditions\QueryFields\IdField($this, '');
			$queryField = new Conditions\QueryFields\IdField($this, /** @scrutinizer ignore-type */ '');
			return $this->queryFields[$fieldName] = $queryField;
		}
		$field = $this->getModuleField($fieldName);
		if (empty($field)) {
			Log::error("Not found field model | Field name: '$fieldName' in module" . $this->getModule());
			throw new \App\Exceptions\AppException("ERR_NOT_FOUND_FIELD_MODEL|$fieldName|" . $this->getModule());
		}
		$className = '\App\Conditions\QueryFields\\' . ucfirst($field->getFieldDataType()) . 'Field';
		if (!class_exists($className)) {
			Log::error('Not found query field condition | FieldDataType: ' . ucfirst($field->getFieldDataType()));
			throw new \App\Exceptions\AppException('ERR_NOT_FOUND_QUERY_FIELD_CONDITION|' . $fieldName);
		}
		$queryField = new $className($this, $field);
		return $this->queryFields[$fieldName] = $queryField;
	}

	/**
	 * Gets query field instance for inventory field.
	 *
	 * @param string $fieldName
	 *
	 * @return Conditions\QueryFields\Inventory\BaseField
	 */
	public function getQueryInvField(string $fieldName): Conditions\QueryFields\Inventory\BaseField
	{
		$field = $this->getModuleModel()->getInventoryModel()->getField($fieldName);
		if (empty($field)) {
			Log::error("Not found inv field model | Field name: '{$fieldName}' in module" . $this->getModule());
			throw new \App\Exceptions\AppException("ERR_NOT_FOUND_FIELD_MODEL|$fieldName|" . $this->getModule());
		}

		$className = '\App\Conditions\QueryFields\Inventory\\' . ucfirst($field->getType()) . 'Field';
		if (!class_exists($className)) {
			Log::error('Not found query inv field condition | FieldDataType: ' . ucfirst($field->getType()));
			throw new \App\Exceptions\AppException('ERR_NOT_FOUND_QUERY_INV_FIELD_CONDITION|' . $fieldName);
		}

		return new $className($this, $field);
	}

	/**
	 * Set condition on reference module fields.
	 *
	 * @param array $condition
	 */
	public function addRelatedCondition($condition)
	{
		$queryCondition = $this->getRelatedCondition($condition);
		if ($queryCondition) {
			if ($condition['conditionGroup']) {
				$this->conditionsAnd[] = $queryCondition;
			} else {
				$this->conditionsOr[] = $queryCondition;
			}
		} else {
			Log::error('Wrong condition');
		}
	}

	/**
	 * Set related field join.
	 *
	 * @param string[] $fieldDetail
	 *
	 * @return bool|\Vtiger_Field_Model
	 */
	public function addRelatedJoin($fieldDetail)
	{
		$relatedFieldModel = $this->getRelatedModuleField($fieldDetail['relatedField'], $fieldDetail['relatedModule']);
		if (!$relatedFieldModel || !$relatedFieldModel->isActiveField()) {

			Log::warning("Field in related module is inactive or does not exist. Related module: {$fieldDetail['relatedModule']} | Related field: {$fieldDetail['relatedField']}");
			return false;
		}
		$tableName = $relatedFieldModel->getTableName();
		$sourceFieldModel = $this->getModuleField($fieldDetail['sourceField']);
		$relatedTableName = $tableName . $fieldDetail['sourceField'];
		$relatedTableIndex = $relatedFieldModel->getModule()->getEntityInstance()->tab_name_index[$tableName];
		$this->addJoin(['LEFT JOIN', "$tableName $relatedTableName", "{$sourceFieldModel->getTableName()}.{$sourceFieldModel->getColumnName()} = $relatedTableName.$relatedTableIndex"]);
		return $relatedFieldModel;
	}

	/**
	 * Get query related field instance.
	 *
	 * @param array|string        $relatedInfo
	 * @param \Vtiger_Field_Model $field
	 *
	 * @throws \App\Exceptions\AppException
	 *
	 * @return \App\Conditions\QueryFields\BaseField
	 */
	public function getQueryRelatedField($relatedInfo, ?\Vtiger_Field_Model $field = null)
	{
		if (!\is_array($relatedInfo)) {
			[$fieldName, $relatedModule, $sourceFieldName] = array_pad(explode(':', $relatedInfo), 3, false);
			$relatedInfo = [
				'sourceField' => $sourceFieldName,
				'relatedModule' => $relatedModule,
				'relatedField' => $fieldName,
			];
		}
		$relatedModule = $relatedInfo['relatedModule'];
		$fieldName = $relatedInfo['relatedField'];

		if (isset($this->relatedQueryFields[$relatedModule][$fieldName])) {
			$queryField = clone $this->relatedQueryFields[$relatedModule][$fieldName];
			$queryField->setRelated($relatedInfo);
			return $queryField;
		}
		if (null === $field) {
			$field = $this->getRelatedModuleField($fieldName, $relatedModule);
		}
		$className = '\App\Conditions\QueryFields\\' . ucfirst($field->getFieldDataType()) . 'Field';
		if (!class_exists($className)) {
			Log::error('Not found query field condition');
			throw new \App\Exceptions\AppException('ERR_NOT_FOUND_QUERY_FIELD_CONDITION');
		}
		$queryField = new $className($this, $field);
		$queryField->setRelated($relatedInfo);
		return $this->relatedQueryFields[$relatedModule][$field->getName()] = $queryField;
	}

	/**
	 * Set order for related module.
	 *
	 * @param string[] $orderDetail
	 */
	public function setRelatedOrder(array $orderDetail)
	{
		$field = $this->addRelatedJoin($orderDetail);
		if (!$field) {
			Log::error('Not found source field');
		}
		$queryField = $this->getQueryRelatedField($orderDetail, $field);
		$this->order = array_merge($this->order, $queryField->getOrderBy($orderDetail['relatedSortOrder']));
	}

	/**
	 * Sets the ORDER BY part of the query.
	 */
	public function loadOrder()
	{
		if ($this->order) {

			$this->query->orderBy($this->order);
		}
	}

	/**
	 * Sets the GROUP BY part of the query.
	 */
	public function loadGroup()
	{
		if ($this->group) {

			$this->query->groupBy(array_unique($this->group));
		}
	}

	/**
	 * Parse base search condition to db condition.
	 *
	 * @param array $searchParams Example: [[["firstname","a","Tom"]]]
	 *
	 * @return array
	 */
	public function parseBaseSearchParamsToCondition($searchParams)
	{
		if (empty($searchParams)) {
			return [];
		}
		$advFilterConditionFormat = [];
		$glueOrder = ['and', 'or'];
		$groupIterator = 0;
		foreach ($searchParams as $groupInfo) {
			if (!empty($groupInfo)) {
				$groupColumnsInfo = [];
				foreach ($groupInfo as $fieldSearchInfo) {
					if ($fieldSearchInfo) {
						[$fieldNameInfo, $operator, $fieldValue] = array_pad($fieldSearchInfo, 3, false);
						$fieldValue = Purifier::decodeHtml($fieldValue);
						[$fieldName, $moduleName, $sourceFieldName] = array_pad(explode(':', $fieldNameInfo), 3, false);
						if (!empty($sourceFieldName)) {
							$field = $this->getRelatedModuleField($fieldName, $moduleName);
						} else {
							$field = $this->getModuleField($fieldName);
						}
						if ($field && ('date_start' === $fieldName || 'due_date' === $fieldName || 'datetime' === $field->getFieldDataType())) {
							$dateValues = explode(',', $fieldValue);
							//Indicate whether it is fist date in the between condition
							$isFirstDate = true;
							foreach ($dateValues as $key => $dateValue) {
								$dateTimeCompoenents = explode(' ', $dateValue);
								if (empty($dateTimeCompoenents[1])) {
									if ($isFirstDate) {
										$dateTimeCompoenents[1] = '00:00:00';
									} else {
										$dateTimeCompoenents[1] = '23:59:59';
									}
								}
								$dateValue = implode(' ', $dateTimeCompoenents);
								$dateValues[$key] = $dateValue;
								$isFirstDate = false;
							}
							$fieldValue = implode(',', $dateValues);
						}
						$groupColumnsInfo[] = ['field_name' => $fieldName, 'module_name' => $moduleName, 'source_field_name' => $sourceFieldName, 'comparator' => $operator, 'value' => $fieldValue];
					}
				}
				$advFilterConditionFormat[$glueOrder[$groupIterator]] = $groupColumnsInfo;
			}
			++$groupIterator;
		}
		return $advFilterConditionFormat;
	}

	/**
	 * Parse search condition to standard condition.
	 *
	 * @param array $searchParams
	 *
	 * @return array
	 */
	public function parseSearchParams(array $searchParams): array
	{
		$glueOrder = ['AND', 'OR'];
		$searchParamsConditions = [];
		foreach ($searchParams as $key => $conditions) {
			if (empty($conditions)) {
				continue;
			}
			$searchParamsConditions['condition'] = $glueOrder[$key];
			$searchParamsConditions['rules'] = [];
			foreach ($conditions as $condition) {
				[$fieldName, , $sourceFieldName] = array_pad(explode(':', $condition[0]), 3, false);
				if (!$sourceFieldName) {
					$condition[0] = "{$fieldName}:{$this->getModule()}";
				}
				$searchParamsConditions['rules'][] = ['fieldname' => $condition[0], 'operator' => $condition[1], 'value' => $condition[2]];
			}
		}
		return $searchParamsConditions;
	}
}

YetiForceCompany / YetiForceCRM

Push — developer ( 762b64...a1cff0 )

QueryGenerator::getQueryInvField() A

Complexity

Size

Duplication

Code Coverage

Importance

Duplication Side-by-Side

Filter issues like
