The timekeeping system

A new project

As in the previous project, we are going to assume this project is going to be deployed in an intranet setting so we won’t have to worry about security and huge numbers of users accessing data at the same time. Most security concerns are beyond the control of a web app itself anyway, like fronting with an nGinx or Apache proxy server, hiding behind a firewall, setting up a CA certificate like the free Let’s Encrypt for HTTPS encryption, hardening MySQL, etc.

So create a new project named loremtime.

C:\Users\Owner>cd \vibeprojects
C:\vibeprojects>dub init loremtime -t vibe.d
Package recipe format (sdl/json) [json]:
Name [loremtime]:
Description [A simple vibe.d server application.]:
Author name [Owner]:
License [proprietary]:
Copyright string [Copyright 2023, Owner]:
Add dependency (leave empty to skip) []:
Success created empty project in C:\vibeprojects\loremtime
Package successfully created in loremtime
C:\vibeprojects>cd loremtime
And add the mysql-native library to the project.
C:\vibeprojects\loremtime>dub add mysql-native
Adding dependency mysql-native ~>3.2.2
C:\vibeprojects\loremtime>

Then, using MySQL Workbench, create a new database named loremdb. After that, we are ready to build the timekeeping system for the Lorem Ipsum company.

The schema

The timekeeping system needs to record the time-in and time-out actions of the employees, like a timecard, so we need a timecard model.

Then, after every salary period, in our case weekly, a timesheet is produced summarizing the number of hours employees worked for the week, so we need a timesheet model.

Here is the structure for the timecard:

Id – the record id
Empid – the employee id
Fullname – the employee full name
Timein – the time the employee punched in for the day
Timeout – the time the employee punched out
Hours – the number of hours between time in and time out, which computed after the punch out

Here is the structure for the timesheet:

Id – the record id
Period – the salary period (or week)
Empid – the employee id
Fullname – the employee full name
Hours – the accrued hours for the week

The reason the empid and fullname are included is to eliminate the need for the timesheet to look up the employee number and names of employees from the employees table.

This time we will create the administrators table. So we need an admins model, like this:

Id – the record id
Email – the admin email address
Password – the admin password

We already have the employees table, so that’s covered.

The timesheet will simply extract and summarize data from the timecards. The timecards will become permanent records while the timesheet will be overwritten every time it is generated. The timecards can be edited but the timesheet does not need to be edited as it depends on the timecards.

The base model

All of the models will be connecting to the same database with the same URL for the connections. To reduce code duplication, we will create a base model.

Create the base model in source\basemodel.d:

module basemodel;
import mysql;
class BaseModel
{
  string realm = "The Lorem Ipsum Company";
  Connection conn;
  
  this()
  {
    string url = "host=localhost;port=3306;user=owner;pwd=qwerty;db=loremdb";
    conn = new Connection(url);
    scope(exit) conn.close;
  }
}

Our base model connects to the database at its creation, which will be inherited by all of the models. Of course, you replace the user and password with your own.

The employee model

Edit source\empmodel.d with this contents. The highlighted lines are only for emphasis; there are some small changes here and there so better copy the whole thing:

module empmodel;
import mysql;
import std.conv;
import std.array;
import basemodel;

struct Employee
{
  int id; //row id or record id
  string empid; //employee number
  string email; //email address
  string passw; //password
  string fname; //first name
  string lname; //last name
  string phone; //phone number
  string photo; //ID photo
  string deprt; //department
  string payrate; //salary grade
  string street; //street address
  string city; //city name
  string province; //province name
  string postcode; //postal code
}

string[] departments =
[
  "Management",
  "Accounting",
  "Production",
  "Maintenance",
  "Shipping",
  "Purchasing",
  "IT Services",
  "HR Services",
  "Marketing"
];

string[][] provinces =
[
  ["AB", "Alberta"],
  ["BC", "British Columbia"],
  ["MB", "Manitoba"],
  ["NB", "New Brunswick"],
  ["NL", "Newfoundland and Labrador"],
  ["NS", "Nova Scotia"],
  ["NT", "Northwest Territories"],
  ["NU", "Nunavut"],
  ["ON", "Ontario"],
  ["PE", "Prince Edward Island"],
  ["QC", "Quebec"],
  ["SK", "Saskatchewan"],
  ["YT", "Yukon Territory"]
];

class EmployeeModel : BaseModel
{
  void createTable()
  {
    string sql = "drop table if exists employees";
    conn.exec(sql);
    sql =
      "create table employees
      (
        id int auto_increment primary key,
        empid char(4) not null unique,
        email varchar(50) not null,
        passw varchar(255) not null,
        fname varchar(25) not null,
        lname varchar(25) not null,
        phone varchar(15) default '123-456-7890',
        photo varchar(50) default 'none provided',
        deprt varchar(30) default 'not assigned yet',
        payrate char(4) default 'B400',
        street varchar(50) default '123 First Street',
        city varchar(30) default 'Toronto',
        province char(2) default 'ON',
        postcode char(7) default 'Z9Z 9Z9',
        created timestamp default current_timestamp,
        updated timestamp on update current_timestamp
      )";
    conn.exec(sql);
  }
  
  void createPayrates()
  {
    int[string] payrates =
    [
      "A100":100, "A200":75, "A300":50, "A400":40,
      "B100":30, "B200":29, "B300":28, "B400":27,
      "C100":26, "C200":25, "C300":24, "C400":23,
      "D100":22, "D200":21, "D300":20, "D400":19,
      "E100":18, "E200":17, "E300":16, "E400":15,
      "F100":14, "F200":13, "F300":12, "F400":11
    ];
    string sql = "drop table if exists payrates";
    conn.exec(sql);
    sql =
      "create table payrates
      (
        id int auto_increment primary key,
        payrate char(4) not null,
        hourly int not null
      )";
    conn.exec(sql);
    foreach(k,v; payrates)
    {
      sql =
        "insert into payrates(payrate, hourly)
        values('" ~ k ~ "', " ~ to!string(v) ~ ")" ;
      conn.exec(sql);
    }
  }
  
  string[] getPayrates()
  {
    string sql = "select payrate from payrates order by payrate";
    Row[] rows = conn.query(sql).array;
    string[] rates;
    foreach(row; rows) rates ~= to!string(row[0]);
    return rates;
  }
  
  void addEmployee(Employee e)
  {
    import vibe.http.auth.digest_auth;
    e.passw = createDigestPassword(realm, e.email, e.passw);
    string sql =
      "insert into employees
      (
        empid,
        email, passw, fname, lname,
        phone, photo, deprt, payrate,
        street, city, province, postcode
      )
      values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs
    (
      to!string(e.empid),
      to!string(e.email),
      to!string(e.passw),
      to!string(e.fname),
      to!string(e.lname),
      to!string(e.phone),
      to!string(e.photo),
      to!string(e.deprt),
      to!string(e.payrate),
      to!string(e.street),
      to!string(e.city),
      to!string(e.province),
      to!string(e.postcode)
    );
    conn.exec(pstmt);
  }
  
  Employee[] getEmployees()
  {
    Employee[] emps;
    string sql = "select * from employees";
    Row[] rows = conn.query(sql).array;
    if(rows.length == 0) return emps;
    foreach(row; rows) emps ~= prepareEmployee(row);
    return emps;
  }
  
  Employee prepareEmployee(Row row)
  {
    Employee e;
    e.id = to!int(to!string(row[0]));
    e.empid = to!string(row[1]);
    e.email = to!string(row[2]);
    e.passw = to!string(row[3]);
    e.fname = to!string(row[4]);
    e.lname = to!string(row[5]);
    e.phone = to!string(row[6]);
    e.photo = to!string(row[7]);
    e.deprt = to!string(row[8]);
    e.payrate = to!string(row[9]);
    e.street = to!string(row[10]);
    e.city = to!string(row[11]);
    e.province = to!string(row[12]);
    e.postcode = to!string(row[13]);
    return e;
  }
  
  Employee getEmployee(int id)
  {
    string sql = "select * from employees where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    Employee e;
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return e;
    return prepareEmployee(rows[0]);
  }
  
  Employee getEmployee(string empid)
  {
    string sql = "select * from employees where empid=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid);
    Employee e;
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return e;
    return prepareEmployee(rows[0]);
  }
  void editEmployee(Employee e, bool newpass)
  {
    if(newpass) // if password was changed, encrypt it
    {
      import vibe.http.auth.digest_auth;
      e.passw = createDigestPassword(realm, e.email, e.passw);
    }
    string sql =
      "update employees set empid=?,
      email=?, passw=?, fname=?, lname=?,
      phone=?, photo=?, deprt=?, payrate=?,
      street=?, city=?, province=?, postcode=?
      where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs
    (
      to!string(e.empid),
      to!string(e.email),
      to!string(e.passw),
      to!string(e.fname),
      to!string(e.lname),
      to!string(e.phone),
      to!string(e.photo),
      to!string(e.deprt),
      to!string(e.payrate),
      to!string(e.street),
      to!string(e.city),
      to!string(e.province),
      to!string(e.postcode),
      to!int(to!string(e.id))
    );
    conn.exec(pstmt);
  }
  
  void deleteEmployee(int id)
  {
    string sql = "delete from employees where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    conn.exec(pstmt);
  }
  
  Employee findEmployee(string first, string last)
  {
    Employee e;
    string sql = "select * from employees where upper(fname)=? and upper(lname)=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(first, last);
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return e;
    return prepareEmployee(rows[0]);
  }
}

We added the line

import basemodel;

near the top to make the BaseModel class visible.

The line

class EmployeeModel : BaseModel

means EmployeeModel inherits from BaseModel, which is another way of saying the EmployeeModel class is derived from the base class BaseModel, or EmployeeModel is a subclass of BaseModel.

The line

int[string] payrates =

means create an associative array of integers with strings as keys.

We created this associative array:

    int[string] payrates =
    [
      "A100":100, "A200":75, "A300":50, "A400":40,
      "B100":30, "B200":29, "B300":28, "B400":27,
      "C100":26, "C200":25, "C300":24, "C400":23,
      "D100":22, "D200":21, "D300":20, "D400":19,
      "E100":18, "E200":17, "E300":16, "E400":15,
      "F100":14, "F200":13, "F300":12, "F400":11
    ];

So when we say

payrates[“A300”]

it will give us the value of 50.

The line

foreach(k,v; payrates)

can be read as “for each key and value pair in payrates, do the following”. Of course, “k” and “v” are random variable names; you can name them “rate” and “pay” and they will have the same meaning:

foreach(rate,pay; payrates)

Through that construct, we were able to populate the payrates table with data from the payrates associative array.

The timecard model

Create source\cardmodel.d with this contents:

module cardmodel;
import mysql;
import std.array;
import std.conv;
import basemodel;

struct Timecard
{
  int id;
  string empid;
  string fullname;
  string timein;
  string timeout;
  float hours;
}

class TimecardModel : BaseModel
{
  void createTable()
  {
    string sql = "drop table if exists timecards";
    conn.exec(sql);
    sql =
      "create table timecards
      (
        id int auto_increment primary key,
        empid char(4) not null references employees(empid),
        fullname varchar(50) not null,
        timein datetime not null default current_timestamp,
        timeout datetime,
        hours float default 0.0
      )";
    conn.exec(sql);
  }

Here we also indicated that TimecardModel is also derived from BaseModel.

The timesheet model

Create source\sheetmodel.d:

module sheetmodel;
import mysql;
import std.array;
import std.conv;
import basemodel;

struct Timesheet
{
  int id;
  int period;
  string empid;
  string fullname;
  float hours;
}

class TimesheetModel : BaseModel
{
  void createTable()
  {
    string sql = "drop table if exists timesheets";
    conn.exec(sql);
    sql =
      "create table timesheets
      (
        id int auto_increment primary key,
        period int not null,
        empid char(4) not null,
        fullname varchar(50) not null,
        hours float
      )";
    conn.exec(sql);
  }
}

And we also indicated here that TimesheetModel is derived from BaseModel.

The administrators model

We are adding an administrators model so in the future we can simply provide a facility to add, edit and delete administrators.

Create source\adminmodel.d:

module adminmodel;
import mysql;
import std.array;
import vibe.http.auth.digest_auth;
import basemodel;

struct Admin
{
  int id;
  string email;
  string passw;
}

class AdminModel : BaseModel
{
  void createTable()
  {
    conn.exec("drop table if exists admins");
    string sql =
      "create table admins
      (
        id int auto_increment primary key,
        email varchar(30) not null unique,
        passw varchar(255) not null
      )";
    conn.exec(sql);
    string[] emails =
    [
      "[e-mail]",
      "[e-mail]",
      "[e-mail]",
      "[e-mail]",
      "[e-mail]",
      "[e-mail]"
    ];
    foreach(email; emails)
    {
      string pword = createDigestPassword(realm, email, "secret");
      sql = "insert into admins(email, passw) values ('" ~ email ~ "','" ~ pword ~ "')";
      conn.exec(sql);
    }
  }
  
  bool getAdmin(string email, string pword)
  {
    string password = createDigestPassword(realm, email, pword);
    string sql = "select * from admins where email=? and passw=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(email, password);
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return false;
    return true;
  }
}

We added the getAdmin() method as we already used this earlier.

The base controller

We are going to make several controllers, most of them paired to a model. This will make debugging files easier as well as follow the notion of separation of concerns. Besides, having all the business logic code in one controller file will make the file big and unwieldy.

To avoid duplication of code, we are going to make a base controller class that hosts the common code and make all the other controllers inherit from it. We will call it BaseController.

Here is source\basecontrol.d:

module basecontrol;
import vibe.vibe;
import empmodel;
import cardmodel;
import sheetmodel;
import adminmodel;

struct User
{
  bool loggedIn;
  string email;
}

class BaseController
{
  protected SessionVar!(User, "user") m_user;
  protected enum auth = before!ensureAuth("_authUser");
  protected EmployeeModel empModel;
  protected TimecardModel cardModel;
  protected TimesheetModel sheetModel;
  protected AdminModel adminModel;
  
