package cn.hmsoft.ltc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.NumberFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

import cn.hmsoft.ltc.common.util.DbUtil;
import cn.hmsoft.ltc.model.Subject;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;

/**
 * 题目管理
 * @author xuwenjin
 * @date 2019-03-27
 *
 */
public class SubjectDao {

	/**
	 * 新增课程信息
	 * @param user
	 */
	public Integer insertSubject(Subject subject) throws SQLException {
		
		DbUtil util = new DbUtil();
		
		String sql = "insert into subject(course_id,question_type_id,teachingmterial,ability,difficult,assessment,teacher_id,chapter,joint,directory,pages,answer,score,remark,test_paper_id,ord) "
				+ "values(" + subject.getCourseId() + ", "
				       + subject.getQuestionTypeId() + ", "
				       + "'" + subject.getTeachingmterial() + "', "
				       + "'" + subject.getAbility() + "', "
		    		   + "'" + subject.getDifficult() + "', "
    				   + "'" + subject.getAssessment() + "', "
    				   + subject.getTeacherId() + ", "
    				   + "'" + subject.getChapter() + "', "
    				   + subject.getJoint() + ", "
    				   + subject.getDirectory() + ", "
					   + subject.getPages() + ", "
					   + "'" + subject.getAnswer() + "', "
    				   + subject.getScore() + ", "
    				   + "'" + subject.getRemark() + "', "
    				   + "'" + subject.getTestPaperId() + "',"
						+ subject.getOrd() + ") ";
		//System.out.println(sql);
		return util.insertReturnPK(sql);
	}
	
	
	/**
	 * 修改题目
	 * @param detailTable
	 */
	public void updateSubject(Subject subject) {
		DbUtil util = new DbUtil();
		StringBuilder sql = new StringBuilder();
		sql.append("update subject set ");
		sql.append(" question_type_id=").append(subject.getQuestionTypeId()).append(",");
		sql.append(" teachingmterial=").append("'"+subject.getTeachingmterial()+"'").append(",");
		sql.append(" ability=").append("'"+subject.getAbility()+"'").append(",");
		sql.append(" difficult=").append("'"+subject.getDifficult()+"'").append(",");
		sql.append(" assessment=").append("'"+subject.getAssessment()+"'").append(",");
		sql.append(" teacher_id=").append(subject.getTeacherId()).append(",");
		sql.append(" chapter=").append("'"+subject.getChapter()+"'").append(",");
		sql.append(" joint=").append(subject.getJoint()).append(",");
		sql.append(" directory=").append(subject.getDirectory()).append(",");
		sql.append(" pages=").append(subject.getPages()).append(",");
		sql.append(" remark=").append("'"+subject.getRemark()+"'").append(",");
		sql.append(" score=").append(subject.getScore()).append(",");
		sql.append(" answer=").append("'"+subject.getAnswer()+"'");
		sql.append(" where subject_id=").append(subject.getSubjectId());
		util.update(sql.toString());
	}

