AnswerSheetDao

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

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
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.apache.log4j.Logger;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.google.common.collect.Lists;
import com.iflytek.edu.elp.common.dto.page.PageParam;
import com.iflytek.edu.elp.common.util.DaoUtil;
import com.iflytek.edu.elp.common.util.JSONUtils;
import com.iflytek.edu.zx.answersheet.model.AnswersheetConfig;
import com.iflytek.edu.zx.answersheet.model.AnswersheetSource;
import com.iflytek.edu.zx.answersheet.model.Desc;
import com.iflytek.edu.zx.answersheet.model.agent.Grade;
import com.iflytek.edu.zx.answersheet.model.agent.Paper;
import com.iflytek.edu.zx.answersheet.model.agent.Subject;
import com.iflytek.edu.zx.answersheet.model.agent.User;

/**
 * 答题卡Dao。
 * 
 * @author mt
 */
@Repository("answerSheetDao")
public class AnswerSheetDao {

	/**
	 * NamedParameterJdbcTemplate。
	 */
	@Resource
	private NamedParameterJdbcTemplate namedParameterJdbcTemplateAnswersheet;

	/**
	 * JdbcTemplate。
	 */
	@Resource
	private JdbcTemplate jdbcTemplateAnswersheet;

	/**
	 * 日志记录。
	 */
	private Logger log = Logger.getLogger(getClass());

	/**
	 * 获取日志对象。
	 * 
	 * @return 日志
	 */
	protected Logger getLog() {
		return this.log;
	}

