| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
UNDERWORK_GET_IT_COMPONENT
| "localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры |
|
UNDERWORK_GET_IT_COMPONENT
Входные параметры
Параметр | Тип | Описание |
|---|---|---|
UWTYPEID |
INTEGER |
|
BEGDATE |
DATE |
|
ENDDATE |
DATE |
|
CROPID |
INTEGER |
|
CLASSID |
INTEGER |
|
AUWORDERID |
INTEGER |
|
CLIENTID |
INTEGER |
|
STORAGEID |
INTEGER |
|
SORTID |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
|---|---|---|
REGDATE |
DATE |
|
CROPFULLNAME |
VARCHAR(100) |
|
NET |
INTEGER |
|
INVENTORYID |
INTEGER |
|
UWORDERID |
INTEGER |
|
SILAGENAME |
VARCHAR(30) |
|
LABQUALITYID |
INTEGER |
|
CLIENTNAME |
VARCHAR(30) |
|
OUTCROPID |
INTEGER |
|
OPERATIONID |
INTEGER |
|
OUTCLASSID |
INTEGER |
|
QUALITYNUMBER |
VARCHAR(15) |
|
WASTECROPID |
INTEGER |
|
Описание
(Нет описания для процедуры UNDERWORK_GET_IT_COMPONENT)
Определение
CREATE PROCEDURE UNDERWORK_GET_IT_COMPONENT(
UWTYPEID INTEGER,
BEGDATE DATE,
ENDDATE DATE,
CROPID INTEGER,
CLASSID INTEGER,
AUWORDERID INTEGER,
CLIENTID INTEGER,
STORAGEID INTEGER,
SORTID INTEGER)
RETURNS (
REGDATE DATE,
CROPFULLNAME VARCHAR(100),
NET INTEGER,
INVENTORYID INTEGER,
UWORDERID INTEGER,
SILAGENAME VARCHAR(30),
LABQUALITYID INTEGER,
CLIENTNAME VARCHAR(30),
OUTCROPID INTEGER,
OPERATIONID INTEGER,
OUTCLASSID INTEGER,
QUALITYNUMBER VARCHAR(15),
WASTECROPID INTEGER)
AS
declare variable MINNUMBER integer;
declare variable MAXNUMBER integer;
begin
UWORDERID = :AUWORDERID;
if (:AUWORDERID is not null) then begin
if (:UWTYPEID in (3,34)) then begin
for
select
cast(min(IT.REGDATE) as date), cast(min(QUALITY_CROPFULLNAME.CROPFULLNAME) as varchar(100)),
cast(sum(IT.NET) as integer), cast(INVOICEREGISTERS.INVOICEREGISTERID as integer),
cast(min(IT.UWORDERID) as integer), cast(min(INVOICEREGISTERS.REGISTERNUMBER) as varchar(10)),
cast(min(IT.LABQUALITYID) as integer),
cast(min(CLIENTS.CLIENTNAME) as varchar(30)), cast(min(QUALITY.CROPID) as integer),
cast(min(IT.OPERATIONID) as integer), cast(min(QUALITY.CROPID) as integer),
cast(min(QUALITYDATA.QUALITYVALUE) as integer), cast(max(QUALITYDATA.QUALITYVALUE) as integer),
cast(min(WASTECROPQUALITY.QUALITYVALUE) as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
-- where (IT.REGDATE between :BEGDATE and :ENDDATE)
-- and (IT.OPERATIONID = 1) and (IT.UWORDERID = :AUWORDERID)
where (IT.OPERATIONID = 1) and (IT.UWORDERID = :AUWORDERID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and ((INVOICEREGISTERS.INVOICEREGISTERID is not null) or (IT.OPERATIONID = 5))
group by INVOICEREGISTERS.INVOICEREGISTERID
union
select
IT.REGDATE, QUALITY_CROPFULLNAME.CROPFULLNAME, IT.NET, IT.INVENTORYID,
IT.UWORDERID, cast(INVOICEREGISTERS.REGISTERNUMBER as varchar(10)), IT.LABQUALITYID,
CLIENTS.CLIENTNAME, QUALITY.CROPID, IT.OPERATIONID, QUALITY.CROPID,
cast(QUALITYDATA.QUALITYVALUE as integer), cast(QUALITYDATA.QUALITYVALUE as integer),
cast(WASTECROPQUALITY.QUALITYVALUE as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 5) and (IT.UWORDERID = :AUWORDERID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and ((INVOICEREGISTERS.INVOICEREGISTERID is not null) or (IT.OPERATIONID = 5))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :UWORDERID, :SILAGENAME,
:LABQUALITYID, :CLIENTNAME, :OUTCROPID, :OPERATIONID, :OUTCLASSID,
:MINNUMBER, :MAXNUMBER, :WASTECROPID
do begin
if (:MINNUMBER = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER;
end
else begin
if (:MINNUMBER + 1 = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER || ',' || :MAXNUMBER;
end
else begin
QUALITYNUMBER = :MINNUMBER || ',' || :MAXNUMBER || '...';
end
end
suspend;
end
end
else
if (:UWTYPEID in (90)) then begin
for
select
cast(min(IT.REGDATE) as date), cast(min(QUALITY_CROPFULLNAME.CROPFULLNAME) as varchar(100)),
cast(sum(IT.NET) - coalesce(min((select sum(coalesce(ITUW.NET,0) + coalesce(ITUW.WEIGHT,0))
from IT ITUW
where (ITUW.INVOICEID = IT.INVOICEID) and (ITUW.UWORDERID = IT.UWORDERID)
and (ITUW.STATE = -999)
group by ITUW.INVOICEID)),0) as integer),
cast(INVOICEREGISTERS.INVOICEREGISTERID as integer), cast(min(IT.UWORDERID) as integer),
cast(min(INVOICEREGISTERS.REGISTERNUMBER) as varchar(10)), cast(min(INVOICEREGISTERS.QUALITYID) as integer),
cast(min(CLIENTS.CLIENTNAME) as varchar(30)), cast(min(QUALITY.CROPID) as integer),
cast(min(IT.OPERATIONID) as integer), cast(min(QUALITY.CROPID) as integer),
cast(min(QUALITYDATA.QUALITYVALUE) as integer), cast(max(QUALITYDATA.QUALITYVALUE) as integer),
cast(min(WASTECROPQUALITY.QUALITYVALUE) as integer)
from IT
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = INVOICEREGISTERS.QUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.OPERATIONID = 1) and (IT.SUWORDERID = :AUWORDERID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and ((INVOICEREGISTERS.INVOICEREGISTERID is not null) or (IT.OPERATIONID = 5))
group by INVOICEREGISTERS.INVOICEREGISTERID
union
select
IT.REGDATE, QUALITY_CROPFULLNAME.CROPFULLNAME, IT.NET, IT.INVENTORYID,
IT.UWORDERID, cast(INVOICEREGISTERS.REGISTERNUMBER as varchar(10)), IT.LABQUALITYID,
CLIENTS.CLIENTNAME, QUALITY.CROPID, IT.OPERATIONID, QUALITY.CROPID,
cast(QUALITYDATA.QUALITYVALUE as integer), cast(QUALITYDATA.QUALITYVALUE as integer),
cast(WASTECROPQUALITY.QUALITYVALUE as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 5) and (IT.UWORDERID = :AUWORDERID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and ((INVOICEREGISTERS.INVOICEREGISTERID is not null) or (IT.OPERATIONID = 5))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :UWORDERID, :SILAGENAME,
:LABQUALITYID, :CLIENTNAME, :OUTCROPID, :OPERATIONID, :OUTCLASSID,
:MINNUMBER, :MAXNUMBER, :WASTECROPID
do begin
if (:MINNUMBER = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER;
end
else begin
if (:MINNUMBER + 1 = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER || ',' || :MAXNUMBER;
end
else begin
QUALITYNUMBER = :MINNUMBER || ',' || :MAXNUMBER || '...';
end
end
suspend;
end
end
else
if (:UWTYPEID in (91)) then begin
for
select
cast(min(IT.REGDATE) as date), cast(min(QUALITY_CROPFULLNAME.CROPFULLNAME) as varchar(100)),
cast(sum(IT.NET) as integer), cast(INVOICEREGISTERS.INVOICEREGISTERID as integer),
cast(min(IT.UWORDERID) as integer), cast(min(INVOICEREGISTERS.REGISTERNUMBER) as varchar(10)),
cast(min(INVOICEREGISTERS.QUALITYID) as integer),
cast(min(CLIENTS.CLIENTNAME) as varchar(30)), cast(min(QUALITY.CROPID) as integer),
cast(min(IT.OPERATIONID) as integer), cast(min(QUALITY.CROPID) as integer),
cast(min(QUALITYDATA.QUALITYVALUE) as integer), cast(max(QUALITYDATA.QUALITYVALUE) as integer),
cast(min(WASTECROPQUALITY.QUALITYVALUE) as integer)
from IT
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
-- where (IT.REGDATE between :BEGDATE and :ENDDATE)
-- and (IT.OPERATIONID = 1) and (IT.UWORDERID = :AUWORDERID)
where (IT.OPERATIONID = 1) and (IT.RELATIONID = :AUWORDERID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and ((INVOICEREGISTERS.INVOICEREGISTERID is not null) or (IT.OPERATIONID = 5))
group by INVOICEREGISTERS.INVOICEREGISTERID
union
select
IT.REGDATE, QUALITY_CROPFULLNAME.CROPFULLNAME, IT.NET, IT.INVENTORYID,
IT.UWORDERID, cast(INVOICEREGISTERS.REGISTERNUMBER as varchar(10)), IT.LABQUALITYID,
CLIENTS.CLIENTNAME, QUALITY.CROPID, IT.OPERATIONID, QUALITY.CROPID,
cast(QUALITYDATA.QUALITYVALUE as integer), cast(QUALITYDATA.QUALITYVALUE as integer),
cast(WASTECROPQUALITY.QUALITYVALUE as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 5) and (IT.UWORDERID = :AUWORDERID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and ((INVOICEREGISTERS.INVOICEREGISTERID is not null) or (IT.OPERATIONID = 5))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :UWORDERID, :SILAGENAME,
:LABQUALITYID, :CLIENTNAME, :OUTCROPID, :OPERATIONID, :OUTCLASSID,
:MINNUMBER, :MAXNUMBER, :WASTECROPID
do begin
if (:MINNUMBER = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER;
end
else begin
if (:MINNUMBER + 1 = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER || ',' || :MAXNUMBER;
end
else begin
QUALITYNUMBER = :MINNUMBER || ',' || :MAXNUMBER || '...';
end
end
suspend;
end
end
else begin
for
select
ITMAIN.REGDATE, QUALITY_CROPFULLNAME.CROPFULLNAME, ITMAIN.NET, ITMAIN.INVENTORYID,
ITMAIN.UWORDERID, SILAGE.NAME, ITMAIN.LABQUALITYID, QUALITY.CROPID, ITMAIN.OPERATIONID,
QUALITY.CLASSID, cast(QUALITYDATA.QUALITYVALUE as integer), cast (WASTECROPQUALITY.QUALITYVALUE as integer),
CLIENTS.CLIENTNAME
from IT ITMAIN
left join QUALITY on (QUALITY.QUALITYID = ITMAIN.SAMPLERQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = ITMAIN.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = ITMAIN.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
left join SILAGE on (SILAGE.SILAGEID = ITMAIN.SILAGEID)
left join CLIENTS on (CLIENTS.CLIENTID = ITMAIN.OWNERID)
where (ITMAIN.REGDATE between :BEGDATE and :ENDDATE)
and (((:UWTYPEID = 1) and (ITMAIN.OPERATIONID in (5,7,14,29,30,36)))
or ((:UWTYPEID = 2) and (ITMAIN.OPERATIONID = 31))
or ((:UWTYPEID = 5) and (ITMAIN.OPERATIONID = 12))
or ((:UWTYPEID = 93) and (ITMAIN.OPERATIONID = 5)))
and (ITMAIN.UWORDERID = :AUWORDERID)
and (ITMAIN.STATE <> -1)
and ((ITMAIN.PARENTID <= 0) or (0 = (select PARENTID from IT IT2 where IT2.INVENTORYID = ITMAIN.PARENTID)))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID,
:UWORDERID, :SILAGENAME, :LABQUALITYID, :OUTCROPID, :OPERATIONID,
:OUTCLASSID, :QUALITYNUMBER, :WASTECROPID, :CLIENTNAME
do suspend;
end
end
else begin
if (:UWTYPEID = 1) then begin
for
select
ITMAIN.REGDATE, QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME, ITMAIN.NET, ITMAIN.INVENTORYID,
ITMAIN.UWORDERID, SILAGE.NAME, ITMAIN.LABQUALITYID, QUALITY.CROPID, ITMAIN.OPERATIONID,
QUALITY.CLASSID, cast(QUALITYDATA.QUALITYVALUE as integer), cast (WASTECROPQUALITY.QUALITYVALUE as integer)
from IT ITMAIN
left join QUALITY on (QUALITY.QUALITYID = ITMAIN.SAMPLERQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = ITMAIN.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = ITMAIN.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_SELECT(ITMAIN.SAMPLERQUALITYID) on (0=0)
left join QUALITY_CROPFULLNAME_FORAGE(QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
left join SILAGE on (SILAGE.SILAGEID = ITMAIN.SILAGEID)
where (ITMAIN.REGDATE between :BEGDATE and :ENDDATE) and (ITMAIN.OPERATIONID in (5,7,14,29,30,36))
and (ITMAIN.UWORDERID is null) and (ITMAIN.STORAGEID = :STORAGEID) and (ITMAIN.STATE <> -1)
and (ITMAIN.PARENTID <= 0 or (0 = (select PARENTID from IT IT2 where IT2.INVENTORYID = ITMAIN.PARENTID)))
and (((QUALITY.CROPID = :CROPID)
and ((((:CLASSID is null) or (QUALITY.CLASSID = :CLASSID))
and ((:SORTID is null) or (QUALITY.SORTID = :SORTID))
and exists(select uwoutacts.invoiceid from uwoutacts where uwoutacts.invoiceid = ITMAIN.invoiceid))
or (ITMAIN.OPERATIONID in (5,14))))
or (QUALITY.CROPID = 9))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :UWORDERID, :SILAGENAME,
:LABQUALITYID, :OUTCROPID, :OPERATIONID, :OUTCLASSID, :QUALITYNUMBER, :WASTECROPID
do suspend;
end
if (:UWTYPEID = 2) then begin
for
select
IT.REGDATE, QUALITY_CROPFULLNAME.CROPFULLNAME, IT.NET, IT.INVENTORYID,
IT.UWORDERID, SILAGE.NAME, IT.SAMPLERQUALITYID, QUALITY.CROPID, IT.OPERATIONID,
QUALITY.CLASSID, cast(QUALITYDATA.QUALITYVALUE as integer), CLIENTS.CLIENTNAME
from IT
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.SAMPLERQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on 0=0
left join SILAGE on (SILAGE.SILAGEID = IT.SILAGEID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.OPERATIONID = 31)
and (IT.UWORDERID is null) and (IT.STORAGEID = :STORAGEID)
and (IT.STATE <> -1) and (IT.PARENTID <= 0)
and (QUALITY.CROPID = :CROPID)
and ((:CLASSID is null) or (QUALITY.CLASSID = :CLASSID))
and ((:SORTID is null) or (QUALITY.SORTID = :SORTID))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :UWORDERID, :SILAGENAME,
:LABQUALITYID, :OUTCROPID, :OPERATIONID, :OUTCLASSID, :QUALITYNUMBER,
:CLIENTNAME
do suspend;
end
if (:UWTYPEID = 5) then begin
for
select
IT.REGDATE, QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME, IT.NET, IT.INVENTORYID,
IT.UWORDERID, SILAGE.NAME, IT.LABQUALITYID, QUALITY.CROPID, IT.OPERATIONID,
QUALITY.CLASSID, cast(QUALITYDATA.QUALITYVALUE as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITY_SELECT(IT.SAMPLERQUALITYID) on (0=0)
left join QUALITY_CROPFULLNAME_FORAGE(QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
left join SILAGE on (SILAGE.SILAGEID = IT.SILAGEID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 12)
and (IT.UWORDERID is null)
and (IT.STORAGEID = :STORAGEID)
and (IT.STATE <> -1)
and (IT.PARENTID <= 0)
and (QUALITY.CROPID = :CROPID)
and ((:CLASSID is null) or (QUALITY.CLASSID = :CLASSID))
and ((:SORTID is null) or (QUALITY.SORTID = :SORTID))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :UWORDERID, :SILAGENAME,
:LABQUALITYID, :OUTCROPID, :OPERATIONID, :OUTCLASSID, :QUALITYNUMBER
do suspend;
end
if (:UWTYPEID in (3,34)) then begin
for
select
min(IT.REGDATE), cast(min(QUALITY_CROPFULLNAME.CROPFULLNAME) as varchar(100)),
cast(sum(IT.NET) as integer), INVOICEREGISTERS.INVOICEREGISTERID,
cast(min(INVOICEREGISTERS.REGISTERNUMBER) as varchar(10)), cast(min(IT.LABQUALITYID) as integer),
cast(min(CLIENTS.CLIENTNAME) as varchar(30)), cast(min(QUALITY.CROPID) as integer),
cast(min(IT.OPERATIONID) as integer), cast(min(QUALITY.CLASSID) as integer),
-- cast(null as integer), cast(min(QUALITYDATA.QUALITYVALUE) as integer),
cast(min(IT.UWORDERID) as integer), cast(min(QUALITYDATA.QUALITYVALUE) as integer),
cast(max(QUALITYDATA.QUALITYVALUE) as integer),
cast(min(WASTECROPQUALITY.QUALITYVALUE) as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
-- left join IT ITW on ((ITW.REGISTERID = IT.REGISTERID) and (ITW.OPERATIONID = 4) and (ITW.STATE = -999))
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 1) and (IT.UWORDERID is null)
and (IT.STORAGEID = :STORAGEID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and (INVOICEREGISTERS.INVOICEREGISTERID is not null)
and (((QUALITY.CROPID = :CROPID)
and ((coalesce(:CLASSID,-1) = -1) or (QUALITY.CLASSID = :CLASSID))
and ((coalesce(:SORTID,-1) = -1) or (QUALITY.SORTID = :SORTID)))
or (QUALITY.CROPID = 9))
and ((coalesce(:CLIENTID,-1) = -1) or (IT.OWNERID = :CLIENTID))
-- and (coalesce(abs(ITW.NET),0) = 0)
group by INVOICEREGISTERS.INVOICEREGISTERID
union
select
IT.REGDATE, QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME, IT.NET, IT.INVENTORYID,
cast(INVOICEREGISTERS.REGISTERNUMBER as varchar(10)), (IT.LABQUALITYID),
CLIENTS.CLIENTNAME, QUALITY.CROPID, IT.OPERATIONID, QUALITY.CLASSID, cast(:AUWORDERID as integer),
cast(QUALITYDATA.QUALITYVALUE as integer),
cast(QUALITYDATA.QUALITYVALUE as integer),
cast(WASTECROPQUALITY.QUALITYVALUE as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID))
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_SELECT(coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) on (0=0)
left join QUALITY_CROPFULLNAME_FORAGE(QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 5) and (IT.STORAGEID = :STORAGEID)
and (IT.UWORDERID is null) and (IT.NET is not null)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and (((QUALITY.CROPID = :CROPID)
and ((coalesce(:CLASSID,-1) = -1) or (QUALITY.CLASSID = :CLASSID))
and ((coalesce(:SORTID,-1) = -1) or (QUALITY.SORTID = :SORTID)))
or (QUALITY.CROPID = 9))
-- and (:CLIENTID is null or IT.providerid = :CLIENTID)
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :SILAGENAME, :LABQUALITYID,
:CLIENTNAME, :OUTCROPID, :OPERATIONID, :OUTCLASSID, :UWORDERID, :MINNUMBER,
:MAXNUMBER, :WASTECROPID
do begin
if (:MINNUMBER = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER;
end
else begin
if (:MINNUMBER + 1 = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER || ',' || :MAXNUMBER;
end
else begin
QUALITYNUMBER = :MINNUMBER || ',' || :MAXNUMBER || '...';
end
end
suspend;
end
end
if (:UWTYPEID = 90) then begin
for
select
min(IT.REGDATE), cast(min(QUALITY_CROPFULLNAME.CROPFULLNAME) as varchar(100)),
cast(sum(IT.NET) - coalesce(min((select sum(coalesce(ITUW.NET,0) + coalesce(ITUW.WEIGHT,0))
from IT ITUW
where (ITUW.INVOICEID = IT.INVOICEID) and (ITUW.UWORDERID = IT.UWORDERID)
and (ITUW.STATE = -999)
group by ITUW.INVOICEID)),0) as integer),
INVOICEREGISTERS.INVOICEREGISTERID, cast(min(INVOICEREGISTERS.REGISTERNUMBER) as varchar(10)),
cast(min(case
when (IT.UWORDERID is null) then
IT.LABQUALITYID
else INVOICEREGISTERS.QUALITYID
end) as integer),
cast(min(CLIENTS.CLIENTNAME) as varchar(30)), cast(min(QUALITY.CROPID) as integer),
cast(min(IT.OPERATIONID) as integer), cast(min(QUALITY.CLASSID) as integer),
cast(null as integer), cast(min(QUALITYDATA.QUALITYVALUE) as integer),
cast(max(QUALITYDATA.QUALITYVALUE) as integer), cast(min(WASTECROPQUALITY.QUALITYVALUE) as integer)
from IT
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = INVOICEREGISTERS.QUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 1) and (IT.SUWORDERID is null)
and (IT.STORAGEID = :STORAGEID) and (IT.STATE > -1) and (IT.PARENTID <= 0)
and (INVOICEREGISTERS.INVOICEREGISTERID is not null)
and (((QUALITY.CROPID = :CROPID)
and ((coalesce(:CLASSID,-1) = -1) or (QUALITY.CLASSID = :CLASSID))
and ((coalesce(:SORTID,-1) = -1) or (QUALITY.SORTID = :SORTID)))
or (QUALITY.CROPID = 9))
and ((coalesce(:CLIENTID,-1) = -1) or (IT.OWNERID = :CLIENTID))
group by INVOICEREGISTERS.INVOICEREGISTERID
union
select
IT.REGDATE, QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME, IT.NET, IT.INVENTORYID,
cast(INVOICEREGISTERS.REGISTERNUMBER as varchar(10)), (IT.LABQUALITYID),
CLIENTS.CLIENTNAME, QUALITY.CROPID, IT.OPERATIONID, QUALITY.CLASSID, cast(:AUWORDERID as integer),
cast(QUALITYDATA.QUALITYVALUE as integer),
cast(QUALITYDATA.QUALITYVALUE as integer),
cast(WASTECROPQUALITY.QUALITYVALUE as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID))
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_SELECT(coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) on (0=0)
left join QUALITY_CROPFULLNAME_FORAGE(QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
left join INVOICEREGISTERS on (IT.REGISTERID = INVOICEREGISTERS.INVOICEREGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 5) and (IT.STORAGEID = :STORAGEID)
and (IT.UWORDERID is null) and (IT.SUWORDERID is null) and (IT.NET is not null)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and (((QUALITY.CROPID = :CROPID)
and ((coalesce(:CLASSID,-1) = -1) or (QUALITY.CLASSID = :CLASSID))
and ((coalesce(:SORTID,-1) = -1) or (QUALITY.SORTID = :SORTID)))
or (QUALITY.CROPID = 9))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :SILAGENAME, :LABQUALITYID,
:CLIENTNAME, :OUTCROPID, :OPERATIONID, :OUTCLASSID, :UWORDERID, :MINNUMBER,
:MAXNUMBER, :WASTECROPID
do begin
if (:MINNUMBER = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER;
end
else begin
if (:MINNUMBER + 1 = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER || ',' || :MAXNUMBER;
end
else begin
QUALITYNUMBER = :MINNUMBER || ',' || :MAXNUMBER || '...';
end
end
suspend;
end
end
if (:UWTYPEID = 91) then begin
for
select
min(IT.REGDATE), cast(min(QUALITY_CROPFULLNAME.CROPFULLNAME) as varchar(100)),
cast(sum(IT.WEIGHT) as integer), INVOICEREGISTERS.INVOICEREGISTERID,
cast(min(INVOICEREGISTERS.REGISTERNUMBER) as varchar(10)), cast(min(INVOICEREGISTERS.QUALITYID) as integer),
cast(min(CLIENTS.CLIENTNAME) as varchar(30)), cast(min(QUALITY.CROPID) as integer),
cast(min(IT.OPERATIONID) as integer) OPERATIONID, cast(min(QUALITY.CLASSID) as integer),
cast(null as integer), cast(min(QUALITYDATA.QUALITYVALUE) as integer),
cast(max(QUALITYDATA.QUALITYVALUE) as integer),
cast(min(WASTECROPQUALITY.QUALITYVALUE) as integer)
from IT
left join INVOICEREGISTERS on (INVOICEREGISTERS.INVOICEREGISTERID = IT.REGISTERID)
left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = IT.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = IT.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 0) on (0=0)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
left join IT ITW on ((ITW.REGISTERID = IT.REGISTERID) and (ITW.OPERATIONID = 4) and (ITW.STATE = -999))
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 1) and (IT.RELATIONID is null)
and (IT.STORAGEID = :STORAGEID)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and (INVOICEREGISTERS.INVOICEREGISTERID is not null)
and (((QUALITY.CROPID = :CROPID)
and ((coalesce(:CLASSID,-1) = -1) or (QUALITY.CLASSID = :CLASSID))
and ((coalesce(:SORTID,-1) = -1) or (QUALITY.SORTID = :SORTID)))
or (QUALITY.CROPID = 9))
and ((coalesce(:CLIENTID,-1) = -1) or (IT.OWNERID = :CLIENTID))
and (coalesce(abs(ITW.NET),0) = 0)
group by INVOICEREGISTERS.INVOICEREGISTERID
union
select
IT.REGDATE, QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME, IT.NET, IT.INVENTORYID,
cast(INVOICEREGISTERS.REGISTERNUMBER as varchar(10)), (IT.LABQUALITYID),
CLIENTS.CLIENTNAME, QUALITY.CROPID, IT.OPERATIONID, QUALITY.CLASSID, cast(:AUWORDERID as integer),
cast(QUALITYDATA.QUALITYVALUE as integer),
cast(QUALITYDATA.QUALITYVALUE as integer),
cast(WASTECROPQUALITY.QUALITYVALUE as integer)
from IT
left join QUALITY on (QUALITY.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID))
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_SELECT(coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) on (0=0)
left join QUALITY_CROPFULLNAME_FORAGE(QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
left join INVOICEREGISTERS on (IT.REGISTERID = INVOICEREGISTERS.INVOICEREGISTERID)
left join CLIENTS on (CLIENTS.CLIENTID = IT.OWNERID)
where (IT.REGDATE between :BEGDATE and :ENDDATE)
and (IT.OPERATIONID = 5) and (IT.STORAGEID = :STORAGEID)
and (IT.UWORDERID is null) and (IT.NET is not null)
and (IT.STATE > -1) and (IT.PARENTID <= 0)
and (((QUALITY.CROPID = :CROPID)
and ((coalesce(:CLASSID,-1) = -1) or (QUALITY.CLASSID = :CLASSID))
and ((coalesce(:SORTID,-1) = -1) or (QUALITY.SORTID = :SORTID)))
or (QUALITY.CROPID = 9))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :SILAGENAME, :LABQUALITYID,
:CLIENTNAME, :OUTCROPID, :OPERATIONID, :OUTCLASSID, :UWORDERID, :MINNUMBER,
:MAXNUMBER, :WASTECROPID
do begin
if (:MINNUMBER = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER;
end
else begin
if (:MINNUMBER + 1 = :MAXNUMBER) then begin
QUALITYNUMBER = MINNUMBER || ',' || :MAXNUMBER;
end
else begin
QUALITYNUMBER = :MINNUMBER || ',' || :MAXNUMBER || '...';
end
end
suspend;
end
end
if (:UWTYPEID = 93) then begin
for
select
ITMAIN.REGDATE, QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME, ITMAIN.NET, ITMAIN.INVENTORYID,
ITMAIN.UWORDERID, SILAGE.NAME, ITMAIN.LABQUALITYID, QUALITY.CROPID, ITMAIN.OPERATIONID,
QUALITY.CLASSID, cast(QUALITYDATA.QUALITYVALUE as integer),
cast(WASTECROPQUALITY.QUALITYVALUE as integer)
from IT ITMAIN
left join QUALITY on (QUALITY.QUALITYID = ITMAIN.SAMPLERQUALITYID)
left join QUALITYDATA on ((QUALITYDATA.QUALITYID = ITMAIN.LABQUALITYID) and (QUALITYDATA.QUALITYTYPEID = 1))
left join QUALITYDATA WASTECROPQUALITY on ((WASTECROPQUALITY.QUALITYID = ITMAIN.LABQUALITYID) and (WASTECROPQUALITY.QUALITYTYPEID = 3220))
left join QUALITY_SELECT(ITMAIN.SAMPLERQUALITYID) on (0=0)
left join QUALITY_CROPFULLNAME_FORAGE(QUALITY_SELECT.CROPID, QUALITY_SELECT.CLASSID, QUALITY_SELECT.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
left join SILAGE on (SILAGE.SILAGEID = ITMAIN.SILAGEID)
where (ITMAIN.REGDATE between :BEGDATE and :ENDDATE)
and (ITMAIN.OPERATIONID = 5) and (ITMAIN.STORAGEID = :STORAGEID)
and (ITMAIN.UWORDERID is null) and (ITMAIN.NET is not null)
and (ITMAIN.STATE > -1) and (ITMAIN.PARENTID <= 0)
and (((QUALITY.CROPID = :CROPID)
and ((coalesce(:CLASSID,-1) = -1) or (QUALITY.CLASSID = :CLASSID))
and ((coalesce(:SORTID,-1) = -1) or (QUALITY.SORTID = :SORTID)))
or (QUALITY.CROPID = 9))
into :REGDATE, :CROPFULLNAME, :NET, :INVENTORYID, :UWORDERID, :SILAGENAME,
:LABQUALITYID, :OUTCROPID, :OPERATIONID, :OUTCLASSID, :QUALITYNUMBER, :WASTECROPID
do suspend;
end
end
end
|
|