_test

// This is the code from including in events to show current bookings
// The code should be pasted into file httpdocs/boats-booked.php

//First create the views (or replace it if it already exists)
//first one selects only relevant records from standard WP user meta table
$createmeta = “CREATE OR REPLACE VIEW umeta AS SELECT user_id, meta_value FROM `wp_usermeta` WHERE meta_key = ‘dbem_phone'”;
$result = $wpdb->query($createmeta);
//second create a view based on bookings and links to user tables
$createview = “CREATE OR REPLACE VIEW ccrc_events AS SELECT wp_em_events.event_id AS event_id, event_status, event_name, event_start_date, ticket_name, ticket_booking_spaces, booking_status, person_id, wp_users.user_email AS user_email, wp_em_bookings.booking_id, booking_comment, booking_meta, display_name, meta_value, wp_em_bookings.booking_date AS bookingdate FROM `wp_em_tickets_bookings` JOIN `wp_em_bookings` ON wp_em_tickets_bookings.booking_id = wp_em_bookings.booking_id JOIN `wp_em_tickets` ON wp_em_tickets_bookings.ticket_id = wp_em_tickets.ticket_id JOIN `wp_em_events` ON wp_em_bookings.event_id = wp_em_events.event_id LEFT JOIN `wp_users` ON person_id = ID LEFT JOIN `umeta` ON person_id = umeta.user_id ORDER BY event_start_date, person_id, ticket_description”;
$result = $wpdb->query($createview);

// get the current event from the URL and then find its details
$currentevent = $post_url=get_permalink();
$currentevent = substr($currentevent,strpos($currentevent,”/”,22)+1);
$currentevent = substr($currentevent,0,strlen($currentevent)-1);
$geteventslist=”SELECT DISTINCT event_id, event_name FROM wp_em_events WHERE event_status = ‘1’ AND event_slug= ‘” . $currentevent . “‘”;
$events=$wpdb->get_results($geteventslist) or die(mysql_error());

// Report By Boat Booking
echo “

Bookings (inc unconfirmed) for ” . $events[0]->event_name . ” include:
“;
$bookingselect = “SELECT DISTINCT booking_id, booking_status, person_id, booking_meta, booking_comment, display_name, meta_value, user_email FROM ccrc_events WHERE event_id ='” . $events[0]->event_id . “‘”;
$bookings=$wpdb->get_results($bookingselect);
$boatlist = “”;
foreach ( $bookings as $booking )
{
$metadata = unserialize($booking->booking_meta);
if ($booking->person_id == “0”) {
echo $metadata[‘registration’][‘dbem_phone’] . “, “;
$boatlist = $boatlist . $metadata[‘registration’][‘dbem_phone’] . “, “;
} else {
echo $booking->meta_value . “, “;
$boatlist = $boatlist . $booking->meta_value . “, “;
}
}
echo “

“;include