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

GO

create function
DBA.GetQtyRentForPeriodInMonth(in @RegNo integer, in @Tip tinyint, in @PeriodStart date, in @PeriodEnd date) 
--@Tip 0-cqloto kolichestvo, 1-platenoto kolichestvo
--Izchislqva koficienta na no6tuvki v zadaden period (plateni ili vsi4ki) ot celiq mesec (broi noshtuvki v perioda v meseca)/(broi dni v meseca)
returns real
begin
  declare @Res real;
  declare @DateArrive date;
  declare @DateLeave date;
  declare @DatePaidTo date;
  declare @MonthIsWith30Days integer;
	declare @CalcStart date;
	declare @CalcEnd date;
	
	if (@PeriodEnd<=@PeriodStart) then
	  return (0);
	end if;
	
	if ((MONTH(@PeriodStart) <> MONTH(@PeriodEnd)) or ((YEAR(@PeriodStart) <> YEAR(@PeriodEnd)))) then
	  return (0);
	end if;
	
  select 
	  FloatToDate(DateReg), 
	  FloatToDate(DateReg+NumLodgings), 
		FloatToDate(DateReg+PayedLodgings) 
	into @DateArrive, @DateLeave, @DatePaidTo
	from 
	  Book 
	where 
	  Book.RegNum = @RegNo;
	
	if (@Tip=1) then
	  set @DateLeave=@DatePaidTo;
	end if;
	
  if (@DateArrive >= @DateLeave) then 
	  return(0);
  end if;
	
  set @MonthIsWith30Days = (select MonthIsWith30Days from Hotels where Hotel_ID = (select IntStojnost from Registers where RegisterName = 'HotelID'));
	
	if (@DateArrive>=@PeriodEnd) then
	  return(0);
	end if;
	
	if (@DateLeave<=@PeriodStart) then
	  return(0);
	end if;
	
	set @CalcStart=@DateArrive;
	set @CalcEnd=@DateLeave;
	
	if (@DateArrive<=@PeriodStart) then
	  set @CalcStart=@PeriodStart;
	end if;
	
	if (@DateLeave>=@PeriodEnd) then
	  set @CalcEnd=@PeriodEnd;
	end if;
	
	set @Res = (cast(DAY(@CalcEnd)-DAY(@CalcStart)+1 as real)/cast(DOM(MONTH(@PeriodStart),YEAR(@PeriodStart),@MonthIsWith30Days) as real));
	if (@Res>1) then
	  return(1);
	else 
	  return(@Res);
	end if;
end
--[v. 29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods