1: <?php
  2: 
  3: namespace webservices\listing;
  4: 
  5: use \PDO as PDO;
  6: /**
  7:  * Web Service.
  8:  * Returns Differential Expressions for dataTable Server-Side processing.
  9:  * See http://www.datatables.net/release-datatables/examples/server_side/server_side.html
 10:  */
 11: class Differential_expressions extends \WebService {
 12:     /**
 13:      * mapping for dataTable columns to database columns.
 14:      * only these are allowed for filtering
 15:      * @var Array 
 16:      */
 17:     public static $columns = array(
 18:         'f.name' => '"feature_name"',
 19:         'd.baseMean' => '"baseMean"',
 20:         'd.baseMeanA' => '"baseMeanA"',
 21:         'd.baseMeanB' => '"baseMeanB"',
 22:         'd.foldChange' => '"foldChange"',
 23:         'd.log2foldChange' => '"log2foldChange"',
 24:         'd.pval' => 'pval',
 25:         'd.pvaladj' => 'pvaladj',
 26:         "f.feature_id" => 'feature_id'
 27:     );
 28: 
 29:     /**
 30:      * get query details for Results overview/csv header
 31:      * @global \PDO $db
 32:      * @param Array $querydata
 33:      * @param boolean $apply_filters
 34:      * @return Array
 35:      */
 36:     public function fullRelease_getQueryDetails($querydata, $apply_filters = false) {
 37:         $ret = array(
 38:             'organism' => '',
 39:             'release' => '',
 40:             'conditionA' => '',
 41:             'conditionB' => '',
 42:             'analysis' => '',
 43:             'filters' => array()
 44:         );
 45: 
 46:         global $db;
 47:         $query_biomat = $db->prepare('SELECT biomaterial_id AS id, name, description FROM biomaterial WHERE biomaterial_id=?');
 48:         $query_biomat->execute(array($querydata['conditionA']));
 49:         $ret['conditionA'] = $query_biomat->fetch(\PDO::FETCH_ASSOC);
 50: 
 51:         $query_biomat->execute(array($querydata['conditionB']));
 52:         $ret['conditionB'] = $query_biomat->fetch(\PDO::FETCH_ASSOC);
 53: 
 54:         $query_analysis = $db->prepare('SELECT analysis_id AS id, name, description, program, programversion, algorithm FROM analysis WHERE analysis_id=?');
 55:         $query_analysis->execute(array($querydata['analysis']));
 56:         $ret['analysis'] = $query_analysis->fetch(\PDO::FETCH_ASSOC);
 57: 
 58:         $query_organism = $db->prepare('SELECT organism_id AS id, common_name AS name FROM organism WHERE organism_id=?');
 59:         $query_organism->execute(array($querydata['organism']));
 60:         $ret['organism'] = $query_organism->fetch(\PDO::FETCH_ASSOC);
 61: 
 62:         $ret['release'] = $querydata['release'];
 63: 
 64:         if ($apply_filters) {
 65:             $where = array();
 66:             $arguments = array();
 67:             $this->get_filters($querydata, $where, $arguments, array_values(self::$columns));
 68: 
 69:             for ($i = 0; $i < count($where); $i++) {
 70:                 array_push($ret['filters'], str_replace('"', '', str_replace('?', $arguments[$i], $where[$i])));
 71:             }
 72:         }
 73:         
 74:         if (isset($querydata['ids']) && count($querydata['ids']) > 0) {
 75:             array_push($ret['filters'], 'feature_id in ('.implode(';', $querydata['ids']).')');
 76:         }
 77: 
 78:         return $ret;
 79:     }
 80: 
 81:     /**
 82:      * Evaluates $querydata and stores filter expressions for SQL WHERE in $where, values in $arguments
 83:      * @param Array $querydata
 84:      * @param outArray &$where
 85:      * @param outArray &$arguments
 86:      * @param Array $keys array_keys(self::$columns)
 87:      */
 88:     public function get_filters($querydata, &$where, &$arguments, $keys) {
 89:         foreach ($querydata['filter_column'] as $key => $filter_column) {
 90:             $type = $filter_column['type'];
 91:             $value = str_replace('Inf', 'Infinity', $filter_column['value']);
 92:             if ($value === "")
 93:                 continue;
 94:             if (!in_array($type, array('lt', 'gt', 'eq', 'geq', 'leq')))
 95:                 continue;
 96:             if (!is_numeric($value) && $value != 'Infinity' && $value != '-Infinity')
 97:                 continue;
 98:             if ($key > count(self::$columns))
 99:                 continue;
100:             switch ($type) {
101:                 case 'eq':
102:                     array_push($where, sprintf('%s = ?', $keys[$key]));
103:                     break;
104:                 case 'gt':
105:                     array_push($where, sprintf('%s > ?', $keys[$key]));
106:                     break;
107:                 case 'lt':
108:                     array_push($where, sprintf('%s < ?', $keys[$key]));
109:                     break;
110:                 case 'geq':
111:                     array_push($where, sprintf('%s >= ?', $keys[$key]));
112:                     break;
113:                 case 'leq':
114:                     array_push($where, sprintf('%s <= ?', $keys[$key]));
115:                     break;
116:             }
117:             array_push($arguments, $value);
118:         }
119:     }
120: 
121:     /**
122:      * Builds full SQL query with $querydata applied
123:      * @param Array $querydata
124:      * @param boolean $apply_filters
125:      * @param boolean $apply_order
126:      * @param boolean $apply_limit
127:      * @return list($query, $arguments)
128:      */
129:     public function fullRelease_buildQuery($querydata, $apply_filters = false, $apply_order = false, $apply_limit = false) {
130:         $keys = array_keys(self::$columns);
131:         $arguments = array();
132: 
133:         $select = implode(",\n", array_map(function($key, $value) {
134:                             return sprintf("%s AS %s", $key, $value);
135:                         }, $keys, self::$columns));
136: 
137:         if ($apply_limit)
138:             $select.=', COUNT(*) OVER () AS cnt';
139: 
140:         $where = array();
141: 
142:         array_push($where, 'd.analysis_id = ?');
143:         array_push($arguments, $querydata['analysis']);
144: 
145:         array_push($where, 'd.biomateriala_id = ?');
146:         array_push($arguments, $querydata['conditionA']);
147: 
148:         array_push($where, 'd.biomaterialb_id = ?');
149:         array_push($arguments, $querydata['conditionB']);
150: 
151:         array_push($where, 'f.organism_id = ?');
152:         array_push($arguments, $querydata['organism']);
153: 
154:         array_push($where, 'f.dbxref_id=(SELECT dbxref_id FROM dbxref WHERE db_id = ' . DB_ID_IMPORTS . ' AND accession = ?)');
155:         array_push($arguments, $querydata['release']);
156: 
157:         if (isset($querydata['ids']) && count($querydata['ids']) > 0) {
158:             array_push($where, 'd.feature_id IN (' . implode(',', array_fill(0, count($querydata['ids']), '?')) . ')');
159:             foreach ($querydata['ids'] as $id){
160:                 array_push($arguments, $id);
161:             }
162:         }
163: 
164:         if ($apply_filters) {
165:             $this->get_filters($querydata, $where, $arguments, $keys);
166:         }
167:         $wherestr = implode(" AND \n", $where);
168: 
169: 
170:         $order_by = 'ORDER BY ' . self::$columns[$keys[0]] . ' DESC';
171:         if ($apply_order && isset($querydata['iSortCol_0'])) {
172:             for ($i = 0; $i < intval($querydata['iSortingCols']); $i++) {
173:                 if ($querydata['bSortable_' . intval($querydata['iSortCol_' . $i])] == "true") {
174:                     $order_by = 'ORDER BY ' . self::$columns[$keys[intval($querydata['iSortCol_' . $i])]] . ' ' . ($querydata['sSortDir_' . $i] === 'asc' ? 'ASC' : 'DESC');
175:                 }
176:             }
177:         }
178:         $limit = '';
179:         if ($apply_limit) {
180:             $limit_from = intval($querydata['iDisplayStart']);
181:             $limit_count = max(array(10, min(array(1000, intval($querydata['iDisplayLength'])))));
182:             $limit = sprintf('OFFSET %d LIMIT %d', $limit_from, $limit_count);
183:         }
184: 
185:         $query = <<<EOF
186: SELECT 
187: $select
188: FROM 
189:     diffexpresult d 
190:     join feature f on d.feature_id=f.feature_id 
191: WHERE
192: $wherestr
193: $order_by
194: $limit
195: EOF;
196: 
197:         return array($query, $arguments);
198:     }
199: 
200:     /**
201:      * returns data in format for dataTable
202:      * @global \PDO $db
203:      * @param Array $querydata
204:      * @return Array
205:      */
206:     public function fullRelease($querydata) {
207:         global $db;
208: 
209: #UI hint
210:         if (false)
211:             $db = new PDO();
212: 
213:         list($query, $arguments) = $this->fullRelease_buildQuery($querydata, true, true, true);
214: 
215:         $stm_get_diffexpr = $db->prepare($query);
216:         $stm_get_diffexpr->execute($arguments);
217:         $data = array(
218:             "sEcho" => intval($querydata['sEcho']),
219:             "iTotalDisplayRecords" => $stm_get_diffexpr->rowCount(),
220:             "aaData" => array()
221:         );
222: 
223: 
224:         while ($row = $stm_get_diffexpr->fetch(PDO::FETCH_ASSOC)) {
225:             if (!isset($data['iTotalRecords'])) {
226:                 $data['iTotalRecords'] = $row['cnt'];
227:                 $data['iTotalDisplayRecords'] = $row['cnt'];
228:             }
229:             array_walk($row, array('webservices\listing\Differential_expressions', 'format'));
230:             $data['aaData'][] = $row; //array_values($row);
231:         }
232: 
233:         $data['query_details'] = $this->fullRelease_getQueryDetails($querydata, true, true, true);
234: 
235:         return $data;
236:     }
237: 
238:     /**
239:      * outputs data as csv
240:      * @global \PDO $db
241:      * @param Array $querydata
242:      */
243:     public function printCsv($querydata) {
244:         global $db;
245: 
246: #UI hint
247:         if (false)
248:             $db = new PDO();
249: 
250:         list($query, $arguments) = $this->fullRelease_buildQuery($querydata, true, true, false);
251: 
252:         $stm_get_diffexpr = $db->prepare($query);
253:         $stm_get_diffexpr->execute($arguments);
254: 
255:         $query_details = $this->fullRelease_getQueryDetails($querydata, true, true, true);
256: 
257:         // output header
258:         echo "# Differential Expression Results\n";
259:         printf("# you can reach the feature details via %s/details/byId/<feature_id>\n", APPPATH);
260:         foreach ($query_details as $mkey => $item) {
261:             echo "# $mkey";
262: 
263:             if (is_string($item)) {
264:                 echo "\n#\t$item\n";
265:             } else if (is_array($item)) {
266:                 echo "\n";
267:                 foreach ($item as $ikey => $ivalue) {
268:                     if (is_string($ikey))
269:                         echo "#\t$ikey\t$ivalue\n";
270:                     else
271:                         echo "#\t$ivalue\n";
272:                 }
273:             }
274:         }
275: 
276:         //output csv
277:         $out = fopen('php://output', 'w');
278:         $first = true;
279:         while ($row = $stm_get_diffexpr->fetch(PDO::FETCH_ASSOC)) {
280:             if ($first) {
281:                 $first = false;
282:                 fputcsv($out, array_keys($row));
283:             }
284:             array_walk($row, array('webservices\listing\Differential_expressions', 'format'));
285:             fputcsv($out, array_values($row));
286:         }
287:         fclose($out);
288:     }
289: 
290:     /**
291:      * @inheritDoc
292:      * Switches behaviour based on $querydata['query1']: "fullRelease" or "releaseCsv"
293:      * even though the name indicates differently, feature ids for a full release subset can be passed
294:      * @param Array $querydata
295:      * @return Array
296:      */
297:     public function execute($querydata) {
298:         if ($querydata['query1'] == 'fullRelease') {
299:             return $this->fullRelease($querydata);
300:         } elseif ($querydata['query1'] == 'releaseCsv') {
301:             header("Pragma: public");
302:             header("Expires: 0");
303:             header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
304:             header("Cache-Control: private", false);
305:             header("Content-Type: application/octet-stream");
306:             header("Content-Disposition: attachment; filename=\"diffexp_export.csv\";");
307:             header("Content-Transfer-Encoding: binary");
308:             $this->printCsv($querydata);
309:             //die or WebService->output will attach return value to output (in our case: null)
310:             die();
311:         }
312:     }
313: 
314:     static function format(&$val, $key) {
315:         if (is_numeric($val) && round($val) != $val)
316:             $val = sprintf('%.5e', $val);
317:         else if ($val == 'Infinity') {
318:             $val = 'Inf';
319:         } else if ($val == '-Infinity') {
320:             $val = '-Inf';
321:         }
322:     }
323: 
324: }
325: 
326: ?>
327: