calculate cart sum

1 files changed, 33 insertions, 9 deletions

diff --git a/db/functions.sql b/db/functions.sql
index 995e7e9..5bbba54 100644
--- a/db/functions.sql
+++ b/db/functions.sql
@@ -1,30 +1,54 @@
-drop function if exists webs.cart;
-drop function if exists webs.add_to_cart;
+use webs;
+drop function if exists cart;
+drop function if exists add_to_cart;
 
 delimiter $$
-create function webs.cart(user_id int) -- get current order for user_id (cart order id)
+create function cart(user_id int) -- get current order for user_id (cart order id)
 returns int
 begin
-	set @order_id = (select id from webs.order where status = 1 and user = user_id);
+	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 webs.order (`user`) values (user_id);
-	set @order_id = (select id from webs.order where status = 1 and user = user_id);
+	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 webs.add_to_cart(product_id int, user_id int)
+create function add_to_cart(product_id int, user_id int)
 returns boolean
 begin
-	set @orderproduct_id = (select id from webs.orderproduct where product = product_id and `order` = cart(user_id));
+	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 webs.orderproduct (`product`, `count`, `order`) values (product_id, 1, cart(user_id));
+	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 ;