<?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.MkShopCouponRecordMapper">


    <select id="queryReceive" resultType="com.czg.account.vo.CouponReceiveVo">
        SELECT
        record.id,
        vip.id as userId ,
        vip.`nick_name`,
        vip.phone as phone,
        record.create_time as receiveTime,
        record.update_time as useTime,
        record.source as source
        FROM
        mk_shop_coupon_record record
        LEFT JOIN tb_shop_user vip
        ON record.shop_user_id = vip.id AND vip.shop_id = #{param.shopId}
        WHERE
        record.coupon_id = #{param.couponId}
        and record.shop_id = #{param.shopId}
        <if test="param.value != null and param.value != '' ">
            and (vip.name like concat('%', #{param.value}, '%') or vip.telephone like concat('%', #{param.value}, '%'))
        </if>
        <if test="param.status != null">
            and record.status = #{param.status}
        </if>
        <if test="param.startTime != null ">
            and record.create_time &gt; #{param.startTime}
        </if>
        <if test="param.endTime != null ">
            and record.create_time &lt; #{param.endTime}
        </if>
        order by record.create_time desc
    </select>
    <select id="findCouponVOByUser" resultType="com.czg.market.vo.UserCouponVO">
        select
        mk_shop_coupon_record.id as id,
        mk_shop_coupon_record.name as name,
        mk_shop_coupon_record.type as type,
        mk_shop_coupon_record.full_amount as fullAmount,
        mk_shop_coupon_record.discount_amount as discountAmount,
        mk_shop_coupon_record.discount_rate as discountRate,
        mk_shop_coupon_record.max_discount_amount as maxDiscountAmount,
        mk_shop_coupon_record.create_time as createTime,
        mk_shop_coupon_record.use_time as useTime,
        mk_shop_coupon_record.use_start_time as effectStartTime,
        mk_shop_coupon_record.use_end_time as effectEndTime,
        mk_shop_coupon_record.status as status,
        coupon.use_shop_type as useShopType,
        coupon.foods as foods,
        coupon.use_shops as useShops,
        coupon.shop_id as shopId,
        coupon.use_type as useType,
        coupon.use_days as useDays,
        coupon.use_time_type as useTimeType,
        coupon.use_start_time as useStartTime,
        coupon.use_end_time as useEndTime,
        coupon.get_limit as getLimit,
        coupon.use_limit as useLimit,
        coupon.discount_share as discountShare,
        coupon.vip_price_share as vipPriceShare,
        coupon.rule_details as ruleDetails,
        coupon.discount_num as discountNum,
        coupon.other_coupon_share as otherCouponShare,
        coupon.use_rule as useRule
        from mk_shop_coupon_record
        left join mk_shop_coupon coupon on mk_shop_coupon_record.coupon_id = coupon.id
        where mk_shop_coupon_record.shop_user_id in
        <foreach collection="shopUserIds" item="userId" open="(" separator="," close=")">
            #{userId}
        </foreach>
        <if test="shopId != null">
            and mk_shop_coupon_record.shop_id = #{shopId}
        </if>
        <if test="name != null and name != ''">
            and mk_shop_coupon_record.name like concat('%', #{name}, '%')
        </if>
        <if test="status != null">
            and mk_shop_coupon_record.status = #{status}
        </if>
        and mk_shop_coupon_record.is_del = 0
        order by mk_shop_coupon_record.create_time DESC ,mk_shop_coupon_record.id DESC
    </select>


    <select id="queryByVipIdAndShopId" resultType="com.czg.account.vo.UserCouponVo">
        SELECT
        inRecord.id as id,
        inRecord.shop_id as shopId,
        shop.shop_name as shopName,
        inRecord.full_amount as fullAmount,
        inRecord.discount_amount as discountAmount,
        inRecord.discount_rate as discountRate,
        inRecord.max_discount_amount as maxDiscountAmount,
        inRecord.coupon_id as couponId,
        inRecord.name as name,
        inRecord.type,
        coupon.foods as foods,
        coupon.use_rule as useRule,
        coupon.rule_details as ruleDetails,
        coupon.discount_num as discountNum,

        inRecord.type as type,
        inRecord.create_time as createTime,
        inRecord.use_time as useTime,
        inRecord.use_start_time as effectStartTime,
        inRecord.use_end_time as effectEndTime,
        inRecord.status as status,
        coupon.use_shop_type as useShopType,
        coupon.use_shops as useShops,
        coupon.use_type as useType,
        coupon.use_days as useDays,
        coupon.use_time_type as useTimeType,
        coupon.use_start_time as useStartTime,
        coupon.use_end_time as useEndTime,
        coupon.get_limit as getLimit,
        coupon.use_limit as useLimit,
        coupon.discount_share as discountShare,
        coupon.other_coupon_share as otherCouponShare,
        coupon.vip_price_share as vipPriceShare,
        coupon.rule_details as ruleDetails
        FROM
        mk_shop_coupon_record inRecord
        LEFT JOIN tb_shop_info shop ON inRecord.shop_id = shop.id
        LEFT JOIN mk_shop_coupon coupon ON inRecord.coupon_id = coupon.id
        WHERE
        inRecord.shop_user_id = #{shopUserId}
        <if test="shopId != null and shopId != ''">
            and inRecord.shop_id = #{shopId}
        </if>
        <choose>
            <when test="type != null ">
                and inRecord.type = #{type}
            </when>
            <otherwise>
                <if test="isFood != null">
                    and inRecord.type != 2
                </if>
            </otherwise>
        </choose>
        and inRecord.status = 0
        and inRecord.use_start_time &lt; now()
        and inRecord.use_end_time &gt; now()
        and inRecord.is_del = 0
        order by inRecord.use_end_time
    </select>
    <select id="getShopByCouponRecord" resultType="com.czg.account.vo.ShopInfoCouponVO">
        SELECT inRecord.shop_id          AS shopId,
               shop.shop_name            AS shopName,
               shop.address              AS shopAddress,
               shop.chain_name           AS chainName,
               COUNT(inRecord.coupon_id) AS couponCount
        FROM mk_shop_coupon_record inRecord
                 LEFT JOIN tb_shop_info shop ON inRecord.shop_id = shop.id
        WHERE inRecord.user_id = #{userId}
          AND inRecord.is_del = 0
        GROUP BY inRecord.shop_id
    </select>

    <select id="countRecordBySource" resultType="com.czg.market.vo.CouponRecordCountVO">
        SELECT CASE
                   WHEN source LIKE '充值赠券%' THEN '充值赠券'
                   WHEN source LIKE '购买会员赠券%' THEN '购买会员赠券'
                   ELSE source
                   END  AS sourceType,
               COUNT(1) AS count
        FROM `mk_shop_coupon_record`
        where (coupon_id = #{couponId} or coupon_sync_id = #{couponId})
        <if test="userId != null">
            AND user_id = #{userId}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="source != null and source != ''">
            <choose>
                <when test='source == "充值赠券"'>
                    AND source LIKE '充值赠券%'
                </when>
                <when test='source == "购买会员赠券"'>
                    AND source LIKE '购买会员赠券%'
                </when>
                <otherwise>
                    AND source = #{source}
                </otherwise>
            </choose>
        </if>
        <if test="startTime != null and endTime != null and startTime != '' and endTime != ''">
            AND create_time BETWEEN #{startTime} AND #{endTime}
        </if>
        GROUP BY sourceType;

    </select>
</mapper>
