/**
* ログ情報を検索する
*
* @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句の１部を作成2
* 件名と本文がそれぞれ単独指定・複合指定になる可能性を持つため
* 列ごとのｷｰﾜｰﾄﾞを繰り返す処理のみを行う関数
*
* @param $query_array_array ｷｰﾜｰﾄﾞ配列
* @param $str_title 列名
* @return $where_sql 作成したSQL条件文
*/
static function create_sql_where_field($query_array_array, $str_field, $flag)
{
$str_like = "";
foreach ($query_array_array as $query_array) {
//全ｷｰﾜｰﾄﾞに対する処理
if (!count($query_array)) {
continue;
//１ｷｰﾜｰﾄﾞ中に値が無い場合（区切り文字？）
}
foreach ($query_array as $id => $str_q) {
//１ｷｰﾜｰﾄﾞに対する処理（文字種のためor処理）
$str_q = pg_escape_string($str_q);
ACSLib::escape_ilike($str_q);
if ($id == 0) {
$str_like = $str_field . " LIKE '%" . $str_q . "%'";
} else {
$where_sql['str_count'] = 2;
$str_like = $str_like . " OR " . $str_field . " LIKE '%" . $str_q . "%'";
}
}
if ($str_like != '') {
//全ｷｰﾜｰﾄﾞをANDかORでつなげる
if ($where_sql['str_like'] != '') {
switch ($flag) {
case 1:
//すべて合致
$where_sql['str_like'] = $where_sql['str_like'] . " AND ({$str_like})";
break;
case 2:
//すべて合致せず
$where_sql['str_like'] = $where_sql['str_like'] . " OR ({$str_like})";
}
} else {
$where_sql['str_like'] = " ({$str_like})";
}
}
}
return $where_sql;
}
/**
* 全コミュニティプットファイル検索
*
* @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;
}