package cn.com.qmth.stmms.ms.core.repository;

import cn.com.qmth.stmms.ms.core.domain.MarkStage;
import cn.com.qmth.stmms.ms.core.domain.task.MarkTask;
import cn.com.qmth.stmms.ms.core.vo.Subject;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * Created by zhengmin on 2016/9/23.
 */
public interface MarkTaskRepo extends JpaRepository<MarkTask, Long>, JpaSpecificationExecutor {

    int countByMarkerIdAndPaperIdAndWorkIdAndRandomSeqNew(Long markerId, Long paperId, Long randomSeqNew, Long workId);

    List<MarkTask> findByWorkIdAndStageAndPaperId(Long workId, MarkStage stage, Long paperId);

    List<MarkTask> findByRandomSeqNew(Long randomSeq);

    /**
     * 查询指定阶段试卷的评卷任务
     *
     * @param paperId 试卷id
     * @param stage   评卷阶段
     * @return
     */
    List<MarkTask> findByPaperIdAndStage(Long paperId, MarkStage stage);

    /**
     * 评卷员任务数量
     *
     * @param markerId
     * @return
     */
    long countByMarkerId(Long markerId);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.batch_no = ?2 where m.question_id = ?1 and m.marker_id = ?3", nativeQuery = true)
    long countByQuestionId(Long questionId, Long batchNo, Long markerId);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.score_batch_no = ?2 where m.question_id = ?1 and m.marker_id = ?3", nativeQuery = true)
    long countScoreByQuestionId(Long questionId, Long batchNo, Long markerId);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.score_batch_no is not null where m.question_id = ?1 and m.marker_id = ?2", nativeQuery = true)
    long countScoreByQuestionIdAll(Long questionId, Long markerId);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result = ?4 and p.batch_no = ?5 and p.is_rejected = false", nativeQuery = true)
    int countByQuestionIdAndMarkerIdAndStageAndResult(Long questionId, Long markerId, int stage, String result, Long batchNo);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result = ?4 and p.batch_no is not null and p.is_rejected = false ", nativeQuery = true)
    int countByQuestionIdAndMarkerIdAndStageAndResultAll(Long questionId, Long markerId, int stage, String result);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and p.level = ?4 and p.score_batch_no = ?5 and m.result is not null", nativeQuery = true)
    int countScoreByQuestionIdAndMarkerIdAndStageAndResult(Long questionId, Long markerId, int stage, String result, Long batchNo);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and p.level = ?4 and p.score_batch_no is not null and m.result is not null", nativeQuery = true)
    int countScoreByQuestionIdAndMarkerIdAndStageAndResultAll(Long questionId, Long markerId, int stage, String result);
    /**
     * 查询评卷员的评卷任务
     *
     * @param markerId
     * @param stage
     * @return
     */
    @Query(value = "select mt from MarkTask mt left outer join fetch mt.paper where mt.markerId = ? and mt.stage = ?")
    List<MarkTask> findByMarkerIdAndStage(Long markerId, MarkStage stage);

    /**
     * 删除评卷员所有任务
     *
     * @param subject
     * @param stage
     * @return
     */
    Long deleteByWorkIdAndSubjectAndStage(Long workId, Subject subject, MarkStage stage);

    /**
     * 删除科目下所有任务
     *
     * @param workId
     * @param subject
     * @return
     */
    Long deleteByWorkIdAndSubjectAndTest(Long workId, Subject subject, int test);

    /**
     * 统计评卷员各试题已评数及总数（分档阶段）
     *
     * @param markerId
     * @return
     */
    @Query(value = "SELECT q.`id`,q.`name`,SUM(IF(t.`result` IS NULL,1,0)),COUNT(t.question_id), q.area_code \n" +
            "FROM mark_task t LEFT OUTER JOIN exam_question q ON t.`question_id` = q.`id` LEFT  JOIN paper p ON t.paper_id = p.id WHERE " +
            "t.`marker_id` = ? and t.stage = ? and p.is_missing = false and p.is_test = ?  GROUP BY t.`question_id` ORDER BY q.`id`", nativeQuery = true)
    List<Object[]> countGroupByQuestion(Long markerId, Integer stageId, int test);

