EXECUTE block /* * Автор Коробка А.А. * * Оповещение о изменении состояния в журнале учета дубликатов карт */ AS DECLARE dcode TYPE OF COLUMN doctor.dcode; DECLARE dname TYPE OF COLUMN doctor.dname; DECLARE cnt INTEGER; DECLARE spresult ttext16k; -- DECLARE addtext ttext16k; DECLARE outtext ttext16k; DECLARE addheader ttext255; DECLARE outheader ttext255; BEGIN addheader = 'Сотрудники, создавшие дубликаты карт'; outheader = 'Сотрудники, убравшие дубликаты карт'; addtext = ''; outtext = ''; -- FOR EXECUTE statement ('select adddcode, count(*) from CL$DUBLE_JORNAL where adddate=current_date-1 group by 1') ON external '192.168.2.10:meta' AS USER 'CHEA' password 'PDNTP' INTO dcode, cnt do BEGIN IF (dcode IS NULL) THEN BEGIN dname = 'не определено'; END ELSE BEGIN SELECT dname||'['||filial||']' FROM doctor d WHERE d.dcode= :dcode INTO dname; END addtext = addtext||'<tr><td>'||dname||'</td><td>'||cnt||'</td></tr>'; END -- FOR EXECUTE statement ('select outdcode, count(*) from CL$DUBLE_JORNAL where outdate=current_date-1 group by 1') ON external '192.168.2.10:meta' AS USER 'CHEA' password 'PDNTP' INTO dcode, cnt do BEGIN IF (dcode IS NULL) THEN BEGIN dname = 'не определено'; END ELSE BEGIN SELECT dname||'['||filial||']' FROM doctor d WHERE d.dcode= :dcode INTO dname; END outtext = outtext||'<tr><td>'||dname||'</td><td>'||cnt||'</td></tr>'; END -- IF (addtext>'' OR outtext>'') THEN BEGIN spresult = '<html>За '||(SELECT * FROM formatdate(current_date-1))||' выявлено:'; IF (addtext>'')THEN BEGIN spresult = spresult||'<table><tr><th colspan=2><b>'||addheader||'</b></th></tr><tr><th>Сотрудник</th><th>Кол-во</th></tr>'||addtext||'</table><br><br>'; END IF (outtext>'')THEN BEGIN spresult = spresult||'<table><tr><th colspan=2><b>'||outheader||'</b></th></tr><tr><th>Сотрудник</th><th>Кол-во</th></tr>'||outtext||'</table><br>'; END spresult = spresult||'</html>'; INSERT INTO mail_log(MAILTYPEID,MAILSTATUS,MAILADDRESS,MAILDATE,CREATEDATE,MODIFYDATE,MAILTYPE,MAILCC, MAILHEADER,MAILTEXT) VALUES (990000002,0,'l.laktionova@mckolomen.ru,imfigurin@mckolomen.ru',CURRENT_DATE,CURRENT_TIMESTAMP,CURRENT_TIMESTAMP,1,'noreply@mckolomen.ru', 'Система оповещения состояния БД [Дубль. Оповещение]', :spresult); END END