package appstu.util;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import appstu.model.*;
import javax.swing.JOptionPane;
import java.sql.*;
import appstu.view.JF_view_error;
public class JdbcAdapter {
    private Connection con = null;
    private Statement stmt = null;
    private PreparedStatement pstmt = null;
    private String infoStr = null;

    public boolean BuildeDeleteTempView(String sqlState){
            boolean flag = false;
            System.out.println("執行的語句為:" + sqlState);
            try{
                con = CommonaJdbc.conection;
                pstmt = con.prepareStatement(sqlState);
                pstmt.execute();
                flag = true;

            }catch(java.sql.SQLException sql){
                flag = false;
                sql.printStackTrace();
            }
            return flag;
    }
//真正的執行對數據庫的各種操作
    private boolean AdapterObject(String sqlState){
        boolean flag = false;
        System.out.println("執行的語句為:" + sqlState);
        con = CommonaJdbc.conection;

        try{
            con = CommonaJdbc.conection;
            pstmt = con.prepareStatement(sqlState);
            pstmt.execute();
            flag = true;
            JOptionPane.showMessageDialog(null,infoStr + "數據成功!!!","系統提示",JOptionPane.INFORMATION_MESSAGE);
        }catch(java.sql.SQLException sql){
            appstu.view.JF_view_error error = new JF_view_error("執行的SQL語句為:\n" + sqlState + "\n錯誤信息為：" + sql.getMessage());
            flag = false;
            sql.printStackTrace();
        }
        return flag;
    }
    //執行刪除數據表中的數據
    public boolean DeleteObject(String deleteSql){
        infoStr = "刪除";
        return AdapterObject(deleteSql);
    }

    //驗證數據表中是否存在數據
    private boolean validateID(String id,String tname,String idvalue){
        String sqlStr = null;
        sqlStr = "select count(*) from " + tname + " where "
                 +  id + " = '" + idvalue + "'";
        System.out.println(sqlStr);
        try{
            con = CommonaJdbc.conection;
            pstmt = con.prepareStatement(sqlStr);
            java.sql.ResultSet rs = null;
            rs = pstmt.executeQuery();
            if (rs.next()) {
                if (rs.getInt(1) > 0)   return true;
            }
        }catch(java.sql.SQLException sql){
            new appstu.view.JF_view_error("執行的SQL語句為:\n" + sqlStr + "\n錯誤信息為：" +sql.getMessage());
           sql.printStackTrace();
           return false;
        }

        return false;
    }

    public boolean InsertOrUpdateObject(Obj_gradeinfo objgradeinfo){
        String sqlStatement = null;
        if (validateID("gradeID","tb_gradeinfo",objgradeinfo.getGradeID().trim())){
            sqlStatement = "Update tb_gradeinfo set gradeID = '"
                       + objgradeinfo.getGradeID() + "',gradeName ='" + objgradeinfo.getGradeName()
                       + "' where gradeID = '" + objgradeinfo.getGradeID().trim() + "'";
            infoStr = "更新年級";
        }else{
            sqlStatement = "Insert tb_gradeinfo(gradeID,gradeName) values ('"
                        + objgradeinfo.getGradeID() + "','" + objgradeinfo.getGradeName()
                        + "')";
           infoStr = "添加年級";
        }

        System.out.println(sqlStatement);
        return AdapterObject(sqlStatement);
    }

