DqlStatement.php

<?php
namespace MyCompany\MyApplication\Domain\Model;

use TYPO3\Flow\Annotations as Flow;
use Doctrine\ORM\Mapping as ORM;
use MyCompany\MyApplication\Utility\DataClassReflection;

/**
 * @Flow\Entity
 */
class DqlStatement {

	/**
	 * @var string
	 * @ORM\Column(type="text")
	 */
	protected $dql;

	/**
	 * @var string
	 * @ORM\Column(type="text")
	 */
	protected $selectStatement;

	/**
	 * @var string
	 * @Flow\Validate(type="NotEmpty")
	 * @ORM\Column(type="text")
	 */
	protected $fromStatement;
	/**
	 * @var string
	 * @ORM\Column(type="text")
	 */
	protected $joinStatement;

	/**
	 * @var string
	 * @ORM\Column(type="text")
	 */
	protected $whereStatement;

	/**
	 * @var string
	 * @ORM\Column(type="text")
	 * @ORM\Column(nullable=true)
	 */
	protected $orderStatement;

	/**
	 * @Flow\Inject
	 * @var \TYPO3\Flow\Reflection\ReflectionService
	 */
	protected $reflectionService;

	/**
	 * @var array
	 * @Flow\Transient
	 */
	static protected $joins = array(
		'normal' => 'JOIN',
		'left' => 'LEFT JOIN',
		'inner' => 'INNER JOIN',
		'outer' => 'LEFT OUTER JOIN'
	);

	/**
	 * @var array
	 * @Flow\Transient
	 */
	static protected $operations = array(
		'less' => '<',
		'lesseq' => '<=',
		'more' => '>',
		'moreeq' => '>=',
		'lessmore' => '<>',
		'eq' => '=',
		'neq' => '!=',
		'like' => 'LIKE'
	);

	/**
	 * @var array
	 * @Flow\Transient
	 */
	static protected $conjunctions = array(
		'and' => 'AND',
		'or' => 'OR',
		'paropen' => '(',
		'parclose' => ')'
	);

	/**
	 * @var array
	 * @Flow\Transient
	 */
	static protected $functions = array(
		'null' => 'IS NULL',
		'notnull' => 'IS NOT NULL',
		'now' => 'CURRENT_TIMESTAMP()'
	);

	/**
	 * @return string
	 */
	public function getDql() {
		return $this->dql;
	}

	/**
	 * @param string $dql
	 * @return void
	 */
	public function setDql($dql) {
		$this->dql = $dql;
	}

	/**
	 * @return string
	 */
	public function getSelectStatement() {
		return $this->selectStatement;
	}

	/**
	 * @param string $selectStatement
	 * @return void
	 */
	public function setSelectStatement($selectStatement) {
		$this->selectStatement = html_entity_decode($selectStatement);
	}

	/**
	 * @return string
	 */
	public function getFromStatement() {
		return $this->fromStatement;
	}

	/**
	 * @param string $fromStatement
	 * @return void
	 */
	public function setFromStatement($fromStatement) {
		$this->fromStatement = html_entity_decode($fromStatement);
	}

	/**
	 * @return string
	 */
	public function getJoinStatement() {
		return $this->joinStatement;
	}

	/**
	 * @param string $joinStatement
	 * @return void
	 */
	public function setJoinStatement($joinStatement) {
		$this->joinStatement = html_entity_decode($joinStatement);
	}

	/**
	 * @return string
	 */
	public function getWhereStatement() {
		return $this->whereStatement;
	}

	/**
	 * @param string $whereStatement
	 * @return void
	 */
	public function setWhereStatement($whereStatement) {
		$this->whereStatement = html_entity_decode($whereStatement);
	}

	/**
	 * @return string
	 */
	public function getOrderStatement() {
		return $this->orderStatement;
	}

	/**
	 * @param string $orderStatement
	 * @return void
	 */
	public function setOrderStatement($orderStatement) {
		$this->orderStatement = html_entity_decode($orderStatement);
	}

