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

GO

create function
DBA.GetRentPriceForDate(in @RegNo integer,in @DateReg date)
returns numeric(12,2)
begin
  declare @R numeric(12,2);
  declare @PriceKindNo integer;
  declare @SeasonNo integer;
  declare @ClassNo integer;
  declare @HumanKind integer;
  declare @AdultType integer;
  declare @FirstDateMonth integer;
  
  set @R= null;
  
  set @FirstDateMonth = dateadd(day,-datepart(day,@DateReg)+1,@DateReg);
  select min(SeassonNo) into @SeasonNo from PriceSeasonPeriods where StartDate <= @FirstDateMonth and EndDate >= @FirstDateMonth;
  if (@SeasonNo is not null) then 
    select B.PriceKind,R.RoomCat, G.HumanKind, G.AdultType  into @PriceKindNo, @ClassNo, @HumanKind, @AdultType from Book B, Guests G, Rooms R where B.RegNum=@RegNo and B.Guest = G.GuestNum and B.Room=R.RoomNum;
	
	select min(PL.PriceAll*C.ExchangeRate) into @R from PriceList PL, Currencies C 
	   where PL.ClassNo=@ClassNo and PL.PriceKindNo=@PriceKindNo and PL.HumanKind=@HumanKind and PL.AdultType=@AdultType 
	     and PL.SeasonNo=@SeasonNo and PL.KodCurrency=C.KodCurrency;
	   
  end if; // @SeasonNo is not null
  
  return (@R);
end

go

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

GO

create function
DBA.FillSingleArticleLodging(in @RegNo integer,in @DateReg date,in @NumDays integer,in @ArticlePrice numeric(12,2),in @PackagePrice numeric(12,2),in @ServiceName varchar(40),in @FillAll integer,in @TipDDS integer,in @place varchar(50))
returns integer
begin
  declare @R integer;
  declare @ArticleName varchar(100);
  declare @DateStart date;
  declare @DateEnd date;
  declare @IsFullMonth integer;
  declare @RealLodgings integer;
  declare @DaysInMonth integer;
  declare @ArtPrice numeric(12,2);
  declare @Qty integer;
  declare @ZIndex integer;
  declare @RoomName varchar(6);
  declare @YearService varchar(4);
  declare @ServiceNoLodging integer;
  declare @ServiceTipDDS integer;
  
  set @IsFullMonth=1;
 
  set @ArtPrice=GetRentPriceForDate(@RegNo,@DateReg);
  if (@ArtPrice is null) then 
    set @ArtPrice=@ArticlePrice;
	set @ServiceTipDDS = @TipDDS;
  else 
    select V.TipDDS into @ServiceTipDDS from Book B, PriceKinds PK, Services as S,VidSdelki as V where B.RegNum=@RegNo and B.PriceKind=PK.PriceKind and S.ServiceNo = PK.ServiceNo and S.TipSdelka = V.N;
    if @ServiceTipDDS = 2 then
      set @ArticlePrice=@ArticlePrice*(bvrDDSPart+1); //ako dds-to e vkliucheno v cenata
      set @ServiceTipDDS=1
    end if;
    set @ArticlePrice=@ArticlePrice-@PackagePrice;
    if @ServiceTipDDS = 1 then
      set @ArticlePrice=@ArticlePrice/(bvrDDSPart+1); //ako dds-to ne e vkliucheno v cenata
      set @ServiceTipDDS=2
    end if;
  end if;
  
  select R.RoomName into @RoomName from Book as B,Rooms as R where B.RegNum = @RegNo and B.Room = R.RoomNum;
  // setva nachalnata data
  set @DateStart=@DateReg;
  if day(@DateStart) <> 1 then
    set @IsFullMonth=0
  end if;
  // setva krajnata data
  set @DateEnd=dateadd(day,-day(@DateReg)+1,@DateReg); // setva DateEnd na purviat den ot meseca na nachalnata data
  set @DateEnd=dateadd(month,1,@DateEnd); // dobavia 1 mesec za da stane purviat den ot seldvashtiat mesec na nachalnata data
  if @DateEnd > dateadd(day,@NumDays,@DateReg) then
    set @DateEnd=dateadd(day,@NumDays,@DateReg);
    set @IsFullMonth=0
  end if;
  //izchisliava broiat dni
  set @RealLodgings=datediff(day,@DateStart,@DateEnd);
  // setva imeto na uslugata
  set @YearService='' || datepart(year,@DateStart);
  set @YearService="right"(@YearService,2);
  set @ArticleName=@ServiceName || ' ' || ImeMesec(month(@DateStart)) || '''' || @YearService || @place || @RoomName;
  if(@IsFullMonth = 0) then
    set @DaysInMonth=day(dateadd(day,-day(@DateStart),dateadd(month,1,@DateStart)));
    set @ArticleName=@ArticleName || ' ' || @RealLodgings || '/' || @DaysInMonth;
    set @ArtPrice=(@ArtPrice*@RealLodgings)/@DaysInMonth
  end if;
  // izchisliava dali da e po podrazbirane izbrana uslugata za plashtane ili ne
  set @Qty=1;
  if(@FillAll = 0) then
    if(month(@DateStart) > month(now(*)) and year(@DateStart) = year(now(*))) or(year(@DateStart) > year(now(*))) then
      set @Qty=0
    end if
  end if;
  // vkarva ZIndex za da ne moje naprimer da se plati naem za oktomvri predi da e
  // platen naema za septemvri - tova se pravi vuv Bills formata
  select Max(ZIndex)+1 into @ZIndex from #ArticleLodgings;
  if @ZIndex is null then
    set @ZIndex=1
  end if;
  // vkarva uslugata
  insert into #ArticleLodgings( RegNum,Name,RealLodgingsNum,Price,Qty,MaxQty,MinQty,ZIndex,TipDDS) values( 
    @RegNo,@ArticleName,@RealLodgings,@ArtPrice,@Qty,1,0,@ZIndex,@ServiceTipDDS);
  // namalia @NumDays sus realnite noshtuvki i vrushta rezultata
  set @R=@NumDays-@RealLodgings;
  return(@R)
end
// [20.02.2009] - Nixon - dobavena godinata kum imeto na smetkata