package com.gton.bean.db;

import java.sql.*;
import com.gton.bean.*;
import com.gton.util.*;
import java.util.*;

public class DataBean {
    private Connection conn = null;

    private ResultSet res = null;

    private java.sql.PreparedStatement prepar = null;

    private java.sql.CallableStatement proc = null;

    public int pagecount = 0;
    public DataBean() { //構造函數
        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
            conn = DriverManager
                   .getConnection(
                           "jdbc:microsoft:sqlserver://127.0.0.1:1433;databasename=Kaoshi",
                           "sa", "123456");
            System.out.println("連接成功");
        } catch (SQLException ex) {
            System.out.println(ex.getMessage() + "路徑錯誤");
        } catch (ClassNotFoundException ex) {
            System.out.println(ex.getMessage() + "驅動錯誤");
        }

    }

    public void Close() { //關閉方法
        try {
            if (res != null) {
                res.close();
            }
            if (prepar != null) {
                prepar.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        System.out.println("關閉成功");
    }

/////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin登陸方法/////////////////////////////////////////////////
    public boolean checkAdminLogin(AdminBean ab) { // 驗證登陸

        boolean result = false;
        if (!MyString.checkParameter(ab.getAdmin_Name()
                                     + ab.getAdmin_Pwd())) {
            return result;
        }
        try {
            String sql =
                    "select count(*) from Admin where Admin_Name=? and Admin_Pwd=?";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, ab.getAdmin_Name());
            prepar.setString(2, ab.getAdmin_Pwd());
            res = prepar.executeQuery();
            if (res.next()) {
                if (res.getInt(1) > 0) {
                    result = true;
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }

    public AdminBean getAdminBean(AdminBean ab) { // 獲取AdminBean持久化
        AdminBean admin = null;
        try {
            String sql =
                    "select * from Admin where Admin_Name=? and Admin_Pwd=?";
            prepar = conn.prepareStatement(sql);
            prepar.setString(1, ab.getAdmin_Name());
            prepar.setString(2, ab.getAdmin_Pwd());
            res = prepar.executeQuery();
            if (res.next()) {
                admin = new AdminBean();
                admin.setAdmin_ID(res.getInt("Admin_ID"));
                admin.setAdmin_Name(res.getString("Admin_Name"));
                admin.setAdmin_Pwd(res.getString("Admin_Pwd"));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return admin;
    }

/////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin錄入考題方法/////////////////////////////////////////////////
    public boolean insertTest(TestBean tb) {
        try {
            prepar = conn.prepareStatement(
                    "insert into Test (Test_Theme,Test_Result,Test_Score,Test_Type,Test_A,Test_B,Test_C,Test_D) values(?,?,?,?,?,?,?,?)");
            prepar.setString(1, tb.getTest_Theme());
            prepar.setString(2, tb.getTest_Result());
            prepar.setInt(3, tb.getTest_Score());
            prepar.setString(4, tb.getTest_Type());
            prepar.setString(5, tb.getTest_A());
            prepar.setString(6, tb.getTest_B());
            prepar.setString(7, tb.getTest_C());
            prepar.setString(8, tb.getTest_D());
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin生成考卷方法/////////////////////////////////////////////////
    public ArrayList getTestList(int count, String type) {
        ArrayList list = new ArrayList();
        String sql =
                "select top " + count + " * from Test where Test_Type='" + type +
                "' order by NEWID()";

        try {
            prepar = conn.prepareStatement(sql);
            res = prepar.executeQuery();
            while (res.next()) {
                TestBean tb = new TestBean();
                tb.setTest_ID(res.getInt(1));
                tb.setTest_Theme(res.getString(2));
                tb.setTest_Result(res.getString(3));
                tb.setTest_Score(res.getInt(4));
                tb.setTest_Type(res.getString(5));
                tb.setTest_A(res.getString(6));
                tb.setTest_B(res.getString(7));
                tb.setTest_C(res.getString(8));
                tb.setTest_D(res.getString(9));
                list.add(tb);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return list;

    }

    public boolean InsertTests(TestsBean tsb) {
        try {
            prepar = conn.prepareStatement(
                    "insert into Tests (Tests_Time,Tests_Path) values(?,?)");
            prepar.setString(1, tsb.getTests_Time());
            prepar.setString(2, tsb.getTests_Path());
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;

    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin列出考卷方法/////////////////////////////////////////////////
    public ArrayList getTestsList(int count, int page) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call proc_tests_page (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                TestsBean tsb = new TestsBean();
                tsb.setTests_ID(res.getInt("Tests_ID"));
                tsb.setTests_Path(res.getString("Tests_Path"));
                tsb.setTests_Time(res.getString("Tests_Time"));
                list.add(tsb);
            }
            pagecount = proc.getInt(3);
        } catch (SQLException ex) {
            System.out.println("服務器異常發生在 getTestsList()");
            ex.printStackTrace();
        }
        return list;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin刪除考卷方法/////////////////////////////////////////////////
    public boolean deleteTests(int tests_ID) {
        try {
            prepar = conn.prepareStatement(
                    "delete from Tests where Tests_ID=?");
            prepar.setInt(1, tests_ID);
            int flag = prepar.executeUpdate();
            if (flag == 0) {
                return false;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    /////////////////////////////////////////////////////////////////////////////////////////
/////////////////////////////////Admin列出試題方法/////////////////////////////////////////////////
    public ArrayList getTestList(int count, int page) {
        ArrayList list = new ArrayList();
        try {
            proc = conn.prepareCall("{call proc_test_page (?,?,?)}");
            proc.setInt(1, page);
            proc.setInt(2, count);
            proc.registerOutParameter(3, Types.INTEGER);
            res = proc.executeQuery();
            while (res.next()) {
                TestBean tb = new TestBean();
                tb.setTest_ID(res.getInt(1));
                tb.setTest_Theme(res.getString(2));
                tb.setTest_Result(res.getString(3));
                tb.setTest_Score(res.getInt(4));