/**
* get_tag_objects
* This gets the objects from a specified tag and returns an array of object ids, nothing more
*/
public static function get_tag_objects($type, $tag_id, $count = '', $offset = '')
{
if (!Core::is_library_item($type)) {
return false;
}
$limit_sql = "";
if ($count) {
$limit_sql = "LIMIT ";
if ($offset) {
$limit_sql .= intval($offset) . ',';
}
$limit_sql .= intval($count);
}
$sql = "SELECT DISTINCT `tag_map`.`object_id` FROM `tag_map` " . "WHERE `tag_map`.`tag_id` = ? AND `tag_map`.`object_type` = ? {$limit_sql} ";
if (AmpConfig::get('catalog_disable')) {
$sql .= "AND " . Catalog::get_enable_filter($type, '`tag_map`.`object_id`');
}
$db_results = Dba::read($sql, array($tag_id, $type));
$results = array();
while ($row = Dba::fetch_assoc($db_results)) {
$results[] = $row['object_id'];
}
return $results;
}
/**
* get_filter_sql
* This returns the filter part of the sql statement
*/
private function get_filter_sql()
{
if (!is_array($this->_state['filter'])) {
return '';
}
$sql = "WHERE 1=1 AND ";
foreach ($this->_state['filter'] as $key => $value) {
$sql .= $this->sql_filter($key, $value);
}
if (AmpConfig::get('catalog_disable')) {
// Add catalog enabled filter
switch ($this->get_type()) {
case "video":
case "song":
$dis = Catalog::get_enable_filter($this->get_type(), '`' . $this->get_type() . '`.`id`');
break;
case "tag":
$dis = Catalog::get_enable_filter($this->get_type(), '`' . $this->get_type() . '`.`object_id`');
break;
}
}
if (!empty($dis)) {
$sql .= $dis . " AND ";
}
$sql = rtrim($sql, 'AND ') . ' ';
return $sql;
}
/**
* get_highest_sql
* Get highest sql
*/
public static function get_highest_sql($type)
{
$type = Stats::validate_type($type);
$sql = "SELECT `object_id` as `id`, AVG(`rating`) AS `rating` FROM rating" . " WHERE object_type = '" . $type . "'";
if (AmpConfig::get('catalog_disable')) {
$sql .= " AND " . Catalog::get_enable_filter($type, '`object_id`');
}
$sql .= " GROUP BY object_id ORDER BY `rating` DESC ";
return $sql;
}
/**
* get_recent_sql
* This returns the get_recent sql
*/
public static function get_recent_sql($type, $user_id = '')
{
$type = self::validate_type($type);
$user_sql = '';
if (!empty($user_id)) {
$user_sql = " AND `user` = '" . $user_id . "'";
}
$sql = "SELECT DISTINCT(`object_id`) as `id`, MAX(`date`) FROM object_count" . " WHERE `object_type` = '" . $type . "'" . $user_sql;
if (AmpConfig::get('catalog_disable')) {
$sql .= " AND " . Catalog::get_enable_filter($type, '`object_id`');
}
$sql .= " GROUP BY `object_id` ORDER BY MAX(`date`) DESC, `id` ";
return $sql;
}
/**
* get_latest_sql
* Get the latest sql
*/
public static function get_latest_sql($type, $user_id = null)
{
if (is_null($user_id)) {
$user_id = $GLOBALS['user']->id;
}
$user_id = intval($user_id);
$type = Stats::validate_type($type);
$sql = "SELECT `object_id` as `id` FROM user_flag" . " WHERE object_type = '" . $type . "' AND `user` = '" . $user_id . "'";
if (AmpConfig::get('catalog_disable')) {
$sql .= " AND " . Catalog::get_enable_filter($type, '`object_id`');
}
$sql .= " ORDER BY `date` DESC ";
return $sql;
}
/**
* get_latest_sql
* Get the latest sql
*/
public static function get_latest_sql($type, $user_id = null)
{
if (is_null($user_id)) {
$user_id = $GLOBALS['user']->id;
}
$user_id = intval($user_id);
$sql = "SELECT `user_flag`.`object_id` as `id`, `user_flag`.`object_type` as `type`, `user_flag`.`user` as `user` FROM `user_flag`";
if ($user_id <= 0) {
// Get latest only from user rights >= content manager
$sql .= " LEFT JOIN `user` ON `user`.`id` = `user_flag`.`user`" . " WHERE `user`.`access` >= 50";
}
if (!is_null($type)) {
if ($user_id <= 0) {
$sql .= " AND";
} else {
$sql .= " WHERE";
}
$type = Stats::validate_type($type);
$sql .= " `user_flag`.`object_type` = '" . $type . "'";
if ($user_id > 0) {
$sql .= " AND `user_flag`.`user` = '" . $user_id . "'";
}
if (AmpConfig::get('catalog_disable')) {
$sql .= " AND " . Catalog::get_enable_filter($type, '`object_id`');
}
}
$sql .= " ORDER BY `user_flag`.`date` DESC ";
return $sql;
}
/**
* get_artists_like
* Returns a list of similar artists
*/
public static function get_artists_like($artist_id, $limit = 10, $local_only = true)
{
$artist = new Artist($artist_id);
$cache = self::get_recommendation_cache('artist', $artist_id, true);
if (!$cache['id']) {
$similars = array();
$query = 'artist=' . rawurlencode($artist->name);
$xml = self::get_lastfm_results('artist.getsimilar', $query);
foreach ($xml->similarartists->children() as $child) {
$name = $child->name;
$mbid = (string) $child->mbid;
$local_id = null;
// First we check by MBID
if ($mbid) {
$sql = "SELECT `artist`.`id` FROM `artist` WHERE `mbid` = ?";
if (AmpConfig::get('catalog_disable')) {
$sql .= " AND " . Catalog::get_enable_filter('artist', '`artist`.`id`');
}
$db_result = Dba::read($sql, array($mbid));
if ($result = Dba::fetch_assoc($db_result)) {
$local_id = $result['id'];
}
}
// Then we fall back to the less likely to work exact
// name match
if (is_null($local_id)) {
$searchname = Catalog::trim_prefix($name);
$searchname = Dba::escape($searchname['string']);
$sql = "SELECT `artist`.`id` FROM `artist` WHERE `name` = ?";
if (AmpConfig::get('catalog_disable')) {
$sql .= " AND " . Catalog::get_enable_filter('artist', '`artist`.`id`');
}
$db_result = Dba::read($sql, array($searchname));
if ($result = Dba::fetch_assoc($db_result)) {
$local_id = $result['id'];
}
}
// Then we give up
if (is_null($local_id)) {
debug_event('Recommendation', "{$name} did not match any local artist", 5);
$similars[] = array('id' => null, 'name' => $name, 'mbid' => $mbid);
} else {
debug_event('Recommendation', "{$name} matched local artist " . $local_id, 5);
$similars[] = array('id' => $local_id, 'name' => $name);
}
}
if (count($similars) > 0) {
self::update_recommendation_cache('artist', $artist_id, $similars);
}
}
if (!isset($similars) || count($similars) == 0) {
$similars = $cache['items'];
}
if ($similars) {
$results = array();
foreach ($similars as $similar) {
if (!$local_only || !is_null($similar['id'])) {
$results[] = $similar;
}
if ($limit && count($results) >= $limit) {
break;
}
}
}
if (isset($results)) {
return $results;
}
return false;
}
/**
* get_recently_played
* This function returns the last X songs that have been played
* it uses the popular threshold to figure out how many to pull
* it will only return unique object
* @param int $user_id
* @return array
*/
public static function get_recently_played($user_id = 0)
{
$user_id = intval($user_id);
$sql = "SELECT `object_id`, `user`, `object_type`, `date`, `agent`, `geo_latitude`, `geo_longitude`, `geo_name` " . "FROM `object_count` WHERE `object_type` = 'song' AND `count_type` = 'stream' ";
if (AmpConfig::get('catalog_disable')) {
$sql .= "AND " . Catalog::get_enable_filter('song', '`object_id`') . " ";
}
if ($user_id) {
// If user is not empty, we're looking directly to user personal info (admin view)
$sql .= "AND `user`='{$user_id}' ";
} else {
if (!Access::check('interface', '100')) {
// If user identifier is empty, we need to retrieve only users which have allowed view of personnal info
$personal_info_id = Preference::id_from_name('allow_personal_info_recent');
if ($personal_info_id) {
$current_user = $GLOBALS['user']->id;
$sql .= "AND `user` IN (SELECT `user` FROM `user_preference` WHERE (`preference`='{$personal_info_id}' AND `value`='1') OR `user`='{$current_user}') ";
}
}
}
$sql .= "ORDER BY `date` DESC ";
$db_results = Dba::read($sql);
$results = array();
while ($row = Dba::fetch_assoc($db_results)) {
if (empty($row['geo_name']) && $row['latitude'] && $row['longitude']) {
$row['geo_name'] = Stats::get_cached_place_name($row['latitude'], $row['longitude']);
}
$results[] = $row;
if (count($results) >= AmpConfig::get('popular_threshold')) {
break;
}
}
return $results;
}
