<?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.njcn.mapper.user.UserMapper">
    <!-- 用户 -->
    <resultMap type="com.njcn.sso.pojo.user.User" id="UserMapperMap" autoMapping="true">
        <id property="userIndex" column="USER_INDEX"/>
    </resultMap>
    <!-- 修改密码 -->
    <update id="updatePassword">
        update PQS_User
        set password=#{password} where USER_INDEX=#{userIndex}
  </update>
    <!-- 修改 -->
    <update id="updateUsers">
    update PQS_User
    set NAME=#{name},LOGINNAME=#{loginName},
    phone=#{phone},email=#{email},mark=#{mark},LOGINTIME=#{loginTime} where USER_INDEX=#{userIndex,jdbcType=VARCHAR}
  </update>

    <!-- 根据id查询id -->
    <select id="selectId" resultMap="UserMapperMap">
    select *from PQS_User u where u.USER_INDEX=#{userIndex}
    </select>
    <!-- 根据登录名查询 -->
    <select id="selectloginName" resultType="java.lang.Integer">
select COUNT(*) from pqs_user where LOGINNAME=#{loginName}
</select>
    <!-- 审核新增用户 -->
    <update id="updateExaminer">
update pqs_user u set u.state=#{state} where u.USER_INDEX=#{userIndex}
</update>

    <!-- 查询除此之外的名称 -->
    <select id="selectName" resultMap="UserMapperMap">
select * from pqs_user  where name not in (select name from pqs_user where user_index=#{userIndex})
</select>
    <!-- 查询 -->
    <select id="selectAllUser" resultMap="UserMapperMap">
select *from PQS_User u WHERE STATE in(1,3) order by u.LOGINTIME desc
</select>
    <select id="selectIdName" resultType="java.lang.String">
select LOGINNAME from pqs_user where  user_index=#{userIndex}
</select>
    <update id="updateByPrimaryKeyBySql" parameterType="user">
        update pqs_user
        <set>
            <if test="userIndex != null">user_Index=#{userIndex},</if>
            <if test="name != null">name=#{name},</if>
            <if test="loginName != null">loginName=#{loginName},</if>
            <if test="password != null">password=#{password},</if>
            <if test="phone != null">phone=#{phone},</if>
            <if test="email != null">email=#{email},</if>
            <if test="registerTime != null">registerTime=#{registerTime},</if>
            <if test="psdvalidity != null">psdvalidity=#{psdvalidity},</if>
            <if test="loginTime != null">loginTime=#{loginTime},</if>
            <if test="state != null">state=#{state},</if>
            <if test="mark != null">mark=#{mark},</if>
            <if test="limitIpStart != null">limit_IpStart=#{limitIpStart},</if>
            <if test="limitIpEnd != null">limit_IpEnd=#{limitIpEnd},</if>
            <if test="limitTime != null">limit_Time=#{limitTime},</if>
            <if test="loginErrorTimes != null">LOGIN_ERROR_TIMES=#{loginErrorTimes},</if>
            <if test="casualUser != null">casual_User=#{casualUser},</if>
            <if test="firstErrorTime != null">firstError_Time=#{firstErrorTime},</if>
            <if test="lockTime != null">lock_Time=#{lockTime}</if>
        </set>
        where user_Index=#{userIndex}

    </update>

    <select id="getUserAll" resultType="com.njcn.pojo.user.UserDetail">
SELECT
	t.userIndex userIndex,
	t.userName userName,
	t.loginName loginName,
	t.deptsName deptsName,
	listagg ( t.roleName, ',' ) within GROUP ( ORDER BY t.userIndex ) AS roleName
FROM
	(
	SELECT DISTINCT
		users.USER_INDEX userIndex,
		users.NAME userName,
		users.LOGINNAME loginName,
		depts.DEPTSNAME deptsName,
		role.ROLE_DESCRIPTION roleName
	FROM
		( SELECT * FROM pqs_user WHERE USER_INDEX = #{userIndex} ) users
		LEFT JOIN PQS_USERSET userset ON userset.USER_INDEX = users.USER_INDEX
		LEFT JOIN PQS_DEPTS depts ON depts.DEPTS_INDEX = userset.DEPTS_INDEX
		LEFT JOIN PQS_ROLEGPASS roleass ON roleass.ROLEGP_INDEX = userset.ROLEGP_INDEX
		LEFT JOIN pqs_role role ON role.ROLE_INDEX = roleass.ROLE_INDEX
	) t
GROUP BY
	t.userIndex,t.userName,t.loginName,t.deptsName
    </select>
</mapper>