package cn.hmsoft.ltc.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import cn.hmsoft.ltc.common.util.DateUtil;
import cn.hmsoft.ltc.common.util.DbUtil;
import cn.hmsoft.ltc.model.DetailTable;

/**
 * 细目表dao
 * @author zq
 *
 */
public class DetailTableDao {

	/**
	 * 新增细目表信息.
	 * @param user
	 */
	public void insertDetailTable(DetailTable detailTable) {
		DbUtil util = new DbUtil();
		util.insert("insert into detail_table(paperNo,courseId,questionTypeId,courseCode,courseName,itemScore,createdTime,createdBy,tx0Nums,tx1Nums,tx2Nums,tx3Nums,tx4Nums,tx5Nums,tx6Nums,tx7Nums,tx8Nums,tx9Nums "
					+ ",tx10Nums,tx11Nums,tx12Nums,tx13Nums,tx14Nums,tx15Nums,tx16Nums,tx17Nums,tx18Nums,tx19Nums,tx20Nums,tx21Nums,tx22Nums,tx23Nums,tx24Nums,tx25Nums) values('"+detailTable.getPaperNo() + "', "
					   + detailTable.getCourseId() + ", "
				       + detailTable.getQuestionTypeId() + ", "
				       + "'" + detailTable.getCourseCode() + "', "
				       + "'" + detailTable.getCourseName() + "', "
				       + detailTable.getItemScore() + ", "
				       +  "'" + DateUtil.formatToString(new Date()) + "', "
				       + detailTable.getCreateBy() +"," 
				       + detailTable.getTx0Nums() +"," 
				       + detailTable.getTx1Nums() +"," 
				       + detailTable.getTx2Nums() +"," 
				       + detailTable.getTx3Nums() +"," 
				       + detailTable.getTx4Nums() +"," 
				       + detailTable.getTx5Nums() +"," 
				       + detailTable.getTx6Nums() +"," 
				       + detailTable.getTx7Nums() +"," 
				       + detailTable.getTx8Nums() +"," 
				       + detailTable.getTx9Nums() +"," 
				       + detailTable.getTx10Nums() +"," 
				       + detailTable.getTx11Nums() +"," 
				       + detailTable.getTx12Nums() +"," 
				       + detailTable.getTx13Nums() +"," 
				       + detailTable.getTx14Nums() +"," 
				       + detailTable.getTx15Nums() +"," 
				       + detailTable.getTx16Nums() +"," 
				       + detailTable.getTx17Nums() +"," 
				       + detailTable.getTx18Nums() +"," 
				       + detailTable.getTx19Nums() +"," 
				       + detailTable.getTx20Nums() +"," 
				       + detailTable.getTx21Nums() +"," 
				       + detailTable.getTx22Nums() +"," 
				       + detailTable.getTx23Nums() +"," 
				       + detailTable.getTx24Nums() +"," 
				       + detailTable.getTx25Nums() +")");
	}
	
	/**
	 * 修改细目表.
	 * @param detailTable
	 */
	public void updateDetailTable(DetailTable detailTable) {
		DbUtil util = new DbUtil();
		StringBuilder sql = new StringBuilder();
		sql.append("update detail_table set ");
		sql.append(" questionTypeId=").append(detailTable.getQuestionTypeId()).append(",");
		sql.append(" itemScore=").append(detailTable.getItemScore()).append(",");
		sql.append(" tx0Nums=").append(detailTable.getTx0Nums()).append(",");
		sql.append(" tx1Nums=").append(detailTable.getTx1Nums()).append(",");
		sql.append(" tx2Nums=").append(detailTable.getTx2Nums()).append(",");
		sql.append(" tx3Nums=").append(detailTable.getTx3Nums()).append(",");
		sql.append(" tx4Nums=").append(detailTable.getTx4Nums()).append(",");
		sql.append(" tx5Nums=").append(detailTable.getTx5Nums()).append(",");
		sql.append(" tx6Nums=").append(detailTable.getTx6Nums()).append(",");
		sql.append(" tx7Nums=").append(detailTable.getTx7Nums()).append(",");
		sql.append(" tx8Nums=").append(detailTable.getTx8Nums()).append(",");
		sql.append(" tx9Nums=").append(detailTable.getTx9Nums()).append(",");
		sql.append(" tx10Nums=").append(detailTable.getTx10Nums()).append(",");
		sql.append(" tx11Nums=").append(detailTable.getTx11Nums()).append(",");
		sql.append(" tx12Nums=").append(detailTable.getTx12Nums()).append(",");
		sql.append(" tx13Nums=").append(detailTable.getTx13Nums()).append(",");
		sql.append(" tx14Nums=").append(detailTable.getTx14Nums()).append(",");
		sql.append(" tx15Nums=").append(detailTable.getTx15Nums()).append(",");
		sql.append(" tx16Nums=").append(detailTable.getTx16Nums()).append(",");
		sql.append(" tx17Nums=").append(detailTable.getTx17Nums()).append(",");
		sql.append(" tx18Nums=").append(detailTable.getTx18Nums()).append(",");
		sql.append(" tx19Nums=").append(detailTable.getTx19Nums()).append(",");
		sql.append(" tx20Nums=").append(detailTable.getTx20Nums()).append(",");
		sql.append(" tx21Nums=").append(detailTable.getTx21Nums()).append(",");
		sql.append(" tx22Nums=").append(detailTable.getTx22Nums()).append(",");
		sql.append(" tx23Nums=").append(detailTable.getTx23Nums()).append(",");
		sql.append(" tx24Nums=").append(detailTable.getTx24Nums()).append(",");
		sql.append(" tx25Nums=").append(detailTable.getTx25Nums());
		sql.append(" where id=").append(detailTable.getId());
		util.update(sql.toString());
	}
	
