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

GO

create function DBA.FillLodgingsForContractBill( in @RegNo integer,in @DateReg date,in @Room integer,in @NumDays integer,in @Price double,in @KodCurrency integer,in @IsRest integer,in @ArticleNumber integer,in @place varchar(50) ) 
returns integer
begin
  declare @ArticleName varchar(40);
  declare @ArticlePrice double;
  declare @PackagePrice double;
  declare @PackagePrWithDDS double; //nt
  declare @TipDDS integer;
  declare @ServiceNoLodging integer;
  declare @I integer;
  declare @Disc double; //nt
  declare @ArticlePrWithDDS double; //nt
  declare @IsForAlServices integer;
  declare @ContractNo integer;
  --Maria
  declare @RoomName varchar(5);
  declare @VatRate numeric(3,3);
  declare @DDSType integer;
  declare @VatRateTax numeric(3,3);
  declare @DDSTypeTax integer;
  --Maria
  //Proverqva dali ima otstypka po dogovora i dali tazi otstypka e za vsi4ki services
  select C.IsDiscForAll,C.Discount,c.ContractNo into @IsForAlServices,
    @Disc,
    @ContractNo from Contracts as C,Book as B
    where B.ContractPays = C.ContractNo
    and B.RegNum = @Regno;
  if(@IsForAlServices = 0) then set @Disc = 0
  end if;
  //@ServiceNoLodging e N-na na noshtuvka ili pochivka syotvetno za nashenec ili chujdenec
  select(if Guests.HumanKind = 1 then(-1) else(-2) endif)
    into @ServiceNoLodging from Book,Guests where Book.RegNum = @RegNo and Book.Guest = Guests.GuestNum;
  //Izchislenie cenata na noshtuvkata v leva syobrazno otstypkata po dogovora ako ima otstypka
  select(ExchangeRate*@Price) into @ArticlePrice from Currencies where KodCurrency = @KodCurrency;
  set @ArticlePrWithDDS = @ArticlePrice;
  //Podvajda sumata na uslugite ot paketa ot
  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 //nt
  else
    set @PackagePrWithDDS = @PackagePrice //nt
  end if;
  //Izchisliava se tipa DDS za noshtuvkata
  select V.TipDDS,if V.TipDDS = 3 then 0 else VR.Rate endif,
    VR.DDSType into @TipDDS,@VatRate,@DDSType from Services as S join VATRates as VR on S.VatRateN = VR.ID,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 e vkliucheno v cenata
    set @TipDDS = 2
  end if;
  --Maria
  set @RoomName = (select RoomName from Rooms where RoomNum = @Room);
  --if @IsRest = 1 then set @ArticleName='Почивка ' || @place || cast(@Room as varchar(5))+' за '+dateformat(@DateReg,'Dd.Mm')
  if @IsRest = 1 then set @ArticleName = 'Почивка ' || @place || @RoomName+' за '+dateformat(@DateReg,'Dd.Mm')
  else --Maria
    --Maria
    -- set @ArticleName='Нощувка ' || @place || cast(@Room as varchar(5))+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm')
    set @ArticleName = 'Нощувка ' || @place || @RoomName+' от '+dateformat(@DateReg,'Dd.Mm')+' до '+dateformat(@DateReg+@NumDays,'Dd.Mm')
  end if; --Maria
  if(@Disc = 0) then
    select srv.Discount
      into @Disc from ServicesForContract as srv,Book as b
      where b.ContractPays = srv.ContractNo
      and b.RegNum = @RegNo
      and srv.ServiceNo = @ServiceNoLodging
  end if; //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice)
  set @ArticlePrWithDDS = @ArticlePrWithDDS-@PackagePrWithDDS; //nt
  //Insertva noshtuvka ili pochivka
  insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
    CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate,VatRate,DDSType ) values( @ArticleNumber,@ArticleName,'бр.',
    @NumDays,@NumDays,0,@ArticlePrice,@Disc,@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
    @TipDDS,@ServiceNoLodging,cast(FormatNum(@ArticlePrWithDDS,'0.00') as double),
    ((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null,@VatRate,@DDSType ) ;
  set @I = 1;
  set @Disc = 0;
  //  if(@IsForAlServices=0) then set @Disc=0
  //  end if
  //  ; //Insertva i uslugite ot paketa
  for f as curs scroll cursor for
    select Packages.ServiceNo as @ServiceNo,Packages.Price as @ServicePrice,Packages.KodCurrency as @ServiceCurrency,
      Services.ServiceName as @ServiceName from Packages,Services
      where RegNo = @RegNo and Packages.ServiceNo = Services.ServiceNo do
    select(ExchangeRate*@ServicePrice) into @ArticlePrice from Currencies where KodCurrency = @ServiceCurrency;
    //Izchisliava se tipa DDS za uslugata
    //    select distinct Max(Contracts.Discount) into @Disc from Contracts,Guests,Book,Packages where Guests.Contract=Contracts.ContractNo and Guests.GuestNum=Book.Guest and Packages.RegNo=Book.RegNum and Book.RegNum=@RegNo; //nt
    select V.TipDDS,if V.TipDDS = 3 then 0 else VR.Rate endif,
      VR.DDSType into @TipDDS,@VatRateTax,@DDSTypeTax from Services as S join VATRates as VR on S.VatRateN = VR.ID,VidSdelki as V where S.ServiceNo = @ServiceNo and S.TipSdelka = V.N;
    if @TipDDS = 1 then
      set @ArticlePrice = @ArticlePrice/(@VatRateTax+1); //ako dds-to e vkliucheno v cenata
      set @TipDDS = 2
    end if;
    //set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice);
    if(@Disc = 0) then
      select Discount into @Disc from ServicesForContract where ContractNo = @ContractNo and ServiceNo = @ServiceNo
    end if;
    //    set @ArticlePrice=CalcPriceWithDiscount(@Disc,@ArticlePrice);
    //Insertva uslugata vuv #Articles
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,PriceInvWithDDS,ArticleSumWithDDS,RegDate,VatRate,DDSType ) values( @ArticleNumber+@I,@ServiceName,'бр.',
      @NumDays,@NumDays,0,@ArticlePrice,@Disc,-@RegNo,0,((@ArticlePrice*@NumDays)-((@ArticlePrice*@NumDays)*@Disc)/100),
      @TipDDS,@ServiceNo,@ArticlePrWithDDS,((@ArticlePrWithDDS*@NumDays)-((@ArticlePrWithDDS*@NumDays)*@Disc)/100),null,@VatRateTax,@DDSTypeTax ) ;
    set @I = @I+1;
    set @Disc = 0 end for;
  return(@I)
end