	/**
	 *  查询题目信息
	 * @return
	 * @throws SQLException
	 */
	public ObservableList<Subject> selectSubject(Subject subject) throws SQLException {
		ObservableList<Subject> lstSubject = FXCollections.observableArrayList();
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select a.subject_id,a.course_id,a.question_type_id,a.teachingmterial,a.ability,a.difficult,a.assessment,status,");
		strSql.append("a.teacher_id,a.chapter,a.joint,a.directory,a.pages,a.answer,a.score,a.remark,a.test_paper_id,a.ord,b.name ");
		strSql.append("from subject a 	");
		strSql.append("left join question_type b on a.question_type_id = b.id ");
		strSql.append("left join course_question cq on (cq.courseId=a.course_id and cq.quesTypeId=a.question_type_id) ");
		strSql.append(" where 1=1 ");
		if(subject.getSubjectId().intValue() > 0) {
			strSql.append(" and subject_id = " + subject.getSubjectId());
		}
		if(StringUtils.isNotBlank(subject.getTestPaperId())) {
			strSql.append(" and test_paper_id = '" + subject.getTestPaperId() + "'");
		}
		if(subject.getQuestionTypeId().intValue() > 0) {
			strSql.append(" and question_type_id = " + subject.getQuestionTypeId());
		}
		if(StringUtils.isNotBlank(subject.getDifficult())) {
			strSql.append(" and difficult = '" + subject.getDifficult()+"'");
		}
		if(StringUtils.isNotBlank(subject.getAssessment())) {
			strSql.append(" and assessment = '" + subject.getAssessment()+"'");
		}
		//关键字查询，关键字 前端传入 存储到 remark 字段中。
		if(StringUtils.isNotBlank(subject.getRemark())) {
			strSql.append(" and (a.remark like '%" + subject.getRemark()+"%')");
		}
		strSql.append(" order by cq.ord");
		ResultSet rs = util.query(strSql.toString());
		NumberFormat format = NumberFormat.getInstance();
		while (rs.next()) {
			Subject sub = new Subject();
			sub.setSubjectId(rs.getInt(1));
			sub.setCourseId(rs.getInt(2));
			sub.setQuestionTypeId(rs.getInt(3));
			sub.setTeachingmterial(rs.getString(4));
			sub.setAbility(rs.getString(5));
			sub.setDifficult(rs.getString(6));
			sub.setAssessment(rs.getString(7));
			sub.setStatus(rs.getString(8));
			sub.setTeacherId(rs.getInt(9));
			sub.setChapter(rs.getString(10));
			sub.setJoint(rs.getInt(11));
			sub.setDirectory(rs.getInt(12));
			sub.setPages(rs.getInt(13));
			sub.setAnswer(rs.getString(14));
			sub.setScore(rs.getDouble(15));
			sub.setRemark(rs.getString(16));
			sub.setTestPaperId(rs.getString(17));
			sub.setOrd(rs.getInt(18));
			sub.setQuestionTypeName(rs.getString(19));
			sub.setScoreStr(format.format(sub.getScore()));
			lstSubject.add(sub);
		}
		rs.close();
		return lstSubject;
	}
	
	/**
	 * 根据ID删除题目信息
	 * @param id
	 * @return true 成功，false 失败
	 * @throws SQLException
	 */
	public boolean deleteSubject(int id)throws SQLException{
		boolean flag = false;
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("delete from subject where subject_id = "+id+" ");
		int count = util.delete(strSql.toString());
		if(count>0) {
			flag = true;; //成功
		}
		return flag;
	}
	
	public boolean deleteSubjectByPageNo(String testPaperId, Integer typeId)throws SQLException{
		boolean flag = false;
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("delete from subject where test_paper_id = '" + testPaperId + "' ");
		if (typeId != null) {
			strSql.append(" and question_type_id=").append(typeId);
		}
		int count = util.delete(strSql.toString());
		if(count > 0) {
			flag = true;; //成功
		}
		return flag;
	}
	
	public List<Subject> findSubjectList(String testPaperId, Integer typeId) throws SQLException {
		List<Subject> subList = new ArrayList<Subject>();
		DbUtil util = new DbUtil();
		String sql = "select * from subject where test_paper_id='" + testPaperId + "'";
		if (typeId != null) {
			sql = sql + " and question_type_id=" + typeId;
		}
		sql += " order by question_type_id,ord";
		ResultSet rs = util.query(sql);
		while (rs.next()) {
			Subject subject = new Subject();
			subject.setSubjectId(rs.getInt(1));
			subject.setCourseId(rs.getInt(2));
			subject.setQuestionTypeId(rs.getInt(3));
			subject.setTeachingmterial(rs.getString(4));
			subject.setAbility(rs.getString(5));
			subject.setDifficult(rs.getString(6));
			subject.setAssessment(rs.getString(7));
			subject.setStatus(rs.getString(8));
			subject.setTeacherId(rs.getInt(9));
			subject.setChapter(rs.getString(10));
			subject.setJoint(rs.getInt(11));
			subject.setDirectory(rs.getInt(12));
			subject.setPages(rs.getInt(13));
			subject.setAnswer(rs.getString(14));
			subject.setScore(rs.getDouble(15));
			subject.setRemark(rs.getString(16));
			subject.setTestPaperId(rs.getString(17));
			subject.setOrd(rs.getInt(18));
			subList.add(subject);
		}
		return subList;
	}

	public Map<String, Integer> findSubjectChapterDist(String paperNo) throws SQLException {
		DbUtil util = new DbUtil();
		Map<String, Integer> map = new HashMap<String, Integer>();
		String sql = "select question_type_id,chapter,count(1) from subject where test_paper_id='" + paperNo + "'";
		sql += " group by test_paper_id,question_type_id,chapter";
		ResultSet rs = util.query(sql);
		while (rs.next()) {
			String key = rs.getInt(1) + "_" + rs.getInt(2);
			map.put(key, rs.getInt(3));
		}
		return map;
	}

