<?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.chinaweal.aiccs.org.mapper.TUsersMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.chinaweal.aiccs.org.entity.TUsers">
        <id column="userid" property="userid"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="realname" property="realname"/>
        <result column="locked" property="locked"/>
        <result column="deleted" property="deleted"/>
        <result column="orgdeptid" property="orgdeptid"/>
        <result column="orgunitid" property="orgunitid"/>
        <result column="orgjobid" property="orgjobid"/>
        <result column="userprop" property="userprop"/>
        <result column="userstatus" property="userstatus"/>
        <result column="gradenum" property="gradenum"/>
        <result column="customindex" property="customindex"/>
        <result column="limittnum" property="limittnum"/>
        <result column="lasterrortime" property="lasterrortime"/>
    </resultMap>

    <resultMap id="AICUserMap" type="com.chinaweal.aicorg.model.AICUser">
        <id column="primaryKey" property="primaryKey"/>
        <result column="name" property="name"/>
        <result column="ename" property="ename"/>
        <result column="empId" property="empID"/>
        <result column="unitId" property="unitID"/>
        <result column="unitName" property="unitName"/>
        <result column="orgId" property="orgID"/>
        <result column="orgName" property="orgName"/>
        <result column="regionId" property="regionID"/>
        <result column="password" property="password"/>
        <result column="status" property="status"/>
        <result column="userProp" property="userProp"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        userid
        , username, password, realname, locked, deleted, orgdeptid, orgunitid, orgjobid, userprop, userstatus, gradenum, customindex
    </sql>

    <update id="updateTusers" parameterType="com.chinaweal.aiccs.org.entity.TUsers">
        UPDATE t_users
        <set>
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
            <if test="realname!=null">
                realname=#{realname},
            </if>
            <if test="limittnum!=null">
                limittnum=#{limittnum},
            </if>
            <if test="locked!=null">
                locked=#{locked},
            </if>
            <if test="lasterrortime!=null">
                lasterrortime=#{lasterrortime},
            </if>
        </set>
        WHERE userID = #{userid}
    </update>

    <select id="selectTusersByUsernameAndPass" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT *
        from t_users
        where locked = '0' and deleted = '0'
          and username = #{username}
          and password = #{passwordJm}
    </select>

    <select id="selectTusersByUsername" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT *
        from t_users
        where username = #{username} and deleted = '0'
    </select>

    <select id="selectTusersById" parameterType="java.lang.String" resultMap="BaseResultMap">
        SELECT *
        from t_users
        where userId = #{userId}
    </select>
    <select id="byOrgUnitName" resultType="com.chinaweal.aiccs.org.entity.OrgUnits">
        SELECT *
        from cxaicorg.t_orgunits
        where orgUnitName = #{orgUnitName} and deleted = '0' and unitType = '1'
    </select>
    <select id="byOrgUnitId" resultType="com.chinaweal.aiccs.org.entity.OrgUnits">
        SELECT *
        from cxaicorg.t_orgunits
        where orgUnitId = #{orgUnitId}
          and deleted = '0'
    </select>
    <select id="queryAllOrg" resultType="com.chinaweal.aiccs.org.entity.OrgUnits">
        select * from cxaicorg.t_orgunits
         where orgnumber like '15%' and orglevel in (2,3)
        and length(orgnumber) = 6
    </select>
    <select id="listOrgParentId" resultType="com.chinaweal.aiccs.org.entity.OrgUnits">
        select *
        from cxaicorg.t_orgunits
        where parentId = #{parentId}
          and orglevel = #{orgLevel}
          and deleted = '0'
    </select>

    <select id="findUsersByDeptAndPermission" resultType="com.chinaweal.aicorg.model.AICUser">
        select a.userID      primaryKey,
               a.userName    ename,
               a.password as password,
               a.realName    name,
               b.orgUnitId   orgID,
               b.orgUnitName orgName,
               c.orgUnitID   unitID,
               c.orgUnitName unitName,
               b.orgNumber   regionID,
               a.userProp    userProp
        from t_users a,
             t_orgUnits b,
             t_orgUnits c,
             sys_menu d,
             t_userMenu e
        where a.orgUnitID = b.orgUnitID
          and a.orgDeptID = c.orgUnitID
          and d.sysMenuID = e.sysMenuID
          and a.deleted = '0'
          and a.locked = '0'
          and a.userID = e.userID
          and d.sysMenuFiled = #{permission}
          and c.orgUnitID =  #{orgUnitId}
    </select>

    <select id="findUsersByUnitAndUnDepAndPermission" resultType="com.chinaweal.aicorg.model.AICUser">
        select a.userID      primaryKey,
               a.userName    ename,
               a.password as password,
               a.realName    name,
               b.orgUnitId   orgID,
               b.orgUnitName orgName,
               c.orgUnitID   unitID,
               c.orgUnitName unitName,
               b.orgNumber   regionID,
               a.userProp    userProp
        from t_users a,
             t_orgUnits b,
             t_orgUnits c,
             sys_menu d,
             t_userMenu e
        where a.orgUnitID = b.orgUnitID
          and a.orgDeptID = c.orgUnitID
          and d.sysMenuID = e.sysMenuID
          and a.deleted = '0'
          and a.locked = '0'
          and a.userID = e.userID
          and d.sysMenuFiled = #{permission}
          and b.orgUnitID =  #{orgUnitId}
          and c.orgUnitID !=  #{orgDeptID}
    </select>

    <select id="queryOrgByUserid" resultType="java.lang.String">
        select tg.orgunitname from cxaicorg.t_users tu
        left join cxaicorg.t_orgunits tg on tu.orgunitid = tg.orgunitid
        where userid = #{userid}
    </select>

    <select id="queryOrgNumByUserid" resultType="java.lang.String">
        select tg.orgNumber from cxaicorg.t_users tu
        left join cxaicorg.t_orgunits tg on tu.orgunitid = tg.orgunitid
        where userid = #{userid}
    </select>

    <select id="selectUserById" resultMap="AICUserMap">
        select a.userID as "primaryKey", a.userName as "ename", a.password as password, a.realName as "name", b.orgUnitId as "orgId",
               b.orgUnitName as "orgName", c.orgUnitID as "unitId", c.orgUnitName as "unitName", b.orgNumber as "regionId", a.userProp as "userProp"
        from t_users a, t_orgUnits b, t_orgUnits c
        where a.orgUnitID=b.orgUnitID and a.orgDeptID=c.orgUnitID and a.deleted='0' and
            a.locked='0' and a.userID = #{userId}
    </select>
</mapper>
