$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(self::$_state['sort'][self::$type])) { return ''; } $sql = 'ORDER BY '; foreach (self::$_state['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()) { 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(self::$_state['join'][self::$type])) { return ''; } $sql = ''; // We need to itterate through these from 0 - 100 so that we add the joins in the right order foreach (self::$_state['join'][self::$type] as $joins) { foreach ($joins as $join) { $sql .= $join . ' '; } // end foreach joins at this level } // end foreach of this level of joins return $sql; } // get_join_sql /** * get_having_sql * this returns the having sql stuff, if we've got anything */ public static function get_having_sql() { $sql = self::$_state['having'][self::$type]; return $sql; } // get_having_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($limit=true) { $sql = self::get_base_sql(); $filter_sql = self::get_filter_sql(); $join_sql = self::get_join_sql(); $having_sql = self::get_having_sql(); $order_sql = self::get_sort_sql(); $limit_sql = $limit ? self::get_limit_sql() : ''; $final_sql = $sql . $join_sql . $filter_sql . $having_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 = self::$_state['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 = ''; switch (self::$type) { case 'song': switch($filter) { case 'exact_match': $filter_sql = " `song`.`title` = '" . Dba::escape($value) . "' AND "; break; 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 'add_gt': $filter_sql = " `song`.`addition_time` >= '" . Dba::escape($value) . "' AND "; break; case 'add_lt': $filter_sql = " `song`.`addition_time` <= '" . Dba::escape($value) . "' AND "; break; case 'update_gt': $filter_sql = " `song`.`update_time` >= '" . Dba::escape($value) . "' AND "; break; case 'update_lt': $filter_sql = " `song`.`update_time` <= '" . 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 break; case 'album': switch($filter) { case 'exact_match': $filter_sql = " `album`.`name` = '" . Dba::escape($value) . "' AND "; break; 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 'artist': $filter_sql = " `artist`.`id` = '". Dba::escape($value) . "' AND "; break; case 'add_lt': self::set_join('left','`song`','`song`.`album`','`album`.`id`'); $filter_sql = " `song`.`addition_time` <= '" . Dba::escape($value) . "' AND "; break; case 'add_gt': self::set_join('left','`song`','`song`.`album`','`album`.`id`'); $filter_sql = " `song`.`addition_time` >= '" . Dba::escape($value) . "' AND "; break; case 'update_lt': self::set_join('left','`song`','`song`.`album`','`album`.`id`'); $filter_sql = " `song`.`update_time` <= '" . Dba::escape($value) . "' AND "; break; case 'update_gt': self::set_join('left','`song`','`song`.`album`','`album`.`id`'); $filter_sql = " `song`.`update_time` >= '" . Dba::escape($value) . "' AND "; break; default: // Rien a faire break; } break; case 'artist': switch($filter) { case 'exact_match': $filter_sql = " `artist`.`name` = '" . Dba::escape($value) . "' AND "; break; 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; case 'add_lt': self::set_join('left','`song`','`song`.`artist`','`artist`.`id`'); $filter_sql = " `song`.`addition_time` <= '" . Dba::escape($value) . "' AND "; break; case 'add_gt': self::set_join('left','`song`','`song`.`artist`','`artist`.`id`'); $filter_sql = " `song`.`addition_time` >= '" . Dba::escape($value) . "' AND "; break; case 'update_lt': self::set_join('left','`song`','`song`.`artist`','`artist`.`id`'); $filter_sql = " `song`.`update_time` <= '" . Dba::escape($value) . "' AND "; break; case 'update_gt': self::set_join('left','`song`','`song`.`artist`','`artist`.`id`'); $filter_sql = " `song`.`update_time` >= '" . Dba::escape($value) . "' AND "; break; default: // Rien a faire break; } // end filter break; case '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 break; case '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 break; case 'tag': switch ($filter) { case 'alpha_match': $filter_sql = " `tag`.`name` LIKE '%" . Dba::escape($value) . "%' AND "; break; case 'exact_match': $filter_sql = " `tag`.`name` = '" . Dba::escape($value) . "' AND "; break; default: // Rien a faire break; } // end filter break; case 'video': switch ($filter) { case 'alpha_match': $filter_sql = " `video`.`title` LIKE '%" . Dba::escape($value) . "%' AND "; break; case 'starts_with': $filter_sql = " `video`.`title` LIKE '" . Dba::escape($value) . "%' AND "; break; default: // Rien a faire break; } // end filter break; } // end switch on type 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; case 'video': switch ($field) { case 'title': $sql = "`video`.`title`"; break; case 'resolution': $sql = "`video`.`resolution_x`"; break; case 'length': $sql = "`video`.`time`"; break; case 'codec': $sql = "`video`.`video_codec`"; break; } // end switch on field break; default: // Rien a faire break; } // end switch if ($sql) { $sql_sort = "$sql $order,"; } return $sql_sort; } // sql_sort /** * 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()) { $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) or !is_array($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(); $order_sql = " ORDER BY "; foreach (self::$_state['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() . $where_sql . $order_sql; } // if not simple $db_results = Dba::read($sql); while ($row = Dba::fetch_assoc($db_results)) { $results[] = $row['id']; } self::save_objects($results); return true; } // resort_objects /** * 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()) { $sid = Dba::escape(session_id()); $data = Dba::escape(serialize($object_ids)); $type = Dba::escape(self::$type); $sql = "REPLACE INTO `tmp_browse` SET `data`='$data', `sid`='$sid',`type`='$type'"; $db_results = Dba::write($sql); self::$total_objects = count($object_ids); } // save it return true; } // save_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'; self::$_state = &$_SESSION['browse']; } // _auto_init /** * get_state * This is a debug only function */ public static function get_state() { return self::$_state; } // get_state } // query