prod_full_analysis.py
5.64 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
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. 查看所有表
print("\n【数据库表列表】")
cursor.execute("""
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_NAME
""")
tables = [row[0] for row in cursor.fetchall()]
print(f"共 {len(tables)} 张表")
# 2. 查看入库单状态分布
print("\n\n【一、入库单状态分布】")
print("-" * 60)
cursor.execute("""
SELECT
COUNT(*) as total,
SUM(CASE WHEN first_status = 0 THEN 1 ELSE 0 END) as 新建,
SUM(CASE WHEN first_status = 50 THEN 1 ELSE 0 END) as 组盘中,
SUM(CASE WHEN first_status = 100 THEN 1 ELSE 0 END) as 已提交,
SUM(CASE WHEN first_status = 200 THEN 1 ELSE 0 END) as 已确认,
SUM(CASE WHEN first_status = 800 THEN 1 ELSE 0 END) as 已回传,
SUM(CASE WHEN first_status = 900 THEN 1 ELSE 0 END) as 已完成
FROM receipt_header
""")
row = cursor.fetchone()
print(f"入库单总数: {row[0]}")
print(f" 新建(0): {row[1]}")
print(f" 组盘中(50): {row[2]}")
print(f" 已提交(100): {row[3]}")
print(f" 已确认(200): {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 first_status = 0 THEN 1 ELSE 0 END) as 新建,
SUM(CASE WHEN first_status = 50 THEN 1 ELSE 0 END) as 组盘中,
SUM(CASE WHEN first_status = 100 THEN 1 ELSE 0 END) as 已提交,
SUM(CASE WHEN first_status = 200 THEN 1 ELSE 0 END) as 已确认,
SUM(CASE WHEN first_status = 800 THEN 1 ELSE 0 END) as 已回传,
SUM(CASE WHEN first_status = 900 THEN 1 ELSE 0 END) as 已完成
FROM shipment_header
""")
row = cursor.fetchone()
print(f"出库单总数: {row[0]}")
print(f" 新建(0): {row[1]}")
print(f" 组盘中(50): {row[2]}")
print(f" 已提交(100): {row[3]}")
print(f" 已确认(200): {row[4]}")
print(f" 已回传(800): {row[5]}")
print(f" 已完成(900): {row[6]}")
# 4. 问题入库单分析
print("\n\n【三、问题入库单分析】")
print("-" * 60)
print("排除条件：调拨入库(DBR/dbr)、倒扣料、已回传(800)、已完成(900)、新建(0)")
# 按类型统计
cursor.execute("""
SELECT
type,
COUNT(*) as cnt
FROM receipt_header
WHERE first_status NOT IN (0, 800, 900)
AND type NOT IN ('DBR', 'dbr')
GROUP BY type
ORDER BY cnt DESC
""")
print("\n问题入库单按类型统计:")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]} 条")
# 详细问题入库单
print("\n问题入库单详情（前50条）:")
print(f"{'入库单号':<35} {'类型':<12} {'头状态':<10} {'尾状态':<10} {'创建时间':<20}")
print("-" * 87)
cursor.execute("""
SELECT TOP 50
code,
type,
first_status,
last_status,
CONVERT(VARCHAR, create_time, 120) as create_time
FROM receipt_header
WHERE first_status NOT IN (0, 800, 900)
AND type NOT IN ('DBR', 'dbr')
ORDER BY create_time DESC
""")
for row in cursor.fetchall():
code = row[0] or ''
bill_type = row[1] or ''
first = row[2] if row[2] is not None else '-'
last = row[3] if row[3] is not None else '-'
create_time = row[4] or ''
print(f"{code:<35} {bill_type:<12} {str(first):<10} {str(last):<10} {create_time:<20}")
# 5. 问题出库单分析
print("\n\n【四、问题出库单分析】")
print("-" * 60)
cursor.execute("""
SELECT
type,
COUNT(*) as cnt
FROM shipment_header
WHERE first_status NOT IN (0, 800, 900)
AND type NOT IN ('DBR', 'dbr')
GROUP BY type
ORDER BY cnt DESC
""")
print("\n问题出库单按类型统计:")
for row in cursor.fetchall():
print(f" {row[0]}: {row[1]} 条")
print("\n问题出库单详情（前50条）:")
print(f"{'出库单号':<35} {'类型':<12} {'头状态':<10} {'尾状态':<10} {'创建时间':<20}")
print("-" * 87)
cursor.execute("""
SELECT TOP 50
code,
type,
first_status,
last_status,
CONVERT(VARCHAR, create_time, 120) as create_time
FROM shipment_header
WHERE first_status NOT IN (0, 800, 900)
AND type NOT IN ('DBR', 'dbr')
ORDER BY create_time DESC
""")
for row in cursor.fetchall():
code = row[0] or ''
bill_type = row[1] or ''
first = row[2] if row[2] is not None else '-'
last = row[3] if row[3] is not None else '-'
create_time = row[4] or ''
print(f"{code:<35} {bill_type:<12} {str(first):<10} {str(last):<10} {create_time:<20}")
# 6. 查看组盘相关表
print("\n\n【五、组盘相关表分析】")
print("-" * 60)
# 查找组盘相关表
inout_tables = [t for t in tables if 'inout' in t.lower() or 'container' in t.lower() or 'group' in t.lower() or '组盘' in t]
print(f"组盘相关表: {inout_tables}")
for table in inout_tables[:5]:
print(f"\n表 {table} 结构:")
cursor.execute(f"""
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '{table}'
ORDER BY ORDINAL_POSITION
""")
cols = [(row[0], row[1]) for row in cursor.fetchall()]
for col in cols:
if 'status' in col[0].lower() or 'inout' in col[0].lower():
print(f" *** {col[0]}: {col[1]}")
else:
print(f" {col[0]}: {col[1]}")
conn.close()
print("\n\n分析完成！")