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

GO

create function
DBA.DOM(in @mnth integer,in @yr integer,in @Return30 smallint default 0)
returns integer
begin
  if @Return30 = 0 then
    if @mnth in( 1,3,5,7,8,10,12) then return(31)
    else
      if @mnth in( 4,6,9,11) then return(30)
      else
        if(MOD(@yr,4) = 0) or MOD(@yr,100) <> 0 //and(MOD(@yr,400) = 0) 
					  	  	 			  	 	then return(29)
        else
          return(28)
        end if
      end if
    end if
  else
    return(30)
  end if
end

//06.02.2012 - Milko fixed za 29 February

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 SeassonNo,
    StartDate,
    EndDate,
    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=DBA.GetRentPriceForDate(B.RegNum,null,SeassonNo),
    Kol=DBA.GetQtyLodgForMonth(B.RegNum,B.NumLodgings,StartDate,EndDate),
    SumOwe=Round(Kol*Cena,2),
    KolPaid=DBA.GetQtyLodgForMonth(B.RegNum,B.PayedLodgings,StartDate,EndDate),
    SumPaid=Round(KolPaid*Cena,2),
    Razlika=SumOwe-SumPaid,
    RealDatePayed=DBA.GetRealDatePayedLodgForMonth(B.RegNum,if MONTH(StartDate) = bvrSpravkaM then DAY(StartDate)-1 else 0 endif+Kol),
    BrStudenti=1,
    RName=R.RoomName,
    CountryName=Countries.Description,
    DaysInMonth=Kol 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)) and //v.1
    dateadd(day,B.NumLodgings,B.DateRegDt) > "date"('01.' || bvrSpravkaM || '.' || bvrSpravkaY) and Kol <> 0 // v.1
	
	// v.1 [07.02.2012] - Milko - Fixed DateFormat