EXECUTE block AS /* * Скрипт рассылки пуш-уведомлений для повторного визита пациентов * * Автор: Коробка А.А. */ -- константы DECLARE ttl INT; DECLARE day_delay INT; DECLARE datemsg DATE; DECLARE cnt INT; DECLARE uid TYPE OF COLUMN doctor.dcode; -- данные протокола DECLARE date_interv INT; DECLARE p1 INT; DECLARE txt TYPE OF COLUMN paramsinfo.valuetext; DECLARE tmp TYPE OF COLUMN paramsinfo.valuetext; DECLARE date_litera ttext16; -- данные пациента DECLARE pcode TYPE OF COLUMN clients.pcode; DECLARE notify TYPE OF COLUMN clphones.notificationid; -- данные приема DECLARE treatcode TYPE OF COLUMN treat.treatcode; DECLARE trdate TYPE OF COLUMN treat.treatdate; 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; BEGIN day_delay = 1; -- приглашать за 1 день минимум ttl = day_delay*24*60*60; -- напоминание держится (расчет на отправку сразу) uid = 0; -- филиал выполнения 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<= CURRENT_DATE+1+ :day_delay ORDER BY bdate DESC INTO structid; FOR SELECT p.pcode, p.treatdate, dcode, treatcode, LOWER(TRIM(valuetext)), cp.notificationid FROM paramsinfo p LEFT JOIN clphones cp ON cp.pcode=p.pcode 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 '%за результатами%' AND COALESCE(cp.isdeleted,0)=0 AND cp.phonetype=21 AND cp.notificationid>'' INTO pcode, trdate, dcode, treatcode, txt, notify do BEGIN datemsg = 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 datemsg = CASE WHEN date_litera LIKE 'год%' THEN dateadd(date_interv YEAR TO trdate) WHEN date_litera LIKE 'мес%' THEN dateadd(date_interv MONTH TO trdate) WHEN date_litera LIKE 'нед%' THEN dateadd(date_interv week TO trdate) WHEN date_litera LIKE 'дн%' THEN dateadd(date_interv DAY TO trdate) WHEN date_litera LIKE 'ден%' THEN dateadd(date_interv DAY TO trdate) ELSE NULL END; END END -- первой указана дата ELSE BEGIN tmp = SUBSTRING(txt FROM 1 FOR 10); BEGIN BEGIN datemsg = CAST(tmp AS DATE); END WHEN any do BEGIN END END tmp = SUBSTRING(txt FROM 1 FOR 8); IF (datemsg IS NULL) THEN BEGIN BEGIN datemsg = CAST(tmp AS DATE); END WHEN any do BEGIN END END END IF ( datemsg BETWEEN CURRENT_DATE+ :day_delay AND CURRENT_DATE+1+ :day_delay ) THEN BEGIN -- беру специализацию 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= :datemsg 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 pushtext = NULL; schid = NULL; dcode_send = NULL; SELECT FIRST 1 schid, dcode, worktime FROM (SELECT ws.schid, 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 wschema ws LEFT JOIN get_pricebyid(:datemsg, 1, :filial, :fjid, 0, ws.schid) s ON 1=1 LEFT JOIN doctspec dsp ON dsp.speccode= ws.speccode JOIN doctor d ON d.dcode=dsp.dcode AND d.filial= :filial LEFT JOIN USER$WEB__DOCTOR_WSCHEMA(d.dcode, ws.schid) u ON 1=1 JOIN doctshedule ds ON ds.dcode=d.dcode AND ds.wdate= :datemsg LEFT JOIN dicinfo dic ON dic.refid=-10010011 AND dic.dcode=d.dcode AND dic.rekvint1=ws.schid WHERE ws.structid= :structid AND ws.speccode= :spec AND ws.consult=1 AND s.sprice>0 AND u.avalue=1 ORDER BY COALESCE(repconsult,0) DESC /* повторная выше */, CASE WHEN d.dcode= :dcode THEN 1 ELSE 0 END DESC /* тот же доктор выше */) WHERE alltime>denytime /* есть время для записи */ AND alltime-denytime > worktime INTO schid, 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":"Приглашаем Вас на повторный прием на '||(SELECT datestr FROM formatdate(:datemsg))||' к доктору '||dname||'.",'; 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(:datemsg))||'",'; 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(datemsg 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 END END END