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 | SELECT
SUM (
(
TO_DAYS( LEAST( s_date_end, r_date_last ) ) -
TO_DAYS( GREATEST( r_date_from, s_date_start ) )
) + 1 ) * sp_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
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
group by r_id
function get_total_price(reservation_id) {
-- return result of above select
}
|