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

GRAIN_OUT_ON_CLASS_ON_RECIPE

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

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

Описание

Расход зерна по классам по помольным партиям

Определение

CREATE PROCEDURE GRAIN_OUT_ON_CLASS_ON_RECIPE(
    ADATEBEGIN DATE,
    ADATEEND DATE,
    STORAGEID INTEGER)
RETURNS (
    STORAGENAME VARCHAR(100),
    RECIPEN VARCHAR(20),
    WEIGHT2 INTEGER,
    WEIGHT3 INTEGER,
    WEIGHT4 INTEGER,
    WEIGHT5 INTEGER,
    WEIGHT6 INTEGER,
    WEIGHT_A INTEGER,
    WEIGHT_B INTEGER)
AS
DECLARE VARIABLE CLASSID INTEGER;
DECLARE VARIABLE WEIGHT INTEGER;
DECLARE VARIABLE MAXCLASS INTEGER;
DECLARE VARIABLE RECIPEID INTEGER;
begin
  for
    select storage.storagename, recipes.recipen, it.operationparam, Sum(it.net), recipes.recipeid, recipes.storageid
    from it
      left join Quality on (it.labqualityid = Quality.qualityid)
      left join recipes on (it.relationid = recipes.recipeid)
      left join storage on (recipes.storageid = storage.storageid)
      left join uwoutacts on (it.invoiceid = uwoutacts.invoiceid)
    where it.operationid = 7
      and uwoutacts.workingdate >= :ADateBegin
      and uwoutacts.workingdate <= :ADateEnd
--      and it.regdate >= :ADateBegin
--      and it.regdate <= :ADateEnd
      and recipes.storageid = :storageid
      and it.state <> -1
      and it.parentid <= 0

    group by storage.storagename, recipes.recipen, it.operationparam, recipes.recipeid, recipes.storageid
    into :storagename, :recipen, :classid, :weight, :recipeid, :storageid
  do begin
    select max(classid) from recipesilages
      left join quality on (recipesilages.qualityid = quality.qualityid)
    where recipesilages.recipeid = :recipeid
    into :maxclass;

    if (:classid = 2) then weight2 = :weight;
    if (:classid = 3) then weight3 = :weight;
    if (:classid = 4) then weight4 = :weight;
    if (:classid = 5) then weight5 = :weight;
    if (:classid = 6) then weight6 = :weight;
    if (:classid = 10) then weight_a = :weight;
    if (:classid = 11) then weight_b = :weight;
    if (:classid = :maxclass) then begin
      suspend;
      weight3 = null;
      weight4 = null;
      weight5 = null;
      weight6 = null;
      weight_a = null;
      weight_b = null;
    end
  end
END