$value) { $sql .= self::sql_filter($key,$value); } $sql = rtrim($sql,'AND ') . ' '; return $sql; } // get_filter_sql /** * get_sort_sql * Returns the sort sql part */ private static function get_sort_sql() { if (!is_array($_SESSION['browse']['sort'][self::$type])) { return ''; } $sql = 'ORDER BY '; foreach ($_SESSION['browse']['sort'][self::$type] as $key=>$value) { $sql .= self::sql_sort($key,$value); } $sql = rtrim($sql,'ORDER BY '); $sql = rtrim($sql,','); return $sql; } // get_sort_sql /** * get_limit_sql * This returns the limit part of the sql statement */ private static function get_limit_sql() { if (!self::is_simple_browse()) { return ''; } $sql = ' LIMIT ' . intval(self::$start) . ',' . intval(self::$offset); return $sql; } // get_limit_sql /** * get_join_sql * This returns the joins that this browse may need to work correctly */ private static function get_join_sql() { if (!is_array($_SESSION['browse']['join'][self::$type])) { return ''; } $sql = ''; foreach ($_SESSION['browse']['join'][self::$type] AS $join) { $sql .= $join . ' '; } return $sql; } // get_join_sql /** * get_sql * This returns the sql statement we are going to use this has to be run * every time we get the objects because it depends on the filters and the * type of object we are currently browsing */ public static function get_sql() { $sql = self::get_base_sql(); // No matter what we have to check the catalog based filters... maybe I'm not sure about this //$where_sql .= self::sql_filter('catalog',''); $filter_sql = self::get_filter_sql(); $join_sql = self::get_join_sql(); $order_sql = self::get_sort_sql(); $limit_sql = self::get_limit_sql(); $final_sql = $sql . $join_sql . $filter_sql . $order_sql . $limit_sql; return $final_sql; } // get_sql /** * post_process * This does some additional work on the results that we've received before returning them */ private static function post_process($results) { $tags = $_SESSION['browse']['filter']['tag']; if (!is_array($tags) || sizeof($tags) < 2) { return $results; } $cnt = sizeof($tags); $ar = array(); foreach($results as $row) { $ar[$row['id']]++; } $res = array(); foreach($ar as $k=>$v) { if ($v >= $cnt) { $res[] = array('id' => $k); } } // end foreach return $res; } // post_process /** * sql_filter * This takes a filter name and value and if it is possible * to filter by this name on this type returns the approiate sql * if not returns nothing */ private static function sql_filter($filter,$value) { $filter_sql = ''; if (self::$type == 'song') { switch($filter) { case 'alpha_match': $filter_sql = " `song`.`title` LIKE '%" . Dba::escape($value) . "%' AND "; break; case 'starts_with': $filter_sql = " `song`.`title` LIKE '" . Dba::escape($value) . "%' AND "; break; case 'unplayed': $filter_sql = " `song`.`played`='0' AND "; break; case 'album': $filter_sql = " `song`.`album` = '". Dba::escape($value) . "' AND "; break; case 'artist': $filter_sql = " `song`.`artist` = '". Dba::escape($value) . "' AND "; break; case 'catalog': $catalogs = $GLOBALS['user']->get_catalogs(); if (!count($catalogs)) { break; } $filter_sql .= " `song`.`catalog` IN (" . implode(',',$GLOBALS['user']->get_catalogs()) . ") AND "; break; default: // Rien a faire break; } // end list of sqlable filters } // if it is a song elseif (self::$type == 'album') { switch($filter) { case 'alpha_match': $filter_sql = " `album`.`name` LIKE '%" . Dba::escape($value) . "%' AND "; break; case 'starts_with': $filter_sql = " `album`.`name` LIKE '" . Dba::escape($value) . "%' AND "; break; case 'min_count': break; case 'artist': $filter_sql = " `artist`.`id` = '". Dba::escape($value) . "' AND "; break; default: // Rien a faire break; } } // end album elseif (self::$type == 'artist') { switch($filter) { case 'alpha_match': $filter_sql = " `artist`.`name` LIKE '%" . Dba::escape($value) . "%' AND "; break; case 'starts_with': $filter_sql = " `artist`.`name` LIKE '" . Dba::escape($value) . "%' AND "; break; default: // Rien a faire break; } // end filter } // end artist elseif (self::$type == 'live_stream') { switch ($filter) { case 'alpha_match': $filter_sql = " `live_stream`.`name` LIKE '%" . Dba::escape($value) . "%' AND "; break; case 'starts_with': $filter_sql = " `live_stream`.`name` LIKE '" . Dba::escape($value) . "%' AND "; break; default: // Rien a faire break; } // end filter } // end live_stream elseif (self::$type == 'playlist') { switch ($filter) { case 'alpha_match': $filter_sql = " `playlist`.`name` LIKE '%" . Dba::escape($value) . "%' AND "; break; case 'starts_with': $filter_sql = " `playlist`.`name` LIKE '" . Dba::escape($value) . "%' AND "; break; case 'playlist_type': $user_id = intval($GLOBALS['user']->id); $filter_sql = " (`playlist`.`type` = 'public' OR `playlist`.`user`='$user_id') AND "; break; default; // Rien a faire break; } // end filter } // end playlist return $filter_sql; } // sql_filter /** * logic_filter * This runs the filters that we can't easily apply * to the sql so they have to be done after the fact * these should be limited as they are often intensive and * require additional queries per object... :( */ private static function logic_filter($object_id) { return true; } // logic_filter /** * sql_sort * This builds any order bys we need to do * to sort the results as best we can, there is also * a logic based sort that will come later as that's * a lot more complicated */ private static function sql_sort($field,$order) { if ($order != 'DESC') { $order == 'ASC'; } // Depending on the type of browsing we are doing we can apply different filters that apply to different fields switch (self::$type) { case 'song': switch($field) { case 'title'; $sql = "`song`.`title`"; break; case 'year': $sql = "`song`.`year`"; break; case 'time': $sql = "`song`.`time`"; break; case 'track': $sql = "`song`.`track`"; break; case 'album': $sql = '`album`.`name`'; self::set_join('left','`album`','`album`.`id`','`song`.`album`'); break; case 'artist': $sql = '`artist`.`name`'; self::set_join('left','`artist`','`artist`.`id`','`song`.`artist`'); break; default: // Rien a faire break; } // end switch break; case 'album': switch($field) { case 'name': $sql = "`album`.`name` $order, `album`.`disk`"; break; case 'artist': $sql = "`artist`.`name`"; self::set_join('left','`song`','`song`.`album`','`album`.`id`'); self::set_join('left','`artist`','`song`.`artist`','`artist`.`id`'); break; case 'year': $sql = "`album`.`year`"; break; } // end switch break; case 'artist': switch ($field) { case 'name': $sql = "`artist`.`name`"; break; } // end switch break; case 'playlist': switch ($field) { case 'type': $sql = "`playlist`.`type`"; break; case 'name': $sql = "`playlist`.`name`"; break; case 'user': $sql = "`playlist`.`user`"; break; } // end switch break; case 'live_stream': switch ($field) { case 'name': $sql = "`live_stream`.`name`"; break; case 'call_sign': $sql = "`live_stream`.`call_sign`"; break; case 'frequency': $sql = "`live_stream`.`frequency`"; break; } // end switch break; case 'genre': switch ($field) { case 'name': $sql = "`genre`.`name`"; break; } // end switch break; case 'user': switch ($field) { case 'username': $sql = "`user`.`username`"; break; case 'fullname': $sql = "`user`.`fullname`"; break; case 'last_seen': $sql = "`user`.`last_seen`"; break; case 'create_date': $sql = "`user`.`create_date`"; break; } // end switch break; default: // Rien a faire break; } // end switch if ($sql) { $sql_sort = "$sql $order,"; } return $sql_sort; } // sql_sort /** * show_objects * This takes an array of objects * and requires the correct template based on the * type that we are currently browsing */ public static function show_objects($object_ids=false) { if (self::is_simple_browse()) { $object_ids = self::get_saved(); } else { $object_ids = is_array($object_ids) ? $object_ids : self::get_saved(); self::save_objects($object_ids); } // Reset the total items self::$total_objects = self::get_total($object_ids); // Limit is based on the users preferences if this is not a simple browse because we've got too much here if (count($object_ids) > self::$start AND !self::is_simple_browse()) { $object_ids = array_slice($object_ids,self::$start,self::$offset); } // Format any matches we have so we can show them to the masses if ($filter_value = self::get_filter('alpha_match')) { $match = ' (' . $filter_value . ')'; } elseif ($filter_value = self::get_filter('starts_with')) { $match = ' (' . $filter_value . ')'; } // Set the correct classes based on type $class = "box browse_".self::$type; // Load any additional object we need for this $extra_objects = self::get_supplemental_objects(); foreach ($extra_objects as $class_name => $id) { ${$class_name} = new $class_name($id); } Ajax::start_container('browse_content'); // Switch on the type of browsing we're doing switch (self::$type) { case 'song': show_box_top(_('Songs') . $match, $class); Song::build_cache($object_ids); require_once Config::get('prefix') . '/templates/show_songs.inc.php'; show_box_bottom(); break; case 'album': show_box_top(_('Albums') . $match, $class); Album::build_cache($object_ids,'extra'); require_once Config::get('prefix') . '/templates/show_albums.inc.php'; show_box_bottom(); break; case 'user': show_box_top(_('Manage Users') . $match, $class); require_once Config::get('prefix') . '/templates/show_users.inc.php'; show_box_bottom(); break; case 'artist': show_box_top(_('Artists') . $match, $class); Artist::build_cache($object_ids,'extra'); require_once Config::get('prefix') . '/templates/show_artists.inc.php'; show_box_bottom(); break; case 'live_stream': show_box_top(_('Radio Stations') . $match, $class); require_once Config::get('prefix') . '/templates/show_live_streams.inc.php'; show_box_bottom(); break; case 'playlist': show_box_top(_('Playlists') . $match, $class); require_once Config::get('prefix') . '/templates/show_playlists.inc.php'; show_box_bottom(); break; case 'playlist_song': show_box_top(_('Playlist Songs') . $match,$class); require_once Config::get('prefix') . '/templates/show_playlist_songs.inc.php'; show_box_bottom(); break; case 'playlist_localplay': show_box_top(_('Current Playlist')); require_once Config::get('prefix') . '/templates/show_localplay_playlist.inc.php'; show_box_bottom(); break; case 'catalog': show_box_top(_('Catalogs'), $class); require_once Config::get('prefix') . '/templates/show_catalogs.inc.php'; show_box_bottom(); break; case 'shoutbox': show_box_top(_('Shoutbox Records'),$class); require_once Config::get('prefix') . '/templates/show_manage_shoutbox.inc.php'; show_box_bottom(); break; case 'flagged': show_box_top(_('Flagged Records'),$class); require_once Config::get('prefix') . '/templates/show_flagged.inc.php'; show_box_bottom(); break; default: // Rien a faire break; } // end switch on type Ajax::end_container(); } // show_object /** * save_objects * This takes the full array of object ides, often passed into show and then * if nessecary it saves them into the session */ public static function save_objects($object_ids) { // Saving these objects has two operations, one hold it in // a local variable and then second hold it in a row in the tmp_browse // table self::$_cache['browse'][self::$type] = $object_ids; // Only do this if it's a not a simple browse if (!self::is_simple_browse()) { $sid = session_id() . '::' . self::$type; $data = Dba::escape(serialize($object_ids)); $sql = "REPLACE INTO `tmp_browse` SET `data`='$data', `sid`='$sid'"; $db_results = Dba::write($sql); self::$total_objects = count($object_ids); } // save it return true; } // save_objects /** * resort_objects * This takes the existing objects, looks at the current * sort method and then re-sorts them This is internally * called by the set_sort() function */ private static function resort_objects() { // There are two ways to do this.. the easy way... // and the vollmer way, hopefully we don't have to // do it the vollmer way if (self::is_simple_browse()) { $sql = self::get_sql(); } else { // First pull the objects $objects = self::get_saved(); // If there's nothing there don't do anything if (!count($objects)) { return false; } $type = self::$type; $where_sql = "WHERE `$type`.`id` IN ("; foreach ($objects as $object_id) { $object_id = Dba::escape($object_id); $where_sql .= "'$object_id',"; } $where_sql = rtrim($where_sql,','); $where_sql .= ")"; $sql = self::get_base_sql(); $sql .= $where_sql; $order_sql = " ORDER BY "; foreach ($_SESSION['browse']['sort'][self::$type] as $key=>$value) { $order_sql .= self::sql_sort($key,$value); } // Clean her up $order_sql = rtrim($order_sql,"ORDER BY "); $order_sql = rtrim($order_sql,","); $sql = $sql . self::get_join_sql() . $order_sql; } // if not simple $db_results = Dba::query($sql); while ($row = Dba::fetch_assoc($db_results)) { $results[] = $row['id']; } self::save_objects($results); return true; } // resort_objects /** * _auto_init * this function reloads information back from the session * it is called on creation of the class */ public static function _auto_init() { self::$offset = Config::get('offset_limit') ? Config::get('offset_limit') : '25'; } // _auto_init public static function set_filter_from_request($r) { foreach ($r as $k=>$v) { //reinterpret v as a list of int $vl = explode(',', $v); $ok = 1; foreach($vl as $i) { if (!is_numeric($i)) { $ok = 0; break; } } if ($ok) if (sizeof($vl) == 1) self::set_filter($k, $vl[0]); else self::set_filter($k, $vl); } } } // browse