  this()
  {
    empModel = new EmployeeModel;
    cardModel = new TimecardModel;
    sheetModel = new TimesheetModel;
    adminModel = new AdminModel;
  }
  
  string ensureAuth(HTTPServerRequest req, HTTPServerResponse res)
  {
    if(!m_user.loggedIn) redirect("#login");
    return m_user.email;
  }
  mixin PrivateAccessProxy;
  
  string getToday()
  {
    import std.datetime.date;
    auto time = Clock.currTime;
    return to!string(Date(time.year, time.month, time.day));
  }
}

There are two common accessibility attributes for variables in D: public and private.

A public variable is accessible from the class and from outside of the class and subclasses.

A private variable is accessible only to the class where it was declared; even subclasses do not have access.

In D, variables are public by default.

A protected variable is not visible to other classes except subclasses.

So, in a sense, a protected variable is “protected” from other classes while accessible from subclasses. You can say it is a cross between a public and a private variable.

We instantiated all the models here so they are ready for use by all the controllers that inherit from this class. We also included the ensureAuth() method here so they are also inherited by the other controllers.

The employee controller

We have already used the employee controller earlier so let’s review it. This time, the employee controller will inherit from the base controller.

Here is source\empcontrol.d; there are changes here and there so simply copy the whole file:

module empcontrol;
import vibe.vibe;
import empmodel;
import adminmodel;
import basecontrol;

class EmployeeController : BaseController
{
  @auth
  void getAddEmployee(string _authUser, string _error = null)
  {
    string error = _error;
    bool loggedIn = m_user.loggedIn;
    string[] payrates = empModel.getPayrates;
    render!("empadd.dt", departments, payrates, provinces, error, loggedIn);
  }
  
  @errorDisplay!getAddEmployee
  void postAddEmployee(Employee e)
  {
    import std.file;
    import std.path;
    import std.algorithm;
    auto pic = "picture" in request.files;
    if(pic !is null)
    {
      string photopath = "none yet";
      string ext = extension(pic.filename.name);
      string[] exts = [".jpg", ".jpeg", ".png", ".gif"];
      if(canFind(exts, ext))
      {
        photopath = "uploads/photos/" ~ e.fname ~ "_" ~ e.lname ~ ext;
        string dir = "./public/uploads/photos/";
        mkdirRecurse(dir);
        string fullpath = dir ~ e.fname ~ "_" ~ e.lname ~ ext;
        try moveFile(pic.tempPath, NativePath(fullpath));
        catch (Exception ex) copyFile(pic.tempPath, NativePath(fullpath), true);
      }
      e.photo = photopath;
    }
    if(e.phone.length == 0) e.phone = "[phone]";
    if(e.payrate.length == 0) e.payrate = "none yet";
    if(e.postcode.length == 0) e.postcode = "A1A 1A1";
    empModel.addEmployee(e);
    redirect("all_employees");
  }
  
  @auth
  void getAllEmployees(string _authUser, string _error = null)
  {
    string error = _error;
    Employee[] emps = empModel.getEmployees;
    bool loggedIn = m_user.loggedIn;
    render!("emplist.dt", emps, error, loggedIn);
  }
  
  @auth
  void getEditEmployee(string _authUser, int id, string _error = null)
  {
    string error = _error;
    Employee e = empModel.getEmployee(id);
    bool loggedIn = m_user.loggedIn;
    string[] payrates = empModel.getPayrates;
    render!("empedit.dt", e, departments, payrates, provinces, error, loggedIn);
  }
  
  @errorDisplay!getEditEmployee
  void postEditEmployee(Employee e, string prevPassw)
  {
    import std.file;
    import std.path;
    import std.algorithm;
    string photopath = e.photo;
    auto pic = "picture" in request.files;
    if(pic !is null)
    {
      string ext = extension(pic.filename.name);
      string[] exts = [".jpg", ".jpeg", ".png", ".gif"];
      if(canFind(exts, ext))
      {
        photopath = "uploads/photos/" ~ e.fname ~ "_" ~ e.lname ~ ext;
        string dir = "./public/uploads/photos/";
        mkdirRecurse(dir);
        string fullpath = dir ~ e.fname ~ "_" ~ e.lname ~ ext;
        try moveFile(pic.tempPath, NativePath(fullpath));
        catch (Exception ex) copyFile(pic.tempPath, NativePath(fullpath), true);
      }
    }
    e.photo = photopath;
    if(e.phone.length == 0) e.phone = "[phone]";
    if(e.payrate.length == 0) e.payrate = "none yet";
    if(e.postcode.length == 0) e.postcode = "A1A 1A1";
    bool newpass = (prevPassw != e.passw) ? true : false;
    empModel.editEmployee(e, newpass);
    redirect("all_employees");
  }
  
  @auth
  void getDeleteEmployee(string _authUser, int id, string _error = null)
  {
    string error = _error;
    Employee e = empModel.getEmployee(id);
    bool loggedIn = m_user.loggedIn;
    render!("empdelete.dt", e, error, loggedIn);
  }
  
  @errorDisplay!getDeleteEmployee
  void postDeleteEmployee(int id)
  {
    empModel.deleteEmployee(id);
    redirect("all_employees");
  }
  
