/**
* 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();
}
