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

SHIPMENT_GET_SILAGE_PACKS

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

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

Описание

Развращает список силосных пакетов, с которых возможна отгрузка

Определение

CREATE PROCEDURE SHIPMENT_GET_SILAGE_PACKS(
    RECIPEID INTEGER)
RETURNS (
    RSILAGEPACKID INTEGER,
    RSILAGESNAME VARCHAR(255),
    RWEIGHT INTEGER,
    RGLUTEN VARCHAR(10),
    RHUMIDITY NUMERIC(4,2),
    RDIRT NUMERIC(5,2),
    RSCREENING NUMERIC(5,2),
    RQUALITYID INTEGER,
    RRELATIONID INTEGER,
    RCROPCLASS VARCHAR(41))
AS
DECLARE VARIABLE STORAGEID INTEGER;
DECLARE VARIABLE CROPID INTEGER;
begin
  for
    select silagepacks.SILAGEPACKID, silagepacks.recipeid
    from recipesilages
      left join silagepacks on (recipesilages.silagepackid = silagepacks.silagepackid)
      left join quality on (silagepacks.qualityid = quality.qualityid)
    where recipesilages.recipeid = :recipeid
      and (silagepacks.weight is not null)
      and (silagepacks.weight <> 0)
      and (quality.cropid is not null)
    into :RSILAGEPACKID, :rrelationid
  do begin
    select weight, qualityid, name from silagepacks where SILAGEPACKID = :RSILAGEPACKID
    into :RWeight, :Rqualityid, :rsilagesname;

    select Gluten || '-' || GlutenQuality, Humidity, Dirt, Screening, crop.cropname || ' ' || class.classname
    from quality_select(:rqualityid)
      left join class on (class.classid = quality_select.classid)
      left join crop on (crop.cropid = quality_select.cropid)
    into :rgluten, :rhumidity, :rdirt, :rscreening, :rcropclass;

    if (:rcropclass is null) then rcropclass = 'п/п';

    suspend;
  end

  select storageid from recipes where recipes.recipeid = :recipeid into :storageid;
  if (:storageid in (3, 4)) then begin
    cropid = 1;
    storageid = 1;
  end
  else begin
    cropid = 3;
    storageid = 13;
  end             

  rrelationid = :recipeid;
  for
    select silagepacks.SILAGEPACKID
    from silagepacks
      left join quality on (silagepacks.qualityid = quality.qualityid)
    where silagepacks.recipeid = :recipeid
      and (silagepacks.state <> 0)
      and (silagepacks.storageid = :storageid)
      and (quality.cropid = :cropid)
      and (silagepacks.weight is not null)
      and (silagepacks.weight <> 0)
      and (quality.cropid is not null)
    into :RSILAGEPACKID
  do begin
    select weight, qualityid, name from silagepacks where SILAGEPACKID = :RSILAGEPACKID
    into :RWeight, :Rqualityid, :rsilagesname;

    select Gluten || '-' || GlutenQuality, Humidity, Dirt, Screening, crop.cropname || ' ' || class.classname
    from quality_select(:rqualityid)
      left join class on (class.classid = quality_select.classid)
      left join crop on (crop.cropid = quality_select.cropid)
    into :rgluten, :rhumidity, :rdirt, :rscreening, :rcropclass;

    if (:rcropclass is null) then rcropclass = 'п/п';

    suspend;
  end
END