363 lines
13 KiB
Plaintext
363 lines
13 KiB
Plaintext
<?php
|
|
|
|
|
|
class CRUD
|
|
{
|
|
|
|
protected $db;
|
|
|
|
function __construct()
|
|
{
|
|
$this->db = DB();
|
|
}
|
|
|
|
function __destruct()
|
|
{
|
|
$this->db = null;
|
|
}
|
|
|
|
|
|
|
|
public function readAuthorityNumbersOfRecords($authType, $search, $id, $sharp)
|
|
{
|
|
$query = "SELECT COUNT(*) as count, Anchor.ID FROM Anchor ";
|
|
$query .= "JOIN Entry ON (Anchor.ID = Entry.ID) WHERE Type = '" .$authType ."' ";
|
|
if (strlen ($search) > 0 && $sharp == false) $query .= " AND LOWER(Entry.CompleteText) LIKE LOWER('" .$search ."%') ";
|
|
if (strlen ($search) > 0 && $sharp == true) $query .= " AND LOWER(Entry.Text) = LOWER('" .$search ."') ";
|
|
if ($id > 0)
|
|
$query .= "AND Entry.ID = " .$id;
|
|
// echo $query ."\n";
|
|
$query = $this->db->prepare($query);
|
|
$query->execute();
|
|
|
|
$num = 0;
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
// print_r($rec);
|
|
$num = $rec['count'] ;
|
|
}
|
|
return $num;
|
|
|
|
}
|
|
|
|
|
|
public function readAuthorityEntriesByID($authType, $id)
|
|
{
|
|
$query = "SELECT Anchor.ID AS ID, Anchor.DetailType AS DetailType, Anchor.Type AS Type, Anchor.Classification AS Classification, Entry.Text as Text, Entry.Comment as Scopenote from Anchor ";
|
|
$query .= "JOIN Entry ON (Anchor.ID = Entry.ID) WHERE Anchor.ID = $id AND Type = '" .$authType ."' ";
|
|
// echo $query ."\n"; exit;
|
|
$query = $this->db->prepare($query);
|
|
$query->execute();
|
|
|
|
$count = $query->rowCount();
|
|
|
|
$ret = array();
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
// print_r($rec);
|
|
$relations = $this->getRelations($authType,$rec['ID']);
|
|
$ret[] = array("ID" => $rec['ID'], "Descriptor" => $relations['Descriptor'], "Text" => $rec['Text'], "DetailType" => $rec['DetailType'], "Type" => $rec['Type'], "Scopenote" => $rec['Scopenote'], "Relations" => $relations['Data'], "Classification" => $rec['Classification']) ;
|
|
}
|
|
|
|
return (array("COUNT" => $count, "RECORDS" => $ret));
|
|
}
|
|
|
|
|
|
public function readAuthorityEntries($authType, $offset, $search, $id, $sort, $max)
|
|
{
|
|
$limit = " limit " .$offset ."," .$max ;
|
|
$sort = " order by " .$sort;
|
|
$query = "SELECT Anchor.ID AS ID, Anchor.DetailType AS DetailType, Anchor.Type AS Type, Anchor.Classification AS Classification, Entry.Text as Text, Entry.Comment as Scopenote from Anchor ";
|
|
$query .= "JOIN Entry ON (Anchor.ID = Entry.ID) WHERE Type = '" .$authType ."' ";
|
|
if (strlen ($search) > 0)
|
|
$query .= "AND LOWER(Entry.CompleteText) LIKE LOWER('" .$search ."%') ";
|
|
if ($id > 0)
|
|
$query .= "AND Entry.ID = " .$id;
|
|
$query .= $sort .$limit;
|
|
// echo $query ."\n"; exit;
|
|
$query = $this->db->prepare($query);
|
|
//$query->bindParam("authType", $authType, PDO::PARAM_STR);
|
|
$query->execute();
|
|
|
|
$count = $query->rowCount();
|
|
|
|
$ret = array();
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
// print_r($rec);
|
|
$relations = $this->getRelations($authType, $rec['ID']);
|
|
if (strcmp($authType,'Subject') == 0) {
|
|
$synonyms = $this->getSynonyms($rec['Text']);
|
|
// print_r($synonyms);
|
|
$ret[] = array("ID" => $rec['ID'], "Descriptor" => $relations['Descriptor'], "Text" => $rec['Text'], "DetailType" => $rec['DetailType'], "Type" => $rec['Type'], "Scopenote" => $rec['Scopenote'], "Relations" => $relations['Data'], "Classification" => $rec['Classification'], "Synonyms" => $synonyms['Synonyms'], "Search" => $synonyms['Search']) ;
|
|
} else {
|
|
$ret[] = array("ID" => $rec['ID'], "Descriptor" => $relations['Descriptor'], "Text" => $rec['Text'], "DetailType" => $rec['DetailType'], "Type" => $rec['Type'], "Scopenote" => $rec['Scopenote'], "Relations" => $relations['Data'], "Classification" => $rec['Classification']) ;
|
|
}
|
|
}
|
|
|
|
return (array("COUNT" => $count, "RECORDS" => $ret));
|
|
}
|
|
|
|
|
|
public function getRelations($authType,$id)
|
|
{
|
|
// $query = "SELECT Linking.ID as LinkingID, IDEntry, Entry.Text, Entry.Comment, Relationtype, DetailType FROM Linking JOIN Entry ON (Linking.IDEntry = Entry.ID) WHERE IDAnchor=" .$id ." AND Relationtype != '' order by Relationtype";
|
|
$query = "SELECT Linking.ID as LinkingID, IDEntry, Entry.Text, Entry.Comment, Relationtype, DetailType FROM Linking JOIN Entry ON (Linking.IDEntry = Entry.ID) join Anchor on (IDAnchor = Anchor.ID) WHERE IDAnchor=" .$id ." AND Anchor.Type='" .$authType ."' AND Relationtype != '' order by Relationtype";
|
|
$query = $this->db->prepare($query);
|
|
$query->execute();
|
|
|
|
$descriptor = true;
|
|
|
|
$ret = array();
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
// print_r($rec);
|
|
if (strcmp(strtolower($rec['Relationtype']), "use") == 0) $descriptor = false;
|
|
$ret[] = array("IDLinking" => $rec['LinkingID'], "IDEntry" => $rec['IDEntry'], "IDRelation" => $rec['IDEntry'], "Detailtype" => $rec['DetailType'], "TextRelation" => $rec['Text'], "CommentRelation" => $rec['Comment'], "Relationtype" => $rec['Relationtype']);
|
|
}
|
|
|
|
return array("Descriptor" => $descriptor, "Data" => $ret) ;
|
|
}
|
|
|
|
|
|
public function getSynonyms($input)
|
|
{
|
|
$desc = $this->prepare_desc($input);
|
|
$query = "SELECT IDLinking from Synonyms where Descriptor = '" .$desc ."'";
|
|
$query = $this->db->prepare($query);
|
|
$query->execute();
|
|
|
|
$ret = '' ;
|
|
$retsearch = "topic:'" .$input ."' OR ";
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
// print_r($rec);
|
|
$idSearch = $rec['IDLinking'] ;
|
|
$query = "SELECT Text from Synonyms where IDLinking = " .$idSearch ;
|
|
$query = $this->db->prepare($query);
|
|
$query->execute();
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
if (strcmp($rec['Text'], $input) == 0) continue;
|
|
$ret .= $rec['Text'] .", " ;
|
|
$retsearch .= "topic:'" .$rec['Text'] ."'" ." OR ";
|
|
}
|
|
}
|
|
if(strlen($ret) == 0) {
|
|
return array("Synonyms" => '', "Search" => '');
|
|
} else {
|
|
$synonyms = trim(substr($ret, 0, strlen($ret) - strlen(", ")));
|
|
$search = trim(substr($retsearch, 0, strlen($retsearch) - strlen(" OR ")));
|
|
return (array("Synonyms" => $synonyms, "Search" => $search)) ;
|
|
}
|
|
}
|
|
|
|
|
|
|
|
|
|
public function getEntryText($authType,$Request)
|
|
{
|
|
$query = "SELECT Anchor.ID AS AnchorID, Entry.Text as Text FROM Anchor JOIN Entry on (Anchor.ID = Entry.ID) WHERE Anchor.Text LIKE LOWER('%" .$Request ."%') AND Anchor.Type='" .$authType ."' order by Entry.Text";
|
|
$query = $this->db->prepare($query);
|
|
$query->execute();
|
|
|
|
$ret = array();
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
// print_r($rec);
|
|
$ret[] = array("Text" => $rec['Text'], "ID" => $rec['AnchorID']);
|
|
}
|
|
|
|
return($ret);
|
|
}
|
|
|
|
public function insertNewTerm($authType, $term, $desc, $detailtype, $classification, $scopenote, $completetext)
|
|
{
|
|
|
|
$query = $this->db->prepare("INSERT INTO Anchor(ID, Text, DetailType, Type, Classification) VALUES (:ID, :Text, :DetailType, :Type, :Classification)");
|
|
|
|
$id = NULL;
|
|
|
|
$query->bindParam("ID", $id, PDO::PARAM_INT);
|
|
$query->bindParam("Text", $desc, PDO::PARAM_STR);
|
|
$query->bindParam("DetailType", $detailtype, PDO::PARAM_STR);
|
|
$query->bindParam("Type", $authType, PDO::PARAM_STR);
|
|
$query->bindParam("Classification", $classification, PDO::PARAM_STR);
|
|
$query->execute();
|
|
|
|
$IDAnchor = $this->db->lastInsertId();
|
|
|
|
$query = $this->db->prepare("INSERT INTO Entry(ID, Text, Comment, Language, CompleteText, DateCreated, DateModified) VALUES (:ID, :Text, :Comment, :Language, :CompleteText, :DateCreated,:DateModified)");
|
|
|
|
$id = NULL;
|
|
$language = "de";
|
|
|
|
$query->bindParam("ID", $id, PDO::PARAM_INT);
|
|
$query->bindParam("Text", $term, PDO::PARAM_STR);
|
|
$query->bindParam("Comment", $scopenote, PDO::PARAM_STR);
|
|
$query->bindParam("Language", $language, PDO::PARAM_STR);
|
|
$query->bindParam("CompleteText", $completetext, PDO::PARAM_STR);
|
|
$date = date('Y-m-d H:i:s');
|
|
$query->bindParam("DateCreated", $date, PDO::PARAM_STR);
|
|
$date = '0000-00-00 00:00:00' ;
|
|
$query->bindParam("DateModified",$date, PDO::PARAM_STR);
|
|
|
|
$query->execute();
|
|
|
|
$IDEntry = $this->db->lastInsertId();
|
|
|
|
$query = $this->db->prepare("INSERT INTO Linking(ID, IDAnchor, IDEntry, Relationtype) VALUES (:ID, :IDAnchor, :IDEntry, :Relationtype)");
|
|
|
|
$id = NULL;
|
|
$relationtype = "";
|
|
|
|
$query->bindParam("ID", $id, PDO::PARAM_INT);
|
|
$query->bindParam("IDAnchor", $IDAnchor, PDO::PARAM_INT);
|
|
$query->bindParam("IDEntry", $IDEntry, PDO::PARAM_INT);
|
|
$query->bindParam("Relationtype", $relationtype, PDO::PARAM_STR);
|
|
$query->execute();
|
|
|
|
$IDLinking = $this->db->lastInsertId();
|
|
|
|
return(array("IDAnchor" => $IDAnchor, "IDEntry" => $IDEntry, "IDLinking" => $IDLinking));
|
|
|
|
}
|
|
|
|
|
|
public function writeNewRelation($anchorID, $relationType, $relationID)
|
|
{
|
|
$query = $this->db->prepare("INSERT INTO Linking (ID, IDAnchor, IDEntry, Relationtype) VALUES (:ID, :IDAnchor, :IDEntry, :Relationtype)");
|
|
|
|
$id = NULL;
|
|
|
|
$query->bindParam("ID", $id, PDO::PARAM_INT);
|
|
$query->bindParam("IDAnchor", $anchorID, PDO::PARAM_STR);
|
|
$query->bindParam("IDEntry", $relationID, PDO::PARAM_STR);
|
|
$query->bindParam("Relationtype", $relationType, PDO::PARAM_STR);
|
|
$query->execute();
|
|
|
|
$IDLinking = $this->db->lastInsertId();
|
|
|
|
return ($IDLinking);
|
|
|
|
}
|
|
|
|
public function deleteRelation($AnchorID, $LinkingID)
|
|
{
|
|
// $query = "DELETE FROM Linking where ID = " .$LinkingID;
|
|
// echo $query ."\n";
|
|
$query = $this->db->prepare("DELETE FROM Linking where ID = " .$LinkingID);
|
|
|
|
|
|
// $query->bindParam("ID", $LinkingID, PDO::PARAM_INT);
|
|
$query->execute();
|
|
|
|
return ($LinkingID);
|
|
|
|
}
|
|
|
|
|
|
public function deleteTerm($AnchorID)
|
|
{
|
|
$ret = array();
|
|
$ret[] = 'DELETE FROM Linking where IDAnchor = ' .$AnchorID;
|
|
$query = $this->db->prepare("DELETE FROM Linking where IDAnchor = " .$AnchorID);
|
|
$query->execute();
|
|
|
|
$ret[] = $this->db->errorInfo();
|
|
|
|
$ret[] = 'DELETE FROM Entry where ID = ' .$AnchorID;
|
|
$query = $this->db->prepare("DELETE FROM Entry where ID = " .$AnchorID);
|
|
$query->execute();
|
|
$ret[] = $this->db->errorInfo();
|
|
|
|
$ret[] = 'DELETE FROM Anchor where ID = ' .$AnchorID;
|
|
$query = $this->db->prepare("DELETE FROM Anchor where ID = " .$AnchorID);
|
|
$query->execute();
|
|
$ret[] = $this->db->errorInfo();
|
|
|
|
return($ret);
|
|
}
|
|
|
|
|
|
public function updateTerm($AnchorID, $authType, $desc, $term, $type, $detailtype, $classification, $scopenote, $completetext)
|
|
{
|
|
$r = array();
|
|
$q = "UPDATE Anchor set Text = '" .$desc ."', Type ='" .$authType ."' , DetailType = '" .$detailtype ."' , Classification = '" .$classification ."' where ID = " .$AnchorID;
|
|
$r[] = $q;
|
|
|
|
$query = $this->db->prepare($q);
|
|
|
|
/* $query->bindParam("ID", $AnchorID, PDO::PARAM_INT);
|
|
$query->bindParam("Text", $term, PDO::PARAM_STR);
|
|
$query->bindParam("Type", $Type, PDO::PARAM_STR);
|
|
$query->bindParam("DetailType", $DetailType, PDO::PARAM_STR);
|
|
$query->bindParam("Classification", $Classification, PDO::PARAM_STR);
|
|
*/
|
|
$query->execute();
|
|
$r[] = $this->db->errorCode();
|
|
$r[] = $this->db->errorInfo();
|
|
|
|
$q = "UPDATE Entry set Text = :Text, Comment = :Comment, Language = :Language, CompleteText = :CompleteText, DateModified = :DateModified where ID = :ID";
|
|
$r[] = $q;
|
|
|
|
$query = $this->db->prepare($q);
|
|
|
|
$Language = "de";
|
|
|
|
$query->bindParam("ID", $AnchorID, PDO::PARAM_INT);
|
|
$query->bindParam("Text", $term, PDO::PARAM_STR);
|
|
$query->bindParam("Language", $Language, PDO::PARAM_STR);
|
|
$query->bindParam("Comment", $scopenote, PDO::PARAM_STR);
|
|
$query->bindParam("CompleteText", $completetext, PDO::PARAM_STR);
|
|
$date = date('Y-m-d H:i:s');
|
|
$query->bindParam("DateModified",($date), PDO::PARAM_STR);
|
|
|
|
|
|
$r[] = $q;
|
|
|
|
$query->execute();
|
|
|
|
$r[] = $this->db->errorCode();
|
|
$r[] = $this->db->errorInfo();
|
|
|
|
return("OK");
|
|
}
|
|
|
|
public function getStatistics($dateStart, $dateEnd)
|
|
{
|
|
$r = array();
|
|
|
|
$Types = array("Subject", "Person", "Corporate", "Publisher", "Classification");
|
|
$DateTypes = array("DateCreated", "DateModified");
|
|
|
|
foreach($Types as $Type) {
|
|
foreach ($DateTypes as $DateType) {
|
|
$q = "SELECT COUNT(*) AS COUNT FROM Anchor JOIN Entry ON (Anchor.ID = Entry.ID) ";
|
|
$q.= "WHERE Type = '" .$Type ."' AND " .$DateType ." BETWEEN '" .$dateStart ."' AND '" .$dateEnd ."'" ;
|
|
$query = $this->db->prepare($q);
|
|
$query->execute();
|
|
|
|
while($rec = ($query->fetch(PDO::FETCH_ASSOC))) {
|
|
// print_r($rec);
|
|
$r[$Type][$DateType] = $rec['COUNT'];
|
|
}
|
|
}
|
|
}
|
|
return ($r);
|
|
}
|
|
|
|
|
|
|
|
|
|
|
|
public function prepare_desc($text) {
|
|
|
|
$desc = is_array($text)? $text[0] : $text;
|
|
$text = strtolower ($desc);
|
|
$desc = str_replace(' ', '_', $text) ;
|
|
$search = array('ü', 'ä', 'ö', 'ß', '.', ',', 'Ö', 'Ü', 'Ä', '[', ']', '<' , '>' , '""');
|
|
$replace = array('ue','ae','oe','ss', '', '', 'oe',"ue", 'ae', '_', '_', '<', '>', '"' );
|
|
$desc = str_replace($search, $replace, $desc);
|
|
|
|
return ($desc);
|
|
}
|
|
}
|
|
|
|
?>
|
|
|
|
|