source: OpenPNE/branches/ebihara/prj_bugfix/webapp/lib/db/diary.php @ 1297

<?php
/**
 * @copyright 2005-2006 OpenPNE Project
 * @license   http://www.php.net/license/3_01.txt PHP License 3.01
 */

/**
 * 指定メンバーの日記カテゴリリストを取得する
 * 
 * @param int $c_member_id
 * @return array
 */
function db_diary_category_list4c_member_id($c_member_id)
{
    $sql = 'SELECT c_diary_category_id, category_name FROM c_diary_category ' .
        'WHERE c_member_id = ?';
    $result = db_get_all($sql, array(intval($c_member_id)));

    return $result;
}

/**
 * カテゴリ名からカテゴリIDを得る
 * 
 * @param int $c_member_id
 * @param string $category_name
 * @return bool
 */
function db_diary_get_category_id4category_name($c_member_id, $category_name)
{
    $sql = 'SELECT c_diary_category_id FROM c_diary_category ' .
        'WHERE category_name LIKE ? AND c_member_id = ?';
    return db_get_one($sql, array($category_name, intval($c_member_id)));
}

/**
 * カテゴリIDからカテゴリ名を得る
 */
function db_diary_get_category_name4category_id($category_id)
{
    $sql = 'SELECT category_name FROM c_diary_category ' .
        'WHERE c_diary_category_id = ?';
    return db_get_one($sql, array($category_id));
}

/**
 * 日記IDからカテゴリリストを得る
 * 
 * @param int $c_diary_id
 * @return array
 */
function db_diary_category_list4c_diary_id($c_diary_id)
{
    $sql = 'SELECT c_diary_category_id FROM c_diary_category_diary WHERE c_diary_id = ?';
    $category_list = db_get_col($sql, array(intval($c_diary_id)));
    $ids = join(',', $category_list);

    $sql = 'SELECT c_diary_category_id, category_name FROM c_diary_category' .
        ' WHERE c_diary_category_id IN ('.$ids.')';
    return db_get_all($sql);
}

/**
 * カテゴリIDから日記を得る
 * 
 * @param int $c_diary_category_id
 * @return array
 */
function db_diary_list4c_diary_category_id($c_member_id, $c_diary_category_id, $u = null)
{
    $sql = 'SELECT c_diary_id FROM c_diary_category_diary WHERE c_diary_category_id = ?';
    $diary_list = db_get_col($sql, array(intval($c_diary_category_id)));
    $ids = join(',', $diary_list);

    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
    $sql = 'SELECT * FROM c_diary' .
        ' WHERE c_diary_id IN ('.$ids.') AND c_member_id = ? ' . $pf_cond . ' ORDER BY r_datetime DESC';
    $list = db_get_all($sql, array($c_member_id));

    foreach ($list as $key => $c_diary) {
        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($c_diary['c_diary_id']);
    }

    return array($list, false, false);
}

/**
 * カテゴリを追加する
 * 
 * @param int $c_member_id
 * @param int $category_name
 */
function db_diary_category_insert_category($c_member_id, $category_name)
{
    $data = array(
        'c_member_id' => intval($c_member_id),
        'category_name' => $category_name,
        'r_datetime' => db_now(),
    );
    return db_insert('c_diary_category', $data);
}

/**
 * カテゴリを削除する
 * 
 * @param int $c_diary_category_id
 */
function db_diary_category_delete_category($c_diary_category_id)
{
    $sql = 'DELETE FROM c_diary_category WHERE c_diary_category_id = ?';
    db_query($sql, array($c_diary_category_id));    
}
/**
 * カテゴリと日記を関連づける
 * 
 * @param int $c_diary_id
 * @param int $c_diary_category_id
 */
function db_diary_category_insert_c_diary_category_diary($c_diary_id, $c_diary_category_id)
{
    $data = array(
        'c_diary_id' => intval($c_diary_id),
        'c_diary_category_id' => intval($c_diary_category_id),
    );
    db_insert('c_diary_category_diary', $data);
}

/**
 * カテゴリと日記の関連づけを削除する
 */
function db_diary_category_delete_c_diary_category_diary($c_diary_id)
{
    $diary_category_list = db_diary_category_list4c_diary_id($c_diary_id);

    $sql = 'DELETE FROM c_diary_category_diary WHERE c_diary_id = ?';
    db_query($sql, array($c_diary_id));

    foreach($diary_category_list as $value) {
            $sql = 'SELECT COUNT(*) FROM c_diary_category_diary'.
                ' WHERE c_diary_category_id = ?';
        $c_diary_category_id = $value['c_diary_category_id'];
        $is_diary = (bool)db_get_one($sql, array(intval($c_diary_category_id)));
        if (!$is_diary) {  //カテゴリに関連付いた日記が存在しない
            db_diary_category_delete_category($c_diary_category_id);
        }
    }
}

/**
 * db_diary_public_flag_condition
 * 
 * @param int $c_member_id target c_member_id
 * @param int $u viewer's c_member_id
 * @param string $force
 * @return string
 */
function db_diary_public_flag_condition($c_member_id, $u = null, $force = null)
{
    $pf_cond = '';
    if ($force) {
        switch ($force) {
        case 'friend':
            $pf_cond = " AND public_flag <> 'private'";
            break;
        case 'private':
            $pf_cond = " AND public_flag = 'public'";
            break;
        }
    } else {
        if (!is_null($u) && $c_member_id != $u) {
            $is_friend = db_friend_is_friend($c_member_id, $u);
            if ($is_friend) {
                $pf_cond = " AND public_flag <> 'private'";
            } else {
                $pf_cond = " AND public_flag = 'public'";
            }
        }
    }
    return $pf_cond;
}

//// c_diary

/**
 * 日記IDから日記を取得
 * 
 * @param   int   $c_diary_id
 * @return  array
 */
function db_diary_get_c_diary4id($c_diary_id)
{
    $sql = 'SELECT * FROM c_diary WHERE c_diary_id = ?';
    return db_get_row($sql, array(intval($c_diary_id)));
}

/**
 * メンバーIDから日記リストを最新順で取得
 * 
 * @param int $c_member_id
 * @return array 日記リスト
 */
function db_diary_get_c_diary_list4c_member_id($target_c_member_id, $count = 10, $u = null, $force = null)
{
    static $is_recurred = false;  //再帰処理中かどうかの判定フラグ

    if (!$is_recurred) {  //function cacheのために再帰処理を行う
        $is_recurred = true;
        $funcargs = func_get_args();
        return pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_FAST, __FUNCTION__, $funcargs);
    }

    $is_recurred = false;

    $pf_cond = db_diary_public_flag_condition($target_c_member_id, $u, $force);
    $sql = 'SELECT * FROM c_diary WHERE c_member_id = ?' . $pf_cond .
           ' ORDER BY r_datetime DESC';
    $params = array(intval($target_c_member_id));
    $arr = db_get_all_limit($sql, 0, $count, $params);
    foreach ($arr as $key => $value) {
        $arr[$key]['comment_count'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
    }
    return $arr;
}

function p_common_is_active_c_diary_id($c_diary_id)
{
    $sql = 'SELECT c_diary_id FROM c_diary WHERE c_diary_id = ?';
    return (bool)db_get_one($sql, array(intval($c_diary_id)));
}

//// c_diary_comment

/**
 * 日記のコメントリストを得る
 * 
 * @param   int $c_diary_id
 * @param   int $limit
 * @return  array 日記コメント＋ニックネーム
 */
function db_diary_get_c_diary_comment_list4c_diary_id($c_diary_id)
{
    $sql = 'SELECT c_member.nickname, c_diary_comment.*' .
        ' FROM c_diary_comment LEFT JOIN c_member USING (c_member_id)' .
        ' WHERE c_diary_id = ?' .
        ' ORDER BY c_diary_comment.r_datetime';
    return db_get_all($sql, array(intval($c_diary_id)));
}

/**
 * 日記のコメントリスト（好きなID(複数)）を取得
 * 
 * @param   array $id_list
 * @param   int   $page
 * @param   int   $page_size
 * @param   bool  $desc  並び順を日時の新しい順にするかどうか
 * @return  日記コメント（＋ニックネーム）リスト
 */
