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


import gov.va.med.esr.common.model.comms.HandbookBatchFileProcessStatistics;
import gov.va.med.esr.common.model.comms.HandbookBatchStatistics;
import gov.va.med.esr.common.model.comms.HandbookStatsColumnInfo;
import gov.va.med.esr.common.persistent.comms.HandbookBatchFileProcessStatisticsDAO;
import gov.va.med.fw.persistent.DAOException;
import gov.va.med.fw.persistent.hibernate.AbstractDAOImpl;


import java.text.Format;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.springframework.dao.DataAccessException;

public class HandbookBatchFileProcessStatisticsDAOImpl extends  AbstractDAOImpl implements HandbookBatchFileProcessStatisticsDAO{

	/**
	 * 
	 */
	private static final long serialVersionUID = 7438726047987826137L;
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_BATCHNUM = "handBookBatchFileProcess_statistics_FileProcessBatchNum";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_TOTALRECORDSNUM = "handBookBatchFileProcess_statistics_FileProcessRecordsNum";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_PFINSERT_SENTTOCMS = "handBookBatchFileProcess_statistics_sentToCMS_400F";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_HB_SENTTOCMS = "handBookBatchFileProcess_statistics_sentToCMS_400H";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_BENEFITINSERT_SENTTOCMS = "handBookBatchFileProcess_statistics_sentToCMS_400B";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_REJ_HEC = "handBookBatchFileProcess_statistics_rejectedByHEC";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_MAILEDBYCMS = "handBookBatchFileProcess_statistics_mailedByCMS";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_ONLINE = "handBookBatchFileProcess_statistics_onLine";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_ERRCMS = "handBookBatchFileProcess_statistics_erroredByCMS";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_CODE1REJ = "handBookBatchFileProcess_statistics_code1Reject";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_RETUSPS = "handBookBatchFileProcess_statistics_returnedByUSPS";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_VBR_HB = "handBookBatchFileProcess_statistics_handbooksInVBR";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_VBR_BAG = "handBookBatchFileProcess_statistics_benefitsAtAGlanceInVBR";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_DATE_LIST="jobResultDateListQuery";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_MINJOBSTARTDATE="jobResultMinJobStartDate";
	private static final String QRY_HANDBOOK_BATCH_FILE_PROCESS_MAXJOBENDDATE="jobResultMaxJobEndDate";

