function getAlphnumericReport($type = 'applications', $flt = "", $subtype = null) { $sel = ""; $model = null; $exp = ""; $subtypequery = ""; switch ($type) { case "researchers": $sel = "researchers.name"; $model = new Default_Model_Researchers(); $model->filter = FilterParser::getPeople($flt); $subtypequery = " WHERE researchers.deleted=false "; break; case "vos": $sel = "vos.name"; $model = new Default_Model_VOs(); $model->filter = FilterParser::getVOs($flt); break; default: $sel = "applications.name"; $model = new Default_Model_Applications(); $model->filter = FilterParser::getApplications($flt); $deleted = "applications.deleted=" . (is_null($subtype) == false && strtolower($subtype) != "deleted" ? "false " : "true "); $moderated = " AND applications.moderated=" . (is_null($subtype) == false && strtolower($subtype) != "moderated" ? "false " : "true "); $subtypequery = " WHERE " . $deleted . $moderated; break; } if (is_null($subtype) === false && $subtypequery == "") { $subtypequery = " WHERE " . $type . "." . $subtype . "=true"; } $so = $model->getMapper()->getDbTable()->select(); $so->from($type); $model->getMapper()->joins($so, $model->filter); $q1 = $so . '' . ($model->filter->expr() ? " WHERE " . $model->filter->expr() : ''); $q = "SELECT count(*) cnt, typechar \n\t\tFROM (SELECT CASE \n\t\tWHEN lower(substring(" . $sel . " from 1 for 1)) ~ '[0-9]' THEN '0-9'\n\t\tWHEN lower(substring(" . $sel . " from 1 for 1)) ~ '[a-zA-Z]' THEN lower(substring(" . $sel . " from 1 for 1))\n\t\tELSE '...'\n\t\tEND typechar FROM (" . $q1 . ") AS " . $type . " " . $subtypequery . " ORDER BY " . $sel . " ASC\n\t\t) fc GROUP BY fc.typechar ORDER BY fc.typechar;"; error_log("MODEL EXPRESSION!!!! => " . $q); try { global $application; $db = $application->getBootstrap()->getResource('db'); $db->setFetchMode(Zend_Db::FETCH_BOTH); $res = $db->query($q)->fetchAll(); } catch (Exception $e) { $res = array(); error_log($e->getMessage()); } return $res; }
/** * @overrides get() from RestResource */ public function get($extraFilter = null) { if (parent::get() !== false) { global $application; $isAdmin = $this->userIsAdmin(); $mapper = new Default_Model_VOsMapper(); $db = $application->getBootstrap()->getResource('db'); $flt = $this->getParam("flt"); $select = $mapper->getDbTable()->getAdapter()->select()->distinct()->from('vos'); $from = ''; $where = ''; $orderby = ''; $limit = ''; $filter = FilterParser::getVOs($flt); if (is_array($filter->expr())) { $ex = implode(" ", $filter->expr()); } else { $ex = $filter->expr(); } $fltexp = $filter->expr(); if (!is_array($fltexp)) { $fltexp = array($fltexp); } foreach ($fltexp as $x) { getZendSelectParts($select, $from, $where, $orderby, $limit); if (!$isAdmin) { if (strpos($from, ' applications ') !== false && (strpos($ex, 'applications.moderated) IS FALSE') === false || strpos($ex, 'applications.deleted) IS FALSE') === false)) { $f = new Default_Model_ApplicationsFilter(); $f->moderated->equals(false)->and($f->deleted->equals(false)); $filter->chain($f, "AND"); } if (strpos($from, ' researchers ') !== false && strpos($ex, 'researchers.deleted) IS FALSE') === false) { $f = new Default_Model_ResearchersFilter(); $f->deleted->equals(false); $filter->chain($f, "AND"); } } if (strpos($ex, 'vos.deleted) IS FALSE') === false) { $f = new Default_Model_VOsFilter(); $f->deleted->equals(false); $filter->chain($f, "AND"); } } if (!is_null($extraFilter)) { $filter->chain($extraFilter, "AND"); } $mapper->joins($select, $filter); if (is_array($filter->expr())) { $from = array(); $where = array(); foreach ($filter->expr() as $x) { $s = clone $select; $s->where($x); getZendSelectParts($s, $f, $w, $orderby, $limit); $from[] = $f; $where[] = $w; } $flt = str_replace("''", "\\'", php_to_pg_array($filter->fltstr, false)); $from = str_replace("''", "\\'", php_to_pg_array($from, false)); $where = str_replace("''", "\\'", php_to_pg_array($where, false)); } else { $select->where($filter->expr()); getZendSelectParts($select, $from, $where, $orderby, $limit); } $db->setFetchMode(Zend_Db::FETCH_BOTH); $rs = $db->query('SELECT * FROM vo_logistics(?,?,?)', array($flt, $from, $where))->fetchAll(); if (count($rs) > 0) { $rs = $rs[0]; $x = $rs['vo_logistics']; } else { $x = ''; } return new XMLFragmentRestResponse($x, $this); } else { return false; } }