	public int findSubjectScoreCount(String paperNo, Integer typeId) throws SQLException {
		DbUtil util = new DbUtil();
		String sql = "select count(distinct score) from subject where test_paper_id='" +paperNo+ "' and question_type_id=" +typeId+ " group by score";
		ResultSet rs = util.query(sql);
		while (rs.next()) {
			return rs.getInt(1);
		}
		return 0;
	}
	
	//修改这一类题型的 小题分标志为1
	public void updateSubjectByParam(String paperNo, Integer typeId) {
		DbUtil util = new DbUtil();
		String sql = "update subject set tiny_score_flag=1 where test_paper_id='" + paperNo + "' and question_type_id=" + typeId;
		util.update(sql);
	}
	
	/**
	 *  查询题目预估难度信息
	 * @return
	 * @throws SQLException
	 */
	public ObservableList<Subject> selectEstimatedDifficulty(Subject subject) throws SQLException {
		ObservableList<Subject> lstSubject = FXCollections.observableArrayList();
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select a.subject_id,a.course_id,a.question_type_id,a.teachingmterial,a.ability,a.difficult,a.assessment,status,");
		strSql.append("a.teacher_id,case when a.difficult ='易' then ' (0.7 - 1) '  ");
		strSql.append(" when a.difficult ='中等偏易' then ' (0.5 - 0.7) '  ");
		strSql.append(" when a.difficult ='中等偏难' then ' (0.3 - 0.5) '  ");
		strSql.append(" when a.difficult ='难' then ' (0 - 0.3) '  ");
		strSql.append(" end as chapter, ");
		strSql.append(" a.joint,a.directory,a.pages,a.answer,a.score,");
		strSql.append(" case when a.difficult ='易' then ' 0.85 '  ");
		strSql.append(" when a.difficult ='中等偏易' then ' 0.6 ' ");
		strSql.append(" when a.difficult ='中等偏难' then ' 0.4 ' ");
		strSql.append(" when a.difficult ='难' then ' 0.15 ' ");
		strSql.append(" end as remark,a.test_paper_id,a.ord,b.name ");
		strSql.append("from subject a 	");
		strSql.append("left join question_type b on a.question_type_id = b.id ");
		strSql.append("left join course_question cq on (cq.courseId=a.course_id and cq.quesTypeId=a.question_type_id) ");
		strSql.append(" where 1=1 ");
		if(subject.getSubjectId().intValue() > 0) {
			strSql.append(" and subject_id = " + subject.getSubjectId());
		}
		if(StringUtils.isNotBlank(subject.getTestPaperId())) {
			strSql.append(" and test_paper_id = '" + subject.getTestPaperId() + "'");
		}
		if(subject.getQuestionTypeId().intValue() > 0) {
			strSql.append(" and question_type_id = " + subject.getQuestionTypeId());
		}
		if(StringUtils.isNotBlank(subject.getDifficult())) {
			strSql.append(" and difficult = '" + subject.getDifficult()+"'");
		}
		if(StringUtils.isNotBlank(subject.getAssessment())) {
			strSql.append(" and assessment = '" + subject.getAssessment()+"'");
		}
		strSql.append(" order by cq.ord");
		ResultSet rs = util.query(strSql.toString());
		NumberFormat format = NumberFormat.getInstance();
		while (rs.next()) {
			Subject sub = new Subject();
			sub.setSubjectId(rs.getInt(1));
			sub.setCourseId(rs.getInt(2));
			sub.setQuestionTypeId(rs.getInt(3));
			sub.setTeachingmterial(rs.getString(4));
			sub.setAbility(rs.getString(5));
			sub.setDifficult(rs.getString(6));
			sub.setAssessment(rs.getString(7));
			sub.setStatus(rs.getString(8));
			sub.setTeacherId(rs.getInt(9));
			sub.setChapter(rs.getString(10));
			sub.setJoint(rs.getInt(11));
			sub.setDirectory(rs.getInt(12));
			sub.setPages(rs.getInt(13));
			sub.setAnswer(rs.getString(14));
			sub.setScore(rs.getDouble(15));
			sub.setRemark(rs.getString(16));
			sub.setTestPaperId(rs.getString(17));
			sub.setOrd(rs.getInt(18));
			sub.setQuestionTypeName(rs.getString(19));
			sub.setScoreStr(format.format(sub.getScore()));
			lstSubject.add(sub);
		}
		rs.close();
		return lstSubject;
	}
}
