package com.mingrisoft.dao;

import com.mingrisoft.actionForm.StudentForm;
import com.mingrisoft.core.ChStr;
import com.mingrisoft.core.ConnDB;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.text.NumberFormat;
public class StudentDAO {
	private ConnDB conn=new ConnDB();
	private ChStr chStr=new ChStr();
    //考生身份验证
    public int checkStudent(StudentForm studentForm) {
        int flag = 1;
        String sql = "SELECT * FROM tb_student where ID='" +
                     studentForm.getID()+ "'";
        ResultSet rs = conn.executeQuery(sql);
        try {
            if (rs.next()) {
                String pwd = studentForm.getPwd();
                if (pwd.equals(rs.getString(3))) {
                    rs.last();
                    int rowSum = rs.getRow();	//获取记录总数
                    rs.first();
                    if (rowSum!=1) {
                        flag = 2;
                        System.out.print("获取row的值：" + sql + rowSum);
                    }
                } else {
                    flag = 2;
                }
            }else{
                flag = 2;
            }
        } catch (Exception ex) {
            flag = 2;
            System.out.println(ex.getMessage());
        }
        return flag;
    }

	// 添加数据
	public String insert(StudentForm s) {
		String sql1 = "SELECT * FROM tb_student WHERE cardNo='" + s.getCardNo()
				+ "'";
		ResultSet rs = conn.executeQuery(sql1); // 执行SQL查询语句
		String sql = "";
		String falg = "miss"; // 用于记录返回信息的变量
		String ID = "";
		try {
			if (rs.next()) { // 假如存在记录
				falg = "re"; // 表示考生信息已经注册
			} else {
				/***************** 自动生成准考证号 ***********************************************/
				String sql_max = "SELECT max(ID) FROM tb_student";
				ResultSet rs_max = conn.executeQuery(sql_max); // 查询最大的准考证号
				java.util.Date date = new java.util.Date(); // 实例化java.util.Date()类
				String newTime = new SimpleDateFormat("yyyyMMdd").format(date); // 格式化当前日期
				if (rs_max.next()) {
					String max_ID = rs_max.getString(1); // 获取最大的准考证号
					int newId = Integer.parseInt(max_ID.substring(10, 16)) + 1;// 取出最大准考证号中的数字编号+1
					String no = chStr.formatNO(newId, 6); // 将生成的编号格式化为6位
					ID = "CN" + newTime + no; // 组合完整的准考证号
				} else { // 当第一个考生注册时
					ID = "CN" + newTime + "000001"; // 生成第一个准考证号
				}
				/********************************************************************************/
				sql = "INSERT INTO tb_student (ID,name,pwd,sex,question,answer,profession," +
						"cardNo) values('"+ ID+ "','"+ s.getName()+ "','"+ s.getPwd()+ "','"
						+ s.getSex()+ "','"+ s.getQuestion()+ "','"+ s.getAnswer()+ "','"
						+ s.getProfession()+ "','"+ s.getCardNo() + "')";
				int ret = conn.executeUpdate(sql); // 保存考生注册信息
				if (ret == 0) {
					falg = "miss"; // 表示考生注册失败
				} else {
					falg = "恭喜您，注册成功!\\r请记住您的准考证号：" + ID; // 返回生成的准考证号
				}
				conn.close(); // 关闭数据库连接
			}
		} catch (Exception e) {
			falg = "miss";
			System.out.println("添加考生信息时的错误信息：" + e.getMessage()); // 输出错误提示信息到控制台
		}
		return falg;
	}

