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

SHIPMENT_SAVE_IN_IT

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

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

Описание

Сохранение отпуска на мельзавод

Определение

CREATE PROCEDURE SHIPMENT_SAVE_IN_IT(
    REGDATE DATE,
    NET INTEGER,
    RECIPEID INTEGER,
    ROUTEID INTEGER,
    AINVOICEID INTEGER,
    INVOICENUMBER VARCHAR(31),
    WORKINGDATE DATE)
RETURNS (
    INVOICEID INTEGER,
    RECIPEN VARCHAR(20))
AS
declare variable netclass integer;
declare variable classid integer;
declare variable sortid integer;
declare variable inventoryid integer;
declare variable storageid integer;
declare variable qualityid integer;
declare variable storagedest integer;
declare variable outweight integer;
declare variable countquality integer;
declare variable cropid integer;
declare variable squalityid integer;
declare variable netclassdouble double precision;
declare variable aclassid integer;
begin
  select recipes.recipen, clients.clientid
  from recipes
    left join clients on (recipes.storageid = clients.storageid)
  where recipeid = :recipeid
  into :recipen, :storagedest;

  if (:ainvoiceid = -1) then begin
    invoiceid = gen_id(invoiceidgen, 1);
--    insert into invoices (invoiceid, invoicenet, invoicenumber) values (:invoiceid, :net, :invoicenumber);
    insert into invoices (invoiceid, invoicenumber) values (:invoiceid, :invoicenumber);
  end
  else begin
    invoiceid = :ainvoiceid;
--    update invoices set invoicenet = invoicenet + -+:net where invoiceid = :invoiceid;
  end

  select min(silagepacks.storageid), min(quality.cropid),
    quality.classid -- added
  from recipesilages
    left join silagepacks on (recipesilages.silagepackid = silagepacks.silagepackid)
    left join quality on (recipesilages.qualityid = quality.qualityid)
  where recipesilages.recipeid = :recipeid and recipesilages.state <> 2
  group by quality.classid -- ??????
  into :storageid, :cropid, :aclassid;

  qualityid = gen_id(qualityidgen, 1);
  insert into quality(qualityid, cropid) values (:qualityid, :cropid);

  select count(silagequality.silagequalityid)
  from Silagepacks
    left join silagequality on (Silagepacks.silagequalityid = silagequality.silagequalityid)
  where Silagepacks.silagepackid = :routeid
  into :countquality;

  if (:countquality = 1) then begin
    select quality.classid, quality.sortid
    from silagepacks
      left join Quality on (silagepacks.qualityid = Quality.qualityid)
    where Silagepacks.silagepackid = :routeid
    into :classid, :sortid;

    squalityid = gen_id(qualityidgen, 1);
    insert into quality(qualityid, cropid, classid, sortid) values(:squalityid, :cropid, :classid, :sortid);

    inventoryid = gen_id(inventoryidgen, 1);
    insert into IT(inventoryid, net, invoiceid, RelationID, RouteID, RegDate, StorageID, operationid, OPERATIONPARAM, SamplerQualityID, providerid)
    values(:inventoryid, :net, :invoiceid, :recipeid, :routeid, :regdate, :storageid, 7, :ClassID, :SQualityID, :storagedest);
  end
  else begin
    for
      select Quality.classid, Quality.sortid, sum(recipesilages.percentweight)
      from recipesilages
        left join Silagepacks on (Silagepacks.silagepackid = recipesilages.silagepackid)
        left join Quality on (Quality.qualityid = recipesilages.qualityid)
      where recipesilages.recipeid = :recipeid
      group by Quality.classid, Quality.sortid
      into :ClassID, :sortid, :netclassdouble
    do begin
      squalityid = gen_id(qualityidgen, 1);
      insert into quality(qualityid, cropid, classid, sortid) values(:squalityid, :cropid, :classid, :sortid);

      inventoryid = gen_id(inventoryidgen, 1);

      netclassdouble = :net * :netclassdouble / 100;
      netclass = cast(netclassdouble as integer);

      insert into IT(inventoryid, net, invoiceid, RelationID, RouteID, RegDate, StorageID, operationid, OPERATIONPARAM, SamplerQualityID, providerid)
      values(:inventoryid, :netclass, :invoiceid, :recipeid, :routeid, :regdate, :storageid, 7, :ClassID, :squalityid, :storagedest);
    end
  end
  if (:ainvoiceid = -1) then
    insert into uwoutacts (invoiceid, weight, BeforeQualityID, WorkingDate, regdate, recipeid, State) values (:invoiceid, :net, :qualityid, :WorkingDate, :regdate, :recipeid, 0);
  else
    update uwoutacts set weight = weight + :net where invoiceid = :invoiceid;

  if (:routeid is not null) then
    execute procedure silagepacks_decrease_weight :routeid, :net, :INVENTORYID, :AClassID;

/* ЗАПИСЬ В recipesilages */
  if (:countquality = 1) then begin
    select outweight from recipesilages
    where recipeid = :recipeid and silagepackid = :routeid
    into :outweight;
    if (:outweight is null) then outweight = 0;
    update recipesilages set outweight = :outweight + :net
    where recipesilages.recipeid = :recipeid
      and recipesilages.silagepackid = :inventoryid;
  end
  else begin
    for
      select silagepackid, outweight
      from recipesilages
      where recipeid = :recipeid
      into :inventoryid, :outweight
    do begin
      if (:outweight is null) then outweight = 0;
      update recipesilages
      set outweight = :outweight + cast((:net / 100 * percentweight) as integer)
      where recipesilages.recipeid = :recipeid
        and recipesilages.silagepackid = :inventoryid;
    end
  end
  suspend;
END