<?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.pcloud.book.timecontrol.mapper.TimeControlTaskMapper" >
  <resultMap id="BaseResultMap" type="com.pcloud.book.timecontrol.entity.TimeControlTask" >
    <id column="task_id" property="taskId" jdbcType="INTEGER" />
    <result column="task_name" property="taskName" jdbcType="VARCHAR" />
    <result column="task_introduce" property="taskIntroduce" jdbcType="VARCHAR" />
    <result column="task_key_word" property="taskKeyWord" jdbcType="VARCHAR" />
    <result column="subscribe_word" property="subscribeWord" jdbcType="VARCHAR" />
    <result column="success_subscribe_word" property="successSubscribeWord" jdbcType="VARCHAR" />
    <result column="success_unsubscribe_word" property="successUnSubscribeWord" jdbcType="VARCHAR" />
    <result column="advanced_setting" property="advancedSetting" jdbcType="INTEGER" />
    <result column="pro_label_id" property="proLabelId" jdbcType="INTEGER" />
    <result column="dep_label_id" property="depLabelId" jdbcType="INTEGER" />
    <result column="pur_label_id" property="purLabelId" jdbcType="INTEGER" />
    <result column="create_time" property="createTime" jdbcType="TIMESTAMP" />
    <result column="update_time" property="updateTime" jdbcType="TIMESTAMP" />
  </resultMap>

    <resultMap id="TaskDtoMap" type="com.pcloud.book.timecontrol.dto.TaskDto" >
        <id column="task_id" property="taskId" jdbcType="INTEGER" />
        <result column="task_name" property="taskName" jdbcType="VARCHAR" />
        <result column="task_introduce" property="taskIntroduce" jdbcType="VARCHAR" />
        <result column="task_key_word" property="taskKeyWord" jdbcType="VARCHAR" />
        <result column="subscribe_word" property="subscribeWord" jdbcType="VARCHAR" />
        <result column="advanced_setting" property="advancedSetting" jdbcType="INTEGER" />
        <result column="pro_label_id" property="proLabelId" jdbcType="INTEGER" />
        <result column="dep_label_id" property="depLabelId" jdbcType="INTEGER" />
        <result column="pur_label_id" property="purLabelId" jdbcType="INTEGER" />
    </resultMap>
  <sql id="Base_Column_List" >
    task_id, task_name, task_introduce, task_key_word, subscribe_word, advanced_setting, 
    create_time, update_time, pro_label_id, dep_label_id, pur_label_id, success_subscribe_word, success_unsubscribe_word
  </sql>

  <update id="updateTask" parameterType="com.pcloud.book.timecontrol.entity.TimeControlTask">
    update time_control_task set task_name = #{taskName}, task_introduce = #{taskIntroduce}, task_key_word = #{taskKeyWord},
    subscribe_word = #{subscribeWord}, advanced_setting = #{advancedSetting}, update_time = now(),
    pro_label_id = #{proLabelId}, dep_label_id = #{depLabelId}, pur_label_id = #{purLabelId}, success_subscribe_word = #{successSubscribeWord},
    success_unsubscribe_word = #{successUnSubscribeWord}
    where task_id = #{taskId}
  </update>

  <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
    select 
    <include refid="Base_Column_List" />
    from time_control_task
    where task_id = #{taskId,jdbcType=INTEGER}
  </select>

  <select id="getTaskCount" resultType="java.lang.Integer">
    SELECT COUNT(1) FROM time_control_task where 1=1
    <if test="search != null">
      and task_name like concat('%', #{search}, '%')
    </if>
  </select>

  <select id="listTask4Pcloud" resultType="com.pcloud.book.timecontrol.dto.TaskDto">
    SELECT
        a.task_id taskId,
        task_name taskName,
        task_introduce taskIntroduce,
        COUNT(b.id) taskItemCount,
        a.pur_label_id purLabelId,
        a.pro_label_id proLabelId,
        a.dep_label_id depLabelId
    FROM
        time_control_task a
    LEFT JOIN time_control_task_item b ON a.task_id = b.task_id
    where 1=1 and a.type = 0
    <if test="search != null">
      and task_name like concat('%', #{search}, '%')
    </if>
    GROUP BY
        a.task_id
    <if test="pageNum != null">
      limit #{pageNum}, #{numPerPage}
    </if>
  </select>

  <!--根据 专业/深度/目的 标签查询任务模板集合-->
  <select id="listIdByLabelId" resultType="java.lang.Integer">
    SELECT task_id FROM time_control_task WHERE 1 = 1
    <if test="proLabelId != null">
      AND pro_label_id = #{proLabelId}
    </if>
    <if test="depLabelId != null">
      AND dep_label_id = #{depLabelId}
    </if>
    <if test="purLabelId != null">
      AND pur_label_id = #{purLabelId}
    </if>
  </select>
    <select id="listTask" resultType="com.pcloud.book.timecontrol.dto.BookTaskBindingDto">
     SELECT
        a.task_id taskId,
        a.task_name taskName,
        b.book_id bookId,
        b.book_name bookName,
        b.isbn,
        CONCAT('BK', b.book_id) serialNumber,
        b.state,
        a.pro_label_id proLabelId,
        a.dep_label_id depLabelId,
        a.pur_label_id purLabelId
    FROM
        time_control_task a
    LEFT JOIN book_task_binding b ON a.task_id = b.task_id
    <if test="search != null">
        and a.task_name like concat('%', #{search}, '%')
    </if>
    </select>

    <select id="mapByIds" resultMap="TaskDtoMap">
        select task_id, task_name, task_introduce, task_key_word, subscribe_word, advanced_setting,
         pro_label_id, dep_label_id, pur_label_id
         from time_control_task
         where task_id in
         <foreach collection="taskIds" open="(" close=")" separator="," item="item">
             #{item}
         </foreach>
    </select>

    <select id="listKeyWordTask" resultType="com.pcloud.book.timecontrol.vo.KeyWordTaskVO">
        SELECT
            a.id,
            a.key_word keyWord,
            a.guide_word guideWord,
            a.task_id taskId,
            task_name taskName,
            task_introduce taskIntroduce
        FROM
            time_control_key_task a
        LEFT JOIN time_control_task b ON a.task_id = b.task_id
    </select>

    <select id="getTaskByKey" resultType="com.pcloud.book.timecontrol.dto.TaskDto">
         SELECT
            a.task_id taskId,
            task_name taskName,
            task_introduce taskIntroduce
        FROM
            time_control_key_task a
        LEFT JOIN time_control_task b ON a.task_id = b.task_id
        where a.key_word like concat('%', #{search}, '%')
        limit 1
    </select>

    <select id="listMaxDepLabel" resultType="Long">
        SELECT
            dep_label_id
        FROM
            `time_control_task`
        GROUP BY
            dep_label_id
        ORDER BY
            COUNT(1) DESC
            limit #{num}
    </select>

    <select id="listTaskByKey4User" resultType="com.pcloud.book.timecontrol.dto.TaskDto">
        SELECT
            task_id taskId,
            task_name taskName,
            task_introduce taskIntroduce
        FROM
            time_control_task
        WHERE
            task_name LIKE CONCAT('%', #{search}, '%')
            <if test="taskIds !=null and taskIds.size > 0">
                OR task_id IN
                <foreach collection="taskIds" open="(" close=")" separator="," item="item">
                    #{item}
                </foreach>
            </if>
    </select>

    <select id="listTaskByIds" resultType="com.pcloud.book.timecontrol.dto.TaskDto">
       SELECT
            task_id taskId,
            task_name taskName,
            task_introduce taskIntroduce
        FROM
            time_control_task
        WHERE task_id IN
        <foreach collection="taskIds" open="(" close=")" separator="," item="item">
            #{item}
        </foreach>
    </select>

    <select id="listTaskByLabelId" resultType="com.pcloud.book.timecontrol.dto.TaskDto">
        SELECT
            task_id taskId,
            task_name taskName,
            task_introduce taskIntroduce
        FROM
            time_control_task
        WHERE
            pro_label_id = #{labelId}
        OR dep_label_id = #{labelId}
        OR pur_label_id = #{labelId}
    </select>

    <select id="getTaskByIdAndWxUserId" resultType="com.pcloud.book.timecontrol.dto.TaskDto">
        select
        t.task_id taskId,
        t.task_name taskName,
        t.task_introduce taskIntroduce,
        ts.state subscribeState
        FROM
        time_control_task t
        left join time_control_task_subscribe ts
        on t.task_id = ts.task_id
        where
        t.task_id = #{relSkillId}
        and ts.wx_user_id = #{wxUserId}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
    delete from time_control_task
    where task_id = #{taskId,jdbcType=INTEGER}
  </delete>
  <insert id="insert" parameterType="com.pcloud.book.timecontrol.entity.TimeControlTask" useGeneratedKeys="true" keyProperty="taskId">
    insert into time_control_task (task_name, task_introduce,
      task_key_word, subscribe_word, advanced_setting, 
      create_time, update_time, pro_label_id, dep_label_id, pur_label_id, success_subscribe_word,
      success_unsubscribe_word)
    values (#{taskName,jdbcType=VARCHAR}, #{taskIntroduce,jdbcType=VARCHAR},
      #{taskKeyWord,jdbcType=VARCHAR}, #{subscribeWord,jdbcType=VARCHAR}, #{advancedSetting,jdbcType=INTEGER}, 
      now(), now(), #{proLabelId}, #{depLabelId}, #{purLabelId}, #{successSubscribeWord}, #{successUnSubscribeWord})
  </insert>

    <select id="getTaskCompletedCount" resultType="java.lang.Integer">
        SELECT ifnull(COUNT(1),0)
        FROM time_control_task a
        where 1=1
        <if test="taskName != null">
            and a.task_name like concat('%', #{taskName}, '%')
        </if>
        <if test="proLabelId != null">
            AND a.pro_label_id = #{proLabelId}
        </if>
        <if test="depLabelId != null">
            AND a.dep_label_id = #{depLabelId}
        </if>
        <if test="purLabelId != null">
            AND a.pur_label_id = #{purLabelId}
        </if>
    </select>

    <select id="listTaskCompleted4Pcloud" resultType="com.pcloud.book.timecontrol.dto.TaskCompletedDto">
        SELECT
        a.task_id taskId,
        a.task_name taskName,
        COUNT(distinct c.id) taskItemCount,
        COUNT(DISTINCT d.wx_user_id) subscribePersonCount
        FROM time_control_task a
        LEFT JOIN time_control_task_item c ON a.task_id = c.task_id
        LEFT JOIN time_control_task_subscribe d on a.task_id=d.task_id
        WHERE 1=1
        <if test="taskName != null">
            and a.task_name like concat('%', #{taskName}, '%')
        </if>
        <if test="proLabelId != null">
            AND a.pro_label_id = #{proLabelId}
        </if>
        <if test="depLabelId != null">
            AND a.dep_label_id = #{depLabelId}
        </if>
        <if test="purLabelId != null">
            AND a.pur_label_id = #{purLabelId}
        </if>
        GROUP BY
        a.task_id
        <if test="pageNum != null">
            limit #{pageNum}, #{numPerPage}
        </if>
    </select>

    <select id="getSubscribeCount" resultType="java.lang.Integer">
        SELECT ifnull(COUNT(1),0)
        FROM time_control_task_subscribe a
        where 1=1
        <if test="wxIds != null and wxIds.size()>0">
            and a.wx_user_id IN
            <foreach collection="wxIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="subscribeType !=null">
            and a.state=#{subscribeType}
        </if>
        and a.task_id=#{taskId}
    </select>

    <select id="listTaskSubscribeUser4Pcloud" resultType="com.pcloud.book.timecontrol.dto.SubscribeTaskUserDTO">
        select t1.wxUserId wxUserId,
        t1.subscribeState subscribeState,
        ifnull(t2.completedCount,0) completedCount from
        (select
        wx_user_id wxUserId,
        state subscribeState
        FROM time_control_task_subscribe a
        where 1=1
        <if test="wxIds != null and wxIds.size()>0">
            and a.wx_user_id IN
            <foreach collection="wxIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        <if test="subscribeType !=null">
            and a.state=#{subscribeType}
        </if>
        and a.task_id=#{taskId}
        )t1
        left join
        (
        select wx_user_id wxId,count(1) completedCount
        from time_control_task_send a where
        1=1
        <if test="wxIds != null and wxIds.size()>0">
           and a.wx_user_id IN
            <foreach collection="wxIds" open="(" close=")" separator="," item="item">
                #{item}
            </foreach>
        </if>
        and a.task_id=#{taskId} and complete_state=1
        GROUP BY wxId
        )t2 on t1.wxUserId=t2.wxId
        ORDER BY completedCount desc,wxUserId asc
        <if test="pageNum != null">
            limit #{pageNum}, #{numPerPage}
        </if>
    </select>
</mapper>