# Slice 5 — extending: a per-customer SQL override

There is a limit to what the metadata-driven framework can express.

A customer needs a different aggregation rule for their sales-check report.
Or a unique custom view to feed their cost dashboard. Or a stored procedure
whose behaviour deviates fundamentally from the standard. The framework's
overlay tables ([Slice 4](04-custom-field.md)) can add fields and override
SQL fragments, but they cannot replace the *logic* of a stored procedure —
the proc is code, not data.

When that limit is reached, xly's escape hatch is **to commit a hand-written
SQL file under `script/客户/<customer-name>/` and apply it directly to that
customer's schema**. This bypasses the metadata layer entirely. It is a
real channel, used in production, for several customers — and the wiki must
document it honestly: the scope, the cost, when to reach for it, and the
operational hygiene it requires.

## What we're documenting

A representative override taken from one customer directory under
`script/客户/`:

| | |
|---|---|
| **Customer** | 重庆展印 (Chongqing Zhanyin Printing) |
| **Override file** | `script/客户/重庆展印/Sp_SalSalesCheck.sql` |
| **What it replaces** | The standard `Sp_SalSalesCheck` stored procedure (sales-check listing) |
| **Companion file** | `script/客户/重庆展印/viw_salsaleschecking_pro.sql` (a custom view this proc reads from) |
| **Deployment** | **Manual** — applied by an engineer / DBA against this customer's schema |

The file's first two lines tell you everything about the channel's
shape:

```sql
DROP PROCEDURE IF EXISTS `Sp_SalSalesCheck`;
delimiter ;;
CREATE PROCEDURE `Sp_SalSalesCheck`(IN sLoginId varchar(100), …)
```

The standard proc is dropped, the custom one takes its place. The
metadata layer in `gdsmodule` continues to reference the proc by name
(`sProcName = 'Sp_SalSalesCheck'`); the runtime calls it as it always
would — the difference lives entirely inside the procedure body, which
is now the engineer-authored variant.

## Customers with overrides in the codebase

Each customer with bespoke SQL gets a subdirectory under `script/客户/`,
named for the customer (typically the Chinese trading name). Each
subdirectory holds anywhere from one to a dozen `.sql` files. Most files
are custom procs (`Sp_*`) and custom views (`viw_*`). Reading the
directory listing is itself a form of system documentation: the
customers and features here are the ones that needed bespoke behaviour
the framework couldn't express. The current set of customer directories
will grow and shrink over time — `ls script/客户/` for the live list.

## How an override gets in

There is **no automated channel**. Searching the Java codebase for
phrases like "load script/客户" or a deployment service that walks
those directories returns nothing. The mechanism is operational:

1. An engineer authors the override `.sql` (using the standard proc as a
   starting point, modifying the body).
2. The file is committed under `script/客户/<customer>/` for traceability.
3. The file is run **manually** against the target customer's MySQL
   schema using `mysql --defaults-file=… < the-file.sql` (or equivalent).
4. From that point, the customer's schema has a different proc body
   from every other customer's. The framework code is unchanged.

The repo location matters: keeping these in the codebase repo (not in a
separate "ops" area) means the engineer team can see at a glance which
customers diverge from standard. It does not mean these files ship as
part of any build.

## Why this is a different channel from Slice 4

Slice 4's customization (`gdsconfigformcustomslave`) and Slice 1's
metadata-driven Add/Update path both stay inside the framework.
Customer-specific behaviour expressed there is *layered over* the
shared codebase — every tenant's runtime reads the same Java, the same
MyBatis mappers, the same standard procs.

The Slice 5 channel is *underneath* the framework. The customer's
schema has a literally-different stored procedure with the same name.
The Java/MyBatis code calling `Sp_SalSalesCheck` has no idea whether
the standard or a 重庆展印 variant is on the other end of the call. The
framework doesn't know; the framework can't tell.

This makes overrides:

- **Powerful.** Anything you can write in MySQL stored-procedure SQL,
  you can use to replace standard behaviour.
- **Operationally fragile.** The override must be re-applied (or kept
  alive) whenever the customer's schema is rebuilt, restored, or
  migrated. It does not travel with backups of the codebase, only with
  backups of the database.
- **Hard to reason about.** A maintainer reading the standard
  `Sp_SalSalesCheck` source has to remember that *for some customers*
  the proc on the live DB is a different piece of code with the same
  name. Stack traces and "what does this proc do" depend on which
  schema you're connected to.

The right rule of thumb: prefer Slice-4 metadata customization. Reach
for Slice-5 SQL overrides only when the metadata model genuinely cannot
express what the customer needs.

## Worked-example: 重庆展印's `Sp_SalSalesCheck` vs the standard

Quantified diff against the live dev DB:

