-- 用友BIP集成数据库安装脚本 -- 使用方法: -- 1. 将 __PREFIX__ 替换为实际的表前缀(默认是 ats_) -- 2. 或者在执行前设置:SET @prefix = 'ats_'; -- 3. 然后执行此SQL文件 -- 设置表前缀(如果未设置,使用默认值 ats_) SET @prefix = IFNULL(@prefix, 'ats_'); -- 创建同步记录表 SET @sql = CONCAT('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同步记录表'';'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 创建基础数据表 SET @sql = CONCAT('CREATE TABLE IF NOT EXISTS `', @prefix, 'yonyou_bip_base_data` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT ''主键ID'', `data_type` varchar(50) NOT NULL DEFAULT '''' COMMENT ''数据类型:organization-销售组织, transaction_type-交易类型, department-销售部门, person-销售业务员, customer-客户'', `bip_code` varchar(100) NOT NULL DEFAULT '''' COMMENT ''用友BIP编码'', `bip_name` varchar(200) NOT NULL DEFAULT '''' COMMENT ''用友BIP名称'', `local_id` int(11) DEFAULT NULL COMMENT ''本地ID(用于映射本地数据)'', `extra_data` text COMMENT ''额外数据(JSON格式)'', `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`), UNIQUE KEY `uk_type_code` (`data_type`, `bip_code`), KEY `idx_data_type` (`data_type`), KEY `idx_local_id` (`local_id`), KEY `idx_sync_time` (`sync_time`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=''用友BIP基础数据表'';'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; -- 在销售订单表中添加同步状态字段 SET @tableName = CONCAT(@prefix, 'kuerp_goods_sales_order'); SET @dbname = DATABASE(); -- 检查并添加 bip_sync_status 字段 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; -- 完成提示 SELECT '用友BIP集成数据库表创建完成!' AS message;