#!perl

use strict;

use CGI;
use DBI;
use URI::Escape;

my $cgi = new CGI;

print "Content-type: text/html\n\n";

# Connect to the database

my $user = "cduser";
my $pw = "cduserpassword";
my $dbh = DBI->connect("DBI:mysql:database=cds;host=localhost", $user, $pw);
if (!$dbh)
{
	show_error($DBI::errstr);
	exit;
}

# Get static lookup info (the list of genres) that gets used in
#   a lot of different places
my @genres = get_genres($dbh);

# Print out HTML header information and some navigation links
print header();

# Print out content of the page
do_something($cgi,$dbh,\@genres);

# Print out HTML footer information
print footer();

# Disconnect from the database - if you don't include this, you might not
#   see anything in your browser, but your error log will fill up with
#   complaints
$dbh->disconnect;

# And that's it
exit;


# function definitions


# void do_something (CGI handle $cgi, DBI handle $dbh, array $genres)

# This function is a front-end to the other functions of the application.
# The variable $action is set to the value, if any, of the CGI field 'action'
# (typically passed in as the Submit button on one of the application's forms).
# Based on the value of $action, other functions are called to perform
# any requested database actions. Then, again based on the value of $action,
# the output of an appropriate function is printed. If a database
# function fails, 'action' is set to a value that will return the user to
# the matching edit function.

sub do_something
{
	my $cgi = shift;
	my $dbh = shift;
	my $genres = shift;


# default action is none
	my $action;
	$action = $cgi->param('action');

	if ($action eq "Save CD")
	{

# if there is an error writing the CD information to the database,
# return the user to the CD edit form to correct any mistakes;
# otherwise, go to the home page of the application

		$action = "";
		if (!save_cd($cgi,$dbh))
		{
			$action = "edit_cd";
		}
	}
	elsif ($action eq "Save Band")
	{

# if there is an error writing the band information to the database,
# return the user to the band edit form to correct any mistakes;
# otherwise, go to the home page of the application

		$action = "";
		if (!save_band($cgi,$dbh))
		{
			$action = "edit_band";
		}
	}
	elsif ($action eq "Delete CD")
	{

# if there is an error deleting the CD from the database,
# return the user to the CD edit form to correct any mistakes;
# otherwise, go to the home page of the application

		$action = "";
		if (!kill_cd($dbh,$cgi->param('cd_id')))
		{
			$action = "edit_cd";
		}
	}
	elsif ($action eq "Delete Band")
	{

# if there is an error deleting the band from the database,
# return the user to the CD edit form to correct any mistakes;
# otherwise, go to the home page of the application

		$action = "";
		if (!kill_band($cgi,$dbh))
		{
			$action = "edit_band";
		}
	}

	if ($action eq "edit_cd")
	{
		print edit_cd($cgi,$dbh,$genres);
	}
	elsif ($action eq "edit_band")
	{
		print edit_band($cgi,$dbh,$genres);
	}
	elsif ($action eq "list_bands")
	{
		print list_bands($dbh);
	}

	if ($cgi->param('search_value') ne "") { $action = "Search"; }

	if (!$action or $action eq "Search")
	{
		print search_form();
	}

	if ($action eq "list_cds" or $action eq "Search")
	{
		print list_cds($dbh
			, $cgi->param('search_field')
			, $cgi->param('search_value')
		);
	}
}

# string edit_cd (CGI handle $cgi, DBI handle $dbh, array $genres)

# This function displays an HTML form for editing the information about
# a CD and the band that recorded it.
# If a particular CD is passed in via the 'cd_id' field from the CGI handle
# $cgi, information about that CD and band is retrieved from the CD database
# and displayed in the form. If no CD is specified, but a band ID is passed
# in via the 'band_id' CGI field, the band name is displayed as a link
# to the edit_band form, along with blank fields for the entry of a new CD.
# If no band ID is passed in, a blank form is displayed allowing for the
# entry of a new band and CD at the same time.
# In case of a database error, the function returns the result of show_error().