    /**
     * 统计各评卷员各试题已评数及总数（分档阶段）
     *
     *
     * @param name
     * @param stageId
     * @param batchNo
     * @return
     */
    @Query(value = "SELECT q.`id`,t.`marker_id` markerId,mu.login_name loginName, t.`marker_name` markerName,q.`name`,SUM(IF(t.`result` IS NULL,1,0)) leftCount,COUNT(t.question_id) totalCount, SUM(t.is_rejected) rejectedCount, q.area_code areaCode " +
            "FROM mark_task t LEFT JOIN mark_user mu on t.marker_id = mu.id LEFT OUTER JOIN exam_question q ON t.`question_id` = q.`id` LEFT  JOIN paper p ON t.paper_id = p.id WHERE " +
            "t.work_id = ? and t.subject = ? and t.stage = ? and p.is_missing = false and p.is_test = ? and p.batch_no = ? GROUP BY t.marker_id,mu.login_name, t.`marker_name` ORDER BY q.`id`,t.marker_id", nativeQuery = true)
    List<Object[]> listGroupByQuestionAndMarkerAndBatchNo(Long workId, String name, Integer stageId, int test, Long batchNo);

    /**
     * 考区已评数及总数（分档阶段）
     *
     *
     * @param name
     * @return
     */
    @Query(value = "SELECT q.area_code areaCode, q.area_name areaName, q.`name`,SUM(IF(p.`score` IS NULL,1,0)) leftCount,COUNT(p.question_id) totalCount " +
            "FROM exam_question q LEFT  JOIN paper p ON q.id = p.question_id AND q.subject = p.subject WHERE " +
            "p.work_id = ? and p.subject = ? and p.is_missing = false  GROUP BY q.area_code, q.area_name ORDER BY q.area_code", nativeQuery = true)
    List<Object[]> listGroupByAreaName(Long workId, String name);

    /**
     * 考区已评数及总数（分档阶段）
     *
     *
     * @param name
     * @return
     */
    @Query(value = "SELECT q.area_code areaCode, q.area_name areaName, q.`name`,SUM(IF(p.`level` IS NULL or (p.level is not null and p.is_active = 0),1,0)) leftCount,COUNT(p.question_id) totalCount " +
            "FROM exam_question q LEFT  JOIN paper p ON q.id = p.question_id AND q.subject = p.subject WHERE " +
            "p.work_id = ? and p.subject = ? and p.is_missing = false and p.is_test = ?  GROUP BY q.area_code, q.area_name ORDER BY q.area_code", nativeQuery = true)
    List<Object[]> listGroupByAreaName(Long workId, String name, int test);

    /**
     * 统计评卷员各试题已评数及总数（分档阶段）
     *
     * @param markerId
     * @return
     */
    @Query(value = "SELECT q.`id`,q.`name`,SUM(IF(t.`result` IS NULL,1,0)),COUNT(t.question_id), q.area_code \n" +
            "FROM mark_task t LEFT OUTER JOIN exam_question q ON t.`question_id` = q.`id` LEFT  JOIN paper p ON t.paper_id = p.id WHERE " +
            "t.`marker_id` = ? and t.stage = ? and p.is_missing = false GROUP BY t.`question_id` ORDER BY q.`id`", nativeQuery = true)
    List<Object[]> countGroupByQuestion(Long markerId, Integer stageId);

    /**
     * 统计各评卷员各试题已评数及总数（分档阶段）
     *
     * @return
     */
    @Query(value = "SELECT q.`id`,t.`marker_id` markerId,mu.login_name loginName,t.`marker_name` markerName,q.`name`,SUM(IF(t.`result` IS NULL,1,0)) leftCount,COUNT(t.question_id) totalCount,SUM(t.is_rejected) rejectedCount, q.area_code areaCode " +
            "FROM mark_task t LEFT JOIN mark_user mu on t.marker_id = mu.id LEFT OUTER JOIN exam_question q ON t.`question_id` = q.`id` LEFT  JOIN paper p ON t.paper_id = p.id WHERE " +
            "t.work_id = ? and t.subject = ? and t.stage = ? and p.score_batch_no = ? and p.is_missing = false GROUP BY t.marker_id,mu.login_name, t.`marker_name` ORDER BY q.`id`, t.`marker_id`", nativeQuery = true)
    List<Object[]> listGroupByQuestionAndMarkerAndScoreBatchNo(Long workId, String name, Integer stageId, Long batchNo);

    /**
     * 统计指定试卷的所有任务数量
     *
     * @param id
     * @return
     */
    Long countByPaperId(Long id);

    List<MarkTask> findByWorkIdAndSecretNumber(Long workId, String secretNumber);

