/** ************************************************************************ * REQUIRED! This is where you prepare your data for display. This method will * usually be used to query the database, sort and filter the data, and generally * get it ready to be displayed. At a minimum, we should set $this->items and * $this->set_pagination_args(), although the following properties and methods * are frequently interacted with here... * * @uses $this->_column_headers * @uses $this->items * @uses $this->get_columns() * @uses $this->get_sortable_columns() * @uses $this->get_pagenum() * @uses $this->set_pagination_args() **************************************************************************/ function prepare_items() { global $wpdb, $_wp_column_headers; $screen = get_current_screen(); /* select t1.* from `sp_sendpress_list_subscribers` as t1 , `sp_sendpress_subscribers` as t2 where t1.subscriberID = t2.subscriberID and t1.listID = 2*/ /* -- Preparing your query -- */ if (isset($_GET["listID"]) && $_GET["listID"] > 0) { $query = "SELECT t1.*, t3.status FROM " . SendPress_Data::subscriber_table() . " as t1," . SendPress_Data::list_subcribers_table() . " as t2," . SendPress_Data::subscriber_status_table() . " as t3"; $query .= " WHERE (t1.subscriberID = t2.subscriberID) AND (t2.status = t3.statusid ) AND (t2.listID = " . $_GET["listID"] . ")"; } else { $query = "SELECT t1.*, t3.status FROM " . SendPress_Data::subscriber_table() . " as t1," . SendPress_Data::list_subcribers_table() . " as t2," . SendPress_Data::subscriber_status_table() . " as t3"; $query .= " WHERE (t1.subscriberID = t2.subscriberID) AND (t2.status = t3.statusid ) "; } /* -- Ordering parameters -- */ //Parameters that are going to be used to order the result $query_count = "SELECT count(*) FROM " . SendPress_Data::subscriber_table() . " as t1," . SendPress_Data::list_subcribers_table() . " as t2," . SendPress_Data::subscriber_status_table() . " as t3"; if (isset($_GET["listID"])) { $query_count .= " WHERE (t1.subscriberID = t2.subscriberID) AND (t2.status = t3.statusid ) AND (t2.listID = " . $_GET["listID"] . ")"; } if (isset($_GET["statusid"]) && $_GET["statusid"] > 0) { $query .= ' AND statusid = ' . $_GET["statusid"]; $query_count .= ' AND statusid = ' . $_GET["statusid"]; } if (isset($_GET["qs"])) { $query .= ' AND ( email LIKE "%' . $_GET["qs"] . '%" or firstname LIKE "%' . $_GET["qs"] . '%" or lastname LIKE "%' . $_GET["qs"] . '%" )'; $query_count .= ' AND ( email LIKE "%' . $_GET["qs"] . '%" or firstname LIKE "%' . $_GET["qs"] . '%" or lastname LIKE "%' . $_GET["qs"] . '%" )'; } /* -- Pagination parameters -- */ //Number of elements in your table? $totalitems = $wpdb->get_var($query_count); //return the total number of affected rows //How many to display per page? // get the current user ID $user = get_current_user_id(); // get the current admin screen $screen = get_current_screen(); // retrieve the "per_page" option $screen_option = $screen->get_option('per_page', 'option'); $per_page = 10; if (!empty($screen_option)) { // retrieve the value of the option stored for the current user $per_page = get_user_meta($user, $screen_option, true); if (empty($per_page) || $per_page < 1) { // get the default value if none is set $per_page = $screen->get_option('per_page', 'default'); } } //Which page is this? $paged = !empty($_GET["paged"]) ? esc_sql($_GET["paged"]) : ''; //Page Number if (empty($paged) || !is_numeric($paged) || $paged <= 0) { $paged = 1; } //How many pages do we have in total? $totalpages = ceil($totalitems / $per_page); $orderby = !empty($_GET["orderby"]) ? esc_sql($_GET["orderby"]) : 'ASC'; $order = !empty($_GET["order"]) ? esc_sql($_GET["order"]) : ''; if ($orderby == 'status') { $orderby = 't2.status'; } if (!empty($orderby) & !empty($order)) { $query .= ' ORDER BY ' . $orderby . ' ' . $order; } if (!isset($_GET["listID"])) { $query .= ' group by t1.email'; } //adjust the query to take pagination into account if (!empty($paged) && !empty($per_page)) { $offset = ($paged - 1) * $per_page; $query .= ' LIMIT ' . (int) $offset . ',' . (int) $per_page; } /* -- Register the pagination -- */ $this->set_pagination_args(array("total_items" => $totalitems, "total_pages" => $totalpages, "per_page" => $per_page)); //The pagination links are automatically built according to those parameters /* -- Register the Columns -- */ $columns = $this->get_columns(); $hidden = array(); $sortable = $this->get_sortable_columns(); $this->_column_headers = array($columns, $hidden, $sortable); /* -- Fetch the items -- */ $this->items = $wpdb->get_results($query); }
static function get_count_subscribers($listID = false, $status = 2) { global $wpdb; $table = SendPress_Data::list_subcribers_table(); $query = "SELECT COUNT(t1.subscriberID) FROM " . SendPress_Data::subscriber_table() . " as t1," . SendPress_Data::list_subcribers_table() . " as t2," . SendPress_Data::subscriber_status_table() . " as t3"; $query .= " WHERE (t1.subscriberID = t2.subscriberID) AND (t2.status = t3.statusid ) AND (t2.status = %d) "; if ($listID !== false) { $query .= "AND (t2.listID = %d)"; } else { $query .= " "; } // "SELECT COUNT(*) FROM $table WHERE listID = $listID AND status = $status" $count = $wpdb->get_var($wpdb->prepare($query, $status, $listID)); return $count; }
function subscriber_table() { _deprecated_function(__FUNCTION__, '0.8.9', 'SendPress_Data::subscriber_table()'); return SendPress_Data::subscriber_table(); }