"localhost:/firebird/data/ILICHEVSK2.FDB". Процедуры

IT_AVG_QUALITY_SHIP_SUMMARY

Входные параметры

Выходные параметры

Описание

(Нет описания для процедуры IT_AVG_QUALITY_SHIP_SUMMARY)

Определение

CREATE PROCEDURE IT_AVG_QUALITY_SHIP_SUMMARY(
    TODATE DATE,
    OWNERID INTEGER,
    CLASSIDCROP INTEGER,
    OPERATIONID INTEGER,
    RELATIONID INTEGER,
    OUTCOMESHIPTYPE INTEGER,
    PROVIDERID INTEGER)
RETURNS (
    COMMENT VARCHAR(15),
    WEIGHT INTEGER,
    DIRT NUMERIC(9,2),
    HUMIDITY NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    GRAINUNIT INTEGER,
    PROTEIN NUMERIC(9,2),
    DROPNUMBER INTEGER,
    ACIDITY NUMERIC(9,2))
AS
declare variable SORTING integer;
begin
  for
/*    select cast(1 as integer),
      cast(' ­   ­ з «®' as varchar(15)),
      cast(sum(IT.NET) as integer),
      sum(IT.NET * QUALITY_SELECT.DIRT) / nullif(sum(IT.NET + QUALITY_SELECT.DIRT - QUALITY_SELECT.DIRT), 0),
      sum(IT.NET * QUALITY_SELECT.HUMIDITY) / nullif(sum(IT.NET + QUALITY_SELECT.HUMIDITY - QUALITY_SELECT.HUMIDITY), 0),
      sum(IT.NET * QUALITY_SELECT.SCREENING) / nullif(sum(IT.NET + QUALITY_SELECT.SCREENING - QUALITY_SELECT.SCREENING), 0),
      sum(IT.NET * QUALITY_SELECT.GRAINUNIT) / nullif(sum(IT.NET + QUALITY_SELECT.GRAINUNIT - QUALITY_SELECT.GRAINUNIT), 0),
      sum(IT.NET * QUALITY_SELECT.PROTEIN) / nullif(sum(IT.NET + QUALITY_SELECT.PROTEIN - QUALITY_SELECT.PROTEIN), 0),
      sum(IT.NET * QUALITY_SELECT.DROPNUMBER) / nullif(sum(IT.NET + QUALITY_SELECT.DROPNUMBER - QUALITY_SELECT.DROPNUMBER), 0)
    from IT
      left join QUALITY on (coalesce(IT.LABQUALITYID, IT.SAMPLERQUALITYID) = QUALITY.QUALITYID)
      left join QUALITY_SELECT(coalesce(IT.LABQUALITYID, IT.SAMPLERQUALITYID)) on (0 = 0)
      left join PARTIES on PARTIES.PARTYID = IT.PARTYID
    where IT.STATE = -100
      and IT.REGDATE < :todate
      and IT.RELATIONID = :RELATIONID
      and IT.OWNERID = :OWNERID
      and QUALITY.classid = :classidcrop
      and IT.OPERATIONID = :OPERATIONID
      and PARTIES.state = 1
    union*/
    select 2,
        DATE_TO_STR.DATESTR, IT.NET,
        (IT.NET * QUALITY_SELECT.DIRT) / nullif(IT.NET + QUALITY_SELECT.DIRT - QUALITY_SELECT.DIRT, 0) ,
        (IT.NET * QUALITY_SELECT.HUMIDITY) / nullif(IT.NET + QUALITY_SELECT.HUMIDITY - QUALITY_SELECT.HUMIDITY, 0),
        (IT.NET * QUALITY_SELECT.SCREENING) / nullif(IT.NET + QUALITY_SELECT.SCREENING - QUALITY_SELECT.SCREENING, 0),
        (IT.NET * QUALITY_SELECT.GRAINUNIT) / nullif(IT.NET + QUALITY_SELECT.GRAINUNIT - QUALITY_SELECT.GRAINUNIT, 0),
        (IT.NET * QUALITY_SELECT.PROTEIN) / nullif(IT.NET + QUALITY_SELECT.PROTEIN - QUALITY_SELECT.PROTEIN, 0),
        (IT.NET * QUALITY_SELECT.DROPNUMBER) / nullif(IT.NET + QUALITY_SELECT.DROPNUMBER - QUALITY_SELECT.DROPNUMBER, 0),
        (IT.NET * QUALITY_SELECT.ACIDITY) / nullif(IT.NET + QUALITY_SELECT.ACIDITY - QUALITY_SELECT.ACIDITY, 0)
      from IT
        left join QUALITY on (QUALITY.QUALITYID = coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID))
        left join QUALITY_SELECT(coalesce(IT.LABQUALITYID,IT.SAMPLERQUALITYID)) on (0 = 0)
        left join PARTIES on (PARTIES.PARTYID = IT.PARTYID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join DATE_TO_STR(IT.REGDATE) on (0 = 0)
      where (((IT.STATE = -100) and (coalesce(:OUTCOMESHIPTYPE,0) = 1)) or ((IT.STATE >= 0) and (coalesce(:OUTCOMESHIPTYPE,0) = 0)))
        and (IT.REGDATE <= :TODATE)
        and (IT.OWNERID = :OWNERID)
        and (QUALITY.CLASSID = :CLASSIDCROP)
        and (IT.OPERATIONID = :OPERATIONID)
        and (PARTIES.STATE > 0)
        and (IT.RELATIONID = :RELATIONID)
        and ((IT.PROVIDERID = :PROVIDERID) or (coalesce(:PROVIDERID,0) = 0))
    order by IT.REGDATE
    into :SORTING, :COMMENT, :WEIGHT, :DIRT, :HUMIDITY, :SCREENING, :GRAINUNIT,
         :PROTEIN, :DROPNUMBER, :ACIDITY

  do suspend;

end