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

DESK_JOURNAL_AVTO

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

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

Описание

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

Определение

CREATE PROCEDURE DESK_JOURNAL_AVTO(
    ABEGINREGDATE DATE,
    AENDREGDATE DATE,
    AOPERATIONID INTEGER,
    ASTORAGEID INTEGER,
    SHOWNOTWEIGHTED INTEGER = 0,
    SHOWDELETED INTEGER = 0)
RETURNS (
    INVENTORYID INTEGER,
    REGDATE DATE,
    BARCODE VARCHAR(30),
    TRANSPORTNUMBER VARCHAR(50),
    TRANSPORTNUMBERX VARCHAR(50),
    INVOICEID INTEGER,
    ORDERLOADID INTEGER,
    ORDERLOADNUMBER VARCHAR(10),
    STORAGEID INTEGER,
    STORAGENAME VARCHAR(100),
    SILAGEID INTEGER,
    SILAGENAME VARCHAR(30),
    ROUTEID INTEGER,
    PATHNAME VARCHAR(255),
    GROSSTIME TIMESTAMP,
    TARETIME TIMESTAMP,
    GROSS INTEGER,
    TARE INTEGER,
    NET INTEGER,
    WEIGHTNUMBER INTEGER,
    NETDEFICITE BIGINT,
    SAMPLERQUALITYID INTEGER,
    TRANSPORTTYPEID INTEGER,
    OPERATIONID INTEGER,
    OPERATIONPARAM INTEGER,
    CROPID INTEGER,
    CLASSID INTEGER,
    SORTID INTEGER,
    CROPFULLNAME VARCHAR(100),
    HUMIDITY NUMERIC(4,2),
    HUMIDITYNAME VARCHAR(30),
    DIRT NUMERIC(4,2),
    DIRTNAME VARCHAR(30),
    INVOICENUMBER VARCHAR(31),
    OWNERID INTEGER,
    OWNERNAME VARCHAR(30),
    PROVIDERID INTEGER,
    PROVIDERNAME VARCHAR(30),
    SILAGEUSERNAME VARCHAR(31),
    STATE INTEGER,
    FULLNET INTEGER,
    WEIGHTSESSION INTEGER,
    SESSIONNAME VARCHAR(10),
    WEIGHTUSERID INTEGER,
    WEIGHTUSERNAME VARCHAR(36),
    INVOICENET INTEGER)
AS
declare variable TMP_TRANSPORTNUMBER varchar(100);
declare variable TMP_TRANSPORTTYPEID integer;
declare variable TMP_PATHNAME varchar(255);
begin
  for
    select ITFULL.REGDATE, ITFULL.INVOICEID, sum(ITFULL.NET)
      from ITFULL
      where (ITFULL.REGDATE between :ABEGINREGDATE and :AENDREGDATE)
        and (ITFULL.PARENTID <= 0) and (ITFULL.OPERATIONID = :AOPERATIONID) and (ITFULL.STORAGEID = :ASTORAGEID)
        and (ITFULL.TRANSPORTTYPEID in (select TRANSPORTTYPE.TRANSPORTTYPEID from TRANSPORTTYPE where (TRANSPORTTYPE.TRANSPORTSUBTYPE = 0)))
        and (((ITFULL.NET <> 0) or (:SHOWNOTWEIGHTED = 1)) and ((ITFULL.STATE in (-5,0)) or (:SHOWDELETED = 1)))
    group by REGDATE, INVOICEID
    order by REGDATE, INVOICEID
    into :REGDATE, :INVOICEID, :FULLNET
  do begin
    for
      select ITFULL.INVENTORYID, ITFULL.BARCODE, ITFULL.TRANSPORTNUMBER,
          ORDERLOAD.ORDERLOADID, ORDERLOAD.ORDERNUMBER, ITFULL.STORAGEID,
          STORAGE.STORAGENAME, ITFULL.SILAGEID, ITFULL.ROUTEID, SILAGE.NAME,
          case :AOPERATIONID
            when 1 then
              coalesce(PLATFORMS.PLATFORMSHORTNAME || ' - ','') || coalesce(SILAGE.NAME,'')
            when 5 then
              coalesce(SILAGE.NAME,(select SAMPLER_SILAGENAME.SILAGESTRING from SAMPLER_SILAGENAME(ITFULL.INVENTORYID)))
          end PATHNAME,
          ITFULL.GROSSTIME, ITFULL.TARETIME, ITFULL.GROSS, ITFULL.TARE, ITFULL.NET,
          ITFULL.WEIGHTNUMBER, (ITFULL.INVOICENET - ITFULL.NET) NETDEFICITE,
          (select sum(ITTRANSPORT.INVOICENET)
             from IT
               left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
             where (IT.INVOICEID = ITFULL.INVOICEID)
          ),
          ITFULL.SAMPLERQUALITYID, ITFULL.TRANSPORTTYPEID, ITFULL.OPERATIONID,
          ITFULL.OPERATIONPARAM, QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID,
          QUALITY_CROPFULLNAME_BYID.CROPFULLNAME,
