Flowgear Audit for Asia-Pacific

Linda Moyer

Asia-Pacific

(Last updated: 05-Oct-2023 @ 4:00 PM)

By Insights View

vw_accounts

  • Melbourne Sports Center - Unknown ID Feed

vw_booked_spaces

  • Melbourne Sports Center - Booking Feed
  • Melbourne Sports Center - Check Space Availability

vw_contacts

  • EventFinda - Xero

vw_event_item_allocations

  • Melbourne Sports Center - Booking Feed
  • Melbourne Sports Center - Function Feed

vw_event_types

  • Cambelltown Stadium - OIF
  • EventFinda Stadium - OIF
  • Melbourne Sports Center - Unknown ID Feed
  • National Capital Authority - OIF

vw_events

  • Melbourne Sports Center - Function Feed

vw_events_lite

  • VOPS Account Usage Stats

vw_functions

  • Melbourne Sports Center - Function Feed

vw_rooms

  • Melbourne Sports Center - Unknown ID Feed

vw_space_usages

  • Melbourne Sports Center - Unknown ID Feed

vw_venues

  • Melbourne Sports Center - Unknown ID Feed

By API Endpoint

(None) connection

Account :: Create

  • Cambelltown Stadium - OIF
  • EventFinda Stadium - OIF
  • National Capital Authority - OIF
  • Sydney Coliseum - OIF

Accounts :: GetAccounts

  • Task Notifications Scheduler

Contact :: Create

  • Cambelltown Stadium - OIF
  • EventFinda Stadium - OIF
  • National Capital Authority - OIF
  • Sydney Coliseum - OIF

Event :: AddContact

  • Cambelltown Stadium - OIF
  • EventFinda Stadium - OIF
  • National Capital Authority - OIF
  • Sydney Coliseum - OIF

Event :: CreateEvent

  • Cambelltown Stadium - OIF
  • EventFinda Stadium - OIF
  • National Capital Authority - OIF
  • Sydney Coliseum - OIF

Event :: GetEvent

  • Sydney Coliseum - OIF

ReferenceData :: Get

  • Melbourne Sports Center - Unknown ID Feed

TaskNotification :: SendTaskDueEmails

  • Task Notifications Scheduler

VenueOps AP Production Accounts connection

Accounts :: GetAccounts

  • Test
  • VOPS Account Usage Stats

Rooms :: GetRooms

  • Test

Venues :: GetVenues

  • Test

VenueOps AP Production Meetings connection

Account :: Create

  • VenueOps account and contact import

Contact :: Create

  • VenueOps account and contact import

Event :: GetEventsByDateRange

  • Test

By Workflow

Cambelltown Stadium - OIF

Endpoints

  • (None) :: Account :: Create
  • (None) :: Contact :: Create
  • (None) :: Event :: AddContact
  • (None) :: Event :: CreateEvent

Views

  • vw_event_types

Queries

SELECT event_type_id FROM vw_event_types WHERE name = '{EventType}'

EventFinda - Xero

Views

  • vw_contacts

Queries

SELECT name, email FROM vw_contacts
WHERE account_id = '{accountId}'
AND is_active = '1'

SELECT name, email FROM vw_contacts
WHERE account_id = 'account-647-A'
AND is_active = '1'

EventFinda Stadium - OIF

Endpoints

  • (None) :: Account :: Create
  • (None) :: Contact :: Create
  • (None) :: Event :: AddContact
  • (None) :: Event :: CreateEvent

Views

  • vw_event_types

Queries

SELECT event_type_id FROM vw_event_types WHERE name = '{EventType}'

Melbourne Sports Center - Booking Feed

Views

  • vw_booked_spaces
  • vw_event_item_allocations

Queries

