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

SILAGE_CALC_QUALITY_FROM_IT

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

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

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

Описание

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

Определение

CREATE PROCEDURE SILAGE_CALC_QUALITY_FROM_IT(
    BEGDATE DATE,
    ENDDATE DATE,
    SILAGEPACKID INTEGER,
    CALCTYPE INTEGER)
AS
declare variable CROPID integer;
declare variable CLASSID integer;
declare variable SORTID integer;
declare variable QTYPEID integer;
declare variable QVALUE numeric(9,2);
declare variable QMAXVALUE integer;
declare variable PACKQUALITYID integer;
declare variable SILAGEQUALITYID integer;
declare variable WEIGHT integer;
declare variable USESUBTYPE integer;
declare variable CATEGORYID integer;
declare variable CALCCONDITION integer;
declare variable CLEANOUTID integer;
declare variable PREVCLASSID integer;
declare variable NET integer;
declare variable QUALITY_CROP integer;
declare variable MIXINGCLASSES integer;
declare variable CLASSQUALITYID integer;
declare variable SILAGEQUALITYID_OLD integer;
begin
  select MIXINGCLASSES
     from STORAGE
     where (STORAGEID = 1)
  into :MIXINGCLASSES;

  select SILAGEPACKS.QUALITYID
    from SILAGEPACKS
    where (SILAGEPACKS.SILAGEPACKID = :SILAGEPACKID)
  into :QUALITY_CROP;

  if (:MIXINGCLASSES = 1) then begin
    SILAGEQUALITYID = gen_id(SILAGEQUALITYIDGEN,1);
    select SILAGEQUALITYID
      from SILAGEPACKS
      where (SILAGEPACKID = :SILAGEPACKID)
    into SILAGEQUALITYID_OLD;
   -- QUALITY_CROP = gen_id(QUALITYIDGEN, 1);
    CLASSQUALITYID = null;
  end
  else CLASSQUALITYID = gen_id(QUALITYIDGEN,1);

  CROPID = null;
  PREVCLASSID = -1;

  if (CALCTYPE = 1) then begin
    for
      select max(INVENTORYID)
        from IT
        where (REGDATE between :BEGDATE and :ENDDATE) and (OPERATIONID = 27) and (SILAGEID = :SILAGEPACKID)
      into :CLEANOUTID
    do begin
      if (:CLEANOUTID is null) then
        CLEANOUTID = 0;
        CALCCONDITION = :CLEANOUTID;
    end
  end
  else CALCCONDITION = 0;

  for
    select max(QUALITY.CROPID), max(QUALITY.CLASSID), max(QUALITY.SORTID), 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),
        sum(IT.NET) NET
      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)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (IT.OPERATIONID in (1,12,29,31,32,54))
        and (IT.REGDATE between :BEGDATE and :ENDDATE)
        and (IT.NET is not null) and (IT.STATE > -1) and (IT.PARENTID <= 0)
        and (((ITTRANSPORT.TRANSPORTTYPEID < 2) and (SILAGE.SILAGEPACKID = :SILAGEPACKID))
             or (((ITTRANSPORT.TRANSPORTTYPEID = 2) or (IT.OPERATIONID in (12,29,31,32,54)))
                 and (SILAGE.SILAGEPACKID = :SILAGEPACKID)))
        and QUALITYDATA.QUALITYTYPEID not in (1,54,86,96)
        and (IT.INVENTORYID between :CALCCONDITION and gen_id(INVENTORYIDGEN,0))
    group by QUALITY.CLASSID, QUALITYDATA.QUALITYTYPEID
    into :CROPID, :CLASSID, :SORTID, :QTYPEID, :USESUBTYPE, :CATEGORYID, :QVALUE, :QMAXVALUE, :NET

  do begin
    if (:MIXINGCLASSES = 1) then begin
      if ((:CLASSID is not null) and (:CLASSID <> :PREVCLASSID)) then begin
        if (:CROPID is not null) then begin
          select SILAGEQUALITY.QUALITYID
            from SILAGEPACKS
              left join SILAGEQUALITY on (SILAGEQUALITY.SILAGEQUALITYID = SILAGEPACKS.SILAGEQUALITYID)
              left join QUALITY on (QUALITY.QUALITYID = SILAGEQUALITY.QUALITYID)
            where (SILAGEPACKS.SILAGEPACKID = :SILAGEPACKID)
              and (QUALITY.CLASSID = :CLASSID)
          into :CLASSQUALITYID;

          delete from QUALITYDATA
            where (QUALITYDATA.QUALITYID = :CLASSQUALITYID);

          PREVCLASSID = :CLASSID;
        end
      end
    end
--------------------------------------------------------------------------------
    if (CLASSQUALITYID is not null) then
      if ((:USESUBTYPE = 1) or (:CATEGORYID = 1)) then
        insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
          values (:CLASSQUALITYID, :QTYPEID, :QMAXVALUE);
      else
        insert into QUALITYDATA (QUALITYID, QUALITYTYPEID, QUALITYVALUE)
          values (:CLASSQUALITYID, :QTYPEID, :QVALUE);
  end

  if (:MIXINGCLASSES <> 1) then begin
    if (:CROPID is not null) then begin
      insert into QUALITY (QUALITYID, CLASSID, CROPID, SORTID)
        values (:CLASSQUALITYID, :CLASSID, :CROPID, :SORTID);

      select WEIGHT
        from SILAGEPACKS
        where (SILAGEPACKID = :SILAGEPACKID)
      into :WEIGHT;

      SILAGEQUALITYID = gen_id(SILAGEQUALITYIDGEN,1);
      insert into SILAGEQUALITY(SILAGEQUALITYID, QUALITYID, WEIGHT)
        values(:SILAGEQUALITYID, :CLASSQUALITYID, :WEIGHT);

      update SILAGEPACKS
      set SILAGEQUALITYID = :SILAGEQUALITYID,
          QUALITYID = :CLASSQUALITYID,
          STATE = 1
      where (SILAGEPACKID = :SILAGEPACKID);
    end
  end
  else begin  --- ??? надо считать для QUALITY_CROP средневзвешенные показатели ???
    update SILAGEQUALITY
    set SILAGEQUALITYID = :SILAGEQUALITYID
    where (SILAGEQUALITYID = :SILAGEQUALITYID_OLD);

    update SILAGEPACKS
    set SILAGEQUALITYID = :SILAGEQUALITYID,
        STATE = 1
    where (SILAGEPACKID = :SILAGEPACKID);
  end

end