function db_ranking_c_commu_topic_comment_ranking($limit = 10)
{
$today = date('Y-m-d 00:00:00');
$yesterday = date('Y-m-d 00:00:00', strtotime('-1 day'));
$sql = 'SELECT c_commu_id, count(*) as count' . ' FROM c_commu_topic_comment' . ' WHERE r_datetime >= ? AND r_datetime < ?' . ' GROUP BY c_commu_id' . ' ORDER BY count DESC';
$params = array($yesterday, $today);
return db_get_all_limit($sql, 0, $limit, $params);
}
function _getUnreadMessageList()
{
$sql = 'SELECT * FROM c_message WHERE c_member_id_to = ? AND is_read = 0 AND is_send = 1 ORDER BY c_message_id DESC';
$list = db_get_all_limit($sql, 0, 5, array(1));
foreach ($list as $key => $value) {
$list[$key]['c_member'] = db_member_c_member4c_member_id_LIGHT($value['c_member_id_from']);
}
return $list;
}
function _getNewMemberList()
{
$sql = 'SELECT * FROM c_member WHERE c_member_id <> 1 ORDER BY c_member_id DESC';
$list = db_get_all_limit($sql, 0, 5);
foreach ($list as $key => $value) {
$list[$key]['c_member_invite'] = db_member_c_member4c_member_id_LIGHT($value['c_member_id_invite']);
}
return $list;
}
/**
* あしあとリスト取得
* 同一人物・同一日付のアクセスは最新の日時だけ
*
* @param int $c_member_id_to 訪問された人
* @param int $limit
* @return array あしあとリスト
*/
function db_ashiato_c_ashiato_list4c_member_id($c_member_id_to, $count)
{
$sql = 'SELECT DISTINCT r_date FROM c_ashiato WHERE c_member_id_to = ? ORDER BY r_date DESC';
$params = array(intval($c_member_id_to));
$days = db_get_col_limit($sql, 0, $count, $params);
$sql = 'SELECT DISTINCT c_member_id_from, MAX(r_datetime) AS r_datetime' . ' FROM c_ashiato WHERE r_date = ? AND c_member_id_to = ?' . ' GROUP BY c_member_id_from ORDER BY r_datetime DESC';
$result = array();
foreach ($days as $day) {
$params = array(strval($day), intval($c_member_id_to));
$day_result = db_get_all_limit($sql, 0, $count, $params);
$result = array_merge($result, $day_result);
$count -= count($day_result);
if ($count <= 0) {
break;
}
}
foreach ($result as $key => $value) {
$c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_from']);
$result[$key]['nickname'] = $c_member['nickname'];
}
return $result;
}
function db_rss_c_rss_cache_list4c_member_id($c_member_id, $limit = 5)
{
$sql = 'SELECT * FROM c_rss_cache WHERE c_member_id = ? ORDER BY r_datetime DESC';
$params = array(intval($c_member_id));
return db_get_all_limit($sql, 0, $limit, $params);
}
function db_review_new_commu_review4c_commu_id($c_commu_id, $limit)
{
$sql = "SELECT ccr.c_review_id, cr.title, ccr.r_datetime" . " FROM c_commu_review AS ccr , c_review AS cr" . " WHERE ccr.c_review_id = cr.c_review_id" . " AND ccr.c_commu_id = ?" . " ORDER BY ccr.r_datetime DESC";
$params = array(intval($c_commu_id));
return db_get_all_limit($sql, 0, $limit, $params);
}
/**
* SNS全体の最新日記リスト取得
* 日記公開範囲を考慮
*
* @param int $limit
* @return array_of_array (c_diary.*, nickname)
*/
function p_h_home_c_diary_all_list($limit)
{
$sql = 'SELECT * FROM c_diary WHERE public_flag = \'public\'' . ' ORDER BY c_diary.r_datetime DESC';
$c_diary_list_all = db_get_all_limit($sql, 0, $limit);
foreach ($c_diary_list_all as $key => $value) {
$c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
$c_diary_list_all[$key]['nickname'] = $c_member['nickname'];
$c_diary_list_all[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
}
return $c_diary_list_all;
}
function p_h_home_c_topic_all_list($limit)
{
$sql = 'SELECT ct.*,c.name as c_commu_name, c.c_commu_id' . ' FROM c_commu_topic as ct' . ' INNER JOIN c_commu as c USING(c_commu_id)' . ' WHERE c.is_open = \'public\'' . ' ORDER BY u_datetime DESC';
$c_topic_all_list = db_get_all_limit($sql, 0, $limit);
foreach ($c_topic_all_list as $key => $value) {
$number = db_commu_get_max_c_commu_topic_comment_number4c_topic_id($value['c_commu_topic_id']);
$c_topic_all_list[$key]['number'] = $number;
$start_comment = db_commu_get_start_c_topic_comment($value['c_commu_topic_id']);
$c_topic_all_list[$key]['image_filename1'] = $start_comment['image_filename1'];
$c_topic_all_list[$key]['image_filename2'] = $start_comment['image_filename2'];
$c_topic_all_list[$key]['image_filename3'] = $start_comment['image_filename3'];
}
return $c_topic_all_list;
}
<?php
/**
* @copyright 2005-2008 OpenPNE Project
* @license http://www.php.net/license/3_01.txt PHP License 3.01
*/
require_once './config.inc.php';
require_once OPENPNE_WEBAPP_DIR . '/init.inc';
require_once OPENPNE_MODULES_DIR . '/admin/lib/db_admin.php';
if (OPENPNE_UNDER_MAINTENANCE) {
exit;
}
$sql = "SELECT * FROM c_message_queue order by c_message_queue_id";
$c_message_queue_list = db_get_all_limit($sql, 0, SEND_MESSAGE_QUEUE_NUM);
foreach ($c_message_queue_list as $c_message_queue) {
$c_message_id = db_message_insert_c_message($c_message_queue['c_member_id_from'], $c_message_queue['c_member_id_to'], $c_message_queue['subject'], $c_message_queue['body']);
do_admin_send_message_mail_send($c_message_queue['c_member_id_to'], $c_message_queue['c_member_id_from'], $c_message_queue['subject'], $c_message_queue['body'], $c_message_id);
do_admin_send_message_mail_send_ktai($c_message_queue['c_member_id_to'], $c_message_queue['c_member_id_from'], $c_message_queue['subject'], $c_message_queue['body']);
db_admin_delete_c_message_queue($c_message_queue['c_message_queue_id']);
}
/**
* お気に入りメンバーリスト
*/
function db_bookmark_member_list($c_member_id, $limit = 0)
{
static $is_recurred = false;
//再帰処理中かどうかの判定フラグ
if (!$is_recurred) {
//function cacheのために再帰処理を行う
$is_recurred = true;
$funcargs = func_get_args();
$result = pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_LONG, __FUNCTION__, $funcargs);
$is_recurred = false;
return $result;
}
$sql = 'SELECT c_member_id_to AS c_member_id FROM c_bookmark' . ' WHERE c_member_id_from = ?' . db_order_by_rand();
$params = array(intval($c_member_id));
if ($limit) {
$result = db_get_all_limit($sql, 0, intval($limit), $params);
} else {
$result = db_get_all($sql, $params);
}
foreach ($result as $key => $value) {
$result[$key] = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
$result[$key]['friend_count'] = db_friend_count_friends($value['c_member_id']);
}
return $result;
}
/**
* リンク集設定を取得する
*
* @param int $limit
*/
function db_portal_get_portal_link($limit = 10)
{
$sql = 'SELECT * FROM portal_link ORDER BY sort_order, portal_link_id ';
return db_get_all_limit($sql, 0, $limit);
}
/**
* 画像リスト取得
*/
function kanshi_get_image_list($updated_from, $updated_to, $offset, $limit)
{
$sql = " SELECT\n c_image_id as data_id,\n filename as title,\n r_datetime\n ";
$sql .= " FROM c_image";
$sql .= " WHERE 1";
$params = array();
if (!empty($updated_from)) {
$sql .= " AND r_datetime > ?";
$params[] = $updated_from;
}
if (!empty($updated_to)) {
$sql .= " AND r_datetime < ?";
$params[] = $updated_to;
}
$sql .= " ORDER BY r_datetime";
if (!empty($offset) || !empty($limit)) {
if (empty($offset)) {
$offset = 0;
}
if (empty($limit)) {
$limit = count(db_get_col($sql, $params)) - $offset;
}
$c_image_list = db_get_all_limit($sql, $offset, $limit, $params);
} else {
$c_image_list = db_get_all($sql, $params);
}
if (count($c_image_list) > 0) {
foreach ($c_image_list as $num => $c_image) {
$c_image_list[$num]['image_url'] = OPENPNE_URL . 'img.php?filename=' . $c_image['title'];
$temp = explode("_", $c_image['title']);
switch ($temp[0]) {
case 'm':
//メンバー画像
$c_image_list[$num]['creator'] = $temp[1];
$c_image_list[$num]['c_member'] = db_member_c_member4c_member_id($temp[1], true, true, 'private');
$c_image_list[$num]['refer_data_url'] = OPENPNE_URL . '&a=page_f_home&target_c_member_id=' . $temp[1];
break;
case 'c':
//コミュニティ画像
$id = db_commu_c_commu4c_commu_id($temp[1]);
$c_image_list[$num]['creator'] = $id['c_member_id_admin'];
$c_image_list[$num]['c_member'] = db_member_c_member4c_member_id($id['c_member_id_admin'], true, true, 'private');
$c_image_list[$num]['refer_watch_type_id'] = 3;
$c_image_list[$num]['refer_data_id'] = $temp[1];
$c_image_list[$num]['refer_data_url'] = OPENPNE_URL . '&a=page_c_home&target_c_commu_id=' . $temp[1];
break;
case 'd':
//日記画像
$id = db_diary_get_c_diary4id($temp[1]);
$c_image_list[$num]['creator'] = $id['c_member_id'];
$c_image_list[$num]['c_member'] = db_member_c_member4c_member_id($id['c_member_id'], true, true, 'private');
$c_image_list[$num]['refer_watch_type_id'] = 1;
$c_image_list[$num]['refer_data_id'] = $temp[1];
$c_image_list[$num]['refer_data_url'] = OPENPNE_URL . '&a=page_fh_diary&target_c_diary_id=' . $temp[1];
break;
case 'dc':
//日記コメント画像
$id = _do_c_diary_comment4c_diary_comment_id($temp[1]);
$c_image_list[$num]['creator'] = $id['c_member_id'];
$c_image_list[$num]['c_member'] = db_member_c_member4c_member_id($id['c_member_id'], true, true, 'private');
$c_image_list[$num]['refer_watch_type_id'] = 4;
$c_image_list[$num]['refer_data_id'] = $temp[1];
$c_image_list[$num]['refer_data_url'] = OPENPNE_URL . '&a=page_fh_diary&target_c_diary_id=' . $id['c_diary_id'];
break;
case 't':
//トピック、イベント画像
$id = db_commu_c_topic4c_commu_topic_id($temp[1]);
$c_image_list[$num]['creator'] = $id['c_member_id'];
$c_image_list[$num]['c_member'] = db_member_c_member4c_member_id($id['c_member_id'], true, true, 'private');
$c_image_list[$num]['refer_watch_type_id'] = 2;
$c_image_list[$num]['refer_data_id'] = $temp[1];
$c_image_list[$num]['refer_data_url'] = OPENPNE_URL . '&a=page_c_topic_detail&target_c_commu_topic_id=' . $temp[1];
break;
case 'tc':
//トピック、イベントコメント画像
$id = db_commu_c_commu_topic_comment4c_commu_topic_comment_id_2($temp[1]);
$c_image_list[$num]['creator'] = $id['c_member_id'];
$c_image_list[$num]['c_member'] = db_member_c_member4c_member_id($id['c_member_id'], true, true, 'private');
$c_image_list[$num]['refer_watch_type_id'] = 5;
$c_image_list[$num]['refer_data_id'] = $temp[1];
$c_image_list[$num]['param'] = '&a=page_c_topic_detail&target_c_commu_topic_id=' . $id['c_commu_topic_id'];
break;
}
}
}
return $c_image_list;
}
function db_admin_album_list4c_album_id($c_album_id, $page_size, $page)
{
$page = intval($page);
$page_size = intval($page_size);
$where = " WHERE c_album_id = ? ";
$params[] = intval($c_album_id);
$select = "SELECT c_album.*";
$from = " FROM c_album";
$order = " ORDER BY r_datetime DESC";
$sql = $select . $from . $where . $order;
$list = db_get_all_limit($sql, ($page - 1) * $page_size, $page_size, $params);
foreach ($list as $key => $value) {
$list[$key]['c_member'] = db_member_c_member_with_profile($value['c_member_id']);
$list[$key]['count_images'] = db_admin_album_count_c_album_image4c_album_id($value['c_album_id']);
}
$sql = "SELECT COUNT(*) " . $from . $where;
$total_num = db_get_one($sql, $params);
$total_page_num = ceil($total_num / $page_size);
$next = $page < $total_page_num;
$prev = $page > 1;
return array($list, $prev, $next, $total_num, $total_page_num);
}
function biz_getJoinGroupList($c_member_id, $page, $page_size)
{
$start = ($page - 1) * $page_size;
$sql = "SELECT biz_group.*" . " FROM biz_group_member , biz_group";
$sql .= " WHERE biz_group_member.c_member_id = ?";
$sql .= " AND biz_group.biz_group_id=biz_group_member.biz_group_id";
$sql .= " ORDER BY biz_group.biz_group_id DESC ";
$params = array(intval($c_member_id));
$biz_group_list = db_get_all_limit($sql, $start, $page_size, $params);
foreach ($biz_group_list as $key => $value) {
$biz_group_list[$key]['count_members'] = count(biz_getGroupMember($value['biz_group_id']));
}
$pager = array("total_num" => biz_getGroupCount($c_member_id), "disp_num" => count($biz_group_list), "start_num" => 0, "end_num" => 0, "total_page" => 0, "prev_page" => 0, "next_page" => 0);
if ($pager['disp_num'] > 0) {
$pager['start_num'] = $start + 1;
$pager['end_num'] = $pager['start_num'] + $pager['disp_num'] - 1;
}
if ($pager['total_num']) {
$pager['total_page'] = ceil($pager['total_num'] / $page_size);
if ($page < $pager['total_page']) {
$pager['next_page'] = max($page + 1, 1);
}
if ($page > 1) {
$pager['prev_page'] = min($page - 1, $pager['total_page']);
}
}
return array($biz_group_list, $pager);
}
/**
* 友達ランダム５０人
*/
function db_friend_ktai_c_friend_list_random4c_member_id($c_member_id)
{
$sql = "SELECT cf.c_member_id_to, cm.nickname";
$sql .= " FROM c_friend AS cf, c_member AS cm";
$sql .= " WHERE cf.c_member_id_from = ?" . " AND cf.c_member_id_to = cm.c_member_id";
$sql .= db_order_by_rand();
$params = array(intval($c_member_id));
return db_get_all_limit($sql, 0, 50, $params);
}
/**
* あしあとを付けたメンバの一覧をメンバプロフィールも含めて取得する
* @param $c_member_id_to あしあとを付けられたメンバーID
* $count 取得データの最大数
* $profile プロフィール項目
* @return 以下の形式の配列
* array ( array( prifile1 => value) [, array( profile2 => value), ...]) )
*/
function op_ashiato_list_c_member_profile($c_member_id_to, $count, $profile)
{
//「あしあと」を登録したメンバーIDを「あしあと」登録日の新しいものの順に取得する
$sql = 'SELECT DISTINCT r_date FROM c_ashiato WHERE c_member_id_to = ? ORDER BY r_date DESC';
$params = array(intval($c_member_id_to));
$days = db_get_col_limit($sql, 0, $count, $params);
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$sql = 'SELECT DISTINCT c_member_id_from, to_char(MAX(r_datetime),\'YYYY/MM/DD HH24:MI:SS\') AS r_datetime' . ' FROM c_ashiato WHERE r_date = ? AND c_member_id_to = ?' . ' GROUP BY c_member_id_from ORDER BY r_datetime DESC';
} else {
$sql = 'SELECT DISTINCT c_member_id_from, DATE_FORMAT(MAX(r_datetime),\'%Y/%m/%d %H:%i:%s\') AS r_datetime' . ' FROM c_ashiato WHERE r_date = ? AND c_member_id_to = ?' . ' GROUP BY c_member_id_from ORDER BY r_datetime DESC';
}
$member_list = array();
foreach ($days as $day) {
$params = array(strval($day), intval($c_member_id_to));
$day_result = db_get_all_limit($sql, 0, intval($count), $params);
$member_list = array_merge($member_list, $day_result);
$count -= count($day_result);
if ($count <= 0) {
break;
}
}
//プロフィールを取得
$list = array();
foreach ($member_list as $key => $value) {
//メンバ情報の取得
$c_member = OP::op_get_member_data_list($value['c_member_id_from']);
//デフォルト項目（メンバId、ニックネーム、登録日）
$list_mem = array('mid' => $value['c_member_id_from'], 'nickname' => $c_member['nickname'], 'datetime' => $value['r_datetime']);
//メンバのプロフィールを取得する
$prof_list = OP::op_get_member_profile_list($value['c_member_id_from']);
//取得指定のプロフィールを設定する
$prof_arry = split(',', $profile);
foreach ($prof_arry as $profile_name) {
$prof_key = strtolower(trim($profile_name));
$prof_val = '';
//プロフィール文字列と一致するプロフィールを探す
$is_search = FALSE;
foreach ($prof_list as $val) {
if ($val['name'] == $prof_key) {
$is_search = TRUE;
//プロフィールの公開区分をチェックする
if ($val['public_flag'] == 'public') {
$prof_val = $val['value'];
}
}
}
//プロフィールテーブルに無い項目？
if ($is_search == FALSE) {
if ($prof_key == 'birthday') {
if ($c_member['public_flag_birth_year'] == 'public') {
$prof_val = $c_member['birth_year'] . '/' . $c_member['birth_month'] . '/' . $c_member['birth_day'];
}
}
}
//項目を追加
$list_mem = array_merge($list_mem, array($prof_key => $prof_val));
}
//返却値リストに追加
array_push($list, $list_mem);
}
return $list;
}
/**
* フレンド最新アルバムリスト取得
* アルバム公開範囲を考慮
*
* @param int $c_member_id
* @param int $limit
* @return array
*/
function p_h_home_c_album_friend_list4c_member_id($c_member_id, $limit)
{
$friends = db_friend_c_member_id_list($c_member_id, true);
if (!$friends) {
return array();
}
$ids = implode(',', array_map('intval', $friends));
$sql = 'SELECT * FROM c_album' . ' WHERE c_member_id IN (' . $ids . ')' . ' AND public_flag <> \'private\'' . ' ORDER BY u_datetime DESC';
$c_album_friend_list = db_get_all_limit($sql, 0, $limit);
foreach ($c_album_friend_list as $key => $value) {
$c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
$c_album_friend_list[$key]['nickname'] = $c_member['nickname'];
}
return $c_album_friend_list;
}