SELECT (ISNULL(SELECT  top(1) item_name FROM vw_event_item_allocations WHERE category_name = 'Building Automation Services' and item_name like 'Lighting%' and is_event_wide = 0 and event_id = event_id and item_start_date = func.date and room_id = func.room_id, SELECT  top(1) item_name FROM vw_event_item_allocations WHERE category_name = 'Building Automation Services' and item_name like 'Lighting%' and is_event_wide = 1 and event_id = event_id)) AS Lighting

SELECT
    account_id,
    account_name,
    attendance,
    booked_space_id,
    booked_status_id,
    booked_status_name,
    DATE_FORMAT(date, "%Y-%m-%d") AS date,
    description,
    DATE_FORMAT(end_time, "%H:%i:%S") AS end_time,
    event_booked_on,
    event_created_by_email,
    event_created_by_id,
    event_created_by_name,
    event_id,
    event_is_active,
    event_is_definite,
    event_is_prospect,
    event_is_tentative,
    event_name,
    event_type_id,
    event_type_is_exhibitor_ordering,
    event_type_is_live_entertainment,
    event_type_master_event_type,
    event_type_name,
    event_unique_number,
    is_all_day,
    is_invoiced,
    number_of_hours,
    option_number,
    room_group,
    room_id,
    room_name,
    room_setup,
    series_id,
    series_name,
    space_usage_id,
    space_usage_name,
    space_usage_type_id,
    space_usage_type_name,
    DATE_FORMAT(start_time, "%H:%i:%S") AS start_time,
    venue_id,
    venue_name
FROM 
	vw_booked_spaces
WHERE 
	event_is_definite = 1
	AND event_is_active = 1
    AND date BETWEEN '{StartDate}' AND '{EndDate}'
ORDER BY
	date,
	room_name,
	start_time

Melbourne Sports Center - Check Space Availability

Views

  • vw_booked_spaces

Queries

SELECT 
    `vw_booked_spaces`.`room_id`,
    `vw_booked_spaces`.`room_name`,
    `vw_booked_spaces`.`room_setup`,
    `vw_booked_spaces`.`venue_id`,
    `vw_booked_spaces`.`venue_name`,
    `vw_booked_spaces`.`event_id`,
    `vw_booked_spaces`.`number_of_hours`,
    DATE_FORMAT(`vw_booked_spaces`.`date`, '%Y-%m-%d') AS date,
    DATE_FORMAT(`vw_booked_spaces`.`start_time`, '%H:%i:%s') AS start_time,
    DATE_FORMAT(`vw_booked_spaces`.`end_time`, '%H:%i:%s') AS end_time
FROM (`vw_booked_spaces`)
WHERE 
	((`vw_booked_spaces`.`date` >= '{StartDate}')
	AND (`vw_booked_spaces`.`date` <= '{EndDate}'))
ORDER BY `vw_booked_spaces`.`date`

SELECT 
    `vw_booked_spaces`.`room_id`,
    `vw_booked_spaces`.`room_name`,
    `vw_booked_spaces`.`room_setup`,
    `vw_booked_spaces`.`venue_id`,
    `vw_booked_spaces`.`venue_name`,
    `vw_booked_spaces`.`event_id`,
    `vw_booked_spaces`.`number_of_hours`,
    DATE_FORMAT(`vw_booked_spaces`.`date`, '%Y-%m-%d') AS date,
    DATE_FORMAT(`vw_booked_spaces`.`start_time`, '%H:%i:%s') AS start_time,
    DATE_FORMAT(`vw_booked_spaces`.`end_time`, '%H:%i:%s') AS end_time
FROM (`vw_booked_spaces`)
WHERE 
	((`vw_booked_spaces`.`date` >= '{StartDate}')
	AND (`vw_booked_spaces`.`date` <= '{EndDate}')
    AND (`vw_booked_spaces`.`room_name` LIKE '%{Sport}%'))
ORDER BY `vw_booked_spaces`.`date`

Melbourne Sports Center - Function Feed

Views

  • vw_event_item_allocations
  • vw_events
  • vw_functions

Queries

