package gov.va.med.esr.common.persistent.security.hibernate;

import gov.va.med.esr.common.model.lookup.FunctionalGroup;
import gov.va.med.esr.common.model.security.CapabilitySet;
import gov.va.med.esr.common.model.security.EEServiceRequest;
import gov.va.med.esr.common.model.security.EEServiceRequestLite;
import gov.va.med.esr.common.model.security.EEServiceUser;
import gov.va.med.esr.common.model.security.ESRRolePrincipalImpl;
import gov.va.med.esr.common.model.security.ESRUserPrincipalImpl;
import gov.va.med.esr.common.model.security.UserLastLoginInfo;
import gov.va.med.esr.common.model.security.UserLogin;
import gov.va.med.esr.common.persistent.security.SecurityDAO;
import gov.va.med.esr.service.PasswordChangeHistory;
import gov.va.med.fw.model.EntityKey;
import gov.va.med.fw.model.RolePrincipalImpl;
import gov.va.med.fw.model.lookup.Lookup;
import gov.va.med.fw.persistent.DAOException;
import gov.va.med.fw.persistent.MaxRecordsExceededException;
import gov.va.med.fw.persistent.hibernate.AbstractDAOAction;
import gov.va.med.fw.persistent.hibernate.GenericDAOImpl;
import gov.va.med.fw.security.UserPrincipal;
import gov.va.med.fw.util.StringUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang.Validate;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;
import org.springframework.dao.DataAccessException;
import org.springframework.orm.hibernate3.HibernateCallback;

/**
 * SecurityDAO implementation.
 * @author DNS   MANSOG
 */
