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 = "(123) 456 7890";
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.
Last updated