[Top][All Lists]
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Fmsystem-commits] [7122] Rental: new SQL logic to query composites vaca
|
From: |
Yngve Espelid |
|
Subject: |
[Fmsystem-commits] [7122] Rental: new SQL logic to query composites vacancy (active contracts) and bugfix in autocomplete of search period |
|
Date: |
Tue, 15 Mar 2011 09:12:26 +0000 |
Revision: 7122
http://svn.sv.gnu.org/viewvc/?view=rev&root=fmsystem&revision=7122
Author: yes
Date: 2011-03-15 09:12:26 +0000 (Tue, 15 Mar 2011)
Log Message:
-----------
Rental: new SQL logic to query composites vacancy (active contracts) and bugfix
in autocomplete of search period
Modified Paths:
--------------
trunk/rental/inc/class.socomposite.inc.php
trunk/rental/templates/base/composite_list_partial.php
Modified: trunk/rental/inc/class.socomposite.inc.php
===================================================================
--- trunk/rental/inc/class.socomposite.inc.php 2011-03-15 08:53:24 UTC (rev
7121)
+++ trunk/rental/inc/class.socomposite.inc.php 2011-03-15 09:12:26 UTC (rev
7122)
@@ -68,32 +68,49 @@
}
$special_query = false; //specify if the query should use
distinct on rental_composite.id (used for selecting composites that has an
active or inactive contract)
$ts_query = strtotime(date('Y-m-d')); // timestamp for query
(today)
+ $availability_date_from = $ts_query;
+ $availability_date_to = $ts_query;
+
if(isset($filters['availability_date_from']) &&
$filters['availability_date_from'] != ''){
$availability_date_from =
strtotime($filters['availability_date_from']);
}
- else{
- $availability_date_from = $ts_query;
- }
+
if(isset($filters['availability_date_to']) &&
$filters['availability_date_to'] != ''){
$availability_date_to =
strtotime($filters['availability_date_to']);
}
- else{
- $availability_date_to = $ts_query;
- }
+
switch($filters['has_contract']){
case "has_contract":
- $filter_clauses[] = "(NOT
rental_contract_composite.contract_id IS NULL AND NOT((NOT
rental_contract.date_start IS NULL AND ( rental_contract.date_start <
$availability_date_from AND rental_contract.date_end < $availability_date_from)
OR (rental_contract.date_end IS NULL OR ( rental_contract.date_start >
$availability_date_to AND rental_contract.date_end >
$availability_date_to)))))";
- //$filter_clauses[] = "(NOT
rental_contract_composite.contract_id IS NULL AND (NOT
rental_contract.date_start IS NULL AND (NOT rental_contract.date_start <
$availability_date_from AND NOT rental_contract.date_end <
$availability_date_from) OR (rental_contract.date_end IS NULL OR (NOT
rental_contract.date_start > $availability_date_to AND NOT
rental_contract.date_end > $availability_date_to))))";
- //$filter_clauses[] = "(NOT
rental_contract_composite.contract_id IS NULL AND (NOT
rental_contract.date_start IS NULL AND rental_contract.date_start <
$availability_date_from OR rental_contract.date_end < $availability_date_from
AND (rental_contract.date_end IS NULL OR (rental_contract.date_start >
$availability_date_to AND rental_contract.date_end > $availability_date_to))))";
- //$filter_clauses[] = "(NOT
rental_contract_composite.contract_id IS NULL AND (NOT
rental_contract.date_start IS NULL AND rental_contract.date_start <
$availability_date_from AND ((rental_contract.date_end IS NULL OR (NOT
rental_contract.date_end IS NULL AND rental_contract.date_end >
$availability_date_from)) OR (rental_contract.date_start >
$availability_date_to AND (rental_contract.date_end IS NULL OR (NOT
rental_contract.date_end IS NULL AND rental_contract.date_end <
$availability_date_to))))))";
- //$filter_clauses[] = "(NOT
rental_contract_composite.contract_id IS NULL AND (NOT
rental_contract.date_start IS NULL AND rental_contract.date_start <
$availability_date_from AND (rental_contract.date_end IS NULL OR (NOT
rental_contract.date_end IS NULL AND rental_contract.date_end >
$availability_date_from))))";
+ $filter_clauses[] = "NOT
rental_contract_composite.contract_id IS NULL"; // Composite must have a
contract
+ $filter_clauses[] = "NOT
rental_contract.date_start IS NULL"; // The contract must have start date
+
+ /* The contract's start date not after the end
of the period if there is no end date */
+ $filter_clauses[] = "
+ ((NOT rental_contract.date_start >
$availability_date_to AND rental_contract.date_end IS NULL)
+ OR
+ (NOT rental_contract.date_start >
$availability_date_to AND NOT rental_contract.date_end IS NULL AND NOT
rental_contract.date_end < $availability_date_from))";
$special_query=true;
break;
case "has_no_contract":
- //$filter_clauses[] =
"(rental_contract_composite.contract_id IS NULL OR NOT rental_composite.id IN
(SELECT rental_composite.id FROM rental_composite LEFT JOIN
rental_contract_composite ON (rental_contract_composite.composite_id =
rental_composite.id) LEFT JOIN rental_contract ON (rental_contract.id =
rental_contract_composite.contract_id) WHERE 1=1 AND rental_composite.is_active
= TRUE AND (NOT rental_contract_composite.contract_id IS NULL AND (NOT
rental_contract.date_start IS NULL AND rental_contract.date_start <
$availability_date_from AND (rental_contract.date_end IS NULL OR (NOT
rental_contract.date_end IS NULL AND rental_contract.date_end >
$availability_date_from))))))";
- //$filter_clauses[] =
"(rental_contract_composite.contract_id IS NULL OR NOT rental_composite.id IN
(SELECT rental_composite.id FROM rental_composite LEFT JOIN
rental_contract_composite ON (rental_contract_composite.composite_id =
rental_composite.id) LEFT JOIN rental_contract ON (rental_contract.id =
rental_contract_composite.contract_id) WHERE 1=1 AND rental_composite.is_active
= TRUE AND (NOT rental_contract_composite.contract_id IS NULL OR (NOT
rental_contract.date_start IS NULL OR (((rental_contract.date_start <
$availability_date_from AND rental_contract.date_end < $availability_date_from)
OR (NOT rental_contract.date_end IS NULL OR (rental_contract.date_start >
$availability_date_to AND rental_contract.date_end >
$availability_date_to))))))))";
- $filter_clauses[] =
"(rental_contract_composite.contract_id IS NULL OR NOT rental_composite.id IN
(SELECT rental_composite.id FROM rental_composite LEFT JOIN
rental_contract_composite ON (rental_contract_composite.composite_id =
rental_composite.id) LEFT JOIN rental_contract ON (rental_contract.id =
rental_contract_composite.contract_id) WHERE NOT
(((((rental_contract.date_start < $availability_date_from AND
rental_contract.date_end < $availability_date_from) OR (
(rental_contract.date_start > $availability_date_to AND
rental_contract.date_end > $availability_date_to))))))))";
-
+ $filter_clauses[] = "
+ (
+ rental_contract_composite.contract_id
IS NULL OR
+ NOT rental_composite.id IN
+ (
+ SELECT rental_composite.id FROM
rental_composite
+ LEFT JOIN
rental_contract_composite ON (rental_contract_composite.composite_id =
rental_composite.id)
+ LEFT JOIN rental_contract ON
(rental_contract.id = rental_contract_composite.contract_id)
+ WHERE
+ (
+ NOT
rental_contract_composite.contract_id IS NULL AND
+ NOT
rental_contract.date_start IS NULL AND
+ ((NOT
rental_contract.date_start > $availability_date_to AND rental_contract.date_end
IS NULL)
+ OR
+ (NOT
rental_contract.date_start > $availability_date_to AND NOT
rental_contract.date_end IS NULL AND NOT rental_contract.date_end <
$availability_date_from))
+ )
+ )
+ )
+ ";
$special_query=true;
break;
case "both":
@@ -130,27 +147,6 @@
$joins .= " {$this->left_join} rental_contract_composite ON
(rental_contract_composite.composite_id = rental_composite.id)";
$joins .= " {$this->left_join} rental_contract ON
(rental_contract.id = rental_contract_composite.contract_id)";
- if(isset($filters['availability_date_from']) &&
$filters['availability_date_from'] != ''){
- $availability_date_from =
strtotime($filters['availability_date_from']);
- }
- else
- {
- $availability_date_from = strtotime(date('Y-m-d'));
- }
- if(isset($filters['availability_date_to']) &&
$filters['availability_date_to'] != ''){
- $availability_date_to =
strtotime($filters['availability_date_to']);
- }
- else
- {
- if(isset($filters['availability_date_from']) &&
$filters['availability_date_from'] != ''){
- $availability_date_to =
strtotime($filters['availability_date_from']);
- }
- else{
- $availability_date_to =
strtotime(date('Y-m-d'));
- }
- }
-
-
if($return_count) // We should only return a count
{
$cols = 'COUNT(DISTINCT(rental_composite.id)) AS count';
@@ -159,17 +155,28 @@
{
if($special_query)
{
- $cols = "DISTINCT(rental_composite.id) AS
composite_id, rental_unit.id AS unit_id, rental_unit.location_code,
rental_composite.name, rental_composite.has_custom_address,
rental_composite.address_1, rental_composite.house_number,
rental_composite.address_2, rental_composite.postcode, rental_composite.place,
rental_composite.is_active, rental_composite.area, CASE WHEN (NOT
rental_contract_composite.contract_id IS NULL AND (NOT
rental_contract.date_start IS NULL AND (NOT (rental_contract.date_start <
$availability_date_from AND rental_contract.date_end <
$availability_date_from)) AND (rental_contract.date_end IS NULL OR NOT
(rental_contract.date_start > $availability_date_to AND
rental_contract.date_end > $availability_date_to)))) THEN 'Ikke ledig' ELSE
'Ledig' END as status";
+ $cols = "DISTINCT(rental_composite.id) AS
composite_id,";
}
else
{
- $cols = "rental_composite.id AS composite_id,
rental_unit.id AS unit_id, rental_unit.location_code, rental_composite.name,
rental_composite.has_custom_address, rental_composite.address_1,
rental_composite.house_number, rental_composite.address_2,
rental_composite.postcode, rental_composite.place, rental_composite.is_active,
rental_composite.area, CASE WHEN (NOT rental_contract_composite.contract_id IS
NULL AND (NOT rental_contract.date_start IS NULL AND (NOT
(rental_contract.date_start < $availability_date_from AND
rental_contract.date_end < $availability_date_from)) AND
(rental_contract.date_end IS NULL OR NOT (rental_contract.date_start >
$availability_date_to AND rental_contract.date_end > $availability_date_to))))
THEN 'Ikke ledig' ELSE 'Ledig' END as status";
+ $cols = "rental_composite.id AS composite_id,";
}
+ $cols .= "rental_unit.id AS unit_id,
rental_unit.location_code, rental_composite.name,
rental_composite.has_custom_address, rental_composite.address_1,
rental_composite.house_number, rental_composite.address_2,
rental_composite.postcode, rental_composite.place, rental_composite.is_active,
rental_composite.area, ";
+ $cols .= "
+ CASE WHEN
+ (
+ NOT rental_contract_composite.contract_id IS
NULL AND
+ NOT rental_contract.date_start IS NULL AND
+ ((NOT rental_contract.date_start >
$availability_date_to AND rental_contract.date_end IS NULL)
+ OR
+ (NOT rental_contract.date_start >
$availability_date_to AND NOT rental_contract.date_end IS NULL AND NOT
rental_contract.date_end < $availability_date_from))
+ )
+ THEN 'Ikke ledig' ELSE 'Ledig' END as status";
}
$dir = $ascending ? 'ASC' : 'DESC';
$order = $sort_field ? "ORDER BY {$this->marshal($sort_field,
'field')} $dir ": '';
- //var_dump("SELECT {$cols} FROM {$tables} {$joins} WHERE
{$condition} {$order}");
+ //var_dump("SELECT {$cols} FROM {$tables} {$joins} WHERE
{$condition} {$order}");
return "SELECT {$cols} FROM {$tables} {$joins} WHERE
{$condition} {$order}";
}
Modified: trunk/rental/templates/base/composite_list_partial.php
===================================================================
--- trunk/rental/templates/base/composite_list_partial.php 2011-03-15
08:53:24 UTC (rev 7121)
+++ trunk/rental/templates/base/composite_list_partial.php 2011-03-15
09:12:26 UTC (rev 7122)
@@ -5,7 +5,7 @@
if(document.forms[0].availability_date_to.value == '')
{
document.forms[0].availability_date_to.value =
document.forms[0].availability_date_from.value;
- document.forms[0].availability_date_to.value_hidden =
document.forms[0].availability_date_from_hidden.value
+ document.forms[0].availability_date_to_hidden.value =
document.forms[0].availability_date_from_hidden.value
}
return true;
}
| [Prev in Thread] |
Current Thread |
[Next in Thread] |
- [Fmsystem-commits] [7122] Rental: new SQL logic to query composites vacancy (active contracts) and bugfix in autocomplete of search period,
Yngve Espelid <=