Blame view

application/database/yonyou_bip_install.sql 4.72 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
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
  -- 用友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;