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

ACN_COMESELECT

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

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

Описание

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

Определение

CREATE PROCEDURE ACN_COMESELECT(
    BEGINTIME TIMESTAMP,
    ENDTIME TIMESTAMP,
    ACROPID INTEGER,
    ACLASSID INTEGER)
RETURNS (
    RECORDID INTEGER,
    TYPEOPERATION CHAR(1),
    TRANSPORTNUMBER VARCHAR(31),
    CROPFULLNAME VARCHAR(100),
    CREATERECDATE TIMESTAMP,
    CREATEUSER VARCHAR(50),
    NET INTEGER,
    NETDATE TIMESTAMP,
    NETUSER VARCHAR(50),
    REGDATE DATE,
    SQDATE TIMESTAMP,
    SQUSER VARCHAR(50),
    LQDATE TIMESTAMP,
    LQUSER VARCHAR(50),
    DELDATE TIMESTAMP,
    DELUSER VARCHAR(50),
    HUMIDITY NUMERIC(9,2),
    GRAINUNIT INTEGER,
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    OILSEED NUMERIC(9,2),
    PROTEIN NUMERIC(9,2),
    GLUTEN NUMERIC(9,1),
    GLUTENQUALITY INTEGER,
    DROPNUMBER INTEGER,
    ACIDITY NUMERIC(9,2),
    GLASSNESS INTEGER)
AS
BEGIN
  FOR
    select ILT1.RECORDID,
        case IT.OPERATIONID
          when 1 then
            'П'
          when 5 then
            'Р'
        end TYPEOPERATION,
        ITTRANSPORT.TRANSPORTNUMBER, QUALITY_CROPFULLNAME.CROPFULLNAME
      from ILT$TABLEOPERATION ILT1
        left join IT on (IT.INVENTORYID = ILT1.RECORDID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, null, 1) on (0=0)
      where (ILT1.CREATETIME between :BEGINTIME and (:ENDTIME))
        and (ILT1.TABLEID = 149) and (IT.OPERATIONID in (1,5))
        and ((QUALITY.CROPID = :ACROPID) or (coalesce(:ACROPID,-1) = -1))
        and ((QUALITY.CLASSID = :ACLASSID) or (coalesce(:ACLASSID,-1) = -1))

    group by RECORDID,
        case IT.OPERATIONID
          when 1 then
            'П'
          when 5 then
            'Р'
        end,
        ITTRANSPORT.TRANSPORTNUMBER, QUALITY_CROPFULLNAME.CROPFULLNAME

    into :RECORDID, :TYPEOPERATION, :TRANSPORTNUMBER, :CROPFULLNAME
  do begin
    for
      select ACN_COMESELECT_R.CREATERECDATE, ACN_COMESELECT_R.CREATEUSER, ACN_COMESELECT_R.NET,
          ACN_COMESELECT_R.NETDATE, ACN_COMESELECT_R.NETUSER, ACN_COMESELECT_R.REGDATE,
          ACN_COMESELECT_R.SQDATE, ACN_COMESELECT_R.SQUSER, ACN_COMESELECT_R.LQDATE,
          ACN_COMESELECT_R.LQUSER, ACN_COMESELECT_R.DELDATE, ACN_COMESELECT_R.DELUSER,
          ACN_COMESELECT_R.HUMIDITY, ACN_COMESELECT_R.GRAINUNIT, ACN_COMESELECT_R.DIRT,
          ACN_COMESELECT_R.SCREENING, ACN_COMESELECT_R.OILSEED, ACN_COMESELECT_R.PROTEIN,
          ACN_COMESELECT_R.GLUTEN, ACN_COMESELECT_R.GLUTENQUALITY, ACN_COMESELECT_R.DROPNUMBER,
          ACN_COMESELECT_R.ACIDITY, ACN_COMESELECT_R.GLASSNESS--, ACN_COMESELECT_R.tmptabid
        from ACN_COMESELECT_R(:RECORDID, :BEGINTIME, (:ENDTIME))
      order by ACN_COMESELECT_R.TMPTABID, ACN_COMESELECT_R.CREATERECDATE,
          ACN_COMESELECT_R.NETDATE, ACN_COMESELECT_R.SQDATE, ACN_COMESELECT_R.LQDATE
      into :CREATERECDATE, :CREATEUSER, :NET, :NETDATE, :NETUSER, :REGDATE, :SQDATE,
           :SQUSER, :LQDATE, :LQUSER, :DELDATE, :DELUSER, :HUMIDITY, :GRAINUNIT,
           :DIRT, :SCREENING, :OILSEED, :PROTEIN, :GLUTEN, :GLUTENQUALITY,
           :DROPNUMBER, :ACIDITY, :GLASSNESS
    do suspend;
  end
end