package cn.com.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 cn.com.entity.Class;
import cn.com.entity.Major;
import cn.com.entity.Question;
import cn.com.entity.Score;
import cn.com.entity.Student;
import cn.com.entity.Teacher;
import cn.com.utils.DBUtils;



public class TeaDao {
	Connection conn;
	PreparedStatement pst;
	ResultSet rs;
	
	
	public Teacher tealogin(Teacher tea) {
		// TODO Auto-generated method stub
		try {
			conn = DBUtils.getConn();
			String sql = "select * from teacher where tid = ? and tname = ? and tpwd = ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, tea.getTid());
			pst.setString(2, tea.getTname());
			pst.setString(3, tea.getTpwd());
			rs = pst.executeQuery();
			while(rs.next()){
				Teacher t = new Teacher();
				t.setTid(rs.getString("tid"));
				t.setTname(rs.getString("tname"));
				t.setTpwd(rs.getString("tpwd"));
				t.setTphone(rs.getString("tphone"));
				return t;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.closeResouce(conn, pst, rs);
		}
		return null;
	}


	public List<Student> showStu(String tid) {
		// TODO Auto-generated method stub
		try {
			conn = DBUtils.getConn();
			String sql = "select * from student s,major m,class c,teacher t where s.mid = m.mid and s.cid = c.cid and t.tid = c.tid and t.tid = ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, tid);
			rs = pst.executeQuery();
			List<Student> bylist = new ArrayList();
			while(rs.next()){
				Student st = new Student();
				st.setSid(rs.getString("sid"));
				st.setSname(rs.getString("sname"));
				st.setSeid(rs.getString("seid"));
				st.setSpwd(rs.getString("spwd"));
				st.setSsex(rs.getInt("ssex"));
				st.setSof(rs.getString("sof"));
				st.setScountenance(rs.getString("scountenance"));
				st.setSchooling(rs.getString("schooling"));
				st.setStatus(rs.getInt("status"));
				st.setSxuezhi(rs.getString("sxuezhi"));
				st.setPhone(rs.getString("phone"));
				st.setSphone(rs.getString("sphone"));
				st.setSqq(rs.getString("sqq"));
				st.setSmail(rs.getString("smail"));
				st.setMid(rs.getInt("mid"));
				st.setCid(rs.getInt("cid"));
				st.setMname(rs.getString("mname"));
				st.setCname(rs.getString("cname"));
				
				bylist.add(st);
				System.out.println(st.getCname()+"!!!!!!???");
			}
			return bylist;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.closeResouce(conn, pst, rs);
		}
		return null;
	}


	public List<Student> showMohu(String tid, String mohu) {
		// TODO Auto-generated method stub
		try {
			conn = DBUtils.getConn();
			String sql = "select * from student s,major m,class c,teacher t where s.mid = m.mid and s.cid = c.cid and t.tid = c.tid and t.tid = ? and sname like ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, tid);
			pst.setString(2, "%"+mohu+"%");
			rs = pst.executeQuery();
			List<Student> bylist = new ArrayList();
			while(rs.next()){
				Student st = new Student();
				st.setSid(rs.getString("sid"));
				st.setSname(rs.getString("sname"));
				st.setSeid(rs.getString("seid"));
				st.setSpwd(rs.getString("spwd"));
				st.setSsex(rs.getInt("ssex"));
				st.setSof(rs.getString("sof"));
				st.setScountenance(rs.getString("scountenance"));
				st.setSchooling(rs.getString("schooling"));
				st.setStatus(rs.getInt("status"));
				st.setSxuezhi(rs.getString("sxuezhi"));
				st.setPhone(rs.getString("phone"));
				st.setSphone(rs.getString("sphone"));
				st.setSqq(rs.getString("sqq"));
				st.setSmail(rs.getString("smail"));
				st.setMid(rs.getInt("mid"));
				st.setCid(rs.getInt("cid"));
				st.setMname(rs.getString("mname"));
				st.setCname(rs.getString("cname"));
				
				bylist.add(st);
				System.out.println(st.getCname()+"!!!!!!???");
			}
			return bylist;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.closeResouce(conn, pst, rs);
		}
		return null;
	}
	
	public List<Major> showMajor() {
		// TODO Auto-generated method stub
		try {
			conn = DBUtils.getConn();
			String sql = "select * from major";
			pst = conn.prepareStatement(sql);
			
			rs = pst.executeQuery();
			List<Major> bylist = new ArrayList();
			while(rs.next()){
				Major ma = new Major();
				ma.setMid(rs.getInt("mid"));
				ma.setMname(rs.getString("mname"));
				bylist.add(ma);
			}
			return bylist;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.closeResouce(conn, pst, rs);
		}
		return null;
	}
	public List<Class> showClass() {
		// TODO Auto-generated method stub
		try {
			conn = DBUtils.getConn();
			String sql = "select * from class";
			pst = conn.prepareStatement(sql);
			
			rs = pst.executeQuery();
			List<Class> bylist = new ArrayList();
			while(rs.next()){
				Class ma = new Class();
				ma.setCid(rs.getInt("cid"));
				ma.setCname(rs.getString("cname"));
				bylist.add(ma);
			}
			return bylist;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.closeResouce(conn, pst, rs);
		}
		return null;
	}
	public int addStu(Student stu) {
		// TODO Auto-generated method stub
		int i = 0;
		try {
			conn = DBUtils.getConn();
			String sql = "insert into student values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			pst = conn.prepareStatement(sql);
			pst.setString(1, stu.getSid());
			pst.setString(2, stu.getSname());
			pst.setString(3, stu.getSeid());
			pst.setString(4, stu.getSpwd());
			pst.setInt(5, stu.getSsex());
			pst.setString(6, stu.getSof());
			pst.setString(7, stu.getScountenance());
			pst.setString(8, stu.getSchooling());
			pst.setInt(9, stu.getStatus());
			
			
			pst.setString(10, stu.getSxuezhi());
			pst.setString(11, null);
			pst.setString(12, null);
			pst.setString(13, null);
			pst.setString(14, null);
			
			pst.setInt(15, stu.getMid());
			pst.setInt(16, stu.getCid());
			i = pst.executeUpdate();
			System.out.println("!!!!!!!!!!!!!!!!!!!!");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.closeResouce(conn, pst);
		}
		return i;
	}
	public List<Question> showQu(String tid) {
		// TODO Auto-generated method stub
		try {
			conn = DBUtils.getConn();
			String sql = "select * from question where tid = ?";
			pst = conn.prepareStatement(sql);
			pst.setString(1, tid);
			rs = pst.executeQuery();
			List<Question> bylist = new ArrayList();
			while(rs.next()){
				Question q = new Question();
				q.setQid(rs.getInt("qid"));
				q.setQpj(rs.getInt("qpj"));
				q.setQlx(rs.getInt("qlx"));
				q.setQtxt(rs.getString("qtxt"));
				q.setTid(rs.getString("tid"));
				bylist.add(q);
			}
			return bylist;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			DBUtils.closeResouce(conn, pst, rs);
		}
		return null;
	}

}