If you have a comment on this topic, contact Aptify Documentation. If you want to return to the Aptify Community Site, please click here.

About the Aptify-Eventpedia Integration Points

Owned by admin (Deactivated)
Nov 23, 2017
16 min read

The Aptify and Eventpedia integration were developed using Aptify Service Oriented Architecture (SOA). This document details the integrations points between Aptify and Eventpedia. It lists all the components in Aptify that are required for the Eventpedia integration to work. It also details the fields in Aptify that are used to provide Event Information to Eventpedia.

Aptify SOA Integration Point

Aptify SOA is required for the integration to work. Eventpedia uses the domain authentication within the Aptify Services API. A user enters his or .her domain username in Avodigy's Mobile Content Management portal required for making the authentication call to the Aptify Services API.

Base Views Integration Points

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 information

MBV Display Name: EventInformation
View: EventInfo
Fields:

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 Location

MBV Display Name: EventLocation
View: EventLocation
Fields:

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 Rooms

MBV Display Name: Room
View: EventRooms
Fields:

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 Agenda

MBV Display Name: EventSessions
View: EventSessions
Fields:

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 Sponsors

MBV Display Name: Sponsors
View: EventSponsors
Fields:

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 Attendees

MBV Display Name: Attendees
View: EventAttendees
Fields:

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 Exhibitors

MBV Display Name: Exhibitor
View: EventExhibitors
Fields:

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 Speakers

MBV Display Name: Speakers
View: EventSpeakers
Fields:

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 Sessions

MBV Display Name: Sessions
View: EventSessions
Fields:

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 Information

MBV Display Name: GeneralInfo
View: EventGeneralInfo
Fields:

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 Tracks

MBV Display Name: Track
View: EventTracks
Fields:

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 

Copyright © 2014-2019 Aptify - Confidential and Proprietary