# `Sp_Outstanding_Query` (procedure)

> 未清存储过程查询、汇总、分页


- **Type:** PROCEDURE
- **Deterministic:** NO
- **SQL data access:** CONTAINS SQL

## Parameters

| # | Mode | Name | Type |
|---|---|---|---|
| 1 | IN | `s_sql` | `longtext` |
| 2 | OUT | `sOutSql` | `longtext` |
| 3 | IN | `b_filter` | `longtext` |
| 4 | IN | `page_num` | `int` |
| 5 | IN | `page_size` | `int` |
| 6 | OUT | `totalCount` | `int` |
| 7 | IN | `count_cloumn` | `varchar(5000)` |
| 8 | OUT | `countMapJson` | `longtext` |
| 9 | IN | `sGroup_by` | `varchar(5000)` |
| 10 | IN | `sOrder_by` | `varchar(5000)` |
| 11 | IN | `sTable_alias` | `varchar(255)` |
| 12 | IN | `sGroupby_select_sql` | `varchar(5000)` |
| 13 | IN | `sGroupby_group_sql` | `varchar(5000)` |

## Body

_Body is not pre-cached. To inspect: `mysql --defaults-file=~/.my.cnf -e 'SHOW CREATE PROCEDURE `Sp_Outstanding_Query`'`._

## Narrative

**Business context:** Cross-module reporting helper — generic pager/aggregator that wraps a caller-built `SELECT` and returns the paged rows, the total row count and per-column totals as `countMapJson`. The COMMENT `未清存储过程查询、汇总、分页` (open-balance query, aggregation, pagination) describes its original purpose for 未清/未结 reports, but it is now the standard back-end of most paginated business reports.

**What it does:** Translates `b_filter` to a WHERE fragment via `Fun_bFilter_toWhere(b_filter, sTable_alias)` (default alias `A`); detects whether `s_sql` already contains a `WHERE` and either wraps it as `select * from (s_sql sGroup_by) <alias> where 1=1 <bFilterWhere>` or appends the filter directly; optionally re-wraps with `sGroupby_select_sql ... sGroupby_group_sql` for grouped reports. Runs `SELECT COUNT(1) INTO @totalCountTmp FROM (...)` into `totalCount`. For every column in the comma-separated `count_cloumn` list it builds a SUM/aggregation into `countMapJson`, with a separate code path for `tree_*` columns that need tree-aware rollups. Finally re-emits the full SQL into `sOutSql` for the caller to PREPARE/EXECUTE.

**Invocation:** Called by ~96 sibling routines — most paginated report procs (e.g. `Sp_Daysalecustomer`, `Sp_DaysaleSalesMan`, `Sp_Inventory_*`, `Sp_Sales_*`, `Sp_Payables_*`, `Sp_Receivables_*`, `Sp_OverdueNo*`, `Sp_OneDatePlan_Machine`, etc.) hand their dynamic SQL string + paging/grouping inputs to this proc and use the OUT `sOutSql`. No xly-src Java caller — it is purely chained from other procedures.
