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
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
|
class CustomerManagementSystem:
"""客户管理系统（SQLite版）"""
def __init__(self, db_file='customer_management.db'):
self.db_file = db_file
self.init_database()
def init_database(self):
"""初始化数据库和表"""
conn = sqlite3.connect(self.db_file)
cursor = conn.cursor()
create_table_sql = """
CREATE TABLE IF NOT EXISTS customers (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
company TEXT,
phone TEXT UNIQUE,
email TEXT,
level TEXT DEFAULT '普通',
total_amount REAL DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)
"""
cursor.execute(create_table_sql)
cursor.execute("""
CREATE TABLE IF NOT EXISTS interactions (
id INTEGER PRIMARY KEY AUTOINCREMENT,
customer_id INTEGER,
type TEXT,
content TEXT,
amount REAL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
)
""")
conn.commit()
conn.close()
def add_customer(self):
"""交互式添加客户"""
print("\n" + "=" * 40)
print("添加新客户")
print("=" * 40)
name = input("姓名: ").strip()
if not name:
print("❌ 姓名不能为空")
return
company = input("公司: ").strip()
phone = input("电话: ").strip()
email = input("邮箱: ").strip()
level = input("等级（VIP/重要/普通，默认普通）: ").strip() or '普通'
try:
amount = float(input("累计交易金额: ").strip() or 0)
except:
amount = 0
conn = sqlite3.connect(self.db_file)
cursor = conn.cursor()
try:
cursor.execute("""
INSERT INTO customers (name, company, phone, email, level, total_amount)
VALUES (?, ?, ?, ?, ?, ?)
""", (name, company, phone, email, level, amount))
conn.commit()
print(f"✅ 客户添加成功！ID: {cursor.lastrowid}")
except sqlite3.IntegrityError:
print("❌ 错误：电话号码已存在，请使用其他号码")
finally:
conn.close()
def search_customers(self):
"""高级查询"""
print("\n" + "=" * 40)
print("客户查询")
print("=" * 40)
keyword = input("输入搜索关键词（姓名/公司/电话）: ").strip()
conn = sqlite3.connect(self.db_file)
query = """
SELECT * FROM customers
WHERE name LIKE ? OR company LIKE ? OR phone LIKE ?
ORDER BY total_amount DESC
"""
keyword_pattern = f'%{keyword}%'
df = pd.read_sql(query, conn, params=[keyword_pattern, keyword_pattern, keyword_pattern])
conn.close()
if df.empty:
print("📭 未找到匹配的客户")
else:
print(f"\n🔍 找到 {len(df)} 位客户：")
print(df.to_string(index=False))
def record_interaction(self):
"""记录客户交互（订单、投诉、回访）"""
print("\n" + "=" * 40)
print("记录客户交互")
print("=" * 40)
customer_id = input("客户ID: ").strip()
interaction_type = input("交互类型（订单/投诉/回访/付款）: ").strip()
content = input("交互内容: ").strip()
try:
amount = float(input("涉及金额（无则回车）: ").strip() or 0)
except:
amount = 0
conn = sqlite3.connect(self.db_file)
cursor = conn.cursor()
cursor.execute("""
INSERT INTO interactions (customer_id, type, content, amount)
VALUES (?, ?, ?, ?)
""", (customer_id, interaction_type, content, amount))
if interaction_type == '订单':
cursor.execute("""
UPDATE customers
SET total_amount = total_amount + ?
WHERE id = ?
""", (amount, customer_id))
print(f"✅ 订单记录成功，客户累计交易已更新")
conn.commit()
conn.close()
print("✅ 交互记录已保存")
def generate_report(self):
"""生成客户分析报告"""
conn = sqlite3.connect(self.db_file)
print("\n" + "=" * 50)
print("客户分析报告")
print("=" * 50)
stats_df = pd.read_sql("""
SELECT
level as 客户等级,
COUNT(*) as 客户数量,
ROUND(AVG(total_amount), 2) as 平均交易额,
ROUND(SUM(total_amount), 2) as 总交易额
FROM customers
GROUP BY level
ORDER BY 总交易额 DESC
""", conn)
print("\n📊 客户等级分布：")
print(stats_df.to_string(index=False))
new_customers = pd.read_sql("""
SELECT name, company, created_at
FROM customers
WHERE created_at >= date('now', '-30 days')
ORDER BY created_at DESC
""", conn)
if not new_customers.empty:
print(f"\n🆕 本月新增客户（{len(new_customers)}人）：")
print(new_customers.to_string(index=False))
else:
print("\n🆕 本月暂无新客户")
top5 = pd.read_sql("""
SELECT name, company, total_amount, level
FROM customers
ORDER BY total_amount DESC
LIMIT 5
""", conn)
print("\n💎 高价值客户TOP5：")
print(top5.to_string(index=False))
interaction_stats = pd.read_sql("""
SELECT
type as 交互类型,
COUNT(*) as 次数,
ROUND(SUM(amount), 2) as 总金额
FROM interactions
WHERE created_at >= date('now', '-30 days')
GROUP BY type
ORDER BY 次数 DESC
""", conn)
if not interaction_stats.empty:
print("\n📞 本月交互统计：")
print(interaction_stats.to_string(index=False))
conn.close()
with pd.ExcelWriter('客户分析报告.xlsx', engine='openpyxl') as writer:
stats_df.to_excel(writer, sheet_name='客户统计', index=False)
if not new_customers.empty:
new_customers.to_excel(writer, sheet_name='新客户', index=False)
top5.to_excel(writer, sheet_name='TOP5客户', index=False)
if not interaction_stats.empty:
interaction_stats.to_excel(writer, sheet_name='交互统计', index=False)
print("\n✅ 报告已保存: 客户分析报告.xlsx")
def main():
"""主菜单"""
cms = CustomerManagementSystem()
print("=" * 55)
print("客户管理系统")
print("数据库版 - 数据安全可靠，查询快速")
print("=" * 55)
while True:
print("\n" + "=" * 45)
print("功能菜单")
print("=" * 45)
print("1. 添加客户")
print("2. 查询客户")
print("3. 记录客户交互")
print("4. 生成分析报告")
print("5. 退出")
print("=" * 45)
choice = input("请选择: ").strip()
if choice == "1":
cms.add_customer()
elif choice == "2":
cms.search_customers()
elif choice == "3":
cms.record_interaction()
elif choice == "4":
cms.generate_report()
elif choice == "5":
print("👋 感谢使用，数据已安全保存！")
break
else:
print("请输入1-5！")
if __name__ == "__main__":
main()
|