<?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.tmerclub.cloud.coupon.mapper.CouponMapper">
  <resultMap id="couponMap" type="com.tmerclub.cloud.coupon.model.Coupon">
    <id column="coupon_id" property="couponId" />
    <result column="create_time" property="createTime"/>
    <result column="update_time" property="updateTime"/>
    <result column="shop_id" property="shopId"/>
    <result column="coupon_name" property="couponName"/>
    <result column="sub_title" property="subTitle"/>
    <result column="coupon_type" property="couponType"/>
    <result column="suitable_prod_type" property="suitableProdType"/>
    <result column="get_way" property="getWay"/>
    <result column="valid_time_type" property="validTimeType"/>
    <result column="cash_condition" property="cashCondition"/>
    <result column="reduce_amount" property="reduceAmount"/>
    <result column="coupon_discount" property="couponDiscount"/>
    <result column="launch_time" property="launchTime"/>
    <result column="start_time" property="startTime"/>
    <result column="end_time" property="endTime"/>
    <result column="after_receive_days" property="afterReceiveDays"/>
    <result column="valid_days" property="validDays"/>
    <result column="total_stock" property="totalStock"/>
    <result column="stocks" property="stocks"/>
    <result column="limit_num" property="limitNum"/>
    <result column="status" property="status"/>
    <result column="puton_status" property="putonStatus"/>
    <result column="version" property="version"/>
  </resultMap>

  <resultMap id="couponVOMap" type="com.tmerclub.cloud.coupon.vo.CouponVO">
    <id column="coupon_id" property="couponId" />
    <result column="shop_id" property="shopId"/>
    <result column="coupon_name" property="couponName"/>
    <result column="sub_title" property="subTitle"/>
    <result column="coupon_type" property="couponType"/>
    <result column="suitable_prod_type" property="suitableProdType"/>
    <result column="get_way" property="getWay"/>
    <result column="valid_time_type" property="validTimeType"/>
    <result column="cash_condition" property="cashCondition"/>
    <result column="reduce_amount" property="reduceAmount"/>
    <result column="coupon_discount" property="couponDiscount"/>
    <result column="launch_time" property="launchTime"/>
    <result column="start_time" property="startTime"/>
    <result column="end_time" property="endTime"/>
    <result column="after_receive_days" property="afterReceiveDays"/>
    <result column="valid_days" property="validDays"/>
    <result column="total_stock" property="totalStock"/>
    <result column="stocks" property="stocks"/>
    <result column="limit_num" property="limitNum"/>
    <result column="status" property="status"/>
    <result column="puton_status" property="putonStatus"/>
    <collection property="spuIds" ofType="java.lang.Long">
      <id column="spu_id"/>
    </collection>
  </resultMap>
  <resultMap id="couponAppVOMap" extends="couponMap" type="com.tmerclub.cloud.coupon.vo.CouponAppVO">
    <id column="coupon_id" property="couponId" />
    <result column="shop_id" property="shopId"/>
    <result column="coupon_name" property="couponName"/>
    <result column="sub_title" property="subTitle"/>
    <result column="coupon_type" property="couponType"/>
    <result column="suitable_prod_type" property="suitableProdType"/>
    <result column="get_way" property="getWay"/>
    <result column="valid_time_type" property="validTimeType"/>
    <result column="cash_condition" property="cashCondition"/>
    <result column="reduce_amount" property="reduceAmount"/>
    <result column="coupon_discount" property="couponDiscount"/>
    <result column="start_time" property="startTime"/>
    <result column="end_time" property="endTime"/>
    <result column="after_receive_days" property="afterReceiveDays"/>
    <result column="valid_days" property="validDays"/>
    <result column="total_stock" property="totalStock"/>
    <result column="stocks" property="stocks"/>
    <result column="limit_num" property="limitNum"/>
    <result column="status" property="status"/>
    <result column="puton_status" property="putonStatus"/>
    <result column="user_has_count" property="hasReceive"/>
    <collection property="spuIds" ofType="java.lang.Long">
      <id column="spu_id"/>
    </collection>
  </resultMap>
  <resultMap id="couponOrderVOMap" type="com.tmerclub.cloud.common.order.vo.CouponOrderVO">
    <result column="coupon_id" property="couponId"/>
    <result column="shop_id" property="shopId"/>
    <result column="coupon_name" property="couponName"/>
    <result column="cash_condition" property="cashCondition"/>
    <result column="reduce_amount" property="reduceAmount"/>
    <result column="coupon_discount" property="couponDiscount"/>
    <result column="start_time" property="startTime"/>
    <result column="end_time" property="endTime"/>
    <result column="suitable_prod_type" property="suitableProdType"/>
    <result column="can_use" property="canUse"/>
    <result column="is_choose" property="isChoose"/>
    <result column="coupon_type" property="couponType"/>
    <result column="coupon_user_id" property="couponUserId"/>
    <result column="order_id" property="orderId"/>
    <collection property="spuIds" ofType="java.lang.Long">
      <id column="spu_id"/>
    </collection>
  </resultMap>
  <sql id="Vo_Column_List">
    `coupon_id`,`create_time`,`update_time`,`shop_id`,`coupon_name`,`sub_title`,`coupon_type`,`suitable_prod_type`,`get_way`,`valid_time_type`,`cash_condition`,`reduce_amount`,`coupon_discount`,`launch_time`,`start_time`,`end_time`,`after_receive_days`,`valid_days`,`total_stock`,`stocks`,`limit_num`,`status`,`puton_status`,`version`
  </sql>
  <select id="list" resultType="com.tmerclub.cloud.coupon.vo.CouponVO">
    select <include refid="Vo_Column_List"/> from coupon
    where status <![CDATA[<>]]> -1
    <if test="coupon.shopId != null">
      and shop_id = #{coupon.shopId}
    </if>
    <if test="coupon.couponName != null and coupon.couponName != ''">
      and coupon_name like concat('%',#{coupon.couponName},'%')
    </if>
    <if test="coupon.status != null">
      and status = #{coupon.status}
    </if>
    <if test="coupon.putonStatus != null">
      and puton_status = #{coupon.putonStatus}
    </if>
    <if test="coupon.startTime != null">
      and start_time &gt;= #{coupon.startTime}
    </if>
    <if test="coupon.endTime != null">
      and end_time &lt;= #{coupon.endTime}
    </if>
    <if test="coupon.couponType != null">
      and coupon_type = #{coupon.couponType}
    </if>
    <if test="coupon.suitableProdType != null">
      and suitable_prod_type = #{coupon.suitableProdType}
    </if>
    <if test="coupon.getWay != null">
      and get_way = #{coupon.getWay}
    </if>
    order by update_time desc
  </select>
  <select id="adminList" resultType="com.tmerclub.cloud.coupon.vo.CouponVO">
    select <include refid="Vo_Column_List"/> from coupon
    where status <![CDATA[<>]]> -1
    <if test="coupon.shopIds != null and coupon.shopIds.size()>0">
      and shop_id in
      <foreach collection="coupon.shopIds" open="(" index="item" item="item" separator="," close=")">
        #{item}
      </foreach>
    </if>
    <if test="coupon.shopIds != null and coupon.shopIds.size()==0">
      and shop_id is NULL
    </if>
    <if test="coupon.shopId != null">
      and shop_id = #{coupon.shopId}
    </if>
    <if test="coupon.shopId == null">
      and shop_id &gt; 0
    </if>

    <if test="coupon.couponName != null and coupon.couponName != ''">
      and coupon_name like concat('%',#{coupon.couponName},'%')
    </if>
    <if test="coupon.status != null">
      and status = #{coupon.status}
    </if>
    <if test="coupon.putonStatus != null">
      and puton_status = #{coupon.putonStatus}
    </if>
    <if test="coupon.startTime != null">
      and start_time &gt;= #{coupon.startTime}
    </if>
    <if test="coupon.endTime != null">
      and end_time &lt;= #{coupon.endTime}
    </if>
    <if test="coupon.couponType != null">
      and coupon_type = #{coupon.couponType}
    </if>
    <if test="coupon.suitableProdType != null">
      and suitable_prod_type = #{coupon.suitableProdType}
    </if>
    order by update_time desc
  </select>

  <select id="getAvailableShopCouponList" resultMap="couponAppVOMap">
    SELECT
    c.coupon_id,c.shop_id,c.coupon_name,c.sub_title,c.coupon_type,c.suitable_prod_type,c.cash_condition,c.reduce_amount,c.coupon_discount,
    c.limit_num,c.valid_time_type,c.start_time,c.end_time,c.after_receive_days,c.valid_days,c.stocks,c.total_stock,cs.spu_id
    FROM
    coupon c
    LEFT JOIN coupon_spu cs ON c.coupon_id = cs.coupon_id
    WHERE
    c.status = 1
    AND get_way = 0
    AND c.puton_status =1
    AND c.shop_id = #{shopId}
    AND c.stocks != 0
    ORDER BY
    c.coupon_type,
    c.cash_condition,
    c.coupon_discount
  </select>

  <select id="getByCouponId" resultType="com.tmerclub.cloud.coupon.model.Coupon">
    select <include refid="Vo_Column_List"/> from coupon where coupon_id = #{couponId}
  </select>
  <insert id="save" useGeneratedKeys="true" keyProperty="couponId">
    insert into coupon (`shop_id`,`coupon_name`,`sub_title`,`coupon_type`,`suitable_prod_type`,`get_way`,`valid_time_type`,`cash_condition`,`reduce_amount`,`coupon_discount`,`launch_time`,`start_time`,`end_time`,`after_receive_days`,`valid_days`,`total_stock`,`stocks`,`limit_num`,`puton_status`)
    values (#{coupon.shopId},#{coupon.couponName},#{coupon.subTitle},#{coupon.couponType},#{coupon.suitableProdType},#{coupon.getWay},#{coupon.validTimeType},#{coupon.cashCondition},#{coupon.reduceAmount},#{coupon.couponDiscount},#{coupon.launchTime},#{coupon.startTime},#{coupon.endTime},#{coupon.afterReceiveDays},#{coupon.validDays},#{coupon.totalStock},#{coupon.stocks},#{coupon.limitNum},#{coupon.putonStatus});
  </insert>
  <update id="update">
    update coupon
    <set>
      <if test="coupon.couponName != null">
        `coupon_name` = #{coupon.couponName},
      </if>
      <if test="coupon.subTitle != null">
        `sub_title` = #{coupon.subTitle},
      </if>
      <if test="coupon.couponType != null">
        `coupon_type` = #{coupon.couponType},
      </if>
      <if test="coupon.suitableProdType != null">
        `suitable_prod_type` = #{coupon.suitableProdType},
      </if>
      <if test="coupon.getWay != null">
        `get_way` = #{coupon.getWay},
      </if>
      <if test="coupon.validTimeType != null">
        `valid_time_type` = #{coupon.validTimeType},
      </if>
      <if test="coupon.cashCondition != null">
        `cash_condition` = #{coupon.cashCondition},
      </if>
      <if test="coupon.reduceAmount != null">
        `reduce_amount` = #{coupon.reduceAmount},
      </if>
      <if test="coupon.couponDiscount != null">
        `coupon_discount` = #{coupon.couponDiscount},
      </if>
      `launch_time` = #{coupon.launchTime},
      <if test="coupon.startTime != null">
        `start_time` = #{coupon.startTime},
      </if>
      <if test="coupon.endTime != null">
        `end_time` = #{coupon.endTime},
      </if>
      <if test="coupon.afterReceiveDays != null">
        `after_receive_days` = #{coupon.afterReceiveDays},
      </if>
      <if test="coupon.validDays != null">
        `valid_days` = #{coupon.validDays},
      </if>
      <if test="coupon.limitNum != null">
        `limit_num` = #{coupon.limitNum},
      </if>
      <if test="coupon.status != null">
        `status` = #{coupon.status},
      </if>
      <if test="coupon.putonStatus != null">
        `puton_status` = #{coupon.putonStatus},
      </if>
      <if test="coupon.version != null">
        `version` = #{coupon.version} + 1,
      </if>
    </set>
    where coupon_id = #{coupon.couponId}
    <if test="coupon.version != null">
      and `version` = #{coupon.version}
    </if>
  </update>
  <update id="updateCoupon">
    update coupon
    <set>
      <if test="coupon.couponName != null">
        `coupon_name` = #{coupon.couponName},
      </if>
      <if test="coupon.subTitle != null">
        `sub_title` = #{coupon.subTitle},
      </if>
      <if test="coupon.couponType != null">
        `coupon_type` = #{coupon.couponType},
      </if>
      <if test="coupon.suitableProdType != null">
        `suitable_prod_type` = #{coupon.suitableProdType},
      </if>
      <if test="coupon.getWay != null">
        `get_way` = #{coupon.getWay},
      </if>
      <if test="coupon.validTimeType != null">
        `valid_time_type` = #{coupon.validTimeType},
      </if>
      <if test="coupon.cashCondition != null">
        `cash_condition` = #{coupon.cashCondition},
      </if>
      <if test="coupon.reduceAmount != null">
        `reduce_amount` = #{coupon.reduceAmount},
      </if>
      <if test="coupon.couponDiscount != null">
        `coupon_discount` = #{coupon.couponDiscount},
      </if>
      <if test="coupon.launchTime != null">
      `launch_time` = #{coupon.launchTime},
      </if>
      <if test="coupon.startTime != null">
        `start_time` = #{coupon.startTime},
      </if>
      <if test="coupon.endTime != null">
        `end_time` = #{coupon.endTime},
      </if>
      <if test="coupon.afterReceiveDays != null">
        `after_receive_days` = #{coupon.afterReceiveDays},
      </if>
      <if test="coupon.validDays != null">
        `valid_days` = #{coupon.validDays},
      </if>
      <if test="coupon.limitNum != null">
        `limit_num` = #{coupon.limitNum},
      </if>
      <if test="coupon.status != null">
        `status` = #{coupon.status},
      </if>
      <if test="coupon.putonStatus != null">
        `puton_status` = #{coupon.putonStatus},
      </if>
      <if test="coupon.version != null">
        `version` = #{coupon.version} + 1,
      </if>
    </set>
    where coupon_id = #{coupon.couponId}
  </update>

  <select id="getCouponAndCouponProdsByCouponId" resultMap="couponVOMap">
    SELECT c.*,cs.spu_id FROM coupon c
    LEFT JOIN coupon_spu cs ON c.coupon_id = cs.coupon_id
    WHERE c.coupon_id = #{couponId}
  </select>

  <select id="getListByCouponIds" resultMap="couponMap">
    SELECT * FROM coupon WHERE coupon_id IN
    <foreach collection="couponIds" item="couponId" open="(" close=")" separator=",">
      #{couponId}
    </foreach>
  </select>

  <select id="generalCouponList" resultType="com.tmerclub.cloud.coupon.vo.CouponAppVO">
    SELECT c.*
    FROM coupon c
    WHERE c.`get_way` != 1 and shop_id = 0 and c.puton_status = 1 and c.status = 1
    ORDER BY c.coupon_id DESC
  </select>

  <select id="getCouponListByCouponIds" resultMap="couponMap">
    select <include refid="Vo_Column_List"/> from coupon where coupon_id in
    <foreach collection="couponIds" item="couponId" open="(" close=")" separator=",">
      #{couponId}
    </foreach>
    <if test="putOnStatus != null">
      and puton_status = #{putOnStatus}
    </if>
  </select>

  <select id="getCouponListByUserIdAndShopId" resultMap="couponOrderVOMap">
    select c.coupon_id,c.shop_id,c.coupon_name,c.cash_condition,c.reduce_amount,c.coupon_discount,c.suitable_prod_type,
        c.coupon_type,cs.spu_id
    from coupon c
    left join coupon_spu cs on cs.coupon_id = c.coupon_id
    where c.coupon_id in
    <foreach collection="couponIds" item="couponId" open="(" close=")" separator=",">
      #{couponId}
    </foreach>
  </select>
  <select id="getProdCouponList" resultMap="couponAppVOMap">
    SELECT c.*,cs.spu_id
    FROM
    (
        SELECT coupon_id,shop_id,coupon_name,sub_title,coupon_type,suitable_prod_type,valid_time_type,total_stock
             ,cash_condition,reduce_amount,coupon_discount,start_time,end_time,after_receive_days,valid_days,stocks,limit_num
        FROM coupon
        WHERE `get_way` != 1 and `status` = 1 AND puton_status = 1 AND shop_id != 0
        ORDER BY coupon_id DESC
        LIMIT #{page.begin}, #{page.size}
    ) c
    LEFT JOIN coupon_spu cs ON cs.`coupon_id` = c.`coupon_id` AND c.suitable_prod_type = 1
  </select>

  <select id="getProdCouponListCount" resultType="java.lang.Long">
    SELECT COUNT(coupon_id) as num FROM coupon WHERE `get_way` != 1 AND `status` = 1 AND puton_status = 1 AND shop_id != 0
  </select>
  <update id="changeCouponStatusAndPutOnStatus">
    UPDATE coupon  SET puton_status = #{putOnStatus}
    <if test="status != null ">
      ,`status` = #{status}
    </if>
    WHERE coupon_id = #{couponId}
  </update>

  <select id="listOverdueStatusShopIds" resultType="java.lang.Long">
    SELECT shop_id FROM coupon
    WHERE end_time &lt;= now()
    and valid_time_type = 1
    GROUP BY shop_id
  </select>

  <update id="cancelPut">
    update coupon set status = 0, puton_status = -1 where `status` != -1 and end_time &lt;= now() and valid_time_type = 1
  </update>

  <update id="putonCoupon">
    update coupon set puton_status = 1 where launch_time &lt;= now() and status = 1 and stocks &gt; 0 and puton_status = 0
  </update>

  <select id="listCouponBySpuIds" resultMap="couponVOMap">
    SELECT c.coupon_id,c.shop_id,c.suitable_prod_type,cs.spu_id,c.puton_status
    FROM coupon c
    LEFT JOIN coupon_spu cs ON c.coupon_id = cs.coupon_id
    WHERE
    (
      c.suitable_prod_type = 0 OR
          c.coupon_id in
        (
          SELECT coupon_id FROM coupon_spu where spu_id IN
          <foreach collection="spuIds" item="spuId" open="(" close=")" separator=",">
            #{spuId}
          </foreach>
        )
    ) AND c.puton_status != -1
    <if test="shopIds != null and shopIds.size() != 0">
      AND c.shop_id IN
      <foreach collection="shopIds" item="shopId" open="(" close=")" separator=",">
        #{shopId}
      </foreach>
    </if>
  </select>

  <update id="batchOfflineByDiscountIdsAndStatus">
    update coupon set puton_status = 4 where coupon_id in
    <foreach collection="couponIds" item="couponId" open="(" close=")" separator=",">
      #{couponId}
    </foreach>
  </update>

  <select id="cancelPutCouponIds" resultType="com.tmerclub.cloud.coupon.model.Coupon">
    select coupon_id, shop_id from coupon where `status` = 1 and puton_status = 1 and end_time &lt;= now() and valid_time_type = 1
  </select>

  <select id="listIdByShopIds" resultType="java.lang.Long">
    SELECT coupon_id FROM coupon
    WHERE `status` = 1 AND puton_status = 1 AND shop_id IN
    <foreach collection="shopIds" item="shopId" open="(" close=")" separator=",">
      #{shopId}
    </foreach>
  </select>
  <update id="batchOfflineByCouponIds">
    UPDATE coupon SET `status` = 0, puton_status = -1
    WHERE coupon_id IN
    <foreach collection="couponIds" item="couponId" open="(" close=")" separator=",">
      #{couponId}
    </foreach>
  </update>
  <update id="updateStockById">
    UPDATE coupon
        SET stocks = stocks - #{changeStock}
    WHERE coupon_id = #{couponId}
        AND stocks >= #{changeStock}
  </update>
  <select id="listMaybeNeedStockPlacementActivity" resultType="java.lang.Long">
    SELECT coupon_id
    FROM `coupon`
    WHERE `status` = 1 AND stocks > 0 AND puton_status = 1
  </select>
</mapper>