| Aspect | Standard `Sp_SalSalesCheck` (in DB) | 重庆展印 override (`script/客户/重庆展印/Sp_SalSalesCheck.sql`) |
|---|---|---|
| Body length | 1,714 lines | 723 lines (≈42 % the size) |
| Parameter signature | 14 params: `sLoginId, sCustomerId, sBrId, sSuId, bFilter, sUnTaskFormId, pageNum, pageSize, totalCount(OUT), countCloumn, countMapJson(OUT), sFilterOrderBy, sGroupby_select_sql, sGroupby_group_sql` | **Identical** — same 14 params in the same order |
| `SysSystemSettings.CbxSrcNoCheck` lookup | **Not used** | **Used** (drives "未对账印件清单来源" — which billing-type sources feed the report) |
| `Fun_GetLookCustomer(sLoginId, sBrId, sSuId)` permission scoping | Used | Used (same call) |
| Temp-table-based aggregation flow (`B1`, `B2` etc., several `DROP TEMPORARY TABLE` + `INSERT INTO` blocks) | Heavy (the bulk of the 1,714 lines) | Removed / simplified |

So 重庆展印's override:
- Keeps the framework call-site unchanged (identical parameter
  signature so the metadata-driven dispatcher
  ([proc-dispatch.md](../reference/maintainer/proc-dispatch.md)) still
  invokes it correctly).
- Adds a `CbxSrcNoCheck` system-setting branch that the standard
  doesn't expose. Twelve other `Sp_*` procs in the schema also use
  `CbxSrcNoCheck` (`Sp_Manufacture_MftWorkOrderAround`,
  `Sp_OverdueNoCheck`, `Sp_Receivables_*` family, plus sibling
  `Sp_SalSalesCheck1`/`_1227`/`_YanBao`/`_ded_copy1`); the override
  brings that pattern into the customer's main proc.
- Strips the standard's heavy temp-table aggregation flow — a simpler
  query path, not a more complex one. The customer's check semantics
  evidently don't need the full standard aggregation.

A maintainer wanting the *exact* business-rule difference should diff
the two file bodies directly:

```bash
mysql --defaults-file=$HOME/.my.cnf xlyweberp_saas_ai \
  -BNe "SELECT ROUTINE_DEFINITION FROM information_schema.routines \
        WHERE ROUTINE_NAME='Sp_SalSalesCheck'" > /tmp/std.sql
diff /tmp/std.sql script/客户/重庆展印/Sp_SalSalesCheck.sql | head -200
```

## Worked-example 2: 万昌 builds a multi-level approval workflow

The 重庆展印 example above replaces *one* proc body. The
**`script/客户/万昌/`** directory shows a more ambitious pattern: the
customer extends the schema and builds a multi-level approval
workflow that the standard framework doesn't ship.

The customisation tree (excerpt):

```
script/客户/万昌/
├── 计件工资/
│   ├── 日报审核/
│   │   └── 领班驳回.sql               ← this slice's anchor
│   ├── 报表/
│   │   ├── 包装补时.sql
│   │   ├── 员工大废品.sql
│   │   ├── 班组大废品率查询报表.sql
│   │   ├── 手工质检组返工.sql
│   │   └── Sp_Manual_quality_inspection_rework.sql
│   └── 计件工资核算/
│       ├── 计件工资/
│       │   ├── sp_piece_rate_j.sql
│       │   ├── sp_piece_rate_JZ.sql
│       │   ├── sp_piece_rate_other.sql
│       │   └── sp_piece_rate_w.sql
│       ├── 员工工资汇总查询/员工工资汇总查询.sql
│       ├── Sp_BtnEven_CalcJsHs.sql
│       └── sp_btn_WorkOrderAssessmentPassRate.sql
├── Sp_getworkorder_calc_cb.sql
└── …
```

