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

GO

create procedure DBA.FillOwePaidRentMonth( @Month date,@DoDelete tinyint= 0 ) 
--Spravka daljim i platen naem - mesec
as --Izpolzva se ot FillOweRentForMonth()
begin
  declare @StartDateMonth date,
  @EndDateMonth date,
  @TempDate date,
  @NextSeasonStartDate date,
  @Normativ double
  set @StartDateMonth = "date"('1.' || MONTH(@Month) || '.' || YEAR(@Month))
  set @EndDateMonth = dateadd(day,-1,dateadd(month,1,@StartDateMonth))
  --message(@StartDateMonth)
  --message(@EndDateMonth)
  set @Normativ = (select isNull(Normativ,0) from SubsidyNormativ where PeriodStart <= @StartDateMonth and PeriodEnd >= @StartDateMonth)
  if(@DoDelete = 1)
    delete from #OwePaidRentMonth
  while(@StartDateMonth <= @EndDateMonth)
    begin
      select @NextSeasonStartDate = Max(StartDate) 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 is null)
        set @NextSeasonStartDate = @EndDateMonth
      --message(@NextSeasonStartDate)
      if(@NextSeasonStartDate < @EndDateMonth)
        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,PriceKindType,PayType,PayTypeStr,Subsidy ) 
            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,
              DaysInMonth=DBA.DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),@StartDateMonth,@NextSeasonStartDate-1)+1,
              PriceKindType=PKT.Name,
              PType=GetPayTypePayedRentForPeriod(B.RegNum,@StartDateMonth,@NextSeasonStartDate-1),
              PayTypeStr=(select PayTypeName from PayTypes where PayTypeN = PType),
              Subsidy=(if SumPaid <> 0 then DaysInMonth*@Normativ else 0 endif)
              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
                ,DBA.PriceKindTypes as PKT
              where B.PriceKind = PK.PriceKind
              and G.GuestNum = B.WhoPays
              and PK.NachinPlat = 1
              and B.DateRegDt < dateadd(month,1,"date"('1.' || MONTH(@Month) || '.' || 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
              --              B.Staying <> 3 and
              and Kol <> 0.0
              and PK.PriceKindType = PKT.N
          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,PriceKindType,PayType,PayTypeStr,Subsidy ) 
            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,
              DaysInMonth=DBA.DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),@StartDateMonth,@EndDateMonth)+1,
              PriceKindType=PKT.Name,
              PType=GetPayTypePayedRentForPeriod(B.RegNum,@StartDateMonth,@EndDateMonth),
              PayTypeStr=(select PayTypeName from PayTypes where PayTypeN = PType),
              Subsidy=(if SumPaid <> 0 then DaysInMonth*@Normativ else 0 endif)
              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
                ,DBA.PriceKindTypes as PKT
              where B.PriceKind = PK.PriceKind
              and G.GuestNum = B.WhoPays
              and PK.NachinPlat = 1
              and B.DateRegDt < dateadd(month,1,"date"('1.' || MONTH(@Month) || '.' || 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
              --              B.Staying <> 3 and
              and Kol <> 0.0
              and PK.PriceKindType = PKT.N
          set @StartDateMonth = dateadd(day,1,@EndDateMonth)
        end
    end
end
--[29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods
--[04.09.2009] - Yoan - sabrazqva se ako studenta e nastanen v posledniq den ot mesec
--[16.09.2009] - Yoan & Milko - zakomentareno B.Stayin <> 3
--[23.10.2009] - Milko - Dobavena nova kolona PriceKindType
