.用户需求
优惠券功能有：
1.后台可以设置优惠券和查看已发出优惠券的状态
2.平台自动给新用户发放优惠劵，或者手动给某些用户发放优惠券
3.用户在小程序中手动领取优惠券
4.用户中心新增“优惠券”模块，查看所有优惠券
5.下单时使用优惠券
2.axure原型设计
使用ie浏览器打开，打开后的html文件目录结构如下：
3.数据库设计
优惠劵2张表的表结构如下：
CREATE TABLE `coupon_rules` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '优惠券主键',
`gmt_create` datetime NOT NULL COMMENT '优惠券创建时间',
`gmt_modified` datetime DEFAULT NULL COMMENT '优惠券修改时间',
`creator` int(11) DEFAULT NULL COMMENT '创建人',
`partner_id` int(11) DEFAULT NULL COMMENT '关联运营商id',
`coupon_name` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '优惠券名称',
`coupon_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '优惠券金额',
`grant_rules` int(2) NOT NULL COMMENT '发放规则',
`use_start_time` datetime DEFAULT NULL COMMENT '使用起始时间',
`use_end_time` datetime DEFAULT NULL COMMENT '使用结束时间',
`quantity_ceiling` int(11) DEFAULT NULL COMMENT '数量上限，一般指按订单金额发放，例如某用户在订单满100元发放5元优惠卷，该用户累计发放次数不能超过10次，累计同一优惠券领取超过该值时则不发放，0为不设限',
`use_conditions` int(2) DEFAULT NULL COMMENT '使用条件：无门槛|满减使用',
`use_conditions_content` decimal(10,2) DEFAULT '0.00' COMMENT '使用条件补充内容',
`grant_conditions` int(11) unsigned DEFAULT NULL COMMENT '发放条件，存类目id',
`order_minimum` decimal(12,4) DEFAULT '0.0000' COMMENT '按照订单金额发放时的最低金额设定',
`grant_start_time` datetime DEFAULT NULL COMMENT '优惠券发放起始时间',
`grant_end_time` datetime DEFAULT NULL COMMENT '优惠券发放结束时间',
`status` int(2) DEFAULT '1' COMMENT '状态，正常1|删除0 ',
`grant_max_num` int(11) DEFAULT '0' COMMENT '优惠券发放上限',
`grant_num` int(11) DEFAULT '0' COMMENT '已发放数量',
`duration` int(11) DEFAULT '30' COMMENT '领取后优惠券的有效天数',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4;
CREATE TABLE `coupon_info` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '优惠券主键',
`rules_id` int(11) NOT NULL COMMENT '关联优惠券规则主键id',
`grant_time` datetime DEFAULT NULL COMMENT '优惠券发放时间',
`status` int(2) NOT NULL DEFAULT '0' COMMENT '优惠券状态，0未使用|1已使用|2已失效',
`member_id` int(11) DEFAULT NULL COMMENT '关联消费者id',
`order_id` int(11) DEFAULT NULL COMMENT '订单id',
`use_time` datetime DEFAULT NULL COMMENT '使用时间',
`coupon_name` varchar(255) CHARACTER SET utf8 NOT NULL COMMENT '优惠券名称',
`coupon_amount` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '优惠券金额',
`use_start_time` datetime DEFAULT NULL COMMENT '使用起始时间',
`use_end_time` datetime DEFAULT NULL COMMENT '使用结束时间',
`grant_conditions` int(11) DEFAULT NULL COMMENT '发放条件，存类目id',
`use_conditions_content` decimal(10,2) DEFAULT NULL COMMENT '使用条件补充内容',
`from_id` int(11) DEFAULT NULL COMMENT '发放来源(存储完工的订单id或注册的商家id等等)',
`from_remark` varchar(255) CHARACTER SET utf8 DEFAULT NULL COMMENT '发放备注',
`gmt_create` datetime NOT NULL COMMENT '优惠券创建时间',
`gmt_modified` datetime DEFAULT NULL COMMENT '优惠券修改时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8mb4;
4.后台优惠劵功能实现
CouponRulesMapper.xml
<?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.**.dao.coupon.CouponRulesMapper">
<resultMap id="BaseResultMap" type="com.**.model.coupon.CouponRules">
<!--@mbg.generated-->
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified"/>
<result column="creator" jdbcType="INTEGER" property="creator"/>
<result column="partner_id" jdbcType="INTEGER" property="partnerId"/>
<result column="coupon_name" jdbcType="VARCHAR" property="couponName"/>
<result column="coupon_amount" jdbcType="DECIMAL" property="couponAmount"/>
<result column="grant_rules" jdbcType="INTEGER" property="grantRules"/>
<result column="use_start_time" jdbcType="TIMESTAMP" property="useStartTime"/>
<result column="use_end_time" jdbcType="TIMESTAMP" property="useEndTime"/>
<result column="quantity_ceiling" jdbcType="INTEGER" property="quantityCeiling"/>
<result column="use_conditions" jdbcType="INTEGER" property="useConditions"/>
<result column="use_conditions_content" jdbcType="DECIMAL" property="useConditionsContent"/>
<result column="grant_conditions" jdbcType="INTEGER" property="grantConditions"/>
<result column="order_minimum" jdbcType="DECIMAL" property="orderMinimum"/>
<result column="grant_start_time" jdbcType="TIMESTAMP" property="grantStartTime"/>
<result column="grant_end_time" jdbcType="TIMESTAMP" property="grantEndTime"/>
<result column="status" jdbcType="INTEGER" property="status"/>
<result column="grant_max_num" jdbcType="INTEGER" property="grantMaxNum"/>
<result column="grant_num" jdbcType="INTEGER" property="grantNum"/>
<result column="duration" jdbcType="INTEGER" property="duration"/>
</resultMap>
<sql id="Base_Column_List">
<!--@mbg.generated-->
id, gmt_create, gmt_modified, creator, partner_id, coupon_name, coupon_amount, grant_rules,
use_start_time, use_end_time, quantity_ceiling, use_conditions, use_conditions_content,
grant_conditions, order_minimum, grant_start_time, grant_end_time, `status`, grant_max_num,
grant_num,duration
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
<!--@mbg.generated-->
select
<include refid="Base_Column_List"/>
from coupon_rules
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
<!--@mbg.generated-->
delete from coupon_rules
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.**.model.coupon.CouponRules"
useGeneratedKeys="true">
<!--@mbg.generated-->
insert into coupon_rules (gmt_create, gmt_modified, creator,
partner_id, coupon_name, coupon_amount,
grant_rules, use_start_time, use_end_time,
quantity_ceiling, use_conditions, use_conditions_content,
grant_conditions, order_minimum, grant_start_time,
grant_end_time, `status`, grant_max_num,
grant_num)
values (#{gmtCreate,jdbcType=TIMESTAMP}, #{gmtModified,jdbcType=TIMESTAMP}, #{creator,jdbcType=INTEGER},
#{partnerId,jdbcType=INTEGER}, #{couponName,jdbcType=VARCHAR}, #{couponAmount,jdbcType=DECIMAL},
#{grantRules,jdbcType=INTEGER}, #{useStartTime,jdbcType=TIMESTAMP}, #{useEndTime,jdbcType=TIMESTAMP},
#{quantityCeiling,jdbcType=INTEGER}, #{useConditions,jdbcType=INTEGER},
#{useConditionsContent,jdbcType=DECIMAL},
#{grantConditions,jdbcType=INTEGER}, #{orderMinimum,jdbcType=DECIMAL}, #{grantStartTime,jdbcType=TIMESTAMP},
#{grantEndTime,jdbcType=TIMESTAMP}, #{status,jdbcType=INTEGER}, #{grantMaxNum,jdbcType=INTEGER},
#{grantNum,jdbcType=INTEGER})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.**.model.coupon.CouponRules"
useGeneratedKeys="true">
<!--@mbg.generated-->
insert into coupon_rules
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="gmtCreate != null">
gmt_create,
</if>
<if test="gmtModified != null">
gmt_modified,
</if>
<if test="creator != null">
creator,
</if>
<if test="partnerId != null">
partner_id,
</if>
<if test="couponName != null">
coupon_name,
</if>
<if test="couponAmount != null">
coupon_amount,
</if>
<if test="grantRules != null">
grant_rules,
</if>
<if test="useStartTime != null">
use_start_time,
</if>
<if test="useEndTime != null">
use_end_time,
</if>
<if test="quantityCeiling != null">
quantity_ceiling,
</if>
<if test="useConditions != null">
use_conditions,
</if>
<if test="useConditionsContent != null">
use_conditions_content,
</if>
<if test="grantConditions != null">
grant_conditions,
</if>
<if test="orderMinimum != null">
order_minimum,
</if>
<if test="grantStartTime != null">
grant_start_time,
</if>
<if test="grantEndTime != null">
grant_end_time,
</if>
<if test="status != null">
`status`,
</if>
<if test="grantMaxNum != null">
grant_max_num,
</if>
<if test="grantNum != null">
grant_num,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="gmtCreate != null">
#{gmtCreate,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
#{gmtModified,jdbcType=TIMESTAMP},
</if>
<if test="creator != null">
#{creator,jdbcType=INTEGER},
</if>
<if test="partnerId != null">
#{partnerId,jdbcType=INTEGER},
</if>
<if test="couponName != null">
#{couponName,jdbcType=VARCHAR},
</if>
<if test="couponAmount != null">
#{couponAmount,jdbcType=DECIMAL},
</if>
<if test="grantRules != null">
#{grantRules,jdbcType=INTEGER},
</if>
<if test="useStartTime != null">
#{useStartTime,jdbcType=TIMESTAMP},
</if>
<if test="useEndTime != null">
#{useEndTime,jdbcType=TIMESTAMP},
</if>
<if test="quantityCeiling != null">
#{quantityCeiling,jdbcType=INTEGER},
</if>
<if test="useConditions != null">
#{useConditions,jdbcType=INTEGER},
</if>
<if test="useConditionsContent != null">
#{useConditionsContent,jdbcType=DECIMAL},
</if>
<if test="grantConditions != null">
#{grantConditions,jdbcType=INTEGER},
</if>
<if test="orderMinimum != null">
#{orderMinimum,jdbcType=DECIMAL},
</if>
<if test="grantStartTime != null">
#{grantStartTime,jdbcType=TIMESTAMP},
</if>
<if test="grantEndTime != null">
#{grantEndTime,jdbcType=TIMESTAMP},
</if>
<if test="status != null">
#{status,jdbcType=INTEGER},
</if>
<if test="grantMaxNum != null">
#{grantMaxNum,jdbcType=INTEGER},
</if>
<if test="grantNum != null">
#{grantNum,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.**.model.coupon.CouponRules">
<!--@mbg.generated-->
update coupon_rules
<set>
<if test="gmtCreate != null">
gmt_create = #{gmtCreate,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
</if>
<if test="creator != null">
creator = #{creator,jdbcType=INTEGER},
</if>
<if test="partnerId != null">
partner_id = #{partnerId,jdbcType=INTEGER},
</if>
<if test="couponName != null">
coupon_name = #{couponName,jdbcType=VARCHAR},
</if>
<if test="couponAmount != null">
coupon_amount = #{couponAmount,jdbcType=DECIMAL},
</if>
<if test="grantRules != null">
grant_rules = #{grantRules,jdbcType=INTEGER},
</if>
<if test="useStartTime != null">
use_start_time = #{useStartTime,jdbcType=TIMESTAMP},
</if>
<if test="useEndTime != null">
use_end_time = #{useEndTime,jdbcType=TIMESTAMP},
</if>
<if test="quantityCeiling != null">
quantity_ceiling = #{quantityCeiling,jdbcType=INTEGER},
</if>
<if test="useConditions != null">
use_conditions = #{useConditions,jdbcType=INTEGER},
</if>
<if test="useConditionsContent != null">
use_conditions_content = #{useConditionsContent,jdbcType=DECIMAL},
</if>
<if test="grantConditions != null">
grant_conditions = #{grantConditions,jdbcType=INTEGER},
</if>
<if test="orderMinimum != null">
order_minimum = #{orderMinimum,jdbcType=DECIMAL},
</if>
<if test="grantStartTime != null">
grant_start_time = #{grantStartTime,jdbcType=TIMESTAMP},
</if>
<if test="grantEndTime != null">
grant_end_time = #{grantEndTime,jdbcType=TIMESTAMP},
</if>
<if test="status != null">
`status` = #{status,jdbcType=INTEGER},
</if>
<if test="grantMaxNum != null">
grant_max_num = #{grantMaxNum,jdbcType=INTEGER},
</if>
<if test="grantNum != null">
grant_num = #{grantNum,jdbcType=INTEGER},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.**.model.coupon.CouponRules">
<!--@mbg.generated-->
update coupon_rules
set gmt_create = #{gmtCreate,jdbcType=TIMESTAMP},
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
creator = #{creator,jdbcType=INTEGER},
partner_id = #{partnerId,jdbcType=INTEGER},
coupon_name = #{couponName,jdbcType=VARCHAR},
coupon_amount = #{couponAmount,jdbcType=DECIMAL},
grant_rules = #{grantRules,jdbcType=INTEGER},
use_start_time = #{useStartTime,jdbcType=TIMESTAMP},
use_end_time = #{useEndTime,jdbcType=TIMESTAMP},
quantity_ceiling = #{quantityCeiling,jdbcType=INTEGER},
use_conditions = #{useConditions,jdbcType=INTEGER},
use_conditions_content = #{useConditionsContent,jdbcType=DECIMAL},
grant_conditions = #{grantConditions,jdbcType=INTEGER},
order_minimum = #{orderMinimum,jdbcType=DECIMAL},
grant_start_time = #{grantStartTime,jdbcType=TIMESTAMP},
grant_end_time = #{grantEndTime,jdbcType=TIMESTAMP},
`status` = #{status,jdbcType=INTEGER},
grant_max_num = #{grantMaxNum,jdbcType=INTEGER},
grant_num = #{grantNum,jdbcType=INTEGER}
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateBatch" parameterType="java.util.List">
<!--@mbg.generated-->
update coupon_rules
<trim prefix="set" suffixOverrides=",">
<trim prefix="gmt_create = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.gmtCreate,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="gmt_modified = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.gmtModified,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="creator = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.creator,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="partner_id = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.partnerId,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="coupon_name = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.couponName,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="coupon_amount = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.couponAmount,jdbcType=DECIMAL}
</foreach>
</trim>
<trim prefix="grant_rules = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantRules,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="use_start_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useStartTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="use_end_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useEndTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="quantity_ceiling = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.quantityCeiling,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="use_conditions = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useConditions,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="use_conditions_content = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useConditionsContent,jdbcType=DECIMAL}
</foreach>
</trim>
<trim prefix="grant_conditions = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantConditions,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="order_minimum = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.orderMinimum,jdbcType=DECIMAL}
</foreach>
</trim>
<trim prefix="grant_start_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantStartTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="grant_end_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantEndTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="`status` = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.status,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="grant_max_num = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantMaxNum,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="grant_num = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantNum,jdbcType=INTEGER}
</foreach>
</trim>
</trim>
where id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.id,jdbcType=INTEGER}
</foreach>
</update>
<insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
<!--@mbg.generated-->
insert into coupon_rules
(gmt_create, gmt_modified, creator, partner_id, coupon_name, coupon_amount, grant_rules,
use_start_time, use_end_time, quantity_ceiling, use_conditions, use_conditions_content,
grant_conditions, order_minimum, grant_start_time, grant_end_time, `status`, grant_max_num,
grant_num)
values
<foreach collection="list" item="item" separator=",">
(#{item.gmtCreate,jdbcType=TIMESTAMP}, #{item.gmtModified,jdbcType=TIMESTAMP},
#{item.creator,jdbcType=INTEGER},
#{item.partnerId,jdbcType=INTEGER}, #{item.couponName,jdbcType=VARCHAR},
#{item.couponAmount,jdbcType=DECIMAL},
#{item.grantRules,jdbcType=INTEGER}, #{item.useStartTime,jdbcType=TIMESTAMP},
#{item.useEndTime,jdbcType=TIMESTAMP},
#{item.quantityCeiling,jdbcType=INTEGER}, #{item.useConditions,jdbcType=INTEGER},
#{item.useConditionsContent,jdbcType=DECIMAL}, #{item.grantConditions,jdbcType=INTEGER},
#{item.orderMinimum,jdbcType=DECIMAL}, #{item.grantStartTime,jdbcType=TIMESTAMP},
#{item.grantEndTime,jdbcType=TIMESTAMP}, #{item.status,jdbcType=INTEGER},
#{item.grantMaxNum,jdbcType=INTEGER},
#{item.grantNum,jdbcType=INTEGER})
</foreach>
</insert>
<resultMap extends="BaseResultMap" id="VoResultMap" type="com.**.model.coupon.vo.CouponRulesVo">
<result column="grantRulesName" jdbcType="VARCHAR" property="grantRulesName"/>
</resultMap>
<sql id="Vo_Column_List">
cr.id, cr.partner_id, cr.coupon_name, cr.coupon_amount, cr.grant_rules, cr.use_start_time, cr.use_end_time,
cr.quantity_ceiling, cr.use_conditions, cr.use_conditions_content, cr.order_minimum, cr.grant_start_time,
cr.grant_end_time,cr.grant_conditions, cr.status, cr.grant_max_num, cr.grant_num ,cr.duration
</sql>
<sql id="Page_Where_Clause">
where 1=1
<trim suffixOverrides=",">
<if test="pd.id != null and pd.id != ''">
and cr.id = #{pd.id}
</if>
<if test="pd.couponName != null and pd.couponName != ''">
and cr.coupon_name LIKE concat('%',#{pd.couponName},'%')
</if>
</trim>
</sql>
<select id="selectlistPage" parameterType="object" resultMap="VoResultMap">
select
<include refid="Vo_Column_List"/>,s.value_name AS grantRulesName
from coupon_rules cr
LEFT JOIN sys_type s ON s.`value` = cr.grant_rules
<include refid="Page_Where_Clause"/>
AND s.type = 'couponGrantRules'
AND cr.status = 1
order by cr.gmt_create desc
</select>
<select id="selectAllcoupons" resultMap="BaseResultMap" parameterType="java.lang.String">
select
<include refid="Base_Column_List"/>
from coupon_rules a
where
grant_rules != 3
and status = 1
and unix_timestamp(grant_end_time)>=unix_timestamp()
and unix_timestamp() >= unix_timestamp(grant_start_time)
<if test="_parameter != null and _parameter != ''">
AND a.id NOT IN (SELECT rules_id FROM coupon_info WHERE member_id=
(SELECT id FROM sys_user_member WHERE open_id=#{_parameter}) );
</if>
</select>
<select id="selectVoById" parameterType="object" resultMap="VoResultMap">
select
<include refid="Vo_Column_List"/>,s.value_name AS grantRulesName
from coupon_rules cr
LEFT JOIN sys_type s ON s.`value` = cr.grant_rules
WHERE
cr.id = #{_parameter}
AND s.type = 'couponGrantRules'
</select>
<select id="selectRegisterCoupon" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from coupon_rules
WHERE
grant_rules = 3
and status = 1
and unix_timestamp(grant_end_time)>=unix_timestamp()
and unix_timestamp() >= unix_timestamp(grant_start_time)
</select>
<!--更新优惠券规则下已发放数量-->
<update id="updateHasGrantCount" parameterType="Object">
UPDATE coupon_rules
SET grant_num=grant_num+1
WHERE
id=#{id}
<!--已发放数量没有超过配置-->
<![CDATA[ AND (grant_max_num=0 OR grant_num<grant_max_num) ]]>
</update>
</mapper>
CouponInfoMapper.xml
<?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.**.dao.coupon.CouponInfoMapper">
<resultMap id="BaseResultMap" type="com.**.model.coupon.CouponInfo">
<!--@mbg.generated-->
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="rules_id" jdbcType="INTEGER" property="rulesId"/>
<result column="grant_time" jdbcType="TIMESTAMP" property="grantTime"/>
<result column="status" jdbcType="INTEGER" property="status"/>
<result column="member_id" jdbcType="INTEGER" property="memberId"/>
<result column="order_id" jdbcType="INTEGER" property="orderId"/>
<result column="use_time" jdbcType="TIMESTAMP" property="useTime"/>
<result column="coupon_name" jdbcType="VARCHAR" property="couponName"/>
<result column="coupon_amount" jdbcType="DECIMAL" property="couponAmount"/>
<result column="use_start_time" jdbcType="TIMESTAMP" property="useStartTime"/>
<result column="use_end_time" jdbcType="TIMESTAMP" property="useEndTime"/>
<result column="grant_conditions" jdbcType="INTEGER" property="grantConditions"/>
<result column="use_conditions_content" jdbcType="DECIMAL" property="useConditionsContent"/>
<result column="from_id" jdbcType="INTEGER" property="fromId"/>
<result column="from_remark" jdbcType="VARCHAR" property="fromRemark"/>
<result column="gmt_create" jdbcType="TIMESTAMP" property="gmtCreate"/>
<result column="gmt_modified" jdbcType="TIMESTAMP" property="gmtModified"/>
</resultMap>
<sql id="Base_Column_List">
<!--@mbg.generated-->
id, rules_id, grant_time, `status`, member_id, order_id, use_time, coupon_name, coupon_amount,
use_start_time, use_end_time, grant_conditions, use_conditions_content, from_id,
from_remark, gmt_create, gmt_modified
</sql>
<select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
<!--@mbg.generated-->
select
<include refid="Base_Column_List"/>
from coupon_info
where id = #{id,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
<!--@mbg.generated-->
delete from coupon_info
where id = #{id,jdbcType=INTEGER}
</delete>
<insert id="insert" keyColumn="id" keyProperty="id" parameterType="com.**.model.coupon.CouponInfo"
useGeneratedKeys="true">
<!--@mbg.generated-->
insert into coupon_info (rules_id, grant_time, `status`,
member_id, order_id, use_time,
coupon_name, coupon_amount, use_start_time,
use_end_time, grant_conditions, use_conditions_content,
from_id, from_remark, gmt_create,
gmt_modified)
values (#{rulesId,jdbcType=INTEGER}, #{grantTime,jdbcType=TIMESTAMP}, #{status,jdbcType=INTEGER},
#{memberId,jdbcType=INTEGER}, #{orderId,jdbcType=INTEGER}, #{useTime,jdbcType=TIMESTAMP},
#{couponName,jdbcType=VARCHAR}, #{couponAmount,jdbcType=DECIMAL}, #{useStartTime,jdbcType=TIMESTAMP},
#{useEndTime,jdbcType=TIMESTAMP}, #{grantConditions,jdbcType=INTEGER}, #{useConditionsContent,jdbcType=DECIMAL},
#{fromId,jdbcType=INTEGER}, #{fromRemark,jdbcType=VARCHAR}, #{gmtCreate,jdbcType=TIMESTAMP},
#{gmtModified,jdbcType=TIMESTAMP})
</insert>
<insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="com.**.model.coupon.CouponInfo"
useGeneratedKeys="true">
<!--@mbg.generated-->
insert into coupon_info
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="rulesId != null">
rules_id,
</if>
<if test="grantTime != null">
grant_time,
</if>
<if test="status != null">
`status`,
</if>
<if test="memberId != null">
member_id,
</if>
<if test="orderId != null">
order_id,
</if>
<if test="useTime != null">
use_time,
</if>
<if test="couponName != null">
coupon_name,
</if>
<if test="couponAmount != null">
coupon_amount,
</if>
<if test="useStartTime != null">
use_start_time,
</if>
<if test="useEndTime != null">
use_end_time,
</if>
<if test="grantConditions != null">
grant_conditions,
</if>
<if test="useConditionsContent != null">
use_conditions_content,
</if>
<if test="fromId != null">
from_id,
</if>
<if test="fromRemark != null">
from_remark,
</if>
<if test="gmtCreate != null">
gmt_create,
</if>
<if test="gmtModified != null">
gmt_modified,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="rulesId != null">
#{rulesId,jdbcType=INTEGER},
</if>
<if test="grantTime != null">
#{grantTime,jdbcType=TIMESTAMP},
</if>
<if test="status != null">
#{status,jdbcType=INTEGER},
</if>
<if test="memberId != null">
#{memberId,jdbcType=INTEGER},
</if>
<if test="orderId != null">
#{orderId,jdbcType=INTEGER},
</if>
<if test="useTime != null">
#{useTime,jdbcType=TIMESTAMP},
</if>
<if test="couponName != null">
#{couponName,jdbcType=VARCHAR},
</if>
<if test="couponAmount != null">
#{couponAmount,jdbcType=DECIMAL},
</if>
<if test="useStartTime != null">
#{useStartTime,jdbcType=TIMESTAMP},
</if>
<if test="useEndTime != null">
#{useEndTime,jdbcType=TIMESTAMP},
</if>
<if test="grantConditions != null">
#{grantConditions,jdbcType=INTEGER},
</if>
<if test="useConditionsContent != null">
#{useConditionsContent,jdbcType=DECIMAL},
</if>
<if test="fromId != null">
#{fromId,jdbcType=INTEGER},
</if>
<if test="fromRemark != null">
#{fromRemark,jdbcType=VARCHAR},
</if>
<if test="gmtCreate != null">
#{gmtCreate,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
#{gmtModified,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.**.model.coupon.CouponInfo">
<!--@mbg.generated-->
update coupon_info
<set>
<if test="rulesId != null">
rules_id = #{rulesId,jdbcType=INTEGER},
</if>
<if test="grantTime != null">
grant_time = #{grantTime,jdbcType=TIMESTAMP},
</if>
<if test="status != null">
`status` = #{status,jdbcType=INTEGER},
</if>
<if test="memberId != null">
member_id = #{memberId,jdbcType=INTEGER},
</if>
<if test="orderId != null">
order_id = #{orderId,jdbcType=INTEGER},
</if>
<if test="useTime != null">
use_time = #{useTime,jdbcType=TIMESTAMP},
</if>
<if test="couponName != null">
coupon_name = #{couponName,jdbcType=VARCHAR},
</if>
<if test="couponAmount != null">
coupon_amount = #{couponAmount,jdbcType=DECIMAL},
</if>
<if test="useStartTime != null">
use_start_time = #{useStartTime,jdbcType=TIMESTAMP},
</if>
<if test="useEndTime != null">
use_end_time = #{useEndTime,jdbcType=TIMESTAMP},
</if>
<if test="grantConditions != null">
grant_conditions = #{grantConditions,jdbcType=INTEGER},
</if>
<if test="useConditionsContent != null">
use_conditions_content = #{useConditionsContent,jdbcType=DECIMAL},
</if>
<if test="fromId != null">
from_id = #{fromId,jdbcType=INTEGER},
</if>
<if test="fromRemark != null">
from_remark = #{fromRemark,jdbcType=VARCHAR},
</if>
<if test="gmtCreate != null">
gmt_create = #{gmtCreate,jdbcType=TIMESTAMP},
</if>
<if test="gmtModified != null">
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP},
</if>
</set>
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.**.model.coupon.CouponInfo">
<!--@mbg.generated-->
update coupon_info
set rules_id = #{rulesId,jdbcType=INTEGER},
grant_time = #{grantTime,jdbcType=TIMESTAMP},
`status` = #{status,jdbcType=INTEGER},
member_id = #{memberId,jdbcType=INTEGER},
order_id = #{orderId,jdbcType=INTEGER},
use_time = #{useTime,jdbcType=TIMESTAMP},
coupon_name = #{couponName,jdbcType=VARCHAR},
coupon_amount = #{couponAmount,jdbcType=DECIMAL},
use_start_time = #{useStartTime,jdbcType=TIMESTAMP},
use_end_time = #{useEndTime,jdbcType=TIMESTAMP},
grant_conditions = #{grantConditions,jdbcType=INTEGER},
use_conditions_content = #{useConditionsContent,jdbcType=DECIMAL},
from_id = #{fromId,jdbcType=INTEGER},
from_remark = #{fromRemark,jdbcType=VARCHAR},
gmt_create = #{gmtCreate,jdbcType=TIMESTAMP},
gmt_modified = #{gmtModified,jdbcType=TIMESTAMP}
where id = #{id,jdbcType=INTEGER}
</update>
<update id="updateBatch" parameterType="java.util.List">
<!--@mbg.generated-->
update coupon_info
<trim prefix="set" suffixOverrides=",">
<trim prefix="rules_id = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.rulesId,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="grant_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="`status` = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.status,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="member_id = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.memberId,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="order_id = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.orderId,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="use_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="coupon_name = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.couponName,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="coupon_amount = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.couponAmount,jdbcType=DECIMAL}
</foreach>
</trim>
<trim prefix="use_start_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useStartTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="use_end_time = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useEndTime,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="grant_conditions = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.grantConditions,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="use_conditions_content = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.useConditionsContent,jdbcType=DECIMAL}
</foreach>
</trim>
<trim prefix="from_id = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.fromId,jdbcType=INTEGER}
</foreach>
</trim>
<trim prefix="from_remark = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.fromRemark,jdbcType=VARCHAR}
</foreach>
</trim>
<trim prefix="gmt_create = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.gmtCreate,jdbcType=TIMESTAMP}
</foreach>
</trim>
<trim prefix="gmt_modified = case" suffix="end,">
<foreach collection="list" index="index" item="item">
when id = #{item.id,jdbcType=INTEGER} then #{item.gmtModified,jdbcType=TIMESTAMP}
</foreach>
</trim>
</trim>
where id in
<foreach close=")" collection="list" item="item" open="(" separator=", ">
#{item.id,jdbcType=INTEGER}
</foreach>
</update>
<insert id="batchInsert" keyColumn="id" keyProperty="id" parameterType="map" useGeneratedKeys="true">
<!--@mbg.generated-->
insert into coupon_info
(rules_id, grant_time, `status`, member_id, order_id, use_time, coupon_name, coupon_amount,
use_start_time, use_end_time, grant_conditions, use_conditions_content, from_id,
from_remark, gmt_create, gmt_modified)
values
<foreach collection="list" item="item" separator=",">
(#{item.rulesId,jdbcType=INTEGER}, #{item.grantTime,jdbcType=TIMESTAMP}, #{item.status,jdbcType=INTEGER},
#{item.memberId,jdbcType=INTEGER}, #{item.orderId,jdbcType=INTEGER}, #{item.useTime,jdbcType=TIMESTAMP},
#{item.couponName,jdbcType=VARCHAR}, #{item.couponAmount,jdbcType=DECIMAL},
#{item.useStartTime,jdbcType=TIMESTAMP},
#{item.useEndTime,jdbcType=TIMESTAMP}, #{item.grantConditions,jdbcType=INTEGER},
#{item.useConditionsContent,jdbcType=DECIMAL}, #{item.fromId,jdbcType=INTEGER},
#{item.fromRemark,jdbcType=VARCHAR}, #{item.gmtCreate,jdbcType=TIMESTAMP},
#{item.gmtModified,jdbcType=TIMESTAMP}
)
</foreach>
</insert>
<select id="findByMemberId" resultType="map">
SELECT coupon_info.id couponId,
coupon_info.grant_time grantTime, coupon_info.`status` status, coupon_info.coupon_name couponName, coupon_info.coupon_amount couponAmount,
coupon_info.use_start_time useStartTime, coupon_info.use_end_time useEndTime, coupon_info.grant_conditions grantConditions,
coupon_info.use_conditions_content useConditionsContent,gc.cat_name catName
FROM coupon_info
LEFT JOIN goods_cat gc on coupon_info.grant_conditions = gc.id
WHERE 1=1
<if test="memberId != null">
AND coupon_info.member_id=#{memberId,jdbcType=INTEGER}
</if>
AND coupon_info.`status` = 0
<![CDATA[
AND DATE_FORMAT(coupon_info.use_start_time,'%Y-%m-%d') <= DATE_FORMAT(now(),'%Y-%m-%d')
AND DATE_FORMAT(coupon_info.use_end_time,'%Y-%m-%d') >= DATE_FORMAT(now(),'%Y-%m-%d')
]]>
</select>
<select id="queryMyCouponList" resultMap="BaseResultMap"
parameterType="object">
select *
from coupon_info ci
<where>
<if test="pd.member_id != null">
and ci.member_id = #{pd.member_id}
</if>
<if test="pd.couponStatus != null ">
and ci.status = #{pd.couponStatus}
</if>
</where>
order by ci.gmt_create desc
</select>
<update id="couponInvalid">
UPDATE coupon_info a SET a.`status`=2,a.`gmt_modified`=NOW() WHERE a.`status`=0 <![CDATA[ AND a.`use_end_time`<NOW() ]]>
</update>
<select id="selectByUserIdAndRulesId" resultMap="BaseResultMap">
select
<include refid="Base_Column_List"/>
from coupon_info
where rules_id = #{param1} and member_id = #{param2}
</select>
</mapper>
……
5.后台优惠劵功能截图
6.微信小程序功能实现
首页展示优惠劵代码
<!-- 优惠券 -->
<view>
<block wx:if="{{coupon != null}}">
<import src="../common/coupon/getcoupon.wxml" />
<template is="getcoupon" data="{{data:coupon}}" />
</block>
</view>
1
2
3
4
5
6
7
getcoupon.wxml内容为：
<template name="getcoupon">
<view class='list-wrapper'>
<view class="coupons-body">
<view wx:for="{{data}}">
<view class="coupon">
<view class="coupon-wrapper">
<view class="coupon-content">
<view class="coupon-top">
<view class="coupon-first">
<text>¥{{item.couponAmount}} </text>
</view>
<view class="couponSecond">
<text style="font-size: 35rpx">商城优惠</text>
</view>
</view>
<view class="coupon-bottom">
<view class="coupon-first">
<text style="color:#fff">满{{item.useConditionsContent}}使用</text>
</view>
<view class="couponSecond">
<text style="color:#fff">领取后{{item.duration}}内有效</text>
</view>
</view>
</view>
<view class="split-line"></view>
<view class="coupon-tip" bindtap="receiveCoupon" data-couponid="{{item.id}}">
<view class="coupon-conditions">
领取
</view>
</view>
</view>
</view>
</view>
</view>
</view>
</template>
商品详情展示优惠劵代码
<view>
<block wx:if="{{coupon != null}}">
<import src="../../common/coupon/orderDetailCoupon.wxml" />
<template is="orderDetailCoupon" data="{{data:coupon}}" />
</block>
</view>
优惠劵列表代码
<block>
<view class='search-box'>
<view class='inp'>
<input value='{{searchKey}}' type='text' bindinput="searchInput" placeholder='搜索' />
</view>
<view class='search-btn' bindtap='searchBtn'>搜索</view>
</view>
<!-- tab切换 -->
<view id='tab-title'>
<view class='{{tabIndex == 0?"active":""}}' bindtap='changeTab' data-id='0'>全部</view>
<view class='{{tabIndex == 1?"active":""}}' bindtap='changeTab' data-id='1'>未使用</view>
<view class='{{tabIndex == 2?"active":""}}' bindtap='changeTab' data-id='2'>已使用</view>
<view class='{{tabIndex == 3?"active":""}}' bindtap='changeTab' data-id='3'>已过期</view>
<text class='line' style='left:{{25*tabIndex}}%'></text>
</view>
<view wx:if="{{tabIndex == 0}}">
<block wx:if="{{coupon0.length != 0}}">
<import src="../../common/coupon/coupon.wxml" />
<template is="coupon" data="{{data:coupon0}}"/>
<view class='load-more' wx:if="{{coupon0!=null && pageIndex[0] == pageTotal[0]}}">没有更多数据了</view>
</block>
<view class='coupon-none' wx:if="{{coupon0 !=null&& coupon0.length == 0}}">
<import src="../../common/list/none.wxml" />
<template is="none" data="{{isNone:true}}"/>
</view>
<view class='coupon-none' wx:if="{{!isLogin}}">
<import src="../../common/list/none.wxml" />
<template is="none" data="{{isNone:false}}"/>
</view>
</view>
<view wx:if="{{tabIndex == 1}}">
<block wx:if="{{coupon1.length != 0}}" >
<import src="../../common/coupon/coupon.wxml" />
<template is="coupon" data="{{data:coupon1}}"/>
<view class='load-more' wx:if="{{coupon1!=null &&pageIndex[1] == pageTotal[1]}}">没有更多数据了</view>
</block >
<view class='coupon-none' wx:if="{{coupon1 !=null&& coupon1.length == 0}}">
<import src="../../common/list/none.wxml" />
<template is="none" data="{{isNone:true}}"/>
</view>
</view>
<view wx:if="{{tabIndex == 2}}">
<block wx:if="{{coupon2.length != 0}}">
<import src="../../common/coupon/coupon.wxml" />
<template is="coupon" data="{{data:coupon2}}"/>
<view class='load-more' wx:if="{{coupon2!=null &&pageIndex[2] == pageTotal[2]}}">没有更多数据了</view>
</block >
<view class='coupon-none' wx:if="{{coupon2 !=null&& coupon2.length == 0}}">
<import src="../../common/list/none.wxml" />
<template is="none" data="{{isNone:true}}"/>
</view>
</view>
<view wx:if="{{tabIndex == 3}}">
<block wx:if="{{coupon3.length != 0}}" >
<import src="../../common/coupon/coupon.wxml" />
<template is="coupon" data="{{data:coupon3}}"/>
<view class='load-more' wx:if="{{coupon3!=null &&pageIndex[3] == pageTotal[3]}}">没有更多数据了</view>
</block >
<view class='coupon-none' wx:if="{{coupon3 !=null&& coupon3.length == 0}}">
<import src="../../common/list/none.wxml" />
<template is="none" data="{{isNone:true}}"/>
</view>
</view>
</block>
使用优惠劵代码
<view class='price-item'>
<view class='price-left'>
<text>优惠券</text>
</view>
<view class='price-right'>
<text class='coupon'>{{couponUsed}}</text>
</view>
</view>
7.微信小程序功能截图
用户中心新增“优惠券”列表如下图所示：
使用优惠劵截图：
