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

import gov.va.med.esr.common.model.cases.WorkflowCase;
import gov.va.med.esr.common.model.lookup.WkfCaseStatusType;
import gov.va.med.esr.common.model.lookup.WkfCaseType;
import gov.va.med.esr.common.model.person.id.PersonEntityKey;
import gov.va.med.esr.common.persistent.workflow.WorkflowDAO;
import gov.va.med.esr.service.WorkflowSearchCriteria;
import gov.va.med.esr.service.WorkflowSearchResultBean;
import gov.va.med.fw.model.EntityKey;
import gov.va.med.fw.model.EntityKeyFactory;
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.persistent.hibernate.PaginatedQueryExecutor;
import gov.va.med.fw.service.pagination.SearchQueryInfo;
import gov.va.med.fw.util.DateUtils;
import gov.va.med.fw.util.SortElement;
import gov.va.med.fw.util.StringUtils;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.HibernateSystemException;


/**
 * A DAO to provide specific business information about a  WorkflowCase 
 * Updated by Madhu 
 */

public class WorkflowDAOImpl extends GenericDAOImpl implements WorkflowDAO 
{    
    private static final long serialVersionUID = -603229775263841625L;

    private static final String SELECT_COUNT_CLAUSE = "SELECT COUNT(*)";
    /*private static final String SELECT_WKF_CLAUSE = "SELECT w";*/
        
    private static final String SELECT_WKF_CLAUSE = 
        //"select fg.description groupType, substr(p.vpid_value,7,17) shortVPID, " +
    	"select fg.description groupType,  " +
        "w.wkf_case_id workflowCaseID, w.person_id personID, ct.description caseType, " + 
        "w.record_created_date createdOn, a.WKF_CASE_ASSIGNMENT_DATE assignmentDate, " +
        "u.full_name assignedTo ";
    
    private static final String FROM_WKF_CLAUSE = " from " +
        "wkf_case w, wkf_case_status_detail s, wkf_case_assignment a, " +
        //"user_group_type fg, wkf_case_type ct, wkf_case_status_type st, person p, users u " +
        "user_group_type fg, wkf_case_type ct, wkf_case_status_type st, users u " +
        "where w.wkf_case_id = s.wkf_case_id and w.wkf_case_id = a.wkf_case_id " + "" +
        "and w.STD_USERGROUPTYPE_ID = fg.id " +
        "and w.WKF_CASE_TYPE_ID = ct.id " +
        "and s.WKF_CASE_STATUS_TYPE_ID = st.id " +
        //"and w.person_id = p.person_id and a.WKF_CASE_ASSIGNED_TO = u.username(+) "  ;
        "and a.WKF_CASE_ASSIGNED_TO = u.username(+) "  ;


    private static final String PERSON_OPEN_CASES_COUNT_QRY = 
        "workflowCaseQuery_getOpenCasesCountForPerson";

    private static final String MOST_RECENTLY_ADDED_CASE_QRY = 
    		"workflowCaseQuery_getMostRecentlyAddedWorkFlowCase";
    
    
    private static final String OPEN_MATCHING_CASES_QRY = 
        "workflowCaseQuery_getExistingMatchingWorkFlowCase";

	private static Map uiFieldToColumnMap;
    static
    {
    	uiFieldToColumnMap = new HashMap();
    	uiFieldToColumnMap.put("result.groupType", "groupType");
    	uiFieldToColumnMap.put("result.shortVPID", "shortVPID");
    	uiFieldToColumnMap.put("result.shortVPID", "personID");
    	uiFieldToColumnMap.put("result.personID", "personID");
    	uiFieldToColumnMap.put("result.workflowCaseID", "workflowCaseID");
    	uiFieldToColumnMap.put("result.caseType", "caseType");
    	uiFieldToColumnMap.put("result.createdOn", "createdOn");
    	uiFieldToColumnMap.put("result.assignmentDate", "assignmentDate");
    	uiFieldToColumnMap.put("result.assignedTo", "assignedTo");
    }
    
    public WorkflowDAOImpl()
    {
        super();
    }

