askrobots / dbbasic-object-server
Public
Runs live, versioned Python application objects on one VM, with a suite of apps: projects, notes, tasks, files and more.
danq9
signup: the person chooses the password and name on the confirm page
83c7aa6
2 weeks, 5 days ago
History
generate_tm_invoice.py · 11.6 KB · 307 lines
| 1 | """action_generate_tm_invoice -- approved hours and costs become one invoice. |
| 2 | |
| 3 | POST {project_id?, customer_name?, through_date?, grouping?, dry_run?} |
| 4 | |
| 5 | The last mile of time-and-materials billing -- both halves of it, since |
| 6 | the materials are half the name. Deliberately the same shape as the |
| 7 | period biller: collect what is owed, raise ONE invoice, mark the sources |
| 8 | so a re-run raises nothing. Everything downstream is the machinery that |
| 9 | already ships -- totals rollup, portal link, send, aging, dunning, |
| 10 | payment, books. |
| 11 | |
| 12 | Time and expenses land on the SAME invoice rather than two, because a |
| 13 | client engaged one firm for one project and should receive one bill for |
| 14 | it. Expenses bill at their stamped billable amount (cost plus whatever |
| 15 | markup was approved), and the line says so when there is a markup: a |
| 16 | pass-through cost quietly grossed up is the thing clients discover later |
| 17 | and remember. |
| 18 | |
| 19 | Idempotency lives on the entries rather than in a dedup table. An entry |
| 20 | that already carries an invoice_id is never billed again, which means a |
| 21 | generator that crashed halfway through can simply be run again: the |
| 22 | entries it already stamped are skipped, the rest are picked up. Double |
| 23 | billing a client for the same hours is the failure a consultancy does not |
| 24 | get to explain twice. |
| 25 | |
| 26 | What it will not do: rate anything. Rates were stamped at approval, back |
| 27 | when a human looked at the hours; re-deriving them here would let a rate |
| 28 | card edited last week silently reprice work approved last month. This |
| 29 | object only adds up numbers other people already agreed to. |
| 30 | """ |
| 31 | |
| 32 | import os |
| 33 | from datetime import date, timedelta |
| 34 | |
| 35 | import object_ids |
| 36 | import object_rates |
| 37 | import object_records |
| 38 | |
| 39 | ACTOR = "action_generate_tm_invoice" |
| 40 | |
| 41 | GROUPINGS = ("detail", "by_person", "by_task") |
| 42 | |
| 43 | |
| 44 | def _base_dir(): |
| 45 | return os.environ.get("DBBASIC_DATA_DIR", "data") |
| 46 | |
| 47 | |
| 48 | def _setting(base, key, default): |
| 49 | try: |
| 50 | for row in object_records.read_collection_records("app_settings", base_dir=base): |
| 51 | if row.get("key") == key and str(row.get("value") or "").strip(): |
| 52 | return row["value"].strip() |
| 53 | except Exception: |
| 54 | pass |
| 55 | return default |
| 56 | |
| 57 | |
| 58 | def _int(value, default=0): |
| 59 | try: |
| 60 | return int(str(value or "").strip() or default) |
| 61 | except (TypeError, ValueError): |
| 62 | return default |
| 63 | |
| 64 | |
| 65 | def _truthy(value): |
| 66 | return str(value or "").strip().lower() in ("true", "1", "yes", "on") |
| 67 | |
| 68 | |
| 69 | def _text(value): |
| 70 | return str(value if value is not None else "").strip() |
| 71 | |
| 72 | |
| 73 | def _billable_entries(base, project_id, through): |
| 74 | """Approved, unbilled hours for this project up to a date. |
| 75 | |
| 76 | An entry with an invoice_id is skipped no matter what its status says: |
| 77 | the stamp is the fact, and trusting status alone would re-bill an |
| 78 | entry somebody had hand-edited. |
| 79 | """ |
| 80 | try: |
| 81 | rows = object_records.read_collection_records("time_logs", base_dir=base) |
| 82 | except Exception: |
| 83 | return None |
| 84 | out = [] |
| 85 | for row in rows: |
| 86 | if _text(row.get("status")) != "approved": |
| 87 | continue |
| 88 | if _text(row.get("invoice_id")): |
| 89 | continue |
| 90 | if project_id and _text(row.get("project_id")) != project_id: |
| 91 | continue |
| 92 | worked = _text(row.get("started_at"))[:10] |
| 93 | if through and worked and worked > through: |
| 94 | continue |
| 95 | if _int(row.get("amount_cents")) <= 0: |
| 96 | continue |
| 97 | out.append(row) |
| 98 | return out |
| 99 | |
| 100 | |
| 101 | def _billable_expenses(base, project_id, through): |
| 102 | """Approved, unbilled, billable costs for this project up to a date. |
| 103 | |
| 104 | Unbillable expenses are skipped here and only here: they still posted |
| 105 | to the books when they were approved, which is where a cost the |
| 106 | client never sees belongs. |
| 107 | """ |
| 108 | try: |
| 109 | rows = object_records.read_collection_records("expenses", base_dir=base) |
| 110 | except Exception: |
| 111 | return [] |
| 112 | out = [] |
| 113 | for row in rows: |
| 114 | if _text(row.get("status")) != "approved": |
| 115 | continue |
| 116 | if _text(row.get("invoice_id")): |
| 117 | continue |
| 118 | if not _truthy(row.get("billable")): |
| 119 | continue |
| 120 | if project_id and _text(row.get("project_id")) != project_id: |
| 121 | continue |
| 122 | incurred = _text(row.get("incurred_on"))[:10] |
| 123 | if through and incurred and incurred > through: |
| 124 | continue |
| 125 | if _int(row.get("billable_amount_cents")) <= 0: |
| 126 | continue |
| 127 | out.append(row) |
| 128 | return out |
| 129 | |
| 130 | |
| 131 | def _expense_description(expense): |
| 132 | incurred = _text(expense.get("incurred_on"))[:10] |
| 133 | what = _text(expense.get("description")) or "Expense" |
| 134 | markup = _int(expense.get("markup_bps")) |
| 135 | if markup <= 0: |
| 136 | return f"{incurred} {what} (at cost)" |
| 137 | return f"{incurred} {what} (cost plus {markup / 100:g}%)" |
| 138 | |
| 139 | |
| 140 | def _group_key(entry, grouping): |
| 141 | if grouping == "by_person": |
| 142 | return _text(entry.get("owner_id")) or "unassigned" |
| 143 | if grouping == "by_task": |
| 144 | return _text(entry.get("task_id")) or "unassigned" |
| 145 | return _text(entry.get("id")) |
| 146 | |
| 147 | |
| 148 | def _describe(entries, grouping, base): |
| 149 | """One line's worth of English. |
| 150 | |
| 151 | A T&M line names the work, the hours and the rate, because the hours |
| 152 | are the whole argument for the number. A client who cannot see what |
| 153 | they are paying for can only dispute the total. |
| 154 | """ |
| 155 | first = entries[0] |
| 156 | seconds = sum(_int(e.get("duration_seconds")) for e in entries) |
| 157 | increment = 0 # already applied per entry at approval |
| 158 | worked_hours = object_rates.hours(seconds, increment) |
| 159 | rates = {_int(e.get("hourly_rate_cents")) for e in entries} |
| 160 | rate_note = (f" at {rates.pop() / 100:.2f}/hr" if len(rates) == 1 else |
| 161 | " at mixed rates") |
| 162 | |
| 163 | if grouping == "by_person": |
| 164 | who = _text(first.get("owner_id")) or "Unassigned" |
| 165 | return f"{who}: {worked_hours} hours{rate_note}" |
| 166 | if grouping == "by_task": |
| 167 | task = _text(first.get("task_id")) or "Unassigned" |
| 168 | title = _lookup(base, "tasks", task, "title") or task or "Work" |
| 169 | return f"{title}: {worked_hours} hours{rate_note}" |
| 170 | notes = _text(first.get("notes")) |
| 171 | worked_on = _text(first.get("started_at"))[:10] |
| 172 | label = notes or _lookup(base, "tasks", _text(first.get("task_id")), "title") or "Work" |
| 173 | return f"{worked_on} {label}: {worked_hours} hours{rate_note}" |
| 174 | |
| 175 | |
| 176 | def _lookup(base, collection, record_id, field): |
| 177 | if not record_id: |
| 178 | return "" |
| 179 | try: |
| 180 | row = object_records.get_collection_record(collection, record_id, base_dir=base) |
| 181 | except Exception: |
| 182 | return "" |
| 183 | return _text((row or {}).get(field)) |
| 184 | |
| 185 | |
| 186 | def POST(request): |
| 187 | base = _base_dir() |
| 188 | project_id = _text(request.get("project_id")) |
| 189 | through = _text(request.get("through_date"))[:10] or date.today().isoformat() |
| 190 | dry_run = _truthy(request.get("dry_run")) |
| 191 | grouping = _text(request.get("grouping")) or _setting( |
| 192 | base, "billing.tm_line_grouping", "detail") |
| 193 | if grouping not in GROUPINGS: |
| 194 | return {"status": 400, |
| 195 | "error": f"grouping must be one of {', '.join(GROUPINGS)}"} |
| 196 | |
| 197 | entries = _billable_entries(base, project_id, through) |
| 198 | if entries is None: |
| 199 | return {"ok": True, "skipped": "time tracking not installed (time_logs absent)"} |
| 200 | expenses = _billable_expenses(base, project_id, through) |
| 201 | if not entries and not expenses: |
| 202 | return {"ok": True, "invoiced": 0, |
| 203 | "note": "no approved, unbilled time or expenses in range"} |
| 204 | |
| 205 | buckets = {} |
| 206 | for entry in entries: |
| 207 | buckets.setdefault(_group_key(entry, grouping), []).append(entry) |
| 208 | |
| 209 | time_cents = sum(_int(e.get("amount_cents")) for e in entries) |
| 210 | expense_cents = sum(_int(e.get("billable_amount_cents")) for e in expenses) |
| 211 | total_cents = time_cents + expense_cents |
| 212 | if dry_run: |
| 213 | return {"ok": True, "would_invoice": len(entries) + len(expenses), |
| 214 | "time_entries": len(entries), "expenses": len(expenses), |
| 215 | "lines": len(buckets) + len(expenses), |
| 216 | "time_cents": time_cents, "expense_cents": expense_cents, |
| 217 | "total_cents": total_cents, "through_date": through, |
| 218 | "grouping": grouping} |
| 219 | |
| 220 | customer_name = (_text(request.get("customer_name")) |
| 221 | or _lookup(base, "projects", project_id, "name") |
| 222 | or "Customer") |
| 223 | due_days = _int(_setting(base, "billing.invoice_due_days", "14"), 14) |
| 224 | invoice_id = object_ids.new_uuid4() |
| 225 | owner = (_text(request.get("owner_id")) |
| 226 | or _text((entries or expenses)[0].get("owner_id"))) |
| 227 | marker = f"time_logs:{project_id or 'all'}:{through}" |
| 228 | |
| 229 | object_records.create_collection_record( |
| 230 | "invoices", |
| 231 | { |
| 232 | "id": invoice_id, |
| 233 | "number": f"TM-{(project_id or 'ALL')[:8]}-{through}", |
| 234 | "customer_id": _text(request.get("customer_id")), |
| 235 | "customer_name": customer_name, |
| 236 | "customer_email": _text(request.get("customer_email")), |
| 237 | "status": "draft", |
| 238 | "issue_date": through, |
| 239 | "due_date": (date.fromisoformat(through) |
| 240 | + timedelta(days=due_days)).isoformat(), |
| 241 | "subtotal_cents": str(total_cents), |
| 242 | "total_cents": str(total_cents), |
| 243 | "notes": f"Generated by {ACTOR} [{marker}]", |
| 244 | "owner_id": owner, |
| 245 | }, |
| 246 | base_dir=base, actor=ACTOR) |
| 247 | |
| 248 | lines = 0 |
| 249 | billed = 0 |
| 250 | for key in sorted(buckets): |
| 251 | group = buckets[key] |
| 252 | amount = sum(_int(e.get("amount_cents")) for e in group) |
| 253 | seconds = sum(_int(e.get("duration_seconds")) for e in group) |
| 254 | object_records.create_collection_record( |
| 255 | "invoice_lines", |
| 256 | { |
| 257 | "id": object_ids.new_uuid4(), |
| 258 | "invoice_id": invoice_id, |
| 259 | "description": _describe(group, grouping, base), |
| 260 | "quantity": object_rates.hours(seconds), |
| 261 | "unit_price_cents": str(_int(group[0].get("hourly_rate_cents"))), |
| 262 | "line_total_cents": str(amount), |
| 263 | "owner_id": owner, |
| 264 | }, |
| 265 | base_dir=base, actor=ACTOR) |
| 266 | lines += 1 |
| 267 | for entry in group: |
| 268 | # Stamp the entry BEFORE counting it billed: if this pass dies |
| 269 | # here, the next run skips what is already stamped rather than |
| 270 | # billing it twice. |
| 271 | object_records.update_collection_record( |
| 272 | "time_logs", entry["id"], |
| 273 | {"status": "billed", "invoice_id": invoice_id}, |
| 274 | base_dir=base, actor=ACTOR) |
| 275 | billed += 1 |
| 276 | |
| 277 | # Expenses are never grouped: one receipt, one line. Collapsing them |
| 278 | # would hide exactly what a client wants to see itemised. |
| 279 | expenses_billed = 0 |
| 280 | for expense in sorted(expenses, key=lambda e: (_text(e.get("incurred_on")), |
| 281 | _text(e.get("id")))): |
| 282 | amount = _int(expense.get("billable_amount_cents")) |
| 283 | object_records.create_collection_record( |
| 284 | "invoice_lines", |
| 285 | { |
| 286 | "id": object_ids.new_uuid4(), |
| 287 | "invoice_id": invoice_id, |
| 288 | "description": _expense_description(expense), |
| 289 | "quantity": "1", |
| 290 | "unit_price_cents": str(amount), |
| 291 | "line_total_cents": str(amount), |
| 292 | "owner_id": owner, |
| 293 | }, |
| 294 | base_dir=base, actor=ACTOR) |
| 295 | lines += 1 |
| 296 | object_records.update_collection_record( |
| 297 | "expenses", expense["id"], |
| 298 | {"status": "billed", "invoice_id": invoice_id}, |
| 299 | base_dir=base, actor=ACTOR) |
| 300 | expenses_billed += 1 |
| 301 | |
| 302 | return {"ok": True, "invoiced": 1, "invoice_id": invoice_id, |
| 303 | "entries_billed": billed, "expenses_billed": expenses_billed, |
| 304 | "lines": lines, "time_cents": time_cents, |
| 305 | "expense_cents": expense_cents, |
| 306 | "total_cents": total_cents, "through_date": through, |
| 307 | "grouping": grouping} |