/**
* $user_community_idの新着フォルダ情報を取得する
* ($commyunity_row_arrayに含まれるcommunity_idが所有するファイルを対象とする)
*
* @param $user_community_id ユーザコミュニティID (ダイアリーへのアクセス者となるユーザ)
* @param $csv_string 所有者条件csv文字列
* @return 新着フォルダ一覧 (連想配列の配列)
*/
function get_new_folder_row_array($user_community_id, $csv_string, $days = false, $offset = false)
{
$user_community_id = pg_escape_string($user_community_id);
// フォルダの新着記事を最新順に取得する
$sql = "SELECT *, acs_is_unread_file(" . $user_community_id . ", fi.file_id) as is_unread " . " FROM folder_file AS ff, folder AS fo, " . "\t file_info AS fi, community AS cm" . " WHERE fi.file_id = ff.file_id" . " AND ff.folder_id = fo.folder_id" . " AND fi.owner_community_id = cm.community_id" . " AND fi.owner_community_id IN (" . $csv_string . ")";
//------ 2007.2 表示時間短縮対応
// 日数指定がある場合
if ($days !== false) {
$sql = $sql . " AND " . ACSLib::get_sql_condition_from_today("fi.update_date", $days);
}
//$sql = $sql . " ORDER BY fi.update_date DESC";
if ($offset != false) {
// 表示件数制御 //
$display_count = ACSSystemConfig::get_keyword_value(ACSMsg::get_mst('system_config_group', 'D02'), 'NEW_INFO_TOP_DISPLAY_MAX_COUNT');
$sql = $sql . " AND open_level_code is not null";
$sql = $sql . " ORDER BY fi.update_date DESC";
$sql = $sql . " OFFSET 0 LIMIT " . $display_count;
} else {
$sql = $sql . " ORDER BY fi.update_date DESC";
}
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* 足跡情報一覧を取得する
*
* @param $user_community_id コミュニティID
* $days データ取得日数
* @return $row(成功) / false(失敗)
*/
static function get_footprint_row($user_community_id, $days = 0)
{
// 日数指定がある場合
if ($days != 0) {
$where = ACSLib::get_sql_condition_from_today("foot.post_date", $days);
}
$ret = ACSUser::get_footprint_list($user_community_id, $where);
return $ret;
}
/**
* 全コミュニティプットファイル検索
*
* @param $community_where putコミュニティ検索条件
* @param $unread_check_user_community_id unreadチェック実施時のユーザコミュニティid
* @param $form 検索条件
* @return フォルダ情報の配列
*/
function search_all_put_file_info_row_array($form, $community_where = "", $unread_check_user_community_id = "", $days = false)
{
// 1. コミュニティにプットされてるユーザフォルダのfolder_idを全て取得する
$sql = "SELECT folder_id";
$sql .= " FROM put_community";
if ($community_where != "") {
$sql .= " WHERE " . $community_where;
}
$row_array = ACSDB::_get_row_array($sql);
$folder_id_array = array();
foreach ($row_array as $row) {
array_push($folder_id_array, $row['folder_id']);
}
if (count($folder_id_array) == 0) {
// 0件
return array();
}
// 2. コミュニティフォルダからアクセス可能な中身のフォルダのfolder_idを全て取得する
$user_folder_id_array = ACSGenericFolder::get_recursive_folder_id_array($folder_id_array);
if (count($user_folder_id_array) == 0) {
// 0件
return array();
}
// 3. 検索対象となる、ユーザがプットしたフォルダ以下のフォルダID CSV
$user_folder_id_array_csv = implode(',', $user_folder_id_array);
$sql = "SELECT * ";
if ($unread_check_user_community_id != '') {
$sql .= ",acs_is_unread_file(" . $unread_check_user_community_id . ",file_info.file_id) as is_unread ";
}
$sql .= " FROM folder LEFT OUTER JOIN put_community ON folder.folder_id = put_community.folder_id, folder_file, file_info";
$sql .= " WHERE folder.folder_id IN ({$user_folder_id_array_csv})";
$sql .= " AND folder.folder_id = folder_file.folder_id";
$sql .= " AND folder_file.file_id = file_info.file_id";
if ($form['q'] != '') {
$query_array_array = ACSLib::get_query_array_array($form['q']);
$where_sql = '';
foreach ($query_array_array as $query_array) {
if (!count($query_array)) {
continue;
}
$sub_where_sql = '';
foreach ($query_array as $query) {
$query = pg_escape_string($query);
ACSLib::escape_ilike($query);
if ($sub_where_sql != '') {
$sub_where_sql .= " OR ";
}
$sub_where_sql .= "(";
$sub_where_sql .= " file_info.display_file_name ILIKE '%{$query}%'";
$sub_where_sql .= " OR file_info.comment ILIKE '%{$query}%'";
$sub_where_sql .= ")";
}
if ($sub_where_sql != '') {
if ($where_sql != '') {
$where_sql .= " AND ";
}
$where_sql .= "({$sub_where_sql})";
}
}
if ($where_sql != '') {
$sql .= " AND ({$where_sql})";
}
}
//
// 日数指定がある場合
if ($days !== false) {
$sql = $sql . " AND " . ACSLib::get_sql_condition_from_today("file_info.update_date", $days);
}
// ORDER
if ($rows != false) {
// 表示件数制御 //
$display_count = ACSSystemConfig::get_keyword_value(ACSMsg::get_mst('system_config_group', 'D02'), 'NEW_INFO_TOP_DISPLAY_MAX_COUNT');
$sql = $sql . " OFFSET 0 LIMIT " . $display_count;
} else {
if ($form['order'] == 'update_date') {
$sql .= " ORDER BY file_info.update_date DESC";
} else {
$sql .= " ORDER BY file_info.display_file_name ASC";
}
}
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* 掲示板の新着記事一覧を取得する
*
* @param $user_community_id ユーザコミュニティID (BBSへのアクセス者となるユーザ)
* $days 取得する日数(最近何日間の新着情報を取得)
* @return 新着記事一覧
*
*/
static function get_new_bbs_row_array($user_community_id, $days = false, $offset = false)
{
// マイコミュニティのコミュニティIDのCSVを作成する
$community_row_array = ACSUser::get_community_row_array($user_community_id);
$community_id_array = array();
foreach ($community_row_array as $index => $community_row) {
array_push($community_id_array, $community_row['community_id']);
}
if (count($community_id_array)) {
$community_id_csv = implode(',', $community_id_array);
} else {
$community_id_csv = 'null';
}
//
$user_community_id = pg_escape_string($user_community_id);
// 掲示板の新着記事を最新順に取得する
$sql = "SELECT\n\t\t\tbbs.*, community.*, open_level_master.*,\n\t\t\tCOALESCE(bbs_res_num_n,0) AS bbs_res_num,\n\t\t\tlastts.bbs_last_timestamp AS bbs_last_post_date,\n\t\t\tCASE\n\t\t\t\tWHEN acdate.access_date IS NULL THEN TRUE\n\t\t\t\tWHEN lastts.bbs_last_timestamp > acdate.access_date THEN TRUE\n\t\t\t\tELSE FALSE\n\t\t\tEND AS is_unread\n\t\tFROM\n\t\t\t(((bbs INNER JOIN community\n\t\t\tON bbs.community_id = community.community_id)\n\t\t\t\tINNER JOIN open_level_master\n\t\t\t\tON bbs.open_level_code = open_level_master.open_level_code)\n\t\t\t\t\tLEFT JOIN\n\t\t\t\t\t\t(SELECT bbs_id, count(*) AS bbs_res_num_n\n\t\t\t\t\t\tFROM bbs_res GROUP BY bbs_id) AS rescnt\n\t\t\t\t\tON bbs.bbs_id = rescnt.bbs_id)\n\t\t\t\t\t\tLEFT JOIN acs_view_bbs_last_timestamp AS lastts\n\t\t\t\t\t\tON bbs.bbs_id = lastts.bbs_id\n\t\t\t\t\t\t\tLEFT JOIN\n\t\t\t\t\t\t\t\t(SELECT bbs_id, access_date\n\t\t\t\t\t\t\t\tFROM bbs_access_history\n\t\t\t\t\t\t\t\tWHERE user_community_id='" . $user_community_id . "') AS acdate\n\t\t\t\t\t\t\tON bbs.bbs_id = acdate.bbs_id\n\t\tWHERE\n\t\t\tbbs.community_id IN (" . $community_id_csv . ")\n\t\t\tAND bbs.bbs_delete_flag != 't'";
if ($days !== false) {
$sql .= " AND " . ACSLib::get_sql_condition_from_today("lastts.bbs_last_timestamp", $days);
}
$sql .= " ORDER BY bbs_last_timestamp DESC, bbs.bbs_id DESC";
if ($offset !== false) {
// 表示件数制御 //
$display_count = ACSSystemConfig::get_keyword_value(ACSMsg::get_mst('system_config_group', 'D02'), 'NEW_INFO_TOP_DISPLAY_MAX_COUNT');
$sql = $sql . " OFFSET 0 LIMIT " . $display_count;
} else {
//$sql .= " ORDER BY bbs_last_timestamp DESC, bbs.bbs_id DESC";
}
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* コメントしたダイアリー一覧を取得する (自分のダイアリー以外が対象)
*
* @param $user_community_id ユーザコミュニティID (コメントしたユーザ)
* $days 取得する日数(最近何日間の新着情報を取得)
* @return 新着ダイアリー一覧 (連想配列の配列)
*/
static function get_commented_diary_row_array($user_community_id, $days = false, $offset = false)
{
$user_community_id = pg_escape_string($user_community_id);
// コメントしたダイアリー
$sql = "SELECT DISTINCT diary.diary_id";
$sql .= " FROM diary, diary_comment";
$sql .= " WHERE diary.diary_id = diary_comment.diary_id";
$sql .= " AND diary.community_id != '{$user_community_id}'";
$sql .= " AND diary_comment.user_community_id = '{$user_community_id}'";
$tmp_row_array = ACSDB::_get_row_array($sql);
$commented_diary_id_array = array();
foreach ($tmp_row_array as $tmp_row) {
array_push($commented_diary_id_array, $tmp_row['diary_id']);
}
if (count($commented_diary_id_array)) {
$commented_diary_id_csv = implode(',', $commented_diary_id_array);
} else {
$commented_diary_id_csv = 'null';
}
// コメントしたダイアリーを最新順に取得する
$sql = "SELECT *, acs_get_diary_comment_num(diary.diary_id) as diary_comment_num, acs_get_diary_last_post_date(diary.diary_id) as diary_last_post_date, acs_is_unread_diary_comment('{$user_community_id}', diary.diary_id) as is_unread";
$sql .= " FROM diary, community as USER_COMMUNITY, user_info, open_level_master";
$sql .= " WHERE diary.diary_id IN ({$commented_diary_id_csv})";
$sql .= " AND diary.community_id = USER_COMMUNITY.community_id";
$sql .= " AND USER_COMMUNITY.community_id = user_info.user_community_id";
$sql .= " AND diary.open_level_code = open_level_master.open_level_code";
//------- 2007.2 表示時間短縮対応
// 日数指定がある場合
if ($days !== false) {
$sql = $sql . " AND " . ACSLib::get_sql_condition_from_today("acs_get_diary_last_post_date(diary.diary_id)", $days);
}
if ($offset != false) {
$sql = $sql . " ORDER BY diary_last_post_date DESC";
// 表示件数制御 //
$display_count = ACSSystemConfig::get_keyword_value(ACSMsg::get_mst('system_config_group', 'D02'), 'NEW_INFO_TOP_DISPLAY_MAX_COUNT');
$sql = $sql . " OFFSET 0 LIMIT " . $display_count;
} else {
$sql .= " ORDER BY diary_last_post_date DESC";
}
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}