package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import domain.Course;
import domain.Student;
import util.ConnectionUtil;

public class StudentDao {

	public boolean addStudent(Student student){
		String sql = "insert into information values(?,?,?,?,?,?,?,?)";
		Object[] params = {
				student.getId(),
				student.getName(),
				student.getAge(),
				student.getSex(),
				student.getcourse_num(),
				student.getAchievements(),
				student.getClasses(),
				student.getSystem()
		};
		return ConnectionUtil.executeUpdate(sql, params);
	}

	public List<Student> queryAllStudent(){
		String sql = "select * from information";
		Connection conn = ConnectionUtil.getConection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			Student u = null;
			List<Student> students = new ArrayList<Student>();
			while(rs.next()){
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				String sex = rs.getString("sex");
				int course_num = rs.getInt("course_num");
				double achievements = rs.getDouble("achievements");
				String classes = rs.getString("classes");
				String system = rs.getString("system");
				u = new Student(id, name, age, sex, course_num, achievements, classes, system);
				students.add(u);
			}
			return students;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	public boolean deleteStudent(int id){
		String sql = "delete from information where id = ?";
		Object[] params = {
				id
		};
		return ConnectionUtil.executeUpdate(sql, params);
	}
	public Student queryStudent(Integer id){
		String sql = "select * from information where id = ?";
		Connection conn = ConnectionUtil.getConection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			ResultSet rs = ps.executeQuery();
			Student u = null;
			if(rs.next()){
				id = rs.getInt("id");
				String name = rs.getString("name");
				int age = rs.getInt("age");
				String sex = rs.getString("sex");
				int course_num = rs.getInt("course_num");
				double achievements = rs.getDouble("achievements");
				String classes = rs.getString("classes");
				String system = rs.getString("system");
				u = new Student(id ,name, age, sex, course_num, achievements, classes, system);
			}
			return u;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
	public boolean updateStudent(Student student,int num){
		String sql = "update information set id = ?,name = ?,age = ?,sex = ?,course_num= ?,achievements= ?,classes = ?,system = ? where id = ?";
		Object[] params = {		
				student.getId(),
				student.getName(),
				student.getAge(),
				student.getSex(),
				student.getcourse_num(),
				student.getAchievements(),
				student.getClasses(),
				student.getSystem(),
				num
				
		};
		return ConnectionUtil.executeUpdate(sql, params);
	}
	
	//查询学号为num的学生选择的课程
	public List<Course> queryCourse(Integer num){
		String sql = "select * from course where Id = ?";
		Connection conn = ConnectionUtil.getConection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, num);
			ResultSet rs = ps.executeQuery();
			Course u = null;
			List<Course> courses = new ArrayList<Course>();
			while(rs.next()){
				int cid = rs.getInt("cid");
				String cname = rs.getString("cname");
				double mark = rs.getDouble("mark");
				double time = rs.getDouble("time");
				int Id = rs.getInt("Id");
				double originalscore = rs.getDouble("originalscore");
				double finallyscore = rs.getDouble("finallyscore");
				u = new Course(cid, cname, mark, time, Id, originalscore, finallyscore);
				courses.add(u);
			}
			return courses;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}
	}
}