public function addValuesColumnQuery($query, $field, $val)
{
$sql_part = array();
$sql_params = array();
$value_from = $val['lower_value'];
$value_to = $val['upper_value'];
$unit = $val['property_unit'];
// We have only 1 Value
if ($value_from != '' && $value_to == '') {
if ($unit == '') {
$sql_part[] = ' ( lower_value = ? OR upper_value = ?) ';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
//We don't know the filed unit
} elseif (Properties::searchRecognizedUnitsGroups($unit) === false) {
$sql_part[] = ' ( lower_value = ? OR upper_value = ?) AND property_unit = ? ';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
$sql_params[] = $unit;
} else {
// Recognized unit
$sql_params[] = $value_from;
$sql_params[] = $unit;
$sql_params[] = $unit;
$unitGroupStr = implode(',', array_fill(0, count($unitGroup), '?'));
$sql_part[] = ' ( convert_to_unified ( ?, ? ) BETWEEN lower_value_unified AND upper_value_unified) AND is_property_unit_in_group(property_unit, ?) ';
}
} elseif ($value_from != '' && $value_to != '') {
if ($unit == '') {
$sql_part[] = ' ( ( lower_value = ? OR upper_value = ?) OR ( lower_value = ? OR upper_value = ?) )';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
$sql_params[] = $value_to;
$sql_params[] = $value_to;
//We don't know the filed unit
} elseif (Properties::searchRecognizedUnitsGroups($unit) === false) {
$sql_part[] = ' ( ( lower_value = ? OR upper_value = ?) OR ( lower_value = ? OR upper_value = ?) ) AND property_unit = ? ';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
$sql_params[] = $value_to;
$sql_params[] = $value_to;
$sql_params[] = $unit;
} else {
// Recognized unit
$conn_MGR = Doctrine_Manager::connection();
$lv = $conn_MGR->quote($value_from, 'string');
$uv = $conn_MGR->quote($value_to, 'string');
$unit = $conn_MGR->quote($unit, 'string');
$sql_part[] = "\n (\n ( lower_value_unified BETWEEN convert_to_unified({$lv},{$unit}) AND convert_to_unified({$uv},{$unit}))\n OR\n ( upper_value_unified BETWEEN convert_to_unified({$lv},{$unit}) AND convert_to_unified({$uv},{$unit}))\n )\n OR\n (\n lower_value_unified BETWEEN 0 AND convert_to_unified({$lv},{$unit})\n AND\n upper_value_unified BETWEEN convert_to_unified({$uv},{$unit}) AND 'Infinity'\n )";
$query->andWhere("is_property_unit_in_group(property_unit,{$unit})");
}
}
if (!empty($sql_part)) {
$query->andWhere(implode(' AND ', $sql_part), $sql_params);
}
return $query;
}
public function addPropertiesQuery($query, $type, $applies_to, $value_from, $value_to, $unit)
{
$sql_part = array();
$sql_params = array();
if (trim($type) != '') {
$sql_part[] = ' property_type = ? ';
$sql_params[] = $type;
}
if (trim($applies_to) != '') {
$sql_part[] = ' applies_to = ? ';
$sql_params[] = $applies_to;
}
$value_from = trim($value_from);
$value_to = trim($value_to);
$unit = trim($unit);
if ($value_from == '' && $value_to != '') {
$value_from = $value_to;
$value_to = '';
}
// We have only 1 Value
if ($value_from != '' && $value_to == '') {
if ($unit == '') {
$sql_part[] = ' ( p.lower_value = ? OR p.upper_value = ?) ';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
//We don't know the filed unit
} elseif (Properties::searchRecognizedUnitsGroups($unit) === false) {
$sql_part[] = ' ( p.lower_value = ? OR p.upper_value = ?) AND property_unit = ? ';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
$sql_params[] = $unit;
} else {
// Recognized unit
$sql_params[] = $value_from;
$sql_params[] = $unit;
$sql_params[] = $unit;
$sql_part[] = ' ( convert_to_unified ( ?, ? ) BETWEEN p.lower_value_unified AND p.upper_value_unified) AND is_property_unit_in_group(property_unit, ?) ';
}
} elseif ($value_from != '' && $value_to != '') {
if ($unit == '') {
$sql_part[] = ' ( ( p.lower_value = ? OR p.upper_value = ?) OR ( p.lower_value = ? OR p.upper_value = ?) )';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
$sql_params[] = $value_to;
$sql_params[] = $value_to;
//We don't know the filed unit
} elseif (Properties::searchRecognizedUnitsGroups($unit) === false) {
$sql_part[] = ' ( ( p.lower_value = ? OR p.upper_value = ?) OR ( p.lower_value = ? OR p.upper_value = ?) ) AND property_unit = ? ';
$sql_params[] = $value_from;
$sql_params[] = $value_from;
$sql_params[] = $value_to;
$sql_params[] = $value_to;
$sql_params[] = $unit;
} else {
// Recognized unit
$conn_MGR = Doctrine_Manager::connection();
$lv = $conn_MGR->quote($value_from, 'string');
$uv = $conn_MGR->quote($value_to, 'string');
$unit = $conn_MGR->quote($unit, 'string');
$sql_part[] = "\n (\n ( p.lower_value_unified BETWEEN convert_to_unified({$lv},{$unit}) AND convert_to_unified({$uv},{$unit}))\n OR\n ( p.upper_value_unified BETWEEN convert_to_unified({$lv},{$unit}) AND convert_to_unified({$uv},{$unit}))\n )\n OR\n (\n p.lower_value_unified BETWEEN 0 AND convert_to_unified({$lv},{$unit})\n AND\n p.upper_value_unified BETWEEN convert_to_unified({$uv},{$unit}) AND 'Infinity'\n )";
$query->andWhere("is_property_unit_in_group(property_unit,{$unit})");
}
} elseif ($unit != '') {
$sql_part[] = ' property_unit = ? ';
$sql_params[] = $unit;
}
if (!empty($sql_part)) {
$query->innerJoin('s.SubProperties p');
$query->groupBy("s.id");
$query->andWhere(implode(' AND ', $sql_part), $sql_params);
$this->with_group = true;
}
return $query;
}
