<?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.yonge.cooleshow.biz.dal.dao.MusicTagDao">
    <resultMap id="BaseResultMap" type="com.yonge.cooleshow.biz.dal.entity.MusicTag">
        <result column="id_" property="id" />
        <result column="parent_tag_id_" property="parentTagId" />
        <result column="name_" property="name" />
        <result column="state_" property="state" />
        <result column="del_flag_" property="delFlag" />
        <result column="create_time_" property="createTime" />
        <result column="update_time_" property="updateTime" />
        <result column="update_by_" property="updateBy" />
        <result column="create_by_" property="createBy" />
    </resultMap>

    <!-- 表字段 -->
    <sql id="baseColumns">
         t.id_ as id
        , t.parent_tag_id_ as parentTagId
        , t.name_ as name
        , t.state_ as state
        , t.del_flag_ as delFlag
        , t.create_time_ as createTime
        , t.update_time_ as updateTime
        , t.update_by_ as updateBy
        , t.create_by_ as createBy
        </sql> 
    
    <select id="detail" resultType="com.yonge.cooleshow.biz.dal.vo.MusicTagVo">
        SELECT
            <include refid="baseColumns"/>
        FROM music_tag t
        where t.id_ = #{id}
    </select>
    
    <select id="selectPage"  resultType="com.yonge.cooleshow.biz.dal.vo.MusicTagVo">
		SELECT         
        	<include refid="baseColumns" />,
            su.username_ as updateName
		FROM music_tag t
        left join sys_user su on t.update_by_ = su.id_
        <where>
            t.parent_tag_id_ = 0 and t.del_flag_ = 0
            <if test="param.state != null">
                and t.state_ = #{param.state}
            </if>
            <if test="param.idAndName != null and param.idAndName != ''">
                and ( t.id_ like concat( '%',#{param.idAndName},'%') or
                    t.name_ like  concat ('%',#{param.idAndName},'%') or
                    exists(select mt.parent_tag_id_ from music_tag mt
                        where (mt.name_ like concat( '%',#{param.idAndName},'%') or mt.id_ like concat('%',#{param.idAndName},'%') )
                        and mt.parent_tag_id_ = t.id_ and mt.del_flag_ = 0
                    )
                    )
            </if>
        </where>
	</select>

	<select id="getChildren" resultType="com.yonge.cooleshow.biz.dal.vo.MusicTagVo">
        SELECT
        <include refid="baseColumns" />,
        su.username_ as updateName
        FROM music_tag t
        left join sys_user su on t.update_by_ = su.id_
        <where>
            t.del_flag_ = 0
            <if test="id != null">
                and t.parent_tag_id_ = #{id}
            </if>
            <if test="state != null">
                and t.state_ = #{state}
            </if>
            <if test="idAndName != null and idAndName != ''">
                and ( t.id_ like concat( '%',#{idAndName},'%') or
                t.name_ like  concat ('%',#{idAndName},'%')
            </if>
        </where>
    </select>

	<select id="getChildrenByParentIdList" resultType="com.yonge.cooleshow.biz.dal.vo.MusicTagVo">
        SELECT
        <include refid="baseColumns" />,
        su.username_ as updateName
        FROM music_tag t
        left join sys_user su on t.update_by_ = su.id_
        <where>
            t.del_flag_ = 0
            <if test="longList != null and longList.size() != 0">
                and t.parent_tag_id_ in
                <foreach collection="longList" close=")" item="item" open="(" separator=",">
                    #{item}
                </foreach>
            </if>
            <if test="state != null">
                and t.state_ = #{state}
            </if>
        </where>
    </select>

    <select id="checkTagBeUsedMusicSheet" resultType="java.lang.Integer">

        select count(1) from music_sheet ms
        <where>
            ms.del_flag_ = 0
            <if test="longs != null and longs.size() != 0">
                and
                <foreach collection="longs" item="item" open="(" close=")"  separator="or">
                    find_in_set(#{item},ms.music_tag_)
                </foreach>
            </if>
        </where>
    </select>

    <select id="checkTagBeUsedMusicAblum" resultType="java.lang.Integer">

        select count(1) from music_album ms
        <where>
            ms.del_flag_ = 0
            <if test="longs != null and longs.size() != 0">
                and
                <foreach collection="longs" item="item" open="(" close=")"  separator="or">
                    find_in_set(#{item},ms.album_tag_)
                </foreach>
            </if>
        </where>
    </select>
</mapper>