PHP MYSQL Leave Management

0-PHP-LEAVE.MD

PHP MYSQL LEAVE MANAGEMENT SYSTEM

https://code-boxx.com/leave-management-system-php-mysql/

NOTES

1. Create a database and import 1-database.sql.
2. Change the database settings in 2-lib-leave.php to your own.
3. Run x-dummy.php to create dummy data entries.
4. Access 4a-apply.php in the browser.

LICENSE

Copyright by Code Boxx

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

1-database.sql

-- (A) HOLIDAYS
CREATE TABLE `holidays` (
  `holiday_id` bigint(20) NOT NULL,
  `holiday_name` varchar(255) NOT NULL,
  `holiday_date` date NOT NULL,
  `holiday_half` tinyint(1) NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `holidays`
  ADD PRIMARY KEY (`holiday_id`),
  ADD KEY `holiday_date` (`holiday_date`);

ALTER TABLE `holidays`
  MODIFY `holiday_id` bigint(20) NOT NULL AUTO_INCREMENT;

-- (B) LEAVE ENTITLED
CREATE TABLE `leave_entitled` (
  `user_id` bigint(20) NOT NULL,
  `leave_type` varchar(1) NOT NULL,
  `leave_days` decimal(4,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `leave_entitled`
  ADD PRIMARY KEY (`user_id`,`leave_type`);

-- (C) LEAVE TAKEN
CREATE TABLE `leave_taken` (
  `leave_id` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL,
  `leave_type` varchar(1) NOT NULL,
  `leave_status` varchar(1) NOT NULL DEFAULT 'P',
  `leave_from` date NOT NULL,
  `leave_to` date NOT NULL,
  `leave_days` decimal(4,1) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `leave_taken`
  ADD PRIMARY KEY (`leave_id`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `leave_type` (`leave_type`),
  ADD KEY `leave_status` (`leave_status`),
  ADD KEY `leave_from` (`leave_from`),
  ADD KEY `leave_to` (`leave_to`);

ALTER TABLE `leave_taken`
  MODIFY `leave_id` bigint(20) NOT NULL AUTO_INCREMENT;

-- (D) LEAVE TAKEN INDIVIDUAL DAYS
CREATE TABLE `leave_taken_days` (
  `leave_id` bigint(20) NOT NULL,
  `leave_day` date NOT NULL,
  `leave_half` varchar(1) NOT NULL DEFAULT 'F'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE `leave_taken_days`
  ADD PRIMARY KEY (`leave_id`,`leave_day`);

2-lib-leave.php

<?php
class Leave {
  // (A) CONSTRUCTOR - CONNECT TO DATABASE
  private $pdo = null;
  private $stmt = null;
  public $error = "";
  function __construct () {
    $this->pdo = new PDO(
      "mysql:host=".DB_HOST.";dbname=".DB_NAME.";charset=".DB_CHARSET,
      DB_USER, DB_PASSWORD, [
      PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
      PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
    ]);
  }

  // (B) DESTRUCTOR - CLOSE DATABASE CONNECTION
  function __destruct () {
    if ($this->stmt!==null) { $this->stmt = null; }
    if ($this->pdo!==null) { $this->pdo = null; }
  }

  // (C) HELPER FUNCTION - EXECUTE SQL QUERY
  function query ($sql, $data=null) : void {
    $this->stmt = $this->pdo->prepare($sql);
    $this->stmt->execute($data);
  }

  // (D) SET HOLIDAY
  function holiday ($name, $date, $half=0, $id=null) {
    if ($id==null) {
      $sql = "INSERT INTO `holidays` (`holiday_name`, `holiday_date`, `holiday_half`) VALUES (?, ?, ?)";
      $data = [$name, $date, $half];
    } else {
      $sql = "UPDATE `holidays` SET `holiday_name`=?, `holiday_date`=?, `holiday_half`=? WHERE `holiday_id`=?";
      $data = [$name, $date, $half, $id];
    }
    $this->query($sql, $data);
    return true;
  }

  // (E) SET ENTITLED LEAVE
  function entitle ($id, $type, $days) {
    $this->query(
      "REPLACE INTO `leave_entitled`  (`user_id`, `leave_type`, `leave_days`)  VALUES (?, ?, ?)",
      [$id, $type, $days]
    );
    return true;
  }
  
  // (F) CHECK GIVEN PERIOD
  function check ($id, $from, $to) {
    // (F1) CHECK START & END DATES
    $start = strtotime($from);
    $end = strtotime($to);
    if ($start<strtotime(LEAVE_MIN)) { return ["error"=>"Earliest leave is ".LEAVE_MIN]; }
    if ($end < $start) { return ["error"=>"End date is earlier than start date?"]; }

    // (F2) DATE RANGE
    $days = [];
    for ($unix=$start; $unix<=$end; $unix+=86400) {
      $weekend = date("N", $unix) > 5;
      $days[date("Y-m-d", $unix)] = [
        "a" => $weekend ? 0 : 1,
        "n" => $weekend ? "Weekend" : ""
      ];
    }

    // (F3) HOLIDAYS
    $this->query(
      "SELECT * FROM `holidays` WHERE `holiday_date` BETWEEN ? AND ?",
      [$from, $to]
    );
    while ($r = $this->stmt->fetch()) {
      $days[$r["holiday_date"]] = [
        "a" => $r["holiday_half"]==1 ? 2 : 0,
        "n" => $r["holiday_name"]
      ];
    }

    // (F4) GET LEAVE ALREADY TAKEN WITHIN PERIOD
    $this->query(
      "SELECT d.* FROM `leave_taken_days` d
       LEFT JOIN `leave_taken` t 
       ON (d.`leave_id`=t.`leave_id` AND t.`user_id`=?)
       WHERE d.`leave_day` BETWEEN ? AND ? AND  t.`leave_status`!='D'",
      [$id, $from, $to]
    );
    while ($r = $this->stmt->fetch()) {
      $days[$r["leave_day"]] = [
        "a" => 0,
        "n" => "Already taken leave on this day"
      ];
    }

    // (F5) RESULTS
    // $days[YYYY-MM-DD] = ["a"=>CODE BELOW, "n"=>NOTES];
    //  2 : Can apply half/none
    //  1 : Can apply full/half/none
    //  0 : No need to apply leave
    return $days;
  }

  // (G) APPLY LEAVE
  function apply ($id, $type, $days) {
    // (G1) APPLIED LEAVE DAYS
    $days = json_decode($days, true);
    $from = array_key_first($days);
    $to = array_key_last($days);
    $year = substr($from, 0, 4);
    $total = 0;
    foreach ($days as $day=>$half) { $total += $half=="F" ? 1 : 0.5 ; }

    // (G2) GET ENTITLED LEAVE
    $this->query(
      "SELECT `leave_days` FROM `leave_entitled` WHERE `user_id`=? AND `leave_type`=?",
      [$id, $type]
    );
    $entitled = $this->stmt->fetchColumn();
    if (!is_numeric($entitled)) {
      $this->error = "Insufficient leave days.";
      return false;
    }
    
    // (G3) CALCULATE REMAINING LEAVE
    $this->query(
      "SELECT SUM(`leave_days`) FROM `leave_taken` 
       WHERE `user_id`=? AND `leave_type`=? 
       AND `leave_status`!='D' AND `leave_from` BETWEEN ? AND ?",
      [$id, $type, "$year-01-01", "$year-12-31"]
    );
    $taken = $this->stmt->fetchColumn();
    if (is_numeric($taken)) { $entitled -= $taken; }
    if ($total > $entitled) {
      $this->error = "Insufficient leave days.";
      return false;
    }

    // (G4) AUTO-COMMIT OFF
    $this->pdo->beginTransaction();

    // (G5) CREATE "MAIN LEAVE ENTRY"
    $this->query(
      "INSERT INTO `leave_taken`
      (`user_id`, `leave_type`, `leave_from`, `leave_to`, `leave_days`)
      VALUES (?, ?, ?, ?, ?)",
      [$id, $type, $from, $to, $total]
    );
    $id = $this->pdo->lastInsertId();
    
    // (G6) LEAVE DAYS
    $sql = "INSERT INTO `leave_taken_days` (`leave_id`, `leave_day`, `leave_half`) VALUES ";
    $data = [];
    foreach ($days as $d=>$h) {
      $sql .= "(?,?,?),";
      $data[] = $id; $data[] = $d; $data[] = $h;
    }
    $this->query(substr($sql,0,-1).";", $data);

    // (G7) DONE
    $this->pdo->commit();
    return true;
  }
}

// (H) DATABASE SETTINGS - CHANGE TO YOUR OWN!
define("DB_HOST", "localhost");
define("DB_NAME", "test");
define("DB_CHARSET", "utf8mb4");
define("DB_USER", "root");
define("DB_PASSWORD", "");

// (I) DEFINITIONS
define("LEAVE_MIN", date("Y-m-d"));
define("LEAVE_TYPES", [
  "P" => "Paid",
  "U" => "Unpaid",
  "M" => "Medical"
]);
define("LEAVE_STATUS", [
  "P" => "Pending",
  "A" => "Approved",
  "D" => "Denied"
]);

// (J) NEW LEAVE OBJECT
$_LEAVE = new Leave();

3-ajax-leave.php

<?php
// (A) LOAD LIBRARY + FIXED USER ID
require "2-lib-leave.php";
$user = 999;

if (isset($_POST["req"])) { switch ($_POST["req"]) {
  // (B) INVALID REQUEST
  default: echo "Invalid request"; break;

  // (C) CHECK SELECTED PERIOD
  case "check":
    echo json_encode($_LEAVE->check($user, $_POST["from"], $_POST["to"]));
    break;

  // (D) APPLY FOR LEAVE
  case "apply";
    echo $_LEAVE->apply($user, $_POST["type"], $_POST["days"])
      ? "OK" : $_LEAVE->error ;
    break;
}}

4a-apply.php

<!DOCTYPE html>
<html>
  <head>
    <title>Apply Leave</title>
    <link rel="stylesheet" href="x-apply.css">
    <script src="4b-apply.js"></script>
  </head>
  <body>
    <?php
    // (A) LOAD PHP LIBRARY
    require "2-lib-leave.php";

    // (B) HTML ?>
    <!-- (B1) SELECT LEAVE TYPE + PERIOD -->
    <form id="form_period" onsubmit="return apply.check()">
      <label>Leave Type</label>
      <select id="leave_type"><?php foreach (LEAVE_TYPES as $c=>$t) {
        echo "<option value='$c'>$t</option>";
      } ?></select>
      <label>Start Date</label>
      <input type="date" id="leave_from" min="<?=LEAVE_MIN?>" value="<?=LEAVE_MIN?>" required>
      <label>End Date</label>
      <input type="date" id="leave_to" min="<?=LEAVE_MIN?>" value="<?=date("Y-m-d", strtotime(LEAVE_MIN)+604800)?>" required>
      <input type="submit" value="Next">
    </form>

    <!-- (B2) SELECT INDIVIDUAL DAYS -->
    <form id="form_days" class="hide" onsubmit="return apply.go()">
      <div id="form_days_list"></div>
      <div id="form_days_total">
        <div class="rowDate">Total Days: </div>
        <div id="form_days_count"></div>
      </div>
      <input type="submit" value="Apply">
      <input type="button" value="Cancel" onclick="apply.toggle(false)">
    </form>
  </body>
</html>

4b-apply.js

var apply = {
  // (A) HTML INTERFACE - TOGGLE SELECT PERIOD/DAY
  toggle : direction => {
    if (direction) {
      document.getElementById("form_period").classList.add("hide");
      document.getElementById("form_days").classList.remove("hide");
    } else {
      document.getElementById("form_days").classList.add("hide");
      document.getElementById("form_period").classList.remove("hide");
    }
  },

  // (B) CHECK SELECTED PERIOD
  check : () => {
    // (B1) FROM & TO DATES
    let from = document.getElementById("leave_from").value,
        to = document.getElementById("leave_to").value;
    if (new Date(from) > new Date(to)) {
      alert("Start date cannot be later than end date");
    }

    // (B2) FORM DATA
    let data = new FormData();
    data.append("req", "check");
    data.append("type", document.getElementById("leave_type").value);
    data.append("from", from);
    data.append("to", to);

    // (B3) AJAX CHECK SELECTED PERIOD
    fetch ("3-ajax-leave.php", { method: "post", body: data })
    .then(res => res.json())
    .then(days => {
      // (B3-1) ERROR!
      if (days.error) {
        alert(days.error);
        return false;
      }

      // (B3-2) RESET "SELECT HALF/FULL DAY" FORM
      let row, wrap = document.getElementById("form_days_list");
      wrap.innerHTML = "";

      // (B3-3) DRAW "SELECT HALF/FULL DAY" FORM
      for (let [d,s] of Object.entries(days)) {
        row = document.createElement("div");
        row.className = "row";
        row.innerHTML = 
        `<div class="rowDate">
           <div>${d}</div>
           <small>${s.n ? s.n : ""}</small>
         </div>
         <select class="rowHalf"></select>`;
        wrap.appendChild(row);

        row = row.getElementsByTagName("select")[0];
        row.onchange = apply.total;
        row.dataset.date = d;
        if (s["a"]==0) {
          row.innerHTML = `<option value="">NA</option>`;
          row.disabled = true;
        } else if (s["a"]==1) {
          row.innerHTML = `<option value="F">Full Day</option>
          <option value="P">PM</option>
          <option value="A">AM</option>
          <option value="">No Leave</option>`;
        } else {
          row.innerHTML = `<option value="P">PM</option>
          <option value="A">AM</option>
          <option value="">No Leave</option>`;
        }
      }

      // (B3-4) TOGGLE HTML FORMS
      apply.total();
      apply.toggle(true);
    });
    return false;
  },

  // (C) CALCULATE TOTAL DAYS
  total : () => {
    let days = 0;
    for (let d of document.querySelectorAll("#form_days_list select")) {
      if (d.value=="") { continue; }
      else if (d.value=="F") { days++; }
      else { days += 0.5; }
    }
    document.getElementById("form_days_count").innerHTML = days;
  },

  // (D) APPLY LEAVE
  go : () => {
    // (D1) GET DAYS
    let days = {};
    for (let d of document.querySelectorAll("#form_days_list select")) {
      if (d.value=="") { continue; }
      days[d.dataset.date] = d.value;
    }
    if (Object.keys(days).length==0) {
      alert("No dates selected!");
      return false;
    }

    // (D2) FORM DATA
    let data = new FormData();
    data.append("req", "apply");
    data.append("type", document.getElementById("leave_type").value);
    data.append("days", JSON.stringify(days));

    // (D3) AJAX SUBMIT
    fetch ("3-ajax-leave.php", { method: "post", body: data })
    .then(res => res.text())
    .then(res => {
      alert(res);
      console.log("@TODO - WHATEVER YOU WANT TO DO NEXT");
      // APPROVAL PROCESS
      // EMAIL CONFIRMATION
      // REDIRECT TO SOMEWHERE ELSE
    });
    return false;
  }
};

x-apply.css

/* (A) WHOLE PAGE */
* {
  font-family: arial, sans-serif;
  box-sizing: border-box;
}
body { background: #f2f2f2; }
.hide { display: none; }

/* (B) FORM */
form {
  max-width: 400px;
  padding: 20px;
  border: 1px solid #ccc;
  background: #fff;
}
label, input, select {
  display: block;
  width: 100%;
}
label {
  color: #7e7e7e;
  margin: 10px 0;
}
input, select { padding: 10px; }
button, input[type=button], input[type=submit] {
  margin-top: 10px;
  border: 0;
  color: #fff;
  background: #7e0000;
  cursor: pointer;
}

/* (C) DATE ROWS */
#form_days_list .row ,#form_days_total {
  display: flex;
  margin-top: 10px;
  background: #f9f9f9;
  border: 1px solid #e5e5e5;
}
.rowDate, #form_days_count {
  padding: 10px;
  font-weight: 700;
}
.rowDate { flex-grow: 1; }
#form_days_list select {
  width: auto;
  border: 0;
  background: 0;
}

x-dummy.php

<?php
// (A) LOAD LIBRARY
require "2-lib-leave.php";

// (B) ADD HOLIDAYS
echo $_LEAVE->holiday("Test Holiday Eve", date("Y-m-d", strtotime("+1 day")), 1) ? "OK" : $_LEAVE->error ;
echo $_LEAVE->holiday("Test Holiday", date("Y-m-d", strtotime("+2 days"))) ? "OK" : $_LEAVE->error ;

// (C) ENTITLE LEAVE
echo $_LEAVE->entitle(999, "M", 10) ? "OK" : $_LEAVE->error ;
echo $_LEAVE->entitle(999, "P", 11) ? "OK" : $_LEAVE->error ;
echo $_LEAVE->entitle(999, "U", 12) ? "OK" : $_LEAVE->error ;