  @auth
  @errorDisplay!getAllEmployees
  void postFindEmployee(string _authUser, string fname, string lname)
  {
    import std.uni; //so we can use the toUpper() function
    Employee e = empModel.findEmployee(toUpper(fname), toUpper(lname));
    enforce(e != Employee.init, "Cannot find employee " ~ fname ~ " " ~ lname);
    bool loggedIn = m_user.loggedIn;
    render!("empshow.dt", e, loggedIn);
  }
}

We made the EmployeeController inherit from BaseController.

The login system and a new menu

We want the menu reflect the login status. For example, once the login is successful, the menu item should change from ‘Login’ to ‘Logout’. Also, some parts of the menu should have different links depending on the login status.

Here is views\layout.dt:

doctype 5
html
  head
    title Employee Timekeeping System
    include cssclock
    include cssfooter
    include cssformgrid
    include csslayout
    include cssmenu
    include csstable
  body
    div.container
      nav
        include menu
      main.content
        block maincontent
      footer
        include footer

Here is views\menu.dt:

ul
  li
    a(href="/") Home
  li
    a(href="#") Time cards
    ul
      li
        a(href="timecards") View timecards
      li
        a(href="create_timesheet") Create timesheet
  li
    a(href="#") Employees
    ul
      li
        a(href="all_employees") View employees
      -if(loggedIn)
        li
          a(href="#find_employee") Find an employee
      -else
        li
          a(href="#login") Find an employee
      li
        a(href="add_employee") New employee
ul.link-right
  li
    -if(loggedIn)
      a(href="logout") Logout
    -else
      a(href="#login") Login
div#find_employee.modal-form
  div.modal-form-wrapper-find_employee
    form.modal-form-grid(method="post", action="find_employee")
      input.text-control(name="fname", type="text", placeholder=" First name", required)
      input.text-control(name="lname", type="text", placeholder=" Last name", required)
      input#but-reset(type="reset", value="Clear")
      input#but-submit(type="submit", value="Find")
    br
    a.close(href="#close") Cancel
div#login.modal-form
  div.modal-form-wrapper-login
    form.modal-form-grid(method="post", action="login")
      input.text-control(name="email", type="email", placeholder=" email address")
      input.text-control(name="password", type="password", placeholder=" password")
      input#but-reset(type="reset", value="Clear")
      input#but-submit(type="submit", value="Login")
    br
    a.close(href="#close") Cancel

Here is the views\footer.dt which remains the same:

div.copyright Copyright © The Lorem Ipsum Company 2023

Here is views\cssclock.dt:

:css
  .clock-wrapper
  {
    margin: 0 10px;
    text-align: center;
  }
  .clock-grid
  {
    margin: 0 auto;
    padding: 0;
    width: 900px;
    height: auto;
    display: grid;
    grid-template: 90px auto / repeat(12, 1fr);
    grid-template-areas:
      "t0 t0 t0 t0 t0 t0 t0 t0 t0 t0 t0 t0"
      "ci ci ci ci ci ci co co co co co co";
    gap: 20px;
    color: #444;
    text-align: center;
  }
  .clock-heading
  {
    margin-bottom: 10px;
    padding: 0;
    grid-area: t0;
    line-height: 90px;
    color: black;
    font-weight: bold;
    text-align: center;
  }
  #clock-title
  {
    font-size: 30px;
  }
  .clock-text-center
  {
    text-align: center;
  }
  .clock-area
  {
    font-size: 40px;
    font-weight: bold;
    text-align: center;
    width: 370px;
    height: 400px;
    margin: 10px auto;
    padding: 0;
  }
  #clock-in
  {
    grid-area: ci;
  }
  #clock-out
  {
    grid-area: co;
  }
  .clock-btn
  {
    margin: 20px auto;
    padding: 10px 60px;
    border: none;
    background: #363636;
    width: 100%;
    transition: ease-in all 0.3s;
    color: #fff;
    height: auto;
    text-align: center;
    border-radius: 30px;
    font-size: 20px;
    font-weight: bold;
    text-decoration: none;
    cursor: pointer;
  }
  .clock-btn:hover
  {
    background: brown;
    color: #fff;
  }
  #clock-time
  {
    font-size: 24px;
    margin: 100px auto 0 auto;
    color: black;
  }
  #clock-form-select
  {
    margin: 0 auto;
    text-align: center;
    font-size: 20px;
    font-weight: bold;
  }
  .success
  {
    font-size:16px;
    font-weight: bold;
    color: black;
    text-align: center;
  }

Here is views\cssfooter.dt:

:css
  footer
  {
    position: fixed;
    bottom: 0;
    margin-top: 15px;
    background-color: #076;
    padding: 5px 0;
    width: 100%;
  }
  .copyright
  {
    font-family: Verdana, Geneva, Tahoma, sans-serif;
    font-size: 14px;
    text-align: center;
    color: white;
  }

Here is views\cssformgrid.dt:

:css
  .form-grid-wrapper
  {
    width: 500px;
    margin: 20px;
    padding: 1px 20px 20px 0;
    border-radius: 20px;
  }
  .form-grid
  {
    display: grid;
    grid-template-columns: 1fr 1fr;
    gap: 10px;
    padding-top: 5px;
  }
  .form-grid-label
  {
    width: 100%;
    font-size: 16px;
    text-align: right;
    padding: 2px;
  }
  .form-grid-input
  {
    width: 100%;
    font-size: 14px;
    padding: 0;
  }
  .form-grid-field
  {
    width: 100%;
    font-size: 16px;
    border-bottom: 1px solid black;
    padding: 2px;
  }
  .form-grid-button
  {
    width: 100%;
    font-size: 14px;
    height: 30px;
    margin-top: 10px;
  }
  .form-hidden
  {
    padding: 0;
    margin: 0;
    display: inline;
  }

Here is views\csslayout.dt:

:css
  html, body, container
  {
    margin: 0;
    padding: 0;
    width: 100%;
    height: 100%;
  }
  .container
  {
    display: grid;
    grid-template-rows: 38px auto 10px;
    background-color: cyan;
  }
  main
  {
    margin: 0;
    padding: 40px 15px;
    font-family: Sans-serif;
    width: auto;
    height: 100%;
  }
  .text-control
  {
    grid-column: 1 / 3;
  }
  #but-reset
  {
    grid-column: 1 / 2;
  }
  #but-submit
  {
    grid-column: 2 / 3;
  }
  .error-div
  {
    margin-bottom: 5px;
  }
  .error-message
  {
    color: brown;
    font-size: 16px;
    font-weight: bold;
    text-align: left;
  }
  .center-align
  {
    margin: 0 auto;
    text-align: center;
  }
  .right-align
  {
    text-align: right;
  }

Here is views\cssmenu.dt:

:css
  nav
  {
    position: fixed;
    top: 0;
    margin: 0;
    padding: 0;
    display: inline-block;
    background: #076;
    font-family: Verdana, Geneva, Tahoma, sans-serif;
    width: 100%;
  }
  nav ul
  {
    margin:0;
    padding:0;
    list-style-type:none;
    float:left;
    display:inline-block;
  }
  nav ul li
  {
    position: relative;
    margin: 0;
    float: left;
    display: inline-block;
  }
  li > a:only-child:after { content: ''; }
  nav ul li a
  {
    padding: 15px 20px;
    display: inline-block;
    color: white;
    text-decoration: none;
  }
  nav ul li a:hover
  {
    opacity: 0.5;
  }
  nav ul li ul
  {
    display: none;
    position: absolute;
    left: 0;
    background: #076;
    float: left;
    width: 190px;
  }
  nav ul li ul li
  {
    width: 100%;
    border-bottom: 1px solid rgba(255,255,255,.3);
  }
  nav ul li ul li a
  {
    padding: 10px 20px;
  }
  nav ul li:hover ul
  {
    display: block;
  }
  .link-right
  {
    float: right;
    margin-right: 20px;
  }
  .modal-form
  {
    position: fixed;
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
    top: 0;
    right: 0;
    bottom: 0;
    left: 0;
    background: rgba(0,0,0,0.5);
    z-index: 99999;
    opacity: 0;
    pointer-events: none;
  }
  #login:target, #find_employee:target
  {
    opacity: 1;
    pointer-events: auto;
  }
  .modal-form-grid
  {
    display: grid;
    grid-template: 30px 30px 30px / 1fr 1fr;
    grid-gap: 10px;
  }
  .modal-form-wrapper-login
  {
    width: 250px;
    position: absolute;
    right: 0;
    margin-top: 40px;
    padding: 25px 20px;
    border-radius: 10px;
    text-align: center;
    background-color: whitesmoke;
  }
  .modal-form-wrapper-find_employee
  {
    width: 250px;
    position: relative;
    left: 220px;
    margin-top: 40px;
    padding: 25px 20px;
    border-radius: 10px;
    text-align: center;
    background-color: whitesmoke;
  }

And here is views\csstable.dt:

:css
  .table-wrapper
  {
    margin: 10px 0;
    padding-bottom: 40px;
  }
  table
  {
    padding: 10px 20px;
    border-spacing: 0;
    border-collapse: collapse;
  }
  table td, table th
  {
    margin: 0;
    padding: 2px 10px;
    text-align: left;
  }
  .no-border
  {
    border: 0;
  }
  tr:nth-child(odd)
  {
    background-color: #DADADA;
  }

The index, time in and time out templates

Here is the views\index.dt:

extends layout
block maincontent
  div.main
    div.clock-wrapper
      div.clock-grid
        div.clock-heading#clock-title Lorem Ipsum Company Timekeeping System
        div#clock-in.clock-area
          img(src="images/timein.png", alt="photo of a clock")
          a.clock-btn(href="time_in") Punch In
        div#clock-out.clock-area
          img(src="images/timeout.png", alt="photo of a clock")
          a.clock-btn(href="time_out") Punch Out

This is the image I used for the punch in part which I named timein.png:

And this is the image I used for the punch out part, which I named timeout.png:

And these are the other images we used earlier:

The pencil.png:

And the trash.png:

Place all four pictures into public\images.

Here is the views\timein.dt:

extends layout
block maincontent
  div.main
    div.clock-wrapper
      div.clock-grid
        div.clock-heading#clock-title Lorem Ipsum Company Timekeeping System
        form#clock-in.clock-area(method="post", action="time_in")
          div#clock-time
          select#clock-form-select(name="empidname")
            -foreach(e; emps)
              option(value="#{e.empid} #{e.fname} #{e.lname}") #{e.empid} #{e.fname} #{e.lname}
          input.clock-btn(type="submit", value="Punch In")
          -if(error)
            div.error-div
              span.error-message #{error}
          -else if(success)
            div.success #{success}
        div#clock-out.clock-area
          img(src="images/timeout.png", alt="photo of a clock")
          a.clock-btn(href="time_out") Punch Out
    :javascript
      const displayTime = document.querySelector("#clock-time");
      function showTime() {
        let time = new Date();
        displayTime.innerText = time.toLocaleString("en-CA", { hour12: true });
        setTimeout(showTime, 1000);
      }
      showTime();

And here is views\timeout.dt:

extends layout
block maincontent
  div.main
    div.clock-wrapper
      div.clock-grid
        div.clock-heading#clock-title Lorem Ipsum Company Timekeeping System
        div#clock-in.clock-area
          img(src="images/timein.png", alt="photo of a clock")
          a.clock-btn(href="time_in") Punch In
        form#clock-out.clock-area(method="post", action="time_out")
          div#clock-time
          select#clock-form-select(name="empidname")
            -foreach(e; emps)
              option(value="#{e.empid} #{e.fname} #{e.lname}") #{e.empid} - #{e.fname} #{e.lname}
          input.clock-btn(type="submit", value="Punch Out")
          -if(error)
            div.error-div
              span.error-message #{error}
          -else if(success)
            div.success #{success}
    :javascript
      const displayTime = document.querySelector("#clock-time");
      function showTime() {
        let time = new Date();
        displayTime.innerText = time.toLocaleString("en-CA", { hour12: true });
        setTimeout(showTime, 1000);
      }
      showTime();

As usual, make sure all the tabs are consistent or you will get errors when compiling. To make sure, click on the ‘Tab size:’ feature on the footer of VS Code and click on either ‘Convert indentation to Spaces’ or ‘Convert indentation to Tabs’ on the drop-down that appears, whichever is your preference. The important thing is to be consistent. Then save the file again.

The employee views

Here is views\empadd.dt:

extends layout
block maincontent
  -if(error)
    div.error-div
      span.error-message #{error}
  div.form-grid-wrapper
    h2.center-align New employee details
    br
    form.form-grid(method="post", action="add_employee", enctype="multipart/form-data")
      label.form-grid-label Employee number
      input.form-grid-input(type="empid", name="e_empid", placeholder="employee number", required)
      label.form-grid-label Email address
      input.form-grid-input(type="email", name="e_email", placeholder="[e-mail]", required)
      label.form-grid-label Password
      input.form-grid-input(type="password", name="e_passw", placeholder="password", required)
      label.form-grid-label First name
      input.form-grid-input(type="text", name="e_fname", placeholder="First name", required)
      label.form-grid-label Last name
      input.form-grid-input(type="text", name="e_lname", placeholder="Last name", required)
      label.form-grid-label Phone
      input.form-grid-input(type="text", name="e_phone", placeholder="Phone number")
      label.form-grid-label Department
      select#deprt.form-grid-input(name="e_deprt")
        -foreach(dep; departments)
          option(value="#{dep}") #{dep}
      label.form-grid-label Salary grade
      select#deprt.form-grid-input(name="e_payrate")
        -foreach(pay; payrates)
          option(value="#{pay}") #{pay}
      label.form-grid-label Street address (no city)
      input.form-grid-input(type="text", name="e_street", placeholder="Street address", required)
      label.form-grid-label City
      input.form-grid-input(type="text", name="e_city", placeholder="City", required)
      label.form-grid-label Province
      select#province.form-grid-input(name="e_province")
        -foreach(prov; provinces)
          option(value="#{prov[0]}") #{prov[1]}
      label.form-grid-label Postal code
      input.form-grid-input(type="text", name="e_postcode", placeholder="A1A 1A1")
      label.form-grid-label ID Picture
      input.form-grid-input(type="file", name="picture")
      input(type="hidden", name="e_photo")
      input(type="hidden", name="e_id", value="1")
      input.form-grid-button(type="reset", value="Clear form")
      input.form-grid-button(type="submit", value="Submit")

Here is views\empdelete.dt:

extends layout
block maincontent
  div.form-grid-wrapper
  -if(error)
    div.error-div
      span.error-message #{error}
    h2.center-align Delete #{e.fname} #{e.lname}'s record?
    br
    form.form-grid(method="post", action="delete_employee")
      span.form-grid-label Employee number:
      span.form-grid-field #{e.empid}
      span.form-grid-label Email address:
      span.form-grid-field #{e.email}
      span.form-grid-label First name:
      span.form-grid-field #{e.fname}
      span.form-grid-label Last name:
      span.form-grid-field #{e.lname}
      span.form-grid-label Phone:
      span.form-grid-field #{e.phone}
      span.form-grid-label Department:
      span.form-grid-field #{e.deprt}
      span.form-grid-label Salary grade:
      span.form-grid-field #{e.payrate}
      span.form-grid-label Street address:
      span.form-grid-field #{e.street}
      span.form-grid-label City:
      span.form-grid-field #{e.city}
      span.form-grid-label Province:
      span.form-grid-field #{e.province}
      span.form-grid-label Postal code:
      span.form-grid-field #{e.postcode}
      span.form-grid-label ID Picture:
      img(src="#{e.photo}", height="80px")
      input(type="hidden", name="id", value="#{e.id}")
      a(href="all_employees")
        button.form-grid-button(type="button") Cancel
      input.form-grid-button(type="submit", value="Delete")

Here is views\empedit.dt:

extends layout
block maincontent
  div.form-grid-wrapper
  -if(error)
    div.error-div
      span.error-message #{error}
    h2.center-align Edit #{e.fname} #{e.lname} details
    br
    form.form-grid(method="post", action="edit_employee", enctype="multipart/form-data")
      label.form-grid-label Employee number
      input.form-grid-input(type="empid", name="e_empid", value="#{e.empid}")
      label.form-grid-label Email address
      input.form-grid-input(type="email", name="e_email", value="#{e.email}")
      label.form-grid-label Password
      input.form-grid-input(type="password", name="e_passw", value="#{e.passw}")
      label.form-grid-label First name
      input.form-grid-input(type="text", name="e_fname", value="#{e.fname}")
      label.form-grid-label Last name
      input.form-grid-input(type="text", name="e_lname", value="#{e.lname}")
      label.form-grid-label Phone
      input.form-grid-input(type="text", name="e_phone", value="#{e.phone}")
      label.form-grid-label Department
      select#deprt.form-grid-input(name="e_deprt")
        -foreach(dep; departments)
          -if(dep == e.deprt)
            option(value="#{dep}", selected) #{dep}
          -else
          option(value="#{dep}") #{dep}
      label.form-grid-label Salary grade
      select#paygd.form-grid-input(name="e_payrate", value="#{e.payrate}")
        -foreach(pay; payrates)
          -if(pay == e.payrate)
            option(value="#{pay}", selected) #{pay}
          -else
            option(value="#{pay}") #{pay}
      label.form-grid-label Street address (no city)
      input.form-grid-input(type="text", name="e_street", value="#{e.street}")
      label.form-grid-label City
      input.form-grid-input(type="text", name="e_city", value="#{e.city}")
      label.form-grid-label Province
      select#province.form-grid-input(name="e_province", value="#{e.province}")
        -foreach(prov; provinces)
          -if(prov[0] == e.province)
            option(value="#{prov[0]}", selected) #{prov[1]}
          -else
            option(value="#{prov[0]}") #{prov[1]}
      label.form-grid-label Postal code
      input.form-grid-input(type="text", name="e_postcode", value="#{e.postcode}")
      label.form-grid-label ID Picture
      img(src="#{e.photo}", height="100px")
      div
      input.form-grid-input(type="file", name="picture")
      input(type="hidden", name="prevPassw", value="#{e.passw}")
      input(type="hidden", name="e_photo", value="#{e.photo}")
      input(type="hidden", name="e_id", value="#{e.id}")
      input(type="hidden", name="id", value="#{e.id}")
      a(href="all_employees")
        button.form-grid-button(type="button") Cancel
      input.form-grid-button(type="submit", value="Submit")

Here is views\emplist.dt:

extends layout
block maincontent
  h2 Lorem Ipsum Employees
  -if(error)
    div.error-div
      span.error-message #{error}
  div.table-wrapper
    table
      tr
        th Emp #
        th Name
        th Department
        th Phone number
        th Email address
        th Action
      -foreach(e; emps)
        tr
          td #{e.empid}
          td #{e.fname} #{e.lname}
          td #{e.deprt}
          td #{e.phone}
          td #{e.email}
          td  
            form.form-hidden(method="get", action="edit_employee")
              input(type="hidden", name="id", value="#{e.id}")
              input(type="image", src="images/pencil.png", height="15px")
            |  
            form.form-hidden(method="get", action="delete_employee")
              input(type="hidden", name="id", value="#{e.id}")
              input(type="image", src="images/trash.png", height="15px")
            |  

Here is views\empshow.dt:

extends layout
block maincontent
  div.form-grid-wrapper
    h2.center-align #{e.fname} #{e.lname} details
    br
    div.form-grid
      span.form-grid-label Employee number:
      span.form-grid-field #{e.empid}
      span.form-grid-label Email address:
      span.form-grid-field #{e.email}
      span.form-grid-label First name:
      span.form-grid-field #{e.fname}
      span.form-grid-label Last name:
      span.form-grid-field #{e.lname}
      span.form-grid-label Phone:
      span.form-grid-field #{e.phone}
      span.form-grid-label Department:
      span.form-grid-field #{e.deprt}
      span.form-grid-label Salary grade:
      span.form-grid-field #{e.payrate}
      span.form-grid-label Street address:
      span.form-grid-field #{e.street}
      span.form-grid-label City:
      span.form-grid-field #{e.city}
      span.form-grid-label Province:
      span.form-grid-field #{e.province}
      span.form-grid-label Postal code:
      span.form-grid-field #{e.postcode}
      span.form-grid-label ID Picture:
      img(src="#{e.photo}", height="80px")
      a(href="all_employees")
        button.form-grid-button(type="button") Close
      a(href="edit_employee?id=#{e.id}")
        button.form-grid-button(type="button") Edit

The home controller and app.d

We don’t have a controller to displays the home page which is in views\index.dt. Let’s create a new controller, which we will name the HomeController, which will also inherit from BaseController.

Here is the source\homecontrol.d file:

module homecontrol;

import vibe.vibe;
import adminmodel;
import basecontrol;

class HomeController : BaseController
{
  void index(string _error = null)
  {
    string error = _error;
    bool loggedIn = m_user.loggedIn;
    render!("index.dt", error, loggedIn);
  }
  
  @errorDisplay!index
  void postLogin(string email, string password)
  {
    bool isAdmin = adminModel.getAdmin(email, password);
    enforce(isAdmin, "Email and password combination not found.");
    User user = m_user;
    user.loggedIn = true;
    user.email = email;
    m_user = user;
    redirect("timecards");
  }
  
  void getLogout()
  {
    m_user = User.init;
    terminateSession;
    redirect("/");
  }
}

Here we added the postLogin() and getLogout() methods which do not belong to other controllers.

Since we created another controller and we want it as another web interface, we have to register it to the router, so we have to make changes to app.d.

Here is source\app.d:

import vibe.vibe;
import homecontrol;
import empcontrol;
void main()
{
  auto settings = new HTTPServerSettings;
  settings.port = 8080;
  settings.bindAddresses = ["::1", "127.0.0.1"];
  settings.sessionStore = new MemorySessionStore;
  auto router = new URLRouter;
  router.get("*", serveStaticFiles("public/"));
  router.registerWebInterface(new HomeController);
  router.registerWebInterface(new EmployeeController);
  auto listener = listenHTTP(settings, router);
  scope (exit) listener.stopListening();
  runApplication();
}

Now we can test the whole thing.

Compile, run and refresh the browser. If you see this screen, you did good.

Displaying the punch-in page

If you click on the ‘Punch In’ button, you only get an error like this:

That’s because we don’t have a handler yet for the /time_in link. Let’s create the timecard controller to handle all timecard-related actions.

Here is source\cardcontrol.d:

module cardcontrol;
import vibe.vibe;
import basecontrol;
import cardmodel;
import sheetmodel;
import empmodel;

class TimecardController : BaseController
{
  string success = null;
  void getTimeIn(string _error = null)
  {
    Employee[] emps = empModel.getEmployees;
    bool loggedIn = m_user.loggedIn;
    string error = _error;
    render!("timein.dt", emps, loggedIn, error, success);
  }
}

Since TimecardController is another web interface, we have to register it to the router in app.d.

Here is source\app.d:
import vibe.vibe;
import homecontrol;
import empcontrol;
import cardcontrol;

void main()
{
  auto settings = new HTTPServerSettings;
  settings.port = 8080;
  settings.bindAddresses = ["::1", "127.0.0.1"];
  settings.sessionStore = new MemorySessionStore;
  auto router = new URLRouter;
  router.get("*", serveStaticFiles("public/"));
  router.registerWebInterface(new HomeController);
  router.registerWebInterface(new EmployeeController);
  router.registerWebInterface(new TimecardController);
  auto listener = listenHTTP(settings, router);
  scope (exit) listener.stopListening();
  runApplication();
}

Before we test it, let us create some database tables.

Edit source\homecontrol.d:

module homecontrol;
import vibe.vibe;
import basecontrol;
import adminmodel;

class HomeController : BaseController
{
 this()
  {
    adminModel.createTable;
  }
  void index(string _error = null)
  {
    string error = _error;
    bool loggedIn = m_user.loggedIn;
    render!("index.dt", error, loggedIn);
  }
  
