EXECUTE block
RETURNS (
DCODE INTEGER,
DNAME VARCHAR(48),
BDATE DATE,
FDATE DATE,
RAZDEL INTEGER,
depname TYPE OF COLUMN buh$zarplata_calc.depname,
FILIAL INTEGER,
FNAME VARCHAR(48),
SECTID INTEGER,
CSECT VARCHAR(128),
SCHTYPEID INTEGER,
SCHTYPE VARCHAR(48),
SPECCODE INTEGER,
SPECNAME VARCHAR(255),
wday INT,
wdayname VARCHAR(24),
CALCTYPE INTEGER, -- ( 1 - выработка, 2 - списание, 3 - экспертиза СК, 7 - zaved line ) (+0 - invis / +10 - visible)
SUMMA_NACH_DOCT NUMERIC(10,2),
SUMMA_NACH NUMERIC(10,2),
schcount INTEGER,
PROC_MINUS NUMERIC(10,2),
PROC_ZP NUMERIC(10,2),
SUMMA_ZP NUMERIC(10,2),
DCODE_ZAVED INTEGER,
SUMMA_ZAVED NUMERIC(10,2)
)
AS
--
DECLARE inbdate DATE;
DECLARE infdate DATE;
DECLARE inList ttext16k;
DECLARE ch CHAR(1);
--
DECLARE doclist ttext16k;
DECLARE sqlText ttext16k;
DECLARE sqlText2 ttext16k;
DECLARE tmp_date DATE;
--
DECLARE wday0 INT;
DECLARE wday1 INT;
DECLARE wday2 INT;
DECLARE wday3 INT;
DECLARE wday4 INT;
DECLARE wday5 INT;
DECLARE wday6 INT;
DECLARE wday7 INT;
DECLARE wday11 INT;
DECLARE wday17 INT;
--
BEGIN
inbdate = '<bdate>';
infdate = '<fdate>';
inList = '<DoctorList>';
--
ch = ascii_char(39);
-- --
-- 1.1 формируем список докторов
-- --
sqlText = 'select d.dcode, 11 calctype from getlist('||ch||inList||ch||') l left join doctor d on d.dcode=l.id';
-- --
-- 1.2 добавляем к списку докторов для вычисления заведования
-- --
sqlText = sqlText||' union all select rekvint11, 1 from getlist('||ch||inList||ch||') l left join dicinfo d on d.rekvint10=l.id where refid=1111110 and d.rekvint11 not in ('||inList||')';
-- --
-- 2. добавляем периоды для вычисления (помесячно)
-- --
sqlText = 'select distinct t.dcode, calctype, doc.filial||'' ''||doc.dname, dep.depname, dep.razdel,
maxvalue(dt.bdate, outdate+1-extract(day from outdate)),
minvalue(dt.fdate, dateadd(1 month to outdate+1-extract(day from outdate))-1, coalesce(doc.lockdate-1,dt.fdate))
from ('||sqlText||') t
left join (select cast(? as timestamp) bdate, cast(? as timestamp) fdate from rdb$database) dt on 1=1
left join doctor doc on doc.dcode=t.dcode
left join departments dep on dep.depnum=doc.depnum
left join getdate(dt.bdate, dt.fdate) d on 1=1';
FOR EXECUTE statement (sqlText)(inbdate, infdate)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO dcode, calctype, dname, depname, razdel, bdate, fdate do
BEGIN
dcode_zaved=0;
EXECUTE statement ('select first 1 rekvint10 from dicinfo where refid=1111110 and ? in (rekvint10,rekvint11) and (disdate is null or disdate> ? ) and bdate <= ? ')(dcode,bdate,fdate)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO dcode_zaved;
EXECUTE statement ('insert into buh$zarplata_calc(dcode, calctype, dname, depname, razdel, bdate, fdate, dcode_zaved) values (?,?,?,?,?,?,?,?)')(dcode,calctype,dname,depname,razdel,bdate,fdate,dcode_zaved);
END
-- --
-- 3. вычисляем начисленные суммы
-- --
FOR SELECT dcode, dname, calctype, bdate, fdate, depname, razdel, dcode_zaved FROM buh$zarplata_calc
INTO dcode, dname, calctype, bdate, fdate, depname, razdel, dcode_zaved do
BEGIN
-- --
-- 3.0 Получение данных по дням недели по %
-- --
wday0=0; -- все
wday1=0; wday2=0; wday3=0; wday4=0; wday5=0; wday6=0; wday7=0; -- по дням
wday11=0; wday17=0; -- будни/вых
FOR EXECUTE statement ('select rekvint3 from dicinfo where refid=1111111
and bdate <= ? and (disdate is null or disdate>= ? )
and dcode = ? ')(bdate, fdate, dcode)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO wday do
BEGIN
IF (wday=0) THEN wday0=1;
IF (wday=1) THEN wday1=1;
IF (wday=2) THEN wday2=1;
IF (wday=3) THEN wday3=1;
IF (wday=4) THEN wday4=1;
IF (wday=5) THEN wday5=1;
IF (wday=6) THEN wday6=1;
IF (wday=7) THEN wday7=1;
IF (wday=11) THEN wday11=1;
IF (wday=17) THEN wday17=1;
END
-- --
-- 3.1 расчет всех наловых приходных операций
-- --
sqlText2 = '/* оплата нал */
select ''nal'' tables, i.filial, i.pcode, extract(weekday from coalesce(t.treatdate,i.paydate)) wday, t.amountcl_disc amountcl, t.orderno, t.doctype, i.paycode, i.dcode, null depnum, i.amountrub summa_pay
from incom i
left join treat t on i.orderno = t.orderno or i.extreatcode = t.treatcode
where i.paydate between ? and ?
and i.dcode= ?
and i.PayCode not in (5,6) union all
/* оплата карта */
select ''cred'', i.filial, i.pcode, extract(weekday from coalesce(t.treatdate,i.pmdate)) wday, t.amountcl_disc, t.orderno, t.doctype, 1, i.dcode, null, i.amountrub
from jppayments i
left join treat t on i.extreatcode = t.treatcode
where i.jid=100
and i.pmdate between ? and ?
and i.dcode= ?
and coalesce(i.bnalpay,0) = 0
and i.opertype not in (5,6) union
select ''cred'', i.filial, i.pcode, extract(weekday from coalesce(t.treatdate,i.lcdate)) wday, t.amountcl_disc, t.orderno, t.doctype, 1, i.dcode, null, i.amountrub
from losecredit i
inner join treat t on i.treatcode = t.treatcode
where i.lcdate between ? and ?
and i.dcode= ?
and i.paycode in (-1,-3,-2) union all
/* погашение из аванса (нал + карта) */
select ''avans'', i.filial, i.pcode, extract(weekday from coalesce(t.treatdate,i.paydate)) wday, t.amountcl_disc, t.orderno, t.doctype, i.typeoper, i.dcode, null, i.amountrub
from clavans i
inner join treat t on i.treatcode = t.treatcode
where i.paydate between ? and ?
and i.dcode= ?
and i.typeoper in (1,3)';
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sqlText = '';
-- --
-- 3.1.1 расчет прихода нала без программ и без франшизы для стоматологии
-- --
IF (razdel = 1) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sectid, csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
schtypeid, schtype,
wday,
sum(realcount), sum(schamount)
from (select ii.filial,
case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end realcount,
case when ii.orderno is null then summa_pay else od.schamount*ii.summa_pay/ii.amountcl end schamount,
sr.sectid, sr.shortname csect,
4 schtypeid, null schtype,
max(case when ws.speccode=36 then 35 /* парадонтология => стом терапия */
when ws.speccode between 55 and 59 then 15 /* лаборатория */
else ws.speccode end) over (partition by ii.orderno) speccode,
ii.wday,
(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1) spec_dep
from ('||sqlText2||') ii
join orderdet od on od.orderno=ii.orderno and od.doctype !=7 and coalesce(od.progservid,0)=0 and od.doctype<10
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema ws on od.schcode = ws.schid
left join sectorref sr on sr.sectid=4363
) t
left join filials f on f.filid=t.filial
left join speciality sp on sp.scode=case when t.speccode=33 then t.spec_dep else t.speccode end
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8, 9
order by t.filial, 4, 7, 6, 1';
END
-- --
-- 3.1.2 расчет прихода нала без программ и без франшизы для поликлиники
-- --
ELSE IF (razdel = 2) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sr.sectid, sr.shortname,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
case when coalesce(w.consult,0)=1 then 1
when coalesce(w.diagnost,0)=1 then 2
when coalesce(w.isoperation,0)=1 then 3
when ii.doctype in (13,14) then 5
else 4 end,
case when coalesce(w.consult,0)=1 then ''1. Косультация''
when coalesce(w.diagnost,0)=1 then ''2. Диагностика''
when coalesce(w.isoperation,0)=1 then ''3. Операция''
when ii.doctype in (13,14) then ''5. Лаборатория''
when ii.doctype is null then ''4. Опл. возвратов''
else ''4. Манипуляция'' end,
ii.wday,
sum(case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end),
sum(case when ii.orderno is null then summa_pay else schamount*summa_pay/ii.amountcl end)
from ('||sqlText2||') ii
join orderdet od on od.orderno=ii.orderno and od.doctype !=7 and coalesce(od.progservid,0)=0 and od.doctype<10
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema w on od.schcode = w.schid
left join speciality sp on (sp.scode=case when w.speccode between 55 and 59 then 15 else w.speccode end or
w.speccode is null and sp.depnum=dep.depnum and sp.mainspec=1 )
left join filials f on f.filid=ii.filial
left join sectorref sr on sr.sectid=4363
group by 1,2,3,4,5,6,7,8, 9
order by 1, 4, 7, 6, 1 ';
END
-- не определено
ELSE
BEGIN
EXECUTE statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||COALESCE(dname,'-')||' [dcode='||COALESCE(dcode,-1)||']') INTO sqlText;
EXECUTE PROCEDURE raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
END
FOR EXECUTE statement (sqlText)(bdate, fdate, dcode,
bdate, fdate, dcode,
bdate, fdate, dcode,
bdate, fdate, dcode)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach do
BEGIN
IF (wday=0) THEN wday=7;
--
IF (wday=1 AND wday1=1) THEN BEGIN END
ELSE IF (wday=2 AND wday2=1) THEN BEGIN END
ELSE IF (wday=3 AND wday3=1) THEN BEGIN END
ELSE IF (wday=4 AND wday4=1) THEN BEGIN END
ELSE IF (wday=5 AND wday5=1) THEN BEGIN END
ELSE IF (wday=6 AND wday6=1) THEN BEGIN END
ELSE IF (wday=7 AND wday7=1) THEN BEGIN END
ELSE IF (wday11=1 AND wday BETWEEN 1 AND 5) THEN wday = 11;
ELSE IF (wday17=1 AND wday BETWEEN 6 AND 7) THEN wday = 17;
ELSE wday=0;
--
EXECUTE statement('insert into buh$zarplata_calc (dcode, dname, depname, razdel, calctype, dcode_zaved, bdate, fdate, filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')(dcode, dname, depname, razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,wday,schcount,summa_nach);
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sqlText = '';
-- --
-- 3.1.3 расчет по франшизе, наловая приходная часть для стоматологии
-- --
IF (razdel = 1) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sectid, csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
schtypeid, schtype,
wday,
sum(realcount), sum(schamount)
from (select ii.filial,
case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end realcount,
case when ii.orderno is null then summa_pay else od.schamount*ii.summa_pay/ii.amountcl end schamount,
sr.sectid, sr.shortname csect,
4 schtypeid, null schtype,
max(case when ws.speccode=36 then 35 /* парадонтология => стом терапия */
when ws.speccode between 55 and 59 then 15 /* лаборатория */
else ws.speccode end) over (partition by ii.orderno) speccode,
ii.wday,
(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1) spec_dep
from ('||sqlText2||') ii
join orderdet od on od.orderno=ii.orderno and od.doctype =7
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema ws on od.schcode = ws.schid
left join sectorref sr on sr.sectid=994052961
) t
left join filials f on f.filid=t.filial
left join speciality sp on sp.scode=case when t.speccode=33 then t.spec_dep else t.speccode end
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8, 9
order by t.filial, 4, 7, 6, 1';
END
-- --
-- 3.1.4 расчет по франшизе, наловая приходная часть для поликлиики
-- --
ELSE IF (razdel = 2) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sr.sectid, sr.shortname,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
case when coalesce(w.consult,0)=1 then 1
when coalesce(w.diagnost,0)=1 then 2
when coalesce(w.isoperation,0)=1 then 3
when ii.doctype in (13,14) then 5
else 4 end,
case when coalesce(w.consult,0)=1 then ''1. Косультация''
when coalesce(w.diagnost,0)=1 then ''2. Диагностика''
when coalesce(w.isoperation,0)=1 then ''3. Операция''
when ii.doctype in (13,14) then ''5. Лаборатория''
when ii.doctype is null then ''4. Опл. возвратов''
else ''4. Манипуляция'' end,
wday,
sum(case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end),
sum(case when ii.orderno is null then summa_pay else schamount*summa_pay/ii.amountcl end)
from ('||sqlText2||') ii
join orderdet od on od.orderno=ii.orderno and od.doctype =7
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema w on od.schcode = w.schid
left join speciality sp on (sp.scode=case when w.speccode between 55 and 59 then 15 else w.speccode end or
w.speccode is null and sp.depnum=dep.depnum and sp.mainspec=1 )
left join filials f on f.filid=ii.filial
left join sectorref sr on sr.sectid=994052961
group by 1,2,3,4,5,6,7,8,9
order by 1, 4, 7, 6, 1 ';
END
-- не определено
ELSE
BEGIN
EXECUTE statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||COALESCE(dname,'-')||' [dcode='||COALESCE(dcode,-1)||']') INTO sqlText;
EXECUTE PROCEDURE raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
END
FOR EXECUTE statement (sqlText)(bdate, fdate, dcode,
bdate, fdate, dcode,
bdate, fdate, dcode,
bdate, fdate, dcode)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach do
BEGIN
csect = TRIM(csect)||' (нал)';
IF (wday=0) THEN wday=7;
--
IF (wday=1 AND wday1=1) THEN BEGIN END
ELSE IF (wday=2 AND wday2=1) THEN BEGIN END
ELSE IF (wday=3 AND wday3=1) THEN BEGIN END
ELSE IF (wday=4 AND wday4=1) THEN BEGIN END
ELSE IF (wday=5 AND wday5=1) THEN BEGIN END
ELSE IF (wday=6 AND wday6=1) THEN BEGIN END
ELSE IF (wday=7 AND wday7=1) THEN BEGIN END
ELSE IF (wday11=1 AND wday BETWEEN 1 AND 5) THEN wday = 11;
ELSE IF (wday17=1 AND wday BETWEEN 6 AND 7) THEN wday = 17;
ELSE wday=0;
--
EXECUTE statement('insert into buh$zarplata_calc (dcode, dname, depname, razdel, calctype, dcode_zaved, bdate, fdate, filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')(dcode, dname, depname, razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,wday,schcount,summa_nach);
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- --
-- 3.2 расчет всех наловых возвратов
-- --
sqlText2 = '/* возврат нал */
select ''v.nal'' tables, i.filial, i.pcode, extract(weekday from coalesce(t.treatdate,i.paydate)) wday, t.amountcl_disc amountcl, t.orderno, t.doctype, i.paycode, i.dcode, i.depnum, -i.amountrub summa_pay
from incom i
left join treat t on i.extreatcode = t.treatcode
where i.paydate between ? and ?
and i.dcode= ?
and i.paycode = 5 union all
/* возврат карта */
select ''v.cred'',i.filial,i.pcode, extract(weekday from coalesce(t.treatdate,i.pmdate)) wday,t.amountcl_disc,t.orderno, t.doctype,i.opertype,i.dcode,i.depnum,-i.amountrub
from jppayments i
left join treat t on i.extreatcode = t.treatcode
where i.jid = 100
and i.pmdate between ? and ?
and i.dcode= ?
and coalesce(i.bnalpay,0) = 0
and i.opertype = 5';
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sqlText = '';
-- --
-- 3.2.1 расчет возврата нала без программ и без франшизы для стоматологии
-- --
IF (razdel = 1) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sectid, csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
schtypeid, schtype,
wday,
sum(realcount), sum(schamount)
from (select ii.filial,
case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end realcount,
case when ii.orderno is null then summa_pay else od.schamount*ii.summa_pay/ii.amountcl end schamount,
sr.sectid, sr.shortname csect,
6 schtypeid, ''6. Опл. возвратов'' schtype,
max(case when ws.speccode=36 then 35 /* парадонтология => стом терапия */
when ws.speccode between 55 and 59 then 15 /* лаборатория */
else ws.speccode end) over (partition by ii.orderno) speccode,
wday,
(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1) spec_dep
from ('||sqlText2||') ii
left join orderdet od on od.orderno=ii.orderno and od.doctype !=7 and coalesce(od.progservid,0)=0 and od.doctype<10
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema ws on od.schcode = ws.schid
left join sectorref sr on sr.sectid=4363
) t
left join filials f on f.filid=t.filial
left join speciality sp on sp.scode=case when t.speccode=33 then t.spec_dep else coalesce(t.speccode,t.spec_dep) end
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8, 9
order by t.filial, 4, 7, 6, 1';
END
-- --
-- 3.2.2 расчет возврата нала без программ и без франшизы для поликлиники
-- --
ELSE IF (razdel = 2) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sr.sectid, sr.shortname,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
case when coalesce(w.consult,0)=1 then 1
when coalesce(w.diagnost,0)=1 then 2
when coalesce(w.isoperation,0)=1 then 3
when ii.doctype in (13,14) then 5
when ii.doctype is null then 6
else 4 end,
case when coalesce(w.consult,0)=1 then ''1. Косультация''
when coalesce(w.diagnost,0)=1 then ''2. Диагностика''
when coalesce(w.isoperation,0)=1 then ''3. Операция''
when ii.doctype in (13,14) then ''5. Лаборатория''
when ii.doctype is null then ''6. Опл. возвратов''
else ''4. Манипуляция'' end,
wday,
sum(case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end),
sum(case when ii.orderno is null then summa_pay else schamount*summa_pay/ii.amountcl end)
from ('||sqlText2||') ii
left join orderdet od on od.orderno=ii.orderno and od.doctype !=7 and coalesce(od.progservid,0)=0 and od.doctype<10
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema w on od.schcode = w.schid
left join speciality sp on (sp.scode=case when w.speccode between 55 and 59 then 15 else w.speccode end or
w.speccode is null and sp.depnum=dep.depnum and sp.mainspec=1 )
left join filials f on f.filid=ii.filial
left join sectorref sr on sr.sectid=4363
group by 1,2,3,4,5,6,7,8,9
order by 1, 4, 7, 6, 1 ';
END
-- не определено
ELSE
BEGIN
EXECUTE statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||COALESCE(dname,'-')||' [dcode='||COALESCE(dcode,-1)||']') INTO sqlText;
EXECUTE PROCEDURE raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
END
FOR EXECUTE statement (sqlText)(bdate, fdate, dcode,
bdate, fdate, dcode)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach do
BEGIN
IF (wday=0) THEN wday=7;
--
IF (wday=1 AND wday1=1) THEN BEGIN END
ELSE IF (wday=2 AND wday2=1) THEN BEGIN END
ELSE IF (wday=3 AND wday3=1) THEN BEGIN END
ELSE IF (wday=4 AND wday4=1) THEN BEGIN END
ELSE IF (wday=5 AND wday5=1) THEN BEGIN END
ELSE IF (wday=6 AND wday6=1) THEN BEGIN END
ELSE IF (wday=7 AND wday7=1) THEN BEGIN END
ELSE IF (wday11=1 AND wday BETWEEN 1 AND 5) THEN wday = 11;
ELSE IF (wday17=1 AND wday BETWEEN 6 AND 7) THEN wday = 17;
ELSE wday=0;
--
EXECUTE statement('insert into buh$zarplata_calc (dcode, dname, depname, razdel, calctype, dcode_zaved, bdate, fdate, filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')(dcode, dname, depname, razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,wday,schcount,summa_nach);
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- комментировано
-- если идет возврат аванса, то не будет указания доктора, соттветственно алгоритм не выполнится.
-- если идет возварт денег, то записей в orderdet не будет.
-- соответственно нет смысла выполнять алгоритм, т.к. он выполнен выше в шаге 3.2.1/3.2.2
/*sqlText = '';
-- --
-- 3.2.3 расчет по франшизе, наловая возвратная часть для стоматологии
-- --
if (razdel = 1) then
begin
sqlText = 'select f.filid, f.shortname,
sectid, csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
schtypeid, schtype,
sum(realcount), sum(schamount)
from (select ii.filial,
case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end realcount,
case when ii.orderno is null then summa_pay else od.schamount*ii.summa_pay/ii.amountcl end schamount,
sr.sectid, sr.shortname csect,
6 schtypeid, null schtype,
max(case when ws.speccode=36 then 35 /* парадонтология => стом терапия * /
when ws.speccode between 55 and 59 then 15 /* лаборатория * /
else ws.speccode end) over (partition by ii.orderno) speccode,
(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1) spec_dep
from ('||sqlText2||') ii
left join orderdet od on od.orderno=ii.orderno and od.doctype =7
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema ws on od.schcode = ws.schid
left join sectorref sr on sr.sectid=994052961
) t
left join filials f on f.filid=t.filial
left join speciality sp on sp.scode=case when t.speccode=33 then t.spec_dep else t.speccode end
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8
order by t.filial, 4, 7, 6, 1';
end
-- --
-- 3.2.4 расчет по франшизе, наловая возвратная часть для поликлиики
-- --
else if (razdel = 2) then
begin
sqlText = 'select f.filid, f.shortname,
sr.sectid, sr.shortname,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
case when coalesce(w.consult,0)=1 then 1
when coalesce(w.diagnost,0)=1 then 2
when coalesce(w.isoperation,0)=1 then 3
when ii.doctype in (13,14) then 5
when ii.doctype is null then 6
else 4 end,
case when coalesce(w.consult,0)=1 then ''1. Косультация''
when coalesce(w.diagnost,0)=1 then ''2. Диагностика''
when coalesce(w.isoperation,0)=1 then ''3. Операция''
when ii.doctype in (13,14) then ''5. Лаборатория''
when ii.doctype is null then ''6. Опл. возвратов''
else ''4. Манипуляция'' end,
sum(case when ii.tables=''cred'' and ii.paycode=-1 then 0 when ii.orderno is null then 1 else od.realcount end),
sum(case when ii.orderno is null then summa_pay else schamount*summa_pay/ii.amountcl end)
from ('||sqlText2||') ii
left join orderdet od on od.orderno=ii.orderno and od.doctype =7
left join doctor d on d.dcode=ii.dcode
left join departments dep on dep.depnum=coalesce(ii.depnum, d.depnum)
left join wschema w on od.schcode = w.schid
left join speciality sp on (sp.scode=case when w.speccode between 55 and 59 then 15 else w.speccode end or
w.speccode is null and sp.depnum=dep.depnum and sp.mainspec=1 )
left join filials f on f.filid=ii.filial
left join sectorref sr on sr.sectid=994052961
group by 1,2,3,4,5,6,7,8
order by 1, 4, 7, 6, 1 ';
end
-- не определено
else
begin
execute statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||coalesce(dname,'-')||' [dcode='||coalesce(dcode,-1)||']') into sqlText;
execute procedure raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
end
for execute statement (sqlText)(bdate, fdate, dcode,
bdate, fdate, dcode)
on external '192.168.2.47:MED99' as user 'CHEA' password 'PDNTP'
into filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, schcount, summa_nach do
begin
csect = trim(csect)||' (нал)';
execute statement('insert into buh$zarplata_calc (dcode, dname, depname, razdel, calctype, dcode_zaved, bdate, fdate, filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, schcount, summa_nach)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')(dcode, dname, depname, razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,schcount,summa_nach);
end */
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sqlText = '';
-- --
-- 3.3.1 расчет по программам для стоматологии
-- --
IF (razdel = 1) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sectid, csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
schtypeid, schtype,
wday,
sum(realcount), sum(schamount)
from (select t.filial, od.realcount, od.schamount_a*clo.schamount/clo.schamount_a schamount,
sr.sectid, sr.shortname||case when jp.agrtype=1 then ''(''||lp.shortname||'')'' else '''' end csect,
4 schtypeid, null schtype,
max(case when ws.speccode=36 then 35 /* парадонтология => стом терапия */
when ws.speccode between 55 and 59 then 15 /* лаборатория */
else ws.speccode end) over (partition by t.treatcode) speccode,
extract(weekday from t.treatdate) wday,
(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1) spec_dep
from treat t
left join jpagreement jp on jp.agrid=t.jid
left join jplists lp on lp.lid=t.lstid
left join orderdet od on t.orderno = od.orderno
left join wschema ws on od.schcode = ws.schid
left join doctor d on d.dcode=t.dcode
left join departments dep on dep.depnum=coalesce(t.depnum, d.depnum)
left join sectorref sr on sr.sectid=jp.sectid
left join clservice cl on cl.id=od.progservid
left join discountsprav ds on ds.did=cl.did
left join treat clt on clt.treatcode=cl.treatcode
left join orderdet clo on clo.orderno=clt.orderno and ds.schid=clo.schcode
where od.doctype < 10
and coalesce(od.progservid,0)>0
and t.dcode= ?
and t.treatdate between ? and ?
) t
left join filials f on f.filid=t.filial
left join speciality sp on sp.scode=case when t.speccode=33 then t.spec_dep else t.speccode end
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8, 9
order by t.filial, 4, 7, 6, 1';
END
-- --
-- 3.3.2 расчет по программам для поликлиики
-- --
ELSE IF (razdel = 2) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sr.sectid, sr.shortname,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
case when coalesce(w.consult,0)=1 then 1
when coalesce(w.diagnost,0)=1 then 2
when coalesce(w.isoperation,0)=1 then 3
when t.doctype in (13,14) then 5
else 4 end,
case when coalesce(w.consult,0)=1 then ''1. Косультация''
when coalesce(w.diagnost,0)=1 then ''2. Диагностика''
when coalesce(w.isoperation,0)=1 then ''3. Операция''
when t.doctype in (13,14) then ''5. Лаборатория''
else ''4. Манипуляция'' end,
extract(weekday from t.treatdate) wday,
sum(od.realcount), sum(od.schamount_a*clo.schamount/clo.schamount_a) from treat t
left join orderdet od on t.orderno = od.orderno
left join wschema w on od.schcode = w.schid
left join speciality sp on sp.scode=case when w.speccode between 55 and 59 then 15 else w.speccode end
left join filials f on f.filid=t.filial
left join sectorref sr on sr.sectid=4363
left join clservice cl on cl.id=od.progservid
left join discountsprav ds on ds.did=cl.did
left join treat clt on clt.treatcode=cl.treatcode
left join orderdet clo on clo.orderno=clt.orderno and ds.schid=clo.schcode
where od.doctype < 10
and coalesce(od.progservid,0)>0
and t.dcode= ?
and t.treatdate between ? and ?
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8, 9
order by t.filial, 4, 7, 6, 1';
END
-- не определено
ELSE
BEGIN
EXECUTE statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||COALESCE(dname,'-')||' [dcode='||COALESCE(dcode,-1)||']') INTO sqlText;
EXECUTE PROCEDURE raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
END
FOR EXECUTE statement (sqlText)(dcode, bdate, fdate)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach do
BEGIN
csect = TRIM(csect)||' (прг)';
IF (wday=0) THEN wday=7;
--
IF (wday=1 AND wday1=1) THEN BEGIN END
ELSE IF (wday=2 AND wday2=1) THEN BEGIN END
ELSE IF (wday=3 AND wday3=1) THEN BEGIN END
ELSE IF (wday=4 AND wday4=1) THEN BEGIN END
ELSE IF (wday=5 AND wday5=1) THEN BEGIN END
ELSE IF (wday=6 AND wday6=1) THEN BEGIN END
ELSE IF (wday=7 AND wday7=1) THEN BEGIN END
ELSE IF (wday11=1 AND wday BETWEEN 1 AND 5) THEN wday = 11;
ELSE IF (wday17=1 AND wday BETWEEN 6 AND 7) THEN wday = 17;
ELSE wday=0;
--
EXECUTE statement('insert into buh$zarplata_calc (dcode,dname,depname,razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,wday,schcount,summa_nach)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')(dcode,dname,depname,razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,wday,schcount,summa_nach);
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sqlText = '';
-- --
-- 3.4.1 расчет страховых (остальных, без прямой оплаты) для стоматологии
-- --
IF (razdel = 1) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sectid, csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
schtypeid, schtype,
wday,
sum(realcount), sum(schamount)
from (select t.filial, realcount, schamount,
sr.sectid, trim(sr.shortname)||case when jp.agrtype=1 then '' (''||lp.shortname||'')'' when sr.sectid=994052961 then '' (дмс)'' else '''' end csect,
4 schtypeid, null schtype,
max(case when ws.speccode=36 then 35 /* парадонтология => стом терапия */
when ws.speccode between 55 and 59 then 15 /* лаборатория */
else ws.speccode end) over (partition by t.treatcode) speccode,
extract(weekday from t.treatdate) wday,
(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1) spec_dep
from treat t
left join jpagreement jp on jp.agrid=t.jid
left join jplists lp on lp.lid=t.lstid
left join orderdet od on t.orderno = od.orderno
left join wschema ws on od.schcode = ws.schid
left join doctor d on d.dcode=t.dcode
left join departments dep on dep.depnum=coalesce(t.depnum, d.depnum)
left join sectorref sr on sr.sectid=jp.sectid
where od.doctype > 10
and t.dcode= ?
and t.treatdate between ? and ?
) t
left join filials f on f.filid=t.filial
left join speciality sp on sp.scode=case when t.speccode=33 then t.spec_dep else t.speccode end
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8, 9
order by t.filial, 4, 7, 6, 1';
END
-- --
-- 3.4.2 расчет страховых (остальных, без прямой оплаты) для поликлиники
-- --
ELSE IF (razdel = 2) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sr.sectid, trim(sr.shortname)||case when jp.agrtype=1 then '' (''||lp.shortname||'')'' when sr.sectid=994052961 then '' (дмс)'' else '''' end,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
case when coalesce(ws.consult,0)=1 then 1
when coalesce(ws.diagnost,0)=1 then 2
when coalesce(ws.isoperation,0)=1 then 3
when t.doctype in (13,14) then 5
else 4 end,
case when coalesce(ws.consult,0)=1 then ''1. Косультация''
when coalesce(ws.diagnost,0)=1 then ''2. Диагностика''
when coalesce(ws.isoperation,0)=1 then ''3. Операция''
when t.doctype in (13,14) then ''5. Лаборатория''
else ''4. Манипуляция'' end,
extract(weekday from t.treatdate) wday,
sum(od.realcount), sum(od.schamount) from treat t
left join jpagreement jp on jp.agrid=t.jid
left join jplists lp on lp.lid=t.lstid
left join orderdet od on t.orderno = od.orderno
left join wschema ws on od.schcode = ws.schid
left join speciality sp on sp.scode=case when ws.speccode between 55 and 59 then 15 else ws.speccode end
left join filials f on f.filid=t.filial
left join sectorref sr on sr.sectid=jp.sectid
where od.doctype > 10
and t.dcode= ?
and t.treatdate between ? and ?
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8, 9
order by t.filial, 4, 7, 6, 1';
END
-- не определено
ELSE
BEGIN
EXECUTE statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||COALESCE(dname,'-')||' [dcode='||COALESCE(dcode,-1)||']') INTO sqlText;
EXECUTE PROCEDURE raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
END
FOR EXECUTE statement (sqlText)(dcode, bdate, fdate)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, wday, schcount, summa_nach do
BEGIN
IF (wday=0) THEN wday=7;
--
IF (wday=1 AND wday1=1) THEN BEGIN END
ELSE IF (wday=2 AND wday2=1) THEN BEGIN END
ELSE IF (wday=3 AND wday3=1) THEN BEGIN END
ELSE IF (wday=4 AND wday4=1) THEN BEGIN END
ELSE IF (wday=5 AND wday5=1) THEN BEGIN END
ELSE IF (wday=6 AND wday6=1) THEN BEGIN END
ELSE IF (wday=7 AND wday7=1) THEN BEGIN END
ELSE IF (wday11=1 AND wday BETWEEN 1 AND 5) THEN wday = 11;
ELSE IF (wday17=1 AND wday BETWEEN 6 AND 7) THEN wday = 17;
ELSE wday=0;
--
EXECUTE statement('insert into buh$zarplata_calc (dcode,dname,depname,razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,wday,schcount,summa_nach)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)')(dcode,dname,depname,razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,wday,schcount,summa_nach);
END
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
END
-- --
-- 4 считаю акты списания
-- --
FOR SELECT DISTINCT dcode, dname, depname, razdel, bdate, fdate, filial,dcode_zaved, calctype FROM buh$zarplata_calc
WHERE razdel =1 /* только стоматология */
AND filial IS NULL
AND calctype IN (1,11)
INTO dcode, dname, depname, razdel, bdate, fdate, filial, dcode_zaved, calctype do
BEGIN
sqlText = '';
-- --
-- 4.1 расчет списания для стоматологии
-- --
IF (razdel = 1) THEN
BEGIN
sqlText = 'select iv.filial, ''Акт списания'' fname,
sr.sectid, trim(sr.shortname)||case when sr.sectid=994052961 then '' (дмс)'' else '''' end csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
4 schtypeid, it.invsubtype||''. ''||it.invname schtype,
sum(mi.xfromnum*1.0), -sum(mi.xfromprice*1.0)
from m_invoice iv
join m_items mi on mi.invcode=iv.invcode
left join m_invtype it on it.invtype=iv.invtype and it.invmode=iv.invmode and it.invsubtype=iv.invsubtype
left join doctor d on d.dcode=iv.doccode
left join departments dep on dep.depnum= d.depnum
left join sectorref sr on sr.sectid= coalesce(completefilial,4363)
left join speciality sp on sp.scode=coalesce(nullif(iv.importid,33),(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1))
where iv.invtype=1 and iv.invmode=1
and iv.doccode= ?
and iv.invdate between ? and ?
and mi.state=1
group by 1, 2, 3, 4, 5, 6, 7, 8
order by 4, 7, 6, 1';
END
-- --
-- 4.2 расчет списания для поликлиники
-- --
ELSE IF (razdel = 2) THEN
BEGIN
sqlText = 'select first 1 null, null, null, null, null, null, null, null, null, null from (select dcode from doctor where dcode= ? ) d left join getdate(?,?) on 1=1';
END
-- не определено
ELSE
BEGIN
EXECUTE statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||COALESCE(dname,'-')||' [dcode='||COALESCE(dcode,-1)||']') INTO sqlText;
EXECUTE PROCEDURE raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
END
FOR EXECUTE statement (sqlText)(dcode, bdate, fdate)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, schcount, summa_nach do
BEGIN
EXECUTE statement('insert into buh$zarplata_calc (dcode,dname,depname,razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,schcount,summa_nach,proc_zp)
values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,1)')(dcode,dname,depname,razdel,calctype+1,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,schcount,summa_nach);
END
END
-- --
-- 5. считаю акты экспертизы
-- --
FOR SELECT DISTINCT dcode, dname, depname, razdel, bdate, fdate,dcode_zaved, calctype FROM buh$zarplata_calc WHERE calctype IN (1,11) AND filial IS NULL
INTO dcode, dname, depname, razdel, bdate, fdate,dcode_zaved, calctype do
BEGIN
sqlText = '';
-- --
-- 5.1 расчет страховых (остальных, без прямой оплаты) для стоматологии
-- --
IF (razdel = 1) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sectid, csect,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
schtypeid, schtype,
sum(realcount), -sum(schamount)
from (select t.filial,
case when p.schnum=od.schnum then ert.schcount else 0 end realcount,
case when p.schnum=od.schnum then ert.amountrub else 0 end schamount,
sr.sectid, trim(sr.shortname)||case when jp.agrtype=1 then '' (''||lp.shortname||'')'' when sr.sectid=994052961 then '' (дмс)'' else '''' end csect,
4 schtypeid, null schtype,
max(case when ws.speccode=36 then 35 /* парадонтология => стом терапия */
when ws.speccode between 55 and 59 then 15 /* лаборатория */
else ws.speccode end) over (partition by t.treatcode) speccode,
(select spp.scode from speciality spp where spp.depnum=dep.depnum and mainspec=1) spec_dep
from jaccounts jc
join jpexpertreportdet ert on ert.accid=jc.accid
left join jpaymdet p on ert.pdid = p.pdid
left join treat t on p.treatcode=t.treatcode
left join orderdet odx on odx.orderno=t.orderno and p.schnum=odx.schnum
left join orderdet od on od.orderno=t.orderno
left join jpagreement jp on jp.agrid=p.jid
left join jplists lp on lp.lid=t.lstid
left join wschema ws on p.schid = ws.schid
left join doctor d on d.dcode=t.dcode
left join departments dep on dep.depnum=coalesce(t.depnum, d.depnum)
left join sectorref sr on sr.sectid=jp.sectid
where jc.acctype=11
and t.dcode= ?
and jc.accdate between ? and ?
) t
left join filials f on f.filid=t.filial
left join speciality sp on sp.scode=case when t.speccode=33 then t.spec_dep else t.speccode end
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8
order by t.filial, 4, 7, 6, 1';
END
-- --
-- 5.2 расчет страховых (остальных, без прямой оплаты) для поликлиники
-- --
ELSE IF (razdel = 2) THEN
BEGIN
sqlText = 'select f.filid, f.shortname,
sr.sectid, trim(sr.shortname)||case when jp.agrtype=1 then '' (''||lp.shortname||'')'' when sr.sectid=994052961 then '' (дмс)'' else '''' end,
sp.scode, case when sp.scode=15 then ''Лаборатория'' else sp.sname end,
case when coalesce(ws.consult,0)=1 then 1
when coalesce(ws.diagnost,0)=1 then 2
when coalesce(ws.isoperation,0)=1 then 3
when t.doctype in (13,14) then 5
else 4 end,
case when coalesce(ws.consult,0)=1 then ''1. Косультация''
when coalesce(ws.diagnost,0)=1 then ''2. Диагностика''
when coalesce(ws.isoperation,0)=1 then ''3. Операция''
when t.doctype in (13,14) then ''5. Лаборатория''
else ''4. Манипуляция'' end,
sum(ert.schcount), -sum(ert.amountrub) from jaccounts jc
left join jaccounts jcp on jcp.accid=jc.accparent
join jpexpertreportdet ert on ert.accid=jc.accid
left join jpaymdet p on ert.pdid = p.pdid
left join treat t on p.treatcode=t.treatcode
left join jplists lp on lp.lid=t.lstid
left join jpagreement jp on jp.agrid=p.jid
left join wschema ws on p.schid = ws.schid
left join filials f on f.filid=p.filial
left join sectorref sr on sr.sectid=jp.sectid
left join speciality sp on sp.scode=case when ws.speccode between 55 and 59 then 15 else ws.speccode end
where jc.acctype=11
and t.dcode= ?
and jc.accdate between ? and ?
group by t.filial, 1, 2, 3, 4, 5, 6, 7, 8
order by t.filial, 4, 7, 6, 1';
END
-- не определено
ELSE
BEGIN
EXECUTE statement ('select OUTSTR from GET_TRANSLIT(?)')('Не определен раздел медицины / отделение для доктора '||COALESCE(dname,'-')||' [dcode='||COALESCE(dcode,-1)||']') INTO sqlText;
EXECUTE PROCEDURE raise_exception('=>'||ascii_char(13)||ascii_char(10)||'=>'||sqlText||ascii_char(13)||ascii_char(10)||'=>');
END
FOR EXECUTE statement (sqlText)(dcode, bdate, fdate)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO filial, fname, sectid, csect, speccode, specname, schtypeid, schtype, schcount, summa_nach do
BEGIN
EXECUTE statement('insert into buh$zarplata_calc (dcode,dname,depname,razdel,calctype,dcode_zaved,bdate,fdate,filial,fname,sectid,csect,speccode,specname,schtypeid,schtype,schcount,summa_nach,proc_zp)
values (?,?,?,?,?,?,?,?,?,''Акт экспертизы'',?,?,?,?,?,?,?,?,1)')(dcode,dname,depname,razdel,calctype+2,dcode_zaved,bdate,fdate,filial,sectid,csect,speccode,specname,schtypeid,schtype,schcount,summa_nach);
END
END
-- --
-- 6. убираем нулевые начисления
-- --
DELETE FROM buh$zarplata_calc WHERE COALESCE(summa_nach,0)=0;
-- --
-- 7. выставляю 1%, там где будет ЗП
-- --
FOR SELECT DISTINCT dcode, bdate, fdate, filial, sectid, schtypeid, COALESCE(speccode,0) FROM buh$zarplata_calc WHERE calctype IN (1,11)
INTO dcode, bdate, fdate, filial, sectid, schtypeid, speccode do
BEGIN
proc_zp = NULL;
EXECUTE statement ('select first 1 rekvfloat from (select rekvfloat, abs(sign(coalesce(dcode,0))) dcode,
abs(sign(coalesce(rekvint1,0))) filial,
abs(sign(coalesce(rekvint2,0))) schtype,
abs(sign(coalesce(rekvint4,0))) speccode,
abs(sign(coalesce(rekvint5,0))) sectid
from dicinfo where refid=1111111
and bdate <= ?
and (disdate is null or disdate>= ? )
and coalesce(dcode, -1) in ( ? ,-1)
and coalesce(rekvint1,-1) in ( ? , -1)
and coalesce(rekvint2,-1) in ( ? , -1)
and coalesce(rekvint4,-1) in ( ? , -1)
and coalesce(rekvint5,-1) in ( ? , -1) )
order by case when dcode+filial+schtype+speccode =0 and sectid>0 then 230 else 0 end +
case when schtype>0 and dcode>0 then 200
when filial+speccode+sectid+dcode =0 and schtype>0 then 110
else 0 end +
dcode*90 + sectid*30 + filial + schtype + speccode desc')
(fdate,bdate,dcode,filial,schtypeid,speccode,sectid)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO proc_zp;
proc_zp = abs(sign(COALESCE(proc_zp,0)));
EXECUTE statement ('update buh$zarplata_calc set proc_zp= ? where calctype in (1,11) and dcode= ?
and bdate= ? and fdate= ? and filial= ? and sectid= ? and schtypeid= ? and coalesce(speccode,0)= ? ')(proc_zp,dcode,bdate,fdate,filial,sectid,schtypeid,speccode);
END
-- --
-- 8. Считаю общую выработку по доктору
-- --
FOR SELECT dcode, bdate, fdate, SUM(proc_zp*summa_nach) FROM buh$zarplata_calc GROUP BY dcode, bdate, fdate
INTO dcode, bdate, fdate, summa_nach_doct do
EXECUTE statement ('update buh$zarplata_calc set summa_nach_doct= ? where dcode= ? and bdate= ? and fdate= ? ')(summa_nach_doct,dcode,bdate,fdate);
-- --
-- 9.1. Сталю %ЗП на основе таблицы по % и выработки
-- --
FOR SELECT DISTINCT dcode, bdate, fdate, filial, sectid, schtypeid, COALESCE(speccode,0), summa_nach_doct, wday FROM buh$zarplata_calc WHERE proc_zp>0 AND calctype IN (1,11)
INTO dcode, bdate, fdate, filial, sectid, schtypeid, speccode, summa_nach_doct,wday do
BEGIN
proc_zp = NULL;
EXECUTE statement ('select first 1 rekvfloat from (select rekvfloat, abs(sign(coalesce(dcode,0))) dcode,
abs(sign(coalesce(rekvint1,0))) filial,
abs(sign(coalesce(rekvint2,0))) schtype,
abs(sign(coalesce(rekvint4,0))) speccode,
abs(sign(coalesce(rekvint5,0))) sectid,
rekvint3 wday
from dicinfo where refid=1111111
and bdate <= ?
and (disdate is null or disdate>= ? )
and coalesce(dcode, -1) in ( ? ,-1)
and coalesce(rekvint1,-1) in ( ? , -1)
and coalesce(rekvint2,-1) in ( ? , -1)
and coalesce(rekvint4,-1) in ( ? , -1)
and coalesce(rekvint5,-1) in ( ? , -1)
and (rekvint8 is null or rekvint8<= ? )
and (rekvint9 is null or rekvint9> ? ) )
order by case when dcode+filial+schtype+speccode =0 and sectid>0 then 230 else 0 end +
case when schtype>0 and dcode>0 then 200
when filial+speccode+sectid+dcode =0 and schtype>0 then 110
else 0 end +
dcode*90 + sectid*30 + filial + schtype + speccode desc,
case when wday = ? then 0 else 1 end,
rekvfloat desc')
(fdate,bdate,dcode,filial,schtypeid,speccode,sectid,summa_nach_doct,summa_nach_doct,wday)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO proc_zp;
proc_zp = COALESCE(proc_zp,0);
EXECUTE statement ('update buh$zarplata_calc set proc_zp= ? where calctype in (1,11) and dcode= ?
and bdate= ? and fdate= ? and filial= ? and sectid= ? and schtypeid= ? and coalesce(speccode,0)= ? and coalesce(wday,0)= ? ')(proc_zp,dcode,bdate,fdate,filial,sectid,schtypeid,speccode,wday);
END
-- --
-- 9.2. Сталю % на снятия на основе таблицы по % и выработки
-- --
FOR SELECT DISTINCT dcode, bdate, fdate, filial, sectid, schtypeid, COALESCE(speccode,0), summa_nach_doct FROM buh$zarplata_calc WHERE proc_zp>0 AND calctype IN (2,3,12,13)
INTO dcode, bdate, fdate, filial, sectid, schtypeid, speccode, summa_nach_doct do
BEGIN
proc_zp = NULL;
EXECUTE statement ('select first 1 rekvfloat from (select rekvfloat, abs(sign(coalesce(dcode,0))) dcode,
abs(sign(coalesce(rekvint1,0))) filial,
abs(sign(coalesce(rekvint2,0))) schtype,
abs(sign(coalesce(rekvint4,0))) speccode,
abs(sign(coalesce(rekvint5,0))) sectid
from dicinfo where refid=1111111
and bdate <= ?
and (disdate is null or disdate>= ? )
and coalesce(dcode,-1) in ( ? )
and coalesce(rekvint1,-1) in ( ? , -1)
and coalesce(rekvint2,-1) in ( ? , -1)
and coalesce(rekvint4,-1) in ( ? , -1)
and coalesce(rekvint5,-1) in ( ? , -1)
and (rekvint8 is null or rekvint8<= ? )
and (rekvint9 is null or rekvint9> ? ) )
order by case when dcode+filial+schtype+speccode =0 and sectid>0 then 230 else 0 end +
case when schtype>0 and dcode>0 then 200
when filial+speccode+sectid+dcode =0 and schtype>0 then 110
else 0 end +
dcode*90 + sectid*30 + filial + schtype + speccode desc,
rekvfloat asc')
(fdate,bdate,dcode,filial,schtypeid,speccode,sectid,summa_nach_doct,summa_nach_doct)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO proc_zp;
proc_zp = COALESCE(proc_zp,0);
EXECUTE statement ('update buh$zarplata_calc set proc_zp= ? where calctype in (2,3,12,13) and dcode= ?
and bdate= ? and fdate= ? and filial= ? and sectid= ? and schtypeid= ? and coalesce(speccode,0)= ? ')(proc_zp,dcode,bdate,fdate,filial,sectid,schtypeid,speccode);
END
-- --
-- 10. Ставлю % уменьшения на основе филиал|категория|razdel|специализация
-- --
EXECUTE statement ('update buh$zarplata_calc set proc_minus= 0');
FOR SELECT DISTINCT bdate, fdate, filial, sectid, razdel, speccode FROM buh$zarplata_calc
INTO bdate, fdate, filial, sectid, razdel, speccode do
BEGIN
proc_minus = NULL;
EXECUTE statement ('select first 1 rekvfloat from dicinfo where refid=1111109
and bdate <= ?
and (disdate is null or disdate> ?)
and (rekvint1 is null or rekvint1 = ? )
and (rekvint5 is null or rekvint5 = ? )
and (rekvint6 is null or rekvint6 = ? )
and (rekvint4 is null or rekvint4 = ? )
order by sign(coalesce(rekvint1,0))+
sign(coalesce(rekvint4,0))+
sign(coalesce(rekvint5,0))+
sign(coalesce(rekvint6,0)) desc,
rekvfloat desc')(fdate,bdate,filial,sectid,razdel,speccode)
ON external '192.168.2.47:MED99' AS USER 'CHEA' password 'PDNTP'
INTO proc_minus;
proc_minus = COALESCE(proc_minus,0);
EXECUTE statement ('update buh$zarplata_calc set proc_minus= ? where bdate= ? and fdate= ? and filial= ? and sectid= ? and razdel= ? and speccode = ? ')(proc_minus,bdate,fdate,filial,sectid,razdel,speccode);
END
-- --
-- 11. Считаю сумму в ЗП
-- --
UPDATE buh$zarplata_calc SET summa_zp = summa_nach*((100-proc_minus)/100.0)*(proc_zp/100.0);
-- --
-- 12. Считаю сумму заведования как таблица в виде заведующий|доктор|сумма
-- --
UPDATE buh$zarplata_calc SET summa_zaved = summa_nach/100.0 WHERE dcode_zaved>0 AND proc_zp>0;
FOR SELECT DISTINCT dcode, dname, bdate, fdate, depname, razdel FROM buh$zarplata_calc WHERE dcode_zaved=dcode AND calctype=11
INTO dcode, dname, bdate, fdate, depname, razdel do
BEGIN
FOR EXECUTE statement ('select dcode, dname, sum(summa_zaved) from buh$zarplata_calc where dcode_zaved= ? and bdate= ? and fdate= ? group by dcode, dname')
(dcode,bdate,fdate)
INTO speccode, specname, summa_nach do
BEGIN
IF (summa_nach>0) THEN
EXECUTE statement ('insert into buh$zarplata_calc(dcode,dname, bdate,fdate, calctype, speccode, specname, summa_zp, fname, depname, razdel)
values (?,?,?,?,17,?,?,?, ''Заведование'',?,?)')(dcode,dname,bdate,fdate,speccode,specname,summa_nach,depname,razdel);
END
END
-- --
-- 13. Вывожу данные
-- --
FOR SELECT dcode,dname,bdate,fdate,razdel,depname,filial,fname,sectid,csect,
schtypeid,schtype,speccode,specname,wday,calctype,summa_nach_doct,SUM(summa_nach),SUM(schcount),proc_minus,
proc_zp,SUM(summa_zp),dcode_zaved,summa_zaved
FROM buh$zarplata_calc WHERE calctype>10
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,20,21,23,24
ORDER BY dname, bdate, calctype, csect, filial, schtype, specname
INTO dcode,dname,bdate,fdate,razdel,depname,filial,fname,sectid,csect,schtypeid,schtype,speccode,specname,wday,calctype,summa_nach_doct,summa_nach,schcount,proc_minus,proc_zp,summa_zp,dcode_zaved,summa_zaved do
BEGIN
wdayname = CASE wday
WHEN 1 THEN 'Пн'
WHEN 2 THEN 'Вт'
WHEN 3 THEN 'Ср'
WHEN 4 THEN 'Чт'
WHEN 5 THEN 'Пт'
WHEN 6 THEN 'Сб'
WHEN 7 THEN 'Вс'
WHEN 11 THEN 'Буд'
WHEN 17 THEN 'Вых'
WHEN 0 THEN ''
END;
suspend;
END
END
