<?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.yami.shop.coupon.comment.dao.CouponMapper">
    <resultMap id="BaseResultMap" type="com.yami.shop.coupon.comment.model.Coupon">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="coupon_id" jdbcType="BIGINT" property="couponId"/>
        <result column="shop_id" jdbcType="BIGINT" property="shopId"/>
        <result column="coupon_name" jdbcType="VARCHAR" property="couponName"/>
        <result column="sub_title" jdbcType="VARCHAR" property="subTitle"/>
        <result column="coupon_type" jdbcType="TINYINT" property="couponType"/>
        <result column="cash_condition" jdbcType="DECIMAL" property="cashCondition"/>
        <result column="reduce_amount" jdbcType="DECIMAL" property="reduceAmount"/>
        <result column="coupon_discount" jdbcType="DECIMAL" property="couponDiscount"/>
        <result column="valid_time_type" jdbcType="TINYINT" property="validTimeType"/>
        <result column="start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="after_receive_days" jdbcType="INTEGER" property="afterReceiveDays"/>
        <result column="valid_days" jdbcType="INTEGER" property="validDays"/>
        <result column="stocks" jdbcType="INTEGER" property="stocks"/>
        <result column="source_stock" jdbcType="INTEGER" property="sourceStock"/>
        <result column="suitable_prod_type" jdbcType="TINYINT" property="suitableProdType"/>
        <result column="limit_num" jdbcType="INTEGER" property="limitNum"/>
        <result column="version" jdbcType="INTEGER" property="version"/>
        <result column="is_score_type" jdbcType="INTEGER" property="isScoreType"/>
        <result column="score_price" jdbcType="INTEGER" property="scorePrice"/>
        <result column="overdue_status" jdbcType="TINYINT" property="overdueStatus"/>
        <result column="puton_status" jdbcType="TINYINT" property="putonStatus"/>
    </resultMap>

    <resultMap id="CouponAndCouponProdMap" type="com.yami.shop.coupon.comment.model.Coupon">
        <id column="coupon_id" jdbcType="BIGINT" property="couponId"/>
        <result column="shop_id" jdbcType="BIGINT" property="shopId"/>
        <result column="coupon_name" jdbcType="VARCHAR" property="couponName"/>
        <result column="sub_title" jdbcType="VARCHAR" property="subTitle"/>
        <result column="get_way" jdbcType="TINYINT" property="getWay"/>
        <result column="coupon_type" jdbcType="TINYINT" property="couponType"/>
        <result column="cash_condition" jdbcType="DECIMAL" property="cashCondition"/>
        <result column="reduce_amount" jdbcType="DECIMAL" property="reduceAmount"/>
        <result column="coupon_discount" jdbcType="DECIMAL" property="couponDiscount"/>
        <result column="valid_time_type" jdbcType="TINYINT" property="validTimeType"/>
        <result column="start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="after_receive_days" jdbcType="INTEGER" property="afterReceiveDays"/>
        <result column="valid_days" jdbcType="INTEGER" property="validDays"/>
        <result column="stocks" jdbcType="INTEGER" property="stocks"/>
        <result column="source_stock" jdbcType="INTEGER" property="sourceStock"/>
        <result column="suitable_prod_type" jdbcType="TINYINT" property="suitableProdType"/>
        <result column="limit_num" jdbcType="INTEGER" property="limitNum"/>
        <result column="version" jdbcType="INTEGER" property="version"/>
        <result column="overdue_status" jdbcType="TINYINT" property="overdueStatus"/>
        <result column="puton_status" jdbcType="TINYINT" property="putonStatus"/>
        <collection property="couponProds" ofType="com.yami.shop.coupon.comment.model.CouponProd">
            <id column="coupon_prod_id" jdbcType="BIGINT" property="couponProdId"/>
            <!--下面注释不要打开，有bug-->
            <!--<result column="coupon_id" jdbcType="BIGINT" property="couponId"/>-->
            <result column="prod_id" jdbcType="BIGINT" property="prodId"/>
            <result column="prod_name" jdbcType="VARCHAR" property="prodName"/>
            <result column="pic" jdbcType="VARCHAR" property="pic"/>
        </collection>
    </resultMap>

    <resultMap id="couponOrderDtoMap" type="com.yami.shop.bean.app.dto.CouponOrderDto">
        <!--<id column="coupon_id" jdbcType="BIGINT" property="couponId"/>-->
        <result column="coupon_user_id" jdbcType="BIGINT" property="couponUserId"/>
        <result column="coupon_id" jdbcType="BIGINT" property="couponId"/>
        <result column="shop_id" jdbcType="BIGINT" property="shopId"/>
        <result column="coupon_name" jdbcType="VARCHAR" property="couponName"/>
        <result column="sub_title" jdbcType="VARCHAR" property="subTitle"/>
        <result column="coupon_type" jdbcType="TINYINT" property="couponType"/>
        <result column="cash_condition" jdbcType="DECIMAL" property="cashCondition"/>
        <result column="reduce_amount" jdbcType="DECIMAL" property="reduceAmount"/>
        <result column="coupon_discount" jdbcType="DECIMAL" property="couponDiscount"/>
        <result column="user_start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="user_end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="suitable_prod_type" jdbcType="TINYINT" property="suitableProdType"/>
        <result column="coupon_user_id" jdbcType="BIGINT" property="couponUserId"/>
        <result column="receive_time" jdbcType="TIMESTAMP" property="receiveTime"/>
        <result column="valid_time_type" jdbcType="BIGINT" property="validTimeType"/>
        <result column="after_receive_days" jdbcType="BIGINT" property="afterReceiveDays"/>
        <result column="valid_days" jdbcType="BIGINT" property="validDays"/>
        <collection property="prodIds" javaType="java.util.List" ofType="long">
            <result column="prodIds"></result>
        </collection>
    </resultMap>



    <resultMap id="couponProdDtoMap" type="com.yami.shop.bean.app.dto.CouponDto">
        <id column="coupon_id" jdbcType="BIGINT" property="couponId"/>
        <result column="shop_id" jdbcType="BIGINT" property="shopId"/>
        <result column="shop_name" jdbcType="VARCHAR" property="shopName"/>
        <result column="shop_logo" jdbcType="VARCHAR" property="shopLogo"/>
        <result column="coupon_name" jdbcType="VARCHAR" property="couponName"/>
        <result column="sub_title" jdbcType="VARCHAR" property="subTitle"/>
        <result column="coupon_type" jdbcType="TINYINT" property="couponType"/>
        <result column="cash_condition" jdbcType="DECIMAL" property="cashCondition"/>
        <result column="reduce_amount" jdbcType="DECIMAL" property="reduceAmount"/>
        <result column="coupon_discount" jdbcType="DECIMAL" property="couponDiscount"/>
        <result column="source_stock" jdbcType="INTEGER" property="sourceStock"/>
        <result column="stocks" jdbcType="INTEGER" property="stocks"/>
        <result column="start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="suitable_prod_type" jdbcType="TINYINT" property="suitableProdType"/>
        <result column="coupon_user_id" jdbcType="BIGINT" property="couponUserId"/>
        <result column="receive_time" jdbcType="TIMESTAMP" property="receiveTime"/>
        <result column="valid_time_type" jdbcType="BIGINT" property="validTimeType"/>
        <result column="after_receive_days" jdbcType="BIGINT" property="afterReceiveDays"/>
        <result column="valid_days" jdbcType="BIGINT" property="validDays"/>
        <result column="cur_user_receive_count" jdbcType="BIGINT" property="curUserReceiveCount"/>
        <result column="user_use_count" jdbcType="INTEGER" property="userHasCount"/>
        <result column="limit_num" jdbcType="INTEGER" property="limitNum"/>
        <collection property="prods" javaType="java.util.List" ofType="com.yami.shop.bean.app.dto.ProductDto" column="coupon_id">
            <id column="prod_id" jdbcType="BIGINT" property="prodId"/>
            <result column="prod_name" jdbcType="VARCHAR" property="prodName"/>
            <result column="pic" jdbcType="VARCHAR" property="pic"/>
            <result column="price" jdbcType="DECIMAL" property="price"/>
        </collection>
    </resultMap>


    <select id="getCouponAndCouponProdsByCouponId" resultMap="CouponAndCouponProdMap">
        select c.*,cp.*,p.* from tz_coupon c
        left join tz_coupon_prod cp on c.coupon_id = cp.coupon_id
        left join tz_prod p on cp.prod_id = p.prod_id
        where c.coupon_id = #{id}