	private static final HandbookStatsColumnInfo[] queryToCountIndexMapList = {
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_REJ_HEC, HandbookBatchStatistics.REJ_BY_HEC),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_PFINSERT_SENTTOCMS, HandbookBatchStatistics.PF_INSERT_SENT_TO_CMS),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_HB_SENTTOCMS, HandbookBatchStatistics.HB_SENT_TO_CMS),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_BENEFITINSERT_SENTTOCMS, HandbookBatchStatistics.BENEFITS_INSERT_SENT_TO_CMS),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_MAILEDBYCMS, HandbookBatchStatistics.MAILED_BY_CMS),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_ONLINE, HandbookBatchStatistics.ONLINE),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_VBR_HB, HandbookBatchStatistics.HB_IN_VBR),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_VBR_BAG, HandbookBatchStatistics.BAG_IN_VBR),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_ERRCMS,  HandbookBatchStatistics.ERRORED_BY_CMS),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_CODE1REJ,  HandbookBatchStatistics.CODE_1_REJECT),
		new HandbookStatsColumnInfo(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_RETUSPS, HandbookBatchStatistics.RETURNED_BY_USPS)
	};
	
	public HandbookBatchFileProcessStatistics getHandbookBatchFileProcessStatistics(Date selectedDate, Date minJobStartDate, Date maxJobEndDate) throws DAOException {
		
		HandbookBatchFileProcessStatistics fileProcessStats = new HandbookBatchFileProcessStatistics();
        
		// First fetch the basic batch request data based on selected date
		getBatchFileProcessInfo(fileProcessStats, selectedDate, minJobStartDate, maxJobEndDate);
		
		// only get statistis counts based on the earlist job start date and the latest job end date if more than one handbook file process batches run on the same day
		getBatchFileProcessStatistics(fileProcessStats, minJobStartDate, maxJobEndDate);
		
		
        return fileProcessStats; 
    } 
	
	
	
	private void getBatchFileProcessInfo(HandbookBatchFileProcessStatistics fileProcessStats, Date selectedDate, Date minJobStartDate, Date maxJobEndDate) 
		throws DAOException {
		try {   
			
			String[] jobDateParamFields = {"minJobStartDate", "maxJobEndDate"}; 
			Object[] jobDateParamValues = {minJobStartDate, maxJobEndDate} ;		
			
			List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_BATCHNUM,
					jobDateParamFields, jobDateParamValues);
			
			Format formatter = new SimpleDateFormat("MM/dd/yyyy");
			String jobStartDateStr = formatter.format(selectedDate);
			fileProcessStats.setFileProcessBatchStartDateStr(jobStartDateStr); 
			
						
			for (Iterator i=results.iterator(); i.hasNext();) {
				Integer batchNum = (Integer) i.next();
				fileProcessStats.setNumFileBatchRequest(batchNum != null ? batchNum : new Integer(0) ); 
			}
			
			results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(QRY_HANDBOOK_BATCH_FILE_PROCESS_STAT_TOTALRECORDSNUM,
					jobDateParamFields, jobDateParamValues);
			
			for (Iterator i=results.iterator(); i.hasNext();) {
				Integer recordsNum = (Integer) i.next();
				fileProcessStats.setNumRecordsFromFileBatchRequest(recordsNum != null ? recordsNum : new Integer(0) ); 
			}
			
		} catch (DataAccessException e) {
            throw new DAOException("Failed to get handbook statistics by job start date and job end date", e);
        }
	}
	
	
	private void getBatchFileProcessStatistics(HandbookBatchFileProcessStatistics fileProcessStats, Date minJobStartDate, Date maxJobEndDate) {

		String[] jobDateParamFields = {"minJobStartDate", "maxJobEndDate"}; 
		Object[] jobDateParamValues = {minJobStartDate, maxJobEndDate} ;		
		// go through all the queries, execute each, set the result count into the proper count value
		for (HandbookStatsColumnInfo queryCountIndexSet : queryToCountIndexMapList) {
			
			List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(queryCountIndexSet.getQueryName(),
					jobDateParamFields, jobDateParamValues);

			for (Iterator i=results.iterator(); i.hasNext();) {
				Integer count = (Integer) i.next();
				((HandbookBatchStatistics) fileProcessStats).setCounts(queryCountIndexSet.getCountIndex(), count != null ? count : new Integer(0) );
			}
		}
	}
	
		
	
	
 public List<Date> getHandbookBatchFileProcessDateList() throws DAOException {
		
		List<Date> hbfDateList = new ArrayList<Date>();
        
		hbfDateList = super.getHibernateTemplate().findByNamedQuery(QRY_HANDBOOK_BATCH_FILE_PROCESS_DATE_LIST);
		
        return hbfDateList; 
    }  
 
 
 //get the earlist job start date if more than one handbook batch file process batch jobs run at the given day
 public Date getHandbookBatchFileProcessMinJobStartDate(Date selectedDate) throws DAOException {
		
		String[] selectedDateParamFields = {"selectedDate"};
		Object[] selectedDateParamValues = {selectedDate} ;	
		
     
		List results = super.getHibernateTemplate().findByNamedQueryAndNamedParam(QRY_HANDBOOK_BATCH_FILE_PROCESS_MINJOBSTARTDATE, selectedDateParamFields, selectedDateParamValues);
		
		return ((results == null) || results.isEmpty()) ? null : (Date) results.get(0);
     
 }  
 
 //get the latest job end date if more than one handbook batch file process batch jobs run at the given day
 public Date getHandbookBatchFileProcessMaxJobEndDate(Date selectedDate) throws DAOException {
		
		String[] selectedDateParamFields = {"selectedDate"};
		Object[] selectedDateParamValues = {selectedDate} ;		
  
		List results =super.getHibernateTemplate().findByNamedQueryAndNamedParam(QRY_HANDBOOK_BATCH_FILE_PROCESS_MAXJOBENDDATE, selectedDateParamFields, selectedDateParamValues);
		
		return ((results == null) || results.isEmpty()) ? null : (Date) results.get(0); 
}  
	

}