yonyou_bip_sync_log.sql 3.31 KB
-- 用友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;