package com.gpc.server.dataaccess.employee;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.Vector;

import org.apache.log4j.Logger;

import com.gpc.backofficecommon.constants.schema.Sch_Employee;
import com.gpc.backofficecommon.constants.schema.Sch_EmployeeClock;
import com.gpc.backofficecommon.constants.schema.Sch_EmployeeNonWorkedHours;
import com.gpc.backofficecommon.constants.schema.Sch_FWGroupUser;
import com.gpc.backofficecommon.constants.schema.Sch_FWUser;
import com.gpc.backofficecommon.constants.schema.Sch_PayPeriodProfile;
import com.gpc.backofficecommon.constants.schema.Sch_RefEmployeeJobCode;
import com.gpc.backofficecommon.constants.schema.Sch_Tables;
import com.gpc.backofficecommon.constants.schema.Sch_TimeTrackingProfile;
import com.gpc.common.ApplicationContext;
import com.gpc.common.PointOfSaleException;
import com.gpc.common.Profile;
import com.gpc.server.util.JDBCUtil;
import com.gpc.server.util.ServerUtil;
import com.gpc.valueobjects.employee.EmployeeVO;
import com.gpc.valueobjects.profile.TimeTrackingProfileVO;

public class Employee {

    private static final Logger logger = Logger.getLogger(Employee.class);
    public static final String ACTIVE_INVOICING_ALLOWED_EMPLOYEES = "ACTIVE_INVOICING_ALLOWED_EMPLOYEES";
    public static final String ACTIVE_SALES_MAN = "ACTIVE_SALES_MAN";
    public static final String ACTIVE_CLOCKED_IN_EMPLOYEES = "ACTIVE_CLOCKED_IN_EMPLOYEES";
    private boolean DEBUG = true;
    final static String SALESMAN_EMPLOYEE_JOB_CODES = "1079, 1093, 1131, 1139";
    final static String SALESMAN_JOB_CODES = "SELECT " + Sch_RefEmployeeJobCode.ID + " FROM " +
    Sch_Tables.REF_EMPLOYEE_JOB_CODE +
    " WHERE " + Sch_RefEmployeeJobCode.EMPLOYEE_JOB_CODE + " IN (" + SALESMAN_EMPLOYEE_JOB_CODES + ")";
    private static final int STORE_MANAGER_ID = 1;
    private static final int DISTRICT_OFFICE_ID = 4;

    private static final String EMPLOYEE_SELECT_SQL = "SELECT " +
    Sch_Employee.ID + ", " +
    Sch_Employee.EMPLOYEE_NUM + ", " +
    Sch_Employee.FIRST_NAME + ", " +
    Sch_Employee.LAST_NAME + ", " +
    Sch_Employee.MIDDLE_INITIAL + ", "+
    Sch_Employee.REF_LANGUAGE_CD + ", " +
    Sch_Employee.CLOCKED_IN + ", " +
    Sch_Employee.INVOICING_ALLOWED +
    " FROM " +
    Sch_Tables.EMPLOYEE;

    private static final String EMPLOYEE_DETAIL_INFO_SELECT_SQL = "SELECT " +
    Sch_Employee.ID + ", " +
    Sch_Employee.EMPLOYEE_NUM + ", " +
    Sch_Employee.FIRST_NAME + ", " +
    Sch_Employee.LAST_NAME + ", " +
    Sch_Employee.MIDDLE_INITIAL + ", " +
    Sch_Employee.REF_LANGUAGE_CD + ", " +
    Sch_Employee.REF_COUNTRY_CD + ", " +
    Sch_Employee.CLOCKED_IN + ", " +
    Sch_Employee.INVOICING_ALLOWED + ", " +
    Sch_Employee.PASSWORD + ", " +
    Sch_Employee.REF_PAY_TYPE_ID + ", " +
    Sch_Employee.CUSTOMER_ID + ", " +
    Sch_Employee.TERMINATION_DATE + ", " +
    Sch_Employee.REF_CATALOG_PREFERENCE_ID + ", " +
    Sch_Employee.DISPLAY_GROSS_PROFIT_AND_COST + ", " +
    Sch_Employee.PASSWORD_EXP_DATE + ", " +
    Sch_Employee.REF_PASSWORD_STATUS_ID +", " +
    Sch_Employee.ALLOW_CHANGE_CATALOG_PREFERENCE+", " +
    Sch_Employee.LDAP_EXTERNAL_IDENTIFIER+
    " FROM " +
    Sch_Tables.EMPLOYEE;
    
