java-bbc/db/2024-07-30 添加电子卡券.sql

USE yami_bbc;

# 商品电子卡券相关字段
ALTER TABLE `tz_prod`
    ADD COLUMN `is_bind_voucher` INT DEFAULT NULL COMMENT '是否关联卡券 1.是 0.否',
    ADD COLUMN `directions` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '使用说明';

ALTER TABLE `tz_sku`
    ADD COLUMN `voucher_id` bigint(0) NULL COMMENT '关联卡券id' AFTER `stock_warning_status`;

CREATE TABLE `tz_voucher` (
                              `voucher_id` bigint NOT NULL AUTO_INCREMENT COMMENT '卡券id',
                              `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '卡券名称',
                              `create_time` datetime NOT NULL COMMENT '创建时间',
                              `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                              `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态 -1：已删除 0：正常',
                              `shop_id` bigint NOT NULL COMMENT '店铺id',
                              PRIMARY KEY (`voucher_id`),
                              KEY `tz_voucher_id` (`voucher_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

CREATE TABLE `tz_voucher_item` (
                                   `voucher_item_id` bigint NOT NULL AUTO_INCREMENT COMMENT '卡券详情id',
                                   `voucher_id` bigint NOT NULL COMMENT '卡券id',
                                   `card_number` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卡号',
                                   `card_pwd` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '卡密',
                                   `coupon_code` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '优惠码',
                                   `create_time` datetime NOT NULL COMMENT '创建时间',
                                   `update_time` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                   `order_number` varchar(50) DEFAULT NULL COMMENT '订单号',
                                   `import_time` datetime NOT NULL COMMENT '导入日期',
                                   `expiration_time` datetime NOT NULL COMMENT '失效时间',
                                   `status` tinyint NOT NULL DEFAULT '0' COMMENT '状态 -1 已删除 0：已失效 1已发送 2未发送',
                                   PRIMARY KEY (`voucher_item_id`),
                                   KEY `tz_voucher_item_id` (`voucher_item_id`) USING BTREE,
                                   KEY `tz_voucher_id` (`voucher_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

ALTER TABLE `tz_order`
    ADD COLUMN `directions` varchar(255) NULL COMMENT '使用说明',
    ADD COLUMN `is_bind_voucher` INT DEFAULT NULL COMMENT '是否关联卡券 1.是 0.否';

ALTER TABLE `tz_order_item`
    ADD COLUMN `voucher_id` bigint(0) NULL COMMENT '卡券id';

INSERT INTO `tz_shop_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`) VALUES
 (754, 1, 'prod/voucher/index', '', 1, '', 5, 0),
 (755, 754, '', 'multishop:voucher:save', 2, '', 0, 0),
 (756, 754, '', 'multishop:voucher:update', 2, '', 0, 0),
 (757, 754, '', 'multishop:voucher:delete', 2, '', 0, 0),
 (758, 754, '', '', 2, '', 0, 0),
 (759, 1, 'prod/voucher/card-list/index', '', 1, '', 6, 1),
 (760, 759, '', 'multishop:voucherItem:save', 2, '', 0, 0),
 (761, 759, '', 'multishop:voucherItem:delete', 2, '', 0, 0),
 (762, 759, '', 'multishop:voucherItem:update', 2, '', 0, 0),
 (763, 759, '', 'multishop:voucherItem:import', 2, '', 0, 0),
 (764, 759, '', 'multishop:voucherItem:export', 2, '', 0, 0),
 (765, 759, '', 'multishop:voucherItem:batchDelete', 2, '', 0, 0);

INSERT INTO `tz_shop_menu_lang`(`menu_id`, `lang`, `name`) VALUES
(754, 0, '卡券管理'),
(754, 1, 'Vouchers'),
(755, 0, '新增'),
(755, 1, 'add'),
(756, 0, '修改'),
(756, 1, 'update'),
(757, 0, '删除'),
(757, 1, 'delete'),
(758, 0, '查看'),
(758, 1, 'view'),
(759, 0, '卡券明细'),
(759, 1, 'Card voucher details'),
(760, 0, '新增'),
(760, 1, 'add'),
(761, 0, '删除'),
(761, 1, 'delete'),
(762, 0, '修改'),
(762, 1, 'update'),
(763, 0, '导入'),
(763, 1, 'import'),
(764, 0, '导出'),
(764, 1, 'export'),
(765, 0, '批量删除'),
(765, 1, 'Batch delete');


USE `xxl_job`;
# 添加定时任务
INSERT INTO `xxl_job_info`(`job_group`, `job_desc`, `add_time`, `update_time`, `author`, `alarm_email`, `schedule_type`, `schedule_conf`, `misfire_strategy`, `executor_route_strategy`, `executor_handler`, `executor_param`, `executor_block_strategy`, `executor_timeout`, `executor_fail_retry_count`, `glue_type`, `glue_source`, `glue_remark`, `glue_updatetime`, `child_jobid`, `trigger_status`, `trigger_last_time`, `trigger_next_time`)
VALUES (2, '过期卡券商品，减少库存', '2024-07-16 10:58:11', '2024-07-16 10:58:19', 'admin', '', 'CRON', '0 0/1 * * * ?', 'DO_NOTHING', 'FIRST', 'overdueVoucher', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2024-07-16 10:58:11', '', 1, 1721099820000, 1721099880000);