fork download

<?php
	$page_title = 'Run LOME selectivity analysis';
	require('../directoryinfo.php');
	include('../connect.php');
	include('functions.php');
	require_once('../header.html');
?>
 
<!-- Form For Executing Code -->
<form action="index.php" method="post" name="file_select">
	<fieldset>
 
	<table align="left" cellspacing=15>
		<tr><td colspan="2"><input type="checkbox" checked name="go" value="yes"><STRONG>Run Full Analysis</STRONG></td></tr>
		<tr><td colspan=2><input type="submit" name="submit" value="Submit"></td></tr>
	</table>
	</fieldset>
</form>
 
<?php
	error_reporting(E_ALL);	
 
	if(isset($_POST['go'])) { 
		$t0 = microtime(true);
 
		$carb_colors = array('blue','blue dark');
		$clast_colors = array('yellow','orange','tan','gray dark','gray light','gray');
 
		//////////////
		//
		// Time scale saved as time_scale.csv
		//
		//////////////
		$q_time_scale = "SELECT interval_name, age_bottom, age_top, intervals.id FROM intervals 
		WHERE interval_name IN ('Whiterock', 'Chazyan', 'Blackriverian', 'Shermanian', 'Richmondian', 'Hirnantian', 'Rhuddanian', 'Aeronian', 'Telychian', 'Wenlock')
		ORDER BY age_bottom";
		$time_scale = mysqli_query($connect_macrostrat, $q_time_scale);         
		$n_bin = mysqli_num_rows($time_scale);
 
		$time_scale_file = "time_scale.csv";
		$fh = fopen($time_scale_file, 'w') or die("can't open file");
		//chmod($time_scale_file, 0666);
		$file_row = "interval_name, age_bottom, age_top, id\n";
		fwrite($fh, $file_row);
 
		while($row = mysqli_fetch_assoc($time_scale)) {			
			if($row['interval_name'] == 'Blackriverian') {
				$file_row = "Blackriveran-Kirkfield,".$row['age_bottom'].",457.0000,".$row['id']."\n";
				$int_name[] = 'Blackriveran-Kirkfield';
				$int_bottom[] = $row['age_bottom'];
				$int_top[] = 457.0000;
			} else if($row['interval_name'] == 'Shermanian') {
				$file_row = "Shermanian-Maysvillian,".$row['age_bottom'].",450.0000,".$row['id']."\n";
				$int_name[] = 'Shermanian-Maysvillian';
				$int_bottom[] = $row['age_bottom'];
				$int_top[] = 450.0000;
			} else {
				$file_row = $row['interval_name'].",".$row['age_bottom'].",".$row['age_top'].",".$row['id']."\n";
				$int_name[] = $row['interval_name'];
				$int_bottom[] = $row['age_bottom'];
				$int_top[] = $row['age_top'];
			}
			$int_id[] = $row['id'];
			fwrite($fh, $file_row);
		}
		fclose($fh);
		echo 'File <a href="'.$time_scale_file.'">'.$time_scale_file.'</a> saved<BR>';
		mysqli_free_result($time_scale);
		$n_int = count($int_name);
 
		$base_ordovician = 488.3;
		$study_max = max($int_bottom);
		$study_min = min($int_top);
 
 
		//////////////
		//
		// Column data saved as columns.csv
		//
		//////////////
		$cols_file = "columns.csv";
		$fh = fopen($cols_file, 'w') or die("can't open file");
		chmod($cols_file, 0777);
		$file_row = "col_id, lat, lng, sheet, col, col_area, project_id\n";
		fwrite($fh, $file_row);
 
		$q = "SELECT cols.id, lat, lng, project_id, col_group, col, col_area FROM cols 
		INNER JOIN col_groups ON col_groups.id=col_group_id 
		INNER JOIN projects ON projects.id=project_id
		WHERE project = 'North America' AND col_group != 'Mexico' AND cols.id NOT IN (34, 111, 139, 218, 586, 587) AND status_code = 'active'"; //echo $q."<br>";
 
		//(456,443,633,499)
		$columns = mysqli_query($connect_macrostrat, $q);       
		while($row = mysqli_fetch_assoc($columns)) {
			$file_row = $row['id'].",".$row['lat'].",".$row['lng'].",".$row['col_group'].",".$row['col'].",".$row['col_area'].",".$row['project_id']."\n";
			fwrite($fh, $file_row);
			$my_cols[] = $row['id'];
		}
		fclose($fh);
		mysqli_free_result($columns);
		echo 'File <a href="'.$cols_file.'">'.$cols_file.'</a> saved<BR>';
 
		//  get column polygons
		$poly_file = "column_polygons.csv";
		$fh = fopen($poly_file, 'w') or die("can't open file");
		chmod($poly_file, 0777);
		$file_row = "col_id, lat, lng\n";
		fwrite($fh, $file_row);
 
		$q = "SELECT col_id, AsText(col_area) coords FROM col_areas 
		WHERE col_id IN (".implode(",", $my_cols).")";
		$columns = mysqli_query($connect_macrostrat, $q);       
 
		while($row = mysqli_fetch_assoc($columns)) {
			$my_coord = ltrim($row['coords'], 'POLYGON((');
			$my_coord = rtrim($my_coord, '))');
			$my_coord = explode(",", $my_coord);
 
			for($i=0; $i<count($my_coord); ++$i) {
				$temp_coord = explode(" ", $my_coord[$i]);
				$file_row = $row['col_id'].",".$temp_coord[0].",".$temp_coord[1]."\n";
				fwrite($fh, $file_row);
			}
		}
		fclose($fh);
		mysqli_free_result($columns);
		echo 'File <a href="'.$poly_file.'">'.$poly_file.'</a> saved<BR>';
 
		// get obselete columns to exclude
		$obsolete_cols = array();
		$q = "SELECT cols.id  FROM cols 
		INNER JOIN col_groups ON col_groups.id=col_group_id 
		INNER JOIN projects ON projects.id=project_id
		WHERE project = 'North America' AND status_code = 'obsolete'"; //echo $q."<br>";		
		$columns = mysqli_query($connect_macrostrat, $q);       
		while($row = mysqli_fetch_assoc($columns)) {
			$obsolete_cols[] = $row['id'];
		}
		mysqli_free_result($columns);
 
 
 
		//////////////
		//
		// get packages	
		//
		//////////////		
		$counter = 0;
		$temp_pkg = split_packages('marine');
 
		//  set package crossers
		$pkg_file = "ordovician_packages.csv";
		$fh = fopen($pkg_file, 'w') or die("can't open file");
		chmod($pkg_file, 0777);
		$file_row = "package_id, age_bottom, age_top, col_id\n";
		fwrite($fh, $file_row);
 
		for($i=0; $i<count($temp_pkg); ++$i) {
			$q = "SELECT col_id, max(b.age_bottom) bottom, min(t.age_top) top, count(distinct units.id) n_unit, FO_h FROM units
			INNER JOIN intervals b ON b.id=FO
			INNER JOIN intervals t ON t.id=LO
			WHERE units.id IN ($temp_pkg[$i])";
			$result=mysqli_query($connect_macrostrat, $q);
			$row=mysqli_fetch_assoc($result);
			if(in_array($row['col_id'], $my_cols)===TRUE && $row['bottom']>$study_min && $row['top']<$study_max) {
				$my_pkg[$counter] = $temp_pkg[$i];	
				$my_pkg_fo[$counter] = $row['bottom'];	
				$my_pkg_lo[$counter] = $row['top'];	
				$my_pkg_n_units[$counter] = $row['n_unit'];	
				$my_pkg_col_id[$counter] = $row['col_id'];
 
				$temp_units = explode(",",$temp_pkg[$i]);
				for($j=0; $j<count($temp_units); ++$j) $unit_packages[$temp_units[$j]]=$counter;  // unit_id is key, package number is value
 
				//  get fo_h and lo_h for packages
				$q = "SELECT units.id, FO_h, (b.age_bottom-b.age_top)/2 half_duration, b.age_bottom FROM units
				INNER JOIN intervals b ON b.id=FO
				WHERE units.id IN ($temp_pkg[$i])
				ORDER BY b.age_bottom DESC, FO_h
				LIMIT 1";
				$fo_result = mysqli_query($connect_macrostrat, $q);
				$fo_row = mysqli_fetch_assoc($fo_result);
				$my_temp_fo = $fo_row['FO_h'];
				mysqli_free_result($fo_result);
 
				$q = "SELECT units.id, LO_h, (t.age_bottom-t.age_top)/2 half_duration, t.age_top FROM units
				INNER JOIN intervals t ON t.id=LO
				WHERE units.id IN ($temp_pkg[$i])
				ORDER BY t.age_top, FIELD(LO_h,0,6,5,4,3,2,1)
				LIMIT 1"; //echo $q."<br>";
				$lo_result = mysqli_query($connect_macrostrat, $q);
				$lo_row = mysqli_fetch_assoc($lo_result);
				$my_temp_lo = $lo_row['LO_h'];
				mysqli_free_result($lo_result);
 
				if($my_temp_fo==0) $my_pkg_fo_h[$counter] = $fo_row['age_bottom'];
				else $my_pkg_fo_h[$counter] = $fo_row['age_bottom'] - $fo_row['half_duration'];
 
				if($my_temp_lo==0) $my_pkg_lo_h[$counter] = $lo_row['age_top'];
				else $my_pkg_lo_h[$counter] = $lo_row['age_top'] + $lo_row['half_duration']; 
 
				$file_row=$counter.",".$fo_row['age_bottom'].",".$lo_row['age_top'].",".$row['col_id']."\n";
//				$file_row=$counter.",".$my_pkg_fo_h[$counter].",".$my_pkg_lo_h[$counter]."\n";
				fwrite($fh, $file_row);
 
				++$counter;
			}
 
			// get packages for testing gaps
			if(in_array($row['col_id'], $my_cols)===TRUE) {			
				//  get fo_h and lo_h for packages
				$q = "SELECT units.id, FO_h, (b.age_bottom-b.age_top)/2 half_duration, b.age_bottom FROM units
				INNER JOIN intervals b ON b.id=FO
				INNER JOIN intervals t ON t.id=LO
				WHERE units.id IN ($temp_pkg[$i])
				ORDER BY b.age_bottom DESC, FO_h, t.age_top DESC, field(LO_h, 1,2,3,4,5,6,0)
				LIMIT 1";
				$fo_result = mysqli_query($connect_macrostrat, $q);
				$fo_row = mysqli_fetch_assoc($fo_result);
				if($fo_row['FO_h']==0) $my_gap_fo_h[] = $fo_row['age_bottom'];
				else $my_gap_fo_h[] = $fo_row['age_bottom']-$fo_row['half_duration'];
				if($fo_row['age_bottom']!=$row['bottom']) echo "bad base calc<br>";
				mysqli_free_result($fo_result);
 
				$my_gap_pkg[] = $temp_pkg[$i];	
				$my_gap_fo[] = $row['bottom'];	
				$my_gap_col_id[] = $row['col_id'];
			}
			mysqli_free_result($result);
		}
		$all_units = implode(",",$my_pkg);
		$all_units_array = explode(",", $all_units);
		$total_units = implode(",",$my_gap_pkg);
		unset($temp_units, $temp_pkg, $counter);
		echo "There are ".(count(explode(",",$total_units)))." units.<br><br>";
		fclose($fh);
		echo 'File <a href="'.$pkg_file.'">'.$pkg_file.'</a> saved<BR>';
 
/*		
		// calculate package crossers
		$crosser_file = 'package_crossers.csv';
		$fh = fopen($crosser_file,'w');
		chmod($crosser_file, 0666);
		fclose($fh);
 
		$file_name_r = 'temp.r';
		$fh = fopen($file_name_r, 'w') or die("can't open file");
		chmod($file_name_r, 0666);
		$r_script = "
		source('myFunctions.r')
		time.scale <- read.csv(file='$time_scale_file', header=TRUE)
		packages <- read.csv(file='$pkg_file', header=TRUE)
 
		cross.temp <- crossers(packages[,2:3], time.scale[,2:3])
		write.csv(cbind(time.scale[,c(4,1:3)],cross.temp), file='$crosser_file', quote=FALSE, row.names=FALSE)
		";
		fwrite($fh, $r_script);
		fclose($fh);
		$cmd = "echo 'rscript <- \"$file_name_r\"; source(rscript)' | " . "/usr/bin/R --slave 2>&1";
		exec($cmd, $r_ran); 
		if(count($r_ran)>0) {
			echo "R (Package Crossers): ";
			print_r($r_ran); echo "<br>";
		}
		echo 'File <a href="'.$crosser_file.'">'.$crosser_file.'</a> saved<BR>';
 
 
 
		//////////////
		//
		// calculate environmental gaps for every unit
		//
		//////////////
		$env_gap = array();
		$env_unit_top = array();
		$all_unit_array = explode(",",$all_units);
		for($i=0; $i<count($all_unit_array); ++$i) {
			// get information for focal unit
			$q = "SELECT units.id, color, col_id, t.age_bottom top_bottom, t.age_top, LO_h, LO, FO_h, b.age_bottom FROM units
			INNER JOIN intervals b ON b.id=FO
			INNER JOIN intervals t ON t.id=LO
			WHERE units.id=".$all_unit_array[$i];
			$result = mysqli_query($connect_macrostrat, $q);
			$focal_unit = mysqli_fetch_assoc($result);
			mysqli_free_result($result);
			if($focal_unit['LO_h'] == 0) {
				$gap_bottom = $focal_unit['age_top'];
			} else {
				$gap_bottom = $focal_unit['age_top'] + ($focal_unit['top_bottom']-$focal_unit['age_top'])/2;
			}
 
			// get information on next youngest unit with same color
			$q = "SELECT units.id, b.age_bottom, b.age_top bottom_top, FO_h, FO, color, t.age_top, LO_h FROM units 
			INNER JOIN intervals b ON b.id=FO
			INNER JOIN intervals t ON t.id=LO
			WHERE col_id=".$focal_unit['col_id']." AND units.id IN (".$total_units.") AND units.id != ".$focal_unit['id']." 
				AND ((FO_h!=0 AND b.age_bottom-(b.age_bottom-b.age_top)/2 <=".$gap_bottom.") OR (FO_h=0 AND b.age_bottom<=".$gap_bottom.")) 
				AND color='".$focal_unit['color']."'
			ORDER BY b.age_bottom DESC, FO_h, t.age_top DESC, field(LO_h, 1,2,3,4,5,6,0)
			LIMIT 1"; //echo $q."<br>";
			$result = mysqli_query($connect_macrostrat, $q);
			if(mysqli_num_rows($result)==1) {
				$test_unit = mysqli_fetch_assoc($result);
				if($test_unit['FO_h'] == 0) {
					$gap_top = $test_unit['age_bottom'];
				} else {
					$gap_top = $test_unit['age_bottom'] - ($test_unit['age_bottom']-$test_unit['bottom_top'])/2;
				}
			} else {
				$gap_top = 0;
			}
			mysqli_free_result($result);
 
			if(round($gap_bottom,5)==round($gap_top,5)) {
				$env_gap[$all_unit_array[$i]]=0;
			} else {
				$env_gap[$all_unit_array[$i]] = log($gap_bottom-$gap_top);
			} 
			if($gap_bottom<$gap_top) {
				echo $focal_unit['id']." (".$test_unit['id']."): ".$gap_bottom." - ".$gap_top." = ".$env_gap[$all_unit_array[$i]]."<br>";
			}
			$env_unit_top[$all_unit_array[$i]] = $gap_bottom;
		}
//		echo "environmental gap:<br>";print_r($env_gap); echo "<br><br>";
 
 
		//////////////
		//
		// get duration of gaps above packages
		//
		//////////////
		for($i=0; $i<count($my_pkg); ++$i) {
			$temp_max=0;
			$temp_fo=0;
			for($j=0; $j<count($my_gap_pkg); ++$j) {
				if($my_gap_pkg[$j]!=$my_pkg[$i] && $my_pkg_col_id[$i]==$my_gap_col_id[$j] && $my_pkg_lo[$i] >= $my_gap_fo[$j] && $my_gap_fo[$j]>$temp_max) {
					$temp_max = $my_gap_fo[$j];
				}
				if($my_gap_pkg[$j]!=$my_pkg[$i] && $my_pkg_col_id[$i]==$my_gap_col_id[$j] && $my_pkg_lo_h[$i] >= $my_gap_fo_h[$j] && $my_gap_fo_h[$j]>$temp_fo) {
					$temp_fo = $my_gap_fo_h[$j];				
				}
			}
			if($my_pkg_lo[$i] != $temp_max) {
				$my_pkg_gap[$i] = log($my_pkg_lo[$i] - $temp_max);
			} else {
				$my_pkg_gap[$i] = 0;
			}
 
			if(round($my_pkg_lo_h[$i],5) != round($temp_fo,5)) {
				$my_pkg_gap2[$i] = log($my_pkg_lo_h[$i] - $temp_fo);
			} else {
				$my_pkg_gap2[$i] = 0;
			}
		}
 
*/
		//////////////
		//
		// get matched ordovician genera and references
		//
		//////////////
 
		// get all appropriate taxa
		$my_genera = array();
		$this_genus = array();
		$my_genus_age = array();
		$my_occurrences = array();
 
		$q = "SELECT occurrences_temp2.taxon_no, genus_name, class, taxon_order, family, min(ta.top_age) pbdb_min,
		occurrences_temp2.taxon_environment, occurrences_temp2.locomotion, occurrences_temp2.life_habit, occurrences_temp2.diet1, occurrences_temp2.diet2 
		FROM occurrences_temp2
		INNER JOIN intervals b ON b.id=occurrences_temp2.FO
		INNER JOIN intervals t ON t.id=occurrences_temp2.LO
		INNER JOIN pbdb.interval_lookup ba ON ba.interval_no=occurrences_temp2.max_interval_no
		INNER JOIN pbdb.interval_lookup ta ON ta.interval_no=occurrences_temp2.min_interval_no
		LEFT OUTER JOIN pbdb.ecotaph ON ecotaph.taxon_no=occurrences_temp2.taxon_no
		LEFT OUTER JOIN pbdb.authorities ON authorities.taxon_no=occurrences_temp2.taxon_no
		WHERE occurrences_temp2.unit_id > 0 AND occurrences_temp2.unit_id IN (".$all_units.") AND (occurrences_temp2.extant = 'no' OR occurrences_temp2.extant IS NULL)
		AND occurrences_temp2.col_id NOT IN (".implode(",",$obsolete_cols).")
		AND ((b.age_bottom > ".$study_min." AND t.age_top < ".$study_max.") OR ((b.age_bottom IS NULL AND t.age_top IS NULL) AND (ba.base_age > ".$study_min." AND ta.top_age < ".$study_max.")))		
		AND (occurrences_temp2.release_date <= now() OR occurrences_temp2.release_date IS NULL) 
		AND (form_taxon = 'no' OR form_taxon IS NULL) AND (preservation != 'trace' OR preservation IS NULL)
		GROUP BY occurrences_temp2.taxon_no"; //echo $q."<br><br>"; 
		$result = mysqli_query($connect_macrostrat, $q);
		echo "There are ".mysqli_num_rows($result)." queried genera<br>"; 
		while($row=mysqli_fetch_assoc($result)) {
			$my_genera[] = $row;
			$this_genus[] = $row['taxon_no'];
			$my_genus_age[$row['taxon_no']]['pbdb_min'] = $row['pbdb_min'];
		}
		mysqli_free_result($result);
 
		// get global and NoAm ranges of taxa - even outside study interval
 
		$q = "SELECT occurrence_no, taxon_no, ba.base_age, ta.top_age, FO_h, LO_h, unit_id,
		b.age_bottom-((b.age_bottom-b.age_top)/2*LEAST(1,FO_h)) bottom, t.age_bottom+((t.age_bottom-t.age_top)/2*LEAST(1,LO_h)) top,
		unit_id, col_id, occurrences_temp2.collection_no,
		original_taxon_no, original_taxon_rank, paleolat, paleolng
		FROM occurrences_temp2
		INNER JOIN pbdb.interval_lookup ba ON ba.interval_no=occurrences_temp2.max_interval_no
		INNER JOIN pbdb.interval_lookup ta ON ta.interval_no=occurrences_temp2.min_interval_no
		LEFT OUTER JOIN intervals b ON b.id=FO
		LEFT OUTER JOIN intervals t ON t.id=LO
		WHERE taxon_no IN (".implode(",", $this_genus).")
		AND (occurrences_temp2.unit_id=0 OR occurrences_temp2.unit_id IN (".$all_units.")) 
		AND (occurrences_temp2.col_id IS NULL OR occurrences_temp2.col_id NOT IN (".implode(",",$obsolete_cols)."))"; //echo $q."<br><br>";
		$result = mysqli_query($connect_macrostrat, $q);
		echo "There are ".mysqli_num_rows($result)." queried occurrences<br>";
		$counter = 0;
		$int_occurrences = array();
		$int_paleolat = array();
		while($row=mysqli_fetch_assoc($result)) {
			if($row['unit_id']==0 || in_array($row['unit_id'], $all_units_array)===FALSE) { // not matched to north america
					$occurrence_bottom = $row['base_age'];
					$occurrence_top = $row['top_age'];
			} else {
				if($row['base_age']>$row['top'] && $row['top_age']<$row['bottom']) {// pbdb and unit ages overlap: use youngest bottom and oldest top
					$occurrence_bottom = min($row['base_age'], $row['bottom']);
					$occurrence_top = max($row['top_age'], $row['top']); 
				} else {  // pbdb and unit ages DO NOT overlap: use macrostrat
					$occurrence_bottom = $row['bottom'];
					$occurrence_top = $row['top'];
				}
			}
 
			$my_occurrences[] = array(
				"occurrence_no" => $row['occurrence_no'], 
				"taxon_no" => $row['taxon_no'], 
				"bottom" => $occurrence_bottom, 
				"top" => $occurrence_top,
				"unit_id" => $row['unit_id'],
				"col_id" => $row['col_id'],
				"collection_no" => $row['collection_no'],
				"original_taxon_no" => $row['original_taxon_no'],
				"original_taxon_rank" => $row['original_taxon_rank'],
				"paleolat" => $row['paleolat'],
				"paleolng" => $row['paleolng']
			);
			// get occurrence interval combinations
			for($i=0; $i<$n_int; ++$i) {
				if($occurrence_bottom>$int_top[$i] && $occurrence_top<$int_bottom[$i]) $int_occurrences[$i][] = $counter;
				if($occurrence_bottom>$int_top[$i] && $occurrence_top<$study_max && is_numeric($row['paleolat'])) $int_paleolat[$i][$row['taxon_no']][] = $row['paleolat'];
			}
			++$counter;
 
			if(isset($my_genus_age[$row['taxon_no']]['global_max'])===FALSE) $my_genus_age[$row['taxon_no']]['global_max'] = $occurrence_bottom;
			else if($my_genus_age[$row['taxon_no']]['global_max'] < $row['base_age']) $my_genus_age[$row['taxon_no']]['global_max'] = $occurrence_bottom;
 
			if(isset($my_genus_age[$row['taxon_no']]['global_min'])===FALSE) $my_genus_age[$row['taxon_no']]['global_min'] = $occurrence_top;
			else if($my_genus_age[$row['taxon_no']]['global_min'] > $row['top_age']) $my_genus_age[$row['taxon_no']]['global_min'] = $occurrence_top;
 
			if($row['unit_id']>0 && in_array($row['unit_id'], $all_units_array) && (($row['top'] < 443.7 && $my_genus_age[$row['taxon_no']]['pbdb_min'] < 443.7) || $row['top'] >= 443.7)) {
				if(isset($my_genus_age[$row['taxon_no']]['noam_max'])===FALSE) $my_genus_age[$row['taxon_no']]['noam_max'] = $occurrence_bottom;
				else if($my_genus_age[$row['taxon_no']]['noam_max'] < $row['base_age']) $my_genus_age[$row['taxon_no']]['noam_max'] = $occurrence_bottom;
 
				if(isset($my_genus_age[$row['taxon_no']]['noam_min'])===FALSE) $my_genus_age[$row['taxon_no']]['noam_min'] = $occurrence_top;
				else if($my_genus_age[$row['taxon_no']]['noam_min'] > $row['top_age']) $my_genus_age[$row['taxon_no']]['noam_min'] = $occurrence_top;
			}			
		}
		mysqli_free_result($result);
 
 
//		$pbdb_file = "ordovician_genera.csv";
		$pbdb_file = "ordovician_genera_culled.csv";
		$fh = fopen($pbdb_file, 'w') or die("can't open file");
		chmod($pbdb_file, 0666);
		$file_row = "interval_id, interval_name, age_bottom, age_top, taxon_no, genus, class, order, family, life_habit, locomotion, taxon_environment, diet1, diet2, n_species_NoAm, n_species_global, n_collections_NoAm, n_occur_NoAm, global_endemic_to_interval, NoAm_FAD, NoAm_LAD, global_FAD, global_LAD, max_N_paleolat_during_prior, max_S_paleolat_during_prior, n_matched_units_greater_n_45, n_matched_units_greater_s_45, max_great_circle_NoAm, max_great_circle_Global, n_packages_occupied, n_packages_total, n_units_occupied, n_units_total,n_carb_units, n_carb_units_occupied, n_siliciclastic_units, n_siliciclastic_units_occupied, n_truncating_packages_total, n_truncating_packages_occupied, n_env_truncation, n_env_truncation_double, n_units_entirely_within_interval, min_gap, max_gap, mean_gap, median_gap, min_env_gap, max_env_gap, mean_env_gap, median_env_gap, n_env_gap, n_columns_occupied, n_columns_total, n_trunc_columns_occupied\n";
		fwrite($fh, $file_row);
 
		$my_collections = array();
		for($i=0; $i < 1; ++$i) {		
//		for($i=0; $i<$n_int; ++$i) {		
			// get total units and packages
			$my_packages = array();
			$my_columns = array();
			$my_carb = array();
			$my_clast = array();
			$q = "SELECT units.id, color, col_id FROM units
			INNER JOIN intervals b ON b.id=FO
			INNER JOIN intervals t ON t.id=LO
			WHERE units.id IN (".$all_units.") AND b.age_bottom>$int_top[$i] AND t.age_top<$int_bottom[$i]";
			$result = mysqli_query($connect_macrostrat, $q);
			while($row = mysqli_fetch_assoc($result)) {
				$my_packages[] = $unit_packages[$row['id']];
				if(in_array($row['color'], $carb_colors)) $my_carb[] = $row['id']; 
				if(in_array($row['color'], $clast_colors)) $my_clast[] = $row['id']; 
				$my_columns[] = $row['col_id'];
			}
			$n_units_total = mysqli_num_rows($result);
			$n_packages_total = count(array_unique($my_packages));
			$n_columns_total = count(array_unique($my_columns));
			mysqli_free_result($result);
			unset($my_packages, $my_columns);
 
			// loop through each package and count truncations
			$truncating_packages = array();
			$spanning_packages = array();
			for($k=0; $k<count($my_pkg); ++$k) {
				// the packages that truncate in this interval
				if($my_pkg_lo[$k]<$int_bottom[$i] && $my_pkg_lo[$k]>=$int_top[$i]) {
					$truncating_packages[] = $k;
				// non-truncating packages
				} else if($my_pkg_lo[$k]<$int_bottom[$i] && $my_pkg_fo[$k]>$int_top[$i]) {
					$spanning_packages[] = $k;
				}
			}
 
			// loop through each occurrence in this interval and get those that occur in this interval
			$interval_genus = array();
			for($k=0; $k<count($int_occurrences[$i]); ++$k) {
				$interval_genus[$my_occurrences[$k]['taxon_no']][] = $my_occurrences[$k];
			}
			$this_genus = array_keys($interval_genus);
 
//			for($j=0; $j < 100; ++$j) {
			for($j=0; $j<count($interval_genus); ++$j) {		
 
				$noam_occurrences = array();
				$noam_collections = array();
				$noam_units = array();
				$noam_columns = array();
				$noam_packages = array();
				$noam_species = array();
				$noam_paleolat = array();
				$noam_paleolng = array();
				$matched_unit_n_45 = array();
				$matched_unit_s_45 = array();
				$unit_paleolat = array();
				$unit_paleolng = array();
 
				$global_occurrences = array();
				$global_collections = array();
				$global_species = array();
				$global_paleolat = array();
				$global_paleolng = array();
 
				if($my_genus_age[$this_genus[$j]]['global_max']<=$int_bottom[$i] && $my_genus_age[$this_genus[$j]]['global_min']>$int_top[$i]) $global_endemic_to_interval = 1;
				else $global_endemic_to_interval = 0;
 
				for($k=0; $k<count($interval_genus[$this_genus[$j]]); ++$k) {
					$row = $interval_genus[$this_genus[$j]][$k];
 
					if($row['unit_id']==0 || in_array($row['unit_id'], $all_units_array)===FALSE || ($row['unit_id']>0 && ($my_genus_age[$row['taxon_no']]['pbdb_min'] < 443.7 && $row['top'] < 443.7) || $row['top'] >= 443.7)) {
						$global_occurrences[] = $row['occurrence_no'];
						$global_collections[] = $row['collection_no'];
						if(is_numeric($row['paleolat']) && is_numeric($row['paleolng'])) {
							$global_paleolat[] = $row['paleolat'];
							$global_paleolng[] = $row['paleolng'];
						}
						$my_collections[] = $row['collection_no'];
 
						if($row['original_taxon_rank']=='species') $global_species[] = $row['original_taxon_no'];
 
						if($row['unit_id']>0 && in_array($row['unit_id'], $all_units_array)) {
							$noam_occurrences[] = $row['occurrence_no'];
							$noam_collections[] = $row['collection_no'];
							$noam_units[] = $row['unit_id'];
							$noam_columns[] = $row['col_id'];
							if($row['original_taxon_rank']=='species') $noam_species[] = $row['original_taxon_no'];
							$noam_paleolat[] = $row['paleolat'];
							$noam_paleolng[] = $row['paleolng'];
							if($row['paleolat']>= 45) $matched_unit_n_45[] = $row['unit_id'];
							if($row['paleolat']<= -45) $matched_unit_s_45[] = $row['unit_id'];
 
							if(is_numeric($row['paleolat']) && is_numeric($row['paleolng'])) {
								$unit_paleolat[$row['unit_id']][] = $row['paleolat']; //echo $row['paleolat']."<br>";
								$unit_paleolng[$row['unit_id']][] = $row['paleolng'];
							}
 
							$noam_packages[] = $unit_packages[$row['unit_id']];
						}
					}
				}
				$n_occur = count(array_unique($noam_occurrences));
				$n_coll = count(array_unique($noam_collections));
				$noam_units = array_unique($noam_units);
				$n_units = count($noam_units);
 
				if($n_units>0) {
					$noam_units = array_combine(range(0,$n_units-1,1), $noam_units);
					$n_cols = count(array_unique($noam_columns));
					$n_species = count(array_unique($noam_species));
					$n_packages = count(array_unique($noam_packages));
//					print_r($noam_columns); echo " - $n_cols<br><br>";
 
					if(count($my_carb)>0) $n_carb_units_occupied = count(array_unique(array_intersect($my_carb, $noam_units)));
					else $n_carb_units_occupied = 0;
					if(count($my_clast)>0) $n_siliciclastic_units_occupied = count(array_unique(array_intersect($my_clast, $noam_units)));
					$n_siliciclastic_units_occupied = 0;
 
					$n_occur_global = count(array_unique($global_occurrences));
					$n_coll_global = count(array_unique($global_collections));
					$n_species_global = count(array_unique($global_species));
					$n_matched_units_greater_n_45 = count(array_unique($matched_unit_n_45));
					$n_matched_units_greater_s_45 = count(array_unique($matched_unit_s_45));
 
					$temp_taxon_no = array_keys($int_paleolat[$i]);
					$max_paleolat_N = 'NA';
					 max_paleolat_S = 'NA';
					for($k=0; $k<count($int_paleolat[$i]); ++$k) {
						if($temp_taxon_no[$k] == $row['taxon_no']) {
							$max_paleolat_N = max($int_paleolat[$i][$row['taxon_no']]);
							$max_paleolat_S = min($int_paleolat[$i][$row['taxon_no']]);
						}
					}
 
					// get matched great circle distance
					//  set up file for sending to R for great circle analysis
					$circle_file = "noam_great_circle.csv";
					$fh2 = fopen($circle_file, 'w') or die("can't open file");
					chmod($circle_file, 0666);
					$file_row = "plat, plng, unit_id\n";
					fwrite($fh2, $file_row);
					for($k=0; $k<count($unit_paleolat); ++$k) {
						if(isset($unit_paleolat[$noam_units[$k]]) && isset($unit_paleolng[$noam_units[$k]])) {
							$unit_mean_lat = array_sum($unit_paleolat[$noam_units[$k]])/count($unit_paleolat[$noam_units[$k]]);
							$unit_mean_lng = array_sum($unit_paleolng[$noam_units[$k]])/count($unit_paleolng[$noam_units[$k]]);
							if(is_null($unit_mean_lat)===FALSE && is_null($unit_mean_lng)===FALSE) {
								$file_row="$unit_mean_lat,$unit_mean_lng,$noam_units[$k]\n";
								fwrite($fh2, $file_row);
							}
						} else {
							echo "paleo coords not properly set<br>";
						}
					}
					fclose($fh2); 
 
					if($n_units>1) {
						$file_name_r = 'temp.r';
						$fh3 = fopen($file_name_r, 'w') or die("can't open file");
						chmod($file_name_r, 0666);
						$r_script = "
						source('myFunctions.r')
						coords <- read.csv(file='$circle_file', header=TRUE)
						if(nrow(unique(coords[,1:2]))>1) {
							max.dist <- max(great.circle2(coords[,1:2]), na.rm=TRUE)
						} else {
							max.dist <- 'NA'
						}
						write.table(max.dist, file='temp_dist.csv', quote=FALSE, row.names=FALSE, col.names=FALSE, sep=',')
						";
						fwrite($fh3, $r_script);
						fclose($fh3);
						$cmd = "echo 'rscript <- \"$file_name_r\"; source(rscript)' | " . "/usr/bin/R --slave 2>&1";
						exec($cmd, $r_ran); 
						if(count($r_ran)>0) {
							echo "R (Matched great circle): ";
							print_r($r_ran); echo "<br>";
						}
						$handle = fopen("temp_dist.csv", "r");
						while ($row = fgetcsv($handle, 1000, ",")) $max_dist_noam = $row[0];
						fclose($handle);
						if($max_dist_noam=='NA') {
							$handle = fopen($circle_file, "r");
							echo $max_dist_noam."<br>";
							while ($file_row = fgetcsv($handle, 1000, ",")) {
								print_r($file_row); echo "<br>";
							}
							print_r($noam_units);echo "<br>";
							print_r($noam_collections);echo "<br><br>";
							fclose($handle);
						}
					} else {
						$max_dist_noam='NA';
					}
 
					// get global great circle distance				
					if(count($global_paleolat)>1 && count($global_paleolng)==count($global_paleolat)) {
						//  set up file for sending to R for great circle analysis
						$circle_file = "global_great_circle.csv";
						$fh2 = fopen($circle_file, 'w') or die("can't open file");
						chmod($circle_file, 0666);
						$file_row = "plat, plng\n";
						fwrite($fh2, $file_row);
 
						for($k=0; $k<count($global_paleolat); ++$k) {
							$file_row=$global_paleolat[$k].",".$global_paleolng[$k]."\n";
							fwrite($fh2, $file_row);
						}
						fclose($fh2);
 
						$file_name_r = 'temp.r';
						$fh3 = fopen($file_name_r, 'w') or die("can't open file");
						chmod($file_name_r, 0666);
						$r_script = "
						source('myFunctions.r')
						coords <- read.csv(file='$circle_file', header=TRUE)
						if(nrow(unique(coords))>1) {
							max.dist <- max(great.circle2(coords), na.rm=TRUE)
						} else {
							max.dist <- 'NA'
						}	
						write.table(max.dist, file='temp_dist.csv', quote=FALSE, row.names=FALSE, col.names=FALSE, sep=',')
						";
						fwrite($fh3, $r_script);
						fclose($fh3);
						$cmd = "echo 'rscript <- \"$file_name_r\"; source(rscript)' | " . "/usr/bin/R --slave 2>&1";
						exec($cmd, $r_ran); 
						if(count($r_ran)>0) {
							echo "R (Global great circle): ";
							print_r($r_ran); echo "<br>";
						}
						$handle = fopen("temp_dist.csv", "r");
						while ($row = fgetcsv($handle, 1000, ",")) $max_dist_global = $row[0];
						fclose($handle);
					} else {
						$max_dist_global = 'NA';
					}
 
					$n_env_temp = array();
					$n_env_temp2 = array();
 
					// get stratigraphic gap duration
					if($n_packages>0) {
						$pkg_occupied = array_combine(range(0,$n_packages-1), array_unique($noam_packages));
						$trunc_temp = array_intersect($truncating_packages, array_unique($noam_packages));
						$n_trunc_packages_occupied = count($trunc_temp);
						if($n_trunc_packages_occupied>0) {
							$trunc_temp = array_combine(range(0,$n_trunc_packages_occupied-1,1), $trunc_temp);
							// get n truncating columns
							$trunc_temp_columns = array();
							for($k=0; $k<$n_trunc_packages_occupied; ++$k) {
								$trunc_temp_columns[] = $my_pkg_col_id[$trunc_temp[$k]];
							}
							$n_trunc_columns_occupied = count(array_unique($trunc_temp_columns));
						} else {
							$n_trunc_columns_occupied = 0;
						}
 
						if($n_trunc_packages_occupied>1) {
							$trunc_packages_occupied = array_combine(range(0,($n_trunc_packages_occupied-1),1), $trunc_temp);
							//print_r($trunc_temp); echo "<br>";
							//print_r($trunc_packages_occupied); echo "<br>";
 
							$temp_gap = array();
							for($zz=0; $zz<$n_trunc_packages_occupied; ++$zz) {
								$temp_gap[] = $my_pkg_gap2[$trunc_packages_occupied[$zz]];
							}
 
							sort($temp_gap);
							//print_r($temp_gap); echo "<br><br>";
							$max_gap = max($temp_gap);
							$min_gap = min($temp_gap);
							$mean_gap = array_sum($temp_gap)/$n_trunc_packages_occupied;
 
							$h = intval($n_trunc_packages_occupied/2);
							if($n_trunc_packages_occupied % 2 == 0) { 
								$median_gap = ($temp_gap[$h] + $temp_gap[$h-1])/2; 
							} else { 
								$median_gap = $temp_gap[$h]; 
							}
						} else if($n_trunc_packages_occupied==1) {
							$trunc_packages_occupied = array_combine(range(0,($n_trunc_packages_occupied-1),1), $trunc_temp); // returns package keys
							$max_gap = $my_pkg_gap2[$trunc_packages_occupied[0]];
							$min_gap = $max_gap;
							$mean_gap = $max_gap;
							$median_gap = $max_gap;
						} else {
							$max_gap = NULL;
							$min_gap = NULL;
							$mean_gap = NULL;
							$median_gap = NULL;
						}
 
 
						// get environmental truncation
						for($k=0; $k<$n_packages; ++$k) {
							// get unit of oldest occurrence in interval 
							$q = "SELECT units.id, b.age_bottom, t.age_top, color FROM occurrences_temp2
							INNER JOIN units ON units.id=occurrences_temp2.unit_id
							INNER JOIN intervals b ON b.id=units.FO
							INNER JOIN intervals t ON t.id=units.LO
							WHERE taxon_no=".$this_genus[$j]." AND (occurrences_temp2.release_date <= now() OR occurrences_temp2.release_date IS NULL) 
							AND units.id IN (".$my_pkg[$pkg_occupied[$k]].") 
							AND b.age_bottom > ".$int_top[$i]." AND t.age_top < ".$int_bottom[$i]." 
							GROUP BY units.id
							ORDER BY b.age_bottom DESC, units.FO_h, t.age_top DESC, field(units.LO_h, 1,2,3,4,5,0)
							LIMIT 1"; //echo $q."<br>";
							$result = mysqli_query($connect_macrostrat, $q);
							if(mysqli_num_rows($result)==1) {
								$max_unit = mysqli_fetch_assoc($result);
								$occupied_unit = $max_unit['id'];
								$occupied_bottom = $max_unit['age_bottom'];
								$occupied_top = $max_unit['age_top'];
								$occupied_color = $max_unit['color'];
								$next_unit_temp = NULL;
 
								// get oldest unit in overlying intervals - may span into focal interval									
								if($i==0) {
									$previous_top = 418.7;
									$previous_bottom = $study_max;
								} else {
									$previous_top = $int_top[$i-1];
									$previous_bottom = $int_bottom[$i-1]; 
								}
								$q = "SELECT units.id, color, LO_h, FO_h, b.age_bottom, t.age_top FROM units
								INNER JOIN intervals b ON b.id=FO
								INNER JOIN intervals t ON t.id=LO
								WHERE units.id IN (".$my_pkg[$pkg_occupied[$k]].") AND b.age_bottom>".$previous_top." AND t.age_top<".$previous_bottom." 
								ORDER BY b.age_bottom DESC, FO_h, t.age_top DESC, field(LO_h, 1,2,3,4,5,0)
								LIMIT 1";
								$temp_pkg_result = mysqli_query($connect_macrostrat, $q);
								if(mysqli_num_rows($temp_pkg_result)>0) {
									$next_int_unit = mysqli_fetch_assoc($temp_pkg_result);
									$next_unit_temp = $next_int_unit['id'];
									if($next_int_unit['color'] != $occupied_color) {
										$n_env_temp[] = $pkg_occupied[$k];
	//											echo $occupied_unit.' '.$occupied_color.' - '.$next_int_unit['id'].' '.$next_int_unit['color']."<br>";
									}
								}
								mysqli_free_result($temp_pkg_result);
 
 
								// loop through the units within the focal interval - may not span into overlying interval									
								if(is_null($next_unit_temp)) $temp_exclude = $occupied_unit;
								else $temp_exclude = $occupied_unit.",".$next_unit_temp;
								$q = "SELECT units.id, color, LO_h, FO_h, b.age_bottom, t.age_top FROM units
								INNER JOIN intervals b ON b.id=FO
								INNER JOIN intervals t ON t.id=LO
								WHERE units.id IN (".$my_pkg[$pkg_occupied[$k]].") AND b.age_bottom > ".$int_top[$i]." AND t.age_top >= ".$int_top[$i]."
								AND b.age_bottom<=".$occupied_top." AND units.id NOT IN (".$temp_exclude.") 
								ORDER BY b.age_bottom DESC, FO_h, t.age_top DESC, field(LO_h, 1,2,3,4,5,0)";  //echo $q."<br>";
								$temp_pkg_result = mysqli_query($connect_macrostrat, $q); //if(mysqli_num_rows($temp_pkg_result)>2) echo $q."<br>";
								while($pkg_row=mysqli_fetch_assoc($temp_pkg_result)) {
									if($pkg_row['color']!=$occupied_color && $pkg_row['age_top']<$occupied_top) {
	//											echo $occupied_unit.' '.$occupied_color.' - '.$pkg_row['id'].' '.$pkg_row['color']."<br>";
										if($pkg_row['age_top']>=$int_top[$i]) {  // unit is contained entirely within the interval
											$n_env_temp[] = $pkg_occupied[$k]; //echo $n_env_truncation."<br>";
										} else if ($pkg_row['age_top']<$int_top[$i] && $pkg_row['age_bottom']>$int_top[$i]) {  // units spans interval boundary
											$n_env_temp[] = $pkg_occupied[$k]; //echo $n_env_truncation."<br>";
										} 
									}
									if($pkg_row['age_top']>=$int_top[$i] && $pkg_row['age_bottom']<=$int_bottom[$i]) $n_env_temp2[] = $pkg_occupied[$k];
								}
								mysqli_free_result($temp_pkg_result);
								if($occupied_top>=$int_top[$i] && $occupied_bottom<=$int_bottom[$i]) $n_env_temp2[] = $pkg_occupied[$k];
							}
							mysqli_free_result($result);
						} 
					}
 
					$n_env_truncation = count(array_unique($n_env_temp));
					$n_env_truncation2 = count($n_env_temp);
					$n_env_truncation3 = count(array_unique($n_env_temp2)); 
	//				if($n_env_truncation>0) {print_r($n_env_temp); echo "<br><br>";}
 
					// tabulate environmental gap length
					$env_gap_count = array();
					if($n_units>1) {
						$my_occupied_units = array_combine(range(0,$n_units-1,1), array_unique($noam_units));
						for($zzz=0; $zzz<$n_units; ++$zzz) {
							if($env_unit_top[$my_occupied_units[$zzz]] >= $int_top[$i] && $env_unit_top[$my_occupied_units[$zzz]] < $int_bottom[$i]) {
								$env_gap_count[] = $env_gap[$my_occupied_units[$zzz]];
							}
						}
						unset($my_occupied_units);
					} else if($n_units==1) {
						$my_occupied_units = array_combine(array(0), array_unique($noam_units));
						if($env_unit_top[$my_occupied_units[0]] >= $int_top[$i] && $env_unit_top[$my_occupied_units[0]] < $int_bottom[$i]) {
							$env_gap_count[] = $env_gap[$my_occupied_units[0]];
						}
						unset($my_occupied_units);
					}
 
					// tabulate stats
					$n_env_gap = count($env_gap_count);
					if($n_env_gap>1) {
						$min_env_gap=min($env_gap_count);
						$max_env_gap=max($env_gap_count);
						$mean_env_gap=array_sum($env_gap_count)/$n_env_gap;
						$h = intval($n_env_gap/2);
						if($n_env_gap % 2 == 0) { 
							$median_env_gap = ($env_gap_count[$h] + $env_gap_count[$h-1])/2; 
						} else { 
							$median_env_gap = $env_gap_count[$h]; 
						}	
					} else if($n_env_gap==1) {
						$min_env_gap=$env_gap_count[0];
						$max_env_gap=$env_gap_count[0];
						$mean_env_gap=$env_gap_count[0];
						$median_env_gap=$env_gap_count[0];
					} else {
						$min_env_gap=NULL;
						$max_env_gap=NULL;
						$mean_env_gap=NULL;
						$median_env_gap=NULL;
					}
					unset($env_gap_count);
					if(isset($file_row)) unset($file_row);
					$file_row = $int_id[$i].",".$int_name[$i].",".$int_bottom[$i].",".$int_top[$i].",".
					$my_genera[$j]['taxon_no'].",".$my_genera[$j]['genus_name'].",".$my_genera[$j]['class'].",".$my_genera[$j]['taxon_order'].",".$my_genera[$j]['family'].",".
					$my_genera[$j]['life_habit'].",".$my_genera[$j]['locomotion'].",".$my_genera[$j]['taxon_environment'].",".$my_genera[$j]['diet1'].",".$my_genera[$j]['diet2'].",".
					$n_species.",".$n_species_global.",".$n_coll.",".$n_occur.",".$global_endemic_to_interval.",".
					$my_genus_age[$this_genus[$j]]['noam_max'].",".$my_genus_age[$this_genus[$j]]['noam_min'].",".
					$my_genus_age[$this_genus[$j]]['global_max'].",".$my_genus_age[$this_genus[$j]]['global_min'].",".
					$max_paleolat_N.",".$max_paleolat_S.",".$n_matched_units_greater_n_45.",".$n_matched_units_greater_s_45.",".
					$max_dist_noam.",".$max_dist_global.",".
					$n_packages.",".$n_packages_total.",".$n_units.",".$n_units_total.",".					
					count($my_carb).",".count(array_intersect($noam_units, $my_carb)).",".count($my_clast).",".count(array_intersect($noam_units, $my_clast)).",".					
					count($truncating_packages).",".count(array_intersect($noam_packages, $truncating_packages)).",".					
					$n_env_truncation.",".$n_env_truncation2.",".$n_env_truncation3.",".					
					$min_gap.",".$max_gap.",".$mean_gap.",".$median_gap.",".					
					$min_env_gap.",".$max_env_gap.",".$mean_env_gap.",".$median_env_gap.",".$n_env_gap.",".					
					$n_cols.",".$n_columns_total.",".$n_trunc_columns_occupied."\n";
					fwrite($fh, $file_row);
					unset($file_row);
				}
			}
		}
		fclose($fh);
		$my_collections = implode(",",array_unique($my_collections));
 
 
		// get references and authorizers & enterers
		$q = "SELECT occurrences_temp2.collection_no, collections.reference_no, collections.authorizer, collections.enterer, collections.modifier,
		refs.author1init, refs.author1last, refs.author2init, refs.author2last, refs.otherauthors, refs.pubyr, refs.reftitle, refs.pubtitle, 
		refs.editors, refs.pubvol, refs.firstpage, refs.lastpage, refs.publication_type 
		FROM occurrences_temp2
		INNER JOIN pbdb.collections ON occurrences_temp2.collection_no=collections.collection_no
		INNER JOIN pbdb.refs ON refs.reference_no=collections.reference_no
		WHERE collections.collection_no IN (".$my_collections.")
		GROUP BY occurrences_temp2.collection_no
		ORDER BY occurrences_temp2.collection_no"; //echo $q."<br><br>";
		$result = mysqli_query($connect_macrostrat, $q);		
 
		$data_credits = array();
		$ref_credits = array();
		while($row=mysqli_fetch_assoc($result)) {		
 
			if(isset($data_credits[$row['authorizer']])) ++$data_credits[$row['authorizer']]['auth'];
			else $data_credits[$row['authorizer']] = array('auth'=> 1, 'enter' => 0, 'modify' => 0);
 
			if(isset($data_credits[$row['enterer']])) ++$data_credits[$row['enterer']]['enter'];
			else $data_credits[$row['enterer']] = array('auth'=> 0, 'enter' => 1, 'modify' => 0); 
 
			if(isset($data_credits[$row['modifier']])) ++$data_credits[$row['modifier']]['modify'];
			else $data_credits[$row['modifier']] = array('auth'=> 0, 'enter' => 0, 'modify' => 1);
 
			if(isset($ref_credits[$row['reference_no']])) {
				++$ref_credits[$row['reference_no']]['count'];
			} else {
				$author_temp = $row['author1last'].", ".$row['author1init'];
				if(is_null($row['otherauthors'])===FALSE) {
					$author_temp = $author_temp.' ,'.$row['author2init']." ".$row['author2last'].", ".$row['otherauthors'].".";
				} else if(is_null($row['author2last'])===FALSE) {
					$author_temp = $author_temp.' and '.$row['author2init']." ".$row['author2last'].".";
				}
 
				if($row['publication_type']=='journal article' || $row['publication_type']=='serial monograph' || $row['publication_type']=='abstract') {
					$full_ref = $author_temp." ".$row['pubyr'].". ".$row['reftitle'].". ".$row['pubtitle']." ".$row['pubvol'].":".$row['firstpage']."-".$row['lastpage'];
				} else if ($row['publication_type']=='guidebook' || $row['publication_type']=='book/book chapter' || $row['publication_type']=='Ph.D. thesis' || $row['publication_type']=='M.S. thesis') {
					if(is_null($row['editors'])) $editor_temp = '';
					else $editor_temp = $row['editors']." (eds.) ";
 
					$full_ref = $author_temp." ".$row['pubyr'].". ".$row['reftitle'].". In ".$editor_temp.". ".$row['pubtitle']." ".$row['firstpage']."-".$row['lastpage'];
				} else {
					if(is_null($row['reftitle']) || $row['reftitle']=='') $temp_pubtitle = '';
					else $temp_pubtitle = $row['reftitle'].". ";
					$full_ref = $author_temp." ".$row['pubyr'].". ".$temp_pubtitle." Unpublished data";
				}				
				$ref_credits[$row['reference_no']] = array('count'=> 1, 'full' => $full_ref, 'type' => $row['publication_type']);
			}
		}
		mysqli_free_result($result);
 
		// write text files
		$credit_file = 'collection_credits.txt';
		$fh = fopen($credit_file,'w');
		chmod($credit_file, 0777);
		$file_row = "name\tauthorized\tentered\tmodified\n";
		fwrite($fh, $file_row);
 
		$my_people = array_keys($data_credits);
		for($i=0; $i<count($data_credits); ++$i) {
			if(is_null($my_people[$i])===FALSE && $my_people[$i]!='') {
				$file_row = $my_people[$i]."\t".$data_credits[$my_people[$i]]['auth']."\t".$data_credits[$my_people[$i]]['enter']."\t".$data_credits[$my_people[$i]]['modify']."\n";
				fwrite($fh, $file_row);
			}
		}
		fclose($fh);
		echo 'File <a href="'.$credit_file.'">'.$credit_file.'</a> saved<BR>';
 
		$ref_file = 'references.txt';
		$fh = fopen($ref_file,'w');
		chmod($ref_file, 0777);
		$file_row = "reference_no\tcitation\tn_collections\n";
		fwrite($fh, $file_row);
 
		$my_refs = array_keys($ref_credits);
		for($i=0; $i<count($ref_credits); ++$i) {
			$file_row = $my_refs[$i]."\t".$ref_credits[$my_refs[$i]]['full']."\t".$ref_credits[$my_refs[$i]]['count']."\n";
			fwrite($fh, $file_row);
		}
		fclose($fh);
		echo 'File <a href="'.$ref_file.'">'.$ref_file.'</a> saved<BR>';
 
		$coll_file = 'collections.csv';
		$fh = fopen($coll_file,'w');
		chmod($coll_file, 0777);
		fwrite($fh, $my_collections);
		fclose($fh);
		echo 'File <a href="'.$coll_file.'">'.$coll_file.'</a> saved<BR>';
	}
 
 
 
	//////////////
	//
	// stuff at the end
	//
	//////////////
	if(isset($_POST['submit'])) {
		$t1 = microtime(true);
		$time_diff = $t1 - $t0;
		if ($time_diff/60 < 1) {
			$diff_seconds = round($time_diff, 3);
			echo "<br>This process took ".$diff_seconds." seconds";
		} else if ($time_diff>=1 & $time_diff<3600) {
			$diff_min = round($time_diff/60, 3);
			echo "<br>This process took ".$diff_min." minutes";
		} else {
			$diff_hour = round($time_diff/3600, 3);
			echo "<br>This process took ".$diff_hour." hours";
		}
	}
?>
 
 
<?php 
	require_once('../footer.html');
	exit();
?>