✎ Скрипт расчета ЗП по отчету [Клиника Wiki]

Вы загрузили старую ревизию документа. Сохранив её, вы создадите новую текущую версию с этим содержимым.

====== Скрипт расчета ЗП по отчету ======

<code sql>
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
</code>

Сводка изменений

Примечание: редактируя эту страницу, вы соглашаетесь на использование своего вклада на условиях следующей лицензии: CC Attribution-Share Alike 4.0 International

Наверх

администрирование/скрипты/разное/зп.отчет.1560448909.txt.gz · Последние изменения: 2019/06/13 21:01 (внешнее изменение)