<?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.cyl.manager.marketing.mapper.WechatMemberCouponMapper">
    
    <resultMap type="WechatMemberCoupon" id="WechatMemberCouponResult">
        <result property="id" column="id"/>
        <result property="stockId" column="stock_id"/>
        <result property="stockName" column="stock_name"/>
        <result property="stockType" column="stock_type"/>
        <result property="couponCode" column="coupon_code"/>
        <result property="availableStartTime" column="available_start_time"/>
        <result property="expireTime" column="expire_time"/>
        <result property="receiveTime" column="receive_time"/>
        <result property="sendRequestNo" column="send_request_no"/>
        <result property="useRequestNo" column="use_request_no"/>
        <result property="useTime" column="use_time"/>
        <result property="couponState" column="coupon_state"/>
        <result property="openid" column="openid"/>
        <result property="phone" column="phone"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>

        <result property="memberId" column="member_id"/>

        <result property="discountType" column="discount_type"/>
    </resultMap>

    <resultMap type="com.cyl.h5.pojo.response.WechatMemberCouponResponse" id="WechatMemberCouponResponseResult">
        <result property="id" column="id"/>
        <result property="stockId" column="stock_id"/>
        <result property="stockName" column="stock_name"/>
        <result property="stockType" column="stock_type"/>
        <result property="couponCode" column="coupon_code"/>
        <result property="availableStartTime" column="available_start_time"/>
        <result property="expireTime" column="expire_time"/>
        <result property="receiveTime" column="receive_time"/>
        <result property="sendRequestNo" column="send_request_no"/>
        <result property="useRequestNo" column="use_request_no"/>
        <result property="useTime" column="use_time"/>
        <result property="couponState" column="coupon_state"/>
        <result property="openid" column="openid"/>
        <result property="phone" column="phone"/>
        <result property="createBy" column="create_by"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="updateBy" column="update_by"/>

        <result property="memberId" column="member_id"/>

        <result property="discountType" column="discount_type"/>

        <result property="discountAmount" column="discount_amount"/>
        <result property="transactionMinimum" column="transaction_minimum"/>
        <result property="discountPercent" column="discount_percent"/>
    </resultMap>

    <sql id="selectWechatMemberCouponVo">
        select member_id, id, stock_id, stock_name, stock_type, coupon_code, available_start_time, expire_time, receive_time, send_request_no, use_request_no, use_time, coupon_state, openid, phone, create_by, create_time, update_time, update_by, discount_type from wechat_member_coupon
    </sql>

    <select id="selectList2" parameterType="WechatMemberCoupon" resultMap="WechatMemberCouponResult">
        <include refid="selectWechatMemberCouponVo"/>
        <where>  
            <if test="stockId != null "> and stock_id = #{stockId}</if>
            <if test="stockName != null  and stockName != ''"> and stock_name like concat('%', #{stockName}, '%')</if>
            <if test="stockType != null  and stockType != ''"> and stock_type = #{stockType}</if>
            <if test="couponCode != null  and couponCode != ''"> and coupon_code = #{couponCode}</if>
            <if test="availableStartTime != null "> and available_start_time = #{availableStartTime}</if>
            <if test="expireTime != null "> and expire_time = #{expireTime}</if>
            <if test="receiveTime != null "> and receive_time = #{receiveTime}</if>
            <if test="sendRequestNo != null  and sendRequestNo != ''"> and send_request_no = #{sendRequestNo}</if>
            <if test="useRequestNo != null  and useRequestNo != ''"> and use_request_no = #{useRequestNo}</if>
            <if test="useTime != null "> and use_time = #{useTime}</if>
            <if test="couponState != null  and couponState != ''"> and coupon_state = #{couponState}</if>
            <if test="memberId != null  and memberId != ''"> and member_id = #{memberId}</if>
            <if test="phone != null  and phone != ''"> and phone = #{phone}</if>

            <if test="discountType != null  and discountType != ''"> and discount_type = #{discountType}</if>
        </where>
    </select>

    <select id="selectListForOrder" parameterType="Long" resultMap="WechatMemberCouponResponseResult">
        SELECT
        wmc.member_id,
        wmc.id,
        wmc.stock_id,
        wmc.stock_name,
        wmc.stock_type,
        wmc.coupon_code,
        wmc.available_start_time,
        wmc.expire_time,
        wmc.receive_time,
        wmc.send_request_no,
        wmc.use_request_no,
        wmc.use_time,
        wmc.coupon_state,
        wmc.openid,
        wmc.phone,
        wmc.create_by,
        wmc.create_time,
        wmc.update_time,
        wmc.update_by,
        wmc.discount_type,
        ws.discount_amount,
        ws.transaction_minimum,
        ws.discount_percent
        FROM
        wechat_member_coupon wmc
        LEFT JOIN wechat_stock ws on ws.stock_id = wmc.stock_id
        WHERE wmc.member_id = #{id} and coupon_state = 'SENDED' order by wmc.create_time desc
    </select>
    
    <select id="selectById" parameterType="Integer" resultMap="WechatMemberCouponResult">
        <include refid="selectWechatMemberCouponVo"/>
        where id = #{id}
    </select>
    <select id="selectMemberCouponById" resultType="com.cyl.h5.pojo.response.WechatMemberCouponResponse">

        SELECT
            wmc.member_id,
            wmc.id,
            wmc.stock_id,
            wmc.stock_name,
            wmc.stock_type,
            wmc.coupon_code,
            wmc.available_start_time,
            wmc.expire_time,
            wmc.receive_time,
            wmc.send_request_no,
            wmc.use_request_no,
            wmc.use_time,
            wmc.coupon_state,
            wmc.openid,
            wmc.phone,
            wmc.create_by,
            wmc.create_time,
            wmc.update_time,
            wmc.update_by,
            wmc.discount_type,
            ws.discount_amount,
            ws.transaction_minimum,
            ws.discount_percent
        FROM
            wechat_member_coupon wmc
                LEFT JOIN wechat_stock ws on ws.stock_id = wmc.stock_id
        WHERE wmc.id = #{id} and coupon_state = 'SENDED' order by wmc.create_time desc

    </select>

    <insert id="insert" parameterType="WechatMemberCoupon" useGeneratedKeys="true" keyProperty="id">
        insert into wechat_member_coupon
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">id,</if>
            <if test="stockId != null">stock_id,</if>
            <if test="stockName != null and stockName != ''">stock_name,</if>
            <if test="stockType != null and stockType != ''">stock_type,</if>
            <if test="couponCode != null and couponCode != ''">coupon_code,</if>
            <if test="availableStartTime != null">available_start_time,</if>
            <if test="expireTime != null">expire_time,</if>
            <if test="receiveTime != null">receive_time,</if>
            <if test="sendRequestNo != null">send_request_no,</if>
            <if test="useRequestNo != null">use_request_no,</if>
            <if test="useTime != null">use_time,</if>
            <if test="couponState != null and couponState != ''">coupon_state,</if>
            <if test="openid != null and openid != ''">openid,</if>
            <if test="phone != null and phone != ''">phone,</if>
            <if test="createBy != null">create_by,</if>
            <if test="createTime != null">create_time,</if>
            <if test="updateTime != null">update_time,</if>
            <if test="updateBy != null">update_by,</if>

            <if test="memberId != null">member_id,</if>

            <if test="discountType != null and discountType != ''">discount_type,</if>
         </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">#{id},</if>
            <if test="stockId != null">#{stockId},</if>
            <if test="stockName != null and stockName != ''">#{stockName},</if>
            <if test="stockType != null and stockType != ''">#{stockType},</if>
            <if test="couponCode != null and couponCode != ''">#{couponCode},</if>
            <if test="availableStartTime != null">#{availableStartTime},</if>
            <if test="expireTime != null">#{expireTime},</if>
            <if test="receiveTime != null">#{receiveTime},</if>
            <if test="sendRequestNo != null">#{sendRequestNo},</if>
            <if test="useRequestNo != null">#{useRequestNo},</if>
            <if test="useTime != null">#{useTime},</if>
            <if test="couponState != null and couponState != ''">#{couponState},</if>
            <if test="openid != null and openid != ''">#{openid},</if>
            <if test="phone != null and phone != ''">#{phone},</if>
            <if test="createBy != null">#{createBy},</if>
            <if test="createTime != null">#{createTime},</if>
            <if test="updateTime != null">#{updateTime},</if>
            <if test="updateBy != null">#{updateBy},</if>

            <if test="memberId != null">#{memberId},</if>

            <if test="discountType != null and discountType != ''">#{discountType},</if>
         </trim>
    </insert>

    <update id="updateWechatMemberCoupon" parameterType="WechatMemberCoupon">
        update wechat_member_coupon
        <trim prefix="SET" suffixOverrides=",">
            <if test="stockId != null">stock_id = #{stockId},</if>
            <if test="stockName != null and stockName != ''">stock_name = #{stockName},</if>
            <if test="stockType != null and stockType != ''">stock_type = #{stockType},</if>
            <if test="couponCode != null and couponCode != ''">coupon_code = #{couponCode},</if>
            <if test="availableStartTime != null">available_start_time = #{availableStartTime},</if>
            <if test="expireTime != null">expire_time = #{expireTime},</if>
            <if test="receiveTime != null">receive_time = #{receiveTime},</if>
            <if test="sendRequestNo != null">send_request_no = #{sendRequestNo},</if>
            <if test="useRequestNo != null">use_request_no = #{useRequestNo},</if>
            <if test="useTime != null">use_time = #{useTime},</if>
            <if test="couponState != null and couponState != ''">coupon_state = #{couponState},</if>
            <if test="openid != null and openid != ''">openid = #{openid},</if>
            <if test="phone != null and phone != ''">phone = #{phone},</if>
            <if test="createBy != null">create_by = #{createBy},</if>
            <if test="createTime != null">create_time = #{createTime},</if>
            <if test="updateTime != null">update_time = #{updateTime},</if>
            <if test="updateBy != null">update_by = #{updateBy},</if>

            <if test="memberId != null">member_id = #{memberId},</if>

            <if test="discountType != null and discountType != ''">discount_type = #{discountType},</if>
        </trim>
        where id = #{id}
    </update>

    <delete id="deleteById" parameterType="Integer">
        delete from wechat_member_coupon where id = #{id}
    </delete>

    <update id="updateCouponState" parameterType="String">
        update wechat_member_coupon set coupon_state = &quot;EXPIRED&quot;, update_time = #{time} where expire_time &lt; #{time} and coupon_state = &quot;SENDED&quot;
    </update>

    <delete id="deleteByIds" parameterType="String">
        delete from wechat_member_coupon where id in 
        <foreach item="id" collection="array" open="(" separator="," close=")">
            #{id}
        </foreach>
    </delete>
    <insert id="insertOrUpdate" parameterType="WechatMemberCoupon" useGeneratedKeys="true" keyProperty="id">
        insert into wechat_member_coupon
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            member_id, stock_id, stock_name, stock_type, coupon_code, available_start_time, expire_time, receive_time, send_request_no, use_request_no, use_time, coupon_state, openid, phone, create_by, create_time, update_time, update_by, discount_type
        </trim>
        values
        <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="id != null">
            #{id, jdbcType=INTEGER},
        </if>
            #{stockId, jdbcType=VARCHAR},
            #{stockName, jdbcType=VARCHAR},
            #{stockType, jdbcType=VARCHAR},
            #{couponCode, jdbcType=VARCHAR},
            #{availableStartTime, jdbcType=DATE},
            #{expireTime, jdbcType=DATE},
            #{receiveTime, jdbcType=DATE},
            #{sendRequestNo, jdbcType=VARCHAR},
            #{useRequestNo, jdbcType=VARCHAR},
            #{useTime, jdbcType=DATE},
            #{couponState, jdbcType=VARCHAR},
            #{openid, jdbcType=VARCHAR},
            #{phone, jdbcType=VARCHAR},
            #{createBy, jdbcType=BIGINT},
            #{createTime, jdbcType=DATE},
            #{updateTime, jdbcType=DATE},
            #{updateBy, jdbcType=BIGINT},
            #{memberId, jdbcType=BIGINT},
            #{discountType, jdbcType=VARCHAR}
        </trim>
        on duplicate key update
        <trim suffixOverrides=",">
            <if test="id != null">
                id = #{id,jdbcType=INTEGER},
            </if>
            stock_id = #{stockId, jdbcType=VARCHAR},
            stock_name = #{stockName, jdbcType=VARCHAR},
            stock_type = #{stockType, jdbcType=VARCHAR},
            coupon_code = #{couponCode, jdbcType=VARCHAR},
            available_start_time = #{availableStartTime, jdbcType=DATE},
            expire_time = #{expireTime, jdbcType=DATE},
            receive_time = #{receiveTime, jdbcType=DATE},
            send_request_no = #{sendRequestNo, jdbcType=VARCHAR},
            use_request_no = #{useRequestNo, jdbcType=VARCHAR},
            use_time = #{useTime, jdbcType=DATE},
            coupon_state = #{couponState, jdbcType=VARCHAR},
            openid = #{openid, jdbcType=VARCHAR},
            phone = #{phone, jdbcType=VARCHAR},
            create_by = #{createBy, jdbcType=BIGINT},
            create_time = #{createTime, jdbcType=DATE},
            update_time = #{updateTime, jdbcType=DATE},
            update_by = #{updateBy, jdbcType=BIGINT},
            member_id = #{memberId, jdbcType=BIGINT},
            discount_type = #{discountType, jdbcType=VARCHAR}
        </trim>
    </insert>
</mapper>
