/*
 * Code by George Kichukov for SAS 10/27/2002
 * 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
 * use like this:
 *   ResultSet rs=Retriever.executeSQL("select * from SCHOOLS");
 */

package utilities;

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

public class Retriever 
{

        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;
       }

        //this method is used by the Admin module to get all authorised users
        public static ResultSet getUsers()
	{
		return executeSQL("select * from USERS");
	}

	 //this method is used to get all MajorIDs and MajorNames
        public static ResultSet getMajors()
        {
                return executeSQL("select MajorID, MajorName from MAJORS");
        }
        
         //this method is used get all MinorIDs and MinorNames
        public static ResultSet getMinors()
        {
                return executeSQL("select MinorID, MinorName from MINORS");
        }


	 //this method is used get any two fields from any table and order it alphabetically by the second field
        public static ResultSet getList(String field1, String field2, String table)
        {
                return executeSQL("select " + field1 + ", " + field2 + " from " + table + " order by " + field2);
        }
        
        //this method returns a list of all course codes and names
        public static ResultSet getCourseList()
        {
                return executeSQL("select CourseID, CourseCode, CourseName from COURSES order by CourseCode");
        }
        
       //this method returns the number of rows in the table passed as parameter
        public static ResultSet getRowCount(String table)
        {
                return executeSQL("SELECT COUNT(*) FROM " + table);
        }
	
	  //this method is used get all fields from the table passed as argument
        public static ResultSet getTable(String table_name)
        {
                return executeSQL("select * from " + table_name);
        }

	 //this method is used get all fields from the table passed as argument
        //for the specific record
        public static ResultSet getTableID(String table_name, String id)
        {
		//MAJOR_REQ to MajorID and MINOR_REQ to MinorID
		String fieldID="M" + table_name.substring(1,table_name.length()-4) +"ID";

                return executeSQL("select * from " + table_name + " where " + fieldID + "=" + id);
        }
        

	//this method is used get the name of a major or a minor from the table passed as argument
        public static ResultSet getRecordName(String id, String table)
        {
        	//get exact field names from table names e.g. from SCHOOLS to SchoolID or SchoolName
                String fieldID=table.substring(0,1) + (table.substring(1,table.length()-1)).toLowerCase() + "ID";
                String fieldName=table.substring(0,1) + (table.substring(1,table.length()-1)).toLowerCase() + "Name";
                
                return executeSQL("select " + fieldName + " from " + table + " where " + fieldID + "=" + id);
        }
	
	//this method is used to return a String of CourseCodes for display from the 
        //array of passed IDs
        public static String getCourseCodesFromIDs(String [] ids)
        {
        	String result="";
		String sql="select CourseCode from COURSES where CourseID= " + ids[0];
		
		for(int i=1; i<ids.length; i++)
		{
			sql = sql + " or CourseID=" + ids[i];
		}
                
               try
               {
                      
                        ResultSet rs = executeSQL (sql );
                        
                        while(rs.next())
                        {
                        	result= result + rs.getString("CourseCode") + ", ";
                        }
			
               }

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

	       //remove the last ,
	       result= result.substring(0, result.length()-2);
	       
               return result;
        }
	
	  //this method is used to return course name, code and id for all course
         //ordered by CourseCode
        public static ResultSet getCourses()
        {
                return executeSQL("select CourseID, CourseCode, CourseName from COURSES order by CourseCode");
        }

	 //this method is used to return the specific field from the table 
        public static ResultSet getTableFieldID(String field, String table, String id)
        {
                return executeSQL("select " + field + " from " + table + " where ID=" + id);
        }
	
	//Chris's method
	public static String getCourseCodeFromCourseId(String id) throws SQLException
        {
                ResultSet rs =
                        executeSQL("select CourseCode from COURSES where CourseID=" + id);

                if(rs.next())
                        return rs.getString("CourseCode");
                else return null;
        }

        public static String getDisciplineCodeFromDisciplineId(String id) throws SQLException
        {
                ResultSet rs =
                        executeSQL("select DisciplineCode from DISCIPLINES where DisciplineID ="
                        + id);

                if(rs.next())
                        return rs.getString("DisciplineCode");
                else return null;
        }

	//this method is used to return a String of MajorCodes for display from the 
        //array of passed IDs
        public static String getMajorCodesFromIDs(String [] ids)
        {
        	String result="";
		String sql="select MajorCode from MAJORS where MajorID= " + ids[0];
		
		for(int i=1; i<ids.length; i++)
		{
			sql = sql + " or MajorID=" + ids[i];
		}
                
               try
               {
                      
                        ResultSet rs = executeSQL (sql );
                        
                        while(rs.next())
                        {
                        	result= result + rs.getString("MajorCode") + ", ";
                        }
			
               }

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

	       //remove the last ,
	       result= result.substring(0, result.length()-2);
	       
               return result;
        }


        //Chris's method used in RequirementParser
        public static String getCourseIdFromCourseCode(String code) throws SQLException
        {
                ResultSet rs =
                        executeSQL("select CourseID from COURSES where CourseCode=\""
                        + code + "\"");

                if(rs != null && rs.next()) return rs.getString("CourseID");
                else throw new SQLException("Cannot connect to SAS Database.");
        }

        public static String getDisciplineIdFromDisciplineCode(String code) throws SQLException
        {
                ResultSet rs =
                        executeSQL("select DisciplineID from DISCIPLINES where DisciplineCode=\""
                        + code + "\"");

                if(rs != null && rs.next()) return rs.getString("DisciplineID");
                else throw new SQLException("Cannot connect to SAS Database.");
        }

        public static String getMajorIdFromMajorCode(String code) throws SQLException
        {
                ResultSet rs =
                        executeSQL("select MajorID from MAJORS where MajorCode=\""
                        + code + "\"");

                if(rs != null && rs.next()) return rs.getString("MajorID");
                else throw new SQLException("Cannot connect to SAS Database.");
        }

        public static String getMajorCodeFromMajorId(String id) throws SQLException
        {
                ResultSet rs =
                        executeSQL("select MajorCode from MAJORS where MajorID=\""
                        + id + "\"");

                if(rs != null && rs.next()) return rs.getString("MajorCode");
                else throw new SQLException("Cannot connect to SAS Database.");

        }

	//end Chris's methods
}