<?php
include ("config.php");
// Site Language selector
//
// The site can become unusable if there are mistakes in the chosen language file
// English is therefore loaded first
// After that, a language file is only loaded if the config file sets a different language
// than english and the file exists. The rest is up to the user.
include ("languages/language-en.php");
if ($choralcfg_sitelanguage !== "en") {
$sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php";
if (file_exists($sitelanguagefilename)) {
include ($sitelanguagefilename);
}
}
//
function html_begin ($title, $header, $cssfile) {
header("Content-Type: text/html; charset=utf-8");
print("<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\" \"http://www.w3.org/TR/html4/loose.dtd\">");
print("<html>\n");
print("<head>\n");
if ($title)
print("<title>$title</title>\n");
print("<link href=\"$cssfile\" rel=\"stylesheet\" type=\"text/css\">");
print("<meta http-equiv=\"Content-type\" content=\"text/html;charset=UTF-8\" />");
print("</head>\n");
print("<body bgcolor=\"#FFFFFF\">\n");
if ($header)
print("<h2>$header</h2>\n");
}
function library_footer() {
}
function html_end() {
print("</body>\n</html>\n");
}
function connect_db($host,$user,$passwd,$database) {
$link = mysql_connect($host,$user,$passwd);
if ( $link && mysql_select_db($database) )
{
return($link);
}
else {
return (FALSE);
}
}
// function to print the search form
function search_form() {
global $DEBUG,$link,$PHP_SELF;
//temporary fix, since functions do not see the included files??
include ("config.php");
include ("languages/language-en.php");
if ($choralcfg_sitelanguage !== "en") {
$sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php";
if (file_exists($sitelanguagefilename)) {
include ($sitelanguagefilename);
}
}
////
$search_string = "<form action='$PHP_SELF' method='POST'><table border=0 cellpadding=10>\n";
$search_string .= "<tr><td>$choral_title</td>".
"<td><input type=text name=the_title value='' size=50 maxlength=80></td></tr>\n";
$search_string .= "<tr><td>$choral_author<br>($choral_composer, $choral_lyricist ,...)</td>".
"<td><input type=text name=artist value='' size=50 maxlength=80></td></tr>\n";
$search_string .= "<tr><td>$choral_language</td>\n";
$sql = "select language_id, language_name ".
"from language order by language_name ASC";
$result = mysql_query($sql, $link);
$search_string .= "<td><select name=language_id >\n";
$search_string .= "<option value=0>$choral_any</option>\n";
while ($row = mysql_fetch_object($result)) {
$language_id = $row -> language_id;
$language[$language_id] = $row -> language_name;
$search_string .= "<option value=$language_id >$language[$language_id]</option>\n";
}
$search_string .= "</select></td></tr>\n";
$search_string .= "<tr><td>$choral_genre</td>";
$sql = "select genre_id, genre_name ".
"from genre order by genre_name ASC";
$result = mysql_query($sql, $link);
$search_string .= "<td><select name=genre_id >\n";
$search_string .= "<option value=0>$choral_any</option>\n";
while ($row = mysql_fetch_object($result)) {
$genre_id = $row -> genre_id;
$genre[$genre_id] = $row -> genre_name;
$search_string .= "<option value=$genre_id >$genre[$genre_id]</option>\n";
}
$search_string .= "</select></td></tr>\n";
$search_string .= "<tr><td>$choral_accompaniment</td>";
$sql = "select accompaniment_id, accompaniment_name ".
"from accompaniment order by accompaniment_name ASC";
$result = mysql_query($sql, $link);
$search_string .= "<td><select name=accompaniment_id >\n";
$search_string .= "<option value=0>$choral_any</option>\n";
while ($row = mysql_fetch_object($result)) {
$accompaniment_id = $row -> accompaniment_id;
$accompaniment[$accompaniment_id] = $row -> accompaniment_name;
$search_string .= "<option value=$accompaniment_id >$accompaniment[$accompaniment_id]</option>\n";
}
$search_string .= "</select></td></tr>\n";
$search_string .= "<tr><td>$choral_typeofsoloist</td><td>\n".
"<select name=soloist>\n".
"<option>$choral_any</option>\n".
"<option>$choral_soprano</option>\n".
"<option>$choral_mezzosoprano</option>\n".
"<option>$choral_contralto</option>\n".
"<option>$choral_alto</option>\n".
"<option>$choral_countertenor</option>\n".
"<option>$choral_tenor</option>\n".
"<option>$choral_baritone</option>\n".
"<option>$choral_bass</option>\n".
"</select></td></tr>\n";
$search_string .= "<tr><td>$choral_publisher</td>\n";
$sql = "select publisher_id, publisher_name ".
"from publisher order by publisher_name ASC";
$result = mysql_query($sql, $link);
$search_string .= "<td><select name=publisher_id >\n";
$search_string .= "<option value=0>$choral_any</option>\n";
while ($row = mysql_fetch_object($result)) {
$publisher_id = $row -> publisher_id;
$publisher[$publisher_id] = $row -> publisher_name;
$search_string .= "<option value=$publisher_id >$publisher[$publisher_id]</option>\n";
}
$search_string .= "</select></td></tr>\n";
$search_string .= "<tr><td>$choral_collection</td>";
$sql = "select collection_id, collection_name ".
"from collection order by collection_name ASC";
$result = mysql_query($sql, $link);
$search_string .= "<td><select name=collection_id >\n";
$search_string .= "<option value=0>$choral_any</option>\n";
while ($row = mysql_fetch_object($result)) {
$collection_id = $row -> collection_id;
$collection[$collection_id] = $row -> collection_name;
$search_string .= "<option value=$collection_id >$collection[$collection_id]</option>\n";
}
$search_string .= "</select></td></tr>\n";
$search_string .= "<TR><TD>$choral_voicing</TD>".
"<TD><input type=text name=voicing size=50 maxlength=20 value=''></TD></TR>\n";
$search_string .= "<tr><td colspan=2 align=center><input type=submit value=\"$choral_captionsearch\"> <input type=reset value=\"$choral_captionreset\"></td></tr>\n";
$search_string .= "</table>\n";
$search_string .= "<input type=hidden name=searching value='1'>\n";
$search_string .= "</form>\n";
return $search_string;
}
// function to return a string array of formatted info about an array of work ids
function format_works($work_ids) {
global $DEBUG, $link, $PHP_SELF;
//temporary fix, since functions do not see the included files??
include ("config.php");
include ("languages/language-en.php");
if ($choralcfg_sitelanguage !== "en") {
$sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php";
if (file_exists($sitelanguagefilename)) {
include ($sitelanguagefilename);
}
}
////
$search_path = "$PHP_SELF";
if ($DEBUG) print "search path = $search_path";
$soloists = array($choral_soprano,$choral_mezzosoprano,$choral_alto,$choral_contralto,$choral_countertenor,$choral_tenor,$choral_baritone,$choral_bass);
// loop through the work ids and get all of the info with queries
while(list($key,$value) = each($work_ids)) {
$this_work_string = "";
$work_id = $value;
$sql = "select * from work where work_id=$work_id";
$result = mysql_query($sql, $link);
$row = mysql_fetch_object($result);
$catalog_number = $row -> catalog_number;
$the_title = $row -> title;
$duration = $row -> duration;
$publisher_id = $row -> publisher_id;
$available = $row -> available;
$voicing = $row -> voicing;
$other = $row -> other;
$this_work_string .= "<P><table border=0>\n";
//$this_work_string .= "<TR><TD><B>WMC Catalog Number:</B></TD><TD>$catalog_number</TD></TR>\n";
$this_work_string .= "<TR><TD><B>$choral_title</B></TD>".
"<TD><a href='$search_path?work_id=$work_id&searching=1'>$the_title</a></TD></TR>\n";
if ($duration > 0) {
$this_work_string .= "<TR><TD><B>$choral_duration</B></TD><TD>$duration</TD></TR>\n";
} else {
$this_work_string .= "<TR><TD><B>$choral_duration</B></TD><TD>n/a</TD></TR>\n";
}
$this_work_string .= "<TR><TD><B>$choral_author_s:</B></TD><TD></TD></TR>\n";
$sql = "select person_work.person_id, concat(last_name, ', ', first_name) as name, type from person_work, person where ".
"person_work.work_id='$work_id' and person_work.person_id = person.person_id";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_object($result)) {
$name = $row -> name;
$person_id = $row -> person_id;
$type = $row -> type;
$this_work_string .= "<TR><TD>  $type:</TD><TD>".
"<a href='$search_path?person_id=$person_id&searching=1'>$name</a></TD></TR>\n";
}
$this_work_string .= "<TR><TD><B>$choral_language</B></TD><TD></TD></TR>\n";
$sql = "select language_work.language_id, language_name from language_work, language where ".
"language_work.work_id='$work_id' and language_work.language_id = language.language_id";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_object($result)) {
$name = $row -> language_name;
$language_id = $row -> language_id;
$this_work_string .= "<TR><TD></TD><TD><a href='$search_path?language_id=$language_id&searching=1'>$name</a></TD></TR>\n";
}
$this_work_string .= "<TR><TD><B>$choral_accompaniment</B></TD><TD></TD></TR>\n";
$sql = "select accompaniment_work.accompaniment_id, accompaniment_name from accompaniment_work, accompaniment where ".
"accompaniment_work.work_id='$work_id' and accompaniment_work.accompaniment_id = accompaniment.accompaniment_id";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_object($result)) {
$name = $row -> accompaniment_name;
$accompaniment_id = $row -> accompaniment_id;
$this_work_string .= "<TR><TD></TD>".
"<TD><a href='$search_path?accompaniment_id=$accompaniment_id&searching=1'>$name</a></TD></TR>\n";
}
$this_work_string .= "<TR><TD><B>$choral_genre</B></TD><TD></TD></TR>\n";
$sql = "select genre_work.genre_id, genre_name from genre_work, genre where ".
"genre_work.work_id='$work_id' and genre_work.genre_id = genre.genre_id";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_object($result)) {
$name = $row -> genre_name;
$genre_id = $row -> genre_id;
$this_work_string .= "<TR><TD></TD>".
"<TD><a href='$search_path?genre_id=$genre_id&searching=1'>$name</a></TD></TR>\n";
}
$this_work_string .= "<TR><TD><B>$choral_typeofsoloist</B></TD><TD></TD></TR>\n";
$sql = "select * from soloist_work where work_id='$work_id'";
$result = mysql_query($sql, $link);
reset($soloists);
if(mysql_numrows($result) >=1) {
unset($solo_type);
while ($row = mysql_fetch_object($result)) {
$solo_id = $row -> soloist_work_id;
$solo_type[$solo_id] = $row -> type;
$solo_alt[$solo_id] = $row -> alternate_to;
}
while(list($key,$value) = each($soloists)) {
reset($solo_type);
while(list($type_key, $type_value) = each($solo_type)) {
if ($value == $type_value) {
if ($solo_alt[$type_key] > 0) {
$alt_key = $solo_alt[$type_key];
$alt_string = "<i>($choral_alternatesoloistto {$solo_type[$alt_key]})</i>";
} else {
$alt_string = "";
}
$this_work_string .= "<TR><TD></TD>".
"<TD><a href='$search_path?soloist=$value&searching=1'>$type_value</a> $alt_string</TD></TR>\n";
}
}
}
} else {
$this_work_string .= "<TR><TD></TD><TD>$choral_none</TD></TR>\n";
}
$publisher_query = "select publisher_name from publisher where ".
"publisher_id='$publisher_id' ";
$result = mysql_query($publisher_query, $link);
$row = mysql_fetch_object($result);
$publisher = $row -> publisher_name;
$this_work_string .= "<TR><TD><B>$choral_publisher</B></TD><TD></TD></TR>\n".
"<TR><TD></TD><TD><a href='$search_path?publisher_id=$publisher_id&searching=1'>$publisher</a></TD></TR>\n";
$this_work_string .= "<TR><TD><B>$choral_collection</B></TD><TD></TD></TR>\n";
$sql = "select collection_work.collection_id, collection_name, num_copies, catalog_num from collection_work, collection where ".
"collection_work.work_id='$work_id' and collection_work.collection_id = collection.collection_id";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_object($result)) {
$name = $row -> collection_name;
$collection_id = $row -> collection_id;
$num_copies = $row -> num_copies;
$catalog_num = $row -> catalog_num;
if (trim($catalog_num) != "") {
$catalog_num_string = trim($catalog_num);
} else {
$catalog_num_string = "n/a";
}
$this_work_string .= "<TR><TD>  $choral_collectionname<br>  $choral_numberofcopies<br>  $choral_catalognumber</TD>".
"<TD><a href='$search_path?collection_id=$collection_id&searching=1'>$name</a><br> ".
"$num_copies <br> $catalog_num_string</TD></TR>\n";
}
$sql = "select sum(num_copies) as sum_copies from collection_work where ".
"collection_work.work_id='$work_id'";
$result = mysql_query($sql, $link);
while ($row = mysql_fetch_object($result)) {
$total_num_copies = $row -> sum_copies;
}
$this_work_string .= "<TR><TD><B>$choral_totalnumberofcopies</B></TD><TD>$total_num_copies</TD></TR>\n";
$this_work_string .= "<TR><TD><B>$choral_numberofcopiesavailable</B></TD><TD>$available</TD></TR>\n";
$this_work_string .= "<TR><TD><B>$choral_voicing</B></TD>".
"<TD><a href='$search_path?voicing=$voicing'>$voicing</a></TD></TR>\n";
$this_work_string .= "<TR><TD><B>$choral_otherinfo</B></TD>";
$this_work_string .= "<TD>$other</TD></TR>\n";
$this_work_string .= "</table></P>\n";
$work_strings[] = $this_work_string;
}
return $work_strings;
}
// return an SQL query based upon the search form
function process_query_form($http_array_input) {
global $DEBUG,$link, $PHP_SELF;
//temporary fix, since functions do not see the included files??
include ("config.php");
include ("languages/language-en.php");
if ($choralcfg_sitelanguage !== "en") {
$sitelanguagefilename = "languages/language-" . $choralcfg_sitelanguage . ".php";
if (file_exists($sitelanguagefilename)) {
include ($sitelanguagefilename);
}
}
////
// the variable $http_array_input contains the HTTP_POST_VARS
while(list($key,$value)=each($http_array_input)) {
$$key = $value;
if ($DEBUG) print "http_array_input[$key] = $value<br>\n";
}
$valid_query = false;
$from = "from ";
// dummy initial where query
$where = "where work.work_id > 0";
// collection
if ($collection_id>0) {
$collection_sql = "and collection_work.collection_id = '$collection_id' and ".
"collection_work.work_id = work.work_id";
$from .= "collection_work, ";
$valid_query = true;
}
if ($publisher_id>0) {
$publisher_sql = "and work.publisher_id = '$publisher_id'";
$valid_query = true;
}
if ($language_id>0) {
$language_sql = "and language_work.language_id = '$language_id' and ".
"language_work.work_id = work.work_id";
$from .= "language_work, ";
$valid_query = true;
}
if ($genre_id>0) {
$genre_sql = "and genre_work.genre_id = '$genre_id' and ".
"genre_work.work_id = work.work_id";
$from .= "genre_work, ";
$valid_query = true;
}
if (trim($voicing) != "") {
$voicing = trim(addslashes($voicing));
$voicing_sql = "and work.voicing like '$voicing'";
$valid_query = true;
}
if ((trim($soloist) != "")&&(trim($soloist) != $choral_any)) {
$soloist = trim(addslashes($soloist));
$soloist_sql = "and soloist_work.type like '$soloist' and ".
"soloist_work.work_id = work.work_id";
$from .= "soloist_work, ";
$valid_query = true;
}
if ($accompaniment_id > 0) {
$accompaniment_sql = "and accompaniment_work.accompaniment_id = '$accompaniment_id' and ".
"accompaniment_work.work_id = work.work_id";
$from .= "accompaniment_work, ";
$valid_query = true;
}
if ($person_id > 0) {
$person_sql = "and person_work.person_id = $person_id and ".
"person_work.work_id = work.work_id";
$from .= "person_work, ";
$valid_query = true;
}
if ($work_id > 0) {
$work_sql = "and work.work_id = $work_id";
$valid_query = true;
}
if (trim($artist) != "") {
// get rid of some offenders that should never be in a name by changing em into spaces
$artist = strtr($artist,"1234567890;%@\"",
" ");
$artist = trim(addslashes($artist));
$artist_sql = "and (";
// pull tokens out of the string here and get rid of spaces and commas
$tok = strtok($artist," ,");
while ($tok) {
if (strlen($tok)>2) {
$artist_sql .= "person.last_name like '%$tok%' or person.first_name like '%$tok%' or ";
}
$tok = strtok(" ,");
}
unset($tok);
if (strlen($artist_sql) > 10) {
$artist_sql = substr($artist_sql,0,strlen($artist_sql)-3).") and ".
"person_work.person_id = person.person_id and ".
"person_work.work_id = work.work_id";
if(strpos($form,"person_work") > 0) {
$form .= "person, ";
} else {
$from .= "person, person_work, ";
}
$valid_query = true;
} else {
$artist_sql = "";
}
}
if($DEBUG) print "artist_sql = $artist_sql <br>\n";
if (trim($the_title) != "") {
$the_title = trim(addslashes($the_title));
$the_title = strtr($the_title,";%@\""," ");
$the_title_sql = "and (";
$tok = strtok($the_title, " ,");
while ($tok) {
if (strlen($tok) > 2) {
$the_title_sql .= "work.title like '%$tok%' or ";
}
$tok = strtok(" ,");
}
unset($tok);
if (strlen($the_title_sql) > 10) {
$the_title_sql = substr($the_title_sql,0,strlen($the_title_sql)-3).") ";
$valid_query = true;
} else {
$the_title_sql = "";
}
}
if($DEBUG) print "the_title_sql = $the_title_sql <br>\n";
$from .= "work ";
$where .= " $work_sql $collection_sql $publisher_sql $language_sql $genre_sql $voicing_sql $soloist_sql $accompaniment_sql ".
"$artist_sql $person_sql $the_title_sql";
$full_sql = "select work.work_id $from $where group by work.work_id order by work.title asc";
if($DEBUG) print "full_sql = $full_sql <br>\n";
if ($valid_query) {
return $full_sql;
} else {
return "";
}
}
?>