settlement.py 4.6 KB

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