public static List<Employee> GetAllEmployees()
{
MySqlCommand cmd = new MySqlCommand("SELECT CONCAT(f_name,', ',l_name) AS 'Name', employee.id AS 'id' from employee join job_categories on employee.job=job_categories.id ORDER BY Name",conn );
List<Employee> emps = new List<Employee>();
conn.Open();
MySqlDataReader DR = cmd.ExecuteReader();
while (DR.Read())
{
Employee emp = new Employee();
emp.FirstName = DR["Name"].ToString();
emp.ID = DR.GetInt32("id");
emps.Add(emp);
}
conn.Close();
return emps;
}
public static Employee GetEmployeeByUsrnameAndPassword(String Username, String Password)
{
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM employee LEFT OUTER JOIN job_categories ON employee.job = job_categories.id WHERE username = ?Username AND pass = ?Password;";
cmd.Parameters.Add(new MySqlParameter("?Username", Username.ToLower().Trim()));
cmd.Parameters.Add(new MySqlParameter("?Password", Password));
Employee emp = null;
try
{
conn.Open();
MySqlDataReader DR = cmd.ExecuteReader();
if (!DR.HasRows)
{
conn.Close();
return null;
}
DR.Read();
emp = new Employee();
if (DR["title"].ToString() == "Admin")
{
emp = new Admin();
}
if (DR["title"].ToString() == "Manager")
{
emp = new Manager(DR["dealership"].ToString());
}
if (DR["title"].ToString() == "Salesman")
{
emp = new Salesperson(DR["dealership"].ToString());
}
emp.FirstName = DR["f_name"].ToString();
emp.LastName = DR["l_name"].ToString();
emp.Dealership = int.Parse(DR["dealership"].ToString());
emp.Address = DR["addr_city"].ToString() + " " + DR["addr_state"].ToString();
emp.Email = DR["email"].ToString();
emp.EmployedDate = DateTime.Parse(DR["start_date"].ToString());
// emp.TerminatedDataAccesste = DataAccessteTime.Parse(DR["end_DataAccesste"].ToString());
emp.Password = DR["pass"].ToString();
emp.Username = DR["username"].ToString();
emp.ID = int.Parse(DR["id"].ToString());
}
finally
{
conn.Close();
}
return emp;
}
public static Employee GetEmployeeByID(int ID)
{
MySqlCommand cmd = conn.CreateCommand();
//cmd.CommandText = "SELECT * FROM employee LEFT OUTER JOIN job_categories ON employee.job = job_categories.id WHERE employee.id = ?id";
cmd.CommandText = "SELECT IF(title LIKE '%Salesman%', 'Salesman', IF(title LIKE '%Admin%', 'Admin', IF(title LIKE '%Manager%', 'Manager', 'Unknown'))) " +
"AS title,employee.id,addr_city,addr_state,start_date,dealership, email,f_name,l_name,username,pass from employee JOIN job_categories ON job=job_categories.id " +
"WHERE end_date IS null AND employee.id=?id";
cmd.Parameters.Add(new MySqlParameter("?id", ID));
Employee emp = null;
try
{
conn.Open();
MySqlDataReader DR = cmd.ExecuteReader();
if (!DR.HasRows)
{
conn.Close();
return null;
}
DR.Read();
emp = new Employee();
if (DR["title"].ToString() == "Admin")
{
emp = new Admin();
}
if (DR["title"].ToString() == "Manager")
{
emp = new Manager(DR["dealership"].ToString());
}
if (DR["title"].ToString() == "Salesman")
{
emp = new Salesperson(DR["dealership"].ToString());
}
emp.FirstName = DR["f_name"].ToString();
emp.LastName = DR["l_name"].ToString();
emp.Address = DR["addr_city"].ToString() + " " + DR["addr_state"].ToString();
emp.Email = DR["email"].ToString();
emp.EmployedDate = DateTime.Parse(DR["start_date"].ToString());
// emp.TerminatedDataAccesste = DataAccessteTime.Parse(DR["end_DataAccesste"].ToString());
emp.Password = DR["pass"].ToString();
emp.Username = DR["username"].ToString();
emp.ID = int.Parse(DR["id"].ToString());
emp.Dealership = int.Parse(DR["dealership"].ToString());
DR.Close();
MySqlCommand msgCmd = conn.CreateCommand();
msgCmd.CommandText = "SELECT sent_date, from_id, to_id, message, messages.id, read_count, empFrom.f_name AS from_f_name , empFrom.l_name AS from_l_name, empTo.f_name AS to_f_name, empTo.l_name AS to_l_name FROM messages, employee as empFrom, employee as empTo WHERE messages.to_id = empTo.id AND messages.from_id = empFrom.id AND (messages.to_id = ?id OR messages.from_id = ?id) ORDER BY sent_date DESC";
msgCmd.Parameters.Add(new MySqlParameter("?id", emp.ID));
MySqlDataReader MsgDR = msgCmd.ExecuteReader();
while (MsgDR.Read())
{
Message msg = new Message();
msg.Text = MsgDR["message"].ToString();
msg.ViewCount = MsgDR.GetInt32("read_count");
msg.Sender = MsgDR["from_f_name"] + " " + MsgDR["from_l_name"];
msg.Receiver = MsgDR["to_f_name"] + " " + MsgDR["to_l_name"];
msg.ID = MsgDR.GetInt32("id");
msg.SentDate = MsgDR["sent_date"].ToString();
if (MsgDR.GetInt32("to_id") == emp.ID)
{
emp.AddRecMessage(msg);
}
if (MsgDR.GetInt32("from_id") == emp.ID)
{
emp.AddSentMessage(msg);
}
}
}
finally
{
conn.Close();
}
//Quick Fix to Retrieve Employees Position
conn.Open();
emp.Position = getPositionFromEmployeeID(ID);
conn.Close();
return emp;
}