云平台服务化之融云消息服务化

#删除队列
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;