caojia
141 lines
8.4 KiB
SQL
141 lines
8.4 KiB
SQL
-- 应付邮编配置。挂在数据管理下面。可重复执行。
|
|
-- 附加费按国家、费用名称、金额原文合并成一条；邮编行仍按 Excel 逐条保存。
|
|
|
|
SET NAMES utf8mb4;
|
|
|
|
CREATE TABLE IF NOT EXISTS `pay_postcode_group` (
|
|
`group_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '配置ID',
|
|
`source_file` varchar(500) NOT NULL COMMENT '来源文件名',
|
|
`file_md5` char(32) NOT NULL COMMENT '来源文件MD5',
|
|
`sheet_name` varchar(200) NOT NULL COMMENT '工作表',
|
|
`block_title` varchar(200) DEFAULT NULL COMMENT '表内区块标题，按Excel原文',
|
|
`config_kind` varchar(20) NOT NULL COMMENT 'ZONE邮编分区 FEE附加费',
|
|
`channel_name` varchar(100) DEFAULT NULL COMMENT '渠道名称，从Excel表头识别',
|
|
`country_name` varchar(100) DEFAULT NULL COMMENT '国家',
|
|
`country_code` varchar(20) DEFAULT NULL COMMENT '国家二字码',
|
|
`fee_name` varchar(200) DEFAULT NULL COMMENT '附加费名称，邮编分区可空',
|
|
`fee_text` varchar(200) DEFAULT NULL COMMENT '附加费金额原文。同国家同费用同原文只保留一条',
|
|
`line_count` int(11) NOT NULL DEFAULT 0 COMMENT '邮编行数',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
|
PRIMARY KEY (`group_id`),
|
|
KEY `idx_pay_postcode_group_file` (`file_md5`, `sheet_name`),
|
|
KEY `idx_pay_postcode_group_country` (`country_code`),
|
|
KEY `idx_pay_postcode_group_fee` (`fee_name`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应付邮编配置：分区或合并后的附加费';
|
|
|
|
CREATE TABLE IF NOT EXISTS `pay_postcode_line` (
|
|
`line_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '邮编行ID',
|
|
`group_id` bigint(20) NOT NULL COMMENT '配置ID',
|
|
`country_name` varchar(100) DEFAULT NULL COMMENT '国家',
|
|
`country_code` varchar(20) DEFAULT NULL COMMENT '国家二字码',
|
|
`region_name` varchar(200) DEFAULT NULL COMMENT '州或地区，Excel原文',
|
|
`region_name_cn` varchar(200) DEFAULT NULL COMMENT '地区中文',
|
|
`city_name` varchar(200) DEFAULT NULL COMMENT '城市',
|
|
`region_code` varchar(50) DEFAULT NULL COMMENT '地区代码',
|
|
`zone_name` varchar(200) DEFAULT NULL COMMENT '分区，Excel原文',
|
|
`postcode` varchar(200) NOT NULL COMMENT '邮编，已去掉末尾星号',
|
|
`postcode_raw` varchar(200) DEFAULT NULL COMMENT '邮编原文',
|
|
`postcode_end` varchar(200) DEFAULT NULL COMMENT '截止邮编，已去掉末尾星号',
|
|
`extra_text` varchar(500) DEFAULT NULL COMMENT '其余列，从左到右用 | 连接',
|
|
`sort_no` int(11) NOT NULL DEFAULT 0 COMMENT 'Excel中的先后顺序',
|
|
PRIMARY KEY (`line_id`),
|
|
KEY `idx_pay_postcode_line_group` (`group_id`),
|
|
KEY `idx_pay_postcode_line_code` (`postcode`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应付邮编明细，一行对应Excel一条邮编';
|
|
|
|
SET @parentId := (
|
|
SELECT menu_id FROM sys_menu
|
|
WHERE path = 'data' AND menu_type = 'M' AND parent_id = 0
|
|
ORDER BY menu_id ASC LIMIT 1
|
|
);
|
|
|
|
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, query, route_name, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark)
|
|
SELECT '应付邮编配置', @parentId, 8, 'payPostcode', 'data/payPostcode/index', '', 'PayPostcode', 1, 0, 'C', '0', '0', 'data:payPostcode:list', 'international', 'admin', sysdate(), '按Excel导入应付邮编和附加费邮编'
|
|
FROM DUAL WHERE @parentId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'data:payPostcode:list');
|
|
|
|
SET @menuId := (SELECT menu_id FROM sys_menu WHERE perms = 'data:payPostcode:list' LIMIT 1);
|
|
|
|
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time)
|
|
SELECT '应付邮编查询', @menuId, 1, '', '', 1, 0, 'F', '0', '0', 'data:payPostcode:query', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @menuId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'data:payPostcode:query');
|
|
|
|
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time)
|
|
SELECT '应付邮编导入', @menuId, 2, '', '', 1, 0, 'F', '0', '0', 'data:payPostcode:import', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @menuId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'data:payPostcode:import');
|
|
|
|
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time)
|
|
SELECT '应付邮编修改', @menuId, 3, '', '', 1, 0, 'F', '0', '0', 'data:payPostcode:edit', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @menuId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'data:payPostcode:edit');
|
|
|
|
INSERT INTO sys_menu (menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time)
|
|
SELECT '应付邮编删除', @menuId, 4, '', '', 1, 0, 'F', '0', '0', 'data:payPostcode:remove', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @menuId IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'data:payPostcode:remove');
|
|
|
|
SET @has_channel_name := (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_postcode_group' AND COLUMN_NAME = 'channel_name'
|
|
);
|
|
SET @add_channel_name := IF(
|
|
@has_channel_name = 0,
|
|
'ALTER TABLE pay_postcode_group ADD COLUMN channel_name varchar(100) DEFAULT NULL COMMENT ''渠道名称，从Excel表头识别'' AFTER config_kind',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE add_channel_name_stmt FROM @add_channel_name;
|
|
EXECUTE add_channel_name_stmt;
|
|
DEALLOCATE PREPARE add_channel_name_stmt;
|
|
|
|
SET @has_coverage := (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_postcode_group' AND COLUMN_NAME = 'coverage'
|
|
);
|
|
SET @add_coverage := IF(
|
|
@has_coverage = 0,
|
|
'ALTER TABLE pay_postcode_group ADD COLUMN coverage varchar(20) DEFAULT NULL COMMENT ''REACH可达 UNREACH不可达 REMOTE偏远，普通分区为空'' AFTER config_kind',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE add_coverage_stmt FROM @add_coverage;
|
|
EXECUTE add_coverage_stmt;
|
|
DEALLOCATE PREPARE add_coverage_stmt;
|
|
|
|
SET @has_service_type := (
|
|
SELECT COUNT(*) FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'pay_postcode_group' AND COLUMN_NAME = 'service_type'
|
|
);
|
|
SET @add_service_type := IF(
|
|
@has_service_type = 0,
|
|
'ALTER TABLE pay_postcode_group ADD COLUMN service_type varchar(100) DEFAULT NULL COMMENT ''目录服务类型'' AFTER country_code, ADD COLUMN catalog_country varchar(100) DEFAULT NULL COMMENT ''目录国家，和邮编目的国分开'' AFTER service_type, ADD COLUMN product_name varchar(200) DEFAULT NULL COMMENT ''目录销售产品'' AFTER catalog_country, ADD COLUMN quote_sheet varchar(200) DEFAULT NULL COMMENT ''目录报价工作表'' AFTER product_name',
|
|
'SELECT 1'
|
|
);
|
|
PREPARE add_service_type_stmt FROM @add_service_type;
|
|
EXECUTE add_service_type_stmt;
|
|
DEALLOCATE PREPARE add_service_type_stmt;
|
|
|
|
INSERT INTO sys_role_menu (role_id, menu_id)
|
|
SELECT 1, m.menu_id
|
|
FROM sys_menu m
|
|
WHERE m.perms LIKE 'data:payPostcode:%'
|
|
AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = 1 AND x.menu_id = m.menu_id);
|
|
|
|
INSERT INTO sys_role_menu (role_id, menu_id)
|
|
SELECT DISTINCT rm.role_id, dst.menu_id
|
|
FROM sys_role_menu rm
|
|
JOIN sys_menu src ON src.menu_id = rm.menu_id AND src.perms = 'data:region:list'
|
|
JOIN sys_menu dst ON dst.perms IN ('data:payPostcode:list', 'data:payPostcode:query')
|
|
WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = rm.role_id AND x.menu_id = dst.menu_id);
|
|
|
|
INSERT INTO sys_role_menu (role_id, menu_id)
|
|
SELECT DISTINCT rm.role_id, dst.menu_id
|
|
FROM sys_role_menu rm
|
|
JOIN sys_menu src ON src.menu_id = rm.menu_id AND src.perms = 'data:region:edit'
|
|
JOIN sys_menu dst ON dst.perms IN ('data:payPostcode:import', 'data:payPostcode:edit')
|
|
WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = rm.role_id AND x.menu_id = dst.menu_id);
|
|
|
|
INSERT INTO sys_role_menu (role_id, menu_id)
|
|
SELECT DISTINCT rm.role_id, dst.menu_id
|
|
FROM sys_role_menu rm
|
|
JOIN sys_menu src ON src.menu_id = rm.menu_id AND src.perms = 'data:region:remove'
|
|
JOIN sys_menu dst ON dst.perms = 'data:payPostcode:remove'
|
|
WHERE NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = rm.role_id AND x.menu_id = dst.menu_id);
|