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
29
30
31
32 | SELECT if (sp_interval = "day"
,
(
SUM(
(
(
TO_DAYS(LEAST(s_date_end, r_date_last))
- TO_DAYS(GREATEST(r_date_from, s_date_start))
) + 1
) * sp_price
)
)
,( ri_amount * ri_price )
) AS total_price
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
WHERE ( ri_id = 1 ) OR (ri_parentid = 1 )
if (sp_interval = "day",
(AND r_date_from <= s_date_end AND r_date_last >= s_date_start )
, ??
)
group by r_id
|