package com.fortech.officetime;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import javax.ejb.Stateless;
import com.microsoft.sqlserver.jdbc.*;

/**
 * Controller class for the Access Control database. Provides the connection to
 * the SQL Server database of the Access Card System using a
 * SQLServerConnectionPoolDataSource.
 *
 * @author david.rus
 *
 */
@Stateless
public class AccessControlDbConnector {

    /**
     * Method used to establish a connection to the database
     *
     * @throws SQLException
     */
    public ResultSet connectToDb(String query) {

        // Declare the JDBC objects.
        Connection con = null;
        ResultSet result = null;
        Statement stmt = null;

        try {
            // Establish the connection.
            System.out.println("Establishing the connection...");
            SQLServerConnectionPoolDataSource ds = new SQLServerConnectionPoolDataSource();
            ds.setUser(Props.DB_USERNAME);
            ds.setPassword(Props.DB_PASSWORD);
            ds.setServerName(Props.DB_SERVERNAME);
            ds.setDatabaseName(Props.DB_DATABASENAME);
            ds.setPortNumber(Props.DB_PORTNUMBER);
            con = ds.getConnection();
            System.out.println("Connection established");
            System.out.println();
            stmt = con.createStatement();
            result = stmt.executeQuery(query);
        }

        // Handle any errors that may have occurred.
        catch (Exception e) {
            e.printStackTrace();
        }

        finally {
            if (stmt != null)
                try {
                    stmt.close();
                } catch (Exception e) {
                }
            if (con != null)
                try {
                    con.close();
                } catch (Exception e) {
                }
        }
        return result;
    }

    /**
     * Method used to retrieve the employees
     * @return a String with the employees
     */
    public String getEmployees() {

        List<String> firstNameList = new ArrayList<String>();
        List<String> lastNameList = new ArrayList<String>();
        String query = "SELECT TOP (1000) [iEmployeeNum]\r\n" + "      ,[tLastName]\r\n"
                + "      ,[tFirstName]\r\n" + "  FROM [axtraxdb].[dbo].[tblEmployees]";

        AccessControlDbConnector connect = new AccessControlDbConnector();
        ResultSet result = connect.connectToDb(query);
        // Iterate through the data in the result set and display it.
        try {
            while (result.next()) {
                firstNameList.add(result.getString("tFirstName"));
                lastNameList.add(result.getString("tLastName"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (result != null)
                try {
                    result.close();
                } catch (Exception e) {
                }
            }
        StringBuffer sb = new StringBuffer();
        for (int i = 0; i < firstNameList.size(); i++) {
            sb.append("Name: " + firstNameList.get(i) + " " + lastNameList.get(i));
            sb.append(System.getProperty("line.separator"));
        }
        String str = sb.toString();
        return str;
    }
}