function db_diary_get_c_diary_comment_list4id_list($id_list, $page = 1, $page_size= -1, $desc = false)
{
    if (!count($id_list)) return array();
    if ($page_size < 0) {
        $page_size = count($id_list);
    }
    $e_id_list = implode(',', array_map('intval', (array)$id_list));

    $sql = 'SELECT * FROM c_diary_comment' .
            ' WHERE c_diary_comment_id IN ('.$e_id_list.')';
    if ($desc) {
        $sql .= ' ORDER BY r_datetime DESC';
    } else {
        $sql .= ' ORDER BY r_datetime';
    }
    $c_diary_comment_list = db_get_all_page($sql, $page, $page_size);
    foreach ($c_diary_comment_list as $key => $value) {
        $c_member = db_common_c_member4c_member_id_LIGHT($value['c_member_id']);
        $c_diary_comment_list[$key]['nickname'] = $c_member['nickname'];
    }

    $sql = 'SELECT COUNT(*) FROM c_diary_comment' .
        ' WHERE c_diary_comment_id IN ('.$e_id_list.')';
    $total_num = db_get_one($sql);

    if ($total_num != 0) {
        $total_page_num = ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }

    return array($c_diary_comment_list, $prev , $next, $total_num);
}

/**
 * 日記のコメント数を取得
 * 
 * @param int $c_diary_id
 * @return int コメント数
 */
function db_diary_count_c_diary_comment4c_diary_id($c_diary_id)
{
    $sql = 'SELECT COUNT(*) FROM c_diary_comment WHERE c_diary_id = ?';
    return db_get_one($sql, array(intval($c_diary_id)));
}

////

/**
 * あるメンバーの日記リストを取得
 * 
 * @param int $c_member_id target c_member_id
 * @param int $page_size
 * @param int $page
 * @param int $u viewer's c_member_id
 * @return array 日記リスト
 */
function p_fh_diary_list_diary_list4c_member_id($c_member_id, $page_size, $page, $u = null)
{
    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
    $sql = 'SELECT * FROM c_diary WHERE c_member_id = ?' . $pf_cond .
           ' ORDER BY r_datetime DESC';
    $params = array(intval($c_member_id));
    $list = db_get_all_page($sql, $page, $page_size, $params);

    foreach ($list as $key => $c_diary) {
        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($c_diary['c_diary_id']);
    }

    $sql = 'SELECT COUNT(*) FROM c_diary WHERE c_member_id = ?' . $pf_cond;
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($list, $prev, $next, $total_num);
}

/**
 * フレンドの最新日記リスト
 */
function p_h_diary_list_friend_h_diary_list_friend4c_member_id($c_member_id, $page_size, $page)
{
    $friends = db_friend_c_member_id_list($c_member_id, true);
    $ids = implode(',', array_map('intval', $friends));

    $hint = db_mysql_hint('USE INDEX (r_datetime_c_member_id, r_datetime)');
    $sql = 'SELECT * FROM c_diary' . $hint .
            ' WHERE c_member_id IN (' . $ids . ')' .
            ' AND public_flag <> \'private\'' .
            ' ORDER BY c_diary.r_datetime DESC';

    $lst = db_get_all_page($sql, $page, $page_size);

    foreach ($lst as $key=>$value) {
        $lst[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
        $lst[$key]['c_member'] = db_common_c_member4c_member_id($value['c_member_id']);
    }

    $sql = 'SELECT count(*) FROM c_diary' .
           ' WHERE c_member_id IN (' . $ids . ')' .
           ' AND public_flag <> \'private\'';
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }

    return array($lst, $prev, $next, $total_num);
}

/**
 * 未読コメントのある日記の数を数える
 * 
 * @param int $c_member_id
 * @return int 未読日記数
 */
function p_h_diary_count_c_diary_not_is_read4c_member_id($c_member_id)
{
    $sql = 'SELECT COUNT(*) FROM c_diary WHERE c_member_id = ? AND is_checked = 0';
    $params = array(intval($c_member_id));
    return db_get_one($sql, $params);
}

/**
 * 未読コメントのある日記のうちもっとも古い日記IDを返す
 * 
 * @param int $c_member_id
 * @return int c_diary_id
 */
function p_h_diary_c_diary_first_diary_read4c_member_id($c_member_id)
{
    $sql = 'SELECT c_diary_id FROM c_diary WHERE c_member_id = ? AND is_checked = 0 ORDER BY r_datetime';
    $params = array(intval($c_member_id));
    return db_get_one($sql, $params);
}

/**
 * フレンド最新日記リスト取得
 * 日記公開範囲を考慮
 * 
 * @param   int $c_member_id
 * @param   int $limit
 * @return  array_of_array  (c_diary.*, nickname)
 */
