public static function getMediaForGame($pack) { $sql_medias = dbconnection::queryArray("SELECT * FROM media WHERE (game_id = '{$pack->game_id}' OR (game_id = 0 AND user_id = 0))"); $medias = array(); for ($i = 0; $i < count($sql_medias); $i++) { if ($ob = media::mediaObjectFromSQL($sql_medias[$i])) { $medias[] = $ob; } } return new return_package(0, $medias); }
public static function searchNotes($pack) { if (isset($pack->auth)) { $pack->auth->game_id = $pack->game_id; $pack->auth->permission = "read_write"; $auth_user = users::authenticateUser($pack->auth); $auth_editor = editors::authenticateGameEditor($pack->auth); } else { $auth_user = false; $auth_editor = false; } $game_id = intval($pack->game_id); $search_terms = isset($pack->search_terms) ? $pack->search_terms : array(); $note_count = intval($pack->note_count); $user_id = $auth_user ? intval($pack->auth->user_id) : 0; $order_by = $pack->order_by; $filter_by = $pack->filter_by; $tag_ids = isset($pack->tag_ids) ? array_map('intval', $pack->tag_ids) : array(); $note_id = intval($pack->note_id); $lines = array(); $selects = array("notes.*", "users.user_name", "users.display_name", "object_tags.tag_id", "tags.tag", "COUNT(all_likes.note_like_id) AS note_likes", "COUNT(my_likes.note_like_id) > 0 AS player_liked", "triggers.latitude", "triggers.longitude", "media.name AS media_name", "media.file_name AS media_file_name", "media.file_folder AS media_file_folder"); $lines[] = "SELECT " . implode(", ", $selects); $lines[] = "FROM notes"; $lines[] = "JOIN users ON notes.user_id = users.user_id"; if ($order_by === 'popular' || !empty($search_terms)) { $lines[] = "LEFT JOIN note_comments ON notes.note_id = note_comments.note_id"; } $lines[] = "LEFT JOIN object_tags ON object_tags.object_type = 'NOTE' AND notes.note_id = object_tags.object_id"; $lines[] = "LEFT JOIN tags ON object_tags.tag_id = tags.tag_id"; $lines[] = "LEFT JOIN note_likes AS all_likes ON notes.note_id = all_likes.note_id"; $lines[] = "LEFT JOIN note_likes AS my_likes ON notes.note_id = my_likes.note_id AND my_likes.user_id = '{$user_id}'"; $lines[] = "LEFT JOIN instances ON instances.object_type = 'NOTE' AND notes.note_id = instances.object_id"; $lines[] = "LEFT JOIN triggers ON triggers.instance_id = instances.instance_id AND triggers.type = 'LOCATION'"; $lines[] = "LEFT JOIN media ON media.media_id = notes.media_id"; $lines[] = "WHERE 1=1"; $lines[] = "AND notes.game_id = '{$game_id}'"; $searchables = array('notes.name', 'notes.description', 'users.user_name', 'users.display_name', 'note_comments.description'); foreach ($search_terms as $term) { $matches = array(); $term = addslashes($term); foreach ($searchables as $key) { $matches[] = "({$key} LIKE '%{$term}%')"; } $lines[] = 'AND (' . implode(' OR ', $matches) . ')'; } if ($user_id && $filter_by === 'mine') { $lines[] = "AND notes.user_id = '{$user_id}'"; } if (!empty($tag_ids)) { $tag_list = implode(',', $tag_ids); $lines[] = "AND object_tags.tag_id IN ({$tag_list})"; } if ($note_id) { $lines[] = "AND notes.note_id = '{$note_id}'"; } if (!$auth_editor) { if ($auth_user) { $lines[] = "AND (notes.published != 'PENDING' OR notes.user_id = '{$user_id}')"; } else { $lines[] = "AND notes.published != 'PENDING'"; } } $lines[] = "GROUP BY notes.note_id"; if ($order_by === 'popular') { $lines[] = "ORDER BY (COUNT(all_likes.note_id) + COUNT(note_comments.note_id)) DESC"; } else { if ($order_by === 'recent') { $lines[] = "ORDER BY notes.created DESC"; } } if ($note_count) { $lines[] = "LIMIT {$note_count}"; } $query = implode(' ', $lines); $sql_notes = dbconnection::queryArray($query); $notes = array(); for ($i = 0; $i < count($sql_notes); $i++) { $ob = notes::noteObjectFromSQL($sql_notes[$i]); if (!$ob) { continue; } foreach (array('tag_id', 'note_likes', 'tag', 'latitude', 'longitude', 'user_name', 'display_name', 'player_liked') as $field) { $ob->{$field} = $sql_notes[$i]->{$field}; } $sql_media = $sql_notes[$i]; $sql_media->name = $sql_media->media_name; $sql_media->file_name = $sql_media->media_file_name; $sql_media->file_folder = $sql_media->media_file_folder; $ob->media = new return_package(0, media::mediaObjectFromSQL($sql_media)); $ob->comments = note_comments::getNoteCommentsForNote((object) array('game_id' => $ob->game_id, 'note_id' => $ob->note_id)); $notes[] = $ob; } return new return_package(0, $notes); }