<?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.vctgo.system.mapper.SysTagManagementMapper">

    <resultMap type="com.vctgo.system.domain.SysTagManagement" id="SysTagManagementResult">
        <result property="id"    column="id"    />
        <result property="serviceName"    column="service_name"    />
        <result property="tagName"    column="tag_name"    />
        <result property="tagNum"    column="tag_num"    />
        <result property="tagStatus"    column="tag_status"    />
        <result property="remark"    column="remark"    />
        <result property="isDeleted"    column="is_deleted"    />
        <result property="createById"    column="create_by_id"    />
        <result property="createBy"    column="create_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateById"    column="update_by_id"    />
        <result property="updateBy"    column="update_by"    />
        <result property="updateTime"    column="update_time"    />
        <result property="tenantId"    column="tenant_id"    />
    </resultMap>

    <sql id="selectSysTagManagementVo">
        select id, service_name, tag_name, tag_num, tag_status, remark, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from sys_tag_management
    </sql>

    <sql id="defaultDeleteCondition">
        is_deleted = 0
    </sql>

    <select id="selectSysTagManagementList"   resultMap="SysTagManagementResult">
        <include refid="selectSysTagManagementVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="query.tagName != null  and query.tagName != ''"> and tag_name like concat('%', #{query.tagName}, '%')</if>
            <if test="query.tagStatus != null  and query.tagStatus != ''"> and tag_status = #{query.tagStatus}</if>
            <if test="query.createBy != null  and query.createBy != ''">
                and create_by_id in (select user_id from sys_user where nick_name like concat('%', #{query.createBy}, '%'))</if>
            <if test="query.params.beginCreateTime != null and query.params.beginCreateTime != ''">
                and date_format(create_time,'%y%m%d') &gt;= date_format(#{query.params.beginCreateTime},'%y%m%d')
            </if>
            <if test="query.params.endCreateTime != null and query.params.endCreateTime != ''">
                and date_format(create_time,'%y%m%d') &lt;= date_format(#{query.params.endCreateTime},'%y%m%d')
            </if>
            <if test="query.tenantId != null "> and tenant_id = #{query.tenantId}</if>
            <if test="query.params.tagIdList != null  and query.params.tagIdList.size >0 ">
                and id in
                <foreach collection="query.params.tagIdList" item="tagId" index="i" open="(" close=")" separator=",">
                    #{tagId}
                </foreach>
            </if>
        </where>
        order by create_time desc
    </select>

    <select id="selectTagListByParams"   resultMap="SysTagManagementResult">
        <include refid="selectSysTagManagementVo"/>
        <where>
            <include refid="defaultDeleteCondition"/>
            <if test="params.tagName != null  and params.tagName != ''"> and tag_name = #{params.tagName}</if>
            <if test="params.tagId != null  and params.tagId != ''"> and id != #{params.tagId}</if>
        </where>
    </select>

    <select id="getAllTag" resultType="com.vctgo.system.domain.SysTagManagement">
        select id, service_name, tag_name, tag_num, tag_status, remark, is_deleted, create_by_id, create_by, create_time, update_by_id, update_by, update_time, tenant_id from sys_tag_management
        where is_deleted = 0
        <if test="query.tagName != null  and query.tagName != ''">  and tag_name like concat('%', #{query.tagName}, '%')</if>
    </select>

    <update id="multiUpdateTagCount">
        update sys_tag_management tag,
        (select tag_id,IFNULL(rel_2.count,0) count
        from sys_tag_management tag_rel
        left join (
            select tag_id, sum(count) count from
            (select related_tag_id tag_id, count(*) count
            from activity.activity_re_tag_info
            where is_deleted='0'
            <if test="tenantId != null "> and tenant_id = #{tenantId}</if>
            group by related_tag_id
            union all
            select tag_id tag_id, count(*) count
            from activity.course_re_template_tag
            where is_deleted='0'
            <if test="tenantId != null "> and tenant_id = #{tenantId}</if>
            group by tag_id) all_tag
            group by all_tag.tag_id) rel_2
        on rel_2.tag_id = tag_rel.id
        ) rel
        set tag.tag_num=rel.count
        where tag.id=rel.tag_id
    </update>

    <update id="batchUpdateTagCount" parameterType="java.util.List">
        UPDATE sys_tag_management
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="tag_num=case" suffix="end,">
                <foreach collection="tagList" item="i" index="index">
                    <if test="i.id != null">
                        when id = #{i.id} then #{i.tagNum}
                    </if>
                </foreach>
            </trim>
        </trim>
        where
        <foreach collection="tagList" separator="or" item="i" index="index">
            id = #{i.id}
        </foreach>
    </update>

    <update id="logicalDeleteSysTagManagementByIds">
        update sys_tag_management set is_deleted = #{query.isDeleted},
        update_by_id = #{query.updateById}, update_by = #{query.updateBy},
        update_time = #{query.updateTime} where id in
        <foreach item="id" collection="ids" open="(" separator="," close=")">
            #{id}
        </foreach>
    </update>

    <delete id="deleteSysTagManagementByIds" parameterType="String">
        delete from sys_tag_management where id in
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
</mapper>
