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

GO

create function
DBA.GetInvoiceNumbers(in @GNo integer,in @Mnth integer,in @Yr integer)
returns varchar(50)
begin
  declare rez varchar(50);
  set rez='';
  for f as curs scroll cursor for
    select distinct A.InvoiceNo as InvNo from Articles as A,SvoRedoveStn as SVS1,SvoRedoveStn as SVS2 where
      A.RegNo = any(select RegNum from Book where WhoPays = @GNo) and
      SVS1.Svoistvo = 1 and SVS2.Svoistvo = 2 and SVS1.Stoinost = @Mnth and SVS2.Stoinost = @Yr and
      A.ArticleNo = SVS1.ArticleNo and A.InvoiceNo = SVS1.InvoiceNo and
      A.ArticleNo = SVS2.ArticleNo and A.InvoiceNo = SVS2.InvoiceNo do 
    set rez=rez || (select Inv1.ExternalNo from Invoices Inv1 where Inv1.InvoiceNo=InvNo) || ', ' end for;
  set rez=substr(rez,0,length(rez)-2);
  return rez
end

--[v 11.09.2009] - Yoan - vrashta ExternalNo



go

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 @BroiPayments integer;
  declare j integer;
  declare sqlsttm varchar(200);
  set @BroiPayments=(select count(*) from Book as B,Articles as A,Invoices as Inv,Currencies as C where B.WhoPays = @Gno and B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo and Inv.CancelInvoiceNo = 0);
  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+bvrDDSPart)) else SUM(A.Price) endif as Price,cast(SRS.Stoinost as integer) as Mnth,cast(SRS1.Stoinost as integer) as Yr,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 = @Gno and B.KodCurrency = C.KodCurrency and B.RegNum = A.RegNo and A.InvoiceNo = Inv.InvoiceNo and
      Inv.CancelInvoiceNo = 0 and SRS.ArticleNo = A.ArticleNo and SRS1.ArticleNo = A.ArticleNo and SRS.InvoiceNo = A.InvoiceNo 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 sqlsttm='update #AddrCardPayments set lvUG' || j || '=' || Round(Price,2) || ',kvUG' || j || '=''' || INo || ''' where MesReal=' || i;
    execute immediate sqlsttm;
    if i = 12 then
      set j=j+1
    end if end for
end

-- Yoan Vadi externalNo pri 2 smetki za mesec

go

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

GO


CREATE PROCEDURE "DBA"."FillArticleLodgings"(in @RegNo integer, in @PackagePrice numeric(12,2), in @FillAll integer, in @place varchar(50))
begin
declare @Price numeric(12,2);
declare @ArticlePrice numeric(12,2);
declare @KodCurrency integer;
declare @NachinPlat integer;
declare @ServiceNoLodging integer;
declare @NumDays integer;
declare @DateReg date;
declare @TipDDS integer;
declare @RoomNo integer;
declare @RoomName varchar(6);
declare @IsRest integer;
declare @ArticleName varchar(100);
declare @ServiceName varchar(40);
declare @DateStart date;
declare @MDays integer;
delete from #ArticleLodgings;
select
B.Price, B.KodCurrency, B.Room, PK.NachinPlat, isNull(PK.ServiceNo,-1), B.NumLodgings-B.PayedLodgings, dateadd(day,B.PayedLodgings,B.DateRegDt), B.IsRest into
@Price, @KodCurrency, @RoomNo, @NachinPlat, @ServiceNoLodging, @NumDays, @DateReg, @IsRest
from Book B, PriceKinds PK
where B.RegNum=@RegNo and B.PriceKind=PK.PriceKind;
if (@NumDays > 0) then
if @ServiceNoLodging=0 then
set @ServiceNoLodging=-1;
end if;
select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
select ServiceName into @ServiceName from Services where ServiceNo=@ServiceNoLodging;
select V.TipDDS into @TipDDS from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
if @TipDDS = 2 then
set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
set @TipDDS=1
end if;
set @ArticlePrice=@ArticlePrice-@PackagePrice;
if @TipDDS = 1 then
set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
set @TipDDS=2
end if;
set @RoomName=(select RoomName from Rooms where RoomNum=@RoomNo);
if @NachinPlat=0 then
if @IsRest=1 then
set @ArticleName='Почивка ' || @place || @RoomName +' за '+dateformat(@DateReg,'Dd.Mm')
else
set @ArticleName=@ServiceName || ' ' || @place || @RoomName +' от '+dateformat(@DateReg,'Dd.Mm.yyyy')+' до '+dateformat(dateadd(day,@NumDays,@DateReg),'Dd.Mm.yyyy')
end if;
insert into #ArticleLodgings (RegNum,"Name",RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS)
values (@RegNo,@ArticleName,0,@ArticlePrice,@NumDays,@NumDays,0,0,@TipDDS);
else // @NachinPlat =1
// vurti cikul mesec po mesec i populva tablicata za vseki mesec
set @DateStart = @DateReg;
while (@NumDays > 0) loop
select FillSingleArticleLodging(@RegNo,@DateStart,@NumDays,@ArticlePrice,@PackagePrice,@ServiceName,@FillAll,@TipDDS,@place) into @MDays;
set @DateStart=dateadd(day,@NumDays-@MDays,@DateStart);
set @NumDays=@MDays;
end loop;
end if; // @NachinPlat=1
update #ArticleLodgings set ServiceNoLodging=@ServiceNoLodging where ServiceNoLodging is null;
end if // @NumDays > 0
end

-- Yoan & Milko - dobaveno i godina v smetkata