Subversion Repositories SmartDukaan

Rev

Rev 37563 | Rev 37778 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 37563 Rev 37566
Line 1... Line 1...
1 
-- ============================================================================
 1 
-- ============================================================================
2 
--  Delist logic v2 - rolling 24-month, house-wide                 2026-09-09
 2 
--  Delist logic v3 - rolling 24-month, house-wide                  2026-09-10
3 
--
 3 
--
4 
--  Supersedes samsung_delist_pre2025_no_stock_20260909.sql, which was one
 4 
--  Supersedes samsung_delist_pre2025_no_stock_20260909.sql, which was one
5 
--  brand with a frozen id list. This runs across EVERY brand and EVERY
 5 
--  brand with a frozen id list. This runs across EVERY brand and EVERY
6 
--  category (mobile and non-mobile) on a rolling 24-month window, so it is
 6 
--  category (mobile and non-mobile) on a rolling 24-month window, so it is
7 
--  safe to schedule daily.
 7 
--  safe to schedule daily.
8 
--
 8 
--
9 
--  Three changes over v1:
 9 
--  Changes over v1:
10 
--    (NEW 0) rolling 24-month window instead of a hardcoded 2025-01-01, and
 10 
--    (NEW 0) rolling 24-month window instead of a hardcoded 2025-01-01, and
11 
--            no brand / category restriction.
 11 
--            no brand / category restriction.
12 
--    (NEW 1) an item with an OUTSTANDING VENDOR PO is never delisted. It is
 12 
--    (NEW 1) an item with an OUTSTANDING VENDOR PO is never delisted. It is
13 
--            skipped, not excluded -- the next run re-evaluates it, so it
 13 
--            skipped, not excluded -- the next run re-evaluates it, so it
14 
--            delists by itself once the PO closes.
 14 
--            delists by itself once the PO closes.
15 
--    (NEW 2) a model that goes FULLY DARK (no active listing left on any of
 15 
--    (NEW 2) a model that goes FULLY DARK (no active listing left on any of
16 
--            its colours) has its movement categorisation moved to OTHER, so
 16 
--            its colours) has its movement categorisation moved to OTHER, so
17 
--            it stops being offered as a live model.
 17 
--            it stops being offered as a live model.
- 
 
 18 
--  Added in v3 (2026-09-10):
- 
 
 19 
--    (NEW 3) NOT MOVED FOR A MONTH on either side. Zero stock alone was too
- 
 
 20 
--            weak: it fired on SKUs partners were still transacting, where the
- 
 
 21 
--            recent movement was often the very sale that emptied the stock.
- 
 
 22 
--    (NEW 4) the internal pseudo-brands (Dummy, FOC, FOC HANDSET, Live Demo)
- 
 
 23 
--            are no longer excluded; they are swept like anything else.
- 
 
 24 
--    (NEW 5) vendor-PO guard narrowed to status = 1; status 2 is unreachable.
- 
 
 25 
--
- 
 
 26 
--  ⚠ The 2026-09-10 run of v2 predates NEW 3, so it delisted 24 listings /
- 
 
 27 
--    21 models that had partner movement inside the month. Reverted by
- 
 
 28 
--    rollback_delist_recent_movement_20260910.sql.
18 
--
 29 
--
19 
--  Written to be run repeatedly (daily). Every step is idempotent.
 30 
--  Written to be run repeatedly (daily). Every step is idempotent.
20 
-- ============================================================================
 31 
-- ============================================================================
21 
 
 32 
 
22 
-- ---------------------------------------------------------------------------
 33 
-- ---------------------------------------------------------------------------
23 
--  WHAT "OUTSTANDING VENDOR PO" MEANS  (NEW 1)
 34 
--  WHAT "OUTSTANDING VENDOR PO" MEANS  (NEW 1)
24 
--
 35 
--
25 
--    warehouse.purchaseorder.status is an ORDINAL enum (in.shop2020.purchase.POStatus):
 36 
--    warehouse.purchaseorder.status is an ORDINAL enum (in.shop2020.purchase.POStatus):
26 
--        0 INIT   1 READY   2 PARTIALLY_FULFILLED   3 PRECLOSED   4 CLOSED
 37 
--        0 INIT   1 READY   2 PARTIALLY_FULFILLED   3 PRECLOSED   4 CLOSED
27 
--
 38 
--
28 
--    Outstanding  = status IN (1,2) AND supplier.internal = 0
 39 
--    Outstanding  = status = 1 AND supplier.internal = 0
29 
--                   AND lineitem.unfulfilledQuantity > 0
 40 
--                   AND lineitem.unfulfilledQuantity > 0
30 
--
 41 
--
31 
--    status 1 is the house definition of an open PO -- the existing named query
 42 
--    status 1 (READY) is the house definition of an open PO -- the existing
- 
 
 43 
--    named query warehouse.selectOpenPo uses exactly
32 
--    warehouse.selectOpenPo uses exactly "po.status = 1 and s.internal = false".
 44 
--    "po.status = 1 and s.internal = false".
- 
 
 45 
--
- 
 
 46 
