IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('BookReserveGuest'))) THEN
Drop function BookReserveGuest
END IF

GO

CREATE FUNCTION "DBA"."BookReserveGuest"(in @DateReg float,in @ReserveNo integer,in @RoomNo integer,in @RN integer,in @NastKind integer,in @HumanKind integer,in @PriceKindNo integer,in @AdultType integer,in @KodAdmin integer)
returns varchar(100)
// Nastaniava edin gost ot rezervacia @ReserveNo v staia @RoomNo.
//
// @DateReg - data za registracia
// @ReserveNo - nomer rezervacia
// @RoomNo - nomer staia
// @RN - ReservePlan.N ili Book.RegNum
// @NastKind:
//   0: Nastaniavane v nezaeta staia - staiata se prispada ot rezervaciata
//   1: Donastaniavane (v zaeta staia) - staiata ne se prispada ot rezervaciata
// @PriceKindNo - cenorazpisa, po koito se nastaniava
// @AdultType - 0 - vuzrasten, 1 - dete
// Return:
//   '' - OK
//   <Error message> - Not OK
begin atomic
  declare @R varchar(100);
  declare @GuestN integer;
  declare @Days integer;
  declare @GuestName varchar(40);
  declare @ContractNo integer;
  declare @BoardPlanDef integer;
  declare @Price decimal(12,2);
  declare @Currency integer;
  declare @S char(100);
  declare @RoomCat integer;
  declare @BookN integer;
  declare @RPlan integer;
  declare @TariffNo integer;
  declare @Description varchar(30);
  declare @IsEgnEnch  integer;
  
  if @HumanKind=1 then
       set @IsEgnEnch=1;
  else
       set @IsEgnEnch=0;
  end if;


  set @R='';
  if @NastKind=0 then set @RPlan=@RN
  else set @RPlan=0
  end if
  ;
  //
  //Izbor na gost:
  set @GuestN=null;
  select MIN(GuestNum) into @GuestN
    from Guests as G,ReservePlan as RP
    where G.ReservePlanNo=RP.N and RP.RoomNo=@RoomNo and G.Status=0 and G.Reservation=@ReserveNo
    and G.ResPriceKind=@PriceKindNo and G.AdultType=@AdultType;
  if @GuestN is null then
    select MIN(GuestNum) into @GuestN
      from Guests as G,ReservePlan as RP
      where G.ReservePlanNo=RP.N and RP.RoomNo=@RoomNo and G.Status=0 and G.Reservation=@ReserveNo
      and G.AdultType=@AdultType
  end if
  ;
  if @GuestN is null then
    select MIN(GuestNum) into @GuestN
      from Guests as G
      where G.Status=0 and G.Reservation=@ReserveNo and G.ReservePlanNo=0
      and G.AdultType=@AdultType
  end if
  ;
  if @GuestN is null then
    select MIN(GuestNum) into @GuestN
      from Guests as G
      where G.Status=0 and G.Reservation=@ReserveNo and G.ReservePlanNo=0 and HasData=0
  end if
  ;
  if @GuestN is null then
    // Create new guest;
    set @GuestN=GetCounter('GuestCounter');
    select "Name"||' '||@GuestN,Days,ContractNo,BoardPlanDef into @GuestName,
      @Days,@ContractNo,
      @BoardPlanDef from Reserve where ReserveNo=@ReserveNo;
    insert into Guests(GuestNum,HumanKind,"Name",Reservation,HasData,
      Contract,BoardPlanDef,ResNumLogin,ResPriceKind,Status,ReservePlanNo,AdultType,IsEgnEnch) values(
      @GuestN,@HumanKind,@GuestName,@ReserveNo,0,
      @ContractNo,@BoardPlanDef,@Days,@PriceKindNo,0,@RPlan,@AdultType,@IsEgnEnch);
  else
    // update guest
    update Guests set HumanKind=@HumanKind,AdultType=@AdultType,IsEgnEnch=@IsEgnEnch where GuestNum=@GuestN
  end if
  ;
  //
  //Izbor na cena:
  select RoomCat into @RoomCat
    from Rooms where RoomNum=@RoomNo;
  select HumanKind into @HumanKind from Guests where GuestNum=@GuestN;
  if @NastKind=0 then
    select NumDays into @Days from ReservePlan where N=@RN
  else
    select NumLodgings into @Days from Book where RegNum=@RN
  end if
  ; //
  select PriceAll,KodCurrency,TariffNo into @Price,@Currency,
    @TariffNo from PriceList,Registers
    where ClassNo=@RoomCat and PriceKindNo=@PriceKindNo and AdultType=@AdultType and HumanKind=@HumanKind
    and SeasonNo=Registers.IntStojnost and Registers.RegisterName='ActivePriceSeason';
  //
  if @TariffNo is null then
    select Description into @Description from PriceKinds where PriceKind=@PriceKindNo;
    set @R='Не е зададена цена за :'||"char"(13)||"char"(13)||'ценоразпис "'
    ||@Description||'"'||','||' за стая N: '
    ||@RoomNo||', ';
    select Description into @Description from HumanKinds where HumanKindNo=@HumanKind;
    set @R=@R||@Description||', ';
    select Description into @Description from AdultTypes where AdultNo=@AdultType;
    set @R=@R||@Description||'!';
    return(@R)
  end if
  ; //
  set @BookN=GetCounter('BookCounter');
  insert into Book(RegNum,DateReg,NumLodgings,PayedLodgings,Payed,Staying,PriceKind,
    KodAdmin,Room,Price,Guest,WhoPays,KodCurrency,IsRest) values(
    @BookN,@DateReg,@Days,0,0,1,@PriceKindNo,
    @KodAdmin,@RoomNo,@Price,@GuestN,@GuestN,@Currency,0);
  update Rooms set Buzy=2,NumPeople=NumPeople+1
    where RoomNum=@RoomNo;
  update Guests set Status=1,ResPriceKind=@PriceKindNo,IsEgnEnch=@IsEgnEnch,
    ReservePlanNo=if @NastKind=0 then @RN else ReservePlanNo endif
    where GuestNum=@GuestN;
  for f as curs scroll cursor for
    select ServiceNo as @ServiceNo,Price as @ServicePrice,KodCurrency as @ServiceCurrency
      from Prices where TariffNo=@TariffNo do
    message 'RegNo=',@BookN,'ServiceNo=',@ServiceNo;
    insert into Packages(RegNo,ServiceNo,Price,KodCurrency) values(
      @BookN,@ServiceNo,@ServicePrice,@ServiceCurrency) end for;
  if @NastKind=0 then
    update ReservePlan set Status=0,RoomNo=@RoomNo where N=@RN
  end if
  ;
  return(@R)
end
//[v 01.01.09]