function p_h_home_c_diary_friend_list4c_member_id($c_member_id, $limit)
{
    static $is_recurred = false;  //再帰処理中かどうかの判定フラグ

    if (!$is_recurred) {  //function cacheのために再帰処理を行う
        $is_recurred = true;
        $funcargs = func_get_args();
        return pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_FAST, __FUNCTION__, $funcargs);
    }

    $is_recurred = false;

    $friends = db_friend_c_member_id_list($c_member_id, true);
    $ids = implode(',', array_map('intval', $friends));

    $hint = db_mysql_hint('USE INDEX (r_datetime_c_member_id, r_datetime)');
    $sql = 'SELECT * FROM c_diary' . $hint .
            ' WHERE c_member_id IN (' . $ids . ')' .
            ' AND public_flag <> \'private\'' .
            ' ORDER BY c_diary.r_datetime DESC';

    $c_diary_friend_list = db_get_all_limit($sql, 0, $limit);

    foreach ($c_diary_friend_list as $key => $value) {
        $c_member = db_common_c_member4c_member_id_LIGHT($value['c_member_id']);
        $c_diary_friend_list[$key]['nickname'] = $c_member['nickname'];
        $c_diary_friend_list[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
    }
    return $c_diary_friend_list;
}

/**
 * 日記コメント記入履歴取得
 * 
 * @param   int $c_member_id
 * @param   int $limit
 * @return  array_of_array  (c_diary.*, nickname)
 */
function p_h_home_c_diary_my_comment_list4c_member_id($c_member_id, $limit)
{
    static $is_recurred = false;  //再帰処理中かどうかの判定フラグ

    if (!$is_recurred) {  //function cacheのために再帰処理を行う
        $is_recurred = true;
        $funcargs = func_get_args();
        return pne_cache_recursive_call(OPENPNE_FUNCTION_CACHE_LIFETIME_FAST, __FUNCTION__, $funcargs);
    }

    $is_recurred = false;

    $date = date('Y-m-d 00:00:00', strtotime('-15 days'));

    $blocked = db_member_access_block_list4c_member_id_to($c_member_id);
    $blocked[] = $c_member_id;
    $except_ids = implode(',', $blocked);

    $sql = 'SELECT c_diary_comment.c_diary_id' .
            ' FROM c_diary_comment INNER JOIN c_diary USING (c_diary_id)' .
            ' WHERE c_diary_comment.c_member_id = ?' .
            ' AND c_diary_comment.r_datetime > ?' .
            ' AND c_diary.c_member_id NOT IN (' . $except_ids . ')' .
            ' AND c_diary.public_flag <> \'private\'';
    $params = array(intval($c_member_id), $date);
    $c_diary_id_list = db_get_col($sql, $params);
    $c_diary_id_list = array_unique($c_diary_id_list);
    if (!$c_diary_id_list) {
        return array();
    }

    $ids = implode(',', $c_diary_id_list);
    $sql = 'SELECT c_diary_id, MAX(r_datetime) as maxdate' .
           ' FROM c_diary_comment' .
           ' WHERE c_diary_id IN (' . $ids . ')' .
           ' GROUP BY c_diary_id' .
           ' ORDER BY maxdate DESC';
    $list = db_get_assoc_limit($sql, 0, $limit);

    $result = array();
    foreach ($list as $c_diary_id => $r_datetime) {
        $item = db_diary_get_c_diary4id($c_diary_id);
        if ($item['public_flag'] == 'friend' && !db_friend_is_friend($c_member_id, $item['c_member_id'])) {
            continue;
        }
        $item += db_common_c_member4c_member_id_LIGHT($item['c_member_id']);
        $item['r_datetime'] = $r_datetime;
        $item['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($c_diary_id);
        $result[] = $item;
    }
    return $result;
}

function p_h_diary_comment_list_c_diary_my_comment_list4c_member_id($c_member_id, $page, $page_size)
{
    $blocked = db_member_access_block_list4c_member_id_to($c_member_id);
    $blocked[] = $c_member_id;
    $except_ids = implode(',', $blocked);

    $friends = db_friend_c_member_id_list($c_member_id);
    $friend_ids = implode(',', $friends);

    $sql = 'SELECT d.c_diary_id' .
            ', d.subject' .
            ', d.c_member_id' .
            ', MAX(dc.r_datetime) AS r_datetime' .
            ', COUNT(DISTINCT dc.c_diary_comment_id) AS num_comment' .
        ' FROM c_diary AS d' .
            ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
            ', c_diary_comment AS mydc' .
        ' WHERE mydc.c_member_id = ?' .
            ' AND mydc.c_diary_id = d.c_diary_id' .
            ' AND mydc.c_member_id <> d.c_member_id' .
            ' AND d.c_member_id NOT IN (' . $except_ids . ')' .
            ' AND (d.public_flag = \'public\' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . ')))' .
        ' GROUP BY dc.c_diary_id' .
        ' ORDER BY r_datetime DESC';

    $params = array(intval($c_member_id));
    $list = db_get_all_page($sql, $page, $page_size, $params);

    foreach ($list as $key => $value) {
        $list[$key] += db_common_c_member4c_member_id_LIGHT($value['c_member_id']);
    }

    $sql = 'SELECT COUNT(DISTINCT d.c_diary_id)' .
        ' FROM c_diary AS d' .
            ' INNER JOIN c_diary_comment AS dc USING (c_diary_id)' .
            ', c_diary_comment AS mydc' .
        ' WHERE mydc.c_member_id = ?' .
            ' AND mydc.c_diary_id = d.c_diary_id' .
            ' AND mydc.c_member_id <> d.c_member_id' .
            ' AND d.c_member_id NOT IN (' . $except_ids . ')' .
            ' AND (d.public_flag = \'public\' OR (d.public_flag = \'friend\' AND d.c_member_id IN (' . $friend_ids . ')))';
    $total_num = db_get_one($sql, $params);

    $is_prev = false;
    $is_next = false;
    if ($total_num) {
        $is_prev = (bool)($page > 1);
        $is_next = (bool)($page < ceil($total_num / $page_size));
    }
    return array($list, $is_prev, $is_next, $total_num);
}

