order-tracking / includes / CustomerManager.class.php

CustomerManager.class.php in Order Tracking – WordPress Status Tracking Plugin 3.2.2, at includes/CustomerManager.class.php

481 lines 12.2 KB

Raw Download Zip

1	<?php
2	/**
3	* Class to handle all customer database interactions for the Order Tracking plugin
4	*/
5
6	if ( !defined( 'ABSPATH' ) )
7	exit;
8
9	if ( !class_exists( 'ewdotpCustomerManager' ) ) {
10	class ewdotpCustomerManager {
11
12	// The name of the customers table, set in the constructor
13	public $customers_table_name;
14
15	// The name of the meta table, set in the constructor
16	public $meta_table_name;
17
18	// Array containing the arguments for the query
19	public $args = array();
20
21	// Array containing retrieved customer objects
22	public $customers = array();
23
24	public function __construct() {
25	global $wpdb;
26
27	$this->customers_table_name = $wpdb->prefix . "EWD_OTP_Customers";
28	$this->meta_table_name = $wpdb->prefix . "EWD_OTP_Fields_Meta";
29
30	if ( get_transient( 'ewd-otp-update-tables' ) ) {
31
32	add_action( 'plugins_loaded', array( $this, 'create_tables' ) );
33	}
34	}
35
36	/**
37	* Creates the tables used to store customers and their meta information
38	* @since 3.0.0
39	*/
40	public function create_tables() {
41
42	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
43
44	$sql = "CREATE TABLE $this->customers_table_name (
45	Customer_ID mediumint(9) NOT NULL AUTO_INCREMENT,
46	Customer_Number text DEFAULT '' NOT NULL,
47	Customer_Name text DEFAULT '' NOT NULL,
48	Sales_Rep_ID mediumint(9) DEFAULT 0 NOT NULL,
49	Customer_WP_ID mediumint(9) DEFAULT 0 NOT NULL,
50	Customer_FEUP_ID mediumint(9) DEFAULT 0 NOT NULL,
51	Customer_Email text DEFAULT '' NOT NULL,
52	Customer_Created datetime DEFAULT '0000-00-00 00:00:00' NULL,
53	UNIQUE KEY id (Customer_ID)
54	)
55	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
56
57	dbDelta($sql);
58
59	$sql = "CREATE TABLE $this->meta_table_name (
60	Meta_ID mediumint(9) NOT NULL AUTO_INCREMENT,
61	Field_ID mediumint(9) DEFAULT '0',
62	Order_ID mediumint(9) DEFAULT '0',
63	Customer_ID mediumint(9) DEFAULT '0',
64	Sales_Rep_ID mediumint(9) DEFAULT '0',
65	Meta_Value text DEFAULT '' NOT NULL,
66	UNIQUE KEY id (Meta_ID)
67	)
68	DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;";
69
70	require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
71
72	dbDelta($sql);
73
74	$this->copy_customer_id_to_number_field();
75	}
76
77	/**
78	* Copies the customer ID database field to the number field, when the number field is blank
79	* @since 3.2.0
80	*/
81	public function copy_customer_id_to_number_field() {
82	global $wpdb;
83
84	$wpdb->query( "UPDATE $this->customers_table_name SET Customer_Number=Customer_ID WHERE Customer_Number=''" );
85	}
86
87	/**
88	* Returns a single customer given their customer ID
89	* @since 3.0.0
90	*/
91	public function get_customer_from_id( $customer_id ) {
92	global $wpdb;
93
94	$db_customer = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $this->customers_table_name WHERE Customer_ID=%d", $customer_id ) );
95
96	return $db_customer;
97	}
98
99	/**
100	* Returns a customer's ID given their WP ID
101	* @since 3.0.0
102	*/
103	public function get_customer_id_from_wp_id( $wp_id ) {
104	global $wpdb;
105
106	$customer_id = $wpdb->get_var( $wpdb->prepare( "SELECT Customer_ID FROM $this->customers_table_name WHERE Customer_WP_ID=%d", $wp_id ) );
107
108	return $customer_id;
109	}
110
111	/**
112	* Returns a customer's ID given their FEUP ID
113	* @since 3.0.0
114	*/
115	public function get_customer_id_from_feup_id( $feup_id ) {
116	global $wpdb;
117
118	$customer_id = $wpdb->get_var( $wpdb->prepare( "SELECT Customer_ID FROM $this->customers_table_name WHERE Customer_FEUP_ID=%d", $feup_id ) );
119
120	return $customer_id;
121	}
122
123	/**
124	* Returns a customer's ID given their number
125	* @since 3.0.0
126	*/
127	public function get_customer_id_from_number( $customer_number ) {
128	global $wpdb;
129
130	$customer_id = $wpdb->get_var( $wpdb->prepare( "SELECT Customer_ID FROM $this->customers_table_name WHERE Customer_Number=%s", $customer_number ) );
131
132	return $customer_id;
133	}
134
135	/**
136	* Returns a customer's ID given their name
137	* @since 3.0.0
138	*/
139	public function get_customer_id_from_name( $customer_name ) {
140	global $wpdb;
141
142	$customer_id = $wpdb->get_var( $wpdb->prepare( "SELECT Customer_ID FROM $this->customers_table_name WHERE Customer_Name=%s", $customer_name ) );
143
144	return $customer_id;
145	}
146
147	/**
148	* Returns a customer's ID given their email
149	* @since 3.0.0
150	*/
151	public function get_customer_id_from_email( $email ) {
152	global $wpdb;
153
154	$customer_id = $wpdb->get_var( $wpdb->prepare( "SELECT Customer_ID FROM $this->customers_table_name WHERE Customer_Email=%s", $email ) );
155
156	return $customer_id;
157	}
158
159	/**
160	* Returns customers matching the arguments supplied
161	* @since 3.0.0
162	*/
163	public function get_matching_customers( $args ) {
164
165	$this->customers = array();
166
167	$defaults = array(
168	'customers_per_page' => 20,
169	'order' => 'ASC',
170	'paged' => 1,
171	);
172
173	$this->args = wp_parse_args( $args, $defaults );
174
175	$this->run_query();
176
177	return $this->customers;
178	}
179
180	/**
181	* Return the counts for customers being displayed on the admin customers page
182	* @since 3.0.0
183	*/
184	public function get_customer_counts( $args ) {
185	global $wpdb;
186
187	$query_string = "SELECT count( * ) AS num_customers
188	FROM $this->customers_table_name
189	WHERE 1=%d
190	";
191
192	$query_args = array( 1 );
193
194	if ( ! empty( $args['name'] ) ) {
195
196	$query_string .= " AND Customer_Name=%s";
197	$query_args[] = sanitize_text_field( $args['name'] );
198	}
199
200	if ( ! empty( $args['email'] ) ) {
201
202	$query_string .= " AND Customer_Email=%s";
203	$query_args[] = sanitize_text_field( $args['email'] );
204	}
205
206	$result = $wpdb->get_results( $wpdb->prepare( $query_string, $query_args ) );
207
208	$counts = array(
209	'total' => $result[0]->num_customers
210	);
211
212	return $counts;
213	}
214
215	/**
216	* Create and run the SQL query based on the arguments received
217	* @since 3.0.0
218	*/
219	public function run_query() {
220	global $wpdb;
221
222	$args = $this->args;
223
224	$query_string = "SELECT * FROM $this->customers_table_name WHERE 1=%d";
225
226	$query_args = array( 1 );
227
228	if ( ! empty( $args['id'] ) ) {
229
230	$query_string .= " AND Customer_ID=%d";
231	$query_args[] = intval( $args['id'] );
232	}
233
234	if ( ! empty( $args['number'] ) ) {
235
236	$query_string .= " AND Customer_Number=%s";
237	$query_args[] = $args['number'];
238	}
239
240	if ( ! empty( $args['name'] ) ) {
241
242	$query_string .= " AND Customer_Name=%s";
243	$query_args[] = $args['name'];
244	}
245
246	if ( ! empty( $args['wp_id'] ) ) {
247
248	$query_string .= " AND Customer_WP_ID=%d";
249	$query_args[] = intval( $args['wp_id'] );
250	}
251
252	if ( ! empty( $args['feup_id'] ) ) {
253
254	$query_string .= " AND Customer_FEUP_ID=%d";
255	$query_args[] = intval( $args['feup_id'] );
256	}
257
258	if ( ! empty( $args['email'] ) ) {
259
260	$query_string .= " AND Customer_Email=%s";
261	$query_args[] = $args['email'];
262	}
263
264	if ( ! empty( $args['sales_rep'] ) ) {
265
266	$query_string .= " AND Sales_Rep_ID=%d";
267	$query_args[] = intval( $args['sales_rep'] );
268	}
269
270	if ( ! empty( $args['orderby'] ) ) {
271
272	$query_string .= ' ORDER BY ' . esc_sql( $args['orderby'] ) . ' ' . ( strtolower( $args['order'] ) == 'desc' ? 'DESC' : 'ASC' );
273	}
274
275	if ( $args['customers_per_page'] > 0 ) {
276
277	$query_string .= ' LIMIT ' . intval( ( $args['paged'] - 1 ) * $args['customers_per_page'] ) . ', ' . intval( $args['customers_per_page'] );
278	}
279
280	$db_customers = $wpdb->get_results( $wpdb->prepare( $query_string, $query_args ) );
281
282	foreach ( $db_customers as $db_customer ) {
283
284	$customer = new ewdotpCustomer();
285
286	$customer->load_customer( $db_customer );
287
288	$this->customers[] = $customer;
289	}
290	}
291
292	/**
293	* Returns the value for a given field/customer id pair
294	* @since 3.0.0
295	*/
296	public function get_customer_field( $field, $customer_id ) {
297	global $wpdb;
298
299	$db_customer = $wpdb->get_row( $wpdb->prepare( "SELECT * FROM $this->customers_table_name WHERE Customer_ID=%d", $customer_id ) );
300
301	$customer = new ewdotpCustomer();
302	$customer->load_customer( $db_customer );
303
304	return ! empty( $customer->$field ) ? $customer->$field : '';
305	}
306
307	/**
308	* Returns the value for a given custom_field/customer pair
309	* @since 3.0.0
310	*/
311	public function get_field_value( $custom_field_id, $customer_id ) {
312	global $wpdb;
313
314	return $wpdb->get_var( $wpdb->prepare( "SELECT Meta_Value FROM $this->meta_table_name WHERE Field_ID=%d AND Customer_ID=%d", $custom_field_id, $customer_id ) );
315	}
316
317
318	/**
319	* Accepts a customer object, inserts it into the database, and returns the ID of the newly inserted customer
320	* @since 3.0.0
321	*/
322	public function insert_customer( $customer ) {
323	global $wpdb;
324	global $ewd_otp_controller;
325
326	$query_args = array(
327	'Customer_Number' => ! empty( $customer->number ) ? $customer->number : '',
328	'Customer_Name' => ! empty( $customer->name ) ? $customer->name : '',
329	'Customer_Email' => ! empty( $customer->email ) ? $customer->email : '',
330	'Sales_Rep_ID' => ! empty( $customer->sales_rep ) ? $customer->sales_rep : 0,
331	'Customer_WP_ID' => ! empty( $customer->wp_id ) ? $customer->wp_id : 0,
332	'Customer_FEUP_ID' => ! empty( $customer->feup_id ) ? $customer->feup_id : 0,
333	'Customer_Created' => date( 'Y-m-d H:i:s' ),
334	);
335
336	$wpdb->insert(
337	$this->customers_table_name,
338	$query_args
339	);
340
341	$customer_id = $wpdb->insert_id;
342
343	if ( ! $customer_id ) { return $customer_id; }
344
345	$custom_fields = $ewd_otp_controller->settings->get_customer_custom_fields();
346
347	foreach ( $custom_fields as $custom_field ) {
348
349	if ( empty( $customer->custom_fields[ $custom_field->id ] ) ) { continue; }
350
351	$query_args = array(
352	'Field_ID' => $custom_field->id,
353	'Customer_ID' => $customer_id,
354	'Meta_Value' => $customer->custom_fields[ $custom_field->id ]
355	);
356
357	$wpdb->insert(
358	$this->meta_table_name,
359	$query_args
360	);
361	}
362
363	return $customer_id;
364	}
365
366	/**
367	* Accepts a customer object, updates it in the database, and returns the ID if successful or false otherwise
368	* @since 3.0.0
369	*/
370	public function update_customer( $customer ) {
371	global $wpdb;
372	global $ewd_otp_controller;
373
374	if ( empty( $customer->id ) ) { return false; }
375
376	$query_args = array(
377	'Customer_Number' => ! empty( $customer->number ) ? $customer->number : '',
378	'Customer_Name' => ! empty( $customer->name ) ? $customer->name : '',
379	'Customer_Email' => ! empty( $customer->email ) ? $customer->email : '',
380	'Sales_Rep_ID' => ! empty( $customer->sales_rep ) ? $customer->sales_rep : 0,
381	'Customer_WP_ID' => ! empty( $customer->wp_id ) ? $customer->wp_id : 0,
382	'Customer_FEUP_ID' => ! empty( $customer->feup_id ) ? $customer->feup_id : 0,
383	);
384
385	$wpdb->update(
386	$this->customers_table_name,
387	$query_args,
388	array( 'Customer_ID' => $customer->id )
389	);
390
391	$custom_fields = $ewd_otp_controller->settings->get_customer_custom_fields();
392
393	foreach ( $custom_fields as $custom_field ) {
394
395	$wpdb->get_var( $wpdb->prepare( "SELECT Meta_Value from $this->meta_table_name WHERE Field_ID=%d AND Customer_ID=%d ORDER BY Meta_ID DESC", $custom_field->id, $customer->id ) );
396
397	$update = $wpdb->num_rows ? true : false;
398
399	if ( empty( $customer->custom_fields[ $custom_field->id ] ) ) {
400
401	$where_args = array(
402	'Field_ID' => $custom_field->id,
403	'Customer_ID' => $customer->id,
404	);
405
406	$wpdb->delete(
407	$this->meta_table_name,
408	$where_args
409	);
410	}
411	elseif ( $update ) {
412
413	$query_args = array(
414	'Meta_Value' => $customer->custom_fields[ $custom_field->id ]
415	);
416
417	$where_args = array(
418	'Field_ID' => $custom_field->id,
419	'Customer_ID' => $customer->id,
420	);
421
422	$wpdb->update(
423	$this->meta_table_name,
424	$query_args,
425	$where_args
426	);
427	}
428	else {
429
430	$query_args = array(
431	'Meta_Value' => $customer->custom_fields[ $custom_field->id ],
432	'Field_ID' => $custom_field->id,
433	'Customer_ID' => $customer->id,
434	);
435
436	$wpdb->insert(
437	$this->meta_table_name,
438	$query_args
439	);
440	}
441	}
442
443	return $customer->id;
444	}
445
446	/**
447	* Accepts a customer id, deletes the corresponding customer
448	* @since 3.0.0
449	*/
450	public function delete_customer( $customer_id ) {
451	global $wpdb;
452
453	$wpdb->delete(
454	$this->customers_table_name,
455	array( 'Customer_ID' => $customer_id )
456	);
457
458	$wpdb->delete(
459	$this->meta_table_name,
460	array( 'Customer_ID' => $customer_id )
461	);
462	}
463
464	/**
465	* Accepts an email and customer id, and verify that they match the saved data,
466	* @since 3.0.0
467	*/
468	public function verify_customer_email( $email, $customer_id ) {
469	global $wpdb;
470
471	$customer_email = $wpdb->get_var( $wpdb->prepare( "SELECT Customer_Email FROM $this->customers_table_name WHERE Customer_ID=%d", $customer_id ) );
472
473	if ( $customer_email == $email ) {
474
475	return true;
476	}
477
478	return false;
479	}
480	}
481	}