Lavish, college-sponsored balls make up an age-old component of the Oxford social tradition. The oldest, richest colleges take it in turns to hold the Commemoration ball each summer. Many others hold their smaller events in the spring, showing off their grounds in Oxford’s most seasonable weather. Wolfson takes a bit of a different approach, and throws an annual Winter ball. This avoids competition with other colleges’ events, and livens up the social scene during what can otherwise be a dull spot on the calendar.
One perennial challenge of these balls is a ticketing system. Because tickets are relatively expensive, and food and drink inside the event are included, ball organizers face a challenge preventing people trying to “crash” the ball. This has lead to a bit of a cat-and-mouse game between ball organizers and those who would rather not pay. After simple tickets were forged, intricate wristbands were distributed in advance. As material science students began to duplicate those, individually numbered tickets were given out in before the event and traded for wristbands on the night, with the tickets marked on a master list with each exchange. While this method proved effective, it was frustrating for the hundreds of ballgoers in the queue waiting for one master list to be consulted for every entry.
For Wolfson’s 2011 ball, I was asked to create a unified ticketing system that would allow ticket purchase and tracking as well as a parallelizable entry system on the night. I was able to come up with an online sales system that integrated with Google Spreadsheets for easy manual modifications. An integrated ticketing system allowed any worker at the door to turn their phone into an effective ticket scanner.
Ticket Sales System
The ticket sales system was a one-trick pony. As payment was delivered in person (cash or check) and the prices were tallied up using a typical JavaScript frontend, the beauty of the system was really its ability to interface with Google Spreadsheets via the Zend Framework. Using the online shareable spreadsheet as the master record of tickets has several advantages. Sales can be tracked in real-time by non-technical users. Automatically updating graphs can be embedded on an admin website, and additional “sheets” can be added to the database to distill pertinent information — payment records, dining requirements, etc. for each member of the committee. Manual changes can be made so long as care is paid to maintain the proper spreadsheet columns. In effect, choosing Google Spreadsheets rather than a formal database solution like MySQL opens a number of user-friendly avenues to the data, and the risk (of a user manually making the database incompatible with the website via direct access) was worth that reward for our small group.
<?php
$n = $_POST['n'];
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Gdata_App_AuthException');
Zend_Loader::loadClass('Zend_Http_Client');
try {
$client = Zend_Gdata_ClientLogin::getHttpClient("wolfsonball2011@gmail.com", "password",
Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME);
}
catch (Zend_Gdata_App_AuthException $ae) {
;
}
$gdClient = new Zend_Gdata_Spreadsheets($client);
$feed = $gdClient->getSpreadsheetFeed();
$currKey = explode('/', $feed->entries[0]->id->text);
$sheetKey = $currKey[5];
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($sheetKey);
$feed = $gdClient->getWorksheetFeed($query);
$currWkshtId = explode('/', $feed->entries[0]->id->text);
$wkshtId = $currWkshtId[8];
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($sheetKey);
$query->setWorksheetId($wkshtId);
$query->setSpreadsheetQuery("confirmnum=$n");
$listFeed = $gdClient->getListFeed($query);
$ballTix = 0;
$dineTix = 0;
$email = "";
foreach($listFeed->entries as $entry) {
$cost = $entry->getCustomByName('cost');
if( isset( $cost ) ) {
$costText = $cost->GetText();
if( isset( $costText ) &&
"" != $costText ) {
$ballTix = $entry->GetCustomByName('balltix');
$dineTix = $entry->GetCustomByName('dinetix');
$email = $entry->GetCustomByName('email');
break;
}
}
}
if( $email == "" ) {
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>Wolfson Ball 2011</title>
<link rel="stylesheet" type="text/css" href="ball11.css" />
<link rel="stylesheet" type="text/css" href="http://fonts.googleapis.com/css?family=Limelight" />
<link rel="apple-touch-icon" href="img/apple-touch-icon.png" />
<link rel="icon" href="img/favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="img/favicon.ico" type="image/x-icon" />
<script type="text/javascript" src="functions.js"></script>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<meta name="viewport" content="width=650,height=855" />
<script type="text/javascript"> window.onload=setup; </script>
</head>
<body>
<div id="frame">
<div id="mainText">
<div class="topBox">Saturday 3rd December 2011</div>
<div class="tagball">Speakeasy Ball</div>
<div class="vDivide"></div>
<div class="blurb" style="text-align: left;">
Unable to generate ticket - problem reading the database.
</div>
<div class="submitButton"><a href="genTicket.html">Try Again</a></div>
<div class="vDivide"></div>
<div class="yellowText">All the Fun of the 1920's<br />Without the Prohibition</div>
<div class="vFlourish"></div>
</div>
</div>
</body>
</html>
<?php
}
else {
exec("/bin/rm -f /home/users/christmasball/public_html/data/qr/$n.png");
exec("/bin/rm -f /home/users/christmasball/public_html/data/tix/$n-tix.png");
exec("/bin/rm -f /home/users/christmasball/public_html/data/tix/$n-etix.png");
exec("/home/users/christmasball/public_html/genTicket.sh $n $dineTix $ballTix");
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
<title>Wolfson Ball 2011</title>
<link rel="stylesheet" type="text/css" href="ball11.css" />
<link rel="stylesheet" type="text/css" href="http://fonts.googleapis.com/css?family=Limelight" />
<link rel="apple-touch-icon" href="img/apple-touch-icon.png" />
<link rel="icon" href="img/favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="img/favicon.ico" type="image/x-icon" />
<script type="text/javascript" src="functions.js"></script>
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<meta name="viewport" content="width=650,height=855" />
<script type="text/javascript"> window.onload=setup; </script>
</head>
<body>
<div id="frame">
<div id="mainText">
<div class="topBox">Saturday 3rd December 2011</div>
<div class="tagball">Speakeasy Ball</div>
<div class="vDivide"></div>
<div class="blurb" style="text-align: left;">
Ticket number <?php echo $n; ?> generated.
</div>
<div class="submitButton"><a href="genTicket.html">Generate Another</a></div>
<form action="sendTicket.php" method="POST">
<input type="hidden" name="n" value=<?php echo "\"$n\""; ?> />
<input type="hidden" name="email" value=<?php echo "\"$email\""; ?> />
<input type="submit" name="submit" value="Send by E-Mail" class="submitButton"
id="confButton"/>
</form>
<div class="vDivide"></div>
<div class="yellowText">All the Fun of the 1920's<br />Without the Prohibition</div>
<div class="vFlourish"></div>
</div>
</div>
</body>
</html>
<?php
}
?>
The ticket itself was generated using a quick bash script that called ImageMagick to add the QR code and relevant text to a blank ticket. Two tickets were produced and e-mailed: one optimized for printing and the other optimized for displaying on a smartphone. The QR code links to t.php, described in the Ticket Scanning section below.
#!/bin/bash
/usr/bin/wget -q -O /home/users/christmasball/public_html/data/qr/$1.png "http://www.kornnuts.com/dev/barcode/php_qrcode/QRcode_image.php?z=10&ecl=M&s=http%3A%2F%2Fwww.wolfsonball.org%2Ft.php%3Fn%3D$1"
/usr/bin/convert /home/users/christmasball/public_html/data/tix/blank.png \
-pointsize 50 -weight bold \
-draw "text 217,475 '$2'" \
-draw "text 567,475 '$3'" \
-pointsize 26 -draw "text 835,511 '$1'" \
-draw "image over 688,250 220,220 '/home/users/christmasball/public_html/data/qr/$1.png'" \
/home/users/christmasball/public_html/data/tix/$1-tix.png
/usr/bin/convert /home/users/christmasball/public_html/data/tix/eBlank.png \
-pointsize 36 -fill white \
-draw "text 570,211 '$2'" \
-draw "text 570,247 '$3'" \
-pointsize 34 -draw "text 485,288 '$1'" \
-draw "image over 90,353 460,460 '/home/users/christmasball/public_html/data/qr/$1.png'" \
/home/users/christmasball/public_html/data/tix/$1-etix.png
As ticket sales went forward, automatically generated graphs from the spreadsheet allowed us to keep tabs on our numbers. Automatically generated sales figures and student identification also helped us reclaim £2,000 in VAT without going through each record by hand.
 |
 |
