package testovernet;

import java.util.*;
import java.sql.*;

public class DatabaseAdmin {

    protected Student findStudent;

    protected LinkedList list = new LinkedList();

    public Student getfindStudent() {
        return findStudent;
    }

    public int getListNum() {
        return list.size();
    }

    public Object getFirst() {
        return list.getFirst();
    }

    public Object[] getQuestionList() {

        // Object[] temp1=list.toArray();
        // Question[] temp2=(Question[])temp1;
        return list.toArray();
    }

    public void queryQuestion(String questionType, int num) {

        // Question[] questionList = new Question[5];

        int flag = 1;
        String currentQuestion;
        try {
            if (questionType.equals("Judgement")) {
                currentQuestion = "QuestionJudgement";
            }
            else if (questionType.equals("Choice")) {
                currentQuestion = "QuestionChoice";
                flag = 2;
            }
            else {

                throw new SQLException();
            }
        }
        catch (SQLException ex) {
            System.out.println("Hava not this sort of question in DataBase");
        }

        //Create a URL specifying an ODBC datasource name
        String url = "jdbc:odbc:Project";

        try {
            DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

            // Connect to the database at that URL
            Connection conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();
            // int temp=num-1;
            //System.out.println(temp);
            String query = "Select * from " + questionType +
                    " where Serial between 1 and " + num;

            System.out.println(query);
            //Execute a SELECT statement
            ResultSet rset = stmt.executeQuery(query);

            //Deal with the result
            while (rset.next()) {
                // get the values from the current row; and get ArrayList
                int temp1 = rset.getInt(1);
                String temp2 = rset.getString(2);
                String temp3 = rset.getString(3);
                // System.out.println(temp1);
                System.out.println(temp1 + "  " + temp2);

                Question tempQuestion;
                if (flag == 1) {
                    tempQuestion = new QuestionJudgement(temp2, temp3);
                    list.add(tempQuestion);
                }
                if (flag == 2) {
                    tempQuestion = new QuestionChoice(temp2, temp3);
                    list.add(tempQuestion);
                }

                // int temp=0;
                // questionList[temp]=new QuestionJudgement(rset.getString(2),rset.getString(3));
                // temp++;
            }

            stmt.close();
            conn.close();
        }

        catch (SQLException ex) {
            System.out.println("\n *** SQLException ***\n");
            while (ex != null) {
                ex = ex.getNextException();
            }
        }
        catch (java.lang.Exception ex) {
            System.out.println("error:" + ex.getMessage() + "\n");
        }

    }

    public boolean queryStudent(int testNum, String name) {

        boolean flag = false;
        String url = "jdbc:odbc:Project";

        try {
            DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

            // Connect to the database at that URL
            Connection conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();

            String query = "Select * from Student where TestNo =" + testNum +
                    " and Name =\'" + name + "\'";
            System.out.println(query);

            //String query1 = "Select * from Student";

            //Execute a SELECT statement
            ResultSet rset = stmt.executeQuery(query);

            //Deal with the result
            while (rset.next()) {
                // get the values from the current row; and get ArrayList
                int temp1 = rset.getInt(1);
                int temp3 = rset.getInt(3);
                String temp2 = rset.getString(2);
                String temp4 = rset.getString(4);

                System.out.println(temp1);
                System.out.println(temp2);
                System.out.println(temp3);
                System.out.println(temp4);

                findStudent = new Student(temp1, temp2, temp3, temp4);

                flag = true;

            }

            stmt.close();
            conn.close();
        }

        catch (SQLException ex) {
            System.out.println("\n *** SQLException ***\n");
            while (ex != null) {
                ex = ex.getNextException();
            }
        }
        catch (java.lang.Exception ex) {
            System.out.println("error:" + ex.getMessage() + "\n");
        }

        return flag;
    }

    public void setStudentScore(int testNum, int studentScore) {

        String url = "jdbc:odbc:Project";

        try {
            DriverManager.registerDriver(new sun.jdbc.odbc.JdbcOdbcDriver());

            // Connect to the database at that URL
            Connection conn = DriverManager.getConnection(url);
            Statement stmt = conn.createStatement();

            String query = "update Student set Score =" +
                    studentScore + " where TestNo =" + testNum;

            System.out.println(query);

            //String query1 = "Select * from Student";

            //Execute a SELECT statement
            // ResultSet rset =
            stmt.executeQuery(query);

            //Deal with the result
            /*   while (rset.next()) {
                   // get the values from the current row; and get ArrayList
                   int temp1 = rset.getInt(1);
                   int temp3 = rset.getInt(3);
                   String temp2 = rset.getString(2);
                   String temp4 = rset.getString(4);
                   System.out.println(temp1);
                   System.out.println(temp2);
                   System.out.println(temp3);
                   System.out.println(temp4);
                   findStudent = new Student(temp1, temp2, temp3, temp4);
               } */

            stmt.close();
            conn.close();
        }

        catch (SQLException ex) {
            // System.out.println("\n *** SQLException ***\n");
            while (ex != null) {
                ex = ex.getNextException();
            }
        }
        catch (java.lang.Exception ex) {
            System.out.println("error:" + ex.getMessage() + "\n");
        }

    }
}