
using System;
using System.Data;
using System.Data.SqlClient;
namespace TinyHeels.App_Code
{
    class Employee
    {

        DBConnection strcon = new DBConnection();

        private string empId;
        public string EmpId
        {
            get { return empId; }
            set { empId = value; }
        }
        private string empName;
        public string EmpName
        {
            get { return empName; }
            set { empName = value; }
        }
        private string empGender;
        public string EmpGender
        {
            get { return empGender; }
            set { empGender = value; }
        }
        private string empPhone;
        public string EmpPhone
        {
            get { return empPhone; }
            set { empPhone = value; }
        }
        private string empAddress;
        public string EmpAddress
        {
            get { return empAddress; }
            set { empAddress = value; }
        }
        private string empEmail;
        public string EmpEmail
        {
            get { return empEmail; }
            set { empEmail = value; }
        }
        private string empLoginRole;
        public string EmpLoginRole
        {
            get { return empLoginRole; }
            set { empLoginRole = value; }
        }
        private string empLoginId;
        public string EmpLoginId
        {
            get { return empLoginId; }
            set { empLoginId = value; }
        }
        private string empLoginPassword;
        public string EmpLoginPassword
        {
            get { return empLoginPassword; }
            set { empLoginPassword = value; }
        }
        private bool empStatus;
        public bool EmpStatus
        {
            get { return empStatus; }
            set { empStatus = value; }
        }
        private double bSalary;
        public double BSalary
        {
            get { return bSalary; }
            set { bSalary = value; }
        }
        private byte[] image;
        public byte[] Image
        {
            get { return image; }
            set { image = value; }
        }

