public function search()
{
$db = new Querier();
$search_param = $db->quote("%" . $this->param . "%");
$subject_id = $db->quote($this->subject_id);
switch ($this->collection) {
case "home":
$q = "SELECT subject_id AS 'id', subject AS 'matching_text',subject AS 'label', description as 'additional_text', shortform AS 'short_form', 'Subject Guide' as 'content_type', '' as 'additional_id', '' as 'parent' FROM subject\n WHERE description LIKE " . $search_param . "\n OR subject LIKE " . $search_param . "\n OR keywords LIKE " . $search_param . "\n UNION\n SELECT p.pluslet_id, p.title,p.title AS 'label', su.subject_id AS 'parent_id', su.shortform, 'Pluslet' AS 'content_type', t.tab_index as 'additional_id',su.subject as 'parent' FROM pluslet AS p\n INNER JOIN pluslet_section AS ps\n ON ps.pluslet_id = p.pluslet_id\n INNER JOIN section AS s\n ON ps.section_id = s.section_id\n INNER JOIN tab AS t\n ON s.tab_id = t.tab_id\n INNER JOIN subject AS su\n ON su.subject_id = t.subject_id\n WHERE p.body LIKE " . $search_param . "\n OR p.title LIKE " . $search_param . "\n\n UNION\n SELECT faq_id AS 'id', question AS 'matching_text',question AS 'label', answer as 'additional_text','' AS 'short_form','FAQ' as 'content_type', '' as 'additional_id', '' as 'parent' FROM faq\n WHERE question LIKE " . $search_param . "\n OR answer LIKE " . $search_param . "\n OR keywords LIKE " . $search_param . "\n UNION\n SELECT talkback_id AS 'id', question AS 'matching_text' ,question AS 'label', answer as 'additional_text','' AS 'short_form', 'Talkback' as 'content_type', '' as 'additional_id', '' as 'parent' FROM talkback\n WHERE question LIKE " . $search_param . "\n OR answer LIKE " . $search_param . "\n UNION\n SELECT staff_id AS 'id', email AS 'matching_text' ,email AS 'label', fname as 'additional_text','' AS 'short_form', 'Staff' as 'content_type', '' as 'additional_id', '' as 'parent' FROM staff\n WHERE fname LIKE " . $search_param . "\n OR lname LIKE " . $search_param . "\n OR email LIKE " . $search_param . "\n OR tel LIKE " . $search_param . "\n UNION\n SELECT department_id AS 'id', name AS 'matching_text' , name AS 'label', telephone as 'additional_text','' AS 'short_form', 'Department' as 'content_type', '' as 'additional_id','' as 'parent' FROM department\n WHERE name LIKE " . $search_param . "\n OR telephone LIKE " . $search_param . "\n UNION\n SELECT video_id AS 'id', title AS 'matching_text' ,title AS 'label', description as 'additional_text','' AS 'short_form', 'Video' as 'content_type', '' as 'additional_id', '' as 'parent' FROM video\n WHERE title LIKE " . $search_param . "\n OR description LIKE " . $search_param . "\n OR vtags LIKE " . $search_param;
break;
case "guides":
$q = "SELECT subject_id as 'id', subject,'Subject Guide' as 'content_type', subject AS 'label',shortform AS 'short_form' FROM subject WHERE subject LIKE " . $search_param . "OR shortform LIKE " . $search_param . "OR description LIKE " . $search_param . "OR keywords LIKE " . $search_param . "OR type LIKE " . $search_param;
break;
case "guide":
$q = "SELECT p.pluslet_id as 'id',su.shortform as 'short_form','Pluslet' as 'content_type', p.title, p.title AS 'label', ps.section_id, t.tab_index AS 'additional_id', t.subject_id, su.subject FROM pluslet AS p\n INNER JOIN pluslet_section AS ps\n ON ps.pluslet_id = p.pluslet_id\n INNER JOIN section AS s\n ON ps.section_id = s.section_id\n INNER JOIN tab AS t\n ON s.tab_id = t.tab_id\n INNER JOIN subject AS su\n ON su.subject_id = t.subject_id\n WHERE p.body LIKE " . $search_param . " AND t.subject_id = " . $subject_id;
break;
case "records":
$q = "SELECT title_id AS 'id', 'Record' as 'content_type',title AS 'label', title FROM title WHERE title LIKE " . $search_param;
break;
case "faq":
$q = "SELECT faq_id AS 'id',question AS 'label', LEFT(question, 55), 'FAQ' as 'content_type' FROM faq WHERE question LIKE " . $search_param;
break;
case "talkback":
$q = "SELECT talkback_id AS 'id',question AS 'label','Talkback' as content_type, LEFT(question, 55) FROM talkback WHERE question LIKE " . $search_param;
break;
case "admin":
$q = "SELECT staff_id AS 'id',email AS 'label','Staff' as 'content_type', CONCAT(fname, ' ', lname, ' (', email, ')') as fullname FROM staff WHERE (fname LIKE " . $search_param . ") OR (lname LIKE " . $search_param . ")";
break;
}
//print_r ($q);
$result = $db->query($q);
$arr = array();
$i = 0;
// This takes the results and creates an array that will be turned into JSON
foreach ($result as $myrow) {
//add no title label if empty
$myrow['label'] = empty($myrow['label']) ? '[no title]' : $myrow['label'];
$arr[$i]['label'] = $myrow['label'];
if (isset($myrow['content_type'])) {
$arr[$i]['id'] = $myrow['id'];
if (isset($myrow['short_form'])) {
$arr[$i]['shortform'] = $myrow['short_form'];
}
if (isset($myrow['matching_text'])) {
$arr[$i]['value'] = $myrow['matching_text'];
}
if (isset($myrow['content_type'])) {
$arr[$i]['content_type'] = $myrow['content_type'];
}
if (isset($myrow['parent'])) {
$arr[$i]['parent'] = $myrow['parent'];
}
if (isset($myrow['additional_id'])) {
$arr[$i]['parent_id'] = $myrow['additional_id'];
}
switch ($myrow['content_type']) {
case "Record":
$arr[$i]['label'] = $myrow['label'];
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = 'record.php?record_id=' . $myrow['id'];
} else {
$db = new Querier();
$record_url_sql = "SELECT location, title\n\t\t\t\tFROM location l, title t, location_title lt \n\t\t\t\tWHERE t.title_id = lt.title_id\n\t\t\t\tAND l.location_id = lt.location_id AND t.title_id = " . $db->quote($myrow['id']) . " ";
$record_url_result = $db->query($record_url_sql);
if (isset($record_url_result[0]['location'])) {
$arr[$i]['url'] = $record_url_result[0]['location'];
} else {
$arr[$i]['url'] = '';
}
}
break;
case "Subject Guide":
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = getControlURL() . 'guides/guide.php?subject_id=' . $myrow['id'];
} else {
$arr[$i]['url'] = 'guide.php?subject=' . $myrow['short_form'];
}
break;
case "FAQ":
$arr[$i]['label'] = $myrow['label'];
$arr[$i]['url'] = 'faq.php?faq_id=' . $myrow['id'];
break;
case "Pluslet":
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = getControlURL() . 'guides/guide.php?subject_id=' . $myrow['short_form'] . '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['hash'] = '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['label'] = $myrow['label'];
} else {
$arr[$i]['url'] = 'guide.php?subject=' . $myrow['short_form'] . '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['hash'] = '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['tab_index'] = $myrow['additional_id'];
}
break;
case "Talkback":
$arr[$i]['label'] = $myrow['label'];
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = 'talkback.php?talkback_id=' . $myrow['id'];
} else {
$arr[$i]['url'] = 'talkback.php';
}
break;
case "Staff":
$arr[$i]['label'] = $myrow['fullname'];
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = 'user.php?staff_id=' . $myrow['id'];
} else {
$name = explode('@', $myrow['label']);
$arr[$i]['url'] = 'staff_details.php?name=' . $name[0];
}
break;
}
} else {
$arr[$i]['value'] = $myrow[0];
}
$i++;
}
$response = json_encode($arr);
return $response;
}
public function search()
{
$db = new Querier();
$connection = $db->getConnection();
$search_param = "%" . $this->param . "%";
$subject_id = $this->subject_id;
switch ($this->collection) {
case "home":
$statement = $connection->prepare("SELECT subject_id AS 'id', subject AS 'matching_text',subject AS 'label', description as 'additional_text', shortform AS 'short_form', 'Subject Guide' as 'content_type', '' as 'additional_id', '' as 'parent' FROM subject\n WHERE description LIKE :search_term\n OR subject LIKE :search_term\n OR keywords LIKE :search_term\n UNION\n SELECT p.pluslet_id, p.title,p.title AS 'label', su.subject_id AS 'parent_id', su.shortform, 'Pluslet' AS 'content_type', t.tab_index as 'additional_id',su.subject as 'parent' FROM pluslet AS p\n INNER JOIN pluslet_section AS ps\n ON ps.pluslet_id = p.pluslet_id\n INNER JOIN section AS s\n ON ps.section_id = s.section_id\n INNER JOIN tab AS t\n ON s.tab_id = t.tab_id\n INNER JOIN subject AS su\n ON su.subject_id = t.subject_id\n WHERE p.body LIKE :search_term\n OR p.title LIKE :search_term\n UNION\n SELECT faq_id AS 'id', question AS 'matching_text',question AS 'label', answer as 'additional_text','' AS 'short_form','FAQ' as 'content_type', '' as 'additional_id', '' as 'parent' FROM faq\n WHERE question LIKE :search_term\n OR answer LIKE :search_term\n OR keywords LIKE :search_term\n UNION\n SELECT talkback_id AS 'id', question AS 'matching_text' ,question AS 'label', answer as 'additional_text','' AS 'short_form', 'Talkback' as 'content_type', '' as 'additional_id', '' as 'parent' FROM talkback\n WHERE question LIKE :search_term\n OR answer LIKE :search_term\n UNION\n SELECT staff_id AS 'id', email AS 'matching_text' ,email AS 'label', fname as 'additional_text','' AS 'short_form', 'Staff' as 'content_type', '' as 'additional_id', '' as 'parent' FROM staff\n WHERE fname LIKE :search_term\n OR lname LIKE :search_term\n OR email LIKE :search_term\n OR tel LIKE :search_term\n UNION\n SELECT department_id AS 'id', name AS 'matching_text' , name AS 'label', telephone as 'additional_text','' AS 'short_form', 'Department' as 'content_type', '' as 'additional_id','' as 'parent' FROM department\n WHERE name LIKE :search_term\n OR telephone LIKE :search_term\n UNION\n SELECT video_id AS 'id', title AS 'matching_text' ,title AS 'label', description as 'additional_text','' AS 'short_form', 'Video' as 'content_type', '' as 'additional_id', '' as 'parent' FROM video\n WHERE title LIKE :search_term\n OR description LIKE :search_term\n OR vtags LIKE :search_term");
break;
case "guides":
$statement = $connection->prepare("SELECT subject_id as 'id', subject,'Subject Guide' as 'content_type', subject AS 'label',shortform AS 'short_form' \n FROM subject \n WHERE active = '1'\n AND (subject LIKE :search_term\n OR shortform LIKE :search_term\n OR description LIKE :search_term\n OR keywords LIKE :search_term\n OR type LIKE :search_term)\n ");
break;
case "all_guides":
$statement = $connection->prepare("SELECT subject_id as 'id', subject,'Subject Guide' as 'content_type', subject AS 'label',shortform AS 'short_form'\n FROM subject\n WHERE (subject LIKE :search_term\n OR shortform LIKE :search_term\n OR description LIKE :search_term\n OR keywords LIKE :search_term\n OR type LIKE :search_term)\n ORDER BY subject\n ");
break;
case "guide":
$statement = $connection->prepare("SELECT p.pluslet_id as 'id',su.shortform as 'short_form','Pluslet' as 'content_type',p.type as 'type', p.title, p.title AS 'label', ps.section_id, t.tab_index AS 'additional_id', t.subject_id, su.subject FROM pluslet AS p\n INNER JOIN pluslet_section AS ps\n ON ps.pluslet_id = p.pluslet_id\n INNER JOIN section AS s\n ON ps.section_id = s.section_id\n INNER JOIN tab AS t\n ON s.tab_id = t.tab_id\n INNER JOIN subject AS su\n ON su.subject_id = t.subject_id\n WHERE p.body LIKE :search_term\n \t\t\t AND t.subject_id = :subject_id");
$statement->bindParam(":subject_id", $subject_id);
break;
case "current_guide":
$statement = $connection->prepare("SELECT p.pluslet_id as 'id',su.shortform as 'short_form','Pluslet' as 'content_type',p.type as 'type', p.title, p.title AS 'label', ps.section_id, t.tab_index AS 'additional_id', t.subject_id, su.subject FROM pluslet AS p\n INNER JOIN pluslet_section AS ps\n ON ps.pluslet_id = p.pluslet_id\n INNER JOIN section AS s\n ON ps.section_id = s.section_id\n INNER JOIN tab AS t\n ON s.tab_id = t.tab_id\n INNER JOIN subject AS su\n ON su.subject_id = t.subject_id\n WHERE p.title LIKE :search_term\n \t\t\t AND t.subject_id = :subject_id");
$statement->bindParam(":subject_id", $subject_id);
break;
case "records":
$statement = $connection->prepare("SELECT DISTINCT title.title_id as 'id','Record' as 'content_type', title.title as 'label', location.location as 'location_url'\nFROM title\nINNER JOIN location_title\nON title.title_id = location_title.title_id\nINNER JOIN location\nON location.location_id = location_title.location_id\nAND title.title LIKE :search_term");
break;
case "azrecords":
$statement = $connection->prepare("SELECT DISTINCT title.title_id as 'id','Record' as 'content_type', title.title as 'label', location.location as 'location_url'\nFROM title\nINNER JOIN location_title \nON title.title_id = location_title.title_id\nINNER JOIN location\nON location.location_id = location_title.location_id\nAND eres_display = 'Y'\nAND title.title LIKE :search_term");
break;
case "faq":
$statement = $connection->prepare("SELECT faq_id AS 'id',question AS 'label', LEFT(question, 55), \n \t\t'FAQ' as 'content_type' FROM faq WHERE question LIKE :search_term");
break;
case "talkback":
$statement = $connection->prepare("SELECT talkback_id AS 'id',question AS 'label','Talkback' \n \t\tas content_type, LEFT(question, 55) FROM talkback WHERE question LIKE :search_term");
break;
case "admin":
$statement = $connection->prepare("SELECT staff_id AS 'id',email AS 'label','Staff' \n \t\tas 'content_type', CONCAT(fname, ' ', lname, ' (', email, ')') as fullname \n \t\tFROM staff WHERE (fname LIKE :search_term) OR (lname LIKE :search_term)");
break;
case "pluslet":
$statement = $connection->prepare("SELECT p.pluslet_id AS 'pluslet_id', p.title,p.title AS 'label',p.type as 'type', p.pluslet_id AS 'id', su.shortform as 'short_form', 'Pluslet' AS 'content_type', t.tab_index as 'additional_id',su.subject as 'parent' FROM pluslet AS p\n INNER JOIN pluslet_section AS ps\n ON ps.pluslet_id = p.pluslet_id\n INNER JOIN section AS s\n ON ps.section_id = s.section_id\n INNER JOIN tab AS t\n ON s.tab_id = t.tab_id\n INNER JOIN subject AS su\n ON su.subject_id = t.subject_id\n WHERE p.title LIKE :search_term\n \t\t\t\n \t\t\t\t");
break;
case "my_pluslets":
$statement = $connection->prepare("SELECT p.pluslet_id AS 'pluslet_id', p.title,p.title AS 'label',p.type as 'type', p.pluslet_id AS 'id', su.shortform as 'short_form', 'Pluslet' AS 'content_type', t.tab_index as 'additional_id',su.subject as 'parent' FROM pluslet AS p\n INNER JOIN pluslet_section AS ps\n ON ps.pluslet_id = p.pluslet_id\n INNER JOIN section AS s\n ON ps.section_id = s.section_id\n INNER JOIN tab AS t\n ON s.tab_id = t.tab_id\n INNER JOIN subject AS su\n ON su.subject_id = t.subject_id\n INNER JOIN staff_subject AS st_sub\n ON st_sub.subject_id = su.subject_id\n WHERE p.title LIKE :search_term\n AND st_sub.staff_id = :staff_id\n \t\t\t\n \t\t\t\t");
$statement->bindParam(":staff_id", $this->staff_id);
break;
}
$search_param = '%' . $search_param . '%';
$statement->bindParam(":search_term", $search_param);
$statement->execute();
$result = $statement->fetchAll();
$arr = array();
$i = 0;
// This takes the results and creates an array that will be turned into JSON
foreach ($result as $myrow) {
//add no title label if empty
$myrow['label'] = empty($myrow['label']) ? '[no title]' : $myrow['label'];
$arr[$i]['label'] = html_entity_decode($myrow['label']);
if (isset($myrow['content_type'])) {
if (isset($myrow['id'])) {
$arr[$i]['id'] = $myrow['id'];
}
$arr[$i]['content_type'] = $myrow['content_type'];
if (isset($myrow['location_url'])) {
$arr[$i]['location_url'] = $myrow['location_url'];
}
if (isset($myrow['short_form'])) {
$arr[$i]['shortform'] = $myrow['short_form'];
}
if (isset($myrow['matching_text'])) {
$arr[$i]['value'] = $myrow['matching_text'];
}
if (isset($myrow['parent'])) {
$arr[$i]['parent'] = $myrow['parent'];
}
if (isset($myrow['additional_id'])) {
$arr[$i]['parent_id'] = $myrow['additional_id'];
}
switch ($myrow['content_type']) {
case "Record":
$arr[$i]['label'] = html_entity_decode($myrow['label']);
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = 'record.php?record_id=' . $myrow['id'];
} else {
$db = new Querier();
$record_url_sql = "SELECT location, title\n FROM location l, title t, location_title lt \n WHERE t.title_id = lt.title_id\n AND l.location_id = lt.location_id AND t.title_id = " . $db->quote($myrow['id']) . " ";
$record_url_result = $db->query($record_url_sql);
if (isset($record_url_result[0]['location'])) {
$arr[$i]['url'] = $record_url_result[0]['location'];
} else {
$arr[$i]['url'] = '';
}
}
break;
case "Subject Guide":
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = getControlURL() . 'guides/guide.php?subject_id=' . $myrow['id'];
} else {
$arr[$i]['url'] = 'guide.php?subject=' . $myrow['short_form'];
}
break;
case "FAQ":
$arr[$i]['label'] = html_entity_decode($myrow['label']);
$arr[$i]['url'] = 'faq.php?faq_id=' . $myrow['id'];
break;
case "Pluslet":
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = getControlURL() . 'guides/guide.php?subject_id=' . $myrow['short_form'] . '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['hash'] = '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['label'] = html_entity_decode($myrow['label']);
if (isset($myrow['type'])) {
$arr[$i]['type'] = $myrow['type'];
}
if (isset($arr[$i]['pluslet_id'])) {
$arr[$i]['pluslet_id'] = $myrow['id'];
}
} else {
$arr[$i]['url'] = 'guide.php?subject=' . $myrow['short_form'] . '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['hash'] = '#box-' . $myrow['additional_id'] . '-' . $myrow['id'];
$arr[$i]['tab_index'] = $myrow['additional_id'];
$arr[$i]['pluslet_id'] = $myrow['id'];
}
break;
case "Talkback":
$arr[$i]['label'] = html_entity_decode($myrow['label']);
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = 'talkback.php?talkback_id=' . $myrow['id'];
} else {
$arr[$i]['url'] = 'talkback.php';
}
break;
case "Staff":
if ($myrow['fullname'] != null) {
$arr[$i]['label'] = $myrow['fullname'];
} else {
$arr[$i]['label'] = "";
}
if ($this->getSearchPage() == "control") {
$arr[$i]['url'] = 'user.php?staff_id=' . $myrow['id'];
} else {
$name = explode('@', $myrow['label']);
$arr[$i]['url'] = 'staff_details.php?name=' . $name[0];
}
break;
}
} else {
$arr[$i]['value'] = $myrow[0];
}
$i++;
}
$response = json_encode($arr);
return $response;
}
<button type="submit" class="pure-button pure-button-primary">' . _("Submit") . '</button>
</div>
</fieldset>
<br />
<div align="center"><a href="forgotpassword.php">' . _("I have forgotten my password") . '</a></div>
</form>
';
$logo = "<img src=\"{$AssetPath}" . "images/admin/logo_v3_full.png\" border=\"0\" />\n\n<br />";
?>
<!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">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" href="<?php
echo getControlURL();
?>
includes/css.php" type="text/css" media="all" />
<title>Login</title>
</head>
<body id="controlpage">
<div style="margin: 4em auto; width: 350px;">
<?php
makePluslet($logo, $login_form, "");
?>
</div>
</body>
include "../includes/functions.php";
use SubjectsPlus\Control\Querier;
use SubjectsPlus\Control\AzRecord\TitleDb;
use SubjectsPlus\Control\AzRecord\TitleFactory;
use SubjectsPlus\Control\AzRecord\LocationDb;
use SubjectsPlus\Control\AzRecord\LocationTitleDb;
use SubjectsPlus\Control\AzRecord\LocationFactory;
$db = new Querier();
$title_db = new TitleDb($db);
$location_db = new LocationDb($db);
$title_json = file_get_contents('php://input');
$title_array = json_decode($title_json, true);
$title = TitleFactory::create($title_array);
$title_insert_id = $title_db->insertTitle($title);
if (isset($title_insert_id)) {
echo json_encode(array("response" => getControlURL() . '/records/record.php?record_id=' . $title_insert_id, "record_id" => $title_insert_id, "record" => $title_array));
} else {
echo json_encode(array("response" => "error"));
}
$locations = $title_array['locations'];
if (is_array($locations)) {
foreach ($locations as $location) {
$location_instance = LocationFactory::create($location);
$location_insert_id = $location_db->insertLocation($location_instance);
$location_title_db = new LocationTitleDb($location_insert_id, $title_insert_id, $db);
$location_title_db->insertLocationTitle();
}
} else {
$location_instance = LocationFactory::create($locations);
$location_insert_id = $location_db->insertLocation($location_instance);
$location_title_db = new LocationTitleDb($location_insert_id, $title_insert_id, $db);
