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

LABOUT_JOURNAL_WORK

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

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

Описание

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

Определение

CREATE PROCEDURE LABOUT_JOURNAL_WORK(
    ADATEBEGIN DATE,
    ADATEEND DATE,
    STORAGEID INTEGER)
RETURNS (
    REGDATE DATE,
    INVENTORYID INTEGER,
    SUPLIERNAME VARCHAR(30),
    LABQUALITYID INTEGER,
    CROPCLASS VARCHAR(40),
    TRANSPORTNUMBER VARCHAR(55),
    NET INTEGER,
    USERNAME VARCHAR(20),
    CLASSID INTEGER,
    SAMPLERQUALITYID INTEGER,
    STORINGID INTEGER,
    RELATIONID INTEGER,
    NUMBER INTEGER,
    WORKINGDATE DATE,
    HUMIDITY NUMERIC(9,2),
    ASH NUMERIC(9,2),
    WHITES NUMERIC(9,2),
    GLUTEN NUMERIC(9,1),
    GLUTENQUALITY INTEGER,
    V_VOLUME INTEGER,
    H_D NUMERIC(9,2),
    DIRT NUMERIC(9,2),
    PASS NUMERIC(9,2),
    REST NUMERIC(9,2))
AS
DECLARE VARIABLE CROPTYPEID INTEGER;
DECLARE VARIABLE OUTVALUE NUMERIC(7,2);
begin
/*
  for
    SELECT IT.REGDATE, IT.InventoryID, clients.clientname, IT.LabQualityID, transporttype.transporttypename || ' ' || ittransport.TRANSPORTNUMBER,
      IT.NET, IT.storageid, it.samplerqualityid, it.storingid, it.relationid, ittransport.transporttypeid, ittransport.sampleroutdate
    FROM IT
      LEFT JOIN clients ON (clients.clientid = it.ProviderID)
      left join ittransport on it.inventoryid = ittransport.inventoryid
      LEFT JOIN transporttype ON (transporttype.transporttypeid = ittransport.transporttypeid)
    where ittransport.sampleroutdate between :ADateBegin and :ADateEnd
      and it.operationid = 5 and it.parentid <= 0 and it.state > -1
      and it.storageid = :storageid
    order by ittransport.sampleroutdate, IT.InventoryID
    into :regdate, :inventoryid, :supliername, :labqualityid, :transportnumber,
      :net, :storageid, :samplerqualityid, :storingid, :relationid, :transporttypeid, :workingdate
  do begin
    classid = null;
    if (:labqualityid is not null) then begin
      select QualityValue from Outqualities
      where OutQualityID = :labqualityid and qualitytypeid = 32
      into :classid;
    end
    else begin
      if (:transporttypeid = 2) then
        select classid from quality where QualityID = :samplerqualityid
        into :classid;
      else
        select QualityValue from Outqualities
        where OutQualityID = :samplerqualityid and qualitytypeid = 32
        into :classid;
    end

    select crop.croptypeid from class left join crop on (crop.cropid = class.cropid) where class.classid = :classid
    into :croptypeid;

    if (:classid is null or (:croptypeid = 2 and :classid <> 49))
    then begin
      cropclass = null;
      username = null;
      number = null;

      select CROP.shortcropname || ' ' || CLASS.shortclassname from class inner join crop on (crop.cropid = class.cropid)
      where class.classid = :classid
      into :cropclass;

      humidity = null;
      ash = null;
      whites = null;
      gluten = null;
      glutenquality = null;
      for
        select qualitytypeid, qualityvalue from Outqualities
        where OutQualityID = :labqualityid
        into :croptypeid, :outvalue
      do begin
        if (:croptypeid = 2) then humidity = cast(:outvalue as numeric(4,2));
        if (:croptypeid = 11) then ash = cast(:outvalue as numeric(4,2));
        if (:croptypeid = 43) then whites = cast(:outvalue as numeric(4,2));
        if (:croptypeid = 3) then gluten = cast(:outvalue as numeric(4,1));
        if (:croptypeid = 4) then glutenquality = cast(:outvalue as integer);
        if (:croptypeid = 33) then select username from users where userid = cast(:outvalue as integer) into :username;
        if (:croptypeid = 55) then number = cast(:outvalue as integer);
        if (:croptypeid = 22) then v_volume = cast(:outvalue as integer);
        if (:croptypeid = 23) then h_d = cast(:outvalue as numeric(4,2));
        if (:croptypeid = 13) then dirt = cast(:outvalue as numeric(4,2));
      end
      suspend;
    end
  end
*/
END