<?php
// JSON web service for PO terms: autocomplete and search methods

/* require the user as the parameter */
if(isset($_GET['user']) && ($_GET['user']) == 'paw') {

        $arr_field_names = array('name','acc');
        $arr_ontologies = array('go','po','to');
        
        /* read the params and/or set our own */
        $type = isset($_GET['type']) ? $_GET['type'] : autocomplete; // autocomplete is the default
        
        // security measures; helps to avoid SQL injection attacks
        $field = isset($_GET['field']) && in_array($_GET['field'],$arr_field_names)
            ? $_GET['field']
            : die('"field" is a required parameter and must match an available data field.');
            
        $number_of_terms = isset($_GET['max']) ? intval($_GET['max']) : 10; //10 is the default
        if ($number_of_terms > 50) { $number_of_terms = 50; } 

        $ont = isset($_GET['ontology']) && in_array(strtolower($_GET['ontology']),$arr_ontologies)
            ? strtolower($_GET['ontology'])
            : die('"ontology" is a required parameter and must match an available data field.');
        
        $qval = $_GET['qval'];
        
        $qval = isset($_GET['qval']) && strlen($_GET['qval']) > 0 
            ? strtolower($_GET['qval'])
            : die('Please provide a searchable value');

        $format = strtolower($_GET['format']) != 'json' 
            ? strtolower($_GET['format']) 
            : 'json'; //json is the default
        
        /* connect to the appropriate db */
        switch ($ont) {
            case 'po':
                $link = mysql_connect($_SERVER['po_host'], $_SERVER['po_user'], $_SERVER['po_pw']) or die('Cannot connect to the DB');
                mysql_select_db($_SERVER['po_db'],$link) or die('Cannot select the DB');

                $term_types = "'plant_anatomy','plant_structure_development_stage'"; 
                break;

            case 'go':
                $link = mysql_connect($_SERVER['go_host'], $_SERVER['go_user'], $_SERVER['go_pw']) or die('Cannot connect to the DB');
                mysql_select_db($_SERVER['go_db'],$link) or die('Cannot select the DB');

                $term_types = "'biological_process','cellular_component','molecular_function'"; 
                break;

            case 'to':
                $link = mysql_connect($_SERVER['to_host'], $_SERVER['to_user'], $_SERVER['to_pw']) or die('Cannot connect to the DB');
                mysql_select_db($_SERVER['to_db'],$link) or die('Cannot select the DB');

                $term_types = "'plant_trait_ontology'"; 
                break;

            default:
                die('Sorry, this ontology type is not available.');
        }
        
        switch ($type) {
            case 'autocomplete':
                /* grab the terms from the db */
                $query = "SELECT t.$field FROM term t"
                    . " LEFT JOIN term_definition d ON d.term_id = t.id"
                    . " WHERE t.$field LIKE '%$qval%'"
                    . " AND t.term_type in ($term_types)"
                    . " AND t.is_obsolete = 0"
                    . " AND UCASE(t.name) NOT LIKE 'OBSOLETE%'"
                    . " AND UCASE(d.term_definition) NOT LIKE 'OBSOLETE%'"
                    . " ORDER BY name LIMIT $number_of_terms";
                $result = mysql_query($query,$link) or die('Errant query:  '.$query);
            
                /* create one master array of the records */
                $terms = array();
                if(mysql_num_rows($result)) {
                    while($term = mysql_fetch_assoc($result)) {
                        $terms[] = array('title'=>$term[$field]);
                    }
                }

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

            case 'term_detail':
                /* grab the ontology data from the db */
                $query = "SELECT DISTINCT t.name as 'name', t.acc as 'acc', t.term_type as 'type', d.term_definition as 'definition', d.term_comment as 'comment'"
                    . " FROM term t"
                    . " LEFT JOIN term_definition d ON d.term_id = t.id"
                    . " WHERE t.$field = '$qval'"
                    . " AND t.term_type in ($term_types)"
                    . " AND t.is_obsolete = 0"
                    . " AND UCASE(t.name) NOT LIKE 'OBSOLETE%'"
                    . " AND UCASE(d.term_definition) NOT LIKE 'OBSOLETE%'"
                    . " ORDER BY t.name LIMIT 1";
                $result = mysql_query($query,$link) or die('Errant query:  '.$query);
            
                /* create one master array of the records */
                $terms = array();
                if(mysql_num_rows($result)) {
                    while($term = mysql_fetch_assoc($result)) {
                        switch($term['type']) {
                            case 'plant_anatomy':
                                $term_type_formal = "Plant Anatomy"; break;
                            case 'plant_structure_development_stage':
                                $term_type_formal = "Plant Structure Development Stage"; break;
                            case 'biological_process':
                                $term_type_formal = "Biological Process"; break;
                            case 'cellular_component':
                                $term_type_formal = "Cellular Component"; break;
                            case 'molecular_function':
                                $term_type_formal = "Molecular Function"; break;
                            case 'plant_trait_ontology':
                                $term_type_formal = "Plant Trait Ontology"; break;
                        }
                        $terms[] = array(
                            'name'=>$term['name'],
                            'id'=>$term['acc'],
                            'aspect'=>$term_type_formal,
                            'definition'=>$term['definition'],
                            'comment'=>$term['comment']);
                    }
                }
                /* output in necessary format */
                if($format == 'json') {
                    header('Content-type: application/json');
                    echo json_encode(array('term_detail_result'=>$terms));
                }
                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);
}
else { die('Not authorized.'); }
?>