Metadata-Version: 2.4
Name: report-engine-kit
Version: 0.3.0
Summary: Framework-agnostic report & tabular-pivot engine: registry data sources, in-memory pivot, formula cells, cross-report references, xlsx/csv/pdf/html/json export, CLI; with an optional Django app.
Author: bzsystem team
License-Expression: MIT
Project-URL: Homepage, https://github.com/bzsystem/report-engine-kit
Project-URL: Repository, https://github.com/bzsystem/report-engine-kit
Project-URL: Documentation, https://github.com/bzsystem/report-engine-kit#readme
Project-URL: Issues, https://github.com/bzsystem/report-engine-kit/issues
Project-URL: Changelog, https://github.com/bzsystem/report-engine-kit/blob/main/CHANGELOG.md
Keywords: report,pivot,pivot-table,analytics,excel,django,csv,pdf,dashboard
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Office/Business
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Classifier: Framework :: Django :: 4.2
Classifier: Framework :: Django :: 5.0
Classifier: Framework :: Django :: 5.1
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Provides-Extra: xlsx
Requires-Dist: openpyxl>=3.1; extra == "xlsx"
Provides-Extra: pdf
Requires-Dist: reportlab>=4.0; extra == "pdf"
Provides-Extra: pandas
Requires-Dist: pandas>=2.0; extra == "pandas"
Provides-Extra: django
Requires-Dist: Django>=4.2; extra == "django"
Provides-Extra: yaml
Requires-Dist: PyYAML>=6.0; extra == "yaml"
Provides-Extra: dev
Requires-Dist: pytest>=7.4; extra == "dev"
Requires-Dist: build>=1.0; extra == "dev"
Requires-Dist: twine>=5.0; extra == "dev"
Requires-Dist: openpyxl>=3.1; extra == "dev"
Requires-Dist: reportlab>=4.0; extra == "dev"
Requires-Dist: Django>=4.2; extra == "dev"
Provides-Extra: all
Requires-Dist: openpyxl>=3.1; extra == "all"
Requires-Dist: reportlab>=4.0; extra == "all"
Requires-Dist: pandas>=2.0; extra == "all"
Requires-Dist: Django>=4.2; extra == "all"
Requires-Dist: PyYAML>=6.0; extra == "all"
Dynamic: license-file

# report-engine-kit

