<?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="SysUserMapper">

	<resultMap type="SysUser" id="SysUserResultMap">
		<id column="id" property="id"/>
		<result column="user_name" property="username"/>
		<result column="password" property="password"/>
		<result column="real_name" property="realName"/>
		<result column="sex" property="sex"/>
		<result column="role_id" property="roleId"/>
		<result column="phone" property="phone"/>
		<result column="email" property="email"/>
		<result column="last_login" property="lastLogin"/>
		<result column="status" property="status"/> 
		<result column="create_by" property="createBy"/>
		<result column="create_date" property="createDate"/>
		<result column="update_by" property="updateBy"/>
		<result column="update_date" property="updateDate"/>
	</resultMap>
	
	<!-- 新增用户信息-->
	<insert id="save">
		insert into sys_user(
			user_name, password, role_id, sex, real_name, province_code,
			city_code, phone, email, status, create_by, create_date, id
		) values (
			#{user_name},#{password},#{role},#{sex},#{real_name},#{province_code},
			#{city_code},#{phone},#{email},#{status},#{create_by},#{create_date},#{id}
		)
	</insert>
	
	<!-- 删除系统用户-->
	<update id="delete">
		update sys_user
		set del_flag = true
		where id = #{id}
	</update>
	
	<!-- 修改用户基本信息 -->
	<update id="edit">
		update  sys_user
		set 
			user_name=#{user_name},
			real_name=#{real_name},
			<if test="password != null and password != ''">
				password=#{password},
			</if>
			phone=#{phone},
			email=#{email},
			province_code = #{province_code},
			city_code = #{city_code},
			update_date=#{update_date},
			update_by=#{update_by},
			sex=#{sex},
			role_id=#{role}
		where id = #{id}
	</update>
	
	<!-- 获取详情 -->
	<select id="findById" resultType="PageData">
		select a.*
		from sys_user a 
		where 1=1
		<if test="id != null and id != ''">
			and a.id=#{id}
		</if>
	</select>
	
	<!-- 获取详情 -->
	<select id="findByPhone" resultType="PageData">
		select a.*
		from sys_user a 
		where a.phone = #{phone}
	</select>
	
	<!-- 获取详情 -->
	<select id="findListByPhones" resultType="PageData">
		select a.*
		from sys_user a 
		where a.phone in
		<foreach collection="servicePhones" item="phone" separator="," open="(" close=")">
			 #{phone}
		</foreach>
	</select>
	
	<!-- 获取详情 -->
	<select id="findByName" resultType="PageData">
		select a.*
		from sys_user a 
		where 1=1
		<if test="user_name != null and user_name != ''">
			and a.user_name=#{user_name}
		</if>
	</select>
	<!-- 修改系统用户的状态 -->
	<update id="updateStatusById">
		update  sys_user
				set 
				<if test="update_by != null and update_by != ''">
			   		update_by=#{update_by},
		   		</if>
		   		<if test="update_date != null and update_date != ''">
			   		update_date=#{update_date},
		   		</if>
					status=#{status}
			where 
			   id = #{id}
	</update>
	
	<select id="checkLogin" resultType="PageData">
		select a.ID AS ID,a.USERNAME,a.ROLE_ID
		from sys_user a 
		where a.USERNAME=#{USERNAME} and a.PASSWORD=#{PASSWORD}
	</select>
	
	<!-- 列表 -->
	<select id="listPage" parameterType="Page" resultType="PageData">
		select a.id,a.user_name,a.real_name,a.sex,a.phone,a.email,a.status,
				(select name FROM sys_role WHERE id=a.role_id) role_name,
				date_format(a.create_date,'%Y-%c-%d %H:%i:%s') create_date
		from sys_user a 
		where 1=1 and a.del_flag = false
		<if test="pd.user_name != null and pd.user_name != ''">
			and a.user_name LIKE CONCAT(CONCAT('%', #{pd.user_name}), '%')
		</if>
		<if test="pd.phone != null and pd.phone != ''">
			and a.phone=#{pd.phone}
		</if>
		<if test="pd.role_id != null and pd.role_id != ''">
			and a.role_id=#{pd.role_id}
		</if>
		<if test="pd.DATA_LEVEL == 'personal'">
			and a.create_by=#{pd.USERID}
		</if>
	</select>
	
	<!-- 列表(全部) -->
	<select id="listAll" resultType="PageData">
		select a.ID AS ID,a.USERNAME,
				(select ROLE_NAME FROM sys_role WHERE ROLE_ID=a.ROLE_ID) ROLE_MAME,
				a.CREATE_DATE
		from sys_user a 
	</select>
	
	<!-- 查询总数 -->
	<select id="findCount" resultType="Long">
		select count(a.id) 
		from sys_user a 
		where 1=1 and a.del_flag = false
		<if test="user_name != null and user_name != ''">
			and a.user_name LIKE CONCAT(CONCAT('%', #{user_name}), '%')
		</if>
		<if test="phone != null and phone != ''">
			and a.phone=#{phone}
		</if>
		<if test="role_id != null and role_id != ''">
			and a.role_id=#{role_id}
		</if>
		<if test="DATA_LEVEL == 'personal'">
			and a.create_by=#{USERID}
		</if>
	</select>
	
	<!-- 判断用户名和密码 -->
	<select id="getUserInfo" resultType="SysUser">
		select * from sys_user 
		where 1=1
		<if test="USERNAME!=null and PASSWORD!=null">
		and user_name = #{username} and password=#{password}
		</if>
		<if test="ID!=null and ID>0">
		and ID = #{ID}
		</if>
	</select>
	
	<select id="getUserInfoByNameAndPwd" resultType="SysUser">
		select * from sys_user 
		where 1=1
		<if test="user_name!=null and password!=null">
		and user_name = #{user_name} and password=#{password}
		</if>
	</select>
	
	<update id="updateLastLogin" parameterType="SysUser">
		update sys_user set 
		last_login=#{lastLogin} 
		where id=#{id}
	</update>
	
	<select id="findRolesByUsername" parameterType="String" resultType="PageData">
		select r.ROLE_NAME 
		from sys_user u left join sys_role r on u.role_id = r.role_id
		where username = #{USERNAME}
	</select>
</mapper>