<?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">
<!-- 命名，每一个映射对象不一样
	namespace:必须与对应的接口全类名一致
 -->
<mapper namespace="com.iamberry.wechat.service.mapper.CouponTypeMapper">
 
	<sql id="allField">
 		COUPON_ID couponId,
		COUPON_NAME couponName,
		COUPON_TOTAL couponTotal,
		COUPON_USED_NUM couponUsedNum,
		COUPON_REMAINING_NUM couponRemainingNum,
		COUPON_GET_LIMIT couponGetLimit,
		COUPON_TYPE couponType,
		COUPON_IS_CONCERN couponIsConcern,
		COUPON_IS_PURCHASE couponIsPurchase,
		COUPON_CONSUME_ENOUGH couponConsumeEnough,
		COUPON_REDUCE couponReduce,
		COUPON_GET_DAYS couponGetDays,
		COUPON_LIMIT_HOURS couponLimitHours,
		COUPON_END_DATE couponEndDate,
		COUPON_CREATE_DATE couponCreateDate,
		COUPON_UPDATE_DATE couponUpdateDate,
		COUPON_IS_GIVE couponIsGive,
		COUPON_PRIORITY_LEVELS couponPriorityLevels,
		COUPON_REMARK couponRemark,
		COUPON_IS_SOURCE couponIsSource,
		COUPON_SOURCE_NAME couponSourceName
	</sql>
 
 	<!-- 查询优惠券类型列表 分页 -->
 	<select id="getCouponTypeList" resultType="CouponType" parameterType="CouponType">
 		SELECT 
 			<include refid="allField"></include>
 		FROM TB_IAMBERRY_COUPON_TYPE
 		<where>
 			<if test="couponName !=null and couponName != '' ">
				COUPON_NAME LIKE CONCAT('%',#{couponName},'%')
		    </if>
		    <if test="couponType !=null and couponType != '' ">
				AND COUPON_TYPE = #{couponType}
		    </if>
			<if test="couponIsNewPeople !=null ">
				AND coupon_is_new_people = #{couponIsNewPeople}
			</if>
 		</where>
		ORDER BY COUPON_CREATE_DATE DESC
		<if test="page!=null and page.recordBegin>0 and page.pageSize>0 ">
	     LIMIT ${page.recordBegin},${page.pageSize}
	    </if>
	     <if test="page!=null and page.recordBegin==0 and page.pageSize>0 ">
	     LIMIT ${page.pageSize}
	    </if>
 	</select>
 	<!-- 查询优惠券类型总数 -->
 	<select id="getCouponTypeCount" resultType="Integer">
 		SELECT COUNT(COUPON_ID) FROM TB_IAMBERRY_COUPON_TYPE
 	</select>
 
 	<!-- 添加一种优惠券类型 -->
 	<insert id="insertCouponType" parameterType="CouponType"
 		useGeneratedKeys="true" keyProperty="couponId">
 		INSERT INTO TB_IAMBERRY_COUPON_TYPE
		(
			COUPON_NAME ,
			COUPON_TOTAL ,
			COUPON_USED_NUM ,
			COUPON_REMAINING_NUM ,
			COUPON_GET_LIMIT ,
	
			COUPON_TYPE ,
			COUPON_IS_CONCERN ,
			COUPON_IS_PURCHASE ,
			COUPON_CONSUME_ENOUGH ,
			COUPON_REDUCE ,
	
			COUPON_GET_DAYS ,
			COUPON_LIMIT_HOURS ,
			COUPON_END_DATE ,
			COUPON_CREATE_DATE ,
			COUPON_UPDATE_DATE ,
	
			COUPON_IS_GIVE ,
			COUPON_PRIORITY_LEVELS ,
			COUPON_REMARK ,
			COUPON_IS_SOURCE,
			COUPON_SOURCE_NAME
		)
		VALUES 
		(
			#{couponName},#{couponTotal},#{couponUsedNum},#{couponRemainingNum},#{couponGetLimit},
			#{couponType},#{couponIsConcern},#{couponIsPurchase},#{couponConsumeEnough},#{couponReduce},
			#{couponGetDays},#{couponLimitHours},#{couponEndDate},#{couponCreateDate},#{couponUpdateDate},
			#{couponIsGive},#{couponPriorityLevels},#{couponRemark},#{couponIsSource},#{couponSourceName}
		)
 	</insert>
 	
 	<!-- 根据id查询单个优惠券类型 -->
 	<select id="getCouponTypeById" resultType="CouponType" parameterType="Integer">
 		SELECT 
 			<include refid="allField"></include>
 		FROM TB_IAMBERRY_COUPON_TYPE
 		WHERE COUPON_ID=#{couponId}
 	</select>
 	
 	<!-- 获取未领取的优惠券 -->
 	<select id="getUnGetCouponType" resultType="CouponType" parameterType="String">
 	select 
 	<include refid="allField"></include>
 	from tb_iamberry_coupon_type 
	where coupon_id not in ( select coupon_id from tb_iamberry_coupon_item 
	where coupon_item_user_openid=#{openid} )
	and coupon_end_date > now()
 	</select>
 	
 	<update id="updateCouponTypeForItem" parameterType="Integer">
		UPDATE TB_IAMBERRY_COUPON_TYPE
		SET COUPON_USED_NUM=COUPON_USED_NUM+1,
		COUPON_REMAINING_NUM=COUPON_REMAINING_NUM-1
		WHERE COUPON_ID=#{couponId}
 	</update>
 	
 	<update id="updateCouponTypeById" parameterType="CouponType">
 		UPDATE TB_IAMBERRY_COUPON_TYPE
 		<set>
 			<if test="couponName !=null and couponName !=''">
	 			COUPON_NAME=#{couponName},
 			</if>
 			<if test="couponTotal !=null and couponTotal !=''">
	 			COUPON_TOTAL=#{couponTotal} ,
 			</if>
 			<if test="couponUsedNum !=null and couponUsedNum !=''">
	 			COUPON_USED_NUM=#{couponUsedNum} ,
 			</if>
 			<if test="couponRemainingNum !=null and couponRemainingNum !=''">
	 			COUPON_REMAINING_NUM=#{couponRemainingNum} ,
 			</if>
 			<if test="couponGetLimit !=null and couponGetLimit !=''">
	 			COUPON_GET_LIMIT=#{couponGetLimit} ,
 			</if>
 			<if test="couponType !=null and couponType !=''">
	 			COUPON_TYPE=#{couponType} ,
 			</if>
 			<if test="couponIsConcern !=null and couponIsConcern !=''">
	 			COUPON_IS_CONCERN=#{couponIsConcern} ,
 			</if>
 			<if test="couponIsPurchase !=null and couponIsPurchase !=''">
	 			COUPON_IS_PURCHASE=#{couponIsPurchase} ,
 			</if>
 			<if test="couponConsumeEnough !=null and couponConsumeEnough !=''">
	 			COUPON_CONSUME_ENOUGH=#{couponConsumeEnough} ,
 			</if>
 			<if test="couponReduce !=null and couponReduce !=''">
	 			COUPON_REDUCE=#{couponReduce} ,
 			</if>
 			<if test="couponGetDays !=null and couponGetDays !=''">
	 			COUPON_GET_DAYS=#{couponGetDays} ,
 			</if>
 			<if test="couponLimitHours !=null and couponLimitHours !=''">
	 			COUPON_LIMIT_HOURS=#{couponLimitHours} ,
 			</if>
 			<if test="couponEndDate !=null and couponEndDate !=''">
	 			COUPON_END_DATE=#{couponEndDate} ,
 			</if>
 			<if test="couponCreateDate !=null and couponCreateDate !=''">
	 			COUPON_CREATE_DATE=#{couponCreateDate} ,
 			</if>
 			<if test="couponIsGive !=null and couponIsGive !=''">
	 			COUPON_IS_GIVE=#{couponIsGive} ,
 			</if>
 			<if test="couponPriorityLevels !=null and couponPriorityLevels !=''">
	 			COUPON_PRIORITY_LEVELS=#{couponPriorityLevels} ,
 			</if>
 			<if test="couponRemark !=null and couponRemark !=''">
	 			COUPON_REMARK =#{couponRemark},
 			</if>
			<if test="couponIsSource != null">
				COUPON_IS_SOURCE = #{couponIsSource},
				COUPON_SOURCE_NAME = #{couponSourceName},
			</if>
 			COUPON_UPDATE_DATE=NOW()
 		</set>
 		WHERE COUPON_ID=#{couponId}
 	</update>
 	
 	<delete id="deleteCouponTypeById" parameterType="Integer">
 		DELETE FROM TB_IAMBERRY_COUPON_TYPE 
 		WHERE COUPON_ID=#{couponId}
 	</delete>
</mapper>