/**
* ログ情報を検索する
*
* @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 $form 検索条件
* @return ユーザ情報の配列 (連想配列の配列)
*/
static function search_user_info_row_array($form, $open_level_name_array)
{
$sql = "SELECT user_info.*, USER_COMMUNITY.*";
if ($form['order'] == 'friends_num') {
$sql .= ", acs_get_friends_num(USER_COMMUNITY.community_id) AS friends_num";
} elseif ($form['order'] == 'community_num') {
$sql .= ", acs_get_community_num(USER_COMMUNITY.community_id) AS community_num";
}
$sql .= " FROM user_info, community as USER_COMMUNITY,";
// サブクエリ: コンテンツ //
$sql .= " (";
$sql .= " SELECT DISTINCT SUB_USER_COMMUNITY.community_id";
$sql .= "\tFROM community as SUB_USER_COMMUNITY, community_type_master, contents, open_level_master";
$sql .= "\tWHERE SUB_USER_COMMUNITY.community_type_code = community_type_master.community_type_code";
$sql .= "\t AND community_type_master.community_type_name = '" . ACSMsg::get_mst('community_type_master', 'D10') . "'";
$sql .= "\t AND SUB_USER_COMMUNITY.community_id = contents.community_id";
// 公開範囲を限定
$sql .= "\t AND contents.open_level_code = open_level_master.open_level_code";
$open_level_name_where_sql = '';
foreach ($open_level_name_array as $open_level_name) {
if ($open_level_name_where_sql != '') {
$open_level_name_where_sql .= ' OR ';
}
$open_level_name_where_sql .= "open_level_master.open_level_name = '" . pg_escape_string($open_level_name) . "'";
}
$sql .= "\t AND ({$open_level_name_where_sql})";
// 検索条件 //
// キーワード
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 .= " SUB_USER_COMMUNITY.community_name ILIKE '%{$query}%'";
$sub_where_sql .= " OR contents.contents_value 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 .= "\tORDER BY SUB_USER_COMMUNITY.community_id ASC ";
$sql .= " ) as SUB_USER_COMMUNITY";
//
$sql .= " ,";
$sql .= " contents as SELF_C,\t contents_type_master as SELF_CTM, open_level_master as SELF_OLM";
// マイページ全体
$sql .= " WHERE user_info.user_community_id = USER_COMMUNITY.community_id";
$sql .= " AND USER_COMMUNITY.community_id = SUB_USER_COMMUNITY.community_id";
// 削除フラグOFF
$sql .= " AND USER_COMMUNITY.delete_flag != 't'";
// 全体=非公開を除く
$sql .= " AND USER_COMMUNITY.community_id = SELF_C.community_id";
$sql .= " AND SELF_C.contents_type_code = SELF_CTM.contents_type_code";
$sql .= " AND SELF_C.open_level_code = SELF_OLM.open_level_code";
$sql .= " AND SELF_CTM.contents_type_name = '" . ACSMsg::get_mst('contents_type_master', 'D00') . "'";
$sql .= " AND SELF_OLM.open_level_name != '" . ACSMsg::get_mst('open_level_master', 'D03') . "'";
// ORDER
if ($form['order'] == 'community_name') {
$sql .= " ORDER BY USER_COMMUNITY.community_name ASC";
} elseif ($form['order'] == 'friends_num') {
$sql .= " ORDER BY friends_num DESC, user_info.user_id ASC";
} elseif ($form['order'] == 'community_num') {
$sql .= " ORDER BY community_num DESC, user_info.user_id ASC";
} else {
$sql .= " ORDER BY user_info.user_id ASC";
}
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}
/**
* ファイル検索
*
* @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;
}
/**
* BBSを検索するためのwhere句の１部を作成
*
* @param $form 条件
[id] => 1000
[move_id] => 2
[q_text] => 検索条件 //条件（※必須）
[search_title] => title_in_serch //件名を選択
[search_all] => subject_in_serch //本文を選択
[open_level_code] => 00 //公開範囲（00は選択なし）（※必須）
[search_all_about] => all_in_serch //すべてのCommunityを選択
* @return str_array($like_sql , $err_str , $str_count)
*/
static function set_bbs_where_list($form, $flag)
{
$str_array = array();
$str_array['like_sql'] = "";
//成功時の戻り値
$str_array['err_str'] = "";
//失敗時の戻り値
$str_array['str_count'] = 1;
//成功時のキーワード数
// 検索キーワードの取得（注意！バイト単位で処理）
$search_text = $form['q_text'];
//formから検索文字列を取得
//〜〜〜〜〜〜〜〜〜〜〜〜〜〜配列として利用(Likeの作成)〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
if ($search_text != '') {
$search_args = ACSLib::get_query_array_array($search_text);
//検索文字列配列に変換
}
if (count($search_args) > 0) {
//検索文字列が存在する場合
$search_args = ACSLib::escape_sql_array($search_args);
//キーワードwhere句の追記
//次のいずれかは必須
$str_like = '';
//件名を選択している場合
if ($form['search_title']) {
$str_title = " bbs.subject";
$str_like_array = ACSDiary::create_sql_where_field($search_args, $str_title, $flag);
$str_like = " (" . $str_like_array['str_like'] . ") ";
$str_array['str_count'] = $str_like_array['str_count'];
}
//本文を選択している場合
if ($form['search_all']) {
$str_body = " bbs.body";
if ($str_like != "") {
$str_like = $str_like . " OR ";
//件名と共に選択している場合
}
$where_sql_array = ACSDiary::create_sql_where_field($search_args, $str_body, $flag);
$where_sql = " (" . $where_sql_array['str_like'] . ") ";
$str_like = $str_like . $where_sql;
$str_array['str_count'] = $where_sql_array['str_count'];
}
//キーワードwhere句の追記終了
if ($str_like == "") {
//キーワードがあるのに、対象がない場合
$str_array['err_str'] = ACSMsg::get_mdmsg(__FILE__, 'M005');
} else {
$str_array['like_sql'] = $str_array['like_sql'] . $str_array['like_sql'] . " AND (" . $str_like . ") ";
}
}
//〜〜〜〜〜〜〜〜〜〜〜〜〜〜特定日記検索〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
//必ずAND
if (!$form['search_all_about']) {
//すべての Communityを選択していない場合
$str_array['like_sql'] = $str_array['like_sql'] . " AND bbs.community_id = " . $form['community_id'];
}
// エラーメッセージが配列になってしまう暫定対処
return $str_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 $user_community_id アクセス者のユーザコミュニティID (非公開コミュニティも検索結果に含めるため)
* @param $form 検索条件
* @return コミュニティ情報の配列
*/
static function search_community_row_array($user_community_id = '', $form = array())
{
$sql = "SELECT *";
if ($form['order'] == 'community_member_num') {
$sql .= ", acs_get_community_member_num(community.community_id) as community_member_num";
}
$sql .= " FROM community, community_type_master,";
$sql .= " category_master,";
// カテゴリ
$sql .= " contents as SELF_C, contents_type_master as SELF_CTM,";
// 全体
$sql .= " open_level_master as SELF_OLM,";
// 全体のopen_level_master
$sql .= " contents as COMMUNITY_PROFILE_C, contents_type_master as COMMUNITY_PROFILE_CTM";
// コミュニティプロフィール(概要)
$sql .= " WHERE community.community_type_code = community_type_master.community_type_code";
$sql .= " AND community_type_master.community_type_name = '" . ACSMsg::get_mst('community_type_master', 'D40') . "'";
// 削除フラグOFF
$sql .= " AND community.delete_flag != 't'";
$sql .= " AND community.community_id = SELF_C.community_id";
$sql .= " AND SELF_C.contents_type_code = SELF_CTM.contents_type_code";
$sql .= " AND SELF_C.open_level_code = SELF_OLM.open_level_code";
$sql .= " AND SELF_CTM.contents_type_name = '" . ACSMsg::get_mst('contents_type_master', 'D00') . "'";
$sql .= " AND (";
$sql .= " (";
// 非公開コミュニティでない
$sql .= " SELF_OLM.open_level_name != '" . ACSMsg::get_mst('open_level_master', 'D03') . "'";
$sql .= " )";
if ($user_community_id) {
$sql .= " OR (";
$sql .= " SELF_OLM.open_level_name = '" . ACSMsg::get_mst('open_level_master', 'D03') . "'";
$sql .= " AND acs_is_community_member('{$user_community_id}', community.community_id)";
$sql .= " )";
}
$sql .= " )";
// コミュニティプロフィール
$sql .= " AND community.community_id = COMMUNITY_PROFILE_C.community_id";
$sql .= " AND COMMUNITY_PROFILE_C.contents_type_code = COMMUNITY_PROFILE_CTM.contents_type_code";
$sql .= " AND COMMUNITY_PROFILE_CTM.contents_type_name = '" . ACSMsg::get_mst('contents_type_master', 'D07') . "'";
// 検索条件 //
// キーワード
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 .= " community.community_name ILIKE '%{$query}%'";
$sub_where_sql .= " OR COMMUNITY_PROFILE_C.contents_value ILIKE '%{$query}%'";
$sub_where_sql .= " OR category_master.category_name 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 .= " AND community.category_code = category_master.category_code";
if ($form['category_code']) {
$sql .= " AND community.category_code = '" . pg_escape_string($form['category_code']) . "'";
}
// 参加資格
if ($form['admission_flag'] == 't' || $form['admission_flag'] == 'f') {
$sql .= " AND community.admission_flag = '{$form['admission_flag']}'";
}
// ORDER
if ($form['order'] == 'new') {
$sql .= " ORDER BY community.register_date DESC";
} elseif ($form['order'] == 'community_member_num') {
$sql .= " ORDER BY community_member_num DESC, community.community_name ASC";
} else {
$sql .= " ORDER BY community.community_name ASC";
}
$row_array = ACSDB::_get_row_array($sql);
return $row_array;
}