calculate cart sum

7 files changed, 67 insertions, 16 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 ;
 
diff --git a/public/admin-order.php b/public/admin-order.php
index debde66..8bb7fc0 100644
--- a/public/admin-order.php
+++ b/public/admin-order.php
@@ -14,6 +14,7 @@
 } while (false); ?>
 <?php
 function order_template($order) {
+	$subtotal = number_format($order->subtotal, 2, ',', '.');
 	echo <<<"EOF"
 		<form method="post">
 			<input type="hidden" name="id" value="$order->id">
@@ -21,6 +22,7 @@ function order_template($order) {
 				<td>$order->id</td>
 				<td>$order->user_name</td>
 				<td>$order->product_count</td>
+				<td>$subtotal</td>
 				<td>
 					<select name="status">
 	EOF;
@@ -57,16 +59,17 @@ function order_template($order) {
 	<div class="main limwidth">
 		<h2>bestellingen</h2>
 		<p>hier kun je bestellingen zien en de status aanpassen. wijzigingen kunnen doorgevoegd worden door op de 'bijwerken'-knop te drukken na het aanpassen van de status. maar één bestellingen kan aangepast worden per update!!</p>
-		<table>
+		<table class="order-table">
 			<tr>
 				<th>ID</th>
 				<th>besteller</th>
 				<th>aantal producten</th>
+				<th>subtotaal</th>
 				<th>status</th>
 				<th>update</th>
 			</tr>
 			<?php
-				$res = $cursor->query("select `order`.id as id, sum(orderproduct.count) as product_count, user.name as user_name, `order`.status from orderproduct join `order` on `order`.id = orderproduct.`order` join user on user.id = `order`.user where status > 1 group by orderproduct.`order` order by status asc");
+				$res = $cursor->query("select `order`.id as id, sum(orderproduct.count) as product_count, cart_sum(`order`.id) as subtotal, user.name as user_name, `order`.status from orderproduct join `order` on `order`.id = orderproduct.`order` join user on user.id = `order`.user where status > 1 group by orderproduct.`order` order by status asc");
 				while ($order = $res->fetch_object()) order_template($order);
 			?>
 		</table>
diff --git a/public/admin.css b/public/admin.css
index 991bb2d..bc1d821 100644
--- a/public/admin.css
+++ b/public/admin.css
@@ -34,8 +34,9 @@ table td {
 	border: 2px solid var(--bg-alt);
 }
 
-table td:nth-child(1) { text-align: center; }
-table td:nth-child(3) { text-align: right; }
-table td select,
-table td input { width: 100%; }
+.order-table td:nth-child(1) { text-align: center; }
+.order-table td:nth-child(3) { text-align: right; }
+.order-table td:nth-child(4) { text-align: right; }
+.order-table td select,
+.order-table td input { width: 100%; }
 
diff --git a/public/cart.css b/public/cart.css
index 277dcde..00aa0af 100644
--- a/public/cart.css
+++ b/public/cart.css
@@ -15,3 +15,5 @@
 .product input[type=number] { width: 32px; }
 
 .product .name { width: 100%; }
+
+.subtotal { font-size: 150%; }
diff --git a/public/cart.php b/public/cart.php
index ba33a9d..f7ee295 100644
--- a/public/cart.php
+++ b/public/cart.php
@@ -38,6 +38,7 @@
 <?php 
 function item_template($item) {
 	$image_path = $item->image ? "/img/product/$item->id-thumb.jpg" : "/img/placeholder.png";
+	$price = number_format($item->price, 2, ',', '.');
 	echo <<<"EOF"
 	<div class="product" product-id="$item->id">
 		<img src="$image_path" alt="productafbeelding">
@@ -45,7 +46,7 @@ function item_template($item) {
 		<label for="$item->id-count">hoeveelheid:</label>
 		<input type="number" value="$item->count" min="1" max="20" id="$item->id-count" class="count buttonstyle filled" disabled>
 		<button type="submit" value="$item->id" name="product_id" class="button outlined">weghalen</button>
-		<span class="price">$item->price</span>
+		<span class="price">$price</span>
 	</div>
 EOF;
 }
@@ -86,8 +87,17 @@ EOF;
 					<input type="hidden" name="type" value="delete">
 			EOF;
 			while ($product = $res->fetch_object()) item_template($product);
+
+			$statement = $cursor->prepare("select cart_sum(cart(?)) as `sum`");
+			$statement->bind_param("i", $user_id);
+			if (!$statement->execute()) break;
+			$res = $statement->get_result();
+			$subtotal = number_format($res->fetch_object()->sum, 2, ',', '.');
 			echo <<<"EOF"
 				</form>
+				<div class="alignright">
+					<span class="sum">Subtotaal: <span class="subtotal price">$subtotal</span></span>
+				</div>
 				<form class="product-footer" method="post" action="/order-complete.php">
 					<input type="submit" value="Bestellen" class="buttonstyle filled">
 				</form>
diff --git a/public/global.css b/public/global.css
index 2a23250..a34b503 100644
--- a/public/global.css
+++ b/public/global.css
@@ -102,3 +102,4 @@ input.buttonstyle[type=number] { cursor: unset; }
 }
 
 .skipafter { margin-bottom: 16px; }
+.alignright { text-align: right; }
diff --git a/public/index.php b/public/index.php
index 0fd0ff2..fecf624 100644
--- a/public/index.php
+++ b/public/index.php
@@ -20,6 +20,16 @@ function promobuff2str($price_buff, $count_buff) {
 <body>
 	<?php include 'navbar.php' ?>
 	<div class="main limwidth">
+		<?php do {
+			if (($user_privileges & PRIVILEGE_ADMIN) == 0) break;
+			echo <<<"EOF"
+				<div class="center">
+					<form action="/admin-promo.php" method="get" class="d-ib">
+						<input type="submit" value="Aanbiedingen beheren" class="button filled">
+					</form>
+				</div>
+			EOF;
+		} while (false); ?>
 		<div class="s1">
 			<h1>hier zijn de aanbiedingen</h1>
 			<ul>