363 lines
22 KiB
SQL
363 lines
22 KiB
SQL
-- 项目经费、库存来源批次、跨项目使用及 AI 指令审计。
|
||
-- 用于从当前 WMS 结构升级。所有增量字段和索引均先检查再创建，允许在中断后重新执行。
|
||
-- 执行前必须显式确认 database() 是目标库，并完成数据库备份。
|
||
|
||
create table if not exists project_info (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
project_code varchar(64) not null comment '项目编码',
|
||
project_name varchar(200) not null comment '项目名称',
|
||
manager_id bigint(20) default null comment '项目负责人用户ID',
|
||
dept_id bigint(20) default null comment '归属部门ID',
|
||
start_date date default null comment '开始日期',
|
||
end_date date default null comment '结束日期',
|
||
project_status tinyint(1) not null default 1 comment '状态：0筹备 1进行中 2已完成 3已归档',
|
||
currency varchar(8) not null default 'CNY' comment '币种',
|
||
remark varchar(500) default null comment '备注',
|
||
create_by varchar(64) default null comment '创建人',
|
||
create_time datetime default null comment '创建时间',
|
||
update_by varchar(64) default null comment '更新人',
|
||
update_time datetime default null comment '更新时间',
|
||
primary key (id),
|
||
unique key uk_project_info_code (project_code),
|
||
key idx_project_info_name (project_name),
|
||
key idx_project_info_manager (manager_id),
|
||
key idx_project_info_status (project_status)
|
||
) engine=innodb default charset=utf8mb4 comment='项目主表';
|
||
|
||
create table if not exists project_member (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
project_id bigint(20) not null comment '项目ID',
|
||
user_id bigint(20) not null comment '用户ID',
|
||
member_role varchar(32) not null default 'MEMBER' comment '成员角色',
|
||
create_by varchar(64) default null comment '创建人',
|
||
create_time datetime default null comment '创建时间',
|
||
primary key (id),
|
||
unique key uk_project_member (project_id, user_id),
|
||
key idx_project_member_user (user_id)
|
||
) engine=innodb default charset=utf8mb4 comment='项目成员表';
|
||
|
||
create table if not exists expense_category (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
category_code varchar(64) not null comment '经费科目编码',
|
||
category_name varchar(100) not null comment '经费科目名称',
|
||
sort_order int not null default 0 comment '显示顺序',
|
||
status tinyint(1) not null default 0 comment '状态：0正常 1停用',
|
||
remark varchar(500) default null comment '备注',
|
||
create_by varchar(64) default null comment '创建人',
|
||
create_time datetime default null comment '创建时间',
|
||
update_by varchar(64) default null comment '更新人',
|
||
update_time datetime default null comment '更新时间',
|
||
primary key (id),
|
||
unique key uk_expense_category_code (category_code),
|
||
unique key uk_expense_category_name (category_name)
|
||
) engine=innodb default charset=utf8mb4 comment='项目经费科目';
|
||
|
||
create table if not exists project_budget (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
project_id bigint(20) not null comment '项目ID',
|
||
expense_category_id bigint(20) not null comment '经费科目ID',
|
||
budget_amount decimal(18,2) not null default 0.00 comment '预算金额',
|
||
committed_amount decimal(18,2) not null default 0.00 comment '已占用金额',
|
||
spent_amount decimal(18,2) not null default 0.00 comment '已支出金额',
|
||
alert_percent decimal(5,2) not null default 80.00 comment '预警百分比',
|
||
version int not null default 0 comment '乐观锁版本',
|
||
remark varchar(500) default null comment '备注',
|
||
create_by varchar(64) default null comment '创建人',
|
||
create_time datetime default null comment '创建时间',
|
||
update_by varchar(64) default null comment '更新人',
|
||
update_time datetime default null comment '更新时间',
|
||
primary key (id),
|
||
unique key uk_project_budget (project_id, expense_category_id),
|
||
key idx_project_budget_category (expense_category_id)
|
||
) engine=innodb default charset=utf8mb4 comment='项目分科目预算';
|
||
|
||
create table if not exists project_fund_ledger (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
ledger_no varchar(64) not null comment '流水号',
|
||
project_id bigint(20) not null comment '项目ID',
|
||
expense_category_id bigint(20) not null comment '经费科目ID',
|
||
biz_type varchar(32) not null comment '业务类型',
|
||
biz_id bigint(20) default null comment '业务主键',
|
||
biz_no varchar(64) default null comment '业务单号',
|
||
budget_delta decimal(18,2) not null default 0.00 comment '预算变化额',
|
||
committed_delta decimal(18,2) not null default 0.00 comment '占用变化额',
|
||
spent_delta decimal(18,2) not null default 0.00 comment '支出变化额',
|
||
balance_budget decimal(18,2) not null default 0.00 comment '变更后预算额',
|
||
balance_committed decimal(18,2) not null default 0.00 comment '变更后占用额',
|
||
balance_spent decimal(18,2) not null default 0.00 comment '变更后支出额',
|
||
idempotency_key varchar(128) default null comment '幂等键',
|
||
operator varchar(64) default null comment '操作人',
|
||
remark varchar(500) default null comment '说明',
|
||
create_time datetime not null default current_timestamp comment '创建时间',
|
||
primary key (id),
|
||
unique key uk_project_fund_ledger_no (ledger_no),
|
||
unique key uk_project_fund_idempotency (idempotency_key),
|
||
key idx_project_fund_project_category (project_id, expense_category_id, create_time),
|
||
key idx_project_fund_biz (biz_type, biz_id)
|
||
) engine=innodb default charset=utf8mb4 comment='项目经费不可变流水';
|
||
|
||
create table if not exists stock_lot (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
lot_no varchar(64) not null comment '库存批次号',
|
||
area_id bigint(20) not null comment '库区ID',
|
||
product_id bigint(20) not null comment '产品ID',
|
||
source_project_id bigint(20) not null comment '采购或来源项目ID',
|
||
expense_category_id bigint(20) default null comment '来源经费科目ID',
|
||
purchase_request_id bigint(20) default null comment '采购申请ID',
|
||
arrival_id bigint(20) default null comment '到货ID',
|
||
in_record_id bigint(20) default null comment '入库记录ID',
|
||
received_quantity decimal(16,3) not null comment '入库数量',
|
||
available_quantity decimal(16,3) not null comment '可用数量',
|
||
unit_cost decimal(18,2) not null default 0.00 comment '单位成本',
|
||
lot_status tinyint(1) not null default 0 comment '状态：0可用 1用尽 2冻结',
|
||
remark varchar(500) default null comment '备注',
|
||
create_by varchar(64) default null comment '创建人',
|
||
create_time datetime default null comment '创建时间',
|
||
update_by varchar(64) default null comment '更新人',
|
||
update_time datetime default null comment '更新时间',
|
||
primary key (id),
|
||
unique key uk_stock_lot_no (lot_no),
|
||
unique key uk_stock_lot_in_record (in_record_id),
|
||
key idx_stock_lot_available (area_id, product_id, lot_status, create_time),
|
||
key idx_stock_lot_source_project (source_project_id),
|
||
key idx_stock_lot_purchase (purchase_request_id)
|
||
) engine=innodb default charset=utf8mb4 comment='库存来源批次';
|
||
|
||
create table if not exists out_record_lot (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
out_record_id bigint(20) not null comment '出库记录ID',
|
||
stock_lot_id bigint(20) not null comment '库存批次ID',
|
||
source_project_id bigint(20) not null comment '来源项目ID',
|
||
usage_project_id bigint(20) not null comment '使用项目ID',
|
||
quantity decimal(16,3) not null comment '出库数量',
|
||
unit_cost decimal(18,2) not null default 0.00 comment '单位成本',
|
||
amount decimal(18,2) not null default 0.00 comment '使用成本',
|
||
create_time datetime not null default current_timestamp comment '创建时间',
|
||
primary key (id),
|
||
key idx_out_record_lot_out (out_record_id),
|
||
key idx_out_record_lot_stock (stock_lot_id),
|
||
key idx_out_record_lot_project_flow (source_project_id, usage_project_id)
|
||
) engine=innodb default charset=utf8mb4 comment='出库库存批次分摊';
|
||
|
||
create table if not exists ai_command (
|
||
id bigint(20) not null auto_increment comment '主键',
|
||
user_id bigint(20) not null comment '发起用户ID',
|
||
username varchar(64) not null comment '发起账号',
|
||
action varchar(64) not null comment '动作',
|
||
required_permission varchar(128) default null comment '所需权限',
|
||
request_text varchar(1000) default null comment '原始文本',
|
||
plan_json json not null comment '服务器确认的执行计划',
|
||
result_json json default null comment '执行结果',
|
||
command_status varchar(20) not null comment 'WAIT_CONFIRM/EXECUTING/SUCCEEDED/REJECTED/EXPIRED/FAILED',
|
||
expires_at datetime not null comment '过期时间',
|
||
confirmed_at datetime default null comment '人工确认时间',
|
||
executed_at datetime default null comment '执行完成时间',
|
||
error_message varchar(1000) default null comment '失败原因',
|
||
create_time datetime not null default current_timestamp comment '创建时间',
|
||
update_time datetime default null comment '更新时间',
|
||
primary key (id),
|
||
key idx_ai_command_user_status (user_id, command_status, create_time),
|
||
key idx_ai_command_expire (expires_at)
|
||
) engine=innodb default charset=utf8mb4 comment='AI 待确认业务指令';
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'purchase_request' and column_name = 'project_id');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table purchase_request add column project_id bigint(20) default null comment ''采购项目ID'' after purchase_batch_id',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'purchase_request' and column_name = 'expense_category_id');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table purchase_request add column expense_category_id bigint(20) default null comment ''经费科目ID'' after project_id',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'purchase_request' and column_name = 'estimated_unit_price');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table purchase_request add column estimated_unit_price decimal(18,2) default null comment ''预计含税单价'' after quantity',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'purchase_request' and column_name = 'estimated_amount');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table purchase_request add column estimated_amount decimal(18,2) default null comment ''预计总金额'' after estimated_unit_price',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @idx_exists = (select count(*) from information_schema.statistics
|
||
where table_schema = database() and table_name = 'purchase_request'
|
||
and index_name = 'idx_purchase_request_project');
|
||
set @ddl = if(@idx_exists = 0,
|
||
'alter table purchase_request add key idx_purchase_request_project (project_id, expense_category_id)',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'purchase_arrival' and column_name = 'actual_unit_price');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table purchase_arrival add column actual_unit_price decimal(18,2) default null comment ''本批实际含税单价'' after arrival_quantity',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'purchase_arrival' and column_name = 'actual_amount');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table purchase_arrival add column actual_amount decimal(18,2) default null comment ''本批实际金额'' after actual_unit_price',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'in_record' and column_name = 'source_project_id');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table in_record add column source_project_id bigint(20) default null comment ''来源项目ID'' after arrival_id',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'in_record' and column_name = 'expense_category_id');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table in_record add column expense_category_id bigint(20) default null comment ''来源经费科目ID'' after source_project_id',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @idx_exists = (select count(*) from information_schema.statistics
|
||
where table_schema = database() and table_name = 'in_record'
|
||
and index_name = 'idx_in_record_source_project');
|
||
set @ddl = if(@idx_exists = 0,
|
||
'alter table in_record add key idx_in_record_source_project (source_project_id)',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'out_record' and column_name = 'usage_project_id');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table out_record add column usage_project_id bigint(20) default null comment ''使用项目ID'' after product_id',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'out_record' and column_name = 'cross_project_flag');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table out_record add column cross_project_flag tinyint(1) not null default 0 comment ''是否跨项目使用'' after source_no',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @column_exists = (select count(*) from information_schema.columns
|
||
where table_schema = database() and table_name = 'out_record' and column_name = 'cross_project_reason');
|
||
set @ddl = if(@column_exists = 0,
|
||
'alter table out_record add column cross_project_reason varchar(500) default null comment ''跨项目使用原因'' after cross_project_flag',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
set @idx_exists = (select count(*) from information_schema.statistics
|
||
where table_schema = database() and table_name = 'out_record'
|
||
and index_name = 'idx_out_record_usage_project');
|
||
set @ddl = if(@idx_exists = 0,
|
||
'alter table out_record add key idx_out_record_usage_project (usage_project_id)',
|
||
'select 1');
|
||
prepare stmt from @ddl; execute stmt; deallocate prepare stmt;
|
||
|
||
insert into project_info(project_code, project_name, manager_id, project_status, currency, remark, create_by, create_time)
|
||
select 'LEGACY', '历史待归属项目', 1, 3, 'CNY', '升级前数据统一归入该项目，后续不得虚构原始归属', 'migration', sysdate()
|
||
where not exists (select 1 from project_info where project_code = 'LEGACY');
|
||
|
||
insert into expense_category(category_code, category_name, sort_order, status, remark, create_by, create_time)
|
||
select 'MATERIAL', '材料费', 10, 0, '材料及耗材', 'migration', sysdate()
|
||
where not exists (select 1 from expense_category where category_code = 'MATERIAL');
|
||
insert into expense_category(category_code, category_name, sort_order, status, remark, create_by, create_time)
|
||
select 'EQUIPMENT', '设备费', 20, 0, '仪器设备购置', 'migration', sysdate()
|
||
where not exists (select 1 from expense_category where category_code = 'EQUIPMENT');
|
||
insert into expense_category(category_code, category_name, sort_order, status, remark, create_by, create_time)
|
||
select 'TRIAL', '试制费', 30, 0, '试制及加工费用', 'migration', sysdate()
|
||
where not exists (select 1 from expense_category where category_code = 'TRIAL');
|
||
insert into expense_category(category_code, category_name, sort_order, status, remark, create_by, create_time)
|
||
select 'OTHER', '其他费用', 90, 0, '其他项目费用', 'migration', sysdate()
|
||
where not exists (select 1 from expense_category where category_code = 'OTHER');
|
||
insert into expense_category(category_code, category_name, sort_order, status, remark, create_by, create_time)
|
||
select 'LEGACY', '历史待归属', 99, 0, '升级前无法确认的经费科目', 'migration', sysdate()
|
||
where not exists (select 1 from expense_category where category_code = 'LEGACY');
|
||
|
||
set @legacy_project_id = (select id from project_info where project_code = 'LEGACY' limit 1);
|
||
set @legacy_category_id = (select id from expense_category where category_code = 'LEGACY' limit 1);
|
||
|
||
insert ignore into project_member(project_id, user_id, member_role, create_by, create_time)
|
||
values(@legacy_project_id, 1, 'MANAGER', 'migration', sysdate());
|
||
|
||
update purchase_request
|
||
set project_id = @legacy_project_id,
|
||
expense_category_id = @legacy_category_id,
|
||
estimated_unit_price = coalesce(estimated_unit_price, 0),
|
||
estimated_amount = coalesce(estimated_amount, 0)
|
||
where project_id is null;
|
||
|
||
update in_record
|
||
set source_project_id = @legacy_project_id,
|
||
expense_category_id = @legacy_category_id
|
||
where source_project_id is null;
|
||
|
||
update out_record
|
||
set usage_project_id = @legacy_project_id
|
||
where usage_project_id is null;
|
||
|
||
insert ignore into stock_lot(
|
||
lot_no, area_id, product_id, source_project_id, expense_category_id,
|
||
received_quantity, available_quantity, unit_cost, lot_status, remark, create_by, create_time
|
||
)
|
||
select concat('LEGACY-', si.id), si.area_id, si.product_id, @legacy_project_id, @legacy_category_id,
|
||
si.stock_num, si.stock_num, 0,
|
||
case when si.stock_num > 0 then 0 else 1 end,
|
||
'由升级时库存汇总生成，原采购来源未知', 'migration', coalesce(si.create_time, sysdate())
|
||
from stock_info si
|
||
where not exists (select 1 from stock_lot sl where sl.lot_no = concat('LEGACY-', si.id));
|
||
|
||
-- 项目管理菜单及权限。固定使用 2100-2113，当前项目菜单最大编号为 2042。
|
||
insert into sys_menu(menu_id, menu_name, parent_id, order_num, path, component, query, route_name,
|
||
is_frame, is_cache, menu_type, visible, status, perms, icon,
|
||
create_by, create_time, update_by, update_time, remark)
|
||
select 2100, '项目管理', 0, 7, 'project', null, '', '', 1, 0, 'M', '0', '0', '', 'money',
|
||
'admin', sysdate(), '', null, '项目、经费和跨项目使用管理'
|
||
where not exists (select 1 from sys_menu where menu_id = 2100);
|
||
|
||
insert into sys_menu values
|
||
(2101, '项目与经费', 2100, 1, 'info', 'project/info/index', '', '', 1, 0, 'C', '0', '0', 'project:project:list', 'tree', 'admin', sysdate(), '', null, '项目及经费预算管理'),
|
||
(2102, '项目查询', 2101, 1, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:project:query', '#', 'admin', sysdate(), '', null, ''),
|
||
(2103, '项目新增', 2101, 2, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:project:add', '#', 'admin', sysdate(), '', null, ''),
|
||
(2104, '项目修改', 2101, 3, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:project:edit', '#', 'admin', sysdate(), '', null, ''),
|
||
(2105, '项目删除', 2101, 4, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:project:remove', '#', 'admin', sysdate(), '', null, ''),
|
||
(2106, '项目成员维护', 2101, 5, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:project:member', '#', 'admin', sysdate(), '', null, ''),
|
||
(2107, '项目经费查询', 2101, 6, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:budget:list', '#', 'admin', sysdate(), '', null, ''),
|
||
(2108, '项目经费维护', 2101, 7, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:budget:edit', '#', 'admin', sysdate(), '', null, ''),
|
||
(2109, '经费科目维护', 2101, 8, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:category:edit', '#', 'admin', sysdate(), '', null, ''),
|
||
(2110, '项目总览权限', 2101, 9, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:project:manage', '#', 'admin', sysdate(), '', null, ''),
|
||
(2111, '项目物资使用', 2101, 10, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:inventory:use', '#', 'admin', sysdate(), '', null, ''),
|
||
(2112, '跨项目使用', 2101, 11, '#', '', '', '', 1, 0, 'F', '0', '0', 'project:inventory:cross-use', '#', 'admin', sysdate(), '', null, ''),
|
||
(2113, 'AI助手使用', 2101, 12, '#', '', '', '', 1, 0, 'F', '1', '0', 'ai:assistant:use', '#', 'admin', sysdate(), '', null, '所有正常角色默认拥有，仅控制入口，具体动作仍校验业务权限')
|
||
on duplicate key update menu_id = values(menu_id);
|
||
|
||
-- 所有现有正常角色均可进入 AI 助手；业务查询和写入仍由原仓库权限控制。
|
||
insert ignore into sys_role_menu(role_id, menu_id)
|
||
select role_id, 2113 from sys_role where status = '0';
|
||
|
||
-- 延续现有业务角色能力：采购和库存角色可查看自己参与的项目及经费；原有出库角色获得项目物资使用权限。
|
||
insert ignore into sys_role_menu(role_id, menu_id)
|
||
select distinct role_id, target_menu_id
|
||
from (
|
||
select rm.role_id, targets.target_menu_id
|
||
from sys_role_menu rm
|
||
join sys_menu source_menu on source_menu.menu_id = rm.menu_id
|
||
join (
|
||
select 2100 target_menu_id union all select 2101 union all select 2102 union all select 2107
|
||
) targets
|
||
where source_menu.perms in ('warehouse:purchase:add', 'warehouse:purchase:manage', 'warehouse:stock:list')
|
||
) role_project_menu;
|
||
|
||
insert ignore into sys_role_menu(role_id, menu_id)
|
||
select distinct rm.role_id, 2111
|
||
from sys_role_menu rm
|
||
join sys_menu m on m.menu_id = rm.menu_id
|
||
where m.perms in ('warehouse:stock:out', 'warehouse:out:add');
|