Входные параметры
Параметр | Тип | Описание |
---|---|---|
BEGINDATE |
DATE |
|
ENDDATE |
DATE |
|
ISCURRENT |
INTEGER |
|
AOWNERID |
INTEGER |
|
CONTRACTID |
INTEGER |
|
STORAGEID |
INTEGER |
|
STORINGID |
INTEGER |
|
ONLYREST |
INTEGER |
|
SHOWWASTE |
INTEGER |
|
CHECKFORAGECLASS |
INTEGER |
|
OWNERWASTEID |
INTEGER |
|
PRELIMINARYSHRINKAGE |
INTEGER |
|
SHOWINGCROPYEAR |
INTEGER |
|
YEARHARVESTING |
INTEGER |
|
SHOWTRANSFERINEXPENSE |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
---|---|---|
OWNERID |
INTEGER |
|
CLIENTFULLNAME |
VARCHAR(100) |
|
CLIENTNAME |
VARCHAR(30) |
|
CLIENTTYPEID |
INTEGER |
|
PARTYID |
INTEGER |
|
PARTY_BEGINDATE |
DATE |
|
PARTY_ENDDATE |
DATE |
|
CROPID |
INTEGER |
|
CLASSID |
INTEGER |
|
CROPNAME |
VARCHAR(20) |
|
CROPFULLNAME |
VARCHAR(100) |
|
CROPYEARFULLNAME |
VARCHAR(100) |
|
CROPTYPEID |
INTEGER |
|
CROPYEAR |
INTEGER |
|
CROPGHI |
INTEGER |
|
CLASSGHI |
INTEGER |
|
PACKNAME |
VARCHAR(30) |
|
REMAINNET |
INTEGER |
|
INCOMENETAVTO |
INTEGER |
|
INCOMENETRRW |
INTEGER |
|
INCOMENET |
INTEGER |
|
REINCOME |
INTEGER |
|
OUTCOME |
INTEGER |
|
REOUTCOME |
INTEGER |
|
DECREASENET |
INTEGER |
|
RESTNET |
INTEGER |
|
HUMWEIGHT |
INTEGER |
|
REMAINWEIGHT |
INTEGER |
|
INCOMEWEIGHTAVTO |
INTEGER |
|
INCOMEWEIGHTRRW |
INTEGER |
|
INCOMEWEIGHT |
INTEGER |
|
OUTCOMEWEIGHT |
INTEGER |
|
RESTWEIGHT |
INTEGER |
|
ARRIVALNET |
INTEGER |
|
BEGINNINGNET |
INTEGER |
|
ARRIVALWEIGHT |
INTEGER |
|
BEGINNINGWEIGHT |
INTEGER |
|
REINCOMEWEIGHT |
INTEGER |
|
REOUTCOMEWEIGHT |
INTEGER |
|
BEGIN_HUMWEIGHT |
INTEGER |
|
PERIOD_HUMWEIGHT |
INTEGER |
|
ALLINCOMEWEIGHT |
INTEGER |
|
REMAINDECLARATION |
INTEGER |
|
INCOMEDECLARATION |
INTEGER |
|
OUTCOMEDECLARATION |
INTEGER |
|
COMEAFTERDECLARATION |
INTEGER |
|
STORAGENAME |
VARCHAR(100) |
|
CROPCLASSNAME |
VARCHAR(41) |
|
Описание
(Нет описания для процедуры ACN_SVODKA_VALARS)
Определение
CREATE PROCEDURE ACN_SVODKA_VALARS(
BEGINDATE DATE,
ENDDATE DATE,
ISCURRENT INTEGER,
AOWNERID INTEGER,
CONTRACTID INTEGER,
STORAGEID INTEGER,
STORINGID INTEGER,
ONLYREST INTEGER,
SHOWWASTE INTEGER,
CHECKFORAGECLASS INTEGER,
OWNERWASTEID INTEGER,
PRELIMINARYSHRINKAGE INTEGER,
SHOWINGCROPYEAR INTEGER,
YEARHARVESTING INTEGER,
SHOWTRANSFERINEXPENSE INTEGER)
RETURNS (
OWNERID INTEGER,
CLIENTFULLNAME VARCHAR(100),
CLIENTNAME VARCHAR(30),
CLIENTTYPEID INTEGER,
PARTYID INTEGER,
PARTY_BEGINDATE DATE,
PARTY_ENDDATE DATE,
CROPID INTEGER,
CLASSID INTEGER,
CROPNAME VARCHAR(20),
CROPFULLNAME VARCHAR(100),
CROPYEARFULLNAME VARCHAR(100),
CROPTYPEID INTEGER,
CROPYEAR INTEGER,
CROPGHI INTEGER,
CLASSGHI INTEGER,
PACKNAME VARCHAR(30),
REMAINNET INTEGER,
INCOMENETAVTO INTEGER,
INCOMENETRRW INTEGER,
INCOMENET INTEGER,
REINCOME INTEGER,
OUTCOME INTEGER,
REOUTCOME INTEGER,
DECREASENET INTEGER,
RESTNET INTEGER,
HUMWEIGHT INTEGER,
REMAINWEIGHT INTEGER,
INCOMEWEIGHTAVTO INTEGER,
INCOMEWEIGHTRRW INTEGER,
INCOMEWEIGHT INTEGER,
OUTCOMEWEIGHT INTEGER,
RESTWEIGHT INTEGER,
ARRIVALNET INTEGER,
BEGINNINGNET INTEGER,
ARRIVALWEIGHT INTEGER,
BEGINNINGWEIGHT INTEGER,
REINCOMEWEIGHT INTEGER,
REOUTCOMEWEIGHT INTEGER,
BEGIN_HUMWEIGHT INTEGER,
PERIOD_HUMWEIGHT INTEGER,
ALLINCOMEWEIGHT INTEGER,
REMAINDECLARATION INTEGER,
INCOMEDECLARATION INTEGER,
OUTCOMEDECLARATION INTEGER,
COMEAFTERDECLARATION INTEGER,
STORAGENAME VARCHAR(100),
CROPCLASSNAME VARCHAR(41))
AS
declare variable TPARTYID integer;
declare variable MINDATE date;
declare variable MAXDATE date;
declare variable CLIENTID integer;
declare variable SORTID integer;
declare variable FORAGECROPID integer;
declare variable FORAGECLASSID integer;
declare variable REGDATE char(10);
declare variable NET integer;
declare variable WEIGHT integer;
declare variable OPERATIONSUBTYPE integer;
declare variable OPERATIONID integer;
declare variable AHUMWEIGHT integer;
declare variable ABEGIN_HUMWEIGHT integer;
declare variable APERIOD_HUMWEIGHT integer;
declare variable AUWORDERID integer;
declare variable AUWACTDATE date;
declare variable OPERATIONPARAM integer;
declare variable UWORDERID integer;
declare variable PARTYNAME varchar(31);
declare variable DEFAULTCROPYEAR integer;
declare variable INITIALDATE date;
begin
if (BEGINDATE is null) then begin
select max(CROPPARAMS.DEFAULTVALUE)
from CROPPARAMS
where ((CROPPARAMS.CROPID = -1) and (CROPPARAMS.QUALITYTYPEID = 6))
into :DEFAULTCROPYEAR;
select min(PARTIES.BEGINDATE)
from PARTIES
where (extract(YEAR from PARTIES.BEGINDATE) = :DEFAULTCROPYEAR)
into INITIALDATE;
BEGINDATE = ENDDATE;
end
else INITIALDATE = BEGINDATE;
for
select CLIENTID, CLIENTFULLNAME, CLIENTNAME, CLIENTTYPEID, PARTYID,
min(BEGINDATE) BEGINDATE, min(ENDDATE) ENDDATE,
CROPID, CLASSID, CROPNAME, CROPFULLNAME, min(CROPTYPEID) CROPTYPEID,
CROPYEAR, min(CROPGHI) CROPGHI, min(CLASSGHI) CLASSGHI, PACKNAME,
sum(REMAINNET) REMAINNET, sum(INCOMENETAVTO) INCOMENETAVTO,
sum(INCOMENETRRW) INCOMENETRRW, sum(INCOMENET) INCOMENET,
sum(INCOMERENEVALNET) INCOMERENEVALNET, sum(OUTCOMENET) OUTCOMENET,
sum(OUTCOMERENEVALNET) OUTCOMERENEVALNET, sum(DECREASENET) DECREASENET,
sum(COMEAFTERNET) COMEAFTERNET, sum(ARRIVALNET) ARRIVALNET,
sum(BEGINNINGNET) BEGINNINGNET, sum(HUMWEIGHT) HUMWEIGHT,
sum(COMEBEFOREWEIGHT) COMEBEFOREWEIGHT, sum(INCOMEWEIGHTAVTO) INCOMEWEIGHTAVTO,
sum(INCOMEWEIGHTRRW) INCOMEWEIGHTRRW, sum(INCOMEWEIGHT) INCOMEWEIGHT,
sum(REINCOMEWEIGHT) REINCOMEWEIGHT, sum(OUTCOMEWEIGHT) OUTCOMEWEIGHT,
sum(REOUTCOMEWEIGHT) REOUTCOMEWEIGHT, sum(COMEAFTERWEIGHT) COMEAFTERWEIGHT,
sum(ARRIVALWEIGHT) ARRIVALWEIGHT, sum(BEGINNINGWEIGHT) BEGINNINGWEIGHT,
sum(ALLINCOMEWEIGHT) ALLINCOMEWEIGHT,
sum(REMAINDECLARATION) REMAINDECLARATION, sum(INCOMEDECLARATION) INCOMEDECLARATION,
sum(OUTCOMEDECLARATION) OUTCOMEDECLARATION, sum(COMEAFTERDECLARATION) COMEAFTERDECLARATION,
STORAGENAME, min(CROPCLASSNAME) CROPCLASSNAME
from (
select
case
when (QUALITY.CROPID = 9) then
:OWNERWASTEID
else IT.OWNERID
end CLIENTID,
case
when (QUALITY.CROPID = 9) then
FORAGECLIENTS.CLIENTFULLNAME
else OWNER.CLIENTFULLNAME
end CLIENTFULLNAME,
case
when (QUALITY.CROPID = 9) then
FORAGECLIENTS.CLIENTNAME
else OWNER.CLIENTNAME
end CLIENTNAME,
case
when (QUALITY.CROPID = 9) then
FORAGECLIENTS.CLIENTTYPEID
else coalesce(OWNER.CLIENTTYPEID,0)
end CLIENTTYPEID,
IT.PARTYID PARTYID, min(PARTIES.BEGINDATE) BEGINDATE,
min(coalesce(PARTIES.ENDDATE,current_date)) ENDDATE,
QUALITY.CROPID CROPID, QUALITY.CLASSID CLASSID, CROP.CROPNAME CROPNAME,
QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME CROPFULLNAME,
min(case
when (CROP.CROPTYPEID = 3) then
100
else CROP.CROPTYPEID
end) CROPTYPEID,
max(case
when (:SHOWINGCROPYEAR = 1) then
case
when (IT.STATE = -999) then
coalesce((select max(QUALITY_SELECT.HARVESTYEAR)
from IT IT2
left join QUALITY_SELECT(IT2.LABQUALITYID) on (0=0)
where ((IT2.REGISTERID = IT.REGISTERID) and (IT2.STATE > -1))
group by IT2.REGISTERID),0)
else coalesce(YEARGR.QUALITYVALUE,coalesce(QUALITY_SELECT.HARVESTYEAR,0))
end
else 0
end) CROPYEAR,
min(coalesce(CROP.CROPGHI,0)) CROPGHI, min(coalesce(CLASS.CLASSGHI,0)) CLASSGHI,
case
when (PACKS.PACKID is null) then
''
when (PACKS.PACKID = 1) then
''
else PACKS.PACKNAME
end PACKNAME,
-- Физический вес
coalesce(sum(case
when (IT.REGDATE < :BEGINDATE) then
case
when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
IT.NET
when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.STATE > -1)) then
-IT.NET
end
end),0) REMAINNET,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID in (0,1))) then
IT.NET
else 0
end),0) INCOMENETAVTO,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID = 2)) then
IT.NET
else 0
end),0) INCOMENETRRW,
coalesce(sum(case
when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
and ((OPERATION.OPERATIONTYPE in (0,4))
or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))
and ((IT.NET > 0) or (:SHOWTRANSFERINEXPENSE = 0))))) then
IT.NET
else 0
end),0) INCOMENET,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
and (IT.NET > 0) and (IT.OPERATIONPARAM is null)) then
IT.NET
end),0) INCOMERENEVALNET,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and ((OPERATION.OPERATIONSUBTYPE = 1)
and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (9,22,71,100)))) then
IT.NET
when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
and ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))
and (IT.NET < 0) and (:SHOWTRANSFERINEXPENSE = 1))) then
-IT.NET
end),0) OUTCOMENET,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
and (IT.NET < 0) and (IT.OPERATIONPARAM is null)) then
-IT.NET
end),0) OUTCOMERENEVALNET,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID in (9,22,71)) and (IT.STATE > -1)) then
IT.NET
else 0
end),0) DECREASENET,
coalesce(sum(case
when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
IT.NET
when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.STATE > -1)) then
-IT.NET
end),0) COMEAFTERNET,
coalesce(sum(case
when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
and ((OPERATION.OPERATIONTYPE = 0)
or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
IT.NET
else 0
end),0) ARRIVALNET,
coalesce(sum(case
when (((IT.REGDATE <= :ENDDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
and ((OPERATION.OPERATIONTYPE = 0)
or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
IT.NET
else 0
end),0) BEGINNINGNET,
-- Условная усушка
sum(case
when ((:PRELIMINARYSHRINKAGE = 1) and (IT.OPERATIONID = 9) and (IT.STATE = -999)) then
IT.NET
when (:PRELIMINARYSHRINKAGE = 2) then
ITTRANSPORT.HUMWEIGHT
when ((:PRELIMINARYSHRINKAGE in (1,2)) and (IT.OPERATIONID = 101)) then
-IT.NET
else 0
end) HUMWEIGHT,
-- Зачетный вес
coalesce(sum(case
when (IT.REGDATE < :BEGINDATE) then
case
when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
IT.WEIGHT
when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (9,22,100))) then
-IT.WEIGHT
when ((IT.OPERATIONID in (9,22,100)) and (QUALITYWASTE.CLASSID = 141)) then
-IT.WEIGHT
end
end),0) COMEBEFOREWEIGHT,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID in (0,1))) then
IT.WEIGHT
else 0
end),0) INCOMEWEIGHTAVTO,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONTYPE in (0,4))
and (OPERATION.OPERATIONSUBTYPE = 0) and (ITTRANSPORT.TRANSPORTTYPEID = 2)) then
IT.WEIGHT
else 0
end),0) INCOMEWEIGHTRRW,
coalesce(sum(case
when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
and ((OPERATION.OPERATIONTYPE in (0,4))
or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
IT.WEIGHT
else 0
end),0) INCOMEWEIGHT,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
and (IT.NET > 0) and (IT.OPERATIONPARAM is null)) then
IT.WEIGHT
end),0) REINCOMEWEIGHT,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (22,100)))) then
IT.WEIGHT
end),0) OUTCOMEWEIGHT,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 17)
and (IT.NET < 0) and (IT.OPERATIONPARAM is null)) then
-IT.WEIGHT
end),0) REOUTCOMEWEIGHT,
coalesce(sum(case
when ((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6))) then
IT.WEIGHT
when ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)) and (IT.OPERATIONID not in (9,22,100))) then
-IT.WEIGHT
when ((IT.OPERATIONID in (9,22,100)) and (QUALITYWASTE.CLASSID = 141)) then
-IT.WEIGHT
end),0) COMEAFTERWEIGHT,
coalesce(sum(case
when (((IT.REGDATE >= :BEGINDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
and ((OPERATION.OPERATIONTYPE = 0)
or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
IT.WEIGHT
else 0
end),0) ARRIVALWEIGHT,
coalesce(sum(case
when (((IT.REGDATE <= :ENDDATE) and (OPERATION.OPERATIONSUBTYPE = 0))
and ((OPERATION.OPERATIONTYPE = 0)
or ((OPERATION.OPERATIONTYPE = 6) and (not (IT.OPERATIONPARAM is null))))) then
IT.WEIGHT
else 0
end),0) BEGINNINGWEIGHT,
coalesce(sum(case
when (OPERATION.OPERATIONTYPE in (0,4)) and (OPERATION.OPERATIONSUBTYPE = 0)
and (ITTRANSPORT.TRANSPORTTYPEID in (0,1)) then
IT.WEIGHT
else 0
end),0) ALLINCOMEWEIGHT,
-- Для декларации
coalesce(sum(case
when (IT.REGDATE < :BEGINDATE) then
case
when (IT.OPERATIONID in (1,17,18,45)) then
IT.WEIGHT
when ((IT.OPERATIONID in (5,9,22,46,70,71)) and (IT.STATE > -1)) then
-IT.WEIGHT
end
end),0) REMAINDECLARATION,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and (IT.OPERATIONID = 1)) then
IT.WEIGHT
else 0
end),0) INCOMEDECLARATION,
coalesce(sum(case
when ((IT.REGDATE >= :BEGINDATE) and ((IT.OPERATIONID in (5,9,22,70,71)) and (IT.STATE > -1))) then
IT.WEIGHT
end),0) OUTCOMEDECLARATION,
coalesce(sum(case
when (IT.OPERATIONID in (1,17,18,45)) then
IT.WEIGHT
when ((IT.OPERATIONID in (5,9,22,46,70,71)) and (IT.STATE > -1)) then
-IT.WEIGHT
end),0) COMEAFTERDECLARATION,
STORAGE.STORAGENAME,
min(CROP.CROPNAME || ' ' || CLASS.CLASSNAME) CROPCLASSNAME
from IT
join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
join PARTIES on (((PARTIES.PARTYID = IT.PARTYID) and (IT.PARTYID <> -1))
and ((((:INITIALDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,current_date))
or (:ENDDATE between PARTIES.BEGINDATE and coalesce(PARTIES.ENDDATE,current_date))
or (PARTIES.BEGINDATE between :INITIALDATE and :ENDDATE)
or (coalesce(PARTIES.ENDDATE,current_date) between :INITIALDATE and :ENDDATE))
and (:ISCURRENT = 0) and (:ONLYREST <> 3))
or (((PARTIES.BEGINDATE between :INITIALDATE and :ENDDATE)
and (coalesce(PARTIES.ENDDATE,:ENDDATE) between :INITIALDATE and :ENDDATE))
and (:ISCURRENT = 0) and (:ONLYREST = 3))
or ((PARTIES.STATE = 1) and (:ISCURRENT = 1))))
left join UWACTS on (UWACTS.UWORDERID = IT.UWORDERID)
left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join CROP on (CROP.CROPID = QUALITY.CROPID)
left join CLASS on (CLASS.CLASSID = QUALITY.CLASSID)
left join QUALITY_SELECT(coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) on (0=0)
left join QUALITY_CROPFULLNAME_FORAGE(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, QUALITY_SELECT.FORAGECROPID,
(case
when (:CHECKFORAGECLASS = 1) then
QUALITY_SELECT.FORAGECLASSID
else null
end), 1) on (0=0)
left join PACKS on (IT.PACKID = PACKS.PACKID)
left join CLIENTS OWNER on (OWNER.CLIENTID = IT.OWNERID)
left join CLIENTS PROVIDER on (PROVIDER.CLIENTID = IT.PROVIDERID)
left join CLIENTS FORAGECLIENTS on (FORAGECLIENTS.CLIENTID = :OWNERWASTEID)
left join QUALITY QUALITYWASTE on ((QUALITYWASTE.QUALITYID = IT.OPERATIONPARAM) and (IT.OPERATIONID = 22))
left join QUALITYDATA YEARGR on ((YEARGR.QUALITYID = IT.BUHQUALITYID) and (YEARGR.QUALITYTYPEID = 6))
left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
where (((((OPERATION.OPERATIONSUBTYPE = 0) and (OPERATION.OPERATIONTYPE in (0,4,6)))
or ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (0,1,2,4,5)))
or ((OPERATION.OPERATIONSUBTYPE = 5) and (OPERATION.OPERATIONTYPE = 2)))
and (IT.STATE > -1))
or ((OPERATION.OPERATIONSUBTYPE = 1) and (OPERATION.OPERATIONTYPE in (2)) and (IT.STATE = -999)))
and ((IT.OWNERID = :AOWNERID) or (coalesce(:AOWNERID,-1) = -1))
and ((IT.CONTRACTID = :CONTRACTID) or (coalesce(:CONTRACTID,-1) = -1))
-- and (((YEARGR.QUALITYVALUE = coalesce(:YEARHARVESTING,-1)) or (QUALITY_SELECT.HARVESTYEAR = coalesce(:YEARHARVESTING,-1)))
-- or (coalesce(:YEARHARVESTING,-1) = -1))
and (IT.REGDATE <= :ENDDATE)
and ((IT.STORAGEID = :STORAGEID) or (coalesce(:STORAGEID,-1) = -1))
and ((IT.STORINGID = :STORINGID) or (coalesce(:STORINGID,-1) = -1))
and ((CROP.CROPTYPEID <> 3) or (:SHOWWASTE = 1))
and (not coalesce(IT.NET,0) = 0)
group by 1, 2, 3, 4, IT.PARTYID, QUALITY.CROPID, QUALITY.CLASSID, CROP.CROPNAME, CROPFULLNAME, 16,
QUALITY_SELECT.HARVESTYEAR, STORAGE.STORAGENAME
order by 12, CROPFULLNAME, CROPYEAR, 3
)
group by CLIENTID, CLIENTFULLNAME, CLIENTNAME, CLIENTTYPEID, PARTYID,
CROPID, CLASSID, CROPNAME, CROPFULLNAME, PACKNAME, CROPYEAR, STORAGENAME
order by CROPTYPEID, CROPFULLNAME, CROPYEAR, PARTYID, CLIENTNAME
into :OWNERID, :CLIENTFULLNAME, :CLIENTNAME, :CLIENTTYPEID,
:PARTYID, PARTY_BEGINDATE, :PARTY_ENDDATE,
:CROPID, :CLASSID, :CROPNAME, :CROPFULLNAME,
:CROPTYPEID, :CROPYEAR, :CROPGHI, :CLASSGHI,
:PACKNAME,
:REMAINNET, :INCOMENETAVTO, :INCOMENETRRW, :INCOMENET,
:REINCOME, :OUTCOME, :REOUTCOME, :DECREASENET, :RESTNET,
:ARRIVALNET, :BEGINNINGNET, :HUMWEIGHT,
:REMAINWEIGHT, :INCOMEWEIGHTAVTO, :INCOMEWEIGHTRRW, :INCOMEWEIGHT,
:REINCOMEWEIGHT, :OUTCOMEWEIGHT, :REOUTCOMEWEIGHT, :RESTWEIGHT,
:ARRIVALWEIGHT, :BEGINNINGWEIGHT, :ALLINCOMEWEIGHT,
:REMAINDECLARATION, :INCOMEDECLARATION,
:OUTCOMEDECLARATION, :COMEAFTERDECLARATION,
:STORAGENAME, :CROPCLASSNAME
do begin
if (((ONLYREST = 3) and ((BEGINNINGNET <> 0) or (BEGINNINGWEIGHT <> 0)))
or ((ONLYREST = 2) and ((ARRIVALNET <> 0) or (ARRIVALWEIGHT <> 0)))
or ((ONLYREST = 1) and ((RESTNET <> 0) or (RESTWEIGHT <> 0)))
or ((ONLYREST = 0) and ((REMAINNET <> 0) or (RESTNET <> 0) or (INCOMENET <> 0)
or (REINCOME <> 0) or (OUTCOME <> 0) or (REOUTCOME <> 0)
or (RESTNET <> 0) or (HUMWEIGHT <> 0)))) then begin
if (SHOWINGCROPYEAR = 1) then
CROPYEARFULLNAME = CROPFULLNAME || ' врожаю ' || CROPYEAR || ' року';
else CROPYEARFULLNAME = CROPFULLNAME;
suspend;
end
end
end