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

ADDITIONFORAKTCROP

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

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

Описание

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

Определение

CREATE PROCEDURE ADDITIONFORAKTCROP(
    DATE1 DATE,
    DATE2 DATE,
    CROPID INTEGER,
    PARTYID INTEGER,
    PARTYDATE DATE,
    CLASSID INTEGER,
    CLIENTID INTEGER,
    CONTRACTID INTEGER,
    POBPROD INTEGER,
    MELKOE INTEGER,
    DOCROP INTEGER)
RETURNS (
    REGDATE DATE,
    SUMNET INTEGER,
    DB NUMERIC(4,2),
    DA NUMERIC(4,2),
    OPERATIONN VARCHAR(30),
    K12 INTEGER,
    K3 INTEGER,
    OPERATIONTYPE INTEGER,
    INVOICENUMBER VARCHAR(31))
AS
declare variable tempdate date;
begin
 tempdate = :date1;
for
  select it.regdate, sum(it.net), invoices.invoicenumber from it
  inner join operation on (it.operationid = operation.operationid)
  inner join quality on (it.buhqualityid = quality.qualityid)
  left join invoices on (it.invoiceid = invoices.invoiceid)
  where operation.operationtype =1
  and (it.operationid  not in (42) or :pobprod =1)
  and (it.operationid  not in (43) or :melkoe =1)
  and (it.operationid  = 42 or :pobprod =0)
  and (it.operationid = 43 or :melkoe =0)
  and it.regdate between :date1 and :date2
  and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, quality.cropid, quality.classid, it.storageid))
  and it.state >= 0
  and it.net <> 0
  and it.net is not null
  and it.parentid >= 0
  and (((quality.cropid = :cropid) and (:DOCROP = 1)) or ((quality.classid = :classid) and (:DOCROP = 0)))
  and ((it.ownerid = :clientid) or (:clientid  = -1))
  and ((it.contractid = :contractid) or (:contractid  = -1))
  group by it.regdate, invoicenumber
  into
  :regdate,
  :k12,
  :INVOICENUMBER
 do
 begin
   select  sum(it.net), sum(qb.dirt*it.net)/sum(it.net) db, sum(qa.dirt*it.net)/sum(it.net) da
 from it
 inner join quality_select(it.buhqualityid) qt on (0=0)
 inner join operation on (it.operationid = operation.operationid)
 inner join uwoutacts on (it.invoiceid = uwoutacts.invoiceid)
 left join quality_select(uwoutacts.beforequalityid) qb on (0=0)
 left join quality_select(uwoutacts.afterqualityid) qa on (0=0)
 and it.regdate between :tempdate and :regdate
 and uwoutacts.state = 0
 and it.partyid = (select partyid from getpartyid(:PARTYID,:PARTYDATE, qt.cropid, qt.classid, it.storageid))
 and it.state >= 0
 and it.parentid >= 0
 and it.net is not null
 and it.net <> 0
 and (((qt.cropid = :cropid) and (:DOCROP = 1)) or ((qt.classid = :classid) and (:DOCROP = 0)))
 and ((it.ownerid = :clientid) or (:clientid  = -1))
 and ((it.contractid = :contractid) or (:contractid  = -1))
 into
  :sumnet,
  :db,
  :da;

  tempdate = :regdate;
  if ((:pobprod = 0) and (:melkoe = 0)) then
  begin
    operationn = 'отходы 2 кат';
    operationtype = 2;
  end
  if (:pobprod = 1) then
  begin
    operationn = 'побочный продукт';
    operationtype = 4;
  end
  if (:melkoe = 1) then
  begin
    operationn = 'мелкое зерно';
    operationtype = 5;
  end
  suspend;
 end
end