PluginProbe
Better Payment – Instant Payments, Donations, Fundraising with Subscriptions & More / 2.4.1
Better Payment – Instant Payments, Donations, Fundraising with Subscriptions & More v2.4.1
2.4.1 2.4.0 2.3.4 2.3.3 2.3.2 2.3.1 2.3.0 2.2.2 2.2.1 2.2.0 2.1.2 2.1.1 trunk 0.0.1 0.0.2 0.0.3 0.0.4 0.0.5 0.0.6 0.0.7 1.0.0 1.0.1 1.0.2 1.0.3 1.0.4 All 68 releases
← All changes | includes/Admin/DB.php +181 -110 2.3.3 → 2.4.1 View file →
@@ -19,8 +19,16 @@
19 19 * @since 0.0.1
20 20 */
21 21 class DB {
22 22
23 + /**
24 + * Helper's counting rule (an unconfirmed pre-2.4.0 `no_payment_required` row is not counted; a
25 + * confirmed one adds 0), aliased so the two deprecated queries below spell it `self::…` exactly
26 + * as Pro's AnalyticsModel copies do (AnalyticsModelParityTest compares the bodies).
27 + */
28 + private const COUNTED_ROW_SQL = Helper::COUNTED_ROW_SQL;
29 + private const COUNTABLE_AMOUNT_SQL = Helper::COUNTABLE_AMOUNT_SQL;
30 +
23 31 public static function get_table_name() {
24 32 global $wpdb;
25 33
26 34 $table = "{$wpdb->prefix}better_payment";
@@ -148,11 +156,43 @@
148 156 return update_option('better_payment_settings', $value);
149 157 }
150 158
151 159 /**
160 + * The UTC `payment_date` bounds for a date range picked in site time.
161 + *
162 + * The admin date filter sends whole days (`Y-m-d`), picked on the site's
163 + * calendar. Compared as-is, `BETWEEN '2026-10-11' AND '2026-10-11'` matched
164 + * only rows at exactly midnight — so the last day of every range was dropped
165 + * — and the days were UTC days, not the site's. A day bound is widened to the
166 + * whole site day (00:00:00 – 23:59:59) and converted to UTC, which is how
167 + * `payment_date` is stored. A bound that already carries a time is passed
168 + * through unchanged.
169 + *
170 + * @since 2.4.1
171 + *
172 + * @param string $from Lower bound.
173 + * @param string $to Upper bound.
174 + * @return string[] [ from, to ].
175 + */
176 + public static function payment_date_range( $from, $to ) {
177 + $from = trim( (string) $from );
178 + $to = trim( (string) $to );
179 +
180 + if ( preg_match( '/^\d{4}-\d{2}-\d{2}$/', $from ) ) {
181 + $from = get_gmt_from_date( $from . ' 00:00:00', 'Y-m-d H:i:s' );
182 + }
183 +
184 + if ( preg_match( '/^\d{4}-\d{2}-\d{2}$/', $to ) ) {
185 + $to = get_gmt_from_date( $to . ' 23:59:59', 'Y-m-d H:i:s' );
186 + }
187 +
188 + return array( $from, $to );
189 + }
190 +
191 + /**
152 192 * Get all transactions from better payment table.
153 193 *
154 - * @param string $args
194 + * @param array|string $args Filters, as wp_parse_args() reads them (every caller passes an array).
155 195 * @return array
156 196 * @since 0.0.1
157 197 */
158 198 public static function get_transactions($args = [], $count_only=0, $version = 'v1', $fetchNull = 0, $transaction_type = '')
@@ -213,10 +253,21 @@
213 253 $statuses_by_transaction_type = array();
214 254
215 255 if( !empty($transaction_type) ){
216 256 $statuses_by_types = $better_payment_helper_obj->transaction_statuses_with_type_v2();
217 - $statuses_by_transaction_type = isset($statuses_by_types[$transaction_type]['statuses']) ? $statuses_by_types[$transaction_type]['statuses'] : $defaults['status'];
218 - $status = '';
257 + $transaction_type = is_string( $transaction_type ) ? $transaction_type : 'all';
258 +
259 + if ( 'null' === $transaction_type ) {
260 + // The count twin of `status=null` below.
261 + $status = 'null';
262 + $transaction_type = '';
263 + } else {
264 + // An unknown type is every row, as an unknown `status` is. It used to fall back to the
265 + // string 'all' and fatal in array_map() below.
266 + $transaction_type = isset( $statuses_by_types[ $transaction_type ] ) ? $transaction_type : 'all';
267 + $statuses_by_transaction_type = $statuses_by_types[ $transaction_type ]['statuses'];
268 + $status = '';
269 + }
219 270 }
220 271
221 272 $whereQuery = '';
222 273
@@ -250,19 +301,37 @@
250 301
251 302 $statuses = array_map(function($status) {
252 303 return "'" . esc_sql($status) . "'";
253 304 }, $statuses_by_type);
254 -
305 +
255 306 $statuses = implode(',', $statuses);
256 -
257 - $nullDataQuery = $fetchNull ? " OR status IS NULL " : '';
258 - $whereQuery .= " AND ( status IN ( $statuses ) " . esc_sql($nullDataQuery) . " ) ";
307 +
308 + $bucket = $transaction_type ? $transaction_type : $status;
309 +
310 + // NULL is the incomplete bucket's own status (PayPal's pending sentinel): `IN ()`
311 + // cannot match it, so the Incomplete tab always ORs it in and lists the rows its
312 + // total and stat card count.
313 + $nullDataQuery = ( $fetchNull || 'incomplete' === $bucket ) ? " OR status IS NULL " : '';
314 +
315 + // The counting rule, for the tabs: an unconfirmed pre-2.4.0 `no_payment_required`
316 + // row is in no count, so it is listed under Incomplete, never Completed, as
317 + // Helper::transaction_status_type() labels it. A confirmed one stays Completed;
318 + // `refunded` is unchanged. Helper constants only (no request data, no `%`).
319 + $unconfirmed_sql = 'incomplete' === $bucket ? ' OR ' . Helper::UNCONFIRMED_LEGACY_SQL . ' ' : '';
320 + $counted_sql = 'completed' === $bucket ? ' AND ' . Helper::COUNTED_ROW_SQL . ' ' : '';
321 +
322 + // `all` is every row, as the All tab's list is: its `IN ()` list would leave NULL and
323 + // unlisted statuses out of the total.
324 + if ( 'all' !== $bucket ) {
325 + $whereQuery .= " AND ( status IN ( $statuses ) " . esc_sql($nullDataQuery) . $unconfirmed_sql . " ) " . $counted_sql;
326 + }
259 327 }
260 328 }
261 329 }
262 330
263 331 if( $args['payment_date_from'] != '' && $args['payment_date_to'] != ''){
264 - $whereQuery .= $wpdb->prepare(" AND payment_date BETWEEN %s AND %s", $args['payment_date_from'], $args['payment_date_to']);
332 + $range = self::payment_date_range( $args['payment_date_from'], $args['payment_date_to'] );
333 + $whereQuery .= $wpdb->prepare(" AND payment_date BETWEEN %s AND %s", $range[0], $range[1]);
265 334 }
266 335
267 336 if($count_only === 1){
268 337 if($valid_search_text){
@@ -335,40 +404,11 @@
335 404
336 405 /**
337 406 * Get transactions analytics
338 407 *
339 - * @return int
408 + * @return array<string, mixed>
340 409 * @since 0.0.1
341 410 */
342 - public static function get_transactions_analytics($filtered_transactions = array())
343 - {
344 - global $wpdb;
345 - $transaction_analytics = [];
346 -
347 - $table = self::get_table_name();
348 - $better_payment_db_obj = new DB();
349 -
350 - $total_transactions = $better_payment_db_obj->get_transactions_amount_by_statuses(array(), '', '', 'count', 1, $filtered_transactions );
351 -
352 - $completed_statuses = $better_payment_db_obj->allowed_statuses('completed', 'v2');
353 - $completed_transactions = $better_payment_db_obj->get_transactions_amount_by_statuses( $completed_statuses, '', '', 'count', 0, $filtered_transactions );
354 -
355 - $incomplete_statuses = $better_payment_db_obj->allowed_statuses('incomplete', 'v2');
356 - $incomplete_transactions = $better_payment_db_obj->get_transactions_amount_by_statuses( $incomplete_statuses, '', '', 'count', 1, $filtered_transactions );
357 -
358 - $transaction_analytics['total_transactions'] = $total_transactions;
359 - $transaction_analytics['completed_transactions'] = $completed_transactions;
360 - $transaction_analytics['incomplete_transactions'] = $incomplete_transactions;
361 -
362 - return $transaction_analytics;
363 - }
364 -
365 - /**
366 - * Get transactions analytics
367 - *
368 - * @return int
369 - * @since 0.0.1
370 - */
371 411 public static function get_transactions_analytics_dashboard($filtered_transactions = array())
372 412 {
373 413 global $wpdb;
374 414 $transaction_analytics = [];
@@ -442,35 +482,15 @@
442 482 return $item;
443 483 }
444 484
445 485 /**
446 - * Get transaction details from better payment table.
447 - *
448 - * @param string $args
449 - * @return object
450 - * @since 0.0.1
451 - */
452 - public static function get_transactions_by_email($email)
453 - {
454 - global $wpdb;
455 -
456 - $table = self::get_table_name();
457 - $email = sanitize_text_field($email);
458 -
459 - $item = $wpdb->get_results(
460 - $wpdb->prepare(
461 - "SELECT * FROM $table WHERE email = %s order by payment_date DESC",
462 - $email
463 - )
464 - );
465 -
466 - return $item;
467 - }
468 -
469 - /**
470 486 * Get analytics counts and amounts for a specific user via a single SQL aggregate query.
471 487 * Returns the same shape as get_transactions_analytics_dashboard() without loading all rows.
472 488 *
489 + * An unconfirmed pre-2.4.0 `no_payment_required` row is left out of every figure; a confirmed
490 + * one counts as completed and adds 0 to the amounts (Helper::COUNTED_ROW_SQL /
491 + * Helper::COUNTABLE_AMOUNT_SQL).
492 + *
473 493 * @param string $email
474 494 * @return array
475 495 */
476 496 public static function get_user_analytics_by_email( $email ) {
@@ -478,25 +498,30 @@
478 498 $table = self::get_table_name();
479 499 $email = sanitize_text_field( $email );
480 500 $ffi_like = '%' . $wpdb->esc_like( '"' . $email . '"' ) . '%';
481 501
502 + // Helper constants only: no request data, and no `%` for prepare() to misread.
503 + $countable_amount = 'CAST(' . Helper::COUNTABLE_AMOUNT_SQL . ' AS DECIMAL(10,2))';
504 + $completed = "status IN ('paid','Completed','completed','success','" . Helper::LEGACY_FREE_CHECKOUT_STATUS . "')";
505 + $counted_row = Helper::COUNTED_ROW_SQL;
506 +
482 507 $row = $wpdb->get_row(
483 508 $wpdb->prepare(
484 509 "SELECT
485 510 COUNT(*) AS total_count,
486 - SUM(CAST(amount AS DECIMAL(10,2))) AS total_amount,
487 - SUM(CASE WHEN status IN ('paid','Completed','completed','success')
511 + SUM({$countable_amount}) AS total_amount,
512 + SUM(CASE WHEN {$completed}
488 513 THEN 1 ELSE 0 END) AS completed_count,
489 - SUM(CASE WHEN status IN ('paid','Completed','completed','success')
490 - THEN CAST(amount AS DECIMAL(10,2)) ELSE 0 END) AS completed_amount,
491 - SUM(CASE WHEN status IN ('pending','Pending','unpaid','incomplete','Incomplete') OR status IS NULL
514 + SUM(CASE WHEN {$completed}
515 + THEN {$countable_amount} ELSE 0 END) AS completed_amount,
516 + SUM(CASE WHEN status IN ('pending','Pending','unpaid','incomplete','Incomplete','failed') OR status IS NULL
492 517 THEN 1 ELSE 0 END) AS incomplete_count,
493 - SUM(CASE WHEN status IN ('pending','Pending','unpaid','incomplete','Incomplete') OR status IS NULL
518 + SUM(CASE WHEN status IN ('pending','Pending','unpaid','incomplete','Incomplete','failed') OR status IS NULL
494 519 THEN CAST(amount AS DECIMAL(10,2)) ELSE 0 END) AS incomplete_amount,
495 520 SUM(CASE WHEN status = 'refunded' THEN 1 ELSE 0 END) AS refunded_count,
496 521 SUM(CASE WHEN status = 'refunded'
497 522 THEN CAST(amount AS DECIMAL(10,2)) ELSE 0 END) AS refunded_amount
498 - FROM $table WHERE ( email = %s OR form_fields_info LIKE %s )",
523 + FROM $table WHERE ( email = %s OR form_fields_info LIKE %s ) AND {$counted_row}",
499 524 $email,
500 525 $ffi_like
501 526 )
502 527 );
@@ -541,11 +566,19 @@
541 566 $page = max( 1, intval( $args['page'] ) );
542 567 $per_page = max( 1, intval( $args['per_page'] ) );
543 568 $offset = ( $page - 1 ) * $per_page;
544 569
545 - $type_where = '';
546 - if ( 'subscriptions' === $args['type'] ) {
547 - $type_where = " AND form_fields_info LIKE '%\"subscription_id\"%'";
570 + $type_where = self::user_transactions_type_where( (string) $args['type'] );
571 + if ( null === $type_where ) {
572 + // Nothing defines this row type (e.g. 'subscriptions' without Pro): answer
573 + // with no rows, never with every row the user owns.
574 + return [
575 + 'transactions' => [],
576 + 'total' => 0,
577 + 'pages' => 1,
578 + 'page' => $page,
579 + 'per_page' => $per_page,
580 + ];
548 581 }
549 582
550 583 // Match rows where the email column is set directly OR where the email
551 584 // appears as a quoted value inside the serialized form_fields_info blob.
@@ -583,8 +616,32 @@
583 616 ];
584 617 }
585 618
586 619 /**
620 + * The SQL condition narrowing get_user_transactions_paginated() to a row type.
621 + *
622 + * 'transactions' (or no type) is every row the user owns. Every other type is
623 + * defined by `better_payment/db/user_transactions_type_where` ($where, $type),
624 + * which starts from null: Pro supplies the 'subscriptions' condition for its
625 + * recurring and split payments. A type nobody defines returns null, and the
626 + * caller answers with no rows.
627 + *
628 + * @since 2.4.0
629 + *
630 + * @param string $type Requested row type.
631 + * @return string|null SQL fragment starting with " AND", '' for all rows, null for none.
632 + */
633 + private static function user_transactions_type_where( string $type ) {
634 + if ( '' === $type || 'transactions' === $type ) {
635 + return '';
636 + }
637 +
638 + $where = apply_filters( 'better_payment/db/user_transactions_type_where', null, $type );
639 +
640 + return is_string( $where ) ? $where : null;
641 + }
642 +
643 + /**
587 644 * Delete a transaction
588 645 *
589 646 * @param int $id
590 647 *
@@ -629,8 +686,17 @@
629 686
630 687 /**
631 688 * Get transactions amount
632 689 *
690 + * Lite never calls this; Better Payment Pro <= 1.4.3's AnalyticsModel does, and
691 + * tests/Unit/ProCouplingContractTest.php pins it. Pro 1.4.4 carries its own copy
692 + * (better-payment-pro/includes/Models/AnalyticsModel.php).
693 + *
694 + * @deprecated 2.4.0 A fatal guard only: Pro 1.4.3 is unsupported since
695 + * ProCompatibility::MIN_PRO_VERSION 1.4.4, but a site that updates Lite
696 + * first must not fatal on Pro's Analytics screen. Delete once no
697 + * supported site can run Pro 1.4.3.
698 + *
633 699 * @return array
634 700 * @since 0.0.1
635 701 */
636 702 public function get_transactions_by_statuses($statuses = array(), $payment_date_from = '', $payment_date_to = '', $fetchNull=0) {
@@ -655,17 +721,17 @@
655 721 $statuses = implode(',', $statuses);
656 722
657 723 if(!empty($payment_date_from) && !empty($payment_date_to)){
658 724 $amount = $wpdb->get_results(
659 - "SELECT amount, payment_date, email FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ") AND payment_date BETWEEN '" . esc_sql($payment_date_from) . "' AND '" . esc_sql($payment_date_to) . "'"
725 + "SELECT " . self::COUNTABLE_AMOUNT_SQL . " AS amount, payment_date, email FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ") AND " . self::COUNTED_ROW_SQL . " AND payment_date BETWEEN '" . esc_sql($payment_date_from) . "' AND '" . esc_sql($payment_date_to) . "'"
660 726 );
661 727 } else if(!empty($payment_date_from) && empty($payment_date_to)){
662 728 $amount = $wpdb->get_results(
663 - "SELECT amount, payment_date, email FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ") AND payment_date >= '" . esc_sql($payment_date_from) . "'"
729 + "SELECT " . self::COUNTABLE_AMOUNT_SQL . " AS amount, payment_date, email FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ") AND " . self::COUNTED_ROW_SQL . " AND payment_date >= '" . esc_sql($payment_date_from) . "'"
664 730 );
665 731 } else {
666 732 $amount = $wpdb->get_results(
667 - "SELECT amount, payment_date, email FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . " )"
733 + "SELECT " . self::COUNTABLE_AMOUNT_SQL . " AS amount, payment_date, email FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . " ) AND " . self::COUNTED_ROW_SQL
668 734 );
669 735 }
670 736
671 737 return $amount;
@@ -673,15 +739,17 @@
673 739
674 740 /**
675 741 * Get transactions amount
676 742 *
677 - * @return array
743 + * Lite's caller, get_transactions_analytics_dashboard(), always passes
744 + * $filtered_transactions and is answered from that array. Without it the answer
745 + * comes from SQL, which only Better Payment Pro <= 1.4.3's AnalyticsModel asks
746 + * for — see transactions_amount_by_statuses_query().
747 + *
748 + * @return mixed
678 749 * @since 0.0.1
679 750 */
680 751 public function get_transactions_amount_by_statuses($statuses = array(), $payment_date_from = '', $payment_date_to = '', $count_or_amount = 'amount', $fetchNull=0, $filtered_transactions = array() ) {
681 - global $wpdb;
682 -
683 - $table = DB::get_table_name();
684 752 $allowed_statuses = $this->allowed_statuses('all', 'v2');
685 753
686 754 $nullDataQuery = $fetchNull ? " OR status IS NULL " : '';
687 755
@@ -703,13 +771,19 @@
703 771 $filtered_transactions_by_statuses = array();
704 772 $filtered_transactions_amount = 0;
705 773
706 774 foreach($filtered_transactions as $filtered_transaction) {
775 + // Helper's counting rule, as the SQL copies below apply it: an unconfirmed pre-2.4.0
776 + // free checkout is no transaction to count, and a confirmed one adds 0.
777 + if ( Helper::is_unconfirmed_legacy_free_checkout( $filtered_transaction ) ) {
778 + continue;
779 + }
780 +
707 781 if( in_array($filtered_transaction->status, $statuses_original) ) {
708 782 $filtered_transactions_by_statuses[] = $filtered_transaction->id;
709 - $filtered_transactions_amount += ! empty( $filtered_transaction->amount ) ? floatval( $filtered_transaction->amount ) : 0;
783 + $filtered_transactions_amount += Helper::countable_amount( $filtered_transaction );
710 784 }
711 - }
785 + }
712 786
713 787 if($count_or_amount == 'count'){
714 788 $filtered_transactions_by_statuses_count = count($filtered_transactions_by_statuses);
715 789 return $filtered_transactions_by_statuses_count;
@@ -719,11 +793,34 @@
719 793 return $filtered_transactions_amount;
720 794 }
721 795 }
722 796
797 + return $this->transactions_amount_by_statuses_query( $statuses, $nullDataQuery, $payment_date_from, $payment_date_to, $count_or_amount );
798 + }
799 +
800 + /**
801 + * The SQL count or sum behind get_transactions_amount_by_statuses() when no
802 + * filtered transactions are passed.
803 + *
804 + * @deprecated 2.4.0 A fatal guard only, for Better Payment Pro <= 1.4.3 (see
805 + * get_transactions_by_statuses()). Pro 1.4.4 carries its own copy in
806 + * better-payment-pro/includes/Models/AnalyticsModel.php.
807 + *
808 + * @param string $statuses Quoted, comma-separated statuses for IN().
809 + * @param string $nullDataQuery " OR status IS NULL " or ''.
810 + * @param string $payment_date_from Lower payment_date bound.
811 + * @param string $payment_date_to Upper payment_date bound.
812 + * @param string $count_or_amount 'count' or 'amount'.
813 + * @return mixed
814 + */
815 + private function transactions_amount_by_statuses_query( $statuses, $nullDataQuery, $payment_date_from, $payment_date_to, $count_or_amount ) {
816 + global $wpdb;
817 +
818 + $table = DB::get_table_name();
819 +
723 820 if($count_or_amount == 'count') {
724 821 $count = (int) $wpdb->get_var(
725 - "SELECT count(id) FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . " )"
822 + "SELECT count(id) FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . " ) AND " . self::COUNTED_ROW_SQL
726 823 );
727 824
728 825 return $count;
729 826 }
@@ -729,44 +826,18 @@
729 826 }
730 827
731 828 if(!empty($payment_date_from) && !empty($payment_date_to)){
732 829 $amount = $wpdb->get_var(
733 - "SELECT sum(amount) FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ") AND payment_date BETWEEN '" . esc_sql($payment_date_from) . "' AND '" . esc_sql($payment_date_to) . "'"
830 + "SELECT sum(" . self::COUNTABLE_AMOUNT_SQL . ") FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ") AND " . self::COUNTED_ROW_SQL . " AND payment_date BETWEEN '" . esc_sql($payment_date_from) . "' AND '" . esc_sql($payment_date_to) . "'"
734 831 );
735 832 }else {
736 833 $amount = $wpdb->get_var(
737 834 // #TODO Amount based on filtered transactions
738 - "SELECT sum(amount) FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ")"
835 + "SELECT sum(" . self::COUNTABLE_AMOUNT_SQL . ") FROM $table WHERE ( status IN (" . $statuses . ") " . esc_sql($nullDataQuery) . ") AND " . self::COUNTED_ROW_SQL
739 836 );
740 837 }
741 838
742 839 return $amount;
743 - }
744 -
745 - public static function search_transactions($items, $search_text, $offset, $per_page){
746 - $filtered_items = array();
747 - $search_text = str_replace('%', '', $search_text);
748 -
749 - if(count($items) > 0){
750 - $filtered_items = array_filter($items, function($item) use ($search_text){
751 - $form_fields_info = maybe_unserialize($item->form_fields_info);
752 -
753 - if(
754 - isset($form_fields_info['first_name']) && stripos($form_fields_info['first_name'], $search_text) !== false
755 - || isset($form_fields_info['last_name']) && stripos($form_fields_info['last_name'], $search_text) !== false
756 - || isset($form_fields_info['primary_first_name']) && stripos($form_fields_info['primary_first_name'], $search_text) !== false
757 - || isset($form_fields_info['primary_last_name']) && stripos($form_fields_info['primary_last_name'], $search_text) !== false
758 - || isset($form_fields_info['email']) && stripos($form_fields_info['email'], $search_text) !== false
759 - || isset($form_fields_info['primary_email']) && stripos($form_fields_info['primary_email'], $search_text) !== false
760 - ){
761 - return true;
762 - }
763 -
764 - });
765 -
766 - }
767 -
768 - return $filtered_items;
769 840 }
770 841
771 842 public static function mark_as_completed($id)
772 843 {