"""数据统计 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, BizInquiry, BizQuotation, BizDeliveryWarning, ) 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, include_inactive_regions: bool = False, ) -> List[Dict[str, Any]]: """获取订单趋势数据（按日统计）""" result = [] today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True] 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) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.create_at >= day_start, BizOrder.create_at < day_end, SysRegion.is_del == 0, *region_active_clause, ) ) ) 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_today_order_stats_by_region( db: Session, region_id: str = None, include_inactive_regions: bool = False, ) -> List[Dict[str, Any]]: """今日各区域营业额(元)、订单笔数；列出未删除的区域，无单则为 0。""" today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) day_end = today + timedelta(days=1) region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True] reg_q = db.query(SysRegion).filter( SysRegion.is_del == 0, ) if region_id: reg_q = reg_q.filter(SysRegion.id == region_id) regions = reg_q.order_by(SysRegion.sort_order, SysRegion.name).all() agg_q = ( db.query( BizOrder.region_id, func.count(BizOrder.id).label("order_count"), func.coalesce(func.sum(BizOrder.total_amount), 0).label("order_amount"), ) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( BizOrder.is_del == 0, BizOrder.create_at >= today, BizOrder.create_at < day_end, SysRegion.is_del == 0, *region_active_clause, ) .group_by(BizOrder.region_id) ) if region_id: agg_q = agg_q.filter(BizOrder.region_id == region_id) agg_map: Dict[str, Any] = {r.region_id: r for r in agg_q.all() if r.region_id} out: List[Dict[str, Any]] = [] for reg in regions: row = agg_map.get(reg.id) cnt = int(row.order_count or 0) if row else 0 amt = float(row.order_amount or 0) if row else 0.0 out.append({ "regionId": reg.id, "regionName": reg.name or reg.id, "orderCount": cnt, "orderAmount": round(amt, 2), }) return out @staticmethod def get_today_order_stats_by_client( db: Session, region_id: str = None, limit: int = 30, include_inactive_regions: bool = False, ) -> List[Dict[str, Any]]: """今日各下单客户：下单金额合计(元)、商品数量合计（订单行数量之和）；按金额降序，最多 limit 条。""" today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) day_end = today + timedelta(days=1) region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True] amt_q = ( db.query( BizOrder.client_id, func.coalesce(func.sum(BizOrder.total_amount), 0).label("order_amount"), ) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( BizOrder.is_del == 0, BizOrder.create_at >= today, BizOrder.create_at < day_end, SysRegion.is_del == 0, *region_active_clause, ) ) if region_id: amt_q = amt_q.filter(BizOrder.region_id == region_id) amt_rows = amt_q.group_by(BizOrder.client_id).all() qty_q = ( db.query( BizOrder.client_id, func.coalesce(func.sum(BizOrderItem.quantity), 0).label("qty"), ) .select_from(BizOrderItem) .join( BizOrder, and_( BizOrder.id == BizOrderItem.order_id, BizOrder.is_del == 0, BizOrder.create_at >= today, BizOrder.create_at < day_end, ), ) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter(BizOrderItem.is_del == 0) .filter(SysRegion.is_del == 0, *region_active_clause) ) if region_id: qty_q = qty_q.filter(BizOrder.region_id == region_id) qty_rows = qty_q.group_by(BizOrder.client_id).all() qty_map: Dict[str, float] = { r.client_id: float(r.qty or 0) for r in qty_rows if r.client_id } merged: List[Dict[str, Any]] = [] for r in amt_rows: cid = r.client_id if not cid: continue merged.append({ "clientId": cid, "orderAmount": round(float(r.order_amount or 0), 2), "productQuantity": round(float(qty_map.get(cid, 0) or 0), 4), }) merged.sort(key=lambda x: -x["orderAmount"]) merged = merged[: max(1, min(int(limit or 30), 100))] client_ids = [m["clientId"] for m in merged] names: Dict[str, str] = {} if client_ids: for u in ( db.query(SysUser) .filter(and_(SysUser.is_del == 0, SysUser.id.in_(client_ids))) .all() ): names[u.id] = (u.real_name or u.phone or u.username or u.id) or u.id out: List[Dict[str, Any]] = [] for m in merged: cid = m["clientId"] nm = names.get(cid, cid) out.append({ "clientId": cid, "clientName": nm, "orderAmount": m["orderAmount"], "productQuantity": m["productQuantity"], }) return out @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 _dashboard_order_region_clause(region_id: str = None): return [BizOrder.region_id == region_id] if region_id else [] @staticmethod def get_dashboard_summary( db: Session, region_id: str = None, include_inactive_regions: bool = False, ) -> Dict[str, Any]: """获取仪表盘汇总数据（运营工作台；含待办摘要、最新订单、预警数等，字段驼峰）""" today = datetime.now().replace(hour=0, minute=0, second=0, microsecond=0) tomorrow_start = today + timedelta(days=1) month_start = today.replace(day=1) yesterday_start = today - timedelta(days=1) region_clause = StatisticsCRUD._dashboard_order_region_clause(region_id) region_active_clause = [] if include_inactive_regions else [SysRegion.is_active == True] order_query = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.create_at >= month_start, SysRegion.is_del == 0, *region_active_clause, *region_clause, ) ) ) month_orders = order_query.all() today_orders = [ o for o in month_orders if o.create_at and o.create_at >= today ] prev_day_orders = [ o for o in month_orders if o.create_at and yesterday_start <= o.create_at < today ] pending_delivery_q = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.delivery_status == "pending_dispatch", SysRegion.is_del == 0, *region_active_clause, *region_clause, ) ) ) pending_delivery_count = pending_delivery_q.count() def _delta_hint(cur_n: int, prev_n: int, unit: str) -> str: d = cur_n - prev_n if d > 0: return f"较昨日 +{d}{unit}" if d < 0: return f"较昨日 {d}{unit}" return "与昨日持平" def _amount_delta_hint(cur_amt: float, prev_amt: float) -> str: d = round(cur_amt - prev_amt, 2) if d > 0.0001: return f"较昨日 +¥{d:,.2f}" if d < -0.0001: return f"较昨日 ¥{d:,.2f}" return "与昨日持平" today_order_count = len(today_orders) today_order_amount = round(sum(float(o.total_amount or 0) for o in today_orders), 2) prev_count = len(prev_day_orders) prev_amount = round(sum(float(o.total_amount or 0) for o in prev_day_orders), 2) # 未解决的配送时效预警（可按区域通过任务表关联） warn_q = ( db.query(BizDeliveryWarning) .join( BizDeliveryTask, and_( BizDeliveryTask.id == BizDeliveryWarning.task_id, BizDeliveryTask.is_del == 0, ), ) .join(SysRegion, BizDeliveryTask.region_id == SysRegion.id) .filter( and_( BizDeliveryWarning.is_del == 0, BizDeliveryWarning.is_resolved == 0, SysRegion.is_del == 0, *region_active_clause, ) ) ) if region_id: warn_q = warn_q.filter(BizDeliveryTask.region_id == region_id) delivery_warning_count = warn_q.count() # 今日新提交报价：状态为已提交，且提交时间（无则用创建时间）落在当日 0 点～次日 0 点 quot_submitted_at = func.coalesce(BizQuotation.submit_time, BizQuotation.create_at) sub_quot_q = ( db.query(BizQuotation) .join(SysRegion, BizQuotation.region_id == SysRegion.id) .filter( and_( BizQuotation.is_del == 0, BizQuotation.status == "submitted", quot_submitted_at >= today, quot_submitted_at < tomorrow_start, SysRegion.is_del == 0, *region_active_clause, ) ) ) if region_id: sub_quot_q = sub_quot_q.filter(BizQuotation.region_id == region_id) pending_quotation_count = sub_quot_q.count() sub_quot_preview = ( db.query(BizQuotation, BizInquiry) .join(BizInquiry, BizInquiry.id == BizQuotation.inquiry_id) .join(SysRegion, BizQuotation.region_id == SysRegion.id) .filter( BizQuotation.is_del == 0, BizQuotation.status == "submitted", quot_submitted_at >= today, quot_submitted_at < tomorrow_start, BizInquiry.is_del == 0, SysRegion.is_del == 0, *region_active_clause, *([BizQuotation.region_id == region_id] if region_id else []), ) .order_by(desc(BizQuotation.submit_time), desc(BizQuotation.create_at)) .limit(2) .all() ) quotation_lines = [] for q, inq in sub_quot_preview: title = (inq.title or "询价单")[:48] quotation_lines.append(title) pd_preview = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.delivery_status == "pending_dispatch", SysRegion.is_del == 0, *region_active_clause, *region_clause, ) ) .order_by(desc(BizOrder.create_at)) .limit(2) .all() ) delivery_lines = [o.order_no for o in pd_preview] cancel_preview = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.cancel_status == "pending_review", SysRegion.is_del == 0, *region_active_clause, *region_clause, ) ) .order_by(desc(BizOrder.update_at)) .limit(2) .all() ) cancel_client_ids = list({o.client_id for o in cancel_preview if o.client_id}) cancel_clients = {} if cancel_client_ids: for u in ( db.query(SysUser) .filter(and_(SysUser.is_del == 0, SysUser.id.in_(cancel_client_ids))) .all() ): cancel_clients[u.id] = u.real_name or u.phone or u.id cancel_lines = [] for o in cancel_preview: nm = cancel_clients.get(o.client_id, "") cancel_lines.append(f"{o.order_no}" + (f" · {nm[:16]}" if nm else "")) settle_preview = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.client_settlement_status == "pending", BizOrder.pay_status == "paid", BizOrder.order_status.in_(["pending_confirm", "completed"]), SysRegion.is_del == 0, *region_active_clause, *region_clause, ) ) .order_by(desc(BizOrder.update_at)) .limit(2) .all() ) settle_client_ids = list({o.client_id for o in settle_preview if o.client_id}) settle_clients = {} if settle_client_ids: for u in ( db.query(SysUser) .filter(and_(SysUser.is_del == 0, SysUser.id.in_(settle_client_ids))) .all() ): settle_clients[u.id] = u.real_name or u.phone or u.id settle_lines = [] for o in settle_preview: nm = settle_clients.get(o.client_id, "") settle_lines.append(f"{o.order_no}" + (f" · {nm[:16]}" if nm else "")) pending_settlement_count = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.client_settlement_status == "pending", BizOrder.pay_status == "paid", BizOrder.order_status.in_(["pending_confirm", "completed"]), SysRegion.is_del == 0, *region_active_clause, *region_clause, ) ) .count() ) pending_cancel_count = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter( and_( BizOrder.is_del == 0, BizOrder.cancel_status == "pending_review", SysRegion.is_del == 0, *region_active_clause, *region_clause, ) ) .count() ) # 最新订单（工作台列表） recent_rows = ( db.query(BizOrder) .join(SysRegion, BizOrder.region_id == SysRegion.id) .filter(and_(BizOrder.is_del == 0, SysRegion.is_del == 0, *region_active_clause, *region_clause)) .order_by(desc(BizOrder.create_at)) .limit(8) .all() ) rid_set = list({o.region_id for o in recent_rows if o.region_id}) cid_set = list({o.client_id for o in recent_rows if o.client_id}) region_name_map = {} if rid_set: for r in db.query(SysRegion).filter( and_(SysRegion.is_del == 0, *region_active_clause, SysRegion.id.in_(rid_set)) ).all(): region_name_map[r.id] = r.name client_name_map = {} if cid_set: for u in db.query(SysUser).filter(and_(SysUser.is_del == 0, SysUser.id.in_(cid_set))).all(): client_name_map[u.id] = u.real_name or u.phone or "-" recent_orders = [] for o in recent_rows: order_status = getattr(o, "order_status", None) or "pending_payment" recent_orders.append( { "id": o.id, "orderNo": o.order_no, "clientName": client_name_map.get(o.client_id, "-"), "regionName": region_name_map.get(o.region_id, "-"), "totalAmount": float(o.total_amount) if o.total_amount else 0, "orderStatus": order_status, "cancelStatus": getattr(o, "cancel_status", None) or "none", "deliveryStatus": getattr(o, "delivery_status", None) or "pending_dispatch", "createAt": str(o.create_at) if o.create_at else None, } ) todo_snapshots = [ { "type": "quotation", "title": "今日新提交报价", "count": pending_quotation_count, "items": quotation_lines, }, { "type": "delivery", "title": "待分配配送", "count": pending_delivery_count, "items": delivery_lines, }, { "type": "cancel", "title": "取消待审核", "count": pending_cancel_count, "items": cancel_lines, }, { "type": "settlement", "title": "待客户结算", "count": pending_settlement_count, "items": settle_lines, }, ] # “客户总数/供应商总数”口径：统计用户表（sys_user）并按 region_id 区分 # - region_id 为空：不过滤全局 # - region_id 传入：按 SysUser.region_id 过滤 customer_count = ( db.query(SysUser) .join(SysRegion, SysUser.region_id == SysRegion.id) .filter( and_( SysUser.is_del == 0, SysUser.role == "client", SysRegion.is_del == 0, *region_active_clause, *([SysUser.region_id == region_id] if region_id else []), ) ) .count() ) supplier_count = ( db.query(SysUser) .join(SysRegion, SysUser.region_id == SysRegion.id) .filter( and_( SysUser.is_del == 0, SysUser.role == "supplier", SysRegion.is_del == 0, *region_active_clause, *([SysUser.region_id == region_id] if region_id else []), ) ) .count() ) return { "todayOrderCount": today_order_count, "todayOrderAmount": today_order_amount, "monthOrderCount": len(month_orders), "monthOrderAmount": round(sum(float(o.total_amount or 0) for o in month_orders), 2), "pendingDeliveryCount": pending_delivery_count, "completedOrderCount": sum( 1 for o in month_orders if (getattr(o, "order_status", None) or o.status) == "completed" ), "activePriceListCount": ( db.query(BizPriceList) .join(SysRegion, BizPriceList.region_id == SysRegion.id) .filter( and_( BizPriceList.is_del == 0, BizPriceList.status == "active", SysRegion.is_del == 0, *region_active_clause, *([BizPriceList.region_id == region_id] if region_id else []), ) ) .count() ), "customerCount": customer_count, "supplierCount": supplier_count, "todayOrderCountHint": _delta_hint(today_order_count, prev_count, " 单"), "todayOrderAmountHint": _amount_delta_hint(today_order_amount, prev_amount), "deliveryWarningCount": delivery_warning_count, "recentOrders": recent_orders, "todoSnapshots": todo_snapshots, } statistics_crud = StatisticsCRUD()