import java.io.*;
import java.sql.*;

/**
  * This class is to be used from all other classes to execute SQL statements on the database.
  * This class uses the MyConnection class to establish the database connection
  */

public class Retriever 
{
      /**
	* Executes sql statements.
	*/
        public static ResultSet executeSQL (String sql)
        {
               Connection conn=null;
               Statement s=null;
               ResultSet rs=null;
                              
               try
               {
                        conn =MyConnection.open();
                        s = conn.createStatement();
                        rs = s.executeQuery (sql );
		
               }

               catch(SQLException e){System.err.println("SQLException " + e.getMessage());}
               
               return rs;
       }

      /**
	* Returns requirements for the major.
	*/

	public static ResultSet p_getMajor(String major_id)
	{
		ResultSet res = executeSQL("select * from MAJOR_REQ where MajorID=" + major_id);
		return res;
	}
      /**
	* Returns requirements for the minor.
	*/

	public static ResultSet p_getMinor(String minor_id)
	{
		ResultSet res = executeSQL("select * from MINOR_REQ where MinorID=" + minor_id);
		return res;
	}
      /**
	* Returns Core requirements.
	*/

	public static ResultSet p_getCore()
	{
		ResultSet res = executeSQL("select * from CORE_REQ");
		return res;
	}

      /**
	* Returns Core requirements for transfer students.
	*/

	public static int p_getTransferCore(String tran_credits)
	{
		if(tran_credits.length()==2)
		{
			tran_credits=tran_credits.substring(0,tran_credits.length()-1);
			tran_credits=tran_credits + "0";
		}
		else if(tran_credits.length()==3)
		{
			tran_credits=tran_credits.substring(0,tran_credits.length()-2);
			tran_credits=tran_credits + "00";
		}

		int res=0;

		ResultSet core_req = executeSQL("select CoreRequired from TRANSFERS where TransferName=" +tran_credits);
		try{
			core_req.next();
			res = core_req.getInt(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;

	}
      /**
	* Returns College-wide requirements.
	*/

	public static ResultSet p_getCollegeWide()
	{
		ResultSet res = executeSQL("select * from COLLEGEWIDE_REQ");
		return res;
	}

      /**
	* Returns General requirements.
	*/

	public static ResultSet p_getGeneral()
	{
		ResultSet res = executeSQL("select * from GENERALS");
		return res;
	}

      /**
	* Returns the name of a specific course.
	*/

	public static String p_getName(String course)
	{
		ResultSet name = executeSQL("select CourseName from COURSES where CourseID=" + course);
		String res="";
		try{
			name.next();
			res = name.getString(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}
      /**
	* Returns the title of a specific major.
	*/

	public static String p_getMajorName(String major)
	{
		ResultSet name = executeSQL("select MajorName from MAJORS where MajorID=" + major);
		String res="";
		try{
			name.next();
			res = name.getString(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}
      /**
	* Returns the title of a specific minor.
	*/

	public static String p_getMinorName(String minor)
	{
		ResultSet name = executeSQL("select MinorName from MINORS where MinorID=" + minor);
		String res="";
		try{
			name.next();
			res = name.getString(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}
      /**
	* Returns the course code for example CSC175.
	*/

	public static String p_getCourseCode(String course)
	{
		ResultSet name = executeSQL("select CourseCode from COURSES where CourseID=" + course);
		String res="";
		try{
			name.next();
			res = name.getString(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}

      /**
	* Returns Expression with prerequisite requirements.
	*/

	public static String p_getPrereq(String course)
	{
		ResultSet prereq = executeSQL("select PreReqList from COURSES where CourseID=" + course);
		String res="";
		try{
			prereq.next();
			res = prereq.getString(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}

      /**
	* Returns Expression with corequisite requirements.
	*/

	public static String p_getCoreq(String course)
	{
		ResultSet prereq = executeSQL("select CoReqList from COURSES where CourseID=" + course);
		String res="";
		try{
			prereq.next();
			res = prereq.getString(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}

      /**
	* Returns credits for a specific course.
	*/

	public static int p_getCredits(String course)
	{
		ResultSet credits = executeSQL("select Credits from COURSES where CourseID=" + course);
		int res=0;
		try{
			credits.next();
			res = credits.getInt(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}

      /**
	* Returns all courses considered liberal arts.
	*/

	public static ResultSet p_get_liberal_arts()
	{
		String str = "select CourseID from COURSES where CourseLetter='A' or CourseLetter='C'";	

		ResultSet liberal= executeSQL(str);
		return liberal;
	}

      /**
	* Returns all courses from a specific discipline. Can discriminate between upper and lower level.
	*/

	public static ResultSet p_get_all_with_level(String level, String discipline)
	{
		ResultSet res;

		if(level.equals("0"))
			res=executeSQL("select CourseID from COURSES where DisciplineID=" + discipline);	
		else
			res=executeSQL("select CourseID from COURSES where LevelID=" + level +" and DisciplineID=" + discipline);	
		return res;
	}

	public static int p_getLiberalArt_req_credits(String major)
	{
		ResultSet credits = executeSQL("select DEGREES.LiberalArts from DEGREES,MAJORS where MAJORS.MajorID=" + major + " and MAJORS.DegreeID=DEGREES.DegreeID");
		int res=0;
		try{
			credits.next();
			res = credits.getInt(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}

      /**
	* Returns requirements for total credits completed before graduation.
	*/

	public static int p_getTotalCredits(String major)
	{
		ResultSet credits = executeSQL("select DEGREES.TotalCredits from DEGREES,MAJORS where MAJORS.MajorID=" + major + " and MAJORS.DegreeID=DEGREES.DegreeID");
		int res=0;
		try{
			credits.next();
			res = credits.getInt(1);
		}catch(SQLException e){System.err.println("SQL Exception " + e.getMessage());}

		return res;
	}
}