exec('ALTER TABLE products ADD COLUMN IF NOT EXISTS no_reorder TINYINT(1) NOT NULL DEFAULT 0'); } catch (Throwable $e) {} $rows = db()->query("SELECT p.*, s.name AS supplier, COALESCE(m.stock, 0) AS stock, COALESCE(o.on_order, 0) AS on_order, COALESCE(o.on_order_value, 0) AS on_order_value FROM products p LEFT JOIN suppliers s ON s.id = p.supplier_id LEFT JOIN (SELECT product_id, SUM(qty) AS stock FROM movements GROUP BY product_id) m ON m.product_id = p.id LEFT JOIN (SELECT l.product_id, SUM(l.qty) AS on_order, SUM(l.qty * l.unit_price) AS on_order_value FROM order_lines l JOIN orders o ON o.id = l.order_id WHERE o.status IN ('open','ready') GROUP BY l.product_id) o ON o.product_id = p.id WHERE " . product_scope() . ($all ? '' : ' AND p.active = 1') . " ORDER BY p.model, p.color")->fetchAll(); // Already in shipments that are not picked up yet (status To ship): still at the supplier, but no longer free $insh = []; foreach (db()->query("SELECT l.product_id, SUM(l.qty) AS q, GROUP_CONCAT(DISTINCT CONCAT(s.country, '-', s.fba_id) ORDER BY s.id SEPARATOR ', ') AS refs FROM shipment_lines l JOIN shipments s ON s.id = l.shipment_id WHERE s.status = 'new' GROUP BY l.product_id") as $x) $insh[(int)$x['product_id']] = $x; $hiddenNR = 0; if (!$all) $rows = array_values(array_filter($rows, function ($r) use (&$hiddenNR) { if (!empty($r['no_reorder']) && (int)$r['stock'] == 0 && (int)$r['on_order'] == 0) { $hiddenNR++; return false; } return true; })); // Average unit cost incl. freight per product, over all shipments with a known freight cost (freight spread by weight). $landed = []; if ($admin) { $lines = db()->query("SELECT l.shipment_id, l.product_id, l.qty, (CASE WHEN l.cartons > 0 THEN l.cartons ELSE l.qty / NULLIF(p.pcs_per_carton, 0) END) * COALESCE(p.carton_kg, 0) AS kg, COALESCE(NULLIF((SELECT SUM(c.unit_price * c.qty) FROM freight_costs c WHERE c.shipment_id = l.shipment_id), 0), f.quote_amount) AS freight FROM shipment_lines l JOIN shipments s ON s.id = l.shipment_id JOIN products p ON p.id = l.product_id LEFT JOIN freight f ON f.shipment_id = l.shipment_id WHERE s.status <> 'cancelled'")->fetchAll(); $sh = []; foreach ($lines as $l) { $k = (int)$l['shipment_id']; $sh[$k]['kg'] = ($sh[$k]['kg'] ?? 0) + (float)$l['kg']; $sh[$k]['qty'] = ($sh[$k]['qty'] ?? 0) + (int)$l['qty']; } $acc = []; foreach ($lines as $l) { if ($l['freight'] === null) continue; $k = (int)$l['shipment_id']; $pid = (int)$l['product_id']; $fr = $sh[$k]['kg'] > 0 ? (float)$l['freight'] * (float)$l['kg'] / $sh[$k]['kg'] : ($sh[$k]['qty'] ? (float)$l['freight'] * (int)$l['qty'] / $sh[$k]['qty'] : 0); $acc[$pid]['q'] = ($acc[$pid]['q'] ?? 0) + (int)$l['qty']; $acc[$pid]['f'] = ($acc[$pid]['f'] ?? 0) + $fr; } foreach ($acc as $pid => $a) if ($a['q'] > 0) $landed[$pid] = $a['f'] / $a['q']; // Products never shipped: estimate by weight with the average freight per kg of past shipments. $fk = 0.0; $kk = 0.0; $seen = []; foreach ($lines as $l) { $k = (int)$l['shipment_id']; if ($l['freight'] === null || isset($seen[$k])) continue; $seen[$k] = 1; if ($sh[$k]['kg'] > 0) { $fk += (float)$l['freight']; $kk += $sh[$k]['kg']; } } $perKg = $kk > 0 ? $fk / $kk : null; } $est = []; // Amazon "Recommended restock" per country (Restock Inventory report, loaded with the hourly stock sync), summed per product. $RST_M = ['US' => 'USA', 'UK' => 'UK', 'DE' => 'Germany', 'AU' => 'Australia']; $rst = []; if ($admin) try { $smap = sku_map(); foreach (db()->query('SELECT market, sku, qty FROM amz_restock WHERE qty > 0') as $x) { $pid = $smap[sku_key((string)$x['sku'])] ?? null; if (!$pid || !isset($RST_M[$x['market']])) continue; $rst[$pid][$x['market']] = ($rst[$pid][$x['market']] ?? 0) + (int)$x['qty']; } } catch (Throwable $e) {} $rstT = array_fill_keys(array_keys($RST_M), 0); // status of each row and the figures for the summary at the top $tq = 0; $tv = 0; $tvl = 0; $tov = 0; $tc = 0; $to = 0; $tin = 0; $tav = 0; $nOrder = 0; $nOn = 0; $ready = 0; $cartons = fn($v) => rtrim(rtrim(number_format($v, 1, '.', ','), '0'), '.'); foreach ($rows as $k => $r) { $pid = (int)$r['id']; $lf = $landed[$pid] ?? null; if ($lf === null && $admin && $perKg !== null && (float)$r['carton_kg'] > 0 && (int)$r['pcs_per_carton'] > 0) { $lf = $perKg * (float)$r['carton_kg'] / (int)$r['pcs_per_carton']; $est[$pid] = 1; } $ins = (int)($insh[$pid]['q'] ?? 0); $av = (int)$r['stock'] - $ins; $cls = 'none'; if ($r['active'] && empty($r['no_reorder']) && $r['min_stock'] !== null) { if ($av <= (int)$r['min_stock']) $cls = ($av + $r['on_order'] <= (int)$r['min_stock']) ? 'order' : 'onord'; else $cls = 'ok'; } if ($cls === 'order') $nOrder++; if ($cls === 'onord') $nOn++; if ($av > 0) $ready += $av; $v = $r['stock'] * $r['price']; $vl = $r['stock'] * ((float)$r['price'] + (float)$lf); $tq += $r['stock']; $tv += $v; $tvl += $vl; $to += $r['on_order']; $tov += $r['on_order_value']; $tin += $ins; $tav += $av; $cart = $r['pcs_per_carton'] ? $r['stock'] / $r['pcs_per_carton'] : null; if ($cart !== null) $tc += $cart; $rows[$k] += ['_lf' => $lf, '_ins' => $ins, '_av' => $av, '_cls' => $cls, '_v' => $v, '_vl' => $vl, '_cart' => $cart]; } page_start('Stock at the supplier’s warehouse', 'index'); $cols = 6 + ($admin ? count($RST_M) + 1 : 0); ?>
Check a Send to Amazon plan (PDF) Download Excel
products to order now
low, but already on order
pieces ready to ship
stock value incl. freighton order ' . money($tov) : '' ?>
$r): $pid = (int)$r['id']; $m = trim((string)$r['model']); if ($m !== $lastM): $lastM = $m; ?> $mn): $q = $rst[$pid][$mk] ?? 0; $rstT[$mk] += $q; ?>
ProductAvailableIn stock In shipmentsOn orderTotal Recommended restockValue incl. freight
Total

No products yet. Add the first one.

The stripe on the left: red — order now, blue — low, already on order, green — enough. Click a product for cartons and cost. More in Help.