<!-- 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
if($formSubmitVerify)
{

   $sqlcreate = "CREATE TEMPORARY TABLE tmpOfflineIDs TYPE=HEAP ";
   
         
if (isset($_REQUEST['NoSpecimenAvailable']))
  { 
    if($formAddLimits == '1')
    { $sql = $sqlcreate .
      $sql = "SELECT DISTINCT tableofflinegathering.ID_Gathering FROM tableofflinegathering LEFT JOIN tableofflinetaxa ON tableofflinegathering.ID_Gathering = tableofflinetaxa.ID_Gathering WHERE tableofflinegathering.UnitID_Specimen = '' " .
             "AND ID_Cache != '' " .
             "ORDER BY tableofflinetaxa.NameAuthorYear"; }
    if($formAddLimits == '2')
    { $sql = $sqlcreate .
      $sql = "SELECT DISTINCT tableofflinegathering.ID_Gathering FROM tableofflinegathering LEFT JOIN tableofflinetaxa ON tableofflinegathering.ID_Gathering = tableofflinetaxa.ID_Gathering WHERE tableofflinegathering.UnitID_Specimen = '' " .
             "AND ID_Cache = '' " .
             "ORDER BY tableofflinetaxa.NameAuthorYear"; }         
    if($formAddLimits == '' ) {
    $sql = $sqlcreate .
    $sql = "SELECT DISTINCT tableofflinegathering.ID_Gathering FROM tableofflinegathering LEFT JOIN tableofflinetaxa ON tableofflinegathering.ID_Gathering = tableofflinetaxa.ID_Gathering WHERE tableofflinegathering.UnitID_Specimen = '' " .
             "ORDER BY tableofflinetaxa.NameAuthorYear";; } }  

if ($formSpecimenNumber != "") {  
   $sql = $sqlcreate .
   $sql = "SELECT DISTINCT tableofflinegathering.ID_Gathering FROM tableofflinegathering LEFT JOIN tableofflinetaxa ON tableofflinegathering.ID_Gathering = tableofflinetaxa.ID_Gathering WHERE tableofflinegathering.UnitID_Specimen = '$formSpecimenNumber' " .
             "ORDER BY tableofflinetaxa.NameAuthorYear"; }
$Offlinecount=0;   
  
if(!empty($sql)) 
 {
      // just in case it is still here: drop tmpTitleIDs
    mysql_query("DROP TABLE IF EXISTS tmpOfflineIDs");
  // create new table with title IDs
    mysql_query($sql);     

   if(!mysql_affected_rows()) 
    { echo "<span class='error'>No hits. Please verify your entry and selection of specimen database. </span>";}  
    else 
    {     
   
   $result1 = mysql_query("SELECT tableofflinegathering.ID_Gathering, " .
                      "tableofflinegathering.UnitID_Specimen, " .
                      "tableofflinegathering.CollectionCode_Specimen, " .
                      "tableofflinegathering.InstitutionCode_Specimen, " .
                      "tableofflinegathering.ContinentOrOcean, " .
                      "tableofflinegathering.CountryName, " .
                      "tableofflinegathering.CountryISO2, " .
                      "tableofflinegathering.SeasAndOceans, " .
                      "tableofflinegathering.GatheringAgent, " .
                      "tableofflinegathering.GatheringDate, " .
                      "tableofflinegathering.GatheringNo, " .
                      "tableofflinegathering.TitleLabel, " .
                      "tableofflinegathering.ExpeditionDetails, " .
                      "tableofflinegathering.Region, " .
                      "tableofflinegathering.Locality, " .
                      "tableofflinegathering.Biotope, " .
                      "tableofflinegathering.Altitude, " .
                      "tableofflinegathering.LongitudeDecimal, " .
                      "tableofflinegathering.LatitudeDecimal, " .
                      "tableofflinegathering.Protected, " .
                      "tableofflinegathering.Admission, " . 
                      "tablesex.Sex, " .
                      "tablebasisofrecordspecimen.BasisOfRecord_Specimen " .
                      "FROM tableofflinegathering JOIN tmpOfflineIDs " .
                      "LEFT JOIN tablesex ON tableofflinegathering.ID_Sex = tablesex.ID_Sex " .
                      "LEFT JOIN tablebasisofrecordspecimen ON tableofflinegathering.ID_BasisOfRecord = tablebasisofrecordspecimen.ID_BasisOfRecord_Specimen " .
                      "WHERE tableofflinegathering.ID_Gathering = tmpOfflineIDs.ID_Gathering"); 

                      
 $result2a = mysql_query("SELECT DISTINCT tableofflinetaxa.ID_Gathering, " .
                        "tableofflinetaxa.ID_Taxonidentified, " .
                        "tableofflinetaxa.Genus, " .
                        "tableofflinetaxa.Subgenus, " .
                        "tableofflinetaxa.FirstEpithet, " .
                        "tableofflinetaxa.SecondEpithet, " .
                        "tableofflinetaxa.Rank, " .
                        "tableofflinetaxa.Author, " .
                        "tableofflinetaxa.Year, " .
                        "tableofflinetaxa.NameAuthorYear, " .
                        "tableofflinetaxa.Identifier, " .
                        "tableofflinetaxa.IdentificationDate, " .
                        "tableofflinetaxa.PreferredFlag, " .
                        "tableofflinetaxa.EntryNameFlag, " .
                        "tableofflinetaxa.Notes " .
//                        "tablespecimentype.SpecimenType " .
                        "FROM tableofflinetaxa JOIN tmpOfflineIDs " .
//                        "LEFT JOIN ON tablespecimentype ON tableofflinetaxa.ID_Type = tablespecimentype.ID_SpecimenType " .
                        "WHERE tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering " .
                        "  AND tableofflinetaxa.PreferredFlag = '1' " .
                        "ORDER BY tableofflinetaxa.NameAuthorYear");

$result2b = mysql_query("SELECT DISTINCT tableofflinetaxa.ID_Gathering, " .
                        "tableofflinetaxa.ID_Taxonidentified, " .
                        "tableofflinetaxa.Genus, " .
                        "tableofflinetaxa.Subgenus, " .
                        "tableofflinetaxa.FirstEpithet, " .
                        "tableofflinetaxa.SecondEpithet, " .
                        "tableofflinetaxa.Rank, " .
                        "tableofflinetaxa.Author, " .
                        "tableofflinetaxa.Year, " .
                        "tableofflinetaxa.NameAuthorYear, " .
                        "tableofflinetaxa.Identifier, " .
                        "tableofflinetaxa.IdentificationDate, " .
                        "tableofflinetaxa.PreferredFlag, " .
                        "tableofflinetaxa.EntryNameFlag, " .
                        "tableofflinetaxa.Notes " .
                        "FROM tableofflinetaxa JOIN tmpOfflineIDs " .
                        "WHERE tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering " .
                        "  AND tableofflinetaxa.PreferredFlag = '0'");                        
  
  while($row = mysql_fetch_object($result2a))
  { 
         $tmp = "<span class='details'>".$row->NameAuthorYear."</span>"; 
         $NameAuthorYear1[$row->ID_Gathering] .= $tmp;
         
         $tmp = "<span class='details'>".$row->Identifier."</span>"; 
         $Identifier1[$row->ID_Gathering] .= $tmp;
         
         $tmp = "<span class='details'>".$row->IdentificationDate."</span>"; 
         $IdentificationDate1[$row->ID_Gathering] .= $tmp;      
         
         $tmp = "<span class='details'>".$row->Notes."</span>"; 
         $Notes1[$row->ID_Gathering] .= $tmp;      
         
        }
        
while($row = mysql_fetch_object($result2b))
  { 
  
       if(isset($NameAuthorYear[$row->ID_Gathering]))
         { $NameAuthorYear[$row->ID_Gathering] .= "<br>"; }
        else
         { $NameAuthorYear[$row->ID_Gathering] = ""; }
         
       if(isset($Identifier[$row->ID_Gathering]))
         { $Identifier[$row->ID_Gathering] .= "<br>"; }
        else
         { $Identifier[$row->ID_Gathering] = ""; }  
         
       if(isset($IdentificationDate[$row->ID_Gathering]))
         { $IdentificationDate[$row->ID_Gathering] .= "<br>"; }
        else
         { $IdentificationDate[$row->ID_Gathering] = ""; }   
         
       if(isset($Notes[$row->ID_Gathering]))
         { $Notes[$row->ID_Gathering] .= "<br>"; }
        else
         { $Notes[$row->ID_Gathering] = ""; }     
        
        $tmp = "<span class='label'>".$row->Identifier."</span>"; 
         $Identifier[$row->ID_Gathering] .= $tmp;
         
        $tmp = "<span class='label'>".$row->IdentificationDate."</span>"; 
         $IdentificationDate[$row->ID_Gathering] .= $tmp;
       
        $tmp = "<span class='label'>".$row->Notes."</span>"; 
         $Notes[$row->ID_Gathering] .= $tmp;  
         
         $tmp = "<span class='label'>".$row->NameAuthorYear."</span>"; 
         $NameAuthorYear[$row->ID_Gathering] .= $tmp; 
        }
  
    
$result3a = mysql_query("SELECT DISTINCT tableofflinetaxa.ID_Gathering, " .
                       "tableofflinetaxa.ID_TaxonIdentified, " .
                       "tableofflinehighertaxon.ID_Taxa, " .
                       "tableofflinehighertaxon.HigherTaxon as HigherTaxon1, " .
                       "tableofflinehighertaxon.TaxonRank as TaxonRank1 " .
                       "FROM tableofflinetaxa, tableofflinehighertaxon, tmpOfflineIDs " .
                       "WHERE tableofflinehighertaxon.ID_Taxa = tableofflinetaxa.ID_TaxonIdentified " .
                       "  AND tableofflinetaxa.PreferredFlag = '1' " .
                       "  AND tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering ");
                       
$result3b = mysql_query("SELECT DISTINCT tableofflinetaxa.ID_Gathering, " .
                       "tableofflinetaxa.ID_TaxonIdentified, " .
                       "tableofflinehighertaxon.ID_Taxa, " .
                       "tableofflinehighertaxon.HigherTaxon as HigherTaxon2, " .
                       "tableofflinehighertaxon.TaxonRank as TaxonRank2 " .
                       "FROM tableofflinetaxa, tableofflinehighertaxon, tmpOfflineIDs " .
                       "WHERE tableofflinehighertaxon.ID_Taxa = tableofflinetaxa.ID_TaxonIdentified " .
                       "  AND tableofflinetaxa.PreferredFlag = '0' " .
                       "  AND tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering ");                     
                       
  while($row = mysql_fetch_object($result3a))
  { 
       
       if(isset($HigherTaxon1[$row->ID_Gathering]))
         { 
           $HigherTaxon1[$row->ID_Gathering].$TaxonRank1[$row->ID_Gathering] .= "<br>";
         }
        else
         { $HigherTaxon1[$row->ID_Gathering] = ""; }
         
        $tmp = "<span class='details'>".$row->HigherTaxon1."</span><span class='label'> (".$row->TaxonRank1.")</span>; ";
        $HigherTaxon1[$row->ID_Gathering].$TaxonRank1[$row->ID_Gathering] .= $tmp;  
         
         $Ausgabe1 .= $tmp;     
        
        }
  
  while($row = mysql_fetch_object($result3b))
  { 
       
       if(isset($HigherTaxon2[$row->ID_Gathering]))
         { 
           $HigherTaxon2[$row->ID_Gathering].$TaxonRank2[$row->ID_Gathering] .= "<br>";
         }
        else
         { $HigherTaxon2[$row->ID_Gathering] = ""; }
         
         
         
        $tmp = "<span class='label'>".$row->HigherTaxon2."</span><span class='label'> (".$row->TaxonRank2.")</span>; ";
        $HigherTaxon2[$row->ID_Gathering].$TaxonRank2[$row->ID_Gathering] .= $tmp;       
        $Ausgabe2 .= $tmp;
        }  
        
        
$result4 = mysql_query("SELECT tableofflineimages.ID_OfflineGathering, " .
                        "tableofflineimages.ID_Images, " .
                        "tableofflineimages.ImageLink " .
                        "FROM tableofflineimages, tmpOfflineIDs " .
                        "WHERE tableofflineimages.ID_OfflineGathering = tmpOfflineIDs.ID_Gathering");
                        
while($row = mysql_fetch_object($result4))
{
$Image = 'true';
          if(isset($ImageLink[$row->ID_Gathering])) 
          { $ImageLink[$row->ID_Gathering] .= "<br> "; }
        else
          { $ImageLink[$row->ID_Gathering] = ""; }
          
          $tmp = build_href1($row->ImageLink,"Link");
          $ImageLink[$row->ID_Gathering] .= $tmp;
          

      }        
        
          
$gesamt = mysql_num_rows($result1);
if($gesamt == 1)
{
while($row = mysql_fetch_object($result1))
  {  
   
    echo "<tr><td colspan='6'>";
    echo "<hr size='1'/><h3>Unit details: </h3><table id='tabledetails'>";
     echo "<input name='formUnitID[0]' value='".$row->UnitID_Specimen."' type='hidden'><input name='formSourceInstitutionID[0]' value='".$row->InstitutionCode_Specimen."' type='hidden'><input name='formformSourceID[0]' value='".$row->CollectionCode_Specimen."' type='hidden'>";
    echo "<tr><td valign='top'><input name='formDna' value='".$row->ID_Gathering."' type='hidden'><span class='label'>Prefered Taxon Name: </span><span class='prefered'>".$NameAuthorYear1[$row->ID_Gathering]."</span>";
    echo "<br/><span class='label'>Unit ID: </span><span class='details'>".$row->UnitID_Specimen."</span>";
    echo "<br/><span class='label'>Basis of Record: </span><span class='details'>".$row->BasisOfRecord_Specimen."</span>";
    echo "</td><td align='right' valign='top'><span class='label'>Institution Code: </span><span class='details'>".$row->InstitutionCode_Specimen."</span>";
    echo "<br/><span class='label'>Collection Code: </span><span class='details'>".$row->CollectionCode_Specimen."</span><br/></td></tr></table>";
    echo "<h3>Identification(s): </h3><table id='tabledetails'>";
    echo "<tr><td valign='top'><span class='label'>Name: </span></td><td valign='top'>" . $NameAuthorYear1[$row->ID_Gathering]. "</td></tr>";
    echo "<tr><td valign='top'><span class='label'>Identifier: </span></td><td valign='top'>" . $Identifier1[$row->ID_Gathering]. "</td></tr>";
    echo "<tr><td valign='top'><span class='label'>Date: </span></td><td valign='top'>" . $IdentificationDate1[$row->ID_Gathering]. "</td></tr>";
    echo $Notes1[$row->ID_Gathering] ? "<tr><td valign='top'><span class='label'>Notes: </span></td><td valign='top'>" . $Notes1[$row->ID_Gathering]. "</td></tr>" : ""; 
    echo $Ausgabe1 ? "<tr><td valign='top'><span class='label'>Taxonomy:</span></td><td>" . $Ausgabe1 . "</td></tr>" : "";
    echo $NameAuthorYear[$row->ID_Gathering] ?  "<tr><td valign='top'><span class='label'>Name: </span></td><td valign='top'>" . $NameAuthorYear[$row->ID_Gathering]. "</td></tr>" : "";
    echo $Identifier[$row->ID_Gathering] ? "<tr><td valign='top'><span class='label'>Identifier: </span></td><td valign='top'>" . $Identifier[$row->ID_Gathering]. "</td></tr>" : "";
    echo $IdentificationDate[$row->ID_Gathering] ? "<tr><td valign='top'><span class='label'>Date: </span></td><td valign='top'>" . $IdentificationDate[$row->ID_Gathering]. "</td></tr>" : "";
    echo $Notes[$row->ID_Gathering] ? "<tr><td valign='top'><span class='label'>Notes: </span></td><td valign='top'>" . $Notes[$row->ID_Gathering]. "</td></tr>" : "";
    echo $Ausgabe2 ? "<tr><td valign='top'><span class='label'>Taxonomy:</span></td><td>" . $Ausgabe2 . "</td></tr>" : "";
    echo "</table>";
    if($Image = 'true') {
    echo "<h3>Multimedia:</h3><table id='tabledetails'><tr><td>".$ImageLink[$row->ID_Gathering]."</td></tr></table>";    }
    echo "<h3>Gathering:</h3><table id='tabledetails'>";
    echo "<tr><th>Locality:</th><th>Collector(s):</th><th>Other info:</th></tr>";
    echo $row->ContinentOrOcean ? "<tr valign='top'><td><span class='label'>Continent/Ocean: </span><span class='details'>".$row->ContinentOrOcean."</span></td>" : "<tr valign='top'><td></td>";
    echo $row->CountryName ? "<tr valign='top'><td><span class='label'>Country: </span><span class='details'>".$row->CountryName." (".$row->CountryISO2.")</span></td>" : "<tr valign='top'><td></td>";
    echo $row->SeasAndOceans ? "<tr valign='top'><td><span class='label'>Sea: </span><span class='details'>".$row->SeasAndOceans."</span></td>" : "<tr valign='top'><td></td>";
    echo $row->GatheringAgent ? "<td><span class='details'>".$row->GatheringAgent."</span></td>" : "<td></td>";
    echo $row->Altitude ? "<td><span class='label'>Altitude: </span><span class='details'>".$row->Altitude."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row->Region ? "<tr valign='top'><td colspan='2'><span class='label'>Region: </span><span class='details'>".$row->Region."</span></td>" : "<tr valign='top'><td colspan='2'>";
    echo $row->Biotope ? "<td><span class='label'>Biotope: </span><span class='details'>".$row->Biotope."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row->Locality ? "<tr valign='top'><td colspan='2'><span class='label'>Locality: </span><span class='details'>".$row->Locality."</span></td>" : "<tr calign='top'><td colspan='2'></td>";
    echo $row->GatheringDate ? "<td><span class='label'>Date: </span><span class='details'>".$row->GatheringDate."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row->LatitudeDecimal || $row->LongitudeDecimal ? "<tr valign='top'><td><span class='label'>Coordinates: </span><span class='details'>".$row->LatitudeDecimal." | ".$row->LongitudeDecimal."</span></td>" : "<tr valign='top'><td></td>";
    echo $row->GatheringNo ? "<td><span class='label'>Collection No: </span><span class='details'>".$row->GatheringNo."</span></td>" : "<td></td>";
    echo $row->TitleLabel || $row->ExpedtionDetails ? "<td><span class='label'>Project: </span><span class='details'>".$row->TitleLabel." (".$row->ExpeditionDetails.")</span></td>" : "<td></td>";
    echo "</tr>";
    echo "<tr><td colspan='3'><span class='label'>Notes: Specimen data not accessible via wrapper!</span></td></tr>";
    echo "<hr size='1'/>";
    echo "</table>";
    }
}

if($gesamt > 1)
{
   echo "<span class='error'>".$gesamt." entries found! Please select a specimen or verify your entry!</span>";
//$Offlinecount=0;

if($formAddLimits == '1')
    { echo "<br><span class='fine'><br>Data with existing DNA sample only! To see all hits without search limits press 'Verify' again!</span>"; }
    if($formAddLimits == '2')
    { echo "<br><span class='fine'><br>Data without existing DNA sample only! To see all hits without search limits press 'Verify' again!</span>"; }         
    if($formAddLimits == '' ) {
    echo "<br><select name='formAddLimits' id='InputLong'><option value=''>Add search limits</option><option value='1'>Show data with existing DNA sample only</option><option value='2'>Show data without existing DNA sample only</option></select> Please press 'Verify' again!"; }

   for ($i=1; $i<=$gesamt; $i++) {
   $row=mysql_fetch_array($result1);
   {
        $Offlinecount++;
// while($row = mysql_fetch_object($result1))
//  {  

    echo "<tr><td colspan='6'>";
    echo "<hr size='1'/>";
    echo "<input name='formTest[".$row['ID_Gathering']."]' value='Select this Specimen' type='submit'><input name='formUnitID[0]' value='true' type='hidden'>";
    echo "<h3>".$i.". Unit details: </h3><table id='tabledetails'>";
    echo "<tr><td valign='top'><span class='label'>Prefered Taxon Name: </span><span class='prefered'>".$NameAuthorYear1[$row['ID_Gathering']]." </span>".$TaxonRank1[$row['ID_Gathering']];
    echo " ".$NameAuthorYear[$row['ID_Gathering']]." ".$TaxonRank2[$row['ID_Gathering']];
    echo $Notes1[$row['ID_Gathering']] ? " <span class='label'>Notes: </span>" . $Notes1[$row['ID_Gathering']] : "";
    echo "<br/><span class='label'>Unit ID: </span><span class='error'>".$row['UnitID_Specimen']." </span>";
    echo "<span class='label'>Institution Code: </span><span class='details'>".$row['InstitutionCode_Specimen']." </span>";
    echo "<span class='label'>Collection Code: </span><span class='details'>".$row['CollectionCode_Specimen']."</span></td></tr></table>";
    if($Image = 'true') {
    echo "<h3>Multimedia2:</h3><table id='tabledetails'><tr><td>".$ImageLink[$row->ID_Gathering]."</td></tr></table>"; }
    echo "<table id='tabledetails'>";
    echo "<tr><th>Locality:</th><th>Collector(s):</th><th>Other info:</th></tr>";
    echo $row['ContinentOrOcean'] ? "<tr valign='top'><td><span class='label'>Continent/Ocean: </span><span class='details'>".$row['ContinentOrOcean']."</span></td>" : "<tr valign='top'><td></td>";
    echo $row['CountryName'] ? "<tr valign='top'><td><span class='label'>Country: </span><span class='details'>".$row['CountryName']." (".$row['CountryISO2'].")</span></td>" : "<tr valign='top'><td></td>";
    echo $row['SeasAndOceans'] ? "<tr valign='top'><td><span class='label'>Sea: </span><span class='details'>".$row['SeasAndOceans']."</span></td>" : "<tr valign='top'><td></td>";
    echo $row['GatheringAgent'] ? "<td><span class='details'>".$row['GatheringAgent']."</span></td>" : "<td></td>";
    echo $row['Altitude'] ? "<td><span class='label'>Altitude: </span><span class='details'>".$row['Altitude']."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row['Region'] ? "<tr valign='top'><td colspan='2'><span class='label'>Region: </span><span class='details'>".$row['Region']."</span></td>" : "<tr valign='top'><td colspan='2'>";
    echo $row['Biotope'] ? "<td><span class='label'>Biotope: </span><span class='details'>".$row['Biotope']."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row['Locality'] ? "<tr valign='top'><td colspan='2'><span class='label'>Locality: </span><span class='details'>".$row['Locality']."</span></td>" : "<tr calign='top'><td colspan='2'></td>";
    echo $row['GatheringDate'] ? "<td><span class='label'>Date: </span><span class='details'>".$row['GatheringDate']."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row['LatitudeDecimal'] || $row['LongitudeDecimal'] ? "<tr valign='top'><td><span class='label'>Coordinates: </span><span class='details'>".$row['LatitudeDecimal']." | ".$row['LongitudeDecimal']."</span></td>" : "<tr valign='top'><td></td>";
    echo $row['GatheringNo'] ? "<td><span class='label'>Collection No: </span><span class='details'>".$row['GatheringNo']."</span></td>" : "<td></td>";
    echo $row['TitleLabel'] || $row['ExpedtionDetails'] ? "<td><span class='label'>Project: </span><span class='details'>".$row['TitleLabel']." (".$row['ExpeditionDetails'].")</span></td>" : "<td></td>";
    echo "</tr>";
    echo "<tr><td colspan='3'><span class='label'>Notes: Specimen data not accessible via wrapper!</span></td></tr>";
    echo "<tr><td colspan='3'><hr size='1'/></td></tr>";
    echo "</table>"; 
    }}
    
    }}
} //if (!empty($sql))

}
$variable5 = $_POST['formTest'];

