package com.home.dao;


import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import com.home.db.Conntion;
import com.home.pojo.Model_question;
import com.home.pojo.Model_score;
import com.home.pojo.Model_stu;
import com.home.pojo.Model_tea;
import com.sun.org.apache.xpath.internal.operations.Mod;



public class MyDao {
	static private Statement smt;
	static private Statement smt1;
	static private Statement smt2;
	static private ResultSet rs;
	static private ResultSet rs1;
	static private ResultSet rs2;
	static private Conntion dbc = null;
	//打开数据库连接
	public static void open(){
		
		try {
			dbc = new Conntion();
		} catch (Exception e1) {
			// TODO Auto-generated catch block
			System.out.print("连接失败!");
		}
		try {
			smt=dbc.getConnection().createStatement();
			smt1=dbc.getConnection().createStatement();
			smt2=dbc.getConnection().createStatement();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("创建连接失败");
		}
	}
	
	//关闭数据库连接
	static void close()
	{
		
			try {
				if(rs!=null)
				    rs.close();
				if(rs1!=null)
					rs1.close();
				if(rs2!=null)
					rs2.close();
				if(smt!=null)
					smt.close();
				if(smt1!=null)
					smt1.close();
				if(smt2!=null)
					smt2.close();
				if(dbc!=null)
					dbc.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.print("关闭不正常，请联系管理员！！");
			}
		
	}
	
