Order-Manager/README.md

Order Manager

A PyQt6 desktop app for the daily order cycle: pull today's tickets from JIRA (8AM-3:30PM intake), then at end-of-day pull ShipStation tracking numbers and see, at a glance, what's fulfilled, what's cancelled, and what's still open from a previous day (carryover). Odoo integration is file-based for now (CSV export) since the live Odoo side is still in development.

One design note up front: every order is one row, sourced from JIRA. ShipStation only exists here to generate shipping labels for JIRA tickets (order/shipment numbers there are the same as the JIRA ticket number), so it never creates its own rows - "Pull Tracking Numbers" just merges tracking numbers onto the matching JIRA ticket. There's no "source" filter because there's effectively one source.

Setup

1. pip install -r requirements.txt
2. Run it once: python main.py (this creates .env from .env.example)
3. Click Settings and fill in:
   • JIRA: Site URL, Email, API Token, JQL (defaults to tickets created today - adjust to match your project/label). The deliverable field IDs are already defaulted from your export (customfield_10573,customfield_10570 for Signify; customfield_12790,customfield_13021 for Oak Street).
   • ShipStation: just the API Key.
   • Companies: SKU prefix -> company mapping (defaults to SH:Signify Health,OK:Oak Street Health).
   • Status Tracking: which statuses count as cancelled/fulfilled - defaults already match what you described (see below).
