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

Starter · █ 18.04.2018 18:24

Да нет, отрицательные остатки не должны мешать совсем...

А вот этот код что возвращает:

SQL код:

 select nvl(sum(decode(nvl(d.locationto, 0), 0, -1, 1) * s.quantity), 0)
    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 <= to_date('08.04.2018','dd.mm.yyyy')
      and nvl(d.locationto, d.locationfrom) = 4
      and s.doctype = d.doctype and s.docid = d.id and s.article = '406338';

Starter · █ 19.04.2018 10:28

И - можно ли вот такой вариант попробовать функции ?

функция:

SQL код:

 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 >= 2 /*= 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 - 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 = 7 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(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 7 then
    return(Calc(trunc(ADateFrom), trunc(ADateTo), ALocID, AArticle, AWhat));
  else
    raise_application_error(-20999, 'Параметр AWhat должен быть в диапазоне от 1 до 7.', true);
  end if;
end;

m1n1mal · █ 19.04.2018 11:20

Здравствуйте.

Цитата:

Starter ➤ А вот этот код что возвращает:

Данный запрос возвращает значение = 9

Цитата:

Starter ➤ И - можно ли вот такой вариант попробовать функции ?

Заменил функцию, результаты вывод запроса

SQL код:

 select Get_Card_Stat(to_date('09.04.2018','dd.mm.yyyy'),to_date('18.04.2018','dd.mm.yyyy'),4,'406338',3) from dual;

1 - возвращает 3
2 - возвращает 7
3 - возвращает 0
4 - возвращает 10
5 - возвращает 0
6 - возвращает 10
7 - возвращает 7
8 - возвращает "ORA-20999: Параметр AWhat должен быть в диапазоне от 1 до 7."

Starter · █ 19.04.2018 14:51

Странно всё это :( Т.е. по частям функция работает, а в собранном виде - нет. Тут только отлаживать и отлаживать.
Если сможете организовать доступ хотя бы к стенду - могу попробовать.

m1n1mal · █ 19.04.2018 15:34

Я проверял на "боевом" сервере, стенда к сожалению нет. Мне потребуется какое-то время чтобы его развернуть и подготовить. Как только я все подготовлю, отпишусь Вам.
Для проверки потребуется только БД или рабочий Супермаг+ тоже?

Starter · █ 19.04.2018 18:12

БД, думаю, вполне достаточно будет. просто пробросить порт до сервера.

Starter · █ 26.04.2018 18:28

В общем, по итогам - в функции был глюк вычисления начального остатка, неправильно обрабатывались документы перемещения.
вот рабочий вариант:

:

SQL код:

 ------------
--Получить статистику по карточке товара
--входные параметры - дата начала периода, дата окончания периода, код места хранения, артикул, что хотим узнать.
--варианты что =
--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((case when(d.locationto=ALocID) then 1 when (d.locationfrom=ALocID) then -1 else 0 end) * 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 >= 2 /*= 3*/ and d.createdat <= AOnDate
      and (d.locationto=ALocID or 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 - 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(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 Get_Card_Stat;
/  
commit;

m1n1mal · █ 27.04.2018 10:30

Спасибо, все отлично, функция возвращает одинаковые значения с расчетом ССР.

Starter · █ 27.04.2018 12:37

Теперь вторая часть марлезонского балета - расчёт недополученной прибыли ? :)
1. Во-первых, какую цифру брать в статистику - как в "ТЗ" - дней с 0 остатком - можно, но возможна ситуация, когда остаток уйдёт в минус, и его вроде как нет, а в статистику не войдёт. Или остаток вначале дня был, но за день весь товар продался и остаток на конец дня стал нулевым. И день войдёт в статистику, хотя вроде как не должен. Мы для таких случаев смотрим те дни, когда продаж не было и остаток <=0. т.е. параметр в вышеуказанной функции =9.
2. Как и говорилось выше, два периода в БА указать нельзя. Но можно задать в коде маленький период (либо его длину в днях). Пример:

(0,05Мб)
В данном случае - в задаче добавлено поле, в значении которого стоит 14. Остальные поля (доход за период и недополуч) прибыль вычисляемые на основе значения этого поля. Если нужен период 30 дней - меняем значение 14 на 30.

Другой вариант - добавляем в задачу два поля, дата1 и дата2, там указываем нужные нам даты, получаем результаты. В принципе, это тоже некое задание фильтра, только не через параметры а через код, что, конечно, некрасиво, но зато работает. Пользователям, соответственно, распечатать пошаговую инструкцию куда и как нажимать, чтобы эти даты выбрать.

(0,06Мб)

(0,02Мб)

Ну и третий вариант - вообще не указываем даты в задаче а зашиваем в коде. Просто обычно редко бывает так, чтобы период был произвольным. обычно это - с начала месяца, прошлый месяц, прошлая неделя, две недели, не считая текущую, и пр. можно узнать у пользователей, какой временной интервал им наиболее интересен и зашить это в код, либо сделать 2 или 3 задачи с разными вшитыми интервалами.

m1n1mal · █ 27.04.2018 17:44

Цитата:

Starter ➤ 1. Во-первых, какую цифру брать в статистику - как в "ТЗ" - дней с 0 остатком - можно, но возможна ситуация, когда остаток уйдёт в минус, и его вроде как нет, а в статистику не войдёт. Или остаток вначале дня был, но за день весь товар продался и остаток на конец дня стал нулевым. И день войдёт в статистику, хотя вроде как не должен. Мы для таких случаев смотрим те дни, когда продаж не было и остаток <=0. т.е. параметр в вышеуказанной функции =9.

В текущей Вашей функции мы опробовали оба параметра и 3 и 9.
В данном случае однозначного решения какой будут использовать нет, интересны оба. Тут интересен как вариант с остатком = 0 (так как тут однозначно свидетельствует об отсутствии товара), так и вариант когда отсутствовали продажи и остаток был <=0 (пересорты и тп.)

Цитата:

Starter ➤ 2. Как и говорилось выше, два периода в БА указать нельзя. Но можно задать в коде маленький период (либо его длину в днях). Пример:

Отличным вариантом выглядит тот, в котором мы задаем дата1 и дата2, такой вариант сможет закрыть разные диапазоны без создания дополнительных задач.