<?php
//
// sheets.php
//
// Version 1.0  2006 Sept 31
//
// Web database for Herbarium Sheets
// Developed for PH at the Academy of Natural Sciences with
// funding from the National Science Foundation.
//
// Copyright (C) 2006 The Academy of Natural Sciences.
//
// Developed with funding under NSF DBI:BRC:0545170
// Released under the GPL as specified in the grant as a deliverable.
//
// Code for version 1.0 written by Paul J. Morris mole@morris.net
// as a work for hire for The Academy of Natural Sciences.
//
/*
    sheets.php A web front end for collections databases.
    Copyright (C) 2006 The Academy of Natural Sciences
                                                                                
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation; either version 2 of the License, or
    (at your option) any later version.
                                                                                
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.
                                                                                
    You should have received a copy of the GNU General Public License
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

    Contacts: bdim@acnatsci.org, herbarium@acnatsci.org

    A copy of the GPL should be included in the file gpl.txt
    distributed with this program.
*/

// clear all variables and set defaults
// all variables listed in globals or passed to functions should be set to default values here.

include_once("page_header.php");

// collection object data related variables
$genus = "";
$species = "";
$taxon_name = "";
$collnumber = "";
$collector= "";
$country = "";
$state = "";
$county = "";
$locality = "";
$operator = "";
$ph_number= "";  // accession number is an identifier for herbarium sheets
$barcode_number = "";  // new unique identifier for herbarium sheets, started in 2006.
$herb_sheet_id = ""; // internal surrogate numberic primary key for herbarium_sheets.
$date_collected = "";

// display related variables
$forcelabels = "";
$offset = 0;
$MAX_RESULTS = 200;
$ajax_header_written = false;  // flag to ensure that javscript functions are only written once

// bounding box critera for search for points
$bounding_lat_n = 0;
$bounding_lat_s = 0;
$bounding_long_e = 0;
$bounding_long_w = 0;

$mode = "main"; // switch block at end of code uses mode to display page in desired form

$view = "";     // determines format of search results
// effectively 4 views are envisioned:
// 1) browse lists of terms linked to searches (browse aggregate, done through different modes rather than $view)
// 2) aggregate search results with results linked to additional searches (aggregate).
// 3) summary search results with one row for each collection object (list of records).
// 4) detail results for an individual collection objects (details for one record).


// include external connection library
// located in a phpexecutable directory off of the web tree.
include_once("connect_lib_ph.php");
include_once("page_header.php");

// get and sanitize fields from url or form submitted via GET.
// variables related to what sort of page is displayed
if ($_GET['mode']!="") {
   // mode is the fundamental switch for this page
   // see the switch statement at the end of the code
   // for which functions (and thus page appearance)
   // are invoked by which values of mode
   // Note: mode=details is a synonym for mode=search&view=all
   // Note: view=mapfull sets mode to searchmap to display map.
   $mode = preg_replace("/[^A-Za-z]/","",$_GET['mode']);
}
if ($_GET['view']!="") {
   // view determines how a list of search results will be shown
   // two different summary views and full details are supported
   $view = preg_replace("/[^A-Za-z\ ]/","",$_GET['view']);
   //<OPTION VALUE="taxa" SELECTED>List of Taxa with links to full records
   //<OPTION VALUE="full">Catalog numbers, Taxa, and Localities
   //<OPTION VALUE="all">Full Records
   // Note: mode=details sets view=all
   if ($view=="mapfull") {
     $mode = "searchmap";
   }
}
  
// variables for search:
if ($_GET['genus']!="") {
   $genus = preg_replace("/[^A-Za-z\%\*\_\?]/","",$_GET['genus']);
}
if ($_GET['species']!="") {
   $species = preg_replace("/[^a-z\%\*\_\?]/","",$_GET['species']);
   // species from form searches both species and subspecies fields.
}
if ($_GET['collnumber']!="") {
   $collnumber = preg_replace("/[^A-Za-z\ \-0-9\%\*\_\?]/","",$_GET['collnumber']);
}
if ($_GET['collector']!="") {
   $collector = preg_replace("/[^A-Za-z::Alpha::\ \-\%\*\_\?]/","",$_GET['collector']);
}
if ($_GET['ph_number']!="") {
   // accession number is the historical hopefully unique identifier for a herbarium sheet
   $ph_number = preg_replace("/[^A-Za-z\ 0-9\%\*\_\?]/","",$_GET['ph_number']);
}
if ($_GET['barcode']!="") {
   // accession number is the historical hopefully unique identifier for a herbarium sheet
   $barcode = preg_replace("/[^0-9]/","",$_GET['barcode']);
}
if ($_GET['herb_sheet_id']!="") {
   // herb sheet id is the internal surrogate numeric unique identifier for a herbarium sheet
   $herb_sheet_id = preg_replace("/[^0-9]/","",$_GET['herb_sheet_id']);
}
if ($_GET['country']!="") {
   $country = preg_replace("/[^A-Za-z\ \.\_\%\*\_\?]/","",$_GET['country']);
}
if ($_GET['state']!="") {
   $state = preg_replace("/[^A-Za-z\ \_\%\*\_\?]/","",$_GET['state']);
}
if ($_GET['county']!="") {
   $county = preg_replace("/[^A-Za-z\ \.\%\*\_\?\(\)]/","",$_GET['county']);
}
if ($_GET['locality']!="") {
   $locality = preg_replace("/[^A-Za-z\ \.\%\*\_\?]/","",$_GET['locality']);
}
if ($_GET['date_collected']!="") {
   $date_collected = preg_replace("/[^0-9\?\_]/","",$_GET['date_collected']);
   // treat as year collected, append wildcard at front to match %1832 to 11-MAY-1832
   $date_collected = "%".substr($date_collected,strlen($date_collected)-4,4);
}
//if ($_GET['operator']!="") {
//   $operator = preg_replace("/[^a-z\=]/","",$_GET['operator']);
//}
if ($_GET['bounding_lat_n']!="") {
   $bounding_lat_n = preg_replace("/[^0-9\-\.]/","",$_GET['bounding_lat_n']);
   $bounding_lat_s = preg_replace("/[^0-9\-\.]/","",$_GET['bounding_lat_s']);
   $bounding_long_e = preg_replace("/[^0-9\-\.]/","",$_GET['bounding_long_e']);
   $bounding_long_w = preg_replace("/[^0-9\-\.]/","",$_GET['bounding_long_w']);
   // Extent limits on blue marble:
   // EXTENT -180.000000 -89.900000 180.000000 83.674733
   if ($bounding_lat_n>90) { $bounding_lat_n= 90; }
   if ($bounding_lat_n<-90) { $bounding_lat_n= -90; }
   if ($bounding_lat_s>90) { $bounding_lat_s= 90; }
   if ($bounding_lat_s<-90) { $bounding_lat_s= -90; }
   if ($bounding_long_e>180) { $bounding_long_e= 180; }
   if ($bounding_long_e<-180) { $bounding_long_e= -180; }
   if ($bounding_long_w>180) { $bounding_long_w= 180; }
   if ($bounding_long_w<-180) { $bounding_long_w= -180; }
}
$bounding_query = "";
// add bounding box to query if specified
if ($bounding_lat_n!="") {
   $bounding_long_w = -1.0 * $bounding_long_w;
   $bounding_long_e = -1.0 * $bounding_long_e;
   $bounding_query .= " and (lat_dd < '$bounding_lat_n' and lat_dd>'$bounding_lat_s' and long_dd>'$bounding_long_w' and long_dd<'$bounding_long_e') ";
}
// Note: Additional map specific GET variables are obtained and sanitized in searchmap()

// autoset like operator if a wildcard is present in the search
$terms = "$genus$species$collnumber$country$state$county$locality$ph_number$collector";
// barcode excluded, exact match only.
$terms = preg_replace("/[^\%\*\?\_]/","",$terms);
if (strlen($terms) > 0 ) {
  $operator = "like";
}
if ($operator=="") { $operator = "="; }

// ready to display requested view of page

// open a connection to the target database
$connection = ansp_connect_wrapper("PH");

if ($connection) {
   // display content for desired mode
   switch($mode) {
     case ""; main(); break;
     case "search"; search(); break;
     case "searchmap"; mapviewer(); $view="map"; search(); break;
     case "details"; $view="all"; search(); break;
     case "count"; counttaxa(); break;
     case "main"; main(); break;
     case "listgenera"; liststuff("genera"); break;
     case "listfamilies"; liststuff("families"); break;
     case "listcollections"; liststuff("collections"); break;
     case "listcountries"; liststuff("countries"); break;
     case "listtypes"; liststuff("types"); break;
     case "coflookup"; getcastypedata(); break;
     default; echo "Mode unkown:$mode";
   }
   pg_close($connection);
} else {
   // connection failure, database is probably down
   echo "Error: Unable to connect to database.<BR>";
}

include_once("page_footer.php");

// *** all done with processing code here ***

// ******************************************
// **** supporting functions begin here *****
// ******************************************


function ajax_header() {
// javascript functions to include near top of html document.
// functions support retrieval of data and insertion into current
// page without reloading the current page.
if ($ajax_header_written==false) {
  // make sure function definitions are only written once
  // integration with mapping php code could allow two
  // calls to ajax_header(), this global is intended to
  // prevent the javascript code from being written twice
  // into one html document.  
  $ajax_header_written=true;
echo '
<script type=text/javascript>
<!--
     // get a handle to an html tag that has an id= element.
     function getById(t_id) {
        result = null;
        n4 = (document.layers && typeof document.classes != "undefined");
        ie = navigator.userAgent.toLowerCase().indexOf("msie") != -1 && document.all ;
        result = (
           n4 ? (document.layers[t_id] || null)
              : ie ? (document.all[t_id] || null)
              : (document.getElementById(t_id) || null)
        );
        return result;
     }

     // Create a new request using the appropriate call
     // for standards compliant browsers with special case
     // handling for various versions of IE.
     // Returns a request object or false.                                                                
     function createXMLHttpRequest() {
        var ua;
  
        ua = false;
                                                                                                                        
        if(window.XMLHttpRequest) {
            try {
              ua = new XMLHttpRequest();
              //ua.overrideMimeType("text/xml");
            } catch(e) {
              ua = false;
            }
        } else if(window.ActiveXObject) {
            try {
              ua = new ActiveXObject("Microsoft.XMLHTTP");
            } catch(e) {
              try {
                  ua = new ActiveXObject("Msxml2.XMLHTTP");
              } catch(e) {
                  ua = false;
              }
            }
        }
        return ua;
     }

     var requests = new Array()     
     var requestrow = 0;

     // example call to a page that pulls in data from an exxternal resource
     // replace cof call with ipni call.
     function RequestCOF(genus,species,subspecies,ph_number,myName) {
        requests[requestrow] = false;
        var genus = trim(genus);
        var species = trim(species);
        var subspecies = trim(subspecies);
        var ph_number = trim(ph_number);
        var target = trim(genus + " " + species + " " + subspecies)
        requests[requestrow] = createXMLHttpRequest();
        var requesttarget = "";
        result = "";
        requesttarget = getById(myName);
        if (requests[requestrow]) {
            if (subspecies != "") {
               getstuff = "biodiversity_databases/cof_lookup.php?genus=" + genus + "&species=" + species + "&subspecies=" + subspecies + "&ph_number=" + ph_number;
            } else {
               getstuff = "biodiversity_databases/cof_lookup.php?genus=" + genus + "&species=" + species + "&ph_number=" + ph_number ;
            }
            // need to define function with local variables req and requesttarget
            // or else function will examine current values of globals on invocation.
            // that is, handleResponse(requestrow,requesttarget) wont work as expected.
            var req = requestrow;
            requests[requestrow].onreadystatechange = function() { handleResponse(req,requesttarget); };
            requests[requestrow].open("GET", getstuff, true);
            requests[requestrow].send(null);
        };
        requestrow = requestrow + 1;
     }

     // event handler for the request
     function handleResponse(req,requesttarget) {
        var obj = requests[req];
        if (obj.readyState == 4) {
            if (obj.status == 200) {
                // result document came back, display it directly for
                // the user without parsing the xml for specific content.
                requesttarget.innerHTML = obj.responseText;
            } else {
                // display error message on search failure.
                requesttarget.innerHTML = requesttarget.innerHTML + "<BR>Search failed.<BR>";
            }
        } else {
           if (obj.readyState == 1 ) {
               // on initial connection tell user that something is happening
               requesttarget.innerHTML = requesttarget.innerHTML + "<BR>Searching...";
           }
        }
     }

     // remove leading and trailing spaces from a string
     function trim(str) {
          return str.replace(/^\s*|\s*$/g,"");
     }
-->
</script>
';
}
}


function main(){
// home page with search form and information about the site
echo "<h3>Online Catalog of the herbarium of the Academy of Natural Sciences, Philadelphia (PH)</h3>

<p>The Virtual Types Project began in 2006 and has thus far yielded more than 12,000 digitized (i.e., databased and imaged) specimens.  The focus has been on types but a number of other specimens have also been digitized, mostly in response to data requests from researchers. Type specimens at PH are filed alphabetically by genus, and specimens are being digitized in that order. </p>

<p>As of April 2008, progress has been as follows:
<ul><li> 8,500 vascular plants (to genus Heliotropium)</li>
<li>150 lichen types (to genus Lecanora)</li>
<li>220 bryophyte types (all types digitized)</li>
<li>3,000 fungal types (up to genus Hypoderma)</li>
<li>130 algal types (all types digitized)</li>
</ul>

<p>An additional ca. 12,000 specimens from the general historically important collections have also been databased but images are not yet available. </p>

<p>Digitization is ongoing such that this database is a work in progress.</p>

<p>So, you are more than welcome to consult this page, but please be patient and try searching again using other search terms if you do not find what you are looking for.  </p>";
echo "<FORM  ACTION='sheets.php' METHOD='GET'>
<INPUT TYPE='hidden' NAME='mode' VALUE='search'> ";
echo '<INPUT TYPE="hidden" NAME="resultstart" VALUE="0">
<TABLE>
<a name=primarycontent></a>Search for:
<TR>
  <TD>
   <LABEL for="genus">Genus:</LABEL> <a href="sheets.php?mode=listgenera">[Browse]</a></TD><TD><INPUT Name=genus>
  </TD><TD>
   <label for="species">Species/Subspecies:</label></TD><TD><INPUT Name=species>
  </TD>
</TR>
<TR>
  <TD>
   <label for=collnumber>Collectors Number:</label></TD><TD><INPUT Name=collnumber>
  </TD><TD>
   <label for=ph_number>Accession Number:</label></TD><TD><INPUT Name=ph_number>
  </TD>
</TR>
<TR>
  <TD>
   <label for=country>Country:</label> [<a href="sheets.php?mode=listcountries">Browse</a>]</TD><TD><INPUT Name=country>
  </TD>
  <TD>
   <label for=state>State:</label></TD><TD><INPUT Name=state>
  </TD>
</TR>
<TR>
  <TD>
   <label for=county>County:</label></TD><TD><INPUT Name=county>
  </TD>
  <TD>
   <label for=locality>Locality:</label></TD><TD><INPUT Name=locality>
  </TD>
</TR>
<TR>
  <TD>
   <label for=date_collected>Year Collected:</label></TD><TD><INPUT Name=date_collected maxlength=5>
  </TD>
  <TD>
   <label for=collector>Collector:</label></TD><TD><INPUT Name=collector>
  </TD>
  </TR>
<TR>
    <TD>
           <INPUT TYPE="submit" NAME="submit" VALUE="Perform Search"  accesskey="4">
        </TD>
    <TD>&nbsp;</TD>
    <TD><label for=view>View:</label></TD>
    <TD><SELECT NAME="view" SIZE="1">
        <OPTION VALUE="taxa" SELECTED>List of Taxa with links to full records
        <OPTION VALUE="full">Accession Numbers, Taxa, and Localities
        <OPTION VALUE="all">Full Records
<!--
        <OPTION VALUE="map">Map and georeferenced specimen records
-->
    </SELECT></TD>
</TR>
</TABLE>
</FORM>
<p>[<a href="sheets.php?mode=listtypes">Browse Types</a>]</p>
<p>Search hints: Use % or * as wildcards to match any number of characters, and ? or _ to match one character.  Use 197_ in year collected to search for all 1970s records.</p>
<div align=center>
<table><tr><td>
<a href="sheets.php?mode=details&ph_number=50046"><img src="get_image.php?imageid=766" height=200 width=148 alt ="PH Barcode 50046 Herbarium sheet of Adonis davurica "></a>
</td><td>
<font size=-1>
<h4>Accessibility: <A HREF="accesskeys.html" accesskey=0>Access keys</A></h4>
<dl>
<dt>Accesskey 1</dt><dd>List of collections</dd>';
$page = "http://ph.acnatsci.org".$_SERVER['REQUEST_URI'];
echo "<dt><a href='$page#primarycontent' accesskey='2'>";
echo "Accesskey 2</dt><dd>Primary Content / Skip Navigation</a></dd>
<dt>Accesskey 4</dt><dd>Search Fish Collection Database</dd>
<dt>Accesskey 8</dt><dd>Terms and Conditions</dd>
</dl>
</font>
</td></tr></table>
</div>
";
}

function search_terms_form() {
global $genus, $species, $collnumber, $country, $state, $county, $locality, $ph_number, $date_collected, $view, $collector;
// smaler version of the search form that can be displayed on search result pages

echo "<FORM  ACTION='sheets.php' METHOD='GET'>";
echo "<INPUT TYPE='hidden' NAME='mode' VALUE='search'> ";

$vts="SELECTED"; $vfs=""; $vas=""; $vms="";
if ($view == "full") {
   $vts=""; $vfs="SELECTED"; $vas=""; $vms="";
}
if ($view == "all") {
  $vts=""; $vfs=""; $vas="SELECTED";
}
if ($view == "mapfull") {
  $vts=""; $vfs=""; $vas=""; $vms="SELECTED";
}
echo "<INPUT TYPE='hidden' NAME='resultstart' VALUE='0'>
<TABLE>
<TR>
  <TD>
   <label for=ph_number style='{ font-size: smaller }'>PH Number:</label><BR>
   <INPUT Name=ph_number value='$ph_number' size=12>
  </TD>
  <TD>
   <LABEL for='genus' style='{ font-size: smaller }' >
   <a href='sheets.php?mode=listgenera'>Genus:</a></LABEL><BR>
   <INPUT Name=genus value='$genus' size=12>
  </TD>
  <TD>
   <label for='species' style='{ font-size: smaller }'>Species:</label><BR>
   <INPUT Name=species value='$species' size=12>
  </TD>
  <TD>
   <label for=collnumber style='{ font-size: smaller }'>Collector's#:</label><BR>
   <INPUT Name=collnumber value='$collnumber' size=12>
  </TD>
  <TD>
    <label for=date_collected style='{ font-size: smaller }'>Year Collected:</label><BR>
    <INPUT Name=date_collected value='$date_collected' size=12 maxlength=5>
  </TD>
  <td>
   <label for=collector style='{ font-size: smaller }' >Collector:</label><BR>
   <INPUT Name=collector value='$collector' size=25 maxlength=30>
  </td>
</tr>
<tr>  
  <TD>
    <INPUT TYPE='submit' NAME='submit' VALUE='SEARCH'  accesskey='4'>
  </TD>
  <TD>
   <label for=country style='{ font-size: smaller }'>
   <a href='sheets.php?mode=listcountries'>Country:</a></label><BR>
   <INPUT Name=country value='$country' size=12>
  </TD>
  <TD>
    <label for=state style='{ font-size: smaller }'>State:</label><BR>
    <INPUT Name=state value='$state' size=12>
  </TD>
  <TD>
    <label for=county style='{ font-size: smaller }'>County:</label><BR>
    <INPUT Name=county value='$county' size=12>
  </TD>
  <TD>
    <label for=locality style='{ font-size: smaller }'>Locality:</label><BR>
    <INPUT Name=locality value='$locality' size=12>
  </TD>
  <TD>
    <label for=view style='{ font-size: smaller }'>View:</label><BR>
    <SELECT NAME='view' SIZE='1'>
";
   echo "
        <OPTION VALUE='taxa' $vts >List of Taxa with links to full records
        <OPTION VALUE='colls' $vfs >Collector numbers, Taxa, and Localities
        <OPTION VALUE='full' $vfs >PH numbers, Taxa, and Localities
        <OPTION VALUE='all' $vas >Full Records
<!--
        <OPTION VALUE='mapfull' $vms>Map and georeferenced specimen records
-->
   ";
echo "
    </SELECT>
  </TD>
</TR>
</TABLE>
</FORM>
";

}


