wk
41765cdf44
- 数据库：新增迁移脚本20261011_icc_line_ledger.sql（electric_line/line_room/icc_sync_log三表、菜单61、role_menu(1,61)、定时任务icc_line_sync），同步db_seed.sql - 后端：新增ElectricLine/IccSyncLog实体、查询DTO、线路台账服务与ICC拉取同步服务、/api/electric-lines控制器，接入RBAC（device:list）与定时任务 - 前端：新增智电改造线路台账页面及路由，API封装electricLineApi - ICC同步采用本系统主动拉取模式，失败仅记日志不抛错，接口统一code/message/data格式兼容APP
116 lines
6.2 KiB
PL/PgSQL
116 lines
6.2 KiB
PL/PgSQL
-- 智电改造线路台账（设备监控）：
|
||
-- 1) 幂等建表 electric_line / line_room / icc_sync_log（若 db_init.sql 已建则跳过）
|
||
-- 2) 设备监控(id=4)下新增菜单 61「智电改造线路台账」
|
||
-- 3) ADMIN 角色绑定新菜单
|
||
-- 4) 注册 icc_line_sync 定时任务（默认每 10 分钟）
|
||
BEGIN;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 1) 幂等建表（与 db_init.sql 定义保持一致）
|
||
-- ------------------------------------------------------------
|
||
CREATE TABLE IF NOT EXISTS electric_line (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
line_code VARCHAR(64) NOT NULL UNIQUE,
|
||
equip_id BIGINT NOT NULL REFERENCES customer_equipment(id),
|
||
icc_device_code VARCHAR(128),
|
||
line_name VARCHAR(128),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
building_id BIGINT REFERENCES customer_building(id),
|
||
floor_no VARCHAR(16),
|
||
location VARCHAR(256),
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
synced_at TIMESTAMPTZ,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE electric_line IS '智电改造线路信息表（ICC 同步）';
|
||
COMMENT ON COLUMN electric_line.id IS '自增主键';
|
||
COMMENT ON COLUMN electric_line.line_code IS '线路编码（唯一）';
|
||
COMMENT ON COLUMN electric_line.equip_id IS '客户设备 ID（→ customer_equipment）';
|
||
COMMENT ON COLUMN electric_line.icc_device_code IS 'ICC 智电设备编号';
|
||
COMMENT ON COLUMN electric_line.line_name IS '线路名称（ICC 同步）';
|
||
COMMENT ON COLUMN electric_line.customer_id IS '客户 ID（逻辑隔离键）';
|
||
COMMENT ON COLUMN electric_line.building_id IS '所属建筑 ID（→ customer_building）';
|
||
COMMENT ON COLUMN electric_line.floor_no IS '所在楼层';
|
||
COMMENT ON COLUMN electric_line.location IS '所在位置';
|
||
COMMENT ON COLUMN electric_line.status IS '状态：1在线 0离线';
|
||
COMMENT ON COLUMN electric_line.synced_at IS 'ICC 同步时间';
|
||
COMMENT ON COLUMN electric_line.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_line_customer ON electric_line (customer_id);
|
||
CREATE INDEX IF NOT EXISTS idx_line_equip ON electric_line (equip_id);
|
||
CREATE INDEX IF NOT EXISTS idx_line_icc ON electric_line (icc_device_code);
|
||
|
||
CREATE TABLE IF NOT EXISTS line_room (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
line_id BIGINT NOT NULL REFERENCES electric_line(id) ON DELETE CASCADE,
|
||
room_id BIGINT NOT NULL REFERENCES customer_room(id),
|
||
customer_id BIGINT NOT NULL REFERENCES customer(id),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE line_room IS '线路所属房间关联表';
|
||
COMMENT ON COLUMN line_room.id IS '自增主键';
|
||
COMMENT ON COLUMN line_room.line_id IS '线路 ID（→ electric_line）';
|
||
COMMENT ON COLUMN line_room.room_id IS '房间 ID（→ customer_room）';
|
||
COMMENT ON COLUMN line_room.customer_id IS '客户 ID（→ customer）';
|
||
COMMENT ON COLUMN line_room.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_lr_line ON line_room (line_id);
|
||
CREATE INDEX IF NOT EXISTS idx_lr_room ON line_room (room_id);
|
||
CREATE INDEX IF NOT EXISTS idx_lr_customer ON line_room (customer_id);
|
||
|
||
CREATE TABLE IF NOT EXISTS icc_sync_log (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
sync_type VARCHAR(32) NOT NULL,
|
||
start_time TIMESTAMPTZ,
|
||
end_time TIMESTAMPTZ,
|
||
record_count INT,
|
||
status SMALLINT NOT NULL DEFAULT 1,
|
||
error_message TEXT,
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
||
);
|
||
COMMENT ON TABLE icc_sync_log IS 'ICC 数据同步日志表';
|
||
COMMENT ON COLUMN icc_sync_log.id IS '自增主键';
|
||
COMMENT ON COLUMN icc_sync_log.sync_type IS '同步类型：客户/设备/用电/报警';
|
||
COMMENT ON COLUMN icc_sync_log.start_time IS '开始时间';
|
||
COMMENT ON COLUMN icc_sync_log.end_time IS '结束时间';
|
||
COMMENT ON COLUMN icc_sync_log.record_count IS '同步记录数';
|
||
COMMENT ON COLUMN icc_sync_log.status IS '状态：1成功 0失败';
|
||
COMMENT ON COLUMN icc_sync_log.error_message IS '错误信息';
|
||
COMMENT ON COLUMN icc_sync_log.created_at IS '创建时间';
|
||
CREATE INDEX IF NOT EXISTS idx_icc_sync_type ON icc_sync_log (sync_type);
|
||
CREATE INDEX IF NOT EXISTS idx_icc_sync_time ON icc_sync_log (created_at);
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 2) 设备监控(id=4)下新增菜单 61「智电改造线路台账」（sort 2，排在设备管理台账之后）
|
||
-- ------------------------------------------------------------
|
||
INSERT INTO sys_menu (id, parent_id, menu_name, menu_path, component, icon, sort_order, menu_type, permission, visible) VALUES
|
||
(61, 4, '智电改造线路台账', '/device/line', 'device/line/index', 'ApartmentOutlined', 2, 2, 'device:list', 1)
|
||
ON CONFLICT (id) DO UPDATE SET
|
||
parent_id = EXCLUDED.parent_id,
|
||
menu_name = EXCLUDED.menu_name,
|
||
menu_path = EXCLUDED.menu_path,
|
||
component = EXCLUDED.component,
|
||
icon = EXCLUDED.icon,
|
||
sort_order = EXCLUDED.sort_order,
|
||
menu_type = EXCLUDED.menu_type,
|
||
permission = EXCLUDED.permission,
|
||
visible = EXCLUDED.visible;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 3) 角色-菜单绑定：ADMIN(role_id=1) 拥有 61
|
||
-- ------------------------------------------------------------
|
||
INSERT INTO sys_role_menu (role_id, menu_id) VALUES
|
||
(1, 61)
|
||
ON CONFLICT (role_id, menu_id) DO NOTHING;
|
||
|
||
-- ------------------------------------------------------------
|
||
-- 4) 定时任务种子：icc_line_sync（默认每 10 分钟，可按 job_key 幂等）
|
||
-- ------------------------------------------------------------
|
||
INSERT INTO sys_job (job_key, job_name, cron_expression, params, retry_times, retry_interval, status, remark) VALUES
|
||
('icc_line_sync', '智电线路同步', '*/10 * * * *', '{}'::jsonb, 1, 300, 1,
|
||
'从 ICC 平台拉取智电改造线路并按 line_code 幂等 upsert（默认每 10 分钟）')
|
||
ON CONFLICT (job_key) DO NOTHING;
|
||
|
||
-- 重置序列（覆盖到 61）
|
||
SELECT setval('sys_menu_id_seq', GREATEST((SELECT MAX(id) FROM sys_menu), 61), true);
|
||
|
||
COMMIT;
|