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

CHECKPOINT_JOURNAL_ADV

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

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

Описание

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

Определение

CREATE PROCEDURE CHECKPOINT_JOURNAL_ADV(
    BEGDATE DATE,
    ENDDATE DATE,
    OPERATIONID INTEGER,
    DELETEDRECORDSSHOW INTEGER,
    REMOTERECORDSSHOW INTEGER)
RETURNS (
    REGDATE DATE,
    INVOICEID INTEGER,
    INVOICENUMBER VARCHAR(31),
    CLASSID INTEGER,
    CROPID INTEGER,
    SORTID INTEGER,
    CROPCLASSNAME VARCHAR(150),
    TRAILERCROPCLASSNAME VARCHAR(150),
    CLIENTID INTEGER,
    CLIENTFULLNAME VARCHAR(150),
    NET INTEGER,
    CHECKPOINTINTIME TIMESTAMP,
    TRAILERCHECKPOINTINTIME TIMESTAMP,
    CHECKPOINTOUTTIME TIMESTAMP,
    TRAILERCHECKPOINTOUTTIME TIMESTAMP,
    TRANSPORTTYPEID INTEGER,
    TRAILERTRANSPORTTYPEID INTEGER,
    TRANSPORTNUMBER VARCHAR(30),
    TRAILERNUMBER VARCHAR(30),
    DRIVERS VARCHAR(30),
    BARCODE VARCHAR(30),
    TRAILERBARCODE VARCHAR(30),
    INVENTORYID INTEGER,
    TRAILERINVENTORYID INTEGER,
    STATE INTEGER,
    TRAILERSTATE INTEGER,
    LABQUALITYID INTEGER,
    TRAILERLABQUALITYID INTEGER,
    SESSION INTEGER,
    TRAILERCHECKPOINTSESSION INTEGER,
    SAMPLERQUALITYID INTEGER,
    TRAILERSAMPLERQUALITYID INTEGER,
    WEIGHTNUMBER INTEGER,
    TRAILERWEIGHTNUMBER INTEGER,
    STORAGEID INTEGER,
    STORAGENAME VARCHAR(100),
    OWNERID INTEGER,
    SENDERID INTEGER,
    CONTRACTID INTEGER,
    ORDERLOADITEMID INTEGER,
    ORDERLOADID INTEGER,
    INVOICENET INTEGER)
AS
declare variable AINVOICEID integer;
declare variable CHECKPOINT_INTIME timestamp;
begin
  for
    select max(CHECKPOINT_INTIME) CHECKPOINT_INTIME, REGDATE, INVOICEID, INVOICENUMBER, DRIVERS,
        max(CARCROPID) CROPID, max(CARCLASSID) CLASSID, max(CARSORTID) SORTID,
        CLIENTNAME, CLIENTID,
        STORAGEID, STORAGENAME, sum(NET) NET,
        max(CARCHECKPOINTINTIME) CARCHECKPOINTINTIME, max(TRAILERCHECKPOINTINTIME) TRAILERCHECKPOINTINTIME,
        max(CARCHECKPOINTOUTTIME), max(TRAILERCHECKPOINTOUTTIME),
        max(CARINVENTORYID) INVENTORYID, max(TRAILERINVENTORYID),
        max(CARTRANSPORTTYPEID) TRANSPORTTYPEID, max(TRAILERTRANSPORTTYPEID),
        max(CARNUMBER) TRANSPORTNUMBER, max(TRAILERNUMBER),
        max(CARBARCODE) BARCODE, max(TRAILERBARCODE),
        max(CARSTATE) STATE,
