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

SILAGE_CALC_QUALITY_UNDERWORK

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

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

(Нет выходных параметров)

Описание

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

Определение

CREATE PROCEDURE SILAGE_CALC_QUALITY_UNDERWORK(
    RELATIONID INTEGER,
    QUALITYID INTEGER,
    OPERATIONID INTEGER = 1)
AS
declare variable QUALITYTYPEID integer;
declare variable USESUBTYPE integer;
declare variable CATEGORYID integer;
declare variable QUALITYVALUE numeric(9,2);
declare variable MAXQUALITYVALUE integer;
declare variable CROPID integer;
declare variable CLASSID integer;
declare variable CROPTYPEID integer;
declare variable SCREENING numeric(4,2);
declare variable SUM_SCREENING numeric(4,2);
begin
  delete from QUALITYDATA
  where (QUALITYDATA.QUALITYID = :QUALITYID);

  for
    select QUALITYDATA.QUALITYTYPEID, max(QUALITYTYPES.USESUBTYPES), max(QUALITYTYPES.CATEGORYID),
        sum(IT.NET * QUALITYDATA.QUALITYVALUE) / nullif(sum(IT.NET + QUALITYDATA.QUALITYVALUE - QUALITYDATA.QUALITYVALUE),0),
        max(QUALITYDATA.QUALITYVALUE)
      from IT
        left join QUALITY on (QUALITY.QUALITYID = IT.LABQUALITYID)
        left join QUALITYDATA on (QUALITYDATA.QUALITYID = IT.LABQUALITYID)
        left join SILAGE on (SILAGE.SILAGEID = IT.SILAGEID)
        left join QUALITYTYPES on (QUALITYTYPES.QUALITYTYPEID = QUALITYDATA.QUALITYTYPEID)
      where (IT.OPERATIONID = :OPERATIONID) and (IT.RELATIONID = :RELATIONID)
        and QUALITYDATA.QUALITYTYPEID not in (1,54,86,96)
    group by QUALITYDATA.QUALITYTYPEID, QUALITYTYPES.QUALITYSHORTNAME
    into :QUALITYTYPEID, :USESUBTYPE, :CATEGORYID, :QUALITYVALUE, :MAXQUALITYVALUE
  do
    if ((:USESUBTYPE = 1) or (:CATEGORYID = 1)) then
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:QUALITYID, :QUALITYTYPEID, :MAXQUALITYVALUE);
    else
      insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
        values (:QUALITYID, :QUALITYTYPEID, :QUALITYVALUE);

  select QUALITY.CROPID, QUALITY.CLASSID, CROP.CROPTYPEID
    from CROP
      left join QUALITY on (QUALITY.QUALITYID = :QUALITYID)
    where (CROP.CROPID = QUALITY.CROPID)
  into :CROPID, :CLASSID, :CROPTYPEID;

  select QUALITYDATA.QUALITYVALUE
    from QUALITYDATA
    where (QUALITYDATA.QUALITYID = :QUALITYID)
      and (((:CROPTYPEID <> 5) and (QUALITYDATA.QUALITYTYPEID = 14))
           or ((:CROPTYPEID = 5) and (QUALITYDATA.QUALITYTYPEID = 110))
          )
  into :SCREENING;

  select sum(QUALITYDATA.QUALITYVALUE)
    from QUALITY
      left join QUALITYDATA on (QUALITYDATA.QUALITYID = :QUALITYID)
      left join QUALITY_GET_COLLECTION(10, :CROPID, :CLASSID) on (QUALITY_GET_COLLECTION.QUALITYTYPEID = QUALITYDATA.QUALITYTYPEID)
    where (QUALITY.QUALITYID = :QUALITYID)
      and (((:CROPTYPEID <> 5) and (QUALITY_GET_COLLECTION.QUALITYPARENTID = 14))
           or ((:CROPTYPEID = 5) and (QUALITY_GET_COLLECTION.QUALITYPARENTID = 110))
          )
  into :SUM_SCREENING;

  if (((SCREENING - SUM_SCREENING) = 0.01) or (SCREENING < SUM_SCREENING)) then
    update QUALITYDATA
    set QUALITYVALUE = :SUM_SCREENING
    where (QUALITYID = :QUALITYID)
      and (((:CROPTYPEID <> 5) and (QUALITYTYPEID = 14))
           or ((:CROPTYPEID = 5) and (QUALITYTYPEID = 110))
          );

  suspend;
end