package com.ora.jsp.beans.emp;

import java.io.*;
import java.sql.*;
import java.text.*;
import java.util.*;
import javax.sql.*;
import javax.servlet.jsp.jstl.sql.*;
import com.ora.jsp.beans.sql.*;

/**
 * This class is an example of an application specific interface
 * to a database. It contains methods for authenticating a user,
 * and retrieving and updating user information.
 *
 * @author Hans Bergsten, Gefion software <hans@gefionsoftware.com>
 * @version 2.0
 */
public class EmployeeRegistryBean implements Serializable {
    private DataSource dataSource;

    /**
     * Sets the dataSource property value.
     */
    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }
    
    /**
     * Returns an EmployeeBean if the specified user name and password
     * match an employee in the database, otherwise null.
     */
    public EmployeeBean authenticate(String userName, String password) 
        throws SQLException {

        EmployeeBean empInfo = getEmployee(userName);
        if (empInfo != null && empInfo.getPassword().equals(password)) {
            return empInfo;
        }
        return null;
    }

    /**
     * Returns an EmployeeBean initialized with the information
     * found in the database for the specified employee, or null if
     * not found.
     */
    public EmployeeBean getEmployee(String userName) throws SQLException {
        
        // Get the user info from the database
        Connection conn = dataSource.getConnection();
        Map empRow = null;
        Map[] projects = null;
        try {
            empRow = getSingleValueProps(userName, conn);
            projects = getProjects(userName, conn);
        }
        finally {
            try {
                conn.close();
            }
            catch (SQLException e) {} // Ignore
        }

        // Create a EmployeeBean if the user was found
        if (empRow == null) {
            // Not found
            return null;
        }
        
        EmployeeBean empInfo = new EmployeeBean();
        empInfo.setDept((String) empRow.get("Dept"));
        empInfo.setEmpDate((java.util.Date) empRow.get("EmpDate"));
        empInfo.setEmailAddr((String) empRow.get("EmailAddr"));
        empInfo.setFirstName((String) empRow.get("FirstName"));
        empInfo.setLastName((String) empRow.get("LastName"));
        empInfo.setPassword((String) empRow.get("Password"));
        empInfo.setUserName((String) empRow.get("UserName"));
        empInfo.setProjects(toProjectsArray(projects));
        return empInfo;
    }

    /**
     * Inserts the information about the specified employee, or 
     * updates the information if it's already defined.
     */
    public void saveEmployee(EmployeeBean empInfo) throws SQLException {
        
        // Save the user info from the database
        Connection conn = dataSource.getConnection();
        conn.setAutoCommit(false);
        try {
            saveSingleValueProps(empInfo, conn);
            saveProjects(empInfo, conn);
            conn.commit();
        }
        catch (SQLException e) {
            conn.rollback();
        }
        finally {
            try {
                conn.setAutoCommit(true);
                conn.close();
            }
            catch (SQLException e) {} // Ignore
        }
    }
    
    /**
     * Returns a Map with all information about the specified
     * employee except the project list, or null if not found.
     */
    private Map getSingleValueProps(String userName, Connection conn) 
        throws SQLException {

        if (userName == null) {
            return null;
        }
        
        SQLCommandBean sqlCommandBean = new SQLCommandBean();
        sqlCommandBean.setConnection(conn);
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM Employee ")
            .append("WHERE UserName = ?");
        sqlCommandBean.setSqlValue(sql.toString());
        List values = new ArrayList();
        values.add(userName);
        sqlCommandBean.setValues(values);
        Result result = sqlCommandBean.executeQuery();
        if (result == null || result.getRowCount() == 0) {
            // User not found
            return null;
        }
        return result.getRows()[0];
    }

    /**
     * Returns a Map[] with a Map per project for the specified employee, 
     * or null if not found.
     */
    private Map[] getProjects(String userName, Connection conn) 
        throws SQLException {

        if (userName == null) {
            return null;
        }
        
        SQLCommandBean sqlCommandBean = new SQLCommandBean();
        sqlCommandBean.setConnection(conn);
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT * FROM EmployeeProjects ")
            .append("WHERE UserName = ?");
        sqlCommandBean.setSqlValue(sql.toString());
        List values = new ArrayList();
        values.add(userName);
        sqlCommandBean.setValues(values);
        return sqlCommandBean.executeQuery().getRows();
    }
    
    /**
     * Inserts the information about the specified employee (except projects), 
     * or updates the information if it's already defined.
     */
    private void saveSingleValueProps(EmployeeBean empInfo, Connection conn) 
        throws SQLException {

        if (empInfo == null) {
            return;
        }
        
        StringBuffer sql = new StringBuffer();
        EmployeeBean dbInfo = getEmployee(empInfo.getUserName());
        if (dbInfo == null) {
            // Use INSERT statement
            sql.append("INSERT INTO Employee ").
                append("(Dept, EmpDate, EmailAddr, FirstName, LastName, ").
                append("Password, ModDate, UserName) ").
                append("VALUES(?, ?, ?, ?, ?, ?, ?, ?)");
        }
        else {
            // Use UPDATE statement
            sql.append("UPDATE Employee SET Dept = ?, EmpDate = ?, ")
                .append("EmailAddr = ?, FirstName = ?, LastName = ?,")
                .append("Password = ?, ModDate = ? WHERE Username = ?");
        }
        SQLCommandBean sqlCommandBean = new SQLCommandBean();
        sqlCommandBean.setConnection(conn);
        sqlCommandBean.setSqlValue(sql.toString());
        List values = new ArrayList();
        values.add(empInfo.getDept());
        // Converts java.util.Date to java.sql.Date
        java.sql.Date empDate = 
            new java.sql.Date(empInfo.getEmpDate().getTime());
        values.add(empDate);
        values.add(empInfo.getEmailAddr());
        values.add(empInfo.getFirstName());
        values.add(empInfo.getLastName());
        values.add(empInfo.getPassword());
        values.add(new Timestamp(System.currentTimeMillis()));
        values.add(empInfo.getUserName());
        sqlCommandBean.setValues(values);
        sqlCommandBean.executeUpdate();
    }

    /**
     * Updates or inserts project information for the specified
     * employee.
     */
    private void saveProjects(EmployeeBean empInfo, Connection conn) 
        throws SQLException {

        if (empInfo == null) {
            return;
        }
        
        SQLCommandBean sqlCommandBean = new SQLCommandBean();
        sqlCommandBean.setConnection(conn);
        sqlCommandBean.setSqlValue("DELETE FROM EmployeeProjects WHERE UserName = ?");
        List values = new ArrayList();
        values.add(empInfo.getUserName());
        sqlCommandBean.setValues(values);
        sqlCommandBean.executeUpdate();
        
        String[] projects = empInfo.getProjects();
        sqlCommandBean.setSqlValue("INSERT INTO EmployeeProjects VALUES(?, ?)");
        for (int i = 0; i < projects.length; i++) {
            values.clear();
            values.add(empInfo.getUserName());
            values.add(projects[i]);
            sqlCommandBean.executeUpdate();
        }
    }
    
    /**
     * Converts a Vector with Row elements containing a ProjectName
     * column to a String[] with the project names.
     */
    private String[] toProjectsArray(Map[] projectRows) {
        if (projectRows == null) {
            return new String[0];
        }
        
        String[] arr = new String[projectRows.length];
        for (int i = 0; i < arr.length; i++) {
            arr[i] = (String) projectRows[i].get("ProjectName");
        }
        return arr;
    }
}