Blame view

application/database/yonyou_bip_sync_log.sql 3.31 KB
2db47680   hexiaodong   chore: 初始提交 php 遗...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
  -- 用友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;