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

LABMEAL_SUMMARYJOURNAL_GRAIN

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

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

Описание

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

Определение

CREATE PROCEDURE LABMEAL_SUMMARYJOURNAL_GRAIN(
    BEGDATE DATE,
    ENDDATE DATE,
    STORAGEID INTEGER)
RETURNS (
    RDATE DATE,
    WEIGHT INTEGER,
    HUMIDITY NUMERIC(4,2),
    ASH NUMERIC(4,2),
    GRAINUNIT INTEGER,
    GLASSNESS INTEGER,
    DIRT NUMERIC(4,2),
    SCREENING NUMERIC(4,2),
    SMALLGRAIN NUMERIC(4,2),
    GLUTEN NUMERIC(4,1),
    SESSION INTEGER,
    AHUMIDITY NUMERIC(4,2),
    AASH NUMERIC(4,2),
    ADIRT NUMERIC(4,2))
AS
DECLARE VARIABLE RCOUNT INTEGER;
DECLARE VARIABLE RINDEX INTEGER;
DECLARE VARIABLE ACOUNT INTEGER;
DECLARE VARIABLE AINDEX INTEGER;
DECLARE VARIABLE BCOUNT INTEGER;
DECLARE VARIABLE BINDEX INTEGER;
begin
  RDate = :begdate;
  while (:RDate <= :enddate) do begin

    select count(UWOUTACTS.invoiceid)
    from UWOUTACTS
      left join recipes on (recipes.recipeid = UWOUTACTS.recipeid)
    where UWOUTACTS.workingdate = :RDate
      and recipes.storageid = :storageid
      and UWOUTACTS.state > -1
    into :bcount;

    select count(graderesults.graderesultid)
    from graderesults
    where graderesults.workingdate = :rdate
      and graderesults.storageid = :storageid
      and graderesults.storingid = 3
      and (graderesults.qualityaftercleanid is not null and graderesults.qualityaftercleanid <> 0)
    into :acount;

    if (:bcount > :acount) then RCount = :bcount; else RCount = :acount;

    rindex = 0;
    while (:rindex < :rcount) do begin
      if (:rindex < :bcount) then begin
        bindex = 0;
        for
          select
            uwoutacts.WEIGHT, quality_select.HUMIDITY, quality_select.ASH, quality_select.GRAINUNIT, quality_select.GLASSNESS,
            quality_select.DIRT, quality_select.SCREENING, quality_select.SMALLGRAIN, quality_select.GLUTEN
          from UWOUTACTS
            left join quality_select(UWOUTACTS.afterqualityid) on 0 = 0
            left join recipes on (recipes.recipeid = UWOUTACTS.recipeid)
          where UWOUTACTS.workingdate = :RDate
            and recipes.storageid = :storageid
            and UWOUTACTS.state > -1
          into :weight, :humidity, :ash, :grainunit, :glassness, :dirt, :screening, :smallgrain, :gluten
        do begin
          if (:rindex = :bindex) then break;
          else bindex = :bindex + 1;
        end
      end
      else begin
        weight = null;
        humidity = null;
        ash = null;
        grainunit = null;
        glassness = null;
        dirt = null;
        screening = null;
        smallgrain = null;
        gluten = null;
      end

      if (:rindex < :acount) then begin
        aindex = 0;
        for
          select graderesults.session, quality_select.humidity, quality_select.ash, quality_select.dirt
          from graderesults
            left join quality_select(graderesults.qualityaftercleanid) on 0 = 0
          where graderesults.workingdate = :rdate
            and graderesults.storageid = :storageid
            and graderesults.storingid = 3
            and (graderesults.qualityaftercleanid is not null and graderesults.qualityaftercleanid <> 0)
          order by graderesults.session
          into :session, :ahumidity, :aash, :adirt
        do begin
          if (:rindex = :aindex) then break;
          else aindex = :aindex + 1;
        end
      end
      else begin
        session = null;
        ahumidity = null;
        aash = null;
        adirt = null;
      end
      suspend;
      rindex = :rindex + 1;
    end
    RDate = :rdate + 1;
  end
end