PageRenderTime 61ms CodeModel.GetById 15ms app.highlight 34ms RepoModel.GetById 1ms app.codeStats 1ms

/webapp/lib/db/diary.php

https://github.com/openpne/OpenPNE2
PHP | 1337 lines | 885 code | 163 blank | 289 comment | 104 complexity | 99c87f6e5b5df597b31bbd0f03ad09bf MD5 | raw file
   1<?php
   2/**
   3 * @copyright 2005-2008 OpenPNE Project
   4 * @license   http://www.php.net/license/3_01.txt PHP License 3.01
   5 */
   6
   7/**
   8 * 指定メンバーの日記カテゴリリストを取得する
   9 *
  10 * @param int $c_member_id
  11 * @return array
  12 */
  13function db_diary_category_list4c_member_id($c_member_id)
  14{
  15    $sql = 'SELECT c_diary_category_id, category_name FROM c_diary_category '
  16         . 'WHERE c_member_id = ? ORDER BY c_diary_category_id';
  17    $result = db_get_all($sql, array(intval($c_member_id)), 'main');
  18
  19    return $result;
  20}
  21
  22/**
  23 * カテゴリIDからカテゴリ名を得る
  24 */
  25function db_diary_get_category_name4category_id($category_id)
  26{
  27    $sql = 'SELECT category_name FROM c_diary_category ' .
  28        'WHERE c_diary_category_id = ?';
  29    return db_get_one($sql, array($category_id));
  30}
  31
  32/**
  33 * 日記IDからカテゴリリストを得る
  34 *
  35 * @param int $c_diary_id
  36 * @return array
  37 */
  38function db_diary_category_list4c_diary_id($c_diary_id)
  39{
  40    $sql = 'SELECT c_diary_category_id FROM c_diary_category_diary WHERE c_diary_id = ?';
  41    $category_list = db_get_col($sql, array(intval($c_diary_id)));
  42    if (!$category_list) {
  43        return array();
  44    }
  45    $ids = implode(',', $category_list);
  46
  47    $sql = 'SELECT c_diary_category_id, category_name FROM c_diary_category'
  48         . ' WHERE c_diary_category_id IN ('.$ids.') ORDER BY c_diary_category_id';
  49    return db_get_all($sql);
  50}
  51
  52/**
  53 * カテゴリIDから日記を得る
  54 *
  55 * @param int $c_member_id
  56 * @param int $c_diary_category_id
  57 * @param int $u
  58 * @param int $page_size
  59 * @param int $page
  60 * @return array
  61 */
  62function db_diary_list4c_diary_category_id($c_member_id, $c_diary_category_id, $u = null, $page_size = 20, $page = 0)
  63{
  64    $sql = 'SELECT c_diary_id FROM c_diary_category_diary WHERE c_diary_category_id = ?';
  65    $diary_list = db_get_col($sql, array(intval($c_diary_category_id)));
  66    if (!$diary_list) {
  67        return array(array(), false, false, 0);
  68    }
  69    $ids = implode(',', $diary_list);
  70
  71    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
  72    $where = ' WHERE c_diary_id IN ('.$ids.') AND c_member_id = ? ' . $pf_cond . ' ORDER BY r_datetime DESC';
  73    $sql = 'SELECT * FROM c_diary' . $where;
  74    $params = array($c_member_id);
  75    $list = db_get_all_limit($sql, $page_size * ($page - 1), $page_size, $params);
  76
  77    foreach ($list as $key => $c_diary) {
  78        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($c_diary['c_diary_id']);
  79    }
  80
  81    $sql = 'SELECT COUNT(*) FROM c_diary' . $where;
  82    $total_num = db_get_one($sql, $params);
  83
  84    if ($total_num != 0) {
  85        $total_page_num =  ceil($total_num / $page_size);
  86        if ($page >= $total_page_num) {
  87            $next = false;
  88        } else {
  89            $next = true;
  90        }
  91        if ($page <= 1) {
  92            $prev = false;
  93        } else {
  94            $prev = true;
  95        }
  96    }
  97
  98    return array($list, $prev, $next, $total_num);
  99}
 100
 101/**
 102 * カテゴリを追加する
 103 *
 104 * @param int $c_member_id
 105 * @param int $category_name
 106 */
 107function db_diary_category_insert_category($c_member_id, $category_name)
 108{
 109    $data = array(
 110        'c_member_id' => intval($c_member_id),
 111        'category_name' => $category_name,
 112        'r_datetime' => db_now(),
 113    );
 114    return db_insert('c_diary_category', $data);
 115}
 116
 117/**
 118 * カテゴリを削除する
 119 *
 120 * @param int $c_diary_category_id
 121 */
 122function db_diary_category_delete_category($c_diary_category_id)
 123{
 124    $sql = 'DELETE FROM c_diary_category WHERE c_diary_category_id = ?';
 125    db_query($sql, array($c_diary_category_id));
 126}
 127/**
 128 * カテゴリと日記を関連づける
 129 *
 130 * @param int $c_diary_id
 131 * @param int $c_diary_category_id
 132 */
 133function db_diary_category_insert_c_diary_category_diary($c_diary_id, $c_diary_category_id)
 134{
 135    $data = array(
 136        'c_diary_id' => intval($c_diary_id),
 137        'c_diary_category_id' => intval($c_diary_category_id),
 138    );
 139    db_insert('c_diary_category_diary', $data);
 140}
 141
 142/**
 143 * カテゴリと日記の関連づけを削除する
 144 */
 145function db_diary_category_delete_c_diary_category_diary($c_diary_id)
 146{
 147    $diary_category_list = db_diary_category_list4c_diary_id($c_diary_id);
 148
 149    $sql = 'DELETE FROM c_diary_category_diary WHERE c_diary_id = ?';
 150    db_query($sql, array($c_diary_id));
 151
 152    foreach($diary_category_list as $value) {
 153        $sql = 'SELECT COUNT(*) FROM c_diary_category_diary'.
 154            ' WHERE c_diary_category_id = ?';
 155        $c_diary_category_id = $value['c_diary_category_id'];
 156        $is_diary = (bool)db_get_one($sql, array(intval($c_diary_category_id)), 'main');
 157        if (!$is_diary) {  //カテゴリに関連付いた日記が存在しない
 158            db_diary_category_delete_category($c_diary_category_id);
 159        }
 160    }
 161}
 162
 163/**
 164 * db_diary_public_flag_condition
 165 *
 166 * @param int $c_member_id target c_member_id
 167 * @param int $u viewer's c_member_id
 168 * @param string $force
 169 * @return string
 170 */
 171function db_diary_public_flag_condition($c_member_id, $u = null, $force = null)
 172{
 173    $pf_cond = '';
 174    if ($force) {
 175        switch ($force) {
 176        case 'friend':
 177            $pf_cond = " AND public_flag <> 'private'";
 178            break;
 179        case 'private':
 180            $pf_cond = " AND public_flag = 'public'";
 181            break;
 182        }
 183    } else {
 184        if (!is_null($u) && $c_member_id != $u) {
 185            $is_friend = db_friend_is_friend($c_member_id, $u);
 186            if ($is_friend) {
 187                $pf_cond = " AND public_flag <> 'private'";
 188            } else {
 189                $pf_cond = " AND public_flag = 'public'";
 190            }
 191        }
 192    }
 193    return $pf_cond;
 194}
 195
 196//// c_diary
 197
 198/**
 199 * 日記IDから日記を取得
 200 *
 201 * @param   int   $c_diary_id
 202 * @return  array
 203 */
 204function db_diary_get_c_diary4id($c_diary_id)
 205{
 206    $sql = 'SELECT * FROM c_diary WHERE c_diary_id = ?';
 207    $c_diary_list = db_get_row($sql, array(intval($c_diary_id)));
 208    return $c_diary_list;
 209}
 210
 211/**
 212 * メンバーIDから日記リストを最新順で取得
 213 *
 214 * @param int $c_member_id
 215 * @return array 日記リスト
 216 */
 217function db_diary_get_c_diary_list4c_member_id($target_c_member_id, $count = 10, $u = null, $force = null)
 218{
 219    static $is_recurred = false;  //再帰処理中かどうかの判定フラグ
 220
 221    if (!$is_recurred) {  //function cacheのために再帰処理を行う
 222        $is_recurred = true;
 223        $funcargs = func_get_args();
 224        $result = pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_FAST, __FUNCTION__, $funcargs);
 225        $is_recurred = false;
 226        return $result;
 227    }
 228
 229    $pf_cond = db_diary_public_flag_condition($target_c_member_id, $u, $force);
 230    $sql = 'SELECT * FROM c_diary WHERE c_member_id = ?' . $pf_cond .
 231           ' ORDER BY r_datetime DESC';
 232    $params = array(intval($target_c_member_id));
 233    $arr = db_get_all_limit($sql, 0, $count, $params);
 234    foreach ($arr as $key => $value) {
 235        $arr[$key]['comment_count'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 236    }
 237    return $arr;
 238}
 239
 240function p_common_is_active_c_diary_id($c_diary_id)
 241{
 242    $sql = 'SELECT c_diary_id FROM c_diary WHERE c_diary_id = ?';
 243    return (bool)db_get_one($sql, array(intval($c_diary_id)));
 244}
 245
 246/**
 247 * 指定した日記の前の日記IDを取得する
 248 *
 249 * @param int $c_member_id
 250 * @param int $c_diary_id
 251 */
 252function db_diary_c_diary_id_prev4c_diary_id($c_member_id, $c_diary_id, $u = null)
 253{
 254    $public_flag_condition = db_diary_public_flag_condition($c_member_id, $u);
 255    $sql = 'SELECT c_diary_id FROM c_diary WHERE c_member_id = ? AND c_diary_id < ?'
 256         . $public_flag_condition . ' ORDER BY c_diary_id DESC';
 257    return db_get_one($sql, array(intval($c_member_id), intval($c_diary_id)));
 258}
 259
 260/**
 261 * 指定した日記の次の日記IDを取得する
 262 *
 263 * @param int $c_member_id
 264 * @param int $c_diary_id
 265 */
 266function db_diary_c_diary_id_next4c_diary_id($c_member_id, $c_diary_id, $u = null)
 267{
 268    $public_flag_condition = db_diary_public_flag_condition($c_member_id, $u);
 269    $sql = 'SELECT c_diary_id FROM c_diary WHERE c_member_id = ? AND c_diary_id > ?'
 270         . $public_flag_condition . ' ORDER BY c_diary_id ASC';
 271    return db_get_one($sql, array(intval($c_member_id), intval($c_diary_id)));
 272}
 273
 274//// c_diary_comment
 275
 276/**
 277 * 日記のコメントリストを得る
 278 *
 279 * @param   int $c_diary_id
 280 * @param   int $limit
 281 * @return  array 日記コメント+ニックネーム
 282 */
 283function db_diary_get_c_diary_comment_list4c_diary_id($c_diary_id)
 284{
 285    $sql = 'SELECT c_member.nickname, c_diary_comment.*' .
 286        ' FROM c_diary_comment LEFT JOIN c_member USING (c_member_id)' .
 287        ' WHERE c_diary_id = ?' .
 288        ' ORDER BY c_diary_comment.r_datetime';
 289    return db_get_all($sql, array(intval($c_diary_id)));
 290}
 291
 292/**
 293 * 日記のコメントリスト(好きなID(複数))を取得
 294 *
 295 * @param   array $id_list
 296 * @param   int   $page
 297 * @param   int   $page_size
 298 * @param   bool  $desc  並び順を日時の新しい順にするかどうか
 299 * @return  日記コメント(+ニックネーム)リスト
 300 */
 301function db_diary_get_c_diary_comment_list4id_list($id_list, $page = 1, $page_size= -1, $desc = false)
 302{
 303    if (!count($id_list)) return array();
 304    if ($page_size < 0) {
 305        $page_size = count($id_list);
 306    }
 307    $e_id_list = implode(',', array_map('intval', (array)$id_list));
 308
 309    $sql = 'SELECT * FROM c_diary_comment' .
 310            ' WHERE c_diary_comment_id IN ('.$e_id_list.')';
 311    if ($desc) {
 312        $sql .= ' ORDER BY r_datetime DESC';
 313    } else {
 314        $sql .= ' ORDER BY r_datetime';
 315    }
 316    $c_diary_comment_list = db_get_all_page($sql, $page, $page_size);
 317    foreach ($c_diary_comment_list as $key => $value) {
 318        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
 319        $c_diary_comment_list[$key]['nickname'] = $c_member['nickname'];
 320    }
 321
 322    $sql = 'SELECT COUNT(*) FROM c_diary_comment' .
 323        ' WHERE c_diary_comment_id IN ('.$e_id_list.')';
 324    $total_num = db_get_one($sql);
 325
 326    if ($total_num != 0) {
 327        $total_page_num = ceil($total_num / $page_size);
 328        if ($page >= $total_page_num) {
 329            $next = false;
 330        } else {
 331            $next = true;
 332        }
 333
 334        if ($page <= 1) {
 335            $prev = false;
 336        } else {
 337            $prev = true;
 338        }
 339    }
 340
 341    return array($c_diary_comment_list, $prev , $next, $total_num);
 342}
 343
 344/**
 345 * 日記のコメント数を取得
 346 *
 347 * @param int $c_diary_id
 348 * @return int コメント数
 349 */
 350function db_diary_count_c_diary_comment4c_diary_id($c_diary_id)
 351{
 352    $sql = 'SELECT COUNT(*) FROM c_diary_comment WHERE c_diary_id = ?';
 353    return db_get_one($sql, array(intval($c_diary_id)));
 354}
 355
 356////
 357
 358/**
 359 * あるメンバーの日記リストを取得
 360 *
 361 * @param int $c_member_id target c_member_id
 362 * @param int $page_size
 363 * @param int $page
 364 * @param int $u viewer's c_member_id
 365 * @return array 日記リスト
 366 */
 367function p_fh_diary_list_diary_list4c_member_id($c_member_id, $page_size, $page, $u = null)
 368{
 369    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
 370    $sql = 'SELECT * FROM c_diary WHERE c_member_id = ?' . $pf_cond .
 371           ' ORDER BY r_datetime DESC';
 372    $params = array(intval($c_member_id));
 373    $list = db_get_all_page($sql, $page, $page_size, $params);
 374
 375    foreach ($list as $key => $c_diary) {
 376        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($c_diary['c_diary_id']);
 377    }
 378
 379    $sql = 'SELECT COUNT(*) FROM c_diary WHERE c_member_id = ?' . $pf_cond;
 380    $total_num = db_get_one($sql, $params);
 381
 382    if ($total_num != 0) {
 383        $total_page_num =  ceil($total_num / $page_size);
 384        if ($page >= $total_page_num) {
 385            $next = false;
 386        } else {
 387            $next = true;
 388        }
 389
 390        if ($page <= 1) {
 391            $prev = false;
 392        } else {
 393            $prev = true;
 394        }
 395    }
 396    return array($list, $prev, $next, $total_num);
 397}
 398
 399/**
 400 * フレンドの最新日記リスト
 401 */
 402function p_h_diary_list_friend_h_diary_list_friend4c_member_id($c_member_id, $page_size, $page)
 403{
 404    $friends = db_friend_c_member_id_list($c_member_id, true);
 405    if (!$friends) {
 406        return array(array(), false, false, 0);
 407    }
 408    $ids = implode(',', array_map('intval', $friends));
 409
 410    $hint = db_mysql_hint('USE INDEX (r_datetime_c_member_id, r_datetime)');
 411    $sql = 'SELECT * FROM c_diary' . $hint .
 412            ' WHERE c_member_id IN (' . $ids . ')' .
 413            ' AND public_flag <> \'private\'' .
 414            ' ORDER BY c_diary.r_datetime DESC';
 415
 416    $lst = db_get_all_page($sql, $page, $page_size);
 417
 418    foreach ($lst as $key=>$value) {
 419        $lst[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 420        $lst[$key]['c_member'] = db_member_c_member4c_member_id($value['c_member_id']);
 421    }
 422
 423    $sql = 'SELECT count(*) FROM c_diary' .
 424           ' WHERE c_member_id IN (' . $ids . ')' .
 425           ' AND public_flag <> \'private\'';
 426    $total_num = db_get_one($sql, $params);
 427
 428    if ($total_num != 0) {
 429        $total_page_num =  ceil($total_num / $page_size);
 430        if ($page >= $total_page_num) {
 431            $next = false;
 432        } else {
 433            $next = true;
 434        }
 435
 436        if ($page <= 1) {
 437            $prev = false;
 438        } else {
 439            $prev = true;
 440        }
 441    }
 442
 443    return array($lst, $prev, $next, $total_num);
 444}
 445
 446/**
 447 * 未読コメントのある日記の数を数える
 448 *
 449 * @param int $c_member_id
 450 * @return int 未読日記数
 451 */
 452function p_h_diary_count_c_diary_not_is_read4c_member_id($c_member_id)
 453{
 454    $sql = 'SELECT COUNT(*) FROM c_diary WHERE c_member_id = ? AND is_checked = 0';
 455    $params = array(intval($c_member_id));
 456    return db_get_one($sql, $params);
 457}
 458
 459/**
 460 * 未読コメントのある日記のうちもっとも古い日記IDを返す
 461 *
 462 * @param int $c_member_id
 463 * @return int c_diary_id
 464 */
 465function p_h_diary_c_diary_first_diary_read4c_member_id($c_member_id)
 466{
 467    $sql = 'SELECT c_diary_id FROM c_diary WHERE c_member_id = ? AND is_checked = 0 ORDER BY r_datetime';
 468    $params = array(intval($c_member_id));
 469    return db_get_one($sql, $params);
 470}
 471
 472/**
 473 * フレンド最新日記リスト取得
 474 * 日記公開範囲を考慮
 475 *
 476 * @param   int $c_member_id
 477 * @param   int $limit
 478 * @param   int $use_is_display_friend_home
 479 * @return  array_of_array  (c_diary.*, nickname)
 480 */
 481function p_h_home_c_diary_friend_list4c_member_id($c_member_id, $limit, $use_is_display_friend_home = false)
 482{
 483    static $is_recurred = false;  //再帰処理中かどうかの判定フラグ
 484
 485    if (!$is_recurred) {  //function cacheのために再帰処理を行う
 486        $is_recurred = true;
 487        $funcargs = func_get_args();
 488        $result = pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_FAST, __FUNCTION__, $funcargs);
 489        $is_recurred = false;
 490        return $result;
 491    }
 492
 493    $friends = db_friend_c_member_id_list($c_member_id, true);
 494    if (!$friends) {
 495        return array();
 496    }
 497    // 対象の最新書き込みのみを表示する場合
 498    if ($use_is_display_friend_home) {
 499        // 表示対象一覧を取得し、取得済みフレンド一覧とぶつける
 500        $friends = array_intersect($friends, db_friend_is_display_friend_home_list($c_member_id, $is_display_friend_home=1));
 501    }
 502    $ids = implode(',', array_map('intval', $friends));
 503
 504    $hint = db_mysql_hint('USE INDEX (r_datetime_c_member_id, r_datetime)');
 505    $sql = 'SELECT * FROM c_diary' . $hint .
 506            ' WHERE c_member_id IN (' . $ids . ')' .
 507            ' AND public_flag <> \'private\'' .
 508            ' ORDER BY c_diary.r_datetime DESC';
 509    $c_diary_friend_list = db_get_all_limit($sql, 0, $limit);
 510
 511    foreach ($c_diary_friend_list as $key => $value) {
 512        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
 513        $c_diary_friend_list[$key]['nickname'] = $c_member['nickname'];
 514        $c_diary_friend_list[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 515    }
 516    return $c_diary_friend_list;
 517}
 518
 519/**
 520 * 日記コメント記入履歴取得
 521 *
 522 * @param   int $c_member_id
 523 * @param   int $limit
 524 * @return  array_of_array  (c_diary.*, nickname)
 525 */
 526function p_h_home_c_diary_my_comment_list4c_member_id($c_member_id, $limit)
 527{
 528    static $is_recurred = false;  //再帰処理中かどうかの判定フラグ
 529
 530    if (!$is_recurred) {  //function cacheのために再帰処理を行う
 531        $is_recurred = true;
 532        $funcargs = func_get_args();
 533        $result = pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_FAST, __FUNCTION__, $funcargs);
 534        $is_recurred = false;
 535        return $result;
 536    }
 537
 538    $sql = 'SELECT cdcl.c_diary_id, cdcl.r_datetime AS maxdate, cd.*'
 539         . ' FROM c_diary_comment_log AS cdcl INNER JOIN c_diary AS cd USING (c_diary_id) '
 540         . ' WHERE cdcl.c_member_id = ?'
 541         . ' AND (cd.public_flag = \'public\'';
 542
 543    $friends = db_friend_c_member_id_list($c_member_id, true);
 544    $friend_ids = implode(',', array_map('intval', $friends));
 545    if ($friend_ids) {
 546         $sql .= ' OR (cd.public_flag = \'friend\' AND cd.c_member_id IN (' . $friend_ids . '))';
 547    }
 548
 549    $sql .= ') ORDER BY maxdate DESC';
 550    $params = array(intval($c_member_id));
 551    $list = db_get_all_limit($sql, 0, $limit, $params);
 552
 553    foreach ($list as $key => $value) {
 554        $list[$key] += db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
 555        $list[$key]['r_datetime'] = $value['maxdate'];
 556        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 557    }
 558
 559    return $list;
 560}
 561
 562function p_h_diary_comment_list_c_diary_my_comment_list4c_member_id($c_member_id, $page, $page_size)
 563{
 564    $select = 'SELECT cdcl.c_diary_id, cdcl.r_datetime AS maxdate, cd.*';
 565    $from = ' FROM c_diary_comment_log AS cdcl INNER JOIN c_diary AS cd USING (c_diary_id) ';
 566    $where = ' WHERE cdcl.c_member_id = ? AND (cd.public_flag = \'public\'';
 567
 568
 569    $friends = db_friend_c_member_id_list($c_member_id, true);
 570    $friend_ids = implode(',', array_map('intval', $friends));
 571    if ($friend_ids) {
 572         $where .= ' OR (cd.public_flag = \'friend\' AND cd.c_member_id IN (' . $friend_ids . '))';
 573    }
 574    $where .= ')';
 575
 576    $sql = $select . $from . $where . ' ORDER BY maxdate DESC';
 577    $params = array(intval($c_member_id));
 578    $list = db_get_all_page($sql, $page, $page_size, $params);
 579
 580    foreach ($list as $key => $value) {
 581        $list[$key] += db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
 582        $list[$key]['r_datetime'] = $value['maxdate'];
 583        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 584    }
 585
 586    $sql = 'SELECT COUNT(cdcl.c_diary_id)' . $from . $where;
 587    $params = array(intval($c_member_id));
 588    $total_num = db_get_one($sql, $params);
 589
 590    $is_prev = false;
 591    $is_next = false;
 592    if ($total_num) {
 593        $is_prev = (bool)($page > 1);
 594        $is_next = (bool)($page < ceil($total_num / $page_size));
 595    }
 596    return array($list, $is_prev, $is_next, $total_num);
 597}
 598
 599/**
 600 * あるメンバーの指定された年月日の日記のリストを得る
 601 */
 602function p_fh_diary_list_diary_list_date4c_member_id($c_member_id, $page_size, $page, $year, $month, $day=0 ,$u = null)
 603{
 604    if ($day) {
 605        $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day, $year));
 606        $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day+1, $year));
 607    } else {
 608        $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, 1, $year));
 609        $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month+1, 1, $year));
 610    }
 611
 612    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
 613    $where = ' WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?';
 614    $sql = 'SELECT * FROM c_diary' .
 615            $where . $pf_cond .
 616            ' ORDER BY r_datetime DESC';
 617    $params = array(intval($c_member_id), $s_date, $e_date);
 618    $list = db_get_all_limit($sql, $page_size * ($page - 1), $page_size, $params);
 619
 620    foreach ($list as $key => $c_diary) {
 621        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($c_diary['c_diary_id']);
 622    }
 623
 624    $sql = 'SELECT COUNT(*) FROM c_diary' . $where;
 625    $total_num = db_get_one($sql, $params);
 626
 627    if ($total_num != 0) {
 628        $total_page_num =  ceil($total_num / $page_size);
 629        if ($page >= $total_page_num) {
 630            $next = false;
 631        } else {
 632            $next = true;
 633        }
 634        if ($page <= 1) {
 635            $prev = false;
 636        } else {
 637            $prev = true;
 638        }
 639    }
 640    return array($list , $prev , $next, $total_num);
 641}
 642
 643/**
 644 * 日記ページの「各月の日記」用
 645 *
 646 * 日記を最初に書いた月からスタートしてみる
 647 */
 648function p_fh_diary_list_date_list4c_member_id($c_member_id)
 649{
 650    $sql = "SELECT r_datetime FROM c_diary" .
 651        " WHERE c_member_id = ?" .
 652        " ORDER BY r_datetime";
 653    $params = array(intval($c_member_id));
 654    if (!$first_datetime = db_get_one($sql, $params)) {
 655        return array();
 656    }
 657
 658    $start_date = getdate(strtotime($first_datetime));
 659    $end_date =  getdate();
 660
 661    $date = array();
 662    $year = $start_date['year'];
 663    $month = $start_date['mon'];
 664    while (1) {
 665        $date[] =  array(
 666            'year' => $year,
 667            'month' => $month,
 668        );
 669
 670        if ($end_date['year'] <= $year
 671            && $end_date['mon'] <= $month) {
 672            break;
 673        }
 674
 675        $month++;
 676        if ($month > 12) {
 677            $month = 1;
 678            $year++;
 679        }
 680    }
 681    return array_reverse($date);
 682}
 683
 684//c_member_id から自分の日記についてるコメントID(複数)を取得
 685function p_fh_diary_c_diary_comment_id_list4c_member_id($c_member_id)
 686{
 687    $sql = "SELECT cdc.c_diary_comment_id FROM c_diary as cd,c_diary_comment as cdc" .
 688        " WHERE cd.c_member_id = ?".
 689        " AND cd.c_diary_id = cdc.c_diary_id";
 690    $params = array(intval($c_member_id));
 691    return db_get_col($sql, $params);
 692}
 693
 694/**
 695 * 年月 から日記のある日(複数)を取得
 696 */
 697function p_fh_diary_list_calendar_list4c_member_id($year, $month, $c_member_id)
 698{
 699    $sql = "SELECT cdc.c_diary_comment_id FROM c_diary as cd,c_diary_comment as cdc" .
 700        " WHERE cd.c_member_id = ?".
 701        " AND cd.c_diary_id = cdc.c_diary_id";
 702    $params = array(intval($c_member_id));
 703    return db_get_col($sql, $params);
 704}
 705
 706/**
 707 * 新着日記検索
 708 * 検索ポイントはタイトル、本文
 709 * 空白(全角半角問わない)でand検索可
 710 */
 711function p_h_diary_list_all_search_c_diary4c_diary($keyword, $page_size, $page, $c_member_id = '')
 712{
 713    $params = array();
 714
 715    $select = 'SELECT *';
 716    $from = ' FROM c_diary';
 717
 718    //自分の日記だけを対象にする事もできる
 719    if ($c_member_id) {
 720        $where = ' WHERE c_member_id = ?';
 721        $params[] = intval($c_member_id);
 722    } else {
 723        $where = " WHERE public_flag = 'public'";
 724    }
 725
 726    //and検索を実装
 727    //subject,body を検索
 728    if ($keyword) {
 729        //全角空白を半角に統一
 730        $keyword = str_replace(' ', ' ', $keyword);
 731
 732        $keyword_list = explode(' ', $keyword);
 733        foreach ($keyword_list as $word) {
 734            $word = check_search_word($word);
 735
 736            $where .= ' AND (subject LIKE ? OR body LIKE ?)';
 737            $params[] = '%'.$word.'%';
 738            $params[] = '%'.$word.'%';
 739        }
 740    }
 741    $order = " ORDER BY r_datetime DESC";
 742
 743    $sql = $select . $from . $where . $order;
 744
 745    $list = db_get_all_page($sql, $page, $page_size, $params);
 746    foreach ($list as $key => $value) {
 747        $list[$key]['c_member'] = db_member_c_member_with_profile($value['c_member_id']);
 748        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 749    }
 750
 751    $sql = 'SELECT COUNT(*)' . $from . $where;
 752    $total_num = db_get_one($sql, $params);
 753
 754    if ($total_num != 0) {
 755        $total_page_num =  ceil($total_num / $page_size);
 756        if ($page >= $total_page_num) {
 757            $next = false;
 758        } else {
 759            $next = true;
 760        }
 761        if ($page <= 1) {
 762            $prev = false;
 763        } else {
 764            $prev = true;
 765        }
 766    }
 767    return array($list , $prev , $next, $total_num);
 768}
 769
 770/**
 771 * 指定された年月に日記を書いている日のリストを返す
 772 */
 773function p_h_diary_is_diary_written_list4date($year, $month, $c_member_id, $u = null)
 774{
 775    include_once 'Date/Calc.php';
 776
 777    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
 778
 779    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
 780        $sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_diary" .
 781               " WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?" . $pf_cond;
 782    } else {
 783        $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_diary' .
 784               ' WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?' . $pf_cond;
 785    }
 786
 787    $date_format = '%Y-%m-%d 00:00:00';
 788    $thismonth = Date_Calc::beginOfMonth($month, $year, $date_format);
 789    $nextmonth = Date_Calc::beginOfNextMonth(0, $month, $year, $date_format);
 790
 791    $params = array(intval($c_member_id), $thismonth, $nextmonth);
 792
 793    return db_get_col($sql, $params);
 794}
 795
 796/**
 797 * 日記コメント情報をIDから取得
 798 *
 799 * @param   int $c_diary_comment_id
 800 * @return array
 801 *              c_diary_comemnt.*
 802 *              c_diary.c_member_id AS c_member_id_author
 803 */
 804function _do_c_diary_comment4c_diary_comment_id($c_diary_comment_id)
 805{
 806    $sql = "SELECT dc.*, d.c_member_id AS c_member_id_author";
 807    $sql .= " FROM c_diary_comment AS dc, c_diary AS d";
 808    $sql .= " WHERE dc.c_diary_comment_id = ?";
 809    $sql .= " AND dc.c_diary_id = d.c_diary_id";
 810    $params = array(intval($c_diary_comment_id));
 811    return db_get_row($sql, $params);
 812}
 813
 814/**
 815 * メンバーの最新日記のリストを返す
 816 */
 817function k_p_f_home_c_diary_list4c_member_id($c_member_id, $limit)
 818{
 819    //日記リスト
 820    $sql = "SELECT c_diary_id, r_date, subject ,image_filename_1";
 821    $sql .= " FROM c_diary";
 822    $sql .= " WHERE c_member_id = ?";
 823    $sql .= " ORDER BY r_date DESC";
 824    $params = array(intval($c_member_id));
 825    $c_diary_list = db_get_all_limit($sql, 0, $limit, $params);
 826
 827    //コメント数
 828    foreach ($c_diary_list as $key => $value) {
 829        $c_diary_id = $value['c_diary_id'];
 830
 831        $sql = "SELECT COUNT(*) ";
 832        $sql .= "FROM c_diary_comment ";
 833        $sql .= "WHERE c_diary_id = ?";
 834        $params = array(intval($c_diary_id));
 835        $c_diary_list[$key]['count_comment'] = db_get_one($sql, $params);
 836    }
 837    return $c_diary_list;
 838}
 839
 840/**
 841 * 最新日記リストを取得。
 842 * 取得範囲を指定できる。
 843 */
 844function k_p_fh_diary_list_c_diary_list4c_member_id($c_member_id, $page_size, $page)
 845{
 846    $sql = 'SELECT * FROM c_diary WHERE c_member_id = ? ORDER BY r_datetime DESC';
 847    $params = array(intval($c_member_id));
 848    $c_diary_list = db_get_all_page($sql, $page, $page_size, $params);
 849
 850    foreach ($c_diary_list as $key => $value) {
 851        $c_diary_list[$key]['count_comment'] =
 852            db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 853    }
 854
 855    $sql = 'SELECT COUNT(*) FROM c_diary WHERE c_member_id = ?';
 856    $total_num = db_get_one($sql, $params);
 857
 858    if ($total_num != 0) {
 859        $total_page_num =  ceil($total_num / $page_size);
 860        if ($page >= $total_page_num) {
 861            $next = false;
 862        } else {
 863            $next = true;
 864        }
 865        if ($page <= 1) {
 866            $prev = false;
 867        } else {
 868            $prev = true;
 869        }
 870    }
 871    return array($c_diary_list , $prev , $next);
 872}
 873
 874/**
 875 * 日記へのコメントリストを取得
 876 */
 877function k_p_fh_diary_c_diary_comment_list4c_diary_id($c_diary_id, $page_size, $page, $desc = true)
 878{
 879    $sql = 'SELECT * FROM c_diary_comment WHERE c_diary_id = ? ORDER BY r_datetime';
 880    if ($desc) {
 881        $sql .= ' DESC';
 882    }
 883    $params = array(intval($c_diary_id));
 884    $c_diary_comment_list = db_get_all_page($sql, $page, $page_size, $params);
 885
 886    foreach ($c_diary_comment_list as $key => $value) {
 887        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
 888        $c_diary_comment_list[$key]['nickname'] = $c_member['nickname'];
 889    }
 890
 891    $sql = 'SELECT COUNT(c_diary_comment_id) FROM c_diary_comment WHERE c_diary_id = ?';
 892    $params = array(intval($c_diary_id));
 893    $total_num = db_get_one($sql, $params);
 894
 895    if ($total_num > 0) {
 896        $total_page_num =  ceil($total_num / $page_size);
 897        if ($page >= $total_page_num) {
 898            $next = false;
 899        } else {
 900            $next = true;
 901        }
 902        if ($page <= 1) {
 903            $prev = false;
 904        } else {
 905            $prev = true;
 906        }
 907    }
 908    return array($c_diary_comment_list, $prev, $next, $total_num, $total_page_num);
 909}
 910
 911/**
 912 * 日記IDからその日記を書いたメンバーIDとニックネームと日記公開範囲を得る
 913 */
 914function k_p_fh_diary_c_member4c_diary_id($c_diary_id)
 915{
 916    $sql = "SELECT cm.c_member_id, cm.nickname,cm. public_flag_diary ";
 917    $sql .= " FROM c_member AS cm, c_diary AS cd ";
 918    $sql .= " WHERE cd.c_diary_id = ?";
 919    $sql .= " AND cm.c_member_id=cd.c_member_id";
 920    $params = array(intval($c_diary_id));
 921    return db_get_row($sql, $params);
 922}
 923
 924/**
 925 * フレンドの最新日記リスト
 926 */
 927function k_p_h_diary_list_friend_h_diary_list_friend4c_member_id($c_member_id, $page_size, $page)
 928{
 929    $from = "c_diary, c_friend";
 930    $where = "c_friend.c_member_id_from = ?" .
 931            " AND c_diary.c_member_id = c_friend.c_member_id_to";
 932
 933    $sql = "SELECT c_diary.* FROM {$from} WHERE {$where}" .
 934            " ORDER BY c_diary.r_datetime DESC";
 935    $params = array(intval($c_member_id));
 936    $lst = db_get_all_page($sql, $page, $page_size, $params);
 937
 938    foreach ($lst as $key=>$value) {
 939        $lst[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
 940        $lst[$key]['c_member'] = db_member_c_member4c_member_id($value['c_member_id']);
 941    }
 942
 943    $sql = "SELECT count(*) FROM {$from} WHERE {$where}";
 944    $total_num = db_get_one($sql, $params);
 945
 946    if ($total_num != 0) {
 947        $total_page_num =  ceil($total_num / $page_size);
 948        if ($page >= $total_page_num) {
 949            $next = false;
 950        } else {
 951            $next = true;
 952        }
 953
 954        if ($page <= 1) {
 955            $prev = false;
 956        } else {
 957            $prev = true;
 958        }
 959    }
 960
 961    return array($lst, $prev, $next, $total_num);
 962}
 963
 964//--- diary
 965
 966/**
 967 * 日記を追加
 968 */
 969function db_diary_insert_c_diary($c_member_id, $subject, $body, $public_flag, $is_comment_input = 1)
 970{
 971    //function cacheの削除
 972    pne_cache_drop('db_diary_get_c_diary_list4c_member_id', $c_member_id, 5);
 973
 974    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 975    $subject = db_ktai_delete_url_session_parameter($subject);
 976    $body = db_ktai_delete_url_session_parameter($body);
 977
 978    $data = array(
 979        'c_member_id' => intval($c_member_id),
 980        'subject' => $subject,
 981        'body' => $body,
 982        'public_flag' => util_cast_public_flag_diary($public_flag),
 983        'r_datetime' => db_now(),
 984        'r_date' => db_now(),
 985        'u_datetime' => db_now(),
 986        'is_checked' => 1,
 987        'image_filename_1' => '',
 988        'image_filename_2' => '',
 989        'image_filename_3' => '',
 990        'is_comment_input' => (bool)$is_comment_input,
 991    );
 992    return db_insert('c_diary', $data);
 993}
 994
 995/**
 996 * 日記を編集
 997 */
 998function db_diary_update_c_diary($c_diary_id, $subject, $body, $public_flag, $is_comment_input, $image_filename_1 = '', $image_filename_2 = '', $image_filename_3 = '')
 999{
1000    //function cacheの削除
1001    $c_diary = db_diary_get_c_diary4id($c_diary_id);
1002    pne_cache_drop('db_diary_get_c_diary_list4c_member_id', $c_diary['c_member_id'], 5);
1003
1004    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
1005    $subject = db_ktai_delete_url_session_parameter($subject);
1006    $body = db_ktai_delete_url_session_parameter($body);
1007
1008    $data = array(
1009        'subject' => $subject,
1010        'body' => $body,
1011        'public_flag' => util_cast_public_flag_diary($public_flag),
1012        'u_datetime' => db_now(),
1013        'is_comment_input' => (bool)$is_comment_input,
1014    );
1015    if ($image_filename_1) $data['image_filename_1'] = $image_filename_1;
1016    if ($image_filename_2) $data['image_filename_2'] = $image_filename_2;
1017    if ($image_filename_3) $data['image_filename_3'] = $image_filename_3;
1018
1019    $where = array(
1020        'c_diary_id' => intval($c_diary_id),
1021    );
1022    return db_update('c_diary', $data, $where);
1023}
1024
1025/**
1026 * 日記削除
1027 * コメント、画像も削除
1028 *
1029 * @param int $c_diary_id
1030 */
1031function db_diary_delete_c_diary($c_diary_id)
1032{
1033     //function cacheの削除
1034    $c_diary = db_diary_get_c_diary4id($c_diary_id);
1035    pne_cache_drop('db_diary_get_c_diary_list4c_member_id', $c_diary['c_member_id'], 5);
1036
1037   // 画像
1038    db_image_data_delete($c_diary['image_filename_1'], $c_diary['c_member_id']);
1039    db_image_data_delete($c_diary['image_filename_2'], $c_diary['c_member_id']);
1040    db_image_data_delete($c_diary['image_filename_3'], $c_diary['c_member_id']);
1041
1042    // コメント
1043    $sql = 'SELECT image_filename_1, image_filename_2, image_filename_3, c_member_id FROM c_diary_comment WHERE c_diary_id =?';
1044    $params = array(intval($c_diary_id));
1045    $comment_images = db_get_all($sql, $params);
1046
1047    //カテゴリ
1048    db_diary_category_delete_c_diary_category_diary($c_diary_id);
1049
1050    foreach ($comment_images as $value) {
1051        db_image_data_delete($value['image_filename_1'], $value['c_member_id']);
1052        db_image_data_delete($value['image_filename_2'], $value['c_member_id']);
1053        db_image_data_delete($value['image_filename_3'], $value['c_member_id']);
1054    }
1055
1056    $sql = 'DELETE FROM c_diary_comment_log WHERE c_diary_id = ?';
1057    db_query($sql, $params);
1058
1059    $sql = 'DELETE FROM c_diary_comment WHERE c_diary_id = ?';
1060    db_query($sql, $params);
1061
1062    // 日記
1063    $sql = 'DELETE FROM c_diary WHERE c_diary_id = ?';
1064    db_query($sql, $params);
1065}
1066
1067/**
1068 * 指定した番号の日記画像を削除
1069 */
1070function db_diary_delete_c_diary_image($c_diary_id, $image_num)
1071{
1072    $data = array(
1073        'image_filename_'.intval($image_num) => '',
1074    );
1075    $where = 'c_diary_id = '.intval($c_diary_id);
1076    return db_update('c_diary', $data, $where);
1077}
1078
1079/**
1080 * 日記に画像を追加する
1081 */
1082function db_diary_update_c_diary_image_filename($c_diary_id, $image_filename, $image_num)
1083{
1084    $data = array(
1085        'image_filename_'.intval($image_num) => $image_filename,
1086    );
1087    $where = 'c_diary_id = '.intval($c_diary_id);
1088    return db_update('c_diary', $data, $where);
1089}
1090
1091/**
1092 * 日記の未読/既読を変更
1093 * c_diary.is_checkedを更新
1094 */
1095function db_diary_update_c_diary_is_checked($c_diary_id, $value)
1096{
1097    $data = array(
1098        'is_checked' => (bool)$value,
1099    );
1100    $where = 'c_diary_id = '.intval($c_diary_id);
1101    return db_update('c_diary', $data, $where);
1102}
1103
1104//--- diary_comment
1105
1106/**
1107 * 日記コメント追加
1108 *
1109 * @param  int    $c_member_id
1110 * @param  int    $c_diary_id
1111 * @param  string $body
1112 * @return int    insert_id
1113 */
1114function db_diary_insert_c_diary_comment($c_member_id, $c_diary_id, $body)
1115{
1116    //function cache削除
1117    $c_diary =  db_diary_get_c_diary4id($c_diary_id);
1118    cache_drop_c_diary($c_member_id, $c_diary['c_member_id']);
1119    pne_cache_drop('p_h_home_c_diary_my_comment_list4c_member_id', $c_member_id, 5);
1120
1121    // 本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
1122    $body = db_ktai_delete_url_session_parameter($body);
1123
1124    $data = array(
1125        'c_member_id' => intval($c_member_id),
1126        'c_diary_id' => intval($c_diary_id),
1127        'body' => $body,
1128        'r_datetime' => db_now(),
1129        'image_filename_1' => '',
1130        'image_filename_2' => '',
1131        'image_filename_3' => '',
1132        'number' => db_diary_get_max_c_diary_comment_number4diary($c_diary_id)+1,
1133    );
1134    return db_insert('c_diary_comment', $data);
1135}
1136
1137/**
1138 * 日記コメント用画像追加
1139 */
1140function db_diary_insert_c_diary_comment_images($c_diary_comment_id, $image_filename_1 = '', $image_filename_2 = '', $image_filename_3 = '')
1141{
1142    $data = array();
1143    if ($image_filename_1) $data['image_filename_1'] = $image_filename_1;
1144    if ($image_filename_2) $data['image_filename_2'] = $image_filename_2;
1145    if ($image_filename_3) $data['image_filename_3'] = $image_filename_3;
1146
1147    $where = array(
1148        'c_diary_comment_id' => intval($c_diary_comment_id),
1149    );
1150    return db_update('c_diary_comment', $data, $where);
1151}
1152
1153/**
1154 * 日記コメント削除
1155 *
1156 * @param   int $c_diary_comment_id
1157 * @param   int $u  : 削除しようとしている人の c_member_id
1158 */
1159function db_diary_delete_c_diary_comment($c_diary_comment_id, $u)
1160{
1161    $dc = _do_c_diary_comment4c_diary_comment_id($c_diary_comment_id);
1162    if ($dc['c_member_id'] != $u && $dc['c_member_id_author'] != $u) {
1163        return false;
1164    }
1165
1166    db_image_data_delete($dc['image_filename_1'], $dc['c_member_id']);
1167    db_image_data_delete($dc['image_filename_2'], $dc['c_member_id']);
1168    db_image_data_delete($dc['image_filename_3'], $dc['c_member_id']);
1169
1170    $sql = 'DELETE FROM c_diary_comment WHERE c_diary_comment_id = ?';
1171    $params = array(intval($c_diary_comment_id));
1172    return db_query($sql, $params);
1173}
1174
1175// 日記公開範囲の一括設定をする
1176function db_diary_update_public_flag_diary_all($c_member_id, $public_flag)
1177{
1178    $data = array(
1179        'public_flag' => util_cast_public_flag_diary($public_flag),
1180    );
1181
1182    $where = array(
1183        'c_member_id' => intval($c_member_id),
1184    );
1185    return db_update('c_diary', $data, $where);
1186}
1187
1188/**
1189 * 現在の日記へのコメントのナンバーの最大値を返す
1190 */
1191function db_diary_get_max_c_diary_comment_number4diary($c_diary_id)
1192{
1193    $sql = 'SELECT MAX(number) FROM c_diary_comment WHERE c_diary_id = ?';
1194    $params = array(intval($c_diary_id));
1195    return db_get_one($sql, $params, 'main');
1196}
1197
1198/**
1199 * SNS全体の最新日記リスト取得
1200 * 日記公開範囲を考慮
1201 *
1202 * @param   int $limit
1203 * @return  array_of_array  (c_diary.*, nickname)
1204 */
1205function p_h_home_c_diary_all_list($limit)
1206{
1207    $sql = 'SELECT * FROM c_diary WHERE public_flag = \'public\''
1208         . ' ORDER BY c_diary.r_datetime DESC';
1209
1210    $c_diary_list_all = db_get_all_limit($sql, 0, $limit);
1211
1212    foreach ($c_diary_list_all as $key => $value) {
1213        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id']);
1214        $c_diary_list_all[$key]['nickname'] = $c_member['nickname'];
1215        $c_diary_list_all[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
1216    }
1217    return $c_diary_list_all;
1218}
1219
1220
1221/**
1222 * 日記コメント記入履歴の追加
1223 *
1224 * @param  int    $c_member_id
1225 * @param  int    $c_diary_id
1226 */
1227function db_diary_insert_c_diary_comment_log($c_member_id, $c_diary_id)
1228{
1229    $sql = 'SELECT c_diary_id FROM c_diary_comment_log'
1230         . ' WHERE c_member_id = ? AND c_diary_id = ?';
1231    $params = array(intval($c_member_id), intval($c_diary_id));
1232    $log_id = db_get_one($sql, $params, 'main');
1233    if (!$log_id) {
1234        $data = array(
1235            'c_member_id' => intval($c_member_id),
1236            'c_diary_id' => intval($c_diary_id),
1237            'r_datetime' => db_now(),
1238        );
1239        return db_insert('c_diary_comment_log', $data);
1240    }
1241    return false;
1242}
1243
1244/**
1245 * 日記コメント記入履歴の更新
1246 *
1247 * @param  int    $c_diary_id
1248 */
1249function db_diary_update_c_diary_comment_log($c_diary_id)
1250{
1251    $data = array(
1252        'r_datetime' => db_now(),
1253    );
1254    $where = array('c_diary_id' => intval($c_diary_id));
1255    return db_update('c_diary_comment_log', $data, $where);
1256}
1257
1258/**
1259 * 日記コメント記入履歴の削除
1260 *
1261 * @param  int    $c_member_id
1262 * @param  int    $c_diary_id
1263 * 該当c_diary_idへのコメント数が0なら、日記コメント記入履歴も削除する。
1264 *
1265 */
1266function db_diary_delete_c_diary_comment_log($c_member_id, $c_diary_id)
1267{
1268    // 投稿したコメントの有無
1269    $sql = 'SELECT COUNT(c_diary_comment_id) FROM c_diary_comment'
1270         . ' WHERE c_member_id = ? AND c_diary_id = ?';
1271    $params = array(intval($c_member_id), intval($c_diary_id));
1272    $count = db_get_one($sql, $params, 'main');
1273
1274    // コメントが無ければ履歴削除
1275    if (!$count) {
1276        $sql = 'DELETE FROM c_diary_comment_log'
1277             . ' WHERE c_member_id = ? AND c_diary_id = ?';
1278        $params = array(intval($c_member_id), intval($c_diary_id));
1279        db_query($sql, $params);
1280    }
1281    $sql = 'SELECT * FROM c_diary_comment WHERE c_diary_id = ?'
1282         . ' ORDER BY c_diary_comment_id DESC';
1283    $params = array(intval($c_diary_id));
1284    $comment = db_get_row($sql, $params, 'main');
1285
1286    // 最新コメントの日付で履歴更新
1287    $data = array(
1288        'r_datetime' => $comment['r_datetime'],
1289    );
1290    $where = array('c_diary_id' => intval($c_diary_id));
1291    return db_update('c_diary_comment_log', $data, $where);
1292}
1293
1294function db_diary_is_writable_comment4c_diary_id($c_diary_id)
1295{
1296    $max_c_diary_comment_number = db_diary_get_max_c_diary_comment_number4diary($c_diary_id);
1297    if ($max_c_diary_comment_number >= 1000) {
1298        return false;
1299    }
1300
1301    return true;
1302}
1303
1304/**
1305 * メンバーの日記とコメントを削除
1306 *
1307 * @param  int    $c_member_id
1308 */
1309function db_diary_delete4c_member_id($c_member_id)
1310{
1311    $sql = 'SELECT * FROM c_diary WHERE c_member_id = ?';
1312    $single = array(intval($c_member_id));
1313    $c_diary_list = db_get_all($sql, $single, 'main');
1314    foreach ($c_diary_list as $c_diary) {
1315        db_image_data_delete($c_diary['image_filename_1']);
1316        db_image_data_delete($c_diary['image_filename_2']);
1317        db_image_data_delete($c_diary['image_filename_3']);
1318
1319        // c_diary_comment
1320        $sql = 'SELECT * FROM c_diary_comment WHERE c_diary_id = ?';
1321        $params = array(intval($c_diary['c_diary_id']));
1322        $c_diary_comment_list = db_get_all($sql, $params, 'main');
1323        foreach ($c_diary_comment_list as $c_diary_comment) {
1324            db_image_data_delete($c_diary_comment['image_filename_1']);
1325            db_image_data_delete($c_diary_comment['image_filename_2']);
1326            db_image_data_delete($c_diary_comment['image_filename_3']);
1327        }
1328
1329        $sql = 'DELETE FROM c_diary_comment WHERE c_diary_id = ?';
1330        db_query($sql, $params);
1331        db_diary_delete_c_diary_comment_log($c_member_id, $c_diary['c_diary_id']);
1332    }
1333    $sql = 'DELETE FROM c_diary WHERE c_member_id = ?';
1334    db_query($sql, $single);
1335}
1336
1337?>