Subversion Repositories SmartDukaan

-- ============================================================================

-- Void net-settled stranded orders (status=0 left in "Unbilled PO")

-- ============================================================================

-- selectPendingIndentWalletAmount counts any billing/refund-NULL order. Some

-- status=0 orders had their wallet PURCHASE debit REVERSED via a non-standard

-- wallet entry (e.g. OTHERS "Oder Cancelled", "order not placed") -- so the

-- transaction nets to ~0 (fully refunded) but the order row stayed status=0 and

-- kept inflating unbilled PO. Two sub-classes:

--   (a) rejected-txn (transaction_approval REJECTED) the PO_REJECTED backfill

--       missed because the refund wasn't the standard "Order canceled" PURCHASE

--       entry  -> mark PO_REJECTED(91).

--   (b) non-rejected, refunded-by-other-means -> keep status=0, just drop from PI.

--

-- SAFETY: only net-settled (transaction net wallet >= 0) orders are voided. A

-- genuinely committed / awaiting-approval PO is net-DEBITED (net < 0) and is left

-- untouched -- this is what protects awaiting-approval bulk POs and real payables.

--

-- Mechanism: refund_timestamp = created_timestamp (drops from pending indent).

-- Use the TEMP-TABLE form (scope wallet net to candidate txns) -- the broad

-- PURCHASE-debit aggregate is a full scan that locks the LIVE DB (hadb1). Run with

-- `transaction` as default schema.

--

-- Ran on hadb1 (live) 2026-06-08: Group-B rejected 55 -> status 91; net-settled

-- non-rejected 186 -> refund_timestamp. IDEMPOTENT (guard refund_timestamp NULL).

-- ============================================================================

-- run with:  mysql ... transaction < void_net_settled_stranded_orders.sql

CREATE TEMPORARY TABLE cand AS

  SELECT o.id AS order_id, CAST(o.transaction_id AS CHAR) AS ref,

         CASE WHEN ta.id IS NOT NULL THEN 1 ELSE 0 END AS is_rejected

  FROM transaction.`order` o

  LEFT JOIN transaction.transaction_approval ta ON ta.id=o.transaction_id AND ta.status='REJECTED'

  WHERE o.status = 0

    AND o.billing_timestamp IS NULL

    AND o.refund_timestamp IS NULL

    AND COALESCE(o.original_wallet_amount, o.wallet_amount) > 0;

ALTER TABLE cand ADD INDEX(ref);

CREATE TEMPORARY TABLE refnet AS

  SELECT h.reference AS ref, SUM(h.amount) AS net

  FROM transaction.userwallethistory h

  WHERE h.reference IN (SELECT ref FROM cand)

  GROUP BY h.reference;

ALTER TABLE refnet ADD INDEX(ref);

-- net-settled only (net >= -1). Net-committed (net < -1) excluded = real payable / awaiting-approval.

CREATE TEMPORARY TABLE void_ids AS

  SELECT c.order_id, c.is_rejected

  FROM cand c

  LEFT JOIN refnet r ON r.ref = c.ref

  WHERE COALESCE(r.net, 0) >= -1;

UPDATE transaction.`order` o

  JOIN void_ids v ON v.order_id = o.id

SET o.status           = CASE WHEN v.is_rejected = 1 THEN 91 ELSE o.status END,  -- 91=PO_REJECTED for rejected txns

    o.refund_timestamp = o.created_timestamp,

    o.refunded_by      = 'system-cleanup',

    o.refund_reason    = CASE WHEN v.is_rejected = 1

                              THEN 'PO rejected (refunded via non-standard wallet entry; order row stranded) - excluded from unbilled PO'

                              ELSE 'Stranded order, wallet debit already reversed (net-settled); excluded from unbilled PO' END;

-- Verify: no net-settled stranded status=0 orders should remain (only net-committed kept).