blob: 5bbba54df4a2e0dcdd4710f0af5018da4112c0e9 (
plain)
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
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
use webs;
drop function if exists cart;
drop function if exists add_to_cart;
delimiter $$
create function cart(user_id int) -- get current order for user_id (cart order id)
returns int
begin
set @order_id = (select id from `order` where status = 1 and user = user_id);
if @order_id is not null then
return @order_id;
end if;
insert into `order` (`user`) values (user_id);
set @order_id = (select id from `order` where status = 1 and user = user_id);
return @order_id;
end$$
create function add_to_cart(product_id int, user_id int)
returns boolean
begin
set @orderproduct_id = (select id from orderproduct where product = product_id and `order` = cart(user_id));
if @orderproduct_id is not null then
update orderproduct set count = count + 1 where id = @orderproduct_id;
return true;
end if;
insert into orderproduct (`product`, `count`, `order`) values (product_id, 1, cart(user_id));
return false;
end$$
create function cart_sum(order_id int) -- get cart sum for order
returns int
begin
return (
with precalc
as (
select
product.price,
orderproduct.count,
promotion.count_buff,
promotion.price_buff,
floor(orderproduct.count / promotion.count_buff) * promotion.count_buff as qualify_count,
mod(orderproduct.count, promotion.count_buff) as remainder_count
from orderproduct
join product on product.id = orderproduct.product
join promotion on promotion.product = product.id
where `order` = order_id
)
select sum(price * qualify_count * price_buff + price * remainder_count)
from precalc
);
end$$
delimiter ;
|