<?php
/**
* saving new specimen data from original specimen data provider
* <br/><br/>
* final script, all subfiles ("Save_xy.php") provide final content in standardised way, used only for changing reference to voucher
* @author Gabriele Droege, DNA Bank Network <contact@dnabank-network.org>
* @version 2.0
* @package Search
* @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
*/
if (isset($_SESSION['guest']) || $_SESSION['guest']) {

echo "<tr><td colspan='3'><span class='error'>Sorry, as a guest you don't have permission for entering new DNA data! If you want to search for existing DNA data please use the Search function!</span></td></tr>"; }

else {


if($formProviderIntern) { $IDDataset = $formProviderIntern; }
if($formProviderExtern) { $IDDataset = $formProviderExtern; }

    /********************************************************************************************
    * at first, check if specimen data are already in cache tables                              *
    * if so, count how many DNA samples are yet connected to this record                       *
    ********************************************************************************************/

$test = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT ID_Cache FROM cachecollection " .
                    "WHERE UnitID_Specimen = '$sqlUnitID' " .
                    "  AND CollectionCode_Specimen = '$sqlSourceName' " .
                    "  AND InstitutionCode_Specimen = '$sqlSourceInstitutionCode' " .
                    "  AND FK_Dataset_Specimen = '$IDDataset'");
                    
while ($row = mysqli_fetch_object($test))
{ $ID_Cache = $row->ID_Cache; }

$test2 = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT ID_TaxonIdentified, NameAuthorYear FROM cachetaxonidentified WHERE FK_Cache = '$ID_Cache'");

while ($row = mysqli_fetch_object($test2))
{ $View = $row->NameAuthorYear; }


if(mysqli_num_rows($test))
{$IDTableCache = $ID_Cache; 
    

$Exist = mysqli_query($GLOBALS["___mysqli_ston"], "SELECT ID_DNA, DNA_Bank_Number FROM dnabanknumbers " .
                     "WHERE FK_Cache = '$IDTableCache'");
                     
$count = mysqli_num_rows($Exist);

echo "<span class='fine'>Data have been saved: <br />Specimen Number: </span><span class='error'>".$sqlUnitID."</span><span class='fine'> Institution Code: </span><span class='error'>".$sqlSourceInstitutionCode."</span><span class='fine'> Collection Code: </span><span class='error'>".$sqlSourceName."</span><span class='fine'> Taxon Name: </span><span class='error'>".$View."</span><br />";
if ($count == '1')
{ echo "<span class='fine'>".$count." DNA Sample already exist for the selected specimen!</span><br />"; }
if($count > 1)
{ echo "<span class='fine'>".$count." DNA Samples already exist for the selected specimen!</span><br />"; }


}

