MEC – custom booking report + CSV export
There are many WordPress plugins for managing events. Among the most popular ones is the ‘Modern Event Calendar’ by Webnus. The lite version is free to download, but the paid version of MEC (Pro) has more features, including a ticketing system, a booking system and a booking form builder. If you’re planning to organize an event, gather user form submissions, and automatically generate tickets and send them by e-mail – then this plugin is for you! Some of the most important features are: seeing the list of all bookings, filtering results and the ability to export data to a CSV file. All these options are featured in the plugin.
Modern Events Calendar – List of bookings
The default functionality is not enough for us. We would like to create our own report with custom text formatting and the ability to filter data by custom rules. We’re going to fetch the data directly from the database by quering wp_posts and wp_postmeta tables. Bookings saved by the Modern Events Calendar are stored as custom post type ‘mec-books’, events are defined as ‘mec-events’. Custom meta value ‘mec_event_id’ links booking to a particular event. We’re displaying some predefined form fields values (Email, Title, Job Title etc), also additional form fields data will be rendered.
<?php
// functions.php
/**
* MEC - custom booking report + CSV export
* Modern Events calendar, version 5.10.0
*/
function ct_get_booking_data($flat_data = false){
$args = array(
'post_type'=> 'mec-books',
'post_status' => 'any',
'posts_per_page' => -1,
'orderby' => 'ID',
'order' => 'ASC'
);
if(isset($_GET['eventid'])) {
$args['meta_key'] = 'mec_event_id';
$args['meta_value'] = intval(sanitize_title($_GET['eventid']));
}
$myposts = get_posts($args);
$counter = 0;
$data = array();
foreach($myposts as $post):
$post->mailchimpAgree = false;
$item_id = $post->ID;
$mec_event_id = get_post_meta( $item_id, 'mec_event_id', true );
$mec_attendees = get_post_meta( $item_id, 'mec_attendees', true );
$booking_time = get_post_meta( $item_id, 'mec_booking_time', true );
$row = maybe_unserialize($mec_attendees);
$additional_fields_json = array();
$reg_fields = ct_get_reg_fields($mec_event_id);
$additional_fields = ct_get_attendees_data($mec_attendees, $reg_fields);
// save user email
$post->user_email = $row[0]['email'];
$post->user_fullname = $row[0]['name'];
foreach($row[0]['reg'] as $elem_key => $row_item){
if(is_array($row_item)){
$row_item = $row_item[0];
}
if($elem_key == '5'){
$post->user_title = $row_item;
}
if($elem_key == '2'){
$post->company = $row_item;
}
if($elem_key == '7'){
$post->job_title = $row_item;
}
if($row_item == 'I would like stay informed on future events'){
$post->mailchimpAgree = true;
}
}
foreach($additional_fields as $elem_key => $row_item){
$additional_fields_json[] = $row_item;
}
if($flat_data){
// hack - to render array as string value in CSV
$additional_fields_string = '';
foreach($additional_fields_json[0] as $additional_info):
foreach($additional_info as $add_row_key => $add_row):
$additional_fields_string .= "".$add_row_key . ": " . $add_row . "\n";
endforeach;
endforeach;
$additional_fields_json = $additional_fields_string;
}
if(TRUE) {
// if($post->mailchimpAgree) {
$x = array();
$x[] = ''; // empty counter
// $x[] = $post->post_title;
$x[] = $post->user_fullname;
$x[] = $booking_time;
$x[] = $post->user_title;
$x[] = $post->company;
$x[] = $post->job_title;
$x[] = get_the_title($mec_event_id);
$x[] = $post->user_email;
$x[] = $post->mailchimpAgree;
$x[] = $additional_fields_json;
// Show only recent user booking
// $data[$post->user_email] = $x;
// Show all bookings (even if email used multiple times)
$data[$counter] = $x;
$counter = $counter + 1;
// }
}
endforeach;
return $data;
}
function ct_get_booking_raport(){
if ( current_user_can('administrator')):
if(is_admin()):
$myposts = ct_get_booking_data();
$counter = 0;
echo "<h1>All Bookings for events ". date("Y-m-d") .":</h1>";
ct_dropdown_events();
// key = user_email
foreach($myposts as $key => $post):
if (TRUE) {
if($counter == 0){
/**
* CSV button
*/
$download_csv_url = '/wp-admin/?ct_booking_raport_download=yes';
$download_csv_text = 'Export to CSV file';
if(isset($_GET['eventid'])) {
$download_csv_url = $download_csv_url .'&eventid='. intval(sanitize_title($_GET['eventid']));
$download_csv_text = 'Export single event to CSV file';
}
echo '<p><a href="'. $download_csv_url .'" style="display:inline-block; text-decoration:none; text-transform:uppercase; font-size:18px; background:#000; color:#fff; padding:8px 16px;">' . $download_csv_text .'</a></p>';
echo "<p><hr></p>";
}
$counter = $counter + 1;
printf("<div style='border-bottom:1px solid #ccc; padding:15px 15px 15px 15px; margin-bottom:5px;'>");
printf("<strong>" . intval($counter) . ".</strong> ");
printf($post[1] . "<br>");
printf("<strong>Email:</strong> ".$post[7] . "<br>");
printf("<strong>Submission date:</strong> ".$post[2]. "<br>");
printf("<strong>Title (Mr, Ms, Dr, ...):</strong> ". $post[3] . "<br>");
printf("<strong>Organisation / Company:</strong> " . $post[4] . "<br>");
printf("<strong>Job Title:</strong> ". $post[5]. "<br>");
printf("<strong>Event name:</strong> ".$post[6] . "<br>");
$mailchimp_agreed = $post[8] ? 'true' : 'false';
printf("<strong>Mailchimp agreed:</strong> " . $mailchimp_agreed. "<br>");
printf( "<br>Additional form fields:<br>");
foreach($post[9][0] as $additional_info):
foreach($additional_info as $add_row_key => $add_row):
print_r( "<strong>".$add_row_key . "</strong>: " . $add_row . "<br>");
endforeach;
endforeach;
printf("</div>" . "");
}
endforeach;
if(empty($myposts)){
echo "<h2>Result empty. Nothing found.</h2>";
}
exit;
endif;
endif;
}
The plugin stores attendee details as a serialized array (meta_key : mec_attendees). It was tricky to extract the proper data from this field. After some research and analyzing plugin source code, we’ve used the logic from
wp-content/plugins/modern-events-calendar/app/libraries/main.php and wp-content/plugins/modern-events-calendar/app/features/books.php
We’ve adapted three plugin functions code and created our own helper functions to extract data from a serialized array. We’ve already displayed predefined fields, so we’re skipping those items here. The fields don’t have keys, so we need to rely on the field_id variable number.
/**
* Logic taken from
* wp-content/plugins/modern-events-calendar/app/libraries/main.php
*/
function ct_get_reg_fields($mec_event_id){
$options = get_option('mec_options', array());
$reg_fields = isset($options['reg_fields']) ? $options['reg_fields'] : array();
// Event Booking Fields
if($mec_event_id)
{
$global_inheritance = get_post_meta($mec_event_id, 'mec_reg_fields_global_inheritance', true);
if(trim($global_inheritance) == '') $global_inheritance = 1;
if(!$global_inheritance)
{
$event_reg_fields = get_post_meta($mec_event_id, 'mec_reg_fields', true);
if(is_array($event_reg_fields)) $reg_fields = $event_reg_fields;
}
}
return apply_filters('mec_get_reg_fields', $reg_fields, $mec_event_id);
}
/**
* Logic copied from:
* wp-content/plugins/modern-events-calendar/app/features/books.php
*/
function ct_get_attendees_data($attendees, $reg_fields){
$bookings = array();
$ct_get_labels = ct_get_label($reg_fields);
foreach($attendees as $key => $attendee)
{
if($key === 'attachments') continue;
if(isset($attendee[0]['MEC_TYPE_OF_DATA'])) continue;
$reg_form = isset($attendee['reg']) ? $attendee['reg'] : array();
foreach($reg_fields as $field_id=>$reg_field)
{
$temp = array();
// Placeholder Keys
if(!is_numeric($field_id)) continue;
// skip main fields
if($field_id == '5'){
continue;
}
if($field_id == '2'){
continue;
}
if($field_id == '7'){
continue;
}
if($field_id == '4'){
continue;
}
$type = isset($reg_field['type']) ? $reg_field['type'] : '';
$label = isset($reg_field['label']) ? __($reg_field['label'], 'mec') : $ct_get_labels[$field_id];
if(trim($label) == '' or $type == 'name' or $type == 'mec_email') continue;
$temp[$label] = isset($reg_form[$field_id]) ? ((is_string($reg_form[$field_id]) and trim($reg_form[$field_id])) ? $reg_form[$field_id] : (is_array($reg_form[$field_id]) ? implode(' | ', $reg_form[$field_id]) : '---')) : '';
$booking[] = $temp;
}
$bookings[] = $booking;
}
return $bookings;
}
function ct_get_label($reg_fields){
$columns = array();
foreach($reg_fields as $reg_field_key=>$reg_field)
{
// Placeholder Keys
if(!is_numeric($reg_field_key)) continue;
$type = isset($reg_field['type']) ? $reg_field['type'] : '';
$label = isset($reg_field['label']) ? __($reg_field['label'], 'mec') : '';
if(trim($label) == '' or $type == 'name' or $type == 'mec_email') continue;
if($type == 'agreement') $label = sprintf($label, get_the_title($reg_field['page']));
$columns[] = $label;
}
return $columns;
}
Helper functions
Some additional functions for rendering events dropdown and parsing urls are also defined:
function ct_get_current_url_without_params(){
$current_link = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://$_SERVER[HTTP_HOST]$_SERVER[REQUEST_URI]";
$old_param_post = strpos($current_link,"&");
if($old_param_post){
$current_link = substr($current_link, 0, $old_param_post);
}
return $current_link;
}
function ct_dropdown_events(){
$args = array(
'post_type'=> 'mec-events',
'post_status' => 'any',
'posts_per_page' => -1,
'orderby' => 'ID',
'order' => 'DESC'
);
$current_link = ct_get_current_url_without_params();
$posts = get_posts($args); ?>
<div style="background:#f5f5f5; padding:20px;">
<?php if(! isset($_GET['eventid'])): ?>
<label>Filter by event</label>
<select style="padding:5px 10px; font-size:18px; width:100%; border:2px solid #333; box-sizing:border-box;"
onchange="this.options[this.selectedIndex].value && (window.location = this.options[this.selectedIndex].value);" >
<option value="">---</option>
<?php
foreach( $posts as $post ) :
$selected = '';
if(intval(sanitize_title($_GET['eventid'])) == $post->ID ){
$selected = 'selected="selected"';
}
?>
<option value="<?php echo $current_link; ?>&eventid=<? echo $post->ID; ?>" <?php echo $selected; ?>><?php echo $post->post_title; ?></option>
<?php endforeach; ?>
</select>
<?php else: ?>
<h1>Event <?php echo get_the_title(intval(sanitize_title($_GET['eventid']))); ?></h1>
<?php endif; ?>
<?php if(isset($_GET['eventid'])): ?>
<p><a href="<?php echo ct_get_current_url_without_params(); ?>">Go back to main list</a></p>
<?php endif; ?>
</div>
<div style="clear:both;"></div>
<?php
}
Export bookings to CSV
Now, it would be nice to add a functionality to export the entire list to an Excel/CSV file. Our function is available for WordPress users with an Administrator role. The script uses already fetched data, define proper column names and download the CSV report.
function ct_generate_csv_from_array($data){
if ( current_user_can('administrator')):
if(is_admin()):
// add columns
$columns_names = array(
"ID",
"User",
"Submission date",
"Title (Mr, Ms, Dr, ...)",
"Organisation / Company",
"Job Title",
"Event name",
"E-mail",
"Agreed to mailchimp",
"Additional form fields"
);
array_unshift($data , $columns_names);
/**
$x[] = $post->counter;
$x[] = $post->post_title;
$x[] = $post->post_date;
$x[] = $post->user_title;
$x[] = $post->company;
$x[] = $post->job_title;
$x[] = get_the_title($mec_event_id);
$x[] = $post->user_email;
$x[] = $post->mailchimpAgree;
*/
$filename_variation = 'all-';
if(isset($_GET['eventid'])) {
$filename_variation = sanitize_title(get_the_title(intval(sanitize_title($_GET['eventid']))))."-";
}
$csv_filename = "bookings-".$filename_variation.time().".csv";
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="'. $csv_filename .'"');
$fp = fopen('php://output', 'wb');
$myCounter = 0;
foreach ($data as $line) {
$line = json_decode(json_encode($line), true);
if($myCounter == 0){
$line[0] = '';
} else {
$line[0] = $myCounter;
}
fputcsv($fp, $line, ',');
$myCounter = $myCounter + 1;
}
fclose($fp);
exit;
endif;
endif;
}
The last step will be to trigger those PHP functions on “init” hook. WordPress will check if proper GET param is available and then initialize the script.
add_action("init","ct_init_get_booking_raport");
function ct_init_get_booking_raport(){
if (isset($_GET["ct_booking_raport"])):
ct_get_booking_raport();
endif;
}
add_action("init","ct_init_download_booking_raport");
function ct_init_download_booking_raport(){
if (isset($_GET["ct_booking_raport_download"])):
$myposts = ct_get_booking_data(true);
ct_generate_csv_from_array($myposts);
endif;
}
Test custom functions
After adding the code to functions.php, you can log in as WP Admin and open the url in the browser: /wp-admin/?ct_booking_raport=yes . You will be able to see the booking list from the Modern Events Calendar, filter bookings by events, use the ‘Export to CSV file’ and ‘Export single event to CSV file’ buttons.
Filter results
With our customized functions we have the ability to filter data of submitted Bookings. Let’s say we would like to have a list of all user e-mails (without duplicates) that checked the checkbox: ‘I would like to stay informed on future events’. These users should be added to our Mailchimp List and will get the newsletter before the next event. Only minor modifications are needed and we will get those e-mails.
/**
* Modification to: get unique e-mails with mailchimpAgree field set as true
*/
function ct_get_booking_data($flat_data = false){
(...)
foreach($myposts as $post):
(...)
if($post->mailchimpAgree) {
$x = array();
(...)
// Show only recent user booking
$data[$post->user_email] = $x;
// Show all bookings (even if email used multiple times)
// $data[$counter] = $x;
$counter = $counter + 1;
}
endforeach;
return $data;
}
That’s it, make sure you follow us for other useful tips and guidelines.
