Subversion Repositories SmartDukaan

-- Fix warehouse.invoice.receiveDate for entries where it is NULL
-- The receiveDate should reflect the actual GRN time
-- Uses warehouse.purchase.receivedOn which was set to LocalDateTime.now() during GRN processing
-- Falls back to MIN(inventoryItem.created) if purchase.receivedOn is also NULL

UPDATE warehouse.invoice wi
JOIN (
    SELECT p.invoice_id, MIN(p.receivedOn) as grnTime
    FROM warehouse.purchase p
    WHERE p.receivedOn IS NOT NULL
    GROUP BY p.invoice_id
) p ON wi.id = p.invoice_id
SET wi.receiveDate = p.grnTime
WHERE wi.receiveDate IS NULL
  AND wi.status = 'received';