if(!mysqli_num_rows($test))
{
if(isset($Identifications))
{
  
    /********************************************************************************************
    * first table to fill is cachecollection                                                    *
    ********************************************************************************************/

  $sql4 = "INSERT INTO cachecollection (UnitID_Specimen, CollectionCode_Specimen, InstitutionCode_Specimen, ContinentOrOcean, CountryName, CountryISO2, Collectors, CollectionDate, CollectionDateMin, CollectionDateMax, CollectorsNo, Locality, Longitude, Latitude, Altitude, CollectionInfo_Other, FK_Dataset_Specimen, Created_Who) VALUES ('$sqlUnitID','$sqlSourceName','$sqlSourceInstitutionCode','$sqlContinentOrOcean','$sqlCountryName','$sqlISO2','$sqlPersonName','$sqlCollectionDate','$sqlCollectionDateMin','$sqlCollectionDateMax','$sqlCollectorsNo','$sqlLocality','$sqlLongitude','$sqlLatitude','$sqlAltitude','$sqlGatheringOther','$IDDataset','$log')";

 $result4 = mysqli_query($GLOBALS["___mysqli_ston"], $sql4);
$IDTableCache=((is_null($___mysqli_res = mysqli_insert_id($GLOBALS["___mysqli_ston"]))) ? false : $___mysqli_res);

###############################################Error 4####################################################


if(!$result4)
     { 
  $msg = $sql4."\n";
  $msg .= "####Error 4####";
  if($debug == '1') echo $msg;
  trigger_error($msg, E_USER_ERROR);
  include ("../config/Errorreport.php"); 
  $error = "true"; } 
         
#########################################################################################################    

  foreach ($Identifications as $IndexIdentification => $Identification) {
  
    /********************************************************************************************
    * for each identification tag entering one row in cachetaxonidentified                      *
    ********************************************************************************************/


  $Genus = $sqlGenus[$IndexIdentification];
  $Subgenus = $sqlSubgenus[$IndexIdentification];
  $FirstEpithet = $sqlFirstEpithet[$IndexIdentification];
  $SecondEpithet = $sqlSecondEpithet[$IndexIdentification];
  $Rank = $sqlRank[$IndexIdentification];
  $HybridFlag = $sqlHybridFlag[$IndexIdentification];
  $NameAuthorYear = $sqlNameAuthorYearString[$IndexIdentification];
  $Name_All = $sqlName_All[$IndexIdentification];
  $PreferredFlag = $sqlPreferredIdentificationFlag[$IndexIdentification];
  
  $sql5 = "INSERT INTO cachetaxonidentified (FK_Cache, Genus, Subgenus, FirstEpithet, SecondEpithet, Rank, HybridFlag, NameAuthorYear, Other, PreferredFlag, Created_Who) VALUES ('$IDTableCache','$Genus','$Subgenus','$FirstEpithet','$SecondEpithet','$Rank','$HybridFlag','$NameAuthorYear','$Name_All','$PreferredFlag','$log')";
  $result5 = mysqli_query($GLOBALS["___mysqli_ston"], $sql5);

  $IDTableCacheTaxonIdentified=((is_null($___mysqli_res = mysqli_insert_id($GLOBALS["___mysqli_ston"]))) ? false : $___mysqli_res);
  
#################################################Error 5################################################  
  if(!$result5)
         { $msg = $sql5."\n";
           $msg .= "####Error 5####";
		   if($debug == '1') echo $msg;
           trigger_error($msg, E_USER_ERROR);
           include ("../config/Errorreport.php"); 
           $error = "true"; } 
         
########################################################################################################         

      foreach ($HigherTaxa as $index => $HigherTaxon) { // more than one HigherTaxon per Identification possible
      
    /********************************************************************************************
    * for each identification tag and higher taxon tag entering one row in cachehighertaxa      *
    ********************************************************************************************/      
       
         $TaxonHigher = $sqlHigherTaxon[$index];
         $RankHigher = $sqlHigherTaxonRank[$index];

      $sql6 = "INSERT INTO cachehighertaxa (FK_CacheTaxonIdentified, HigherTaxon, TaxonRank, Created_Who) VALUES ('$IDTableCacheTaxonIdentified','$TaxonHigher','$RankHigher','$log')";
      $result6 = mysqli_query($GLOBALS["___mysqli_ston"], $sql6);

#################################################Error 6################################################
if(!$result6)
         { $msg = $sql6."\n";
           $msg .= "####Error 6####";
		   if($debug == '1') echo $msg;
           trigger_error($msg, E_USER_ERROR);
           include ("../config/Errorreport.php"); 
           $error = "true"; } 
         
########################################################################################################         
  }
 }
 
if(isset($sqlFileURI))
{ 
foreach ($sqlFileURI as $MultiMediaObject)
  {
    /*********************************************************************************************
    * for each multimedia tag entering one row in cacheimages                                    *
    *********************************************************************************************/ 
  $sql7 = "INSERT INTO cacheimages (FK_Cache, Images, Created_Who) VALUES ('$IDTableCache','$MultiMediaObject','$log')";
  $result7 = mysqli_query($GLOBALS["___mysqli_ston"], $sql7);
  
#######################################Error 7###############################################################

if(!$result7)
{ $msg = $sql7."\n";
  $msg .= "####Error 7####";
  if($debug == '1') echo $msg;
  trigger_error($msg, E_USER_ERROR);
  include ("../config/Errorreport.php");   
  $error = "true"; }
  
#############################################################################################################    
} 
if(empty($sqlFileURI)) {
foreach ($sqlProductURI as $MultiMediaObject)
  {
   
  $sql8= "INSERT INTO cacheimages (FK_Cache, Images, Created_Who) VALUES ('$IDTableCache','$MultiMediaObject','$log')";
  $result8 = mysqli_query($GLOBALS["___mysqli_ston"], $sql8);
  
#######################################Error 8###############################################################

if(!$result8)
{ $msg = $sql8."\n";
  $msg .= "####Error 8####";
  if($debug == '1') echo $msg;
  trigger_error($msg, E_USER_ERROR);
  include ("../config/Errorreport.php");   
  $error = "true"; }
  
#############################################################################################################    
} } }


if($result4)
{ 

echo "<span class='fine'>Data have been saved: <br />Specimen Number: </span><span class='error'>".$sqlUnitID."</span><span class='fine'> Institution Code: </span><span class='error'>".$sqlSourceInstitutionCode."</span><span class='fine'> Collection Code: </span><span class='error'>".$sqlSourceName."</span><span class='fine'> Taxon Name: </span><span class='error'>".$NameAuthorYear."</span><br />"; }   
  }
  
  else {

 $sql7 = "INSERT INTO cachecollection (UnitID_Specimen, CollectionCode_Specimen, InstitutionCode_Specimen, ContinentOrOcean, CountryName, Collectors, CollectionDate, CollectionDateMin, CollectionDateMax, CollectorsNo, Locality, Longitude, Latitude, Altitude, FK_Dataset_Specimen, Created_Who) VALUES ('$sqlUnitID','$sqlSourceName','$sqlSourceInstitutionCode','$sqlContinentOrOcean','$sqlCountryName','$sqlPersonName','$sqlCollectionDate','$sqlCollectionDateMin','$sqlCollectionDateMax','$sqlCollectorsNo','$sqlLocality','$sqlLongitude','$sqlLatitude','$sqlAltitude','$IDDataset','$log')";

 $result7 = mysqli_query($GLOBALS["___mysqli_ston"], $sql7);
$IDTableCache=((is_null($___mysqli_res = mysqli_insert_id($GLOBALS["___mysqli_ston"]))) ? false : $___mysqli_res);

#######################################Error 7###############################################################

if(!$result7)
{ $msg = $sql7."\n";
  $msg .= "####Error 7####";
  if($debug == '1') echo $msg;
  trigger_error($msg, E_USER_ERROR);
  include ("../config/Errorreport.php");   
  $error = "true"; }
  
############################################################################################################# 

if($result7)
{ 

echo "<span class='fine'>Data have been saved: <br />Specimen Number: </span><span class='error'>".$sqlUnitID."</span><span class='fine'> Institution Code: </span><span class='error'>".$sqlSourceInstitutionCode."</span><span class='fine'> Collection Code: </span><span class='error'>".$sqlSourceName."</span><span class='fine'> Taxon Name: </span><span class='error'>".$sqlNameAuthorYearString."</span><br />";  }

 $sql8 = "INSERT INTO cachetaxonidentified (FK_Cache, Genus, Subgenus, FirstEpithet, SecondEpithet, Rank, NameAuthorYear, Created_Who) VALUES ('$IDTableCache','$sqlGenus','$sqlSubgenus','$sqlFirstEpithet','$sqlSecondEpithet','$sqlRank','$sqlNameAuthorYearString','$log')";
 $result8 = mysqli_query($GLOBALS["___mysqli_ston"], $sql8);

######################################################Error 8###########################################
if(!$result8)
         { $msg = $sql8."\n";
           $msg .= "####Error 8####";
		   if($debug == '1') echo $msg;
           trigger_error($msg, E_USER_ERROR);
           include ("../config/Errorreport.php"); 
           $error = "true"; } 
         
########################################################################################################         

  $IDTableCacheTaxonIdentified=((is_null($___mysqli_res = mysqli_insert_id($GLOBALS["___mysqli_ston"]))) ? false : $___mysqli_res);


 foreach ($sqlHigherTaxon as $index => $HigherTaxa) { // more than one HigherTaxon per Identification possible 
 
 if(!empty($sqlHigherTaxon[$index])) {
         $TaxonHigher = $sqlHigherTaxon[$index];
         $RankHigher = $sqlHigherTaxonRank[$index];

    $sql9 = "INSERT INTO cachehighertaxa (FK_CacheTaxonIdentified, HigherTaxon, TaxonRank, Created_Who) VALUES ('$IDTableCacheTaxonIdentified','$TaxonHigher','$RankHigher','$log')";
    $result9 = mysqli_query($GLOBALS["___mysqli_ston"], $sql9);

########################################################Error 9###########################################
if(!$result9)
         { $msg = $sql9."\n";
           $msg .= "####Error 9####";
		   if($debug == '1') echo $msg;
           trigger_error($msg, E_USER_ERROR);
           include ("../config/Errorreport.php"); 
           $error = "true"; } 
##########################################################################################################           
  } }
if(isset($sqlFileURI))
{
foreach ($sqlFileURI as $MultiMediaObject)
  {
   
  $sql10 = "INSERT INTO cacheimages (FK_Cache, Images, Created_Who) VALUES ('$IDTableCache','$MultiMediaObject','$log')";
  $result10 = mysqli_query($GLOBALS["___mysqli_ston"], $sql10);
  
#######################################Error 10###############################################################

if(!$result10)
{ $msg = $sql10."\n";
  $msg .= "####Error 10####";
  if($debug == '1') echo $msg;
  trigger_error($msg, E_USER_ERROR);
  include ("../config/Errorreport.php");   
  $error = "true"; }
  
#############################################################################################################    
} 
if(empty($sqlFileURI))
{
foreach ($sqlProductURI as $MultiMediaObject)
  {
   
  $sql11= "INSERT INTO cacheimages (FK_Cache, Images, Created_Who) VALUES ('$IDTableCache','$MultiMediaObject','$log')";
  $result11 = mysqli_query($GLOBALS["___mysqli_ston"], $sql11);
  
#######################################Error 11###############################################################

if(!$result11)
{ $msg = $sql11."\n";
  $msg .= "####Error 11####";
  if($debug == '1') echo $msg;
  trigger_error($msg, E_USER_ERROR);
  include ("../config/Errorreport.php");   
  $error = "true"; }
  
#############################################################################################################    
}   } }
}
}
}

?>