<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.hxhq.business.mapper.StudyMapper">
    <select id="queryList" resultType="com.hxhq.business.dto.study.StudyListDto">
        SELECT s.`id`,s.`type`,s.`sn`,s.`name`,s.`leader`,s.`leader_name`,s.`leader_name_en`,s.`status`,s.`borrow_status`,s.`create_time`,s.`dept_name` FROM `t_study` s
        <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
            <where>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>

    <select id="queryFormCountList" resultType="com.hxhq.business.dto.study.StudyListDto">
        SELECT study_id AS id, COUNT(0) AS formCount,COUNT(IF(bdzt=5 OR bdzt=7 OR bdzt=11,id,NULL)) AS formFinishCount
        FROM `t_study_form_fill`
        WHERE del_flag='0'
        AND study_id IN
        <foreach collection="idList" item="id" open="(" separator="," close=")">
            #{id}
        </foreach>
        GROUP BY study_id
    </select>

    <select id="queryNotFinishFormName" resultType="java.lang.String">
        SELECT bdmc FROM `t_study_form_fill` WHERE
        del_flag='0' AND study_id=#{studyId}
        AND bdzt NOT IN (5,7,11)

        UNION ALL

        SELECT bdmc FROM `t_study_form_apply` WHERE
        del_flag='0' AND study_id=#{studyId}
        AND bdzt NOT IN (5,7,11)

        UNION ALL

        SELECT bdmc FROM `t_study_form_plan` WHERE
        del_flag='0' AND study_id=#{studyId}
        AND bdzt NOT IN (5,7,11)
    </select>

    <select id="queryHomeFormCount" resultType="com.hxhq.business.dto.home.HomeCountDto">
        SELECT SUM(formTodayCount) AS formTodayCount,SUM(formYtjCount) AS formYtjCount,SUM(formTbzCount) AS formTbzCount
        FROM (
        SELECT
        COUNT(IF(DATE(create_time)=DATE(NOW()),id,NULL)) AS formTodayCount,
        COUNT(IF(bdzt>1,id,NULL)) AS formYtjCount,
        COUNT(IF(bdzt=1,id,NULL)) AS formTbzCount
        FROM `t_study_form_fill` WHERE del_flag='0'
        <if test="userId!=null">
            AND user_id=#{userId}
        </if>


        UNION ALL

        SELECT
        COUNT(IF(DATE(create_time)=DATE(NOW()),id,NULL)) AS formTodayCount,
        COUNT(IF(bdzt>1,id,NULL)) AS formYtjCount,
        COUNT(IF(bdzt=1,id,NULL)) AS formTbzCount
        FROM `t_study_form_pre` WHERE del_flag='0'
        <if test="userId!=null">
            AND user_id=#{userId}
        </if>


        UNION ALL

        SELECT
        COUNT(IF(DATE(create_time)=DATE(NOW()),id,NULL)) AS formTodayCount,
        COUNT(IF(bdzt>1,id,NULL)) AS formYtjCount,
        COUNT(IF(bdzt=1,id,NULL)) AS formTbzCount
        FROM `t_study_form_apply` WHERE del_flag='0'
        <if test="userId!=null">
            AND user_id=#{userId}
        </if>


        UNION ALL

        SELECT
        COUNT(IF(DATE(create_time)=DATE(NOW()),id,NULL)) AS formTodayCount,
        COUNT(IF(bdzt>1,id,NULL)) AS formYtjCount,
        COUNT(IF(bdzt=1,id,NULL)) AS formTbzCount
        FROM `t_study_form_plan` WHERE del_flag='0'
        <if test="userId!=null">
            AND user_id=#{userId}
        </if>
        )a
    </select>

    <select id="queryHomeStudyCount" resultType="com.hxhq.business.dto.home.HomeCountDto">
        SELECT COUNT(IF(s.`status` IN (5,7,9),s.`id`,NULL)) AS studyFinishCount,
        COUNT(IF(s.create_time>=DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY) AND s.create_time&lt;DATE_ADD(DATE_SUB(CURDATE(), INTERVAL WEEKDAY(CURDATE()) DAY), INTERVAL 7 DAY),s.id,NULL))
        AS studyWeekCount
        FROM `t_study` s

        WHERE s.del_flag=0 AND s.`type`=1
        <if test="userId!=null">
            AND
            (s.leader=#{userId}
            OR EXISTS (SELECT ss.id FROM `t_study_subject` ss WHERE ss.`del_flag`='0' AND ss.`study_id`=s.`id` AND ss.leader=#{userId})
            OR EXISTS (SELECT ssu.id FROM `t_study_subject_user` ssu WHERE ssu.`del_flag`='0' AND ssu.`study_id`=s.`id` AND ssu.user_id=#{userId})
            )
        </if>
    </select>

    <!--    档案相关-->
    <select id="queryArchiveList" resultType="com.hxhq.business.dto.study.StudyListDto">
        SELECT s.`id`,s.`sn`,s.`name`,s.`leader`,s.`leader_name`,s.`leader_name_en`,s.`status`,s.`borrow_status`,s.`gd_apply_time`,s.borrow_start_date,s.borrow_end_date
        FROM `t_study` s
        <if test="ew.sqlSegment != '' and ew.sqlSegment != null">
            <where>
                ${ew.sqlSegment}
            </where>
        </if>
    </select>
    <!--    档案相关-->
</mapper>