	//学生登录验证
	public static boolean stu_login(String sname,String spass){
		String sql="select * from s_user where s_id='"+sname+"' and s_password='"+spass+"'";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("登陆失败！");
		}
		close();
		return false;
	}
	
	
	//教师登录验证
		public static boolean tea_login(String tname,String tpass){
			String sql="select * from t_user where t_id='"+tname+"' and t_password='"+tpass+"'";
			open();
			try {
				rs=smt.executeQuery(sql);
				while(rs.next()){
					return true;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.print("登陆失败！");
			}
			close();
			return false;
		}
		
		
		//管理员登录验证
				public static boolean admin_login(String aname,String apass){
					String sql="select * from admin_user where a_id='"+aname+"' and a_password='"+apass+"'";
					open();
					try {
						rs=smt.executeQuery(sql);
						while(rs.next()){
							return true;
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						System.out.print("登陆失败！");
					}
					close();
					return false;
				}
				
	//修改学生登录密码

    public static void stu_mod_password(String rsname,String rspass){
		String sql="update s_user set s_password='"+rspass+"' where s_id='"+rsname+"'";
		open();
		try {
			smt.executeUpdate(sql);
				} catch (SQLException e) {
			      System.out.print("更新失败！！");
				}
			close();
				}
	
	//注册新用户
	public static void addU(String rename,String repass,String remail){
		String sql="insert into users(user_name,user_password,user_email) values('"+rename+"','"+repass+"','"+remail+"')";
		open();
		try {
			smt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("插入失败！！");
		}
	   close();
		
	}
	
	//返回所有问题
	public static ArrayList<Model_question> get_question(){
		ArrayList<Model_question> ul=new ArrayList<Model_question>();
		String sql="select * from question";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				Model_question info=new Model_question();
				info.setSc1(rs.getString(2));
				info.setId(rs.getInt(1));
				ul.add(info);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回问题失败！");
		}
		close();
		return ul;
	}

	
	//返回学生姓名
	public static String get_stu_name(String sname){
		String stu_name = null;
		String sql="select * from s_user where s_id='"+sname+"'";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				stu_name=rs.getString(4);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回姓名失败！");
		}
		close();
		return stu_name;
	}
	
	
	//返回所有教师
	public static ArrayList<Model_tea> get_tea_name(){
		ArrayList<Model_tea> ul=new ArrayList<Model_tea>();
		String sql="select * from t_user";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				Model_tea info=new Model_tea();
				info.setT_name(rs.getString(6));
				info.setT_id(rs.getInt(2));
				int id=rs.getInt(1);
				info.setId_1(rs.getInt(1));
				String sql1="select * from course where t_user_id='"+id+"'";
				rs1=smt1.executeQuery(sql1);
				while(rs1.next()){
					info.setT_course(rs1.getString(2));//返回课程名称
					info.setCourse_id(rs1.getInt(1));//返回课程ID
				}
				ul.add(info);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回所有教师失败！");
		}
		close();
		return ul;
		
	}
	
	
	//更新教师的评教分数
	public static void update_pingjiao(int stu_id,int id_1,int course_id,String time,int sc1,int sc2,int sc3,int sc4
			,int sc5,int sc6,int sc7,int sc8,int sc9,int sc10,String area ){
		open();
		String sql="select * from evaluate where t_user_id='"+id_1+"' and s_user_id='"+stu_id+"'";
		try {
			rs=smt.executeQuery(sql);
			if(rs.next()){
				String sql1="update evaluate set sc1='"+sc1+"',sc2='"+sc2+"'," +
					"sc3='"+sc3+"',sc4='"+sc4+"',sc5='"+sc5+"',sc6='"+sc6+"'," +
							"sc7='"+sc7+"',sc8='"+sc8+"',sc9='"+sc9+"',sc10='"+sc10+"'" +
									",comments='"+area+"',time='"+time+"' where s_user_id='"+stu_id+"'" +
											" and t_user_id='"+id_1+"'";
				smt1.executeUpdate(sql1);
			}else{
				String sql2="insert into evaluate(s_user_id,t_user_id,time,course_id," +
						"sc1,sc2,sc3,sc4,sc5,sc6," +
						"sc7,sc8,sc9,sc10,comments)" +
						" values('"+stu_id+"','"+id_1+"','"+time+"','"+course_id+"','"+sc1+"'" +
								",'"+sc2+"','"+sc3+"','"+sc4+"','"+sc5+"','"+sc6+"'" +
										",'"+sc7+"','"+sc8+"','"+sc9+"','"+sc10+"','"+area+"')";
				smt2.executeUpdate(sql2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("修改评分失败");
		}
		close();
		
		
	}
	
	
	//返回学生基本信息
	public static ArrayList<Model_stu> get_stu_info(int s_id){
		ArrayList<Model_stu> ul=new ArrayList<Model_stu>();
		String sql="select * from s_user where s_id='"+s_id+"'";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				Model_stu info=new Model_stu();
				info.setS_zhuanye(rs.getString(5));
				info.setS_tel(rs.getString(6));
				info.setS_sex(rs.getString(7));
				info.setS_yuanxi(rs.getString(8));
				info.setS_address(rs.getString(9));
				info.setS_zhengzhi(rs.getString(10));
				info.setS_birthday(rs.getString(11));
				info.setS_minzu(rs.getString(12));
				ul.add(info);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回学生信息失败！");
		}
		close();
		return ul;
	}
	
	
	//修改学生信息
	public static void mod_stu_info(int s_id,String sex,String tel,String adress,String birthday,String minzu){
		String sql="update s_user set s_sex='"+sex+"',s_tel='"+tel+"'," +
					"s_address='"+adress+"',s_birthday='"+birthday+"',s_minzu='"+minzu+"' where s_id='"+s_id+"'";
		open();
		try {
			smt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("修改学生信息失败！");
		}
		close();
	}

	
	//返回所有学生信息
	public static ArrayList<Model_stu> get_all_stu(int t_id){
		ArrayList<Model_stu> ul=new ArrayList<Model_stu>();
		String sql="select * from s_user";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				Model_stu info=new Model_stu();
				info.setId(rs.getInt(1));
				info.setS_id(rs.getInt(2));
				int s_id=rs.getInt(2);
				info.setS_name(rs.getString(4));
				info.setS_zhuanye(rs.getString(5));
				
				String sql1="select * from t_user where t_id='"+t_id+"'";
				rs1=smt1.executeQuery(sql1);
				while(rs1.next()){
					int t_ID=rs1.getInt(1);//返回教师序号
					String sql2="select * from evaluate where s_user_id='"+s_id+"' and t_user_id='"+t_ID+"'";
					rs2=smt2.executeQuery(sql2);
					if(rs2.next()){
						info.setS_if("已评教");
					}
					else
						info.setS_if("未评教");
				}
				ul.add(info);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回所有学生信息失败!");
		}
		close();
		return ul;
	}
	
	
	//返回评测单个学生评测结果
	public static ArrayList<Model_score> get_score(int s_id,int t_id){
		ArrayList<Model_score> ul=new ArrayList<Model_score>();
		open();
		String sql="select * from t_user where t_id='"+t_id+"'";
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt(1);//返回教师序号
				String sql1="select * from evaluate where s_user_id='"+s_id+"' and t_user_id='"+id+"'";
				rs1=smt1.executeQuery(sql1);
				while(rs1.next()){
					Model_score info=new Model_score();	
					info.setSc1(rs1.getInt(6));
					info.setSc2(rs1.getInt(7));
					info.setSc3(rs1.getInt(8));
					info.setSc4(rs1.getInt(9));
					info.setSc5(rs1.getInt(10));
					info.setSc6(rs1.getInt(11));
					info.setSc7(rs1.getInt(12));
					info.setSc8(rs1.getInt(13));
					info.setSc9(rs1.getInt(14));
					info.setSc10(rs1.getInt(15));
					info.setComments(rs1.getString(16));
					ul.add(info);
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回评测单个学生评测结果失败！");
		}
		close();
		return ul;
	}
	
	//返回指定教师所教的课程
	public static String get_course(int t_id){
		String course_name=null;
		String sql="select * from t_user where t_id='"+t_id+"'";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				int id=rs.getInt(1);
				String sql1="select * from course where t_user_id='"+id+"'";
				rs1=smt1.executeQuery(sql1);
				while(rs1.next())
					course_name=rs1.getString(2);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回指定教师所教的课程失败！");
		}
		close();
		return course_name;
	}
	
	
	//分数段人数统计分析
	public static ArrayList<Model_score> get_all_score(int t_id,int i){
		
		ArrayList<Model_score> ul=new ArrayList<Model_score>();
		int id=get_tea_id(t_id);
			try {
				for(int i1=1;i1<=10;i1++){
					
					String sql="select count(*) from evaluate where t_user_id='"+id+"' and sc"+i+"='"+i1+"'";
					
					open();
				rs=smt.executeQuery(sql);
				while(rs.next()){
					System.out.println("人数:"+rs.getInt(1));
					Model_score info=new Model_score();
					switch(i){
					case 1:
						info.setSc1(rs.getInt(1));
					case 2:
						info.setSc2(rs.getInt(1));
					case 3:
						info.setSc3(rs.getInt(1));
					case 4:
						info.setSc4(rs.getInt(1));
					case 5:
						info.setSc5(rs.getInt(1));
					case 6:
						info.setSc6(rs.getInt(1));
					case 7:
						info.setSc7(rs.getInt(1));
					case 8:
						info.setSc8(rs.getInt(1));
					case 9:
						info.setSc9(rs.getInt(1));
					case 10:
						info.setSc10(rs.getInt(1));
						
					}
					ul.add(info);
				}
				close();
				
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			
		}
			
		return ul;
	}
	
	//返回教师序号，和上一个函数专用
	public static int get_tea_id(int t_id){
		int id=0;
		String sql="select * from t_user where t_id='"+t_id+"'";
		open();
		try {
			rs1=smt1.executeQuery(sql);
			while(rs1.next()){
				id=rs1.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回教师序号失败！");
		}
		close();
		return id;
	}
	
	//返回已评教人数
	public static int get_yes_count(int t_id){
		int count=0;
		int id=get_tea_id(t_id);
		String sql="select count(*) from evaluate where t_user_id='"+id+"'";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				count=rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("返回已评教人数失败！");
		}
		close();
		return count;
	}
	
	
	//修改评教问题
	public static void mod_question(String sc1,int i){
	
			String sql="update question set sc1='"+sc1+"' where id='"+i+"'";
			open();
			try {
				smt.executeUpdate(sql);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.print("修改评教问题！");
			}
			close();
		
	}
	
	//管理员返回所有学生
	public static ArrayList<Model_stu> admin_get_all_stu(){
		ArrayList<Model_stu> ul=new ArrayList<Model_stu>();
		String sql="select * from s_user";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				Model_stu info=new Model_stu();
				info.setId(rs.getInt(1));
				info.setS_id(rs.getInt(2));
				info.setS_name(rs.getString(4));
				info.setS_zhuanye(rs.getString(5));
				ul.add(info);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return ul;
	}
	
	//管理员删除学生
	public static void admin_del_stu(int s_id){
		String sql="delete from s_user where s_id='"+s_id+"'";
		open();
		try {
			smt.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.print("删除出错！");
		}
		close();
	}
	
	//管理员返回所有教师
	public static ArrayList<Model_tea> admin_get_all_tea(){
		ArrayList<Model_tea> ul=new ArrayList<Model_tea>();
		String sql="select * from t_user";
		open();
		try {
			rs=smt.executeQuery(sql);
			while(rs.next()){
				Model_tea info=new Model_tea();
				info.setId_1(rs.getInt(1));
				info.setT_id(rs.getInt(2));
				info.setT_name(rs.getString(6));
				info.setT_sex(rs.getString(5));
				info.setT_tel(rs.getString(4));
				info.setT_xueli(rs.getString(7));
				ul.add(info);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		close();
		return ul;
	}
	
}