--        max(TRAILERSTATE),
        (select IT.STATE from IT where (IT.INVENTORYID = max(TRAILERINVENTORYID))),
        max(CARCROPFULLNAME) CROPFULLNAME, max(TRAILERCROPFULLNAME),
        max(CARLABQUALITYID) LABQUALITYID, max(TRAILERLABQUALITYID),
        max(CARCHECKPOINTSESSION) CHECKPOINTSESSION, max(TRAILERCHECKPOINTSESSION),
        max(CARSAMPLERQUALITYID) SAMPLERQUALITYID, max(TRAILERSAMPLERQUALITYID),
        max(CARWEIGHTNUMBER) WEIGHTNUMBER, max(TRAILERWEIGHTNUMBER),
        max(OWNERID), max(SENDERID),
        max(CONTRACTID), max(ORDERLOADITEMID), max(ORDERLOADID), sum(INVOICENET)
      from (
        select coalesce(ITTRANSPORT.CHECKPOINTINTIME,current_timestamp) CHECKPOINT_INTIME,
            IT.REGDATE, IT.INVOICEID, INVOICES.INVOICENUMBER, INVOICES.DRIVERS,
            QUALITY.CROPID CARCROPID, QUALITY.CLASSID CARCLASSID, QUALITY.SORTID CARSORTID,
            CLIENTS.CLIENTNAME, CLIENTS.CLIENTID,
            IT.STORAGEID, STORAGE.STORAGESHORTNAME STORAGENAME, coalesce(IT.NET,0) NET,
            ITTRANSPORT.CHECKPOINTINTIME CARCHECKPOINTINTIME, null TRAILERCHECKPOINTINTIME,
            ITTRANSPORT.CHECKPOINTOUTTIME CARCHECKPOINTOUTTIME, null TRAILERCHECKPOINTOUTTIME,
            IT.INVENTORYID CARINVENTORYID, null TRAILERINVENTORYID,
            ITTRANSPORT.TRANSPORTTYPEID CARTRANSPORTTYPEID, null TRAILERTRANSPORTTYPEID,
            ITTRANSPORT.TRANSPORTNUMBER CARNUMBER, null TRAILERNUMBER,
            ITTRANSPORT.BARCODE CARBARCODE, null TRAILERBARCODE,
            IT.STATE CARSTATE, null TRAILERSTATE,
            QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME CARCROPFULLNAME, null TRAILERCROPFULLNAME,
            IT.LABQUALITYID CARLABQUALITYID, null TRAILERLABQUALITYID,
            ITTRANSPORT.CHECKPOINTSESSION CARCHECKPOINTSESSION, null TRAILERCHECKPOINTSESSION,
            IT.SAMPLERQUALITYID CARSAMPLERQUALITYID, null TRAILERSAMPLERQUALITYID,
            IT.WEIGHTNUMBER CARWEIGHTNUMBER, null TRAILERWEIGHTNUMBER,
            IT.OWNERID, INVOICES.SENDERID,
            IT.CONTRACTID, IT.ORDERLOADITEMID,
            ORDERLOADITEMS.ORDERLOADID, ITTRANSPORT.INVOICENET
          from IT
            left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
            left join QUALITY_SELECT(IT.SAMPLERQUALITYID) on (0=0)
            left join QUALITY_CROPFULLNAME_FORAGE(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
            left join CROPSORT on (CROPSORT.SORTID = QUALITY.SORTID)
            left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
            left join CLIENTS on (CLIENTS.CLIENTID = IT.PROVIDERID)
            left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
            left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
            left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = IT.ORDERLOADITEMID)
          where (((((IT.REGDATE between :BEGDATE and :ENDDATE)
                    and ((coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is not null)
                         or (IT.BUHQUALITYID is not null)
                        )
                    )
                    or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE)
                        and ((coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is null)
                             and (IT.BUHQUALITYID is null)
                            )
                       )
                  )
                  and (IT.STATE >= 0))
                 or ((IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.STATE = -1) and (:DELETEDRECORDSSHOW = 1))
                 or ((IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.STATE = -3) and (:REMOTERECORDSSHOW = 1))
                )
            and (IT.OPERATIONID = :OPERATIONID) and (ITTRANSPORT.TRANSPORTTYPEID = 0) and (IT.PARENTID <= 0)
        union
          select null CHECKPOINT_INTIME, IT.REGDATE, IT.INVOICEID, INVOICES.INVOICENUMBER, INVOICES.DRIVERS,
              QUALITY.CROPID CARCROPID, QUALITY.CLASSID CARCLASSID, QUALITY.SORTID CARSORTID,
              CLIENTS.CLIENTNAME, CLIENTS.CLIENTID,
              IT.STORAGEID, STORAGE.STORAGESHORTNAME STORAGENAME, coalesce(IT.NET,0) NET,
              null CARCHECKPOINTINTIME, ITTRANSPORT.CHECKPOINTINTIME TRAILERCHECKPOINTINTIME,
              null CARCHECKPOINTOUTTIME, ITTRANSPORT.CHECKPOINTOUTTIME TRAILERCHECKPOINTOUTTIME,
              null CARINVENTORYID, IT.INVENTORYID TRAILERINVENTORYID,
              null CARTRANSPORTTYPEID, ITTRANSPORT.TRANSPORTTYPEID TRAILERTRANSPORTTYPEID,
              null CARNUMBER, ITTRANSPORT.TRANSPORTNUMBER TRAILERNUMBER,
              null CARBARCODE, ITTRANSPORT.BARCODE TRAILERBARCODE,
              null CARSTATE, IT.STATE TRAILERSTATE,
              null CARCROPFULLNAME, QUALITY_CROPFULLNAME_FORAGE.CROPFULLNAME TRAILERCROPFULLNAME,
              null CARLABQUALITYID, IT.LABQUALITYID TRAILERLABQUALITYID,
              null CARCHECKPOINTSESSION, ITTRANSPORT.CHECKPOINTSESSION TRAILERCHECKPOINTSESSION,
              null CARSAMPLERQUALITYID, IT.SAMPLERQUALITYID TRAILERSAMPLERQUALITYID,
              null CARWEIGHTNUMBER, IT.WEIGHTNUMBER TRAILERWEIGHTNUMBER,
              IT.OWNERID, INVOICES.SENDERID,
              IT.CONTRACTID, IT.ORDERLOADITEMID,
              ORDERLOADITEMS.ORDERLOADID, ITTRANSPORT.INVOICENET
            from IT
              left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
              left join QUALITY_SELECT(IT.SAMPLERQUALITYID) on (0=0)
              left join QUALITY_CROPFULLNAME_FORAGE(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, QUALITY_SELECT.FORAGECROPID, QUALITY_SELECT.FORAGECLASSID, 1) on (0=0)
              left join CROPSORT on (CROPSORT.SORTID = QUALITY.SORTID)
              left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
              left join CLIENTS on (CLIENTS.CLIENTID = IT.PROVIDERID)
              left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
              left join STORAGE on (STORAGE.STORAGEID = IT.STORAGEID)
              left join ORDERLOADITEMS on (ORDERLOADITEMS.ORDERLOADITEMID = IT.ORDERLOADITEMID)
            where (((((IT.REGDATE between :BEGDATE and :ENDDATE)
                      and ((coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is not null)
                           or (IT.BUHQUALITYID is not null)
                          )
                     )
                     or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE)
                         and ((coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is null)
                              and (IT.BUHQUALITYID is null)
                             )
                        )
                    )
                    and (IT.STATE >= 0)
                   )
                   or ((((IT.REGDATE between :BEGDATE and :ENDDATE)
                         and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is not null)
                        )
                        or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE)
                            and ((coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is null)
                                 and (IT.BUHQUALITYID is null)
                                )
                           )
                       )
                       and (IT.STATE = -5)
                      )
                   or ((IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.STATE = -1) and (:DELETEDRECORDSSHOW = 1))
                   or ((IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.STATE = -3) and (:REMOTERECORDSSHOW = 1))
                  )
              and (IT.OPERATIONID = :OPERATIONID) and (ITTRANSPORT.TRANSPORTTYPEID = 1) and (IT.PARENTID <= 0)
              and ((select IT2.INVOICEID
                      from IT IT2
                        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT2.INVENTORYID)
                      where (((((IT2.REGDATE between :BEGDATE and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT2.TARETIME) is not null))
                               or ((IT2.REGDATE between :BEGDATE - 10 and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT2.TARETIME) is null)))
                              and (IT2.STATE >= 0))
                             or ((IT2.REGDATE between :BEGDATE and :ENDDATE) and (IT2.STATE = -1) and (:DELETEDRECORDSSHOW = 1))
                             or ((IT2.REGDATE between :BEGDATE and :ENDDATE) and (IT2.STATE = -3) and (:REMOTERECORDSSHOW = 1)))
                        and (IT2.INVOICEID = IT.INVOICEID) and (ITTRANSPORT.TRANSPORTTYPEID = 0)
                   ) is not null
                  )
          order by 2, 3
      )
    group by REGDATE, STORAGEID, STORAGENAME, INVOICEID, INVOICENUMBER, DRIVERS, CLIENTNAME, CLIENTID
    order by CHECKPOINT_INTIME, CARCHECKPOINTINTIME

    into :CHECKPOINT_INTIME, :REGDATE, :INVOICEID, :INVOICENUMBER, :DRIVERS,
        :CROPID, :CLASSID, :SORTID,
        :CLIENTFULLNAME, :CLIENTID,
        :STORAGEID, :STORAGENAME, :NET,
        :CHECKPOINTINTIME, :TRAILERCHECKPOINTINTIME,
        :CHECKPOINTOUTTIME, :TRAILERCHECKPOINTOUTTIME,
        :INVENTORYID, :TRAILERINVENTORYID,
        :TRANSPORTTYPEID, :TRAILERTRANSPORTTYPEID,
        :TRANSPORTNUMBER, :TRAILERNUMBER,
        :BARCODE, :TRAILERBARCODE,
        :STATE, :TRAILERSTATE,
        :CROPCLASSNAME, TRAILERCROPCLASSNAME,
        :LABQUALITYID, :TRAILERLABQUALITYID,
        :SESSION, :TRAILERCHECKPOINTSESSION,
        :SAMPLERQUALITYID, :TRAILERSAMPLERQUALITYID,
        :WEIGHTNUMBER, :TRAILERWEIGHTNUMBER,
        :OWNERID, :SENDERID,
        :CONTRACTID, :ORDERLOADITEMID, :ORDERLOADID, :INVOICENET

  do suspend;
