Push уведомления

Технология push используется для рассылки персонализированных сообщений на мобильные телефоны. Для рассылки сообщений используется сервер FCM¹⁾

Подписка на функции осуществляется через функцию API²⁾ Личного кабинета пациента.
Непосредственная рассылка объектов осуществляется сервисом смс-рассылки

В настоящий момент используется несколько типов передаваемых сообщений:

• Генерируется Инфоклиникой
  Генерация осуществляется на основе триггеров к соответствующим таблицам данных
  • Запись
  • Повторная явка
  • Результат исследования
  • Оценка приема
• Генерируется на сайте
  • Акции

Также, для работы с данными событиями дополняется справочник типов смс сообщений (SMS_REF) с настройками рассылки

Тип рассылки: Только Push.
Время рассылки: с 0ч до 23ч.

{
    "to":"<to>",
    "time_to_leave":2119200,
    "notification":{
        "title":"Назначение перенесено",
        "body":"Назначение к доктору перенесено с 07.11.2017 на 08.11.2017.",
        "sound":"true",
        "tag":"schedule"
    },
    "data":{
        "datatype":"schedule",
        "action":"[new|move|delete]",
        "old":{
            "SchedId":641286841,
            "DCode":50000040,
            "DName":"Рябинина С.Н.",
            "WorkDate":"07.11.2017",
            "BHour":9,
            "BMin":30
        },
        "new":{
            "SchedId":641286841,
            "DCode":50000040,
            "DName":"Рябинина С.Н.",
            "WorkDate":"08.11.2017",
            "BHour":8,
            "BMin":30
        },
    }
}

В поле action передается производимое действие (new - новое назначение; move - перенесено назначение; delete - удалено назначение)

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;
      --in autonomous transaction do
      BEGIN
        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
  --
END

{
    "to":"<to>",
    "time_to_leave":2119200,
    "notification":{
        "title":"Приглашение",
        "body":"Приглашаем Вас на повторный прием к доктору",
        "sound":"true",
        "tag":"record"
    },
    "data":{
        "action":"record",
        "DCode":10101010,
        "DName":"Фартух Д.А.",
        "DepNum":120,
        "DepName":"Дерматолог",
        "filial":1,
        "FName":"Коломенский",
        "WorkDate":"21.11.2017",
        "SchList":"0,990099,990088",
        "wtime":45
    }
}