/*
          case :AOPERATIONID
            when 1 then
              QHUMIDITYVALUE.QUALITYVALUE
            when 5 then
              QHUMIDITYVALUELAB.QUALITYVALUE
          end HUMIDITY,
*/
          BUHQUALITY.HUMIDITY HUMIDITY, SAMPLERQUALITY.STATEHUMIDITY HUMIDITYNAME,
/*
          case ITFULL.OPERATIONID
            when 1 then
              QDIRTVALUE.QUALITYVALUE
            when 5
              then QDIRTVALUELAB.QUALITYVALUE
          end DIRT,
*/
          BUHQUALITY.DIRT DIRT, SAMPLERQUALITY.STATEDIRT DIRTNAME, INVOICES.INVOICENUMBER,
          ITFULL.OWNERID, OWNERS.CLIENTNAME, ITFULL.PROVIDERID, PROVIDERS.CLIENTNAME,
          ITFULL.STATE, ITFULL.WEIGHTSESSION, SESSIONS.SESSIONNAME,
          coalesce(ITFULL.WEIGHTUSERID,(select IT.WEIGHTUSERID
                                          from IT
                                            left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
                                          where ((IT.INVOICEID = ITFULL.INVOICEID)
                                                 and (IT.STATE = 0)
                                                 and (ITTRANSPORT.TRANSPORTTYPEID = 0)
                                                )
                                       )) WEIGHTUSERID
        from ITFULL
          left join QUALITY_CROPFULLNAME_BYID(ITFULL.SAMPLERQUALITYID, 1) on (1=1)
          left join QUALITY on (QUALITY.QUALITYID = ITFULL.SAMPLERQUALITYID)
          left join TRANSPORTTYPE on ((TRANSPORTTYPE.TRANSPORTTYPEID = ITFULL.TRANSPORTTYPEID) and (TRANSPORTTYPE.TRANSPORTTYPEID =2))
          left join INVOICES on (INVOICES.INVOICEID = ITFULL.INVOICEID)
          left join SILAGE on (SILAGE.SILAGEID = ITFULL.SILAGEID)
          left join CLIENTS OWNERS on (OWNERS.CLIENTID = ITFULL.OWNERID)
          left join CLIENTS PROVIDERS on (ITFULL.PROVIDERID = PROVIDERS.CLIENTID)
          left join STORAGE on (STORAGE.STORAGEID = ITFULL.STORAGEID)
          left join ROUTECHEMA on (ROUTECHEMA.ROUTECHEMAID = ITFULL.ROUTEID)
          left join PLATFORMS on (PLATFORMS.PLATFORMID = ROUTECHEMA.PLATFORMID)
