<?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.DeptMapper">

    <resultMap id="BaseResultMap" type="im.zhaojun.system.model.Dept">
        <!--
          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"/>
        <result column="levels" jdbcType="VARCHAR" property="level"/>
    </resultMap>
    <resultMap id="TreeBaseVideoResultMap" type="im.zhaojun.system.model.Dept">
        <!--
          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.Dept" select="selectAllVideoTree" column="{parent_id = dept_id}"/>
    </resultMap>

    <resultMap id="TreeBaseResultMap" type="im.zhaojun.system.model.Dept">
        <!--
          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="nodes" ofType="im.zhaojun.system.model.Dept" select="selectAllTree" column="{parent_id = dept_id}"/>
    </resultMap>
    <resultMap id="VideoTreeBaseResultMap" type="im.zhaojun.system.model.DeptVideo">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="dept_id" jdbcType="VARCHAR" 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="vels" jdbcType="INTEGER" property="vels"/>
    </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" resultType="java.util.HashMap">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        select
        dept_id "deptId" , dept_name "deptName", parent_id "parentId", order_num "orderNum", create_time "createTime", modify_time "modifyTime",levels "level"
        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,jdbcType=INTEGER}
    </delete>

    <insert id="insert" parameterType="im.zhaojun.system.model.Dept">
        <!--
          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.Dept">
        <!--
          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,jdbcType=INTEGER}
    </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" resultType="java.lang.Integer">
        select dept_id
        from dept
        where parent_id = #{deptId, jdbcType=INTEGER}
    </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>
    <delete id="updateDefaultVideoByDeptId">
      DELETE from deptdefaultvideo where xzbh=#{deptId} and  videoid=#{videoId}
    </delete>

    <select id="selectAllTree" resultMap="TreeBaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from dept
        <where>
            <choose>
                <when test="parent_id!=null">
                    and parent_id = #{parent_id}
                </when>
                <otherwise>
                    and parent_id =0
                </otherwise>
            </choose>
        </where>
        order by order_num
    </select>

    <select id="listvideo" resultMap="VideoTreeBaseResultMap">
        select distinct t.dept_id  dept_id,
        t.dept_name ,
        parent_id  parent_id,
        levels  ,t.order_num,0 vels
        from dept t
        <if test="deptId != null and  deptId!=0">
            ,t_user a
            where a.dept_id = t.dept_id
            and a.dept_id = #{deptId}
            <if test="username != null and  username!='' ">
                and a.username  = #{username}
            </if>
        </if>
        union all
        select  a.* ,0 vels from (SELECT
        CONCAT_WS( '_' ,A .sbbh , tdbh) dept_id,
        A .tdmc dept_name,
        A .xzbh  parent_id,
        '' levels,
        (
        SELECT
        max(order_num)
        FROM
        dept
        ) + (@rownum := @rownum + 1) order_num
        FROM
        dept T,(SELECT @rownum:=0) r ,
        sbtdspsr A where
        T .dept_id = A .xzbh
        <if test="tdmc != null and  tdmc!='' ">
            and tdmc  like '%${tdmc}%'
        </if>)a
    </select>


    <select id="listAllvideoIdsByDeptid" resultType="java.util.HashMap">
      select concat(sbbh,'_',tdbh) vidoid
       from  sbtdspsr a
        where  a.xzbh in (
       select t1.dept_id
        from dept t1
        left join dept t2
        on t1.parent_id = t2.dept_id
        where t1.parent_id =#{deptId}
        or t1.dept_id = #{deptId}
        or t2.parent_id =#{deptId}
        or t2.dept_id = #{deptId})

    </select>


    <select id="selectDeptChildren" resultMap="BaseResultMap" >
         select t1.*
           from dept t1
        left join dept t2
        on t1.parent_id = t2.dept_id
        where t1.parent_id =  #{deptId}
        or t1.dept_id =  #{deptId}
        or t2.parent_id =#{deptId}
        or t2.dept_id = #{deptId}
    </select>


    <select id="selectDefaultVideoByDeptId" resultType="java.util.HashMap" >
    select
           b.videoid  "videoid",
            (select dept_name
              from dept c
             where c.dept_id = b.xzbh
               limit 1) "deptname",
                 (select tdmc
              from sbtdspsr c
             where  concat(c.sbbh,'_',tdbh) = b.videoid
               limit 1) "videoname"
      from deptdefaultvideo b
              where b.xzbh=#{deptId}
    </select>

    <select id="selectAllDefaultVideo" resultType="java.util.HashMap" >
        select dept_Id "deptId",
            (select dept_name
              from dept b
             where b.deptid_id = a.dept_id
               limit 1) "deptname",
           b.videoid  "videoid",
           (select video_name
              from video b
             where b.video_id = b.videoid
               limit 1) "videoname"
      from (select t1.* from dept t1
        left join dept t2
        on t1.parent_id = t2.dept_id
        where t1.parent_id = #{deptId}
        or t1.dept_id =  #{deptId}
        or t2.parent_id =#{deptId}
        or t2.dept_id = #{deptId}
        ) a ,deptdefaultvideo b
          where a.dept_id=b.xzbh
    </select>

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

    select  count(0) from deptdefaultvideo a where xzbh=#{deptId} and videoid=#{videoId}

</select>

    <insert id="insertDefaultVideo" >
        insert into deptdefaultvideo (xzbh,videoid)
        values (#{deptId,jdbcType=VARCHAR},#{videoId,jdbcType=VARCHAR})
    </insert>

    <select id="selectAllVideoTree" resultMap="TreeBaseVideoResultMap">
        select
        <include refid="Base_Column_List"/>
        from
        (
        SELECT concat(A.sbbh, '_',tdbh) dept_id,
        A.tdmc dept_name,
        TO_CHAR(a.xzbh) parent_id,
        to_number(t.levels) + 1  LEVELS,
        (select max(order_num) from dept) + (@rownum := @rownum + 1) order_num,
        a.cjrq create_time,
        a.xgrq modify_time
        from dept T, sbtdspsr a,(select @rownum:=0) r
        where T.dept_id = a.xzbh
        union
        select distinct  t1.dept_id  dept_id,
        t1.dept_name,
        t1.parent_id parent_id,
        t1.levels,
        t1.order_num,
        t1. create_time,
        t1.modify_time
        from   dept t1
        left join dept t2
        on t1.parent_id = t2.dept_id
        where t1.parent_id = 0
        or t1.dept_id = 0  or t2.parent_id =0
        or t2.dept_id = 0)a
        <where>
            <choose>
                <when test="parent_id!=null">
                    and  a.parent_id = #{parent_id,jdbcType=INTEGER}
                </when>
                <otherwise>
                    and a.parent_id =0
                </otherwise>
            </choose>
        </where>
        order by order_num
    </select>

</mapper>