Exemple #1
0
 /**
  * ログ情報を検索する
  *
  * @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;
 }
Exemple #2
0
 /**
  * ユーザ情報を検索する
  *
  * @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;
 }
Exemple #4
0
 /**
  * BBSを検索するためのwhere句の1部を作成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;
             //1キーワード中に値が無い場合(区切り文字?)
         }
         foreach ($query_array as $id => $str_q) {
             //1キーワードに対する処理(文字種のため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;
 }