query_prod.py
2.06 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
import pymssql
conn = pymssql.connect(
server='192.168.1.168',
port=1433,
database='wms4_global',
user='sa',
password='HHrobot123.'
)
cursor = conn.cursor(as_dict=True)
# 统计：头表status=50但详情不一致的数据（排除BMR/DBR/QTR等类型）
sql = """
SELECT
COUNT(*) as total_count,
SUM(CASE WHEN d.status IS NULL THEN 1 ELSE 0 END) as detail_null_count,
SUM(CASE WHEN d.status = 0 THEN 1 ELSE 0 END) as detail_status_0,
SUM(CASE WHEN d.status = 20 THEN 1 ELSE 0 END) as detail_status_20,
SUM(CASE WHEN d.status = 50 THEN 1 ELSE 0 END) as detail_status_50,
SUM(CASE WHEN h.status = 50 AND (d.status IS NULL OR d.status != 50) THEN 1 ELSE 0 END) as will_update_count
FROM receipt_container_header h
INNER JOIN receipt_container_detail d ON d.receipt_container_id = h.id
INNER JOIN receipt_header r ON r.id = d.receipt_id
WHERE
h.status = 50
AND NOT (h.status = 50 AND d.status = 50)
AND NOT (h.status = 0 AND d.status = 0)
AND r.type NOT IN ('BMR', 'DBR', 'QTR', 'SCR', 'CPR', 'BCPR', 'YCLR', 'QTRKD')
"""
cursor.execute(sql)
row = cursor.fetchone()
print('=== 影响统计（头表status=50且需要同步到详情的）===')
for k, v in row.items():
print(f' {k}: {v}')
# 样例数据
cursor.execute("""
SELECT TOP 5
h.id AS header_id,
h.status AS header_status,
d.id AS detail_id,
d.status AS detail_status,
d.receipt_id,
r.type AS receipt_type,
r.code AS receipt_code
FROM receipt_container_header h
INNER JOIN receipt_container_detail d ON d.receipt_container_id = h.id
INNER JOIN receipt_header r ON r.id = d.receipt_id
WHERE
h.status = 50
AND NOT (h.status = 50 AND d.status = 50)
AND NOT (h.status = 0 AND d.status = 0)
AND r.type NOT IN ('BMR', 'DBR', 'QTR', 'SCR', 'CPR', 'BCPR', 'YCLR', 'QTRKD')
""")
print()
print('=== 样例数据 ===')
for row in cursor:
print(f' header_id={row["header_id"]} detail_id={row["detail_id"]} header_status={row["header_status"]} detail_status={row["detail_status"]} type={row["type"]} receipt={row["receipt_code"]}')
conn.close()