PHP ET::SQL方法代码示例

 /**
  * Toggle the user's subscription to a channel.
  *
  * @param int $channelId The ID of the channel to toggle subscription to.
  * @return void
  */
 public function action_subscribe($channelId = "")
 {
     if (!ET::$session->user or !$this->validateToken()) {
         return;
     }
     // If we don't have permission to view this channel, don't proceed.
     if (!ET::channelModel()->hasPermission((int) $channelId, "view")) {
         return;
     }
     // Work out if we're already unsubscribed or not, and switch to the opposite of that.
     $channel = ET::SQL()->select("unsubscribed, lft, rgt")->from("channel c")->from("member_channel mc", "mc.channelId = c.channelId AND mc.memberId = :userId", "left")->bind(":userId", ET::$session->userId)->where("c.channelId", (int) $channelId)->exec()->firstRow();
     // Get all the child channels of this channel.
     $rows = ET::SQL()->select("channelId")->from("channel")->where("lft >= :lft")->bind(":lft", $channel["lft"])->where("rgt <= :rgt")->bind(":rgt", $channel["rgt"])->exec()->allRows();
     $channelIds = array();
     foreach ($rows as $row) {
         $channelIds[] = $row["channelId"];
     }
     // Write to the database.
     ET::channelModel()->setStatus($channelIds, ET::$session->userId, array("unsubscribed" => !$channel["unsubscribed"]));
     // Normally, redirect back to the channel list.
     if ($this->responseType === RESPONSE_TYPE_DEFAULT) {
         redirect(URL("channels"));
     }
     // Otherwise, set a JSON var.
     $this->json("unsubscribed", !$channel["unsubscribed"]);
     $this->render();
 }

 public function action_create()
 {
     // Include the file needed to create the sitemap.
     include "lib/Sitemap.php";
     $sitemap = new Sitemap(C("esoTalk.baseURL"));
     $sitemap->setPath(PATH_ROOT . "/");
     $sitemap->addItem("", "1.0", "hourly", 'now');
     $result = ET::SQL()->select("ch.channelId")->select("ch.slug")->from("channel ch")->orderBy("ch.channelId ASC")->exec();
     $channels = $result->allRows("channelId");
     foreach ($channels as $channel) {
         if (!in_array($channel["slug"], C("plugin.Sitemap.channels"))) {
             $sitemap->addItem("conversations/" . $channel["slug"], C("plugin.Sitemap.priority3"), C("plugin.Sitemap.frequency3"), 'now');
             $result = ET::SQL()->select("c.conversationId")->select("c.title")->select("c.channelId")->select("c.sticky")->select("lastPostTime")->from("conversation c")->where("c.channelId = :channelId")->where("private", 0)->orderBy("c.conversationId ASC")->bind(":channelId", $channel["channelId"])->exec();
             $conversations = $result->allRows();
             foreach ($conversations as $conversation) {
                 $url = conversationURL($conversation["conversationId"], $conversation["title"]);
                 if ($conversation["sticky"]) {
                     $sitemap->addItem($url, C("plugin.Sitemap.priority2"), C("plugin.Sitemap.frequency2"), $conversation["lastPostTime"]);
                 } else {
                     $sitemap->addItem($url, C("plugin.Sitemap.priority1"), C("plugin.Sitemap.frequency1"), $conversation["lastPostTime"]);
                 }
             }
         }
     }
     $sitemap->createSitemapIndex("http://www.bitcoinclub.nl/", 'now');
 }

 /**
  * Initialize the admin controller. Construct a menu to show all admin panels.
  *
  * @return void
  */
 public function init()
 {
     // If the user isn't an administrator, kick them out.
     if (!ET::$session->isAdmin()) {
         $this->redirect(URL("user/login?return=" . urlencode($this->selfURL)));
     }
     parent::init();
     // Construct the menus for the side bar.
     $this->defaultMenu = ETFactory::make("menu");
     $this->menu = ETFactory::make("menu");
     $this->defaultMenu->add("dashboard", "<a href='" . URL("admin/dashboard") . "'><i class='icon-dashboard'></i> " . T("Dashboard") . "</a>");
     $this->defaultMenu->add("settings", "<a href='" . URL("admin/settings") . "'><i class='icon-cog'></i> " . T("Forum Settings") . "</a>");
     $this->defaultMenu->add("appearance", "<a href='" . URL("admin/appearance") . "'><i class='icon-eye-open'></i> " . T("Appearance") . "</a>");
     $this->defaultMenu->add("channels", "<a href='" . URL("admin/channels") . "'><i class='icon-tags'></i> " . T("Channels") . "</a>");
     $this->defaultMenu->add("members", "<a href='" . URL("members") . "'><i class='icon-group'></i> " . T("Members") . "</a>");
     $this->defaultMenu->add("plugins", "<a href='" . URL("admin/plugins") . "'><i class='icon-puzzle-piece'></i> " . T("Plugins") . "</a>");
     $this->defaultMenu->highlight(ET::$controllerName);
     $this->menu->highlight(ET::$controllerName);
     // If new registrations require admin approval, add the 'unapproved' admin page with a count.
     if (C("esoTalk.registration.requireConfirmation") == "approval") {
         $count = ET::SQL()->select("COUNT(1)")->from("member")->where("confirmed", 0)->exec()->result();
         $this->menu->add("unapproved", "<a href='" . URL("admin/unapproved") . "'><i class='icon-lock'></i> " . T("Unapproved") . " <span class='badge'>" . $count . "</span></a>");
     }
     if ($this->responseType === RESPONSE_TYPE_DEFAULT) {
         $this->pushNavigation("admin", "administration", URL($this->selfURL));
     }
     $this->addJSFile("core/js/admin.js");
     $this->addCSSFile("core/skin/admin.css");
     $this->trigger("initAdmin", array($this->menu, $this->defaultMenu));
 }

 public function insertAttachments($attachments, $keys)
 {
     $inserts = array();
     foreach ($attachments as $id => $attachment) {
         $inserts[] = array_merge(array($id, $attachment["name"], $attachment["secret"]), array_values($keys));
     }
     ET::SQL()->insert("attachment")->setMultiple(array_merge(array("attachmentId", "filename", "secret"), array_keys($keys)), $inserts)->exec();
 }

 public function validateSlug($slug)
 {
     if (!strlen($slug)) {
         return "empty";
     }
     if (ET::SQL()->select("COUNT(pageId)")->from("page")->where("slug=:slug")->bind(":slug", $slug)->exec()->result() > 0) {
         return "channelSlugTaken";
     }
 }

 public function handler_conversationController_conversationIndexDefault($sender, &$conversation)
 {
     $sender->addCSSFile($this->resource("views.css"));
     if ($conversation["startMemberId"] == ET::$session->userId) {
         return;
     }
     $conversation["views"]++;
     ET::SQL()->update("conversation")->set("views", "views + 1", false)->where("conversationId", $conversation["conversationId"])->exec();
 }

 /**
  * Show a sheet containing a list of groups. Pretty simple, really!
  *
  * @return void
  */
 public function action_index()
 {
     ET::activityModel()->markNotificationsAsRead('unapproved');
     $sql = ET::SQL();
     $sql->where("confirmed", 0);
     $sql->orderBy("m.memberId desc");
     $members = ET::memberModel()->getWithSQL($sql);
     $this->data("members", $members);
     $this->render("admin/unapproved");
 }

 /**
  * 複数タグ文字列で検索
  * @param type $keys
  * @return type
  */
 public function getTagsIds($keys)
 {
     if (is_array($keys) && count($keys)) {
         // タグID,タグテキストの配列を取得
         $result = ET::SQL()->select("distinct tagsId")->from("tags")->where("tagText IN (:keys)")->bind(":keys", $keys)->exec()->allRows();
         $ids = array();
         if (count($result)) {
             foreach ($result as $r) {
                 $ids[] = $r["tagsId"];
             }
         }
         return $ids;
     }
 }

 /**
  * Show the administrator dashboard view.
  *
  * @return void
  */
 public function action_index()
 {
     $this->title = T("Dashboard");
     // Work out a UNIX timestamp of one week ago.
     $oneWeekAgo = time() - 60 * 60 * 24 * 7;
     // Create an array of statistics to show on the dashboard.
     $statistics = array("<a href='" . URL("members") . "'>" . T("Members") . "</a>" => number_format(ET::SQL()->select("COUNT(*)")->from("member")->exec()->result()), T("Conversations") => number_format(ET::SQL()->select("COUNT(*)")->from("conversation")->exec()->result()), T("Posts") => number_format(ET::SQL()->select("COUNT(*)")->from("post")->exec()->result()), T("New members in the past week") => number_format(ET::SQL()->select("COUNT(*)")->from("member")->where(":time<joinTime")->bind(":time", $oneWeekAgo)->exec()->result()), T("New conversations in the past week") => number_format(ET::SQL()->select("COUNT(*)")->from("conversation")->where(":time<startTime")->bind(":time", $oneWeekAgo)->exec()->result()), T("New posts in the past week") => number_format(ET::SQL()->select("COUNT(*)")->from("post")->where(":time<time")->bind(":time", $oneWeekAgo)->exec()->result()));
     // Determine if we should show the welcome sheet.
     if (!C("esoTalk.admin.welcomeShown")) {
         $this->data("showWelcomeSheet", true);
         ET::writeConfig(array("esoTalk.admin.welcomeShown" => true));
     }
     $this->data("statistics", $statistics);
     $this->render("admin/dashboard");
 }

 public function getReputationMembers()
 {
     $result = ET::SQL()->select("username")->select("memberId")->select("reputationPoints")->from("member")->orderBy("reputationPoints DESC")->exec()->allRows();
     //Assign ranks to all members based on reputation points
     $rank = 1;
     foreach ($result as $k => $v) {
         $results[$k]["rank"] = $rank;
         $results[$k]["avatar"] = avatar($v, "thumb");
         $results[$k]["username"] = $result[$k]["username"];
         $results[$k]["memberId"] = $result[$k]["memberId"];
         $results[$k]["reputationPoints"] = $result[$k]["reputationPoints"];
         $rank++;
     }
     return $results;
 }

 public function handler_postModel_getPostsAfter($sender, &$posts)
 {
     $postsById = array();
     foreach ($posts as &$post) {
         $postsById[$post["postId"]] =& $post;
         $post["likes"] = array();
     }
     if (!count($postsById)) {
         return;
     }
     $result = ET::SQL()->select("postId, m.memberId, m.email, username, avatarFormat")->from("like l")->from("member m", "m.memberId=l.memberId", "left")->where("postId IN (:ids)")->bind(":ids", array_keys($postsById))->exec();
     while ($row = $result->nextRow()) {
         $postsById[$row["postId"]]["likes"][$row["memberId"]] = array("memberId" => $row["memberId"], "username" => $row["username"], "email" => $row["email"], "avatarFormat" => $row["avatarFormat"]);
     }
 }

 public function handler_conversationModel_addReplyAfter($sender, $conversation, $postId, $content)
 {
     // Only continue if this is the first post.
     if ($conversation["countPosts"] > 1) {
         return;
     }
     // We get all members who have starred the post author and have no unread posts in the conversation.
     $sql = ET::SQL()->from("member_member mm2", "mm2.memberId2=:userId AND mm2.memberId1=m.memberId AND mm2.follow=1 AND mm2.memberId1!=:userId", "inner")->from("member_conversation co", "co.conversationId=:conversationId AND co.type='member' AND co.id=m.memberId", "left")->where("co.lastRead IS NULL OR co.lastRead>=:posts")->bind(":conversationId", $conversation["conversationId"])->bind(":posts", $conversation["countPosts"] - 1)->bind(":userId", ET::$session->userId);
     $members = ET::memberModel()->getWithSQL($sql);
     $data = array("conversationId" => $conversation["conversationId"], "postId" => $postId, "title" => $conversation["title"]);
     $emailData = array("content" => $content);
     foreach ($members as $member) {
         // Check if this member is allowed to view this conversation before sending them a notification.
         $sql = ET::SQL()->select("conversationId")->from("conversation c")->where("conversationId", $conversation["conversationId"]);
         ET::conversationModel()->addAllowedPredicate($sql, $member);
         if (!$sql->exec()->numRows()) {
             continue;
         }
         ET::activityModel()->create("postMember", $member, ET::$session->user, $data, $emailData);
     }
 }

 /**
  * Toggle the user's subscription to a channel.
  *
  * @param int $channelId The ID of the channel to toggle subscription to.
  * @return void
  */
 public function subscribe($channelId = "")
 {
     if (!ET::$session->user or !$this->validateToken()) {
         return;
     }
     // If we don't have permission to view this channel, don't proceed.
     if (!ET::channelModel()->hasPermission((int) $channelId, "view")) {
         return;
     }
     // Work out if we're already unsubscribed or not, and switch to the opposite of that.
     $unsubscribed = !ET::SQL()->select("unsubscribed")->from("member_channel")->where("memberId", ET::$session->userId)->where("channelId", (int) $channelId)->exec()->result();
     // Write to the database.
     ET::channelModel()->setStatus($channelId, ET::$session->userId, array("unsubscribed" => $unsubscribed));
     // Normally, redirect back to the channel list.
     if ($this->responseType === RESPONSE_TYPE_DEFAULT) {
         redirect(URL("channels"));
     }
     // Otherwise, set a JSON var.
     $this->json("unsubscribed", $unsubscribed);
     $this->render();
 }

 /**
  * Common initialization for all controllers, called on every page load. This will add basic user links to
  * the "user" menu, and add core JS files and language definitions.
  *
  * If this is overridden, parent::init() should be called to maintain consistency between controllers.
  *
  * @return void
  */
 public function init()
 {
     // Check for updates to the esoTalk software, but only if we're the root admin and we haven't checked in
     // a while.
     if (ET::$session->userId == C("esoTalk.rootAdmin") and C("esoTalk.admin.lastUpdateCheckTime") + C("esoTalk.updateCheckInterval") < time()) {
         ET::upgradeModel()->checkForUpdates();
     }
     if ($this->responseType === RESPONSE_TYPE_DEFAULT) {
         // If the user IS NOT logged in, add the 'login' and 'sign up' links to the bar.
         if (!ET::$session->user) {
             $this->addToMenu("user", "join", "<a href='" . URL("user/join?return=" . urlencode($this->selfURL)) . "' class='link-join'>" . T("Sign Up") . "</a>");
             $this->addToMenu("user", "login", "<a href='" . URL("user/login?return=" . urlencode($this->selfURL)) . "' class='link-login'>" . T("Log In") . "</a>");
         } else {
             $this->addToMenu("user", "user", "<a href='" . URL("member/me") . "'>" . avatar(ET::$session->user, "thumb") . name(ET::$session->user["username"]) . "</a>");
             $this->addToMenu("user", "settings", "<a href='" . URL("settings") . "' class='link-settings'>" . T("Settings") . "</a>");
             if (ET::$session->isAdmin()) {
                 $this->addToMenu("user", "administration", "<a href='" . URL("admin") . "' class='link-administration'>" . T("Administration") . "</a>");
             }
             $this->addToMenu("user", "logout", "<a href='" . URL("user/logout?token=" . ET::$session->token) . "' class='link-logout'>" . T("Log Out") . "</a>");
         }
         // Get the number of members currently online and add it as a statistic.
         if (C("esoTalk.members.visibleToGuests") or ET::$session->user) {
             $online = ET::SQL()->select("COUNT(*)")->from("member")->where("UNIX_TIMESTAMP()-:seconds<lastActionTime")->bind(":seconds", C("esoTalk.userOnlineExpire"))->exec()->result();
             $stat = Ts("statistic.online", "statistic.online.plural", number_format($online));
             $stat = "<a href='" . URL("members/online") . "' class='link-membersOnline'>{$stat}</a>";
             $this->addToMenu("statistics", "statistic-online", $stat);
         }
         $this->addToMenu("meta", "copyright", "<a href='http://esotalk.org/' target='_blank'>" . T("Powered by") . " esoTalk</a>");
         // Set up some default JavaScript files and language definitions.
         $this->addJSFile("core/js/lib/jquery.js", true);
         $this->addJSFile("core/js/lib/jquery.migrate.js", true);
         $this->addJSFile("core/js/lib/jquery.misc.js", true);
         $this->addJSFile("core/js/lib/jquery.history.js", true);
         $this->addJSFile("core/js/lib/jquery.scrollTo.js", true);
         $this->addJSFile("core/js/global.js", true);
         $this->addJSLanguage("message.ajaxRequestPending", "message.ajaxDisconnected", "Loading...", "Notifications");
         $this->addJSVar("notificationCheckInterval", C("esoTalk.notificationCheckInterval"));
     }
     $this->trigger("init");
 }

 /**
  * Return a JSON array of up to 50 members whose usernames match a given string. This data can be used to
  * create a list of members in an autocomplete menu.
  *
  * @param string $input The string to match member usernames against.
  * @return void
  */
 public function autocomplete($input = "")
 {
     // Force the response type to JSON.
     $this->responseType = RESPONSE_TYPE_JSON;
     // Don't do this for strings less than three characters for performance reasons.
     if (strlen($input) < 3) {
         return;
     }
     // Construct a query to fetch matching members.
     $results = ET::SQL()->select("'member' AS type")->select("memberId AS id")->select("username AS name")->select("avatarFormat")->select("email")->from("member")->where("username LIKE :username")->bind(":username", $input . "%")->orderBy("username")->limit(50)->exec()->allRows();
     // Loop through the results and generate avatar HTML for each one.
     foreach ($results as $k => $v) {
         $results[$k]["avatar"] = avatar($v, "thumb");
         unset($results[$k]["avatarFormat"]);
         unset($results[$k]["email"]);
         // Convert spaces in the member name to non-breaking spaces.
         // (Spaces aren't usually allowed in esoTalk usernames, so this is a bit of a "hack" for
         // certain esoTalk installations that do allow them.)
         $results[$k]["name"] = str_replace(" ", " ", $results[$k]["name"]);
     }
     $this->json("results", $results);
     $this->render();
 }