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

GETROUTECHEMA

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

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

Описание

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

Определение

CREATE PROCEDURE GETROUTECHEMA(
    AROUTECHEMAID INTEGER)
RETURNS (
    RESULTROUTECHEMA VARCHAR(255))
AS
DECLARE VARIABLE ACLASSNAME VARCHAR(20);
DECLARE VARIABLE ACROPNAME VARCHAR(20);
DECLARE VARIABLE APLATFORM INTEGER;
DECLARE VARIABLE ASILAGE VARCHAR(255);
DECLARE VARIABLE AGLUTENMIN NUMERIC(4,1);
DECLARE VARIABLE AHUMIDITYMIN NUMERIC(4,2);
DECLARE VARIABLE ADIRTMIN NUMERIC(4,2);
DECLARE VARIABLE ASCREENINGMIN NUMERIC(4,2);
DECLARE VARIABLE AGRAINUNITMIN INTEGER;
DECLARE VARIABLE ASMALLGRAINMIN NUMERIC(4,2);
DECLARE VARIABLE AGLUTENMAX NUMERIC(4,1);
DECLARE VARIABLE AHUMIDITYMAX NUMERIC(4,2);
DECLARE VARIABLE ADIRTMAX NUMERIC(4,2);
DECLARE VARIABLE ASCREENINGMAX NUMERIC(4,2);
DECLARE VARIABLE AGRAINUNITMAX INTEGER;
DECLARE VARIABLE ASMALLGRAINMAX NUMERIC(4,2);
DECLARE VARIABLE AINFECTIONNAME VARCHAR(30);
DECLARE VARIABLE AINFECTIONID INTEGER;
DECLARE VARIABLE AQUALITY VARCHAR(255);
DECLARE VARIABLE SORTNAME VARCHAR(20);
DECLARE VARIABLE ACROSPIREMIN NUMERIC(4,2);
DECLARE VARIABLE ACROSPIREMAX NUMERIC(4,2);
DECLARE VARIABLE ADROPNUMBERMIN INTEGER;
DECLARE VARIABLE ADROPNUMBERMAX INTEGER;
begin /*
 select Crop.CropName, Class.ClassName, RouteChema.platformid,
    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.infectionid, infection.name,
    (select result from GetSilagesFromRoute(RouteChema.RouteChemaID)),
    CropSort.sortname, QualityMin.acidity, QualityMax.acidity, QualityMin.dropnumber, QualityMax.dropnumber
  from RouteChema
    left outer join Quality QualityMin on (RouteChema.QualityMin = QualityMin.QualityID)
    left outer join Quality QualityMax on (RouteChema.QualityMax = QualityMax.QualityID)
    left outer join Crop on (QualityMin.CropID = Crop.CropID)
    left outer join Class on (QualityMin.ClassID = Class.ClassID)
    left outer join CropSort on (CropSort.sortid = QualityMin.CropSortID)
    left join Infection on (QualityMin.infectionid = infection.infectionid)
  where RouteChema.RouteChemaId = :ARouteChemaId
  into :ACropName, :AClassName, :APlatform,
       :AGlutenMin, :AHumidityMin, :ADirtMin,
       :AScreeningMin, :AGrainunitMin, :ASmallGrainMin,
       :AGlutenMax, :AHumidityMax, :ADirtMax,
       :AScreeningMax, :AGrainunitMax, :ASmallGrainMax,
       :AInfectionId, :AInfectionName, :ASilage, :sortname, :acrospiremin, :acrospiremax, :adropnumbermin, :adropnumbermax;
  AQuality = '';
  if (not (:AGlutenMin is null) and not (:AGlutenMax is null)) then
    if (:aglutenmin = -1) then AQuality = AQuality || 'клейк. неотм. ';
    else AQuality = AQuality || 'клейк. ' || :aglutenmin || ' - ' || :aglutenmax;
  if ((:AGlutenMin is null) and not (:AGlutenMax is null)) then
    if (:aglutenmin = -1) then AQuality = AQuality || 'клейк. неотм. ';
    else AQuality = AQuality || 'клейк. до ' || :aglutenmax;
  if (not (:AGlutenMin is null) and (:AGlutenMax is null)) then
    if (:aglutenmin = -1) then AQuality = AQuality || 'клейк. неотм. ';
    else AQuality = AQuality || 'клейк. св.' || :aglutenmin;

  if (not (:AHumidityMin is null) and not (:AHumidityMax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'влажн. ' || :ahumiditymin || ' - ' || :ahumiditymax;
  end
  if ((:AHumidityMin is null) and not (:AHumidityMax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'влажн. до ' || :ahumiditymax;
  end
  if (not (:AHumidityMin is null) and (:AHumidityMax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'влажн. св.' || :ahumiditymin;
  end
  if (not (:ADirtMin is null) and not (:ADirtMax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'сорн. ' || :ADirtMin || ' - ' || :ADirtMax;
  end
  if ((:ADirtMin is null) and not (:ADirtMax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'сорн. до ' || :ADirtMax;
  end
  if (not (:ADirtMin is null) and (:ADirtMax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'сорн. св.' || :ADirtMin;
  end
  if (not (:ascreeningmin is null) and not (:ascreeningmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'зерн. ' || :ascreeningmin || ' - ' || :ascreeningmax;
  end
  if ((:ascreeningmin is null) and not (:ascreeningmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'зерн. до ' || :ascreeningmax;
  end
  if (not (:ascreeningmin is null) and (:ascreeningmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'зерн. св.' || :ascreeningmin;
  end
  if (not (:agrainunitmin is null) and not (:agrainunitmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'натура ' || :agrainunitmin || ' - ' || :agrainunitmax;
  end
  if ((:agrainunitmin is null) and not (:agrainunitmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'натура до ' || :agrainunitmax;
  end
  if (not (:agrainunitmin is null) and (:agrainunitmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'натура св.' || :agrainunitmin;
  end
  if (not (:asmallgrainmin is null) and not (:asmallgrainmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'м/з ' || :asmallgrainmin || ' - ' || :asmallgrainmax;
  end
  if ((:asmallgrainmin is null) and not (:asmallgrainmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'м/з до ' || :asmallgrainmax;
  end
  if (not (:asmallgrainmin is null) and (:asmallgrainmax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'м/з св.' || :asmallgrainmin;
  end
  if (not (:acrospiremin is null) and not (:acrospiremax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'проросшие ' || :acrospiremin || ' - ' || :acrospiremax;
  end
  if ((:acrospiremin is null) and not (:acrospiremax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'проросшие до ' || :acrospiremax;
  end
  if (not (:acrospiremin is null) and (:acrospiremax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'проросшие св.' || :acrospiremin;
  end
  if (:ainfectionid > 1 ) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'зараж. ' || :ainfectionname;
  end

  if (not (:adropnumbermin is null) and not (:adropnumbermax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'ч.п. ' || :adropnumbermin || ' - ' || :adropnumbermax;
  end
  if ((:adropnumbermin is null) and not (:adropnumbermax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'ч.п. до ' || :adropnumbermax;
  end
  if (not (:adropnumbermin is null) and (:adropnumbermax is null)) then begin
    if (AQuality <> '') then AQuality = AQuality || ', ';
    AQuality = AQuality || 'ч.п. св.' || :adropnumbermin;
  end
  ResultRouteChema = :ACropName || ' ' || :AClassName || ' ';
  if (:sortname is not null) then ResultRouteChema = :ResultRouteChema || :sortname || ' ';
  if (not(:aquality = '')) then ResultRouteChema = ResultRouteChema || '(' || :aquality || ') ';
  ResultRouteChema = ResultRouteChema || '- ' || :APlatform || ' площ. - ' || :ASilage || ' сил.';
  suspend;
 */ EXIT;
END