--    PARTIALLY_FULFILLED (2) is NOT included, and this is not an oversight.
- 
 
 47 
--    It is UNREACHABLE, not merely rare -- verified 2026-09-10:
- 
 
 48 
--      * all 8 PO-status writes in the codebase set INIT, READY, PRECLOSED or
- 
 
 49 
--        CLOSED (PurchaseOrderServiceImpl 399/479/913, GrnController:495,
- 
 
 50 
--        V2FofoGrnController:441, PurchaseOrderController:188,
- 
 
 51 
--        V2FofoPurchaseOrderController:139, POScheduler:46). None sets it.
- 
 
 52 
--      * no native SQL writes purchaseorder.status.
- 
 
 53 
--      * SELECT COUNT(*) ... WHERE status = 2 -> 0, across 51,593+ POs to 2011.
- 
 
 54 
--    Its only two code references (InvoiceServiceImpl:248, POScheduler:32) are
- 
 
 55 
--    defensive READS. Partial receipt is modelled per LINE
- 
 
 56 
--    (unfulfilledQuantity / fulfilled -- 9,153 lines are genuinely part
- 
 
 57 
--    received), never rolled up to a header status, so a part-received PO sits
- 
 
 58 
--    in READY and is already caught by "status = 1 AND unfulfilled > 0".
- 
 
 59 
--    Do not "restore" status 2 here thinking it closes a gap.
- 
 
 60 
--
33 
--    status 2 is included because PARTIALLY_FULFILLED is a legitimate open
 61 
--    NOTE the guard is effectively a 6-DAY GRACE, not an indefinite hold:
- 
 
 62 
--    POScheduler.autoClosePurchaseOrders (ScheduledSkeleton:747, daily 01:00)
- 
 
 63 
--    force-CLOSES vendor POs 6 days after creation (internal: 4) regardless of
- 
 
 64 
--    whether goods arrived. That is why every READY PO on prod is <= 6 days
34 
--    state (it happens to hold no unfulfilled lines today).
 65 
--    old, and it guarantees a deferral always resolves.
35 
--
 66 
--
36 
--    INIT (0) is DELIBERATELY EXCLUDED. Measured on prod 2026-09-09:
 67 
--    INIT (0) is DELIBERATELY EXCLUDED. Measured on prod 2026-09-09:
37 
--        status 0 -> 376 unfulfilled lines spanning 2024-04-02 .. 2026-09-03
 68 
--        status 0 -> 376 unfulfilled lines spanning 2024-04-02 .. 2026-09-03
38 
--        status 1 ->  97 unfulfilled lines spanning 2026-09-01 .. 2026-09-09
 69 
--        status 1 ->  97 unfulfilled lines spanning 2026-09-01 .. 2026-09-09
39 
--    INIT is a drawer of abandoned drafts, 2.5 years deep. Nothing ever closes
 70 
--    INIT is a drawer of abandoned drafts, 2.5 years deep. Nothing ever closes
Line 105... Line 136...
105 
FROM catalog.tag_listing tl
 136 
FROM catalog.tag_listing tl
106 
JOIN catalog.item i ON i.id = tl.item_id
 137 
JOIN catalog.item i ON i.id = tl.item_id
107 
WHERE tl.tag_id = 4                       -- default_fofo; tag 7 'test' has no rows
 138 
WHERE tl.tag_id = 4                       -- default_fofo; tag 7 'test' has no rows
108 
  AND tl.active = 1
 139 
  AND tl.active = 1
109 
  AND tl.start_date < CURDATE() - INTERVAL 24 MONTH
 140 
  AND tl.start_date < CURDATE() - INTERVAL 24 MONTH
110 
  -- Internal pseudo-brands. These are NOT sellable catalogue: the app already
 - 
 
111 
  -- hard-excludes them from every partner listing
 - 
 
112 
  -- (StoreController/FofoSolr excludeBrands = Dummy, FOC HANDSET, FOC, Live Demo).
 141 
  -- No brand exclusion. The internal pseudo-brands (Dummy, FOC, FOC HANDSET,
113 
  -- Delisting them would change nothing a partner sees while disturbing demo /
 142 
  -- Live Demo) are swept like anything else, by instruction 2026-09-10.
114 
  -- FOC operations. Remove this line if you want them swept too.
 - 
 
115 
  AND i.brand NOT IN ('Dummy', 'FOC', 'FOC HANDSET', 'Live Demo')
 - 
 
116 
  -- no SmartDukaan stock
 143 
  -- no SmartDukaan stock
117 
  AND NOT EXISTS (
 144 
  AND NOT EXISTS (
118 
        SELECT 1 FROM warehouse.view_availability va
 145 
        SELECT 1 FROM warehouse.view_availability va
119 
        WHERE va.item_id = tl.item_id AND va.total > 0)
 146 
        WHERE va.item_id = tl.item_id AND va.total > 0)
120 
  -- no stock at any live partner
 147 
  -- no stock at any live partner
Line 125... Line 152...
125 
        WHERE ii.item_id = tl.item_id AND ii.good_quantity > 0
 152 
        WHERE ii.item_id = tl.item_id AND ii.good_quantity > 0
