-- =============================================
|
-- OA 通知公告模块初始化
|
-- =============================================
|
|
CREATE TABLE IF NOT EXISTS oa_notice (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
|
title VARCHAR(50) NOT NULL COMMENT '公告标题',
|
content TEXT NOT NULL COMMENT '公告内容',
|
type TINYINT NOT NULL COMMENT '公告类型（1通知 2公告）',
|
status TINYINT NOT NULL DEFAULT 0 COMMENT '状态（0草稿 1已发布 2已撤销）',
|
send_scope TINYINT NOT NULL COMMENT '发送范围（1通知所有人 2指定人）',
|
is_top TINYINT NOT NULL DEFAULT 0 COMMENT '是否置顶（0否 1是）',
|
publish_user_id BIGINT COMMENT '发布人ID',
|
publish_time DATETIME COMMENT '发布时间',
|
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
INDEX idx_type (type),
|
INDEX idx_status (status),
|
INDEX idx_publish_time (publish_time),
|
INDEX idx_is_top (is_top)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OA通知公告表';
|
|
CREATE TABLE IF NOT EXISTS oa_notice_user (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
|
notice_id BIGINT NOT NULL COMMENT '通知公告ID',
|
user_id BIGINT NOT NULL COMMENT '指定用户ID',
|
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
INDEX idx_notice_id (notice_id),
|
INDEX idx_user_id (user_id)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OA通知公告指定人关联表';
|
|
CREATE TABLE IF NOT EXISTS oa_notice_read (
|
id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
|
notice_id BIGINT NOT NULL COMMENT '通知公告ID',
|
user_id BIGINT NOT NULL COMMENT '用户ID',
|
read_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '阅读时间',
|
creator VARCHAR(64) DEFAULT '' COMMENT '创建者',
|
create_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
updater VARCHAR(64) DEFAULT '' COMMENT '更新者',
|
update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
deleted BIT DEFAULT 0 COMMENT '是否删除',
|
UNIQUE KEY uk_notice_user (notice_id, user_id),
|
INDEX idx_notice_id (notice_id),
|
INDEX idx_user_id (user_id)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='OA通知公告已读记录表';
|
|
-- 系统通知模板（用于OA通知公告发布时发送站内信）
|
INSERT INTO system_notify_template (name, code, nickname, content, type, params, status, creator, create_time, updater, update_time, deleted)
|
VALUES ('OA通知公告提醒', 'oa-notice-publish', '系统通知', '您有一条新的通知公告：{title}', 1, '["title"]', 0, '', NOW(), '', NOW(), 0)
|
ON DUPLICATE KEY UPDATE id = id;
|
