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

ACN_QUALITY_IINFORMATION

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

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

Описание

Информация о качестве пшеницы

Определение

CREATE PROCEDURE ACN_QUALITY_IINFORMATION(
    CROPID INTEGER,
    ENDDATE DATE)
RETURNS (
    CLASSNAME VARCHAR(20),
    HARVESTINGYEAR INTEGER,
    ALLNET INTEGER,
    HUMIDITY_MIN DECIMAL(4,2),
    HUMIDITY_MAX DECIMAL(4,2),
    HUMIDITY_AVG DECIMAL(4,2),
    GRAINUNIT_MIN INTEGER,
    GRAINUNIT_MAX INTEGER,
    GRAINUNIT_AVG INTEGER,
    GLASSNESS_MIN INTEGER,
    GLASSNESS_MAX INTEGER,
    GLASSNESS_AVG INTEGER,
    GLUTEN_MIN DECIMAL(4,2),
    GLUTEN_MAX DECIMAL(4,2),
    GLUTEN_AVG DECIMAL(4,2),
    GLUTENQUALITY_MIN INTEGER,
    GLUTENQUALITY_MAX INTEGER,
    GLUTENQUALITY_AVG INTEGER,
    GLUTENGROUP_I INTEGER,
    GLUTENGROUP_II INTEGER,
    GLUTENGROUP_III INTEGER,
    GLUTENGROUP_O INTEGER,
    GLUTENGROUP_OP DECIMAL(4,2),
    PROTEIN_MIN DECIMAL(4,2),
    PROTEIN_MAX DECIMAL(4,2),
    PROTEIN_AVG DECIMAL(4,2),
    DROPNUMBER_MIN INTEGER,
    DROPNUMBER_MAX INTEGER,
    DROPNUMBER_AVG INTEGER,
    DIRT_MIN DECIMAL(4,2),
    DIRT_MAX DECIMAL(4,2),
    DIRT_AVG DECIMAL(4,2),
    SPOILT_MIN DECIMAL(4,2),
    SPOILT_MAX DECIMAL(4,2),
    SPOILT_AVG DECIMAL(4,2),
    FUSARIOSE_MIN DECIMAL(4,2),
    FUSARIOSE_MAX DECIMAL(4,2),
    FUSARIOSE_AVG DECIMAL(4,2),
    SCREENING_MIN DECIMAL(4,2),
    SCREENING_MAX DECIMAL(4,2),
    SCREENING_AVG DECIMAL(4,2),
    NEVYPOLNENNYE_MIN DECIMAL(4,2),
    NEVYPOLNENNYE_MAX DECIMAL(4,2),
    NEVYPOLNENNYE_AVG DECIMAL(4,2),
    ACROSPIRE_MIN DECIMAL(4,2),
    ACROSPIRE_MAX DECIMAL(4,2),
    ACROSPIRE_AVG DECIMAL(4,2),
    ACROSPIRE_2 INTEGER,
    ACROSPIRE_2_3 INTEGER,
    ACROSPIRE_3_4 INTEGER,
    ACROSPIRE_4_15 INTEGER,
    ACROSPIRE_15 INTEGER,
    TURTLEBUG_MIN DECIMAL(4,2),
    TURTLEBUG_MAX DECIMAL(4,2),
    TURTLEBUG_AVG DECIMAL(4,2),
    GOLOVNEVIE_MIN DECIMAL(4,2),
    GOLOVNEVIE_MAX DECIMAL(4,2),
    GOLOVNEVIE_AVG DECIMAL(4,2),
    DEFECTIVE_MIN DECIMAL(4,2),
    DEFECTIVE_MAX DECIMAL(4,2),
    DEFECTIVE_AVG DECIMAL(4,2))