Ticket Scanning at the Door
The interesting bit, while relatively quick, is done in t.php. Each ticket is printed with a QR code that links to the t.php URL passing the ticket number as a GET argument. t.php then redirects all incoming traffic to the main ball website, so anybody scanning their own ticket will be sent there. Before redirecting, however, it checks for an authentication cookie. If that cookie is set appropriately, t.php lists the names associated with a ticket and allows the doormen to check off any people who are being issued a wrist band. This immediately updates the shared Google Spreadsheet, so the doormen will easily see if some portion of a party has already been admitted.
By scanning static QR codes linking to quick php scripts that would set and clear the authentication cookie, doormen could be added and removed dynamically during the opening period as demand required. No special equipment was needed beyond a QR-capable smartphone.
<?php
if( !isset($_COOKIE["ballauth"]) || $_COOKIE["ballauth"] != "password" ) {
header("Location: http://www.wolfsonball.org");
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Wolfson Ball 2011</title>
<meta http-equiv="REFRESH" content="0;/"></HEAD>
<body>
This page is not available. Please <a href="/">click here</a> to return to the Wolfson Ball site.
</body>
</html>
<?php
} else if( !isset($_GET["n"]) || $_GET["n"] == "" ) {
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Wolfson Ball 2011</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0">
<link rel="stylesheet" type="text/css" href="iphone.css" />
<link rel="apple-touch-icon" href="img/apple-touch-icon.png" />
<link rel="icon" href="img/favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="img/favicon.ico" type="image/x-icon" />
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<body style="text-align:center;">
Enter ticket number:<br />
<br />
<form action="t.php" method="GET">
<input type="text" name="n" /><br /><br />
<input type="submit" name="submit" value="Submit" />
</form>
<script type="text/javascript">
window.addEventListener('load', function() {
setTimeout(scrollTo, 0, 0, 1);
}, false);
</script>
</body>
</html>
<?php
} else {
$n = $_GET["n"];
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Gdata_App_AuthException');
Zend_Loader::loadClass('Zend_Http_Client');
try {
$client = Zend_Gdata_ClientLogin::getHttpClient("wolfsonball2011@gmail.com", "password",
Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME);
}
catch (Zend_Gdata_App_AuthException $ae) {
;
}
$gdClient = new Zend_Gdata_Spreadsheets($client);
$feed = $gdClient->getSpreadsheetFeed();
$currKey = explode('/', $feed->entries[0]->id->text);
$sheetKey = $currKey[5];
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($sheetKey);
$feed = $gdClient->getWorksheetFeed($query);
$currWkshtId = explode('/', $feed->entries[0]->id->text);
$wkshtId = $currWkshtId[8];
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($sheetKey);
$query->setWorksheetId($wkshtId);
$query->setSpreadsheetQuery("confirmnum=$n");
$listFeed = $gdClient->getListFeed($query);
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Wolfson Ball 2011</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0">
<link rel="stylesheet" type="text/css" href="iphone.css" />
<link rel="apple-touch-icon" href="img/apple-touch-icon.png" />
<link rel="icon" href="img/favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="img/favicon.ico" type="image/x-icon" />
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<body style="text-align:center;">
Guests for ticket <?php echo $n; ?>:<br />
<br />
<form action="band.php" method="post">
<input type="hidden" name="n" value=<?php echo "\"$n\""; ?> />
<table border=0 style="margin: 0 auto;">
<?php
$i = 0;
foreach($listFeed->entries as $entry) {
if($entry->getCustomByName('band')->GetText() == "y") { ?>
<tr><td> </td>
<td style="color:#aaaaaa"><?php echo $entry->getCustomByName('name')->GetText(); ?></td>
</tr>
<?php } else { ?>
<tr><td><input type="checkbox" name="i[]" value=<?php echo "\"$i\""; ?> /></td>
<td><?php echo $entry->getCustomByName('name')->GetText(); ?></td>
</tr>
<?php }
$i++;
}
?>
</table>
<br />
<input type="submit" name="submit" value="Dispense Wristbands" />
</form>
<script type="text/javascript">
window.addEventListener('load', function() {
setTimeout(scrollTo, 0, 0, 1);
}, false);
</script>
</body>
</html>
<?php
}
?>
The second half of the doorman code, band.php is called by a form submit by t.php. This script just updates the Google Spreadsheet record to indicate that the given attendee has been given a wristband.
<?php
$n = $_POST["n"];
$i = $_POST["i"];
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
Zend_Loader::loadClass('Zend_Gdata_App_AuthException');
Zend_Loader::loadClass('Zend_Http_Client');
try {
$client = Zend_Gdata_ClientLogin::getHttpClient("wolfsonball2011@gmail.com", "password",
Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME);
}
catch (Zend_Gdata_App_AuthException $ae) {
;
}
$gdClient = new Zend_Gdata_Spreadsheets($client);
$feed = $gdClient->getSpreadsheetFeed();
$currKey = explode('/', $feed->entries[0]->id->text);
$sheetKey = $currKey[5];
$query = new Zend_Gdata_Spreadsheets_DocumentQuery();
$query->setSpreadsheetKey($sheetKey);
$feed = $gdClient->getWorksheetFeed($query);
$currWkshtId = explode('/', $feed->entries[0]->id->text);
$wkshtId = $currWkshtId[8];
$query = new Zend_Gdata_Spreadsheets_ListQuery();
$query->setSpreadsheetKey($sheetKey);
$query->setWorksheetId($wkshtId);
$query->setSpreadsheetQuery("confirmnum=$n");
$listFeed = $gdClient->getListFeed($query);
foreach( $i as $j ) {
unset( $theRow );
foreach( $listFeed->entries[$j]->getCustom() as $value )
$theRow[ $value->getColumnName() ] = $value->getText();
$theRow["band"] = "y";
$gdClient->updateRow($listFeed->entries[$j], $theRow );
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Wolfson Ball 2011</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=0">
<link rel="stylesheet" type="text/css" href="iphone.css" />
<link rel="apple-touch-icon" href="img/apple-touch-icon.png" />
<link rel="icon" href="img/favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="img/favicon.ico" type="image/x-icon" />
<meta http-equiv="Content-Type" content="text/html;charset=utf-8" />
<body style="text-align:center;">
The selected guests have been recorded as having received their wristbands.<br />
<br />
<a href="t.php">Process Another Ticket</a>
<script type="text/javascript">
window.addEventListener('load', function() {
setTimeout(scrollTo, 0, 0, 1);
}, false);
</script>
</body>
</html>
The system worked quite well on the night, and the following year’s ball committee used a very similar setup!
Leave a Reply