Delete from Relations where RelationTable='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_Services', '__OPTIONS__', 'Услуги', '__OPTIONS__', 'Services', '__OPTIONS__', 1, GetFreeNInRelations('Services',null,null,5), '__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_Services', 'ServiceDate', 'Услуги', 'Дата', 'Services', 'Date', 2, GetFreeNInRelations('Services',null,null,5), 'ServiceDate', '', '', 0, 1, '', '', '', '', 80, '', 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_Services', 'ServiceDateTime', 'Услуги', 'Реални дата и час', 'Services', 'ServiceDateTime', 2, GetFreeNInRelations('Services',null,null,5), 'ServiceDateTime', '', '', 0, 1, '', '', '', '', 80, '', 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_Services', 'Room', 'Услуги', 'Стая No', 'Services', 'Room #', 2, GetFreeNInRelations('Services',null,null,5), 'Room', '', '', 0, 1, '', '', '', '', 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_Services', 'Account', 'Услуги', 'Кл.номер', 'Services', 'Client Number', 2, GetFreeNInRelations('Services',null,null,5), 'Account', '', '', 0, 0, '', '', '', '', 80, '', 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_Services', 'GuestName', 'Услуги', 'Име гост', 'Services', 'Guest Name', 2, GetFreeNInRelations('Services',null,null,5), '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_Services', 'Country', 'Услуги', 'Държава', 'Services', 'Country', 2, GetFreeNInRelations('Services',null,null,5), 'Country', '', '', 0, 0, '', '', 'select Description from Countries', '', 100, '', 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_Services', 'Agent', 'Услуги', 'Агент', 'Services', 'Agent', 2, GetFreeNInRelations('Services',null,null,5), 'Agent', '', '', 0, 0, '', '', 'select ShortName from Contracts order by ContractNo', '', 100, '', 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_Services', 'Service', 'Услуги', 'Услуга - пълно име', 'Services', 'Service', 2, GetFreeNInRelations('Services',null,null,5), 'Service', '', '', 0, 0, '', '', 'select ServiceName from Services', '', 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_Services', 'ExplanationColumn', 'Услуги', 'Услуги от', 'Services', 'ExplanationColumn', 2, GetFreeNInRelations('Services',null,null,5), 'ExplanationColumn', '', '', 1, 0, '', '', 'select ''POS'' union select ''PBX'' union select ''''', '', 100, '', 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_Services', 'PayedSum', 'Услуги', 'Платени', 'Services', 'Paid', 2, GetFreeNInRelations('Services',null,null,5), 'Sum(PayedSum)', 'Sum(qrData.Платени)', '# ##0.00', 1, 0, '', '', '', '', 100, '', 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_Services', 'PayTypeStr', 'Услуги', 'Начин на плащане', 'Services', 'PayTypeStr', 2, GetFreeNInRelations('Services',null,null,5), 'PayTypeName', '', '', 1, 0, '', '', 'select PayTypeName from PayTypes', '', 100, '', 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_Services', 'Balance', 'Услуги', 'Баланс', 'Services', 'Balance', 2, GetFreeNInRelations('Services',null,null,5), 'Balance', 'Sum(qrData.Баланс)', '# ##0.00', 1, 0, '', '', '', '', 100, '', 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_Services', 'Currency', 'Услуги', 'Код валута', 'Services', 'Currency', 2, GetFreeNInRelations('Services',null,null,5), 'Currency', '', '', 1, 0, '', '', '', '', 100, '', 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_Services', 'ServiceGroupBy', 'Услуги', 'Услуга', 'Services', 'ServiceGroupBy', 2, GetFreeNInRelations('Services',null,null,5), 'ServiceGroupBy', '', '', 1, 0, '', '', '', '', 100, '', 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_Services', 'AdminName', 'Услуги', 'Админ', 'Services', 'Admin', 2, GetFreeNInRelations('Services',null,null,5), 'AdminName', '', '', 1, 0, '', '', '', '', 100, '', 0, 0, 1, today())
 
GO

Call InsertPravaSpravki('Services');

GO

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

GO

create view /*SQL-1003-0002689930*/
  DBA.v_Services
  as select Account=g.GuestNum,
    ServiceDate=DATEFORMAT(sc.DateCreditDt,'DD.MM.YYYY'),
    /*Maria +, 1728*/
    ServiceDateTime=sc.RealDateTime,
    /*Maria -*/
    Service=if exists(select * from DBA.SvoServiceCreditsStn where SvoServiceCreditsStn.glava = sc.CreditNo and SvoServiceCreditsStn.Svoistvo = 1)
    then(select SvoServiceCreditsStn.Stoinost from DBA.SvoServiceCreditsStn where SvoServiceCreditsStn.glava = sc.CreditNo and SvoServiceCreditsStn.Svoistvo = 1)
    else S.ServiceName
    endif,ServiceGroupBy=s.ServiceName,
    GuestName=g.Name,
    Country=Countries.Description,
    Agent=Contracts.ShortName,
    PayedSum=sc.PayedServices*sc.SumCredit,
    Balance=(sc.PayedServices-sc.NumberServices)*sc.SumCredit,
    AdminName=Admin.ShortName,
    --Maria
    --Room=ASL.LastRoom,
    Room=ASL.LastRoomName,
    --Maria
    Currency=(if(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1) then(select Spacur.Symbol from DBA.SpaCurrencies as Spacur where sc.KodCurrency = Spacur.KodCurrency) else cur.Symbol
    endif),ExplanationColumn=if(sc.PBXLogN is not null or sc.KodAdmin = 1002) then 'PBX'
    else if(select count(A.AdminNo)
      from dba.Admin as A
      where A.AdminGroup = 0 and A.AdminNo = sc.KodAdmin) > 0 then 'POS'
    else if(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1) then 'СПА'
    else null
    endif
    endif
    endif,
	PayTypeStr = PT.PayTypeName
    from dba.ServiceCredits as sc
	left outer join Articles as A on A.CreditNo = sc.CreditNo
	left outer join Invoices as Inv on A.InvoiceNo = Inv.InvoiceNo
	left outer join PayTypes as PT on Inv.PayHow = PT.PayTypeN
    ,DBA.Services as s
    ,DBA.Contracts left outer join DBA.Guests as g on Contracts.ContractNo = g.Contract
    ,DBA.Countries
    ,DBA.Admin
    ,DBA.Currencies as cur
    ,DBA.v_ArriveStayLeaveGuestBook as ASL
    where sc.Service = s.ServiceNo
    and sc.GuestNo = g.GuestNum
    and Contracts.GroupNo = any(select distinct(GrContracts.showtype) from dba.GrContracts where GrContracts.GrNo = -1)
    and g.CountryArrive = Countries.CountryKod
    and sc.KodAdmin = Admin.AdminNo
    and ASL.GuestNum = g.GuestNum
    and sc.KodCurrency = cur.KodCurrency and(
    (sc.SPaReservationFk = -1) or(sc.SPaReservationFk <> -1 and sc.SpaTransferFlag = 1))
/*Opravia filtriraneto na data na spravkata*/
//[17.03.2005] Safa, optimizatsia
//[21.12.2007] Maria, dobavena kolonata ServiceDateTime, realnata data i chas