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

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

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