<?php
/*
JSON web service for Plant Onpotology terms

Required parameters:
    request_type
    max
Optional parameters:
    app_key (for access to restricted methods)
    debug (for debugging, only available to owner app_key)
    format (JSON only for now)

Public Request Types:
    term_search(req str search_value, opt bool inc_synonyms, 
        opt bool inc_accession_synonyms, opt str branch_filter)
    term_detail(req str accession_id)

TODO: Restricted Request Types (requires user key):
    wiki_autocomplete(req str search_value)
    wiki_term_detail(req str accession_id || req str term_name)
*/

/* read the params and/or set our own */
$request_type = isset($_GET['request_type'])
                    ? $_GET['request_type'] 
                    : die('Please provide a request type.');

$number_of_terms = isset($_GET['max']) ? intval($_GET['max']) : 10; // default
if ($number_of_terms > 50) { $number_of_terms = 50; } // ceiling

/*
appliction authorization key; to be implemented later;
for now it is only used to provide the debugging option to the application owner 
*/
$app_key = isset($_GET['app_key']) ? $_GET['app_key'] : "";

$debug = isset($_GET['debug']) ? 1 : 0;

$format = isset($_GET['format']) && strtolower($_GET['format']) != 'json' 
    ? strtolower($_GET['format']) 
    : 'json'; //json is the default

// includes synonyms in term search results
$inc_synonyms = isset($_GET['inc_synonyms']) ? 1 : 0;

// includes accession ids listed as synonyms, in both term_search and
// term_detail methods
$inc_accession_synonyms = isset($_GET['inc_accession_synonyms']) ? 1 : 0; 

// filters out results not matching the comma-delimited list of branches
// specified here:
$branch_filter = isset($_GET['branch_filter']) && strlen($_GET['branch_filter']) > 0
                    ? "'" . strtolower($_GET['branch_filter']) . "'"
                    : "'plant_anatomy','plant_structure_development_stage'";

// optionally group alpha-sorted results by branch (term.term_type)
$group_branches = isset($_GET['group_branches']) ? 1 : 0;

// only return the exact match
$exact_match = isset($_GET['exact_match']) ? 1 : 0;

// place any exact match at the top
$prioritize_exact_match = isset($_GET['prioritize_exact_match']) ? 1 : 0;

/* connect to the db */
$link = mysql_connect($_SERVER['po_host'], $_SERVER['po_user'], 
    $_SERVER['po_pw']) or die(mysql_error() . 'Cannot connect to the DB');
mysql_select_db($_SERVER['po_db'],$link) or die('Cannot select the DB');

$query = ""; 