	/**
	 *  查询细目表信息.
	 *  @param courseCode 课程编号
	 * @return
	 * @throws SQLException
	 */
	public List<DetailTable> selectDetailTable(String paperNo) throws SQLException {
		List<DetailTable> lstDetailTable = new ArrayList<DetailTable>();
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select t.id,t.paperNo,t.courseId,t.questionTypeId,t.itemScore,t.createdTime,t.createdBy,t.updatedTime,t.updatedBy,t.tx0Nums,");
		strSql.append("t.tx1Nums,t.tx2Nums,t.tx3Nums,t.tx4Nums,t.tx5Nums,t.tx6Nums,t.tx7Nums,t.tx8Nums,t.tx9Nums,t.tx10Nums,t.tx11Nums,t.tx12Nums,");
		strSql.append("t.tx13Nums,t.tx14Nums,t.tx15Nums,t.tx16Nums,t.tx17Nums,t.tx18Nums,t.tx19Nums,t.tx20Nums,t.tx21Nums,t.tx22Nums,t.tx23Nums,t.tx24Nums,t.tx25Nums,");
		strSql.append("q.name questionTypeName from detail_table t,question_type q,course_question cq ");
		strSql.append("where t.questionTypeId=q.id and t.courseId=cq.courseId and t.questionTypeId=cq.quesTypeId and t.paperNo = '" + paperNo + "' ");
		strSql.append("order by cq.ord									 ");
		ResultSet rs = util.query(strSql.toString());
		while (rs.next()) {
			DetailTable detailTable = new DetailTable();
			detailTable.setId(rs.getInt(1));
			detailTable.setPaperNo(rs.getString(2));
			detailTable.setCourseId(rs.getInt(3));
			detailTable.setQuestionTypeId(rs.getInt(4));
			detailTable.setItemScore(rs.getDouble(5));
			detailTable.setCreateTime(rs.getString(6));
			detailTable.setCreateBy(rs.getInt(7));
			detailTable.setUpdateTime(rs.getString(8));
			detailTable.setUpdateBy(rs.getInt(9));
			detailTable.setTx0Nums(rs.getInt(10));
			detailTable.setTx1Nums(rs.getInt(11));
			detailTable.setTx2Nums(rs.getInt(12));
			detailTable.setTx3Nums(rs.getInt(13));
			detailTable.setTx4Nums(rs.getInt(14));
			detailTable.setTx5Nums(rs.getInt(15));
			detailTable.setTx6Nums(rs.getInt(16));
			detailTable.setTx7Nums(rs.getInt(17));
			detailTable.setTx8Nums(rs.getInt(18));
			detailTable.setTx9Nums(rs.getInt(19));
			detailTable.setTx10Nums(rs.getInt(20));
			detailTable.setTx11Nums(rs.getInt(21));
			detailTable.setTx12Nums(rs.getInt(22));
			detailTable.setTx13Nums(rs.getInt(23));
			detailTable.setTx14Nums(rs.getInt(24));
			detailTable.setTx15Nums(rs.getInt(25));
			detailTable.setTx16Nums(rs.getInt(26));
			detailTable.setTx17Nums(rs.getInt(27));
			detailTable.setTx18Nums(rs.getInt(28));
			detailTable.setTx19Nums(rs.getInt(29));
			detailTable.setTx20Nums(rs.getInt(30));
			detailTable.setTx21Nums(rs.getInt(31));
			detailTable.setTx22Nums(rs.getInt(32));
			detailTable.setTx23Nums(rs.getInt(33));
			detailTable.setTx24Nums(rs.getInt(34));
			detailTable.setTx25Nums(rs.getInt(35));
			detailTable.setQuestionTypeName(rs.getString(36));
			lstDetailTable.add(detailTable);
		}
		return lstDetailTable;
	}
	
	/**
	 * 通过课程id，查询试卷是否生成细目，如果数据为0，则未生成细目表，如果>1则生成了试卷表
	 * @param courseId
	 * @return
	 * @throws SQLException
	 */
	public int checkDetailTable(Integer courseId) throws SQLException {
		DbUtil util=new DbUtil();
		StringBuffer strSql = new StringBuffer();
		strSql.append("select count(*) from detail_table where courseId = "+courseId).toString();
		ResultSet rs = util.query(strSql.toString());
		int count = 0;
		while (rs.next()) {
			count = rs.getInt(1);
			System.out.println("课程id:" + courseId+" ;count = "+rs.getInt(1));
		}
		return count;
	}
	
}
