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

GETROUTEORDERGRID

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

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

Описание

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

Определение

CREATE PROCEDURE GETROUTEORDERGRID(
    AROUTEORDERID INTEGER)
RETURNS (
    ROUTEORDERID INTEGER,
    GLUTENMIN NUMERIC(5,1),
    GLUTENMAX NUMERIC(5,1),
    HUMIDITYMIN NUMERIC(5,2),
    HUMIDITYMAX NUMERIC(5,2),
    DIRTMIN NUMERIC(5,2),
    DIRTMAX NUMERIC(5,2),
    SCREENINGMIN NUMERIC(5,2),
    SCREENINGMAX NUMERIC(5,2),
    GRAINUNITMIN INTEGER,
    GRAINUNITMAX INTEGER,
    SMALLGRAINMIN NUMERIC(5,2),
    SMALLGRAINMAX NUMERIC(5,2),
    ACROSPIREMIN NUMERIC(5,2),
    ACROSPIREMAX NUMERIC(5,2),
    INFECTIONNAME VARCHAR(255),
    PLATFORMID INTEGER,
    SILAGESNAME VARCHAR(255),
    SILAGESID VARCHAR(255),
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    CROPCLASS VARCHAR(40),
    GLUTEN VARCHAR(20),
    HUMIDITY VARCHAR(20),
    DIRT VARCHAR(20),
    SCREENING VARCHAR(20),
    GRAINUNIT VARCHAR(20),
    SMALLGRAIN VARCHAR(20),
    ACROSPIRE VARCHAR(20),
    INFECTIONID INTEGER,
    DROPNUMBERMIN INTEGER,
    DROPNUMBERMAX INTEGER,
    DROPNUMBER VARCHAR(20))
AS
DECLARE VARIABLE ROUTECHEMAID INTEGER;
DECLARE VARIABLE SILAGENAME VARCHAR(20);
DECLARE VARIABLE SILAGEID INTEGER;
DECLARE VARIABLE SORTNAME VARCHAR(20);
begin /*
  if (:arouteorderid is null) then
    select routeorder.routeorderid from routeorder where routeorder.state = 1
    into :routeorderid;
  else routeorderid = :arouteorderid;
  for
    select RouteChema.routechemaid,
      RouteChema.platformid, Crop.CropName || ' ' || Class.ClassName, Crop.cropid,
      Class.classid, QualityMin.gluten, QualityMin.humidity, QualityMin.dirt,
      QualityMin.screening, QualityMin.grainunit, QualityMin.smallgrain,
      QualityMax.gluten, QualityMax.humidity, QualityMax.dirt,
      QualityMax.screening, QualityMax.grainunit, QualityMax.smallgrain,
      QualityMin.acidity, QualityMax.acidity,
      Infection.Name, cropsort.sortid, cropsort.sortname, Infection.infectionid,
      QualityMin.dropnumber, QualityMax.dropnumber
    from RouteChema
      left outer join Quality QualityMin on (QualityMin.qualityid = RouteChema.QualityMin)
      left outer join Quality QualityMax on (QualityMax.qualityid = RouteChema.QualityMax)
      left outer join cropsort on (cropsort.sortid = QualityMin.cropsortid)
      left outer join crop on (QualityMin.CropID = Crop.CropID)
      left outer join class on (QualityMin.ClassID = Class.ClassID)
      left outer join Infection on (Infection.InfectionID = QualityMin.InfectionID)
    where routechema.routeorderid = :routeorderid
    order by crop.CropName, class.classname
    into :routechemaid, :platformid, :cropclass,
         :cropid, :classid, :glutenmin, :humiditymin, :dirtmin, :screeningmin,
         :grainunitmin, :smallgrainmin, :glutenmax, :humiditymax, :dirtmax, :screeningmax,
         :grainunitmax, :smallgrainmax, :acrospiremin, :acrospiremax, :infectionname, :sortid, :sortname, :infectionid,
         :dropnumbermin, :dropnumbermax
  do begin
    gluten = '';
    if (:glutenmin is not null) then gluten = 'от ' || :glutenmin || ' ';
    if (:glutenmax is not null) then gluten = :gluten || 'до ' || :glutenmax;
    if (:glutenmin = -1) then gluten = 'неотм.';
    humidity = '';
    if (:humiditymin is not null) then humidity = 'от ' || :humiditymin || '  ';
    if (:humiditymax is not null) then humidity = :humidity || 'до ' || :humiditymax;
    dirt = '';
    if (:dirtmin is not null) then dirt = 'от ' || :dirtmin || '  ';
    if (:dirtmax is not null) then dirt = :dirt || 'до ' || :dirtmax;
    dropnumber = '';
    if (:dropnumbermin is not null) then dropnumber = 'от ' || :dropnumbermin || '  ';
    if (:dropnumbermax is not null) then dropnumber = :dropnumber || 'до ' || :dropnumbermax;
    Screening = '';
    if (:screeningmin is not null) then screening = 'от ' || :screeningmin || '  ';
    if (:screeningmax is not null) then screening = :screening || 'до ' || :screeningmax;
    grainunit = '';
    if (:grainunitmin is not null) then grainunit = 'от ' || :grainunitmin || '  ';
    if (:grainunitmax is not null) then grainunit = :grainunit || 'до ' || :grainunitmax;
    smallgrain = '';
    if (:smallgrainmin is not null) then smallgrain = 'от ' || :smallgrainmin || '  ';
    if (:smallgrainmax is not null) then smallgrain = :smallgrain || 'до ' || :smallgrainmax;
    acrospire = '';
    if (:acrospiremin is not null) then acrospire = 'от ' || :acrospiremin || '  ';
    if (:acrospiremax is not null) then acrospire = :acrospire || 'до ' || :acrospiremax;
    if (:sortname is not null) then cropclass = :cropclass || ' (' || :sortname || ')';
    silagesname = '';
    for
      select silage.name, silage.silageid from routesilagechema, silage
      where routesilagechema.ROUTECHEMAID = :routechemaid
      and routesilagechema.silageid = silage.silageid
      into :SilageName, :silageid
    do
      if (silagesname = '') then begin
        silagesname = :SilageName;
        silagesid = :silageid;
      end
      else begin
        silagesname = silagesname || ',' || :SilageName;
        silagesid = silagesid || ',' || cast(:SilageID as varchar(10));
      end
    suspend;
  end
 */ EXIT;
END