/// <summary>
/// Gets an employee from the database with the matching id, returning an empty employee otherwise
/// </summary>
/// <param name="id">The id of the employee to pull from the database</param>
/// <returns>An employee from the database with the matching id, returning an empty employee otherwise</returns>
public static DBEmployee GetEmployee(int id)
{
using (var conn = new SqlConnection(Properties.Settings.Default.Britannicus_DBConnectionString))
using (var command = new SqlCommand("GetEmployeeById", conn)
{
CommandType = CommandType.StoredProcedure
})
{
conn.Open();
command.Parameters.Add("@id", SqlDbType.Int).Value = id;
var reader = command.ExecuteReader();
DBEmployee tempEmployee;
if (reader.Read())
{
tempEmployee = new DBEmployee((int)reader["employeeID"], (int)reader["positionID"],
(string)reader["firstName"], (string)reader["lastName"], (string)reader["phoneNumber"]);
}
else
{
tempEmployee = new DBEmployee(id, 0, "", "", "");
}
return(tempEmployee);
}
}
/// <summary>
/// Queries the database to find a matching combination of username and password
/// then grabs the employee the account belongs to
/// </summary>
/// <param name="username">The username to query for</param>
/// <param name="password">The password to query for</param>
/// <returns>True if successful</returns>
public static bool Login(string username, string password)
{
bool result = false;
using (var conn = new SqlConnection(Properties.Settings.Default.Britannicus_DBConnectionString))
using (var command = new SqlCommand("Login", conn)
{
CommandType = CommandType.StoredProcedure
})
{
conn.Open();
command.Parameters.Add("@username", SqlDbType.VarChar).Value = username;
command.Parameters.Add("@password", SqlDbType.VarChar).Value = sha256(password);
var reader = command.ExecuteReader();
if (reader.Read())
{
DBUser.Employee = new DBEmployee((int)reader["employeeID"], (int)reader["positionID"], (string)reader["firstName"], (string)reader["lastName"], (string)reader["phoneNumber"]);
DBUser.Username = username;
DBUser.LastAccess = (DateTime)reader["lastAccess"];
result = true;
}
}
return(result);
}
private void BtnAdd_Click(object sender, EventArgs e)
{
MasterForm master = (this.Parent.Parent as MasterForm);
try
{
//Input data gathered here to improve readability and centralize any processing
//that needs to be done before insertion
int roleID = (int)this.cbxRoleInput.SelectedValue;
string firstName = this.txtFirstNameInput.Text.Trim();
string lastName = this.txtLastNameInput.Text.Trim();
string phoneNumber = this.mtxtPhoneNumberInput.Text.Trim();
string status = "";
if (String.IsNullOrEmpty(status = DBEmployee.Validate(firstName, lastName, phoneNumber)))
{
if (DBEmployee.InsertEmployee(roleID, firstName, lastName, phoneNumber))
{
status = "Employee " + firstName + " " + lastName + " has been added.";
}
}
master.SetStatus(status);
}
catch (Exception ex)
{
master.SetStatus("Error! Add failed: " + ex.Message);
}
}
private void ShowEmployee()
{
DBEmployee tempEmployee = DBEmployee.GetEmployee((int)this.nudEmployeeIdInput.Value);
this.txtFirstNameInput.Text = tempEmployee.FirstName;
this.txtLastNameInput.Text = tempEmployee.LastName;
this.mtxtPhoneNumberInput.Text = tempEmployee.PhoneNumber;
this.cbxRoleInput.SelectedValue = tempEmployee.PositionID;
}
private void ReloadData()
{
this.employees = DBEmployee.GetEmployees();
//this.txtSearchName.DataBindings.Clear();
//this.mtxtPhoneNumber.DataBindings.Clear();
this.dgvEmployees.DataSource = this.employees;
this.cbxSelectedEmployee.DataSource = this.employees;
//this.txtSearchName.DataBindings.Add("Text", this.employees, "FullName", true, System.Windows.Forms.DataSourceUpdateMode.Never);
//this.mtxtPhoneNumber.DataBindings.Add("Text", this.employees, "PhoneNumber", true, System.Windows.Forms.DataSourceUpdateMode.Never);
}
private void FilterEmployees(string name, string phoneNumber)
{
BindingList <DBEmployee> filteredEmployees = new BindingList <DBEmployee>();
foreach (DBEmployee c in this.employees)
{
if (c.FullName.ToLower().Contains(name.Trim().ToLower()) && DBEmployee.IsPhoneNumberMatching(c.PhoneNumber, this.mtxtPhoneNumber.Text))
{
filteredEmployees.Add(c);
}
}
this.dgvEmployees.DataSource = filteredEmployees;
this.cbxSelectedEmployee.DataSource = filteredEmployees;
}
/// <summary>
/// Filters through a list of collectors and returns a new list with collectors that have similar names and phone numbers
/// to the passed values
/// </summary>
/// <param name="collectors">The collectors to filter through</param>
/// <param name="name">The name to look for</param>
/// <param name="phoneNumber">The phone number to look for</param>
/// <returns></returns>
public static BindingList <DBCollector> GetFilteredCollectors(BindingList <DBCollector> collectors, string name, string phoneNumber)
{
BindingList <DBCollector> filteredCollectors = new BindingList <DBCollector>();
foreach (DBCollector c in collectors)
{
if (c.FullName.ToLower().Contains(name.Trim().ToLower()) && DBEmployee.IsPhoneNumberMatching(c.PhoneNumber, phoneNumber.ToLower()))
{
filteredCollectors.Add(c);
}
}
return(filteredCollectors);
}
/// <summary>
/// Gets a list of all the employees in the database
/// </summary>
/// <returns>A list of all the employees in the database</returns>
public static BindingList <DBEmployee> GetEmployees()
{
BindingList <DBEmployee> employees = new BindingList <DBEmployee>();
string query = "SELECT * FROM employees";
using (var conn = new SqlConnection(Properties.Settings.Default.Britannicus_DBConnectionString))
{
var command = new SqlCommand(query, conn);
conn.Open();
var reader = command.ExecuteReader();
while (reader.Read())
{
DBEmployee temp = new DBEmployee((int)reader["employeeID"], (int)reader["positionID"],
(string)reader["firstName"], (string)reader["lastName"], (string)reader["phoneNumber"]);
employees.Add(temp);
}
}
return(employees);
}
private void BtnRemove_Click(object sender, EventArgs e)
{
MasterForm master = (this.Parent.Parent as MasterForm);
try
{
if (DBEmployee.DeleteEmployee((int)this.nudEmployeeIdInput.Value))
{
master.SetStatus("Employee " + this.txtFirstNameInput.Text + " " + this.txtLastNameInput.Text + " has been deleted.");
if (this.nudEmployeeIdInput.Value > this.nudEmployeeIdInput.Minimum)
{
this.nudEmployeeIdInput.Value--;
}
}
}
catch (Exception ex)
{
master.SetStatus("Error! Deletion failed: " + ex.Message);
}
}
private void BtnRemoveEmployee_Click(object sender, EventArgs e)
{
MasterForm master = (this.Parent.Parent as MasterForm);
if (this.cbxSelectedEmployee.SelectedItem is DBEmployee)
{
try
{
DBEmployee tempEmployee = (this.cbxSelectedEmployee.SelectedItem as DBEmployee);
//Attempt to delete from the database
if (tempEmployee.Delete())
{
//Deletion successful
master.SetStatus("Employee " + tempEmployee.FullName + " has been deleted.");
//Remove all related datagridview rows
for (var i = 0; i < this.dgvEmployees.Rows.Count; i++)
{
if ((int)this.dgvEmployees.Rows[i].Cells["EmployeeID"].Value == tempEmployee.EmployeeID)
{
this.dgvEmployees.Rows.RemoveAt(i);
}
}
this.employees.Remove(tempEmployee);
}
}
catch (Exception ex)
{
master.SetStatus("Error! Deletion failed: " + ex.Message);
}
}
else
{
master.SetStatus("Error! You must select an employee to remove");
}
}
/// <summary>
/// Deletes an employee from the database based on the id of this employee
/// </summary>
/// <returns>If an employee was deleted</returns>
public bool Delete()
{
return(DBEmployee.DeleteEmployee(this.EmployeeID));
}
/// <summary>
/// Logs the user out of the system
/// </summary>
public static void Logout()
{
DBUser.Username = "";
DBUser.Employee = null;
DBUser.LastAccess = DateTime.Now;
}
public RegisterScreen(Screen backScreen) : base("Register", backScreen, 1)
{
this.Dock = DockStyle.Fill;
this.BackColor = Color.White;
this.ColumnCount = 2;
this.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle(System.Windows.Forms.SizeType.Percent, 45F));
this.ColumnStyles.Add(new System.Windows.Forms.ColumnStyle(System.Windows.Forms.SizeType.Percent, 55F));
this.RowCount = 7;
this.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Percent, 20F));
this.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Absolute, 50F));
this.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Absolute, 50F));
this.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Absolute, 50F));
this.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Absolute, 50F));
this.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Absolute, 50F));
this.RowStyles.Add(new System.Windows.Forms.RowStyle(System.Windows.Forms.SizeType.Percent, 20F));
this.ParentChanged += RegisterScreen_ParentChanged;
Label lblUsernamePrompt = new Label();
lblUsernamePrompt.Text = "Username:"******"Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Controls.Add(lblUsernamePrompt, 0, 1);
txtUsernameInput = new TextBox();
txtUsernameInput.Anchor = AnchorStyles.Left;
//txtUsernameInput.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
txtUsernameInput.Width = 150;
txtUsernameInput.MaxLength = DBControlHelper.MaximumUsernameLength;
this.Controls.Add(txtUsernameInput, 1, 1);
Label lblPasswordPrompt = new Label();
lblPasswordPrompt.Text = "Password:"******"Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Controls.Add(lblPasswordPrompt, 0, 2);
txtPasswordInput = new TextBox();
txtPasswordInput.Anchor = AnchorStyles.Left;
//txtPasswordInput.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
txtPasswordInput.Width = 150;
txtPasswordInput.PasswordChar = '*';
txtPasswordInput.MaxLength = DBControlHelper.MaximumPasswordLength;
this.Controls.Add(txtPasswordInput, 1, 2);
Label lblConfirmPasswordPrompt = new Label();
lblConfirmPasswordPrompt.Text = "Confirm Password:"******"Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Controls.Add(lblConfirmPasswordPrompt, 0, 3);
txtConfirmPasswordInput = new TextBox();
txtConfirmPasswordInput.Anchor = AnchorStyles.Left;
//txtConfirmPasswordInput.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
txtConfirmPasswordInput.Width = 150;
txtConfirmPasswordInput.PasswordChar = '*';
txtConfirmPasswordInput.MaxLength = DBControlHelper.MaximumPasswordLength;
this.Controls.Add(txtConfirmPasswordInput, 1, 3);
Label lblEmployeePrompt = new Label();
lblEmployeePrompt.Text = "Employee:";
lblEmployeePrompt.Dock = DockStyle.None;
lblEmployeePrompt.Anchor = AnchorStyles.Right;
lblEmployeePrompt.TextAlign = ContentAlignment.MiddleRight;
lblEmployeePrompt.AutoSize = true;
//lblEmployeePrompt.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
this.Controls.Add(lblEmployeePrompt, 0, 4);
cbxEmployee = new ComboBox();
cbxEmployee.DataSource = DBEmployee.GetEmployees();
cbxEmployee.ValueMember = "EmployeeID";
cbxEmployee.DisplayMember = "ComboBoxDisplay";
cbxEmployee.Anchor = AnchorStyles.Left;
//cbxEmployee.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
cbxEmployee.Width = 150;
this.Controls.Add(cbxEmployee, 1, 4);
btnRegister = new Button();
btnRegister.Text = "Register";
btnRegister.Dock = DockStyle.None;
//btnRegister.Font = new System.Drawing.Font("Arial", 12F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
btnRegister.AutoSize = true;
btnRegister.Anchor = AnchorStyles.Left;
btnRegister.BackColor = DefaultBackColor;
btnRegister.Click += BtnRegister_Click;
this.Controls.Add(btnRegister, 1, 5);
this.SetFontSizes(this.Controls);
}