public class SecurityDAOImpl
    extends GenericDAOImpl
    implements SecurityDAO {
    
    private static final long serialVersionUID = -2161487908175786748L;
    
    private static final String FIND_BY_USER_NAME = "userPrincipal_FindByUserName";
    private static final String PARAM_NAME        = "name";
    private static final String PARAM_USERID        = "userid";
    private static final String PARAM_FUNCTIONAL_GROUP_NAME = "functinalGroupName";
    private static final String PARAM_ROLE_NAME        = "roleName";
    private static final String PARAM_REQUEST_ID        = "requestId";
        
    private static final String FIELD_USERNAME = "name";
    private static final String FIELD_GIVEN_NAME = "givenName";
    private static final String FIELD_FAMILY_NAME = "familyName";
    private static final String FIELD_MIDDLE_NAME = "middleName";
    
    private static final String QRY_FIND_ALL_CAPABILITIES = "capabilitySet_FindAll";
    private static final String QRY_FIND_ALL_ROLES = "rolePrincipal_FindAll";
    private static final String QRY_FIND_ALL_USERS = "userPrincipal_FindAll";
    private static final String QRY_LOOKUP_USER = "userPrincipal_lookupUser";
    private static final String QRY_LOOKUP_USER_BY_FULLNAME = "userPrincipal_lookupUserByFullname";
    private static final String QRY_FIND_USERS_BY_ROLE_NAME = "userPrincipal_FindByUserRoleName";
    private static final String QRY_FIND_USERS_BY_FUNCTIONAL_GROUP = 
    	"userPrincipal_FindByUserFunctionalGroup";
    private static final String QRY_FIND_USERS_BY_ROLE_NAME_AND_FUNCTIONAL_GROUP = 
    	"userPrincipal_FindByUserRoleNameAndFunctionalGroup";
    private static final String QRY_FIND_USERS_BY_CAPABILITY_NAME = "userPrincipal_FindByUserCapabilityName";
    private static final String QRY_PASSWORD_CHANGE_HISTORY = "userPrincipal_passwordChangeHistory";
    
    private static final String QRY_SERVICE_REQUEST_ALL = "servicerequest_all";
    
    private static final String QRY_SERVICE_REQUEST_BYNAME = "servicerequest_byName";
    
    private static final String QRY_SERVICE_REQUESTLITE_ALL = "servicerequestlite_all";
    
    private static final String QRY_SERVICE_REQUESTLITE_BYNAME = "servicerequestlitelite_byName";

    
    private static final String QRY_SERVICE_USER_ALL = "serviceuser_all";
    
    private static final String QRY_SERVICE_USER_BYUSERNAME = "serviceuser_byUserName";
    
    private static final String QRY_SERVICE_REQUEST_FIELDS_BY_REQ_ID = "eeServiceRequest.fieldsSet";
    
    /**
     * Default constructor.
     */
    public SecurityDAOImpl() {
        super();
    }

    /*
     * (non-Javadoc)
     * 
     * @see gov.va.med.esr.common.persistent.security.SecurityDAO#findUserByUsername(java.lang.String)
     */
    public UserPrincipal findUserByUsername(String username)
            throws DAOException {
        Validate.notNull(username, "NULL username not allowed.");
        try {
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { username };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
                    FIND_BY_USER_NAME, paramNames, paramValues);

            return ((results == null) || results.isEmpty()) ? null : (UserPrincipal) results.get(0);
        } catch (DataAccessException e) {
            throw new DAOException("Failed find UserPrincipal by username.", e);
        }
    }

    /**
     * Search for User by the given criteria
     */
    public UserPrincipal findUser(UserPrincipal userPrincipal)
        throws DAOException, MaxRecordsExceededException {
        
        Validate.notNull(userPrincipal, "NULL userPrincipal not allowed.");        
        try {
			Map contextData = new HashMap();
			contextData.put("userPrincipal", userPrincipal);				        		        	
			HibernateCallback callback = new AbstractDAOAction(contextData) { 
				public Object execute(Session session) {
					UserPrincipal targetUserPrincipal = (UserPrincipal) this.getContextData().get("userPrincipal");
		            //Dynamicaly build the query based on the user input
		            Criteria criteria = session.createCriteria(ESRUserPrincipalImpl.class);
		            
		            if (StringUtils.isNotEmpty(targetUserPrincipal.getName()))
		                criteria.add(Expression.ilike(FIELD_USERNAME,targetUserPrincipal.getName().trim()));            
		            if (StringUtils.isNotEmpty(targetUserPrincipal.getFamilyName()))
		                criteria.add(Expression.ilike(FIELD_FAMILY_NAME,targetUserPrincipal.getFamilyName().trim()));
		            if (StringUtils.isNotEmpty(targetUserPrincipal.getGivenName()))
		                criteria.add(Expression.ilike(FIELD_GIVEN_NAME,targetUserPrincipal.getGivenName().trim()));            
		            if (targetUserPrincipal.getMiddleName() != null)
		                criteria.add(Expression.ilike(FIELD_MIDDLE_NAME,targetUserPrincipal.getMiddleName().trim()));
		            
		            return criteria.list();					
				}
			};
			List results = this.getHibernateTemplate().executeFind(callback);
            
            if ((results == null) || results.isEmpty()) {
                //error no mactchng results
                return null;
            }
            else if (results.size() > 1) {
                //error message more than one result found
                throw new MaxRecordsExceededException(results.size(),1);
            }
            else {
                //Unique record found
                return (UserPrincipal) results.get(0);
            }
            
        } catch (DataAccessException e) {
            throw new DAOException("Failed find UserPrincipal by username.", e);
        }
    }    
    /*
     * (non-Javadoc)
     * 
     * @see gov.va.med.esr.common.persistent.security.SecurityDAO#getById(java.lang.String)
     */
    public UserPrincipal getUserById(EntityKey userId) throws DAOException {
        Validate.notNull(userId, "User identifier can not be null.");
        Validate.notNull(userId.getKeyValue(),
                "User identifier can not be null.");
        try {
            return (UserPrincipal) getHibernateTemplate().get(ESRUserPrincipalImpl.class, userId.getKeyValue());
        } catch (DataAccessException e) {
            throw new DAOException("Failed to get an entity "
                    + ESRUserPrincipalImpl.class.getName() + " by id "
                    + userId.getKeyValue(), e);
        }
    }
    
    
    public UserLastLoginInfo getUserLastLoginInfo(EntityKey userId) throws DAOException {
        Validate.notNull(userId, "User identifier can not be null.");
        Validate.notNull(userId.getKeyValue(), "User identifier can not be null.");
        UserLastLoginInfo loginInfo = new UserLastLoginInfo();
        try {
			Map contextData = new HashMap();
			contextData.put("userId", userId);				        		        	
			HibernateCallback callback = new AbstractDAOAction(contextData) { 
				public Object execute(Session session) {
		            String hqlQuery = 
		                "select max(successfulLoginDate) as successDate, max(failedLoginDate) as failedDate " +
		                "FROM   UserLogin WHERE  USERID = ?" ; //+ ((EntityKey) getContextData().get("userId")).getKeyValueAsString();
		                
		                Query query = session.createQuery(hqlQuery);
		                query.setString(0, ((EntityKey) getContextData().get("userId")).getKeyValueAsString());
		                return query.list();					
				}
			};
			List results = this.getHibernateTemplate().executeFind(callback);
			            
            if (results != null && results.size() > 0) {
                Object[] data = (Object[])results.get(0);
                loginInfo.setSuccessfulLoginDate((Date)data[0]);
                loginInfo.setFailedLoginDate((Date)data[1]);               
            }            
        } catch (DataAccessException e) {
            throw new DAOException("Failed to get User Login Info "
                    + UserLastLoginInfo.class.getName() + " by user id "
                    + userId.getKeyValueAsString(), e);
        }
        return loginInfo;
    }
    
    public List getAllCapabilitySets() throws DAOException {
        //Find all the Capability sets
        List sets = find(QRY_FIND_ALL_CAPABILITIES);
        if (sets != null && sets.size() > 0) {
            //find the list of assigned Capabilities
			HibernateCallback callback = new AbstractDAOAction() { 
				public Object execute(Session session) {
		            String hqlQuery = 
		                "select distinct(PERMISSION_SET_ID) from USER_PERMISSION_SETS " +
		                "union select distinct(PERMISSION_SET_ID) from ROLE_PERMISSION_SETS";
		                
		            Query query = session.createSQLQuery(hqlQuery);
		            return query.list();					
				}
			};
			List assigned = this.getHibernateTemplate().executeFind(callback);
			                
            //List assigned = find("capabilitySet_FindAssignedList");
            if (assigned != null && assigned.size() > 0) {
                Set assignedSetIds = new HashSet(assigned);
                for (Iterator i=sets.iterator(); i.hasNext();) {
                    CapabilitySet set = (CapabilitySet) i.next();
                    if (assignedSetIds.contains(set.getEntityKey().getKeyValue())) {
                        set.setAssigned(Boolean.TRUE);
                    }
                    else
                        set.setAssigned(Boolean.FALSE);
                }
            }
        }
        return sets;
    }
    public List getAllRoles() throws DAOException {
        //Find all roles
        List roles = find(QRY_FIND_ALL_ROLES);
        if (roles != null && roles.size() > 0) {
            //find the list of assigned Capabilities
			HibernateCallback callback = new AbstractDAOAction() { 
				public Object execute(Session session) {
		            String sqlQuery = 
		                "select distinct(ROLE_ID) from USER_ROLES";
		                
		            Query query = session.createSQLQuery(sqlQuery);
		            return query.list();					
				}
			};
			List assigned = this.getHibernateTemplate().executeFind(callback);
                
            //List assigned = find("capabilitySet_FindAssignedList");
            if (assigned != null && assigned.size() > 0) {
                Set assignedSetIds = new HashSet(assigned);
                for (Iterator i=roles.iterator(); i.hasNext();) {
                    ESRRolePrincipalImpl role = (ESRRolePrincipalImpl) i.next();
                    if (assignedSetIds.contains(role.getEntityKey().getKeyValue())) {
                        role.setAssigned(Boolean.TRUE);
                    }
                    else 
                        role.setAssigned(Boolean.FALSE);
                }
            }
        }
        return roles;        
    }
    
    public CapabilitySet findCapabilitySetByName(String name)throws DAOException {    
        Validate.notNull(name, "NULL name not allowed.");        
        try {            
            //Dynamicaly build the query based on the user input
			Map contextData = new HashMap();
			contextData.put("name", name);				        		        	        	
			HibernateCallback callback = new AbstractDAOAction(contextData) { 
				public Object execute(Session session) {
		            Criteria criteria = session.createCriteria(CapabilitySet.class);        
		            criteria.add(Expression.ilike(PARAM_NAME,
		            		((String) getContextData().get("name")).trim()));            
		            
		            return criteria.list();					
				}
			};
			
			List results = this.getHibernateTemplate().executeFind(callback);
            
            if ((results == null) || results.isEmpty()) {
                return null;
            }
            //we are only expecting one record here as the name is unique
            else {
                return (CapabilitySet) results.get(0);
            }
            
        } catch (DataAccessException e) {
            throw new DAOException("Failed find CapabilitySet by Name.", e);
        }
    }

    public RolePrincipalImpl findRoleByName(String name)throws DAOException {
        Validate.notNull(name, "NULL name not allowed.");        
        try {
            
            //Dynamicaly build the query based on the user input
			Map contextData = new HashMap();
			contextData.put("name", name);				        		        	        	
			HibernateCallback callback = new AbstractDAOAction(contextData) { 
				public Object execute(Session session) {
		            Criteria criteria = session.createCriteria(RolePrincipalImpl.class);        
		            criteria.add(Expression.ilike(PARAM_NAME,
		            		((String) getContextData().get("name")).trim()));            
		            
		            return criteria.list();					
				}
			};
			List results = this.getHibernateTemplate().executeFind(callback);
            
            if ((results == null) || results.isEmpty()) {
                return null;
            }
            //we are only expecting one record here as the name is unique
            else {
                return (RolePrincipalImpl) results.get(0);
            }
            
        } catch (DataAccessException e) {
            throw new DAOException("Failed find Role by Name.", e);
        }
    }
    public List findDeletedRoles() throws DAOException {
        return null;
    }
    
    public List findDeletedCapabilitySets() throws DAOException {
        return null;
    }
    
    /**
     * Used both by the lookup services and user profile service
     */
    public List findAllUsers() throws DAOException {
        List users = new ArrayList ();
        try {                       
            List results = find(QRY_FIND_ALL_USERS);
            for (Iterator i=results.iterator(); i.hasNext();) {
                Object[] data = (Object[])i.next();
                ESRUserPrincipalImpl user = buildUser(data); //userid
                users.add(user);
            }
           
        } catch (DataAccessException e) {
            throw new DAOException("Failed to find all users.", e);
        }
        return users;
    }
    /**
     * Find users by the associated role name
     * @param roleName
     * @return
     * @throws DAOException
     */
    public List findUsersByRoleName(String roleName) throws DAOException {
    	List users = new ArrayList ();
        try {                       
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { roleName };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_FIND_USERS_BY_ROLE_NAME, paramNames, paramValues);

            for (Iterator i=results.iterator(); i.hasNext();) {
                Object[] data = (Object[])i.next();
                ESRUserPrincipalImpl user = buildUser(data); //userid
                users.add(user);
            }            
        } catch (DataAccessException e) {
            throw new DAOException("Failed to find User list by role name.", e);
        }
        return users;    	
    }
    /**
     * Find users in the given functional group
     */
    public List findUsersByFunctionalGroup(FunctionalGroup functionalGroup) throws DAOException
    {
    	List users = new ArrayList ();
    	if (functionalGroup == null ) 
    		return users;
    	
        try {                       
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { functionalGroup.getCode() };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_FIND_USERS_BY_FUNCTIONAL_GROUP, paramNames, paramValues);

            for (Iterator i=results.iterator(); i.hasNext();) {
                Object[] data = (Object[])i.next();
                ESRUserPrincipalImpl user = buildUser(data); //userid
                users.add(user);
            }            
        } catch (DataAccessException e) {
            throw new DAOException("Failed to find User list by Functional Group.", e);
        }
        return users;    	
    }
    
    /**
     * Find users in the given functional group with the associated role
     */
    public List findUsersByRoleAndFunctionalGroup(FunctionalGroup functionalGroup, String roleName) throws DAOException
    {
    	List users = new ArrayList ();
    	if (functionalGroup == null ) 
    		return users;
    	
        try {                       
            String[] paramNames = new String[] { PARAM_FUNCTIONAL_GROUP_NAME, PARAM_ROLE_NAME};
            Object[] paramValues = new String[] { functionalGroup.getCode(), roleName  };
            
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_FIND_USERS_BY_ROLE_NAME_AND_FUNCTIONAL_GROUP, 
            		paramNames, paramValues);

            for (Iterator i=results.iterator(); i.hasNext();) {
                Object[] data = (Object[])i.next();
                ESRUserPrincipalImpl user = buildUser(data); //userid
                users.add(user);
            }            
        } catch (DataAccessException e) {
            throw new DAOException("Failed to find User list by Role and Functional Group.", e);
        }
        return users;    	
    }
    
    /**
     * Find active users by the associated Capability Name
     */
    public List findUsersByCapabilityCode(String capabilityCode) throws DAOException
    {
    	List users = new ArrayList ();
        try {                       
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { capabilityCode };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_FIND_USERS_BY_CAPABILITY_NAME, paramNames, paramValues);

            for (Iterator i=results.iterator(); i.hasNext();) {
                Object[] data = (Object[])i.next();
                ESRUserPrincipalImpl user = buildUser(data); //userid
                users.add(user);
            }            
        } catch (DataAccessException e) {
            throw new DAOException("Failed to User list by role name", e);
        }
        return users;    	
    }
    
    /**
     * returns object to be used by the lookup services
     * @param userName
     * @return
     * @throws DAOException
     */
    public Lookup getByCode(String userName) throws DAOException {
        
        ESRUserPrincipalImpl user = null;
        try {                                  
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { userName };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
                    QRY_LOOKUP_USER, paramNames, paramValues);
            if (results != null & results.size() > 0) {
                for (Iterator i=results.iterator(); i.hasNext();) {
                    Object[] data = (Object[])i.next();
                    user = buildUser(data); //userid
                    break;
                }           
            }
        } catch (DataAccessException e) {
            throw new DAOException("Failed to getByCode.", e);
        }
        
        //not found got an empty result set
        //Create a default user with the given name as the user could be a non-esr user
        if (user == null) {
        	user = buildDefaultUser(userName); //userid
        }
        return user;
    }
    
    /* (non-Javadoc)
	 * @see gov.va.med.esr.common.persistent.security.SecurityDAO#getByFullname(java.lang.String)
	 */
	public Lookup getByFullname(String userName) throws DAOException {

        ESRUserPrincipalImpl user = null;
        try {                                  
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { userName+"%" };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_LOOKUP_USER_BY_FULLNAME, paramNames, paramValues);
            if (results != null & results.size() > 0) {
                for (Iterator i=results.iterator(); i.hasNext();) {
                    Object[] data = (Object[])i.next();
                    user = buildUser(data);
                    break;
                }           
            }
        } catch (DataAccessException e) {
            throw new DAOException("Failed to getByFullName.", e);
        }
        
        return user;
	}

	/**
     * update user principla and log user login attempts
     */
    public void updateAndLog(UserPrincipal userPrincipal, UserLogin userLogin) throws DAOException
    {
    	ESRUserPrincipalImpl user = (ESRUserPrincipalImpl) userPrincipal;
    	ESRUserPrincipalImpl userinDB = (ESRUserPrincipalImpl)getUserById(user.getEntityKey());
    	//copy the data
    	merge(user,userinDB);
    	update(userinDB);
    	insertObject(userLogin);
    }
    
    public void updatePassword(UserPrincipal userPrincipal) throws DAOException
    {
        ESRUserPrincipalImpl user = (ESRUserPrincipalImpl) userPrincipal;
        ESRUserPrincipalImpl userinDB = (ESRUserPrincipalImpl)getUserById(user.getEntityKey());
        //copy the data
        userinDB.setPassword(user.getPassword());
        userinDB.setPasswordChangeDate(user.getPasswordChangeDate());
        userinDB.setFailAttemptCount(user.getFailAttemptCount());
        userinDB.setPasswordExpireDate(user.getPasswordExpireDate());
        update(userinDB);
    }
    
    public List getPasswordChangeHistory(EntityKey userId) throws DAOException
    {
        List pwdHistory = new ArrayList ();
        try {                       
            String[] paramNames = new String[] { PARAM_USERID };
            Object[] paramValues = new Object[] {userId.getKeyValue() };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
                    QRY_PASSWORD_CHANGE_HISTORY, paramNames, paramValues);

            for (Iterator i=results.iterator(); i.hasNext();) {
                Object[] data = (Object[])i.next();                
                pwdHistory.add(new PasswordChangeHistory((Date)data[0],(String)data[1], (String)data[2]));
            }
            
        } catch (DataAccessException e) {
            throw new DAOException("Failed to User list by role name", e);
        }
        return pwdHistory;   
    }
    
    public List getEEServiceRequests() throws DAOException {
        List requests = null;
        try {                       
        	requests = super.getHibernateTemplate().findByNamedQuery(
                    QRY_SERVICE_REQUEST_ALL);

        } catch (DataAccessException e) {
            throw new DAOException("Failed to request list by name", e);
        }
        return requests; 
    }
    
    public List getEEServiceLiteRequests() throws DAOException {
        List requests = null;
        try {                       
        	requests = super.getHibernateTemplate().findByNamedQuery(
                    QRY_SERVICE_REQUESTLITE_ALL);

        } catch (DataAccessException e) {
            throw new DAOException("Failed to request list by name", e);
        }
        return requests; 
    }
    
    
    public EEServiceRequest getEEServiceRequest(EntityKey eeServiceRequestId) throws DAOException {
        Validate.notNull(eeServiceRequestId, "eeServiceRequestId identifier can not be null.");
        Validate.notNull(eeServiceRequestId.getKeyValue(),
                "eeServiceRequest identifier can not be null.");
        try {
            return (EEServiceRequest) getHibernateTemplate().get(EEServiceRequest.class, eeServiceRequestId.getKeyValue());
        } catch (DataAccessException e) {
            throw new DAOException("Failed to get an entity "
                    + EEServiceRequest.class.getName() + " by id "
                    + eeServiceRequestId.getKeyValue(), e);
        }
    }
    
    
    public EEServiceRequestLite getEEServiceRequestLite(EntityKey eeServiceRequestId) throws DAOException {
        Validate.notNull(eeServiceRequestId, "eeServiceRequestId identifier can not be null.");
        Validate.notNull(eeServiceRequestId.getKeyValue(),
                "eeServiceRequest identifier can not be null.");
        try {
            return (EEServiceRequestLite) getHibernateTemplate().get(EEServiceRequestLite.class, eeServiceRequestId.getKeyValue());
        } catch (DataAccessException e) {
            throw new DAOException("Failed to get an entity "
                    + EEServiceRequestLite.class.getName() + " by id "
                    + eeServiceRequestId.getKeyValue(), e);
        }
    }
    
    
    public EEServiceRequest getEEServiceRequestByName(String name) throws DAOException {
        Validate.notNull(name, "NULL rquest not allowed.");
        try {
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { name };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_SERVICE_REQUEST_BYNAME, paramNames, paramValues);

            return ((results == null) || results.isEmpty()) ? null : (EEServiceRequest) results.get(0);
        } catch (DataAccessException e) {
            throw new DAOException("Failed find EEServiceRequest by name.", e);
        }
    }

    
    public Set getEEServiceRequestFieldsByRequest(EntityKey eeServiceRequestId) throws DAOException {
        Validate.notNull(eeServiceRequestId, "eeServiceRequestId identifier can not be null.");
        Validate.notNull(eeServiceRequestId.getKeyValue(),
                "eeServiceRequest identifier can not be null.");
        try {
        	
            String[] paramNames = new String[] { PARAM_REQUEST_ID };
            Object[] paramValues = new String[] { eeServiceRequestId.getKeyValueAsString() };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_SERVICE_REQUEST_FIELDS_BY_REQ_ID, paramNames, paramValues);

            if (results != null && results.size() > 0  ) {
            	HashSet fields = new HashSet();
            	fields.addAll(results);
            	return fields;
            }
            
        } catch (DataAccessException e) {
            throw new DAOException("Failed to get an fields  "
                     + " by id "  + eeServiceRequestId.getKeyValue(), e);
        }
    	return null;
    }
    
    public EEServiceRequestLite getEEServiceRequestLiteByName(String name) throws DAOException {
        Validate.notNull(name, "NULL rquest not allowed.");
        try {
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { name };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_SERVICE_REQUESTLITE_BYNAME, paramNames, paramValues);

            return ((results == null) || results.isEmpty()) ? null : (EEServiceRequestLite) results.get(0);
        } catch (DataAccessException e) {
            throw new DAOException("Failed find EEServiceRequest by name.", e);
        }

    }

    
    
    public List getEEServiceUsers() throws DAOException {
        List requests = null;
        try {                       
        	requests = super.getHibernateTemplate().findByNamedQuery(
                    QRY_SERVICE_USER_ALL);

        } catch (DataAccessException e) {
            throw new DAOException("Failed to request list by name", e);
        }
        return requests; 
    }
    
    public EEServiceUser getEEServiceUserByName(String name) throws DAOException {
        Validate.notNull(name, "NULL rquest not allowed.");
        try {
            String[] paramNames = new String[] { PARAM_NAME };
            Object[] paramValues = new String[] { name };
            List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
            		QRY_SERVICE_USER_BYUSERNAME, paramNames, paramValues);

            return ((results == null) || results.isEmpty()) ? null : (EEServiceUser) results.get(0);
        } catch (DataAccessException e) {
            throw new DAOException("Failed find EEServiceRequest by name.", e);
        }

    }
    public EEServiceUser getEEServiceUserById(EntityKey eeServiceUserId) throws DAOException {
        Validate.notNull(eeServiceUserId, "eeServiceRequestId identifier can not be null.");
        Validate.notNull(eeServiceUserId.getKeyValue(),
                "eeServiceRequest identifier can not be null.");
        try {
            return (EEServiceUser) getHibernateTemplate().get(EEServiceUser.class, eeServiceUserId.getKeyValue());
        } catch (DataAccessException e) {
            throw new DAOException("Failed to get an entity "
                    + EEServiceRequest.class.getName() + " by id "
                    + eeServiceUserId.getKeyValue(), e);
        }
    }
    /**
     * UserPrincipal objects are merged in case of authetication failure and success logins
     * to prevent unnecessary updates
     * @param user
     * @param userinDB
     */
    private void merge(ESRUserPrincipalImpl user, ESRUserPrincipalImpl userinDB){
    	//check for user attributes and merge
    	userinDB.setInitialLoginDate(user.getInitialLoginDate());
    	userinDB.setSignatureVerified(user.getSignatureVerified());
    	userinDB.setLockDate(user.getLockDate());
    	userinDB.setFailAttemptCount(user.getFailAttemptCount());
    }
    
    private ESRUserPrincipalImpl buildUser(Object[] data) {        
    	ESRUserPrincipalImpl user = new ESRUserPrincipalImpl((String)data[0]); //userid
        user.setFullName((String)data[1]);
        user.setGivenName((String)data[2]);
        user.setFamilyName((String)data[3]);
        user.setMiddleName((String)data[4]);
        return user;
    }
    
    private ESRUserPrincipalImpl buildDefaultUser(String userName) {        
    	ESRUserPrincipalImpl user = new ESRUserPrincipalImpl(userName); //userid
        user.setFullName(userName);
        user.setGivenName(userName);
        return user;
    }
    
    
}