andyesfly
219091f864
本次提交新增了客户拜访、客户建筑、客户房间的完整业务模块，同时补充了项目勘察、项目方案、合同管理、项目验收、回访记录的全项目流程相关功能，包括： 1. 新增对应业务的实体类、DTO、控制器、服务层代码 2. 完善RBAC权限映射配置 3. 添加前端API、路由及页面菜单配置 4. 更新数据库种子数据补充菜单和权限绑定
308 lines
16 KiB
PL/PgSQL
308 lines
16 KiB
PL/PgSQL
-- ============================================================
|
||
-- 增量脚本：客户管理扩展 + 项目管理模块表结构
|
||
-- 日期：2026-08-26
|
||
-- 数据库：PostgreSQL 15+ (xintong)
|
||
-- 说明：新建客户域扩展（客户拜访/建筑/房间）与项目域
|
||
-- （勘察派单/方案+明细/合同/验收/回访）共 9 张表。
|
||
-- 沿用 Sequelize Entity 定义 + 幂等建表，可重复执行。
|
||
--
|
||
-- 兼容性说明：
|
||
-- - 使用 CREATE TABLE IF NOT EXISTS，已有表自动跳过。
|
||
-- - project_plan_detail 对 customer_equipment / smart_device 仅预留
|
||
-- equip_id / smart_device_id 数值列，不建外键（两表后续迭代实现）。
|
||
-- - 对已实现表（customer / customer_department / customer_building /
|
||
-- project_plan / contract）保留外键约束。
|
||
--
|
||
-- 用法：
|
||
-- psql -U postgres -d xintong -f 20260826_project_customer_ext.sql
|
||
-- ============================================================
|
||
|
||
BEGIN;
|
||
|
||
-- 依赖：trigger_set_updated_at() 已由 db_init.sql 创建（若缺失则这里兜底创建）
|
||
CREATE OR REPLACE FUNCTION trigger_set_updated_at()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ LANGUAGE plpgsql;
|
||
|
||
-- ============================================================
|
||
-- 1. 客户拜访信息表 customer_visit
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS customer_visit (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
visit_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
visit_person VARCHAR(64) NOT NULL,
|
||
visit_phone VARCHAR(20),
|
||
visit_content TEXT,
|
||
visit_time DATE,
|
||
salesman_id BIGINT,
|
||
will_plan SMALLINT DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_visit IS '客户拜访信息表';
|
||
COMMENT ON COLUMN customer_visit.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_visit.visit_no IS '拜访单号（唯一）';
|
||
COMMENT ON COLUMN customer_visit.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_visit.visit_person IS '拜访人';
|
||
COMMENT ON COLUMN customer_visit.visit_phone IS '拜访人电话';
|
||
COMMENT ON COLUMN customer_visit.visit_content IS '拜访内容';
|
||
COMMENT ON COLUMN customer_visit.visit_time IS '拜访时间';
|
||
COMMENT ON COLUMN customer_visit.salesman_id IS '业务员';
|
||
COMMENT ON COLUMN customer_visit.will_plan IS '是否做方案：0不同意 1同意';
|
||
COMMENT ON COLUMN customer_visit.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_visit_customer ON customer_visit (customer_id);
|
||
CREATE INDEX IF NOT EXISTS idx_visit_salesman ON customer_visit (salesman_id);
|
||
|
||
-- ============================================================
|
||
-- 2. 客户智电改造建筑信息表 customer_building
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS customer_building (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
building_name VARCHAR(128) NOT NULL,
|
||
basic_info TEXT,
|
||
purpose VARCHAR(128),
|
||
floor_count INT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_building IS '客户智电改造建筑信息表';
|
||
COMMENT ON COLUMN customer_building.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_building.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_building.building_name IS '建筑名称';
|
||
COMMENT ON COLUMN customer_building.basic_info IS '基本情况';
|
||
COMMENT ON COLUMN customer_building.purpose IS '用途';
|
||
COMMENT ON COLUMN customer_building.floor_count IS '层数';
|
||
COMMENT ON COLUMN customer_building.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_building_customer ON customer_building (customer_id);
|
||
|
||
-- ============================================================
|
||
-- 3. 客户建筑房间信息表 customer_room
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS customer_room (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
room_code VARCHAR(64) NOT NULL,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
room_name VARCHAR(128) NOT NULL,
|
||
building_id BIGINT NOT NULL REFERENCES customer_building(id),
|
||
floor_no VARCHAR(16),
|
||
room_purpose VARCHAR(128),
|
||
dept_id BIGINT REFERENCES customer_department(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE customer_room IS '客户建筑房间信息表';
|
||
COMMENT ON COLUMN customer_room.id IS '自增主键';
|
||
COMMENT ON COLUMN customer_room.room_code IS '房间编码';
|
||
COMMENT ON COLUMN customer_room.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN customer_room.room_name IS '房间名称';
|
||
COMMENT ON COLUMN customer_room.building_id IS '所属建筑 ID（→ customer_building）';
|
||
COMMENT ON COLUMN customer_room.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN customer_room.room_purpose IS '房间用途';
|
||
COMMENT ON COLUMN customer_room.dept_id IS '房间所属客户部门（→ customer_department）';
|
||
COMMENT ON COLUMN customer_room.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_room_customer ON customer_room (customer_id);
|
||
CREATE INDEX IF NOT EXISTS idx_room_building ON customer_room (building_id);
|
||
|
||
-- ============================================================
|
||
-- 4. 智电改造方案表 project_plan
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS project_plan (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
plan_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
registrar VARCHAR(64),
|
||
register_time DATE,
|
||
plan_desc TEXT,
|
||
plan_file_url VARCHAR(512),
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE project_plan IS '智电改造方案信息表';
|
||
COMMENT ON COLUMN project_plan.id IS '自增主键';
|
||
COMMENT ON COLUMN project_plan.plan_no IS '方案单号（唯一）';
|
||
COMMENT ON COLUMN project_plan.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN project_plan.registrar IS '方案登记人（业务员）';
|
||
COMMENT ON COLUMN project_plan.register_time IS '方案登记时间';
|
||
COMMENT ON COLUMN project_plan.plan_desc IS '方案说明';
|
||
COMMENT ON COLUMN project_plan.plan_file_url IS '方案最终文件';
|
||
COMMENT ON COLUMN project_plan.status IS '状态：0草稿 1已确认';
|
||
COMMENT ON COLUMN project_plan.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_plan_customer ON project_plan (customer_id);
|
||
|
||
-- ============================================================
|
||
-- 5. 项目方案智电设备明细表 project_plan_detail
|
||
-- equip_id / smart_device_id 仅预留数值列，不建外键
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS project_plan_detail (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
plan_id BIGINT NOT NULL REFERENCES project_plan(id) ON DELETE CASCADE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
equip_name VARCHAR(128),
|
||
equip_id BIGINT,
|
||
smart_device_id BIGINT,
|
||
quantity NUMERIC(10,2) NOT NULL DEFAULT 1,
|
||
remark TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
|
||
-- 兼容已有表：若 project_plan_detail 表已存在（旧脚本建表）则补齐 equip_name 列
|
||
DO $$
|
||
BEGIN
|
||
IF NOT EXISTS (
|
||
SELECT 1 FROM information_schema.columns
|
||
WHERE table_name = 'project_plan_detail' AND column_name = 'equip_name'
|
||
) THEN
|
||
ALTER TABLE project_plan_detail ADD COLUMN equip_name VARCHAR(128);
|
||
RAISE NOTICE 'project_plan_detail: 已新增列 equip_name';
|
||
END IF;
|
||
END $$;
|
||
|
||
COMMENT ON TABLE project_plan_detail IS '项目方案智电设备明细信息表';
|
||
COMMENT ON COLUMN project_plan_detail.id IS '自增主键';
|
||
COMMENT ON COLUMN project_plan_detail.plan_id IS '方案 ID（→ project_plan）';
|
||
COMMENT ON COLUMN project_plan_detail.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN project_plan_detail.equip_name IS '设备名称（设备域未实现前为自由文本）';
|
||
COMMENT ON COLUMN project_plan_detail.equip_id IS '客户设备 ID（预留）';
|
||
COMMENT ON COLUMN project_plan_detail.smart_device_id IS '智电设备型号 ID（预留）';
|
||
COMMENT ON COLUMN project_plan_detail.quantity IS '数量';
|
||
COMMENT ON COLUMN project_plan_detail.remark IS '备注';
|
||
COMMENT ON COLUMN project_plan_detail.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_pd_plan ON project_plan_detail (plan_id);
|
||
CREATE INDEX IF NOT EXISTS idx_pd_customer ON project_plan_detail (customer_id);
|
||
|
||
-- ============================================================
|
||
-- 6. 智电项目合同表 contract
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS contract (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
contract_no VARCHAR(64) NOT NULL UNIQUE,
|
||
xt_contract_no VARCHAR(64),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
contract_amount NUMERIC(14,2),
|
||
pay_cycle VARCHAR(32),
|
||
salesman_id BIGINT,
|
||
sign_date DATE,
|
||
contract_desc TEXT,
|
||
start_date DATE,
|
||
end_date DATE,
|
||
plan_id BIGINT REFERENCES project_plan(id),
|
||
contract_file_url VARCHAR(512),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE contract IS '智电项目合同信息表';
|
||
COMMENT ON COLUMN contract.id IS '自增主键';
|
||
COMMENT ON COLUMN contract.contract_no IS '合同单号（唯一）';
|
||
COMMENT ON COLUMN contract.xt_contract_no IS '鑫潼合同号（XT+年份+序号）';
|
||
COMMENT ON COLUMN contract.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN contract.contract_amount IS '合同金额';
|
||
COMMENT ON COLUMN contract.pay_cycle IS '支付周期：按月/按年';
|
||
COMMENT ON COLUMN contract.salesman_id IS '业务员';
|
||
COMMENT ON COLUMN contract.sign_date IS '签订日期';
|
||
COMMENT ON COLUMN contract.contract_desc IS '合同说明';
|
||
COMMENT ON COLUMN contract.start_date IS '开始日期';
|
||
COMMENT ON COLUMN contract.end_date IS '结束日期';
|
||
COMMENT ON COLUMN contract.plan_id IS '方案 ID（→ project_plan）';
|
||
COMMENT ON COLUMN contract.contract_file_url IS '签字盖章电子合同';
|
||
COMMENT ON COLUMN contract.status IS '状态：1有效 0终止';
|
||
COMMENT ON COLUMN contract.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_contract_customer ON contract (customer_id);
|
||
CREATE INDEX IF NOT EXISTS idx_contract_xt ON contract (xt_contract_no);
|
||
|
||
-- ============================================================
|
||
-- 7. 勘察派单表 survey_order
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS survey_order (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
survey_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
surveyor VARCHAR(64),
|
||
worker_id BIGINT,
|
||
survey_time TIMESTAMPTZ,
|
||
remark TEXT,
|
||
status SMALLINT NOT NULL DEFAULT 0,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE survey_order IS '客户勘察派单信息表';
|
||
COMMENT ON COLUMN survey_order.id IS '自增主键';
|
||
COMMENT ON COLUMN survey_order.survey_no IS '勘察单号（唯一）';
|
||
COMMENT ON COLUMN survey_order.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN survey_order.surveyor IS '勘察人（共享电工）';
|
||
COMMENT ON COLUMN survey_order.worker_id IS '共享电工 ID';
|
||
COMMENT ON COLUMN survey_order.survey_time IS '勘察时间';
|
||
COMMENT ON COLUMN survey_order.remark IS '备注';
|
||
COMMENT ON COLUMN survey_order.status IS '状态：0已派发 1已接单 2已完成';
|
||
COMMENT ON COLUMN survey_order.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_survey_customer ON survey_order (customer_id);
|
||
CREATE INDEX IF NOT EXISTS idx_survey_worker ON survey_order (worker_id);
|
||
|
||
-- ============================================================
|
||
-- 8. 项目验收表 acceptance
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS acceptance (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
accept_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
acceptor VARCHAR(64),
|
||
accept_opinion TEXT,
|
||
accept_date DATE,
|
||
installer VARCHAR(64),
|
||
accept_file_url VARCHAR(512),
|
||
contract_id BIGINT REFERENCES contract(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE acceptance IS '项目验收信息表';
|
||
COMMENT ON COLUMN acceptance.id IS '自增主键';
|
||
COMMENT ON COLUMN acceptance.accept_no IS '验收单号（唯一）';
|
||
COMMENT ON COLUMN acceptance.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN acceptance.acceptor IS '验收人';
|
||
COMMENT ON COLUMN acceptance.accept_opinion IS '验收意见';
|
||
COMMENT ON COLUMN acceptance.accept_date IS '验收日期';
|
||
COMMENT ON COLUMN acceptance.installer IS '安装人';
|
||
COMMENT ON COLUMN acceptance.accept_file_url IS '验收文件电子版';
|
||
COMMENT ON COLUMN acceptance.contract_id IS '合同 ID（→ contract）';
|
||
COMMENT ON COLUMN acceptance.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_accept_customer ON acceptance (customer_id);
|
||
|
||
-- ============================================================
|
||
-- 9. 客户回访信息表 visit_record
|
||
-- ============================================================
|
||
CREATE TABLE IF NOT EXISTS visit_record (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
visit_no VARCHAR(64) NOT NULL UNIQUE,
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
contact_person VARCHAR(64),
|
||
contact_phone VARCHAR(20),
|
||
visitor VARCHAR(64),
|
||
visit_time DATE,
|
||
customer_opinion TEXT,
|
||
photos JSONB,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE visit_record IS '客户回访信息表';
|
||
COMMENT ON COLUMN visit_record.id IS '自增主键';
|
||
COMMENT ON COLUMN visit_record.visit_no IS '回访单号（唯一）';
|
||
COMMENT ON COLUMN visit_record.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN visit_record.contact_person IS '联系人';
|
||
COMMENT ON COLUMN visit_record.contact_phone IS '电话';
|
||
COMMENT ON COLUMN visit_record.visitor IS '回访人';
|
||
COMMENT ON COLUMN visit_record.visit_time IS '回访时间';
|
||
COMMENT ON COLUMN visit_record.customer_opinion IS '客户意见';
|
||
COMMENT ON COLUMN visit_record.photos IS '回访照片（JSONB）';
|
||
COMMENT ON COLUMN visit_record.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_visit_rec_customer ON visit_record (customer_id);
|
||
|
||
COMMIT;
|
||
|
||
-- ============================================================
|
||
-- 验证查询（可选）
|
||
-- ============================================================
|
||
SELECT tablename FROM pg_tables
|
||
WHERE schemaname = 'public'
|
||
AND tablename IN ('customer_visit','customer_building','customer_room',
|
||
'project_plan','project_plan_detail','contract',
|
||
'survey_order','acceptance','visit_record')
|
||
ORDER BY tablename; |