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
