/**
* Exprime les conditions en SQL
* @param $pConditions les conditions à mettre dans le SQL
* @param boolean $pExplainKind si l'on souhaite expliquer le and / or
* @param array $pFields tableau des champs sur lesquels travailler
* $pFields[nomPropriete][0] == nom champ
* $pFields[nomPropriete][1] == type champ
* $pFields[nomPropriete][2] == table
* @param CopixDBConnection $pConnection La connection qui corresponds à l'emplacement ou l'on souhaite lancer la requête
* @return string SQL
*/
private function _explainSQLCondition($pConditions, $pExplainKind, $pFields, $pConnection = null)
{
$r = ' ';
$fieldsForQueryParams = array();
//direct conditions for the group
$first = true;
foreach ($pConditions->conditions as $conditionDescription) {
//Si c'est une valeur sous la forme d'un tableau mais qu'aucune valeur
//n'est indiquée, on passe à la suivante.
if (array_key_exists('value', $conditionDescription) && is_array($conditionDescription['value']) && count($conditionDescription['value']) == 0) {
continue;
}
//Si c'est une forme SQL et que la chaine est vide, on passe à la condition suivante.
if (array_key_exists('sql', $conditionDescription) && strlen($conditionDescription['sql']) === 0) {
continue;
}
//Si ce n'est pas le premier passage, il faut ajouter le mot clef relatif à la condition
if (!$first) {
$r .= ' ' . $conditionDescription['kind'] . ' ';
}
//Nous ne sommes plus dans le premier passage.
$first = false;
if (isset($conditionDescription['sql'])) {
//C'est une condition SQL rajoutée à la main.
//on remplace les noms de paramètres pour s'assurer de l'unicité.
//On parcours dans le sens inverse de l'ordre alpha pour éviter de remplacer
//des portions de nom de variable (ex :a avant :ab)
krsort($conditionDescription['params']);
foreach ($conditionDescription['params'] as $paramName => $paramValue) {
$conditionDescription['sql'] = str_replace($paramName, $newParamName = $paramName . self::$_countParam, $conditionDescription['sql']);
self::$_countParam++;
//ajout du paramètre dans le tableau des paramètres
$fieldsForQueryParams[$newParamName] = $paramValue;
}
//Ajout de la chaine SQL traitée.
$r .= ' ' . $conditionDescription['sql'] . ' ';
} else {
//C'est une condition gérée par addCondition.
$prefix = $pFields[$conditionDescription['field_id']][2] . '.' . $pFields[$conditionDescription['field_id']][0];
$prefixNoCondition = $prefix;
$prefix .= ' ' . $conditionDescription['condition'] . ' ';
if (!is_array($conditionDescription['value'])) {
$variableName = ':' . $this->_compressVariable($pFields[$conditionDescription['field_id']][0] . '_' . $pFields[$conditionDescription['field_id']][2] . '_' . self::$_countParam);
if ($conditionDescription['value'] === null && $conditionDescription['condition'] == '=') {
$r .= $prefixNoCondition . ' IS NULL';
} elseif ($conditionDescription['value'] === null && $conditionDescription['condition'] == '<>') {
$r .= $prefixNoCondition . ' IS NOT NULL';
} else {
$fieldsForQueryParams[$variableName] = $this->_variableValue($pFields[$conditionDescription['field_id']][1], $conditionDescription['value'], $pConnection ? $pConnection->getProfile()->getDriverName() : null);
if ($pConnection !== null && $pConnection->getProfile()->getDriverName() == 'oci' && in_array($pFields[$conditionDescription['field_id']][1], array('datetime', 'date', 'time'))) {
if ($pFields[$conditionDescription['field_id']][1] == 'datetime') {
$r .= 'to_char(' . $prefixNoCondition . ', \'YYYYMMDDHH24MISS\') ' . $conditionDescription['condition'] . ' ' . $variableName;
} elseif ($pFields[$conditionDescription['field_id']][1] == 'date') {
$r .= 'to_char(' . $prefixNoCondition . ', \'YYYYMMDD\') ' . $conditionDescription['condition'] . ' ' . $variableName;
} elseif ($pFields[$conditionDescription['field_id']][1] == 'time') {
$r .= 'to_char(' . $prefixNoCondition . ', \'HH24MISS\') ' . $conditionDescription['condition'] . ' ' . $variableName;
}
} else {
$methodDebut = '';
$methodFin = '';
if (isset($pFields[$conditionDescription['field_id']][4])) {
$methodDebut = $pFields[$conditionDescription['field_id']][4] . '(';
$methodFin = ')';
}
$r .= $prefix . $methodDebut . $variableName . $methodFin;
}
self::$_countParam++;
}
} else {
if (count($conditionDescription['value'])) {
$r .= ' ( ';
$firstCV = true;
foreach ($conditionDescription['value'] as $conditionValue) {
$variableName = ':' . $this->_compressVariable($pFields[$conditionDescription['field_id']][0] . '_' . $pFields[$conditionDescription['field_id']][2] . '_' . self::$_countParam);
if (!$firstCV) {
$r .= ' or ';
}
if ($conditionValue === null && $conditionDescription['condition'] == '=') {
$r .= $prefixNoCondition . ' IS NULL';
} elseif ($conditionValue === null && $conditionDescription['condition'] == '<>') {
$r .= $prefixNoCondition . ' IS NOT NULL';
} else {
if ($pConnection !== null && $pConnection->getProfile()->getDriverName() == 'oci' && in_array($pFields[$conditionDescription['field_id']][1], array('datetime', 'date', 'time'))) {
if ($pFields[$conditionDescription['field_id']][1] == 'datetime') {
$r .= 'to_char(' . $prefixNoCondition . ', \'YYYYMMDDHH24MISS\') ' . $conditionDescription['condition'] . ' ' . $variableName;
} elseif ($pFields[$conditionDescription['field_id']][1] == 'date') {
$r .= 'to_char(' . $prefixNoCondition . ', \'YYYYMMDD\') ' . $conditionDescription['condition'] . ' ' . $variableName;
} elseif ($pFields[$conditionDescription['field_id']][1] == 'time') {
$r .= 'to_char(' . $prefixNoCondition . ', \'HH24MISS\') ' . $conditionDescription['condition'] . ' ' . $variableName;
}
} else {
$methodDebut = '';
$methodFin = '';
if (isset($pFields[$conditionDescription['field_id']][4])) {
$methodDebut = $pFields[$conditionDescription['field_id']][4] . '(';
$methodFin = ')';
}
$r .= $prefix . $methodDebut . $variableName . $methodFin;
}
$fieldsForQueryParams[$variableName] = $this->_variableValue($pFields[$conditionDescription['field_id']][1], $conditionValue, $pConnection ? $pConnection->getProfile()->getDriverName() : null);
// $fieldsForQueryParams[$variableName] = $conditionValue;
}
$firstCV = false;
self::$_countParam++;
}
$r .= ' ) ';
}
}
}
}
//sub conditions
foreach ($pConditions->group as $conditionDetail) {
list($sql, $fields) = $this->_explainSQLCondition($conditionDetail, !$first, $pFields, $pConnection);
$r .= $sql;
$fieldsForQueryParams = array_merge($fieldsForQueryParams, $fields);
if (!$conditionDetail->isEmpty()) {
$first = false;
}
}
//adds parenthesis around the sql if needed (non empty)
if (strlen(trim($r)) > 0) {
$r = ($pExplainKind ? ' ' . $pConditions->kind . ' ' : '') . '(' . $r . ')';
}
return array($r, $fieldsForQueryParams);
}
