prepare(), with %i for table names and %s/%d/%f for values, so nothing is ever interpolated into SQL. /** * Orchestrates payout creation and processing. */ class Payouts_Service { /** * Payout repository. * * @var Payout_Repository */ private $payouts; /** * Referral repository. * * @var Referral_Repository */ private $referrals; /** * Affiliate repository. * * @var Affiliate_Repository */ private $affiliates; /** * Settings. * * @var Settings */ private $settings; /** * Constructor. * * @param Payout_Repository $payouts Payout repository. * @param Referral_Repository $referrals Referral repository. * @param Affiliate_Repository $affiliates Affiliate repository. * @param Settings $settings Settings service. */ public function __construct( Payout_Repository $payouts, Referral_Repository $referrals, Affiliate_Repository $affiliates, Settings $settings ) { $this->payouts = $payouts; $this->referrals = $referrals; $this->affiliates = $affiliates; $this->settings = $settings; } /** * Registered payout gateways keyed by id. * * @since 1.0.0 * @return Payout_Gateway_Interface[] */ public function gateways() { $gateways = array(); foreach ( array( new Store_Credit_Gateway(), new Manual_Gateway() ) as $gateway ) { $gateways[ $gateway->id() ] = $gateway; } /** * Filters available payout gateways. * * @since 1.0.0 * @param Payout_Gateway_Interface[] $gateways Gateways keyed by id. */ return apply_filters( 'ppl_afw_payout_gateways', $gateways ); } /** * Payout methods an affiliate may choose, limited by the store setting. * The manual gateway stays admin only. * * @since 1.0.0 * @return Payout_Gateway_Interface[] */ public function enabled_gateways() { $enabled = (array) $this->settings->get( 'payout_methods' ); $gateways = array(); foreach ( $this->gateways() as $id => $gateway ) { if ( in_array( $id, $enabled, true ) ) { $gateways[ $id ] = $gateway; } } /** * Filters the payout gateways offered to affiliates in the portal. * * @since 1.0.0 * @param Payout_Gateway_Interface[] $gateways Enabled gateways keyed by id. */ return apply_filters( 'ppl_afw_enabled_payout_gateways', $gateways ); } /** * The detail fields a payout method collects from the affiliate. * * @since 1.0.0 * @param string $method Gateway id. * @return array[] Field definitions with key, label, type. */ public function method_fields( $method ) { $gateways = $this->gateways(); if ( isset( $gateways[ $method ] ) && method_exists( $gateways[ $method ], 'fields' ) ) { return $gateways[ $method ]->fields(); } return array(); } /** * Creates and processes a payout for one affiliate's approved balance. * * Only active affiliates are paid, through a method the store still * offers (the manual method is always available to the admin). Each * currency the affiliate earned in is paid as its own payout. * * What happens next depends on the gateway. A gateway that moves the * money itself (store credit, Stripe) completes the payout. A gateway * that cannot (manual, bank transfer, PayPal without the Payouts API) * leaves it in processing, awaiting payment, until the merchant marks * it as sent; nobody is told they were paid before that. * * @since 1.0.0 * @param int $affiliate_id Affiliate id. * @param string $method Gateway id; empty uses the affiliate's method. * @param string $currency Currency to pay; empty pays every currency with a balance. * @return int|\WP_Error Payout id (the first one when several currencies are paid) or error. */ public function pay_affiliate( $affiliate_id, $method = '', $currency = '' ) { $affiliate = $this->affiliates->find( $affiliate_id ); if ( ! $affiliate ) { return new \WP_Error( 'not_found', __( 'Affiliate not found.', 'affireo-affiliates-for-woocommerce' ) ); } if ( 'active' !== $affiliate->status ) { return new \WP_Error( 'not_active', __( 'Only active affiliates can be paid. Reactivate the affiliate first.', 'affireo-affiliates-for-woocommerce' ) ); } if ( '' === (string) $method ) { $method = '' !== (string) $affiliate->payout_method ? $affiliate->payout_method : 'manual'; } $method = sanitize_key( $method ); if ( 'manual' !== $method && ! in_array( $method, (array) $this->settings->get( 'payout_methods' ), true ) ) { return new \WP_Error( 'method_disabled', sprintf( /* translators: %s: payout method id */ __( 'The payout method %s is not offered any more. Ask the affiliate to choose another one, or pay them manually.', 'affireo-affiliates-for-woocommerce' ), $method ) ); } if ( '' === $currency ) { $first = null; $error = null; foreach ( $this->referrals->payable_totals( 0, (int) $affiliate_id ) as $row ) { $result = $this->pay_affiliate( $affiliate_id, $method, (string) $row->payout_currency ); if ( is_wp_error( $result ) ) { $error = $error ? $error : $result; } elseif ( null === $first ) { $first = $result; } } if ( null !== $first ) { return $first; } return $error ? $error : new \WP_Error( 'nothing_payable', __( 'This affiliate has no payable balance.', 'affireo-affiliates-for-woocommerce' ) ); } return $this->pay_currency( $affiliate, $method, $currency ); } /** * Pays one affiliate's approved balance in one currency. * * @since 1.3.0 * @param object $affiliate Affiliate row. * @param string $method Gateway id. * @param string $currency Currency code. * @return int|\WP_Error */ private function pay_currency( $affiliate, $method, $currency ) { global $wpdb; $affiliate_id = (int) $affiliate->id; // Advisory lock guards against concurrent payout runs for the same affiliate. $lock_name = $wpdb->prefix . 'ppl_afw_payout_' . absint( $affiliate_id ); $locked = (int) $wpdb->get_var( $wpdb->prepare( 'SELECT GET_LOCK(%s, 5)', $lock_name ) ); if ( 1 !== $locked ) { return new \WP_Error( 'locked', __( 'A payout for this affiliate is already being processed.', 'affireo-affiliates-for-woocommerce' ) ); } $status = 'failed'; try { $payout_id = $this->payouts->insert( array( 'affiliate_id' => $affiliate_id, 'amount' => 0, 'currency' => sanitize_text_field( $currency ), 'method' => $method, 'status' => 'processing', 'invoice_number' => $this->payouts->next_invoice_number(), 'processed_by' => get_current_user_id(), ) ); // Claim referrals first, then derive the amount from what was actually claimed. $claimed = $this->referrals->attach_to_payout( $affiliate_id, $payout_id, $currency ); $amount = $claimed ? $this->referrals->payout_total( $payout_id ) : 0.0; if ( $amount <= 0 ) { // Nothing, or clawbacks larger than the earnings: give the rows back. $this->referrals->detach_from_payout( $payout_id ); $this->payouts->delete( $payout_id ); return new \WP_Error( 'nothing_payable', __( 'This affiliate has no payable balance.', 'affireo-affiliates-for-woocommerce' ) ); } $this->payouts->update( $payout_id, array( 'amount' => $amount ) ); $this->affiliates->adjust_earnings( $affiliate_id, -$amount, false ); $gateways = $this->gateways(); $payout = $this->payouts->find( $payout_id ); if ( ! isset( $gateways[ $method ] ) ) { $result = new \WP_Error( 'no_gateway', sprintf( /* translators: %s: payout method id */ __( 'The payout method %s is not available.', 'affireo-affiliates-for-woocommerce' ), $method ) ); } else { $result = $gateways[ $method ]->process( $payout, $affiliate ); } if ( is_wp_error( $result ) ) { // Roll back: release referrals and restore the balance. $this->referrals->detach_from_payout( $payout_id ); $this->affiliates->adjust_earnings( $affiliate_id, $amount, false ); $this->payouts->update( $payout_id, array( 'status' => 'failed', 'notes' => $result->get_error_message(), ) ); $this->affiliates->log_activity( $affiliate_id, 'payout_failed', $result->get_error_message() ); return $result; } if ( is_array( $result ) && 'processing' === ( $result['status'] ?? '' ) ) { // The gateway recorded the payout but no money has moved yet. $status = 'processing'; $this->payouts->update( $payout_id, array( 'gateway_reference' => sanitize_text_field( (string) ( $result['reference'] ?? '' ) ), 'notes' => sanitize_text_field( (string) ( $result['note'] ?? __( 'Awaiting payment: send the money, then mark the payout as sent.', 'affireo-affiliates-for-woocommerce' ) ) ), ) ); } else { $status = 'completed'; $this->payouts->update( $payout_id, array( 'status' => 'completed', 'gateway_reference' => sanitize_text_field( (string) ( is_array( $result ) ? ( $result['reference'] ?? '' ) : $result ) ), 'completed_at' => current_time( 'mysql', true ), ) ); } } finally { $wpdb->query( $wpdb->prepare( 'SELECT RELEASE_LOCK(%s)', $lock_name ) ); } if ( 'completed' === $status ) { $this->affiliates->log_activity( $affiliate_id, 'payout', sprintf( 'Payout of %s via %s', wp_strip_all_tags( wc_price( $amount, array( 'currency' => $currency ) ) ), $method ) ); /** * Fires after a payout completes: the money was sent. * * @since 1.0.0 * @param int $payout_id Payout id. * @param object $affiliate Affiliate row. */ do_action( 'ppl_afw_payout_completed', $payout_id, $affiliate ); } else { $this->affiliates->log_activity( $affiliate_id, 'payout_processing', sprintf( 'Payout of %s via %s awaiting payment', wp_strip_all_tags( wc_price( $amount, array( 'currency' => $currency ) ) ), $method ) ); /** * Fires after a payout is recorded but its money has not moved * yet (offline methods, or a gateway that confirms later). * * @since 1.3.0 * @param int $payout_id Payout id. * @param object $affiliate Affiliate row. */ do_action( 'ppl_afw_payout_processing', $payout_id, $affiliate ); } return $payout_id; } /** * Marks a payout in processing as sent: the merchant (or a gateway that * confirms later) reports that the money moved. * * @since 1.3.0 * @param int $payout_id Payout id. * @param string $reference Optional payment reference, kept with any earlier one. * @return true|\WP_Error */ public function complete_payout( $payout_id, $reference = '' ) { $payout = $this->payouts->find( $payout_id ); if ( ! $payout || 'processing' !== $payout->status ) { return new \WP_Error( 'not_processing', __( 'Only payouts awaiting payment can be marked as sent.', 'affireo-affiliates-for-woocommerce' ) ); } $reference = sanitize_text_field( (string) $reference ); $this->payouts->update( (int) $payout->id, array( 'status' => 'completed', 'gateway_reference' => '' !== $reference ? $reference : (string) $payout->gateway_reference, 'notes' => '', 'completed_at' => current_time( 'mysql', true ), ) ); $affiliate = $this->affiliates->find( (int) $payout->affiliate_id ); $this->affiliates->log_activity( (int) $payout->affiliate_id, 'payout', sprintf( 'Payout %s marked as sent', $payout->invoice_number ) ); /** * Fires after a payout completes: the money was sent. Documented * above in pay_currency(). * * @since 1.0.0 * @param int $payout_id Payout id. * @param object $affiliate Affiliate row. */ do_action( 'ppl_afw_payout_completed', (int) $payout->id, $affiliate ); return true; } /** * Fails a payout in processing (cancelled by the merchant, or refused by * the gateway later): the referrals go back to approved and the balance * is restored, so the next run pays them again. * * @since 1.3.0 * @param int $payout_id Payout id. * @param string $message Reason shown on the payout. * @param string $status failed or cancelled. * @return true|\WP_Error */ public function fail_payout( $payout_id, $message, $status = 'failed' ) { $payout = $this->payouts->find( $payout_id ); if ( ! $payout || 'processing' !== $payout->status ) { return new \WP_Error( 'not_processing', __( 'Only payouts awaiting payment can be cancelled.', 'affireo-affiliates-for-woocommerce' ) ); } $this->referrals->detach_from_payout( (int) $payout->id ); $this->affiliates->adjust_earnings( (int) $payout->affiliate_id, (float) $payout->amount, false ); $this->payouts->update( (int) $payout->id, array( 'status' => in_array( $status, array( 'failed', 'cancelled' ), true ) ? $status : 'failed', 'notes' => sanitize_text_field( (string) $message ), ) ); $this->affiliates->log_activity( (int) $payout->affiliate_id, 'payout_failed', $payout->invoice_number . ': ' . $message ); return true; } /** * Pays every active affiliate whose approved balance in a currency * reaches the minimum payout. Used by the Run payouts button and the * Pro schedule. * * @since 1.3.0 * @return array{completed: int, processing: int, failed: int, errors: string[]} */ public function run_all() { $summary = array( 'completed' => 0, 'processing' => 0, 'failed' => 0, 'errors' => array(), ); $minimum = (float) $this->settings->get( 'payout_minimum' ); foreach ( $this->referrals->payable_totals( $minimum ) as $row ) { $result = $this->pay_affiliate( (int) $row->affiliate_id, '', (string) $row->payout_currency ); if ( is_wp_error( $result ) ) { ++$summary['failed']; $summary['errors'][] = '#' . (int) $row->affiliate_id . ': ' . $result->get_error_message(); continue; } $payout = $this->payouts->find( (int) $result ); if ( $payout && 'completed' === $payout->status ) { ++$summary['completed']; } else { ++$summary['processing']; } } return $summary; } /** * Changes an affiliate's payout method from their dashboard. New * values are merged over the stored ones, so a field left empty keeps * its saved value, then validated before anything is stored. * * @since 1.3.0 * @param object $affiliate Affiliate row. * @param string $method Gateway id, one of the enabled methods. * @param array $details New detail values, key => value. * @return true|\WP_Error */ public function update_payout_method( $affiliate, $method, array $details ) { if ( ! isset( $this->enabled_gateways()[ $method ] ) ) { return new \WP_Error( 'invalid_method', __( 'That payout method is not available.', 'affireo-affiliates-for-woocommerce' ) ); } $merged = array_merge( ppl_afw_decrypt_details( $affiliate ), $details ); /** * Filters the validation of payout details before they are saved. * Return a WP_Error to refuse them. * * @since 1.3.0 * @param true|\WP_Error $valid True when valid. * @param string $method Gateway id. * @param array $details Details after merging, key => value. * @param object $affiliate Affiliate row. */ $valid = apply_filters( 'ppl_afw_validate_payout_details', true, $method, $merged, $affiliate ); if ( is_wp_error( $valid ) ) { return $valid; } $this->save_payout_details( (int) $affiliate->id, $method, $merged ); return true; } /** * Stores an affiliate's payout details encrypted at rest. * * @since 1.0.0 * @param int $affiliate_id Affiliate id. * @param string $method Gateway id. * @param array $details Raw detail fields. */ public function save_payout_details( $affiliate_id, $method, array $details ) { $clean = array(); foreach ( $details as $key => $value ) { $clean[ sanitize_key( $key ) ] = sanitize_text_field( (string) $value ); } $this->affiliates->update( $affiliate_id, array( 'payout_method' => sanitize_key( $method ), 'payout_details' => Crypto::encrypt( wp_json_encode( $clean ) ), ) ); $this->affiliates->log_activity( $affiliate_id, 'payout_method_changed', $method ); } /** * Returns masked payout details for display. * * @since 1.0.0 * @param object $affiliate Affiliate row. * @return array */ public function masked_details( $affiliate ) { $decrypted = Crypto::decrypt( (string) $affiliate->payout_details ); $details = $decrypted ? (array) json_decode( $decrypted, true ) : array(); return array_map( array( Crypto::class, 'mask' ), $details ); } /** * Retries a failed payout after the underlying issue is fixed. * * @since 1.0.0 * @param int $payout_id Payout id. * @return int|\WP_Error New payout id or error. */ public function retry_failed( $payout_id ) { $payout = $this->payouts->find( $payout_id ); if ( ! $payout || 'failed' !== $payout->status ) { return new \WP_Error( 'not_retryable', __( 'Only failed payouts can be retried.', 'affireo-affiliates-for-woocommerce' ) ); } $this->payouts->update( $payout_id, array( 'status' => 'cancelled' ) ); return $this->pay_affiliate( (int) $payout->affiliate_id, $payout->method, (string) $payout->currency ); } /** * Builds CSV rows for a bulk payment file. Includes the affiliate's * payout destination (PayPal email or bank details) so the store owner * can send money without opening each profile. * * @since 1.0.0 * @param string $status Payout status filter. * @param int $limit Row cap. * @param string $method Optional payout method filter. * @return array[] CSV rows including header. */ public function export_rows( $status = 'processing', $limit = 500, $method = '' ) { $result = $this->payouts->query( array( 'status' => sanitize_key( $status ), 'method' => sanitize_key( $method ), 'per_page' => absint( $limit ), ) ); $rows = array( array( 'name', 'account_email', 'amount', 'currency', 'method', 'destination', 'invoice' ) ); foreach ( $result['rows'] as $payout ) { $affiliate = $this->affiliates->find( (int) $payout->affiliate_id ); $user = $affiliate ? get_userdata( (int) $affiliate->user_id ) : null; $rows[] = array( $user ? $user->display_name : '#' . $payout->affiliate_id, $user ? $user->user_email : '', number_format( (float) $payout->amount, 2, '.', '' ), $payout->currency, $payout->method, $affiliate ? $this->destination_label( $affiliate ) : '', $payout->invoice_number, ); } return $rows; } /** * CSV rows of what is owed but not paid yet: approved commissions that * are in no payout, per affiliate and currency, with the affiliate's * status and payout destination. Lets the merchant see and fund the * balances before any payout run. * * @since 1.3.0 * @return array[] CSV rows including header. */ public function unpaid_rows() { $minimum = (float) $this->settings->get( 'payout_minimum' ); $rows = array( array( 'affiliate_id', 'name', 'account_email', 'status', 'amount', 'currency', 'referrals', 'reaches_minimum', 'method', 'destination' ) ); foreach ( $this->referrals->unpaid_balances() as $balance ) { $affiliate = $this->affiliates->find( (int) $balance->affiliate_id ); $user = $affiliate ? get_userdata( (int) $affiliate->user_id ) : null; $rows[] = array( (int) $balance->affiliate_id, $user ? $user->display_name : '', $user ? $user->user_email : '', $affiliate ? $affiliate->status : '', number_format( (float) $balance->total, 2, '.', '' ), $balance->payout_currency, (int) $balance->referral_count, (float) $balance->total >= $minimum && (float) $balance->total > 0 ? 'yes' : 'no', $affiliate ? $affiliate->payout_method : '', $affiliate ? $this->destination_label( $affiliate ) : '', ); } return $rows; } /** * Human readable payout destination for an affiliate, from their * encrypted details and chosen method. * * @since 1.0.0 * @param object $affiliate Affiliate row. * @return string */ private function destination_label( $affiliate ) { $details = ppl_afw_decrypt_details( $affiliate ); switch ( $affiliate->payout_method ) { case 'paypal': return (string) ( $details['paypal_email'] ?? '' ); case 'bank_transfer': $parts = array_filter( array( $details['account_name'] ?? '', $details['account_number'] ?? '', $details['bank_name'] ?? '' ) ); return implode( ' / ', $parts ); } return ''; } /** * Generates a simple HTML invoice for browser printing to PDF. * * @since 1.0.0 * @param object $payout Payout row. * @return string HTML markup. */ public function invoice_html( $payout ) { $affiliate = $this->affiliates->find( (int) $payout->affiliate_id ); $user = $affiliate ? get_userdata( (int) $affiliate->user_id ) : null; ob_start(); ?>

invoice_number ); ?>

display_name : '#' . $payout->affiliate_id ); ?>

