1: <?php
2:
3: namespace webservices\listing;
4:
5: use \PDO as PDO;
6: 7: 8: 9: 10:
11: class Differential_expressions extends \WebService {
12: 13: 14: 15: 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: 31: 32: 33: 34: 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: 83: 84: 85: 86: 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: 123: 124: 125: 126: 127: 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: 202: 203: 204: 205:
206: public function fullRelease($querydata) {
207: global $db;
208:
209:
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;
231: }
232:
233: $data['query_details'] = $this->fullRelease_getQueryDetails($querydata, true, true, true);
234:
235: return $data;
236: }
237:
238: 239: 240: 241: 242:
243: public function printCsv($querydata) {
244: global $db;
245:
246:
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:
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:
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: 292: 293: 294: 295: 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:
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: