<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<mapper namespace="com.pcloud.book.skill.dao.impl.PcloudSkillDaoImpl">
    <resultMap id="BaseResultMap" type="com.pcloud.book.skill.entity.PcloudSkill">
        <id column="id" property="id" jdbcType="BIGINT"/>
        <result column="skill_type" property="skillType" jdbcType="INTEGER"/>
        <result column="rel_skill_id" property="relSkillId" jdbcType="BIGINT"/>
        <result column="skill_fuse_reply" property="skillFuseReply" jdbcType="VARCHAR"/>
        <result column="seq_num" property="seqNum" jdbcType="INTEGER"/>
        <result column="create_time" property="createTime" jdbcType="TIMESTAMP"/>
    </resultMap>

    <sql id="Base_Column_List">
        id, skill_type, rel_skill_id, skill_fuse_reply, seq_num, create_time
    </sql>

    <sql id="Column_List_Skill">
        id, skill_type, rel_skill_id, skill_fuse_reply, seq_num,
    </sql>

    <select id="getById" resultMap="BaseResultMap" parameterType="java.lang.Long">
        select
        <include refid="Base_Column_List"/>
        from pcloud_skill
        where id = #{id,jdbcType=BIGINT}
    </select>

    <delete id="deleteById" parameterType="long">
        delete from pcloud_skill
        where id = #{id,jdbcType=BIGINT}
    </delete>

    <insert id="insert" parameterType="com.pcloud.book.skill.entity.PcloudSkill" useGeneratedKeys="true" keyProperty="id">
        insert into pcloud_skill
        <trim prefix="(" suffix=")" suffixOverrides=",">
            skill_type,
            rel_skill_id,
            skill_fuse_reply,
            seq_num,
            create_time
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            #{skillType,jdbcType=INTEGER},
            #{relSkillId,jdbcType=BIGINT},
            #{skillFuseReply,jdbcType=VARCHAR},
            #{seqNum,jdbcType=INTEGER},
            NOW()
        </trim>
    </insert>

    <update id="update" parameterType="com.pcloud.book.push.entity.Push">
        update pcloud_skill
        <set>
            <if test="skillType != null">
                skill_type = #{skillType,jdbcType=INTEGER},
            </if>
            <if test="relSkillId != null">
                rel_skill_id = #{relSkillId,jdbcType=BIGINT},
            </if>
            <if test="skillFuseReply != null">
                skill_fuse_reply = #{skillFuseReply,jdbcType=VARCHAR},
            </if>
            <if test="seqNum != null">
                seq_num = #{seqNum,jdbcType=INTEGER},
            </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>

    <select id="getPcloudSkillList" parameterType="map" resultMap="BaseResultMap">
        SELECT
        s.id,
        s.skill_type,
        s.rel_skill_id,
        s.skill_fuse_reply,
        s.seq_num,
        s.create_time
        FROM
        pcloud_skill s
        LEFT JOIN time_control_task t ON s.rel_skill_id = t.task_id
        AND s.skill_type = 1
        LEFT JOIN pcloud_group_activity a ON s.rel_skill_id = a.id
        AND s.skill_type = 2
        LEFT JOIN pcloud_resource r ON s.rel_skill_id = r.id
        AND s.skill_type = 3
        WHERE  1=1
        <if test="skillType != null">
            and s.skill_type=#{skillType}
        </if>
        <if test="name != null">
            and (
            t.task_name like concat('%', #{name}, '%') or
            a.`name` like concat('%', #{name}, '%') or
            r.`name` like concat('%', #{name}, '%')
            )
        </if>
        <if test="taskName != null">
            and t.task_name like concat('%', #{taskName}, '%')
        </if>
        order by s.seq_num DESC,s.create_time DESC
    </select>

    <update id="updateSeqNum" parameterType="map">
        update pcloud_skill
        set seq_num=#{seqNum}
        where id=#{id}
    </update>

    <select id="getSkillList4TimeController" parameterType="map" resultType="com.pcloud.book.keywords.entity.RobotSkill4H5">
        select
        p.id,
        p.skill_type type,
        p.rel_skill_id relSkillId,
        t.task_name relSkillName,
        t.task_introduce relSkillIntroduce
        from pcloud_skill p
        left join time_control_task t
        on p.rel_skill_id = t.task_id
        where
        skill_type = 1
        <if test="labelId != null">
            and t.dep_label_id = #{labelId}
        </if>
        order by p.seq_num
    </select>

    <select id="getSkillList4Activity" parameterType="map" resultType="com.pcloud.book.keywords.entity.RobotSkill4H5">
        select
        p.id,
        p.skill_type type,
        p.rel_skill_id relSkillId,
        pa.name relSkillName,
        pa.description relSkillIntroduce
        from pcloud_skill p
        left join pcloud_group_activity pa
        on p.rel_skill_id = pa.id
        where
        skill_type = 2
        <if test="labelId != null">
            and pa.dep_label_id = #{labelId}
        </if>
        order by p.seq_num
    </select>

    <select id="getSkillList4Resource" parameterType="map" resultType="com.pcloud.book.keywords.entity.RobotSkill4H5">
        select
        p.id,
        p.skill_type type,
        p.rel_skill_id relSkillId,
        pr.name relSkillName,
        pr.description relSkillIntroduce
        from pcloud_skill p
        left join pcloud_resource pr
        on p.rel_skill_id = pr.id
        where
        skill_type = 3 and pr.is_delete = 0
        <if test="labelId != null">
            and pr.dep_label_id = #{labelId}
        </if>
        order by p.seq_num
    </select>

    <select id="getLabelList4Activity" parameterType="integer" resultType="long">
        select
        DISTINCT pa.dep_label_id
        from pcloud_skill p
        left join pcloud_group_activity pa
        on p.rel_skill_id = pa.id
        where
        skill_type = 2
        and
        pa.dep_label_id is not null
    </select>

    <select id="getLabelList4TimeController" parameterType="integer" resultType="long">
    select
    DISTINCT t.dep_label_id
    from pcloud_skill p
    left join time_control_task t
    on p.rel_skill_id = t.task_id
    where
    skill_type = 1
    and
    t.dep_label_id is not null
    </select>

    <select id="getLabelList4Resource" parameterType="integer" resultType="long">
        select
        DISTINCT pr.dep_label_id
        from pcloud_skill p
        left join pcloud_resource pr
        on p.rel_skill_id = pr.id
        where
        skill_type = 3 and pr.is_delete = 0
        and
        pr.dep_label_id is not null
    </select>

    <select id="getMaxSeqNum" resultType="integer">
        select ifnull(max(seq_num),0) from pcloud_skill
    </select>

    <select id="getByRelId" resultMap="BaseResultMap">
        select id, skill_type, rel_skill_id, skill_fuse_reply, seq_num
        from pcloud_skill where rel_skill_id = #{relSkillId} and skill_type = #{type}
        limit 1
    </select>

    <select id="getPcloudSkillList4Select" parameterType="map" resultMap="BaseResultMap">
        SELECT
        s.id,
        s.skill_type,
        s.rel_skill_id,
        s.skill_fuse_reply,
        s.seq_num
        FROM
        pcloud_skill s
        LEFT JOIN time_control_task t ON s.rel_skill_id = t.task_id
        AND s.skill_type = 1
        LEFT JOIN pcloud_group_activity a ON s.rel_skill_id = a.id
        AND s.skill_type = 2
        LEFT JOIN pcloud_resource r ON s.rel_skill_id = r.id
        AND s.skill_type = 3
        WHERE  1=1
        <if test="skillType != null">
            and s.skill_type=#{skillType}
        </if>
        <if test="name !=null">
           and (
            t.task_introduce like concat('%', #{name}, '%') or
            a.description like concat('%', #{name}, '%') or
            r.description like concat('%', #{name}, '%') or
            t.task_name like concat('%', #{name}, '%') or
            a.`name` like concat('%', #{name}, '%') or
            r.`name` like concat('%', #{name}, '%')
            )
        </if>
        <if test="depLabelId != null">
            and (
            t.dep_label_id = #{depLabelId} or
            a.dep_label_id = #{depLabelId} or
            r.dep_label_id = #{depLabelId}
            )
        </if>
        <if test="proLabelId != null">
            and (
            t.pro_label_id = #{proLabelId} or
            a.pro_label_id = #{proLabelId} or
            r.pro_label_id = #{proLabelId}
            )
        </if>
        <if test="purLabelId != null">
            and (
            t.pur_label_id = #{purLabelId} or
            a.pur_label_id = #{purLabelId} or
            r.pur_label_id = #{purLabelId}
            )
        </if>
        ORDER BY
        s.seq_num
    </select>


    <select id="getListByRelId" parameterType="integer" resultType="Long">
        select
        id
        from
        pcloud_skill
        where
        rel_skill_id = #{taskId}
    </select>
</mapper>
