/** * Build query * * @param array $filters * @return query string */ public function buildQuery($filters = array(), $admin = false) { $now = Date::toSql(); $groupby = ' GROUP BY C.id '; $project = isset($filters['project']) && intval($filters['project']) ? $filters['project'] : ""; $dev = isset($filters['dev']) && $filters['dev'] == 1 ? 1 : 0; $projects = isset($filters['projects']) && !empty($filters['projects']) ? $filters['projects'] : array(); $mine = isset($filters['mine']) && $filters['mine'] ? $filters['mine'] : 0; $sortby = isset($filters['sortby']) ? $filters['sortby'] : 'title'; $query = "FROM "; if (isset($filters['tag']) && $filters['tag'] != '') { $query .= "#__tags_object AS RTA "; $query .= "INNER JOIN #__tags AS TA ON RTA.tagid = TA.id AND RTA.tbl='publications', "; } $query .= " #__publication_versions as V, #__projects as PP,\n\t\t\t\t #__publication_master_types AS MT"; if (isset($filters['author']) && intval($filters['author'])) { $query .= ", #__publication_authors as A "; } $query .= ", {$this->_tbl} AS C "; $query .= "LEFT JOIN #__publication_categories AS t ON t.id=C.category "; $query .= " WHERE V.publication_id=C.id AND MT.id=C.master_type AND PP.id = C.project_id "; if ($dev) { $query .= " AND V.main=1 "; if (isset($filters['status']) && $filters['status'] != 'all') { if (is_array($filters['status'])) { $squery = ''; foreach ($filters['status'] as $s) { $squery .= "'" . $s . "',"; } $squery = substr($squery, 0, strlen($squery) - 1); $query .= " AND (V.state IN (" . $squery . ")) "; } else { $query .= " AND V.state=" . intval($filters['status']); } } if ($mine) { if (count($projects) > 0) { $p_query = ''; foreach ($projects as $p) { $p_query .= "'" . $p . "',"; } $p_query = substr($p_query, 0, strlen($p_query) - 1); $query .= " AND (C.project_id IN (" . $p_query . ")) "; } else { $query .= "AND C.created_by=" . intval($filters['mine']); } } // Individual assigned curator? if (isset($filters['curator'])) { if ($filters['curator'] == 'owner') { $query .= " AND V.curator = " . User::get('id'); } if ($filters['curator'] == 'other') { $query .= " AND V.curator != " . User::get('id'); } } // Make sure we get the max version $query .= " AND V.id = (SELECT MAX(wv2.id) FROM `#__publication_versions` AS wv2 WHERE wv2.publication_id = C.id)"; } else { $query .= " AND V.version_number = (SELECT MAX(version_number) FROM #__publication_versions\n\t\t\t\t\t\tWHERE publication_id=C.id AND state=1 ) AND (V.state=1"; if (count($projects) > 0) { $p_query = ''; foreach ($projects as $p) { $p_query .= "'" . $p . "',"; } $p_query = substr($p_query, 0, strlen($p_query) - 1); $query .= " OR (C.project_id IN (" . $p_query . ") AND V.state != 3 AND V.state != 2) "; } $query .= ") "; } $query .= $project ? " AND C.project_id=" . $project : ""; // Category if (isset($filters['category']) && $filters['category'] != '') { if (is_numeric($filters['category'])) { $query .= " AND C.category=" . $filters['category'] . " "; } else { $query .= " AND t.url_alias='" . $filters['category'] . "' "; } } if (isset($filters['author']) && intval($filters['author'])) { $query .= " AND A.publication_version_id=V.id AND A.user_id=" . $filters['author']; $query .= " AND A.status=1 AND (A.role IS NULL OR A.role!='submitter') "; } // Master type if (isset($filters['master_type']) && $filters['master_type'] != '') { if (is_array($filters['master_type']) && !empty($filters['master_type'])) { $tquery = ''; foreach ($filters['master_type'] as $type) { $tquery .= "'" . $type . "',"; } $tquery = substr($tquery, 0, strlen($tquery) - 1); $query .= " AND ((C.master_type IN (" . $tquery . ") ) "; } elseif (is_numeric($filters['master_type'])) { $query .= " AND (C.master_type=" . $filters['master_type'] . " "; } elseif (is_string($filters['master_type'])) { $query .= " AND (MT.alias='" . $filters['master_type'] . "' "; } else { $query .= " AND (1=1"; } $query .= " OR V.curator = " . User::get('id') . ") "; } if (isset($filters['minranking']) && $filters['minranking'] != '' && $filters['minranking'] > 0) { $query .= " AND C.ranking > " . $filters['minranking'] . " "; } if (!$dev) { $query .= " AND (V.published_up = '0000-00-00 00:00:00' OR V.published_up <= '" . $now . "') "; $query .= " AND (V.published_down IS NULL OR V.published_down = '0000-00-00 00:00:00' OR V.published_down >= '" . $now . "') "; } if (isset($filters['startdate'])) { $query .= "AND V.published_up > " . $this->_db->quote($filters['startdate']) . " "; } if (isset($filters['enddate'])) { $query .= "AND V.published_up < " . $this->_db->quote($filters['enddate']) . " "; } if (isset($filters['search']) && $filters['search'] != '') { $words = array(); $ws = explode(' ', $filters['search']); foreach ($ws as $w) { $w = trim($w); if (strlen($w) > 2) { $words[] = $w; } } $text = implode(' +', $words); $text = addslashes($text); $text2 = str_replace('+', '', $text); $query .= " AND ((MATCH(V.title) AGAINST ('+{$text} -\"{$text2}\"') > 0) OR" . " (MATCH(V.abstract,V.description) AGAINST ('+{$text} -\"{$text2}\"') > 0)) "; } // Do not show deleted if ($admin == false || isset($filters['status']) && $filters['status'] != 2) { $query .= " AND V.state != 2 "; } if (!isset($filters['ignore_access']) || $filters['ignore_access'] == 0) { $query .= " AND (V.access != 2) "; } if (isset($filters['tag']) && $filters['tag'] != '') { include_once PATH_CORE . DS . 'components' . DS . 'com_publications' . DS . 'helpers' . DS . 'tags.php'; $tagging = new \Components\Publications\Helpers\Tags($this->_db); $tags = $tagging->_parse_tags($filters['tag']); $query .= "AND RTA.objectid=C.id AND TA.tag IN ('" . implode("','", $tags) . "')"; $groupby = " GROUP BY C.id HAVING uniques=" . count($tags); } $query .= $groupby; if (!isset($filters['count']) or $filters['count'] == 0) { $query .= " ORDER BY "; $sortdir = isset($filters['sortdir']) && strtoupper($filters['sortdir']) == 'DESC' ? 'DESC' : 'ASC'; switch ($sortby) { case 'date': case 'date_published': $query .= 'V.published_up DESC'; break; case 'date_oldest': $query .= 'V.published_up ASC'; break; case 'date_accepted': $query .= 'V.accepted DESC'; break; case 'date_created': $query .= 'C.created DESC'; break; case 'date_version_created': $query .= 'V.created DESC'; break; case 'date_modified': $query .= 'V.modified DESC'; break; case 'title': default: $query .= 'V.title ' . $sortdir . ', V.version_number DESC'; break; case 'id': $query .= 'C.id ' . $sortdir; break; case 'mine': $query .= 'PP.provisioned DESC, V.title ' . $sortdir . ', V.version_number DESC'; break; case 'rating': $query .= "C.rating DESC, C.times_rated DESC"; break; case 'ranking': $query .= "C.ranking DESC"; break; case 'project': $query .= "PP.title " . $sortdir; break; case 'version_ranking': $query .= "V.ranking DESC"; break; case 'popularity': $query .= "stat DESC, V.published_up ASC"; break; case 'category': $query .= "C.category " . $sortdir; break; case 'type': $query .= "C.master_type " . $sortdir; break; case 'status': $query .= "V.state " . $sortdir; break; case 'random': $query .= "RAND()"; break; case 'submitted': $query .= "V.submitted " . $sortdir; break; } } return $query; }
/** * Get stats for publication(s) for a custom report * * @param string $from Date from * @param string $to Date to * @param array $data Data to extract * @param string $filter Tag name to match * @return void */ public function getCustomStats($from = NULL, $to = NULL, $exclude = array(), $filter = NULL) { // Parse dates $parts = explode('-', $from); $fromY = substr($parts[0], -2, 2); $fromM = intval(end($parts)); $parts = explode('-', $to); $toY = substr($parts[0], -2, 2); $toM = intval(end($parts)); $datequery = $fromY == $toY ? "AND (L.year={$fromY} AND L.month>={$fromM} AND L.month<={$toM} )" : "AND ((L.year={$fromY} AND L.month >= {$fromM} ) OR (L.year={$toY} AND L.month <= {$toM}))"; $citeFrom = Date::of($from)->toSql(); $citeTo = Date::of($to)->toSql(); $query = "SELECT V.publication_id as id, V.title, A.name as author,\n\t\t\t\t\tV.version_label as version, V.doi"; $query .= ", (SELECT COALESCE( SUM(L.primary_accesses) , 0 )\n\t\t\t\t\tFROM {$this->_tbl} as L\n\t\t\t\t\tWHERE L.publication_id=V.publication_id " . $datequery . ") AS downloads "; $query .= ", (SELECT COALESCE( SUM(L.page_views) , 0 )\n\t\t\t\t\tFROM {$this->_tbl} as L\n\t\t\t\t\tWHERE L.publication_id=V.publication_id " . $datequery . ") AS views "; $query .= ", (SELECT COUNT(*)\n\t\t\t\t\tFROM #__citations as C\n\t\t\t\t\tJOIN #__citations_assoc as CA ON CA.cid=C.id\n\t\t\t\t\tAND tbl='publication'\n\t\t\t\t\tWHERE CA.oid=V.publication_id\n\t\t\t\t\tAND C.created <= " . $this->_db->quote($citeTo) . " ) AS citations "; $query .= "FROM "; if ($filter) { $query .= "#__tags_object AS RTA "; $query .= "INNER JOIN #__tags AS TA ON RTA.tagid = TA.id AND RTA.tbl='publications', "; } $query .= " #__publications as C, #__publication_categories AS t, #__publication_versions as V "; $query .= " LEFT JOIN #__publication_authors as A\n\t\t\t\t\tON A.publication_version_id=V.id\n\t\t\t\t\tAND A.ordering=1 AND status=1"; $query .= " WHERE C.id=V.publication_id AND V.state=1 AND C.category = t.id\n\t\t\t\t\tAND V.main=1 AND V.published_up < '" . Date::toSql() . "' "; if (!empty($exclude)) { $query .= " AND C.project_id NOT IN ("; $tquery = ''; foreach ($exclude as $ex) { $tquery .= "'" . $ex . "',"; } $tquery = substr($tquery, 0, strlen($tquery) - 1); $query .= $tquery . ") "; } if ($filter) { include_once PATH_CORE . DS . 'components' . DS . 'com_publications' . DS . 'helpers' . DS . 'tags.php'; $tagging = new \Components\Publications\Helpers\Tags($this->_db); $tags = $tagging->_parse_tags($filter); $query .= " AND RTA.objectid=C.id AND (TA.tag IN ("; $tquery = ''; foreach ($tags as $tagg) { $tquery .= "'" . $tagg . "',"; } $tquery = substr($tquery, 0, strlen($tquery) - 1); $query .= $tquery . "))"; } $query .= " GROUP BY V.publication_id "; $query .= " ORDER BY V.publication_id ASC "; $this->_db->setQuery($query); return $this->_db->loadObjectList(); }