Subversion Repositories SmartDukaan

-- Record the vendor on stock received against orders raised before lines carried an origin (2026-09-18)

--

-- WHY

--   r37694 stamps a receipt with the origin held on its purchase order line, and lines only carry one from that

--   revision onward. Goods arriving on older open orders therefore landed with a cost but no vendor - 282 rows on the

--   first day alone - even where the order itself names an outside vendor we bought from.

--   The code now falls back to the order's own supplier; this repairs what was received before that fix.

--

-- Facts only: an outside vendor named on the unit's own purchase order. Internal movements are untouched, and stock

-- whose origin was never knowable keeps NULL.

USE warehouse;

CREATE TABLE warehouse._bak_receipt_origin_20260918 AS

SELECT ii.id, ii.origin_vendor_id, ii.receipt_unit_cost

  FROM warehouse.inventoryItem ii

  JOIN warehouse.purchase p       ON p.id = ii.purchaseId

  JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id

  JOIN warehouse.supplier s       ON s.id = po.supplierId AND s.internal = 0

 WHERE ii.origin_vendor_id IS NULL;

SELECT COUNT(*) AS rows_to_repair FROM warehouse._bak_receipt_origin_20260918;

START TRANSACTION;

UPDATE warehouse.inventoryItem ii

  JOIN warehouse._bak_receipt_origin_20260918 b ON b.id = ii.id

  JOIN warehouse.purchase p       ON p.id = ii.purchaseId

  JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id

  LEFT JOIN warehouse.lineitem li ON li.purchaseOrder_id = po.id AND li.itemId = ii.itemId

   SET ii.origin_vendor_id  = po.supplierId,

       ii.receipt_unit_cost = COALESCE(ii.receipt_unit_cost, li.unitPrice);

COMMIT;

-- Verify: repaired = 0 left, and no internal supplier ever recorded as an origin

SELECT (SELECT COUNT(*) FROM warehouse.inventoryItem ii

          JOIN warehouse.purchase p ON p.id = ii.purchaseId

          JOIN warehouse.purchaseorder po ON po.id = p.purchaseOrder_id

          JOIN warehouse.supplier s ON s.id = po.supplierId AND s.internal = 0

         WHERE ii.origin_vendor_id IS NULL) AS external_receipts_still_blank,

       (SELECT COUNT(*) FROM warehouse.inventoryItem ii

          JOIN warehouse.supplier s ON s.id = ii.origin_vendor_id AND s.internal = 1) AS internal_wrongly_recorded,

       (SELECT COUNT(*) FROM warehouse.inventoryItem WHERE currentQuantity > 0 AND origin_vendor_id IS NULL) AS in_stock_unknown;

-- Rollback:

-- UPDATE warehouse.inventoryItem ii JOIN warehouse._bak_receipt_origin_20260918 b ON b.id = ii.id

--    SET ii.origin_vendor_id = b.origin_vendor_id, ii.receipt_unit_cost = b.receipt_unit_cost;