sub edit_cd
{
	my $cgi = shift;
	my $dbh = shift;
	my $genres = shift;

	my $query;
	my $mode = "Add";

	my ($band_id,$band_prefix,$band_name,$band_genre);
	my ($artist_first,$artist_last);
	my $bands;

	my ($cd_id,$cd_title,$cd_release_year,$cd_genre);
	my $cd_title;

	my ($track_no,$track_name,$track_mins,$track_secs);
	my %tracks;

	$cd_id = $cgi->param('cd_id');

# Check to see if we've been given a specific band ID

	if ($cd_id)
	{

# First, we'll get information about the CD from the 'cds' table:

		$mode = "Edit";
		$query = <<EOQ;
select band_id,cd_title,cd_release_year,cd_genre from cds where cd_id=$cd_id
EOQ
		($band_id,$cd_title,$cd_release_year,$cd_genre)
			= $dbh->selectrow_array($query)
				or return show_error($dbh->errstr);

# This will give us the ID number for the band, which we'll use to get
# data from the 'bands' table. We'll diddle with the band_prefix and
# band_name fields to take care of the various ways band and artist
# names might be stored:

		$query = <<EOQ;
select band_prefix,band_name,band_genre from bands where band_id=$band_id
EOQ
		($band_prefix,$band_name,$band_genre)
			= $dbh->selectrow_array($query)
				or return show_error($dbh->errstr);
		if ($band_prefix eq "The")
		{
			$band_name = "$band_prefix $band_name";
		}
		elsif ($band_prefix ne "")
		{
			$artist_first = $band_prefix;
			$artist_last = $band_name;
			$band_prefix = "";
			$band_name = "";
		}

# Finally, we'll get information about the tracks on the CD. The values
# for each track will be stored in a hash named %tracks, which we
# declared at the start of the function, keyed on the track number.
# The values of the seconds field 'track_secs' is formatted to display
# with a leading zero if it's less than 10, to give us a more normal
# looking time value - "2:03", not "2:3":

		$query = <<EOQ;
select track_no,track_name,track_mins,track_secs
from tracks
where cd_id = $cd_id order by track_no
EOQ
		my $tres = $dbh->selectall_hashref($query)
			or return show_error($dbh->errstr);
		foreach my $trow (@$tres)
		{
			my $tno = $$trow{'track_no'};
			$tracks{$tno}{'track_name'} = $$trow{'track_name'};
			$tracks{$tno}{'track_mins'} = $$trow{'track_mins'};
			$tracks{$tno}{'track_secs'}
				= sprintf("%0.2d", $$trow{'track_secs'});
		}
	}

# Now we're ready to create an HTML form that you can fill
# in with information about the CD. First, we need to know about who
# recorded it. Since once we've entered info about a band into the
# database we won't want to have to re-enter it, when we're using
# this form to enter a new CD, we'll display a <SELECT> field to
# let the user pick an existing band record if they like, or
# enter information about a brand new band right here.

	my $output = <<EOQ;
<h4>$mode CD</h4>
<form method="post" action="index.pl">
<table border="1">
<tr>
<td valign="top" align="left">
<b>Band:</b>
</td>
<td valign="top" align="left">
<input type="hidden" name="cd_id" value="$cd_id">
EOQ
	if (!$band_id)
	{
		$output .= band_select_field($dbh);
		$output .= <<EOQ;
</td>
</tr>
<tr>
<td valign="top" align="left">
<b>New Band:</b>
</td>
<td>
EOQ
	}
	else
	{
		$output .= <<EOQ;
<input type="hidden" name="band_id" value="$band_id">
EOQ
	}
	$output .= band_form($dbh,$band_id,$genres);

	$output .= <<EOQ;
</td>
</tr>
EOQ

# And now, fields about the CD itself.
# We need to get the title of the CD, the year it was released, the
# genre of the CD itself (we'll default to the genre of the band),
# and then information about each track: the track number, the name
# of the track, and how long it is in minutes and seconds. For simplicity's
# sake, I just display a fixed number of track fields, with track
# numbers predefined - if the track has no name, it will be ignored:

	$output .= <<EOQ;
<tr>
<td valign="top" align="left">
<b>CD Title:</b>
</td>
<td valign="top" align="left">
<input type="text" size="50" name="cd_title" value="$cd_title">
</td>
</tr>
<tr>
<td valign="top" align="left">
<b>Year Released:</b>
</td>
<td valign="top" align="left">
<input type="text" size="4" maxlength="4" name="cd_release_year" value="$cd_release_year">
</td>
</tr>
<tr>
<td valign="top" align="left">
<b>CD Genre:</b>
</td>
<td valign="top" align="left">
EOQ
	$output .= genre_select_field($genres,"cd_genre",$cd_genre);
	$output .= <<EOQ;
</td>
</tr>

<tr><td colspan="2"></td></tr>

<tr>
<td valign="top" align="left">
<b>Tracks:</b>
</td>
<td valign="top" align="left">
<table border="1">
<tr>
<td valign="top" align="left"><b>#</b></td>
<td valign="top" align="left"><b>Name</b></td>
<td valign="top" align="left"><b>Time</b></td>
</tr>
EOQ
	for (my $i = 1; $i <= 20; $i++)
	{
		$output .= <<EOQ;
<tr>
<td valign="top" align="left">
<input type="hidden" name="track_no" value="$i">
$i
</td>
<td valign="top" align="left"><input type="text" size="30" name="track_name_$i" value="$tracks{$i}{'track_name'}"></td>
<td valign="top" align="left"><input type="text" size="3" maxlength="2" name="track_mins_$i" value="$tracks{$i}{'track_mins'}"><b>:</b><input type="text" size="3" maxlength="2" name="track_secs_$i" value="$tracks{$i}{'track_secs'}"></td>
</tr>
EOQ
	}
	$output .= <<EOQ;
</table>
</td>
</tr>
</table>
EOQ

# Finally, we add some submit fields and end the form, then return the
# whole thing to be printed out:

	if ($cd_id)
	{
		$output .= <<EOQ;
<input type="submit" name="action" value="Delete CD">
EOQ
	}
	$output .= <<EOQ;
<input type="submit" name="action" value="Save CD">
<input type="reset">
</form>
EOQ
	return $output;
}


