Форум OlegON > Программы и оборудование для автоматизации торговли > Системы автоматизации торговли > Супермаг Плюс (Супермаг 2000)

Создание отчета с количеством дней отсутствия товара, в Супермаг+

31.10.2020 13:03


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

А вот этот код что возвращает:
SQL код:
select nvl(sum(decode(nvl(d.locationto0), 0, -11) * s.quantity), 0)
    
from supermag.smdocuments dsupermag.smspec s
    where d
.doctype in ('WI''WO''IW''CS''CR''PN''PE''PO')
      and 
d.docstate and d.createdat <= to_date('08.04.2018','dd.mm.yyyy')
      and 
nvl(d.locationtod.locationfrom) = 4
      
and s.doctype d.doctype and s.docid d.id and s.article '406338'
19.04.2018 10:28
Starter
 
И - можно ли вот такой вариант попробовать функции ?

функция:
SQL код:
create or replace function Get_Card_Stat(ADateFrom in dateADateTo in dateALocID in supermag.smstorelocations.id%type,
  
AArticle in supermag.smcard.article%typeAWhat in integer) return integer 
is
  i integer
;
  
error_param_value exception;
  
  function 
SaleDaysCount(ADateFrom in dateADateTo in dateALocID 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 dsupermag.smspec s
    where d
.doctype in ('WO''CS') and d.opcode and d.docstate 
      
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 dateALocID in supermag.smstorelocations.id%type,
    
AArticle in supermag.smcard.article%type) return number 
  is
    res number
;
  
begin
    select nvl
(sum(decode(nvl(d.locationto0), 0, -11) * s.quantity), 0)
    
into res
    from supermag
.smdocuments dsupermag.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.locationtod.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 dateADateTo in dateALocID in supermag.smstorelocations.id%type,
    
AArticle in supermag.smcard.article%typeAWhat in integer) return integer
  is
    vRemains number 
:= GetRemainsOnDate(ADateFrom 1ALocIDAArticle);
    
res integer := 0;
  
begin
    
