package Sys.dao;

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

import Sys.db.*;
import Sys.vo.*;
public class StudentSroceDao {
	private static Connection conDB=null;
	public StudentSroceDao(){
		conDB=DatabaseTool.conDB();
	}
	public void addSroce(StudentSroce studentsroce){
		PreparedStatement ps=null;
		try{
			ps=conDB.prepareStatement("insert into StudentCourse(classid,coursename" +
					",studentid,sroce,modifytime) values(?,?,?,?,?)");
			ps.setString(1, studentsroce.getClassID());
			ps.setString(2, studentsroce.getCourseName());
			ps.setString(3, studentsroce.getStudentID());
			ps.setDouble(4, studentsroce.getSroce());
			ps.setString(5, studentsroce.getModifyTime());
			ps.executeUpdate();
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null,"插入學生成績出錯");
		}finally{
			DatabaseTool.closeStatement(ps);
		}
	}
	public void addSroceList(ArrayList<StudentSroce> srocelist){
		PreparedStatement ps=null;
		Iterator<StudentSroce> iter=srocelist.iterator();
		while(iter.hasNext()){
			StudentSroce subjectsroce=(StudentSroce)iter.next();
			try{
				ps=conDB.prepareStatement("update StudentCourse set sroce=?,modifytime=? " +
						"where coursename=? and studentid=?");
				ps.setDouble(1, subjectsroce.getSroce());
				ps.setString(2, (new java.util.Date()).toString());
				ps.setString(3, subjectsroce.getCourseName());
				ps.setString(4, subjectsroce.getStudentID());
				ps.executeUpdate();
			}catch(SQLException e){
				e.printStackTrace();
				JOptionPane.showMessageDialog(null,"對不起，可能輸入信息類型不對，請正確輸入信息！");
				return;
			}finally{
				DatabaseTool.closeStatement(ps);
			}
		}
		JOptionPane.showMessageDialog(null,"恭喜，添加學生成績成功");
	}
	
	//刪除學生會將他的成績也刪除
	public void delSroce(String studentID){
		PreparedStatement ps=null;
		try{
			StudentInfoDao studentinfodao=new StudentInfoDao();
			StudentInfo studentinfo=studentinfodao.getStudentInfo(studentID);
			if(studentinfo!=null){
				ps=conDB.prepareStatement("delete from StudentCourse where studentid=?");
				//刪除多條記錄
				ps.setString(1, studentID);
				ps.executeUpdate();
				//JOptionPane.showMessageDialog(null, "恭喜，刪除學生成功！");
			}else{
				//JOptionPane.showMessageDialog(null, "對不起，沒有該學生，請核查！");
			}
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "對不起，沒有該學生，請核查！");
		}finally{
			DatabaseTool.closeStatement(ps);
		}
	}
	

	
	public List<StudentSroce> getStudentSroceList(String classid ,String coursename){
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<StudentSroce> studentsrocelist=null;
		try{
			ps=conDB.prepareStatement("select StudMes.studentid,StudMes.studentname,StudMes.sex" +
					",StudentCourse.sroce,StudentCourse.modifytime from StudentCourse" +
					",StudMes where StudMes.studentid=StudentCourse.studentid and " +
					"StudentCourse.classid=? and StudentCourse.coursename=?");
			ps.setString(1, classid);
			ps.setString(2, coursename);
			rs=ps.executeQuery();
			studentsrocelist=new ArrayList<StudentSroce>();
			StudentSroce studentsroce=null;
			while(rs.next()){
				studentsroce=new StudentSroce();
				studentsroce.setClassID(classid);
				studentsroce.setStudentID(rs.getString("studentid"));
				studentsroce.setStudentName(rs.getString("studentname"));
				studentsroce.setSex(rs.getString("sex"));
				studentsroce.setCourseName(coursename);
				studentsroce.setSroce(rs.getDouble("sroce"));
				studentsroce.setModifyTime(rs.getString("modifytime"));
				studentsrocelist.add(studentsroce);
			}
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "對不起，該班級和課程有錯！");
		}finally{
			DatabaseTool.closeResultSet(rs);
			DatabaseTool.closeStatement(ps);
		}
		return studentsrocelist;
	}
	
	//public 
	public List<StudentSroce> getStudentSroce(String studentid){
		PreparedStatement ps=null;
		ResultSet rs=null;
		List<StudentSroce> list=null;
		try{
			ps=conDB.prepareStatement("select * from StudentCourse where studentid=?");
			ps.setString(1, studentid);
			rs=ps.executeQuery();
			StudentSroce studentsroce=null;
			list=new ArrayList<StudentSroce>();
			while(rs.next()){
				studentsroce=new StudentSroce();
				studentsroce.setCourseName(rs.getString("coursename"));
				studentsroce.setSroce(rs.getDouble("sroce"));
				list.add(studentsroce);
			}
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "對不起，沒有該學生！");
		}finally{
			DatabaseTool.closeResultSet(rs);
			DatabaseTool.closeStatement(ps);
		}
		return list;
	}
	
	
	public boolean getCourseStudentID(String coursename){
		PreparedStatement ps=null;
		ResultSet rs=null;
		try{
			ps=conDB.prepareStatement("select * from StudentCourse where coursename=?");
			ps.setString(1, coursename);
			rs=ps.executeQuery();
			boolean b=true;
			while(rs.next()){
				if(rs.getDouble("sroce")!=0.0){
					b=false;
				}
			}
			return b;
		}catch(SQLException e){
			e.printStackTrace();
			JOptionPane.showMessageDialog(null, "可以刪除該課程，因為沒有人在學");
			return true;
		}finally{
			DatabaseTool.closeResultSet(rs);
			DatabaseTool.closeStatement(ps);
		}
	}
}