/**
* 掲載中のシステムアナウンス情報一覧を取得する
*
* @return システムアナウンス情報一覧
*/
static function get_all_system_announce_row_array()
{
$sql = "SELECT *, acs_is_expire_date(system_announce.expire_date) as is_expire";
$sql .= " FROM (system_announce LEFT OUTER JOIN community ON system_announce.user_community_id = community.community_id)";
$sql .= " ORDER BY system_announce.system_announce_id DESC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* ファイルコンテンツ種別リストを取得する
*
* @param
* @return
*/
static function get_file_contents_type_list_row_array($file_category_code)
{
$file_category_code = pg_escape_string($file_category_code);
$sql = "SELECT *";
$sql .= " FROM file_contents_type_list";
$sql .= " WHERE file_contents_type_list.file_category_code = '{$file_category_code}'";
$sql .= " ORDER BY file_contents_type_list.file_contents_type_code ASC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* ファイル履歴コメント一覧を取得する
*
* @param $file_history_id ファイル履歴ID
* @param $entry_user_community_id 登録者のユーザコミュニティID
* @param $comment コメント
* @return
*/
static function get_file_history_comment_row_array($file_history_id)
{
$file_history_id = pg_escape_string($file_history_id);
$sql = "SELECT *";
$sql .= " FROM file_history_comment, community as USER_COMMUNITY";
$sql .= " WHERE file_history_comment.file_history_id = '{$file_history_id}'";
$sql .= " AND file_history_comment.user_community_id = USER_COMMUNITY.community_id";
$sql .= " ORDER BY file_history_comment.post_date ASC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* ファイル履歴情報一覧を取得する
*
* @param $file_id ファイルID
* @return ファイル履歴情報の配列 (連想配列の配列)
*/
static function get_file_history_row_array($file_id)
{
$file_id = pg_escape_string($file_id);
$sql = "SELECT *";
$sql .= " FROM file_history, file_history_operation_master, community as USER_COMMUNITY";
$sql .= " WHERE file_history.file_id = '{$file_id}'";
$sql .= " AND file_history.file_history_operation_code = file_history_operation_master.file_history_operation_code";
$sql .= " AND file_history.update_user_community_id = USER_COMMUNITY.community_id";
$sql .= " ORDER BY file_history.update_date DESC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* 画像ファイル情報取得（複数）
* ひとりのユーザが持つ画像情報のすべて
* @param $file_id_array
*/
static function select_community_image_row_array($community_id)
{
$sql = "SELECT community_image_file.*,";
$sql .= " file_info.display_file_name AS save_file_name,file_info.server_file_name AS insystem_file_name,";
$sql .= " file_info.mime_type AS file_kind";
//種類
$sql .= " FROM file_info, community_image_file";
$sql .= " WHERE community_id = " . $community_id . ")";
$sql .= " AND file_info.owner_community_id = community_image_file.community_id";
$sql .= " ORDER BY community_image_file.file_id ASC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* ログ情報を検索する
*
* @param $form 検索条件
* @return ログ情報の配列 (連想配列の配列)
*/
static function search_log_row_array($form)
{
$sql = "SELECT *";
$sql .= " FROM log, operation_master";
$sql .= " WHERE log.operation_code = operation_master.operation_code";
// 検索条件 //
// キーワード
if ($form['q'] != '') {
$sqlfunc = "acs_convert_timestamp_" . ACSMsg::get_lang();
$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 .= " log.log_id ILIKE '%{$query}%'";
//$sub_where_sql .= " OR acs_convert_timestamp_to_jdate(log.log_date, 'YYYY/MM/DD', 'FMHH24:MI:SS') ILIKE '%$query%'";
$sub_where_sql .= " OR " . $sqlfunc . "(log.log_date, 'YYYY/MM/DD', 'FMHH24:MI:SS') ILIKE '%{$query}%'";
$sub_where_sql .= " OR log.user_id ILIKE '%{$query}%'";
$sub_where_sql .= " OR log.user_name ILIKE '%{$query}%'";
$sub_where_sql .= " OR log.community_name ILIKE '%{$query}%'";
$sub_where_sql .= " OR (CASE WHEN administrator_flag = 't' THEN '" . ACSMsg::get_mdmsg(__FILE__, 'M001') . "' ELSE '" . ACSMsg::get_mdmsg(__FILE__, 'M002') . "' END) ILIKE '%{$query}%'";
$sub_where_sql .= " OR operation_master.operation_name ILIKE '%{$query}%'";
$sub_where_sql .= " OR log.message ILIKE '%{$query}%'";
$sub_where_sql .= " OR (CASE WHEN log.operation_result = 't' THEN '" . ACSMsg::get_mdmsg(__FILE__, 'M003') . "' ELSE '" . ACSMsg::get_mdmsg(__FILE__, 'M004') . "' END) 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})";
}
}
//
$sql .= " ORDER BY log_id DESC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* ファイル情報取得（複数）
*
* @param $file_id_array
*/
static function select_file_info_row_array($file_id_array)
{
$target_file_id = implode(", ", $file_id_array);
$sql = "SELECT file_info.*,";
$sql .= " ENTRY_USER_COMMUNITY.community_name AS entry_user_community_name,";
$sql .= " UPDATE_USER_COMMUNITY.community_name AS update_user_community_name";
$sql .= " FROM file_info, community AS ENTRY_USER_COMMUNITY, community AS UPDATE_USER_COMMUNITY";
$sql .= " WHERE file_id IN (" . $target_file_id . ")";
$sql .= " AND file_info.entry_user_community_id = ENTRY_USER_COMMUNITY.community_id";
$sql .= " AND file_info.update_user_community_id = UPDATE_USER_COMMUNITY.community_id";
$sql .= " ORDER BY display_file_name ASC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* 待機コミュニティメンバ情報を取得する
*
* @param $community_id 対象のコミュニティIDまたはユーザコミュニティID (承認側)
* @param 待機種別名
* @param 待機状態名
* @return 待機情報 (連想配列の配列)
*/
static function get_waiting_row_array($community_id, $waiting_type_name, $waiting_status_name)
{
$community_id = pg_escape_string($community_id);
$waiting_type_name = pg_escape_string($waiting_type_name);
$waiting_status_name = pg_escape_string($waiting_status_name);
$sql = "SELECT *";
$sql .= " FROM waiting, community, waiting_type_master, waiting_status_master";
$sql .= " WHERE waiting.community_id = '{$community_id}'";
$sql .= " AND waiting.waiting_community_id = community.community_id";
$sql .= " AND waiting.waiting_type_code = waiting_type_master.waiting_type_code";
$sql .= " AND waiting_type_master.waiting_type_name = '{$waiting_type_name}'";
$sql .= " AND waiting.waiting_status_code = waiting_status_master.waiting_status_code";
$sql .= " AND waiting_status_master.waiting_status_name = '{$waiting_status_name}'";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* システム設定情報取得
*
* @parma $sql_where
*/
static function select_system_config_row_array($sql_where = "")
{
$sql = "SELECT";
$sql .= " system_config_group.system_config_group_code, system_config_group.system_config_group_name,";
$sql .= " system_config.keyword, system_config.value, system_config.type";
$sql .= " FROM system_config_group, system_config";
$sql .= " WHERE";
$sql .= " system_config_group.system_config_group_code = system_config.system_config_group_code";
if ($sql_where) {
// 条件の指定がある場合のみ、追加
$sql .= " AND " . $sql_where;
}
$sql .= " ORDER BY system_config_group.display_order, system_config.display_order";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* 送信済メッセージの一覧を取得する
*
* @param $user_community_id ユーザコミュニティID
* @return 送信済メッセージの一覧 (連想配列の配列)
*/
static function get_send_message_row_array($user_community_id)
{
$user_community_id = pg_escape_string($user_community_id);
$sql = "SELECT message.message_id";
$sql .= ", message.subject";
$sql .= ", message.post_date";
$sql .= ", message_sender.message_sender_id";
$sql .= ", message_receiver.read_flag";
$sql .= ", community.community_id AS user_id";
$sql .= ", community.community_name AS user_name";
$sql .= " FROM message, message_sender, message_receiver, community";
$sql .= " WHERE message_sender.community_id = '{$user_community_id}'";
$sql .= " AND message_receiver.community_id = community.community_id";
$sql .= " AND message_sender.message_id = message.message_id";
$sql .= " AND message.message_id = message_receiver.message_id";
$sql .= " AND message_sender.message_delete_flag = 'f'";
$sql .= " ORDER BY message.post_date DESC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* 公開範囲マスタ取得
*
* @return open_level_code をキーにした配列
*/
static function get_all_open_level_master_row_array()
{
$sql = "SELECT *";
$sql .= " FROM open_level_master";
$row_array = ACSDB::_get_row_array($sql);
// set true or false
$role_array = array('public', 'user', 'member', 'administrator');
foreach ($row_array as $index => $row) {
$open_level_code = $row['open_level_code'];
$new_row_array[$open_level_code]['open_level_name'] = $row['open_level_name'];
foreach ($role_array as $role_key) {
$new_row_array[$open_level_code]["open_for_{$role_key}"] = $row["open_for_{$role_key}"];
}
}
return $new_row_array;
}
/**
* スケジュール一覧インスタンス生成
*
* 戻り値は、array([user_community_id] => ACSScheduleParticipantインスタンス)
*
* @param string $schedule_id 該当のスケジュールid
* @param boolean $is_target_kind_all 全員参加モードで取得するかどうか
* @return array ACSScheduleParticipantインスタンスの配列
*/
function &get_schedule_participant_instance_list($schedule_id, $is_target_kind_all)
{
if ($is_target_kind_all) {
$sql = "SELECT " . " sc.schedule_id, " . " mmb.user_community_id, " . " pa.participant_comment, " . " pa.participant_delete_flag, " . " cmm.community_name AS user_community_name " . "FROM (((schedule AS sc " . "LEFT JOIN community AS cm " . " ON sc.community_id = cm.community_id) " . "LEFT JOIN community_member AS mmb " . " ON sc.community_id = mmb.community_id) " . "LEFT JOIN community AS cmm " . " ON mmb.user_community_id = cmm.community_id) " . "LEFT JOIN schedule_participant AS pa " . " ON sc.schedule_id = pa.schedule_id " . " AND mmb.user_community_id = pa.user_community_id " . "WHERE sc.schedule_id = " . pg_escape_string($schedule_id) . " AND cmm.delete_flag != 't'";
" ORDER BY mmb.user_community_id ";
} else {
$sql = "SELECT " . " pa.schedule_id, " . " pa.user_community_id, " . " pa.participant_comment, " . " pa.participant_delete_flag, " . " cm.community_name AS user_community_name " . "FROM schedule_participant AS pa " . "LEFT JOIN community AS cm " . " ON pa.user_community_id = cm.community_id " . "WHERE pa.schedule_id = " . pg_escape_string($schedule_id) . " AND pa.participant_delete_flag = FALSE " . " AND cm.delete_flag != 't'";
" ORDER BY pa.user_community_id ";
}
$participant_rows = ACSDB::_get_row_array($sql);
$sql = "SELECT * FROM schedule_answer " . "WHERE schedule_id = " . pg_escape_string($schedule_id) . " ORDER BY user_community_id, adjustment_date_id";
$answer_res = ACSDB::_get_res($sql);
$answer_all_rows = array();
while ($row = $answer_res->fetchRow(DB_FETCHMODE_ASSOC)) {
$answer_all_rows[$row['user_community_id']][] = $row;
}
$answer_res->free();
$schedule_participant_array = array();
foreach ($participant_rows as $participant_row) {
$user_community_id = $participant_row['user_community_id'];
$schedule_participant =& new ACSScheduleParticipant($schedule_id, $user_community_id);
$schedule_participant->initialize($participant_row, $answer_all_rows[$user_community_id]);
$schedule_participant_array[$user_community_id] = $schedule_participant;
}
return $schedule_participant_array;
}
/**
* $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;
}
/**
* 各スケジュールの参加、回答人数の取得
*
* FREEでの人数情報となります。<br>
* ALLは別途コミュニティ参加者数を取得する必要があります。
*
* @param string $community_id コミュニティid(未指定も可)
* @param string $schedule_id スケジュールid(未指定も可)
* @return array 参加回答人数配列
* array([schedule_id]=> array(
* 'participate_person_count' => [参加人数]
* 'answer_person_count' => [回答人数] ))
*/
function &get_total_person_count($community_id = '', $schedule_id = '')
{
$where = '';
if ($community_id != '') {
$where .= $where == '' ? '' : ' AND ';
$where .= 'SCH.community_id = ' . pg_escape_string($community_id);
}
if ($schedule_id != '') {
$where .= $where == '' ? '' : ' AND ';
$where .= 'SCH.schedule_id = ' . pg_escape_string($schedule_id);
}
$where = $where == '' ? '' : 'WHERE ' . $where;
$sql = "SELECT " . " SCH.schedule_id, " . " PAT.cnt AS participate_person_count, " . " ANS.cnt AS answer_person_count " . "FROM " . "( " . " schedule AS SCH LEFT JOIN " . " ( " . " SELECT schedule_id, COUNT(*) AS cnt FROM schedule_participant " . " WHERE participant_delete_flag = FALSE GROUP BY schedule_id " . " ) AS PAT " . " ON SCH.schedule_id = PAT.schedule_id " . ") " . "LEFT JOIN " . "( " . " SELECT schedule_id, COUNT(*) AS cnt FROM " . " ( " . " SELECT DISTINCT schedule_id, user_community_id " . " FROM schedule_answer " . " ) AS SBUQ " . " GROUP BY schedule_id " . ") AS ANS " . "ON SCH.schedule_id = ANS.schedule_id " . " " . $where . " " . "ORDER BY SCH.schedule_id ";
$rows = ACSDB::_get_row_array($sql);
$persons_count_array = array();
foreach ($rows as $row) {
$persons_count_array[$row['schedule_id']] = array('participate_person_count' => $row['participate_person_count'] == '' ? 0 : $row['participate_person_count'], 'answer_person_count' => $row['answer_person_count'] == '' ? 0 : $row['answer_person_count']);
}
return $persons_count_array;
}
/**
* ログイン情報を取得する
*
* @param ユーザID
* @return ログイン情報 (連想配列)
*/
static function get_login_info_row_array($user_id)
{
static $cache_rows;
$user_id = pg_escape_string($user_id);
if (is_array($cache_rows[$user_id])) {
return $cache_rows[$user_id];
}
$sql = "SELECT community.community_id,";
$sql .= "\t\tcommunity.community_name,";
$sql .= "\t\tcontents.contents_value,";
$sql .= "\t\tuser_info.user_id,";
$sql .= "\t\tlogin_info.login_date,";
$sql .= "\t\tlogin_info.logout_date,";
$sql .= "\t\tlogin_info.use_button_flg";
$sql .= " FROM community,";
$sql .= " contents,";
$sql .= " user_info,";
$sql .= " login_info";
$sql .= " WHERE community.community_id = '{$user_id}'";
$sql .= " AND community.community_id = login_info.community_id";
$sql .= " AND community.community_id = user_info.user_community_id";
$sql .= " AND community.community_id = contents.community_id";
$sql .= " AND contents.contents_type_code = '01'";
$sql .= " ORDER BY login_info.login_date DESC";
$row = ACSDB::_get_row_array($sql);
$cache_rows[$user_id] = $row;
return $row;
}
/**
* ファイル検索
*
* @param $user_community_id
* @param $form 検索条件
* @return フォルダ情報の配列
*/
function search_file_info_row_array($user_community_id, $form)
{
$user_community_id = pg_escape_string($user_community_id);
$sql = "SELECT *";
$sql .= " FROM folder, folder_file, file_info";
$sql .= " WHERE folder.community_id = '{$user_community_id}'";
$sql .= " AND folder.folder_id = folder_file.folder_id";
$sql .= " AND folder_file.file_id = file_info.file_id";
$sql .= " AND file_info.owner_community_id = '{$user_community_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})";
}
}
//
// ORDER
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;
}
/**
* 最新の掲示板親記事一覧を取得する (掲示板RSS用)
*
* @param $community_id ユーザコミュニティID
* @param $term 取得期間
* @return 掲示版親記事の一覧 (連想配列の配列)
*/
static function get_new_bbs_rss_row_array($community_id, $term)
{
$community_id = pg_escape_string($community_id);
$term = pg_escape_string($term);
// 掲示板の新着記事を最新順に取得する
$sql = "SELECT *, bbs.bbs_id as bbs_id, acs_get_bbs_last_post_date(bbs.bbs_id) as bbs_last_post_date";
$sql .= " FROM (bbs LEFT OUTER JOIN bbs_file ON bbs.bbs_id = bbs_file.bbs_id), community, open_level_master";
$sql .= " WHERE bbs.community_id = '{$community_id}'";
$sql .= " AND bbs.community_id = community.community_id";
$sql .= " AND bbs.open_level_code = open_level_master.open_level_code";
// 削除フラグOFF
$sql .= " AND bbs.bbs_delete_flag != 't'";
// $term日以内に投稿のあった記事を取得
$sql .= " AND acs_get_bbs_last_post_date(bbs.bbs_id)::DATE > (CURRENT_DATE - '@ {$term} days'::INTERVAL)";
// 最終投稿日時でソート
$sql .= " ORDER BY bbs_last_post_date DESC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* ファイル情報取得
*
* @param $folder_id_array
*/
static function select_folder_file_info_row_array($folder_id_array)
{
/* フォルダファイルを取得 */
$target_folder_id = implode(", ", $folder_id_array);
$folder_id_sql = "SELECT *";
$folder_id_sql .= " FROM folder_file";
$folder_id_sql .= " WHERE folder_id IN (" . $target_folder_id . ")";
$folder_file_row_array = ACSDB::_get_row_array($folder_id_sql);
/* フォルダIDを配列に格納 */
$file_id_array = array();
foreach ($folder_file_row_array as $folder_file_row) {
array_push($file_id_array, $folder_file_row['file_id']);
}
/* ファイル情報を取得 */
if (count($file_id_array) > 0) {
$row_array = ACSFileInfoModel::select_file_info_row_array($file_id_array);
}
return $row_array;
}
/**
* 全コミュニティプットファイル検索
*
* @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 $community_id_array コミュニティID配列
* @return コミュニティ情報の配列
*/
static function get_each_community_row_array($community_row_array)
{
if (count($community_row_array)) {
$community_id_csv = implode(',', $community_row_array);
} else {
$community_id_csv = 'null';
}
$sql = "SELECT *";
$sql .= " FROM ((community LEFT OUTER JOIN category_master ON community.category_code = category_master.category_code)";
$sql .= " LEFT OUTER JOIN community_image_file USING(community_id)) as JOINED_COMMUNITY,";
$sql .= " community_type_master";
$sql .= " WHERE JOINED_COMMUNITY.community_id IN( " . $community_id_csv . " )";
$sql .= " AND JOINED_COMMUNITY.community_type_code = community_type_master.community_type_code";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* RSSフィードURLをセットしているコミュニティ情報一覧を取得する
*
* @param $community_id コミュニティID
* @return コミュニティ情報一覧 (連想配列の配列)
*/
static function get_external_rss_community_row_array()
{
$sql = "SELECT community.community_id, community.community_name";
$sql .= " FROM community, contents, contents_type_master";
$sql .= " WHERE community.delete_flag = 'f'";
$sql .= " AND community.community_id = contents.community_id";
$sql .= " AND contents.contents_type_code = contents_type_master.contents_type_code";
$sql .= " AND contents.contents_value IS NOT NULL";
$sql .= " AND contents.contents_value != ''";
$sql .= " AND contents_type_master.contents_type_name = '" . ACSMsg::get_mst('contents_type_master', 'D63') . "'";
$sql .= " ORDER BY community.community_id";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* 最新のダイアリー一覧を取得する (ダイアリーRSS用)
*
* @param $user_community_id ユーザコミュニティID
* @param $term 取得期間
* @return ダイアリー親記事の一覧 (連想配列の配列)
*/
static function get_new_diary_rss_row_array($user_community_id, $term)
{
$user_community_id = pg_escape_string($user_community_id);
$term = pg_escape_string($term);
$sql = "SELECT *, acs_get_diary_comment_num(diary.diary_id) as diary_comment_num, diary.diary_id as diary_id";
$sql .= " FROM diary LEFT OUTER JOIN diary_file USING(diary_id)";
$sql .= ", community as USER_COMMUNITY, user_info, open_level_master";
$sql .= " WHERE diary.community_id = '{$user_community_id}'";
$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";
$sql .= " AND diary.diary_delete_flag = 'f'";
// $term日以内の投稿を取得
$sql .= " AND diary.post_date::DATE > (CURRENT_DATE - '@ {$term} days'::INTERVAL)";
$sql .= " ORDER BY diary.post_date DESC";
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
