"""数据统计 CRUD""" from sqlalchemy.orm import Session from sqlalchemy import and_, func, desc, extract from typing import Optional, List, Dict, Any, Tuple from datetime import datetime, timedelta from decimal import Decimal from app.models.models import ( BizOrder, BizOrderItem, BizDeliveryTask, BizSettlement, BizRefund, BizPriceList, BizPriceListItem, SysUser, SysRegion, BasProduct ) class StatisticsCRUD: """数据统计CRUD操作""" @staticmethod def get_order_statistics( db: Session, region_id: str = None, start_date: str = None, end_date: str = None ) -> Dict[str, Any]: """获取订单统计数据""" query = db.query(BizOrder).filter(BizOrder.is_del == 0) if region_id: query = query.filter(BizOrder.region_id == region_id) if start_date: try: start_dt = datetime.strptime(start_date, "%Y-%m-%d") query = query.filter(BizOrder.create_at >= start_dt) except: pass if end_date: try: end_dt = datetime.strptime(end_date, "%Y-%m-%d") end_dt = end_dt.replace(hour=23, minute=59, second=59) query = query.filter(BizOrder.create_at <= end_dt) except: pass orders = query.all() total_orders = len(orders) pending_payment = sum(1 for o in orders if o.status == "pending_payment") pending_dispatch = sum(1 for o in orders if getattr(o, "delivery_status", None) == "pending_dispatch") delivering = sum(1 for o in orders if o.status == "delivering") completed = sum(1 for o in orders if o.status == "completed") cancelled = sum(1 for o in orders if o.status == "cancelled") total_amount = sum(float(o.total_amount or 0) for o in orders) paid_amount = sum(float(o.paid_amount or 0) for o in orders) refund_amount = sum(float(o.refund_amount or 0) for o in orders) return { "totalOrders": total_orders, "pendingPayment": pending_payment, "pendingDelivery": pending_dispatch, "delivering": delivering, "completed": completed, "cancelled": cancelled, "totalAmount": round(total_amount, 2), "paidAmount": round(paid_amount, 2), "refundAmount": round(refund_amount, 2) } @staticmethod def get_order_trend( db: Session, region_id: str = None, days: int = 7 ) -> List[Dict[str, Any]]: """获取订单趋势数据(按日统计)""" result = [] today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) for i in range(days - 1, -1, -1): day_start = today - timedelta(days=i) day_end = day_start + timedelta(days=1) query = db.query(BizOrder).filter( and_( BizOrder.is_del == 0, BizOrder.create_at >= day_start, BizOrder.create_at < day_end ) ) if region_id: query = query.filter(BizOrder.region_id == region_id) orders = query.all() order_count = len(orders) order_amount = sum(float(o.total_amount or 0) for o in orders) result.append({ "date": day_start.strftime("%Y-%m-%d"), "orderCount": order_count, "orderAmount": round(order_amount, 2) }) return result @staticmethod def get_financial_statistics( db: Session, region_id: str = None, start_date: str = None, end_date: str = None ) -> Dict[str, Any]: """获取财务统计数据""" order_query = db.query(BizOrder).filter(BizOrder.is_del == 0) settlement_query = db.query(BizSettlement).filter(BizSettlement.is_del == 0) refund_query = db.query(BizRefund).filter(BizRefund.is_del == 0) if region_id: order_query = order_query.filter(BizOrder.region_id == region_id) settlement_query = settlement_query.filter(BizSettlement.region_id == region_id) if start_date: try: start_dt = datetime.strptime(start_date, "%Y-%m-%d") order_query = order_query.filter(BizOrder.create_at >= start_dt) settlement_query = settlement_query.filter(BizSettlement.create_at >= start_dt) refund_query = refund_query.filter(BizRefund.create_at >= start_dt) except: pass if end_date: try: end_dt = datetime.strptime(end_date, "%Y-%m-%d") end_dt = end_dt.replace(hour=23, minute=59, second=59) order_query = order_query.filter(BizOrder.create_at <= end_dt) settlement_query = settlement_query.filter(BizSettlement.create_at <= end_dt) refund_query = refund_query.filter(BizRefund.create_at <= end_dt) except: pass orders = order_query.all() settlements = settlement_query.filter(BizSettlement.status == "confirmed").all() refunds = refund_query.filter(BizRefund.status == "completed").all() total_revenue = sum(float(o.total_amount or 0) for o in orders) total_received = sum(float(s.amount or 0) for s in settlements) total_refund = sum(float(r.refund_amount or 0) for r in refunds) net_revenue = total_received - total_refund return { "totalRevenue": round(total_revenue, 2), "totalReceived": round(total_received, 2), "totalRefund": round(total_refund, 2), "netRevenue": round(net_revenue, 2), "orderCount": len(orders), "settlementCount": len(settlements), "refundCount": len(refunds) } @staticmethod def get_financial_trend( db: Session, region_id: str = None, days: int = 7 ) -> List[Dict[str, Any]]: """获取财务趋势数据(按日统计)""" result = [] today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) for i in range(days - 1, -1, -1): day_start = today - timedelta(days=i) day_end = day_start + timedelta(days=1) settlement_query = db.query(BizSettlement).filter( and_( BizSettlement.is_del == 0, BizSettlement.status == "confirmed", BizSettlement.create_at >= day_start, BizSettlement.create_at < day_end ) ) refund_query = db.query(BizRefund).filter( and_( BizRefund.is_del == 0, BizRefund.status == "completed", BizRefund.create_at >= day_start, BizRefund.create_at < day_end ) ) if region_id: settlement_query = settlement_query.filter(BizSettlement.region_id == region_id) settlements = settlement_query.all() refunds = refund_query.all() received = sum(float(s.amount or 0) for s in settlements) refund = sum(float(r.refund_amount or 0) for r in refunds) result.append({ "date": day_start.strftime("%Y-%m-%d"), "received": round(received, 2), "refund": round(refund, 2), "net": round(received - refund, 2) }) return result @staticmethod def get_delivery_statistics( db: Session, region_id: str = None, start_date: str = None, end_date: str = None ) -> Dict[str, Any]: """获取配送统计数据""" query = db.query(BizDeliveryTask).filter(BizDeliveryTask.is_del == 0) if region_id: query = query.filter(BizDeliveryTask.region_id == region_id) if start_date: try: start_dt = datetime.strptime(start_date, "%Y-%m-%d") query = query.filter(BizDeliveryTask.create_at >= start_dt) except: pass if end_date: try: end_dt = datetime.strptime(end_date, "%Y-%m-%d") end_dt = end_dt.replace(hour=23, minute=59, second=59) query = query.filter(BizDeliveryTask.create_at <= end_dt) except: pass tasks = query.all() pending = sum(1 for t in tasks if t.status == "pending") picked_up = sum(1 for t in tasks if t.status == "picked_up") delivering = sum(1 for t in tasks if t.status == "delivering") completed = sum(1 for t in tasks if t.status == "completed") cancelled = sum(1 for t in tasks if t.status == "cancelled") return { "totalTasks": len(tasks), "pending": pending, "pickedUp": picked_up, "delivering": delivering, "completed": completed, "cancelled": cancelled } @staticmethod def get_product_statistics( db: Session, region_id: str = None, start_date: str = None, end_date: str = None, limit: int = 10 ) -> List[Dict[str, Any]]: """获取商品销售排行""" query = db.query( BizOrderItem.product_id, func.sum(BizOrderItem.quantity).label("total_quantity"), func.sum(BizOrderItem.amount).label("total_amount") ).join( BizOrder, and_( BizOrder.id == BizOrderItem.order_id, BizOrder.is_del == 0, BizOrder.status.in_(["completed", "delivering", "pending_confirm"]) ) ).filter( BizOrderItem.is_del == 0 ) if region_id: query = query.filter(BizOrder.region_id == region_id) if start_date: try: start_dt = datetime.strptime(start_date, "%Y-%m-%d") query = query.filter(BizOrder.create_at >= start_dt) except: pass if end_date: try: end_dt = datetime.strptime(end_date, "%Y-%m-%d") end_dt = end_dt.replace(hour=23, minute=59, second=59) query = query.filter(BizOrder.create_at <= end_dt) except: pass results = query.group_by(BizOrderItem.product_id).order_by( desc("total_amount") ).limit(limit).all() product_stats = [] for r in results: product = db.query(BasProduct).filter( and_(BasProduct.id == r.product_id, BasProduct.is_del == 0) ).first() product_stats.append({ "productId": r.product_id, "productName": product.name if product else None, "totalQuantity": float(r.total_quantity or 0), "totalAmount": float(r.total_amount or 0) }) return product_stats @staticmethod def get_customer_statistics( db: Session, region_id: str = None, start_date: str = None, end_date: str = None, limit: int = 10 ) -> List[Dict[str, Any]]: """获取客户订单排行""" query = db.query( BizOrder.client_id, func.count(BizOrder.id).label("order_count"), func.sum(BizOrder.total_amount).label("total_amount") ).filter( and_( BizOrder.is_del == 0, BizOrder.status.in_(["completed", "delivering", "pending_confirm", "pending_payment"]) ) ) if region_id: query = query.filter(BizOrder.region_id == region_id) if start_date: try: start_dt = datetime.strptime(start_date, "%Y-%m-%d") query = query.filter(BizOrder.create_at >= start_dt) except: pass if end_date: try: end_dt = datetime.strptime(end_date, "%Y-%m-%d") end_dt = end_dt.replace(hour=23, minute=59, second=59) query = query.filter(BizOrder.create_at <= end_dt) except: pass results = query.group_by(BizOrder.client_id).order_by( desc("total_amount") ).limit(limit).all() customer_stats = [] for r in results: customer = db.query(SysUser).filter( and_(SysUser.id == r.client_id, SysUser.is_del == 0) ).first() customer_stats.append({ "customerId": r.client_id, "customerName": customer.real_name if customer else None, "customerPhone": customer.phone if customer else None, "orderCount": r.order_count, "totalAmount": float(r.total_amount or 0) }) return customer_stats @staticmethod def get_dashboard_summary( db: Session, region_id: str = None ) -> Dict[str, Any]: """获取仪表盘汇总数据""" today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) month_start = today.replace(day=1) order_query = db.query(BizOrder).filter( and_(BizOrder.is_del == 0, BizOrder.create_at >= month_start) ) if region_id: order_query = order_query.filter(BizOrder.region_id == region_id) month_orders = order_query.all() today_orders = [o for o in month_orders if o.create_at >= today] pending_delivery_count = db.query(BizOrder).filter( and_( BizOrder.is_del == 0, BizOrder.delivery_status == "pending_dispatch" ) ) if region_id: pending_delivery_count = pending_delivery_count.filter(BizOrder.region_id == region_id) return { "todayOrderCount": len(today_orders), "todayOrderAmount": round(sum(float(o.total_amount or 0) for o in today_orders), 2), "monthOrderCount": len(month_orders), "monthOrderAmount": round(sum(float(o.total_amount or 0) for o in month_orders), 2), "pendingDeliveryCount": pending_delivery_count.count(), "completedOrderCount": sum(1 for o in month_orders if o.status == "completed"), "activePriceListCount": db.query(BizPriceList).filter( and_(BizPriceList.is_del == 0, BizPriceList.status == "active") ).count(), "customerCount": db.query(SysUser).filter( and_(SysUser.is_del == 0, SysUser.role == "client") ).count(), "supplierCount": db.query(SysUser).filter( and_(SysUser.is_del == 0, SysUser.role == "supplier") ).count() } statistics_crud = StatisticsCRUD()