package com.wgh.dao;

import com.wgh.actionForm.StudentForm;
import com.wgh.core.ChStr;
import com.wgh.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();	//獲取記錄總數(shù)
                    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;
    }

    //添加數(shù)據(jù)
    public String insert(StudentForm s) {
        String sql1="SELECT * FROM tb_student WHERE cardNo='"+s.getCardNo()+"'";
        ResultSet rs = conn.executeQuery(sql1);							//執(zhí)行SQL查詢語句
        String sql = "";
        String falg = "miss";											//用于記錄返回信息的變量
        String ID="";
            try {
                if (rs.next()) {											//假如存在記錄
                    falg="re";										//表示考生信息已經(jīng)注冊
                } 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;//取出最大準考證號中的數(shù)字編號+1
                		String no=chStr.formatNO(newId,6);				//將生成的編號格式化為6位
                		ID="CN"+newTime+no;						//組合完整的準考證號
                	}else{											//當?shù)谝粋€考生注冊時
                		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();											//關(guān)閉數(shù)據(jù)庫連接
                }
            } 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 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("找回密碼（第一步）出現(xiàn)的錯誤信息："+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("找回密碼（第二步）出現(xiàn)的錯誤信息："+e.getMessage());
            }
            return s;
    }
//    刪除數(shù)據(jù)
        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;
        }
}