#!/usr/bin/env python3 """V2.0 饮料机（仙草甘茶）六月销售报告""" import subprocess, json, sys, os from datetime import datetime, timezone, timedelta from collections import defaultdict CST = timezone(timedelta(hours=8)) BASE = 'https://new.baiguoyu888.tech/api' COOKIE_JAR = '/tmp/v2_cookies.txt' OUTPUT = '/mnt/c/Users/kingw/Desktop/饮料机销售报告_2026年6月.xlsx' BEVERAGE_KEYWORDS = ['仙草甘茶', '凉茶'] def run_curl(url, method='GET', data=None, auth=False): """Run curl with cookie jar.""" cmd = ['curl', '-s', '-b', COOKIE_JAR] if auth: cmd += ['-c', COOKIE_JAR] cmd += ['-H', 'client-type: admin'] if method == 'POST' and data: cmd += ['-X', 'POST', '-H', 'Content-Type: application/json', '-d', data] cmd.append(url) r = subprocess.run(cmd, capture_output=True, text=True, timeout=60) if r.returncode != 0 or not r.stdout.strip(): return None try: return json.loads(r.stdout) except: return None # Step 1: Login V2.0 print('[1/5] 登录 V2.0...') login = run_curl(f'{BASE}/auth/admin/login', 'POST', '{"username":"13020181941","password":"888888"}', auth=True) if not login or login.get('code') != 2000: print('登录失败:', login) sys.exit(1) token = login['data']['token'] print(f' 登录成功') # Step 2: Fetch all devices print('[2/5] 拉取设备列表...') devices_resp = run_curl(f'{BASE}/device') if not devices_resp: print('拉取设备失败') sys.exit(1) devices_raw = devices_resp if isinstance(devices_resp, list) else devices_resp.get('data', []) devices = devices_raw if isinstance(devices_raw, list) else [] print(f' 共 {len(devices)} 台设备') # Build device map device_map = {} for d in devices: code = d.get('deviceNum', '') device_map[code] = { 'name': d.get('deviceName', '?'), 'code': code, 'type': d.get('type', '?'), 'state': d.get('state', '?'), 'create_time': d.get('createTime', ''), 'address': d.get('addressVO', {}).get('name', ''), } # Step 3: Fetch stock to identify beverage machines print('[3/5] 拉取库存数据，识别饮料机...') stock_resp = run_curl(f'{BASE}/device/stock') if not stock_resp: print('拉取库存失败') sys.exit(1) stock_raw = stock_resp.get('data', []) if isinstance(stock_resp, dict) else stock_resp stock_items = stock_raw if isinstance(stock_raw, list) else [] print(f' 库存记录: {len(stock_items)} 条') beverage_codes = set() beverage_products = set() for item in stock_items: gi = item.get('goodInfo') or {} gname = gi.get('name', '') or '' if any(kw in gname for kw in BEVERAGE_KEYWORDS): di = item.get('deviceInfo') or {} code = di.get('deviceNum', '') if code: beverage_codes.add(code) beverage_products.add(gname) print(f' 饮料产品: {beverage_products}') print(f' 饮料机设备: {len(beverage_codes)} 台') # Step 4: Fetch June orders print('[4/5] 拉取6月订单...') start = '2026-06-01%2000%3A00%3A00' end = '2026-06-17%2023%3A59%3A59' orders_url = f'{BASE}/order/list?page=1&size=10000&startTime={start}&endTime={end}' orders_resp = run_curl(orders_url) if not orders_resp: print('拉取订单失败') sys.exit(1) orders_data = orders_resp.get('data', {}) all_orders = orders_data.get('records', []) print(f' 共 {len(all_orders)} 条订单 (total={orders_data.get("total", "?")})') # Step 5: Aggregate by beverage device print('[5/5] 聚合饮料机销售数据...') # Per device: total amount, products set, first/last order date, order count device_sales = defaultdict(lambda: {'amount': 0.0, 'products': set(), 'orders': 0, 'first_date': None, 'last_date': None}) for order in all_orders: if order.get('state') != 'DONE': continue code = order.get('deviceNum', '') if code not in beverage_codes: continue ds = device_sales[code] ds['amount'] += float(order.get('amountMoney') or 0) ds['orders'] += 1 ct = order.get('createTime', '') if ct: try: dt = datetime.strptime(ct, '%Y-%m-%d %H:%M:%S') if ds['first_date'] is None or dt < ds['first_date']: ds['first_date'] = dt if ds['last_date'] is None or dt > ds['last_date']: ds['last_date'] = dt except: pass for detail in order.get('details', []): gname = detail.get('goodName', '') or '' if any(kw in gname for kw in BEVERAGE_KEYWORDS): ds['products'].add(gname) # Build result list results = [] for code in beverage_codes: dev = device_map.get(code, {'name': code, 'code': code, 'type': '?', 'state': '?', 'create_time': ''}) ds = device_sales.get(code, {'amount': 0.0, 'products': set(), 'orders': 0, 'first_date': None, 'last_date': None}) # Install date: first order date, fallback to createTime if ds['first_date']: install_date = ds['first_date'].strftime('%Y-%m-%d') elif dev.get('create_time'): try: install_date = dev['create_time'][:10] except: install_date = '未知' else: install_date = '未知' results.append({ 'name': dev['name'], 'code': code, 'type': dev['type'], 'state': dev['state'], 'install_date': install_date, 'products_count': len(ds['products']), 'products': ', '.join(sorted(ds['products'])), 'amount': round(ds['amount'], 2), 'orders': ds['orders'], 'first_order': ds['first_date'].strftime('%Y-%m-%d') if ds['first_date'] else '本月无销售', 'last_order': ds['last_date'].strftime('%Y-%m-%d') if ds['last_date'] else '', }) # Sort by sales amount descending results.sort(key=lambda x: x['amount'], reverse=True) # Generate Excel try: import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers from openpyxl.utils import get_column_letter except ImportError: subprocess.run([sys.executable, '-m', 'pip', 'install', 'openpyxl', '-q'], check=True) import openpyxl from openpyxl.styles import Font, PatternFill, Alignment, Border, Side, numbers from openpyxl.utils import get_column_letter wb = openpyxl.Workbook() ws = wb.active ws.title = '饮料机销售' # Headers headers = ['排名', '设备名称', '设备编号', '设备类型', '在线状态', '安装/首单日期', '销售产品数(款)', '销售产品', '销售额(元)', '订单数', '首单日期', '末单日期'] # Styles header_fill = PatternFill(start_color='1F4E79', end_color='1F4E79', fill_type='solid') header_font = Font(name='微软雅黑', bold=True, color='FFFFFF', size=11) data_font = Font(name='微软雅黑', size=10) money_font = Font(name='微软雅黑', size=10, bold=True) title_font = Font(name='微软雅黑', bold=True, size=14) thin_border = Border( left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) alt_fill = PatternFill(start_color='F2F7FB', end_color='F2F7FB', fill_type='solid') no_sale_fill = PatternFill(start_color='FFF2CC', end_color='FFF2CC', fill_type='solid') top_fill = PatternFill(start_color='E2EFDA', end_color='E2EFDA', fill_type='solid') # Title row ws.merge_cells('A1:L1') ws['A1'] = f'百果遇 V2.0 饮料机（仙草甘茶）销售报告 — 2026年6月（截至17日）' ws['A1'].font = title_font ws['A1'].alignment = Alignment(horizontal='center', vertical='center') ws.row_dimensions[1].height = 30 # Summary row total_amount = sum(r['amount'] for r in results) total_devices = len(results) with_sales = sum(1 for r in results if r['amount'] > 0) without_sales = total_devices - with_sales ws.merge_cells('A2:L2') ws['A2'] = f'饮料机总数：{total_devices}台 | 有销售：{with_sales}台 | 无销售：{without_sales}台 | 总销售额：¥{total_amount:,.2f} | 产品款数：{len(beverage_products)}' ws['A2'].font = Font(name='微软雅黑', size=10, color='333333') ws['A2'].alignment = Alignment(horizontal='center') ws.row_dimensions[2].height = 22 # Headers in row 3 for col, h in enumerate(headers, 1): cell = ws.cell(row=3, column=col, value=h) cell.fill = header_fill cell.font = header_font cell.alignment = Alignment(horizontal='center', vertical='center', wrap_text=True) cell.border = thin_border ws.row_dimensions[3].height = 25 # Data rows for i, r in enumerate(results): row = i + 4 has_sales = r['amount'] > 0 values = [ i + 1, r['name'], r['code'], r['type'], r['state'], r['install_date'], r['products_count'], r['products'], r['amount'], r['orders'], r['first_order'], r['last_order'] ] for col, val in enumerate(values, 1): cell = ws.cell(row=row, column=col, value=val) cell.font = data_font cell.border = thin_border cell.alignment = Alignment(vertical='center', wrap_text=(col == 8)) # Alternate row color if i % 2 == 1: cell.fill = alt_fill # Highlight top 10 if i < 10 and has_sales: if col == 9: # amount cell.font = money_font if col == 2: cell.font = Font(name='微软雅黑', size=10, bold=True) # Highlight no sales devices if not has_sales: cell.fill = no_sale_fill # Money format ws.cell(row=row, column=9).number_format = '#,##0.00' ws.cell(row=row, column=9).font = Font(name='微软雅黑', size=10, bold=True) if has_sales else data_font ws.row_dimensions[row].height = 20 # Column widths widths = [6, 35, 28, 8, 8, 15, 14, 50, 14, 8, 14, 14] for i, w in enumerate(widths, 1): ws.column_dimensions[get_column_letter(i)].width = w # Freeze header ws.freeze_panes = 'A4' # Auto filter ws.auto_filter.ref = f'A3:L{len(results) + 3}' # ---- Sheet 2: Summary ---- ws2 = wb.create_sheet('汇总统计') ws2.merge_cells('A1:C1') ws2['A1'] = '饮料机销售汇总' ws2['A1'].font = title_font summary_data = [ ('饮料机总数', total_devices), ('饮料产品款数', len(beverage_products)), ('产品列表', ', '.join(sorted(beverage_products))), ('有销售设备', with_sales), ('无销售设备', without_sales), ('总销售额', f'¥{total_amount:,.2f}'), ('总订单数', sum(r['orders'] for r in results)), ('平均单台销售额', f'¥{total_amount/with_sales:,.2f}' if with_sales > 0 else '¥0'), ('TOP1', f"{results[0]['name']} — ¥{results[0]['amount']:,.2f}" if results else ''), ('数据截止', '2026-06-17'), ] for i, (label, value) in enumerate(summary_data, 2): ws2.cell(row=i, column=1, value=label).font = Font(name='微软雅黑', bold=True, size=10) ws2.cell(row=i, column=2, value=value).font = Font(name='微软雅黑', size=10) ws2.row_dimensions[i].height = 20 ws2.column_dimensions['A'].width = 18 ws2.column_dimensions['B'].width = 50 # Save wb.save(OUTPUT) print(f'\n✅ Excel已保存到桌面: {OUTPUT}') print(f' 饮料机: {total_devices}台 | 有销售: {with_sales}台 | 无销售: {without_sales}台') print(f' 总销售额: ¥{total_amount:,.2f}') print(f' 总订单: {sum(r["orders"] for r in results)}') # Print top 20 for terminal preview print('\n📊 TOP 20 饮料机销售:') print(f'{"排名":<4} {"设备名称":<30} {"款数":<6} {"销售额":>10} {"订单":>6}') print('-' * 62) for i, r in enumerate(results[:20]): name = r['name'][:28] print(f'{i+1:<4} {name:<30} {r["products_count"]:<6} ¥{r["amount"]:>9,.2f} {r["orders"]:>6}') if len(results) > 20: print(f'... 还有 {len(results)-20} 台（详见Excel）')