EXECUTE block
AS
-- константы
DECLARE ttl INT;  
DECLARE day_delay INT;  
DECLARE cnt INT;     
DECLARE uid TYPE OF COLUMN doctor.dcode;
-- данные пациента  
DECLARE notify TYPE OF COLUMN clphones.notificationid; 
DECLARE pcode TYPE OF COLUMN clients.pcode;
-- данные приема       
DECLARE treatcode TYPE OF COLUMN treat.treatcode;
DECLARE return_date DATE;
DECLARE fjid TYPE OF COLUMN filials.jid;
DECLARE filial TYPE OF COLUMN filials.filid;
DECLARE fname TYPE OF COLUMN filials.shortname;  
DECLARE depnum TYPE OF COLUMN departments.depnum;
DECLARE depname TYPE OF COLUMN departments.depname;
DECLARE dcode TYPE OF COLUMN doctor.dcode;
DECLARE dname TYPE OF COLUMN doctor.dname; 
DECLARE dcode_send TYPE OF COLUMN doctor.dcode;  
-- данные по услуге   
DECLARE spec INT;
DECLARE schid TYPE OF COLUMN wschema.schid;
DECLARE worktime INT;
DECLARE structid TYPE OF COLUMN wschema.structid;
-- вставка сообщения
DECLARE logid TYPE OF COLUMN sms_log.logid;
DECLARE smsid TYPE OF COLUMN sms_logdet.smsid;
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 pushtext TYPE OF COLUMN sms_log.smstext;
DECLARE planbtime TYPE OF COLUMN sms_log.planbtime;
DECLARE planftime TYPE OF COLUMN sms_log.planftime;
-- процедура поиска
DECLARE PROCEDURE prc_datelist
RETURNS (
  treatdate TYPE OF COLUMN paramsinfo.treatdate,
  treatcode TYPE OF COLUMN paramsinfo.treatcode,
  pcode TYPE OF COLUMN clients.pcode,
  dcode TYPE OF COLUMN doctor.dcode,
  return_date DATE
)
AS
DECLARE txt TYPE OF COLUMN paramsinfo.valuetext;
DECLARE p1 INT;
DECLARE tmp TYPE OF COLUMN paramsinfo.valuetext;
--
DECLARE date_litera ttext16;
DECLARE date_interv INT;
--
BEGIN
  FOR SELECT LOWER(TRIM(valuetext)), treatdate, treatcode, pcode, dcode FROM paramsinfo p
       WHERE p.codeparams IN (990001860/* повторная явка */,990002043/*гастро*/,
                              990001689/*аллерг-1*/,990001823/*аллерг-2*/,
                              990001748/*невро*/,640000067/*берем*/,
                              990002216/*офтальм*/)
         AND p.ver_no = 0
         AND p.treatdate BETWEEN current_date-370 AND CURRENT_DATE
         AND TRIM(valuetext)!=''
         AND valuetext NOT LIKE '%за результатами анализ%'
  INTO txt, treatdate, treatcode, pcode, dcode do
  BEGIN
    return_date = NULL;
    date_interv = 0;
    --
    while (POSITION('  ', txt)>0) do
    BEGIN
      txt = REPLACE(txt, '  ', ' ');
    END
 
    -- первым указано `через N период`
    IF (POSITION('через ', txt)=1) THEN
    BEGIN
      -- отсекаем 1е слово
      tmp = TRIM(SUBSTRING(txt FROM 6));
      -- иищем начало 2го слова
      p1 = POSITION(' ', tmp);
      IF (p1>0) THEN
      BEGIN
        BEGIN
          date_interv = TRIM(SUBSTRING(tmp FROM 1 FOR p1));
          date_litera = TRIM(SUBSTRING(tmp FROM p1 FOR 6));
        END
        WHEN any do BEGIN END
      END
      --
      IF (date_interv>0) THEN
      BEGIN
        return_date = CASE WHEN date_litera LIKE 'год%' THEN dateadd(date_interv YEAR TO treatdate)
                           WHEN date_litera LIKE 'мес%' THEN dateadd(date_interv MONTH TO treatdate)
                           WHEN date_litera LIKE 'нед%' THEN dateadd(date_interv week TO treatdate)
                           WHEN date_litera LIKE 'дн%'  THEN dateadd(date_interv DAY TO treatdate)
                           WHEN date_litera LIKE 'ден%' THEN dateadd(date_interv DAY TO treatdate)
                           ELSE NULL END;
      END
    END
 
    -- первой указана дата
    ELSE
    BEGIN
      --ret_date= current_date;
      tmp = SUBSTRING(txt FROM 1 FOR 10);
      BEGIN
        BEGIN
          return_date = CAST(tmp AS DATE);
        END
        WHEN any do BEGIN END
      END
 
      tmp = SUBSTRING(txt FROM 1 FOR 8);
      IF (return_date IS NULL) THEN
      BEGIN
        BEGIN
          return_date = CAST(tmp AS DATE);
        END
        WHEN any do BEGIN END
      END
 
    END
    --
    IF (return_date IS NOT NULL) THEN
      suspend;
    --
  END