if($variable5 != "" and !$formSubmitVerify and !$formSubmitSave)
   {
$mykey = key($variable5); 

$sqlcreate = "CREATE TEMPORARY TABLE tmpOfflineIDs TYPE=HEAP ";
        
   $sql = $sqlcreate .
   $sql = "SELECT DISTINCT ID_Gathering FROM tableofflinegathering WHERE ID_Gathering = '$mykey'";
$Offlinecount=0;   
  
if(!empty($sql)) 
 {
      // just in case it is still here: drop tmpTitleIDs
    mysql_query("DROP TABLE IF EXISTS tmpOfflineIDs");
  // create new table with title IDs
    mysql_query($sql);     
    
   if(!mysql_affected_rows()) 
    { echo "<span class='error'>No hits. Please verify your entry and selection of specimen database. </span>";}  
    else 
    {     
   
   $result1 = mysql_query("SELECT tableofflinegathering.ID_Gathering, " .
                      "tableofflinegathering.UnitID_Specimen, " .
                      "tableofflinegathering.CollectionCode_Specimen, " .
                      "tableofflinegathering.InstitutionCode_Specimen, " .
                      "tableofflinegathering.ContinentOrOcean, " .
                      "tableofflinegathering.CountryName, " .
                      "tableofflinegathering.CountryISO2, " .
                      "tableofflinegathering.SeasAndOceans, " .
                      "tableofflinegathering.GatheringAgent, " .
                      "tableofflinegathering.GatheringDate, " .
                      "tableofflinegathering.GatheringNo, " .
                      "tableofflinegathering.TitleLabel, " .
                      "tableofflinegathering.ExpeditionDetails, " .
                      "tableofflinegathering.Region, " .
                      "tableofflinegathering.Locality, " .
                      "tableofflinegathering.Biotope, " .
                      "tableofflinegathering.Altitude, " .
                      "tableofflinegathering.LongitudeDecimal, " .
                      "tableofflinegathering.LatitudeDecimal, " .
                      "tableofflinegathering.Protected, " .
                      "tableofflinegathering.Admission, " . 
                      "tablesex.Sex, " .
                      "tablebasisofrecordspecimen.BasisOfRecord_Specimen " .
                      "FROM tableofflinegathering JOIN tmpOfflineIDs " .
                      "LEFT JOIN tablesex ON tableofflinegathering.ID_Sex = tablesex.ID_Sex " .
                      "LEFT JOIN tablebasisofrecordspecimen ON tableofflinegathering.ID_BasisOfRecord = tablebasisofrecordspecimen.ID_BasisOfRecord_Specimen " .
                      "WHERE tableofflinegathering.ID_Gathering = tmpOfflineIDs.ID_Gathering"); 

                      
 $result2a = mysql_query("SELECT tableofflinetaxa.ID_Gathering, " .
                        "tableofflinetaxa.ID_Taxonidentified, " .
                        "tableofflinetaxa.Genus, " .
                        "tableofflinetaxa.Subgenus, " .
                        "tableofflinetaxa.FirstEpithet, " .
                        "tableofflinetaxa.SecondEpithet, " .
                        "tableofflinetaxa.Rank, " .
                        "tableofflinetaxa.Author, " .
                        "tableofflinetaxa.Year, " .
                        "tableofflinetaxa.NameAuthorYear, " .
                        "tableofflinetaxa.Identifier, " .
                        "tableofflinetaxa.IdentificationDate, " .
                        "tableofflinetaxa.PreferredFlag, " .
                        "tableofflinetaxa.EntryNameFlag, " .
                        "tableofflinetaxa.Notes " .
//                        "tablespecimentype.SpecimenType " .
                        "FROM tableofflinetaxa JOIN tmpOfflineIDs " .
//                        "LEFT JOIN ON tablespecimentype ON tableofflinetaxa.ID_Type = tablespecimentype.ID_SpecimenType " .
                        "WHERE tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering " .
                        "  AND tableofflinetaxa.PreferredFlag = '1'");

$result2b = mysql_query("SELECT tableofflinetaxa.ID_Gathering, " .
                        "tableofflinetaxa.ID_Taxonidentified, " .
                        "tableofflinetaxa.Genus, " .
                        "tableofflinetaxa.Subgenus, " .
                        "tableofflinetaxa.FirstEpithet, " .
                        "tableofflinetaxa.SecondEpithet, " .
                        "tableofflinetaxa.Rank, " .
                        "tableofflinetaxa.Author, " .
                        "tableofflinetaxa.Year, " .
                        "tableofflinetaxa.NameAuthorYear, " .
                        "tableofflinetaxa.Identifier, " .
                        "tableofflinetaxa.IdentificationDate, " .
                        "tableofflinetaxa.PreferredFlag, " .
                        "tableofflinetaxa.EntryNameFlag, " .
                        "tableofflinetaxa.Notes " .
                        "FROM tableofflinetaxa JOIN tmpOfflineIDs " .
                        "WHERE tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering " .
                        "  AND tableofflinetaxa.PreferredFlag = '0'");                        
  
  while($row = mysql_fetch_object($result2a))
  { 
         $tmp = "<span class='details'>".$row->NameAuthorYear."</span>"; 
         $NameAuthorYear1[$row->ID_Gathering] .= $tmp;
         
         $tmp = "<span class='details'>".$row->Identifier."</span>"; 
         $Identifier1[$row->ID_Gathering] .= $tmp;
         
         $tmp = "<span class='details'>".$row->IdentificationDate."</span>"; 
         $IdentificationDate1[$row->ID_Gathering] .= $tmp;    
         
         $tmp = "<span class='details'>".$row->Notes."</span>"; 
         $Notes1[$row->ID_Gathering] .= $tmp;                
         
        }
        
while($row = mysql_fetch_object($result2b))
  { 
  
       if(isset($NameAuthorYear[$row->ID_Gathering]))
         { $NameAuthorYear[$row->ID_Gathering] .= "<br>"; }
        else
         { $NameAuthorYear[$row->ID_Gathering] = ""; }
         
       if(isset($Identifier[$row->ID_Gathering]))
         { $Identifier[$row->ID_Gathering] .= "<br>"; }
        else
         { $Identifier[$row->ID_Gathering] = ""; }  
         
       if(isset($IdentificationDate[$row->ID_Gathering]))
         { $IdentificationDate[$row->ID_Gathering] .= "<br>"; }
        else
         { $IdentificationDate[$row->ID_Gathering] = ""; }   
         
       if(isset($Notes[$row->ID_Gathering]))
         { $Notes[$row->ID_Gathering] .= "<br>"; }
        else
         { $Notes[$row->ID_Gathering] = ""; }     
        
        $tmp = "<span class='label'>".$row->Identifier."</span>"; 
         $Identifier[$row->ID_Gathering] .= $tmp;
         
        $tmp = "<span class='label'>".$row->IdentificationDate."</span>"; 
         $IdentificationDate[$row->ID_Gathering] .= $tmp;
         
        $tmp = "<span class='label'>".$row->Notes."</span>"; 
         $Notes[$row->ID_Gathering] .= $tmp;  
         
         $tmp = "<span class='label'>".$row->NameAuthorYear."</span>"; 
         $NameAuthorYear[$row->ID_Gathering] .= $tmp; 
        }
  
    
$result3a = mysql_query("SELECT tableofflinetaxa.ID_Gathering, " .
                       "tableofflinetaxa.ID_TaxonIdentified, " .
                       "tableofflinehighertaxon.ID_Taxa, " .
                       "tableofflinehighertaxon.HigherTaxon, " .
                       "tableofflinehighertaxon.TaxonRank " .
                       "FROM tableofflinetaxa, tableofflinehighertaxon, tmpOfflineIDs " .
                       "WHERE tableofflinehighertaxon.ID_Taxa = tableofflinetaxa.ID_TaxonIdentified " .
                       "  AND tableofflinetaxa.PreferredFlag = '1' " .
                       "  AND tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering ");
                       
$result3b = mysql_query("SELECT tableofflinetaxa.ID_Gathering, " .
                       "tableofflinetaxa.ID_TaxonIdentified, " .
                       "tableofflinehighertaxon.ID_Taxa, " .
                       "tableofflinehighertaxon.HigherTaxon, " .
                       "tableofflinehighertaxon.TaxonRank " .
                       "FROM tableofflinetaxa, tableofflinehighertaxon, tmpOfflineIDs " .
                       "WHERE tableofflinehighertaxon.ID_Taxa = tableofflinetaxa.ID_TaxonIdentified " .
                       "  AND tableofflinetaxa.PreferredFlag = '0' " .
                       "  AND tableofflinetaxa.ID_Gathering = tmpOfflineIDs.ID_Gathering ");
                       
  while($row = mysql_fetch_object($result3a))
  { 
       
       if(isset($HigherTaxon[$row->ID_Gathering]))
         { 
           $HigherTaxon[$row->ID_Gathering].$TaxonRank[$row->ID_Gathering] .= "<br>";
         }
        else
         { $HigherTaxon[$row->ID_Gathering] = ""; }
         
        $tmp = "<span class='details'>".$row->HigherTaxon."</span><span class='label'> (".$row->TaxonRank.")</span>; ";
        $HigherTaxon[$row->ID_Gathering].$TaxonRank[$row->ID_Gathering] .= $tmp;  
         
         $Ausgabe1 .= $tmp;     
        
        }
  
  while($row = mysql_fetch_object($result3b))
  { 
       
       if(isset($HigherTaxon[$row->ID_Gathering]))
         { 
           $HigherTaxon[$row->ID_Gathering].$TaxonRank[$row->ID_Gathering] .= "<br>";
         }
        else
         { $HigherTaxon[$row->ID_Gathering] = ""; }
         
         
         
        $tmp = "<span class='label'>".$row->HigherTaxon."</span><span class='label'> (".$row->TaxonRank.")</span>; ";
        $HigherTaxon[$row->ID_Gathering].$TaxonRank[$row->ID_Gathering] .= $tmp;       
        $Ausgabe2 .= $tmp;
        }      
        
$result4 = mysql_query("SELECT tableofflineimages.ID_Images, " .
                        "tableofflineimages.ID_OfflineGathering, " .
                        "tableofflineimages.ImageLink " .
                        "FROM tableofflineimages JOIN tmpOfflineIDs " .
                        "WHERE tableofflineimages.ID_OfflineGathering = tmpOfflineIDs.ID_Gathering");          
while($row = mysql_fetch_object($result4))

{if(isset($ImageLink[$row->ID_OfflineGathering]))
         { 
           $ImageLink .= build_href1($row->ImageLink,$row->ImageLink) . "<br>";
         }
        else
         {  $ImageLink .= build_href1($row->ImageLink,$row->ImageLink) . "<br>";} 
        
      }            

while($row = mysql_fetch_object($result1))
  {  
   
    echo "<tr><td colspan='6'>";
    echo "<hr size='1'/><h3>Unit details: </h3><table id='tabledetails'>";  
    echo "<input name='formUnitID[0]' value='".$row->UnitID_Specimen."' type='hidden'><input name='formSourceInstitutionID[0]' value='".$row->InstitutionCode_Specimen."' type='hidden'><input name='formformSourceID[0]' value='".$row->CollectionCode_Specimen."' type='hidden'>";   
    echo "<tr><td valign='top'><input name='formDna' value='".$row->ID_Gathering."' type='hidden'><span class='label'>Prefered Taxon Name: </span><span class='prefered'>".$NameAuthorYear1[$row->ID_Gathering]."</span>";
    echo "<br/><span class='label'>Unit ID: </span><span class='details'>".$row->UnitID_Specimen."</span>";
    echo "<br/><span class='label'>Basis of Record: </span><span class='details'>".$row->BasisOfRecord_Specimen."</span>";
    echo "</td><td align='right' valign='top'><span class='label'>Institution Code: </span><span class='details'>".$row->InstitutionCode_Specimen."</span>";
    echo "<br/><span class='label'>Collection Code: </span><span class='details'>".$row->CollectionCode_Specimen."</span><br/></td></tr></table>";
    echo "<h3>Identification(s): </h3><table id='tabledetails'>";
    echo "<tr><td valign='top'><span class='label'>Name: </span></td><td valign='top'>" . $NameAuthorYear1[$row->ID_Gathering]. "</td></tr>";
    echo "<tr><td valign='top'><span class='label'>Identifier: </span></td><td valign='top'>" . $Identifier1[$row->ID_Gathering]. "</td></tr>";
    echo "<tr><td valign='top'><span class='label'>Date: </span></td><td valign='top'>" . $IdentificationDate1[$row->ID_Gathering]. "</td></tr>";
    echo $Ausgabe1 ? "<tr><td valign='top'><span class='label'>Taxonomy:</span></td><td>" . $Ausgabe1 . "</td></tr>" : "";
    echo $NameAuthorYear[$row->ID_Gathering] ?  "<tr><td valign='top'><span class='label'>Name: </span></td><td valign='top'>" . $NameAuthorYear[$row->ID_Gathering]. "</td></tr>" : "";
    echo $Identifier[$row->ID_Gathering] ? "<tr><td valign='top'><span class='label'>Identifier: </span></td><td valign='top'>" . $Identifier[$row->ID_Gathering]. "</td></tr>" : "";
    echo $IdentificationDate[$row->ID_Gathering] ? "<tr><td valign='top'><span class='label'>Date: </span></td><td valign='top'>" . $IdentificationDate[$row->ID_Gathering]. "</td></tr>" : "";
    echo $Notes[$row->ID_Gathering] ? "<tr><td valign='top'><span class='label'>Date: </span></td><td valign='top'>" . $Notes[$row->ID_Gathering]. "</td></tr>" : "";
    echo $Ausgabe2 ? "<tr><td valign='top'><span class='label'>Taxonomy:</span></td><td>" . $Ausgabe2 . "</td></tr>" : "";
    echo "</table>";
    echo $ImageLink ? "<h3>Multimedia:</h3><table id='tabledetails'><tr><td>".$ImageLink."</td></tr></table>" : "";   
    echo "<h3>Gathering:</h3><table id='tabledetails'>";
    echo "<tr><th>Locality:</th><th>Collector(s):</th><th>Other info:</th></tr>";
    echo $row->ContinentOrOcean ? "<tr valign='top'><td><span class='label'>Continent/Ocean: </span><span class='details'>".$row->ContinentOrOcean."</span></td>" : "<tr valign='top'><td></td>";
    echo $row->CountryName ? "<tr valign='top'><td><span class='label'>Country: </span><span class='details'>".$row->CountryName." (".$row->CountryISO2.")</span></td>" : "<tr valign='top'><td></td>";
    echo $row->SeasAndOceans ? "<tr valign='top'><td><span class='label'>Sea/Ocean: </span><span class='details'>".$row->SeasAndOceans."</span></td>" : "<tr valign='top'><td></td>";
    echo $row->GatheringAgent ? "<td><span class='details'>".$row->GatheringAgent."</span></td>" : "<td></td>";
    echo $row->Altitude ? "<td><span class='label'>Altitude: </span><span class='details'>".$row->Altitude."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row->Region ? "<tr valign='top'><td colspan='2'><span class='label'>Region: </span><span class='details'>".$row->Region."</span></td>" : "<tr valign='top'><td colspan='2'>";
    echo $row->Biotope ? "<td><span class='label'>Biotope: </span><span class='details'>".$row->Biotope."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row->Locality ? "<tr valign='top'><td colspan='2'><span class='label'>Locality: </span><span class='details'>".$row->Locality."</span></td>" : "<tr calign='top'><td colspan='2'></td>";
    echo $row->GatheringDate ? "<td><span class='label'>Date: </span><span class='details'>".$row->GatheringDate."</span></td>" : "<td></td>";
    echo "</tr>";
    echo $row->LatitudeDecimal || $row->LongitudeDecimal ? "<tr valign='top'><td><span class='label'>Coordinates: </span><span class='details'>".$row->LatitudeDecimal." | ".$row->LongitudeDecimal."</span></td>" : "<tr valign='top'><td></td>";
    echo $row->GatheringNo ? "<td><span class='label'>Collection No: </span><span class='details'>".$row->GatheringNo."</span></td>" : "<td></td>";
    echo $row->TitleLabel || $row->ExpedtionDetails ? "<td><span class='label'>Project: </span><span class='details'>".$row->TitleLabel." (".$row->ExpeditionDetails.")</span></td>" : "<td></td>";
    echo "</tr>";
    echo "<tr><td colspan='3'><span class='label'>Notes: Specimen data not accessible via wrapper!</span></td></tr>";
    echo "<hr size='1'/>";
    echo "</table>";

}

    
    }}
} //if (!empty($sql))
   
   




?>