// function search() runs a search based on query parameters provided
function search() {
global $genus, $species, $collnumber, $country, $state, $county, $locality, $operator, $resultstart, $result, $numresults, $view, $query, $ph_number, $date_collected, $bounding_query, $MAX_RESULTS, $connection, $herb_sheet_id, $collector;

// FIX ME! mapping turned off.
$showmap = true;
$showmap = false;

if ($view=="map") {
   $view="full";
   $showmap = false;
} else {
  // display the page header, ajax code, and the short version of the search form
  ajax_header();
  search_terms_form();
  if ($view=="mapfull") {
     $view="full";
  }
}

// Set up to search and display results with code that loops through arrays of fields/values
// add new search terms here, in defaults list, and in get/sanitize code at start of program.
// Build the query in three parts:

// Part 1
// This block specifies the fields or concepts to be searched (in the where clause).
// The fields to be displayed and their labels are specified below in switch $view.
// $num_were_fields is high index in $where_fields[] and $targets
// Fields used here may or may not map directly to database fields, in cases where they don't
// special case handling is needed in building the where clause in part 3.
// Fields to select are handled separately in part 2.
//
$where_fields = array('anngenus', 'annspecies', 'full_coll_number', 'country', 'state_province', 'county_parish', 'locality', 'accession_no', 'year_collected','first_collector_last', 'herbarium_sheet.herb_sheet_id');
$num_where_fields = count($where_fields);
// $directmap  indicates whether each field maps directly to a database field or needs special case handling.
// true = directly maps, false = special handling.
$directmap = array(false, false, true, true, true, true, true, false, true, true, true);
// $ targets is list of criteria provided by user
$targets = array($genus, $species, $collnumber, $country, $state, $county, $locality, $ph_number, $date_collected, $collector,$herb_sheet_id);
// build part of a get hyperlink repeating the search
$requested_values = "&genus=$genus&species=$species&country=$country&collnumber=$collnumber&state=$state&county=$county&locality=$locality&ph_number=$ph_number&date_collected=$date_collected&collector=$collector";


// set some default values
$soundex_query = $query;
$wildcardsonly = "True";
$getbit = "";
$query = "";
$order = "";
$tofield = 0;
$zero_is_record_id = false;
$first_col_is_count_search = false;

// Part 2
// Build the framework for the select .. from .. order parts of the query,
// specify the fields to be shown (in the select .. from clause),
// and specify the labels to use for these fields on the web page.
//
// select list and display use one or two arrays containing a list of select fields and labels
// up to four variables may be involved.
// In current implementation these are all overwritten in the switch block below
// and included here just for documentation.
//
// number of array elements, used to iterate $x in $row[$x] in results (thus starting with 0)
// required after switch block.
// $num_select_fields = 8;
//
// is the first array element $select_fields the collection object id (herb_sheet_id):
// if so, $row[0] gets special handling
// required after switch block.
// $zero_is_record_id = false;
//
// $select_fields is list of field names to be displayed.
// $select_fields may be iterated through in part 2 (or not if fields are included in query).
// not required after switch block, fields order here will be the field order in $row[].  
// $select_fields = array('anngenus', 'annspecies', 'full_coll_number', 'country', 'state_province', 'county_parish', 'locality', 'accession_no', 'year_collected');
//
// $select_field_labels is default labels to apply to field names on list display
// $select_field labels will be iterated through in display, order here needs to match field order in $row[].
// thus if $select_fields is set and iterated through, $selectfield_labels must match $select_fields
// or if $select_fields is not used, $selectfield_labels must match field order in $querypre = "select ... ".
// required after switch block.
// $selectfield_labels = array('anngenus', 'annspecies', 'full_coll_number', 'country', 'state_province', 'county_parish', 'locality', 'accession_no', 'year_collected');
// (if true, used to build link to collection object details rather than search)
//
// $querypre is the select from clause of the query, required after switch
// $order is the order clause of the query, required after switch.

// in some cases use a different select field/select field list than the defaults
switch ($view) {
  case "taxa":
     $tofield = 3;  // high index in $selectfield_labels[]
     // querypre includes fields, so we don't need $select_fields
     $selectfield_labels = array('Genus', 'Species', 'Country','Count');
     $zero_is_record_id = false;
     $first_col_is_count_search = true;
     // Retrieves brief list of identifications and countries
     $querypre = "select anngenus, annspecies, specimens.country, count(*) from specimens left join annotation_table on specimens.specimen_id = annotation_table.specimen_id  left join herbarium_sheet on specimens.herb_sheet_id = herbarium_sheet.herb_sheet_id where ";
     $order=" group by anngenus, annspecies, anninfrasp_taxon, specimens.country order by anngenus, annspecies, anninfrasp_taxon, specimens.country ";
     break;
  case "colls":
     $tofield = 4;  // high index in $selectfield_labels[]
     $select_fields = array('full_coll_number', 'anngenus','annpecies', 'anninfrasp_taxon', 'country');
     $zero_is_record_id = false;
     $first_col_is_count_search = true;
     $selectfield_labels = array('Collector\'s number','Genus', 'Species',  'Country','Count');
     // Retrieves brief list of collectors, identifications and countries
     $querypre = "select full_coll_number, anngenus, annspecies, specimens.country, count(*) from specimens left join annotation_table on specimens.specimen_id = annotation_table.specimen_id  left join herbarium_sheet on specimens.herb_sheet_id = herbarium_sheet.herb_sheet_id where ";
     $order=" group by anngenus, annspecies, anninfrasp_taxon, specimens.country, full_coll_number order by anngenus, annspecies, anninfrasp_taxon, specimens.country ";
     break;
  case "all":
     // all is a special case - only a single core collection object (a herbarium sheet)
     // is retrieved by its internal id, then data from related tables are retrieved using a series
     // of additional queries.  
     $tofield = 0;  // high index in $selectfield_labels[]
     // querypre includes field, so we don't need $select_fields
     $select_fields = array('herb_sheet_id');
     $selectfield_labels = array('herb_sheet_id');
     $zero_is_record_id = true;  // but should be irrelevant
     // Used to retrieve full details for a single herbarium sheet.
     // Note that the results of this query are passed on to formatted_records().
     $querypre = "SELECT distinct herbarium_sheet.herb_sheet_id FROM herbarium_sheet left join specimens on herbarium_sheet.herb_sheet_id = specimens.herb_sheet_id left join annotation_table on specimens.specimen_id = annotation_table.specimen_id  where ";
     $order=" order by herb_sheet_id ";
     break;
default:
     // case "full"
     $tofield = 8;  // high index in $selectfield_labels[]
     $select_fields = array('herbarium_sheet.herb_sheet_id','accession_no || \'[Barcode:\' || sheet_barcode_number || \']\'','anngenus', 'annspecies', 'country', 'state_province', 'county_parish', 'full_coll_number', 'year_collected');
     $selectfield_labels = array('ID','PH accession number', 'Genus','Species', 'Country', 'State', 'County', 'Collector\'s number', 'Year collected');
     $zero_is_record_id = true;  //
     // Retrieves more detailed list of specimens.
     $querypre = "select ".$select_fields[0]." ";
     for ($x=1; $x<=$tofield; $x++) {
        $querypre = $querypre.", ".$select_fields[$x]." ";
     }
     $querypre = $querypre." from specimens left join annotation_table on specimens.specimen_id = annotation_table.specimen_id left join herbarium_sheet on specimens.herb_sheet_id = herbarium_sheet.herb_sheet_id where ";
     $order=" order by anngenus, annspecies, country, state_province, county_parish ";
}

// test that at least some search criterion was provided.
$allblank = 'True';
for ($x=0; $x<=$num_where_fields; $x++) {
  if ($targets[$x]!="") {
    $allblank = 'False';
  }
}

// Part 3
// build the where clause for the query using the parameters provided
// by the user.
if ($allblank=='True') {
   // handle case of all terms being blank after sanitizer has emptied them
   echo("<B>Error:</B> You didn't specify any search terms");
} else {
   // build where clause
   $firstphrase = "True";
   // iterate throught $where_fields and $targets to build where clause
   // create two where clauses, one for exact/like match, one for soundex match
   for ($x=0; $x<=$num_where_fields; $x++) {
      if ($targets[$x] != "") {
         $targets[$x] = str_replace("*","%",$targets[$x]);
         $targets[$x] = str_replace("?","_",$targets[$x]);
         if ($targets[$x] != "%") { $wildcardsonly = "False"; }
         if ($firstphrase=='True') {
            $firstphrase = "False";
         } else {
            $query=$query." and ";
            $soundex_query=$soundex_query." and ";
         }
         $terms = preg_replace("/[^\%\*\?\_]/","",$targets[$x]);
         if (strlen($terms) > 0 ) { $operator = "like"; } else { $operator = "="; }

         // special case handling of query terms that don't map directly onto database fields
         if (!$directmap[$x]) {
            // build special case parts of where clause
            if ($where_fields[$x]=="anngenus") {
               //$query= $query." ($where_fields[$x] $operator '$targets[$x]' ";
               $query= $query." (anngenus $operator '$targets[$x]' ";
               $query= $query." or basionym like '$targets[$x] %') ";
               $query .= $clause;
               $soundex_query= $soundex_query." soundex(anngenus) = soundex('$targets[$x]') ";
            }
            if ($where_fields[$x]=="annspecies") {
               //$clause = " ($where_fields[$x] $operator '$targets[$x]' ";
               //$clause .= " or subspecies $operator '$targets[$x]' ";
               //$clause .= " or variety $operator '$targets[$x]' ";
               //$clause .= " or form $operator '$targets[$x]' ";
               $clause .= " (basionym like '% $targets[$x] %' ";
               $clause .= " or anninfrasp_taxon $operator '$targets[$x]' ";
               $clause .= " or annspecies $operator '$targets[$x]') ";
               $query .= $clause;
               $soundex_query .= " ( soundex(annspecies) = soundex('$targets[$x]') ";
               $soundex_query .= " or soundex(anninfrasp_taxon) = soundex('$targets[$x]') ) ";
            }
            if ($where_fields[$x]=="accession_no") {
               $clause =  " (sheet_barcode_number $operator '$targets[$x]' ";
               $clause .= " or accession_no $operator '$targets[$x]') ";
               $query .= $clause;
               $soundex_query .= $clause;
            }
         } else {
            // just add the term to the where clause
            $query= $query." $where_fields[$x] $operator '$targets[$x]' ";
            $soundex_query= $soundex_query." soundex($where_fields[$x]) = soundex('$targets[$x]') ";
         }
         $getbit .= "$where_fields[$x]=$targets[$x]&";
      }
   }
}
$onmap = "";
if ($showmap==false) {
  // FIX ME! mapping turned off?
  //$onmap = " and not isnull(lat_dd) ";
}
$foundonmap = " found.";
if ($bounding_query !="") {
  $foundonmap = " shown on map.";
}
// assemble the select, where, and order parts of the query
$querybit = $query;
$query=$querypre.$query.$onmap.$bounding_query.$order;
$soundex_query = $querypre.$soundex_query.$onmap.$bounding_query.$order;

// run query
if ($wildcardsonly == "True") {
  echo ("<B>Error:</B> Wildcard only searches are not allowed.  You must enter some search criteria.<BR>");
} else {

  $paginglinks = "";
  // run the assembled query
  if ($view=="all") {
     // Show list of full records for each matching herbarium sheet.
     //
     // view==all is a special case
     //
     // need to run multiple additional queries to retrieve records related
     // to the master collection object (e.g. herbarium sheet->specimens->annotations->taxa)
     //
     $result = pg_query($query);
     $numresults = pg_num_rows($result);
     for ($x=0;$x<$numresults;$x++) {
        $row = pg_fetch_row($result);
        if ($row[0] != "") {
           // pass on the sheet identifier
           formatedrecords($row[0]);
        }
     }
  } else {
   // Show summary results.
   //
   // Views of the data other than view==all can interate through the select_fields and select_field_labels
   // arrays.
   $nameoffoundstuff = "record";
   if ($first_col_is_count_search) {
      // number of records represents taxa+countries, not records
      $nameoffoundstuff = "result";
   }
   $found = "";
   if ($resultstart==0) {
      $result = pg_query($query);
      $numresults = pg_num_rows($result);
      $page = "".$_SERVER['REQUEST_URI'];
      echo "<a href='$page#primarycontent' accesskey='2' name=primarycontent>";
      echo "$numresults ".plural($nameoffoundstuff,$numresults)."</a> $foundonmap<BR>";
      if ($numresults==0) {
         echo "Trying soundex search<BR>";
         //echo "$soundex_query<BR>";
         $result = pg_query($soundex_query);
         $numresults = pg_num_rows($result);
         echo ("$numresults ".plural($nameoffoundstuff,$numresults)." $foundonmap<BR>");
         $query .= ";<BR><BR> $soundex_query";
         //$wherebit = "";
      }
      if ($numresults > $MAX_RESULTS) { $paginglinks.=("Displaying ".plural($nameoffoundstuff,2)." 1 to $MAX_RESULTS<BR>"); }
   }
   $nameoffoundstuff = plural($nameoffoundstuff,$numresults);

   // page result set if needed
   $paging = false;
   if ($numresults>0) {
      if ($numresults<=$MAX_RESULTS) {
          $resultstop = $numresults ;
       } else {
          // build list of paging links
          $paging = true;
          $paginglinks = "Page:";
          $pages = ceil($numresults/$MAX_RESULTS);
          $current = round($resultstart/$MAX_RESULTS) + 1;
          for($i=1;$i<$pages+1;$i++) {
               if ($i==1 || $pages < 11 || ($i<$current+8 && $i>$current-8) || $i==$pages) {
                  $pageoffset = $MAX_RESULTS * ($i - 1) ;
                  if ($i==$current) {
                    $paginglinks .= "&nbsp;<strong>$i</strong>&nbsp;";
                  } else {
                    $paginglinks .= "&nbsp;<a href='sheets.php?resultstart=$pageoffset&mode=search&numresults=$numresults&$getbit'>$i</a>&nbsp;";
                  }
               } else {
                  if ($i==2 || $i==$pages-1) {
                     $paginglinks .= "&nbsp;...&nbsp;";
                  }
               }
          }
          // work our range of query
          if ($resultstart==0) {
             $resultstop = $resultstart + $MAX_RESULTS;
          } else {
             $resultstop = $resultstart + $MAX_RESULTS-1;
          }
          if ($resultstop > $numresults) { $resultstop = $numresults; }

      }
      if ($resultstart==0) {
         $resultstart = 1;
      } else {
         // apply limit
         $oldquery = $query;
         $limitbit=" limit $resultstart, $MAX_RESULTS";
         $query = $query.$limitbit;
         $result = pg_query($query);
         $query = $oldquery;
         if (trim($foundonmap)=="found") { $foundonmap= "found."; }
         echo ("$numresults $nameoffoundstuff $foundonmap<BR>");
         $paginglinks = "Displaying $nameoffoundstuff $resultstart to $resultstop<BR>\n".$paginglinks;
      }
      if ($querybit !="") {
         $sql = "select count(*) from specimens left join annotation_table on specimens.specimen_id = annotation_table.specimen_id left join herbarium_sheet on specimens.herb_sheet_id = herbarium_sheet.herb_sheet_id where lat_decimal is not null and  $querybit ";
         $r2 = pg_query($sql);
         $rw2 = pg_fetch_array($r2);
         if ($rw2[0]>0) {
            if ($showmap) {
               echo "<a href='map_viewer.php?$getbit'><img src='map.php?$getbit' height=300 width=600 alt='Map of all georeferenced specimens in query'></a><BR><HR>";
           }
         }
      }
      if ($paginglinks != "") {
         echo "<div align=center>$paginglinks</div><BR>\n";
      }
      echo (" <TABLE><TR> ");
      if ($first_col_is_count_search) { echo ("<TD></TD>"); }
      for ($z=0; $z<=$tofield; $z++) {
          if ($zero_is_record_id && $z==0) {
             // do nothing
          } else {
             // show field names in top row of table
             echo("<TD><B>$selectfield_labels[$z]</B></TD>");
          }
      }
      echo("</TR>");         
      if ($zero_is_record_id) { $loopfrom = 1; } else { $loopfrom=0; }
      for ($x=$resultstart; $x<=$resultstop; $x++) {
         $failed = 0;
         $row = pg_fetch_array($result) or $failed=1;
         if ($failed==0) {
            echo("<TR><TD>\n"); // begin a table row
            // *** show a row of results ***
            if ($first_col_is_count_search) {
              $lots = plural("sheet",$row[$tofield]);
              if ($view=="taxa") {
                  echo "<a href='sheets.php?mode=search&view=all&genus=$row[0]&species=$row[1]&country=$row[2]&collnumber=$collnumber&state=$state&county=$county&locality=$locality&ph_number=$ph_number&date_collected=$date_collected'>$row[$tofield] $lots</a>&nbsp; ";
              }
              if ($view=="colls") {  
                 echo "<a href='sheets.php?mode=search&view=all&genus=$row[1]&species=$row[2]&country=$row[3]&collnumber=$row[0]&state=$state&county=$county&locality=$locality&ph_number=$ph_number&date_collected=$date_collected'>$row[$tofield] $lots</a>&nbsp; ";
              }
              echo "</td>\n";
            } else {
               if ($zero_is_record_id) {
                  echo("<a href='sheets.php?mode=details&herb_sheet_id=$row[0]'>PH $row[1]</a></TD>\n");
               } else {
                  //echo("<INPUT TYPE='submit' NAME='ph_number' VALUE='$row[0]'></TD>");
                  echo("<a href='sheets.php?mode=details&ph_number=$row[0]'>PH $row[0]</a></TD>\n");
               }
            }
            for ($y=$loopfrom; $y<=$tofield; $y++) {
               if ($zero_is_record_id && $y==1) {
                  // do nothing
               } else {
                  echo ("<TD>$row[$y]</TD>\n");
               }
            }
            echo("</TR>\n"); // end table row
         }
      }
      echo("</TABLE>\n");
   }
   if ($resultstop < $numresults) {
      $showrange = "Results $resultstart to $resultstop of $numresults shown <BR>";

      $nextset = $resultstop - $resultstart + 1;
      $resultstart = $resultstart + $MAX_RESULTS;
      if (($resultstart + $nextset)>$numresults) {
          $nextset = $numresults - $resultstart;
      }
      echo "<div align=center>$showrange$paginglinks<BR><a href='sheets.php?mode=search$requested_values&resultstart=$resultstart&numresults=$numresults&view=$view'>View next $nextset $nameoffoundstuff</a>&nbsp; </div>";
   }
  }
  display_query_sql($query);
}

echo "
<FORM  ACTION='sheets.php' METHOD='GET'>
<INPUT TYPE='hidden' NAME='mode' VALUE='main'>
<INPUT TYPE='submit' NAME='submit' VALUE='Another Search'>
</FORM>
";
}

