營銷（優惠券） · Niushop單商戶V5開發手冊

```[sql] -- ---------------------------- -- Table structure for ns_promotion_coupon -- ---------------------------- DROP TABLE IF EXISTS `ns_promotion_coupon`; CREATE TABLE `ns_promotion_coupon` ( `coupon_id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '優惠券id', `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '優惠券類型 reward-滿減 discount-折扣 random-隨機', `coupon_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '優惠券名稱', `coupon_type_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '優惠券類型id', `site_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '站點Id', `coupon_code` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '優惠券編碼', `member_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '領用人', `use_order_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '優惠券使用訂單id', `goods_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '適用商品類型1-全部商品可用；2-指定商品可用；3-指定商品不可用', `goods_ids` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '適用商品id', `at_least` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '最小金額', `money` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '面額', `discount` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '1 =< 折扣 <= 9.9 當type為discount時需要添加', `discount_limit` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '最多折扣金額當type為discount時可選擇性添加', `is_mark` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否同時給會員打標簽 0-否 1-是', `member_label_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '會員標簽id', `is_share` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '分享設置優惠券允許分享給好友領取', `is_handsel` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '轉贈設置優惠券允許轉贈給好友', `is_forbid_preference` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '優惠疊加 0-不限制 1- 優惠券僅原價購買商品時可用', `is_expire_notice` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否開啟過期提醒0-不開啟 1-開啟', `expire_notice_fixed_term` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '過期前N天提醒', `is_noticed` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否已提醒', `state` tinyint(4) UNSIGNED NOT NULL DEFAULT 0 COMMENT '優惠券狀態 1已領用（未使用） 2已使用 3已過期', `get_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '獲取方式1訂單2.直接領取3.活動領取 4轉贈 5分享獲取', `related_id` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '獲取優惠券的關聯id', `fetch_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '領取時間', `use_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '使用時間', `start_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '可使用的開始時間', `end_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '有效期結束時間', PRIMARY KEY (`coupon_id`) USING BTREE, INDEX `IDX_ns_promotion_coupon_coupon_type_id`(`coupon_type_id`) USING BTREE, INDEX `IDX_ns_promotion_coupon_end_time`(`end_time`) USING BTREE, INDEX `IDX_ns_promotion_coupon_member_id`(`member_id`) USING BTREE, INDEX `IDX_ns_promotion_coupon_site_id`(`site_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 AVG_ROW_LENGTH = 287 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '優惠券表' ROW_FORMAT = Compact; -- ---------------------------- -- Table structure for ns_promotion_coupon_type -- ---------------------------- DROP TABLE IF EXISTS `ns_promotion_coupon_type`; CREATE TABLE `ns_promotion_coupon_type` ( `coupon_type_id` int(11) NOT NULL AUTO_INCREMENT COMMENT '優惠券類型Id', `type` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '優惠券類型 reward-滿減 discount-折扣 random-隨機', `site_id` int(11) NOT NULL DEFAULT 1 COMMENT '站點id', `coupon_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '優惠券名稱', `coupon_name_remark` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '名稱備注', `image` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '優惠券圖片', `count` int(11) NOT NULL DEFAULT 0 COMMENT '發放數量', `lead_count` int(11) NOT NULL DEFAULT 0 COMMENT '已領取數量', `used_count` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '已使用數量', `goods_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 1 COMMENT '適用商品類型1-全部商品可用；2-指定商品可用；3-指定商品不可用', `goods_ids` varchar(2000) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '適用商品id', `is_limit` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '使用門檻0-無門檻 1-有門檻', `at_least` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '滿多少元使用 0代表無限制', `money` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '發放面額當type為reward時需要添加', `discount` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '1 =< 折扣 <= 9.9 當type為discount時需要添加', `discount_limit` decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '最多折扣金額當type為discount時可選擇性添加', `min_money` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '最低金額當type為radom時需要添加', `max_money` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '最大金額當type為radom時需要添加', `validity_type` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '過期類型1-時間范圍過期 2-領取之日固定日期后過期 3-領取次日固定日期后過期', `start_use_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '使用開始日期過期類型1時必填', `end_use_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '使用結束日期過期類型1時必填', `fixed_term` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '當validity_type為2或者3時需要添加領取之日起或者次日N天內有效', `is_limit_member` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否限制會員身份0-不限制 1限制', `member_level_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '若開啟會員身份限制,需要添加會員等級id', `is_limitless` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否無限制0-否 1是', `max_fetch` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '每人最大領取個數', `is_expire_notice` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否開啟過期提醒0-不開啟 1-開啟', `expire_notice_fixed_term` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '過期前N天提醒', `is_mark` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否同時給會員打標簽 0-否 1-是', `member_label_ids` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' COMMENT '會員標簽id', `is_share` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '分享設置優惠券允許分享給好友領取', `is_handsel` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '轉贈設置優惠券允許轉贈給好友', `is_forbid_preference` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '優惠疊加 0-不限制 1- 優惠券僅原價購買商品時可用', `is_show` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否顯示', `discount_order_money` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '訂單的優惠總金額', `order_money` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT '用券總成交額', `is_forbidden` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT '是否禁止發放0-否 1-是', `old_member_num` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '使用優惠券的老會員數', `new_member_num` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '平臺第一次購買使用優惠券的會員數', `order_goods_num` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '使用優惠券購買的商品數量', `status` int(11) NOT NULL DEFAULT 0 COMMENT '狀態（1進行中2已結束-1已關閉）', `create_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '創建時間', `update_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '修改時間', `end_time` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT '有效日期結束時間', PRIMARY KEY (`coupon_type_id`) USING BTREE, INDEX `IDX_ns_promotion_coupon_type_site_id`(`site_id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 AVG_ROW_LENGTH = 1820 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '優惠券類型表' ROW_FORMAT = Compact;