</select>

    <update id="updateCouponStocksAndVersion">
    update tz_coupon set version = version + 1, stocks = stocks - 1 where coupon_id = #{couponId} and stocks > 0
  </update>

    <update id="updateOverdueStatusByTime">
    update tz_coupon set overdue_status = 0, puton_status = -1 where end_time &lt; #{now} and valid_time_type = 1
  </update>

    <update id="updatePutonStatusByStocks">
    update tz_coupon set puton_status = -1 where stocks = 0
  </update>

    <update id="putonCoupon">
    update tz_coupon set puton_status = 1 where start_time &lt;= #{now} and overdue_status = 1 and stocks &gt; 0 and puton_status = 0
  </update>

    <select id="getCouponList" resultMap="couponProdDtoMap">

        SET @tmp=NULL;
        SET @rank=NULL;
        set @id=NULL;
        <!--指定商品参与-->
        <if test="couponForProd != null and couponForProd.size() > 0">
            SELECT (SELECT COUNT(*) FROM tz_coupon_user WHERE coupon_id = pc.coupon_id AND user_id =  #{userId} and status = 1) AS cur_user_receive_count ,
            (SELECT COUNT(*) FROM tz_coupon_user WHERE coupon_id = pc.coupon_id AND user_id = #{userId}  and status = 1) AS user_use_count,pc.*
                FROM ( SELECT cp.prod_id,cp.pic,cp.price,cp.prod_name,c.*
                    FROM (
                            SELECT IF(@tmp=cp.coupon_id,@rank:=@rank + 1,@rank:=1) AS new_rank,@tmp:=coupon_id AS tmp,cp.coupon_prod_id,cp.coupon_id,p.prod_id,p.pic,p.price,p.prod_name
                            FROM tz_coupon_prod cp
                            JOIN tz_prod p ON p.prod_id = cp.`prod_id` AND p.`status` = 1
                            WHERE cp.coupon_id IN
                                <foreach collection="couponForProd" item="couponId" separator="," open="(" close=")">
                                    #{couponId}
                                </foreach>
                            ORDER BY cp.coupon_id
                        ) AS cp
                    JOIN tz_coupon c ON c.coupon_id = cp.coupon_id WHERE cp.new_rank &lt;= 3
                ) AS pc
            <if test="(couponForAllProd != null and couponForAllProd.size() > 0) or (couponForElseProd != null and couponForElseProd.size() > 0)">
                UNION
            </if>
        </if>
        <if test="(couponForAllProd != null and couponForAllProd.size() > 0) or (couponForElseProd != null and couponForElseProd.size() > 0)">
            SELECT (SELECT COUNT(*) FROM tz_coupon_user WHERE coupon_id = pc.coupon_id AND user_id = #{userId}) AS cur_user_receive_count ,
            (SELECT COUNT(*) FROM tz_coupon_user WHERE coupon_id = pc.coupon_id AND user_id = #{userId}  and status = 1) AS user_use_count,pc.*
            FROM  (
                SELECT temp.prod_id,temp.pic,temp.price,temp.prod_name,c.*
                FROM (
                    <!--全部商品参与-->
                    <foreach collection="couponForAllProd" item="coupon" separator="UNION">
                        (SELECT @id:=#{coupon.couponId} AS temp_id,p.* FROM tz_prod p WHERE p.status = 1 AND p.shop_id = #{coupon.shopId} LIMIT 0,3)
                    </foreach>
                    <if test="couponForElseProd != null and couponForElseProd.size() > 0">
                        UNION
                    </if>
                    <!--指定商品不参与-->
                    <if test="couponForElseProd != null and couponForElseProd.size() > 0">
                        <foreach collection="couponForElseProd" item="coupon" separator="UNION">
                            (SELECT @id:=#{coupon.couponId} AS temp_id,p.* FROM tz_prod p WHERE p.status = 1 AND p.shop_id = #{coupon.shopId}
                              AND p.prod_id NOT IN (SELECT prod_id FROM tz_coupon_prod WHERE coupon_id = #{coupon.couponId}) LIMIT 0,3)
                        </foreach>
                    </if>
                ) AS temp
                JOIN tz_coupon c
                ON c.coupon_id = temp.temp_id
            ) AS pc
        </if>
    </select>

    <!--<select id="generalCouponList" resultType="com.yami.shop.bean.app.dto.CouponDto">-->
        <!--SELECT tc.*,tsd.`shop_name`-->
        <!--FROM tz_coupon tc-->
        <!--JOIN tz_shop_detail tsd-->
        <!--ON tsd.`shop_id` = tc.`shop_id`-->
        <!--WHERE-->
        <!--tc.suitable_prod_type =0 &#45;&#45; 获取全部商品券-->
          <!--&lt;!&ndash;AND tc.`stocks` >0&ndash;&gt;-->
          <!--AND tc.`overdue_status` =1 AND tc.`puton_status` = 1 and tsd.shop_status = 1-->
          <!--AND tc.`is_score_type` <![CDATA[<>]]> 1-->
        <!--ORDER BY tc.start_time DESC-->
    <!--</select>-->

    <select id="generalCouponList" resultType="com.yami.shop.bean.app.dto.CouponDto">
        SELECT tc.*, (SELECT COUNT(*) FROM tz_coupon_user WHERE coupon_id = tc.coupon_id AND user_id = #{userId}) AS cur_user_receive_count,
        (SELECT COUNT(*) FROM tz_coupon_user WHERE coupon_id = tc.coupon_id AND user_id = #{userId}  and status = 1) AS user_has_count
        FROM tz_coupon tc
        WHERE tc.shop_id = 0
        AND tc.`overdue_status` =1 AND tc.`puton_status` = 1
        AND tc.`is_score_type` <![CDATA[<>]]> 1 AND tc.`get_way` <![CDATA[<>]]> 1
        ORDER BY tc.coupon_id DESC
    </select>

    <select id="getCouponListByStatus" resultType="com.yami.shop.bean.app.dto.CouponDto">
        SELECT tc.*,tcu.*,sd.shop_name
        FROM tz_coupon_user tcu
        INNER JOIN tz_coupon tc
        ON tcu.`coupon_id` = tc.`coupon_id` AND tc.puton_status NOT IN (2,3)
        LEFT JOIN tz_shop_detail sd
        ON tc.`shop_id` = sd.shop_id
        WHERE tcu.`user_id` = #{userId}
        AND tcu.`status` = #{status}
        AND tcu.is_delete = 0
    </select>
         <!--AND tc.puton_status = 1-->

    <select id="getCouponListByShopIds" resultMap="couponOrderDtoMap">
      select tc.*,tcp.prod_id as prodIds,tcu.* from tz_coupon tc
        join tz_coupon_user tcu
          on tc.coupon_id = tcu.coupon_id
        left join tz_coupon_prod tcp
          on tcp.coupon_id = tc.coupon_id
      where tcu.user_id = #{userId}
          -- 获取用户未使用的优惠券
        AND tc.puton_status NOT IN (2,3)
        and tcu.status = 1
        and tcu.is_delete = 0
        and tc.shop_id = #{shopId}
    </select>
        <!--and tc.puton_status = 1-->

    <select id="listOverdueStatusShopIds" resultType="long">
        SELECT shop_id FROM tz_coupon
        WHERE end_time &lt; #{now}
        and valid_time_type = 1
        GROUP BY shop_id
    </select>
    <select id="listPutonCouponAndCouponProdsByShopId" resultMap="CouponAndCouponProdMap">
        SELECT c.*,cp.*,p.*
        FROM tz_coupon c
                 LEFT JOIN tz_coupon_prod cp ON c.coupon_id = cp.coupon_id
                 LEFT JOIN tz_prod p ON cp.prod_id = p.prod_id
        WHERE c.`overdue_status` = 1 AND c.puton_status =1 AND c.shop_id =#{shopId} AND c.`is_score_type` <![CDATA[<>]]> 1
        ORDER BY c.`start_time` DESC
    </select>
    <select id="listCouponIdsByUserId" resultType="java.lang.Long">
        SELECT tcu.coupon_id
        FROM tz_coupon_user tcu
        LEFT JOIN tz_coupon tc
            ON tcu.`coupon_id` = tc.`coupon_id`
        WHERE tcu.`user_id` = #{userId}
          AND tcu.is_delete = 0
    </select>
          <!--AND  tc.puton_status = 1-->

    <select id="getCouponCountByStatus" resultType="map">
      SELECT
      COUNT(CASE WHEN cu.`status` = 0 THEN 1 ELSE NULL END) AS 'expiredCount' ,-- 失效
      COUNT(CASE WHEN cu.`status` = 1 THEN 1 ELSE NULL END) AS 'unUseCount', -- 有效
      COUNT(CASE WHEN cu.`status` = 2 THEN 1 ELSE NULL END) AS 'useCount'  -- 过期
      FROM tz_coupon_user cu
      INNER JOIN tz_coupon c
      ON c.`coupon_id` = cu.`coupon_id` AND c.puton_status NOT IN (2,3)
      WHERE cu.`user_id` =#{userId}
      AND cu.`is_delete` = 0
    </select>
      <!--AND c.`puton_status` =1-->

    <select id="getPlatformPage" resultType="com.yami.shop.coupon.comment.model.Coupon">
        SELECT c.*,sd.shop_name `shop_name` FROM tz_coupon c
        LEFT JOIN tz_shop_detail sd ON c.`shop_id` = sd.`shop_id`
        <where>
            <if test="coupon.shopId != null">
                AND c.shop_id = #{coupon.shopId}
            </if>
            <if test="coupon.shopId == null">
                AND c.shop_id != 0
            </if>
            <if test="coupon.couponName !=null">
                AND c.coupon_name LIKE concat("%",#{coupon.couponName},"%")
            </if>
            <if test="coupon.putonStatus != null">
                AND c.puton_status = #{coupon.putonStatus}
            </if>
            <if test="coupon.overdueStatus != null">
                AND c.overdue_status = #{coupon.overdueStatus}
            </if>
            <if test="coupon.endTime != null">
                AND c.end_time &lt;= #{coupon.endTime}
            </if>
            <if test="coupon.startTime != null">
                AND c.start_time &gt;= #{coupon.startTime}
            </if>
            <if test="coupon.couponType != null">
                AND c.coupon_type = #{coupon.couponType}
            </if>
            <if test="coupon.isScoreType != null">
                AND c.is_score_type = #{coupon.isScoreType}
            </if>
            <if test="coupon.shopName != null">
                AND sd.shop_name LIKE concat("%",#{coupon.shopName},"%")
            </if>
        </where>
        ORDER BY c.`coupon_id` DESC
    </select>

    <update id="updatePutOnStatusByCouponId">
        UPDATE tz_coupon c SET c.`puton_status` =#{status} WHERE c.`coupon_id` = #{couponId}
    </update>
    <update id="updateOverdueStatusByCouponId">
        UPDATE tz_coupon c SET c.`overdue_status` =#{overdueStatus} WHERE c.`coupon_id` = #{couponId}
    </update>
    <select id="getCouponPageByCouponDto" resultMap="couponProdDtoMap">
        SELECT c.*,sd.shop_name,sd.shop_logo FROM tz_coupon c JOIN tz_shop_detail sd
                                                                   ON sd.shop_id = c.shop_id
                                                              LEFT JOIN tz_coupon_prod cp ON cp.`coupon_id` = c.`coupon_id`
                                                              LEFT JOIN tz_prod p ON cp.`prod_id` = p.`prod_id` AND p.`status` = 1
        WHERE c.overdue_status = 1 AND c.puton_status = 1 AND c.is_score_type = 0 AND c.shop_id != 0

        GROUP BY c.`coupon_id` HAVING IF (c.`suitable_prod_type` = 1, COUNT(p.`prod_id`) &gt; 0, 1=1)
        order by c.coupon_id desc
    </select>

</mapper>