[![PyPI](https://img.shields.io/badge/PyPI-report--engine--kit-blue)](https://pypi.org/project/report-engine-kit/)
[![Python](https://img.shields.io/badge/Python-3.9%20%E2%80%93%203.13-success)](https://www.python.org/)
[![License](https://img.shields.io/badge/license-MIT-green)](LICENSE)
[![tests](https://img.shields.io/badge/tests-110%20passed-brightgreen)]()

[English](#english) | [中文文档](#中文文档)

---

<a id="english"></a>
## English

**report-engine-kit** is a **framework-agnostic Python report & pivot-table
engine** with an optional Django integration layer. Extracted from a
production drawing/change-management system, it ships three reporting
capabilities:

1. **Fixed-statistic chart data sources** — register a Python function or
   class to produce standard `ReportData` (Chart.js-compatible JSON),
   exportable to `xlsx / csv / pdf / json / html / md`.
2. **An in-memory pivot engine (Excel-pivot-like)** — feed it a
   "headers + string rows" table (CSV, JSON, list[dict], pandas DataFrame or
   an ORM queryset), and users drive a full pivot table with one JSON
   config: multi-table comparison, column-dimension cross-tabs,
   sum/average/min/max/distinct-count, multi-level subtotals, per-metric
   sorting & Top N, percent/running-total derived metrics, multi-level
   headers, custom rows/columns, Excel-style formulas, cross-report
   references, manual overrides, blank rows/columns, row/column moving, row
   heights & column widths …
3. **An optional Django app** (`[django]` extra) — generic HTTP views, a
   saved-report model with migrations, an ORM drag-and-drop pivot engine
   (single-SQL conditional aggregation), a cross-report snapshot refresher,
   plus front-end pages and JS assets.

> **The core has zero third-party dependencies.** openpyxl, reportlab,
> pandas, Django and PyYAML are all opt-in extras; the core runs on the
> Python standard library alone.

### Table of contents

- [1. What it is / is not](#1-what-it-is--is-not)
- [2. Installation](#2-installation)
- [3. Five-minute quickstart](#3-five-minute-quickstart)
- [4. Tutorial A — Fixed-statistic data sources](#4-tutorial-a--fixed-statistic-data-sources)
- [5. Tutorial B — In-memory pivot (core tutorial, full JSON config)](#5-tutorial-b--in-memory-pivot-core-tutorial-full-json-config)
- [6. Tutorial C — Declarative chart analytics (no-code charts)](#6-tutorial-c--declarative-chart-analytics-no-code-charts)
- [7. Tutorial D — Command line (CLI)](#7-tutorial-d--command-line-cli)
- [8. Tutorial E — Saving reports without a database (JSON file store)](#8-tutorial-e--saving-reports-without-a-database-json-file-store)
- [9. Tutorial F — Django integration](#9-tutorial-f--django-integration)
- [10. Counting semantics (read first)](#10-counting-semantics-read-first)
- [11. Caching & configuration](#11-caching--configuration)
- [12. Security model](#12-security-model)
- [13. Capability map vs an Excel pivot table](#13-capability-map-vs-an-excel-pivot-table)
- [14. Project layout](#14-project-layout)
- [15. Development, testing & release](#15-development-testing--release)
- [16. FAQ](#16-faq)
- [License](#license-en)

---

### 1. What it is / is not

**Good fits**

- Your data is fundamentally a *ledger table* (headers + rows, cells mostly
  text/dates/amounts) and end users want to drag out statistics in the
  browser like an Excel pivot table.
- You already have a bunch of statistic functions and want one registry, one
  cache, one export pipeline and one Chart.js contract for all of them.
- You want report configs (filters, dimensions, measures, styles, formulas)
  to be fully JSON-driven — persistable and recomputed per-viewer
  permissions across processes.
- You work in Django / Flask / FastAPI / plain scripts / pandas but don't
  want to be tied to one framework.

**Not a good fit**

- Billion-row OLAP (the in-memory engine targets ledger-sized data and walks
  representative rows in memory; for very large data use the ORM
  conditional-aggregation engine in the `[django]` extra, or aggregate in
  SQL inside your data source).
- A full BI suite (dashboard authoring, row/column permission matrices,
  data-source management UI). This package ships the **engine and a generic
  builder page**; menus/navigation stay in the host system.

---

### 2. Installation

```bash
pip install report-engine-kit                  # core only: csv/json/html/md + all pivot math
pip install "report-engine-kit[xlsx,pdf]"      # Excel/PDF exporters
pip install "report-engine-kit[pandas]"        # DataFrame adapter
pip install "report-engine-kit[yaml]"          # YAML configs for the CLI
pip install "report-engine-kit[django]"        # Django app + ORM pivot engine
pip install "report-engine-kit[all]"           # everything
pip install "report-engine-kit[dev]"           # development/testing
```

Python 3.9–3.13. Verify:

```bash
report-engine --help
python -c "import report_engine_kit as r; print(r.__version__)"
```

Naming conventions:

| Name | Form | Example |
|---|---|---|
| **Distribution** (pip install) | hyphenated | `report-engine-kit` |
| **Python import** | underscored | `import report_engine_kit` |
| **CLI command** | hyphenated | `report-engine domains ...` |

---

### 3. Five-minute quickstart

Given an orders CSV, build a pivot "orders & revenue by region" and export
Excel in ~20 lines:

```python
from report_engine_kit.tabular_pivot import (
    register_domain, PivotService, SimpleUser, table_from_csv)

# 1) Register the CSV as a "domain". Column 0 is the order code (dedup key).
register_domain(
    "orders", "Orders",
    load_tables=lambda force=False: [
        table_from_csv("orders.csv", key="orders", label="Orders", code_col=0),
    ],
    can_access=lambda user: True,
    can_manage=lambda user: True,
)

# 2) Execute a config through the service facade
svc = PivotService("orders", user=SimpleUser(id=1))
config = {
    "sheets": ["orders"],
    "row_dimensions": ["Region"],
    "metrics": [
        {"key": "cnt", "label": "Orders"},                    # default = count
        {"key": "rev", "label": "Revenue", "agg": "sum",
         "value_field": "Amount"},                            # summation
    ],
    "global_filters": {},
}
result = svc.execute(config)
for row in result["rows"]:
    print(row["kind"], row["dim_values"], row["values"])

# 3) Export (xlsx / csv / json; xlsx needs pip install "report-engine-kit[xlsx]")
open("orders.xlsx", "wb").write(svc.export_xlsx(config).getvalue())
open("orders.csv", "wb").write(svc.export_csv(config))
```

A runnable version lives in [`examples/02_tabular_pivot.py`](examples/02_tabular_pivot.py);
every config field is explained in [Tutorial B](#5-tutorial-b--in-memory-pivot-core-tutorial-full-json-config).

Result shape (excerpt):

```json
{
  "domain": "orders",
  "dim_labels": ["Region"],
  "metric_columns": [{"key": "cnt", "label": "Orders"}, "..."],
  "rows": [
    {"kind": "data", "dim_values": ["East"], "values": {"cnt": 12, "rev": 3500}},
    {"kind": "totals", "dim_values": ["Total"], "values": {"cnt": 20, "rev": 9800}}
  ],
  "totals": {"cnt": 20, "rev": 9800},
  "layout": ["..."]
}
```

Runnable examples in [`examples/`](examples/):

```bash
python examples/01_data_source.py               # fixed-statistic chart
python examples/02_tabular_pivot.py             # CSV -> pivot -> xlsx
python examples/03_saved_reports_file_store.py  # saving without a DB
python examples/04_analytics_chart.py           # declarative analytics
```

---

### 4. Tutorial A — Fixed-statistic data sources

For charts with fixed metrics and parameterized queries (status
distribution, monthly trends, KPI cards).

#### 4.1 Data contract

| Class | Field | Meaning |
|---|---|---|
| `ReportParams` | `start_date / end_date` | `datetime.date`, inclusive |
| | `profession_filter` | business-scope string (app-defined meaning) |
| | `time_dimension` | `day/week/month/quarter/year` |
| | `field_key` | field key for field statistics |
| | `extra` | any JSON-serializable extension params |
| `ReportData` | `chart_type` | `bar/line/pie/doughnut/polarArea/radar/table` |
| | `title / labels / summary` | title, x-axis labels, summary dict |
| | `datasets` | `[Dataset(label, data, backgroundColor?, borderColor?, fill?, extra?)]` |

`to_dict()` is exactly the JSON Chart.js consumes; `from_dict()` parses it
back.

#### 4.2 Option 1 — Subclassing (recommended: drilldown, fields, cache)

```python
from datetime import date
from report_engine_kit import (
    ReportDataSource, ReportData, Dataset, ReportParams,
    DataSourceRegistry, export_report_data)

@DataSourceRegistry.register
class OrdersSource(ReportDataSource):
    name = "orders_by_status"      # required, unique within app_label
    label = "Orders by status"
    chart_type = "bar"
    cache_ttl = 60                 # seconds; 0 = no engine cache
    app_label = "shop"             # scope isolation; "" = visible to all apps

    def fetch(self, params: ReportParams) -> ReportData:
        rows = my_db.query_orders(params.start_date, params.end_date)  # your query
        return ReportData(
            title=f"Orders ({params.start_date} ~ {params.end_date})",
            labels=[r["status"] for r in rows],
            datasets=[Dataset(label="Orders", data=[r["n"] for r in rows])],
            summary={"total": sum(r["n"] for r in rows)},
        )

    # optional cell drilldown
    def drilldown(self, params, group_key: str) -> "DrilldownData":
        from report_engine_kit import DrilldownData
        items = my_db.query_detail(group_key)
        return DrilldownData(
            headers=["Order", "Amount"],
            rows=[[x["code"], x["amount"]] for x in items],
            total=len(items))

    # optional dynamic field metadata (for UI field pickers)
    def list_fields(self, params):
        from report_engine_kit import FieldMeta
        return [FieldMeta("status", "Status"), FieldMeta("dept", "Dept")]

# fetch with app_label isolation
data = DataSourceRegistry.fetch(
    "orders_by_status",
    ReportParams(start_date=date(2026, 1, 1), end_date=date(2026, 9, 30)),
    app_label="shop")

open("orders.xlsx", "wb").write(export_report_data(data, "xlsx"))
```

#### 4.3 Option 2 — Register an existing function (zero changes)

The adapter introspects the signature and passes ISO date strings:

```python
from report_engine_kit import register_legacy_function

def get_sales(start_date=None, end_date=None, profession_filter=None):
    return {"chart_type": "line", "title": "Sales trend",
            "labels": ["Jun", "Jul", "Aug"],
            "datasets": [{"label": "Revenue", "data": [10, 20, 30]}]}

register_legacy_function(
    "sales", "Sales trend", get_sales,
    chart_type="line",
    supports_drilldown=False,
    drilldown_fn=None,          # or (params, group_key) -> DrilldownData
    app_label="shop",
    cache_ttl=None)             # None = global LEGACY_SOURCE_CACHE_TTL
```

#### 4.4 Six export formats

```python
from report_engine_kit import export_report_data
for fmt in ("csv", "json", "md", "html", "xlsx", "pdf"):
    try:
        with open(f"out.{fmt}", "wb") as f:
            f.write(export_report_data(data, fmt))
    except Exception as e:
        print(fmt, "needs its extra:", e)
```

| Format | Extra | Notes |
|---|---|---|
| `csv` | none | utf-8-sig BOM (Excel-friendly); cells starting with `= + - @` get a tab prefix (formula-injection guard) |
| `json` | none | full payload; adds `params` when provided |
| `md` | none | Markdown pipe table, escapes `\|`/newlines |
| `html` | none | standalone styled HTML with optional embedded Chart.js; `chart_cdn_url=""` for full offline |
| `xlsx` | `xlsx` (openpyxl) | styled headers, CJK-aware auto width, frozen header, compact JSON scalars, injection guard |
| `pdf` | `pdf` (reportlab) | A4 table; auto CJK font discovery (simhei/msyh/simsun, wqy/noto, `REPORT_ENGINE_FONT_PATH`) |

Custom exporter:

```python
from report_engine_kit.export import BaseExporter, ExporterRegistry

@ExporterRegistry.register
class ParquetExporter(BaseExporter):
    format_name = "parquet"
    content_type = "application/vnd.apache.parquet"
    file_extension = "parquet"
    def export(self, data, params=None) -> bytes: ...
```

---

### 5. Tutorial B — In-memory pivot (core tutorial, full JSON config)

#### 5.1 The data contract

```python
DataTable(
    key="cr",                 # unique table key inside the domain
    label="CR",               # display name / column-dimension value
    headers=["Code", "Status", "Date", "Amount"],
    rows=[TableRow(code="CR-001", cells={"0": "CR-001", "1": "Closed",
                                         "2": "2026-02-10", "3": "200"})],
    code_col=0,               # dedup code column
    status_col=1,             # __status__ semantic dimension maps here
    date_cols=[2],            # date columns (headers containing date/time auto-detected)
    version="20260930_v1",    # version bump drives the representative-row L1 cache key
)
```

**Table loader**: `load_tables(force) -> list[DataTable]`; `force=True`
bypasses business-side caches.

#### 5.2 Register a domain (all permission hooks live here)

```python
register_domain(
    code="change", label="Change management",
    load_tables=load_change_tables,
    can_access=lambda u: u.is_authenticated,             # read
    can_manage=lambda u: u.is_staff,                     # create/save-as/publish
    restricted_cols=lambda u, t: {3} if not u.is_staff else set(),  # hidden columns
    blocked_header=lambda name, t: name == "Internal note",
    default_tables=["cr", "fcr"],
    drill_resolver=my_drill_builder,                     # numeric-cell drilldown
)
```

| Hook | Signature | Purpose |
|---|---|---|
| `can_access(user)` | → bool | enforced in service + HTTP layers |
| `can_manage(user)` | → bool | create/edit/publish; defaults to can_access |
| `restricted_cols(user, table)` | → set[int] | columns hidden from this user (**cache key includes the permission fingerprint**) |
| `blocked_header(name, table)` | → bool | extra blocked columns; by default flow headers containing 分发/回收/接收/归还 are also blocked |
| `drill_resolver(config, row_key, metric_key, restricted)` | → list[dict] | drilldown → `[{code,label,count,url}]` |

#### 5.3 Build a config and execute

```python
config = {
    # tables (__type__ dimension available with multiple tables)
    "sheets": ["cr", "fcr"],

    # row dimensions (up to 4); __type__=table, __status__=semantic status,
    # anything else is a literal header name
    "row_dimensions": ["__type__", "__status__"],
    # dimensions can also filter values: {"key": "Dept", "values": ["EM4","EM5"]}

    # column dimension (cross-tab, 1 level): each measure fans out per value
    "column_dimension": "Dept",

    # measures (up to 30)
    "metrics": [
        {
            "key": "total", "label": "Total", "group": "Phase 1",  # same group -> multi-level header
            "agg": "count",                                       # count/sum/avg/min/max/distinct
            "col_filters": [                                      # per-measure filters
                {"name": "Close date", "values": [], "mode": "include",
                 "keyword": "",
                 "date_rule": {"col": "Close date", "mode": "range",
                               "start": "2026-01-01", "end": "2026-03-31"}}],
            "filter_logic": "and",                                # and / or
            "date_rule": {"col": "Import date", "mode": "year"},  # own date window
            "number_format": {"decimals": 0, "thousands": True}
        },
        {"key": "rev", "label": "Amount", "agg": "sum",
         "value_field": "Amount",
         "number_format": {"decimals": 2, "thousands": True, "prefix": "$"}},
        {"key": "share", "label": "Share", "calc": "pct_of_total",
         "calc_source": "rev",
         "number_format": {"percent": True, "decimals": 1}},
        {"key": "cum", "label": "Cumulative", "calc": "running_total",
         "calc_source": "rev"}
    ],

    # global filters (common row set; each measure then adds its own)
    "global_filters": {
        "date_col": "", "start": "", "end": "",                  # legacy fields
        "date_rule": {"col": "Import date", "mode": "month"},   # new rule takes precedence
        "col_filters": [{"name": "Dept", "values": ["EM4"], "mode": "exclude"}],
        "filter_logic": "and"
    },

    # row/column control
    "show_totals": True,            # grand-total row
    "show_column_totals": True,     # per-measure "Total" column under a column dimension
    "hide_empty_rows": False,       # hide all-zero rows
    "subtotals": True,              # insert subtotals for multi-level row dimensions
    "sort": {"by": "rev", "dir": "desc"},   # sort by a measure
    "row_limit": 10,                # Top N (data rows only; totals unaffected)

    # manual overrides (applied outside cache; totals recomputed)
    "manual_overrides": {'["Closed"]': {"total": 99}},

    # custom rows / columns (formulas in 5.6)
    "custom_rows": [{"id": "r1", "label": "Monthly target",
                     "values": {"total": 100}, "formulas": {}}],
    "custom_columns": [
        {"id": "cc1", "label": "Double", "template": "=B1*2",
         "cells": {"1": {"t": "n", "v": 3}}}
    ],

    # layout editing (see 5.7)
    "spacer_rows": [{"id": "sr1", "after": -1, "height": 30}],
    "spacer_cols": [{"id": "sc1", "after": "dim:__status__", "width": 24}],
    "row_heights": {"data": 26, "totals": 32},
    "col_widths": {"metric:rev": 160},
    "layout_overrides": {"row_order": [], "col_order": [], "hidden_rows": []},

    # text banners, manual merges, footer
    "custom_texts": [], "custom_merges": [],
    "footer_text": "Source: change ledger"
}

result = svc.execute(config)
```

#### 5.4 Value aggregations (Excel "value field settings")

| agg | Meaning | Needs value_field | Notes |
|---|---|---|---|
| `count` | row count | no | representative rows (blank codes excluded) |
| `sum` | summation | yes | non-numeric cells ignored |
| `avg` | average | yes | averages numeric cells only (Excel semantics) |
| `min` / `max` | min / max | yes | |
| `distinct` | distinct count | yes | distinct numeric values |

Numeric parsing is lenient: `"1,234.5"`, `"$200"`, `"~300"` all parse.

Derived metrics:

| calc | Meaning | Requirement |
|---|---|---|
| `pct_of_total` | fraction of the column grand total (stored 0–1) | `calc_source` |
| `running_total` | cumulative sum across rows in order | `calc_source` |

`number_format`:

```json
{"decimals": 2, "thousands": true, "percent": true,
 "prefix": "$", "suffix": "", "multiplier": 1}
```

xlsx export writes real Excel cell formats; CSV/HTML render formatted text.

#### 5.5 Date rules — 7 modes

| mode | Meaning | Extra fields |
|---|---|---|
| `today` | today | — |
| `month` | first of month ~ today | — |
| `year` | Jan 1 ~ today | — |
| `recent` | last N days | `days` (0–3650) |
| `range` | fixed interval | `start`, `end` (YYYY-MM-DD) |
| `offset` | relative offset (overdue etc.) | `base`(today/fixed), `baseDate`, `sign`(plus/minus), `unit`(day/month/year), `amount`, `cmp`(gt/lt) |
| `wheel` | rolling window (quarter-to-date etc.) | `s`/`e` as `{y,m,d,mode,cur}` parts following current year/month/day |

`wheel` example (first day of quarter ~ today; endpoints normalized so
start>end never silently empties the table):

```json
{"col": "Close date", "mode": "wheel",
 "s": {"y": {"mode": "cur"}, "m": {"mode": "fix", "v": 10}, "d": {"mode": "fix", "v": 1}},
 "e": {"y": {"mode": "cur"}, "m": {"mode": "cur"}, "d": {"mode": "cur"}}}
```

Column filters support multi-value include/exclude, keyword
contains/not-contains, per-group AND/OR; blank sentinels `(blank)` /
`(non-blank)`.

#### 5.6 Formula DSL (Excel-flavored; custom parser, never eval)

| Category | Contents |
|---|---|
| Operators | `+ - * /`, parentheses, unary signs, comparisons `= == <> != < <= > >=` (return 1/0) |
| Cells | `A1`, ranges `B1:B10` (physical coordinates, 1-based) |
| Cross-report | `[12]C3`, range `[12]A1:A10` |
| Functions | `SUM / AVG / MIN / MAX / COUNT / ROUND / ABS / SQRT / POWER / MOD / IF / AND / OR / NOT` |
| Custom columns | `template` for relative column fill (shifts per row); per-cell `{t:"n",v}` or `{t:"f",v:"=..."}` |
| Error codes | `#VALUE! #REF! #DIV/0! #CYCLE! #NAME? #NUM!` |
| Guards | formula length ≤200, depth ≤100, cross-report chain ≤20, cycle detection |

```text
=SUM(B1:B5)+ROUND(AVG(C1:C5)*0.1, 2)
=IF(B1>=100, C1*0.9, C1)
=[12]C3 + [12]D3
```

#### 5.7 Layout editing (added in 0.3.0, applied server-side)

```json
{
  "col_widths": {"dim:Region": 200, "metric:rev": 160,
                 "ccol:cc1": 120, "spacer:sc1": 24},
  "row_heights": {"title": 36, "header": 28, "data": 24,
                  "subtotal": 26, "totals": 30, "spacer": 12},
  "spacer_rows": [{"id": "sr1", "after": -1, "height": 30},
                  {"id": "sr2", "after": 4, "height": 16}],
  "spacer_cols": [{"id": "sc1", "after": "dim:Region", "width": 24},
                  {"id": "sc2", "after": "metric:rev"}],
  "layout_overrides": {
    "row_order": ["[\"West\"]", "[\"East\"]"],
    "col_order": ["metric:rev", "metric:cnt", "ccol:cc1"],
    "hidden_rows": ["[\"Void\"]"]
  }
}
```

- **Column widths**: token → px (8–800); stay correct after column moves,
  fan-out and inserted gaps.
- **Row heights**: per row kind or per element id (`spacer:<id>`,
  `customrow:<id>`); xlsx converts px → points (×0.75).
- **Blank rows**: `after` anchors to a data-row index (-1 = top); they
  break dimension auto-merges like a real inserted Excel row and shift
  formula/merge coordinates consistently; excluded from flat CSV.
- **Blank columns**: anchors `__first__ / head / dim:<key> /
  metric:<key> / ccol:<id>`; borderless gaps.
- **Move rows**: `row_order` holds JSON row keys — listed rows move to the
  top in order, unlisted rows keep natural order after them;
  `hidden_rows` removes rows and recomputes subtotals/totals.
- **Move columns**: metric blocks and custom columns are movable (a
  fan-out metric moves as one block); head/dim columns stay fixed.

All moves/inserts are driven by one canonical **column plan**
(`result.column_plan` = `{blocks, width, metric_phys, ccol_phys,
spacer_phys}`), shared by formula coordinates, merge validation, headers and
xlsx — no coordinate drift.

#### 5.8 Export

```python
buf = svc.export_xlsx(config, report_name="Quarterly")   # BytesIO with all styles/formats/subtotals/spacers
open("r.xlsx", "wb").write(buf.getvalue())

csv_bytes = svc.export_csv(config, formatted=True)        # flat CSV with subtotal/total row tags
json_bytes = svc.export_json_bytes(config)                # raw result JSON
```

The first CSV column tags the row kind: `data / subtotal / total / custom`.

#### 5.9 Theming (light/dark) & white-label

The shipped builder is fully tokenized — every color is a CSS variable
defined once in the static `theme.css` (`:root` for light,
`[data-theme="dark"]` for dark).

- The toolbar moon/sun button toggles the theme; the choice persists in
  `localStorage` (`tpv-theme`).
- **White-label without forking**: override the `--tpv-*` variables in
  your own stylesheet (brand color, surfaces, borders, radius…) — the
  component markup never changes.
- The builder now also exposes the **entire engine surface in the UI**:
  per-measure aggregation (count/sum/avg/min/max/distinct), derived
  metrics and number formats, plus a collapsible "高级布局" card for the
  column dimension, multi-level subtotals, sort/Top N, blank rows/columns
  and row heights. All choices round-trip through save/reload.

---

### 6. Tutorial C — Declarative chart analytics (no-code charts)

Instead of writing `fetch()` for every chart, hand list[dict] rows to
`aggregate_rows` with a declarative spec:

```python
from report_engine_kit import Dimension, Measure, aggregate_rows

data = aggregate_rows(
    rows,                                   # any list[dict]
    dimensions=[
        Dimension("month", "Month", date_trunc="month"),
        Dimension("status", "Status"),      # 2nd dimension automatically becomes the series
    ],
    measures=[
        Measure("orders", "Orders"),                                     # count
        Measure("revenue", "Revenue", agg="sum", field_name="amount"),
        Measure("aov", "AOV", agg="avg", field_name="amount"),
        Measure("buyers", "Buyers", agg="distinct_count",
                field_name="customer_id"),
    ],
    filters=[
        {"key": "status", "op": "in", "values": ["paid"]},
        {"key": "amount", "op": "gte", "values": ["0"]},
        {"key": "dept", "op": "contains", "value": "EM"},
    ],
    series_dimension=None,    # defaults to the second dimension
    sort="-orders::paid",     # label / -label / measure key / -measure key
    top_n=20,
    chart_type="bar",
)
```

- Filter ops: `in / not_in / contains / not_contains / eq / ne / gt / gte /
  lt / lte / between / blank / nonblank`
- Date buckets: `day / week / month / year`; blanks go to `(blank)` and
  always sort last.
- The same spec can **drive a data source over HTTP**, so end users add new
  charts without server-side code:

```python
from report_engine_kit import analytics_source_factory, ReportParams, DataSourceRegistry

analytics_source_factory(
    "shop_dyn", "Shop dynamic",
    rows_loader=lambda params: my_db.fetch_all(),   # -> list[dict]
    app_label="shop")

params = ReportParams(extra={
    "dimensions": [{"key": "dept"}],
    "measures": [{"key": "n", "label": "Count"}],
    "filters": [{"key": "status", "op": "in", "values": ["paid"]}],
})
data = DataSourceRegistry.fetch("shop_dyn", params, app_label="shop")
```

Runnable example: [`examples/04_analytics_chart.py`](examples/04_analytics_chart.py).

---

### 7. Tutorial D — Command line (CLI)

Any Python module that calls `register_domain(...)` on import can be the
"domain module" (see [`examples/shop_domain.py`](examples/shop_domain.py)).

```bash
# list registered domains
report-engine --domain-module shop_domain domains

# available row dimensions / date columns
report-engine --domain-module shop_domain dimensions --domain shop_orders

# field options (with counts, pagination, keyword)
report-engine --domain-module shop_domain field-options --domain shop_orders \
    --field Status --limit 20

# run an ad-hoc config (JSON; YAML with the [yaml] extra)
report-engine --domain-module shop_domain execute --domain shop_orders \
    --config pivot.json -o result.json

# export (xlsx / csv / json)
report-engine --domain-module shop_domain export --domain shop_orders \
    --config pivot.json --format xlsx --out r.xlsx --name Quarterly

# save/list/run/publish/delete reports without a database (JSON file store)
report-engine --domain-module shop_domain reports save \
    --domain shop_orders --store file:reports.json --config pivot.json
report-engine --domain-module shop_domain reports list \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports run 1 \
    --domain shop_orders --store file:reports.json --format xlsx --out r1.xlsx
report-engine --domain-module shop_domain reports public 1 \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports delete 1 \
    --domain shop_orders --store file:reports.json
```

Global options: `--user-id` (defaults to env `REPORT_ENGINE_USER_ID`),
`--admin`, `--store memory|file:PATH`.

---

### 8. Tutorial E — Saving reports without a database (JSON file store)

```python
from report_engine_kit.tabular_pivot import (
    PivotService, JsonFileReportStore, SimpleUser, register_domain)

register_domain("orders", "Orders",
                load_tables=..., can_access=lambda u: True,
                can_manage=lambda u: True)

store = JsonFileReportStore("data/reports.json")   # atomic writes (temp + os.replace)
svc = PivotService("orders", user=SimpleUser(id=1, is_superuser=True), store=store)

record = svc.save_report({"name": "Sep board", **pivot_config})
print(svc.list_reports(scope="available"))
print(svc.list_reports(scope="public"))
result = svc.run_report(record["id"], force=False)
svc.set_public(record["id"], True)
```

Permission semantics are identical across the in-memory, JSON-file and
Django ORM stores:

- content edits are owner-only (built-in templates superuser-only); everyone
  else uses "save as";
- creating/publishing needs `can_manage` or superuser;
- reads allow: owner / public / built-in template / domain manager (who may
  reference others' private reports);
- deletes: owner or superuser.

Implement the store protocol (`list_reports/get_config/save_fields/delete/
set_public`) to back MongoDB/Redis/etc.

---

### 9. Tutorial F — Django integration

#### 9.1 Install & routes

```python
# settings.py
INSTALLED_APPS = [..., "report_engine_kit.contrib.django"]

# urls.py
from django.urls import include, path
urlpatterns += [
    path("report-engine/tabular/<str:domain_code>/",
         include("report_engine_kit.contrib.django.tabular_urls")),
]
```

```bash
python manage.py migrate report_engine_kit   # app_label = report_engine_kit
```

The app wires report caching to Django's cache framework in `ready()`.
Register a domain in your own app's `ready()`:

```python
from report_engine_kit.tabular_pivot import register_domain
from report_engine_kit.contrib.django.orm_adapters import table_from_queryset
from myapp.models import Order

register_domain(
    "orders", "Orders",
    load_tables=lambda force=False: [table_from_queryset(
        Order.objects.all(), key="orders", label="Orders",
        fields=["code", "status", "created_at", "dept__name"],
        code_field="code", status_field="status")],
    can_access=lambda u: u.is_authenticated,
    can_manage=lambda u: u.is_staff,
)
```

Open `/report-engine/tabular/orders/` for the self-contained drag-and-drop
pivot builder.

#### 9.2 Generic endpoints

```
GET  dimensions/        field-options/      config/list/
GET  config/load/<id>/  config/execute/<id>/
POST execute/  export/  drill/  config/save/
POST config/delete/<id>/  config/visibility/<id>/
```

For fixed-statistic charts use
`report_engine_kit.contrib.django.http.ReportApiMixin`
(`list_sources/get_data/get_drilldown/export/list_fields`) and add your own
auth decorators.

#### 9.3 ORM pivot engine (SQL aggregation instead of in-memory counting)

When measures must aggregate directly in the database, implement a
`PivotContext` (dimension metadata, base queryset, scope filter,
cross-source joins, predicate Q builders, fast-path hooks, metric
versions …):

```python
from report_engine_kit.contrib.django.pivot import PivotContext, PivotReportEngine

class OrderPivotContext(PivotContext):
    app_label = "orders"
    config_model = MyReportConfig
    metric_model = None
    def list_dimensions(self, scope_code=""): ...
    def get_base_queryset(self, data_source): ...
    def apply_scope_filter(self, qs, ds, codes): ...

engine = PivotReportEngine(OrderPivotContext())
```

Features: single-SQL `Count(Q(...))` conditional aggregation with automatic
per-metric fallback, predicate AND/OR trees, HAVING, chart data, cell
formulas, `chunked_robust_count` (survives MySQL 2013 disconnects).

Cross-report snapshot refresher (daemon thread, cross-process cache lock,
`close_old_connections()` for MySQL wait_timeout):

```python
from report_engine_kit.contrib.django.pivot.snapshot import (
    register_pivot_domain, start_snapshot_scheduler)
register_pivot_domain("orders", OrderPivotContext, MyReportConfig)
start_snapshot_scheduler("22:30")
```

See [docs/django.md](docs/django.md) and [docs/api.md](docs/api.md).

---

### 10. Counting semantics (read first)

The most important business contracts — they determine whether the numbers
are correct:

1. **Dedup by code column**: rows sharing `code_col` are one entity; rows
   with a blank code never count.
2. **Same code = merged cell**: each column inherits the **first non-empty
   value** in row order (representative-row inheritance).
3. **Per-measure filters**: global filters (incl. global date) fix the
   common row set; each measure adds its own column filters and date
   window.
4. **Date filtering**: when a date range is active, rows with blank/unparsable
   dates are excluded; `parse_date_lenient` accepts `2026/2/9`,
   `2026.2.9`, `2026年2月9日`.
5. **Multi-table**: `__type__` groups by table label; a shared dimension
   name uses common columns across tables.
6. **Restricted columns**: columns hidden via `restricted_cols` enter
   neither dimensions nor measure values for that user; the cache key
   contains the exact hidden-column fingerprint.
7. **Totals**: the totals row is produced by merging accumulators (avg =
   total sum / total numeric count, not an average of averages; distinct =
   set union), so sorting/Top N can never corrupt it.

---

### 11. Caching & configuration

```python
from report_engine_kit.cache import (
    MemoryCacheBackend, ReportCacheManager, configure_cache)
from report_engine_kit import configure

# switch to the Django cache (the [django] extra does this automatically in AppConfig.ready())
configure_cache(DjangoCacheBackend(alias="default"))

# global settings
configure(DEFAULT_CACHE_TTL=300,
          LEGACY_SOURCE_CACHE_TTL=120,
          MEMORY_CACHE_MAX_ENTRIES=2048,
          DRILL_PAGE_SIZE_DEFAULT=50,
          DRILL_PAGE_SIZE_MAX=200)
```

- The default `MemoryCacheBackend` is thread-safe with TTL, bounded
  eviction, atomic `add` and glob deletion; env overrides
  `REPORT_ENGINE_DEFAULT_TTL` / `REPORT_ENGINE_MEMORY_CACHE_MAX_ENTRIES`.
- Pivot results cache for 30 s, representative-row L1 for 1 h (max 64 keys);
  **cache key = config + permission fingerprint + app id** — views never
  leak across users.
- A custom backend only needs `get/set/delete` (optional
  `add/delete_pattern`).

---

### 12. Security model

- Domain access/manage/restricted-column hooks are enforced **server-side**;
  client input is untrusted.
- All pivot input is whitelist-sanitized with size caps (≤4 row dimensions,
  ≤30 metrics, ≤200 filter values, ≤5000 custom cells, formula length
  ≤200, cross-report chain ≤20 …).
- Cross-report refs `[id]` recompute as the **viewer**; only owner/public/
  domain-manager-visible reports are reachable.
- CSV/XLSX exports neutralize cells starting with `= + - @`; download
  filenames are RFC 5987 encoded.
- Formulas use a purpose-built tokenizer/parser (AST whitelist), never
  `eval`; manual overrides accept numbers only.

---

### 13. Capability map vs an Excel pivot table

| Capability | report-engine-kit |
|---|---|
| Row fields (multi-level) | ✅ up to 4 levels, auto-merge equal values |
| Column fields | ✅ 1-level cross-tab, measures fan out per value, optional per-measure total |
| Value aggregation | ✅ count/sum/avg/min/max/distinct count |
| Value/report filters | ✅ per-measure + global, AND/OR, keyword, 13 operators |
| Date grouping | ✅ day/week/month/year + 7 dynamic date rules (incl. wheel) |
| Subtotals | ✅ auto multi-level subtotals, exact merge for every aggregation |
| Grand totals | ✅ row totals + column totals (toggleable) |
| Sort / Top N | ✅ by any measure, keep first N |
| Percent / running total | ✅ pct_of_total, running_total |
| Number formats | ✅ decimals/thousands/percent/prefix/suffix/multiplier, real xlsx formats |
| Calculated fields/formulas | ✅ 14 functions + comparisons + ranges + cross-report refs + cycle guard |
| Manual overrides | ✅ cell override, red font, totals recomputed |
| Custom rows/columns | ✅ incl. formula columns and templates |
| Insert blank rows/columns | ✅ server-side, unified coordinate system |
| Move rows/columns, hide rows | ✅ server-side (engine + xlsx + CSV consistent) |
| Row heights/column widths | ✅ per kind/element, correct after moves |
| Manual cell merges | ✅ owner-matrix validation, stale/illegal items dropped |
| Multi-level headers/banners | ✅ layout engine as single source of truth |
| Drilldown | ✅ domain hook, code set → detail links |
| Export | ✅ xlsx / csv / pdf / json / html / md |
| DB-free deployment | ✅ CLI + atomic JSON file store |
| Web integration | ✅ optional Django app + ORM aggregation engine |
| Charts | ✅ data-source registry + declarative analytics (no-code charts) |

---

### 14. Project layout

```
report-engine-kit/
├── pyproject.toml / MANIFEST.in / LICENSE / CHANGELOG.md
├── README.md
├── docs/                  # api.md / django.md / release.md
├── examples/              # 4 runnable tutorial scripts + sample data
├── scripts/release.ps1    # Windows release script
├── .github/workflows/publish.yml
├── tests/                 # 110 unit tests
└── src/report_engine_kit/
    ├── chart core: contracts/base/registry/cache/export/snapshot/analytics/date_utils/cli
    ├── tabular_pivot/     # in-memory pivot engine (stores/loaders/adapters/numbers)
    └── contrib/
        ├── pandas_support.py   # [pandas]
        └── django/             # [django]: app/model/migrations/HTTP/ORM pivot/assets
```

---

### 15. Development, testing & release

```bash
git clone <repo> && cd report-engine-kit
python -m venv .venv
.\.venv\Scripts\Activate.ps1     # Windows
source .venv/bin/activate         # macOS/Linux

pip install -e ".[dev,all]"
pytest                                   # 110 tests
python examples/02_tabular_pivot.py
python -m build
python -m twine check dist/*
```

Release (see [docs/release.md](docs/release.md)):

```powershell
$env:TWINE_USERNAME = "__token__"
$env:TWINE_PASSWORD = "<token>"
.\scripts\release.ps1 -Version 0.3.0 -Target testpypi
.\scripts\release.ps1 -Version 0.3.0 -Target pypi
```

Tags `v*rc*` publish to TestPyPI and `v*` to PyPI via GitHub Actions
(OIDC trusted publishing preconfigured).

---

### 16. FAQ

**My count doesn't match Excel COUNTIF?** Check: ① the code column (blank
codes excluded, same code → one representative row); ② whether the measure's
own `col_filters/date_rule` filtered further; ③ restricted columns. See
[section 10](#10-counting-semantics-read-first).

**What does sum/avg do with text cells?** Non-numeric cells are ignored
(while still counted by `count`); avg's denominator counts numeric cells
only. Thousands separators/currency symbols parse leniently.

**The UI shows hidden rows/sorting but Excel export doesn't?** Since 0.3.0
`row_order/col_order/hidden_rows` apply server-side; make sure you're on
0.3.0+ and the values are saved under `layout_overrides`.

**`=[12]C3` returns `#REF!`?** Report 12 doesn't exist, the viewer lacks
access (not owner/public/domain-manager), or C3 isn't numeric in the target
report. Cycles return `#CYCLE!`.

**Can I use it without Django/openpyxl?** Yes — the zero-dependency core
covers csv/json/html/md, all pivot math, the CLI and JSON file storage.

**Garbled Chinese in PDF?** Set `REPORT_ENGINE_FONT_PATH` to a font file; on
Linux install fonts-wqy-zenhei or noto-cjk.

**Very large data?** The in-memory engine suits ledger-sized data (tens of
thousands of representative rows). Aggregate in SQL inside `load_tables`, or
use the ORM `PivotReportEngine` in the `[django]` extra.

**Flask/FastAPI?** Instantiate `PivotService` and pass the request JSON as
config; permission hooks accept your own user object (duck-typed `.id` /
`.is_superuser`).

<a id="license-en"></a>
### License

[MIT](LICENSE) © bzsystem team

---

<a id="中文文档"></a>
## 中文文档

> 回到语言切换：[English](#english) | 中文文档

**report-engine-kit（报表引擎工具包）** 是一个**框架无关的 Python 报表与数据透视引擎**，并提供可选的 Django 集成层。它从一个生产级图纸/变更管理系统中抽取，包含三大报表能力：

1. **固定统计图数据源**——注册 Python 函数或类即可产出标准 `ReportData`，输出 Chart.js 兼容 JSON，可导出 `xlsx / csv / pdf / json / html / md`。
2. **内存表透视引擎（类 Excel 数据透视表）**——喂给引擎"表头 + 字符串行"（CSV、JSON、dict 列表、pandas DataFrame、ORM QuerySet 都行），用户通过一份 JSON 配置即可得到完整的透视表：多表对比、列维度交叉、求和/平均/最大/最小/去重计数、多级小计、按指标排序/Top N、占比/累计等衍生指标、多级表头、自定义行列、Excel 风格公式、跨报表引用、手动改数、插空行空列、拖拽行列、行高列宽……
3. **可选 Django App**（`[django]` extra）——通用 HTTP 视图、已存报表模型与迁移、ORM 版拖拽透视引擎（单条 SQL 条件聚合）、跨报表快照定时刷新、前端页面与 JS 资源。

> **核心零第三方依赖**：openpyxl、reportlab、pandas、Django、PyYAML 全部是按需安装的 extras，纯核心只用 Python 标准库。

### 中文目录

- [一、这是什么 / 不是什么](#一这是什么--不是什么-zh)
- [二、安装](#二安装-zh)
- [三、五分钟快速开始](#三五分钟快速开始-zh)
- [四、教程 A：固定统计图数据源](#四教程-a固定统计图数据源-zh)
- [五、教程 B：内存表透视（核心教程，含完整 JSON 配置）](#五教程-b内存表透视核心教程含完整-json-配置-zh)
- [六、教程 C：声明式图表分析（用户零代码自定义图表）](#六教程-c声明式图表分析用户零代码自定义图表-zh)
- [七、教程 D：命令行 CLI](#七教程-d命令行-cli-zh)
- [八、教程 E：不用数据库保存报表（JSON 文件存储）](#八教程-e不用数据库保存报表json-文件存储-zh)
- [九、教程 F：Django 集成](#九教程-fdjango-集成-zh)
- [十、数据口径（计数规则，务必先读）](#十数据口径计数规则务必先读-zh)
- [十一、缓存机制与配置](#十一缓存机制与配置-zh)
- [十二、安全模型](#十二安全模型-zh)
- [十三、能力总览（与 Excel 数据透视表对照）](#十三能力总览与-excel-数据透视表对照-zh)
- [十四、项目结构](#十四项目结构-zh)
- [十五、开发、测试与发布](#十五开发测试与发布-zh)
- [十六、常见问题 FAQ](#十六常见问题-faq-zh)
- [许可证](#许可证-zh)

---

<a id="一这是什么--不是什么-zh"></a>
### 一、这是什么 / 不是什么

**适合的场景**

- 你的数据本质是"台账表"（表头 + 若干行，单元格多为文本/日期/金额），用户希望在前端像 Excel 透视表一样拖拽出各种统计报表；
- 你有一堆已经写好的统计函数，想统一注册、统一缓存、统一导出、统一走 Chart.js；
- 你希望报表配置（筛选、维度、指标、样式、公式）完全由 JSON 驱动，可以落库、可以跨进程按查看者权限重算；
- 你在 Django / Flask / FastAPI / 纯脚本 / pandas 环境中使用，但不想被某一个框架绑定。

**不适合的场景**

- 亿级数据的 OLAP 分析（内存表引擎是为台账量级设计的，默认在内存里遍历代表行；超大数据请用 `[django]` extra 里的 ORM 条件聚合引擎，或自己在数据源层做 SQL 聚合）；
- 需要完整 BI 平台（仪表盘编排、行列权限矩阵、数据源管理 UI）——本包提供的是**引擎和通用设计器页面**，业务菜单/导航仍由宿主系统负责。

---

<a id="二安装-zh"></a>
### 二、安装

```bash
pip install report-engine-kit                  # 仅核心：csv/json/html/md + 全部透视计算
pip install "report-engine-kit[xlsx,pdf]"      # Excel/PDF 导出
pip install "report-engine-kit[pandas]"        # DataFrame 适配器
pip install "report-engine-kit[yaml]"          # CLI 读取 YAML 配置
pip install "report-engine-kit[django]"        # Django App + ORM 透视引擎
pip install "report-engine-kit[all]"           # 一次性装全
pip install "report-engine-kit[dev]"           # 开发/测试
```

支持 Python 3.9、3.10、3.11、3.12、3.13。验证安装：

```bash
report-engine --help
python -c "import report_engine_kit as r; print(r.__version__)"
```

安装包的命名约定（避免歧义）：

| 名称 | 形式 | 示例 |
|---|---|---|
| **分发包名**（pip install） | 连字符 | `report-engine-kit` |
| **Python 导入名** | 下划线 | `import report_engine_kit` |
| **CLI 命令名** | 连字符 | `report-engine domains ...` |

---

<a id="三五分钟快速开始-zh"></a>
### 三、五分钟快速开始

**场景**：你有一个订单 CSV，想用 20 行代码生成"按区域统计订单数和金额"的透视表并导出 Excel。

```python
from report_engine_kit.tabular_pivot import (
    register_domain, PivotService, SimpleUser, table_from_csv)

# 1) 把 CSV 注册成一个"业务域"（domain）。code=0 的列是订单编号（去重列）。
register_domain(
    "orders", "订单",
    load_tables=lambda force=False: [
        table_from_csv("orders.csv", key="orders", label="订单", code_col=0),
    ],
    can_access=lambda user: True,
    can_manage=lambda user: True,
)

# 2) 用服务门面执行一份配置
svc = PivotService("orders", user=SimpleUser(id=1))
config = {
    "sheets": ["orders"],
    "row_dimensions": ["区域"],
    "metrics": [
        {"key": "cnt", "label": "订单数"},                       # 默认 count
        {"key": "rev", "label": "金额", "agg": "sum",
         "value_field": "金额"},                                 # 求和
    ],
    "global_filters": {},
}
result = svc.execute(config)
for row in result["rows"]:
    print(row["kind"], row["dim_values"], row["values"])

# 3) 导出（xlsx / csv / json；需要 pip install "report-engine-kit[xlsx]"）
open("订单统计.xlsx", "wb").write(svc.export_xlsx(config).getvalue())
open("订单统计.csv", "wb").write(svc.export_csv(config))
```

> 示例 CSV 的真实表头是中文，且"状态"列被识别为语义状态列；完整可运行版本见
> [`examples/02_tabular_pivot.py`](examples/02_tabular_pivot.py)，配置字段逐项解释见
> [教程 B](#五教程-b内存表透视核心教程含完整-json-配置-zh)。

返回结果结构（节选）：

```json
{
  "domain": "orders",
  "dim_labels": ["区域"],
  "metric_columns": [{"key": "cnt", "label": "订单数"}, "..."],
  "rows": [
    {"kind": "data", "dim_values": ["华东"], "values": {"cnt": 12, "rev": 3500}},
    {"kind": "totals", "dim_values": ["合计"], "values": {"cnt": 20, "rev": 9800}}
  ],
  "totals": {"cnt": 20, "rev": 9800},
  "layout": ["..."]
}
```

可直接运行的例子在 [`examples/`](examples/) 目录：

```bash
python examples/01_data_source.py            # 固定统计图
python examples/02_tabular_pivot.py          # CSV → 透视 → xlsx
python examples/03_saved_reports_file_store.py  # 免数据库保存
python examples/04_analytics_chart.py        # 声明式图表分析
```

---

<a id="四教程-a固定统计图数据源-zh"></a>
### 四、教程 A：固定统计图数据源

适用于"指标固定、参数化查询"的图表（如任务状态分布、月度趋势、KPI 卡片）。

#### 4.1 数据契约

| 类 | 字段 | 说明 |
|---|---|---|
| `ReportParams` | `start_date / end_date` | `datetime.date`，闭区间 |
| | `profession_filter` | 业务域过滤字符串（含义由你的 app 定义） |
| | `time_dimension` | `day/week/month/quarter/year` |
| | `field_key` | 字段统计用的列键 |
| | `extra` | 任意 JSON 可序列化扩展参数 |
| `ReportData` | `chart_type` | `bar/line/pie/doughnut/polarArea/radar/table` |
| | `title / labels / summary` | 标题、X 轴标签、摘要 dict |
| | `datasets` | `[Dataset(label, data, backgroundColor?, borderColor?, fill?, extra?)]` |

`to_dict()` 输出的就是 Chart.js 能直接吃的 JSON；`from_dict()` 可反向解析。

#### 4.2 方式一：子类化（推荐，支持钻取/字段列表/缓存）

```python
from datetime import date
from report_engine_kit import (
    ReportDataSource, ReportData, Dataset, ReportParams,
    DataSourceRegistry, export_report_data)

@DataSourceRegistry.register
class OrdersSource(ReportDataSource):
    name = "orders_by_status"      # 必填，同一 app_label 内唯一
    label = "订单状态分布"
    chart_type = "bar"
    cache_ttl = 60                 # 秒；0 = 关闭引擎层缓存
    app_label = "shop"             # 业务隔离；"" 表示所有 app 可见

    def fetch(self, params: ReportParams) -> ReportData:
        # 这里替换成你的真实查询（SQL/ORM/HTTP 都行）
        rows = my_db.query_orders(params.start_date, params.end_date)
        return ReportData(
            title=f"订单状态（{params.start_date} ~ {params.end_date}）",
            labels=[r["status"] for r in rows],
            datasets=[Dataset(label="订单数", data=[r["n"] for r in rows])],
            summary={"total": sum(r["n"] for r in rows)},
        )

    # 可选：单元格下钻
    def drilldown(self, params, group_key: str) -> "DrilldownData":
        from report_engine_kit import DrilldownData
        items = my_db.query_detail(group_key)
        return DrilldownData(
            headers=["订单号", "金额"],
            rows=[[x["code"], x["amount"]] for x in items],
            total=len(items))

    # 可选：动态字段元信息（前端字段下拉用）
    def list_fields(self, params):
        from report_engine_kit import FieldMeta
        return [FieldMeta("status", "状态"), FieldMeta("dept", "部门")]

# 调用（带 app_label 做隔离校验）
data = DataSourceRegistry.fetch(
    "orders_by_status",
    ReportParams(start_date=date(2026, 1, 1), end_date=date(2026, 9, 30)),
    app_label="shop")

open("orders.xlsx", "wb").write(export_report_data(data, "xlsx"))
```

#### 4.3 方式二：注册已有函数（零改造接入）

引擎会通过函数签名自动识别它需要哪些参数，日期按旧约定传 ISO 字符串：

```python
from report_engine_kit import register_legacy_function

def get_sales(start_date=None, end_date=None, profession_filter=None):
    # 你已有的函数，完全不用改
    return {"chart_type": "line", "title": "销售趋势",
            "labels": ["6月", "7月", "8月"],
            "datasets": [{"label": "收入", "data": [10, 20, 30]}]}

register_legacy_function(
    "sales", "销售趋势", get_sales,
    chart_type="line",
    supports_drilldown=False,
    drilldown_fn=None,          # 也可注入 (params, group_key) -> DrilldownData
    app_label="shop",
    cache_ttl=None)             # None = 用全局默认 LEGACY_SOURCE_CACHE_TTL
```

#### 4.4 六种导出格式

```python
from report_engine_kit import export_report_data
for fmt in ("csv", "json", "md", "html", "xlsx", "pdf"):
    try:
        with open(f"out.{fmt}", "wb") as f:
            f.write(export_report_data(data, fmt))
    except Exception as e:
        print(fmt, "需要安装对应 extra：", e)
```

| 格式 | extra | 特点 |
|---|---|---|
| `csv` | 无 | utf-8-sig BOM（Excel 直接打开不乱码）；`= + - @` 开头自动加 Tab 防公式注入 |
| `json` | 无 | 完整 payload；传了 params 时附 `params` 字段 |
| `md` | 无 | Markdown 管道表格，`\|`/换行自动转义 |
| `html` | 无 | 独立样式 HTML，默认内嵌 Chart.js（CDN）；`chart_cdn_url=""` 可完全离线 |
| `xlsx` | `xlsx`（openpyxl） | 样式表头、CJK 感知自动列宽、冻结表头、非标量紧凑 JSON 化、公式注入防护 |
| `pdf` | `pdf`（reportlab） | A4 表格；自动探测中文字体（Windows simhei/msyh/simsun、Linux wqy/noto、`REPORT_ENGINE_FONT_PATH`） |

自定义导出器：

```python
from report_engine_kit.export import BaseExporter, ExporterRegistry

@ExporterRegistry.register
class ParquetExporter(BaseExporter):
    format_name = "parquet"
    content_type = "application/vnd.apache.parquet"
    file_extension = "parquet"
    def export(self, data, params=None) -> bytes: ...
```

---

<a id="五教程-b内存表透视核心教程含完整-json-配置-zh"></a>
### 五、教程 B：内存表透视（核心教程，含完整 JSON 配置）

#### 5.1 第一步：理解数据契约

```python
DataTable(
    key="cr",                 # 域内唯一表标识
    label="CR",               # 显示名/列维度取值
    headers=["编号", "状态", "日期", "金额"],
    rows=[TableRow(code="CR-001", cells={"0": "CR-001", "1": "关闭",
                                         "2": "2026-02-10", "3": "200"})],
    code_col=0,               # 去重编码列
    status_col=1,             # __status__ 语义维度映射到此列
    date_cols=[2],            # 日期列（表头名含日期/时间也会自动识别）
    version="20260930_v1",    # 版本变化会驱动代表行 L1 缓存换 key
)
```

**数据加载器**：`load_tables(force) -> list[DataTable]`，`force=True` 表示穿透业务侧缓存重读。

#### 5.2 第二步：注册业务域（权限钩子全部在这里）

```python
register_domain(
    code="change", label="变更管理",
    load_tables=load_change_tables,
    can_access=lambda u: u.is_authenticated,            # 读权限
    can_manage=lambda u: u.is_staff,                    # 新建/另存/公开权限
    restricted_cols=lambda u, t: {3} if not u.is_staff else set(),  # 列级隐藏
    blocked_header=lambda name, t: name == "内部备注",  # 额外禁止入筛/入维度的列
    default_tables=["cr", "fcr"],
    drill_resolver=my_drill_builder,                    # 数值格下钻
)
```

钩子说明：

| 钩子 | 签名 | 用途 |
|---|---|---|
| `can_access(user)` | → bool | 服务层与 HTTP 层都校验 |
| `can_manage(user)` | → bool | 决定能否创建/编辑/公开；缺省同 can_access |
| `restricted_cols(user, table)` | → set[int] | 该用户看不到的列（**结果缓存键含权限指纹**，不会串视角） |
| `blocked_header(name, table)` | → bool | 额外屏蔽列；引擎默认还会屏蔽表头含"分发/回收/接收/归还"的流转列 |
| `drill_resolver(config, row_key, metric_key, restricted)` | → list[dict] | 右键下钻明细，返回 `[{code,label,count,url}]` |

#### 5.3 第三步：构造配置并执行

```python
config = {
    # ── 参与统计的表（多表时 __type__ 维度可用）
    "sheets": ["cr", "fcr"],

    # ── 行维度（最多 4 级）；__type__=表类型，__status__=语义状态列，其余写表头名
    "row_dimensions": ["__type__", "__status__"],
    # 维度也可以带值过滤：{"key": "专业", "values": ["EM4", "EM5"]}

    # ── 列维度（交叉透视，最多 1 级）：每个指标按该维度取值扇出多列
    "column_dimension": "部门",

    # ── 指标列（最多 30 个）
    "metrics": [
        {
            "key": "total", "label": "总数", "group": "一期",   # group 相同→多级表头分组
            "agg": "count",                                    # count/sum/avg/min/max/distinct
            "col_filters": [                                   # 本指标独立筛选
                {"name": "关闭日期", "values": [], "mode": "include",
                 "keyword": "",
                 "date_rule": {"col": "关闭日期", "mode": "range",
                               "start": "2026-01-01", "end": "2026-03-31"}}],
            "filter_logic": "and",                             # and / or
            "date_rule": {"col": "导入日期", "mode": "year"},  # 指标自己的日期窗口
            "number_format": {"decimals": 0, "thousands": True}
        },
        {"key": "rev", "label": "金额", "agg": "sum",
         "value_field": "金额",
         "number_format": {"decimals": 2, "thousands": True, "prefix": "￥"}},
        {"key": "share", "label": "占比", "calc": "pct_of_total",
         "calc_source": "rev",
         "number_format": {"percent": True, "decimals": 1}},
        {"key": "cum", "label": "累计", "calc": "running_total",
         "calc_source": "rev"}
    ],

    # ── 全局筛选（所有指标共同行集合；各指标再叠加自己的筛选）
    "global_filters": {
        "date_col": "", "start": "", "end": "",                 # 旧版字段
        "date_rule": {"col": "导入日期", "mode": "month"},      # 新版日期规则（优先）
        "col_filters": [{"name": "专业", "values": ["EM4"], "mode": "exclude"}],
        "filter_logic": "and"
    },

    # ── 行/列控制
    "show_totals": True,            # 合计行
    "show_column_totals": True,     # 列维度下每个指标的"合计"列
    "hide_empty_rows": False,       # 全 0 行隐藏
    "subtotals": True,              # 多级行维度时插入分类小计
    "sort": {"by": "rev", "dir": "desc"},   # 按指标排序
    "row_limit": 10,                # Top N（只保留前 N 个数据行，合计不受影响）

    # ── 手动改值（缓存外应用，自动重算合计）
    "manual_overrides": {'["关闭"]': {"total": 99}},

    # ── 自定义行 / 自定义列（公式见 5.6）
    "custom_rows": [{"id": "r1", "label": "月度目标",
                     "values": {"total": 100}, "formulas": {}}],
    "custom_columns": [
        {"id": "cc1", "label": "翻倍", "template": "=B1*2",
         "cells": {"1": {"t": "n", "v": 3}}}
    ],

    # ── 布局编辑（详见 5.7）
    "spacer_rows": [{"id": "sr1", "after": -1, "height": 30}],
    "spacer_cols": [{"id": "sc1", "after": "dim:__status__", "width": 24}],
    "row_heights": {"data": 26, "totals": 32},
    "col_widths": {"metric:rev": 160},
    "layout_overrides": {"row_order": [], "col_order": [], "hidden_rows": []},

    # ── 文字横幅行/侧边行头、手选合并、备注
    "custom_texts": [], "custom_merges": [],
    "footer_text": "数据来源：变更台账"
}

result = svc.execute(config)
```

#### 5.4 值聚合方式（Excel "值字段设置"）

| agg | 含义 | 是否需要 value_field | 说明 |
|---|---|---|---|
| `count` | 计数 | 否 | 代表行数量（空编码行不计） |
| `sum` | 求和 | 是 | 非数字格忽略 |
| `avg` | 平均 | 是 | 只对数字格求平均（非数字不计入分母，同 Excel） |
| `min` / `max` | 最小/最大 | 是 | |
| `distinct` | 去重计数 | 是 | 数值去重后的个数 |

数值解析宽松：`"1,234.5"`、`"￥200"`、`"约 300 元"` 都能取出数字。

衍生计算：

| calc | 含义 | 要求 |
|---|---|---|
| `pct_of_total` | 占同列合计比例（存 0–1 小数） | `calc_source` 指定基础指标 |
| `running_total` | 按当前行顺序累计 | `calc_source` 指定基础指标 |

数字格式 `number_format`：

```json
{"decimals": 2, "thousands": true, "percent": true,
 "prefix": "￥", "suffix": "元", "multiplier": 1}
```

xlsx 导出会落成真实的 Excel 单元格格式；CSV/HTML 按格式渲染文本。

#### 5.5 日期规则（date_rule）的 7 种模式

| mode | 含义 | 额外字段 |
|---|---|---|
| `today` | 当天 | — |
| `month` | 本月 1 号 ~ 今天 | — |
| `year` | 本年 1 月 1 号 ~ 今天 | — |
| `recent` | 最近 N 天 | `days`（0–3650） |
| `range` | 固定区间 | `start`, `end`（YYYY-MM-DD） |
| `offset` | 相对偏移（超期未关闭类） | `base`(today/fixed)、`baseDate`、`sign`(plus/minus)、`unit`(day/month/year)、`amount`、`cmp`(gt/lt) |
| `wheel` | 滚轮窗口（季度至今等） | `s`/`e` 各为 `{y,m,d,mode,cur}` 部件，跟随当前年/月/日 |

`wheel` 示例（本季度第一天到今天，起止自动归一防止"起>止"空表）：

```json
{"col": "关闭日期", "mode": "wheel",
 "s": {"y": {"mode": "cur"}, "m": {"mode": "fix", "v": 10}, "d": {"mode": "fix", "v": 1}},
 "e": {"y": {"mode": "cur"}, "m": {"mode": "cur"}, "d": {"mode": "cur"}}}
```

筛选条件（`col_filters`）支持：值多选 include/exclude、关键词包含/不包含、组内 AND/OR；空白值哨兵为 `(空白)` / `(非空)`。

#### 5.6 公式 DSL（类 Excel，自研解析器，非 eval）

| 类别 | 内容 |
|---|---|
| 运算 | `+ - * /`、括号、一元正负号、比较 `= == <> != < <= > >=`（返回 1/0） |
| 单元格 | `A1`、区间 `B1:B10`（物理坐标，1 基） |
| 跨报表 | `[12]C3` 单格、`[12]A1:A10` 区间 |
| 函数 | `SUM / AVG / MIN / MAX / COUNT / ROUND / ABS / SQRT / POWER / MOD / IF / AND / OR / NOT` |
| 自定义列 | `template` 整列相对填充（锚第 0 行自动平移）；`cells` 逐格 `{t:"n",v}` 手填或 `{t:"f",v:"=..."}` 公式 |
| 错误码 | `#VALUE! #REF! #DIV/0! #CYCLE! #NAME? #NUM!` |
| 防护 | 公式长度 ≤200、嵌套深度 ≤100、跨报表链 ≤20 层、循环引用检测 |

```text
=SUM(B1:B5)+ROUND(AVG(C1:C5)*0.1, 2)
=IF(B1>=100, C1*0.9, C1)
=[12]C3 + [12]D3
```

#### 5.7 布局编辑（0.3.0 新增，服务端真实生效）

```json
{
  "col_widths": {"dim:区域": 200, "metric:rev": 160,
                 "ccol:cc1": 120, "spacer:sc1": 24},
  "row_heights": {"title": 36, "header": 28, "data": 24,
                  "subtotal": 26, "totals": 30, "spacer": 12},
  "spacer_rows": [{"id": "sr1", "after": -1, "height": 30},
                  {"id": "sr2", "after": 4, "height": 16}],
  "spacer_cols": [{"id": "sc1", "after": "dim:区域", "width": 24},
                  {"id": "sc2", "after": "metric:rev"}],
  "layout_overrides": {
    "row_order": ["[\"West\"]", "[\"East\"]"],
    "col_order": ["metric:rev", "metric:cnt", "ccol:cc1"],
    "hidden_rows": ["[\"作废]\"]"]
  }
}
```

- **列宽**：token → 像素（8–800），移动列/列维度扇出/插空列后仍准确。
- **行高**：按行类别或按具体元素 id（`spacer:<id>`、`customrow:<id>`），xlsx 按 ×0.75 转磅。
- **空白行**：`after` 锚数据行序号（-1 顶部）；会像 Excel 插行一样打断维度合并、平移公式/合并坐标；扁平 CSV 自动剔除。
- **空白列**：锚点 `__first__ / head / dim:<key> / metric:<key> / ccol:<id>`，无边框间隙。
- **移动行**：`row_order` 为 JSON 行键数组，列出的置顶排序、未列的自然顺序追加；`hidden_rows` 删除并重算小计合计。
- **移动列**：指标块/自定义列可移动（含列维度扇出时整块移动），行头/维度列固定在前。

所有移动/插入都由统一的**列规划** `result.column_plan`
（`{blocks, width, metric_phys, ccol_phys, spacer_phys}`）驱动，公式坐标、合并校验、表头、xlsx 共用，不会发生坐标漂移。

#### 5.8 导出

```python
buf = svc.export_xlsx(config, report_name="季度报表")   # BytesIO，带全部样式/格式/小计/空白行列
open("r.xlsx", "wb").write(buf.getvalue())

csv_bytes = svc.export_csv(config, formatted=True)      # 扁平 CSV（含 subtotal/total 行标记）
json_bytes = svc.export_json_bytes(config)              # 原始结果 JSON
```

CSV 首列为行类型标记：`data / subtotal / total / custom`，方便二次分析。

---

<a id="六教程-c声明式图表分析用户零代码自定义图表-zh"></a>
### 六、教程 C：声明式图表分析（用户零代码自定义图表）

不想为每一种统计图写 `fetch()`？把 list[dict] 行交给 `aggregate_rows`，用一份声明式 spec 描述维度和指标即可：

```python
from report_engine_kit import Dimension, Measure, aggregate_rows

data = aggregate_rows(
    rows,                                   # 任意 list[dict]
    dimensions=[
        Dimension("month", "月份", date_trunc="month"),
        Dimension("status", "状态"),        # 第二个维度自动成为系列（多 dataset）
    ],
    measures=[
        Measure("orders", "订单数"),                                # count
        Measure("revenue", "收入", agg="sum", field_name="amount"),
        Measure("aov", "客单价", agg="avg", field_name="amount"),
        Measure("buyers", "客户数", agg="distinct_count",
                field_name="customer_id"),
    ],
    filters=[
        {"key": "status", "op": "in", "values": ["paid"]},
        {"key": "amount", "op": "gte", "values": ["0"]},
        {"key": "dept", "op": "contains", "value": "EM"},
    ],
    series_dimension=None,    # 缺省取第二个维度
    sort="-orders::paid",     # label / -label / 指标键 / -指标键
    top_n=20,
    chart_type="bar",
)
```

- 过滤算子：`in / not_in / contains / not_contains / eq / ne / gt / gte / lt / lte / between / blank / nonblank`
- 日期分桶：`day / week / month / year`；空值归入 `(blank)` 且始终排序最后
- 同样的 spec 可以**通过 HTTP 驱动数据源**，终端用户不改服务端代码即可新增图表：

```python
from report_engine_kit import analytics_source_factory, ReportParams, DataSourceRegistry

analytics_source_factory(
    "shop_dyn", "店铺动态报表",
    rows_loader=lambda params: my_db.fetch_all(),   # 返回 list[dict]
    app_label="shop")

# HTTP 侧把 spec 放进 extra
params = ReportParams(extra={
    "dimensions": [{"key": "dept"}],
    "measures": [{"key": "n", "label": "数量"}],
    "filters": [{"key": "status", "op": "in", "values": ["paid"]}],
})
data = DataSourceRegistry.fetch("shop_dyn", params, app_label="shop")
```

完整可运行示例：[`examples/04_analytics_chart.py`](examples/04_analytics_chart.py)。

---

<a id="七教程-d命令行-cli-zh"></a>
### 七、教程 D：命令行 CLI

任何 import 时调用 `register_domain(...)` 的 Python 模块都可以作为"域模块"。参考 [`examples/shop_domain.py`](examples/shop_domain.py)。

```bash
# 列出已注册业务域
report-engine --domain-module shop_domain domains

# 查询可用行维度 / 日期列
report-engine --domain-module shop_domain dimensions --domain shop_orders

# 字段取值（带计数、分页、关键词）
report-engine --domain-module shop_domain field-options --domain shop_orders \
    --field Status --limit 20

# 临时执行一份配置（JSON；装了 [yaml] extra 也支持 YAML）
report-engine --domain-module shop_domain execute --domain shop_orders \
    --config pivot.json -o result.json

# 导出（xlsx / csv / json）
report-engine --domain-module shop_domain export --domain shop_orders \
    --config pivot.json --format xlsx --out r.xlsx --name 季度报表

# 免数据库：用 JSON 文件保存/列表/执行/删除/公开报表
report-engine --domain-module shop_domain reports save \
    --domain shop_orders --store file:reports.json --config pivot.json
report-engine --domain-module shop_domain reports list \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports run 1 \
    --domain shop_orders --store file:reports.json --format xlsx --out r1.xlsx
report-engine --domain-module shop_domain reports public 1 \
    --domain shop_orders --store file:reports.json
report-engine --domain-module shop_domain reports delete 1 \
    --domain shop_orders --store file:reports.json
```

全局选项：`--user-id`（默认取环境变量 `REPORT_ENGINE_USER_ID`）、`--admin`、`--store memory|file:路径`。

---

<a id="八教程-e不用数据库保存报表json-文件存储-zh"></a>
### 八、教程 E：不用数据库保存报表（JSON 文件存储）

```python
from report_engine_kit.tabular_pivot import (
    PivotService, JsonFileReportStore, SimpleUser, register_domain)

register_domain("orders", "订单",
                load_tables=..., can_access=lambda u: True,
                can_manage=lambda u: True)

store = JsonFileReportStore("data/reports.json")   # 原子写（temp+os.replace）
svc = PivotService("orders", user=SimpleUser(id=1, is_superuser=True), store=store)

record = svc.save_report({"name": "9 月经营看板", **pivot_config})
print(svc.list_reports(scope="available"))
print(svc.list_reports(scope="public"))
result = svc.run_report(record["id"], force=False)
svc.set_public(record["id"], True)
```

权限语义（内存存储、JSON 文件存储、Django ORM 存储完全一致）：

- 内容编辑只允许**属主**（内置模板仅超管），其他人用"另存为"；
- 新建/公开需要 `can_manage` 或超管；
- 读取允许：本人 / 已公开 / 内置模板 / 域管理员（可引用他人私有表）；
- 删除：属主或超管。

存储协议可自行实现 `list_reports/get_config/save_fields/delete/set_public`，例如接 MongoDB/Redis。

---

<a id="九教程-fdjango-集成-zh"></a>
### 九、教程 F：Django 集成

#### 9.1 安装与路由

```python
# settings.py
INSTALLED_APPS = [..., "report_engine_kit.contrib.django"]

# urls.py
from django.urls import include, path
urlpatterns += [
    path("report-engine/tabular/<str:domain_code>/",
         include("report_engine_kit.contrib.django.tabular_urls")),
]
```

```bash
python manage.py migrate report_engine_kit   # app_label = report_engine_kit
```

App 在 `ready()` 中自动把报表缓存接到 Django 缓存框架。然后在你自己 app 的 `ready()` 注册 domain：

```python
from report_engine_kit.tabular_pivot import register_domain
from report_engine_kit.contrib.django.orm_adapters import table_from_queryset
from myapp.models import Order

register_domain(
    "orders", "订单",
    load_tables=lambda force=False: [table_from_queryset(
        Order.objects.all(), key="orders", label="订单",
        fields=["code", "status", "created_at", "dept__name"],
        code_field="code", status_field="status")],
    can_access=lambda u: u.is_authenticated,
    can_manage=lambda u: u.is_staff,
)
```

打开 `/report-engine/tabular/orders/` 即是自包含的拖拽透视设计器页面。

#### 9.2 通用端点

```
GET  dimensions/        field-options/      config/list/
GET  config/load/<id>/  config/execute/<id>/
POST execute/  export/  drill/  config/save/
POST config/delete/<id>/  config/visibility/<id>/
```

固定统计图用 `report_engine_kit.contrib.django.http.ReportApiMixin`
（`list_sources/get_data/get_drilldown/export/list_fields`），自行加登录装饰器。

#### 9.3 ORM 版透视引擎（SQL 聚合而非内存计数）

当指标需要直接在数据库里聚合时，实现一个 `PivotContext`（钩子：维度元数据、基础 QuerySet、业务域过滤、跨源关联、谓词 Q、谓词快路径、统计项版本……）：

```python
from report_engine_kit.contrib.django.pivot import PivotContext, PivotReportEngine

class OrderPivotContext(PivotContext):
    app_label = "orders"
    config_model = MyReportConfig
    metric_model = None
    def list_dimensions(self, scope_code=""): ...
    def get_base_queryset(self, data_source): ...
    def apply_scope_filter(self, qs, ds, codes): ...

engine = PivotReportEngine(OrderPivotContext())
```

特点：单条 SQL `Count(Q(...))` 条件聚合、失败自动降级逐指标分组、谓词指标 AND/OR 树、HAVING、图表数据、单元格公式、`chunked_robust_count`（抗 MySQL 2013 断连）。

跨报表快照刷新（守护线程、跨进程缓存锁、`close_old_connections()` 处理 MySQL wait_timeout）：

```python
from report_engine_kit.contrib.django.pivot.snapshot import (
    register_pivot_domain, start_snapshot_scheduler)
register_pivot_domain("orders", OrderPivotContext, MyReportConfig)
start_snapshot_scheduler("22:30")
```

完整说明见 [docs/django.md](docs/django.md)，完整 API 参考见 [docs/api.md](docs/api.md)。

---

<a id="十数据口径计数规则务必先读-zh"></a>
### 十、数据口径（计数规则，务必先读）

这是透视引擎最重要的业务约定，直接决定数字对不对：

1. **按编码列去重**：`code_col` 相同的多行视为同一实体；编码为空的行**不参与统计**。
2. **同编码多行 = 合并单元格**：每一列按行顺序取**第一个非空值**（代表行继承）。例如同一变更号两行，状态分别为空/"关闭"，最终状态 = "关闭"。
3. **每指标独立筛选**：全局筛选（含全局日期）确定共同行集合，各指标再叠加自己的列筛选和日期窗口。
4. **日期筛选**：启用日期区间时，日期空/不可解析的行不计入；`parse_date_lenient` 支持 `2026/2/9`、`2026.2.9`、`2026年2月9日` 等宽松格式。
5. **多表对比**：`__type__` 维度按表 label 分组；同一维度名取各表共有列。
6. **受限列**：被 `restricted_cols` 隐藏的列对该用户既不进维度也不进指标取值；缓存键含精确的隐藏列指纹。
7. **合计口径**：合计行由累积器合并而来（avg 是总 sum/总数字数，不是平均的平均；distinct 是值集并集），不会因为排序/Top N 而算错。

---

<a id="十一缓存机制与配置-zh"></a>
### 十一、缓存机制与配置

```python
from report_engine_kit.cache import (
    MemoryCacheBackend, ReportCacheManager, configure_cache)
from report_engine_kit import configure

# 换成 Django 缓存（[django] extra 在 AppConfig.ready() 里会自动做）
configure_cache(DjangoCacheBackend(alias="default"))

# 全局参数
configure(DEFAULT_CACHE_TTL=300,
          LEGACY_SOURCE_CACHE_TTL=120,
          MEMORY_CACHE_MAX_ENTRIES=2048,
          DRILL_PAGE_SIZE_DEFAULT=50,
          DRILL_PAGE_SIZE_MAX=200)
```

- 核心默认后端 `MemoryCacheBackend`：线程安全、TTL、容量淘汰、原子 `add`、glob 模式删除；也可由环境变量 `REPORT_ENGINE_DEFAULT_TTL` / `REPORT_ENGINE_MEMORY_CACHE_MAX_ENTRIES` 覆盖。
- 透视结果缓存 30 秒，代表行 L1 缓存 1 小时（最多 64 个键），**缓存键 = 配置 + 权限指纹 + app 标识**，不同视角绝不串数据。
- 自定义后端只需实现 `get/set/delete`（可选 `add/delete_pattern`）。

---

<a id="十二安全模型-zh"></a>
### 十二、安全模型

- 域访问/管理/列隐藏钩子在**服务层**强制执行，前端传参不可信；
- 透视输入全部白名单清洗并有数量上限（行维度 ≤4、指标 ≤30、筛选值 ≤200、自定义格 ≤5000、公式长度 ≤200、跨报表链 ≤20……）；
- 跨报表引用 `[id]` 以**查看者身份**重算，只允许本人/公开/域管理员可见的报表；
- CSV/XLSX 导出对 `= + - @` 开头的值做公式注入中和；下载文件名 RFC 5981/5987 编码；
- 公式是自研 tokenizer/parser，不是 `eval`，数值 AST 白名单；
- HTML 导出为独立文档，图表 CDN 可关闭；手动数据只接受数字。

---

<a id="十三能力总览与-excel-数据透视表对照-zh"></a>
### 十三、能力总览（与 Excel 数据透视表对照）

| 能力 | report-engine-kit |
|---|---|
| 行字段（多级） | ✅ 最多 4 级，自动合并相同值 |
| 列字段 | ✅ 1 级列维度交叉透视，指标×维度值扇出，可带每指标合计列 |
| 值汇总 | ✅ 计数/求和/平均/最大/最小/去重计数 |
| 值筛选 / 报告筛选 | ✅ 每指标独立筛选 + 全局筛选，AND/OR，关键词，13 种算子 |
| 日期分组 | ✅ 日/周/月/年 + 7 种动态日期规则（含滚轮窗口） |
| 分类汇总（小计） | ✅ 多级行维度自动小计，所有聚合精确合并 |
| 总计 | ✅ 行合计、列合计（可开关） |
| 排序 / Top N | ✅ 按任意指标升降序 + 保留前 N |
| 占比 / 累计 | ✅ pct_of_total、running_total |
| 数字格式 | ✅ 小数位/千分位/百分比/前后缀/乘数，xlsx 真实格式 |
| 计算字段/公式 | ✅ 14 个函数 + 比较运算 + 区间 + 跨报表引用 + 循环检测 |
| 手动改值 | ✅ 单元格覆盖，红字标注，合计重算 |
| 自定义行/列 | ✅ 含公式列、整列模板 |
| 插空白行/空白列 | ✅ 服务端生效，坐标系统一 |
| 移动行/列、隐藏行 | ✅ 服务端生效（引擎+xlsx+CSV 一致） |
| 行高/列宽 | ✅ 分类/按元素配置，移动后仍准确 |
| 手选合并单元格 | ✅ owner 矩阵校验，过期/非法项静默丢弃 |
| 多级表头/文字横幅 | ✅ 布局引擎单一事实来源 |
| 下钻明细 | ✅ domain 钩子，编码集合 → 明细链接 |
| 导出 | ✅ xlsx / csv / pdf / json / html / md |
| 免数据库部署 | ✅ CLI + 原子 JSON 文件存储 |
| Web 集成 | ✅ 可选 Django App（视图/模型/迁移/前端资源）+ ORM 聚合引擎 |
| 统计图 | ✅ 数据源注册表 + 声明式分析（用户零代码配图表） |

---

<a id="十四项目结构-zh"></a>
### 十四、项目结构

```
report-engine-kit/
├── pyproject.toml / MANIFEST.in / LICENSE / CHANGELOG.md
├── README.md                      # 本文档
├── docs/
│   ├── api.md                     # 核心 API 完整参考（英文）
│   ├── django.md                  # Django 接入与 ORM 透视引擎指南（英文）
│   └── release.md                 # 版本发布手册
├── examples/                      # 4 个可直接运行的教程脚本 + 示例数据
├── scripts/release.ps1            # Windows 发布脚本
├── .github/workflows/publish.yml  # 测试/构建/发 TestPyPI/PyPI
├── tests/                         # 110 个单元测试
└── src/report_engine_kit/
    ├── 固定统计图：contracts/base/registry/cache/export/snapshot/analytics/date_utils/cli
    ├── tabular_pivot/             # 内存表透视引擎（含 stores/loaders/adapters/numbers）
    └── contrib/
        ├── pandas_support.py      # [pandas]
        └── django/                # [django]：app/模型/迁移/HTTP/ORM 透视/前端资源
```

---

<a id="十五开发测试与发布-zh"></a>
### 十五、开发、测试与发布

```bash
git clone <repo> && cd report-engine-kit
python -m venv .venv
# Windows
.\.venv\Scripts\Activate.ps1
# macOS/Linux
source .venv/bin/activate

pip install -e ".[dev,all]"
pytest                                   # 110 个测试
python examples/02_tabular_pivot.py      # 手工冒烟
python -m build
python -m twine check dist/*
```

发布（详细步骤见 [docs/release.md](docs/release.md)）：

```powershell
$env:TWINE_USERNAME = "__token__"
$env:TWINE_PASSWORD = "<token>"
.\scripts\release.ps1 -Version 0.3.0 -Target testpypi   # 先 TestPyPI
.\scripts\release.ps1 -Version 0.3.0 -Target pypi       # 正式 PyPI（有二次确认）
```

Git 打 `v*rc*` 标签自动发 TestPyPI，`v*` 正式标签发 PyPI（GitHub Actions，预置 OIDC trusted publishing）。

---

<a id="十六常见问题-faq-zh"></a>
### 十六、常见问题 FAQ

**Q：为什么我的计数和 Excel COUNTIF 对不上？**
A：检查三点：① 编码列是否正确（空编码行不计、同编码多行只算 1 个代表行）；② 指标是否被自己的 `col_filters/date_rule` 二次过滤；③ 受限列是否把数据藏掉了。口径说明见[第十节](#十数据口径计数规则务必先读-zh)。

**Q：sum/avg 遇到文字单元格怎么办？**
A：非数字格被忽略（count 仍计入），avg 的分母只算数字格。金额里的逗号、货币符号会被宽松解析，实在没有数字才算非数字。

**Q：配置在前端能拖，导出 Excel 却没有隐藏行/排序？**
A：0.3.0 起 `row_order/col_order/hidden_rows` 在服务端生效；请确认你用的是 0.3.0+，且配置确实保存在 `layout_overrides` 里。

**Q：公式 `=[12]C3` 报 `#REF!`？**
A：三种原因：报表 12 不存在、当前查看者无权访问（不是本人/公开/域管理员）、或 C3 在目标报表里不是数字格。循环引用会报 `#CYCLE!`。

**Q：不想装 Django / openpyxl 能用吗？**
A：能。核心零依赖，csv/json/html/md 导出、全部透视计算、CLI、JSON 文件存储都可用；只有 xlsx/pdf/pandas/Django 相关能力需要对应 extra。

**Q：PDF 中文乱码？**
A：设置环境变量 `REPORT_ENGINE_FONT_PATH` 指向字体文件；Linux 可装 fonts-wqy-zenhei 或 noto-cjk。

**Q：数据量很大怎么办？**
A：内存表引擎适合台账量级（单表几万行字符串代表行以内）。更大数据量请：① 在 `load_tables` 里先做 SQL 聚合再喂引擎；② 或用 `[django]` extra 的 ORM `PivotReportEngine`（单条 SQL 条件聚合）。

**Q：如何在 Flask/FastAPI 里用？**
A：直接实例化 `PivotService`，把 request JSON 作为 config 传入即可；权限钩子接收你自己的 user 对象（鸭子类型，只需 `.id` / `.is_superuser`）。Django 视图只是薄封装，不依赖 Django 之外的框架。

---

<a id="许可证-zh"></a>
### 许可证

[MIT](LICENSE) © bzsystem team
