/** * Returns the information of the N last posts, with attachments, surveys, events... * * @param array $params Associative array of paramaters. Possibles keys : * * official: Only official posts if true, only non-official posts if false, all posts if null * * show_private: Private posts include if true * * category_id: Category's id * * category_name: Category's name * * group_id: Group's id * * group_name: Group's name * * user_id: User's id * * id: ID of a post to get * * ids: List of IDs of post to get * * restricted: If true, limits the number of photos displayed * @param int $limit Number of posts to be returned * @param int $offset Number of posts to skip * @return array */ public function getPosts($params, $limit, $offset = 0) { $cache_entry = 'posts-' . $limit . '-' . $offset; foreach ($params as $key => $value) { if (isset($value)) { $cache_entry .= '-' . $key . ':' . (is_array($value) ? implode(',', $value) : $value); } } $posts = Cache::read($cache_entry); if ($posts !== false) { return $posts; } $where = array(); if (isset($params['group_id'])) { $where[] = 'p.group_id = ' . $params['group_id']; } if (isset($params['group_name'])) { $where[] = 'a.url_name = ' . DB::quote($params['group_name']); } if (isset($params['official'])) { $where[] = 'p.official = ' . ($params['official'] ? 1 : 0); } if (!isset($params['show_private']) || !$params['show_private']) { $where[] = 'p.private = 0'; } if (isset($params['category_id'])) { $where[] = 'c.id = "' . $params['category_id'] . '"'; } if (isset($params['category_name'])) { $where[] = 'c.url_name = ' . DB::quote($params['category_name']); } if (isset($params['user_id'])) { $where[] = 'p.user_id = ' . DB::quote($params['user_id']); } if (isset($params['ids']) && is_array($params['ids'])) { if (count($params['ids']) == 0) { return array(); } $where[] = 'p.id IN (' . implode(',', $params['ids']) . ')'; } if (isset($params['id']) && (is_int($params['id']) || ctype_digit($params['id']))) { $where[] = 'p.id = ' . $params['id']; } $posts = DB::select(' SELECT p.id, p.message, p.time, p.private, p.official, p.category_id,p.dislike, a.id AS group_id, a.name AS group_name, a.url_name AS group_url, u.username, s.student_number, s.firstname, s.lastname FROM posts p INNER JOIN categories c ON c.id = p.category_id INNER JOIN users u ON u.id = p.user_id ' . (isset($params['group_id']) || isset($params['group_name']) ? 'INNER' : 'LEFT') . ' JOIN groups a ON a.id = p.group_id LEFT JOIN students s ON s.username = u.username ' . (count($where) != 0 ? 'WHERE ' . implode(' AND ', $where) : '') . ' ORDER BY p.time DESC LIMIT ' . $offset . ', ' . $limit . ' '); if (count($posts) != 0) { if (isset($params['ids']) && is_array($params['ids'])) { Utils::arraySort($posts, 'id', $params['ids']); } $post_ids = array(); foreach ($posts as $post) { $post_ids[] = (int) $post['id']; } // Comments $comments = DB::select(' SELECT pc.post_id, pc.id, pc.message, pc.time, pc.attachment_id, pc.id, u.username, s.student_number, s.firstname, s.lastname FROM post_comments pc INNER JOIN users u ON u.id = pc.user_id INNER JOIN students s ON s.username = u.username WHERE pc.post_id IN (' . implode(',', $post_ids) . ') ' . (isset($params['restricted']) && $params['restricted'] ? 'AND pc.attachment_id IS NULL' : '') . ' ORDER BY pc.time ASC '); $comment_likes = DB::select(' SELECT pcl.comment_id, pcl.user_id as comment_like_user_id, u.username, s.student_number, s.firstname, s.lastname FROM post_comment_likes pcl INNER JOIN post_comments pc ON pcl.comment_id = pc.id INNER JOIN users u ON u.id = pcl.user_id INNER JOIN students s ON s.username = u.username WHERE pc.post_id IN (' . implode(',', $post_ids) . ') ' . (isset($params['restricted']) && $params['restricted'] ? 'AND pc.attachment_id IS NULL' : '') . ' '); $comments_by_post_id = array(); foreach ($comments as $comment) { $post_id = (int) $comment['post_id']; if (!isset($comments_by_post_id[$post_id])) { $comments_by_post_id[$post_id] = array(); } unset($comment['post_id']); $comment['avatar_url'] = Student_Model::getAvatarURL($comment['student_number'], true); /* Traitement des Likes */ foreach ($comment_likes as $comment_like) { // Si c'est le like en question : if ($comment['id'] == $comment_like['comment_id']) { $comment['like'][] = $comment_like; $comment['user_liked'][] = $comment_like['comment_like_user_id']; } } $comments_by_post_id[$post_id][] = $comment; } unset($comments); // Posts Likes $likes = DB::select(' SELECT li.post_id, li.id, li.user_id as like_user_id,li.attachment_id, u.username, s.firstname, s.lastname FROM post_likes li INNER JOIN users u ON u.id = li.user_id INNER JOIN students s ON s.username = u.username WHERE li.post_id IN (' . implode(',', $post_ids) . ') ' . (isset($params['restricted']) && $params['restricted'] ? 'AND li.attachment_id IS NULL' : '') . ' ORDER BY li.id DESC '); $likes_by_post_id = array(); $users_likes = array(); foreach ($likes as $like) { // Les trie par post_id => puis par $attachement_id $post_id = (int) $like['post_id']; // On Extrait le n° d'attachment if ($like['attachment_id'] == null) { $attachment_id = 0; } else { $attachment_id = (int) $like['attachment_id']; } if (empty($likes_by_post_id[$post_id][$attachment_id])) { $likes_by_post_id[$post_id][$attachment_id] = array(); } // Pour savoir qui a "Aimé" if (empty($users_likes[$post_id][$attachment_id])) { $users_likes[$post_id][$attachment_id] = array(); } $users_likes[$post_id][$attachment_id][] = $like['like_user_id']; unset($like['post_id']); unset($like['attachment_id']); $likes_by_post_id[$post_id][$attachment_id][] = $like; } unset($likes); // Posts Dislikes $dislikes = DB::select(' SELECT dli.post_id, dli.id, dli.user_id as dislike_user_id,dli.attachment_id, u.username, s.firstname, s.lastname FROM post_dislikes dli INNER JOIN users u ON u.id = dli.user_id INNER JOIN students s ON s.username = u.username WHERE dli.post_id IN (' . implode(',', $post_ids) . ') ' . (isset($params['restricted']) && $params['restricted'] ? 'AND dli.attachment_id IS NULL' : '') . ' ORDER BY dli.id DESC '); $dislikes_by_post_id = array(); $users_dislikes = array(); foreach ($dislikes as $dislike) { // Les trie par post_id => puis par $attachement_id $post_id = (int) $dislike['post_id']; // On Extrait le n° d'attachment if ($dislike['attachment_id'] == null) { $attachment_id = 0; } else { $attachment_id = (int) $dislike['attachment_id']; } if (empty($dislikes_by_post_id[$post_id][$attachment_id])) { $dislikes_by_post_id[$post_id][$attachment_id] = array(); } // Pour savoir qui a "Aimé" if (empty($users_dislikes[$post_id][$attachment_id])) { $users_dislikes[$post_id][$attachment_id] = array(); } $users_dislikes[$post_id][$attachment_id][] = $dislike['dislike_user_id']; unset($dislike['post_id']); unset($dislike['attachment_id']); $dislikes_by_post_id[$post_id][$attachment_id][] = $dislike; } unset($dislikes); // echo '<pre>'; // print_r($likes_by_post_id); // echo '</pre>'; // Attachments $attachments = DB::select(' SELECT post_id, id, name, ext FROM attachments WHERE post_id IN (' . implode(',', $post_ids) . ') ORDER BY ext, id ASC '); $attachments_by_post_id = array(); $nb_photos_by_post_id = array(); foreach ($attachments as $attachment) { $post_id = (int) $attachment['post_id']; // Limitation of the number of displayed photos if (in_array($attachment['ext'], array('jpg', 'png', 'gif'))) { if (!isset($nb_photos_by_post_id[$post_id])) { $nb_photos_by_post_id[$post_id] = 0; } $nb_photos_by_post_id[$post_id]++; if (isset($params['restricted']) && $params['restricted'] && $nb_photos_by_post_id[$post_id] > Config::PHOTOS_PER_POST) { continue; } } $attachment['url'] = self::getAttachedFileURL((int) $attachment['id'], $attachment['ext']); if (in_array($attachment['ext'], array('jpg', 'png', 'gif', 'flv', 'mp4'))) { $attachment['thumb'] = self::getAttachedFileURL((int) $attachment['id'], 'jpg', 'thumb'); } if (!isset($attachments_by_post_id[$post_id])) { $attachments_by_post_id[$post_id] = array(); } unset($attachment['post_id']); $attachments_by_post_id[$post_id][] = $attachment; } unset($attachments); // Events $events = DB::select(' SELECT post_id, id, title, date_start, date_end FROM events WHERE post_id IN (' . implode(',', $post_ids) . ') '); $events_by_post_id = array(); foreach ($events as $event) { $post_id = (int) $event['post_id']; unset($event['post_id']); $events_by_post_id[$post_id] = $event; } unset($events); // Surveys $surveys = DB::select(' SELECT post_id, id, question, multiple, date_end FROM surveys WHERE post_id IN (' . implode(',', $post_ids) . ') '); $surveys_by_post_id = array(); if (count($surveys) != 0) { $surveys_ids = array(); foreach ($surveys as $survey) { $surveys_ids[] = (int) $survey['id']; } $survey_answers = DB::select(' SELECT id, survey_id, answer, nb_votes, votes FROM survey_answers WHERE survey_id IN (' . implode(',', $surveys_ids) . ') ORDER BY id ASC '); $survey_answers_by_survey_id = array(); foreach ($survey_answers as $survey_answer) { $survey_id = (int) $survey_answer['survey_id']; unset($survey_answer['survey_id']); if (!isset($survey_answers_by_survey_id[$survey_id])) { $survey_answers_by_survey_id[$survey_id] = array(); } $survey_answers_by_survey_id[$survey_id][] = $survey_answer; } unset($survey_answers); foreach ($surveys as $survey) { $post_id = (int) $survey['post_id']; unset($survey['post_id']); $survey['answers'] = isset($survey_answers_by_survey_id[(int) $survey['id']]) ? $survey_answers_by_survey_id[(int) $survey['id']] : array(); $surveys_by_post_id[$post_id] = $survey; } unset($survey_answers_by_survey_id); } unset($surveys); foreach ($posts as &$post) { $post_id = (int) $post['id']; if (isset($comments_by_post_id[$post_id])) { $post['comments'] =& $comments_by_post_id[$post_id]; } if (isset($likes_by_post_id[$post_id])) { $post['likes']['data'] =& $likes_by_post_id[$post_id]; } if (isset($users_likes[$post_id])) { $post['likes']['users'] =& $users_likes[$post_id]; } if (isset($dislikes_by_post_id[$post_id])) { $post['dislikes']['data'] =& $dislikes_by_post_id[$post_id]; } if (isset($users_dislikes[$post_id])) { $post['dislikes']['users'] =& $users_dislikes[$post_id]; } if (isset($attachments_by_post_id[$post_id])) { $post['attachments'] =& $attachments_by_post_id[$post_id]; } if (isset($events_by_post_id[$post_id])) { $post['event'] =& $events_by_post_id[$post_id]; } if (isset($surveys_by_post_id[$post_id])) { $post['survey'] =& $surveys_by_post_id[$post_id]; } $post['attachments_nb_photos'] = isset($nb_photos_by_post_id[$post_id]) ? $nb_photos_by_post_id[$post_id] : 0; // Avatar if (isset($post['group_id']) && $post['official'] == '1') { $post['avatar_url'] = Group_Model::getAvatarURL((int) $post['group_id'], true); } else { if (isset($post['student_number'])) { $post['avatar_url'] = Student_Model::getAvatarURL((int) $post['student_number'], true); } } } } // Write the cache Cache::write($cache_entry, $posts, 20 * 60); $cache_list = Cache::read('posts-cachelist'); if (!$cache_list) { $cache_list = array(); } if (!in_array($cache_entry, $cache_list)) { $cache_list[] = $cache_entry; } Cache::write('posts-cachelist', $cache_list, 20 * 60); return $posts; }