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

DESK_JOURNAL_RAILROAD

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

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

Описание

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

Определение

CREATE PROCEDURE DESK_JOURNAL_RAILROAD(
    SAMPLERINBEGINDATE DATE,
    SAMPLERINENDDATE DATE,
    AOPERATIONID INTEGER,
    ASTORAGEID INTEGER,
    SHOWNOTWEIGHTED INTEGER = 0,
    SHOWDELETED INTEGER = 0)
RETURNS (
    INVENTORYID INTEGER,
    REGDATE DATE,
    ORDERLOADID INTEGER,
    ORDERLOADITEMID INTEGER,
    SAMPLERINDATE DATE,
    TRANSPORTNUMBER VARCHAR(25),
    TRANSPORTNUMBERX VARCHAR(25),
    INVOICEID INTEGER,
    STORAGEID INTEGER,
    STORAGENAME VARCHAR(100),
    SILAGEID INTEGER,
    ROUTEID INTEGER,
    PLATFORMID INTEGER,
    SILAGENAME VARCHAR(255),
    GROSSTIME TIMESTAMP,
    TARETIME TIMESTAMP,
    GROSS INTEGER,
    TARE INTEGER,
    NET INTEGER,
    INVOICEGROSS INTEGER,
    INVOICETARE INTEGER,
    INVOICENET INTEGER,
    WEIGHTNUMBER INTEGER,
    NETDEFICITE BIGINT,
    SAMPLERQUALITYID INTEGER,
    TRANSPORTTYPEID INTEGER,
    STATIONNAME VARCHAR(30),
    OPERATIONID INTEGER,
    CROPFULLNAME VARCHAR(100),
    INVOICENUMBER VARCHAR(31),
    OWNERID INTEGER,
    OWNERNAME VARCHAR(100),
    PROVIDERID INTEGER,
    PROVIDERNAME VARCHAR(100),
    SILAGEUSERNAME VARCHAR(31),
    STATE INTEGER,
    WEIGHTSESSION INTEGER,
    SESSIONNAME VARCHAR(10),
    HUMIDITY NUMERIC(4,2),
    DIRT NUMERIC(4,2))
AS
begin
  for
    select IT.INVENTORYID, IT.REGDATE, ORDERLOADITEMS.ORDERLOADID, IT.ORDERLOADITEMID,
        ITTRANSPORT.SAMPLERINDATE, ITTRANSPORT.TRANSPORTNUMBER, IT.INVOICEID,
        IT.STORAGEID, STORAGE.STORAGENAME, IT.SILAGEID, IT.ROUTEID, ROUTECHEMA.PLATFORMID,
        case :AOPERATIONID
          when 1 then
            coalesce(PLATFORMS.PLATFORMSHORTNAME || ' - ', '') || coalesce(SILAGE.NAME, '')
