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

SAMPLER_JOURNAL

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

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

Описание

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

Определение

CREATE PROCEDURE SAMPLER_JOURNAL(
    BEGDATE DATE,
    ENDDATE DATE,
    OPERATIONID INTEGER,
    DELETEDRECORDSSHOW INTEGER,
    REMOTERECORDSSHOW INTEGER)
RETURNS (
    INVENTORYID INTEGER,
    REGDATE DATE,
    TRANSPORTTYPEID INTEGER,
    BARCODE VARCHAR(30),
    SAMPLERQUALITYID INTEGER,
    STATE INTEGER,
    INVOICEID INTEGER,
    CROPCLASSNAME VARCHAR(70),
    TRANSPORTNUMBER VARCHAR(31),
    USERNAME VARCHAR(31),
    LABQUALITYID INTEGER,
    INVOICENUMBER VARCHAR(31),
    STORAGENAME VARCHAR(100),
    INVOICENET INTEGER,
    SILAGEN VARCHAR(255),
    CLIENTFULLNAME VARCHAR(100),
    CLIENTID INTEGER,
    NET INTEGER,
    INFECTIONNAME VARCHAR(255),
    HARVESTYEAR INTEGER,
    STATEHUMIDITY VARCHAR(255),
    STATEDIRT VARCHAR(255),
    PLATFORMSHORTNAME VARCHAR(10),
    PLATFORMID INTEGER,
    CHECKPOINTOUTTIME TIMESTAMP,
    UWORDERID INTEGER,
    REGIONNAME VARCHAR(255),
    CHECKPOINTINTIME TIMESTAMP,
    ROUTE VARCHAR(255),
    ACIDITY NUMERIC(4,2),
    DIRT NUMERIC(4,2),
    DROPNUMBER INTEGER,
    GLUTEN NUMERIC(4,2),
    GLUTENQUALITY INTEGER,
    GRAINUNIT INTEGER,
    HUMIDITY NUMERIC(4,2),
    QUALITYNUMBER INTEGER,
    SCREENING NUMERIC(4,2),
    INFRALUMGLUTEN INTEGER,
    SMALLGRAIN NUMERIC(4,2),
    GLASSNESS NUMERIC(4,2),
    GL VARCHAR(30),
    CROPID INTEGER,
    CLASSID INTEGER,
    SESSION INTEGER,
    LASTRETURNDATE DATE,
    PROTEIN NUMERIC(9,2),
    WEIGHTNUMBER INTEGER,
    OILSEED NUMERIC(9,2),
    SMELL VARCHAR(255),
    TURTLEBUG NUMERIC(9,2),
    DIRTY NUMERIC(9,2),
    BRAND NUMERIC(9,2),
    WHEAT NUMERIC(9,2),
    BARLEY NUMERIC(9,2),
    RYE NUMERIC(9,2),
    SUNFLOWER NUMERIC(9,2),
    OATS NUMERIC(9,2),
    TRANSPORTSTATE VARCHAR(255),
    SAMPLERINTIME TIMESTAMP,
    SAMPLEROUTTIME TIMESTAMP,
    PREANALYSISTIME TIMESTAMP,
    OWNERNAME VARCHAR(100))
