<?php
|
namespace JVBase\importers;
|
|
use WP_Error;
|
use JVBase\managers\ReferralManager;
|
|
if (!defined('ABSPATH')) {
|
exit;
|
}
|
|
/**
|
* JaneApp Sales Importer
|
*
|
* Imports sales/treatment data from JaneApp CSV exports and updates referral tracking
|
*/
|
class JaneAppSalesImporter
|
{
|
protected $wpdb;
|
protected string $jane_clients_table;
|
protected string $referrals_table;
|
protected string $treatments_table;
|
protected string $rewards_table;
|
protected ReferralManager $referral_manager;
|
protected array $import_stats = [];
|
protected array $skipped_details = [];
|
|
// CSV column mapping
|
protected array $column_map = [
|
'patient_guid' => 'Patient Guid',
|
'purchase_date' => 'Purchase Date',
|
'item' => 'Item',
|
'status' => 'Status',
|
'invoice_number' => 'Invoice #',
|
'total' => 'Total',
|
];
|
|
// Treatment types that qualify for rewards
|
protected array $treatment_types = [
|
'free_consult' => 'Free Consult',
|
'tier_1' => 'Tier 1',
|
'tier_2' => 'Tier 2',
|
'tier_3' => 'Tier 3',
|
'tier_4' => 'Tier 4',
|
'tier_5' => 'Tier 5',
|
'tier_6' => 'Tier 6',
|
'brows' => 'Brows',
|
'freckles' => 'Freckles',
|
'test_snap' => 'Test Snap',
|
'smp_half' => 'SMP - Half Head',
|
'smp_full' => 'SMP - Full Head',
|
];
|
|
public function __construct()
|
{
|
global $wpdb;
|
$this->wpdb = $wpdb;
|
$this->jane_clients_table = $wpdb->prefix . BASE . 'jane_clients';
|
$this->referrals_table = $wpdb->prefix . BASE . 'referrals';
|
$this->treatments_table = $wpdb->prefix . BASE . 'referral_treatments';
|
$this->rewards_table = $wpdb->prefix . BASE . 'referral_rewards';
|
$this->referral_manager = new ReferralManager();
|
}
|
|
/**
|
* Import sales from CSV file
|
*
|
* @param string $file_path Path to the CSV file
|
* @param array $options Import options
|
* @return array|WP_Error Import results with stats and errors
|
*/
|
public function importFromCSV(string $file_path, array $options = []): array|WP_Error
|
{
|
// Initialize stats
|
$this->import_stats = [
|
'total_rows' => 0,
|
'processed' => 0,
|
'consultations' => 0,
|
'treatments' => 0,
|
'skipped' => 0,
|
'errors' => [],
|
'unmatched_guids' => [],
|
'no_referral' => []
|
];
|
|
// Validate file exists
|
if (!file_exists($file_path)) {
|
return new WP_Error('file_not_found', 'CSV file not found');
|
}
|
|
// Parse options
|
$skip_existing = $options['skip_existing'] ?? true;
|
|
// Open and parse CSV
|
$handle = fopen($file_path, 'r');
|
if (!$handle) {
|
return new WP_Error('file_open_error', 'Could not open CSV file');
|
}
|
|
// Get header row
|
$headers = fgetcsv($handle);
|
if (!$headers) {
|
fclose($handle);
|
return new WP_Error('invalid_csv', 'CSV file is empty or invalid');
|
}
|
|
// Trim whitespace from headers to handle inconsistent CSV formats
|
$headers = array_map('trim', $headers);
|
|
// Map column indices
|
$column_indices = $this->mapColumnIndices($headers);
|
if (is_wp_error($column_indices)) {
|
fclose($handle);
|
return $column_indices;
|
}
|
|
// Get Patient column index for error reporting
|
$patient_name_index = array_search('Patient', $headers);
|
|
// Start transaction for data integrity
|
$this->wpdb->query('START TRANSACTION');
|
|
try {
|
// Process each row
|
$row_number = 1; // Header is row 1, data starts at row 2
|
while (($row = fgetcsv($handle)) !== false) {
|
$row_number++;
|
$this->import_stats['total_rows']++;
|
|
$result = $this->processSalesRow($row, $column_indices, [
|
'skip_existing' => $skip_existing
|
]);
|
|
if (is_wp_error($result)) {
|
$error_data = [
|
'row' => $row_number,
|
'patient_guid' => trim($row[$column_indices['patient_guid']] ?? 'Unknown'),
|
'patient_name' => trim($row[$patient_name_index] ?? 'Unknown'),
|
'item' => trim($row[$column_indices['item']] ?? 'Unknown'),
|
'reason' => $result->get_error_message()
|
];
|
|
$this->import_stats['errors'][] = $error_data;
|
$this->import_stats['skipped']++;
|
} else {
|
$this->import_stats['processed']++;
|
if ($result['type'] === 'consultation') {
|
$this->import_stats['consultations']++;
|
} elseif ($result['type'] === 'treatment') {
|
$this->import_stats['treatments']++;
|
}
|
}
|
}
|
|
// Commit transaction
|
$this->wpdb->query('COMMIT');
|
|
} catch (\Exception $e) {
|
// Rollback on error
|
$this->wpdb->query('ROLLBACK');
|
fclose($handle);
|
return new WP_Error('import_error', 'Import failed: ' . $e->getMessage());
|
}
|
|
fclose($handle);
|
|
return $this->import_stats;
|
}
|
|
/**
|
* Map CSV column headers to internal field names
|
*
|
* @param array $headers CSV header row
|
* @return array|WP_Error Column index mapping or error
|
*/
|
protected function mapColumnIndices(array $headers): array|WP_Error
|
{
|
$indices = [];
|
|
foreach ($this->column_map as $field => $csv_column) {
|
$index = array_search($csv_column, $headers);
|
if ($index === false) {
|
return new WP_Error(
|
'missing_column',
|
sprintf('Required column "%s" not found in CSV', $csv_column)
|
);
|
}
|
$indices[$field] = $index;
|
}
|
|
return $indices;
|
}
|
|
/**
|
* Process a single sales row from CSV
|
*
|
* @param array $row CSV row data
|
* @param array $column_indices Column mapping
|
* @param array $options Processing options
|
* @return array|WP_Error Result of processing
|
*/
|
protected function processSalesRow(array $row, array $column_indices, array $options): array|WP_Error
|
{
|
// Extract data from row
|
$patient_guid = trim($row[$column_indices['patient_guid']] ?? '');
|
$purchase_date = trim($row[$column_indices['purchase_date']] ?? '');
|
$item = trim($row[$column_indices['item']] ?? '');
|
$status = trim($row[$column_indices['status']] ?? '');
|
$invoice_number = trim($row[$column_indices['invoice_number']] ?? '');
|
$total = trim($row[$column_indices['total']] ?? 0);
|
|
// Validate required fields
|
if (empty($patient_guid) || empty($purchase_date) || empty($item)) {
|
return new WP_Error('invalid_data', 'Missing required fields');
|
}
|
|
// Skip no-shows and cancellations
|
if ($this->shouldSkipItem($item, $status)) {
|
return new WP_Error('skipped_status', 'No show or cancelled appointment');
|
}
|
|
// Get user ID from patient GUID
|
$user_id = $this->getUserIdByGuid($patient_guid);
|
if (!$user_id) {
|
$this->import_stats['unmatched_guids'][] = $patient_guid;
|
return new WP_Error('user_not_found', 'User not found for patient GUID: ' . $patient_guid);
|
}
|
|
// Get referral record for this user
|
$referral = $this->referral_manager->getReferralByReferee($user_id);
|
if (!$referral) {
|
$this->import_stats['no_referral'][] = [
|
'patient_guid' => $patient_guid,
|
'user_id' => $user_id
|
];
|
return new WP_Error('no_referral', 'User has no referral record');
|
}
|
|
// Parse date
|
$treatment_date = date('Y-m-d H:i:s', strtotime($purchase_date));
|
|
// Determine if this is a consultation or treatment
|
$is_consultation = $this->isConsultation($item);
|
|
if ($is_consultation) {
|
return $this->processConsultation($referral, $user_id, $treatment_date, $invoice_number);
|
} else {
|
return $this->processTreatment($referral, $user_id, $item, $treatment_date, $invoice_number, $total, $options);
|
}
|
}
|
|
/**
|
* Check if item should be skipped based on status or suffix
|
*
|
* @param string $item Item name
|
* @param string $status Item status
|
* @return bool True if should skip
|
*/
|
protected function shouldSkipItem(string $item, string $status): bool
|
{
|
// Check for no-show or cancelled suffix
|
if (stripos($item, ' - No Show') !== false || stripos($item, ' - Cancelled') !== false) {
|
return true;
|
}
|
|
// Check status if available
|
if (!empty($status) && in_array(strtolower($status), ['no_show', 'cancelled', 'no show'])) {
|
return true;
|
}
|
|
return false;
|
}
|
|
/**
|
* Check if item is a consultation
|
*
|
* @param string $item Item name
|
* @return bool True if consultation
|
*/
|
protected function isConsultation(string $item): bool
|
{
|
return stripos($item, 'Free Consult') !== false || stripos($item, 'Consultation') !== false;
|
}
|
|
/**
|
* Process a consultation
|
*
|
* @param object $referral Referral record
|
* @param int $user_id User ID
|
* @param string $treatment_date Date of consultation
|
* @param string $invoice_number Invoice number
|
* @return array Result
|
*/
|
protected function processConsultation(object $referral, int $user_id, string $treatment_date, string $invoice_number): array
|
{
|
// Update referral status to 'consulted' if still pending
|
if ($referral->status === 'pending') {
|
$this->wpdb->update(
|
$this->referrals_table,
|
[
|
'status' => 'consulted',
|
'consulted_at' => $treatment_date
|
],
|
['id' => $referral->id],
|
['%s', '%s'],
|
['%d']
|
);
|
|
// Create 50% off reward for referee
|
$this->createConsultationReward($referral->id, $user_id);
|
}
|
|
// Record consultation in treatments table
|
$this->insertTreatment([
|
'referral_id' => $referral->id,
|
'user_id' => $user_id,
|
'treatment_type' => 'Free Consultation',
|
'treatment_date' => $treatment_date,
|
'invoice_number' => $invoice_number,
|
'amount' => 0,
|
'status' => 'completed'
|
]);
|
|
return [
|
'type' => 'consultation',
|
'referral_id' => $referral->id,
|
'user_id' => $user_id
|
];
|
}
|
|
/**
|
* Process a treatment
|
*
|
* @param object $referral Referral record
|
* @param int $user_id User ID
|
* @param string $treatment_type Treatment type
|
* @param string $treatment_date Date of treatment
|
* @param string $invoice_number Invoice number
|
* @param float $total Treatment cost
|
* @param array $options Processing options
|
* @return array Result
|
*/
|
protected function processTreatment(
|
object $referral,
|
int $user_id,
|
string $treatment_type,
|
string $treatment_date,
|
string $invoice_number,
|
float $total,
|
array $options
|
): array
|
{
|
// Check if this treatment already exists (prevent duplicates)
|
if ($options['skip_existing'] && $this->treatmentExists($referral->id, $invoice_number)) {
|
return new WP_Error('duplicate_treatment', 'Treatment already imported');
|
}
|
|
// Insert treatment record
|
$treatment_id = $this->insertTreatment([
|
'referral_id' => $referral->id,
|
'user_id' => $user_id,
|
'treatment_type' => $treatment_type,
|
'treatment_date' => $treatment_date,
|
'invoice_number' => $invoice_number,
|
'amount' => $total,
|
'status' => 'completed'
|
]);
|
|
// Update referral counts and status
|
$treatment_count = (int)$referral->treatment_count + 1;
|
$update_data = [
|
'treatment_count' => $treatment_count
|
];
|
|
// If this is the first treatment, mark as treated and set treated_at
|
if ($referral->status !== 'treated') {
|
$update_data['status'] = 'treated';
|
$update_data['treated_at'] = $treatment_date;
|
|
// Create full rewards for both referrer and referee
|
$this->createTreatmentRewards($referral->id);
|
}
|
|
$this->wpdb->update(
|
$this->referrals_table,
|
$update_data,
|
['id' => $referral->id],
|
array_fill(0, count($update_data), '%s'),
|
['%d']
|
);
|
|
return [
|
'type' => 'treatment',
|
'referral_id' => $referral->id,
|
'user_id' => $user_id,
|
'treatment_id' => $treatment_id
|
];
|
}
|
|
/**
|
* Create consultation reward (50% off)
|
*
|
* @param int $referral_id Referral ID
|
* @param int $user_id User ID
|
*/
|
protected function createConsultationReward(int $referral_id, int $user_id): void
|
{
|
$this->wpdb->insert(
|
$this->rewards_table,
|
[
|
'referral_id' => $referral_id,
|
'user_id' => $user_id,
|
'reward_type' => 'referee',
|
'amount' => 50,
|
'reward_calculation' => 'percentage',
|
'status' => 'available',
|
'created_at' => current_time('mysql'),
|
'notes' => 'Consultation reward - 50% off first treatment'
|
],
|
['%d', '%d', '%s', '%f', '%s', '%s', '%s', '%s']
|
);
|
}
|
|
/**
|
* Create treatment rewards for both referrer and referee
|
*
|
* @param int $referral_id Referral ID
|
*/
|
protected function createTreatmentRewards(int $referral_id): void
|
{
|
$referral = $this->wpdb->get_row($this->wpdb->prepare(
|
"SELECT * FROM {$this->referrals_table} WHERE id = %d",
|
$referral_id
|
));
|
|
if (!$referral) {
|
return;
|
}
|
|
$settings = $this->referral_manager->getRewardSettings();
|
|
// Create referrer reward (fixed amount)
|
$this->wpdb->insert(
|
$this->rewards_table,
|
[
|
'referral_id' => $referral_id,
|
'user_id' => $referral->referrer_id,
|
'reward_type' => 'referrer',
|
'amount' => $settings['referrer_reward_amount'],
|
'reward_calculation' => $settings['referrer_reward_type'],
|
'status' => 'available',
|
'created_at' => current_time('mysql'),
|
'notes' => 'Referral reward for completed treatment'
|
],
|
['%d', '%d', '%s', '%f', '%s', '%s', '%s', '%s']
|
);
|
|
// Create referee reward (percentage or fixed)
|
$this->wpdb->insert(
|
$this->rewards_table,
|
[
|
'referral_id' => $referral_id,
|
'user_id' => $referral->referee_id,
|
'reward_type' => 'referee',
|
'amount' => $settings['referee_reward_amount'],
|
'reward_calculation' => $settings['referee_reward_type'],
|
'status' => 'available',
|
'created_at' => current_time('mysql'),
|
'notes' => 'Treatment completion reward'
|
],
|
['%d', '%d', '%s', '%f', '%s', '%s', '%s', '%s']
|
);
|
}
|
|
/**
|
* Check if treatment already exists
|
*
|
* @param int $referral_id Referral ID
|
* @param string $invoice_number Invoice number
|
* @return bool True if exists
|
*/
|
protected function treatmentExists(int $referral_id, string $invoice_number): bool
|
{
|
$count = $this->wpdb->get_var($this->wpdb->prepare(
|
"SELECT COUNT(*) FROM {$this->treatments_table}
|
WHERE referral_id = %d AND invoice_number = %s",
|
$referral_id,
|
$invoice_number
|
));
|
|
return (int)$count > 0;
|
}
|
|
/**
|
* Insert treatment record
|
*
|
* @param array $data Treatment data
|
* @return int|false Insert ID or false on failure
|
*/
|
protected function insertTreatment(array $data): int|false
|
{
|
$result = $this->wpdb->insert(
|
$this->treatments_table,
|
$data,
|
['%d', '%d', '%s', '%s', '%s', '%f', '%s']
|
);
|
|
return $result ? $this->wpdb->insert_id : false;
|
}
|
|
/**
|
* Get user ID by patient GUID
|
*
|
* @param string $patient_guid Patient GUID
|
* @return int|null User ID or null if not found
|
*/
|
protected function getUserIdByGuid(string $patient_guid): ?int
|
{
|
$result = $this->wpdb->get_var($this->wpdb->prepare(
|
"SELECT user_id FROM {$this->jane_clients_table} WHERE patient_guid = %s",
|
$patient_guid
|
));
|
|
return $result ? (int)$result : null;
|
}
|
|
/**
|
* Get import statistics
|
*
|
* @return array Import statistics
|
*/
|
public function getImportStats(): array
|
{
|
return $this->import_stats;
|
}
|
|
/**
|
* Get treatment history for a referral
|
*
|
* @param int $referral_id Referral ID
|
* @return array Treatment records
|
*/
|
public function getTreatmentHistory(int $referral_id): array
|
{
|
return $this->wpdb->get_results($this->wpdb->prepare(
|
"SELECT * FROM {$this->treatments_table}
|
WHERE referral_id = %d
|
ORDER BY treatment_date DESC",
|
$referral_id
|
));
|
}
|
|
/**
|
* Get treatment statistics for a user
|
*
|
* @param int $user_id User ID
|
* @return array Statistics
|
*/
|
public function getUserTreatmentStats(int $user_id): array
|
{
|
$referral = $this->referral_manager->getReferralByReferee($user_id);
|
|
if (!$referral) {
|
return [
|
'total_treatments' => 0,
|
'last_treatment' => null,
|
'treatment_types' => []
|
];
|
}
|
|
$treatments = $this->getTreatmentHistory($referral->id);
|
|
$stats = [
|
'total_treatments' => count($treatments),
|
'last_treatment' => $treatments[0]->treatment_date ?? null,
|
'treatment_types' => []
|
];
|
|
foreach ($treatments as $treatment) {
|
$type = $treatment->treatment_type;
|
if (!isset($stats['treatment_types'][$type])) {
|
$stats['treatment_types'][$type] = 0;
|
}
|
$stats['treatment_types'][$type]++;
|
}
|
|
return $stats;
|
}
|
}
|
