function writeTable($qualifier, $subject_id = '', $description_search = 0)
{
global $IconPath;
global $proxyURL;
$db = new Querier();
// sanitize submission
switch ($qualifier) {
case "Num":
$connection = $db->getConnection();
$statement = $connection->prepare("SELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n \tWHERE left(title, 1) REGEXP '[[:digit:]]+'\n \tAND eres_display = 'Y'\n \tORDER BY newtitle");
$statement->execute();
$results = $statement->fetchAll();
break;
case "All":
$connection = $db->getConnection();
$statement = $connection->prepare("\n SELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide, alternate_title\n FROM title as t\n INNER JOIN location_title as lt\n ON t.title_id = lt.title_id\n INNER JOIN location as l\n ON lt.location_id = l.location_id\n INNER JOIN restrictions as r\n ON l.access_restrictions = r.restrictions_id\n INNER JOIN rank as rk\n ON rk.title_id = t.title_id\n INNER JOIN source as s\n ON rk.source_id = s.source_id\n WHERE title != ''\n AND eres_display = 'Y'\n \t\tORDER BY newtitle");
$statement->execute();
$results = $statement->fetchAll();
break;
case "bysub":
if (isset($subject_id)) {
// get title ids in pluslets' resource token connected to subject
// I don't know the purpose of this, so I zeroed it out below by resetting the $lobjTitleIds array --agd
$lobjGuide = new Guide($subject_id);
$lobjTitleIds = $lobjGuide->getRelatedTitles();
$lobjTitleIds = array();
if (count($lobjTitleIds) > 0) {
$connection = $db->getConnection();
$statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide, alternate_title\n \tFrom title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE title LIKE :qualifer\n \tAND eres_display = 'Y'\n ORDER BY newtitle");
/*
$condition1 = "WHERE (subject_id = $subject_id";
$condition1 .= count ( $lobjTitleIds ) > 0 ? "\nOR t.title_id IN (" . implode ( ',', $lobjTitleIds ) . ")" : "";
$condition1 .= ")";
*/
$letter = "%" . $qualifier . "%";
$statement->bindParam(":qualifer", $letter);
$statement->execute();
$results = $statement->fetchAll();
} else {
$connection = $db->getConnection();
$statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide, alternate_title\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE subject_id = :subject_id\n \tAND eres_display = 'Y'\n ORDER BY newtitle");
$statement->bindParam(":subject_id", $subject_id);
$statement->execute();
$results = $statement->fetchAll();
}
} else {
$connection = $db->getConnection();
$statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE title LIKE :qualifer\n \tAND eres_display = 'Y'\n ORDER BY newtitle");
$letter = "%" . $qualifier . "%";
$statement->bindParam(":qualifer", $letter);
$statement->execute();
$results = $statement->fetchAll();
}
break;
case "bytype":
if (isset($_GET["type"])) {
$type = scrubData("%" . $_GET["type"] . "%");
$connection = $db->getConnection();
$statement = $connection->prepare("\n \t\tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n FROM title as t\n INNER JOIN location_title as lt\n ON t.title_id = lt.title_id\n INNER JOIN location as l\n ON lt.location_id = l.location_id\n INNER JOIN restrictions as r\n ON l.access_restrictions = r.restrictions_id\n INNER JOIN rank as rk\n ON rk.title_id = t.title_id\n INNER JOIN source as s\n ON rk.source_id = s.source_id\n WHERE ctags LIKE :type\n AND eres_display = 'Y'\n ORDER BY newtitle");
$statement->bindParam(":type", $type);
$statement->execute();
$results = $statement->fetchAll();
}
break;
case "search":
// If you uncomment the next line, it will search description field
$connection = $db->getConnection();
$statement = $db->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE (title LIKE :qualifier OR description LIKE :qualifier)\n \tAND eres_display = 'Y'\n ORDER BY newtitle");
$qualifier = "%" . $qualifier . "%";
$statement->execute();
$results = $statement->fetchAll();
break;
default:
// This is the simple output by letter and also the search
if (strlen($qualifier) == 1) {
// Is like the first letter
$connection = $db->getConnection();
$statement = $connection->prepare("\n\tSELECT DISTINCT LEFT(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide,alternate_title\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE left(t.title, 1) LIKE :qualifer\n \tAND eres_display = 'Y'\n\n UNION\n \tSELECT DISTINCT LEFT(alternate_title,1) as initial, alternate_title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide,alternate_title\n FROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE left(alternate_title, 1) LIKE :qualifer\n \tAND eres_display = 'Y'\nORDER BY newtitle\n\n");
$letter = $qualifier . "%";
$statement->bindParam(":qualifer", $letter);
$statement->execute();
$results = $statement->fetchAll();
} else {
$connection = $db->getConnection();
$statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE title LIKE :qualifer\n \tAND eres_display = 'Y'\n ORDER BY newtitle");
$letter = "%" . $qualifier . "%";
$statement->bindParam(":qualifer", $letter);
$statement->execute();
$results = $statement->fetchAll();
$condition1 = "WHERE title LIKE " . $db->quote("%" . $qualifier . "%");
}
if ($description_search == 1) {
$connection = $db->getConnection();
$statement = $connection->prepare("\n \tSELECT distinct left(t.title,1) as initial, t.title as newtitle, t.description, location, access_restrictions, t.title_id as this_record,eres_display, display_note, pre, citation_guide, ctags, helpguide\n \tFROM title as t\n \tINNER JOIN location_title as lt\n \tON t.title_id = lt.title_id\n \tINNER JOIN location as l\n \tON lt.location_id = l.location_id\n \tINNER JOIN restrictions as r\n \tON l.access_restrictions = r.restrictions_id\n \tINNER JOIN rank as rk\n \tON rk.title_id = t.title_id\n \tINNER JOIN source as s\n \tON rk.source_id = s.source_id\n WHERE (title LIKE :qualifer OR description LIKE :qualifer1)\n \tAND eres_display = 'Y'\n ORDER BY newtitle");
$qualifier = "%" . $qualifier . "%";
$statement->bindParam(":qualifer", $qualifier);
$statement->bindParam(":qualifer1", $qualifier);
$statement->execute();
$results = $statement->fetchAll();
}
}
$num_rows = count($results);
if ($num_rows == 0) {
return "<div class=\"no_results\">" . _("Sorry, there are no results at this time.") . "</div>";
}
// prepare header
$items = "<table width=\"98%\" class=\"item_listing trackContainer\">";
$row_count = 0;
$colour1 = "oddrow";
$colour2 = "evenrow";
foreach ($results as $myrow) {
$row_colour = $row_count % 2 ? $colour1 : $colour2;
$patterns = "/'|\"/";
$replacements = "";
$item_title = $myrow[1];
if ($myrow["pre"] != "") {
$item_title = $myrow["pre"] . " " . $item_title;
}
$safe_title = trim(preg_replace($patterns, $replacements, $item_title));
$blurb = $myrow["description"];
$bib_id = $myrow[5];
// / CHECK RESTRICTIONS ///
if ($myrow['4'] == 2 or $myrow['4'] == 3) {
$url = $proxyURL . $myrow[3];
$rest_icons = "restricted";
} elseif ($myrow['4'] == 4) {
$url = $myrow[3];
$rest_icons = "restricted";
} else {
$url = $myrow[3];
$rest_icons = "";
// if you want the unlocked icon to show, enter "unrestricted" here
}
$current_ctags = explode("|", $myrow["ctags"]);
// add our $rest_icons info to this array at the beginning
array_unshift($current_ctags, $rest_icons);
$icons = showIcons($current_ctags);
// / Check for Help Guide ///
if ($myrow["helpguide"] != "") {
$helpguide = " <a href=\"" . $myrow["helpguide"] . "\"><img src=\"{$IconPath}/help.gif\" border=\"0\" alt=\"" . _("Help Guide") . "\" title=\"" . _("Help Guide") . "\" /></a>";
} else {
$helpguide = "";
}
// Check if there is a display note
if ($myrow["display_note"] == NULL) {
$display_note_text = "";
} else {
$display_note_text = "<br /><strong>" . _("Note:") . " </strong>" . $myrow['display_note'];
}
$bonus = "{$blurb}<br />";
if ($blurb != "") {
$information1 = "<span id=\"bib-{$bib_id}\" class=\"toggleLink curse_me\"><img src=\"{$IconPath}/information.png\" border=\"0\" alt=\"" . _("more information") . "\" title=\"" . _("more information") . "\" /></span>";
// This is new details link; you can use the one above if you prefer
$information = "<span id=\"bib-{$bib_id}\" class=\"toggleLink curse_me\">" . _("about") . "</span>";
} else {
$information = "";
$information1 = "";
}
$target = targetBlanker();
$items .= self::generateLayout($row_colour, $url, $target, $item_title, $information, $information1, $icons, $helpguide, $display_note_text, $bonus);
$row_count++;
}
$items .= "</table>";
return $items;
}
