/**
* Retourne une requete SQL.
*
* @param string $startEntity nom de l'entite de depart
* @param string $macro chemin en cas de jointure, mais pas encadre par des '%'
* Exple: 'FlyType().Name'
* les () expriment une relation 1..* ou *..*
* @param string $operator FilterRule::OPERATOR_IN, ...:
* cf ds SearchTools::getToSqlOperator() tous les operateurs disponibles
* @param string $value valeur du champs
*
* @return string
*/
static function getSQLfromMacro($startEntity, $macro, $operator, $value)
{
$tableArray = array();
// contiendra les tables pour la req SQL
$join = array();
// contiendra les clauses de jointure pour la req SQL
$parts = explode('.', $macro);
$criteria = array_pop($parts);
require_once MODELS_DIR . '/' . $startEntity . '.php';
$leftTable = call_user_func(array($startEntity, 'getTableName'));
$tableArray[] = $leftTable . ' T0';
$currentEntity = $startEntity;
foreach ($parts as $key => $attributeName) {
$pos = strpos($attributeName, "()");
require_once MODELS_DIR . '/' . $currentEntity . '.php';
$inherits = explode('@', $attributeName);
// strpos($attributeName, "@")
if ($pos === false) {
// attribut de type FK
$attributeName = $inherits[0];
$rightTable = Registry::getPropertyTableName($currentEntity, $attributeName);
$tableArray[] = $rightTable . ' T' . strval($key + 1);
if (count($inherits) == 1) {
$EntitiesArray = call_user_func(array($currentEntity, 'getProperties'));
$currentEntity = $EntitiesArray[$attributeName];
} else {
$currentEntity = $inherits[1];
}
$join[] = ' T' . strval($key + 1) . '._Id = T' . strval($key) . '._' . $attributeName;
} else {
// attribut de type collection
$attributeName = $inherits[0];
$attributeName = substr($attributeName, 0, strlen($attributeName) - 2);
// suppression des ()
$links = call_user_func(array($currentEntity, 'getLinks'));
$attributeForJoin = $links[$attributeName]['field'];
if (count($inherits) == 1) {
$currentEntity = $links[$attributeName]['linkClass'];
} else {
$currentEntity = $inherits[1];
}
require_once MODELS_DIR . '/' . $currentEntity . '.php';
$rightTable = call_user_func(array($currentEntity, 'getTableName'));
$tableArray[] = $rightTable . ' T' . strval($key + 1);
if (!isset($links[$attributeName]['linkTable'])) {
// relation 1..*
$join[] = ' T' . strval($key) . '._Id = T' . strval($key + 1) . '._' . $attributeForJoin;
} else {
// relation *..*: intervention en plus de la table de liens!!
$tableArray[] = $links[$attributeName]['linkTable'] . ' T' . strval($key + 100);
// table de liens
$join[] = ' T' . strval($key) . '._Id = T' . strval($key + 100) . '._' . $links[$attributeName]['field'];
$join[] = ' T' . strval($key + 1) . '._Id = T' . strval($key + 100) . '._' . $links[$attributeName]['linkField'];
}
}
}
$sql = 'SELECT DISTINCT T0._Id FROM ' . implode(',', $tableArray) . ' WHERE ' . implode(' AND ', $join) . ' AND T' . strval($key + 1) . '._' . $criteria;
// Attention: si $value est un array, l'Operator doit etre In ou NotIn
if (is_array($value)) {
$value = "('" . implode("','", $value) . "')";
} else {
$value = "'" . str_replace("*", "%", $value) . "'";
}
$sql .= $operator . $value;
if (defined('DATABASE_ID') && !Object::isPublicEntity($startEntity)) {
$sql .= ' AND (T0._DBId IS NULL OR T0._DBId = ' . DATABASE_ID . ')';
}
return $sql;
}
