<?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.discount.comment.dao.DiscountMapper">
    <resultMap id="BaseResultMap" type="com.yami.shop.discount.comment.model.Discount">
        <!--
          WARNING - @mbg.generated
        -->
        <id column="discount_id" jdbcType="BIGINT" property="discountId"/>
        <result column="discount_name" jdbcType="VARCHAR" property="discountName"/>
        <result column="discount_rule" jdbcType="TINYINT" property="discountRule"/>
        <result column="discount_type" jdbcType="TINYINT" property="discountType"/>
        <result column="suitable_prod_type" jdbcType="TINYINT" property="suitableProdType"/>
        <result column="max_reduce_amount" jdbcType="DECIMAL" property="maxReduceAmount"/>
        <result column="start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="status" jdbcType="TINYINT" property="status"/>
    </resultMap>

    <resultMap id="DiscountAndItemAndProdMap" type="com.yami.shop.discount.comment.model.Discount">
        <id column="discount_id" jdbcType="BIGINT" property="discountId"/>
        <result column="shop_id" jdbcType="BIGINT" property="shopId"/>
        <result column="discount_name" jdbcType="VARCHAR" property="discountName"/>
        <result column="mobile_pic" jdbcType="VARCHAR" property="mobilePic"/>
        <result column="pc_pic" jdbcType="VARCHAR" property="pcPic"/>
        <result column="pc_background_pic" jdbcType="VARCHAR" property="pcBackgroundPic"/>
        <result column="discount_rule" jdbcType="TINYINT" property="discountRule"/>
        <result column="discount_type" jdbcType="TINYINT" property="discountType"/>
        <result column="suitable_prod_type" jdbcType="TINYINT" property="suitableProdType"/>
        <result column="max_reduce_amount" jdbcType="DECIMAL" property="maxReduceAmount"/>
        <result column="start_time" jdbcType="TIMESTAMP" property="startTime"/>
        <result column="end_time" jdbcType="TIMESTAMP" property="endTime"/>
        <result column="status" jdbcType="TINYINT" property="status"/>
        <collection property="discountItems" ofType="com.yami.shop.discount.comment.model.DiscountItem">
            <result column="discount_item_id" jdbcType="BIGINT" property="discountItemId"/>
            <result column="discount_id" jdbcType="BIGINT" property="discountId"/>
            <result column="need_amount" jdbcType="DECIMAL" property="needAmount"/>
            <result column="discount" jdbcType="DECIMAL" property="discount"/>
        </collection>
        <collection property="discountProds" javaType="list" ofType="com.yami.shop.discount.comment.model.DiscountProd">
            <result column="discount_prod_id" jdbcType="BIGINT" property="discountProdId"/>
            <result column="prod_id" jdbcType="BIGINT" property="prodId"/>
            <result column="imgs" jdbcType="VARCHAR" property="imgs"/>
            <result column="pic" jdbcType="VARCHAR" property="pic"/>
            <result column="prod_name" jdbcType="VARCHAR" property="prodName"/>
        </collection>
    </resultMap>


    <select id="getDiscountAndItemAndProdById" resultMap="DiscountAndItemAndProdMap">
    select d.*,di.*,dp.*,tp.imgs,tp.pic,tp.prod_name from tz_discount d
    left join tz_discount_item di on d.discount_id = di.discount_id
    left join tz_discount_prod dp on d.discount_id = dp.discount_id
    left join tz_prod tp on dp.prod_id = tp.prod_id
    where d.discount_id = #{discountId}
    ORDER BY di.need_amount ASC
  </select>

    <delete id="deleteDiscounts">
        delete from tz_discount where discount_id = #{id} and shop_id = #{shopId}
    </delete>

    <select id="getDiscountsAndItemsByShopId" resultMap="DiscountAndItemAndProdMap">
        SELECT d.*,dp.*,di.* FROM tz_discount d
        LEFT JOIN tz_discount_item di ON d.discount_id = di.discount_id
        LEFT JOIN tz_discount_prod dp ON d.discount_id = dp.discount_id
        WHERE d.shop_id = #{shopId} and d.status = 1 and d.start_time &lt; NOW() and d.end_time &gt; NOW()
        ORDER BY d.start_time, di.need_amount DESC
  </select>

    <select id="listByProdId" resultType="com.yami.shop.bean.app.dto.DiscountDto">
        SELECT d.*
        FROM tz_discount d
        LEFT JOIN tz_discount_prod dp ON d.discount_id = dp.discount_id AND d.suitable_prod_type
        WHERE d.status = 1 AND d.shop_id = #{shopId}  AND d.start_time &lt; NOW() AND d.end_time &gt; NOW()
        AND (d.suitable_prod_type = 0 OR (dp.prod_id = #{prodId} AND d.suitable_prod_type = 1) OR (dp.prod_id != #{prodId} AND d.suitable_prod_type = 2))
        ORDER BY d.start_time
    </select>
    <select id="discountProdList" resultType="com.yami.shop.bean.app.dto.ProductDto">
        SELECT DISTINCT p.prod_id,
        p.pic,
        p.prod_name,
        p.price,
        p.brief,
        p.sold_num,
        p.create_time
        FROM tz_prod p, tz_discount d, tz_discount_prod dp
        WHERE  p.`status` = 1 AND p.prod_type = 0 AND d.`status` = 1 AND d.`start_time` &lt;= NOW() AND d.`end_time` &gt; NOW()
        <if test="discountId != null">
            AND d.discount_id = #{discountId}
        </if>
        AND (
            (d.suitable_prod_type = 0 AND p.`shop_id` = d.shop_id)
            OR
            (d.suitable_prod_type = 1 AND d.`discount_id` = dp.`discount_id` AND p.`prod_id` = dp.`prod_id`)
            OR
            (d.suitable_prod_type = 2 AND d.`discount_id` = dp.`discount_id` AND p.`shop_id` = d.shop_id AND p.`prod_id` != dp.`prod_id`)
        )
        ORDER BY p.sold_num DESC, p.create_time DESC
    </select>

    <select id="getPlatformDiscountPage" resultType="com.yami.shop.discount.comment.model.Discount">
        SELECT d.*,sd.`shop_name` FROM tz_discount d
        LEFT JOIN tz_shop_detail sd ON sd.`shop_id` = d.`shop_id`
        <where>
            <if test="discount.shopName != null">
                sd.shop_name LIKE concat("%",#{discount.shopName} ,"%")
            </if>
            <if test="discount.status != null">
                AND d.status = #{discount.status}
            </if>
            <if test="discount.discountName != null">
                AND d.discount_name LiKE concat("%",#{discount.discountName},"%")
            </if>
        </where>
        ORDER BY d.`start_time` DESC
    </select>
    <update id="updateStatusByDiscountId">
        UPDATE tz_discount d SET d.`status` = #{status} WHERE d.`discount_id` = #{discountId}
    </update>
    <select id="getDiscountList" resultType="com.yami.shop.bean.app.dto.DiscountDto">
        SELECT d.discount_id,d.discount_name,d.end_time,d.start_time,d.mobile_pic,d.pc_pic,d.pc_background_pic,s.shop_name,s.shop_logo
        FROM tz_discount d
        JOIN tz_shop_detail s ON s.shop_id = d.shop_id
        WHERE d.`status` = 1 AND d.start_time &lt; NOW() AND d.end_time &gt; NOW()
        ORDER BY d.end_time
    </select>
    <select id="getDiscountByDiscountId" resultType="com.yami.shop.bean.app.dto.DiscountDto">
        SELECT d.discount_id,d.discount_name,d.end_time,d.start_time,d.mobile_pic,d.pc_pic,d.pc_background_pic,s.shop_name,s.shop_logo
        FROM tz_discount d
        JOIN tz_shop_detail s ON s.shop_id = d.shop_id
        WHERE d.`status` = 1 AND d.discount_id = #{discountId} AND d.start_time &lt; NOW() AND d.end_time &gt; NOW()
    </select>
    <select id="countProdIsSeckillAll" resultType="java.lang.Integer" parameterType="java.lang.Long">
        SELECT COUNT(1) FROM `tz_discount` dt
        WHERE dt.suitable_prod_type = 0 AND dt.status = 1 AND dt.shop_id = #{shopId}
    </select>
    <select id="countProdIsSeckillSome" resultType="java.lang.Integer" parameterType="java.lang.Long">
      SELECT COUNT(1) FROM `tz_discount` dt
      LEFT JOIN `tz_discount_prod` dp ON dp.discount_id = dt.discount_id
      WHERE dt.suitable_prod_type = 1 AND dt.status = 1
      AND dt.shop_id = #{shopId}
      AND dp.prod_id = #{prodId}
    </select>
    <select id="countProdIsSeckillExcept" resultType="java.lang.Integer">
      SELECT COUNT(1) FROM `tz_discount` dt
      LEFT JOIN `tz_discount_prod` dp ON dp.discount_id = dt.discount_id
      WHERE dt.suitable_prod_type = 2 AND dt.status = 1
      AND dt.shop_id = #{shopId}
      AND dp.prod_id = #{prodId}
    </select>

    <select id="getProdIsSeckillExcept" resultType="com.yami.shop.discount.comment.model.DiscountProd"
            parameterType="java.lang.Long">
      SELECT dp.* FROM `tz_discount` dt
      LEFT JOIN `tz_discount_prod` dp ON dp.discount_id = dt.discount_id
      WHERE dt.suitable_prod_type = 2 AND dt.status = 1
      AND dt.shop_id = #{shopId}
    </select>

    <update id="closeDiscountBySetStatus">
        UPDATE tz_discount SET `status` = 0
        WHERE `status` = 1 AND end_time &lt; NOW()
    </update>

    <select id="pageProdByDiscount" resultType="com.yami.shop.bean.model.Product">
        select p.* from tz_prod p
        WHERE p.`status` = 1 and p.shop_id = #{discount.shopId} and  p.STATUS = 1 AND p.prod_type = 0
        <if test="discount.shopId != 0">
            AND p.shop_id = #{discount.shopId}
        </if>
        <if test="product.prodName != null">
            AND p.prod_name LIKE concat('%',#{product.prodName},'%')
        </if>
        <if test="product.shopCategoryId != null">
            AND p.shop_category_id = #{product.shopCategoryId}
        </if>
        AND p.`prod_id`
        <if test="discount.suitableProdType == 1">
            IN
        </if>
        <if test="discount.suitableProdType == 2">
            NOT IN
        </if>
        (SELECT
        dp.`prod_id`
        FROM
        tz_discount_prod dp
        WHERE
        dp.`discount_id` = #{discount.discountId})
        ORDER BY p.`sold_num` DESC,p.`update_time` DESC
    </select>
</mapper>
