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

GRAINAVAILABILITYCLASS

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

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

Описание

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

Определение

CREATE PROCEDURE GRAINAVAILABILITYCLASS(
    DATE2 DATE,
    PARTYID INTEGER,
    PARTYDATE DATE)
RETURNS (
    CLIENTFULLNAME VARCHAR(100),
    CROPCLASS VARCHAR(50),
    AVAILABILE BIGINT,
    CLIENTTYPENAME VARCHAR(30))
AS
declare variable income integer;
declare variable outcome integer;
declare variable clientid integer;
declare variable classid integer;
declare variable operationtype integer;
declare variable tempweight integer;
BEGIN
  FOR
    select
      clients.clientfullname,
      coalesce(sum(it.weight),0),
      clients.clientid,
      quality.classid,
      qn.cropfullname,
      case clients.clienttypeid
        when 1 then 'Товаропроизводители' when 2 then 'Владельцы'
        else (case it.ownerid when 231 then 'Госрезерв'  when 263 then 'Госресурсы' else 'прочие' end)
      end clienttypename
    from  it
      join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
      join clients on (it.ownerid = clients.clientid)
      join quality on (it.BuhQualityid = quality.qualityid)
      left join operation on (it.operationid = operation.operationid)
      left join quality_cropfullname(quality.cropid, quality.classid, null, 1) qn on (0=0)
      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 = 0
      and storage.storagetypeid = 1
      and it.regdate <= :date2
      and it.weight is not null  and it.weight <> 0
      and it.parentid >= 0
      and it.state >=0
    group by clients.clientfullname,
     clients.clientid, quality.classid, qn.cropfullname,
     clienttypename
--     order by clients.clientfullname, crop.cropname||' '||class.classname
      INTO
         :clientfullname,
         :income,
         :clientid,
         :classid,
         :cropclass,
         :clienttypename
  DO
  BEGIN
    outcome = 0;
  for
    select
      coalesce(sum(it.weight),0),
      operation.operationtype
    from  it
      join getworkparties(:partyid,:partydate,null,null,null)
       on (it.partyid = getworkparties.partyid)
      join quality on (it.BuhQualityid = quality.qualityid)
      left join operation on (it.operationid = operation.operationid)
    where operation.operationsubtype = 1
      and it.regdate <= :date2
      and it.ownerid = :clientid
      and quality.classid = :classid
      and it.weight is not null  and it.weight <> 0
      and it.parentid >= 0
      and it.state >=0
    group by  operation.operationtype
    INTO :tempweight,
         :operationtype
   do
   begin
     if (:operationtype = 2) then
       outcome =  :outcome + :tempweight * -1;
     else
       outcome = :outcome + :tempweight;
   end

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