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

RENUMBERING_LABQUALITY

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

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

Описание

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

Определение

CREATE PROCEDURE RENUMBERING_LABQUALITY(
    BEGINDATE DATE,
    ENDDATE DATE,
    OPERATIONID INTEGER)
RETURNS (
    LABQUALITYID INTEGER,
    LABNUMBER INTEGER,
    TRANSPORTTYPEID INTEGER,
    HARVESTYEAR INTEGER,
    NEW_LABNUMBER INTEGER,
    LABQUALITYNUMBER INTEGER,
    GENERATORNAME VARCHAR(31))
AS
declare variable AOPERATIONID integer = -1;
declare variable ATRANSPORTSUBTYPE integer = -1;
declare variable AHARVESTINGYEAR integer = -1;
declare variable AUTONUMBERINGANALYSES integer;
declare variable REGDATE date;
declare variable HARVESTINGYEAR integer;
begin
  NEW_LABNUMBER = 0;

  select coalesce(ENVIRONMENTOPTIONS.AUTONUMBERINGANALYSES,0)
    from ENVIRONMENTOPTIONS
  into :AUTONUMBERINGANALYSES;

  for
    select PARTIES.HARVESTINGYEAR
      from PARTIES
      where (PARTIES.STATE = 1)
    group by PARTIES.HARVESTINGYEAR
    into :HARVESTINGYEAR

  do begin
    if (AUTONUMBERINGANALYSES > 1) then begin
      if (bin_and(AUTONUMBERINGANALYSES,2) = 2) then
        AOPERATIONID = :OPERATIONID;

      if (bin_and(AUTONUMBERINGANALYSES,4) = 4) then
        ATRANSPORTSUBTYPE = 0;

      if (bin_and(AUTONUMBERINGANALYSES,8) = 8) then
        AHARVESTINGYEAR = :HARVESTINGYEAR;
    end

    while ((ATRANSPORTSUBTYPE < 3) and (AHARVESTINGYEAR > -100)) do begin
      for
        select IT.REGDATE, IT.LABQUALITYID, QUALITY_SELECT.QUALITYNUMBER,
            min(QUALITY_SELECT.HARVESTYEAR), min(ITTRANSPORT.TRANSPORTTYPEID)
          from IT
            left join QUALITY_SELECT(IT.LABQUALITYID) on (0=0)
            left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
            left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
            left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
          where (IT.REGDATE between :BEGINDATE and :ENDDATE)
            and (IT.LABQUALITYID is not null) and (IT.STATE = 0)
            and ((:AOPERATIONID = -1) or (IT.OPERATIONID = :AOPERATIONID))
            and (OPERATION.OPERATIONSUBTYPE < 2)
            and ((:ATRANSPORTSUBTYPE = -1)
                 or ((:ATRANSPORTSUBTYPE > -1) and (TRANSPORTTYPE.TRANSPORTSUBTYPE = :ATRANSPORTSUBTYPE)))
            and ((:AHARVESTINGYEAR = -1) or (QUALITY_SELECT.HARVESTYEAR = :HARVESTINGYEAR))
        group by IT.LABQUALITYID, QUALITY_SELECT.QUALITYNUMBER, IT.REGDATE
        order by IT.REGDATE, QUALITY_SELECT.QUALITYNUMBER
        into :REGDATE, :LABQUALITYID, :LABNUMBER, :HARVESTYEAR, :TRANSPORTTYPEID

      do begin
        if (NEW_LABNUMBER = 0) then
          NEW_LABNUMBER = LABNUMBER;
        else NEW_LABNUMBER = NEW_LABNUMBER + 1;

        if (NEW_LABNUMBER <> LABNUMBER) then
          update QUALITYDATA
          set QUALITYVALUE = :NEW_LABNUMBER
          where ((QUALITYID = :LABQUALITYID) and (QUALITYTYPEID = 1));

        suspend;
      end

      if (NEW_LABNUMBER > 0) then
        select GET_LABQUALITYNUMBER.LABQUALITYNUMBER, GET_LABQUALITYNUMBER.GENERATORNAME
          from GET_LABQUALITYNUMBER(:OPERATIONID, :TRANSPORTTYPEID, 1, :NEW_LABNUMBER)
        into :LABQUALITYNUMBER, :GENERATORNAME;
      else GENERATORNAME = null;

      suspend;

      if (ATRANSPORTSUBTYPE <> -1) then begin
        ATRANSPORTSUBTYPE = ATRANSPORTSUBTYPE + 1;
        NEW_LABNUMBER = 0;
      end
      else ATRANSPORTSUBTYPE = 3;

      if ((AHARVESTINGYEAR = -1) and (ATRANSPORTSUBTYPE = 3)) then
        AHARVESTINGYEAR = -100;
    end
  end
end