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

TIMING

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

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

Описание

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

Определение

CREATE PROCEDURE TIMING(
    BEGINDATE DATE,
    ENDDATE DATE,
    OPERATIONID INTEGER = 1)
RETURNS (
    CONNUMBER INTEGER,
    INVENTORYID INTEGER,
    INVOICEID INTEGER,
    REGDATE DATE,
    CARNUMBER VARCHAR(31),
    TRAILERNUMBER VARCHAR(31),
    CROPFULLNAME VARCHAR(100),
    INVOICENUMBER VARCHAR(30),
    CHECKPOINTINTIME TIMESTAMP,
    CHECKPOINTOUTTIME TIMESTAMP,
    SAMPLERINTIME TIMESTAMP,
    PREANALYSISTIME TIMESTAMP,
    SILAGEINNAME VARCHAR(30),
    GROSSTIME TIMESTAMP,
    LOADCONFIRMTIME TIMESTAMP,
    SILAGEOUTNAME VARCHAR(30),
    TARETIME TIMESTAMP,
    SAMPLEROUTTIME TIMESTAMP,
    CARSTATE INTEGER,
    TRAILERSTATE INTEGER,
    STORAGEID INTEGER,
    CHECKPOINTSESSION INTEGER,
    SAMPLERSESSION INTEGER,
    WEIGHTNUMBER INTEGER,
    WEIGHTUSERID INTEGER,
    WEIGHTSESSION INTEGER,
    DISPATCHINGTIME TIMESTAMP)
AS
declare variable STARTIME time;
declare variable MOVEENDTIME time;
declare variable BEGINTIME timestamp;
declare variable ENDTIME timestamp;
declare variable TABLEOPERATIONID integer;
begin
  select min(SESSIONS.STARTIME), max(SESSIONS.MOVEENDTIME)
    from SESSIONS
  into :STARTIME, :MOVEENDTIME;

  BEGINTIME = :BEGINDATE + :STARTIME;
  ENDTIME = :ENDDATE + 1 + :MOVEENDTIME;
  CONNUMBER = 0;

  for
    select IT.INVOICEID, IT.REGDATE, IT.STORAGEID, ITTRANSPORT.TRANSPORTNUMBER,
        QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, INVOICES.INVOICENUMBER,
        ITTRANSPORT.CHECKPOINTINTIME, ITTRANSPORT.CHECKPOINTOUTTIME,
        ITTRANSPORT.SAMPLERINDATE + ITTRANSPORT.SAMPLERINTIME SAMPLERINTIME,
        ITTRANSPORT.PREANALYSISTIME, ITTRANSPORT.WEIGHTSESSION,
        case
          when (IT.STATE = -3) then
            'возврат'
        end SILAGEINNAME,
        IT.GROSSTIME, IT.LOADCONFIRMTIME,
        case
          when ((IT.STATE = 0) and (coalesce(IT.LOADCONFIRMTIME,IT.TARETIME) is not null)) then
            SILAGE.NAME
        end SILAGEOUTNAME,
        IT.TARETIME,
        ITTRANSPORT.SAMPLEROUTDATE + ITTRANSPORT.SAMPLEROUTTIME SAMPLEROUTTIME,
        IT.STATE, IT.INVENTORYID, IT.WEIGHTNUMBER, IT.WEIGHTUSERID,
        ITTRANSPORT.CHECKPOINTSESSION, ITTRANSPORT.SAMPLERSESSION, ITTRANSPORT.DISPATCHINGTIME
      from ITTRANSPORT
        left join IT on (IT.INVENTORYID = ITTRANSPORT.INVENTORYID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join SILAGE on (SILAGE.SILAGEID = IT.SILAGEID)
        left join QUALITY_CROPFULLNAME_BYID(IT.SAMPLERQUALITYID, 1) on (0=0)
      where (((ITTRANSPORT.CHECKPOINTINTIME between :BEGINTIME and :ENDTIME)
              and ((IT.TARETIME is null) or (IT.TARETIME > :ENDTIME)))
             or (((IT.TARETIME between :BEGINTIME and :ENDTIME) and (IT.TARETIME is not null))
                 and (IT.STATE = 0))
             or ((ITTRANSPORT.PREANALYSISTIME between :BEGINTIME and :ENDTIME)
                 and (IT.STATE = -3))
            )
          and (IT.OPERATIONID = :OPERATIONID) and (ITTRANSPORT.TRANSPORTTYPEID = 0)
          and (IT.PARENTID <= 0) and (IT.STATE in (-3,0))
    order by ITTRANSPORT.CHECKPOINTINTIME
    into :INVOICEID, :REGDATE, :STORAGEID, :CARNUMBER, :CROPFULLNAME, :INVOICENUMBER,
        :CHECKPOINTINTIME, :CHECKPOINTOUTTIME, :SAMPLERINTIME, :PREANALYSISTIME,
        :WEIGHTSESSION, :SILAGEINNAME, :GROSSTIME, :LOADCONFIRMTIME, :SILAGEOUTNAME,
        :TARETIME, :SAMPLEROUTTIME, :CARSTATE, :INVENTORYID, :WEIGHTNUMBER,
        :WEIGHTUSERID, :CHECKPOINTSESSION, :SAMPLERSESSION, :DISPATCHINGTIME

  do begin
    if (:CARSTATE = 0) then
      select first 1 ILT$TABLEOPERATION.TABLEOPERATIONID, SILAGE.NAME
        from ILT$TABLEOPERATION
          left join ILT$FIELDINTEGER on (ILT$FIELDINTEGER.TABLEOPERATIONID = ILT$TABLEOPERATION.TABLEOPERATIONID)
          left join SILAGE on (SILAGE.SILAGEID = ILT$FIELDINTEGER.FIELDVALUE)
        where (ILT$TABLEOPERATION.RECORDID = :INVENTORYID)
          and (ILT$FIELDINTEGER.FIELDID = 31)
      into :TABLEOPERATIONID, :SILAGEINNAME;

    TRAILERNUMBER = null;
    TRAILERSTATE = null;

    select ITTRANSPORT.TRANSPORTNUMBER, IT.STATE
      from IT
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (IT.INVOICEID = :INVOICEID)
        and (ITTRANSPORT.TRANSPORTTYPEID = 1) and (IT.STATE <> -1)
    into :TRAILERNUMBER, :TRAILERSTATE;

    CONNUMBER = CONNUMBER + 1;
    suspend;
  end
end