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

GO

create view DBA.v_PayedAdditionalServices
  as select Service=Articles.ArticleName,
    PayType=PayTypes.PayTypeName,
    Account=ServiceCredits.GuestNo,
    AllSum= (if (Articles.TipDDS=2) then 
	          Sum(Round(Articles.TotalSum*(1+bvrDDSPart),2))
			else if (Articles.TipDDS=3) then 
			  Sum(Round(Articles.TotalSum,2))
			endif endif),
    Operator=Max(Admin.ShortName) 
	from
    dba.PayTypes,dba.ResMoney,dba.Admin,DBA.Articles,DBA.ServiceCredits 
	where
    ResMoney.DateMoneyDt >= bvrData1 and 
	ResMoney.DateMoneyDt <= bvrData2 and 
	ResMoney.Payment = PayTypes.PayTypeN and
    ResMoney.KodAdmin = Admin.AdminNo and 
	ResMoney.InvoiceNo = Articles.InvoiceNo and
    Articles.RegNo = 0 and 
	Articles.CreditNo = ServiceCredits.CreditNo
    group by 
	Articles.ArticleName,ServiceCredits.GuestNo,PayTypes.PayTypeName, Articles.TipDDS
	
GO

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

GO

create view
  DBA.v_WaitingBills as
  select G.GuestNum,
    Season=0,
    GuestName=G.Name,
    Qty=(SC.NumberServices-SC.PayedServices),
    Prc=0.0,
    Price=string(SC.SumCredit,' ',CrS.Symbol),
    PriceLv=SC.SumCredit*CrS.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo = ASL.LastRoom,
    RoomNo=ASL.LastRoomName,
    --Maria
    S.ServiceName,
    G.Note,
    DateService=SC.DateCreditDt from
    dba.Guests as G,dba.ServiceCredits as SC,dba.Currencies as CrS,
    dba.v_ArriveStayLeaveGuestBook as ASL,dba.Services as S where
    SC.WhoPays = G.GuestNum and
    SC.WhoPays = ASL.GuestNum and
    SC.KodCurrency = CrS.KodCurrency and
    SC.NumberServices <> SC.PayedServices and
    SC.Service = S.ServiceNo and
    G.ServiceSum <> 0 and
    G.Status = 2 and
    Balance <> 0 and(
    (SC.SPaReservationFk = -1) or(SC.SPaReservationFk <> -1 and SC.SpaTransferFlag = 1)) union all
  /* Yanko +*/
  select GuestNum=B.WhoPays,
    Season=Ps.SeasonNo,
    GuestName=G.Name,
    Qty=Round(DBA.GetMonthKol(B.RegNum,Season),3),
    Prc=DBA.GetPriceForBook(B.RegNum,B.PriceKind,Season),
    Price=string(Prc,' ',Crs.Symbol),
    PriceLv=Prc*Crs.ExchangeRate,
    Total=PriceLv*Qty,
    --Maria
    --RoomNo=B.Room,
    RoomNo=R.RoomName,ServiceName='НАЕМ (' || Lower(Ps.Description) || ' сезон)',
    --Maria
    G.Note,
    DateService=B.DateRegDt from DBA.PriceSeasons as Ps,
    dba.Book as B,dba.Guests as G,dba.Currencies as CrS,DBA.Rooms as R where /*Maria*/ /*Maria*/
    --Maria
    B.Room = R.RoomNum and
    --Maria
    B.WhoPays = G.GuestNum and
    B.KodCurrency = CrS.KodCurrency and
    B.PayedLodgings <> B.NumLodgings and
    B.Price <> 0 and
    G.Status = 2 and
    B.WhoPays <> 0 and
    Qty <> 0.0 union all
  /* Yanko -*/
  select GuestNum=RM.GuestNo,
    Season=0,
    GuestName=G.Name,
    Qty=(-1),
    Prc=0.0,
    Price=string(RM.Sum,' ',C.Symbol),
    PriceLv=RM.Sum*C.ExchangeRate,
    Total=Qty*PriceLv,RoomNo=ASL.LastRoomName,ServiceName='Депозит',
    G.Note,
    DateService=RM.DateMoneyDt from
    dba.ResMoney as RM,dba.Guests as G,dba.Currencies as C, dba.v_ArriveStayLeaveGuestBook as ASL where
    RM.Status = 2 and
    RM.InvoiceNo = any(select InvoiceNo from dba.Invoices where CancelInvoiceNo = 0) and
    RM.GuestNo <> 0 and
    RM.DepositNo > 0 and
    G.GuestNum = RM.GuestNo and
    G.Balance <> 0 and
    G.Status = 2 and
    C.KodCurrency = RM.Currency and 
	RM.GuestNo = ASL.GuestNum
	