function display_query_sql($sql) {
    $sql = str_replace(" where "," <BR>where ",$sql);
    $sql = str_replace(" group by "," <BR>group by ",$sql);
    $sql = str_replace(" order by "," <BR>order by ",$sql);
    echo("<div style='{ background: #DDFFDD; color: #0000FF; }' align=center><strong>Your search ran the following query:</strong><BR><table><tr><td><div align=left><div style='{ color: #0000FF; font-family: Courier, monoface; }'>$sql</div></td></tr></table></div></div><BR>");
}


// function formatedrecords
// displays full details of a single top level collection object (a herbarium sheet).
function formatedrecords($herb_sheet_id) {
   global $MAX_RESULTS;
   $ajax_call = "";
   $do_ajax = false;
   $connection = ansp_connect_wrapper("PH");
   if ($connection) {
        $sheet_stuff = "";
        $capture_metadata = "";
        // find out about the herbarium sheet
        $sql = "select sheet_barcode_number, currentcollection, verified,  data_capture_by_name, data_capture_on_date, verified_by, verification_date from herbarium_sheet where herb_sheet_id = $herb_sheet_id ";
        $result1 = pg_query($sql);
        $numresults = pg_num_rows($result1);
        for ($y=1; $y<=$numresults; $y++) {
          // fetch the herbarium sheet info
          $row1 = pg_fetch_object($result1);
          $sheet_barcode_number = $row1->sheet_barcode_number;
          $currentcollection = trim($row1->currentcollection);
          if ($currentcollection == "PH Type Collection") {
            $in_type_collection = true;
          } else {
            $in_type_collection = false;
          }
          
          $sheet_stuff = "<h1>$currentcollection</h1>";
          if ($sheet_barcode_number != "") {
             $sheet_stuff .= "<h2>Sheet Barcode Number: $sheet_barcode_number</h2>";
          }
          
          $verified = $row1->verified;
          $data_capture_by_name = $row1->data_capture_by_name;
          $data_capture_on_date = $row1->data_capture_on_date;
          $verified_by = $row1->verified_by;
          $verification_date = $row1->verification_date;
    
          $capture_metadata = "[Sheet data captured by $data_capture_by_name on $data_capture_on_date]<BR>";
          if ($verified=="t") {
             $capture_metadata .= "[Sheet data verified by $verified_by on $verification_date]<BR>";
          }

        }
        echo $sheet_stuff;

        // fetch images of the herbarium sheet
        $image0 = "";
        $images = "";
        // handle display of more than one image differently than if there is just one image
        $sql = "select imageid,  filename, image_of, caption, imaging_device, creator, date_imaged, title, subject, format, height, width, copyright from images where c_herbariumsheetid = $herb_sheet_id";
        $result1 = pg_query($sql);
        $numresults1 = pg_num_rows($result1);
        for ($y=1; $y<=$numresults1; $y++) {
          $im = "";
          // fetch the herbarium sheet info
          $row1 = pg_fetch_object($result1);
          $filename = trim($row1->filename);
          $caption = trim($row1->caption);
          $title = trim($row1->title);
          $height = trim($row1->height);
          $width = trim($row1->width);
          $imaging_device = trim($row1->imaging_device);
          $creator = trim($row1->creator);
          $date_imaged = trim($row1->date_imaged);
          $imageid = trim($row1->imageid);

          $im = "<table><tr><td><a href='image_viewer.php?image_id=$imageid'><img src='get_image.php?imageid=$imageid'></a><BR>$caption$title<BR>$imaging_device $creator $date_imaged<BR></td></tr></table>";
          if ($y==1) {
            $image0 = $im;
          } else {
            $images .= "[$im]";
          }
        }
        echo "<table><tr><td>";


        // find out about specimens on the herbarium sheet
        $sql = "select specimen_id, basionym, project, country, state_province, county_parish, lat_decimal, long_decimal, locality, habitat, coordinates_est, national_forest, latdeg, latmin, latsec, latns, longdeg, longmin, longsec, longew, taxon_notes, utm_zone, utm_e, utm_n, township, range, section, elev_est, elevation_m, elev_max_m, elevation_ft, elev_max_ft, locality, habitat, plant_description, assoc_species, specimen_notes, phenology, cultivated, hybrid, chromosome_number, first_collector_last, first_collector_init, collnumprefix, collnumber, full_coll_number, other_collectors, date_collected, month_collected, day_collected, year_collected, ex_herbarium, herb_acro, accession_no, basionym_ann_lastname, basionym_ann_initals, basionym_ann_institution, basionym_ann_year, basionym_ann_month, basionym_ann_day, exsiccati_title, fascicle_number, exsiccati_number, distributed_name, distributor, preparator, error_radius, verbatim_locality, source_of_coords, day_collected_end, month_collected_end, year_collected_end from specimens where herb_sheet_id = $herb_sheet_id";
        $result1 = pg_query($sql);
        $numresults1 = pg_num_rows($result1);
        for ($y=1; $y<=$numresults1; $y++) {
           if ($numresults1 > 1) { $spnum = "<h3>Specimen $y of $numresults1</h3>\n"; } else { $spnum=""; }
           $id = "";
           $loc = "";
           $phen = "";
           $ann = "";
           $pt = "";
           // fetch the herbarium sheet info
           $row1 = pg_fetch_object($result1);
           $specimen_id = $row1->specimen_id;
           $basionym = $row1->basionym;
           $project = $row1->project ;
           $country = $row1->country ;
           $state_province = $row1->state_province ;
           $county_parish = $row1->county_parish ;
           $locality = $row1->locality ;
           if ($in_type_collection===false && $locality != "" ) {
              $locality = "[Redacted for non-types]";
           } else {
              $tsr = "";
              $ll = "";
              $utm = "";
              $err = "";
              $lat_decimal = $row1->lat_decimal ;
              $long_decimal = $row1->long_decimal ;
              $latdeg = trim($row1->latdeg);
              $latmin = trim($row1->latmin);
              $latsec = trim($row1->latsec);
              $latns = trim($row1->latns);
              $longdeg = trim($row1->longdeg);
              $longmin = trim($row1->longmin);
              $longsec = trim($row1->longsec);
              $longew = trim($row1->longew);
              if ($latdeg != "") {
                 $ll .= "Latitude/Longitude: $latdeg&deg; $latmin' $latsec\" $latns $longdeg&deg; $longmin' $longsec\" $longew";
              }
              if ($lat_decimal != "") {
                 $ll .= "[$lat_decimal, $long_decimal]";
              }
              if ($ll != "") {
                 $ll .= "<BR>";
              }
              $utm_zone = trim($row1->utm_zone);
              $utm_e = trim($row1->utm_e);
              $utm_n = trim($row1->utm_n);
              if ($utm_zone != "") {
                $utm = "UTM:  $zone $utm_e $utm_n<BR>";
              }
              $township = trim($row1->township);
              $range = trim($row1->range);
              $section = trim($row1->section);
              if ($township !="") {
                 $tsr = "Township $township Range $range Section $section<BR>";
              }
              $coordinates_est = trim($row1->coordinates_est);
              $source_of_coords = trim($row1->source_of_coords);
              $error_radius = trim($row1->error_radius);
              if ($coordinates_est=="t") {
                 $coordinates_est = "Coordinates Estimated";
              } elseif ($coordinates_est == "f") {
                 $coordinates_est = "";
              }
              if ($coordinates_est=="t") { $coordinates_est = "Coordinates Estimated"; } else { $coordinates_est = ""; }
              if ($error_radius == "0") { $error_radius = ""; }
              $err = trim("$coordinates_est $error_radius $source_of_cooords");
              if ($err != "") {
                 $err .= "$coordinates_est $source_of_coords Error Radius (m): $error_radius<BR>";
              }
              $locality .= "$ll $utm $tsr $err";
              $verbatim_locality = trim($row1->verbatim_locality);
              if ($verbatim_locality!="") {
                 $locality .= "Verbatim Locality: $verbatim_locality<BR>";
              }
           }
           $habitat = $row1->habitat ;
           $taxon_notes = trim($row1->taxon_notes);
           $national_forest = trim($row1->national_forest);
           $elev_est = trim($row1->elev_est);
           $elevation_m = trim($row1->elevation_m);
           $elev_max_m = trim($row1->elev_max_m);
           $elevation_ft = trim($row1->elevation_ft);
           $elev_max_ft = trim($row1->elev_max_ft);
           $el = trim($elevation_m.$elevation_ft);
           if ($el != "") { $locality .= "Elevation:"; }
           if ($elevation_m != "") {
              if ($elev_max_m != "") { $elev_max_m = " to $elev_max_m m "; }
              $locality .= "$elevation_m m $elev_max_m";
           }
           if ($elevation_ft != "") {
              if ($elev_max_ft != "") { $elev_max_ft = " to $elev_max_ft feet "; }
              $locality .= "$elevation_ft feet $elev_max_ft";
           }
           if ($el != "") { $locality .= "<BR>\n"; }
           $phenology = trim($row1->phenology);
           $plant_description = trim($row1->plant_description);
           $assoc_species = trim($row1->assoc_species);
           $specimen_notes = trim($row1->specimen_notes);
           $cultivated = trim($row1->cultivated);
           if ($cultivated == "f") { $cultivated = ""; } elseif ($cultivated == "t") { $cultivated = "Yes"; }
           $hybrid = trim($row1->hybrid);
           if ($hybrid == "f") { $hybrid = ""; } elseif ($hybrid == "t") { $hybrid = "Yes"; }
           $chromosome_number = trim($row1->chromosome_number);
           if ($plant_description != "") { $pt .= "Description: $plant_description<BR>"; }
           if ($assoc_species != "") { $pt .= "Associated Species: $assoc_species<BR>"; }
           if ($specimen_notes != "") { $pt .= "Notes: $specimen_notes<BR>"; }
           if ($cultivated != "") { $pt .= "Cultivated: $cultivated<BR>"; }
           if ($hybrid != "") { $pt .= "Hybrid: $hybrid<BR>"; }
           if ($chromosome_number != "") { $pt .= "Chromosome Number: $chromosome_number<BR>"; }
           $first_collector_last = trim($row1->first_collector_last);
           $first_collector_init = trim($row1->first_collector_init);
           $full_coll_number = trim($row1->full_coll_number);
           $other_collectors = trim($row1->other_collectors);
           $col = trim("$first_collector_last $first_collector_init $other_collectors $full_coll_number");
           if ($col != "") {
               if ($other_collectors != "") {  
                  $col = "Collectors: $first_collector_last $first_collector_init, $other_collectors<BR>";
               } else {
                  $col = "Collector: $first_collector_last $first_collector_init<BR>";
               }
           }
           if ($full_coll_number == "0") { $full_coll_number = ""; }  
           if ($full_coll_number != "") {
               $col .= "Collector's Number: $full_coll_number<BR>";
           }
           $date_collected = trim($row1->date_collected);
           $month_collected = trim($row1->month_collected);
           $day_collected = trim($row1->day_collected);
           $year_collected = trim($row1->year_collected);
           $month_collected_end = trim($row1->month_collected_end);
           $day_collected_end = trim($row1->day_collected_end);
           $year_collected_end = trim($row1->year_collected_end);
           if ($year_collected == "0") { $year_collected = ""; }   
           if ($month_collected == "0") { $month_collected = ""; }   
           if ($day_collected == "0") { $day_collected = ""; }   
           if ($year_collected_end == "0") { $year_collected_end = ""; }   
           if ($month_collected_end == "0") { $month_collected_end = ""; }   
           if ($day_collected_end == "0") { $day_collected_end = ""; }   
           $dc = trim($year_collected.$month_collected.$day_collected.$year_collected_end.$month_collected_end.$day_collected_end);
           if ($dc!="") {
               $col .= "Date Collected: $year_collected $month_collected $day_collected";
               if (trim($year_collected_end.$month_collected_end.$day_collected_end)!="") {
                  $col .= "to $year_collected $month_collected $day_collected";
               }
               $col .= "<BR>\n";
           }
           $ex_herbarium = trim($row1->ex_herbarium);
           $herb_acro = trim($row1->herb_acro);
           $accession_no = trim($row1->accession_no);
           $num = "";
           if ($accession_no != "") { $num = "<h2>Accession Number: PH $accession_no</h2>\n"; }
           if (trim($ex_herbarium.$herb_acro) != "") { $num .= "Ex Herbarium: $herb_acro $ex_herbarium<BR>\n"; }
           $basionym_ann_lastname = trim($row1->basionym_ann_lastname);
           $basionym_ann_initals = trim($row1->basionym_ann_initals);
           $basionym_ann_institution = trim($row1->basionym_ann_institution);
           $basionym_ann_year = trim($row1->basionym_ann_year);
           $basionym_ann_month = trim($row1->basionym_ann_month);
           $basionym_ann_day = trim($row1->basionym_ann_day);
           $exsiccati_title = trim($row1->exsiccati_title);
           $fascicle_number = trim($row1->fascicle_number);
           $exsiccati_number = trim($row1->exsiccati_number);
           $distributed_name = trim($row1->distributed_name);
           $distributor = trim($row1->distributor);
           $exs = trim("$exsiccati_title $exsicatti_number $fascicle_number $distributed_name $distributor");
           $preparator = trim($row1->preparator);

           if ($basionym != "") {
              $id .= "Original Identification: $basionym<BR>";
              if (trim($basionym_ann_last_name.$basionym_ann_initials)=="") { $basionym_ann_lastname = "[Unknown]"; }
              if (trim($basionym_ann_institution)=="") {
                   $basionym_ann_institution= "";
              } else {
                   $basionym_ann_institution= "($basionym_ann_institution)";
              }
              if ($basionym_ann_year=="0") { $basionym_ann_year=""; }
              if ($basionym_ann_month=="0") { $basionym_ann_month=""; }
              if ($basionym_ann_day=="0") {