settlement.py 5.6 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142
  1. # app/models/settlement.py
  2. from datetime import datetime
  3. from typing import Optional
  4. from sqlalchemy import Column, Integer, String, DateTime, JSON, Enum, Text
  5. from app.core.database import Base
  6. class VasSettlementPeriod(Base):
  7. __tablename__ = "vas_settlement_period"
  8. __ddl__ = """
  9. CREATE TABLE IF NOT EXISTS `vas_settlement_period` (
  10. `id` VARCHAR(32) NOT NULL COMMENT '账期ID如 2026-10',
  11. `name` VARCHAR(128) NOT NULL,
  12. `start_date` DATETIME NOT NULL,
  13. `end_date` DATETIME NOT NULL,
  14. `total_orders` INT NOT NULL DEFAULT 0,
  15. `total_revenue` INT NOT NULL DEFAULT 0,
  16. `total_cost` INT NOT NULL DEFAULT 0,
  17. `avg_cost` INT NOT NULL DEFAULT 0,
  18. `net_profit` INT NOT NULL DEFAULT 0,
  19. `status` ENUM('draft', 'confirmed', 'paid') NOT NULL DEFAULT 'draft',
  20. `summary_data` JSON NULL,
  21. `confirmed_by` VARCHAR(128) NULL,
  22. `confirmed_at` DATETIME NULL,
  23. `paid_at` DATETIME NULL,
  24. `notes` TEXT NULL,
  25. `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  26. `updated_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  27. PRIMARY KEY (`id`)
  28. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='结算账期总表';
  29. """
  30. id = Column(String(32), primary_key=True) # 账期ID,如 2026-10 或 2026-10-Q1
  31. name = Column(String(128), nullable=False) # 账期显示名称
  32. start_date = Column(DateTime, nullable=False)
  33. end_date = Column(DateTime, nullable=False)
  34. total_orders = Column(Integer, default=0, nullable=False) # 本账期履约完成订单总数
  35. total_revenue = Column(Integer, default=0, nullable=False) # 本期实收总额 (分)
  36. total_cost = Column(Integer, default=0, nullable=False) # 本期耗材总成本 (分)
  37. avg_cost = Column(Integer, default=0, nullable=False) # 单均摊薄耗材 (分)
  38. net_profit = Column(Integer, default=0, nullable=False) # 本期总净毛利 (分)
  39. status = Column(
  40. Enum('draft', 'confirmed', 'paid', name='settlement_period_status'),
  41. default='draft',
  42. nullable=False
  43. )
  44. # 包含甲乙分红、各对接人提成总计、开发者版权总计、过失扣除明细的完整汇总 JSON
  45. summary_data = Column(JSON, nullable=True)
  46. confirmed_by = Column(String(128), nullable=True)
  47. confirmed_at = Column(DateTime, nullable=True)
  48. paid_at = Column(DateTime, nullable=True)
  49. notes = Column(Text, nullable=True)
  50. created_at = Column(DateTime, default=datetime.utcnow)
  51. updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
  52. class VasSettlementDetail(Base):
  53. __tablename__ = "vas_settlement_detail"
  54. __ddl__ = """
  55. CREATE TABLE IF NOT EXISTS `vas_settlement_detail` (
  56. `id` INT NOT NULL AUTO_INCREMENT,
  57. `period_id` VARCHAR(32) NOT NULL,
  58. `order_id` VARCHAR(128) NOT NULL,
  59. `channel_type` VARCHAR(32) NOT NULL,
  60. `agency_id` VARCHAR(64) NULL,
  61. `handler_id` VARCHAR(128) NULL,
  62. `developer_id` VARCHAR(128) NULL,
  63. `order_amount` INT NOT NULL,
  64. `avg_cost` INT NOT NULL,
  65. `net_profit` INT NOT NULL,
  66. `tech_fee` INT NOT NULL DEFAULT 0,
  67. `sales_fee` INT NOT NULL DEFAULT 0,
  68. `pool_fee` INT NOT NULL DEFAULT 0,
  69. `partner_a_amount` INT NOT NULL DEFAULT 0,
  70. `partner_b_amount` INT NOT NULL DEFAULT 0,
  71. `snapshot` JSON NULL,
  72. `created_at` DATETIME NULL DEFAULT CURRENT_TIMESTAMP,
  73. PRIMARY KEY (`id`),
  74. INDEX `idx_vas_settle_det_period` (`period_id`),
  75. INDEX `idx_vas_settle_det_order` (`order_id`)
  76. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='单笔订单分润快照表';
  77. """
  78. id = Column(Integer, primary_key=True, autoincrement=True)
  79. period_id = Column(String(32), nullable=False, index=True)
  80. order_id = Column(String(128), nullable=False, index=True)
  81. channel_type = Column(String(32), nullable=False)
  82. agency_id = Column(String(64), nullable=True)
  83. handler_id = Column(String(128), nullable=True) # 40%提成归属人
  84. developer_id = Column(String(128), nullable=True) # 20%技术版权归属人
  85. order_amount = Column(Integer, nullable=False) # 订单实收
  86. avg_cost = Column(Integer, nullable=False) # 均摊耗材
  87. net_profit = Column(Integer, nullable=False) # 单单毛利
  88. tech_fee = Column(Integer, default=0, nullable=False) # 20% 技术版权费
  89. sales_fee = Column(Integer, default=0, nullable=False) # 40% 销售包干提成
  90. pool_fee = Column(Integer, default=0, nullable=False) # 40% 合伙分红池
  91. partner_a_amount = Column(Integer, default=0, nullable=False) # 甲应得金额
  92. partner_b_amount = Column(Integer, default=0, nullable=False) # 乙应得金额
  93. # 包含该单完整计算快照
  94. snapshot = Column(JSON, nullable=True)
  95. created_at = Column(DateTime, default=datetime.utcnow)
  96. @property
  97. def handler_name(self) -> Optional[str]:
  98. if self.snapshot and isinstance(self.snapshot, dict):
  99. return self.snapshot.get("handler_name")
  100. return None
  101. @property
  102. def developer_name(self) -> Optional[str]:
  103. if self.snapshot and isinstance(self.snapshot, dict):
  104. return self.snapshot.get("developer_name")
  105. return None
  106. @property
  107. def original_amount(self) -> Optional[int]:
  108. if self.snapshot and isinstance(self.snapshot, dict):
  109. return self.snapshot.get("original_amount")
  110. return None
  111. @property
  112. def original_currency(self) -> Optional[str]:
  113. if self.snapshot and isinstance(self.snapshot, dict):
  114. return self.snapshot.get("original_currency")
  115. return None
  116. @property
  117. def currency(self) -> str:
  118. if self.snapshot and isinstance(self.snapshot, dict):
  119. return self.snapshot.get("currency") or "CNY"
  120. return "CNY"