创建短信模板与发送日志表_lq_sms.sql
6.56 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
-- 阿里云短信：模板管理 + 发送日志（会员生日等场景可扩展）
-- 执行库：lqerp_dev / 生产对应库
CREATE TABLE IF NOT EXISTS `lq_sms_template` (
`F_Id` varchar(50) NOT NULL COMMENT '主键',
`F_Name` varchar(128) NOT NULL COMMENT '模板名称',
`F_TemplateCode` varchar(64) NOT NULL COMMENT '阿里云模板CODE',
`F_SignName` varchar(64) NOT NULL DEFAULT '绿纤聚财' COMMENT '短信签名',
`F_TemplateType` int NOT NULL DEFAULT 1 COMMENT '模板类型：1验证码 2推广短信 3通知短信',
`F_SceneCode` varchar(64) NOT NULL COMMENT '业务场景编码：Birthday/Login/Register/VerifyCode 等',
`F_ParamTemplate` varchar(512) DEFAULT NULL COMMENT '模板参数JSON模板，如 {"name":"{name}"}',
`F_Enabled` int NOT NULL DEFAULT 1 COMMENT '是否启用：1启用 0停用',
`F_Sort` int NOT NULL DEFAULT 0 COMMENT '排序',
`F_Remark` varchar(512) DEFAULT NULL COMMENT '备注',
`F_CreatorTime` datetime DEFAULT NULL COMMENT '创建时间',
`F_CreatorUserId` varchar(64) DEFAULT NULL COMMENT '创建人ID',
`F_LastModifyTime` datetime DEFAULT NULL COMMENT '最后修改时间',
`F_LastModifyUserId` varchar(64) DEFAULT NULL COMMENT '最后修改人ID',
`F_DeleteMark` int DEFAULT NULL COMMENT '删除标记（null 未删除）',
PRIMARY KEY (`F_Id`),
KEY `IX_lq_sms_template_SceneCode` (`F_SceneCode`),
KEY `IX_lq_sms_template_Code` (`F_TemplateCode`),
KEY `IX_lq_sms_template_Enabled_Sort` (`F_Enabled`, `F_Sort`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信模板（按业务场景可扩展）';
CREATE TABLE IF NOT EXISTS `lq_sms_send_log` (
`F_Id` varchar(50) NOT NULL COMMENT '主键',
`F_SceneCode` varchar(64) NOT NULL COMMENT '业务场景编码',
`F_TemplateId` varchar(50) DEFAULT NULL COMMENT '模板主键',
`F_TemplateCode` varchar(64) NOT NULL COMMENT '模板CODE',
`F_SignName` varchar(64) DEFAULT NULL COMMENT '签名',
`F_MobileMasked` varchar(32) NOT NULL COMMENT '手机号脱敏',
`F_MobileHash` varchar(64) DEFAULT NULL COMMENT '手机号哈希（防重比对，非明文）',
`F_MemberId` varchar(50) DEFAULT NULL COMMENT '会员ID（lq_khxx.F_Id）',
`F_MemberName` varchar(128) DEFAULT NULL COMMENT '会员姓名',
`F_TemplateParam` varchar(1024) DEFAULT NULL COMMENT '实际发送模板参数JSON',
`F_Status` int NOT NULL DEFAULT 0 COMMENT '发送状态：0失败 1成功 2跳过(防重)',
`F_ProviderResult` varchar(256) DEFAULT NULL COMMENT '厂商返回码/说明',
`F_BizDate` date DEFAULT NULL COMMENT '业务日期（生日防重按日）',
`F_IsTest` int NOT NULL DEFAULT 0 COMMENT '是否测试发送：1是 0否',
`F_CreatorTime` datetime DEFAULT NULL COMMENT '发送时间',
`F_CreatorUserId` varchar(64) DEFAULT NULL COMMENT '操作人ID',
PRIMARY KEY (`F_Id`),
KEY `IX_lq_sms_send_log_BizDate_Scene_Member` (`F_BizDate`, `F_SceneCode`, `F_MemberId`),
KEY `IX_lq_sms_send_log_CreatorTime` (`F_CreatorTime`),
KEY `IX_lq_sms_send_log_Status` (`F_Status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='短信发送日志';
-- 种子模板（幂等：同场景编码已存在则跳过）
INSERT INTO `lq_sms_template`
(`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000001', '生日提醒', 'SMS_503595047', '绿纤聚财', 2, 'Birthday', '{"name":"{name}"}', 1, 1, '会员生日推广短信', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'Birthday' AND `F_DeleteMark` IS NULL);
INSERT INTO `lq_sms_template`
(`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000002', '用于登录', 'SMS_489770174', '绿纤聚财', 1, 'Login', '{"code":"{code}"}', 1, 2, '登录验证码', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'Login' AND `F_DeleteMark` IS NULL);
INSERT INTO `lq_sms_template`
(`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000003', '用于注册', 'SMS_489645186', '绿纤聚财', 1, 'Register', '{"code":"{code}"}', 1, 3, '注册验证码', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'Register' AND `F_DeleteMark` IS NULL);
INSERT INTO `lq_sms_template`
(`F_Id`, `F_Name`, `F_TemplateCode`, `F_SignName`, `F_TemplateType`, `F_SceneCode`, `F_ParamTemplate`, `F_Enabled`, `F_Sort`, `F_Remark`, `F_CreatorTime`, `F_DeleteMark`)
SELECT '202607230000000004', '验证码短信', 'SMS_321375912', '绿纤聚财', 1, 'VerifyCode', '{"code":"{code}"}', 1, 4, '通用验证码（签名也可为绿纤聚财成都美容）', NOW(), NULL
WHERE NOT EXISTS (SELECT 1 FROM `lq_sms_template` WHERE `F_SceneCode` = 'VerifyCode' AND `F_DeleteMark` IS NULL);
-- 默认短信配置占位（Category=SmsConfig；密钥请在系统配置页填写真实值）
INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000101', '短信签名', 'smssignname', '绿纤聚财', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smssignname');
INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000102', '短信厂商', 'smscompany', '1', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smscompany');
INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000103', '短信Endpoint', 'smsendpoint', 'dysmsapi.aliyuncs.com', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smsendpoint');
INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000104', '生日防重发', 'smspreventduplicate', '1', 'SmsConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SmsConfig' AND LOWER(`F_KEY`) = 'smspreventduplicate');
-- 隐私：手机号脱敏开关（默认关闭=明文；Category=SysConfig）
INSERT INTO `BASE_SYSCONFIG` (`F_Id`, `F_NAME`, `F_KEY`, `F_VALUE`, `F_CATEGORY`)
SELECT '202607230000000201', '手机号脱敏', 'privacymobilemask', '0', 'SysConfig'
WHERE NOT EXISTS (SELECT 1 FROM `BASE_SYSCONFIG` WHERE `F_CATEGORY` = 'SysConfig' AND LOWER(`F_KEY`) = 'privacymobilemask');