# The EmployeeModel class

A data-entry form in Vibe.d needs a **struct** data type from which it can extract the fields for saving into the database table. It makes things simpler if the database table, the **struct** and the form have a one-to-one correspondence in field names.

We will create a **struct** data structure that mimics an employee record. We will call it **Employee**.

Then we create a class that contains the methods to manipulate that **struct**, such as add, edit, get and delete, in short, all the CRUD operations on the MySQL server table. It is a model that represents the employees table on the MySQL server, so we will call that class **EmployeeModel**.

We are going to presume that this app will only be deployed in an intranet setting and only a few people have access, and that there is a rare chance of more than one person using the app at the same time, so we won’t have to deal with security, HTTPS encryption, multiple simultaneous users, etc.

Let us create the EmployeeModel class inside the **empmodel** module. By convention, modules and filenames have the same name for convenience. Create **source\empmodel.d** with this contents:

```d
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;
    insertIntoAdmins(); // be sure to remove this after the first run!
  }
  
  void insertIntoAdmins()
  {
    import vibe.http.auth.digest_auth;
    string email1 = "admin1@lorem.com";
    string email2 = "admin2@lorem.com";
    string email3 = "admin3@lorem.com";
    string realm = "The Lorem Ipsum Company";
    string pass1 = createDigestPassword(realm, email1, "secret");
    string pass2 = createDigestPassword(realm, email2, "secret");
    string pass3 = createDigestPassword(realm, email3, "secret");
    string sql = "insert into admins(email, pword)
values ('" ~ email1 ~ "','" ~ pass1 ~ "')";
    conn.exec(sql);
    sql = "insert into admins(email, pword)
values ('" ~ email2 ~ "','" ~ pass2 ~ "')";
    conn.exec(sql);
    sql = "insert into admins(email, pword)
values ('" ~ email3 ~ "','" ~ pass3 ~ "')";
    conn.exec(sql);
  }
  
  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);
  }
}
```

We created the **departments**, the **provinces** and the **paygrades** array variables for forms that require drop-down options for departments, provinces and salary grades.

Also, there is an **insertIntoAdmins()** method that adds three records to the **admins** table as our test administrators. Be sure to comment this out or erase after the first compilation and run.

In the **this()** constructor of the **EmployeeModel** class, we get a connection to the MySQL server running at localhost (127.0.0.1), meaning, our own local machine where we installed MySQL.

The connection string we use for connecting to the MySQL server with **mysq-native** is

    `string url = "host=localhost;port=3306;user=owner;pwd=qwerty;db=empdb";`

Of course, replace the user and the password fields with you own username and password for MySQL.

After that, we can connect to the **empdb** database through the **conn** variable.

The line

    `scope(exit) conn.close;`

means that, before the **conn** variable gets garbage-collected, its **close()** method should be called. The **addEmployee()** method shows how to insert a record in **mysql-native** syntax.

We need to import **std.conv** 

`import std.conv;`

so we can use the conversion functions of the D standard library, such as

 `to!string(e.deprt),`

which converts the data in **e.deprt** (which we will get from the web form, which we haven’t created yet) into a regular string before we save it to the database.

We need to add new records to the empty **employees** table, so we should create an employee data-entry form. Let’s do that next.