Building a Time Off System
Lets build a Paid Time off system.
This system needs the following requirements
- Create Access levels for users to manage application
- Allow user to control creation of departments
- Employee control with option to keep transfer history and comments about the employee.
- View detail Employee history by time-off taken
- Allow employee to change sector categorization
- Schedule of paid leave and unpaid leave
- Alarm for minimum amount of paid time off used in the year
- Reports by sector
We might not get all of this but we will start.
Creating our project
We are going to build this project with PHP and MySQL. We’ll be using Doctrine as the ORM and Twig as the template engine and some Bulma for styling.
Bootstrapping our application
Our bootstrap.php file has all the configurations we need to run our application. Lets check it out.
$connection = DriverManager::getConnection([
// mysql driver
'dbname' => 'timeoff',
'user' => '',
'password' => '',
'host' => '',
'driver' => 'pdo_mysql',
], $config);
// obtaining the entity manager
try {
$entityManager = new EntityManager($connection, $config);
} catch (\Doctrine\ORM\Exception\MissingMappingDriverImplementation $e) {
}
$loader = new \Twig\Loader\FilesystemLoader(__DIR__ . '/templates',__DIR__);
$twig = new \Twig\Environment($loader, [
'cache' => '/cache/',
'auto_reload' => true,
]);
// menu function
function getActions(){
return [
'home' => 'index.php',
'departments' => 'departments.php',
'employees' => 'employee.php',
'timeoff' => 'timeoff.php',
];
}
// render twig templates
function render($template, $data = [])
{
global $twig;
$template = $twig->load($template);
echo $template->render($data);
}The getAction and render functions allow us to create quick menu item and render the twig template respectively. We can add more as we need to.
Database Design
Currently we have five tables. Sectors, Leaves, Employees, Departments and Comments. We create classes to model/create/update these tables. Using doctrine ORM mapping we can create a class for Departments that looks like below
<?php
namespace src;
use Doctrine\ORM\Mapping as ORM;
#[ORM\Entity]
#[ORM\Table(name: 'departments')]
class Department
{
#[ORM\Id]
#[ORM\Column(type: 'integer')]
#[ORM\GeneratedValue]
private int|null $id;
#[ORM\Column(type: 'string')]
private string $name;
#[ORM\Column(type: 'string')]
private string $description;
public function getId(): int
{
return $this->id;
}
public function setId(int $id): void
{
$this->id = $id;
}
public function getName(): string
{
return $this->name;
}
public function setName(string $name): void
{
$this->name = $name;
}
public function getDescription(): string
{
return $this->description;
}
public function setDescription(string $description): void
{
$this->description = $description;
}
}Once we have our model complete we can run the command to create the table.
php vendor/bin/doctrine orm:schema-tool:create
# or update changes made
php vendor/bin/doctrine orm:schema-tool:updateWe can do this for the other tables. Lets look at the employee model, pay attention to the joins in this model.
<?php
namespace src;
use Doctrine\ORM\Mapping as ORM;
use Doctrine\ORM\Mapping\OneToOne;
use Doctrine\ORM\Mapping\JoinColumn;
#[ORM\Entity]
#[ORM\Table(name: 'employees')]
class Employee
{
#[ORM\Id]
#[ORM\Column(type: 'integer')]
#[ORM\GeneratedValue]
private int|null $id;
#[ORM\Column(type: 'string')]
private string $name;
#[ORM\Column(type: 'string')]
private string $email;
#[ORM\Column(type: 'integer')]
private string $department;
#[ORM\Column(type: 'integer')]
private int $sectorId;
// Join employee to department
#[ORM\ManyToOne(targetEntity: Department::class, inversedBy: 'employee')]
#[ORM\JoinColumn(name: 'department', referencedColumnName: 'id')]
private Department|null $departmentModel = null;
// Join employee to sector
#[ORM\ManyToOne(targetEntity: Sector::class)]
#[ORM\JoinColumn(name: 'sectorId', referencedColumnName: 'id')]
private Sector|null $sector = null;
public function getId(): int
{
return $this->id;
}
... more code
}Listing a model
Next we have a employee listing. We pull all our employees from the database.
$employees = $entityManager->getRepository("src\Employee")->findAll();
// get departments listing
$departments = $entityManager->getRepository("src\Department")->findAll();
render("employees.html.twig",
[
"employees" => $employees,
'departments' => $departments,
"actions" => getActions()
]);Listing the department and leave can be accomplished the same way as above.
In the comments view we can pull the comments by leaveId as shown below. We can also get a particular leave just by the id by using the find command.
// get comments by leave
$comments = $entityManager->getRepository("src\Comment")->findBy(["leaveId" => $id]);
// get leave by id
$leave = $entityManager->getRepository("src\Leave")->find($id);Saving an model
Lets save an employee. We enter a new employee in our form and we post the data and save it. Pretty straight forward.
So the twig template has
<form method="post" action="employee.php">
Name: <input type="text" name="name"/><br>
Email : <input type="text" name="email"/><br>
Department:
<select name="department">
{% for dept in departments %}
<option value="{{ dept.id }}">{{ dept.name }} ( {{ dept.description }} )</option>
{% endfor %}
</select>
<br>
<input type="submit" value="Save"/>
</form>Next lets look at the backend logic to save our new employee.
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// get post data
$name = $_POST['name'];
$email = $_POST['email'];
$department = $_POST['department'];
// create new employee
$employee = new Employee();
$employee->setName($name);
$employee->setEmail($email);
$employee->setDepartment($department);
// save employee
$entityManager->persist($employee);
$entityManager->flush();
}Project Layout
Our Project layout is simple we have a app directory, src directory and a templates directory.
Application features
Lets look at the different features the application will have.
Access levels
I didn’t implement this but we can add a users table and a roles table. Two additional tables that will allow use to manage access. Each role will only be able to take certain actions.
Employee control with option to keep transfer history and comments
To do this we allow for 3 things. We create a employee listing view. We create a leave table view and allow users to create time offs with comment options. We also allow each leave taken to be applied to a sector incase the employee sector changes. In all we can view the page as shown below.
Viewing the time off taken
Our time off listing is very straight forward a sample is shown below
<h3>Time off listing</h3>
<p>List of leave</p>
<table>
<tr>
<th>Employee</th>
<th>Start Date</th>
<th>End Date</th>
<th>Start Time</th>
<th>End Time</th>
<th>Reason</th>
<th>Avg Days</th>
<th>Comments</th>
<th>Action</th>
</tr>
{% for leave in leaves %}
<tr>
<td>{{ leave.employee.name }}</td>
<td>{{ leave.startDate }}</td>
<td>{{ leave.endDate }}</td>
<td>{{ leave.startTime }}</td>
<td>{{ leave.endTime }}</td>
<td>{{ leave.reason }}</td>
<td>{{ leave.days }}</td>
<td>{{ leave.CommentsCount }}</td>
<td><a href="comment.php?id={{ leave.id }}">Comment</a></td>
</tr>
{% endfor %}
</table>Basically we are just displaying the list of leave
What's import is that in our Leave Object we need to do the correct mapping to ensure we can access the correct data.
#[ORM\ManyToOne(targetEntity: Employee::class)]
#[JoinColumn(name: 'employeeId', referencedColumnName: 'id')]
protected Employee|null $employee= null;
/** @var Collection<int, Comment> An ArrayCollection of Comment objects. */
#[ORM\OneToMany(mappedBy: 'leave', targetEntity: Comment::class)]
private Collection $comments;Above we add a Many To One relationship to employee and a One To many Relationship to Comments. This allows use to view the employee name and view the comments count.
Control of departments
This is pretty straightforward we can just create a form to add departments and list these departments.
use src\Department;
include __DIR__ . "/../bootstrap.php";
// global $entityManager;
// if post request
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// get post data
$name = $_POST['name'];
$description = $_POST['description'];
// create new department
$department = new Department();
$department->setName($name);
$department->setDescription($description);
// save department
$entityManager->persist($department);
$entityManager->flush();
}
// get all departments
$departments = $entityManager->getRepository("src\Department")->findAll();
render("departments.html.twig",
[
"departments" => $departments,
"actions" => getActions()
]);Sector transfer option
You can create new sectors and assign and employee to a sector.
<?php
include __DIR__ . '/../bootstrap.php';
use src\Sector;
// if post request
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
$sector = new Sector();
$sector->setSector($_POST['sector']);
$entityManager->persist($sector);
$entityManager->flush();
}
$sectors = $entityManager->getRepository(Sector::class)->findAll();
render('sectors.html.twig', ['sectors' => $sectors, 'actions' => getActions()]);When you create a new employee you can assigned them a sector similar to adding a department
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
// get post data
$name = $_POST['name'];
$email = $_POST['email'];
$department = $_POST['department'];
$sector = $_POST['sector'];
// create new employee
$employee = new Employee();
$employee->setName($name);
$employee->setEmail($email);
// get one department
$departmentModel = $entityManager->find("src\Department", $department);
$employee->setDepartmentModel($departmentModel);
// get one sector
$sectorModel = $entityManager->find("src\Sector", $sector);
$employee->setSector($sectorModel);
// save employee
$entityManager->persist($employee);
$entityManager->flush();
}Employee history
For this we create a history view in the employees listing to show all the leave the particular employee has taken.
Now we can view all the leave for the particular employee. To do this we get the id from the url and pull the leaves and the particular employee by id.
$id = $_GET['id'];
if($id){
// get all leave by employee id
$leaves = $entityManager->getRepository("src\Leave")->findBy(["employeeId" => $id]);
// get employee by id
$employee = $entityManager->getRepository("src\Employee")->find($id);
render("history.html.twig", [
"id" => $id,
'leaves' => $leaves,
'employee' => $employee
]);
}We can see a preview of how this looks.
Adding Sector transfer option
We have no update pages but if we did we can easily allow the updating of an employee sector.
Reports by sector
I pulled all the leaves and did a count of how many leaves and days was taken by sector.
<?php
include __DIR__ . "/../bootstrap.php";
// get all leaves
$leaves = $entityManager->getRepository("src\Leave")->findAll();
$sectors = [];
$sectorCount = [];
$sectorDaysCount = [];
foreach ($leaves as $leave){
$sector = $leave->getEmployee()->getSector()->getSector();
// var_dump($sector);
if(!in_array($sector, $sectors)){
$sectors[] = $sector;
$sectorCount[$sector] = 1;
$sectorDaysCount[$sector] = $leave->getDays();
}else{
$sectorCount[$sector] = $sectorCount[$sector] + 1;
$sectorDaysCount[$sector] = $sectorDaysCount[$sector] + $leave->getDays();
}
}
render("reports.html.twig", [
"actions" => getActions(),
"sectorCount" => $sectorCount,
"sectorDaysCount" => $sectorDaysCount
]);On the report.php view you can see how this looks.
All done
Well that's it. View it here https://github.com/wftutorials/paidtimeoff
