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

IT_AVG_QUA_WE_SUMM_NIK

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

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

Описание

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

Определение

CREATE PROCEDURE IT_AVG_QUA_WE_SUMM_NIK(
    TODATE DATE,
    OWNERID INTEGER,
    CLASSIDCROP INTEGER,
    OPERATIONID INTEGER,
    BEGPARTYDATE DATE)
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 NUMERIC(9,2))
AS
declare variable sorting integer;
begin
  if (:begpartydate is null) then begin
    select first 1 parties.partyid
    from parties
    where parties.state = 1
      and parties.cropid = (select cropid from class where classid = :classidcrop)
      and parties.storageid = 1
    order by coalesce(parties.classid, 100000)
    into :begpartydate;
  end
  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),
      sum(IT.NET * quality_select.glutenquality) / nullif(sum(IT.NET + quality_select.glutenquality - quality_select.glutenquality), 0),
      sum(IT.NET * quality_select.gluten) / nullif(sum(IT.NET + quality_select.gluten - quality_select.gluten), 0),
      sum(IT.NET * quality_select.turtlebug) / nullif(sum(IT.NET + quality_select.turtlebug - quality_select.turtlebug), 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 <> -1
      and it.regdate < :todate
      and it.regdate >= :begpartydate
      and it.ownerid = :ownerid
      and quality.classid = :classidcrop
      and it.operationid = :operationid
      and parties.state = 1
    union
    select 2,
      cast('' as varchar(15)),
      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.glutenquality) / nullif(IT.NET + quality_select.glutenquality - quality_select.glutenquality, 0),
      (IT.NET * quality_select.gluten) / nullif(IT.NET + quality_select.gluten - quality_select.gluten, 0),
      (IT.NET * quality_select.turtlebug) / nullif(IT.NET + quality_select.turtlebug - quality_select.turtlebug, 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
      left join ittransport on ittransport.inventoryid = it.inventoryid
    where IT.STATE <> -1
      and it.regdate = :todate
      and it.ownerid = :ownerid
      and quality.classid = :classidcrop
      and it.operationid = :operationid
      and parties.state = 1
    into :SORTING, :comment, :weight, :dirt, :humidity, :screening, :grainunit, :protein, :dropnumber, :glutenquality, :gluten, :turtlebug
  do suspend;
end