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; }