add/remove from cart working

4 files changed, 56 insertions, 13 deletions

diff --git a/db/data.sql b/db/data.sql
index 01f18aa..e6ac757 100644
--- a/db/data.sql
+++ b/db/data.sql
@@ -27,14 +27,16 @@ insert into webs.user (`name`, `hash`) values
 	("willem", "$2b$12$vCDpn5fnGBL7dv3Ty1cgZegDKOguoRIgHNrUFYOCWoensgI4HnJde"); -- biege
 update webs.user set `privileges` = 1073741824 where `name` = "loek";
 
-insert into webs.cart (`product`, `user`) values
-	(4, 1),
-	(5, 1),
-	(6, 1),
-	(7, 1),
-	(8, 1);
-
 insert into webs.promotion (`product`, `price_buff`) values
 	(1, 0.80), -- 20% korting
 	(2, 0.80),
 	(3, 0.80);
+
+set @order_id = webs.cart(1); -- cart id voor loek
+insert into webs.orderproduct (`product`, `count`, `order`) values
+	(4, 1, @order_id),
+	(5, 1, @order_id),
+	(6, 1, @order_id),
+	(7, 1, @order_id),
+	(8, 1, @order_id);
+
diff --git a/db/functions.sql b/db/functions.sql
new file mode 100644
index 0000000..995e7e9
--- /dev/null
+++ b/db/functions.sql
@@ -0,0 +1,30 @@
+drop function if exists webs.cart;
+drop function if exists webs.add_to_cart;
+
+delimiter $$
+create function webs.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);
+	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);
+	return @order_id;
+end$$
+
+create function webs.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));
+	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));
+	return false;
+end$$
+
+delimiter ;
+
diff --git a/db/init.sql b/db/init.sql
index b7b3310..df7aa30 100644
--- a/db/init.sql
+++ b/db/init.sql
@@ -33,19 +33,30 @@ create table if not exists webs.user (
   primary key (`id`)
 );
 
-create table if not exists webs.cart (
+create table if not exists webs.order (
   `id` int not null auto_increment,
-  `product` int not null,
+  `status` int not null default 1,
   `user` int not null,
+  primary key (`id`),
+  constraint `order_user_fk`
+    foreign key (`user`)
+    references webs.user (`id`)
+    on update cascade
+);
+
+create table if not exists webs.orderproduct (
+  `id` int not null auto_increment,
+  `product` int not null,
 	`count` int not null default 1,
+	`order` int not null,
   primary key (`id`),
   constraint `cart_product_fk`
     foreign key (`product`)
     references webs.product (`id`)
     on update cascade,
-  constraint `cart_user_fk`
-    foreign key (`user`)
-    references webs.user (`id`)
+  constraint `cart_order_fk`
+    foreign key (`order`)
+    references webs.order (`id`)
     on update cascade
 );
 
diff --git a/db/makefile b/db/makefile
index 0802fa6..59d9fc2 100644
--- a/db/makefile
+++ b/db/makefile
@@ -18,7 +18,7 @@ data: data.sql
 full: full.sql
 	$(SQL) < $<
 
-base.sql: reset.sql init.sql
+base.sql: reset.sql init.sql functions.sql
 	cat $^ > $@
 
 full.sql: base.sql data.sql