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)
returns real --@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)
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(@DateArrive >= @DateLeave) then
return(0)
end if;
if((Month(@PeriodStart) = Month(@DateArrive)) and(Year(@PeriodStart) = Year(@DateArrive))) or((Month(@PeriodStart) = Month(@DateLeave)) and(Year(@PeriodStart) = Year(@DateLeave))) then
set @MonthIsWith30Days=(select MonthIsWith30Days from Hotels where Hotel_ID = (select IntStojnost from Registers where RegisterName = 'HotelID'))
else
set @MonthIsWith30Days=0
end if;
if(@Tip = 1) then
set @DateLeave=@DatePaidTo
end if;
if(@DateArrive > @PeriodEnd) then
return(0)
end if;
if(@DateLeave < @PeriodStart) then
return(0)
end if;
set @CalcStart=@DateArrive;
set @CalcEnd=@DateLeave-1;
if(@CalcStart <= @PeriodStart) then
set @CalcStart=@PeriodStart
end if;
if(@CalcEnd >= @PeriodEnd) then
set @CalcEnd=@PeriodEnd
end if;
set @Res=(cast(datediff(day,@CalcStart,@CalcEnd)+1 as real)/cast(DOM(MONTH(@PeriodStart),YEAR(@PeriodStart),@MonthIsWith30Days) as real));
if(MONTH(@PeriodStart) = 2) and(@MonthIsWith30Days = 1) then
if(cast(datediff(day,@CalcStart,@CalcEnd)+1 as real) = 28) then
set @Res=1
end if
end if;
if(@Res > 1) then
return(1)
else
return(@Res)
end if
end
--[v. 29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods
--[v. 04.09.2009] - Yoan & Milko - Ne se saobrazqvane kogato e posledniq den ot mesec ne vliza v spravkata
--[v. 16.09.2009] - Yoan - Bug FIX za parviq den v meseca