<?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.xxfc.platform.activity.mapper.UserCouponMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.xxfc.platform.activity.vo.UserCouponVo" id="userCouponMap">
        <result property="couponId" column="coupon_id"/>
        <result property="userId" column="user_id"/>
        <result property="tickerNo" column="ticker_no"/>
        <result property="isUse" column="is_use"/>
        <result property="orderNo" column="order_no"/>
        <result property="crtTime" column="crt_time"/>
        <result property="startTime" column="start_time"/>
        <result property="expireTime" column="expire_time"/>
        <result property="expireDate" column="expireDate"/>
        <result property="useTime" column="use_time"/>
        <result property="title" column="title"/>
        <result property="subtitle" column="subtitle"/>
        <result property="icon" column="icon"/>
        <result property="used" column="used"/>
        <result property="channel" column="channel"/>
        <result property="userType" column="user_type"/>
        <result property="type" column="type"/>
        <result property="withSpecial" column="with_special"/>
        <result property="withSn" column="with_sn"/>
        <result property="withAmount" column="with_amount"/>
        <result property="usedAmount" column="used_amount"/>
        <result property="status" column="status"/>
        <result property="url" column="url"/>
    </resultMap>


    <select id="getUserCouponsByType" resultMap="userCouponMap">
        SELECT
        *,IF(expire_time=-1,'永久有效',CONCAT('有效期至',FROM_UNIXTIME(expire_time/1000,'%Y-%m-%d'))) as expireDate
        FROM
        user_coupon u
        LEFT JOIN coupon c ON u.coupon_id = c.id
        WHERE
        u.is_del = 0 and u.user_id=#{userId}
        <if test="type==0">
            and u.is_use=0 and (u.expire_time >=#{time} or u.expire_time=-1)
        </if>
        <if test="type==1">
            and u.is_use=1
        </if>
        <if test="type==2">
            and u.is_use=0 and u.expire_time &lt;#{time} and u.expire_time!=-1
        </if>
        <if test="channel!=null and channel!=''">
            and (c.channel=#{channel} or c.channel=0 )
        </if>
        <choose>
            <when test="type==1">
                ORDER BY u.use_time DESC
            </when>
            <otherwise>
                ORDER BY c.used_amount DESC
            </otherwise>

        </choose>

    </select>

    <select id="getUserCoupon" resultMap="userCouponMap">
        SELECT
        *
        FROM
        user_coupon u
        LEFT JOIN coupon c ON u.coupon_id = c.id
        WHERE
        u.is_del = 0  and u.ticker_no=#{tickerNo} limit 1
    </select>


    <select id="findUserCoupons" resultType="com.xxfc.platform.activity.dto.UserCouponDTO"
            parameterType="com.xxfc.platform.activity.dto.UserCouponFindDTO">
        SELECT
        uc.id,
        uc.user_id AS `userId`,
        uc.is_use AS `isUse`,
        cp.title,
        cp.used_amount AS `usedAmount`,
        cp.valid_type AS `validType`,
        uc.crt_time AS `crtTime`,
        uc.expire_time AS `expireTime`,
        uc.use_time AS `useTime`
        FROM
        ( SELECT `id`, `coupon_id`, `user_id`, `is_use`, `crt_time`, `expire_time`, `use_time` FROM `user_coupon` WHERE
        `is_del` = 0
        <if test="userId != null">
           AND  `user_id`=#{userId}
        </if>
        <if test="status != null">
           AND  `is_use`=#{status}
        </if>) AS `uc`
        INNER JOIN ( SELECT `id`, `title`, `used_amount`,`valid_type` FROM `coupon` WHERE 1=1
        <if test="couponId != null">
            AND `id`=#{couponId}
        </if>
        <if test="usedAmount != null">
            AND `used_amount`=#{usedAmount}
        </if>
        ) AS `cp` ON uc.coupon_id = cp.id  order by `crtTime` DESC
    </select>

    <insert id="inserBatch">
        INSERT INTO `user_coupon`(`user_id`,`coupon_id`,`ticker_no`,`start_time`,`expire_time`,`crt_time`) VALUES
       <foreach collection="userCoupons" item="userCoupon" separator=",">
           (#{userCoupon.userId},#{userCoupon.couponId},#{userCoupon.tickerNo} ,#{userCoupon.startTime},#{userCoupon.expireTime},#{userCoupon.crtTime} )
       </foreach>
    </insert>

    <update id="updateUserCouponStatusById">
        update `user_coupon` set `is_del`=1 where  `id`=#{id}
    </update>
</mapper>