    /**
     * Generic Non Paginated query
     * @param searchQueryInfo
     * @return
     * @throws DAOException
     */
    public List getWorkItems(WorkflowSearchCriteria wkfsearchCriteria) throws DAOException{        
		Map contextData = new HashMap();
		contextData.put("wkfsearchCriteria", wkfsearchCriteria);				        		        	        	
		HibernateCallback callback = new AbstractDAOAction(contextData) { 
			public Object execute(Session session) {
		        Query dataQuery = getSearchSelectDataQueryString(session,
		        		(WorkflowSearchCriteria) getContextData().get("wkfsearchCriteria"));
		        return dataQuery.list();				
			}
		};
		List workItems = this.getHibernateTemplate().executeFind(callback);
        //load lazy properties for these workflow cases
        for (int i=0; i<workItems.size(); i++) {
            loadLazyProperties ((WorkflowCase) workItems.get(i));
        }
        return workItems;
    }

    /**
     * Paginated search query
     */
    public List find(SearchQueryInfo searchQueryInfo) throws DAOException, MaxRecordsExceededException {
        
        WorkflowSearchCriteria wkfsearchCriteria = (WorkflowSearchCriteria) searchQueryInfo;  
                
		Map contextData = new HashMap();
		contextData.put("wkfsearchCriteria", wkfsearchCriteria);				        		        	        	
		HibernateCallback callback = new AbstractDAOAction(contextData) { 
			public Object execute(Session session) throws DAOException {
				WorkflowSearchCriteria targetQueryInfo = (WorkflowSearchCriteria) getContextData().get("wkfsearchCriteria");
		        // Build the count and data retrieval queries
		        Query countQuery = getSearchSelectCountQueryString(session, targetQueryInfo);                
		        Query dataQuery = getSearchSelectDataQueryString(session, targetQueryInfo);      
		        
		        // Execute the query to retrieve the results
		        PaginatedQueryExecutor queryExecutor = new PaginatedQueryExecutor(countQuery,dataQuery,targetQueryInfo);
	            try {
	            	List results = queryExecutor.executeQuery();
                    List items = new ArrayList ();
                    List personids = new ArrayList();
                    for (Iterator i = results.iterator(); i.hasNext();){
                        Object[] objArray = (Object[])i.next();
                        
                        WorkflowSearchResultBean bean = new WorkflowSearchResultBean();
                        bean.setGroupType((String)objArray[0]);
                        //bean.setShortVPID((String)objArray[1]);
                        bean.setWorkflowCaseID((BigDecimal)objArray[1]);
                        bean.setPersonId((BigDecimal)objArray[2]);
                        bean.setCaseType((String)objArray[3]);
                        bean.setCreatedOn((Date)objArray[4]);
                        bean.setAssignmentDate((Date)objArray[5]);
                        bean.setAssignedTo((String)objArray[6]);
                        items.add(bean);
                    }
                    return items;
	            } catch(MaxRecordsExceededException e) {
	            	throw new DAOException("max records exceeded", e);
	            }	            
			}
		};
		try {
			return this.getHibernateTemplate().executeFind(callback);
		} catch(HibernateSystemException e) {
			MaxRecordsExceededException rootCause = (MaxRecordsExceededException) getRootExceptionOfType(e, MaxRecordsExceededException.class);
			if(rootCause != null)
				throw rootCause;
			throw e;
		}    		
    }

    public int getOpenCasesCount(PersonEntityKey personEntityKey) throws DAOException {
        int openCases = 0;
        
        String[] paramNames = new String[] {"personId"};
        Object[] paramValues = new BigDecimal[] {(BigDecimal)personEntityKey.getKeyValue()};
        List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
                PERSON_OPEN_CASES_COUNT_QRY, paramNames, paramValues);

        openCases = ((results == null) || results.isEmpty()) ? 0
                : ((Integer)results.get(0)).intValue();
     
