prod_analysis.py
7.72 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
import pyodbc
# 生产环境数据库
conn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};'
'SERVER=192.168.1.168,1433;'
'DATABASE=wms4_global;'
'UID=sa;PWD=HHrobot123.'
)
cursor = conn.cursor()
print("=" * 80)
print("生产环境 ERP推送问题单据分析")
print("=" * 80)
# 1. 检查数据库版本确认是生产库
cursor.execute("SELECT @@VERSION")
row = cursor.fetchone()
print(f"\n数据库: {row[0][:50]}...")
# 2. 查看所有入库单状态分布
print("\n\n【一、入库单状态分布】")
print("-" * 60)
cursor.execute("""
SELECT
COUNT(*) as total,
SUM(CASE WHEN head_status = 0 THEN 1 ELSE 0 END) as 新建,
SUM(CASE WHEN head_status = 100 THEN 1 ELSE 0 END) as 已提交,
SUM(CASE WHEN head_status = 200 THEN 1 ELSE 0 END) as 已确认,
SUM(CASE WHEN head_status = 300 THEN 1 ELSE 0 END) as 配货中,
SUM(CASE WHEN head_status = 800 THEN 1 ELSE 0 END) as 已回传,
SUM(CASE WHEN head_status = 900 THEN 1 ELSE 0 END) as 已完成
FROM receipt_header
WHERE del_flag = 0
""")
row = cursor.fetchone()
print(f"入库单总数: {row[0]}")
print(f" 新建(0): {row[1]}")
print(f" 已提交(100): {row[2]}")
print(f" 已确认(200): {row[3]}")
print(f" 配货中(300): {row[4]}")
print(f" 已回传(800): {row[5]}")
print(f" 已完成(900): {row[6]}")
# 3. 查看所有出库单状态分布
print("\n\n【二、出库单状态分布】")
print("-" * 60)
cursor.execute("""
SELECT
COUNT(*) as total,
SUM(CASE WHEN head_status = 0 THEN 1 ELSE 0 END) as 新建,
SUM(CASE WHEN head_status = 100 THEN 1 ELSE 0 END) as 已提交,
SUM(CASE WHEN head_status = 200 THEN 1 ELSE 0 END) as 已确认,
SUM(CASE WHEN head_status = 300 THEN 1 ELSE 0 END) as 配货中,
SUM(CASE WHEN head_status = 800 THEN 1 ELSE 0 END) as 已回传,
SUM(CASE WHEN head_status = 900 THEN 1 ELSE 0 END) as 已完成
FROM shipment_header
WHERE del_flag = 0
""")
row = cursor.fetchone()
print(f"出库单总数: {row[0]}")
print(f" 新建(0): {row[1]}")
print(f" 已提交(100): {row[2]}")
print(f" 已确认(200): {row[3]}")
print(f" 配货中(300): {row[4]}")
print(f" 已回传(800): {row[5]}")
print(f" 已完成(900): {row[6]}")
# 4. 问题入库单：不是新建(0)，不是已回传(800)，不是已完成(900)，且有任务但任务状态不是50
print("\n\n【三、问题入库单分析】")
print("-" * 60)
cursor.execute("""
SELECT COUNT(*) FROM receipt_header
WHERE del_flag = 0
AND head_status NOT IN (0, 800, 900)
AND bill_type NOT IN ('DBR', 'dbr', '倒扣料类型') -- 排除调拨入库和倒扣料
""")
total_problem_receipt = cursor.fetchone()[0]
print(f"问题入库单总数（排除已回传和调拨入库）: {total_problem_receipt}")
# 5. 详细列出问题入库单
print("\n问题入库单详情:")
print(f"{'入库单号':<30} {'类型':<10} {'头状态':<8} {'尾状态':<8} {'任务状态':<10} {'组盘码':<25} {'上游单号':<30}")
print("-" * 121)
cursor.execute("""
SELECT TOP 100
rh.bill_no,
rh.bill_type,
rh.head_status,
rh.tail_status,
ISNULL(CAST(th.status AS VARCHAR), '无任务') as task_status,
STUFF((
SELECT ',' + ch.container_code
FROM task_detail td
JOIN container_head ch ON td.container_id = ch.id
WHERE td.receipt_id = rh.id
FOR XML PATH('')
), 1, 1, '') as container_codes,
rh.source_no
FROM receipt_header rh
LEFT JOIN task_header th ON rh.id = th.receipt_container_header_id
WHERE rh.del_flag = 0
AND rh.head_status NOT IN (0, 800, 900)
AND rh.bill_type NOT IN ('DBR', 'dbr')
ORDER BY rh.create_time DESC
""")
for row in cursor.fetchall():
bill_no = row[0] or ''
bill_type = row[1] or ''
head_status = row[2] if row[2] is not None else '-'
tail_status = row[3] if row[3] is not None else '-'
task_status = row[4] or '-'
containers = (row[5] or '')[:20]
source_no = (row[6] or '')[:25]
print(f"{bill_no:<30} {bill_type:<10} {str(head_status):<8} {str(tail_status):<8} {task_status:<10} {containers:<25} {source_no:<30}")
# 6. 问题出库单
print("\n\n【四、问题出库单分析】")
print("-" * 60)
cursor.execute("""
SELECT COUNT(*) FROM shipment_header
WHERE del_flag = 0
AND head_status NOT IN (0, 800, 900)
AND bill_type NOT IN ('DBR', 'dbr')
""")
total_problem_shipment = cursor.fetchone()[0]
print(f"问题出库单总数（排除已回传和调拨出库）: {total_problem_shipment}")
print("\n问题出库单详情:")
print(f"{'出库单号':<30} {'类型':<10} {'头状态':<8} {'尾状态':<8} {'任务状态':<10} {'组盘码':<25} {'上游单号':<30}")
print("-" * 121)
cursor.execute("""
SELECT TOP 100
sh.bill_no,
sh.bill_type,
sh.head_status,
sh.tail_status,
ISNULL(CAST(th.status AS VARCHAR), '无任务') as task_status,
STUFF((
SELECT ',' + ch.container_code
FROM task_detail td
JOIN container_head ch ON td.container_id = ch.id
WHERE td.shipment_id = sh.id
FOR XML PATH('')
), 1, 1, '') as container_codes,
sh.source_no
FROM shipment_header sh
LEFT JOIN task_header th ON sh.id = th.shipment_container_header_id
WHERE sh.del_flag = 0
AND sh.head_status NOT IN (0, 800, 900)
AND sh.bill_type NOT IN ('DBR', 'dbr')
ORDER BY sh.create_time DESC
""")
for row in cursor.fetchall():
bill_no = row[0] or ''
bill_type = row[1] or ''
head_status = row[2] if row[2] is not None else '-'
tail_status = row[3] if row[3] is not None else '-'
task_status = row[4] or '-'
containers = (row[5] or '')[:20]
source_no = (row[6] or '')[:25]
print(f"{bill_no:<30} {bill_type:<10} {str(head_status):<8} {str(tail_status):<8} {task_status:<10} {containers:<25} {source_no:<30}")
# 7. 组盘头表 inout_status 分析
print("\n\n【五、组盘头表 inout_status 分析】")
print("-" * 60)
cursor.execute("""
SELECT
inout_status,
COUNT(*) as cnt
FROM container_head
WHERE del_flag = 0
GROUP BY inout_status
ORDER BY inout_status
""")
print("组盘状态分布:")
for row in cursor.fetchall():
status = row[0] if row[0] is not None else 'NULL'
cnt = row[1]
print(f" 状态 {status}: {cnt} 条")
# 8. 组盘头表状态不是50的详细
print("\n\n【六、组盘状态不是50的单据（可能有问题）】")
print("-" * 60)
cursor.execute("""
SELECT
COUNT(*)
FROM container_head
WHERE del_flag = 0
AND inout_status IS NOT NULL
AND inout_status != 50
AND inout_status != 0
""")
not_50_count = cursor.fetchone()[0]
print(f"组盘状态不是50的单据数: {not_50_count}")
print("\n详细列表:")
print(f"{'组盘码':<20} {'类型':<10} {'inout_status':<12} {'关联入库单':<30} {'关联出库单':<30}")
print("-" * 102)
cursor.execute("""
SELECT TOP 50
ch.container_code,
ch.bill_type,
ch.inout_status,
ISNULL(rh.bill_no, '-') as receipt_bill,
ISNULL(sh.bill_no, '-') as shipment_bill
FROM container_head ch
LEFT JOIN receipt_header rh ON ch.id = rh.container_id
LEFT JOIN shipment_header sh ON ch.id = sh.container_id
WHERE ch.del_flag = 0
AND ch.inout_status IS NOT NULL
AND ch.inout_status NOT IN (50, 0, 800, 900)
ORDER BY ch.create_time DESC
""")
for row in cursor.fetchall():
code = row[0] or ''
bill_type = row[1] or ''
status = row[2] if row[2] is not None else '-'
receipt = (row[3] or '-')[:25]
shipment = (row[4] or '-')[:25]
print(f"{code:<20} {bill_type:<10} {str(status):<12} {receipt:<30} {shipment:<30}")
conn.close()
print("\n\n分析完成！")