GO

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

GO

create function
DBA.GetMonthKol(in @RegNo integer,in @SeasonNo smallint default 0)
returns real
begin
  declare DateA date;
  declare DateL date;
  declare rez real;
  declare MonthsDiff integer;
  declare @MonthIsWith30Days integer;
  set @MonthIsWith30Days = (select MonthIsWith30Days from Hotels where Hotel_ID=(select IntStojnost from Registers where RegisterName='HotelID'));
  set rez=0.0;
  set DateA=(select FloatToDate(DateReg+PayedLodgings) from Book where RegNum = @RegNo);
  set DateL=(select FloatToDate(DateReg+NumLodgings) from Book where RegNum = @RegNo);
  if @SeasonNo <> 0 then
    for f1 as curs scroll cursor for
      select StartDate,EndDate from PriceSeasonPeriods where N=(select N from PriceSeasonPeriods where DateA>=StartDate and DateL<=EndDate) and SeassonNo = @SeasonNo do
      set DateA=(select FloatToDate(DateReg+PayedLodgings) from Book where RegNum = @RegNo);
      if StartDate > DateA then set DateA=StartDate
      end if;
      set DateL=(select FloatToDate(DateReg+NumLodgings) from Book where RegNum = @RegNo);
      if EndDate < DateL then set DateL=EndDate
      end if;
      set Monthsdiff=YEAR(DateL)*12+MONTH(DateL)-YEAR(DateA)*12-MONTH(DateA)-1;
      if(MONTH(DateA) = MONTH(DateL)) and(YEAR(DateA) = YEAR(DateL)) then
        set rez=cast(DAY(DateL)-DAY(DateA) as real)/cast(DOM(MONTH(DateL),YEAR(DateL),@MonthIsWith30Days) as real)
      else
        set rez=cast((DOM(MONTH(DateA),YEAR(DateA),@MonthIsWith30Days)-DAY(DateA)+1) as real)/cast(DOM(MONTH(DateA),YEAR(DateA),@MonthIsWith30Days) as real)+cast(DAY(DateL)-1 as real)/cast(DOM(MONTH(DateL),YEAR(DateL),@MonthIsWith30Days) as real)
      end if;
      if Monthsdiff > 0 then set rez=rez+Monthsdiff
      end if end for
  else
    set Monthsdiff=YEAR(DateL)*12+MONTH(DateL)-YEAR(DateA)*12-MONTH(DateA)-1;
    if(MONTH(DateA) = MONTH(DateL)) and(YEAR(DateA) = YEAR(DateL)) then
      set rez=cast(DAY(DateL)-DAY(DateA) as real)/cast(DOM(MONTH(DateL),YEAR(DateL),@MonthIsWith30Days) as real)
    else
      set rez=cast((DOM(MONTH(DateA),YEAR(DateA),@MonthIsWith30Days)-DAY(DateA)+1) as real)/cast(DOM(MONTH(DateA),YEAR(DateA),@MonthIsWith30Days) as real)+cast(DAY(DateL)-1 as real)/cast(DOM(MONTH(DateL),YEAR(DateL),@MonthIsWith30Days) as real)
    end if;
    if Monthsdiff > 0 then set rez=rez+Monthsdiff
    end if
  end if;
  if rez < 0 then set rez=0.0
  end if;
  return rez
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 < @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.GetRealDatePayedRentForMonth(B.RegNum),
        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.GetRealDatePayedRentForMonth(B.RegNum),
        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

GO

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

GO

create procedure DBA.FillOweRentForMonth()
--Spravka Daljim i platen naem
begin
  set bvrSpravkaM=bvrSpravkaM1;
  set bvrSpravkaY=bvrSpravkaY1;
  delete from #OwePaidRentForMonth;
	delete from #OwePaidRentMonth;
  while((bvrSpravkaY < bvrSpravkaY2) or((bvrSpravkaY = bvrSpravkaY2) and(bvrSpravkaM <= bvrSpravkaM2))) loop
	  call FillOwePaidRentMonth("date"('1.' || bvrSpravkaM || '.' || bvrSpravkaY),0);
    if(bvrSpravkaM >= 12) then
      set bvrSpravkaM=1;
      set bvrSpravkaY=bvrSpravkaY+1
    else
      set bvrSpravkaM=bvrSpravkaM+1
    end if
  end loop;
	insert into #OwePaidRentForMonth(
	select GuestNo, NameMonth, DaysInMonth, Year, Name, UniName, Kurs, Spec, FN, Address, City, 
																		Sex, PriceKind, Usluga,	Cena,	Kol, SumOwe,	KolPaid, SumPaid, Razlika, 
																		RealDatePaid,	Koef,	1, RoomName,	Country from
        #OwePaidRentMonth);