/**
 * あるメンバーの指定された年月日の日記のリストを得る
 */
function p_fh_diary_list_diary_list_date4c_member_id($c_member_id, $page_size, $page, $year, $month, $day=0 ,$u = null)
{
    if ($day) {
        $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day, $year));
        $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, $day+1, $year));
    } else {
        $s_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month, 1, $year));
        $e_date = date('Y-m-d H:i:s', mktime(0, 0, 0, $month+1, 1, $year));
    }

    $pf_cond = db_diary_public_flag_condition($c_member_id, $u);
    $where = ' WHERE c_member_id = ? AND r_datetime >= ? AND r_datetime < ?';
    $sql = 'SELECT * FROM c_diary' .
            $where . $pf_cond .
            ' ORDER BY r_datetime DESC';
    $params = array(intval($c_member_id), $s_date, $e_date);
    $list = db_get_all_limit($sql, $page_size * ($page - 1), $page_size, $params);

    foreach ($list as $key => $c_diary) {
        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($c_diary['c_diary_id']);
    }

    $sql = 'SELECT COUNT(*) FROM c_diary' . $where;
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }
        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($list , $prev , $next, $total_num);
}

/**
 * 日記ページの「各月の日記」用
 * 
 * 日記を最初に書いた月からスタートしてみる
 */
function p_fh_diary_list_date_list4c_member_id($c_member_id)
{
    $sql = "SELECT r_datetime FROM c_diary" .
        " WHERE c_member_id = ?" .
        " ORDER BY r_datetime";
    $params = array(intval($c_member_id));
    if (!$first_datetime = db_get_one($sql, $params)) {
        return array();
    }

    $start_date = getdate(strtotime($first_datetime));
    $end_date =  getdate();

    $date = array();
    $year = $start_date['year'];
    $month = $start_date['mon'];
    while (1) {
        $date[] =  array(
            'year' => $year,
            'month' => $month,
        );

        if ($end_date['year'] <= $year
            && $end_date['mon'] <= $month) {
            break;
        }

        $month++;
        if ($month > 12) {
            $month = 1;
            $year++;
        }
    }
    return array_reverse($date);
}

//c_member_id から自分の日記についてるコメントID(複数)を取得
function 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";
    $params = array(intval($c_member_id));
    return db_get_col($sql, $params);
}

/**
 * 年月 から日記のある日(複数)を取得
 */
