/* Plugin Name: AFS Advantage+ Tracker Description: Tracks weekly and quarterly spending and rebates for customers, with manual override functionality. Version: 1.03.4 Author: Absolute Food Service */ if (!defined('ABSPATH')) exit; // Hook admin menu if (is_admin()) { add_action('admin_menu', function() { add_menu_page('AFS Advantage+ Tracker', 'AFS Advantage+ Tracker', 'manage_woocommerce', 'afs-advantage-tracker', 'afs_tracker_render'); }); } function afs_tracker_render() { if (!is_admin()) return; global $wpdb; if (isset($_GET['tab']) && $_GET['tab'] === 'payouts') { $tab = 'payouts'; echo '

Payout Management

'; $payouts = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}afs_rebate_payouts ORDER BY quarter DESC, customer_id"); echo ''; foreach ($payouts as $p) { if ($p->status === 'unpaid') { echo ""; } else { echo ""; } } } return; } global $wpdb; $table = $wpdb->prefix . 'afs_rebate_log'; $override_table = $wpdb->prefix . 'afs_rebate_override'; // Fetch unique customers per quarter $quarter_summary = $wpdb->get_results("SELECT customer_id, CONCAT(YEAR(week_start), ' Q', QUARTER(week_start)) as quarter, SUM(weekly_total) as total_spend FROM $table GROUP BY customer_id, quarter"); // Get override values $raw_overrides = $wpdb->get_results("SELECT * FROM $override_table"); $override_data = []; foreach ($raw_overrides as $row) { $key = $row->customer_id . '_' . str_replace(' ', '', $row->quarter); $override_data[$key] = $row->override_value; } foreach ($quarter_summary as $row) { $cid = $row->customer_id; $quarter = $row->quarter; $total_spend = round($row->total_spend, 2); $rebate_percent = ($total_spend >= 12000) ? 4 : (($total_spend >= 8000) ? 3 : (($total_spend >= 3000) ? 2 : (($total_spend >= 2000) ? 1 : 0))); $calc_rebate = round($total_spend * $rebate_percent / 100, 2); $override_key = $cid . '_' . str_replace(' ', '', $quarter); $override_val = isset($override_data[$override_key]) ? $override_data[$override_key] : ''; $total_rebate = $calc_rebate + floatval($override_val); } // Save override if (isset($_POST['save_override']) && !empty($_POST['override'])) { foreach ($_POST['override'] as $key => $val) { $cid = strtok($key, '_'); $q = substr($key, strlen($cid) + 1); $q = substr($q, 0, 4) . ' Q' . substr($q, 5); $existing = $wpdb->get_var($wpdb->prepare("SELECT COUNT(*) FROM $override_table WHERE customer_id = %d AND quarter = %s", $cid, $q)); if ($existing) { $wpdb->update($override_table, ['override_value' => floatval($val)], ['customer_id' => $cid, 'quarter' => $q]); } else { $wpdb->insert($override_table, ['customer_id' => $cid, 'quarter' => $q, 'override_value' => floatval($val)]); } } } $weekly = $wpdb->get_results("SELECT * FROM $table ORDER BY week_start DESC"); foreach ($weekly as $log) { $tier = $log->rebate_percent; $points = $log->points_earned; $rebate = '$' . number_format($log->rebate_value, 2); $status = $log->status; } // Create override table if not exists register_activation_hook(__FILE__, function() { global $wpdb; $table = $wpdb->prefix . 'afs_rebate_override'; $charset = $wpdb->get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS $table ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, quarter VARCHAR(10) NOT NULL, override_value DECIMAL(10,2) DEFAULT 0, UNIQUE KEY unique_combo (customer_id, quarter) ) $charset;"; require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); dbDelta($sql); }); // CSV Export Handler add_action('admin_post_afs_export_quarterly_csv', function() { global $wpdb; $table = $wpdb->prefix . 'afs_rebate_log'; $override_table = $wpdb->prefix . 'afs_rebate_override'; $quarter_summary = $wpdb->get_results("SELECT customer_id, CONCAT(YEAR(week_start), ' Q', QUARTER(week_start)) as quarter, SUM(weekly_total) as total_spend FROM $table GROUP BY customer_id, quarter"); $raw_overrides = $wpdb->get_results("SELECT * FROM $override_table"); $override_data = []; foreach ($raw_overrides as $row) { $key = $row->customer_id . '_' . str_replace(' ', '', $row->quarter); $override_data[$key] = $row->override_value; } header('Content-Type: text/csv'); header('Content-Disposition: attachment;filename=afs_quarterly_summary.csv'); $output = fopen('php://output', 'w'); fputcsv($output, ['Customer ID', 'Quarter', 'Total Spend', 'Rebate %', 'Override Rebate', 'Total Rebate']); foreach ($quarter_summary as $row) { $cid = $row->customer_id; $quarter = $row->quarter; $total_spend = round($row->total_spend, 2); $rebate_percent = ($total_spend >= 12000) ? 4 : (($total_spend >= 8000) ? 3 : (($total_spend >= 3000) ? 2 : (($total_spend >= 2000) ? 1 : 0))); $calc_rebate = round($total_spend * $rebate_percent / 100, 2); $override_key = $cid . '_' . str_replace(' ', '', $quarter); $override_val = isset($override_data[$override_key]) ? floatval($override_data[$override_key]) : 0; $total_rebate = $calc_rebate + $override_val; fputcsv($output, [$cid, $quarter, number_format($total_spend, 2), $rebate_percent . '%', number_format($override_val, 2), number_format($total_rebate, 2)]); } fclose($output); exit; }); // Create rebate payouts table on plugin activation register_activation_hook(__FILE__, function() { global $wpdb; $table = $wpdb->prefix . 'afs_rebate_payouts'; $charset = $wpdb->get_charset_collate(); $sql = "CREATE TABLE IF NOT EXISTS $table ( id BIGINT AUTO_INCREMENT PRIMARY KEY, customer_id BIGINT NOT NULL, quarter VARCHAR(10) NOT NULL, rebate_amount DECIMAL(10,2) NOT NULL, status VARCHAR(10) DEFAULT 'unpaid', paid_date DATETIME DEFAULT NULL, UNIQUE KEY unique_combo (customer_id, quarter) ) $charset;"; require_once(ABSPATH . 'wp-admin/includes/upgrade.php'); dbDelta($sql); }); // Handle payout generation add_action('admin_post_afs_generate_payouts', function() { global $wpdb; if (!isset($_POST['payout_quarter'])) wp_die('Invalid request'); $quarter = sanitize_text_field($_POST['payout_quarter']); $log_table = $wpdb->prefix . 'afs_rebate_log'; $override_table = $wpdb->prefix . 'afs_rebate_override'; $payout_table = $wpdb->prefix . 'afs_rebate_payouts'; $summary = $wpdb->get_results($wpdb->prepare(" SELECT customer_id, SUM(weekly_total) as total_spend FROM $log_table WHERE CONCAT(YEAR(week_start), ' Q', QUARTER(week_start)) = %s GROUP BY customer_id ", $quarter)); $raw_overrides = $wpdb->get_results($wpdb->prepare("SELECT * FROM $override_table WHERE quarter = %s", $quarter)); $override_map = []; foreach ($raw_overrides as $row) { $override_map[$row->customer_id] = floatval($row->override_value); } foreach ($summary as $row) { $cid = $row->customer_id; $spend = floatval($row->total_spend); $tier = ($spend >= 12000) ? 4 : (($spend >= 8000) ? 3 : (($spend >= 3000) ? 2 : (($spend >= 2000) ? 1 : 0))); $calc_rebate = round($spend * $tier / 100, 2); $override = isset($override_map[$cid]) ? $override_map[$cid] : 0; $total = $calc_rebate + $override; $wpdb->replace($payout_table, [ 'customer_id' => $cid, 'quarter' => $quarter, 'rebate_amount' => $total, 'status' => 'unpaid', 'paid_date' => null ]); } wp_redirect(admin_url('admin.php?page=afs-advantage-tracker&tab=payouts&gen=1')); exit; }); // Handle mark as paid add_action('admin_post_afs_mark_paid', function() { global $wpdb; $cid = intval($_GET['cid']); $q = sanitize_text_field($_GET['q']); $table = $wpdb->prefix . 'afs_rebate_payouts'; $wpdb->update($table, ['status' => 'paid', 'paid_date' => current_time('mysql')], ['customer_id' => $cid, 'quarter' => $q]); wp_redirect(admin_url('admin.php?page=afs-advantage-tracker&tab=payouts')); exit; }); // CSV export handler for payouts add_action('admin_post_afs_export_payouts', function() { global $wpdb; $table = $wpdb->prefix . 'afs_rebate_payouts'; $filter = isset($_GET['only_unpaid']) ? "WHERE status = 'unpaid'" : ""; $rows = $wpdb->get_results("SELECT * FROM $table $filter"); header('Content-Type: text/csv'); header('Content-Disposition: attachment;filename=afs_payouts.csv'); $output = fopen('php://output', 'w'); fputcsv($output, ['Customer ID', 'Quarter', 'Rebate Amount', 'Status', 'Paid Date']); foreach ($rows as $r) { fputcsv($output, [$r->customer_id, $r->quarter, number_format($r->rebate_amount, 2), $r->status, $r->paid_date]); } fclose($output); exit; }); // Handle mark as unpaid add_action('admin_post_afs_mark_unpaid', function() { global $wpdb; $cid = intval($_GET['cid']); $q = sanitize_text_field($_GET['q']); $table = $wpdb->prefix . 'afs_rebate_payouts'; $wpdb->update($table, ['status' => 'unpaid', 'paid_date' => null], ['customer_id' => $cid, 'quarter' => $q]); wp_redirect(admin_url('admin.php?page=afs-advantage-tracker&tab=payouts')); exit; }); My account | Foodservice GTA | AFS
et-loader

My account

Customer IDQuarterRebateStatusPaid DateAction
Mark as PaidMark as Unpaid