Kemp.rest / Examples / Code / Affiliate referral sync
KEMP.REST / EXAMPLES / CODE
Affiliate referral sync
Written by Kevin Kemp in PHP. MySQL through WordPress, JSON, and the AffiliateWP REST API, for a proprietary back office .NET points system.
View the codeAffiliateWP created a pending sale referral for every WooCommerce order. Commission was not that simple. A first order, a recent repeat buyer, a customer quiet for 18 months, and a cancelled or refunded order each needed a different referral result. The back office already knew the customer program. WooCommerce had the orders. This job connects them.
Type of code
WordPress functions on a cron hook. No storefront page. It reads WooCommerce orders, queries the AffiliateWP table, and updates referrals through the AffiliateWP REST API.
Technology
PHP, WordPress cron, WooCommerce orders and customers, MySQL through $wpdb against affiliate_wp_referrals, JSON, and the AffiliateWP REST API with GET, PATCH, and DELETE. Calls use the WordPress HTTP API and HTTP Basic auth. The username and password come from Advanced Custom Fields options and are not written in the file.
What it does
- Each run loads the latest 20 pending sale referrals.
- It labels the buyer from the WooCommerce order: Audit when the order was cancelled, refunded, or failed; New for a single order; Active when a later order falls inside 18 months; Lapsed when the last order is older than 18 months; otherwise Unknown.
- An active repeat sale is accepted as unpaid, described as “Repeat Order,” and marked as a lifetime referral. Related tiered-sale referrals are set to unpaid. The order gets a note.
- A first sale, or a lapsed customer returning, is accepted as a first-sale bonus. Related tiered-sale referrals are removed, the amount is recalculated, and the order is noted.
- Ineligible cases reject the referral, with the description “Rejected referral. Customer ineligible,” and reject the related tiered-sale records.
Problem it solved
Affiliate payouts followed the customer rules instead of paying every pending referral the same way. The scheduled job classified the WooCommerce buyer and then accepted, repriced, or rejected the referral through the API.
Code
Back to summary · affiliate-function.php
<?php
if (class_exists('ACF')) {
// The ACF class doesn't exist, so you can probably redefine your functions here
define('BACKOFFICE_AFFILIATE_USERNAME_API',get_field('affiliate_wp_username','option'));
define('BACKOFFICE_AFFILIATE_PASSWORD_API',get_field('affiliate_wp_password','option'));
define('BACKOFFICE_DOMAIN',get_home_url());
}
function get_order_ids_by_referral($list_order_ids){
$args = array(
'post_type' => 'shop_order', // Specify that we're querying orders
'post__in' => $list_order_ids,
'orderby' => 'post__in', // Order by the post__in array
'posts_per_page' => -1, // Retrieve all matching posts
'post_status' => 'any',
);
$orders = new WP_Query($args);
$cd_reference = array();
if ($orders->have_posts()) {
while ($orders->have_posts()) { $orders->the_post();
$order_id = get_the_ID();
$order = wc_get_order($order_id);
$status = $order->get_status();
$customer_id = get_post_meta($order_id, '_customer_user', true);
$total_count_order = get_order_ids_by_user_id($customer_id);
$customer = new WC_Customer( $customer_id );
// Get the last WC_Order Object instance from current customer
$last_order = $customer->get_last_order();
$last_order_date = $last_order->get_date_created();
$current_date = new WC_DateTime();
// Calculate the date 18 months ago from today
$date_18_months_ago = new WC_DateTime();
$date_18_months_ago->modify('-18 months');
if($status == 'cancelled' || $status == 'refunded' || $status == 'failed') {
$cd_reference[] = array(
'id' => $order_id,
'customerStatus' => 'Audit'
);
} elseif($total_count_order == '1') {
$cd_reference[] = array(
'id' => $order_id,
'customerStatus' => 'New'
);
} elseif(($total_count_order > '1') && ($last_order_date < $date_18_months_ago)) {
$cd_reference[] = array(
'id' => $order_id,
'customerStatus' => 'Lapsed'
);
} elseif(($total_count_order > '1') && ($last_order_date > $date_18_months_ago) ){
$cd_reference[] = array(
'id' => $order_id,
'customerStatus' => 'Active'
);
} else {
$cd_reference[] = array(
'id' => $order_id,
'customerStatus' => 'Unknown'
);
}
// Process your order here
}
}
wp_reset_postdata();
return $cd_reference;
}
function get_order_ids_by_user_id($user_id) {
// Ensure WooCommerce is active
if (!class_exists('WooCommerce')) {
return 'WooCommerce is not active';
}
// Get all orders by user ID
$args = array(
'customer_id' => $user_id,
'limit' => -1, // To get all orders, you can set a specific number if needed
'return' => 'ids', // Only return IDs
);
$orders = wc_get_orders($args);
return $orders;
}
function get_referral_ids_by_order_ids_and_status($order_ids, $status = 'unpaid') {
global $wpdb;
$table_name = $wpdb->prefix . 'affiliate_wp_referrals';
// Prepare the query
$placeholders = implode(',', array_fill(0, count($order_ids), '%d'));
$sql = $wpdb->prepare("
SELECT referral_id
FROM $table_name
WHERE reference IN ($placeholders) AND status = %s
", array_merge($order_ids, array($status)));
// Execute the query
$results = $wpdb->get_col($sql);
return $results;
}
function fetch_api_data_with_basic_auth( $api_endpoint ) {
// Setup the HTTP request arguments
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$args = array(
'headers' => array(
'Authorization' => 'Basic ' . base64_encode( $username . ':' . $password ),
),
);
// Make the GET request
$response = wp_remote_get( $api_endpoint, $args );
// Check for WP_Error and handle if there's an error
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function patch_data_eligible_repeat_sale($referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$body = array(
'status' => 'unpaid',
'description' => 'Repeat Order',
'custom' => 'a:1:{s:17:"lifetime_referral";b:1;}'
);
$args = array(
'method' => 'PATCH',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function patch_data_eligible_initial_sale($amount,$referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$body = array(
'status' => 'unpaid',
'description' => 'First Sale Bonus',
'amount' => $amount
);
$args = array(
'method' => 'PATCH',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function patch_data_eligible_repeat_sale_related($referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$body = array(
'status' => 'unpaid',
'description' => 'Repeat Order',
);
$args = array(
'method' => 'PATCH',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function delete_data_eligible_repeat_sale_related($referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$args = array(
'method' => 'DELETE',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
//'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function patch_data_ineligible_initial_sale_related($referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$body = array(
'status' => 'rejected',
);
$args = array(
'method' => 'PATCH',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function patch_data_ineligible_initial_sale($referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$body = array(
'status' => 'rejected',
'description' => 'Rejected referral. Customer ineligible',
);
$args = array(
'method' => 'PATCH',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function patch_data_ineligible_repeat_sale_related($referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$body = array(
'status' => 'rejected',
);
$args = array(
'method' => 'PATCH',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
function patch_data_ineligible_repeat_sale($referral_id){
$url = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals/'.$referral_id.'';
$username = BACKOFFICE_AFFILIATE_USERNAME_API;
$password = BACKOFFICE_AFFILIATE_PASSWORD_API;
$body = array(
'status' => 'rejected',
'description' => 'Rejected referral. Customer ineligible',
'custom' => 'a:1:{s:17:"lifetime_referral";b:1;}'
);
$args = array(
'method' => 'PATCH',
'headers' => array(
'Authorization' => 'Basic ' . base64_encode($username . ':' . $password),
'Content-Type' => 'application/json'
),
'body' => json_encode($body),
'timeout' => 45,
'sslverify' => false,
);
// Make the PATCH request
$response = wp_remote_request($url, $args);
if ( is_wp_error( $response ) ) {
return $response; // Return WP_Error object
}
// Retrieve the response body
$body = wp_remote_retrieve_body( $response );
// Attempt to decode JSON if the response is JSON-formatted
$data = json_decode( $body, true );
// Return the decoded data array or the body if not JSON
return ( $data ) ? $data : $body;
}
add_action( 'backoffice_sync_status_referral_cron', 'backoffice_sync_status_referral' );
function backoffice_sync_status_referral(){
$excludedStatuses = array("Audit", "Unknown", "New", "Lapsed", "Active");
$api_endpoint = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals?number=20&order=desc&status=pending&type=sale';
$api_data = fetch_api_data_with_basic_auth( $api_endpoint );
if ( is_wp_error( $api_data ) ) {
// Handle WP_Error object
$error_message = $api_data->get_error_message();
echo "Error fetching API data: $error_message";
} else {
// Process $api_data as needed
if ($api_data[0]['referral_id']) {
$c_reference = array();
foreach ( $api_data as $item ) {
$c_reference[] = array(
'id' => $item['reference'],
'type' => $item['type'],
'custom' => $item['custom'],
'amount' => $item['amount'],
'referral_id' => $item['referral_id']
);
}
// Extract unique IDs into an array
$list_order_ids = array_unique(array_column($c_reference, 'id'));
$cd_reference = get_order_ids_by_referral($list_order_ids);
$array_reference = array();
foreach ($c_reference as $item) {
$array_reference[$item['id']] = $item;
}
// Merge arrays
$merged_reference = array();
foreach ($cd_reference as $item) {
$id = (string)$item['id']; // Convert id to string to match the keys in $array_reference
if (isset($array_reference[$id])) {
$merged_item = array_merge($item, $array_reference[$id]);
$merged_reference[] = $merged_item;
}
}
foreach ($merged_reference as $item) {
$referral_id = $item['referral_id'];
$amount = $item['amount'];
$id_reference = $item['id'];
// Skip Referral
if( ($item['customerStatus'] == "Audit" || $item['customerStatus'] == "Unknown")){
}
// Eligible Repeat Sale
elseif ($item['customerStatus'] == "Active" && !isset($entry['custom']['lifetime_referral'])) {
// related referrals of type "tiered_sale"
$api_endpoint = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals?reference='.$id_reference.'&order=desc&type=tiered_sale';
$api_related_referrals = fetch_api_data_with_basic_auth( $api_endpoint );
if ( is_wp_error( $api_related_referrals ) ) {
// Handle WP_Error object
$error_message = $api_related_referrals->get_error_message();
echo "Error fetching API data: $error_message";
} else {
if ($api_related_referrals['code'] != 'no_referrals') {
foreach ($api_related_referrals as $item) {
$related_referral_id = $item['referral_id'];
// update status unpaid related referrals
patch_data_eligible_repeat_sale_related($related_referral_id);
}
}
}
// update status , custom , des root referral
patch_data_eligible_repeat_sale($referral_id);
// update order note
$order = wc_get_order( $id_reference );
$note = __("AffilateWP: Repeat Referral accepted");
$order->add_order_note( $note );
}
// Eligible Initial Sale
elseif(($item['customerStatus'] == "New" && isset($item['custom']['lifetime_referral'] )) || ($item['customerStatus'] == "Lapsed" && isset($item['custom']['lifetime_referral'] )) ) {
// 1. related referrals of type "tiered_sale"
$api_endpoint = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals?reference='.$id_reference.'&order=desc&type=tiered_sale';
$api_related_referrals = fetch_api_data_with_basic_auth( $api_endpoint );
if ( is_wp_error( $api_related_referrals ) ) {
// Handle WP_Error object
$error_message = $api_related_referrals->get_error_message();
echo "Error fetching API data: $error_message";
} else {
if ($api_related_referrals['code'] != 'no_referrals') {
foreach ($api_related_referrals as $item) {
$related_referral_id = $item['referral_id'];
// 1. delete related referrals of type "tiered_sale"
delete_data_eligible_repeat_sale_related($related_referral_id);
}
}
}
// 4. update amount of the referral
$total_amount = $amount*3.3333;
patch_data_eligible_initial_sale(round($total_amount, 2),$referral_id);
// 5. update order note
$order = wc_get_order( $id_reference );
$note = __("AffiliateWP: Initial referral accepted, recalculated");
$order->add_order_note( $note );
}
// Ineligible Initial Sale
elseif (!in_array($entry['customerStatus'], $excludedStatuses) && isset($entry['custom']['lifetime_referral']) && $entry['custom']['lifetime_referral'] === true) {
// related referrals of type "tiered_sale"
$api_endpoint = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals?reference='.$id_reference.'&order=desc&type=tiered_sale';
$api_related_referrals = fetch_api_data_with_basic_auth( $api_endpoint );
if ( is_wp_error( $api_related_referrals ) ) {
// Handle WP_Error object
$error_message = $api_related_referrals->get_error_message();
echo "Error fetching API data: $error_message";
} else {
if ($api_related_referrals['code'] != 'no_referrals') {
foreach ($api_related_referrals as $item) {
$related_referral_id = $item['referral_id'];
// update status unpaid related referrals
patch_data_ineligible_initial_sale_related($related_referral_id);
}
}
}
// update status , custom , des root referral
patch_data_ineligible_initial_sale($referral_id);
// 5. update order note
$order = wc_get_order( $id_reference );
$note = __("AffiliateWP: Initial referral was rejected");
$order->add_order_note( $note );
// get user_id by current order
$user_id = get_post_meta($id_reference, '_customer_user', true);
// get all order by user id
$order_ids = get_order_ids_by_user_id($user_id);
if (is_wp_error($order_ids)) {
echo $order_ids->get_error_message(); // Output error message
} else {
// get all referral by list order with status unpaid
$referral_ids = get_referral_ids_by_order_ids_and_status($order_ids);
if ($referral_ids['code'] != 'no_referrals') {
foreach ($referral_ids as $referral_id) {
patch_data_ineligible_initial_sale_related($referral_id);
}
}
}
}
// Ineligible Repeat Sale
elseif (!in_array($entry['customerStatus'], $excludedStatuses) && (!isset($entry['custom']['lifetime_referral']) || $entry['custom']['lifetime_referral'] !== true)) {
// related referrals of type "tiered_sale"
$api_endpoint = BACKOFFICE_DOMAIN.'/wp-json/affwp/v1/referrals?reference='.$id_reference.'&order=desc&type=tiered_sale';
$api_related_referrals = fetch_api_data_with_basic_auth( $api_endpoint );
if ( is_wp_error( $api_related_referrals ) ) {
// Handle WP_Error object
$error_message = $api_related_referrals->get_error_message();
echo "Error fetching API data: $error_message";
} else {
if ($api_related_referrals['code'] != 'no_referrals') {
foreach ($api_related_referrals as $item) {
$related_referral_id = $item['referral_id'];
// update status unpaid related referrals
patch_data_ineligible_repeat_sale_related($related_referral_id);
}
}
}
// update status , custom , des root referral
patch_data_ineligible_repeat_sale($referral_id);
// 5. update order note
$order = wc_get_order( $id_reference );
$note = __("AffiliateWP: Repeat referral was rejected");
$order->add_order_note( $note );
}
}
}
}
}