20250222-currency-rate.sql

CREATE TABLE IF NOT EXISTS `ecw_currency_rate` (
    `id` BIGINT AUTO_INCREMENT,
    `source_id` BIGINT NOT NULL COMMENT '原币种',
    `target_id` BIGINT NOT NULL COMMENT '支付币种',
    `source_amount` DECIMAL(14, 4) NOT NULL COMMENT '原币种金额',
    `target_amount` DECIMAL(14, 4) NOT NULL COMMENT '支付币种金额',
    `countries` JSON NOT NULL COMMENT '国家',
    `expiration` DATE NOT NULL COMMENT '有效日期',
    `remarks` VARCHAR(255) NULL COMMENT '备注',
    `creator` VARCHAR(32) NOT NULL COMMENT '创建者',
    `create_time` DATETIME NOT NULL COMMENT '创建时间',
    `updater` VARCHAR(32) NOT NULL COMMENT '更新者',
    `update_time` DATETIME NOT NULL COMMENT '更新时间',
    `deleted` BIT(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`)
) COMMENT '币种汇率表';

CREATE TABLE IF NOT EXISTS `ecw_currency_rate_log` (
    `id` BIGINT AUTO_INCREMENT,
    `rate_id` BIGINT NOT NULL COMMENT '汇率编号',
    `source_amount1` DECIMAL(14, 4) NOT NULL COMMENT '修改前原币种金额',
    `source_amount2` DECIMAL(14, 4) NOT NULL COMMENT '修改后原币种金额',
    `target_amount1` DECIMAL(14, 4) NOT NULL COMMENT '修改前支付币种金额',
    `target_amount2` DECIMAL(14, 4) NOT NULL COMMENT '修改后支付币种金额',
    `expiration1` DATE NOT NULL COMMENT '修改前有效期',
    `expiration2` DATE NOT NULL COMMENT '修改后有效期',
    `remarks` VARCHAR(255) NULL COMMENT '备注',
    `creator` VARCHAR(32) NOT NULL COMMENT '创建者',
    `create_time` DATETIME NOT NULL COMMENT '创建时间',
    `updater` VARCHAR(32) NOT NULL COMMENT '更新者',
    `update_time` DATETIME NOT NULL COMMENT '更新时间',
    `deleted` BIT(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    PRIMARY KEY (`id`)
) COMMENT '币种汇率变更表';

BEGIN;

INSERT INTO `system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`)
VALUES ('货币汇率状态', 'currency_rate_status', 0, NULL, '1', '2022-05-23 23:29:07', '1', '2022-05-23 23:34:59', b'0');

INSERT INTO `system_dict_data`
(`sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`)
VALUES
(0, '已过期', '1', 'currency_rate_status', 0, 'warning', '', NULL, '1', '2022-11-18 00:18:34', '1', '2023-01-15 18:09:43', b'0', 'Expired'),
(1, '生效中', '0', 'currency_rate_status', 0, 'warning', '', NULL, '1', '2022-11-18 00:18:34', '1', '2023-01-15 18:09:43', b'0', 'Active');

SET @parent = 1244;

INSERT INTO `system_menu` (`name`, `permission`, `menu_type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `is_show_in_menu_bar`, `name_en`, `keepalive`, `redirect`)
VALUES ('货币汇率', 'ecw:currency:rate:query', 2, 3, @parent, 'currencyRate', 'money', 'ecw/currencyRate/index', 0, '115', '2023-07-09 16:02:32', '115', '2023-07-12 22:33:09', b'0', b'1', 'currency rate', b'0', NULL);

SET @parent = LAST_INSERT_ID();

INSERT INTO `system_menu`
(`name`, `permission`, `menu_type`, `sort`, `parent_id`, `path`, `icon`, `component`, `status`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `is_show_in_menu_bar`, `name_en`, `keepalive`, `redirect`)
VALUES
('货币汇率新增', 'ecw:currency:rate:create', 3, 1, @parent, '', '', '', 0, '115', '2023-07-09 16:02:32', '115', '2023-07-12 22:33:09', b'0', b'0', 'create', b'0', NULL),
('货币汇率编辑', 'ecw:currency:rate:update', 3, 2, @parent, '', '', '', 0, '115', '2023-07-09 16:02:32', '115', '2023-07-12 22:33:09', b'0', b'0', 'update', b'0', NULL),
('货币汇率删除', 'ecw:currency:rate:delete', 3, 3, @parent, '', '', '', 0, '115', '2023-07-09 16:02:32', '115', '2023-07-12 22:33:09', b'0', b'0', 'delete', b'0', NULL),
('货币汇率日志', 'ecw:currency:rate:history', 3, 4, @parent, '', '', '', 0, '115', '2023-07-09 16:02:32', '115', '2023-07-12 22:33:09', b'0', b'0', 'history', b'0', NULL);

COMMIT;