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