render_offer_effectiveness_card($nonce, $ajax); } private function render_offer_effectiveness_card($nonce, $ajax) { list($aieo_oe_open, $aieo_oe_close) = AIEO_DMM_UI::card(array( 'anchor' => 'offer-effectiveness-monthly-breakdown', 'icon' => 'calendar-days', 'title' => __('Monthly breakdown by offer type', 'ai-eshop-optimizer'), 'description' => __('What every offer you run actually costs and returns, month by month — gifts, discounts and free shipping side by side, from the attribution log written at order time.', 'ai-eshop-optimizer'), 'flush' => true, )); echo $aieo_oe_open; $aieo_oe_note = \Rooster\Admin\Guidance::text(__('Real P&L per offer per month. "Applied" = the value handed to the customer (gift retail × qty, discount amount forgone, etc.). "Cost" = what it actually cost us (COGS for gifts, the would-have-been carrier rate for free-shipping, the same amount for plain discounts). "Net" = applied minus cost — the indicative revenue impact. Free-shipping rows show "Pending" until the resolver back-fills the carrier cost (runs nightly at 03:30, or run it manually from Performance → Shipment cost reports).', 'ai-eshop-optimizer'), 'guidance', array('question' => __('What do Applied, Cost and Net mean?', 'ai-eshop-optimizer'))); echo $aieo_oe_note; $aieo_oe_measures = \Rooster\Admin\Guidance::text(__('What each offer brought is measured twice. "Orders" and "Order revenue" count every order an offer touched, once, with the order\'s whole total. "Product units" and "Product revenue" count only the offer\'s own products: the order lines it targets (for a gift, the products that earned it; for free shipping, the products of the rule that made the shipping free; for a coupon, the lines it discounted). Group by "Offer total" or "Month × Offer" to read one campaign a row; in a coarser grouping an order line that two offers target counts for both. Orders recorded before the product figures existed show none.', 'ai-eshop-optimizer'), 'guidance', array('question' => __('What do the order and product figures count?', 'ai-eshop-optimizer'))); echo $aieo_oe_measures; echo '
'; $aieo_oe_month_options = array( 'current' => __('Current month', 'ai-eshop-optimizer'), 'last' => __('Last month', 'ai-eshop-optimizer'), ); foreach (array(3, 6, 12, 24, 36) as $m) { $aieo_oe_month_options[(string) $m] = sprintf(_n('Last %d month', 'Last %d months', $m, 'ai-eshop-optimizer'), $m); } $aieo_oe_months = \Rooster\Admin\Ui::select('', $aieo_oe_month_options, '12', array('id' => 'aieo-oe-months')); echo \Rooster\Admin\Ui::filter(__('Period:', 'ai-eshop-optimizer'), $aieo_oe_months, 'aieo-oe-months'); $aieo_oe_type = \Rooster\Admin\Ui::select('', array( '' => __('All', 'ai-eshop-optimizer'), 'free_shipping' => __('Free shipping', 'ai-eshop-optimizer'), 'gift' => __('Gift', 'ai-eshop-optimizer'), 'discount' => __('Discount', 'ai-eshop-optimizer'), ), '', array('id' => 'aieo-oe-type')); echo \Rooster\Admin\Ui::filter(__('Offer type:', 'ai-eshop-optimizer'), $aieo_oe_type, 'aieo-oe-type'); $aieo_oe_all = array('' => __('All', 'ai-eshop-optimizer')); echo \Rooster\Admin\Ui::filter(__('Channel:', 'ai-eshop-optimizer'), \Rooster\Admin\Ui::select('', $aieo_oe_all, '', array('id' => 'aieo-oe-channel')), 'aieo-oe-channel'); echo \Rooster\Admin\Ui::filter(__('Shipping method:', 'ai-eshop-optimizer'), \Rooster\Admin\Ui::select('', $aieo_oe_all, '', array('id' => 'aieo-oe-method')), 'aieo-oe-method'); echo \Rooster\Admin\Ui::filter(__('Shipping zone:', 'ai-eshop-optimizer'), \Rooster\Admin\Ui::select('', $aieo_oe_all, '', array('id' => 'aieo-oe-zone')), 'aieo-oe-zone'); $aieo_oe_group = \Rooster\Admin\Ui::select('', array( 'month_type' => __('Month × Offer type', 'ai-eshop-optimizer'), 'month_offer' => __('Month × Offer', 'ai-eshop-optimizer'), 'month_type_channel' => __('Month × Offer type × Channel', 'ai-eshop-optimizer'), 'month_type_method' => __('Month × Offer type × Shipping method', 'ai-eshop-optimizer'), 'month_type_zone' => __('Month × Offer type × Shipping zone', 'ai-eshop-optimizer'), 'month_method_zone' => __('Month × Method × Zone (free-shipping focus)', 'ai-eshop-optimizer'), 'month' => __('Month total', 'ai-eshop-optimizer'), 'type' => __('Offer type total', 'ai-eshop-optimizer'), 'offer' => __('Offer total', 'ai-eshop-optimizer'), 'method' => __('Shipping method total', 'ai-eshop-optimizer'), 'zone' => __('Shipping zone total', 'ai-eshop-optimizer'), ), 'month_type', array('id' => 'aieo-oe-group')); echo \Rooster\Admin\Ui::filter(__('Group by:', 'ai-eshop-optimizer'), $aieo_oe_group, 'aieo-oe-group'); echo '
' . '' . '' . '' . '' . '
'; echo '
'; echo '
'; \Rooster\Admin\States::enqueue(); $aieo_oe_head = \Rooster\Admin\States::result_head('', array('id' => 'aieo-oe-result', 'attrs' => array('hidden' => true))); echo $aieo_oe_head; $aieo_oe_loading = \Rooster\Admin\States::state('loading'); echo '
' . $aieo_oe_loading . '
'; $aieo_oe_first = \Rooster\Admin\States::state('first-use', array('title' => __('No attribution rows match the current filters. If you just enabled the module, place a few orders or run the retrospective free-shipping scan under Performance → Shipment cost reports.', 'ai-eshop-optimizer'))); echo ''; $aieo_oe_none = \Rooster\Admin\States::state('no-results', array('text' => '', 'clear' => '#')); echo ''; $aieo_oe_failed = \Rooster\Admin\States::state('failed', array('text' => __('Unknown error.', 'ai-eshop-optimizer'))); echo ''; $aieo_oe_row_one = _n('%s row', '%s rows', 1, 'ai-eshop-optimizer'); $aieo_oe_row_many = _n('%s row', '%s rows', 2, 'ai-eshop-optimizer'); AIEO_Table::table_assets(); echo ''; $cur = array( 'sym' => function_exists('get_woocommerce_currency_symbol') ? html_entity_decode(get_woocommerce_currency_symbol(), ENT_QUOTES, 'UTF-8') : '', 'pos' => get_option('woocommerce_currency_pos', 'right_space'), 'dec' => function_exists('wc_get_price_decimal_separator') ? wc_get_price_decimal_separator() : '.', 'thou' => function_exists('wc_get_price_thousand_separator') ? wc_get_price_thousand_separator() : ',', ); echo ''; echo $aieo_oe_close; } public function ajax_offer_effectiveness() { AIEO_DMM::verify_ajax_request(); if (!current_user_can('manage_woocommerce')) { wp_send_json_error(array('message' => __('Insufficient permissions.', 'ai-eshop-optimizer'))); } $payload = $this->compute_offer_effectiveness($_POST); $payload['table'] = $this->oe_table($payload); wp_send_json_success($payload); } private function oe_table($payload) { $cols = isset($payload['columns']) && is_array($payload['columns']) ? $payload['columns'] : array(); $columns = array(); foreach ($cols as $i => $col) { $num = in_array($col['fmt'], array('currency', 'net', 'integer'), true); $columns[] = array( 'key' => (string) $col['key'], 'label' => (string) $col['label'], 'type' => $num ? 'num' : 'text', 'sort_type' => $num ? 'number' : 'text', ) + (in_array($col['fmt'], array('currency', 'net'), true) ? array('format' => 'money') : array()) + ($i > 0 && 'offer_label' === $col['key'] ? array('width' => 'sm') : array()); } $rows = array(); foreach ((array) ($payload['rows'] ?? array()) as $i => $r) { $row = array('_id' => (string) ($i + 1), '_s' => array()); foreach ($cols as $col) { $k = (string) $col['key']; $v = $r[$k] ?? ''; if ('currency' === $col['fmt'] || 'net' === $col['fmt']) { $row[$k] = AIEO_Table_Columns::money($v); if ('net' === $col['fmt']) { $row[$k]['tone'] = (float) $v >= 0 ? 'success' : 'destructive'; } $row['_s'][$k] = (float) $v; } elseif ('integer' === $col['fmt']) { $row[$k] = number_format_i18n((int) $v); $row['_s'][$k] = (int) $v; } else { $row[$k] = (string) $v; $row['_s'][$k] = (string) $v; } } $rows[] = $row; } return AIEO_Table::local_answer('aieo-oe-table', $columns, $rows, array( 'prefix' => 'oe_', 'size' => 0, 'caption' => __('Offer effectiveness', 'ai-eshop-optimizer'), )); } public function ajax_offer_effectiveness_export() { AIEO_DMM::verify_ajax_request(); if (!current_user_can('manage_woocommerce')) { wp_die(__('Insufficient permissions.', 'ai-eshop-optimizer')); } $payload = $this->compute_offer_effectiveness($_GET); if (!class_exists('\Rooster\Admin\Export') && function_exists('rooster_admin_builders')) { rooster_admin_builders(); } if (!class_exists('\Rooster\Admin\Export')) { wp_die(esc_html__('XLSX writer unavailable.', 'ai-eshop-optimizer')); } $columns = array(); foreach ($payload['columns'] as $col) { $format = ($col['fmt'] === 'currency' || $col['fmt'] === 'net') ? 'money' : ($col['fmt'] === 'integer' ? array('decimals' => 0) : null); $columns[$col['key']] = null === $format ? array('label' => $col['label']) : array('label' => $col['label'], 'format' => $format); } $rows = $payload['rows']; $rows[] = array_merge(array( 'orders' => $payload['totals']['orders'] ?? 0, 'order_revenue' => $payload['totals']['order_revenue'] ?? 0, 'target_units' => $payload['totals']['target_units'] ?? 0, 'target_revenue' => $payload['totals']['target_revenue'] ?? 0, 'fires' => $payload['totals']['fires'], 'applied_total' => $payload['totals']['applied_total'], 'cost_total' => $payload['totals']['cost_total'], 'net' => $payload['totals']['applied_total'] - $payload['totals']['cost_total'], 'pending' => $payload['totals']['pending'], ), array((string) key($columns) => __('TOTAL', 'ai-eshop-optimizer'))); $months = (int) $payload['filters']['months']; $type_tag = $payload['filters']['offer_type'] !== '' ? '-' . $payload['filters']['offer_type'] : ''; $filename = 'offer-effectiveness-' . $months . 'mo' . $type_tag . '-' . gmdate('Y-m-d') . '.xlsx'; \Rooster\Admin\Export::xlsx($filename, array(array('name' => 'Offer effectiveness', 'columns' => $columns, 'rows' => $rows))); exit; } private function period_window_sql($period_value, $date_col) { $period = (string) $period_value; $cur_bounds = function_exists('aieo_site_month_bounds_utc') ? aieo_site_month_bounds_utc('current') : array('start' => gmdate('Y-m-01 00:00:00'), 'end' => gmdate('Y-m-01 00:00:00', strtotime('+1 month'))); $last_bounds = function_exists('aieo_site_month_bounds_utc') ? aieo_site_month_bounds_utc('last') : array('start' => gmdate('Y-m-01 00:00:00', strtotime('-1 month')), 'end' => gmdate('Y-m-01 00:00:00')); if ($period === 'current') { return array( 'sql' => "{$date_col} >= '{$cur_bounds['start']}' AND {$date_col} < '{$cur_bounds['end']}'", 'tag' => 'current-month', 'label' => 'current', 'months_for_compat' => 1, ); } if ($period === 'last') { return array( 'sql' => "{$date_col} >= '{$last_bounds['start']}' AND {$date_col} < '{$last_bounds['end']}'", 'tag' => 'last-month', 'label' => 'last', 'months_for_compat' => 1, ); } $n = max(1, min(60, (int) $period)); return array( 'sql' => "{$date_col} >= DATE_SUB(UTC_TIMESTAMP(), INTERVAL {$n} MONTH)", 'tag' => $n . 'mo', 'label' => (string) $n, 'months_for_compat' => $n, ); } private function compute_offer_effectiveness($input) { global $wpdb; $table = $wpdb->prefix . 'aieo_dmm_offer_attributions'; $period_in = isset($input['months']) ? sanitize_text_field((string) $input['months']) : '12'; $period = $this->period_window_sql($period_in, 'fired_at'); $months = $period['months_for_compat']; $offer_type = isset($input['offer_type']) ? sanitize_key((string) $input['offer_type']) : ''; $channel = isset($input['channel']) ? sanitize_text_field((string) $input['channel']) : ''; $group_by = isset($input['group_by']) ? sanitize_key((string) $input['group_by']) : 'month_type'; $valid_groups = array( 'month_type', 'month_offer', 'month_type_channel', 'month_type_method', 'month_type_zone', 'month_method_zone', 'month', 'type', 'offer', 'method', 'zone', ); if (!in_array($group_by, $valid_groups, true)) { $group_by = 'month_type'; } $by_offer = in_array($group_by, array('month_offer', 'offer'), true); $exists = (string) $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table)); if ($exists !== $table) { return array( 'rows' => array(), 'columns' => array(), 'channels' => array(), 'totals' => array('orders'=>0,'order_revenue'=>0,'target_units'=>0,'target_revenue'=>0,'fires'=>0,'applied_total'=>0,'cost_total'=>0,'pending'=>0), 'filters' => array('months'=>$months, 'offer_type'=>$offer_type, 'channel'=>$channel, 'group_by'=>$group_by), ); } switch ($group_by) { case 'month_type_channel': $group_select = "DATE_FORMAT(fired_at, '%Y-%m') AS month_key, offer_type, entry_channel"; $group_clause = 'month_key, offer_type, entry_channel'; $columns = array( array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'offer_type', 'label'=>__('Offer type', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'entry_channel', 'label'=>__('Channel', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'month_type_method': $group_select = "DATE_FORMAT(fired_at, '%Y-%m') AS month_key, offer_type, COALESCE(shipping_method, '—') AS shipping_method"; $group_clause = 'month_key, offer_type, shipping_method'; $columns = array( array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'offer_type', 'label'=>__('Offer type', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'shipping_method', 'label'=>__('Shipping method', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'month_type_zone': $group_select = "DATE_FORMAT(fired_at, '%Y-%m') AS month_key, offer_type, COALESCE(shipping_zone, '—') AS shipping_zone"; $group_clause = 'month_key, offer_type, shipping_zone'; $columns = array( array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'offer_type', 'label'=>__('Offer type', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'shipping_zone', 'label'=>__('Shipping zone', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'month_method_zone': $group_select = "DATE_FORMAT(fired_at, '%Y-%m') AS month_key, COALESCE(shipping_method, '—') AS shipping_method, COALESCE(shipping_zone, '—') AS shipping_zone"; $group_clause = 'month_key, shipping_method, shipping_zone'; $columns = array( array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'shipping_method', 'label'=>__('Shipping method', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'shipping_zone', 'label'=>__('Shipping zone', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'method': $group_select = "COALESCE(shipping_method, '—') AS shipping_method"; $group_clause = 'shipping_method'; $columns = array( array('key'=>'shipping_method', 'label'=>__('Shipping method', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'zone': $group_select = "COALESCE(shipping_zone, '—') AS shipping_zone"; $group_clause = 'shipping_zone'; $columns = array( array('key'=>'shipping_zone', 'label'=>__('Shipping zone', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'month': $group_select = "DATE_FORMAT(fired_at, '%Y-%m') AS month_key"; $group_clause = 'month_key'; $columns = array( array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'type': $group_select = "offer_type"; $group_clause = 'offer_type'; $columns = array( array('key'=>'offer_type', 'label'=>__('Offer type', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'offer': $group_select = "offer_type, offer_id, CASE WHEN offer_id = 0 THEN offer_label ELSE '' END AS offer_key"; $group_clause = 'offer_type, offer_id, offer_key'; $columns = array( array('key'=>'offer_label', 'label'=>__('Offer', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'offer_type', 'label'=>__('Offer type', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'month_offer': $group_select = "DATE_FORMAT(fired_at, '%Y-%m') AS month_key, offer_type, offer_id, CASE WHEN offer_id = 0 THEN offer_label ELSE '' END AS offer_key"; $group_clause = 'month_key, offer_type, offer_id, offer_key'; $columns = array( array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'offer_type', 'label'=>__('Offer type', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'offer_label', 'label'=>__('Offer', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; case 'month_type': default: $group_select = "DATE_FORMAT(fired_at, '%Y-%m') AS month_key, offer_type"; $group_clause = 'month_key, offer_type'; $columns = array( array('key'=>'month_key', 'label'=>__('Month', 'ai-eshop-optimizer'), 'fmt'=>'string'), array('key'=>'offer_type', 'label'=>__('Offer type', 'ai-eshop-optimizer'), 'fmt'=>'string'), ); break; } if ($group_by === 'month_method_zone' && $offer_type === '') { $offer_type = 'free_shipping'; } $columns[] = array('key'=>'orders', 'label'=>__('Orders', 'ai-eshop-optimizer'), 'fmt'=>'integer'); $columns[] = array('key'=>'order_revenue', 'label'=>__('Order revenue', 'ai-eshop-optimizer'), 'fmt'=>'currency'); $columns[] = array('key'=>'target_units', 'label'=>__('Product units', 'ai-eshop-optimizer'), 'fmt'=>'integer'); $columns[] = array('key'=>'target_revenue', 'label'=>__('Product revenue', 'ai-eshop-optimizer'), 'fmt'=>'currency'); $columns[] = array('key'=>'fires', 'label'=>__('Fires', 'ai-eshop-optimizer'), 'fmt'=>'integer'); $columns[] = array('key'=>'applied_total', 'label'=>__('Applied', 'ai-eshop-optimizer'), 'fmt'=>'currency'); $columns[] = array('key'=>'cost_total', 'label'=>__('Cost', 'ai-eshop-optimizer'), 'fmt'=>'currency'); $columns[] = array('key'=>'net', 'label'=>__('Net', 'ai-eshop-optimizer'), 'fmt'=>'net'); $columns[] = array('key'=>'pending', 'label'=>__('Pending cost', 'ai-eshop-optimizer'), 'fmt'=>'integer'); $where = array(); $params = array(); $where[] = '(' . $period['sql'] . ')'; if ($offer_type !== '' && in_array($offer_type, array('free_shipping','gift','discount','customer_pricing'), true)) { $where[] = 'offer_type = %s'; $params[] = $offer_type; } if ($channel !== '') { $where[] = 'entry_channel = %s'; $params[] = $channel; } $f_method = isset($input['shipping_method']) ? sanitize_text_field((string) $input['shipping_method']) : ''; $f_zone = isset($input['shipping_zone']) ? sanitize_text_field((string) $input['shipping_zone']) : ''; if ($f_method !== '') { $where[] = 'shipping_method = %s'; $params[] = $f_method; } if ($f_zone !== '') { $where[] = 'shipping_zone = %s'; $params[] = $f_zone; } $where_sql = $where ? ('WHERE ' . implode(' AND ', $where)) : ''; $has_measures = $this->log_has_measures($table); $units_expr = $has_measures ? 'target_units' : '0'; $revenue_expr = $has_measures ? 'target_revenue' : '0'; $sql = "SELECT {$group_clause}, SUM(fires) AS fires, SUM(applied_total) AS applied_total, SUM(cost_total) AS cost_total, SUM(pending) AS pending, COUNT(*) AS orders, SUM(order_total) AS order_revenue, SUM(target_units) AS target_units, SUM(target_revenue) AS target_revenue, MAX(last_row) AS last_row FROM ( SELECT {$group_select}, order_id, COUNT(*) AS fires, SUM(applied_value) AS applied_total, SUM(COALESCE(actual_cost, 0)) AS cost_total, SUM(actual_cost IS NULL) AS pending, MAX(order_total) AS order_total, SUM({$units_expr}) AS target_units, SUM({$revenue_expr}) AS target_revenue, MAX(attribution_id) AS last_row FROM {$table} {$where_sql} GROUP BY {$group_clause}, order_id ) AS per_order GROUP BY {$group_clause} ORDER BY " . $this->order_clause_for_group($group_by); $rows_raw = $wpdb->get_results($this->with_params($sql, $params), ARRAY_A); if (!is_array($rows_raw)) { $rows_raw = array(); } $names = $by_offer ? $this->offer_names($rows_raw, $table) : array(); $rows = array(); $totals = array('orders'=>0, 'order_revenue'=>0.0, 'target_units'=>0, 'target_revenue'=>0.0, 'fires'=>0, 'applied_total'=>0.0, 'cost_total'=>0.0, 'pending'=>0); foreach ($rows_raw as $i => $r) { $fires = (int) $r['fires']; $applied = (float) $r['applied_total']; $cost = (float) $r['cost_total']; $pending = (int) $r['pending']; $units = (int) $r['target_units']; $own = (float) $r['target_revenue']; $row = array( 'orders' => (int) $r['orders'], 'order_revenue' => round((float) $r['order_revenue'], 2), 'target_units' => $units, 'target_revenue' => round($own, 2), 'fires' => $fires, 'applied_total' => round($applied, 2), 'cost_total' => round($cost, 2), 'net' => round($applied - $cost, 2), 'pending' => $pending, ); if (isset($r['month_key'])) { $row['month_key'] = $r['month_key']; } if (isset($r['offer_type'])) { $row['offer_type'] = $r['offer_type']; } if ($by_offer) { $row['offer_label'] = $names[$i] ?? '—'; } if (isset($r['entry_channel'])) { $row['entry_channel'] = $r['entry_channel'] !== '' ? $r['entry_channel'] : '—'; } if (isset($r['shipping_method'])) { $row['shipping_method'] = $r['shipping_method'] !== '' ? $r['shipping_method'] : '—'; } if (isset($r['shipping_zone'])) { $row['shipping_zone'] = $r['shipping_zone'] !== '' ? $r['shipping_zone'] : '—'; } $rows[] = $row; $totals['target_units'] += $units; $totals['target_revenue'] += $own; $totals['fires'] += $fires; $totals['applied_total'] += $applied; $totals['cost_total'] += $cost; $totals['pending'] += $pending; } $totals['target_revenue'] = round($totals['target_revenue'], 2); $totals['applied_total'] = round($totals['applied_total'], 2); $totals['cost_total'] = round($totals['cost_total'], 2); $distinct = $wpdb->get_row($this->with_params( "SELECT COUNT(*) AS orders, COALESCE(SUM(order_total), 0) AS order_revenue FROM (SELECT order_id, MAX(order_total) AS order_total FROM {$table} {$where_sql} GROUP BY order_id) AS per_order", $params ), ARRAY_A); $totals['orders'] = (int) ($distinct['orders'] ?? 0); $totals['order_revenue'] = round((float) ($distinct['order_revenue'] ?? 0), 2); $period_clause = $period['sql']; $channels = $wpdb->get_col( "SELECT DISTINCT entry_channel FROM {$table} WHERE ({$period_clause}) AND entry_channel <> '' ORDER BY entry_channel ASC" ); if (!is_array($channels)) { $channels = array(); } $methods = $wpdb->get_col( "SELECT DISTINCT shipping_method FROM {$table} WHERE ({$period_clause}) AND shipping_method IS NOT NULL AND shipping_method <> '' ORDER BY shipping_method ASC" ); if (!is_array($methods)) { $methods = array(); } $zones = $wpdb->get_col( "SELECT DISTINCT shipping_zone FROM {$table} WHERE ({$period_clause}) AND shipping_zone IS NOT NULL AND shipping_zone <> '' ORDER BY shipping_zone ASC" ); if (!is_array($zones)) { $zones = array(); } return array( 'rows' => $rows, 'columns' => $columns, 'totals' => $totals, 'channels' => $channels, 'methods' => $methods, 'zones' => $zones, 'filters' => array( 'months' => $months, 'offer_type' => $offer_type, 'channel' => $channel, 'shipping_method' => isset($input['shipping_method']) ? sanitize_text_field((string) $input['shipping_method']) : '', 'shipping_zone' => isset($input['shipping_zone']) ? sanitize_text_field((string) $input['shipping_zone']) : '', 'group_by' => $group_by, ), ); } private function with_params($sql, array $params) { global $wpdb; return $params ? $wpdb->prepare($sql, $params) : $sql; } private function log_has_measures($table) { global $wpdb; return 2 === (int) $wpdb->get_var($wpdb->prepare( "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = %s AND COLUMN_NAME IN ('target_units', 'target_revenue')", $table )); } private function offer_names(array $rows_raw, $table) { global $wpdb; $last = array(); $gift = array(); foreach ($rows_raw as $r) { $last[(int) $r['last_row']] = ''; if ('gift' === $r['offer_type'] && (int) $r['offer_id'] > 0) { $gift[(int) $r['offer_id']] = ''; } } unset($last[0]); if ($last) { $found = $wpdb->get_results("SELECT attribution_id, offer_label FROM {$table} WHERE attribution_id IN (" . implode(',', array_keys($last)) . ')', ARRAY_A); foreach ((array) $found as $f) { $last[(int) $f['attribution_id']] = (string) $f['offer_label']; } } $gift_offers = $wpdb->prefix . 'aieo_dmm_brand_gift_offers'; if ($gift && function_exists('aieo_dmm_table_exists') && aieo_dmm_table_exists($gift_offers)) { $found = $wpdb->get_results("SELECT id, label FROM {$gift_offers} WHERE id IN (" . implode(',', array_keys($gift)) . ')', ARRAY_A); foreach ((array) $found as $f) { $gift[(int) $f['id']] = trim((string) $f['label']); } } $names = array(); foreach ($rows_raw as $i => $r) { $id = (int) $r['offer_id']; $name = ('gift' === $r['offer_type'] && !empty($gift[$id])) ? $gift[$id] : (string) ($last[(int) $r['last_row']] ?? ''); if ('' === $name) { $name = $id > 0 ? '#' . $id : '—'; } $names[$i] = $name; } return $names; } private function order_clause_for_group($group_by) { switch ($group_by) { case 'month_offer': return 'month_key DESC, offer_type ASC, orders DESC, offer_id ASC'; case 'offer': return 'offer_type ASC, orders DESC, offer_id ASC'; case 'month_type_channel': return 'month_key DESC, offer_type ASC, entry_channel ASC'; case 'month_type_method': return 'month_key DESC, offer_type ASC, shipping_method ASC'; case 'month_type_zone': return 'month_key DESC, offer_type ASC, shipping_zone ASC'; case 'month_method_zone': return 'month_key DESC, shipping_method ASC, shipping_zone ASC'; case 'method': return 'shipping_method ASC'; case 'zone': return 'shipping_zone ASC'; case 'month': return 'month_key DESC'; case 'type': return 'offer_type ASC'; case 'month_type': default: return 'month_key DESC, offer_type ASC'; } } }