<?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.diaoyun.zion.chinafrica.dao.TbCfCouponDao">

    <resultMap type="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity" id="tbCfCouponMap">
        <result property="couponId" column="coupon_id"/>
        <result property="couponCategoryId" column="coupon_category_id"/>
        <result property="couponUse" column="coupon_use"/>
        <result property="couponTitle" column="coupon_title"/>
        <result property="couponIcon" column="coupon_icon"/>
        <result property="withStationId" column="with_station_id"/>
        <result property="withAmount" column="with_amount"/>
        <result property="deductAmount" column="deduct_amount"/>
        <result property="quato" column="quato"/>
        <result property="takeCount" column="take_count"/>
        <result property="usedCount" column="used_count"/>
        <result property="startTime" column="start_time"/>
        <result property="endTime" column="end_time"/>
        <result property="validStartTime" column="valid_start_time"/>
        <result property="validEndTime" column="valid_end_time"/>
        <result property="status" column="status"/>
        <result property="createUserId" column="create_user_id"/>
        <result property="createTime" column="create_time"/>
        <result property="updateUserId" column="update_user_id"/>
        <result property="updateTime" column="update_time"/>
        <result property="couponVaild" column="coupon_vaild"/>
    </resultMap>

    <select id="queryObject" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
		select
			`coupon_id`,
			`coupon_category_id`,
			`coupon_use`,
			`coupon_title`,
			`coupon_icon`,
			`with_station_id`,
			`with_amount`,
			`deduct_amount`,
			`quato`,
			`take_count`,
			`used_count`,
			`start_time`,
			`end_time`,
			`valid_start_time`,
			`valid_end_time`,
			`status`,
			`create_user_id`,
			`create_time`,
			`update_user_id`,
			`update_time`,
			`coupon_vaild`
		from tb_cf_coupon a
		where coupon_id = #{id}
	</select>

    <select id="queryList" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
        select
        `coupon_id`,
        `coupon_category_id`,
        `coupon_use`,
        `coupon_title`,
        `coupon_icon`,
        `with_station_id`,
        `with_amount`,
        `deduct_amount`,
        `quato`,
        `take_count`,
        `used_count`,
        `start_time`,
        `end_time`,
        `valid_start_time`,
        `valid_end_time`,
        `status`,
        `create_user_id`,
        `create_time`,
        `update_user_id`,
        `update_time`,
        `coupon_vaild`
        from tb_cf_coupon a
        WHERE 1=1
        <if test="name != null and name.trim() != ''">
            AND name LIKE concat('%',#{name},'%')
        </if>
        <choose>
            <when test="sidx != null and sidx.trim() != ''">
                order by ${sidx} ${order}
            </when>
            <otherwise>
                order by coupon_id desc
            </otherwise>
        </choose>
        <if test="offset != null and limit != null">
            limit #{offset}, #{limit}
        </if>
    </select>

    <select id="queryTotal" resultType="int">
        select count(*) from tb_cf_coupon
        WHERE 1=1
        <if test="name != null and name.trim() != ''">
            AND name LIKE concat('%',#{name},'%')
        </if>
    </select>

    <insert id="save" parameterType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
		insert into tb_cf_coupon(
			`coupon_id`,
			`coupon_category_id`,
			`coupon_use`,
			`coupon_title`,
			`coupon_icon`,
			`with_station_id`,
			`with_amount`,
			`deduct_amount`,
			`quato`,
			`take_count`,
			`used_count`,
			`start_time`,
			`end_time`,
			`valid_start_time`,
			`valid_end_time`,
			`status`,
			`create_user_id`,
			`create_time`,
			`update_user_id`,
			`update_time`,
			`coupon_vaild`)
		values(
			#{couponId},
			#{couponCategoryId},
			#{couponUse},
			#{couponTitle},
			#{couponIcon},
			#{withStationId},
			#{withAmount},
			#{deductAmount},
			#{quato},
			#{takeCount},
			#{usedCount},
			#{startTime},
			#{endTime},
			#{validStartTime},
			#{validEndTime},
			#{status},
			#{createUserId},
			#{createTime},
			#{updateUserId},
			#{updateTime}),
			#{coupon_vaild})
	</insert>
    <insert id="saves" parameterType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
		insert into tb_cf_coupon(
			`coupon_id`,
			`coupon_category_id`,
			`coupon_use`,
			`coupon_title`,
			`coupon_icon`,
			`with_station_id`,
			`with_amount`,
			`deduct_amount`,
			`quato`,
			`take_count`,
			`used_count`,
			`start_time`,
			`end_time`,
			`valid_start_time`,
			`valid_end_time`,
			`status`,
			`create_user_id`,
			`create_time`,
			`update_user_id`,
			`update_time`,
			`coupon_vaild`)
		values(
			#{couponId},
			#{couponCategoryId},
			#{couponUse},
			#{couponTitle},
			#{couponIcon},
			#{withStationId},
			#{withAmount},
			#{deductAmount},
			#{quato},
			#{takeCount},
			#{usedCount},
			#{startTime},
			#{endTime},
			#{validStartTime},
			#{validEndTime},
			#{status},
			#{createUserId},
			#{createTime},
			#{updateUserId},
			#{updateTime}),
			#{coupon_vaild})
	</insert>

    <update id="update" parameterType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
        update tb_cf_coupon
        <set>
            <if test="couponCategoryId != null">`coupon_category_id` = #{couponCategoryId},</if>
            <if test="couponUse != null">`coupon_use` = #{couponUse},</if>
            <if test="couponTitle != null">`coupon_title` = #{couponTitle},</if>
            <if test="couponIcon != null">`coupon_icon` = #{couponIcon},</if>
            <if test="withStationId != null">`with_station_id` = #{withStationId},</if>
            <if test="withAmount != null">`with_amount` = #{withAmount},</if>
            <if test="deductAmount != null">`deduct_amount` = #{deductAmount},</if>
            <if test="quato != null">`quato` = #{quato},</if>
            <if test="takeCount != null">`take_count` = #{takeCount},</if>
            <if test="usedCount != null">`used_count` = #{usedCount},</if>
            <if test="startTime != null">`start_time` = #{startTime},</if>
            <if test="endTime != null">`end_time` = #{endTime},</if>
            <if test="validStartTime != null">`valid_start_time` = #{validStartTime},</if>
            <if test="validEndTime != null">`valid_end_time` = #{validEndTime},</if>
            <if test="status != null">`status` = #{status},</if>
            <if test="createUserId != null">`create_user_id` = #{createUserId},</if>
            <if test="createTime != null">`create_time` = #{createTime},</if>
            <if test="updateUserId != null">`update_user_id` = #{updateUserId},</if>
            <if test="updateTime != null">`update_time` = #{updateTime}</if>
            <if test="couponVaild != null">`coupon_vaild` = #{couponVaild}</if>
        </set>
        where coupon_id = #{couponId}

    </update>
    <update id="updates" parameterType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
        update tb_cf_coupon
        <set>
            <if test="couponCategoryId != null">`coupon_category_id` = #{couponCategoryId},</if>
            <if test="couponUse != null">`coupon_use` = #{couponUse},</if>
            <if test="couponTitle != null">`coupon_title` = #{couponTitle},</if>
            <if test="couponIcon != null">`coupon_icon` = #{couponIcon},</if>
            <if test="withStationId != null">`with_station_id` = #{withStationId},</if>
            <if test="withAmount != null">`with_amount` = #{withAmount},</if>
            <if test="deductAmount != null">`deduct_amount` = #{deductAmount},</if>
            <if test="quato != null">`quato` = #{quato},</if>
            <if test="takeCount != null">`take_count` = #{takeCount},</if>
            <if test="usedCount != null">`used_count` = #{usedCount},</if>
            <if test="startTime != null">`start_time` = #{startTime},</if>
            <if test="endTime != null">`end_time` = #{endTime},</if>
            <if test="validStartTime != null">`valid_start_time` = #{validStartTime},</if>
            <if test="validEndTime != null">`valid_end_time` = #{validEndTime},</if>
            <if test="status != null">`status` = #{status},</if>
            <if test="createUserId != null">`create_user_id` = #{createUserId},</if>
            <if test="createTime != null">`create_time` = #{createTime},</if>
            <if test="updateUserId != null">`update_user_id` = #{updateUserId},</if>
            <if test="updateTime != null">`update_time` = #{updateTime}</if>
            <if test="couponVaild != null">`coupon_vaild` = #{couponVaild}</if>
        </set>
        where coupon_id = #{couponId}
    </update>
    <delete id="delete">
		delete from tb_cf_coupon where coupon_id = #{value}
	</delete>
    <select id="getCouponId" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
		select distinct  coupon_id from tb_cf_issue_coupon where  coupon_id='1087f4a682354843b145d36c27d2d90e'
	</select>
    <delete id="deleteBatch">
        delete from tb_cf_coupon where coupon_id in
        <foreach item="couponId" collection="array" open="(" separator="," close=")">
            #{couponId}
        </foreach>
    </delete>

    <!--查询用户所有有效的优惠券-->
    <select id="queryUserAvailableCoupon" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
       select
        t3.toitable_id ,
        t2.coupon_id,
		t2.coupon_category_id,
		t2.coupon_use,
		t2.coupon_title,
		t2.coupon_icon,
		t2.with_station_id,
		t2.with_amount,
		t2.deduct_amount,
		t2.quato,
		t2.take_count,
		t2.used_count,
		t2.start_time,
		t2.end_time,
		t3.start_time valid_start_time,
		t3.end_time valid_end_time,
		t2.status,
		t2.create_user_id,
		t2.create_time,
		t2.update_user_id,
		t2.update_time,
		t2.coupon_vaild
        from tb_cf_coupon t2 left join tb_cf_toicoupon t3 on t3.coupon_id=t2.coupon_id
		where  t3.user_id=#{userId} and t3.enable_flag=1 and t2.status=1
		and (<![CDATA[ t3.start_time<=#{nowTime}]]>  and <![CDATA[t3.end_time>=#{nowTime}]]>) ORDER BY deduct_amount desc
    </select>


    <!--更改优惠券统计-->
    <update id="updateUsedCount">
		update tb_cf_coupon set used_count=used_count+1 where coupon_id=#{couponId}
	</update>

    <!--获取已使用的优惠券-->
    <select id="queryUserUsedCoupon" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
		 select
        t3.toitable_id ,
        t2.coupon_id,
		t2.coupon_category_id,
		t2.coupon_use,
		t2.coupon_title,
		t2.coupon_icon,
		t2.with_station_id,
		t2.with_amount,
		t2.deduct_amount,
		t2.quato,
		t2.take_count,
		t2.used_count,
		t2.start_time,
		t2.end_time,
		t3.start_time valid_start_time,
		t3.end_time valid_end_time,
		t2.status,
		t2.create_user_id,
		t2.create_time,
		t2.update_user_id,
		t2.update_time,
		t2.coupon_vaild
        from tb_cf_coupon t2 left join tb_cf_toicoupon t3 on t3.coupon_id=t2.coupon_id
		where  t3.user_id=#{userId} and t3.enable_flag=0  ORDER BY deduct_amount desc
	</select>


    <!--获取已过期的优惠券-->
    <select id="queryUserExpiredoupon" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
	 select
        t3.toitable_id ,
        t2.coupon_id,
		t2.coupon_category_id,
		t2.coupon_use,
		t2.coupon_title,
		t2.coupon_icon,
		t2.with_station_id,
		t2.with_amount,
		t2.deduct_amount,
		t2.quato,
		t2.take_count,
		t2.used_count,
		t2.start_time,
		t2.end_time,
		t3.start_time valid_start_time,
		t3.end_time valid_end_time,
		t2.status,
		t2.create_user_id,
		t2.create_time,
		t2.update_user_id,
		t2.update_time,
		t2.coupon_vaild
        from tb_cf_coupon t2 left join tb_cf_toicoupon t3 on t3.coupon_id=t2.coupon_id
		where t3.user_id=#{userId} and  <![CDATA[ t3.end_time<#{nowTime}]]> and t3.enable_flag=1 ORDER BY deduct_amount desc
	</select>

    <select id="getCouponAndVaildAll" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
		select
		 	`coupon_id`,
			`coupon_category_id`,
			`coupon_use`,
			`coupon_title`,
			`coupon_icon`,
			`with_station_id`,
			`with_amount`,
			`deduct_amount`,
			`quato`,
			`take_count`,
			`used_count`,
			`start_time`,
			`end_time`,
			`valid_start_time`,
			`valid_end_time`,
			`status`,
			`create_user_id`,
			`create_time`,
			`update_user_id`,
			`update_time`,
			`coupon_vaild`
			from tb_cf_coupon
			where coupon_vaild = 1
	</select>
    <!--根据优惠券种类获取发放中的优惠券-->
    <select id="getCouponByCategory" resultType="com.diaoyun.zion.chinafrica.entity.TbCfCouponEntity">
		select * from tb_cf_coupon where coupon_category_id=#{couponCategoryId} and
		<![CDATA[ start_time<=#{nowTime}]]>  and <![CDATA[end_time>=#{nowTime}]]> and status=1;
	</select>

</mapper>