if (select count(*) from SvoServicesStn where Svoistvo=9 and Glava = 1003)=0 then
  insert into SvoServicesStn
    VALUES(1003, 9 ,'')
end if;

go

if (select count(*) from SvoServicesStn where Svoistvo=10 and Glava = 1004)=0 then
  insert into SvoServicesStn
   VALUES(1004, 10 ,'')
end if;

go


if (select count(*) from SvoServicesStn where Svoistvo=11 and Glava = 1014)=0 then
  insert into SvoServicesStn
    VALUES(1014, 11 ,'')
end if;

go

call FillSvoRedoveStn()

go

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('GetUchGodinaPayments')))
THEN Drop procedure GetUchGodinaPayments
END IF

GO

create procedure DBA.GetUchGodinaPayments(in @GNo integer)
begin
  declare _egn varchar(18);
  declare _isegnench smallint;
  declare BeginYear integer;
  declare BrGodiniSum integer;
  declare i integer;
  declare LocalI integer;
  declare sqlstmnt varchar(30); //set bvrPayYear(1..5) = xxxx
  set i=5; //izpolzvam i za sqlstmnt - da si pazi vyv for cikyla do koi bvrPayYear sym stignal
  set LocalI=0; //izpolzvam LocalI za lokalniq while cikyl vyv for-a
  set BrGodiniSum=0;
  select top 1 G.egn,G.isegnench,YEAR(dateadd(day,B.Numlodgings,B.DateRegDt))+1 into _egn,_isegnench,
    BeginYear from Guests as G join Book as B on G.GuestNum = B.Guest where G.GuestNum = @GNo order by RegNum asc;
  --obrazuvame vsichki periodi, v koito studenta s tova egn e bil v obshtejitieto  i broim kolko obshto godini e imal plashtaniq
  for f1 as curs scroll cursor for
    select YEAR(DateRegDt) as Ybegin,
      YEAR(dateadd(day,if Staying = 1 then PayedLodgings else Numlodgings endif,DateRegDt)) as Yend,
      Yend-Ybegin+1 as BrGodini from Book where
      Guest = any(select GuestNum from Guests where egn = _egn and isegnench = _isegnench) order by
      RegNum desc do
    set LocalI=BrGodini;
    if BeginYear = Yend then
      set BrGodiniSum=BrGodiniSum-1;
      set LocalI=LocalI-1
    end if;
    set BrGodiniSum=BrGodiniSum+BrGodini;
    while(LocalI > 0) and(i > 0) loop
      set sqlstmnt='set bvrPayYear' || cast(i as varchar(1)) || ' = ';
      set sqlstmnt=sqlstmnt || Yend-BrGodini+LocalI;
      execute immediate sqlstmnt;
      set LocalI=LocalI-1;
      set i=i-1
    end loop;
    set BeginYear=Ybegin end for;
  if BrGodiniSum < 5 then
    --Myrdam godinite po mestata im, poneje ako ima po-malko ot 5 godini prestoi nqma da sa podredeni kakto trqbva
    while(BrGodiniSum > 0) loop
      set sqlstmnt='set bvrPayYear' || cast(5-BrGodiniSum+1-i as varchar(1)) || '=' || 'bvrPayYear' || cast(5-BrGodiniSum+1 as varchar(1));
      execute immediate sqlstmnt;
      set BrGodiniSum=BrGodiniSum-1
    end loop;
    --popylvam poslednite godini
    while(i > 0) loop
      set sqlstmnt='set bvrPayYear' || cast(6-i as varchar(1)) || '=' || 'bvrPayYear' || cast(6-i-1 as varchar(1)) || '+1';
      execute immediate sqlstmnt;
      set i=i-1
    end loop
  end if
end