-- 用友BIP同步记录表 -- 用于记录与用友BIP的数据同步情况 -- 注意:请将 __PREFIX__ 替换为实际的表前缀(默认是 ats_) CREATE TABLE IF NOT EXISTS `__PREFIX__yonyou_bip_sync_log` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID', `order_id` int(11) NOT NULL DEFAULT '0' COMMENT '订单ID', `order_code` varchar(100) NOT NULL DEFAULT '' COMMENT '订单编号', `sync_type` varchar(50) NOT NULL DEFAULT '' COMMENT '同步类型:sales_order-销售订单,sales_delivery-销售发货', `sync_status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '同步状态:0-失败,1-成功', `sync_result` text COMMENT '同步结果(JSON格式)', `bip_order_id` varchar(100) DEFAULT NULL COMMENT '用友BIP返回的订单ID', `error_message` text COMMENT '错误信息', `sync_time` int(11) NOT NULL DEFAULT '0' COMMENT '同步时间', `createtime` int(11) NOT NULL DEFAULT '0' COMMENT '创建时间', `updatetime` int(11) NOT NULL DEFAULT '0' COMMENT '更新时间', PRIMARY KEY (`id`), KEY `idx_order_id` (`order_id`), KEY `idx_order_code` (`order_code`), KEY `idx_sync_type` (`sync_type`), KEY `idx_sync_status` (`sync_status`), KEY `idx_sync_time` (`sync_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用友BIP同步记录表'; -- 在销售订单表中添加同步状态字段(如果不存在) -- 注意:请将 __PREFIX__ 替换为实际的表前缀(默认是 ats_) -- 如果字段已存在,请手动删除对应的 ALTER TABLE 语句 -- 检查并添加 bip_sync_status 字段 SET @dbname = DATABASE(); SET @tablename = '__PREFIX__kuerp_goods_sales_order'; SET @columnname = 'bip_sync_status'; SET @preparedStatement = (SELECT IF( ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE (table_name = @tablename) AND (table_schema = @dbname) AND (column_name = @columnname) ) > 0, 'SELECT 1', CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` tinyint(1) NOT NULL DEFAULT ''0'' COMMENT ''用友BIP同步状态:0-未同步,1-已同步'' AFTER `examine_status`') )); PREPARE alterIfNotExists FROM @preparedStatement; EXECUTE alterIfNotExists; DEALLOCATE PREPARE alterIfNotExists; -- 检查并添加 bip_order_id 字段 SET @columnname = 'bip_order_id'; SET @preparedStatement = (SELECT IF( ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE (table_name = @tablename) AND (table_schema = @dbname) AND (column_name = @columnname) ) > 0, 'SELECT 1', CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` varchar(100) DEFAULT NULL COMMENT ''用友BIP订单ID'' AFTER `bip_sync_status`') )); PREPARE alterIfNotExists FROM @preparedStatement; EXECUTE alterIfNotExists; DEALLOCATE PREPARE alterIfNotExists; -- 检查并添加 bip_sync_time 字段 SET @columnname = 'bip_sync_time'; SET @preparedStatement = (SELECT IF( ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE (table_name = @tablename) AND (table_schema = @dbname) AND (column_name = @columnname) ) > 0, 'SELECT 1', CONCAT('ALTER TABLE `', @tablename, '` ADD COLUMN `', @columnname, '` int(11) NOT NULL DEFAULT ''0'' COMMENT ''用友BIP同步时间'' AFTER `bip_order_id`') )); PREPARE alterIfNotExists FROM @preparedStatement; EXECUTE alterIfNotExists; DEALLOCATE PREPARE alterIfNotExists;