end
--[07.04.2009] - Nixon - promeneno DaysInMonth - broj dni v meseca prez koito studenta e bil v obshtejitieto
--[v. 29.07.2009] - Yoan - promeneno da vzema ot vremenna tablica #OwePaidRentMonth

GO

Delete from Relations where lcase(RelationTable) = lcase('Monthly rent information')

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','__OPTIONS__','Дължим и платен наем - месец','__OPTIONS__','Monthly rent information','__OPTIONS',1,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','call FillOwePaidRentMonth("date"(''1.'' || bvrSpravkaM || ''.'' || bvrSpravkaY),1)',1,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','bvrSpravkaY','Дължим и платен наем - месец','Година','Monthly rent information','Year',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'bvrSpravkaY','','',0,0,1,1,'','select YEAR(today(*))',20,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','bvrSpravkaM','Дължим и платен наем - месец','Месец','Monthly rent information','Month',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'bvrSpravkaM','','',0,0,1,1,'','select MONTH(today(*))',20,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Name','Дължим и платен наем - месец','Студент','Monthly rent information','Name',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Name','','',0,0,'','','','',120,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','UniName','Дължим и платен наем - месец','Университет','Monthly rent information','UniName',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Uni','','',0,0,'','','select FullName from Universities','',200,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Spec','Дължим и платен наем - месец','Специалност','Monthly rent information','Spec',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Spec','','',0,0,'','','select Name from Specialities','',200,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Kurs','Дължим и платен наем - месец','Курс','Monthly rent information','Kurs',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Kurs','','',0,0,'','','select Name from Kursove','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','FN','Дължим и платен наем - месец','ФН','Monthly rent information','FN',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'FN','','',0,0,'','','','',50,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','City','Дължим и платен наем - месец','Град','Monthly rent information','City',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'City','','',0,0,'','','','',120,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Address','Дължим и платен наем - месец','Адрес','Monthly rent information','Address',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Address','','',0,0,'','','','',150,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Country','Дължим и платен наем - месец','Държава','Monthly rent information','Country',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'CountryName','','',0,0,'','','select Description from Countries','',150,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Sex','Дължим и платен наем - месец','Пол','Monthly rent information','Sex',0,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sex','','',0,0,'','','','',30,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','PriceKind','Дължим и платен наем - месец','Ценоразпис','Monthly rent information','PriceKind',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'PriceKind','','',0,0,'','','select Description from PriceKinds','',120,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Usluga','Дължим и платен наем - месец','Услуга','Monthly rent information','Usluga',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Usluga','','',0,0,'','','select ServiceName from Services','',150,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Kol','Дължим и платен наем - месец','Количество','Monthly rent information','Kol',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(Kol)','Sum(qrData.Количество)','',0,0,'','','','',80,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Cena','Дължим и платен наем - месец','Цена','Monthly rent information','Cena',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Cena','','',0,0,'','','','',80,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','SumOwe','Дължим и платен наем - месец','Дължима Сума','Monthly rent information','SumOwe',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(SumOwe)','Sum(qrData.Дължима Сума)','',0,0,'','','','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','SumPaid','Дължим и платен наем - месец','Платена Сума','Monthly rent information','SumPaid',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(SumPaid)','Sum(qrData.Платена Сума)','',0,0,'','','','',100,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Razlika','Дължим и платен наем - месец','Разлика','Monthly rent information','Razlika',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(Razlika)','Sum(qrData.Разлика)','',0,0,'','','','',80,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','RoomName','Дължим и платен наем - месец','Стая','Monthly rent information','RoomName',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'RName','','',0,0,'','','','',60,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','Koef','Дължим и платен наем - месец','Коефициент','Monthly rent information','Koef',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'Sum(Koef)','Sum(qrData.Кофициент)','',0,0,'','','','',90,'',0,0,1,today())

Go

INSERT INTO Relations(TableName,ColName,BGTableName,BGColName,RelationTable,RelationCol,SelectCol,N,Function,Suma,Mask,Align,Sortirane,Zadaljitelna,Promenliva,FilterList,DftFilter,Shirina,Opisanie,DftLike,AutoStretch,Spravka,PoslData)
VALUES('#OwePaidRentMonth','RealDatePaid','Дължим и платен наем - месец','Дата плащане','Monthly rent information','RealDatePaid',2,GetFreeNInRelations('Monthly rent information', Null, Null, 10),'RealDatePayed','','',0,0,'','','','',80,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='#OwePaidRentMonth'and ColName='__OPTIONS__')

Go

