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);
}