Reorder alerts from Shopify exports

For: Online stores (Shopify and others)

The job: Run out of a best seller and the sales stop until the supplier delivers.

You give it: Shopify inventory export, orders export, and suppliers.csv (lead time + minimum order).

It is one agent of the free AI OS starter (https://subzero.bodymindbeing.me/free/ai-os-starter/) - download the whole starter, it runs on sample data out of the box. Nothing is sent anywhere: replies come out as drafts.

Download the AI OS starter (zip) 65 KB

MIT licence. Python 3.10+, no other installs. Unzip, then follow README.md.

Run this one

1. Set up the starter (the steps on the starter page).
2. python -m aios run reorder_alerts
3. Edit config.json: point "reorder_alerts" at your own files (paths relative to config.json).

Check it yourself

1. Pick one SKU, count its units in the orders export for the last 28 days: sold per day = units / 28.
2. Days left = on hand / sold per day; order-by = today + days left - lead time - 3 safety days.
3. Quantity = sold per day x (lead time + 30) - on hand, at least the supplier's minimum. Purchase-order drafts must list every SKU and quantity or a plain table is used.

agents/reorder_alerts.py

"""ONLINE STORES (Shopify, or any store that exports CSVs) - reorder alerts. Reads your inventory export (Shopify:
Products > Inventory > Export) and your orders export (Orders > Export), plus suppliers.csv (lead time + minimum order
per supplier). Plain code, no AI, for every SKU:
  sold per day over the last `days` days -> days of stock left -> the date you must order by
  (stock runs out minus the supplier's lead time minus `safety_days`) -> how many to order
  (enough for lead time + `cover_days`, at least the supplier's minimum).
The AI then drafts one purchase-order email per supplier; the code checks every SKU and quantity in it matches the
table, otherwise a plain table is used. Nothing is ordered or sent: drafts are in data/drafts/orders/."""
import math
from collections import defaultdict
from datetime import timedelta

from aios import llm

from ._common import col, money, save_draft, table, to_date, today

TITLE = "Reorder alerts"


def plan(sku, on_hand, sold, days, lead, safety, cover, minimum, now):
    rate = sold / days if days else 0
    left = on_hand / rate if rate else math.inf
    order_by = now + timedelta(days=math.floor(left - lead - safety)) if rate else None
    qty = max(minimum, math.ceil(rate * (lead + cover) - on_hand)) if rate else 0
    return {"sku": sku, "on_hand": on_hand, "per_day": round(rate, 2), "days_left": left, "order_by": order_by,
            "qty": qty}


def run(cfg, data, s):
    now, days = today(cfg), s.get("days", 28)
    sold = defaultdict(int)
    for r in table(cfg, s["orders"]):
        d = to_date(col(r, "created at", "date", "order date"))
        if d and now - timedelta(days=days) <= d < now and "refund" not in col(r, "financial status").lower():
            sold[col(r, "lineitem sku", "sku")] += int(money(col(r, "lineitem quantity", "quantity")))
    sups = [(col(r, "sku starts with"), r) for r in table(cfg, s["suppliers"])]
    rows = []
    for r in table(cfg, s["inventory"]):
        sku = col(r, "sku")
        if not sku:
            continue
        sup = next((x for pre, x in sups if sku.startswith(pre)), {})
        p = plan(sku, int(money(col(r, "on hand", "available", "quantity"))), sold[sku], days,
                 int(money(col(sup, "lead time days"))) or s.get("default_lead_days", 14), s.get("safety_days", 3),
                 s.get("cover_days", 30), int(money(col(sup, "minimum order"))), now)
        p.update(name=" ".join(x for x in (col(r, "title"), col(r, "option1 value")) if x),
                 supplier=col(sup, "supplier") or "?", email=col(sup, "email"))
        rows.append(p)
    rows.sort(key=lambda p: p["order_by"] or now + timedelta(days=9999))
    items, attention, due = [], [], defaultdict(list)
    for p in rows:
        when = p["order_by"]
        left = "no sales" if p["days_left"] == math.inf else f"{p['days_left']:.0f} days left"
        line = f"{p['sku']} {p['name']}: {p['on_hand']} in stock, {p['per_day']}/day, {left}"
        if when and when <= now + timedelta(days=s.get("warn_days", 7)):
            by = f"NOW - the order-by date was {when}" if when < now else f"by {when}"
            line += f" -> ORDER {by} ({p['qty']} units from {p['supplier']})"
            due[p["supplier"]].append(p)
            attention.append(f"{p['sku']} {p['name']}: order {p['qty']} {by} ({left})")
        elif when:
            line += f" -> order by {when}"
        items.append(line)
    for n, (sup, ps) in enumerate(sorted(due.items()), 1):
        table_txt = "\n".join(f"- {p['sku']} {p['name']}: {p['qty']} units" for p in ps)
        text = llm.ask(f"Write a short, polite purchase order email to our supplier {sup}. Order exactly these items "
                       f"and quantities, nothing else:\n{table_txt}\nAsk them to confirm the delivery date. Just the email body.")
        if not all(p["sku"] in text and str(p["qty"]) in text for p in ps):
            text = f"Hello {sup},\n\nWe would like to order:\n{table_txt}\n\nPlease confirm the delivery date.\n\nThank you."
        save_draft(data, "orders", n, ps[0]["email"] or sup, f"Purchase order - {sup}", text)
    return {"summary": f"{len(rows)} SKUs checked from {days} days of orders: {len(attention)} to reorder this week. "
                       f"{len(due)} purchase-order draft(s) - nothing ordered.", "items": items, "attention": attention}