1: <?php
  2: 
  3: namespace webservices\details\annotations\feature;
  4: 
  5: use \PDO as PDO;
  6: /**
  7:  * Web Service. Returns MapMan Feature Annotation
  8:  */
  9: class Mapman extends \WebService {
 10: 
 11:     public function getById($param_isoform_id) {
 12: 
 13: 
 14:         global $db;
 15: #UI hint
 16:         if (false)
 17:             $db = new PDO();
 18: 
 19:         $const = 'constant';
 20: 
 21:         /**
 22:          * select all MapMan subFeatures along with all their dbxref annotation ids
 23:          * due to import, annotation_feature_id and dbxref_id are 1-1 relations
 24:          */
 25:         $query_get_mapman = <<<EOF
 26: SELECT 
 27:     f.feature_id AS annotation_feature_id,
 28:     fp.value AS feature_annotation,
 29:         'MapMan' AS db_name,
 30:         fdb.dbxref_id AS dbxref_id
 31: FROM 
 32:     feature f
 33:     JOIN featureprop fp ON (f.feature_id = fp.feature_id)
 34:     JOIN feature_dbxref fdb ON (f.feature_id = fdb.feature_id)
 35: WHERE   
 36:     f.feature_id IN (SELECT subject_id  FROM feature_relationship WHERE object_id = :isoform_id)
 37:         AND f.type_id={$const('CV_ANNOTATION_MAPMAN_FEATURE')}
 38: 
 39: EOF;
 40: 
 41:         //select details (dbxref->cvterm->cvtermprop) for all given dbxref_ids
 42:         $query_get_dbxrefs = <<<EOF
 43: SELECT   
 44:         c.dbxref_id,
 45:         c.name AS bin_accession,
 46:     c.definition AS bin_definition,
 47:     split_part(cp.value, '\t', 1) AS bin_annot_chem,
 48:     split_part(cp.value, '\t', 2) AS bin_annot_definition
 49: FROM 
 50:     cvterm c
 51:     LEFT JOIN cvtermprop cp ON (c.cvterm_id = cp.cvterm_id AND cp.type_id={$const('CV_ANNOTATION_MAPMAN_PROP')})        
 52: WHERE 
 53:         c.dbxref_id = any(ARRAY[%s])
 54: EOF;
 55: 
 56:         $stm_get_mapman_hits = $db->prepare($query_get_mapman);
 57:         $stm_get_mapman_hits->bindParam('isoform_id', $param_isoform_id);
 58: 
 59:         //array with return value
 60:         $ret = array();
 61:         //array to store bins
 62:         $bins = array();
 63: 
 64:         $stm_get_mapman_hits->execute();
 65:         while ($row = $stm_get_mapman_hits->fetch(PDO::FETCH_ASSOC)) {
 66:             //add empty entry for dbxref_id to $bins
 67:             $bins[$row['dbxref_id']] = array();
 68:             //add annotation and dbxref_id to $ret[annotation_feature_id]
 69:             $ret[$row['annotation_feature_id']]= array(
 70:                     'annotation' => $row['feature_annotation'],
 71:                     'dbxref' => $row['dbxref_id']
 72:                 );
 73:         }
 74: 
 75:         //get all dbxref ids
 76:         if (count($bins) > 0) {
 77:             //keys to look for
 78:             $lookfor = array_keys($bins);
 79:             //build a string like ? ::int,? ::int,? ::int
 80:             $place_holders = implode(',', array_fill(0, count($lookfor), '? ::int'));
 81:             //insert this string in $query_get_dbxrefs and build a statement around it
 82:             $stm_get_dbxrefs = $db->prepare(sprintf($query_get_dbxrefs, $place_holders));
 83:             //insert with $lookfor
 84:             $stm_get_dbxrefs->execute($lookfor);
 85:             while ($row = $stm_get_dbxrefs->fetch(PDO::FETCH_ASSOC)) {
 86:                 //shorthand
 87:                 $ref = &$bins[$row['dbxref_id']];
 88:                 //set data
 89:                 $ref['bin_accession'] = $row['bin_accession'];
 90:                 $ref['bin_definition'] = $row['bin_definition'];
 91:                 if ($row['bin_annot_chem'] != null) {
 92:                     $ref['bin_annotations'][] = array(
 93:                        'bin_annot_chem' => $row['bin_annot_chem'],
 94:                        'bin_annot_definition' => $row['bin_annot_definition']
 95:                     );
 96:                 }
 97:             }
 98:             //apply all those $bins to their respective $val
 99:             foreach ($ret as &$val){
100:                 $val = array_merge($val, $bins[$val['dbxref']]);
101:             } 
102:         }
103:         return $ret;
104:     }
105: 
106:     /**
107:      * @inheritDoc
108:      */
109:     public function execute($querydata) {
110: 
111:         return $this->getById($querydata['query1']);
112:     }
113: 
114: }
115: 
116: ?>
117: