package Sys.dao;

import java.sql.*;
import java.io.*;
import javax.swing.*;
import java.util.*;

import Sys.db.*;
import Tools.Teacher;


public class TeacherDao {
	private Connection conDB=null;
	public TeacherDao(){
		conDB=DatabaseTool.conDB();//所以這里的dao引用的是DatabaseTool的連接，所以我們可以通過DatabaseTool來關閉
	}
	
	public void addTeacher(Teacher teacher) {//這段代碼寫的好差
		PreparedStatement ps=null;
		if(teacher.getOLEPhoto()!=null){
			try{
				//ps=conDB.prepareStatement("insert into Teacher(teacherid,teachername,teacherkey" +
					//	",title,sex,classid,photo) values(?,?,?,?,?,?,?)");
					ps=conDB.prepareStatement("insert into Teacher(teacherid,teachername,teacherkey" +
					",title,sex,classid,photo) values(?,?,?,?,?,?,?)");
					ps.setString(1, teacher.getTeacherID());
					ps.setString(2, teacher.getTeacherName());
					ps.setString(3, teacher.getTeacherKey());
					ps.setString(4, teacher.getTitle());
					ps.setString(5, teacher.getSex());
					ps.setString(6, teacher.getClassID());
					ps.setBinaryStream(7, teacher.getOLEPhoto(), teacher.getOLEPhoto().available());
					//上面沒有捕抓異常，因為在它之前的類聲明已經捕抓了，不再拋出異常
					//ps.setString(8, teacher.getBlant());
					ps.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				JOptionPane.showMessageDialog(null, "對不起，已經存在該老師ID，請重新輸入！");
			}catch(IOException ex){
				ex.printStackTrace();
				JOptionPane.showMessageDialog(null, "對不起，獲取圖片失敗，由于選擇路徑不對，請重新選擇！");
			}finally{
				DatabaseTool.closeStatement(ps);
			}
		}else{
			try{
				ps=conDB.prepareStatement("insert into Teacher(teacherid,teachername,teacherkey" +
				",title,sex,classid) values(?,?,?,?,?,?)");
				ps.setString(1, teacher.getTeacherID());
				ps.setString(2, teacher.getTeacherName());
				ps.setString(3, teacher.getTeacherKey());
				ps.setString(4, teacher.getTitle());
				ps.setString(5, teacher.getSex());
				ps.setString(6, teacher.getClassID());
				//上面沒有捕抓異常，因為在它之前的類聲明已經捕抓了，不再拋出異常
				//ps.setString(8, teacher.getBlant());
				ps.executeUpdate();		
			}catch(SQLException ew){
				ew.printStackTrace();
				JOptionPane.showMessageDialog(null, "對不起，已經存在該老師ID，請重新輸入！");
			}finally{
				DatabaseTool.closeStatement(ps);
				//連接conDB不應該在這里關閉，因為還沒有提交
			}
		}
	}
	
	public void modifyClassTeacher(Teacher teacher,String classid) {
		PreparedStatement ps=null;
		if(teacher.getOLEPhoto()!=null){
			try{
				ps=conDB.prepareStatement("update Teacher set teacherid=?,teachername=?,teacherkey=?" +
						",sex=?,title=?,classid=?,photo=? where classid=?");
				ps.setString(1, teacher.getTeacherID());
				ps.setString(2, teacher.getTeacherName());
				ps.setString(3, teacher.getTeacherKey());
				ps.setString(4, teacher.getTitle());
				ps.setString(5, teacher.getSex());
				ps.setString(6, teacher.getClassID());
				ps.setBinaryStream(7, teacher.getOLEPhoto(), teacher.getOLEPhoto().available());
				ps.setString(8, classid);
				ps.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				JOptionPane.showMessageDialog(null, "對不起，已經存在該老師ID，請重新輸入！");
			}catch(IOException ep){
				ep.printStackTrace();
				JOptionPane.showMessageDialog(null, "對不起，獲取圖片失敗，由于選擇路徑不對，請重新選擇！");
			}finally{
				DatabaseTool.closeStatement(ps);
			}
		}else{
			try{
				ps=conDB.prepareStatement("update Teacher set teacherid=?,teachername=?,teacherkey=?" +
						",sex=?,title=?,classid=? where classid=?");
				ps.setString(1, teacher.getTeacherID());
				ps.setString(2, teacher.getTeacherName());
				ps.setString(3, teacher.getTeacherKey());
				ps.setString(4, teacher.getTitle());
				ps.setString(5, teacher.getSex());
				ps.setString(6, teacher.getClassID());
				ps.setString(7, classid);
				ps.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				JOptionPane.showMessageDialog(null, "對不起，已經存在該老師ID，請重新輸入！");
			}finally{
				DatabaseTool.closeStatement(ps);
			}
		}
	}
	
