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

    <resultMap id="BaseResultMap" type="im.zhaojun.system.model.User">
        <id column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="username" jdbcType="VARCHAR" property="username"/>
        <result column="password" jdbcType="VARCHAR" property="password"/>
        <result column="salt" jdbcType="VARCHAR" property="salt"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="status" jdbcType="CHAR" property="status"/>
        <result column="last_login_time" jdbcType="VARCHAR" property="lastLoginTime"/>
        <result column="create_time" jdbcType="VARCHAR" property="createTime"/>
        <result column="modify_time" jdbcType="VARCHAR" property="modifyTime"/>
        <result column="dept_id" jdbcType="INTEGER" property="deptId"/>
        <result column="realname" jdbcType="VARCHAR" property="realName"/>
        <result column="lxdh" jdbcType="VARCHAR" property="lxdh"/>
    </resultMap>

    <resultMap id="TreeBaseResultMap" type="im.zhaojun.system.model.Menu">
        <!--
          WARNING - @mbg.generated
          This element is automatically generated by MyBatis Generator, do not modify.
        -->
        <id column="menu_id" jdbcType="INTEGER" property="menuId"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="menu_name" jdbcType="VARCHAR" property="menuName"/>
        <result column="url" jdbcType="VARCHAR" property="url"/>
        <result column="perms" jdbcType="VARCHAR" property="perms"/>
        <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="icon" jdbcType="VARCHAR" property="icon"/>
        <collection property="children" ofType="im.zhaojun.system.model.Menu" select="selectAllTree" column="{parent_id = menu_id}"/>
    </resultMap>


    <sql id="Base_Column_List">
            user_id,
            username,
            password,
            salt,
            email,
            status,
            last_login_time,
            create_time,
            modify_time,
            active_code,
            dept_id,realname,lxdh
    </sql>

    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from t_user
        where user_id = #{userId,jdbcType=INTEGER}
    </select>

    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete
        from t_user
        where user_id = #{userId,jdbcType=INTEGER}
    </delete>

    <insert id="insert"  parameterType="im.zhaojun.system.model.User"  >
        insert into t_user ( user_id, username, password, salt,
                          email, status, last_login_time, active_code, dept_id,realname,lxdh,create_time,modify_time )
        select  max(user_id)+1,#{username,jdbcType=VARCHAR}, #{password,jdbcType=VARCHAR}, #{salt,jdbcType=VARCHAR},#{email,jdbcType=VARCHAR}, #{status,jdbcType=CHAR}, #{lastLoginTime,jdbcType=VARCHAR},#{activeCode,jdbcType=VARCHAR}, #{deptId,jdbcType=INTEGER}, #{realName,jdbcType=VARCHAR},#{lxdh,jdbcType=VARCHAR},#{createTime,jdbcType=VARCHAR} ,#{modifyTime,jdbcType=VARCHAR} from  t_user
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="im.zhaojun.system.model.User">
        update t_user
        <set>
            <if test="username != null">
                username = #{username,jdbcType=VARCHAR},
            </if>
            <if test="password != null">

                password = #{password,jdbcType=VARCHAR},
            </if>
            <if test="salt != null">
                salt = #{salt,jdbcType=VARCHAR},
            </if>
            <if test="email != null">
                email = #{email,jdbcType=VARCHAR},
            </if>
            <if test="status != null">
                status = #{status,jdbcType=CHAR},
            </if>
            <if test="lastLoginTime != null">
                last_login_time = #{lastLoginTime,jdbcType=VARCHAR},
            </if>
            <if test="deptId != null and deptId !=''">
                dept_id = #{deptId,jdbcType=INTEGER},
            </if>
            <if test="realName != null and realName !=''">
                realname = #{realName,jdbcType=VARCHAR},
            </if>
            <if test="lxdh != null">
                lxdh = #{lxdh,jdbcType=VARCHAR},
            </if>
            <if test="modifyTime != null">
                modify_time = #{modifyTime,jdbcType=VARCHAR},
            </if>
        </set>
        where user_id = #{userId,jdbcType=INTEGER} limit 1
    </update>
    <update id="updateByPrimaryKey" parameterType="im.zhaojun.system.model.User">
        update t_user
        set username        = #{username,jdbcType=VARCHAR},
            password        = #{password,jdbcType=VARCHAR},
            salt            = #{salt,jdbcType=VARCHAR},
            email           = #{email,jdbcType=VARCHAR},
            status          = #{status,jdbcType=CHAR},
            last_login_time = #{lastLoginTime,jdbcType=VARCHAR},
            dept_id         = #{deptId,jdbcType=INTEGER},
            realname        = #{realName,jdbcType=VARCHAR},
            lxdh        = #{lxdh,jdbcType=VARCHAR}
        where user_id = #{userId,jdbcType=INTEGER}
    </update>

    <select id="selectPermsByUserName" resultType="String" parameterType="String">
        select menu.perms
        from t_user t,
             role,
             user_role,
             menu,
             role_menu
        where t.user_id = user_role.user_id
          and role.role_id = user_role.role_id
          and role.role_id = role_menu.role_id
          and menu.menu_id = role_menu.menu_id
          and t.username = #{username, jdbcType=VARCHAR}
    </select>

    <select id="selectRoleNameByUserName" resultType="String" parameterType="String">
        select role.role_name
        from t_user t,
             role,
             user_role
        where t.user_id = user_role.user_id
          and role.role_id = user_role.role_id
          and t.username = #{username,jdbcType=VARCHAR}
    </select>

    <select id="selectOneByUserName" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from t_user
        where username=#{username,jdbcType=VARCHAR}
    </select>


    <select id="selectAll" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from t_user
    </select>

    <update id="updateStatusByPrimaryKey">
        update t_user
        set status = #{status}
        where user_id = #{id}
    </update>

    <update id="updateLastLoginTimeByUsername">
        update t_user
        set last_login_time =  #{time}
        where username = #{username}

    </update>

    <select id="countByUserName" resultType="java.lang.Integer">
        select count(*)
        from t_user
        where username = #{username}
    </select>

    <select id="countByUserNameNotIncludeUserId" resultType="java.lang.Integer">
        select count(*)
        from t_user
        where username = #{username}
          and user_id != #{userId}
    </select>

    <select id="selectRoleIdsByUserId" resultType="java.lang.Integer">
        select role_id
        from user_role
        where user_id = #{userId}
    </select>

    <select id="selectByActiveCode" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from user where active_code = #{activeCode}
    </select>

    <select id="count" resultType="java.lang.Integer">
        select count(*)
        from t_user
    </select>

    <select id="selectOperatorPermsByUserName" resultType="String" parameterType="String">
        select operator.perms
        from t_user t,
             role,
             user_role,
             operator,
             role_operator
        where t.user_id = user_role.user_id
          and role.role_id = user_role.role_id
          and role.role_id = role_operator.role_id
          and operator.operator_id = role_operator.operator_id
          and t.username = #{username, jdbcType=VARCHAR}
    </select>

    <update id="updatePasswordByUserId">
        update t_user
        set password = #{password},
            salt     = #{salt}
        where user_id = #{userId}
    </update>


    <select id="selectAllWithDept" resultMap="BaseResultMap">
        select u.user_id,
               u.username,
               u.password,
               u.salt,
               u.email,
               u.status,
               u.last_login_time,
               u.create_time,
               u.modify_time,
               u.active_code,
               u.dept_id,
               u.realname,
               u.lxdh,
               d.dept_name
        from t_user u
                 left join dept d on u.dept_id = d.dept_id
        <where>
            <if test="username != null and username != ''">
                AND u.username = #{username}
            </if>
            <if test="email != null and email != ''">
                AND u.email = #{email}
            </if>
            <if test="status != null and status != ''">
                AND u.status = #{status}
            </if>
            <if test="deptId != null and deptId != ''">
                AND d.dept_id = #{deptId}
            </if>
        </where>
    </select>
    <select id="selectAllWithDeptId" resultMap="BaseResultMap">
        select u.user_id,
        u.username,
        u.password,
        u.salt,
        u.email,
        u.status,
        u.last_login_time,
        u.create_time,
        u.modify_time,
        u.active_code,
        u.dept_id,
        u.lxdh,
        u.realname,
        d.dept_name
        from t_user u
        left join dept d on u.dept_id = d.dept_id
        <where>
            <if test="userName != null and userName != ''">
                AND u.username  like '%${userName}%'
            </if>
            <if test="deptId != null and deptId != ''">
                AND d.dept_id = #{deptId}
            </if>
        </where>
    </select>



    <update id="activeUserByUserId">
        update t_user
        set status = 1
        where user_id = #{userId}
    </update>

    <select id="selectUserFirstMenu" resultType="java.util.HashMap">
        <if test="username!=null and username!='' ">
            select  distinct d.*
            from t_user a, user_role b, role_menu c, menu d
            where a.user_id = b.user_id
            and b.role_id = c.role_id
            and c.menu_id = d.menu_id
            and d.parent_id =0
            and a.username =#{username}
            order by d.order_num desc
        </if>
        <if test="username==null or username=='' ">
            select  d.*
            from   menu d
            where d.parent_id =0
            order by d.order_num desc

        </if>
    </select>

    <select id="selectUserSecondMenu" resultType="java.util.HashMap">
        <if test="username!=null and username!='' ">
            select  distinct d.*
            from t_user a, user_role b, role_menu c, menu d
            where a.user_id = b.user_id
            and b.role_id = c.role_id
            and c.menu_id = d.menu_id
            and d.parent_id =#{menuid}
            and a.username =#{username}
            order by d.order_num desc
        </if>
        <if test="username==null or username=='' ">
            select  d.*
            from   menu d
            where d.parent_id  =#{menuid}
            order by d.order_num desc

        </if>
    </select>




</mapper>