fix pms.md

Notebook PMS bug investigation

On doit trouver pourquoi certains produits attributes ont un stock négatifs.

On voit que reserved_quantity reste positif alors que le stock a été shipped côté PMS

query to find wrong stock

SELECT p.id_product, COUNT(*) as declinaisons, SUM(f.quantity) as q  
FROM `psds17_stock_available` p 
INNER JOIN psds17_stock_available f ON f.id_product = p.id_product AND f.id_product_attribute > 0 AND f.quantity > 0 
WHERE p.quantity = 0 AND p.id_product_attribute = 0  
GROUP BY p.id_product  
ORDER BY id_product DESC LIMIT 1000;

Compare calculated reserved_quantity from Prestashop orders with Prestashop stock available (à lancer après avoir fix les erreurs):

SELECT * FROM (
  SELECT 
     sa.id_product, sa.quantity, sa.reserved_quantity, sa.id_product_attribute, 
    (SELECT Sum(od.product_quantity - od.product_quantity_refunded) 
        FROM psds17_order_detail od 
        INNER JOIN psds17_orders o ON o.id_order = od.id_order 
        INNER JOIN psds17_order_state os ON os.id_order_state = o.current_state 
        WHERE 
           od.product_attribute_id = sa.id_product_attribute 
           AND os.shipped != 1 
           AND ( o.valid = 1 OR ( os.id_order_state != 8 AND os.id_order_state != 6 ) )
   ) AS calculated_reserved_quantity 
   FROM psds17_stock_available sa 
   WHERE sa.reserved_quantity > 0 
   GROUP BY sa.id_product, sa.id_product_attribute, sa.quantity, sa.reserved_quantity 
  ) s 
  WHERE s.calculated_reserved_quantity IS NULL;

Overview des states des orders

SELECT count(*), o.valid, o.current_state, os.shipped, osl.name, (os.shipped != 1 AND (
                    o.valid = 1 OR (
                        os.id_order_state != 8 AND
                        os.id_order_state != 6
                    )
                )) AS reserved
FROM `psds17_orders` o 
INNER JOIN psds17_order_state os ON os.id_order_state = o.current_state
INNER JOIN psds17_order_state_lang osl ON osl.id_order_state = os.id_order_state
GROUP BY o.valid, o.current_state, os.shipped, osl.name;

Réconcilier les mauvais order.valid qui sont annulés

UPDATE `psds17_orders` o SET o.valid = 0 WHERE o.valid = 1 AND o.current_state = 6;

Liste les commandes pour 1 produit, pour trouver la commande bugguée

SELECT o.id_order, o.date_upd, od.product_id, o.valid, os.shipped, o.current_state, osl.name, od.product_attribute_id, (os.shipped != 1 AND ( o.valid = 1 OR ( os.id_order_state != 8 AND os.id_order_state != 6 ) )) AS reserved, od.product_quantity - od.product_quantity_refunded AS calculated_reserved_quantity 
FROM psds17_orders o 
INNER JOIN psds17_order_detail od ON od.id_order = o.id_order 
INNER JOIN psds17_order_state os ON os.id_order_state = o.current_state 
INNER JOIN psds17_order_state_lang osl ON osl.id_order_state = os.id_order_state 
WHERE od.product_id = 61493 
ORDER BY o.date_upd DESC;

Même requête, pour une liste de réf

SELECT p.reference, od.product_id, o.id_order, o.date_upd, o.valid, os.shipped, o.current_state, osl.name, od.product_attribute_id, (os.shipped != 1 AND ( o.valid = 1 OR ( os.id_order_state != 8 AND os.id_order_state != 6 ) )) AS reserved, od.product_quantity, od.product_quantity_refunded 
FROM psds17_orders o 
INNER JOIN psds17_order_detail od ON od.id_order = o.id_order 
INNER JOIN psds17_order_state os ON os.id_order_state = o.current_state 
INNER JOIN psds17_order_state_lang osl ON osl.id_order_state = os.id_order_state 
INNER JOIN psds17_product p ON p.id_product = od.product_id
WHERE p.reference IN ('P22H3329', 'P21H0456', 'P21H0353', 'P20H0313A', 'P18H0917', 'P21F0548', 'H24H0747', 'H23H0130', 'H20F0524', 'P22H0298', 'P22F3087', 'P22F1519', 'H21F0651C')
ORDER BY o.date_upd DESC;

Fix order state 45 (4 commandes)

UPDATE psds17_orders o SET o.valid = 1 WHERE o.current_state = 45;
UPDATE psds17_orders o SET o.current_state = 24 WHERE o.current_state = 45;

Fix stock quantity

UPDATE psds17_stock_available sa 
SET sa.reserved_quantity = 0, sa.quantity = sa.physical_quantity 
WHERE sa.id_product_attribute IN (
  SELECT id_product_attribute FROM (SELECT sa.id_product_attribute, (SELECT Sum(od.product_quantity - od.product_quantity_refunded) FROM psds17_order_detail od INNER JOIN psds17_orders o ON o.id_order = od.id_order INNER JOIN psds17_order_state os ON os.id_order_state = o.current_state WHERE od.product_attribute_id = sa.id_product_attribute AND os.shipped != 1 AND ( o.valid = 1 OR ( os.id_order_state != 8 AND os.id_order_state != 6 ) )) AS calculated_reserved_quantity FROM psds17_stock_available sa WHERE sa.reserved_quantity > 0 GROUP BY sa.id_product_attribute ) s WHERE s.calculated_reserved_quantity IS NULL);