------------
--Получить статистику по карточке товара
--входные параметры - дата начала периода, дата окончания периода, код места хранения, артикул, что хотим узнать.
--варианты что =
--1 - число дней, когда карточка продавалась
--2 - число дней, когда карточка не продавалась
--3 - число дней, когда остаток по карточке был равен нулю
--4 - число дней, когда остаток был не равен нулю
--5 - число дней, когда остаток был отрицательным.
--6 - число дней, когда остаток был положительным.
--7 - число дней, когда карточка не продавалась и остаток по ней не был равен нулю.
--8 - суммарный остаток за все дни периода (для вычисления среднего остатка, для расчета точной оборачиваемости)
--9 - число дней, когда карточка продавалась или остаток по ней не был больше нуля
create or replace function 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(ADateFrom, 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 + 1 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 + 1 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 or c.quantity > 0 then -- число дней, когда карточка продавалась или остаток по ней не был больше нуля
res := res + 1;
end if;
end loop;
return(res);
exception when no_data_found then
return(0);
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;