for c in (select nvl(sum(nvl(d.quantity0)) over (order by c.dat) + vRemains0) as quantity,
                
nvl(sum(nvl(d.salequantity0)) over (partition by c.dat), 0) as salequantity
              from
                
(select dd.createdatsum(dd.quantity) as quantitysum(dd.salequantity) as salequantity
                 from
                   
(select d.createdatsum(s.quantity) as quantityas salequantity
                    from supermag
.smdocuments dsupermag.smspec s
                    where d
.doctype s.doctype and d.id s.docid and s.article AArticle and d.locationto ALocID
                      
and d.docstate >= and d.createdat between ADateFrom and ADateTo
                    group by d
.createdat
                    union all
                    select d
.createdatsum(-s.quantity) as quantitysum(decode(d.opcode1, -s.quantity0)) as salequantity
                    from supermag
.smdocuments dsupermag.smspec s
                    where d
.doctype s.doctype and d.id s.docid and s.article AArticle and d.locationfrom ALocID
                      
and d.docstate >= and d.createdat between ADateFrom and ADateTo
                    group by d
.createdatdd
                 group by dd
.createdatd,
                (
select dt.dat
                 from 
                   
(select ADateFrom level as dat
                    from dual connect by level 
<= ADateTo ADateFrom 1dtc
              where c
.dat d.createdat(+)) loop
      
if AWhat and c.quantity 0 then -- число днейкогда остаток по карточке был равен нулю
        res 
:= res 1;
      
elsif AWhat and c.quantity != 0 then -- число днейкогда остаток был не равен нулю
        res 
:= res 1;
      
elsif AWhat and c.quantity 0 then -- число днейкогда остаток был отрицательным
        res 
:= res 1;
      
elsif AWhat and c.quantity 0 then -- число днейкогда остаток был положительным
        res 
:= res 1;
      
elsif AWhat and c.salequantity 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), ALocIDAArticle));
  
elsif AWhat 2 then -- число днейкогда карточка не продавалась
    
return(trunc(ADateTo) - trunc(ADateFrom) + SaleDaysCount(trunc(ADateFrom), trunc(ADateTo), ALocIDAArticle));
  
elsif AWhat between 3 and 7 then
    
return(Calc(trunc(ADateFrom), trunc(ADateTo), ALocIDAArticleAWhat));
  else
    
raise_application_error(-20999'Параметр AWhat должен быть в диапазоне от 1 до 7.'true);
  
end if;
end
19.04.2018 11:20
m1n1mal
 
Здравствуйте.

Цитата:
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',3from dual
1 - возвращает 3
2 - возвращает 7
3 - возвращает 0
4 - возвращает 10
5 - возвращает 0
6 - возвращает 10
7 - возвращает 7
8 - возвращает "ORA-20999: Параметр AWhat должен быть в диапазоне от 1 до 7."
19.04.2018 14:51
Starter
 
Странно всё это :( Т.е. по частям функция работает, а в собранном виде - нет. Тут только отлаживать и отлаживать.
Если сможете организовать доступ хотя бы к стенду - могу попробовать.
19.04.2018 15:34
m1n1mal
 
Я проверял на "боевом" сервере, стенда к сожалению нет. Мне потребуется какое-то время чтобы его развернуть и подготовить. Как только я все подготовлю, отпишусь Вам.
Для проверки потребуется только БД или рабочий Супермаг+ тоже?
19.04.2018 18:12
Starter
 
БД, думаю, вполне достаточно будет. просто пробросить порт до сервера.
26.04.2018 18:28
Starter
 
В общем, по итогам - в функции был глюк вычисления начального остатка, неправильно обрабатывались документы перемещения.
вот рабочий вариант:

:
SQL код:
------------
--
Получить статистику по карточке товара
--входные параметры дата начала периодадата окончания периодакод места храненияартикулчто хотим узнать.
--
варианты что =
--
число днейкогда карточка продавалась
--число днейкогда карточка не продавалась
--число днейкогда остаток по карточке был равен нулю
--число днейкогда остаток был не равен нулю
--число днейкогда остаток был отрицательным.
--
число днейкогда остаток был положительным
--
число днейкогда карточка не продавалась и остаток по ней не был равен нулю.
--
суммарный остаток за все дни периода (для вычисления среднего остатка)
--
число днейкогда карточка не продавалась и остаток по ней был меньше или равен нули

create 
or replace function Get_Card_Stat(ADateFrom in dateADateTo in dateALocID in supermag.smstorelocations.id%type,
  
AArticle in supermag.smcard.article%typeAWhat in integer) return integer 
is
  i integer
;
  
error_param_value exception;
  
  function 
SaleDaysCount(ADateFrom in dateADateTo in dateALocID 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 dsupermag.smspec s
    where d
.doctype in ('WO''CS') and d.opcode and d.docstate 
      
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 dateALocID in supermag.smstorelocations.id%type,
    
AArticle in supermag.smcard.article%type) return number 
  is
    res number
;
  
begin
    select nvl
(sum((case when(d.locationto=ALocIDthen 1 when (d.locationfrom=ALocIDthen -else 0 end) * s.quantity), 0)
    
into res
    from supermag
.smdocuments dsupermag.smspec s
    where d
.doctype in ('WI''WO''IW''CS''CR''PN''PE''PO')
      and 
d.docstate >= /*= 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 dateADateTo in dateALocID in supermag.smstorelocations.id%type,
    
AArticle in supermag.smcard.article%typeAWhat in integer) return integer
  is
    vRemains number 
:= GetRemainsOnDate(ADateFrom 1ALocIDAArticle);
    
res integer := 0;
  
begin
    
for c in (select nvl(sum(nvl(d.quantity0)) over (order by c.dat) + vRemains0) as quantity,
                
nvl(sum(nvl(d.salequantity0)) over (partition by c.dat), 0) as salequantity
              from
                
(select dd.createdatsum(dd.quantity) as quantitysum(dd.salequantity) as salequantity
                 from
                   
(select d.createdatsum(s.quantity) as quantityas salequantity
                    from supermag
.smdocuments dsupermag.smspec s
                    where d
.doctype s.doctype and d.id s.docid and s.article AArticle and d.locationto ALocID
                      
and d.docstate >= and d.createdat between ADateFrom and ADateTo
                    group by d
.createdat
                    union all
                    select d
.createdatsum(-s.quantity) as quantitysum(decode(d.opcode1, -s.quantity0)) as salequantity
                    from supermag
.smdocuments dsupermag.smspec s
                    where d
.doctype s.doctype and d.id s.docid and s.article AArticle and d.locationfrom ALocID
                      
and d.docstate >= and d.createdat between ADateFrom and ADateTo
                    group by d
.createdatdd
                 group by dd
.createdatd,
                (
select dt.dat
                 from 
                   
(select ADateFrom level as dat
                    from dual connect by level 
<= ADateTo ADateFrom 1dtc
              where c
.dat d.createdat(+)) loop
      
if AWhat and c.quantity 0 then -- число днейкогда остаток по карточке был равен нулю
        res 
:= res 1;
      
elsif AWhat and c.quantity != 0 then -- число днейкогда остаток был не равен нулю
        res 
:= res 1;
      
elsif AWhat and c.quantity 0 then -- число днейкогда остаток был отрицательным
        res 
:= res 1;
      
elsif AWhat and c.quantity 0 then -- число днейкогда остаток был положительным
        res 
:= res 1;
      
elsif AWhat and c.quantity 0 then -- cуммостатоккогда остаток был положительным (для расчета оборачиваемости)
        
res := res c.quantity;
      
elsif AWhat and (c.salequantity and c.quantity !=0then -- число днейкогда карточка не продавалась и остаток по ней не был равен нулю
        res 
:= res 1;
      
elsif ((AWhat 9) and (c.salequantity 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), ALocIDAArticle));
  
elsif AWhat 2 then -- число днейкогда карточка не продавалась
    
return(trunc(ADateTo) - trunc(ADateFrom) + SaleDaysCount(trunc(ADateFrom), trunc(ADateTo), ALocIDAArticle));
  
elsif AWhat between 3 and 9 then
    
return(Calc(trunc(ADateFrom), trunc(ADateTo), ALocIDAArticleAWhat));
  else
    
raise_application_error(-20999'Параметр AWhat должен быть в диапазоне от 1 до 9.'true);
  
end if;
end Get_Card_Stat;
/  
commit
27.04.2018 10:30
m1n1mal
 
Спасибо, все отлично, функция возвращает одинаковые значения с расчетом ССР.
27.04.2018 12:37
Starter
 
Теперь вторая часть марлезонского балета - расчёт недополученной прибыли ? :)
1. Во-первых, какую цифру брать в статистику - как в "ТЗ" - дней с 0 остатком - можно, но возможна ситуация, когда остаток уйдёт в минус, и его вроде как нет, а в статистику не войдёт. Или остаток вначале дня был, но за день весь товар продался и остаток на конец дня стал нулевым. И день войдёт в статистику, хотя вроде как не должен. Мы для таких случаев смотрим те дни, когда продаж не было и остаток <=0. т.е. параметр в вышеуказанной функции =9.
2. Как и говорилось выше, два периода в БА указать нельзя. Но можно задать в коде маленький период (либо его длину в днях). Пример:



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

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



(0,06Мб)



(0,02Мб)

Ну и третий вариант - вообще не указываем даты в задаче а зашиваем в коде. Просто обычно редко бывает так, чтобы период был произвольным. обычно это - с начала месяца, прошлый месяц, прошлая неделя, две недели, не считая текущую, и пр. можно узнать у пользователей, какой временной интервал им наиболее интересен и зашить это в код, либо сделать 2 или 3 задачи с разными вшитыми интервалами.
27.04.2018 17:44
m1n1mal
 
Цитата:
Starter 1. Во-первых, какую цифру брать в статистику - как в "ТЗ" - дней с 0 остатком - можно, но возможна ситуация, когда остаток уйдёт в минус, и его вроде как нет, а в статистику не войдёт. Или остаток вначале дня был, но за день весь товар продался и остаток на конец дня стал нулевым. И день войдёт в статистику, хотя вроде как не должен. Мы для таких случаев смотрим те дни, когда продаж не было и остаток <=0. т.е. параметр в вышеуказанной функции =9.
В текущей Вашей функции мы опробовали оба параметра и 3 и 9.
В данном случае однозначного решения какой будут использовать нет, интересны оба. Тут интересен как вариант с остатком = 0 (так как тут однозначно свидетельствует об отсутствии товара), так и вариант когда отсутствовали продажи и остаток был <=0 (пересорты и тп.)

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

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.