ExamDao

package com.iflytek.edu.zx.exammng.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import com.iflytek.edu.elp.common.dto.page.PageParam;
import com.iflytek.edu.elp.common.util.DaoUtil;
import com.iflytek.edu.zx.exammng.model.Exam;
import com.iflytek.edu.zx.exammng.model.ExamStatus;
import com.iflytek.edu.zx.exammng.model.agent.Grade;
import com.iflytek.edu.zx.exammng.model.agent.Phase;
import com.iflytek.edu.zx.exammng.model.agent.User;

/**
 * 考试Dao.
 * 
 * @author znyin
 * 
 */
@Repository("examDao")
public class ExamDao {

	/**
	 * NamedParameterJdbcTemplate
	 */
	@Resource
	private NamedParameterJdbcTemplate namedParameterJdbcTemplateExammng;

	/**
	 * JdbcTemplate
	 */
	@Resource
	private JdbcTemplate jdbcTemplateExammng;

	/**
	 * 插入。
	 * 
	 * @param exam
	 *            考试。
	 */
	public void insert(Exam exam) {
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into exammng_exam (id,name,phaseCode,phaseName,gradeCode,");
		sql.append("	gradeName,examTime,creatorId,createTime,isDelete)");
		sql.append(" values (:id,:name,:phaseCode,:phaseName,:gradeCode,");
		sql.append("	:gradeName,:examTime,:creatorId,:createTime,:isDelete)");

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("id", exam.getId());
		paramMap.put("name", exam.getName());
		paramMap.put("phaseCode", exam.getGrade().getCode());
		paramMap.put("phaseName", exam.getGrade().getName());
		paramMap.put("gradeCode", exam.getGrade().getCode());
		paramMap.put("gradeName", exam.getGrade().getName());
		paramMap.put("examTime", exam.getExamTime());
		paramMap.put("creatorId", exam.getCreator().getId());
		paramMap.put("createTime", exam.getCreateTime());
		paramMap.put("isDelete", false);

		namedParameterJdbcTemplateExammng.update(sql.toString(), paramMap);
	}

	/**
	 * 更新。
	 * <p>
	 * 只会更新name、examTime。
	 * 
	 * @param examId
	 *            考试Id
	 * @param name
	 *            考试名称
	 * @param examTime
	 *            考试时间
	 */
	public void update(String examId, String name, Date examTime) {
		String sql = "update exammng_exam set name=?,examTime=? where id=?";
		jdbcTemplateExammng.update(sql, name, examTime, examId);
	}

	/**
	 * 查询一考试。
	 * 
	 * @param examId
	 *            考试Id
	 * @return 考试
	 */
	public Exam find(String examId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select id,name,phaseCode,phaseName,gradeCode,");
		sql.append("	gradeName,examTime,creatorId,status,createTime");
		sql.append(" from exammng_exam where id=?");

		Exam exam = jdbcTemplateExammng.queryForObject(sql.toString(), new Object[] { examId }, new ExamMapper());
		return exam;
	}

	/**
	 * 查询某次考试的创建人编码。
	 * 
	 * @param examId
	 *            考试Id
	 * @return 创建人编码
	 */
	public String findCreatorCode(String examId) {
		String sql = "select creatorId from exammng_exam where id=?";
		return jdbcTemplateExammng.queryForObject(sql, new Object[] { examId }, String.class);
	}

	/**
	 * 查询某次考试的状态。
	 * 
	 * @param examId
	 *            考试Id
	 * @return 考试状态
	 */
	public ExamStatus findStatus(String examId) {
		String sql = "select status from exammng_exam where id=?";
		String status = jdbcTemplateExammng.queryForObject(sql, new Object[] { examId }, String.class);
		return ExamStatus.valueOf(status);
	}

	/**
	 * 查询考试列表。
	 * <p>
	 * 
	 * <pre>
	 * 物理分页。
	 * 
	 * 考试的id、name、grade、examTime会填充。
	 * 
	 * 默人按createTime降序排列。
	 * </pre>
	 * 
	 * @param creatorId
	 *            创建人Id。
	 * @param statusList
	 *            考试状态列表。
	 * @param pageParam
	 *            分页参数
	 * @return 考试
	 */
	public List<Exam> findExams(String creatorId, List<ExamStatus> statusList, PageParam pageParam) {
		StringBuilder strStatus=new StringBuilder();
		strStatus.append("(");
		for (int i=0;i<statusList.size();i++) {
			strStatus.append("'");
			strStatus.append(statusList.get(i).name());
			strStatus.append("'");
			if(i<statusList.size()-1){
				strStatus.append(",");
			}
		}
		strStatus.append(")");
		
		StringBuilder sql = new StringBuilder();
		sql.append(" select id,name,gradeCode,gradeName,examTime");
		sql.append(" from exammng_exam");
		sql.append(" where isDelete=false and creatorId=? and status in ");
		sql.append(strStatus.toString());

		if (!StringUtils.isEmpty(pageParam.getOrderBy())) {
			sql.append(" order by ").append(pageParam.getOrderBy());
			sql.append(pageParam.getOrderDirection() == null ? "ASC" : pageParam.getOrderDirection().name());
		} else {
			sql.append(" order by createTime DESC");
		}

		sql.append(" limit ?,?");
		
		Object[] params = new Object[] { creatorId, pageParam.getStartIndex(), pageParam.getPageSize() };
		return jdbcTemplateExammng.query(sql.toString(), params, new ExamMapper());
	}

	/**
	 * 查询考试数量。
	 * 
	 * @param creatorId
	 *            创建人Id.
	 * @param statusList
	 *            考试状态列表。
	 * @return 考试数量
	 */
	public int findCount(String creatorId, List<ExamStatus> statusList) {
		String sql = "select count(1) from exammng_exam where isDelete=false and creatorId=? and status in ";
		StringBuilder strStatus=new StringBuilder();
		strStatus.append("(");
		for (int i=0;i<statusList.size();i++) {
			strStatus.append("'");
			strStatus.append(statusList.get(i).name());
			strStatus.append("'");
			if(i<statusList.size()-1){
				strStatus.append(",");
			}
		}
		strStatus.append(")");
		sql=sql+strStatus.toString();
		return jdbcTemplateExammng.queryForObject(sql, new Object[] { creatorId }, Integer.class);
	}

	/**
	 * 更新考试状态。
	 * 
	 * @param examId
	 *            考试Id
	 * @param status
	 *            考试状态。
	 */
	public void updateStatus(String examId, ExamStatus status) {
		String sql = "update exammng_exam set status=? where id=?";
		jdbcTemplateExammng.update(sql, status.name(), examId);
	}

	/**
	 * 更新删除标识。
	 * 
	 * @param examId
	 *            考试Id
	 * @param isDelete
	 *            删除标识，true：删除，false：未删除。
	 */
	public void updateIsDelete(String examId, boolean isDelete) {
		String sql = "update exammng_exam set isDelete=? where id=?";
		jdbcTemplateExammng.update(sql, isDelete, examId);
	}

	/**
	 * 
	 * @author znyin
	 * 
	 */
	class ExamMapper implements RowMapper<Exam> {
		@Override
		public Exam mapRow(ResultSet rs, int rowNum) throws SQLException {
			Exam exam = new Exam();
			if (DaoUtil.isColumnExist(rs, "createTime")) {
				exam.setCreateTime(rs.getDate("createTime"));
			}
			if (DaoUtil.isColumnExist(rs, "creatorId")) {
				User creator = new User();
				creator.setId(rs.getString("creatorId"));
				exam.setCreator(creator);
			}
			if (DaoUtil.isColumnExist(rs, "examTime")) {
				exam.setExamTime(rs.getDate("examTime"));
			}
			if (DaoUtil.isColumnExist(rs, "gradeCode")) {
				Grade grade = new Grade();
				grade.setCode(rs.getString("gradeCode"));
				grade.setName(rs.getString("gradeName"));
				exam.setGrade(grade);
			}
			if (DaoUtil.isColumnExist(rs, "id")) {
				exam.setId(rs.getString("id"));
			}
			if (DaoUtil.isColumnExist(rs, "name")) {
				exam.setName(rs.getString("name"));
			}
			if (DaoUtil.isColumnExist(rs, "phaseCode")) {
				Phase phase = new Phase();
				phase.setCode(rs.getString("phaseCode"));
				phase.setName(rs.getString("phaseName"));
				exam.setPhase(phase);
			}
			if (DaoUtil.isColumnExist(rs, "phaseCode")) {
				exam.setStatus(ExamStatus.valueOf(rs.getString("status")));
			}
			return exam;
		}
	}

}