The Chinese-named subdirectories (`计件工资`/`日报审核` = "piece-rate
wages / daily-report approval") encode the customer's organisational
flow into the file system itself. A maintainer reading `ls` knows
which business process each script belongs to.

### What the rejection script actually does

`领班驳回.sql` ("Foreman Rejection") is 185 lines defining
`Sp_mftproductionreportmaster_check1_0`. The naming is xly's
state-transition convention: `Sp_<table>_check<currentState>_<nextState>`,
so `check1_0` means "transition from state 1 (approved) back to
state 0 (draft)" — i.e., a rejection.

Trimmed body of the central UPDATE:

```sql
SET p_setSql = CONCAT('bManager = 0,
                       bIPQC = 0,
                       bDeputy = 0,
                       bSubmit = 0,
                       bWorkshopManager = 0,
                       bCheck = 0,
                       sRejectMemo = ''', p_sRejectMemo, ''',
                       sMReserve1 = ', p_textareaValue);

Set @sSqlStmt = CONCAT('Update mftproductionreportmaster
                        Set ', p_setSql, '
                        Where sId = ''', p_sTmpId, '''
                          AND sBrandsId = ''', sBrId, '''
                          AND sSubsidiaryId = ''', sSuId, '''');
PREPARE sSqlStmt FROM @sSqlStmt;
EXECUTE sSqlStmt;

CALL sp_add_flow_log(p_sTmpId, p_sTmpId, '驳回', '驳回', '驳回',
                     sMakePerson, p_sRejectMemo, @sReturn, @sCode);
```

So one button click resets **six** approval flags simultaneously,
appends to a per-row rejection-reason history, and writes to a
custom audit log.

### What's customer-side and not in standard

Verified against the dev DB recon target (`xlyweberp_saas_ai`):

| Customisation | Standard schema? | 万昌 needs to add it |
|---|---|---|
| Multi-level approval columns: `bManager`, `bIPQC`, `bDeputy`, `bSubmit`, `bWorkshopManager` on `mftproductionreportmaster` | **No** — only `bCheck`, `sCheckPerson`, `tCheckDate` exist. | Yes — `ALTER TABLE` to add 5 boolean columns. |
| `sRejectMemo` rejection-reason history column | **No** | Yes — `ALTER TABLE` to add a longtext. |
| `sp_add_flow_log` audit-log proc | **No** — does not exist in standard. | Yes — wholly customer-defined. |
| Naming convention `Sp_<table>_check<n>_<m>` | **No** — no procs in DB use this pattern. | Yes — 万昌's convention. |
| Hook into the framework's button machinery | Yes — `gdsconfigformslave.sButtonParam` points at the proc name. | (configuration only) |

So 万昌's "Foreman Rejection" workflow is **a customer-built
state-machine atop xly's button primitive**: schema extension +
custom procs + custom audit log. The framework provides only the
button-press dispatch (via `/business/genericProcedureCall*` or the
button-param hook on the form-slave). Everything else — what state
the document is in, what flags toggle, what audit text gets logged —
is customer-side.

This is fundamentally different from how Activiti would solve the
same problem (BPMN graph + assignee model + Activiti's task table).
xly's framework lets the customer choose either model:
- **Activiti style**: deploy a BPMN, link via `gdsmoduleflow`,
  flip `ConstantUtils.bCheckflowCheck = true` (see
  [activiti.md](../reference/maintainer/activiti.md#path-3-activiti-bpmn-workflow-gated-currently-disabled-in-code)).
- **万昌 style**: extend the schema, write transition procs, drop
  them under `script/客户/<name>/<flow-name>/`, apply manually.

The 万昌 style is what the codebase actually shows in production-
adjacent customisations — Activiti is wired but no customer
directory under `script/客户/` deploys a BPMN, while 万昌-style
schema-extending workflows DO show up. That's the empirical answer
to "how is workflow customised in this repo?": **schema-extending
stored procs delivered via per-customer override scripts**.

### Customer customisation patterns at a glance

Of the 18 customer override directories, most don't customise
*workflow* per se — they customise **calculations and reports**.
The breakdown of what each directory contains:

- `万昌` (14 files): includes the `领班驳回.sql` workflow extension,
  plus piece-rate wage calculation procs.
- `千彩` (50 files): the most heavily customised customer. Mostly
  per-tenant calculation overrides (`Sp_Calc_*`, `Sp_Inventory_*`,
  `Sp_Manufacture_*`) and one workflow-list view
  (`viw_NoSalSalesChecking`).
- `重庆展印` (2 files): replacement of one sales-check proc + a
  companion view, as documented above.
- `朝阳` (8), `金宣发` (8), `无锡中江` (8), `亚明威` (6), `福雅` (5),
  `金九` (5), `快马` (4), and others: smaller calc / report
  overrides.

So the workflow customisation pattern (schema extension + transition
procs + custom audit) is **rare** — it's worth doing only when the
customer's process genuinely doesn't fit a single-step approval and
the standard framework's `bCheck` toggle isn't enough. Most customer
divergence is calculation logic, not workflow shape.

The companion view `viw_salsaleschecking_pro.sql` exists for the same
reason — when the override needs a join shape the standard doesn't
provide, the engineer authors a customer-specific view, drops it into
the same directory, and applies it to that customer's schema alongside
the proc.

## Concepts this slice introduces

- *The two customization channels* (sharpens [the existing concept page](../concepts/customization-channels.md)):
  metadata edits via BACK (slices 1, 2, 4) versus raw SQL overrides
  applied directly to a customer's schema (this slice).
- *Schema divergence between customers* — the same proc name can mean
  different procs in different customer DBs. Affects how maintainers
  reason about runtime behaviour.

## Reference entries this slice exercises

- New page (Maintainer): *Per-customer SQL overrides* — the
  `script/客户/<customer>/` convention, the manual application
  process, the operational implications, the auditing patterns that
  let a maintainer see which customers diverge from standard for which
  procs.

## Open verification items

1. **Is the application of these scripts truly entirely manual, or is
   there a Quartz-job / `DbToDbController` mechanism that loads them?**
   The `xlyFlow/dbtodb` package is named suspiciously close to "DB
   migration" but the surface area looked like inter-DB sync, not
   script application. Confirm by reading
   `DbToDbServiceImpl.java`.
2. **Auditing.** Build a small script that connects to a customer's DB
   and diffs every `Sp_*`/`viw_*` body against the standard. Customers
   running unexpectedly-divergent procs are an operational risk.
3. **Side-by-side `Sp_SalSalesCheck` diff** — the wiki currently
   describes the override structurally. A future revision should
   include the actual body diff that shows which business rule changed
   for 重庆展印 and why.
4. **Lifecycle.** When a customer migrates schemas (upgrade, restore,
   rebuild), how is each override re-applied? A documented runbook for
   that operation belongs in the maintainer chapter on deployment.