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

GO

create procedure DBA.PrepareContractBill( in @ContractNo integer,in @place varchar(50) ) 
// Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles
// sus noshtuvkite,pochivkite i uslugite koito triabva da plashtat gostite nastaneni po daden dogovor
begin
  declare @ArticleNumber integer;
  declare @Deposit double;
  declare @Disc decimal(12,2);
  declare @PrWithDisc decimal(12,2);
  declare @IsForAlServices integer;
  declare @HumanKind integer;
  delete from #Articles;
  select Max(ArticleNumber)+1 into @ArticleNumber from #Articles;
  if @ArticleNumber is null then set @ArticleNumber = 1
  end if;
  //Proverqva dali ima ostanovene otstypka za vsi4ki uslugi ot paketa uslugi po dogovor
  select C.IsDiscForAll,C.Discount into @IsForAlServices,@Disc from Contracts as C where C.ContractNo = @ContractNo;
  if(@IsForAlServices = 0) then set @Disc = 0
  end if;
  for f2 as curs2 scroll cursor for
    select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest,
      B.Room as @Room,R.RoomName as @RoomName,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency,IsRest as @IsRest /*M*/ /*M*/
      from Book as B left outer join Rooms as R on B.Room = R.RoomNum /*,Rooms as R*/
      where B.ContractPays = @ContractNo and(B.NumLodgings-B.PayedLodgings) <> 0
  do //select(if HumanKind=1 then-1 else-2 endif) into @HumanKind from Guests where GuestNum=@Guest;
    //    if(@Disc=0) then
    //      select Discount into @Disc from ServicesForContract where ServiceNo=@HumanKind and ContractNo=@ContractNo;
    //      set @Price=CalcPriceWithDiscount(@Disc,@Price)
    //    end if
    //    ;
    //    message 'Cenata s otstupkata - >'+cast(@Price as varchar);
    //set @ArticleNumber=@ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,@ArticleNumber,@place) end for;
    set @ArticleNumber = @ArticleNumber+FillLodgingsForContractBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,@ArticleNumber,@place) end for;
  //    if(@IsForAlServices=0) then set @Disc=0
  //    end if
  //Naliva uslugi po dogovor
  if(@IsForAlServices = 0) then set @Disc = 0
  end if;
  for f1 as curs1 scroll cursor for
    select SC.CreditNo as @CreditNo,
      S.ServiceName as @ArticleName,SC.RoomNum as @Room,R.RoomName as @RoomName, /*М*/ /*M*/
      (SC.SumCredit*C.ExchangeRate) as @Price,SC.Service as @Service,
      (NumberServices-PayedServices) as @ArticleQty,VS.TipDDS as @TipDDS,
      SC.DateCreditDt as @DateCr,(if VS.TipDDS = 3 then 0 else VR.Rate endif) as @VatRate,VR.DDSType as @DDSType
      from ServiceCredits as SC left outer join Rooms as R on SC.RoomNum = R.RoomNum,Services as S join VATRates as VR on Services.VatRateN = VR.ID,VidSdelki as VS,Currencies as C /*,Rooms as R*/
      where SC.ContractPays = @ContractNo and(NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency do /*and SC.RoomNum = R.RoomNum*/
    if(@Room is not null) then
      --Maria
      --set @ArticleName=@ArticleName || @place || cast(@Room as varchar)
      set @ArticleName = @ArticleName || @place || @RoomName
    else --Maria
      set @ArticleName = @ArticleName
    end if;
    if(@Disc = 0) then
      select Discount into @Disc from ServicesForContract where ServiceNo = @Service and ContractNo = @ContractNo;
      message 'Otstypka - >'+cast(@Disc as varchar) type info to console;
      //set @Price=CalcPriceWithDiscount(@Disc,@Price);
      message 'Cenata s otstupkata - >'+cast(@Price as varchar) type info to console
    end if;
    call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service,@Disc,@DateCr);
    set @Disc = 0;
    set @ArticleNumber = @ArticleNumber+1 end for;
  set @Deposit = 0;
  select if VS.TipDDS = 3 then 0 else VR.Rate endif,
    VR.DDSType into @VR,@DDS_Type from Services as S join VatRates as VR on S.VatRateN = VR.ID
      join VidSdelki as VS on S.TipSdelka = VS.N
    where S.ServiceNo = 1004;
  set @Deposit = @Deposit+CalcContractDepositSum(@ContractNo)/(1+@VR);
  if @Deposit <> 0 then
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,RegDate,Vatrate,DDSType ) values( @ArticleNumber,'Депозити(и)','бр.',-1,0,-1,@Deposit,0,
      0,0,-@Deposit,2,1004,today(),@VR,@DDS_Type ) 
  end if
end
//[v 18.02.2003] Jorko
//[v 18.07.2003] Jorko Pribavia data na uslugata 1004