<!-- 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");
include ("Log.php");
if($formSubmitSearch or ($_GET['UnitIDS'] != "" and $_GET['sqlType'] != "Detail"))
{
setcookie("formSearch", "");
setcookie("formHigherTaxon", "");
setcookie("formTaxonName", "");
setcookie("formCountryList", "");
setcookie("formContinentList", "");
setcookie("formSeaList", "");
setcookie("formOceanList", "");
setcookie("formLatitudeFrom", "");
setcookie("formLatitudeTo", "");
setcookie("formLongitudeFrom", "");
setcookie("formLongitudeTo", "");
setcookie("formYearFrom", "");
setcookie("formYearTo", "");
setcookie("formCollector", "");
setcookie("formCollectorsNo", "");
setcookie("formQuality", "");
setcookie("formTissueList", "");
setcookie("formDnaBankNumber", "");
setcookie("formGenBankNumber", "");
setcookie("formNCBI", "");
setcookie("UnitIDS", "");
setcookie("CollCodeS", "");
setcookie("InstCodeS", "");
// setcookie("formTypeList", "");
setcookie("formType", "");
setcookie("formImage", "");
setcookie("formSpecUnitID", "");
setcookie("formDNAbank", "");
setcookie("FormOfAddress", "");
setcookie("title", "");
setcookie("surname", "");
setcookie("forename", "");
setcookie("email", "");
setcookie("email2", "");
setcookie("institut", "");
setcookie("division", "");
setcookie("street", "");
setcookie("postalcode", "");
setcookie("city", "");
setcookie("country", "");
setcookie("institut2", "");
setcookie("division2", "");
setcookie("street2", "");
setcookie("postalcode2", "");
setcookie("city2", "");
setcookie("country2", "");
setcookie("username", "");
setcookie("mta", "");
setcookie("notes", "");
setcookie("formSearch", $formSearch);
setcookie("formHigherTaxon", $formHigherTaxon);
setcookie("formTaxonName", $formTaxonName);
setcookie("formCountryList", $formCountryList);
setcookie("formContinentList", $formContinentList);
setcookie("formSeaList", $formSeaList);
setcookie("formOceanList", $formOceanList);
setcookie("formLatitudeFrom", $formLatitudeFrom);
setcookie("formLatitudeTo", $formLatitudeTo);
setcookie("formLongitudeFrom", $formLongitudeFrom);
setcookie("formLongitudeTo", $formLongitudeTo);
setcookie("formYearFrom", $formYearFrom);
setcookie("formYearTo", $formYearTo);
setcookie("formCollector", $formCollector);
setcookie("formCollectorsNo", $formCollectorsNo);
setcookie("formQuality", $formQuality);
setcookie("formTissueList", $formTissueList);
setcookie("formDnaBankNumber", $formDnaBankNumber);
setcookie("formGenBankNumber", $formGenBankNumber);
setcookie("formNCBI", $formNCBI);
setcookie("UnitIDS", $_GET['UnitIDS']);
setcookie("CollCodeS", $_GET['CollCodeS']);
setcookie("InstCodeS", $_GET['InstCodeS']);
// setcookie("formTypeList", $formTypeList);
setcookie("formType", $formType);
setcookie("formImage", $formImage);
setcookie("formSpecUnitID", $formSpecUnitID);
setcookie("formDNAbank", $formDNAbank);
}
?>
<html>
<head>
<meta http-equiv="Content-Language" content="de">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" type="text/css" href="style.css">
<link rel="stylesheet" type="text/css" href="XSLT/Styles.css">
<SCRIPT LANGUAGE="JavaScript1.1" src="functions.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript1.1" src="ajax_framework.js"></SCRIPT>
<!--[if gte IE 5]>
<style type="text/css">
#results,#results2, #results3, #results4 {
position:relative;
}
</style>
<! endif -->
<title>DNA Bank Network</title>
</head>
<body>
<table border="0" id="table1">
<form method="POST" action="Query.php" name="formular" onSubmit="return CheckQuery();">
<tr>
<td id="Head">
<img border="0" src="images/Logo-DNA-Bank-Network.jpg" width="107" height="59"></td>
<td align="left" valign="top">
<?php
if(isset($_SESSION['login']) or $_SESSION['login'])
{
echo "<table border='0' id='log2'><tr><td>Logged in as: ";
$log = $_SESSION['log'];
echo $log;
echo ". </td><td><i>";
if(isset($_COOKIE['test']) or $_COOKIE['test']) {
include ("shop.php"); }
echo "</i></td></tr></table>";}
?>
</td>
<td id="NaviImpressum" align="right">
<table border="0"><tr><td><?php include "header.php"; ?></td><td align="right">
<p><input type="text" name="formSearch" id="Search" autocomplete="off"><input type="submit" name="formSubmitSearch" value="Search" id="button"></td></tr></table>
</td>
</tr>
<tr>
<td height="105" colspan="3" background="images/image.jpg"></td>
</tr>
<NOSCRIPT><tr><td colspan="3" align="center"><span id="error">Please enable JavaScript to view this page. Our portal offers some features which require JavaScript to run properly.</span></td></tr></NOSCRIPT>
<tr>
<td valign="top"><?php
$v11 = true;
if($_GET['sqlType'] == "Detail")
{ $v11c = true; }
if($formSubmitSearch)
{ $v11a = true;}
if($_GET['sqlType'] != "Detail" and !$formSubmitSearch) { $v11b = true; }
include ("TableNavi.php"); ?></td><?php
if(!$formSubmitSearch and $_GET['sqlType'] != "Detail" and !$formSubmitShopDetail and $_REQUEST['hitlist'] != "true" and $ShopTest != 'true' and empty($_GET['UnitIDS']) and !$formSubmitShop and !$formSubmitShopDelete and !$formSubmitOrder and !$formSubmitOrder2)
{
echo "<td valign='top' colspan='2'>";
include("Advanced.php"); }
// Number of records per site
$pagesize = 50;
if(!$page || $page<1 || !is_numeric($page))
{ $page = 1; }
// elseif ($page > 50)
// { $page = 50; }
elseif($page<1)
$page = 1;
elseif(!is_numeric($page))
unset($page);
$sql = "";
$sqlcreate = "CREATE TEMPORARY TABLE tmpDnaIDs TYPE=HEAP ";
if($_REQUEST['page']!= "")
{ $sqllimit = "LIMIT " . (($page-1) * $pagesize) . "," . (($page * $pagesize) + 1); }
else
{ $sqllimit = "LIMIT " . ($pagesize + 1); }
/* FEHLERMELDUNG AUSGEBEN
$result = mysql_query($sql) or die($sql.'<br>'.mysql_error()); */
if($formSubmitShop or ($_GET['check'] == 'shop'))
{
$check == 'shop';
if(isset($_SESSION['login']) or $_SESSION['login'])
{ $ShopTest = "true"; }
}
if(!empty($formYearFrom) and (strlen($formYearFrom) != 4) or preg_match('/[^0-9]/', $formYearFrom))
{ $YearTest = "true";
$Error = "true"; }
if(!empty($formYearTo) and (strlen($formYearTo) != 4) or preg_match('/[^0-9]/', $formYearTo))
{ $YearTest = "true";
$Error = "true"; }
if(!empty($formYearFrom) and !empty($formYearTo) and ($formYearFrom > $formYearTo))
{ $YearTest2 = "true";
$Error = "true"; }
if(!empty($formLatitudeFrom) or !empty($formLatitudeTo) or !empty($formLongitudeFrom) or !empty($formLongitudeTo))
{
if(preg_match('/[^0-9.-]/', $formLatitudeFrom) or preg_match('/[^0-9.-]/', $formLatitudeTo) or preg_match('/[^0-9.-]/', $formLongitudeFrom) or preg_match('/[^0-9.-]/', $formLongitudeTo))
{ $CoordinateTest = "true";
$Error = "true"; }
if(($formLatitudeFrom > 90.0) or ($formLatitudeFrom < -90.0) or ($formLatitudeTo > 90.0) or ($formLatitudeTo < -90.0))
{ $CoordinateTest2 = "true";
$Error = "true"; }
if(($formLongitudeFrom > 180.0) or ($formLongitudeFrom < -180.0) or ($formLongitudeTo > 180.0) or ($formLongitudeTo < -180.0))
{ $CoordinateTest3 = "true";
$Error = "true"; }
}
if($Error == "true") {
echo "<td valign='top' colspan='2'><table border='0' id='TableTextSmall2'><tr><td><a class='hitlist2' href='Query.php'>New Search</a></td>";
echo "<td><a class='hitlist2' href='Query.php?back=true'>Change search</a></td></tr>";
echo "<tr><td colspan='2'><hr/></td></tr>";
echo "<tr><td colspan='2'><span id='error'>";
if($YearTest == "true") { echo "Collection year not valid! Please enter a four-digit year.<br/>"; }
if($YearTest2 == "true") { echo "First collection year has to be smaller than the second.<br/>"; }
if($CoordinateTest == "true") { echo "Coordinates not valid, comma not permitted! Please enter a decimal coordinate.<br/>"; }
if($CoordinateTest2 == "true") { echo "Coordinates not valid. Latitude must no be smaller than -90.0 or bigger than 90.0!<br/>"; }
if($CoordinateTest3 == "true") { echo "Coordinates not valid. Longitude must no be smaller than -180.0 or bigger than 180.0!<br/>"; }
echo "</span></td></tr></table>"; }
/*******************************************************************************************************
* Suchabfrage für Schlagwortsuche oder Erweiterte Suche *
* *
*******************************************************************************************************/
if($_GET['sqlType'] != "Detail" and !$formSubmitShopDetail and $Error != "true" and ($formSubmitSearch or $_REQUEST['hitlist'] == "true" or $ShopTest == 'true' or $_GET['UnitIDS'] != ""))
{
$tr = "=";
$no = "!=";
$lk = "like";
$bt = ">=";
$st = "<=";
if($_REQUEST['hitlist'] == "true" or $ShopTest == "true")
{$parr = $_COOKIE; }
if($_REQUEST['hitlist'] == "" and $ShopTest != "true")
{$parr = $_POST; }
if($_GET['UnitIDS'] != "" and $_GET['sqlType'] != "Detail")
{$parr = $_GET; }
array($parr);
foreach ($parr as $var)
{
$i ++;
if(preg_match('/[<>]/', $var))
{ $testhtml = true;
echo "</td></tr><tr><td colspan='3' align='center'><span id='error'>Incorrect character (<,>). Please change your entry!</span>"; }
if($testhtml != true)
{
if(!empty($var) and $var!="Search" and key($parr)!="PHPSESSID" and $var!="test" and $var!="Yes")
{
switch (key($parr))
{
case 'formSearch':
$last = substr($var,-1,1);
$test = strlen($var);
$count = $test-1;
$search = substr($var,0,$count);
if($last == '*' and $test > 1)
{
$str = "(tablecachetaxonidentified.NameAuthorYear ".$lk." '".addslashes($search)."%' OR ";
$str .= "tablecache.UnitID_DNA ".$lk." '".addslashes($search)."%' OR ";
$str .= "tablecache.UnitID_Specimen ".$lk." '".addslashes($search)."%' OR ";
$str .= "tablecache.CountryName ".$lk." '".addslashes($search)."%' OR ";
$str .= "tablecachehighertaxa.Family ".$lk." '".addslashes($search)."%') AND "; }
if($last != '*')
{
$str = "(tablecachetaxonidentified.NameAuthorYear ".$lk." '".addslashes($var)."%' OR ";
$str .= "tablecache.UnitID_DNA ".$lk." '".addslashes($var)."' OR ";
$str .= "tablecache.UnitID_Specimen ".$lk." '".addslashes($var)."' OR ";
$str .= "tablecache.CountryName ".$lk." '".addslashes($var)."' OR ";
$str .= "tablecachehighertaxa.Family ".$lk." '".addslashes($var)."') AND "; }
$description = " Search key = ";
if($_POST['formSearch']) { $value = $formSearch; }
else {$value = $_COOKIE['formSearch']; }
break;
case 'formHigherTaxon':
$str = "tablecachehighertaxa.Family ".$tr." '".addslashes($var)."' AND ";
$description = " Family = ";
if($_POST['formHigherTaxon']) {$value = $formHigherTaxon; }
else {$value = $_COOKIE['formHigherTaxon'];}
break;
case 'formTaxonName':
$last = substr($var,-1,1);
$test = strlen($var);
$count = $test-1;
$search = substr($var,0,$count);
if($last == '*' and $test > 1)
{
$str = "tablecachetaxonidentified.NameAuthorYear ".$lk." '".addslashes($search)."%' AND "; }
if($last != '*')
{
$str = "tablecachetaxonidentified.NameAuthorYear ".$tr." '".addslashes($var)."' AND "; }
$description = " Species = ";
if($_POST['formTaxonName']) {$value = $formTaxonName; }
else {$value = $_COOKIE['formTaxonName'];}
break;
case 'formNCBI':
$str = "tablecachetaxonidentified.Tax_ID_NCBI ".$tr." '".addslashes($var)."' AND ";
$description = " Taxonomy ID (NCBI) = ";
if($_POST['formNCBI']) {$value = $formNCBI; }
else {$value = $_COOKIE['formNCBI'];}
break;
case 'formCountryList':
$str = "tablecache.CountryName ".$tr." '".addslashes($var)."' AND ";
$description = " Country = ";
if($_POST['formCountryList']) {$value = $formCountryList; }
else {$value = $_COOKIE['formCountryList'];}
break;
case 'formContinentList':
$str = "tablecountry_continent.Continent ".$tr." '".addslashes($var)."' AND ";
$description = " Continent = ";
if($_POST['formContinentList']) {$value = $formContinentList; }
else {$value = $_COOKIE['formContinentList'];}
break;
case 'formSeaList':
$str = "tablecache.Seas ".$tr." '".addslashes($var)."' AND ";
$description = " Sea = ";
if($_POST['formSeaList']) {$value = $formSeaList; }
else {$value = $_COOKIE['formSeaList'];}
break;
case 'formOceanList':
$str = "tablecache.Ocean ".$tr." '".addslashes($var)."' AND ";
$description = " Ocean = ";
if($_POST['formOceanList']) {$value = $formOceanList; }
else {$value = $_COOKIE['formOceanList'];}
break;
case 'formLongitudeFrom':
$str = "tablecache.LongitudeDecimal ".$bt." '".addslashes($var)."' AND ";
$description = " Longitude >= ";
if($_POST['formLongitudeFrom']) {$value = $formLongitudeFrom; }
else {$value = $_COOKIE['formLongitudeFrom'];}
break;
case 'formLongitudeTo':
$str = "tablecache.LongitudeDecimal ".$st." '".addslashes($var)."' AND ";
$description = " Longitude <= ";
if($_POST['formLongitudeTo']) {$value = $formLongitudeTo; }
else {$value = $_COOKIE['formLongitudeTo'];}
break;
case 'formLatitudeFrom':
$str = "tablecache.LatitudeDecimal ".$bt." '".addslashes($var)."' AND ";
$description = " Latitude >= ";
if($_POST['formLatitudeFrom']) {$value = $formLatitudeFrom; }
else {$value = $_COOKIE['formLatitudeFrom'];}
break;
case 'formLatitudeTo':
$str = "tablecache.LatitudeDecimal ".$st." '".addslashes($var)."' AND ";
$description = " Latitude <= ";
if($_POST['formLatitudeTo']) {$value = $formLatitudeTo; }
else {$value = $_COOKIE['formLatitudeTo'];}
break;
case 'formYearFrom':
$str = "tablecache.CollectionYear ".$bt." '".addslashes($var)."' AND ";
$description = " Collection Year >= ";
if($_POST['formYearFrom']) {$value = $formYearFrom; }
else {$value = $_COOKIE['formYearFrom'];}
break;
case 'formYearTo':
$str = "tablecache.CollectionYear ".$st." '".addslashes($var)."' AND ";
$description = " Collection Year <= ";
if($_POST['formYearTo']) {$value = $formYearTo; }
else {$value = $_COOKIE['formYearTo'];}
break;
case 'formCollector':
$str = "tablecache.Collector ".$lk." '%".addslashes($var)."%' AND ";
$description = " Collector = ";
if($_POST['formCollector']) {$value = $formCollector; }
else {$value = $_COOKIE['formCollector'];}
break;
case 'formCollectorsNo':
$str = "tablecache.CollectorsNo ".$lk." '%".addslashes($var)."%' AND ";
$description = " Collector's No = ";
if($_POST['formCollectorsNo']) {$value = $formCollectorsNo; }
else {$value = $_COOKIE['formCollectorsNo'];}
break;
case 'formType':
$str = "tablecache.TypeStatus ".$no." '' AND ";
$description = " Type Status = ";
$value = "true";
break;
case 'formImage':
$str = "tablecache.Image_Available ".$tr."'1' AND ";
$description = " Images = ";
$value = "available";
break;
case 'formSpecUnitID':
$str = "tablecache.UnitID_Specimen ".$tr." '".addslashes($var)."' AND ";
$description = " UnitID (Specimen) = ";
if($_POST['formSpecUnitID']) {$value = $formSpecUnitID; }
else {$value = $_COOKIE['formSpecUnitID'];}
break;
case 'formQuality':
$str = "tablecache.DNA_Quality ".$tr." '".addslashes($var)."' AND ";
$description = " DNA Quality = ";
if($_POST['formQuality']) {$value = $formQuality; }
else {$value = $_COOKIE['formQuality'];}
break;
case 'formTissueList':
$str = "tablecache.Tissue ".$tr." '".addslashes($var)."' AND ";
$description = " Tissue = ";
if($_POST['formTissueList']) {$value = $formTissueList; }
else {$value = $_COOKIE['formTissueList'];}
break;
case 'formDnaBankNumber':
$str = "tablecache.UnitID_DNA ".$tr." '".addslashes($var)."' AND ";
$description = " DNA Bank No = ";
if($_POST['formDnaBankNumber']) {$value = $formDnaBankNumber; }
else {$value = $_COOKIE['formDnaBankNumber'];}
break;
case 'formGenBankNumber':
$str = "tablecachegenbanknumbers.GenBankNumber ".$tr." '".addslashes($var)."' AND ";
$description = " GenBank Accession Number = ";
if($_POST['formGenBankNumber']) {$value = $formGenBankNumber; }
else {$value = $_COOKIE['formGenBankNumber'];}
break;
case 'formDNAbank':
$str = "tablecache.ID_Dataset_DNA ".$tr." '".addslashes($var)."' AND ";
$description = " DNA Bank = ";
$result = mysql_query("SELECT InstitutionCode_DNA FROM tablecache WHERE ID_Dataset_DNA ".$tr." '".$var."'");
while($row = mysql_fetch_object($result))
{$value = $row->InstitutionCode_DNA; }
break;
case 'UnitIDS':
$str = "tablecache.UnitID_Specimen ".$tr." '".addslashes($var)."' AND ";
$description = " UnitID Specimen = ";
if($_GET['UnitIDS']) {$value = $_GET['UnitIDS']; }
else {$value = $_COOKIE['UnitIDS'];}
break;
case 'CollCodeS':
$str = "tablecache.CollectionCode_Specimen ".$tr." '".addslashes($var)."' AND ";
$description = " Collection Code Specimen = ";
if($_GET['CollCodeS']) {$value = $_GET['CollCodeS']; }
else {$value = $_COOKIE['CollCodeS'];}
break;
case 'InstCodeS':
$str = "tablecache.InstitutionCode_Specimen ".$tr." '".addslashes($var)."' AND ";
$description = " Institution Code Specimen = ";
if($_GET['InstCodeS']) {$value = $_GET['InstCodeS']; }
else {$value = $_COOKIE['InstCodeS'];}
break;
case 'page':
break;
}
$where[$i] = $str;
$descriptionall[$i] = $description;
$valueall[$i] = $value;
}
next($parr);
}}
if(empty($str))
{
echo "<td valign='top' colspan='2'><table id='TableWrapper2'><tr><td><a class='hitlist2' href='Query.php'>New Search</a></td></tr>";
echo "<tr><td><hr/></td></tr>";
echo "<tr><td><span class='error'>No search key entered.</span></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;
/*******************************************************************************************************
* SQL-Befehl wird zusammengebaut aus den verschiedenen Such-Feldern der Formulare, *
* Ansichtstyp "DNA" wird festgelegt (Trefferliste) *
*******************************************************************************************************/
//abfrage
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
$sqlType = "Dna";
$sql = $sqlcreate .
$sql = "SELECT DISTINCT tablecache.ID_Cache FROM tablecache
LEFT JOIN tablecachetaxonidentified ON tablecache.ID_Cache = tablecachetaxonidentified.ID_Cache
LEFT JOIN tablecachehighertaxa ON tablecachetaxonidentified.ID_TaxonIdentified = tablecachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN tablecachegenbanknumbers ON tablecache.ID_Cache = tablecachegenbanknumbers.ID_Cache
LEFT JOIN tablecountry_continent ON tablecache.ID_Country = tablecountry_continent.ID_Country " .
$wherestr." ORDER BY tablecachetaxonidentified.NameAuthorYear, tablecache.CountryName ".$sqllimit;
//echo $sql;
$result = mysql_query($sql);
$sqlcount = "SELECT DISTINCT tablecache.ID_Cache FROM tablecache
LEFT JOIN tablecachetaxonidentified ON tablecache.ID_Cache = tablecachetaxonidentified.ID_Cache
LEFT JOIN tablecachehighertaxa ON tablecachetaxonidentified.ID_TaxonIdentified = tablecachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN tablecachegenbanknumbers ON tablecache.ID_Cache = tablecachegenbanknumbers.ID_Cache
LEFT JOIN tablecountry_continent ON tablecache.ID_Country = tablecountry_continent.ID_Country " .
$wherestr;
$resultcount = mysql_query($sqlcount);
$gesamt = mysql_num_rows($resultcount);
//echo $sqlcount;
$log = addslashes($where_str);
$sqllog = "INSERT INTO tablelog (Query, Resultcount) VALUES ('$log','$gesamt')";
$resultlog = mysql_query($sqllog);
}
} //if($_GET['sqlType'] != "Detail" and !$formSubmitShopDetail and ($formSubmitSearch or $_REQUEST['hitlist'] == "true" or $ShopTest == "true"))
/*******************************************************************************************************
* SQL-Befehl wird zusammengebaut für Detail-Ansicht der DNA/Specimen-Sample *
* (2 Triple-IDs als Abfragewerte) *
*******************************************************************************************************/
if($_GET['sqlType'] == "Detail")
{
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
$sqlType = "Detail";
$UnitIDS = $_GET['UnitIDS'];
$CollCodeS = $_GET['CollCodeS'];
$InstCodeS = $_GET['InstCodeS'];
$UnitID = $_GET['UnitID'];
$CollCode = $_GET['CollCode'];
$InstCode = $_GET['InstCode'];
$ID_Cache = $_GET['ID_Cache'];
$sql = $sqlcreate;
if($UnitID == "Unknown")
{$sql .= "SELECT DISTINCT tablecache.ID_Cache FROM tablecache " .
"LEFT JOIN tablecachetaxonidentified ON tablecache.ID_Cache = tablecachetaxonidentified.ID_Cache
LEFT JOIN tablecachehighertaxa ON tablecachetaxonidentified.ID_TaxonIdentified = tablecachehighertaxa.ID_CacheTaxonIdentified ";
if($_GET['check'] == "query" and $ID_Cache != "")
{ $sql .= "WHERE tablecache.ID_Cache = '$ID_Cache' " . $sqllimit; }
else {
$sql .= "WHERE tablecache.UnitID_Specimen = '$UnitIDS' " .
" AND tablecache.CollectionCode_Specimen = '$CollCodeS' " .
" AND tablecache.InstitutionCode_Specimen = '$InstCodeS' " .
" AND tablecache.UnitID_DNA IS NULL " .
" AND tablecache.CollectionCode_DNA IS NULL " .
" AND tablecache.InstitutionCode_DNA = '$InstCode' " . $sqllimit; }
}
//echo $sql;
if($UnitID != "Unknown")
{
$sql .= "SELECT DISTINCT tablecache.ID_Cache FROM tablecache " .
"LEFT JOIN tablecachetaxonidentified ON tablecache.ID_Cache = tablecachetaxonidentified.ID_Cache
LEFT JOIN tablecachehighertaxa ON tablecachetaxonidentified.ID_TaxonIdentified = tablecachehighertaxa.ID_CacheTaxonIdentified ";
if($_GET['check'] == "query" and $ID_Cache != "")
{ $sql .= "WHERE tablecache.ID_Cache = '$ID_Cache' " . $sqllimit; }
else {
$sql .= "WHERE tablecache.UnitID_Specimen = '$UnitIDS' " .
" AND tablecache.CollectionCode_Specimen = '$CollCodeS' " .
" AND tablecache.InstitutionCode_Specimen = '$InstCodeS' " .
" AND tablecache.UnitID_DNA = '$UnitID' " .
" AND tablecache.CollectionCode_DNA = '$CollCode' " .
" AND tablecache.InstitutionCode_DNA = '$InstCode' " . $sqllimit; }
}
// }
}
/*******************************************************************************************************
* SQL-Befehl wird zusammengebaut für Shop-Ansicht (sieht aus wie normale Trefferliste, jedoch mit *
* Möglichkeit, Einträge aus Warenkorb zu entfernen *
*******************************************************************************************************/
if($formSubmitShopDetail or $formSubmitShopDelete)
{
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
$sqlType = "Shop";
$sql = $sqlcreate .
$sql .= "SELECT DISTINCT tablecache.ID_Cache FROM tablecache
LEFT JOIN tablecachetaxonidentified ON tablecache.ID_Cache = tablecachetaxonidentified.ID_Cache
LEFT JOIN tablecachehighertaxa ON tablecachetaxonidentified.ID_TaxonIdentified = tablecachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN tablecachegenbanknumbers ON tablecache.ID_Cache = tablecachegenbanknumbers.ID_Cache
LEFT JOIN tablecountry_continent ON tablecache.ID_Country = tablecountry_continent.ID_Country
LEFT JOIN tablecart ON tablecache.ID_Cache = tablecart.ID_Cache " .
"WHERE tablecart.Session = '$SID' AND tablecart.ID_User = '$UserID' ".
" ORDER BY tablecachetaxonidentified.NameAuthorYear, tablecache.CountryName, LENGTH(tablecache.UnitID_DNA), tablecache.UnitID_DNA";
$result = mysql_query($sql);
}
/*******************************************************************************************************
* SQL-Befehl wird zusammengebaut für Bestell-Übersichten (Warenkorb), keine Möglichkeit mehr, Dinge *
* aus dem Warenkorb zu entfernen *
*******************************************************************************************************/
if($formSubmitOrder or $formSubmitOrder2)
{
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
$sqlInstCode = "SELECT DISTINCT tablecache.InstitutionCode_DNA AS InstCodeDNA FROM tablecache
LEFT JOIN tablecachetaxonidentified ON tablecache.ID_Cache = tablecachetaxonidentified.ID_Cache
LEFT JOIN tablecachehighertaxa ON tablecachetaxonidentified.ID_TaxonIdentified = tablecachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN tablecachegenbanknumbers ON tablecache.ID_Cache = tablecachegenbanknumbers.ID_Cache
LEFT JOIN tablecountry_continent ON tablecache.ID_Country = tablecountry_continent.ID_Country
LEFT JOIN tablecart ON tablecache.ID_Cache = tablecart.ID_Cache " .
"WHERE tablecart.Session = '$SID' AND tablecart.ID_User = '$UserID' ".
"GROUP BY tablecache.InstitutionCode_DNA";
$resultInstCode = mysql_query($sqlInstCode);
while($row = mysql_fetch_object($resultInstCode))
{ $InstCodeDNA[] = $row->InstCodeDNA; }
}
$Dnacount=0;
if(!empty($sql) or ($check == "shop" and $ShopTest == "true") or !empty($sqlInstCode))
{
// just in case it is still here: drop tmpDnaIDs
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
mysql_query($sql);
if(!mysql_affected_rows())
{
if($ID_Cache != "")
{
#######################################Error 1###############################################################
$msg = "Try to find results for:\n";
$msg .= "ID_Cache: ".$ID_Cache."\n";
$msg .= $sql;
trigger_error($msg, E_USER_ERROR);
#############################################################################################################
}
/*******************************************************************************************************
* Trefferliste wird erzeugt, Anzeige für "No hits" *
* *
*******************************************************************************************************/
if($sqlType=="Dna") {
echo "<td valign='top' colspan='2'><table id='TableWrapper2'><tr><td><a class='hitlist2' href='Query.php'>New Search</a></td>";
echo "<td><a class='hitlist2' href='Query.php?back=true'>Change search</a></td><td colspan='2'></td><tr>";
echo "<tr><td colspan='2'><hr/></td></tr>";
echo "<tr><td colspan='2'><span class='error'>No hits found for: ";
foreach($descriptionall as $index => $descriptiontest)
{ $valuetest = $valueall[$index];
echo $descriptiontest."[".$valuetest."]"; }
echo ".<br/><br/><br/></span></td></tr>";
echo "<tr><td colspan='2'>
<div class='box'>
Nothing found? <br/><br/>
Please note! The query boxes (e.g. species or family name) offer suggestion lists while the search term is being typed. If no list appears, no specimen is available in our DNA or tissue collection.<br/><br/>
If you search for DNA of a certain species which is not listed here please contact one of the curators see below. The partner institutions of the network hold major specimen collections. May be on-demand DNA extractions can be made available.</div></td></tr>
<tr><td width='150px'>Plants, Algae<br/>
Microorganisms<br/>
Fungi, Animals<br/>
Animals</td>
<td><a class='black' href='mailto:h.zetzsche@bgbm.org'>Holger Zetzsche (BGBM)</a><br/>
<a class='black' href='mailto:ckc@dsmz.de'>Cathrin Sproeer (DSMZ)</a><br/>
<a class='black' href='mailto:knebelsberger@zsm.mwn.de'>Thomas
Knebelsberger (ZSM)</a><br/>
<a class='black' href='mailto:h.letsch.zfmk@uni-bonn.de'>Harald Letsch (ZFMK) </a></td></tr>";
}
if($sqlType=="Detail" and !$formSubmitShop) {
echo "<td valign='top' colspan='2'><table id='TableWrapper2'><tr><td><a class='hitlist2' href='Query.php'>New Search</a></td>";
echo "<td><a class='hitlist2' href='Query.php?back=true'>Change search</a></td></tr>";
echo "<tr><td colspan='2'><hr/></td></tr>";
echo "<tr><td colspan='2'><span class='error'>No hits found! Please change your current search!</span></td></tr>";
}
echo "</table>";
}
else
{
if(isset($InstCodeDNA)) {
$sqlType = "Shop";
/*******************************************************************************************************
* Trefferliste für Warenkorb/Bestellung erzeugt *
* Übersicht nach DNA-Banken sortiert, also Bestellungen bei DNA Bank 1, Bestellungen bei DNA Bank 2 *
*******************************************************************************************************/
if(($formSubmitOrder or $formSubmitOrder2) and $sqlType=="Shop")
{
echo "<td valign='top' colspan='2'><h2><i>Your Shopping Cart</i></h2>";
if(!isset($_COOKIE['test']) or !$_COOKIE['test']) {
echo "<b>Please enable Cookies! Otherwise search will not work properly.</b><br>";}
echo "<table id='TableWrapper2'><tr><td width='20%'><a class='hitlist2' href='Query.php'>New search</a></td>";
echo "<td><a class='hitlist2' href='Query.php?back=true'>Change search</a>";
echo "</td></tr>
<tr><td colspan='2'><hr/></td></tr></table>";
if($formSubmitOrder)
{ echo "<span id='error'>Please check your data.";
if($sum > 1) { echo "You will order DNA from ".$sum." different DNA banks"; }
echo "</span><br/>"; }
$sum = sizeof($InstCodeDNA);
echo "<table border='0' id='TableTextSmall2'>";
if($formSubmitOrder2)
{ echo "<tr><td colspan='6'><b>Dear ".$log.".</b><br>
Thank you very much for your request of DNA samples from the DNA Bank Network.<br>
Your inquiry has been forwarded to the DNA bank(s) in authority of the requested samples. Shortly you will receive an email offer of these DNA bank(s) including binding sample prices and shipping fee(s).<br><br>
Sincerely,<br>
The DNA Bank Network Team</td></tr>";
echo "<tr><td colspan='6'><hr></td></tr>"; }
foreach ($InstCodeDNA as $InstCode) {
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
// $sql = $sqlcreate .
$sql = "CREATE TEMPORARY TABLE tmpDnaIDs TYPE=HEAP SELECT DISTINCT tablecache.ID_Cache FROM tablecache
LEFT JOIN tablecachetaxonidentified ON tablecache.ID_Cache = tablecachetaxonidentified.ID_Cache
LEFT JOIN tablecachehighertaxa ON tablecachetaxonidentified.ID_TaxonIdentified = tablecachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN tablecachegenbanknumbers ON tablecache.ID_Cache = tablecachegenbanknumbers.ID_Cache
LEFT JOIN tablecountry_continent ON tablecache.ID_Country = tablecountry_continent.ID_Country
LEFT JOIN tablecart ON tablecache.ID_Cache = tablecart.ID_Cache " .
"WHERE tablecart.Session = '$SID' AND tablecart.ID_User = '$UserID' ".
" AND tablecache.InstitutionCode_DNA = '$InstCode' " .
" ORDER BY tablecachetaxonidentified.NameAuthorYear, tablecache.CountryName, LENGTH(tablecache.UnitID_DNA), tablecache.UnitID_DNA ".$sqllimit;
$result = mysql_query($sql);
include("Query-Shop.php");
if($formSubmitOrder2)
{ echo $message;
$test[] = $message;
$mesgDNABank = "<font face='Arial' size='1'><table id='TableImpressum'><tr><td colspan='6'><b>Dear ".$InstitutionCode." DNA Bank.</b><br>";
$mesgDNABank .= "The customer ".$Forename." ".$Surname." preconfirmed ".$gesamt." DNA ";
if($gesamt == 1) { $mesgDNABank .= "sample"; }
if($gesamt > 1) { $mesgDNABank .= "samples"; }
$mesgDNABank .= " of your DNA bank. Please check if the ";
if($gesamt == 1) { $mesgDNABank .= "sample is"; }
if($gesamt > 1) { $mesgDNABank .= "samples are"; }
$mesgDNABank .= " available and send an email offer containing binding prices and shipping fees to the customer.<br><br></td></tr>";
$mesgDNABank .= $message; //Taxa und DNA-Nummern
$mesgDNABank .= "<tr class='background1'><td colspan='5'><b>Customer details:</b></td></tr>";
$mesgDNABank .= "<tr><td><b>Name</b></td><td colspan='4'>";
$mesgDNABank .= $Title ? $Title." " : "";
$mesgDNABank .= $FormOfAddress ? $FormOfAddress." " : "";
$mesgDNABank .= $Forename." ".$Surname."</td></tr>";
$mesgDNABank .= "<tr><td><b>Email</b></td><td colspan='4'>".$Email."</td></tr>";
$mesgDNABank .= $Phone ? "<tr><td valign='top'><b>Phone</b></td><td colspan='4'>".$Phone."</td></tr>" : "";
$mesgDNABank .= "<tr><td valign='top'><b>Address</b></td><td colspan='4'>";
$mesgDNABank .= $Institut ? $Institut."<br>" : "";
$mesgDNABank .= $Division ? $Division."<br>" : "";
$mesgDNABank .= $Street ? $Street."<br>" : "";
$mesgDNABank .= $Postal_Code ? $Postal_Code." " : "";
$mesgDNABank .= $City ? $City."<br>" : "<br>";
$mesgDNABank .= $Country ? $Country."" : "";
$mesgDNABank .= "</td></tr>";
$mesgDNABank .= $Institut_Delivery || $Division_Delivery || $Street_Delivery || $City_Delivery || $Postal_Code_Delivery || $Country_Delivery ? "<tr><td><b>Delivery Address</b></td><td colspan='5'>".$Institut_Delivery."<br>".$Division_Delivery."<br>".$Street_Delivery."<br>".$Postal_Code_Delivery." ".$City_Delivery."<br>".$Country_Delivery."</td></tr>" : "";
$mesgDNABank .= "<tr><td valign='top'><b>Notes</b><td colspan='4'>".$formNotes."</td></tr>";
$mesgDNABank .= "</table></font>";
$fromDNABank = "From: contact@dnabank-network.org\n";
$fromDNABank .= "Content-Type: text/html\n";
mail($ContactEmail, "DNA Bank Network - Order Request", htmlspecialchars_decode($mesgDNABank), $fromDNABank);
}
if($formSubmitOrder)
{ echo "<tr><td colspan='6'><hr><br></td></tr>"; }
}
/*******************************************************************************************************
* Anzeige der Kundendaten *
* *
*******************************************************************************************************/
if($formSubmitOrder)
{ echo "<tr class='background1'><td colspan='6'><b>Customer details:</b></td></tr>";
echo "<tr><td><br><b>Name</b></td><td colspan='5'><br>";
echo $Title ? $Title." " : "";
echo $FormOfAddress ? $FormOfAddress." " : "";
echo $Forename." ".$Surname."</td></tr>";
echo "<tr><td valign='top'><b>Address</b></td><td colspan='5'>";
echo $Institut ? $Institut."<br>" : "";
echo $Division ? $Division."<br>" : "";
echo $Street ? $Street."<br>" : "";
echo $Postal_Code ? $Postal_Code." " : "";
echo $City ? $City."<br>" : "<br>";
echo $Country ? $Country."" : "";
echo "</td></tr>";
echo $Institut_Delivery || $Division_Delivery || $Street_Delivery || $City_Delivery || $Postal_Code_Delivery || $Country_Delivery ? "<tr><td><b>Delivery Address</b></td><td colspan='5'>".$Institut_Delivery."<br>".$Division_Delivery."<br>".$Street_Delivery."<br>".$Postal_Code_Delivery." ".$City_Delivery."<br>".$Country_Delivery."</td></tr>" : "";
echo "<tr><td valign='top'><br><br><b>Notes</b><td colspan='5'><br><br><textarea name='formNotes' id='notes'></textarea></td></tr>";
echo '<tr><td colspan="4"><span id="error">Your order will be directed to the DNA bank(s) in authority of requested samples. Every DNA bank only receives its relevant order information. Subsequently a confirmation email will be send to you by the DNA bank(s) in question. An offer including binding prices will than be made within a separate email.</span></td><td colspan="2" align="right" valign="bottom"><input type="submit" name="formSubmitShopDetail" value="Correct (Step 2/3<-)" id="buttonorder2"><br><br><input type="submit" name="formSubmitOrder2" value="Finish Order (->Step 3/3)" id="buttonorder"></td></tr>'; }
if($formSubmitOrder2)
{
$mesg = "<font face='Arial'><table><tr><td colspan='6'><b>Dear ".$Forename." ".$Surname.".</b><br>";
$mesg .= "Thank you very much for your request of DNA samples from the DNA Bank Network. You preconfirmed ".$items;
if($gesamt == 1) { $mesg .= " sample "; }
if($gesamt > 1) { $mesg .= " samples "; }
$mesg .= "from ".$sum;
if($sum == 1) { $mesg .= " DNA bank"; }
if($sum > 1) { $mesg .= " DNA banks"; }
$mesg .= ".<br><br></td></tr>";
foreach($test as $ausgabe)
{ $mesg .= $ausgabe; }
$mesg .= "<tr><td colspan='6'>Your inquiry has been forwarded to the DNA bank(s) in authority of the requested samples. Shortly you will receive an email offer of these DNA bank(s) including binding sample prices and shipping fees.<br><br>
Thank you for your interest.<br><br><hr>
Sincerely,<br>The DNA Bank Network Team</td></tr>";
$mesg .= "</table></font>";
$from = "From: contact@dnabank-network.org\n";
$from .= "Content-Type: text/html\n";
mail($Email, "DNA Bank Network - Order Confirmation", htmlspecialchars_decode($mesg), $from);
$sqldelete = "DELETE FROM tablecart WHERE ID_User = '$UserID' and Session = '$SID'";
$resultdelete = mysql_query($sqldelete);
}
echo "</table>";
echo "<br><br>";
}
}
else {
if($SID)
{
/*******************************************************************************************************
* Zusammenbauen der Ergebnisse allgemein *
* *
*******************************************************************************************************/
// Abfrage für TableDnaBankNumbers
$result1 = mysql_query
("SELECT tablecache.ID_Cache AS ID_Cache, " .
" tablecache.ID_Dataset_Specimen AS ID_Dataset_Specimen, " .
" tablecache.UnitID_Specimen AS UnitID_Specimen, " .
" tablecache.CollectionCode_Specimen AS CollectionCode_Specimen, " .
" tablecache.InstitutionCode_Specimen AS InstitutionCode_Specimen, " .
// " tablecache.Continent AS Continent, " .
" tablecache.CountryName AS CountryName, " .
// " tablecache.CountryISO2 AS CountryISO2, " .
// " tablecache.Collector AS Collector, " .
// " tablecache.CollectionDate AS CollectionDate, " .
// " tablecache.CollectorsNo AS CollectorsNo, " .
// " tablecache.LocalityText AS LocalityText, " .
// " tablecache.Altitude AS Altitude, " .
// " tablecache.LongitudeDecimal AS LongitudeDecimal, " .
// " tablecache.LatitudeDecimal AS LatitudeDecimal, " .
" tablecache.UnitID_DNA AS UnitID_DNA, " .
" tablecache.CollectionCode_DNA AS CollectionCode_DNA, " .
" tablecache.InstitutionCode_DNA AS InstitutionCode_DNA, " .
" tablecache.Blocked_Until AS Blocked_Until " .
" FROM tablecache JOIN tmpDnaIDs " .
" WHERE tablecache.ID_Cache = tmpDnaIDs.ID_Cache");
if(!$result1) { echo "</td></tr><table border='0' id='TableTextSmall2'><tr><td><a class='hitlist2' href='Query.php'>New Search</a></td></tr>";
echo "<tr><td><a class='hitlist2' href='Query.php?back=true'>Change search</a></td></tr>";
echo "<tr><td align='center'><b>No hits found! Please change your current search!</b></td></tr>"; }
else {
$result2 = mysql_query
("SELECT tablecachetaxonidentified.ID_Cache, " .
" tablecachetaxonidentified.NameAuthorYear AS NameAuthorYear, " .
" tablecachetaxonidentified.PreferredFlag AS PreferredFlag " .
" FROM tablecachetaxonidentified, tmpDnaIDs " .
" WHERE tablecachetaxonidentified.ID_Cache = tmpDnaIDs.ID_Cache");
// $result = mysql_query($result3) or die($result3.'<br>'.mysql_error());
while($row = mysql_fetch_object($result2))
{
if(isset($PreferredFlag[$row->ID_Cache]))
{ $Flag = $PreferredFlag[$row->ID_Cache];
if($Flag == 'true' or $Flag == '1') {$Preferred = "(preferred species name)";}
else {$Preferred = ""; }
}
else
{ $PreferredFlag[$row->ID_Cache] = ""; }
if(isset($NameAuthorYear[$row->ID_Cache]))
{ $NameAuthorYear[$row->ID_Cache] .= " " . $Preferred . ", Other Identifications: "; }
else
{ $NameAuthorYear[$row->ID_Cache] = ""; }
$tmp = $row->NameAuthorYear;
$NameAuthorYear[$row->ID_Cache] .= $tmp;
$tmp2 = $row->PreferredFlag;
$PreferredFlag[$row->ID_Cache] .= $tmp2; }
$result3 = mysql_query
("SELECT tablecache.ID_Cache, " .
" tablecachetaxonidentified.ID_TaxonIdentified, " .
" tablecachehighertaxa.Family AS Family, " .
" tablecachehighertaxa.TaxonRank AS TaxonRank " .
" FROM tablecache, tablecachetaxonidentified, tablecachehighertaxa, tmpDnaIDs " .
" WHERE tablecachehighertaxa.ID_CacheTaxonIdentified = tablecachetaxonidentified.ID_TaxonIdentified " .
" AND tablecachetaxonidentified.ID_Cache = tablecache.ID_Cache " .
" AND tablecache.ID_Cache = tmpDnaIDs.ID_Cache " .
" AND (tablecachehighertaxa.TaxonRank = 'familia' OR " .
" tablecachehighertaxa.TaxonRank = 'family' OR " .
" tablecachehighertaxa.TaxonRank = 'familie')");
while($row = mysql_fetch_object($result3))
{
if(isset($TaxonRank[$row->ID_Cache]))
{ $TaxonRank[$row->ID_Cache] .= "<br>"; }
else { $TaxonRank[$row->ID_Cache] = ""; }
if(isset($Family[$row->ID_Cache]))
{
$Family[$row->ID_Cache] .= "<br>";
}
else
{ $Family[$row->ID_Cache] = ""; }
$tmp = $row->Family;
$Family[$row->ID_Cache] .= $tmp;
$tmp2 = $row->TaxonRank;
$TaxonRank[$row->ID_Cache] .= $tmp2;
}
if($sqlType=="Detail")
{
$result4 = mysql_query
("SELECT tablecache.ID_Cache, " .
" tabledataset.ID_Dataset AS ID_Dataset, " .
" tableschema.QueryPart1b AS QueryPart1b, " .
" tableschema.QueryPart1_1 AS QueryPart1_1, " .
" tableschema.QueryPart1_2b AS QueryPart1_2b, " .
" tableschema.QueryPart2 AS QueryPart2, " .
" tableschema.QueryPart3 AS QueryPart3, " .
" tableschema.QueryPart4 AS QueryPart4, " .
" tableschema.QueryPart5 AS QueryPart5, " .
" tableprovider.Provider AS Provider, " .
" tabledataset.Digir_Resource AS Digir_Resource, " .
" tabledataset.Digir_Source AS Digir_Source, " .
" tabledataset.bioRecordResourceKey AS ResourceKey, " .
" tabledataset.Uuid AS Uuid, " .
" tableprovider.Provider AS Provider, " .
" tableprovider.ID_Schema AS ID_Schema " .
" FROM tablecache, tabledataset, tableschema, tableprovider, tmpDnaIDs " .
" WHERE tableprovider.ID_Schema = tableschema.ID_Schema " .
" AND tabledataset.ID_Provider = tableprovider.ID_Provider " .
" AND tablecache.ID_Dataset_Specimen = tabledataset.ID_Dataset " .
" AND tablecache.ID_Cache = tmpDnaIDs.ID_Cache");
while($row = mysql_fetch_object($result4))
{
$ID_Dataset = $row->ID_Dataset_Specimen;
$Provider = $row->Provider;
$QueryPart1b = $row->QueryPart1b;
$QueryPart1_1 = $row->QueryPart1_1;
$QueryPart1_2b = $row->QueryPart1_2b;
$QueryPart2 = $row->QueryPart2;
$QueryPart3 = $row->QueryPart3;
$QueryPart4 = $row->QueryPart4;
$QueryPart5 = $row->QueryPart5;
$Digir_Resource = $row->Digir_Resource;
$Digir_Source = $row->Digir_Source;
$ResourceKey = $row->ResourceKey; //gilt nur für die Specimens!!
$ID_Schema = $row->ID_Schema;
$Uuid = $row->Uuid;
} //while $row $result1 $sqlType="Detail"
$result5 = mysql_query
("SELECT tablecache.ID_Cache, " .
" tabledataset.ID_Dataset AS ID_Dataset2, " .
" tabledataset.ContactName AS ContactName, " .
" tabledataset.ContactEmail AS ContactEmail, " .
" tableschema.QueryPart1b AS QueryPart1b2, " .
" tableschema.QueryPart1_1 AS QueryPart1_12, " .
" tableschema.QueryPart1_2b AS QueryPart1_2b2, " .
" tableschema.QueryPart2 AS QueryPart22, " .
" tableschema.QueryPart3 AS QueryPart32, " .
" tableschema.QueryPart4 AS QueryPart42, " .
" tableschema.QueryPart5 AS QueryPart52, " .
" tableprovider.Provider AS Provider2, " .
" tabledataset.Digir_Resource AS Digir_Resource2, " .
" tabledataset.Digir_Source AS Digir_Source2, " .
" tableprovider.Provider AS Provider2, " .
" tableprovider.ID_Schema AS ID_Schema2 " .
" FROM tablecache, tabledataset, tableschema, tableprovider, tmpDnaIDs " .
" WHERE tableprovider.ID_Schema = tableschema.ID_Schema " .
" AND tabledataset.ID_Provider = tableprovider.ID_Provider " .
" AND tablecache.ID_Dataset_DNA = tabledataset.ID_Dataset " .
" AND tablecache.ID_Cache = tmpDnaIDs.ID_Cache");
//$result5 = mysql_query($test2) or die($test2.'<br>'.mysql_error());
while($row = mysql_fetch_object($result5))
{
$ID_Dataset2 = $row->ID_Dataset_Specimen2;
$ContactName = $row->ContactName;
$ContactEmail = $row->ContactEmail;
$Provider2 = $row->Provider2;
$QueryPart1b2 = $row->QueryPart1b2;
$QueryPart1_12 = $row->QueryPart1_12;
$QueryPart1_2b2 = $row->QueryPart1_2b2;
$QueryPart22 = $row->QueryPart22;
$QueryPart32 = $row->QueryPart32;
$QueryPart42 = $row->QueryPart42;
$QueryPart52 = $row->QueryPart52;
$Digir_Resource2 = $row->Digir_Resource2;
$Digir_Source2 = $row->Digir_Source2;
$ID_Schema2 = $row->ID_Schema2;
} //while $row $result1 $sqlType="Detail" */
if($_GET['link'] != "")
{ $result6 = mysql_query
("SELECT tablecache.ID_Cache, " .
" tableannotations.Replace_TaxonName " .
" FROM tablecache, tableannotations, tmpDnaIDs " .
" WHERE tablecache.UnitID_Specimen = tableannotations.UnitID_Specimen " .
" AND tablecache.CollectionCode_Specimen = tableannotations.CollectionCode_Specimen " .
" AND tablecache.InstitutionCode_Specimen = tableannotations.InstitutionCode_Specimen " .
" AND tablecache.ID_Cache = tmpDnaIDs.ID_Cache");
while($row = mysql_fetch_object($result6))
{
$ReplaceTaxonName = $row->Replace_TaxonName; } //while $row $result1 $sqlType="Detail" */
}
}
}
}
/*******************************************************************************************************
* Zusammenbauen der Ergebnisse normale Trefferliste *
* *
*******************************************************************************************************/
if($sqlType=="Dna")
{
echo "<td valign='top' colspan='2'>";
if((!isset($_COOKIE['test']) or !$_COOKIE['test']) and $_GET['UnitIDS'] = "") {
echo "<b>Please enable Cookies! Otherwise search will not work properly.</b><br/>";}
echo "<table id='TableWrapper2' border='0'><tr><td><a class='hitlist2' href='Query.php'>New search</a></td><td> ";
echo "<a class='hitlist2' href='Query.php?back=true'>Change search</a></td>";
if(!isset($_SESSION['login']) or !$_SESSION['login'])
{echo '<td align="right"><a class="orange" href="Login.php?check=search">Please login to add DNA samples to </a></td><td><img border="0" height="35px" src="images/warenkorb.jpg"></td></tr>
<tr><td colspan="4"><hr/></td></table>'; }
if(isset($_SESSION['login']) or $_SESSION['login'])
{echo '<td align="right"><button type="submit" name="formSubmitShop" value="Yes" id="buttonshop">Add selected DNA samples to <img border="0" height="35px" src="images/warenkorb.jpg"></button>';
echo '<input type="hidden" name="page" value="'.$page.'"></td></tr>
<tr><td colspan="4"><hr/></td></table>'; }
echo "<table border='0' id='TableWrapper2'>";
$pagegesamt = mysql_num_rows($result1);
echo "<tr class='head'><td colspan='6' align='center'><b>".$gesamt;
if($gesamt == 1) {echo " entry "; } else { echo " entries ";}
echo "found for:</b>";
foreach($descriptionall as $index => $descriptiontest)
{ $valuetest = $valueall[$index];
echo $descriptiontest."[<b>".$valuetest."</b>]";
if ($formNCBI != "" or ($_COOKIE['formNCBI'] != "" and $_GET['hitlist'] == "true"))
{ $NCBI_Test = "true";
$NCBI_Number = $valuetest; } }
echo ". ";
if($NCBI_Test == "true")
{ echo " <a class='ncbi' href='http://www.ncbi.nlm.nih.gov/Taxonomy/Browser/wwwtax.cgi?id=".$NCBI_Number."' target='_blank'>Check ID in <sub><img src='http://www.ncbi.nlm.nih.gov/corehtml/left.GIF' height='20px' border='0'></sub></a>"; }
echo "</td></tr>";
include("pages.php");
echo "<tr><td colspan='7'><hr /></td></tr>";
$Dnacount=0;
echo "<th></th><th class='head'>Species</th><th class='head'>Family</th><th class='head'>Country</th>";
echo "<th class='head'>DNA Bank</th><th class='head'>DNA No.</th><th class='head'>Specimen No.</th>";
if(isset($_SESSION['login']) or $_SESSION['login'])
{ echo "<th><img border='0' height='20px' src='images/warenkorb.jpg'></th>"; }
for ($i=1; $i<=$pagegesamt; $i++) {
$row=mysql_fetch_array($result1);
{
$Dnacount++;
if($Dnacount<=$pagesize)
{
$ID = $row['ID_Cache'];
if(empty($row['UnitID_DNA'])) {
$UnitIDDNA = "Unknown"; }
else {$UnitIDDNA = $row['UnitID_DNA']; }
if(empty($row['CollectionCode_DNA'])) {
$CollCodeDNA = "Unknown"; }
else {$CollCodeDNA = $row['CollectionCode_DNA']; }
$UnitIDS = $row['UnitID_Specimen'];
$CollCodeS = $row['CollectionCode_Specimen'];
$InstCodeS = $row['InstitutionCode_Specimen'];
$InstCodeDNA = $row['InstitutionCode_DNA'];
$CountryName = $row['CountryName'];
$Blocked_Until = $row['Blocked_Until'];
echo "<tr style='border-top-width:1px;border-top-style:solid;border-color:#CCCCCC'><td width='40%' valign='top' colspan='2'><b>";
if((!isset($_COOKIE['test']) or !$_COOKIE['test']) and !isset($_GET['UnitIDS']))
{ echo $NameAuthorYear[$row['ID_Cache']]; }
if(empty($row['UnitID_DNA']) and (isset($_COOKIE['test']) or $_COOKIE['test'] or isset($_GET['UnitIDS'])))
{
$LinkSpecimen = "sqlType=Detail&UnitID=Unknown&CollCode=Unknown&InstCode=$InstCodeDNA&UnitIDS=$UnitIDS&CollCodeS=$CollCodeS&InstCodeS=$InstCodeS&ID_Cache=$ID"; }
if(!empty($row['UnitID_DNA']) and (isset($_COOKIE['test']) or $_COOKIE['test'] or isset($_GET['UnitIDS'])))
{
$LinkSpecimen = "sqlType=Detail&UnitID=$UnitIDDNA&CollCode=$CollCodeDNA&InstCode=$InstCodeDNA&UnitIDS=$UnitIDS&CollCodeS=$CollCodeS&InstCodeS=$InstCodeS&ID_Cache=$ID"; }
if($_GET['page'] > 1) { $LinkSpecimen .= "&Page=".$_GET['page']; }
echo build_hrefhitlist("Query.php", $LinkSpecimen, "<img src='images/Zoom.jpg' border='0'> ".$NameAuthorYear[$row['ID_Cache']]);
echo "</td><td valign='top'>".$Family[$row['ID_Cache']]."</td><td valign='top'>".$CountryName."</td><td valign='top'> " .
$row['InstitutionCode_DNA'] . "</td><td valign='top'>" .
$row['UnitID_DNA'] . "</b></td><td valign='top'>".$row['UnitID_Specimen']."</td>";
if(isset($_SESSION['login']) or $_SESSION['login'])
{
if($Blocked_Until == "" OR $Blocked_Until == "0000-00-00 00:00:00") {
echo "<td valign='top'><input type='checkbox' name='check[".$i."]' value='yes'><input type='hidden' name='cache[".$i."]' value='".$ID."'></td>";}
if($Blocked_Until != "" AND $Blocked_Until != "0000-00-00 00:00:00") {
echo "<td valign='top'><span id='error'>X</span></td>";}
}
echo "</tr>";
}
}
}
echo "<tr><td colspan='7'><hr /></td></tr>";
include("pages.php");
echo "</table>";
echo "</form>";
}
/*******************************************************************************************************
* Zusammenbauen der Ergebnisse Detailansicht, Wrapper-Abfragen für Specimen und DNA-Daten *
* *
*******************************************************************************************************/
if($sqlType=="Detail")
{
while($row = mysql_fetch_object($result1))
{
echo "<td colspan='2' valign='top'>";
echo "<table id='TableWrapper2'><tr><td><a class='hitlist2' href='Query.php'>New search</a></td>";
echo "<td><a class='hitlist2' href='Query.php?back=true'>Change search</a></td>";
echo "<td><a class='hitlist2' href='Query.php?hitlist=true";
if($_GET['Page'] != "") { echo "&page=".$_GET['Page']; }
echo "' name='formBackToHitlist'>Back to hitlist</a>";
echo "</td>";
if(!isset($_SESSION['login']) or !$_SESSION['login'])
{
echo '<td align="right"><a class="orange" href="Login.php?check=query&sqlType=Detail&ID_Cache='.$_REQUEST["ID_Cache"].'">Please login to add DNA samples to </a></td><td><img border="0" height="35px" src="images/warenkorb.jpg">.</td></tr>
<tr><td colspan="5"><hr/></td></tr></table>'; }
if(isset($_SESSION['login']) or $_SESSION['login'])
{
if($row->Blocked_Until == "")
{echo '<td align="right"><button type="submit" name="formSubmitShop" value="Yes" id="buttonshop">Add DNA sample to </td><td><img border="0" height="35px" src="images/warenkorb.jpg"></button><td></tr>
<tr><td colspan="5"><hr/></td></tr></table>';}
if($row->Blocked_Until != "")
{echo '<td align="right"><span class="blocked">Sorry, this DNA sample is blocked and can not be ordered until '.$row->Blocked_Until.'.</span><br/>
Please contact the <a class="link" href="mailto:'.$ContactEmail.'">'.$row->InstitutionCode_DNA.'</a> for further details!</td></tr>
<tr><td colspan="4"><hr/></td></tr></table>';}
}
$UnitID_Specimen = $row->UnitID_Specimen;
$CollectionCode_Specimen = $row->CollectionCode_Specimen;
$InstitutionCode_Specimen = $row->InstitutionCode_Specimen;
$UnitID_DNA = $row->UnitID_DNA;
$CollectionCode_DNA = $row->CollectionCode_DNA;
$InstitutionCode_DNA = $row->InstitutionCode_DNA;
$ID = $row->ID_Cache;
$UUID = $row->Uuid;
}
$Digir_Resource1 = urlencode($Digir_Resource);
$Digir_Source1 = urlencode($Digir_Source);
$Specimen_UnitID = urlencode($UnitID_Specimen);
$Specimen_CollectionCode = urlencode($CollectionCode_Specimen);
$Specimen_InstitutionCode = urlencode($InstitutionCode_Specimen);
$Digir_Resource12 = urlencode($Digir_Resource2);
$Digir_Source12 = urlencode($Digir_Source2);
$DNA_UnitID = urlencode($UnitID_DNA);
$DNA_CollectionCode = urlencode($CollectionCode_DNA);
$DNA_InstitutionCode = urlencode($InstitutionCode_DNA);
$sqllogdetails = "INSERT INTO tablelogdetails (UnitID_Specimen, CollectionCode_Specimen, InstitutionCode_Specimen, UnitID_DNA, CollectionCode_DNA, InstitutionCode_DNA) VALUES ('$UnitID_Specimen','$CollectionCode_Specimen','$InstitutionCode_Specimen','$UnitID_DNA','$CollectionCode_DNA','$InstitutionCode_DNA')";
$resultlogdetails = mysql_query($sqllogdetails);
$file1 = $Provider.$QueryPart1b.$Digir_Resource1.$QueryPart1_1.$Digir_Source1.$QueryPart1_2b.$Specimen_UnitID.$QueryPart3.$Specimen_InstitutionCode.$QueryPart4.$Specimen_CollectionCode.$QueryPart5;
//echo $file1;
if($DNA_UnitID != "")
{$file2 = $Provider2.$QueryPart1b2.$Digir_Resource12.$QueryPart1_12.$Digir_Source12.$QueryPart1_2b2.$DNA_UnitID.$QueryPart32.$DNA_InstitutionCode.$QueryPart42.$DNA_CollectionCode.$QueryPart52; }
//echo $file2;
echo "<table border='0' id='TableWrapper'>";
echo "<tr><td style='border-top-width:1px; border-top-style:solid; border-color: black;'>";
echo "<input type='hidden' name='cachedetail' value='".$ID."'>";
if($DNA_UnitID != "") { include ("verify2.php"); }
if($DNA_UnitID == "") { echo "<table id='ondemand'><tr><td>On-demand DNA extraction is possible for this specimen. Please use the order function to it. Your on-demand extraction request will then be forwarded to the <b>".$InstitutionCode_DNA. "</b>.</td></tr></table>"; }
echo "</td></tr>";
echo "<tr><td><hr size='1' style='border-top-width:0.5px; border-top-style:solid; border-color: black;'/></td></tr>";
echo "<tr><td><input type='hidden' name='hallo123' value='".$ResourceKey."'/></td></tr>";
echo "<tr><td>";
if($ID_Schema == 1) {
$format = urlencode("http://www.tdwg.org/schemas/abcd/1.2");
$schema = urlencode("abcd1.2"); }
if($ID_Schema == 2) {
$format = urlencode("http://www.tdwg.org/schemas/abcd/2.06");
$schema = urlencode("abcd2.06"); }
if($ID_Schema == 3) {
$format = urlencode("http://digir.net/schema/conceptual/darwin/2003/1.0");
$schema = urlencode("http://digir.net/schema/conceptual/darwin/2003/1.0"); }
if($ID_Schema == 4) {
$format = urlencode("http://www.tdwg.org/schemas/abcd/2.05");
$schema = urlencode("http://www.tdwg.org/schemas/abcd/2.05"); }
echo "</form>";
if($Uuid != "") {
$file3 = "http://search.biocase.org/annotation/all/".$Uuid."?instid=".$Specimen_InstitutionCode."&format=".$format."&collid=".$Specimen_CollectionCode."&unitid=".$Specimen_UnitID; }
if($_GET['anno'] == "true")
{ echo "<h1>Annotations for these specimen data:</h1></td></tr><tr><td align='center'><table class='tableanno'>";
echo "<th>Date</th><th>Title</th><th>Author</th><th>Schema</th><th><img src='images/Zoom.jpg' border='0'></th></th>"; }
include("CheckAnnotation.php");
if($_GET['anno'] == "true")
{ echo "</table></td></tr>";
echo "<tr><td><hr size='1' style='border-top-width:0.5px; border-top-style:solid; border-color: black;'/></td></tr><tr><td>"; }
if($testcurrent == "true" and $_GET['anno'] != "true" and empty($_GET['link']))
{ echo "</td></tr><tr><td><span class='anno'>For these specimen data annotations are available. <a href='";
echo "Query.php?sqlType=Detail&UnitID=".$_GET['UnitID']."&CollCode=".$_GET['CollCode']."&InstCode=".$_GET['InstCode']."&UnitIDS=".$_GET['UnitIDS']."&CollCodeS=".$_GET['CollCodeS']."&InstCodeS=".$_GET['InstCodeS']."&anno=true";
if($_GET['Page'] != "")
{ echo "&Page=".$_GET['Page']; }
echo "' class='anno'>See annotations</a></span></td></tr><tr><td>"; }
$implode = "http://search.biocase.org/world/search/units/annotation/compareHTMLonly?url=";
$implode .= urlencode($_GET['link']);
$implode .= "&schema=".$format;
// $implode .= "&schema=http%3A%2F%2Fwww.tdwg.org%2Fschemas%2Fabcd%2F1.2";
//echo $implode;
echo "<tr><td style='border-bottom-width:1px; border-bottom-style: solid; border-color: black;'>";
if($_GET['link'] != "") {
echo "<a href='";
echo "Query.php?sqlType=Detail&UnitID=".$_GET['UnitID']."&CollCode=".$_GET['CollCode']."&InstCode=".$_GET['InstCode']."&UnitIDS=".$_GET['UnitIDS']."&CollCodeS=".$_GET['CollCodeS']."&InstCodeS=".$_GET['InstCodeS'];
if($_GET['Page'] != "")
{ echo "&Page=".$_GET['Page']; }
echo "' class='anno'>See original specimen data from provider.</a><br/>";
$Anno = implode('', file($implode));
echo '<div id="compareDiv">'.utf8_decode($Anno).'</div>'; }
if($_GET['link'] == "") {
include ("verify.php"); }
echo "</td></tr>";
echo "</table>";
} // if($SID)
}
}
}
/*******************************************************************************************************
* Zusammenbauen der Ergebnisse Warenkorb *
* *
*******************************************************************************************************/
if(($formSubmitShopDetail or $formSubmitShopDelete) and $sqlType=="Shop")
{
if(!empty($result1)) {
$gesamt = mysql_num_rows($result1); }
echo "<td valign='top' colspan='2'><h2><i>Your Shopping Cart</i></h2>";
if($gesamt != 0) {
if(!isset($_COOKIE['test']) or !$_COOKIE['test']) {
echo "<b>Please enable Cookies! Otherwise search will not work properly.</b><br/>";}
echo "<table id='TableWrapper2'><tr><td>";
echo "<a class='hitlist2' href='Query.php'>New search</a></td>";
echo "<td><a class='hitlist2' href='Query.php?back=true'>Change search</a>";
echo "</td>";
echo '<td align="right"><button type="submit" name="formSubmitShopDelete" value="Yes" id="buttonshop2"><img border="0" src="images/bin.gif"> Delete selected DNA samples.</button></td></tr>
<tr><td colspan="3"><hr/></td></tr></table>'; }
echo "<table border='0' id='TableWrapper2'>";
if($gesamt != 0) {
echo "<tr class='head'><td colspan='7' align='center'><b>You have ".$gesamt;
if($gesamt == 1) {echo " item "; } else { echo " items ";}
echo "in your shopping cart.</b></td></tr>"; }
if($gesamt == 0)
{ echo "<tr class='head'><td colspan='7' align='center'><b>You have no items in your shopping cart.</b></td></tr>";}
if($gesamt != 0) {
echo "<tr><td colspan='7'><hr /></td></tr>";
$Dnacount=0;
echo "<th></th><th class='head'>Species</th><th class='head'>Family</th><th class='head'>Country</th><th class='head'>DNA Bank</th><th class='head'>DNA No.</th><th class='head'>Specimen No.</th><th><img border='0' src='images/bin.gif'></th>"; }
for ($i=1; $i<=$gesamt; $i++) {
$row=mysql_fetch_array($result1);
{
$Dnacount++;
if($Dnacount<=$pagesize)
{
$ID = $row['ID_Cache'];
if(empty($row['UnitID_DNA'])) {
$UnitIDDNA = "Unknown"; }
else {$UnitIDDNA = $row['UnitID_DNA']; }
if(empty($row['CollectionCode_DNA'])) {
$CollCodeDNA = "Unknown"; }
else {$CollCodeDNA = $row['CollectionCode_DNA']; }
$UnitIDS = $row['UnitID_Specimen'];
$CollCodeS = $row['CollectionCode_Specimen'];
$InstCodeS = $row['InstitutionCode_Specimen'];
$InstCodeDNA = $row['InstitutionCode_DNA'];
$CountryName = $row['CountryName'];
echo "<tr style='border-top-width:1px;border-top-style:solid;border-color:#CCCCCC'><td width='40%' valign='top' colspan='2'><b>";
if(!isset($_COOKIE['test']) or !$_COOKIE['test'])
{ echo $NameAuthorYear[$row['ID_Cache']]; }
if(empty($row['UnitID_DNA']) and (isset($_COOKIE['test']) or $_COOKIE['test']))
{
echo build_hrefhitlist("Query.php",
"sqlType=Detail&UnitID=Unknown&CollCode=Unknown&InstCode=$InstCodeDNA&UnitIDS=$UnitIDS&CollCodeS=$CollCodeS&InstCodeS=$InstC odeS",
"<img src='images/Zoom.jpg' border='0'> ".$NameAuthorYear[$row['ID_Cache']]); }
if(!empty($row['UnitID_DNA']) and (isset($_COOKIE['test']) or $_COOKIE['test'])) {
echo build_hrefhitlist("Query.php",
"sqlType=Detail&UnitID=$UnitIDDNA&CollCode=$CollCodeDNA&InstCode=$InstCodeDNA&UnitIDS=$UnitIDS&CollCodeS=$CollCodeS&InstCodeS=$InstCodeS",
"<img src='images/Zoom.jpg' border='0'> ".$NameAuthorYear[$row['ID_Cache']]); }
echo "</td><td valign='top'>".$Family[$row['ID_Cache']]."</td><td valign='top'>".$CountryName."</td><td valign='top'> " .
$row['InstitutionCode_DNA'] . "</td><td valign='top'>" .
$row['UnitID_DNA'] . "</b></td><td valign='top'>".$row['UnitID_Specimen']."</td><td valign='top'><input type='checkbox' name='check[".$i."]' value='yes'><input type='hidden' name='cache[".$i."]' value='".$ID."'></td></tr>";
}
} }
if($gesamt != 0) {
echo '<tr><td colspan="6" align="right"><br><br><input type="submit" name="formSubmitOrder" value="Continue Order (->Step 2/3)" id="buttonorder"></td></tr>'; }
echo "</table>";
echo "</form>";
}
?>
</td>
</tr>
<tr>
<td></td><td colspan="2"><?php
include ("TableImpressum.php"); ?></td></tr>
</table>
<?php include ("Piwik.js"); ?>
</body>
</html>