Sp_Sales_SalesAnalysiseOfProfit (procedure)

@author:杨恒林 @date:20210616 封存 @describe: 销售利润分析-按客户，从对帐，送货，入库 三个层面分析 三个层面没有关联，只与时间段有关

• Type: PROCEDURE
• Deterministic: NO
• SQL data access: CONTAINS SQL

Parameters

#	Mode	Name	Type
1	IN	tStartDate	datetime
2	IN	tEndDate	datetime
3	IN	sBrId	varchar(100)
4	IN	sSuId	varchar(100)
5	IN	sLoginId	varchar(100)
6	IN	bFilter	varchar(5000)
7	IN	pageNum	int
8	IN	pageSize	int
9	OUT	totalCount	int
10	IN	countCloumn	varchar(5000)
11	OUT	countMapJson	longtext
12	IN	sFilterOrderBy	varchar(5000)
13	IN	sGroupby_select_sql	varchar(5000)
14	IN	sGroupby_group_sql	varchar(5000)

Body

Body is not pre-cached. To inspect: mysql --defaults-file=~/.my.cnf -e 'SHOW CREATE PROCEDURESpSales_SalesAnalysiseOfProfit'._

Narrative

Business context: 成本管理 → 利润分析 → 销售利润分析-按客户 — paged customer-level profit report. Per the embedded comment (@author:杨恒林 @date:20210616 封存, "由对帐、送货、入库 三个层面分析"), it cross-checks profit from three independent angles (入库 / 送货 / 对账) within a date window — the three layers are not joined, only sharing the same period.

What it does: Builds dynamic INSERT INTO SAS(sCustomerId,sCustomerName) ... from eleCustomer filtered by Fun_GetLookCustomer(sLoginId,...). Three parallel UPDATE ... INNER JOINs pull (SUM(dProductNoTaxMoney), SUM(dCostMoney), profit = sales − cost, rate = profit/sales*100) grouped by sCustomerId from viw_pitproductinstore → dInStoreSalesMoney/CostMoney/ProfitMoney, from viw_saldelivergoods → dDelivery*, and from viw_salsaleschecking → dReconciliation*. Filters out rows where all three layers sum to zero, then pages via Sp_Outstanding_Query. Note: the proc has a swapped-target bug — the in-store update writes its rate into dDeliveryProfitRate, and the delivery update writes its rate into dInStoreProfitRate. Verify before trusting the rate columns.

Invocation: Bound as the data-source of the 销售利润分析-按客户 form gfm.sId=19211681019715708609753630 via gdsconfigformmaster.sSqlStr; module path 成本管理 → 利润分析 → 销售利润分析-按客户. Marked 封存 (archived) in 2021-06-16 by the author but still wired to the form. xly-src has no install/optimize script for it — DB-only.