        //-------------------------------------------------------------------------------------------
        public string getCredentials()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT EmployeeLoginRole from employee where EmployeeId='" + this.empId + "'";
            cmd1.CommandType = CommandType.Text;
            cmd1.Connection = connection;
            string value = cmd1.ExecuteScalar().ToString();
            connection.Close();
            return value;
        }
        public string getEmpId()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT EmployeeId from employee where EmployeeLoginId='" + this.empLoginId + "' and EmployeeLoginPassword='" + this.empLoginPassword + "'";
            cmd1.CommandType = CommandType.Text;
            cmd1.Connection = connection;
            string value = cmd1.ExecuteScalar().ToString();
            connection.Close();
            return value;
        }
        public string getEmployeeName()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT EmployeeName from employee where EmployeeID='" + this.empId + "'";
            cmd1.CommandType = CommandType.Text;
            cmd1.Connection = connection;
            string value = cmd1.ExecuteScalar().ToString();
            connection.Close();
            return value;
        }
        public bool checkEmployeeById()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Select COALESCE(count(*),0) from employee where EmployeeID='" + this.empId + "'";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                int count = (Int32)cmd.ExecuteScalar();
                connection.Close();
                if (count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {

                throw new Exception("Could not check employee");
            }
        }

        public void insertNewEmployee()
        {
            try
            {
                bool x = this.checkEmployeeById();
                if (x)
                {
                    //update employee
                    SqlConnection connection = strcon.connect();
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "update employee set EmployeeName=@EmployeeName,EmployeeGender=@EmployeeGender,EmployeePhone=@EmployeePhone,EmployeeAddress=@EmployeeAddress,EmployeeEmail=@EmployeeEmail,EmployeeLoginRole=@EmployeeLoginRole,EmployeeSalary=@EmployeeSalary,EmployeeStatus=@EmployeeStatus,EmployeeImage=@EmployeeImage where EmployeeID='" + this.empId + "'";
                    cmd.Parameters.AddWithValue("@EmployeeName", this.empName);
                    cmd.Parameters.AddWithValue("@EmployeeGender", this.empGender);
                    cmd.Parameters.AddWithValue("@EmployeePhone", this.empPhone);
                    cmd.Parameters.AddWithValue("@EmployeeAddress", this.empAddress);
                    cmd.Parameters.AddWithValue("@EmployeeEmail", this.empEmail);
                    cmd.Parameters.AddWithValue("@EmployeeLoginRole", this.empLoginRole);
                    cmd.Parameters.AddWithValue("@EmployeeSalary", this.bSalary);
                    cmd.Parameters.AddWithValue("@EmployeeStatus", this.empStatus);
                    cmd.Parameters.AddWithValue("@EmployeeImage", this.image);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
                else
                {
                    //insert fresh employee
                    SqlConnection connection = strcon.connect();
                    string sql = "insert into employee(EmployeeId,EmployeeName,EmployeeGender,EmployeePhone,EmployeeAddress,EmployeeEmail,EmployeeLoginRole,EmployeeLoginId,EmployeeLoginPassword,EmployeeSalary,EmployeeStatus,EmployeeImage)"
                        + " values (@EmployeeId,@EmployeeName,@EmployeeGender,@EmployeePhone,@EmployeeAddress,@EmployeeEmail,@EmployeeLoginRole,@EmployeeLoginId,@EmployeeLoginPassword,@EmployeeSalary,@EmployeeStatus,@EmployeeImage)";
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    cmd.Parameters.AddWithValue("@EmployeeId", this.empId);
                    cmd.Parameters.AddWithValue("@EmployeeName", this.empName);
                    cmd.Parameters.AddWithValue("@EmployeeGender", this.empGender);
                    cmd.Parameters.AddWithValue("@EmployeePhone", this.empPhone);
                    cmd.Parameters.AddWithValue("@EmployeeAddress", this.empAddress);
                    cmd.Parameters.AddWithValue("@EmployeeEmail", this.empEmail);
                    cmd.Parameters.AddWithValue("@EmployeeLoginRole", this.empLoginRole);
                    cmd.Parameters.AddWithValue("@EmployeeLoginId", this.empLoginId);
                    cmd.Parameters.AddWithValue("@EmployeeLoginPassword", this.empLoginPassword);
                    cmd.Parameters.AddWithValue("@EmployeeSalary", this.bSalary);
                    cmd.Parameters.AddWithValue("@EmployeeStatus", this.empStatus);
                    cmd.Parameters.AddWithValue("@EmployeeImage", this.image);
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Error inserting new employee");
            }
        }
        public DataTable getAllEmployee()
        {
            try
            {
                DataTable dt;
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds = new SqlDataAdapter("Select EmployeeId as ID, EmployeeName as Name, EmployeePhone as [Contact Number], EmployeeAddress as [Address] from employee", connection))
                {
                    DataSet dSet = new DataSet();
                    ds.Fill(dSet);
                    dt = new DataTable();
                    dt = dSet.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Get Employee Data");
            }
        }
        public DataTable getAllEmployee2()
        {
            try
            {
                DataTable dt;
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds = new SqlDataAdapter("Select EmployeeId as ID, EmployeeName as Name, EmployeePhone as [Contact Number], EmployeeAddress as [Address] from employee where EmployeeLoginRole='Waiter'", connection))
                {
                    DataSet dSet = new DataSet();
                    ds.Fill(dSet);
                    dt = new DataTable();
                    dt = dSet.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Get Employee Data");
            }
        }
        public DataTable getSpecificEmployee()
        {
            try
            {
                DataTable dt;
                SqlConnection connection = strcon.connect();
                using (SqlDataAdapter ds = new SqlDataAdapter("Select * from employee where EmployeeID='" + this.empId + "'", connection))
                {
                    DataSet dSet = new DataSet();
                    ds.Fill(dSet);
                    dt = new DataTable();
                    dt = dSet.Tables[0];
                    connection.Close();
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Get Employee Data");
            }
        }
        //Function to delete an employee
        public void deleteEmployee()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                string sql = "DELETE FROM EMPLOYEE WHERE EmployeeID='" + this.EmpId + "'";
                SqlCommand cmd = new SqlCommand(sql, connection);
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Sorry Unable To Delete Employee Data");
            }
        }
        //-------
        public bool checkEmployeeLogin()
        {
            SqlConnection connection = strcon.connect();
            SqlCommand cmd1 = new SqlCommand();
            cmd1.CommandText = "SELECT COUNT(*) from employee where EmployeeLoginId='" + this.empLoginId + "' and EmployeeLoginPassword='" + this.empLoginPassword + "' AND EmployeeStatus='True'";
            cmd1.CommandType = CommandType.Text;
            cmd1.Connection = connection;
            Int32 count = (Int32)cmd1.ExecuteScalar();
            connection.Close();
            if (count > 0)
                return true;
            else
                return false;
        }
        public void updateLoginId()
        {
            try
            {
                bool x = checkEmployeeLogin();
                if (x)
                {
                    throw new Exception("Could not update employee data \nOr same Login ID Exists");

                }
                else
                {
                    SqlConnection connection = strcon.connect();
                    SqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "update employee set EmployeeLoginId=@id where EmployeeId='" + this.empId + "'";
                    cmd.Parameters.AddWithValue("@id", this.empLoginId);
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                    connection.Close();
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Could not update employee data \nOr same Login ID Exists");
            }
        }
        public bool checkLoginID()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "Select COALESCE(count(*),0) from employee where EmployeeID='" + this.empId + "'";
                cmd.CommandType = CommandType.Text;
                cmd.Connection = connection;
                int count = (Int32)cmd.ExecuteScalar();
                connection.Close();
                if (count > 0)
                    return true;
                else
                    return false;
            }
            catch (Exception ex)
            {

                throw new Exception("Could not check employee");
            }
        }
        public void updatePassword()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "update employee set EmployeeLoginPassword=@id where EmployeeId='" + this.empId + "'";
                cmd.Parameters.AddWithValue("@id", this.empLoginPassword);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Could not update employee password data");
            }
        }

        public void reset()
        {
            try
            {
                SqlConnection connection = strcon.connect();
                SqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "update employee set EmployeeLoginId=@loginid,EmployeeLoginPassword=@loginpwd where EmployeeId='" + this.empId + "'";
                cmd.Parameters.AddWithValue("@loginid", this.empLoginId);
                cmd.Parameters.AddWithValue("@loginpwd", this.empLoginPassword);
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                connection.Close();
            }
            catch (Exception ex)
            {
                throw new Exception("Could not update employee data");
            }
        }

    }
}
