<!--
confirmation.php:
Gets spreadsheet or spreadsheets specified by user, validates data fields automatically, and inserts
them into the database if everything is in order. If there are problems, it describes them to the user
and returns to the upload page without inserting anything.
-->
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Confirm Upload - IGD Sample Database</title>
</head>
<body>
<?php
include("header.php");
define('DEBUG',false);
if(DEBUG){
ini_set("display_errors","1");
ERROR_REPORTING(E_ALL);
}
//Compare form ID stored in the session to that submitted with the most recent form.
//If they differ, form data have been submitted without going through the upload page.
//If debug mode is on, ignore this test.
if (isset($_SESSION['FORM_ID'])) {
if (DEBUG && strcasecmp($_POST['FORM_ID'], $_SESSION['FORM_ID'])) {
print "The ID number of your upload form didn't match the ID number of your current login session." .
" Going back to the upload page and/or logging in again may solve the problem.";
exit;
}
} else {
print "<meta http-equiv=\"Refresh\" content=\"0;url=upload.php\">";
return;
}
unset($_SESSION['FORM_ID']); //Clear this form's ID after checking
//Check image and upload if it's OK
for ($i = 0; isset($_FILES['gel_image_' . $i]); $i++) {
check_image('gel_image_' . $i);
}
//Check spreadsheet and upload if it's OK
check_spreadsheet();
//check_spreadsheet(): contains operations that need to be performed on each spreadsheet. Confirms that spreadsheet was uploaded, that
//the upload completed, that the spreadsheet is a .csv file, that each field matches its specification, and that each submitted plate has a blank well.
function check_spreadsheet() {
//Check existence of spreadsheet and output its name if it exists
if (is_uploaded_file($_FILES['spreadsheet']['tmp_name'])) {
if (DEBUG) {
print "Spreadsheet uploaded successfully as " . $_FILES['spreadsheet']['tmp_name'] . ".<br>";
}
} else {
print"Cannot find an uploaded spreadsheet.<br>";
$total_errors++;
}
// Check for errors in spreadsheet upload
if ($_FILES['spreadsheet']['error'] > 0) {
switch ($_FILES['spreadsheet']['error']) {
case 1: print "Spreadsheet exceeded PHP's maximum file size.";
break;
case 2: print "Spreadsheet exceeded web page's maximum file size.";
break;
case 3: print "Spreadsheet was only partially uploaded.";
break;
case 4: print "No spreadsheet uploaded.";
break;
}
exit;
}
//Check spreadsheet MIME type.
//if ($_FILES['spreadsheet']['type'] != 'text/csv' &&
// $_FILES['spreadsheet']['type'] != 'text/comma-separated-values') {
// print "<b>Warning:</b> Your spreadsheet may not be a CSV table. It identifies itself as \"" . $_FILES['spreadsheet']['type'] . "\" instead of \"text/csv\" or \"text/comma-separated-values\"." .
// " Keep in mind that some programs, such as Microsoft Excel, will not identify files as .csv even if they are valid.<br>";
//}
//Contains format descriptions to be printed when errors are detected. They are numbered according to their column number in the spreadsheet.
$format[0] = "Only letters, numbers, dots, hyphens or underscores are allowed. Maximum 255 characters.";
$format[1] = "Only alphanumeric characters, dots, hyphens, and underscores are allowed. Maximum length is 255 characters.";
$format[2] = "This is a required field. Only alphanumeric characters, dots, hyphens, or underscores are allowed. Maximum 15 characters.";
$format[3] = "This is a required field. It should be a letter from A-H, followed by a number from 1-12.";
$format[4] = "This is a required field. Maximum 255 characters.";
$format[5] = "";
$format[6] = "Maximum length is 255 characters.";
$format[7] = "Maximum length is 255 characters.";
$format[8] = "This is a required field. If this is an empty well, write <b>NA</b> in this field. Otherwise, only numbers and an optional decimal point are allowed. Maximum length is 8 characters.";
$format[9] = "This is a required field. If this is an empty well, write <b>NA</b> in this field. Otherwise, only numbers and an optional decimal point are allowed. Maximum length is 8 characters.";
$format[10] = "This is a required field. If this is an empty well, write <b>NA</b> in this field. Otherwise, only numbers and an optional decimal point are allowed. Maximum length is 8 characters.";
$format[11] = "This is a required field. Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[12] = "This is a required field. Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[13] = "Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[14] = "Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[15] = "Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[16] = "Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[17] = "This is a required field. Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[18] = "This is a required field. Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[19] = "Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[20] = "Only letters, dots and hyphens are allowed. Maximum length is 255 characters.";
$format[21] = "Only letters, numbers, dots, hyphens or underscores are allowed. Maximum length is 255 characters.";
$format[22] = "Only three letters are allowed.";
$format[23] = "Only letters, dots and hyphens allowed. Maximum length is 255 characters.";
$format[24] = "Only letters, dots and hyphens allowed. Maximum length is 255 characters.";
$format[25] = "Only numbers allowed, with an optional decimal point and sign (+/-). Maximum length is 16 characters.";
$format[26] = "Only numbers allowed, with an optional decimal point and sign (+/-). Maximum length is 16 characters.";
$format[27] = "Only numbers allowed, with an optional decimal point and sign (+/-). Maximum length is 16 characters.";
//Loop through uploaded spreadsheet and read into data structure. Check for unspecified data.
$curr_row = 0;
$total_errors = 0;
// if (($handle = fopen($_FILES['spreadsheet']['tmp_name'], "r")) !== FALSE) {
// while (($data = fgetcsv($handle, 0, ",")) !== FALSE) {
if(($file_string = file_get_contents($_FILES['spreadsheet']['tmp_name'])) !== FALSE){
$file_string = preg_replace("/\r/", "\n", $file_string);
$file_string = preg_replace("/\n+/", "\n", $file_string);
$file_string = preg_replace("/\n$/", "", $file_string); //Trim trailing newline
$file_array = preg_split("/\n/", $file_string); //Split into array of lines
//Iterate over lines
foreach ($file_array as $line) {
$data = explode(",",$line);
$num = count($data); //Number of columns
if($num > 28){
print "<b>Warning:</b> row ".$curr_row." has more than 28 columns in it. Your spreadsheet may contain an ".
"extra comma that is being read as a column separator.<br>";
$total_errors++;
break;
}
//Print error if 2nd row doesn't say what it's supposed to say -- file was probably corrupted
if ($curr_row == 1 && !preg_match('/Project Name/', $data[0])) {
print "<b>Warning:</b> The program could not read the column labels on your spreadsheet. You may have changed the labels or uploaded the wrong file.<br>";
$total_errors++;
break;
//save column headings for labels
} else if ($curr_row == 1) {
$labels = $data;
} else if ($curr_row > 1) {
for ($c = 0; $c < $num; $c++) {
$data[$c] = trim($data[$c]); //Trim surrounding whitespace
if($c==3){ $data[$c]=format_well_name($data[$c]); } //Add initial zeroes if needed
if($c==4){$data[$c] = preg_replace("/[\s\:]/", "_", $data[$c]); }//Change whitespace & colons in sample names to underscores
if ($c == 4 && preg_match('/^blank$/i', $data[$c])) { //If sample name is "blank", this is a control well
$control_well[$data[2]] = $data[3]; //Associate the well name with the plate name
$num_blanks[$data[2]]++; //Increment the number of blank wells associated with this plate
$is_control_sample = true; //Make a note to skip error checking
//print "Found blank well #".$num_blanks[$data[2]].", named \"".$data[3].
//"\" in column ".$c." of plate .".$data[2].".";
}
//Check that data are entered correctly. If so, store in 2d array. Otherwise, print error messages and count total errors
if (!valid($c, $data[$c])) {
print $labels[$c] . " field in row " . ($curr_row + 1) . " (\"$data[$c]\") contains errors: " . $format[$c] . "<br>";
$total_errors++;
}
$spreadsheet[$curr_row][$c] = $data[$c]; //Put datum into 2D array
}
$plate_name=$data[2];
$plate_names[$plate_name] = 1; //Record all plate names
//Count unique wells and total wells for each plate
$unique_wells[$plate_name][$data[3]]++;
$num_wells[$plate_name]++;
}
$curr_row++;
}
if($curr_row < 2){
print "<b>Warning:</b> No rows of data were found in the uploaded spreadsheet. ".
"You may have accidentally uploaded a template spreadsheet, or submitted ".
"a file without end-of-line characters.<br>";
$total_errors++;
}
foreach (array_keys($plate_names) as $plate1) {
foreach(array_keys($unique_wells[$plate1]) as $unique_well){
if($unique_wells[$plate1][$unique_well] > 1){
print "Plate ".$plate1." contains more than one sample record for well ".$unique_well.".<br>";
$total_errors++;
}
}
//print $num_blanks[$plate1]." blanks found in plate ".$plate1.".";
//Check that all plates have 96 wells
if ($num_wells[$plate1] > 96 || $num_wells[$plate1] < 96) {
print 'Plate "' . $plate1 . '" contains ' . $num_wells[$plate1] . ' samples. It should ordinarily have 96 (including one blank well labeled "blank").<br>';
$total_errors++;
next;
}
//Check that:
//1. All plates have at least one blank
//2. One plate, at most, has more than one blank
//3. If any two distinct plates have only only one blank each, that blank cannot be in the same location.
if ($num_blanks[$plate1]==0) {
print 'Plate "' . $plate1 . '" does not contain a sample named "blank".<br>';
$total_errors++;
break;
}
foreach (array_keys($plate_names) as $plate2) {
if ($plate1 != $plate2) {
if ($num_blanks[$plate1] > 1 && $num_blanks[$plate2] > 1) {
print '<b>Warning:</b> Plates "' . $plate1 . '" and "' . $plate2 . '" both contain more than one sample named "blank". ' .
'If a staff member has not previously agreed to this, they may reject your submission for containing partially empty plates.<br>';
print "\n".$num_blanks[$plate1]." ".$num_blanks[$plate2];
//$total_errors++;
break;
} elseif ($control_well[$plate1] == $control_well[$plate2]) {
print 'Plates "' . $plate1 . '" and "' . $plate2 . '" both contain a single sample named "blank" in the same well. ' .
'Control wells should be in different locations from plate to plate.<br>';
$total_errors++;
break;
}
}
}
}
// fclose($handle);
} else {
print "Couldn't find uploaded spreadsheet on server to check for errors.<br>";
$total_errors++;
}
//If there were no errors, pass 2D array to update function for insertion in DB
if ($total_errors==0) {
print "<center><h1>Upload Successful</h1></center>No errors were found in your data. They were uploaded successfully and have been listed below for confirmation. This submission will " .
"need to be approved by a staff member before it is committed to the database. At that point, you will receive " .
"instructions on how to prepare your sample for mailing.<br>";
update_db($spreadsheet);
} else {
print "Click the <b>Upload File</b> link above and check the field definitions. After you have corrected any errors, save and try uploading your file again.<br>";
}
}
//check_image(): contains operations that need to be performed on each uploaded gel image.
//Confirms that files were uploaded, that the upload was complete, that they do not exceed the maximum file size,
//that they can be moved to a temporary location, and that they are a .jpg or a convertible format.
function check_image($gel_image_name) {
//Make temporary variables for the properties of each file: temp file (destroyed after script executes), name, and error code.
$tempfile = $_FILES[$gel_image_name]['tmp_name'];
$name = $_FILES[$gel_image_name]['name'];
$error = $_FILES[$gel_image_name]['error'];
//Check existence of gel image and output its name if it exists
if (is_uploaded_file($tempfile)) {
if (DEBUG) {
print "Gel image uploaded successfully as " . $tempfile . ".<br>";
}
if (!preg_match('/^.*\.jpg$|^.*\.jpeg$/i', $name)) {
print "<b>Warning:</b> Your image may not be in JPEG format. The program will try to convert it. However, if you don't " .
"see your gel image at the bottom of this page after uploading, you may need to upload your samples again.<br>";
}
$command = 'identify ' . $tempfile; //Identify image file format. If it is not a JPEG, convert it into one before adding to DB.
$output = shell_exec($command);
if (DEBUG) {
print $command;
print $output;
}
if (preg_match('/error/', $output)) {
print "The image converter could not identify the format of your image file. Try converting it to .JPG format before uploading again.<br>";
exit;
} else {
$command = 'convert ' . $tempfile . ' ' . $tempfile . '.jpg';
$output = shell_exec($command);
if (DEBUG) {
print $command;
print $output;
}
}
} else {
// Check for errors in gel image upload
if ($error > 0 &&
$error < 4) {
switch ($error) {
case 1: print "Gel image exceeded PHP's maximum file size.";
break;
case 2: print "Gel image exceeded web page's maximum file size.";
break;
case 3: print "Gel image was only partially uploaded.";
break;
}
exit;
}
print"One of your gel images is missing. Please try uploading your sample data again.";
exit;
}
}
//format_well_name($well_name): Tests that well names are written as a capital letter from A-Z,
//followed by either a single-digit number with an initial zero, or a double-digit number. If
//the well name doesn't match this format, it is modified and returned; otherwise it's returned unchanged.
function format_well_name($well_name){
$well_name=strtoupper($well_name);
foreach (range(A, Z) as $i) {
foreach (range(1, 9) as $j) {
if (strcasecmp($i . $j, $well_name) == 0) {
$well_name = $i . "0" . $j;
}
}
}
return $well_name;
}
//valid($field, $contents): Peforms error checking on individual spreadsheet fields. Takes as arguments a spreadsheet field number and the contents of that field in the current row. Returns
//1 if the field contains a correctly typed datum and 0 otherwise.
function valid($field, $contents) {
if (
//Project Name
($field == 0 && preg_match('/(^.*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Source Lab
($field == 1 && preg_match('/(^[A-Za-z0-9.\-_ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Plate name
($field == 2 && preg_match('/(^.*$)/', $contents) && strlen($contents) <= 15 && strlen($contents) > 0) ||
//Well
($field == 3 && preg_match('/(^[A-Pa-p][0-9]+$)/', $contents)) ||
//Sample name
($field == 4 && preg_match('/(^.*$)/', $contents) && strlen($contents) <= 255 && strlen($contents) > 0) ||
//Pedigree
($field == 5 && preg_match('/(^.*$){0,1}/', $contents)) ||
//Population
($field == 6 && preg_match('/(^.*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Stock Number
($field == 7 && preg_match('/(^.*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Concentration
($field == 8 && preg_match('/(^[0-9]*[\.]{0,1}[0-9]+$)|[Nn][Aa]/', $contents) && strlen($contents) <= 8) ||
//Volume
($field == 9 && preg_match('/(^[0-9]*[\.]{0,1}[0-9]+$)|[Nn][Aa]/', $contents) && strlen($contents) <= 8) ||
//Mass (total DNA)
($field == 10 && preg_match('/(^[0-9]*[\.]{0,1}[0-9]+$)|[Nn][Aa]/', $contents) && strlen($contents) <= 8) ||
//Preparer
($field == 11 && preg_match('/(^[A-Za-z\.\-\ ]*$)/', $contents) && strlen($contents) <= 255) ||
//Kingdom
// ($field == 12 && preg_match('/(^[A-Za-z\.\-\ ]*$)/', $contents) && strlen($contents)<=255) ||
($field == 12 && preg_match('/plantae|animalia|protista|fungi|archaea/i', $contents) && strlen($contents) <= 255) ||
//Phylum
($field == 13 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Class
($field == 14 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Order
($field == 15 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Family
($field == 16 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Genus
($field == 17 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255 && strlen($contents) > 0) ||
//Species
($field == 18 && preg_match('/(^[A-Za-z\.\-\ ]*$)/', $contents) && strlen($contents) <= 255 && strlen($contents) > 0) ||
//Subspecies
($field == 19 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Variety
($field == 20 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Location Name
($field == 21 && preg_match('/(^.*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Country
($field == 22 && (preg_match('/^([a-zA-Z]){3}$/', $contents) || strlen($contents)==0) ) ||
//Province
($field == 23 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//City
($field == 24 && preg_match('/(^[A-Za-z\.\-\ ]*$){0,1}/', $contents) && strlen($contents) <= 255) ||
//Elevation
($field == 25 && preg_match('/(^[\+\-]{0,1}([0-9]*\.){0,1}[0-9]+$){0,1}/', $contents) && strlen($contents) <= 8) ||
//Latitude
($field == 26 && preg_match('/(^[\+\-]{0,1}([0-9]*\.){0,1}[0-9]+$){0,1}/', $contents) && strlen($contents) <= 8) ||
//Longitude
($field == 27 && preg_match('/(^[\+\-]{0,1}([0-9]*\.){0,1}[0-9]+$){0,1}/', $contents) && strlen($contents) <= 8)
) {
return(1);
} else {
return(0);
}
}
//update_db(): Connects to database, creates sample data table if necessary, and fills it with data from uploaded
//spreadsheet. Emails staffers with information about the update.
function update_db($spreadsheet) {
print "updating spreadsheet.<br>";
ini_set('magic_quotes_runtime','Off');
//Connect to DB
@$db = new mysqli(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
if (mysqli_connect_errno ()) {
print "Could not connect to DB.";
exit;
}
//Insert spreadsheet data into DB. Order of commands is important, since some statements
//depend on insert ids taken from previous statements.
$curr_row = 0;
foreach ($spreadsheet as $data) {
//Escape slashes, etc. to prevent SQL injection
for($i=0; $i < sizeof($data); $i++){ $data[$i] = $db->real_escape_string($data[$i]); }
// foreach ($data as $element) {
// print "Before: ".$element."<br>";
// $element = $db->real_escape_string($element);
// print "After: ".$element."<br>";
// }
//Creates a new submission for each form submission. All rows submitted
//simultaneously have the same submission ID.
if ($curr_row == 0) {
$result = $db->query(
"insert into submission values(
'',
'" . date('c') . "',
'unknown',
'" . $_SESSION['logged_in_user_id'] . "'
)"
);
if ($result) {
//Save insert id as this submission's submission ID
$insert_id['submission'] = $db->insert_id;
if (DEBUG) {
print "Inserted new submission entry. ". $db->error . "<br>";
}
} else {
print "Couldn't insert new row into submission table: " . $db->error . "<br>";
exit;
}
//Insert gel images. This only needs to be done once, after the first submission ID has been generated.
//Gel image rows are not checked for duplicates because they contain both a gel image ID and a submission ID.
//Hence, reusing a gel image will also reuse its (incorrect) submission number. This is a weakness of the schema and
//only exists because I didn't anticipate storing multiple gel images per submission. Duplicate gel images
//will probably not occur in practice.
for ($i = 0; isset($_FILES['gel_image_' . $i]); $i++) {
//Check for duplicates
$result = $db->query(
"insert into gel_images(
gel_image_id,
submission_id,
gel_image,
md5_sum
) values(
'',
'" . $insert_id['submission'] . "',
'" . $db->real_escape_string(file_get_contents($_FILES['gel_image_' . $i]['tmp_name'] . ".jpg")) . "',
'" . md5_file($_FILES['gel_image_' . $i]['tmp_name'] . ".jpg") . "'
)"
);
if ($result) {
$insert_id['gel_image_' . $i] = $db->insert_id;
if (DEBUG) {
print "No duplicate row found. Inserted row into gel_images table.<br>";
print $db->error;
}
} else {
print "Couldn't insert new row into gel_images table: " . $db->error . "<br>";
}
}
}
$curr_row++;
//Creates a new taxonomy ID for each unique combination of taxonomic identifiers.
//The database is queried for duplicates with each submission, and if any are found,
//no data are inserted.
$result = $db->query("select * from taxonomy where
kingdom='" . $data[12] . "' and
phylum='" . $data[13] . "' and
class='" . $data[14] . "' and
taxonomy_order='" . $data[15] . "' and
family='" . $data[16] . "' and
genus='" . $data[17] . "' and
species='" . $data[18] . "' and
subspecies='" . $data[19] . "' and
variety='" . $data[20] . "'
");
if ($result) {
if ($result->num_rows == 0) {
$db->query(
"insert into taxonomy values(
'',
'" . $data[12] . "',
'" . $data[13] . "',
'" . $data[14] . "',
'" . $data[15] . "',
'" . $data[16] . "',
'" . $data[17] . "',
'" . $data[18] . "',
'" . $data[19] . "',
'" . $data[20] . "'
)"
);
if ($result) {
$insert_id['taxonomy'] = $db->insert_id;
if (DEBUG) {
print "No matching row found. Inserting row into taxonomy table.<br>";
print $db->error;
}
} else {
print "Couldn't insert new row into taxonomy table: " . $db->error . "<br>";
}
} else {
$row = $result->fetch_assoc();
$insert_id['taxonomy'] = $row['taxonomy_id'];
if (DEBUG) {
print $result->num_rows . " duplicate entries with the same taxonomy data exist.<br>";
}
}
} else {
print "Couldn't retrieve existing taxonomy entries from DB: " . $db->error;
}
//Creates a new location ID for each unique combination of location identifiers.
//The database is queried for duplicates with each submission, and if any are found,
//no data are inserted.
$result = $db->query("select * from location where
location_name='" . $data[21] . "' and
country='" . $data[22] . "' and
province='" . $data[23] . "' and
city='" . $data[24] . "' and
elevation='" . $data[25] . "' and
latitude='" . $data[26] . "' and
longitude='" . $data[27] . "'
");
if ($result) {
if (DEBUG) {
print 'Retrieved ' . $result->num_rows . ' location rows from DB.<br>';
}
if ($result->num_rows == 0) {
$db->query(
"insert into location values(
'',
'" . $data[21] . "',
'" . $data[22] . "',
'" . $data[23] . "',
'" . $data[24] . "',
'" . $data[25] . "',
'" . $data[26] . "',
'" . $data[27] . "'
)"
);
if ($result) {
$insert_id['location'] = $db->insert_id;
if (DEBUG) {
print "No matching row found. Inserted row into location table.<br>" . $db->error . "<br>";
}
} else {
print "Couldn't insert new row into location table: " . $db->error . "<br>";
}
} else {
$row = $result->fetch_assoc();
$insert_id['location'] = $row['location_id'];
if (DEBUG) {
print $result->num_rows . "Duplicate entries for this row exist.<br>";
}
}
} else {
print "Couldn't retrieve existing location entries from DB: " . $db->error;
}
//Creates a new div_stock entry.
//The database is queried for duplicates with each submission, and if any are found,
//no data are inserted.
$result = $db->query("select div_stock_id, seed_lot, stock_source from div_stock where
div_stock_acc is null and
div_generation_id is null and
div_passport_id is null and
seed_lot = '" . $data[7] . "' and
stock_source = '" . $data[1] . "' and
comments is null
");
if ($result) {
if ($result->num_rows == 0) {
$db->query(
"insert into div_stock(
div_stock_id,
seed_lot,
stock_source
) values(
'',
'" . $data[7] . "',
'" . $data[1] . "'
)"
);
if ($result) {
$insert_id['div_stock'] = $db->insert_id;
if (DEBUG) {
print "No duplicate row found. Inserted row into div_stock table.<br>";
print $db->error;
}
} else {
print "Couldn't insert new row into div_stock table: " . $db->error . "<br>";
}
} else {
if (DEBUG) {
print $result->num_rows . " duplicate entries with the same div_stock data exist.<br>";
}
$row = $result->fetch_assoc();
$insert_id['div_stock'] = $row['div_stock_id'];
}
} else {
print "Couldn't retrieve existing div_stock entries from DB: " . $db->error;
}
//Creates a new div_obs_unit entry.
//The database is queried for duplicates with each row, and if any are found,
//no data are inserted.
$result = $db->query("select div_stock_id, div_obs_unit_id from div_obs_unit where
div_obs_unit_acc is null and
div_experiment_id is null and
div_stock_id = '" . $insert_id['div_stock'] . "' and
div_locality_id is null and
name is null and
coord_x is null and
coord_y is null and
rep is null and
block is null and
plot is null and
season is null and
plant is null and
planting_date is null and
harvest_date is null and
comments is null
");
if ($result) {
if ($result->num_rows == 0) {
$db->query(
"insert into div_obs_unit(
div_obs_unit_id,
div_stock_id
) values(
'',
'" . $insert_id['div_stock'] . "'
)"
);
if ($result) {
$insert_id['div_obs_unit'] = $db->insert_id;
if (DEBUG) {
print "No duplicate row found. Inserted row into div_obs_unit table.<br>";
print $db->error;
}
} else {
print "Couldn't insert new row into div_obs_unit table: " . $db->error . "<br>";
}
} else {
if (DEBUG) {
print $result->num_rows . " duplicate entries with the same div_obs_unit data exist.<br>";
}
$row = $result->fetch_assoc();
$insert_id['div_obs_unit'] = $row['div_obs_unit_id'];
}
} else {
print "Couldn't retrieve existing div_obs_unit entries from DB: " . $db->error;
}
//Creates a new grouping entry.
//The database is queried for plates with the same name frome the same submission,
//and if any are found, no data are inserted.
$result = $db->query("select grouping.grouping_id, grouping.plate_label from grouping, sample_dna where
plate_label = '" . $data[2] . "' and
sample_dna.grouping_id = grouping.grouping_id and
sample_dna.submission_id = '" . $insert_id['submission'] . "'
");
if ($result) {
if ($result->num_rows == 0) {
$db->query(
"insert into grouping(
grouping_id,
plate_label,
create_date
) values(
'',
'" . $data[2] . "',
'" . date('c') . "'
)"
);
if ($result) {
$insert_id['grouping'] = $db->insert_id;
if (DEBUG) {
print "No duplicate row found. Inserted row into grouping table.<br>";
print $db->error;
}
} else {
print "Couldn't insert new row into grouping table: " . $db->error . "<br>";
}
} else {
if (DEBUG) {
print $result->num_rows . " duplicate entries with the same grouping data exist.<br>";
}
$row = $result->fetch_assoc();
$insert_id['grouping'] = $row['grouping_id'];
}
} else {
print "Couldn't retrieve existing grouping entries from DB: " . $db->error;
}
//Creates a new project entry.
//The database is queried for duplicates with each submission, and if any are found,
//no data are inserted.
$result = $db->query("select project_id, name from project where
name = '" . $data[0] . "'
");
if ($result) {
if (DEBUG) {
print "Retrieved " . $result->num_rows . " project rows from DB.<br>";
}
if ($result->num_rows == 0) {
$db->query(
"insert into project(
project_id,
name
) values(
'',
'" . $data[0] . "'
)"
);
if ($result) {
$insert_id['project'] = $db->insert_id;
if (DEBUG) {
print "No duplicate row found. Inserted row into project table.<br>";
print $db->error;
}
} else {
print "Couldn't insert new row into project table: " . $db->error . "<br>";
}
} else {
if (DEBUG) {
print $result->num_rows . " duplicate entries with the same project data exist.<br>";
}
$row = $result->fetch_assoc();
$insert_id['project'] = $row['project_id'];
}
} else {
print "Couldn't retrieve existing project entries from DB: " . $db->error;
}
//Creates a new sample ID for each row of the uploaded spreadsheet.
$db->query(
"insert into sample_dna (
sample_dna_id,
div_obs_unit_id,
grouping_id,
name,
producer,
well,
project_id,
submission_id,
taxonomy_id,
location_id,
pedigree,
concentration,
volume,
mass,
population
) values(
'',
'" . $insert_id['div_obs_unit'] . "',
'" . $insert_id['grouping'] . "',
'" . $data[4] . "',
'" . $data[11] . "',
'" . $data[3] . "',
'" . $insert_id['project'] . "',
'" . $insert_id['submission'] . "',
'" . $insert_id['taxonomy'] . "',
'" . $insert_id['location'] . "',
'" . $data[5] . "',
'" . $data[8] . "',
'" . $data[9] . "',
'" . $data[10] . "',
'" . $data[6] . "'
)"
);
if ($result) {
$insert_id['sample_dna'] = $db->insert_id;
if (DEBUG) {
print "Inserted new sample_dna row.<br>" . $db->error . "<br>";
}
} else {
print "Couldn't insert new row into sample_dna: " . $db->error . "<br>";
}
}
//Print confirmation for user
//Go back to the DB and retrieve information on the requested submission ID.
//Note that "order" is a reserved word in SQL
$result = $db->query("SELECT
project.name as 'Project Name',
submission.submission_id as 'Submission ID',
submission_date as 'Submission Date',
kingdom as Kingdom,
phylum as Phylum,
class as Class,
taxonomy_order as 'Order',
family as Family,
genus as Genus,
species as Species,
subspecies as Subspecies,
variety as Variety,
sample_dna_id as 'Sample ID',
sample_dna.producer as Preparer,
well as Well,
pedigree as Pedigree,
concentration as Concentration,
volume as Volume,
mass as Mass,
population as Population,
location_name as 'Location Name',
country as Country,
province as Province,
city as City,
elevation as Elevation,
latitude as Latitude,
longitude as Longitude,
stock_source as 'Source Lab',
grouping.plate_label as 'Plate Name',
sample_dna.name as 'Sample Name',
seed_lot as 'Stock Number'
FROM
submission,
taxonomy,
location,
sample_dna,
div_stock,
div_obs_unit,
grouping,
project
WHERE
project.project_id = sample_dna.project_id AND
location.location_id = sample_dna.location_id AND
grouping.grouping_id = sample_dna.grouping_id AND
taxonomy.taxonomy_id = sample_dna.taxonomy_id AND
div_stock.div_stock_id = div_obs_unit.div_stock_id AND
div_obs_unit.div_obs_unit_id = sample_dna.div_obs_unit_id AND
submission.submission_id = sample_dna.submission_id AND
submission.submission_id = '" . $insert_id['submission'] . "'"
);
if ($result) {
if (DEBUG) {
print 'Retrieved ' . $db->affected_rows . ' rows from DB.<br>';
}
//Print table containing spreadsheet info and gel image
print "<table border=0>";
//Spreadsheet info
print "<tr>
<td align=left valign=top>
<b>Sample Properties</b><br>
<table border =1 cellpadding=2 cellspacing=0>";
for ($curr_row = 0; $curr_row <= $result->num_rows; $curr_row++) {
$submission = $result->fetch_assoc();
if ($curr_row == 0) {
print "<tr>";
foreach ($submission as $key => $value) {
print "<td>" . $key . "</td>";
}
print "</tr>";
}
print "<tr>";
foreach ($submission as $key => $value) {
print "<td valign=top>" . $value . "</td>";
}
print "</tr>";
}
print "</table>
</td></tr>";
//Gel Image
print "<tr>
<td align=left valign=top>
<b>Gel Images</b><br>";
$result = $db->query("select gel_image_id from gel_images where gel_images.submission_id = '" . $insert_id['submission'] . "'");
if (!$result) {
print "Couldn't pull gel image from DB: " . $db->error . "<br>";
} else {
while (($image = $result->fetch_assoc()) == true) {
print '<img src="load_images.php?id=' . $image['gel_image_id'] . '"><br>';
}
}
print "</td></tr>";
print "</table></p>";
} else {
print "Couldn't retrieve submitted records from DB: " . $db->error;
}
//Construct notification email for staffers
$result = $db->query("select user_email,name,surname,lab from users where user_email='" . $_SESSION['logged_in_user'] . "'");
if ($result) {
$user_output = $result->fetch_assoc();
//Fill in default data for blank fields
if (!$user_output['name'] && !$user_output['surname']) {
$user_output['name'] = 'Anonymous';
}
if (!$user_output['lab']) {
$user_output['lab'] = 'Anonymous';
}
} else {
print "Couldn't retrieve info for user " . $_SESSION['logged_in_user'] . " from DB: " . $db->error . "<br>";
}
//Text of e-mail to staff members
foreach (explode(',', MODS) as $staff_email) { //Loop through list of mods
$subject = "GBS sample submission";
//The body contains a notification that samples were submitted, and a link to the review page.
$body = "<html><body><b>" .
$user_output['name'] . " " . $user_output['surname'] . "</b> from the laboratory <b>" . $user_output['lab'] .
" (" . $_SESSION['logged_in_user'] . ")</b> just submitted DNA sample information. <a href=\"" . WEB_ROOT .
"review_submissions.php\"" . ">Click here</a> to approve this submission.<br></body></html>";
//Extra headers are needed to specify HTML email
$headers = "From: " . $_SESSION['logged_in_user'] .
"\r\n" .
'MIME-Version: 1.0' .
"\r\n" .
'Content-type: text/html; charset=iso-8859-1' .
"\r\n";
mail($staff_email, $subject, $body, $headers);
}
// }
//Disconnect now that we're finished using the DB
// $db->disconnect;
}
?>
</body>
</html>
