<?php
include ("../config/head.php");
include ("../auth.php");
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>DNA Module - Publications</title>
<link rel="stylesheet" type="text/css" href="../format.css">
<link rel="stylesheet" type="text/css" href="../input/XSLT/Styles.css">
<SCRIPT LANGUAGE="JavaScript1.1" src="../Functions.js"></SCRIPT>
<NOSCRIPT>Please enable Javascript!</NOSCRIPT>
</head>
<body>
<div align="center">
<?php include("../tablenavi.php"); ?>
<form name="formular" method="POST" action="search.php">
<table id="tablemain" class="search">
<tr>
<td id="tdLogo"><img border="0" src="../images/Logo-DNA-Bank-Network-ocker.jpg" width="123" height="78"></td>
<td>
<h1>Publications - Search</h1></td>
<td id="tdLogo2"><?php echo $Path_Logo; ?></td>
</tr>
<tr>
<td colspan="3"><?php
echo "Logged in as: ";
$log = $_SESSION["log"];
echo $log;
?></td>.</td>
</tr>
<tr>
<td valign="top" align="center" valign="middle" colspan="3">
<table id="table2">
<?php
if($formSubmitSave)
{
$result0 = "UPDATE publications SET Editors = '$formEds', " .
"Year = '$formYear', " .
"Title = '$formTitle', " .
"FK_Journal = '$formJournal', " .
"Volume = '$formVolume', " .
"Pages = '$formPages', " .
"Book = '$formBook', " .
"Publisher = '$formPublisher', " .
"Link = '$formLink', " .
"Update_Who = '$log' " .
"WHERE ID_Publications = '$formPublication'";
$sql0 = mysql_query($result0);
#######################################Error 5###############################################################
if(!$sql0)
{ $msg = $result0."\n";
$msg .= "####Error 5####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
$result1 = "SELECT ID_Journal, Journal_Display FROM journals WHERE ID_Journal = '$formJournal'";
$sql1 = mysql_query($result1);
while($row = mysql_fetch_object($sql1))
{$Journal = $row->Journal_Display; }
#######################################Error 5a###############################################################
if(!$sql1)
{ $msg = $result1."\n";
$msg .= "####Error 5a####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
$autcount=0;
$AuthorArray = explode(";", $formAuthor);
while($i = each($AuthorArray))
{
$Author = trim($i[1]);
// ist der Autor bereits bekannt?
if($Author)
{
$resulta = "SELECT ID_Author FROM authors " .
"WHERE Author = '$Author'";
$sqla = mysql_query($resulta);
while($row = mysql_fetch_object($sqla))
{$ID_Author_Test = $row->ID_Author; }
#######################################Error Author-A###############################################################
if(!$sqla)
{ $msg = $resulta."\n";
$msg .= "####Error Author-A####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
if(mysql_num_rows($sqla))
{$IDAuthor[$autcount++] = mysql_result($sqla, 0, 0);}
// wenn unbekannt, insert
else
{
$resultb = "INSERT INTO authors (Author, Created_Who) " .
"VALUES('$Author','$log')";
$sqlb = mysql_query($resultb);
$IDAuthor[$autcount++]=mysql_insert_id();
#######################################Error Author-B###############################################################
if(!$sqlb)
{ $msg = $resultb."\n";
$msg .= "####Error Author-B####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
}
}
}// Ende der Autorenschleife
$resultc = "DELETE FROM publications_authors WHERE FK_Publications = '$formPublication'";
$sqlc = mysql_query($resultc);
#######################################Error Author-C###############################################################
if(!$sqlc)
{ $msg = $resultc."\n";
$msg .= "####Error c####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
if($sqlc)
{
#############################################################################################################
for($i=0; $i<$autcount; $i++)
{
if($i!=0) $sql .= ",";
$sql .= "($formPublication, $IDAuthor[$i])";
$resultd = "INSERT INTO publications_authors (FK_Publications, FK_Author, Created_Who) " .
"VALUES ('$formPublication','$IDAuthor[$i]','$log')";
$sqld = mysql_query($resultd);
#######################################Error Author-D###############################################################
if(!$sqld)
{ $msg = $resultd."\n";
$msg .= "####Error d####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
}
}
if($sql0)
{
$result2 = "SELECT authors.ID_Author, " .
"authors.Author AS Author1 " .
"FROM authors, publications_authors " .
"WHERE authors.ID_Author = publications_authors.FK_Author " .
" AND publications_authors.FK_Publications = '$formPublication'";
$sql2 = mysql_query($result2);
#######################################Error 7###############################################################
if(!$sql2)
{ $msg = $result2."\n";
$msg .= "####Error 7####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
while($row = mysql_fetch_object($sql2))
{
if(isset($Author1[$row->ID_Publication]))
{$Author1[$row->ID_Publications] .= ", "; }
else {$Author1[$row->IDPublications] = ""; }
$tmp = $row->Author1;
$Author1[$row->ID_Publications] .= $tmp;
}
$Paper = $Author1[$row->ID_Publications];
if(isset($formEds)) { $Paper .= " ".$formEds; }
$Paper .= " (".$formYear."): ".$formTitle;
if($formJournal) { $Paper .= " ".$Journal; }
if(!$formJournal) { $Paper .= " IN: ".$formBook." ".$formPublisher; }
if($formVolume) { $Paper .= " ".$formVolume.": "; }
if($formPages) { $Paper .= " ".$formPages."."; }
$result3 = "UPDATE publications_amplifications SET Paper_Cache = '$Paper' WHERE FK_Publications = '$formPublication'";
$sql3 = mysql_query($result3);
#######################################Error 8###############################################################
if(!$sql3)
{ $msg = $result3."\n";
$msg .= "####Error 8####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
if($sql2 and $sql3)
{echo "<span class='fine'>Data have been saved!</span>"; }
}
}
?>
<tr><td id="DescriptionGreen">Author</td><td>
<?php
build_select_list("formAuthor",
"SELECT ID_Author, Author FROM authors " .
"ORDER BY Author", $_POST['formAuthor']);
?>
</td><td id="DescriptionGreen">or show all datasets</td><td><input type="checkbox" id="checkbox" name="formShowAll" checked></td></tr>
<tr><td id="DescriptionGreen">Year</td><td colspan="3"><input type="text" name="formSearchYear" id="InputLong" value="<?php echo $_POST['formSearchYear']; ?>"/></td></tr>
<tr><td id="DescriptionGreen">Title</td><td colspan="3"><input type="text" name="formSearchTitle" id="InputLong" value="<?php echo $_POST['formSearchTitle']; ?>"/></td></tr>
<tr><td id="DescriptionGreen">Journal</td><td colspan="3">
<?php
build_select_list("formJournal",
"SELECT Journal_Display, Journal_Display FROM journals " .
"ORDER BY Journal_Display", $_POST['formJournal']);
?>
</td></tr>
<tr><td id="DescriptionGreen">Book</td><td colspan="3"><input type="text" name="formSearchBook" id="InputLong" value="<?php echo $_POST['formSearchBook']; ?>"/></td></tr>
<tr><td id="DescriptionGreen">Publisher</td><td colspan="3"><input type="text" name="formSearchPublisher" id="InputLong" value="<?php echo $_POST['formSearchPublisher']; ?>"/></td></tr>
<tr><td id="DescriptionGreen">Online version available</td><td colspan="3"><input type="checkbox" id="checkbox" name="formOnline"/></td></tr>
<tr><td></td><td colspan="3"><input type="submit" class="button" name="formSubmitSearch" value="Search"></td></tr>
<?php
if($_GET['deleteauthor'] == 'true')
{ $resultdeletea = "DELETE FROM authors WHERE ID_Author NOT IN (SELECT FK_Author FROM publications_authors)";
$sqldeletea = mysql_query($resultdeletea);
if($sqldeletea) { echo "<span class='fine'>Authors have been deleted!</span>"; } }
if($_GET['deletejournal'] == 'true')
{ $resultdeleteb = "DELETE FROM journals WHERE ID_Journal NOT IN (SELECT FK_Journal FROM publications)";
$sqldeleteb = mysql_query($resultdeleteb);
if($sqldeleteb) { echo "<span class='fine'>Journals have been deleted!</span>"; } }
$resulttesta = "SELECT DISTINCT ID_Author, Author FROM authors WHERE ID_Author NOT IN (SELECT FK_Author FROM publications_authors)";
$sqltesta = mysql_query($resulttesta);
$resulttestb = "SELECT DISTINCT ID_Journal, Journal_Display FROM journals WHERE ID_Journal NOT IN (SELECT FK_Journal FROM publications)";
$sqltestb = mysql_query($resulttestb);
if(mysql_num_rows($sqltesta))
{echo "<tr><td colspan='4'>Note: For the following authors don't exist publications: ";
while($row = mysql_fetch_object($sqltesta))
{ echo "<b>".$row->Author. "; </b>"; }
echo "Do you want to delete this authors from database? <a href='search.php?deleteauthor=true'>Yes</a></td></tr>"; }
if(mysql_num_rows($sqltestb))
{echo "<tr><td colspan='4'>Note: For the following journals don't exist publications: ";
while($row = mysql_fetch_object($sqltestb))
{ echo "<b>".$row->Journal_Display. "; </b>"; }
echo "Do you want to delete this journals from database? <a href='search.php?deletejournal=true'>Yes</a></td></tr>"; }
?>
<tr><td colspan="4">
<?php
if($formSubmitEditDataset and !$formSubmitSave)
{
if($_POST['x']=="")
{ unset($x);
echo "<span class='error'>Please select a publication!</span>"; }
else {
if(isset($_POST['x']))
{ $mykey = key($_POST['formPublications']);
if($mykey = $_POST['x'])
{
$IDPublication = $_POST['formPublications'][$mykey];
} } //if($_POST['x']!="")
$result = "SELECT ID_Pub_Amp FROM publications_amplifications WHERE FK_Publications = '$IDPublication'";
$sql = mysql_query($result);
#######################################Error 1###############################################################
if(!$sql)
{ $msg = $result."\n";
$msg .= "####Error 1####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
$number = mysql_num_rows($sql);
if($number >= 1 and $formAction == '1')
{ echo "<span class='error'>Publikation kann nicht gelöscht werden, da ".$number." Amplifikationen mit ihr verknüpft sind!"; }
if($number == 0 and $formAction == '1')
{ $result = "DELETE FROM publications WHERE FK_Publications = '$IDPublication'";
$sql = mysql_query($result);
#######################################Error 2###############################################################
if(!$sql)
{ $msg = $result."\n";
$msg .= "####Error 2####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
else { echo "<span class='fine'>Publikation wurde gelöscht!</span>"; }
}
if($formAction == '2')
{
$result1 = "SELECT publications.ID_Publications, " .
"publications.Editors, " .
"publications.Year, " .
"publications.Title, " .
"journals.Journal_Display, " .
"publications.Volume, " .
"publications.Pages, " .
"publications.Book, " .
"publications.Publisher, " .
"publications.Link " .
"FROM publications
LEFT JOIN journals ON publications.FK_Journal = journals.ID_Journal " .
"WHERE publications.ID_Publications = '$IDPublication'";
$sql1 = mysql_query($result1);
#######################################Error 3###############################################################
if(!$sql1)
{ $msg = $result1."\n";
$msg .= "####Error 3####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
while($row = mysql_fetch_object($sql1))
{ $Editors = $row->Editors;
$Year = $row->Year;
$Title = $row->Title;
$JournalView = $row->Journal_Display;
$Volume = $row->Volume;
$Pages = $row->Pages;
$Book = $row->Book;
$Publisher = $row->Publisher;
$Link = $row->Link; }
$result2 = "SELECT authors.ID_Author, " .
"authors.Author " .
"FROM authors, publications_authors " .
"WHERE authors.ID_Author = publications_authors.FK_Author " .
" AND publications_authors.FK_Publications = '$IDPublication'";
$sql2 = mysql_query($result2);
#######################################Error 4###############################################################
if(!$sql2)
{ $msg = $result2."\n";
$msg .= "####Error 4####";
if($debug == '1') echo $msg;
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
while($row = mysql_fetch_object($sql2))
{
if(isset($Author[$row->ID_Publication]))
{$Author[$row->ID_Publications] .= ";"; }
else {$Author[$row->IDPublications] = ""; }
$tmp = $row->Author;
$Author[$row->ID_Publications] .= $tmp;
}
echo "<table><tr><td colspan='4'><hr/></td></tr><tr><td>Author(s):<br>Please separate with semicolon (e.g. Smith, J.;Johnson, C.)</td><td>(eds.) Year</td><td>Title:</td></tr>";
echo "<tr><td><input type='hidden' name='formPublication' value='".$IDPublication."'><input type='text' name='formAuthor' id='InputVeryLong2' value='".$Author[$row->ID_Publications]."'/></td><td><input type='checkbox' name='formEds' id='checkbox' value='(eds.)'";
if($Editors == "(eds.)")
{ echo " checked"; }
echo "/><input type='text' name='formYear' id='InputYear2' value='".$Year."'/></td><td><input type='text' name='formTitle' id='InputVeryLong2' value='".$Title."'/></td></tr>";
echo "<tr><td>Journal: <a href='new_journal.php' target='_blank'>new</a></td><td>Volume (Issue) / Chapter:</td><td>Pages:</td></tr>";
echo "<tr><td>";
build_select_list("formJournal",
"SELECT Journal_Display, Journal_Display FROM journals " .
"ORDER BY Journal_Display", $_POST['formJournal']);
echo "</td><td><input type='text' name='formVolume' id='InputLong' value='".$Volume."'/></td><td><input type='text' name='formPages' id='InputLong' value='".$Pages."'/></td></tr>";
echo "<tr><td colspan='4'>Book: IN:</td><td>Publisher(s) (Book only):</td></tr>";
echo "<tr><td colspan='2'><input type='text' name='formBook' id='InputVeryLong2' value='".$Book."'/></td><td colspan='2'><input type='text' nmame='formPublisher' id='InputLong' value='".$Publisher."'/></td></tr>";
echo "<tr><td colspan='4'>Link to online version: <input type='text' name='formLink' id='InputVeryLong2' value='".$Link."'/></td></tr>";
if($number >= 1)
{ echo "<tr><td colspan='4'><span class='error'>Edits will concern ".$number." Amplifications! Save Edits anyway?</span></td></tr>"; }
if($number == 0)
{ echo "<tr><td colspan='4'><span class='fine'>Edits will concern no Amplifications!!</span></td></tr>"; }
echo "<tr><td colspan='4'><input type='submit' id='button' name='formSubmitSave' value='Save Edits'></td></tr>";
echo "<tr><td colspan='4'><hr/></td></tr></table>";
}
}
}
if($formSubmitSearch)
{
$result = "";
$sqlcreate = "CREATE TEMPORARY TABLE tmpPublicationIDs AS ";
include("date.php");
$tr = "=";
$no = "!=";
$lk = "like";
$bt = ">=";
$st = "<=";
$parr = $_POST;
array($parr);
foreach ($parr as $var)
{
$i ++;
if(!empty($var) and $var!="Search" and $var!="formShowAll")
{
switch (key($parr))
{
case 'formAuthor':
$str = "authors.ID_Author ".$tr."'".$var."' AND ";
$description = " Author = ";
$value = $formAuthor;
break;
case 'formSearchYear':
$str = "publications.Year ".$lk."'%".$var."%' AND ";
$description = " Year = ";
$value = $formSearchYear;
break;
case 'formSearchTitle':
$str = "publications.Title ".$lk."'%".$var."%' AND ";
$description = " Title = ";
$value = $formSearchTitle;
break;
case 'formJournal':
$str = "(journals.Journal_All ".$lk."'%".$var."%' OR ";
$str .= "journals.Journal_Display ".$lk."'%".$var."%') AND ";
$description = " Journal = ";
$value = $formJournal;
break;
case 'formSearchBook':
$str = "publications.Book ".$lk."'%".$var."%' AND ";
$description = " Book = ";
$value = $formSearchBook;
break;
case 'formSearchPublisher':
$str = "publications.Publisher ".$lk."'%".$var."%' AND ";
$description = " Publisher = ";
$value = $formSearchPublisher;
break;
case 'formOnline':
$str = "publications.Link ".$no."'' AND ";
$description = " Online version = ";
$value = "available";
break;
}
$where[$i] = $str;
$descriptionall[$i] = $description;
$valueall[$i] = $value;
}
next($parr);
}
if(empty($str) and empty($formShowAll))
{ echo "<table border='0' id='tablesearch'><tr><td><b>No search key entered.</b></td></tr></table>"; }
if(!empty($str) and !empty($formShowAll))
{ echo "<table border='0' id='tablesearch'><tr><td><b>Please either enter a search key or select the checkbox!</b></td></tr></table>"; }
else {
foreach($where AS $a) $string .= $a;
$l=strlen ($string);
$max = $l-4;
$where_str = substr($string, 0, $max);
$wherestr = ' WHERE ' . $where_str;
$result = $sqlcreate .
$result = "SELECT DISTINCT publications.ID_Publications " .
"FROM publications
LEFT JOIN journals ON publications.FK_Journal = journals.ID_Journal
LEFT JOIN publications_authors ON publications.ID_Publications = publications_authors.FK_Publications
LEFT JOIN authors ON publications_authors.FK_Author = authors.ID_Author";
if(!empty($str)) {$result .= $wherestr; }
$sql = mysql_query($result) or die($result.'<br>'.mysql_error());
if(!empty($result))
{
// just in case it is still here: drop tmpPublicationIDs
mysql_query("DROP TABLE IF EXISTS tmpPublicationIDs");
mysql_query($result);
if(!mysql_affected_rows())
{echo "<span class='error'>No hits found.</span>"; }
else {
$result1 = "SELECT publications.ID_Publications, " .
"publications.Year, " .
"publications.Title, " .
"publications.Volume, " .
"publications.Pages, " .
"publications.Book, " .
"publications.Publisher, " .
"publications.Link, " .
"publications.Created_Who, " .
"publications.Created_When, " .
"journals.Journal_Display, " .
"journals.Journal_All " .
"FROM publications JOIN tmpPublicationIDs
LEFT JOIN journals ON publications.FK_Journal = journals.ID_Journal " .
"WHERE publications.ID_Publications = tmpPublicationIDs.ID_Publications";
$sql1 = mysql_query($result1);
echo "<table width='100%' border='0'><th>Select</th><th>Author(s)</th><th>Year</th><th>Title</th><th>Journal</th><th>Volume</th><th>Pages </th><th>Book</th><th>Publisher</th><th>Online</th><th>Sequences</th>";
$gesamt = mysql_num_rows($sql1);
for ($i=1; $i<=$gesamt; $i++) {
$row=mysql_fetch_array($sql1);
$publications[] = $row['ID_Publications'];
foreach ($publications as $Test)
{
$result2 = "SELECT publications_authors.FK_Publications, " .
"authors.Author, " .
"publications_authors.Order " .
"FROM publications_authors, authors " .
"WHERE publications_authors.FK_Author = authors.ID_Author " .
" AND publications_authors.FK_Publications = '$Test' " .
"ORDER BY publications_authors.Order";
$sql2 = mysql_query($result2);
while ($roww = mysql_fetch_object($sql2))
{
if(isset($Author[$roww->FK_Publications]))
{$Author[$roww->FK_Publications] .= ", ";}
else
{$Author[$roww->FK_Publications] .= ""; }
$tmp = $roww->Author;
$Author[$roww->FK_Publications] .= $tmp;
$View = $Author[$roww->FK_Publications];
}
$result3 = "SELECT publications_amplifications.ID_Pub_Amp " .
"FROM publications_amplifications " .
"WHERE publications_amplifications.FK_Publications = '$Test'";
$sql3 = mysql_query($result3);
$Relations = mysql_num_rows($sql3);
}
echo "<tr><td align='centern'><input type='radio' name='x' value='".$i."'></td><td>".$View."<input type='hidden' name='formPublications[".$i."]' value='".$row['FK_Publications']."'></td><td align='center'>".$row['Year']."</td><td align='center'>".$row['Title']."</td><td align='center'>".$row['Journal_Display']."</td><td align='center'>".$row['Volume']."</td><td align='center'>".$row['Pages']."</td><td align='center'>".$row['Book']."</td><td align='center'>".$row['Publisher']."</td><td align='center'>";
if($row['Link']) { echo build_href1($row['Link'],"Link"); }
echo "</td><td align='center'>".$Relations."</td></tr>"; }
echo "</table></td></tr>";
echo "<tr><td colspan='3'><hr /></td></tr>";
echo "<tr><td id='DescriptionBlack'>Action:</td><td colspan='2'><select name='formAction' id='InputLong'><option value=''>-- Please select --</option><option value='1' ";
if($_POST['formAction']=='1') { echo "selected"; }
echo ">Delete publication</option><option value='2' ";
if($_POST['formAction']=='2') { echo "selected"; }
echo ">Edit publication</option></select><input type='submit' id='button' name='formSubmitEditDataset' value='Action'></td></tr>";
echo "<tr><td colspan='3'><hr /></td></tr>";
}
}
}
}
?>
</td></tr>
</table>
</td>
</tr>
<tr><td colspan="4"><input type="button" value="Return to Menu" name="formSubmitMenu" class="button" style="float:right" onClick="self.location.href = 'Index-Publications.php';"></td></tr>
</table>
</form>
<?php
include("../footer.php");
?>
</div>
</body>
</html>