  @errorDisplay!index
  void postLogin(string email, string password)
  {
    bool isAdmin = adminModel.getAdmin(email, password);
    enforce(isAdmin, "Email and password combination not found.");
    User user = m_user;
    user.loggedIn = true;
    user.email = email;
    m_user = user;
    redirect("timecards");
  }
  
  void getLogout()
  {
    m_user = User.init;
    terminateSession;
    redirect("/");
  }
}

So that when the HomeController is instantiated, the admins table is created. After compilation, you should delete these lines:

 this()
  {
    adminModel.createTable;
  }

Edit source\empcontrol.d by adding this code at the beginning of the class declaration:

class EmployeeController : BaseController
{
 this()
  {
    empModel.createTable;
    empModel.createPayrates;
  }
  @auth
  void getAddEmployee(string _authUser, string _error = null)
  {
  ...

This means the employees table and the payrates table will be created when EmployeeController is instantiated.

Go ahead and test the whole thing by compiling, running and refreshing the browser.

C:\vibeprojects\loremtime>dub
Starting Performing "debug" build using C:\D\dmd2\windows\bin64\dmd.exe for x86_64.
Up-to-date taggedalgebraic 0.11.22: target for configuration [library] is up to date.
Up-to-date eventcore 0.9.25: target for configuration [winapi] is up to date.
...

Then erase this code from source\empcontrol.d file or else we will always be erasing the employee data:

this()

{

empModel.createTable;

empModel.createPayrates;

}

And erase this from source\homecontrol.d file or else we will always be erasing the admin data:

this()

{

adminModel.createTable;

}

After compilation, erase those lines above or else we will always be recreating those tables, erasing all data every time we compile and run.

Adding employees

After compiling, running and refreshing the browser, click on the ‘Punch In’ button. You should see something like this.

The dropdown list of employees doesn’t show because our employees table has no data since we just created it. So let’s add some employees to the database first.

But then we are redirected to the Login dialogue box, so let’s log in first.

User: [e-mail]

Password: secret

After logging in, we are brought back to the index page, but this time, the ‘Login’ prompt on the menu becomes ‘Logout’, indicating that we are now logged in.

So click on the ‘New employee’ once again to add new employees. Which brings us to this screen.

Add some new employees so we can fill up the employees list like this:

Now let’s go back to the home page and click on the ‘Punch In’ button again. This time, the drop-down list of employees is populated.

Select an employee and click on the ‘Punch In’ button. You see this error:

So let’s create the timecards table.

Edit source\cardcontrol.d:

module cardcontrol;
import vibe.vibe;
import basecontrol;
import cardmodel;
import sheetmodel;
import empmodel;

class TimecardController : BaseController
{
  string success = null;  
  
  this()
  {
    cardModel.createTable;
  }

  
  void getTimeIn(string _error = null)
  {
    Employee[] emps = empModel.getEmployees;
    bool loggedIn = m_user.loggedIn;
    string error = _error;
    render!("timein.dt", emps, loggedIn, error, success);
  }
}

We only added these lines to call the createTable() method on the timecard model:

this()

{

cardModel.createTable;

}

After compiling, delete these lines or else you will always lose the timecard data every time you compile and run the app.

this()

{

cardModel.createTable;

}

Punching in

When punching in, the system should check if the employee already punched in earlier before a new record is created so there is no duplication.

After compiling, go back to the home page and click on the ‘Punch In’ button. We get this error:

So let’s edit source\cardcontrol.d and append this code:

  @errorDisplay!getTimeIn
  void postTimeIn(string empidname)
  {
    string empid = empidname[0..4];
    string fullname = empidname[5..$];
    //the record should not exist, otherwise employee already punched in earlier
    enforce(cardModel.noTimeIn(empid), fullname ~ ", you already punched in today!");
    bool timedin = cardModel.timeIn(empid, fullname);
    if(timedin) success = fullname ~ " punched in successfully.";
    else success = null;
    redirect("time_in");
  }

The lines

string empid = empidname[0..4];

string fullname = empidname[5..$];

mean we are extracting a substring from a string. In D, parts of arrays are called slices, so a substring is a slice. Strings in D are treated like an array of characters so getting substrings out of strings is relatively trivial. Indexing starts with zero as in other languages, but ranges are exclusive of the upper bound. The $ character refers to the length of the array. This means the slice 0..4 actually gets characters 0 to 3, which are four characters.

An example of an empidname data looks like this:

empidname = ‘1002 Emma Rob’

So the line

string empid = empidname[0..4];

means the variable empid will get the characters from 0 to 3, which is ‘1002’.

The line

string fullname = empidname[5..$];

means the variable fullname will get the characters from index 5 up to the end of the string, which is ‘Emma Rob’.

The postTimeIn() method is calling two cardModel methods, noTimeIn() and timeIn(), so let’s write them.

Open source\cardmodel.d and append this code:

  bool noTimeIn(string empid)
  {
    string sql = "select * from timecards where empid=? and date(timein)=?";
    Prepared pstmt = conn.prepare(sql);
    string today = getToday;
    pstmt.setArgs(empid, today);
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return true;
    return false;  
  }
  
  bool timeIn(string empid, string fullname)
  {
    string sql = "insert into timecards(empid, fullname, timein) values(?, ?, now())";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid, fullname);
    if(conn.exec(pstmt) > 0) return true;
    return false;
  }
  
  string getToday()
  {
    Row[] rows = conn.query("select curdate() + 0").array;
    return to!string(rows[0][0]);
  }

The noTimeIn() method checks if the user has timed in previously and is calling the getToday() method, so we added that too.

Compile, run and refresh the browser. Then click on the ‘Punch In’ button again. Select an employee and click ‘Punch In’.

This time we get a successful message.

So go ahead and punch in all the other employees.

Viewing the timecards

Click on the ‘View timecards’ on the menu to see the timecards.

But then you get an error message.

Edit source\cardcontrol.d and append this code:

  void getTimecards(string _error = null)
  {
    string error = _error;
    string today = getToday;
    bool loggedIn = m_user.loggedIn;
    Timecard[] cards = cardModel.getTimecards(loggedIn);
    render!("cardlist.dt", cards, today, error, loggedIn);
  }

This code calls cardModel.getTimecards(), which we haven’t written yet, so let’s write that.

Edit source\cardmodel.d and append this code:

  Timecard[] getTimecards(bool loggedIn)
  {
    Timecard[] cards;
    string sql;
    if(loggedIn)
      sql = "select * from timecards order by timein desc";
    else
      sql = "select * from timecards where date(timein) = curdate() order by timein desc";
    Row[] rows = conn.query(sql).array;
    if(rows.length != 0)
    {
      foreach(row; rows)
      {
        Timecard c;
        c.id = to!int(to!string(row[0]));
        c.empid = to!string(row[1]);
        c.fullname = to!string(row[2]);
        c.timein = to!string(row[3]);
        c.timeout = to!string(row[4]);
        c.hours = to!float(to!string(row[5]));
        cards ~= c;
      }
    }
    return cards;
  }

If you are logged in, meaning you are an administrator, you get to view all the timecards at whatever date. However, ordinary employees only get to view the timecards of the day so they can review their status for the day.

The cardControl.getTimecards() method needs the cardlist.dt template, so let’s create it.

Here is the views\cardlist.dt:

extends layout
block maincontent
  div.table-wrapper
    -if(loggedIn)
      h2 All time cards
    -else
      h2 #{today}
    table
      tr
        th Emp #
        th Name
        th Time in
        th Time out
        th Hours
        -if(loggedIn)
          th Action
      -foreach(c; cards)
        tr
          td #{c.empid}
          td #{c.fullname}
          td #{c.timein}
          td #{c.timeout}
          td #{c.hours}
          -if(loggedIn)
            td  
              form.form-hidden(method="get", action="edit_timecard")
                input(type="hidden", name="id", value="#{c.id}")
                input(type="image", src="images/pencil.png", height="15px")
              |  
              form.form-hidden(method="get", action="delete_timecard")
                input(type="hidden", name="id", value="#{c.id}")
                input(type="image", src="images/trash.png", height="15px")
              |             

Compile, run and refresh the browser. If you are not logged in, you see the list of time cards only for the day.

Punching out

When an employee punches out, the system should check first if the employee already punched in earlier, meaning, the record already exists and the timeout column with a null value. If not, then the employee cannot punch out since there is no timeout to edit. If the record exists, then the app should check if the employee has already punched out earlier, in that case the employee cannot punch out again.

As of now, when you click the ‘Punch Out’ button on the home page, this is what we get:

So let’s edit source\cardcontrol.d and append this code:

  void getTimeOut(string _error = null)
  {
    Employee[] emps = empModel.getEmployees;
    bool loggedIn = m_user.loggedIn;
    string error = _error;
    render!("timeout.dt", emps, loggedIn, error, success);
  }
  
  @errorDisplay!getTimeOut
  void postTimeOut(string empidname)
  {
    string empid = empidname[0..4];
    string fullname = empidname[5..$];
    //the record should exist, otherwise employee did not punch in today
    enforce(!cardModel.noTimeIn(empid), fullname ~ ", you did not punch in today.");
    //the timeout field should be null, otherwise employee already punched out
    enforce(cardModel.noTimeOut(empid), fullname ~ ", you already punched out today!");
    bool timedout = cardModel.timeOut(empid);
    if(timedout) success = fullname ~ " punched out successfully.";
    else success = null;
    redirect("time_out");
  }

The postTimeOut() method is calling two new methods from cardModel: noTimeOut() and timeOut(), so let’s write them.

Edit source\cardmodel.d and append this code:

  bool noTimeOut(string empid)
  {
    string sql = "select * from timecards where timeout is null and empid=? and date(timein)=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid, getToday);
    Row[] rows = conn.query(pstmt).array;
    if(rows.length != 0) return true;
    return false;  
  }
  
  bool timeOut(string empid)
  {
    // we want to include the fraction of the hour
    // so we use the MySQL function time_to_sec()
    // 3600 is seconds in an hour
    // but we round out the quotient to just two decimals
    string sql =
    "update timecards set timeout=now(),
      hours=round((time_to_sec(now())-time_to_sec(timein))/3600,2)
      where empid=? and date(timein)=curdate()";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid);
    if(conn.exec(pstmt) > 0) return true;
    return false;
  }

Then compile, run and go back to the home page and click on the ‘Punch Out’ button. This time, the punch out screen appears.

Go ahead and punch out some employees. You get a message each time, either successful

or unsuccessful

Then click on the ‘View timecards’ link on the menu to see the list of timecards. This time, you see the punch out times and hours.

When an employee complains he/she cannot punch out because he/she forgot to punch in, the supervisor (who should be one of the administrators) could simply login to edit or add records.

The steps to follow by the employee should be:

1. Punch in even though it is late; this will create the record

2. Then punch out

3. Let the admin know that you forgot to punch in earlier

4. The admin edits the punch-in time

So let us mimic the administrator.

Go ahead and log in. Remember that ‘secret’ is the password. After a successful login, the ‘Login’ item on the menu becomes ‘Logout’.

Then click on the ‘View timecards’ link on the menu to see the list of timecards. This time, the ‘edit’ and ‘delete’ icons appear.

Because you have logged in as an administrator, you can now edit or delete any record in the timecards table.

Editing a timecard entry

But then, when you click on one of the pencil icons to edit a record, you get an error message because we still haven’t written the editing part yet.

Open source\cardcontrol.d and append this code:

  void getEditTimecard(int id, string _error = null)
  {
    string error = _error;
    Timecard t = cardModel.getTimecard(id);
    Employee e = empModel.getEmployee(t.empid);
    bool loggedIn = m_user.loggedIn;
    render!("cardedit.dt", t, e, error, loggedIn);
  }
  @errorDisplay!getEditTimecard
  void postEditTimecard(Timecard t)
  {
    cardModel.editTimecard(t);
    redirect("timecards");
  }

This code is calling the TimecardModel methods getTimecard() and editTimecard() as well as rendering the cardedit.dt template, so let’s write them.

Append this code to source\cardmodel.d:

  Timecard getTimecard(int id)
  {
    string sql = "select * from timecards where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    Row[] rows = conn.query(pstmt).array;
    Timecard c;
    if(rows.length > 0)
    {
      Row row = rows[0];
      c.id = to!int(to!string(row[0]));
      c.empid = to!string(row[1]);
      c.fullname = to!string(row[2]);
      c.timein = to!string(row[3]);
      c.timeout = to!string(row[4]);
      c.hours = to!float(to!string(row[5]));
    }
    return c;
  }
  
