<?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">
<!-- 命名，每一个映射对象不一样
	namespace:必须与对应的接口全类名一致
 -->
<mapper namespace="com.iamberry.app.mapper.UserMapper">

	<sql id="userAttribute">
		id,
		username,
		password,
		token,
		display_name,
		display_picture,
		created_on,
		ext_open_id,
		ext_name,
		ext_type,
		location,
		baby_nickname,
		baby_dob,
		baby_gender,
		status_ ,
		user_tel userTel
	</sql>
	
	<resultMap type="User" id="userMap">
		<id property="id" column="id"/>
		<result property="username" column="username"/>
		<result property="password" column="password"/>
		<result property="token" column="token"/>
		<result property="display_name" column="display_name"/>
		<result property="display_picture" column="display_picture"/>
		<result property="created_on" column="created_on"/>
		<result property="ext_open_id" column="ext_open_id"/>
		<result property="ext_name" column="ext_name"/>
		<result property="ext_type" column="ext_type"/>
		<result property="location" column="location"/>
		<result property="baby_nickname" column="baby_nickname"/>
		<result property="baby_dob" column="baby_dob"/>
		<result property="baby_gender" column="baby_gender"/>
		<result property="status_" column="status_"/>
		<result property="userTel" column="user_tel"/>
	</resultMap>
	
	<!-- 根据id查询user所有信息 -->
	<select id="selectUserById" parameterType="java.lang.Long" resultMap="userMap">
		SELECT 
			<include refid="userAttribute" />
		FROM USER WHERE ID = #{0}
	</select>
	
	<!-- 根据username查询user所有信息 -->
	<select id="selectUserByUsername" parameterType="java.lang.String" resultMap="userMap">
		SELECT <include refid="userAttribute" /> FROM USER WHERE USERNAME = #{0}
	</select>
	
	<!-- 通过ext_open_id查询user所有信息 -->
	<select id="selectUserByExtOpenId" parameterType="java.lang.String" resultMap="userMap">
		SELECT <include refid="userAttribute" /> FROM USER WHERE EXT_OPEN_ID = #{0}
	</select>
	
	<!-- 添加用户信息到user表中 -->
	<insert id="insertUser" parameterType="User" useGeneratedKeys="true" keyProperty="id">
		INSERT INTO USER 
			(
				USERNAME,PASSWORD,TOKEN,DISPLAY_NAME,DISPLAY_PICTURE,
				CREATED_ON,EXT_OPEN_ID,EXT_NAME,EXT_TYPE,
				LOCATION,BABY_NICKNAME,BABY_DOB,BABY_GENDER,STATUS_,USER_TEL) 
		VALUES 
			(
				#{username},#{password},#{token},#{display_name},#{display_picture},
				NOW(),#{ext_open_id},#{ext_name},#{ext_type},
				#{location},#{baby_nickname},#{baby_dob},#{baby_gender},#{status_}, #{userTel}
			)
	</insert>
	
	<!-- 根据id修改user表数据 -->
	<update id="updateUser" parameterType="User">
		UPDATE USER 
		<set>
			<if test="username != null and username != ''">
				USERNAME = #{username}, 
			</if>
			<if test="password != null and password != ''">
				PASSWORD = #{password}, 
			</if>
			<if test="token != null and token != ''">
				TOKEN = #{token}, 
			</if>
			<if test="display_name != null and display_name != ''">
				DISPLAY_NAME = #{display_name}, 
			</if>
			<if test="display_picture != null and display_picture != ''">
				DISPLAY_PICTURE = #{display_picture}, 
			</if>
			<if test="ext_open_id != null and ext_open_id != ''">
				EXT_OPEN_ID = #{ext_open_id}, 
			</if>
			<if test="ext_name != null and ext_name != ''">
				EXT_NAME = #{ext_name}, 
			</if>
			<if test="ext_type != null and ext_type != ''">
				EXT_TYPE = #{ext_type}, 
			</if>
			<if test="location != null and location != ''">
				LOCATION = #{location}, 
			</if>
			<if test="baby_nickname != null and baby_nickname != ''">
				BABY_NICKNAME = #{baby_nickname}, 
			</if>
			<if test="baby_dob != null and baby_dob != ''">
				BABY_DOB = #{baby_dob}, 
			</if>
			<if test="baby_gender != null and baby_gender != ''">
				BABY_GENDER = #{baby_gender}, 
			</if>
			<if test="status_ != null and status_ != ''">
				STATUS_ = #{status_},
			</if>
			<if test="userTel != null and userTel != ''">
				USER_TEL = #{userTel} 
			</if>
		</set>
		WHERE 
			ID = #{id} 
	</update>
	
	<!-- 根据id修改user表的个人头像 -->
	<update id="updateDisplayPicture">
		UPDATE USER SET DISPLAY_PICTURE = #{0} WHERE ID = #{1} 
	</update>
	
	<!-- 根据用户名和密码查询用户表信息 -->
	<select id="validateUser" parameterType="java.lang.String" resultMap="userMap">
		SELECT <include refid="userAttribute"/> FROM USER WHERE USERNAME=#{0} AND PASSWORD=#{1} 
	</select>
	
	<!-- 通过id修改用户表密码 -->
	<update id="changePassword">
		UPDATE USER SET PASSWORD=#{1} WHERE ID=#{0}
	</update>
	
	<!-- 通过token查询user表所有信息 -->
	<select id="selectUserByToken" parameterType="java.lang.String" resultMap="userMap">
		SELECT <include refid="userAttribute"/> FROM USER WHERE TOKEN=#{0} 
	</select>
	
	<!-- 根据id修改token值 -->
	<update id="updateUserToken">
		UPDATE USER SET TOKEN=#{0} WHERE ID=#{1}
	</update>
	
	<!-- 根据id修改user表的用户名 -->
	<update id="updateUserName">
		UPDATE USER SET USERNAME=#{0} WHERE ID=#{1}
	</update>
	
	<!-- 通过key_查询sys_config表数据 -->
	<select id="selectConfig" parameterType="java.lang.String" resultType="String">
		SELECT VALUE_ FROM SYS_CONFIG WHERE KEY_ = #{0}
	</select>
	
	<!-- USER_AVATOR表中添加数据 -->
	<insert id="insertUserAvator">
		INSERT INTO USER_AVATOR (USER_ID, AVATOR) VALUE (#{0}, #{1})
	</insert>
	
	<!-- 通过user_id修改USER_AVATOR表数据 -->
	<update id="updateUserAvator">
		UPDATE USER_AVATOR SET AVATOR = #{1} WHERE USER_ID = #{0}
	</update>
	
	<!-- 根据user_id查询USER_AVATOR表数据 -->
	<select id="selectUserAvator" parameterType="java.lang.Long" resultType="java.lang.String">
		SELECT avator FROM USER_AVATOR WHERE USER_ID = #{id}
	</select>
	
	<!-- 根据user_id查询USER_AVATOR表数据 -->
	<select id="getOtherUserByTel" parameterType="java.util.Map" resultMap="userMap">
		SELECT 
			<include refid="userAttribute"/> 
		FROM 
			USER 
		WHERE 
			USER_TEL = #{phone} 
				AND 
			ID != #{userId} 
				AND 
			STATUS_ = 'ACTIVE' 
		LIMIT 
			0, 1
	</select>
	
	<select id="getAll" parameterType="java.util.Map" resultMap="userMap">
		SELECT 
			<include refid="userAttribute"/> 
		FROM 
			USER
		LIMIT
			#{start}, #{end}
	</select>
</mapper>