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

SAMPLER_ROUTESELECT

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

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

Описание

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

Определение

CREATE PROCEDURE SAMPLER_ROUTESELECT(
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    ISTORAGEID INTEGER,
    IGLUTEN NUMERIC(9,2),
    IGLUTENQUALITY NUMERIC(9,2),
    IHUMIDITY NUMERIC(9,2),
    IDIRT NUMERIC(9,2),
    ISCREENING NUMERIC(9,2))
RETURNS (
    SILAGEID INTEGER,
    SILAGEPACKID INTEGER,
    SILAGENAME VARCHAR(20),
    MAXWEIGHT INTEGER,
    QUALITYID INTEGER,
    WEIGHT INTEGER,
    QCLASS INTEGER,
    QSORT INTEGER,
    MINUSWEIGHT INTEGER,
    CROPCLASSNAME VARCHAR(150),
    QCROP INTEGER,
    STORAGEID INTEGER,
    GLUTEN NUMERIC(9,2),
    GLUTENQUALITY NUMERIC(9,2),
    HUMIDITY NUMERIC(9,2),
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    GL VARCHAR(20),
    STATUS INTEGER,
    ACIDITY NUMERIC(9,2),
    SMALLGRAIN NUMERIC(9,2),
    DROPNUMBER INTEGER,
    GRAINUNIT INTEGER)
AS
declare variable cropname varchar(50);
declare variable classname varchar(50);
declare variable sortname varchar(50);
begin
for
select silage.silageid, silage.silagepackid, silage.name,
       silage.maxweight, silage.weight, silagepacks.qualityid,
       quality.classid, quality.sortid, quality.cropid,
       quality_CropFullName.cropfullname, silagepacks.storageid,
       quality_select.gluten, quality_select.glutenquality,
       quality_select.humidity, quality_select.dirt, quality_select.screening,
       quality_select.dropnumber, quality_select.acrospire,
       quality_select.smallgrain, quality_select.grainunit

from silage
  left join silagepacks on (silage.silagepackid = silagepacks.silagepackid)
  left join silagequality on (silagequality.silagequalityid = silagepacks.silagequalityid)   -- added
  left join quality on (silagequality.qualityid = quality.qualityid)
  left join quality_CropFullName(quality.cropid, quality.classid,quality.sortid, 0) on (1=1)
  left join storage on (silagepacks.storageid = storage.storageid)
  left join quality_select(silagepacks.qualityid) on (0 = 0)

where (silagepacks.storageid = :istorageid
  and quality.classid = :classid
  and ((quality.sortid = :sortid) or (quality.sortid is null)))
  or ((silagepacks.qualityid is null) and (silagepacks.storageid = :istorageid))

order by silage.silageid
into
    :silageid, :silagepackid, :silagename, :maxweight, :weight,
    :qualityid, :qclass, :qsort, :qcrop, :cropclassname, :storageid,
    :gluten, :glutenquality, :humidity, :dirt, :screening,
    :dropnumber, :acidity, :smallgrain, :grainunit
  do begin
    minusweight = :maxweight - :weight;
    if (:weight is null) then minusweight = :maxweight;
    if ((:qualityid is null) and (:weight is null)) then cropclassname = 'пусто';
    if ((:gluten is not null) and (:glutenquality is not null)) then
      GL = :gluten || '/' || :glutenquality;
    if (:glutenquality is null) then GL = :gluten;
    if (:gluten is null) then GL = '/' || :glutenquality;
    if ((:gluten = :igluten) and
        (:glutenquality = :iglutenquality) and
        (:humidity = :ihumidity) and
        (:dirt = :idirt) and
        (:screening = :iscreening)) then status = 1;
    if (
        ((:gluten <> :igluten) or (:igluten is null))or
        ((:glutenquality <> :iglutenquality) or (:iglutenquality is null)) or
        ((:humidity <> :ihumidity) or (:humidity is null))or
        ((:dirt <> :idirt) or (:idirt is null))or
        ((:screening <> :iscreening) or (:iscreening is null))
        ) then status = 0;

  suspend;
  end
END