email-log / include / Core / DB / TableManager.php

TableManager.php in Email Log 2.1.0, at include/Core/DB/TableManager.php

268 lines 7.1 KB

Raw Download Zip

1	<?php namespace EmailLog\Core\DB;
2	/**
3	* Handle installation and db table creation
4	*/
5
6	use EmailLog\Core\Loadie;
7
8	defined( 'ABSPATH' ) \|\| exit; // Exit if accessed directly.
9
10	/**
11	* Helper class to create table.
12	*
13	* @since 2.0.0
14	*/
15	class TableManager implements Loadie {
16
17	/* Database table name */
18	const LOG_TABLE_NAME = 'email_log';
19
20	/* Database option name */
21	const DB_OPTION_NAME = 'email-log-db';
22
23	/* Database version */
24	const DB_VERSION = '0.1';
25
26	/**
27	* Setup hooks.
28	*/
29	public function load() {
30	add_action( 'wpmu_new_blog', array( $this, 'create_table_for_new_blog' ) );
31
32	add_filter( 'wpmu_drop_tables', array( $this, 'delete_table_from_deleted_blog' ) );
33	}
34
35	/**
36	* On plugin activation, create table if needed.
37	*
38	* @param bool $network_wide True if the plugin was network activated.
39	*/
40	public function on_activate( $network_wide ) {
41	if ( is_multisite() && $network_wide ) {
42	// Note: if there are more than 10,000 blogs or
43	// if `wp_is_large_network` filter is set, then this may fail.
44	// TODO: Take care of the deprecated function.
45	$sites = wp_get_sites();
46
47	foreach ( $sites as $site ) {
48	switch_to_blog( $site['blog_id'] );
49	$this->create_table();
50	restore_current_blog();
51	}
52	} else {
53	$this->create_table();
54	}
55	}
56
57	/**
58	* Create email log table when a new blog is created.
59	*
60	* @param int $blog_id Blog Id.
61	*/
62	public function create_table_for_new_blog( $blog_id ) {
63	if ( is_plugin_active_for_network( 'email-log/email-log.php' ) ) {
64	switch_to_blog( $blog_id );
65	$this->create_table();
66	restore_current_blog();
67	}
68	}
69
70	/**
71	* Add email log table to the list of tables deleted when a blog is deleted.
72	*
73	* @param array $tables List of tables to be deleted.
74	*
75	* @return string[] $tables Modified list of tables to be deleted.
76	*/
77	public function delete_table_from_deleted_blog( $tables ) {
78	$tables[] = $this->get_log_table_name();
79
80	return $tables;
81	}
82
83	/**
84	* Get email log table name.
85	*
86	* @return string Email Log Table name.
87	*/
88	public function get_log_table_name() {
89	global $wpdb;
90
91	return $wpdb->prefix . self::LOG_TABLE_NAME;
92	}
93
94	/**
95	* Insert log data into DB.
96	*
97	* @param array $data Data to be inserted.
98	*/
99	public function insert_log( $data ) {
100	global $wpdb;
101
102	$table_name = $this->get_log_table_name();
103	$wpdb->insert( $table_name, $data );
104	}
105
106	/**
107	* Delete log entries by ids.
108	*
109	* @param string $ids Comma separated list of log ids.
110	*
111	* @return false\|int Number of log entries that got deleted. False on failure.
112	*/
113	public function delete_logs( $ids ) {
114	global $wpdb;
115
116	$table_name = $this->get_log_table_name();
117
118	// Can't use wpdb->prepare for the below query. If used it results in this bug // https://github.com/sudar/email-log/issues/13.
119	$ids = esc_sql( $ids );
120
121	return $wpdb->query( "DELETE FROM {$table_name} where id IN ( {$ids} )" ); //@codingStandardsIgnoreLine
122	}
123
124	/**
125	* Delete all log entries.
126	*
127	* @return false\|int Number of log entries that got deleted. False on failure.
128	*/
129	public function delete_all_logs() {
130	global $wpdb;
131
132	$table_name = $this->get_log_table_name();
133
134	return $wpdb->query( "DELETE FROM {$table_name}" ); //@codingStandardsIgnoreLine
135	}
136
137	/**
138	* Deletes Email Logs older than the specified interval.
139	*
140	* @param int $interval_in_days No. of days beyond which logs are to be deleted.
141	*
142	* @return int $deleted_rows_count Count of rows deleted.
143	*/
144	public function delete_logs_older_than( $interval_in_days ) {
145	global $wpdb;
146	$table_name = $this->get_log_table_name();
147
148	$query = $wpdb->prepare( "DELETE FROM {$table_name} WHERE sent_date < DATE_SUB( CURDATE(), INTERVAL %d DAY )", $interval_in_days );
149	$deleted_rows_count = $wpdb->query( $query );
150
151	return $deleted_rows_count;
152	}
153
154	/**
155	* Fetch log item by ID.
156	*
157	* @param array $ids Optional. Array of IDs of the log items to be retrieved.
158	*
159	* @return array Log item(s).
160	*/
161	public function fetch_log_items_by_id( $ids = array() ) {
162	global $wpdb;
163	$table_name = $this->get_log_table_name();
164
165	$query = "SELECT * FROM {$table_name}";
166
167	if ( ! empty( $ids ) ) {
168	$ids = array_map( 'absint', $ids );
169
170	// Can't use wpdb->prepare for the below query. If used it results in this bug https://github.com/sudar/email-log/issues/13.
171	$ids_list = esc_sql( implode( ',', $ids ) );
172
173	$query .= " where id IN ( {$ids_list} )";
174	}
175
176	return $wpdb->get_results( $query, 'ARRAY_A' ); //@codingStandardsIgnoreLine
177	}
178
179	/**
180	* Fetch log items.
181	*
182	* @param array $request Request object.
183	* @param int $per_page Entries per page.
184	* @param int $current_page_no Current page no.
185	*
186	* @return array Log entries and total items count.
187	*/
188	public function fetch_log_items( $request, $per_page, $current_page_no ) {
189	global $wpdb;
190	$table_name = $this->get_log_table_name();
191
192	$query = 'SELECT * FROM ' . $table_name;
193	$count_query = 'SELECT count(*) FROM ' . $table_name;
194	$query_cond = '';
195
196	if ( isset( $request['s'] ) && $request['s'] !== '' ) {
197	$search_term = trim( esc_sql( $request['s'] ) );
198	$query_cond .= " WHERE ( to_email LIKE '%$search_term%' OR subject LIKE '%$search_term%' ) ";
199	}
200
201	if ( isset( $request['d'] ) && $request['d'] !== '' ) {
202	$search_date = trim( esc_sql( $request['d'] ) );
203	if ( '' === $query_cond ) {
204	$query_cond .= " WHERE sent_date BETWEEN '$search_date 00:00:00' AND '$search_date 23:59:59' ";
205	} else {
206	$query_cond .= " AND sent_date BETWEEN '$search_date 00:00:00' AND '$search_date 23:59:59' ";
207	}
208	}
209
210	// Ordering parameters.
211	$orderby = ! empty( $request['orderby'] ) ? esc_sql( $request['orderby'] ) : 'sent_date';
212	$order = ! empty( $request['order'] ) ? esc_sql( $request['order'] ) : 'DESC';
213
214	if ( ! empty( $orderby ) & ! empty( $order ) ) {
215	$query_cond .= ' ORDER BY ' . $orderby . ' ' . $order;
216	}
217
218	// Find total number of items.
219	$count_query = $count_query . $query_cond;
220	$total_items = $wpdb->get_var( $count_query );
221
222	// Adjust the query to take pagination into account.
223	if ( ! empty( $current_page_no ) && ! empty( $per_page ) ) {
224	$offset = ( $current_page_no - 1 ) * $per_page;
225	$query_cond .= ' LIMIT ' . (int) $offset . ',' . (int) $per_page;
226	}
227
228	// Fetch the items.
229	$query = $query . $query_cond;
230	$items = $wpdb->get_results( $query );
231
232	return array( $items, $total_items );
233	}
234
235	/**
236	* Create email log table.
237	*
238	* @access private
239	*
240	* @global object $wpdb
241	*/
242	private function create_table() {
243	global $wpdb;
244
245	$table_name = $this->get_log_table_name();
246	$charset_collate = $wpdb->get_charset_collate();
247
248	if ( $wpdb->get_var( "show tables like '{$table_name}'" ) != $table_name ) {
249
250	$sql = 'CREATE TABLE ' . $table_name . ' (
251	id mediumint(9) NOT NULL AUTO_INCREMENT,
252	to_email VARCHAR(100) NOT NULL,
253	subject VARCHAR(250) NOT NULL,
254	message TEXT NOT NULL,
255	headers TEXT NOT NULL,
256	attachments TEXT NOT NULL,
257	sent_date timestamp NOT NULL,
258	PRIMARY KEY (id)
259	) ' . $charset_collate . ' ;';
260
261	require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
262	dbDelta( $sql );
263
264	add_option( self::DB_OPTION_NAME, self::DB_VERSION );
265	}
266	}
267	}
268