Создание отчета с количеством дней отсутствия товара, в Супермаг+ : Супермаг Плюс (Супермаг 2000)

select nvl(sum(nvl(d.quantity, 0)) over (order by c.dat) + 4, 0) as quantity,
                nvl(sum(nvl(d.salequantity, 0)) over (partition by c.dat), 0) as salequantity
              from
                (select dd.createdat, sum(dd.quantity) as quantity, sum(dd.salequantity) as salequantity
                 from
                   (select d.createdat, sum(s.quantity) as quantity, 0 as salequantity
                    from supermag.smdocuments d, supermag.smspec s
                    where d.doctype = s.doctype and d.id = s.docid and s.article = '406338' and d.locationto = 2
                      and d.docstate >= 2 and d.createdat between trunc(to_date('09.04.2018','dd.mm.yyyy')) and trunc(to_date('18.04.2018','dd.mm.yyyy'))
                    group by d.createdat
                    union all
                    select d.createdat, sum(-s.quantity) as quantity, sum(decode(d.opcode, 1, -s.quantity, 0)) as salequantity
                    from supermag.smdocuments d, supermag.smspec s
                    where d.doctype = s.doctype and d.id = s.docid and s.article = '406338' and d.locationfrom = 2
                      and d.docstate >= 2 and d.createdat between trunc(to_date('09.04.2018','dd.mm.yyyy')) and trunc(to_date('18.04.2018','dd.mm.yyyy'))
                    group by d.createdat) dd
                 group by dd.createdat) d,
                (select dt.dat
                 from 
                   (select trunc(to_date('09.04.2018','dd.mm.yyyy')) - 1 + level as dat
                    from dual connect by level <= trunc(to_date('18.04.2018','dd.mm.yyyy')) - trunc(to_date('09.04.2018','dd.mm.yyyy')) + 1) dt) c
              where c.dat = d.createdat(+); 

--Получить статистику по карточке товара
--входные параметры - дата начала периода, дата окончания периода, код места хранения, артикул, что хотим узнать.
--варианты что =
--1 - число дней, когда карточка продавалась
--2 - число дней, когда карточка не продавалась
--3 - число дней, когда остаток по карточке был равен нулю
--4 - число дней, когда остаток был не равен нулю
--5 - число дней, когда остаток был отрицательным.
--6 - число дней, когда остаток был положительным. 
--7 - число дней, когда карточка не продавалась и остаток по ней не был равен нулю.
--8 - суммарный остаток за все дни периода (для вычисления среднего остатка)
--9 - число дней, когда карточка не продавалась и остаток по ней был меньше или равен нули

CREATE OR REPLACE function SUPERMAG.Get_Card_Stat(ADateFrom in date, ADateTo in date, ALocID in supermag.smstorelocations.id%type,
  AArticle in supermag.smcard.article%type, AWhat in integer) return integer