    private static final String CURRENT_EMPLOYEE_SELECT_SQL = "SELECT " +
    Sch_Employee.EMPLOYEE_NUM + ", " +
    Sch_Employee.FIRST_NAME + ", " +
    Sch_Employee.LAST_NAME + ", " +
    Sch_Employee.PAY_PERIOD_PROFILE_ID +
    " FROM " +
    Sch_Tables.EMPLOYEE + ", " +
    Sch_Tables.TIME_TRACKING_PROFILE;
    
    
    private static final String TIME_ENTERED_EMPLOYEE_SELECT_SQL = "SELECT " +
    " DISTINCT " +
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.EMPLOYEE_NUM + ", " +
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.FIRST_NAME + ", " +
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.LAST_NAME + 
    " FROM " +
    Sch_Tables.EMPLOYEE + " LEFT OUTER JOIN " +
    Sch_Tables.EMPLOYEE_CLOCK + " ON " + 
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.ID + " = " + 
    Sch_Tables.EMPLOYEE_CLOCK + "." + Sch_EmployeeClock.EMPLOYEE_ID + " AND " + 
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.LOC + " = " +
    Sch_Tables.EMPLOYEE_CLOCK + "." + Sch_EmployeeClock.LOC + " LEFT OUTER JOIN " +
    Sch_Tables.PAY_PERIOD_PROFILE + " ON " +
    Sch_Tables.PAY_PERIOD_PROFILE + "." + Sch_PayPeriodProfile.ID + " = " +
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.PAY_PERIOD_PROFILE_ID + " AND " + 
    Sch_Tables.PAY_PERIOD_PROFILE + "." + Sch_PayPeriodProfile.LOC + " = " +  
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.LOC +
    " LEFT OUTER JOIN " + Sch_Tables.EMPLOYEE_NON_WORKED_HOURS + " ON " + 
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.ID + " = " + 
    Sch_Tables.EMPLOYEE_NON_WORKED_HOURS + "." + Sch_EmployeeNonWorkedHours.EMPLOYEE_ID + " AND " + 
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.LOC + " = " +
    Sch_Tables.EMPLOYEE_NON_WORKED_HOURS + "." + Sch_EmployeeNonWorkedHours.LOC;
    
    private static final String SELECT_STORE_MANAGER_OR_DIST_OFFICE_EMP = "SELECT TOP 1 " +
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.ID + ", " +
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.EMPLOYEE_NUM + 
    " FROM " + Sch_Tables.EMPLOYEE + 
    " INNER JOIN " + Sch_Tables.FW_USER + " ON " +
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.FW_USER_ID + " = " + 
    Sch_Tables.FW_USER + "." + Sch_FWUser.ID + " AND " + 
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.LOC + " = " + 
    Sch_Tables.FW_USER + "." + Sch_FWUser.LOC + 
    " INNER JOIN " + Sch_Tables.FW_GROUP_USER + " ON " + 
    Sch_Tables.FW_USER + "." + Sch_FWUser.ID + " = " +
    Sch_Tables.FW_GROUP_USER + "." + Sch_FWGroupUser.USER_ID + " AND " +
    Sch_Tables.FW_USER + "." + Sch_FWUser.LOC + " = " + 
    Sch_Tables.FW_GROUP_USER + "." + Sch_FWGroupUser.LOC +
    " WHERE " + 
    Sch_Tables.EMPLOYEE + "." + Sch_Employee.LOC + " = ? AND (" +
    Sch_Employee.TERMINATION_DATE +
    " IS NULL OR " + Sch_Employee.TERMINATION_DATE + " >= CURRENT DATE) AND " +
    Sch_Tables.FW_GROUP_USER + "." + Sch_FWGroupUser.GROUP_ID + 
    " IN (" + STORE_MANAGER_ID + "," + DISTRICT_OFFICE_ID + ") " +
    " ORDER BY " + Sch_Tables.EMPLOYEE + "." + Sch_Employee.EMPLOYEE_NUM;
    