# string edit_band (CGI handle $cgi, DBI handle $dbh, array $genres)

# This function displays a form for editing information about a band.
# If the ID of a particular band is passed in, via the 'band_id' field
# from the CGI handle $cgi, information about that band is retrieved from
# the CD database and displayed in the form fields, along with a list of
# the band's CDs (if any). If no band ID is passed in, a blank form is
# displayed for the entry of a new band.

sub edit_band
{
	my $cgi = shift;
	my $dbh = shift;
	my $genres = shift;

# You'll see that edit_band() is mostly a subset
# of edit_cd() - it's missing only the <SELECT> field for bands, and
# the fields for CD and track information:

	my $band_id;
	$band_id = $cgi->param('band_id');

	my $mode = $band_id ? "Edit" : "Add";

	my $output = <<EOQ;
<h4>$mode Band</h4>
<form method="post" action="index.pl">
EOQ

	$output .= band_form($dbh,$band_id,$genres);

	if ($band_id)
	{
		$output .= <<EOQ;
<input type="submit" name="action" value="Delete Band">
EOQ
	}
	$output .= <<EOQ;
<input type="submit" name="action" value="Save Band">
<input type="reset">
</form>
EOQ

	if ($band_id)
	{
		$output .= list_cds($dbh, "b.band_id", $band_id);
	}

	return $output;
}


# string list_cds (DBI handle $dbh, string $search_field, string $search_value)

# This function uses the second and third arguments, $search_field and
# $search_value, to build a query to search the CD database and return
# any matching CDs and/or tracks as an HTML table. If the query is
# not for a specific band, the band name will be displayed. If the query
# is for a specific track, the track name will be displayed. CD and band
# names are shown as links to the appropriate edit form.