--        when 1 then SILAGE.NAME
-- если по приказам  when 5 then (select sampler_silagename.silagestring from sampler_silagename(IT.INVENTORYID))
          when 5 then
            SILAGE.NAME
        end SILAGENAME,
        IT.GROSSTIME, IT.TARETIME, IT.GROSS, IT.TARE, IT.NET, ITTRANSPORT.INVOICEGROSS,
        ITTRANSPORT.INVOICETARE, ITTRANSPORT.INVOICENET, IT.WEIGHTNUMBER,
        (ITTRANSPORT.INVOICENET - IT.NET) NETDEFICITE, IT.SAMPLERQUALITYID,
        ITTRANSPORT.TRANSPORTTYPEID, STATIONS.STATIONNAME, IT.OPERATIONID,
        QUALITY_CROPFULLNAME_BYID.CROPFULLNAME, INVOICES.INVOICENUMBER,
        IT.OWNERID, OWNERS.CLIENTFULLNAME, IT.PROVIDERID, PROVIDERS.CLIENTFULLNAME,
        USERS.USERNAME SILAGEUSERNAME, IT.STATE, ITTRANSPORT.WEIGHTSESSION, SESSIONS.SESSIONNAME,
        BUHQUALITY.HUMIDITY HUMIDITY, BUHQUALITY.DIRT DIRT
      from IT
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join QUALITY_CROPFULLNAME_BYID(IT.SAMPLERQUALITYID, 1) on (1 = 1)
        left join TRANSPORTTYPE on ((TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID) and (TRANSPORTTYPE.TRANSPORTTYPEID = 2))
        left join INVOICES on (IT.INVOICEID = INVOICES.INVOICEID)
        left join CLIENTS PROVIDERS on (PROVIDERS.CLIENTID = IT.PROVIDERID)
        left join CLIENTS OWNERS on (OWNERS.CLIENTID = IT.OWNERID)
        left join STATIONS on (STATIONS.STATIONID = INVOICES.STATIONID)
        left join SILAGE on (SILAGE.SILAGEID = IT.SILAGEID)
        left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
        left join USERS on (USERS.USERID = IT.SILAGEUSERID)
        left join ORDERLOADITEMS on (IT.ORDERLOADITEMID = ORDERLOADITEMS.ORDERLOADITEMID)
        left join ROUTECHEMA on (ROUTECHEMA.ROUTECHEMAID = IT.ROUTEID)
        left join PLATFORMS on (PLATFORMS.PLATFORMID = ROUTECHEMA.PLATFORMID)
        left join SESSIONS on (SESSIONS.SESSIONID = ITTRANSPORT.WEIGHTSESSION)
        left join QUALITY_SELECT(IT.BUHQUALITYID) BUHQUALITY on (0=0)
      where (IT.REGDATE between :SAMPLERINBEGINDATE and :SAMPLERINENDDATE)
        and (IT.PARENTID <= 0) and (ITTRANSPORT.TRANSPORTTYPEID = 2)
        and (IT.OPERATIONID = :AOPERATIONID) and (STORAGE.STORAGEID = :ASTORAGEID)
        and (((IT.NET <> 0) or (:SHOWNOTWEIGHTED = 1)) and ((IT.STATE >= 0) or (:SHOWDELETED = 1)))
    order by IT.REGDATE, ITTRANSPORT.SAMPLERINDATE, STATIONS.STATIONNAME, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME
    into :INVENTORYID, :REGDATE, :ORDERLOADID, :ORDERLOADITEMID, :SAMPLERINDATE,
         :TRANSPORTNUMBER, :INVOICEID, :STORAGEID, :STORAGENAME, :SILAGEID,
         :ROUTEID, :PLATFORMID, :SILAGENAME, :GROSSTIME, :TARETIME, :GROSS,
         :TARE, :NET, :INVOICEGROSS, :INVOICETARE, :INVOICENET, :WEIGHTNUMBER,
         :NETDEFICITE, :SAMPLERQUALITYID, :TRANSPORTTYPEID, :STATIONNAME,
         :OPERATIONID, :CROPFULLNAME, :INVOICENUMBER, :OWNERID, :OWNERNAME,
         :PROVIDERID, :PROVIDERNAME, :SILAGEUSERNAME, :STATE, :WEIGHTSESSION,
         :SESSIONNAME, :HUMIDITY, :DIRT
  do begin
    TRANSPORTNUMBERX = '%';

/*
    if (STATE = -1) then
      TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 1);
    else
      if (STATE = -2) then
        TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 2);
      else
        if (STATE = -3) then
          TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 3);
        else
          if (STATE = -104) then
            TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 4);
          else
            if (NET > 0) then
              TRANSPORTNUMBERX = TRANSPORTNUMBERX || (TRANSPORTTYPEID * 12);
            else TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 5);
*/
    if (STATE < 0) then
      TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + mod(abs(STATE),100));
    else
      if (NET > 0) then
        TRANSPORTNUMBERX = TRANSPORTNUMBERX || (TRANSPORTTYPEID * 12);
      else TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 5);

    TRANSPORTNUMBERX = TRANSPORTNUMBERX || '% ' || coalesce(TRANSPORTNUMBER, '');

    suspend;
  end
end