tutor / models / OrderModel.php

OrderModel.php in Tutor LMS – eLearning and online course solution 3.9.2, at models/OrderModel.php

2,151 lines 57.3 KB

Raw Download Zip

1	<?php
2	/**
3	* Order Model
4	*
5	* @package Tutor\Models
6	* @author Themeum <support@themeum.com>
7	* @link https://themeum.com
8	* @since 3.0.0
9	*/
10
11	namespace Tutor\Models;
12
13	use Exception;
14	use TUTOR\Earnings;
15	use Tutor\Ecommerce\Tax;
16	use Tutor\Ecommerce\Ecommerce;
17	use Tutor\Helpers\QueryHelper;
18	use Tutor\Helpers\DateTimeHelper;
19	use Tutor\Ecommerce\BillingController;
20	use Tutor\Ecommerce\CheckoutController;
21	use Tutor\Ecommerce\OrderActivitiesController;
22
23	/**
24	* OrderModel Class
25	*
26	* @since 3.0.0
27	*/
28	class OrderModel {
29
30	/**
31	* Order status
32	*
33	* @since 3.0.0
34	*
35	* @var string
36	*/
37	const ORDER_INCOMPLETE = 'incomplete';
38	const ORDER_COMPLETED = 'completed';
39	const ORDER_CANCELLED = 'cancelled';
40	const ORDER_TRASH = 'trash';
41
42	/**
43	* Payment status
44	*
45	* @since 3.0.0
46	*
47	* @var string
48	*/
49	const PAYMENT_PAID = 'paid';
50	const PAYMENT_FAILED = 'failed';
51	const PAYMENT_UNPAID = 'unpaid';
52	const PAYMENT_REFUNDED = 'refunded';
53	const PAYMENT_PARTIALLY_REFUNDED = 'partially-refunded';
54
55	/**
56	* Payment methods
57	*
58	* @since 3.5.0
59	*
60	* @var string
61	*/
62	const PAYMENT_METHOD_MANUAL = 'manual';
63	const PAYMENT_METHOD_FREE = 'free';
64
65	/**
66	* Order Meta keys for history & refunds
67	*
68	* @since 3.0.0
69	*
70	* @var string
71	*/
72	const META_KEY_HISTORY = 'history';
73	const META_KEY_REFUND = 'refund';
74	const META_KEY_ORDER_ID = 'tutor_order_id_';
75	const META_KEY_BILLING_ADDRESS = 'billing_address';
76
77	/**
78	* Order meta for subscription order.
79	*
80	* @since 3.4.0
81	*
82	* @var string
83	*/
84	const META_ENROLLMENT_FEE = 'plan_enrollment_fee';
85	const META_TRIAL_FEE = 'plan_trial_fee';
86	const META_PLAN_INFO = 'plan_info';
87	const META_IS_PLAN_TRIAL_ORDER = 'is_plan_trial_order';
88	const META_IS_RESUBSCRIPTION_ORDER = 'is_resubscription_order';
89
90	/**
91	* Tax type constants
92	*
93	* @since 3.0.0
94	*
95	* @var string
96	*/
97	const TAX_TYPE_EXCLUSIVE = 'exclusive';
98	const TAX_TYPE_INCLUSIVE = 'inclusive';
99
100
101	/**
102	* Order type
103	*
104	* @since 3.0.0
105	*
106	* @var string
107	*/
108	const TYPE_SINGLE_ORDER = 'single_order';
109	const TYPE_SUBSCRIPTION = 'subscription';
110	const TYPE_RENEWAL = 'renewal';
111
112
113	/**
114	* Transient constants
115	*
116	* @since 3.0.0
117	*/
118	const TRANSIENT_ORDER_BADGE_COUNT = 'tutor_order_badge_count';
119
120	/**
121	* Order placement success
122	*
123	* @since 3.0.0
124	*/
125	const ORDER_PLACEMENT_SUCCESS = 'success';
126
127	/**
128	* Order placement failed
129	*
130	* @since 3.0.0
131	*/
132	const ORDER_PLACEMENT_FAILED = 'failed';
133
134	/**
135	* Order table name
136	*
137	* @since 3.0.0
138	*
139	* @var string
140	*/
141	private $table_name = 'tutor_orders';
142
143	/**
144	* Order item table name
145	*
146	* @since 3.0.0
147	*
148	* @var string
149	*/
150	private $order_item_table = 'tutor_order_items';
151
152	/**
153	* Order item fillable fields
154	*
155	* @since 3.0.0
156	*
157	* @var array
158	*/
159	private $order_items_fillable_fields = array(
160	'order_id',
161	'item_id',
162	'regular_price',
163	'sale_price',
164	'discount_price',
165	'coupon_code',
166	);
167
168	/**
169	* Resolve props & dependencies
170	*
171	* @since 3.0.0
172	*/
173	public function __construct() {
174	global $wpdb;
175	$this->table_name = $wpdb->prefix . $this->table_name;
176	$this->order_item_table = $wpdb->prefix . $this->order_item_table;
177	}
178
179	/**
180	* Get table name with wp prefix
181	*
182	* @since 3.0.0
183	*
184	* @return string
185	*/
186	public function get_table_name() {
187	return $this->table_name;
188	}
189
190	/**
191	* Get a order record.
192	*
193	* @since 3.6.0
194	*
195	* @param array $where where clause.
196	*
197	* @return mixed
198	*/
199	public function get_row( $where = array() ) {
200	return QueryHelper::get_row( $this->table_name, $where, 'id' );
201	}
202
203	/**
204	* Get recalculated order tax data.
205	*
206	* @since 3.4.0
207	* @since 3.8.0 tax re-calculation based on pre_tax_price column value.
208	*
209	* @param int\|object $order the order id or object.
210	*
211	* @return array
212	*/
213	public function get_recalculated_order_tax_data( $order ) {
214	$tax_type = Tax::get_tax_type();
215	$tax_rate = Tax::get_user_tax_rate( $order->user_id );
216	$order = self::get_order( $order );
217	$order_data = array(
218	'tax_type' => null,
219	'tax_rate' => null,
220	'tax_amount' => null,
221	);
222
223	if ( ! Tax::should_calculate_tax()
224	\|\| ! $tax_rate
225	\|\| ! $order
226	\|\| ! $order->pre_tax_price ) {
227	return $order_data;
228	}
229
230	$order_data['tax_type'] = $tax_type;
231	$order_data['tax_rate'] = $tax_rate;
232
233	if ( ! Tax::is_tax_included_in_price() ) {
234	// For exclusive tax type.
235	$tax_amount = Tax::calculate_tax( $order->pre_tax_price, $tax_rate );
236	$total_price = $order->pre_tax_price + $tax_amount;
237
238	$order_data['tax_amount'] = $tax_amount;
239	$order_data['total_price'] = $total_price;
240	$order_data['net_payment'] = $total_price;
241	} else {
242	// For inclusive tax type.
243	$tax_amount = Tax::calculate_tax( $order->total_price, $tax_rate );
244
245	$order_data['tax_amount'] = $tax_amount;
246	$order_data['pre_tax_price'] = $order->total_price - $tax_amount;
247	}
248
249	return $order_data;
250	}
251
252	/**
253	* Get order item display price.
254	*
255	* @since 3.5.0
256	*
257	* @param object $item order item object.
258	*
259	* @return string
260	*/
261	public function get_order_item_display_price( $item ) {
262	$display_price = is_numeric( $item->sale_price )
263	? $item->sale_price
264	: ( is_numeric( $item->discount_price ) ? $item->discount_price : $item->regular_price );
265	return $display_price;
266	}
267
268	/**
269	* Check order item has sale price or discount price
270	*
271	* @since 3.5.0
272	*
273	* @param object $item order item object.
274	*
275	* @return boolean
276	*/
277	public function has_order_item_sale_price( $item ) {
278	return is_numeric( $item->sale_price ) \|\| is_numeric( $item->discount_price );
279	}
280
281	/**
282	* Get all order statuses
283	*
284	* @since 3.0.0
285	*
286	* @return array
287	*/
288	public static function get_order_status() {
289	return array(
290	self::ORDER_INCOMPLETE => __( 'Incomplete', 'tutor' ),
291	self::ORDER_COMPLETED => __( 'Completed', 'tutor' ),
292	self::ORDER_CANCELLED => __( 'Cancelled', 'tutor' ),
293	self::ORDER_TRASH => __( 'Trash', 'tutor' ),
294	);
295	}
296
297	/**
298	* Get all order types
299	*
300	* @since 3.7.0
301	*
302	* @return array
303	*/
304	public static function get_order_type_list() {
305	return array(
306	self::TYPE_SINGLE_ORDER => __( 'Single Order', 'tutor' ),
307	self::TYPE_SUBSCRIPTION => __( 'Subscription', 'tutor' ),
308	self::TYPE_RENEWAL => __( 'Renewal', 'tutor' ),
309	);
310	}
311
312	/**
313	* Get all payment statuses
314	*
315	* @since 3.0.0
316	*
317	* @return array
318	*/
319	public static function get_payment_status() {
320	return array(
321	self::PAYMENT_PAID => __( 'Paid', 'tutor' ),
322	self::PAYMENT_UNPAID => __( 'Unpaid', 'tutor' ),
323	self::PAYMENT_FAILED => __( 'Failed', 'tutor' ),
324	self::PAYMENT_REFUNDED => __( 'Refunded', 'tutor' ),
325	self::PAYMENT_PARTIALLY_REFUNDED => __( 'Partially Refunded', 'tutor' ),
326	);
327	}
328
329	/**
330	* Get order items fillable fields
331	*
332	* @since 3.0.0
333	*
334	* @return array
335	*/
336	public function get_order_items_fillable_fields() {
337	return $this->order_items_fillable_fields;
338	}
339
340	/**
341	* Get searchable fields
342	*
343	* This method is intendant to use with get order list
344	*
345	* @since 3.0.0
346	*
347	* @return array
348	*/
349	private function get_searchable_fields() {
350	return array(
351	'o.id',
352	'o.transaction_id',
353	'o.coupon_code',
354	'o.payment_method',
355	'o.order_status',
356	'o.payment_status',
357	'u.display_name',
358	'u.user_login',
359	'u.user_email',
360	);
361	}
362
363	/**
364	* Create order
365	*
366	* Note: validate data before using this method
367	*
368	* This method will also insert items if
369	* item is set.
370	*
371	* Ex: data['order_items] = [
372	* user_id => 1,
373	* course_id => 1,
374	* regular_price => 100,
375	* sale_price => 90
376	* ]
377	*
378	* @since 3.0.0
379	*
380	* @param array $data Order data based on db table.
381	*
382	* @throws \Exception Database error if occur.
383	*
384	* @return int Order id on success
385	*/
386	public function create_order( array $data ) {
387	$order_items = $data['items'] ?? null;
388	unset( $data['items'] );
389
390	global $wpdb;
391
392	// Start transaction.
393	$wpdb->query( 'START TRANSACTION' );
394
395	try {
396	$order_id = QueryHelper::insert( $this->table_name, $data );
397	if ( $order_id ) {
398	if ( $order_items ) {
399	$insert = $this->insert_order_items( $order_id, $order_items );
400	if ( $insert ) {
401	$wpdb->query( 'COMMIT' );
402	return $order_id;
403	} else {
404	$wpdb->query( 'ROLLBACK' );
405	throw new \Exception( __( 'Failed to insert order items', 'tutor' ) );
406	}
407	} else {
408	$wpdb->query( 'COMMIT' );
409	return $order_id;
410	}
411	}
412	} catch ( \Throwable $th ) {
413	throw new \Exception( $th->getMessage() );
414	}
415	}
416
417	/**
418	* Insert order items
419	*
420	* Note: validate data before using this method
421	*
422	* @since 3.0.0
423	*
424	* @param int $order_id Order ID.
425	* @param array $items Order items.
426	*
427	* @throws Exception Database error if occur.
428	*
429	* @return bool
430	*/
431	public function insert_order_items( int $order_id, array $items ): bool {
432	// Check if item is multi dimensional.
433	if ( ! isset( $items[0] ) ) {
434	$items = array( $items );
435	}
436
437	// Set order id on each item.
438	foreach ( $items as $item ) {
439	$item['order_id'] = $order_id;
440	$meta_data = $item['meta_data'] ?? null;
441	try {
442	unset( $item['meta_data'] );
443	$insert = QueryHelper::insert(
444	$this->order_item_table,
445	$item,
446	);
447	if ( $insert ) {
448	if ( ! empty( $meta_data ) ) {
449	foreach ( $meta_data as $meta ) {
450	( new OrderItemMetaModel() )->add_meta( $insert, $meta['meta_key'], maybe_serialize( $meta['meta_value'] ) );
451	}
452	}
453	}
454	} catch ( \Throwable $th ) {
455	return false;
456	}
457	}
458
459	return true;
460	}
461
462	/**
463	* Retrieve order details by order ID.
464	*
465	* This function fetches order information from the database based on the given
466	* order ID. It queries the 'tutor_orders' table for the order data, retrieves
467	* the corresponding user information and metadata, and constructs a detailed
468	* student object with placeholder values for billing address and phone.
469	*
470	* The function then assigns this student object to the order data, removes
471	* the user ID from the order data, and returns the modified order data.
472	*
473	* @since 3.0.0
474	*
475	* @global wpdb $wpdb WordPress database abstraction object.
476	*
477	* @param int $order_id The ID of the order to retrieve.
478	*
479	* @return object\|false The order data with the student's information included, or false if no order is found.
480	*/
481	public function get_order_by_id( $order_id ) {
482	$order_data = QueryHelper::get_row(
483	$this->table_name,
484	array( 'id' => $order_id ),
485	'id'
486	);
487
488	if ( ! $order_data ) {
489	return false;
490	}
491
492	$user_info = get_userdata( $order_data->user_id );
493
494	$student = new \stdClass();
495	$student->id = (int) $user_info->ID ?? 0;
496	$student->name = $user_info->data->display_name ?? '';
497	$student->email = $user_info->data->user_email ?? '';
498	$student->phone = get_user_meta( $order_data->user_id, 'phone_number', true );
499	$student->billing_address = $this->get_order_billing_address( $order_id, $order_data->user_id );
500	$student->image = get_avatar_url( $order_data->user_id );
501
502	$order_data->student = $student;
503	$order_data->items = $this->get_order_items_by_id( $order_id );
504
505	$order_data->subtotal_price = (float) $order_data->subtotal_price;
506	$order_data->total_price = (float) $order_data->total_price;
507	$order_data->net_payment = (float) $order_data->net_payment;
508	$order_data->discount_amount = (float) $order_data->discount_amount;
509	$order_data->coupon_amount = (float) $order_data->coupon_amount;
510	$order_data->tax_rate = (float) $order_data->tax_rate;
511	$order_data->tax_amount = (float) $order_data->tax_amount;
512
513	$order_data->payment_method_readable = Ecommerce::get_payment_method_label( $order_data->payment_method );
514	$order_data->created_at_readable = DateTimeHelper::get_gmt_to_user_timezone_date( $order_data->created_at_gmt );
515	$order_data->updated_at_readable = empty( $order_data->updated_at_gmt ) ? '' : DateTimeHelper::get_gmt_to_user_timezone_date( $order_data->updated_at_gmt );
516
517	$order_data->created_by = get_userdata( $order_data->created_by )->display_name ?? '';
518	$order_data->updated_by = get_userdata( $order_data->updated_by )->display_name ?? '';
519
520	$order_activities_model = new OrderActivitiesModel();
521	$order_data->activities = $order_activities_model->get_order_activities( $order_id );
522	$order_data->refunds = $this->get_order_refunds( $order_id );
523
524	unset( $student->billing_address->id );
525	unset( $student->billing_address->user_id );
526
527	return apply_filters( 'tutor_order_details', $order_data );
528	}
529
530	/**
531	* Get order data
532	*
533	* @since 3.1.0
534	*
535	* @param int\|object $order order id or object.
536	*
537	* @return object
538	*/
539	public static function get_order( $order ) {
540	if ( is_numeric( $order ) ) {
541	$order = ( new self() )->get_order_by_id( $order );
542	}
543
544	return $order;
545	}
546
547	/**
548	* Check order is subscription order
549	*
550	* @since 3.1.0
551	*
552	* @param int\|object $order order id or object.
553	*
554	* @return boolean
555	*/
556	public static function is_subscription_order( $order ) {
557	$order = self::get_order( $order );
558	return $order && self::TYPE_SUBSCRIPTION === $order->order_type;
559	}
560
561	/**
562	* Check order is single order
563	*
564	* @since 3.2.0
565	*
566	* @param int\|object $order order id or object.
567	*
568	* @return boolean
569	*/
570	public static function is_single_order( $order ) {
571	$order = self::get_order( $order );
572	return $order && self::TYPE_SINGLE_ORDER === $order->order_type;
573	}
574
575	/**
576	* Mark order Unpaid to Paid.
577	*
578	* @since 3.0.0
579	*
580	* @param int $order_id order id.
581	* @param string $note note.
582	* @param bool $trigger_hooks trigger hooks or not.
583	*
584	* @return bool
585	*/
586	public function mark_as_paid( $order_id, $note = '', $trigger_hooks = true ) {
587	if ( $trigger_hooks ) {
588	do_action( 'tutor_before_order_mark_as_paid', $order_id );
589	}
590
591	$data = array(
592	'payment_status' => self::PAYMENT_PAID,
593	'order_status' => self::ORDER_COMPLETED,
594	'note' => $note,
595	);
596
597	$response = $this->update_order( $order_id, $data );
598	if ( ! $response ) {
599	return false;
600	}
601
602	if ( $trigger_hooks ) {
603	do_action( 'tutor_order_payment_status_changed', $order_id, self::PAYMENT_UNPAID, self::PAYMENT_PAID );
604
605	$order = $this->get_order_by_id( $order_id );
606	$discount_amount = $this->calculate_discount_amount( $order->discount_type, $order->discount_amount, $order->subtotal_price );
607	do_action( 'tutor_after_order_mark_as_paid', $order, $discount_amount );
608	}
609
610	return true;
611	}
612
613
614	/**
615	* Retrieve order items by order ID.
616	*
617	* This function fetches order item details from the database based on the given
618	* order ID. It queries the 'tutor_order_items' table and joins it with the 'posts'
619	* table to get the course titles associated with each order item.
620	*
621	* The function then returns the retrieved order items, or an empty array if no
622	* items are found.
623	*
624	* @since 3.0.0
625	*
626	* @global wpdb $wpdb WordPress database abstraction object.
627	*
628	* @param int $order_id The ID of the order to retrieve items for.
629	*
630	* @return array The order items, each containing details and course titles, or an empty array if no items are found.
631	*/
632	public function get_order_items_by_id( $order_id ) {
633	global $wpdb;
634
635	$primary_table = "{$wpdb->prefix}tutor_order_items AS oi";
636	$joining_tables = array(
637	array(
638	'type' => 'LEFT',
639	'table' => "{$wpdb->prefix}posts AS p",
640	'on' => 'p.ID = oi.item_id',
641	),
642	);
643
644	$where = array( 'order_id' => $order_id );
645
646	$select_columns = array( 'oi.id AS primary_id', 'oi.item_id AS id', 'oi.regular_price', 'oi.sale_price', 'oi.discount_price', 'oi.coupon_code', 'p.post_title AS title', 'p.post_type AS type' );
647
648	$courses_data = QueryHelper::get_joined_data( $primary_table, $joining_tables, $select_columns, $where, array(), 'id', 0, 0 );
649	$courses = $courses_data['results'];
650
651	if ( tutor()->has_pro ) {
652	$bundle_model = new \TutorPro\CourseBundle\Models\BundleModel();
653	}
654
655	if ( ! empty( $courses_data['total_count'] ) ) {
656	foreach ( $courses as &$course ) {
657	if ( tutor()->has_pro && 'course-bundle' === $course->type ) {
658	$course->total_courses = count( $bundle_model->get_bundle_course_ids( $course->id ) );
659	}
660
661	$course->id = (int) $course->id;
662	$course->regular_price = (float) $course->regular_price;
663	$course->image = get_the_post_thumbnail_url( $course->id );
664
665	// Add meta items.
666	$order_item_meta = new OrderItemMetaModel();
667	$course->item_meta_list = apply_filters( 'tutor_order_item_meta', $order_item_meta->get_meta( $course->primary_id, null, false ) );
668	}
669	}
670
671	unset( $course );
672
673	return ! empty( $courses ) ? $courses : array();
674	}
675
676	/**
677	* Get order billing address with fallback customer billing address record support.
678	* It'll return order billing address if found, otherwise it'll return customer billing address record.
679	*
680	* @since 3.5.0
681	*
682	* @param int $order_id order id.
683	* @param int $user_id order id.
684	*
685	* @return object
686	*/
687	public static function get_order_billing_address( $order_id, $user_id ) {
688	$billing_address = OrderMetaModel::get_meta_value( $order_id, self::META_KEY_BILLING_ADDRESS, true );
689
690	/**
691	* Fallback data from customer billing record.
692	*/
693	if ( false === $billing_address ) {
694	$billing_address = ( new BillingController( false ) )->get_billing_info( $user_id );
695	} else {
696	$billing_address = json_decode( $billing_address );
697	}
698
699	$data = (object) array(
700	'first_name' => $billing_address->billing_first_name ?? '',
701	'last_name' => $billing_address->billing_last_name ?? '',
702	'full_name' => trim( ( $billing_address->billing_first_name ?? '' ) . ' ' . ( $billing_address->billing_last_name ?? '' ) ),
703	'email' => $billing_address->billing_email ?? '',
704	'phone' => $billing_address->billing_phone ?? '',
705	'address' => $billing_address->billing_address ?? '',
706	'country' => $billing_address->billing_country ?? '',
707	'state' => $billing_address->billing_state ?? '',
708	'city' => $billing_address->billing_city ?? '',
709	'zip_code' => $billing_address->billing_zip_code ?? '',
710	);
711
712	return $data;
713	}
714
715	/**
716	* Retrieve order refunds by order ID.
717	*
718	* This function fetches all order refunds from the 'tutor_ordermeta' table
719	* based on the given order ID and the 'refund' meta key. It uses a helper
720	* function from the QueryHelper class to perform the database query.
721	*
722	* If no order refunds are found, the function returns an empty array.
723	* Otherwise, it decodes the JSON-encoded meta values and returns them as an array.
724	*
725	* @global wpdb $wpdb WordPress database abstraction object.
726	*
727	* @param int $order_id The ID of the order to retrieve refunds for.
728	*
729	* @since 3.0.0
730	*
731	* @return array An array of order refunds, each decoded from its JSON representation.
732	*/
733	public function get_order_refunds( $order_id ) {
734	global $wpdb;
735
736	$meta_keys = array(
737	OrderActivitiesModel::META_KEY_REFUND,
738	OrderActivitiesModel::META_KEY_PARTIALLY_REFUND,
739	);
740
741	// Retrieve order refunds for the given order ID from the 'tutor_ordermeta' table.
742	$order_refunds = QueryHelper::get_all(
743	"{$wpdb->prefix}tutor_ordermeta",
744	array(
745	'order_id' => $order_id,
746	'meta_key' => $meta_keys,
747	),
748	'created_at_gmt',
749	1000,
750	'ASC'
751	);
752
753	if ( empty( $order_refunds ) ) {
754	return array();
755	}
756
757	$response = array();
758
759	foreach ( $order_refunds as $refund ) {
760	$parsed_meta_value = json_decode( $refund->meta_value );
761	$values = new \stdClass();
762	$values->id = (int) $refund->id;
763
764	foreach ( $parsed_meta_value as $key => $value ) {
765	$values->$key = $value;
766	}
767
768	$values->date = $refund->created_at_gmt;
769
770	$response[] = $values;
771	}
772
773	// Custom comparison function for sorting by date.
774	usort(
775	$response,
776	function ( $a, $b ) {
777	$date_a = strtotime( $a->date );
778	$date_b = strtotime( $b->date );
779
780	return $date_b - $date_a;
781	}
782	);
783
784	return $response;
785	}
786
787	/**
788	* Update an order
789	*
790	* @since 3.0.0
791	*
792	* @param int\|array $order_id Integer or array of ids sql escaped.
793	* @param array $data Data to update, escape data.
794	* @param array $order_items order items (optional).
795	*
796	* @return bool
797	*/
798	public function update_order( $order_id, array $data, array $order_items = array() ) {
799	$order_id = is_array( $order_id ) ? $order_id : array( $order_id );
800	$order_id = QueryHelper::prepare_in_clause( $order_id );
801
802	try {
803	QueryHelper::update_where_in(
804	$this->table_name,
805	$data,
806	$order_id
807	);
808
809	if ( ! empty( $order_items ) ) {
810	$this->update_order_items( $order_id, $order_items );
811	}
812	return true;
813	} catch ( \Throwable $th ) {
814	error_log( $th->getMessage() . ' in ' . $th->getFile() . ' at line ' . $th->getLine() );
815	return false;
816	}
817	}
818
819	/**
820	* Get enrollment ids by order id.
821	*
822	* @since 3.0.0
823	*
824	* @param int $order_id order id.
825	*
826	* @return array
827	*/
828	public function get_enrollment_ids( $order_id ) {
829	global $wpdb;
830	$enrollment_ids = array();
831
832	$enrollments = $wpdb->get_results(
833	$wpdb->prepare(
834	"SELECT * FROM {$wpdb->postmeta}
835	WHERE meta_key=%s
836	AND meta_value LIKE %d",
837	'_tutor_enrolled_by_order_id',
838	$order_id
839	)
840	);
841
842	if ( $enrollments ) {
843	$enrollment_ids = array_column( $enrollments, 'post_id' );
844	}
845
846	return $enrollment_ids;
847	}
848
849	/**
850	* Delete an order by order ID.
851	*
852	* This function deletes an order from the 'tutor_orders' table based on the given
853	* order ID. It uses the QueryHelper class to perform the database delete operation.
854	*
855	* @since 3.0.0
856	*
857	* @param int\|array $order_id The ID of the order to delete.
858	*
859	* @return bool
860	*/
861	public function delete_order( $order_id ) {
862	global $wpdb;
863	$order_ids = is_array( $order_id ) ? $order_id : array( intval( $order_id ) );
864
865	try {
866	$wpdb->query( 'START TRANSACTION' );
867
868	foreach ( $order_ids as $order_id ) {
869	// Delete enrollments if exist.
870	$enrollment_ids = $this->get_enrollment_ids( $order_id );
871	if ( $enrollment_ids ) {
872	QueryHelper::bulk_delete_by_ids( $wpdb->posts, $enrollment_ids );
873	// After enrollment delete, delete the course progress.
874	foreach ( $enrollment_ids as $enrollment_id ) {
875	$course_id = get_post_field( 'post_parent', $enrollment_id );
876	$student_id = get_post_field( 'post_author', $enrollment_id );
877
878	if ( $course_id && $student_id ) {
879	tutor_utils()->delete_course_progress( $course_id, $student_id );
880	}
881	}
882	}
883
884	// Delete earnings.
885	QueryHelper::delete(
886	$wpdb->prefix . 'tutor_earnings',
887	array(
888	'order_id' => $order_id,
889	'process_by' => Earnings::PROCESS_BY_TUTOR,
890	)
891	);
892
893	// Now delete order.
894	QueryHelper::delete( $this->table_name, array( 'id' => $order_id ) );
895	}
896
897	$wpdb->query( 'COMMIT' );
898	return true;
899
900	} catch ( \Throwable $th ) {
901	$wpdb->query( 'ROLLBACK' );
902	return false;
903	}
904	}
905
906	/**
907	* Get orders list
908	*
909	* @since 3.0.0
910	*
911	* @param array $where where clause conditions.
912	* @param string $search_term search clause conditions.
913	* @param int $limit limit default 10.
914	* @param int $offset default 0.
915	* @param string $order_by column default 'o.id'.
916	* @param string $order list order default 'desc'.
917	*
918	* @return array
919	*/
920	public function get_orders( array $where = array(), $search_term = '', int $limit = 10, int $offset = 0, string $order_by = 'o.id', string $order = 'desc' ) {
921
922	global $wpdb;
923
924	$primary_table = "{$this->table_name} o";
925	$joining_tables = array(
926	array(
927	'type' => 'LEFT',
928	'table' => "{$wpdb->users} u",
929	'on' => 'o.user_id = u.ID',
930	),
931	);
932
933	$select_columns = array( 'o.*', 'u.user_login' );
934
935	$search_clause = array();
936	if ( '' !== $search_term ) {
937	foreach ( $this->get_searchable_fields() as $column ) {
938	$search_clause[ $column ] = $search_term;
939	}
940	}
941
942	$response = array(
943	'results' => array(),
944	'total_count' => 0,
945	);
946
947	try {
948	return QueryHelper::get_joined_data( $primary_table, $joining_tables, $select_columns, $where, $search_clause, $order_by, $limit, $offset, $order );
949	} catch ( \Throwable $th ) {
950	// Log with error, line & file name.
951	error_log( $th->getMessage() . ' in ' . $th->getFile() . ' at line ' . $th->getLine() );
952	return $response;
953	}
954	}
955
956	/**
957	* Get order count
958	*
959	* @since 3.0.0
960	*
961	* @param array $where Where conditions, sql esc data.
962	* @param string $search_term Search terms, sql esc data.
963	*
964	* @return int
965	*/
966	public function get_order_count( $where = array(), string $search_term = '' ) {
967	global $wpdb;
968
969	$search_clause = array();
970	if ( '' !== $search_term ) {
971	foreach ( $this->get_searchable_fields() as $column ) {
972	$search_clause[ $column ] = $search_term;
973	}
974	}
975
976	$join_table = array(
977	array(
978	'type' => 'INNER',
979	'table' => "{$wpdb->users} u",
980	'on' => 'o.user_id = u.ID',
981	),
982	);
983	$primary_table = "{$this->table_name} o";
984	return QueryHelper::get_joined_count( $primary_table, $join_table, $where, $search_clause );
985	}
986
987	/**
988	* Get order of a user
989	*
990	* @since 3.0.0
991	*
992	* @param string $time_period $time_period Sorting time period,
993	* supported time periods are: today, monthly & yearly.
994	* @param string $start_date $start_date For date range sorting.
995	* @param string $end_date $end_date For date range sorting.
996	* @param int $user_id User id for fetching order list.
997	* @param int $limit Limit to fetch record.
998	* @param int $offset Offset to fetch record.
999	*
1000	* @throws \Exception Throw exception if database error occur.
1001	*
1002	* @return array
1003	*/
1004	public function get_user_orders( $time_period = null, $start_date = null, $end_date = null, int $user_id = 0, $limit = 10, int $offset = 0 ) {
1005	$user_id = $user_id ? $user_id : get_current_user_id();
1006
1007	$response = array(
1008	'results' => array(),
1009	'total_count' => 0,
1010	);
1011
1012	global $wpdb;
1013
1014	$time_period_clause = '';
1015	$date_range_clause = '';
1016
1017	if ( $start_date && $end_date ) {
1018	$date_range_clause = $wpdb->prepare( 'AND DATE(created_at_gmt) BETWEEN %s AND %s', $start_date, $end_date );
1019	} elseif ( $time_period ) {
1020	if ( 'today' === $time_period ) {
1021	$time_period_clause = 'AND DATE(o.created_at_gmt) = CURDATE()';
1022	} elseif ( 'monthly' === $time_period ) {
1023	$time_period_clause = 'AND MONTH(o.created_at_gmt) = MONTH(CURDATE()) ';
1024	} else {
1025	$time_period_clause = 'AND YEAR(o.created_at_gmt) = YEAR(CURDATE()) ';
1026	}
1027	}
1028
1029	//phpcs:disable
1030	$query = $wpdb->prepare(
1031	"SELECT
1032	SQL_CALC_FOUND_ROWS
1033	o.*
1034	FROM $this->table_name AS o
1035	WHERE o.user_id = %d
1036	{$time_period_clause}
1037	{$date_range_clause}
1038	ORDER BY o.id DESC
1039	LIMIT %d OFFSET %d
1040	",
1041	$user_id,
1042	$limit,
1043	$offset
1044	);
1045
1046	$results = $wpdb->get_results( $query );
1047	//phpcs:enable
1048
1049	if ( $wpdb->last_error ) {
1050	throw new \Exception( $wpdb->last_error );
1051	} else {
1052	$response['results'] = $results;
1053	$response['total_count'] = is_array( $results ) && count( $results ) ? (int) $wpdb->get_var( 'SELECT FOUND_ROWS()' ) : 0;
1054	}
1055
1056	return $response;
1057	}
1058
1059	/**
1060	* Get total discounts by user_id (instructor), optionally can set period ( today \| monthly\| yearly )
1061	*
1062	* Optionally can set start date & end date to get enrollment list from date range
1063	*
1064	* If period or date range not pass then it will return all time enrollment list
1065	*
1066	* @since 3.0.0
1067	*
1068	* @param int $user_id User id, if user not have admin access
1069	* then only this user's refund amount will fetched.
1070	* @param string $period Time period.
1071	* @param string $start_date Start date.
1072	* @param string $end_date End date.
1073	* @param int $course_id Course id.
1074	*
1075	* @return array
1076	*/
1077	public function get_discounts_by_user( int $user_id, string $period = '', $start_date = '', string $end_date = '', int $course_id = 0 ): array {
1078	$response = array(
1079	'discounts' => array(),
1080	'total_discounts' => 0,
1081	);
1082
1083	global $wpdb;
1084
1085	$user_clause = '';
1086	$date_range_clause = '';
1087	$period_clause = '';
1088	$course_clause = '';
1089	$group_clause = ' GROUP BY DATE(date_format) ';
1090	$discount_clause = 'o.coupon_amount as total';
1091
1092	if ( $start_date && $end_date ) {
1093	$date_range_clause = $wpdb->prepare(
1094	'AND o.created_at_gmt BETWEEN %s AND %s',
1095	$start_date,
1096	$end_date
1097	);
1098	} else {
1099	$period_clause = QueryHelper::get_period_clause( 'o.created_at_gmt', $period );
1100	}
1101
1102	if ( 'today' !== $period ) {
1103	$group_clause = ' GROUP BY MONTH(date_format) ';
1104	}
1105
1106	if ( $course_id ) {
1107	$course_clause = $wpdb->prepare( 'AND i.item_id = %d', $course_id );
1108	$discount_clause = 'i.regular_price - i.discount_price AS total';
1109	}
1110
1111	$item_table = $wpdb->prefix . 'tutor_order_items';
1112
1113	if ( $course_id ) {
1114	if ( $user_id ) {
1115	$user_clause = $wpdb->prepare( 'AND c.post_author = %d', $user_id );
1116	}
1117
1118	//phpcs:disable
1119	$discounts = $wpdb->get_results(
1120	$wpdb->prepare(
1121	"SELECT
1122	i.item_id AS course_id,
1123	SUM(
1124	COALESCE(o.coupon_amount, 0) +
1125	COALESCE(
1126	IF(
1127	o.discount_type = 'percentage',
1128	COALESCE(o.subtotal_price * (o.discount_amount / 100), 0),
1129	COALESCE(o.discount_amount, 0)
1130	),
1131	0
1132	)
1133	) AS total,
1134	o.created_at_gmt AS date_format
1135	FROM
1136	{$this->table_name} o
1137	JOIN
1138	{$item_table} i ON o.id = i.order_id
1139	JOIN
1140	{$wpdb->posts} c
1141	ON c.ID = i.item_id
1142	AND c.post_type = %s
1143	WHERE
1144	1 = 1
1145	AND i.item_id = %d
1146	{$user_clause}
1147	{$period_clause}
1148	{$date_range_clause}
1149	{$group_clause}
1150	",
1151	tutor()->course_post_type,
1152	$course_id
1153	)
1154	);
1155	//phpcs:enable
1156	} else {
1157	if ( $user_id ) {
1158	$user_clause = $wpdb->prepare( "AND %d = (SELECT user_id FROM {$wpdb->tutor_earnings} WHERE order_status = 'completed' LIMIT 1) ", $user_id );
1159	}
1160
1161	//phpcs:disable
1162	$discounts = $wpdb->get_results(
1163	$wpdb->prepare(
1164	"SELECT
1165	SUM(
1166	COALESCE(o.coupon_amount, 0) +
1167	COALESCE(
1168	IF(
1169	o.discount_type = 'percentage',
1170	COALESCE(o.subtotal_price * (o.discount_amount / 100), 0),
1171	COALESCE(o.discount_amount, 0)
1172	),
1173	0
1174	)
1175	) AS total,
1176	o.created_at_gmt AS date_format
1177	FROM {$this->table_name} AS o
1178	WHERE 1 = %d
1179	AND o.order_status = 'completed'
1180	{$user_clause}
1181	{$period_clause}
1182	{$date_range_clause}
1183	{$course_clause}
1184	{$group_clause}
1185	HAVING total > 0
1186	",
1187	1
1188	)
1189	);
1190	//phpcs:enable
1191	}
1192
1193	$total_discount = 0;
1194	$discount_items = array();
1195
1196	$response = array(
1197	'discounts' => array(),
1198	'total_discounts' => 0,
1199	);
1200
1201	if ( $discounts ) {
1202	foreach ( $discounts as $discount ) {
1203	$total_discount += $discount->total;
1204	$discount_items[] = $discount;
1205
1206	// Split each discount.
1207	list( $admin_discount, $instructor_discount ) = array_values( tutor_split_amounts( $discount->total ) );
1208
1209	$discount->total = is_admin() ? $admin_discount : $instructor_discount;
1210	}
1211
1212	list( $admin_total, $instructor_total ) = array_values( tutor_split_amounts( $total_discount ) );
1213
1214	$response['discounts'] = $discount_items;
1215	$response['total_discounts'] = is_admin() ? $admin_total : $instructor_total;
1216	}
1217
1218	return $response;
1219	}
1220
1221	/**
1222	* Get total refunds by user_id (instructor), optionally can set period ( today \| monthly\| yearly )
1223	*
1224	* Optionally can set start date & end date to get enrollment list from date range
1225	*
1226	* If period or date range not pass then it will return all time enrollment list
1227	*
1228	* @since 3.0.0
1229	*
1230	* @param int $user_id User id, if user not have admin access
1231	* then only this user's refund amount will fetched.
1232	* @param string $period Time period.
1233	* @param string $start_date Start date.
1234	* @param string $end_date End date.
1235	* @param int $course_id Course id.
1236	*
1237	* @return array
1238	*/
1239	public function get_refunds_by_user( int $user_id, string $period = '', $start_date = '', string $end_date = '', int $course_id = 0 ): array {
1240	$response = array(
1241	'refunds' => array(),
1242	'total_refunds' => 0,
1243	);
1244
1245	global $wpdb;
1246
1247	$user_clause = '';
1248	$date_range_clause = '';
1249	$period_clause = '';
1250	$course_clause = '';
1251	$commission_clause = '';
1252	$group_clause = ' GROUP BY DATE(o.created_at_gmt) ';
1253
1254	if ( $start_date && $end_date ) {
1255	$date_range_clause = $wpdb->prepare(
1256	'AND o.created_at_gmt BETWEEN %s AND %s',
1257	$start_date,
1258	$end_date
1259	);
1260	$group_clause = ' GROUP BY DATE(o.created_at_gmt) ';
1261
1262	} else {
1263	$period_clause = QueryHelper::get_period_clause( 'o.created_at_gmt', $period );
1264	}
1265
1266	if ( 'today' !== $period ) {
1267	$group_clause = ' GROUP BY MONTH(o.created_at_gmt) ';
1268	}
1269
1270	if ( $course_id ) {
1271	if ( $user_id ) {
1272	$user_clause = $wpdb->prepare( 'AND c.post_author = %d', $user_id );
1273	}
1274	} elseif ( $user_id ) {
1275	$user_clause = $wpdb->prepare( 'AND c.post_author = %d', $user_id );
1276	}
1277
1278	// Refund query logic remains the same.
1279	$item_table = $wpdb->prefix . 'tutor_order_items';
1280
1281	if ( $course_id ) {
1282	//phpcs:disable
1283	$refunds = $wpdb->get_results(
1284	$wpdb->prepare(
1285	"SELECT
1286	i.item_id AS course_id,
1287	ROUND(
1288	SUM(
1289	o.refund_amount *
1290	(
1291	CASE
1292	WHEN i.discount_price THEN i.discount_price
1293	WHEN i.sale_price > 0 THEN i.sale_price
1294	ELSE i.regular_price
1295	END / o.total_price
1296	)
1297	), 2
1298	) AS total
1299	FROM
1300	{$this->table_name} o
1301	JOIN
1302	{$item_table} i ON o.id = i.order_id
1303	JOIN
1304	{$wpdb->posts} c
1305	ON c.ID = i.item_id
1306	AND c.post_type = %s
1307	WHERE
1308	o.refund_amount > 0
1309	AND i.item_id = %d
1310	{$user_clause}
1311	{$period_clause}
1312	{$date_range_clause}
1313	{$group_clause},
1314	i.item_id
1315	",
1316	tutor()->course_post_type,
1317	$course_id
1318	)
1319	);
1320	//phpcs:enable
1321	} else {
1322	$earning_table = $wpdb->tutor_earnings;
1323	if ( $user_id ) {
1324	$user_clause = "AND {$user_id} = (SELECT user_id FROM {$earning_table} LIMIT 1)";
1325	}
1326
1327	//phpcs:disable
1328	$refunds = $wpdb->get_results(
1329	$wpdb->prepare(
1330	"SELECT
1331	COALESCE(SUM(o.refund_amount), 0) AS total,
1332	created_at_gmt AS date_format
1333	FROM {$this->table_name} AS o
1334	-- LEFT JOIN {$item_table} AS i ON i.order_id = o.id
1335	-- LEFT JOIN {$wpdb->posts} AS c ON c.id = i.item_id
1336	WHERE 1 = %d
1337	AND o.refund_amount > %d
1338	{$user_clause}
1339	{$period_clause}
1340	{$date_range_clause}
1341	{$group_clause},
1342	o.id",
1343	1,
1344	0
1345	)
1346	);
1347	//phpcs:enable
1348	}
1349
1350	$total_refund = 0;
1351
1352	foreach ( $refunds as $refund ) {
1353	$total_refund += $refund->total;
1354
1355	// Update total amount from list.
1356	$split_refund = (object) tutor_split_amounts( $refund->total );
1357	$refund->total = is_admin() ? $split_refund->admin : $split_refund->instructor;
1358	}
1359
1360	$split_total_refund = (object) tutor_split_amounts( $total_refund );
1361
1362	$response = array(
1363	'refunds' => $refunds,
1364	'total_refunds' => is_admin() ? $split_total_refund->admin : $split_total_refund->instructor,
1365	);
1366
1367	return $response;
1368	}
1369
1370	/**
1371	* Update the payment status of an order.
1372	*
1373	* This function updates the payment status and note of an order in the database.
1374	* It uses the QueryHelper class to perform the update operation.
1375	*
1376	* @since 3.0.0
1377	*
1378	* @param object $data An object containing the payment status, note, and order ID.
1379	* - 'payment_status' (string): The new payment status.
1380	* - 'note' (string): A note regarding the payment status update.
1381	* - 'order_id' (int): The ID of the order to update.
1382	*
1383	* @return bool True on successful update, false on failure.
1384	*/
1385	public function payment_status_update( object $data ) {
1386	$response = QueryHelper::update(
1387	$this->table_name,
1388	array(
1389	'payment_status' => $data->payment_status,
1390	'note' => $data->note,
1391	),
1392	array( 'id' => $data->order_id )
1393	);
1394
1395	if ( $response ) {
1396	$activity_controller = new OrderActivitiesController();
1397	$activity_controller->store_order_activity_for_marked_as_paid( $data->order_id );
1398	}
1399
1400	return $response;
1401	}
1402
1403	/**
1404	* Add a discount to an order.
1405	*
1406	* This function updates the order in the database with the provided discount details.
1407	* It updates the discount type, discount amount, and discount reason for the given order ID.
1408	*
1409	* @since 3.0.0
1410	*
1411	* @param object $data An object containing the discount details:
1412	* - $data->order_id (int) The ID of the order.
1413	* - $data->discount_type (string) The type of the discount.
1414	* - $data->discount_amount(float) The amount of the discount.
1415	* - $data->discount_reason(string) The reason for the discount.
1416	*
1417	* @return bool True on successful update, false on failure.
1418	*/
1419	public function add_order_discount( object $data ) {
1420	$response = QueryHelper::update(
1421	$this->table_name,
1422	array(
1423	'discount_type' => $data->discount_type,
1424	'discount_amount' => $data->discount_amount,
1425	'discount_reason' => $data->discount_reason,
1426	),
1427	array( 'id' => $data->order_id )
1428	);
1429
1430	return $response;
1431	}
1432
1433	/**
1434	* Updates the status of an order and logs the activity.
1435	*
1436	* This function updates the status of an order in the database and, if successful, logs the activity
1437	* with a message indicating the status change. The message includes the current user's display name,
1438	* if available.
1439	*
1440	* The possible order statuses include:
1441	* - ORDER_CANCELLED
1442	* - ORDER_COMPLETED
1443	* - ORDER_INCOMPLETE
1444	* - ORDER_TRASH
1445	*
1446	* If the update is successful, an order activity log entry is created with the current date, time,
1447	* and status change message.
1448	*
1449	* @since 3.0.0
1450	*
1451	* @param object $data An object containing:
1452	* - int $order_id The ID of the order to update.
1453	* - string $order_status The new status of the order.
1454	* - string $cancel_reason The reason for the order cancellation (optional).
1455	*
1456	* @return bool True on successful update, false on failure.
1457	*/
1458	public function order_status_update( object $data ) {
1459	$response = QueryHelper::update(
1460	$this->table_name,
1461	array(
1462	'order_status' => $data->order_status,
1463	),
1464	array( 'id' => $data->order_id )
1465	);
1466
1467	if ( $response ) {
1468	$user_name = '';
1469	$current_user = wp_get_current_user();
1470
1471	if ( $current_user->exists() ) {
1472	$user_name = $current_user->display_name;
1473	}
1474
1475	$message = '';
1476
1477	if ( self::ORDER_CANCELLED === $data->order_status ) {
1478	/* translators: %s: username */
1479	$message = empty( $user_name ) ? __( 'Order marked as cancelled', 'tutor' ) : sprintf( __( 'Order marked as cancelled by %s', 'tutor' ), $user_name );
1480	} elseif ( self::ORDER_COMPLETED === $data->order_status ) {
1481	/* translators: %s: username */
1482	$message = empty( $user_name ) ? __( 'Order marked as completed', 'tutor' ) : sprintf( __( 'Order marked as completed by %s', 'tutor' ), $user_name );
1483	} elseif ( self::ORDER_INCOMPLETE === $data->order_status ) {
1484	/* translators: %s: username */
1485	$message = empty( $user_name ) ? __( 'Order marked as incomplete', 'tutor' ) : sprintf( __( 'Order marked as incomplete by %s', 'tutor' ), $user_name );
1486	} elseif ( self::ORDER_TRASH === $data->order_status ) {
1487	/* translators: %s: username */
1488	$message = empty( $user_name ) ? __( 'Order marked as trash', 'tutor' ) : sprintf( __( 'Order marked as trash by %s', 'tutor' ), $user_name );
1489	}
1490
1491	// insert cancel reason in tutor_ordermeta table.
1492	if ( self::ORDER_CANCELLED === $data->order_status && ! empty( $data->cancel_reason ) ) {
1493	$meta_payload = new \stdClass();
1494	$meta_payload->order_id = $data->order_id;
1495	$meta_payload->meta_key = OrderActivitiesModel::META_KEY_CANCEL_REASON;
1496	$meta_payload->meta_value = $data->cancel_reason;
1497
1498	$order_activities_model = new OrderActivitiesModel();
1499	$order_activities_model->add_order_meta( $meta_payload );
1500	}
1501
1502	if ( $message ) {
1503	$value = wp_json_encode(
1504	array(
1505	'message' => $message,
1506	)
1507	);
1508	OrderActivitiesController::store_order_activity( $data->order_id, OrderActivitiesModel::META_KEY_HISTORY, $value );
1509	}
1510	}
1511
1512	return $response;
1513	}
1514
1515	/**
1516	* Calculate discount amount.
1517	*
1518	* @since 3.0.0
1519	*
1520	* @param string $discount_type The type of discount ('percent' or 'flat').
1521	* @param float $discount_amount The amount of discount to apply.
1522	* @param float $sub_total The subtotal amount before applying the discount.
1523	*
1524	* @return float discount amount.
1525	*/
1526	public function calculate_discount_amount( $discount_type, $discount_amount, $sub_total ) {
1527	if ( 'percentage' === $discount_type ) {
1528	$discounted_price = (float) $sub_total * ( ( (float) $discount_amount / 100 ) );
1529	} else {
1530	$discounted_price = (float) $discount_amount;
1531	}
1532	return $discounted_price;
1533	}
1534
1535	/**
1536	* Retrieves the total refund amount for a given order.
1537	*
1538	* This method fetches all refund records for the specified order ID from the database,
1539	* calculates the total refund amount, and returns it. The refund records are retrieved
1540	* from the `tutor_ordermeta` table where the `meta_key` matches the refund meta keys.
1541	*
1542	* @since 3.0.0
1543	*
1544	* @param int $order_id The ID of the order for which the refund amount is to be calculated.
1545	*
1546	* @return float The total refund amount for the order.
1547	*/
1548	public function get_refund_amount( $order_id ) {
1549	global $wpdb;
1550
1551	$table = $wpdb->prefix . 'tutor_ordermeta';
1552	$meta_keys = array( OrderActivitiesModel::META_KEY_REFUND, OrderActivitiesModel::META_KEY_PARTIALLY_REFUND );
1553
1554	$where = array(
1555	'meta_key' => $meta_keys,
1556	'order_id' => $order_id,
1557	);
1558	$refund_records = QueryHelper::get_all( $table, $where, 'created_at_gmt' );
1559
1560	$refund_amount = 0;
1561
1562	foreach ( $refund_records as $refund ) {
1563	$refund_data = json_decode( $refund->meta_value );
1564
1565	if ( ! empty( $refund_data->amount ) ) {
1566	$refund_amount += (float) $refund_data->amount;
1567	}
1568	}
1569
1570	return $refund_amount;
1571	}
1572
1573	/**
1574	* Get order status based on the payment status
1575	*
1576	* @since 3.0.0
1577	*
1578	* @param string $payment_status Order payment status.
1579	*
1580	* @return string
1581	*/
1582	public function get_order_status_by_payment_status( $payment_status ) {
1583	$status = '';
1584
1585	switch ( $payment_status ) {
1586	case self::PAYMENT_PAID:
1587	$status = self::ORDER_COMPLETED;
1588	break;
1589	case self::PAYMENT_UNPAID:
1590	$status = self::ORDER_INCOMPLETE;
1591	break;
1592	case self::PAYMENT_PARTIALLY_REFUNDED:
1593	$status = self::ORDER_COMPLETED;
1594	break;
1595	case self::PAYMENT_REFUNDED:
1596	$status = self::ORDER_CANCELLED;
1597	break;
1598	case self::PAYMENT_FAILED:
1599	$status = self::ORDER_CANCELLED;
1600	break;
1601	case self::ORDER_TRASH:
1602	$status = self::ORDER_TRASH;
1603	break;
1604	case 'delete':
1605	$status = self::ORDER_CANCELLED;
1606	break;
1607	case self::ORDER_CANCELLED:
1608	$status = self::ORDER_CANCELLED;
1609	break;
1610	}
1611
1612	return $status;
1613	}
1614
1615	/**
1616	* Calculate order price
1617	*
1618	* @since 3.0.0
1619	*
1620	* @param array $items Order items, multi or single dimensional arr.
1621	*
1622	* @return object {subtotal => 10, total => 10}
1623	*/
1624	public static function calculate_order_price( array $items ) {
1625	$subtotal = 0;
1626	$total = 0;
1627
1628	if ( isset( $items[0] ) ) {
1629	foreach ( $items as $item ) {
1630	$regular_price = tutor_get_locale_price( $item['regular_price'] );
1631	$sale_price = is_null( $item['sale_price'] ) \|\| '' === $item['sale_price'] ? null : tutor_get_locale_price( $item['sale_price'] );
1632	$discount_price = is_null( $item['discount_price'] ) \|\| '' === $item['discount_price'] ? null : tutor_get_locale_price( $item['discount_price'] );
1633
1634	// Subtotal is the original price (regular price).
1635	$item_subtotal = $regular_price;
1636	$item_total = $regular_price;
1637
1638	// Determine the total based on sale price and discount.
1639	if ( ! is_null( $sale_price ) && $sale_price < $regular_price ) {
1640	$item_subtotal = $sale_price;
1641	$item_total = $sale_price;
1642	} else {
1643	// If there's a discount, apply it to the total price.
1644	if ( ! is_null( $discount_price ) && $discount_price >= 0 ) {
1645	$item_total = max( 0, $discount_price ); // Ensure total doesn't go below 0.
1646	}
1647	}
1648
1649	// $subtotal += $item_subtotal;
1650	$subtotal += $regular_price;
1651	$total += $item_total;
1652	}
1653	} else {
1654	// for single dimensional array.
1655	$regular_price = tutor_get_locale_price( $items['regular_price'] );
1656	$sale_price = is_null( $items['sale_price'] ) \|\| '' === $items['sale_price'] ? null : tutor_get_locale_price( $items['sale_price'] );
1657	$discount_price = is_null( $items['discount_price'] ) \|\| '' === $items['discount_price'] ? null : tutor_get_locale_price( $items['discount_price'] );
1658
1659	// Subtotal is the original price (regular price).
1660	$item_subtotal = $regular_price;
1661	$item_total = $regular_price;
1662
1663	// Determine the total based on sale price and discount.
1664	if ( ! is_null( $sale_price ) && $sale_price < $regular_price ) {
1665	$item_subtotal = $sale_price;
1666	$item_total = $sale_price;
1667	} else {
1668	// If there's a discount, apply it to the total price.
1669	if ( ! is_null( $discount_price ) && $discount_price >= 0 ) {
1670	$item_total = max( 0, $discount_price ); // Ensure total doesn't go below 0.
1671	}
1672	}
1673
1674	// $subtotal = $item_subtotal;
1675	$subtotal = $regular_price;
1676	$total = $item_total;
1677	}
1678
1679	return (object) array(
1680	'subtotal' => tutor_get_locale_price( $subtotal ),
1681	'total' => tutor_get_locale_price( $total ),
1682	);
1683	}
1684
1685	/**
1686	* Check has exclusive type tax.
1687	*
1688	* @since 3.0.0
1689	*
1690	* @param object $order order object.
1691	*
1692	* @return boolean
1693	*/
1694	public static function has_exclusive_tax( $order ) {
1695	return self::TAX_TYPE_EXCLUSIVE === $order->tax_type && $order->tax_rate > 0 && $order->tax_amount > 0;
1696	}
1697
1698	/**
1699	* Check has inclusive type tax.
1700	*
1701	* @since 3.0.0
1702	*
1703	* @param object $order order object.
1704	*
1705	* @return boolean
1706	*/
1707	public static function has_inclusive_tax( $order ) {
1708	return self::TAX_TYPE_INCLUSIVE === $order->tax_type && $order->tax_rate > 0 && $order->tax_amount > 0;
1709	}
1710
1711	/**
1712	* Get an item
1713	*
1714	* @since 3.0.0
1715	*
1716	* @param integer $item_id Item id.
1717	*
1718	* @return mixed
1719	*/
1720	public function get_item( int $item_id ) {
1721	return QueryHelper::get_row(
1722	$this->order_item_table,
1723	array(
1724	'item_id' => $item_id,
1725	),
1726	'id'
1727	);
1728	}
1729
1730	/**
1731	* Get sellable price
1732	*
1733	* @since 3.0.0
1734	*
1735	* @param mixed $regular_price Regular price.
1736	* @param mixed $sale_price Sale price.
1737	* @param mixed $discount_price Discount price.
1738	*
1739	* @return float item sellable price
1740	*/
1741	public static function get_item_sellable_price( $regular_price, $sale_price = null, $discount_price = null ) {
1742	// Ensure prices are numeric and properly formatted.
1743	$sellable_price = (
1744	! empty( $sale_price )
1745	? $sale_price
1746	: (
1747	( ! is_null( $discount_price ) && '' !== $discount_price ) && $discount_price >= 0
1748	? $discount_price
1749	: $regular_price
1750	)
1751	);
1752
1753	return $sellable_price;
1754	}
1755
1756	/**
1757	* Get item sold price
1758	*
1759	* @since 3.0.0
1760	*
1761	* @param mixed $item_id Item id.
1762	* @param bool $format Item id.
1763	*
1764	* @return mixed item sellable price
1765	*/
1766	public static function get_item_sold_price( $item_id, $format = true ) {
1767	$item = ( new self() )->get_item( $item_id );
1768
1769	if ( $item ) {
1770	$sold_price = self::get_item_sellable_price( $item->regular_price, $item->sale_price, $item->discount_price );
1771
1772	return $format ? tutor_get_formatted_price( $sold_price ) : $sold_price;
1773	}
1774
1775	return 0;
1776	}
1777
1778	/**
1779	* Should show pay btn to the user
1780	*
1781	* @since 3.0.0
1782	*
1783	* @param object $order Order object.
1784	*
1785	* @return boolean
1786	*/
1787	public static function should_show_pay_btn( object $order ) {
1788	$order_items = ( new self() )->get_order_items_by_id( $order->id );
1789	$is_enrolled_any_course = false;
1790	$is_incomplete_payment = self::PAYMENT_UNPAID === $order->payment_status && self::ORDER_INCOMPLETE === $order->order_status;
1791	$is_manual_payment = $order->payment_method ? self::is_manual_payment( $order->payment_method ) : false;
1792
1793	if ( $is_incomplete_payment && ! $is_manual_payment && $order_items ) {
1794	if ( self::TYPE_SINGLE_ORDER === $order->order_type ) {
1795	foreach ( $order_items as $item ) {
1796	$course_id = $item->id;
1797	if ( $course_id ) {
1798	$is_enrolled = tutor_utils()->is_enrolled( $course_id );
1799	if ( $is_enrolled ) {
1800	$is_enrolled_any_course = true;
1801	break;
1802	}
1803	}
1804	}
1805	} elseif ( tutor_utils()->count( $order_items ) ) {
1806	$course_id = apply_filters( 'tutor_subscription_course_by_plan', $order_items[0]->id );
1807	if ( tutor_utils()->is_enrolled( $course_id ) ) {
1808	$is_enrolled_any_course = true;
1809	}
1810	}
1811	}
1812
1813	return apply_filters( 'tutor_should_show_pay_btn', $is_incomplete_payment && ! $is_manual_payment && ! $is_enrolled_any_course );
1814	}
1815
1816	/**
1817	* Check is manual payment
1818	*
1819	* @since 3.0.0
1820	*
1821	* @param string $method_name Payment method name.
1822	*
1823	* @return boolean
1824	*/
1825	public static function is_manual_payment( $method_name ) {
1826	$payment_methods = tutor_get_manual_payment_gateways();
1827
1828	$is_manual_payment = false;
1829	foreach ( $payment_methods as $payment_method ) {
1830	$is_manual_payment = $payment_method->name === $method_name;
1831	}
1832
1833	return $is_manual_payment;
1834	}
1835
1836	/**
1837	* Render pay button
1838	*
1839	* @since 3.0.1
1840	*
1841	* @param int\|object $order Order id or object.
1842	*
1843	* @return void
1844	*/
1845	public static function render_pay_button( $order ) {
1846
1847	$self = new self();
1848
1849	if ( is_numeric( $order ) ) {
1850	$order = $self->get_order_by_id( $order );
1851	}
1852
1853	$show_pay_button = self::should_show_pay_btn( $order );
1854
1855	if ( ! self::should_active_pay_button( $order, $show_pay_button ) && $show_pay_button ) : ?>
1856
1857	<div class="tooltip-wrap tooltip-icon">
1858	<span class="tooltip-txt tooltip-left">
1859	<?php esc_html_e( 'Payment Is Pending Due To Gateway Processing.', 'tutor' ); ?>
1860	</span>
1861	</div>
1862	<?php
1863	elseif ( $show_pay_button ) :
1864	ob_start();
1865	$self->pay_now_link( $order->id );
1866	echo apply_filters( 'tutor_after_pay_button', ob_get_clean(), $order );//phpcs:ignore --sanitized output.
1867	endif;
1868	}
1869
1870	/**
1871	* Checks if the Repay Order-Time expired based on stored expiry time.
1872	*
1873	* @since 3.3.0
1874	*
1875	* @param object $order The order object containing order details.
1876	* @param bool $show_pay_button Whether the pay button should be shown.
1877	*
1878	* @return bool Returns true if the order is expired or expiry time is not set, otherwise false.
1879	*/
1880	private static function should_active_pay_button( $order, $show_pay_button ) {
1881
1882	$current_time = time();
1883	$meta_key = self::META_KEY_ORDER_ID . $order->id;
1884	$user_id = get_current_user_id();
1885	$expiry_time = get_user_meta( $user_id, $meta_key, true );
1886
1887	if ( $expiry_time ) {
1888
1889	// If the time is expired or the order is paid then delete the meta key.
1890	if ( $expiry_time < $current_time \|\| ! $show_pay_button ) {
1891	delete_user_meta( $user_id, $meta_key );
1892	return true;
1893	}
1894
1895	return false;
1896	}
1897
1898	return true;
1899	}
1900
1901	/**
1902	* Retrieves statements for a specific user.
1903	*
1904	* @since 3.5.0
1905	*
1906	* @param string $post_type_in_clause SQL clause to filter the course post types.
1907	* @param string $course_query SQL query string to further filter the courses .
1908	* @param string $date_query SQL query string to filter by date range.
1909	* @param int $user_id The user ID for which the statements are being retrieved.
1910	* @param int $offset The offset for pagination.
1911	* @param int $limit The number of rows to return.
1912	*
1913	* @return array
1914	*/
1915	public function get_statements( $post_type_in_clause, $course_query, $date_query, $user_id, $offset, $limit ): array {
1916	global $wpdb;
1917
1918	//phpcs:disable
1919	$statements = $wpdb->get_results(
1920	$wpdb->prepare(
1921	"SELECT
1922	IF (
1923	orders.total_price,
1924	orders.total_price,
1925	statements.course_price_total
1926	) AS order_total_price,
1927	orders.tax_amount AS order_tax_amount,
1928	orders.tax_type AS order_tax_type,
1929	statements.*,
1930	course.post_title AS course_title
1931	FROM {$wpdb->prefix}tutor_earnings AS statements
1932	LEFT JOIN {$wpdb->prefix}tutor_orders AS orders
1933	ON statements.order_id = orders.id
1934	INNER JOIN {$wpdb->posts} AS course ON course.ID = statements.course_id
1935	AND course.post_type IN ({$post_type_in_clause})
1936	WHERE statements.user_id = %d
1937	{$course_query}
1938	{$date_query}
1939	ORDER BY statements.created_at DESC
1940	LIMIT %d, %d
1941	",
1942	$user_id,
1943	$offset,
1944	$limit
1945	)
1946	);
1947
1948	$total_statements = $wpdb->get_var(
1949	$wpdb->prepare(
1950	"SELECT COUNT(*)
1951	FROM {$wpdb->prefix}tutor_earnings AS statements
1952	INNER JOIN {$wpdb->posts} AS course ON course.ID = statements.course_id
1953	AND course.post_type IN ({$post_type_in_clause})
1954	WHERE statements.user_id = %d
1955	{$course_query}
1956	{$date_query}
1957	",
1958	$user_id
1959	)
1960	);
1961	//phpcs:enable
1962
1963	return array(
1964	'statements' => $statements,
1965	'total_statements' => $total_statements,
1966	);
1967	}
1968
1969	/**
1970	* Get order details for given course IDs.
1971	*
1972	* @since 3.8.1
1973	*
1974	* @param int[] $course_ids Array of course IDs to fetch order details.
1975	*
1976	* @return array Returns an array of order details with each element containing:
1977	* - order data (all columns from tutor_orders table)
1978	* - order_items data (al columns except id)
1979	* Returns an empty array if no results found or on error.
1980	*/
1981	public function get_order_details( array $course_ids ) {
1982	global $wpdb;
1983
1984	$result = array();
1985
1986	$select_columns = array(
1987	'orders.*,
1988	order_items.id AS order_items_id,
1989	order_items.order_id,
1990	order_items.item_id,
1991	order_items.regular_price,
1992	order_items.sale_price,
1993	order_items.discount_price,
1994	order_items.coupon_code AS item_coupon_code',
1995	);
1996	$primary_table = "{$wpdb->tutor_order_items} AS order_items";
1997	$joining_tables = array(
1998	array(
1999	'type' => 'LEFT',
2000	'table' => "{$wpdb->tutor_orders} AS orders",
2001	'on' => 'orders.id = order_items.order_id',
2002	),
2003	);
2004	$where = array( 'order_items.item_id' => array( 'IN', $course_ids ) );
2005
2006	$result = QueryHelper::get_joined_data( $primary_table, $joining_tables, $select_columns, $where, array(), '', -1 );
2007
2008	return $result['results'];
2009	}
2010
2011	/**
2012	* Retrieve order meta for a specific order.
2013	*
2014	* @since 3.8.1
2015	*
2016	* @param int $order_id The ID of the order for which the metadata is to be retrieved.
2017	*
2018	* @return array An array of order meta. Returns an empty array if no meta is found.
2019	*/
2020	public function get_order_meta_by_order_id( $order_id ) {
2021
2022	return QueryHelper::get_all( 'tutor_ordermeta', array( 'order_id' => $order_id ), 'order_id' );
2023	}
2024
2025	/**
2026	* Retrieve earnings for a specific order and course.
2027	*
2028	* @since 3.8.1
2029	*
2030	* @param int $order_id The ID of the order for which the earnings are being retrieved.
2031	* @param int $course_id The ID of the course for which the earnings are being retrieved.
2032	*
2033	* @return array An array of earnings data. Returns an empty array if no data is found.
2034	*/
2035	public function get_earnings_by_order_and_course( $order_id, $course_id ) {
2036
2037	$where = array( 'order_id' => $order_id );
2038
2039	if ( ! empty( $course_id ) ) {
2040	$where['course_id'] = $course_id;
2041	}
2042
2043	return QueryHelper::get_all( 'tutor_earnings', $where, 'order_id' );
2044	}
2045
2046	/**
2047	* Retrieve an existing order if it is incomplete, unpaid, and belongs to the given user.
2048	*
2049	* @since 3.9.2
2050	*
2051	* @param int $order_id The ID of the order.
2052	* @param int $user_id The ID of the current user.
2053	* @param bool $return_order_data Optional. Whether to return the order data instead of a boolean.
2054	*
2055	* @return bool\|object Returns:
2056	* - The order object if valid and $return_order_data is true.
2057	* - True if valid and $return_order_data is false.
2058	* - false if the order is invalid or not found.
2059	*/
2060	public static function get_valid_incomplete_order( int $order_id, int $user_id, $return_order_data = false ) {
2061
2062	if ( empty( $order_id ) \|\| empty( $user_id ) ) {
2063	return false;
2064	}
2065
2066	$order_data = ( new self() )->get_order_by_id( $order_id );
2067
2068	if ( empty( $order_data ) ) {
2069	return false;
2070	}
2071
2072	$is_valid = self::ORDER_INCOMPLETE === $order_data->order_status
2073	&& self::PAYMENT_UNPAID === $order_data->payment_status
2074	&& $user_id === $order_data->student->id
2075	&& self::should_show_pay_btn( $order_data );
2076
2077	if ( $is_valid && $return_order_data ) {
2078	return $order_data;
2079	}
2080
2081	return $is_valid;
2082	}
2083
2084	/**
2085	* Update all order items for a given order.
2086	*
2087	* @since 3.9.2
2088	*
2089	* @param int $order_id The ID of the order whose items are being updated.
2090	* @param array $order_items An array of order item data arrays or objects to update.
2091	*
2092	* @return bool True on success, false if any update fails.
2093	*/
2094	public function update_order_items( int $order_id, array $order_items ) {
2095
2096	foreach ( $order_items as $item ) {
2097	try {
2098	QueryHelper::update_where_in(
2099	$this->order_item_table,
2100	$item,
2101	$order_id
2102	);
2103
2104	} catch ( \Throwable $th ) {
2105	tutor_log( "Failed to update order item for order ID {$order_id}: " . $th->getMessage() );
2106	return false;
2107	}
2108	}
2109
2110	return true;
2111	}
2112
2113	/**
2114	* Generate the payment link button HTML for a given order.
2115	*
2116	* @since 3.9.2
2117	*
2118	* @param int $order_id The unique ID of the order.
2119	* @param bool $display Optional. Whether to echo the link (true) or return it (false). Default true.
2120	*
2121	* @return string\|void
2122	*/
2123	public static function pay_now_link( $order_id, $display = true ) {
2124
2125	$checkout_url = add_query_arg( array( 'order_id' => $order_id ), CheckoutController::get_page_url() );
2126
2127	$link =
2128	sprintf(
2129	'<a href="%s" class="tutor-btn tutor-btn-sm tutor-btn-outline-primary">
2130	%s
2131	</a>',
2132	esc_url( $checkout_url ),
2133	esc_html__( 'Pay', 'tutor' )
2134	);
2135
2136	if ( $display ) {
2137	echo wp_kses(
2138	$link,
2139	array(
2140	'a' => array(
2141	'href' => true,
2142	'class' => true,
2143	),
2144	)
2145	);
2146	} else {
2147	return $link;
2148	}
2149	}
2150	}
2151