<!-- Copyright (C) 2009 DNA Bank Network
http://www.dnabank-network.org
The contents of this file are subject to the Mozilla Public License Version 1.1
See LICENSE.TXT at the top of this package for the full license terms. -->
<?php
include ("head.php");
include ("auth.php");
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>DNA Bank Network - Publications Administration</title>
<link rel="stylesheet" type="text/css" href="format.css">
<SCRIPT LANGUAGE="JavaScript1.1" src="Functions.js"></SCRIPT>
<NOSCRIPT>Bitte aktivieren Sie Javascript!</NOSCRIPT>
</head>
<body>
<form name="formular" method="POST" action="Publications_Edit_self.php">
<table id="tablemain" border="0">
<tr>
<td id="tdLogo"><img border="0" src="images/Logo-DNA-Bank-Network-ocker.jpg" width="123" height="78"></td>
<td>
<h1>DNA Bank Network - Publications Administration</h1></td>
<td id="tdLogo"><img border="0" src="images/Logo.jpg"></td>
</tr>
<tr>
<td><?php
echo "Logged in as: ";
$log = $_SESSION["log"];
echo $log;
?></td><td colspan="2"><a href="logout.php">Logout</a>.</td>
</tr>
<tr>
<td valign="top" align="center" valign="middle" colspan="3">
<table id="table" width="98%" border="0" style="margin:30 0 0 0">
<tr><td align="center" colspan="2"><a class="black" href="Help-Publications-Edit.php" target="_blank">>>> Help <<<</a><br><br></td></tr>
<tr>
<td align="center" colspan="2">
<table border="0">
<tr><td colspan="3">Mit dem Formular können Sie die Publikationen verwalten.</td></tr>
<?php
if($formSubmitSave)
{
$result0 = "UPDATE tablepublications SET Editors = '$formEds', " .
"Year = '$formYear', " .
"Title = '$formTitle', " .
"ID_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####";
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
$result1 = "SELECT ID_Journal, Journal_View FROM tablejournals WHERE ID_Journal = '$formJournal'";
$sql1 = mysql_query($result1);
while($row = mysql_fetch_object($sql1))
{$Journal = $row->Journal_View; }
#######################################Error 5a###############################################################
if(!$sql1)
{ $msg = $result1."\n";
$msg .= "####Error 5a####";
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 tableauthors " .
"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####";
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 tableauthors (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####";
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
}
}
}// Ende der Autorenschleife
$resultc = "DELETE FROM tablepublications_authors WHERE ID_Publications = '$formPublication'";
$sqlc = mysql_query($resultc);
#######################################Error Author-C###############################################################
if(!$sqlc)
{ $msg = $resultc."\n";
$msg .= "####Error c####";
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 tablepublications_authors (ID_Publications, ID_Author, Created_Who) " .
"VALUES ('$formPublication','$IDAuthor[$i]','$log')";
$sqld = mysql_query($resultd);
#######################################Error Author-D###############################################################
if(!$sqld)
{ $msg = $resultd."\n";
$msg .= "####Error d####";
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
}
}
if($sql0)
{
$result2 = "SELECT tableauthors.ID_Author, " .
"tableauthors.Author AS Author1 " .
"FROM tableauthors, tablepublications_authors " .
"WHERE tableauthors.ID_Author = tablepublications_authors.ID_Author " .
" AND tablepublications_authors.ID_Publications = '$formPublication'";
$sql2 = mysql_query($result2);
#######################################Error 7###############################################################
if(!$sql2)
{ $msg = $result2."\n";
$msg .= "####Error 7####";
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 tablepublications_amplifications SET Paper_Cache = '$Paper' WHERE ID_Publications = '$formPublication'";
$sql3 = mysql_query($result3);
#######################################Error 8###############################################################
if(!$sql3)
{ $msg = $result3."\n";
$msg .= "####Error 8####";
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
if($sql2 and $sql3)
{echo "<span class='fine'>Data have been saved!</span>"; }
}
}
?>
<tr><td>Author:</td><td>
<?php
build_select_list("formAuthor",
"SELECT ID_Author, Author FROM tableauthors " .
"ORDER BY Author", $_POST['formAuthor']);
?>
or show all datasets: <input type="checkbox" id="checkbox" name="formShowAll" <?php if(isset($formShowAll)) { echo " checked"; }?>>
</td></tr>
<tr><td>Year:</td><td> <input type="text" name="formSearchYear" id="InputLong" value="<?php echo $_POST['formSearchYear']; ?>"/></td></tr>
<tr><td>Title:</td><td> <input type="text" name="formSearchTitle" id="InputLong" value="<?php echo $_POST['formSearchTitle']; ?>"/></td></tr>
<tr><td>Journal:</td><td>
<?php
build_select_list("formJournal",
"SELECT Journal_View, Journal_View FROM tablejournals " .
"ORDER BY Journal_View", $_POST['formJournal']);
?>
</td></tr>
<tr><td>Book:</td><td> <input type="text" name="formSearchBook" id="InputLong" value="<?php echo $_POST['formSearchBook']; ?>"/></td></tr>
<tr><td>Publisher:</td><td> <input type="text" name="formSearchPublisher" id="InputLong" value="<?php echo $_POST['formSearchPublisher']; ?>"/></td></tr>
<tr><td>Online version available:</td><td> <input type="checkbox" id="checkbox" name="formOnline" <?php if(isset($formOnline)) { echo " checked"; }?>/></td></tr>
<tr><td colspan="2"><input type="submit" id="Button" name="formSubmitSearch" value="Search"></td></tr>
<?php
if($_GET['deleteauthor'] == 'true')
{ $resultdeletea = "DELETE FROM tableauthors WHERE ID_Author NOT IN (SELECT ID_Author FROM tablepublications_authors)";
$sqldeletea = mysql_query($resultdeletea);
if($sqldeletea) { echo "<span class='fine'>Authors have been deleted!</span>"; } }
if($_GET['deletejournal'] == 'true')
{ $resultdeleteb = "DELETE FROM tablejournals WHERE ID_Journal NOT IN (SELECT ID_Journal FROM tablepublications)";
$sqldeleteb = mysql_query($resultdeleteb);
if($sqldeleteb) { echo "<span class='fine'>Journals have been deleted!</span>"; } }
$resulttesta = "SELECT DISTINCT ID_Author, Author FROM tableauthors WHERE ID_Author NOT IN (SELECT ID_Author FROM tablepublications_authors)";
$sqltesta = mysql_query($resulttesta);
$resulttestb = "SELECT DISTINCT ID_Journal, Journal_View FROM tablejournals WHERE ID_Journal NOT IN (SELECT ID_Journal FROM tablepublications)";
$sqltestb = mysql_query($resulttestb);
if(mysql_num_rows($sqltesta))
{echo "<tr><td colspan='2'>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='Publications_Edit_self.php?deleteauthor=true'>Yes</a></td></tr>"; }
if(mysql_num_rows($sqltestb))
{echo "<tr><td colspan='2'>Note: For the following journals don't exist publications: ";
while($row = mysql_fetch_object($sqltestb))
{ echo "<b>".$row->Journal_View. "; </b>"; }
echo "Do you want to delete this journals from database? <a href='Publications_Edit_self.php?deletejournal=true'>Yes</a></td></tr>"; }
?>
<tr><td colspan="2">
<?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 tablepublications_amplifications WHERE ID_Publications = '$IDPublication'";
$sql = mysql_query($result);
#######################################Error 1###############################################################
if(!$sql)
{ $msg = $result."\n";
$msg .= "####Error 1####";
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 tablepublications WHERE ID_Publications = '$IDPublication'";
$sql = mysql_query($result);
#######################################Error 2###############################################################
if(!$sql)
{ $msg = $result."\n";
$msg .= "####Error 2####";
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
else { echo "<span class='fine'>Publikation wurde gelöscht!</span>"; }
}
if($formAction == '2')
{
$result1 = "SELECT tablepublications.ID_Publications, " .
"tablepublications.Editors, " .
"tablepublications.Year, " .
"tablepublications.Title, " .
"tablejournals.Journal_View, " .
"tablepublications.Volume, " .
"tablepublications.Pages, " .
"tablepublications.Book, " .
"tablepublications.Publisher, " .
"tablepublications.Link " .
"FROM tablepublications
LEFT JOIN tablejournals ON tablepublications.ID_Journal = tablejournals.ID_Journal " .
"WHERE tablepublications.ID_Publications = '$IDPublication'";
$sql1 = mysql_query($result1);
#######################################Error 3###############################################################
if(!$sql1)
{ $msg = $result1."\n";
$msg .= "####Error 3####";
trigger_error($msg, E_USER_ERROR); }
#############################################################################################################
while($row = mysql_fetch_object($sql1))
{ $Editors = $row->Editors;
$Year = $row->Year;
$Title = $row->Title;
$JournalView = $row->Journal_View;
$Volume = $row->Volume;
$Pages = $row->Pages;
$Book = $row->Book;
$Publisher = $row->Publisher;
$Link = $row->Link; }
$result2 = "SELECT tableauthors.ID_Author, " .
"tableauthors.Author " .
"FROM tableauthors, tablepublications_authors " .
"WHERE tableauthors.ID_Author = tablepublications_authors.ID_Author " .
" AND tablepublications_authors.ID_Publications = '$IDPublication'";
$sql2 = mysql_query($result2);
#######################################Error 4###############################################################
if(!$sql2)
{ $msg = $result2."\n";
$msg .= "####Error 4####";
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='3'><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='InputLong' 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='InputLong' 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><select name='formJournal' id='InputLong'><option value=''>--------";
$resultJournal = mysql_query("SELECT ID_Journal, Journal_View FROM tablejournals " .
"ORDER BY Journal_View");
while($row=mysql_fetch_row($resultJournal))
{
$Journal_ID_Journal = $row[0];
$Journal_Journal = $row[1];
echo "<option value=\"$Journal_ID_Journal\"";
if($Journal_Journal == $JournalView)
{ echo " selected"; }
echo "> $Journal_Journal \n";
echo "</option>";
}
echo "</select>\n";
mysql_free_result($resultJournal);
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='2'>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><input type='text' nmame='formPublisher' id='InputLong' value='".$Publisher."'/></td></tr>";
echo "<tr><td colspan='3'>Link to online version: <input type='text' name='formLink' id='InputVeryLong2' value='".$Link."'/></td></tr>";
if($number >= 1)
{ echo "<tr><td colspan='3'><span class='error'>Edits will concern ".$number." Amplifications! Save Edits anyway?</span></td></tr>"; }
if($number == 0)
{ echo "<tr><td colspan='3'><span class='fine'>Edits will concern no Amplifications!!</span></td></tr>"; }
echo "<tr><td colspan='3'><input type='submit' id='button' name='formSubmitSave' value='Save Edits'></td></tr>";
echo "<tr><td colspan='3'><hr/></td></tr></table>";
}
}
}
if($formSubmitSearch)
{
$result = "";
$sqlcreate = "CREATE TEMPORARY TABLE tmpPublicationIDs TYPE=HEAP ";
include("date.php");
$tr = "=";
$no = "!=";
$lk = "like";
$bt = ">=";
$st = "<=";
$parr = $_POST;
array($parr);
foreach ($parr as $var)
{
$i ++;
if(!empty($var) and $var!="Search")
{
switch (key($parr))
{
case 'formAuthor':
$str = "tableauthors.ID_Author ".$tr."'".$var."' AND ";
$description = " Author = ";
$value = $formAuthor;
break;
case 'formSearchYear':
$str = "tablepublications.Year ".$lk."'%".$var."%' AND ";
$description = " Year = ";
$value = $formSearchYear;
break;
case 'formSearchTitle':
$str = "tablepublications.Title ".$lk."'%".$var."%' AND ";
$description = " Title = ";
$value = $formSearchTitle;
break;
case 'formJournal':
$str = "(tablejournals.Journal_All ".$lk."'%".$var."%' OR ";
$str .= "tablejournals.Journal_View ".$lk."'%".$var."%') AND ";
$description = " Journal = ";
$value = $formJournal;
break;
case 'formSearchBook':
$str = "tablepublications.Book ".$lk."'%".$var."%' AND ";
$description = " Book = ";
$value = $formSearchBook;
break;
case 'formSearchPublisher':
$str = "tablepublications.Publisher ".$lk."'%".$var."%' AND ";
$description = " Publisher = ";
$value = $formSearchPublisher;
break;
case 'formOnline':
$str = "tablepublications.Link ".$no."'' AND ";
$description = " Online version = ";
$value = "available";
break;
}
$where[$i] = $str;
$descriptionall[$i] = $description;
$valueall[$i] = $value;
}
next($parr);
}
if(empty($str) and !isset($formShowAll))
{ echo "<table border='0' id='tablesearch'><tr><td><b>No search key entered.</b></td></tr></table>"; }
if(!empty($str) and isset($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 tablepublications.ID_Publications " .
"FROM tablepublications
LEFT JOIN tablejournals ON tablepublications.ID_Journal = tablejournals.ID_Journal
LEFT JOIN tablepublications_authors ON tablepublications.ID_Publications = tablepublications_authors.ID_Publications
LEFT JOIN tableauthors ON tablepublications_authors.ID_Author = tableauthors.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 tablepublications.ID_Publications, " .
"tablepublications.Year, " .
"tablepublications.Title, " .
"tablepublications.Volume, " .
"tablepublications.Pages, " .
"tablepublications.Book, " .
"tablepublications.Publisher, " .
"tablepublications.Link, " .
"tablepublications.Created_Who, " .
"tablepublications.Created_When, " .
"tablejournals.Journal_View, " .
"tablejournals.Journal_All " .
"FROM tablepublications JOIN tmpPublicationIDs
LEFT JOIN tablejournals ON tablepublications.ID_Journal = tablejournals.ID_Journal " .
"WHERE tablepublications.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>Linked Amplifications</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 tablepublications_authors.ID_Publications, " .
"tableauthors.Author, " .
"tablepublications_authors.Order " .
"FROM tablepublications_authors, tableauthors " .
"WHERE tablepublications_authors.ID_Author = tableauthors.ID_Author " .
" AND tablepublications_authors.ID_Publications = '$Test' " .
"ORDER BY tablepublications_authors.Order";
$sql2 = mysql_query($result2);
while ($roww = mysql_fetch_object($sql2))
{
if(isset($Author[$roww->ID_Publications]))
{$Author[$roww->ID_Publications] .= ", ";}
else
{$Author[$roww->ID_Publications] .= ""; }
$tmp = $roww->Author;
$Author[$roww->ID_Publications] .= $tmp;
$View = $Author[$roww->ID_Publications];
}
$result3 = "SELECT tablepublications_amplifications.ID_Pub_Amp " .
"FROM tablepublications_amplifications " .
"WHERE tablepublications_amplifications.ID_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['ID_Publications']."'></td><td align='center'>".$row['Year']."</td><td align='center'>".$row['Title']."</td><td align='center'>".$row['Journal_View']."</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>
<tr><td colspan="3"><input type="button" value="Return to Menu" name="formSubmitMenu" id="button" style="float:right" onClick="self.location.href = 'Index-Publications.php';"></td></tr>
</table>
</td>
</tr>
</table>
</form>
</body>
</html>