sub list_cds
{
	my $dbh = shift;
	my $search_field = shift;
	my $search_value = shift;

# The output of the function is going to be an HTML table. So it starts
# by building up a list of column headers, depending on what we asked
# it to display. We can also call this function to display information
# about specific tracks, so we allow for a "Track" column if requested.
# And there's no point in having an "Artist" column if we're looking at
# a specific band, so that's allowed for as well.

	my @fields;
	push @fields, "Track" if $search_field eq "t.track_name";
	push @fields, "Title";
	push @fields, "Artist" if $search_field ne "b.band_id";
	push @fields, ("Year","Genre");

	my $output = <<EOQ;
<b>List of CDs</b>
<table border="1">
<tr>
EOQ
	foreach (@fields) { $output .= "<th>$_</th>\n"; }
	$output .= <<EOQ;
</tr>
EOQ

# Now we start putting together our SQL select statement. First, a list
# of the columns we're going to be getting. We don't want to add the
# column from the 'tracks' table to the query if we aren't looking
# for specific track info, because we'd end up getting the same
# CDs once for every track on them. So that's only added conditionally.

	my $columns = "b.band_id, b.band_name, b.band_prefix";
	$columns .= ", c.cd_id, c.cd_title, c.cd_release_year, c.cd_genre";
	if ($search_field eq "t.track_name")
	{
		$columns .= ", t.track_name";
	}

# Much the same for the list of tables used in the FROM clause of the
# select statement.
	my $tables = "bands b, cds c";
	if ($search_field eq "t.track_name")
	{
		$tables .= ", tracks t";
	}

# We begin the WHERE clause with a join of the 'cds' table and the 'bands'
# table. Only if we're asking for track information do we add a join to
# the 'tracks' table as well.

	my $where = "c.band_id = b.band_id";
	if ($search_field eq "t.track_name")
	{
		$where .= " and c.cd_id = t.cd_id";
	}

# We complete the WHERE clause and build our ORDER BY clause at the same time.
# Because MySQL is not picky about doing text-like comparisons on non-text
# fields (e.g., 'cd_release_year'), we can do a LIKE comparison between
# the $search_field and the $search_value no matter what column we're
# checking. If we've been given a band ID, though, just do a straight
# match on the 'band_id' column of the 'bands' table, since it's an
# indexed column. Note that we're using the quote() DBI method on
# the value being sought, in case it's "Baba O'Reilly" or the like.
# We also use the SQL wildcard character "%" at the beginning and
# end of the match value, so that we'll match a value occurring
# anywhere in the field. We'll order the results by the field being
# checked, unless it's the 'band_id' field, and then by CD title
# and band name.

	my $order_by;
	if ($search_field eq "b.band_id")
	{
		$where .= " and b.band_id = $search_value";
	}
	elsif ($search_field ne "")
	{
		$where .= " and lower("
			.$search_field
			.") like lower("
			.$dbh->quote("%".$search_value."%")
			.")"
		;
		$order_by .= $search_field.",";
	}
	$order_by .= "cd_title, band_name, band_prefix, cd_release_year desc";

# Put it all together and run the query.

	my $query = <<EOQ;
select $columns
from $tables
where $where
order by $order_by
EOQ
	my $result = $dbh->selectall_hashref($query)
		or return show_error($dbh->errstr);

# Now that we've got our results, we'll create the HTML table rows to
# display them. Again, only display track names and/or band names if
# appropriate.

	my $row;
	my $band_name;
	foreach $row (@$result)
	{
		$output .= "<tr>\n";
		if ($$row{'track_name'} ne "")
		{
			$output .= <<EOQ;
<td>$$row{'track_name'}</td>
EOQ
		}
		$output .= <<EOQ;
<td><a href="index.pl?action=edit_cd&cd_id=$$row{'cd_id'}">$$row{'cd_title'}</a></td>
EOQ
		if ($search_field ne "b.band_id")
		{
			$band_name =
			fix_band_name($$row{'band_name'}, $$row{'band_prefix'});
			$output .= <<EOQ;
<td><a href="index.pl?action=edit_band&band_id=$$row{'band_id'}">$band_name</a></td>
EOQ
		}
		$output .= <<EOQ;
<td>$$row{'cd_release_year'}</td>
<td>$$row{'cd_genre'}</td>
</tr>
EOQ
	}

# Finally, close the HTML table and return it.

	$output .= <<EOQ;
</table>
EOQ
	return $output;
}


