#*********************************************************

#  Written by: Bradley University

#  Dr. Steven Dolins

#  Use: Transform and Load Raw Specimen

#       GBIF Data from GBIF file into the

# 		Staging Table (StagingRawSpecimen).

#  Version: 2.0

#  Date: November 09, 2009

#*********************************************************

<?php	

## ======== Connect to database

include('connect.php');

## ======== Spacing for buttons in index.php

echo "<br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br><br>";

## ======== Identify data file

$Validation_check = 0;

$GBIFFile = 'GBIF.txt';

$duplicateEntries=0;

$successEntries=0;

$validation_errors=0;

$recordCount = 0;

## ======== Load file as a character array - each entry is one line of text with newline still attached

$GBIFFile = fopen($GBIFFile, 'r');

##==== Pull off a single line from file and add it to array ====

//$line = fgets($GBIFFile);	//UNCOMMENT ONLY IF TITLES ARE INCLUDED IN FIRST LINE OF FILE

while($line = fgets($GBIFFile))

{

	$line = trim($line);

	$arr = explode("\t", $line);

	$recordCount++;

	## ======== Ensure that data enetered is a full tuple (has all columns associated with GBIF data)

	if(count($arr) != 30)

	{

		reportError(NULL, NULL, $recordCount, "Incomplete tuple, ignored");

		continue;

	}

	## ======== Clean up values in array and check data consistency

	checkArray();

	## ======== Populate an array which holds only the values to be inserted

	$arrayInsert['InstitutionCD'] = $arr[5];

	$arrayInsert['CollectionCD'] = $arr[6];

	$arrayInsert['CatalogNO'] = $arr[7];

	$arrayInsert['DBSourceName'] = "GBIF";

	$arrayInsert['LastDateModified'] = $arr[8];

	$arrayInsert['ScientificName'] = $arr[12];

	$arrayInsert['ScientificNameOriginal'] = $arr[10];

	$arrayInsert['Family'] = $arr[14];

	$arrayInsert['Genus'] = $arr[16];

	$arrayInsert['AuthorOfScientificName'] = $arr[11];

	$arrayInsert['Country'] = $arr[17];

	$arrayInsert['StateProvince'] = $arr[20];

	$arrayInsert['County'] = $arr[19];

	$arrayInsert['MinimumElevationInMeter'] = $arr[26];

	$arrayInsert['MaximumElevationInMeter'] = $arr[27];

	$arrayInsert['LatestDateCollected'] = $arr[4];

	$arrayInsert['Collector'] = $arr[2];

	$arrayInsert['DecimalLatitude'] = $arr[21];

	$arrayInsert['DecimalLongitude'] = $arr[23];

	$arrayInsert['Locality'] = $arr[18];

	$arrayInsert['IdentifiedBy'] = $arr[9];

	$arrayInsert['VerbatimLatitude'] = $arr[22];

	$arrayInsert['VerbatimLongitude'] = $arr[24];

	$arrayInsert['SpecificEpithet'] = $arr[28];

	$arrayInsert['InfraspecificEpithet'] = $arr[29];

	$arrayInsert['GBIFFamilyOriginal'] = $arr[13];

	$arrayInsert['GBIFGenusOriginal'] = $arr[15];

	$arrayInsert['InfraspecificRank'] = $arr[30];

	## ======== Ensure primary key values and not null values (CollectionCD, CatalogNO, InstitutionCD) are not null

	if(is_null($arrayInsert['InstitutionCD']) OR is_null($arrayInsert['CatalogNO']) OR is_null($arrayInsert['InstitutionCD']))

	{

		reportError(NULL, NULL, NULL, "One or more non-null value is NULL");

		continue;

	} 

	## ======== Filter out MOBOT and NYBG Data since those are inserted from another file

	if ( strstr($arrayInsert['InstitutionCD'], "MOBOT") or strstr($arrayInsert['InstitutionCD'] , "NYBG"))

	{

		reportError($arrayInsert['CollectionCD'], $arrayInsert['CatalogNO'], $arrayInsert['InstitutionCD'], "Institution code contains either MOBOT or NYBG");

		continue;

	}

	## ======== Generate SQL code to insert desired information

	$sql = sprintf("INSERT INTO StagingRawSpecimen	

			(

				InstitutionCD,

				CollectionCD,

				CatalogNO,

				DBSourceName,

				LastDateModified,

				ScientificName,

				ScientificNameOriginal,

				GBIFFamilyOriginal,

				Family,

				GBIFGenusOriginal,

				Genus,

				AuthorOfScientificName,

				Country,

				StateProvince,

				County,

				MinimumElevationInMeter,

				MaximumElevationInMeter,

				LatestDateCollected,

				Collector,

				DecimalLatitude,

				DecimalLongitude,

				Locality,

				IdentifiedBy,

				VerbatimLatitude,

				VerbatimLongitude,

				SpecificEpithet,

				InfraspecificEpithet,

				InfraspecificRank

				) 

			VALUES

				(

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s,

				%s

			)

			",

			insertSQLData($arrayInsert['InstitutionCD']),

			insertSQLData($arrayInsert['CollectionCD']),

			insertSQLData($arrayInsert['CatalogNO']),

			insertSQLData($arrayInsert['DBSourceName']),

			insertSQLData($arrayInsert['LastDateModified']),

			insertSQLData($arrayInsert['ScientificName']),

			insertSQLData($arrayInsert['ScientificNameOriginal']),

			insertSQLData($arrayInsert['GBIFFamilyOriginal']),

			insertSQLData($arrayInsert['Family']),

			insertSQLData($arrayInsert['GBIFGenusOriginal']),

			insertSQLData($arrayInsert['Genus']),

			insertSQLData($arrayInsert['AuthorOfScientificName']),

			insertSQLData($arrayInsert['Country']),

			insertSQLData($arrayInsert['StateProvince']),

			insertSQLData($arrayInsert['County']),

			insertSQLData($arrayInsert['MinimumElevationInMeter']),

			insertSQLData($arrayInsert['MaximumElevationInMeter']),

			insertSQLData($arrayInsert['LatestDateCollected']),

			insertSQLData($arrayInsert['Collector']),

			insertSQLData($arrayInsert['DecimalLatitude']),

			insertSQLData($arrayInsert['DecimalLongitude']),

			insertSQLData($arrayInsert['Locality']),

			insertSQLData($arrayInsert['IdentifiedBy']),

			insertSQLData($arrayInsert['VerbatimLatitude']),

			insertSQLData($arrayInsert['VerbatimLongitude']),

			insertSQLData($arrayInsert['SpecificEpithet']),

			insertSQLData($arrayInsert['InfraspecificEpithet']),

			insertSQLData($arrayInsert['InfraspecificRank'])

		);

		//echo("<br> <br> $sql <br> <br>");

	## ======== Check whether a tuple with the variables you're trying to enter has already been entered (trying to insert duplicate tuple)

	if(mysql_query($sql))

	{

		$successEntries++;

	}

	else if(mysql_errno() == 1062)

	{

		$duplicateEntries++;

		//reportError($arrayInsert['CollectionCD'], $arrayInsert['CatalogNO'], NULL, mysql_error());

		continue;

	}

	else

	{

		reportError($arrayInsert['CollectionCD'], $arrayInsert['CatalogNO'], NULL, mysql_error());

	}

	## ======== Validate data entered

	if ($Validation_check) // Validation Check status

{	

	$validation_query = sprintf(

				"SELECT * FROM StagingRawSpecimen WHERE

				CollectionCD %s

				AND CatalogNO %s",

			selectSQLData($arrayInsert['CollectionCD']),

			selectSQLData($arrayInsert['CatalogNO'])

			);

	//echo("<br> <br> $validation_query <br> <br>");

	$validation_result = mysql_query($validation_query) OR reportError($arrayInsert['CollectionCD'], $arrayInsert['CatalogNO'], NULL, mysql_error());

	if(mysql_num_rows($validation_result) > 1)	## IF more than 1 record has these properties, then NOT UNIQUE				 

	{

		reportError($arrayInsert['CollectionCD'], $arrayInsert['CatalogNO'], NULL, "Validation result not unique");

		continue;	//error, skip the rest of validation

	}

	if(mysql_num_rows($validation_result) < 1)

	{

		reportError($arrayInsert['CollectionCD'], $arrayInsert['CatalogNO'], NULL, "Validation result does not exist");

		continue;	//error, skip the rest of validation

	}		

	else //(num_rows == 1)

	{

		$validation_result_row = mysql_fetch_assoc($validation_result) OR DIE("Error - no result found");		

		$CatalogNO = $arrayInsert['CatalogNO'];

		$CollectionCD = $arrayInsert['CollectionCD'];

		foreach($arrayInsert AS $key => $data)

		{

			## ======== check if the result found in the database matches that entered (Check both exact string match and numerical equality)

			if((strcasecmp($validation_result_row[$key], $data) != 0) AND (!checkDifference($data, $validation_result_row[$key], 5)))

			{

				reportError($CollectionCD, $CatalogNO, $key, "Result does not match data entered |--| $data |--| $validation_result_row[$key] |--|");	

			}

		}

	}

}	//end while loop

}	

