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

GO

create procedure DBA.PrepareAddrCard( in @Gno integer ) 
begin
  declare @MonthsGreek varchar(50);
  declare i integer;
  declare j integer;
  declare sqlsttm varchar(200);
  declare _egn varchar(18);
  declare _IsEgnEnch integer;
  call GetUchGodinaPayments(@Gno);
  select egn,isegnench into _egn,_IsEgnEnch from Guests where GuestNum = @GNo;
  set i = 1;
  set @MonthsGreek = '   I  II III  IV   V  VI VIIVIII  IX   X  XI XII';
  delete from #AddrCardPayments;
  while i <= 48 loop
    insert into #AddrCardPayments( Mes,MesReal ) values( Trim(SUBSTR(@MonthsGreek,i,4)),i/4+1 ) ;
    set i = i+4
  end loop;
  set j = 1;
  for f1 as curs1 scroll cursor for
    select if TipDDS = 2 then SUM(A.Price*(1+A.VatRate)) else SUM(A.Price) endif as Price,
      cast(SRS.Stoinost as integer) as Mnth,cast(SRS1.Stoinost as integer) as Yr,GetInvoices(@GNo,Mnth,Yr) as INo //GetInvoiceNumbers(@GNo,Mnth,Yr) as INo
      from Book as B,Articles as A,Invoices as Inv,Currencies as C,SvoRedoveStn as SRS,SvoRedoveStn as SRS1
      where B.WhoPays = any(select GuestNum from Guests where egn = _egn and isegnench = _IsEgnEnch)
      and B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo
      and SRS.ArticleNo = A.ArticleNo and SRS1.ArticleNo = A.ArticleNo and SRS.InvoiceNo = A.InvoiceNo /*and Inv.CancelInvoiceNo = 0*/
      and SRS1.InvoiceNo = A.InvoiceNo and SRS.Svoistvo = 1 and SRS1.Svoistvo = 2
      group by TipDDS,INo,Mnth,Yr
      order by Yr asc,Mnth asc do
    set i = Mnth;
    set j = 0;
    if Yr = bvrPayYear1 then
      set j = 1
    else
      if Yr = bvrPayYear2 then
        set j = 2
      else
        if Yr = bvrPayYear3 then
          set j = 3
        else
          if Yr = bvrPayYear4 then
            set j = 4
          else
            if Yr = bvrPayYear5 then
              set j = 5
            end if
          end if end if end if end if;
    if j <> 0 then
      set sqlsttm = 'update #AddrCardPayments set lvUG' || j || '=' || Round(Price,2) || ',kvUG' || j || '=''' || INo || ''' where MesReal=' || i
    end if;
    execute immediate sqlsttm
  end for
end
-- Yoan Vadi externalNo pri 2 smetki za mesec