	private static final String EMPLOYEE_ACCOUNT_INFO_SELECT_SQL =  "SELECT " +
		    Sch_Employee.ID + ", " +
		    Sch_Employee.PASSWORD_EXP_DATE + ", " +
		    Sch_Employee.REF_PASSWORD_STATUS_ID +
		    " FROM " +
		    Sch_Tables.EMPLOYEE;
    
    private String whereClauseForSelectEmployee(Integer loc, String typeOfList) {
	        String retValue = " WHERE " +
	        Sch_Employee.LOC + " = " + loc + " AND (((" +
	        Sch_Employee.TERMINATION_DATE +
	        " IS NULL) OR (" +
	        Sch_Employee.TERMINATION_DATE +
	        " >= CURRENT DATE))";
	        if (typeOfList.trim().equals(ACTIVE_SALES_MAN)) {
	            retValue += " AND (" + Sch_Employee.REF_EMPLOYEE_JOB_CODE_ID + " IN (" + SALESMAN_JOB_CODES + ")) OR EMPLOYEE_NUM  = 0 )";
	        }
	        if (typeOfList.trim().equals(ACTIVE_INVOICING_ALLOWED_EMPLOYEES)) {
	            retValue += " AND (" + Sch_Employee.INVOICING_ALLOWED + " = 'Y'))";
	        }
	        return retValue;
    }

    private String whereClauseForEmployeeDetailInfo(Integer loc, Integer empNum) {
        String retValue = " WHERE " +
        Sch_Employee.LOC + " = " + loc + " AND " +
        Sch_Employee.EMPLOYEE_NUM + " = " + empNum + " AND ((" +
        Sch_Employee.TERMINATION_DATE +
        " IS NULL) OR (" +
        Sch_Employee.TERMINATION_DATE +
        " >= CURRENT DATE))";
        return retValue;
    }
    private String whereClauseForEmployeeDetailInfoByID(Integer loc, Integer ID) {
        String retValue = " WHERE " +
        Sch_Employee.LOC + " = " + loc + " AND " +
        Sch_Employee.ID + " = " + ID + " AND ((" +
        Sch_Employee.TERMINATION_DATE +
        " IS NULL) OR (" +
        Sch_Employee.TERMINATION_DATE +
        " >= CURRENT DATE))";
        return retValue;
    }

      private String whereClauseForAllEmployeeDetailInfoByID(Integer loc, Integer ID) {
        String retValue = " WHERE " +
        Sch_Employee.LOC + " = " + loc + " AND " +
        Sch_Employee.ID + " = " + ID;
        return retValue;
    }
      
    private String whereClauseForCuurentPayPeriodEmployee(Integer loc) {
        String retValue = " WHERE " + Sch_Employee.PAY_PERIOD_PROFILE_ID + " = ";
        TimeTrackingProfileVO timeTrackingProfileVO = ApplicationContext.getInstance()
                .getProfile(Profile.SERVER, loc.intValue()).getTimeTrackingProfile();
        Integer biweeklyPPPID = timeTrackingProfileVO.getBiweeklyPayPeriodProfileID();
        Integer weeklyPPPID = timeTrackingProfileVO.getWeeklyPayPeriodProfileID();

        logger.debug("biweeklyPPPID" + biweeklyPPPID);
        logger.debug("weeklyPPPID" + weeklyPPPID);
        if (biweeklyPPPID != null && weeklyPPPID != null) {
            retValue += Sch_TimeTrackingProfile.BIWEEKLY_PAY_PERIOD_PROFILE_ID + " OR "
                    + Sch_Employee.PAY_PERIOD_PROFILE_ID + " = "
                    + Sch_TimeTrackingProfile.WEEKLY_PAY_PERIOD_PROFILE_ID;
        } else if (biweeklyPPPID != null) {
            retValue += Sch_TimeTrackingProfile.BIWEEKLY_PAY_PERIOD_PROFILE_ID;
        } else if (weeklyPPPID != null) {
            retValue += Sch_TimeTrackingProfile.WEEKLY_PAY_PERIOD_PROFILE_ID;
        }
        logger.debug(retValue + retValue);
        return retValue;
    }
    
