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

AC_QUALITYSET_XML

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

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

Описание

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

Определение

CREATE PROCEDURE AC_QUALITYSET_XML(
    QUALITYID INTEGER,
    ISBLANK INTEGER)
RETURNS (
    OTHERQ VARCHAR(500))
AS
declare variable CURSTR varchar(50);
begin
  OTHERQ = '';

  for
/*
    select
        case
          when (not QUALITYDATA.QUALITYVALUE is null) then
            QUALITYTYPES.QUALITYSHORTNAME || ': ' ||
            case
              when (QUALITYDATA.QUALITYTYPEID in (7,12,59)) then
                cast(cast(QUALITYDATA.QUALITYVALUE as integer) as varchar(12)) || ' '
              else
                substring(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12)) from 1 for POS.POSl - 1) || ',' ||
                substring(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12)) from POS.POSl + 1 for char_length(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12)))) || ' '
            end
          else ''
        end
      from QUALITYDATA
        left join QUALITYTYPES on (QUALITYTYPES.QUALITYTYPEID = QUALITYDATA.QUALITYTYPEID)
        left join QUALITY on (QUALITY.QUALITYID = QUALITYDATA.QUALITYID)
        left join CROP on (CROP.CROPID = QUALITY.CROPID)
        left join POS('.', cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12))) on (0=0)
      where (QUALITYDATA.QUALITYID = :QUALITYID)
-- Пшеница : натура, стекловидность, клоп-черепашка, проросшие, число падения, головневые
        and (((CROP.CROPGHI = 400) and (QUALITYDATA.QUALITYTYPEID in (7,12,17,56,59,207)) and (coalesce(:ISBLANK,0) = 0))
-- Пшеница : натура, белок, стекловидность, клоп-черепашка, проросшие, число падения, головневые
            or ((CROP.CROPGHI = 400) and (QUALITYDATA.QUALITYTYPEID in (7,10,12,17,56,59,207)) and (:ISBLANK = 1))
-- Ячмень : натура, мелкое зерно
            or ((CROP.CROPGHI = 67) and (QUALITYDATA.QUALITYTYPEID in (7,16)))
-- Рожь : натура, мелкое зерно, число падения
            or ((CROP.CROPGHI = 64) and (QUALITYDATA.QUALITYTYPEID in (7,16,59)))
-- Кукуруза : поврежденные
            or ((CROP.CROPGHI = 44) and (QUALITYDATA.QUALITYTYPEID in (70)))
-- Подсолнечник : кислотное число масла, поврежденные, обрушенные, масличная примесь, масличность
            or (((CROP.CROPGHI in (60,404))) and (QUALITYDATA.QUALITYTYPEID in (8,70,104,110,3234)))
-- Рапс : кислотное число масла, масличная примесь, эруковая кислота, глюкозинолат, масличность
            or ((CROP.CROPGHI = 401) and (QUALITYDATA.QUALITYTYPEID in (8,110,138,139,3234)))
-- Соя : белок, поврежденные, масличная примесь, масличность
            or ((CROP.CROPGHI = 402) and (QUALITYDATA.QUALITYTYPEID in (10,70,110,3234))))
/*
        and (((QUALITY.CROPID = 1) and (QUALITYDATA.QUALITYTYPEID in (7, 12, 17, 56, 59, 207)) and (coalesce(:ISBLANK, 0) = 0))
            or ((QUALITY.CROPID = 1) and (QUALITYDATA.QUALITYTYPEID in (7, 10, 12, 17, 56, 59, 207)) and (:ISBLANK = 1))
            or ((QUALITY.CROPID = 2) and (QUALITYDATA.QUALITYTYPEID in (7, 16)))
            or ((QUALITY.CROPID = 3) and (QUALITYDATA.QUALITYTYPEID in (7, 16, 59)))
            or ((QUALITY.CROPID = 4) and (QUALITYDATA.QUALITYTYPEID in (70)))
            or ((QUALITY.CROPID = 37) and (QUALITYDATA.QUALITYTYPEID in (8, 70, 104, 110, 3234)))
            or ((QUALITY.CROPID = 81) and (QUALITYDATA.QUALITYTYPEID in (8, 110, 138, 139, 3234)))
            or ((QUALITY.CROPID = 82) and (QUALITYDATA.QUALITYTYPEID in (10, 110, 3234))))
*/
    select
        case
          when (not QUALITYDATA.QUALITYVALUE is null) then
            QUALITYTYPES.QUALITYSHORTNAME || ': ' ||
            case
              when (QUALITYDATA.QUALITYTYPEID in (7,12,59)) then
                cast(cast(QUALITYDATA.QUALITYVALUE as integer) as varchar(12)) || ' '
              else
                substring(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12)) from 1 for POS.POSl - 1) || ',' ||
                substring(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12)) from POS.POSl + 1 for char_length(cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12)))) || ' '
            end
          else ''
        end
      from QUALITYDATA
        left join QUALITYTYPES on (QUALITYTYPES.QUALITYTYPEID = QUALITYDATA.QUALITYTYPEID)
        left join QUALITY on (QUALITY.QUALITYID = QUALITYDATA.QUALITYID)
        left join CROP on (CROP.CROPID = QUALITY.CROPID)
        left join POS('.', cast(cast(QUALITYDATA.QUALITYVALUE as numeric(9,2)) as varchar(12))) on (0=0)
        left join CROPPARAMS on (CROPPARAMS.QUALITYTYPEID = QUALITYDATA.QUALITYTYPEID)
      where (QUALITYDATA.QUALITYID = :QUALITYID)
          and ((CROPPARAMS.CROPPARAMTYPEID in (8,10)) and (CROPPARAMS.STORAGETICKET = 1)
               and (CROPPARAMS.CROPID in (-1,QUALITY.CROPID))
               and ((CROPPARAMS.CLASSID is null) or (CROPPARAMS.CLASSID = QUALITY.CLASSID)))
    into :CURSTR

  do begin
    OTHERQ = OTHERQ || :CURSTR;
  end

  suspend;

end