<?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.market.mapper.SmsShopMoneyMapper">

    <!-- 关联查询店铺短信余额及店铺信息，支持店铺名称模糊搜索 -->
    <select id="selectWithShopInfo" parameterType="java.lang.String" resultType="com.czg.market.vo.SmsShopMoneyVO">
        SELECT
        t.id as shopId,
        COALESCE(s.money, 0) as money,
        COALESCE(s.send_total, 0) as sendTotal,
        COALESCE(s.amount_total, 0) as amountTotal,
        COALESCE(s.month_send_total, 0) as monthSendTotal,
        COALESCE(s.month_amount_total, 0) as monthAmountTotal,
        s.create_time as createTime,
        s.update_time as updateTime,
        t.logo,
        t.shop_type AS shopType,
        t.profiles,
        t.cover_img AS coverImg,
        t.main_id AS mainId,
        main_shop.shop_name AS mainShopName,
        t.shop_name AS shopName
        FROM
        sms_shop_money s
        right JOIN
        tb_shop_info t ON s.shop_id = t.id
        LEFT join
        tb_shop_info main_shop ON t.main_id = main_shop.id
        <where>
            <!-- 店铺名称模糊查询条件 -->
            <if test="name != null and name != ''">
                AND t.shop_name LIKE CONCAT('%', #{name}, '%')
            </if>
        </where>
        order by shopId asc
    </select>
</mapper>
