function espresso_export_stuff()
{
$today = date("Y-m-d-Hi", time());
$export_all_events = isset($_REQUEST['all_events']) && $_REQUEST['all_events'] == "true" ? TRUE : FALSE;
//Export data to Excel file
if (isset($_REQUEST['export'])) {
switch ($_REQUEST['export']) {
case "report":
global $wpdb;
$event_id = isset($_REQUEST['event_id']) ? $_REQUEST['event_id'] : FALSE;
// export for one event only ?
if ($event_id) {
$SQL = "SELECT event_name, event_desc, event_identifier, question_groups, event_meta FROM " . EVENTS_DETAIL_TABLE;
$SQL .= " WHERE id = %d";
if ($results = $wpdb->get_row($wpdb->prepare($SQL, $event_id), ARRAY_N)) {
list($event_name, $event_description, $event_identifier, $question_groups, $event_meta) = $results;
$question_groups = maybe_unserialize($question_groups);
$event_meta = maybe_unserialize($event_meta);
if (!empty($event_meta['add_attendee_question_groups'])) {
$question_groups = array_unique(array_merge((array) $question_groups, (array) $event_meta['add_attendee_question_groups']));
}
}
} else {
// export for ALL EVENTS
$question_groups = array();
$event_meta = array();
$SQL = "SELECT event_name, event_desc, event_identifier, question_groups, event_meta FROM " . EVENTS_DETAIL_TABLE;
if ($results = $wpdb->get_results($SQL, ARRAY_N)) {
foreach ($results as $result) {
list($event_name, $event_description, $event_identifier, $q_groups, $e_meta) = $result;
$question_groups = array_unique(array_merge($question_groups, (array) maybe_unserialize($q_groups)));
$e_meta = (array) maybe_unserialize($e_meta);
$event_meta = array_unique(array_merge($event_meta, (array) $e_meta['add_attendee_question_groups']));
}
}
}
$basic_header = array(__('Group', 'event_espresso'), __('ID', 'event_espresso'), __('Reg ID', 'event_espresso'), __('Payment Method', 'event_espresso'), __('Reg Date', 'event_espresso'), __('Pay Status', 'event_espresso'), __('Type of Payment', 'event_espresso'), __('Transaction ID', 'event_espresso'), __('Price', 'event_espresso'), __('Coupon Code', 'event_espresso'), __('# Attendees', 'event_espresso'), __('Amount Paid', 'event_espresso'), __('Date Paid', 'event_espresso'), __('Event Name', 'event_espresso'), __('Price Option', 'event_espresso'), __('Event Date', 'event_espresso'), __('Event Time', 'event_espresso'), __('Website Check-in', 'event_espresso'), __('Tickets Scanned', 'event_espresso'), __('Seat Tag', 'event_espresso'), __('First Name', 'event_espresso'), __('Last Name', 'event_espresso'), __('Email', 'event_espresso'));
$question_groups = maybe_unserialize($question_groups);
$event_meta = maybe_unserialize($event_meta);
if (isset($event_meta['add_attendee_question_groups'])) {
// if ( is_serialized( $event_meta['add_attendee_question_groups'] ) ){
// $add_attendee_question_groups = unserialize($event_meta['add_attendee_question_groups']);
// } else {
// $add_attendee_question_groups = $event_meta['add_attendee_question_groups'];
// }
if (!empty($add_attendee_question_groups)) {
$question_groups = array_unique(array_merge((array) $question_groups, (array) $event_meta['add_attendee_question_groups']));
}
}
switch ($_REQUEST['action']) {
case "event":
espresso_event_export($event_name);
break;
case "payment":
$question_list = array();
//will be used to associate questions with correct answers
$question_filter = array();
//will be used to keep track of newly added and deleted questions
if (count($question_groups) > 0) {
$question_sequence = array();
$questions_in = '';
foreach ($question_groups as $g_id) {
$questions_in .= $g_id . ',';
}
$questions_in = substr($questions_in, 0, -1);
$group_name = '';
$counter = 0;
$quest_sql = "SELECT q.id, q.question FROM " . EVENTS_QUESTION_TABLE . " q ";
$quest_sql .= " JOIN " . EVENTS_QST_GROUP_REL_TABLE . " qgr on q.id = qgr.question_id ";
$quest_sql .= " JOIN " . EVENTS_QST_GROUP_TABLE . " qg on qg.id = qgr.group_id ";
$quest_sql .= " WHERE qgr.group_id in ( {$questions_in} ) ";
if (function_exists('espresso_member_data') && espresso_member_data('role') == 'espresso_event_manager') {
$quest_sql .= " AND qg.wp_user = '******'id') . "' ";
}
//Fix from Jesse in the forums (http://eventespresso.com/forums/2010/10/form-questions-appearing-in-wrong-columns-in-excel-export/)
//$quest_sql .= " AND q.system_name is null ORDER BY qg.id, q.id ASC ";
//$quest_sql .= " AND q.system_name is null ";
$quest_sql .= " ORDER BY q.sequence, q.id ASC ";
$questions = $wpdb->get_results($quest_sql);
$ignore = array(1, 2, 3);
$num_rows = $wpdb->num_rows;
if ($num_rows > 0) {
foreach ($questions as $question) {
if (!isset($ignore[$question->id])) {
$question_list[$question->id] = $question->question;
$question_filter[$question->id] = $question->id;
array_push($basic_header, escape_csv_val(stripslashes($question->question)));
//array_push($question_sequence, $question->sequence);
}
}
}
}
if (count($question_filter) > 0) {
$question_filter = implode(",", $question_filter);
}
//$question_filter = str_replace( array( '1,','2,','3,' ), '', $question_filter );
$sql = '';
$espresso_member = function_exists('espresso_member_data') && espresso_member_data('role') == 'espresso_group_admin' ? TRUE : FALSE;
if ($espresso_member) {
$group = get_user_meta(espresso_member_data('id'), "espresso_group", true);
$group = maybe_unserialize($group);
$group = implode(",", $group);
$sql .= "(SELECT ed.event_name, ed.start_date, a.id AS att_id, a.registration_id, a.payment, a.date, a.payment_status, a.txn_type, a.txn_id";
$sql .= ", a.amount_pd, a.quantity, a.coupon_code, a.checked_in, a.checked_in_quantity";
$sql .= ", a.payment_date, a.event_time, a.price_option, a.final_price a_final_price, a.quantity a_quantity, a.fname, a.lname, a.email";
$sql .= " FROM " . EVENTS_ATTENDEE_TABLE . " a ";
$sql .= " JOIN " . EVENTS_DETAIL_TABLE . " ed ON ed.id=a.event_id ";
if ($group != '') {
$sql .= " JOIN " . EVENTS_VENUE_REL_TABLE . " r ON r.event_id = ed.id ";
$sql .= " JOIN " . EVENTS_LOCALE_REL_TABLE . " l ON l.venue_id = r.venue_id ";
}
$sql .= $event_id ? " WHERE ed.id = '" . $event_id . "' " : '';
$sql .= $group != '' ? " AND l.locale_id IN (" . $group . ") " : '';
$sql .= ") UNION (";
}
$sql .= "SELECT ed.event_name, ed.start_date, a.id AS att_id, a.registration_id, a.payment, a.date, a.payment_status, a.txn_type, a.txn_id";
$sql .= ", a.quantity, a.coupon_code, a.checked_in, a.checked_in_quantity, a.final_price a_final_price, a.amount_pd, a.quantity a_quantity";
$sql .= ", a.payment_date, a.event_time, a.price_option, a.fname, a.lname, a.email";
$sql .= " FROM " . EVENTS_ATTENDEE_TABLE . " a ";
$sql .= " JOIN " . EVENTS_DETAIL_TABLE . " ed ON ed.id=a.event_id ";
//$sql .= " JOIN " . EVENTS_ATTENDEE_COST_TABLE . " ac ON a.id=ac.attendee_id ";
$sql .= $event_id ? " WHERE ed.id = '" . $event_id . "' " : '';
if (function_exists('espresso_member_data') && (espresso_member_data('role') == 'espresso_event_manager' || espresso_member_data('role') == 'espresso_group_admin')) {
$sql .= " AND ed.wp_user = '******'id') . "' ";
}
$sql .= $espresso_member ? ") ORDER BY att_id " : " ORDER BY a.id ";
$participants = $wpdb->get_results($sql);
$filename = isset($_REQUEST['all_events']) && $_REQUEST['all_events'] == "true" ? __('all-events', 'event_espresso') : sanitize_title_with_dashes($event_name);
$filename = $filename . "-" . $today;
switch ($_REQUEST['type']) {
case "csv":
$st = "";
$et = ",";
$s = $et . $st;
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=" . $filename . ".csv");
//header("Content-Disposition: attachment; filename='" .$filename .".csv'");
header("Pragma: no-cache");
header("Expires: 0");
//echo header
echo implode($s, $basic_header) . "\r\n";
break;
default:
$st = "";
$et = "\t";
$s = $et . $st;
header("Content-Disposition: attachment; filename=" . $filename . ".xls");
//header("Content-Disposition: attachment; filename='" .$filename .".xls'");
header("Content-Type: application/vnd.ms-excel");
header("Pragma: no-cache");
header("Expires: 0");
//echo header
echo implode($s, $basic_header) . $et . "\r\n";
break;
}
if ($participants) {
$temp_reg_id = '';
//will temporarily hold the registration id for checking with the next row
$attendees_group = '';
//will hold the names of the group members
$group_counter = 1;
$amount_pd = 0;
foreach ($participants as $participant) {
if ($temp_reg_id == '') {
$temp_reg_id = $participant->registration_id;
$amount_pd = $participant->amount_pd;
}
if ($temp_reg_id == $participant->registration_id) {
//Do nothing
} else {
$group_counter++;
$temp_reg_id = $participant->registration_id;
}
$attendees_group = "Group {$group_counter}";
//Build the seating assignment
$seatingchart_tag = '';
if (defined("ESPRESSO_SEATING_CHART")) {
if (class_exists("seating_chart")) {
if (seating_chart::check_event_has_seating_chart($event_id)) {
$rs = $wpdb->get_row("select scs.* from " . EVENTS_SEATING_CHART_EVENT_SEAT_TABLE . " sces inner join " . EVENTS_SEATING_CHART_SEAT_TABLE . " scs on sces.seat_id = scs.id where sces.attendee_id = " . $participant->att_id);
if ($rs !== NULL) {
$participant->seatingchart_tag = $rs->custom_tag . " " . $rs->seat . " " . $rs->row;
}
}
}
} else {
$participant->seatingchart_tag = '';
}
echo $attendees_group . $s . escape_csv_val($participant->att_id) . $s . escape_csv_val($participant->registration_id) . $s . escape_csv_val(stripslashes($participant->payment)) . $s . escape_csv_val(stripslashes(event_date_display($participant->date, get_option('date_format')))) . $s . escape_csv_val(stripslashes($participant->payment_status)) . $s . escape_csv_val(stripslashes($participant->txn_type)) . $s . escape_csv_val(stripslashes($participant->txn_id)) . $s . escape_csv_val($participant->a_final_price * $participant->a_quantity) . $s . escape_csv_val($participant->coupon_code) . $s . escape_csv_val($participant->quantity) . $s . escape_csv_val($participant->amount_pd) . $s . escape_csv_val(event_date_display($participant->payment_date, get_option('date_format'))) . $s . escape_csv_val($participant->event_name) . $s . escape_csv_val($participant->price_option) . $s . escape_csv_val(event_date_display($participant->start_date, get_option('date_format'))) . $s . escape_csv_val(event_date_display($participant->event_time, get_option('time_format'))) . $s . escape_csv_val($participant->checked_in) . $s . escape_csv_val($participant->checked_in_quantity) . $s . escape_csv_val($participant->seatingchart_tag) . $s . escape_csv_val($participant->fname) . $s . escape_csv_val($participant->lname) . $s . escape_csv_val($participant->email);
$SQL = "SELECT question_id, answer FROM " . EVENTS_ANSWER_TABLE . " ";
$SQL .= "WHERE question_id IN ({$question_filter}) AND attendee_id = %d";
$answers = $wpdb->get_results($wpdb->prepare($SQL, $participant->att_id), OBJECT_K);
foreach ($question_list as $k => $v) {
// in case the event organizer removes a question from a question group,
// the orphaned answers will remian in the answers table. This check will make sure they don't get exported.
$search = array("\r", "\n", "\t");
if (isset($answers[$k])) {
$clean_answer = str_replace($search, " ", $answers[$k]->answer);
$clean_answer = stripslashes(str_replace("'", "'", trim($clean_answer)));
$clean_answer = escape_csv_val($clean_answer);
echo $s . $clean_answer;
} else {
echo $s;
}
}
switch ($_REQUEST['type']) {
case "csv":
echo "\r\n";
break;
default:
echo $et . "\r\n";
break;
}
}
} else {
echo __('No participant data has been collected.', 'event_espresso');
}
exit;
break;
default:
echo '<p>' . __('This Is Not A Valid Selection!', 'event_espresso') . '</p>';
break;
}
default:
break;
}
}
}
function espresso_export_stuff()
{
//Export data to Excel file
if (isset($_REQUEST['export'])) {
switch ($_REQUEST['export']) {
case "report":
global $wpdb;
$event_id = $_REQUEST['event_id'];
$today = date("Y-m-d-Hi", time());
$sql_x = "SELECT id, event_name, event_desc, event_identifier, question_groups, event_meta FROM " . EVENTS_DETAIL_TABLE;
$sql_x .= $_REQUEST['all_events'] == "true" ? '' : " WHERE id = '" . $event_id . "' ";
//echo $sql_x;
$results = $wpdb->get_row($sql_x, ARRAY_N);
list($event_id, $event_name, $event_description, $event_identifier, $question_groups, $event_meta) = $results;
$basic_header = array(__('Group', 'event_espresso'), __('Reg ID', 'event_espresso'), __('Last Name', 'event_espresso'), __('First Name', 'event_espresso'), __('Email', 'event_espresso'), __('Address', 'event_espresso'), __('Address 2', 'event_espresso'), __('City', 'event_espresso'), __('State', 'event_espresso'), __('Zip', 'event_espresso'), __('Phone', 'event_espresso'), __('Payment Method', 'event_espresso'), __('Reg Date', 'event_espresso'), __('Pay Status', 'event_espresso'), __('Type of Payment', 'event_espresso'), __('Transaction ID', 'event_espresso'), __('Payment', 'event_espresso'), __('Coupon Code', 'event_espresso'), __('# Attendees', 'event_espresso'), __('Date Paid', 'event_espresso'), __('Event Name', 'event_espresso'), __('Price Option', 'event_espresso'), __('Event Date', 'event_espresso'), __('Event Time', 'event_espresso'), __('Website Check-in', 'event_espresso'), __('Tickets Scanned', 'event_espresso'));
$question_groups = unserialize($question_groups);
$event_meta = unserialize($event_meta);
if (isset($event_meta['add_attendee_question_groups'])) {
$add_attendee_question_groups = $event_meta['add_attendee_question_groups'];
if (!empty($add_attendee_question_groups)) {
$question_groups = array_unique(array_merge((array) $question_groups, (array) $add_attendee_question_groups));
}
}
switch ($_REQUEST['action']) {
case "event":
espresso_event_export($event_name);
break;
case "payment":
$question_list = array();
//will be used to associate questions with correct answers
$question_filter = array();
//will be used to keep track of newly added and deleted questions
if (count($question_groups) > 0) {
$questions_in = '';
$question_sequence = array();
/*foreach ($question_groups as $g_id)
{
$questions_in .= $g_id . ',';
}*/
$questions_in = implode(",", $question_groups);
/*$questions_in = substr($questions_in,0,-1);*/
$group_name = '';
$counter = 0;
$quest_sql = "SELECT q.id, q.question FROM " . EVENTS_QUESTION_TABLE . " q ";
$quest_sql .= " JOIN " . EVENTS_QST_GROUP_REL_TABLE . " qgr on q.id = qgr.question_id ";
$quest_sql .= " JOIN " . EVENTS_QST_GROUP_TABLE . " qg on qg.id = qgr.group_id ";
$quest_sql .= " WHERE qgr.group_id in ( " . $questions_in . ") ";
if (function_exists('espresso_member_data') && (espresso_member_data('role') == 'espresso_event_manager' || espresso_member_data('role') == 'espresso_group_admin')) {
$quest_sql .= " AND qg.wp_user = '******'id') . "' ";
}
//Fix from Jesse in the forums (http://eventespresso.com/forums/2010/10/form-questions-appearing-in-wrong-columns-in-excel-export/)
//$quest_sql .= " AND q.system_name is null ORDER BY qg.id, q.id ASC ";
$quest_sql .= " AND q.system_name is null ORDER BY q.sequence, q.id ASC ";
$questions = $wpdb->get_results($quest_sql);
$num_rows = $wpdb->num_rows;
if ($num_rows > 0) {
foreach ($questions as $question) {
$question_list[$question->id] = $question->question;
$question_filter[$question->id] = $question->id;
array_push($basic_header, escape_csv_val($question->question, $_REQUEST['type']));
//array_push($question_sequence, $question->sequence);
}
}
}
if (count($question_filter) > 0) {
$question_filter = implode(",", $question_filter);
}
//$participants = $wpdb->get_results("SELECT * FROM ".EVENTS_ATTENDEE_TABLE." WHERE event_id = '$event_id'");
//$participants = $wpdb->get_results("SELECT ed.event_name, ed.start_date, a.id, a.lname, a.fname, a.email, a.address, a.city, a.state, a.zip, a.phone, a.payment, a.date, a.payment_status, a.txn_type, a.txn_id, a.amount_pd, a.quantity, a.coupon_code, a.payment_date, a.event_time, a.price_option FROM " . EVENTS_ATTENDEE_TABLE . " a JOIN " . EVENTS_DETAIL_TABLE . " ed ON ed.id=a.event_id WHERE ed.id = '" . $event_id . "'");
$sql = "(";
if (function_exists('espresso_member_data') && espresso_member_data('role') == 'espresso_group_admin') {
$group = get_user_meta(espresso_member_data('id'), "espresso_group", true);
$group = unserialize($group);
$group = implode(",", $group);
$sql .= "SELECT ed.event_name, ed.start_date, a.id, a.registration_id, a.lname, a.fname, a.email, a.address, a.address2, a.city";
$sql .= ", a.state, a.zip, a.phone, a.payment, a.date, a.payment_status, a.txn_type, a.txn_id";
$sql .= ", a.amount_pd, a.quantity, a.coupon_code, a.checked_in, a.checked_in_quantity";
$sql .= ", a.payment_date, a.event_time, a.price_option";
$sql .= " FROM " . EVENTS_ATTENDEE_TABLE . " a ";
$sql .= " JOIN " . EVENTS_DETAIL_TABLE . " ed ON ed.id=a.event_id ";
if ($group != '') {
$sql .= " JOIN " . EVENTS_VENUE_REL_TABLE . " r ON r.event_id = ed.id ";
$sql .= " JOIN " . EVENTS_LOCALE_REL_TABLE . " l ON l.venue_id = r.venue_id ";
}
$sql .= $_REQUEST['all_events'] == "true" ? '' : " WHERE ed.id = '" . $event_id . "' ";
$sql .= $group != '' ? " AND l.locale_id IN (" . $group . ") " : '';
$sql .= ") UNION (";
}
$sql .= "SELECT ed.event_name, ed.start_date, a.id, a.registration_id, a.lname, a.fname, a.email, a.address, a.address2, a.city";
$sql .= ", a.state, a.zip, a.phone, a.payment, a.date, a.payment_status, a.txn_type, a.txn_id";
$sql .= ", a.amount_pd, a.quantity, a.coupon_code, a.checked_in, a.checked_in_quantity";
$sql .= ", a.payment_date, a.event_time, a.price_option";
$sql .= " FROM " . EVENTS_ATTENDEE_TABLE . " a ";
$sql .= " JOIN " . EVENTS_DETAIL_TABLE . " ed ON ed.id=a.event_id ";
$sql .= $_REQUEST['all_events'] == "true" ? '' : " WHERE ed.id = '" . $event_id . "' ";
if (function_exists('espresso_member_data') && (espresso_member_data('role') == 'espresso_event_manager' || espresso_member_data('role') == 'espresso_group_admin')) {
$sql .= " AND ed.wp_user = '******'id') . "' ";
}
$sql .= ") ORDER BY id ";
$participants = $wpdb->get_results($sql);
$filename = $_REQUEST['all_events'] == "true" ? __('all-events', 'event_espresso') : sanitize_title_with_dashes($event_name);
$filename = $filename . "-" . $today;
switch ($_REQUEST['type']) {
case "csv":
$st = "";
$et = ",";
$s = $et . $st;
header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=" . $filename . ".csv");
//header("Content-Disposition: attachment; filename='" .$filename .".csv'");
header("Pragma: no-cache");
header("Expires: 0");
//echo header
echo implode($s, $basic_header) . "\r\n";
break;
default:
$st = "";
$et = "\t";
$s = $et . $st;
header("Content-Disposition: attachment; filename=" . $filename . ".xls");
//header("Content-Disposition: attachment; filename='" .$filename .".xls'");
header("Content-Type: application/vnd.ms-excel");
header("Pragma: no-cache");
header("Expires: 0");
//echo header
echo implode($s, $basic_header) . $et . "\r\n";
break;
}
//echo data
if ($participants) {
$temp_reg_id = '';
//will temporarily hold the registration id for checking with the next row
$attendees_group = '';
//will hold the names of the group members
$group_counter = 1;
$amount_pd = 0;
foreach ($participants as $participant) {
if ($temp_reg_id == '') {
$temp_reg_id = $participant->registration_id;
$amount_pd = $participant->amount_pd;
}
if ($temp_reg_id == $participant->registration_id) {
} else {
$group_counter++;
$temp_reg_id = $participant->registration_id;
}
$attendees_group = "Group {$group_counter}";
echo $attendees_group . $s . $participant->id . $s . escape_csv_val(stripslashes($participant->lname)) . $s . escape_csv_val(stripslashes($participant->fname)) . $s . stripslashes($participant->email) . $s . escape_csv_val(stripslashes($participant->address)) . $s . escape_csv_val(stripslashes($participant->address2)) . $s . escape_csv_val(stripslashes($participant->city)) . $s . escape_csv_val(stripslashes($participant->state)) . $s . escape_csv_val(stripslashes($participant->zip)) . $s . escape_csv_val(stripslashes($participant->phone)) . $s . escape_csv_val(stripslashes($participant->payment)) . $s . event_date_display($participant->date, 'Y-m-d') . $s . stripslashes($participant->payment_status) . $s . stripslashes($participant->txn_type) . $s . stripslashes($participant->txn_id) . $s . $participant->amount_pd . $s . escape_csv_val($participant->coupon_code) . $s . $participant->quantity . $s . event_date_display($participant->payment_date, 'Y-m-d') . $s . escape_csv_val($participant->event_name) . $s . $participant->price_option . $s . event_date_display($participant->start_date, 'Y-m-d') . $s . event_date_display($participant->event_time, get_option('time_format')) . $s . $participant->checked_in . $s . $participant->checked_in_quantity;
$answers = $wpdb->get_results("SELECT a.question_id, a.answer FROM " . EVENTS_ANSWER_TABLE . " a WHERE question_id IN ({$question_filter}) AND attendee_id = '" . $participant->id . "'", OBJECT_K);
foreach ($question_list as $k => $v) {
/*
* in case the event organizer removes a question from a question group,
* the orphaned answers will remian in the answers table. This check will make sure they don't get exported.
*/
/*if (array_key_exists($k, $answers))
{*/
$search = array("\r", "\n", "\t");
$clean_answer = str_replace($search, " ", $answers[$k]->answer);
$clean_answer = escape_csv_val($clean_answer);
echo $s . $clean_answer;
/*}
else
{
echo $s;
}*/
}
switch ($_REQUEST['type']) {
case "csv":
echo "\r\n";
break;
default:
echo $et . "\r\n";
break;
}
}
} else {
echo __('No participant data has been collected.', 'event_espresso');
}
exit;
break;
default:
echo '<p>' . __('This Is Not A Valid Selection!', 'event_espresso') . '</p>';
break;
}
default:
break;
}
}
}