From e031215516573b14545e81665b991176a467bce5 Mon Sep 17 00:00:00 2001
From: Sudipto Mallick <smlckz@termux-alpine>
Date: Sat, 3 Feb 2024 16:29:27 +0000
Subject: Implement PHP assignment #3, improve the rest

---
 mysql-php/code/a3.php | 132 +++++++++++++++++++++++++++++---------------------
 1 file changed, 77 insertions(+), 55 deletions(-)

(limited to 'mysql-php/code/a3.php')

diff --git a/mysql-php/code/a3.php b/mysql-php/code/a3.php
index f029dd8..65fa2b9 100644
--- a/mysql-php/code/a3.php
+++ b/mysql-php/code/a3.php
@@ -1,23 +1,24 @@
 <?php
 declare(strict_types=1);
 error_reporting(E_ALL);
-// student: name roll city email date_of_birth
+
 function connect_to_database() {
     mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
-    if (!($dbh = mysqli_connect('localhost', 'root', '')))
-        display_failure('Could not connect to the database: ' . mysqli_connect_error($dbh));
-    mysqli_set_charset($dbh, 'utf8mb4');
-    if (!mysqli_query($dbh, 'CREATE DATABASE IF NOT EXISTS STUDENTS_DB'))
-        display_failure('Could not create database: ' . mysqli_error($dbh));
-    mysqli_select_db($dbh, 'STUDENTS_DB');
-    if (!mysqli_query($dbh, 'CREATE TABLE IF NOT EXISTS STUDENT (
-        ROLL INT(20) PRIMARY KEY,
-        NAME VARCHAR(255),
-        CITY VARCHAR(255),
-        EMAIL VARCHAR(255),
-        DATE_OF_BIRTH DATE
-    )'))
-        display_failure('Could not create table: ' . mysqli_error($dbh));
+    $dbh = new mysqli('localhost', 'root', '');
+    $dbh->set_charset('utf8mb4');
+    $dbh->query('CREATE DATABASE IF NOT EXISTS EATERY_DB');
+    $dbh->select_db('EATERY_DB');
+    $dbh->query('CREATE TABLE IF NOT EXISTS `FOOD_DETAILS` (`FOOD_ID` INT PRIMARY KEY, `FOOD_ITEM` VARCHAR(255), `PRICE_PER_ITEM` DOUBLE, UNIQUE (`FOOD_ID`))');
+    $dbh->query('INSERT INTO `FOOD_DETAILS` VALUES
+        (1, "Egg Biriyani (w/ 2 eggs)", 200),
+        (2, "Chicken Biriyani", 300),
+        (3, "Mutton Biriyani", 350),
+        (4, "Veg Pulao", 200),
+        (5, "Shahi Paneer", 220),
+        (6, "Schezwan Paneer", 240),
+        (7, "Steam Rice", 90)
+        ON DUPLICATE KEY UPDATE `FOOD_ITEM` = VALUES(`FOOD_ITEM`)');
+    $dbh->query('CREATE TABLE IF NOT EXISTS `CUSTOMER_DETAILS` (`BILL_ID` INT PRIMARY KEY AUTO_INCREMENT, `CUSTOMER_NAME` VARCHAR(255), `TOTAL_AMOUNT_PAID` DOUBLE, `DATE_OF_PAYMENT` DATE)');
     return $dbh;
 }
 
@@ -32,22 +33,14 @@ h2 { font-size: 1.8rem; font-weight: 500; }
 form { margin: 2em auto; width: 20em; }
 form > * { padding: 0.5em; }
 table, tr, th, td { border-collapse: collapse; border: 1px solid black; }
+table.noborder, table.noborder * { border: none; }
+table.invoice td:nth-child(2), table.invoice td:nth-child(3) { text-align: end; }
+p.right { text-align: right; }
 th, td { padding: 5px; }
 </style>
 <?php
 }
 
-function display_search_form() {
-    html_prologue('Student details');
-    ?>
-<form action="<?php echo $_SERVER['PHP_SELF']; ?>">
-    <h2>Enter E-mail address to search for the record of a student</h2>
-    <label>E-mail: <input type="email" name="email"></label>
-    <input type="submit" value="Search">
-</form>
-<?php
-}
-
 function display_failure($reason) {
     html_prologue('Operation failure');
     ?>
@@ -57,39 +50,68 @@ function display_failure($reason) {
     die();
 }
 
-function search_and_show($dbh, $email) {
-    $stmt = mysqli_prepare($dbh, 'SELECT * FROM STUDENT WHERE EMAIL = ?');
-    $result = mysqli_query($dbh, );
-    html_prologue('Students\' details');
+function show_menu($dbh) {
+    html_prologue('Eatery Menu');
     ?>
-<h2>Students' details</h2>
-<p><?php echo mysqli_num_rows($result); ?> record(s) found.</p>
-<table>
-    <tr>
-        <th>Roll No.</th>
-        <th>Name</th>
-        <th>E-mail</th>
-        <th>City</th>
-        <th>Date of birth</th>
-    </tr><?php
-    while ($row = mysqli_fetch_assoc($result)) { ?>
-    <tr><td>
-        <?php echo implode('</td><td>', array_map('htmlspecialchars', [
-            $row['ROLL'], $row['NAME'], $row['EMAIL'], $row['CITY'],
-            $row['DATE_OF_BIRTH']		
-        ])); ?>
-    </td></tr>
-    <?php
+<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
+<h1>Welcome to Delish Eatery</h1>
+<table class="noborder">
+<tr><td><label for="name">Name:</label></td><td colspan="2"><input type="text" name="customer_name" id="name"></td></tr>
+<tr><th>Servings</th><th>Food item</th><th>Price</th></tr>
+    <?php $result = $dbh->query('SELECT * FROM `FOOD_DETAILS`');
+    while ($row = $result->fetch_assoc()) {
+        echo '<tr><td><input type="number" min="0" placeholder="0" name="servings[', $row['FOOD_ID'], ']"></td>';
+        echo '<td>', $row['FOOD_ITEM'], '</td>', '<td>₹', number_format(+$row['PRICE_PER_ITEM'], 2), '</td></tr>';
     } ?>
+<tr><td colspan="3"><input type="submit" value="Order"></td></tr>
 </table>
-<?php
+</form>
+    <?php
+}
+
+function process_order($dbh) {
+    if (empty($_POST['customer_name']))
+        display_failure('Can not order without the customer name');
+    $servings = empty($_POST['servings']) ? [] : array_filter(array_map('intval', $_POST['servings']));
+    if (count($servings) == 0)
+        display_failure('No serving selected in the order');
+    $orders = array_filter(array_map('intval', array_keys($servings)));
+    $items = $dbh->query('SELECT * FROM `FOOD_DETAILS` WHERE `FOOD_ID` IN (' . implode(', ', $orders) . ')')->fetch_all(MYSQLI_ASSOC);
+    $total_price = 0.0;
+    foreach ($items as $i => $item) {
+        $items[$i]['price'] = $servings[$item['FOOD_ID']] * $item['PRICE_PER_ITEM'];
+        $total_price += $items[$i]['price'];
+    }
+    $tax = 0.15 * $total_price;
+    $net_price = $total_price + $tax;
+    $stmt = $dbh->prepare('INSERT INTO `CUSTOMER_DETAILS` (`CUSTOMER_NAME`, `TOTAL_AMOUNT_PAID`, `DATE_OF_PAYMENT`) VALUES (?, ?, ?)');
+    $stmt->bind_param('sds', $_POST['customer_name'], $net_price, @date('Y-m-d'));
+    $stmt->execute();
+    $bill_id = $dbh->insert_id;
+    html_prologue('Customer invoice');
+    ?>
+<h1>Customer Invoice</h1>
+<p class="right">Bill No.: D.E./<?php echo date('Y.m.d'), '/', $bill_id; ?></p>
+<p><b>Name</b>: <?php echo $_POST['customer_name']; ?></p>
+<table class="invoice">
+<tr><th>Food item</th><th>Servings</th><th>Price</th></tr>
+<?php foreach ($items as $item) {
+    echo '<tr><td>', implode('</td><td>', [$item['FOOD_ITEM'], $servings[$item['FOOD_ID']], number_format($item['price'], 2)]), '</td></tr>';
+} ?>
+<tr><td colspan="2">Total:</td><td><?php echo number_format($total_price, 2); ?></td></tr>
+<tr><td colspan="2">GST (15%):</td><td><?php echo number_format($tax, 2); ?></td></tr>
+<tr><td colspan="2">Net price:</td><td><?php echo number_format($net_price, 2); ?></td></tr>
+</table>
+    <?php
 }
 
-if (isset($_GET['email']) && !empty($_GET['email'])) {
+try {
     $dbh = connect_to_database();
-    search_and_show($dbh, $_GET['email']);
-    mysqli_close($dbh);
-} else {
-    html_prologue('Search for a student');
-    display_search_form();
+    if ($_SERVER['REQUEST_METHOD'] == 'POST')
+        process_order($dbh);
+    else show_menu($dbh);
+    $dbh->close();
+} catch (mysqli_sql_exception $e) {
+    display_failure($e->getMessage());
 }
+
-- 
cgit 1.4.1-2-gfad0