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

GO

create view "DBA".v_SpaPocedures
  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 ,
	    'Приключена' 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
  and SR.ReservetionType=2
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 ,
	'Приключена' 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
  and SR.ReservetionType=2

GO

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

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_SpaPocedures','__OPTIONS__','СПА Процедури','__OPTIONS__','SPA Procedures',
        '__OPTIONS__',1,isnull(GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','bvrData1','СПА Процедури','От дата','SPA Procedures',
                      'bvrData1',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','bvrData2','СПА Процедури','До дата','SPA Procedures',
                      'bvrData2',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','BeginDate','СПА Процедури','Дата','SPA Procedures','BeginDate',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','BeginTime','СПА Процедури','Час от','SPA Procedures','BeginTime',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','EndTime','СПА Процедури','Час до','SPA Procedures','EndTime',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','Worker','СПА Процедури','Служител','SPA Procedures','Worker',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','Kabinet','СПА Процедури','Кабинет','SPA Procedures','Kabinet',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','Room','СПА Процедури','Стая','SPA Procedures','Room',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','Status','СПА Процедури','Статус','SPA Procedures','Status',2,GetFreeNInRelations('SPA Procedures', Null, Null, 10),'Status','','',0,0,'','',
        '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_SpaPocedures','Usluga','СПА Процедури','Услуга','SPA Procedures','Usluga',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','ServicePrice','СПА Процедури','Цена','SPA Procedures','ServicePrice',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','Discount','СПА Процедури','отст.','SPA Procedures','Discount',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','PriceWithDiscount','СПА Процедури','Цена с отст.','SPA Procedures','PriceWithDiscount',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','PayedSum','СПА Процедури','Платено','SPA Procedures','PayedSum',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','Admin','СПА Процедури','Администратор','SPA Procedures','Admin',2,GetFreeNInRelations('SPA Procedures', 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_SpaPocedures','GuestName','СПА Процедури','Име на гост','SPA Procedures','GuestName',2,GetFreeNInRelations('SPA Procedures', 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 Procedures');