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

GO

create view
  DBA.v_OwePaidRentMonth
  as select GNo=Guest,
    Month=DBA.ImeMesec(bvrSpravkaM),
    Year=bvrSpravkaY,
    G.Name,
    Uni=U.FullName,
    Kurs=K.Name,
    Spec=S.Name,
    G.FN,
    G.Address,
    City=(select SettleMentName from DBA.Settlements where N = G.SettlementN),
    G.Sex,
    PriceKind=PK.Description,
    Usluga=DBA.GetNoshtuvkaNameForMonthBook(B.RegNum),
    Cena=isNull(DBA.GetRentPriceForDate(B.RegNum,'01.' || bvrSpravkaM || '.' || bvrSpravkaY),B.Price*C.ExchangeRate),
    Kol=Round(DBA.GetQtyRentForMonth(B.RegNum,0),2),
    SumOwe=Round(DBA.GetQtyRentForMonth(B.RegNum,0)*Cena,2),
    KolPaid=Round(DBA.GetQtyRentForMonth(B.RegNum,1),2),
    SumPaid=Round(DBA.GetQtyRentForMonth(B.RegNum,1)*Cena,2),
    Razlika=SumOwe-SumPaid,
    RealDatePayed=DBA.GetRealDatePayedRentForMonth(B.RegNum),
    Koef=Kol,
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Ctr.Description,
	BeginDateMonth="date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY),
	EndDateMonth=dateadd(day,-1,dateadd(month,1,BeginDateMonth)),
	DaysInMonth=DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),BeginDateMonth,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 where
    B.PriceKind = PK.PriceKind and G.GuestNum = B.Guest and PK.NachinPlat = 1 and
    B.DateRegDt < dateadd(month,1,"date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)) and
    dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY) 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
    G.NativeCountry = Ctr.CountryKod
//[07.04.2009] - Nixon - dobavena kolona DaysInMonth - broj dni v meseca prez koito studenta e bil v obshtejitieto
go

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

GO

create procedure
DBA.FillOweRentForMonth()
begin
  set bvrSpravkaM=bvrSpravkaM1;
  set bvrSpravkaY=bvrSpravkaY1;
  delete from #OwePaidRentForMonth;
  while((bvrSpravkaY < bvrSpravkaY2) or((bvrSpravkaY = bvrSpravkaY2) and(bvrSpravkaM <= bvrSpravkaM2))) loop
    insert into #OwePaidRentForMonth(
      select GNo,Month,DaysInMonth,Year,Name,Uni,Kurs,Spec,FN,Address,City,Sex,PriceKind,Usluga,Cena,Kol,SumOwe,KolPaid,SumPaid,
        Razlika,RealDatePayed,Koef,BrStudenti,RName,CountryName from
        v_OwePaidRentMonth);
    if(bvrSpravkaM >= 12) then
      set bvrSpravkaM=1;
      set bvrSpravkaY=bvrSpravkaY+1
    else
      set bvrSpravkaM=bvrSpravkaM+1
    end if
  end loop
end
//[07.04.2009] - Nixon - promeneno DaysInMonth - broj dni v meseca prez koito studenta e bil v obshtejitieto
go

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 GNo=Guest,
    Month=DBA.ImeMesec(bvrSpravkaM),
    Year=bvrSpravkaY,
    G.Name,
    Uni=U.FullName,
    Kurs=K.Name,
    Spec=S.Name,
    G.FN,
    G.Address,
    City=(select SettleMentName from DBA.Settlements where N = G.SettlementN),
    G.Sex,
    PriceKind=PK.Description,
    Usluga=DBA.GetNoshtuvkaNameForMonthBook(B.RegNum,1),
    Cena=B.Price*C.ExchangeRate,
    Kol=DBA.GetQtyLodgForMonth(B.RegNum,B.NumLodgings),
    SumOwe=Round(Kol*Cena,2),
    KolPaid=DBA.GetQtyLodgForMonth(B.RegNum,B.PayedLodgings),
    SumPaid=Round(KolPaid*Cena,2),
    Razlika=SumOwe-SumPaid,
    RealDatePayed=DBA.GetRealDatePayedLodgForMonth(B.RegNum),
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Countries.Description,
	BeginDateMonth="date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY),
	EndDateMonth=dateadd(day,-1,dateadd(month,1,BeginDateMonth)),
	DaysInMonth=DateIntersection(B.DateRegDt,dateadd(day,B.NumLodgings-1,B.DateRegDt),BeginDateMonth,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 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
    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"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)) and
    dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY)
//[07.04.2009] - Nixon - dobavena kolona DaysInMonth - broj dni v meseca prez koito studenta e bil v obshtejitieto
go

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

GO

create procedure
DBA.FillOweRentLodg()
begin
  set bvrSpravkaM=bvrSpravkaM1;
  set bvrSpravkaY=bvrSpravkaY1;
  delete from #OwePaidRentLodg;
  while((bvrSpravkaY < bvrSpravkaY2) or((bvrSpravkaY = bvrSpravkaY2) and(bvrSpravkaM <= bvrSpravkaM2))) loop
    insert into #OwePaidRentLodg(
      select GNo,Month,DaysInMonth,Year,Name,Uni,Kurs,Spec,FN,Address,City,Sex,PriceKind,Usluga,Cena,Kol,SumOwe,KolPaid,SumPaid,
        Razlika,RealDatePayed,BrStudenti,RName,CountryName from
        v_OwePaidLodgMonth);
    if(bvrSpravkaM >= 12) then
      set bvrSpravkaM=1;
      set bvrSpravkaY=bvrSpravkaY+1
    else
      set bvrSpravkaM=bvrSpravkaM+1
    end if
  end loop
end
//[07.04.2009] - Nixon - promeneno DaysInMonth - broj dni v meseca prez koito studenta e bil v obshtejitieto
go

update Relations set
  BGColName='Äíè',
  "Function"='sum(DaysInMonth)',
  "Suma"='sum(qrData.Äíè)'
where
  ColName='DaysInMonth'
  and (TableName='#OwePaidRentForMonth' or TableName='#OwePaidRentLodg')
go