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

GO

create procedure DBA.FillInfoExtendedlodgings( in @GuestNo integer,in @IsRest tinyint ) 
begin
  declare @ArticleName varchar(40);
  declare @ArticlePrice double;
  declare @ArticlePrWithDDS double; //nt
  declare @PackagePrice double;
  declare @PackagePrWithDDS double; //nt
  declare @TipDDS integer;
  declare @VatRate numeric(3,3);
  declare @DDSType integer;
  declare @ServiceNoLodging integer;
  delete from #ExtendedLodgings;
  for f1 as curs scroll cursor for
    select RegNum as @RegNo,Price as @Price,KodCurrency as @KodCurrency from Book
      where whopays = @GuestNo and IsRest = @IsRest do
    if(select NachinPlat from PriceKinds as PK join Book as B on PK.PriceKind = B.PriceKind where B.RegNum = @RegNo) = 1 then
      select PK.ServiceNo into @ServiceNoLodging from Book,PriceKinds as PK where Book.RegNum = @RegNo and Book.PriceKind = PK.PriceKind;
      select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
      set @ArticlePrWithDDS = @ArticlePrice; //nt
      select Sum(if VidSdelki.TipDDS = 2 then(1+VR.Rate)*Packages.Price*Currencies.ExchangeRate else Packages.Price*Currencies.ExchangeRate endif)
        into @PackagePrice from Packages,Currencies,Services join VATRates as VR on Services.VatRateN = VR.ID,VidSdelki
        where Packages.RegNo = @RegNo and Packages.KodCurrency = Currencies.KodCurrency
        and Packages.ServiceNo = Services.ServiceNo and Services.TipSdelka = VidSdelki.N;
      if @PackagePrice is null then
        set @PackagePrice = 0;
        set @PackagePrWithDDS = 0
      else
        set @PackagePrWithDDS = @PackagePrice
      end if;
      select V.TipDDS,if V.TipDDS = 3 then 0 else VR.Rate endif,
      VR.DDSType into @TipDDS,@VatRate,@DDSType from Services as S,VidSdelki as V where S.ServiceNo = @ServiceNoLodging and S.TipSdelka = V.N;
      if @TipDDS = 2 then
        set @ArticlePrice = @ArticlePrice*(@VatRate+1); //ako dds-to e vkliucheno v cenata
        set @TipDDS = 1
      end if;
      set @ArticlePrice = @ArticlePrice-@PackagePrice;
      if @TipDDS = 1 then
        set @ArticlePrice = @ArticlePrice/(@VatRate+1); //ako dds-to ne e vkliucheno v cenata
        set @TipDDS = 2
      end if;
      set @ArticlePrWithDDS = @ArticlePrWithDDS-@PackagePrWithDDS;
      call GetRealNoshtuvki(@RegNo,0,0,0,0,0,@ArticlePrice,@ArticlePrWithDDS)
    end if
  end for
end