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