    //查询方法
    public List query(String id) {
    	List studentList = new ArrayList();
        StudentForm studentForm1 = null;
        String sql="";
        if(id==null ||id.equals("")){
            sql = "SELECT * FROM tb_student ORDER BY joinTime DESC";
        }else{
        	sql = "SELECT * FROM tb_student WHERE id='" +id+ "'";
        }
        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                studentForm1 = new StudentForm();
                studentForm1.setID(rs.getString(1));
                studentForm1.setName(rs.getString(2));
                studentForm1.setPwd(rs.getString(3));
                studentForm1.setSex(rs.getString(4));
                studentForm1.setJoinTime(java.text.DateFormat.getDateTimeInstance().parse(rs.getString(5)));
                studentForm1.setQuestion(rs.getString(6));
                studentForm1.setAnswer(rs.getString(7));
                studentForm1.setProfession(rs.getString(8));
                studentForm1.setCardNo(rs.getString(9));
                studentList.add(studentForm1);
            }
        } catch (Exception ex) {}
        return studentList;
    }
    
   //根据返回学生单个记录
    public StudentForm getStudentObj(String id) {
        StudentForm studentForm1 = null;
        String sql="";
        if(id==null ||id.equals("")){
            return null;
        }else{
        	sql = "SELECT * FROM tb_student WHERE id='" +id+ "'";
        }
        ResultSet rs = conn.executeQuery(sql);
        try {
            while (rs.next()) {
                studentForm1 = new StudentForm();
                studentForm1.setID(rs.getString(1));
                studentForm1.setName(rs.getString(2));
                studentForm1.setPwd(rs.getString(3));
                studentForm1.setSex(rs.getString(4));
                studentForm1.setJoinTime(java.text.DateFormat.getDateTimeInstance().parse(rs.getString(5)));
                studentForm1.setQuestion(rs.getString(6));
                studentForm1.setAnswer(rs.getString(7));
                studentForm1.setProfession(rs.getString(8));
                studentForm1.setCardNo(rs.getString(9));
            }
        } catch (Exception ex) {}
        return studentForm1;
    }
    //修改考生资料
    public int update(StudentForm s){
        String sql="UPDATE tb_student SET pwd='"+s.getPwd()+"',sex='"+s.getSex()+"',question='"+s.getQuestion()+"',answer='"+s.getAnswer()+"',profession='"+s.getProfession()+"' where ID='"+s.getID()+"'";
        int ret=conn.executeUpdate(sql);
        System.out.println("修改考生资料时的SQL："+sql);
        conn.close();
        return ret;
    }
//找回密码（第一步）
    public StudentForm seekPwd1(StudentForm s){
    	String sql="SELECT * FROM tb_student WHERE ID='"+s.getID()+"'";
        ResultSet rs = conn.executeQuery(sql);
            try {
                if (rs.next()) {
                    s.setID(rs.getString(1));
                    s.setQuestion(rs.getString(6));
                }else{
                s.setID("");
                }
            }catch(Exception e){
            	System.out.println("找回密码（第一步）出现的错误信息："+e.getMessage());
            }
            return s;
    }
//  找回密码（第二步）
    public StudentForm seekPwd2(StudentForm s){
    	String sql="SELECT * FROM tb_student WHERE ID='"+s.getID()+"'";
    	System.out.println("SQL"+sql);
        ResultSet rs = conn.executeQuery(sql);
        try {
            if (rs.next()) {
                	String ID=rs.getString(1);
                	String pwd=rs.getString(3);
                	String answer=rs.getString(7);
                	if(answer.equals(s.getAnswer())){
                		s.setID(ID);
                		s.setPwd(pwd);
                		System.out.println("密码："+pwd);
                	}else{
                		s.setID("");
                	}
                }
            }catch(Exception e){
            	System.out.println("找回密码（第二步）出现的错误信息："+e.getMessage());
            }
            return s;
    }
//    删除数据
        public int delete(StudentForm studentForm) {
        	int flag=0;
        	String[] delId=studentForm.getDelIdArray();
        	if (delId.length>0){
        		String id="'";
        		for(int i=0;i<delId.length;i++){
        			id=id+delId[i]+"','";
        		}
        		id=id.substring(0,id.length()-2);
                String sql = "DELETE FROM tb_student where id in (" + id +")";
                System.out.println("删除时的SQL："+sql);
                flag = conn.executeUpdate(sql);
                conn.close();
        	}else{
        		flag=0;
        	}
            return flag;
        }
}