AS
declare variable SILAGENAME varchar(30);
declare variable PREV_RETURN varchar(8192);
declare variable RETURNNUMBER varchar(35);
declare variable STARTIME time;
declare variable MOVEENDTIME time;
declare variable USEBARCODE integer;
begin
  PREV_RETURN = '';

  for
    select ';' || extract(day from IT.REGDATE) || ITTRANSPORT.TRANSPORTNUMBER
      from IT
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
      where (IT.REGDATE between (:BEGDATE - 10) and (:ENDDATE - 1))
        and (IT.OPERATIONID = :OPERATIONID)
        and (IT.STATE = -3) and (IT.PARENTID <= 0)
        and (ITTRANSPORT.TRANSPORTTYPEID < 2)
        and (ITTRANSPORT.TRANSPORTNUMBER is not null)
    into :RETURNNUMBER
  do
    PREV_RETURN = :PREV_RETURN || :RETURNNUMBER;

  PREV_RETURN = :PREV_RETURN || ';';

  select min(SESSIONS.STARTIME), max(SESSIONS.MOVEENDTIME),
      min(bin_and(ENVIRONMENTOPTIONS.BARCODE, 32))
    from SESSIONS
      left join ENVIRONMENTOPTIONS on (0=0)
  into :STARTIME, :MOVEENDTIME, :USEBARCODE;

  for
    select IT.INVENTORYID, ITTRANSPORT.TRANSPORTTYPEID, ITTRANSPORT.BARCODE,
        IT.SAMPLERQUALITYID, IT.STATE,  IT.INVOICEID, QUALITY_CROPFULLNAME.CROPFULLNAME,
        CLIENTS.CLIENTNAME, CLIENTS.CLIENTID, OWNER.CLIENTNAME, ITTRANSPORT.TRANSPORTNUMBER, SILAGE.NAME,
        USERS.USERLASTNAME, IT.LABQUALITYID, INVOICES.INVOICENUMBER, STORAGE.STORAGENAME,
        ITTRANSPORT.INVOICENET, QUALITY_SELECT_VALUESI.SELECTVALUE, QUALITY_SELECT.HARVESTYEAR,
        QUALITY_SELECT_VALUESH.SELECTVALUE, QUALITY_SELECT_VALUESD.SELECTVALUE,
        PLATFORMS.PLATFORMSHORTNAME, PLATFORMS.PLATFORMID, ITTRANSPORT.CHECKPOINTOUTTIME,
        IT.UWORDERID, IT.REGDATE, REGIONS.REGIONNAME, ITTRANSPORT.CHECKPOINTINTIME, IT.NET,
        QUALITY.CROPID, QUALITY.CLASSID, ITTRANSPORT.SAMPLERSESSION, ITTRANSPORT.PREANALYSISTIME,
        cast(ITTRANSPORT.SAMPLERINDATE + ITTRANSPORT.SAMPLERINTIME as timestamp),
        cast(ITTRANSPORT.SAMPLEROUTDATE + ITTRANSPORT.SAMPLEROUTTIME as timestamp),
        case
          when (:PREV_RETURN like ('%;' || extract(day from (IT.REGDATE)) || ITTRANSPORT.TRANSPORTNUMBER || ';%')) then
            IT.REGDATE
          when (:PREV_RETURN like ('%;' || extract(day from (IT.REGDATE - 1)) || ITTRANSPORT.TRANSPORTNUMBER || ';%')) then
            IT.REGDATE - 1
          when (:PREV_RETURN like ('%;' || extract(day from (IT.REGDATE - 2)) || ITTRANSPORT.TRANSPORTNUMBER || ';%')) then
            IT.REGDATE - 2
          else null
        end,
        QUALITY_SELECT.ACROSPIRE, QUALITY_SELECT.DIRT, QUALITY_SELECT.DROPNUMBER,
        QUALITY_SELECT.GLUTEN, QUALITY_SELECT.GLUTENQUALITY, QUALITY_SELECT.GRAINUNIT,
        QUALITY_SELECT.HUMIDITY, QUALITY_SELECT.QUALITYNUMBER, QUALITY_SELECT.SCREENING,
        QUALITY_SELECT_VALUESSM.SELECTVALUE SMELL, QUALITY_SELECT.GLASSNESS,
        QUALITY_SELECT.INFRALUMGLUTEN, QUALITY_SELECT.SMALLGRAIN,
        QUALITY_SELECT.PROTEIN, IT.WEIGHTNUMBER, QUALITY_SELECT.OILSEED,
        QUALITY_SELECT.TURTLEBUG, QUALITY_SELECT.DIRTY, QUALITY_SELECT.BRAND,
        QUALITY_SELECT.WHEAT, QUALITY_SELECT.BARLEY, QUALITY_SELECT.RYE,
        QUALITY_SELECT.SUNFLOWER, QUALITY_SELECT.OATS,
        QUALITY_SELECT_VALUESTST.SELECTVALUE TRANSPORTSTATE
      from IT
        left join QUALITY on (QUALITY.QUALITYID = IT.SAMPLERQUALITYID)
        left join CROP on (CROP.CROPID = QUALITY.CROPID)
        left join CLASS on (CLASS.CLASSID = QUALITY.CLASSID)
        left join CROPSORT on (CROPSORT.SORTID = QUALITY.SORTID)
        left join QUALITY_CROPFULLNAME(QUALITY.CROPID, QUALITY.CLASSID, QUALITY.SORTID, 1) on (0=0)
        left join INVOICES on (INVOICES.INVOICEID = IT.INVOICEID)
        left join CLIENTS on (CLIENTS.CLIENTID = IT.PROVIDERID)
        left join CLIENTS OWNER on (OWNER.CLIENTID = IT.OWNERID)
        left join STORAGE on (IT.STORAGEID = STORAGE.STORAGEID)
        left join ITTRANSPORT on (ITTRANSPORT.INVENTORYID = IT.INVENTORYID)
        left join TRANSPORTTYPE on (TRANSPORTTYPE.TRANSPORTTYPEID = ITTRANSPORT.TRANSPORTTYPEID)
        left join OPERATION on (OPERATION.OPERATIONID = IT.OPERATIONID)
        left join SILAGE on (SILAGE.SILAGEID = IT.SILAGEID)
        left join QUALITY_SELECT(IT.SAMPLERQUALITYID) on (0=0)
        left join QUALITY_SELECT_VALUES(19, QUALITY_SELECT.INFECTIONID, 0) QUALITY_SELECT_VALUESI on (0=0)
        left join QUALITY_SELECT_VALUES(27, QUALITY_SELECT.SMELLID, 0) QUALITY_SELECT_VALUESSM on (0=0)
        left join QUALITY_SELECT_VALUES(95, QUALITY_SELECT.TRANSPORTSTATEID, 0) QUALITY_SELECT_VALUESTST on (0=0)
        left join QUALITY_SELECT_VALUES(102, QUALITY_SELECT.STATEHUMIDITY, 0) QUALITY_SELECT_VALUESH on (0=0)
        left join QUALITY_SELECT_VALUES(103, QUALITY_SELECT.STATEDIRT, 0) QUALITY_SELECT_VALUESD on (0=0)
        left join REGIONS on (REGIONS.REGIONID = QUALITY_SELECT.REGIONID)
        left join USERS on (USERS.USERID = QUALITY_SELECT.USERID)
        left join ROUTECHEMA on (ROUTECHEMA.ROUTECHEMAID = IT.ROUTEID)
        left join PLATFORMS on (PLATFORMS.PLATFORMID = ROUTECHEMA.PLATFORMID)
      where ((IT.REGDATE between :BEGDATE and :ENDDATE)
             or (((IT.REGDATE between (:BEGDATE - 10) and :ENDDATE)
                  and ((ITTRANSPORT.SAMPLERINDATE is null)
                       or (ITTRANSPORT.SAMPLERINDATE + ITTRANSPORT.SAMPLERINTIME between :BEGDATE + :STARTIME and :ENDDATE + 1 + :MOVEENDTIME))
                  and (IT.STATE in (-5,0)))
                 and (:USEBARCODE = 32)))
        and ((ITTRANSPORT.TRANSPORTTYPEID < 2) and (IT.OPERATIONID = :OPERATIONID))