    private String whereClauseForClockInEmployees() {
        String retValue = " WHERE " + " ( " + Sch_Tables.EMPLOYEE + "."
                + Sch_Employee.PAY_PERIOD_PROFILE_ID + " = " + " ( " + "SELECT "
                + Sch_TimeTrackingProfile.WEEKLY_PAY_PERIOD_PROFILE_ID + " FROM "
                + Sch_Tables.TIME_TRACKING_PROFILE + " ) " + " OR " + Sch_Tables.EMPLOYEE + "."
                + Sch_Employee.PAY_PERIOD_PROFILE_ID + " = " + " ( " + "SELECT "
                + Sch_TimeTrackingProfile.BIWEEKLY_PAY_PERIOD_PROFILE_ID + " FROM "
                + Sch_Tables.TIME_TRACKING_PROFILE + " )) " + " AND DATE(" 
                + Sch_Tables.EMPLOYEE_CLOCK + "." + Sch_EmployeeClock.CLOCK_IN_DATE + ") >= "
                + Sch_PayPeriodProfile.CURRENT_PAY_PERIOD_BEGIN_DATE + " AND "
                + Sch_Tables.EMPLOYEE_CLOCK + "." + Sch_EmployeeClock.CLOCK_OUT_DATE + " IS NOT "
                + "NULL" + " AND " + Sch_Tables.EMPLOYEE_CLOCK + "."
                + Sch_EmployeeClock.EMPLOYEE_ID + " NOT IN " + " ( " + "SELECT "
                + Sch_Tables.EMPLOYEE_CLOCK + "." + Sch_EmployeeClock.EMPLOYEE_ID + " FROM "
                + Sch_Tables.EMPLOYEE_CLOCK + " WHERE " + Sch_EmployeeClock.CLOCK_OUT_DATE
                + " IS NULL " + " AND DATE(" + Sch_EmployeeClock.CLOCK_IN_DATE + ") BETWEEN "
                + Sch_PayPeriodProfile.CURRENT_PAY_PERIOD_BEGIN_DATE + " AND "
                + Sch_PayPeriodProfile.CURRENT_PAY_PERIOD_END_DATE + " )" + " AND DATE("
                + Sch_Tables.EMPLOYEE_CLOCK + "." + Sch_EmployeeClock.CLOCK_OUT_DATE + ") BETWEEN "
                + Sch_PayPeriodProfile.CURRENT_PAY_PERIOD_BEGIN_DATE + " AND "
                + Sch_PayPeriodProfile.CURRENT_PAY_PERIOD_END_DATE
                + " OR "+ "( " + Sch_EmployeeNonWorkedHours.REF_ACTIVITY_TYPE_ID 
                + " NOT IN (1,2,3) AND "+Sch_EmployeeNonWorkedHours.ACTIVITY_DATE +" BETWEEN "
                + Sch_PayPeriodProfile.CURRENT_PAY_PERIOD_BEGIN_DATE + " AND " 
                + Sch_PayPeriodProfile.CURRENT_PAY_PERIOD_END_DATE + ")";
        logger.debug(retValue + retValue);
        return retValue;
    }
    
    private String whereClauseForEmployeeAccountInfo(Integer loc, Integer empNum) {
		
		return  " WHERE " +
		        Sch_Employee.LOC + " = " + loc + " AND " +
		        Sch_Employee.EMPLOYEE_NUM + " = " + empNum + " AND ((" +
		        Sch_Employee.TERMINATION_DATE +
		        " IS NULL) OR (" +
		        Sch_Employee.TERMINATION_DATE +
		        " >= CURRENT DATE))";
		       
	}

	public Employee() {}

	 public Vector getEmployeeList(Integer loc, String typeOfList) throws PointOfSaleException, RemoteException {
		 return getEmployeeList(loc, typeOfList, null);
	 }

