Source for file data_cleaning.php

Documentation is available at data_cleaning.php

  1. <?php
  2. /**
  3. * Data Cleaning
  4. * <br/><br/>
  5. * Start script for cleaning DNA data
  6. * <br/><br/>
  7. * Access for all users except of guests
  8. * @author Gabriele Droege, DNA Bank Network <contact@dnabank-network.org>
  9. * @version 2.0
  10. * @package Data_Cleaning
  11. * @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
  12. * @filesource
  13. * @license http://www.mozilla.org/MPL/ MPL
  14. */
  15.  
  16.     /**
  17.     * calling connection to database
  18.     */
  19.     include ("../config/head.php");
  20.  
  21.     /**
  22.     * calling authorisation script
  23.     */
  24.     include ("../auth.php");
  25. ?>
  26. <html>
  27.  
  28. <head>
  29. <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  30. <title>DNA Module - Data Cleaning Tool</title>
  31. <link rel="stylesheet" type="text/css" href="../format.css">
  32. <link rel="stylesheet" type="text/css" href="../input/XSLT/Styles.css">
  33. <SCRIPT LANGUAGE="JavaScript1.1" src="../Functions.js"></SCRIPT>
  34. <NOSCRIPT>Please enable Javascript!</NOSCRIPT>
  35.  
  36. </head>
  37.  
  38. <body>
  39. <div align="center">
  40. <?php 
  41.     /**
  42.     * including navigation menu
  43.     */
  44.     include("../tablenavi.php"); ?>
  45. <form name="formular" method="POST" action="query_clean.php">
  46. <table id="tablemain" class="main">
  47.     <tr>
  48.   <td id="tdLogo"><a href="<?php echo $Path_Wiki."/Features#Input"; ?>" target="_blank"><img border="0" src="../images/Logo-Wiki.png" height="78"></a></td>
  49.         <td id="tdtitle">
  50.         <h1>Data Cleaning Tool</h1></td>
  51.     <td id="tdLogo2"><?php echo $Path_Logo; ?></td>
  52.     </tr>
  53.   <tr>
  54.   <td colspan="4" align="center"><?php
  55.  
  56.     echo "Logged in as: ";
  57.     $log = $_SESSION["log"];
  58.     echo $log;
  59.     ?></td>
  60.   </tr>
  61.  
  62. <?php 
  63.     /********************************************************************************************
  64.     * permission denied for guests                                                              *
  65.     *********************************************************************************************/
  66.  
  67. if (isset($_SESSION['guest']) || $_SESSION['guest']) {
  68.  
  69. echo "<tr><td colspan='4'><span class='error'>Sorry, you don't have permission for this site! If you want to search for existing DNA data please use the Search function!</span></td></tr>"; }
  70.  
  71. else {  
  72.     /********************************************************************************************
  73.     * check for incomplete data                                                                 *
  74.     * some further restrictions for members of group "project"                                  *
  75.     * START                                                                                     *
  76.     *********************************************************************************************/
  77.   ?> 
  78.     <tr>
  79.         <td colspan="4">
  80.         <table border="0" id="table2">
  81.         <tr><td colspan="3"><h3>Listed below are empty parameters in your DNA database. If you want to see the individual records please select individual checkboxes and submit "Show details".</h3></td></tr>
  82.         <th>Parameter</th><th>Details</th><th>Select</th>
  83.                 <tr>
  84.                 <td id="DescriptionBlack">
  85.                 DNA Extraction Number: *</td>
  86.                 <td id="entry">
  87.          <?php
  88.         $sql1 = 'SELECT ID_DNA FROM dnabanknumbers WHERE (Dna_Bank_Number = "" OR Dna_Bank_Number IS NULL)';
  89.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  90.         $sql1 .= " AND Created_Who = '".$log."'"; }
  91.         $result1 = mysql_query($sql1);
  92.         if(!mysql_affected_rows()) { $DNANo = "true"; 
  93.         echo "<span class='fine'>No entries found empty.</span>"; }
  94.         else {  $count = mysql_num_rows($result1);
  95.         if($count == 1) { echo "<span class='error'>".$count." entry found empty.</span>"; }
  96.         else {  echo "<span class='error'>".$count. " entries found empty.</span>"; } }
  97.        /* $sql2 = mysql_query("SELECT DISTINCT dnabanknumbers.ID_DNA, dnabanknumbers.Dna_Bank_Number " .
  98.                 ", COUNT(*) AS number FROM dnabanknumbers " .
  99.                 "GROUP BY dnabanknumbers.Dna_Bank_Number HAVING number > 1");
  100.         if(!mysql_affected_rows()) { 
  101.         $DNADouble = "true";
  102.         echo "<br><span class='fine'>No double entries found.</span>"; }
  103.         else { echo "<span class='error'><br />Following DNA no. are double recorded: </span><br />";
  104.         while($roww = mysql_fetch_object($sql2))
  105.         {  $formDna = $roww->Dna_Bank_Number."; ";
  106.             echo $formDna; } } */
  107.         ?>
  108.          </td>
  109.          <td>
  110.          <?php if ($DNANo != "true") {
  111.          echo "<input type='checkbox' name='formDnaBankNumber' value='empty'/><br/>"; } 
  112.     //     if ($DNADouble != "true") {
  113.     //     echo "<input type='checkbox' name='formDnaBankNumberDouble' value='double' />"; } 
  114.          ?>
  115.          </td>
  116.          </tr>
  117.          <tr>
  118.     <td id="DescriptionBlackA">
  119.     Relation to Voucher: *</td>
  120.     <td id="entry">
  121. <?php
  122.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_RelationDNA_Voucher = "" OR ID_RelationDNA_Voucher = "0" OR ID_RelationDNA_Voucher IS NULL)';
  123.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  124.         $sql .= " AND Created_Who = '".$log."'"; }
  125.         $result = mysql_query($sql);
  126.         
  127.         if(!mysql_affected_rows()) { $Relation = "true";
  128.         echo "<span class='fine'>No entries found empty.</span>"; }
  129.         else { $count = mysql_num_rows($result);
  130.         if($count == 1) { echo "<span class='error'>".$count." entry found empty.</span>"; }
  131.         else {  echo "<span class='error'>".$count. " entries found empty.</span>"; } }
  132.         ?>
  133.     </td>   
  134.     <td>
  135.          <?php if ($Relation != "true") {
  136.          echo "<input type='checkbox' name='formRelation' value='empty' />"; } ?>
  137.          </td>          
  138.             </tr>
  139.       <tr>
  140.       <td id="DescriptionBlackA">
  141.       Voucher data: *</td>
  142.       <td id="entry">
  143. <?php
  144.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Cache NOT IN (SELECT ID_Cache FROM cachecollection))';
  145.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  146.         $sql .= " AND Created_Who = '".$log."'"; }        
  147.         $result = mysql_query($sql);        
  148.         
  149.         if(!mysql_affected_rows()) { $VoucherData = "true";
  150.         echo "<span class='fine'>No entries found empty.</span>"; }
  151.         else { $count = mysql_num_rows($result);
  152.         if($count == 1) { echo "<span class='error'>".$count." entry found without voucher data.</span>"; }
  153.         else {  echo "<span class='error'>".$count. " entries found without voucher data.</span>"; } }
  154.         ?>      
  155.       </td>
  156.       <td>
  157.         <?php if ($VoucherData != "true") {
  158.         echo "<input type='checkbox' name='formNoVoucherData' />"; } ?>
  159.       </td>
  160.       </tr>
  161.     <tr>
  162.     <td id="DescriptionBlack">
  163.     Tissue:</td>
  164.     <td id="entry">
  165. <?php
  166.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Tissue = "" OR ID_Tissue = "0" OR ID_Tissue IS NULL)';
  167.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  168.         $sql .= " AND Created_Who = '".$log."'"; }        
  169.         $result = mysql_query($sql);        
  170.         
  171.         if(!mysql_affected_rows()) { $Tissue = "true";
  172.         echo "<span class='fine'>No entries found empty.</span>"; }
  173.         else { $count = mysql_num_rows($result);
  174.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  175.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  176.         ?>
  177.     </td>
  178.     <td>
  179.           <?php if ($Tissue != "true") {
  180.          echo "<input type='checkbox' name='formTissue' value='empty' />"; } ?>
  181.          </td>    
  182.     </tr>
  183.     <tr>
  184.         <td id="DescriptionBlack">
  185.                 Preservation:</td>
  186.                 <td id="entry">
  187. <?php
  188.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Preservation = "" OR ID_Preservation = "0" OR ID_Preservation IS NULL)';
  189.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  190.         $sql .= " AND Created_Who = '".$log."'"; }        
  191.         $result = mysql_query($sql);        
  192.                 
  193.         if(!mysql_affected_rows()) { $Preservation = "true";
  194.         echo "<span class='fine'>No entries found empty.</span>"; }
  195.         else { $count = mysql_num_rows($result);
  196.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  197.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  198.         ?>
  199.     </td>
  200.     <td>
  201.          <?php if ($Preservation != "true") {
  202.          echo "<input type='checkbox' name='formPreservation' value='empty' />"; } ?>
  203.          </td>
  204.     </tr>  
  205.         <tr>
  206.         <td id="DescriptionBlack">
  207.         DNA Type:</td>
  208.         <td id="entry">
  209. <?php
  210.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Type = "" OR ID_Type = "0" OR ID_Type IS NULL)';
  211.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  212.         $sql .= " AND Created_Who = '".$log."'"; }        
  213.         $result = mysql_query($sql);        
  214.         
  215.         if(!mysql_affected_rows()) { $Type = "true";
  216.         echo "<span class='fine'>No entries found empty.</span>"; }
  217.         else { $count = mysql_num_rows($result);
  218.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  219.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  220.         ?>
  221.     </td>
  222.     <td>
  223.          <?php if ($Type != "true") {
  224.          echo "<input type='checkbox' name='formType' value='empty' />"; } ?>
  225.          </td>    
  226.         </tr>
  227.  
  228.                 <tr>
  229.                 <td id="DescriptionBlack">
  230.                 DNA Extraction Method: *</td>
  231.                 <td id="entry"> 
  232. <?php
  233.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Extraction_Method = "" OR ID_Extraction_Method = "0" OR ID_Extraction_Method IS NULL)';
  234.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  235.         $sql .= " AND Created_Who = '".$log."'"; }        
  236.         $result = mysql_query($sql);        
  237.         
  238.         if(!mysql_affected_rows()) { $ExtractionMethod = "true";
  239.         echo "<span class='fine'>No entries found empty.</span>"; }
  240.         else { $count = mysql_num_rows($result);
  241.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  242.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  243.         ?>
  244.     </td>
  245.     <td>
  246.          <?php if ($ExtractionMethod != "true") {
  247.          echo "<input type='checkbox' name='formExtractionMethod' value='empty' />"; } ?>
  248.          </td>    
  249.     </tr>
  250.         <tr>
  251.                 <td id="DescriptionBlack">
  252.                     
  253.                 DNA Extraction Date: *</td>
  254.                 <td id="entry"> 
  255. <?php
  256.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (Extraction_Date = "" OR Extraction_Date IS NULL)';
  257.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  258.         $sql .= " AND Created_Who = '".$log."'"; }        
  259.         $result = mysql_query($sql);        
  260.         
  261.         if(!mysql_affected_rows()) { $ExtractionDate = "true";
  262.         echo "<span class='fine'>No entries found empty.</span>"; }
  263.         else { $count = mysql_num_rows($result);
  264.         if($count == 1) { echo "<span class='error'>".$count." entry found empty.</span>"; }
  265.         else {  echo "<span class='error'>".$count. " entries found empty.</span>"; } }
  266.         ?>
  267.     </td>
  268.     <td>
  269.          <?php if ($ExtractionDate != "true") {
  270.          echo "<input type='checkbox' name='formExtractionDate' value='empty' />"; } ?>
  271.          </td>
  272.                 </tr>
  273.                 <tr>
  274.                 <td id="DescriptionBlack">
  275.                 DNA Extraction Staff: *</td>
  276.                 <td id="entry"> 
  277. <?php
  278.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Extraction_Staff = "" OR ID_Extraction_Staff = "0" OR ID_Extraction_Staff IS NULL)';
  279.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  280.         $sql .= " AND Created_Who = '".$log."'"; }        
  281.         $result = mysql_query($sql);        
  282.         
  283.         if(!mysql_affected_rows()) { $ExtractionStaff = "true";
  284.         echo "<span class='fine'>No entries found empty.</span>"; }
  285.         else { $count = mysql_num_rows($result);
  286.         if($count == 1) { echo "<span class='error'>".$count." entry found empty.</span>"; }
  287.         else {  echo "<span class='error'>".$count. " entries found empty.</span>"; } }
  288.         ?>
  289.     </td>
  290.     <td>
  291.          <?php if ($ExtractionStaff != "true") {
  292.          echo "<input type='checkbox' name='formExtractionStaff' value='empty' />"; } ?>
  293.          </td>    
  294.     </tr>
  295.             <tr>
  296.                 <td id="DescriptionBlack">
  297.                 DNA Purification Method:
  298.                 </td>
  299.             <td id="entry">
  300. <?php
  301.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Purification = "" OR ID_Purification = "0" OR ID_Purification IS NULL)';
  302.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  303.         $sql .= " AND Created_Who = '".$log."'"; }        
  304.         $result = mysql_query($sql);                
  305.         
  306.         if(!mysql_affected_rows()) { $Purification = "true";
  307.         echo "<span class='fine'>No entries found empty.</span>"; }
  308.         else { $count = mysql_num_rows($result);
  309.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  310.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  311.         ?>            
  312.       </td>
  313.     <td>
  314.          <?php if ($Purification != "true") {
  315.          echo "<input type='checkbox' name='formPurificationMethod' value='empty' />"; } ?>
  316.          </td>        
  317.                 </tr>
  318.                 <tr>
  319.                 <td id="DescriptionBlack">
  320.                 Ratios of absorbence <a id="Black">OD<sub>260nm</sub>/OD<sub>280nm</sub>:<br>
  321.         OD<sub>260nm</sub>/OD<sub>230nm</sub>:</a></td>
  322.                 <td id="entry"> 
  323.                 <?php
  324.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (Absorbance280 = "" OR Absorbance280 IS NULL)';
  325.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  326.         $sql .= " AND Created_Who = '".$log."'"; }        
  327.         $result = mysql_query($sql);                
  328.         
  329.         if(!mysql_affected_rows()) { $R280 = "true";
  330.         echo "<span class='fine'>No entries found empty.</span>"; }
  331.         else { $count = mysql_num_rows($result);
  332.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  333.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  334.         echo "<br />";
  335.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (Absorbance230 = "" OR Absorbance230 IS NULL)';
  336.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  337.         $sql .= " AND Created_Who = '".$log."'"; }        
  338.         $result = mysql_query($sql);  
  339.         
  340.         if(!mysql_affected_rows()) { $R230 = "true";
  341.         echo "<span class='fine'>No entries found empty.</span>"; }
  342.         else { $count = mysql_num_rows($result);
  343.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  344.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  345.         ?>    
  346.       </td>
  347.       <td>
  348.          <?php if ($R280 != "true") {
  349.          echo "<input type='checkbox' name='formAbsorbance280' value='empty' /><br/>"; }
  350.          if ($R230 != "true") {
  351.          echo "<input type='checkbox' name='formAbsorbance230' value='empty' />"; } ?>
  352.          </td>        
  353.         </tr>
  354.                 <tr>
  355.  
  356.                 <td id="DescriptionBlack">
  357.                 DNA Concentration:</td>
  358.                 <td id="entry"> 
  359. <?php
  360.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (Concentration = "" OR Concentration IS NULL)';
  361.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  362.         $sql .= " AND Created_Who = '".$log."'"; }        
  363.         $result = mysql_query($sql);                
  364.         
  365.         if(!mysql_affected_rows()) { $Concentration = "true";
  366.         echo "<span class='fine'>No entries found empty.</span>"; }
  367.         else { $count = mysql_num_rows($result);
  368.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  369.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  370.         ?>    
  371.             </td>
  372.       <td>
  373.          <?php if ($Concentration != "true") {
  374.          echo "<input type='checkbox' name='formConcentration' value='empty' />"; } ?>
  375.          </td>        
  376.                 </tr>
  377.                 <tr>
  378.                 <td id="DescriptionBlack">
  379.  
  380.                 DNA Quality:</td>
  381.                 <td id="entry"> 
  382. <?php
  383.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Degradation = "" or ID_Degradation = "0" OR ID_Degradation IS NULL)';
  384.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  385.         $sql .= " AND Created_Who = '".$log."'"; }        
  386.         $result = mysql_query($sql);                
  387.         
  388.         if(!mysql_affected_rows()) { $Quality = "true";
  389.         echo "<span class='fine'>No entries found empty.</span>"; }
  390.         else { $count = mysql_num_rows($result);
  391.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  392.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  393.         ?>                             
  394.         </td>
  395.     <td>
  396.          <?php if ($Quality != "true") {
  397.          echo "<input type='checkbox' name='formDegradation' value='empty' />"; } ?>
  398.          </td>    
  399.     </tr>
  400.     <tr>
  401.                 <td id="DescriptionBlack">
  402.                 DNA Quality Check Date:</td>
  403.                 <td id="entry"> 
  404. <?php
  405.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (Check_Date = "" or Check_Date IS NULL)';
  406.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  407.         $sql .= " AND Created_Who = '".$log."'"; }        
  408.         $result = mysql_query($sql);                
  409.         
  410.         if(!mysql_affected_rows()) { $CheckDate = "true";
  411.         echo "<span class='fine'>No entries found empty.</span>"; }
  412.         else { $count = mysql_num_rows($result);
  413.         if($count == 1) { echo "<span class='error2'>".$count." entry found empty.</span>"; }
  414.         else {  echo "<span class='error2'>".$count. " entries found empty.</span>"; } }
  415.         ?>    
  416.     </td>
  417.     <td>
  418.          <?php if ($CheckDate != "true") {
  419.          echo "<input type='checkbox' name='formCheckDate' value='empty' />"; } ?>
  420.          </td>        
  421.                 </tr>
  422.                 <tr>
  423.                 <td id="DescriptionBlack">
  424.                 Location Stock Solution:</td>
  425.                 <td id="entry">
  426. <?php
  427.  
  428.         $sql = 'SELECT ID_DNA FROM dnabanknumbers WHERE (ID_Location_Stock = "" OR ID_Location_Stock = "0" OR ID_Location_Stock IS NULL)';
  429.         if (isset($_SESSION['project']) || $_SESSION['project']) {
  430.         $sql .= " AND Created_Who = '".$log."'"; }        
  431.         $result = mysql_query($sql);                
  432.         
  433.         if(!mysql_affected_rows()) { $Stock = "true";
  434.         echo "<span class='fine'>No entries found empty.</span>"; }
  435.         else { $count = mysql_num_rows($result);
  436.         if($count == 1) { echo "<span class='error'>".$count." entry found empty.</span>"; }
  437.         else {  echo "<span class='error'>".$count. " entries found empty.</span>"; } }
  438.         ?>    
  439.      </td>
  440.      <td>
  441.          <?php if ($Stock != "true") {
  442.          echo "<input type='checkbox' name='formStock' value='empty' />"; } ?>
  443.          </td>     
  444.      </tr>
  445.      
  446.     <?php 
  447.     /********************************************************************************************
  448.     * check for incomplete data                                                                 *
  449.     * END                                                                                       *
  450.     *********************************************************************************************/ ?>
  451.     
  452.         <tr>
  453.     <td colspan="2"></td>
  454.         <td id="tdbutton">
  455.                     <input type="submit" value="Show details" name="formSubmitSearch" id="SelectLong">
  456.                 </td>        
  457.         </tr>
  458.   <?php } ?>
  459.    <tr>
  460.        <td colspan="2"></td>
  461.            <td id="tdbutton">
  462.                     <input type="button" value="Return to Main Menu" name="formSubmitMenu" id="SelectLong" onClick="self.location.href = '../Index.php';">
  463.              </td>
  464.         </tr>    
  465. <?php if (!isset($_SESSION['guest']) && !$_SESSION['guest']) { ?>
  466.     <tr>
  467.   <td colspan="3">* Required field, please fill out missing data!</td>
  468.     </tr> <?php } ?>
  469.             </table>
  470.         </td>
  471.     </tr>
  472. </table>
  473. <?php
  474.     /**
  475.     * including footer
  476.     */
  477.     include("../footer.php"); ?> 
  478. </div>
  479. </form>
  480. </body>
  481.  
  482. </html>

Documentation generated on Tue, 29 Nov 2011 10:44:49 +0100 by phpDocumentor 1.4.3