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

GO

create procedure DBA.PrepareGroupBill( in @GroupNo integer ) 
// Tazi procedura se izpolzva za napylwane na wremennata tablica #Articles
// sus noshtuvkite,pochivkite i uslugite koito triabva da plashtat gostite ot dadena grupa
begin
  declare @ArticleNumber integer;
  declare @Deposit double;
  declare @VatRate numeric(3,3);
  declare @DDSType integer;
  declare @aTipDDS integer;
  //Iztriva wremennata tablica za smetka
  delete from #Articles;
  //
  select Max(ArticleNumber)+1 into @ArticleNumber from #Articles;
  if @ArticleNumber is null then set @ArticleNumber = 1
  end if;
  //Vkarva noshtuvkite, pochivkite i zastrahovkite, koito triabva da plashtat ot gostite ot grupata
  for f as curs scroll cursor for
    select B.RegNum as @RegNo,dateadd(day,B.PayedLodgings,B.DateRegDt) as @DateReg,B.Guest as @Guest,B.WhoPays as @WhoPays,
      B.Room as @Room,(B.NumLodgings-B.PayedLodgings) as @NumDays,B.Price as @Price,B.KodCurrency as @KodCurrency,B.IsRest as @IsRest
      from Book as B left outer join Guests on Guests.GuestNum = B.WhoPays
      where(B.NumLodgings-B.PayedLodgings) <> 0 and(Guests."Group" = @GroupNo or GroupPays = @GroupNo)
  do //Vmukva vuv Vremennata tablica
    set @ArticleNumber = @ArticleNumber+FillLodgingsForBill(@RegNo,@DateReg,@Room,@NumDays,@Price,@KodCurrency,@IsRest,
      @ArticleNumber) end for;
  //Vkarva uslugite, koito triabva da plashtat gostite ot grupata
  for f0 as curs0 scroll cursor for
    select SC.CreditNo as @CreditNo,S.ServiceName as @ArticleName,(SC.SumCredit*C.ExchangeRate) as @Price,SC.Service as @Service,
      (NumberServices-PayedServices) as @ArticleQty,VS.TipDDS as @TipDDS,SC.WhoPays as @WhoPays
      from ServiceCredits as SC left outer join Guests on Guests.GuestNum = SC.WhoPays,Services as S,VidSdelki as VS,Currencies as C
      where(NumberServices-PayedServices) <> 0 and SC.Service = S.ServiceNo and S.TipSdelka = VS.N and SC.KodCurrency = C.KodCurrency
      and(SC.GroupPays = @GroupNo or Guests."Group" = @GroupNo) do
    call FillServicesForBill(@ArticleNumber,@ArticleName,@ArticleQty,@Price,@CreditNo,@TipDDS,@Service);
    set @ArticleNumber = @ArticleNumber+1 end for;
  //Izchisliava dali ima depositi i gi vkarva
  select sum(ResMoney.Sum*Currencies.ExchangeRate)
    into @Deposit from ResMoney left outer join Guests on ResMoney.GuestNo = Guests.GuestNum,Currencies
    where ResMoney.Status = 2 and ResMoney.Currency = Currencies.KodCurrency
    and(ResMoney.GroupNo = @GroupNo or Guests."Group" = @GroupNo);
  if @Deposit is null then set @Deposit = 0
  end if;
  select if VS.TipDDS = 3 then 0 else VR.Rate endif,
    VR.DDSType,VS.TipDDS into @VatRate,@DDSType,@aTipDDS
    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 = @Service;
  set @Deposit = @Deposit/(1+@VatRate);
  if @Deposit <> 0 then
    insert into #Articles( ArticleNumber,ArticleName,Measure,ArticleQty,MaxQty,MinQty,ArticlePrice,Discount,RegNo,
      CreditNo,ArticleSum,TipDDS,ServiceNo,VatRate,DDSType ) values( @ArticleNumber,'Депозит(и)','бр.',-1,0,-1,@Deposit,0,
      0,0,-@Deposit,2,@Service,@VatRate,@DDSType ) 
  end if
end
//[v 00.11.17]