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