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
33 | CREATE FUNCTION db.get_reservation_item_price(reservation_item_id INT)
RETURNS INT
READS SQL DATA
RETURN ( 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
AND r_date_from <= s_date_end
AND r_date_last >= s_date_start
WHERE ri_id = @reservation_item_id
group by r_id )
|