    //Business methods
    public Vector getEmployeeList(Integer loc, String typeOfList, Connection c) throws PointOfSaleException, RemoteException {
        Vector v = new Vector();
        EmployeeVO vo = null;
        String sSQL = EMPLOYEE_SELECT_SQL + whereClauseForSelectEmployee(loc, typeOfList);
        logger.debug("getEmployeeList = " + sSQL);
        boolean connectionWasPassed = true;
        try {
        	if (c == null) {
        		c = ServerUtil.getConnection();
        		connectionWasPassed = false;
        	}
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery(sSQL);
            while (rs.next()) {
                vo = new EmployeeVO();
                vo.setID((Integer)rs.getObject(Sch_Employee.ID));
                vo.setEmployeeNum((Integer)rs.getObject(Sch_Employee.EMPLOYEE_NUM));
                vo.setFirstName(rs.getString(Sch_Employee.FIRST_NAME));
                vo.setLastName(rs.getString(Sch_Employee.LAST_NAME));
                vo.setMiddleInitial(rs.getString(Sch_Employee.MIDDLE_INITIAL) == null ? "" : rs.getString(Sch_Employee.MIDDLE_INITIAL));
                vo.setRefLanguageCD(rs.getString(Sch_Employee.REF_LANGUAGE_CD));
                vo.setClockedIn(rs.getString(Sch_Employee.CLOCKED_IN));
                vo.setInvoicingAllowed(rs.getString(Sch_Employee.INVOICING_ALLOWED));
                v.add(vo);
            }
            JDBCUtil.closeResultSet(rs);
            JDBCUtil.closeStatement(stmt);

        } catch (SQLException e) {
            logger.error("getEmployeeList", e);
        } finally {
            if(c != null && !connectionWasPassed) {
                ServerUtil.releaseConnection(c);
            }
        }
        logger.debug("Done getEmployeeList");
        return v;
    }

    public EmployeeVO getEmployeeDetailInfo(Integer loc, Integer empNum) throws PointOfSaleException, RemoteException {
    	return getEmployeeDetailInfo(loc, empNum, null);
    }

    public EmployeeVO getEmployeeDetailInfo(Integer loc, Integer empNum, Connection conn) throws PointOfSaleException, RemoteException {
        EmployeeVO vo = null;
        String sSQL = EMPLOYEE_DETAIL_INFO_SELECT_SQL + whereClauseForEmployeeDetailInfo(loc, empNum);
        logger.debug("getEmployeeDetailInfo = " + sSQL);
        vo = getDetailInfo(sSQL, conn);
        logger.debug("Done getEmployeeDetailInfo");
        return vo;
    }


    public EmployeeVO getAllEmployeeDetailInfoByID(Integer loc, Integer ID) throws PointOfSaleException, RemoteException {
	 	return getAllEmployeeDetailInfo(loc, ID, null);
	}

	//gets all Employee details, including those who have been teminated.
	public EmployeeVO getAllEmployeeDetailInfoByID(Integer loc, Integer ID, Connection conn) throws PointOfSaleException, RemoteException {
	        EmployeeVO vo = null;
	        String sSQL = EMPLOYEE_DETAIL_INFO_SELECT_SQL + whereClauseForAllEmployeeDetailInfoByID(loc, ID);
	        vo = getDetailInfo(sSQL, conn);
	        return vo;
    }

    public EmployeeVO getAllEmployeeDetailInfo(Integer loc, Integer empNum) throws PointOfSaleException, RemoteException {
    	return getAllEmployeeDetailInfo(loc, empNum, null);
    }

	//gets all Employee details, including those who have been teminated.
    public EmployeeVO getAllEmployeeDetailInfo(Integer loc, Integer empNum, Connection conn) throws PointOfSaleException, RemoteException {
        EmployeeVO vo = null;
        String sSQL = EMPLOYEE_DETAIL_INFO_SELECT_SQL + whereClauseForAllEmployeeDetailInfoByID(loc, empNum);
        vo = getDetailInfo(sSQL, conn);
        return vo;
    }

    public EmployeeVO getEmployeeDetailInfoByID(Integer loc, Integer ID) throws PointOfSaleException, RemoteException {
		return getEmployeeDetailInfoByID(null, loc, ID);
    }

    public EmployeeVO getEmployeeDetailInfoByID(Connection conn, Integer loc, Integer ID) throws PointOfSaleException, RemoteException {
        EmployeeVO vo = null;
        String sSQL = EMPLOYEE_DETAIL_INFO_SELECT_SQL + whereClauseForEmployeeDetailInfoByID(loc, ID);
        logger.debug("getEmployeeDetailInfo = " + sSQL);
        vo = getDetailInfo(sSQL, conn);
        logger.debug("Done getEmployeeDetailInfo");
        return vo;
    }


