yumoqing
2547fad996
- Updated core.py, init.py, opportunity_core.py, mysql.ddl.sql - Updated UI: base.ui, opportunity_management.ui - Added: opportunity.json, opportunity_stage_history.json - Added API files: opportunities CRUD, sales_stages_list, check_tables - Added UI/DSPY: opportunity_edit, opportunity_list, sales_stages_list
78 lines
5.0 KiB
SQL
78 lines
5.0 KiB
SQL
-- Table from opportunities.json
|
||
CREATE TABLE IF NOT EXISTS `opportunities` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`customer_id` VARCHAR(32) NOT NULL COMMENT '关联客户管理模块的客户ID',
|
||
`customer_name` VARCHAR(255) NOT NULL COMMENT '客户名称，必填字段',
|
||
`opportunity_name` VARCHAR(255) NOT NULL COMMENT '商机标题或项目名称',
|
||
`estimated_amount` DECIMAL(15,2) NOT NULL COMMENT '预估成交金额，必填字段',
|
||
`current_stage` VARCHAR(50) NOT NULL COMMENT '当前所处的销售阶段，必填字段',
|
||
`expected_close_date` DATE NOT NULL COMMENT '预计成交日期，必填字段',
|
||
`source_type` VARCHAR(20) NOT NULL DEFAULT 'manual' COMMENT 'manual=手动录入, lead=线索转化',
|
||
`owner_id` VARCHAR(32) NOT NULL COMMENT '负责该商机的销售人员ID',
|
||
`owner_name` VARCHAR(100) NOT NULL COMMENT '负责该商机的销售人员姓名',
|
||
`region` VARCHAR(100) COMMENT '商机所属区域，用于分析筛选',
|
||
`description` TEXT COMMENT '商机详细描述信息',
|
||
`probability` FLOAT(5,2) NOT NULL DEFAULT '0.00' COMMENT '基于历史转化率计算的成交概率百分比',
|
||
`predicted_revenue` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '基于成交概率计算的预测收入',
|
||
`created_at` TIMESTAMP NOT NULL,
|
||
`updated_at` TIMESTAMP NOT NULL,
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'active' COMMENT 'active=活跃, won=已成交, lost=已丢失, closed=已关闭',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商机表';
|
||
|
||
CREATE INDEX `idx_opportunities_customer` ON `opportunities` (`customer_id`);
|
||
CREATE INDEX `idx_opportunities_owner` ON `opportunities` (`owner_id`);
|
||
CREATE INDEX `idx_opportunities_stage` ON `opportunities` (`current_stage`);
|
||
CREATE INDEX `idx_opportunities_region` ON `opportunities` (`region`);
|
||
CREATE INDEX `idx_opportunities_status` ON `opportunities` (`status`);
|
||
CREATE INDEX `idx_opportunities_expected_close` ON `opportunities` (`expected_close_date`);
|
||
|
||
-- Table from opportunity_predictions.json
|
||
CREATE TABLE IF NOT EXISTS `opportunity_predictions` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`opportunity_id` VARCHAR(32) NOT NULL COMMENT '关联的商机ID',
|
||
`predicted_amount` DECIMAL(15,2) NOT NULL DEFAULT '0.00' COMMENT '基于历史转化率计算的预测成交金额',
|
||
`confidence_level` DECIMAL(5,4) NOT NULL DEFAULT '0.0000' COMMENT '预测的置信度（0-1）',
|
||
`prediction_date` DATE NOT NULL COMMENT '预测生成日期',
|
||
`actual_amount` DECIMAL(15,2) COMMENT '实际成交金额（成交后更新）',
|
||
`deviation_rate` DECIMAL(5,4) COMMENT '预测与实际的偏差率',
|
||
`created_at` TIMESTAMP NOT NULL COMMENT '记录创建时间',
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商机预测表';
|
||
|
||
CREATE UNIQUE INDEX `idx_predictions_opportunity` ON `opportunity_predictions` (`opportunity_id`, `prediction_date`);
|
||
CREATE INDEX `idx_predictions_date` ON `opportunity_predictions` (`prediction_date`);
|
||
|
||
-- Table from opportunity_stage_history.json
|
||
CREATE TABLE IF NOT EXISTS `opportunity_stage_history` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`opportunity_id` VARCHAR(32) NOT NULL COMMENT '关联的商机ID',
|
||
`from_stage` VARCHAR(50) COMMENT '变更前的销售阶段',
|
||
`to_stage` VARCHAR(50) NOT NULL COMMENT '变更后的销售阶段',
|
||
`change_reason` TEXT NOT NULL COMMENT '阶段变更的原因说明，必填字段',
|
||
`changed_by_id` VARCHAR(32) NOT NULL COMMENT '执行阶段变更的用户ID',
|
||
`changed_by_name` VARCHAR(100) NOT NULL COMMENT '执行阶段变更的用户姓名',
|
||
`changed_at` TIMESTAMP NOT NULL,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='商机阶段变更历史表';
|
||
|
||
CREATE INDEX `idx_stage_history_opportunity` ON `opportunity_stage_history` (`opportunity_id`);
|
||
CREATE INDEX `idx_stage_history_changed_by` ON `opportunity_stage_history` (`changed_by_id`);
|
||
CREATE INDEX `idx_stage_history_changed_at` ON `opportunity_stage_history` (`changed_at`);
|
||
|
||
-- Table from sales_stages.json
|
||
CREATE TABLE IF NOT EXISTS `sales_stages` (
|
||
`id` VARCHAR(32) NOT NULL COMMENT '主键 - UUID格式',
|
||
`stage_name` VARCHAR(100) NOT NULL COMMENT '销售阶段名称，如\'初步接洽\'、\'需求确认\'等',
|
||
`stage_order` INT NOT NULL COMMENT '阶段在销售漏斗中的顺序，从小到大',
|
||
`conversion_rate` FLOAT(5,2) NOT NULL DEFAULT '0.00' COMMENT '该阶段到下一阶段的历史平均转化率',
|
||
`is_won_stage` VARCHAR(5) NOT NULL DEFAULT 'no' COMMENT 'yes=成交阶段, no=非成交阶段',
|
||
`is_lost_stage` VARCHAR(5) NOT NULL DEFAULT 'no' COMMENT 'yes=丢失阶段, no=非丢失阶段',
|
||
`created_at` TIMESTAMP NOT NULL,
|
||
`updated_at` TIMESTAMP NOT NULL,
|
||
PRIMARY KEY (`id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='销售阶段配置表';
|
||
|
||
CREATE UNIQUE INDEX `idx_sales_stages_order` ON `sales_stages` (`stage_order`);
|
||
CREATE UNIQUE INDEX `idx_sales_stages_name` ON `sales_stages` (`stage_name`);
|