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

IT_AVG_QUALITY_SHIP_SUMMARY_WHY

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

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

Описание

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

Определение

CREATE PROCEDURE IT_AVG_QUALITY_SHIP_SUMMARY_WHY(
    TODATE DATE,
    OWNERID INTEGER,
    CLASSIDCROP INTEGER,
    OPERATIONID INTEGER,
    RELATIONID 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,
    GLUTEN NUMERIC(9,2),
    TURTLEBUG NUMERIC(9,2),
    GLUTENQUALITY INTEGER,
    GLUTENGROUP INTEGER,
    SMALLGRAIN NUMERIC(9,2),
    BEATEN NUMERIC(9,2),
    GLUKOZIN NUMERIC(9,2),
    ERUKACID NUMERIC(9,2),
    ACIDITY NUMERIC(9,2),
    HARVESTYEAR NUMERIC(9,2))
AS
declare variable SORTING integer;
begin
  for
    select 1, min('на ' || DATE_TO_STR.DATESTR), sum(IT.NET),
        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),
        sum(IT.NET * QUALITY_SELECT.GLUTEN) / nullif(sum(IT.NET + QUALITY_SELECT.GLUTEN - QUALITY_SELECT.GLUTEN), 0),
        sum(IT.NET * QUALITY_SELECT.GLUTENGROUP) / nullif(sum(IT.NET + QUALITY_SELECT.GLUTENGROUP - QUALITY_SELECT.GLUTENGROUP), 0),
        sum(IT.NET * QUALITY_SELECT.GLUTENQUALITY) / nullif(sum(IT.NET + QUALITY_SELECT.GLUTENQUALITY - QUALITY_SELECT.GLUTENQUALITY), 0),
        sum(IT.NET * QUALITY_SELECT.TURTLEBUG) / nullif(sum(IT.NET + QUALITY_SELECT.TURTLEBUG - QUALITY_SELECT.TURTLEBUG), 0),
        sum(IT.NET * QUALITY_SELECT.SMALLGRAIN) / nullif(sum(IT.NET + QUALITY_SELECT.SMALLGRAIN - QUALITY_SELECT.SMALLGRAIN), 0),
        sum(IT.NET * QUALITY_SELECT.ACIDITY) / nullif(sum(IT.NET + QUALITY_SELECT.ACIDITY - QUALITY_SELECT.ACIDITY), 0),
        sum(IT.NET * QUALITY_SELECT.BEATEN) / nullif(sum(IT.NET + QUALITY_SELECT.BEATEN - QUALITY_SELECT.BEATEN), 0),
        sum(IT.NET * QUALITY_SELECT.GLUKOZINILAT) / nullif(sum(IT.NET + QUALITY_SELECT.GLUKOZINILAT - QUALITY_SELECT.GLUKOZINILAT), 0),
        sum(IT.NET * QUALITY_SELECT.ERUKOVA_KISLOTA) / nullif(sum(IT.NET + QUALITY_SELECT.ERUKOVA_KISLOTA - QUALITY_SELECT.ERUKOVA_KISLOTA), 0),
        min(QUALITY_SELECT.HARVESTYEAR)
      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 DATE_TO_STR(:TODATE) on (0=0)
      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.GLUTEN) / nullif(IT.NET + QUALITY_SELECT.GLUTEN - QUALITY_SELECT.GLUTEN, 0),
          (IT.NET * QUALITY_SELECT.GLUTENGROUP) / nullif(IT.NET + QUALITY_SELECT.GLUTENGROUP - QUALITY_SELECT.GLUTENGROUP, 0),
          (IT.NET * QUALITY_SELECT.GLUTENQUALITY) / nullif(IT.NET + QUALITY_SELECT.GLUTENQUALITY - QUALITY_SELECT.GLUTENQUALITY, 0),
          (IT.NET * QUALITY_SELECT.TURTLEBUG) / nullif(IT.NET + QUALITY_SELECT.TURTLEBUG - QUALITY_SELECT.TURTLEBUG, 0),
          (IT.NET * QUALITY_SELECT.SMALLGRAIN) / nullif(IT.NET + QUALITY_SELECT.SMALLGRAIN - QUALITY_SELECT.SMALLGRAIN, 0),
          (IT.NET * QUALITY_SELECT.ACIDITY) / nullif(IT.NET + QUALITY_SELECT.ACIDITY - QUALITY_SELECT.ACIDITY, 0),
          (IT.NET * QUALITY_SELECT.BEATEN) / nullif(IT.NET + QUALITY_SELECT.BEATEN - QUALITY_SELECT.BEATEN, 0),
          (IT.NET * QUALITY_SELECT.GLUKOZINILAT) / nullif(IT.NET + QUALITY_SELECT.GLUKOZINILAT - QUALITY_SELECT.GLUKOZINILAT, 0),
          (IT.NET * QUALITY_SELECT.ERUKOVA_KISLOTA) / nullif(IT.NET + QUALITY_SELECT.ERUKOVA_KISLOTA - QUALITY_SELECT.ERUKOVA_KISLOTA, 0),
          QUALITY_SELECT.HARVESTYEAR
        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 QUALITYdata HARVESTYEAR on (IT.LABQUALITYID = HARVESTYEAR.QUALITYID
--       and HARVESTYEAR.QUALITYtypeid = 6)
          left join DATE_TO_STR(IT.REGDATE) on (0=0)
        where (IT.STATE = -100) and (IT.REGDATE = :TODATE) and (IT.OWNERID = :OWNERID)
          and (QUALITY.CLASSID = :CLASSIDCROP) and (IT.OPERATIONID = :OPERATIONID)
          and (PARTIES.STATE = 1) and (IT.RELATIONID = :RELATIONID)
          and ((IT.PROVIDERID = :PROVIDERID) or (:PROVIDERID is null))
      --and ((coalesce(HARVESTYEAR.QUALITYvalue, -1) = :HARVESTYEAR) or (:HARVESTYEAR = 0))
    order by 2
    into :SORTING, :COMMENT, :WEIGHT, :DIRT, :HUMIDITY, :SCREENING, :GRAINUNIT,
         :PROTEIN, :DROPNUMBER, :GLUTEN, :GLUTENGROUP, :GLUTENQUALITY, :TURTLEBUG,
         :SMALLGRAIN, :ACIDITY, :BEATEN, :GLUKOZIN, :ERUKACID, :HARVESTYEAR
  do suspend;
end