0 AND z.last_unit_cost IS NOT NULL THEN ROUND( ( z.sale_net - z.last_unit_cost ) / z.sale_net * 100, 1 ) END AS margin_pct FROM ( SELECT y.*, CASE WHEN y.sale_price IS NOT NULL AND y.last_vat_pct IS NOT NULL THEN ROUND( y.sale_price / ( 1 + y.last_vat_pct / 100 ), 2 ) END AS sale_net FROM ( SELECT COALESCE( NULLIF( ln.matched_variation_id, 0 ), ln.matched_product_id ) AS pid, MAX( ln.matched_product_id ) AS base_product_id, MAX( CONVERT( COALESCE( NULLIF( pv.product_name, '' ), NULLIF( pv.name, '' ), NULLIF( ln.product_snapshot, '' ), ln.description ) USING utf8mb4 ) ) AS product, MAX( CONVERT( COALESCE( NULLIF( ln.sku_snapshot, '' ), pv.sku ) USING utf8mb4 ) ) AS sku, MAX( CONVERT( COALESCE( NULLIF( t.name, '' ), NULLIF( doc.supplier_snapshot, '' ), doc.supplier_vat ) USING utf8mb4 ) ) AS supplier, COUNT( DISTINCT COALESCE( NULLIF( doc.supplier_vat, '' ), doc.supplier_snapshot ) ) AS supplier_count, COUNT( ln.id ) AS purchase_lines, SUM( CASE WHEN doc.doc_type = 'credit_note' THEN -COALESCE( ln.pieces, ln.qty ) ELSE COALESCE( ln.pieces, ln.qty ) END ) AS units_bought, SUM( CASE WHEN doc.doc_type = 'credit_note' THEN -ln.line_net ELSE ln.line_net END ) AS net_spend, CAST( SUBSTRING_INDEX( GROUP_CONCAT( COALESCE( ln.unit_cost, ln.line_net / NULLIF( ln.qty, 0 ) ) ORDER BY doc.doc_date DESC, ln.id DESC ), ',', 1 ) AS DECIMAL( 12, 4 ) ) AS last_unit_cost, CAST( SUBSTRING_INDEX( GROUP_CONCAT( ln.vat_pct ORDER BY doc.doc_date DESC, ln.id DESC ), ',', 1 ) AS DECIMAL( 5, 2 ) ) AS last_vat_pct, MAX( doc.doc_date ) AS last_bought, MAX( COALESCE( pv.price, pml.min_price ) ) AS sale_price FROM {prefix}aieo_dmm_invoice_lines ln JOIN {prefix}aieo_dmm_invoice_documents doc ON doc.id = ln.document_id LEFT JOIN {prefix}terms t ON t.term_id = doc.supplier_term_id LEFT JOIN {prefix}aieo_core_product_vitals pv ON pv.product_id = COALESCE( NULLIF( ln.matched_variation_id, 0 ), ln.matched_product_id ) LEFT JOIN {prefix}wc_product_meta_lookup pml ON pml.product_id = COALESCE( NULLIF( ln.matched_variation_id, 0 ), ln.matched_product_id ) WHERE doc.doc_type IN ( 'invoice', 'credit_note' ) AND ln.matched_product_id > 0 GROUP BY COALESCE( NULLIF( ln.matched_variation_id, 0 ), ln.matched_product_id ) ) y ) z "; return array( 'id' => 'erp_landed_cost_margin', 'label' => 'Landed cost vs sale price', 'description' => 'Every product the invoice scanner matched, with what the shop paid for it, the most recent landed unit cost, and the current sale price beside it — margin computed net against net, so VAT cannot flatter it.', 'footnote' => 'Landed cost prefers the scanner\'s own landed computation and falls back to the line\'s net divided by quantity, discounts included. The margin nets the gross sale price using the VAT rate from the product\'s latest purchase line, and stays blank where that rate could not be read — a guessed rate would fabricate a margin for exactly the weakest rows. Products bought from more than one supplier show one supplier and a count. Spend can be negative when credit notes outweigh invoices in the current view.', 'group' => 'eshop_insights', 'section' => 'sales_reports', 'icon' => 'banknote', 'tier' => 'pro', 'scope' => 'local', 'source' => 'sql', 'sql' => $aieo_lcm_sql, 'registry_id' => 'erp_landed_cost_margin.by_product', 'version' => '1', 'per_page' => 50, 'row_key' => 'pid', 'default_sort' => array( 'net_spend', 'desc' ), 'empty_state' => 'No scanned invoice line has been matched to a product yet. Match lines in the Invoice Scanner\'s review screen and the costs appear here beside the sale prices.', 'actions' => array( array( 'id' => 'edit', 'label' => 'Edit', 'intent' => array( 'name' => 'edit_product', 'key' => 'base_product_id' ), 'capability' => 'edit_products', ), ), 'columns' => array( array( 'key' => 'pid', 'label' => 'ID', 'type' => 'id', 'hidden' => true, ), array( 'key' => 'base_product_id', 'label' => 'Product ID', 'type' => 'id', 'hidden' => true, ), array( 'key' => 'product', 'label' => 'Product', 'type' => 'text', 'sortable' => true, 'searchable' => true, 'filter' => 'text', 'truncate' => 44, 'tooltip' => true, 'subline' => 'sku', ), array( 'key' => 'sku', 'label' => 'SKU', 'type' => 'code', 'searchable' => true, 'hidden' => true, ), array( 'key' => 'supplier', 'label' => 'Supplier', 'type' => 'text', 'sortable' => true, 'filter' => 'select', 'truncate' => 28, 'empty' => '—', ), array( 'key' => 'supplier_count', 'label' => 'Suppliers', 'type' => 'integer', 'width' => '70px', 'hidden' => true, ), array( 'key' => 'purchase_lines', 'label' => 'Buys', 'type' => 'integer', 'width' => '64px', 'sortable' => true, ), array( 'key' => 'units_bought', 'label' => 'Units', 'type' => 'integer', 'width' => '70px', 'sortable' => true, 'aggregate' => 'SUM', ), array( 'key' => 'net_spend', 'label' => 'Net spend', 'type' => 'money', 'sortable' => true, 'filter' => 'number_range', 'aggregate' => 'SUM', ), array( 'key' => 'last_unit_cost', 'label' => 'Latest unit cost', 'type' => 'money', 'sortable' => true, 'empty' => '—', ), array( 'key' => 'last_bought', 'label' => 'Last bought', 'type' => 'date', 'nowrap' => true, 'sortable' => true, 'filter' => 'date_range', 'empty' => '—', ), array( 'key' => 'sale_price', 'label' => 'Sale price', 'type' => 'money', 'sortable' => true, 'empty' => '—', 'header_tip' => 'The product\'s current price (gross, as displayed in the shop). Blank when the matched product no longer resolves to a live product.', ), array( 'key' => 'sale_net', 'label' => 'Sale price net', 'type' => 'money', 'hidden' => true, ), array( 'key' => 'last_vat_pct', 'label' => 'VAT rate', 'type' => 'percent', 'format' => array( 'precision' => 0 ), 'hidden' => true, ), array( 'key' => 'margin_pct', 'label' => 'Margin %', 'type' => 'percent', 'format' => array( 'precision' => 1 ), 'sortable' => true, 'filter' => 'number_range', 'empty' => '—', 'bands' => array( array( 'max' => 0, 'tone' => 'danger' ), array( 'max' => 15, 'tone' => 'warning' ), ), ), ), );