package org.rd.qm.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import oracle.jdbc.pool.OracleDataSource;

import org.oasisopen.sca.annotation.Reference;
import org.oasisopen.sca.annotation.Service;
import org.rd.qm.QueryExecutor;
import org.rd.qm.QueryResults;

import com.bca.pearl.domain.Person;
import com.bca.pearl.domain.PhysicalLocation;
import com.bca.pearl.domain.impl.PatientImpl;
/*                **************************************************                */
/*                **************************************************                */
@Service(QueryExecutor.class)
public class QueryExecutorImpl implements QueryExecutor {
    @Reference
    protected Person patient;
    @Reference
    protected PhysicalLocation address;
    @Reference
    protected QueryResults queryResults;
    /*                **************************************************                */
	public QueryResults getPatients(String query_) {
		ArrayList<Person> alPatients = new ArrayList<Person>();
		try {
			Connection oraConn = getOracleConnection();
		    Statement stmt = oraConn.createStatement();
		    ResultSet rset = stmt.executeQuery(query_);
		    while (rset.next()) {
		    	Person p = new PatientImpl();
		    	p.setFirstName(rset.getString("firstN")); 
		    	p.setLastName(rset.getString("lastN"));
		    	p.setMI(rset.getString("mi"));
		    	//p.setDOB(rset.getString("dob"));
		    	p.setRace(rset.getString("race"));
		    	p.setEthnicity(rset.getString("ethnicity"));
		    	alPatients.add(p);
		    }
		    rset.close();
		    stmt.close();
		    oraConn.close();
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		}
		System.out.println("**************************************************");
		System.out.println(query_);
		System.out.println("Patients Count: " + alPatients.size());
		queryResults.setResults(alPatients);
		return queryResults;
	}
	/*                **************************************************                */
	public ArrayList<Integer> getCounts() {
		ArrayList<Integer> alCounts = new ArrayList<Integer>();
		try {
			Connection oraConn = getOracleConnection();
			
		    Statement stmt1 = oraConn.createStatement();
		    ResultSet rset1 = stmt1.executeQuery("select count(1) as count from  patient");
		    while (rset1.next()) {alCounts.add(rset1.getInt("count"));}
		    rset1.close();
		    stmt1.close();
		    
		    Statement stmt2 = oraConn.createStatement();
		    ResultSet rset2 = stmt2.executeQuery("select count(1) as count from  mv_patient_fac_admission");
		    while (rset2.next()) {alCounts.add(rset2.getInt("count"));}
		    rset2.close();
		    stmt2.close();
		    
		    Statement stmt3 = oraConn.createStatement();
		    ResultSet rset3 = stmt3.executeQuery("select count(1) as count from  mv_encounter_proc");
		    while (rset3.next()) {alCounts.add(rset3.getInt("count"));}
		    rset3.close();
		    stmt3.close();
		    
		    oraConn.close();
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		}
		
		return alCounts;
	}
	/*                **************************************************                */
	public Connection getOracleConnection() {
		Connection oraConn = null;
		try {
			OracleDataSource ods = new OracleDataSource();
			ods.setUser("xxxxx");	ods.setPassword("xxxxx");
			ods.setURL("jdbc:oracle:thin:@55.55.55.55:5555/xxxx");
			//<host>:<port>/<service_name>
			oraConn = ods.getConnection();
			oraConn.setAutoCommit(false);
		} catch (SQLException sqlEx) {
			sqlEx.printStackTrace();
		}
		return oraConn;
	}
	/*                **************************************************                */
}
/*                **************************************************                */
/*                **************************************************                */