    @Query(value = "SELECT max(t.randomSeq) FROM MarkTask t " +
            "WHERE t.workId = ?1 and t.subject = ?2 and t.markerId = ?3 and t.stage = ?4 and t.questionId = ?5 ")
    Integer findRandomSeqByWorkIdAndSubjectAndMarkerIdAndStageAndQuestionId(Long workId, Subject subject, Long markerId, MarkStage stage, Long questionId);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.batch_no = ?5 where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result is null and p.is_rejected = false", nativeQuery = true)
    int countByQuestionIdAndMarkerIdAndStageAndResultIsNullAndIsMissing(Long questionId, Long markerId, int stage, boolean isMissing, Long batchNo);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.score_batch_no = ?5 where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result is null and p.is_shift = 0 and p.is_shift_score = 0", nativeQuery = true)
    int countScoreByQuestionIdAndMarkerIdAndStageAndResultIsNullAndIsMissing(Long questionId, Long markerId, int stage, boolean isMissing, Long batchNo);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.score_batch_no is not null where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result is null and p.is_shift = 0 and p.is_shift_score = 0", nativeQuery = true)
    int countScoreByQuestionIdAndMarkerIdAndStageAndResultIsNullAndIsMissingAll(Long questionId, Long markerId, int stage, boolean isMissing);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.batch_no = ?5 where m.question_id = ?1 and m.marker_id = ?6 and m.stage = ?2 and m.result =?3 and p.is_rejected = false", nativeQuery = true)
    int countByQuestionIdAndStageAndResultAndIsMissing(Long questionId, int stage, String result, boolean isMissing,Long batchNo,Long markerId);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.score_batch_no = ?5 where m.question_id = ?1 and m.marker_id = ?6 and m.stage = ?2 and p.level =?3 and m.result is not null", nativeQuery = true)
    int countScoreByQuestionIdAndStageAndResultAndIsMissing(Long questionId, int stage, String result, boolean isMissing,Long batchNo,Long markerId);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.score_batch_no is not null where m.question_id = ?1 and m.marker_id = ?5 and m.stage = ?2 and p.level =?3 and m.result is not null", nativeQuery = true)
    int countScoreByQuestionIdAndStageAndResultAndIsMissingAll(Long questionId, int stage, String result, boolean isMissing,Long markerId);

    MarkTask findByPaperIdAndMarkerId(Long paperId, Long markId);

    List<MarkTask> findByWorkId(Long workId);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result is null and p.is_shift = 1", nativeQuery = true)
    int countShiftByQuestionIdAndMarkerIdAndStageAndResultIsNullAndIsMissing(Long questionId, Long id, int ordinal, boolean b);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result is null and p.is_shift = 0 and p.is_shift_score = 1", nativeQuery = true)
    int countShiftScoreByQuestionIdAndMarkerIdAndStageAndResultIsNullAndIsMissing(Long questionId, Long id, int ordinal, boolean b);

    @Query(value = "select count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.batch_no is not null where m.question_id = ?1 and m.marker_id = ?2", nativeQuery = true)
    long countByQuestionIdAll(Long questionId, Long markId);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.batch_no is not null where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and m.result is null and p.is_rejected = false", nativeQuery = true)
    int countByQuestionIdAndMarkerIdAndStageAndResultIsNullAndIsMissingAll(Long questionId, Long markerId, int stage, boolean isMissing);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.batch_no is not null where m.question_id = ?1 and m.marker_id = ?5 and m.stage = ?2 and m.result =?3 and p.is_rejected = false", nativeQuery = true)
    int countByQuestionIdAndStageAndResultAndIsMissingAll(Long questionId, int stage, String result, boolean isMissing,Long markerId);

    @Query(value = "SELECT count(1) from mark_task m inner join paper p on m.paper_id = p.id and p.is_missing = ?4 and p.batch_no is not null where m.question_id = ?1 and m.marker_id = ?2 and m.stage = ?3 and p.is_rejected = true  and m.is_rejected = true", nativeQuery = true)
    int countByQuestionIdAndMarkerIdAndStageAndIsRejectedTrueAndIsMissing(Long questionId, Long markerId, int ordinal,boolean isMissing);

    List<MarkTask> findByWorkIdAndSubjectAndMarkerIdAndStage(Long workId, Subject subject, Long valueOf, MarkStage stage);

    int countByWorkIdAndStageAndResultNotNull(Long id, MarkStage level);
}
