|
|
|
|
|
|
| State |
City |
Orchestra |
Performance(s) |
#!/usr/bin/perl
## Bring in all general modules
use strict;
use CGI qw/:standard/;
use CGI::Carp qw(fatalsToBrowser);
$CGI::POST_MAX = 1024 * 100; # max 100K posts
## Connect to databases
use DBI;
# postgres
my $pg_username = "psql";
my $pg_password = "kalamata";
my $dbpg = "DBI:Pg:dbname='meet_the_music';";
my $postgres = DBI-> connect($dbpg, ,$pg_username,$pg_password) or print "Can't connect to the postgres database.";
# OPAS
my $opas_username = 'opasdev';
my $opas_password = 'opas';
my $opas_db = 'opas';
my $opas = DBI->connect("dbi:Sybase:server=$opas_db", $opas_username, $opas_password, {PrintError => 1});
## Create a hash mapping OPAS state abbreviations to full state names
my %state_names = (
'AK' => "Alaska",
'AL' => "Alabama",
'AR' => "Arkansas",
'AZ' => "Arizona",
'CA' => "California",
'CO' => "Colorado",
'CT' => "Connecticut",
'DC' => "District of Columbia",
'DE' => "Delaware",
'FL' => "Florida",
'GA' => "Georgia",
'GU' => "Guam",
'HI' => "Hawaii",
'IA' => "Iowa",
'ID' => "Idaho",
'IL' => "Illinois",
'IN' => "Indiana",
'KS' => "Kansas",
'KY' => "Kentucky",
'LA' => "Louisiana",
'MA' => "Massachusetts",
'MD' => "Maryland",
'ME' => "Maine",
'MI' => "Michigan",
'MN' => "Minnesota",
'MO' => "Missouri",
'MS' => "Mississippi",
'MT' => "Montana",
'NC' => "North Carolina",
'ND' => "North Dakota",
'NE' => "Nebraska",
'NH' => "New Hampshire",
'NJ' => "New Jersey",
'NM' => "New Mexico",
'NV' => "Nevada",
'NY' => "New York",
'OH' => "Ohio",
'OK' => "Oklahoma",
'OR' => "Oregon",
'PA' => "Pennsylvania",
'PR' => "Puerto Rico",
'RI' => "Rhode Island",
'SC' => "South Carolina",
'SD' => "South Dakota",
'TN' => "Tennessee",
'TX' => "Texas",
'UT' => "Utah",
'VA' => "Virginia",
'VT' => "Vermont",
'WA' => "Washington",
'WI' => "Wisconsin",
'WV' => "West Virginia",
'WY' => "Wyoming",
'International' => "International",
'Canada' => "Canada",
);
## Set up dispatch table
my $r = param('r');
my %requests = (
'add_comment_disabled' => \&add_comment_disabled,
'get_all_performances' => \&get_all_performances,
'get_bio' => \&get_bio,
'get_comments' => \&get_comments,
'get_future_performances' => \&get_future_performances,
'get_purchases' => \&get_purchases,
);
## Do the request
print "Content-type: text/html\n\n" unless ($r eq 'add_comment');
&{$requests{$r}};
$opas-> disconnect;
$postgres-> disconnect;
exit 0;
## Subs
sub add_comment {
## Collect and clean all the input
my $feature_id = '13744';
my $name = param('name');
$name =~ s/<|>//g;
my $city = param('city');
$city =~ s/<|>//g;
my $state = param('state');
$state =~ s/<|>//g;
my $country = param('country');
$country =~ s/<|>//g;
my $email = param('email');
my $subject = param('subject');
$subject =~ s/<|>//g;
my $comments = param('comments');
$comments =~ s/<|>//g;
# ## Find the first open ID in comments
# my $largest_id;
# my $new_id = 1;
# my $get_next_id = $postgres-> prepare('SELECT id FROM comments ORDER BY id ASC');
# $get_next_id-> execute;
# while ((my $stored_id) = $get_next_id-> fetchrow_array) {
# $new_id++ if ($stored_id == $new_id);
# $largest_id = $stored_id if ($stored_id > $largest_id);
# }
# $get_next_id-> finish;
#
# ## Add the record to the postgres database
# my $today = set_date('today_integer');
# my $add_comments = $postgres-> prepare('INSERT INTO comments (id, feature_id, comments, name, city, state, country, subject,
# date_posted, status, email) VALUES (?,?,?,?,?,?,?,?,?,?,?)') or print $postgres-> errstr;
# $add_comments-> execute($new_id, $feature_id, $comments, $name, $city, $state, $country, $subject, $today, '1', $email) or print $postgres-> errstr;
# $add_comments-> finish;
#
# ## Email Talia that a comment has been posted.
# open MAIL,"|/usr/lib/sendmail -oi -t kgallo\@americanorchestras.org" or print qq(Location: http://www.fordmadeinamerica.org/\n\n) ;
# print MAIL "From: Web Site \n";
# print MAIL "To: Ken Gallo \n";
# print MAIL "Subject: Meet the Music Visitor Comments\n\n";
# print MAIL qq(Hi Kenny,\n\n);
# print MAIL qq(A new visitor comment has been posted to the Meet the Music site for\n);
# print MAIL qq(Made in America by Joan Tower.\n\n);
# print MAIL qq(-The Meet the Music Site\n\n);
# close MAIL;
## Redirect to the thank you page
print qq(Location: http://www.fordmadeinamerica.org/thanks.html\n\n);
}
sub get_all_performances {
## Called from http://www.fordmadeinamerica.org/orchestras.shtml as SSI to build a table of all places orchestras performing "Made in America"
## Query OPAS for all the performances of the selected work
my $get_orchestras = $opas-> prepare("SELECT aDates.DATE_, aDates.DATE_1, aDates.Location_ID, aDates.Orchestra_ID,
sAddresses.Name1, sAddresses.Place, sAddresses.State
FROM aDates, aDate_Works, sAddresses
WHERE aDate_Works.Work_ID = 13744 AND
sAddresses.ID = aDates.Orchestra_ID AND
aDate_Works.Date_ID = aDates.ID AND
aDates.PlanningLevel = 1
ORDER BY sAddresses.State ASC, sAddresses.Place ASC, aDates.DATE_1 DESC");
$get_orchestras-> execute;
while ((my $start, my $end, my $location_id, my $orch_id, my $raw_orchestra, my $raw_city, my $state) = $get_orchestras-> fetchrow_array) {
## If $location_id then this performance is somewhere other than the home $city/$state which is what wegot from $get_orchestras.
## So, query sAddresses for the city and state associated with this $location_id.
if ($location_id) {
my $get_alt_venue = $opas-> prepare("SELECT Place, State FROM sAddresses WHERE ID = $location_id");
$get_alt_venue-> execute;
($raw_city, $state) = $get_alt_venue-> fetchrow_array;
$get_alt_venue-> finish;
}
## Parse dates
my %months = (
'Jan' => 'January ',
'Feb' => 'February ',
'Mar' => 'March ',
'Apr' => 'April ',
'May' => 'May ',
'Jun' => 'June ',
'Jul' => 'July ',
'Aug' => 'August ',
'Sep' => 'September ',
'Oct' => 'October ',
'Nov' => 'November ',
'Dec' => 'December ',
);
(my $start_month, my $start_day, my $start_year, my $start_time) = split " ", $start;
$start_day =~ s/^0//;
(my $end_month, my $end_day, my $end_year, my $end_time) = split " ", $end;
$end_day =~ s/^0//;
my $dates = $months{$start_month} . $start_day;
$dates .= ', ' . $start_year if ($start_year != $end_year || ($end_month && $start_day == $end_day));
if ($end_month && $start_day != $end_day) {
if ($months{$end_month} eq $months{$start_month}) {
$dates .= ' - ' . $end_day . ', ' . $end_year;
}
else {
$dates .= ' -
' . $months{$end_month} . $end_day . ', ' . $end_year;
}
}
## Parse orchestra name
my $orchestra = parse_orchestra($raw_orchestra);
## Get orchestra contact information
my ($url, $phone);
my $get_contact_info = $opas-> prepare("SELECT Number_ FROM sAddress_Numbers WHERE Address_ID = $orch_id");
$get_contact_info-> execute;
while ((my $info) = $get_contact_info-> fetchrow_array) {
if ($info =~ /^(http|www)/) {
$url = $info;
$url =~ s/ //g;
$url = 'http://' . $url if ($url !~ /^http/);
}
else {
$phone = $info;
}
}
$get_contact_info-> finish;
## Format the phone number
$phone =~ s/(\(\d\d\d\))(.+)/$1 $2/ if ($phone =~ /\(\d\d\d\)/);
## Format the state
$state =~ s/ //g;
$state = $state_names{$state} if (exists $state_names{$state});
## Format the city
my $city = parse_city($raw_city);
## Display the row
print qq(\n);
print qq(\t| $state | \n);
print qq(\t$city | \n);
if ($url) {
print qq(\t$orchestra | \n);
}
else {
print qq(\t$orchestra
$phone | \n);
}
print qq(\t$dates | \n);
print qq( \n);
}
$get_orchestras-> finish;
}
sub get_bio {
## Called from http://www.fordmadeinamerica.org/orchestras.shtml as SSI to build a table of all future performances of "Made in America"
## Get the bio from postgres and display it
my $get_bio = $postgres-> prepare('SELECT bio FROM composers WHERE id = ?') or print $postgres-> errstr;
$get_bio-> execute('244') or print $postgres-> errstr;
(my $bio) = $get_bio-> fetchrow_array;
$get_bio-> finish;
## Print the bio
my @bio_lines = split "\r", $bio;
my $display_bio;
my $last_line = 'e';
foreach (@bio_lines) {
if ($_ !~ /[a-zA-Z0-9]/) {
$display_bio .= "\n\n";
$last_line = 'e';
next;
}
my $break;
if ($_ !~ '^\</?(table|tr|td|th|blockquote)') {
$break = ($last_line eq 'e') ? '' : '
';
}
$display_bio .= $break . $_ ;
$last_line = 'b';
}
$display_bio =~ s/"/"/g;
print $display_bio;
}
sub get_comments {
## Query postgres to get all comments related to this feature
my $get_comments = $postgres-> prepare('SELECT name, city, state, country, subject, comments, date_posted FROM comments WHERE
feature_id = ? AND status = ? ORDER BY date_posted DESC');
$get_comments-> execute('13744', '2');
my $postings = 0;
while ((my $name, my $city, my $state, my $country, my $subject, my $comments, my $date) = $get_comments-> fetchrow_array) {
my $header = join ', ', $city, $state, $country;
(my $year, my $month, my $day) = $date =~ /(\d\d\d\d)(\d\d)(\d\d)/;
my $date_posted = $month . '-' . $day . '-' . $year;
print qq( \n) if ($postings == 0);
print <<" HTML";
|
$name | $header | $date_posted
HTML
## Print the comments
my @comment_lines = split "\r", $comments;
my $display_comments;
my $last_line = 'e';
foreach (@comment_lines) {
if ($_ !~ /[a-zA-Z0-9]/) {
$display_comments .= " \n\n";
$last_line = 'e';
next;
}
my $break;
if ($_ !~ '^\</?(table|tr|td|th|blockquote)') {
$break = ($last_line eq 'e') ? '' : '
';
}
$display_comments .= $break . $_ ;
$last_line = 'b';
}
$display_comments =~ s/"/"/g;
print $display_comments;
print <<" HTML";
|
HTML
$postings++;
}
if ($postings > 0) {
print qq( \n\n);
}
else {
print qq(| There are currently no comments posted. Add a comment now! | \n);
}
}
sub get_future_performances {
## Called from http://www.fordmadeinamerica.org/performance.shtml as SSI to build a table of all future performances of "Made in America"
## Query OPAS for all the performances of the selected work
my $today = set_date('today');
my $get_orchestras = $opas-> prepare("SELECT aDates.DATE_, aDates.DATE_1, aDates.Location_ID, aDates.Orchestra_ID,
sAddresses.Name1, sAddresses.Place, sAddresses.State
FROM aDates, aDate_Works, sAddresses
WHERE aDate_Works.Work_ID = 13744 AND
sAddresses.ID = aDates.Orchestra_ID AND
aDate_Works.Date_ID = aDates.ID AND
aDates.DATE_ >= '$today' AND
aDates.PlanningLevel = 1
ORDER BY sAddresses.State ASC, sAddresses.Place ASC, aDates.DATE_1 DESC");
$get_orchestras-> execute;
while ((my $start, my $end, my $location_id, my $orch_id, my $raw_orchestra, my $raw_city, my $state) = $get_orchestras-> fetchrow_array) {
## If $location_id then this performance is somewhere other than the home $city/$state which is what wegot from $get_orchestras.
## So, query sAddresses for the city and state associated with this $location_id.
if ($location_id) {
my $get_alt_venue = $opas-> prepare("SELECT Place, State FROM sAddresses WHERE ID = $location_id");
$get_alt_venue-> execute;
($raw_city, $state) = $get_alt_venue-> fetchrow_array;
$get_alt_venue-> finish;
}
## Parse dates
my %months = (
'Jan' => 'January ',
'Feb' => 'February ',
'Mar' => 'March ',
'Apr' => 'April ',
'May' => 'May ',
'Jun' => 'June ',
'Jul' => 'July ',
'Aug' => 'August ',
'Sep' => 'September ',
'Oct' => 'October ',
'Nov' => 'November ',
'Dec' => 'December ',
);
(my $start_month, my $start_day, my $start_year, my $start_time) = split " ", $start;
$start_day =~ s/^0//;
(my $end_month, my $end_day, my $end_year, my $end_time) = split " ", $end;
$end_day =~ s/^0//;
my $dates = $months{$start_month} . $start_day;
$dates .= ', ' . $start_year if ($start_year != $end_year || ($end_month && $start_day == $end_day));
if ($end_month && $start_day != $end_day) {
if ($months{$end_month} eq $months{$start_month}) {
$dates .= ' - ' . $end_day . ', ' . $end_year;
}
else {
$dates .= ' -
' . $months{$end_month} . $end_day . ', ' . $end_year;
}
}
## Parse orchestra name
my $orchestra = parse_orchestra($raw_orchestra);
## Get orchestra contact information
my ($url, $phone);
my $get_contact_info = $opas-> prepare("SELECT Number_ FROM sAddress_Numbers WHERE Address_ID = $orch_id");
$get_contact_info-> execute;
while ((my $info) = $get_contact_info-> fetchrow_array) {
if ($info =~ /^(http|www)/) {
$url = $info;
$url =~ s/ //g;
$url = 'http://' . $url if ($url !~ /^http/);
}
else {
$phone = $info;
}
}
$get_contact_info-> finish;
## Format the phone number
$phone =~ s/(\(\d\d\d\))(.+)/$1 $2/ if ($phone =~ /\(\d\d\d\)/);
## Format the state
$state =~ s/ //g;
$state = $state_names{$state} if (exists $state_names{$state});
## Format the city
my $city = parse_city($raw_city);
## Display the row
print qq(\n);
print qq(\t| $state | \n);
print qq(\t$city | \n);
if ($url) {
print qq(\t$orchestra | \n);
}
else {
print qq(\t$orchestra
$phone | \n);
}
print qq(\t$dates | \n);
print qq( \n);
}
$get_orchestras-> finish;
}
sub get_purchases {
## Query postgres for all recording information related to this piece
## Now get the purchase places for this feature
my $shops = 0;
my $get_sellers = $postgres-> prepare('SELECT orchestra, url, conductor, soloists FROM purchases WHERE
feature_id = ? ORDER BY orchestra ASC') or print $postgres-> errstr;
$get_sellers-> execute('13744') or print $postgres-> errstr;
while ((my $orchestra, my $url, my $conductor, my $soloists) = $get_sellers-> fetchrow_array) {
if ($shops == 0) {
print <<" HTML";
| Orchestra |
Conductor |
Soloists |
HTML
$shops++;
}
$url =~ s/^ +//;
$url =~ s/ +$//;
$orchestra =~ s/(^.+)(!The !)/The $1/ if ($orchestra =~ /!The !/);
print <<" HTML";
| $orchestra |
$conductor |
$soloists |
HTML
$shops++;
}
$get_sellers-> finish;
if ($shops == 0) {
print qq(There are currently no recordings available for this piece.
| \n);
}
}
sub parse_city {
## Take unformatted city name, format it and return.
my $city_unformatted = shift;
my $city;
## First, change the name so that only the first letter of each word is capitalized
$city_unformatted =~ s/ +$//g;
$city_unformatted = lc "$city_unformatted";
(my @city_title) = split ' ', $city_unformatted;
foreach (@city_title) {
$city .= ucfirst "$_" . ' ';
}
$city =~ s/-(.)/-\U$1\E/g; # Make Foo-faa Foo-Faa
## Other formatting
$city = 'N/A' unless ($city);
$city = 'New York' if ($city eq 'New York City');
$city =~ s/''/'/g; # Weird double single quotes in data coming from OPAS, fix it here.
return $city;
}
sub parse_orchestra {
## Take unformatted orchestra name, format it and return.
my $orchestra_unformatted = shift;
my $orchestra;
## First, change the name so that only the first letter of each word is capitalized
$orchestra_unformatted = lc "$orchestra_unformatted";
(my @orch_title) = split ' ', $orchestra_unformatted;
foreach (@orch_title) {
if (/\bof\b/) {
$orchestra .= "$_" . ' ';
}
else {
$orchestra .= ucfirst "$_" . ' ';
}
}
$orchestra =~ s/-(.)/-\U$1\E/g; # Make Foo-faa Foo-Faa
$orchestra =~ s/\((.)/\(\U$1\E/g;
$orchestra =~ s/''/'/g; # Weird double single quotes in data coming from OPAS, fix it here.
## Change abbreviations to full words
my %abbrevs = (
'Fest' => 'Festival',
'Fest.' => 'Festival',
'Assn.' => 'Association',
'Orch.' => 'Orchestra',
'Orch' => 'Orchestra',
'Orchestr' => 'Orchestra',
'Symph' => 'Symphony',
'Symph.' => 'Symphony',
'Sym.' => 'Symphony',
'Sym' => 'Symphony',
'Assoc.' => 'Association',
'Assoc' => 'Association',
'Phil' => 'Philharmonic',
'Phil.' => 'Philharmonic',
'Inst.' => 'Institute',
'Consort' => 'Consortium',
'Cho' => 'Chorale',
'Comm.' => 'Community',
'Soc.' => 'Society',
'Soc' => 'Society',
'Coll.' => 'College',
'Univ.' => 'University',
);
while ((my $find, my $replace) = each %abbrevs) {
$orchestra =~ s/\b\Q$find\E\b/$replace /g;
}
## Remove any excess whitespaces
$orchestra =~ s/ +$//g;
## Depending on the order of the keys in the loop above, you can end up with some left over dots, so remove them.
$orchestra =~ s/ \.//g;
## Other formatting
$orchestra =~ s/D'alene/d'Alene/;
$orchestra =~ s/Bbc/BBC/;
$orchestra =~ s/Uci\b/UCI/;
$orchestra =~ s/\bUsc/USC/;
$orchestra =~ s/Csun/CSUN/;
$orchestra =~ s/\bDc\b/DC/;
$orchestra =~ s/\bNw\b/NW/;
$orchestra =~ s/Usm\/fleet/USM\/Fleet/;
$orchestra =~ s/\bWpu\b/WPU/;
$orchestra =~ s/\bNmsu\b/NMSU/;
$orchestra =~ s/\bNcsa\b/NCSA/;
$orchestra =~ s/Univ\./University/;
return $orchestra;
}
sub set_date {
my $date_type = shift;
(my @date) = localtime();
my $month = sprintf "%02d", ($date[4] + 1);
my $day = sprintf "%02d", $date[3];
my $year = sprintf "%02d", ($date[5] - 100);
if ($date_type eq 'today') {
my $today = $month . '/' . $day . '/' . '20' . $year;
return $today;
}
if ($date_type eq 'today_integer') {
my $today = '20' . $year . $month . $day;
return $today;
}
if ($date_type eq 'live_date') {
my $today = $year . $month . $day;
return $today;
}
if ($date_type eq '3back') {
$month -= 3;
if ($month < 1) {
$month += 12;
$year--;
$year = sprintf "%02d", $year;
}
$month = sprintf "%02d", $month;
my $date = $month . '/' . $day . '/' . '20' . $year;
return $date;
}
if ($date_type eq '14forward') {
$month += 2;
if ($month > 12) {
$month -= 12;
$year++
}
$year++;
$month = sprintf "%02d", $month;
$year = sprintf "%02d", $year;
my $date = $month . '/' . $day . '/' . '20' . $year;
return $date;
}
}
|
|
|
|
|
|
|