function p_fh_diary_list_calendar_list4c_member_id($year, $month, $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";
    $params = array(intval($c_member_id));
    return db_get_col($sql, $params);
}

/**
 * 新着日記検索
 * 検索ポイントはタイトル、本文
 * 空白（全角半角問わない）でand検索可
 */
function p_h_diary_list_all_search_c_diary4c_diary($keyword, $page_size, $page, $c_member_id = '')
{
    $params = array();

    $select = 'SELECT *';
    $from = ' FROM c_diary';

    //自分の日記だけを対象にする事も出来る
    if ($c_member_id) {
        $where = ' WHERE c_member_id = ?';
        $params[] = intval($c_member_id);
    } else {
        $where = " WHERE public_flag = 'public'";
    }

    //and検索を実装
    //subject,body を検索
    if ($keyword) {
        //全角空白を半角に統一
        $keyword = str_replace('　', ' ', $keyword);

        $keyword_list = explode(' ', $keyword);
        foreach ($keyword_list as $word) {
            $word = check_search_word($word);

            $where .= ' AND (subject LIKE ? OR body LIKE ?)';
            $params[] = '%'.$word.'%';
            $params[] = '%'.$word.'%';
        }
    }
    $order = " ORDER BY r_datetime DESC";

    $sql = $select . $from . $where . $order;

    $list = db_get_all_page($sql, $page, $page_size, $params);
    foreach ($list as $key => $value) {
        $list[$key]['c_member'] = db_common_c_member_with_profile($value['c_member_id']);
        $list[$key]['num_comment'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
    }

    $sql = 'SELECT COUNT(*)' . $from . $where;
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }
        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($list , $prev , $next, $total_num);
}

/**
 * 指定された年月に日記を書いている日のリストを返す
 */
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);
    $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);
}

/**
 * 日記コメント情報をIDから取得
 * 
 * @param   int $c_diary_comment_id
 * @return array
 *              c_diary_comemnt.*
 *              c_diary.c_member_id AS c_member_id_author
 */
function _do_c_diary_comment4c_diary_comment_id($c_diary_comment_id)
{
    $sql = "SELECT dc.*, d.c_member_id AS c_member_id_author";
    $sql .= " FROM c_diary_comment AS dc, c_diary AS d";
    $sql .= " WHERE dc.c_diary_comment_id = ?";
    $sql .= " AND dc.c_diary_id = d.c_diary_id";
    $params = array(intval($c_diary_comment_id));
    return db_get_row($sql, $params);
}

/**
 * ターゲットメンバの最新日記のリストを返す
 */
function k_p_f_home_c_diary_list4c_member_id($c_member_id, $limit)
{
    //日記リスト
    $sql = "SELECT c_diary_id, r_date, subject ,image_filename_1";
    $sql .= " FROM c_diary";
    $sql .= " WHERE c_member_id = ?";
    $sql .= " ORDER BY r_date DESC";
    $params = array(intval($c_member_id));
    $c_diary_list = db_get_all_limit($sql, 0, $limit, $params);

    //コメント数
    foreach ($c_diary_list as $key => $value) {
        $c_diary_id = $value['c_diary_id'];

        $sql = "SELECT COUNT(*) ";
        $sql .= "FROM c_diary_comment ";
        $sql .= "WHERE c_diary_id = ?";
        $params = array(intval($c_diary_id));
        $c_diary_list[$key]['count_comment'] = db_get_one($sql, $params);
    }
    return $c_diary_list;
}

/**
 * 最新日記リストを取得。
 * 取得範囲を指定できる。
 */
function k_p_fh_diary_list_c_diary_list4c_member_id($c_member_id, $page_size, $page)
{
    $sql = 'SELECT * FROM c_diary WHERE c_member_id = ? ORDER BY r_datetime DESC';
    $params = array(intval($c_member_id));
    $c_diary_list = db_get_all_page($sql, $page, $page_size, $params);

    foreach ($c_diary_list as $key => $value) {
        $c_diary_list[$key]['count_comment'] =
            db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
    }

    $sql = 'SELECT COUNT(*) FROM c_diary WHERE c_member_id = ?';
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }
        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($c_diary_list , $prev , $next);
}

/**
 * 日記へのコメントリストを取得
 */
