PageRenderTime 54ms CodeModel.GetById 17ms app.highlight 26ms RepoModel.GetById 1ms app.codeStats 1ms

/webapp/lib/db/message.php

https://github.com/openpne/OpenPNE2
PHP | 1044 lines | 758 code | 146 blank | 140 comment | 118 complexity | dd1225b4e9827443c99be0e9ced2cd0c 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 * メッセージIDからメッセージ情報取得
   9 */
  10function db_message_c_message4c_message_id($c_message_id)
  11{
  12    $sql = 'SELECT * FROM c_message WHERE c_message_id = ?';
  13    $params = array(intval($c_message_id));
  14    $c_message = db_get_row($sql, $params);
  15
  16    $c_member_from = db_member_c_member4c_member_id_LIGHT($c_message['c_member_id_from']);
  17    $c_member_to = db_member_c_member4c_member_id_LIGHT($c_message['c_member_id_to']);
  18
  19    $c_message['c_member_image_filename_from'] = $c_member_from['image_filename'];
  20    $c_message['c_member_nickname_from'] = $c_member_from['nickname'];
  21    $c_message['c_member_image_filename_to'] = $c_member_to['image_filename'];
  22    $c_message['c_member_nickname_to'] = $c_member_to['nickname'];
  23
  24    return $c_message;
  25}
  26
  27/**
  28 * 未読メッセージの数を数える
  29 *
  30 * @return  num_message_not_is_read
  31 */
  32function db_message_count_c_message_not_is_read4c_member_to_id($c_member_id_to)
  33{
  34    static $is_recurred = false;  //再帰処理中かどうかの判定フラグ
  35
  36    if (!$is_recurred) {  //function cacheのために再帰処理を行う
  37        $is_recurred = true;
  38        $funcargs = func_get_args();
  39        $result = pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_FAST, __FUNCTION__, $funcargs);
  40        $is_recurred = false;
  41        return $result;
  42    }
  43
  44    $sql = 'SELECT COUNT(*) FROM c_message WHERE c_member_id_to = ?' .
  45            ' AND is_read = 0 AND is_send = 1';
  46    $params = array(intval($c_member_id_to));
  47    return db_get_one($sql, $params);
  48}
  49
  50/**
  51 * メッセージ取得
  52 */
  53function db_message_c_message4c_message_id2($c_message_id, $u)
  54{
  55    $c_message = db_message_c_message4c_message_id($c_message_id);
  56
  57    if ($c_message['c_member_id_to'] == $u) {
  58        // 受信メッセージ
  59        $c_message['is_received'] = true;
  60        $c_message['image_filename_disp'] = $c_message['c_member_image_filename_from'];
  61    } elseif ($c_message['c_member_id_from'] == $u) {
  62        // 送信メッセージ
  63        $c_message['is_received'] = false;
  64        $c_message['image_filename_disp'] = $c_message['c_member_image_filename_to'];
  65    }
  66    return $c_message;
  67}
  68
  69/**
  70 * 受信メッセージリストを取得(年月日絞りに対応)
  71 */
  72function db_message_c_message_received_list4c_member_id4range($c_member_id, $page, $page_size, $year = '', $month = '', $day = '')
  73{
  74    $params = array();
  75    $params[] = intval($c_member_id);
  76
  77    $sql = "SELECT * FROM c_message";
  78    $where = "c_member_id_to = ?".
  79            " AND is_deleted_to = 0" .
  80            " AND is_send = 1";
  81
  82    //年月日で絞る
  83    if ($year && $month) {
  84        if ($day) {
  85            $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day, $year));
  86            $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day+1, $year));
  87        } else {
  88            $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, 1, $year));
  89            $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month+1, 1, $year));
  90        }
  91        $where .= ' AND r_datetime >= ? AND r_datetime < ?';
  92        $params[] = $s_date;
  93        $params[] = $e_date;
  94    }
  95
  96    $sql .= " WHERE $where";
  97    $sql .= " ORDER BY r_datetime DESC";
  98
  99    $c_message_list = db_get_all_page($sql, $page, $page_size, $params);
 100
 101    foreach ($c_message_list as $key => $value) {
 102        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_from']);
 103        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 104        $c_message_list[$key]['image_filename'] = $c_member['image_filename'];
 105    }
 106
 107    $sql = "SELECT COUNT(*) FROM c_message WHERE $where";
 108    $total_num = db_get_one($sql, $params);
 109
 110    if ($total_num != 0) {
 111        $total_page_num =  ceil($total_num / $page_size);
 112        if ($page >= $total_page_num) {
 113            $next = false;
 114        } else {
 115            $next = true;
 116        }
 117        if ($page <= 1) {
 118            $prev = false;
 119        } else {
 120            $prev = true;
 121        }
 122    }
 123    return array($c_message_list , $prev , $next, $total_num);
 124}
 125
 126/**
 127 * 送信メッセージリストを取得
 128 */
 129function db_message_c_message_sent_list4c_member_id4range($c_member_id, $page, $page_size, $year = '', $month = '', $day = '')
 130{
 131    $params = array(intval($c_member_id));
 132
 133    $sql = "SELECT * FROM c_message";
 134    $where = "c_member_id_from = ?" .
 135            " AND is_deleted_from = 0" .
 136            " AND is_send = 1";
 137
 138
 139    //年月日で絞る
 140    if ($year && $month) {
 141        if ($day) {
 142            $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day, $year));
 143            $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day+1, $year));
 144        } else {
 145            $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, 1, $year));
 146            $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month+1, 1, $year));
 147        }
 148        $where .= ' AND r_datetime >= ? AND r_datetime < ?';
 149        $params[] = $s_date;
 150        $params[] = $e_date;
 151    }
 152
 153    $sql .= " WHERE $where";
 154    $sql .= " ORDER BY r_datetime DESC";
 155    $c_message_list = db_get_all_page($sql, $page, $page_size, $params);
 156
 157    foreach ($c_message_list as $key => $value) {
 158        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_to']);
 159        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 160        $c_message_list[$key]['image_filename'] = $c_member['image_filename'];
 161    }
 162
 163    $sql =  "SELECT COUNT(*) FROM c_message WHERE $where";
 164    $total_num = db_get_one($sql, $params);
 165
 166    if ($total_num != 0) {
 167        $total_page_num =  ceil($total_num / $page_size);
 168        if ($page >= $total_page_num) {
 169            $next = false;
 170        } else {
 171            $next = true;
 172        }
 173        if ($page <= 1) {
 174            $prev = false;
 175        } else {
 176            $prev = true;
 177        }
 178    }
 179
 180    return array($c_message_list , $prev , $next, $total_num);
 181}
 182
 183/**
 184 * 下書き保存メッセージリストを取得
 185 */
 186function db_message_c_message_save_list4c_member_id4range($c_member_id, $page, $page_size)
 187{
 188    $sql = "SELECT * FROM c_message";
 189    $where = "c_member_id_from = ?".
 190            " AND is_send = 0" .
 191            " AND is_deleted_from = 0";
 192    $sql .= " WHERE $where";
 193    $sql .= " ORDER BY r_datetime DESC";
 194    $params = array(intval($c_member_id));
 195    $c_message_list = db_get_all_page($sql, $page, $page_size, $params);
 196
 197    foreach ($c_message_list as $key => $value) {
 198        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_to']);
 199        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 200        $c_message_list[$key]['image_filename'] = $c_member['image_filename'];
 201    }
 202
 203    $sql = "SELECT COUNT(*) FROM c_message WHERE $where";
 204    $total_num = db_get_one($sql, $params);
 205
 206    if ($total_num != 0) {
 207        $total_page_num =  ceil($total_num / $page_size);
 208        if ($page >= $total_page_num) {
 209            $next = false;
 210        } else {
 211            $next = true;
 212        }
 213        if ($page <= 1) {
 214            $prev = false;
 215        } else {
 216            $prev = true;
 217        }
 218    }
 219
 220    return array($c_message_list, $prev, $next, $total_num);
 221}
 222
 223/**
 224 * ごみ箱メッセージリストを取得
 225 */
 226function db_message_c_message_trash_list4c_member_id4range($c_member_id, $page, $page_size)
 227{
 228    $where_from = "c_member_id_from = ?" .
 229            " AND is_deleted_from = 1" .
 230            " AND is_kanzen_sakujo_from = 0";
 231
 232    $sql = 'SELECT * FROM c_message WHERE '. $where_from . ' ORDER BY r_datetime DESC';
 233    $sql_num = 'SELECT COUNT(*) FROM c_message WHERE '. $where_from;
 234    $params = array(intval($c_member_id));
 235    $c_message_list_from = db_get_all_page($sql, 1, ($page_size * ($page + 1)), $params);
 236    $c_message_num_from = db_get_one($sql_num, $params);
 237
 238    $where_to = "c_member_id_to = ?" .
 239            " AND is_deleted_to = 1" .
 240            " AND is_kanzen_sakujo_to = 0";
 241
 242    $sql = 'SELECT * FROM c_message WHERE '. $where_to . ' ORDER BY r_datetime DESC';
 243    $sql_num = 'SELECT COUNT(*) FROM c_message WHERE '. $where_to;
 244    $c_message_list_to = db_get_all_page($sql, 1, ($page_size * ($page + 1)), $params);
 245    $c_message_num_to = db_get_one($sql_num, $params);
 246
 247    $c_message_list = array_merge($c_message_list_from, $c_message_list_to);
 248
 249    foreach ($c_message_list as $key => $row) {
 250       $c_message_id[$key] = intval($row['c_message_id']);
 251    }
 252    @array_multisort($c_message_id, SORT_DESC, $c_message_list);
 253    $c_message_list = array_slice($c_message_list, $page_size * ($page - 1), $page_size);
 254
 255    $total_num = $c_message_num_from + $c_message_num_to;
 256
 257    if ($total_num != 0) {
 258        $total_page_num =  ceil($total_num / $page_size);
 259        if ($page >= $total_page_num) {
 260            $next = false;
 261        } else {
 262            $next = true;
 263        }
 264        if ($page <= 1) {
 265            $prev = false;
 266        } else {
 267            $prev = true;
 268        }
 269    }
 270
 271    foreach ($c_message_list as $key => $value) {
 272        if ($value['c_member_id_to'] == $c_member_id) {
 273            $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_from']);
 274        } else {
 275            $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_to']);
 276        }
 277        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 278        $c_message_list[$key]['image_filename'] = $c_member['image_filename'];
 279    }
 280
 281    return array($c_message_list, $prev, $next, $total_num);
 282}
 283
 284/**
 285 * 未読メッセージ数を取得
 286 *
 287 * @param int $c_member_id
 288 * @return int 未読メッセージ数
 289 */
 290function db_message_c_message_received_unread_all_count4c_member_id($c_member_id)
 291{
 292    $sql = 'SELECT COUNT(*) FROM c_message WHERE c_member_id_to = ?' .
 293            ' AND is_read = 0 AND is_send = 1';
 294    $params = array(intval($c_member_id));
 295    return db_get_one($sql, $params);
 296}
 297
 298function db_message_c_message_received_list4c_member_id4range2($c_member_id, $page_size, $page)
 299{
 300    $sql = "SELECT * FROM c_message";
 301    $sql .= " WHERE c_member_id_to = ?".
 302            " AND is_deleted_to = 0" .
 303            " AND is_send = 1";
 304    $sql .= " ORDER BY r_datetime DESC";
 305    $params = array(intval($c_member_id));
 306    $c_message_list = db_get_all_page($sql, $page, $page_size, $params);
 307
 308    foreach ($c_message_list as $key => $value) {
 309        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_from']);
 310        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 311    }
 312
 313    $sql = "SELECT COUNT(*) FROM c_message" .
 314            " WHERE c_member_id_to = ?".
 315            " AND is_deleted_to = 0" .
 316            " AND is_send = 1";
 317    $total_num = db_get_one($sql, $params);
 318
 319    if ($total_num != 0) {
 320        $total_page_num = ceil($total_num / $page_size);
 321        if ($page >= $total_page_num) {
 322            $next = false;
 323        } else {
 324            $next = true;
 325        }
 326        if ($page <= 1) {
 327            $prev = false;
 328        } else {
 329            $prev = true;
 330        }
 331    }
 332
 333    return array($c_message_list, $prev, $next, $total_num);
 334}
 335
 336function db_message_c_message_sent_list4c_member_id4range2($c_member_id, $page_size, $page)
 337{
 338    $sql = "SELECT * FROM c_message";
 339    $sql .= " WHERE c_member_id_from = ?".
 340            " AND is_deleted_from = 0" .
 341            " AND is_send = 1";
 342    $sql .= " ORDER BY r_datetime DESC";
 343    $params = array(intval($c_member_id));
 344    $c_message_list = db_get_all_page($sql, $page, $page_size, $params);
 345
 346    foreach ($c_message_list as $key => $value) {
 347        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_to']);
 348        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 349    }
 350
 351    $sql = "SELECT COUNT(*) FROM c_message" .
 352            " WHERE c_member_id_from = ?".
 353            " AND is_deleted_from = 0" .
 354            " AND is_send = 1";
 355    $total_num = db_get_one($sql, $params);
 356
 357    if ($total_num != 0) {
 358        $total_page_num =  ceil($total_num / $page_size);
 359        if ($page >= $total_page_num) {
 360            $next = false;
 361        } else {
 362            $next = true;
 363        }
 364        if ($page <= 1) {
 365            $prev = false;
 366        } else {
 367            $prev = true;
 368        }
 369    }
 370
 371    return array($c_message_list, $prev, $next, $total_num);
 372}
 373
 374/**
 375 * 返信側にある返信元メッセージIDを取得
 376 */
 377function db_message_hensinmoto_id($hensin_c_message_id)
 378{
 379    $sql = 'SELECT hensinmoto_c_message_id FROM c_message WHERE c_message_id = ?';
 380    $params = array(intval($hensin_c_message_id));
 381    return db_get_one($sql, $params);
 382}
 383
 384/*** write ***/
 385
 386/**
 387 * メッセージ作成
 388 *
 389 * @param   int $c_member_id_from
 390 * @param   int $c_member_id_to
 391 * @param   string  $subject
 392 * @param   string  $body
 393 * @return  int $insert_id
 394 */
 395function db_message_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body)
 396{
 397    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 398    $subject = db_ktai_delete_url_session_parameter($subject);
 399    $body = db_ktai_delete_url_session_parameter($body);
 400
 401    $data = array(
 402        'c_member_id_from' => intval($c_member_id_from),
 403        'c_member_id_to'   => intval($c_member_id_to),
 404        'subject'          => $subject,
 405        'body'             => $body,
 406        'r_datetime'       => db_now(),
 407        'is_send'          => 1,
 408        'image_filename_1' => '',
 409        'image_filename_2' => '',
 410        'image_filename_3' => '',
 411    );
 412    return db_insert('c_message', $data);
 413}
 414
 415/**
 416 * 承認メッセージ作成
 417 *
 418 * @param   int $c_member_id_from
 419 * @param   int $c_member_id_to
 420 * @param   string  $subject
 421 * @param   string  $body
 422 * @return  int $insert_id
 423 */
 424function db_message_insert_c_message_syoudaku($c_member_id_from, $c_member_id_to, $subject, $body)
 425{
 426    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 427    $subject = db_ktai_delete_url_session_parameter($subject);
 428    $body = db_ktai_delete_url_session_parameter($body);
 429
 430    $data = array(
 431        'c_member_id_from' => intval($c_member_id_from),
 432        'c_member_id_to'   => intval($c_member_id_to),
 433        'subject'          => $subject,
 434        'body'             => $body,
 435        'r_datetime'       => db_now(),
 436        'is_send'          => 1,
 437        'is_syoudaku'      => 1,
 438        'is_read'          => 1,
 439        'image_filename_1' => '',
 440        'image_filename_2' => '',
 441        'image_filename_3' => '',
 442    );
 443    return db_insert('c_message', $data);
 444}
 445
 446/**
 447 * メッセージを下書き保存する
 448 */
 449function db_message_insert_message_to_is_save($c_member_id_to,$c_member_id_from,$subject,$body,$jyusin_message_id)
 450{
 451    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 452    $subject = db_ktai_delete_url_session_parameter($subject);
 453    $body = db_ktai_delete_url_session_parameter($body);
 454
 455    $data = array(
 456        'c_member_id_from' => intval($c_member_id_from),
 457        'c_member_id_to'   => intval($c_member_id_to),
 458        'subject'          => $subject,
 459        'body'             => $body,
 460        'r_datetime'       => db_now(),
 461        'is_send'          => 0,
 462        'hensinmoto_c_message_id' => intval($jyusin_message_id),
 463        'image_filename_1' => '',
 464        'image_filename_2' => '',
 465        'image_filename_3' => '',
 466    );
 467    return db_insert('c_message', $data);
 468}
 469
 470/**
 471 * メッセージをゴミ箱へ移動
 472 * 受信メッセージの場合は既読にする
 473 *
 474 * @param   int $c_message_id
 475 * @param   int $c_member_id
 476 * @return  bool  削除が成功したかどうか
 477 */
 478function db_message_delete_c_message4c_message_id($c_message_id, $c_member_id)
 479{
 480    $message = db_message_c_message4c_message_id($c_message_id);
 481    $where = 'c_message_id = '.intval($c_message_id);
 482
 483    if ($message['c_member_id_to'] == $c_member_id) {
 484        // 受信メッセージ
 485        $data = array(
 486            'is_deleted_to' => 1,
 487            'is_read' => 1,
 488        );
 489        db_update('c_message', $data, $where);
 490        return true;
 491    } elseif ($message['c_member_id_from'] == $c_member_id) {
 492        // 送信メッセージ
 493        $data = array(
 494            'is_deleted_from' => 1,
 495        );
 496        db_update('c_message', $data, $where);
 497        return true;
 498    }
 499
 500    return false;
 501}
 502
 503/**
 504 * メッセージをごみ箱から元に戻す
 505 */
 506function db_message_move_message($c_message_id, $c_member_id)
 507{
 508    // 受信メッセージだった場合
 509    $data = array('is_deleted_from' => 0);
 510    $where = array(
 511        'c_message_id' => intval($c_message_id),
 512        'c_member_id_from' => intval($c_member_id),
 513    );
 514    db_update('c_message', $data, $where);
 515
 516    // 送信メッセージだった場合
 517    // 下書きメッセージだった場合
 518    $data = array('is_deleted_to' => 0);
 519    $where = array(
 520        'c_message_id' => intval($c_message_id),
 521        'c_member_id_to' => intval($c_member_id),
 522    );
 523    db_update('c_message', $data, $where);
 524}
 525
 526/**
 527 * メッセージをごみ箱から削除
 528 */
 529function db_message_delete_c_message_from_trash($c_message_id, $c_member_id)
 530{
 531    $data = array('is_kanzen_sakujo_from' => 1);
 532    $where = 'c_message_id = '.intval($c_message_id);
 533    db_update('c_message', $data, $where);
 534
 535    $sql = 'SELECT * FROM c_message WHERE c_message_id = ?';
 536    $params = array(intval($c_message_id));
 537    $c_message = db_get_row($sql, $params);
 538
 539    //c_image_sizeのレコード削除
 540    $where = array();
 541    $params = array();
 542    $sql = 'DELETE FROM c_image_size';
 543    if ($c_message['image_filename_1']) {
 544        $where[] = "filename = ?";
 545        $params[] = $c_message['image_filename_1'];
 546    }
 547    if ($c_message['image_filename_2']) {
 548        $where[] = "filename = ?";
 549        $params[] = $c_message['image_filename_2'];
 550    }
 551    if ($c_message['image_filename_3']) {
 552        $where[] = "filename = ?";
 553        $params[] = $c_message['image_filename_3'];
 554    }
 555
 556    if ($where) {
 557        $sql .= " WHERE " . implode(' OR ', $where);
 558        db_query($sql, $params);
 559    }
 560
 561    //function cacheの削除
 562    $category = util_image_filename2category($filename);
 563    pne_cache_drop('db_image_get_image_filesize', $c_member_id, $category);
 564}
 565
 566function db_message_delete_c_message_to_trash($c_message_id)
 567{
 568    $data = array('is_kanzen_sakujo_to' => 1);
 569    $where = 'c_message_id = '.intval($c_message_id);
 570    db_update('c_message', $data, $where);
 571}
 572
 573/**
 574 * 返信側に受信メッセージIDを渡す
 575 */
 576function db_message_update_is_hensinmoto_c_message_id($jyusin_c_message_id, $hensin_c_message_id)
 577{
 578    $data = array('hensinmoto_c_message_id' => intval($jyusin_c_message_id));
 579    $where = array('c_message_id' => intval($hensin_c_message_id));
 580    return db_update('c_message', $data, $where);
 581}
 582
 583/**
 584 * 返信済みにする
 585 */
 586function db_message_update_is_hensin($c_message_id)
 587{
 588    $data = array('is_hensin' => 1);
 589    $where = array('c_message_id' => intval($c_message_id));
 590    db_update('c_message', $data, $where);
 591}
 592
 593/**
 594 * メッセージを既読にする
 595 */
 596function db_message_update_c_message_is_read4c_message_id($c_message_id, $c_member_id)
 597{
 598    $data = array('is_read' => 1);
 599    $where = array(
 600        'c_message_id' => intval($c_message_id),
 601        'c_member_id_to' => intval($c_member_id),
 602    );
 603    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id);
 604    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id);
 605    return db_update('c_message', $data, $where);
 606}
 607
 608/**
 609 * メッセージの下書きを更新
 610 */
 611function db_message_update_message_to_is_save($c_message_id, $subject, $body, $is_send = 0)
 612{
 613    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 614    $subject = db_ktai_delete_url_session_parameter($subject);
 615    $body = db_ktai_delete_url_session_parameter($body);
 616
 617    $data = array(
 618        'subject'    => $subject,
 619        'body'       => $body,
 620        'r_datetime' => db_now(),
 621        'is_send'    => (bool)$is_send,
 622    );
 623    $where = array('c_message_id' => intval($c_message_id));
 624    db_update('c_message', $data, $where);
 625}
 626
 627//---
 628
 629//◆メッセージ受信メール
 630function db_message_send_message($c_member_id_from, $c_member_id_to, $subject, $body)
 631{
 632    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 633    $subject = db_ktai_delete_url_session_parameter($subject);
 634    $body = db_ktai_delete_url_session_parameter($body);
 635
 636    //メッセージ
 637    $c_message_id = db_message_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body);
 638
 639    do_common_send_message_mail_send($c_member_id_to, $c_member_id_from, $subject, $body, $c_message_id);
 640    do_common_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from, $subject, $body);
 641    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id_to);
 642    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id_to);
 643
 644    return $c_message_id;
 645}
 646
 647//◆承認依頼メッセージ受信メール
 648function db_message_send_message_syoudaku($c_member_id_from, $c_member_id_to, $subject, $body)
 649{
 650    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 651    $subject = db_ktai_delete_url_session_parameter($subject);
 652    $body = db_ktai_delete_url_session_parameter($body);
 653
 654    //メッセージ
 655    db_message_insert_c_message_syoudaku($c_member_id_from, $c_member_id_to, $subject, $body);
 656    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id_to);
 657    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id_to);
 658
 659    do_common_send_message_syoudaku_mail_send($c_member_id_to, $c_member_id_from);
 660    do_common_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from, $subject, $body);
 661}
 662
 663// コミュニティ紹介
 664function db_message_send_message_syoukai_commu($c_member_id_from, $c_member_id_to, $subject, $body)
 665{
 666    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 667    $subject = db_ktai_delete_url_session_parameter($subject);
 668    $body = db_ktai_delete_url_session_parameter($body);
 669
 670    //メッセージ
 671    db_message_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body);
 672    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id_to);
 673    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id_to);
 674
 675    do_common_send_message_syoukai_commu_mail_send($c_member_id_to, $c_member_id_from);
 676    do_common_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from, $subject, $body);
 677}
 678
 679// コミュニティ参加者全員にメール
 680function db_message_send_message_commu_send_msg($c_member_id_from, $c_member_id_to, $subject, $body, $c_commu_id)
 681{
 682    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 683    $subject = db_ktai_delete_url_session_parameter($subject);
 684    $body = db_ktai_delete_url_session_parameter($body);
 685
 686    //メッセージ
 687    db_message_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body);
 688    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id_to);
 689    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id_to);
 690
 691    do_common_send_message_commu_send_msg($c_member_id_to, $c_member_id_from, $c_commu_id);
 692    do_common_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from, $subject, $body);
 693}
 694
 695// メンバー紹介
 696function db_message_send_message_syoukai_member($c_member_id_from, $c_member_id_to, $subject, $body)
 697{
 698    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 699    $subject = db_ktai_delete_url_session_parameter($subject);
 700    $body = db_ktai_delete_url_session_parameter($body);
 701
 702    //メッセージ
 703    db_message_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body);
 704    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id_to);
 705    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id_to);
 706
 707    do_common_send_message_syoukai_member_mail_send($c_member_id_to, $c_member_id_from);
 708    do_common_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from, $subject, $body);
 709}
 710
 711//イベント紹介
 712function db_message_send_message_event_invite($c_member_id_from, $c_member_id_to, $subject, $body)
 713{
 714    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 715    $subject = db_ktai_delete_url_session_parameter($subject);
 716    $body = db_ktai_delete_url_session_parameter($body);
 717
 718    //メッセージ
 719    db_message_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body);
 720    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id_to);
 721    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id_to);
 722
 723    do_common_send_message_event_invite_mail_send($c_member_id_to, $c_member_id_from);
 724    do_common_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from, $subject, $body);
 725}
 726
 727//イベントメッセージ
 728function db_message_send_message_event_message($c_member_id_from, $c_member_id_to, $subject, $body)
 729{
 730    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 731    $subject = db_ktai_delete_url_session_parameter($subject);
 732    $body = db_ktai_delete_url_session_parameter($body);
 733
 734    //メッセージ
 735    db_message_insert_c_message($c_member_id_from, $c_member_id_to, $subject, $body);
 736    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (int)$c_member_id_to);
 737    pne_cache_drop('db_message_count_c_message_not_is_read4c_member_to_id', (string)$c_member_id_to);
 738
 739    do_common_send_message_event_message_mail_send($c_member_id_to, $c_member_id_from);
 740    do_common_send_message_mail_send_ktai($c_member_id_to, $c_member_id_from, $subject, $body);
 741}
 742
 743function db_message_update_c_message($c_message_id, $subject, $body, $image_filename_1 = '', $image_filename_2 = '', $image_filename_3 = '', $filename = '')
 744{
 745    // タイトルと本文中に書いてあるURLがSNS内でありセッションパラメータを含んでいた場合は削除
 746    $subject = db_ktai_delete_url_session_parameter($subject);
 747    $body = db_ktai_delete_url_session_parameter($body);
 748
 749    $data = array(
 750        'subject' => $subject,
 751        'body' => $body,
 752    );
 753    if ($image_filename_1) $data['image_filename_1'] = $image_filename_1;
 754    if ($image_filename_2) $data['image_filename_2'] = $image_filename_2;
 755    if ($image_filename_3) $data['image_filename_3'] = $image_filename_3;
 756    if ($filename) $data['filename'] = $filename;
 757
 758    $where = array(
 759        'c_message_id' => intval($c_message_id),
 760    );
 761    return db_update('c_message', $data, $where);
 762}
 763
 764
 765//メッセージの次のc_message_idを取得
 766function db_message_get_c_message_next_id4c_message_id($c_member_id, $c_message_id, $box)
 767{
 768    if ($box == 'savebox' || $box == 'trash') {
 769        return null;
 770    } else if ($box == 'outbox'){   //送信箱
 771        $where = " WHERE c_member_id_from = ? AND is_deleted_from = 0";
 772    } else {                        //受信箱
 773        $where = " WHERE c_member_id_to = ? AND is_deleted_to = 0";
 774    }
 775
 776    $sql =  "SELECT c_message_id FROM c_message" .
 777            $where.
 778            " AND is_send = 1" .
 779            " AND c_message_id > ?" .
 780            " ORDER BY r_datetime";
 781    $params = array(
 782                intval($c_member_id),
 783                intval($c_message_id)
 784              );
 785
 786    return db_get_one($sql, $params);
 787}
 788
 789//メッセージの前のc_message_idを取得
 790function db_message_get_c_message_prev_id4c_message_id($c_member_id, $c_message_id, $box)
 791{
 792    if ($box == 'savebox' || $box == 'trash') {
 793        return null;
 794    } else if ($box == 'outbox'){   //送信箱
 795        $where = " WHERE c_member_id_from = ? AND is_deleted_from = 0";
 796    } else {                        //受信箱
 797        $where = " WHERE c_member_id_to = ? AND is_deleted_to = 0";
 798    }
 799
 800    $sql =  "SELECT c_message_id FROM c_message" .
 801            $where.
 802            " AND is_send = 1" .
 803            " AND c_message_id < ?" .
 804            " ORDER BY r_datetime DESC";
 805    $params = array(
 806                intval($c_member_id),
 807                intval($c_message_id)
 808              );
 809
 810    return db_get_one($sql, $params);
 811}
 812
 813//メッセージを検索
 814//検索対象:subject,body
 815
 816function db_message_search_c_message($c_member_id, $page, $page_size, $keyword, $box, $target_c_member_id = null)
 817{
 818    $params = array();
 819    $params[] = intval($c_member_id);
 820
 821    if ($box == 'inbox' || !$box) {
 822        $where = "c_member_id_to = ?".
 823                 " AND is_deleted_to = 0" .
 824                 " AND is_send = 1";
 825        if ($target_c_member_id) {
 826            $where .= " AND c_member_id_from = ?";
 827            $params[] = intval($target_c_member_id);
 828        }
 829    } elseif ($box == 'outbox') {
 830        $where = "c_member_id_from = ?".
 831                 " AND is_deleted_from = 0" .
 832                 " AND is_send = 1";
 833        if ($target_c_member_id) {
 834            $where .= " AND c_member_id_to = ?";
 835            $params[] = intval($target_c_member_id);
 836        }
 837    }
 838
 839    $where .= " AND ( subject like ? OR body like ?)";
 840
 841    $params[] = '%'.strval($keyword).'%';
 842    $params[] = '%'.strval($keyword).'%';
 843
 844    $sql = "SELECT * FROM c_message";
 845    $sql .= " WHERE $where";
 846    $sql .= " ORDER BY r_datetime DESC";
 847
 848    $c_message_list = db_get_all_page($sql, $page, $page_size, $params);
 849
 850    foreach ($c_message_list as $key => $value) {
 851        if ($box == 'inbox' || !$box) {
 852            $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_from']);
 853        } else {
 854            $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_to']);
 855        }
 856        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 857        $c_message_list[$key]['image_filename'] = $c_member['image_filename'];
 858    }
 859
 860    $sql = "SELECT COUNT(*) FROM c_message WHERE $where";
 861    $total_num = db_get_one($sql, $params);
 862
 863    if ($total_num != 0) {
 864        $total_page_num =  ceil($total_num / $page_size);
 865        if ($page >= $total_page_num) {
 866            $next = false;
 867        } else {
 868            $next = true;
 869        }
 870        if ($page <= 1) {
 871            $prev = false;
 872        } else {
 873            $prev = true;
 874        }
 875    }
 876    return array($c_message_list , $prev , $next, $total_num);
 877
 878}
 879
 880
 881/**
 882 * 受信メッセージの送信者リストを取得
 883 */
 884function db_message_c_message_sender_list4c_member_id($c_member_id)
 885{
 886    $where = "c_member_id_to = ?".
 887            " AND is_deleted_to = 0" .
 888            " AND is_send = 1";
 889
 890    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
 891        $sql = "SELECT c_member_id_from" .
 892                " FROM" .
 893                    "(" .
 894                        "SELECT distinct on(c_member_id_from) *" .
 895                        " FROM" .
 896                            " c_message" .
 897                        " WHERE " . $where .
 898                    ") as sub_member_tbl";
 899    } else {
 900        $sql = "SELECT distinct c_member_id_from FROM c_message";
 901        $sql .= " WHERE $where";
 902    }
 903    $sql .= " ORDER BY r_datetime DESC";
 904    $params = array(intval($c_member_id));
 905    $c_message_list = db_get_all($sql, $params);
 906
 907    foreach ($c_message_list as $key => $value) {
 908        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_from']);
 909        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 910    }
 911    return $c_message_list;
 912}
 913
 914/**
 915 * 受信メッセージの送信者リストを取得
 916 */
 917function db_message_c_message_receiver_list4c_member_id($c_member_id)
 918{
 919    $where = "c_member_id_from = ?".
 920            " AND is_deleted_from = 0" .
 921            " AND is_send = 1";
 922
 923    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
 924        $sql = "SELECT c_member_id_to" .
 925                " FROM" .
 926                    "(" .
 927                        "SELECT distinct on(c_member_id_to) *" .
 928                        " FROM" .
 929                            " c_message" .
 930                        " WHERE " . $where .
 931                    ") as sub_member_tbl";
 932    } else {
 933        $sql = "SELECT distinct c_member_id_to FROM c_message";
 934        $sql .= " WHERE $where";
 935    }
 936
 937    $sql .= " ORDER BY r_datetime DESC";
 938
 939    $params = array(intval($c_member_id));
 940    $c_message_list = db_get_all($sql, $params);
 941
 942    foreach ($c_message_list as $key => $value) {
 943        $c_member = db_member_c_member4c_member_id_LIGHT($value['c_member_id_to']);
 944        $c_message_list[$key]['nickname'] = $c_member['nickname'];
 945    }
 946    return $c_message_list;
 947}
 948
 949
 950/**
 951 * メッセージページの「各月のメッセージ」用
 952 *
 953 * メッセージを最初に書いた月からスタートしてみる
 954 */
 955function db_message_month_list4c_member_id($c_member_id, $box)
 956{
 957    if ($box == 'inbox' || !$box) {
 958        $where = "c_member_id_to = ?".
 959                 " AND is_deleted_to = 0" .
 960                 " AND is_send = 1";
 961    } elseif ($box == 'outbox') {
 962        $where = "c_member_id_from = ?".
 963                 " AND is_deleted_from = 0" .
 964                 " AND is_send = 1";
 965    } else {
 966        return null;
 967    }
 968
 969    $sql = "SELECT r_datetime FROM c_message" .
 970        " WHERE $where" .
 971        " ORDER BY r_datetime";
 972
 973    $params = array(intval($c_member_id));
 974    if (!$first_datetime = db_get_one($sql, $params)) {
 975        return array();
 976    }
 977
 978    $start_date = getdate(strtotime($first_datetime));
 979    $end_date =  getdate();
 980
 981    $date = array();
 982    $year = $start_date['year'];
 983    $month = $start_date['mon'];
 984    while (1) {
 985        $date[] =  array(
 986            'year' => $year,
 987            'month' => $month,
 988        );
 989
 990        if ($end_date['year'] <= $year
 991            && $end_date['mon'] <= $month) {
 992            break;
 993        }
 994
 995        $month++;
 996        if ($month > 12) {
 997            $month = 1;
 998            $year++;
 999        }
1000    }
1001    return array_reverse($date);
1002}
1003
1004
1005
1006/**
1007 * 指定された年月にメッセージを送受信した日のリストを返す
1008 */
1009function db_message_is_message_list4date($u, $year, $month, $box)
1010{
1011    include_once 'Date/Calc.php';
1012
1013    if ($box == 'inbox' || !$box) {
1014        $where = "c_member_id_to = ?".
1015                 " AND is_deleted_to = 0" .
1016                 " AND is_send = 1";
1017    } elseif ($box == 'outbox') {
1018        $where = "c_member_id_from = ?".
1019                 " AND is_deleted_from = 0" .
1020                 " AND is_send = 1";
1021    } else {
1022        return null;
1023    }
1024
1025    if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
1026        $sql = "SELECT DISTINCT date_part('day', r_datetime) FROM c_message" .
1027               " WHERE $where" .
1028               ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
1029    } else {
1030        $sql = 'SELECT DISTINCT DAYOFMONTH(r_datetime) FROM c_message' .
1031               " WHERE $where" .
1032               ' AND is_send=1 AND r_datetime >= ? AND r_datetime < ?';
1033    }
1034
1035    $date_format = '%Y-%m-%d 00:00:00';
1036    $thismonth = Date_Calc::beginOfMonth($month, $year, $date_format);
1037    $nextmonth = Date_Calc::beginOfNextMonth(0, $month, $year, $date_format);
1038
1039    $params = array(intval($u), $thismonth, $nextmonth);
1040
1041    return db_get_col($sql, $params);
1042}
1043
1044?>