    public boolean InsertOrUpdateObject(Obj_classinfo objclassinfo){
        String sqlStatement = null;
        if (validateID("classID","tb_classinfo",objclassinfo.getClassID())){
            /*sqlStatement = "Update tb_classinfo set classID = '" + objclassinfo.getClassID()
                           + "',gradeID = '" + objclassinfo.getGradeID()
                           + "', className = '" + objclassinfo.getClassName()
                           + "' where classID = '" + objclassinfo.getClassID().trim() + "'";*/
            sqlStatement = "Update tb_classinfo set className = '" + objclassinfo.getClassName()
                           + "' where classID = '" + objclassinfo.getClassID().trim() + "'";

            infoStr = "更新班級";

        }else{
            sqlStatement = "Insert tb_classinfo(classID,gradeID,className) values ('"
                       + objclassinfo.getClassID() + "','" + objclassinfo.getGradeID()
                       + "','" + objclassinfo.getClassName() + "')";
           infoStr = "添加班級";
        }
        System.out.println(sqlStatement);
        return AdapterObject(sqlStatement);

    }
    public boolean InsertOrUpdateObject(Obj_subject objsubject){
        String sqlStatement = null;
        if (validateID("code","tb_subject",objsubject.getCode())){
            sqlStatement = "Update tb_subject set code = '" + objsubject.getCode()
                           + "',subject = '" + objsubject.getSubject()
                           + "' where code = '" + objsubject.getCode().trim() + "'";
            infoStr = "更新考試科目";

        }else{
            sqlStatement = "Insert tb_subject(code,subject) values ('"
                       + objsubject.getCode() + "','" + objsubject.getSubject()
                       + "')";
            infoStr = "添加考試科目";
        }

        return AdapterObject(sqlStatement);

    }
    public boolean InsertOrUpdateObject(Obj_examkinds objexamkinds){
        String sqlStatement = null;
        if (validateID("KindID","tb_examkinds",objexamkinds.getKindID())){
            sqlStatement = "Update tb_examkinds set KindID = '" + objexamkinds.getKindID()
                           + "',KindName = '" +  objexamkinds.getKindName()
                           + "' where KindID = '" + objexamkinds.getKindID().trim() + "'";

            infoStr = "更新考試類別";

        }else{
            sqlStatement = "Insert tb_examkinds(KindID,KindName) values ('"
                           + objexamkinds.getKindID() + "','" +
                           objexamkinds.getKindName()
                           + "')";
            infoStr = "添加考試類別";
        }
        return AdapterObject(sqlStatement);
    }
    public boolean InsertOrUpdateObject(Obj_user objuser){
        String sqlStatement = null;
        if (validateID("userid","tb_user",objuser.getUserid())){
            sqlStatement = "Update tb_user set userid = '" + objuser.getUserid()
                           + "',username = '" + objuser.getUsername()
                           + "',pass = '" + objuser.getPass()
                           + "' where userid = '" + objuser.getUserid().trim() + "'";
            infoStr = "更新用戶";

        }else{
            sqlStatement = "Insert tb_user(userid,username,pass) values ('"
                       + objuser.getUserid() + "','" + objuser.getUsername() + "','" + objuser.getPass()
                       + "')";
            infoStr = "添加用戶";
        }

        return AdapterObject(sqlStatement);

    }

    ///////////////////////////////////////////////
    //產生更新存盤學生信息語句
    public boolean InsertOrUpdateObject(Obj_student objstudent){
        String sqlStatement = null;
        if (validateID("stuid","tb_studentinfo",objstudent.getStuid())){
            sqlStatement = "Update tb_studentinfo set stuid = '" + objstudent.getStuid()
                           + "',classID = '" +  objstudent.getClassID()
                           + "',stuname = '" + objstudent.getStuname()
                           + "',sex = '" + objstudent.getSex()
                           + "',age = '" + objstudent.getAge()
                           + "',addr = '" + objstudent.getAddress()
                           + "',phone = '" + objstudent.getPhone()
                           + "' where stuid = '" + objstudent.getStuid().trim() + "'";

            infoStr = "更新學生信息";

        }else{
            sqlStatement = "Insert tb_studentinfo(stuid,classid,stuname,sex,age,addr,phone) values ('" + objstudent.getStuid()
                           + "','" +  objstudent.getClassID()
                           + "','" + objstudent.getStuname()
                           + "','" + objstudent.getSex()
                           + "','" + objstudent.getAge()
                           + "','" + objstudent.getAddress()
                           + "','" + objstudent.getPhone()
                           + "')";

            infoStr = "添加學生信息";
        }
        return AdapterObject(sqlStatement);
    }

