#删除队列 redis-cli del sendmessage_list #查看队列 redis-cli lrange sendmessage_list 0 -1 #测试Redis写入 SELECT redis_connect('10.10.3.199',6379); SELECT redis_pipe(CONCAT('lpush sendmessage_list',' ','123','\r\n')); SELECT redis_pipe(CONCAT('lpush sendmessage_list',' ','456','\r\n')); SELECT redis_pipe(CONCAT('lpush sendmessage_list',' ','789','\r\n')); #测试用例 TRUNCATE table t_sendmessage_task;
insert into t_sendmessage_task
(SenderIdentity,SenderId,SenderLoginName,SendStatus,IsUrl,UrlName,TemplateId,BureauId,ProvinceId,CityId,DistrictId,ParamsList,TargetList,businessId)
values(
5,91936,'huanghai',-1,1,'zy_index_window',1,-1,-1,-1,-1,'{"[#param1]":"熊猫","[#param2]":"狗熊","[#param3]":"驴子","[#param4]":"金鱼","[#param5]":"海豚"}'
,"huanghai_edusoa",-1
);
#3、更新测试 update t_sendmessage_rongyun_original set SendStatus=2 where id=1; INSERT INTO `t_sendmessage_rongyun_template` VALUES ('1', '【[#param1]】您【[#param2]】的【[#param3]】在【[#param4]】应用中已经[#param5]。'); INSERT INTO `t_sendmessage_rongyun_template` VALUES ('2', '【[#param1]】您有一条新的【[#param2]】,请到【[#param3]】应用中查看。'); INSERT INTO `t_sendmessage_rongyun_template` VALUES ('3', '【[#param1]】您的孩子【[#param2]】[#param3]【[#param4]】课堂,点名时间:[#param5],任课教师:[#param6]。'); INSERT INTO `t_sendmessage_rongyun_template` VALUES ('4', '【[#param1]】您的孩子【[#param2]】在【[#param3]】课堂上回答了教师提问,评价等级为【[#param4]】。'); INSERT INTO `t_sendmessage_rongyun_template` VALUES ('5', '【[#param1]】您的孩子【[#param2]】在[#param3]【[#param4]】课堂上,课堂评价为:[#param5];评价等级为【[#param6]】。'); INSERT INTO `t_sendmessage_rongyun_template` VALUES ('6', '【[#param1]】您的孩子【[#param2]】在【[#param3]】课堂上回答了教师提问,问题为:【[#param4]】;答案为:【[#param5]】;评价等级为【[#param6]】。'); INSERT INTO `t_sendmessage_rongyun_template` VALUES ('7', '【[#param1]】【[#param2]】申请添加您为好友,请到【[#param3]】应用中查看。');
业务表,描述查询的业务,比如A区所有的教师
CREATE TABLE `t_sendmessage_business` ( `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '业务ID', `BusinessName` varchar(255) NOT NULL COMMENT '业务名称,如:按区查询所有教师,按区查询语文教研员', `Url` varchar(1024) NOT NULL COMMENT '将数据提供到的ACTION接口,可以分页查询到分发的人员', `CreateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`Id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
指定人员列表的消息发送
CREATE TABLE `t_sendmessage_task` (
`Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键,无实际意义',
`SenderIdentity` int(11) NOT NULL COMMENT '发送人身份ID',
`SenderId` int(11) NOT NULL COMMENT '发送人ID',
`SenderLoginName` varchar(255) NOT NULL COMMENT '发送人登录名',
`CreateTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
`SendStatus` int(11) NOT NULL DEFAULT '-1' COMMENT '发送状态,-1:待发送,1:已送,2:发送失败',
`SendTime` datetime DEFAULT NULL COMMENT '真正发送完成的时间',
`IsUrl` int(255) NOT NULL DEFAULT '0' COMMENT '是否允许跳转页面 默认值:0',
`UrlName` varchar(255) NOT NULL COMMENT '跳转页面名称,需到周枫处确认',
`TemplateId` int(11) NOT NULL COMMENT '系统消息编号',
`BureauId` int(11) NOT NULL COMMENT '机构id',
`ProvinceId` int(11) NOT NULL COMMENT '省id',
`CityId` int(11) NOT NULL COMMENT '市id',
`DistrictId` int(11) NOT NULL COMMENT '区id',
`ParamsList` text NOT NULL COMMENT '参数JSON串',
`BusinessId` int(11) NOT NULL DEFAULT '-1' COMMENT '哪种业务消息?关联t_sendmessage_business,-1表示不是业务要求,而是基本要求',
`TargetList` text NOT NULL COMMENT '接收人的登录名Json串,如果是业务要求,那么默认值是-1',
`TypeId` int(11) NOT NULL DEFAULT '1' COMMENT '1: 融云,2:短信,3:Email',
PRIMARY KEY (`Id`),
KEY `SenderId` (`SenderIdentity`,`SenderId`),
KEY `SendStatus` (`SendStatus`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
CREATE TRIGGER `tr_sendmessage_task_insert` before INSERT ON `t_sendmessage_task` FOR EACH ROW BEGIN
select identity_id ,person_id into @identity_id,@person_id from t_sys_loginperson where LOGIN_NAME=new.SenderLoginName;
select BUREAU_ID,PROVINCE_ID,CITY_ID ,DISTRICT_ID into @BUREAU_ID, @PROVINCE_ID, @CITY_ID,@DISTRICT_ID
from t_base_person where IDENTITY_ID=@identity_id and PERSON_ID=@person_id;
set new.BureauId=@BUREAU_ID;
set new.ProvinceId=@PROVINCE_ID;
set new.CityId=@CITY_ID;
set new.DistrictId=@DISTRICT_ID;
set @serverIp = (select `value` from t_sys_config where id=1);
set @n =(SELECT redis_connect(@serverIp,6379));
set @str = (select redis_pipe(CONCAT('lpush sendmessage_list',' ',new.id,'
')));
END;
CREATE TRIGGER `tr_sendmessage_task_modify` AFTER UPDATE ON `t_sendmessage_task` FOR EACH ROW begin
if(new.SendStatus=-1) then
set @serverIp = (select `value` from t_sys_config where id=1);
set @n =(SELECT redis_connect(@serverIp,6379));
set @str = (select redis_pipe(CONCAT('lpush sendmessage_list',' ',new.id,'
')));
end if;
end;
消息模板,不允许随意消息发送
CREATE TABLE `t_sendmessage_template` (
`Id` int(11) NOT NULL AUTO_INCREMENT,
`Content` varchar(255) NOT NULL COMMENT '模板内容',
PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;