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

GO

create view "DBA".v_SpaReservations
  as
select SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from Registers where RegisterName='TimeStep') "Time",
       minutes(SR.BeginTime,"Time") Endtime,SW.FullName Worker,SM.Name Kabinet,
       (select max(RoomNum) from ServiceCredits where WhoPays=SG.GuestNum) Room ,
	   (if SR.ReservetionType=1 then 'Резервирана' else if SR.ReservetionType=2 then 'Приключена' else 'Отменена' endif endif) as Status1,
	   (if SR.IfPayed=0 then 'Неплатена' else if SR.IfPayed=1 then 'Платена' else 'Прехвърлена' endif endif) as Status2,
	   Status1||' '||Status2 Status,S.ServiceName Usluga,
	   SR.ServicePrice,SR.DiscountStn Discount,CalcPriceWithDiscount(SR.DiscountStn,SR.ServicePrice) PriceWithDiscount,
	  (select sum(TotalSumInvCurrency)*(1+bvrDDSPart) from SpaArticles
        where CreditNo=(select max(SC1.CreditNo) from ServiceCredits SC1 where SC1.SpaReservationFk=SR.N)) as PayedSum,
    (select FullName from Admin  where AdminNo=(select max(KodAdmin) from Servicecredits
     where CreditNo=(select max(SC2.CreditNo) from ServiceCredits SC2 where SC2.SpaReservationFk=SR.N))) as Admin,
        SG.Name GuestName
from SpaReservations SR,SpaWorkers SW,SpaMedicalOffices SM,Services S,SpaGuests SG
where SW.N=SR.Spaworker
  and SM.N=SR.MedicalOffice
  and S.ServiceNo=SR.MedicalService
  and SG.GuestNum=SR.GuestFk
  and SR.GuestType=1
  and BeginDate>=bvrData1
  and BeginDate<=bvrData2
union all
select SR.BeginDate,SR.BeginTime,SR.TimeSteps*(select max(IntStojnost) from Registers where RegisterName='TimeStep') "Time",
       minutes(SR.BeginTime,"Time") Endtime,SW.FullName Worker,SM.Name Kabinet,
       (select max(RoomNum) from ServiceCredits where WhoPays=G.GuestNum) Room ,
	   (if SR.ReservetionType=1 then 'Резервирана' else if SR.ReservetionType=2 then 'Приключена' else 'Отменена' endif endif) as Status1,
	   (if SR.IfPayed=0 then 'Неплатена' else if SR.IfPayed=1 then 'Платена' else 'Прехвърлена' endif endif) as Status2,
       Status1||' '||Status2 Status,S.ServiceName Usluga,
	   SR.ServicePrice,SR.DiscountStn Discount,CalcPriceWithDiscount(SR.DiscountStn,SR.ServicePrice) PriceWithDiscount,
	  (select sum(TotalSumInvCurrency)*(1+bvrDDSPart) from SpaArticles
        where CreditNo=(select max(SC1.CreditNo) from ServiceCredits SC1 where SC1.SpaReservationFk=SR.N)) as PayedSum,
  (select FullName from Admin  where AdminNo=(select max(KodAdmin) from Servicecredits
   where CreditNo=(select max(SC2.CreditNo) from ServiceCredits SC2 where SC2.SpaReservationFk=SR.N))) as Admin,
        G.Name GuestName
from SpaReservations SR,SpaWorkers SW,SpaMedicalOffices SM,Services S,Guests G
where SW.N=SR.Spaworker
  and SM.N=SR.MedicalOffice
  and S.ServiceNo=SR.MedicalService
  and G.GuestNum=SR.GuestFk
  and SR.GuestType=0
  and BeginDate>=bvrData1
  and BeginDate<=bvrData2

GO

