-- ============================================================================
|
-- 康意智能 mom-kyzn 数据库修复总脚本
|
-- 生成日期：2026-10-30
|
--
|
-- 用途：修复全量接口测试发现的 500 系统异常所依赖的数据库结构缺失
|
-- 内容：
|
-- 第一批 缺失表/列 + mes_wm_return_issue.type 类型修正
|
-- 第二批 17 个缺失列
|
-- 第三批 29 个缺失列（按 DO 实体自动比对生成）
|
-- 第四批 16 张缺失表 + tenant_id
|
--
|
-- 特性：全部语句幂等（IF NOT EXISTS / information_schema 判断），可重复执行
|
-- 前提：MySQL 8.x，库名 mom-kyzn
|
-- 注意：包含 ALTER TABLE / CREATE TABLE，请先备份，业务低峰期执行
|
-- ============================================================================
|
|
SET NAMES utf8mb4;
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
-- ############################################################################
|
-- ## 来源文件：fix_api_500_schema_20261030.sql
|
-- ############################################################################
|
-- ============================================================================
|
-- 接口 500 修复：补齐缺失表 / 缺失列 / 类型不一致
|
--
|
-- 背景：全量接口探测发现 60 个接口返回 code=500，根因分三类：
|
-- 1. 表不存在（member_* 9 张、hrm_social_security_config）
|
-- 2. 列不存在（盘点、考勤、外协、装箱等）
|
-- 3. 列类型与实体不匹配（mes_wm_return_issue.type 为 varchar，实体是 Integer）
|
--
|
-- 特性：幂等。可重复执行，已存在的表/列不会报错。
|
-- 适用：MySQL 8.x（库名 mom-kyzn）
|
-- ============================================================================
|
|
-- ---------------------------------------------------------------------------
|
-- 一、补齐缺失的列
|
-- ---------------------------------------------------------------------------
|
|
-- 1.1 MES 盘点任务：缺 taking_date
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task'
|
AND column_name = 'taking_date');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `taking_date` date DEFAULT NULL COMMENT ''盘点日期'' AFTER `name`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.2 MES 盘点任务行：缺 batch_code
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_line'
|
AND column_name = 'batch_code');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_stock_taking_task_line` ADD COLUMN `batch_code` varchar(64) DEFAULT NULL COMMENT ''批次号'' AFTER `batch_id`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.3 MES 盘点结果：缺 batch_code
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_result'
|
AND column_name = 'batch_code');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_stock_taking_task_result` ADD COLUMN `batch_code` varchar(64) DEFAULT NULL COMMENT ''批次号'' AFTER `batch_id`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.4 HRM 考勤规则：缺 location_name
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule'
|
AND column_name = 'location_name');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_rule` ADD COLUMN `location_name` varchar(200) DEFAULT NULL COMMENT ''打卡地点名称''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.5 HRM 考勤记录：缺打卡经纬度
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_in_longitude');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_longitude` decimal(10,6) DEFAULT NULL COMMENT ''上班打卡经度''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_in_latitude');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_latitude` decimal(10,6) DEFAULT NULL COMMENT ''上班打卡纬度''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_out_longitude');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_longitude` decimal(10,6) DEFAULT NULL COMMENT ''下班打卡经度''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_out_latitude');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_latitude` decimal(10,6) DEFAULT NULL COMMENT ''下班打卡纬度''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.6 MES 外协发料行：缺 material_stock_id
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_issue_line'
|
AND column_name = 'material_stock_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_outsource_issue_line` ADD COLUMN `material_stock_id` bigint DEFAULT NULL COMMENT ''库存记录编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.7 MES 外协入库行：缺 batch_id / batch_code
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line'
|
AND column_name = 'batch_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `batch_id` bigint DEFAULT NULL COMMENT ''批次编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line'
|
AND column_name = 'batch_code');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `batch_code` varchar(64) DEFAULT NULL COMMENT ''批次号'' AFTER `batch_id`',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.8 MES 装箱明细：缺 work_order_id
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_package_line'
|
AND column_name = 'work_order_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_package_line` ADD COLUMN `work_order_id` bigint DEFAULT NULL COMMENT ''生产工单编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------------------------------------
|
-- 二、修正类型不一致：mes_wm_return_issue.type
|
-- 建表时为 varchar(255)，实体为 Integer，历史数据存了中文，读取时报
|
-- "Cannot determine value type from string"
|
-- ---------------------------------------------------------------------------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_issue'
|
AND column_name = 'type' AND data_type IN ('varchar', 'char', 'text'));
|
SET @sql := IF(@exists > 0,
|
'UPDATE `mes_wm_return_issue` SET `type` = CASE `type`
|
WHEN ''余料退料'' THEN ''1''
|
WHEN ''不良退料'' THEN ''2''
|
WHEN ''其他退料'' THEN ''3''
|
ELSE `type` END
|
WHERE `type` IS NOT NULL AND `type` NOT REGEXP ''^[0-9]+$''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_return_issue'
|
AND column_name = 'type' AND data_type IN ('varchar', 'char', 'text'));
|
SET @sql := IF(@exists > 0,
|
'ALTER TABLE `mes_wm_return_issue` MODIFY COLUMN `type` int DEFAULT NULL COMMENT ''退料类型：1-余料退料 2-不良退料 3-其他退料''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------------------------------------
|
-- 三、补齐缺失的表（member 模块 9 张 + HRM 社保配置 1 张）
|
-- ---------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `member_user` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`mobile` varchar(11) NOT NULL COMMENT '手机号',
|
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
|
`password` varchar(100) NOT NULL DEFAULT '' COMMENT '密码',
|
`status` tinyint NOT NULL COMMENT '状态',
|
`register_ip` varchar(32) NOT NULL COMMENT '注册 IP',
|
`register_terminal` int DEFAULT NULL COMMENT '注册终端',
|
`login_ip` varchar(50) DEFAULT '' COMMENT '最后登录IP',
|
`login_date` datetime DEFAULT NULL COMMENT '最后登录时间',
|
`nickname` varchar(30) NOT NULL DEFAULT '' COMMENT '用户昵称',
|
`avatar` varchar(512) NOT NULL DEFAULT '' COMMENT '头像',
|
`name` varchar(30) DEFAULT NULL COMMENT '真实名字',
|
`sex` tinyint DEFAULT NULL COMMENT '性别',
|
`birthday` datetime DEFAULT NULL COMMENT '出生日期',
|
`area_id` int DEFAULT NULL COMMENT '所在地',
|
`mark` varchar(255) DEFAULT NULL COMMENT '用户备注',
|
`point` int DEFAULT 0 COMMENT '积分',
|
`tag_ids` varchar(255) DEFAULT NULL COMMENT '用户标签编号列表，逗号分隔',
|
`level_id` bigint DEFAULT NULL COMMENT '等级编号',
|
`experience` int DEFAULT NULL COMMENT '经验',
|
`group_id` bigint DEFAULT NULL COMMENT '用户分组编号',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`),
|
KEY `idx_mobile` (`mobile`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员用户';
|
|
CREATE TABLE IF NOT EXISTS `member_tag` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`name` varchar(30) NOT NULL COMMENT '标签名称',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员标签';
|
|
CREATE TABLE IF NOT EXISTS `member_level` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`name` varchar(30) NOT NULL COMMENT '等级名称',
|
`level` int NOT NULL COMMENT '等级',
|
`experience` int NOT NULL COMMENT '升级经验',
|
`discount_percent` int NOT NULL COMMENT '折扣百分比',
|
`icon` varchar(255) NOT NULL COMMENT '图标',
|
`background_url` varchar(255) NOT NULL COMMENT '背景图',
|
`status` tinyint NOT NULL DEFAULT 0 COMMENT '状态',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员等级';
|
|
CREATE TABLE IF NOT EXISTS `member_group` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`name` varchar(30) NOT NULL COMMENT '分组名称',
|
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
`status` tinyint NOT NULL DEFAULT 0 COMMENT '状态',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员分组';
|
|
CREATE TABLE IF NOT EXISTS `member_config` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`point_trade_deduct_enable` bit(1) NOT NULL DEFAULT b'0' COMMENT '积分抵扣开关',
|
`point_trade_deduct_unit_price` int DEFAULT NULL COMMENT '积分抵扣单价',
|
`point_trade_deduct_max_price` int DEFAULT NULL COMMENT '积分抵扣最大金额',
|
`point_trade_give_point` int DEFAULT NULL COMMENT '交易赠送积分',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员配置';
|
|
CREATE TABLE IF NOT EXISTS `member_experience_record` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`user_id` bigint NOT NULL COMMENT '用户编号',
|
`biz_type` int NOT NULL COMMENT '业务类型',
|
`biz_id` varchar(64) NOT NULL COMMENT '业务编号',
|
`title` varchar(255) NOT NULL COMMENT '标题',
|
`description` varchar(255) NOT NULL COMMENT '描述',
|
`experience` int NOT NULL COMMENT '经验',
|
`total_experience` int NOT NULL COMMENT '变更后的经验',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`),
|
KEY `idx_user_id` (`user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员经验记录';
|
|
CREATE TABLE IF NOT EXISTS `member_level_record` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`user_id` bigint NOT NULL COMMENT '用户编号',
|
`level_id` bigint NOT NULL COMMENT '等级编号',
|
`level` int NOT NULL COMMENT '等级',
|
`discount_percent` int NOT NULL COMMENT '折扣百分比',
|
`experience` int NOT NULL COMMENT '经验',
|
`user_experience` int NOT NULL COMMENT '用户经验',
|
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
`description` varchar(255) DEFAULT NULL COMMENT '描述',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`),
|
KEY `idx_user_id` (`user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员等级记录';
|
|
CREATE TABLE IF NOT EXISTS `member_point_record` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`user_id` bigint NOT NULL COMMENT '用户编号',
|
`biz_id` varchar(64) NOT NULL COMMENT '业务编号',
|
`biz_type` int NOT NULL COMMENT '业务类型',
|
`title` varchar(255) NOT NULL COMMENT '标题',
|
`description` varchar(255) NOT NULL COMMENT '描述',
|
`point` int NOT NULL COMMENT '积分',
|
`total_point` int NOT NULL COMMENT '变更后的积分',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`),
|
KEY `idx_user_id` (`user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='会员积分记录';
|
|
CREATE TABLE IF NOT EXISTS `member_sign_in_config` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`day` int NOT NULL COMMENT '第几天',
|
`point` int NOT NULL COMMENT '赠送积分',
|
`experience` int NOT NULL COMMENT '赠送经验',
|
`status` tinyint NOT NULL DEFAULT 0 COMMENT '状态',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到配置';
|
|
CREATE TABLE IF NOT EXISTS `member_sign_in_record` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`user_id` bigint NOT NULL COMMENT '用户编号',
|
`day` int NOT NULL COMMENT '第几天',
|
`point` int NOT NULL COMMENT '赠送积分',
|
`experience` int NOT NULL COMMENT '赠送经验',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`),
|
KEY `idx_user_id` (`user_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='签到记录';
|
|
CREATE TABLE IF NOT EXISTS `hrm_social_security_config` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`name` varchar(100) NOT NULL COMMENT '方案名称',
|
`effective_date` date DEFAULT NULL COMMENT '生效日期',
|
`pension_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '养老-公司比例',
|
`pension_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '养老-个人比例',
|
`medical_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '医疗-公司比例',
|
`medical_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '医疗-个人比例',
|
`unemployment_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '失业-公司比例',
|
`unemployment_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '失业-个人比例',
|
`work_injury_ratio` decimal(10,4) DEFAULT NULL COMMENT '工伤-公司比例',
|
`maternity_ratio` decimal(10,4) DEFAULT NULL COMMENT '生育-公司比例',
|
`housing_fund_company_ratio` decimal(10,4) DEFAULT NULL COMMENT '公积金-公司比例',
|
`housing_fund_personal_ratio` decimal(10,4) DEFAULT NULL COMMENT '公积金-个人比例',
|
`social_security_base_min` decimal(12,2) DEFAULT NULL COMMENT '社保基数下限',
|
`social_security_base_max` decimal(12,2) DEFAULT NULL COMMENT '社保基数上限',
|
`housing_fund_base_min` decimal(12,2) DEFAULT NULL COMMENT '公积金基数下限',
|
`housing_fund_base_max` decimal(12,2) DEFAULT NULL COMMENT '公积金基数上限',
|
`status` int DEFAULT NULL COMMENT '状态',
|
`remark` varchar(255) DEFAULT NULL COMMENT '备注',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='社保配置';
|
|
-- ############################################################################
|
-- ## 来源文件：fix_api_500_schema_20261030_part2.sql
|
-- ############################################################################
|
-- ============================================================================
|
-- 接口 500 修复（第二批）：补齐代码新增但库中缺失的列
|
--
|
-- 背景：本地全量接口测试（1992 个接口）后有 77 个仍返回 500，
|
-- 其中 17 个是"列不存在"——当前代码的 DO 已新增字段，
|
-- 但全库初始化脚本 config_export_all_20260928.sql 未同步。
|
--
|
-- 特性：幂等，可重复执行。
|
-- 依赖：在 fix_api_500_schema_20261030.sql 之后执行。
|
-- ============================================================================
|
|
-- ---------------------------------------------------------------------------
|
-- 一、MES 仓库模块
|
-- ---------------------------------------------------------------------------
|
|
-- 1.1 杂项收料单行：batch_code
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt_line'
|
AND column_name = 'batch_code');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_misc_receipt_line` ADD COLUMN `batch_code` varchar(64) DEFAULT NULL COMMENT ''批次号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.2 杂项收料单：type
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt'
|
AND column_name = 'type');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_misc_receipt` ADD COLUMN `type` int DEFAULT NULL COMMENT ''收料类型''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.3 外协发料单行：batch_id
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_issue_line'
|
AND column_name = 'batch_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_outsource_issue_line` ADD COLUMN `batch_id` bigint DEFAULT NULL COMMENT ''批次编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.4 外协入库单行：production_date
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line'
|
AND column_name = 'production_date');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `production_date` datetime DEFAULT NULL COMMENT ''生产日期''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.5 装箱明细：expire_date
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_package_line'
|
AND column_name = 'expire_date');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_package_line` ADD COLUMN `expire_date` datetime DEFAULT NULL COMMENT ''有效期''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.6 盘点任务：type（退料类型/盘点类型）
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task'
|
AND column_name = 'type');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `type` int DEFAULT NULL COMMENT ''盘点类型''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.7 盘点任务行：taking_quantity
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_line'
|
AND column_name = 'taking_quantity');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_stock_taking_task_line` ADD COLUMN `taking_quantity` decimal(24,6) DEFAULT NULL COMMENT ''实盘数量''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 1.8 盘点结果：taking_quantity
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_result'
|
AND column_name = 'taking_quantity');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_wm_stock_taking_task_result` ADD COLUMN `taking_quantity` decimal(24,6) DEFAULT NULL COMMENT ''实盘数量''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------------------------------------
|
-- 二、MES 生产 / 设备 / 质检
|
-- ---------------------------------------------------------------------------
|
|
-- 2.1 生产报工：machinery_ids
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_feedback'
|
AND column_name = 'machinery_ids');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_pro_feedback` ADD COLUMN `machinery_ids` varchar(500) DEFAULT NULL COMMENT ''设备编号列表，逗号分隔''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 2.2 设备台账：collect_device_code
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_dv_machinery'
|
AND column_name = 'collect_device_code');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_dv_machinery` ADD COLUMN `collect_device_code` varchar(64) DEFAULT NULL COMMENT ''数采设备编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 2.3 计量校验：check_user_name
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_dv_metering_check'
|
AND column_name = 'check_user_name');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_dv_metering_check` ADD COLUMN `check_user_name` varchar(64) DEFAULT NULL COMMENT ''校验人姓名''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 2.4 来料检验：vendor_name
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_qc_iqc'
|
AND column_name = 'vendor_name');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_qc_iqc` ADD COLUMN `vendor_name` varchar(128) DEFAULT NULL COMMENT ''供应商名称''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 2.5 工艺路线工序 / 工单工序：yield_rate
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_route_process'
|
AND column_name = 'yield_rate');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_pro_route_process` ADD COLUMN `yield_rate` decimal(10,4) DEFAULT NULL COMMENT ''良率''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order_process'
|
AND column_name = 'yield_rate');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `mes_pro_work_order_process` ADD COLUMN `yield_rate` decimal(10,4) DEFAULT NULL COMMENT ''良率''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------------------------------------
|
-- 三、HRM 考勤
|
-- ---------------------------------------------------------------------------
|
|
-- 3.1 考勤规则：location_address
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule'
|
AND column_name = 'location_address');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_rule` ADD COLUMN `location_address` varchar(500) DEFAULT NULL COMMENT ''打卡地点地址''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 3.2 考勤记录：clock_in_accuracy
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_in_accuracy');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_accuracy` decimal(10,2) DEFAULT NULL COMMENT ''上班打卡定位精度（米）''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_out_accuracy');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_accuracy` decimal(10,2) DEFAULT NULL COMMENT ''下班打卡定位精度（米）''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 3.3 考勤节假日：start_date / end_date
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_holiday'
|
AND column_name = 'start_date');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_holiday` ADD COLUMN `start_date` date DEFAULT NULL COMMENT ''开始日期''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_holiday'
|
AND column_name = 'end_date');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `hrm_attendance_holiday` ADD COLUMN `end_date` date DEFAULT NULL COMMENT ''结束日期''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------------------------------------
|
-- 四、CRM 客户
|
-- ---------------------------------------------------------------------------
|
|
-- 4.1 客户：auto_pool_enabled
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'crm_customer'
|
AND column_name = 'auto_pool_enabled');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `crm_customer` ADD COLUMN `auto_pool_enabled` bit(1) NOT NULL DEFAULT b''1'' COMMENT ''是否开启自动进入公海''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ---------------------------------------------------------------------------
|
-- 五、会员用户：tenant_id（实体继承 TenantBaseDO）
|
-- ---------------------------------------------------------------------------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'member_user'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `member_user` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT ''租户编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- 其余 member_* 表同样补 tenant_id，保证与 TenantBaseDO 一致
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'member_tag'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `member_tag` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT ''租户编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'member_level'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `member_level` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT ''租户编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'member_group'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
'ALTER TABLE `member_group` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT ''租户编号''',
|
'SELECT 1');
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
-- ############################################################################
|
-- ## 来源文件：fix_api_500_schema_20261030_part3.sql
|
-- ############################################################################
|
-- ============================================================================
|
-- 接口 500 修复（第三批）：按 DO 实体自动比对补齐所有缺失列
|
-- 生成方式：解析 359 个 DO 的 @TableName + 字段，与 mom-kyzn 实际列比对取差集
|
-- 特性：幂等，可重复执行
|
-- ============================================================================
|
|
-- ---------- after_sale_visit ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'after_sale_visit'
|
AND column_name = 'recurred');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `after_sale_visit` ADD COLUMN `recurred` bit(1) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'after_sale_visit'
|
AND column_name = 'satisfied');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `after_sale_visit` ADD COLUMN `satisfied` bit(1) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- hrm_attendance_record ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_in_address');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_address` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_in_distance');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_in_distance` decimal(24,6) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_out_address');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_address` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_record'
|
AND column_name = 'clock_out_distance');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_record` ADD COLUMN `clock_out_distance` decimal(24,6) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- hrm_attendance_rule ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule'
|
AND column_name = 'coordinate_system');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `coordinate_system` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule'
|
AND column_name = 'latitude');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `latitude` decimal(24,6) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule'
|
AND column_name = 'location_required');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `location_required` bit(1) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule'
|
AND column_name = 'longitude');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `longitude` decimal(24,6) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'hrm_attendance_rule'
|
AND column_name = 'radius_meters');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `hrm_attendance_rule` ADD COLUMN `radius_meters` int DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_dv_machinery ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_dv_machinery'
|
AND column_name = 'collect_enabled');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_dv_machinery` ADD COLUMN `collect_enabled` bit(1) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_pro_work_order ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_pro_work_order'
|
AND column_name = 'feedback_approve');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_pro_work_order` ADD COLUMN `feedback_approve` bit(1) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_wm_misc_receipt ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt'
|
AND column_name = 'source_doc_code');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_misc_receipt` ADD COLUMN `source_doc_code` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt'
|
AND column_name = 'source_doc_id');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_misc_receipt` ADD COLUMN `source_doc_id` bigint DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt'
|
AND column_name = 'source_doc_type');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_misc_receipt` ADD COLUMN `source_doc_type` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_wm_misc_receipt_detail ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt_detail'
|
AND column_name = 'batch_code');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_misc_receipt_detail` ADD COLUMN `batch_code` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_wm_misc_receipt_line ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt_line'
|
AND column_name = 'area_id');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_misc_receipt_line` ADD COLUMN `area_id` bigint DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt_line'
|
AND column_name = 'location_id');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_misc_receipt_line` ADD COLUMN `location_id` bigint DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_misc_receipt_line'
|
AND column_name = 'warehouse_id');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_misc_receipt_line` ADD COLUMN `warehouse_id` bigint DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_wm_outsource_receipt_line ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line'
|
AND column_name = 'expire_date');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `expire_date` datetime DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_outsource_receipt_line'
|
AND column_name = 'lot_number');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_outsource_receipt_line` ADD COLUMN `lot_number` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_wm_stock_taking_task ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task'
|
AND column_name = 'blind_flag');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `blind_flag` bit(1) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task'
|
AND column_name = 'end_time');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `end_time` datetime DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task'
|
AND column_name = 'frozen');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `frozen` bit(1) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task'
|
AND column_name = 'start_time');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `start_time` datetime DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task'
|
AND column_name = 'user_id');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task` ADD COLUMN `user_id` bigint DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- mes_wm_stock_taking_task_line ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_wm_stock_taking_task_line'
|
AND column_name = 'status');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `mes_wm_stock_taking_task_line` ADD COLUMN `status` int DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ---------- qc_report_template_version ----------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'qc_report_template_version'
|
AND column_name = 'schema');
|
SET @sql := IF(@exists = 0, "ALTER TABLE `qc_report_template_version` ADD COLUMN `schema` varchar(255) DEFAULT NULL", "SELECT 1");
|
PREPARE stmt FROM @sql;
|
EXECUTE stmt;
|
DEALLOCATE PREPARE stmt;
|
|
-- ############################################################################
|
-- ## 来源文件：fix_api_500_schema_20261030_part4.sql
|
-- ############################################################################
|
-- ============================================================================
|
-- 接口 500 修复（第四批）：补齐缺失的表（按 DO 实体生成）
|
-- 特性：IF NOT EXISTS，幂等
|
-- ============================================================================
|
|
CREATE TABLE IF NOT EXISTS `hrm_user_handover` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`from_user_id` bigint NULL COMMENT '/',
|
`to_user_id` bigint NULL COMMENT '/',
|
`resignation_id` bigint NULL COMMENT '/',
|
`handover_status` int NULL COMMENT '/',
|
`handover_time` datetime NULL COMMENT '/',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='hrm_user_handover';
|
|
CREATE TABLE IF NOT EXISTS `member_address` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`user_id` bigint NULL COMMENT '/',
|
`name` varchar(255) NULL COMMENT '/',
|
`mobile` varchar(255) NULL COMMENT '/',
|
`area_id` bigint NULL COMMENT '/',
|
`detail_address` varchar(255) NULL COMMENT '/',
|
`default_status` bit(1) NOT NULL DEFAULT b'0' COMMENT '/',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='member_address';
|
|
CREATE TABLE IF NOT EXISTS `mes_collect_alarm` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`machinery_id` bigint NULL COMMENT 'private Long id;',
|
`device_code` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`task_id` bigint NULL COMMENT 'private String deviceCode;',
|
`work_order_id` bigint NULL COMMENT 'private Long taskId;',
|
`wm_id` int NULL COMMENT 'private Long workOrderId;',
|
`wm_msg` varchar(255) NULL COMMENT 'private Integer wmId;',
|
`wm_time` datetime NULL COMMENT 'private String wmMsg;',
|
`status` int NULL COMMENT 'private LocalDateTime wmTime;',
|
`clear_time` datetime NULL COMMENT 'private Integer status;',
|
`collect_time` datetime NULL COMMENT 'private LocalDateTime clearTime;',
|
`data_json` varchar(255) NULL COMMENT 'private LocalDateTime collectTime;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_collect_alarm';
|
|
CREATE TABLE IF NOT EXISTS `mes_collect_cycle` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`machinery_id` bigint NULL COMMENT 'private Long id;',
|
`device_code` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`task_id` bigint NULL COMMENT 'private String deviceCode;',
|
`work_order_id` bigint NULL COMMENT 'private Long taskId;',
|
`cycle_no` bigint NULL COMMENT 'private Long workOrderId;',
|
`collect_time` datetime NULL COMMENT 'private Long cycleNo;',
|
`ecyct` decimal(24,6) NULL COMMENT 'private LocalDateTime collectTime;',
|
`eiss` decimal(24,6) NULL COMMENT 'private BigDecimal ecyct;',
|
`eivm` decimal(24,6) NULL COMMENT 'private BigDecimal eiss;',
|
`eipm` decimal(24,6) NULL COMMENT 'private BigDecimal eivm;',
|
`esipt` decimal(24,6) NULL COMMENT 'private BigDecimal eipm;',
|
`esipp` decimal(24,6) NULL COMMENT 'private BigDecimal esipt;',
|
`esips` decimal(24,6) NULL COMMENT 'private BigDecimal esipp;',
|
`eipt` decimal(24,6) NULL COMMENT 'private BigDecimal esips;',
|
`eipse` decimal(24,6) NULL COMMENT 'private BigDecimal eipt;',
|
`eplst` decimal(24,6) NULL COMMENT 'private BigDecimal eipse;',
|
`eplsse` decimal(24,6) NULL COMMENT 'private BigDecimal eplst;',
|
`eplspm` decimal(24,6) NULL COMMENT 'private BigDecimal eplsse;',
|
`et_temps` varchar(255) NULL COMMENT 'private BigDecimal eplspm;',
|
`data_json` varchar(255) NULL COMMENT 'private String etTemps;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_collect_cycle';
|
|
CREATE TABLE IF NOT EXISTS `mes_collect_message` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`machinery_id` bigint NULL COMMENT 'private Long id;',
|
`device_code` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`mqtt_topic` varchar(255) NULL COMMENT 'private String deviceCode;',
|
`msg_type` varchar(255) NULL COMMENT 'private String mqttTopic;',
|
`event_time` datetime NULL COMMENT 'private String msgType;',
|
`raw_payload` varchar(255) NULL COMMENT 'private LocalDateTime eventTime;',
|
`process_status` int NULL COMMENT 'private String rawPayload;',
|
`error_msg` varchar(255) NULL COMMENT 'private Integer processStatus;',
|
`receive_time` datetime NULL COMMENT 'private String errorMsg;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_collect_message';
|
|
CREATE TABLE IF NOT EXISTS `mes_collect_operate_log` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`machinery_id` bigint NULL COMMENT 'private Long id;',
|
`device_code` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`task_id` bigint NULL COMMENT 'private String deviceCode;',
|
`work_order_id` bigint NULL COMMENT 'private Long taskId;',
|
`var_id` varchar(255) NULL COMMENT 'private Long workOrderId;',
|
`old_value` varchar(255) NULL COMMENT 'private String varId;',
|
`new_value` varchar(255) NULL COMMENT 'private String oldValue;',
|
`modify_time` datetime NULL COMMENT 'private String newValue;',
|
`collect_time` datetime NULL COMMENT 'private LocalDateTime modifyTime;',
|
`data_json` varchar(255) NULL COMMENT 'private LocalDateTime collectTime;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_collect_operate_log';
|
|
CREATE TABLE IF NOT EXISTS `mes_collect_realtime` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`machinery_id` bigint NULL COMMENT 'private Long id;',
|
`device_code` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`task_id` bigint NULL COMMENT 'private String deviceCode;',
|
`work_order_id` bigint NULL COMMENT 'private Long taskId;',
|
`collect_time` datetime NULL COMMENT 'private Long workOrderId;',
|
`sts` int NULL COMMENT 'private LocalDateTime collectTime;',
|
`opm` int NULL COMMENT 'private Integer sts;',
|
`asts` int NULL COMMENT 'private Integer opm;',
|
`ot` decimal(24,6) NULL COMMENT 'private Integer asts;',
|
`temps` varchar(255) NULL COMMENT 'private BigDecimal ot;',
|
`data_json` varchar(255) NULL COMMENT 'private String temps;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_collect_realtime';
|
|
CREATE TABLE IF NOT EXISTS `mes_collect_realtime_history` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`machinery_id` bigint NULL COMMENT 'private Long id;',
|
`device_code` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`task_id` bigint NULL COMMENT 'private String deviceCode;',
|
`work_order_id` bigint NULL COMMENT 'private Long taskId;',
|
`collect_time` datetime NULL COMMENT 'private Long workOrderId;',
|
`sts` int NULL COMMENT 'private LocalDateTime collectTime;',
|
`opm` int NULL COMMENT 'private Integer sts;',
|
`asts` int NULL COMMENT 'private Integer opm;',
|
`ot` decimal(24,6) NULL COMMENT 'private Integer asts;',
|
`temps` varchar(255) NULL COMMENT 'private BigDecimal ot;',
|
`data_json` varchar(255) NULL COMMENT 'private String temps;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_collect_realtime_history';
|
|
CREATE TABLE IF NOT EXISTS `mes_collect_tech` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`machinery_id` bigint NULL COMMENT 'private Long id;',
|
`device_code` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`task_id` bigint NULL COMMENT 'private String deviceCode;',
|
`work_order_id` bigint NULL COMMENT 'private Long taskId;',
|
`collect_time` datetime NULL COMMENT 'private Long workOrderId;',
|
`data_json` varchar(255) NULL COMMENT 'private LocalDateTime collectTime;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_collect_tech';
|
|
CREATE TABLE IF NOT EXISTS `mes_dv_spare_movement` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`request_key` varchar(255) NULL COMMENT 'public class MesDvSpareMovementDO extends BaseDO {',
|
`spare_part_id` bigint NULL COMMENT 'private String requestKey;',
|
`spare_part_code` varchar(255) NULL COMMENT 'private Long sparePartId;',
|
`spare_part_name` varchar(255) NULL COMMENT 'private String sparePartCode;',
|
`specification` varchar(255) NULL COMMENT 'private String sparePartName;',
|
`unit` varchar(255) NULL COMMENT 'private String specification;',
|
`type` int NULL COMMENT '/** 1 初始库存；2 入库；3 维修领用；4 退回 */',
|
`quantity` decimal(24,6) NULL COMMENT 'private Integer type;',
|
`before_quantity` decimal(24,6) NULL COMMENT 'private BigDecimal quantity;',
|
`after_quantity` decimal(24,6) NULL COMMENT 'private BigDecimal beforeQuantity;',
|
`repair_id` bigint NULL COMMENT 'private BigDecimal afterQuantity;',
|
`repair_code` varchar(255) NULL COMMENT 'private Long repairId;',
|
`machinery_id` bigint NULL COMMENT 'private String repairCode;',
|
`machinery_name` varchar(255) NULL COMMENT 'private Long machineryId;',
|
`original_id` bigint NULL COMMENT 'private String machineryName;',
|
`receiver_user_id` bigint NULL COMMENT 'private Long originalId;',
|
`receiver_name` varchar(255) NULL COMMENT 'private Long receiverUserId;',
|
`operator_user_id` bigint NULL COMMENT 'private String receiverName;',
|
`remark` varchar(255) NULL COMMENT 'private Long operatorUserId;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_dv_spare_movement';
|
|
CREATE TABLE IF NOT EXISTS `mes_dv_spare_part` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`code` varchar(255) NULL COMMENT 'public class MesDvSparePartDO extends BaseDO {',
|
`name` varchar(255) NULL COMMENT 'private String code;',
|
`specification` varchar(255) NULL COMMENT 'private String name;',
|
`unit` varchar(255) NULL COMMENT 'private String specification;',
|
`location` varchar(255) NULL COMMENT 'private String unit;',
|
`stock_quantity` decimal(24,6) NULL COMMENT 'private String location;',
|
`safety_quantity` decimal(24,6) NULL COMMENT 'private BigDecimal stockQuantity;',
|
`status` int NULL COMMENT 'private BigDecimal safetyQuantity;',
|
`remark` varchar(255) NULL COMMENT 'private Integer status;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_dv_spare_part';
|
|
CREATE TABLE IF NOT EXISTS `mes_energy_alert_log` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`rule_id` bigint NULL COMMENT 'public class EnergyAlertLogDO extends TenantBaseDO {',
|
`rule_name` varchar(255) NULL COMMENT 'private Long ruleId;',
|
`record_date` date NULL COMMENT 'private String ruleName;',
|
`period_type` varchar(255) NULL COMMENT 'private LocalDate recordDate;',
|
`energy_type` varchar(255) NULL COMMENT 'private String periodType;',
|
`scope` varchar(255) NULL COMMENT 'private String energyType;',
|
`metric` varchar(255) NULL COMMENT 'private String scope;',
|
`actual_value` decimal(24,6) NULL COMMENT 'private String metric;',
|
`threshold` decimal(24,6) NULL COMMENT 'private BigDecimal actualValue;',
|
`recipient_id` bigint NULL COMMENT 'private BigDecimal threshold;',
|
`recipient_name` varchar(255) NULL COMMENT 'private Long recipientId;',
|
`message_id` bigint NULL COMMENT 'private String recipientName;',
|
`status` varchar(255) NULL COMMENT 'private Long messageId;',
|
`error_message` varchar(255) NULL COMMENT 'private String status;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_energy_alert_log';
|
|
CREATE TABLE IF NOT EXISTS `mes_energy_alert_rule` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`name` varchar(255) NULL COMMENT 'public class EnergyAlertRuleDO extends TenantBaseDO {',
|
`energy_type` varchar(255) NULL COMMENT 'private String name;',
|
`usage_area` varchar(255) NULL COMMENT 'private String energyType;',
|
`product_category` varchar(255) NULL COMMENT 'private String usageArea;',
|
`metric` varchar(255) NULL COMMENT 'private String productCategory;',
|
`threshold` decimal(24,6) NULL COMMENT 'private String metric;',
|
`enabled` bit(1) NOT NULL DEFAULT b'0' COMMENT 'private BigDecimal threshold;',
|
`recipient_ids` varchar(255) NULL COMMENT '/** Comma-separated validated administrator IDs, at most 50. */',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_energy_alert_rule';
|
|
CREATE TABLE IF NOT EXISTS `mes_energy_record` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`record_date` date NULL COMMENT 'private Long id;',
|
`energy_type` varchar(255) NULL COMMENT '/** WATER / ELECTRICITY / STEAM，单位分别为 m³ / kWh / t。 */',
|
`usage_area` varchar(255) NULL COMMENT 'private String energyType;',
|
`product_category` varchar(255) NULL COMMENT 'private String usageArea;',
|
`usage_purpose` varchar(255) NULL COMMENT 'private String productCategory;',
|
`quantity` decimal(24,6) NULL COMMENT 'private String usagePurpose;',
|
`unit_price` decimal(24,6) NULL COMMENT 'private BigDecimal quantity;',
|
`amount` decimal(24,6) NULL COMMENT 'private BigDecimal unitPrice;',
|
`recorder` varchar(255) NULL COMMENT 'private BigDecimal amount;',
|
`remark` varchar(255) NULL COMMENT 'private String recorder;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_energy_record';
|
|
CREATE TABLE IF NOT EXISTS `mes_safety_record` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`record_type` varchar(255) NULL COMMENT 'public class SafetyRecordDO extends BaseDO {',
|
`title` varchar(255) NULL COMMENT 'private String recordType;',
|
`category` varchar(255) NULL COMMENT 'private String title;',
|
`area` varchar(255) NULL COMMENT 'private String category;',
|
`risk_level` varchar(255) NULL COMMENT 'private String area;',
|
`status` varchar(255) NULL COMMENT 'private String riskLevel;',
|
`responsible_person` varchar(255) NULL COMMENT 'private String status;',
|
`inspector` varchar(255) NULL COMMENT 'private String responsiblePerson;',
|
`planned_date` date NULL COMMENT 'private String inspector;',
|
`deadline` date NULL COMMENT 'private LocalDate plannedDate;',
|
`completed_time` datetime NULL COMMENT 'private LocalDate deadline;',
|
`description` varchar(255) NULL COMMENT 'private LocalDateTime completedTime;',
|
`result` varchar(255) NULL COMMENT 'private String description;',
|
`certificate_no` varchar(255) NULL COMMENT 'private String result;',
|
`expiry_date` date NULL COMMENT 'private String certificateNo;',
|
`temperature` decimal(24,6) NULL COMMENT 'private LocalDate expiryDate;',
|
`leakage_current` decimal(24,6) NULL COMMENT 'private BigDecimal temperature;',
|
`attachment_urls` varchar(255) NULL COMMENT 'private BigDecimal leakageCurrent;',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='mes_safety_record';
|
|
CREATE TABLE IF NOT EXISTS `wms_warehouse_approval_config` (
|
`id` bigint NOT NULL AUTO_INCREMENT COMMENT '编号',
|
`warehouse_id` bigint NULL COMMENT '/',
|
`biz_type` varchar(255) NULL COMMENT '/',
|
`approval_enabled` bit(1) NOT NULL DEFAULT b'0' COMMENT '/',
|
`process_definition_key` varchar(255) NULL COMMENT '/',
|
`category_code` varchar(255) NULL COMMENT '/',
|
`creator` varchar(64) DEFAULT '' COMMENT '创建者',
|
`create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
`updater` varchar(64) DEFAULT '' COMMENT '更新者',
|
`update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
`deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
|
PRIMARY KEY (`id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='wms_warehouse_approval_config';
|
|
-- ---------------------------------------------------------------------------
|
-- 补充：新建表缺少 TenantBaseDO 必需的 tenant_id 列
|
-- ---------------------------------------------------------------------------
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_energy_record'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
"ALTER TABLE `mes_energy_record` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号'",
|
"SELECT 1");
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_energy_alert_log'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
"ALTER TABLE `mes_energy_alert_log` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号'",
|
"SELECT 1");
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_energy_alert_rule'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
"ALTER TABLE `mes_energy_alert_rule` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号'",
|
"SELECT 1");
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET @exists := (SELECT COUNT(*) FROM information_schema.columns
|
WHERE table_schema = DATABASE() AND table_name = 'mes_safety_record'
|
AND column_name = 'tenant_id');
|
SET @sql := IF(@exists = 0,
|
"ALTER TABLE `mes_safety_record` ADD COLUMN `tenant_id` bigint NOT NULL DEFAULT 0 COMMENT '租户编号'",
|
"SELECT 1");
|
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
-- ============================================================================
|
-- 执行完毕
|
-- ============================================================================
|
