<?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.kym.mapper.UserMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.kym.entity.User">
        <id column="id" property="id"/>
        <result column="company_id" property="companyId" />
        <result column="openid" property="openid"/>
        <result column="unionid" property="unionid"/>
        <result column="username" property="username"/>
        <result column="password" property="password"/>
        <result column="gender" property="gender"/>
        <result column="nickname" property="nickname"/>
        <result column="mobile_phone" property="mobilePhone"/>
        <result column="avatar" property="avatar"/>
        <result column="status" property="status"/>
        <result column="create_time" property="createTime"/>
        <result column="update_time" property="updateTime"/>
        <result column="last_login_time" property="lastLoginTime"/>
        <result column="follow_official_account" property="followOfficialAccount"/>
    </resultMap>

    <resultMap id="CustomUserMap" type="com.kym.entity.vo.CustomUserVo">
        <result column="user_id" property="userId"/>
        <result column="username" property="userName"/>
        <result column="station_id" property="stationId"/>
        <result column="mobile_phone" property="mobilePhone"/>
        <result column="status" property="status"/>
        <result column="register_time" property="registerTime"/>
        <result column="last_login_time" property="lastLoginTime"/>
        <result column="recharge_times" property="rechargeTimes"/>
        <result column="recharge_amount" property="rechargeAmount"/>
        <result column="wash_times" property="washTimes"/>
        <result column="amount" property="amount"/>
        <result column="amount_receivable" property="amountReceivable"/>
        <result column="amount_received" property="amountReceived"/>
        <result column="discount_amount" property="discountAmount"/>
        <result column="follow_official_account" property="followOfficialAccount"/>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, company_id,openid, unionid, username, password, gender, nickname, mobile_phone, avatar, status, create_time, update_time, last_login_time, follow_official_account
    </sql>


    <select id="listUser" resultMap="CustomUserMap" parameterType="java.util.List">
        <if test="list!=null">
            SELECT
            t1.`id` user_id,
            t1.`username`,
            t1.`station_id`,
            t1.`mobile_phone`,
            t1.`status`,
            t1.`create_time` register_time,
            t1.`last_login_time`,
            t3.recharge_times,
            t3.recharge_amount,
            t2.wash_times,
            t2.amount,
            t2.amount_receivable,
            t2.amount_received,
            t2.discount_money,
            t1.`follow_official_account`
            FROM
            t_user t1
            LEFT JOIN
            (SELECT
            user_id,
            COUNT(IFNULL(1, 0)) wash_times,
            SUM(IFNULL(amount, 0)) amount,
            SUM(IFNULL(amount_receivable, 0)) amount_receivable,
            SUM(IFNULL(amount_received, 0)) amount_received,
            SUM(IFNULL(discount_money, 0)) discount_money
            FROM
            t_wash_order
            WHERE user_id in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            AND order_status = 1
            GROUP BY user_id) t2
            ON t1.`id` = t2.`user_id`
            LEFT JOIN
            (SELECT
            user_id,
            COUNT(IFNULL(1, 0)) recharge_times,
            SUM(IFNULL(amount, 0)) recharge_amount
            FROM
            t_wallet_detail
            WHERE user_id in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            AND TYPE = 1 AND status = 1
            GROUP BY user_id) t3
            ON t1.`id` = t3.`user_id`
            WHERE t1.id in
            <foreach collection="list" item="item" open="(" separator="," close=")">
                #{item}
            </foreach>
            ORDER BY t1.id DESC
        </if>
        <if test="list==null">

            SELECT
            user.`id` user_id,
            user.`username`,
            user.`station_id`,
            user.`mobile_phone`,
            user.`status`,
            user.`create_time` register_time,
            user.`last_login_time`,
            t.recharge_times,
            t.recharge_amount,
            t1.wash_times,
            t1.amount,
            t1.amount_receivable,
            t1.amount_received,
            t1.discount_money,
            user.`follow_official_account`
            FROM t_user `user`
            LEFT JOIN
            (SELECT user_id,
            COUNT(
            CASE
            WHEN wd.`type` = 1 AND wd.`status` = 1
            THEN 1
            ELSE NULL
            END
            ) recharge_times,
            SUM(
            CASE
            WHEN wd.`type` = 1 AND wd.`status` = 1
            THEN wd.`amount`
            ELSE 0
            END
            ) recharge_amount
            FROM t_wallet_detail wd
            GROUP BY wd.user_id) t
            ON t.user_id = user.id
            LEFT JOIN
            (SELECT user_id,
            COUNT(
            CASE
            WHEN o.`order_status` = 1
            THEN 1
            ELSE NULL
            END
            ) wash_times,
            SUM(
            CASE
            WHEN o.`amount` > 0
            THEN o.`amount`
            ELSE 0
            END
            ) amount,
            SUM(
            CASE
            WHEN o.`amount_receivable` > 0
            THEN o.`amount_receivable`
            ELSE 0
            END
            ) amount_receivable,
            SUM(
            CASE
            WHEN o.`amount_received` > 0
            THEN o.`amount_received`
            ELSE 0
            END
            ) amount_received,
            SUM(
            CASE
            WHEN o.`discount_money` > 0
            THEN o.`discount_money`
            ELSE 0
            END
            ) discount_money
            FROM t_wash_order o
            GROUP BY o.user_id) t1
            ON t1.user_id = user.id
            ORDER BY register_time DESC
        </if>
    </select>

</mapper>