# string list_bands (DBI handle $dbh)

# This function returns an HTML table containing a list of all unique
# band names in the CD database. Each band name is displayed as a link
# to the band editing form (see edit_band()).
# In case of a database error, the function returns the result of show_error().

sub list_bands
{
	my $dbh = shift;

	my $output = <<EOQ;
<b>List of Bands</b>
<table border="1">
<tr>
<th>Artist</th>
<th>Genre</th>
</tr>
EOQ

	my $query = <<EOQ;
select b.band_id, b.band_name, b.band_prefix, b.band_genre
from bands b
order by band_name, band_prefix
EOQ

	my $result = $dbh->selectall_hashref($query)
		or return show_error($dbh->errstr);
	my $row;
	foreach $row (@$result)
	{
		my $band_name =
			fix_band_name($$row{'band_name'}, $$row{'band_prefix'});
		$output .= <<EOQ;
<tr>
<td><a href="index.pl?action=edit_band&band_id=$$row{'band_id'}">$band_name</a></td>
<td>$$row{'band_genre'}</td>
</tr>
EOQ
	}
	$output .= <<EOQ;
</table>
EOQ
	return $output;
}


# string search_form (void)

# This function prints out a form allowing the user to search the CD
# database by band name or genre, CD name or year released, or track
# name.

sub search_form
{
	my $output = <<EOQ;
<form method=post>
<b>Search:</b>
<select name="search_field">
<option>
<option value="b.band_name">Band Name
<option value="b.band_genre">Band Genre
<option value="c.cd_title">CD Title
<option value="c.cd_release_year">Year CD Released
<option value="t.track_name">Track Name
</select>
matches
<input type=text name="search_value" size="20">
<input type="submit" name="action" value="Search">
</form>
EOQ
	return $output;
}


# int save_cd (CGI handle $cgi, DBI handle $dbh)

# This function writes information about a CD and the associated band,
# retrieved via the first argument $cgi, to the CD database. It returns
# the ID number for the CD.
# In case of a database error, the function returns the result of show_error().

sub save_cd
{
	my $cgi = shift;
	my $dbh = shift;

# The first thing save_cd() does is pass the buck to save_band().
# If the band information can't be entered into the database, there's
# no point in trying to do anything with the CD information:

	my $band_id = save_band($cgi,$dbh);
	if (!$band_id)
	{
		return show_error("Error saving band for CD");
	}

	my ($cd_id,$cd_title);
	$cd_id = $cgi->param('cd_id');
	$cd_title = $cgi->param('cd_title');
	if (!$cd_id)
	{
		if (!$cd_title)
		{
			return show_error("No CD Title");
		}
		else
		{
			($cd_id) = $dbh->selectrow_array(
		"select cd_id from cds where band_id = ? and cd_title = ?"
				, undef
				, ($band_id, $cd_title)
			);
		}
		if (!$cd_id)
		{
			$dbh->do(
		"insert into cds (band_id,cd_title) values ($band_id,'NEW CD')"
			);
			$cd_id = get_key_value($dbh);
		}
	}

	my $q = <<EOQ;
replace into cds (cd_id, band_id, cd_title, cd_release_year, cd_genre)
values (?, ?, ?, ?, ?)
EOQ
	my $sth = $dbh->prepare($q);
	my $cd_genre = $cgi->param('cd_genre');
	if (!$cd_genre)
	{
		$cd_genre = $cgi->param('new_cd_genre');
	}
	if (!$cd_genre)
	{
		($cd_genre) = $dbh->selectrow_array(
		 "select band_genre from bands where band_id = $band_id"
		);
	}
	$sth->execute(
		$cd_id
		, $band_id
		, $cd_title
		, $cgi->param('cd_release_year')
		, $cd_genre
	);
	$dbh->do("delete from tracks where cd_id = $cd_id");
	$q = <<EOQ;
insert into tracks (cd_id, track_no, track_name, track_mins, track_secs)
values (?, ?, ?, ?, ?)
EOQ
	$sth = $dbh->prepare($q);
	for (my $i = 1; $i <= 40; $i++)
	{
		if ($cgi->param("track_name_$i"))
		{
			$sth->execute(
				$cd_id
				, $i
				, $cgi->param("track_name_$i")
				, $cgi->param("track_mins_$i")
				, $cgi->param("track_secs_$i")
			);
		}
	}
	save_genre($dbh, $cd_genre);
	return $cd_id;
}