Delete from Relations where lcase(RelationTable) = lcase('SPA Reservations')

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_SpaReservations','__OPTIONS__','СПА Резервации','__OPTIONS__','SPA Reservations',
        '__OPTIONS__',1,isnull(GetFreeNInRelations('SPA Reservations', 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,PoslData)

     VALUES('v_SpaReservations','bvrData1','СПА Резервации','От дата','SPA Reservations',
                      'bvrData1',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'bvrData1','',
                      '',0,0,1,1,
                      null,'select bvrData1',70,null,0,
                      0,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,PoslData)

     VALUES('v_SpaReservations','bvrData2','СПА Резервации','До дата','SPA Reservations',
                      'bvrData2',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'bvrData2','',
                      '',0,0,1,1,
                      null,'select bvrData2',70,null,0,
                      0,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)
 VALUES('v_SpaReservations','BeginDate','СПА Резервации','Дата','SPA Reservations','BeginDate',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'BeginDate',
        '','',0,1,'','','','',
        70,'',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_SpaReservations','BeginTime','СПА Резервации','Час от','SPA Reservations','BeginTime',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'BeginTime',
        '','',0,2,'','','','',
       40,'',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_SpaReservations','EndTime','СПА Резервации','Час до','SPA Reservations','EndTime',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'EndTime','','',0,0,'','','','',
        40,'',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_SpaReservations','Worker','СПА Резервации','Служител','SPA Reservations','Worker',2,GetFreeNInRelations('SPA Reservations', 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_SpaReservations','Kabinet','СПА Резервации','Кабинет','SPA Reservations','Kabinet',2,GetFreeNInRelations('SPA Reservations', 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_SpaReservations','Room','СПА Резервации','Стая','SPA Reservations','Room',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'Room','','',0,0,'','',
         'select RoomNum from Rooms','',40,'',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_SpaReservations','Status','СПА Резервации','Статус','SPA Reservations','Status',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'Status','','',0,0,'','',
        'select ''Резервирана Неплатена '' union select ''Резервирана Платена'' union select ''Резервирана Прехвърлена'' union select ''Приключена Неплатена'' union select ''Приключена Платена'' union select ''Приключена Прехвърлена'' union select ''Отменена Неплатена'' union select ''Отменена Платена'' union select ''Отменена Прехвърлена'''
        ,'',160,'',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_SpaReservations','Usluga','СПА Резервации','Услуга','SPA Reservations','Usluga',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'Usluga','','',0,0,'','',
  'select Servicename from Services where ServiceNo in (select distinct MedicalService from SpaReservations)','',
        160,'',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_SpaReservations','ServicePrice','СПА Резервации','Цена','SPA Reservations','ServicePrice',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'ServicePrice','','# ### ##0.00',
        1,0,'','',
        '','',60,'',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_SpaReservations','Discount','СПА Резервации','отст.','SPA Reservations','Discount',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),
        'Discount','','',
        1,0,'','',
        '','',40,'',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_SpaReservations','PriceWithDiscount','СПА Резервации','Цена с отст.','SPA Reservations','PriceWithDiscount',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),
        'SUM(PriceWithDiscount)','SUM(qrData.Цена с отст.)','# ### ##0.00',
        1,0,'','',
        '','',60,'',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_SpaReservations','PayedSum','СПА Резервации','Платено','SPA Reservations','PayedSum',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),
        'SUM(PayedSum)','SUM(qrData.Платено)','# ### ##0.00',
        1,0,'','',
        '','',60,'',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_SpaReservations','Admin','СПА Резервации','Администратор','SPA Reservations','Admin',2,GetFreeNInRelations('SPA Reservations', Null, Null, 10),'Admin','','',0,0,'','',
        'select distinct FullName from Admin',
        '',120,'',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_SpaReservations','GuestName','СПА Резервации','Име на гост','SPA Reservations','GuestName',2,GetFreeNInRelations('SPA Reservations', 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',
        '',150,'',0,0,1)

GO

Call InsertPravaSpravki('SPA Reservations');