<?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.czg.service.account.mapper.ShopUserMapper">
    <update id="incrAccount">
        update tb_shop_user
        set amount      = COALESCE(amount, 0) + #{money},
            update_time = #{time}
        where id = #{id}

    </update>
    <update id="decrAccount">
        update tb_shop_user
        set amount=amount - #{money},
            update_time=#{time}
        where id = #{id}
          and amount - #{money} >= 0
    </update>
    <update id="updateOneOrTwoAmount">
        update tb_shop_user_invite
        <set>
            <if test="isOne != null and isOne == 1">
                one_income = one_income + #{amount}
            </if>
            <if test="isOne != null and isOne == 2">
                two_income = two_income + #{amount}
            </if>
        </set>
        where shop_user_id = #{shopUserId}
        and shop_id = #{shopId}
    </update>


    <select id="selectUserSummary" resultType="com.czg.account.dto.shopuser.ShopUserSummaryDTO">
        select count(a.id) userTotal, sum(IFNULL(a.amount, 0)) balanceTotal,sum(IFNULL(b.amount,0)) chargeTotal from
        tb_shop_user as a
        left join tb_shop_user_flow as b on a.id=b.user_id and b.biz_code in ('cashIn', 'wechatIn',
        'alipayIn')
        where a.main_shop_id = #{shopId}
        <if test="isVip !=null">
            and a.is_vip=#{isVip}
        </if>
    </select>

    <select id="selectVipCard" resultType="com.czg.account.dto.shopuser.ShopUserVipCardDTO">
        select tb_shop_info.logo, tb_shop_info.shop_name shopName, tb_shop_user.amount, tb_shop_user.shop_id shopId
        from tb_shop_user
                 left join tb_shop_info on tb_shop_user.main_shop_id = tb_shop_info.id
            ${qwSql}
        limit ${pageOffset}, ${pageSize}
    </select>

    <select id="selectAssetsSummary" resultType="com.czg.account.dto.user.userinfo.UserInfoAssetsSummaryDTO">
        SELECT IFNULL(SUM(b.amount), 0)         AS amount,
               IFNULL(SUM(b.account_points), 0) AS accountPoints,
               COUNT(DISTINCT c.id)             AS couponNum
        FROM tb_shop_user AS b
                 LEFT JOIN mk_shop_coupon_record AS c
                           ON c.shop_user_id = b.id
                               AND c.`status` = 1
                               AND c.use_start_time &lt; NOW()
                               AND c.use_end_time > NOW()
                               AND c.is_del = 0
        WHERE b.user_id = #{userId};


    </select>
    <select id="selectPageByKeyAndIsVip" resultType="com.czg.account.dto.shopuser.ShopUserDTO">
        SELECT
        a.*,
        IFNULL(c.couponNum, 0) AS couponNum,
        IFNULL(d.orderNumber, 0) AS orderNumber,
        IFNULL(f.rechargeAmount, 0) AS rechargeAmount,
        c.name as memberLevelName,
        c.discount
        FROM tb_shop_user a
        LEFT JOIN tb_user_info b ON b.id = a.user_id
        LEFT JOIN tb_member_level_config c on c.id=a.member_level_id

        -- 预计算优惠券数量
        LEFT JOIN (
        SELECT shop_user_id, COUNT(*) AS couponNum
        FROM mk_shop_coupon_record
        WHERE
        is_del = 0
        # AND status = 0
        # AND use_start_time &lt; NOW()
        # AND use_end_time > NOW()
        GROUP BY shop_user_id
        ) c ON c.shop_user_id = a.id

        -- 预计算订单数量
        LEFT JOIN (
        SELECT user_id, shop_id, COUNT(*) AS orderNumber
        FROM tb_order_info
        GROUP BY user_id, shop_id
        ) d ON d.user_id = a.user_id AND d.shop_id = a.main_shop_id

        -- 预计算充值总金额
        LEFT JOIN (
        SELECT user_id, shop_id, SUM(amount) AS rechargeAmount
        FROM tb_shop_user_flow
        WHERE biz_code IN ('cashIn', 'wechatIn', 'alipayIn')
        GROUP BY user_id, shop_id
        ) f ON f.user_id = a.user_id AND f.shop_id = a.main_shop_id

        WHERE a.main_shop_id = #{shopId}

        <if test="isVip != null">
            AND a.is_vip = #{isVip}
        </if>

        <if test="key != null and key != ''">
            AND (a.nick_name LIKE CONCAT('%', #{key}, '%')
            OR a.phone LIKE CONCAT('%', #{key}, '%'))
        </if>

        <if test="amount != null">
            AND a.amount >= #{amount}
        </if>

        ORDER BY a.create_time DESC
    </select>
    <select id="selectVipCard_COUNT" resultType="java.lang.Long">
        select count(1)
        from tb_shop_user
                 left join tb_shop_info on tb_shop_user.main_shop_id = tb_shop_info.id
            ${qwSql}
    </select>


    <select id="selectPushEventUser" resultType="com.czg.account.entity.ShopUser">
        SELECT DISTINCT
        u.id,u.user_id, u.head_img, u.nick_name, u.amount, u.account_points, u.phone,
        <choose>
            <!-- 当有筛选条件时才计算订单相关字段 -->
            <when test="param.orderTimeToday == 1 or param.orderTimeYesterday == 1 or
                   param.orderTimeTwoWeeks == 1 or param.orderTimeMoreThanTwoWeeks == 1 or
                   param.noOrder == 1 or param.oneOrder == 1 or param.fiveOrder == 1">
                COUNT(o.id) AS order_count,
                SUM(CASE WHEN o.trade_day = CURDATE() THEN 1 ELSE 0 END) AS today_orders,
                SUM(CASE WHEN o.trade_day = CURDATE() - INTERVAL 1 DAY THEN 1 ELSE 0 END) AS yesterday_orders,
                SUM(CASE WHEN o.trade_day >= CURDATE() - INTERVAL 2 WEEK AND o.trade_day &lt; CURDATE() - INTERVAL 1
                DAY
                THEN 1 ELSE 0 END) AS two_weeks_orders,
                SUM(CASE WHEN o.trade_day &lt; CURDATE() - INTERVAL 2 WEEK THEN 1 ELSE 0 END) AS earlier_orders
            </when>
            <!-- 无筛选条件时不计算订单相关字段 -->
            <otherwise>
                0 AS order_count,
                0 AS today_orders,
                0 AS yesterday_orders,
                0 AS two_weeks_orders,
                0 AS earlier_orders
            </otherwise>
        </choose>
        FROM tb_shop_user u
        <!-- 只有当有筛选条件时才关联order表 -->
        <if test="param.orderTimeToday == 1 or param.orderTimeYesterday == 1 or
              param.orderTimeTwoWeeks == 1 or param.orderTimeMoreThanTwoWeeks == 1 or
              param.noOrder == 1 or param.oneOrder == 1 or param.fiveOrder == 1">
            LEFT JOIN `tb_order_info` o ON u.user_id = o.user_id and o.user_id is not null AND o.shop_id = #{shopId}
            AND o.status = 'done'
        </if>
        WHERE u.main_shop_id = #{mainShopId} and u.phone is not null and u.user_id is not null
        <!-- 性别筛选条件 -->
        <if test="!(param.sexMan == 1 and param.sexWoman == 1 and param.sexUnknown == 1)
          and (param.sexMan == 1 or param.sexWoman == 1 or param.sexUnknown == 1)">
            AND
            <trim prefix="(" suffix=")" prefixOverrides="OR">
                <if test="param.sexMan == 1">OR u.sex = 1</if>
                <if test="param.sexWoman == 1">OR u.sex = 0</if>
                <if test="param.sexUnknown == 1">OR u.sex IS NULL</if>
            </trim>
        </if>

        <!-- 会员状态筛选 -->
        <if test="param.isVip != null">
            AND u.is_vip = #{param.isVip}
        </if>

        <if test="param.isRecharge != null">
            AND u.recharge_count <![CDATA[ ${param.isRecharge == 0 ? '=' : '>'} ]]> 0
        </if>
        <!-- 只有当有筛选条件时才需要HAVING子句 -->
        <if test="param.orderTimeToday == 1 or param.orderTimeYesterday == 1 or
              param.orderTimeTwoWeeks == 1 or param.orderTimeMoreThanTwoWeeks == 1 or
              param.noOrder == 1 or param.oneOrder == 1 or param.fiveOrder == 1">
            GROUP BY u.id
            HAVING
            <trim prefix="(" suffix=")" prefixOverrides="OR">
                <if test="param.orderTimeToday == 1">OR today_orders > 0</if>
                <if test="param.orderTimeYesterday == 1">OR yesterday_orders > 0</if>
                <if test="param.orderTimeTwoWeeks == 1">OR two_weeks_orders > 0</if>
                <if test="param.orderTimeMoreThanTwoWeeks == 1">OR earlier_orders > 0</if>

                <if test="param.noOrder == 1">OR order_count = 0</if>
                <if test="param.oneOrder == 1">OR order_count = 1</if>
                <if test="param.fiveOrder == 1">OR order_count >= 5</if>
            </trim>
        </if>
        order by u.create_time desc
    </select>

    <select id="selectAcPushEventUser" resultType="com.czg.account.entity.ShopUser">
        SELECT DISTINCT
        u.id,u.user_id, u.head_img, u.nick_name, u.amount, u.account_points, u.phone,
        <choose>
            <!-- 当有筛选条件时才计算订单相关字段 -->
            <when test="param.orderTimeToday == 1 or param.orderTimeYesterday == 1 or
                   param.orderTimeTwoWeeks == 1 or param.orderTimeMoreThanTwoWeeks == 1 or
                   param.noOrder == 1 or param.oneOrder == 1 or param.fiveOrder == 1">
                COUNT(o.id) AS order_count,
                SUM(CASE WHEN o.trade_day = CURDATE() THEN 1 ELSE 0 END) AS today_orders,
                SUM(CASE WHEN o.trade_day = CURDATE() - INTERVAL 1 DAY THEN 1 ELSE 0 END) AS yesterday_orders,
                SUM(CASE WHEN o.trade_day >= CURDATE() - INTERVAL 2 WEEK AND o.trade_day &lt; CURDATE() - INTERVAL 1
                DAY
                THEN 1 ELSE 0 END) AS two_weeks_orders,
                SUM(CASE WHEN o.trade_day &lt; CURDATE() - INTERVAL 2 WEEK THEN 1 ELSE 0 END) AS earlier_orders
            </when>
            <!-- 无筛选条件时不计算订单相关字段 -->
            <otherwise>
                0 AS order_count,
                0 AS today_orders,
                0 AS yesterday_orders,
                0 AS two_weeks_orders,
                0 AS earlier_orders
            </otherwise>
        </choose>
        FROM tb_shop_user u
        inner join tb_user_info user on u.user_id = user.id and user.is_ac = 1
        <!-- 只有当有筛选条件时才关联order表 -->
        <if test="param.orderTimeToday == 1 or param.orderTimeYesterday == 1 or
              param.orderTimeTwoWeeks == 1 or param.orderTimeMoreThanTwoWeeks == 1 or
              param.noOrder == 1 or param.oneOrder == 1 or param.fiveOrder == 1">
            LEFT JOIN `tb_order_info` o ON u.user_id = o.user_id and o.user_id is not null AND o.shop_id = #{shopId}
            AND o.status = 'done'
        </if>
        WHERE u.main_shop_id = #{mainShopId} and u.user_id is not null
        <!-- 性别筛选条件 -->
        <if test="!(param.sexMan == 1 and param.sexWoman == 1 and param.sexUnknown == 1)
          and (param.sexMan == 1 or param.sexWoman == 1 or param.sexUnknown == 1)">
            AND
            <trim prefix="(" suffix=")" prefixOverrides="OR">
                <if test="param.sexMan == 1">OR u.sex = 1</if>
                <if test="param.sexWoman == 1">OR u.sex = 0</if>
                <if test="param.sexUnknown == 1">OR u.sex IS NULL</if>
            </trim>
        </if>

        <!-- 会员状态筛选 -->
        <if test="param.isVip != null">
            AND u.is_vip = #{param.isVip}
        </if>

        <if test="param.isRecharge != null">
            AND u.recharge_count <![CDATA[ ${param.isRecharge == 0 ? '=' : '>'} ]]> 0
        </if>
        <!-- 只有当有筛选条件时才需要HAVING子句 -->
        <if test="param.orderTimeToday == 1 or param.orderTimeYesterday == 1 or
              param.orderTimeTwoWeeks == 1 or param.orderTimeMoreThanTwoWeeks == 1 or
              param.noOrder == 1 or param.oneOrder == 1 or param.fiveOrder == 1">
            GROUP BY u.id
            HAVING
            <trim prefix="(" suffix=")" prefixOverrides="OR">
                <if test="param.orderTimeToday == 1">OR today_orders > 0</if>
                <if test="param.orderTimeYesterday == 1">OR yesterday_orders > 0</if>
                <if test="param.orderTimeTwoWeeks == 1">OR two_weeks_orders > 0</if>
                <if test="param.orderTimeMoreThanTwoWeeks == 1">OR earlier_orders > 0</if>

                <if test="param.noOrder == 1">OR order_count = 0</if>
                <if test="param.oneOrder == 1">OR order_count = 1</if>
                <if test="param.fiveOrder == 1">OR order_count >= 5</if>
            </trim>
        </if>
        order by u.create_time desc
    </select>
    <select id="getInviteUser" resultType="com.czg.market.vo.InviteUserVO">
        SELECT
        u.id AS shopUserId,
        u.head_img AS headImg,
        u.nick_name AS shopUserName,
        u.phone AS shopUserPhone,
        invite.one_income AS oneIncome,
        invite.invite_time AS inviteTime,
        dist.total_income AS totalIncome,
        dist.pending_income AS pendingIncome,
        dist.received_income AS receivedIncome,
        dist.withdrawn_income AS withdrawnIncome,
        dist.status AS status,
        dist.distribution_level_id AS levelId,
        dist.distribution_level_name AS levelName,
        u.distribution_shops AS distributionShops
        FROM tb_shop_user_invite invite
        left join tb_shop_user u on invite.shop_user_id = u.id
        left join mk_distribution_user dist on u.id = dist.id and dist.shop_id = #{shopId}
        WHERE invite.`distribution_user_id` = #{distributionUserId} and invite.shop_id = #{shopId}
        <if test="distributionLevelId != null">and dist.distribution_level_id = #{distributionLevelId}</if>
        <if test="shopUserId != null">and invite.shop_user_id = #{shopUserId}</if>
        ORDER BY invite.`invite_time` DESC
    </select>
</mapper>