# int save_band (CGI handle $cgi, DBI handle $dbh)

# This function writes information about a band, retrieved via the first
# argument $cgi, to the CD database. It returns the ID number for the band.
# In case of a database error, the function returns the result of show_error().

sub save_band
{
	my $cgi = shift;
	my $dbh = shift;


# We start by getting the values of the Artist First Name and Last Name
# fields. We have to allow for a person having only a first name or a
# last name, so we can put our Prince CDs into the database.

	my ($band_prefix,$band_name);
	$band_prefix = $cgi->param('artist_first');
	$band_name = $cgi->param('artist_last');
	if (!$band_name)
	{
		if ($band_prefix)
		{
			$band_name = $band_prefix;
			$band_prefix = "";
		}
	}

# If $band_name is still blank, we'll look at the Band Name field.
# Names starting with "The " get that stripped off and stored in
# $band_prefix.

	if (!$band_name)
	{
		$band_name = $cgi->param('band_name');
		if ($band_name =~ /^The /)
		{
			($band_prefix,$band_name)
				= ($band_name =~ /^(The) (.*)$/);
		}
		else
		{
			$band_prefix = "";
		}
	}

# Now we've got whatever band name there was to get. Next, we check the
# 'band_id' field, to see if this is supposed to be for an existing
# record or not. If it's empty, we check to see if we already have
# a record for this band, just in case. No band ID and no band name
# will cause an error to be printed out and the function to return zero.
# If a band ID is passed in, and $band_name is empty, then that
# means the user picked a band from the <SELECT> field, and we
# don't have to bother writing a record to the database; we can
# just return the band ID.

	my $band_id = $cgi->param('band_id');
	if (!$band_id)
	{
		if (!$band_name)
		{
			return show_error("No Band or Artist Name");
		}
		else
		{
			($band_id) = $dbh->selectrow_array(
	"select band_id from bands where band_prefix = ? and band_name = ?"
				, undef
				, ($band_name,$band_prefix)
			);
		}
		if (!$band_id)
		{
# if new band, create record
			$dbh->do(
			"insert into bands (band_name) values ('NEW BAND')"
			);
			$band_id = get_key_value($dbh);
		}
	}
	elsif (!$band_name)
	{
# nothing to do here, show's over, move along
# - on the "New CD" form, the user can pick just the band_id from a list
		return $band_id;
	}

# Now we write the rest of the information about the band to the database
# and return the band ID value.

	my $band_genre = $cgi->param('band_genre');
	if (!$band_genre)
	{
		$band_genre = $cgi->param('new_band_genre');
	}
	my $q = <<EOQ;
replace into bands (band_id, band_prefix, band_name, band_genre)
values (?,?,?,?)
EOQ
	my $sth = $dbh->prepare($q) or return show_error($dbh->errstr);
	$sth->execute($band_id, $band_prefix,$band_name,$band_genre)
		or return show_error($dbh->errstr);

	save_genre($dbh, $band_genre);

	return $band_id;
}


# int save_genre (DBI handle $dbh, string $genre)

# This function writes a genre name, from the second argument $genre,
# to the 'genres' table in the CDs database.  If $genre is blank, or
# if there is a database error, the function returns the result of show_error().
# Otherwise, it returns the result of the database 'replace' statement.

sub save_genre
{
	my $dbh = shift;
	my $genre = shift;

	return show_error("Error: no genre given") if not $genre;
	my $rv =
	$dbh->do("replace into genres (genre) values (?)",undef,($genre))
		or return show_error($dbh->errstr);
	return $rv;
}


# int kill_cd (DBI handle $dbh, int $cd_id)

