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

SETSUMMARYSTATUS

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

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

Описание

Формирование сводки по резервуарному парку

Определение

CREATE PROCEDURE SETSUMMARYSTATUS(
    STATUSDATE DATE,
    STATUSTIME TIME,
    STORAGEID INTEGER)
RETURNS (
    SILAGEID INTEGER)
AS
declare variable SILAGE_ID integer;
declare variable HEIGHT decimal(6,1);
declare variable VOLUME decimal(10,3);
declare variable TEMPERATURE numeric(6,2);
declare variable CONSISTENCE decimal(6,3);
declare variable MASS decimal(10,3);
declare variable BALANCEBEGIN decimal(10,3);
declare variable INCOME decimal(10,3);
declare variable OUTCOME decimal(10,3);
begin
  for
    select SILAGE.SILAGEID, SILAGEPACKS.HEIGHT, SILAGEPACKS.VOLUME,
        SILAGEPACKS.TEMPERATURE, cast(SILAGEPACKS.MASS as numeric(10,3)) / 1000,
        CROP.CONSISTENCE
      from SILAGE
        left join SILAGEPACKS on (SILAGEPACKS.SILAGEPACKID = SILAGE.SILAGEPACKID)
        left join QUALITY on (QUALITY.QUALITYID = SILAGEPACKS.QUALITYID)
        left join CROP on (CROP.CROPID = QUALITY.CROPID)
      where (SILAGEPACKS.STORAGEID = :STORAGEID)
    into :SILAGEID, :HEIGHT, :VOLUME, :TEMPERATURE, :MASS, :CONSISTENCE
  do begin
    BALANCEBEGIN = 0;
    INCOME = 0;
    OUTCOME = 0;

    select
        sum(case
              when (OPERATIONTIME < :STATUSDATE - 1) then
                INCOME - OUTCOME
              else 0
            end) / 1000 BALANCEBEGIN,
        sum(case
              when (cast(OPERATIONTIME as date) between :STATUSDATE - 1 and :STATUSDATE) then
                INCOME
            end) / 1000 INCOME,
        sum(case
              when (cast(OPERATIONTIME as date) between :STATUSDATE - 1 and :STATUSDATE) then
                OUTCOME
            end) / 1000 OUTCOME
      from (
        select SILAGEID, INCOME, OUTCOME, OPERATIONID, OPERATIONTIME
          from (
            select SILAGE.SILAGEID SILAGEID, SILAGEPACKS.EMPTYDATE EMPTYDATE,
                IT.INVENTORYID ITINVENTORYID, IT.OPERATIONID OPERATIONID,
                case
                  when ((IT.OPERATIONID = 1) or ((IT.OPERATIONID = 29) and (IT.SILAGEID = SILAGE.SILAGEID))) then
                    cast(IT.NET as numeric (12,3))
                  else 0
                end INCOME,
                case
                  when ((IT.OPERATIONID = 5) or ((IT.OPERATIONID = 29) and (IT.ROUTEID = SILAGE.SILAGEPACKID))) then
                    cast(IT.NET as numeric (12,3))
                  else 0
                end OUTCOME,
                case
                  when(IT.OPERATIONID in (5,29)) then
                    coalesce(IT.GROSSTIME,cast(IT.REGDATE as timestamp))
                  else IT.TARETIME
                end OPERATIONTIME
              from SILAGE
                left join SILAGEPACKS on (SILAGEPACKS.SILAGEPACKID = SILAGE.SILAGEPACKID)
                left join IT on ((IT.REGDATE between cast(SILAGEPACKS.EMPTYDATE as date) and :STATUSDATE)
                                 and (IT.OPERATIONID in (1,5,29)) and (IT.NET is not null) and (IT.STATE >= 0)
                                 and ((IT.SILAGEID = SILAGE.SILAGEID) or (IT.ROUTEID = SILAGE.SILAGEPACKID)))
              where (SILAGE.SILAGEID = :SILAGEID)
          )
            left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = ITINVENTORYID)
          where (OPERATIONTIME >= EMPTYDATE)
      )
    group by SILAGEID
    order by SILAGEID
    into :BALANCEBEGIN, :INCOME, :OUTCOME;

    insert into SUMMARYSTATUS (SILAGEID, STATUSDATE, STATUSTIME, HEIGHT, VOLUME, TEMPERATURE,
                               CONSISTENCE, MASS, BALANCEBEGIN, INCOME, OUTCOME, STORAGEID)
      values (:SILAGEID, :STATUSDATE, :STATUSTIME, :HEIGHT, :VOLUME, :TEMPERATURE,
              :CONSISTENCE, :MASS, :BALANCEBEGIN, :INCOME, :OUTCOME, :STORAGEID);
  end

  suspend;
end