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

YIELD_GRAFF_VOLUME

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

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

Описание

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

Определение

CREATE PROCEDURE YIELD_GRAFF_VOLUME(
    BEGDATE DATE,
    ENDDATE DATE,
    STORAGEID INTEGER)
RETURNS (
    GRADEDATE DATE,
    MEALEXTRA NUMERIC(9,2),
    MEAL1 NUMERIC(9,2),
    MEAL2 NUMERIC(9,2),
    MEAL3 NUMERIC(9,2),
    SEMOLINA NUMERIC(9,2),
    BRAN NUMERIC(9,2),
    WASTE NUMERIC(9,2),
    SUMPRODUCTION NUMERIC(9,2),
    BASIS_MEALEXTRA NUMERIC(9,2),
    BASIS_MEAL1 NUMERIC(9,2),
    BASIS_MEAL2 NUMERIC(9,2),
    BASIS_MEAL3 NUMERIC(9,2),
    BASIS_SEMOLINA NUMERIC(9,2),
    BASIS_BRAN NUMERIC(9,2),
    BASIS_WASTE NUMERIC(9,2),
    BASIS_SUMPRODUCTION NUMERIC(9,2),
    BASIS_FLAKES NUMERIC(9,2),
    BASIS_WASTEUSELES NUMERIC(9,2))
AS
DECLARE VARIABLE CLASSID INTEGER;
DECLARE VARIABLE WEIGHT INTEGER;
DECLARE VARIABLE OLD_DATE DATE;
DECLARE VARIABLE TOTALWEIGHT INTEGER;
begin /*$$IBE$$ 
  select semolina, mealextra, meal1, meal2, meal3, waste, bran, flakes, wasteuseless
  from gradebasises
  where gradebasises.storageid = :StorageID and gradebasises.state = 1
  into :basis_semolina, :basis_mealextra, :basis_meal1, :basis_meal2, :basis_meal3,
       :basis_waste, :basis_bran, :basis_flakes, :basis_wasteuseles;

  basis_sumproduction = coalesce(:basis_semolina, 0) + coalesce(:basis_mealextra, 0) + coalesce(:basis_meal1, 0) + coalesce(:basis_meal2, 0) + coalesce(:basis_meal3, 0);

  sumproduction = 0;
  TOTALWEIGHT = 0;
  gradedate = '1.1.1';
  for
    select graderesults.gradedate, gradeouts.classid, sum(gradeouts.weight)
    from graderesults
      left join gradeouts on (graderesults.graderesultid = gradeouts.graderesultid)
    where graderesults.gradedate between :BegDate and :EndDate
      and graderesults.storingid = 3
      and graderesults.storageid = :StorageID
    group by graderesults.gradedate, gradeouts.classid
    into :OLD_DATE, :CLASSID, :WEIGHT
  do begin
    if (:OLD_DATE <> :gradedate) then begin
      if (:TOTALWEIGHT = 0) then TOTALWEIGHT = null;
      mealextra = :mealextra * 100 / :TOTALWEIGHT;
      meal1 = :meal1 * 100 / :TOTALWEIGHT;
      meal2 = :meal2 * 100 / :TOTALWEIGHT;
      bran = :bran * 100 / :TOTALWEIGHT;
      semolina = :semolina * 100 / :TOTALWEIGHT;
      waste = :waste * 100 / :TOTALWEIGHT;
      meal3 = :meal3 * 100 / :TOTALWEIGHT;
      sumproduction = :sumproduction * 100 / :TOTALWEIGHT;
      if (:gradedate <> '1.1.1') then suspend;
      gradedate = :OLD_DATE;
      sumproduction = 0;
      TOTALWEIGHT = 0;
      mealextra = 0;
      meal1 = 0;
      meal2 = 0;
      bran = 0;
      semolina = 0;
      waste = 0;
      meal3 = 0;
    end
    if (:CLASSID = 13) then mealextra = :WEIGHT;
    if (:CLASSID = 14) then meal1 = :WEIGHT;
    if (:CLASSID = 15) then meal2 = :WEIGHT;
    if (:CLASSID = 46) then bran = :WEIGHT;
    if (:CLASSID = 44) then semolina = :WEIGHT;
    if (:CLASSID = 66) then waste = :WEIGHT;
    if (:CLASSID = 65) then meal3 = :WEIGHT;
    if (:CLASSID in (13, 14, 15, 44, 65)) then sumproduction = :sumproduction + :WEIGHT;
    TOTALWEIGHT = :TOTALWEIGHT + :WEIGHT;
  end
  if (:gradedate <> '1.1.1') then begin
    if (:TOTALWEIGHT = 0) then TOTALWEIGHT = null;
    mealextra = :mealextra * 100 / :TOTALWEIGHT;
    meal1 = :meal1 * 100 / :TOTALWEIGHT;
    meal2 = :meal2 * 100 / :TOTALWEIGHT;
    bran = :bran * 100 / :TOTALWEIGHT;
    semolina = :semolina * 100 / :TOTALWEIGHT;
    waste = :waste * 100 / :TOTALWEIGHT;
    meal3 = :meal3 * 100 / :TOTALWEIGHT;
    sumproduction = :sumproduction * 100 / :TOTALWEIGHT;
    suspend;
  end
 $$IBE$$*/ EXIT;
end