Входные параметры
Параметр | Тип | Описание |
---|---|---|
PARTYID |
INTEGER |
|
BEGINDATE |
DATE |
|
ENDDATE |
DATE |
|
CROPID |
INTEGER |
|
CLASSID |
INTEGER |
|
SORTID |
INTEGER |
|
STORAGEID |
INTEGER |
|
STORINGID |
INTEGER |
|
OWNERID |
INTEGER |
|
CONTRACTID |
INTEGER |
|
TOCONSIDERINCENTNERS |
SMALLINT |
|
SHOWTRANSFERINEXPENSE |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
---|---|---|
REGDATE |
DATE |
|
RECEIPT |
INTEGER |
|
EXPENSE |
INTEGER |
|
REST |
INTEGER |
|
QUANTITYDAYS |
INTEGER |
|
SUMDAILYRESTS |
BIGINT |
|
SUMREST |
BIGINT |
|
SUMRECEIPT |
INTEGER |
|
PERIODSTORAGE |
INTEGER |
|
STRDAILY |
VARCHAR(10) |
|
Описание
(Нет описания для процедуры AVERAGE_PERIOD_STORAGE_GRAIN)
Определение
CREATE PROCEDURE AVERAGE_PERIOD_STORAGE_GRAIN(
PARTYID INTEGER,
BEGINDATE DATE,
ENDDATE DATE,
CROPID INTEGER,
CLASSID INTEGER,
SORTID INTEGER,
STORAGEID INTEGER,
STORINGID INTEGER,
OWNERID INTEGER,
CONTRACTID INTEGER,
TOCONSIDERINCENTNERS SMALLINT,
SHOWTRANSFERINEXPENSE INTEGER)
RETURNS (
REGDATE DATE,
RECEIPT INTEGER,
EXPENSE INTEGER,
REST INTEGER,
QUANTITYDAYS INTEGER,
SUMDAILYRESTS BIGINT,
SUMREST BIGINT,
SUMRECEIPT INTEGER,
PERIODSTORAGE INTEGER,
STRDAILY VARCHAR(10))
AS
declare variable AREGDATE date;
declare variable ARECEIPT integer;
declare variable AEXPENSE integer;
declare variable ASUMRECEIPT bigint;
begin
REST = 0;
SUMREST = 0;
SUMRECEIPT = 0;
ASUMRECEIPT = 0;
for
select
IT.REGDATE,
sum(case
when ((OPERATION.OPERATIONSUBTYPE = 0)
and (((coalesce(:OWNERID,-1) = -1) and ((IT.NET > 0) or (:SHOWTRANSFERINEXPENSE = 0)))
or ((coalesce(:OWNERID,-1) <> -1) and (IT.NET > 0)))) then
IT.NET
end) INCOME,
sum(case
when (OPERATION.OPERATIONSUBTYPE = 1) then
IT.NET
when (((coalesce(:OWNERID,-1) <> -1) or (:SHOWTRANSFERINEXPENSE = 1))
and (IT.OPERATIONID = 17) and (IT.NET < 0)) then
-IT.NET
end) OUTCOME
from IT
-- join GETWORKPARTIES(null, :ENDDATE, :CROPID, :CLASSID, :STORAGEID) on (GETWORKPARTIES.PARTYID = IT.PARTYID)
left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
left join QUALITY on (QUALITY.QUALITYID = IT.BUHQUALITYID)
-- left join storingreminder_weight(operation.operationsubtype,it.net) on (0=0)
where (IT.REGDATE between :BEGINDATE and :ENDDATE)
and (QUALITY.CROPID = :CROPID)
and ((QUALITY.CLASSID = :CLASSID) or (coalesce(:CLASSID,-1) = -1))
and ((QUALITY.SORTID = :SORTID) or (coalesce(:SORTID,-1) = -1))
and (IT.STORAGEID = :STORAGEID)
and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
and (IT.PARTYID = :PARTYID)
and ((IT.OWNERID = :OWNERID) or (coalesce(:OWNERID,-1) = -1))
and ((IT.CONTRACTID = :CONTRACTID) or (coalesce(:CONTRACTID,-1) = -1))
and ((IT.PARENTID >= 0) and (IT.STATE >= 0) and (IT.NET is not null))
and ((((OPERATION.OPERATIONSUBTYPE in (0,1)) and (not (IT.OPERATIONID in (9,29))))
and ((IT.OPERATIONID <> 17) or (coalesce(:OWNERID,-1) <> -1)
or ((IT.OPERATIONID = 17) and (IT.OPERATIONPARAM = 1700))))
and ((not (IT.OPERATIONID in (65)))
or ((IT.OPERATIONID = 17) and (not IT.OPERATIONPARAM is null))))
group by 1
union
select (:ENDDATE + 1), 0, 0
from rdb$database
into :AREGDATE, :ARECEIPT, :AEXPENSE
do begin
if ((RECEIPT <> 0) or (EXPENSE <> 0)) then begin
if (REST > 0) then begin
QUANTITYDAYS = :AREGDATE - :REGDATE;
SUMDAILYRESTS = :REST * :QUANTITYDAYS;
ASUMRECEIPT = :ASUMRECEIPT + :RECEIPT;
SUMRECEIPT = :ASUMRECEIPT;
if (TOCONSIDERINCENTNERS = 1) then begin
SUMDAILYRESTS = cast((cast(:SUMDAILYRESTS as numeric(12,2)) / 100) as integer);
SUMRECEIPT = cast((cast(:SUMRECEIPT as numeric(12,2)) / 100) as integer);
end
SUMREST = :SUMREST + :SUMDAILYRESTS;
PERIODSTORAGE = cast((cast(:SUMREST as numeric(12,2)) / :SUMRECEIPT) as integer);
if ((mod(:PERIODSTORAGE, 10) = 1) and (:PERIODSTORAGE <> 11)) then
STRDAILY = 'день';
else
if ((mod(:PERIODSTORAGE, 10) in (2,3,4)) and (not(:PERIODSTORAGE in (12,13,14)))) then
STRDAILY = 'дні';
else STRDAILY = 'днів';
end
else QUANTITYDAYS = 0;
suspend;
end
REGDATE = :AREGDATE;
RECEIPT = coalesce(:ARECEIPT, 0);
EXPENSE = coalesce(:AEXPENSE, 0);
REST = :REST + :RECEIPT - :EXPENSE;
end
end