SELECT (ISNULL(SELECT  top(1) item_name FROM vw_event_item_allocations WHERE category_name = 'Building Automation Services' and item_name like 'Lighting%' and is_event_wide = 0 and event_id = event_id and item_start_date = func.date and room_id = func.room_id, SELECT  top(1) item_name FROM vw_event_item_allocations WHERE category_name = 'Building Automation Services' and item_name like 'Lighting%' and is_event_wide = 1 and event_id = event_id)) AS Lighting

SELECT
	evt.booked_by_name,
	evt.booked_definite_on,
	evt.booked_on,
	evt.description,
	evt.end_date_time,
	evt.estimated_total_attendance,
	evt.event_id AS event_Id,
	evt.event_type_name,
	evt.is_definite,
	evt.name,
	evt.number_of_event_days,
	evt.room_ids,
	evt.room_names,
	evt.start_date_time,
	evt.venue_ids,
	evt.venue_names,
	func.date,
	func.end_time,
	func.expected_attendance,
	func.function_id,
	func.function_type_id,
	func.function_type_name,
	func.guaranteed_attendance,
	func.is_all_day,
	func.number_of_hours,
	func.room_id,
	func.room_name,
	func.start_time,
	func.status_id,
	func.status_name
FROM 
	vw_functions AS func
JOIN 
	vw_events AS evt ON evt.event_id = func.event_id
WHERE 
	is_definite = 1
	AND is_active = 1
    AND func.date BETWEEN '{StartDate}' AND '{EndDate}'
ORDER BY
	func.date,
	func.room_name,
	func.start_time

Melbourne Sports Center - Unknown ID Feed

Endpoints

  • (None) :: ReferenceData :: Get

Views

  • vw_accounts
  • vw_event_types
  • vw_rooms
  • vw_space_usages
  • vw_venues

Queries

SELECT DISTINCT
	name, venue_id
FROM
	vw_venues;

SELECT DISTINCT
	name, space_usage_id
FROM
	vw_space_usages;

SELECT DISTINCT
	name, room_id
FROM
	vw_rooms;

SELECT DISTINCT 
	name, 
	event_type_id 
FROM 
	vw_event_types;

SELECT DISTINCT
	lead_source as leadSource,
    market_segment as marketSegment
FROM 
	vw_accounts;

National Capital Authority - OIF

Endpoints

  • (None) :: Account :: Create
  • (None) :: Contact :: Create
  • (None) :: Event :: AddContact
  • (None) :: Event :: CreateEvent

Views

  • vw_event_types

Queries

SELECT event_type_id FROM vw_event_types WHERE name = '{EventType}'

Sydney Coliseum - OIF

Endpoints

  • (None) :: Account :: Create
  • (None) :: Contact :: Create
  • (None) :: Event :: AddContact
  • (None) :: Event :: CreateEvent
  • (None) :: Event :: GetEvent

Task Notifications Scheduler

Endpoints

  • (None) :: Accounts :: GetAccounts
  • (None) :: TaskNotification :: SendTaskDueEmails

Test

Endpoints

  • VenueOps AP Production Accounts :: Accounts :: GetAccounts
  • VenueOps AP Production Accounts :: Rooms :: GetRooms
  • VenueOps AP Production Accounts :: Venues :: GetVenues
  • VenueOps AP Production Meetings :: Event :: GetEventsByDateRange

VenueOps account and contact import

Endpoints

  • VenueOps AP Production Meetings :: Account :: Create
  • VenueOps AP Production Meetings :: Contact :: Create

VOPS Account Usage Stats

Endpoints

  • VenueOps AP Production Accounts :: Accounts :: GetAccounts

Views

  • vw_events_lite

Queries

use venueops_meetings_{AccountId};
SELECT 
	count(*) as Count
FROM 
	vw_events_lite AS evt
WHERE created_on <= NOW() AND created_on >= NOW() - interval {Interval} day
ORDER BY
	evt.created_on

Comments

0 comments

Article is closed for comments.