  void editTimecard(Timecard t)
  {
    Prepared pstmt;
    if(to!string(t.timeout) == "null")
    {
      string sql =
        "update timecards set
        timein=str_to_date(?,'%Y-%b-%d %H:%i:%S'),
        timeout=null
        where id=?";
      pstmt = conn.prepare(sql);
      pstmt.setArgs(t.timein, t.id);
    }
    else
    {
      string sql =
        "update timecards set
        timein=str_to_date(?,'%Y-%b-%d %H:%i:%S'),
        timeout=str_to_date(?,'%Y-%b-%d %H:%i:%S'),
        hours=round((time_to_sec(timeout)-time_to_sec(timein))/3600,2)
        where id=?";
      pstmt = conn.prepare(sql);
      pstmt.setArgs(t.timein, t.timeout, t.id);
    }
    conn.exec(pstmt);
  }

And here is views\cardedit.dt:

extends layout
block maincontent
  div.form-grid-wrapper
    h2 Edit time card entry
    -if(error)
      div.error-div
        span.error-message #{error}
    form.form-grid(method="post", action="edit_timecard")
      label.form-grid-label Employee :
      label.form-grid-text #{t.empid} - #{t.fullname}
      label.form-grid-label Time in :
      input.form-grid-input(type="text", name="t_timein", value="#{t.timein}")
      label.form-grid-label Time out :
      input.form-grid-input(type="text", name="t_timeout", value="#{t.timeout}")
      div
      input(type="hidden", name="t_id", value="#{t.id}")
      input(type="hidden", name="t_empid", value="#{t.empid}")
      input(type="hidden", name="t_fullname", value="#{t.fullname}")
      input(type="hidden", name="t_hours", value="#{t.hours}")
      div
      div
        a(href="timecards")
          button.form-grid-button(type="button") Cancel
      div
        input.form-grid-button(type="submit", value="Submit")

Now we can compile, run and refresh the browser. Go back to the home page to refresh the browser. Try to log in again. Then try to view the timecards again.

Click on one of the pencil icons. This time it will open the timecard-editing page.

Now you can edit either the time-in or the time-out or both, as long as you follow the format of the date and time, including the space in between.

Deleting a timecard entry

As we have done with the employee records earlier, when the user clicks on the delete button (the trash image), the record should be shown on another page and ask the user for confirmation. That is what we will do next.

Append this code to source\cardcontrol.d:

  void getDeleteTimecard(int id, string _error = null)
  {
    string error = _error;
    Timecard t = cardModel.getTimecard(id);
    Employee e = empModel.getEmployee(t.empid);
    bool loggedIn = m_user.loggedIn;
    render!("carddelete.dt", t, e, error, loggedIn);
  }
  
  @errorDisplay!getDeleteTimecard
  void postDeleteTimecard(int id)
  {
    cardModel.deleteTimecard(id);
    redirect("timecards");
  }

The getDeleteTimecard() is rendering the carddelete.dt, so let’s create it.

Here is views\carddelete.dt:

extends layout
block maincontent
  div.form-grid-wrapper
    h2 Delete this time card entry?
    form.form-grid(method="post", action="delete_timecard")
      label.form-grid-label Employee :
      label.form-grid-text #{e.empid} - #{e.fname} #{e.lname}
      label.form-grid-label Punched in :
      label.form-grid-text #{t.timein}
      label.form-grid-label Punched out :
      label.form-grid-text #{t.timeout}
      input(type="hidden", name="id", value="#{t.id}")
      div
        a(href="timecards")
          button.form-grid-button(type="button") Cancel
      div
        input.form-grid-button(type="submit", value="Delete")

The postDeleteTimecard() method is calling the cardModel.deleteTimecard(), so let’s write it.

Append this code to source\cardmodel.d:

  void deleteTimecard(int id)
  {
    string sql = "delete from timecards where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    conn.exec(pstmt);
  }

Now compile, run and go back to the home page to refresh the browser. Open the list of timecards.

There are no ‘edit’ and ‘delete’ icons. You have to log in again because this is a new version of the app and the session values were erased when you recompiled and ran again.

Open the list of timecards again. This time you will see the ‘edit’ and ‘delete’ icons.

Click on one of the trash icons to open the record in a separate page.

And if you press ‘Delete’, the record is gone from the list.

Generating the timesheet

The timesheet computes the hours worked by each employee at end of wage period, in this case weekly. It looks up the hours from the timecards table and generates the timesheet based on the timecards data. The timesheet is overwritten every time it is created but the timecards remain so they are permanent records.

So let’s edit source\sheetmodel.d:

module sheetmodel;
import mysql;
import std.array;
import std.conv;
import basemodel;

struct Timesheet
{
  int id;
  int period;
  string empid;
  string fullname;
  float hours;
}

class TimesheetModel : BaseModel
{
  void createTable()
  {
    string sql = "drop table if exists timesheets";
    conn.exec(sql);
    sql =
      "create table timesheets
      (
        id int auto_increment primary key,
        period int not null,
        empid char(4) not null,
        fullname varchar(50) not null,
        hours float
      )";
    conn.exec(sql);
  }
  
  void createTimesheet(int week)
  {
    string sql = "delete from timesheets";
    conn.exec(sql);
    sql =
      "insert into timesheets(period, hours, empid, fullname)
        (select week(timein) as period, sum(hours), empid, fullname from timecards
        where week(timein)=? group by period, empid, fullname)";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(week);
    conn.exec(pstmt);
  }
  
  Timesheet[] getTimesheets()
  {
    string sql = "select * from timesheets order by empid";
    Row[] rows = conn.query(sql).array;
    Timesheet[] sheets;
    foreach (row; rows)
    {
      Timesheet t;
      t.id = to!int(to!string(row[0]));
      t.period = to!int(to!string(row[1]));
      t.empid = to!string(row[2]);
      t.fullname = to!string(row[3]);
      t.hours = to!double(to!string(row[4]));
      sheets ~= t;
    }
    return sheets;
  }
  
  int getTheWeek()
  {
    Row[] rows = conn.query("select period from timesheets limit 1").array;
    if(rows.length == 0) return 0;
    return to!int(to!string(rows[0][0]));
  }
}

We added the method createTimesheet() to the model which is the code that actually creates the timesheet, as well as the getTheWeek() method which will be needed by the TimesheetController.

Here is source\sheetcontrol.d:

module sheetcontrol;
import vibe.vibe;
import basecontrol;
import empmodel;
import cardmodel;
import sheetmodel;

class TimesheetController : BaseController
{
  @auth
  void getCreateTimesheet(string _authUser, string _error = null)
  {
    string error = _error;
    int[] weeks = cardModel.getTheWeeks;
    bool loggedIn = m_user.loggedIn;
    render!("sheetcreate.dt", error, loggedIn, weeks);
  }
  
  @errorDisplay!getCreateTimesheet
  void postCreateTimesheet(int week)
  {
    sheetModel.createTimesheet(week);
    redirect("timesheets");
  }
  
  void getTimesheets()
  {
    Timesheet[] sheets = sheetModel.getTimesheets;
    bool loggedIn = m_user.loggedIn;
    int week = sheetModel.getTheWeek;
    render!("sheetlist.dt", sheets, loggedIn, week);
  }
}

The getCreateTimesheet() method is calling cardModel.getTheWeeks() before rendering the form sheetcreate.dt, so let’s create them.

Append this code to source\cardmodel.dt:

  int[] getTheWeeks()
  {
    string sql = "select distinct week(timein) as weeks from timecards order by weeks";
    Row[] rows = conn.query(sql).array;
    int[] weeks;
    foreach (row; rows) weeks ~= to!int(to!string(row[0]));
    return weeks;
  }

And here is views\sheetcreate.dt:

extends layout
block maincontent
  include cssformgrid.dt
  div.form-grid-wrapper
    -if(error)
      div.error-div
        span.error-message #{error}
    form.form-grid(method="post", action="create_timesheet")
      div
        label.form-grid-label For week number
        select.form-grid-input(name="week")
          -foreach(w; weeks)
            option(value="#{w}") #{w}
      div
      input.form-grid-button(type="submit", value="Generate timesheet")

The sheetControl.getTimesheets() does the actual display of the timesheet by calling sheetModel.getTimesheets() and sheetModel.getTheWeek(), which we have already written, then rendering sheetlist.dt, which we haven’t created yet, so let’s create it.

Here is views\sheetlist.dt:

extends layout
block maincontent
  h2 Timesheet for week #{week}
  div.table-wrapper
    table
      tr
        th Emp #
        th Name
        th Hours
      -foreach(s; sheets)
        tr
          td #{s.empid}
          td #{s.fullname}
          td.right-align #{s.hours}

Now compile and run the app.

C:\vibeprojects\loremtime>dub
Starting Performing "debug" build using C:\D\dmd2\windows\bin64\dmd.exe for x86_64.
…
Running loremtime.exe
[main(----) INF] Listening for requests on http://[::1]:8080/
[main(----) INF] Listening for requests on http://127.0.0.1:8080/

Try to log in again. Then click on the ‘Create timesheet’ link, then choose a week from the drop-down list.

Click on the ‘Generate timesheet’ button.

And you get an error message.

So let’s create the timesheets table.

Edit source\sheetcontrol.d and add this code right after the class declaration:

class TimesheetController : BaseController
{
  this()
  {
    sheetModel.createTable;
  }
  
