full_analysis.py
8.84 KB
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
# -*- coding: utf-8 -*-
"""
ERP推送问题单据分析
排除条件：
1. 调拨入库单（DBR）
2. 头尾状态全是"回传"的（假设状态800/900是回传成功）
3. 新建状态（first_status=0）
4. 倒扣料出入库单据（不用回传ERP）
重点关注：
- task_header.status 不是50的（任务未完成）
"""
import pyodbc
conn_str = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=192.168.1.91,1433;DATABASE=wms4_global;UID=sa;PWD=HHrobot123.'
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()
print("=" * 100)
print("ERP推送问题单据分析报告")
print("=" * 100)
# ============================================================
# 一、入库单分析
# ============================================================
print("\n" + "=" * 100)
print("【一、入库单问题分析】")
print("=" * 100)
# 1. 入库单总数和类型分布
print("\n【1. 入库单总数和类型分布】")
cursor.execute("""
SELECT type, COUNT(*) as cnt
FROM receipt_header
GROUP BY type
ORDER BY cnt DESC
""")
print(" 类型 | 数量")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]}条")
# 2. 入库单头尾状态组合分布
print("\n【2. 入库单头尾状态组合分布】")
cursor.execute("""
SELECT
CAST(ISNULL(first_status, 0) AS VARCHAR) + '/' +
CAST(ISNULL(last_status, 0) AS VARCHAR) as status_combo,
COUNT(*) as cnt
FROM receipt_header
GROUP BY CAST(ISNULL(first_status, 0) AS VARCHAR), CAST(ISNULL(last_status, 0) AS VARCHAR)
ORDER BY cnt DESC
""")
print(" 状态(头/尾) | 数量")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]}条")
# 3. 通过 task_detail 关联查询入库任务的完成状态
print("\n【3. 入库任务按状态分布】")
cursor.execute("""
SELECT
th.status as task_status,
COUNT(DISTINCT td.receipt_id) as receipt_count,
COUNT(DISTINCT th.id) as task_count
FROM task_detail td
INNER JOIN task_header th ON td.task_header_id = th.id
WHERE td.receipt_id IS NOT NULL
GROUP BY th.status
ORDER BY th.status
""")
print(" 任务状态 | 入库单数 | 任务数")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]}条入库单, {row[2]}个任务")
# 4. 重点：任务未完成（status != 50）的入库单
print("\n【4. 【重点】入库任务未完成的问题单据（task_header.status != 50）】")
cursor.execute("""
SELECT
rh.code as receipt_code,
rh.type,
rh.first_status,
rh.last_status,
th.status as task_status,
th.task_type,
th.container_code,
rh.refer_code,
rh.create_time
FROM receipt_header rh
INNER JOIN task_detail td ON rh.id = td.receipt_id
INNER JOIN task_header th ON td.task_header_id = th.id
WHERE th.status != 50
AND rh.type NOT IN ('DBR')
GROUP BY rh.code, rh.type, rh.first_status, rh.last_status, th.status, th.task_type, th.container_code, rh.refer_code, rh.create_time
ORDER BY rh.create_time DESC
""")
rows = cursor.fetchall()
print(f"\n 共找到 {len(rows)} 条问题入库单记录:")
if rows:
print(" " + "-" * 110)
print(f" {'入库单号':<25} {'类型':<10} {'头':<5} {'尾':<5} {'任务':<5} {'任务类型':<8} {'组盘码':<15} {'上游单号':<20}")
print(" " + "-" * 110)
for row in rows:
print(f" {row[0] or '':<25} {row[1] or '':<10} {row[2] or '':<5} {row[3] or '':<5} {row[4] or '':<5} {row[5] or '':<8} {row[6] or '':<15} {row[7] or '':<20}")
else:
print(" 没有找到未完成的任务！")
# 5. 查看入库任务状态=1的单据（未开始的）
print("\n【5. 入库任务状态=1（未开始）的单据】")
cursor.execute("""
SELECT
rh.code as receipt_code,
rh.type,
rh.first_status,
rh.last_status,
th.status as task_status,
th.task_type,
th.container_code,
rh.refer_code,
rh.create_time
FROM receipt_header rh
INNER JOIN task_detail td ON rh.id = td.receipt_id
INNER JOIN task_header th ON td.task_header_id = th.id
WHERE th.status = 1
AND rh.type NOT IN ('DBR')
GROUP BY rh.code, rh.type, rh.first_status, rh.last_status, th.status, th.task_type, th.container_code, rh.refer_code, rh.create_time
ORDER BY rh.create_time DESC
""")
rows = cursor.fetchall()
print(f"\n 共找到 {len(rows)} 条:")
if rows:
print(" " + "-" * 110)
print(f" {'入库单号':<25} {'类型':<10} {'头':<5} {'尾':<5} {'任务':<5} {'任务类型':<8} {'组盘码':<15} {'上游单号':<20}")
print(" " + "-" * 110)
for row in rows:
print(f" {row[0] or '':<25} {row[1] or '':<10} {row[2] or '':<5} {row[3] or '':<5} {row[4] or '':<5} {row[5] or '':<8} {row[6] or '':<15} {row[7] or '':<20}")
# ============================================================
# 二、出库单分析
# ============================================================
print("\n" + "=" * 100)
print("【二、出库单问题分析】")
print("=" * 100)
# 1. 出库单总数和类型分布
print("\n【1. 出库单总数和类型分布】")
cursor.execute("""
SELECT type, COUNT(*) as cnt
FROM shipment_header
GROUP BY type
ORDER BY cnt DESC
""")
print(" 类型 | 数量")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]}条")
# 2. 出库单头尾状态组合分布
print("\n【2. 出库单头尾状态组合分布】")
cursor.execute("""
SELECT
CAST(ISNULL(first_status, 0) AS VARCHAR) + '/' +
CAST(ISNULL(last_status, 0) AS VARCHAR) as status_combo,
COUNT(*) as cnt
FROM shipment_header
GROUP BY CAST(ISNULL(first_status, 0) AS VARCHAR), CAST(ISNULL(last_status, 0) AS VARCHAR)
ORDER BY cnt DESC
""")
print(" 状态(头/尾) | 数量")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]}条")
# 3. 通过 task_detail 关联查询出库任务的完成状态
print("\n【3. 出库任务按状态分布】")
cursor.execute("""
SELECT
th.status as task_status,
COUNT(DISTINCT td.shipment_id) as shipment_count,
COUNT(DISTINCT th.id) as task_count
FROM task_detail td
INNER JOIN task_header th ON td.task_header_id = th.id
WHERE td.shipment_id IS NOT NULL
GROUP BY th.status
ORDER BY th.status
""")
print(" 任务状态 | 出库单数 | 任务数")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]}条出库单, {row[2]}个任务")
# 4. 重点：任务未完成（status != 50）的出库单
print("\n【4. 【重点】出库任务未完成的问题单据（task_header.status != 50）】")
cursor.execute("""
SELECT
sh.code as shipment_code,
sh.type,
sh.first_status,
sh.last_status,
th.status as task_status,
th.task_type,
th.container_code,
sh.refer_code,
sh.create_time
FROM shipment_header sh
INNER JOIN task_detail td ON sh.id = td.shipment_id
INNER JOIN task_header th ON td.task_header_id = th.id
WHERE th.status != 50
GROUP BY sh.code, sh.type, sh.first_status, sh.last_status, th.status, th.task_type, th.container_code, sh.refer_code, sh.create_time
ORDER BY sh.create_time DESC
""")
rows = cursor.fetchall()
print(f"\n 共找到 {len(rows)} 条问题出库单记录:")
if rows:
print(" " + "-" * 110)
print(f" {'出库单号':<25} {'类型':<10} {'头':<5} {'尾':<5} {'任务':<5} {'任务类型':<8} {'组盘码':<15} {'上游单号':<20}")
print(" " + "-" * 110)
for row in rows:
print(f" {row[0] or '':<25} {row[1] or '':<10} {row[2] or '':<5} {row[3] or '':<5} {row[4] or '':<5} {row[5] or '':<8} {row[6] or '':<15} {row[7] or '':<20}")
else:
print(" 没有找到未完成的任务！")
# 5. 查看出库任务状态=1的单据（未开始的）
print("\n【5. 出库任务状态=1（未开始）的单据】")
cursor.execute("""
SELECT
sh.code as shipment_code,
sh.type,
sh.first_status,
sh.last_status,
th.status as task_status,
th.task_type,
th.container_code,
sh.refer_code,
sh.create_time
FROM shipment_header sh
INNER JOIN task_detail td ON sh.id = td.shipment_id
INNER JOIN task_header th ON td.task_header_id = th.id
WHERE th.status = 1
GROUP BY sh.code, sh.type, sh.first_status, sh.last_status, th.status, th.task_type, th.container_code, sh.refer_code, sh.create_time
ORDER BY sh.create_time DESC
""")
rows = cursor.fetchall()
print(f"\n 共找到 {len(rows)} 条:")
if rows:
print(" " + "-" * 110)
print(f" {'出库单号':<25} {'类型':<10} {'头':<5} {'尾':<5} {'任务':<5} {'任务类型':<8} {'组盘码':<15} {'上游单号':<20}")
print(" " + "-" * 110)
for row in rows:
print(f" {row[0] or '':<25} {row[1] or '':<10} {row[2] or '':<5} {row[3] or '':<5} {row[4] or '':<5} {row[5] or '':<8} {row[6] or '':<15} {row[7] or '':<20}")
conn.close()
print("\n" + "=" * 100)
print("分析完成！")
print("=" * 100)