END
-- end prc_datelist
--
BEGIN
  day_delay = 1; -- приглашать за 1 день минимум
  ttl = day_delay*24*60*60; -- напоминание держится (расчет на отправку сразу)
  uid = NULL;  
  -- филиал выполнения
  SELECT FIRST 1 f.filid, f.shortname, f.jid
    FROM repl$getgrpid g
    LEFT JOIN filials f ON f.grpid=g.repl$grpid
    INTO filial, fname, fjid;
  -- поиск структуры
  SELECT FIRST 1 structid FROM pricestructure
   WHERE bdate<= :return_date ORDER BY bdate DESC
    INTO structid;
  --
  FOR SELECT tx.pcode, tx.dcode, tx.return_date, tx.treatcode, cp.notificationid
        FROM prc_datelist tx
        LEFT JOIN clphones cp ON cp.pcode=tx.pcode
       WHERE tx.return_date BETWEEN CURRENT_DATE+ :day_delay AND CURRENT_DATE+1+ :day_delay
         AND COALESCE(cp.isdeleted,0)=0 AND cp.phonetype=21
  INTO pcode, dcode, return_date, treatcode, notify do
  BEGIN
    pushtext = NULL;   
    schid = NULL;
    dcode_send = NULL;
    -- беру специализацию
    SELECT FIRST 1 ws.speccode FROM treat t
      JOIN orderdet od ON od.orderno=t.orderno
      JOIN wschema ws ON ws.schid=od.schcode
     WHERE t.treatcode= :treatcode
       AND ws.consult=1
      INTO spec;
    -- смотрю наличие записей к доктору
    SELECT COUNT(*)
      FROM doctor d
      LEFT JOIN USER$WEB__DOCTOR_WSCHEMA(d.dcode, NULL, :spec) u ON 1=1
      LEFT JOIN schedule s ON s.workdate= :return_date AND s.dcode=d.dcode
     WHERE d.filial= :filial   
       AND COALESCE(d.locked,0)=0
       AND u.avalue=1
       AND s.pcode= :pcode
      INTO cnt;
    -- готовлю сообщение
    IF (cnt=0) THEN
    BEGIN
      -- поиск услуги (повторной)
      SELECT FIRST 1 ws.schid
        FROM wschema ws
        LEFT JOIN get_pricebyid(:return_date, 1, :filial, :fjid, 0, ws.schid) s ON 1=1
       WHERE ws.structid= :structid AND ws.speccode= :spec AND ws.repconsult=1  /* повторная */
         AND s.sprice>0
       ORDER BY rand()
        INTO schid;
      IF (schid IS NULL) THEN  
        SELECT FIRST 1 ws.schid
          FROM wschema ws
          LEFT JOIN get_pricebyid(:return_date, 1, :filial, :fjid, 0, ws.schid) s ON 1=1
         WHERE ws.structid= :structid AND ws.speccode= :spec AND ws.consult=1   /* первичная - повторной нет */
           AND s.sprice>0
         ORDER BY rand()
          INTO schid;
      -- поиск доктора, с учетом услуги
      SELECT FIRST 1 dcode, worktime FROM (
             SELECT d.dcode, COALESCE(ws.worktime, dic.rekvint3) worktime,
                    (ds.endhour*60+ds.endmin) - (ds.beghour*60+ds.begmin) alltime,
                    (SELECT SUM(CASE WHEN pcode>0 OR nazndis=1 THEN (s.fhour*60+s.fmin)-(s.bhour*60+s.bmin) ELSE 0 END )
                       FROM schedule s LEFT JOIN shedmarks m ON m.mrkid=s.tmstatus WHERE s.schedident=ds.schedident) denytime
               FROM doctor d                          
               JOIN doctshedule ds ON ds.dcode=d.dcode AND ds.wdate= :return_date  
               JOIN wschema ws ON ws.schid= :schid
               LEFT JOIN USER$WEB__DOCTOR_WSCHEMA(d.dcode, ws.schid) u ON 1=1
               LEFT JOIN dicinfo dic ON dic.refid=-10010011 AND dic.dcode=d.dcode AND dic.rekvint1=ws.schid
              WHERE d.filial= :filial AND COALESCE(d.locked,0)=0 AND u.avalue=1)
       WHERE alltime>denytime /* есть время для записи */
         AND alltime-denytime > worktime
       ORDER BY CASE WHEN :dcode = dcode THEN 0 ELSE 1 END,  alltime-denytime DESC, rand()
        INTO dcode_send, worktime;
      -- возможность формирования
      IF (schid IS NOT NULL AND dcode_send IS NOT NULL) THEN
      BEGIN
        -- данные
        SELECT d.dname, dep.depnum, dep.depname
          FROM doctor d
          LEFT JOIN departments dep ON dep.depnum=d.depnum
         WHERE  d.dcode= :dcode_send
          INTO dname, depnum, depname;
        -- формирование текста
        pushtext = '{"to":"'||notify||'","time_to_leave":'||ttl||',"notification":{';
        pushtext = pushtext||'"title":"Приглашение","body":"Приглашаем Вас на повторный прием к доктору.",';
        pushtext = pushtext||'"sound":"true","tag":"record"},';
        pushtext = pushtext||'"data":{"action":"record",';
        pushtext = pushtext||'"DCode":'||dcode_send||',"DName":"'||dname||'",';
        pushtext = pushtext||'"DepNum":'||depnum||',"DepName":"'||depname||'",';
        pushtext = pushtext||'"filial":'||filial||',"FName":"'||fname||'",';
        pushtext = pushtext||'"WorkDate":"'||(SELECT datestr FROM formatdate(:return_date))||'",';
        pushtext = pushtext||'"SchList":"'||schid||'","wtime":"'||worktime||'",';
        pushtext = pushtext||'}}';
        -- заполняем данные
        logid = gen_id(SMS_LOG_GEN, 1);
        smsid = gen_id(SMS_GEN, 1);
        smstypeid = 0;
        smsprofileid = 1;
        smsrefid = 70005;
        planbtime = dateadd(9 HOUR TO CAST(CURRENT_DATE AS TIMESTAMP));
        planftime = dateadd(18 HOUR TO CAST(return_date AS TIMESTAMP));
        -- вставляем запись
        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, :treatcode, :planbtime, :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
    --
    suspend;
  END
