Este documento resume como calcular el stock que muestra el dashboard de Pampling y que fuentes de datos deben usarse para evitar discrepancias.
La base usada por defecto es stock-pamplig.
Para pantallas interactivas, el catalogo de articulos por tienda sale de:
config_item_shop: combinaciones tienda/articulo/color/talla que deben aparecer.cmz_item: metadatos del articulo y variante, como EAN, descripcion, familia y seccion.stock: movimientos reales usados para calcular unidades disponibles.El punto importante es que config_item_shop define el universo visible de articulos y variantes por tienda, pero el stock numerico se calcula con stock.
El stock de una variante se calcula sumando solo registros de tipo BASE y MOVEMENT:
SUM(CASE WHEN type IN ('BASE', 'MOVEMENT') THEN stock ELSE 0 END)
En el backend se separa tambien en:
SUM(CASE WHEN type = 'BASE' THEN stock ELSE 0 END) AS stock_base,
SUM(CASE WHEN type = 'MOVEMENT' THEN stock ELSE 0 END) AS stock_movimiento,
SUM(CASE WHEN type IN ('BASE', 'MOVEMENT') THEN stock ELSE 0 END) AS stock_total
Por tanto:
stock_total = stock_base + stock_movimiento
La clave de variante es:
id_shop + id_item + var_1 + var_2
Donde:
id_shop: tienda.id_item: articulo.var_1: color.var_2: talla.SELECT
id_shop,
id_item,
var_1,
var_2,
SUM(CASE WHEN type = 'BASE' THEN stock ELSE 0 END) AS stock_base,
SUM(CASE WHEN type = 'MOVEMENT' THEN stock ELSE 0 END) AS stock_movimiento,
SUM(CASE WHEN type IN ('BASE', 'MOVEMENT') THEN stock ELSE 0 END) AS stock_total
FROM stock
WHERE id_shop = '0005'
GROUP BY id_shop, id_item, var_1, var_2;
Para obtener el total de un articulo se suma el stock_total de todas sus variantes:
WITH stock_by_variant AS (
SELECT
id_shop,
id_item,
var_1,
var_2,
SUM(CASE WHEN type = 'BASE' THEN stock ELSE 0 END) AS stock_base,
SUM(CASE WHEN type = 'MOVEMENT' THEN stock ELSE 0 END) AS stock_movimiento,
SUM(CASE WHEN type IN ('BASE', 'MOVEMENT') THEN stock ELSE 0 END) AS stock_total
FROM stock
WHERE id_shop = '0005'
GROUP BY id_shop, id_item, var_1, var_2
)
SELECT
cfg.id_shop,
cfg.id_item,
SUM(COALESCE(st.stock_base, 0)) AS stock_base,
SUM(COALESCE(st.stock_movimiento, 0)) AS stock_movimiento,
SUM(COALESCE(st.stock_total, 0)) AS stock_total,
COALESCE(MAX(NULLIF(c.item_description, '')), MAX(NULLIF(cfg.item_description, '')), cfg.id_item) AS item_description,
MAX(c.family) AS family,
MAX(c.section) AS section
FROM config_item_shop cfg
LEFT JOIN stock_by_variant st
ON cfg.id_shop = st.id_shop
AND cfg.id_item COLLATE utf8mb4_general_ci = st.id_item COLLATE utf8mb4_general_ci
AND cfg.var_1 COLLATE utf8mb4_general_ci = st.var_1 COLLATE utf8mb4_general_ci
AND cfg.var_2 COLLATE utf8mb4_general_ci = st.var_2 COLLATE utf8mb4_general_ci
LEFT JOIN cmz_item c
ON cfg.id_item COLLATE utf8mb4_general_ci = c.id_item COLLATE utf8mb4_general_ci
AND cfg.var_1 COLLATE utf8mb4_general_ci = c.var_1 COLLATE utf8mb4_general_ci
AND cfg.var_2 COLLATE utf8mb4_general_ci = c.var_2 COLLATE utf8mb4_general_ci
WHERE cfg.id_shop = '0005'
GROUP BY cfg.id_shop, cfg.id_item
ORDER BY
CASE WHEN SUM(COALESCE(st.stock_total, 0)) > 0 THEN 0 ELSE 1 END,
cfg.id_item;
Para resumenes agregados, familia y seccion se extraen del codigo de articulo cuando aplica el patron ^[A-Za-z][0-9]{9}$:
family_code = SUBSTRING(id_item, 2, 2)
section_code = SUBSTRING(id_item, 4, 2)
Ejemplo:
WITH stock_by_variant AS (
SELECT
id_shop,
id_item,
var_1,
var_2,
SUM(CASE WHEN type IN ('BASE', 'MOVEMENT') THEN stock ELSE 0 END) AS stock_total
FROM stock
WHERE id_shop = '0005'
GROUP BY id_shop, id_item, var_1, var_2
)
SELECT
SUBSTRING(cfg.id_item, 2, 2) AS family_code,
SUBSTRING(cfg.id_item, 4, 2) AS section_code,
SUM(COALESCE(st.stock_total, 0)) AS stock_total
FROM config_item_shop cfg
LEFT JOIN stock_by_variant st
ON cfg.id_shop = st.id_shop
AND cfg.id_item COLLATE utf8mb4_general_ci = st.id_item COLLATE utf8mb4_general_ci
AND cfg.var_1 COLLATE utf8mb4_general_ci = st.var_1 COLLATE utf8mb4_general_ci
AND cfg.var_2 COLLATE utf8mb4_general_ci = st.var_2 COLLATE utf8mb4_general_ci
WHERE cfg.id_shop = '0005'
AND cfg.id_item REGEXP '^[A-Za-z][0-9]{9}$'
GROUP BY family_code, section_code
HAVING stock_total <> 0
ORDER BY stock_total DESC, family_code, section_code;
Hay dos formas de historico:
data/stock-history.json, generados con npm.cmd run snapshot:stock.data/stock-history-summary.json, generado con npm.cmd run history:backfill -- --days=365.Para reconstruir historico real desde stock, se calcula:
La fecha operativa usa:
COALESCE(movement_datetime, datetime)
Ejemplo de base historica:
SELECT
var_1,
var_2,
SUM(stock) AS stock_total
FROM stock
WHERE id_shop = '0005'
AND id_item = 'P015803777'
AND type IN ('BASE', 'MOVEMENT')
AND COALESCE(movement_datetime, datetime) < '2026-01-01 00:00:00'
GROUP BY var_1, var_2
HAVING stock_total <> 0;
Ejemplo de delta historico:
SELECT
DATE(COALESCE(movement_datetime, datetime)) AS day,
var_1,
var_2,
SUM(stock) AS stock_delta
FROM stock
WHERE id_shop = '0005'
AND id_item = 'P015803777'
AND type IN ('BASE', 'MOVEMENT')
AND COALESCE(movement_datetime, datetime) >= '2026-01-01 00:00:00'
AND COALESCE(movement_datetime, datetime) < '2026-02-01 00:00:00'
GROUP BY day, var_1, var_2
ORDER BY day, var_2, var_1;
stock_displayconfig_item_shop.stock_display aparece en la configuracion y se devuelve como dato auxiliar, pero no debe tratarse como la formula canonica del stock calculado en el dashboard actual.
La formula canonica implementada para unidades es:
SUM(stock.type BASE + MOVEMENT)
stock_display puede servir para contrastes o diagnostico, pero si no coincide con la suma real de stock, hay que investigar la causa antes de usarlo como verdad.
config_item_shop para saber que articulos y variantes existen en una tienda.stock para calcular unidades disponibles.id_shop, id_item, var_1 y var_2.COLLATE utf8mb4_general_ci en joins de texto si hay riesgo de diferencias de collation.stock para pantallas interactivas: filtrar siempre por tienda y paginar.stock-history-summary.json cuando ya exista.npm.cmd start
npm.cmd run check
npm.cmd run snapshot:stock -- --shop=0005
npm.cmd run cron:stock -- --shop=0005 --dry-run
npm.cmd run history:backfill -- --days=365
Para una tienda concreta, por ejemplo 0005:
stock_total por id_shop + id_item + var_1 + var_2.Si los puntos 2, 3 y 4 coinciden, el calculo de stock vivo esta alineado.