storeengine / includes / models / order.php

order.php in StoreEngine — Complete eCommerce Solution with Memberships, Licensing, Affiliates & More 2.2.0, at includes/models/order.php

990 lines 37.4 KB

Raw Download Zip

1	<?php
2
3	namespace StoreEngine\models;
4
5	use StoreEngine\Addons\Subscription\Classes\SubscriptionCollection;
6	use StoreEngine\Classes\AbstractModel;
7	use StoreEngine\Classes\ProductFactory;
8	use StoreEngine\Utils\Helper;
9	use WP_Error;
10
11
12	if ( ! defined( 'ABSPATH' ) ) {
13	exit;
14	}
15
16	/**
17	* @deprecated - Use `Order` entity or `OrderCollection` class instead.
18	*
19	* @see \StoreEngine\Classes\Order
20	* @see \StoreEngine\Classes\OrderCollection
21	*/
22	class Order extends AbstractModel {
23
24	protected string $table = 'storeengine_orders';
25	protected string $primary_key = 'id';
26	public array $data;
27
28	public function __construct( $order_id = null ) {
29	parent::__construct();
30	if ( null !== $order_id ) {
31	$this->data = $this->get_by_primary_key( $order_id );
32	}
33	}
34
35	public function get_order_by_hash( $order_hash = null ) {
36	$order_hash = $order_hash ?? Helper::get_cart_hash_from_cookie();
37	if ( ! $order_hash ) {
38	return new WP_Error( 'failed-to-fetch', __( 'No hash set in cookie', 'storeengine' ) );
39	}
40	global $wpdb;
41	$order = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders WHERE hash = %s AND status !='draft'", $order_hash ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
42	if ( ! $order ) {
43	return [];
44	}
45
46	$order = $order[0];
47	$order['meta'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders_meta WHERE order_id = %d", $order[0]['id'] ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
48	$order['purchase_items'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id = %d", $order[0]['id'] ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
49	$order['order_billing_address'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_addresses WHERE order_id = %d", $order[0]['id'] ) )[0]; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
50
51	return $order;
52	}
53
54
55	public function save( $args = [] ) {
56	$defaults = [
57	'status' => '',
58	'currency' => '',
59	'type' => '',
60	'tax_amount' => '',
61	'total_amount' => '',
62	'customer_id' => is_user_logged_in() ? get_current_user_id() : '',
63	'billing_email' => '',
64	'date_created_gmt' => current_time( 'mysql', 1 ),
65	'date_updated_gmt' => current_time( 'mysql', 1 ),
66	'parent_order_id' => '',
67	'payment_method' => '',
68	'payment_method_title' => '',
69	'transaction_id' => '',
70	'ip_address' => Helper::get_user_ip(),
71	'user_agent' => ( isset( $_SERVER['HTTP_USER_AGENT'] ) ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : null,
72	'hash' => Helper::get_cart_hash_from_cookie(),
73
74	];
75
76	$order_args = array_filter( $args, fn( $value ) => ! is_array( $value ) );
77	$data = wp_parse_args( $order_args, $defaults );
78	$inserted = $this->wpdb->insert(
79	$this->table,
80	$data,
81	[
82	'%s',
83	'%s',
84	'%s',
85	'%f',
86	'%f',
87	'%d',
88	'%s',
89	'%s',
90	'%d',
91	'%s',
92	'%s',
93	'%s',
94	'%s',
95	'%s',
96	]
97	);
98	if ( ! $inserted ) {
99	return new WP_Error( 'failed-to-insert', $this->wpdb->last_error );
100	}
101	$order_id = $this->wpdb->insert_id;
102	$meta_insert_result = $this->order_meta_insert( $order_id, $args['meta'] );
103	if ( is_wp_error( $meta_insert_result ) ) {
104	return $meta_insert_result;
105	}
106	$lookup_insert_result = $this->order_items_and_lookup_insert( $order_id, $args['purchase_items'] );
107	if ( is_wp_error( $lookup_insert_result ) ) {
108	return $lookup_insert_result;
109	}
110	$address_insert_result = $this->order_billing_address_insert( $order_id, $args['order_billing_address'] );
111	if ( is_wp_error( $address_insert_result ) ) {
112	return $address_insert_result;
113	}
114	$inserted_order = $this->get_by_primary_key( $order_id );
115	do_action( 'storeengine/models/after_create_order', $inserted_order );
116
117	return $inserted_order;
118	}
119
120	public function order_meta_insert( $order_id, $items ) {
121	if ( is_array( $items ) && count( $items ) > 0 ) {
122	foreach ( $items as $key => $item ) {
123	$this->wpdb->insert( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
124	$this->wpdb->prefix . 'storeengine_orders_meta',
125	[
126	'order_id' => $order_id,
127	'meta_key' => $key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
128	'meta_value' => $item, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
129	],
130	[ '%d', '%s', '%s' ]
131	);
132
133	if ( ! $this->wpdb->insert_id ) {
134	return new WP_Error( 'failed-to-insert', $this->wpdb->last_error, 'storeengine' );
135	}
136	}
137	}
138
139	return false;
140	}
141
142	private function order_items_and_lookup_insert( $order_id, $order_item_details ) {
143	foreach ( $order_item_details as $single_item ) {
144	$this->wpdb->insert(
145	$this->wpdb->prefix . 'storeengine_order_items',
146	[
147	'order_item_name' => get_the_title( $single_item['product_id'] ),
148	'order_item_type' => 'product',
149	'order_id' => $order_id,
150	],
151	[ '%s', '%s', '%d' ]
152	);
153
154	$product_model = new Product();
155	$price_expiry_date = $product_model->get_price_expiry_date( $single_item['price_id'] );
156
157	$result = $this->wpdb->insert(
158	$this->wpdb->prefix . 'storeengine_order_product_lookup',
159	[
160	'order_item_id' => $this->wpdb->insert_id,
161	'order_id' => $order_id,
162	'product_id' => $single_item['product_id'],
163	'variation_id' => $single_item['variation_id'] ?? 0,
164	'price_id' => $single_item['price_id'],
165	'price' => $single_item['price'],
166	'customer_id' => is_user_logged_in() ? get_current_user_id() : '',
167	'date_created' => current_time( 'mysql', 1 ),
168	'product_qty' => $single_item['product_qty'],
169	'product_net_revenue' => /TODO: its needs to be calculated/ 0,
170	'product_gross_revenue' => /TODO: its needs to be calculated/ 0,
171	'coupon_amount' => $single_item['coupon_amount'] ?? 0,
172	'tax_amount' => $single_item['tax_amount'] ?? 0,
173	'shipping_amount' => $single_item['shipping_amount'] ?? 0,
174	'shipping_tax_amount' => $single_item['shipping_tax_amount'] ?? 0,
175	'shipping_status' => $single_item['shipping_status'] ?? '',
176	'expire_date' => $price_expiry_date ?? '0000-00-00 00:00:00',
177	],
178	[
179	'%d', // order_item_id
180	'%d', // order_id
181	'%d', // product_id
182	'%d', // validation_id
183	'%d', // price_id
184	'%f', // price
185	'%d', // customer_id
186	'%s', // date_created
187	'%d', // product_qty
188	'%f', // product_net_revenue
189	'%f', // product_gross_revenue
190	'%f', // coupon_amount
191	'%f', // tax_amount
192	'%f', // shipping_amount
193	'%f', // shipping_tax_amount
194	'%s', // shipping_status
195	'%s', // expire_date
196	]
197	);
198	}//end foreach
199
200	if ( false === $result ) {
201	return new WP_Error( 'failed-to-insert', $this->wpdb->last_error, 'storeengine' );
202	}
203
204	// There might be a bug present exception handling is not done properly
205
206	return true;
207	}
208
209	public function order_billing_address_insert( $order_id, $billing_address ) {
210	$this->wpdb->insert(
211	$this->wpdb->prefix . 'storeengine_order_addresses',
212	[
213
214	'order_id' => $order_id,
215	'address_type' => 'Billing Address',
216	'first_name' => $billing_address['first_name'],
217	'last_name' => $billing_address['last_name'],
218	'company' => $billing_address['company'],
219	'address_1' => $billing_address['address_1'],
220	'address_2' => $billing_address['address_2'],
221	'city' => $billing_address['city'],
222	'state' => $billing_address['state'],
223	'postcode' => $billing_address['postcode'],
224	'country' => $billing_address['country'],
225	'email' => $billing_address['email'],
226	'phone' => $billing_address['phone'],
227
228	],
229	[
230	'%d',
231	'%s',
232	'%s',
233	'%s',
234	'%s',
235	'%s',
236	'%s',
237	'%s',
238	'%s',
239	'%s',
240	'%s',
241	'%s',
242	'%s',
243	]
244	);
245
246	if ( ! $this->wpdb->insert_id ) {
247	return new WP_Error( 'failed-to-insert', $this->wpdb->last_error, 'storeengine' );
248	}
249
250	return true;
251	}
252
253	public function get_order_hash( $order_id ) {
254	global $wpdb;
255
256	$order_hash = $wpdb->get_results( $wpdb->prepare( "SELECT hash FROM {$wpdb->prefix}storeengine_orders WHERE id = %d", $order_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
257
258	return $order_hash[0]['hash'];
259	}
260
261	public function order_meta_update( $order_id, $items ) {
262	if ( is_array( $items ) && count( $items ) > 0 ) {
263	foreach ( $items as $key => $item ) {
264	if ( is_array( $item ) ) {
265	$item = wp_json_encode( $item );
266	}
267	global $wpdb;
268	$have_meta = $wpdb->get_results( $wpdb->prepare( "SELECT order_id, meta_key FROM {$wpdb->prefix}storeengine_orders_meta WHERE order_id=%d AND meta_key=%s", intval( $order_id ), $key ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key
269	if ( count( $have_meta ) === 0 ) {
270	$this->wpdb->insert( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
271	$this->wpdb->prefix . 'storeengine_orders_meta',
272	[
273	'order_id' => $order_id,
274	'meta_key' => $key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
275	'meta_value' => $item, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
276	],
277	[ '%d', '%s', '%s' ]
278	);
279	} else {
280	$this->wpdb->update( // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
281	$this->wpdb->prefix . 'storeengine_orders_meta',
282	[
283	'meta_key' => $key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
284	'meta_value' => $item, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
285	],
286	[
287	'order_id' => $order_id,
288	'meta_key' => $key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
289	],
290	[ '%s', '%s' ],
291	[ '%d', '%s' ]
292	);
293	}//end if
294	}//end foreach
295	return true;
296	}//end if
297	return false;
298	}
299
300	public function update( int $id, array $args ) {
301	$order_table_data = [
302	'status' => $args['status'],
303	'currency' => $args['currency'],
304	'type' => $args['type'],
305	'tax_amount' => $args['tax_amount'],
306	'total_amount' => $args['total_amount'],
307	'customer_id' => $args['customer_id'],
308	'billing_email' => $args['billing_email'],
309	'date_updated_gmt' => current_time( 'mysql', 1 ),
310	'parent_order_id' => $args['parent_order_id'],
311	'payment_method' => $args['payment_method'],
312	'payment_method_title' => $args['payment_method_title'],
313	'transaction_id' => $args['transaction_id'],
314	'ip_address' => Helper::get_user_ip(),
315	'user_agent' => ( isset( $_SERVER['HTTP_USER_AGENT'] ) ) ? sanitize_text_field( wp_unslash( $_SERVER['HTTP_USER_AGENT'] ) ) : null,
316	];
317
318	$this->wpdb->update(
319	$this->wpdb->prefix . 'storeengine_orders',
320	$order_table_data,
321	[ 'id' => $id ],
322	[
323	'%s', // status
324	'%s', // currency
325	'%s', // type
326	'%f', // tax_amount
327	'%f', // total_amount
328	'%d', // customer_id
329	'%s', // billing_email
330	'%s', // date_updated_gmt
331	'%d', // parent_order_id
332	'%s', // payment_method
333	'%s', // payment_method_title
334	'%s', // transaction_id
335	'%s', // ip_address
336	'%s', // user_agent
337	],
338	[ '%d' ]
339	);
340
341	$meta_update_result = $this->order_meta_update( $id, $args['meta'] );
342	$order_items_update_result = $this->order_items_and_lookup_update( $id, $args['purchase_items'] );
343	$address_update_result = $this->order_billing_address_update( $id, $args['order_billing_address'] );
344	if ( is_wp_error( $meta_update_result ) ) {
345	return $meta_update_result->get_error_message();
346	}
347	if ( is_wp_error( $order_items_update_result ) ) {
348	return $order_items_update_result->get_error_message();
349	}
350	if ( is_wp_error( $address_update_result ) ) {
351	return $address_update_result->get_error_message();
352	}
353
354	$updated_order = $this->get_by_primary_key( $id );
355	do_action( 'storeengine/models/after_update_order', $updated_order );
356
357	return $updated_order;
358	}
359
360	private function order_items_and_lookup_update( int $order_id, $purchase_items ) {
361	global $wpdb;
362	$exist_order_items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_items WHERE order_id=%d", $order_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
363	if ( count( $exist_order_items ) === 0 ) {
364	return new WP_Error( 'failed-to-update', __( 'No order items found with the provided ID', 'storeengine' ) );
365	}
366	foreach ( $purchase_items as $key => $item ) {
367	$this->wpdb->update(
368	$this->wpdb->prefix . 'storeengine_order_items',
369	[
370	'order_item_name' => get_the_title( $item->product_id ),
371	'order_item_type' => 'product',
372	'order_id' => $order_id,
373	],
374	[
375	'order_item_id' => $exist_order_items[ $key ]->order_item_id,
376	'order_id' => $order_id,
377	],
378	[ '%s', '%s', '%d' ],
379	[ '%d', '%d' ]
380	);
381	$item = (object) $item;
382	$this->wpdb->update(
383	$this->wpdb->prefix . 'storeengine_order_product_lookup',
384	[
385	'order_item_id' => $exist_order_items[ $key ]->order_item_id,
386	'order_id' => $order_id,
387	'product_id' => $item->product_id,
388	'variation_id' => $item->variation_id,
389	'price_id' => $item->price_id,
390	'customer_id' => is_user_logged_in() ? get_current_user_id() : null,
391	'product_qty' => $item->product_qty,
392	'product_net_revenue' => $item->product_net_revenue ?? 0,
393	'product_gross_revenue' => $item->product_gross_revenue ?? 0,
394	'coupon_amount' => $item->coupon_amount,
395	'tax_amount' => $item->tax_amount ?? 0,
396	'shipping_amount' => $item->shipping_amount ?? 0,
397	'shipping_tax_amount' => $item->shipping_tax_amount ?? 0,
398	],
399	[
400	'order_item_id' => $exist_order_items[ $key ]->order_item_id,
401	'order_id' => $order_id,
402	],
403	[
404	'%d',
405	'%d',
406	'%d',
407	'%d',
408	'%d',
409	'%d',
410	'%s',
411	'%d',
412	'%f',
413	'%f',
414	'%f',
415	'%f',
416	'%f',
417	'%f',
418	],
419	[
420	'%d',
421	'%d',
422	]
423	);
424	}//end foreach
425
426	return true;
427	}
428
429	public function order_billing_address_update( $order_id, $billing_address ) {
430	global $wpdb;
431
432	$result = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix }storeengine_order_addresses WHERE order_id=%d", $order_id ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
433
434	if ( count( $result ) === 0 ) {
435	return new WP_Error( 'failed-to-update', __( 'No order billing address found with the provided ID', 'storeengine' ) );
436	}
437
438	$billing_address = (array) $billing_address;
439
440	$this->wpdb->update(
441	$this->wpdb->prefix . 'storeengine_order_addresses',
442	[
443	'address_type' => 'Billing Address',
444	'first_name' => $billing_address['first_name'],
445	'last_name' => $billing_address['last_name'],
446	'company' => $billing_address['company'],
447	'address_1' => $billing_address['address_1'],
448	'address_2' => $billing_address['address_2'],
449	'city' => $billing_address['city'],
450	'state' => $billing_address['state'],
451	'postcode' => $billing_address['postcode'],
452	'country' => $billing_address['country'],
453	'email' => $billing_address['email'],
454	'phone' => $billing_address['phone'],
455	],
456	[
457	'order_id' => $order_id,
458	'id' => 0,
459	],
460	[
461	'%s',
462	'%s',
463	'%s',
464	'%s',
465	'%s',
466	'%s',
467	'%s',
468	'%s',
469	'%s',
470	'%s',
471	'%s',
472	],
473	[
474	'%d',
475	'%d',
476	]
477	);
478
479	$this->wpdb->update(
480	$this->wpdb->prefix . 'storeengine_order_addresses',
481	[
482	'address_type' => 'shipping_address',
483	'first_name' => $shipping_address['first_name'],
484	'last_name' => $shipping_address['last_name'],
485	'company' => $shipping_address['company'],
486	'address_1' => $shipping_address['address_1'],
487	'address_2' => $shipping_address['address_2'],
488	'city' => $shipping_address['city'],
489	'state' => $shipping_address['state'],
490	'postcode' => $shipping_address['postcode'],
491	'country' => $shipping_address['country'],
492	'email' => $shipping_address['email'],
493	'phone' => $shipping_address['phone'],
494	],
495	[
496	'order_id' => $order_id,
497	'id' => $shipping_address_id,
498	],
499	[
500	'%s',
501	'%s',
502	'%s',
503	'%s',
504	'%s',
505	'%s',
506	'%s',
507	'%s',
508	'%s',
509	'%s',
510	'%s',
511	],
512	[
513	'%d',
514	'%d',
515	]
516	);
517
518	return true;
519	}
520
521	public function get_by_primary_key( $key_value ) {
522	global $wpdb;
523
524	$order_data = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders WHERE id = %d LIMIT 1;", $key_value ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
525
526	if ( ! $order_data ) {
527	return [];
528	}
529
530	$order_data = $order_data[0];
531	$order_data['meta'] = [];
532
533	$metas = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders_meta WHERE order_id = %d", $key_value ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
534	$order_data['purchase_items'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id = %d", $key_value ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
535	$order_data['order_billing_address'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_addresses WHERE order_id = %d", $key_value ) )[0]; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
536
537	foreach ( $metas as $meta_data ) {
538	$order_data['meta'][ $meta_data->meta_key ] = $meta_data->meta_value; // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key -- this is not a meta query.
539	}
540
541
542	if ( 'subscription' === $order_data['type'] ) {
543	$order_data['billing_periods'] = $this->get_billing_periods_for_subscription( $order_data['id'] );
544	}
545
546	return $order_data;
547	}
548
549	public function get_order_id_from_hash( $hash ) {
550	global $wpdb;
551
552	$order_id = $wpdb->get_results( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}storeengine_orders WHERE hash = %s", $hash ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
553
554	return $order_id[0]['id'];
555	}
556
557	public function delete( ?int $id = null ) {
558	if ( ! $id ) {
559	return new WP_Error( 'failed-to-delete', __( 'No ID provided', 'storeengine' ) );
560	}
561
562	$order = $this->get_by_primary_key( $id );
563	if ( is_wp_error( $order ) ) {
564	return new WP_Error( 'failed-to-delete', __( 'No order found with the provided ID', 'storeengine' ) );
565	}
566
567	global $wpdb;
568	$wpdb->query( 'START TRANSACTION' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
569	$is_delete_order = $wpdb->delete( $wpdb->prefix . 'storeengine_orders', [ 'id' => $id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
570	$is_delete_order_meta = $wpdb->delete( $wpdb->prefix . 'storeengine_orders_meta', [ 'order_id' => $id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
571	$is_delete_order_items = $wpdb->delete( $wpdb->prefix . 'storeengine_order_items', [ 'order_id' => $id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
572	$is_delete_order_product_lookup = $wpdb->delete( $wpdb->prefix . 'storeengine_order_product_lookup', [ 'order_id' => $id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
573	$is_delete_order_billing_address = $wpdb->delete( $wpdb->prefix . 'storeengine_order_addresses', [ 'order_id' => $id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
574	if ( $is_delete_order && $is_delete_order_meta && $is_delete_order_items && $is_delete_order_product_lookup && $is_delete_order_billing_address ) {
575	$wpdb->query( 'COMMIT' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
576
577	return $is_delete_order;
578	}
579	// Something went wrong. Rollback.
580	$wpdb->query( 'ROLLBACK' ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
581	// return wp error with db error
582	return new WP_Error( 'failed-to-delete', $wpdb->last_error );
583	}
584
585	public function get_all_orders( $per_page, $offset, $order_status, $type = 'order' ) {
586	global $wpdb;
587
588	if ( ! in_array( $type, [ 'order', 'refund_order' ], true ) ) {
589	return [];
590	}
591
592	$args = [ $type, 'draft', 'subscription' ];
593	$order_query = '';
594
595	if ( $order_status && 'any' !== $order_status ) {
596	$order_query .= ' AND status = %s';
597	$args[] = $order_status;
598	}
599
600	$order_query .= ' ORDER BY date_created_gmt DESC';
601
602	if ( $per_page && $offset ) {
603	$order_query .= ' LIMIT %d, %d';
604	$args[] = $offset;
605	$args[] = $per_page;
606	}
607
608	$orders = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders WHERE type = %s AND status != %s AND type != %s {$order_query};", $args ), ARRAY_A ); // phpcs:ignore PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber -- query build above.
609
610	foreach ( $orders as $key => $order ) {
611	// Fetch meta data.
612	$purchase_items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id = %d", $order['id'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
613
614	// Prepare meta data for output.
615	$orders[ $key ]['meta'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders_meta WHERE order_id = %d", $order['id'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
616	$orders[ $key ]['purchase_items'] = $this->prepare_purchase_items_for_response( $purchase_items );
617	$orders[ $key ]['order_billing_address'] = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_addresses WHERE order_id = %d", $order['id'] ), ARRAY_A )[0]; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
618	}
619
620	return $orders;
621	}
622
623	public function prepare_purchase_items_for_response( $purchase_items ) {
624	foreach ( $purchase_items as $key => $item ) {
625	$purchase_items[ $key ]['product_type'] = get_post_meta( $item['product_id'], '_storeengine_product_type', true );
626	$purchase_items[ $key ]['product_name'] = get_the_title( $item['product_id'] );
627	}
628
629	return $purchase_items;
630	}
631
632
633	public function get_orders_by_customer_id( int $customer_id ): array {
634	global $wpdb;
635
636	$all_order = $wpdb->get_results( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}storeengine_orders WHERE customer_id = %d AND status !='draft' AND type!='subscription'", $customer_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
637
638	if ( ! $all_order ) {
639	return [];
640	}
641
642	$orders = [];
643	foreach ( $all_order as $order ) {
644	$orders[] = $this->get_by_primary_key( $order['id'] );
645	}
646
647	return $orders;
648	}
649
650	/**
651	* @param int $customer_id
652	*
653	* @return array
654	*
655	* @deprecated
656	* @see SubscriptionCollection
657	*/
658	public function get_subscriptions_by_customer_id( int $customer_id ): array {
659	global $wpdb;
660
661	$all_order = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders WHERE customer_id = %d AND type = 'subscription'", $customer_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
662
663	$orders = [];
664	foreach ( $all_order as $order ) {
665	$orders[] = $this->get_by_primary_key( $order['id'] );
666	}
667
668	return $orders;
669	}
670
671
672	public function update_order_billing_details_by_key( $order_id, $key, $value ): bool {
673	global $wpdb;
674
675	$wpdb->update( $wpdb->prefix . 'storeengine_order_addresses', [ $key => $value ], [ 'order_id' => $order_id ] ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
676
677	return true;
678	}
679
680	public function get_purchase_items( $order_id ) {
681	global $wpdb;
682
683	return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id = %d", $order_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
684	}
685
686
687	public function get_orders_with_non_zero_payment(): array {
688	global $wpdb;
689	$orders = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}storeengine_orders WHERE total_amount > 0", ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
690	foreach ( $orders as $key => $order ) {
691	$orders_meta = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_orders_meta WHERE order_id = %d", $order['id'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
692	$purchase_items = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id = %d", $order['id'] ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
693	$shipping_address = $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_addresses WHERE order_id = %d", $order['id'] ), ARRAY_A )[0]; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
694	$orders[ $key ]['meta'] = $orders_meta;
695	$orders[ $key ]['purchase_items'] = $purchase_items;
696	$orders[ $key ]['order_billing_address'] = $shipping_address;
697	}
698
699	return $orders;
700	}
701
702	public static function add_order_meta( $order_id, $meta_key, $meta_value ) {
703	global $wpdb;
704	$wpdb->insert( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key
705	$wpdb->prefix . 'storeengine_orders_meta',
706	[
707	'order_id' => $order_id,
708	'meta_key' => $meta_key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
709	'meta_value' => $meta_value, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
710	],
711	[ '%d', '%s', '%s' ]
712	);
713	}
714
715	public static function get_order_meta( $order_id, $meta_key ) {
716	global $wpdb;
717	$meta = $wpdb->get_results( $wpdb->prepare( "SELECT meta_value FROM {$wpdb->prefix}storeengine_orders_meta WHERE order_id = %d AND meta_key = %s", $order_id, $meta_key ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key
718	if ( ! $meta ) {
719	return [];
720	}
721
722	return $meta[0]['meta_value'];
723	}
724
725	public static function update_order_meta( $order_id, $meta_key, $meta_value ) {
726	global $wpdb;
727	$wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key
728	$wpdb->prefix . 'storeengine_orders_meta',
729	[ 'meta_value' => $meta_value ], // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_value
730	[
731	'order_id' => $order_id,
732	'meta_key' => $meta_key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
733	],
734	[ '%s' ],
735	[ '%d', '%s' ]
736	);
737	}
738
739	public static function delete_order_meta( $order_id, $meta_key ) {
740	global $wpdb;
741	$wpdb->delete( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.SlowDBQuery.slow_db_query_meta_key
742	$wpdb->prefix . 'storeengine_orders_meta',
743	[
744	'order_id' => $order_id,
745	'meta_key' => $meta_key, // phpcs:ignore WordPress.DB.SlowDBQuery.slow_db_query_meta_key
746	],
747	[ '%d', '%s' ]
748	);
749	}
750
751
752	public function get_subscription_orders(): array {
753	global $wpdb;
754	$orders = $wpdb->get_results( "SELECT id FROM {$wpdb->prefix}storeengine_orders WHERE type = 'subscription' AND status='active'", ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
755
756	if ( ! $orders ) {
757	return [];
758	}
759	$subscription_orders = [];
760	foreach ( $orders as $order ) {
761	$subscription_orders[] = $this->get_by_primary_key( $order['id'] );
762	}
763
764	return $subscription_orders;
765	}
766
767
768	public function get_subscription_orders_by_customer_id( int $customer_id ): array {
769	global $wpdb;
770	$all_order = $wpdb->get_results( $wpdb->prepare( "SELECT id FROM {$wpdb->prefix}storeengine_orders WHERE customer_id = %d AND status !='draft' AND type='subscription'", $customer_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
771	if ( ! $all_order ) {
772	return [];
773	}
774
775	$orders = [];
776	foreach ( $all_order as $order ) {
777	$orders[] = $this->get_by_primary_key( $order['id'] );
778	}
779
780	return $orders;
781	}
782
783	public function get_total_spent_by_customer_id( int $customer_id ) {
784	global $wpdb;
785	$total_spent = $wpdb->get_results( $wpdb->prepare( "SELECT SUM(total_amount) as total_spent FROM {$wpdb->prefix}storeengine_orders WHERE customer_id = %d", $customer_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
786
787	return $total_spent[0]['total_spent'];
788	}
789
790	/**
791	* @param $order_data
792	*
793	* @return true\|WP_Error
794	* @deprecated
795	*/
796	public function cancel_subscription( $order_data ) {
797	if ( 'subscription' !== $order_data['type'] ) {
798	return new WP_Error( 'failed-to-cancel', __( 'This is not a subscription order', 'storeengine' ) );
799	}
800
801	do_action( 'storeengine/models/before_cancel_subscription', $order_data );
802	$this->update( $order_data['id'], [ 'status' => 'cancelled' ] );
803
804	return true;
805	}
806
807	public function get_renewal_subscriptions( int $current_time ) {
808	global $wpdb;
809	$order_ids = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
810	$wpdb->prepare(
811	"SELECT order_id
812	FROM {$wpdb->prefix}storeengine_orders_meta om
813	WHERE om.meta_key = 'next_payment_date'
814	AND om.meta_value < %d;", $current_time ), ARRAY_A );
815	if ( ! $order_ids ) {
816	return [];
817	}
818
819	$subscriptions = [];
820	foreach ( $order_ids as $order_id ) {
821	$subscriptions[] = $this->get_by_primary_key( $order_id['order_id'] );
822	}
823
824	return $subscriptions;
825	}
826
827	public function order_meta_delete( $id, string $string ) {
828	global $wpdb;
829	$result = $wpdb->get_results( $wpdb->prepare( "DELETE FROM {$wpdb->prefix}storeengine_orders_meta WHERE order_id = %d AND meta_key = %s", $id, $string ) ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
830
831	if ( ! $result ) {
832	return new WP_Error( 'failed-to-delete', __( 'No meta found with the provided key', 'storeengine' ) );
833	}
834
835	return true;
836	}
837
838	public function products_purchased_by_customer( $id ): array {
839	global $wpdb;
840	$products = $wpdb->get_results( $wpdb->prepare( "SELECT product_id, SUM(product_qty) as total_qty FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id IN (SELECT id FROM {$wpdb->prefix}storeengine_orders WHERE customer_id = %d) GROUP BY product_id", $id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
841
842	$formatted_products = [];
843	foreach ( $products as $index => $product ) {
844	$formatted_products[ $index ]['product_id'] = $product['product_id'];
845	$formatted_products[ $index ]['product_name'] = get_the_title( $product['product_id'] );
846	$formatted_products[ $index ]['total_quantity'] = $product['total_qty'];
847	}
848
849	return $formatted_products;
850	}
851
852	public function get_order_items( $order_id ) {
853	global $wpdb;
854
855	return $wpdb->get_results( $wpdb->prepare( "SELECT * FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id = %d", $order_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
856	}
857
858	public function is_subscription_order( $order_id ) {
859	global $wpdb;
860	$order = $wpdb->get_results( $wpdb->prepare( "SELECT type FROM {$wpdb->prefix}storeengine_orders WHERE id = %d", $order_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
861
862	return 'subscription' === $order[0]['type'];
863	}
864
865	public function get_subscription_by_order_id( $order_id ): array {
866	$subscription = $this->get_by_primary_key( $order_id );
867
868	if ( 'subscription' !== $subscription['type'] \|\| 'renewed' === $subscription['status'] ) {
869	return [];
870	}
871
872	return $subscription;
873	}
874
875
876	public function get_billing_periods_for_subscription( $subscription_id ): array {
877	global $wpdb;
878	$billing_periods = $wpdb->get_results( $wpdb->prepare( "SELECT id,total_amount,date_created_gmt,status FROM {$wpdb->prefix}storeengine_orders WHERE parent_order_id = %d AND status = 'renewed'", $subscription_id ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
879	$billing_periods_data = [];
880	foreach ( $billing_periods as $key => $renewed ) {
881	$billing_periods_data[ $key ]['id'] = $renewed['id'];
882	$billing_periods_data[ $key ]['amount'] = Helper::currency_format( $renewed['total_amount'] );
883	$billing_periods_data[ $key ]['renewal_date'] = $renewed['date_created_gmt'];
884	$billing_periods_data[ $key ]['status'] = $renewed['status'];
885	}
886
887	return $billing_periods_data;
888	}
889
890	public function get_order_meta_and_lookup_details( array $args = [] ) {
891	global $wpdb;
892
893	$default = [
894	'product_id' => $product_id ?? 0,
895	'order_id' => $order_id ?? 0,
896	];
897
898	$data = wp_parse_args( $args, $default );
899
900	// @FIXME sort & limit to return last item.
901	$result = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
902	$wpdb->prepare(
903	"SELECT * FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE order_id=%d AND product_id=%d",
904	$data['order_id'],
905	$data['product_id'],
906	),
907	ARRAY_A
908	);
909
910	if ( $result ) {
911	$result = current( $result );
912	}
913
914	return $result;
915	}
916
917	public function update_shipping_status( int $order_id, int $product_id, string $shipping_status ) {
918	global $wpdb;
919
920	return $wpdb->update( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
921	$wpdb->prefix . 'storeengine_order_product_lookup',
922	array(
923	'shipping_status' => $shipping_status,
924	),
925	array(
926	'order_id' => $order_id,
927	'product_id' => $product_id,
928	),
929	array(
930	'%s',
931	),
932	array(
933	'%d',
934	'%d',
935	),
936	);
937	}
938
939	public function get_single_product_shipping_status( $product_id, $order_id ) {
940	global $wpdb;
941
942	$result = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
943	$wpdb->prepare(
944	"SELECT shipping_status FROM {$wpdb->prefix}order_product_lookup WHERE product_id=%d AND order_id=%d",
945	$product_id,
946	$order_id,
947	)
948	);
949
950	return $result ? current( $result ) : [];
951	}
952
953	public function is_expired( $customer_id, $price_id ): bool {
954	global $wpdb;
955
956	$expired = $wpdb->get_results( // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
957	$wpdb->prepare(
958	"SELECT expire_date FROM {$wpdb->prefix}storeengine_order_product_lookup WHERE customer_id=%d AND price_id=%d",
959	$customer_id,
960	$price_id
961	)
962	);
963
964	if ( ! $expired ) {
965	return false;
966	}
967
968	$expired = current( $expired );
969
970	if ( '0000-00-00 00:00:00' === $expired->expire_date ) {
971	return true;
972	}
973
974	// compare current time with expire date in this '0000-00-00 00:00:00' format
975
976	return strtotime( $expired->expire_date ) < time();
977	}
978
979	public function get_order_by_meta( $meta_key, $meta_value ): array {
980	global $wpdb;
981	$order_id = $wpdb->get_results( $wpdb->prepare( "SELECT order_id FROM {$wpdb->prefix}storeengine_orders_meta WHERE meta_key = %s AND meta_value = %s", $meta_key, $meta_value ), ARRAY_A ); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
982
983	if ( ! $order_id ) {
984	return [];
985	}
986
987	return $this->get_by_primary_key( $order_id[0]['order_id'] );
988	}
989	}
990