prefix . 'aieo_addcart_events'; } public static function get_reco_table_name() { global $wpdb; return $wpdb->prefix . 'aieo_reco_events'; } public function maybe_install() { $installed = get_option(self::SCHEMA_OPTION, ''); if ($installed !== self::SCHEMA_VERSION && AIEO_Schema_Lock::acquire(self::SCHEMA_OPTION)) { self::install(); update_option(self::SCHEMA_OPTION, self::SCHEMA_VERSION, false); } $reco_installed = get_option(self::RECO_SCHEMA_OPTION, ''); if ($reco_installed !== self::RECO_SCHEMA_VERSION && 'failed' !== self::reco_backoff() && AIEO_Schema_Lock::acquire(self::RECO_SCHEMA_OPTION)) { if ('failed' === self::reco_backoff(true)) { AIEO_Schema_Lock::release(self::RECO_SCHEMA_OPTION); } else { self::install_reco(); if (self::reco_schema_complete()) { update_option(self::RECO_SCHEMA_OPTION, self::RECO_SCHEMA_VERSION, false); delete_transient(self::RECO_SCHEMA_RETRY_TRANSIENT); } else { set_transient(self::RECO_SCHEMA_RETRY_TRANSIENT, 'failed', 15 * MINUTE_IN_SECONDS); AIEO_Schema_Lock::release(self::RECO_SCHEMA_OPTION); } } } if (get_option(self::COLUMNS_OPTION, '') !== self::COLUMNS_VERSION && ! get_transient(self::COLUMNS_RETRY_TRANSIENT) && AIEO_Schema_Lock::acquire(self::COLUMNS_OPTION)) { set_transient(self::COLUMNS_RETRY_TRANSIENT, 1, HOUR_IN_SECONDS); $ok = self::ensure_session_channel_columns(); $ok = self::ensure_badge_context_column() && $ok; if ($ok) { update_option(self::COLUMNS_OPTION, self::COLUMNS_VERSION, true); delete_transient(self::COLUMNS_RETRY_TRANSIENT); } } } public static function ensure_badge_context_column() { global $wpdb; $tbl = self::get_table_name(); if ((string) $wpdb->get_var("SHOW TABLES LIKE '{$tbl}'") !== $tbl) { return false; } $bcs = $wpdb->get_var("SHOW COLUMNS FROM {$tbl} LIKE 'badge_context_snapshot'"); if (!$bcs) { $wpdb->query("ALTER TABLE {$tbl} ADD COLUMN badge_context_snapshot LONGTEXT NULL DEFAULT NULL AFTER source_meta"); } $bf = $wpdb->get_var("SHOW COLUMNS FROM {$tbl} LIKE 'backfilled_at'"); if (!$bf) { $wpdb->query("ALTER TABLE {$tbl} ADD COLUMN backfilled_at DATETIME NULL DEFAULT NULL"); } $bf_idx = $wpdb->get_var("SHOW INDEX FROM {$tbl} WHERE Key_name = 'idx_backfilled_at'"); if (!$bf_idx) { @$wpdb->query("ALTER TABLE {$tbl} ADD INDEX idx_backfilled_at (backfilled_at, id)"); } $wf_fired = $wpdb->get_var("SHOW COLUMNS FROM {$tbl} LIKE 'aieo_wf_fired_at'"); if (!$wf_fired) { $wpdb->query("ALTER TABLE {$tbl} ADD COLUMN aieo_wf_fired_at DATETIME NULL DEFAULT NULL"); } $wf_idx = $wpdb->get_var("SHOW INDEX FROM {$tbl} WHERE Key_name = 'idx_wf_fired'"); if (!$wf_idx) { @$wpdb->query("ALTER TABLE {$tbl} ADD INDEX idx_wf_fired (aieo_wf_fired_at)"); } $virtuals = (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) ? array() : array( 'bcs_was_on_sale' => "TINYINT GENERATED ALWAYS AS (CAST(JSON_VALUE(badge_context_snapshot, '\$.was_on_sale') AS UNSIGNED)) VIRTUAL", 'bcs_is_new' => "TINYINT GENERATED ALWAYS AS (CAST(JSON_VALUE(badge_context_snapshot, '\$.is_new_product') AS UNSIGNED)) VIRTUAL", 'bcs_gift_offer_id' => "INT GENERATED ALWAYS AS (CAST(JSON_VALUE(badge_context_snapshot, '\$.eligible_gift_offer.offer_id') AS UNSIGNED)) VIRTUAL", 'bcs_monthly_offer_id' => "INT GENERATED ALWAYS AS (CAST(JSON_VALUE(badge_context_snapshot, '\$.monthly_offer.offer_id') AS UNSIGNED)) VIRTUAL", 'bcs_free_shipping_offer_id' => "INT GENERATED ALWAYS AS (CAST(JSON_VALUE(badge_context_snapshot, '\$.free_shipping_offer.offer_id') AS UNSIGNED)) VIRTUAL", ); foreach ($virtuals as $name => $def) { $exists = $wpdb->get_var("SHOW COLUMNS FROM {$tbl} LIKE '{$name}'"); if (!$exists) { @$wpdb->query("ALTER TABLE {$tbl} ADD COLUMN {$name} {$def}"); } } $idx_names = (function_exists('aieo_db_supports_analytics') && !aieo_db_supports_analytics()) ? array() : array( 'idx_bcs_sale' => 'bcs_was_on_sale', 'idx_bcs_new' => 'bcs_is_new', 'idx_bcs_gift' => 'bcs_gift_offer_id', 'idx_bcs_mo' => 'bcs_monthly_offer_id', 'idx_bcs_fs' => 'bcs_free_shipping_offer_id', ); foreach ($idx_names as $idx => $col) { $exists = $wpdb->get_var("SHOW INDEX FROM {$tbl} WHERE Key_name = '{$idx}'"); if (!$exists) { @$wpdb->query("ALTER TABLE {$tbl} ADD INDEX {$idx} ({$col})"); } } self::ensure_archive_column_parity($tbl); return true; } public static function capture_badge_context($product_id, $variation_id = 0, $user_id = 0) { $product_id = (int) $product_id; $variation_id = (int) $variation_id; $priced_pid = $variation_id > 0 ? $variation_id : $product_id; $audience_key = class_exists('AIEO_Identity_Bridge') ? AIEO_Identity_Bridge::audience_key_for_current_user() : 'guest'; $snap = array( 'schema_v' => 1, 'captured_at' => gmdate('c'), 'audience_key' => $audience_key, 'priced_pid' => $priced_pid, ); $facts = null; if (class_exists('AIEO_DMM_Price_Filter') && AIEO_DMM_Price_Filter::resolves() && class_exists('AIEO_DMM_Offer_Facts')) { AIEO_DMM_Offer_Facts::prime(array($product_id, $priced_pid)); $facts = null !== AIEO_DMM_Offer_Facts::get($product_id) ? AIEO_DMM_Offer_Facts::viewer($product_id, 'guest' === $audience_key ? null : AIEO_DMM_Offer_Facts::current_customer()) : array('badges' => array()); } if (null !== $facts && array_key_exists('role', $facts)) { $b = $facts['badges']; $snap['was_on_sale'] = isset($b['sale']) ? 1 : 0; $snap['sale_discount_pct'] = isset($b['sale']['pct']) ? number_format((float) $b['sale']['pct'], 2, '.', '') : null; $snap['regular_price'] = null !== $facts['list'] ? (float) $facts['list'] : null; $snap['effective_price'] = null !== $facts['price'] ? (float) $facts['price'] : $snap['regular_price']; $snap['role_price_applied'] = !empty($b['pro']) ? 1 : 0; $month_row = $facts['offer'] > 0 && class_exists('AIEO_DMM_Monthly_Offers') && AIEO_DMM_Monthly_Offers::row_of((int) $facts['offer']) > 0 ? (int) $facts['offer'] : 0; $snap['monthly_offer'] = array( 'active' => isset($b['monthly']) || $month_row > 0 ? 1 : 0, 'offer_id' => $month_row > 0 ? $month_row : null, ); $type = null; if (!empty($facts['role'])) { $type = 'role'; } elseif (isset($b['monthly']) || $month_row > 0) { $type = 'monthly_offer'; } elseif ($facts['offer'] > 0) { $type = 'offer'; } elseif (isset($b['sale'])) { $type = 'native_sale'; } $snap['source_rule'] = array( 'type' => $type, 'id' => 'offer' === $type ? (int) $facts['offer'] : ($month_row > 0 && 'monthly_offer' === $type ? $month_row : null), ); } elseif (null === $facts && class_exists('AIEO_Effective_Prices')) { $row = AIEO_Effective_Prices::get_for($product_id, $audience_key); if (is_array($row)) { $snap['was_on_sale'] = !empty($row['was_on_sale']) ? 1 : 0; $snap['sale_discount_pct'] = isset($row['sale_discount_pct']) ? $row['sale_discount_pct'] : null; $snap['regular_price'] = isset($row['base_price']) ? (float) $row['base_price'] : null; $snap['effective_price'] = isset($row['sale_price']) && $row['sale_price'] !== null ? (float) $row['sale_price'] : (isset($row['base_price']) ? (float) $row['base_price'] : null); $snap['role_price_applied'] = !empty($row['role_price_applied']) ? 1 : 0; $snap['monthly_offer'] = array( 'active' => !empty($row['monthly_offer_active']) ? 1 : 0, 'offer_id' => null, ); $snap['source_rule'] = array( 'type' => isset($row['source_rule_type']) ? $row['source_rule_type'] : null, 'id' => isset($row['source_rule_id']) ? (int) $row['source_rule_id'] : null, ); } } if (class_exists('AIEO_DMM_Brand_Discounts')) { $snap['is_new_product'] = AIEO_DMM_Brand_Discounts::is_new_product($product_id) ? 1 : 0; $post_time = get_post_time('U', true, $product_id); $snap['days_since_publish'] = $post_time ? (int) floor((time() - (int) $post_time) / DAY_IN_SECONDS) : null; } if (class_exists('AIEO_Effective_Eligibility') || null !== $facts) { foreach (array('brand_gift', 'product_label', 'complementary') as $surface) { if ('brand_gift' === $surface && null !== $facts) { $gift = isset($facts['badges']['gift']) ? $facts['badges']['gift'] : null; $row = is_array($gift) ? array('eligible' => 1, 'offer_id' => (int) ($gift['offer'] ?? 0), 'payload' => array('label' => (string) ($gift['label'] ?? ''))) : null; } else { $row = class_exists('AIEO_Effective_Eligibility') ? AIEO_Effective_Eligibility::get_for($surface, $audience_key, $product_id) : null; } if (!is_array($row) || empty($row['eligible'])) { continue; } switch ($surface) { case 'brand_gift': $snap['eligible_gift_offer'] = array( 'offer_id' => isset($row['offer_id']) ? (int) $row['offer_id'] : null, 'label' => isset($row['payload']['label']) ? (string) $row['payload']['label'] : '', 'qualified_at_addtime' => 0, ); break; case 'product_label': if (!isset($row['payload']['rule'])) { break; } $snap['product_label'] = array( 'rule' => (string) $row['payload']['rule'], 'label' => isset($row['payload']['label']) ? (string) $row['payload']['label'] : '', ); break; case 'complementary': $snap['complementary'] = array( 'recipe_id' => isset($row['offer_id']) ? (int) $row['offer_id'] : null, 'count' => isset($row['payload']['count']) ? (int) $row['payload']['count'] : 0, ); break; } } } if (!empty($snap['monthly_offer']['active']) && empty($snap['monthly_offer']['offer_id']) && class_exists('AIEO_DMM_Brand_Discounts')) { $batch = get_post_meta($product_id, AIEO_DMM_Brand_Discounts::META_MONTHLY_BATCH, true); if (!empty($batch)) { $snap['monthly_offer']['offer_id'] = is_numeric($batch) ? (int) $batch : (string) $batch; } } if (null !== $facts) { $fs_row = AIEO_DMM_Offer_Facts::get($priced_pid); $fs = is_array($fs_row) && isset($fs_row['badges']['free_shipping']) ? $fs_row['badges']['free_shipping'] : null; if (is_array($fs) && !empty($fs['offer'])) { $snap['free_shipping_offer'] = array( 'offer_id' => (int) $fs['offer'], 'label' => (string) ($fs['label'] ?? ''), ); } } elseif (class_exists('AIEO_DMM_Free_Shipping_Rules') && AIEO_DMM_Free_Shipping_Rules::has_targeted()) { $fs_rule = AIEO_DMM_Free_Shipping_Rules::covering_rule($priced_pid); if (is_array($fs_rule) && !empty($fs_rule['id'])) { $snap['free_shipping_offer'] = array( 'offer_id' => (int) $fs_rule['id'], 'label' => isset($fs_rule['label']) ? (string) $fs_rule['label'] : '', ); } } return $snap; } public static function ensure_session_channel_columns() { global $wpdb; $tbl = $wpdb->prefix . 'aieo_sessions_statics'; if ((string) $wpdb->get_var("SHOW TABLES LIKE '{$tbl}'") !== $tbl) { return false; } $cols = array( 'entry_channel' => "VARCHAR(40) NOT NULL DEFAULT '' AFTER referrer", 'entry_campaign' => "VARCHAR(120) NOT NULL DEFAULT '' AFTER entry_channel", 'entry_medium' => "VARCHAR(40) NOT NULL DEFAULT '' AFTER entry_campaign", 'entry_term' => "VARCHAR(80) NOT NULL DEFAULT '' AFTER entry_medium", 'entry_content' => "VARCHAR(80) NOT NULL DEFAULT '' AFTER entry_term", 'entry_source' => "VARCHAR(80) NOT NULL DEFAULT '' AFTER entry_channel", 'entry_click_id' => "VARCHAR(255) NOT NULL DEFAULT '' AFTER entry_content", 'entry_ad_id' => "VARCHAR(40) NOT NULL DEFAULT '' AFTER entry_click_id", 'entry_adset_id' => "VARCHAR(40) NOT NULL DEFAULT '' AFTER entry_ad_id", 'entry_ad_campaign_id' => "VARCHAR(40) NOT NULL DEFAULT '' AFTER entry_adset_id", ); $prev_suppress = $wpdb->suppress_errors(true); foreach ($cols as $name => $def) { $exists = $wpdb->get_var("SHOW COLUMNS FROM {$tbl} LIKE '{$name}'"); if (!$exists) { $wpdb->query("ALTER TABLE {$tbl} ADD COLUMN {$name} {$def}"); } } $idx = $wpdb->get_var("SHOW INDEX FROM {$tbl} WHERE Key_name = 'idx_entry_channel'"); if (!$idx) { $wpdb->query("ALTER TABLE {$tbl} ADD INDEX idx_entry_channel (entry_channel)"); } $wpdb->suppress_errors($prev_suppress); $coll = function_exists('aieo_analytics_collation') ? aieo_analytics_collation() : 'utf8mb4_uca1400_ai_ci'; self::reconcile_session_id_collation($coll); self::reconcile_column_collation('email', $coll); return true; } public static function reconcile_session_id_collation(string $target): void { self::reconcile_column_collation('session_id', $target); } public static function reconcile_column_collation(string $column, string $target): void { global $wpdb; $column = preg_replace('/[^a-z0-9_]/i', '', $column); if ($column === '') { return; } $supported = (string) $wpdb->get_var($wpdb->prepare( "SELECT COLLATION_NAME FROM information_schema.COLLATIONS WHERE COLLATION_NAME = %s LIMIT 1", $target )); if ($supported === '') { $addcart_tbl = $wpdb->prefix . 'aieo_addcart_events'; $fallback = (string) $wpdb->get_var($wpdb->prepare( "SELECT COLLATION_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", $addcart_tbl, $column )); if ($fallback === '' || $fallback === $target) { return; } $target = $fallback; } $prefix_like = $wpdb->esc_like($wpdb->prefix . 'aieo_') . '%'; $rows = $wpdb->get_results($wpdb->prepare( "SELECT TABLE_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME LIKE %s AND COLUMN_NAME = %s AND COLLATION_NAME <> %s", $prefix_like, $column, $target ), ARRAY_A); if (empty($rows)) { return; } foreach ($rows as $r) { $tbl = $r['TABLE_NAME']; $type = $r['COLUMN_TYPE']; $null = strtoupper($r['IS_NULLABLE']) === 'YES' ? 'NULL' : 'NOT NULL'; if ($r['COLUMN_DEFAULT'] === null) { $default = ($null === 'NOT NULL') ? "DEFAULT ''" : ''; } else { $default = "DEFAULT '" . esc_sql((string) $r['COLUMN_DEFAULT']) . "'"; } $wpdb->query( "ALTER TABLE `{$tbl}` MODIFY `{$column}` {$type} CHARACTER SET utf8mb4 COLLATE {$target} {$null} {$default}" ); } } public static function install() { global $wpdb; $table = self::get_table_name(); $charset = aieo_get_charset_collate(); $sql = "CREATE TABLE {$table} ( id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, event_id varchar(36) NOT NULL DEFAULT '', session_id varchar(64) NOT NULL DEFAULT '', user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, product_id bigint(20) UNSIGNED NOT NULL, variation_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, quantity int(11) NOT NULL DEFAULT 1, unit_price decimal(18,4) NOT NULL DEFAULT 0.0000, source varchar(40) NOT NULL DEFAULT 'unknown', source_pid bigint(20) UNSIGNED NOT NULL DEFAULT 0, source_meta text NULL, cart_position int(11) NOT NULL DEFAULT 0, ts_added datetime NOT NULL, ts_viewed datetime NULL DEFAULT NULL, order_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, ga_sent tinyint(1) NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY event_id (event_id), KEY session_id (session_id), KEY product_id (product_id), KEY source (source), KEY ts_added (ts_added), KEY order_id (order_id), KEY user_id (user_id) ) {$charset};"; if (!function_exists('dbDelta')) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } dbDelta($sql); } const RECO_COLUMNS = array('id', 'event_id', 'session_id', 'user_id', 'event_type', 'target_pid', 'target_type', 'anchor_pid', 'anchor_type', 'anchor_term_id', 'anchor_term_tax', 'surface', 'position', 'arm', 'reason', 'origin_id', 'set_hash', 'ts'); public static function install_reco() { global $wpdb; $table = self::get_reco_table_name(); $charset = aieo_get_charset_collate(); $sql = "CREATE TABLE {$table} ( id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, event_id varchar(36) NOT NULL DEFAULT '', session_id varchar(64) NOT NULL DEFAULT '', user_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, event_type varchar(16) NOT NULL DEFAULT 'impression', target_pid bigint(20) UNSIGNED NOT NULL, target_type varchar(20) NOT NULL DEFAULT '', anchor_pid bigint(20) UNSIGNED NOT NULL DEFAULT 0, anchor_type varchar(20) NOT NULL DEFAULT '', anchor_term_id bigint(20) UNSIGNED NOT NULL DEFAULT 0, anchor_term_tax varchar(32) NOT NULL DEFAULT '', surface varchar(40) NOT NULL DEFAULT 'unknown', position smallint(5) UNSIGNED NOT NULL DEFAULT 0, arm varchar(16) NOT NULL DEFAULT '', reason varchar(16) NOT NULL DEFAULT '', origin_id int(10) UNSIGNED NOT NULL DEFAULT 0, set_hash char(12) NOT NULL DEFAULT '', ts datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY event_id (event_id), KEY session_target (session_id, target_pid), KEY surface (surface), KEY anchor_pid (anchor_pid), KEY anchor_term_id (anchor_term_id), KEY arm_ts (arm, ts), KEY origin_ts (origin_id, ts), KEY ts (ts) ) {$charset};"; if (!function_exists('dbDelta')) { require_once ABSPATH . 'wp-admin/includes/upgrade.php'; } dbDelta($sql); self::ensure_archive_column_parity(); } public static function reco_backoff($fresh = false) { if (!$fresh) { return (string) get_transient(self::RECO_SCHEMA_RETRY_TRANSIENT); } if (function_exists('wp_using_ext_object_cache') && wp_using_ext_object_cache()) { return (string) wp_cache_get(self::RECO_SCHEMA_RETRY_TRANSIENT, 'transient', true); } global $wpdb; $name = '_transient_' . self::RECO_SCHEMA_RETRY_TRANSIENT; $limit = '_transient_timeout_' . self::RECO_SCHEMA_RETRY_TRANSIENT; $rows = $wpdb->get_results($wpdb->prepare( "SELECT option_name, option_value FROM {$wpdb->options} WHERE option_name IN (%s, %s)", $name, $limit ), OBJECT_K); $value = isset($rows[$name]) ? (string) $rows[$name]->option_value : ''; $until = isset($rows[$limit]) ? (int) $rows[$limit]->option_value : 0; return '' !== $value && $until > time() ? $value : ''; } public static function reco_column_missing($column) { global $wpdb; $found = $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME = %s", self::get_reco_table_name(), (string) $column )); return null !== $found && 0 === (int) $found; } public static function reco_schema_complete() { global $wpdb; $hot = self::get_reco_table_name(); $archive = $hot . '_archive'; $found = $wpdb->get_results($wpdb->prepare( "SELECT TABLE_NAME AS t, COLUMN_NAME AS c FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN (%s, %s)", $hot, $archive )); $have = array(strtolower($hot) => array(), strtolower($archive) => array()); foreach ((array) $found as $column) { $have[strtolower((string) $column->t)][] = strtolower((string) $column->c); } if (array_diff(self::RECO_COLUMNS, $have[strtolower($hot)])) { return false; } return !$have[strtolower($archive)] || !array_diff(self::RECO_COLUMNS, $have[strtolower($archive)]); } public static function ensure_archive_column_parity($hot = '') { global $wpdb; $hot = '' !== (string) $hot ? (string) $hot : self::get_reco_table_name(); $archive = $hot . '_archive'; $have_archive = (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM information_schema.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s", $archive )); if ($have_archive < 1) { return 0; } $cols = $wpdb->get_results($wpdb->prepare( "SELECT TABLE_NAME, COLUMN_NAME, EXTRA FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME IN (%s, %s)", $hot, $archive )); $hot_cols = array(); $arc_cols = array(); foreach ((array) $cols as $c) { if ($c->TABLE_NAME === $hot) { if (false === stripos((string) $c->EXTRA, 'GENERATED')) { $hot_cols[strtolower($c->COLUMN_NAME)] = $c; } } else { $arc_cols[strtolower($c->COLUMN_NAME)] = true; } } $missing = array_diff_key($hot_cols, $arc_cols); if (!$missing) { return 0; } $create = $wpdb->get_row("SHOW CREATE TABLE `{$hot}`", ARRAY_N); $defs = array(); foreach (preg_split('/\r?\n/', (string) ($create[1] ?? '')) as $line) { if (preg_match('/^\s*`([^`]+)`\s+(.+?),?\s*$/', $line, $m)) { $defs[strtolower($m[1])] = $m[2]; } } $added = 0; foreach ($missing as $name => $c) { if (!isset($defs[$name])) { continue; } $ok = $wpdb->query("ALTER TABLE `{$archive}` ADD COLUMN `{$c->COLUMN_NAME}` {$defs[$name]}"); if (false === $ok) { aieo_debug_log('AIEO_Attribution: archive column parity failed for ' . $c->COLUMN_NAME . ': ' . $wpdb->last_error); continue; } $added++; } return $added; } public function enqueue_assets() { if (is_admin()) { return; } if (!apply_filters('aieo_attribution_client_enabled', true)) { return; } $has_wc = class_exists('WooCommerce'); $has_reco_cards = self::content_reco_surface_present(); if (!$has_wc && !$has_reco_cards) { return; } if (!$has_wc) { self::enqueue_reco_observer(null); return; } $archive_context = $this->resolve_archive_context(); self::enqueue_reco_observer($archive_context); } public static function content_reco_surface_present() { return class_exists('AIEO_Semantic_Reco_Source') && AIEO_Semantic_Reco_Source::available(); } private static function enqueue_reco_observer($archive_context) { self::enqueue_cards_asset(); $reco_handle = 'aieo-reco-observer'; $reco_rel = 'assets/js/aieo-reco-observer.js'; $reco_url = plugins_url($reco_rel, AIEO_FILE); $reco_path = dirname(AIEO_FILE) . '/' . $reco_rel; $reco_ver = file_exists($reco_path) ? filemtime($reco_path) : (defined('AIEO_VERSION') ? AIEO_VERSION : false); wp_enqueue_script($reco_handle, $reco_url, array('aieo-cards'), $reco_ver, array('in_footer' => true, 'strategy' => 'defer')); add_filter('rooster/third_party/handles', static function ($handles) use ($reco_handle) { $handles = is_array($handles) ? $handles : array(); $handles[$reco_handle] = array('trigger' => 'human'); return $handles; }); add_filter('rocket_delay_js_exclusions', array(__CLASS__, 'rocket_delay_exclusions_reco')); $reco_config = array( 'enabled' => true, 'ajaxUrl' => admin_url('admin-ajax.php'), 'flushIntervalMs' => 5000, 'maxBatchSize' => 40, 'archive' => $archive_context, ); if (class_exists('AIEO_Signals') && AIEO_Signals::on('intake')) { $reco_config['ack'] = true; } if (class_exists('AIEO_Sighting') && AIEO_Sighting::on()) { $reco_config['sighting'] = AIEO_Sighting::settings(); } wp_localize_script($reco_handle, 'aieoRecoObserver', $reco_config); } public static function rocket_delay_exclusions_reco($list) { $list = is_array($list) ? $list : array(); $list[] = 'aieo-reco-observer'; $list[] = 'aieo-cards'; return array_values(array_unique($list)); } public static function enqueue_cards_asset() { if (wp_script_is('aieo-cards', 'enqueued') || wp_script_is('aieo-cards', 'registered')) { wp_enqueue_script('aieo-cards'); return; } $rel = 'assets/js/aieo-cards.js'; $path = dirname(AIEO_FILE) . '/' . $rel; $ver = file_exists($path) ? filemtime($path) : (defined('AIEO_VERSION') ? AIEO_VERSION : false); wp_enqueue_script('aieo-cards', plugins_url($rel, AIEO_FILE), array(), $ver, array('in_footer' => true, 'strategy' => 'defer')); } private function resolve_archive_context() { if (!function_exists('is_tax') || !function_exists('get_queried_object')) { return null; } $brand_taxonomies = array('product_brand', 'pwb-brand', 'yith_product_brand'); if (!is_tax()) { return null; } $term = get_queried_object(); if (!$term || empty($term->term_id) || empty($term->taxonomy)) { return null; } $tax = (string) $term->taxonomy; $tax_obj = function_exists('get_taxonomy') ? get_taxonomy($tax) : null; $object_types = ($tax_obj && !empty($tax_obj->object_type)) ? (array) $tax_obj->object_type : array(); $is_brand = in_array($tax, $brand_taxonomies, true); if (!$is_brand && !in_array('product', $object_types, true)) { return null; } if ($is_brand) { $tax = 'product_brand'; } return array( 'term_id' => (int) $term->term_id, 'tax' => $tax, 'slug' => (string) $term->slug, 'name' => (string) $term->name, ); } private static function count_refusal($stream, $reason) { if (is_callable(array('AIEO_Session_Tracker', 'count_refusal'))) { AIEO_Session_Tracker::count_refusal($stream, $reason); } } public function ajax_log() { if (!headers_sent()) { @header('Connection: close'); @header('Content-Length: 0'); } while (ob_get_level() > 0) { ob_end_clean(); } $ip = isset($_SERVER['REMOTE_ADDR']) ? (string) $_SERVER['REMOTE_ADDR'] : ''; if (class_exists('AIEO_Signals') && AIEO_Signals::on('intake')) { if (AIEO_Signals::limited('addcart', (int) apply_filters('aieo_attribution_log_rate_limit_per_minute', 60))) { self::count_refusal('addcart', 'rate'); status_header(429); wp_die('', '', array('response' => 429)); } } elseif ($ip !== '') { $cap = (int) apply_filters('aieo_attribution_log_rate_limit_per_minute', 60); if ($cap > 0) { $key = 'aieo_attr_rl_' . md5($ip); $hits = (int) get_transient($key); if ($hits >= $cap) { self::count_refusal('addcart', 'rate'); status_header(429); wp_die('', '', array('response' => 429)); } set_transient($key, $hits + 1, MINUTE_IN_SECONDS); } } $this->log_event(array( 'event_id' => isset($_POST['event_id']) ? sanitize_text_field((string) wp_unslash($_POST['event_id'])) : '', 'visit_id' => isset($_POST['visit_id']) ? sanitize_text_field((string) wp_unslash($_POST['visit_id'])) : '', 'product_id' => isset($_POST['product_id']) ? absint(wp_unslash($_POST['product_id'])) : 0, 'variation_id' => isset($_POST['variation_id']) ? absint(wp_unslash($_POST['variation_id'])) : 0, 'quantity' => isset($_POST['quantity']) ? max(1, absint(wp_unslash($_POST['quantity']))) : 1, 'unit_price' => isset($_POST['unit_price']) ? (float) sanitize_text_field(wp_unslash($_POST['unit_price'])) : 0.0, 'source' => isset($_POST['source']) ? sanitize_key((string) wp_unslash($_POST['source'])) : 'unknown', 'source_pid' => isset($_POST['source_pid']) ? absint(wp_unslash($_POST['source_pid'])) : 0, 'source_meta' => isset($_POST['source_meta']) ? (is_array($_POST['source_meta']) ? map_deep(wp_unslash($_POST['source_meta']), 'sanitize_text_field') : sanitize_textarea_field((string) wp_unslash($_POST['source_meta']))) : null, )); status_header(204); wp_die('', '', array('response' => 204)); } public function ajax_reco_batch() { $intake = class_exists('AIEO_Signals') && AIEO_Signals::on('intake'); $batch = $intake && isset($_POST['batch']) ? substr((string) preg_replace('/[^A-Za-z0-9_-]+/', '', (string) wp_unslash($_POST['batch'])), 0, 40) : ''; if (!headers_sent()) { @header('Connection: close'); if ('' === $batch) { @header('Content-Length: 0'); } } while (ob_get_level() > 0) { ob_end_clean(); } if (class_exists('AIEO_Session_Tracker') && method_exists('AIEO_Session_Tracker', 'is_consent_blocking_writes') && AIEO_Session_Tracker::is_consent_blocking_writes()) { self::count_refusal('reco', 'consent'); if (class_exists('AIEO_Sighting') && AIEO_Sighting::on()) { AIEO_Sighting::refused(isset($_POST['events']) ? wp_unslash((string) $_POST['events']) : '', $batch); } $refused = '' !== $batch ? 403 : 204; status_header($refused); wp_die('', '', array('response' => $refused)); } $ip = isset($_SERVER['REMOTE_ADDR']) ? (string) $_SERVER['REMOTE_ADDR'] : ''; if ($intake) { if (AIEO_Signals::limited('reco', (int) apply_filters('aieo_reco_log_rate_limit_per_minute', 60))) { self::count_refusal('reco', 'rate'); status_header(429); wp_die('', '', array('response' => 429)); } } elseif ($ip !== '') { $cap = (int) apply_filters('aieo_reco_log_rate_limit_per_minute', 60); if ($cap > 0) { $key = 'aieo_reco_rl_' . md5($ip); $hits = (int) get_transient($key); if ($hits >= $cap) { self::count_refusal('reco', 'rate'); status_header(429); wp_die('', '', array('response' => 429)); } set_transient($key, $hits + 1, MINUTE_IN_SECONDS); } } $raw = isset($_POST['events']) ? wp_unslash((string) $_POST['events']) : ''; $events = $raw !== '' ? json_decode($raw, true) : null; $written = true; if (is_array($events) && !empty($events)) { $written = false !== $this->log_reco_events($events); } else { self::count_refusal('reco', 'invalid'); } if ('' !== $batch) { if ($written) { wp_send_json(array('ack' => $batch), 200); } status_header(500); wp_die('', '', array('response' => 500)); } status_header(204); wp_die('', '', array('response' => 204)); } private function log_reco_events(array $events) { global $wpdb; $table = self::get_reco_table_name(); $session_id = sanitize_text_field($this->get_session_id()); $user_id = function_exists('get_current_user_id') ? (int) get_current_user_id() : 0; $request_time = time(); $arm = class_exists('AIEO_Reco_Experiment') ? AIEO_Reco_Experiment::arm() : ''; $origin_id = self::resolve_origin_id(isset($_POST['origin']) ? wp_unslash($_POST['origin']) : ''); $env = class_exists('AIEO_Signal_Envelope') && AIEO_Signal_Envelope::on() ? AIEO_Signal_Envelope::stamp() : null; $sighting = null !== $env && class_exists('AIEO_Sighting') && AIEO_Sighting::on(); $seen_marks = $sighting ? ', %d, %s, %d, %s, %d, %d, %f' : ''; $no_place = 0; $rows = array(); $placeholders = array(); $has_set = false; $cap = 100; $i = 0; foreach ($events as $e) { if ($i++ >= $cap) { break; } if (!is_array($e)) { continue; } $type = isset($e['type']) ? (string) $e['type'] : ''; if (!in_array($type, self::RECO_EVENT_TYPES, true)) { continue; } $seen = null; if ('offer_sighting' === $type || 'offer_engaged' === $type) { if (!$sighting) { continue; } $seen = AIEO_Sighting::columns($e, $type); if (null === $seen) { $no_place++; continue; } } $target_pid = isset($e['target_pid']) ? (int) $e['target_pid'] : 0; if ($target_pid <= 0 && !in_array($type, self::RECO_NON_PRODUCT_EVENT_TYPES, true)) { continue; } $anchor_pid = isset($e['anchor_pid']) ? (int) $e['anchor_pid'] : 0; $target_type = isset($e['target_type']) ? substr(sanitize_key((string) $e['target_type']), 0, 20) : ''; $anchor_type = isset($e['anchor_type']) ? substr(sanitize_key((string) $e['anchor_type']), 0, 20) : ''; $surface = isset($e['surface']) ? sanitize_key((string) $e['surface']) : 'unknown'; $event_id = isset($e['event_id']) ? (string) $e['event_id'] : ''; if ($event_id === '' || strlen($event_id) > 36 || stripos($event_id, 'eic-') === 0) { $event_id = $this->generate_uuid_v4(); } $offset_ms = isset($e['ts_offset_ms']) ? (int) $e['ts_offset_ms'] : 0; if ($offset_ms < 0) { $offset_ms = 0; } if ($offset_ms > 6 * HOUR_IN_SECONDS * 1000) { $offset_ms = 0; } $ts = gmdate('Y-m-d H:i:s', $request_time - intdiv($offset_ms, 1000)); if (null !== $env) { $ts = gmdate('Y-m-d H:i:s', $request_time); } $position = isset($e['position']) ? max(0, min(999, (int) $e['position'])) : 0; $anchor_term_id = isset($e['anchor_term_id']) ? (int) $e['anchor_term_id'] : 0; $anchor_term_tax = isset($e['anchor_term_tax']) ? sanitize_key((string) $e['anchor_term_tax']) : ''; $allowed_tax = array('product_cat', 'product_tag', 'product_brand'); if ($anchor_term_tax !== '' && !in_array($anchor_term_tax, $allowed_tax, true)) { $anchor_term_tax = ''; $anchor_term_id = 0; } $reason = ''; if ('suppressed' === $type && isset($e['reason']) && is_scalar($e['reason'])) { $candidate = sanitize_key((string) $e['reason']); if (self::ensure_display_conditions() && in_array($candidate, AIEO_Display_Conditions::SUPPRESS_REASONS, true)) { $reason = $candidate; } } if (null !== $seen) { $reason = $seen['reason']; } $row_arm = $arm; if (self::ensure_display_conditions()) { $row_arm = AIEO_Display_Conditions::event_arm( $surface, $session_id, isset($e['arm']) ? $e['arm'] : '', $arm ); } $set = isset($e['set']) && is_string($e['set']) && 1 === preg_match('/^[0-9a-f]{12}$/D', $e['set']) ? $e['set'] : ''; if ('' !== $set) { $has_set = true; } $row = array( 'event_id' => $event_id, 'session_id' => $session_id, 'user_id' => $user_id, 'event_type' => $type, 'target_pid' => $target_pid, 'target_type' => $target_type, 'anchor_pid' => $anchor_pid, 'anchor_type' => $anchor_type, 'anchor_term_id' => $anchor_term_id, 'anchor_term_tax' => $anchor_term_tax, 'surface' => $surface, 'position' => $position, 'arm' => $row_arm, 'reason' => $reason, 'origin_id' => $origin_id, 'set_hash' => $set, 'ts' => $ts, ); if (null !== $env) { $row['visit_id'] = AIEO_Signal_Envelope::visit(isset($e['visit_id']) && is_string($e['visit_id']) ? $e['visit_id'] : ''); $row['consent'] = $env['consent']; $row['env_v'] = $env['env_v']; $row['client_ms'] = isset($e['client_ms']) && is_numeric($e['client_ms']) ? AIEO_Signal_Envelope::clamp_ms((int) $e['client_ms']) : 0; } if ($sighting) { $row += null !== $seen ? $seen : AIEO_Sighting::blank(); } $rows[] = $row; $placeholders[] = null !== $env ? '(%s, %s, %d, %s, %d, %s, %d, %s, %d, %s, %s, %d, %s, %s, %d, %s, %s, %d, %d, %d' . $seen_marks . ')' : '(%s, %s, %d, %s, %d, %s, %d, %s, %d, %s, %s, %d, %s, %s, %d, %s)'; } if (count($rows) < count($events)) { self::count_refusal('reco', empty($rows) ? 'empty' : 'skipped'); } if ($no_place > 0) { AIEO_Sighting::unknown_places($no_place); } if (empty($rows)) { return; } $with_set = $has_set && (int) get_option(self::RECO_SCHEMA_OPTION, 0) >= 8; list($sql, $values) = self::reco_statement($table, $rows, $placeholders, null !== $env, $sighting, $with_set); $written = $wpdb->query($wpdb->prepare($sql, $values)); if (false === $written && $with_set && self::reco_column_missing('set_hash')) { delete_option(self::RECO_SCHEMA_OPTION); list($sql, $values) = self::reco_statement($table, $rows, $placeholders, null !== $env, $sighting, false); $written = $wpdb->query($wpdb->prepare($sql, $values)); } if (false === $written) { self::count_refusal('reco', 'write'); return false; } return true; } private static function reco_statement($table, array $rows, array $placeholders, $enveloped, $sighting, $with_set) { $values = array(); foreach ($rows as $r) { $values[] = $r['event_id']; $values[] = $r['session_id']; $values[] = $r['user_id']; $values[] = $r['event_type']; $values[] = $r['target_pid']; $values[] = $r['target_type']; $values[] = $r['anchor_pid']; $values[] = $r['anchor_type']; $values[] = $r['anchor_term_id']; $values[] = $r['anchor_term_tax']; $values[] = $r['surface']; $values[] = $r['position']; $values[] = $r['arm']; $values[] = $r['reason']; $values[] = $r['origin_id']; $values[] = $r['ts']; if ($enveloped) { $values[] = $r['visit_id']; $values[] = $r['consent']; $values[] = $r['env_v']; $values[] = $r['client_ms']; } if ($sighting) { $values[] = $r['offer_id']; $values[] = $r['offer_store']; $values[] = $r['placement_id']; $values[] = $r['place']; $values[] = $r['n_served']; $values[] = $r['n_in_view']; $values[] = $r['progress']; } if ($with_set) { $values[] = $r['set_hash']; } } $env_cols = $enveloped ? ', visit_id, consent, env_v, client_ms' : ''; if ($sighting) { $env_cols .= ', offer_id, offer_store, placement_id, place, n_served, n_in_view, progress'; } $set_cols = ''; if ($with_set) { $set_cols = ', set_hash'; foreach ($placeholders as $k => $p) { $placeholders[$k] = substr($p, 0, -1) . ', %s)'; } } $sql = "INSERT IGNORE INTO {$table} (event_id, session_id, user_id, event_type, target_pid, target_type, anchor_pid, anchor_type, anchor_term_id, anchor_term_tax, surface, position, arm, reason, origin_id, ts{$env_cols}{$set_cols}) VALUES " . implode(', ', $placeholders); return array($sql, $values); } public static function resolve_origin_id($json) { if (!is_string($json) || '' === $json) { return 0; } if (strlen($json) > 2048) { return 0; } if (!self::ensure_display_origin()) { return 0; } $raw = json_decode($json, true); if (!is_array($raw)) { return 0; } return AIEO_Display_Origin::resolve_id(AIEO_Display_Origin::sanitize_tuple($raw)); } private static function ensure_display_origin() { if (class_exists('AIEO_Display_Origin')) { return true; } $local = __DIR__ . '/class-aieo-display-origin.php'; if (file_exists($local)) { require_once $local; } if (!class_exists('AIEO_Display_Origin') && defined('AIEO_DIR') && file_exists(AIEO_DIR . 'includes/class-aieo-display-origin.php')) { require_once AIEO_DIR . 'includes/class-aieo-display-origin.php'; } return class_exists('AIEO_Display_Origin'); } private static function ensure_display_conditions() { if (class_exists('AIEO_Display_Conditions')) { return true; } $local = __DIR__ . '/class-aieo-display-conditions.php'; if (file_exists($local)) { require_once $local; } if (!class_exists('AIEO_Display_Conditions') && defined('AIEO_DIR') && file_exists(AIEO_DIR . 'includes/class-aieo-display-conditions.php')) { require_once AIEO_DIR . 'includes/class-aieo-display-conditions.php'; } return class_exists('AIEO_Display_Conditions'); } private function insert_ignoring_a_repeat(array $row, array $formats) { global $wpdb; $marks = array(); $values = array(); foreach (array_values($row) as $i => $value) { if (null === $value) { $marks[] = 'NULL'; continue; } $marks[] = isset($formats[$i]) ? $formats[$i] : '%s'; $values[] = $value; } $table = self::get_table_name(); $done = $wpdb->query($wpdb->prepare("INSERT IGNORE INTO {$table} (`" . implode('`, `', array_keys($row)) . '`) VALUES (' . implode(', ', $marks) . ')', $values)); if (false === $done) { self::count_refusal('addcart', 'write'); return false; } if ((int) $done > 0) { return (int) $wpdb->insert_id; } AIEO_Signals::count('addcart', 'duplicate'); return (int) $wpdb->get_var($wpdb->prepare("SELECT id FROM {$table} WHERE event_id = %s", (string) $row['event_id'])); } public function log_event(array $args) { global $wpdb; $product_id = isset($args['product_id']) ? (int) $args['product_id'] : 0; if ($product_id <= 0) { self::count_refusal('addcart', 'product'); return false; } if ( class_exists('AIEO_Session_Tracker') && method_exists('AIEO_Session_Tracker', 'is_consent_blocking_writes') ) { if ( AIEO_Session_Tracker::is_consent_blocking_writes() ) { self::count_refusal('addcart', 'consent'); return false; } } $source = isset($args['source']) ? (string) $args['source'] : 'unknown'; if (!in_array($source, self::VALID_SOURCES, true)) { $source = 'unknown'; } $event_id = isset($args['event_id']) ? (string) $args['event_id'] : ''; if ($event_id === '' || strlen($event_id) > 36) { $event_id = $this->generate_uuid_v4(); } $session_id = $this->get_session_id(); $user_id = function_exists('get_current_user_id') ? (int) get_current_user_id() : 0; if ( $session_id !== '' && preg_match('/^[0-9]{1,3}$/', (string) $session_id) ) { if ( function_exists('aieo_debug_log') ) { aieo_debug_log( '[AIEO_Attribution] Numeric-trivial session_id "' . $session_id . '" — ST cookie may not have initialised yet. product_id=' . $product_id . ' source=' . $source ); } } $cart_position = 0; $session_id_clean = sanitize_text_field($session_id); if ($session_id_clean !== '') { $prev = (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM " . self::get_table_name() . " WHERE session_id = %s AND order_id = 0", $session_id_clean )); $cart_position = $prev + 1; } $source_meta = null; if (isset($args['source_meta'])) { if (is_array($args['source_meta'])) { $source_meta = wp_json_encode($args['source_meta']); } else { $decoded = json_decode((string) $args['source_meta'], true); if ($decoded !== null || $args['source_meta'] === 'null') { $source_meta = (string) $args['source_meta']; } else { $source_meta = wp_json_encode(array('raw' => (string) $args['source_meta'])); } } $source_meta = substr((string) $source_meta, 0, 8192); } $variation_id_int = isset($args['variation_id']) ? (int) $args['variation_id'] : 0; $badge_snapshot = self::capture_badge_context($product_id, $variation_id_int, $user_id); $badge_snapshot_json = wp_json_encode($badge_snapshot); if (!is_string($badge_snapshot_json)) { $badge_snapshot_json = null; } $row = array( 'event_id' => $event_id, 'session_id' => $session_id_clean, 'user_id' => $user_id, 'product_id' => $product_id, 'variation_id' => $variation_id_int, 'quantity' => isset($args['quantity']) ? max(1, (int) $args['quantity']) : 1, 'unit_price' => isset($args['unit_price']) ? (float) $args['unit_price'] : 0.0, 'source' => $source, 'source_pid' => isset($args['source_pid']) ? (int) $args['source_pid'] : 0, 'source_meta' => $source_meta, 'badge_context_snapshot' => $badge_snapshot_json, 'cart_position' => $cart_position, 'ts_added' => current_time('mysql', true), 'ga_sent' => 0, ); $formats = array('%s', '%s', '%d', '%d', '%d', '%d', '%f', '%s', '%d', '%s', '%s', '%d', '%s', '%d'); if (class_exists('AIEO_Signal_Envelope') && AIEO_Signal_Envelope::on()) { $row = $row + AIEO_Signal_Envelope::stamp(isset($args['visit_id']) && is_string($args['visit_id']) ? $args['visit_id'] : ''); $formats = array_merge($formats, array('%s', '%d', '%d')); } if (class_exists('AIEO_Signal_Facts', false) && AIEO_Signal_Facts::on()) { $row['writer'] = (isset($args['writer']) && 's' === $args['writer']) ? 's' : 'b'; $row['cart_item_key'] = isset($args['cart_item_key']) ? substr((string) preg_replace('/[^a-f0-9]+/', '', (string) $args['cart_item_key']), 0, 32) : ''; $formats = array_merge($formats, array('%s', '%s')); $met = AIEO_Signal_Facts::meet(self::get_table_name(), $row, isset($args['event_id']) && $event_id === (string) $args['event_id']); if ($met > 0) { return $met; } } if (class_exists('AIEO_Signals') && AIEO_Signals::on('intake')) { return $this->insert_ignoring_a_repeat($row, $formats); } $ok = $wpdb->insert(self::get_table_name(), $row, $formats); if ($ok === false) { self::count_refusal('addcart', false !== stripos((string) $wpdb->last_error, 'Duplicate') ? 'duplicate' : 'write'); return false; } return (int) $wpdb->insert_id; } public function on_order_processed($order_id, $posted_data) { $this->backfill_order_id($order_id); } public function on_store_api_order_processed($order) { if (is_object($order) && is_callable(array($order, 'get_id'))) { $this->backfill_order_id((int) $order->get_id()); } } public function on_thankyou($order_id) { $this->backfill_order_id((int) $order_id); } private function backfill_order_id($order_id) { if (!$order_id) { return; } global $wpdb; $order = function_exists('wc_get_order') ? wc_get_order($order_id) : null; if (!$order) { return; } $candidates = array(); $current = $this->get_session_id(); if ($current !== '') { $candidates[] = $current; } $cust_id = (int) $order->get_customer_id(); if ($cust_id > 0) { $candidates[] = (string) $cust_id; } $candidates = array_values(array_unique(array_filter($candidates))); $since = gmdate('Y-m-d H:i:s', time() - (7 * DAY_IN_SECONDS)); $table = self::get_table_name(); $order_created_ts = $order->get_date_created() ? $order->get_date_created()->getTimestamp() : time(); $window_since = gmdate('Y-m-d H:i:s', $order_created_ts - (2 * HOUR_IN_SECONDS)); $window_until = gmdate('Y-m-d H:i:s', $order_created_ts + (10 * MINUTE_IN_SECONDS)); $aieo_facts = class_exists('AIEO_Signal_Facts', false) && AIEO_Signal_Facts::on(); foreach ($aieo_facts ? array() : $order->get_items() as $item) { if (!is_a($item, 'WC_Order_Item_Product')) { continue; } $pid = (int) $item->get_product_id(); if ($pid <= 0) { continue; } $matched = 0; if (!empty($candidates)) { $placeholders = implode(',', array_fill(0, count($candidates), '%s')); $args = array_merge( array((int) $order_id), $candidates, array($pid, $since) ); $matched = (int) $wpdb->query($wpdb->prepare( "UPDATE {$table} SET order_id = %d, backfilled_at = UTC_TIMESTAMP() WHERE session_id IN ({$placeholders}) AND product_id = %d AND order_id = 0 AND ts_added >= %s", $args )); } if ($matched > 0) { continue; } $qty = max(1, (int) $item->get_quantity()); $ids_to_claim = $wpdb->get_col($wpdb->prepare( "SELECT id FROM {$table} WHERE product_id = %d AND order_id = 0 AND user_id = 0 AND session_id LIKE 't\_%%' AND ts_added >= %s AND ts_added <= %s ORDER BY ts_added DESC LIMIT %d", $pid, $window_since, $window_until, $qty )); if (!empty($ids_to_claim)) { $id_list = implode(',', array_map('intval', $ids_to_claim)); $wpdb->query($wpdb->prepare( "UPDATE {$table} SET order_id = %d, backfilled_at = UTC_TIMESTAMP() WHERE id IN ({$id_list})", (int) $order_id )); } } if (class_exists('AIEO_Subject_Resolver')) { $aieo_vk = $this->get_session_id(); if ('' !== $aieo_vk) { $aieo_sid = AIEO_Subject_Resolver::get_or_create_subject($aieo_vk); if ($aieo_sid > 0) { AIEO_Subject_Resolver::stamp_identity( $aieo_sid, (int) $order->get_customer_id(), (string) $order->get_billing_email() ); } } } } private function get_session_id() { $cookie = 'aieo_attr_sid'; $sid = self::visitor_id_of(isset($_COOKIE[$cookie]) ? wp_unslash($_COOKIE[$cookie]) : ''); if ('' !== $sid) { return $sid; } if (class_exists('AIEO_Session_Tracker') && method_exists('AIEO_Session_Tracker', 'is_consent_blocking_writes') && AIEO_Session_Tracker::is_consent_blocking_writes()) { return ''; } $sid = $this->generate_uuid_v4(); if (!headers_sent()) { setcookie($cookie, $sid, time() + (30 * DAY_IN_SECONDS), COOKIEPATH ? COOKIEPATH : '/', COOKIE_DOMAIN); } $_COOKIE[$cookie] = $sid; return $sid; } public static function read_visitor_key() { $sid = self::visitor_id_of(isset($_COOKIE['aieo_attr_sid']) ? wp_unslash($_COOKIE['aieo_attr_sid']) : ''); if ('' !== $sid) { return $sid; } return (string) self::instance()->get_session_id(); } private static function visitor_id_of($raw) { $raw = is_string($raw) ? trim($raw) : ''; if (class_exists('AIEO_Signal_Envelope')) { return AIEO_Signal_Envelope::visitor_id($raw); } return 1 === preg_match('/^[A-Za-z0-9_-]{1,64}$/D', $raw) ? $raw : ''; } private function generate_uuid_v4() { try { $data = random_bytes(16); $data[6] = chr(ord($data[6]) & 0x0f | 0x40); $data[8] = chr(ord($data[8]) & 0x3f | 0x80); return vsprintf('%s%s-%s-%s-%s-%s%s%s', str_split(bin2hex($data), 4)); } catch (Exception $e) { return function_exists('wp_generate_uuid4') ? wp_generate_uuid4() : str_replace('.', '', uniqid('aieo_', true)); } } const VALID_CHANNELS = array( 'web_push', 'newsletter', 'paid_google', 'paid_facebook', 'paid_microsoft', 'paid_tiktok', 'paid_other', 'organic_search', 'social', 'referral', 'direct', 'internal', ); const CANONICAL_CHANNELS = array( 'web_push', 'newsletter', 'paid_google', 'paid_facebook', 'paid_microsoft', 'paid_tiktok', 'paid_other', 'organic_search', 'social', 'referral', 'direct', 'internal', 'skroutz', 'shopflix', 'bestprice', 'admin', 'unknown', ); const PAID_MEDIUM_REGEX = 'cpc|ppc|paid|display|cpm'; private static function click_id_channels() { return array( 'gclid' => 'paid_google', 'gbraid' => 'paid_google', 'wbraid' => 'paid_google', 'fbclid' => 'paid_facebook', 'ttclid' => 'paid_tiktok', 'msclkid' => 'paid_microsoft', ); } private static function source_platform_map() { return array( 'fb' => array( 'paid_facebook', 'social' ), 'facebook' => array( 'paid_facebook', 'social' ), 'ig' => array( 'paid_facebook', 'social' ), 'instagram' => array( 'paid_facebook', 'social' ), 'meta' => array( 'paid_facebook', 'social' ), 'tiktok' => array( 'paid_tiktok', 'social' ), 'google' => array( 'paid_google', 'organic_search' ), 'googleads' => array( 'paid_google', 'paid_google' ), 'adwords' => array( 'paid_google', 'paid_google' ), 'bing' => array( 'paid_microsoft', 'organic_search' ), 'microsoft' => array( 'paid_microsoft', 'paid_microsoft' ), 'skroutz' => array( 'skroutz', 'skroutz' ), 'shopflix' => array( 'shopflix', 'shopflix' ), 'bestprice' => array( 'bestprice', 'bestprice' ), 'newsletter' => array( 'newsletter', 'newsletter' ), 'youtube' => array( 'paid_google', 'social' ), 'yahoo' => array( 'paid_other', 'organic_search' ), 'duckduckgo' => array( 'paid_other', 'organic_search' ), 'ecosia' => array( 'paid_other', 'organic_search' ), 'yandex' => array( 'paid_other', 'organic_search' ), 'outbrain' => array( 'paid_other', 'paid_other' ), 'taboola' => array( 'paid_other', 'paid_other' ), 'chatgpt' => array( 'referral', 'referral' ), 'perplexity' => array( 'referral', 'referral' ), 'vivapayments' => array( 'internal', 'internal' ), 'paypal' => array( 'internal', 'internal' ), ); } public static function canonical_source_token( $src ) { $s = strtolower( trim( (string) $src ) ); if ( '' === $s ) { return ''; } $s = preg_replace( '#^https?://#', '', $s ); $s = preg_replace( '#^(www|l|m|lm|business|search)\.#', '', $s ); $dot = strpos( $s, '.' ); if ( false !== $dot ) { $s = substr( $s, 0, $dot ); } return preg_replace( '/[^a-z0-9_]/', '', $s ); } public static function canonical_channel( $raw_channel, $utm_source = '', $utm_medium = '', $landing_url = '', $source_type = '' ) { $raw = strtolower( trim( (string) $raw_channel ) ); $med = strtolower( trim( (string) $utm_medium ) ); $type = strtolower( trim( (string) $source_type ) ); $url = strtolower( (string) $landing_url ); $tok = self::canonical_source_token( $utm_source ); if ( '' !== $url ) { foreach ( self::click_id_channels() as $param => $channel ) { if ( false !== strpos( $url, $param . '=' ) ) { return $channel; } } } $is_paid = ( '' !== $med && preg_match( '/' . self::PAID_MEDIUM_REGEX . '/', $med ) ); $map = self::source_platform_map(); if ( '' !== $tok && isset( $map[ $tok ] ) ) { return $map[ $tok ][ $is_paid ? 0 : 1 ]; } if ( 'email' === $med || 0 === strpos( $tok, 'newsletter' ) || 0 === strpos( $tok, 'email' ) ) { return 'newsletter'; } if ( in_array( $raw, self::CANONICAL_CHANNELS, true ) ) { return $raw; } if ( $is_paid ) { return 'paid_other'; } $raw_tok = self::canonical_source_token( $raw ); if ( '' !== $raw_tok && isset( $map[ $raw_tok ] ) ) { return $map[ $raw_tok ][1]; } switch ( $type ) { case 'organic': return 'organic_search'; case 'typein': return 'direct'; case 'referral': return 'referral'; case 'admin': return 'admin'; } if ( 'organic' === $raw ) { return 'organic_search'; } if ( 0 === strpos( $raw, 'aio' ) || 0 === strpos( $raw, 'paid_aio' ) || 'woo' === $raw ) { return 'internal'; } return 'unknown'; } public static function canonical_channel_sql( $raw, $src, $med, $entry, $type ) { $paid = self::PAID_MEDIUM_REGEX; $lmed = "LOWER(COALESCE({$med},''))"; $lraw = "LOWER(COALESCE({$raw},''))"; $ltyp = "LOWER(COALESCE({$type},''))"; $tok = function ( $expr ) { return "REGEXP_REPLACE(REGEXP_REPLACE(REGEXP_REPLACE(LOWER(COALESCE({$expr},''))," . "'^https?://',''),'^(www|l|m|lm|business|search)\\\\.',''),'\\\\..*$','')"; }; $stok = $tok( $src ); $rtok = $tok( $raw ); $sql = "CASE\n"; foreach ( self::click_id_channels() as $param => $channel ) { $sql .= " WHEN LOWER(COALESCE({$entry},'')) LIKE '%{$param}=%' THEN '{$channel}'\n"; } foreach ( self::source_platform_map() as $token => $pair ) { $sql .= " WHEN {$stok} = '{$token}' AND {$lmed} REGEXP '{$paid}' THEN '{$pair[0]}'\n"; $sql .= " WHEN {$stok} = '{$token}' THEN '{$pair[1]}'\n"; } $sql .= " WHEN {$lmed} = 'email' OR {$stok} LIKE 'newsletter%' OR {$stok} LIKE 'email%' THEN 'newsletter'\n"; $quoted = array(); foreach ( self::CANONICAL_CHANNELS as $c ) { $quoted[] = "'{$c}'"; } $sql .= " WHEN {$lraw} IN (" . implode( ',', $quoted ) . ") THEN {$lraw}\n"; $sql .= " WHEN {$lmed} REGEXP '{$paid}' THEN 'paid_other'\n"; foreach ( self::source_platform_map() as $token => $pair ) { $sql .= " WHEN {$rtok} = '{$token}' THEN '{$pair[1]}'\n"; } $sql .= " WHEN {$ltyp} = 'organic' THEN 'organic_search'\n"; $sql .= " WHEN {$ltyp} = 'typein' THEN 'direct'\n"; $sql .= " WHEN {$ltyp} = 'referral' THEN 'referral'\n"; $sql .= " WHEN {$ltyp} = 'admin' THEN 'admin'\n"; $sql .= " WHEN {$lraw} = 'organic' THEN 'organic_search'\n"; $sql .= " WHEN {$lraw} LIKE 'aio%' OR {$lraw} LIKE 'paid\\_aio%' OR {$lraw} = 'woo' THEN 'internal'\n"; $sql .= " ELSE 'unknown'\n END"; return $sql; } public static function canonical_utm_value( $value ) { return trim( str_replace( '+', ' ', (string) $value ) ); } public static function canonical_utm_value_sql( $col ) { return "NULLIF(TRIM(REPLACE(COALESCE({$col},''),'+',' ')),'')"; } public static function resolve_channel($landing_page, $referrer) { $out = array('channel' => 'direct', 'source' => '', 'campaign' => '', 'medium' => '', 'term' => '', 'content' => '', 'click_id' => '', 'ad_id' => '', 'adset_id' => '', 'ad_campaign_id' => ''); $landing_page = (string) $landing_page; $referrer = (string) $referrer; $qs = ''; if ($landing_page !== '') { $parts = wp_parse_url($landing_page); if (isset($parts['query'])) { $qs = (string) $parts['query']; } } $params = array(); if ($qs !== '') { parse_str($qs, $params); } $utm_source = isset($params['utm_source']) ? strtolower(trim((string) $params['utm_source'])) : ''; $utm_medium = isset($params['utm_medium']) ? strtolower(trim((string) $params['utm_medium'])) : ''; $utm_campaign = isset($params['utm_campaign']) ? trim((string) $params['utm_campaign']) : ''; $utm_term = isset($params['utm_term']) ? trim((string) $params['utm_term']) : ''; $utm_content = isset($params['utm_content']) ? trim((string) $params['utm_content']) : ''; $aieo_src = isset($params['aieo_src']) ? strtolower(trim((string) $params['aieo_src'])) : ''; $out['source'] = $utm_source; $out['campaign'] = $utm_campaign; $out['medium'] = $utm_medium; $out['term'] = $utm_term; $out['content'] = $utm_content; foreach (array('fbclid', 'gclid', 'gbraid', 'wbraid', 'msclkid', 'ttclid', 'twclid', 'li_fat_id', 'epik', 'rdt_cid', 'sc_cid', 'yclid', 'dclid', 'srsltid') as $__cid) { if (!empty($params[$__cid])) { $out['click_id'] = (string) $params[$__cid]; break; } } if (isset($params['ad_id']) && $params['ad_id'] !== '') { $out['ad_id'] = (string) $params['ad_id']; } if (isset($params['adset_id']) && $params['adset_id'] !== '') { $out['adset_id'] = (string) $params['adset_id']; } elseif (isset($params['adgroup_id']) && $params['adgroup_id'] !== '') { $out['adset_id'] = (string) $params['adgroup_id']; } elseif (isset($params['adgroupid']) && $params['adgroupid'] !== '') { $out['adset_id'] = (string) $params['adgroupid']; } if (isset($params['campaign_id']) && $params['campaign_id'] !== '') { $out['ad_campaign_id'] = (string) $params['campaign_id']; } elseif (isset($params['utm_id']) && $params['utm_id'] !== '') { $out['ad_campaign_id'] = (string) $params['utm_id']; } if ($aieo_src === 'push') { $out['channel'] = 'web_push'; if ($out['campaign'] === '' && !empty($params['aieo_dispatch_id'])) { $out['campaign'] = 'dispatch_' . (int) $params['aieo_dispatch_id']; } return $out; } if ($aieo_src === 'newsletter') { $out['channel'] = 'newsletter'; if ($out['campaign'] === '' && !empty($params['aieo_email_id'])) { $out['campaign'] = 'tnp_email_' . (int) $params['aieo_email_id']; } return $out; } if ($aieo_src === 'social') { $out['channel'] = 'social'; if ($out['source'] === '' && $utm_source !== '') { $out['source'] = $utm_source; } if ($out['campaign'] === '' && !empty($params['aieo_post_id'])) { $out['campaign'] = 'social_' . (int) $params['aieo_post_id']; } if ($out['medium'] === '') { $out['medium'] = ($utm_medium === 'paid_social') ? 'paid_social' : 'social'; } return $out; } if (!empty($params['gclid']) || !empty($params['gbraid']) || !empty($params['wbraid'])) { $out['channel'] = 'paid_google'; if ($out['medium'] === '') $out['medium'] = 'cpc'; return $out; } if (!empty($params['fbclid']) || (!empty($params['ad_id']) && !empty($params['adset_id']))) { $out['channel'] = 'paid_facebook'; if ($out['medium'] === '') $out['medium'] = 'cpc'; return $out; } if (!empty($params['msclkid'])) { $out['channel'] = 'paid_microsoft'; if ($out['medium'] === '') $out['medium'] = 'cpc'; return $out; } if (!empty($params['ttclid'])) { $out['channel'] = 'paid_tiktok'; if ($out['medium'] === '') $out['medium'] = 'paid_social'; return $out; } if (in_array($utm_medium, array('email', 'newsletter'), true)) { $out['channel'] = 'newsletter'; return $out; } if (in_array($utm_source, array('newsletter', 'tnp', 'mailchimp', 'klaviyo', 'sendgrid', 'sendinblue', 'brevo'), true)) { $out['channel'] = 'newsletter'; return $out; } if (in_array($utm_medium, array('cpc', 'paid', 'ppc', 'paid-social', 'paidsocial'), true)) { if (in_array($utm_source, array('google', 'adwords', 'google_ads', 'googleads'), true)) { $out['channel'] = 'paid_google'; return $out; } if (in_array($utm_source, array('facebook', 'meta', 'instagram', 'fb'), true)) { $out['channel'] = 'paid_facebook'; return $out; } if (in_array($utm_source, array('tiktok', 'tiktokads', 'tiktok_ads', 'tt'), true)) { $out['channel'] = 'paid_tiktok'; return $out; } $out['channel'] = 'paid_other'; return $out; } if ($utm_medium === 'social') { $out['channel'] = 'social'; return $out; } $ref_host = ''; if ($referrer !== '') { $rp = wp_parse_url($referrer); $ref_host = isset($rp['host']) ? strtolower((string) $rp['host']) : ''; } $own_host = ''; $hp = wp_parse_url((string) home_url('/')); if (isset($hp['host'])) { $own_host = strtolower((string) $hp['host']); } if ($ref_host === '') { $out['channel'] = 'direct'; return $out; } if ($own_host !== '' && $ref_host === $own_host) { $out['channel'] = 'direct'; return $out; } $search_hosts = array('google.', 'bing.com', 'duckduckgo.com', 'yahoo.', 'ecosia.org', 'brave.com', 'qwant.com', 'startpage.com', 'yandex.', 'baidu.com'); foreach ($search_hosts as $h) { if (strpos($ref_host, $h) !== false) { $out['channel'] = 'organic_search'; return $out; } } $social_hosts = array('facebook.com', 'fb.com', 'm.facebook.com', 'l.facebook.com', 'instagram.com', 'twitter.com', 'x.com', 't.co', 'tiktok.com', 'pinterest.', 'youtube.com', 'youtu.be', 'reddit.com', 'linkedin.com', 'lnkd.in', 'whatsapp.com', 'telegram.', 'discord.'); foreach ($social_hosts as $h) { if (strpos($ref_host, $h) !== false) { $out['channel'] = 'social'; return $out; } } $out['channel'] = 'referral'; return $out; } public static function backfill_session_channels($limit = 5000) { global $wpdb; $tbl = $wpdb->prefix . 'aieo_sessions_statics'; $rows = $wpdb->get_results($wpdb->prepare( "SELECT id, landing_page, referrer FROM {$tbl} WHERE entry_channel = '' ORDER BY id ASC LIMIT %d", (int) $limit ), ARRAY_A); if (empty($rows)) { return array('processed' => 0, 'by_channel' => array()); } $by = array(); $now = current_time('mysql', true); foreach ($rows as $r) { $resolved = self::resolve_channel((string) $r['landing_page'], (string) $r['referrer']); $campaign_str = mb_substr((string) $resolved['campaign'], 0, 120); $campaign_id = ($campaign_str !== '' && class_exists('AIEO_DMM_Campaigns')) ? AIEO_DMM_Campaigns::touch($campaign_str, (string) $resolved['channel']) : null; $update_data = array( 'entry_channel' => $resolved['channel'], 'entry_source' => mb_substr((string) (isset($resolved['source']) ? $resolved['source'] : ''), 0, 80), 'entry_campaign' => $campaign_str, 'entry_medium' => mb_substr((string) $resolved['medium'], 0, 40), 'entry_term' => mb_substr((string) $resolved['term'], 0, 80), 'entry_content' => mb_substr((string) $resolved['content'], 0, 80), 'entry_click_id' => mb_substr((string) (isset($resolved['click_id']) ? $resolved['click_id'] : ''), 0, 255), 'entry_ad_id' => mb_substr((string) (isset($resolved['ad_id']) ? $resolved['ad_id'] : ''), 0, 40), 'entry_adset_id' => mb_substr((string) (isset($resolved['adset_id']) ? $resolved['adset_id'] : ''), 0, 40), 'entry_ad_campaign_id' => mb_substr((string) (isset($resolved['ad_campaign_id']) ? $resolved['ad_campaign_id'] : ''), 0, 40), 'updated_at' => $now, ); $update_fmt = array('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s'); if ($campaign_id) { $update_data['entry_campaign_id'] = (int) $campaign_id; $update_fmt[] = '%d'; } $wpdb->update($tbl, $update_data, array('id' => (int) $r['id']), $update_fmt, array('%d')); $ch = $resolved['channel']; if (!isset($by[$ch])) { $by[$ch] = 0; } $by[$ch]++; } return array('processed' => count($rows), 'by_channel' => $by); } public static function reconcile_session_from_order($order_id) { $order_id = (int) $order_id; if ($order_id <= 0 || !function_exists('wc_get_order')) { return false; } $order = wc_get_order($order_id); if (!$order) { return false; } $sid = self::visitor_id_of((string) $order->get_meta('_aieo_attr_sid')); if ($sid === '') { return false; } global $wpdb; $tbl = $wpdb->prefix . 'aieo_sessions_statics'; $row = $wpdb->get_row($wpdb->prepare( "SELECT id, entry_channel, order_id FROM {$tbl} WHERE session_id = %s LIMIT 1", $sid ), ARRAY_A); if (!$row) { return false; } $landing = (string) $order->get_meta('_wc_order_attribution_session_entry'); $referrer = (string) $order->get_meta('_wc_order_attribution_referrer'); if ($landing === '') { $q = array(); $g = (string) $order->get_meta('_aieo_gclid'); if ($g !== '') { $q['gclid'] = $g; } foreach (array('utm_source', 'utm_medium', 'utm_campaign', 'utm_term', 'utm_content') as $u) { $v = (string) $order->get_meta('_wc_order_attribution_' . $u); if ($v !== '') { $q[$u] = $v; } } if (!empty($q)) { $landing = home_url('/?' . http_build_query($q)); } } $resolved = self::resolve_channel($landing, $referrer); $new_ch = (string) $resolved['channel']; $cur = (string) $row['entry_channel']; $do_channel = in_array($cur, array('', 'internal', 'direct'), true) && !in_array($new_ch, array('', 'internal', 'direct'), true); $campaign_str = ''; $data = array(); $fmt = array(); if ($do_channel) { $campaign_str = mb_substr((string) $resolved['campaign'], 0, 120); $data['entry_channel'] = $new_ch; $fmt[] = '%s'; $data['entry_source'] = mb_substr((string) $resolved['source'], 0, 80); $fmt[] = '%s'; $data['entry_campaign'] = $campaign_str; $fmt[] = '%s'; $data['entry_medium'] = mb_substr((string) $resolved['medium'], 0, 40); $fmt[] = '%s'; $data['entry_term'] = mb_substr((string) $resolved['term'], 0, 80); $fmt[] = '%s'; $data['entry_content'] = mb_substr((string) $resolved['content'], 0, 80); $fmt[] = '%s'; $data['entry_click_id'] = mb_substr((string) $resolved['click_id'], 0, 255); $fmt[] = '%s'; $data['entry_ad_id'] = mb_substr((string) $resolved['ad_id'], 0, 40); $fmt[] = '%s'; $data['entry_adset_id'] = mb_substr((string) $resolved['adset_id'], 0, 40); $fmt[] = '%s'; $data['entry_ad_campaign_id'] = mb_substr((string) $resolved['ad_campaign_id'], 0, 40); $fmt[] = '%s'; } if ((int) $row['order_id'] <= 0) { $data['order_id'] = $order_id; $fmt[] = '%d'; $data['order_total'] = (float) $order->get_total(); $fmt[] = '%f'; } if (empty($data)) { self::stamp_order_entry($order, $sid, true, true); return false; } $data['updated_at'] = current_time('mysql', true); $fmt[] = '%s'; $wpdb->update($tbl, $data, array('id' => (int) $row['id']), $fmt, array('%d')); if ($do_channel && $campaign_str !== '' && class_exists('AIEO_DMM_Campaigns')) { $cid = AIEO_DMM_Campaigns::touch($campaign_str, $new_ch); if ($cid) { $wpdb->update($tbl, array('entry_campaign_id' => (int) $cid), array('id' => (int) $row['id']), array('%d'), array('%d')); } } self::stamp_order_entry($order, $sid, true, true); return true; } const META_ENTRY_CHANNEL = '_aieo_entry_channel'; const META_ENTRY_CAMPAIGN = '_aieo_entry_campaign'; const ENTRY_REPAIR_HOOK = 'aieo_order_entry_repair'; const ENTRY_REPAIR_OPTION = 'aieo_order_entry_repair'; public static function stamp_entry_at_checkout($order) { self::stamp_order_entry($order, self::cookie_session_id(), false); } public static function stamp_entry_at_checkout_block($order) { self::stamp_order_entry($order, self::cookie_session_id(), true); } private static function cookie_session_id() { foreach (array('aieo_attr_sid', 'aieo_session_id') as $ck) { if (!empty($_COOKIE[$ck])) { $sid = self::visitor_id_of(wp_unslash($_COOKIE[$ck])); if ('' !== $sid) { return $sid; } } } return ''; } public static function stamp_order_entry($order, $sid, $save, $upgrade = false) { if (!is_a($order, 'WC_Order') || '' === (string) $sid) { return false; } $have = (string) $order->get_meta(self::META_ENTRY_CHANNEL); $weak = array('', 'direct', 'internal', 'unattributed'); if ('' !== $have && (!$upgrade || !in_array($have, $weak, true))) { return false; } global $wpdb; $tbl = $wpdb->prefix . 'aieo_sessions_statics'; if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $tbl)) !== $tbl) { return false; } $row = $wpdb->get_row($wpdb->prepare( "SELECT entry_channel, entry_source, entry_medium, entry_campaign, landing_page, referrer FROM {$tbl} WHERE session_id = %s LIMIT 1", (string) $sid ), ARRAY_A); if (!$row) { return false; } list($channel, $campaign) = self::entry_of_session($row); if ('' === $channel || ('' !== $have && in_array($channel, $weak, true))) { return false; } $order->update_meta_data(self::META_ENTRY_CHANNEL, $channel); if ('' !== $campaign) { $order->update_meta_data(self::META_ENTRY_CAMPAIGN, $campaign); } if ($save) { $order->save_meta_data(); } return true; } private static function entry_of_session(array $row) { $raw = (string) ($row['entry_channel'] ?? ''); $src = (string) ($row['entry_source'] ?? ''); $med = (string) ($row['entry_medium'] ?? ''); $campaign = (string) ($row['entry_campaign'] ?? ''); $landing = (string) ($row['landing_page'] ?? ''); $referrer = (string) ($row['referrer'] ?? ''); if ('' === $raw && ('' !== $landing || '' !== $referrer)) { $r = self::resolve_channel($landing, $referrer); $raw = (string) $r['channel']; $src = (string) ($r['source'] ?? ''); $med = (string) ($r['medium'] ?? ''); $campaign = '' !== $campaign ? $campaign : (string) $r['campaign']; } if ('' === $raw) { return array('', ''); } return array((string) self::canonical_channel($raw, $src, $med, $landing), mb_substr($campaign, 0, 120)); } public static function schedule_order_entry_repair() { if (is_array(get_option(self::ENTRY_REPAIR_OPTION))) { return; } update_option(self::ENTRY_REPAIR_OPTION, array('status' => 'pending', 'after' => 0, 'filled' => 0), false); aieo_schedule_at_init(static function () { if (!wp_next_scheduled(self::ENTRY_REPAIR_HOOK)) { wp_schedule_single_event(time() + 2 * MINUTE_IN_SECONDS, self::ENTRY_REPAIR_HOOK); } }); } public static function repair_order_entries($batch = 200) { global $wpdb; $state = get_option(self::ENTRY_REPAIR_OPTION); if (!is_array($state) || 'done' === ($state['status'] ?? '') || !function_exists('wc_get_order')) { return is_array($state) ? $state : array(); } $hpos = class_exists('AIEO_Order_Store') && AIEO_Order_Store::uses_hpos(); $after = (int) ($state['after'] ?? 0); $batch = max(1, (int) $batch); $ids = $hpos ? $wpdb->get_col($wpdb->prepare("SELECT id FROM {$wpdb->prefix}wc_orders WHERE type = 'shop_order' AND id > %d ORDER BY id ASC LIMIT %d", $after, $batch)) : $wpdb->get_col($wpdb->prepare("SELECT ID FROM {$wpdb->posts} WHERE post_type = 'shop_order' AND ID > %d ORDER BY ID ASC LIMIT %d", $after, $batch)); $ids = array_map('intval', (array) $ids); if (empty($ids)) { $state['status'] = 'done'; update_option(self::ENTRY_REPAIR_OPTION, $state, false); return $state; } $in = implode(',', $ids); $meta_t = $hpos ? $wpdb->prefix . 'wc_orders_meta' : $wpdb->postmeta; $meta_k = $hpos ? 'order_id' : 'post_id'; $done = array_flip(array_map('intval', (array) $wpdb->get_col($wpdb->prepare( "SELECT {$meta_k} FROM {$meta_t} WHERE meta_key = %s AND meta_value <> '' AND {$meta_k} IN ({$in})", self::META_ENTRY_CHANNEL )))); $found = array(); $attr = $wpdb->prefix . 'aieo_order_attribution'; if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $attr)) === $attr) { $grp = $wpdb->get_var($wpdb->prepare('SHOW COLUMNS FROM `' . $attr . '` LIKE %s', 'channel_group')) ? "COALESCE(NULLIF(channel_group, ''), channel)" : 'channel'; foreach ((array) $wpdb->get_results("SELECT order_id, {$grp} AS ch, utm_campaign FROM `{$attr}` WHERE order_id IN ({$in})", ARRAY_A) as $r) { $found[(int) $r['order_id']] = array((string) $r['ch'], (string) $r['utm_campaign']); } } $sess = $wpdb->prefix . 'aieo_sessions_statics'; if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $sess)) === $sess) { $rows = $wpdb->get_results("SELECT order_id, entry_channel, entry_source, entry_medium, entry_campaign, landing_page, referrer FROM `{$sess}` WHERE order_id IN ({$in}) ORDER BY last_timestamp DESC, id DESC", ARRAY_A); foreach ((array) $rows as $r) { $oid = (int) $r['order_id']; list($ch, $cp) = self::entry_of_session($r); if (!isset($found[$oid])) { $found[$oid] = array($ch, $cp); } else { if ('' === $found[$oid][0]) { $found[$oid][0] = $ch; } if ('' === $found[$oid][1]) { $found[$oid][1] = $cp; } } } } $filled = 0; foreach ($ids as $oid) { if (isset($done[$oid]) || empty($found[$oid]) || '' === $found[$oid][0]) { continue; } $order = wc_get_order($oid); if (!$order) { continue; } list($ch, $cp) = $found[$oid]; $cp = mb_substr($cp, 0, 120); $order->update_meta_data(self::META_ENTRY_CHANNEL, $ch); if ('' !== $cp) { $order->update_meta_data(self::META_ENTRY_CAMPAIGN, $cp); } $order->save_meta_data(); self::fill_entry_copies($oid, $ch, $cp); $filled++; } $state['after'] = (int) end($ids); $state['filled'] = (int) ($state['filled'] ?? 0) + $filled; $state['status'] = count($ids) < $batch ? 'done' : 'running'; update_option(self::ENTRY_REPAIR_OPTION, $state, false); if ('running' === $state['status'] && !wp_next_scheduled(self::ENTRY_REPAIR_HOOK)) { wp_schedule_single_event(time() + MINUTE_IN_SECONDS, self::ENTRY_REPAIR_HOOK); } return $state; } private static function fill_entry_copies($order_id, $channel, $campaign) { global $wpdb; $cid = ('' !== $campaign && class_exists('AIEO_DMM_Campaigns')) ? (int) AIEO_DMM_Campaigns::id_for_slug($campaign) : 0; $oc = $wpdb->prefix . 'aieo_dmm_order_cancellations'; if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $oc)) === $oc) { $wpdb->query($wpdb->prepare("UPDATE `{$oc}` SET entry_channel = %s WHERE order_id = %d AND (entry_channel IS NULL OR entry_channel = '')", $channel, (int) $order_id)); if ($cid > 0) { $wpdb->query($wpdb->prepare("UPDATE `{$oc}` SET entry_campaign_id = %d WHERE order_id = %d AND entry_campaign_id IS NULL", $cid, (int) $order_id)); } } $oa = $wpdb->prefix . 'aieo_dmm_offer_attributions'; if ((string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $oa)) === $oa) { $wpdb->query($wpdb->prepare("UPDATE `{$oa}` SET entry_channel = %s WHERE order_id = %d AND entry_channel IN ('', 'unattributed')", $channel, (int) $order_id)); if ($cid > 0 && $wpdb->get_var("SHOW COLUMNS FROM `{$oa}` LIKE 'entry_campaign_id'")) { $wpdb->query($wpdb->prepare("UPDATE `{$oa}` SET entry_campaign_id = %d WHERE order_id = %d AND entry_campaign_id IS NULL", $cid, (int) $order_id)); } } } }