    //產生更新存盤教師信息語句
    public boolean InsertOrUpdateObject(Obj_teacher objteacher){
        String sqlStatement = null;
        if (validateID("teaid","tb_teacher",objteacher.getTeaid())){
            sqlStatement = "Update tb_teacher set teaid = '" + objteacher.getTeaid()
                           + "',classID = '" +  objteacher.getClassID()
                           + "',teaname = '" + objteacher.getTeaname()
                           + "',sex = '" + objteacher.getSex()
                           + "',knowledge = '" + objteacher.getKnowledge()
                           + "',knowlevel = '" + objteacher.getKnowlevel()
                           + "' where teaid = '" + objteacher.getTeaid().trim() + "'";

            infoStr = "更新教師信息";

        }else{
            sqlStatement = "Insert tb_teacher(teaid,classID,teaname,sex,knowledge,knowlevel) values ('" + objteacher.getTeaid()
                           + "','" +  objteacher.getClassID()
                           + "','" + objteacher.getTeaname()
                           + "','" + objteacher.getSex()
                           + "','" + objteacher.getKnowledge()
                           + "','" + objteacher.getKnowlevel()
                           + "')";

            infoStr = "添加教師信息";
        }
        return AdapterObject(sqlStatement);
    }
    //驗證數據表中是否存在數據
    private boolean validateobjgradeinfo(String stuid,String kindid,String code){
        String sqlStr = null;
        sqlStr = "select count(*) from tb_gradeinfo_sub where stuid = '"
                 +  stuid  + "' and kindID = '" + kindid + "' and code = '" + code + "'";
        System.out.println(sqlStr);
        try{
            con = CommonaJdbc.conection;
            pstmt = con.prepareStatement(sqlStr);
            java.sql.ResultSet rs = null;
            rs = pstmt.executeQuery();
            if (rs.next()) {
                if (rs.getInt(1) > 0)   return true;
            }
        }catch(java.sql.SQLException sql){
             sql.printStackTrace();
          new appstu.view.JF_view_error("執行的SQL語句為:\n" + sqlStr + "\n錯誤信息為：" +sql.getMessage());

           return false;
        }

        return false;
    }

    //產生更新學生信息信息語句
    public boolean InsertOrUpdate_Obj_gradeinfo_sub(Obj_gradeinfo_sub[] object){
        try{
            con = CommonaJdbc.conection;
            stmt = con.createStatement();

            for (int i = 0; i < object.length; i++) {
                String sqlStr = null;
                if (validateobjgradeinfo(object[i].getStuid(),object[i].getKindID(),object[i].getCode())){
                    sqlStr =
                        "update tb_gradeinfo_sub set stuid = '" +
                        object[i].getStuid() + "',stuname = '" + object[i].getSutname() +
                        "',kindID = '" + object[i].getKindID() + "',code = '" +object[i].getCode() +
                        "',grade = " + object[i].getGrade() + " ,examdate = '" +
                        object[i].getExamdate() +"' where stuid = '"
                        +  object[i].getStuid()  + "' and kindID = '" + object[i].getKindID() + "' and code = '" + object[i].getCode() + "'";

                }else{
                    sqlStr =
                       "insert  tb_gradeinfo_sub(stuid,stuname,kindID,code,grade,examdate)  values ('" +
                       object[i].getStuid() + "','" + object[i].getSutname() +
                       "','" + object[i].getKindID() + "','" +
                       object[i].getCode() +
                       "'," + object[i].getGrade() + " ,'" +
                       object[i].getExamdate() +
                       "')";
                }

                System.out.println("sqlStr = " + sqlStr);

                stmt.addBatch(sqlStr);
            }
            stmt.executeBatch();
            JOptionPane.showMessageDialog(null, "學生成績數據存盤成功!!!", "系統提示",
                                          JOptionPane.INFORMATION_MESSAGE);
        }catch(java.sql.SQLException sqlerror) {
            new appstu.view.JF_view_error("錯誤信息為：" +sqlerror.getMessage());
            return false;
        }
        return true;
    }
    //產生更新學生信息信息語句
    public boolean Delete_Obj_gradeinfo_sub(Obj_gradeinfo_sub[] object){
        try{
            con = CommonaJdbc.conection;
            stmt = con.createStatement();

            for (int i = 0; i < object.length; i++) {
                String sqlStr = null;
                sqlStr = "Delete From tb_gradeinfo_sub  where stuid = '"
                        +  object[i].getStuid()  + "' and kindID = '"
                        + object[i].getKindID() + "' and code = '"
                        + object[i].getCode() + "'";
                System.out.println("sqlStr = " + sqlStr);
                stmt.addBatch(sqlStr);
            }
            stmt.executeBatch();
            JOptionPane.showMessageDialog(null, "學生成績數據數據刪除成功!!!", "系統提示",
                                          JOptionPane.INFORMATION_MESSAGE);
        }catch(java.sql.SQLException sqlerror) {
            new appstu.view.JF_view_error("錯誤信息為：" +sqlerror.getMessage());
            return false;
        }
        return true;
    }


}