hytc-admin/addons/notice/install.sql

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for fa_notice
-- ----------------------------
CREATE TABLE `__PREFIX__notice` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `name` varchar(255) DEFAULT NULL COMMENT '消息名称',
  `event` varchar(255) DEFAULT NULL COMMENT '消息事件',
  `platform` enum('user','admin','mptemplate') DEFAULT 'user' COMMENT '平台:user=用户,admin=后台',
  `type` enum('msg','email','mptemplate') NOT NULL COMMENT '消息类型:msg=站内通知,email=邮箱通知',
  `to_id` int(255) NOT NULL COMMENT '接收人id',
  `content` text COMMENT '内容',
  `ext` text COMMENT '扩展',
  `notice_template_id` int(11) unsigned DEFAULT '0' COMMENT '消息模板',
  `readtime` bigint(16) DEFAULT NULL COMMENT '是否已读',
  `createtime` bigint(16) DEFAULT NULL COMMENT '创建时间',
  `updatetime` bigint(16) DEFAULT NULL COMMENT '更新时间',
  `deletetime` bigint(16) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `notifications_notifiable_id_notifiable_type_index` (`to_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='消息通知';

-- ----------------------------
-- Table structure for fa_notice_admin_mptemplate
-- ----------------------------
CREATE TABLE `__PREFIX__notice_admin_mptemplate` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `admin_id` int(11) DEFAULT NULL COMMENT '管理员',
  `nickname` varchar(255) DEFAULT NULL COMMENT '微信昵称',
  `avatar` varchar(255) DEFAULT NULL COMMENT '微信头像',
  `openid` varchar(100) DEFAULT NULL,
  `unionid` varchar(100) DEFAULT NULL,
  `createtime` bigint(16) DEFAULT NULL COMMENT '创建时间',
  `updatetime` bigint(16) DEFAULT NULL COMMENT '更新时间',
  `deletetime` bigint(16) DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='管理员绑定微信(模版消息)';

-- ----------------------------
-- Table structure for fa_notice_event
-- ----------------------------
CREATE TABLE `__PREFIX__notice_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `platform` set('user','admin') NOT NULL DEFAULT 'user' COMMENT '支持平台:user=用户,admin=后台',
  `type` set('msg','email','mptemplate') NOT NULL COMMENT '支持类型:msg=站内通知,email=邮箱通知',
  `name` varchar(50) NOT NULL COMMENT '消息名称',
  `event` varchar(50) NOT NULL COMMENT '消息事件',
  `send_num` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '发送次数',
  `send_fail_num` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '发送失败次数',
  `content` text COMMENT '参数',
  `visible_switch` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态:0=关闭,1=启用',
  `createtime` bigint(16) DEFAULT NULL COMMENT '创建时间',
  `updatetime` bigint(16) DEFAULT NULL COMMENT '更新时间',
  `deletetime` bigint(16) unsigned DEFAULT NULL COMMENT '删除时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `event` (`event`) USING HASH
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='消息事件';

-- ----------------------------
-- Records of fa_notice_event
-- ----------------------------
BEGIN;
INSERT INTO `__PREFIX__notice_event` VALUES (1, 'user', 'msg', '消费积分', 'reduce_score', 0, 0, '{\"user_id\":\"用户id\",\"score\":\"积分值\",\"before\":\"变更前积分\",\"after\":\"变更后积分\",\"memo\":\"原因\",\"createtime_text\":\"发生时间(年-月-日 时:分:秒)\"}', 1, 1611811483, 1647929069, NULL);
INSERT INTO `__PREFIX__notice_event` VALUES (2, 'user', 'msg', '获得积分', 'gain_score', 0, 0, '{\"user_id\":\"用户id\",\"score\":\"积分值\",\"before\":\"变更前积分\",\"after\":\"变更后积分\",\"memo\":\"原因\",\"createtime_text\":\"发生时间(年-月-日 时:分:秒)\"}', 1, 1611811519, 1647929076, NULL);
INSERT INTO `__PREFIX__notice_event` VALUES (3, 'user', 'msg,email', '消费余额', 'reduce_money', 0, 0, '{\"user_id\":\"用户id\",\"money\":\"余额\",\"before\":\"变更前积分\",\"after\":\"变更后积分\",\"memo\":\"原因\",\"createtime_text\":\"发生时间(年-月-日 时:分:秒)\"}', 1, 1611811483, 1647929422, NULL);
INSERT INTO `__PREFIX__notice_event` VALUES (4, 'admin', 'msg,email', '获得余额', 'gain_money', 0, 0, '{\"user_id\":\"用户id\",\"money\":\"余额\",\"before\":\"变更前积分\",\"after\":\"变更后积分\",\"memo\":\"原因\",\"createtime_text\":\"发生时间(年-月-日 时:分:秒)\"}', 1, 1611811519, 1647929452, NULL);
INSERT INTO `__PREFIX__notice_event` VALUES (5, 'user,admin', 'msg,email,mptemplate', '新订单提醒(测试)', 'test', 0, 0, '{\"user_id\":\"用户id\",\"no\":\"订单号\",\"title\":\"订单标题\",\"money\":\"订单金额\"}', 1, 1647929183, 1647929183, NULL);
COMMIT;

-- ----------------------------
-- Table structure for fa_notice_template
-- ----------------------------
CREATE TABLE `__PREFIX__notice_template` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `notice_event_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '事件',
  `platform` enum('user','admin') NOT NULL DEFAULT 'user' COMMENT '平台:user=用户,admin=后台',
  `type` enum('msg','email','mptemplate') NOT NULL COMMENT '类型:msg=站内通知,email=邮箱通知',
  `mptemplate_id` varchar(100) DEFAULT '' COMMENT '微信公众号模板id',
  `mptemplate_json` varchar(3000) DEFAULT NULL COMMENT '公众号模版数据',
  `content` text COMMENT '消息内容',
  `visible_switch` tinyint(1) unsigned NOT NULL DEFAULT '1' COMMENT '状态:0=关闭,1=启用',
  `send_num` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '发送次数',
  `send_fail_num` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '发送失败次数',
  `ext` varchar(255) DEFAULT NULL COMMENT '扩展数据',
  `createtime` bigint(16) DEFAULT NULL COMMENT '创建时间',
  `updatetime` bigint(16) DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COMMENT='消息模版';

-- ----------------------------
-- Records of fa_notice_template
-- ----------------------------
BEGIN;
INSERT INTO `__PREFIX__notice_template` VALUES (1, 1, 'user', 'msg', NULL, NULL, '您于{{createtime_text}}消费了{{score}}积分。', 0, 0, 0, NULL, NULL, 1647929707);
INSERT INTO `__PREFIX__notice_template` VALUES (2, 2, 'user', 'msg', NULL, NULL, '您于{{createtime_text}}获得了{{score}}积分。', 0, 0, 0, NULL, 1611997563, 1647929700);
INSERT INTO `__PREFIX__notice_template` VALUES (3, 1, 'user', 'email', NULL, NULL, '您于{{createtime_text}}消费了{{score}}积分。', 0, 0, 0, NULL, 1611998552, 1613964268);
INSERT INTO `__PREFIX__notice_template` VALUES (4, 2, 'user', 'email', NULL, NULL, '您于{{createtime_text}}获得了{{score}}积分。', 0, 0, 0, NULL, 1611998562, 1613962053);
INSERT INTO `__PREFIX__notice_template` VALUES (5, 1, 'admin', 'email', NULL, NULL, '用户{{user_nickname}}通过{{memo}}消费了{{score}}积分。', 0, 0, 0, NULL, 1613662852, 1613888236);
INSERT INTO `__PREFIX__notice_template` VALUES (6, 1, 'admin', 'msg', NULL, NULL, '用户{{user_nickname}}通过{{memo}}消费{{score}}积分。', 1, 0, 0, NULL, 1613662880, 1613888225);
INSERT INTO `__PREFIX__notice_template` VALUES (7, 5, 'admin', 'msg', NULL, NULL, '您有新的订单待处理请及时查看、金额{{money}}、订单号{{no}}。', 1, 0, 0, NULL, 1647929671, 1648003565);
INSERT INTO `__PREFIX__notice_template` VALUES (8, 5, 'user', 'msg', NULL, NULL, '<span style=\"font-size:10.8333px;\">你成功下单 {{title}}，消费金额 {{money}}.</span>', 1, 0, 0, NULL, 1647929769, 1647929769);
INSERT INTO `__PREFIX__notice_template` VALUES (9, 5, 'admin', 'mptemplate', 'fJ3Z3QDxo4hiOrltu2PtMBwmzeU3RQ1I86UG2iYoGY4', '{\"title\":\"{{title}}\",\"money\":\"{{money}}\"}', '', 1, 0, 0, NULL, 1647939557, 1647941061);
INSERT INTO `__PREFIX__notice_template` VALUES (10, 5, 'user', 'mptemplate', 'fJ3Z3QDxo4hiOrltu2PtMBwmzeU3RQ1I86UG2iYoGY4', '{\"title\":\"标题{{title}}\",\"money\":\"{{money}}\"}', NULL, 1, 0, 0, NULL, 1647939557, 1647941071);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;


--
-- 1.0.2
-- 添加url字段url
-- 添加url标题字段urltitle
-- 添加url类型字段url_type
ALTER TABLE `__PREFIX__notice_template`
ADD COLUMN `url` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'url' AFTER `createtime`,
ADD COLUMN `url_title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'url标题' AFTER `url`,
ADD COLUMN `url_type` tinyint(255) UNSIGNED NULL DEFAULT 1 COMMENT 'url类型:1=链接,2=弹窗,3=新窗口' AFTER `url_title`;