switch ($request_type) {

    case 'term_search':
        $search_value = $_GET['search_value'];
        $search_value = isset($_GET['search_value']) && strlen($_GET['search_value']) > 0 
            ? strtolower($_GET['search_value'])
            : die('Please provide the "search_value" parameter.');

        /*
        just a touch of SQL injection attack prevention; our db user is 
        read-only, so injected SELECT statements are the only concern
        */
        if (preg_match("/(SELECT)/", strtoupper($search_value))) {
            die('Attempted injection attack denied.');
        }

        // return only exact matches to a term (or synonym, if requested)
        if ($exact_match) {
            $query .= "(SELECT t.name, 'term' as match_type, t.acc, t.term_type as 'type', null as parent_name"
                . " FROM term t"
                . " LEFT JOIN term_definition d ON d.term_id = t.id"
                . " WHERE t.name = '$search_value'"
                . " AND t.term_type in (" . $branch_filter . ")"
                . " AND t.is_obsolete = 0"
                . " AND UCASE(t.name) NOT LIKE 'OBSOLETE%'"
                . " AND UCASE(d.term_definition) NOT LIKE 'OBSOLETE%')";
            if ($inc_synonyms) {
                $query .= " UNION"
                    . " (SELECT ts.term_synonym as name, 'synonym' as match_type, t2.acc, t2.term_type as 'type', t2.name as parent_name"
                    . " FROM term_synonym ts"
                    . " LEFT JOIN term t2 ON ts.term_id = t2.id"
                    . " LEFT JOIN term_definition d2 ON d2.term_id = t2.id"
                    . " WHERE ts.term_synonym = '$search_value'";
                if (!$inc_accession_synonyms) {
                    $query .= " AND ts.acc_synonym IS NULL";
                }
                $query .= " AND t2.term_type in (" . $branch_filter . ")"
                    . " AND t2.is_obsolete = 0"
                    . " AND UCASE(t2.name) NOT LIKE 'OBSOLETE%'"
                    . " AND UCASE(d2.term_definition) NOT LIKE 'OBSOLETE%')";
            }
            $query .= " ORDER BY name";    
        }
        else {
            // place any exact match at the top
            if ($prioritize_exact_match) {
                $query .= "(SELECT 'exact_match' as sort_col, t.name, 'term' as match_type, t.acc, t.term_type as 'type', null as parent_name"
                    . " FROM term t"
                    . " LEFT JOIN term_definition d ON d.term_id = t.id"
                    . " WHERE t.name = '$search_value'"
                    . " AND t.term_type in (" . $branch_filter . ")"
                    . " AND t.is_obsolete = 0"
                    . " AND UCASE(t.name) NOT LIKE 'OBSOLETE%'"
                    . " AND UCASE(d.term_definition) NOT LIKE 'OBSOLETE%')";
                if ($inc_synonyms) {
                    $query .= " UNION"
                        . " (SELECT 'exact_match' as sort_col, ts.term_synonym as name, 'synonym' as match_type, t2.acc, t2.term_type as 'type', t2.name as parent_name"
                        . " FROM term_synonym ts"
                        . " LEFT JOIN term t2 ON ts.term_id = t2.id"
                        . " LEFT JOIN term_definition d2 ON d2.term_id = t2.id"
                        . " WHERE ts.term_synonym = '$search_value'";
                    if (!$inc_accession_synonyms) {
                        $query .= " AND ts.acc_synonym IS NULL";
                    }
                    $query .= " AND t2.term_type in (" . $branch_filter . ")"
                        . " AND t2.is_obsolete = 0"
                        . " AND UCASE(t2.name) NOT LIKE 'OBSOLETE%'"
                        . " AND UCASE(d2.term_definition) NOT LIKE 'OBSOLETE%')";
                }
                $query .= " UNION";
            }
            // grab the terms from the db
                $query .= " (SELECT 'search_results' as sort_col, t.name, 'term' as match_type, t.acc, t.term_type as 'type', null as parent_name"
                . " FROM term t"
                . " LEFT JOIN term_definition d ON d.term_id = t.id"
                . " WHERE t.name LIKE '%$search_value%'"
                . ($prioritize_exact_match ? " AND t.name <> '$search_value'" : "") // exclude the earlier exact matches
                . " AND t.term_type in (" . $branch_filter . ")"
                . " AND t.is_obsolete = 0"
                . " AND UCASE(t.name) NOT LIKE 'OBSOLETE%'"
                . " AND UCASE(d.term_definition) NOT LIKE 'OBSOLETE%'"
                . " GROUP BY t.id"  // GROUP BY necessary?
                . ")";
            if ($inc_synonyms) {
                $query .= " UNION"
                    . " (SELECT 'search_results' as sort_col, ts.term_synonym as name, 'synonym' as match_type, t2.acc, t2.term_type as 'type', t2.name as parent_name"
                    . " FROM term_synonym ts"
                    . " LEFT JOIN term t2 ON ts.term_id = t2.id"
                    . " LEFT JOIN term_definition d2 ON d2.term_id = t2.id"
                    . " WHERE ts.term_synonym LIKE '%$search_value%'"
                    . ($prioritize_exact_match ? " AND ts.term_synonym <> '$search_value'" : ""); // exclude the earlier exact matches
                if (!$inc_accession_synonyms) {
                    $query .= " AND ts.acc_synonym IS NULL";
                }
                $query .= " AND t2.term_type in (" . $branch_filter . ")"
                    . " AND t2.is_obsolete = 0"
                    . " AND UCASE(t2.name) NOT LIKE 'OBSOLETE%'"
                    . " AND UCASE(d2.term_definition) NOT LIKE 'OBSOLETE%'"
                    . ")";
            }
            $query .= ($group_branches ? " ORDER BY sort_col, type, name" : " ORDER BY sort_col, name");
            $query .= " LIMIT $number_of_terms";
        }
        
        $result = mysql_query($query,$link) or die('Errant query:  '.$query);
    
        /* create one master array of the records */
        $term_matches = array();
        if(mysql_num_rows($result)) {
            while($term = mysql_fetch_assoc($result)) {
                $ary_match = array(
                    'match'=>$term['name'],
                    'match_type'=>$term['match_type'],
                    'accession_id'=>$term['acc'],
                    'aspect'=>$term['type'] == "plant_anatomy" ? "Plant Anatomy" : "Plant Structure Development Stage");
                if ($term['match_type'] == 'synonym') {
                    $ary_match['is_synonym_of'] = $term['parent_name'];
                }
                $term_matches[] = $ary_match;
          }
        }

        if ($app_key == $_SERVER['owner_app_key'] && $debug) {
            $ary_debug = array(
                'query'=>$query,
                'num_results'=>count($term_matches));
            array_unshift($term_matches, $ary_debug); 
        }
        

        /* output in necessary format */
        if($format == 'json') {
            header('Content-type: application/json');
            echo json_encode(array('PO_term_search_response'=>$term_matches));
        }
        else {
            die('Sorry, this request cannot be fulfilled in '.$format.' format.');
        }
        break;

    case 'term_detail':
        $accession_id = $_GET['accession_id'];
        $accession_id = isset($_GET['accession_id']) && strlen($_GET['accession_id']) > 0 
            ? $_GET['accession_id']
            : die('Please provide the "accession_id" parameter.');

        /* grab the ontology data from the db */
        $query .= "SELECT DISTINCT t.name, t.acc as 'acc', t.term_type as 'type'," 
            . " d.term_definition as 'definition', d.term_comment as 'comment',"
            . " group_concat(s.term_synonym separator ', ') as synonyms"
            . " FROM term t"
            . " LEFT JOIN term_definition d ON d.term_id = t.id"
            . " LEFT JOIN term_synonym s ON s.term_id = t.id";
            if (!$inc_accession_synonyms) {
                $query .= " AND s.acc_synonym IS NULL";
            }
            $query .= " WHERE t.acc = '$accession_id'"
            . " AND t.term_type in (" . $branch_filter . ")"
            . " AND t.is_obsolete = 0"
            . " AND UCASE(t.name) NOT LIKE 'OBSOLETE%'"
            . " AND UCASE(d.term_definition) NOT LIKE 'OBSOLETE%'"
            //. " GROUP BY t.id" // necessary?
            . " ORDER BY t.name LIMIT 1";
        $result = mysql_query($query,$link) or die('Errant query:  '.$query);
    
        /* create one master array of the records */
        $term_details = array();
        if(mysql_num_rows($result)) {
            while($term = mysql_fetch_assoc($result)) {
                $term_details[] = array(
                    'accession_id'=>$term['acc'],
                    'name'=>$term['name'],
                    'aspect'=>$term['type'] == "plant_anatomy" ? "Plant Anatomy" : "Plant Structure Development Stage",
                    'definition'=>$term['definition'],
                    'comment'=>$term['comment'],
                    'synonyms'=>preg_split("/, /",$term['synonyms'])
                    );
            }
        }
        /* output in necessary format */
        if($format == 'json') {
            header('Content-type: application/json');
            echo json_encode(array('PO_term_detail_response'=>$term_details));
        }
        else {
            die('Sorry, this request cannot be fulfilled in '.$format.' format.');
        }
        break;
    default:
        die('Sorry, this web service method is not available.');
}
/* disconnect from the db */
@mysql_close($link);
?>