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

import cn.com.qmth.stmms.ms.core.domain.Paper;
import cn.com.qmth.stmms.ms.core.vo.Subject;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

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

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

    List<Paper> findByRandomSeq(Long randomSeq);

    int countByWorkIdAndExamNumberAndRandomSeq(Long workId, String examNumber, Long randomSeq);

    List<Paper> findByWorkIdAndSubject(Long workId, Subject subject);

    List<Paper> findByWorkIdAndSubjectAndIsMissing(Long workId, Subject subject, boolean isMissing);

    List<Paper> findByWorkIdAndSubjectAndIsMissingAndTest(Long workId, Subject subject, boolean isMissing, int test);

    @Query(value = "select * from paper p where p.work_id = ?1 and p.subject = ?2 and p.is_missing = false and p.is_active = false limit ?3", nativeQuery = true)
    List<Paper> findByWorkIdAndSubjectAndIsMissingAndIsActive(Long workId, Subject subject, int taskCount);

    List<Paper> findByWorkIdAndSubjectAndIsMissingFalseAndActiveFalseAndTestOrderByQuestionId(Long workId, Subject subject, int test);

    List<Paper> findByWorkIdAndQuestionIdAndSubjectAndIsMissingFalseAndActiveFalseAndTestOrderByQuestionId(Long workId, Long questionId, Subject subject, int test);

    List<Paper> findByWorkIdAndSubjectAndIsMissingFalseAndActiveTrue(Long workId, Subject subject, Sort sort);

    List<Paper> findByWorkIdAndSubjectAndIsMissingFalseAndActiveTrueAndLevelIn(Long workId, Subject subject, List<String> levelList);

    List<Paper> findByWorkIdAndSubject(Long workId, Subject subject, Sort sort);

    List<Paper> findByQuestionId(Long questionId, Sort sort);

    Page<Paper> findByQuestionId(Long questionId, Pageable pageable);

    @Query(value = "select * from paper p where p.`level` = ?1 and p.is_sample = ?2 and p.is_missing = ?3 and p.subject = ?4 and (p.is_test = ?5 or p.is_test = ?6) and p.area_code = ?7 and p.work_id = ?8 order by p.updated_on desc", nativeQuery = true)
    List<Paper> findSample(String level, boolean isSample, boolean isMissing, String subject, int test, int test1, String areaCode, Long workId);

    /**
     * 统计各省各科试卷数量[areaCode,areaName,questionName,count]
     *
     * @param workId
     * @return
     */
    @Query(value = "SELECT q.`area_code`,q.`area_name`,p.`question_name`,COUNT(*) FROM paper p LEFT OUTER JOIN exam_question q ON p.`question_id` = q.`id` WHERE p.`work_id` = ? GROUP BY p.`question_id`,p.`question_name` ORDER BY p.`question_id` ", nativeQuery = true)
    List<Object[]> countGroupByQuestion(Long workId);

    /**
     * 统计科目各试题已评数及总数（分档阶段）
     *
     * @param workId
     * @param subject
     * @return
     */
    @Query(value = "SELECT p.`question_id`,p.question_name,SUM(IF(p.level IS NULL,1,0)),COUNT(p.`question_id`), MAX(p.area_code) \n" +
            "FROM paper p WHERE p.`work_id` = ? AND p.`subject` = ? AND p.`is_missing` = false AND p.`is_test` = ? " +
            "GROUP BY p.`question_id` ,p.question_name ORDER BY p.`question_id`", nativeQuery = true)
    List<Object[]> countGroupByQuestionForLevel(Long workId, String subject, int test);

    /**
     * 统计科目指定试题的各档位数量
     *
     * @param questionId
     * @return
     */
    @Query(value = "SELECT p.`level`,COUNT(*)," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))" +
            "FROM paper p " +
            "WHERE p.`question_id` = ? and p.is_missing = false " +
            "AND p.batch_no = ? " +
            "GROUP BY p.`level` ORDER BY p.`level`", nativeQuery = true)
    List<Object[]> countGroupByLevel(Long questionId, Long batchNo);

    @Query(value = "SELECT p.`level`,COUNT(*)," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))" +
            "FROM paper p " +
            "WHERE p.`question_id` = ? and p.is_missing = false and p.batch_no is not null " +
            "GROUP BY p.`level` ORDER BY p.`level`", nativeQuery = true)
    List<Object[]> countGroupByLevel(Long questionId);

    /**
     * 统计科目指定试题的各档位打分数量
     *
     * @param questionId
     * @return
     */
    @Query(value = "SELECT SUM(IF(p.`score` is null, 1, 0))," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))," +
            "SUM(IF(p.`is_shift` = 1, 1, 0))," +
            "SUM(IF(p.`is_shift` = 0 and p.`is_shift_score` = 1, 1, 0)) " +
            "FROM paper p " +
            "WHERE p.`question_id` = ? and p.is_missing = false " +
            "AND p.score_batch_no = ?", nativeQuery = true)
    List<Object[]> countScoreGroupByLevel(Long questionId, Long batchNo);

    /**
     * 统计科目指定试题的各档位数量
     *
     * @param questionId
     * @return
     */
    @Query(value = "SELECT p.`level`,COUNT(*)," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))," +
            "SUM(IF(p.`is_shift` = 1, 1, 0))," +
            "SUM(IF(p.`is_shift_score` = 1, 1, 0))" +
            "FROM paper p " +
            "WHERE p.`question_id` = ? and p.is_missing = false and p.is_test = ? " +
            "GROUP BY p.`level` ORDER BY p.`level`", nativeQuery = true)
    List<Object[]> countGroupByLevelAll(Long questionId, int test);

    /**
     * 统计科目指定试题的各档位数量
     *
     * @param questionId
     * @return
     */
    @Query(value = "select * from (SELECT p.`level`,COUNT(*)," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))" +
            "FROM paper p " +
            "WHERE p.`question_id` = ? and p.is_missing = false " +
            "AND p.batch_no = (select max(batch_no) from paper where question_id = ?) " +
            " AND p.level is null  " +
            " GROUP BY p.`level`  union  " +
            "SELECT p.`level`,COUNT(*)," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))" +
            "FROM paper p " +
            "WHERE p.`question_id` = ? and p.is_missing = false " +
            " AND p.level  is not null  " +
            " GROUP BY p.`level`) t ORDER BY t.`level`"
            , nativeQuery = true)
    List<Object[]> countGroupByLevelAllOther(Long questionId, Long questionId1, Long questionId2);

    /**
     * 统计workId各档位数量
     *
     * @param workId
     * @return
     */
    @Query(value = "SELECT p.`level`,COUNT(*)," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))" +
            "FROM paper p " +
            "WHERE p.`work_id` = ? " +
            "GROUP BY p.`level` ORDER BY p.`level`", nativeQuery = true)
    List<Object[]> countGroupByLevelAndWorkId(Long workId);

    /**
     * 统计科目各试题已评数及总数（打分阶段）
     *
     * @param workId
     * @return
     */
    @Query(value = "SELECT p.`question_id`,p.question_name,SUM(IF(p.score IS NULL,1,0)),COUNT(p.`question_id`) ,MAX(p.area_code) \n" +
            "FROM paper p WHERE p.`work_id` = ? AND p.`subject` = ? AND p.is_missing = false " +
            "GROUP BY p.`question_id` ,p.question_name ORDER BY p.`question_id`", nativeQuery = true)
    List<Object[]> countGroupByQuestionForScore(Long workId, String string);

    /**
     * 统计科目未分档的试卷数
     *
     * @param workId
     * @param subject
     * @return
     */
    Long countByWorkIdAndSubjectAndLevelIsNull(Long workId, Subject subject);

    Long countByWorkIdAndSubjectAndTestAndLevelIsNull(Long workId, Subject subject, int test);

    long countByWorkIdAndScoreIsNull(Long workId);

    Long countByWorkIdAndTest(Long workId, int test);

    long countByWorkIdAndQuestionId(Long workId, Long questionId);

    /**
     * 统计同一个考生的paper数量
     *
     * @param workId
     * @param examNumber
     * @return
     */
    Long countByWorkIdAndExamNumber(Long workId, String examNumber);

    /**
     * 查询指定试卷
     *
     * @param workId
     * @param subject
     * @param examNumber
     * @return
     */
    Paper findByWorkIdAndSubjectAndExamNumber(Long workId, Subject subject, String examNumber);

    Paper findByWorkIdAndSubjectAndExamNumberAndAreaCodeAndTest(Long workId, Subject subject, String examNumber, String areaCode, int test);

    /**
     * 根据条件查询指定试卷
     *
     * @param workId
     * @param subject
     * @param examNumber
     * @param test
     * @return
     */
    Paper findByWorkIdAndSubjectAndExamNumberAndTest(Long workId, Subject subject, String examNumber, int test);

    /**
     * 查询同一个考生的paper
     *
     * @param workId
     * @param examNumber
     * @return
     */
    List<Paper> findByWorkIdAndExamNumber(Long workId, String examNumber);

    List<Paper> findByWorkIdAndExamNumberAndTest(Long workId, String examNumber, String test);

    /**
     * @param workId
     * @param secretNumber
     * @return
     */
    List<Paper> findByWorkIdAndSecretNumber(Long workId, String secretNumber);

    List<Paper> findByWorkIdAndExamNumberIn(Long workId, List<String> examNumbers);

    @Query(value = "select p from Paper p where p.score >= ?1 or p.score <= ?2")
    List<Paper> findByScoreRange(Double highScore, Double lowScore);

    //@Query(value = "SELECT p.`exam_number`,p.`student_name`,p.question_id,GROUP_CONCAT(score  ORDER BY p.`subject`) FROM paper p WHERE p.`work_id` = ? GROUP BY p.exam_number,p.`student_name`", nativeQuery = true)
    @Query(value = "SELECT p.`exam_number`,p.`student_name`,p.question_id, p.source_name, GROUP_CONCAT(if(isnull(p.inspect_score),p.score,p.inspect_score)  ORDER BY p.`subject`) FROM paper p WHERE p.`work_id` = ? GROUP BY p.exam_number,p.`student_name` , p.source_name", nativeQuery = true)
    List<Object[]> findScores(Long workId);

    List<Paper> findByWorkId(Long workId);

    /**
     * 根据条件查找paper
     *
     * @param workId
     * @param subject
     * @param test
     * @return
     */
    List<Paper> findByWorkIdAndSubjectAndTest(Long workId, Subject subject, int test);

    @Modifying
    @Query(nativeQuery = true, value = "update paper set inspect_range = null where inspect_range = :inspectRange")
    void resetInspectRange(@Param("inspectRange") Long inspectRange);


    @Modifying
    @Query(value = "update Paper p set p.inspectRange = ?1 where p.examNumber in ?2")
    void markInspectRange(Long inspectRange, List<String> examNumbers);

    @Modifying
    @Query(value = "update Paper p set p.test = ?4 where p.workId = ?1 and p.subject = ?2 and p.test = ?3")
    void updateByWorkIdAndSubjectAndTest(Long workId, Subject subject, int oldTest, int newTest);


    long countByWorkIdAndSubjectAndIsSampleIsTrue(Long workId, Subject subject);

    long countByWorkIdAndSubjectAndLevel(Long workId, Subject subject, String code);

    long countByWorkIdAndSubject(Long workId, Subject subject);

    long countByWorkIdAndSubjectAndQuestionIdAndLevel(Long workId,
                                                      Subject subject, Long id, String code);

    long countByWorkIdAndSubjectAndQuestionId(Long workId, Subject subject,
                                              Long id);

    void deleteByWorkIdAndSubjectAndTest(Long workId, Subject subject, int test);

    int countByWorkIdAndSubjectAndIsMissingFalseAndTest(Long workId, Subject subject, int test);

    int countByWorkIdAndSubjectAndLevelIsNullAndIsMissingFalseAndActiveTrueAndBatchNoNotNullAndTest(Long workId, Subject subject, int test);

    int countByWorkIdAndQuestionIdAndSubjectAndLevelIsNullAndIsMissingFalseAndTest(Long workId, Long questionId, Subject subject, int test);

    int countByWorkIdAndSubjectAndIsMissingFalseAndActiveFalseAndBatchNoIsNullAndTest(Long workId, Subject subject, int test);

    int countByWorkIdAndQuestionIdAndSubjectAndIsMissingFalseAndActiveFalseAndBatchNoIsNullAndTest(Long workId, Long questionId, Subject subject, int test);

    int countByWorkIdAndSubjectAndLevelNotNullAndIsMissingFalseAndActiveTrueAndTest(Long workId, Subject subject, int test);

    @Query("select max(p.batchNo) from Paper p where p.questionId = ?1")
    Long findByQuestionId(Long questionId);

    List<Paper> findByworkIdAndSubjectAndAreaCode(Long workId, Subject subject, String areaCode);

    int countByWorkIdAndSubjectAndLevelNotNullAndIsMissingFalseAndActiveTrueAndTestAndQuestionId(Long workId, Subject subject, int test, Long questionId);

    int countByWorkIdAndSubjectAndIsMissingFalseAndTestAndQuestionId(Long workId, Subject subject, int test, Long questionId);

    List<Paper> findByworkIdAndSubjectAndQuestionIdAndIsMissingAndTest(Long workId, Subject subject, Long questionId, boolean missing, int test);

    List<Paper> findByWorkIdAndSubjectAndIsMissingFalseAndActiveTrueAndScoreBatchNoIsNull(Long workId, Subject subject, Sort sort);

    int countByWorkIdAndSubjectAndIsArbitratedTrue(Long workId, Subject subject);

    int countByWorkIdAndSubjectAndScoreBatchNoIsNotNullAndScoreIsNull(Long workId, Subject subject);

    List<Paper> findByWorkIdAndSubjectAndIsSampleTrue(Long workId, Subject subject);

    @Query("select max(p.scoreBatchNo) from Paper p where p.questionId = ?1")
    Long findScoreBatchNoByQuestionId(Long questionId);

    int countByWorkIdAndSubjectAndScoreNotNullAndIsMissingFalseAndActiveTrueAndTest(Long workId, Subject subject, int i);

    List<Paper> findByWorkIdAndSubjectAndIsOneClickTrue(Long workId, Subject subject);

    @Query("select count(p) from Paper p where p.workId = ?1 and p.subject = ?2 and p.isMissing = false and (p.test = ?3 or p.test = ?4) and p.questionId = ?5")
    int countByWorkIdAndSubjectAndIsMissingFalseAndTestOrTestAndQuestionId(Long workId, Subject subject, int i, int i1, Long id);

    @Query("select count(p) from Paper p where p.workId = ?1 and p.subject = ?2 and p.isMissing = false and (p.test = ?3 or p.test = ?4)")
    int countByWorkIdAndSubjectAndIsMissingFalseAndTestOrTest(Long workId, Subject subject, int i, int i1);

    @Query("select p from Paper p where p.workId = ?1 and p.subject = ?2 and p.isMissing = false and p.active = false and (p.test = ?3 or p.test = ?4) order by p.questionId")
    List<Paper> findByWorkIdAndSubjectAndIsMissingFalseAndActiveFalseAndTestOrTestOrderByQuestionId(Long workId, Subject subject, int id, int id1);

    @Query("select p from Paper p where p.workId = ?1 and p.subject = ?2 and p.questionId = ?3 and p.isMissing = ?4 and (p.test = ?5 or p.test = ?6)")
    List<Paper> findByworkIdAndSubjectAndQuestionIdAndIsMissingAndTestOrTest(Long workId, Subject subject, Long questionId, boolean b, int id, int id1);

    @Query(value = "SELECT p.`score`,COUNT(*)," +
            "SUM(IF(p.`is_rejected` = 1, 1, 0))," +
            "SUM(IF(p.`is_arbitrated` = 1, 1, 0))," +
            "SUM(IF(p.`is_shift` = 1, 1, 0))," +
            "SUM(IF(p.`is_shift_score` = 1, 1, 0)) " +
            "FROM paper p " +
            "WHERE p.`question_id` = ? and p.is_missing = false and p.is_test = ? and p.score is null and p.score_batch_no is not null " +
            "GROUP BY p.`score`", nativeQuery = true)
    List<Object[]> countScoreGroupByLevelAll(Long questionId, int test);

    int countByWorkIdAndSubjectAndLevelAndScoreNotNull(Long workId, Subject subject, Object id);

    int countByWorkIdAndSubjectAndQuestionIdAndLevelAndScoreNotNull(Long workId, Subject subject, Long questionId, String valueOf);

    List<Paper> findByWorkIdAndQuestionIdAndSubjectAndIsMissingFalseAndActiveTrueAndScoreBatchNoIsNull(Long workId, Long questionId0, Subject subject, Sort sort);

    @Query("select max(p.batchNo) from Paper p where p.workId = ?1")
    Long findMaxBatchNoByWorkId(Long workId);

    @Query("select max(p.scoreBatchNo) from Paper p where p.workId = ?1")
    Long findMaxScoreBatchNoByWorkId(Long workId);

    List<Paper> findByWorkIdAndQuestionIdAndSubjectAndLevelAndIsMissingFalseAndActiveTrueAndScoreBatchNoIsNull(Long workId, Long questionId0, Subject subject, String code, Sort sort);

    @Query(value = "select p.* from paper p left join work w on p.work_id = w.id where w.organization_id = ?1 limit 1", nativeQuery = true)
    Paper findOneByOrganizationId(Long organizationId);
	
    @Query("select max(p.batchNo) from Paper p where p.workId = ?1 and p.subject = ?2")
    Long findMaxBatchNoByWorkIdAndSubject(Long workId, Subject subject);

    @Query("select max(p.scoreBatchNo) from Paper p where p.workId = ?1 and p.subject = ?2")
    Long findMaxScoreBatchNoByWorkIdAndSubject(Long workId, Subject subject);

//    List<Paper> findByWorkIdAndSubjectAndInspectRange(Long workId, Subject subject, Long inspectRange);
}