function k_p_fh_diary_c_diary_comment_list4c_diary_id($c_diary_id, $page_size, $page)
{
    $sql = 'SELECT * FROM c_diary_comment WHERE c_diary_id = ? ORDER BY r_datetime DESC';
    $params = array(intval($c_diary_id));
    $c_diary_comment_list = db_get_all_page($sql, $page, $page_size, $params);

    foreach ($c_diary_comment_list as $key => $value) {
        $c_member = db_common_c_member4c_member_id_LIGHT($value['c_member_id']);
        $c_diary_comment_list[$key]['nickname'] = $c_member['nickname'];
    }

    $sql = "SELECT COUNT(*) FROM c_diary_comment WHERE c_diary_id = ?";
    $params = array(intval($c_diary_id));
    $total_num = db_get_one($sql, $params);

    if ($total_num > 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }
        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }
    return array($c_diary_comment_list , $prev , $next, $total_num, $total_page_num);
}

/**
 * 日記ＩＤからその日記を書いたメンバＩＤとニックネームと日記公開範囲を得る
 */
function k_p_fh_diary_c_member4c_diary_id($c_diary_id)
{
    $sql = "SELECT cm.c_member_id, cm.nickname,cm. public_flag_diary ";
    $sql .= " FROM c_member AS cm, c_diary AS cd ";
    $sql .= " WHERE cd.c_diary_id = ?";
    $sql .= " AND cm.c_member_id=cd.c_member_id";
    $params = array(intval($c_diary_id));
    return db_get_row($sql, $params);
}

/**
 * フレンドの最新日記リスト
 */
function k_p_h_diary_list_friend_h_diary_list_friend4c_member_id($c_member_id, $page_size, $page)
{
    $from = "c_diary, c_friend";
    $where = "c_friend.c_member_id_from = ?" .
            " AND c_diary.c_member_id = c_friend.c_member_id_to";

    $sql = "SELECT c_diary.* FROM {$from} WHERE {$where}" .
            " ORDER BY c_diary.r_datetime DESC";
    $params = array(intval($c_member_id));
    $lst = db_get_all_page($sql, $page, $page_size, $params);

    foreach ($lst as $key=>$value) {
        $lst[$key]['count_comments'] = db_diary_count_c_diary_comment4c_diary_id($value['c_diary_id']);
        $lst[$key]['c_member'] = db_common_c_member4c_member_id($value['c_member_id']);
    }

    $sql = "SELECT count(*) FROM {$from} WHERE {$where}";
    $total_num = db_get_one($sql, $params);

    if ($total_num != 0) {
        $total_page_num =  ceil($total_num / $page_size);
        if ($page >= $total_page_num) {
            $next = false;
        } else {
            $next = true;
        }

        if ($page <= 1) {
            $prev = false;
        } else {
            $prev = true;
        }
    }

    return array($lst, $prev, $next, $total_num);
}

//--- diary

/**
 * 日記を追加
 */
function db_diary_insert_c_diary($c_member_id, $subject, $body, $public_flag)
{
    //function cacheの削除
    pne_cache_drop('db_diary_get_c_diary_list4c_member_id', $c_member_id, 5);

    $data = array(
        'c_member_id' => intval($c_member_id),
        'subject' => $subject,
        'body' => $body,
        'public_flag' => $public_flag,
        'r_datetime' => db_now(),
        'r_date' => db_now(),
        'is_checked' => 1,
    );
    return db_insert('c_diary', $data);
}

/**
 * 日記を編集
 */
function db_diary_update_c_diary($c_diary_id, $subject, $body, $public_flag,
    $image_filename_1 = '', $image_filename_2 = '', $image_filename_3 = '')
{
    //function cacheの削除
    $c_diary = db_diary_get_c_diary4id($c_diary_id);
    pne_cache_drop('db_diary_get_c_diary_list4c_member_id', $c_diary['c_member_id'], 5);

    $data = array(
        'subject' => $subject,
        'body' => $body,
        'public_flag' => $public_flag,
    );
    if ($image_filename_1) $data['image_filename_1'] = $image_filename_1;
    if ($image_filename_2) $data['image_filename_2'] = $image_filename_2;
    if ($image_filename_3) $data['image_filename_3'] = $image_filename_3;

    $where = array(
        'c_diary_id' => intval($c_diary_id),
    );
    return db_update('c_diary', $data, $where);
}

/**
 * 日記削除
 * コメント、画像も削除
 * 
 * @param int $c_diary_id
 */
