1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28 | -- We are counting reservation days, therefore we will always add 1, because:
-- 31/08 - 29/07 are 3 days, not 2 which will be returned.
--
-- And we need the r_date_from <= s_date_end AND r_date_last >= s_date_start as
-- a season contains those dates as well..
SELECT r_id,
(
(
TO_DAYS( LEAST( s_date_end, r_date_last ) ) -
TO_DAYS( GREATEST( r_date_from, s_date_start ) )
) + 1
)
AS total_days
, s_date_start, s_date_end, r_date_from, r_date_last
FROM reservations
INNER JOIN reservation_items ON ri_reservationsid = r_id
INNER JOIN products ON ri_productsid = p_id
INNER JOIN product_groups ON if( p_type = "house", p_house_public_productgroupsid, p_productgroupsid ) = pg_id
INNER JOIN season_prices ON sp_productgroupsid = pg_id
INNER JOIN seasons ON s_id = sp_seasonsid
AND r_date_from <= s_date_end
AND r_date_last >= s_date_start
WHERE (
ri_parentid =0
OR ri_parentid IS NULL
)
AND ri_id =1
|