	public void delClassTeacher(int teacherid){
		PreparedStatement ps=null;
		try{
			ps=conDB.prepareStatement("delete from Teacher where teacherid=?");
			ps.setInt(1, teacherid);
			ps.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "對不起，該學號不存在，可能已刪除！");
		}finally{
			DatabaseTool.closeStatement(ps);
		}
	}
	//我一開始是將下面的參數設為teacherid，后面發現錯了
	public Teacher getTeacher(String classid){
		byte[] by=new byte[1000*1024];
		PreparedStatement ps=null;
		ResultSet rs=null;
		Teacher teacher=null;
		try{
			ps=conDB.prepareStatement("select * from Teacher where classid=?");
			ps.setString(1, classid);
			rs=ps.executeQuery();
			if(rs.next()){
				teacher=new Teacher();
				teacher.setTeacherID(rs.getString("teacherid"));
				teacher.setTeacherName(rs.getString("teachername"));
				teacher.setTeacherKey(rs.getString("teacherkey"));
				//這里很神奇，下面兩行代碼我是調換了的，發現錯了，因為跟數據庫放置順序不同
				teacher.setTitle(rs.getString("title"));
				teacher.setSex(rs.getString("sex"));
				//我還想為什么這里會錯了，如果之前沒有插入圖片就會錯，因為Input會是null，所以加多一個判斷
				ByteArrayOutputStream bytestream = new ByteArrayOutputStream();
			    int ch;
			    InputStream input=rs.getBinaryStream("photo");
			    if(input!=null){
				    while ((ch = input.read()) != -1)
				       bytestream.write(ch);
				    by=bytestream.toByteArray();
					teacher.setOLEPhoto(new ByteArrayInputStream(by));
			    }
				teacher.setClassID(rs.getString("classid"));
			}
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "對不起，獲取老師資料失敗，可能該班級老師已經給刪除！");
		}catch(IOException ex){
			ex.printStackTrace();
			JOptionPane.showMessageDialog(null, "對不起，獲取老師相片失敗，文件可能損壞！");
		}finally{
			DatabaseTool.closeResultSet(rs);
			DatabaseTool.closeStatement(ps);
		}
		return teacher;
	}
	
	public Teacher getTeacherid(String teacherid){
		byte[] by=new byte[1000*1024];
		PreparedStatement ps=null;
		ResultSet rs=null;
		Teacher teacher=null;
		try{
			ps=conDB.prepareStatement("select * from Teacher where teacherid=?");
			ps.setString(1, teacherid);
			rs=ps.executeQuery();
			if(rs.next()){
				teacher=new Teacher();//為什么在這里才分配空間，因為如果查詢老師不成功也可以返回null，進行判斷，如登錄時
				teacher.setTeacherID(rs.getString("teacherid"));
				teacher.setTeacherName(rs.getString("teachername"));
				teacher.setTeacherKey(rs.getString("teacherkey"));
				teacher.setTitle(rs.getString("title"));
				teacher.setSex(rs.getString("sex"));
				ByteArrayOutputStream bytestream = new ByteArrayOutputStream();
			    int ch;
			    InputStream input=rs.getBinaryStream("photo");
			    if(input!=null){
				    while ((ch = input.read()) != -1)
				       bytestream.write(ch);
				    by=bytestream.toByteArray();
					teacher.setOLEPhoto(new ByteArrayInputStream(by));
			    }
				teacher.setClassID(rs.getString("classid"));
			}
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "沒有該用戶");
		}catch(IOException ex){
			ex.printStackTrace();
			JOptionPane.showMessageDialog(null, "獲取圖片出錯");
		}finally{
			DatabaseTool.closeResultSet(rs);
			DatabaseTool.closeStatement(ps);
		}
		return teacher;
	}
	
	public List<Teacher> getAllTeacher(){
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<Teacher> teacherlist=null;
		try{
			ps=conDB.prepareStatement("select * from Teacher");
			rs=ps.executeQuery();
			teacherlist=new ArrayList<Teacher>();
			Teacher teacher=null;
			while(rs.next()){
				teacher=new Teacher();
				teacher.setTeacherID(rs.getString("teacherid"));
				teacher.setTeacherName(rs.getString("teachername"));
				teacher.setTeacherKey(rs.getString("teacherkey"));
				teacher.setSex(rs.getString("sex"));
				teacher.setTitle(rs.getString("title"));
				teacher.setOLEPhoto(rs.getBinaryStream("photo"));
				teacher.setClassID(rs.getString("classid"));
				teacher.setBlant(rs.getString("blant"));
				teacherlist.add(teacher);
			}
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "獲取老師列表失敗");
		}finally{
			DatabaseTool.closeResultSet(rs);
			DatabaseTool.closeStatement(ps);
		}
		return teacherlist;
	}
}