OrderModel.php — Tutor LMS – eLearning and online course solution 3.8.3

tutor / models / OrderModel.php

tutor / models Last commit date

BaseModel.php 9 months ago BillingModel.php 1 year ago CartItemModel.php 9 months ago CartModel.php 9 months ago CouponModel.php 11 months ago CourseModel.php 9 months ago LessonModel.php 9 months ago OrderActivitiesModel.php 1 year ago OrderItemMetaModel.php 9 months ago OrderItemModel.php 9 months ago OrderMetaModel.php 1 year ago OrderModel.php 9 months ago QuizModel.php 9 months ago UserModel.php 1 year ago WithdrawModel.php 1 year ago

OrderModel.php

2052 lines

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