	/**
	 * 插入。
	 * 
	 * @param answersheetConfig
	 *            答题卡
	 */
	public void insert(AnswersheetConfig answersheetConfig) {
		StringBuilder sql = new StringBuilder();
		sql.append(" insert into answersheet_config (uuid,locationJson,topicSetName,topicSetUid,createDatetime,createUserId,updateDatetime,");
		sql.append("updateUserId,subjectName,gradeName,scanMode,stuCount,frontJson,html,subjectCode,isDelete,isUsed,source)");
		sql.append(" values (:uuid,:locationJson,:topicSetName,:topicSetUid,:createDatetime,:createUserId,:updateDatetime,:updateUserId,:subjectName,");
		sql.append(":gradeName,:scanMode,:stuCount,:frontJson,:html,:subjectCode,:isDelete,:isUsed,:source)");

		Map<String, Object> paramMap = new HashMap<String, Object>();
		paramMap.put("uuid", answersheetConfig.getId());
		paramMap.put("locationJson", answersheetConfig.getLocationJson());
		paramMap.put("topicSetName", answersheetConfig.getPaper().getName());
		paramMap.put("topicSetUid", answersheetConfig.getPaper().getId());
		paramMap.put("createDatetime", answersheetConfig.getCreateTime());
		paramMap.put("createUserId", answersheetConfig.getCreator().getId());
		paramMap.put("updateDatetime", answersheetConfig.getUpdateTime());
		paramMap.put("updateUserId", answersheetConfig.getUpdator().getId());
		paramMap.put("subjectName", answersheetConfig.getSubject() == null ? null : answersheetConfig.getSubject().getName());
		paramMap.put("gradeName", answersheetConfig.getGrade() == null ? null : answersheetConfig.getGrade().getName());
		paramMap.put("scanMode", null);
		paramMap.put("stuCount", null);
		String forntJson = JSONUtils.toJSONString(answersheetConfig.getDesc());
		paramMap.put("frontJson", forntJson);
		paramMap.put("html", answersheetConfig.getHtml());
		paramMap.put("subjectCode", answersheetConfig.getSubject() == null ? null : answersheetConfig.getSubject().getCode());
		paramMap.put("isDelete", answersheetConfig.getIsDelete());
		paramMap.put("isUsed", answersheetConfig.getIsUsed());
		paramMap.put("source", answersheetConfig.getSource().name());

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

	/**
	 * 更新。
	 * <p>
	 * 只会更新以下字段:
	 * 
	 * @param uuid
	 *            答题卡id
	 * @param updatorId
	 *            更新人id
	 * @param locationJson
	 *            定位点json
	 * @param desc
	 *            答题卡描述
	 * @param html
	 *            答题卡html
	 */
	public void update(String uuid, String updatorId, String locationJson, Desc desc, String html) {
		String sql = "update answersheet_config set updateDatetime=?, updateUserId=?,locationJson=?,frontJson=?,html=? where uuid=?";
		jdbcTemplateAnswersheet.update(sql, new Date(), updatorId, locationJson, JSONUtils.toJSONString(desc), html, uuid);
	}

	/**
	 * 根据id获取答题卡。
	 * <p>
	 * 不包含非可用。
	 * 
	 * @param uuid
	 *            答题卡唯一标识
	 * @return 答题卡
	 */
	public AnswersheetConfig find(String uuid) {
		StringBuilder sql = new StringBuilder();
		sql.append("select uuid,locationJson,topicSetName,topicSetUid,createDatetime,");
		sql.append("createUserId,updateDatetime,updateUserId,subjectName,gradeName,frontJson,html,subjectCode,isUsed,source");
		sql.append(" from answersheet_config where uuid=?");

		AnswersheetConfig config = jdbcTemplateAnswersheet.queryForObject(sql.toString(), new Object[] { uuid }, new AnswersheetMapper());
		return config;
	}

	/**
	 * 根据id删除答题卡。
	 * 
	 * @param id
	 *            答题卡id
	 */
	public void delete(String uuid) {
		StringBuilder sql = new StringBuilder();
		sql.append(" update answersheet_config set isDelete = 1 where uuid=?");
		jdbcTemplateAnswersheet.update(sql.toString(), uuid);
	}

	/**
	 * 获取答题卡基础信息。
	 * <p>
	 * 
	 * <pre>
	 *  内容如下:
	 * id(答题卡id),paper.name(所关联的试卷的名称),paper.id(所关联的试卷的id),
	 * subject.name(关联的学科),grade.name(关联的年级名称),
	 * subject.code(关联的学科code),isUsed(是否被使用),
	 * source(来源).
	 * </pre>
	 * 
	 * @param uuid
	 *            答题卡id
	 * @return 答题卡对象
	 */
	public AnswersheetConfig getBaseInfo(String uuid) {
		Assert.notNull(uuid, "uuid is required!");
		StringBuilder sql = new StringBuilder();
		sql.append("select uuid,topicSetName,topicSetUid,");
		sql.append("subjectName,gradeName,subjectCode,isUsed,source");
		sql.append(" from answersheet_config where isDelete=0 and uuid=?");

		AnswersheetConfig config = jdbcTemplateAnswersheet.queryForObject(sql.toString(), new Object[] { uuid }, new AnswersheetMapper());
		return config;
	}

	/**
	 * 根据试卷的id 和 来源获取答题卡。
	 * 
	 * @param paperId
	 *            试卷id
	 * @param source
	 *            来源
	 * @return 答题卡对象
	 */
	public String getFrontJson(String paperId, AnswersheetSource source) {
		String sql = "select frontJson from answersheet_config where isDelete=0 and topicSetUid=? and source=?";
		return jdbcTemplateAnswersheet.queryForObject(sql, new Object[]{paperId,source.name()}, String.class);
	}

	/**
	 * 根据用户id获取分页答题卡信息。
	 * 
	 * @param userId
	 *            用户id
	 * @param pageParam
	 *            分页参数
	 * @return 答题卡分页信息
	 */
	public List<AnswersheetConfig> getAnswersheets(String userId, PageParam pageParam) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select uuid,topicSetName,createDatetime from answersheet_config");
		sql.append(" where isDelete=0 and createUserId=?");

		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 createDatetime DESC");
		}

		sql.append(" limit ?,?");