is
  i integer;
  error_param_value exception;

  function SaleDaysCount(ADateFrom in date, ADateTo in date, ALocID in supermag.smstorelocations.id%type,
    AArticle in supermag.smcard.article%type) return integer is
    res integer := 0;
  begin
    select count(distinct d.createdat)
    into res
    from supermag.smdocuments d, supermag.smspec s
    where d.doctype in ('WO', 'CS') and d.opcode = 1 and d.docstate = 3
      and d.createdat between ADateFrom and ADateTo and d.locationfrom = ALocID
      and s.doctype = d.doctype and s.docid = d.id and s.article = AArticle;
    return(res);
  exception when no_data_found then
    return(0);
  end;
  function GetRemainsOnDate(AOnDate in date, ALocID in supermag.smstorelocations.id%type,
    AArticle in supermag.smcard.article%type) return number
  is
    res number;
  begin
    select nvl(sum(decode(nvl(d.locationto, 0), 0, -1, 1) * s.quantity), 0)
    into res
    from supermag.smdocuments d, supermag.smspec s
    where d.doctype in ('WI', 'WO', 'IW', 'CS', 'CR', 'PN', 'PE', 'PO')
      and d.docstate = 3 and d.createdat <= AOnDate
      and nvl(d.locationto, d.locationfrom) = ALocID
      and s.doctype = d.doctype and s.docid = d.id and s.article = AArticle;
    return(res);
  exception when no_data_found then
    return(0);
  end;
  function Calc(ADateFrom in date, ADateTo in date, ALocID in supermag.smstorelocations.id%type,
    AArticle in supermag.smcard.article%type, AWhat in integer) return integer
  is
    vRemains number := GetRemainsOnDate(trunc(ADateFrom)-1, ALocID, AArticle);
    res integer := 0;
  begin
    for c in (select nvl(sum(nvl(d.quantity, 0)) over (order by c.dat) + vRemains, 0) as quantity,
                nvl(sum(nvl(d.salequantity, 0)) over (partition by c.dat), 0) as salequantity
              from
                (select dd.createdat, sum(dd.quantity) as quantity, sum(dd.salequantity) as salequantity
                 from
                   (select d.createdat, sum(s.quantity) as quantity, 0 as salequantity
                    from supermag.smdocuments d, supermag.smspec s
                    where d.doctype = s.doctype and d.id = s.docid and s.article = AArticle and d.locationto = ALocID
                      and d.docstate >= 2 and d.createdat between ADateFrom and ADateTo
                    group by d.createdat
                    union all
                    select d.createdat, sum(-s.quantity) as quantity, sum(decode(d.opcode, 1, -s.quantity, 0)) as salequantity
                    from supermag.smdocuments d, supermag.smspec s
                    where d.doctype = s.doctype and d.id = s.docid and s.article = AArticle and d.locationfrom = ALocID
                      and d.docstate >= 2 and d.createdat between ADateFrom and ADateTo
                    group by d.createdat) dd
                 group by dd.createdat) d,
                (select dt.dat
                 from
                   (select ADateFrom - 1 + level as dat
                    from dual connect by level <= ADateTo - ADateFrom + 1) dt) c
              where c.dat = d.createdat(+)) loop
      if AWhat = 3 and c.quantity = 0 then -- число дней, когда остаток по карточке был равен нулю
        res := res + 1;
      elsif AWhat = 4 and c.quantity != 0 then -- число дней, когда остаток был не равен нулю
        res := res + 1;
      elsif AWhat = 5 and c.quantity < 0 then -- число дней, когда остаток был отрицательным
        res := res + 1;
      elsif AWhat = 6 and c.quantity > 0 then -- число дней, когда остаток был положительным
        res := res + 1;
      elsif AWhat = 8 and c.quantity > 0 then -- cумм. остаток, когда остаток был положительным (для расчета оборачиваемости)
        res := res + c.quantity;

      elsif AWhat = 7 and (c.salequantity = 0 and c.quantity !=0) then -- число дней, когда карточка не продавалась и остаток по ней не был равен нулю
        res := res + 1;
      elsif ((AWhat = 9) and (c.salequantity = 0 and c.quantity <= 0)) then -- число дней, когда карточка не продавалась и остаток по ней был меньше или равен нулю
        res := res + 1;
      end if;
    end loop;
    return(res);
  exception when no_data_found then
    return(99);
  end;

begin
  if AWhat = 1 then -- число дней, когда карточка продавалась
    return(SaleDaysCount(trunc(ADateFrom), trunc(ADateTo), ALocID, AArticle));
  elsif AWhat = 2 then -- число дней, когда карточка не продавалась
    return(trunc(ADateTo) - trunc(ADateFrom) + 1 - SaleDaysCount(trunc(ADateFrom), trunc(ADateTo), ALocID, AArticle));
  elsif AWhat between 3 and 9 then
    return(Calc(trunc(ADateFrom), trunc(ADateTo), ALocID, AArticle, AWhat));
  else
    raise_application_error(-20999, 'Параметр AWhat должен быть в диапазоне от 1 до 9.', true);
  end if;
end;
/  
commit;