/** * itemを取得する * * @param int $item_id 項目ID * @return array * @access public */ function &getItems($where_params = null, $order_params = null, $limit = null, $offset = null, $func = null, $func_param = null) { $sql = "SELECT {items}.*, {items_options}.options,{items_options}.default_selected" . " FROM {items} " . " LEFT JOIN {items_options} ON ({items}.item_id={items_options}.item_id)"; $sql .= $this->_db->getWhereSQL($params, $where_params); $sql .= $this->_db->getOrderSQL($order_params); $result = $this->_db->execute($sql, $params, $limit, $offset, true, $func, $func_param); if ($result === false) { // エラーが発生した場合、エラーリストに追加 $this->_db->addError(); return $result; } return $result; }
/** * 写真一覧データを取得する * * @param string $offset 取得開始行 * @return array アルバム一覧データ配列 * @access public */ function &getPhotos($limit = null, $offset = null) { $params = array($this->_request->getParameter("album_id")); $container =& DIContainerFactory::getContainer(); $actionChain =& $container->getComponent("ActionChain"); $actionName = $actionChain->getCurActionName(); if ($actionName == "photoalbum_action_main_photo_sequence") { $sql = "SELECT photo_id " . "FROM {photoalbum_photo} " . "WHERE album_id = ?"; $photos = $this->_db->execute($sql, $params, $limit, $offset, true, array($this, "_checkPhotoSequence")); } else { $sort = $this->_request->getParameter("sort"); $album = $this->_request->getParameter("album"); if ($album["vote_flag"] != _ON && $sort == PHOTOALBUM_ALBUM_SORT_VOTE) { unset($sort); } if (!isset($sort) || $sort == PHOTOALBUM_PHOTO_SORT_NONE) { $orderParams["photo_sequence"] = "ASC"; } elseif ($sort == PHOTOALBUM_PHOTO_SORT_DATE_DESC) { $orderParams["insert_time"] = "DESC"; } elseif ($sort == PHOTOALBUM_PHOTO_SORT_DATE_ASC) { $orderParams["insert_time"] = "ASC"; } elseif ($sort == PHOTOALBUM_PHOTO_SORT_PHOTO_NAME) { $orderParams["photo_name"] = "ASC"; } elseif ($sort == PHOTOALBUM_PHOTO_SORT_VOTE) { $orderParams["photo_vote_count"] = "DESC"; } $sql = "SELECT photo_id, photo_name, photo_sequence, photo_path, width, height, " . "photo_vote_count, photo_description, insert_user_id " . "FROM {photoalbum_photo} " . "WHERE album_id = ? " . $this->_db->getOrderSQL($orderParams); $photos = $this->_db->execute($sql, $params, $limit, $offset, true, array($this, "_makePhotoArray")); } if ($photos === false) { $this->_db->addError(); } return $photos; }
/** * 解答一覧データを取得する * * @param array $statistics 統計データ配列 * @return array 解答一覧データ配列 * @access public */ function &getSummaries($statistics = null) { if (empty($statistics)) { $statistics = $this->getStatistics(); } if (empty($statistics)) { return $statistics; } $limit = $this->_request->getParameter("limit"); $offset = $this->_request->getParameter("offset"); $sortColumn = $this->_request->getParameter("sort_col"); if (empty($sortColumn)) { $sortColumn = "summary_id"; } $sortDirection = $this->_request->getParameter("sort_dir"); if (empty($sortDirection)) { $container =& DIContainerFactory::getContainer(); $session =& $container->getComponent("Session"); $sortDirection = $session->getParameter("_mobile_flag") == _ON ? "DESC" : "ASC"; } $orderParams[$sortColumn] = $sortDirection; $chiefItemShow = $this->_request->getParameter("chiefItemShow"); $chiefSelect = ""; $chiefWhere = ""; if ($chiefItemShow) { $chiefSelect = ", insert_user_id, insert_user_name "; } else { $chiefWhere = "AND insert_user_id = ? "; } $params = array($this->_request->getParameter("quiz_id")); if (!$chiefItemShow) { $container =& DIContainerFactory::getContainer(); $session =& $container->getComponent("Session"); $params[] = $session->getParameter("_user_id"); } $sql = "SELECT summary_id, answer_flag, answer_number, summary_score, answer_time " . $chiefSelect . "FROM {quiz_summary} " . "WHERE quiz_id = ? " . $chiefWhere . $this->_db->getOrderSQL($orderParams); $summaries = $this->_db->execute($sql, $params, $limit, $offset, true, array($this, "_makeSummaries"), $statistics); if ($summaries === false) { $this->_db->addError(); return $summaries; } //非会員回答処理 if ($chiefItemShow) { $container =& DIContainerFactory::getContainer(); $filterChain =& $container->getComponent("FilterChain"); $smartyAssign =& $filterChain->getFilterByName("SmartyAssign"); foreach (array_keys($summaries) as $summari_id) { if (empty($summaries[$summari_id]["insert_user_name"])) { $summaries[$summari_id]["insert_user_name"] = $smartyAssign->getLang("quiz_nonmember_answer"); } } } return $summaries; }
/** * 下の階層のpageのリストを求める * @param array $page * @access private */ function _getChildPages($page) { $where_params = array("room_id = " . intval($page['room_id']) . " OR parent_id = " . intval($page['room_id']) => null); $order_params = array("thread_num" => "ASC"); $sql = "SELECT {pages}.* FROM {pages} "; $params = array(); $sql .= $this->_db->getWhereSQL($params, $where_params); $sql .= $this->_db->getOrderSQL($order_params); $parent_pages = $this->_db->execute($sql, $params, null, null, true, array($this, "_fetchcallbackGetChildPages"), $page); return $parent_pages; }
/** * pages,pages_users_linkの取得 * @param array $where_params * @param array $order_params * @param int $limit * @param int $start * @param function $func * @param array $func_param * @access public */ function &getPagesUsers($where_params = null, $order_params = null, $limit = 0, $start = 0, $func = null, $func_param = null) { $params = array(); $sql = "SELECT {pages}.*, {pages_users_link}.user_id, {authorities}.user_authority_id AS authority_id,{authorities}.hierarchy, {pages_users_link}.role_authority_id, {pages_users_link}.createroom_flag " . " FROM {pages} " . " LEFT JOIN {pages_users_link} ON {pages}.room_id = {pages_users_link}.room_id " . " LEFT JOIN {authorities} ON {pages_users_link}.role_authority_id = {authorities}.role_authority_id "; $sql .= $this->_db->getWhereSQL($params, $where_params); $sql .= $this->_db->getOrderSQL($order_params); $result = $this->_db->execute($sql, $params, $limit, $start, true, $func, $func_param); if ($result === false) { $this->_db->addError(); return $result; } return $result; }
/** * CSVデータを作成する * * @return boolean true:正常、false:異常 * @access public */ function setCSV() { $sortColumn = "D.insert_time"; $sortDirection = "DESC"; $orderParams[$sortColumn] = $sortDirection; $params = array($this->_request->getParameter("registration_id")); $sql = $this->_getDataSQL() . "WHERE D.registration_id = ? " . $this->_db->getOrderSQL($orderParams); $datas = $this->_db->execute($sql, $params, null, null, true, array($this, "_makeCSV")); if ($datas === false) { $this->_db->addError(); } return true; }
/** * タスクデータ配列を取得する * * @return array タスクデータ配列 * @access public */ function &getTasks() { $todo = $this->_request->getParameter("todo"); if ($todo["used_category"] == _ON) { $orderParams["category_id"] = "ASC"; } $sortColumn = $this->_request->getParameter("sort_col"); $sortDirection = $this->_request->getParameter("sort_dir"); if (empty($sortColumn)) { if ($todo["default_sort"] == TODO_NONE) { $sortColumn = "task_sequence"; } elseif ($todo["default_sort"] == TODO_PRIORITY) { $sortColumn = "priority"; $sortDirection = "DESC"; } elseif ($todo["default_sort"] == TODO_STATE) { $sortColumn = "state"; $sortDirection = "DESC"; } elseif ($todo["default_sort"] == TODO_PERIOD) { $sortColumn = "period"; $sortDirection = "DESC"; } elseif ($todo["default_sort"] == TODO_PROGRESS) { $sortColumn = "progress"; } elseif ($todo["default_sort"] == TODO_TASK_VALUE) { $sortColumn = "task_value"; } } if (empty($sortDirection)) { $sortDirection = "ASC"; } $orderParams[$sortColumn] = $sortDirection; $params = array($this->_request->getParameter("todo_id")); $stateSQL = ""; $targetState = $this->_request->getParameter("target_state"); if (isset($targetState) || $this->_mobile_flag == _ON) { $params[] = intval($targetState); $stateSQL = "AND state = ? "; } $sql = "SELECT task_id, priority, state, period, progress, task_value, insert_user_id, category_id " . "FROM {todo_task} " . "WHERE todo_id = ? " . $stateSQL . $this->_db->getOrderSQL($orderParams); $callbackFunc = array($this, "_makeTaskArray"); if ($todo["used_category"] == _OFF) { $funcParams = array(); } else { $funcParams = array("array_key" => "category"); } $tasks = $this->_db->execute($sql, $params, null, null, true, $callbackFunc, $funcParams); if ($tasks === false) { $this->_db->addError(); } return $tasks; }
/** * ルームに所属するpages_users_linkテーブルを取得する * * @param array $room_id_arr ルームID * @param array $where_params キー名称配列、whereデータ配列 * @param array $order_params キー名称配列、orderデータ配列 * @param integer $offset 取得し始めるレコードのオフセット * @param integer $limit 取得する件数 * @param function $func 各レコード処理で実行させるメソッド * @param array $func_param 各レコード処理で実行させるメソッドの引数 * @return array * @access public */ function &getPagesUsersLinkByRoom($room_id_arr, $where_params = null, $order_params = null, $limit = null, $offset = null, $func = null, $func_param = null) { //if($func == null) $func = array($this, "_getPagesUsersLinkFetchcallback"); $params = array(); $sql = "SELECT {pages_users_link}.*,{authorities}.user_authority_id AS authority_id,{authorities}.hierarchy " . " FROM {pages_users_link} " . " LEFT JOIN {authorities} ON ({pages_users_link}.role_authority_id={authorities}.role_authority_id)" . " WHERE room_id IN (" . implode(",", $room_id_arr) . ") "; $sql .= $this->_db->getWhereSQL($params, $where_params, false); //$sql .= " GROUP BY {pages_users_link}.user_id "; $sql .= $this->_db->getOrderSQL($order_params); $result = $this->_db->execute($sql, $params, $limit, $offset, true, $func, $func_param); if ($result === false) { // エラーが発生した場合、エラーリストに追加 $this->_db->addError(); return $result; } return $result; }
/** * リンクリスト一覧データを取得する * * @return array リンクリスト一覧データ配列 * @access public */ function &getLinklists() { $limit = $this->_request->getParameter("limit"); $offset = $this->_request->getParameter("offset"); $sortColumn = $this->_request->getParameter("sort_col"); if (empty($sortColumn)) { $sortColumn = "linklist_id"; } $sortDirection = $this->_request->getParameter("sort_dir"); if (empty($sortDirection)) { $sortDirection = "DESC"; } $orderParams[$sortColumn] = $sortDirection; $params = array($this->_request->getParameter("room_id")); $sql = "SELECT linklist_id, linklist_name, insert_time, insert_user_id, insert_user_name " . "FROM {linklist} " . "WHERE room_id = ? " . $this->_db->getOrderSQL($orderParams); $linklists = $this->_db->execute($sql, $params, $limit, $offset); if ($linklists === false) { $this->_db->addError(); } return $linklists; }
/** * 課題一覧データを取得する * * @return array 課題一覧データ配列 * @access public */ function &getAssignments() { $sort_col = $this->_request->getParameter("sort_col"); if (empty($sort_col)) { $sort_col = "Assign.assignment_id"; } $sort_dir = $this->_request->getParameter("sort_dir"); if (empty($sort_dir)) { $sort_dir = "DESC"; } $order[$sort_col] = $sort_dir; $limit = $this->_request->getParameter("limit"); $offset = $this->_request->getParameter("offset"); $params = array($this->_request->getParameter("room_id")); $sql = "SELECT Assign.*, COUNT(Submitter.submit_id) AS submit_count" . " FROM {assignment} Assign" . " LEFT JOIN {assignment_submitter} Submitter" . " ON (Assign.assignment_id = Submitter.assignment_id)" . " WHERE Assign.room_id = ?" . " GROUP BY Assign.assignment_id" . " " . $this->_db->getOrderSQL($order); $assignments = $this->_db->execute($sql, $params, $limit, $offset, true); if ($assignments === false) { $this->_db->addError(); } return $assignments; }
/** * ファイルリスト取得 * * @access public */ function getFileList($offset, $limit) { $sql = "SELECT F.*, C.comment " . "FROM {cabinet_file} F "; $sql .= "LEFT JOIN {cabinet_comment} C " . "ON (F.file_id = C.file_id) "; $sql .= "WHERE F.cabinet_id = ? "; $sql .= "AND F.parent_id = ? "; $cabinet_id = $this->_request->getParameter("cabinet_id"); $folder_id = $this->_request->getParameter("folder_id"); $sort_col = $this->_request->getParameter("sort_col"); $sort_dir = $this->_request->getParameter("sort_dir"); if (isset($sort_col)) { if ($sort_col == "comment") { $order_params = array("F.file_type" => $sort_dir == "ASC" ? "DESC" : "ASC", "C." . $sort_col => $sort_dir); } else { $order_params = array("F.file_type" => $sort_dir == "ASC" ? "DESC" : "ASC", "F." . $sort_col => $sort_dir); } } else { $order_params = null; } if (isset($order_params)) { $order_params = array_merge(array("F.file_type" => "DESC"), $order_params); } else { $order_params = array("F.file_type" => "DESC", "F.display_sequence" => "ASC", "F.file_name" => "ASC"); } $sql .= $this->_db->getOrderSQL($order_params); $params = array("F.cabinet_id" => $cabinet_id, "F.parent_id" => $folder_id); if ($folder_id > 0) { if ($offset == 0) { $limit--; } else { $offset--; } } $result = $this->_db->execute($sql, $params, $limit, $offset, true, array($this, "_getFileList")); if ($result === false) { $this->_db->addError(); return $result; } return $result; }
/** * 日誌一覧データを取得する * * @return array 日誌一覧データ配列 * @access public */ function &getJournals() { $request =& $this->_container->getComponent("Request"); $limit = $request->getParameter("limit"); $offset = $request->getParameter("offset"); $sortColumn = $request->getParameter("sort_col"); if (empty($sortColumn)) { $sortColumn = "journal_id"; } $sortDirection = $request->getParameter("sort_dir"); if (empty($sortDirection)) { $sortDirection = "DESC"; } $orderParams[$sortColumn] = $sortDirection; $params = array($request->getParameter("room_id")); $sql = "SELECT journal_id, journal_name, active_flag, insert_time, insert_user_id, insert_user_name " . "FROM {journal} " . "WHERE room_id = ? " . $this->_db->getOrderSQL($orderParams); $result = $this->_db->execute($sql, $params, $limit, $offset); if ($result === false) { $this->_db->addError(); } return $result; }
/** * タイトル一覧取得 * @param array items * @param array order_params * @return array */ function &getMDBTitleList($multidatabase_id, $metadata_title_id, $order_params) { $sql = ""; $params = array("multidatabase_id" => $multidatabase_id); $sql = "SELECT {multidatabase_content}.content_id, {multidatabase_content}.vote_count, " . ($sql .= " {multidatabase_content}.insert_time, {multidatabase_content}.update_time, "); $sql .= " m_content.content AS title, m_file.file_name "; $sql .= " FROM {multidatabase_content} "; $sql .= " LEFT JOIN {multidatabase_metadata_content} m_content "; $sql .= " ON ({multidatabase_content}.content_id = m_content.content_id"; $sql .= " AND m_content.metadata_id = " . $metadata_title_id . ")"; $sql .= " LEFT JOIN {multidatabase_file} m_file "; $sql .= " ON (m_content.metadata_content_id = m_file.metadata_content_id) "; $sql .= $this->_getAuthorityFromSQL(); $sql .= " WHERE {multidatabase_content}.multidatabase_id=? "; $sql .= $this->_getAuthorityWhereSQL($params); $sql .= $this->_db->getOrderSQL($order_params); $result = $this->_db->execute($sql, $params); if ($result === false) { // エラーが発生した場合、エラーリストに追加 $this->_db->addError(); return $result; } return $result; }
/** * メッセージ検索用データを取得する * * @return array メッセージ検索用データ * @access public */ function &generateMessagesQuery($action = 'common', $count = false) { $query = array(); $sortColumn = $this->_request->getParameter("sort_col"); $sortDirection = $this->_request->getParameter("sort_dir"); if (empty($sortColumn)) { $sortColumn = "m.sent_time"; } if (empty($sortDirection)) { $sortDirection = "DESC"; } $orderParams[$sortColumn] = $sortDirection; if ($count) { $sql = "SELECT COUNT(*) AS cnt "; } else { $sql = "SELECT distinct r.receiver_id, m.message_id, m.subject, m.sent_time, m.image_count, " . "m.file_count, m.insert_user_id, m.insert_user_name, m.receivers_list, m.send_all_flag, r.mailbox, " . "r.read_state, r.importance_flag, r.delete_state "; } switch ($action) { case 'search': $search = $this->_session->getParameter("search"); $search_sender = $search["search_sender"]; $search_cc = $search["search_cc"]; $search_subject = $search["search_subject"]; $search_keywords = $search["search_keywords"]; $search_date_from = $search["search_date_from"]; $search_date_to = $search["search_date_to"]; $search_upload_flag = $search["search_upload_flag"]; $search_range = $search["search_range"]; $search_range_prefix = ''; $search_range_value = ''; if (!empty($search_range)) { $search_range_pair = explode(PM_SPLIT_COLON, $search_range); $search_range_prefix = strtoupper($search_range_pair[0]); $search_range_value = $search_range_pair[1]; } // if(!empty($search_sender) || !empty($search_cc)){ if (!empty($search_cc)) { $innerJoinSql = " INNER JOIN {pm_message_receiver} r2 ON r.message_id = r2.message_id "; } else { $innerJoinSql = ""; } if ($search_range_prefix === "TAG") { $params = array("tag_user_id" => $this->_session->getParameter("_user_id"), "tag_id" => intval($search_range_value)); $sql .= "FROM {pm_message_receiver} r " . $innerJoinSql . ", " . "{pm_message} m, {pm_message_tag_link} m2t, {pm_tag} t " . "WHERE r.message_id = m.message_id " . "AND r.receiver_id = m2t.receiver_id " . "AND m2t.tag_id = t.tag_id " . "AND (t.insert_user_id = ? AND m2t.tag_id = ?) "; } else { $params = array(); $sql .= "FROM {pm_message_receiver} r " . $innerJoinSql . ", {pm_message} m " . "WHERE r.message_id = m.message_id "; switch ($search_range_prefix) { case 'MAILBOX': $search_mailbox = intval($search_range_value); if ($search_mailbox == PM_LEFTMENU_TRASHBOX) { $params["is_trashbox"] = _ON; $sql .= "AND r.delete_state = ? "; } else { $params["mailbox"] = $search_mailbox; $params["is_trashbox"] = _OFF; $sql .= "AND r.mailbox = ? AND r.delete_state = ? "; } break; case 'FLAG': $params["flag"] = intval($search_range_value); $sql .= "AND r.importance_flag = ? "; break; case 'READSTATE': $params["read_state"] = intval($search_range_value); $sql .= "AND r.read_state = ? "; break; } } $params["delete_state"] = PM_MESSAGE_STATE_DELETE; $params["receiver_user_id"] = $this->_session->getParameter("_user_id"); $sql .= "AND r.delete_state < ? AND r.receiver_user_id = ? "; if (!empty($search_sender) && !empty($search_cc)) { $params["sender_user_name"] = $search_sender; $params["receiver_user_name"] = $search_cc; $params["receiver1_mailbox1"] = PM_LEFTMENU_OUTBOX; $params["receiver1_mailbox2"] = PM_LEFTMENU_TRASHBOX; $sql .= "AND (m.insert_user_name = ? OR (r2.receiver_user_name = ? AND (r.mailbox = ? OR r.mailbox = ?))) "; } else { if (!empty($search_sender)) { $params["sender_user_name"] = $search_sender; $sql .= "AND m.insert_user_name = ? "; } else { if (!empty($search_cc)) { $params["receiver_user_name"] = $search_cc; $params["receiver1_mailbox1"] = PM_LEFTMENU_OUTBOX; $params["receiver1_mailbox2"] = PM_LEFTMENU_TRASHBOX; $sql .= "AND r2.receiver_user_name = ? AND (r.mailbox = ? OR r.mailbox = ?) "; } } } if (!empty($search_date_from) || !empty($search_date_to)) { $sql_date_format = str_replace(array('Y', 'm', 'd', '/'), array('%Y', '%m', '%d', ''), _DATE_FORMAT); if (!empty($search_date_from)) { $params["insert_time_from"] = $search_date_from; $sql .= "AND date_format(r.insert_time, '" . $sql_date_format . "') >= ? "; } if (!empty($search_date_to)) { $params["insert_time_to"] = $search_date_to; $sql .= "AND date_format(r.insert_time, '" . $sql_date_format . "') <= ? "; } } if ($search_upload_flag == _ON) { $sql .= "AND (m.file_count > 0 OR m.image_count > 0) "; } if (!empty($search_subject)) { $params["subject"] = "%" . trim($search_subject) . "%"; $sql .= "AND m.subject LIKE ? "; } if (!empty($search_keywords)) { $the_keywords = explode(",", $search_keywords); $keywords = array(); for ($i = 0; $i < count($the_keywords); $i++) { $the_keyword = trim($the_keywords[$i]); if (!empty($the_keyword) && !in_array($the_keyword, $keywords)) { $keywords[] = $the_keyword; } } $keywords_cnt = count($keywords); $j = 0; for ($i = 0; $i < $keywords_cnt; $i++) { if ($i == 0) { $sql .= "AND ( "; } //$params["keyword" . $j++] = '%' . strtoupper($keywords[$i]) . '%'; $params["keyword" . $j++] = '%' . strtoupper($keywords[$i]) . '%'; $sql .= "(upper(m.body) LIKE ?) "; if ($i == $keywords_cnt - 1) { $sql .= ") "; } else { $sql .= "OR "; } } } $filter = $this->_request->getParameter("filter"); $filterSql = ""; // 絞り込みSQLを取得 if (!empty($filter)) { $filterSql = $this->_getFilterSql($filter); } $sql = $sql . $filterSql; if (!$count) { $sql .= $this->_db->getOrderSQL($orderParams); } $query['sql'] = $sql; $query['params'] = $params; break; case 'common': default: $mailbox = $this->_request->getParameter("mailbox"); $filter = $this->_request->getParameter("filter"); $filterSql = ""; // 絞り込みSQLを取得 if (!empty($filter)) { $filterSql = $this->_getFilterSql($filter); } // mailboxが空時、メッセージ所在トレイデフォルト値設定 if (empty($mailbox)) { $mailbox = PM_LEFTMENU_INBOX; } $sql .= "FROM {pm_message_receiver} r, {pm_message} m " . "WHERE r.message_id = m.message_id AND " . "r.receiver_user_id = ? AND " . "r.delete_state = ? "; // 検索条件設定 $params = array("user_id" => $this->_session->getParameter("_user_id")); if ($mailbox == PM_LEFTMENU_TRASHBOX) { $params["delete_state"] = PM_MESSAGE_STATE_TRASH; } else { $params["delete_state"] = PM_MESSAGE_STATE_NORMAL; $params["mailbox"] = $mailbox; $sql = $sql . "AND mailbox = ? "; } $sql = $sql . $filterSql; if (!$count) { $sql .= $this->_db->getOrderSQL($orderParams); } $query['sql'] = $sql; $query['params'] = $params; break; } return $query; }