END

{
    "to":"<to>",
    "time_to_leave":"2119200",
    "notification":{
        "title":"Новое заключение",
        "body":"У Вас новое заключение доктора (результат исследования)",
        "sound":"true",
        "tag":"protocol"
    },
    "data":{
        "action":"protocol",
        "ProtocolID":99009900,
        "TreatCode":99009911,
        "DCode":99009922,
        "DName":"Фартух Д.А.",
        "ProtName":"Биохимия крови",
    }
}

{
    "to":"<to>",
    "time_to_leave":"2119200",
    "notification":{
        "title":"Оценка посещения",
        "body":"Пожалуйста, оцените прием доктора",
        "sound":"true",
        "tag":"mark"
    },
    "data":{
        "action":"mark",
        "SchedId":99009900,
        "DCode":99009922,
        "DName":"Фартух Д.А.",
        "filial":1,
        "FName":"Коломенский",
    }
}

CREATE OR ALTER TRIGGER USER$ORDERS_PUSH FOR ORDERS
active after INSERT OR UPDATE OR DELETE POSITION 99
AS
--
DECLARE logid TYPE OF COLUMN sms_log.logid;
DECLARE smsid TYPE OF COLUMN sms_logdet.smsid;
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 planbtime TYPE OF COLUMN sms_log.planbtime;
DECLARE planftime TYPE OF COLUMN sms_log.planftime;
DECLARE pushtext TYPE OF COLUMN sms_log.smstext;
DECLARE ttl INT;
--
DECLARE uid TYPE OF COLUMN doctor.dcode;
DECLARE notify TYPE OF COLUMN clphones.notificationid;
DECLARE summa TYPE OF COLUMN orderdet.schamount;
--
DECLARE schedid TYPE OF COLUMN schedule.schedid;
DECLARE filial TYPE OF COLUMN filials.filid;
DECLARE fname TYPE OF COLUMN filials.shortname;
DECLARE dcode TYPE OF COLUMN doctor.dcode;
DECLARE dname TYPE OF COLUMN doctor.dname;
BEGIN
  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;
 
    SELECT current_uid FROM s_session_info INTO uid;
    ttl = 7*24*60*60; -- 1 неделя  секундах
    -- вставка или обновление
    IF (inserting OR updating)THEN
    BEGIN
      -- проверяем наличие идентификатора
      notify = NULL;
      SELECT notificationid FROM clphones cp WHERE cp.pcode= NEW.pcode AND COALESCE(cp.isdeleted,0)=0 AND cp.phonetype=21
        INTO notify;
      notify = COALESCE(notify,'-');
      -- проверяем сумму
      SELECT SUM(COALESCE(schamount,0)) FROM orderdet WHERE orderno= NEW.ordercod
        INTO summa;
      -- проверяем возможность рассылки (SCHEDULE)
      SELECT MAX(COALESCE(s.schedid,0)) FROM treat t JOIN schedule s ON s.treatcode=t.treatcode
       WHERE t.orderno= NEW.ordercod AND s.workdate=CURRENT_DATE
        INTO schedid;
 
      -- объек добавляем/изменяем
      IF (notify != '-' AND summa > 0 AND schedid>0) THEN
      BEGIN
        -- формируем запись
        logid = NULL;
        SELECT logid FROM sms_log WHERE smsrefid=70008 AND pcode= NEW.pcode AND internalid= :schedid
          INTO logid;
        planbtime = dateadd(2 HOUR TO CURRENT_TIMESTAMP);  -- не ранее чем через 2 часа после приема
        planftime = dateadd(ttl SECOND TO planbtime);      -- не позже ttl (неделя), т.к. не имеет смысла
        IF (logid IS NULL) THEN
        BEGIN
          -- данные
          SELECT d.dcode, d.dname, f.filid, f.shortname FROM schedule s
            LEFT JOIN doctor d ON d.dcode=s.dcode
            LEFT JOIN filials f ON f.filid=s.filial
           WHERE s.schedid= :schedid
            INTO dcode, dname, filial, fname;
          -- формируем объект
          pushtext = '{"to":"'||notify||'","time_to_leave":'||ttl||',"notification":{';
          pushtext = pushtext||'"title":"Оценка посещения","body":"Пожалуйста, оцените прием доктора",';
          pushtext = pushtext||'"sound":"true","tag":"mark"},';
          pushtext = pushtext||'"data":{"action":"mark",';
          pushtext = pushtext||'"SchedId":'||schedid||',';
          pushtext = pushtext||'"DCode":'||dcode||',"DName":"'||dname||'",';
          pushtext = pushtext||'"filial":'||filial||',"FName":"'||fname||'",';
          pushtext = pushtext||'}}';
 
          -- заполняем данные
          logid = gen_id(SMS_LOG_GEN, 1);
          smsid = gen_id(SMS_GEN, 1);
          smstypeid = 0;
          smsprofileid = 1;
          smsrefid = 70008;
          -- вставляем запись
          INSERT INTO sms_log(logid,smstypeid,smsrefid,smsprofileid,filial,
                     createdate,smstext,pcode,uid,internalid,planbtime,planftime,state)
              VALUES(:logid, :smstypeid, :smsrefid, :smsprofileid, NEW.filial,
                     CURRENT_TIMESTAMP, :pushtext, NEW.pcode, :uid, :schedid, :planbtime, :planftime, 0);
          INSERT INTO sms_logdet(smsid,logid,phonetype,extsmsid,phprefix,phone,state,notificationid)
              VALUES(:smsid, :logid, 21, NULL, NULL, NULL, 0, :notify);
        END
        ELSE
        BEGIN
          -- обновляем запись
          UPDATE sms_log SET planbtime = :planbtime , planftime = :planftime
           WHERE logid= :logid;
          UPDATE sms_logdet SET state=0 WHERE logid= :logid AND state NOT IN (1,2);
        END
      END
 
      -- объект удаляем
      ELSE
      BEGIN
        logid = NULL;
        SELECT logid FROM sms_log WHERE smsrefid=70008 AND pcode= NEW.pcode AND internalid= :schedid
          INTO logid;
        -- деактивируем запись с ошибкой
        IF (logid IS NOT NULL AND schedid>0) THEN
          UPDATE sms_logdet SET state=-6 WHERE state=0 AND logid= :logid;
      END
    END
 
    -- удаление
    ELSE
    BEGIN
      -- вычисляем данные
      SELECT MAX(COALESCE(s.schedid,0)) FROM treat t JOIN schedule s ON s.treatcode=t.treatcode
       WHERE t.orderno= OLD.ordercod
        INTO schedid;
      logid = NULL;
      SELECT logid FROM sms_log WHERE smsrefid=70008 AND pcode= OLD.pcode AND internalid= :schedid
        INTO logid;
      -- деактивируем запись с ошибкой
      IF (logid IS NOT NULL AND schedid>0) THEN
        UPDATE sms_logdet SET state=-6 WHERE state=0 AND logid= :logid;
    END
  END
END

(при передаче объекта используется картинка с самым маленьким размером - 2)

{
    "to":"/topic/shares",
    "time_to_leave":"2119200",
    "notification":{
        "title":"Оценка посещения",
        "body":"Пожалуйста, оцените прием доктора",
        "sound":"true",
        "tag":"shares"
    },
    "data":{
        "action":"shares",
        "info":<InfoRecord>,
    }
}