## ======== Display results to user

if($duplicateEntries >= 1)

{

	echo "<br>" . $duplicateEntries . " record(s) has been skipped, key already exists<br>";

}

if($successEntries >= 1)

{

	echo "<br>" . $successEntries . " record(s) has been successfully inserted<br>";

}

echo("<br> $validation_errors Validation Errors");

// =============================

// ======== END PROGRAM ========

// =============================

## ======================================

## Name: checkArray

## Function: Checks the data form the file and formats it for entry into the staging table

## Internal Variables: 

## Global Variables: $arr

## Calls Function: None

## Called by: Main loop

## Written by: Andrew Becker, Mark Sheehan

## Last Modified: 07-22-09

## =======================================

function checkArray()

{

	global $arr;

	for($a=0; $a<31; $a++)

	{

		if(!(array_key_exists($a, $arr)))

		{

			$arr[$a] = ' ';

		}

		## ==== Trim white spaces and newline off of front and end of string (necessary for values at end of line)

		$arr[$a] = trim($arr[$a], ' ');

		## ==== Replace Empty values ('') with null keyword

		if($arr[$a] == '' OR $arr[$a] == '\N')	

		{

			$arr[$a] = NULL;

		}

		if(($a == 4 OR $a == 8) AND $arr[$a] != NULL)	## CHANGED JUN 22 09 - IF date/time AND NOT NULL, then convert it to a form that mysql/PHP recognizes - YYYY-MM-DD HH-MM-SS

		{

			if($a == 8)

			{

				//==== Actual data in GBIF file in format "MM-DD-YYYY HH:MM"

				//Start formatting date

				$dateFormat = explode(' ', $arr[$a]);

				$date = $dateFormat[0];

				$time = $dateFormat[1];

				$date = explode('-', $date);	//break date up into MM DD YYYY

				$month = str_pad($date[1], 2, '0', STR_PAD_LEFT);	//if only entered as 5/27/2008, need it in form 05/27/2008 so need padding on left

				$day = str_pad($date[2], 2, '0', STR_PAD_LEFT);

				$year = $date[0];

				$time = explode(':', $time);

				$hour = $time[0];

				$hour = str_pad($hour, 2, '0', STR_PAD_LEFT);	//same issue as above

				$minute = $time[1];

				$second = "00";	//seconds not given, assume 00

				$arr[$a] = sprintf("%s-%s-%s %s:%s:%s", $year, $month, $day, $hour, $minute, $second); 

				//echo(" $arr[$a] <br>");

			}

			else if($a == 4)

			{

				//==== Actual data in GBIF file in format MM/DD/YYYY OR YYYY-MM-DD

				if(stripos($arr[$a], '/'))	// if in MM/DD/YYYY format

				{

					$dateFormat = explode('/', $arr[$a]);

					$month = str_pad($dateFormat[0], 2, '0', STR_PAD_LEFT);

					$day = str_pad($dateFormat[1], 2, '0', STR_PAD_LEFT);

					$year = $dateFormat[2];

					$arr[$a] = sprintf("%s-%s-%s", $year, $month, $day);	

				}

				else if(stripos($arr[$a], '-'))	//if in YYYY-MM-DD format

				{

					$dateFormat = explode('-', $arr[$a]);

					$month = str_pad($dateFormat[1], 2, '0', STR_PAD_LEFT);

					$day = str_pad($dateFormat[2], 2, '0', STR_PAD_LEFT);

					$year = $dateFormat[0];

					$arr[$a] = sprintf("%s-%s-%s", $year, $month, $day);

				}

				else

				{

					reportError($arr[6], $arr[7], $arr[$a], "Date format for ||$arr[$a]|| not found.");

				}

			}

		}

		else if($a == 26) // if elevation range is given as XXX-YYY in one field instead of as minimum and maximum

		{

			$minMax = explode('-', $arr[$a]);

			if(sizeof($minMax) > 1)

			{

				$arr[26] = $minMax[0];

				$arr[27] = $minMax[1];

			}

		}

		else if ($a == 12) //  To get the species and subspeices from the scientific name, Dhaval Shah

		{

			$spe_subspe = explode(' ',$arr[$a]);

			if(sizeof($spe_subspe) > 1 and !(is_numeric($spe_subspe[1])))

			{

				$arr[28] = trim($spe_subspe[1]);

				if(sizeof($spe_subspe) > 3 and !(is_numeric($spe_subspe[2])))

				{

					if ((strstr($spe_subspe[2], "var") or strstr($spe_subspe[2], "subsp") or strstr($spe_subspe[2], "ssp") or strstr($spe_subspe[2], "sbsp") or strstr($spe_subspe[2], "forma") or strstr($spe_subspe[2], "fo") or strstr($spe_subspe[2], "f") or strstr($spe_subspe[2], "cv")) and (strlen($spe_subspe[2]) < 6))

					{

						if ( strstr($spe_subspe[2], "ssp"))

						{

							$spe_subspe[2] = "subsp.";

						}

						$arr[30] = trim($spe_subspe[2]);

						$arr[29] = trim($spe_subspe[3]);

					}

					else

					{

						$arr[30] = NULL;

						$arr[29] = NULL;

					}

				}

				else

				{

					$arr[30] = NULL;

					$arr[29] = NULL;

				}

			}

			else

			{

				$arr[28] = NULL;

				$arr[29] = NULL;

				$arr[30] = NULL;

			}

		}

	}

}

