java-bbc/db/2023-12-18-签名密钥访问和消息模板优化.sql

USE yami_bbc;

#第三方签名密钥修改
CREATE TABLE `tz_sys_access_menu` (
        `id` BIGINT NOT NULL AUTO_INCREMENT,
        `sys_access_key_id` BIGINT DEFAULT NULL COMMENT '密钥id',
        `menu_id` BIGINT DEFAULT NULL COMMENT '菜单ID',
        PRIMARY KEY (`id`),
        KEY `sys_access_key_idx` (`sys_access_key_id`,`menu_id`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=6456 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='密钥对应可用菜单';


ALTER TABLE `tz_sys_access_key`
    ADD COLUMN `usability_shop_id` tinyint(0) NOT NULL DEFAULT '0' COMMENT '密钥可用店铺id,如果为0则为所有店铺',
    ADD COLUMN `sys_type` tinyint(0) NOT NULL COMMENT '系统类型 0.c端用户 1.商家端 2.平台端';


# 1.公众号模板修改,此处将暂无公众号对应模板的修改为短信和站内信发送方式,注意!!!!
update tz_notify_template set template_types = '1,3' where send_type in (1,5,9,10,11);

# 2.选择行业： 电商平台 > 电商平台,在模板库搜索模板名称，或者直接Ctrl+F在搜索到对应的编号添加上,最后将模板id填入我们的消息模板表中即可。

模板名称			            编号              使用字段
订单未支付提醒	            暂无
下单成功通知			        47973           订单编号,商品名称,订单金额,下单时间
同意退款退货提醒 	   	        46064           订单号,退货商品,退货金额
退款失败通知(商家拒绝退款)	    46476           订单号,商品名称,退款金额
核销成功提醒		            暂无
发货提醒 		            44610           订单编号,订单商品,快递公司,快递单号,发货时间
拼团失败提醒		            53754           订单编号,商品名称,退款金额
拼团成功提醒		            44579           商品名称,拼单时间
开团成功提醒		            暂无
会员升级通知		            暂无
退款超时提醒		            暂无


# 菜单权限补充
INSERT INTO `tz_shop_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`)
VALUES
    (721, 394, '', 'shop:notifyLog:page', 2, '', 0, 1),
    (722, 394, '', 'shop:notifyLog:count', 2, '', 0, 1);

INSERT INTO `tz_shop_menu_lang`(`menu_id`, `lang`, `name`)
VALUES
    (721, 0, '查询'),
    (722, 0, '统计');


CREATE TABLE `tz_notice_tag` (
    `id` BIGINT NOT NULL AUTO_INCREMENT COMMENT '公告用户标签关联表',
    `notice_id` BIGINT DEFAULT NULL COMMENT '公告id',
    `user_tag_id` BIGINT DEFAULT NULL COMMENT '用户标签id',
    PRIMARY KEY (`id`),
    KEY `template_tag_idx` (`notice_id`,`user_tag_id`) USING BTREE
) ENGINE=INNODB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='公告用户标签关联信息';

ALTER TABLE `tz_notice`
    ADD COLUMN `visible_user_type` TINYINT(0) NOT NULL DEFAULT '0' COMMENT '可见用户类型 0.全部用户可见 1.标签用户可见',
    DROP COLUMN user_ids;


#!!!!!!!!删除站内消息模板,722为站内消息的菜单id!!!!!
DELETE FROM `tz_sys_menu_lang` WHERE menu_id IN (722);
DELETE FROM `tz_sys_menu_lang` WHERE menu_id IN
(SELECT menu_id FROM `tz_sys_menu` WHERE parent_id IN (722));
DELETE FROM `tz_sys_menu` WHERE menu_id IN (722);
DELETE FROM `tz_sys_menu` WHERE parent_id IN (722);

# 地址菜单合并
INSERT INTO `tz_sys_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`)
VALUES
    (1139, 1, '', '', 0, '', 0, 0);

INSERT INTO `tz_sys_menu_lang`(`menu_id`, `lang`, `name`)
VALUES
    (1139, 0, '地址管理');

# menu_id=457为'地址管理'
UPDATE `tz_sys_menu`
SET
    `parent_id` = 1139
WHERE
    `menu_id` = 457;

# menu_id=927为'热门城市'
UPDATE `tz_sys_menu`
SET
    `parent_id` = 1139
WHERE
    `menu_id` = 927;

# 添加积分规格菜单
INSERT INTO `tz_sys_menu`(`menu_id`, `parent_id`, `url`, `perms`, `type`, `icon`, `order_num`, `hidden`)
VALUES
    (1140, 576, 'member/score/spec/index', '', 1, '', 1, 0),
    (1141, 1140, '', 'prod:spec:list,prod:spec:page', 2, '', 0, 0),
    (1142, 1140, '', 'prod:spec:save', 2, '', 0, 0),
    (1143, 1140, '', 'prod:spec:update', 2, '', 0, 0),
    (1144, 1140, '', 'prod:spec:delete', 2, '', 0, 0);

INSERT INTO `tz_sys_menu_lang`(`menu_id`, `lang`, `name`)
VALUES
    (1140, 0, '积分规格'),
    (1141, 0, '查询'),
    (1142, 0, '新增'),
    (1143, 0, '修改'),
    (1144, 0, '删除');

# 716为'运费模板'
UPDATE `tz_sys_menu`
SET
    `order_num` = 2
WHERE
    `menu_id` = 716;

# 用户拓展信息新增字段
ALTER TABLE `tz_user_extension`
ADD COLUMN `allinpay_create` tinyint(1) DEFAULT 0 COMMENT '是否创建通联会员(0:否，1:是)' AFTER `allinpay_total_balance`;

# 新增定时任务
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, '用户创建通联会员', '2023-11-10 14:30:03', '2023-11-10 14:51:03', 'admin', '', 'CRON', '0 0/10 * * * ? *', 'DO_NOTHING', 'FIRST', 'createAllinpayMember', '', 'SERIAL_EXECUTION', 0, 0, 'BEAN', '', 'GLUE代码初始化', '2023-11-10 14:30:03', '', 0, 0, 1699599600000);