126 
          AND fs.active = 1 AND fs.closed = 0)
 153 
          AND fs.active = 1 AND fs.closed = 0)
127 
  -- nothing already on order from the partner side
 154 
  -- nothing already on order from the partner side
128 
  AND NOT EXISTS (
 155 
  AND NOT EXISTS (
129 
        SELECT 1 FROM warehouse.view_cis vc
 156 
        SELECT 1 FROM warehouse.view_cis vc
130 
        WHERE vc.item_id = tl.item_id AND vc.indent > 0);
 157 
        WHERE vc.item_id = tl.item_id AND vc.indent > 0)
- 
 
 158 
  -- ---------------------------------------------------------------------
- 
 
 159 
  -- NOT MOVED FOR A MONTH, EITHER SIDE.
- 
 
 160 
  --
- 
 
 161 
  -- The stock guards above are point-in-time: they say "zero right now", not
- 
 
 162 
  -- "zero for a while". Nothing records when stock hit zero
- 
 
 163 
  -- (view_availability.updated_at is the materialized-view refresh clock, it
- 
 
 164 
  -- ticks continuously and cannot be used for this), so recent MOVEMENT is
- 
 
 165 
  -- used as the proxy for "still trading".
- 
 
 166 
  --
- 
 
 167 
  -- Warehouse side: warehouse.scanNew (3.4M rows, live) via inventoryItem.
- 
 
 168 
  --   warehouse.scan is a dead Saholic relic - 2,476 rows, last write 2012.
- 
 
 169 
  -- Partner side: fofo.scan_record via fofo.inventory_item. Any type counts
- 
 
 170 
  --   (PURCHASE / SALE / returns) - the question is whether the SKU is moving
- 
 
 171 
  --   at all, not which direction.
- 
 
 172 
  --
- 
 
 173 
  -- Both are NOT EXISTS rather than MAX(...) comparisons so they short-circuit
- 
 
 174 
  -- on the first recent row and use the (inventoryItemId, scannedAt) and
- 
 
 175 
  -- (inventory_item_id, create_timestamp) indexes. An item that has NEVER
- 
 
 176 
  -- moved qualifies for delisting, which is correct.
- 
 
 177 
  AND NOT EXISTS (
- 
 
 178 
        SELECT 1
- 
 
 179 
        FROM warehouse.inventoryItem wii
- 
 
 180 
        JOIN warehouse.scanNew sn ON sn.inventoryItemId = wii.id
- 
 
 181 
        WHERE wii.itemId = tl.item_id
- 
 
 182 
          AND sn.scannedAt >= CURDATE() - INTERVAL 1 MONTH)
- 
 
 183 
  AND NOT EXISTS (
- 
 
 184 
        SELECT 1
- 
 
 185 
        FROM fofo.inventory_item fii
- 
 
 186 
        JOIN fofo.scan_record sr ON sr.inventory_item_id = fii.id
- 
 
 187 
        WHERE fii.item_id = tl.item_id
- 
 
 188 
          AND sr.create_timestamp >= CURDATE() - INTERVAL 1 MONTH);
131 
 
 189 
 
132 
-- ===========================================================================
 190 
-- ===========================================================================
133 
--  STEP 2 (NEW 1) - drop anything with an outstanding vendor PO.
 191 
--  STEP 2 (NEW 1) - drop anything with an outstanding vendor PO.
134 
--  These are SKIPPED, not excluded: tomorrow's run re-evaluates them.
 192 
--  These are SKIPPED, not excluded: tomorrow's run re-evaluates them.
135 
-- ===========================================================================
 193 
-- ===========================================================================
Line 143... Line 201...
143 
FROM _delist_candidate c
 201 
FROM _delist_candidate c
144 
JOIN warehouse.lineitem      li ON li.itemId = c.item_id
 202 
JOIN warehouse.lineitem      li ON li.itemId = c.item_id
145 
JOIN warehouse.purchaseorder po ON po.id     = li.purchaseOrder_id
 203 
JOIN warehouse.purchaseorder po ON po.id     = li.purchaseOrder_id
146 
JOIN warehouse.supplier      s  ON s.id      = po.supplierId
 204 
JOIN warehouse.supplier      s  ON s.id      = po.supplierId
147 
WHERE s.internal = 0
 205 
WHERE s.internal = 0
148 
  AND po.status IN (1, 2)
 206 
  AND po.status = 1                       -- READY only; status 2 is unreachable (see header)
149 
  AND li.unfulfilledQuantity > 0;
 207 
  AND li.unfulfilledQuantity > 0;
150 
 
 208 
 
151 
-- Report before writing anything.
 209 
-- Report before writing anything.
152 
-- NOTE: MySQL cannot reference the same TEMPORARY table twice in one
 210 
-- NOTE: MySQL cannot reference the same TEMPORARY table twice in one
153 
-- statement ("Can't reopen table"), so these counts are deliberately kept as
 211 
-- statement ("Can't reopen table"), so these counts are deliberately kept as