--        and ((:DELETEDRECORDSSHOW = 1) or ((:DELETEDRECORDSSHOW = 0) and (IT.STATE <> -1)))
--        and ((:REMOTERECORDSSHOW = 1) or ((:REMOTERECORDSSHOW = 0) and (IT.STATE <> -3)))
--        and (IT.PARENTID <= 0)
      order by IT.REGDATE, IT.INVOICEID, IT.INVENTORYID
    into :INVENTORYID, :TRANSPORTTYPEID, :BARCODE, :SAMPLERQUALITYID, :STATE,
         :INVOICEID, :CROPCLASSNAME, :CLIENTFULLNAME, :CLIENTID, :OWNERNAME, :TRANSPORTNUMBER,
         :SILAGENAME, :USERNAME, :LABQUALITYID, :INVOICENUMBER, :STORAGENAME,
         :INVOICENET, :INFECTIONNAME, :HARVESTYEAR, :STATEHUMIDITY, :STATEDIRT,
         :PLATFORMSHORTNAME, :PLATFORMID, :CHECKPOINTOUTTIME, :UWORDERID, :REGDATE,
         :REGIONNAME, :CHECKPOINTINTIME, :NET,
         :CROPID, :CLASSID, :SESSION, :PREANALYSISTIME, :SAMPLERINTIME, :SAMPLEROUTTIME, :LASTRETURNDATE,
         :ACIDITY, :DIRT, :DROPNUMBER, :GLUTEN,
         :GLUTENQUALITY, :GRAINUNIT, :HUMIDITY, :QUALITYNUMBER, :SCREENING, :SMELL,
         :GLASSNESS, :INFRALUMGLUTEN, :SMALLGRAIN,
         :PROTEIN, :WEIGHTNUMBER, :OILSEED, :TURTLEBUG, :DIRTY,
         :BRAND, :WHEAT, :BARLEY, :RYE, :SUNFLOWER, :OATS, :TRANSPORTSTATE
  do begin
    ROUTE = coalesce(:PLATFORMSHORTNAME,'') || '-' || coalesce(:SILAGENAME,'');
    SILAGEN = :SILAGENAME;
    GL = coalesce(:GLUTEN,'') || '-' || coalesce(:GLUTENQUALITY,'');

    if (:OPERATIONID = 5) then
      select SAMPLER_SILAGENAME.SILAGESTRING
        from SAMPLER_SILAGENAME(:INVENTORYID)
      into :SILAGEN;
    suspend;

    if ((:STATE = -3) and (:TRANSPORTNUMBER is not null)) then
      PREV_RETURN = :PREV_RETURN || extract(day from :REGDATE) || :TRANSPORTNUMBER || ';';
  end
end