  @auth
  void getCreateTimesheet(string _authUser, string _error = null)
  {
  ...

Then compile and run.

After compiling and running, delete the code that you have just added!

this()

{

sheetModel.createTable;

}

Then compile again.

Click on the ‘Create timesheet’ link. You are redirected to the login dialogue.

Login again, then click on the ‘Create timesheet’ button again.

After selecting the week and clicking ‘Generate timesheet’, you should see this:

You should be able to see the total number of hours the employees worked for the week. As mentioned before, the timesheet is not editable because it simply extracts the data from the timecards table. The timecards table is editable and is a permanent record while the timesheets table is simply generated when needed.

The end

That’s it, we reached the end of this tutorial.

If you want to give me some feedback, you can email me at [e-mail].

The following pages show all the source code.

The main program

Here is source\app.d:

import vibe.vibe;
import homecontrol;
import empcontrol;
import cardcontrol;
import sheetcontrol;

void main()
{
  auto settings = new HTTPServerSettings;
  settings.port = 8080;
  settings.bindAddresses = ["::1", "127.0.0.1"];
  settings.sessionStore = new MemorySessionStore;
  auto router = new URLRouter;
  router.get("*", serveStaticFiles("public/"));
  router.registerWebInterface(new HomeController);
  router.registerWebInterface(new EmployeeController);
  router.registerWebInterface(new TimecardController);
  router.registerWebInterface(new TimesheetController);
  auto listener = listenHTTP(settings, router);
  scope (exit) listener.stopListening();
  runApplication();
}

The base controller

Here is source\basecontrol.d:

module basecontrol;
import vibe.vibe;
import empmodel;
import cardmodel;
import sheetmodel;
import adminmodel;

struct User
{
  bool loggedIn;
  string email;
}

class BaseController
{
  protected SessionVar!(User, "user") m_user;
  protected enum auth = before!ensureAuth("_authUser");
  protected EmployeeModel empModel;
  protected TimecardModel cardModel;
  protected TimesheetModel sheetModel;
  protected AdminModel adminModel;
  
  this()
  {
    empModel = new EmployeeModel;
    cardModel = new TimecardModel;
    sheetModel = new TimesheetModel;
    adminModel = new AdminModel;
  }
  
  string ensureAuth(HTTPServerRequest req, HTTPServerResponse res)
  {
    if(!m_user.loggedIn) redirect("#login");
    return m_user.email;
  }
  mixin PrivateAccessProxy;
  
  string getToday()
  {
    import std.datetime.date;
    auto time = Clock.currTime;
    return to!string(Date(time.year, time.month, time.day));
  }
}

The base model

module basemodel;

import mysql;

class BaseModel
{
	string realm = "The Lorem Ipsum Company";
	Connection conn;

	this()
	{
		string url = "host=localhost;port=3306;user=owner;pwd=qwerty;db=loremdb";
		conn = new Connection(url);
		scope(exit) conn.close;
	}
}

The home controller

Here is source\homecontrol.d:

module homecontrol;
import vibe.vibe;
import basecontrol;
import adminmodel;

class HomeController : BaseController
{
  void index(string _error = null)
  {
    string error = _error;
    bool loggedIn = m_user.loggedIn;
    render!("index.dt", error, loggedIn);
  }
  
  @errorDisplay!index
  void postLogin(string email, string password)
  {
    bool isAdmin = adminModel.getAdmin(email, password);
    enforce(isAdmin, "Email and password combination not found.");
    User user = m_user;
    user.loggedIn = true;
    user.email = email;
    m_user = user;
    redirect("/");
  }
  
  void getLogout()
  {
    m_user = User.init;
    terminateSession;
    redirect("/");
  }
}

The employees controller

Here is source\empcontrol.d:

module empcontrol;
import vibe.vibe;
import basecontrol;
import empmodel;
import adminmodel;

class EmployeeController : BaseController
{
  @auth
  void getAddEmployee(string _authUser, string _error = null)
  {
    string error = _error;
    bool loggedIn = m_user.loggedIn;
    string[] payrates = empModel.getPayrates;
    render!("empadd.dt", departments, payrates, provinces, error, loggedIn);
  }
  
  @errorDisplay!getAddEmployee
  void postAddEmployee(Employee e)
  {
    import std.file;
    import std.path;
    import std.algorithm;
    auto pic = "picture" in request.files;
    if(pic !is null)
    {
      string photopath = "none yet";
      string ext = extension(pic.filename.name);
      string[] exts = [".jpg", ".jpeg", ".png", ".gif"];
      if(canFind(exts, ext))
      {
        photopath = "uploads/photos/" ~ e.fname ~ "_" ~ e.lname ~ ext;
        string dir = "./public/uploads/photos/";
        mkdirRecurse(dir);
        string fullpath = dir ~ e.fname ~ "_" ~ e.lname ~ ext;
        try moveFile(pic.tempPath, NativePath(fullpath));
        catch (Exception ex) copyFile(pic.tempPath, NativePath(fullpath), true);
      }
      e.photo = photopath;
    }
    if(e.phone.length == 0) e.phone = "[phone]";
    if(e.payrate.length == 0) e.payrate = "none yet";
    if(e.postcode.length == 0) e.postcode = "A1A 1A1";
    empModel.addEmployee(e);
    redirect("all_employees");
  }
  
  @auth
  void getAllEmployees(string _authUser, string _error = null)
  {
    string error = _error;
    Employee[] emps = empModel.getEmployees;
    bool loggedIn = m_user.loggedIn;
    render!("emplist.dt", emps, error, loggedIn);
  }
  
  @auth
  void getEditEmployee(string _authUser, int id, string _error = null)
  {
    string error = _error;
    Employee e = empModel.getEmployee(id);
    bool loggedIn = m_user.loggedIn;
    string[] payrates = empModel.getPayrates;
    render!("empedit.dt", e, departments, payrates, provinces, error, loggedIn);
  }
  
  @errorDisplay!getEditEmployee
  void postEditEmployee(Employee e, string prevPassw)
  {
    import std.file;
    import std.path;
    import std.algorithm;
    string photopath = e.photo;
    auto pic = "picture" in request.files;
    if(pic !is null)
    {
      string ext = extension(pic.filename.name);
      string[] exts = [".jpg", ".jpeg", ".png", ".gif"];
      if(canFind(exts, ext))
      {
        photopath = "uploads/photos/" ~ e.fname ~ "_" ~ e.lname ~ ext;
        string dir = "./public/uploads/photos/";
        mkdirRecurse(dir);
        string fullpath = dir ~ e.fname ~ "_" ~ e.lname ~ ext;
        try moveFile(pic.tempPath, NativePath(fullpath));
        catch (Exception ex) copyFile(pic.tempPath, NativePath(fullpath), true);
      }
    }
    e.photo = photopath;
    if(e.phone.length == 0) e.phone = "[phone]";
    if(e.payrate.length == 0) e.payrate = "none yet";
    if(e.postcode.length == 0) e.postcode = "A1A 1A1";
    bool newpass = (prevPassw != e.passw) ? true : false;
    empModel.editEmployee(e, newpass);
    redirect("all_employees");
  }
  
  @auth
  void getDeleteEmployee(string _authUser, int id, string _error = null)
  {
    string error = _error;
    Employee e = empModel.getEmployee(id);
    bool loggedIn = m_user.loggedIn;
    render!("empdelete.dt", e, error, loggedIn);
  }
  
  @errorDisplay!getDeleteEmployee
  void postDeleteEmployee(int id)
  {
    empModel.deleteEmployee(id);
    redirect("all_employees");
  }
  
  @auth
  @errorDisplay!getAllEmployees
  void postFindEmployee(string _authUser, string fname, string lname)
  {
    import std.uni; //so we can use the toUpper() function
    Employee e = empModel.findEmployee(toUpper(fname), toUpper(lname));
    enforce(e != Employee.init, "Cannot find employee " ~ fname ~ " " ~ lname);
    bool loggedIn = m_user.loggedIn;
    render!("empshow.dt", e, loggedIn);
  }
}

The employee model

Here is source\empmodel.d:

module empmodel;
import mysql;
import std.conv;
import std.array;
import basemodel;

struct Employee
{
  int id; //row id or record id
  string empid; //employee number
  string email; //email address
  string passw; //password
  string fname; //first name
  string lname; //last name
  string phone; //phone number
  string photo; //ID photo
  string deprt; //department
  string payrate; //salary grade
  string street; //street address
  string city; //city name
  string province; //province name
  string postcode; //postal code
}

string[] departments =
[
  "Management",
  "Accounting",
  "Production",
  "Maintenance",
  "Shipping",
  "Purchasing",
  "IT Services",
  "HR Services",
  "Marketing"
];

string[][] provinces =
[
  ["AB", "Alberta"],
  ["BC", "British Columbia"],
  ["MB", "Manitoba"],
  ["NB", "New Brunswick"],
  ["NL", "Newfoundland and Labrador"],
  ["NS", "Nova Scotia"],
  ["NT", "Northwest Territories"],
  ["NU", "Nunavut"],
  ["ON", "Ontario"],
  ["PE", "Prince Edward Island"],
  ["QC", "Quebec"],
  ["SK", "Saskatchewan"],
  ["YT", "Yukon Territory"]
];

class EmployeeModel : BaseModel
{
  void createTable()
  {
    string sql = "drop table if exists employees";
    conn.exec(sql);
    sql =
      "create table employees
      (
        id int auto_increment primary key,
        empid char(4) not null unique,
        email varchar(50) not null,
        passw varchar(255) not null,
        fname varchar(25) not null,
        lname varchar(25) not null,
        phone varchar(15) default '123-456-7890',
        photo varchar(50) default 'none provided',
        deprt varchar(30) default 'not assigned yet',
        payrate char(4) default 'B400',
        street varchar(50) default '123 First Street',
        city varchar(30) default 'Toronto',
        province char(2) default 'ON',
        postcode char(7) default 'Z9Z 9Z9',
        created timestamp default current_timestamp,
        updated timestamp on update current_timestamp
      )";
    conn.exec(sql);
  }
  
  void createPayrates()
  {
    int[string] payrates =
    [
      "A100":100, "A200":75, "A300":50, "A400":40,
      "B100":30, "B200":29, "B300":28, "B400":27,
      "C100":26, "C200":25, "C300":24, "C400":23,
      "D100":22, "D200":21, "D300":20, "D400":19,
      "E100":18, "E200":17, "E300":16, "E400":15,
      "F100":14, "F200":13, "F300":12, "F400":11
    ];
    string sql = "drop table if exists payrates";
    conn.exec(sql);
    sql =
      "create table payrates
      (
        id int auto_increment primary key,
        payrate char(4) not null,
        hourly float not null
      )";
    conn.exec(sql);
    foreach(k,v; payrates)
    {
      sql =
        "insert into payrates(payrate, hourly)
        values('" ~ k ~ "', " ~ to!string(v) ~ ")" ;
      conn.exec(sql);
    }
  }
  
  string[] getPayrates()
  {
    string sql = "select payrate from payrates order by payrate";
    Row[] rows = conn.query(sql).array;
    string[] rates;
    foreach(row; rows) rates ~= to!string(row[0]);
    return rates;
  }
  
  void addEmployee(Employee e)
  {
    import vibe.http.auth.digest_auth;
    e.passw = createDigestPassword(realm, e.email, e.passw);
    string sql =
      "insert into employees
      (
        empid,
        email, passw, fname, lname,
        phone, photo, deprt, payrate,
        street, city, province, postcode
      )
      values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs
    (
      to!string(e.empid),
      to!string(e.email),
      to!string(e.passw),
      to!string(e.fname),
      to!string(e.lname),
      to!string(e.phone),
      to!string(e.photo),
      to!string(e.deprt),
      to!string(e.payrate),
      to!string(e.street),
      to!string(e.city),
      to!string(e.province),
      to!string(e.postcode)
    );
    conn.exec(pstmt);
  }
  
  Employee[] getEmployees()
  {
    Employee[] emps;
    string sql = "select * from employees";
    Row[] rows = conn.query(sql).array;
    if(rows.length == 0) return emps;
    foreach(row; rows) emps ~= prepareEmployee(row);
    return emps;
  }
  
  Employee prepareEmployee(Row row)
  {
    Employee e;
    e.id = to!int(to!string(row[0]));
    e.empid = to!string(row[1]);
    e.email = to!string(row[2]);
    e.passw = to!string(row[3]);
    e.fname = to!string(row[4]);
    e.lname = to!string(row[5]);
    e.phone = to!string(row[6]);
    e.photo = to!string(row[7]);
    e.deprt = to!string(row[8]);
    e.payrate = to!string(row[9]);
    e.street = to!string(row[10]);
    e.city = to!string(row[11]);
    e.province = to!string(row[12]);
    e.postcode = to!string(row[13]);
    return e;
  }
  
  Employee getEmployee(int id)
  {
    string sql = "select * from employees where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    Employee e;
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return e;
    return prepareEmployee(rows[0]);
  }
  
  Employee getEmployee(string empid)
  {
    string sql = "select * from employees where empid=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid);
    Employee e;
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return e;
    return prepareEmployee(rows[0]);
  }
  
  void editEmployee(Employee e, bool newpass)
  {
    if(newpass) // if password was changed, encrypt it
    {
      import vibe.http.auth.digest_auth;
      e.passw = createDigestPassword(realm, e.email, e.passw);
    }
    string sql =
      "update employees set empid=?,
      email=?, passw=?, fname=?, lname=?,
      phone=?, photo=?, deprt=?, payrate=?,
      street=?, city=?, province=?, postcode=?
      where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs
    (
      to!string(e.empid),
      to!string(e.email),
      to!string(e.passw),
      to!string(e.fname),
      to!string(e.lname),
      to!string(e.phone),
      to!string(e.photo),
      to!string(e.deprt),
      to!string(e.payrate),
      to!string(e.street),
      to!string(e.city),
      to!string(e.province),
      to!string(e.postcode),
      to!int(to!string(e.id))
    );
    conn.exec(pstmt);
  }
  
  void deleteEmployee(int id)
  {
    string sql = "delete from employees where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    conn.exec(pstmt);
  }
  
  Employee findEmployee(string first, string last)
  {
    Employee e;
    string sql = "select * from employees where upper(fname)=? and upper(lname)=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(first, last);
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return e;
    return prepareEmployee(rows[0]);
  }
}

The timecard controller

Here is source\cardcontrol.d:

module cardcontrol;
import vibe.vibe;
import basecontrol;
import cardmodel;
import sheetmodel;
import empmodel;

class TimecardController : BaseController
{
  string success = null;
  
  void getTimeIn(string _error = null)
  {
    Employee[] emps = empModel.getEmployees;
    bool loggedIn = m_user.loggedIn;
    string error = _error;
    render!("timein.dt", emps, loggedIn, error, success);
  }
  
  @errorDisplay!getTimeIn
  void postTimeIn(string empidname)
  {
    string empid = empidname[0..4];
    string fullname = empidname[5..$];
    //the record should not exist, otherwise employee already punched in earlier
    enforce(cardModel.noTimeIn(empid), fullname ~ ", you already punched in today!");
    bool timedin = cardModel.timeIn(empid, fullname);
    if(timedin) success = fullname ~ " punched in successfully.";
    else success = null;
    redirect("time_in");
  }
  
  void getTimecards(string _error = null)
  {
    string error = _error;
    string today = getToday;
    bool loggedIn = m_user.loggedIn;
    Timecard[] cards = cardModel.getTimecards(loggedIn);
    render!("cardlist.dt", cards, today, error, loggedIn);
  }
  
  void getTimeOut(string _error = null)
  {
    Employee[] emps = empModel.getEmployees;
    bool loggedIn = m_user.loggedIn;
    string error = _error;
    render!("timeout.dt", emps, loggedIn, error, success);
  }
  
  @errorDisplay!getTimeOut
  void postTimeOut(string empidname)
  {
    string empid = empidname[0..4];
    string fullname = empidname[5..$];
    //the record should exist, otherwise employee did not punch in today
    enforce(!cardModel.noTimeIn(empid), fullname ~ ", you did not punch in today.");
    //the timeout field should be null, otherwise employee already punched out
    enforce(cardModel.noTimeOut(empid), fullname ~ ", you already punched out today!");
    bool timedout = cardModel.timeOut(empid);
    if(timedout) success = fullname ~ " punched out successfully.";
    else success = null;
    redirect("time_out");
  }
  
  void getEditTimecard(int id, string _error = null)
  {
    string error = _error;
    Timecard t = cardModel.getTimecard(id);
    Employee e = empModel.getEmployee(t.empid);
    bool loggedIn = m_user.loggedIn;
    render!("cardedit.dt", t, e, error, loggedIn);
  }
  
  @errorDisplay!getEditTimecard
  void postEditTimecard(Timecard t)
  {
    cardModel.editTimecard(t);
    redirect("timecards");
  }
  
  void getDeleteTimecard(int id, string _error = null)
  {
    string error = _error;
    Timecard t = cardModel.getTimecard(id);
    Employee e = empModel.getEmployee(t.empid);
    bool loggedIn = m_user.loggedIn;
    render!("carddelete.dt", t, e, error, loggedIn);
  }
  
  @errorDisplay!getDeleteTimecard
  void postDeleteTimecard(int id)
  {
    cardModel.deleteTimecard(id);
    redirect("timecards");
  }
}

The timecard model

Here is source\cardmodel.d:

module cardmodel;
import mysql;
import std.array;
import std.conv;
import basemodel;

struct Timecard
{
  int id;
  string empid;
  string fullname;
  string timein;
  string timeout;
  float hours;
}

class TimecardModel : BaseModel
{
  void createTable()
  {
    string sql = "drop table if exists timecards";
    conn.exec(sql);
    sql =
      "create table timecards
      (
        id int auto_increment primary key,
        empid char(4) not null references employees(empid),
        fullname varchar(50) not null,
        timein datetime not null default current_timestamp,
        timeout datetime,
        hours float default 0.0
      )";
    conn.exec(sql);
  }
  
  bool timeIn(string empid, string fullname)
  {
    string sql = "insert into timecards(empid, fullname, timein) values(?, ?, now())";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid, fullname);
    if(conn.exec(pstmt) > 0) return true;
    return false;
  }
  
  bool noTimeIn(string empid)
  {
    string sql = "select * from timecards where empid=? and date(timein)=?";
    Prepared pstmt = conn.prepare(sql);
    string today = getToday;
    pstmt.setArgs(empid, today);
    Row[] rows = conn.query(pstmt).array;
    if(rows.length == 0) return true;
    return false;  
  }
  
  string getToday()
  {
    Row[] rows = conn.query("select curdate() + 0").array;
    return to!string(rows[0][0]);
  }
  
  Timecard[] getTimecards(bool loggedIn)
  {
    Timecard[] cards;
    string sql;
    if(loggedIn)
      sql = "select * from timecards order by timein desc";
    else
      sql = "select * from timecards where date(timein) = curdate() order by timein desc";
    Row[] rows = conn.query(sql).array;
    if(rows.length != 0)
    {
      foreach(row; rows)
      {
        Timecard c;
        c.id = to!int(to!string(row[0]));
        c.empid = to!string(row[1]);
        c.fullname = to!string(row[2]);
        c.timein = to!string(row[3]);
        c.timeout = to!string(row[4]);
        c.hours = to!float(to!string(row[5]));
        cards ~= c;
      }
    }
    return cards;
  }
  
  bool noTimeOut(string empid)
  {
    string sql = "select * from timecards where timeout is null and empid=? and date(timein)=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid, getToday);
    Row[] rows = conn.query(pstmt).array;
    if(rows.length != 0) return true;
    return false;  
  }
  
  bool timeOut(string empid)
  {
    // we want to include the fraction of the hour
    // so we use the MySQL function time_to_sec()
    // 3600 is seconds in an hour
    // but we round the result to just two decimals
    string sql =
    "update timecards set timeout=now(),
      hours=round((time_to_sec(now())-time_to_sec(timein))/3600,2)
      where empid=? and date(timein)=curdate()";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(empid);
    if(conn.exec(pstmt) > 0) return true;
    return false;
  }
  
  Timecard getTimecard(int id)
  {
    string sql = "select * from timecards where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    Row[] rows = conn.query(pstmt).array;
    Timecard c;
    if(rows.length > 0)
    {
      Row row = rows[0];
      c.id = to!int(to!string(row[0]));
      c.empid = to!string(row[1]);
      c.fullname = to!string(row[2]);
      c.timein = to!string(row[3]);
      c.timeout = to!string(row[4]);
      c.hours = to!float(to!string(row[5]));
    }
    return c;
  }
  
  void editTimecard(Timecard t)
  {
    Prepared pstmt;
    if(to!string(t.timeout) == "null")
    {
      string sql =
        "update timecards set
        timein=str_to_date(?,'%Y-%b-%d %H:%i:%S'),
        timeout=null
        where id=?";
      pstmt = conn.prepare(sql);
      pstmt.setArgs(t.timein, t.id);
    }
    else
    {
      string sql =
        "update timecards set
        timein=str_to_date(?,'%Y-%b-%d %H:%i:%S'),
        timeout=str_to_date(?,'%Y-%b-%d %H:%i:%S'),
        hours=round((time_to_sec(timeout)-time_to_sec(timein))/3600,2)
        where id=?";
      pstmt = conn.prepare(sql);
      pstmt.setArgs(t.timein, t.timeout, t.id);
    }
    conn.exec(pstmt);
  }
  
  void deleteTimecard(int id)
  {
    string sql = "delete from timecards where id=?";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(id);
    conn.exec(pstmt);
  }
  
  int[] getTheWeeks()
  {
    string sql = "select distinct week(timein) as weeks from timecards order by weeks";
    Row[] rows = conn.query(sql).array;
    int[] weeks;
    foreach (row; rows) weeks ~= to!int(to!string(row[0]));
    return weeks;
  }
}

The timesheet controller

Here is source\sheetcontrol.d:

module sheetcontrol;
import vibe.vibe;
import basecontrol;
import empmodel;
import cardmodel;
import sheetmodel;

class TimesheetController : BaseController
{
  @auth
  void getCreateTimesheet(string _authUser, string _error = null)
  {
    string error = _error;
    int[] weeks = cardModel.getTheWeeks;
    bool loggedIn = m_user.loggedIn;
    render!("sheetcreate.dt", error, loggedIn, weeks);
  }
  
