using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

        EmployeeDbase dbase = new EmployeeDbase("un", "pword", "12121", "2332");
        dbase.Open();
        EmployeeDbase.Department systems = dbase.GetDepartmentByName("Systems");
        EmployeeDbase.Employee brian = dbase.CreateNewEmployee("Brian", systems, DateTime.Parse("10/01/1987"));
        brian.name = "Brian Willis";
        brian.Save();


    }

    class EmployeeDbase
    {
        string db_username;
        string db_pword;
        string db_address;
        string db_name;
        public SqlConnection connection;
        public EmployeeDbase db;

        public EmployeeDbase(string db_username, string db_pword, string db_address, string db_name)
        {
            this.db_username = db_username;
            this.db_pword = db_pword;
            this.db_address = db_address;
            this.db_name = db_name;
            connection = new SqlConnection("user id=" + db_username +
                ";password=" + db_pword +
                ";server=" + db_address +
                ";database=" + db_name);
            EmployeeDbase db = this;
        }
        public void Open()
        {
            connection.Open();
        }
        public void Close()
        {
            connection.Close();
        }
        public SqlConnection GetConnection()
        {
            if (this.connection.State.Equals(ConnectionState.Closed) || this.connection.State.Equals(ConnectionState.Broken))
            {
                this.connection.Open();
                return this.connection;
            }
            else
            {
                return this.connection;
            }
        }
        public Employee GetEmployeeByID(int id)
        {
            SqlConnection connection = GetConnection();
            SqlCommand myCommand = new SqlCommand("SELECT employee_name, employee_dob, department_id, employee_id " +
            "FROM employees WHERE employee_id = '" + id + "'", connection);
            myCommand.ExecuteNonQuery();
            SqlDataReader reader = myCommand.ExecuteReader();
            Employee employee;
            Department department = GetDepartmentByID(reader.GetInt32(2));
            employee = new Employee(reader.GetString(0), department, reader.GetDateTime(1), this.db);
            employee.id = reader.GetInt16(3);
            return employee;
        }
        public Employee CreateNewEmployee(string name, Department department, DateTime dob)
        {
            Employee employee = new Employee(name, department, dob, db);
            return employee;
        }
        public Department CreateNewDepartment(string department_name)
        {
            Department department = new Department(department_name, db);
            return department;
        }
        public Department GetDepartmentByName(string department_name)
        {
            Department department = new Department(department_name,this.db);
            SqlConnection connection = db.GetConnection();
            SqlCommand myCommand = new SqlCommand("SELECT department_id, department_name FROM departments WHERE " +
                "department_name = '" + department_name + "'",connection);
            SqlDataReader reader = myCommand.ExecuteReader();
            department.id = reader.GetInt32(0);
            department.name = reader.GetString(1);
            return department;
        }
        public Department GetDepartmentByID(int department_id)
        {
            SqlConnection connection = db.GetConnection();
            SqlCommand myCommand = new SqlCommand("SELECT department_id, department_name FROM departments WHERE " +
                "department_id = '" + department_id + "'", connection);
            SqlDataReader reader = myCommand.ExecuteReader();
            Department department = new Department(reader.GetString(1), db);
            department.id = reader.GetInt32(0);
            return department;
        }
        public class Employee : IEmployeeCRUD
        {
            public int id;
            public string name;
            public Department department;
            public DateTime dob;
            public EmployeeDbase db;

            public Employee(string name, Department department, DateTime dob, EmployeeDbase db)
            {
                this.name = name;
                this.department = department;
                this.dob = dob;
                this.db = db;
            }
            public void Save()
            {
                SqlConnection connection = db.GetConnection();
                if (this.id == null)
                {
                    SqlCommand insertCommand = new SqlCommand("INSERT INTO employees (employee_name, employee_dob, department_id) " +
                                      "Values ('" + this.name + "', '" + this.dob.Date.ToString() + "', '" +
                                      this.department.ToString() + "')", connection);
                    int new_id = (Int32)insertCommand.ExecuteScalar();
                    this.id = new_id;
                }
                else
                {
                    SqlCommand updateCommand = new SqlCommand("UPDATE employees SET employee_name = '" + name +
                                  "', department_id = '" + department.id + "', employee_dob = '" + dob.Date.ToString() + "'" +
                                  " WHERE employee_ID = '" + this.id + "'", connection);
                    updateCommand.ExecuteNonQuery();
                }
            }
            public void Delete()
            {
                SqlConnection connection = db.GetConnection();
                SqlCommand deleteCommand = new SqlCommand("DELETE FROM employees WHERE employee_id = '" + this.id + "'", connection);
                deleteCommand.ExecuteNonQuery();
            }
        }
        public class Department : IEmployeeCRUD
        {
            public int id;
            public string name;
            public EmployeeDbase db;
            public Department(string name, EmployeeDbase db)
            {
                this.name = name;
                this.db = db;
            }
            public void save()
            {
                SqlConnection connection = db.GetConnection();
                if (this.id == null)
                {
                    SqlCommand insertCommand = new SqlCommand("INSERT INTO departments (department_id, department_name) " +
                                      "Values ('" + this.id.ToString() + "', " + this.name + "')", connection);
                    int new_id = (Int32)insertCommand.ExecuteScalar();
                    this.id = new_id;
                }
                else
                {
                    SqlCommand updateCommand = new SqlCommand("UPDATE departments SET department_name = '" + name +
                                  "' WHERE department_id = '" + this.id + "'", connection);
                    updateCommand.ExecuteNonQuery();
                }
            }
            public void Delete()
            {
                SqlConnection connection = db.GetConnection();
                SqlCommand deleteCommand = new SqlCommand("DELETE FROM departments WHERE department_id = '" + this.id + "'", connection);
                deleteCommand.ExecuteNonQuery();
            }
        }
        interface IEmployeeCRUD
        {
            void Save();
            void Delete();
        }
    }
}