4. Click Import from JIRA during/after the intake window.
5. Click Pull Tracking Numbers (ShipStation) at end-of-day - it merges tracking numbers onto the matching tickets, no separate rows.
6. Check the Dashboard tab for Fulfilled / Cancelled / Carryover / Tracking Received counts.
7. Use All Orders to filter by company or search (ticket #, SKU, status, or tracking #), and Export Visible Orders to Odoo CSV for whatever's currently filtered.

Orders are cached locally in SQLite (orders.db). Re-importing updates existing tickets rather than duplicating them.

Three tabs: Active, Cancelled, Done

The tab a ticket shows up on is decided by an allowlist, not a list of every "finished" status:

• Active Orders: status is in ACTIVE_STATUSES (default just Created) - the only tickets that represent real work still to do.
• Cancelled: status is in CANCELLED_STATUSES (default Cancelled) and it was cancelled today. A ticket cancelled on a prior day falls through to Done instead - this tab is meant to be reviewed same-day and then filed away, not accumulate forever. Any transition into Cancelled also triggers a popup right after the import that caused it. Cancelled rows show in red text (rather than a background tint) wherever they appear, including if one later ages out into Done - it's still useful to see at a glance that a Done-tab row got there via cancellation rather than fulfillment.
• Done: everything else, automatically. This is deliberate - rather than maintaining a list of every status that means "finished" (Waiting For Return, Device Return Not Needed, and whatever your JIRA automation adds next, like 1st Contact Attempt), only the couple of statuses that mean "not done yet" (or "cancelled today") are named. Anything else lands on Done with zero config changes needed when your workflow adds another downstream status later.

This is a filter, not a physical move - it's the same orders table, split by current status (and, for Cancelled, cancelled_at) every time the view refreshes, so nothing to reconcile if a status ever changes back.

Within Done, the two fulfilled statuses (FULFILLED_STATUS_WITH_RETURN / FULFILLED_STATUS_WITHOUT_RETURN, defaulting to Waiting For Return / Device Return Not Needed) still get highlighted green and drive the "Fulfilled Today" dashboard count and fulfilled_at timestamp - they're the two statuses this app actually knows the meaning of, versus other done-statuses (like 1st Contact Attempt) which land on Done but aren't otherwise tracked, per "it is considered done...unnecessary to be tracked for us."

Carryover: tickets that didn't get closed out same-day don't need anything special - since ACTIVE_STATUSES defaults to just Created, every JIRA import re-checks any ticket still in that status regardless of when it was created, so it stays "alive" and its eventual status change gets picked up whenever it happens. The Dashboard's Carryover count is Active tickets created before today, plus today's tickets that arrived past the cutoff (see below) - both cases are known not to get done today, just at different points in the day.

Intake cutoff tracking

INTAKE_CUTOFF_TIME (default 15:30, i.e. 3:30 PM) flags any ticket created after that time on its arrival day with a checkmark in the Past Cutoff column, and the Dashboard shows how many of today's tickets arrived past cutoff. This flag expires at midnight - it means "came in too late for today's window," not a permanent marker, so a ticket that arrived late yesterday shows no checkmark today (it's Carryover now, a different, already-tracked concept). While it's still showing, it also counts toward Carryover immediately - not just once the date actually rolls over - unless it still gets fulfilled the same day despite arriving late, in which case it correctly drops out of both.

Table layout

Left to right: Company, Ticket #, Status, SKUs, Summary, Outgoing Tracking, Return Tracking, Uploaded, Past Cutoff, Created. Columns auto-size to their content on load and stay individually resizable by dragging (not force-stretched to equal widths) - the last column stretches to fill any remaining space. Uploaded shows a checkmark once a ticket has been successfully sent via the emergency "Send to ShipStation" action's API path specifically - the CSV export path intentionally doesn't set this, since exporting a file isn't the same as it actually being imported into ShipStation, and the app has no way to confirm that manual step happened.

How tracking numbers get pulled and matched

Confirmed against a real ShipStation payload from your queue (not guessed): a shipment's shipment_number and external_shipment_id both hold the ticket number directly (e.g. both were "AR-160269"). "Pull Tracking Numbers":

1. Bulk-fetches all of today's labels and all shipments from the last SHIPSTATION_LOOKBACK_DAYS (a code constant in shipstation_service.py, default 7 days - wide enough to catch a shipment that sat "pending" for a day or two before its label was generated). This replaced an earlier version that looked up each shipment individually (one HTTP call per ticket) - the actual cause of the slowness, now down to a handful of bulk list calls.
2. Fetches those (and any additional pages either needs) concurrently via a PyQt QThreadPool - several requests in flight at once instead of one after another. Capped at MAX_CONCURRENT_REQUESTS (default 5) to stay well clear of any ShipStation rate limit.
3. Only after both full batches have landed does it sort/correlate labels to shipments to ticket numbers, entirely in memory - reading tracking_number and is_return_label straight off each label, and the ticket number off shipment_number (checked first) or external_shipment_id, falling back to scanning the whole payload with TICKET_NUMBER_REGEX if neither is present.

If a label's ticket number doesn't match any ticket you have locally (e.g. JIRA hasn't been imported yet, or it's an account outlier), you'll get a popup listing which ones didn't match, rather than the data silently vanishing.

Known gap, deferred on purpose: there's a SKU for emailed labels that doesn't go through this shipment/label flow at all - those tickets just won't have tracking numbers pulled, which is expected for now, not a bug. Flag it when you're ready to handle it.

Packing: serial numbers and marking Done

Reflects your actual workflow: staff enter serial numbers (mostly via barcode scanner) and mark a ticket packed as devices go into the box - this is the beginning of what eventually becomes the Ship Sheet, built directly into the app instead of a separate spreadsheet.

Select a ticket and click Pack Ticket:

• Suggested fields, not a fixed schema. Device types and their field sets vary a lot by company and by kit (confirmed against your real Ship Sheet - Oak Street tracks OptiPlex/Laptop/Phone, Signify tracks iPad/Spiro/Laptop/Phone x2), so hard-coding columns per device type would fight the "versatile" requirement. Instead, fields are suggested from keywords in the ticket's line items (DEVICE_FIELD_SUGGESTIONS) - a starting point staff can always extend with a custom field. A "Shipping - Return Label X" line item is deliberately excluded from matching (tested against this - it describes a label deliverable, not an actual second device).
• Built for the scanner, not around it. Each field's Enter key (which a scanner sends automatically after scanning) jumps focus to the next field - scan straight through a device list with zero mouse clicks. After the last field, focus lands on the Packed checkbox rather than auto-submitting, so finishing still takes one deliberate action.
• Reopening a partially-packed ticket preserves whatever was already scanned and still shows the current suggestions for what's left - tested explicitly.
• This data is staff-entered, not sourced from JIRA, and a JIRA re-import never touches it - confirmed the update path doesn't reference serial_numbers/packed at all. It's exactly the data the eventual end-of-day JIRA push will need, whenever that gets built.

Assignee (who's working the ticket, from JIRA) and Shipping Method (the outbound label's service, from the ShipStation tracking pull - ups_ground -> "Ground", etc. via SHIPPING_METHOD_LABELS, confirmed against ShipStation's real UPS service codes) are also pulled in now, matching two more Ship Sheet columns.

One consequence worth knowing: since packing data doesn't come from JIRA, Reset Local Database now also clears it - the warning dialog says so explicitly. Before this update, a reset was always harmless (just a rebuildable cache); now it isn't, for this one kind of data.

Emergency: Send to ShipStation

For the rare case a ticket needs to skip the normal daily batch. Select a ticket in Active Orders, click Send to ShipStation (Emergency) in the toolbar, review the address/SKU summary shown, then pick one:

• Send via API Now - calls ShipStation's API directly (POST /v2/shipments with create_sales_order: true) to create the order without leaving the app. Needs SHIPSTATION_SIGNIFY_STORE_ID / SHIPSTATION_OAKSTREET_STORE_ID set in Settings.
• Export CSV Row... - writes a single-ticket CSV matching your real upload template exactly (verified column-for-column against a real export), for manual upload if the API is down.

Both exist on purpose, per "in case the API goes down we still have an option for CSV uploads."

Please verify your first live send, either path. ShipStation's own docs say automation rules apply tags to orders "when they import based on any criteria you set" - meaning your 90 box-packing rules should fire automatically off the SKU/item data, same as a normal CSV import, with no manual tagging needed from this app. That's the best read of their documentation, but it's not something testable without your real account - check that ShipStation packed and priced an emergency-sent order the way a normal one would before relying on this in an actual emergency.

One thing worth knowing: Quantity is always sent as 1 per line item today, since JIRA doesn't currently give a per-deliverable quantity. Say the word if that's ever not right.

Keeping .env in sync as new settings get added

.env is gitignored on purpose (it holds real credentials and field IDs), which means pulling new code never updates it automatically - new settings would otherwise sit silently blank until someone noticed a feature wasn't working (this is what caused an early version of the emergency-send feature to have no address data - the field IDs existed in .env.example but never made it into the real .env). Every startup now calls config.sync_env_with_example(), which adds any key present in .env.example but missing from .env, using the example's value as the default - without touching anything you've already set. Existing tickets in the local database still need a fresh Import from JIRA to pick up newly-added fields, though, since extraction only runs when a ticket is actually re-fetched.

How company/SKU extraction works

Each company has its own pair of "deliverable" custom fields in JIRA (e.g. Signify's Deliverables + Hardware Needed; Oak Street's own versions), configurable via JIRA_SIGNIFY_SKU_FIELDS / JIRA_OAKSTREET_SKU_FIELDS. Each deliverable value looks like SH011: Shipping - Return Label iPad - Physical in Box - the app pulls out just the SH011 code as the SKU and keeps the description for the summary. Company is resolved from the SKU prefix (SH/OK) via COMPANY_SKU_MAP. Since these tickets' actual JIRA "Summary" field is usually blank, the app falls back to the joined deliverable descriptions for the Summary column when there's nothing else there.

Resetting the local database

Reset Local Database in the toolbar clears every cached order (with a confirmation first). This is always safe - JIRA is the real source of truth, this is just a rebuildable cache - but it's worth knowing when you'd actually need it: if a ticket transitioned status under an older, buggy version of this app, its fulfilled_at/cancelled_at timestamp can get stuck wrong, since those are only recalculated at the moment of a transition - re-importing an already-transitioned ticket finds "no status change" and never touches that timestamp again. A reset clears the stale value entirely; the next Import from JIRA then stamps everything correctly from scratch. Follow a reset with Import from JIRA, and Pull Tracking Numbers if you rely on today's already-pulled tracking data.

Moving storage to your MariaDB LXC later

In Settings (or directly in .env), change:

DB_URL=mysql+pymysql://user:password@<lxc-ip>:3306/order_manager

and pip install pymysql. No code changes needed. Note: this is a local cache rebuilt from JIRA/ShipStation, so if you change DB_URL or the schema changes in a future update, it's safe to just delete orders.db and re-import rather than migrate it.

Project layout

main.py                        entry point
app/
  config.py                    reads/writes .env, defines the settings schema
  database.py                  SQLAlchemy engine/session (SQLite now, MariaDB later)
  models.py                    Order table - one row per JIRA ticket
  companies.py                 SKU-prefix -> company resolution
  status_rules.py              status-list parsing/matching, cancellation detection
  tracking.py                  tracking numbers -> suggested JIRA status
  queries.py                   "what tickets are still open" - used for JIRA re-checking
  workers.py                   background thread(s), save/enrich logic, dashboard stats
  services/
    base.py                    OrderService interface - implement this for new sources
    jira_service.py            JIRA REST API -> NormalizedOrder (SKU fields, company, re-check)
    shipstation_service.py     ShipStation V2 labels+shipments -> tracking numbers by ticket
    odoo_export.py             CSV export for the (in-development) Odoo import template
    __init__.py                SERVICE_REGISTRY - register new sources here
  ui/
    main_window.py             tabs, toolbar, import/export/notification wiring
    settings_dialog.py         auto-built from config.SETTINGS_SCHEMA
    widgets/
      dashboard.py             summary stat cards
      orders_table.py          sortable/filterable Qt table for orders
      order_detail_dialog.py   raw payload + tracking numbers + suggested status, per ticket

Adding real Odoo API access next

1. Add settings to app/config.py -> SETTINGS_SCHEMA (Odoo group).
2. Create app/services/odoo_service.py. If it's push-based (you're sending orders to Odoo, most likely given the workflow), it doesn't need to subclass OrderService - a push_orders(orders) method is fine, called from a new toolbar action the same way _on_export_clicked calls export_orders_to_csv.
3. Wire it up the same way the JIRA/ShipStation buttons are.

Emailed return labels (SH007 / OK012)

A different workflow entirely from the normal outbound-kit tickets: a customer already has product to return, and needs a return label emailed to them. Select a ticket in Active Orders and click Create Return Label:

• Shows the ticket's Description (parsed from JIRA's rich-text format into plain text) - this is where staff note what boxes are needed, so it's visible without opening JIRA.
• Lets you specify any number of packages, each with its own weight and dimensions - replacing the old fixed "1x1x1, 1oz dummy ticket" with real per-request control.
• Calls ShipStation directly (POST /v2/labels with is_return_label: true). Confirmed against ShipStation's own return-label docs: ship_from is the customer and ship_to is your warehouse - reversed from every other label this app creates, and easy to get backwards, so this was tested explicitly.
• charge_event is configurable per your risk preference: on_creation (pay immediately), on_carrier_acceptance (only pay if the customer actually ships it - needs the carrier to enable this on your account first, can take 3-4 weeks), or carrier_default.

This app does not email the label. Per your workflow, that happens from ShipStation itself (Returns tab -> Other Actions -> Send Return Label) so it goes out through your branded template - something this app couldn't replicate anyway, since ShipStation doesn't expose that step through its API as far as I could find. After a label is created, the ticket's Uploaded checkmark is set (same flag the emergency-send feature uses) so you can see at a glance which return-label tickets have already had their label created.

Both companies share one physical warehouse but each has its own UPS account (SIGNIFY_RETURN_CARRIER_ID / OAKSTREET_RETURN_CARRIER_ID)

• the return address is the same, just filed under the right company name. Occasional shipments to company HQ instead of the shared warehouse aren't handled yet - flagged for later, per your note.

Still an assumption pending confirmation: the service code (SIGNIFY_RETURN_SERVICE_CODE / OAKSTREET_RETURN_SERVICE_CODE) defaults to ups_ground since you gave me the carrier/account but not a specific service level - change it in Settings if that's not right.

Known open item

You mentioned occasionally shipping return items to a company's headquarters instead of the shared warehouse - noted, not built yet since you said we could tackle it later. When you're ready, this would likely be a dropdown in the Return Label dialog (Warehouse vs. HQ) rather than a new settings group, since the workflow is otherwise identical.