## ========================================

## Name: reportError

## Function: creates a detailed error report in errorLog

## Internal Variables:

## Globals: $recordCount, $GBIFFile

## Calls Function: None

## Called by: Main Loop

## Written by: Mark Sheehan

## Last Updated: 07-22-09

## =======================================

function reportError($CollectionCD, $CatalogNo, $columnName, $Error)

{

	GLOBAL $recordCount;

	GLOBAL $GBIFFile;

	//echo("Creating error log for $CollectionCD | $CatalogNo | $Error <br><br>");

	$errorReport = sprintf(

		"INSERT INTO ErrorLog (CollectionCD, CatalogNO, RawDataFileName, RawRecordNO, StagingTableName, StagingColumnName, ErrorDescription)

		VALUES (%s, %s, %s, %s, %s, %s, %s)",

		insertSQLData($CollectionCD), 

		insertSQLData($CatalogNo),

		insertSQLData("GBIFFile.txt"),

		insertSQLData($recordCount),

		insertSQLData("StagingRawSpecimen"),

		insertSQLData($columnName),

		insertSQLData($Error)

	);

	//echo ("<br> $errorReport <br>");

	mysql_query($errorReport) OR DIE("Failed to enter log into error report." . mysql_error());

}

## =======================================

## Name: checkDifference

