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

GO

CREATE function GetDateLihva(in @CreditNo integer)
returns date
begin
  declare @rez date;
  declare @M varchar(5);
  declare @Y varchar(5);
  set @M=(select Stoinost from SvoServiceCreditsStn where Glava=@CreditNo and svoistvo=4);
  set @Y=(select Stoinost from SvoServiceCreditsStn where Glava=@CreditNo and svoistvo=5);
  if @M is null or @Y is null then set @rez=cast('01.01.1910' as date) else
    if Length(@M)=1 then set @M='0'||@M end if;
    set @rez=cast('01.'||@M||'.'||@Y as date);
  end if;
  return(@rez);
end
--Dobrin 12.02.2009 created

GO

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

GO

CREATE function ShouldShowService(in @CreditNo integer,in @IsMonthly tinyint,in @SumDiff float,in @RegDate date)
returns tinyint
begin
  declare @IsForPeriod tinyint;
  declare @D1 date;
  declare @D2 date;
  declare @DF date;//dateForfeit
  declare @rez tinyint;
  set @IsForPeriod=1;
  set @D1=(select Stoinost from SvoServiceCreditsStn where Glava=@CreditNo and Svoistvo=2);
  set @D2=(select Stoinost from SvoServiceCreditsStn where Glava=@CreditNo and Svoistvo=3);
  if @D1 is null or @D2 is null then set @IsForPeriod=0 end if;
  if @IsForPeriod=0 then
    if @RegDate<=bvrData2 and @RegDate>=bvrData1 then
      if @IsMonthly=0 then
		set @rez=1
	  else
	    if @SumDiff=0 then set @rez=1 else set @rez=0 end if
	  end if
    else
      set @rez=0 
	end if;
  else
    if DateIntersection(bvrData1,bvrData2,@D1,@D2)=0 then
	  set @rez=0
	else
	  if @IsMonthly=0 then
	    set @rez=1;
	  else
	    if @SumDiff=0 then set @rez=1 else set @rez=0 end if
	  end if
	end if
  end if;
  if (select Service from ServiceCredits where CreditNo=@CreditNo)=(select intStojnost from Registers where RegisterName='ForfeitService') then
    set @DF=cast('01.'||(select Stoinost from SvoServiceCreditsStn where Glava=@CreditNo and Svoistvo=4)||'.'||(select Stoinost from SvoServiceCreditsStn where Glava=@CreditNo and Svoistvo=5) as date); 
	if @DF<=bvrData2 and @DF>=bvrData1 then set @rez=1 else set @rez=0 end if;
  end if;
  return(@rez);	    
end
--Dobrin 12.02.2009 Created za spravka Registrirani uslugi. Vryshta 1 ako trqbva da se pokaje uslugata i 0 ako ne trqbva

GO

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

GO

create view
  DBA.v_RegServicesPeriod
  as select RegDate=if Services.ServiceNo = 4 then GetDateLihva(ServiceCredits.CreditNo) else DateCreditDt endif,
    /*Maria +, 1728*/
    RealDateTime=ServiceCredits.RealDateTime,
    /*Maria -*/
    GuestName=Guests.Name,
    Account=Guests.GuestNum,
    --Maria
    --RoomNo=v_ArriveStayLeaveGuestBook.LastRoom,
    RoomNo=v_ArriveStayLeaveGuestBook.LastRoomName,
    --Maria
    Service=Services.ServiceName || ' в стая ' || B.Room || ' за месец ' || ImeMesec(MONTH(RegDate)), --Dobrin 2195 - Promqna na imeto na uslugata
    ServiceNo=Services.ServiceNo,
    ServicesNum=ServiceCredits.NumberServices,
    /*ServiceSngPrice=if ServiceCredits.KodCurrency=2 then DayRates.ExchangeRate*ServiceCredits.SumCredit
    else ServiceCredits.SumCredit endif, */
    ServiceSngPrice=(if(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1) then(select SpaCurrencies.ExchangeRate from DBA.SpaCurrencies where ServiceCredits.KodCurrency = SpaCurrencies.KodCurrency) else Currencies.ExchangeRate endif)*ServiceCredits.SumCredit,
    AllSum=ServiceCredits.NumberServices*ServiceSngPrice,
    Plateni=ServiceCredits.PayedServices*ServiceSngPrice,
    Dogovor=Contracts.ShortName,
    Admin=Admin.ShortName,
    ShowService=ShouldShowService(ServiceCredits.CreditNo,Services.IsMonthlyBill,AllSum-Plateni,RegDate) from --Dobrin 2195 - ako e komunalna i e platena da ne se pokazva
    DBA.ServiceCredits left outer join DBA.Admin on ServiceCredits.KodAdmin = Admin.AdminNo,
    DBA.Services,
    DBA.Guests,
    DBA.Currencies,
    DBA.v_ArriveStayLeaveGuestBook,
    DBA.Contracts,
    DBA.Book as B where --Dobrin 2195
    ShowService=1 and B.Guest = Guests.GuestNum and --Dobrin 2195
    ServiceCredits.Service = Services.ServiceNo and
    ServiceCredits.KodCurrency = Currencies.KodCurrency and
    ServiceCredits.NumberServices <> 0 and Guests.Contract = Contracts.ContractNo and
    Contracts.GroupNo = any(select ShowType from dba.GrContracts where GrNo = -1) and
    ServiceCredits.GuestNo = Guests.GuestNum and
    v_ArriveStayLeaveGuestBook.GuestNum = Guests.GuestNum and(
    (ServiceCredits.SPaReservationFk = -1) or(ServiceCredits.SPaReservationFk <> -1 and ServiceCredits.SpaTransferFlag = 1))

GO

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

GO

create view
  DBA.v_RegAllServicesPeriod as
  select v_RegServicesPeriod.RegDate,
    /*Maria +, 1728*/
    v_RegServicesPeriod.RealDateTime,
    /*Maria -*/
    v_RegServicesPeriod.GuestName,
    v_RegServicesPeriod.Account,
    v_RegServicesPeriod.RoomNo,
    v_RegServicesPeriod.Service,
    v_RegServicesPeriod.ServicesNum,
    ServiceSngPrice,
    v_RegServicesPeriod.AllSum,
    v_RegServicesPeriod.Plateni,
    Dogovor,
    v_RegServicesPeriod.Admin from
    DBA.v_RegServicesPeriod
//[v 00.07.12]
//[v1 21.12.2007, Maria] - dobavena kolona, vzimat se realnata data i chas

GO

Delete from Relations where lcase(RelationTable) = lcase('Registered Services')

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('v_RegAllServicesPeriod','__OPTIONS__','Регистрирани услуги','__OPTIONS__','Registered Services','__OPTIONS__',1,GetFreeNInRelations('Registered Services', Null, Null, 10),'__OPTIONS__','','',0,0,'','','','',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('v_RegAllServicesPeriod','bvrData1','Регистрирани услуги','От дата','Registered Services','From Date',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'bvrData1','','',0,0,1,1,'','select bvrData1',72,'',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('v_RegAllServicesPeriod','bvrData2','Регистрирани услуги','До дата','Registered Services','To Date',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'bvrData2','','',0,0,1,1,'','select bvrData2',72,'',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('v_RegAllServicesPeriod','RegDate','Регистрирани услуги','Дата','Registered Services','Date',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'RegDate','','',0,1,'','','','',72,'',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('v_RegAllServicesPeriod','RealDateTime','Регистрирани услуги','Реални дата и час','Registered Services','RealDateTime',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'RealDateTime','','',0,1,'','','','',72,'',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('//v_RegAllServicesPeriod','Dogovor','Регистрирани услуги','Договор','Registered Services','Contract',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'Dogovor','','',0,0,'','','SELECT ShortName FROM Contracts','',104,'',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('v_RegAllServicesPeriod','Account','Регистрирани услуги','Кл.номер','Registered Services','Client Number',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'Account','','',0,1,'','','','',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('v_RegAllServicesPeriod','GuestName','Регистрирани услуги','Име гост','Registered Services','Guest Name',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'GuestName','','',0,0,'','','SELECT "Name" FROM Guests GROUP BY "Name"','',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('v_RegAllServicesPeriod','RoomNo','Регистрирани услуги','Стая No','Registered Services','Room #',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'RoomNo','','',0,0,'','','select RoomName from Rooms where Status <> 1 and Status <> 3','',48,'',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('v_RegAllServicesPeriod','Service','Регистрирани услуги','Услуга','Registered Services','Service',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'Service','','',0,0,'','','select ServiceName from Services union (select ''Нощувка и застраховка'')','',168,'',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('v_RegAllServicesPeriod','ServicesNum','Регистрирани услуги','Кол.','Registered Services','Quant.',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'SUM(ServicesNum)','Sum(qrData.Кол.)','# ### ##0.####',1,0,'','','','',56,'',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('v_RegAllServicesPeriod','ServiceSngPrice','Регистрирани услуги','Ед.цена','Registered Services','Sng. Price',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'ServiceSngPrice','','# ### ##0.00',1,0,'','','','',72,'',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('v_RegAllServicesPeriod','AllSum','Регистрирани услуги','Сума','Registered Services','Total',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'Sum(AllSum)','Sum(qrData.Сума)','# ### ##0.00',1,0,'','','','',72,'',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('v_RegAllServicesPeriod','Plateni','Регистрирани услуги','Платени','Registered Services','Paid',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'Sum(Plateni)','Sum(qrData.Платени)','# ### ##0.00',1,0,'','','','',72,'',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('v_RegAllServicesPeriod','Admin','Регистрирани услуги','Админ','Registered Services','Admin',2,GetFreeNInRelations('Registered Services', Null, Null, 10),'Admin','','',1,0,'','','SELECT ShortName FROM Admin','',80,'',0,0,1,today())

Go

delete from InformPermission where RelationN=(
select N from Relations where TableName='v_RegAllServicesPeriod'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='v_RegAllServicesPeriod'and ColName='__OPTIONS__'  ),GrNo,1);
  end if;
end for;