prefix . 'order_invoice_match_report_historical'; if ( (string) $wpdb->get_var( "SHOW TABLES LIKE '{$aieo_im_hist}'" ) === $aieo_im_hist ) { require_once dirname( __DIR__ ) . '/class-aieo-report-query.php'; $aieo_im_branches[] = $aieo_im_branch( 'order_invoice_match_report_historical', 'historical', AIEO_Report_Query::branch_site_time_expression( 'order_datetime' ) ); } $aieo_im_sql = implode( "\nUNION ALL\n", $aieo_im_branches ); return array( 'id' => 'insights_invoice_match', 'label' => 'Woo against ERP', 'description' => 'Every order set beside the ERP document that should match it, with what the two disagree about.', 'footnote' => 'The archive matched 79.30% of its rows overall, with a floor of 12.8% in one month — the rate was never "about 90% every month". Eight months were never reconciled at all (2025-09 through 2026-03, and 2026-06) and cannot appear. The ERP total repeats on every order one document covers, so it is deliberately never totalled; the Woo columns are. On archive rows, "Days apart" is the matcher\'s own stored arithmetic from when the archive was written.', 'group' => 'eshop_insights', 'section' => 'comparative_turnover', 'icon' => 'scale', 'tier' => 'pro', 'scope' => 'both', 'source' => 'sql', 'sql' => $aieo_im_sql, 'registry_id' => 'insights_t40.woo_vs_erp_last_month', 'version' => '1', 'per_page' => 50, 'row_key' => 'row_uid', 'default_sort' => array( 'order_datetime', 'desc' ), 'requires_hpos' => true, 'empty_state' => 'No orders have been reconciled against the ERP yet. This report fills once the matching run has processed its first month.', 'filter_bar' => array( 'first' => array( 'month_slot', 'match_status', 'invoice_branch', 'order_datetime' ) ), 'columns' => array( array( 'key' => 'row_uid', 'label' => 'Row', 'type' => 'code', 'hidden' => true, ), array( 'key' => 'month_slot', 'label' => 'Period', 'type' => 'badge', 'sortable' => true, 'filter' => 'select', 'hidden' => true, 'format' => array( 'map' => array( 'previous' => 'muted', 'current' => 'info', 'historical' => 'warning' ), 'labels' => array( 'previous' => 'Previous month', 'current' => 'Current month', 'historical' => 'Archive', ), ), ), array( 'key' => 'report_month', 'label' => 'Month', 'type' => 'text', 'sortable' => true, 'filter' => 'select', 'nowrap' => true, ), array( 'key' => 'order_id', 'label' => 'Order', 'type' => 'id', 'sortable' => true, 'searchable' => true, 'filter' => 'number', ), array( 'key' => 'order_datetime', 'label' => 'Ordered', 'type' => 'datetime', 'sortable' => true, 'filter' => 'date_range', 'nowrap' => true, ), array( 'key' => 'order_status', 'label' => 'Order status', 'type' => 'badge', 'sortable' => true, 'filter' => 'multiselect', 'format' => array( 'map' => array( 'completed' => 'success', 'processing' => 'info', 'on-hold' => 'warning', 'cancelled' => 'danger', 'refunded' => 'danger', 'failed' => 'danger', ), ), ), array( 'key' => 'customer_email', 'label' => 'Customer', 'type' => 'text', 'sortable' => true, 'searchable' => true, 'filter' => 'text', 'truncate' => 34, 'tooltip' => true, ), array( 'key' => 'shippingtype', 'label' => 'Shipping', 'type' => 'text', 'sortable' => true, 'filter' => 'multiselect', 'truncate' => 28, 'tooltip' => true, ), array( 'key' => 'woo_total', 'label' => 'Woo total', 'type' => 'money', 'sortable' => true, 'filter' => 'number_range', 'aggregate' => 'SUM', ), array( 'key' => 'woo_net_total', 'label' => 'Woo, goods only', 'type' => 'money', 'sortable' => true, 'filter' => 'number_range', 'aggregate' => 'SUM', ), array( 'key' => 'invoice_total', 'label' => 'ERP total', 'type' => 'money', 'sortable' => true, 'filter' => 'number_range', ), array( 'key' => 'match_status', 'label' => 'Decision', 'type' => 'badge', 'sortable' => true, 'filter' => 'multiselect', 'format' => array( 'map' => array( 'Matched' => 'success', 'Probable pickup' => 'warning', 'Not matched' => 'danger', ), ), ), array( 'key' => 'invoice_id', 'label' => 'ERP document', 'type' => 'id', 'sortable' => true, 'filter' => 'number', 'empty' => '', ), array( 'key' => 'invoice_code', 'label' => 'ERP code', 'type' => 'code', 'sortable' => true, 'searchable' => true, 'filter' => 'text', 'empty' => '', ), array( 'key' => 'invoice_date', 'label' => 'Invoiced', 'type' => 'datetime', 'sortable' => true, 'filter' => 'date_range', 'nowrap' => true, 'empty' => '', ), array( 'key' => 'date_diff_days', 'label' => 'Days apart', 'type' => 'integer', 'sortable' => true, 'filter' => 'number_range', 'empty' => '', 'bands' => array( array( 'max' => -1, 'tone' => 'danger' ), ), ), array( 'key' => 'invoice_branch', 'label' => 'Issuing branch', 'type' => 'text', 'sortable' => true, 'filter' => 'multiselect', 'empty' => '', ), array( 'key' => 'woo_line_items', 'label' => 'Woo lines', 'type' => 'integer', 'sortable' => true, 'hidden' => true, ), array( 'key' => 'invoice_line_items', 'label' => 'ERP lines', 'type' => 'integer', 'sortable' => true, 'hidden' => true, ), array( 'key' => 'woo_qty_sum', 'label' => 'Woo units', 'type' => 'decimal', 'format' => array( 'precision' => 0 ), 'sortable' => true, 'hidden' => true, ), array( 'key' => 'invoice_qty_sum', 'label' => 'ERP units', 'type' => 'decimal', 'format' => array( 'precision' => 0 ), 'sortable' => true, 'hidden' => true, ), array( 'key' => 'overlap_sku_count', 'label' => 'SKUs in common', 'type' => 'integer', 'sortable' => true, 'empty' => '', 'hidden' => true, ), array( 'key' => 'overlap_pct_vs_order', 'label' => 'Order covered', 'type' => 'percent', 'sortable' => true, 'filter' => 'number_range', 'hidden' => true, ), array( 'key' => 'overlap_pct_vs_invoice', 'label' => 'Document covered', 'type' => 'percent', 'sortable' => true, 'filter' => 'number_range', 'hidden' => true, ), array( 'key' => 'price_diff_pct', 'label' => 'Price gap', 'type' => 'percent', 'sortable' => true, 'filter' => 'number_range', 'hidden' => true, ), array( 'key' => 'notes', 'label' => 'Why', 'type' => 'text', 'searchable' => true, 'filter' => 'text', 'truncate' => 44, 'tooltip' => true, 'empty' => '', ), ), );