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

REMINDER_CLIENT

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

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

Описание

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

Определение

CREATE PROCEDURE REMINDER_CLIENT(
    DATE1 DATE,
    DATE2 DATE,
    STORINGID INTEGER)
RETURNS (
    CROPNAME VARCHAR(20),
    CLIENTNAME VARCHAR(30),
    SUMMA INTEGER)
AS
declare variable income integer;
declare variable outcome integer;
declare variable clientid integer;
declare variable cropid integer;
begin
for
  select crop.cropname, clients.clientname, crop.CROPID, CLIENTS.CLIENTID from it
   left join clients on (it.ownerid = clients.clientid)
   left join quality on (it.BuhQualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   LEFT JOIN OPERATION on (it.OPERATIONID = OPERATION.OPERATIONID)
 where it.ownerid is not null and it.net is not null
  and quality.cropid is not null
  and it.regdate between :date1 and :date2
  and it.storingid = :storingid
  and it.STATE >= 0
  and it.PARENTID >= 0
--  AND OPERATION.OPERATIONSUBTYPE = 0
group by crop.cropname, clients.clientname, crop.CROPID, CLIENTS.CLIENTID
into
:CROPNAME, 
:CLIENTNAME,
:CROPID,
:CLIENTID
do
begin
  select cast(coalesce(sum(it.net),0) as integer) summa from it
   left join clients on (it.ownerid = clients.clientid)
   left join quality on (it.BuhQualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   LEFT JOIN OPERATION on (it.OPERATIONID = OPERATION.OPERATIONID)
where it.net is not null
  and it.regdate between :date1 and :date2
  and it.storingid = :storingid
  and it.STATE >= 0
  and it.PARENTID >= 0
  AND OPERATION.OPERATIONSUBTYPE = 0
  and QUALITY.CROPID = :CROPID
  and it.OWNERID = :CLIENTID
  into :INCOME;

  select cast(coalesce(sum(it.net),0) as integer) summa from it
   left join clients on (it.ownerid = clients.clientid)
   left join quality on (it.BuhQualityid = quality.qualityid)
   left join crop on (quality.cropid = crop.cropid)
   LEFT JOIN OPERATION on (it.OPERATIONID = OPERATION.OPERATIONID)
where it.net is not null
  and it.regdate between :date1 and :date2
  and it.storingid = :storingid
  and it.STATE >= 0
  and it.PARENTID >= 0
  AND OPERATION.OPERATIONSUBTYPE = 1
  and QUALITY.CROPID = :CROPID
  and it.OWNERID = :CLIENTID
  into :OUTCOME;
summa = :INCOME - :OUTCOME;
suspend;
end
end