	/**
	 * Puts together all the parts into one single DQL statement
	 *
	 * @param array<\MyCompany\MyApplication\Utility\TagsinputTag> $tags
	 * @return void
	 * @throws \TYPO3\Flow\Security\Exception\AccessDeniedException
	 */
	public function generate($tags = array()) {
		$fromStatements = (strlen(trim($this->fromStatement)) > 0) ? explode(',', $this->fromStatement) : array();
		$joinStatements = (strlen(trim($this->joinStatement)) > 0) ? explode(',', $this->joinStatement) : array();
		$selectStatements = (strlen(trim($this->selectStatement)) > 0) ? explode(',', $this->selectStatement) : array();
		$whereStatements = (strlen(trim($this->whereStatement)) > 0) ? explode(',', $this->whereStatement) : array();
		$orderStatements = (strlen(trim($this->orderStatement)) > 0) ? explode(',', $this->orderStatement) : array();
		$tagCounter = 0;
		/** @var \MyCompany\MyApplication\Utility\TagsinputTag $tag */
		foreach($tags as $tag) {
			if($tag->isDataTag()) {
				// SELECT product.attribute FROM MyCompany\MyApplication\Domain\Model\Product product WHERE product.attribute = '...' ORDER BY product.price ASC
				// -> SELECT data1.value AS attribute FROM MyCompany\MyApplication\Domain\Model\Product product JOIN product.data data1 WHERE ( data1.identifier = 'attribute' AND d.value = '...' ) ORDER BY data1.value ASC
				list($sourcePropertyName, $dataIdentifier) = explode('.', $tag->getValue());
				$dataJoinPropertyName = 'data'.$tagCounter++;
				if(in_array($tag->getValue(), array_merge($selectStatements, $whereStatements, $orderStatements))) {
					$joinStatements[] = 'LEFT OUTER JOIN';
					$joinStatements[] = $sourcePropertyName.'.data '.$dataJoinPropertyName.' WITH '.$dataJoinPropertyName.'.identifier = \''.$dataIdentifier.'\'';
				}
				if(in_array($tag->getValue(), $selectStatements)) {
					foreach($selectStatements as $i => $selectStatement) {
						if($tag->getValue() === $selectStatement) {
							$selectStatements[$i] = $dataJoinPropertyName.'.value AS '.$dataIdentifier;
						}
					}
				}
				if(in_array($tag->getValue(), $whereStatements)) {
					foreach($whereStatements as $i => $whereStatement) {
						if($tag->getValue() === $whereStatement) {
							$dataValueField = $dataJoinPropertyName.'.value';
							$whereStatements[$i] = $dataValueField;
						}
					}
				}
				if(in_array($tag->getValue(), $orderStatements)) {
					foreach($orderStatements as $i => $orderStatement) {
						if($tag->getValue().' ASC' === $orderStatement) {
							$orderStatements[$i] = $dataJoinPropertyName.'.value ASC';
						}
						if($tag->getValue().' DESC' === $orderStatement) {
							$orderStatements[$i] = $dataJoinPropertyName.'.value DESC';
						}
					}
				}
			}
			else {
				if(in_array($tag->getValue(), $selectStatements)) {
					foreach($selectStatements as $i => $selectStatement) {
						if($tag->getValue() === $selectStatement) {
							$selectStatements[$i] = $selectStatement.' AS '.$tag->getPropertyValue().$tagCounter++;;
						}
					}
				}
			}
		}
		$fromStatement = (count($fromStatements) > 0) ? implode(',', $fromStatements) : '';
		$joinStatement = (count($joinStatements) > 0) ? implode(',', $joinStatements) : '';
		$selectStatement = (count($selectStatements) > 0) ? implode(',', $selectStatements) : '';
		$whereStatement = (count($whereStatements) > 0) ? implode(',', $whereStatements) : '';
		$orderStatement = (count($orderStatements) > 0) ? implode(',', $orderStatements) : '';
		$this->dql = preg_replace('/[ ]+/', ' ', implode(' ', array(
			$this->generateSelectStatement($selectStatement),
			$this->generateFromStatements($fromStatement, $joinStatement),
			$this->generateWhereStatement($whereStatement),
			$this->generateOrderStatement($orderStatement)
		)));
	}

	/**
	 * @param string $statement
	 * @return string
	 */
	protected function generateSelectStatement($statement = NULL) {
		if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->selectStatement;
		$statement = (strlen($statement) > 0) ? str_replace(',', ', ', $statement) : DataClassReflection::getPropertyNameFromClassName($this->fromStatement);
		return 'SELECT '.$statement;
	}

	/**
	 * @param string $statement
	 * @return string
	 */
	protected function generateFromStatements($statement = NULL, $joinStatement = NULL) {
		if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->fromStatement;
		if(strlen($statement) <= 0) return '';
		$fromStatementParts = array();
		foreach(explode(',', $statement) as $fromClassName) {
			$fromPropertyName = DataClassReflection::getPropertyNameFromClassName($fromClassName);
			$currentJoinChain = array($fromPropertyName);
			if(!is_string($joinStatement) || strlen($joinStatement) <= 0) $joinStatement = $this->joinStatement;
			$joinStatementParts = array();
			if(strlen($joinStatement) > 0) {
				$joinStatements = explode(',', $joinStatement);
				foreach($joinStatements as $i => $joinStatementPart) {
					if(strpos($joinStatementPart, '.') === FALSE) {
						// is a join operator (like 'LEFT JOIN')
						$lookAhead = $joinStatements[$i+1];
						list($source, $target) = explode('.', trim($lookAhead));
						if(in_array($source, $currentJoinChain)) {
							$joinStatementParts[] = $joinStatementPart;
							if(preg_match('/^[\w\d]+ [\w\d]+( WITH .+)?$/i', $target)) {
								// join statement was already prepared
								$joinStatementParts[] = $lookAhead;
							}
							else {
								$joinStatementParts[] = $source.'.'.$target.' '.$target;
								$currentJoinChain[] = $target;
							}
						}
					}
				}
			}
			$fromStatementParts[] = $fromClassName.' '.$fromPropertyName.' '.implode(' ', $joinStatementParts);
		}
		return 'FROM '.implode(', ', $fromStatementParts);
	}

	/**
	 * @param string $statement
	 * @return string
	 */
	protected function generateWhereStatement($statement = NULL) {
		if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->whereStatement;
		if(strlen($statement) <= 0) return '';
		return 'WHERE '.str_replace(',', ' ', $statement);
	}

	/**
	 * @param string $statement
	 * @return string
	 */
	protected function generateOrderStatement($statement = NULL) {
		if(!is_string($statement) || strlen($statement) <= 0) $statement = $this->orderStatement;
		if(strlen($statement) <= 0) return '';
		return 'ORDER BY '.str_replace(',', ', ', $statement);
	}

	/**
	 * @param string $propertyName
	 * @return boolean
	 */
	static public function isProperty($propertyName) {
		return !in_array($propertyName, array_merge(self::getOperations(), self::getConjunctions(), self::getFunctions(), self::getJoins()));
	}

	/**
	 * @return array
	 */
	static public function getJoins() {
		return self::$joins;
	}

	/**
	 * @return array
	 */
	static public function getOperations() {
		return self::$operations;
	}

	/**
	 * @return array
	 */
	static public function getConjunctions() {
		return self::$conjunctions;
	}

	/**
	 * @return array
	 */
	static public function getFunctions() {
		return self::$functions;
	}
}