<?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.github.wxiaoqi.security.admin.mapper.AppUserDetailMapper">
    <resultMap id="AppUserVoMap" type="com.github.wxiaoqi.security.admin.vo.AppUserVo">
        <result column="id" property="id"/>
        <result column="userid" property="userid"/>
        <result column="im_userid" property="imUserid"/>
        <result column="username" property="username"/>
        <result column="wx_openid" property="wxOpenid"/>
        <result column="unionid" property="unionid"/>
        <result column="openid" property="openid"/>
        <result column="status" property="status"/>
        <result column="id_number" property="idNumber"/>
        <result column="certification_status" property="certificationStatus"/>
        <result column="is_member" property="isMember"/>
        <result column="nickname" property="nickname"/>
        <result column="wx_nickname" property="wxNickname"/>
        <result column="qq_nickname" property="qqNickname"/>
        <result column="realname" property="realname"/>
        <result column="headimgurl" property="headimgurl"/>
        <result column="birthday" property="birthday"/>
        <result column="email" property="email"/>
        <result column="sex" property="sex"/>
        <result column="person_sign" property="personSign"/>
        <result column="remark" property="remark"/>
        <result column="channel" property="channel"/>
        <result column="province_code" property="provinceCode"/>
        <result column="city_code" property="cityCode"/>
        <result column="crt_host" property="crtHost"/>
        <result column="upd_host" property="updHost"/>
        <result column="position_id" property="positionId"/>
        <result column="source" property="source"/>
        <result column="code" property="code"/>
        <result column="inviter_account" property="inviterAccount"/>
        <result column="state" property="state"/>
        <result column="jobTitle" property="jobTitle"/>
        <result column="memberName" property="memberName"/>
    </resultMap>


    <!--  获取用户信息 -->
    <select id="getUserInfo" resultMap="AppUserVoMap">
    select l.im_userid,l.username,l.wx_openid,l.unionid,l.openid,l.status,l.id_number,l.certification_status,d.*  from app_user_login l
    left join  app_user_detail d
    on d.userid = l.id
     where d.userid = #{userId}  limit 1
  </select>


    <!--  获取用户信息 -->
    <select id="getUserVo" resultMap="AppUserVoMap">
        select
        l.im_userid,l.username,l.wx_openid,l.unionid,l.openid,l.status,l.id_number,l.certification_status,d.*,p.name as
        jobTitle,buml.name as memberName from app_user_login l
        left join app_user_detail d
        on d.userid = l.id
        left join base_user_member bum
        on bum.user_id=l.id
        left join (select level, name from base_user_member_level level where isdel = 0) buml
        on bum.member_level = buml.level
        left join (select id, name from app_user_position where is_del=0) p
        on d.position_id=p.id
        where
        d.userid in
        <foreach collection="list" item="userId" index="index" open="(" separator="," close=")">
            #{userId}
        </foreach>

    </select>


    <select id="getUserIdByUsername" resultType="java.lang.Integer" parameterType="java.lang.String">
        select d.userid  from app_user_login l
        left join  app_user_detail d
        on d.userid = l.id
        where d.realname like concat("%", #{keywords}, "%") or l.username like concat("%", #{keywords}, "%")
    </select>


    <select id="selectAppUserManage" parameterType="Integer"
            resultType="com.github.wxiaoqi.security.admin.vo.AppUserManageVo">
   SELECT
	l.id AS userid,
	l.username,
	l.certification_status AS certificationStatus,
	l.id_number AS idNumber,
	l.createtime,
	l.last_time AS lastTime,
	l. STATUS,
	d.channel,
	d.is_member,
	d.realname,
	d.nickname,
	d.source,
	d.province_code AS provinceCode,
	d.city_code AS cityCode,
	d.sex,
	d.headimgurl,
	d.email,
	m.valid_time AS validTime,
	m.buy_count AS buyCount,
	m.total_number AS totalNumber,
	m.rent_free_days AS rentFreeDays,
	m.crt_time AS timeOfMembership,
	m.recent_recharge AS recentRecharge,
	ml. NAME AS memberName,
	aup. NAME AS `positionName`,
	ul.username AS inviter,
	IFNULL(pd.realname, pd.nickname) AS nameOfSuperior,
	pl.username AS superiorMobileNumber
    FROM
	app_user_login l
    LEFT JOIN app_user_detail d ON d.userid = l.id
    LEFT JOIN app_user_login ul ON ul.id = d.inviter_account
    LEFT JOIN base_user_member m ON d.userid = m.user_id
    LEFT JOIN
    (
	SELECT
		LEVEL,
		NAME
	FROM
		base_user_member_level
	WHERE
		isdel = 0
    ) ml ON m.member_level = ml. LEVEL
    LEFT JOIN app_user_position aup ON aup.id = d.position_id
    LEFT JOIN
    (
	SELECT
		user_id,
		parent_id
	FROM
		app_user_relation
	WHERE
		is_del = 0
    ) r ON l.id = r.user_id
    LEFT JOIN app_user_login pl ON r.parent_id = pl.id
    LEFT JOIN app_user_detail pd ON pl.id = pd.userid
    WHERE
	l.id = #{userId}
    </select>

    <update id="updateUserMemberStatusByUserId">
    update `app_user_detail` set `is_member`={status}  where `userid`=#{userId}
</update>
    <update id="updateUserPositionByUserId">
        update  `app_user_detail` set `position_id`=#{positionId} where `userid`=#{userId}
    </update>
    <select id="selectAppUser" parameterType="com.github.wxiaoqi.security.admin.dto.AppUserManageDTO"
            resultType="com.github.wxiaoqi.security.admin.vo.AppUserManageVo">
        SELECT
        l.id AS userid,
        l.username,
        d.channel,
        d.source,
        d.realname,
        ml. NAME AS memberName,
        aup. NAME AS positionName,
        m.crt_time AS timeOfMembership,
        m.total_number AS totalNumber,
        m.rent_free_days AS rentFreeDays,
        IFNULL(pd.realname, pd.nickname) AS nameOfSuperior,
        pl.username AS superiorMobileNumber
        FROM
        app_user_login l
        LEFT JOIN app_user_detail d ON d.userid = l.id
        LEFT JOIN base_user_member m ON d.userid = m.user_id
        LEFT JOIN (
        SELECT
        LEVEL,
        NAME
        FROM
        base_user_member_level
        WHERE
        isdel = 0
        ) ml ON m.member_level = ml. LEVEL
        LEFT JOIN app_user_position aup ON aup.id = d.position_id
        LEFT JOIN (
        SELECT
        user_id,
        parent_id
        FROM
        app_user_relation
        WHERE
        is_del = 0
        ) r ON l.id = r.user_id
        LEFT JOIN app_user_login pl ON r.parent_id = pl.id
        LEFT JOIN app_user_detail pd ON pl.id = pd.userid
        WHERE
        l.isdel = 0
        <if test="mobile !=null and mobile !='' ">
            and l.username like CONCAT('%',#{mobile},'%')
        </if>
        <if test="channel !=null ">
            and d.channel=#{channel}
        </if>
        <if test="memberLevel !=null and memberLevel != -1">
            and m.member_level = #{memberLevel}
        </if>
        <if test="memberLevel == -1">
            and m.member_level in (select level from base_user_member_level)
        </if>
        <if test="registrationTimeBegin !=null  ">
            and l.createtime &gt;= #{registrationTimeBegin}
        </if>
        <if test="registrationTimeEnd !=null ">
            and l.createtime &lt;= #{registrationTimeEnd}
        </if>
        <if test="source !=null  ">
            and d.source = #{source}
        </if>
        <if test="userId!=null">
            and l.id=#{userId}
        </if>
        <if test="postionState != null">
            <choose>
                <when test="postionState == 6">
                    and d.position_id =#{postionState}
                </when>
                <otherwise>
                    and <![CDATA[d.`position_id`<>6]]>
                </otherwise>
            </choose>
        </if>
        <if test="realName !=null and  realName != ''">
            and d.realname like CONCAT('%',#{realName},'%')
        </if>
        <if test="citySet != null ">
            and d.city_code in
            <foreach collection="citySet" item="item" index="index" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        order by l.id ASC
    </select>

    <!--查询非普通用户-->
    <select id="selectAllStaffs" resultType="com.github.wxiaoqi.security.admin.bo.UserStaffBo">
select aud.userid as `userId`,aud.realname as `realName`,aud.nickname as `nickName`,aul.username as `phone`,aud.position_id as `postionId`,aup.name as `postionName`,aupt.company_id as `companyId`,aupt.company_name as `companyName`
from   `app_user_detail` as `aud` left join `app_user_login` as `aul` on aul.id=aud.userid
    left join `app_user_position_temp` as `aupt` on aupt.user_id=aul.id
    left join `app_user_position` as `aup`  on aup.id=aud.position_id
        where  <![CDATA[aud.position_id<>6]]> and aud.isdel=0
    </select>
    <select id="getUserByUserIdList" resultMap="AppUserVoMap">
        select
        l.im_userid,l.username,l.wx_openid,l.unionid,l.openid,l.status,l.id_number,l.certification_status,d.*,p.name as
        jobTitle,buml.name as memberName from app_user_login l
        left join app_user_detail d
        on d.userid = l.id
        left join base_user_member bum
        on bum.user_id=l.id
        left join (select level, name from base_user_member_level level where isdel = 0) buml
        on bum.member_level = buml.level
        left join (select id, name from app_user_position where is_del=0) p
        on d.position_id=p.id
        where l.isdel=0 and
        d.userid in
        <foreach collection="userIds" item="userId" index="index" open="(" separator="," close=")">
            #{userId}
        </foreach>
    </select>

    <select id="findAllStaffsByCompanyIdAndPostionId"
            resultType="com.github.wxiaoqi.security.admin.bo.UserStaffBo">
        select * from (select aud.userid as `userId`,aud.realname as `realName`,aud.nickname as `nickName`,aul.username as `phone`,aud.position_id
        as `postionId`,aup.name as `postionName`,aupt.company_id as `companyId`,aupt.company_name as `companyName` from `app_user_detail` as `aud`
        left join `app_user_login` as `aul` on aul.id=aud.userid
         left join (select * from `app_user_position_temp` where is_del=0) as `aupt` on aupt.user_id=aul.id
         left join `app_user_position` as `aup` on aup.id=aud.position_id where <![CDATA[aud.position_id<>6]]> and aud.isdel=0
          <if test="userPostionId!=null">
           and  aud.`position_id`=#{userPostionId}
          </if>
        <if test="companyId!=null">
            and  aupt.`company_id`=#{companyId}
        </if>
        <if test="userIds!=null and userIds.size>0">
            and aud.userid in
            <foreach collection="userIds" item="userId" open="(" close=")" separator=",">
                #{userId}
            </foreach>
        </if>) as `saff`
        left join (
        select auswp.user_id, IFNULL(auswp.upIncome, 0) - IFNULL(auswd.dowIncome, 0) as `sellAmount`
        from (select `user_id`, sum(commission / (extract/100.0)) as `upIncome`
        from `app_user_selling_water`
        where <![CDATA[position_id<>6]]>
        and status = 0
        <if test="startDate!=null and endDate!=null">
          and `crt_time` between #{startTime} and #{endTime}
        </if>
        <if test="startDate!=null and endDate==null">
            and <![CDATA[
             `crt_time` >=#{startTime}
            ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            and <![CDATA[
             `crt_time` <=#{endTime}
            ]]>
        </if>
        group by user_id) as auswp
        left join (select `user_id`, sum(commission / (extract/100.0)) as `dowIncome`
        from `app_user_selling_water`
        where <![CDATA[position_id<>6]]>
        and status = 1
        <if test="startDate!=null and endDate!=null">
            and `crt_time` between #{startTime} and #{endTime}
        </if>
        <if test="startDate!=null and endDate==null">
            and <![CDATA[
             `crt_time` >=#{startTime}
            ]]>
        </if>
        <if test="startDate==null and endDate!=null">
            and <![CDATA[
             `crt_time` <=#{endTime}
            ]]>
        </if>
        group by user_id) as auswd
        on auswd.user_id = auswp.user_id
        ) as `sell` on sell.user_id=saff.userId
        order by sell.sellAmount desc
    </select>
</mapper>