/**
* orderby:
* r_datetime => 作成順
* r_num => 登録数順
*/
function db_review_search_result4keyword_category($keyword, $category_id, $orderby, $page = 1, $page_size = 30)
{
$from = " FROM c_review INNER JOIN c_review_comment USING (c_review_id)";
$wheres = array();
$params = array();
if ($keyword) {
$wheres[] = 'c_review.title LIKE ?';
$params[] = '%' . check_search_word($keyword) . '%';
}
if ($category_id) {
$wheres[] = 'c_review.c_review_category_id = ?';
$params[] = intval($category_id);
}
if ($wheres) {
$where = ' WHERE ' . implode(' AND ', $wheres);
} else {
$where = '';
}
switch ($orderby) {
case "r_datetime":
default:
$order = " ORDER BY r_datetime2 DESC";
break;
case "r_num":
$order = " ORDER BY write_num DESC, r_datetime2 DESC";
break;
}
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$group = " GROUP BY c_review.c_review_id" . ", c_review.title" . ", c_review.release_date" . ", c_review.manufacturer" . ", c_review.author" . ", c_review.c_review_category_id" . ", c_review.image_small" . ", c_review.image_medium" . ", c_review.image_large" . ", c_review.url" . ", c_review.asin" . ", c_review.list_price" . ", c_review.retail_price" . ", c_review.r_datetime";
} else {
$group = " GROUP BY c_review.c_review_id";
}
$sql = "SELECT" . " c_review.*" . ", MAX(c_review_comment.r_datetime) as r_datetime2" . ", COUNT(c_review_comment.c_review_comment_id) AS write_num" . $from . $where . $group . $order;
$lst = db_get_all_page($sql, $page, $page_size, $params);
//$lstに書き込み数を追加 + レビュー書き込み内容と日付を追加
foreach ($lst as $key => $value) {
$sql = "SELECT body FROM c_review_comment" . " WHERE c_review_id = ?" . " ORDER BY r_datetime DESC";
$p2 = array(intval($value['c_review_id']));
$lst[$key]['body'] = db_get_one($sql, $p2);
}
$sql = "SELECT COUNT(DISTINCT c_review.c_review_id)" . $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;
}
}
$start_num = ($page - 1) * $page_size + 1;
$end_num = ($page - 1) * $page_size + $page_size > $total_num ? $total_num : ($page - 1) * $page_size + $page_size;
return array($lst, $prev, $next, $total_num, $start_num, $end_num);
}
function db_admin_album_list($keyword, $page_size, $page)
{
$page = intval($page);
$page_size = intval($page_size);
$wheres = array();
if ($keyword) {
//全角空白を半角に統一
$keyword = str_replace(' ', ' ', $keyword);
$keyword_list = explode(' ', $keyword);
for ($i = 0; $i < count($keyword_list); $i++) {
$keyword = check_search_word($keyword_list[$i]);
$wheres[] = '(subject LIKE ? OR description LIKE ?)';
$params[] = '%' . $keyword . '%';
$params[] = '%' . $keyword . '%';
}
}
if ($wheres) {
$where = ' WHERE ' . implode(' AND ', $wheres);
} else {
$where = '';
}
$select = "SELECT *";
$from = " FROM c_album";
$order = " ORDER BY r_datetime DESC";
$sql = $select . $from . $where . $order;
$list = db_get_all_limit($sql, ($page - 1) * $page_size, $page_size, $params);
foreach ($list as $key => $value) {
$list[$key]['c_member'] = db_member_c_member_with_profile($value['c_member_id']);
$list[$key]['count_images'] = db_admin_album_count_c_album_image4c_album_id($value['c_album_id']);
}
$sql = "SELECT COUNT(*) " . $from . $where;
$total_num = db_get_one($sql, $params);
$total_page_num = ceil($total_num / $page_size);
$next = $page < $total_page_num;
$prev = $page > 1;
return array($list, $prev, $next, $total_num, $total_page_num);
}
/**
* 新着日記検索
* 検索ポイントはタイトル、本文
* 空白（全角半角問わない）で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_member_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 db_commu_search_c_commu_topic($search_word, $page_size, $page, $type = 'all', $c_commu_id = 0)
{
if (!$search_word) {
return db_commu_new_topic_list($page_size, $page, $type, $c_commu_id);
}
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$select = 'SELECT distinct on (ct.c_commu_topic_id) c.name AS commu_name, c.image_filename AS commu_image' . ', ct.*, ctc2.max_datetime';
} else {
$select = 'SELECT c.name AS commu_name, c.image_filename AS commu_image' . ', ct.*, ct.r_datetime AS max_datetime';
}
$from = ' FROM c_commu AS c, c_commu_topic AS ct, c_commu_topic_comment AS ctc';
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$from .= ', (SELECT c_commu_topic_id, max(r_datetime) as max_datetime' . ' FROM c_commu_topic_comment' . ' GROUP BY c_commu_topic_id' . ') AS ctc2';
}
$params = array();
$where = ' WHERE ct.c_commu_topic_id = ctc.c_commu_topic_id' . ' AND c.c_commu_id = ct.c_commu_id';
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$where .= ' AND ct.c_commu_topic_id = ctc2.c_commu_topic_id';
}
if ($c_commu_id) {
$where .= ' AND ct.c_commu_id = ?';
$params[] = $c_commu_id;
} else {
$where .= " AND c.is_open ='public'";
}
if ($search_word) {
$words = explode(' ', $search_word);
foreach ($words as $word) {
$word = check_search_word($word);
$where .= ' AND ((ct.name LIKE ?) OR (ctc.body LIKE ?))';
$params[] = '%' . $word . '%';
$params[] = '%' . $word . '%';
}
}
switch ($type) {
case 'topic':
$where .= ' AND event_flag = 0';
break;
case 'event':
$where .= ' AND event_flag = 1';
break;
case 'all':
default:
break;
}
$group = ' GROUP BY ct.c_commu_topic_id';
$order = ' ORDER BY max_datetime DESC';
if ($GLOBALS['_OPENPNE_DSN_LIST']['main']['dsn']['phptype'] == 'pgsql') {
$sql = "SELECT sub_tbl.* FROM (" . $select . $from . $where . ") as sub_tbl " . $order;
} else {
$sql = $select . $from . $where . $group . $order;
}
$list = db_get_all_page($sql, $page, $page_size, $params);
foreach ($list as $key => $value) {
$p = array((int) $value['c_commu_topic_id']);
$sql = 'SELECT body FROM c_commu_topic_comment WHERE number = 0 AND c_commu_topic_id = ?';
$list[$key]['body'] = db_get_one($sql, $p);
$number = db_commu_get_max_c_commu_topic_comment_number4c_topic_id($value['c_commu_topic_id']);
$list[$key]['max_number'] = $number;
$start_comment = db_commu_get_start_c_topic_comment($value['c_commu_topic_id']);
$list[$key]['image_filename1'] = $start_comment['image_filename1'];
$list[$key]['image_filename2'] = $start_comment['image_filename2'];
$list[$key]['image_filename3'] = $start_comment['image_filename3'];
}
$sql = 'SELECT COUNT(DISTINCT ct.c_commu_topic_id)' . $from . $where;
$total_num = db_get_one($sql, $params);
if ($total_num != 0) {
$total_page_num = ceil($total_num / $page_size);
$next = $page < $total_page_num;
$prev = $page > 1;
}
$start_num = ($page - 1) * $page_size + 1;
$end_num = $start_num + $page_size - 1 >= $total_num ? $total_num : $start_num + $page_size - 1;
return array($list, $prev, $next, $total_num, $start_num, $end_num);
}
