-- ============================================================================ -- 复欣物业「一屋一表」数字化台账系统 · 数据库脚本 -- 版本 v1.0 2026-10-04 目标库:现有管控后台数据库(SQLite) -- ---------------------------------------------------------------------------- -- 设计原则(对应需求文档第 3、4 章): -- 1. 不重复造轮子:baoxiu / tousu / gongdan 三张表如已存在则「原样复用」, -- 本脚本用 CREATE TABLE IF NOT EXISTS 保护,绝不覆盖现有数据; -- 2. 真正新增的表只有:building(楼栋说明书)+ 两张规则配置表; -- 3. 一屋一表(house)只做「补字段」,不重建表; -- 4. 所有业务表统一通过 project_name + building + unit + room 关联一屋一表; -- 5. 台账模块对一屋一表只读不改,绝不触碰收费业务数据。 -- ============================================================================ PRAGMA foreign_keys = ON; -- ############################################################################ -- 一、新增表:楼栋基本情况说明书(每栋一份) -- ############################################################################ CREATE TABLE IF NOT EXISTS building ( id INTEGER PRIMARY KEY AUTOINCREMENT, project_name VARCHAR(100) NOT NULL, -- 项目名称 building_no VARCHAR(20) NOT NULL, -- 栋号,与一屋一表 building 字段完全一致 unit_count INTEGER NOT NULL DEFAULT 1, -- 单元数 floors_per_unit INTEGER, -- 每单元层数 total_households INTEGER, -- 总户数 total_area DECIMAL(12,2), -- 总建筑面积 m² structure VARCHAR(50), -- 建筑结构:框架/砖混/剪力墙 delivery_year VARCHAR(4), -- 交付年份 property_fee DECIMAL(6,2), -- 物业费标准 元/m²/月 elevator_count INTEGER, -- 电梯数量 elevator_info VARCHAR(200), -- 电梯品牌/年检情况 fire_facility VARCHAR(200), -- 消防设施情况 utilities VARCHAR(200), -- 供水供电情况 access_control VARCHAR(200), -- 门禁/监控情况 has_commercial VARCHAR(10) DEFAULT '无', -- 有无底商 has_basement VARCHAR(10) DEFAULT '无', -- 有无地下室/车库 building_manager VARCHAR(50), -- 楼栋管家/责任人 cleaning_owner VARCHAR(50), -- 保洁负责人 security_owner VARCHAR(50), -- 保安负责人 special_notes TEXT, -- 特殊注意事项 remark VARCHAR(500), created_at DATETIME DEFAULT (datetime('now','localtime')), updated_at DATETIME DEFAULT (datetime('now','localtime')), UNIQUE(project_name, building_no) -- 一个项目下栋号唯一 ); CREATE INDEX IF NOT EXISTS idx_building_proj ON building(project_name); -- ############################################################################ -- 二、规则配置表(管理员可配,避免写死在代码里) -- ############################################################################ -- 2.1 类别 → 责任部门 自动推荐映射(需求 5.1 第 5 步) CREATE TABLE IF NOT EXISTS rule_dept_map ( id INTEGER PRIMARY KEY AUTOINCREMENT, bill_type VARCHAR(10) NOT NULL, -- baoxiu / tousu category VARCHAR(20) NOT NULL, -- 报修或投诉类别 dept VARCHAR(20) NOT NULL, -- 推荐责任部门 UNIQUE(bill_type, category) ); INSERT OR REPLACE INTO rule_dept_map (bill_type, category, dept) VALUES ('baoxiu','水电维修','工程'), ('baoxiu','门窗维修','工程'), ('baoxiu','电梯故障','工程'), ('baoxiu','管道堵塞','工程'), ('baoxiu','电路问题','工程'), ('baoxiu','设施损坏','工程'), ('baoxiu','卫生保洁','保洁'), ('baoxiu','其他','项目经理'), ('tousu','噪音扰民','保安'), ('tousu','安保秩序','保安'), ('tousu','环境卫生','保洁'), ('tousu','客服态度','客服'), ('tousu','费用争议','项目经理'), ('tousu','工程质量','工程'), ('tousu','其他','项目经理'); -- 2.2 超时阈值(单位:小时,按紧急程度区分,需求 6.3 环节④) CREATE TABLE IF NOT EXISTS rule_timeout ( urgency VARCHAR(10) PRIMARY KEY, -- 紧急/一般/不紧急 hours_limit INTEGER NOT NULL -- 超时阈值(小时) ); INSERT OR REPLACE INTO rule_timeout (urgency, hours_limit) VALUES ('紧急', 4), ('一般', 24), ('不紧急', 48); -- ############################################################################ -- 三、复用表结构对照(已存在则跳过创建,仅作字段标准供开发比对) -- ⚠ 开发务必执行第四章的「字段补全检查」,确认现有表字段是否齐全 -- ############################################################################ -- 3.1 报修单 CREATE TABLE IF NOT EXISTS baoxiu ( id INTEGER PRIMARY KEY AUTOINCREMENT, baoxiu_no VARCHAR(32) UNIQUE, -- BX+年月+序号,如 BX2026100001 baoxiu_date DATETIME NOT NULL, -- 客服代录时间(即业主报单时间) month VARCHAR(7) NOT NULL, -- YYYY-MM 月度统计冗余字段 year VARCHAR(4) NOT NULL, -- YYYY 年度统计冗余字段 project_name VARCHAR(100), -- ★ 关联一屋一表必须(见开发清单第 1 条) building VARCHAR(20) NOT NULL, -- 栋号 unit VARCHAR(20), -- 单元号 room VARCHAR(20) NOT NULL, -- 房号 owner_name VARCHAR(50), -- 业主/报修人,选房号自动带出 owner_phone VARCHAR(20), category VARCHAR(20) NOT NULL, -- 水电维修/门窗维修/电梯故障/管道堵塞/电路问题/卫生保洁/设施损坏/其他 urgency VARCHAR(10) NOT NULL DEFAULT '一般', -- 紧急/一般/不紧急 channel VARCHAR(20), -- 来源:微信群/电话/前台/其他 content TEXT NOT NULL, images VARCHAR(500), -- 多张 URL 逗号分隔 dept VARCHAR(20), -- 责任部门(五部门) handler VARCHAR(50), -- 处理人 accept_time DATETIME, -- 接单时间 finish_time DATETIME, -- 完成时间 status VARCHAR(20) NOT NULL DEFAULT '待接单', -- 待接单/处理中/已完成/已关闭/超时未处理 result TEXT, -- 处理结果 satisfaction VARCHAR(10), -- 满意/一般/不满意/未评价 remark VARCHAR(500), created_at DATETIME DEFAULT (datetime('now','localtime')), updated_at DATETIME DEFAULT (datetime('now','localtime')) ); CREATE INDEX IF NOT EXISTS idx_baoxiu_addr ON baoxiu(project_name, building, unit, room); CREATE INDEX IF NOT EXISTS idx_baoxiu_month ON baoxiu(month); CREATE INDEX IF NOT EXISTS idx_baoxiu_st ON baoxiu(status, dept); -- 3.2 投诉单 CREATE TABLE IF NOT EXISTS tousu ( id INTEGER PRIMARY KEY AUTOINCREMENT, tousu_no VARCHAR(32) UNIQUE, -- TS+年月+序号 tousu_date DATETIME NOT NULL, month VARCHAR(7) NOT NULL, year VARCHAR(4) NOT NULL, project_name VARCHAR(100), building VARCHAR(20) NOT NULL, unit VARCHAR(20), room VARCHAR(20) NOT NULL, complainant VARCHAR(50), phone VARCHAR(20), channel VARCHAR(20), -- 微信群/电话/前台/其他 category VARCHAR(20) NOT NULL, -- 噪音扰民/环境卫生/安保秩序/工程质量/客服态度/费用争议/其他 content TEXT NOT NULL, dept VARCHAR(20), handler VARCHAR(50), accept_time DATETIME, finish_time DATETIME, status VARCHAR(20) NOT NULL DEFAULT '待接单', result TEXT, callback VARCHAR(200), -- 回访情况 satisfaction VARCHAR(10), remark VARCHAR(500), created_at DATETIME DEFAULT (datetime('now','localtime')), updated_at DATETIME DEFAULT (datetime('now','localtime')) ); CREATE INDEX IF NOT EXISTS idx_tousu_addr ON tousu(project_name, building, unit, room); CREATE INDEX IF NOT EXISTS idx_tousu_month ON tousu(month); CREATE INDEX IF NOT EXISTS idx_tousu_st ON tousu(status, dept); -- 3.3 工单(五部门协同处理记录,KPI 唯一数据源) CREATE TABLE IF NOT EXISTS gongdan ( id INTEGER PRIMARY KEY AUTOINCREMENT, gongdan_no VARCHAR(32) UNIQUE, -- GD+年月+序号 source VARCHAR(20) NOT NULL, -- 报修/投诉/日常巡检/入户服务/其他 source_id INTEGER, -- 关联 baoxiu.id 或 tousu.id work_date DATETIME NOT NULL, month VARCHAR(7) NOT NULL, year VARCHAR(4) NOT NULL, project_name VARCHAR(100), building VARCHAR(20) NOT NULL, unit VARCHAR(20), room VARCHAR(20) NOT NULL, related_person VARCHAR(50), content TEXT NOT NULL, dept VARCHAR(20), -- 保洁/保安/客服/项目经理/工程 handler VARCHAR(50), -- KPI 考核主体 accept_time DATETIME, finish_time DATETIME, handle_method VARCHAR(200), status VARCHAR(20) NOT NULL DEFAULT '待接单', result TEXT, images VARCHAR(500), -- 处理后照片 overtime INTEGER DEFAULT 0, -- 是否超时 0/1,由守护任务写入 remark VARCHAR(500), created_at DATETIME DEFAULT (datetime('now','localtime')), updated_at DATETIME DEFAULT (datetime('now','localtime')) ); CREATE INDEX IF NOT EXISTS idx_gd_addr ON gongdan(project_name, building, unit, room); CREATE INDEX IF NOT EXISTS idx_gd_month ON gongdan(month); CREATE INDEX IF NOT EXISTS idx_gd_dept ON gongdan(dept, handler, status); -- ############################################################################ -- 四、一屋一表(house)字段补全 -- SQLite 不支持 ADD COLUMN IF NOT EXISTS,请由迁移脚本逐列判断后执行; -- 下面列出需求 4.4 要求补充的 4 个字段(按现有表实际缺哪个补哪个) -- ############################################################################ -- ALTER TABLE house ADD COLUMN payment_status VARCHAR(20); -- 已缴清/欠费/部分缴纳/免缴 -- ALTER TABLE house ADD COLUMN occupancy_status VARCHAR(20); -- 已入住/空置/装修中/未交付 -- ALTER TABLE house ADD COLUMN vehicle_info VARCHAR(200); -- 车辆信息 -- ALTER TABLE house ADD COLUMN relation VARCHAR(20); -- 产权人/租户/家属/其他 -- 附:幂等补列辅助(Node/Python 侧伪代码) -- cols = PRAGMA table_info(house) -- if 'payment_status' not in cols: ALTER TABLE house ADD COLUMN payment_status VARCHAR(20) -- ############################################################################ -- 五、台账归集视图(按户串历史、按月汇总,都不用改业务表结构) -- ############################################################################ -- 5.1 按户归集视图:一屋一表每户的「历史报单」标签数据源 CREATE VIEW IF NOT EXISTS v_house_ledger AS SELECT project_name, building, unit, room, related_person, '报修' AS bill_type, CAST(id AS TEXT) AS source_id, gongdan_no AS bill_no, work_date AS happen_time, category, dept, handler, status, result, satisfaction, accept_time, finish_time FROM ( SELECT g.*, b.category FROM gongdan g LEFT JOIN baoxiu b ON g.source='报修' AND g.source_id=b.id ) UNION ALL SELECT project_name, building, unit, room, related_person, '投诉', CAST(id AS TEXT), gongdan_no, work_date, (SELECT category FROM tousu t WHERE t.id=g.source_id), dept, handler, status, result, NULL, accept_time, finish_time FROM gongdan g WHERE source='投诉' UNION ALL SELECT project_name, building, unit, room, related_person, '日常', CAST(id AS TEXT), gongdan_no, work_date, source, dept, handler, status, result, NULL, accept_time, finish_time FROM gongdan g WHERE source IN ('日常巡检','入户服务','其他'); -- 5.2 月度汇总视图(月度/年度报表导出直接读它) CREATE VIEW IF NOT EXISTS v_month_summary AS SELECT month, dept, COUNT(*) AS total_cnt, SUM(CASE WHEN status IN ('已完成','已关闭') THEN 1 ELSE 0 END) AS done_cnt, SUM(CASE WHEN status='待接单' THEN 1 ELSE 0 END) AS wait_cnt, SUM(CASE WHEN status='处理中' THEN 1 ELSE 0 END) AS doing_cnt, SUM(CASE WHEN status='超时未处理' THEN 1 ELSE 0 END) AS overtime_cnt FROM gongdan GROUP BY month, dept; -- 5.3 KPI 视图(指标口径固化,需求 5.6) CREATE VIEW IF NOT EXISTS v_kpi_person_month AS SELECT month, handler, dept, COUNT(*) AS handle_cnt, AVG(CASE WHEN accept_time IS NOT NULL THEN (julianday(accept_time)-julianday(work_date))*24 END) AS avg_resp_h, AVG(CASE WHEN finish_time IS NOT NULL AND accept_time IS NOT NULL THEN (julianday(finish_time)-julianday(accept_time))*24 END) AS avg_done_h, SUM(CASE WHEN status='超时未处理' OR overtime=1 THEN 1 ELSE 0 END) AS overtime_cnt, SUM(CASE WHEN satisfaction='满意' THEN 1 ELSE 0 END) AS satis_good, SUM(CASE WHEN satisfaction='一般' THEN 1 ELSE 0 END) AS satis_mid, SUM(CASE WHEN satisfaction='不满意' THEN 1 ELSE 0 END) AS satis_bad FROM gongdan WHERE handler IS NOT NULL AND handler<>'' GROUP BY month, handler, dept; -- ============================================================================ -- 六、上线前数据一致性校验(逐条跑,全 PASS 才可上线) -- SELECT COUNT(*) FROM baoxiu WHERE room IS NULL OR building IS NULL; -- 应为 0 -- SELECT COUNT(*) FROM gongdan WHERE source_id IS NOT NULL -- AND source NOT IN ('报修','投诉'); -- 应为 0 -- SELECT room FROM gongdan WHERE room NOT IN (SELECT room FROM house); -- 孤儿房号 -- 房号格式统一性:见《开发接入清单》第 2 条 -- ============================================================================