f1: for f1 as curs scroll cursor for select grNo=N from AdminGroups do
  if grNo <> 0 then
    insert into InformPermission(N,RelationN,AdminGroupNo,Stoinost)
    values((select MAX(N+1) from InformPermission),(select N from Relations where TableName='#OwePaidRentMonth'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;

GO

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

GO

create function
DBA.GetNoshtuvkaNameForPeriodInMonth(in @RegNo integer,in @PeriodStart date, in @PeriodEnd date, in @IsLodg integer default 0)
--@IsLodg 0-uslugata e za mesec,1-uslugata e za noshtuvka
--Resultata e palen string s ime na noshtvkata vkliuchvasht mesec, staq i broi dni
returns varchar(60)
begin
  declare @Res varchar(60);
  declare @ServiceName varchar(40);
  declare @RoomName varchar(6);
  declare @DaysInMonth varchar(6);
  declare @KoefDaysInMonth real;
  set @KoefDaysInMonth=GetQtyRentForPeriodInMonth(@RegNo,0,@PeriodStart,@PeriodEnd);
  if @KoefDaysInMonth <> 1 then
    set @DaysInMonth=' ' || Round(@KoefDaysInMonth*DOM(MONTH(@PeriodStart),YEAR(@PeriodStart)),0) || '/' || DOM(MONTH(@PeriodStart),YEAR(@PeriodStart))
  end if;
  select S.ServiceName,R.RoomName into @ServiceName,@RoomName from Services as S,PriceKinds as PK,Book as B,Rooms as R where
    B.RegNum = @RegNo and B.PriceKind = PK.PriceKind and PK.ServiceNo = S.ServiceNo and R.RoomNum = B.Room;
  if @IsLodg = 1 then
    if(select IsRest from Book where RegNum = @RegNo) = 1 then
      set @ServiceName='Почивка ';
    else
      set @ServiceName='Нощувка ';
    end if;
  end if;
  if @IsLodg = 0 then 
	  set @Res=@ServiceName || ' ' || ImeMesec(MONTH(@PeriodStart)) || ' в стая ' || @RoomName || @DaysInMonth;
  else 
	  set @Res=@ServiceName || ' в стая ' || @RoomName;
  end if;
  return(@Res)
end
--[29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods

GO

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

GO

create function
DBA.GetQtyRentForPeriodInMonth(in @RegNo integer, in @Tip tinyint, in @PeriodStart date, in @PeriodEnd date) 
--@Tip 0-cqloto kolichestvo, 1-platenoto kolichestvo
--Izchislqva koficienta na no6tuvki v zadaden period (plateni ili vsi4ki) ot celiq mesec (broi noshtuvki v perioda v meseca)/(broi dni v meseca)
returns real
begin
  declare @Res real;
  declare @DateArrive date;
  declare @DateLeave date;
  declare @DatePaidTo date;
  declare @MonthIsWith30Days integer;
	declare @CalcStart date;
	declare @CalcEnd date;
	
	if (@PeriodEnd<=@PeriodStart) then
	  return (0);
	end if;
	
	if ((MONTH(@PeriodStart) <> MONTH(@PeriodEnd)) or ((YEAR(@PeriodStart) <> YEAR(@PeriodEnd)))) then
	  return (0);
	end if;
	
  select 
	  FloatToDate(DateReg), 
	  FloatToDate(DateReg+NumLodgings), 
		FloatToDate(DateReg+PayedLodgings) 
	into @DateArrive, @DatePaidTo, @DateLeave
	from 
	  Book 
	where 
	  Book.RegNum = @RegNo;
	
	if (@Tip=1) then
	  set @DateLeave=@DatePaidTo;
	end if;
	
  if (@DateArrive >= @DateLeave) then 
	  return(0);
  end if;
	
  set @MonthIsWith30Days = (select MonthIsWith30Days from Hotels where Hotel_ID = (select IntStojnost from Registers where RegisterName = 'HotelID'));
	
	if (@DateArrive>=@PeriodEnd) then
	  return(0);
	end if;
	
	if (@DateLeave<=@PeriodStart) then
	  return(0);
	end if;
	
	set @CalcStart=@DateArrive;
	set @CalcEnd=@DateLeave;
	
	if (@DateArrive<=@PeriodStart) then
	  set @CalcStart=@PeriodStart;
	end if;
	
	if (@DateLeave>=@PeriodEnd) then
	  set @CalcEnd=@PeriodEnd;
	end if;
	
	set @Res = (cast(DAY(@CalcEnd)-DAY(@CalcStart)+1 as real)/cast(DOM(MONTH(@PeriodStart),YEAR(@PeriodStart),@MonthIsWith30Days) as real));
	if (@Res>1) then
	  return(1);
	else 
	  return(@Res);
	end if;
end
--[v. 29.07.2009] - Yoan - Saobrazqva se s PriceSeasonPeriods

GO