<?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.jsh.erp.datasource.mappers.UserMapperEx">
    <resultMap extends="com.jsh.erp.datasource.mappers.UserMapper.BaseResultMap" id="ResultMapEx"
               type="com.jsh.erp.datasource.entities.UserEx">
        <result column="orgaId" jdbcType="BIGINT" property="orgaId"/>
        <result column="org_abr" jdbcType="VARCHAR" property="orgAbr"/>
        <result column="user_blng_orga_dspl_seq" jdbcType="VARCHAR" property="userBlngOrgaDsplSeq"/>
        <result column="orgaUserRelId" jdbcType="BIGINT" property="orgaUserRelId"/>
    </resultMap>
    <select id="selectByConditionUser" parameterType="com.jsh.erp.datasource.entities.UserExample"
            resultMap="com.jsh.erp.datasource.mappers.UserMapper.BaseResultMap">
        select *
        FROM jsh_user
        where 1=1
        and ifnull(status,'0') not in('1','2')
        <if test="userName != null and userName != ''">
            <bind name="userName" value="'%' + _parameter.userName + '%'"/>
            and username like #{userName}
        </if>
        <if test="loginName != null and loginName != ''">
            <bind name="loginName" value="'%' + _parameter.loginName + '%'"/>
            and loginame like #{loginName}
        </if>
        <if test="offset != null and rows != null">
            limit #{offset},#{rows}
        </if>
    </select>
    <select id="countsByUser" resultType="java.lang.Long">
        SELECT
        COUNT(id)
        FROM jsh_user
        WHERE 1=1
        and ifnull(status,'0') not in('1','2')
        <if test="userName != null and userName != ''">
            <bind name="userName" value="'%' + _parameter.userName + '%'"/>
            and username like #{userName}
        </if>
        <if test="loginName != null and loginName != ''">
            <bind name="loginName" value="'%' + _parameter.loginName + '%'"/>
            and loginame like #{loginName}
        </if>
    </select>
    <select id="getUserList" parameterType="java.util.Map" resultMap="ResultMapEx">
        select user.id, user.username, user.loginame, user.position, user.email, user.phonenum,
        user.description, user.remark,user.isystem,org.id as
        orgaId,user.tenant_id,org.org_abr,rel.user_blng_orga_dspl_seq,
        rel.id as orgaUserRelId
        FROM jsh_user user
        left join jsh_orga_user_rel rel on user.id=rel.user_id and ifnull(rel.delete_flag,'0') !='1'
        left join jsh_organization org on rel.orga_id=org.id and ifnull(org.org_stcd,'0') !='5'
        where 1=1
        and ifnull(user.status,'0') not in('1','2')
        <if test="userName != null and userName != ''">
            <bind name="userName" value="'%' + _parameter.userName + '%'"/>
            and user.userName like #{userName}
        </if>
        <if test="loginName != null and loginName != ''">
            <bind name="loginName" value="'%' + _parameter.loginName + '%'"/>
            and user.loginName like #{loginName}
        </if>
        order by user.id desc
    </select>
    <insert id="addUser" parameterType="com.jsh.erp.datasource.entities.UserEx"
            useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        insert into jsh_user (username, loginame,
        password, position,
        email, phonenum, ismanager,
        isystem, status, description,
        remark)
        values (#{username,jdbcType=VARCHAR}, #{loginame,jdbcType=VARCHAR},
        #{password,jdbcType=VARCHAR}, #{position,jdbcType=VARCHAR},
        #{email,jdbcType=VARCHAR}, #{phonenum,jdbcType=VARCHAR}, #{ismanager,jdbcType=TINYINT},
        #{isystem,jdbcType=TINYINT}, #{status,jdbcType=TINYINT}, #{description,jdbcType=VARCHAR},
        #{remark,jdbcType=VARCHAR})
    </insert>
    <update id="updateUser" parameterType="com.jsh.erp.datasource.entities.UserEx">
        update jsh_user
        <set>
            <if test="username != null">
                username = #{username,jdbcType=VARCHAR},
            </if>
            <if test="loginame != null">
                loginame = #{loginame,jdbcType=VARCHAR},
            </if>
            <if test="password != null">
                password = #{password,jdbcType=VARCHAR},
            </if>
            <if test="position != null">
                position = #{position,jdbcType=VARCHAR},
            </if>
            <if test="department != null">
                department = #{department,jdbcType=VARCHAR},
            </if>
            <if test="email != null">
                email = #{email,jdbcType=VARCHAR},
            </if>
            <if test="phonenum != null">
                phonenum = #{phonenum,jdbcType=VARCHAR},
            </if>
            <if test="ismanager != null">
                ismanager = #{ismanager,jdbcType=TINYINT},
            </if>
            <if test="isystem != null">
                isystem = #{isystem,jdbcType=TINYINT},
            </if>
            <if test="status != null">
                status = #{status,jdbcType=TINYINT},
            </if>
            <if test="description != null">
                description = #{description,jdbcType=VARCHAR},
            </if>
            <if test="remark != null">
                remark = #{remark,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=BIGINT}
    </update>
    <select id="getUserListByUserNameOrLoginName" resultMap="com.jsh.erp.datasource.mappers.UserMapper.BaseResultMap">
        select user.id, user.username, user.loginame, user.position, user.email, user.phonenum,
        user.description, user.remark,user.isystem
        FROM jsh_user user
        where 1=1
        and ifnull(user.status,'0') not in('1','2')
        <if test="userName != null and userName != ''">
            and user.userName = #{userName}
        </if>
        <if test="loginame != null and loginame != ''">
            and user.loginame = #{loginame}
        </if>
        order by user.id desc
    </select>
    <update id="batDeleteOrUpdateUser">
        update jsh_user
        set status=#{status}
        where id in (
        <foreach collection="ids" item="id" separator=",">
            #{id}
        </foreach>
        )
    </update>
    <resultMap id="BaseTreeResultMap" type="com.jsh.erp.datasource.vo.TreeNodeEx">
        <result column="id" property="id"/>
        <result column="text" property="text"/>
        <association property="attributes" javaType="com.jsh.erp.datasource.vo.NodeAttributes">
            <id column="orgNo" property="no"></id>
            <result column="type" property="type"></result>
        </association>
        <collection column="{orgId=id,orgNo=orgNo}" property="children" javaType="java.util.ArrayList"
                    ofType="com.jsh.erp.datasource.vo.TreeNode" select="getNextNodeTree"/>
    </resultMap>

    <resultMap id="NextTreeResultMap" type="com.jsh.erp.datasource.vo.TreeNodeEx">
        <result column="id" property="id"/>
        <result column="text" property="text"/>
        <result column="iconCls" property="iconCls"/>
        <association property="attributes" javaType="com.jsh.erp.datasource.vo.NodeAttributes">
            <id column="orgNo" property="no"></id>
            <result column="type" property="type"></result>
        </association>
        <collection column="{orgId=id,orgNo=orgNo}" property="children" javaType="java.util.ArrayList"
                    ofType="com.jsh.erp.datasource.vo.TreeNode" select="getNextNodeTree"/>
    </resultMap>

    <select id="getNextNodeTree" resultMap="NextTreeResultMap">
        select id ,	text,orgNo,sort ,iconCls,type from (
            SELECT
            org.id, org.org_abr as text,org.org_no as orgNo,org.sort as sort,null as iconCls,'0' as type
            FROM jsh_organization org
            WHERE org.org_parent_no = #{orgNo}
            and ifnull(org.org_stcd,'0') !='5'
            union all
            select
            user.id,user.username as text, null as orgNo,rel.user_blng_orga_dspl_seq as sort,'icon-user' as iconCls,'1' as type
            from jsh_user user,jsh_orga_user_rel rel
            where
            1=1
            and user.id=rel.user_id
            and rel.orga_id=#{orgId}
            and ifnull(rel.delete_flag,'0') !='1'
            and ifnull(user.status,'0') not in('1','2')
          ) node
          order by sort asc
    </select>

    <select id="getNodeTree" resultMap="BaseTreeResultMap">
        SELECT
         id, org_abr as text,org_no as orgNo,'0' as type
        FROM jsh_organization
        WHERE org_parent_no = -1
        and ifnull(org_stcd,'0') !='5'
        order by sort asc
    </select>
    <select id="getUserListByUserNameAndTenantId" resultMap="com.jsh.erp.datasource.mappers.UserMapper.BaseResultMap">
        select user.id, user.username, user.loginame, user.position, user.email, user.phonenum,
        user.description, user.remark,user.isystem
        FROM jsh_user user
        where 1=1
        and ifnull(user.status,'0') not in('1','2')
        <if test="userName != null and userName != ''">
            and user.userName = #{userName}
        </if>
        <choose>
            <when test="tenantId != null">
                and user.tenant_id = #{tenantId}
            </when>
            <otherwise>
                AND user.tenant_id is null
            </otherwise>
        </choose>
        order by user.id desc
    </select>
    <select id="addRegisterUserNotInclueUser" resultType="java.lang.String">
      select registerUserTemplate(#{userId},#{tenantId},#{roleId}) from dual;
    </select>


</mapper>