<?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=amount + #{money},
            update_time=#{time}
        where id = #{id}
          and shop_id = #{shopId}
    </update>
    <update id="decrAccount">
        update tb_shop_user
        set amount=amount - #{money},
            update_time=#{time}
        where id = #{id}
          and shop_id = #{shopId}
          and amount - #{money} >= 0
    </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.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.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 tb_shop_activate_coupon_record AS c
        ON c.shop_id = b.shop_id
        AND c.shop_user_id = b.id
        AND c.`status` = 1
        AND c.use_start_time &lt; NOW()
        AND c.use_end_time > NOW()
        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
        FROM tb_shop_user a
        LEFT JOIN tb_user_info b ON b.id = a.user_id

        -- 预计算优惠券数量
        LEFT JOIN (
        SELECT shop_user_id, COUNT(*) AS couponNum
        FROM tb_shop_activate_coupon_record
        WHERE 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.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.shop_id

        WHERE a.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.shop_id = tb_shop_info.id
            ${qwSql}
    </select>

</mapper>
