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/a1.php    |  24 ++++-----
 mysql-php/code/a2.php    |  13 +++--
 mysql-php/code/a3.php    | 132 +++++++++++++++++++++++++++--------------------
 mysql-php/output/3/1.png | Bin 0 -> 45417 bytes
 mysql-php/output/3/2.png | Bin 0 -> 34124 bytes
 5 files changed, 95 insertions(+), 74 deletions(-)
 create mode 100644 mysql-php/output/3/1.png
 create mode 100644 mysql-php/output/3/2.png

diff --git a/mysql-php/code/a1.php b/mysql-php/code/a1.php
index 4569ca8..614f044 100644
--- a/mysql-php/code/a1.php
+++ b/mysql-php/code/a1.php
@@ -1,7 +1,7 @@
 <?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', '')))
@@ -103,7 +103,7 @@ function display_failure($reason) {
 function check_post_vars($vardesc) {
     $vars = [];
     foreach ($vardesc as $name => $desc) {
-        if (!isset($_POST[$name]) || empty($_POST[$name]))
+        if (empty($_POST[$name]))
             display_failure('The ' . $desc . ' field can not be empty');
         $vars[$name] = $_POST[$name];
     }
@@ -186,7 +186,7 @@ function show_record($record) {
 <?php
 }
 
-if (!isset($_SERVER['PATH_INFO']) || empty($_SERVER['PATH_INFO']) || $_SERVER['PATH_INFO'] == '/') {
+if (empty($_SERVER['PATH_INFO']) || $_SERVER['PATH_INFO'] == '/') {
     display_menu();
     die();
 }
@@ -211,17 +211,17 @@ if ($path === 'insert') {
     show_table_dob_range($dbh);
     mysqli_close($dbh);
 } elseif ($path === 'search') {
-    if (isset($_GET['email']) && !empty($_GET['email'])) {
-        $dbh = connect_to_database();
-        if (!($record = search_student($dbh, $_GET['email']))) {
-            mysqli_close($dbh);
-            display_failure('Record not found for given E-mail: ' . htmlspecialchars($_GET['email']));
-        }
-        show_record($record);
-        mysqli_close($dbh);
-    } else {
+    if (empty($_GET['email'])) {
         display_search_form();
+        die();
     }
+    $dbh = connect_to_database();
+    if (!($record = search_student($dbh, $_GET['email']))) {
+        mysqli_close($dbh);
+        display_failure('Record not found for given E-mail: ' . htmlspecialchars($_GET['email']));
+    }
+    show_record($record);
+    mysqli_close($dbh);
 } else {
     display_menu();
     echo '<p class="error">Path <code>' . htmlspecialchars($path) . '</code> was not found.</p>';
diff --git a/mysql-php/code/a2.php b/mysql-php/code/a2.php
index 10a7a33..9d9a1a5 100644
--- a/mysql-php/code/a2.php
+++ b/mysql-php/code/a2.php
@@ -1,7 +1,7 @@
 <?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', '')))
@@ -32,8 +32,7 @@ function connect_to_database() {
 }
 
 function check_credentials($dbh, $username, $password) {
-    if (!isset($username) || empty($username) || !isset($password) || empty($password)) 
-        return false;
+    if (empty($username) || empty($password)) return false;
     $stmt = mysqli_prepare($dbh, 'SELECT `PASSWORD` FROM `LOGIN` WHERE `USERNAME` = ?');
     mysqli_stmt_bind_param($stmt, 's', $username);
     mysqli_stmt_execute($stmt);
@@ -44,17 +43,17 @@ function check_credentials($dbh, $username, $password) {
 }
 
 function update_credentials($dbh) {
-    if (!check_credentials($dbh, $_POST['previous_username'], $_POST['previous_password']))
+    if (!check_credentials($dbh, @$_POST['previous_username'], @$_POST['previous_password']))
         display_failure('Can not update credentials, both previous usernames and passwords need to be provided and they need to be valid.');
     $new_username = null;
     $new_password = null;
     $successful = [];
-    if (isset($_POST['new_username']) && !empty($_POST['new_username']))
+    if (!empty($_POST['new_username']))
         $new_username = $_POST['new_username'];
-    if (isset($_POST['new_password']) && !empty($_POST['new_password']))
+    if (!empty($_POST['new_password']))
         $new_password = $_POST['new_password'];
     if ($new_password !== null) {
-        if (!isset($_POST['new_password2']) || empty($_POST['new_password2']))
+        if (empty($_POST['new_password2']))
             display_failure('Need to provide new password twice');
         if ($new_password !== $_POST['new_password2'])
             display_failure('New password provided twice need to match');
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());
 }
+
diff --git a/mysql-php/output/3/1.png b/mysql-php/output/3/1.png
new file mode 100644
index 0000000..55e8f05
Binary files /dev/null and b/mysql-php/output/3/1.png differ
diff --git a/mysql-php/output/3/2.png b/mysql-php/output/3/2.png
new file mode 100644
index 0000000..f8ee1b7
Binary files /dev/null and b/mysql-php/output/3/2.png differ
-- 
cgit 1.4.1-2-gfad0