query_laundry_cost.py
4.08 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
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
查询绿纤明信店2025年12月毛巾总成本
"""
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 query_laundry_cost():
"""查询绿纤明信店2025年12月毛巾总成本"""
connection = None
try:
connection = pymysql.connect(**DB_CONFIG)
cursor = connection.cursor(pymysql.cursors.DictCursor)
# 门店ID：绿纤明信店
store_id = '1649328471923847187'
month_str = '202512' # 2025年12月
# 查询总成本
sql = """
SELECT
F_StoreId as 门店ID,
SUM(F_TotalPrice) as 毛巾总成本,
COUNT(*) as 记录数量,
MIN(COALESCE(F_SendTime, F_CreateTime)) as 最早记录时间,
MAX(COALESCE(F_SendTime, F_CreateTime)) as 最晚记录时间
FROM lq_laundry_flow
WHERE F_IsEffective = 1
AND F_FlowType = 0
AND F_StoreId = %s
AND DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') = %s
GROUP BY F_StoreId
"""
cursor.execute(sql, (store_id, month_str))
result = cursor.fetchone()
if result:
print("=" * 60)
print("绿纤明信店 2025年12月 毛巾成本统计")
print("=" * 60)
print(f"门店ID: {result['门店ID']}")
print(f"毛巾总成本: {result['毛巾总成本']:.2f} 元")
print(f"记录数量: {result['记录数量']} 条")
print(f"最早记录时间: {result['最早记录时间']}")
print(f"最晚记录时间: {result['最晚记录时间']}")
print("=" * 60)
# 查询详细信息
detail_sql = """
SELECT
F_Id as 记录ID,
F_BatchNumber as 批次号,
F_ProductType as 产品类型,
F_Quantity as 数量,
F_LaundryPrice as 清洗单价,
F_TotalPrice as 总费用,
F_SendTime as 送出时间,
F_CreateTime as 创建时间,
COALESCE(F_SendTime, F_CreateTime) as 统计时间
FROM lq_laundry_flow
WHERE F_IsEffective = 1
AND F_FlowType = 0
AND F_StoreId = %s
AND DATE_FORMAT(COALESCE(F_SendTime, F_CreateTime), '%%Y%%m') = %s
ORDER BY COALESCE(F_SendTime, F_CreateTime)
"""
cursor.execute(detail_sql, (store_id, month_str))
details = cursor.fetchall()
if details:
print(f"\n详细记录列表（共{len(details)}条）:")
print("-" * 100)
print(f"{'批次号':<20} {'产品类型':<10} {'数量':<8} {'单价':<10} {'总费用':<12} {'统计时间':<20}")
print("-" * 100)
for detail in details:
print(f"{detail['批次号'] or '':<20} "
f"{detail['产品类型'] or '':<10} "
f"{detail['数量']:<8} "
f"{detail['清洗单价']:<10.2f} "
f"{detail['总费用']:<12.2f} "
f"{str(detail['统计时间']):<20}")
print("-" * 100)
print(f"总计: {result['毛巾总成本']:.2f} 元")
else:
print(f"未找到绿纤明信店（{store_id}）2025年12月的毛巾送出记录")
return result
except Exception as e:
print(f"查询失败: {str(e)}")
import traceback
traceback.print_exc()
return None
finally:
if connection:
connection.close()
if __name__ == '__main__':
query_laundry_cost()