# This function deletes records for the CD specified by the second argument,
# $cd_id, from the 'tracks' and 'cds' tables in the CD database. It returns
# the ID number for the CD.
# In case of a database error, the function returns the result of show_error().

sub kill_cd
{
	my $dbh = shift;
	my $cd_id = shift;

	if ($cd_id)
	{
		$dbh->do("delete from tracks where cd_id=$cd_id")
			or return show_error($dbh->errstr);
		$dbh->do("delete from cds where cd_id=$cd_id")
			or return show_error($dbh->errstr);
	}
	return $cd_id;
}


# int kill_band (CGI handle $cgi, DBI handle $dbh)

# This function deletes records from the CD database that pertain to a specific
# band, identified by the 'band_id' field obtained from the CGI handle $cgi.
# The ID number of the band is returned.
# In case of a database error, the function returns the result of show_error().

sub kill_band
{
	my $cgi = shift;
	my $dbh = shift;

	my $band_id = $cgi->param('band_id');

	if ($band_id)
	{
		my $cds = $dbh->selectall_arrayref("select cd_id from cds where band_id = $band_id");
		foreach my $r (@$cds)
		{
			if (!kill_cd($dbh, $$r[0]))
			{
				return show_error("Error deleting band ($band_id) - could not delete CD ($$r[0])");
			}
		}
		$dbh->do("delete from bands where band_id=$band_id")
			or return show_error($dbh->errstr);
	}
	return $band_id;
}
# string band_form (DBI handle $dbh, int $band_id, array $genres)

# This function returns an HTML table containing form fields for editing
# information about the band identified by the second argument, $band_id.
# If $band_id is blank or zero, empty fields are displayed allowing
# for the entry of a new band.
# In case of a database error, the function returns the result of show_error().

sub band_form
{
	my $dbh = shift;
	my $band_id = shift;
	my $genres = shift;

	my ($band_prefix,$band_name,$band_genre);
	my ($artist_first,$artist_last);

	if ($band_id)
	{
		my $query = <<EOQ;
select band_prefix,band_name,band_genre from bands where band_id=$band_id
EOQ
		($band_prefix,$band_name,$band_genre)
			= $dbh->selectrow_array($query) or print $dbh->errstr;
		if ($band_prefix eq "The")
		{
			$band_name = "$band_prefix $band_name";
		}
		elsif ($band_prefix ne "")
		{
			$artist_first = $band_prefix;
			$artist_last = $band_name;
			$band_prefix = "";
			$band_name = "";
		}
	}

# Here, we give the user two ways to enter the artist's name:
# a single text field, 'Band Name', or in two text fields for
# an actual person's first name and last name, 'Artist First Name'
# and 'Artist Last Name'. We also let the user choose from existing
# genres, or enter a new one (which is how the list of genres gets
# built up):

	my $genre_field = genre_select_field($genres,"band_genre",$band_genre);
	my $output = <<EOQ;
<table border="1">
<tr>
<td valign="top" align="left">Band Name:</td>
<td valign="top" align="left">
<input type="hidden" name="band_id" value="$band_id">
<input type="text" size="30" name="band_name" value="$band_name">
</td>
</tr>
<tr><td>OR</td></tr>
<tr>
<td>Artist First Name:</td>
<td><input type="text" size="20" name="artist_first" value="$artist_first"></td>
</tr>
<tr>
<td>Artist Last Name:</td>
<td><input type="text" size="20" name="artist_last" value="$artist_last"></td>
</tr>
<tr>
<td>Band Genre:</td>
<td>$genre_field</td>
</tr>
</table>
EOQ

	return $output;
}


# string header (void)

# This function returns the opening tags for an HTML page,
# and navigation links to the various parts of the application.

