/**
* お気に入りのメンバーIDリスト取得
*
* @param int $c_member_id
* @param bool $block アクセスブロックしているメンバーを除外するかどうか
* @return array お気に入りのメンバーID配列
*/
function db_bookmark_c_member_id_list($c_member_id_from, $block = false)
{
$sql = 'SELECT c_member_id_to FROM c_bookmark WHERE c_member_id_from = ? ORDER BY c_bookmark_id';
$params = array(intval($c_member_id_from));
$list = db_get_col($sql, $params);
if ($block) {
$block_list = db_member_access_block_list4c_member_id_to($c_member_id_from);
$list = array_diff($list, $block_list);
}
return $list;
}
function _p_fh_diary_c_diary_comment_id_list4c_member_id($c_member_id, $is_friend, $type)
{
if ($type == 'h') {
return p_fh_diary_c_diary_comment_id_list4c_member_id($c_member_id);
}
$sql = "SELECT cdc.c_diary_comment_id FROM c_diary as cd,c_diary_comment as cdc" . " WHERE cd.c_member_id = ?" . " AND cd.c_diary_id = cdc.c_diary_id";
if ($is_friend) {
$sql .= " AND cd.public_flag <> 'private'";
} else {
$sql .= " AND cd.public_flag = 'public'";
}
$params = array(intval($c_member_id));
return db_get_col($sql, $params);
}
function db_schedule_event4c_member_id($year, $month, $c_member_id)
{
$sql = 'SELECT c_commu_id FROM c_commu_member WHERE c_member_id = ?';
$params = array(intval($c_member_id));
$ids = db_get_col($sql, $params);
$ids = implode(', ', $ids);
if (!$ids) {
return array();
}
$sql = 'SELECT * FROM c_commu_topic WHERE c_commu_id IN (' . $ids . ')' . ' AND event_flag = 1 AND open_date >= ? AND open_date <= ?';
$max_day = date("t", mktime(0, 0, 0, intval($month), 1, intval($year)));
$params = array(sprintf('%04d-%02d', intval($year), intval($month)) . '-01', sprintf('%04d-%02d', intval($year), intval($month)) . '-' . $max_day);
$list = db_get_all($sql, $params);
$res = array();
foreach ($list as $item) {
$item['is_join'] = db_commu_is_c_event_member_2($item['c_commu_topic_id'], $c_member_id);
$day = date('j', strtotime($item['open_date']));
$res[$day][] = $item;
}
return $res;
}
/**
* 指定された年月にメッセージを送受信した日のリストを返す
*/
function db_message_is_message_list4date($u, $year, $month, $box)
{
include_once 'Date/Calc.php';
if ($box == 'inbox' || !$box) {
$where = "c_member_id_to = ?" . " AND is_deleted_to = 0" . " AND is_send = 1";
} elseif ($box == 'outbox') {
$where = "c_member_id_from = ?" . " AND is_deleted_from = 0" . " AND is_send = 1";
} else {
return null;
}
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_message" . " WHERE {$where}" . ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
} else {
$sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' . " WHERE {$where}" . ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
}
$date_format = '%Y-%m-%d 00:00:00';
$thismonth = Date_Calc::beginOfMonth($month, $year, $date_format);
$nextmonth = Date_Calc::beginOfNextMonth(0, $month, $year, $date_format);
$params = array(intval($u), $thismonth, $nextmonth);
return db_get_col($sql, $params);
}
function get_id_list($input)
{
$data_type = $input['data_type'];
$c_member_id = $input['member']['c_member_id'];
$start_datetime = $input['period']['start_datetime'];
$end_datetime = $input['period']['end_datetime'];
$c_commu_category_id = $input['commu']['category_id'];
$c_commu_id = $input['commu']['commu_id'];
$params = array();
//メンバーで絞る場合
if ($input['member']['select'] > 0) {
$where .= " and a.c_member_id = ?";
array_push($params, intval($c_member_id));
}
//期間指定だった場合(日付で絞る条件追加)
if ($input['period']['is_period'] > 0) {
$where .= " and a.r_datetime >= ? and a.r_datetime < ?";
array_push($params, $start_datetime);
array_push($params, $end_datetime);
}
//コミュ指定の場合
if ($data_type > 1) {
if ($input['commu']['is_commu'] == 1) {
$where .= ' and cm.c_commu_category_id = ?';
array_push($params, $c_commu_category_id);
} else {
if ($input['commu']['is_commu'] == 2) {
$where .= ' and a.c_commu_id = ?';
array_push($params, $c_commu_id);
}
}
if ($data_type > 3) {
$where .= " and a.event_flag = 1";
} else {
$where .= " and a.event_flag = 0";
}
}
if ($where) {
$where = ereg_replace('^ and', ' WHERE', $where);
}
//本体データの取得(本体のIDのみ）
// 日記の場合
if ($data_type <= 1) {
$sql = "SELECT a.c_diary_id FROM c_diary as a" . $where . " ORDER BY c_diary_id";
// トピック/イベントの場合
} else {
$sql = "SELECT a.c_commu_topic_id FROM c_commu_topic as a,c_commu as cm " . $where . " and a.c_commu_id = cm.c_commu_id ORDER BY a.c_commu_id,a.c_commu_topic_id";
}
$id_list = db_get_col($sql, $params);
return $id_list;
}
/**
* OpenPNE2.5以前のBIZスケジュールデータを、OpenPNE2.6以降向けに変換するスクリプト
*/
set_time_limit(0);
//OpenPNEからconfig.phpの情報を読み込む
define('OPENPNE_DIR', realpath('../../../'));
require_once OPENPNE_DIR . '/config.php';
require_once OPENPNE_WEBAPP_DIR . '/init.inc';
//biz_schedule_idのリストを取得
$sql = 'SELECT biz_schedule_id FROM biz_schedule';
$biz_schedule_id_list = db_get_col($sql);
$result = array();
foreach ($biz_schedule_id_list as $biz_schedule_id) {
//スケジュールの内容を得る
$sql = 'SELECT * FROM biz_schedule WHERE biz_schedule_id = ?';
$schedule = db_get_row($sql, array($biz_schedule_id));
array_shift($schedule);
//スケジュールの参加者を得る
$sql = 'SELECT c_member_id FROM biz_schedule_member WHERE biz_schedule_id = ?';
$schedule_member_list = db_get_col($sql, array($biz_schedule_id));
//新規スケジュール挿入
foreach ($schedule_member_list as $c_member_id) {
$schedule['c_member_id'] = $c_member_id;
db_insert('biz_schedule', $schedule);
}
//既存のスケジュールの削除
if (!empty($schedule_member_list)) {
$sql = 'DELETE FROM biz_schedule WHERE biz_schedule_id = ?';
db_query($sql, array($biz_schedule_id));
}
}
function biz_insertSchedule($title, $c_member_id, $begin_date, $finish_date, $begin_time = null, $finish_time = null, $value = '', $rep_type, $first_id = 0, $biz_group_id = 0, $public_flag = "public", $join_members = array())
{
//登録値のセット、チェック
if (!$value) {
$value = '';
}
if (!$rep_type) {
$rep_type = 0;
}
// 書き込み内に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
$title = db_ktai_delete_url_session_parameter($title);
$value = db_ktai_delete_url_session_parameter($value);
//biz_scheduleにデータを追加する
$data = array('title' => $title, 'c_member_id' => intval($c_member_id), 'begin_date' => $begin_date, 'finish_date' => $finish_date, 'begin_time' => $begin_time, 'finish_time' => $finish_time, 'value' => $value, 'rep_type' => $rep_type, 'rep_first' => $first_id, 'biz_group_id' => $biz_group_id, 'public_flag' => $public_flag);
$new_schedule_id = db_insert('biz_schedule', $data);
// 参加者が指定されていない場合は、c_memberすべてが予定の参加者に
if (empty($join_members)) {
$sql = 'SELECT c_member_id FROM c_member';
$join_members = db_get_col($sql);
}
foreach ($join_members as $value) {
$data = array('c_member_id' => $value, 'biz_schedule_id' => $new_schedule_id, 'is_read' => 0);
db_insert('biz_schedule_member', $data);
}
}
/**
* Update c_member `ashiato_count_log` and delete c_ashiato rows
*
* @param int $limit
*/
function db_ashiato_update_log($limit = 30)
{
$sql = 'SELECT c_member_id FROM c_member';
$c_member_id_list = db_get_col($sql);
foreach ($c_member_id_list as $c_member_id) {
$disp = db_ashiato_c_ashiato_list4c_member_id($c_member_id, $limit);
if (!$disp) {
continue;
}
$oldest_row = array_pop($disp);
$yesterday = date('Y-m-d 00:00:00', strtotime('-1 day'));
$cutline = min($oldest_row['r_datetime'], $yesterday);
// delete c_ashiato rows
$sql = 'DELETE FROM c_ashiato WHERE c_member_id_to = ? AND r_datetime < ?';
$params = array(intval($c_member_id), $cutline);
db_query($sql, $params);
$affected_rows = db_affected_rows();
// update c_member `ashiato_count_log`
if ($affected_rows > 0) {
$sql = 'UPDATE c_member SET ashiato_count_log = ashiato_count_log + ?' . ' WHERE c_member_id = ?';
$params = array(intval($affected_rows), intval($c_member_id));
db_query($sql, $params);
}
}
}
/**
* 友達のメンバーで、新着表示可否のIDリスト取得
*
* @param int $c_member_id
* @param bool $is_display_friend_home = 1:新着表示対象 / 0:新着表示対象外
* @return array 友達のメンバーID配列
*/
function db_friend_is_display_friend_home_list($c_member_id, $is_display_friend_home = 1)
{
$sql = 'SELECT c_member_id_to ';
$sql .= 'FROM c_friend ';
$sql .= 'WHERE c_member_id_from = ? ';
$sql .= 'AND is_display_friend_home = ? ';
$sql .= 'ORDER BY c_friend_id';
$params = array(intval($c_member_id), intval($is_display_friend_home));
$friends = db_get_col($sql, $params);
return $friends;
}
/**
* カレンダーの祝日を取得する
*/
function db_c_holiday_list4date($m, $d)
{
$sql = 'SELECT name FROM c_holiday WHERE month = ? AND day = ?';
$params = array(intval($m), intval($d));
return db_get_col($sql, $params);
}
/**
* 指定された年月に日記を書いている日のリストを返す
*/
function p_h_diary_is_diary_written_list4date($year, $month, $c_member_id, $u = null)
{
include_once 'Date/Calc.php';
$pf_cond = db_diary_public_flag_condition($c_member_id, $u);
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_diary" . " WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?" . $pf_cond;
} else {
$sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_diary' . ' WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?' . $pf_cond;
}
$date_format = '%Y-%m-%d 00:00:00';
$thismonth = Date_Calc::beginOfMonth($month, $year, $date_format);
$nextmonth = Date_Calc::beginOfNextMonth(0, $month, $year, $date_format);
$params = array(intval($c_member_id), $thismonth, $nextmonth);
return db_get_col($sql, $params);
}
/**
* 画像リスト取得
*/
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_update_c_point_clear($c_member_profile_value)
{
$sql = 'SELECT c_profile_id, public_flag_default FROM c_profile where name = ?';
$params = array('PNE_POINT');
$c_profile = db_get_row($sql, $params);
$c_profile_id = $c_profile['c_profile_id'];
$public_flag_default = $c_profile['public_flag_default'];
$data = array('value' => $c_member_profile_value);
$where = array('c_profile_id' => intval($c_profile_id));
db_update('c_member_profile', $data, $where);
// すべてのメンバーのIDを取得
$sql = 'SELECT c_member_id FROM c_member';
$all_member_ids = db_get_col($sql);
// ポイント情報があるメンバーのIDを取得
$sql = 'SELECT c_member_id FROM c_member_profile WHERE c_profile_id = ?';
$params = array($c_profile_id);
$has_point_member_ids = db_get_col($sql, $params);
// 差分から、ポイント情報がないメンバーのIDを取得
$c_member_ids = array_diff($all_member_ids, $has_point_member_ids);
// プロフィールにポイント情報が存在しない場合はレコードを追加
foreach ($c_member_ids as $c_member_id) {
$data = array('c_member_id' => intval($c_member_id), 'c_profile_id' => intval($c_profile_id), 'c_profile_option_id' => 0, 'value' => $c_member_profile_value, 'public_flag' => $public_flag_default);
db_insert('c_member_profile', $data);
}
}
/**
* メンバーの共通参加コミュニティ数を取得
*
* @param int $target_c_member_id , $u
* @return int 参加コミュニティ数
*/
function db_common_commu_common_commu_id4c_member_id($target_c_member_id, $u)
{
// 相手のコミュニティリスト
$sql = 'SELECT c_commu_id FROM c_commu_member ' . ' WHERE c_member_id = ?' . ' ORDER BY c_commu_id DESC ';
$params = array(intval($target_c_member_id));
$f_commu_id_list = db_get_col($sql, $params);
if (is_null($f_commu_id_list)) {
return null;
}
// 自分のコミュニティリスト
$sql = 'SELECT c_commu_id FROM c_commu_member ' . ' WHERE c_member_id = ?' . ' ORDER BY c_commu_id DESC ';
$params = array(intval($u));
$h_commu_id_list = db_get_col($sql, $params);
if (is_null($h_commu_id_list)) {
return null;
}
//共通コミュニティリスト
$common_commu_id_list = array_intersect($f_commu_id_list, $h_commu_id_list);
if (is_null($common_commu_id_list)) {
return null;
}
return $common_commu_id_list;
}
function db_member_filter_c_access_block_id($c_member_id, $c_member_id_block)
{
$c_member_id_block = array_unique(array_map('intval', $c_member_id_block));
if (!$c_member_id_block) {
return array();
}
$ids = implode(',', $c_member_id_block);
$sql = 'SELECT c_member_id FROM c_member WHERE (c_member_id IN (' . $ids . ')) AND (c_member_id <> ?) ';
return db_get_col($sql, array($c_member_id));
}
/**
* メンバーのアルバムを削除する
*
* @param int $c_member_id
*/
function db_album_delete4c_member_id($c_member_id)
{
$sql = 'SELECT c_album_id FROM c_album WHERE c_member_id = ?';
$params = array(intval($c_member_id));
$c_album_id_list = db_get_col($sql, $params, 'main');
foreach ($c_album_id_list as $c_album_id) {
db_album_delete_c_album($c_album_id);
}
}
