Sp_MaterialHistoricalComparison (procedure)
- Type: PROCEDURE
- Deterministic: NO
- SQL data access: CONTAINS SQL
Parameters
| # | Mode | Name | Type |
|---|---|---|---|
| 1 | IN | sStartDate |
varchar(32) |
| 2 | IN | sEndDate |
varchar(32) |
| 3 | IN | sBrId |
varchar(100) |
| 4 | IN | sSuId |
varchar(100) |
Body
Body is not pre-cached. To inspect: mysql --defaults-file=~/.my.cnf -e 'SHOW CREATE PROCEDURESpMaterialHistoricalComparison'._
Narrative
Business context: 采购管理 → 分析报表 → 主材历史数据对比报表 — month-over-month price/quantity comparison for non-paper materials. Pulls two month-buckets of audited production-material consumption (viw_mitproductionmaterials with bCheck=1, excluding sMaterialsType='纸张'), computes per-material average price and total qty/money for each side, and reports the price delta — used by procurement to surface materials whose unit cost has drifted between two months.
What it does: Creates temp MaterialHistorical (one row per material × style per month) and MaterialHistoricalComparison (the comparison grid). insert into MaterialHistorical from viw_mitproductionmaterials LEFT JOIN elematerials keyed on date_format(tCheckDate,'%Y-%m') IN (sStartDate, sEndDate). Two update MaterialHistoricalComparison passes splice each side's dDosage / dMoney / dPrice into dBeforeDosage / dBeforeMoney / dBeforeAveragePrice and dEnd*. Final update sets dTotalPriceDifference = (dEndAveragePrice - dBeforeAveragePrice) * dBeforeDosage. Returns the comparison grid as the result-set. Note: the proc takes sBrId / sSuId and defaults them to '1111111111' but never filters by tenant downstream — every tenant's data is mixed in the comparison.
Invocation: Bound as the data-source of two report forms via gdsconfigformmaster.sSqlStr: 采购主材价格浮动表 (gfm.sId 16147620210008611994372989400000, under 物料采购流程 → 采购主材价格浮动表 module 101251240115016147537123820) and 主材历史数据对比报表 (gfm.sId 101251240115016204642443080, under 采购管理 → 主材历史数据对比报表 module 101251240115016204642442770). Install script ships at script/标版/30100101/Sp_MaterialHistoricalComparison.sql.