AS
declare variable CLASSID integer;
declare variable PARTYID integer;
declare variable BEGINDATE date;
begin
  for
    select CLASS.CLASSID, CLASS.CLASSNAME, PARTIES.PARTYID, PARTIES.BEGINDATE,
        PARTIES.HARVESTINGYEAR
      from CLASS
        left join PARTIES on ((PARTIES.CROPID = :CROPID) and (PARTIES.CLASSID = CLASS.CLASSID) and (PARTIES.STATE = 1))
    where (CLASS.CROPID = :CROPID) and (PARTIES.PARTYID is not null)
    order by CLASS.CLASSNAME
    into :CLASSID, :CLASSNAME, :PARTYID, :BEGINDATE, :HARVESTINGYEAR
  do begin
    select round(sum(cast(IT.NET as numeric(15,3))) / 1000),
        min(QUALITY_SELECT.HUMIDITY), max(QUALITY_SELECT.HUMIDITY),
        cast(sum(cast(QUALITY_SELECT.HUMIDITY as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.GRAINUNIT), max(QUALITY_SELECT.GRAINUNIT),
        cast(sum(cast(QUALITY_SELECT.GRAINUNIT as numeric(15,4)) * IT.NET) / sum(IT.NET) as integer),
        min(QUALITY_SELECT.GLASSNESS), max(QUALITY_SELECT.GLASSNESS),
        cast(sum(cast(QUALITY_SELECT.GLASSNESS as numeric(15,4)) * IT.NET) / sum(IT.NET) as integer),
        min(QUALITY_SELECT.GLUTEN), max(QUALITY_SELECT.GLUTEN),
        cast(sum(cast(QUALITY_SELECT.GLUTEN as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.GLUTENQUALITY), max(QUALITY_SELECT.GLUTENQUALITY),
        cast(sum(cast(QUALITY_SELECT.GLUTENQUALITY as numeric(15,4)) * IT.NET) / sum(IT.NET) as integer),
        round(sum(case
                    when (QUALITY_SELECT.GLUTENGROUP = 1) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        round(sum(case
                    when (QUALITY_SELECT.GLUTENGROUP = 2) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        round(sum(case
                    when (QUALITY_SELECT.GLUTENGROUP = 3) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        round(sum(case
                    when (QUALITY_SELECT.GLUTENGROUP = 0) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        sum(case
              when (QUALITY_SELECT.GLUTENGROUP = 0) then
                cast(IT.NET as numeric(15,2))
            end) / (sum(IT.NET) / 100),
        min(QUALITY_SELECT.PROTEIN), max(QUALITY_SELECT.PROTEIN),
        cast(sum(cast(QUALITY_SELECT.PROTEIN as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.DROPNUMBER), max(QUALITY_SELECT.DROPNUMBER),
        cast(sum(cast(QUALITY_SELECT.DROPNUMBER as numeric(15,4)) * IT.NET) / sum(IT.NET) as integer),
        min(QUALITY_SELECT.DIRT), max(QUALITY_SELECT.DIRT),
        cast(sum(cast(QUALITY_SELECT.DIRT as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.SPOILT), max(QUALITY_SELECT.SPOILT),
        cast(sum(cast(QUALITY_SELECT.SPOILT as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.FUSARIOSE), max(QUALITY_SELECT.FUSARIOSE),
        cast(sum(cast(QUALITY_SELECT.FUSARIOSE as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.SCREENING), max(QUALITY_SELECT.SCREENING),
        cast(sum(cast(QUALITY_SELECT.SCREENING as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.NEVYPOLNENNYE), max(QUALITY_SELECT.NEVYPOLNENNYE),
        cast(sum(cast(QUALITY_SELECT.NEVYPOLNENNYE as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.ACROSPIRE), max(QUALITY_SELECT.ACROSPIRE),
        cast(sum(cast(QUALITY_SELECT.ACROSPIRE as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        round(sum(case
                    when (QUALITY_SELECT.ACROSPIRE <= 2) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        round(sum(case
                    when ((QUALITY_SELECT.ACROSPIRE > 2) and (QUALITY_SELECT.ACROSPIRE <= 3)) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        round(sum(case
                    when ((QUALITY_SELECT.ACROSPIRE > 3) and (QUALITY_SELECT.ACROSPIRE <= 4)) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        round(sum(case
                    when ((QUALITY_SELECT.ACROSPIRE > 4) and (QUALITY_SELECT.ACROSPIRE <= 15)) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        round(sum(case
                    when (QUALITY_SELECT.ACROSPIRE > 15) then
                      cast(IT.NET as numeric(15,3))
                  end) / 1000),
        min(QUALITY_SELECT.TURTLEBUG), max(QUALITY_SELECT.TURTLEBUG),
        cast(sum(cast(QUALITY_SELECT.TURTLEBUG as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.GOLOVNEVIE), max(QUALITY_SELECT.GOLOVNEVIE),
        cast(sum(cast(QUALITY_SELECT.GOLOVNEVIE as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2)),
        min(QUALITY_SELECT.DEFECTIVE), max(QUALITY_SELECT.DEFECTIVE),
        cast(sum(cast(QUALITY_SELECT.DEFECTIVE as numeric(15,4)) * IT.NET) / sum(IT.NET) as numeric(4,2))
      from IT
        left join QUALITY_SELECT(IT.BUHQUALITYID) on (0=0)
      where (IT.PARTYID = :PARTYID) and (IT.REGDATE < :ENDDATE) and (IT.OPERATIONID = 1)
    into :ALLNET,
         :HUMIDITY_MIN, :HUMIDITY_MAX, :HUMIDITY_AVG,
         :GRAINUNIT_MIN, :GRAINUNIT_MAX, :GRAINUNIT_AVG,
         :GLASSNESS_MIN, :GLASSNESS_MAX, :GLASSNESS_AVG,
         :GLUTEN_MIN, :GLUTEN_MAX, :GLUTEN_AVG,
         :GLUTENQUALITY_MIN, :GLUTENQUALITY_MAX, :GLUTENQUALITY_AVG,
         :GLUTENGROUP_I, :GLUTENGROUP_II, :GLUTENGROUP_III, GLUTENGROUP_O, :GLUTENGROUP_OP,
         :PROTEIN_MIN, :PROTEIN_MAX, :PROTEIN_AVG,
         :DROPNUMBER_MIN, :DROPNUMBER_MAX, :DROPNUMBER_AVG,
         :DIRT_MIN, :DIRT_MAX, :DIRT_AVG,
         :SPOILT_MIN, :SPOILT_MAX, :SPOILT_AVG,
         :FUSARIOSE_MIN, :FUSARIOSE_MAX, :FUSARIOSE_AVG,
         :SCREENING_MIN, :SCREENING_MAX, :SCREENING_AVG,
         :NEVYPOLNENNYE_MIN, :NEVYPOLNENNYE_MAX, :NEVYPOLNENNYE_AVG,
         :ACROSPIRE_MIN, :ACROSPIRE_MAX, :ACROSPIRE_AVG,
         :ACROSPIRE_2, :ACROSPIRE_2_3, :ACROSPIRE_3_4, :ACROSPIRE_4_15, :ACROSPIRE_15,
         :TURTLEBUG_MIN, :TURTLEBUG_MAX, :TURTLEBUG_AVG,
         :GOLOVNEVIE_MIN, :GOLOVNEVIE_MAX, :GOLOVNEVIE_AVG,
         :DEFECTIVE_MIN, :DEFECTIVE_MAX, :DEFECTIVE_AVG;

    suspend;
  end
end