## Function: determines whether the difference between two numbers is close to 0 at given precision

## Internal Variables:

## Globals: None

## Calls Function: None

## Called by: main loop

## Written by: Mark Sheehan

## Last updated: 07-29-09

## ======================================

function checkDifference($firstNum, $secondNum, $precision)

{

	if(abs($firstNum - $secondNum) < 2 * pow(10, -$precision))

	{

		return TRUE;

	}

	else

	{

		return FALSE;

	}

}

## =======================================

## Name: InsertSQLData

## Function: Formats data to be inserted for SQL insert query

## Internal Variables:

## Globals: None

## Calls Function: None

## Called by: Main Loop

## Written by: Mark Sheehan

## Last Updated: 07-22-09

## ======================================

function InsertSQLData($string)

{

	if($string == '' OR $string == NULL)

	{

		return('NULL');

	}

	else

	{

		return(sprintf("'%s'", mysql_real_escape_string($string)));

	}

}

## =======================================

## Name: SelectSQLData

## Function: Formats data to be inserted into SQL select query

## Internal Variables:

## Globals: None

## Calls Function: None

## Called by: Main Loop

## Written by: Mark Sheehan

## Last Updated: 07-22-09

## ========================================

function SelectSQLData($string)

{

	if($string == '' OR $string == NULL)

	{

		return("IS NULL");

	}

	else

	{

		return(sprintf("= '%s'", mysql_real_escape_string($string)));

	}

}

?>