statistics.py 5.5 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173
  1. """CRM 统计分析 CRUD"""
  2. from __future__ import annotations
  3. from datetime import datetime, timedelta
  4. from typing import Any, Dict, List, Optional
  5. from sqlalchemy import and_, func
  6. from sqlalchemy.orm import Session
  7. from app.models.crm_models import CrmCustomer, CrmCustomerFollow
  8. from app.crud.crm.data_scope_helpers import apply_crm_create_by_scope, apply_crm_owner_scope
  9. def _customer_base_query(db: Session, tenant_no: str, user_info=None):
  10. q = db.query(CrmCustomer).filter(
  11. CrmCustomer.tenant_no == tenant_no,
  12. CrmCustomer.del_flag == 0,
  13. )
  14. if user_info:
  15. q = apply_crm_owner_scope(q, CrmCustomer, user_info, db, tenant_no)
  16. return q
  17. def get_overview_statistics(
  18. db: Session, tenant_no: str, user_id: str = None, user_info=None
  19. ) -> Dict[str, Any]:
  20. """客户统计概览:客户总数、公海池数、本月新增、成交数"""
  21. base = _customer_base_query(db, tenant_no, user_info=user_info)
  22. total = base.count()
  23. pool_count = base.filter(CrmCustomer.status == "pool").count()
  24. now = datetime.now()
  25. month_start = now.replace(day=1, hour=0, minute=0, second=0, microsecond=0)
  26. month_new = base.filter(CrmCustomer.create_time >= month_start).count()
  27. deal_count = base.filter(CrmCustomer.deal_status == "dealed").count()
  28. return {
  29. "total": total,
  30. "poolCount": pool_count,
  31. "monthNew": month_new,
  32. "dealCount": deal_count,
  33. }
  34. def get_source_statistics(
  35. db: Session, tenant_no: str, user_info=None
  36. ) -> List[Dict[str, Any]]:
  37. """按客户来源统计"""
  38. rows = (
  39. _customer_base_query(db, tenant_no, user_info=user_info)
  40. .with_entities(CrmCustomer.source, func.count(CrmCustomer.id).label("count"))
  41. .group_by(CrmCustomer.source)
  42. .all()
  43. )
  44. return [{"source": r.source or "未知", "count": r.count} for r in rows]
  45. def get_level_statistics(
  46. db: Session, tenant_no: str, user_info=None
  47. ) -> List[Dict[str, Any]]:
  48. """按客户级别统计"""
  49. rows = (
  50. _customer_base_query(db, tenant_no, user_info=user_info)
  51. .with_entities(CrmCustomer.level, func.count(CrmCustomer.id).label("count"))
  52. .group_by(CrmCustomer.level)
  53. .all()
  54. )
  55. return [{"level": r.level or "未知", "count": r.count} for r in rows]
  56. def get_industry_statistics(
  57. db: Session, tenant_no: str, user_info=None
  58. ) -> List[Dict[str, Any]]:
  59. """按行业统计"""
  60. rows = (
  61. _customer_base_query(db, tenant_no, user_info=user_info)
  62. .with_entities(CrmCustomer.industry, func.count(CrmCustomer.id).label("count"))
  63. .group_by(CrmCustomer.industry)
  64. .all()
  65. )
  66. return [{"industry": r.industry or "未知", "count": r.count} for r in rows]
  67. def get_trend_statistics(
  68. db: Session,
  69. tenant_no: str,
  70. start_date: str = None,
  71. end_date: str = None,
  72. user_info=None,
  73. ) -> List[Dict[str, Any]]:
  74. """成交趋势(按月统计成交客户数)"""
  75. query = _customer_base_query(db, tenant_no, user_info=user_info).with_entities(
  76. func.date_format(CrmCustomer.deal_time, "%Y-%m").label("month"),
  77. func.count(CrmCustomer.id).label("count"),
  78. ).filter(
  79. CrmCustomer.deal_status == "dealed",
  80. CrmCustomer.deal_time.isnot(None),
  81. )
  82. if start_date:
  83. query = query.filter(CrmCustomer.deal_time >= start_date)
  84. if end_date:
  85. query = query.filter(CrmCustomer.deal_time <= end_date + " 23:59:59")
  86. rows = query.group_by("month").order_by("month").all()
  87. return [{"month": r.month, "count": r.count} for r in rows]
  88. def get_owner_statistics(
  89. db: Session, tenant_no: str, user_info=None
  90. ) -> List[Dict[str, Any]]:
  91. """按负责人统计客户数"""
  92. rows = (
  93. _customer_base_query(db, tenant_no, user_info=user_info)
  94. .with_entities(
  95. CrmCustomer.owner_user_name,
  96. func.count(CrmCustomer.id).label("count"),
  97. )
  98. .group_by(CrmCustomer.owner_user_id, CrmCustomer.owner_user_name)
  99. .all()
  100. )
  101. return [{"ownerName": r.owner_user_name or "无", "count": r.count} for r in rows]
  102. def get_follow_statistics(
  103. db: Session,
  104. tenant_no: str,
  105. start_date: str = None,
  106. end_date: str = None,
  107. user_info=None,
  108. ) -> Dict[str, Any]:
  109. """跟进统计"""
  110. base = db.query(CrmCustomerFollow).filter(
  111. CrmCustomerFollow.tenant_no == tenant_no,
  112. CrmCustomerFollow.del_flag == 0,
  113. )
  114. if user_info:
  115. base = apply_crm_create_by_scope(base, CrmCustomerFollow, user_info, db, tenant_no)
  116. if start_date:
  117. base = base.filter(CrmCustomerFollow.follow_time >= start_date)
  118. if end_date:
  119. base = base.filter(CrmCustomerFollow.follow_time <= end_date + " 23:59:59")
  120. total = base.count()
  121. # 按跟进方式分组
  122. type_rows = (
  123. base.with_entities(
  124. CrmCustomerFollow.follow_type,
  125. func.count(CrmCustomerFollow.id).label("count"),
  126. )
  127. .group_by(CrmCustomerFollow.follow_type)
  128. .all()
  129. )
  130. by_type = [{"type": r.follow_type or "未知", "count": r.count} for r in type_rows]
  131. # 按负责人分组
  132. owner_rows = (
  133. base.with_entities(
  134. CrmCustomerFollow.owner_user_name,
  135. func.count(CrmCustomerFollow.id).label("count"),
  136. )
  137. .group_by(CrmCustomerFollow.owner_user_id, CrmCustomerFollow.owner_user_name)
  138. .all()
  139. )
  140. by_owner = [
  141. {"ownerName": r.owner_user_name or "无", "count": r.count} for r in owner_rows
  142. ]
  143. return {
  144. "total": total,
  145. "byType": by_type,
  146. "byOwner": by_owner,
  147. }