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

GO

create function
DBA.GetRealDatePayedRentForPeriod(in @RegNo integer, in @StartDate date, in @EndDate date)
returns date
begin
  for f as curs scroll cursor for
    select 
			Inv.InvoiceDateDt as @InvoiceDateDt 
		from
      Invoices as Inv,Articles as A 
		where
      A.InvoiceNo = Inv.InvoiceNo and
      A.RegNo = @RegNo and
      Inv.CancelInvoiceNo = 0 
		order by
      Inv.InvoiceNo asc 
	do
    if(@InvoiceDateDt>=@StartDate) and (@InvoiceDateDt<=@EndDate) then
      return(@InvoiceDateDt);
    end if;
	end for;
  return(null)
end

GO

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

GO

create procedure DBA.FillOwePaidRentMonth(in @Month date, in @DoDelete tinyint default 0)
--Spravka daljim i platen naem - mesec
--Izpolzva se ot FillOweRentForMonth()
begin
	declare @StartDateMonth date;
	declare @EndDateMonth date;
	declare @TempDate date;
	declare @NextSeasonStartDate date;
	set @StartDateMonth="date"('1.' || MONTH(@Month) || '.' || YEAR(@Month));
	set @EndDateMonth="date"('1.' || MONTH(@Month)+1 || '.' || YEAR(@Month))-1;
	
	if (@DoDelete=1) then
	  delete from #OwePaidRentMonth;
	end if;
	
	while (@StartDateMonth<@EndDateMonth) loop
	  select Max(StartDate) into @NextSeasonStartDate from PriceSeasonPeriods as PSP where PSP.N =((select Max(N) from PriceSeasonPeriods as PSP1 where((PSP1.StartDate <= @StartDateMonth) and(PSP1.EndDate >= @StartDateMonth)))+1);
		if(@NextSeasonStartDate=null) then
		  set @NextSeasonStartDate=@EndDateMonth;
		end if;
	  if(@NextSeasonStartDate < @EndDateMonth) then
		begin
		  insert into #OwePaidRentMonth(GuestNo, NameMonth, Year, Name, UniName, Kurs, Spec, FN, Address, City, 
																		Sex, PriceKind, Usluga,	Cena,	Kol, SumOwe,	KolPaid, SumPaid, Razlika, 
																		RealDatePaid,	Koef,	RoomName,	Country, DaysInMonth)
		  select B.Guest,
        DBA.ImeMesec(MONTH(@Month)),
        YEAR(@Month),
        G.Name,
        U.FullName,
        K.Name,
        S.Name,
        G.FN,
        G.Address,
        Settlements.SettleMentName,
        G.Sex,
        PK.Description,
        DBA.GetNoshtuvkaNameForPeriodInMonth(B.RegNum,@StartDateMonth,@NextSeasonStartDate-1),
        Cena=isNull(DBA.GetRentPriceForDate(B.RegNum,@StartDateMonth),B.Price*C.ExchangeRate),
        Kol=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@NextSeasonStartDate-1),2),
        SumOwe=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@NextSeasonStartDate-1)*Cena,2),
        KolPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@NextSeasonStartDate-1),2),
        SumPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@NextSeasonStartDate-1)*Cena,2),
        Razlika=SumOwe-SumPaid,
        DBA.GetRealDatePayedRentForPeriod(B.RegNum,@StartDateMonth,@NextSeasonStartDate-1),
        Kol,
        R.RoomName,
        Ctr.Description,
	      DBA.DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),@StartDateMonth,@NextSeasonStartDate-1)+1
  	  from
        DBA.Book as B,DBA.PriceKinds as PK,DBA.Guests as G,DBA.Kursove as K,DBA.Specialities as S,
        DBA.Universities as U,DBA.Currencies as C,DBA.Rooms as R,DBA.Countries as Ctr, DBA.Settlements 
	    where
        B.PriceKind = PK.PriceKind and
	      G.GuestNum = B.WhoPays and
	      PK.NachinPlat = 1 and
        B.DateRegDt < "date"('1.' || MONTH(@Month)+1 || '.' || YEAR(@Month)) and
        dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || MONTH(@Month) || '.' || YEAR(@Month)) and
        G.Kurs = K.N and
  	    G.SpecNo = S.N and
  	    G.UniN = U.N and
  	    B.KodCurrency = C.KodCurrency and
  	    R.RoomNum = B.Room and
  	    Settlements.N = G.SettlementN and
        G.NativeCountry = Ctr.CountryKod and
	      B.Staying <> 3 and
	      Kol <> 0.0;
		  set @StartDateMonth=@NextSeasonStartDate;
		end
	  else
	  begin
		  insert into #OwePaidRentMonth(GuestNo, NameMonth, Year, Name, UniName, Kurs, Spec, FN, Address, City, 
																		Sex, PriceKind, Usluga,	Cena,	Kol, SumOwe,	KolPaid, SumPaid, Razlika, 
																		RealDatePaid,	Koef,	RoomName,	Country, DaysInMonth) 
			select B.Guest,
        DBA.ImeMesec(MONTH(@Month)),
        YEAR(@Month),
        G.Name,
        U.FullName,
        K.Name,
        S.Name,
        G.FN,
        G.Address,
        Settlements.SettleMentName,
        G.Sex,
        PK.Description,
        DBA.GetNoshtuvkaNameForPeriodInMonth(B.RegNum,@StartDateMonth,@EndDateMonth),
        Cena=isNull(DBA.GetRentPriceForDate(B.RegNum,@StartDateMonth),B.Price*C.ExchangeRate),
        Kol=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@EndDateMonth),2),
        SumOwe=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,0,@StartDateMonth,@EndDateMonth)*Cena,2),
        KolPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@EndDateMonth),2),
        SumPaid=Round(DBA.GetQtyRentForPeriodInMonth(B.RegNum,1,@StartDateMonth,@EndDateMonth)*Cena,2),
        Razlika=SumOwe-SumPaid,
        DBA.GetRealDatePayedRentForPeriod(B.RegNum,@StartDateMonth,@EndDateMonth),
        Kol,
        R.RoomName,
        Ctr.Description,
	      DBA.DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),@StartDateMonth,@EndDateMonth)+1
  	  from
        DBA.Book as B,DBA.PriceKinds as PK,DBA.Guests as G,DBA.Kursove as K,DBA.Specialities as S,
        DBA.Universities as U,DBA.Currencies as C,DBA.Rooms as R,DBA.Countries as Ctr, DBA.Settlements 
	    where
        B.PriceKind = PK.PriceKind and
	      G.GuestNum = B.WhoPays and
	      PK.NachinPlat = 1 and
        B.DateRegDt < "date"('1.' || MONTH(@Month)+1 || '.' || YEAR(@Month)) and
        dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || MONTH(@Month) || '.' || YEAR(@Month)) and
        G.Kurs = K.N and
  	    G.SpecNo = S.N and
  	    G.UniN = U.N and
  	    B.KodCurrency = C.KodCurrency and
  	    R.RoomNum = B.Room and
  	    Settlements.N = G.SettlementN and
        G.NativeCountry = Ctr.CountryKod and
	      B.Staying <> 3 and
	      Kol <> 0.0;
			set @StartDateMonth=@EndDateMonth;
	  end;
		end if;
	end loop
end
--[29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods