| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173 |
- """CRM 统计分析 CRUD"""
- from __future__ import annotations
- from datetime import datetime, timedelta
- from typing import Any, Dict, List, Optional
- from sqlalchemy import and_, func
- from sqlalchemy.orm import Session
- from app.models.crm_models import CrmCustomer, CrmCustomerFollow
- from app.crud.crm.data_scope_helpers import apply_crm_create_by_scope, apply_crm_owner_scope
- def _customer_base_query(db: Session, tenant_no: str, user_info=None):
- q = db.query(CrmCustomer).filter(
- CrmCustomer.tenant_no == tenant_no,
- CrmCustomer.del_flag == 0,
- )
- if user_info:
- q = apply_crm_owner_scope(q, CrmCustomer, user_info, db, tenant_no)
- return q
- def get_overview_statistics(
- db: Session, tenant_no: str, user_id: str = None, user_info=None
- ) -> Dict[str, Any]:
- """客户统计概览:客户总数、公海池数、本月新增、成交数"""
- base = _customer_base_query(db, tenant_no, user_info=user_info)
- total = base.count()
- pool_count = base.filter(CrmCustomer.status == "pool").count()
- now = datetime.now()
- month_start = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
- month_new = base.filter(CrmCustomer.create_time >= month_start).count()
- deal_count = base.filter(CrmCustomer.deal_status == "dealed").count()
- return {
- "total": total,
- "poolCount": pool_count,
- "monthNew": month_new,
- "dealCount": deal_count,
- }
- def get_source_statistics(
- db: Session, tenant_no: str, user_info=None
- ) -> List[Dict[str, Any]]:
- """按客户来源统计"""
- rows = (
- _customer_base_query(db, tenant_no, user_info=user_info)
- .with_entities(CrmCustomer.source, func.count(CrmCustomer.id).label("count"))
- .group_by(CrmCustomer.source)
- .all()
- )
- return [{"source": r.source or "未知", "count": r.count} for r in rows]
- def get_level_statistics(
- db: Session, tenant_no: str, user_info=None
- ) -> List[Dict[str, Any]]:
- """按客户级别统计"""
- rows = (
- _customer_base_query(db, tenant_no, user_info=user_info)
- .with_entities(CrmCustomer.level, func.count(CrmCustomer.id).label("count"))
- .group_by(CrmCustomer.level)
- .all()
- )
- return [{"level": r.level or "未知", "count": r.count} for r in rows]
- def get_industry_statistics(
- db: Session, tenant_no: str, user_info=None
- ) -> List[Dict[str, Any]]:
- """按行业统计"""
- rows = (
- _customer_base_query(db, tenant_no, user_info=user_info)
- .with_entities(CrmCustomer.industry, func.count(CrmCustomer.id).label("count"))
- .group_by(CrmCustomer.industry)
- .all()
- )
- return [{"industry": r.industry or "未知", "count": r.count} for r in rows]
- def get_trend_statistics(
- db: Session,
- tenant_no: str,
- start_date: str = None,
- end_date: str = None,
- user_info=None,
- ) -> List[Dict[str, Any]]:
- """成交趋势(按月统计成交客户数)"""
- query = _customer_base_query(db, tenant_no, user_info=user_info).with_entities(
- func.date_format(CrmCustomer.deal_time, "%Y-%m").label("month"),
- func.count(CrmCustomer.id).label("count"),
- ).filter(
- CrmCustomer.deal_status == "dealed",
- CrmCustomer.deal_time.isnot(None),
- )
- if start_date:
- query = query.filter(CrmCustomer.deal_time >= start_date)
- if end_date:
- query = query.filter(CrmCustomer.deal_time <= end_date + " 23:59:59")
- rows = query.group_by("month").order_by("month").all()
- return [{"month": r.month, "count": r.count} for r in rows]
- def get_owner_statistics(
- db: Session, tenant_no: str, user_info=None
- ) -> List[Dict[str, Any]]:
- """按负责人统计客户数"""
- rows = (
- _customer_base_query(db, tenant_no, user_info=user_info)
- .with_entities(
- CrmCustomer.owner_user_name,
- func.count(CrmCustomer.id).label("count"),
- )
- .group_by(CrmCustomer.owner_user_id, CrmCustomer.owner_user_name)
- .all()
- )
- return [{"ownerName": r.owner_user_name or "无", "count": r.count} for r in rows]
- def get_follow_statistics(
- db: Session,
- tenant_no: str,
- start_date: str = None,
- end_date: str = None,
- user_info=None,
- ) -> Dict[str, Any]:
- """跟进统计"""
- base = db.query(CrmCustomerFollow).filter(
- CrmCustomerFollow.tenant_no == tenant_no,
- CrmCustomerFollow.del_flag == 0,
- )
- if user_info:
- base = apply_crm_create_by_scope(base, CrmCustomerFollow, user_info, db, tenant_no)
- if start_date:
- base = base.filter(CrmCustomerFollow.follow_time >= start_date)
- if end_date:
- base = base.filter(CrmCustomerFollow.follow_time <= end_date + " 23:59:59")
- total = base.count()
- # 按跟进方式分组
- type_rows = (
- base.with_entities(
- CrmCustomerFollow.follow_type,
- func.count(CrmCustomerFollow.id).label("count"),
- )
- .group_by(CrmCustomerFollow.follow_type)
- .all()
- )
- by_type = [{"type": r.follow_type or "未知", "count": r.count} for r in type_rows]
- # 按负责人分组
- owner_rows = (
- base.with_entities(
- CrmCustomerFollow.owner_user_name,
- func.count(CrmCustomerFollow.id).label("count"),
- )
- .group_by(CrmCustomerFollow.owner_user_id, CrmCustomerFollow.owner_user_name)
- .all()
- )
- by_owner = [
- {"ownerName": r.owner_user_name or "无", "count": r.count} for r in owner_rows
- ]
- return {
- "total": total,
- "byType": by_type,
- "byOwner": by_owner,
- }
|