function db_diary_delete_c_diary($c_diary_id)
{
     //function cacheの削除
    $c_diary = db_diary_get_c_diary4id($c_diary_id);
    pne_cache_drop('db_diary_get_c_diary_list4c_member_id', $c_diary['c_member_id'], 5);

   // 画像
    image_data_delete($c_diary['image_filename_1']);
    image_data_delete($c_diary['image_filename_2']);
    image_data_delete($c_diary['image_filename_3']);

    // コメント
    $sql = 'SELECT image_filename_1, image_filename_2, image_filename_3 FROM c_diary_comment WHERE c_diary_id =?';
    $params = array(intval($c_diary_id));
    $comment_images = db_get_all($sql, $params);

    //カテゴリ
    db_diary_category_delete_c_diary_category_diary($c_diary_id);

    foreach ($comment_images as $value) {
        image_data_delete($value['image_filename_1']);
        image_data_delete($value['image_filename_2']);
        image_data_delete($value['image_filename_3']);
    }

    $sql = 'DELETE FROM c_diary_comment WHERE c_diary_id = ?';
    db_query($sql, $params);

    // 日記
    $sql = 'DELETE FROM c_diary WHERE c_diary_id = ?';
    db_query($sql, $params);
}

/**
 * 指定した番号の日記画像を削除
 */
function db_diary_delete_c_diary_image($c_diary_id, $image_num)
{
    $data = array(
        'image_filename_'.intval($image_num) => '',
    );
    $where = 'c_diary_id = '.intval($c_diary_id);
    return db_update('c_diary', $data, $where);
}

/**
 * 日記に画像を追加する
 */
function db_diary_update_c_diary_image_filename($c_diary_id, $image_filename, $image_num)
{
    $data = array(
        'image_filename_'.intval($image_num) => $image_filename,
    );
    $where = 'c_diary_id = '.intval($c_diary_id);
    return db_update('c_diary', $data, $where);
}

/**
 * 日記の未読/既読を変更
 * c_diary.is_checkedを更新
 */
function db_diary_update_c_diary_is_checked($c_diary_id, $value)
{
    $data = array(
        'is_checked' => (bool)$value,
    );
    $where = 'c_diary_id = '.intval($c_diary_id);
    return db_update('c_diary', $data, $where);
}

//--- diary_comment

/**
 * 日記コメント追加
 * 
 * @param  int    $c_member_id
 * @param  int    $c_diary_id
 * @param  string $body
 * @return int    insert_id
 */
function db_diary_insert_c_diary_comment($c_member_id, $c_diary_id, $body)
{
    //function cache削除
    pne_cache_drop('p_h_home_c_diary_my_comment_list4c_member_id', $c_member_id, 5);

    $data = array(
        'c_member_id' => intval($c_member_id),
        'c_diary_id' => intval($c_diary_id),
        'body' => $body,
        'r_datetime' => db_now(),
    );
    return db_insert('c_diary_comment', $data);
}

/**
 * 日記コメント用画像追加
 */
function db_diary_insert_c_diary_comment_images($c_diary_comment_id, $image_filename_1 = '', $image_filename_2 = '', $image_filename_3 = '')
{
    $data = array();
    if ($image_filename_1) $data['image_filename_1'] = $image_filename_1;
    if ($image_filename_2) $data['image_filename_2'] = $image_filename_2;
    if ($image_filename_3) $data['image_filename_3'] = $image_filename_3;

    $where = array(
        'c_diary_comment_id' => intval($c_diary_comment_id),
    );
    return db_update('c_diary_comment', $data, $where);
}

/**
 * 日記コメント削除
 * 
 * @param   int $c_diary_comment_id
 * @param   int $u  : 削除しようとしている人の c_member_id
 */
function db_diary_delete_c_diary_comment($c_diary_comment_id, $u)
{
    $dc = _do_c_diary_comment4c_diary_comment_id($c_diary_comment_id);
    if ($dc['c_member_id'] != $u && $dc['c_member_id_author'] != $u) {
        return false;
    }

    image_data_delete($dc['image_filename_1']);
    image_data_delete($dc['image_filename_2']);
    image_data_delete($dc['image_filename_3']);

    $sql = 'DELETE FROM c_diary_comment WHERE c_diary_comment_id = ?';
    $params = array(intval($c_diary_comment_id));
    return db_query($sql, $params);
}

?>