From c4aa5cdb5e90ad4b420e22772797d16980232a2b Mon Sep 17 00:00:00 2001
From: Jake Vanderwerf <get@jakevanderwerf.ca>
Date: Wed, 15 Apr 2026 18:38:55 +0000
Subject: [PATCH] =Updating custom tables to utilize CustomTable.php
---
inc/managers/ReferralManager.php | 325 +++++++++++++++++++++++++++++++++++++++++++-----------
1 files changed, 258 insertions(+), 67 deletions(-)
diff --git a/inc/managers/ReferralManager.php b/inc/managers/ReferralManager.php
index 48245fa..af9f0b2 100644
--- a/inc/managers/ReferralManager.php
+++ b/inc/managers/ReferralManager.php
@@ -31,6 +31,12 @@
protected ?int $referralPage = null;
protected string $rewards_table;
+ protected CustomTable $referrals;
+ protected CustomTable $codes;
+ protected CustomTable $janeClients;
+ protected CustomTable $rewards;
+ protected CustomTable $treatments;
+
// Default reward settings
protected array $default_settings = [
'referrer_reward_applies_to' => 'per_user', // 'per_user' or 'flat_total'
@@ -48,6 +54,7 @@
public function __construct()
{
+ $this->defineTables();
global $wpdb;
$this->wpdb = $wpdb;
$this->cache = Cache::for('referrals', WEEK_IN_SECONDS);
@@ -63,7 +70,7 @@
add_action('jvbUserRegistered', [$this, 'processRegistrationToken'], 10, 3);
add_action('jvb_add_token_inputs', [$this, 'addLoginInputs'], 10, 1);
- add_action('jvbUserRegistered', [$this, 'processReferral'], 10, 1);
+ add_action('user_register', [$this, 'processReferral'], 10, 1);
// Add meta boxes for admin to manage referrals
add_action('show_user_profile', [$this, 'displayUserReferralInfo']);
@@ -97,6 +104,182 @@
add_filter('jvb_admin_page_submission', [$this, 'handleAdminSubmission'], 10, 3);
}
+ protected function defineTables():void
+ {
+ $this->defineReferralsTable();
+ $this->defineCodeTable();
+ $this->defineJaneClientsTable();
+ $this->defineRewardsTable();
+ $this->defineTreatmentsTable();
+ }
+ protected function defineReferralsTable():void
+ {
+ $table = CustomTable::for('referrals');
+
+ $table->setColumns([
+ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
+ 'from_user' => "{$table->getUserIDType()} NOT NULL",
+ 'to_user' => "{$table->getUserIDType()} NOT NULL",
+ 'to_name' => 'varchar(255) NOT NULL',
+ 'to_email' => 'varchar(255) NOT NULL',
+ 'to_phone' => 'varchar(50) NOT NULL',
+ 'referral_code'=> 'varchar(50) NOT NULL',
+ 'status' => "ENUM('pending', 'consulted', 'treated', 'cancelled') DEFAULT 'pending'",
+ 'created_at'=> 'datetime DEFAULT CURRENT_TIMESTAMP',
+ 'consulted_at'=> 'datetime DEFAULT NULL',
+ 'treated_at'=> 'datetime DEFAULT NULL',
+ 'treatment_count' => 'int DEFAULT 0',
+ 'notes' => 'text DEFAULT NULL',
+ ]);
+
+ $table->setKeys([
+ ['key' => 'PRIMARY', 'value' => 'id'],
+ ['key' => 'UNIQUE', 'value' => 'to_user (`to_user`)'],
+ 'from_user (`from_user`)',
+ 'status (`status`)',
+ 'code (`referral_code`)',
+ 'date (`created_at`)',
+ 'consult (`consulted_at`)'
+ ]);
+
+ $base = BASE;
+ $table->setConstraints([
+ "CONSTRAINT `{$base}referral_from_user_fk` FOREIGN KEY (`from_user`)
+ REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE",
+ "CONSTRAINT `{$base}referral_to_user_fk` FOREIGN KEY (`to_user`)
+ REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE"
+ ]);
+ $table->defineTable();
+ $this->referrals = $table;
+ }
+
+ protected function defineCodeTable():void
+ {
+ $table = CustomTable::for('referrals_codes');
+
+ $table->setColumns([
+ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
+ 'user_id' => "{$table->getUserIDType()} NOT NULL",
+ 'code' => 'varchar(50) NOT NULL',
+ 'created_at'=> 'datetime DEFAULT CURRENT_TIMESTAMP',
+ ]);
+
+ $table->setKeys([
+ ['key' => 'PRIMARY', 'value' => 'id'],
+ ['key' => 'UNIQUE', 'value' => 'code (`code`)'],
+ 'user (`user_id`)',
+ ]);
+
+ $base = BASE;
+ $table->setConstraints([
+ "CONSTRAINT `{$base}referral_code_user_fk` FOREIGN KEY (`user_id`)
+ REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE",
+ ]);
+ $table->defineTable();
+ $this->codes = $table;
+ }
+ protected function defineJaneClientsTable():void
+ {
+ $table = CustomTable::for('referrals_jane_clients');
+
+ $table->setColumns([
+ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
+ 'patient_guid' => 'varchar(50) NOT NULL',
+ 'user_id' => "{$table->getUserIDType()} NOT NULL",
+ 'first_name' => 'varchar(100) NOT NULL',
+ 'last_name' => 'varchar(100) NOT NULL',
+ 'email' => 'varchar(255) NOT NULL',
+ 'imported_at' => 'datetime DEFAULT CURRENT_TIMESTAMP',
+ 'updated_at' => 'datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
+ ]);
+
+ $table->setKeys([
+ ['key' => 'PRIMARY', 'value' => '(`id`)'],
+ ['key' => 'UNIQUE', 'value' => 'patient_guid (`patient_guid`)'],
+ 'user (`user_id`)',
+ 'email (`email`)',
+ ]);
+
+ $base = BASE;
+ $table->setConstraints([
+ "CONSTRAINT `{$base}jane_clients_user` FOREIGN KEY (`user_id`)
+ REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE",
+ ]);
+ $table->defineTable();
+ $this->janeClients = $table;
+ }
+ protected function defineRewardsTable():void
+ {
+ $table = CustomTable::for('referrals_rewards');
+
+ $table->setColumns([
+ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
+ 'referral_id' => 'bigint(20) unsigned NOT NULL',
+ 'user_id' => "{$table->getUserIDType()} NOT NULL",
+ 'reward_type' => "ENUM('referrer', 'referee') NOT NULL",
+ 'amount' => 'decimal(10,2) NOT NULL',
+ 'reward_calculation'=> "ENUM('percentage', 'fixed')",
+ 'status' => "ENUM('available', 'redeemed', 'expired', 'cancelled') DEFAULT 'available'",
+ 'created_at' => 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP',
+ 'updated_at' => 'datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP',
+ 'redeemed_at' => 'datetime DEFAULT NULL',
+ 'expires_at' => 'datetime DEFAULT NULL',
+ 'notes' => 'text DEFAULT NULL',
+ ]);
+
+ $table->setKeys([
+ ['key' => 'PRIMARY', 'value' => '(`id`)'],
+ 'referral (`referral_id`)',
+ 'user (`user_id`)',
+ 'status (`status`)',
+ 'type (`reward_type`)'
+ ]);
+
+ $base = BASE;
+ $table->setConstraints([
+ "CONSTRAINT `{$base}reward_referral` FOREIGN KEY (`referral_id`)
+ REFERENCES {$this->referrals->getFullTableName()} (`id`) ON DELETE CASCADE",
+ "CONSTRAINT `{$base}reward_user` FOREIGN KEY (`user_id`)
+ REFERENCES {$table->getUserTable()}` (`ID`) ON DELETE CASCADE"
+ ]);
+ $table->defineTable();
+ $this->rewards = $table;
+ }
+ protected function defineTreatmentsTable():void
+ {
+ $table = CustomTable::for('referral_treatments');
+
+ $table->setColumns([
+ 'id' => 'bigint(20) unsigned NOT NULL AUTO_INCREMENT',
+ 'referral_id' => 'bigint(20) unsigned NOT NULL',
+ 'user_id' => "{$table->getUserIDType()} NOT NULL",
+ 'treatment_type'=> 'varchar(100) NOT NULL', //Tier 1-6, Brows, etc
+ 'treatment_date'=> 'datetime NOT NULL',
+ 'invoice_number'=> 'varchar(50) DEFAULT NULL',
+ 'amount' => 'decimal(10,2) DEFAULT NULL',
+ 'status' => "ENUM('completed', 'no_show', 'cancelled') DEFAULT 'completed'",
+ 'imported_at' => 'datetime DEFAULT CURRENT_TIMESTAMP',
+ ]);
+
+ $table->setKeys([
+ ['key' => 'PRIMARY', 'value' => '(`id`)'],
+ 'referral (`referral_id`)',
+ 'user (`user_id`)',
+ 'date (`treatment_date`)',
+ 'type (`treatment_type`)',
+ ]);
+
+ $base = BASE;
+ $table->setConstraints([
+ "CONSTRAINT `{$base}treatment_referral` FOREIGN KEY (`referral_id`)
+ REFERENCES `{$this->referrals->getFullTableName()}` (`id`) ON DELETE CASCADE",
+ "CONSTRAINT `{$base}treatment_user` FOREIGN KEY (`user_id`)
+ REFERENCES `{$table->getUserTable()}` (`ID`) ON DELETE CASCADE"
+ ]);
+ $table->defineTable();
+ $this->treatments = $table;
+ }
+
public function getSettings():array
{
return $this->settings;
@@ -186,6 +369,26 @@
}
}
+ public function createCode(int $user_id, string $code):string|false
+ {
+ $code = sanitize_title($code);
+ $existing = $this->codes->get(['code' => $code]);
+ if ($existing) {
+ if ($existing['user_id'] !== $user_id) {
+ return false;
+ }
+ return $code;
+ }
+ $success = $this->codes->insert([
+ 'user_id' => $user_id,
+ 'code' => $code
+ ]);
+ if ($success) {
+ return $code;
+ }
+ return false;
+ }
+
/**
* Generate or get existing referral code for a user
*
@@ -193,31 +396,34 @@
* @param string|null $custom_code Optional custom code
* @return string|WP_Error
*/
- public function getUserReferralCode(int $user_id, ?string $custom_code = null)
+ public function getUserReferralCode(int $user_id, ?string $custom_code = null):array|wp_error
{
$user = get_userdata($user_id);
if (!$user) {
return new WP_Error('invalid_user', 'User not found');
}
- // Check if user already has a code
- $existing_code = get_user_meta($user_id, BASE . 'referral_code', true);
-
- if ($existing_code && !$custom_code) {
- return $existing_code;
+ $existing = $this->codes->pluck('code', ['user_id' => $user_id],'created_at', 'DESC');
+ if ($existing && !$custom_code) {
+ return $existing;
+ }
+ if ($custom_code && !in_array($custom_code, $existing)) {
+ $test = $this->createCode($user_id, $custom_code);
+ if ($test) {
+ return $this->codes->pluck('code', ['user_id' => $user_id], 'created_at', 'DESC');
+ }
+ } else {
+ return $existing;
}
// Generate new code if custom provided or none exists
- $code = $custom_code ?: $this->generateReferralCode($user);
+ $code = $this->generateReferralCode($user);
- // Validate uniqueness
- if ($this->isCodeTaken($code, $user_id)) {
- return new WP_Error('code_taken', 'This referral code is already in use');
+ $success = $this->createCode($user_id, $code);
+ if ($success) {
+ return $this->codes->pluck('code', ['user_id' => $user_id], 'created_at', 'DESC');
}
- // Save the code
- update_user_meta($user_id, BASE . 'referral_code', $code);
-
return $code;
}
@@ -251,24 +457,11 @@
* Check if a referral code is already taken
*
* @param string $code
- * @param int|null $exclude_user_id
* @return bool
*/
- protected function isCodeTaken(string $code, ?int $exclude_user_id = null): bool
+ protected function isCodeTaken(string $code): bool
{
- $args = [
- 'meta_key' => BASE . 'referral_code',
- 'meta_value' => $code,
- 'fields' => 'ID',
- 'number' => 1
- ];
-
- if ($exclude_user_id) {
- $args['exclude'] = [$exclude_user_id];
- }
-
- $users = get_users($args);
- return !empty($users);
+ return (bool) $this->codes->get(['code' => $code]);
}
public function processRegistrationToken(int $user_id, string $email, array $data): void
@@ -295,60 +488,58 @@
* Track a new referral when user registers
*
* @param int $user_id
+ * @param array $userData
* @return bool;
*/
- public function processReferral(int $user_id): bool
+ public function processReferral(int $user_id, array $userData): bool
{
- // Try to get code from user meta first (set during registration)
- $referral_code = get_user_meta($user_id, BASE . 'pending_referral_code', true);
+ $referral = $this->referrals->get(['to_user' => $user_id]);
- if (empty($referral_code)) {
+ if (empty($referral)) {
+ $referral = $this->referrals->get(['to_email' => $userData['email']]);
+ }
+ if (empty($referral)) {
// Check session/cookie if not in meta
if (session_status() === PHP_SESSION_NONE) {
session_start();
}
$referral_code = $_SESSION[BASE . 'referral_code'] ?? $_COOKIE[BASE . 'referral_code'] ?? '';
+ if (!empty ($referral_code)) {
+ $referral = [
+ 'to_user' => $user_id,
+ 'referral_code' => $referral_code,
+ 'to_email' => $userData['user_email']
+ ];
+ }
}
- if (empty($referral_code)) {
+ if (empty($referral)) {
return false; // No referral code - regular registration
}
// Find the referrer
- $referrer = $this->getUserByReferralCode($referral_code);
- if (!$referrer) {
- delete_user_meta($user_id, BASE . 'pending_referral_code');
+ $referrer = $this->codes->pluck('user_id', ['code' => $referral['referral_code']]);
+ if (empty($referrer)) {
+ //This should not happen, but whatever
+ return false;
+ }
+ $referrer = $referrer[0];
+ $record = $this->referrals->findOrCreate([
+ 'to_user' => $user_id,
+ 'referral_code' => $referral['referral_code'],
+ ], [
+ 'from_user' => $referrer,
+ 'to_email' => $referral['to_email'],
+ 'to_name' => $userData['first_name'],
+// 'to_phone' =>
+ 'status' => 'pending'
+ ]);
+
+ if (!$record) {
+ error_log('[ReferralManager]::processReferral Could not update record for user: '.print_r($referral, true));
return false;
}
- $user = get_userdata($user_id);
-
- // Check if referral already exists for this user
- $existing = $this->wpdb->get_row($this->wpdb->prepare(
- "SELECT * FROM {$this->referrals_table}
- WHERE referrer_id = %d AND (referee_email = %s OR referee_id = %d)",
- $referrer->ID,
- $user->user_email,
- $user_id
- ));
-
- if (!$existing) {
- // Create new referral record - referred_at captures registration time
- $this->wpdb->insert(
- $this->referrals_table,
- [
- 'referrer_id' => $referrer->ID,
- 'referee_id' => $user_id,
- 'referee_name' => $user->display_name,
- 'referee_email' => $user->user_email,
- 'referee_phone' => get_user_meta($user_id, BASE . 'phone', true) ?: '',
- 'referral_code' => $referral_code,
- 'status' => 'pending', // pending first treatment
- 'referred_at' => current_time('mysql') // When they registered
- ],
- ['%d', '%d', '%s', '%s', '%s', '%s', '%s', '%s']
- );
- }
// Clean up temp data
delete_user_meta($user_id, BASE . 'pending_referral_code');
@@ -363,10 +554,10 @@
$this->cache->flush();
// Fire action for tracking
- do_action('jvb_referral_processed', $user_id, $referrer->ID, $referral_code);
+ do_action('jvb_referral_processed', $user_id, $referrer->ID, $referral['referral_code']);
// Send notification to referrer
- $this->sendReferrerNotification($referrer->ID, $user->display_name);
+ $this->sendReferrerNotification($referrer->ID, $userData['display_name']);
return true;
}
--
Gitblit v1.10.0