<?php
/**
* File: Ecre_Database.php
*
* Database operations for Echo Rewards - CRUD operations only.
*
* @package ECRE
* @since 1.0.0
*/
namespace ECRE;
if ( ! defined( 'ABSPATH' ) ) {
exit;
}
if ( ! class_exists( 'ECRE\Ecre_Database' ) ) {
/**
* Echo Rewards database operations (CRUD only).
*
* @since 1.0.0
*/
class Ecre_Database {
/**
* Constructor intentionally empty.
*
* @since 1.0.0
*/
public function __construct() {}
/**
* Store referral order data.
*
* @since 1.0.0
*
* @param \WC_Order $order WooCommerce order object.
* @param string $coupon_code Coupon code used in the order.
* @param int $coupon_id Coupon ID.
* @param int $referral_user_id Referrer user ID.
*
* @return int|false Inserted referral ID on success, false on failure.
*/
public function store_referral_order_data( $order, $coupon_code, $coupon_id, $referral_user_id ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_referrals_orders';
// Bail if table does not exist.
$table_exists = $wpdb->get_var(
$wpdb->prepare(
'SHOW TABLES LIKE %s',
$wpdb->esc_like( $table_name )
)
);
if ( $table_exists !== $table_name ) {
return false;
}
// Customer info.
$names = ecre_get_customer_names_from_order( $order );
$customer_email = $order->get_billing_email();
$referred_user_id = $order->get_user_id();
// Coupon discount.
$discount_amount = ecre_calculate_coupon_discount_amount( $order, $coupon_code );
// Prevent duplicates.
$existing_entry = $wpdb->get_var(
$wpdb->prepare(
"SELECT id FROM `{$wpdb->prefix}ecre_referrals_orders` WHERE order_id = %d AND referrer_user_id = %d",
$order->get_id(),
$referral_user_id
)
);
if ( $existing_entry ) {
return false;
}
// Prepare data.
$referral_data = array(
'referrer_user_id' => (int) $referral_user_id,
'referred_user_id' => $referred_user_id ? (int) $referred_user_id : null,
'order_id' => (int) $order->get_id(),
'coupon_code' => sanitize_text_field( $coupon_code ),
'coupon_id' => $coupon_id ? (int) $coupon_id : null,
'order_value' => (float) $order->get_total(),
'discount_amount' => (float) $discount_amount,
'referral_status' => $order->get_status(),
'firstname' => sanitize_text_field( $names['firstname'] ),
'lastname' => sanitize_text_field( $names['lastname'] ),
'fullname' => sanitize_text_field( $names['fullname'] ),
'customer_email' => sanitize_email( $customer_email ),
'order_date' => $order->get_date_created()->date( 'Y-m-d H:i:s' ),
);
$data_formats = array( '%d', '%d', '%d', '%s', '%d', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s' );
// Remove null values safely.
$clean_data = array();
$clean_formats = array();
$i = 0;
foreach ( $referral_data as $key => $value ) {
if ( null !== $value ) {
$clean_data[ $key ] = $value;
$clean_formats[ $i ] = $data_formats[ $i ];
}
$i++;
}
$result = $wpdb->insert( $table_name, $clean_data, $clean_formats );
if ( false === $result ) {
return false;
}
$referral_id = $wpdb->insert_id;
// Store reference in order meta.
update_post_meta( $order->get_id(), '_ecre_referral_id', $referral_id );
return $referral_id;
}
/**
* Update referral status in the database for a given order.
*
* @since 1.0.0
*
* @param int $order_id WooCommerce order ID.
* @param string $referral_status New referral status (e.g. 'completed', 'cancelled').
*
* @return bool True on success, false on failure.
*/
public function update_referral_status( $order_id, $referral_status ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_referrals_orders';
// Bail if table does not exist.
$table_exists = $wpdb->get_var(
$wpdb->prepare(
'SHOW TABLES LIKE %s',
$wpdb->esc_like( $table_name )
)
);
if ( $table_exists !== $table_name ) {
return false;
}
// Update referral status and updated_at timestamp.
$result = $wpdb->update(
$table_name,
array(
'referral_status' => sanitize_text_field( $referral_status ),
'updated_at' => current_time( 'mysql' ),
),
array( 'order_id' => (int) $order_id ),
array( '%s', '%s' ),
array( '%d' )
);
if ( false === $result ) {
return false;
}
return true;
}
/**
* Stores reward order data when a reward coupon is used in an order.
*
* This function saves the reward order details, including the reward coupon and its associated IDs,
* along with referrer and referral information. Optionally, it can store referral coupon details if they are provided.
*
* @since 1.0.0
*
* @param \WC_Order $order WooCommerce order object.
* @param string $reward_coupon_code Reward coupon code used in the order.
* @param int $reward_coupon_id Reward coupon ID used in the order.
* @param int $referrer_user_id The original referrer user's ID.
* @param string $referral_coupon_code Original referral coupon code used (optional).
* @param int $referral_coupon_id Original referral coupon ID (optional).
* @param int $referral_user_id The original referral user's ID (optional).
*
* @return int|false Returns the inserted reward order ID on success, or false on failure.
*/
public function store_reward_order_data( $order, $reward_coupon_code, $reward_coupon_id, $referrer_user_id, $referral_coupon_code = null, $referral_coupon_id = null, $referral_user_id = null ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_rewards_orders';
// Bail if table does not exist.
$table_exists = $wpdb->get_var(
$wpdb->prepare(
'SHOW TABLES LIKE %s',
$wpdb->esc_like( $table_name )
)
);
if ( $table_exists !== $table_name ) {
return false;
}
// Customer info.
$names = ecre_get_customer_names_from_order( $order );
$customer_email = $order->get_billing_email();
$user_id = $order->get_user_id();
// Calculate reward discount amount.
$reward_discount_amount = ecre_calculate_coupon_discount_amount( $order, $reward_coupon_code );
// Prevent duplicates.
$existing_entry = $wpdb->get_var(
$wpdb->prepare(
"SELECT id FROM `{$wpdb->prefix}ecre_rewards_orders` WHERE reward_coupon_id = %d AND order_id = %d",
$reward_coupon_id,
$order->get_id()
)
);
if ( $existing_entry ) {
return false;
}
// Prepare data.
$reward_order_data = array(
'reward_coupon_id' => (int) $reward_coupon_id,
'reward_coupon_code' => sanitize_text_field( $reward_coupon_code ),
'order_id' => (int) $order->get_id(),
'user_id' => $user_id ? (int) $user_id : null,
'referral_user_id' => (int) $referral_user_id,
'referral_coupon_id' => $referral_coupon_id ? (int) $referral_coupon_id : null,
'referral_coupon_code' => $referral_coupon_code ? sanitize_text_field( $referral_coupon_code ) : null,
'reward_discount_amount' => (float) $reward_discount_amount,
'order_total' => (float) $order->get_total(),
'order_subtotal' => (float) $order->get_subtotal(),
'customer_email' => sanitize_email( $customer_email ),
'firstname' => sanitize_text_field( $names['firstname'] ),
'lastname' => sanitize_text_field( $names['lastname'] ),
'fullname' => sanitize_text_field( $names['fullname'] ),
'usage_date' => $order->get_date_created()->date( 'Y-m-d H:i:s' ),
'reward_status' => $order->get_status(),
'reward_type' => 'coupon',
);
$data_formats = array( '%d', '%s', '%d', '%d', '%d', '%d', '%s', '%f', '%f', '%f', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s' );
// Remove null values safely.
$clean_data = array();
$clean_formats = array();
$i = 0;
foreach ( $reward_order_data as $key => $value ) {
if ( null !== $value ) {
$clean_data[ $key ] = $value;
$clean_formats[ $i ] = $data_formats[ $i ];
}
$i++;
}
$result = $wpdb->insert( $table_name, $clean_data, $clean_formats );
if ( false === $result ) {
return false;
}
$reward_order_id = $wpdb->insert_id;
// Store reference in order meta.
update_post_meta( $order->get_id(), '_ecre_reward_order_id', $reward_order_id );
return $reward_order_id;
}
/**
* Update reward order status in the database for a given order.
*
* @since 1.0.0
*
* @param int $order_id WooCommerce order ID.
* @param string $reward_status New reward status (e.g. 'completed', 'cancelled').
*
* @return bool True on success, false on failure.
*/
public function update_reward_order_status( $order_id, $reward_status ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_rewards_orders';
// Bail if table does not exist.
$table_exists = $wpdb->get_var(
$wpdb->prepare(
'SHOW TABLES LIKE %s',
$wpdb->esc_like( $table_name )
)
);
if ( $table_exists !== $table_name ) {
return false;
}
// Update reward order status and updated_at timestamp.
$result = $wpdb->update(
$table_name,
array(
'reward_status' => sanitize_text_field( $reward_status ),
'updated_at' => current_time( 'mysql' ),
),
array( 'order_id' => (int) $order_id ),
array( '%s', '%s' ),
array( '%d' )
);
if ( false === $result ) {
return false;
}
return true;
}
/**
* Add reward coupon record when a coupon is generated
*
* @param array $data Coupon data.
* @return int|false Insert ID or false on failure
*/
public function store_reward_coupon_data( $data ) {
global $wpdb;
$table_reward_coupons = $wpdb->prefix . 'ecre_reward_coupons';
$defaults = array(
'referrer_user_id' => 0,
'order_id' => 0,
'coupon_code' => '',
'coupon_id' => null,
'discount_type' => 'fixed_cart',
'discount_amount' => 0.00,
'usage_limit' => 1,
'usage_count' => 0,
'status' => 'active',
'expires_at' => null,
'include_products' => null,
'exclude_products' => null,
'include_product_categories' => null,
'exclude_product_categories' => null,
);
$data = wp_parse_args( $data, $defaults );
// FAST CHECK: Already in our tracking table?
$existing = $wpdb->get_var(
$wpdb->prepare(
"SELECT id FROM `{$wpdb->prefix}ecre_reward_coupons` WHERE coupon_code = %s LIMIT 1",
$data['coupon_code']
)
);
if ( $existing ) {
return false; // Already tracked, skip.
}
// Prepare insert data with new column names.
$insert_data = array(
'referrer_user_id' => $data['referrer_user_id'],
'order_id' => $data['order_id'],
'coupon_code' => $data['coupon_code'],
'coupon_id' => $data['coupon_id'],
'discount_type' => $data['discount_type'],
'discount_amount' => $data['discount_amount'],
'usage_limit' => $data['usage_limit'],
'usage_count' => $data['usage_count'],
'status' => $data['status'],
'expires_at' => $data['expires_at'],
);
// Handle old column names for backward compatibility.
if ( isset( $data['product_restrictions'] ) && ! empty( $data['product_restrictions'] ) ) {
$product_restrictions = is_array( $data['product_restrictions'] )
? wp_json_encode( $data['product_restrictions'] )
: $data['product_restrictions'];
$insert_data['include_products'] = $product_restrictions;
} elseif ( isset( $data['include_products'] ) && ! empty( $data['include_products'] ) ) {
$include_products = is_array( $data['include_products'] )
? wp_json_encode( $data['include_products'] )
: $data['include_products'];
$insert_data['include_products'] = $include_products;
}
if ( isset( $data['exclude_products'] ) && ! empty( $data['exclude_products'] ) ) {
$exclude_products = is_array( $data['exclude_products'] )
? wp_json_encode( $data['exclude_products'] )
: $data['exclude_products'];
$insert_data['exclude_products'] = $exclude_products;
}
if ( isset( $data['category_restrictions'] ) && ! empty( $data['category_restrictions'] ) ) {
$category_restrictions = is_array( $data['category_restrictions'] )
? wp_json_encode( $data['category_restrictions'] )
: $data['category_restrictions'];
$insert_data['include_product_categories'] = $category_restrictions;
} elseif ( isset( $data['include_product_categories'] ) && ! empty( $data['include_product_categories'] ) ) {
$include_product_categories = is_array( $data['include_product_categories'] )
? wp_json_encode( $data['include_product_categories'] )
: $data['include_product_categories'];
$insert_data['include_product_categories'] = $include_product_categories;
}
if ( isset( $data['exclude_product_categories'] ) && ! empty( $data['exclude_product_categories'] ) ) {
$exclude_product_categories = is_array( $data['exclude_product_categories'] )
? wp_json_encode( $data['exclude_product_categories'] )
: $data['exclude_product_categories'];
$insert_data['exclude_product_categories'] = $exclude_product_categories;
}
$result = $wpdb->insert( $table_reward_coupons, $insert_data );
return $result ? $wpdb->insert_id : false;
}
/**
* Sync reward coupon usage data from WooCommerce to the ecre_reward_coupons table.
*
* This function syncs the usage_count and usage_limit from WooCommerce coupon post meta
* to our custom table when a reward coupon is used in an order.
*
* @since 1.0.0
*
* @param int $coupon_id The WooCommerce coupon ID.
* @return bool True on success, false on failure.
*/
public function sync_reward_coupon_usage( $coupon_id ) {
global $wpdb;
$table_reward_coupons = $wpdb->prefix . 'ecre_reward_coupons';
// Get actual usage count and limit from WooCommerce coupon post meta
$usage_count = (int) get_post_meta( $coupon_id, 'usage_count', true );
$usage_limit = (int) get_post_meta( $coupon_id, 'usage_limit', true );
// Sync the values to our custom table
$result = $wpdb->query(
$wpdb->prepare(
"UPDATE {$table_reward_coupons}
SET usage_count = %d,
usage_limit = %d
WHERE coupon_id = %d",
$usage_count,
$usage_limit,
$coupon_id
)
);
return $result !== false;
}
/**
* Stores the referrer information for a user, including optional coupon details.
*
* This function checks if a referrer entry already exists for the given user. If it does, it updates the record.
* If no entry exists, a new referrer record is created. Optionally, the function can store a coupon code and coupon ID
* for the referral.
*
* @param int $user_id The ID of the user being referred.
* @param string $coupon_code The coupon code associated with the referral, if any. Default is an empty string.
* @param int $coupon_id The ID of the coupon, if provided. Default is null.
*
* @return bool Returns true if the referrer was successfully stored or updated, false otherwise.
*/
public function store_referrer( $user_id, $coupon_code = '', $coupon_id = null ) {
global $wpdb;
$table_referrers = $wpdb->prefix . 'ecre_referrers';
// Check if referrer already exists.
$exists = $wpdb->get_var(
$wpdb->prepare(
"SELECT id FROM `{$wpdb->prefix}ecre_referrers` WHERE user_id = %d",
$user_id
)
);
if ( $exists ) {
// Update existing referrer with coupon information if provided.
if ( ! empty( $coupon_code ) && ! empty( $coupon_id ) ) {
$wpdb->update(
$table_referrers,
array(
'referral_coupon_code' => $coupon_code,
'referral_coupon_id' => $coupon_id,
'updated_at' => current_time( 'mysql' ),
),
array( 'user_id' => $user_id )
);
}
return true;
}
// Insert new referrer with zero totals, coupon information, and new columns.
$referrer_data = array(
'user_id' => $user_id,
'referral_coupon_code' => ! empty( $coupon_code ) ? $coupon_code : null,
'referral_coupon_id' => ! empty( $coupon_id ) ? $coupon_id : null,
'total_referrals' => 0,
'total_sales' => 0.00,
'total_rewards_issued' => 0,
'total_discount' => 0.00,
'conversion_rate' => 0.00,
'custom_rewards' => 0,
'custom_referrals' => 0,
'first_referral_date' => null,
'last_referral_date' => null,
);
$result = $wpdb->insert( $table_referrers, $referrer_data );
// Store the referrer ID for potential use in referral records.
if ( $result && ! empty( $coupon_code ) && ! empty( $coupon_id ) ) {
$referrer_id = $wpdb->insert_id;
// Store coupon information in user meta for easy access (for backward compatibility).
update_user_meta( $user_id, 'ecre_referral_coupon_id', $coupon_id );
update_user_meta( $user_id, 'ecre_referrer_id', $referrer_id );
}
return false !== $result;
}
/**
* Updates the referrer statistics when a referral order is placed.
*
* This function updates the referral statistics for the referrer, including the total number of referrals,
* total sales, total discounts, the first and last referral dates, and the updated timestamp. It also recalculates
* the referrer's conversion rate after the stats are updated.
*
* The referrer data is fetched from the `ecre_referrers` table using the provided referral user ID. If an existing
* record for the referrer is found, the statistics are updated accordingly. If no record is found, no changes are made.
*
* @since 1.0.0
*
* @param \WC_Order $order WooCommerce order object for the referral order.
* @param string $coupon_code The coupon code used in the referral order (if any).
* @param int $coupon_id The ID of the coupon used in the referral order (if any).
* @param int $referral_user_id The ID of the user who referred the order.
*
* @return void
*/
public function update_referrer_stats_on_order( $order, $coupon_code, $coupon_id, $referral_user_id ) {
global $wpdb;
$table_referrers = $wpdb->prefix . 'ecre_referrers';
// Calculate order totals.
$order_total = (float) $order->get_total();
$discount_total = (float) $order->get_discount_total();
// Current timestamp for last referral date.
$current_date = current_time( 'mysql' );
// Get existing record for this user.
$referrer = $wpdb->get_row(
$wpdb->prepare(
"SELECT id, total_referrals, total_sales, total_discount, first_referral_date
FROM `{$wpdb->prefix}ecre_referrers` WHERE user_id = %d",
$referral_user_id
)
);
if ( $referrer ) {
// First referral date stays if already set, otherwise use now.
$first_date = ! empty( $referrer->first_referral_date ) ? $referrer->first_referral_date : $current_date;
// Update stats.
$wpdb->query(
$wpdb->prepare(
"UPDATE `{$wpdb->prefix}ecre_referrers`
SET total_referrals = total_referrals + 1,
total_sales = total_sales + %f,
total_discount = total_discount + %f,
first_referral_date = %s,
last_referral_date = %s,
updated_at = %s
WHERE user_id = %d",
$order_total,
$discount_total,
$first_date,
$current_date,
$current_date,
$referral_user_id
)
);
}
// Recalculate conversion rate using the new helper function.
$this->recalculate_referrer_conversion_rate( $referral_user_id );
}
/**
* Updates the referrer's reward count upon the completion of a referral order.
*
* This function increments the total rewards issued for a given referral user by 1 and updates the
* timestamp of the last update in the `ecre_referrers` table. After updating the rewards, the function
* recalculates the referrer's conversion rate based on the new data.
*
* @since 1.0.0
*
* @param int $referral_user_id The ID of the user who referred the order and is receiving the reward.
*
* @return void
*/
public function update_referrer_rewards_on_completion( $referral_user_id ) {
global $wpdb;
$table_referrers = $wpdb->prefix . 'ecre_referrers';
$wpdb->query(
$wpdb->prepare(
"UPDATE `{$wpdb->prefix}ecre_referrers`
SET total_rewards_issued = total_rewards_issued + 1,
updated_at = %s
WHERE user_id = %d",
current_time( 'mysql' ),
$referral_user_id
)
);
$this->recalculate_referrer_conversion_rate( $referral_user_id );
}
/**
* Updates the dashboard summary statistics upon the completion of an order.
*
* This function updates the summary statistics for the referrer’s dashboard by incrementing the total
* number of referrals, updating the total referral sales, total discount given, and the timestamp for the
* last update. The statistics are updated in the `ecre_referrars_dashboard_summary` table under the 'all_time' period.
* After updating the stats, the function recalculates the dashboard’s average conversion rate.
*
* @since 1.0.0
*
* @param \WC_Order $order WooCommerce order object for the referral order.
* @param string $coupon_code The coupon code used in the referral order (if any).
* @param int $coupon_id The ID of the coupon used in the referral order (if any).
* @param int $referral_user_id The ID of the user who referred the order.
*
* @return void
*/
public function update_dashboard_summary_stats_on_order( $order, $coupon_code, $coupon_id, $referral_user_id ) {
global $wpdb;
$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';
// Calculate order totals.
$order_total = (float) $order->get_total();
$discount_total = (float) $order->get_discount_total();
// Current timestamp for last update.
$current_date = current_time( 'mysql' );
// Update dashboard summary stats.
$wpdb->query(
$wpdb->prepare(
"UPDATE `{$wpdb->prefix}ecre_referrars_dashboard_summary`
SET total_referrals = total_referrals + 1,
total_referral_sales = total_referral_sales + %f,
total_discount_given = total_discount_given + %f,
last_updated = %s
WHERE summary_period = 'all_time'",
$order_total,
$discount_total,
$current_date
)
);
$this->recalculate_dashboard_average_conversion_rate();
}
/**
* Updates the dashboard summary rewards when a referral reward is issued.
*
* This function increments the total rewards issued by 1 in the `ecre_referrars_dashboard_summary` table
* for the 'all_time' period. It also updates the timestamp for the last update and recalculates the average
* conversion rate for the dashboard.
*
* @since 1.0.0
*
* @param int $referral_user_id The ID of the user who referred the order and is receiving the reward.
*
* @return void
*/
public function update_dashboard_summary_rewards_on_completion( $referral_user_id ) {
global $wpdb;
$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';
// Increase total_rewards_issued by 1.
$wpdb->query(
$wpdb->prepare(
"UPDATE `{$wpdb->prefix}ecre_referrars_dashboard_summary`
SET total_rewards_issued = total_rewards_issued + 1,
last_updated = %s
WHERE summary_period = 'all_time'",
current_time( 'mysql' )
)
);
// Recalculate average conversion rate.
$this->recalculate_dashboard_average_conversion_rate();
}
/**
* Recalculates the average conversion rate for the dashboard.
*
* This function calculates the average conversion rate by dividing the total rewards issued by the total
* referrals, and then multiplying by 100 to get a percentage. The result is rounded to two decimal places.
* If there are no referrals, the conversion rate is set to 0 to avoid division by zero. After recalculating,
* the average conversion rate is updated in the `ecre_referrars_dashboard_summary` table.
*
* @since 1.0.0
*
* @return void
*/
public function recalculate_dashboard_average_conversion_rate() {
global $wpdb;
$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';
$stats = $wpdb->get_row(
"SELECT total_referrals, total_rewards_issued
FROM `{$wpdb->prefix}ecre_referrars_dashboard_summary`
WHERE summary_period = 'all_time'"
);
if ( $stats && $stats->total_referrals > 0 ) {
$average_conversion_rate = round( ( $stats->total_rewards_issued / $stats->total_referrals ) * 100, 2 );
} else {
$average_conversion_rate = 0.00; // Avoid division by zero.
}
$wpdb->update(
$dashboard_summary_table,
array(
'average_conversion_rate' => $average_conversion_rate,
'last_updated' => current_time( 'mysql' ),
),
array( 'summary_period' => 'all_time' )
);
}
/**
* Recalculates the conversion rate for a specific referrer.
*
* This function calculates the conversion rate for a given referrer by dividing the total rewards issued
* by the total referrals, and multiplying the result by 100 to get the percentage. If there are no referrals,
* the conversion rate is set to 0 to avoid division by zero. The calculated conversion rate is then updated
* in the `ecre_referrers` table for the given referrer.
*
* @since 1.0.0
*
* @param int $referral_user_id The ID of the referrer whose conversion rate is being recalculated.
*
* @return void
*/
public function recalculate_referrer_conversion_rate( $referral_user_id ) {
global $wpdb;
$table_referrers = $wpdb->prefix . 'ecre_referrers';
$stats = $wpdb->get_row(
$wpdb->prepare(
"SELECT total_referrals, total_rewards_issued
FROM `{$wpdb->prefix}ecre_referrers`
WHERE user_id = %d",
$referral_user_id
)
);
if ( $stats && $stats->total_referrals > 0 ) {
$conversion_rate = round( ( $stats->total_rewards_issued / $stats->total_referrals ) * 100, 2 );
} else {
$conversion_rate = 0.00; // avoid division by zero.
}
$wpdb->update(
$table_referrers,
array(
'conversion_rate' => $conversion_rate,
'updated_at' => current_time( 'mysql' ),
),
array( 'user_id' => $referral_user_id )
);
}
/**
* Increments the total referrers count in the dashboard summary upon referrer creation.
*
* This function increases the `total_referrers` value by 1 in the `ecre_referrars_dashboard_summary`
* table for the 'all_time' summary period. It also updates the timestamp for the last update.
*
* @since 1.0.0
*
* @param int $user_id The ID of the newly created referrer user.
*
* @return void
*/
public function update_dashboard_summary_referrers_on_creation( $user_id ) {
global $wpdb;
$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';
// Increase total_referrers by 1.
$wpdb->query(
$wpdb->prepare(
"UPDATE `{$wpdb->prefix}ecre_referrars_dashboard_summary`
SET total_referrers = total_referrers + 1,
last_updated = %s
WHERE summary_period = 'all_time'",
current_time( 'mysql' )
)
);
}
/**
* Retrieves a paginated list of referrers with optional filters and sorting.
*
* This method fetches a list of referrers from the database, allowing for pagination, search, and filtering
* by time period and custom rewards or referrals. The results include details such as the referrer's total
* referrals, total sales, rewards issued, conversion rate, and more. Pagination details are also returned
* to help with displaying results in a paginated format.
*
* @since 1.0.0
*
* @param int $page The current page number for pagination (defaults to 1).
* @param int $per_page The number of results per page (defaults to 10).
* @param string $search Search term to filter referrers by their name or email (optional).
* @param string $time_filter The time period to filter results by (e.g., 'All Time', 'Last 30 days').
* @param bool $custom_rewards_filter Whether to filter results by referrers with custom rewards (optional).
* @param bool $custom_referrals_filter Whether to filter results by referrers with custom referrals (optional).
*
* @return array An array containing the filtered referrers' data and pagination information.
* The array includes 'data' (the list of referrers) and 'pagination' (pagination details).
*/
public function get_referrers_with_pagination( $page = 1, $per_page = 10, $search = '', $time_filter = 'All Time', $custom_rewards_filter = false, $custom_referrals_filter = false ) {
global $wpdb;
$offset = ( $page - 1 ) * $per_page;
// Build the base query with ALL columns from referrers table.
$referrers_table = $wpdb->prefix . 'ecre_referrers';
$users_table = $wpdb->users;
$base_query = "
SELECT
r.id,
r.user_id,
r.referral_coupon_code,
r.referral_coupon_id,
r.total_referrals,
r.total_sales,
r.total_rewards_issued,
r.total_discount,
r.conversion_rate,
r.custom_rewards,
r.custom_referrals,
r.first_referral_date,
r.last_referral_date,
r.created_at,
r.updated_at,
u.display_name as name,
u.user_email as email
FROM {$referrers_table} r
LEFT JOIN {$users_table} u ON r.user_id = u.ID
";
// Build WHERE conditions.
$where_conditions = array( '1=1' );
$where_params = array();
// Search functionality.
if ( ! empty( $search ) ) {
$search_term = '%' . $wpdb->esc_like( $search ) . '%';
$where_conditions[] = '(u.display_name LIKE %s OR u.user_email LIKE %s)';
$where_params[] = $search_term;
$where_params[] = $search_term;
}
// Time filter.
if ( 'All Time' !== $time_filter ) {
$date_condition = $this->get_time_filter_date_condition( $time_filter );
if ( $date_condition ) {
$where_conditions[] = 'r.updated_at >= %s';
$where_params[] = $date_condition;
}
}
// Custom rewards filter.
if ( $custom_rewards_filter ) {
$where_conditions[] = 'r.custom_rewards = 1';
}
// Custom referrals filter.
if ( $custom_referrals_filter ) {
$where_conditions[] = 'r.custom_referrals = 1';
}
// Combine WHERE conditions.
$where_clause = implode( ' AND ', $where_conditions );
// Count total items (for pagination).
$count_query = "
SELECT COUNT(*)
FROM {$referrers_table} r
LEFT JOIN {$users_table} u ON r.user_id = u.ID
WHERE {$where_clause}
";
if ( ! empty( $where_params ) ) {
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$total_items = $wpdb->get_var( $wpdb->prepare( $count_query, $where_params ) );
} else {
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$total_items = $wpdb->get_var( $count_query );
}
// Build final query with pagination.
$final_query = "
{$base_query}
WHERE {$where_clause}
ORDER BY r.total_sales DESC, r.total_referrals DESC
LIMIT %d OFFSET %d
";
// Add pagination parameters to params array.
$final_params = array_merge( $where_params, array( $per_page, $offset ) );
// Execute query.
if ( ! empty( $where_params ) ) {
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$results = $wpdb->get_results( $wpdb->prepare( $final_query, $final_params ), ARRAY_A );
} else {
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$results = $wpdb->get_results( $wpdb->prepare( $final_query, array( $per_page, $offset ) ), ARRAY_A );
}
// Process results - include ALL database columns.
$processed_results = array();
foreach ( $results as $row ) {
$processed_results[] = array(
'id' => $row['id'],
'user_id' => $row['user_id'],
'name' => isset( $row['name'] ) ? $row['name'] : 'Unknown User',
'email' => $row['email'],
'referrals' => intval( $row['total_referrals'] ),
'salesGenerated' => number_format( (float) $row['total_sales'], 2 ),
'avatar' => get_avatar_url( $row['user_id'] ),
'hasCustomRewards' => (bool) $row['custom_rewards'],
'hasCustomReferrals' => (bool) $row['custom_referrals'],
'joinDate' => $row['created_at'],
'lastActivity' => $row['updated_at'],
// Add all database columns.
'referral_coupon_code' => $row['referral_coupon_code'],
'referral_coupon_id' => $row['referral_coupon_id'],
'total_referrals' => intval( $row['total_referrals'] ),
'total_sales' => floatval( $row['total_sales'] ),
'total_rewards_issued' => intval( $row['total_rewards_issued'] ),
'total_discount' => floatval( $row['total_discount'] ),
'conversion_rate' => floatval( $row['conversion_rate'] ),
'first_referral_date' => $row['first_referral_date'],
'last_referral_date' => $row['last_referral_date'],
);
}
// Calculate pagination info.
$total_pages = ceil( $total_items / $per_page );
$has_more = $page < $total_pages;
$pagination = array(
'current_page' => $page,
'per_page' => $per_page,
'total_items' => intval( $total_items ),
'total_pages' => $total_pages,
'has_more' => $has_more,
);
return array(
'data' => $processed_results,
'pagination' => $pagination,
);
}
/**
* Retrieves the dashboard summary data for the 'all_time' period.
*
* This method fetches the summary data from the `ecre_referrars_dashboard_summary` table, including total
* referrers, total referrals, total referral sales, and total rewards issued. It retrieves the most recent
* summary based on the last update timestamp. If no data is available, it falls back to calculating the summary
* using a custom fallback method.
*
* @since 1.0.0
*
* @return array An associative array containing the dashboard summary data:
* - `total_referrers`: Total number of referrers.
* - `total_referrals`: Total number of referrals.
* - `total_referral_sales`: Total sales generated from referrals, formatted to two decimal places.
* - `total_rewards_issued`: Total rewards issued to referrers.
*/
public function get_dashboard_summary() {
global $wpdb;
$dashboard_summary_table = $wpdb->prefix . 'ecre_referrars_dashboard_summary';
// Get summary from the dashboard summary table.
$summary = $wpdb->get_row(
"SELECT * FROM `{$wpdb->prefix}ecre_referrars_dashboard_summary` WHERE summary_period = 'all_time' ORDER BY last_updated DESC LIMIT 1",
ARRAY_A
);
if ( $summary ) {
return array(
'total_referrers' => intval( $summary['total_referrers'] ),
'total_referrals' => intval( $summary['total_referrals'] ),
'total_referral_sales' => number_format( (float) $summary['total_referral_sales'], 2 ),
'total_rewards_issued' => intval( $summary['total_rewards_issued'] ),
);
} else {
// Fallback to calculate if not cached.
return $this->calculate_dashboard_summary_fallback();
}
}
/**
* Get dashboard summary for a specific referrer by user_id
*
* @param int $user_id The user ID of the referrer.
* @return array|null Referrer dashboard data or null if not found
*/
public function get_referrer_dashboard_summary( $user_id ) {
global $wpdb;
$referrer = $wpdb->get_row(
$wpdb->prepare(
"SELECT
r.id,
r.user_id,
r.referral_coupon_code,
r.referral_coupon_id,
r.total_referrals,
r.total_sales,
r.total_rewards_issued,
r.total_discount,
r.conversion_rate,
r.custom_rewards,
r.custom_referrals,
r.first_referral_date,
r.last_referral_date,
r.created_at,
r.updated_at,
u.display_name as name,
u.user_email as email
FROM {$wpdb->prefix}ecre_referrers r
LEFT JOIN {$wpdb->users} u ON r.user_id = u.ID
WHERE r.user_id = %d",
$user_id
),
ARRAY_A
);
if ( ! $referrer ) {
return null;
}
// Format the data to match expected types.
$referrer['total_sales'] = floatval( $referrer['total_sales'] );
$referrer['total_referrals'] = intval( $referrer['total_referrals'] );
$referrer['total_rewards_issued'] = intval( $referrer['total_rewards_issued'] );
$referrer['total_discount'] = floatval( $referrer['total_discount'] );
$referrer['conversion_rate'] = floatval( $referrer['conversion_rate'] );
$referrer['custom_rewards'] = intval( $referrer['custom_rewards'] );
$referrer['custom_referrals'] = intval( $referrer['custom_referrals'] );
// Add avatar (WordPress doesn't store it in DB, use get_avatar_url).
$referrer['avatar'] = get_avatar_url( $user_id );
return $referrer;
}
/**
* Helper function to convert a time filter into an SQL date condition.
*
* This function takes a time filter string (e.g., '1 Month', '3 Month', '1 Year') and converts it into a
* valid SQL date condition, which can be used in database queries. The function calculates the appropriate
* date based on the current time and returns the date in the format 'Y-m-d H:i:s'. If the time filter is not
* recognized, the function returns `null`.
*
* @param string $time_filter The time filter to be converted (e.g., '1 Month', '3 Month', '1 Year').
*
* @return string|null The SQL date condition or `null` if the time filter is not recognized.
*/
private function get_time_filter_date_condition( $time_filter ) {
$current_date = current_time( 'mysql' );
switch ( $time_filter ) {
case '1 Month':
return gmdate( 'Y-m-d H:i:s', strtotime( '-1 month', strtotime( $current_date ) ) );
case '3 Month':
return gmdate( 'Y-m-d H:i:s', strtotime( '-3 months', strtotime( $current_date ) ) );
case '6 Month':
return gmdate( 'Y-m-d H:i:s', strtotime( '-6 months', strtotime( $current_date ) ) );
case '1 Year':
return gmdate( 'Y-m-d H:i:s', strtotime( '-1 year', strtotime( $current_date ) ) );
default:
return null;
}
}
/**
* Calculate the dashboard summary as a fallback if cached data is unavailable.
*
* This function calculates the total number of referrers, total referrals, total sales, and total rewards
* issued from the database. It uses the `ecre_referrers` and `ecre_rewards_orders` tables to gather the
* necessary data and returns it in a structured array. The function serves as a fallback when no cached
* dashboard summary is found.
*
* @return array An associative array containing the calculated dashboard summary:
* - `total_referrers`: Total number of referrers.
* - `total_referrals`: Total number of referrals.
* - `total_referral_sales`: Total sales generated from referrals, formatted to two decimal places.
* - `total_rewards_issued`: Total rewards issued, or 0 if no rewards are found.
*/
private function calculate_dashboard_summary_fallback() {
global $wpdb;
$referrers_table = $wpdb->prefix . 'ecre_referrers';
$total_referrers = $wpdb->get_var( "SELECT COUNT(*) FROM `{$wpdb->prefix}ecre_referrers`" );
$totals = $wpdb->get_row(
"SELECT
COALESCE(SUM(total_referrals), 0) as total_referrals,
COALESCE(SUM(total_sales), 0) as total_sales
FROM `{$wpdb->prefix}ecre_referrers`",
ARRAY_A
);
$rewards_table = $wpdb->prefix . 'ecre_rewards_orders';
$total_rewards = $wpdb->get_var( "SELECT COUNT(*) FROM `{$wpdb->prefix}ecre_rewards_orders`" );
return array(
'total_referrers' => intval( $total_referrers ),
'total_referrals' => intval( $totals['total_referrals'] ),
'total_referral_sales' => number_format( (float) $totals['total_sales'], 2 ),
'total_rewards_issued' => intval( $total_rewards ? $total_rewards : 0 ),
);
}
/**
* Retrieves a paginated history of referrals for a given referrer with optional time-based filtering.
*
* This function retrieves a list of referral records associated with a specific referrer user ID, supporting
* pagination and optional time-based filters (e.g., "1 Month", "3 Months", "1 Year"). The data includes detailed
* The results are formatted for front-end display and include pagination details for easy navigation.
*
* @since 1.0.0
*
* @param int $referrer_user_id The ID of the referrer for whom the history is being retrieved.
* @param int $page The page number for pagination (defaults to 1).
* @param int $per_page The number of results per page (defaults to 10).
* @param string $time_filter The time period filter (e.g., 'All Time', '1 Month', '3 Months', '1 Year').
*
* @return array An associative array containing:
* - `referrals`: A list of referral data for the given referrer.
* - `pagination`: Pagination information including the current page, items per page, total items,
* total pages, and whether there are more pages.
*/
public function get_referrer_history_with_pagination( $referrer_user_id, $page = 1, $per_page = 10, $time_filter = 'All Time' ) {
global $wpdb;
$referrals_table = $wpdb->prefix . 'ecre_referrals_orders';
// Build time filter condition.
$time_condition = '';
$time_params = array();
if ( 'All Time' !== $time_filter ) {
switch ( $time_filter ) {
case '1 Month':
$time_condition = ' AND order_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-1 month' ) );
break;
case '3 Months':
$time_condition = ' AND order_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-3 months' ) );
break;
case '6 Months':
$time_condition = ' AND order_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-6 months' ) );
break;
case '1 Year':
$time_condition = ' AND order_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-1 year' ) );
break;
}
}
// Get total count.
$count_sql = "SELECT COUNT(*) FROM {$referrals_table} WHERE referrer_user_id = %d" . $time_condition;
$count_params = array_merge( array( $referrer_user_id ), $time_params );
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$total_items = $wpdb->get_var( $wpdb->prepare( $count_sql, $count_params ) );
// Calculate pagination.
$total_pages = ceil( $total_items / $per_page );
$offset = ( $page - 1 ) * $per_page;
// Get referral history data with ALL columns.
$sql = "SELECT
ro.id,
ro.referrer_user_id,
ro.referred_user_id,
ro.order_id,
ro.coupon_code,
ro.coupon_id,
ro.order_value,
ro.discount_amount,
ro.referral_status,
ro.firstname,
ro.lastname,
ro.fullname,
ro.customer_email,
ro.order_date,
ro.created_at,
ro.updated_at
FROM {$referrals_table} ro
WHERE ro.referrer_user_id = %d" . $time_condition . "
ORDER BY ro.order_date DESC
LIMIT %d OFFSET %d";
$sql_params = array_merge(
array( $referrer_user_id ),
$time_params,
array( $per_page, $offset )
);
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$referrals = $wpdb->get_results( $wpdb->prepare( $sql, $sql_params ), ARRAY_A );
// Format the data with correct field names for frontend.
$formatted_referrals = array();
foreach ( $referrals as $referral ) {
$formatted_referrals[] = array(
'id' => $referral['id'],
'order_id' => $referral['order_id'],
'customer_email' => $referral['customer_email'],
'order_value' => number_format( (float) $referral['order_value'], 2 ),
'discount_amount' => number_format( (float) $referral['discount_amount'], 2 ),
'date' => gmdate( 'j M, Y', strtotime( $referral['order_date'] ) ),
'status' => ucfirst( $referral['referral_status'] ),
'coupon_code' => $referral['coupon_code'],
// Include all database fields for future use.
'referrer_user_id' => $referral['referrer_user_id'],
'referred_user_id' => $referral['referred_user_id'],
'coupon_id' => $referral['coupon_id'],
'firstname' => $referral['firstname'],
'lastname' => $referral['lastname'],
'fullname' => ! empty( $referral['fullname'] ) ? $referral['fullname'] : 'Guest',
'order_date' => $referral['order_date'],
'created_at' => $referral['created_at'],
'updated_at' => $referral['updated_at'],
);
}
return array(
'referrals' => $formatted_referrals,
'pagination' => array(
'current_page' => $page,
'per_page' => $per_page,
'total_items' => (int) $total_items,
'total_pages' => $total_pages,
'has_more' => $page < $total_pages,
),
);
}
/**
* Retrieves a paginated list of reward coupons for a specific referrer with optional time-based filtering.
*
* This function fetches reward coupons associated with a given referrer user ID, allowing for pagination and
* time-based filters (e.g., '1 Month', '3 Months', '1 Year'). The results include coupon details such as
* coupon code, discount type, usage limit, and current status. It implements cursor-based pagination for better
* performance with large datasets, especially for deep pagination beyond page 100. The function also caches the
* total item count for faster retrieval on subsequent requests.
*
* @since 1.0.0
*
* @param int $referrer_user_id The ID of the referrer whose reward coupons are being retrieved.
* @param int $page The current page number for pagination (defaults to 1).
* @param int $per_page The number of results per page (defaults to 10).
* @param string $time_filter The time filter for the coupons (e.g., 'All Time', '1 Month', '3 Months').
*
* @return array An associative array containing:
* - `reward_coupons`: A list of reward coupon details.
* - `pagination`: Pagination information, including the current page, items per page,
* total items, total pages, and whether there are more pages.
*/
public function get_referrer_reward_coupons_with_pagination( $referrer_user_id, $page = 1, $per_page = 10, $time_filter = 'All Time' ) {
global $wpdb;
$reward_coupons_table = $wpdb->prefix . 'ecre_reward_coupons';
// Build time filter condition based on created_at.
$time_condition = '';
$time_params = array();
if ( 'All Time' !== $time_filter ) {
switch ( $time_filter ) {
case '1 Month':
$time_condition = ' AND rc.created_at >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-1 month' ) );
break;
case '3 Months':
$time_condition = ' AND rc.created_at >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-3 months' ) );
break;
case '6 Months':
$time_condition = ' AND rc.created_at >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-6 months' ) );
break;
case '1 Year':
$time_condition = ' AND rc.created_at >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-1 year' ) );
break;
}
}
// Cache key for count query (5 minutes cache).
$cache_key = 'ecre_coupon_count_' . $referrer_user_id . '_' . md5( $time_filter );
$total_items = wp_cache_get( $cache_key, 'ecre_reward_coupons' );
if ( false === $total_items ) {
// Get total count with timeout protection.
$count_sql = "SELECT COUNT(*) FROM {$reward_coupons_table} rc WHERE rc.referrer_user_id = %d" . $time_condition;
$count_params = array_merge( array( $referrer_user_id ), $time_params );
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$total_items = $wpdb->get_var( $wpdb->prepare( $count_sql, $count_params ) );
// Cache for 5 minutes.
wp_cache_set( $cache_key, $total_items, 'ecre_reward_coupons', 300 );
}
// Calculate pagination.
$total_pages = ceil( $total_items / $per_page );
$offset = ( $page - 1 ) * $per_page;
// For deep pagination, use cursor-based pagination instead of OFFSET.
$order_condition = 'ORDER BY rc.created_at DESC, rc.id DESC';
if ( $page > 100 ) { // Switch to cursor for deep pagination.
// Get the last item from previous page as cursor.
$cursor_offset = ( $page - 1 ) * $per_page - 1;
$cursor_sql = "SELECT rc.created_at, rc.id FROM {$reward_coupons_table} rc
WHERE rc.referrer_user_id = %d" . $time_condition . "
ORDER BY rc.created_at DESC, rc.id DESC
LIMIT 1 OFFSET %d";
$cursor_params = array_merge( array( $referrer_user_id ), $time_params, array( $cursor_offset ) );
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$cursor = $wpdb->get_row( $wpdb->prepare( $cursor_sql, $cursor_params ) );
if ( $cursor ) {
$time_condition .= ' AND (rc.created_at < %s OR (rc.created_at = %s AND rc.id < %d))';
$time_params[] = $cursor->created_at;
$time_params[] = $cursor->created_at;
$time_params[] = $cursor->id;
$offset = 0; // No offset needed with cursor.
}
}
// Get reward coupons data - only fetch essential fields first.
$sql = "SELECT
rc.id,
rc.referrer_user_id,
rc.order_id,
rc.coupon_code,
rc.coupon_id,
rc.discount_type,
rc.discount_amount,
rc.usage_limit,
rc.usage_count,
rc.status,
rc.expires_at,
rc.created_at
FROM {$reward_coupons_table} rc
WHERE rc.referrer_user_id = %d" . $time_condition . "
{$order_condition}
LIMIT %d OFFSET %d";
$sql_params = array_merge(
array( $referrer_user_id ),
$time_params,
array( $per_page, $offset )
);
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$reward_coupons = $wpdb->get_results( $wpdb->prepare( $sql, $sql_params ), ARRAY_A );
// Format the data for frontend consumption.
$formatted_coupons = array();
foreach ( $reward_coupons as $coupon ) {
// Format discount amount with currency symbol or percentage.
$discount_display = '';
if ( 'percent' === $coupon['discount_type'] ) {
$discount_display = number_format( (float) $coupon['discount_amount'], 0 ) . '%';
} else {
$discount_display = number_format( (float) $coupon['discount_amount'], 2 );
}
// Format usage limit display - synced from WooCommerce.
$usage_display = '';
if ( $coupon['usage_limit'] > 0 ) {
$usage_display = $coupon['usage_count'] . '/' . $coupon['usage_limit'];
} else {
$usage_display = $coupon['usage_count'] . '/∞';
}
// Use the actual discount_type from database.
$coupon_type = ucfirst( str_replace( '_', ' ', $coupon['discount_type'] ) );
// Determine current status - check if expired.
$current_status = $coupon['status'];
if ( $coupon['expires_at'] && strtotime( $coupon['expires_at'] ) < time() && 'active' === $coupon['status'] ) {
$current_status = 'expired';
}
// Map database status to display status.
$status_display = ucfirst( $current_status );
if ( 'active' === $current_status ) {
$status_display = 'Available';
} elseif ( 'used' === $current_status ) {
$status_display = 'Used';
} elseif ( 'expired' === $current_status ) {
$status_display = 'Expired';
} elseif ( 'disabled' === $current_status ) {
$status_display = 'Removed';
}
$formatted_coupons[] = array(
'id' => $coupon['id'],
'code' => strtoupper( $coupon['coupon_code'] ),
'issuedDate' => gmdate( 'j M, Y', strtotime( $coupon['created_at'] ) ),
'expiryDate' => $coupon['expires_at'] ? gmdate( 'j M, Y', strtotime( $coupon['expires_at'] ) ) : '--',
'amount' => $discount_display,
'usageLimit' => $usage_display,
'type' => $coupon_type,
'status' => $status_display,
// Include essential fields only.
'order_id' => $coupon['order_id'],
'coupon_id' => $coupon['coupon_id'],
'raw_status' => $coupon['status'],
);
}
return array(
'reward_coupons' => $formatted_coupons,
'pagination' => array(
'current_page' => $page,
'per_page' => $per_page,
'total_items' => (int) $total_items,
'total_pages' => $total_pages,
'has_more' => $page < $total_pages,
),
);
}
/**
* Retrieves a paginated list of reward points for a specific referrer with optional time-based filtering.
*
* This function fetches reward points associated with a given referrer user ID, supporting pagination and
* optional time-based filters (e.g., '1 Month', '3 Months', '1 Year'). The results include details such as
* the total points earned, the date they were earned, and a unique identifier. It calculates the total number
* of reward points and applies pagination, returning a formatted list of the results.
*
* @since 1.0.0
*
* @param int $referrer_user_id The ID of the referrer whose reward points are being retrieved.
* @param int $page The current page number for pagination (defaults to 1).
* @param int $per_page The number of results per page (defaults to 10).
* @param string $time_filter The time filter for the reward points (e.g., 'All Time', '1 Month', '3 Months').
*
* @return array An associative array containing:
* - `reward_points`: A list of reward point details including user email, points, and earned date.
* - `pagination`: Pagination information, including the current page, items per page,
* total items, total pages, and whether there are more pages.
*/
public function get_referrer_reward_points_with_pagination( $referrer_user_id, $page = 1, $per_page = 10, $time_filter = 'All Time' ) {
global $wpdb;
$reward_points_table = $wpdb->prefix . 'reward_points';
$time_condition = '';
$time_params = array();
if ( 'All Time' !== $time_filter ) {
switch ( $time_filter ) {
case '1 Month':
$time_condition = ' AND earned_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-1 month' ) );
break;
case '3 Months':
$time_condition = ' AND earned_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-3 months' ) );
break;
case '6 Months':
$time_condition = ' AND earned_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-6 months' ) );
break;
case '1 Year':
$time_condition = ' AND earned_date >= %s';
$time_params[] = gmdate( 'Y-m-d H:i:s', strtotime( '-1 year' ) );
break;
}
}
$count_sql = "SELECT COUNT(*) FROM {$reward_points_table} WHERE user_id = %d" . $time_condition;
$count_params = array_merge( array( $referrer_user_id ), $time_params );
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$total_items = $wpdb->get_var( $wpdb->prepare( $count_sql, $count_params ) );
// Calculate pagination.
$total_pages = ceil( $total_items / $per_page );
$offset = ( $page - 1 ) * $per_page;
// Get reward points data - same pattern as referral history.
$sql = "SELECT
id,
user_id,
email,
points,
earned_date,
unique_id,
order_id
FROM {$reward_points_table}
WHERE user_id = %d" . $time_condition . "
ORDER BY earned_date DESC
LIMIT %d OFFSET %d";
$sql_params = array_merge(
array( $referrer_user_id ),
$time_params,
array( $per_page, $offset )
);
// Using $wpdb->prepare() to safely prepare the dynamic query with parameters.
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$reward_points = $wpdb->get_results( $wpdb->prepare( $sql, $sql_params ), ARRAY_A );
// Format the data for frontend consumption - same pattern as others.
$formatted_points = array();
foreach ( $reward_points as $point ) {
$formatted_points[] = array(
'id' => $point['id'],
'user_id' => $point['user_id'],
'email' => $point['email'],
'points' => intval( $point['points'] ),
'earned_date' => gmdate( 'j M, Y', strtotime( $point['earned_date'] ) ),
'earned_date_raw' => $point['earned_date'],
'unique_id' => $point['unique_id'],
'order_id' => isset( $point['order_id'] ) ? intval( $point['order_id'] ) : null,
'points_display' => intval( $point['points'] ) . ' Points',
);
}
return array(
'reward_points' => $formatted_points,
'pagination' => array(
'current_page' => $page,
'per_page' => $per_page,
'total_items' => (int) $total_items,
'total_pages' => $total_pages,
'has_more' => $page < $total_pages,
),
);
}
/**
* Retrieves a user-specific reward setting or falls back to a global default value.
*
* This function checks if a specific reward setting exists for a given user in the `ecre_user_reward_settings`
* table. If the setting exists and is active, it converts the setting value according to its data type (e.g.,
* number, boolean, array). If no setting is found, it returns the provided global default value.
*
* @since 1.0.0
*
* @param int $user_id The ID of the user whose reward setting is being retrieved.
* @param string $setting_key The key of the reward setting to fetch.
* @param mixed $global_default The default value to return if the user-specific setting is not found.
*
* @return mixed The user-specific reward setting value or the global default if the setting is not found.
*/
public function get_user_reward_setting( $user_id, $setting_key, $global_default = null ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_user_reward_settings';
$setting = $wpdb->get_row(
$wpdb->prepare(
"SELECT setting_value, data_type FROM `{$wpdb->prefix}ecre_user_reward_settings`
WHERE user_id = %d AND setting_key = %s AND is_active = 1",
$user_id,
$setting_key
)
);
if ( $setting ) {
// Convert based on data type.
switch ( $setting->data_type ) {
case 'number':
return (float) $setting->setting_value;
case 'boolean':
return (bool) $setting->setting_value;
case 'array':
return json_decode( $setting->setting_value, true );
default:
return $setting->setting_value;
}
}
return $global_default;
}
/**
* Saves a user-specific reward setting to the database.
*
* This function inserts or updates a specific reward setting for a user in the `ecre_user_reward_settings` table.
* The setting value is saved with a specified data type (e.g., string, number, array). If the data type is 'array',
* the value is encoded as JSON before being saved. The setting is marked as active upon saving.
*
* @since 1.0.0
*
* @param int $user_id The ID of the user whose reward setting is being saved.
* @param string $setting_key The key of the reward setting to save.
* @param mixed $setting_value The value of the reward setting to save.
* @param string $data_type The data type of the setting (defaults to 'string'). Can be 'string', 'number', 'boolean', or 'array'.
*
* @return bool Returns true if the setting was successfully saved, false otherwise.
*/
public function save_user_reward_setting( $user_id, $setting_key, $setting_value, $data_type = 'string' ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_user_reward_settings';
// Convert value based on data type.
if ( 'array' === $data_type ) {
$setting_value = wp_json_encode( $setting_value );
}
$result = $wpdb->replace(
$table_name,
array(
'user_id' => $user_id,
'setting_key' => $setting_key,
'setting_value' => $setting_value,
'data_type' => $data_type,
'is_active' => 1,
),
array( '%d', '%s', '%s', '%s', '%d' )
);
return false !== $result;
}
/**
* Deletes a user-specific reward setting or all settings for the user.
*
* This function removes a specific reward setting for a user if the `setting_key` is provided. If no `setting_key`
* is provided, it deletes all reward settings for the user, effectively resetting them to the global default.
* The deletion is performed in the `ecre_user_reward_settings` table.
*
* @since 1.0.0
*
* @param int $user_id The ID of the user whose reward setting(s) are to be deleted.
* @param string $setting_key The key of the reward setting to delete (optional). If not provided, all settings for the user will be deleted.
*
* @return bool Returns true if the setting(s) were successfully deleted, false otherwise.
*/
public function delete_user_reward_setting( $user_id, $setting_key = null ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_user_reward_settings';
if ( $setting_key ) {
// Delete specific setting.
$result = $wpdb->delete(
$table_name,
array(
'user_id' => $user_id,
'setting_key' => $setting_key,
),
array( '%d', '%s' )
);
} else {
// Delete all settings for user.
$result = $wpdb->delete(
$table_name,
array( 'user_id' => $user_id ),
array( '%d' )
);
}
return false !== $result;
}
/**
* Retrieves all custom reward settings for a specific user.
*
* This function fetches all active custom reward settings for a given user from the `ecre_user_reward_settings`
* table. It processes each setting based on its data type (e.g., number, boolean, array) and returns the settings
* in an associative array with the setting keys as the array keys and the corresponding setting values.
* The values are converted according to their data type, such as converting numbers to floats or decoding arrays from JSON.
*
* @since 1.0.0
*
* @param int $user_id The ID of the user whose custom settings are being retrieved.
*
* @return array An associative array of custom settings where the key is the setting key,
* and the value is the corresponding setting value, properly converted.
*/
public function get_user_custom_settings( $user_id ) {
global $wpdb;
$table_name = $wpdb->prefix . 'ecre_user_reward_settings';
$settings = $wpdb->get_results(
$wpdb->prepare(
"SELECT setting_key, setting_value, data_type FROM `{$wpdb->prefix}ecre_user_reward_settings`
WHERE user_id = %d AND is_active = 1",
$user_id
),
ARRAY_A
);
$formatted_settings = array();
foreach ( $settings as $setting ) {
$value = $setting['setting_value'];
// Convert based on data type.
switch ( $setting['data_type'] ) {
case 'number':
$value = (float) $value;
break;
case 'boolean':
$value = (bool) $value;
break;
case 'array':
$value = json_decode( $value, true );
break;
}
$formatted_settings[ $setting['setting_key'] ] = $value;
}
return $formatted_settings;
}
/**
* Retrieves user settings merged with global defaults.
*
* This function combines the global default settings with the user's custom settings by fetching the user's custom
* settings from the `ecre_user_reward_settings` table. It then merges these user-specific settings with the provided
* global settings, with user-specific settings taking precedence for certain keys. Only customizable settings are merged,
* ensuring that the final returned settings reflect both the global defaults and any user-specific customizations.
*
* @since 1.0.0
*
* @param int $user_id The ID of the user whose custom settings are being retrieved.
* @param array $global_settings An array of global default settings to be merged with the user’s custom settings.
*
* @return array An associative array of merged settings, where user-specific settings override the global defaults.
*/
public function get_merged_user_settings( $user_id, $global_settings ) {
$user_custom_settings = $this->get_user_custom_settings( $user_id );
// Merge user-specific settings with global defaults.
$merged_settings = $global_settings;
$customizable_settings = array(
// Reward Points settings.
'rewardPoint',
'redeemPoint',
'redeemLimit',
'enableRedeemLimit',
'redeemDiscount',
// Reward Coupons settings .
'rewardType',
'rewardDiscount',
'rewardDiscountCapping',
'enableRewardExpiry',
'rewardCouponValidity',
'enableRewardCouponUsageLimit',
'rewardCouponUsageLimit',
'referralDiscount',
'referralDiscountCapping',
'enableReferralLimit',
'referralMonthlyLimit',
'referralMinimumPurchaseAmount',
'rewardMinimumPurchaseAmount',
);
foreach ( $customizable_settings as $setting_key ) {
if ( isset( $user_custom_settings[ $setting_key ] ) ) {
$merged_settings[ $setting_key ] = $user_custom_settings[ $setting_key ];
}
}
return $merged_settings;
}
/**
* Issues reward points to a user manually with a table existence check.
*
* This function manually adds reward points to a user's account. It first checks if the necessary database table
* (`reward_points`) exists. If the table does not exist or the user is not found, the function returns `false`.
* If the table exists, it inserts a new record into the `reward_points` table with the provided points, user email,
* and other relevant details. It also updates the user's total and available points stored in their user meta data.
*
* @since 1.0.0
*
* @param int $user_id The ID of the user to whom the reward points will be issued.
* @param int $points The number of reward points to issue to the user.
* @param string $source The source of the points (defaults to 'manual_issue').
*
* @return bool Returns `true` if the points were successfully issued and updated, `false` otherwise.
*/
public function issue_reward_points( $user_id, $points, $source = 'manual_issue' ) {
global $wpdb;
$table_name = $wpdb->prefix . 'reward_points';
$table_exists = $wpdb->get_var( $wpdb->prepare( 'SHOW TABLES LIKE %s', $table_name ) );
if ( $table_exists !== $table_name ) {
return false;
}
$user_data = get_userdata( $user_id );
if ( ! $user_data ) {
return false;
}
$user_email = $user_data->user_email;
$earned_date = gmdate( 'Y-m-d' );
$unique_id = wp_generate_uuid4();
// Insert with existing table structure.
$result = $wpdb->insert(
$table_name,
array(
'user_id' => $user_id,
'email' => $user_email,
'points' => $points,
'earned_date' => $earned_date,
'unique_id' => $unique_id,
),
array(
'%d',
'%s',
'%d',
'%s',
'%s',
)
);
if ( false === $result ) {
return false;
}
// Update user meta totals.
$available_points = intval( get_user_meta( $user_id, 'ecre_available_points', true ) );
$total_points = intval( get_user_meta( $user_id, 'ecre_reward_points', true ) );
update_user_meta( $user_id, 'ecre_reward_points', $total_points + $points );
update_user_meta( $user_id, 'ecre_available_points', $available_points + $points );
return true;
}
}
}