CREATE OR ALTER TRIGGER USER$SCHEDULE_PUSH FOR SCHEDULE
active after INSERT OR UPDATE OR DELETE POSITION 99
AS
DECLARE action VARCHAR(6);
DECLARE send INT;
DECLARE ttl INT;
DECLARE time_delay INT; -- время, после которого рассылка не формируется;
DECLARE notify TYPE OF COLUMN clphones.NOTIFICATIONID;
--
DECLARE logid TYPE OF COLUMN sms_log.logid;
DECLARE smsid TYPE OF COLUMN sms_logdet.smsid;
DECLARE uid TYPE OF COLUMN doctor.dcode;
DECLARE filial TYPE OF COLUMN filials.filid;
DECLARE smstypeid TYPE OF COLUMN sms_log.smstypeid;
DECLARE smsrefid TYPE OF COLUMN sms_log.smsrefid;
DECLARE smsprofileid TYPE OF COLUMN sms_log.smsprofileid;
DECLARE internalid TYPE OF COLUMN sms_log.internalid;
DECLARE pcode TYPE OF COLUMN sms_log.pcode;
DECLARE planftime TYPE OF COLUMN sms_log.planftime;
DECLARE pushtext TYPE OF COLUMN sms_log.smstext;
BEGIN
time_delay = 600;
IF (EXISTS(SELECT * FROM repl$getaccess WHERE repl$access = 'USER')) THEN
BEGIN
IF (EXISTS(SELECT * FROM repl$getgrpid g JOIN filials f ON f.grpid=g.repl$grpid WHERE f.ismain=1)) THEN
exit;
-- формируем переменные
send = 0;
ttl = 0;
IF (updating)THEN
BEGIN
IF (NEW.workdate!=OLD.workdate) THEN
BEGIN
send = 1;
END
ELSE IF (NEW.bhour*60+NEW.bmin != OLD.bhour*60+OLD.bmin) THEN
BEGIN
send = 1;
END
ttl = datediff(SECOND FROM CURRENT_TIMESTAMP TO dateadd(NEW.bmin MINUTE TO dateadd(NEW.bhour HOUR TO NEW.workdate)) );
smsrefid = 70002;
pcode = NEW.pcode;
action = 'move';
END
ELSE IF (inserting) THEN
BEGIN
send = 1;
ttl = datediff(SECOND FROM CURRENT_TIMESTAMP TO dateadd(NEW.bmin MINUTE TO dateadd(NEW.bhour HOUR TO NEW.workdate)) );
smsrefid = 70001;
pcode = NEW.pcode;
action = 'new';
END
ELSE IF (deleting) THEN
BEGIN
send = 1;
ttl = datediff(SECOND FROM CURRENT_TIMESTAMP TO dateadd(OLD.bmin MINUTE TO dateadd(OLD.bhour HOUR TO OLD.workdate)) );
smsrefid = 70003;
pcode = OLD.pcode;
action = 'delete';
END
IF (ttl>2419200) THEN
ttl = 2419200;
-- смотрим наличие пациента
IF (COALESCE(pcode,0)=0) THEN
exit;
-- смотрим наличие идентификатора
notify = NULL;
SELECT NOTIFICATIONID FROM clphones cp WHERE cp.pcode= :pcode AND COALESCE(cp.isdeleted,0)=0 AND cp.phonetype=21
INTO notify;
notify = COALESCE(notify,'-');
SELECT current_uid FROM s_session_info INTO uid;
-- формируем данные
-- если есть идентификатор, можно рассылать, событие будет не ранее чем через time_delay сек
IF (notify != '-' AND send=1 AND ttl>time_delay)THEN
BEGIN
pushtext = '{"to":"'||notify||'","time_to_leave":'||ttl||',"notification":{';
IF (inserting) THEN
BEGIN
pushtext = pushtext||'"title":"Новое назначение","body":"У Вас новое назначение к доктору на '||(SELECT datestr FROM formatdate(NEW.workdate))||'."';
END
ELSE IF (updating) THEN
BEGIN
pushtext = pushtext||'"title":"Назначение перенесено","body":"Назначение к доктору перенесено';
IF (OLD.workdate != NEW.workdate) THEN
pushtext = pushtext||' с '||(SELECT datestr FROM formatdate(OLD.workdate));
pushtext = pushtext||' на '||(SELECT datestr FROM formatdate(NEW.workdate))||'."';
END
ELSE
BEGIN
pushtext = pushtext||'"title":"Назначение отменено","body":"Назначение к доктору на '||(SELECT datestr FROM formatdate(OLD.workdate))||' отменено."';
END
pushtext = pushtext||',"sound":"true","tag":"schedule"},';
pushtext = pushtext||'"data":{"action":"'||action||'",';
-- формируем старый объект
IF (updating OR deleting) THEN
BEGIN
pushtext = pushtext||'"old":{"SchedId":"'||OLD.schedid||'","DCode":"'||OLD.dcode||'","DName":"'||(SELECT dname FROM doctor WHERE dcode= OLD.dcode)||'","WorkDate":"'||(SELECT datestr FROM formatdate(OLD.workdate))||'","BHour":"'||OLD.bhour||'","BMin":"'||OLD.bmin||'"},';
filial = OLD.filial;
internalid = OLD.schedid;
END
-- формируем новый объект
IF (inserting OR updating) THEN
BEGIN
pushtext = pushtext||'"new":{"SchedId":'||NEW.schedid||',"DCode":"'||NEW.dcode||'","DName":"'||(SELECT dname FROM doctor WHERE dcode= NEW.dcode)||'","WorkDate":"'||(SELECT datestr FROM formatdate(NEW.workdate))||'","BHour":"'||NEW.bhour||'","BMin":"'||NEW.bmin||'"},';
filial = NEW.filial;
internalid = NEW.schedid;
END
planftime = dateadd(ttl SECOND TO CURRENT_TIMESTAMP);
pushtext = pushtext||'}}';
logid = gen_id(SMS_LOG_GEN, 1);
smsid = gen_id(SMS_GEN, 1);
smstypeid = 0;
smsprofileid = 1;
--
INSERT INTO SMS_LOG(LOGID,SMSTYPEID,SMSREFID,SMSPROFILEID,FILIAL,
CREATEDATE,SMSTEXT,PCODE,UID,INTERNALID,PLANBTIME,PLANFTIME,STATE)
VALUES(:logid, :smstypeid, :smsrefid, :smsprofileid, :filial,
CURRENT_TIMESTAMP, :pushtext, :pcode, :uid, :internalid, CURRENT_TIMESTAMP, :planftime, 0);
INSERT INTO SMS_LOGDET(SMSID,LOGID,PHONETYPE,EXTSMSID,PHPREFIX,PHONE,STATE,NOTIFICATIONID)
VALUES(:smsid, :logid, 21, NULL, NULL, NULL, 0, :notify);
END
END
--
END
