/**
* 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;
}
/**
* Save a review
*
* @return void
*/
public function savereview()
{
// Is the user logged in?
if (User::isGuest()) {
$this->setError(Lang::txt('PLG_PUBLICATIONS_REVIEWS_LOGIN_NOTICE'));
return;
}
$publication =& $this->publication;
// Do we have a publication ID?
if (!$publication->exists()) {
// No ID - fail! Can't do anything else without an ID
$this->setError(Lang::txt('PLG_PUBLICATIONS_REVIEWS_NO_RESOURCE_ID'));
return;
}
$database = App::get('db');
// Bind the form data to our object
$row = new \Components\Publications\Tables\Review($database);
if (!$row->bind($_POST)) {
$this->setError($row->getError());
return;
}
// Perform some text cleaning, etc.
$row->id = Request::getInt('reviewid', 0);
$row->state = 1;
$row->comment = \Hubzero\Utility\Sanitize::stripAll($row->comment);
$row->anonymous = $row->anonymous == 1 || $row->anonymous == '1' ? $row->anonymous : 0;
$row->created = $row->created ? $row->created : Date::toSql();
$row->created_by = User::get('id');
$message = $row->id ? Lang::txt('PLG_PUBLICATIONS_REVIEWS_EDITS_SAVED') : Lang::txt('PLG_PUBLICATIONS_REVIEWS_REVIEW_POSTED');
// Check for missing (required) fields
if (!$row->check()) {
$this->setError($row->getError());
return;
}
// Save the data
if (!$row->store()) {
$this->setError($row->getError());
return;
}
// Calculate the new average rating for the parent publication
$publication->table()->calculateRating();
$publication->table()->updateRating();
// Process tags
$tags = trim(Request::getVar('review_tags', ''));
if ($tags) {
$rt = new \Components\Publications\Helpers\Tags($database);
$rt->tag_object($row->created_by, $publication->get('id'), $tags, 1, 0);
}
// Get version authors
$users = $publication->table('Author')->getAuthors($publication->get('version_id'), 1, 1, true);
// Build the subject
$subject = Config::get('sitename') . ' ' . Lang::txt('PLG_PUBLICATIONS_REVIEWS_CONTRIBUTIONS');
// Message
$eview = new \Hubzero\Plugin\View(array('folder' => 'publications', 'element' => 'reviews', 'name' => 'emails'));
$eview->option = $this->_option;
$eview->juser = User::getInstance();
$eview->publication = $publication;
$message = $eview->loadTemplate();
$message = str_replace("\n", "\r\n", $message);
// Build the "from" data for the e-mail
$from = array();
$from['name'] = Config::get('sitename') . ' ' . Lang::txt('PLG_PUBLICATIONS_REVIEWS_CONTRIBUTIONS');
$from['email'] = Config::get('mailfrom');
// Send message
if (!Event::trigger('xmessage.onSendMessage', array('publications_new_comment', $subject, $message, $from, $users, $this->_option))) {
$this->setError(Lang::txt('PLG_PUBLICATIONS_REVIEWS_FAILED_TO_MESSAGE'));
}
App::redirect(Route::url($publication->link('reviews')), $message);
return;
}
/**
* Process parsed data
*
* @access public
* @return string
*/
public function processRecord($item, &$out)
{
// Create publication record
if (!$item['publication']->store()) {
return false;
}
$pid = $item['publication']->id;
// Create version record
$item['version']->publication_id = $pid;
$item['version']->version_number = 1;
$item['version']->created_by = $this->_uid;
$item['version']->created = Date::toSql();
$item['version']->secret = strtolower(\Components\Projects\Helpers\Html::generateCode(10, 10, 0, 1, 1));
$item['version']->access = 0;
$item['version']->main = 1;
$item['version']->state = 3;
if (!$item['version']->store()) {
// Roll back
$item['publication']->delete();
return false;
}
$vid = $item['version']->id;
// Build pub object
$pub = new stdClass();
$pub = $item['version'];
$pub->id = $pid;
$pub->version_id = $vid;
// Build version object
$version = new stdClass();
$version->secret = $item['version']->secret;
$version->id = $vid;
$version->publication_id = $pid;
// Create attachments records and attach files
foreach ($item['files'] as $fileRecord) {
$this->processFileData($fileRecord, $pub, $version);
}
// Create author records
foreach ($item['authors'] as $authorRecord) {
$this->processAuthorData($authorRecord['author'], $pid, $vid);
}
// Build tags string
if ($item['tags']) {
$tags = '';
$i = 0;
foreach ($item['tags'] as $tag) {
$i++;
$tags .= trim($tag);
$tags .= $i == count($item['tags']) ? '' : ',';
}
// Add tags
$tagsHelper = new \Components\Publications\Helpers\Tags($this->database);
$tagsHelper->tag_object($this->_uid, $pid, $tags, 1);
}
// Display results
$out .= '<p class="publication">#' . $pid . ': <a href="' . trim($this->site, DS) . '/publications/' . $pid . DS . '1" rel="external">' . $item['version']->title . ' v.' . $item['version']->version_label . '</a></p>';
return true;
}
Ranking
</dt>
<dd>
<p>
Ranking is calculated from a formula comprised of <a href="<?php
echo Route::url('index.php?option=' . $this->option . '&id=' . $this->publication->id . '&active=reviews');
?>
">user reviews</a> and usage statistics. <a href="about/ranking/">Learn more ›</a>
</p>
<div></div>
</dd>
</dl>
<?php
}
// Supported publication?
$rt = new \Components\Publications\Helpers\Tags($database);
$supported = $rt->checkTagUsage($this->config->get('supportedtag'), $this->publication->id);
if ($supported) {
$tag = new \Components\Tags\Tables\Tag($database);
$tag->loadTag($this->config->get('supportedtag'));
$sl = $this->config->get('supportedlink');
if ($sl) {
$link = $sl;
} else {
$link = Route::url('index.php?option=com_tags&tag=' . $tag->tag);
}
echo '<p class="supported"><a href="' . $link . '">' . $tag->raw_tag . '</a></p>';
}
// Show audience
if ($this->params->get('show_audience')) {
$ra = new \Components\Publications\Tables\Audience($database);
echo Lang::txt('COM_PUBLICATIONS_NO_RESULTS');
?>
</p>
<?php
}
?>
<?php
$this->pageNav->setAdditionalUrlParam('tag', $this->filters['tag']);
$this->pageNav->setAdditionalUrlParam('category', $this->filters['category']);
$this->pageNav->setAdditionalUrlParam('sortby', $this->filters['sortby']);
echo $this->pageNav->render();
?>
<div class="clearfix"></div>
</div><!-- / .container -->
</div><!-- / .subject -->
<div class="aside">
<div class="container">
<h3>Popular Tags</h3>
<?php
$rt = new \Components\Publications\Helpers\Tags($database);
echo $rt->getTopTagCloud(20, $this->filters['tag']);
?>
<p><?php
echo Lang::txt('Click a tag to see only publications with that tag.');
?>
</p>
</div>
</div><!-- / .aside -->
</section><!-- / .main section -->
</form>
/**
* 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();
}
/**
* Check completion status
*
* @return object
*/
public function getStatus($pub = NULL, $manifest = NULL, $elementId = NULL)
{
// Start status
$status = new \Components\Publications\Models\Status();
$tagsHelper = new \Components\Publications\Helpers\Tags($this->_parent->_db);
// Required?
$required = $manifest->params->required;
$count = $tagsHelper->countTags($pub->id);
$status->status = $required && $count == 0 ? 0 : 1;
$status->status = !$required && $count == 0 ? 2 : $status->status;
return $status;
}
