if (select count(*) from sys.systable where table_name='TypeGuest')=0
  then
CREATE TABLE "DBA"."TypeGuest"
(
	"N"     			integer NOT NULL,
	"TypeGuestName"       		varchar(30) NOT NULL,
	"Note"  			text NOT NULL,
	"Active"        		integer NULL,
	PRIMARY KEY ("N")
);
end if;

GO

delete from TypeGuest;

GO

insert into TypeGuest(N,TypeGuestName,Note,Active) values(0,'Гост от Хотел','Госта от Хотела',1);

GO

insert into TypeGuest(N,TypeGuestName,Note,Active) values(1,'СПА Гост','Госта от СПА',1);

GO

if (Exists (Select Table_name from SysTable where Table_name='v_SpaServices')) then
  Drop View v_SpaServices
end if

GO
create view "DBA"."v_SpaServices"  as
select SG.GuestNum GuestNum,SG.Name GuestName,S.ServiceName Usluga,SW.FullName Worker,SM.Name Kabinet,
SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from Registers where RegisterName='TimeStep') "Time",
minutes(SR.BeginTime,"Time") Endtime,TG.TypeGuestName GuestType
from SpaReservations SR,SpaGuests SG,Services S,SpaWorkers SW,SpaMedicalOffices SM,TypeGuest TG
where SG.GuestNum=SR.GuestFK
and SR.GuestType=1
and SR.ReservetionType=1
and S.ServiceNo=SR.MedicalService
and SW.N=SR.SpaWorker
and SM.N=SR.MedicalOffice
and TG.N=SR.GuestType
union all
select G.GuestNum GuestNum,G.Name GuestName,S.ServiceName Usluga,SW.FullName Worker,SM.Name Kabinet,
SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from Registers where RegisterName='TimeStep') "Time",
minutes(SR.BeginTime,"Time") Endtime,TG.TypeGuestName GuestType
from SpaReservations SR,Guests G,Services S,SpaWorkers SW,SpaMedicalOffices SM,TypeGuest TG
where G.GuestNum=SR.GuestFK
and SR.GuestType=0
and SR.ReservetionType=1
and S.ServiceNo=SR.MedicalService
and SW.N=SR.SpaWorker
and SM.N=SR.MedicalOffice
and TG.N=SR.GuestType

GO

Delete from Relations where lcase(RelationTable) = lcase('SPA Program for Guest')

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)
 VALUES('v_SpaServices','__OPTIONS__','СПА програма за гост','__OPTIONS__','SPA Program for Guest',
        '__OPTIONS__',1,isnull(GetFreeNInRelations('SPA Program for Guest', Null, Null, 100), 100),'__OPTIONS__','','',0,0,'','','','',NULL,'',0,0,1)

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)
 VALUES('v_SpaServices','BeginDate','СПА програма за гост','Дата','SPA Program for Guest','BeginDate',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'BeginDate','','',0,0,'','','','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','GuestNum','СПА програма за гост','Номер на гост','SPA Program for Guest','GuestNum',1,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'GuestNum','','',0,0,'','',
        '','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','GuestName','СПА програма за гост','Име на гост','SPA Program for Guest','GuestName',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'GuestName','','',0,0,'','',
        'if bvrGuestType=0 then select Name from Guests where GuestNum in (select distinct GuestFK from SpaReservations)
            else
         select Name from SpaGuests where GuestNum in (select distinct GuestFK from SpaReservations) end if',
        '',140,'',0,0,1)

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)
 VALUES('v_SpaServices','Usluga','СПА програма за гост','Услуга','SPA Program for Guest','Usluga',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'Usluga','','',0,0,'','',
  'select Servicename from Services where ServiceNo in (select distinct MedicalService from SpaReservations)','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','Worker','СПА програма за гост','Служител','SPA Program for Guest','Worker',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'Worker','','',0,0,'','',
        'select FullName from Spaworkers','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','Kabinet','СПА програма за гост','Кабинет','SPA Program for Guest','Kabinet',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'Kabinet','','',0,0,'','',
        'select Name from SpaMedicalOffices','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','BeginTime','СПА програма за гост','Час от','SPA Program for Guest','BeginTime',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'BeginTime','','',0,0,'','','','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','EndTime','СПА програма за гост','Час до','SPA Program for Guest','EndTime',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'EndTime','','',0,0,'','','','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','"Time"','СПА програма за гост','Продължителност','SPA Program for Guest','"Time"',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'"Time"','','',0,0,'','','','',140,'',0,0,1)

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)
 VALUES('v_SpaServices','GuestType','СПА програма за гост','Тип гост','SPA Program for Guest','GuestType',2,GetFreeNInRelations('SPA Program for Guest', Null, Null, 10),'GuestType','','',0,0,'','',
        'select TypeGuestName from TypeGuest','',140,'',0,0,1)

GO

Call InsertPravaSpravki('SPA Program for Guest');