/**
* Purpose: Gets a single employee from database
* Arguments:
* EmployeeId - An Employee ID
* Return:
* Single employee from database
*/
public Employee get(int EmployeeId)
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
Employee e = null;
conn.execute(@"SELECT
EmployeeId,
FirstName,
LastName,
DepartmentId,
Administrator
FROM Employee
WHERE EndDate IS NULL AND EmployeeId = " + EmployeeId, (SqliteDataReader reader) => {
while (reader.Read())
{
e = new Employee
{
EmployeeId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString(),
DepartmentId = reader.GetInt32(3),
Administrator = Convert.ToBoolean(reader.GetInt32(4))
};
}
});
return(e);
}
/**
* Purpose: Gets all employee records from database
* Return:
* List of all employee records
*/
public List <Employee> getAll()
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
List <Employee> list = new List <Employee>();
// Execute the query to retrieve all customers
conn.execute(@"SELECT
EmployeeId,
FirstName,
LastName,
DepartmentId,
Administrator
FROM Employee
WHERE EndDate IS NULL",
(SqliteDataReader reader) => {
while (reader.Read())
{
list.Add(new Employee
{
EmployeeId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString(),
DepartmentId = reader.GetInt32(3),
Administrator = Convert.ToBoolean(reader.GetInt32(4))
});
}
}
);
return(list);
}
public Employee getEmployeeByFullName(string fullName)
{
// Break the string into an array, whitespace as the delimiter
string[] fullNameAsArray = Regex.Split(fullName, @"\s+").Where(s => s != string.Empty).ToArray();
string firstName = fullNameAsArray[0];
string lastName = fullNameAsArray[1];
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
Employee e = null;
conn.execute(@"SELECT
EmployeeId,
FirstName,
LastName,
DepartmentId,
Administrator
FROM Employee
WHERE EndDate IS NULL AND FirstName = '" + firstName + "' AND " + "LastName = '" + lastName + "'", // pls no sql hacks :(
(SqliteDataReader reader) => {
while (reader.Read())
{
e = new Employee
{
EmployeeId = reader.GetInt32(0),
FirstName = reader[1].ToString(),
LastName = reader[2].ToString(),
DepartmentId = reader.GetInt32(3),
Administrator = Convert.ToBoolean(reader.GetInt32(4))
};
}
});
return(e);
}
/**
* Purpose: Gets all deparments from database
* Return:
* List of all department records
*/
public List <Department> getAll()
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
List <Department> list = new List <Department>();
// Execute the query to retrieve all customers
conn.execute(@"SELECT
DepartmentId,
Name
FROM Department",
(SqliteDataReader reader) =>
{
while (reader.Read())
{
list.Add(new Department
{
DepartmentId = reader.GetInt32(0),
Label = reader[1].ToString()
});
}
}
);
return(list);
}
public void CanSaveNewEmployeeToDatabase()
{
var employeeFactory = new EmployeeFactory();
int lastPK = employeeFactory.getAll().Last().EmployeeId;
Employee Jeb = new Employee()
{
FirstName = "Jeb",
LastName = "Bush",
DepartmentId = 1,
Administrator = false
};
Jeb.save();
Employee shouldBeJeb = employeeFactory.getAll().Last();
Assert.NotNull(shouldBeJeb);
Assert.NotNull(shouldBeJeb.EmployeeId);
Assert.True(shouldBeJeb.EmployeeId > lastPK);
Assert.True(Jeb.FirstName == shouldBeJeb.FirstName);
Assert.True(Jeb.LastName == shouldBeJeb.LastName);
Assert.True(Jeb.DepartmentId == shouldBeJeb.DepartmentId);
Assert.True(Jeb.Administrator == shouldBeJeb.Administrator);
var conn = new BangazonWorkforceConnection();
conn.insert($"DELETE FROM Employee WHERE EmployeeId = {shouldBeJeb.EmployeeId}");
}
/**
* Purpose: Saves an employee instance to the database
* Return:
* void
*/
public void save()
{
string query = string.Format(@"
INSERT INTO Employee
(FirstName, LastName, DepartmentId, Administrator, StartDate)
VALUES
('{0}', '{1}', '{2}', '{3}', '{4}');
",
this.FirstName,
this.LastName,
this.DepartmentId,
this.Administrator ? 1 : 0,
this.StartDate
);
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
conn.insert(query);
}
/**
* Purpose: Gets a single department from database
* Arguments:
* DepartmentId - A department ID
* Return:
* Single department from database
*/
public Department get(int DepartmentId)
{
BangazonWorkforceConnection conn = new BangazonWorkforceConnection();
Department d = null;
conn.execute(@"SELECT
DepartmentId,
Name
FROM Department
WHERE DepartmentId = " + DepartmentId, (SqliteDataReader reader) =>
{
while (reader.Read())
{
d = new Department
{
DepartmentId = reader.GetInt32(0),
Label = reader[1].ToString()
};
}
});
return(d);
}
