yonyou_bip_install.sql 4.72 KB
-- 用友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;