"localhost:/firebird/data/ILICHEVSK2.FDB". Триггеры

SILAGEPACKS_CalcQuality

Описание

(Нет описания для триггера SILAGEPACKS_CalcQuality)

Определение

CREATE TRIGGER "SILAGEPACKS_CalcQuality" FOR SILAGEPACKS
ACTIVE BEFORE UPDATE POSITION 0
as
declare variable QUALITYID integer;
declare variable CROPID integer;
declare variable SORTID integer;
declare variable RWEIGHT integer;
declare variable QTYPEID integer;
declare variable QVALUE numeric(9,2);
declare variable QMAXVALUE integer;
declare variable USESUBTYPE integer;
declare variable CATEGORYID integer;
declare variable QUALITYCOUNT integer;
declare variable MIXINGCLASSES integer;

begin
  if ((not (new.SILAGEQUALITYID is null))
      and ((old.SILAGEQUALITYID is null) or (not (old.SILAGEQUALITYID = new.SILAGEQUALITYID)))) then begin

/*
    select STORAGE.MIXINGCLASSES
      from STORAGE
      where (STORAGE.STORAGEID = 1)
    into :MIXINGCLASSES;

    select
        max(SILAGEQUALITY.QUALITYID), count(SILAGEQUALITY.QUALITYID)
      from SILAGEQUALITY
      where (SILAGEQUALITY.SILAGEQUALITYID = new.SILAGEQUALITYID)
*/
    select
        max(SILAGEQUALITY.QUALITYID), count(SILAGEQUALITY.QUALITYID),
        max(STORAGE.MIXINGCLASSES)
      from SILAGEQUALITY
        left join SILAGEPACKS on (SILAGEPACKS.SILAGEQUALITYID = new.SILAGEQUALITYID)
        left join STORAGE on (STORAGE.STORAGEID = coalesce(old.STORAGEID,1))
      where (SILAGEQUALITY.SILAGEQUALITYID = new.SILAGEQUALITYID)
    into :QUALITYID, :QUALITYCOUNT, :MIXINGCLASSES;

    if (:QUALITYCOUNT = 1) then begin
      new.STATE = 1;

      if (:MIXINGCLASSES <> 1) then
        new.QUALITYID = :QUALITYID;
    end

    if ((:MIXINGCLASSES = 1) or (:QUALITYCOUNT > 1)) then begin
      QUALITYID = gen_id(QUALITYIDGEN, 1);

      for
        select
            max(QUALITY.CROPID), max(QUALITY.SORTID), sum(SILAGEQUALITY.WEIGHT),
            QUALITYDATA.QUALITYTYPEID, max(QUALITYTYPES.USESUBTYPES),
            max(QUALITYTYPES.CATEGORYID), max(QUALITYDATA.QUALITYVALUE),
            case
              when (sum(SILAGEQUALITY.WEIGHT) <> 0) then
                sum(QUALITYDATA.QUALITYVALUE * SILAGEQUALITY.WEIGHT) / sum(SILAGEQUALITY.WEIGHT + QUALITYDATA.QUALITYVALUE - QUALITYDATA.QUALITYVALUE)
              else max(QUALITYDATA.QUALITYVALUE)
            end
          from SILAGEQUALITY
            left join QUALITY on (QUALITY.QUALITYID = SILAGEQUALITY.QUALITYID)
            left join QUALITYDATA on (QUALITYDATA.QUALITYID = SILAGEQUALITY.QUALITYID)
            left join QUALITYTYPES on (QUALITYTYPES.QUALITYTYPEID = QUALITYDATA.QUALITYTYPEID)
          where (SILAGEQUALITY.SILAGEQUALITYID = new.SILAGEQUALITYID)
        group by QUALITYDATA.QUALITYTYPEID
        into :CROPID, :SORTID, :RWEIGHT, :QTYPEID, :USESUBTYPE,
             :CATEGORYID, :QMAXVALUE, :QVALUE
      do begin
        if (:QTYPEID is not null) then begin
          if ((:USESUBTYPE = 1) or (:CATEGORYID = 1)) then
            insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
            values (:QUALITYID, :QTYPEID, :QMAXVALUE);
          else
            insert into QUALITYDATA(QUALITYID, QUALITYTYPEID, QUALITYVALUE)
            values (:QUALITYID, :QTYPEID, :QVALUE);
        end
      end

      if (:MIXINGCLASSES = 1) then begin
        insert into QUALITY (QUALITYID, CROPID)
          values (:QUALITYID, :CROPID);
        new.STATE = 1;
        new.QUALITYID = :QUALITYID;
      end
      else begin
        insert into QUALITY (QUALITYID, CROPID, SORTID)
          values(:QUALITYID, :CROPID, :SORTID);
        new.STATE = 5;
      end
    end
  end
end