views_table = $wpdb->prefix . 'statpl_product_totals'; $this->events_table = $wpdb->prefix . 'statpl_product_view_events'; $this->order_events_table = $wpdb->prefix . 'statpl_product_metric_events'; $this->order_items_table = $wpdb->prefix . 'statpl_order_product_snapshot'; register_activation_hook(__FILE__, [$this, 'activate']); add_action('plugins_loaded', [$this, 'init']); } public function activate() { global $wpdb; $previous_version = (string) get_option('statpl_db_version', ''); require_once ABSPATH . 'wp-admin/includes/upgrade.php'; $collate = $wpdb->get_charset_collate(); dbDelta("CREATE TABLE {$this->views_table} ( product_id BIGINT(20) UNSIGNED NOT NULL, total_views BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (product_id) ) $collate;"); dbDelta("CREATE TABLE {$this->events_table} ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, product_id BIGINT(20) UNSIGNED NOT NULL, viewed_at DATETIME NOT NULL, PRIMARY KEY (id), KEY product_time (product_id, viewed_at), KEY viewed_at (viewed_at) ) $collate;"); // Migrate the obsolete event index only when this table already exists. // On a fresh install, avoid issuing SHOW INDEX against a table that has not yet been created. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Schema detection is required before the conditional index migration; the identifier is built from the WordPress prefix and a fixed suffix. $events_table_exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $wpdb->esc_like($this->order_events_table))); if ($events_table_exists === $this->order_events_table) { // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- The identifier is built from the WordPress prefix and a fixed plugin suffix; this conditional migration checks the obsolete index. $old_index = $wpdb->get_var($wpdb->prepare("SHOW INDEX FROM {$this->order_events_table} WHERE Key_name = %s", 'order_product_event')); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.SchemaChange, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- The identifier is built from the WordPress prefix and a fixed plugin suffix; this conditional migration removes only the obsolete index. if ($old_index) { $wpdb->query("ALTER TABLE {$this->order_events_table} DROP INDEX order_product_event"); } } dbDelta("CREATE TABLE {$this->order_events_table} ( id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT, order_id BIGINT(20) UNSIGNED NOT NULL, product_id BIGINT(20) UNSIGNED NOT NULL, event_type VARCHAR(20) NOT NULL, event_ref_id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0, event_at DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY order_product_event_ref (order_id, product_id, event_type, event_ref_id), KEY product_event_time (product_id, event_type, event_at) ) $collate;"); dbDelta("CREATE TABLE {$this->order_items_table} ( order_id BIGINT(20) UNSIGNED NOT NULL, product_id BIGINT(20) UNSIGNED NOT NULL, order_status VARCHAR(24) NOT NULL, created_at DATETIME NULL, paid_at DATETIME NULL, completed_at DATETIME NULL, quantity DECIMAL(20,6) NOT NULL DEFAULT 0, net_quantity DECIMAL(20,6) NOT NULL DEFAULT 0, net_revenue DECIMAL(20,6) NOT NULL DEFAULT 0, has_refund TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (order_id, product_id), KEY product_status (product_id, order_status), KEY paid_at (paid_at), KEY completed_at (completed_at), KEY status_created (order_status, created_at) ) $collate;"); // Rebuild order snapshots once after an upgrade so completed_at and refund attribution // are populated consistently for orders already present in the store. if ($previous_version !== '' && version_compare($previous_version, self::VERSION, '<')) { update_option('statpl_import_complete', 0, false); update_option('statpl_import_page', 1, false); delete_transient('statpl_order_stats_v1'); delete_transient('statpl_global_summary_v2'); } update_option('statpl_db_version', self::VERSION); } public function init() { if (get_option('statpl_db_version') !== self::VERSION) { $this->activate(); } add_action('wp_enqueue_scripts', [$this, 'enqueue_frontend']); add_action('wp_ajax_statpl_record_view', [$this, 'record_view']); add_action('wp_ajax_nopriv_statpl_record_view', [$this, 'record_view']); add_action('admin_menu', [$this, 'admin_menu']); add_action('admin_enqueue_scripts', [$this, 'enqueue_admin_styles']); add_action('admin_notices', [$this, 'woocommerce_notice']); add_action('woocommerce_order_status_cancelled', [$this, 'record_cancel_event'], 10, 2); add_action('woocommerce_order_refunded', [$this, 'record_refund_event'], 10, 2); // Invalidate cached aggregates when order data or status changes. foreach (['woocommerce_new_order', 'woocommerce_update_order', 'woocommerce_order_status_changed', 'woocommerce_order_payment_status_changed', 'woocommerce_saved_order_items'] as $hook) { add_action($hook, [$this, 'invalidate_order_stats_cache'], 10, 3); } add_action('woocommerce_order_refunded', [$this, 'invalidate_order_stats_cache'], 20, 2); // A refund changes net quantity/revenue in the order snapshot as well as refund-event history. add_action('woocommerce_order_refunded', [$this, 'queue_order_snapshot'], 30, 2); add_action('woocommerce_before_delete_order', [$this, 'delete_order_snapshot'], 10, 1); foreach (['woocommerce_new_order','woocommerce_update_order','woocommerce_order_status_changed','woocommerce_order_payment_status_changed','woocommerce_saved_order_items'] as $hook) { add_action($hook, [$this, 'queue_order_snapshot'], 30, 3); } add_action('statpl_import_batch', [$this, 'run_import_batch']); add_action('statpl_sync_order', [$this, 'sync_order_snapshot'], 10, 1); if (!get_option('statpl_import_complete')) { $this->schedule_import_batch(); } } public function woocommerce_notice() { if (!current_user_can('manage_options') || class_exists('WooCommerce')) return; echo '

Statora Plus: WooCommerce is not active. Product/order statistics are unavailable.

'; } public function enqueue_frontend() { if (!function_exists('is_product') || !is_product()) return; $product_id = get_queried_object_id(); if (!$product_id) return; wp_enqueue_script('statpl-tracker', plugin_dir_url(__FILE__) . 'assets/tracker.js', [], self::VERSION, true); wp_localize_script('statpl-tracker', 'StatoraPlusData', [ 'ajaxUrl' => admin_url('admin-ajax.php'), 'nonce' => wp_create_nonce(self::NONCE_ACTION), 'productId' => (int) $product_id, ]); } public function record_view() { if (!isset($_POST['nonce'])) { wp_send_json_error(['message' => 'Invalid request.'], 403); } $nonce = sanitize_text_field(wp_unslash($_POST['nonce'])); if (!wp_verify_nonce($nonce, self::NONCE_ACTION)) { wp_send_json_error(['message' => 'Invalid nonce'], 403); } $product_id = isset($_POST['productId']) ? absint($_POST['productId']) : 0; if (!$product_id || get_post_type($product_id) !== 'product' || is_user_logged_in()) wp_send_json_success(); // Invalidate the dashboard aggregate because a newly recorded view changes its totals. delete_transient('statpl_global_summary_v2'); $cookie = 'statpl_view_' . $product_id; if (isset($_COOKIE[$cookie])) wp_send_json_success(['counted' => false]); global $wpdb; $now = current_time('mysql'); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- The identifier is built from the WordPress prefix and a fixed plugin suffix; this atomic view-count write must not be cached. $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Internal table identifier uses the WordPress prefix plus a fixed plugin suffix; product ID is prepared. "INSERT INTO {$this->views_table} (product_id, total_views) VALUES (%d, 1) ON DUPLICATE KEY UPDATE total_views = total_views + 1", $product_id )); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Event writes must be persisted immediately and cannot be cached. $wpdb->insert($this->events_table, ['product_id' => $product_id, 'viewed_at' => $now], ['%d','%s']); setcookie($cookie, '1', time() + 1200, COOKIEPATH ?: '/', COOKIE_DOMAIN, is_ssl(), true); $_COOKIE[$cookie] = '1'; // Keep only the 48-hour event window; lifetime totals remain in the totals table. $cutoff = gmdate('Y-m-d H:i:s', time() - 172800); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- The identifier is internal and the cutoff is prepared; this cleanup deletes expired rows, so caching is not applicable. $wpdb->query($wpdb->prepare("DELETE FROM {$this->events_table} WHERE viewed_at < %s", get_date_from_gmt($cutoff))); wp_send_json_success(['counted' => true]); } /** Record one order-level cancellation and product-level detail for affected products. */ public function record_cancel_event($order_id, $order = null) { $order = $order instanceof WC_Order ? $order : wc_get_order($order_id); if (!$order) return; global $wpdb; // Reuse the earliest recorded timestamp when reconciling an existing cancellation. // This prevents a later snapshot sync from making an older cancellation appear to happen today. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; the query values are prepared. $event_at = $wpdb->get_var($wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed plugin suffix; both query values are prepared. "SELECT MIN(event_at) FROM {$this->order_events_table} WHERE order_id = %d AND event_type = %s AND event_ref_id = 0", (int) $order->get_id(), 'cancelled' )); $this->record_order_event_products($order, 'cancelled', $event_at ?: null); } /** Determine whether the full monetary value of an order has been refunded. */ private function is_fully_refunded_amount($order) { if (!$order || !is_a($order, 'WC_Order')) return false; $order_total = max(0, (float) $order->get_total()); $refunded_total = max(0, (float) $order->get_total_refunded()); if ($order_total <= 0 || $refunded_total <= 0) return false; $decimals = function_exists('wc_get_price_decimals') ? wc_get_price_decimals() : 2; return round($refunded_total, $decimals) >= round($order_total, $decimals); } /** A full return requires both a full monetary refund and every line-item quantity returned. */ private function is_fully_refunded_order($order) { if (!$this->is_fully_refunded_amount($order)) return false; $items = $order->get_items('line_item'); if (!$items) return false; foreach ($items as $item_id => $item) { $quantity = abs((float) $item->get_quantity()); $refunded_quantity = abs((float) $order->get_qty_refunded_for_item($item_id)); if ($quantity <= 0 || ($refunded_quantity + 0.000001) < $quantity) { return false; } } return true; } /** A full refund without a recorded completion date is treated as a pre-completion cancellation. */ private function is_pre_completion_full_refund($order) { if (!$this->is_fully_refunded_amount($order)) return false; if ($order->get_date_completed()) return false; return (bool) $order->get_date_paid(); } /** Return distinct catalog product IDs represented by an order. */ private function order_product_ids($order) { $product_ids = []; if (!$order || !is_a($order, 'WC_Order')) return $product_ids; foreach ($order->get_items('line_item') as $item) { $product_id = (int) $item->get_product_id(); if (!$product_id) $product_id = (int) $item->get_variation_id(); if ($product_id) $product_ids[$product_id] = true; } return array_keys($product_ids); } /** Record one WooCommerce refund transaction globally and attribute it to known products. */ public function record_refund_event($order_id, $refund_id) { $order = wc_get_order($order_id); $refund = wc_get_order($refund_id); $refund_id = absint($refund_id); if (!$order || !$refund || !is_a($refund, 'WC_Order_Refund') || !$refund_id) return; if (abs((float) $refund->get_amount()) <= 0) return; // Do not count refunds for orders with no recorded payment or completion. if (!$order->get_date_paid() && !$order->get_date_completed()) return; $when = $refund->get_date_created(); $event_at = $when ? $when->date('Y-m-d H:i:s') : current_time('mysql'); $order_product_ids = $this->order_product_ids($order); $allowed_products = array_fill_keys($order_product_ids, true); $product_ids = []; foreach ($refund->get_items('line_item') as $item) { $product_id = (int) $item->get_product_id(); if (!$product_id) $product_id = (int) $item->get_variation_id(); if ($product_id && isset($allowed_products[$product_id])) { $product_ids[$product_id] = true; } } // Amount-only refunds can be attributed to a single-product order without guessing. // For a multi-product order, retain the order-level refund event but do not guess a product. if (!$product_ids && count($order_product_ids) === 1) { $product_ids[$order_product_ids[0]] = true; } // Attribute all products for a full pre-completion cancellation or a fully returned order. // A cancelled order with an amount-only refund is also attributed to all its products. if ( $this->is_fully_refunded_order($order) || $this->is_pre_completion_full_refund($order) || ($order->get_status() === 'cancelled' && !$product_ids) ) { $product_ids = $allowed_products; } global $wpdb; // Product ID 0 is the order-level event row. It prevents a multi-product order or refund // from being counted more than once in the store overview. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; all event values are prepared. $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed plugin suffix; all event values are prepared. "INSERT INTO {$this->order_events_table} (order_id, product_id, event_type, event_ref_id, event_at) VALUES (%d, 0, %s, %d, %s) ON DUPLICATE KEY UPDATE event_at=LEAST(event_at, VALUES(event_at))", (int) $order_id, 'refund', $refund_id, $event_at )); foreach (array_keys($product_ids) as $product_id) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; all event values are prepared. $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed plugin suffix; all event values are prepared. "INSERT INTO {$this->order_events_table} (order_id, product_id, event_type, event_ref_id, event_at) VALUES (%d, %d, %s, %d, %s) ON DUPLICATE KEY UPDATE event_at=LEAST(event_at, VALUES(event_at))", (int) $order_id, (int) $product_id, 'refund', $refund_id, $event_at )); } // A full return after completion or a full refund before completion is also a cancellation. // Partial refunds never create cancellation events. if ($this->is_fully_refunded_order($order) || $this->is_pre_completion_full_refund($order)) { $this->record_order_event_products($order, 'cancelled', $event_at); } delete_transient('statpl_order_stats_v1'); delete_transient('statpl_global_summary_v2'); } /** Record an order-level event once, plus one product-level row for each affected product. */ private function record_order_event_products($order, $event_type, $event_at = null) { global $wpdb; $event_at = $event_at ? $event_at : current_time('mysql'); // Product ID 0 is reserved for order-level dashboard aggregation. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; all event values are prepared. $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed plugin suffix; all event values are prepared. "INSERT INTO {$this->order_events_table} (order_id, product_id, event_type, event_ref_id, event_at) VALUES (%d, 0, %s, 0, %s) ON DUPLICATE KEY UPDATE event_at=LEAST(event_at, VALUES(event_at))", (int) $order->get_id(), $event_type, $event_at )); foreach ($this->order_product_ids($order) as $product_id) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; all event values are prepared. $wpdb->query($wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed plugin suffix; all event values are prepared. "INSERT INTO {$this->order_events_table} (order_id, product_id, event_type, event_ref_id, event_at) VALUES (%d, %d, %s, 0, %s) ON DUPLICATE KEY UPDATE event_at=LEAST(event_at, VALUES(event_at))", (int) $order->get_id(), (int) $product_id, $event_type, $event_at )); } delete_transient('statpl_order_stats_v1'); delete_transient('statpl_global_summary_v2'); } public function enqueue_admin_styles($hook_suffix) { if ('toplevel_page_statpl-product-metrics' !== $hook_suffix) { return; } wp_register_style('statpl-admin', false, [], self::VERSION); wp_enqueue_style('statpl-admin'); $css = <<<'STATPL_CSS' .statpl-table-scroll{overflow-x:auto} .statpl-stock-not-tracked{color:#646970} .statpl-metrics-wrap .nav-tab{border-color:#c9d7ff;background:#f2f6ff;color:#3155F5;font-weight:600} .statpl-metrics-wrap .nav-tab-active{background:#fff;color:#1d2327;border-bottom-color:#fff;border-top:3px solid #3155F5} .statpl-metrics-wrap a{color:#3155F5} .statpl-metrics-wrap .statpl-overview-rows{margin:18px 0 12px;background:#fff;border:1px solid #dbe5ff;border-radius:5px;overflow:hidden} .statpl-metrics-wrap .statpl-overview-row{display:grid;grid-template-columns:repeat(6,minmax(0,1fr));border-bottom:1px solid #dbe5ff} .statpl-metrics-wrap .statpl-overview-row:last-child{border-bottom:0} .statpl-metrics-wrap .statpl-overview-row.statpl-overview-three{grid-template-columns:repeat(3,minmax(0,1fr))} .statpl-metrics-wrap .statpl-overview-item{padding:13px 16px;min-width:0;border-right:1px solid #edf1fb} .statpl-metrics-wrap .statpl-overview-item:last-child{border-right:0} .statpl-metrics-wrap .statpl-overview-label{color:#505b6b;margin-bottom:7px;font-weight:600;line-height:1.35} .statpl-metrics-wrap .statpl-overview-value{font-size:22px;color:#263b8f;font-weight:700;line-height:1.25} .statpl-metrics-wrap .statpl-overview-row.statpl-overview-three .statpl-overview-item{grid-column:span 1} @media(max-width:900px){.statpl-metrics-wrap .statpl-overview-row{grid-template-columns:repeat(2,minmax(0,1fr))}.statpl-metrics-wrap .statpl-overview-row.statpl-overview-three{grid-template-columns:repeat(3,minmax(0,1fr))}} @media(max-width:600px){.statpl-metrics-wrap .statpl-overview-row,.statpl-metrics-wrap .statpl-overview-row.statpl-overview-three{grid-template-columns:1fr}.statpl-metrics-wrap .statpl-overview-item{border-right:0;border-bottom:1px solid #edf1fb}.statpl-metrics-wrap .statpl-overview-item:last-child{border-bottom:0}} .statpl-metrics-wrap .notice-success.inline{border-left-color:#3155F5;background:#f2f6ff} .statpl-metrics-wrap .notice-success.inline p{color:#263b8f} .statpl-metrics-wrap .statpl-overview-note{color:#505b6b} .statpl-metrics-wrap .statpl-metrics-toolbar{display:flex;justify-content:space-between;align-items:center;gap:16px;flex-wrap:wrap;margin:18px 0 12px}.statpl-metrics-wrap .statpl-sort-form{display:flex;align-items:center;gap:8px}.statpl-metrics-wrap .statpl-metrics-search{display:flex;justify-content:flex-end;align-items:center;gap:8px;margin:0}.statpl-metrics-wrap .statpl-metrics-search input[type=search]{width:min(375px,100%)}.statpl-metrics-wrap .statpl-metrics-search input[type=submit],.statpl-metrics-wrap .button-primary{background:#3155F5!important;border-color:#3155F5!important;color:#fff!important} .statpl-metrics-wrap .widefat{border-color:#c9d7ff;border-collapse:separate;border-spacing:0} .statpl-metrics-wrap .widefat thead th{background:#3155F5;color:#fff;border-color:#3155F5;padding:9px 8px;vertical-align:middle;line-height:1.3;font-weight:600;text-align:center} .statpl-metrics-wrap .widefat thead th:first-child,.statpl-metrics-wrap .widefat tbody td:first-child{text-align:left} .statpl-metrics-wrap .widefat thead th:not(:first-child),.statpl-metrics-wrap .widefat tbody td:not(:first-child){text-align:center} .statpl-metrics-wrap .widefat thead th a{color:#fff;text-decoration:none;display:block} .statpl-metrics-wrap .widefat tbody tr:nth-child(odd){background:#fff} .statpl-metrics-wrap .widefat tbody tr:nth-child(even){background:#f2f6ff} .statpl-metrics-wrap .widefat tbody td{padding:9px 8px;border-bottom:1px solid #dbe5ff;vertical-align:middle} .statpl-metrics-wrap td a.statpl-product-title{font-weight:600;text-decoration:none} STATPL_CSS; wp_add_inline_style('statpl-admin', $css); } public function admin_menu() { add_menu_page('Statora Plus', 'Statora Plus', 'manage_woocommerce', 'statpl-product-metrics', [$this, 'render_page'], 'dashicons-chart-bar', 56); } /** Bulk-load view counts for a product set to avoid two or more queries per product. */ /** Start of the current calendar day in the WordPress timezone. */ private function today_start_datetime() { return current_datetime()->setTime(0, 0, 0); } private function today_start_mysql() { return $this->today_start_datetime()->format('Y-m-d H:i:s'); } /** Return a displayable stock quantity without double-counting parent and variation stock. */ private function stock_summary_for_product($product) { if (!$product) return ['managed' => false, 'quantity' => null]; if ($product->managing_stock()) { $quantity = $product->get_stock_quantity(); return ['managed' => true, 'quantity' => is_numeric($quantity) ? (float) $quantity : 0.0]; } if ($product->is_type('variable')) { $managed_any = false; $total = 0.0; foreach ($product->get_children() as $variation_id) { $variation = wc_get_product($variation_id); if (!$variation || !$variation->managing_stock()) continue; $managed_any = true; $quantity = $variation->get_stock_quantity(); if (is_numeric($quantity)) $total += (float) $quantity; } if ($managed_any) return ['managed' => true, 'quantity' => $total]; } return ['managed' => false, 'quantity' => null]; } private function view_counts_for_products($product_ids) { global $wpdb; $ids = array_values(array_unique(array_filter(array_map('intval', $product_ids)))); $out = []; foreach ($ids as $id) { $out[$id] = ['total' => 0, 'today' => 0, 'source' => 'Statora Plus']; } if (!$ids) { return $out; } $placeholders = implode(',', array_fill(0, count($ids), '%d')); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; all query values are prepared. $totals = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated; the IN placeholders are generated from the count of integer IDs, and every ID is passed to prepare() as a value. "SELECT product_id, total_views FROM {$this->views_table} WHERE product_id IN ($placeholders)", $ids ), ARRAY_A ); foreach (is_array($totals) ? $totals : [] as $row) { $product_id = (int) $row['product_id']; if (isset($out[$product_id])) { $out[$product_id]['total'] = (int) $row['total_views']; } } $since = $this->today_start_mysql(); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; all query values are prepared. $recent = $wpdb->get_results( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated; the date and every product ID are passed to prepare() as values. The IN placeholders are generated from the count of integer IDs. "SELECT product_id, COUNT(*) AS views_today FROM {$this->events_table} WHERE viewed_at >= %s AND product_id IN ($placeholders) GROUP BY product_id", array_merge([$since], $ids) ), ARRAY_A ); foreach (is_array($recent) ? $recent : [] as $row) { $product_id = (int) $row['product_id']; if (isset($out[$product_id])) { $out[$product_id]['today'] = (int) $row['views_today']; } } return $out; } /** Queue a single-order refresh; the snapshot table makes page rendering independent of order-history scans. */ public function queue_order_snapshot($order_id, $arg2 = null, $arg3 = null) { $order_id = absint($order_id); if (!$order_id || !function_exists('wc_get_order')) return; $args = [$order_id]; if (function_exists('as_enqueue_async_action') && did_action('action_scheduler_init')) { // Avoid queuing duplicate refresh jobs for the same order. if (function_exists('as_has_scheduled_action') && as_has_scheduled_action('statpl_sync_order', $args, 'statpl-plus')) return; $action_id = as_enqueue_async_action('statpl_sync_order', $args, 'statpl-plus'); if ($action_id) return; } // Fall back to WP-Cron if Action Scheduler is unavailable or rejects the action. if (!wp_next_scheduled('statpl_sync_order', [$order_id])) { wp_schedule_single_event(time() + 5, 'statpl_sync_order', [$order_id]); } } /** Snapshot one order atomically enough for idempotent retries: delete its old rows then insert current rows. */ public function sync_order_snapshot($order_id) { if (!function_exists('wc_get_order')) return false; $order_id = absint($order_id); $order = wc_get_order($order_id); // A transient lookup failure must not erase a previously valid snapshot. // Deletion is handled explicitly by woocommerce_before_delete_order. if (!$order || is_a($order, 'WC_Order_Refund')) return false; global $wpdb; // Replace a snapshot atomically so readers never see a half-written order. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction control is required for an atomic snapshot replacement and cannot be cached. if ($wpdb->query('START TRANSACTION') === false) return false; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This write replaces the stored order snapshot inside a transaction; it must execute immediately and must not be cached. $deleted = $wpdb->delete($this->order_items_table, ['order_id' => $order_id], ['%d']); if ($deleted === false) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction rollback is required after a failed write; caching is not applicable. $wpdb->query('ROLLBACK'); return false; } $created = $order->get_date_created(); $paid = $order->get_date_paid(); $completed = $order->get_date_completed(); $created_at = $created ? $created->date('Y-m-d H:i:s') : null; $paid_at = $paid ? $paid->date('Y-m-d H:i:s') : null; $completed_at = $completed ? $completed->date('Y-m-d H:i:s') : null; $is_fully_refunded = $this->is_fully_refunded_order($order); $is_fully_refunded_amount = $this->is_fully_refunded_amount($order); $order_product_ids = $this->order_product_ids($order); $single_product_order = count($order_product_ids) === 1; $order_refunded_total = max(0, (float) $order->get_total_refunded()); $by_product = []; foreach ($order->get_items('line_item') as $item_id => $item) { $ids = array_values(array_unique(array_filter([(int)$item->get_product_id(), (int)$item->get_variation_id()]))); $qty = (float) $item->get_quantity(); $item_total = max(0, (float) $item->get_total()); $ref_qty = abs((float) $order->get_qty_refunded_for_item($item_id)); $ref_total = abs((float) $order->get_total_refunded_for_item($item_id)); if ($is_fully_refunded) { // A fully returned order has all item quantities refunded. $ref_qty = $qty; $ref_total = $item_total; } elseif ($is_fully_refunded_amount) { // A full monetary refund removes the product revenue even if the refund was // entered without item quantities. Physical return quantities remain unassumed. $ref_total = $item_total; } elseif ($single_product_order && $order_refunded_total > 0 && $ref_qty <= 0 && $ref_total <= 0) { // A single-product amount-only partial refund can be attributed to that product. $ref_total = min($item_total, $order_refunded_total); } foreach ($ids as $pid) { if (!isset($by_product[$pid])) $by_product[$pid] = ['quantity'=>0.0,'net_quantity'=>0.0,'net_revenue'=>0.0,'has_refund'=>0]; $by_product[$pid]['quantity'] += $qty; $by_product[$pid]['net_quantity'] += max(0, $qty - $ref_qty); $by_product[$pid]['net_revenue'] += max(0, $item_total - $ref_total); if ($ref_qty > 0 || $ref_total > 0) $by_product[$pid]['has_refund'] = 1; } } // Cancelled orders and full pre-completion refunds have no recognized sale. // Completed orders require recorded returned quantities for sold-unit reductions. if ( $order->get_status() === 'cancelled' || $is_fully_refunded || $this->is_pre_completion_full_refund($order) ) { foreach ($by_product as &$product_row) { $product_row['net_quantity'] = 0.0; } unset($product_row); } if ($order->get_status() === 'cancelled' || $is_fully_refunded_amount) { foreach ($by_product as &$product_row) { $product_row['net_revenue'] = 0.0; } unset($product_row); } foreach ($by_product as $pid => $row) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- These rows are the authoritative refreshed order snapshot; writes must execute immediately and must not be cached. $inserted = $wpdb->insert($this->order_items_table, [ 'order_id'=>(int)$order_id, 'product_id'=>(int)$pid, 'order_status'=>$order->get_status(), 'created_at'=>$created_at, 'paid_at'=>$paid_at, 'completed_at'=>$completed_at, 'quantity'=>$row['quantity'], 'net_quantity'=>$row['net_quantity'], 'net_revenue'=>$row['net_revenue'], 'has_refund'=>$row['has_refund'] ], ['%d','%d','%s','%s','%s','%s','%f','%f','%f','%d']); if ($inserted === false) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction rollback is required after a failed write; caching is not applicable. $wpdb->query('ROLLBACK'); return false; } } // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Transaction commit is required to persist the completed snapshot atomically. if ($wpdb->query('COMMIT') === false) { $wpdb->query('ROLLBACK'); return false; } // Reconcile cancellation events for orders already cancelled before this version. if ($order->get_status() === 'cancelled') { $this->record_cancel_event($order_id, $order); } // Reconcile refund events from WooCommerce refund objects. The refund ID makes // this safe when the refund hook and snapshot refresh both run for the same refund. foreach ($order->get_refunds() as $refund) { $this->record_refund_event($order_id, $refund->get_id()); } delete_transient('statpl_order_stats_v1'); // The dashboard cache must be invalidated after the snapshot write completes, // not only when the order hook queues this asynchronous refresh. delete_transient('statpl_global_summary_v2'); return true; } public function delete_order_snapshot($order_id) { global $wpdb; // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Deleting a removed order snapshot is a required write; caching is not applicable. $wpdb->delete($this->order_items_table, ['order_id'=>absint($order_id)], ['%d']); delete_transient('statpl_order_stats_v1'); delete_transient('statpl_global_summary_v2'); } /** Schedule one import batch with a delay to avoid overlapping batch execution. */ public function schedule_import_batch($delay = 10) { if (get_option('statpl_import_complete')) return; $delay = max(5, (int) $delay); if (function_exists('as_schedule_single_action') && !did_action('action_scheduler_init')) { add_action('action_scheduler_init', [$this, 'schedule_import_batch'], 10, 0); return; } if (function_exists('as_has_scheduled_action') && did_action('action_scheduler_init') && as_has_scheduled_action('statpl_import_batch', [], 'statpl-plus')) return; if (function_exists('as_schedule_single_action') && did_action('action_scheduler_init')) { $action_id = as_schedule_single_action(time() + $delay, 'statpl_import_batch', [], 'statpl-plus'); if ($action_id) return; } // Fall back to WP-Cron if Action Scheduler is unavailable or fails to enqueue. if (!wp_next_scheduled('statpl_import_batch')) { wp_schedule_single_event(time() + $delay, 'statpl_import_batch'); } } public function run_import_batch() { if (!function_exists('wc_get_orders') || get_option('statpl_import_complete')) return; $page = max(1, (int)get_option('statpl_import_page', 1)); $batch = wc_get_orders(['limit'=>50,'page'=>$page,'paginate'=>true,'type'=>'shop_order','status'=>array_map(static function($s){return preg_replace('/^wc-/','',$s);},array_keys(wc_get_order_statuses())),'orderby'=>'ID','order'=>'ASC','return'=>'objects']); if (!is_object($batch) || !isset($batch->orders)) { // Retry a transient query failure rather than leaving the import stuck forever. $this->schedule_import_batch(60); return; } // Do not advance the cursor or mark the import complete if any order snapshot failed. foreach ($batch->orders as $order) { if (!$this->sync_order_snapshot($order->get_id())) { $this->schedule_import_batch(60); return; } } if ($page < max(1,(int)$batch->max_num_pages)) { update_option('statpl_import_page', $page + 1, false); $this->schedule_import_batch(); } else { update_option('statpl_import_complete', 1, false); delete_option('statpl_import_page'); } } /** Invalidate cached order aggregates after order mutations. */ public function invalidate_order_stats_cache($arg1 = null, $arg2 = null, $arg3 = null) { delete_transient('statpl_order_stats_v1'); delete_transient('statpl_global_summary_v2'); } /** Aggregate order metrics for all catalog products; cache results to avoid scanning orders on every page load. */ private function product_stats_for_products($product_ids) { global $wpdb; $empty = ['orders'=>0,'orders24'=>0,'units'=>0,'units24'=>0,'revenue'=>0.0,'revenue24'=>0.0,'cancelled'=>0,'cancelled24'=>0,'refunds'=>0,'refunds24'=>0]; $ids = array_values(array_unique(array_filter(array_map('intval', $product_ids)))); $out = []; foreach ($ids as $id) $out[$id] = $empty; if (!$ids) return $out; $since = $this->today_start_mysql(); $placeholders = implode(',', array_fill(0, count($ids), '%d')); $sql = "SELECT product_id, COUNT(DISTINCT CASE WHEN order_status IN ('processing','completed') AND (net_quantity > 0 OR net_revenue > 0) THEN order_id END) AS orders_total, COUNT(DISTINCT CASE WHEN order_status IN ('processing','completed') AND created_at >= %s AND (net_quantity > 0 OR net_revenue > 0) THEN order_id END) AS orders_24, SUM(CASE WHEN order_status='completed' AND completed_at IS NOT NULL THEN net_quantity ELSE 0 END) AS units_total, SUM(CASE WHEN order_status='completed' AND completed_at >= %s THEN net_quantity ELSE 0 END) AS units_24, SUM(CASE WHEN order_status='completed' AND completed_at IS NOT NULL THEN net_revenue ELSE 0 END) AS revenue_total, SUM(CASE WHEN order_status='completed' AND completed_at >= %s THEN net_revenue ELSE 0 END) AS revenue_24 FROM {$this->order_items_table} WHERE product_id IN ($placeholders) GROUP BY product_id"; $args = array_merge([$since,$since,$since], $ids); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query is prepared with all variable values; table name is a class property built from the WordPress table prefix and fixed suffix; event counts must be current. $rows = $wpdb->get_results($wpdb->prepare($sql, $args), ARRAY_A); foreach ($rows as $r) { $id = (int)$r['product_id']; if (!isset($out[$id])) continue; $out[$id]['orders']=(int)$r['orders_total']; $out[$id]['orders24']=(int)$r['orders_24']; $out[$id]['units']=(float)$r['units_total']; $out[$id]['units24']=(float)$r['units_24']; $out[$id]['revenue']=(float)$r['revenue_total']; $out[$id]['revenue24']=(float)$r['revenue_24']; } // Cancellation and refund counts are event-based, not inferred from the current order status. $event_since = $this->today_start_mysql(); $event_sql = "SELECT product_id, COUNT(DISTINCT CASE WHEN event_type='cancelled' THEN order_id END) AS cancelled_total, COUNT(DISTINCT CASE WHEN event_type='cancelled' AND event_at >= %s THEN order_id END) AS cancelled_24, COUNT(DISTINCT CASE WHEN event_type='refund' AND event_ref_id > 0 THEN event_ref_id END) AS refunds_total, COUNT(DISTINCT CASE WHEN event_type='refund' AND event_ref_id > 0 AND event_at >= %s THEN event_ref_id END) AS refunds_24 FROM {$this->order_events_table} WHERE product_id IN ($placeholders) GROUP BY product_id"; $event_args = array_merge([$event_since, $event_since], $ids); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query is prepared with all variable values; table name is a class property built from the WordPress table prefix and fixed suffix; event counts must be current. $event_rows = $wpdb->get_results($wpdb->prepare($event_sql, $event_args), ARRAY_A); foreach ($event_rows as $event_row) { $id = (int)$event_row['product_id']; if (isset($out[$id])) { $out[$id]['cancelled'] = (int)$event_row['cancelled_total']; $out[$id]['cancelled24'] = (int)$event_row['cancelled_24']; $out[$id]['refunds'] = (int)$event_row['refunds_total']; $out[$id]['refunds24'] = (int)$event_row['refunds_24']; } } foreach ($ids as $id) { $out[$id]['revenue'] = round($out[$id]['revenue'], wc_get_price_decimals()); $out[$id]['revenue24'] = round($out[$id]['revenue24'], wc_get_price_decimals()); } return $out; } /** Build dashboard totals independently of the currently displayed product page. */ private function global_summary_metrics() { global $wpdb; $cached = get_transient('statpl_global_summary_v2'); if (is_array($cached)) return $cached; $since = $this->today_start_mysql(); $summary = [ // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This dashboard aggregate is cached by global_summary_metrics() for 60 seconds and invalidated when relevant data changes. 'products' => (int) $wpdb->get_var("SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type='product' AND post_status IN ('publish','private','draft','pending')"), 'views' => 0, 'views24' => 0, 'orders' => 0, 'orders24' => 0, 'units' => 0.0, 'units24' => 0.0, 'revenue' => 0.0, 'revenue24' => 0.0, 'cancelled' => 0, 'cancelled24' => 0, 'refunds' => 0, 'refunds24' => 0, ]; // All product views are recorded and aggregated by Statora Plus. // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is constructor-generated from the WordPress prefix and a fixed suffix; the dashboard aggregate is cached for 60 seconds and view recording invalidates that cache. $summary['views'] = (int) $wpdb->get_var("SELECT COALESCE(SUM(total_views),0) FROM {$this->views_table}"); // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; the date value is prepared. $summary['views24'] = (int) $wpdb->get_var( $wpdb->prepare( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; the date value is prepared. "SELECT COUNT(*) FROM {$this->events_table} WHERE viewed_at >= %s", $since ) ); // The table identifier is generated from the WordPress prefix and a fixed plugin-owned suffix; value placeholders are still prepared. // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- SQL identifiers cannot be bound as value placeholders; the identifier is internal, not user-controlled. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- This aggregate is cached by global_summary_metrics() for 60 seconds and invalidated on relevant changes. $sales = $wpdb->get_row($wpdb->prepare("SELECT COALESCE(SUM(CASE WHEN statpl_snapshot.order_status='completed' AND statpl_snapshot.completed_at IS NOT NULL THEN statpl_snapshot.net_quantity ELSE 0 END),0) AS units_total, COALESCE(SUM(CASE WHEN statpl_snapshot.order_status='completed' AND statpl_snapshot.completed_at >= %s THEN statpl_snapshot.net_quantity ELSE 0 END),0) AS units24, COALESCE(SUM(CASE WHEN statpl_snapshot.order_status='completed' AND statpl_snapshot.completed_at IS NOT NULL THEN statpl_snapshot.net_revenue ELSE 0 END),0) AS revenue_total, COALESCE(SUM(CASE WHEN statpl_snapshot.order_status='completed' AND statpl_snapshot.completed_at >= %s THEN statpl_snapshot.net_revenue ELSE 0 END),0) AS revenue24 FROM {$this->order_items_table} statpl_snapshot INNER JOIN {$wpdb->posts} statpl_parent ON statpl_parent.ID=statpl_snapshot.product_id AND statpl_parent.post_type='product'", $since, $since), ARRAY_A); // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter if ($sales) { $summary['units'] = (float) $sales['units_total']; $summary['units24'] = (float) $sales['units24']; $summary['revenue'] = (float) $sales['revenue_total']; $summary['revenue24'] = (float) $sales['revenue24']; } // Store-level figures count one cancellation per order and one return per refund record. // Product-level detail is calculated separately from rows with actual product IDs. // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed suffix; no user-supplied SQL values are used. $event_totals = $wpdb->get_row( // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- The table identifier is constructor-generated from $wpdb->prefix and a fixed plugin suffix; this aggregate has no user-supplied SQL values. "SELECT COUNT(DISTINCT CASE WHEN event_type='cancelled' AND product_id=0 THEN order_id END) AS cancelled_total, COUNT(DISTINCT CASE WHEN event_type='refund' AND product_id=0 AND event_ref_id > 0 THEN event_ref_id END) AS refunds_total FROM {$this->order_events_table}", ARRAY_A ); if ($event_totals) { $summary['cancelled'] = (int) $event_totals['cancelled_total']; $summary['refunds'] = (int) $event_totals['refunds_total']; } // Every normal order is represented once per product in the snapshot; count order IDs only once. // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is constructor-generated from the WordPress prefix and a fixed suffix.; Table identifier is internally generated from the WordPress prefix and a fixed suffix.; This aggregate is part of the 60-second cached dashboard summary. $summary['total_orders'] = (int) $wpdb->get_var("SELECT COUNT(DISTINCT order_id) FROM {$this->order_items_table}"); // Clean orders are currently processing/completed and retain some non-refunded product quantity/value. // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is constructor-generated from the WordPress prefix and a fixed suffix.; Table identifier is internally generated from the WordPress prefix and a fixed suffix.; This aggregate is part of the 60-second cached dashboard summary. $summary['orders'] = (int) $wpdb->get_var("SELECT COUNT(DISTINCT order_id) FROM {$this->order_items_table} WHERE order_status IN ('processing','completed') AND (net_quantity > 0 OR net_revenue > 0)"); // Today's clean orders are based on order creation date, not payment/completion date. // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is internally generated; date value is prepared.; This aggregate is part of the 60-second cached dashboard summary. $summary['orders24'] = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT order_id) FROM {$this->order_items_table} WHERE order_status IN ('processing','completed') AND created_at >= %s AND (net_quantity > 0 OR net_revenue > 0)", $since)); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is internally generated; date value is prepared.; This aggregate is part of the 60-second cached dashboard summary. $summary['cancelled24'] = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT order_id) FROM {$this->order_events_table} WHERE product_id=0 AND event_type='cancelled' AND event_at >= %s", $since)); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Table identifier is internally generated; date value is prepared.; This aggregate is part of the 60-second cached dashboard summary. $summary['refunds24'] = (int) $wpdb->get_var($wpdb->prepare("SELECT COUNT(DISTINCT event_ref_id) FROM {$this->order_events_table} WHERE product_id=0 AND event_type='refund' AND event_ref_id > 0 AND event_at >= %s", $since)); $summary['revenue'] = round($summary['revenue'], wc_get_price_decimals()); $summary['revenue24'] = round($summary['revenue24'], wc_get_price_decimals()); // A short TTL reduces repeated whole-store aggregate scans without hiding changes for long. set_transient('statpl_global_summary_v2', $summary, 60); return $summary; } public function render_page() { if (!current_user_can('manage_woocommerce')) wp_die(esc_html__('You do not have permission to access this page.', 'statora-plus')); if (!class_exists('WooCommerce')) { echo '

Στατιστικά προϊόντων

Απαιτείται ενεργό WooCommerce.

'; return; } // These query parameters only filter a read-only dashboard; no state-changing action is performed. // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only search filter; capability is checked above and input is sanitized. $search = isset($_GET['s']) ? sanitize_text_field(wp_unslash($_GET['s'])) : ''; // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only sort selection; capability is checked above and values are allowlisted below. $has_column_sort = isset($_GET['orderby']); // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only sort selection; value is sanitized and allowlisted below. $orderby = $has_column_sort ? sanitize_key(wp_unslash($_GET['orderby'])) : 'date'; // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only sort direction, constrained to ASC or DESC. $order = (isset($_GET['order']) && strtolower(sanitize_text_field(wp_unslash($_GET['order']))) === 'asc') ? 'ASC' : 'DESC'; $sort_options = [ 'date_desc' => ['date', 'DESC'], 'date_asc' => ['date', 'ASC'], 'title_asc' => ['title', 'ASC'], 'title_desc' => ['title', 'DESC'], 'views_desc' => ['views', 'DESC'], 'views24_desc' => ['views24', 'DESC'], 'orders_desc' => ['orders', 'DESC'], 'orders24_desc' => ['orders24', 'DESC'], 'units_desc' => ['units', 'DESC'], 'units24_desc' => ['units24', 'DESC'], 'revenue_desc' => ['revenue', 'DESC'], 'revenue24_desc' => ['revenue24', 'DESC'], 'cancelled_desc' => ['cancelled', 'DESC'], 'cancelled24_desc' => ['cancelled24', 'DESC'], 'refunds_desc' => ['refunds', 'DESC'], 'refunds24_desc' => ['refunds24', 'DESC'], ]; // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only sort choice; checked against the fixed $sort_options allowlist. $sort_choice = isset($_GET['sort']) ? sanitize_key(wp_unslash($_GET['sort'])) : 'date_desc'; if (!$has_column_sort && isset($sort_options[$sort_choice])) { [$orderby, $order] = $sort_options[$sort_choice]; } elseif ($has_column_sort) { $sort_choice = 'custom'; } else { $sort_choice = 'date_desc'; } $sortable = ['title','date','sku','views','views24','orders','orders24','units','units24','revenue','revenue24','cancelled','cancelled24','refunds','refunds24','stock']; if (!in_array($orderby, $sortable, true)) { $orderby = 'date'; $order = 'DESC'; $sort_choice = 'date_desc'; } // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only pagination; value is converted to an absolute integer. $page = max(1, isset($_GET['paged']) ? absint($_GET['paged']) : 1); $per_page = 25; $statuses = ['publish','private','draft','pending']; // phpcs:ignore WordPress.Security.NonceVerification.Recommended -- Read-only view switch, restricted to products or overview. $view = isset($_GET['view']) && sanitize_key(wp_unslash($_GET['view'])) === 'products' ? 'products' : 'overview'; $numeric_keys = ['views','views24','orders','orders24','units','units24','revenue','revenue24','cancelled','cancelled24','refunds','refunds24','stock']; // SQL-level filtering, ordering and pagination for catalog fields and grouped order/view metrics. $db_paged = ($view === 'products' && in_array($orderby, ['title','date','sku','stock','views','views24','orders','orders24','units','units24','revenue','revenue24','cancelled','cancelled24','refunds','refunds24'], true)); $total = 0; if ($db_paged) { global $wpdb; $status_placeholders = implode(',', array_fill(0, count($statuses), '%s')); $where = "p.post_type = 'product' AND p.post_status IN ($status_placeholders)"; $args = $statuses; if ($search !== '') { $like = '%' . $wpdb->esc_like($search) . '%'; $where .= " AND (p.post_title LIKE %s OR p.post_content LIKE %s OR sku.meta_value LIKE %s)"; array_push($args, $like, $like, $like); } $join = " LEFT JOIN {$wpdb->postmeta} sku ON (sku.post_id = p.ID AND sku.meta_key = '_sku')"; $order_columns = ['title'=>'p.post_title','date'=>'p.post_date','sku'=>'sku.meta_value','stock'=>'CAST(stock_meta.meta_value AS DECIMAL(20,6))']; if ($orderby === 'stock') { $join .= " LEFT JOIN {$wpdb->postmeta} stock_meta ON (stock_meta.post_id = p.ID AND stock_meta.meta_key = '_stock')"; } if (in_array($orderby, ['views', 'views24'], true)) { $since_mysql = $this->today_start_mysql(); $join .= " LEFT JOIN ( SELECT totals.product_id, totals.total_views AS views_total, COALESCE(recent.views_today, 0) AS views_today FROM {$this->views_table} totals LEFT JOIN ( SELECT product_id, COUNT(*) AS views_today FROM {$this->events_table} WHERE viewed_at >= " . $wpdb->prepare('%s', $since_mysql) . " GROUP BY product_id ) recent ON recent.product_id = totals.product_id ) statpl_views ON statpl_views.product_id = p.ID"; // Include recent events even if a lifetime totals row is missing. $join .= " LEFT JOIN ( SELECT product_id, COUNT(*) AS views_today FROM {$this->events_table} WHERE viewed_at >= " . $wpdb->prepare('%s', $since_mysql) . " GROUP BY product_id ) statpl_recent_views ON statpl_recent_views.product_id = p.ID"; $order_columns['views'] = 'COALESCE(statpl_views.views_total, 0)'; $order_columns['views24'] = 'COALESCE(statpl_views.views_today, statpl_recent_views.views_today, 0)'; } $metric_sort = in_array($orderby, ['orders','orders24','units','units24','revenue','revenue24','cancelled','cancelled24','refunds','refunds24'], true); if ($metric_sort) { $since = $this->today_start_mysql(); $join .= " LEFT JOIN ( SELECT product_id, COUNT(DISTINCT CASE WHEN order_status IN ('processing','completed') AND (net_quantity > 0 OR net_revenue > 0) THEN order_id END) AS orders, COUNT(DISTINCT CASE WHEN order_status IN ('processing','completed') AND created_at >= " . $wpdb->prepare('%s', $since) . " AND (net_quantity > 0 OR net_revenue > 0) THEN order_id END) AS orders24, SUM(CASE WHEN order_status='completed' AND completed_at IS NOT NULL THEN net_quantity ELSE 0 END) AS units, SUM(CASE WHEN order_status='completed' AND completed_at >= " . $wpdb->prepare('%s', $since) . " THEN net_quantity ELSE 0 END) AS units24, SUM(CASE WHEN order_status='completed' AND completed_at IS NOT NULL THEN net_revenue ELSE 0 END) AS revenue, SUM(CASE WHEN order_status='completed' AND completed_at >= " . $wpdb->prepare('%s', $since) . " THEN net_revenue ELSE 0 END) AS revenue24 FROM {$this->order_items_table} GROUP BY product_id ) statpl_orders ON statpl_orders.product_id = p.ID LEFT JOIN ( SELECT product_id, COUNT(DISTINCT CASE WHEN event_type='cancelled' THEN order_id END) AS cancelled, COUNT(DISTINCT CASE WHEN event_type='cancelled' AND event_at >= " . $wpdb->prepare('%s', $since) . " THEN order_id END) AS cancelled24, COUNT(DISTINCT CASE WHEN event_type='refund' AND event_ref_id > 0 THEN event_ref_id END) AS refunds, COUNT(DISTINCT CASE WHEN event_type='refund' AND event_ref_id > 0 AND event_at >= " . $wpdb->prepare('%s', $since) . " THEN event_ref_id END) AS refunds24 FROM {$this->order_events_table} GROUP BY product_id ) statpl_events ON statpl_events.product_id = p.ID"; $order_columns = array_merge($order_columns, array_combine(['orders','orders24','units','units24','revenue','revenue24','cancelled','cancelled24','refunds','refunds24'], array_map(static function($key) { return 'COALESCE(' . (in_array($key, ['cancelled','cancelled24','refunds','refunds24'], true) ? 'statpl_events.' : 'statpl_orders.') . $key . ',0)'; }, ['orders','orders24','units','units24','revenue','revenue24','cancelled','cancelled24','refunds','refunds24']))); } $count_sql = "SELECT COUNT(DISTINCT p.ID) FROM {$wpdb->posts} p {$join} WHERE {$where}"; // $join and $where are assembled only from fixed SQL fragments and prepared placeholders; user input is passed in $args. // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query fragments are plugin-controlled; all user-supplied values are bound through prepare(); product filters are evaluated live. $total = (int) $wpdb->get_var($wpdb->prepare($count_sql, $args)); $order_column = $order_columns[$orderby] ?? 'p.post_title'; $pages_for_query = max(1, (int) ceil($total / $per_page)); if ($page > $pages_for_query) $page = $pages_for_query; $offset = ($page - 1) * $per_page; $id_sql = "SELECT DISTINCT p.ID, {$order_column} AS statpl_sort_value FROM {$wpdb->posts} p {$join} WHERE {$where} ORDER BY statpl_sort_value {$order}, p.ID ASC LIMIT %d OFFSET %d"; $id_args = array_merge($args, [$per_page, $offset]); // $order_column is selected from the internal allowlist and $order is normalized before query construction. // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching -- Query fragments are plugin-controlled; all user-supplied values are bound through prepare(); product filters are evaluated live. $product_ids = array_map('intval', $wpdb->get_col($wpdb->prepare($id_sql, $id_args))); } else { $base_args = ['post_type'=>'product','post_status'=>$statuses,'posts_per_page'=>-1,'fields'=>'ids','orderby'=>'ID','order'=>'ASC']; // Search by product text OR partial SKU, then deduplicate before sorting and pagination. if ($search !== '') { $text_args = $base_args; $text_args['s'] = $search; $text_ids = get_posts($text_args); $sku_like = '%' . $wpdb->esc_like($search) . '%'; $sku_cache_key = 'statpl_sku_' . md5(wp_json_encode([$search, $statuses])); $sku_ids = wp_cache_get($sku_cache_key, 'statpl_plus'); if (false === $sku_ids) { // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery -- Prepared partial-SKU lookup is required; results are cached for 60 seconds. $sku_ids = $wpdb->get_col( $wpdb->prepare( "SELECT DISTINCT p.ID FROM {$wpdb->posts} p INNER JOIN {$wpdb->postmeta} statpl_product_meta ON statpl_product_meta.post_id = p.ID AND statpl_product_meta.meta_key = %s WHERE p.post_type = %s AND p.post_status IN (%s, %s, %s, %s) AND statpl_product_meta.meta_value LIKE %s ORDER BY p.ID ASC", '_sku', 'product', $statuses[0], $statuses[1], $statuses[2], $statuses[3], $sku_like ) ); $sku_ids = array_map('intval', $sku_ids); wp_cache_set($sku_cache_key, $sku_ids, 'statpl_plus', 60); } $product_ids = array_values(array_unique(array_map('intval', array_merge($text_ids, $sku_ids)))); } else { $product_ids = array_map('intval', get_posts($base_args)); } $total = count($product_ids); } $stats_by_product = $this->product_stats_for_products($product_ids); $views_by_product = $this->view_counts_for_products($product_ids); $rows = []; foreach ($product_ids as $product_id) { $product = wc_get_product($product_id); if (!$product) continue; $views = $views_by_product[$product_id] ?? ['total'=>0,'today'=>0,'source'=>'Statora Plus']; $st = $stats_by_product[$product_id] ?? ['orders'=>0,'orders24'=>0,'units'=>0,'units24'=>0,'revenue'=>0.0,'revenue24'=>0.0,'cancelled'=>0,'cancelled24'=>0,'refunds'=>0,'refunds24'=>0]; $stock_summary = $this->stock_summary_for_product($product); $rows[] = ['id'=>$product_id,'title'=>$product->get_name(),'date'=>get_post_field('post_date',$product_id),'sku'=>$product->get_sku(), 'views'=>(int)$views['total'],'views24'=>(int)$views['today'],'source'=>$views['source'],'stock_managed'=>$stock_summary['managed'],'stock'=>$stock_summary['quantity'],'edit_url'=>get_edit_post_link($product_id, 'raw')] + $st; } if (!$db_paged) { usort($rows, static function($a, $b) use ($orderby, $order, $numeric_keys) { if (in_array($orderby, $numeric_keys, true)) $cmp = ((float)$a[$orderby] <=> (float)$b[$orderby]); else { $av = (string)($a[$orderby] ?? ''); $bv = (string)($b[$orderby] ?? ''); $cmp = strnatcasecmp($av, $bv); } if ($cmp === 0) $cmp = ((int)$a['id'] <=> (int)$b['id']); return $order === 'DESC' ? -$cmp : $cmp; }); } $pages = max(1, (int)ceil($total / $per_page)); if ($page > $pages) $page = $pages; $visible_rows = $db_paged ? $rows : array_slice($rows, ($page - 1) * $per_page, $per_page); // Dashboard totals must represent the whole store, not only the current paginated result. $summary = $this->global_summary_metrics(); echo '

Statora Plus

Στατιστικά προϊόντων WooCommerce σε μία συνοπτική εικόνα.

'; $overview_url = add_query_arg(['page'=>'statpl-product-metrics','view'=>'overview'], admin_url('admin.php')); $products_url = add_query_arg(['page'=>'statpl-product-metrics','view'=>'products'], admin_url('admin.php')); echo ''; if (get_option('statpl_import_complete')) { echo '

Η αρχική εισαγωγή ιστορικού έχει επισημανθεί ως ολοκληρωμένη.

'; } else { $import_page = max(1, (int) get_option('statpl_import_page', 1)); echo '

Η αρχική εισαγωγή ιστορικού εκτελείται στο παρασκήνιο. Επεξεργασία παρτίδας: '.esc_html((string)$import_page).'. Τα στατιστικά ενδέχεται να είναι ελλιπή μέχρι να ολοκληρωθεί.

'; } if ($view === 'overview') { echo '

Συνολικά στατιστικά καταστήματος

'; // Group related metrics into compact rows instead of a wall of individual cards. $metric_rows = [ ['class'=>'statpl-overview-three','items'=>[ 'Προϊόντα'=>$summary['products'], 'Προβολές'=>$summary['views'], 'Προβολές σήμερα'=>$summary['views24'] ]], ['class'=>'statpl-overview-three','items'=>[ 'Συνολικές παραγγελίες'=>$summary['total_orders'], 'Καθαρές παραγγελίες'=>$summary['orders'], 'Παραγγελίες σήμερα'=>$summary['orders24'] ]], ['class'=>'','items'=>['Πωληθέντα τεμάχια'=>$summary['units'], 'Πωληθέντα τεμάχια σήμερα'=>$summary['units24']]], ['class'=>'','items'=>['Έσοδα χωρίς ΦΠΑ'=>$summary['revenue'], 'Έσοδα σήμερα'=>$summary['revenue24']]], ['class'=>'','items'=>['Ακυρώσεις'=>$summary['cancelled'], 'Ακυρώσεις σήμερα'=>$summary['cancelled24']]], ['class'=>'','items'=>['Επιστροφές χρημάτων'=>$summary['refunds'], 'Επιστροφές σήμερα'=>$summary['refunds24']]], ]; echo '
'; foreach ($metric_rows as $metric_row) { echo '
'; foreach ($metric_row['items'] as $label=>$value) { $is_unit_count = in_array($label, ['Πωληθέντα τεμάχια', 'Πωληθέντα τεμάχια σήμερα'], true); $formatted = (strpos($label, 'Έσοδα') === 0) ? wc_price((float)$value) : number_format_i18n($is_unit_count ? (int) round((float)$value) : $value, $is_unit_count ? 0 : (is_float($value) ? 2 : 0)); echo '
'.esc_html($label).'
'.wp_kses_post($formatted).'
'; } echo '
'; } echo '
'; echo '

Συνολικές παραγγελίες: Όλες οι παραγγελίες που έχουν δημιουργηθεί.

Καθαρές παραγγελίες: Παραγγελίες σε επεξεργασία ή ολοκληρωμένες, χωρίς ακύρωση ή πλήρη επιστροφή.

Παραγγελίες σήμερα: Καθαρές παραγγελίες που δημιουργήθηκαν σήμερα.

Πωληθέντα τεμάχια: Τεμάχια παραγγελιών που έχουν ολοκληρωθεί από τον καταστηματάρχη.

Έσοδα: Αναγνωρίζονται όταν η παραγγελία μεταφερθεί σε Ολοκληρωμένη, μετά την παραλαβή από τον πελάτη. Η ημερήσια μέτρηση βασίζεται στην ημερομηνία ολοκλήρωσης.

Η συνολική εικόνα μετρά μία ακύρωση ανά παραγγελία και μία επιστροφή ανά εγγραφή επιστροφής χρημάτων στο WooCommerce. Στα στατιστικά ανά προϊόν, τα συμβάντα αποδίδονται στα προϊόντα που αφορούνται. Μια απλήρωτη ακύρωση, όπως παραγγελία αντικαταβολής που δεν πληρώθηκε, μετρά μόνο ως ακύρωση. Πλήρης χρηματική επιστροφή πριν η παραγγελία σημειωθεί ως Ολοκληρωμένη μετρά και ως επιστροφή χρημάτων και ως ακύρωση, εφόσον έχει καταγραφεί πληρωμή· δεν απαιτείται να έχουν καταχωριστεί οι επιστρεφόμενες ποσότητες. Πλήρης επιστροφή μετά την Ολοκλήρωση μετρά και ως επιστροφή χρημάτων και ως ακύρωση μόνο όταν έχει επιστραφεί ολόκληρο το ποσό και έχουν καταχωριστεί ως επιστραφείσες όλες οι ποσότητες των προϊόντων. Μερική επιστροφή μετρά ως επιστροφή χρημάτων, όχι ως ακύρωση.

Απόθεμα: Η δέσμευση και η μείωση αποθέματος γίνονται από το WooCommerce. Το Statora Plus δεν μεταβάλλει το απόθεμα· αναγνωρίζει πωληθέντα τεμάχια και έσοδα μόνο όταν η παραγγελία γίνει Ολοκληρωμένη.

'; } else { echo '
'; echo '
'; echo '
'; if ($sort_choice === 'custom') echo ''; else echo ''; echo '
'; echo '
'; echo '
'; $columns = ['Προϊόν'=>'title','Απόθεμα'=>'stock','Προβολές'=>'views','Προβολές σήμερα'=>'views24','Παραγγελίες'=>'orders','Παραγγελίες σήμερα'=>'orders24','Πωληθέντα τεμάχια'=>'units','Πωληθέντα τεμάχια σήμερα'=>'units24','Έσοδα'=>'revenue','Έσοδα σήμερα'=>'revenue24','Ακυρώσεις'=>'cancelled','Ακυρώσεις σήμερα'=>'cancelled24','Επιστροφές χρημάτων'=>'refunds','Επιστροφές σήμερα'=>'refunds24']; foreach ($columns as $label=>$key) { $next_order = ($orderby === $key && $order === 'ASC') ? 'desc' : 'asc'; $url = add_query_arg(['page'=>'statpl-product-metrics','s'=>$search,'orderby'=>$key,'order'=>$next_order,'view'=>'products','paged'=>1], admin_url('admin.php')); $arrow = $orderby === $key ? ($order === 'ASC' ? ' ↑' : ' ↓') : ''; echo ''; } echo ''; if (!$visible_rows) echo ''; foreach ($visible_rows as $r) { echo ''; echo ''; echo ''; foreach (['orders','orders24'] as $k) echo ''; foreach (['units','units24'] as $k) echo ''; echo ''; foreach (['cancelled','cancelled24','refunds','refunds24'] as $k) echo ''; echo ''; } echo '
'.esc_html($label.$arrow).'
Δεν βρέθηκαν προϊόντα.
'.esc_html($r['title']).''.($r['stock_managed'] ? esc_html(number_format_i18n($r['stock'])) : 'Δεν παρακολουθείται').''.esc_html(number_format_i18n($r['views'])).''.esc_html(number_format_i18n($r['views24'])).''.esc_html(number_format_i18n($r[$k])).''.esc_html(number_format_i18n((int) round((float)$r[$k]), 0)).''.wp_kses_post(wc_price($r['revenue'])).''.wp_kses_post(wc_price($r['revenue24'])).''.esc_html(number_format_i18n($r[$k])).'
'; if ($pages > 1) echo '
'.wp_kses_post(paginate_links(['base'=>add_query_arg(['page'=>'statpl-product-metrics','view'=>'products','s'=>$search,'orderby'=>$orderby,'order'=>strtolower($order),'paged'=>'%#%'],admin_url('admin.php')),'format'=>'','current'=>$page,'total'=>$pages,'prev_text'=>'‹','next_text'=>'›'])).'
'; } echo '

Τα στοιχεία προορίζονται για παρακολούθηση και ανάλυση προϊόντων, όχι ως λογιστική αναφορά.

'.'
'; } } STATPL_Core::instance();