1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
| USE [YHWCS]
GO
/****** Object: StoredProcedure [dbo].[P_时间统计报表] Script Date: 2024/7/23 11:09:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:xiaoxianghui
-- Create date: 2024年7月5日
-- Description: 时间统计数据
-- =============================================
ALTER PROCEDURE [dbo].[P_时间统计报表]
@TaskNo nvarchar(50) = NULL,
@PalletId nvarchar(50) = NULL,
@PositionId nvarchar(50) = NULL,
@Tunner nvarchar(50) = NULL,
@PLCType nvarchar(50) = NULL,
@IoDate datetime = NULL
AS
BEGIN
SELECT
--distinct tps.Cn, tps.Id,
tps.CreateTime as "创建时间",
tps.Name as "设备名称",
case tps.PLCType
when 1 then '入库'
when 2 then '出库'
when 4 then '移库'
end as "指令类型",
tps.TaskNo as "工作编号",
tps.BarCode as "托盘编号",
tps.StartTime as "开始时间",
tps.EndTime as "结束时间",
--DATEDIFF(SECOND, tps.StartTime, tps.EndTime) AS "工作时间(秒)",
CASE
WHEN tps.PLCType = 1 AND tps.Name = '一层输送线' THEN NewTimeSubQuery1.NewTime
WHEN tps.PLCType = 2 AND tps.Name = '一层输送线' THEN NewTimeSubQuery2.NewTime
WHEN tps.PLCType in (1, 2) AND tps.Name = '双工位RGV' THEN NewTimeSubQuery3.NewTime
WHEN tps.PLCType in (1, 2) AND tps.Name like '%堆垛机' THEN NewTimeSubQuery4.NewTime
--WHEN tps.PLCType = 2 AND tps.Name = '双工位RGV' THEN NewTimeSubQuery5.NewTime
--WHEN tps.PLCType = 2 AND tps.Name like '%堆垛机' THEN NewTimeSubQuery6.NewTime
WHEN tps.PLCType = 1 AND tps.Name not in ( '一层输送线','双工位RGV', '%堆垛机') THEN NewTimeSubQuery7.NewTime
WHEN tps.PLCType = 2 AND tps.Name not in ( '一层输送线','双工位RGV', '%堆垛机') THEN NewTimeSubQuery8.NewTime
WHEN tps.PLCType = 4 AND tps.Name like '%堆垛机' THEN NewTimeSubQuery9.NewTime
ELSE NULL
END AS "流程时间(秒)",
--tps.StartDesign ,
--tps.EndDesign ,
--toh.ordIdNew as "工作编号",
--toh.palletId as "托盘编号",
--toh.srcPositionId as "移出仓位",
--toh.positionId as "仓位编号",
case toh.optType
when 0 then 'WMS错误' when 1 then '整入' when 2 then '补入' when 3 then '整出' when 4 then '分拣' when 5 then '空入' when 6 then '空出/补出' when 7 then '空盘移库' when 8 then '盘库' when 9 then '盘库返库' when 10 then '实盘移库' when 11 then '转移出库' when 12 then '转移入库' when 13 then '移动穿梭车'
end as "工控类型",
toh.ioDate as "工控日期",
--toh.optStation as "操作台",
--toh.pHs as "分配台位",
case toh.design
when -2 then '被删除的指令' when -1 then '待执行' when 0 then '堆垛机待命' when 1 then '堆垛机工作' when 2 then '未过账' when 3 then '已过帐' when 5 then '分拣到达' when 6 then '分拣返回' when 7 then '执行入库' when 8 then '重入未过账' when 9 then '重入已过账' when 10 then 'RGV待命' when 11 then 'RGV工作' when 12 then '线体等待'
end as "工控进程",
--toh.userId as "操作员",
case toh.sysType
when 0 then 'wms' when 1 then 'RF' when 2 then 'DPS' when 3 then '其他'
end as "系统下发",
case toh.byHand
when 0 then '自动' when 1 then '手动'
end as "指令方式",
--case toh.mStatus
-- when 0 then '已组盘' when 1 then '待执行' when 2 then '正在执行'
-- end as "生成状态",
case toh.dStatus
when 0 then '正常' when 1 then '暂停'
end as "执行状态",
--case toh.pltReturn
-- when 0 then '线体上' when 1 then '线体下'
-- end as "运动状态",
--case toh.PlcType
-- when 1 then '入库' when 2 then '出库' when 4 then '移库'
-- end as "指令类型",
toh.tunner as "巷道",
toh.mBillIds as "订单主编号集合"
FROM
tPLCServerLog tps
LEFT JOIN
tOrderHy toh ON tps.TaskNo = toh.ordIdNew AND tps.BarCode = toh.palletId
OUTER APPLY
(
SELECT
COALESCE(
DATEDIFF(SECOND,
MIN(CASE WHEN tps2.Name = '一层输送线' THEN tps2.StartTime ELSE NULL END),
MAX(CASE WHEN tps2.Name = '双工位RGV' THEN tps2.StartTime ELSE NULL END)),
DATEDIFF(SECOND,
MIN(CASE WHEN tps2.Name = '一层输送线' THEN tps2.StartTime END),
MAX(CASE WHEN tps2.Name = '一层输送线' THEN tps2.EndTime END))
) AS NewTime
FROM
tPLCServerLog tps2
--left join tOrderHy toh2 ON tps2.TaskNo = toh2.ordIdNew AND tps2.BarCode = toh2.palletId
WHERE
tps2.TaskNo = tps.TaskNo AND
tps2.BarCode = tps.BarCode AND
tps2.PLCType = 1
--DATEDIFF(SECOND, tps2.StartTime, tps2.EndTime) < 86400 and
--tps2.StartTime < tps2.EndTime and
--tps2.EndTime < toh2.ioDate and
--toh2.ordIdNew is not NULL and toh2.palletId is not NULL
) NewTimeSubQuery1
OUTER APPLY
(
SELECT
COALESCE(
DATEDIFF(SECOND,
MIN(CASE WHEN tps3.Name = '双工位RGV' THEN tps3.EndTime ELSE NULL END),
MAX(CASE WHEN tps3.Name = '一层输送线' THEN tps3.EndTime ELSE NULL END)
),
DATEDIFF(SECOND,
MAX(CASE WHEN tps3.Name = '一层输送线' THEN tps3.StartTime ELSE NULL END),
MAX(CASE WHEN tps3.Name = '一层输送线' THEN tps3.EndTime ELSE NULL END)
)) AS NewTime
FROM
tPLCServerLog tps3
--left join tOrderHy toh3 ON tps3.TaskNo = toh3.ordIdNew AND tps3.BarCode = toh3.palletId
WHERE
tps3.TaskNo = tps.TaskNo AND
tps3.BarCode = tps.BarCode AND
tps3.PLCType = 2
--DATEDIFF(SECOND, tps3.StartTime, tps3.EndTime) < 86400 and
--tps3.StartTime < tps3.EndTime and
----tps3.EndTime < toh3.ioDate and
--toh3.ordIdNew is not NULL and toh3.palletId is not NULL
) NewTimeSubQuery2
OUTER APPLY
(
SELECT
MIN(DATEDIFF(SECOND,
(CASE WHEN tps4.Name = '双工位RGV' THEN tps4.StartTime ELSE NULL END),
(CASE WHEN tps4.Name = '双工位RGV' THEN tps4.EndTime ELSE NULL END)
)) AS NewTime
FROM
tPLCServerLog tps4
--left join tOrderHy toh4 ON tps4.TaskNo = toh4.ordIdNew AND tps4.BarCode = toh4.palletId
WHERE
tps4.TaskNo = tps.TaskNo AND
tps4.BarCode = tps.BarCode AND
tps4.PLCType in (1, 2)
--DATEDIFF(SECOND, tps4.StartTime, tps4.EndTime) < 86400 and
--tps4.StartTime < tps4.EndTime and
--tps4.EndTime < toh4.ioDate and
--toh4.ordIdNew is not NULL and toh4.palletId is not NULL
) NewTimeSubQuery3
OUTER APPLY
(
SELECT
COALESCE(
DATEDIFF(SECOND,
MAX(CASE WHEN tps5.Name like '%堆垛机' THEN tps5.StartTime ELSE NULL END),
MAX(CASE WHEN tps5.Name like '%堆垛机' THEN tps5.EndTime ELSE NULL END)),
DATEDIFF(SECOND,
MAX(CASE WHEN tps5.Name like '%堆垛机' THEN tps5.StartTime ELSE NULL END),
MAX(CASE WHEN tps5.Name like '%堆垛机' THEN toh5.ioDate ELSE NULL END))
) AS NewTime
FROM
tPLCServerLog tps5
left join tOrderHy toh5 ON tps5.TaskNo = toh5.ordIdNew AND tps5.BarCode = toh5.palletId
WHERE
tps5.TaskNo = tps.TaskNo AND
tps5.BarCode = tps.BarCode AND
tps5.PLCType in (1, 2)
--DATEDIFF(SECOND, tps5.StartTime, tps5.EndTime) < 86400 and
--tps5.StartTime < tps5.EndTime and
--toh5.ordIdNew is not NULL and toh5.palletId is not NULL
----tps5.EndTime < toh5.ioDate
) NewTimeSubQuery4
--OUTER APPLY
--(
-- SELECT
-- MIN(DATEDIFF(SECOND,
-- (CASE WHEN tps6.Name = '双工位RGV' THEN tps6.StartTime ELSE NULL END),
-- (CASE WHEN tps6.Name = '双工位RGV' THEN tps6.EndTime ELSE NULL END)
-- )) AS NewTime
-- FROM
-- tPLCServerLog tps6
-- --left join tOrderHy toh6 ON tps6.TaskNo = toh6.ordIdNew AND tps6.BarCode = toh6.palletId
-- WHERE
-- tps6.TaskNo = tps.TaskNo AND
-- tps6.BarCode = tps.BarCode AND
-- tps6.PLCType = 2
-- --DATEDIFF(SECOND, tps6.StartTime, tps6.EndTime) < 86400 and
-- --tps6.StartTime < tps6.EndTime and
-- --tps6.EndTime < toh6.ioDate and
-- --toh6.ordIdNew is not NULL and toh6.palletId is not NULL
--) NewTimeSubQuery5
--OUTER APPLY
--(
-- SELECT
-- DATEDIFF(SECOND,
-- MAX(CASE WHEN tps7.Name like '%堆垛机' THEN tps7.StartTime ELSE NULL END),
-- MAX(CASE WHEN tps7.Name like '%堆垛机' THEN tps7.EndTime ELSE NULL END)
-- ) AS NewTime
-- FROM
-- tPLCServerLog tps7
-- --left join tOrderHy toh7 ON tps7.TaskNo = toh7.ordIdNew AND tps7.BarCode = toh7.palletId
-- WHERE
-- tps7.TaskNo = tps.TaskNo AND
-- tps7.BarCode = tps.BarCode AND
-- tps7.PLCType = 2
-- --DATEDIFF(SECOND, tps7.StartTime, tps7.EndTime) < 86400 and
-- --tps7.StartTime < tps7.EndTime and
-- --tps7.EndTime < toh7.ioDate and
-- --toh7.ordIdNew is not NULL and toh7.palletId is not NULL
--) NewTimeSubQuery6
OUTER APPLY
(
SELECT
DATEDIFF(SECOND,
MAX(CASE WHEN tps8.Name not in ( '一层输送线','双工位RGV', '%堆垛机') THEN tps8.StartTime ELSE NULL END),
MAX(CASE WHEN tps8.Name not in ( '一层输送线','双工位RGV', '%堆垛机') THEN tps8.EndTime ELSE NULL END)
) AS NewTime
FROM
tPLCServerLog tps8
--left join tOrderHy toh8 ON tps8.TaskNo = toh8.ordIdNew AND tps8.BarCode = toh8.palletId
WHERE
tps8.TaskNo = tps.TaskNo AND
tps8.BarCode = tps.BarCode AND
tps8.PLCType = 1
--DATEDIFF(SECOND, tps8.StartTime, tps8.EndTime) < 86400 and
--tps8.StartTime < tps8.EndTime and
--tps8.EndTime < toh8.ioDate and
--toh8.ordIdNew is not NULL and toh8.palletId is not NULL
) NewTimeSubQuery7
OUTER APPLY
(
SELECT
DATEDIFF(SECOND,
MAX(CASE WHEN tps9.Name not in ( '一层输送线','双工位RGV', '%堆垛机') THEN tps9.StartTime ELSE NULL END),
MAX(CASE WHEN tps9.Name not in ( '一层输送线','双工位RGV', '%堆垛机') THEN tps9.EndTime ELSE NULL END)
) AS NewTime
FROM
tPLCServerLog tps9
--left join tOrderHy toh9 ON tps9.TaskNo = toh9.ordIdNew AND tps9.BarCode = toh9.palletId
WHERE
tps9.TaskNo = tps.TaskNo AND
tps9.BarCode = tps.BarCode AND
tps9.PLCType = 2
--DATEDIFF(SECOND, tps9.StartTime, tps9.EndTime) < 86400 and
--tps9.StartTime < tps9.EndTime and
--tps9.EndTime < toh9.ioDate and
--toh9.ordIdNew is not NULL and toh9.palletId is not NULL
) NewTimeSubQuery8
OUTER APPLY
(
SELECT
DATEDIFF(SECOND,
MAX(CASE WHEN tps10.Name like '%堆垛机' THEN tps10.StartTime ELSE NULL END),
MAX(CASE WHEN tps10.Name like '%堆垛机' THEN tps10.EndTime ELSE NULL END)
) AS NewTime
FROM
tPLCServerLog tps10
--left join tOrderHy toh10 ON tps10.TaskNo = toh10.ordIdNew AND tps10.BarCode = toh10.palletId
WHERE
tps10.TaskNo = tps.TaskNo AND
tps10.BarCode = tps.BarCode AND
tps10.PLCType = 4
--DATEDIFF(SECOND, tps10.StartTime, tps10.EndTime) < 86400 and
--tps10.StartTime < tps10.EndTime and
--toh10.ordIdNew is not NULL and toh10.palletId is not NULL
--tps10.EndTime < toh10.ioDate
) NewTimeSubQuery9
where
-- --DATEDIFF(SECOND, tps.StartTime, tps.EndTime) < 86400 and
-- tps.Cn <> 0 AND
-- tps.StartTime IS NOT NULL AND
-- tps.EndTime IS NOT NULL AND
-- --tps.StartTime < tps.EndTime AND
-- toh.ordIdNew is not NULL and toh.palletId is not NULL
-- --DATEDIFF(SECOND, tps.StartTime, tps.EndTime) > 5
-- --AND tps.TaskNo = '17731' and tps.BarCode = 'T03452'
(@Tunner IS NULL OR @Tunner = '' OR toh.tunner = @Tunner)
AND (@TaskNo IS NULL OR @TaskNo = '' OR tps.TaskNo = @TaskNo)
AND (@PalletId IS NULL OR @PalletId = '' OR toh.palletId = @PalletId)
AND (@PositionId IS NULL OR @PositionId = '' OR toh.positionId = @PositionId OR srcpositionid=@PositionId)
AND (@PLCType IS NULL OR @PLCType = '' OR tps.PLCType = @PLCType)
AND (@IoDate IS NULL OR @IoDate = '' OR convert(varchar(10),toh.ioDate,21) = convert(varchar(10),@IoDate,21))
--order by tps.TaskNo, tps.BarCode
END
|