    /** Method that populates a VO if you send it the right sql
     Just to avoid duplicate code. Not very clean.
     **/
    private EmployeeVO getDetailInfo(String sSQL, Connection c) {
        EmployeeVO vo = null;
        boolean connectionWasPassed = true;
        try {
        	if (c == null) {
        		c = ServerUtil.getConnection();
        		connectionWasPassed = false;
        	}
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery(sSQL);
            if (rs.next()) {
                vo = new EmployeeVO();
                vo.setID((Integer)rs.getObject(Sch_Employee.ID));
                vo.setEmployeeNum((Integer)rs.getObject(Sch_Employee.EMPLOYEE_NUM));
                vo.setFirstName(rs.getString(Sch_Employee.FIRST_NAME));
                vo.setLastName(rs.getString(Sch_Employee.LAST_NAME));
                vo.setMiddleInitial(rs.getString(Sch_Employee.MIDDLE_INITIAL) == null ? "" : rs.getString(Sch_Employee.MIDDLE_INITIAL));
                vo.setRefLanguageCD(rs.getString(Sch_Employee.REF_LANGUAGE_CD));
                vo.setRefCountryCD(rs.getString(Sch_Employee.REF_COUNTRY_CD));
                vo.setClockedIn(rs.getString(Sch_Employee.CLOCKED_IN));
                vo.setInvoicingAllowed(rs.getString(Sch_Employee.INVOICING_ALLOWED));
                vo.setPassword(rs.getString(Sch_Employee.PASSWORD));
                vo.setRefPayTypeID((Integer)rs.getObject(Sch_Employee.REF_PAY_TYPE_ID));
                vo.setCustomerID((Integer)rs.getObject(Sch_Employee.CUSTOMER_ID));
                vo.setTerminationDate((Timestamp)rs.getObject(Sch_Employee.TERMINATION_DATE));
                vo.setRefCatalogPreference((Integer)rs.getObject(Sch_Employee.REF_CATALOG_PREFERENCE_ID));
                vo.setDisplayGrossProfitAndCost(rs.getString(Sch_Employee.DISPLAY_GROSS_PROFIT_AND_COST));
                vo.setPasswordExpDate(rs.getDate(Sch_Employee.PASSWORD_EXP_DATE));
                vo.setRefPasswordStatus((Integer)rs.getObject(Sch_Employee.REF_PASSWORD_STATUS_ID));
                vo.setAllowChangeCatalogPreference(rs.getString(Sch_Employee.ALLOW_CHANGE_CATALOG_PREFERENCE));
                vo.setEmployeeLdapId(rs.getString(Sch_Employee.LDAP_EXTERNAL_IDENTIFIER));
            }
            JDBCUtil.closeResultSet(rs);
            JDBCUtil.closeStatement(stmt);

        }catch (SQLException e) {
            logger.error("getEmployeeDetailInfo", e);
        } finally {
            if(c != null && !connectionWasPassed) {
                ServerUtil.releaseConnection(c);
            }
        }
        return vo;
    }

    public EmployeeVO getSalesPersonInfo(Integer loc, Integer ID) throws PointOfSaleException, RemoteException {
    	return getSalesPersonInfo(loc, ID, null);
    }

    public EmployeeVO getSalesPersonInfo(Integer loc, Integer ID, Connection c) throws PointOfSaleException, RemoteException {
        EmployeeVO vo = null;
        int id = ID.intValue();
        boolean connectionWasPassed = true;
        StringBuffer sbSQL = new StringBuffer(512);
        sbSQL.append("SELECT ");
        //Fields to select
        sbSQL.append(Sch_Employee.EMPLOYEE_NUM + ", ").append(Sch_Employee.FIRST_NAME + ", ").append(Sch_Employee.LAST_NAME);
        sbSQL.append(", ").append(Sch_Employee.MIDDLE_INITIAL);
        //From cluse
        sbSQL.append(" FROM " + Sch_Tables.EMPLOYEE);
        //Where Cluase
        sbSQL.append(" WHERE " + Sch_Employee.ID + " = " + id);
        //.append (" AND " +  Sch_Employee.REF_EMPLOYEE_JOB_CODE_ID + " IN (" + SALESMAN_JOB_CODES + ")");
        sbSQL.append(" AND " + Sch_Employee.LOC + " = " + loc);
        logger.debug("getSalesPersonInfo = " + sbSQL.toString());
        try {
        	if (c == null) {
        		c = ServerUtil.getConnection();
        		connectionWasPassed = false;
        	}
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery(sbSQL.toString());
            if (rs.next()) {
                vo = new EmployeeVO();
                vo.setID(ID);
                vo.setEmployeeNum((Integer)rs.getObject(Sch_Employee.EMPLOYEE_NUM));
                vo.setFirstName(rs.getString(Sch_Employee.FIRST_NAME));
                vo.setLastName(rs.getString(Sch_Employee.LAST_NAME));
                vo.setMiddleInitial(rs.getString(Sch_Employee.MIDDLE_INITIAL) == null ? "" : rs.getString(Sch_Employee.MIDDLE_INITIAL));
            }
            JDBCUtil.closeResultSet(rs);
            JDBCUtil.closeStatement(stmt);

        } catch (SQLException e) {
            logger.error("getSalesPersonInfo", e);
        } finally {
            if(c != null && !connectionWasPassed) {
                ServerUtil.releaseConnection(c);
            }
        }
        logger.debug("Done getSalesPersonInfo");
        return vo;
    }

