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

SILAGEPACKS_APT_ON_QUALITY

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

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

Описание

Выбор силосов подходящих по качеству

Определение

CREATE PROCEDURE SILAGEPACKS_APT_ON_QUALITY(
    STORAGEID INTEGER,
    QUALITYID INTEGER,
    SHOWEMPTY SMALLINT)
RETURNS (
    SILAGEPACKID INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER)
AS
declare variable SORTID integer;
declare variable HUMIDITYID integer;
declare variable DIRTID integer;
begin
  if (QUALITYID is not null) then
    select QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID,
        QUALITY_SAMPER_TO_LAB.HUMIDITYID, QUALITY_SAMPER_TO_LAB.DIRTID
      from QUALITY
        left join QUALITYDATA HUMIDITY on ((HUMIDITY.QUALITYID = QUALITY.QUALITYID) and (HUMIDITY.QUALITYTYPEID = 2))
        left join QUALITYDATA DIRT on ((DIRT.QUALITYID = QUALITY.QUALITYID) and (DIRT.QUALITYTYPEID = 13))
        left join QUALITY_SAMPER_TO_LAB(QUALITY.CROPID, HUMIDITY.QUALITYVALUE, DIRT.QUALITYVALUE) on (0=0)
      where (QUALITY.QUALITYID = :QUALITYID)
    INTO :CROPID, :CLASSID, :SORTID, :HUMIDITYID, :DIRTID;
  for
    select SILAGEPACKS.SILAGEPACKID
      from SILAGEPACKS
        left join SILAGEQUALITY on (SILAGEQUALITY.SILAGEQUALITYID = SILAGEPACKS.SILAGEQUALITYID)   -- added
        left join QUALITY on (QUALITY.QUALITYID = SILAGEQUALITY.QUALITYID)
        left join QUALITYDATA HUMIDITY on ((HUMIDITY.QUALITYID = QUALITY.QUALITYID) and (HUMIDITY.QUALITYTYPEID = 102))
        left join QUALITYDATA DIRT on ((DIRT.QUALITYID = QUALITY.QUALITYID) and (DIRT.QUALITYTYPEID = 102))
      where (SILAGEPACKS.STORAGEID = :STORAGEID)
        and ((SILAGEPACKS.QUALITYID is null) and coalesce(:SHOWEMPTY,-1) = 1)
        or (((QUALITY.CROPID = coalesce(:CROPID,-1) or (coalesce(:CROPID,-1) = -1)))
            and ((QUALITY.CLASSID = coalesce(:CLASSID,-1) or (coalesce(:CLASSID,-1) = -1)))
            and ((QUALITY.SORTID = coalesce(:SORTID,-1) or (coalesce(:SORTID,-1) = -1))))
    order by SILAGEPACKS.NAME
    into :SILAGEPACKID
  do suspend;
end