<?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="im.zhaojun.system.mapper.DeptTreeMapper">

    <resultMap id="BaseResultMap" type="im.zhaojun.system.model.DeptTree">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="dept_id" jdbcType="INTEGER" property="deptId"/>
        <result column="dept_name" jdbcType="VARCHAR" property="deptName"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="order_num" jdbcType="INTEGER" property="orderNum"/>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
        <result column="modify_time" jdbcType="VARCHAR" property="modifyTime"/>
    </resultMap>


    <resultMap id="TreeBaseResultMap" type="im.zhaojun.system.model.DeptTree">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="dept_id" jdbcType="INTEGER" property="deptId"/>
        <result column="dept_name" jdbcType="VARCHAR" property="deptName"/>
        <result column="levels" jdbcType="VARCHAR" property="level"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="order_num" jdbcType="INTEGER" property="orderNum"/>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
        <result column="modify_time" jdbcType="VARCHAR" property="modifyTime"/>
        <!--<collection property="children" ofType="im.zhaojun.system.model.DeptTree" select="selectAllTree"-->
                    <!--column="{parent_id = dept_id}"/>-->
    </resultMap>

    <resultMap id="TreeBaseVideoResultMap" type="im.zhaojun.system.model.DeptTree">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="dept_id" jdbcType="INTEGER" property="deptId"/>
        <result column="dept_name" jdbcType="VARCHAR" property="deptName"/>
        <result column="levels" jdbcType="VARCHAR" property="level"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="order_num" jdbcType="INTEGER" property="orderNum"/>
    </resultMap>

    <sql id="Base_Column_List">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        dept_id, dept_name, parent_id, order_num, create_time, modify_time,levels
    </sql>

    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        select
        <include refid="Base_Column_List"/>
        from dept
        where dept_id = #{deptId}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        delete from dept
        where dept_id = #{deptId}
    </delete>

    <insert id="insert" keyColumn="dept_id" keyProperty="deptId" parameterType="im.zhaojun.system.model.DeptTree"
            useGeneratedKeys="true">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        insert into dept (dept_id,dept_name, parent_id, order_num,
        create_time, modify_time,levels)
        values (#{deptId,jdbcType=VARCHAR},#{deptName,jdbcType=VARCHAR}, #{parentId,jdbcType=INTEGER},
        #{orderNum,jdbcType=INTEGER},
        #{createTime,jdbcType=VARCHAR}, #{modifyTime,jdbcType=VARCHAR}, #{level,jdbcType=VARCHAR})
    </insert>

    <update id="updateByPrimaryKey" parameterType="im.zhaojun.system.model.DeptTree">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        update dept
        set dept_name = #{deptName,jdbcType=VARCHAR},
        modify_time = #{modifyTime,jdbcType=VARCHAR}
        where dept_id = #{deptId}
    </update>

    <select id="selectByParentId" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dept
        where parent_id = #{parentId}
        order by order_num
    </select>

    <select id="selectChildrenIDByPrimaryKey">
        select dept_id
        from dept
        where parent_id = #{deptId}
    </select>

    <select id="selectMaxOrderNum" resultType="int">
        select nvl(max(order_num), 0)
        from dept
    </select>

    <update id="swapSort">
        UPDATE
        dept AS per
        JOIN
        dept AS next
        ON
        (per.`dept_id` = #{currentId} AND next.`dept_id` = #{swapId})
        OR
        (per.`dept_id` = #{swapId} AND next.`dept_id` = #{currentId})
        SET per.`order_num` = next.`order_num`,
        next.`order_num` = per.`order_num`
    </update>

    <select id="selectAllTree" resultMap="TreeBaseResultMap">
        select
        t1.dept_id, t1.dept_name, t1.parent_id, t1.order_num, t1.create_time, t1.modify_time,t1.levels
        from dept t1  left join dept t2
        on t1.parent_id = t2.dept_id
        <where>
            <choose>
                <when test="parent_id!=null">
                    and t1.parent_id = #{parent_id}
                    or t1.dept_id =  #{parent_id}
                    or t2.parent_id =#{parent_id}
                    or t2.dept_id = #{parent_id}
                </when>
                <otherwise>
                    t1.parent_id = 0
                    or t1.dept_id =0
                    or t2.parent_id =0
                    or t2.dept_id =0
                </otherwise>
            </choose>
        </where>
        order by order_num
    </select>

    <select id="selectAllVideoTree" resultMap="TreeBaseVideoResultMap">
        select *
        from (select distinct t.dept_id dept_id,
        t.dept_name,
        t.parent_id  parent_id,
        levels,
        t.order_num
        from dept t

        union
        select distinct  t1.dept_id  dept_id,
        t1.dept_name,
        t1.parent_id  parent_id,
        t1.levels,
        t1.order_num
        from dept t1
        left join dept t2
        on t1.parent_id = t2.dept_id
        where t1.parent_id =-1
        or t1.dept_id =-1
        or t2.parent_id =-1
        or t2.dept_id =-1
        union
        select concat(sbbh , '_' , tdbh )dept_id,
        tdmc dept_name,
        xzbh parent_id,
        '3' levels,
        (SELECT count(*) FROM dept) + (@rownum := @rownum + 1) order_num
        from sbtdspsr,(SELECT @rownum:=0) r
        union
        select id  dept_id,
        b.name   dept_name,
        videoid parent_id,
        '4' levels,
        (SELECT count(*) FROM sbtdspsr) + (@rownum := @rownum + 1) order_num
        from videoerecordtype a
        left join t_code b
        on a.recordtype = b.key and  b.type=1) a,(SELECT @rownum:=0) r
        order by order_num

    </select>

    <select id="listeventByvideoid" resultType="java.lang.String">
        select videorecordtypeid
        from role_videoerecordtype a,user_role b
        where a.roleid =b.role_id and a.roleid = #{userId}
    </select>

    <select id="selectVideoeRecordType" resultType="im.zhaojun.system.model.VideoeRecordType">
        select a.*,(select  tdmc   from  sbtdspsr b  where  a.videoid=concat(b.sbbh ,'_',b.tdbh) limit  1) videoName,
        (select  name  from  t_code b   where  a.recordtype=b.key and  b.type=1)recordtypeName
        from videoerecordtype a
        where  a.videoid=#{videoId}

        <!--and c.xzbh  in(-->
        <!--select t1.dept_id  from  dept t1  left join  dept t2 on  t1.parent_id=t2.dept_id where  t1.parent_id= #{videoId}-->
        <!--or t1.dept_id = #{videoId})-->
    </select>

    <delete id="delvideorecord">
        delete from videoerecordtype where id=#{id}
    </delete>


</mapper>