IF (EXISTS(Select * from sys.systable where LCase(table_name) = LCase('v_OwePaidLodgMonth')))
THEN Drop view v_OwePaidLodgMonth
END IF

GO

create view DBA.v_OwePaidLodgMonth
  as select PSP.SeassonNo,
    PSP.StartDate,
    PSP.EndDate,
    GNo=B.Guest,
    Month=DBA.ImeMesec(bvrSpravkaM),
    Year=bvrSpravkaY,
    G.Name,
    Uni=U.FullName,
    Kurs=K.Name,
    Spec=S.Name,
    G.FN,
    G.Address,
    City=(select Settlements.SettleMentName from DBA.Settlements where Settlements.N = G.SettlementN),
    G.Sex,
    PriceKind=PK.Description,
    Usluga=DBA.GetNoshtuvkaNameForMonthBook(B.RegNum,1),
    Cena=DBA.GetRentPriceForDate(B.RegNum,null,PSP.SeassonNo),
    Kol=DBA.GetQtyLodgForMonth(B.RegNum,B.NumLodgings,PSP.StartDate,PSP.EndDate),
    SumOwe=Round(Kol*Cena,2),
    KolPaid=DBA.GetQtyLodgForMonth(B.RegNum,B.PayedLodgings,PSP.StartDate,PSP.EndDate),
    SumPaid=Round(KolPaid*Cena,2),
    Razlika=SumOwe-SumPaid,
	RealDatePayed=DBA.GetRealDatePayedLodgForMonth(B.RegNum),
    //RealDatePayed=DBA.GetRealDatePayedLodgForMonth(B.RegNum,if MONTH(PSP.StartDate) = bvrSpravkaM then DAY(PSP.StartDate)-1 else 0 endif
    //+Kol),
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Countries.Description,
    DaysInMonth=Kol,
    PayType=DBA.GetPayTypePayedRentForMonth(B.RegNum),
    PayTypeStr=(select PayTypes.PayTypeName from DBA.PayTypes where PayTypes.PayTypeN = PayType)
    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,DBA.PriceSeasonPeriods as PSP
    where B.PriceKind = PK.PriceKind
    and G.GuestNum = B.Guest
    and PK.NachinPlat = 0
    and G.Kurs = K.N
    and G.SpecNo = S.N
    and(MONTH(PSP.StartDate)+12*YEAR(PSP.StartDate)) <= (bvrSpravkaM+12*bvrSpravkaY)
    and(MONTH(PSP.EndDate)+12*YEAR(PSP.EndDate)) >= (bvrSpravkaM+12*bvrSpravkaY)
    and G.UniN = U.N
    and B.KodCurrency = C.KodCurrency
    and R.RoomNum = B.Room
    and G.NativeCountry = Countries.CountryKod
    and B.DateRegDt < dateadd(month,1,"date"('01.' || bvrSpravkaM || '.' || bvrSpravkaY)) --v.1
    and dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('01.' || bvrSpravkaM || '.' || bvrSpravkaY) and Kol <> 0 // v.1
// v.1 [07.02.2012] - Milko - Fixed DateFormat