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

GO

create function GetRealDatePayedRentForMonth(in @RegNo integer)
returns date
begin
  declare @SpravkaDate date;
  declare @DiffMonths integer;
  declare @BookRegDate date;
  declare @ResultDate date;
  
  select DateRegDt into @BookRegDate from Book where RegNum=@RegNo;
  
  set @SpravkaDate = date('1.'||bvrSpravkaM||'.'||bvrSpravkaY);
  set @DiffMonths = datediff(month,@BookRegDate,@SpravkaDate);
  
  set @ResultDate = null;
  for f as curs scroll cursor for 
    select
	  Inv.InvoiceNo as @InvoiceNo,
	  Inv.InvoiceDateDt as @InvoiceDateDt
	from
	  Invoices Inv, Articles A
	where
	  A.InvoiceNo=Inv.InvoiceNo
	  and A.RegNo=@RegNo
	order by Inv.InvoiceNo asc
  do
    if (@DiffMonths=0) then 
	  set @ResultDate = @InvoiceDateDt
	end if;
	set @DiffMonths=@DiffMonths-1;
  end for;
  
  return (@ResultDate);
end

go

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('CheckGuestBalanceForMonth'))) THEN
Drop function CheckGuestBalanceForMonth
END IF
GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('CheckBookBalanceForMonth'))) THEN
Drop function CheckBookBalanceForMonth
END IF
GO
create function CheckBookBalanceForMonth(in @RegNo integer)
returns tinyint
begin
  declare @rez tinyint;
  declare @DateA date;
  declare @DateL date;
  select FloatToDate(DateReg), FloatToDate(DateReg+NumLodgings) into @DateA, @DateL from Book where RegNum=@RegNo;
  if ((bvrSpravkaY-YEAR(@DateA))*12 + bvrSpravkaM-MONTH(@DateA))>=0 AND ((YEAR(@DateL)-bvrSpravkaY)*12+MONTH(@DateL)-bvrSpravkaM>=0) then
	set @rez=1 else set @rez=0 end if;
  return(@rez);
end
GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetQtyArtForMonth'))) THEN
Drop function GetQtyArtForMonth
END IF
GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetQtyRentForMonth'))) THEN
Drop function GetQtyRentForMonth
END IF
GO

create function GetQtyRentForMonth(in @RegNo integer,in @Tip tinyint)//@Tip 0-cqloto kolichestvo, 1-platenoto kolichestvo
returns float
begin
  declare @rez float;
  declare @DateA date;
  declare @DateL date;
  declare @DateP date;
  select FloatToDate(DateReg), FloatToDate(DateReg+NumLodgings), FloatToDate(DateReg+PayedLodgings) into @DateA, @DateL, @DateP from Book where RegNum=@RegNo;
  if MONTH(@DateA)=bvrSpravkaM AND YEAR(@DateA)=bvrSpravkaY then
    set @rez=cast(DOM(bvrSpravkaM,bvrSpravkaY)-DAY(@DateA)+1 as float)/DOM(bvrSpravkaM,bvrSpravkaY);
  else
    if MONTH(@DateL)=bvrSpravkaM AND YEAR(@DateL)=bvrSpravkaY then
	  set @rez=cast(DAY(@DateL)-1 as float)/DOM(bvrSpravkaM,bvrSpravkaY);
	else
	  set @rez=CheckBookBalanceForMonth(@RegNo);
	end if
  end if;
  if DAY(@DateL)<DOM(bvrSpravkaM,bvrSpravkaY) and MONTH(@DateL)=bvrSpravkaM and YEAR(@DateL)=bvrSpravkaY and MONTH(@DateL)=MONTH(@DateA) and YEAR(@DateL)=YEAR(@DateA) then
    set @rez=cast(DAY(@DateL)-DAY(@DateA) as float)/DOM(bvrSpravkaM,bvrSpravkaY)
  end if;
  if @Tip=0 then return(@rez) else
    if (@DateA=@DateP) then
	  set @rez=0;
	else
	  if (@DateL<>@DateP) then
	    if (year(@DateP) < bvrSpravkaY) then 
		  set @rez=0;
		else
  	      if (year(@DateP) = bvrSpravkaY)then
		    if (month(@DateP) < bvrSpravkaM) then
		      set @rez = 0;
			else
			  if (month(@DateP) = bvrSpravkaM) and (@DateL <> @DateP) then
			    set @rez = 0;
			  end if;
			end if;
		  end if;
        end if;
	  end if;
	end if;
  end if;
  return(@rez);
end

GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetNoshtuvkaNameForMonth'))) THEN
Drop function GetNoshtuvkaNameForMonth
END IF
GO

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetNoshtuvkaNameForMonthBook'))) THEN
Drop function GetNoshtuvkaNameForMonthBook
END IF
GO

create function GetNoshtuvkaNameForMonthBook(in @RegNo integer,in @IsLodg integer default 0)//@IsLodg 0-uslugata e za mesec,1-uslugata e za noshtuvka
returns varchar(60)
begin
  declare @rez varchar(60);
  declare @ServiceName varchar(40);
  declare @RoomName varchar(6);
  declare @NeplDni varchar(6);
  declare @KoefNeplDni real;
  set @KoefNeplDni = GetQtyRentForMonth(@RegNo,0);
  if @KoefNeplDni<>1 then//ne e za celiq mesec
    set @NeplDni=' ' || Round(@KoefNeplDni*DOM(bvrSpravkaM,bvrSpravkaY),0) || '/' || DOM(bvrSpravkaM,bvrSpravkaY);
  end if;
  select S.ServiceName, R.RoomName into @ServiceName, @RoomName from Services S, PriceKinds PK, Book B, Rooms 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 @rez= @ServiceName || ' ' || ImeMesec(bvrSpravkaM) || ' в стая ' || @RoomName  || @NeplDni
  else set @rez= @ServiceName || 'в стая ' || @RoomName;
  end if;
  return(@rez);
end

GO

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 Settlements where N = G.SettlementN),
    G.Sex,
    PriceKind=PK.Description,
    Usluga=DBA.GetNoshtuvkaNameForMonthBook(B.RegNum),
    Cena=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=GetRealDatePayedRentForMonth(B.RegNum),
	Koef=Kol,
    BrStudenti=1,
    RName=R.RoomName,
	CountryName=Ctr.Description
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
    
Go


if (not exists(select ColName from Relations where ColName = 'RealDatePayed' and TableName='v_OwePaidRentMonth')) then
  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('v_OwePaidRentMonth','RealDatePayed','Дължим и платен наем - месец','Дата плащане','Monthly rent information','RealDatePayed',2,GetFreeNInRelations('Monthly rent information', Null, Null, 1),'RealDatePayed','','',0,0,'','','','',80,'',0,0,1,today(*))
end if

Go

IF (EXISTS (select proc_name from sysprocedure where LCase(proc_name)=LCase('GetQtyLodgForMonth'))) THEN
Drop function GetQtyLodgForMonth
END IF
GO

create function GetQtyLodgForMonth(in @RegNo integer,in @NumberLodgings integer)
returns integer
begin
  declare @DateEnd date;
  declare @DateBegin date;
  declare @res integer;
  
  select DateRegDt, dateadd(day,@NumberLodgings,DateRegDt) into @DateBegin,@DateEnd from Book where RegNum=@RegNo;
  
  if (@DateEnd > dateadd(month,1,date('1.'||bvrSpravkaM||'.'||bvrSpravkaY))) then
    set @DateEnd = dateadd(month,1,date('1.'||bvrSpravkaM||'.'||bvrSpravkaY));
  end if;
  if (@DateBegin < date('1.'||bvrSpravkaM||'.'||bvrSpravkaY)) then
    set @DateBegin = date('1.'||bvrSpravkaM||'.'||bvrSpravkaY);
  end if;
  set @res = datediff(day,@DateBegin,@DateEnd);
  return (@res)
end

go

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

GO

create function GetRealDatePayedLodgForMonth(in @RegNo integer)
returns date
begin
  declare @SpravkaDate date;
  declare @DiffDays integer;
  declare @BookRegDate date;
  declare @ResultDate date;
  
  select DateRegDt into @BookRegDate from Book where RegNum=@RegNo;
  
  set @SpravkaDate = date('1.'||bvrSpravkaM||'.'||bvrSpravkaY);
  set @DiffDays = datediff(day,@BookRegDate,@SpravkaDate);
  
  set @ResultDate = null;
  for f as curs scroll cursor for 
    select
	  Inv.InvoiceNo as @InvoiceNo,
	  Inv.InvoiceDateDt as @InvoiceDateDt,
	  A.Qty as @Qty
	from
	  Invoices Inv, Articles A
	where
	  A.InvoiceNo=Inv.InvoiceNo
	  and A.RegNo=@RegNo
	order by Inv.InvoiceNo asc
  do
    set @DiffDays = @DiffDays - @Qty;
    if (@DiffDays<=0) then 
	  set @ResultDate = @InvoiceDateDt
	end if;
  end for;
  
  return (@ResultDate);
end
go

IF (EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('v_OwePaidLodgMonth'))) THEN
Drop View v_OwePaidLodgMonth
END IF

GO

create view v_OwePaidLodgMonth as
select
 Guest as GNo,
 Month=ImeMesec(bvrSpravkaM),
 Year=bvrSpravkaY,
 G.Name,
 Uni=U.FullName,
 Kurs=K.Name,
 Spec=S.Name,
 G.FN,
 G.Address,
 City=(select SettleMentName from Settlements where N = G.SettlementN),
 G.Sex,
 PriceKind=PK.Description,
 Usluga=GetNoshtuvkaNameForMonthBook(B.RegNum,1), 
 Cena=B.Price*C.ExchangeRate,
 Kol=GetQtyLodgForMonth(B.RegNum,B.NumLodgings),
 SumOwe=Round(Kol*Cena,2),
 KolPaid=GetQtyLodgForMonth(B.RegNum,B.PayedLodgings),
 SumPaid=Round(KolPaid*Cena,2),
 Razlika=SumOwe-SumPaid,
 RealDatePayed=GetRealDatePayedLodgForMonth(B.RegNum),
 BrStudenti=1,
 RName=R.RoomName
from 
  Book B, PriceKinds PK, Guests G, Kursove K, Specialities S, Universities U, Currencies C, Rooms R
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 B.DateRegDt < dateadd(month,1,date('1.'||bvrSpravkaM||'.'||bvrSpravkaY))
  and dateadd(day,B.NumLodgings,B.DateRegDt) > date('1.'||bvrSpravkaM||'.'||bvrSpravkaY)
GO

if (not exists(select ColName from Relations where ColName='RealDatePayed' and TableName='v_OwePaidLodgMonth')) then
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('v_OwePaidLodgMonth','RealDatePayed','Дължими и платени нощувки - месец','Дата плащане','Monthly lodgings information','RealDatePayed',2,GetFreeNInRelations('Monthly lodgings information', Null, Null, 1),'RealDatePayed','','',0,0,'','','','',120,'',0,0,1,today(*))
end if

Go