...
This integration uses Aptify Views to retrieve data from the Aptify business layer via web services. Various multiple base views are included in the Aptify-Eventpedia installation media, which are required to share Event Information between Aptify and Eventpedia. These multiple base views are all under the Meetings entity. The multiple base views used for the Aptify-Eventpedia integration are as follows:
Event informationMBV Display Name: EventInformation
View: EventInfo
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwEventInformations
AS
SELECT
m.ID 'ID',
m.MeetingTitle 'EventName',
m.StartDate 'StartDate',
m.EndDate 'EndDate',
e.FirstLast 'PlannerName',
CASE WHEN e.WorkAreaCode IS NULL THEN e.WorkPhone else '(' + e.WorkAreaCode + ')' + e.WorkPhone end 'PlannerPhone',
e.HomeFax 'PlannerFax',
e.WebPage 'PlannerURL',
e.Email1 'PlannerEmail',
e.FirstLast 'PlannerContactName',
m1.MeetingID 'MeetingID',
m.VerboseDescription 'Description',
p.WebProductPage 'Website'
FROM
Meeting m
LEFT OUTER JOIN
vwEmployees e
ON
m.CoordinatorID=e.ID
LEFT OUTER JOIN
vwMeetings m1
ON
m.ID=m1.ID
LEFT OUTER JOIN
Product p
ON
m.ProductID=p.ID |
Event LocationMBV Display Name: EventLocation
View: EventLocation
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwEventLocations
AS
SELECT
m.ID 'ID',
m.Place 'EventLocationName',
a.Line1 'EventLocationAddress1',
a.Line2 'EventLocationAddress2',
a.Line3 'EventLocationAddress3',
a.City 'EventCity',
a.StateProvince 'EventState',
a.PostalCode 'PostalCode',
m1.MeetingID 'MeetingID',
a.Country 'Country',
Case when
(SELECT CASE WHEN ParentMeeting.ID IS NULL
THEN 0
ELSE ParentMeeting.ID
END AS ParentMeetingID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID
) is null then m.id else
(SELECT TOP 1 ParentMeeting.ID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID) end as
'ParentMeetingID',
m.VenueID 'LocationID'
FROM
Meeting m
LEFT OUTER JOIN
Address a
ON
m.AddressID=a.ID
LEFT OUTER JOIN
vwMeetings m1
ON
m.ID=m1.ID |
Event RoomsMBV Display Name: Room
View: EventRooms
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwRooms
AS
SELECT
m.ID 'ID',
v.Name 'EventLocationName',
mr.Name 'RoomName',
mrt.Name 'RoomType',
v.Floor 'RoomFloor',
m.ID 'MeetingID',
m.MeetingRoomID 'RoomID',
Case when
(SELECT CASE WHEN ParentMeeting.ID IS NULL
THEN 0
ELSE ParentMeeting.ID
END AS ParentMeetingID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID
) is null then m.id else
(SELECT TOP 1 ParentMeeting.ID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID) end as
'ParentMeetingID',
m.VenueID 'LocationID'
FROM
Meeting m
INNER JOIN
MeetingRoom mr
ON
m.MeetingRoomID=mr.ID
LEFT OUTER JOIN
MeetingRoomType mrt
ON
m.MeetingRoomTypeID=mrt.ID
INNER JOIN
Venue v
ON
m.VenueID=v.ID |
Event AgendaMBV Display Name: EventSessions
View: EventSessions
Fields:
Image Removed
...
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwAgendas
AS
SELECT
m.ID 'ID',
m.StartDate 'Date',
m.MeetingTitle 'AgendaTitle',
m.verboseDescription 'Description',
m1.MeetingID 'MeetingID',
m1.ParentMeetingID 'ParentMeetingID',
STUFF( right( convert( varchar(26), m.StartDate, 109 ), 15 ), 7, 7, ' ' ) 'StartTime',
STUFF( right( convert( varchar(26), m.EndDate, 109 ), 15 ), 7, 7, ' ' ) 'EndTime'
FROM
Meeting m
INNER JOIN
Product p
ON
m.ProductID=p.ID
LEFT OUTER JOIN
vwMeetings m1
ON
m.ID=m1.ID |
Event SponsorsMBV Display Name: Sponsors
View: EventSponsors
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW vwSponsors AS
SELECT
m.ID 'ID',
c.Name 'SponsorName',
ms.Comments 'Description',
c.Name 'Sponsor',
p.NameWCompany 'ContactName',
m1.MeetingID 'MeetingID',
p.Title 'ContactTitle',
c.CompanyType 'Type',
CASE WHEN c.MainAreaCode is null then c.MainPhone else '(' + RTRIM(c.MainAreaCode) +')'+ c.MainPhone end 'Phone',
c.MainEmail 'Email',
c.WebSite 'Website',
c.AddressLine1 'Address1',
c.AddressLine2 'Address2',
c.AddressLine3 'Address3',
c.City 'City',
c.State 'State',
c.ZipCode 'PostalCode',
ms.SponsorID 'SponsorsID',
c.Country 'Country',
Case when
(SELECT CASE WHEN ParentMeeting.ID IS NULL
THEN 0
ELSE ParentMeeting.ID
END AS ParentMeetingID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID
) is null then m.id else
(SELECT TOP 1 ParentMeeting.ID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID) end as
'ParentMeetingID'
FROM
Meeting m
LEFT OUTER JOIN
MeetingSponsor ms
ON
m.ID=ms.MeetingID
LEFT OUTER JOIN
vwMeetings m1
ON
m.ID=m1.ID
LEFT OUTER JOIN
vwCompanies c
ON
ms.SponsorID=c.ID
INNER JOIN
Person p
ON
ms.ContactID=p.ID |
Event AttendeesMBV Display Name: Attendees
View: EventAttendees
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwAttendees
AS
SELECT
p.ID 'PersonID',
p.FirstName,
p.LastName,
p.Prefix,
p.MiddleName,
p.Suffix,
p.Email,
p.MemberType 'Type',
p.Title,
p.FirstLast ,
p.Email1,
p.AddressLine1,
p.AddressLine2,
p.City,
p.State,
p.ZipCode 'PostalCode',
CASE WHEN p.phoneAreaCode is null then p.Phone else '(' + RTRIM(p.phoneAreaCode) +')'+ p.Phone end 'Phone',
p.Company,
od.ProductID 'ProductMeetingID',
p.ID ,
m.ID 'MeetingID',
p.Country 'Country'
FROM vwPersons p
LEFT JOIN vwOrderMeetDetail omd ON p.ID=omd.AttendeeID
LEFT JOIN OrderDetail od ON (omd.OrderID = od.OrderID AND omd.Sequence=od.Sequence)
INNER JOIN Meeting m on m.ProductID = od.ProductID
WHERE od.Quantity > 0 AND ((omd.StatusID = (SELECT ID FROM AttendeeStatus WHERE Name = 'Attended')) OR (omd.StatusID = (SELECT ID FROM AttendeeStatus WHERE Name = 'Registered'))) |
Event ExhibitorsMBV Display Name: Exhibitor
View: EventExhibitors
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwExhibitors
AS
(SELECT
c.Name 'ExhibitorName',
c.Name 'Exhibitor',
c.WebSite 'WebSite',
ct.Name 'Type',
a.Line1 'Address1' ,
a.Line2 'Address2',
a.Line3 'Address3' ,
a.City ,
a.StateProvince 'State' ,
a.PostalCode 'PostalCode',
p.ID 'PersonID',
p.FirstLast 'ContactName',
p.Email 'ContactEmail',
p.Title 'ContactTitle',
CASE WHEN p.phoneAreaCode is null then p.Phone else '(' + RTRIM(p.phoneAreaCode) +')'+ p.Phone end 'ContactPhone',
e.ProductID 'ProductMeetingID',
c.ID,
m.ID 'MeetingID',
obd.BoothID 'BoothID'
FROM OrderDetail od
INNER JOIN OrderBoothdetail obd ON (obd.OrderID = od.OrderID)
INNER JOIN vwPersons p ON p.ID = obd.ContactID
INNER JOIN Company c ON c.ID = obd.ExhibitorID
INNER JOIN CompanyType ct ON ct.ID = c.CompanyTypeID
LEFT JOIN Address a ON a.ID = c.AddressID
LEFT JOIN Expo e on e.ProductID = od.ProductID
INNER JOIN Meeting m ON e.ID = m.LinkedExpoID
WHERE od.Quantity > 0 AND obd.BoothID is not null )
Union
(
SELECT
c.Name 'ExhibitorName',
c.Name 'Exhibitor',
c.WebSite 'WebSite'
ct.Name 'Type',
a.Line1 'Address1' ,
a.Line2 'Address2',
a.Line3 'Address3' ,
a.City ,
a.StateProvince 'State' ,
a.PostalCode 'PostalCode',
p.ID 'PersonID',
p.FirstLast 'ContactName',
p.Email 'ContactEmail',
p.Title 'ContactTitle',
CASE WHEN p.phoneAreaCode is null then p.Phone else '(' + RTRIM(p.phoneAreaCode) +')'+ p.Phone end 'ContactPhone',
e.ProductID 'ProductMeetingID',
c.ID,
m.ID 'MeetingID',
obd.BoothID 'BoothID'
FROM vwPersons p
LEFT JOIN vwOrderMeetDetail omd ON p.ID=omd.AttendeeID
LEFT JOIN OrderDetail od ON (omd.OrderID = od.OrderID AND omd.Sequence=od.Sequence)
INNER JOIN OrderBoothdetail obd ON (obd.OrderID = od.OrderID AND p.ID = obd.ContactID)
INNER JOIN Company c ON c.ID = obd.ExhibitorID
INNER JOIN CompanyType ct ON ct.ID = c.CompanyTypeID
LEFT JOIN Address a ON a.ID = c.AddressID
LEFT JOIN Expo e on e.ProductID = od.ProductID
INNER JOIN Meeting m ON m.ProductID = od.ProductID
WHERE od.Quantity > 0 AND omd.StatusID = (SELECT ID FROM AttendeeStatus WHERE Name='Attended') ) |
Event SpeakersMBV Display Name: Speakers
View: EventSpeakers
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwSpeakers
AS
SELECT
m.ID 'ID',
ms.Type 'Type',
a.Line1 'Address',
Case when
(SELECT CASE WHEN ParentMeeting.ID IS NULL
THEN 0
ELSE ParentMeeting.ID
END AS ParentMeetingID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID
) is null then m.id else
(SELECT TOP 1 ParentMeeting.ID
FROM Meeting ParentMeeting
INNER JOIN Product ON ParentMeeting.ProductID = Product.ParentID
WHERE Product.ID = m.ProductID) end as
'ParentMeetingID',
ms.MeetingID 'MeetingID',
p.FirstName 'FirstName',
p.LastName 'LastName',
p.Prefix 'Prefix',
p.MiddleName 'MiddleName',
p.Suffix 'Suffix',
p.WebSite 'Website',
CASE WHEN p.PhoneAreaCode is null then p.phone else '(' + RTRIM(p.PhoneAreaCode) +')'+ p.phone end 'Phone',
p.Email 'EMail',
p.Title 'JobTitle',
p.CompanyName 'Employer',
ms.SpeakerID 'SpeakerID'
FROM
Meeting m
INNER JOIN
MeetingSpeaker ms
ON
m.ID=ms.MeetingID
LEFT OUTER JOIN
Address a
ON
m.AddressID=a.ID
INNER JOIN
vwMeetings m1
ON
m.ID=m1.ID
LEFT OUTER JOIN
vwPersons p
ON
ms.SpeakerID=p.ID |
Event SessionsMBV Display Name: Sessions
View: EventSessions
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwEventSessions AS
SELECT
m.ID 'ID',
m.MeetingTitle 'SessionTitle',
m.StartDate 'Date',
mt.Name 'Type',
mt1.Name 'Track',
m.verboseDescription 'Agenda',
m.MeetingTitle 'Event',
--m.Place 'LocationName',
v.Name 'LocationName',
mr.Name 'RoomName',
m.verboseDescription 'Description',
--ms.Description 'Session',
--ms.Speaker 'Presenter2',
--ms.Speaker 'Presenter3',
--ms1.Presenter1 'Presenter1',
--ms2.Presenter2 'Presenter2',
--ms3.Presenter3 'Presenter3',
--ms4.Presenter4 'Presenter4',
--ms5.Presenter5 'Presenter5',
ms1.SpeakerID 'Presenter1',
ms2.SpeakerID 'Presenter2',
ms3.SpeakerID 'Presenter3',
ms4.SpeakerID 'Presenter4',
ms5.SpeakerID 'Presenter5',
m1.MeetingID 'MeetingID',
m1.ParentMeetingID 'ParentMeetingID',
--Case when
--m1.ParentMeetingID is null then m.id else
--m1.ParentMeetingID end as 'ParentMeetingID',
Ltrim(Rtrim(STUFF( right( convert( varchar(26), m.StartDate, 109 ), 15 ), 7, 7, ' ' ))) 'StartTime',
Ltrim(Rtrim(STUFF( right( convert( varchar(26), m.EndDate, 109 ), 15 ), 7, 7, ' ' ))) 'EndTime',
m.VenueID 'LocationID',
m.MeetingRoomID 'RoomID'
FROM
Meeting m
LEFT OUTER JOIN
MeetingType mt
ON
m.MeetingTypeID=mt.ID
LEFT OUTER JOIN
MeetingTrack mt1
ON
m.ID=mt1.MeetingID
LEFT OUTER JOIN
vwMeetingSessions ms
ON
m.ID=ms.MeetingID
Left JOIN Venue v
ON m.VenueID = v.ID
LEFT OUTER JOIN
MeetingRoom mr
ON
m.MeetingRoomID=mr.ID
INNER JOIN Product p ON m.ProductID=p.ID --Added to show all sessions details
LEFT OUTER JOIN
vwMeetings m1
ON
m.ID=m1.ID
FULL OUTER JOIN
(SELECT ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter1' ,ms.SpeakerID
FROM MeetingSpeaker ms
INNER JOIN Person p ON p.ID = ms.SpeakerID
INNER JOIN Meeting m ON m.ID = ms.MeetingID
WHERE ms.Sequence = 1
) as ms1 ON ms1.MeetingID = m.ID
FULL OUTER JOIN
(
SELECT ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter2' ,ms.SpeakerID
FROM MeetingSpeaker ms
INNER JOIN Person p ON p.ID = ms.SpeakerID
INNER JOIN Meeting m ON m.ID = ms.MeetingID
WHERE ms.Sequence = 2
) as ms2 ON ms1.MeetingID = ms2.MeetingID
FULL OUTER JOIN
(
SELECT ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter3' ,ms.SpeakerID
FROM MeetingSpeaker ms
INNER JOIN Person p ON p.ID = ms.SpeakerID
INNER JOIN Meeting m ON m.ID = ms.MeetingID
WHERE ms.Sequence = 3
) as ms3 ON ms2.MeetingID = ms3.MeetingID
FULL OUTER JOIN
(
SELECT ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter4' ,ms.SpeakerID
FROM MeetingSpeaker ms
INNER JOIN Person p ON p.ID = ms.SpeakerID
INNER JOIN Meeting m ON m.ID = ms.MeetingID
WHERE ms.Sequence = 4
) as ms4 ON ms3.MeetingID = ms4.MeetingID
FULL OUTER JOIN
(
SELECT ms.MeetingID, p.FirstName +' ' +p.Lastname 'Presenter5' ,ms.SpeakerID
FROM MeetingSpeaker ms
INNER JOIN Person p ON p.ID = ms.SpeakerID
INNER JOIN Meeting m ON m.ID = ms.MeetingID
WHERE ms.Sequence = 5
) as ms5 ON ms4.MeetingID = ms5.MeetingID |
Event General InformationMBV Display Name: GeneralInfo
View: EventGeneralInfo
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwGeneralInfos
AS
SELECT
m.ID 'ID',
m.MeetingTitle 'Title',
p.ProductCategory 'Category',
p.Description 'Description',
a.Line1 'Address1',
a.Line2 'Address2',
a.Line3 'Address3',
a.City 'City',
a.StateProvince 'State',
a.PostalCode 'ZipCode',
CASE WHEN pn.AreaCode is null then pn.Phone else '(' + RTRIM(pn.AreaCode) +')'+ pn.Phone end 'Phone',
p.WebProductPage 'Website',
m1.MeetingID 'MeetingID',
m.ProductID 'MeetingProductID'
FROM
Meeting m
INNER JOIN
vwProducts p
ON
m.ProductID=p.ID
LEFT OUTER JOIN
Address a
ON
m.AddressID=a.ID
LEFT OUTER JOIN
PhoneNumber pn
ON
m.PhoneID=pn.ID
LEFT OUTER JOIN
vwMeetings m1
ON
m.ID=m1.ID |
Event TracksMBV Display Name: Track
View: EventTracks
Fields:
Image Removed
Image Added
Code Block |
---|
|
CREATE VIEW [dbo].vwTracks
AS
SELECT
m.ID 'ID',
mt.Name 'TrackName',
mt.Description 'Description',
mt.ID 'TrackID'
FROM
Meeting m
LEFT OUTER JOIN
MeetingTrack mt
ON
m.ID=mt.MeetingID |