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

SAMPLER_ORDER_UNLOADING

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

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

Описание

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

Определение

CREATE PROCEDURE SAMPLER_ORDER_UNLOADING(
    INVENTORYID INTEGER)
RETURNS (
    CROPNAME VARCHAR(255),
    GLUTEN NUMERIC(9,2),
    GLUTENQUALITY NUMERIC(9,2),
    HUMIDITY NUMERIC(9,2),
    DIRT NUMERIC(9,2),
    SCREENING NUMERIC(9,2),
    INFECTION VARCHAR(255),
    SILAGENAME VARCHAR(30),
    PLATFORMNAME VARCHAR(30),
    TRANSPORTNUMBER_CAR VARCHAR(31),
    TRANSPORTNUMBER_TRAILER VARCHAR(31),
    CLIENTNAME VARCHAR(30),
    NET INTEGER,
    TARE INTEGER,
    GROSS INTEGER)
AS
DECLARE VARIABLE TRANSPORTTYPEID INTEGER;
DECLARE VARIABLE INVOICEID INTEGER;
DECLARE VARIABLE ASILAGENAME VARCHAR(30);
DECLARE VARIABLE TRANSPORTNUMBER1 VARCHAR(31);
DECLARE VARIABLE TRANSPORTNUMBER2 VARCHAR(31);
DECLARE VARIABLE ANET INTEGER;
DECLARE VARIABLE ATARE INTEGER;
DECLARE VARIABLE AGROSS INTEGER;
begin
  select quality_cropfullname.cropfullname, q.gluten, q.glutenquality, q.humidity,
    q.dirt, q.screening, infection.selectvalue infection, silage.name silagename,
    platforms.platformname, ittransport.transportnumber, clients.clientname, ittransport.transporttypeid,
    IT.invoiceid, IT.net, IT.tare, IT.gross
  from IT
    left join ittransport on IT.inventoryid = ittransport.inventoryid
    left join quality_select(IT.samplerqualityid) q on 0=0
    left join quality_cropfullname(q.cropid, q.classid, q.sortid, 1) on 0=0
    left join quality_select_values(19, q.infectionid, 1) infection on 0=0
    left join silage on it.silageid = silage.silageid
    left join routechema on it.routeid = routechema.routechemaid
    left join platforms on platforms.platformid = routechema.platformid
    left join clients on it.ownerid = clients.clientid
  where IT.inventoryid = :inventoryid
  into :cropname, :gluten, :glutenquality, :humidity, :dirt, :screening, :infection,
       :silagename, :platformname, :transportnumber1, :clientname, :transporttypeid, :invoiceid,
       :net, :tare, :gross;

  select first 1 silage.name, IT.net, IT.tare, IT.gross, ittransport.transportnumber
  from it
    left join silage on it.silageid = silage.silageid
    left join ittransport on it.inventoryid = ittransport.inventoryid
  where it.invoiceid = :invoiceid and it.inventoryid <> :inventoryid
  into :asilagename, :anet, :atare, :agross, :transportnumber2;

  if (coalesce(:silagename, '') = coalesce(:asilagename, '')) then begin
    net = coalesce(:net, 0) + coalesce(:anet, 0);
    tare = coalesce(:tare, 0) + coalesce(:atare, 0);
    gross = coalesce(:gross, 0) + coalesce(:agross, 0);
    if (:transporttypeid = 0) then begin
      transportnumber_car = :transportnumber1;
      transportnumber_trailer = :transportnumber2;
    end
    else begin
      transportnumber_car = :transportnumber2;
      transportnumber_trailer = :transportnumber1;
    end
  end
  else begin
    if (:transporttypeid = 0) then transportnumber_car = :transportnumber1;
    else transportnumber_trailer = :transportnumber1;
  end
  suspend;
end