package com.wrox;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class EmployeeData {

  public static Employee getEmployee(String username, DataSource dataSource)
    throws Exception {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    Employee employee = null;
    try {

      conn = dataSource.getConnection();
      stmt = conn.createStatement();
      rs = stmt.executeQuery("select * from employees where username=\'"
        + username + "'");

      if ( rs.next() ) {

        employee = new Employee();

        employee.setUsername(rs.getString("username"));
        employee.setPassword(rs.getString("password"));
        employee.setDepid(new Integer(rs.getInt("depid")));
        employee.setRoleid(new Integer(rs.getString("roleid")));
        String name = rs.getString("name");
        employee.setName(name);
        employee.setPhone(rs.getString("phone"));
        employee.setEmail(rs.getString("email"));
      }
      else {

        throw new Exception("Employee " + username + " not found!");
      }
    }
    finally {

      if ( rs != null ) {

        rs.close();
      }
      if ( stmt != null ) {

        stmt.close();
      }
      if ( conn != null ) {

        conn.close();
      }
    }
    return employee;
  }

  public static ArrayList getEmployees(DataSource dataSource) {

    Employee employee = null;
    ArrayList employees = new ArrayList();
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {

      conn = dataSource.getConnection();
      stmt = conn.createStatement();
      rs =
        stmt.executeQuery("select * from employees, roles, "
        + "departments where employees.roleid=roles.roleid "
        + "and employees.depid=departments.depid");

      while (rs.next()) {

        employee = new Employee();

        employee.setUsername(rs.getString("username"));
        employee.setName(rs.getString("name"));
        employee.setRolename(rs.getString("rolename"));
        employee.setPhone(rs.getString("phone"));
        employee.setEmail(rs.getString("email"));
        employee.setRoleid(new Integer(rs.getInt("roleid")));
        employee.setDepid(new Integer(rs.getInt("depid")));
        employee.setDepartment(rs.getString("depname"));

        employees.add(employee);

        System.err.println("Username : "
          + employee.getUsername()
          + " Department : " + rs.getString("depname"));
      }
    }
    catch ( SQLException e ) {

      System.err.println(e.getMessage());
    }
    finally {

      if ( rs != null ) {

        try {

          rs.close();
        }
        catch ( SQLException sqle ) {

          System.err.println(sqle.getMessage());
        }
        rs = null;
      }
      if ( stmt != null ) {

        try {

          stmt.close();
        }
        catch ( SQLException sqle ) {

          System.err.println(sqle.getMessage());
        }
        stmt = null;
      }
      if ( conn != null ) {

        try {

          conn.close();
        }
        catch ( SQLException sqle ) {

          System.err.println(sqle.getMessage());
        }
        conn = null;
      }
    }
    return employees;
  }

  public static void removeEmployee(String username, DataSource dataSource)
    throws Exception {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {

      conn = dataSource.getConnection();
      stmt = conn.createStatement();

      StringBuffer sqlString =
        new StringBuffer("delete from employees ");
      sqlString.append("where username='" + username + "'");

      stmt.execute(sqlString.toString());
    }
    finally {

      if ( rs != null ) {

        rs.close();
      }
      if ( stmt != null ) {

        stmt.close();
      }
      if ( conn != null ) {

        conn.close();
      }
    }
  }

  public static void addEmployee(Employee employee, DataSource dataSource)
    throws Exception {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {

      conn = dataSource.getConnection();
      stmt = conn.createStatement();

      StringBuffer sqlString =
        new StringBuffer("insert into employees ");

      sqlString.append("values (\""
        + employee.getUsername() + "\", ");
      sqlString.append("\"" +
        employee.getPassword() + "\", ");
      sqlString.append("\""
        + employee.getRoleid() + "\", ");
      sqlString.append("\""
        + employee.getName() + "\", ");
      sqlString.append("\""
        + employee.getPhone() + "\", ");
      sqlString.append("\""
        + employee.getEmail() + "\", ");
      sqlString.append("\""
        + employee.getDepid() + "\")");

      stmt.execute(sqlString.toString());
    }
    finally {

      if ( rs != null ) {

        rs.close();
      }
      if ( stmt != null ) {

        stmt.close();
      }
      if ( conn != null ) {

        conn.close();
      }
    }
  }

  public static void updateEmployee(Employee employee, DataSource dataSource)
    throws Exception {

    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;

    try {

      conn = dataSource.getConnection();
      stmt = conn.createStatement();

      StringBuffer sqlString =
        new StringBuffer("update employees ");

      sqlString.append("set password='"
        + employee.getPassword() + "', ");
      sqlString.append("roleid="
        + employee.getRoleid() + ", ");
      sqlString.append("name='"
        + employee.getName() + "', ");
      sqlString.append("phone='"
        + employee.getPhone() + "', ");
      sqlString.append("email='"
        + employee.getEmail() + "', ");
      sqlString.append("depid="
        + employee.getDepid());
      sqlString.append(" where username='"
        + employee.getUsername() + "'");

      stmt.execute(sqlString.toString());
    }
    finally {

      if ( rs != null ) {

        rs.close();
      }
      if ( stmt != null ) {

        stmt.close();
      }
      if ( conn != null ) {

        conn.close();
      }
    }
  }
}