    //For now getEmployeeInfo looks very similar to getSalesPersonInfo,  in future this may populate
    //more information into employeeVO than getSalesPersonInfo does.  We should consider the
    //option to remove getSalesPersonInfo, insted keep only getEmployeeInfo even though it may
    //send more data to the client than need.  If we do removed getSalesPersonInfo, we will have
    //to modify EmployeeServiceImpl on the client side to use getEmployeeInfo rather than
    //getSalesPersonInfo
    public EmployeeVO getEmployeeInfo(Integer loc, Integer ID) throws PointOfSaleException, RemoteException {
        //needs to return language of employee
        EmployeeVO vo = getEmployeeDetailInfoByID(loc, ID);
        logger.debug("Done with getEmployeeInfo.");
        return vo;
    }

    public Vector getCurrentEmployeesList(Integer loc) throws PointOfSaleException, RemoteException {
        return getCurrentEmployeeList(loc, null);
    }

    public Vector getCurrentEmployeeList(Integer loc, Connection c) throws PointOfSaleException,
            RemoteException {
        Vector v = new Vector();
        EmployeeVO vo = null;
        String sSQL = CURRENT_EMPLOYEE_SELECT_SQL + whereClauseForCuurentPayPeriodEmployee(loc);
        logger.debug("getEmployeeList = " + sSQL);
        boolean connectionWasPassed = true;
        try {
            if (c == null) {
                c = ServerUtil.getConnection();
                connectionWasPassed = false;
            }
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery(sSQL);
            while (rs.next()) {
                vo = new EmployeeVO();
                vo.setEmployeeNum((Integer) rs.getObject(Sch_Employee.EMPLOYEE_NUM));
                vo.setFirstName(rs.getString(Sch_Employee.FIRST_NAME));
                vo.setLastName(rs.getString(Sch_Employee.LAST_NAME));
                v.add(vo);
            }
            JDBCUtil.closeResultSet(rs);
            JDBCUtil.closeStatement(stmt);

        } catch (SQLException e) {
            logger.error("getCurrentEmployeeList", e);
        } finally {
            if (c != null && !connectionWasPassed) {
                ServerUtil.releaseConnection(c);
            }
        }
        logger.debug("Done getCurrentEmployeeList");
        return v;
    }
    
    public Vector getTimeEnteredEmployeesList() throws PointOfSaleException, RemoteException {
        return getTimeEnteredEmployeesList(null);
    }
    
    public Vector getTimeEnteredEmployeesList(Connection c) throws PointOfSaleException,
            RemoteException {
        Vector v = new Vector();
        EmployeeVO vo = null;
        String sSQL = TIME_ENTERED_EMPLOYEE_SELECT_SQL + whereClauseForClockInEmployees();
        logger.debug("getTimeEnteredEmployeeList = " + sSQL);
        boolean connectionWasPassed = true;
        try {
            if (c == null) {
                c = ServerUtil.getConnection();
                connectionWasPassed = false;
            }
            Statement stmt = c.createStatement();
            ResultSet rs = stmt.executeQuery(sSQL);
            while (rs.next()) {
                vo = new EmployeeVO();
                vo.setEmployeeNum((Integer) rs.getObject(Sch_Employee.EMPLOYEE_NUM));
                vo.setFirstName(rs.getString(Sch_Employee.FIRST_NAME));
                vo.setLastName(rs.getString(Sch_Employee.LAST_NAME));
                v.add(vo);
            }
            JDBCUtil.closeResultSet(rs);
            JDBCUtil.closeStatement(stmt);

        } catch (SQLException e) {
            logger.error("getTimeEnteredEmployeeList", e);
        } finally {
            if (c != null && !connectionWasPassed) {
                ServerUtil.releaseConnection(c);
            }
        }
        logger.debug("Done getTimeEnteredEmployeeList");
        return v;
    }
    
