Listing all the employees

Edit source\empcontrol.d and add this code:

  void getAllEmployees()
  {
    Employee[] emps = empModel.getEmployees;
    render!("emplistall.dt", emps);
  }

Now, that’s a very simple method. Two lines!

But it is calling the empModel.getEmployees() method, which we haven’t written yet, and also needs the emplistall.dt file, which we haven’t created yet, so let’s resolve that.

First, let’s define the getEmployees() method at the end of source\empmodel.d:

  Employee[] getEmployees()
  {
    Employee[] emps;
    string sql = "select * from employees";
    Row[] rows = conn.query(sql).array;
    if(rows.length == 0) return emps;
    return prepareEmployees(rows);
  }

This method returns all the records in the table. But this method calls the prepareEmployees() method, so let’s define it.

  Employee[] prepareEmployees(Row[] rows)
  {
    Employee[] emps;
    foreach(row; rows)
    {
      Employee e = prepareEmployee(row);
      emps ~= e;
    }
    return emps;
  }

This method in turn calls the prepareEmployee() (singular, no ‘s’) method, so let’s define that too.

  Employee prepareEmployee(Row row)
  {
    Employee e;
    e.id = to!int(to!string(row[0]));
    e.empid = to!string(row[1]);
    e.deprt = to!string(row[2]);
    e.paygd = to!string(row[3]);
    e.email = to!string(row[4]);
    e.pword = to!string(row[5]);
    e.fname = to!string(row[6]);
    e.lname = to!string(row[7]);
    e.phone = to!string(row[8]);
    e.photo = 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;
  }

You should make sure the order of fields in prepareEmployee() follows the order of fields in the MySQL employees table. Sometimes MySQL does not save the fields in the same order as in your SQL CREATE TABLE statement. Check the Schemas view of MySQL Browser.

Now let’s create views\emplistall.dt.

extends layout
block maincontent
  include csstable.dt
  div.table-wrapper
    table
      tr
        th Employee Id
        th First name
        th Last name
        th Department
        th Phone number
        th Email address
        th Street address
        th City
        th Province
        th PostCode
        th Action
      -foreach(e; emps)
        tr
          td #{e.empid}
          td #{e.fname}
          td #{e.lname}
          td #{e.deprt}
          td #{e.phone}
          td #{e.email}
          td #{e.street}
          td #{e.city}
          td #{e.province}
          td #{e.postcode}
          td  
            form.form-hidden(method="get", action="edit_employee")
              input(type="hidden", name="id", value="#{e.id}")
              input(type="image", src="images/pencil.ico", height="15px")
            |  
            form.form-hidden(method="get", action="delete_employee")
              input(type="hidden", name="id", value="#{e.id}")
              input(type="image", src="images/trash.ico", height="15px")
            |  

As mentioned before, we can mix D code inside a Diet template file with – (hyphen)

Remember that we passed an Employee array emps to the template views\emplistall.dt:

  void getListEmployees()
  {
    Employee[] emps = empModel.getEmployees;
    render!("emplistall.dt", emps);
  }

Hence, we can inject this code:

-foreach(e; emps)

which will iterate through all of emps, which is an array of Employees, with e representing the current Employee record being processed inside the loop.

To display the value of a variable passed to the template, we use the construct #{variable}. This is equivalent to JSP’s <%= variable %> construct.

Hence the line

td #{e.deprt}

means display the value of e.deprt inside that table cell.

And here is views\csstable.dt which is needed by emplistall.dt.

:css
  .table-wrapper
  {
    margin: 20px auto;
    border-radius: 20px;
  }
  table
  {
    margin: 0 auto;
    padding: 20px 0;
    border-collapse: collapse;
    background-color: #eff;
  }
  table td, table th
  {
    border: 1px solid black;
    margin: 0;
    padding: 0 5px;
  }
  .no-border
  {
    border: 0;
  }

Compile and run the app and refresh the browser. Click on New employee again and add another employee, then click the Submit button.

And after adding some employees, you should see this:

I got the icons for the pencil and the trash bin by simply googling for ‘pencil icon’ and ‘trash bin icon’ and saving the icons into the \public\images\ folder.

In the views\emplistall.dt file, we have these lines

input(type="image", src="images/pencil.ico", height="15px")

input(type="image", src="images/trash.ico", height="15px")

so save and name the icon files in the \public\images\ folder with the same names.

Add more sample records by clicking on the Add employee link after each submission.

So here is the full source\empmodel.d so far:

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

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

struct Admin
{
  string email; //email address
  string pword; //passsword
}

string[] departments =
[
  "Management and Admin",
  "Accounting and Finance",
  "Production",
  "Maintenance",
  "Shipping and Receiving",
  "Purchasing and Supplies",
  "IT Services",
  "Human Resources",
  "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"]
];

string[] paygrades =
[
  "A100", "A200", "A300", "A400",
  "B100", "B200", "B300", "B400",
  "C100", "C200", "C300", "C400",
  "D100", "D200", "D300", "D400",
  "E100", "E200", "E300", "E400",
  "F100", "F200", "F300", "F400"
];

class EmployeeModel
{
  Connection conn;
  
  this()
  {
    string url = "host=localhost;port=3306;user=owner;pwd=qwerty;db=empdb";
    conn = new Connection(url);
    scope(exit) conn.close;
  }
  
  ulong addEmployee(Employee e)
  {
    string sql =
      "insert into employees
      (
        empid,
        deprt, paygd, email, pword,
        fname, lname, phone, photo,
        street, city, province, postcode
      )
      values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
    Prepared pstmt = conn.prepare(sql);
    pstmt.setArgs
    (
      to!string(e.empid),
      to!string(e.deprt),
      to!string(e.paygd),
      to!string(e.email),
      to!string(e.pword),
      to!string(e.fname),
      to!string(e.lname),
      to!string(e.phone),
      to!string(e.photo),
      to!string(e.street),
      to!string(e.city),
      to!string(e.province),
      to!string(e.postcode)
    );
    return conn.exec(pstmt);
  }
  
  Employee[] getEmployees()
  {
    Employee[] emps;
    string sql = "select * from employees";
    Row[] rows = conn.query(sql).array;
    if(rows.length == 0) return emps;
    return prepareEmployees(rows);
  }
  Employee[] prepareEmployees(Row[] rows)
  {
    Employee[] emps;
    foreach(row; rows)
    {
      Employee e = prepareEmployee(row);
      emps ~= e;
    }
    return emps;
  }
  
  Employee prepareEmployee(Row row)
  {
    Employee e;
    e.id = to!int(to!string(row[0]));
    e.empid = to!string(row[1]);
    e.deprt = to!string(row[2]);
    e.paygd = to!string(row[3]);
    e.email = to!string(row[4]);
    e.pword = to!string(row[5]);
    e.fname = to!string(row[6]);
    e.lname = to!string(row[7]);
    e.phone = to!string(row[8]);
    e.photo = 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;
  }
}

And here is the full source\empcontrol.d file so far:

module empcontrol;
import vibe.vibe;
import empmodel;

class EmployeeController
{
  private EmployeeModel empModel;
  private string realm = "The Lorem Ipsum Company";
 
  this()
  {
    empModel = new EmployeeModel;
  }
  
  void index()
  {
    render!("index.dt");
  }
  
  void getAddEmployee()
  {
    render!("empadd.dt", departments, paygrades, provinces);
  }
  
  void postAddEmployee(Employee e)
  {
    import std.file;
    import std.path;
    import std.algorithm;
    import vibe.http.auth.digest_auth;
    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.paygd.length == 0) e.paygd = "none yet";
    if(e.postcode.length == 0) e.postcode = "A1A 1A1";
    e.pword = createDigestPassword(realm, e.email, e.pword);
    empModel.addEmployee(e);
    redirect("all_employees");
  }
  
  void getAllEmployees()
  {
    Employee[] emps = empModel.getEmployees;
    render!("emplistall.dt", emps);
  }
}

Now let’s implement the editing of records to make the pencil icon functional.