/** * $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; }