    public EmployeeVO getStoreManagerOrDistrictOfficeEmpInfo(int location) {
        
        EmployeeVO employeeVO = null;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            connection = ServerUtil.getConnection();
            statement = connection.prepareStatement(SELECT_STORE_MANAGER_OR_DIST_OFFICE_EMP);
            statement.setInt(1, location);
            resultSet = statement.executeQuery();
            if(resultSet.next()) {
                employeeVO = new EmployeeVO();
                employeeVO.setID((Integer)resultSet.getObject(Sch_Employee.ID));
                employeeVO.setEmployeeNum((Integer) resultSet.getObject(Sch_Employee.EMPLOYEE_NUM));
            }

        } catch (SQLException sqlException) {
            logger.error("Exception occured while getting manager info ", sqlException);
        } finally {
            JDBCUtil.closeResultSet(resultSet);
            JDBCUtil.closePreparedStatement(statement);
            if (connection != null) {
                ServerUtil.releaseConnection(connection);
            }
        }
        return employeeVO;
    }
    
 

   

	public EmployeeVO getEmployeeAccountInfo(Integer loc, Integer empNum) throws RemoteException, PointOfSaleException {
		return getEmployeeAccountInfo(loc, empNum, null);
	}
	
	
	
	  private EmployeeVO getEmployeeAccountInfo(Integer loc, Integer empNum, Connection conn) throws PointOfSaleException, RemoteException {
		    EmployeeVO vo = null;
	        String sSQL = EMPLOYEE_ACCOUNT_INFO_SELECT_SQL + whereClauseForEmployeeAccountInfo(loc, empNum);
	        logger.debug("getEmployeeDetailInfo = " + sSQL);
	        vo = getAccountInfo(sSQL, conn);
	        logger.debug("Done getEmployeeDetailInfo");
	        return vo;
	}

	

	private EmployeeVO getAccountInfo(String sSQL, Connection c) {
	        EmployeeVO vo = null;
	        boolean connectionWasPassed = true;
	        try {
	        	if (c == null) {
	        		c = ServerUtil.getConnection();
	        		connectionWasPassed = false;
	        	}
	            Statement stmt = c.createStatement();
	            ResultSet rs = stmt.executeQuery(sSQL);
	            if (rs.next()) {
	                vo = new EmployeeVO();
	                vo.setID((Integer)rs.getObject(Sch_Employee.ID));
	                vo.setPasswordExpDate(rs.getDate(Sch_Employee.PASSWORD_EXP_DATE));
	                vo.setRefPasswordStatus((Integer)rs.getObject(Sch_Employee.REF_PASSWORD_STATUS_ID));
	               
	            }
	            JDBCUtil.closeResultSet(rs);
	            JDBCUtil.closeStatement(stmt);

	        }catch (SQLException e) {
	            logger.error("getEmployeeAccountInfo", e);
	        } finally {
	            if(c != null && !connectionWasPassed) {
	                ServerUtil.releaseConnection(c);
	            }
	        }
	        return vo;
	    }
    public String getEmployeeIdUsingNum(String empNum, String loc){
        StringBuilder buff = new StringBuilder();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        String result = null;
        try {
            buff.append("select ID from employee where EMPLOYEE_NUM = ? and LOC = ?");
            logger.debug("getEmployeeID query: " + buff);
            connection = ServerUtil.getConnection();
            preparedStatement = connection.prepareStatement(buff.toString());
            preparedStatement.setString(1, empNum);
            preparedStatement.setString(2, loc);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                result = resultSet.getObject("ID").toString();
            }
            return result;
        } catch (Exception e) {
            logger.error("getEmployeeID exception", e);
        } finally {
            JDBCUtil.closeResultSet(resultSet);
            JDBCUtil.closePreparedStatement(preparedStatement);
            ServerUtil.releaseConnection(connection);
        }
        return result;
    }
}