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

GRAINAVAILABILITY

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

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

Описание

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

Определение

CREATE PROCEDURE GRAINAVAILABILITY(
    DATE1 DATE,
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE,
    SELECTPERIOD SMALLINT,
    STORAGEID INTEGER)
RETURNS (
    CLIENTFULLNAME VARCHAR(100),
    ADDRESS VARCHAR(100),
    OKPO VARCHAR(10),
    CROPNAME VARCHAR(100),
    AVAILABILE NUMERIC(15,3),
    INCOME NUMERIC(15,3),
    OUTCOME NUMERIC(15,3),
    SAMPLE NUMERIC(15,3),
    WASTE NUMERIC(15,3),
    CLASSNAME VARCHAR(20))
AS
declare variable clientid integer;
declare variable cropid integer;
declare variable classid integer;
BEGIN
   AVAILABILE = 0;
   income = 0;
   outcome = 0;
   waste = 0;
   sample = 0;
  FOR
    select
      clients.okpo,
      clients.clientfullname ,
      clients.address,
      clients.clientid,
      quality.cropid,
      crop.cropname,
      class.classname,
      quality.classid
    from  it
      join GetWorkParties(:PARTYID,:PARTYDATE,null,null,null)
        on (IT.PartyID = GetWorkParties.PartyID)
      left join clients on (it.ownerid = clients.clientid)
      left join operation on (it.operationid = operation.operationid)
      left join quality on (it.samplerqualityid = quality.qualityid)
      left join crop on (quality.cropid = crop.cropid)
      left join class on (quality.classid = class.classid)
      left join storage on (it.storageid = storage.storageid)
    where operation.operationsubtype in (0,1)
     and storage.storagetypeid = 1
     and ((it.regdate  <= :date2 and :selectperiod = 1) or (it.regdate between :date1 and :date2 and :selectperiod = 2))
     and it.parentid >= 0
     and it.operationid <> 17
     and crop.croptypeid <> 3
     and it.weight is not null
     --and it.weight <> 0
     and it.state >= 0
     and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
    group by clients.clientfullname , clients.address,  clients.clientid, clients.okpo, quality.cropid, crop.cropname, class.classname, quality.classid
    order by crop.cropname, clients.clientfullname, quality.classid
       INTO :OKPO,
         :CLIENTFULLNAME,
         :ADDRESS,
         :clientid,
         :cropid,
         :cropname,
         :classname,
         :classid
  do
  begin
    select coalesce(sum(it.weight),0)
    from  it
      join GetWorkParties(:PARTYID,:PARTYDATE,:CropID,:ClassID,null)
       on (IT.PartyID = GetWorkParties.PartyID)
      left join operation on (it.operationid = operation.operationid)
      left join quality on (it.samplerqualityid = quality.qualityid)
      left join storage on (it.storageid = storage.storageid)
    where operation.operationsubtype = 0
     and storage.storagetypeid = 1
     and it.ownerid = :clientid
     and quality.classid = :classid
     and quality.cropid = :cropid
     and ((it.regdate  <= :date2 and :selectperiod = 1) or (it.regdate between :date1 and :date2 and :selectperiod = 2))
     and it.operationid <> 17
     and it.parentid >= 0
     and it.weight is not null
     and it.weight <> 0
     and it.state >= 0
     and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
 --   group by quality.cropid, it.ownerid
    INTO :INCOME;

   select
     coalesce(sum( it.net),0)
   from  it
     join GetWorkParties(:PARTYID,:PARTYDATE,:cropID,:Classid,null)
       on (IT.PartyID = GetWorkParties.PartyID)
     left join operation on (it.operationid = operation.operationid)
     left join quality on (it.samplerqualityid = quality.qualityid)
     left join storage on (it.storageid = storage.storageid)
     left join invoices on (it.invoiceid = invoices.invoiceid)
     left join clients on (invoices.senderid = clients.clientid)
   where operation.operationsubtype = 1
    and it.operationid <> 70
    and it.operationid <> 65
    and operation.operationtype not in (1,5)
    and ((it.regdate <= :date2 and :selectperiod = 1)
      or (it.regdate between :date1 and :date2 and :selectperiod = 2))
    and storage.storagetypeid = 1
    and it.ownerid = :clientid
    and quality.classid = :classid
    and quality.cropid = :cropid
    and it.parentid >= 0
    and it.state >= 0
    and it.net is not null
    and it.net <> 0
    and ((clients.storageid is null and coalesce(:storageid,0) = 0)
         or (coalesce(:storageid,0) <> 0))
    and ((it.storageid = :storageid)
      or (:storageid = 0) or (:storageid is null) )
  INTO :OUTCOME;

   select coalesce(sum(it.net),0)
   from  it
     join GetWorkParties(:PARTYID,:PARTYDATE,null,null,null)
       on (IT.PartyID = GetWorkParties.PartyID)
    left join operation on (it.operationid = operation.operationid)
   inner join quality on (it.samplerqualityid = quality.qualityid)
   left join storage on (it.storageid = storage.storageid)
   where
    it.operationid = 70
   and ((it.regdate  <= :date2 and :selectperiod = 1) or (it.regdate between :date1 and :date2 and :selectperiod = 2))
   and storage.storagetypeid = 1
   and it.ownerid = :clientid
   and quality.cropid = :cropid
   and quality.classid = :classid
   and it.parentid >= 0
   and it.state >= 0
   and it.net is not null  and it.net <> 0
   and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
  INTO :sample;

   select coalesce(sum(it.net),0) from  it
     join GetWorkParties(:PARTYID,:PARTYDATE,:cropid,:classid,null)
       on (IT.PartyID = GetWorkParties.PartyID)
     left join operation on (it.operationid = operation.operationid)
     inner join quality on (it.samplerqualityid = quality.qualityid)
     left join storage on (it.storageid = storage.storageid)
   where
   operation.operationtype in (1,5)
   and ((it.regdate  <= :date2 and :selectperiod = 1) or (it.regdate between :date1 and :date2 and :selectperiod = 2))
   and storage.storagetypeid = 1
   and it.ownerid = :clientid
   and quality.cropid = :cropid
   and quality.classid = :classid
   and it.parentid >= 0
   and it.state >= 0
   and it.net is not null  and it.net <> 0
   and ((it.storageid = :storageid) or ((:storageid = 0) or (:storageid is null) ))
  INTO :waste;

   if (:income <> 0) then
     income = :income / 1000;
   if (:outcome <> 0) then
     outcome = :outcome / 1000;
   if (:waste <> 0) then
     waste = :waste / 1000;
   if (:sample <> 0) then
     sample = :sample / 1000;

    AVAILABILE = :income - :outcome - :waste - :sample;
    if ((:availabile is not null) and (availabile <> 0)) then
    begin
      SUSPEND;
    end
    AVAILABILE = 0;
    income = 0;
    outcome = 0;
  END
END