/*
  for
    select IT.INVOICEID, INVOICES.INVOICENUMBER, INVOICES.DRIVERS,
        QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID,
        CLIENTS.CLIENTNAME, CLIENTS.CLIENTID, IT.NET,
        ITTRANSPORT.CHECKPOINTINTIME, ITTRANSPORT.CHECKPOINTOUTTIME,
        IT.INVENTORYID, ITTRANSPORT.TRANSPORTTYPEID,
        ITTRANSPORT.TRANSPORTNUMBER, ITTRANSPORT.BARCODE, IT.STATE,
        QUALITY_CROPFULLNAME.CROPFULLNAME, IT.LABQUALITYID, IT.REGDATE,
        ITTRANSPORT.CHECKPOINTSESSION, IT.SAMPLERQUALITYID, IT.WEIGHTNUMBER
      from IT
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0 = 0)
        left join CROPSORT on (CROPSORT.SORTID = QUALITY.SORTID)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join CLIENTS on (CLIENTS.CLIENTID = IT.PROVIDERID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (((((IT.REGDATE between :BEGDATE and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is not null))
               or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is null)))
              and (IT.STATE >= 0))
             or ((((IT.REGDATE between :BEGDATE and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is not null))
               or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is null)))
              and (IT.STATE = -5))
             or ((IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.STATE = -1) and (:DELETEDRECORDSSHOW = 1))
             or ((IT.REGDATE between :BEGDATE and :ENDDATE) and (IT.STATE = -3) and (:REMOTERECORDSSHOW = 1)))
        and (IT.OPERATIONID = :OPERATIONID) and (ITTRANSPORT.TRANSPORTTYPEID < 2) and (IT.PARENTID <= 0)
    order by IT.REGDATE, IT.INVOICEID, ITTRANSPORT.TRANSPORTTYPEID, IT.INVENTORYID
    into :INVOICEID, :INVOICENUMBER, :DRIVERS, :CROPID, :CLASSID, :SORTID,
        :CLIENTFULLNAME, :CLIENTID, :NET, :CHECKPOINTINTIME, :CHECKPOINTOUTTIME,
        :INVENTORYID, :TRANSPORTTYPEID, :TRANSPORTNUMBER, :BARCODE, :STATE,
        :CROPCLASSNAME, :LABQUALITYID, :REGDATE, :SESSION, :SAMPLERQUALITYID,
        :WEIGHTNUMBER
  do
    if (STATE = -5) then begin
      select IT.INVOICEID
        from IT
          left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        where ((((IT.REGDATE between :BEGDATE and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is not null))
                or ((IT.REGDATE between :BEGDATE - 10 and :ENDDATE) and (coalesce(ITTRANSPORT.CHECKPOINTOUTTIME,IT.TARETIME) is null)))
               and (IT.STATE >= 0))
          and (IT.INVOICEID = :INVOICEID)
      into :AINVOICEID;
      if (AINVOICEID is not null) then
        suspend;
    end
    else suspend;
*/
end