		Object[] params = new Object[] { userId, pageParam.getStartIndex(), pageParam.getPageSize() };
		return jdbcTemplateAnswersheet.query(sql.toString(), params, new AnswersheetMapper());
	}

	/**
	 * 根据用户id获取答题卡总数。
	 * 
	 * @param creatorId
	 *            用户id
	 * @return 答题卡总数
	 */
	public int getCount(String creatorId) {
		String sql = "select count(1) from answersheet_config where isDelete=0 and createUserId = ?";
		return jdbcTemplateAnswersheet.queryForObject(sql, new Object[] { creatorId }, Integer.class);
	}

	/**
	 * 批量设置答题卡是否被使用。
	 * 
	 * @param uuidList
	 *            答题卡id集合
	 * @param isUsed
	 *            是否被使用
	 */
	public void setUsed(List<String> uuidList, boolean isUsed) {
		String sql = "update answersheet_config set isUsed = ? where isDelete=0 and uuid = ?";
		List<Object[]> batchArgs = new ArrayList<Object[]>(uuidList.size());
		for (String uuid : uuidList) {
			batchArgs.add(new Object[] { isUsed, uuid });
		}
		jdbcTemplateAnswersheet.batchUpdate(sql, batchArgs);
	}

	/**
	 * 查询答题卡是否已被使用。
	 * 
	 * @param uuid
	 *            答题卡id
	 */
	/**
	 * 查询答题卡是否已被使用。
	 * 
	 * @param uuid
	 *            答题卡id
	 * @return 是否被使用
	 */
	public Boolean isUsed(String uuid) {
		Assert.notNull(uuid, "uuid must not be null.");
		String sql = " select isUsed from  answersheet_config where isDelete = 0 and uuid=" + uuid;
		final Map<String, String> map = new HashMap<String, String>();
		jdbcTemplateAnswersheet.query(sql, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				map.put("isUsed", rs.getString("isUsed"));
			}
		});
		if (map.get("isUsed").equals("1")) {
			return true;
		} else {
			return false;
		}

	}

	/**
	 * 根据试卷id获取答题卡对象。
	 * 
	 * @param paperId
	 *            试卷id
	 * @return 答题卡
	 */
	public AnswersheetConfig getAnswersheetByPaperId(String paperId) {
		String sql = "select uuid,frontJson from answersheet_config where isDelete = 0 and topicSetUid=?";
		AnswersheetConfig config = jdbcTemplateAnswersheet.queryForObject(sql.toString(), new Object[] { paperId }, new AnswersheetMapper());
		return config;
	}

	/**
	 * 根据试卷id查找对应答题卡。
	 * 
	 * @param id
	 *            答题卡id
	 */
	public void deleteByPaperId(String id) {
		String sql = "update answersheet_config set isDelete = 1 where topicSetUid=?";
		jdbcTemplateAnswersheet.update(sql.toString(), id);
	}

	/**
	 * 根据答题卡id获取frontJson
	 * 
	 * @param id
	 *            答题卡id
	 * @return frontJson
	 */
	public String getFrontJson(String id) {
		final String sql = "select frontJson from answersheet_config where isDelete=0 and uuid=" + id;
		final Map<String, String> map = new HashMap<String, String>();
		jdbcTemplateAnswersheet.query(sql, new RowCallbackHandler() {
			public void processRow(ResultSet rs) throws SQLException {
				map.put("frontJson", rs.getString("frontJson"));
			}
		});
		return map.get("frontJson");
	}

	/**
	 * 根据创建人id获取答题卡列表
	 * 
	 * @param creatorId
	 * @return 答题卡列表
	 */
	public List<AnswersheetConfig> getAnswersheets(String creatorId) {
		Assert.notNull(creatorId, "creatorId is required! ");
		String sql = "select uuid,topicSetName,createDataTime from answersheet_config where isDelete=0 and userId=?";

		return jdbcTemplateAnswersheet.query(sql, new Object[] { creatorId }, new AnswersheetMapper());

	}

	/**
	 * 
	 * @author mt
	 * 
	 */
	class AnswersheetMapper implements RowMapper<AnswersheetConfig> {

		@Override
		public AnswersheetConfig mapRow(ResultSet rs, int rowNum) throws SQLException {
			AnswersheetConfig config = new AnswersheetConfig();
			if (DaoUtil.isColumnExist(rs, "uuid")) {
				config.setId(rs.getString("uuid"));
			}
			if (DaoUtil.isColumnExist(rs, "createDatetime")) {
				config.setCreateTime(rs.getDate("createDatetime"));
			}
			if (DaoUtil.isColumnExist(rs, "updateDatetime")) {
				config.setUpdateTime(rs.getDate("updateDatetime"));
			}
			if (DaoUtil.isColumnExist(rs, "createUserId")) {
				User user = new User();
				user.setId(rs.getString("createUserId"));
				config.setCreator(user);
			}
			if (DaoUtil.isColumnExist(rs, "updateUserId")) {
				User user = new User();
				user.setId(rs.getString("updateUserId"));
				config.setUpdator(user);
			}
			if (DaoUtil.isColumnExist(rs, "topicSetName")) {
				Paper paper = new Paper();
				paper.setName(rs.getString("topicSetName"));
				if (DaoUtil.isColumnExist(rs, "topicSetUid")) {
					paper.setId(rs.getString("topicSetUid"));
				}
				config.setPaper(paper);
			}
			if (DaoUtil.isColumnExist(rs, "locationJson")) {
				config.setLocationJson(rs.getString("locationJson"));
			}
			if (DaoUtil.isColumnExist(rs, "frontJson")) {
				Desc desc = new Desc();
				desc = JSONUtils.parseObject(rs.getString("frontJson"), Desc.class);
				config.setDesc(desc);
			}
			if (DaoUtil.isColumnExist(rs, "subjectCode")) {
				Subject subject = new Subject();
				subject.setCode(rs.getString("subjectCode"));
				subject.setName(rs.getString("subjectName"));
				config.setSubject(subject);
			}
			if (DaoUtil.isColumnExist(rs, "gradeName")) {
				Grade grade = new Grade();
				grade.setName(rs.getString("gradeName"));
				config.setGrade(grade);
			}
			if (DaoUtil.isColumnExist(rs, "html")) {
				config.setHtml(rs.getString("html"));
			}
			if (DaoUtil.isColumnExist(rs, "isUsed")) {
				config.setIsUsed(rs.getBoolean("isUsed"));
			}

			final List<String> AnswerSheetSource = Lists.newArrayList("word", "paper", "manual");
			if (DaoUtil.isColumnExist(rs, "source") && AnswerSheetSource.contains(rs.getString("source"))) {
				config.setSource(AnswersheetSource.valueOf(rs.getString("source")));
			}
			return config;
		}

	}

	/**
	 * @author mt
	 */
	class AnswersheetConfigBaseInfoMapper implements RowMapper<AnswersheetConfig> {

		@Override
		public AnswersheetConfig mapRow(ResultSet rs, int rowNum) throws SQLException {
			AnswersheetConfig answersheetConfig = new AnswersheetConfig();
			Paper paper = new Paper();
			paper.setName(rs.getString("topicSetName"));
			paper.setId(rs.getString("topicSetUid"));
			answersheetConfig.setPaper(paper);

			Subject subject = new Subject();
			subject.setCode(rs.getString("subjectCode"));
			subject.setName(rs.getString("subjectName"));

			Grade grade = new Grade();
			grade.setCode(rs.getString("gradeCode"));
			grade.setName(rs.getString("gradeName"));
			answersheetConfig.setIsUsed(rs.getBoolean("isUsed"));
			answersheetConfig.setSource(AnswersheetSource.valueOf(rs.getString("source")));

			return answersheetConfig;
		}
	}

}