check_laundry_flow_time_issue.py
4.13 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
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
检查洗毛巾记录的时间字段差异问题
比较CreateTime和SendTime的差异
"""
import pymysql
from datetime import datetime
# 数据库配置
DB_CONFIG = {
'host': 'rm-2vccze142rc9a8f58bo.mysql.cn-chengdu.rds.aliyuncs.com',
'port': 3306,
'user': 'lvqiansql',
'password': 'LvQ1@n!20251211',
'database': 'lqerp_dev',
'charset': 'utf8mb4',
'connect_timeout': 60,
'read_timeout': 300,
'write_timeout': 300
}
def check_time_issue():
"""检查时间字段差异"""
connection = None
try:
connection = pymysql.connect(**DB_CONFIG)
cursor = connection.cursor(pymysql.cursors.DictCursor)
store_id = '1649328471923847187'
month_str = '202512'
# 查询所有相关记录
sql = """
SELECT
F_Id,
F_BatchNumber,
F_FlowType,
F_ProductType,
F_Quantity,
F_TotalPrice,
F_CreateTime,
F_SendTime,
COALESCE(F_SendTime, F_CreateTime) as StatTime,
DATE_FORMAT(F_CreateTime, '%%Y%%m') as CreateMonth,
DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') as StatMonth
FROM lq_laundry_flow
WHERE F_IsEffective = 1
AND F_FlowType = 0
AND F_StoreId = %s
AND (
DATE_FORMAT(F_CreateTime, '%%Y%%m') = %s
OR DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') = %s
)
ORDER BY COALESCE(F_SendTime, F_CreateTime)
"""
cursor.execute(sql, (store_id, month_str, month_str))
records = cursor.fetchall()
print("=" * 120)
print("洗毛巾记录时间字段差异检查")
print("=" * 120)
print(f"\n查询条件：门店ID={store_id}, 月份={month_str}")
print(f"\n共查询到 {len(records)} 条记录\n")
# 按CreateTime统计
create_time_records = [r for r in records if r['CreateMonth'] == month_str]
print(f"按CreateTime过滤（12月创建）: {len(create_time_records)} 条")
# 按StatTime统计（工资计算使用的逻辑）
stat_time_records = [r for r in records if r['StatMonth'] == month_str]
print(f"按StatTime过滤（12月统计）: {len(stat_time_records)} 条")
# 找出差异
create_time_ids = {r['F_Id'] for r in create_time_records}
stat_time_ids = {r['F_Id'] for r in stat_time_records}
diff_ids = stat_time_ids - create_time_ids
if diff_ids:
print(f"\n⚠️ 差异记录（StatTime在12月，但CreateTime不在12月）: {len(diff_ids)} 条")
print("-" * 120)
print(f"{'批次号':<20} {'产品类型':<10} {'CreateTime':<20} {'SendTime':<20} {'总费用':<10}")
print("-" * 120)
diff_total = 0
for r in records:
if r['F_Id'] in diff_ids:
print(f"{r['F_BatchNumber']:<20} "
f"{r['F_ProductType'] or '':<10} "
f"{str(r['F_CreateTime']):<20} "
f"{str(r['F_SendTime'] or ''):<20} "
f"{r['F_TotalPrice']:<10.2f}")
diff_total += r['F_TotalPrice']
print("-" * 120)
print(f"差异金额总计: {diff_total:.2f} 元")
# 汇总统计
create_total = sum(r['F_TotalPrice'] for r in create_time_records)
stat_total = sum(r['F_TotalPrice'] for r in stat_time_records)
print(f"\n汇总统计:")
print(f" 按CreateTime统计金额: {create_total:.2f} 元")
print(f" 按StatTime统计金额: {stat_total:.2f} 元")
print(f" 差异金额: {stat_total - create_total:.2f} 元")
return records
except Exception as e:
print(f"查询失败: {str(e)}")
import traceback
traceback.print_exc()
return None
finally:
if connection:
connection.close()
if __name__ == '__main__':
check_time_issue()