liuminghui
180 lines
9.8 KiB
SQL
180 lines
9.8 KiB
SQL
-- 应付账单。订单应付仍用 prc_pay_bill、prc_pay_bill_fee，不改这两张表的名字。可重复执行。
|
|
SET NAMES utf8mb4;
|
|
|
|
SET @fee_source = (
|
|
SELECT IF(
|
|
EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prc_pay_bill_fee' AND COLUMN_NAME = 'fee_source'),
|
|
'SELECT 1',
|
|
'ALTER TABLE prc_pay_bill_fee ADD COLUMN fee_source varchar(20) NOT NULL DEFAULT ''CALC'' COMMENT ''CALC计算 LADING提单分摊 MANUAL人工'' AFTER remark'
|
|
)
|
|
);
|
|
PREPARE stmt FROM @fee_source;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(
|
|
COUNT(*) = 0,
|
|
'ALTER TABLE prc_pay_bill_fee ADD COLUMN source_id varchar(64) DEFAULT NULL COMMENT ''来源数据id'' AFTER fee_source',
|
|
'SELECT 1'
|
|
)
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prc_pay_bill_fee' AND COLUMN_NAME = 'source_id'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
SET @sql = (
|
|
SELECT IF(
|
|
COUNT(*) = 0,
|
|
'ALTER TABLE prc_pay_bill_fee ADD COLUMN is_deleted char(1) NOT NULL DEFAULT ''0'' COMMENT ''是否删除：0未删除 1已删除'' AFTER source_id',
|
|
'SELECT 1'
|
|
)
|
|
FROM information_schema.COLUMNS
|
|
WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prc_pay_bill_fee' AND COLUMN_NAME = 'is_deleted'
|
|
);
|
|
PREPARE stmt FROM @sql;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
CREATE TABLE IF NOT EXISTS `pay_lading` (
|
|
`lading_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '提单ID',
|
|
`lading_no` varchar(64) NOT NULL COMMENT '提单号或航空主单号',
|
|
`supplier_id` bigint(20) DEFAULT NULL COMMENT '供应商ID',
|
|
`channel_id` varchar(64) DEFAULT NULL COMMENT '渠道ID',
|
|
`biz_date` date DEFAULT NULL COMMENT '出运日期',
|
|
`pieces` int(11) DEFAULT NULL COMMENT '件数',
|
|
`charge_weight` decimal(12,3) DEFAULT NULL COMMENT '计费重',
|
|
`currency` varchar(10) DEFAULT NULL COMMENT '币种',
|
|
`status` varchar(20) NOT NULL DEFAULT 'OPEN' COMMENT 'OPEN可改 ALLOCATED已分摊 SETTLED已进账单',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
|
PRIMARY KEY (`lading_id`),
|
|
UNIQUE KEY `uk_pay_lading_no` (`lading_no`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应付提单';
|
|
|
|
CREATE TABLE IF NOT EXISTS `pay_lading_fee` (
|
|
`lading_fee_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '提单费用ID',
|
|
`lading_id` bigint(20) NOT NULL COMMENT '提单ID',
|
|
`fee_name` varchar(100) NOT NULL COMMENT '费用名称',
|
|
`amount` decimal(18,4) NOT NULL COMMENT '金额',
|
|
`currency` varchar(10) NOT NULL COMMENT '币种',
|
|
`alloc_rule` varchar(20) NOT NULL DEFAULT 'NONE' COMMENT 'WEIGHT按计费重 PIECE按件数 ORDER按票 NONE留在主单',
|
|
`remark` varchar(500) DEFAULT NULL COMMENT '说明',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
|
|
PRIMARY KEY (`lading_fee_id`),
|
|
KEY `idx_pay_lading_fee_lading` (`lading_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='提单费用';
|
|
|
|
CREATE TABLE IF NOT EXISTS `pay_bill` (
|
|
`bill_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '应付账单ID',
|
|
`bill_no` varchar(50) NOT NULL COMMENT '账单号',
|
|
`supplier_id` bigint(20) NOT NULL COMMENT '供应商ID',
|
|
`supplier_name` varchar(100) DEFAULT NULL COMMENT '供应商名称',
|
|
`currency` varchar(10) NOT NULL COMMENT '币种',
|
|
`source_type` varchar(20) NOT NULL COMMENT 'ORDER订单号 TIME计费日 LADING提单',
|
|
`period_start` date DEFAULT NULL COMMENT '计费日起',
|
|
`period_end` date DEFAULT NULL COMMENT '计费日止',
|
|
`order_count` int(11) NOT NULL DEFAULT '0' COMMENT '订单数',
|
|
`total_amount` decimal(18,2) NOT NULL DEFAULT '0.00' COMMENT '应付总额',
|
|
`status` varchar(20) NOT NULL DEFAULT 'DRAFT' COMMENT 'DRAFT草稿 CONFIRMED已确认 PAID已付款 VOID已作废',
|
|
`void_reason` varchar(500) DEFAULT NULL COMMENT '作废原因',
|
|
`void_by` varchar(64) DEFAULT NULL COMMENT '作废人',
|
|
`void_time` datetime DEFAULT NULL COMMENT '作废时间',
|
|
`create_by` varchar(64) DEFAULT '' COMMENT '创建者',
|
|
`create_time` datetime NOT NULL COMMENT '创建时间',
|
|
`update_by` varchar(64) DEFAULT '' COMMENT '更新者',
|
|
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
|
|
`remark` varchar(500) DEFAULT NULL COMMENT '备注',
|
|
PRIMARY KEY (`bill_id`),
|
|
UNIQUE KEY `uk_pay_bill_no` (`bill_no`),
|
|
KEY `idx_pay_bill_status` (`status`, `create_time`),
|
|
KEY `idx_pay_bill_supplier` (`supplier_id`, `currency`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应付账单，作废后保留';
|
|
|
|
CREATE TABLE IF NOT EXISTS `pay_bill_line` (
|
|
`line_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '账单行ID',
|
|
`bill_id` bigint(20) NOT NULL COMMENT '应付账单ID',
|
|
`order_bill_id` varchar(36) DEFAULT NULL COMMENT '订单ID',
|
|
`order_no` varchar(64) DEFAULT NULL COMMENT '订单号',
|
|
`lading_no` varchar(64) DEFAULT NULL COMMENT '提单号',
|
|
`lading_fee_id` bigint(20) DEFAULT NULL COMMENT '留在主单上的费用',
|
|
`fee_name` varchar(100) NOT NULL COMMENT '费用名称',
|
|
`amount` decimal(18,4) NOT NULL COMMENT '金额快照',
|
|
`currency` varchar(10) NOT NULL COMMENT '币种',
|
|
`price_date` date DEFAULT NULL COMMENT '取价日',
|
|
`version_no` int(11) DEFAULT NULL COMMENT '价卡版本',
|
|
`remote_flag` char(1) DEFAULT NULL COMMENT 'Y偏远 N非偏远',
|
|
`density_level` varchar(20) DEFAULT NULL COMMENT 'HIGH MEDIUM LOW',
|
|
`line_source` varchar(20) NOT NULL COMMENT 'ORDER订单费用 LADING主单费用',
|
|
PRIMARY KEY (`line_id`),
|
|
KEY `idx_pay_bill_line_bill` (`bill_id`),
|
|
KEY `idx_pay_bill_line_order` (`order_bill_id`),
|
|
KEY `idx_pay_bill_line_lading_fee` (`lading_fee_id`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应付账单费用快照';
|
|
|
|
CREATE TABLE IF NOT EXISTS `pay_bill_log` (
|
|
`log_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '日志ID',
|
|
`bill_id` bigint(20) NOT NULL COMMENT '应付账单ID',
|
|
`operation_type` varchar(20) NOT NULL COMMENT 'CREATE CONFIRM PAY VOID',
|
|
`from_status` varchar(20) DEFAULT NULL COMMENT '变更前状态',
|
|
`to_status` varchar(20) DEFAULT NULL COMMENT '变更后状态',
|
|
`operation_desc` varchar(500) DEFAULT NULL COMMENT '说明',
|
|
`operator_name` varchar(64) DEFAULT NULL COMMENT '操作人',
|
|
`create_time` datetime NOT NULL COMMENT '操作时间',
|
|
PRIMARY KEY (`log_id`),
|
|
KEY `idx_pay_bill_log_bill` (`bill_id`, `create_time`)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='应付账单操作日志';
|
|
|
|
SET @lading_col = (
|
|
SELECT IF(
|
|
EXISTS (SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'prc_order_bill_ext' AND COLUMN_NAME = 'lading_id'),
|
|
'SELECT 1',
|
|
'ALTER TABLE prc_order_bill_ext ADD COLUMN lading_id bigint(20) DEFAULT NULL COMMENT ''应付提单ID'' AFTER air_no_old'
|
|
)
|
|
);
|
|
PREPARE stmt FROM @lading_col;
|
|
EXECUTE stmt;
|
|
DEALLOCATE PREPARE stmt;
|
|
|
|
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, remark)
|
|
SELECT '应付账单', parent.menu_id, 8, 'payBill', 'price/billing/payBill/index', 1, 0, 'C', '0', '0', 'price:payBill:list', 'money', 'admin', sysdate(), '按订单号、计费日或提单生成应付账单，作废留痕'
|
|
FROM (
|
|
SELECT menu_id FROM sys_menu WHERE menu_name = '账单管理' AND menu_type = 'M' ORDER BY menu_id LIMIT 1
|
|
) parent
|
|
WHERE NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'price:payBill:list');
|
|
|
|
SET @payBillMenu = (SELECT menu_id FROM sys_menu WHERE perms = 'price:payBill: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 '应付账单查询', @payBillMenu, 1, '', '', 1, 0, 'F', '0', '0', 'price:payBill:query', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @payBillMenu IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'price:payBill: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 '应付账单生成', @payBillMenu, 2, '', '', 1, 0, 'F', '0', '0', 'price:payBill:add', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @payBillMenu IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'price:payBill:add');
|
|
|
|
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 '应付账单确认', @payBillMenu, 3, '', '', 1, 0, 'F', '0', '0', 'price:payBill:confirm', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @payBillMenu IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'price:payBill:confirm');
|
|
|
|
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 '应付账单作废', @payBillMenu, 4, '', '', 1, 0, 'F', '0', '0', 'price:payBill:void', '#', 'admin', sysdate()
|
|
FROM DUAL WHERE @payBillMenu IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys_menu WHERE perms = 'price:payBill:void');
|
|
|
|
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.component = 'price/billing/bill'
|
|
JOIN sys_menu dst ON dst.perms LIKE 'price:payBill:%'
|
|
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 1, m.menu_id
|
|
FROM sys_menu m
|
|
WHERE m.perms LIKE 'price:payBill:%'
|
|
AND NOT EXISTS (SELECT 1 FROM sys_role_menu x WHERE x.role_id = 1 AND x.menu_id = m.menu_id);
|