import java.util.Collection;
import java.util.Iterator;
import java.util.HashMap;
import java.util.StringTokenizer;
import java.sql.*;

/**
 * Static methods used in the FrontEnd to query the SAS database system.
 */
public class RetrieverUtils
{
	/**
	 * When a student's Banner transcript is parsed into a list of courses, it obviously
	 * does not contain the course id's used to represent the courses in the SAS database.
	 * Using the dowling course codes as keys, this method figures out the corresponding
	 * course id's in the SAS database.
	 * @param courseList a Collection of Course objects to translate.
	 * @exception SQLException if an error occurs while connecting to the SAS database.
	 */
	public static void translateCourseList(Collection courseList) throws SQLException
	{
		Iterator iter = courseList.iterator();

		while(iter.hasNext())
		{
			Course course = (Course) iter.next();

			ResultSet rs = Retriever.executeSQL("select CourseID from COURSES where CourseCode like'"
				+ course.getDiscipline() + "%" + course.getCourseNumber() + "%" 
				+ course.getCourseLetter() + "%'");

			if(rs == null)
			{
				throw new SQLException(
					"Unable to connect to SAS database.");
			}

			if(rs.next())
				course.setCourseId(rs.getString("CourseID"));
			else course.setCourseId("-1");			
		}		
	}

	/**
	 * Checks to see if a particular minor is allowed with a particular major.
	 * @param major A major id from the SAS database.
	 * @param minor A minor id from the SAS database.
	 * @exception SQLException if an error occurs while connecting to the SAS database.
	 */
	public static boolean isMajorOkWithMinor(String major, String minor) throws SQLException
	{
		ResultSet rs = Retriever.executeSQL("select NotAvailableList from MINORS where MinorID="
			+ minor);

		if(rs == null)
		{
			throw new SQLException(
				"Unable to connect to SAS database.");
		}

		if(rs.next())
		{
			StringTokenizer k = new StringTokenizer(
				rs.getString("NotAvailableList"), "*");

			while(k.hasMoreTokens())
			{
				if(major.equals(k.nextToken().trim()))
					return false;
			}
		}
		else
		{
			throw new SQLException(
				"Minor " + minor + " was unavailable.");
		}

		return true;
	}

	/**
	 * Builds a HashMap of major names mapping to minor ids.  This method includes only
	 * majors with a DoubleFlagID of 1 or 2.
	 * @exception SQLException if an error occurs while connecting to the SAS database.
	 */
	public static HashMap getMajors() throws SQLException
	{
		HashMap map = new HashMap();
		ResultSet rs = Retriever.executeSQL("select MajorID, MajorName, DoubleFlagID from MAJORS");

		if(rs == null)
		{
			throw new SQLException(
				"Unable to connect to SAS database.");
		}

		while(rs.next())
		{
			String doubleFlagId = rs.getString("DoubleFlagID");

			if(doubleFlagId.equals("1") || doubleFlagId.equals("2"))
				map.put(rs.getString("MajorName"), rs.getString("MajorID"));
		}

		return map;		
	}

	/**
	 * Builds a HashMap of minor names mapping to minor ids.  This method
	 * includes all minors.
	 * @exception SQLException if an error occurs while connecting to the SAS database.
	 */
	public static HashMap getMinors() throws SQLException
	{
		HashMap map = new HashMap();
		ResultSet rs = Retriever.executeSQL("select MinorName, MinorID from MINORS");

		if(rs == null)
		{
			throw new SQLException(
				"Unable to connect to SAS database.");
		}

		while(rs.next())
			map.put(rs.getString("MinorName"), rs.getString("MinorID"));

		return map;
	}
}