SET TERM ^ ;

CREATE OR ALTER PROCEDURE NARAH_TREE (
    data date,
    id_abon integer)
returns (
    period date,
    kod integer,
    text varchar(50),
    suma double precision,
    id_parent integer,
    n_tel char(11),
    id integer)
as
declare variable id_tel integer;
declare variable cnt integer;
begin
select max(R_FIRSTDAYMONTH(period)) from narah INTO :DATA;/
                 
select sum(suma) from narah where id_abon=:id_abon and period>=:data into :suma;
ID=:ID_ABON;
ID_PARENT =-1;
KOD=0;
PERIOD=:DATA;
TEXT=NULL;
SUSPEND;
FOR select t.id, t.n_tel
from telephones t
where t.id_abon=:ID_ABON
INTO :ID_TEL, :N_TEL
DO BEGIN
   KOD=1;
   TEXT='телефон '||:N_TEL;
   SUMA=NULL;
   ID_PARENT=:ID_ABON;
   ID=:ID_TEL;

   select count(*)
   from  narah n
   where n.id_tel=:ID_TEL
   and n.period>=:DATA
   into :cnt;

   if (:cnt>0) then SUSPEND;

   ID_PARENT=:ID_TEL;
   FOR select n.kod, n.period, n.suma, n.id , t.type_narah
   from  narah n, type_narah t
   where n.id_tel=:ID_TEL
   and n.kod=t.kod
   and n.period>=:DATA
   INTO :KOD, :PERIOD, :SUMA, :ID, :text DO SUSPEND;
END
FOR select l.id, 'линия '||l.punkt_1||' - '||l.punkt_2
from leaseds l
where l.id_abon=:ID_ABON
INTO :ID_TEL, :text
DO BEGIN
   KOD=7;
   SUMA=NULL;
   ID_PARENT=:ID_ABON;
   ID=:ID_TEL;
   N_TEL=:ID_TEL;

   select count(*)
   from  narah n
   where n.id_tel=:ID_TEL
   and n.period>=:DATA
   into :cnt;

   if (:cnt>0) then SUSPEND;

   ID_PARENT=:ID_TEL;
   FOR select n.kod, n.period, n.suma, n.id , t.type_narah
   from  narah n, type_narah t
   where n.id_tel=:ID_TEL
   and n.kod=t.kod
   and n.period>=:DATA
   INTO :KOD, :PERIOD, :SUMA, :ID, :text DO SUSPEND;
END
end^

SET TERM ; ^

/* Following GRANT statetements are generated automatically */

GRANT SELECT ON NARAH TO PROCEDURE NARAH_TREE;

/* Existing privileges on this procedure */

GRANT EXECUTE ON PROCEDURE NARAH_TREE TO SYSDBA;