/*
 * This class is just a wrapper around an ArrayList to avoid the useBean issues when passing to the JSP.  
 * 
 */

package us.deans.parrot.dataprovider;

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 javax.sql.DataSource;

import java.text.SimpleDateFormat;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;						// might be able to take out...
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class ExperienceDataSet {
	
	public Iterator<ContractRecord> iter;
	
	private Log log = LogFactory.getLog(ExperienceDataSet.class);
	
	private ArrayList<ContractRecord> contracts = new ArrayList<ContractRecord>();
	private Map<String, ContractRecord> contractMap = new HashMap<String, ContractRecord>();
	
	private DataSource ds_wp1;
	private Connection dc1 = null;
	private Statement st1;
	private ResultSet rs1; 
	
	private DataSource ds_res;
	private Connection dc2 = null;
	private PreparedStatement st2;
	private ResultSet rs2; 
	
	private Connection dc3 = null;
	private PreparedStatement st3;
	private ResultSet rs3;
	
	public String SQL1 = ".";
	public String SQL2 = ".";
	public String SQL3 = ".";
	
	public ExperienceDataSet() {
		
    	// get data source through the initial context.
    	try {
    		Context initContext = new InitialContext();
    		Context envContext = (Context)initContext.lookup("java:comp/env");
    		ds_wp1 = (DataSource)envContext.lookup("jdbc/CMS1-WP1");
    		ds_res = (DataSource)envContext.lookup("jdbc/CMS1-RES");
    	} 
    	catch (NamingException e) {
    		e.printStackTrace();
    	}
		
	}
	
	// Loading Data...
    public void loadData(String SQL1, String SQL2, String SQL3){
    	
    	this.SQL1 = SQL1; 
    	this.SQL2 = SQL2; 
    	this.SQL3 = SQL3;
    	
    	log.debug(">> dataprovider >> Loading data....");
    	log.debug(">> dataprovider >> SQL1 = " + this.SQL1);
		
		// professional experience section 
		try {

			log.debug(">> dataprovider >> Getting connections...");
			dc1 = ds_res.getConnection();
			dc2 = ds_wp1.getConnection();
			dc3 = ds_wp1.getConnection(); 

			st1 = dc1.createStatement();
			
			log.debug(">> Getting list of contracts...");
			rs1 = st1.executeQuery(SQL1);	

			contracts = new ArrayList<ContractRecord>();
		
			// per contract... (contract record)
			while (rs1.next()) {
				
				ContractRecord contract = new ContractRecord();
				
				String client = rs1.getString("c2/client");
				
				if (client.equalsIgnoreCase("n/a")) {
					contract.setEmployerName(rs1.getString("c1/contractor"));
					contract.setEmployerCity(rs1.getString("c1/city"));
					contract.setEmployerState(rs1.getString("c1/state"));
					contract.setClientName("");
					contract.setClientCity("");
					contract.setClientState("");
				}
				else {
					contract.setEmployerName(client);
					contract.setEmployerCity(rs1.getString("c2/city"));
					contract.setEmployerState(rs1.getString("c2/state"));
					contract.setClientName(rs1.getString("c1/contractor"));
					contract.setClientCity(rs1.getString("c1/city"));
					contract.setClientState(rs1.getString("c1/state"));
				}
				
				contract.setContractTerm(processTerm(rs1.getDate("start_date"), rs1.getDate("end_date")));

				contract.setJobTitle(rs1.getString("job_title"));
				contract.setContractId(rs1.getString("wp_tag"));
				
				ArrayList<ExperienceRecord> details = new ArrayList<ExperienceRecord>();
				
				log.debug("data for '" + contract.getContractId() + ";");
				
				st2 = dc2.prepareStatement(SQL2);
				st2.setString(1, contract.getContractId());
				rs2 = st2.executeQuery();
				
				// log.debug(">> " + rs2.getFetchSize() + " experience records.");
				
				// per bullet...  (experience record) 
				while (rs2.next()) {
				
					ExperienceRecord exp = new ExperienceRecord();
					exp.setBullet(rs2.getString("post_content"));

					try{
						st3 = dc3.prepareStatement(SQL3);
						st3.setString(1, rs2.getString("post_name"));
						rs3 = st3.executeQuery();
						log.debug(">>> st3 executed...");
						
						// per skill 	
						while (rs3.next()) {
							String skill = rs3.getString("name");
							log.debug(">>> --- " + skill);
							exp.addSkill(skill);
						}
						
						log.debug(">> "  + exp.getSkillSet().size() + " skills");
						
					}
					catch(Exception ex){
						log.debug("error...attempting to load experience details... " + ex.getMessage());
					}
					
					details.add(exp);						
					
				}

				contract.setDetails(details);
				rs2.close(); st2.close();	

				contracts.add(contract);
				contractMap.put(contract.getContractId(), contract);				// added to support BIRT
				
			}

			dc2.close();							
			rs1.close(); st1.close(); dc1.close();									
			
		} 
		catch (SQLException e) {
			e.printStackTrace();	
		}
		
    }

    public String processTerm(Date start_d, Date end_d ){
    	
    	SimpleDateFormat formatYear = new SimpleDateFormat("yyyy");
    	SimpleDateFormat formatMonth = new SimpleDateFormat("MMM");
    	
    	String start_year = formatYear.format(start_d);
    	String start_month = formatMonth.format(start_d);
    	String end_year = formatYear.format(end_d);
    	String end_month = formatMonth.format(end_d);
    	
    	String term = start_month + " " + start_year + " to " + end_month + " " + end_year;
    	return term;
 
    }
	
	
    // Basic Stuff...

	public int count(){
		return this.contracts.size();
	}

    public void setDataSet(ArrayList<ContractRecord> dset) {
		this.contracts = dset;
	}
	
	public ArrayList<ContractRecord> getDataSet() {
		return this.contracts;
	}	
	
	public List<ContractRecord> getContracts() {
		return this.contracts;
	}
	
	public ContractRecord getContract(String contactID) {
		ContractRecord rec = contractMap.get(contactID);
		return rec;
	}

	
	
	
	// BIRT support...
	
	public void open(Object obj, Map<String, Object> map) {
		
		// Map<String, Object> test = (Map<String, Object>) map;
		// HttpServletRequest birt_request = (HttpServletRequest) test.get(EngineConstants.APPCONTEXT_BIRT_VIEWER_HTTPSERVET_REQUEST);
		
		// String contractID = birt_request.getParameter("contractID");
		// iter = getContracts().iterator();
		
	}
	
	
	public Object next() {

		if (iter == null) iter = getContracts().iterator();
		if (iter.hasNext()) return iter.next();
		return null;
		
	}
	
	
	public void close() { 	}
	
	
}