File Source for query

<?php
/**
* Data Cleaning query
* @author Gabriele Droege, DNA Bank Network <contact@dnabank-network.org>
* @version 2.0
* @package Data_Cleaning
* @copyright Copyright © 2011 DNA Bank Network http://www.dnabank-network.org<br>The contents of this file are subject to the Mozilla Public License Version 1.1
* @filesource
* @license http://www.mozilla.org/MPL/ MPL
*/
/**
* calling connection to database
*/
include ("../config/head.php");
/**
* calling authorisation script
*/
include ("../auth.php");
/********************************************************************************************
* Setting cookies after submitting search query *
*********************************************************************************************/
if($formSubmitSearch)
{setcookie("formSpecimenNumber", "");
setcookie("formProviderIntern", "");
setcookie("formProviderExtern", "");
setcookie("formHigherTaxon", "");
setcookie("formTaxonName", "");
setcookie("formGenus", "");
setcookie("formSpecificEpithet", "");
setcookie("formCountryList", "");
setcookie("formCountryListISO", "");
setcookie("formContinent", "");
setcookie("formLocality", "");
setcookie("formLatFrom", "");
setcookie("formLatTo", "");
setcookie("formLongFrom", "");
setcookie("formLongTo", "");
setcookie("formDnaBankNumber", "");
setcookie("formRelation", "");
setcookie("formType", "");
setcookie("formPreservation", "");
setcookie("formTissue", "");
setcookie("formExtractionMethod", "");
setcookie("formExtractionDateFrom", "");
setcookie("formExtractionDateTo", "");
setcookie("formExtractionStaff", "");
setcookie("formPurificationMethod", "");
setcookie("formAbsorbance280From", "");
setcookie("formAbsorbance280To", "");
setcookie("formAbsorbance230From", "");
setcookie("formAbsorbance230To", "");
setcookie("formConcentrationFrom", "");
setcookie("formConcentrationTo", "");
setcookie("formDegradation", "");
setcookie("formCheckDateFrom", "");
setcookie("formCheckDateTo", "");
setcookie("formNumberAliquots", "");
setcookie("formQuantityAliquotsFrom", "");
setcookie("formQuantityAliquotsTo", "");
setcookie("formGeneticLocus", "");
setcookie("formGenBankNumber", "");
setcookie("formGenBankNumberA", "");
setcookie("formGenBankNumberB", "");
setcookie("formGenBankNumberC", "");
setcookie("formGenBankNumberD", "");
setcookie("formGenBankLink", "");
setcookie("formGenBankLinkA", "");
setcookie("formGenBankLinkB", "");
setcookie("formGenBankLinkC", "");
setcookie("formGenBankLinkD", "");
setcookie("radioGenBank", "");
setcookie("formStockFridge", "");
setcookie("formStockRack", "");
setcookie("formStockBox", "");
setcookie("formProvider", "");
setcookie("formAliquotsFridge", "");
setcookie("formAliquotsRack", "");
setcookie("formAliquotsBox", "");
setcookie("radioSource", "");
setcookie("radioStock", "");
setcookie("radioBackup", "");
setcookie("formBlockDateFrom", "");
setcookie("formBlockDateTo", "");
setcookie("formBlockGeneral", "");
setcookie("formRequest", "");
setcookie("formNotes", "");
setcookie("formBarcodeAliquot", "");
setcookie("formPositionAliquot", "");
setcookie("formBarcodeStock", "");
setcookie("formPositionStock", "");
setcookie("formSpecimenNumber", $formSpecimenNumber);
setcookie("formProviderIntern", $formProviderIntern);
setcookie("formProviderExtern", $formProviderExtern);
setcookie("formHigherTaxon", $formHigherTaxon);
setcookie("formTaxonName", $formTaxonName);
setcookie("formGenus", $formGenus);
setcookie("formSpecificEpithet", $formSpecificEpithet);
setcookie("formCountryList", $formCountryList);
setcookie("formCountryListISO", $formCountryListISO);
setcookie("formContinent", $formContinent);
setcookie("formLocality", $formLocality);
setcookie("formLatFrom", $formLatFrom);
setcookie("formLatTo", $formLatTo);
setcookie("formLongFrom", $formLongFrom);
setcookie("formLongTo", $formLongTo);
setcookie("formDnaBankNumber", $formDnaBankNumber);
setcookie("formRelation", $formRelation);
setcookie("formType", $formType);
setcookie("formPreservation", $formPreservation);
setcookie("formTissue", $formTissue);
setcookie("formExtractionMethod", $formExtractionMethod);
setcookie("formExtractionDateFrom", $formExtractionDateFrom);
setcookie("formExtractionDateTo", $formExtractionDateTo);
setcookie("formExtractionStaff", $formExtractionStaff);
setcookie("formPurificationMethod", $formPurificationMethod);
setcookie("formAbsorbance280From", $formAbsorbance280From);
setcookie("formAbsorbance280To", $formAbsorbance280To);
setcookie("formAbsorbance230From", $formAbsorbance230From);
setcookie("formAbsorbance230To", $formAbsorbance230To);
setcookie("formConcentrationFrom", $formConcentrationFrom);
setcookie("formConcentrationTo", $formConcentrationTo);
setcookie("formDegradation", $formDegradation);
setcookie("formCheckDateFrom", $formCheckDateFrom);
setcookie("formCheckDateTo", $formCheckDateTo);
setcookie("formNumberAliquots", $formNumberAliquots);
setcookie("formQuantityAliquotsFrom", $formQuantityAliquotsFrom);
setcookie("formQuantityAliquotsTo", $formQuantityAliquotsTo);
setcookie("formGeneticLocus", $formGeneticLocus);
setcookie("formGenBankNumber", $formGenBankNumber);
setcookie("formGenBankNumberA", $formGenBankNumberA);
setcookie("formGenBankNumberB", $formGenBankNumberB);
setcookie("formGenBankNumberC", $formGenBankNumberC);
setcookie("formGenBankNumberD", $formGenBankNumberD);
setcookie("formGenBankLink", $formGenBankLink);
setcookie("formGenBankLinkA", $formGenBankLinkA);
setcookie("formGenBankLinkB", $formGenBankLinkB);
setcookie("formGenBankLinkC", $formGenBankLinkC);
setcookie("formGenBankLinkD", $formGenBankLinkD);
setcookie("radioGenBank", $radioGenBank);
setcookie("formStockFridge", $formStockFridge);
setcookie("formStockRack", $formStockRack);
setcookie("formStockBox", $formStockBox);
setcookie("formProvider", $formProvider);
setcookie("formAliquotsFridge", $formAliquotsFridge);
setcookie("formAliquotsRack", $formAliquotsRack);
setcookie("formAliquotsBox", $formAliquotsBox);
setcookie("radioSource", $radioSource);
setcookie("radioStock", $radioStock);
setcookie("radioBackup", $radioBackup);
setcookie("formBlockDateFrom", $formBlockDateFrom);
setcookie("formBlockDateTo", $formBlockDateTo);
setcookie("formBlockGeneral", $formBlockGeneral);
setcookie("formRequest", $formRequest);
setcookie("formNotes", $formNotes);
setcookie("formBarcodeAliquot", $formBarcodeAliquot);
setcookie("formPositionAliquot", $formPositionAliquot);
setcookie("formBarcodeStock", $formBarcodeStock);
setcookie("formPositionStock", $formBarcodeAliquot);
}
?>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>DNA Module - Data Cleaning Results</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
/**
* including navigation menu
*/
include("../tablenavi.php"); ?>
<table id="tablemain" border="0" class="search">
<tr>
<td id="tdLogo"><img border="0" src="../images/Logo-DNA-Bank-Network.png" width="123" height="78"></td>
<td id="tdtitle">
<?php if ($formSubmitUpdate or $formSubmitVerify) { echo "<h1>Edit Dataset</h1>"; }
else { echo "<h1>Search Results</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>
</tr>
<form name="formular" method="POST" action="query_clean.php?ID_DNA=<?php echo $ID_DNA; ?>">
<tr>
<td colspan="3" valign="top" align="center">
<?php
/********************************************************************************************
* Number of records per site *
*********************************************************************************************/
if($recordspage != "") {
$pagesize = $recordspage; }
else {$pagesize = 50; }
$page = $_GET['page'];
if(!$page || $page<1 || !is_numeric($page))
{ $page = 1; }
elseif($page<1)
$page = 1;
elseif(!is_numeric($page))
unset($page);
/********************************************************************************************
* Create temporary table for hitlist *
*********************************************************************************************/
$sql = "";
$sqlcreate = "CREATE TEMPORARY TABLE tmpDnaIDs AS ";
if($_GET['page']!= "")
$sqllimit = "LIMIT " . (($page-1) * $pagesize) . "," . ($pagesize + 1);
else
$sqllimit = "LIMIT " . ($pagesize + 1);
if($formSubmitUpdate or $formSubmitVerify)
{
/**
* during editing process calling this script
*/
include("update_clean.php"); }
else { // if(!$formSubmitUpdate and !$formSubmitVerify)
/********************************************************************************************
* building search query *
* START *
*********************************************************************************************/
/********************************************************************************************
* $_REQUEST['sort'] is defined in lines 397-407, user can sort hitlist by several parameters*
*********************************************************************************************/
if($formSubmitSearch or $_REQUEST['hitlist'] == "true" or $_REQUEST['sort'] != "")
{
$tr = "=";
$no = "!=";
$lk = "like";
$bt = ">=";
$st = "<=";
if($_REQUEST['hitlist'] == "true")
{$parr = $_COOKIE;}
if($_REQUEST['hitlist'] == "")
{$parr = $_POST; }
array($parr);
foreach ($parr as $varorigin)
{ $var = addslashes($varorigin);
$i ++;
if(!empty($var) and $var!="Search" and $var != $DNANo and key($parr)!="PHPSESSID")
{
switch (key($parr))
{
case 'formDnaBankNumber':
$str = "(dnabanknumbers.DNA_Bank_Number = '' OR dnabanknumbers.DNA_Bank_Number IS NULL) OR ";
$description = " DNA Extraction No = ";
$value = "empty";
break;
case 'formRelation':
$str = "(dnabanknumbers.ID_RelationDNA_Voucher = '0' OR dnabanknumbers.ID_RelationDNA_Voucher = '' OR dnabanknumbers.ID_RelationDNA_Voucher IS NULL) OR ";
$description = " Relation to Voucher = ";
$value = "empty";
break;
case 'formNoVoucherData':
$str = "dnabanknumbers.ID_Cache NOT IN (SELECT ID_Cache FROM cachecollection) OR ";
$description = " DNA samples ";
$value = "without voucher data";
break;
case 'formType':
$str = "(dnabanknumbers.ID_Type = '0' OR dnabanknumbers.ID_Tissue = '' OR dnabanknumbers.ID_Tissue IS NULL) OR ";
$description = " DNA Type = ";
$value = "empty";
break;
case 'formPreservation':
$str = "(dnabanknumbers.ID_Preservation = '0' OR dnabanknumbers.ID_Preservation = '' OR dnabanknumbers.ID_Preservation IS NULL) OR ";
$description = " Preservation = ";
$value = "empty";
break;
case 'formTissue':
$str = "(dnabanknumbers.ID_Tissue = '0' OR dnabanknumbers.ID_Tissue = '' OR dnabanknumbers.ID_Tissue IS NULL) OR ";
$description = " Tissue = ";
$value = "empty";
break;
case 'formExtractionMethod':
$str = "(dnabanknumbers.ID_Extraction_Method = '0' OR dnabanknumbers.ID_Extraction_Method = '' OR dnabanknumbers.ID_Extraction_Method IS NULL) AND ";
$description = " Extraction Method = ";
$value = "empty";
break;
case 'formExtractionDate':
$str = "(dnabanknumbers.Extraction_Date IS NULL OR dnabanknumbers.Extraction_Date = '') OR ";
$description = " Extraction Date = ";
$value = "empty";
break;
case 'formExtractionStaff':
$str = "dnabanknumbers.ID_Extraction_Staff = '0' OR ";
$description = " Extraction Staff = ";
$value = "empty";
break;
case 'formPurificationMethod':
$str = "dnabanknumbers.ID_Purification = '0' OR ";
$description = " Purification Method = ";
$value = "empty";
break;
case 'formAbsorbance280':
$str = "(dnabanknumbers.Absorbance280 IS NULL OR dnabanknumbers.Absorbance280 = '') OR ";
$description = " Absorbance 280/260 = ";
$value = "empty";
break;
case 'formAbsorbance230':
$str = "(dnabanknumbers.Absorbance230 IS NULL OR dnabanknumbers.Absorbance230 = '') OR ";
$description = " Absorbance 230/260 = ";
$value = "empty";
break;
case 'formConcentration':
$str = "(dnabanknumbers.Concentration IS NULL OR dnabanknumbers.Concentration = '') OR ";
$description = " Concentration = ";
$value = "empty";
break;
case 'formDegradation':
$str = "(dnabanknumbers.ID_Degradation = '0' OR dnabanknumbers.ID_Degradation = '' OR dnabanknumbers.ID_Degradation IS NULL) OR ";
$description = " Quality = ";
$value = "empty";
break;
case 'formCheckDate':
$str = "(dnabanknumbers.Check_Date IS NULL OR dnabanknumbers.Check_Date = '') OR ";
$description = " Quality Check Date = ";
$value = "empty";
break;
case 'formStock':
$str = "(dnabanknumbers.ID_Location_Stock = '0' OR dnabanknumbers.ID_Location_Stock = '' OR dnabanknumbers.ID_Location_Stock IS NULL) OR ";
$description = " Stock Fridge = ";
$value = "empty";
break;
}
$where[$i] = $str;
$descriptionall[$i] = $description;
$valueall[$i] = $value;
}
next($parr);
}
if(empty($str))
{ echo "<table border='0' id='tablesearch'><tr><td colspan='5'><b>No search key entered.</b></td></tr>";
echo "<tr><td colspan='5'><a class='red' href=\"data_cleaning.php\">Back to Data Cleaning Overwiew.</a></td></tr></table>"; }
else {
foreach($where AS $a) $string .= $a;
$l=strlen ($string);
$max = $l-3;
$where_str = substr($string, 0, $max);
$wherestr = ' WHERE ' . $where_str;
/********************************************************************************************
* Further restriction for members of group "Guest" *
* Guests don't have access to blocked samples *
*********************************************************************************************/
if (isset($_SESSION['guest']) || $_SESSION['guest']) {
$wherestr .= ' AND dnabanknumbers.Block_General = "0"'; }
/********************************************************************************************
* Further restriction for members of group "Project" *
* Project members don't have access to blocked samples that are not created by themselves *
*********************************************************************************************/
if (isset($_SESSION['project']) || $_SESSION['project']) {
$wherestr .= " AND ((dnabanknumbers.Created_Who != '".$log."' AND dnabanknumbers.Block_General = '0') OR dnabanknumbers.Created_Who = '".$log."') "; }
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
/********************************************************************************************
* Sorting query *
*********************************************************************************************/
if($_REQUEST['sort'] == "DU" or $_REQUEST['sort'] == "" or $Sorting == "") { $OrderBy = " ORDER BY LENGTH(dnabanknumbers.Dna_Bank_Number),dnabanknumbers.DNA_Bank_Number "; }
if($_REQUEST['sort'] == "DD") { $OrderBy = " ORDER BY LENGTH(dnabanknumbers.Dna_Bank_Number),dnabanknumbers.DNA_Bank_Number DESC "; }
if($_REQUEST['sort'] == "SU") { $OrderBy = " ORDER BY cachecollection.UnitID_Specimen "; }
if($_REQUEST['sort'] == "SD") { $OrderBy = " ORDER BY cachecollection.UnitID_Specimen DESC "; }
if($_REQUEST['sort'] == "CU") { $OrderBy = " ORDER BY cachecollection.CollectionCode_Specimen "; }
if($_REQUEST['sort'] == "CD") { $OrderBy = " ORDER BY cachecollection.CollectionCode_Specimen DESC "; }
if($_REQUEST['sort'] == "IU") { $OrderBy = " ORDER BY cachecollection.InstitutionCode_Specimen "; }
if($_REQUEST['sort'] == "ID") { $OrderBy = " ORDER BY cachecollection.InstitutionCode_Specimen DESC "; }
if($_REQUEST['sort'] == "TU") { $OrderBy = " ORDER BY cachetaxonidentified.NameAuthorYear "; }
if($_REQUEST['sort'] == "TD") { $OrderBy = " ORDER BY cachetaxonidentified.NameAuthorYear DESC "; }
/********************************************************************************************
* building main query for hitlist ($sqlType == "Dna") *
*********************************************************************************************/
$sqlType = "Dna";
$sql = $sqlcreate .
$sql = "SELECT DISTINCT dnabanknumbers.ID_DNA FROM dnabanknumbers
LEFT JOIN cachecollection ON dnabanknumbers.ID_Cache = cachecollection.ID_Cache
LEFT JOIN cachetaxonidentified ON cachecollection.ID_Cache = cachetaxonidentified.ID_Cache
LEFT JOIN cachehighertaxa ON cachetaxonidentified.ID_TaxonIdentified = cachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN amplifications ON dnabanknumbers.ID_DNA = amplifications.ID_DNA
LEFT JOIN location_stock ON location_stock.ID_Location_Stock = dnabanknumbers.ID_Location_Stock
LEFT JOIN aliquots ON dnabanknumbers.ID_DNA = aliquots.ID_DNA
LEFT JOIN request ON dnabanknumbers.ID_DNA = request.ID_DNA " .
$wherestr.$OrderBy.$sqllimit;
$result = mysql_query($sql);
/********************************************************************************************
* building main query for counting records *
*********************************************************************************************/
$sqlcount = "SELECT DISTINCT dnabanknumbers.ID_DNA FROM dnabanknumbers
LEFT JOIN cachecollection ON dnabanknumbers.ID_Cache = cachecollection.ID_Cache
LEFT JOIN cachetaxonidentified ON cachecollection.ID_Cache = cachetaxonidentified.ID_Cache
LEFT JOIN cachehighertaxa ON cachetaxonidentified.ID_TaxonIdentified = cachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN amplifications ON dnabanknumbers.ID_DNA = amplifications.ID_DNA
LEFT JOIN location_stock ON location_stock.ID_Location_Stock = dnabanknumbers.ID_Location_Stock
LEFT JOIN aliquots ON dnabanknumbers.ID_DNA = aliquots.ID_DNA
LEFT JOIN request ON dnabanknumbers.ID_DNA = request.ID_DNA " .
$wherestr;
$resultcount = mysql_query($sqlcount);
$sum = mysql_num_rows($resultcount);
} } //if($formSubmitSearch)
/********************************************************************************************
* building search query *
* Code for "Show Hitlist" see line 1596 *
* Code for "Building main query for details of single record" see line 1148 *
* Code for "Show details of single record" see line 1709 *
* END *
*********************************************************************************************/
else
{
/********************************************************************************************
* Saving edited record *
* 1. insert old record into _log_-tables *
* 2. update tables with new parameters *
* 3. show details of record again *
* record is locked for editing when another user is editing the same record at the same time*
* START *
*********************************************************************************************/
if($formSubmitSave)
{
$Current_Date = date('Y-m-d H:i:s');
$result2a = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" people.Name_All " .
" FROM dnabanknumbers, people " .
" WHERE dnabanknumbers.ID_Extraction_Staff = people.ID_People " .
" AND dnabanknumbers.ID_DNA = '$ID_DNA'");
while($row = mysql_fetch_object($result2a))
{ $Extraction_Staff_old = $row->Name_All; }
$result3a = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" people.Name_All " .
" FROM dnabanknumbers, people " .
" WHERE dnabanknumbers.ID_Provided_By_People = people.ID_People " .
" AND dnabanknumbers.ID_DNA = '$ID_DNA'");
while($row = mysql_fetch_object($result3a))
{ $Provided_By_People_old = $row->Name_All; }
$result4a = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" stock_fridge.Stock_Fridge, " .
" stock_rack.Stock_Rack, " .
" stock_box.Stock_Box, " .
" location_stock.ID_Stock_Fridge, " .
" location_stock.ID_Stock_Rack, " .
" location_stock.ID_Stock_Box, " .
" location_stock.Origin_Quantity, " .
" location_stock.Rest_Quantity, " .
" location_stock.Stock_Barcode, " .
" location_stock.Stock_Position " .
" FROM dnabanknumbers " .
" LEFT JOIN location_stock ON dnabanknumbers.ID_Location_Stock = location_stock.ID_Location_Stock " .
" LEFT JOIN stock_fridge ON location_stock.ID_Stock_Fridge = stock_fridge.ID_Stock_Fridge " .
" LEFT JOIN stock_rack ON location_stock.ID_Stock_Rack = stock_rack.ID_Stock_Rack " .
" LEFT JOIN stock_box ON location_stock.ID_Stock_Box = stock_box.ID_Stock_Box " .
" WHERE dnabanknumbers.ID_DNA = '$ID_DNA'");
while($row = mysql_fetch_object($result4a))
{ $Stock_Fridge = $row->Stock_Fridge;
$Stock_Rack = $row->Stock_Rack;
$Stock_Box = $row->Stock_Box;
$ID_Stock_Fridge_old = $row->ID_Stock_Fridge;
$ID_Stock_Rack_old = $row->ID_Stock_Rack;
$ID_Stock_Box_old = $row->ID_Stock_Box;
$Stock_Origin_old = $row->Origin_Quantity;
$Stock_Rest_old = $row->Rest_Quantity;}
$Location_Stock_old = "Fridge: " . $Stock_Fridge . " Drawer: " . $Stock_Box . " Rack: " . $Stock_Rack . " Position: " . $Stock_Position . " Barcode: " . $Stock_Barcode;
$result6a = mysql_query
("SELECT dnabanknumbers.Dna_Bank_Number, " .
"dnabanknumbers.ID_Cache, " .
"dnabanknumbers.Extraction_Date, " .
"dnabanknumbers.NoExtractDate, " .
"dnabanknumbers.Check_Date, " .
"dnabanknumbers.Concentration, " .
"dnabanknumbers.Absorbance280, " .
"dnabanknumbers.Absorbance230, " .
"dnabanknumbers.Stock_Gone, " .
"dnabanknumbers.Source_Gone, " .
"dnabanknumbers.Notes, " .
"dnabanknumbers.Block_Until, " .
"dnabanknumbers.Block_General, " .
"dnabanknumbers.Backup_Aliquot, " .
"dnabanknumbers.ID_Type, " .
"dnabanknumbers.ID_Tissue, " .
"dnabanknumbers.ID_RelationDNA_Voucher, " .
"dnabanknumbers.ID_Preservation, " .
"dnabanknumbers.ID_Extraction_Method, " .
"dnabanknumbers.ID_Purification, " .
"dnabanknumbers.ID_Degradation, " .
"dnabanknumbers.ID_Extraction_Staff, " .
"dnabanknumbers.ID_Provided_By_People, " .
"dnabanknumbers.ID_Location_Stock, " .
"dnatype.Type, " .
"tissue.Tissue, " .
"relationdna_voucher.RelationDNA_Voucher, " .
"preservation.Preservation, " .
"extractionmethod.Method AS Extraction_Method, " .
"purification.Method AS Purification_Method, " .
"degradation.Degradation " .
"FROM dnabanknumbers " .
"LEFT JOIN dnatype ON dnabanknumbers.ID_Type = dnatype.ID_Type " .
"LEFT JOIN tissue ON dnabanknumbers.ID_Tissue = tissue.ID_Tissue " .
"LEFT JOIN relationdna_voucher ON dnabanknumbers.ID_RelationDNA_Voucher = relationdna_voucher.ID_RelationDNA_Voucher " .
"LEFT JOIN preservation ON dnabanknumbers.ID_Preservation = preservation.ID_Preservation " .
"LEFT JOIN extractionmethod ON dnabanknumbers.ID_Extraction_Method = extractionmethod.ID_Method " .
"LEFT JOIN purification ON dnabanknumbers.ID_Purification = purification.ID_Purification " .
"LEFT JOIN degradation ON dnabanknumbers.ID_Degradation = degradation.ID_Degradation " .
"WHERE dnabanknumbers.ID_DNA = '$ID_DNA'");
while($row = mysql_fetch_object($result6a))
{ $Dna_Bank_Number_old = $row->Dna_Bank_Number;
$ID_Cache_old = $row->ID_Cache;
$Extraction_Date_old = $row->Extraction_Date;
$NoExtractDate_old = $row->NoExtractDate;
$Check_Date_old = $row->Check_Date;
$Concentration_old = $row->Concentration;
$Absorbance280_old = $row->Absorbance280;
$Absorbance230_old = $row->Absorbance230;
$Stock_Gone_old = $row->Stock_Gone;
$Source_Gone_old = $row->Source_Gone;
$Backup_Aliquot_old = $row->Backup_Aliquot;
$Notes_old = $row->Notes;
$Block_Until_old = $row->Block_Until;
$BlockGeneral_old = $row->Block_General;
$Type_old = $row->Type;
$ID_Type_old = $row->ID_Type;
$Tissue_old = $row->Tissue;
$ID_Tissue_old = $row->ID_Tissue;
$RelationDNA_Voucher_old = $row->RelationDNA_Voucher;
$ID_RelationDNA_Voucher_old = $row->ID_RelationDNA_Voucher;
$Preservation_old = $row->Preservation;
$ID_Preservation_old = $row->ID_Preservation;
$Extraction_Method_old = $row->Extraction_Method;
$ID_Extraction_Method_old = $row->ID_Extraction_Method;
$Purification_Method_old = $row->Purification_Method;
$ID_Extraction_Staff_old = $row->ID_Extraction_Staff;
$ID_Purification_Method_old = $row->ID_Purification;
$Degradation_old = $row->Degradation;
$ID_Degradation_old = $row->ID_Degradation;
$ID_Provided_By_People_old = $row->ID_Provided_By_People;
$ID_Location_Stock_old = $row->ID_Location_Stock; }
if(!empty($formAmplificationID))
{
foreach ($formAmplificationID as $index => $AmplificationIDTest)
{
$LinkTest = $formLink[$index];
$GenBankNumberTest = $formGenBankNo[$index];
$FragmentIDTest = $formFragment[$index];
$resultAmplification = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" amplifications.GenBankNumber AS GenBankNumber, " .
" amplifications.Link AS Link, " .
" amplifications.ID_Fragment, " .
" fragment.Fragment AS Fragment " .
" FROM dnabanknumbers " .
" LEFT JOIN amplifications ON dnabanknumbers.ID_DNA = amplifications.ID_DNA " .
" LEFT JOIN fragment ON amplifications.ID_Fragment = fragment.ID_Fragment " .
" WHERE amplifications.ID_Amplification = '$AmplificationIDTest' " .
" ORDER BY amplifications.ID_Amplification");
if(!empty($AmplificationIDTest))
{
while($row = mysql_fetch_object($resultAmplification))
{
$GenBankNumber_old = $row->GenBankNumber;
$Link_old = $row->Link;
$Fragment_old = $row->Fragment;
if ($row->Link != $LinkTest or $row->GenBankNumber != $GenBankNumberTest or $row->ID_Fragment != $FragmentIDTest)
{
$sql1 = "INSERT INTO _log_amplifications (ID_Amplification, ID_DNA, Fragment, GenBankNumber, Link, Update_Created_Who, Source_Created_Who, Source_Created_When) " .
" (SELECT amplifications.ID_Amplification, " .
"amplifications.ID_DNA, " .
"'$Fragment_old', " .
"'$GenBankNumber_old', " .
"'$Link_old', " .
"'$log', " .
"amplifications.Created_Who, " .
"amplifications.Created_When " .
"FROM amplifications " .
"WHERE amplifications.ID_Amplification = '$AmplificationIDTest')";
$result1 = mysql_query($sql1);
#######################################Error 1###############################################################
if(!$result1)
{ $msg = $sql1."\n";
$msg .= "####Error 1####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false1"; }
#############################################################################################################
if ($result1)
{
$sql2 = "UPDATE amplifications SET GenBankNumber = '$GenBankNumberTest', " .
"Link = '$LinkTest', " .
"ID_Fragment = '$FragmentIDTest', " .
"Update_Who = '$log', " .
"Update_When = '$Current_Date' " .
"WHERE amplifications.ID_Amplification = '$AmplificationIDTest'";
$result2 = mysql_query($sql2);
#######################################Error 2###############################################################
if(!$result2)
{ $msg = $sql2."\n";
$msg .= "####Error 2####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false2"; }
#############################################################################################################
}
}
} //while($row = mysql_fetch_object($resultAmplification))
}
} //foreach End $formGenBankID
}
$QuantityAliquots = array_sum($_POST['formRestQuantity']);
foreach ($formLocationAliquotsID as $index => $AliquotsID)
{
$AliquotsFridge = $formAliquotsFridge[$index];
$AliquotsRack = $formAliquotsRack[$index];
$AliquotsBox = $formAliquotsBox[$index];
$RestQuantity = $formRestQuantity[$index];
$SourceQuantity = $formSourceQuantity[$index];
$Positions = $_POST['PositionAliquot'][$index];
$Barcodes = $_POST['BarcodeAliquot'][$index];
$resultAliquots = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" aliquots.Aliquot_Number, " .
" aliquots_fridge.Aliquots_Fridge, " .
" aliquots.ID_Aliquots_Fridge, " .
" aliquots_rack.Aliquots_Rack, " .
" aliquots.ID_Aliquots_Rack, " .
" aliquots_box.Aliquots_Box, " .
" aliquots.Rest_Quantity, " .
" aliquots.Origin_Quantity, " .
" aliquots.ID_Aliquots_Box, " .
" aliquots.Aliquot_Barcode, " .
" aliquots.Aliquot_Position " .
" FROM dnabanknumbers " .
" LEFT JOIN aliquots ON dnabanknumbers.ID_DNA = aliquots.ID_DNA " .
" LEFT JOIN aliquots_fridge ON aliquots.ID_Aliquots_Fridge = aliquots_fridge.ID_Aliquots_Fridge " .
" LEFT JOIN aliquots_rack ON aliquots.ID_Aliquots_Rack = aliquots_rack.ID_Aliquots_Rack " .
" LEFT JOIN aliquots_box ON aliquots.ID_Aliquots_Box = aliquots_box.ID_Aliquots_Box " .
" WHERE aliquots.ID_Aliquots = '$AliquotsID' " .
" ORDER BY aliquots.ID_Aliquots");
if(empty($AliquotsID))
{
$AliquotNumber = $formDnaBankNumber.'-A';
$sql3a = "INSERT INTO aliquots (ID_DNA, ID_Aliquots_Box, ID_Aliquots_Rack, ID_Aliquots_Fridge, Origin_Quantity, Rest_Quantity, Aliquot_Number, Aliquot_Barcode, Aliquot_Position, Created_Who) " .
" VALUES('$ID_DNA','$AliquotsBox','$AliquotsRack','$AliquotsFridge','$SourceQuantity','$RestQuantity','$AliquotNumber','$Barcodes','$Positions','$log')";
$result3a = mysql_query($sql3a);
#######################################Error 3a/3b##############################################################
if(!$result3a)
{ $msg = $sql3a."\n";
$msg .= "####Error 3a####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false1"; }
#############################################################################################################
}
else {
while($row = mysql_fetch_object($resultAliquots))
{
$Aliquot_Number_old = $row->Aliquot_Number;
$Aliquots_Fridge_old = $row->Aliquots_Fridge;
$Aliquots_Rack_old = $row->Aliquots_Rack;
$Aliquots_Box_old = $row->Aliquots_Box;
$Origin_Quantity_old = $row->Origin_Quantity;
$Rest_Quantity_old = $row->Rest_Quantity;
$Barcode_old = $row->Aliquot_Barcode;
$Position_old = $row->Aliquot_Position;
if ($row->ID_Aliquots_Fridge != $AliquotsFridge or $row->ID_Aliquots_Rack != $AliquotsRack or $row->ID_Aliquots_Box != $AliquotsBox or $Rest_Quantity_old != $RestQuantity or $Position_old != $Positions)
{
$sql3 = "INSERT INTO _log_aliquots (ID_Aliquots, ID_DNA, Aliquots_Box, Aliquots_Rack, Aliquots_Fridge, Aliquot_Number, Origin_Quantity, Rest_Quantity, Aliquot_Barcode, Aliquot_Position, Update_Created_Who, Source_Created_Who, Source_Created_When) " .
" (SELECT aliquots.ID_Aliquots, " .
"aliquots.ID_DNA, " .
"'$Aliquots_Box_old', " .
"'$Aliquots_Rack_old', " .
"'$Aliquots_Fridge_old', " .
"aliquots.Aliquot_Number, " .
"aliquots.Origin_Quantity, " .
"aliquots.Rest_Quantity, " .
"'$Barcode_old', " .
"'$Position_old', " .
"'$log', " .
"aliquots.Created_Who, " .
"aliquots.Created_When " .
"FROM aliquots " .
"WHERE aliquots.ID_Aliquots = '$AliquotsID')";
$result3 = mysql_query($sql3);
#######################################Error 3###############################################################
if(!$result3)
{ $msg = $sql3."\n";
$msg .= "####Error 3####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false1"; }
#############################################################################################################
if ($result3)
{
$sql4 = "UPDATE aliquots SET ID_Aliquots_Fridge = '$AliquotsFridge', " .
"ID_Aliquots_Rack = '$AliquotsRack', " .
"ID_Aliquots_Box = '$AliquotsBox', " .
"Origin_Quantity = '$SourceQuantity', " .
"Rest_Quantity = '$RestQuantity', " .
"Aliquot_Position = '$Positions', " .
"Aliquot_Barcode = '$Barcodes', " .
"Update_Who = '$log', " .
"Update_When = '$Current_Date' " .
"WHERE aliquots.ID_Aliquots = '$AliquotsID'";
$result4 = mysql_query($sql4);
#######################################Error 4###############################################################
if(!$result4)
{ $msg = $sql4."\n";
$msg .= "####Error 4####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false2"; }
#############################################################################################################
}
}
} //while($row = mysql_fetch_object($resultAliquots))
}
} //foreach End $formLocationAliquotsID
if (isset($_REQUEST['c']))
{
reset($_REQUEST['c']);
foreach ($_REQUEST['c'] as $b)
{
if ($b == "SourceGone")
$SourceGone = "1";
if ($b == "StockGone")
$StockGone = "1";
if ($b == "BlockGeneral")
$BlockGeneral = "1";
if ($b == "Aliquot")
$Aliquot = "1";
if ($b == "Backup")
$Backup = "1";
}
}
if($formGenBankNumberA)
{
$sql = "INSERT INTO amplifications (ID_DNA, ID_Fragment, GenBankNumber, Link, Created_Who)" .
"VALUES ('$ID_DNA','$formFragmentA','$formGenBankNumberA','$formGenBankLinkA','$log')";
$result = mysql_query($sql);
#######################################Error formGenbankNumberA###############################################################
if(!$result)
{ $msg = $sql."\n";
$msg .= "####Error formGenBankNumberA####";
trigger_error($msg, E_USER_ERROR);
/**
* calling Error message
*/
include ("../config/Errorreport.php"); }
#############################################################################################################
}
if($formGenBankNumberB)
{
$sql = "INSERT INTO amplifications (ID_DNA, ID_Fragment, GenBankNumber, Link, Created_Who)" .
"VALUES ('$ID_DNA','$formFragmentB','$formGenBankNumberB','$formGenBankLinkB','$log')";
$result = mysql_query($sql);
#######################################Error formGenbankNumberB###############################################################
if(!$result)
{ $msg = $sql."\n";
$msg .= "####Error formGenBankNumberB####";
trigger_error($msg, E_USER_ERROR);
/**
* calling Error message
*/
include ("../config/Errorreport.php"); }
#############################################################################################################
}
if($formGenBankNumberC)
{
$sql = "INSERT INTO amplifications (ID_DNA, ID_Fragment, GenBankNumber, Link, Created_Who)" .
"VALUES ('$ID_DNA','$formFragmentC','$formGenBankNumberC','$formGenBankLinkC','$log')";
$result = mysql_query($sql);
#######################################Error formGenbankNumberC###############################################################
if(!$result)
{ $msg = $sql."\n";
$msg .= "####Error formGenBankNumberC####";
trigger_error($msg, E_USER_ERROR);
/**
* calling Error message
*/
include ("../config/Errorreport.php"); }
#############################################################################################################
}
if($formGenBankNumberD)
{
$sql = "INSERT INTO amplifications (ID_DNA, ID_Fragment, GenBankNumber, Link, Created_Who)" .
"VALUES ('$ID_DNA','$formFragmentD','$formGenBankNumberD','$formGenBankLinkD','$log')";
$result = mysql_query($sql);
#######################################Error formGenbankNumberD###############################################################
if(!$result)
{ $msg = $sql."\n";
$msg .= "####Error formGenBankNumberD####";
trigger_error($msg, E_USER_ERROR);
/**
* calling Error message
*/
include ("../config/Errorreport.php"); }
#############################################################################################################
}
if (isset($_REQUEST['Aliquot']))
{
foreach ($_POST['x'] as $v => $value)
{
$rack = $_POST['y'][$v];
$box = $_POST['z'][$v];
$SourceQuantity = $AliquotSourceQuantity[$v];
$RestQuantity = $AliquotRestQuantity[$v];
$Barcode = $BarcodeAliquot[$v];
$Position = $PositionAliquot[$v];
if (!isset($_REQUEST['Aliquot']))
{ unset($value);
unset($rack);
unset($box);
unset($SourceQuantity);
unset($RestQuantity);
unset($Barcode);
unset($Position); }
else {
if($value=="")
{ unset($value);
unset($rack);
unset($box);
unset($SourceQuantity);
unset($RestQuantity);
unset($Barcode);
unset($Position); }
else {
$resultLastAliquotsNumber = mysql_query("SELECT Aliquot_Number FROM aliquots WHERE ID_DNA = '$ID_DNA' ORDER BY Aliquot_Number DESC LIMIT 0,1");
if(!$resultLastAliquotsNumber)
{ $letter = A;
$lettercount = $letter++;
$AliquotNumber = $formDnaBankNumber.'-'.$lettercount;
}
else {
while($row = mysql_fetch_object($resultLastAliquotsNumber))
{
$Number = $row->Aliquot_Number;
$array = explode("-",$Number);
$letter = $array[1]; }
$lettercount = $letter++;
$AliquotNumber = $formDnaBankNumber.'-'.$letter++;
$sql10 = "INSERT INTO aliquots (ID_DNA, ID_Aliquots_Fridge, ID_Aliquots_Rack, ID_Aliquots_Box,
Aliquot_Number, Origin_Quantity, Rest_Quantity, Aliquot_Barcode, Aliquot_Position, Created_Who) " .
"VALUES ('$ID_DNA','$value','$rack','$box','$AliquotNumber','$SourceQuantity','$SourceQuantity','$Barcode','$Position','$log')";
$result10 = mysql_query($sql10);
#######################################Error 10###############################################################
if(!$result10)
{ $msg = $sql10."\n";
$msg .= "####Error 10####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false2"; }
#############################################################################################################
} } }
}
} //foreach ($_POST['x'] as $v => $value)
$sql11 = "INSERT INTO _log_dnabanknumbers (ID_DNA, ID_Cache, Dna_Bank_Number, Extraction_Date, NoExtractDate, Check_Date, Concentration, Absorbance280, Absorbance230, Quantity_Aliquots, Stock_Gone, Source_Gone, Notes, Block_Until, Block_General, Number_Aliquots, Source_Created_When, Source_Created_Who, Type, Tissue, Preservation, Purification, Extraction_Method, Degradation, RelationDNA_Voucher, Extraction_Staff, Provided_By_People, Update_Created_Who, Location_Stock, Backup_Aliquot) " .
"(SELECT dnabanknumbers.ID_DNA, " .
"'$ID_Cache_old', " .
"'$Dna_Bank_Number_old', " .
"'$Extraction_Date_old', " .
"'$NoExtractDate_old', " .
"'$Check_Date_old', " .
"'$Concentration_old', " .
"'$Absorbance280_old', " .
"'$Absorbance230_old', " .
"'$Quantity_Aliquots_old', " .
"'$Stock_Gone_old', " .
"'$Source_Gone_old', " .
"'$Notes_old', " .
"'$Block_Until_old', " .
"'$BlockGeneral_old', " .
"'$Number_Aliquots_old', " .
"dnabanknumbers.Created_When, " .
"dnabanknumbers.Created_Who, " .
"'$Type_old', " .
"'$Tissue_old', " .
"'$Preservation_old', " .
"'$Purification_Method_old', " .
"'$Extraction_Method_old', " .
"'$Degradation_old', " .
"'$RelationDNA_Voucher_old', " .
"'$Extraction_Staff_old', " .
"'$Provided_By_People_old', " .
"'$log', " .
"'$Location_Stock_old', " .
"'$Backup_Aliquot_old' " .
"FROM dnabanknumbers " .
"WHERE dnabanknumbers.ID_DNA = '$ID_DNA')";
$result11 = mysql_query($sql11);
$IDDnaLog=mysql_insert_id();
#######################################Error 11###############################################################
if(!$result11)
{ $msg = $sql11."\n";
$msg .= "####Error 11####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false1"; }
#############################################################################################################
if ($result11)
{
if($_POST['formExtractionDate'] == "YYYY-MM-DD" or $_POST['formExtractionDate'] == "") {unset($formExtractionDate);}
if($_POST['formCheckDate'] == "YYYY-MM-DD" or $_POST['formCheckDate'] == "") {unset($formCheckDate);}
if($_POST['formBlockDate'] == "YYYY-MM-DD" or $_POST['formBlockDate == ""']) {unset($formBlockDate);}
if($_POST['formExtractionDate'] == "YYYY-MM-DD" or $_POST['formExtractionDate'] == "") { unset($_POST['formExtractionDate']); }
else { $date = $_POST['formExtractionDate'];
if (!valiDate($date)) {
$Validation = "FALSE";
echo '<span class="error">Extraction date incorrect!</span><br>'; }
else { $_POST['formExtractionDate'] = $_POST['formExtractionDate']; } }
if($_POST['formCheckDate'] == "YYYY-MM-DD" or $_POST['formCheckDate'] == "") { unset($_POST['formCheckDate']); }
else { $date = $_POST['formCheckDate'];
if (!valiDate($date)) {
$Validation = "FALSE";
echo '<span class="error">Quality check date incorrect!</span><br>'; }
else { $_POST['formCheckDate'] = $_POST['formCheckDate']; } }
if($_POST['formBlockDate'] == "YYYY-MM-DD" or $_POST['formBlockDate'] == "") { unset($_POST['formBlockDate']); }
else { $date = $_POST['formBlockDate'];
if (!valiDate($date)) {
$Validation = "FALSE";
echo '<span class="error">Block date incorrect!</span><br>'; }
else { $_POST['formBlockDate'] = $_POST['formBlockDate']; } }
$sql12 = "UPDATE dnabanknumbers SET DNA_Bank_Number = '$formDnaBankNumber', " .
"Extraction_Date = '$formExtractionDate', " .
"NoExtractDate = '$formNoExtractDate', " .
"Check_Date = '$formCheckDate', " .
"Concentration = '$formConcentration', " .
"Absorbance280 = '$formAbsorbance280', " .
"Absorbance230 = '$formAbsorbance230', " .
"Notes = '$formNotes', " .
"Notes_Intern = '$formNotesIntern', " .
"Block_Until = '$formBlockDate', " .
"ID_Type = '$formType', " .
"ID_Tissue = '$formTissue', " .
"ID_RelationDNA_Voucher = '$formRelation', " .
"ID_Preservation = '$formPreservation', " .
"ID_Extraction_Method = '$formExtractionMethod', " .
"ID_Extraction_Staff = '$formExtractionStaff', " .
"ID_Purification = '$formPurificationMethod', " .
"ID_Degradation = '$formDegradation', " .
"ID_Provided_By_People = '$formProvidedBy', " .
"Stock_Gone = '$StockGone', " .
"Source_Gone = '$SourceGone', " .
"Block_General = '$BlockGeneral', " .
"Block_Until = '$formBlockDate', " .
"Backup_Aliquot = '$Backup', " .
"Update_Who = '$log', " .
"Update_When = '$Current_Date' " .
"WHERE dnabanknumbers.ID_DNA = '$ID_DNA'";
$result12 = mysql_query($sql12);
if(empty($ID_Stock_Fridge_old) or empty($ID_Stock_Rack_old) or empty($ID_Stock_Box_old))
{
$sql13a = "INSERT INTO location_stock (ID_Stock_Fridge, ID_Stock_Rack, ID_Stock_Box, Origin_Quantity, Rest_Quantity, Created_Who) " .
"VALUES ('$formStockFridge','$formStockRack','$formStockBox','$formStockQuantity','$formStockRest','$log')";
$result13a = mysql_query($sql13a);
$ID_Location_Stock_New = mysql_insert_id();
$sql13b = "UPDATE dnabanknumbers SET ID_Location_Stock = '$ID_Location_Stock_New' " .
"WHERE dnabanknumbers.ID_DNA = '$ID_DNA'";
$result13b = mysql_query($sql13b);
#######################################Error 13a/13b###############################################################
if(!$result13a or !$result13b)
{ $msg = $sql13a."\n";
$msg .= $sql13b. "\n";
$msg .= "####Error 13a/13b####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false2"; }
}
#############################################################################################################
$sql13 = "UPDATE location_stock SET ID_Stock_Fridge = '$formStockFridge', " .
"ID_Stock_Rack = '$formStockRack', " .
"ID_Stock_Box = '$formStockBox', " .
"Origin_Quantity = '$formStockQuantity', " .
"Rest_Quantity = '$formStockRest', " .
"Stock_Position = '$formPositionStock', " .
"Update_Who = '$log', " .
"Update_When = '$Current_Date' " .
"WHERE location_stock.ID_Location_Stock = '$ID_Location_Stock_old'";
$result13 = mysql_query($sql13);
#######################################Error 12/13###############################################################
if(!$result12 or !$result13)
{ $msg = $sql12."\n";
$msg .= $sql13. "\n";
$msg .= "####Error 12/13####";
trigger_error($msg, E_USER_ERROR);
$test1 = "false2"; }
#############################################################################################################
/********************************************************************************************
* Creating main query for details of single record after saving edits *
*********************************************************************************************/
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
$sqlType = "Detail";
$sql = $sqlcreate .
$sql = "SELECT DISTINCT dnabanknumbers.ID_DNA FROM dnabanknumbers
LEFT JOIN cachecollection ON dnabanknumbers.ID_Cache = cachecollection.ID_Cache
LEFT JOIN cachetaxonidentified ON cachecollection.ID_Cache = cachetaxonidentified.ID_Cache
LEFT JOIN cachehighertaxa ON cachetaxonidentified.ID_TaxonIdentified = cachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN amplifications ON dnabanknumbers.ID_DNA = amplifications.ID_DNA
LEFT JOIN location_stock ON location_stock.ID_Location_Stock = dnabanknumbers.ID_Location_Stock
LEFT JOIN aliquots ON dnabanknumbers.ID_DNA = aliquots.ID_DNA
LEFT JOIN request ON dnabanknumbers.ID_DNA = request.ID_DNA " .
"WHERE dnabanknumbers.ID_DNA = '$ID_DNA' ";
if (isset($_SESSION['guest']) || $_SESSION['guest']) {
$sql .= " AND dnabanknumbers.Block_General = '0' "; }
if (isset($_SESSION['project']) || $_SESSION['project']) {
$sql .= " AND ((dnabanknumbers.Created_Who != '".$log."' AND dnabanknumbers.Block_General = '0') OR dnabanknumbers.Created_Who = '".$log."') "; }
$sql .= $sqllimit;
}
} // if (isset($_REQUEST['Aliquot']))
/********************************************************************************************
* Creating main query for details of single record *
* START *
*********************************************************************************************/
else {
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
$sqlType = "Detail";
$sql = $sqlcreate .
$sql = "SELECT DISTINCT dnabanknumbers.ID_DNA FROM dnabanknumbers
LEFT JOIN cachecollection ON dnabanknumbers.ID_Cache = cachecollection.ID_Cache
LEFT JOIN cachetaxonidentified ON cachecollection.ID_Cache = cachetaxonidentified.ID_Cache
LEFT JOIN cachehighertaxa ON cachetaxonidentified.ID_TaxonIdentified = cachehighertaxa.ID_CacheTaxonidentified
LEFT JOIN amplifications ON dnabanknumbers.ID_DNA = amplifications.ID_DNA
LEFT JOIN location_stock ON location_stock.ID_Location_Stock = dnabanknumbers.ID_Location_Stock
LEFT JOIN aliquots ON dnabanknumbers.ID_DNA = aliquots.ID_DNA
LEFT JOIN request ON dnabanknumbers.ID_DNA = request.ID_DNA " .
"WHERE dnabanknumbers.ID_DNA = '$ID_DNA' ";
if (isset($_SESSION['guest']) || $_SESSION['guest']) {
$sql .= " AND dnabanknumbers.Block_General = '0' "; }
if (isset($_SESSION['project']) || $_SESSION['project']) {
$sql .= " AND ((dnabanknumbers.Created_Who != '".$log."' AND dnabanknumbers.Block_General = '0') OR dnabanknumbers.Created_Who = '".$log."') "; }
$sql .= $sqllimit;
}
/********************************************************************************************
* Creating main query for details of single record *
* END *
*********************************************************************************************/
} // if(!$formSubmitUpdate and !$formSubmitVerify)
/********************************************************************************************
* Show data *
* START *
*********************************************************************************************/
$Dnacount=0;
if(!empty($sql))
{
/********************************************************************************************
* just in case it is still here: drop tmpDnaIDs *
********************************************************************************************/
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
mysql_query($sql);
if(!mysql_affected_rows())
{
echo "<table border='0' id='tablesearch'><tr><td colspan='5'><a class='red' href=\"data_cleaning.php\">Back to Data Cleaning Overview.</a></td></tr>";
/********************************************************************************************
* Further message for guests and project members when query was wrong *
*********************************************************************************************/
if (isset($_SESSION['guest']) || $_SESSION['guest'] || isset($_SESSION['project']) || $_SESSION['project']) {
echo "<tr><td colspan='5'><span class='error'>Sorry, you don't have permission to see this record.</span></td></tr></table>"; }
else { //user has access to data
/********************************************************************************************
* Search key string *
*********************************************************************************************/
echo "<tr><td colspan='5'><b>No hits found for:</b>";
foreach($descriptionall as $index => $descriptiontest)
{ $valuetest = $valueall[$index];
echo $descriptiontest."[<b>".$valuetest."</b>]"; }
echo "</td></tr></table>"; }
} //END if(!mysql_affected_rows())
else
{
$result1 = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" dnabanknumbers.ID_Cache, " .
" dnabanknumbers.DNA_Bank_Number, " .
" dnabanknumbers.Extraction_Date, " .
" dnabanknumbers.NoExtractDate, " .
" dnabanknumbers.Absorbance280, " .
" dnabanknumbers.Absorbance230, " .
" dnabanknumbers.Concentration, " .
" dnabanknumbers.Check_Date, " .
" dnabanknumbers.Created_Who, " .
" dnabanknumbers.Created_When, " .
" dnabanknumbers.Stock_Gone, " .
" dnabanknumbers.Source_Gone, " .
" dnabanknumbers.Block_Until, " .
" dnabanknumbers.Block_General, " .
" dnabanknumbers.Notes, " .
" dnabanknumbers.Notes_Intern, " .
" dnabanknumbers.Backup_Aliquot, " .
" dnabanknumbers.Update_Who, " .
" dnabanknumbers.Update_When, " .
" relationdna_voucher.RelationDNA_Voucher, " .
" dnatype.Type, " .
" preservation.Preservation, " .
" tissue.Tissue, " .
" extractionmethod.Method, " .
" extractionmethod.Company, " .
" purification.Method, " .
" purification.Company, " .
" degradation.Degradation, " .
" cachecollection.ID_Dataset_Specimen, " .
" cachecollection.UnitID_Specimen, " .
" cachecollection.CollectionCode_Specimen, " .
" cachecollection.InstitutionCode_Specimen, " .
" cachecollection.ContinentOrOcean, " .
" cachecollection.CountryName, " .
" cachecollection.CountryISO2, " .
" cachecollection.Collectors, " .
" cachecollection.CollectionDate, " .
" cachecollection.CollectorsNo, " .
" cachecollection.Locality, " .
" cachecollection.Altitude, " .
" cachecollection.Longitude, " .
" cachecollection.Latitude, " .
" cachecollection.CollectionInfo_Other, " .
" cachecollection.TypeStatus, " .
" dataset.Display AS SpecimenDatabase, " .
" dataset.ID_Dataset, " .
" schemata.QueryPart1b, " .
" schemata.QueryPart1_1, " .
" schemata.QueryPart1_2b, " .
" schemata.QueryPart2, " .
" schemata.QueryPart3, " .
" schemata.QueryPart4, " .
" schemata.QueryPart5, " .
" provider.Provider, " .
" dataset.Digir_Resource, " .
" dataset.Digir_Source, " .
" provider.Provider, " .
" provider.ID_Schema " .
" FROM dnabanknumbers JOIN tmpDnaIDs " .
" LEFT JOIN cachecollection ON dnabanknumbers.ID_Cache = cachecollection.ID_Cache " .
" LEFT JOIN relationdna_voucher ON dnabanknumbers.ID_RelationDNA_Voucher = relationdna_voucher.ID_RelationDNA_Voucher " .
" LEFT JOIN dnatype ON dnabanknumbers.ID_Type = dnatype.ID_Type " .
" LEFT JOIN preservation ON dnabanknumbers.ID_Preservation = preservation.ID_Preservation " .
" LEFT JOIN tissue ON dnabanknumbers.ID_Tissue = tissue.ID_Tissue " .
" LEFT JOIN extractionmethod ON dnabanknumbers.ID_Extraction_Method = extractionmethod.ID_Method " .
" LEFT JOIN purification ON dnabanknumbers.ID_Purification = purification.ID_Purification " .
" LEFT JOIN people ON dnabanknumbers.ID_Extraction_Staff = people.ID_People " .
" LEFT JOIN degradation ON dnabanknumbers.ID_Degradation = degradation.ID_Degradation " .
" LEFT JOIN dataset ON cachecollection.ID_Dataset_Specimen = dataset.ID_Dataset " .
" LEFT JOIN provider ON dataset.ID_Provider = provider.ID_Provider " .
" LEFT JOIN schemata ON provider.ID_Schema = schemata.ID_Schema " .
" WHERE dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
$result2 = mysql_query
("SELECT dnabanknumbers.ID_DNA AS ID_DNA, " .
" dnabanknumbers.ID_Cache, " .
" cachecollection.ID_Cache, " .
" cachetaxonidentified.NameAuthorYear AS NameAuthorYear, " .
" cachetaxonidentified.PreferredFlag AS PreferredFlag " .
" FROM cachecollection, cachetaxonidentified, dnabanknumbers, tmpDnaIDs " .
" WHERE cachetaxonidentified.ID_Cache = cachecollection.ID_Cache " .
" AND cachecollection.ID_Cache = dnabanknumbers.ID_Cache " .
" AND cachetaxonidentified.PreferredFlag != '' " .
" AND dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result2))
{
if(isset($PreferredFlag[$row->ID_DNA]))
{ $Flag = $PreferredFlag[$row->ID_DNA];
if($Flag == 'true' or $Flag == '1') {$Preferred = "(preferred species name)";}
else {$Preferred = ""; }
}
else
{ $PreferredFlag[$row->ID_DNA] = ""; }
if(isset($NameAuthorYear[$row->ID_DNA]))
{ $NameAuthorYear[$row->ID_DNA] .= " " . $Preferred . ", Other Identifications: "; }
else
{ $NameAuthorYear[$row->ID_DNA] = ""; }
$tmp = $row->NameAuthorYear;
$NameAuthorYear[$row->ID_DNA] .= $tmp;
$tmp2 = $row->PreferredFlag;
$PreferredFlag[$row->ID_DNA] .= $tmp2; }
$result3 = mysql_query
("SELECT dnabanknumbers.ID_DNA AS ID_DNA, " .
" dnabanknumbers.ID_Cache, " .
" cachecollection.ID_Cache, " .
" cachetaxonidentified.ID_TaxonIdentified, " .
" cachehighertaxa.HigherTaxon AS HigherTaxon, " .
" cachehighertaxa.TaxonRank AS TaxonRank " .
" FROM cachecollection, cachetaxonidentified, cachehighertaxa, dnabanknumbers, tmpDnaIDs " .
" WHERE cachehighertaxa.ID_CacheTaxonIdentified = cachetaxonidentified.ID_TaxonIdentified " .
" AND cachetaxonidentified.ID_Cache = cachecollection.ID_Cache " .
" AND cachecollection.ID_Cache = dnabanknumbers.ID_Cache " .
" AND dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result3))
{
if(isset($TaxonRank[$row->ID_DNA]))
{ $TaxonRank[$row->ID_DNA] .= "<br>"; }
else { $TaxonRank[$row->ID_DNA] = ""; }
if(isset($HigherTaxon[$row->ID_DNA]))
{
$HigherTaxon[$row->ID_DNA] .= "<br>";
}
else
{ $HigherTaxon[$row->ID_DNA] = ""; }
$tmp = $row->HigherTaxon;
$HigherTaxon[$row->ID_DNA] .= $tmp;
$tmp2 = $row->TaxonRank;
$TaxonRank[$row->ID_DNA] .= $tmp2;
}
$result3a = mysql_query
("SELECT dnabanknumbers.ID_DNA AS ID_DNA, " .
" dnabanknumbers.ID_Cache, " .
" cacheimages.ID_Cache, " .
" cacheimages.Images " .
" FROM cacheimages, dnabanknumbers, tmpDnaIDs " .
" WHERE cacheimages.ID_Cache = dnabanknumbers.ID_Cache " .
" AND dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result3a))
{
if(isset($ImageLink[$row->ID_DNA]))
{ $ImageLink .= build_href1($row->Images,$row->Images) . "<br>"; }
else
{$ImageLink .= build_href1($row->Images,$row->Images) . "<br>"; }
}
$result5 = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" people.Name_All AS Name_All_Extraction " .
" FROM dnabanknumbers,people, tmpDnaIDs " .
" WHERE dnabanknumbers.ID_Extraction_Staff = people.ID_People " .
" AND dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result5))
{ $Extraction_Staff = $row->Name_All_Extraction; }
$result6 = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" people.Name_All AS Name_All_Provided_By " .
" FROM dnabanknumbers,people, tmpDnaIDs " .
" WHERE dnabanknumbers.ID_Provided_By_People = people.ID_People " .
" AND dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result6))
{ $Provided_By_People = $row->Name_All_Provided_By; }
$result7 = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" amplifications.GenBankNumber AS GenBankNumber, " .
" amplifications.Link AS Link, " .
" geneticlocus.GeneticLocus AS GeneticLocus " .
" FROM dnabanknumbers JOIN tmpDnaIDs " .
" LEFT JOIN amplifications ON dnabanknumbers.ID_DNA = amplifications.ID_DNA " .
" LEFT JOIN geneticlocus ON amplifications.ID_GeneticLocus = geneticlocus.ID_GeneticLocus " .
" WHERE dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result7))
{
if(isset($Link[$row->ID_DNA]))
{ if($formSubmitSave) {}
else { $Link[$row->ID_DNA] .= ""; }
$Link2 .= build_href1($row->Link,$row->GenBankNumber) . "<br>";
}
else
{
$Link[$row->ID_DNA] = "";
$Link2 .= build_href1($row->Link,$row->GenBankNumber) . "<br>";
}
if(empty($row->Link)) { $Link2 = ""; }
if(isset($GeneticLocus[$row->ID_DNA]))
{
$GeneticLocus[$row->ID_DNA] .= "<br>";
}
else
{ $GeneticLocus[$row->ID_DNA] = ""; }
if($formSubmitSave) {}
else {
$tmp = $row->Link;
$Link[$row->ID_DNA] .= $tmp; }
$tmp = $row->GeneticLocus;
$GeneticLocus[$row->ID_DNA] .= $tmp;
}
$result8 = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" stock_box.Stock_Box, " .
" stock_rack.Stock_Rack, " .
" stock_fridge.Stock_Fridge, " .
" location_stock.Origin_Quantity AS Location_Volume, " .
" location_stock.Rest_Quantity AS Location_Rest, " .
" location_stock.Stock_Barcode, " .
" location_stock.Stock_Position " .
" FROM dnabanknumbers JOIN tmpDnaIDs " .
" LEFT JOIN location_stock ON dnabanknumbers.ID_Location_Stock = location_stock.ID_Location_Stock " .
" LEFT JOIN stock_box ON stock_box.ID_Stock_Box = location_stock.ID_Stock_Box " .
" LEFT JOIN stock_rack ON stock_rack.ID_Stock_Rack = location_stock.ID_Stock_Rack " .
" LEFT JOIN stock_fridge ON stock_fridge.ID_Stock_Fridge = location_stock.ID_Stock_Fridge " .
" WHERE dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result8))
{ $Location_Box = $row->Stock_Box;
$Location_Rack = $row->Stock_Rack;
$Location_Fridge = $row->Stock_Fridge;
$Location_Volume = $row->Location_Volume;
$Location_Rest = $row->Location_Rest;
$Stock_Barcode = $row->Stock_Barcode;
$Stock_Position = $row->Stock_Position;
}
$result9 = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" aliquots_box.Aliquots_Box, " .
" aliquots_rack.Aliquots_Rack, " .
" aliquots_fridge.Aliquots_Fridge, " .
" aliquots.Aliquot_Number, " .
" aliquots.Origin_Quantity AS Aliquot_Origin, " .
" aliquots.Rest_Quantity AS Aliquot_Rest, " .
" aliquots.Aliquot_Barcode, " .
" aliquots.Aliquot_Position, " .
" aliquots.Price, " .
" aliquots.Currency " .
" FROM dnabanknumbers JOIN tmpDnaIDs " .
" LEFT JOIN aliquots ON dnabanknumbers.ID_DNA = aliquots.ID_DNA " .
" LEFT JOIN aliquots_box ON aliquots_box.ID_Aliquots_Box = aliquots.ID_Aliquots_Box " .
" LEFT JOIN aliquots_rack ON aliquots_rack.ID_Aliquots_Rack = aliquots.ID_Aliquots_Rack " .
" LEFT JOIN aliquots_fridge ON aliquots_fridge.ID_Aliquots_Fridge = aliquots.ID_Aliquots_Fridge " .
" WHERE dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA " .
" ORDER BY aliquots.Aliquot_Number");
$result10 = mysql_query
("SELECT dnabanknumbers.ID_DNA, " .
" request.Request_Date, " .
" request.Request_Number_Aliquots, " .
" request.Request_Notes, " .
" people.Name_All AS Customer " .
" FROM dnabanknumbers JOIN tmpDnaIDs " .
" LEFT JOIN request ON dnabanknumbers.ID_DNA = request.ID_DNA " .
" LEFT JOIN people ON request.ID_People = people.ID_People " .
" WHERE dnabanknumbers.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result10))
{
if(isset($Request_Date[$row->ID_DNA]))
{ $Request_Date[$row->ID_DNA] .= "";
$DateRequest .= $row->Request_Date . "<br>";
}
else
{ $Request_Date[$row->ID_DNA] = "";
$DateRequest .= $row->Request_Date . "<br>"; }
if(isset($Request_Number_Aliquots[$row->ID_DNA]))
{ $Request_Number_Aliquots[$row->ID_DNA] .= "<br>"; }
else
{ $Request_Number_Aliquots[$row->ID_DNA] = ""; }
if(isset($Request_Notes[$row->ID_DNA]))
{ $Request_Notes[$row->ID_DNA] .= "<br>"; }
else
{ $Request_Notes[$row->ID_DNA] = ""; }
if(isset($Customer[$row->ID_DNA]))
{ $Customer[$row->ID_DNA] .= "<br>"; }
else
{ $Customer[$row->ID_DNA] = ""; }
$tmp = $row->Request_Date;
$Request_Date[$row->ID_DNA] .= $tmp;
$tmp = $row->Request_Number_Aliquots;
$Request_Number_Aliquots[$row->ID_DNA] .= $tmp;
$tmp = $row->Request_Notes;
$Request_Notes[$row->ID_DNA] .= $tmp;
$tmp = $row->Customer;
$Customer[$row->ID_DNA] .= $tmp; }
$result11 = mysql_query
("SELECT publications_amplifications.ID_DNA, " .
" publications_amplifications.Paper_Cache, " .
" publications_amplifications.GeneticLocus AS GeneticLocusPub, " .
" publications.Link AS LinkPub " .
" FROM publications_amplifications JOIN tmpDnaIDs " .
" LEFT JOIN publications ON publications_amplifications.ID_Publications = publications.ID_Publications " .
" WHERE publications_amplifications.ID_DNA = tmpDnaIDs.ID_DNA");
while($row = mysql_fetch_object($result11))
{
if(isset($Paper_Cache[$row->ID_DNA]))
{ $Paper_Cache[$row->ID_DNA] .= "<br>"; }
else
{ $Paper_Cache[$row->ID_DNA] = ""; }
if(isset($GeneticLocusPub[$row->ID_DNA]))
{ $GeneticLocusPub[$row->ID_DNA] .= "<br>"; }
else
{ $GeneticLocusPub[$row->ID_DNA] = ""; }
if(isset($LinkPub[$row->ID_DNA]))
{ if($formSubmitSave) {}
else { $LinkPub[$row->ID_DNA] .= ""; }
$LinkPub2 .= build_href1($row->LinkPub,"document") . "<br>";
}
else
{
$LinkPub[$row->ID_DNA] = "";
$LinkPub2 .= build_href1($row->LinkPub,"document") . "<br>";
}
$tmp = $row->Paper_Cache;
$Paper_Cache[$row->ID_DNA] .= $tmp;
$tmp = $row->GeneticLocusPub;
$GeneticLocusPub[$row->ID_DNA] .= $tmp;
if($formSubmitSave) {}
else {
$tmp = $row->LinkPub;
$LinkPub[$row->ID_DNA] .= $tmp; }
}
/********************************************************************************************
* Show hitlist ($sqlType == "Dna") *
* START *
*********************************************************************************************/
if($sqlType=="Dna")
{
echo "<table border='0' id='tablesearch'><tr><td colspan='5'><a class='red' href=\"data_cleaning.php\">Back to Data Cleaning Overview.</a></td></tr>";
$pagesum = mysql_num_rows($result1);
echo "<tr><td colspan='5' align='center'><b>".$sum;
if($sum == 1) {echo " entry "; } else { echo " entries ";}
echo "found for:</b>";
foreach($descriptionall as $index => $descriptiontest)
{ $valuetest = $valueall[$index];
echo $descriptiontest."[<b>".$valuetest."</b>]"; }
echo ".<input type='hidden' name='page' value='".$page."'></td></tr>";
/**
* paging hitlist
*/
include("pages.php");
echo "<tr><td colspan='5'><hr /></td></tr></table>";
$Dnacount=0;
echo "<table border='0' id='tablesearch'>";
echo "<th></th><th width='120'><a href='query_clean.php?hitlist=true&sort=DU";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "DU" or $_GET['sort'] == "") { echo "upg.jpg"; }
else { echo "up.jpg"; }
echo "' border='0'/></a>DNA Bank No<a href='query_clean.php?hitlist=true&sort=DD";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo"'><img src='../images/";
if($_GET['sort'] == "DD") { echo "downg.jpg"; }
else { echo "down.jpg"; }
echo "' border='0'/></a></th><th width='160'><a href='query_clean.php?hitlist=true&sort=SU";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "SU") { echo "upg.jpg"; }
else { echo "up.jpg"; }
echo "' border='0'/></a>UnitID/Specimen No<a href='query_clean.php?hitlist=true&sort=SD";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "SD") { echo "downg.jpg"; }
else { echo "down.jpg"; }
echo "' border='0'/></a></th><th width='200'><a href='query_clean.php?hitlist=true&sort=CU";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "CU") { echo "upg.jpg"; }
else { echo "up.jpg"; }
echo "' border='0'/></a>Collection<a href='query_clean.php?hitlist=true&sort=CD";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "CD") { echo "downg.jpg"; }
else { echo "down.jpg"; }
echo "' border='0'/></a></th><th width='120'><a href='query_clean.php?hitlist=true&sort=IU";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "IU") { echo "upg.jpg"; }
else { echo "up.jpg"; }
echo "' border='0'/></a>Institution<a href='query_clean.php?hitlist=true&sort=ID";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "ID") { echo "downg.jpg"; }
else { echo "down.jpg"; }
echo "' border='0'/></a></th><th><a href='query_clean.php?hitlist=true&sort=TU";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "TU") { echo "upg.jpg"; }
else { echo "up.jpg"; }
echo "' border='0'/></a>Taxon<a href='query_clean.php?hitlist=true&sort=TD";
if($_GET['page'] != "") { echo "&page=".$_GET['page']; }
echo "'><img src='../images/";
if($_GET['sort'] == "TD") { echo "downg.jpg"; }
else { echo "down.jpg"; }
echo "' border='0'/></a></th>";
echo "<tr><td colspan='6'><hr/></td></tr>";
for ($i=1; $i<=$pagesum; $i++) {
$row=mysql_fetch_array($result1);
{
$Dnacount++;
if($Dnacount<=$pagesize)
{
$ID = $row['ID_DNA'];
$LinkSpecimen = "sqlType=Detail&ID_DNA=".$ID;
if($_GET['page'] > 1) { $LinkSpecimen .= "&Page=".$_GET['page']; }
$number = ($page-1)*$pagesize+$i;
echo "<tr style='border-top-width:1px;border-top-style:solid;border-color:#CCCCCC;'><td valign='top' align='right'><b>".$number.".</b></td><td valign='top'><b><span style='margin-left:10px;'>".build_href("query_clean.php",
$LinkSpecimen,
$row['DNA_Bank_Number']) . "</span></b></td><td valign='top'><span style='margin-left:10px;'>".$row['UnitID_Specimen']."</span></td><td valign='top'><span style='margin-left:10px;'>".$row['CollectionCode_Specimen']."</a></td><td valign='top'><span style='margin-left:10px;'>".$row['InstitutionCode_Specimen']."</a></td><td valign='top'>";
echo $NameAuthorYear[$row['ID_DNA']];
echo "</td></tr>";
}
} } // for ($i=1; $i<=$pagesum; $i++)
echo "</table>";
} // if($sqlType=="Dna")
/********************************************************************************************
* Show hitlist ($sqlType == "Dna") *
* END *
*********************************************************************************************/
/********************************************************************************************
* Show details of a single record ($sqlType == "Detail") *
* 1. Selecting DNA data from database *
* 2. Selecting reference to specimen record *
* 3. Getting specimen data live from original provider *
* 3a. When specimen provider is not available, show specimen data from cache tables *
* 3b. When specimen data recorded in Specimen Tool and not yet available via wrapper use *
* another script (see line 1894) *
* START *
*********************************************************************************************/
if($sqlType=="Detail")
{
echo "<table border='0' id='tablesearch'><tr><td width='80'><a class='red' href='query_clean.php'>Back to Data Cleaning Overview</a></td>";
if($formSubmitSave)
{ echo "<td><a class='red' href='query_clean.php?hitlist=true' name='formBackToHitlist'>Back to Hitlist</a></td>";
if ($test1 == 'false1' or $test1 == 'false2'){ echo "<td>";
/**
* calling Error message
*/
include("../config/Errorreport.php"); }
if ($test1 != 'false1' and $test1 != 'false2') { echo "<td align='right'><div class='background3'><span class='fine'>Changes accepted.</span></div>"; }
echo "</td></tr>"; }
else {
echo "<td><a class='red' href='query_clean.php?hitlist=true";
if($_GET['Page'] != "") { echo "&page=".$_GET['Page']; }
echo "'>Back to Hitlist</a></td></tr>"; }
echo "</table>";
/********************************************************************************************
* Building table for DNA details *
* START *
*********************************************************************************************/
echo "<table border='0' id='tabledetails' class='main'>";
$Dnacount=0;
while($row = mysql_fetch_object($result1))
{
$Dnacount++;
$ID_Cache = $row->ID_Cache;
/********************************************************************************************
* Select further DNA samples from same voucher ($test) *
* See also line 1960 *
*********************************************************************************************/
$test = mysql_query("SELECT dnabanknumbers.ID_DNA, dnabanknumbers.DNA_Bank_Number, " .
"dnabanknumbers.Extraction_Date FROM dnabanknumbers, tmpDnaIDs " .
"WHERE dnabanknumbers.ID_Cache = '$ID_Cache' " .
" AND dnabanknumbers.ID_DNA != tmpDnaIDs.ID_DNA " .
"ORDER BY LENGTH(dnabanknumbers.DNA_Bank_Number),dnabanknumbers.DNA_Bank_Number");
if($Dnacount<=$pagesize)
{
if($row->NoExtractDate == '1')
{ $NoExtractDate = "No Extraction Date available."; }
echo "<tr><td colspan='7'><hr></td></tr>",
"<tr><td colspan='7'><h6>DNA details </h6></td></tr>",
"<tr><td id='DescriptionBlack2'>Extraction No</td><td colspan='2'><span class='details'>" . $row->DNA_Bank_Number . "</span></td>",
$row->Type ? "<td id='DescriptionBlack2'>DNA Type</td><td colspan='3'><span class='DescriptionGreen2'>" . $row->Type . "</span></td></tr>" : "<td colspan='2'></td></tr>",
"<tr><td><h4>Relation to voucher </h4></td><td colspan='6'><h4>" . $row->RelationDNA_Voucher . "</h4></td></tr>",
"<tr>",
$row->Tissue ? "<td id='DescriptionBlack2'>Tissue</td><td colspan='2'><span class='DescriptionGreen2'>" . $row->Tissue . "</span></td>" : "",
$row->Source_Gone ? "<td id='DescriptionBlack2'>Tissue source material gone</td><td colspan='3'><span class='DescriptionGreen2'>Yes</span></td>" : "",
"</tr>",
$row->Preservation ? "<tr><td id='DescriptionBlack2'>Preservation</td><td colspan='6'><span class='DescriptionGreen2'>" . $row->Preservation . "</span></td></tr>" : "",
"<tr><td colspan='7'><h4>Extraction </h4></td></tr>",
$row->Extraction_Date || $row->NoExtractDate ? "<tr><td id='DescriptionBlack2'>Extraction Date</td><td colspan='5'><span class='DescriptionGreen2'>" . $row->Extraction_Date . $NoExtractDate . "</span></td></tr>" : "",
"<tr>",
$row->Extraction_Method ? "<td id='DescriptionBlack2'>Extraction Method</td><td colspan='2'><span class='DescriptionGreen2'>" . $row->Extraction_Method . " (" . $row->Extraction_Company . ")</span></td>" : "",
$Extraction_Staff ? "<td id='DescriptionBlack2'>Extraction Staff</td><td colspan='3'><span class='DescriptionGreen2'>" . $Extraction_Staff . "</span></td>" : "",
"</tr>",
$row->Absorbance280 || $row->Absorbance230 || $row->Concentration || $row->Purification_Method || $row->Degradation || $row->Check_Date ?"<tr><td colspan='7'><h4>Quality </h4></td></tr>" : "",
$row->Absorbance280 || $row->Absorbance230 ? "<tr><td id='DescriptionBlack2'>Ratio of Absorbance</td>" : "",
$row->Absorbance280 ? "<td colspan='2'><span class='DescriptionGreen2'>" . $row->Absorbance280 . " OD<sub>260nm</sub>/OD<sub>280nm</sub></span></td>" : "",
$row->Absorbance230 ? "<td></td><td colspan='3'><span class='DescriptionGreen2'>" . $row->Absorbance230 . " OD<sub>260nm</sub>/OD<sub>230nm</sub></span></td>" : "",
$row->Absorbance280 || $row->Absorbance230 ? "</tr>" : "",
"<tr>",
$row->Concentration ? "<td id='DescriptionBlack2'>Concentration</td><td colspan='2'><span class='DescriptionGreen2'>" . $row->Concentration . " ng/μl</span></td>" : "",
$row->Purification_Method ? "<td id='DescriptionBlack2'>Purification Method</td><td colspan='3'><span class='DescriptionGreen2'>" . $row->Purification_Method . " (" . $row->Purificaton_Company . ")</span></td>" : "",
"</tr>",
"<tr>",
$row->Degradation ? "<td id='DescriptionBlack2'>Quality</td><td colspan='2'><span class='DescriptionGreen2'>" . $row->Degradation . "</span></td>" : "",
$row->Check_Date ? "<td id='DescriptionBlack2'>Quality Check Date</td><td colspan='3'><span class='DescriptionGreen2'>" . $row->Check_Date . "</span></td>" : "",
"</tr>",
$Link2 || $Fragment[$row->ID_DNA] ? "<tr><td colspan='7'><h4>GenBank Entries </h4></td></tr>" : "",
"<tr>",
$Fragment[$row->ID_DNA] ? "<td id='DescriptionBlack2'>Genetic Locus</td><td colspan='6'><span class='DescriptionGreen2'>" . $Fragment[$row->ID_DNA] . "</span></td>" : "",
"</tr>",
$Link2 ? "<tr><td id='DescriptionBlack2' valign='top'>GenBank Acc.No/<br/>BOLD Process ID</td><td colspan='6'>" .$Link2 . "</td></tr>" : "",
$Provided_By_People || $row->Block_Until || $row->Block_General || $row->Notes ? "<tr><td colspan='7'><h4>Notes </h4></td></tr>" : "",
$Provided_By_People ? "<tr><td id='DescriptionBlack2'>DNA Sample Provided By</td><td colspan='6'><span class='DescriptionGreen2'>" . $Provided_By_People . "</span></td></tr>" : "",
"<tr>",
$row->Block_Until ? "<td id='DescriptionBlack2'>Blocked Until</td><td colspan='2'><span class='DescriptionGreen2'>" . $row->Block_Until . "</span></td>" : "",
$row->Block_General ? "<td id='DescriptionBlack2'>Blocked in General</td><td colspan='3'><span class='DescriptionGreen2'>Yes</span></td>" : "",
"</tr>",
"<tr>",
$row->Notes ? "<tr><td id='DescriptionBlack2'>Remarks for Customers</td><td colspan='6' valign='top'><span class='DescriptionGreen2'>" . $row->Notes . "</span></td>" : "";
if ((isset($_SESSION['guest']) && $_SESSION['guest']) || ((isset($_SESSION['project']) || $_SESSION['project']) && $row->Created_Who != $log) )
{}
else {
echo $row->Notes_Intern ? "<tr><td id='DescriptionBlack2'>Internal Remarks</td><td colspan='6' valign='top'><span class='DescriptionGreen2'>" . $row->Notes_Intern . "</span></td>" : ""; }
echo "</tr>";
if ((isset($_SESSION['guest']) && $_SESSION['guest']) || ((isset($_SESSION['project']) || $_SESSION['project']) && $row->Created_Who != $log) )
{}
else {
echo "<tr class='background1'><td><h4>Stock/Aliquots</h4></td><td align='center'><h4>Fridge/Rack/Box</h4></td><td align='center'><h4>Barcode</h4></td><td align='center'><h4>Position</h4></td><td align='center'><h4>Source (µl)</h4></td><td align='center'><h4>Remaining (µl)</h4></td><td><h4>Price</h4></td></tr>",
$row->Stock_Gone ? "<tr><td id='DescriptionBlack2'>Stock material gone</td><td colspan='6'><span class='DescriptionGreen2'>Yes</span></td></tr>" : "",
$Location_Fridge || $Location_Rack || $Location_Box || $Location_Volume || $Stock_Barcode || $Stock_Position ? "<tr><td id='DescriptionBlack2'><span class='DescriptionGreen2'>Stock ".$row->DNA_Bank_Number."   </span></td><td align='center'><span class='DescriptionGreen2'>" . $Location_Fridge . "/" . $Location_Rack . " /" . $Location_Box . "</span></td><td align='center'><span class='DescriptionGreen2'> ".$Stock_Barcode."</span></td><td align='center'><span class='DescriptionGreen2'>".$Stock_Position."</span></td><td align='center'><span class='DescriptionGreen2'>" .$Location_Volume . "</span></td><td align='center'><span class='DescriptionGreen2'>" . $Location_Rest . "</span></td></tr>" : "<tr><td id='DescriptionBlack2'><span class='DescriptionGreen2'>Stock ".$row->DNA_Bank_Number."   </span></td><td colspan='6'><span class='error'>No Stock made</span></td></tr>";
while($row2 = mysql_fetch_object($result9))
{
$Test4[] = $row2->Aliquots_Box;
$Test5[] = $row2->Aliquots_Rack;
$Test6[] = $row2->Aliquots_Fridge;
$Test7[] = $row2->Aliquot_Number;
$Test9[] = $row2->Aliquot_Origin;
$Test10[] = $row2->Aliquot_Rest;
$Test11[] = $row2->Aliquot_Barcode;
$Test12[] = $row2->Aliquot_Position;
$Test13[] = $row2->Price." ".$row2->Currency; }
foreach($Test7 as $index => $NumberTest)
{
$RackTest = $Test5[$index];
$FridgeTest = $Test6[$index];
$BoxTest = $Test4[$index];
$SourceQuantity = $Test9[$index];
$RestQuantity = $Test10[$index];
$Barcode = $Test11[$index];
$Position = $Test12[$index];
$Price = $Test13[$index];
echo "<tr><td id='DescriptionBlack2'>Aliquot ".$NumberTest."</td><td valign='top' align='center'><span class='DescriptionGreen2'>".$FridgeTest."/".$RackTest."/".$BoxTest;
echo "</span></td><td align='center'><span class='DescriptionGreen2'>".$Barcode."</span></td><td align='center'><span class='DescriptionGreen2'>".$Position."</span></td><td align='center'><span class='DescriptionGreen2'>".$SourceQuantity."</span></td><td align='center'><span class='DescriptionGreen2'>".$RestQuantity."</span></td><td align='center'><span class='DescriptionGreen2'>".$Price."</span></td></tr>"; }
} // END if (!isset($_SESSION['guest']) && !$_SESSION['guest'])
echo $row->Backup_Aliquot ? "<tr><td id='DescriptionBlack2'>Backup Aliquot made</td><td colspan='6'><span class='DescriptionGreen2'>Yes</span></td></tr>" : "<tr><td id='DescriptionBlack2'>Backup Aliquot made</td><td colspan='6'><span class='DescriptionGreen2'>No</span></td></tr>";
echo $Paper_Cache[$row->ID_DNA] ? "<tr><td valign='top'><h4 class='background1'>Publications </h4></td><td valign='top'><b>Fragment</b><br><span class='DescriptionGreen2'>" . $FragmentPub[$row->ID_DNA] . "</span></td><td colspan='4'><b>Publication</b><br><span class='DescriptionGreen2'>" . $Paper_Cache[$row->ID_DNA] . "</span></td>" : "",
$LinkPub[$row->ID_DNA] && $Paper_Cache[$row->ID_DNA] ? "<td><br>" . $LinkPub2 ."</td></tr>" : "";
if (!isset($_SESSION['guest']) && !$_SESSION['guest'] && !isset($_SESSION['project']) && !$_SESSION['project']) {
echo $Customer[$row->ID_DNA] ? "<tr><td valign='top'><h4 class='background1'>Requests </h4></td><td colspan='3'><b>Customer</b><br><span class='DescriptionGreen2'>" . $Customer[$row->ID_DNA] . "</span></td><td><b>Date</b><br><span class='DescriptionGreen2'>" . $DateRequest . "</span></td><td><b>Aliquot No</b><br><span class='DescriptionGreen2'>" . $Request_Number_Aliquots[$row->ID_DNA] . "<(span></td><td valign='top'><b>Notes</b><br><span class='DescriptionGreen2'>" . $Request_Notes[$row->ID_DNA] . "</span></td></tr>" : ""; }
echo "</table>";
/********************************************************************************************
* Building table for DNA details *
* END *
*********************************************************************************************/
/********************************************************************************************
* Building table for specimen/voucher details *
* START *
*********************************************************************************************/
echo "<table border='0' id='tablesearch'>";
echo "<tr><td colspan='5'>";
$ID_Dataset = $row->ID_Dataset;
$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_Resource1 = urlencode($row->Digir_Resource);
$Digir_Source1 = urlencode($row->Digir_Source);
$ID_Schema = $row->ID_Schema;
$SourceUnitID = urlencode($row->UnitID_Specimen);
$SourceInstitutionCode = urlencode($row->InstitutionCode_Specimen);
$SourceCode = urlencode($row->CollectionCode_Specimen);
if($ID_Dataset == '1' and $Provider == '')
{ /**
* when specimen data are recorded in Specimen Tool ($ID_Dataset == '1') and this data are
* not yet available via wrapper ($Provider == '') include another script
*/
include ("../sptool/sptool.php"); }
else { //data availabe via wrapper
$searchform = true;
/**
* calling script verify.php for getting specimen data from original data provider
* sending parameter $searchform = true in opposite to calling same script during Input package
* see script verify.php for more details
*/
include ("../input/verify.php");
if($ProviderCheck == true) {
echo "<h6>Specimen details (voucher) </h6></td></tr>",
"<tr><td id='DescriptionGreen'>Specimen Number/UnitID</td><td>" . $row->UnitID_Specimen . "</td><td id='DescriptionGreen'>Specimen database</td><td colspan='2'>" . $row->SpecimenDatabase . "</td></tr>",
"<tr><td id='DescriptionGreen'>Collection Code</td><td>" . $row->CollectionCode_Specimen . "</td><td id='DescriptionGreen'>Institution Code</td><td colspan='2'>" . $row->InstitutionCode_Specimen . "</td></tr>",
"<tr><td colspan='5'><h4 class='background1'>Identifications</h4></td></tr>",
"<tr><td valign='top' id='DescriptionGreen'>Taxon Name </td><td valign='top'>";
echo $NameAuthorYear[$row->ID_DNA];
echo "</td>",
$HigherTaxon[$row->ID_DNA] ? "<td valign='top' id='DescriptionGreen'>Higher Taxon</td><td valign='top'>" . $HigherTaxon[$row->ID_DNA] . "</td><td valign='top'>" . $TaxonRank[$row->ID_DNA] . "</td></tr>" : "",
"<tr><td colspan='5'><h4 class='background1'>Gathering</h4></td></tr>",
"<tr>",
$row->CountryName ? "<td id='DescriptionGreen'>Country</td><td>" . $row->CountryName . "</td>" : "",
$row->CountryISO2 ? "<td id='DescriptionGreen'>Country ISO Code</td><td colspan='2'>" . $row->CountryISO2 . "</td>" : "",
"</tr>",
"<tr>",
$row->ContinentOrOcean ? "<td id='DescriptionGreen'>Continent/Ocean</td><td>" . $row->ContinentOrOcean . "</b></td>" : "",
$row->Locality ? "<td id='DescriptionGreen' valign='top'>Locality</td><td colspan='2'>" . $row->Locality . "</td>" : "",
"</tr>",
"<tr>",
$row->Longitude || $row->Latitude ? "<td id='DescriptionGreen'>Long | Lat </td><td>" . $row->Longitude . " | ".$row->Latitude."</td>" : "",
$row->Altitude ? "<td id='DescriptionGreen'>Altitude</td><td colspan='2'>" . $row->Altitude . "</td>" : "",
"</tr>",
"<tr>",
$row->Collectors ? "<td id='DescriptionGreen'>Collector(s)</td><td>" . $row->Collectors . "</td>" : "",
$row->CollectionDate ? "<td id='DescriptionGreen'>Collection Date</td><td colspan='2'>" . $row->CollectionDate . "</td>" : "",
"</tr>",
"<tr>",
$row->CollectorsNo ? "<td id='DescriptionGreen'>Collectors' Number</td><td>" . $row->CollectorsNo . "</td>" : "",
$row->CollectionInfo_Other ? "<td id='DescriptionGreen'>Collection</td><td colspan='2'>" . $row->CollectionInfo_Other . "</td>" : "",
"</tr>",
$ImageLink ? "<tr><td id='DescriptionGreen'>Multimedia</td><td colspan='4'>" . $ImageLink . "</td></tr>" : "",
$row->TypeStatus ? "<tr><td id='DescriptionGreen'>Type Status</td><td colspan='4'>" . $row->TypeStatus . "</td></tr>" : "";
} // if($ProviderCheck == true)
} //data availabe via wrapper
echo "<tr><td colspan='5'><hr></td></tr></table>";
/********************************************************************************************
* Building table for specimen/voucher details *
* END *
*********************************************************************************************/
/********************************************************************************************
* Building table for related DNA samples (source = same voucher/specimen) and details about *
* creation date and staff of record and date and staff of last edit process *
* START *
*********************************************************************************************/
echo "<table border='0' id='tablesearch' class='main'>";
if(mysql_num_rows($test) > 0)
{ echo "<tr><td colspan='7'><h6>Other DNA Samples from this voucher</h6></td></tr>";
while($roww = mysql_fetch_object($test))
{ $Test_ID = $roww->ID_DNA;
echo "<tr><td id='DescriptionBlack2'>Extraction No</td><td colspan='6'>".build_href("query_clean.php","sqlType=Detail&ID_DNA=$Test_ID",$roww->DNA_Bank_Number)."</td></tr>"; }
echo "<tr><td colspan='7'><hr /></td></tr>"; }
echo "<tr><td colspan='7'><h4>Dataset </h4></td></tr>",
"<tr><td id='DescriptionBlack2'>Created by</td><td colspan='6'><span class='DescriptionGreen2'>" . $row->Created_Who . " (" . $row->Created_When . ")</span></td></tr>",
$row->Update_Who ? "<tr><td id='DescriptionBlack2'>Last modified by</td><td colspan='6'><span class='DescriptionGreen2'>" . $row->Update_Who . " (" . $row->Update_When . ")</span></td></tr>" : "",
"<tr><td colspan='7' align='right'>";
if($formSubmitSave)
{$sql0 = "UPDATE dnabanknumbers SET Lock_Who = '', Lock_When = '' WHERE dnabanknumbers.ID_DNA = '$ID_DNA'";
$result0 = mysql_query($sql0);}
else {
if ((isset($_SESSION['guest']) && $_SESSION['guest']) || ((isset($_SESSION['project']) || $_SESSION['project']) && $row->Created_Who != $log) )
{}
else {
/********************************************************************************************
* Locking record if another user is editing the same record at the same time *
* see also update_clean.php line 29 *
*********************************************************************************************/
$sql10 = "SELECT ID_DNA, Lock_Who, Lock_When FROM dnabanknumbers WHERE ID_DNA = '$ID_DNA'";
$result10 = mysql_query($sql10);
while($row = mysql_fetch_object($result10))
{ $TestWho = $row->Lock_Who;
$TestWhen = $ow->Lock_When;}
if($TestWho == "")
{ echo "<input type='submit' name='formSubmitUpdate' value='Edit Dataset'></td></tr>"; }
if($TestWho != "")
{
$TestWhen2 = $TestWhen + "0000-00-00 00:30:00";
if($TestWhen2 >= $Current_Date)
{
if($TestWho != $log)
{echo "Dataset temporarily not editable."; }
}
if($TestWhen2 < $Current_Date or $TestWho == $log)
{ $sql11 = "UPDATE dnabanknumbers SET Lock_Who = '', Lock_When = '' WHERE dnabanknumbers.ID_DNA = '$ID_DNA'";
$result11 = mysql_query($sql11);
echo "<input type='submit' name='formSubmitUpdate' value='Edit Dataset'></td></tr>";
}}
} }
echo "</table>";
/********************************************************************************************
* Building table for related DNA samples (source = same voucher/specimen) and details about *
* creation date and staff of record and date and staff of last edit process *
* END *
*********************************************************************************************/
} // if($Dnacount<=$pagesize)
} //$Dnacount=0; while($row = mysql_fetch_object($result1))
} // if($sqlType=="Detail")
/********************************************************************************************
* Show details of a single record ($sqlType == "Detail") *
* END *
*********************************************************************************************/
mysql_query("DROP TABLE IF EXISTS tmpDnaIDs");
} //user has access to data
} //if(!empty($sql))
} /********************************************************************************************
* Saving edited record *
* END *
*********************************************************************************************/
?>
</td>
</tr>
</form>
</table>
<?php
/**
* including footer
*/
include("../footer.php"); ?>
</div>
</body>
</html>

Documentation generated on Tue, 29 Nov 2011 10:45:04 +0100 by phpDocumentor 1.4.3

Source for file query_clean.php