--          left join USERS SILAGEUSER on (SILAGEUSER.USERID = ITFULL.SILAGEUSERID)
--          left join USERS WEIGHTUSER on (WEIGHTUSER.USERID = ITFULL.WEIGHTUSERID)
          left join QUALITY_SELECT(ITFULL.SAMPLERQUALITYID) SAMPLERQUALITY on (0=0)
          left join QUALITY_SELECT(ITFULL.BUHQUALITYID) BUHQUALITY on (0=0)
          left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = ITFULL.ORDERLOADITEMID)
          left join ORDERLOAD on (ORDERLOAD.ORDERLOADID = ORDERLOADITEMS.ORDERLOADID)
          left join SESSIONS on (SESSIONS.SESSIONID = ITFULL.WEIGHTSESSION)
        where (ITFULL.INVOICEID = :INVOICEID)
          and (((ITFULL.NET <> 0) or (:SHOWNOTWEIGHTED = 1))
               and ((ITFULL.STATE in (-5,0)) or (:SHOWDELETED = 1)))
--        and (not coalesce (ITFULL.GROSS, ITFULL.TARE, ITFULL.NET) is not null or not )
--      order by ITFULL.REGDATE--, ITFULL.TRANSPORTTYPEID, QUALITY_CROPFULLNAME_BYID.CROPFULLNAME
      order by ITFULL.TRANSPORTTYPEID
      into :INVENTORYID, :BARCODE, :TRANSPORTNUMBER, :ORDERLOADID, :ORDERLOADNUMBER,
           :STORAGEID, :STORAGENAME, :SILAGEID, :ROUTEID, :SILAGENAME, :PATHNAME,
           :GROSSTIME, :TARETIME, :GROSS, :TARE, :NET, :WEIGHTNUMBER, :NETDEFICITE,
           :INVOICENET, :SAMPLERQUALITYID, :TRANSPORTTYPEID, :OPERATIONID, :OPERATIONPARAM,
           :CROPID, :CLASSID, :SORTID, :CROPFULLNAME, :HUMIDITY, :HUMIDITYNAME,
           :DIRT, :DIRTNAME, :INVOICENUMBER, :OWNERID, :OWNERNAME, :PROVIDERID,
           :PROVIDERNAME, :STATE, :WEIGHTSESSION, :SESSIONNAME, :WEIGHTUSERID
    do begin
      TRANSPORTNUMBERX = '%';

/*
      if (STATE = -1) then
        TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 1);
      else
        if (STATE = -3) then
          TRANSPORTNUMBERX = TRANSPORTNUMBERX || ((TRANSPORTTYPEID * 12) + 3);
        else
          if (FULLNET > 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,'');
/*
        TMP_TRANSPORTNUMBER = null;

        for
          select TRANSPORTNUMBER, TRANSPORTTYPEID from ITFULL
            where (ITFULL.INVOICEID = :INVOICEID) and (ITFULL.BARCODE is null) and (ITFULL.STATE <> -1)
          into :TMP_TRANSPORTNUMBER, :TMP_TRANSPORTTYPEID
        do
          if (:TMP_TRANSPORTNUMBER is not null) then begin
            TRANSPORTTYPEID = null;
            TRANSPORTNUMBERX = coalesce(TRANSPORTNUMBERX,'') || ' %' || TMP_TRANSPORTTYPEID || '%' || coalesce(TMP_TRANSPORTNUMBER,'');
            TRANSPORTNUMBER = coalesce(TRANSPORTNUMBER,'') || '; ' || coalesce(TMP_TRANSPORTNUMBER,'');
          end
*/
      if ((TRANSPORTTYPEID = 1) and (STATE = -5)) then begin
/*
          CROPFULLNAME = null;
          STORAGENAME = null;
          PATHNAME = null;
*/
        PATHNAME = TMP_PATHNAME;
      end
      else TMP_PATHNAME = PATHNAME;

      select coalesce(USERS.USERLASTNAME,'') ||
          coalesce(' ' || substring(USERS.USERNAME from 1 for 1) || '.','') || ' ' ||
          coalesce(' ' || substring(USERS.USERMIDDLENAME from 1 for 1) || '.','')
        from USERS
        where (USERS.USERID = :WEIGHTUSERID)
      into :WEIGHTUSERNAME;

      suspend;
    end
  end
end