  @errorDisplay!getCreateTimesheet
  void postCreateTimesheet(int week)
  {
    sheetModel.createTimesheet(week);
    redirect("timesheets");
  }
  
  void getTimesheets()
  {
    Timesheet[] sheets = sheetModel.getTimesheets;
    bool loggedIn = m_user.loggedIn;
    int week = sheetModel.getTheWeek;
    render!("sheetlist.dt", sheets, loggedIn, week);
  }
}

The timesheet model

Here is source\sheetmodel.d:

module sheetmodel;
import mysql;
import std.array;
import std.conv;
import basemodel;

struct Timesheet
{
  int id;
  int period;
  string empid;
  string fullname;
  float hours;
}

class TimesheetModel : BaseModel
{
  void createTable()
  {
    string sql = "drop table if exists timesheets";
    conn.exec(sql);
    sql =
      "create table timesheets
      (
        id int auto_increment primary key,
        period int not null,
        empid char(4) not null,
        fullname varchar(50) not null,
        hours float
      )";
    conn.exec(sql);
  }
  
  void createTimesheet(int week)
  {
    string sql = "delete from timesheets";
    conn.exec(sql);
    sql =
      "insert into timesheets(period, hours, empid, fullname)
        (select week(timein) as period, sum(hours), empid, fullname from timecards
        where week(timein)=? group by period, empid, fullname)";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs(week);
    conn.exec(pstmt);
  }
  
  Timesheet[] getTimesheets()
  {
    string sql = "select * from timesheets order by empid";
    Row[] rows = conn.query(sql).array;
    Timesheet[] sheets;
    foreach (row; rows)
    {
      Timesheet t;
      t.id = to!int(to!string(row[0]));
      t.period = to!int(to!string(row[1]));
      t.empid = to!string(row[2]);
      t.fullname = to!string(row[3]);
      t.hours = to!double(to!string(row[4]));
      sheets ~= t;
    }
    return sheets;
  }
  
