Sp_Manufacture_CapacityStatistics.md 2.45 KB

Edit Raw Blame History



Sp_Manufacture_CapacityStatistics (procedure)


@author:  qmj

                            @date:20210714  封存

                            @describe :     机台负荷

                            各机台后推14天展示排产工时，并显示机台负荷状态


Type: PROCEDURE

Deterministic: NO

SQL data access: CONTAINS SQL


Parameters


#
Mode
Name
Type


1
IN
sLoginId
varchar(4000)


2
IN
sAttribute
varchar(100)


3
IN
sMachineName
varchar(100)


4
IN
sProcessName
varchar(100)


5
IN
sWorkcenterName
varchar(100)


6
IN
iType
char(1)


7
IN
tStartDate
datetime


8
IN
tEndDate
datetime


9
IN
sBrId
varchar(100)


10
IN
sSuId
varchar(100)


11
IN
bFilter
varchar(5000)


12
IN
pageNum
int


13
IN
pageSize
int


14
OUT
totalCount
int


15
IN
countCloumn
varchar(5000)


16
OUT
countMapJson
longtext


17
IN
sFilterOrderBy
varchar(5000)


18
IN
sGroupby_select_sql
varchar(5000)


19
IN
sGroupby_group_sql
varchar(5000)


20
OUT
sColumnNameConfig
longtext


Body

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


Narrative

Business context: 生产管理 → 生产运营报表 → 设备排单负荷分析 (机台负荷) — paged 14-day machine-load forecast. Per-machine workload (排产工时) for the next 14 days plus a load-status indicator, sliced by attribute / machine / process / workcenter / type.

What it does: Resolves iType (1: full per-machine view) and computes the 14-day window from tStartDate. Builds a temporary p_tmp_plan from MftProductionPlanSlave and mftProductionPlan filtered by sBrId/sSuId, then aggregates per machine into p_tmp_T (14 day-bucket columns) using prepared dynamic SQL — paged via pageNum/pageSize and bFilter spliced in, with sColumnNameConfig returned to drive the front-end's dynamic-column rendering.

Invocation: Bound as the data-source of the 机台负荷 / 设备排单负荷分析 form gfm.sId=192116811110616262623708530 via gdsconfigformmaster.sSqlStr — loaded when the user opens that report. Install: xly-src script/标版/30100101/schedule/Sp_Manufacture_CapacityStatistics.sql (plus a 福雅 customer-override under script/客户/福雅/optimizeprocedure.sql).