package com.mingrisoft.dao;

import com.mingrisoft.actionForm.QuestionsForm;
import com.mingrisoft.core.ConnDB;
import java.sql.*;
import java.util.*;

public class QuestionsDAO {
	private ConnDB conn=new ConnDB();
    //添加数据
    public int insert(QuestionsForm q) {
        String sql1="SELECT * FROM tb_questions WHERE subject='"+q.getSubject()+"' AND taoTiId="+q.getTaoTiId()+"";
        ResultSet rs = conn.executeQuery(sql1);
        System.out.println("添加时的查询"+sql1);
        String sql = "";
        String answer="";
        int falg = 0;
            try {
                if (rs.next()) {
                    falg=2;
                } else {
                	if(q.getType().equals("多选题")){
	            		String[] mOption=(String [])q.getAnswerArr();
	                	if (mOption.length>0){
	                		String str="";
	                		for(int i=0;i<mOption.length;i++){
	                			str=str+mOption[i]+",";
	                		}
	                		str=str.substring(0,str.length()-1);
	                		answer=str;
	                	}
                	}else{
                		answer=q.getAnswer();
                	}
                    sql = "INSERT INTO tb_questions (subject,type,lessonId,taoTiId,optionA,optionB,optionC,optionD,answer,note) values('" +
                                 q.getSubject() + "','"+q.getType()+"',"+q.getLessonId()+","+q.getTaoTiId()+",'"+q.getOptionA()+"','"+q.getOptionB()+"','"+q.getOptionC()+"','"+q.getOptionD()+"','"+answer+"','"+q.getNote()+"')";
                    falg = conn.executeUpdate(sql);
                    System.out.println("添加考试题目时的SQL：" + sql);
                    conn.close();
                }
            } catch (Exception ex) {
                falg=0;
            }finally{
            	conn.close();
            }
        return falg;
    }
    //查询方法
    public List query(int id) {
    	List questionsList = new ArrayList();							//初始化List集合的实例
        QuestionsForm questionsForm1 = null; 						//声明QuestionsForm类的对象
        String sql="";
        if(id==0){
            sql = "SELECT * FROM tb_questions ORDER BY lessonId DESC,taoTiId DESC,type";	//查询全部数据
        }else{
        	sql = "SELECT * FROM tb_questions WHERE id=" +id+ "";	//修改时应用的查询语句
        }
        ResultSet rs = conn.executeQuery(sql);							//执行查询语句
        String type="";
        String answer="";
        try {													//捕捉异常
            while (rs.next()) {
                questionsForm1 = new QuestionsForm();				//实例化QuestionsForm类的对象
                questionsForm1.setID(rs.getInt(1));
                questionsForm1.setSubject(rs.getString(2));
                type=rs.getString(3);
                questionsForm1.setType(type);
                questionsForm1.setJoinTime(java.text.DateFormat.getDateTimeInstance()
                		.parse(rs.getString(4)));               
                questionsForm1.setLessonId(rs.getInt(5));
                questionsForm1.setTaoTiId(rs.getInt(6));
                questionsForm1.setOptionA(rs.getString(7));
                questionsForm1.setOptionB(rs.getString(8));
                questionsForm1.setOptionC(rs.getString(9));
                questionsForm1.setOptionD(rs.getString(10));
                if(type.equals("多选题")){
    				String[] ans=rs.getString(11).split(",");			//将获取的正确答案分割为数组
                	questionsForm1.setAnswerArr(ans);				//设置answerArr属性
                }else{
                	questionsForm1.setAnswer(rs.getString(11));		//设置answerArr属性
                }
                questionsForm1.setNote(rs.getString(12));				//获取并设置备注属性
                questionsList.add(questionsForm1);					//将查询结果保存到List集合中
            }
        } catch (Exception e) {
        	e.printStackTrace();
        }finally{
        	conn.close();
        }
        return questionsList;
    }

    //修改数据
    public int update(QuestionsForm q){
    	String answer="";
       	if(q.getType().equals("多选题")){
    		String[] mOption=(String [])q.getAnswerArr();
        	if (mOption.length>0){
        		String str="";
        		for(int i=0;i<mOption.length;i++){
        			str=str+mOption[i]+",";
        		}
        		str=str.substring(0,str.length()-1);
        		answer=str;
        	}
    	}else{
    		answer=q.getAnswer();
    	}
        String sql="UPDATE tb_questions SET subject='"+q.getSubject()+"',type='"+q.getType()+"',optionA='"+q.getOptionA()+"',optionB='"+q.getOptionB()+"',optionC='"+q.getOptionC()+"',optionD='"+q.getOptionD()+"',answer='"+answer+"',note='"+q.getNote()+"' where id="+q.getID()+"";
        int ret=conn.executeUpdate(sql);
        System.out.println("修改考试题目时的SQL："+sql);
        conn.close();
        return ret;
    }
    //根据所属套题查询套题名称(通过jsp:useBean调用)
    public String getTaoTi(int id){
    	String taoTiName="";
    	if(id>0){
    		String sql="SELECT * FROM tb_taoTi WHERE id="+id+"";
    		ResultSet rs=conn.executeQuery(sql);
            try {
                if(rs.next()) {
                	taoTiName=rs.getString(2);
                }
            }  catch (Exception e) {
            	e.printStackTrace();
            }finally{
            	conn.close();
            }  		
    	}
    	return taoTiName;
    }
//    删除数据
    public int delete(QuestionsForm questionsForm) {
    	int flag=0;
    	String[] delId=questionsForm.getDelIdArray();		//获取删除记录的ID保存到字符串数组中
    	if (delId.length>0){
    		String id="";
    		for(int i=0;i<delId.length;i++){
    			id=id+delId[i]+",";						//将获取的ID连接成由逗号分隔的字符串
    		}
    		id=id.substring(0,id.length()-1); 								//去掉生成字符串中的最后一个逗号
            String sql = "DELETE FROM tb_questions where id in (" + id +")";	//批量删除记录
            flag = conn.executeUpdate(sql);								//执行更新操作
            conn.close();												//关闭数据库连接
    	}else{
    		flag=0;
    	}
        return flag;
    }

}