  int getTheWeek()
  {
    Row[] rows = conn.query("select period from timesheets limit 1").array;
    if(rows.length == 0) return 0;
    return to!int(to!string(rows[0][0]));
  }
}

The CSS files

Here is views\cssclock.dt:

:css
  .clock-wrapper
  {
    margin: 0 10px;
    text-align: center;
  }
  .clock-grid
  {
    margin: 0 auto;
    padding: 0;
    width: 900px;
    height: auto;
    display: grid;
    grid-template: 90px auto / repeat(12, 1fr);
    grid-template-areas:
      "t0 t0 t0 t0 t0 t0 t0 t0 t0 t0 t0 t0"
      "ci ci ci ci ci ci co co co co co co";
    gap: 20px;
    color: #444;
    text-align: center;
  }
  .clock-heading
  {
    margin-bottom: 10px;
    padding: 0;
    grid-area: t0;
    line-height: 90px;
    color: black;
    font-weight: bold;
    text-align: center;
  }
  #clock-title
  {
    font-size: 30px;
  }
  .clock-text-center
  {
    text-align: center;
  }
  .clock-area
  {
    font-size: 40px;
    font-weight: bold;
    text-align: center;
    width: 370px;
    height: 400px;
    margin: 10px auto;
    padding: 0;
  }
  #clock-in
  {
    grid-area: ci;
  }
  #clock-out
  {
    grid-area: co;
  }
  .clock-btn
  {
    margin: 20px auto;
    padding: 10px 60px;
    border: none;
    background: #363636;
    width: 100%;
    transition: ease-in all 0.3s;
    color: #fff;
    height: auto;
    text-align: center;
    border-radius: 30px;
    font-size: 20px;
    font-weight: bold;
    text-decoration: none;
    cursor: pointer;
  }
  .clock-btn:hover
  {
    background: brown;
    color: #fff;
  }
  #clock-time
  {
    font-size: 26px;
    margin: 100px auto 0 auto;
    color: black;
    text-align: center;
  }
  #clock-form-select
  {
    margin: 0 auto;
    text-align: center;
    font-size: 20px;
    font-weight: bold;
  }
  .success
  {
    font-size:16px;
    font-weight: bold;
    color: black;
    text-align: center;
  }

Here is views\cssfooter.dt:

:css
  footer
  {
    position: fixed;
    bottom: 0;
    margin-top: 15px;
    background: #076;
    padding: 5px 0;
    width: 100%;
  }
  .copyright
  {
    font-family: Verdana, Geneva, Tahoma, sans-serif;
    font-size: 14px;
    text-align: center;
    color: white;
  }

Here is views\cssformgrid.dt:

:css
  .form-grid-wrapper
  {
    width: 500px;
    margin: 20px;
    padding: 1px 20px 20px 0;
    border-radius: 20px;
  }
  .form-grid
  {
    display: grid;
    grid-template-columns: 1fr 1fr;
    gap: 10px;
    padding-top: 5px;
  }
  .form-grid-label
  {
    width: 100%;
    font-size: 16px;
    text-align: right;
    padding: 2px;
  }
  .form-grid-input
  {
    width: 100%;
    font-size: 14px;
    padding: 0;
  }
  .form-grid-field
  {
    width: 100%;
    font-size: 16px;
    border-bottom: 1px solid black;
    padding: 2px;
  }
  .form-grid-button
  {
    width: 100%;
    font-size: 14px;
    height: 30px;
    margin-top: 10px;
  }
  .form-hidden
  {
    padding: 0;
    margin: 0;
    display: inline;
  }

Here is views\csslayout.dt:

:css
  html, body, container
  {
    margin: 0;
    padding: 0;
    width: 100%;
    height: 100%;
  }
  .container
  {
    display: grid;
    grid-template-rows: 38px auto 10px;
    background-color: cyan;
  }
  main
  {
    margin: 0;
    padding: 40px 15px;
    font-family: Sans-serif;
    width: auto;
    height: 100%;
  }
  .text-control
  {
    grid-column: 1 / 3;
  }
  #but-reset
  {
    grid-column: 1 / 2;
  }
  #but-submit
  {
    grid-column: 2 / 3;
  }
  .error-div
  {
    margin-bottom: 5px;
  }
  .error-message
  {
    color: brown;
    font-size: 16px;
    font-weight: bold;
    text-align: left;
  }
  .center-align
  {
    margin: 0 auto;
    text-align: center;
  }
  .right-align
  {
    text-align: right;
  }

Here is views\cssmenu.dt:

:css
  nav
  {
    position: fixed;
    top: 0;
    margin: 0;
    padding: 0;
    display: inline-block;
    background: #076;
    font-family: Verdana, Geneva, Tahoma, sans-serif;
    width: 100%;
  }
  nav ul
  {
    margin:0;
    padding:0;
    list-style-type:none;
    float:left;
    display:inline-block;
  }
  nav ul li
  {
    position: relative;
    margin: 0;
    float: left;
    display: inline-block;
  }
  li > a:only-child:after { content: ''; }
  nav ul li a
  {
    padding: 15px 20px;
    display: inline-block;
    color: white;
    text-decoration: none;
  }
  nav ul li a:hover
  {
    opacity: 0.5;
  }
  nav ul li ul
  {
    display: none;
    position: absolute;
    left: 0;
    background: #076;
    float: left;
    width: 190px;
  }
  nav ul li ul li
  {
    width: 100%;
    border-bottom: 1px solid rgba(255,255,255,.3);
  }
  nav ul li ul li a
  {
    padding: 10px 20px;
  }
  nav ul li:hover ul
  {
    display: block;
  }
  .link-right
  {
    float: right;
    margin-right: 20px;
  }
  .modal-form
  {
    position: fixed;
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
    top: 0;
    right: 0;
    bottom: 0;
    left: 0;
    background: rgba(0,0,0,0.5);
    z-index: 99999;
    opacity: 0;
    pointer-events: none;
  }
  #login:target, #find_employee:target
  {
    opacity: 1;
    pointer-events: auto;
  }
  .modal-form-grid
  {
    display: grid;
    grid-template: 30px 30px 30px / 1fr 1fr;
    grid-gap: 10px;
  }
  .modal-form-wrapper-login
  {
    width: 250px;
    position: absolute;
    right: 0;
    margin-top: 40px;
    padding: 25px 20px;
    border-radius: 10px;
    text-align: center;
    background-color: whitesmoke;
  }
  .modal-form-wrapper-find_employee
  {
    width: 250px;
    position: relative;
    left: 220px;
    margin-top: 40px;
    padding: 25px 20px;
    border-radius: 10px;
    text-align: center;
    background-color: whitesmoke;
  }

Here is views\csstable.dt:

:css
  .table-wrapper
  {
    margin: 10px 0;
    padding-bottom: 40px;
  }
  table
  {
    padding: 10px 20px;
    border-spacing: 0;
    border-collapse: collapse;
  }
  table td, table th
  {
    margin: 0;
    padding: 2px 10px;
    text-align: left;
  }
  .no-border
  {
    border: 0;
  }
  tr:nth-child(odd)
  {
    background-color: #DADADA;
}

The timecard views

views\cardelete.dt:

extends layout
block maincontent
  div.form-grid-wrapper
    h2 Delete this time card entry?
    form.form-grid(method="post", action="delete_timecard")
      label.form-grid-label Employee :
      label.form-grid-text #{e.empid} - #{e.fname} #{e.lname}
      label.form-grid-label Punched in :
      label.form-grid-text #{t.timein}
      label.form-grid-label Punched out :
      label.form-grid-text #{t.timeout}
      input(type="hidden", name="id", value="#{t.id}")
      div
        a(href="timecards")
          button.form-grid-button(type="button") Cancel
      div
        input.form-grid-button(type="submit", value="Delete")

views\cardedit.dt:

extends layout
block maincontent
  div.form-grid-wrapper
    h2 Edit time card entry
    -if(error)
      div.error-div
        span.error-message #{error}
    form.form-grid(method="post", action="edit_timecard")
      label.form-grid-label Employee :
      label.form-grid-text #{t.empid} - #{t.fullname}
      label.form-grid-label Time in :
      input.form-grid-input(type="text", name="t_timein", value="#{t.timein}")
      label.form-grid-label Time out :
      input.form-grid-input(type="text", name="t_timeout", value="#{t.timeout}")
      div
      input(type="hidden", name="t_id", value="#{t.id}")
      input(type="hidden", name="t_empid", value="#{t.empid}")
      input(type="hidden", name="t_fullname", value="#{t.fullname}")
      input(type="hidden", name="t_hours", value="#{t.hours}")
      div
      div
        a(href="timecards")
          button.form-grid-button(type="button") Cancel
      div
        input.form-grid-button(type="submit", value="Submit")

views\cardlist.dt:

extends layout
block maincontent
  div.table-wrapper
    -if(loggedIn)
      h2 All time cards
    -else
      h2 #{today}
    table
      tr
        th Emp #
        th Name
        th Time in
        th Time out
        th Hours
        -if(loggedIn)
          th Action
      -foreach(c; cards)
        tr
          td #{c.empid}
          td #{c.fullname}
          td #{c.timein}
          td #{c.timeout}
          td #{c.hours}
          -if(loggedIn)
            td  
              form.form-hidden(method="get", action="edit_timecard")
                input(type="hidden", name="id", value="#{c.id}")
                input(type="image", src="images/pencil.png", height="15px")
              |  
              form.form-hidden(method="get", action="delete_timecard")
                input(type="hidden", name="id", value="#{c.id}")
                input(type="image", src="images/trash.png", height="15px")
              |  

views\timein.dt:

extends layout
block maincontent
  div.main
    div.clock-wrapper
      div.clock-grid
        div.clock-heading#clock-title Lorem Ipsum Company Timekeeping System
        form#clock-in.clock-area(method="post", action="time_in")
          div#clock-time
          select#clock-form-select(name="empidname")
            -foreach(e; emps)
              option(value="#{e.empid} #{e.fname} #{e.lname}") #{e.empid} #{e.fname} #{e.lname}
          input.clock-btn(type="submit", value="Punch In")
          -if(error)
            div.error-div
              span.error-message #{error}
          -else if(success)
            div.success #{success}
        div#clock-out.clock-area
          img(src="images/timeout.png", alt="photo of a clock")
          a.clock-btn(href="time_out") Punch Out
    :javascript
      const displayTime = document.querySelector("#clock-time");
      function showTime() {
        let time = new Date();
        displayTime.innerText = time.toLocaleString("en-CA", { hour12: true });
        setTimeout(showTime, 1000);
      }
      showTime();

views\timeout.dt:

extends layout
block maincontent
  div.main
    div.clock-wrapper
      div.clock-grid
        div.clock-heading#clock-title Lorem Ipsum Company Timekeeping System
        div#clock-in.clock-area
          img(src="images/timein.png", alt="photo of a clock")
          a.clock-btn(href="time_in") Punch In
        form#clock-out.clock-area(method="post", action="time_out")
          div#clock-time
          select#clock-form-select(name="empidname")
            -foreach(e; emps)
              option(value="#{e.empid} #{e.fname} #{e.lname}") #{e.empid} #{e.fname} #{e.lname}
          input.clock-btn(type="submit", value="Punch Out")
          -if(error)
            div.error-div
              span.error-message #{error}
          -else if(success)
            div.success #{success}
    :javascript
      const displayTime = document.querySelector("#clock-time");
      function showTime() {
        let time = new Date();
        displayTime.innerText = time.toLocaleString("en-CA", { hour12: true });
        setTimeout(showTime, 1000);
      }
      showTime();

The employee views

views\ empadd.dt:

extends layout
block maincontent
  -if(error)
    div.error-div
      span.error-message #{error}
  div.form-grid-wrapper
    h2.center-align New employee details
    br
    form.form-grid(method="post", action="add_employee", enctype="multipart/form-data")
      label.form-grid-label Employee number
      input.form-grid-input(type="empid", name="e_empid", placeholder="employee number", required)
      label.form-grid-label Email address
      input.form-grid-input(type="email", name="e_email", placeholder="[e-mail]", required)
      label.form-grid-label Password
      input.form-grid-input(type="password", name="e_passw", placeholder="password", required)
      label.form-grid-label First name
      input.form-grid-input(type="text", name="e_fname", placeholder="First name", required)
      label.form-grid-label Last name
      input.form-grid-input(type="text", name="e_lname", placeholder="Last name", required)
      label.form-grid-label Phone
      input.form-grid-input(type="text", name="e_phone", placeholder="Phone number")
      label.form-grid-label Department
      select#deprt.form-grid-input(name="e_deprt")
        -foreach(dep; departments)
          option(value="#{dep}") #{dep}
      label.form-grid-label Salary grade
      select#deprt.form-grid-input(name="e_payrate")
        -foreach(pay; payrates)
          option(value="#{pay}") #{pay}
      label.form-grid-label Street address (no city)
      input.form-grid-input(type="text", name="e_street", placeholder="Street address", required)
      label.form-grid-label City
      input.form-grid-input(type="text", name="e_city", placeholder="City", required)
      label.form-grid-label Province
      select#province.form-grid-input(name="e_province")
        -foreach(prov; provinces)
          option(value="#{prov[0]}") #{prov[1]}
      label.form-grid-label Postal code
      input.form-grid-input(type="text", name="e_postcode", placeholder="A1A 1A1")
      label.form-grid-label ID Picture
      input.form-grid-input(type="file", name="picture")
      input(type="hidden", name="e_photo")
      input(type="hidden", name="e_id", value="1")
      input.form-grid-button(type="reset", value="Clear form")
      input.form-grid-button(type="submit", value="Submit")

views\empdelete.dt:

extends layout
block maincontent
  -if(error)
    div.error-div
      span.error-message #{error}
  div.form-grid-wrapper
    h2.center-align Delete #{e.fname} #{e.lname}'s record?
    br
    form.form-grid(method="post", action="delete_employee")
      span.form-grid-label Employee number:
      span.form-grid-field #{e.empid}
      span.form-grid-label Email address:
      span.form-grid-field #{e.email}
      span.form-grid-label First name:
      span.form-grid-field #{e.fname}
      span.form-grid-label Last name:
      span.form-grid-field #{e.lname}
      span.form-grid-label Phone:
      span.form-grid-field #{e.phone}
      span.form-grid-label Department:
      span.form-grid-field #{e.deprt}
      span.form-grid-label Salary grade:
      span.form-grid-field #{e.payrate}
      span.form-grid-label Street address:
      span.form-grid-field #{e.street}
      span.form-grid-label City:
      span.form-grid-field #{e.city}
      span.form-grid-label Province:
      span.form-grid-field #{e.province}
      span.form-grid-label Postal code:
      span.form-grid-field #{e.postcode}
      span.form-grid-label ID Picture:
      img(src="#{e.photo}", height="80px")
      input(type="hidden", name="id", value="#{e.id}")
      a(href="all_employees")
        button.form-grid-button(type="button") Cancel
      input.form-grid-button(type="submit", value="Delete")

views\empedit.dt:

extends layout
block maincontent
  -if(error)
    div.error-div
      span.error-message #{error}
  div.form-grid-wrapper
    h2.center-align Edit #{e.fname} #{e.lname} details
    br
    form.form-grid(method="post", action="edit_employee", enctype="multipart/form-data")
      label.form-grid-label Employee number
      input.form-grid-input(type="empid", name="e_empid", value="#{e.empid}")
      label.form-grid-label Email address
      input.form-grid-input(type="email", name="e_email", value="#{e.email}")
      label.form-grid-label Password
      input.form-grid-input(type="password", name="e_passw", value="#{e.passw}")
      label.form-grid-label First name
      input.form-grid-input(type="text", name="e_fname", value="#{e.fname}")
      label.form-grid-label Last name
      input.form-grid-input(type="text", name="e_lname", value="#{e.lname}")
      label.form-grid-label Phone
      input.form-grid-input(type="text", name="e_phone", value="#{e.phone}")
      label.form-grid-label Department
      select#deprt.form-grid-input(name="e_deprt")
        -foreach(dep; departments)
          -if(dep == e.deprt)
            option(value="#{dep}", selected) #{dep}
          -else
          option(value="#{dep}") #{dep}
      label.form-grid-label Salary grade
      select#paygd.form-grid-input(name="e_payrate", value="#{e.payrate}")
        -foreach(pay; payrates)
          -if(pay == e.payrate)
            option(value="#{pay}", selected) #{pay}
          -else
            option(value="#{pay}") #{pay}
      label.form-grid-label Street address (no city)
      input.form-grid-input(type="text", name="e_street", value="#{e.street}")
      label.form-grid-label City
      input.form-grid-input(type="text", name="e_city", value="#{e.city}")
      label.form-grid-label Province
      select#province.form-grid-input(name="e_province", value="#{e.province}")
        -foreach(prov; provinces)
          -if(prov[0] == e.province)
            option(value="#{prov[0]}", selected) #{prov[1]}
          -else
            option(value="#{prov[0]}") #{prov[1]}
      label.form-grid-label Postal code
      input.form-grid-input(type="text", name="e_postcode", value="#{e.postcode}")
      label.form-grid-label ID Picture
      img(src="#{e.photo}", height="100px")
      div
      input.form-grid-input(type="file", name="picture")
      input(type="hidden", name="prevPassw", value="#{e.passw}")
      input(type="hidden", name="e_photo", value="#{e.photo}")
      input(type="hidden", name="e_id", value="#{e.id}")
      input(type="hidden", name="id", value="#{e.id}")
      a(href="all_employees")
        button.form-grid-button(type="button") Cancel
      input.form-grid-button(type="submit", value="Submit")

views\emplist.dt:

extends layout
block maincontent
  h2 Lorem Ipsum Employees
  div.table-wrapper
    -if(error)
      div.error-div
        span.error-message #{error}
    table
      tr
        th Emp #
        th Name
        th Department
        th Phone number
        th Email address
        th Action
      -foreach(e; emps)
        tr
          td #{e.empid}
          td #{e.fname} #{e.lname}
          td #{e.deprt}
          td #{e.phone}
          td #{e.email}
          td  
            form.form-hidden(method="get", action="edit_employee")
              input(type="hidden", name="id", value="#{e.id}")
              input(type="image", src="images/pencil.png", height="15px")
            |  
            form.form-hidden(method="get", action="delete_employee")
              input(type="hidden", name="id", value="#{e.id}")
              input(type="image", src="images/trash.png", height="15px")
            |  

views\empshow.dt:

extends layout
block maincontent
  div.form-grid-wrapper
    h2.center-align #{e.fname} #{e.lname} details
    br
    div.form-grid
      span.form-grid-label Employee number:
      span.form-grid-field #{e.empid}
      span.form-grid-label Email address:
      span.form-grid-field #{e.email}
      span.form-grid-label First name:
      span.form-grid-field #{e.fname}
      span.form-grid-label Last name:
      span.form-grid-field #{e.lname}
      span.form-grid-label Phone:
      span.form-grid-field #{e.phone}
      span.form-grid-label Department:
      span.form-grid-field #{e.deprt}
      span.form-grid-label Salary grade:
      span.form-grid-field #{e.payrate}
      span.form-grid-label Street address:
      span.form-grid-field #{e.street}
      span.form-grid-label City:
      span.form-grid-field #{e.city}
      span.form-grid-label Province:
      span.form-grid-field #{e.province}
      span.form-grid-label Postal code:
      span.form-grid-field #{e.postcode}
      span.form-grid-label ID Picture:
      img(src="#{e.photo}", height="80px")
      a(href="all_employees")
        button.form-grid-button(type="button") Close
      a(href="edit_employee?id=#{e.id}")
        button.form-grid-button(type="button") Edit

The layout views

views\layout.dt:

doctype 5
html
  head
    title Employee Timekeeping System
    include cssclock
    include cssfooter
    include cssformgrid
    include csslayout
    include cssmenu
    include csstable
  body
    div.container
      nav
        include menu
      main.content
        block maincontent
      footer
        include footer

views\menu.dt:

ul
  li
    a(href="/") Home
  li
    a(href="#") Timecards
    ul
      li
        a(href="timecards") View timecards
      li
        a(href="create_timesheet") Create timesheet
  li
    a(href="#") Employees
    ul
      li
        a(href="all_employees") View employees
      -if(loggedIn)
        li
          a(href="#find_employee") Find an employee
      -else
        li
          a(href="#login") Find an employee
      li
        a(href="add_employee") New employee
ul.link-right
  li
    -if(loggedIn)
      a(href="logout") Logout
    -else
      a(href="#login") Login
div#find_employee.modal-form
  div.modal-form-wrapper-find_employee
    form.modal-form-grid(method="post", action="find_employee")
      input.text-control(name="fname", type="text", placeholder=" First name", required)
      input.text-control(name="lname", type="text", placeholder=" Last name", required)
      input#but-reset(type="reset", value="Clear")
      input#but-submit(type="submit", value="Find")
    br
    a.close(href="#close") Cancel
div#login.modal-form
  div.modal-form-wrapper-login
    form.modal-form-grid(method="post", action="login")
      input.text-control(name="email", type="email", placeholder=" email address")
      input.text-control(name="password", type="password", placeholder=" password")
      input#but-reset(type="reset", value="Clear")
      input#but-submit(type="submit", value="Login")
    br
    a.close(href="#close") Cancel
views\footer.dt:
div.copyright Copyright © The Lorem Ipsum Company 2023

views\footer.dt

div.copyright Copyright © The Lorem Ipsum Company 2023

The home view

views\index.dt:

extends layout
block maincontent
  div.main
    div.clock-wrapper
      div.clock-grid
        div.clock-heading#clock-title Lorem Ipsum Company Timekeeping System
        div#clock-in.clock-area
          img(src="images/timein.png", alt="photo of a clock")
          a.clock-btn(href="time_in") Punch In
        div#clock-out.clock-area
          img(src="images/timeout.png", alt="photo of a clock")
          a.clock-btn(href="time_out") Punch Out

The timesheet views

views\sheetcreate.dt:

extends layout
block maincontent
  include cssformgrid.dt
  div.form-grid-wrapper
    -if(error)
      div.error-div
        span.error-message #{error}
    form.form-grid(method="post", action="create_timesheet")
      div
        label.form-grid-label For week number
        select.form-grid-input(name="week")
          -foreach(w; weeks)
            option(value="#{w}") #{w}
      div
      input.form-grid-button(type="submit", value="Generate timesheet")

views\sheetlist.dt:

extends layout
block maincontent
  h2 Timesheet for week #{week}
  div.table-wrapper
    table
      tr
        th Emp #
        th Name
        th Hours
      -foreach(s; sheets)
        tr
          td #{s.empid}
          td #{s.fullname}
          td.right-align #{s.hours}

The project configuration file

dub.json:

{
  "authors": [
    "Owner"
  ],
  "copyright": "Copyright © 2023, Owner",
  "dependencies": {
    "mysql-native": "~>3.2.2",
    "vibe-d": "~>0.9"
  },
  "description": "A simple vibe.d server application.",
  "license": "proprietary",
  "name": "loremtime"
}

…and that’s the last one.

That’s all, bye!