<?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="cn.com.ty.lift.ud.userInfo.mapper.UserInfoMapper">
	<resultMap id="BaseResultMap" type="cn.com.ty.lift.ud.userInfo.mapper.entity.UserInfoEntity">
		<id column="id" property="id" jdbcType="INTEGER"/>
		<id column="user_id" property="userId" jdbcType="BIGINT"/>
		<result column="name" property="name" jdbcType="VARCHAR"/>
		<result column="real_name" property="realName" jdbcType="VARCHAR"/>
		<result column="avatar_url" property="avatarUrl" jdbcType="VARCHAR"/>
		<result column="mobile" property="mobile" jdbcType="VARCHAR"/>
		<result column="proficiency_brand" property="proficiencyBrand" jdbcType="VARCHAR"/>
		<result column="signature" property="signature" jdbcType="VARCHAR"/>
		<result column="vip_flag" property="vipFlag" jdbcType="INTEGER"/>
		<result column="type" property="type" jdbcType="VARCHAR"/>
		<result column="surname" property="surname" jdbcType="VARCHAR"/>
		<result column="statuz" property="statuz" jdbcType="INTEGER"/>
		<result column="user_level" property="userLevel" jdbcType="INTEGER"/>
		<result column="expert_level" property="expertLevel" jdbcType="INTEGER"/>
		<result column="service_fee" property="serviceFee" jdbcType="DECIMAL"/>
		<result column="balance" property="balance" jdbcType="DECIMAL"/>
		<result column="qr_code_url" property="qrCodeUrl" jdbcType="VARCHAR"/>
		<result column="show_which_name" property="showWhichName" jdbcType="INTEGER"/>
		<result column="request_code" property="requestCode" jdbcType="INTEGER"/>
	</resultMap>

	<select id="selectByQuery" resultType="cn.com.ty.lift.ud.userInfo.mapper.entity.UserInfoEntity">
		SELECT
			*,IF(show_which_name=2,IFNULL(real_name,NAME),NAME)`name`,
			(SELECT GROUP_CONCAT(bt.name SEPARATOR ',') FROM lift_brand bt WHERE FIND_IN_SET(bt.id, a.proficiency_brand)) proficiencyBrandName
		FROM user_info AS a
		LEFT JOIN user_account b ON a.user_id = b.user_id
		<where>
			<if test="page.brandId != null">
		    FIND_IN_SET(#{page.brandId}, a.proficiency_brand) AND 
			</if>
			${ew.sqlSegment}
		</where>
		limit #{page.pageIndex},#{page.pageSize}
	</select>
	<select id="countByQuery" resultType="java.lang.Integer">
		select count(0) FROM user_info a LEFT JOIN user_account b ON a.user_id
		= b.user_id
		<where>
			${ew.sqlSegment}
		</where>
	</select>

	<!--用户列表-->
	<select id="selectUserList" resultType="cn.com.ty.lift.ud.userInfo.mapper.entity.UserInfoEntity">
		select company.`name` as companyName,a.*,b.expert_flag as expertFlag,ulr.name as roleName
		from user_info a
		left join  maintenance_company company on company.id = a.maintenance_company_id
		left join  user_account b on b.user_id = a.user_id
		left join  user_level_rule ulr on ulr.id = a.user_level
		<where>
			${ew.sqlSegment}
		</where>
		limit #{page.pageIndex},#{page.pageSize}
	</select>

	<!--用户列表总数-->
	<select id="countUserList" resultType="java.lang.Integer">
		select count(0) FROM user_info a
		left join  maintenance_company company on company.id = a.maintenance_company_id
		left join  user_account b on b.user_id = a.user_id
		left join  user_level_rule ulr on ulr.id = a.user_level
		<where>
			${ew.sqlSegment}
		</where>
	</select>


	<!--修改用户信息-->
	<update id="updateUserInfo" parameterType="cn.com.ty.lift.ud.userInfo.mapper.entity.UserInfoEntity" >
		update user_info
		<set >
			<if test="name != null" >
				name = #{name,jdbcType=VARCHAR},
			</if>
			<if test="realName != null" >
				real_name = #{realName,jdbcType=INTEGER},
			</if>
			<if test="avatarUrl != null" >
				avatar_url = #{avatarUrl,jdbcType=VARCHAR},
			</if>
			<if test="proficiencyBrand != null" >
				proficiency_brand = #{proficiencyBrand,jdbcType=VARCHAR},
			</if>
			<if test="proficiency != null" >
                proficiency = #{proficiency,jdbcType=VARCHAR},
            </if>
			<if test="signature != null" >
				signature = #{signature,jdbcType=VARCHAR},
			</if>
			<if test="vipFlag != null" >
				vip_flag = #{vipFlag,jdbcType=INTEGER},
			</if>
			<if test="type != null" >
				type = #{type,jdbcType=VARCHAR},
			</if>
			<if test="surname != null" >
				surname = #{surname,jdbcType=VARCHAR},
			</if>
			<if test="userLevel != null" >
				user_level = #{userLevel,jdbcType=INTEGER},
			</if>
			<if test="statuz != null" >
				statuz = #{statuz,jdbcType=INTEGER},
			</if>
			<if test="expertLevel != null" >
				expert_level = #{expertLevel,jdbcType=INTEGER},
			</if>
			<if test="platformInvitedFlag != null" >
				platform_invited_flag = #{platformInvitedFlag,jdbcType=INTEGER},
			</if>
			<if test="serviceFee != null" >
				service_fee = #{serviceFee,jdbcType=DECIMAL},
			</if>
			<if test="balance != null" >
				balance = #{balance,jdbcType=DECIMAL},
			</if>
			<if test="qrCodeUrl != null" >
				qr_code_url = #{qrCodeUrl,jdbcType=VARCHAR},
			</if>
			<if test="alipayRealName != null" >
				alipay_real_name = #{alipayRealName,jdbcType=VARCHAR},
			</if>
			<if test="alipayAccount != null" >
				alipay_account = #{alipayAccount,jdbcType=VARCHAR},
			</if>
			<if test="showWhichName != null" >
				show_which_name = #{showWhichName,jdbcType=INTEGER},
			</if>
			<if test="requestCode != null" >
				request_code = #{requestCode,jdbcType=INTEGER},
			</if>
		</set>
		where user_id = #{userId,jdbcType=BIGINT}
	</update>

	<select id="getNearUser" resultType="cn.com.ty.lift.ud.userInfo.mapper.entity.UserInfoVo">
		SELECT
			us.user_id AS userId,
		    IF( show_which_name = 2, IFNULL( real_name, NAME ), NAME ) `name`,
			us.avatar_url AS avatarUrl,
			us.signature signature,
			us.last_city_district lastCityDistrict,
			nickname.nickname kindlyName,
			ROUND(6378.138 * 2 * ASIN(
					SQRT(
						POW( SIN( ( #{lat} * PI() / 180 - lat * PI() / 180 ) / 2 ), 2 ) + COS( #{lat} * PI() / 180) * COS(lat * PI() / 180) *
						POW( SIN( ( #{lon} * PI() / 180 -   lon * PI() / 180 ) / 2 ), 2 ) ) ) * 1000 ) AS distance
		FROM user_info us
		LEFT JOIN user_account ua ON ua.user_id = us.user_id
		left join user_nickname_for_another nickname on nickname.target_user_id = us.user_id and nickname.user_id = #{userId}
		WHERE ua.expert_flag = 0 AND us.nearby_flag = 0
		ORDER BY distance ASC
	</select>


	<select id="listGroupExpert" resultMap="BaseResultMap">
	select *
	from(
		select
			info.name,													info.real_name,
			info.user_id,												info.avatar_url,
			ifnull(GROUP_CONCAT(brand.name) ,'')proficiency_brand
		from user_info info
		left join user_account account  on account.user_id = info.user_id
		left join lift_brand brand on FIND_IN_SET(brand.id,info.proficiency_brand)
		where account.expert_flag=1
		group by info.name,info.real_name,info.user_id,info.avatar_url

		union

		select
			info.name,													info.real_name,
			info.user_id,												info.avatar_url,
			ifnull(GROUP_CONCAT(brand.name) ,'')proficiency_brand
		from(select expert from question_bank group by expert) qbank
		left join user_info info on qbank.expert = info.user_id
		left join user_account account  on account.user_id = info.user_id
		left join lift_brand brand on FIND_IN_SET(brand.id,info.proficiency_brand)
		group by info.name,info.real_name,info.user_id,info.avatar_url
	)temp
	order by CONVERT(temp.name USING gbk) asc
	</select>

	<select id="listGroupHotExpert" resultMap="BaseResultMap">
	select *
	from(
		select
			info.name,													info.real_name,
			info.user_id,												info.avatar_url,
			ifnull(GROUP_CONCAT(brand.name) ,'')proficiency_brand
		from user_info info
		left join user_account account  on account.user_id = info.user_id
		left join lift_brand brand on FIND_IN_SET(brand.id,info.proficiency_brand)
		where account.expert_flag=1 and info.platform_invited_flag=0
		group by info.name,info.real_name,info.user_id,info.avatar_url

		union

		select
			info.name,													info.real_name,
			info.user_id,												info.avatar_url,
			ifnull(GROUP_CONCAT(brand.name) ,'')proficiency_brand
		from user_info info
		left join user_account account  on account.user_id = info.user_id
		left join lift_brand brand on FIND_IN_SET(brand.id,info.proficiency_brand)
		where account.user_id=1
		group by info.name,info.real_name,info.user_id,info.avatar_url
	)temp
	order by CONVERT(temp.name USING gbk) asc
	</select>

	<select id="getExpertRewardedCount" resultType="int">
		select count(0)
		from question_operate opt
		left join question_bank bank on bank.id = opt.data_id
		where bank.expert=#{expert}
	</select>



	<select id="getUserRewardList" resultType="cn.com.ty.lift.ud.userInfo.mapper.entity.UserRewardListVo">
	select * from(
		select
			sv.data_id id,
			sv.user_id userId,
			CONCAT("电梯学堂","-",info.name) targetUserName,
			bill.user_id targetUserId,
			info.avatar_url targetAvatarUrl,
			bill.amount amount,
			sv.create_time createTime
		from school_video_operate sv
		left join user_bill bill on bill.flow_num=sv.third_order_sn and bill.user_id!=#{userId}
		left join user_info info on info.user_id = bill.user_id
		where sv.type = 4 and sv.user_id=#{userId}

		union

		select
			sv.data_id id,
			sv.user_id userId,
			CONCAT("案例中心","-",info.name) targetUserName,
			bill.user_id targetUserId,
			info.avatar_url targetAvatarUrl,
			bill.amount amount,
			sv.create_time createTime
		from question_operate sv
		left join user_bill bill on bill.flow_num=sv.order_serial_number and bill.user_id!=#{userId}
		left join user_info info on info.user_id = bill.user_id
		where sv.type = 4 and sv.user_id=#{userId}
	) temp order by create_time;

	</select>
</mapper>