<?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="deleteUserByIndex">
update pqs_user
set state=#{state} 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=1 where u.USER_INDEX=#{userIndex}
</update>
    <!-- 查询 -->
    <select id="selectAllUser" resultMap="UserMapperMap">
select *from PQS_User u WHERE STATE in(1,3) order by u.LOGINTIME desc
</select>
    <select id="getAllUser" resultMap="UserMapperMap">
        select * from PQS_User u 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="updateState">
        update pqs_user set state = #{state} where user_index = #{userindex}
    </update>

    <update id="dealUserActive">
         update pqs_user set state = 1 ,login_error_times=0 where user_index = #{userindex}
    </update>

    <select id="getAllName" resultType="String">
        select  LOGINNAME from pqs_user
    </select>

    <update id="updateLockState">
        update pqs_user set state = #{state},login_error_times = 0 where user_index = #{userIndex}
    </update>
    <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="selectByIndex" resultMap="UserMapperMap">
	    select * from pqs_user a where a.user_index=#{index} or a.loginname=#{index}
</select>

    <select id="getAdminName" resultType="java.lang.String">
        select a.rolename
  from pqs_role a, pqs_userset b, pqs_rolegp c, pqs_rolegpass d, pqs_user e
 where b.rolegp_index = c.rolegp_index
   and c.rolegp_index = d.rolegp_index
   and d.role_index = a.role_index
   and b.user_index = e.user_index
   and e.user_index = #{userindex}
    </select>

    <select id="selectNormalAndCasualUser" resultMap="UserMapperMap">
        SELECT * from pqs_user where  USER_INDEX in (
        SELECT USER_INDEX from PQS_USERSET where ROLEGP_INDEX in (
        select ROLEGP_INDEX from PQS_ROLEGP where ROLEGPNAME in
        <foreach item="roleList" collection="roleName" open="(" separator="," close=")">
            #{roleList}
        </foreach>
        )
        )
        AND STATE in
        <foreach item="list" collection="userState" open="(" separator="," close=")">
            #{list}
        </foreach>
        AND CASUAL_USER in
        <foreach item="item" collection="careUser" open="(" separator="," close=")">
            #{item}
        </foreach>
        and referral_code is null
    </select>

    <select id="selectNormalAndCasualUsers" resultMap="UserMapperMap">
        SELECT * from pqs_user where  USER_INDEX in (
        SELECT USER_INDEX from PQS_USERSET where ROLEGP_INDEX in (
        select ROLEGP_INDEX from PQS_ROLEGP where ROLEGPNAME in
        <foreach item="roleList" collection="roleName" open="(" separator="," close=")">
            #{roleList}
        </foreach>
        )
        )
        AND STATE in
        <foreach item="list" collection="userState" open="(" separator="," close=")">
            #{list}
        </foreach>
        AND CASUAL_USER in
        <foreach item="item" collection="careUser" open="(" separator="," close=")">
            #{item}
        </foreach>
        and referral_code is not null
    </select>

    <select id="selectAllManagers" resultMap="UserMapperMap">
        SELECT * from pqs_user where USER_INDEX in (
            SELECT USER_INDEX from PQS_USERSET where ROLEGP_INDEX in (
                select ROLEGP_INDEX from PQS_ROLEGP where ROLEGPNAME in
                <foreach item="item" collection="roleGroupNames" open="(" separator="," close=")" >
                    #{item}
                </foreach>
              )
        )
        AND STATE in
        <foreach item="list" collection="userState" open="(" separator="," close=")" >
            #{list}
        </foreach>
    </select>

    <select id="getPhone" resultMap="UserMapperMap">
        select * from PQS_USER where PHONE = #{phone} and STATE !=#{state}
    </select>
</mapper>