1 files changed, 107 insertions, 0 deletions
diff --git a/mysql-php/code/a4.php b/mysql-php/code/a4.php
new file mode 100644
index 0000000..baa1b45
--- /dev/null
+++ b/mysql-php/code/a4.php
@@ -0,0 +1,107 @@
+<?php
+declare(strict_types=1);
+error_reporting(E_ALL);
+
+function connect_to_database() {
+    mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
+    $dbh = new mysqli('localhost', 'root', '');
+    $dbh->set_charset('utf8mb4');
+    $dbh->query('CREATE DATABASE IF NOT EXISTS COMPANY_DB');
+    $dbh->select_db('COMPANY_DB');
+    $dbh->query('CREATE TABLE IF NOT EXISTS `SALARY` (`EMP_ID` INT PRIMARY KEY AUTO_INCREMENT, `EMP_NAME` VARCHAR(255), `BASIC_PAY` DOUBLE NOT NULL, `HRA` DOUBLE, `DA` DOUBLE, `PROFESSIONAL_TAX` DOUBLE)');
+    $result = $dbh->query('SELECT COUNT(*) AS count FROM `SALARY`');
+    $row = $result->fetch_assoc();
+    if ($row['count'] == 0) {
+        $dbh->query('INSERT INTO `SALARY` (`EMP_NAME`, `BASIC_PAY`) VALUES
+            ("Yu Jae-hoon", 70000),
+            ("Meng Cilin", 65000),
+            ("Mike Smith", 60000),
+            ("Natsukashii Morimoto", 55000)
+        ');
+    }
+    return $dbh;
+}
+
+
+function html_prologue($title) {
+?><!doctype html>
+<meta charset="utf-8">
+<title><?php echo $title; ?></title>
+<style>
+body { font-family: sans-serif; font-size: 1.3rem; }
+h1 { font-size: 2rem; font-weight: 500; }
+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.salary td:nth-child(2) { text-align: end; }
+</style>
+<?php
+}
+
+function display_failure($reason) {
+    html_prologue('Operation failure');
+    ?>
+<h2>Operation failed</h2>
+<p>Reason: <?php echo $reason; ?></p>
+<?php
+    die();
+}
+
+function display_records($dbh) {
+    html_prologue('Employee Salary Records');
+    $result = $dbh->query('SELECT * FROM `SALARY`');
+    ?>
+<h1>Employee Salary Records</h1>
+<table class="salary">
+<tr><th>Employee Name</th><th>Basic Pay</th><th>HRA</th><th>DA</th><th>Professional Tax</th></tr>
+<?php while ($row = $result->fetch_assoc()) {
+    echo '<tr><td>', $row['EMP_NAME'], '</td><td>', number_format(+$row['BASIC_PAY'], 2), '</td><td>', number_format(+$row['HRA'], 2), '</td><td>', number_format(+$row['DA'], 2), '</td><td>', number_format(+$row['PROFESSIONAL_TAX'], 2), '</td></tr>';
+} ?>
+</table>
+<?php
+}
+
+function show_salary_form() {
+    html_prologue('Enter Salary Components');
+    ?>
+<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>">
+<h1>Enter Salary Components</h1>
+<table class="noborder">
+<tr><td><label for="hra_percentage">HRA Percentage:</label></td><td><input type="number" name="hra_percentage" id="hra_percentage" step="0.01">%</td></tr>
+<tr><td><label for="da_percentage">DA Percentage:</label></td><td><input type="number" name="da_percentage" id="da_percentage" step="0.01">%</td></tr>
+<tr><td><label for="professional_tax">Professional Tax:</label></td><td><input type="number" name="professional_tax" id="professional_tax" step="0.01">%</td></tr>
+<tr><td colspan="2"><input type="submit" value="Calculate Salary"></td></tr>
+</table>
+</form>
+    <?php
+}
+
+function calculate_salary($dbh) {
+    $hra_percentage = $_POST['hra_percentage'] ?? 0;
+    $da_percentage = $_POST['da_percentage'] ?? 0;
+    $professional_tax = $_POST['professional_tax'] ?? 0;
+    $result = $dbh->query('SELECT * FROM `SALARY`');
+    while ($row = $result->fetch_assoc()) {
+        $hra = $row['BASIC_PAY'] * ($hra_percentage / 100);
+        $da = $row['BASIC_PAY'] * ($da_percentage / 100);
+        $ptax = $row['BASIC_PAY'] * ($professional_tax / 100);
+        $stmt = $dbh->prepare('UPDATE `SALARY` SET `HRA` = ?, `DA` = ?, `PROFESSIONAL_TAX` = ? WHERE `EMP_ID` = ?');
+        $stmt->bind_param('dddi', $hra, $da, $ptax, $row['EMP_ID']);
+        $stmt->execute();
+    }
+    display_records($dbh);
+}
+
+if ($_SERVER['REQUEST_METHOD'] === 'POST') {
+    try {
+        $dbh = connect_to_database();
+        calculate_salary($dbh);
+        $dbh->close();
+    } catch (mysqli_sql_exception $e) {
+        display_failure($e->getMessage());
+    }
+} else {
+    show_salary_form();
+}