created_at ); ?>

method ); ?>

amount . ' ' . $payout->currency ); ?>

payout_details ?? '' ) ); return $decrypted ? (array) json_decode( $decrypted, true ) : array(); } /** * Pays out as a WooCommerce store credit coupon. */ class Store_Credit_Gateway implements Payout_Gateway_Interface { /** * Gateway id. * * @since 1.0.0 * @return string */ public function id() { return 'store_credit'; } /** * Gateway label. * * @since 1.0.0 * @return string */ public function label() { return __( 'Store credit coupon', 'affireo-affiliates-for-woocommerce' ); } /** * Creates a fixed cart coupon restricted to the affiliate's email. * * @since 1.0.0 * @param object $payout Payout row. * @param object $affiliate Affiliate row. * @return string|\WP_Error */ public function process( $payout, $affiliate ) { $user = get_userdata( (int) $affiliate->user_id ); if ( ! $user ) { return new \WP_Error( 'no_user', __( 'Affiliate user account not found.', 'affireo-affiliates-for-woocommerce' ) ); } if ( $payout->currency && get_woocommerce_currency() !== $payout->currency ) { return new \WP_Error( 'credit_currency', __( 'Store credit can only be paid in the store currency. Pay this balance with another method.', 'affireo-affiliates-for-woocommerce' ) ); } $coupon = new \WC_Coupon(); $coupon->set_code( strtolower( 'aff-credit-' . $payout->id . '-' . wp_generate_password( 6, false ) ) ); $coupon->set_discount_type( 'fixed_cart' ); $coupon->set_amount( (float) $payout->amount ); $coupon->set_email_restrictions( array( $user->user_email ) ); $coupon->set_usage_limit( 1 ); $coupon->save(); return $coupon->get_code(); } } /** * Offline gateway: the store owner records a payout they sent by other means. */ class Manual_Gateway implements Payout_Gateway_Interface { /** * Gateway id. * * @since 1.0.0 * @return string */ public function id() { return 'manual'; } /** * Gateway label. * * @since 1.0.0 * @return string */ public function label() { return __( 'Manual / offline', 'affireo-affiliates-for-woocommerce' ); } /** * Records the payout as awaiting payment. No money moves through this * method, so the payout completes only when it is marked as sent. * * @since 1.0.0 * @param object $payout Payout row. * @param object $affiliate Affiliate row. * @return array */ public function process( $payout, $affiliate ) { return array( 'status' => 'processing', 'reference' => 'manual-' . $payout->id, ); } }