Finding an employee record by name

We have been finding and displaying an employee record already, so this should be trivial to us by now. However, we have always used the id field as the key. This time, we are going to use the name of the employee as the key.

Click on the Find employee menu item to test it. For now, try to find an employee by the first name and last name.

But when you click on submit, you get this error:

It is looking for the postFindEmployee() method, so let’s create it.

Edit source\empcontrol.d and append this code:

  void postFindEmployee(string fname, string lname)
  {
    import std.uni;  //so we can use toUpper() function
    string first = toUpper(fname);
    string last = toUpper(lname);
    Employee e = empModel.findEmployee(first, last);
    if(e != Employee.init) render!("employee.dt", e);
    else redirect("all_employees");
  }

We are converting the names to uppercase because we are not sure how the names were inputted in the forms and saved in the database.

Edit source\empmodel.d and define this method at the end.

  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]);
  }

In the SQL statement, we are also converting the names to uppercase before comparing. Instead of looking for the row ID, this time the method is looking for the first name and last name converted to uppercase.

Create views\employee.dt.

extends layout
block maincontent
  include cssformgrid.dt
  div.form-grid-wrapper
    h2.center-align Employee details
    div.form-grid
      span.form-grid-label Employee number:
      span.form-grid-field #{e.empid}
      span.form-grid-label Department:
      span.form-grid-field #{e.deprt}
      span.form-grid-label Salary grade:
      span.form-grid-field #{e.paygd}
      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 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")
      div
      div
        a(href="all_employees")
          button.form-grid-button(type="button") Close

Then compile, run and refresh the browser. Look for an employee and click Find. If the employee was not found, it simply shows the home page.

But if the employee was found, the record should be displayed.

So here is the full source\empcontrol.d so far.

module empcontrol;
import vibe.vibe;
import empmodel;

class EmployeeController
{
  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);
  }
  
  void getEditEmployee(int id)
  {
    Employee e = empModel.getEmployee(id);
    render!("empedit.dt", e, departments, paygrades, provinces);
  }
  
  void postEditEmployee(Employee e)
  {
    import std.file;
    import std.path;
    import std.algorithm;
    import vibe.http.auth.digest_auth;
    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.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.editEmployee(e);
    redirect("all_employees");
  }
  
  void getDeleteEmployee(int id)
  {
    Employee e = empModel.getEmployee(id);
    render!("empdelete.dt", e);
  }
  
  void postDeleteEmployee(int id)
  {
    empModel.deleteEmployee(id);
    redirect("all_employees");
  }
  
  void postFindEmployee(string fname, string lname)
  {
    import std.uni; //so we can use the toUpper() or toLower() function
    string first = toUpper(fname);
    string last = toUpper(lname);
    Employee e = empModel.findEmployee(first, last);
    if(e != Employee.init) render!("employee.dt", e);
    else redirect("all_employees");
  }
}

And 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;
  }
  
  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]);
  }
  
  ulong editEmployee(Employee e)
  {
    string sql = "update employees set empid=?,
      deprt=?, paygd=?, email=?, pword=?,
      fname=?, lname=?, phone=?, photo=?,
      street=?, city=?, province=?, postcode=?
      where id=?";
    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),
      to!int(to!string(e.id))
    );
    return 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]);
  }
}

Now we have gone through all the CRUD (create, read, update and delete) operations.

Next let’s talk about error trapping.