Входные параметры
Параметр | Тип | Описание |
---|---|---|
QUALITYID |
INTEGER |
|
ISBLANK |
INTEGER |
|
Выходные параметры
Параметр | Тип | Описание |
---|---|---|
OTHERQ |
VARCHAR(500) |
|
Описание
(Нет описания для процедуры 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