sub header
{
	my $thisfile = uri_escape($ENV{'SCRIPT_FILENAME'});
	return <<EOQ;
<html>
<title>CDs Database</title>
<body bgcolor=white>
<h3>CDs Database</h3>
<a href="index.pl">Home</a>
|
<a href="index.pl?action=list_bands">List Bands</a>
|
<a href="index.pl?action=list_cds">List CDs</a>
|
<a href="index.pl?action=edit_band&band_id=0">Add New Band</a>
|
<a href="index.pl?action=edit_cd&cd_id=0">Add New CD</a>
|
<a href="source.pl?file=$thisfile">Source</a>
|
<a href="dbschema.txt">DB Schema</a>
<br>
<br>
EOQ
}


# string footer (void)

# This function returns the closing tags of an HTML page.

sub footer
{
	return <<EOQ;
<br>
<br>
</body>
</html>
EOQ
}


# array get_genres (DBI handle $dbh)

# This function retrieves the current list of genre descriptions
# and returns it as a simple list.
# In case of a database error, the function returns the result of show_error().

sub get_genres
{
	my $dbh = shift;

	my $result = $dbh->selectall_arrayref("select genre from genres")
		or return show_error($dbh->errmsg);
	my @genres = ();
	foreach my $row (@$result)
	{
		if (@$row[0]) { push @genres, @$row[0]; }
	}
	return @genres;
}


# string genre_select_field (array $genres, string $field, string $value)

# The genre_select_field() function is pretty simple. We pass in the
# field name - it might be 'band_genre' or 'cd_genre' - and an existing
# value, if we have one. The function returns a <SELECT> field if there
# are any existing genre values, and a text input field for a new genre:

sub genre_select_field
{
	my $genres = shift;
	my $field = shift;
	my $value = shift;

# some kind of default value
	$field = "genre" if not $field;

	my $output = "Genre:";
	if (@$genres)
	{
		$output .= <<EOQ;
<select name="$field">
<option>
EOQ
		foreach (@$genres)
		{
			my $selected = $_ eq $value ? "selected" : "";
			$output .= <<EOQ;
<option $selected>$_
EOQ
		}
		$output .= <<EOQ;
</select>
EOQ
	}
	$output .= <<EOQ;
New: <input type="text" size="5" name="new_$field">
EOQ
	return $output;
}


# string band_select_field (DBI handle $dbh)

# This function gets a list of existing bands from the database and returns
# it as a <SELECT> field.
# In case of a database error, the function returns the result of show_error().

sub band_select_field
{
	my $dbh = shift;

	my $query = <<EOQ;
select band_id, band_prefix, band_name
from bands
order by band_name, band_prefix
EOQ
	my $bands = $dbh->selectall_hashref($query)
		or return show_error($dbh->errstr);

	my $output = <<EOQ;
<select name="band_id">
<option>
EOQ
	my $band_name;
	foreach my $row (@$bands)
	{
                $band_name =
			fix_band_name($$row{'band_name'}, $$row{'band_prefix'});
		$output .= <<EOQ;
<option value="$$row{'band_id'}">$band_name
EOQ
	}
	$output .= <<EOQ;
</select>
EOQ
	return $output;
}


# int get_key_value (DBI handle $dbh)

# The get_key_value() function calls the MySQL last_insert_id() function.
# and returns the result. It's separated out into a function so that if we
# do have to change the way it works, we only change one thing.
# In case of a database error, the function returns the result of show_error().

sub get_key_value
{
	my $dbh = shift;

	my $key;
	($key) = $dbh->selectrow_array("select last_insert_id()")
		or return show_error($dbh->errstr);
	return $key;
}


# string fix_band_name ([string $band_name[, string $band_prefix]])

# This function reconstructs a complete band or artist name from
# the arguments, $band_name (e.g. an artist's last name) and
# $band_prefix (e.g. an artist's first name).

sub fix_band_name
{
	my $band_name;
	my $band_prefix;
	my $output;

	$band_name = shift;
	$band_prefix = shift;

	$output = $band_prefix." ".$band_name;

	$output =~ s/^[\s]*(.*)[\s]*$/$1/;

	return $output;
}


# int show_error (string $errmsg)

# This function displays error messages in a uniform manner and returns
# zero.

sub show_error
{
	my $errmsg = shift;
	print <<EOQ;
<font color="red">
<b>
<li>ERROR: $errmsg
</b>
</font>
EOQ
	return 0;
}