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;
        }