        return openCases;
    }

    public WorkflowCase getMostRecentlyCreatedWorkflowItem(PersonEntityKey personEntityKey) throws DAOException {
    	WorkflowCase wkfCase = null;
        
        String[] paramNames = new String[] {"personId"};
        Object[] paramValues = new BigDecimal[] {(BigDecimal)personEntityKey.getKeyValue()};
        List workItems = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
        		MOST_RECENTLY_ADDED_CASE_QRY, paramNames, paramValues);

		if (workItems != null && workItems.size() > 0) {
			wkfCase = (WorkflowCase) workItems.get(0);
		}
     
        return wkfCase;
    }
    
    /*
     * Check for all open cases to close.
     * CCR_9809
     * 
     * */
    public List getMatchingOpenCasesForAWorkItem(WorkflowCase workFlowCase) throws DAOException {
        PersonEntityKey key = workFlowCase.getPersonEntityKey();
        String[] paramNames = new String[] {"personId", "caseType", "errorMessageText"};
        Object[] paramValues = new Object[]{(BigDecimal)key.getKeyValue(), 
                (WkfCaseType)workFlowCase.getCaseType(), (String)workFlowCase.getErrorMessage()};
        
        List workflowCaseList = super.getHibernateTemplate().findByNamedQueryAndNamedParam(
                OPEN_MATCHING_CASES_QRY, paramNames, paramValues);
        
        return workflowCaseList;
    }
    
    public int getWorkItemsCount(WorkflowSearchCriteria workflowSearchCriteria) throws DAOException {
		Map contextData = new HashMap();
		contextData.put("workflowSearchCriteria", workflowSearchCriteria);				        		        	        	
		HibernateCallback callback = new AbstractDAOAction(contextData) { 
			public Object execute(Session session) {
		        Query query = getSearchSelectCountQueryString(session,
		        		(WorkflowSearchCriteria) getContextData().get("workflowSearchCriteria"));
		        return new Integer(executeCountQuery(query));				
			}
		};
		return ((Integer) this.getHibernateTemplate().execute(callback)).intValue();
    }
    
    
    /**
     * Count query
     * @param searchQueryInfo
     * @return
     */
    private Query getSearchSelectCountQueryString(Session session, WorkflowSearchCriteria searchCriteria) {
        
        // Create parameter and value lists
        ArrayList paramList = new ArrayList();
        ArrayList valueList = new ArrayList();
        
        // Get the count query
        String queryString = SELECT_COUNT_CLAUSE +
        getSearchFromClause(searchCriteria) +
        getSearchWhereClause(searchCriteria, paramList, valueList);
        
        Query query = session.createSQLQuery(queryString);

        // Prepare the count query by filling in the parameters values
        for (int i = 0; i < paramList.size(); i++) {
        	Object parValue = valueList.get(i);
        	if (parValue != null && parValue instanceof List) {
        		query.setParameterList((String)paramList.get(i), (List)parValue);
        	}
        	else {
        		query.setParameter((String)paramList.get(i), valueList.get(i));
        	}
        }
        return query;
    }
    /**
     * Data retrieval query
     * @param searchQueryInfo
     * @return
     */
    private Query getSearchSelectDataQueryString(Session session, WorkflowSearchCriteria searchCriteria) {
        // Create parameter and value lists
        ArrayList paramList = new ArrayList();
        ArrayList valueList = new ArrayList();
        
        // Get the count query
        String queryString = SELECT_WKF_CLAUSE + " " +
        getSearchFromClause(searchCriteria) +
        getSearchWhereClause(searchCriteria, paramList, valueList) +
        getSearchOrderByClause(searchCriteria.getSortElements());
        
        Query query = session.createSQLQuery(queryString);

        // Prepare the count query by filling in the parameters values
        for (int i = 0; i < paramList.size(); i++){
        	Object parValue = valueList.get(i);
        	if (parValue != null && parValue instanceof List) {
        		query.setParameterList((String)paramList.get(i), (List)parValue);
        	}
        	else {
        		query.setParameter((String)paramList.get(i), parValue);
        	}
        }
        return query;
    }
    
 
    //DNS   doank CCR8600
    //Build order by clause based on the sort elements acquired in the UI
    private String getSearchOrderByClause(List sortElements) {
    	StringBuffer clauseBuffer = new StringBuffer();
        if (sortElements != null)
        {
            for (Iterator iterator = sortElements.iterator(); iterator.hasNext();)
            {
                // Get the GUI sort column
                SortElement sortElement = (SortElement)iterator.next();

                // Get the database column associated with the GUI column
                String databaseColumn = (String)uiFieldToColumnMap.get(sortElement.getSortColumn());

                // If a database column exists for the GUI column, add it to the clause
                if (databaseColumn != null)
                {
                    if (clauseBuffer.length() > 0)
                    {
                        clauseBuffer.append(", ").append(databaseColumn).append(" ").append(sortElement.getSortDirection());
                    }
                    else
                    {
                        clauseBuffer.append(" ORDER BY ").append(databaseColumn).append(" ").append(sortElement.getSortDirection());
                    }
                }
                else
                {
                    // A GUI sort column is not supported by the database so don't implement the
                    // sort.  This will cause the PaginatedQueryExecutor to read all the records
                    // and require the GUI to handle the sorting.
                    return "";
                }
            }
        }
        return clauseBuffer.toString();
	}

	private String getSearchFromClause(WorkflowSearchCriteria searchCriteria) {
        //String fromClause = " from WorkflowCase w ";     FROM_WKF_CLAUSE   
        return FROM_WKF_CLAUSE;
    }
    
    /**
     * Execute count query
     * @param query
     * @return
     * @throws HibernateException
     */
    private int executeCountQuery(Query query) throws HibernateException {
        List results = query.list();     
        int count = 0;
        if (results.size() > 0) {
            Object resultCount = results.get(0);
            if (BigDecimal.class.isAssignableFrom(resultCount.getClass())){
                count = ((BigDecimal) resultCount).intValue();
            }
            else {
                count = ((Integer) resultCount).intValue();
            }
        }
        return count;
    }
    
    private String getSearchWhereClause(WorkflowSearchCriteria searchCriteria, List paramList, List valueList) {
        List criteriaList = new ArrayList();

        //functional Group
        if (searchCriteria.getFunctionalGroup() != null) {
                criteriaList.add("w.STD_USERGROUPTYPE_ID = :functionalGroup");
                paramList.add("functionalGroup");
                valueList.add(searchCriteria.getFunctionalGroup().getIdentifier());                
        }
        
        //workflow case id
        if (StringUtils.isNotEmpty(searchCriteria.getItemNumber())) {
            criteriaList.add("w.wkf_case_id = :itemNumber");
            paramList.add("itemNumber");
            valueList.add(new BigDecimal(searchCriteria.getItemNumber()));
        }
        
        //Create where clasue based on user selections(inputs)
        if(StringUtils.isNotEmpty(searchCriteria.getAssignedTo())) {
            criteriaList.add("a.WKF_CASE_ASSIGNED_TO = :assignedTo");
            paramList.add("assignedTo");
            valueList.add(searchCriteria.getAssignedTo());
        }
        //wkfcasetype
        if(searchCriteria.getItemType() != null){
            criteriaList.add("w.wkf_case_type_id = :caseType");
            paramList.add("caseType");
            valueList.add(searchCriteria.getItemType().getIdentifier());
        }  
        
        //wkfissuetype
        /*if(searchCriteria.getIssueType() != null){
            criteriaList.add("w.issueType = :issueType");
            paramList.add("issueType");
            valueList.add(searchCriteria.getIssueType());
        } */ 
        
        //WkfCaseStatusType 
        if(searchCriteria.getItemStatus() != null){
            criteriaList.add("s.WKF_CASE_STATUS_TYPE_ID = :itemStatus");
            paramList.add("itemStatus");
            valueList.add(searchCriteria.getItemStatus().getIdentifier());
        } //Retrive Only Open Items
        else if (searchCriteria.isOpenItems()) {
            criteriaList.add("st.code <> '" + WkfCaseStatusType.CLOSED.getName() + "'");               
        }
        
       //veteran open items
        if (StringUtils.isNotEmpty(searchCriteria.getPersonId())) {
            criteriaList.add("w.person_id = :personId");
            paramList.add("personId");
            valueList.add(new BigDecimal(searchCriteria.getPersonId()));
        }
        
        Date createDateFrom = searchCriteria.getCreateDateFrom();
        Date createDateTo = searchCriteria.getCreateDateTo();
        Date assignedDateFrom = searchCriteria.getAssignedDateFrom();
        Date assignedDateTo = searchCriteria.getAssignedDateTo();
        Date assignedDate = searchCriteria.getAssignedDate();
        Date createDate = searchCriteria.getCreateDate();
        
        if (createDate != null) {
        	createDateFrom = createDate;
            createDateTo = createDate;
        }
        
        if (createDateFrom != null) {
            criteriaList.add(" w.record_created_date >= :createDateFrom");
            paramList.add("createDateFrom");
            valueList.add(createDateFrom);
        }        
        if (createDateTo != null) {
            criteriaList.add(" w.record_created_date < :createDateTo");
            paramList.add("createDateTo");
            valueList.add(nextDay(createDateTo));
        }

        if (assignedDate != null) {
        	assignedDateFrom = assignedDate;
            assignedDateTo = assignedDate;
        }
        if (assignedDateFrom != null) {
            criteriaList.add(" a.WKF_CASE_ASSIGNMENT_DATE >= :assignedDateFrom");
            paramList.add("assignedDateFrom");
            valueList.add(assignedDateFrom);
        }
        
        if (assignedDateTo != null) {
            criteriaList.add(" a.WKF_CASE_ASSIGNMENT_DATE < :assignedDateTo");
            paramList.add("assignedDateTo");
            valueList.add(nextDay(assignedDateTo));
        }
        //assigned items
        if (searchCriteria.isAssignedItems()) {
            criteriaList.add("a.WKF_CASE_ASSIGNED_TO is not null");
        }
        //unassigned items
        if (searchCriteria.isUnassignedItems()) {
            criteriaList.add("a.WKF_CASE_ASSIGNED_TO is null");
        }
        
        //overdue items
        if (searchCriteria.isOverdueItems()) {
            Date currentDate = DateUtils.getCurrentDate();
            criteriaList.add(" w.CASE_DUE_DATE is not null and  w.CASE_DUE_DATE < :currentDate ");
            paramList.add("currentDate");
            valueList.add(currentDate);
        }
        
        /**
        List vpids = searchCriteria.getVpids();
        //todo fix when search criteria is compound (esr and psim)
          if (vpids != null && vpids.size() > 0) {
        	//we will be using just the first one as we will be searching by ssn
            criteriaList.add("p.VPID_Value in (:VPIDValue)");
            paramList.add("VPIDValue");
            valueList.add(vpids);            
        }
        **/

        List personids = searchCriteria.getPersonIds();
        //todo fix when search criteria is compound (esr and psim)
          if (personids != null && personids.size() > 0) {
        	//we will be using just the first one as we will be searching by ssn
            criteriaList.add("w.person_id in (:personIDs)");
            paramList.add("personIDs");
            valueList.add(personids);            
        }

          
        StringBuffer whereClause = new StringBuffer();
        if(!criteriaList.isEmpty()) {
            //whereClause.append(" where ").append(criteriaList.get(0));
            for(int index = 0; index < criteriaList.size(); index++) {
                whereClause.append(" and ").append(criteriaList.get(index));
            }
        }
        return whereClause.toString();        
    }
    
    public Object getByKey(EntityKey key) throws DAOException {
        Object entity = super.getByKey(key);
        //if the entity is retrived successfull - retrive the lazy loaded properties
        if (entity != null && entity instanceof WorkflowCase) {
            WorkflowCase wfCase = (WorkflowCase) entity;
            loadLazyProperties(wfCase);
        }
        return entity;
    }
    
    private void loadLazyProperties (WorkflowCase wfCase){
        //get comments
        accessSet(wfCase.getComments());
        accessSet(wfCase.getAssignmentHistory());
        accessSet(wfCase.getStatusHistory());        
    }
    /**
     * Get next dat
     * @param date
     * @return
     */
    private Date nextDay(Date date) {
        Calendar calendar = Calendar.getInstance();
        if (date != null)
        	calendar.setTime(date);
        //Add a day for the date
        calendar.add(Calendar.DATE, 1);
        return calendar.getTime();
    }
    /**
     * Access the set to initialize the lazy properties
     * @param set
     */
    private void accessSet (Set set){
        for (Iterator i= set.iterator(); i.hasNext();) {
            Object obj = i.next();
            break;
        }
    }
}  

