update_V1.7.sql

alter table `ecw_customer`
    add column `name_en` varchar(255) default null COMMENT '客户英文名称';


alter table `ecw_order_consignor`
    add column `name_en` varchar(255) default null COMMENT '客户英文名称' after `name`;

alter table `ecw_order_consignor`
    add column `company_en` varchar(255) default null COMMENT '公司英文名称' after `company`;

alter table `ecw_order_consignee`
    add column `name_en` varchar(255) default null COMMENT '客户英文名称' after `name`;

alter table `ecw_order_consignee`
    add column `company_en` varchar(255) default null COMMENT '公司英文名称' after `company`;

alter table `ecw_order_cargo_control`
    add column `name_en` varchar(255) default null COMMENT '控货人英文名称' after `name`;

alter table `ecw_order_cargo_control`
    add column `company_en` varchar(255) default null COMMENT '控货人公司英文名称' after `company`;

alter table `ecw_order_cargo_control_pick`
    add column `name_en` varchar(255) default null COMMENT '控货人客户英文名称' after `name`;

alter table `ecw_order_cargo_control_pick`
    add column `company_en` varchar(255) default null COMMENT '控货人公司英文名称' after `company`;

alter table `ecw_order_cargo_control_pick`
    add column `consignee_name_en` varchar(255) default null COMMENT '收货人英文名称' after `consignee_name`;


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 (18, '新增商品异常', 'goods_add_exception', 'order_error_type', 0, 'default', '', NULL, '1',
        '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'goods add exception');

INSERT INTO `jiedao`.`system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`,
                                         `update_time`, `deleted`)
VALUES ('订单服务类型', 'order_service_type', 0, NULL, '115', '2023-04-26 18:38:16', '115', '2023-04-26 18:38:40',
        b'0');
INSERT INTO `jiedao`.`system_dict_type` (`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`,
                                         `update_time`, `deleted`)
VALUES ('处理新增商品异常处理结果', 'goods_add_exception_result', 0, NULL, '1', '2022-10-06 22:23:10', '1',
        '2022-10-06 22:23:10', 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 (1, '待处理', 'pending', 'goods_add_exception_result', 0, 'default', '', NULL, '1',
        '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'goods absent');

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 (2, '系统已有商品,修改入仓信息', 'goods_exists', 'goods_add_exception_result', 0, 'default', '', NULL, '1',
        '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'goods absent');


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 (3, '系统没有此商品,去新增', 'goods_absent', 'goods_add_exception_result', 0, 'default', '', NULL, '1',
        '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'goods absent');


-- 20230427 添加包装类型铁架
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 (9, '铁架', '9', 'packaging_type', 0, 'default', '', NULL, '1',
        '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'Iron frame');


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 (7, '当天捞取公海客户数量限制', '030', 'customer_time', 0, 'default', '', NULL, '1',
        '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'catch_customer_limit_of_day');
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 (8, '当月捞取公海客户数量限制', '500', 'customer_time', 0, 'default', '', NULL, '1',
        '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'catch_customer_limit_of_month');


-- 20230430 新增公司英文名称、联系人英文名称、付款人姓名
alter table `ecw_customer`
    add column `company_en` varchar(255) default null COMMENT '公司英文名称';

alter table `ecw_customer`
    add column `payer_name` varchar(255) default null COMMENT '付款人姓名';

alter table `ecw_customer_contacts`
    add column `name_en` varchar(255) default null COMMENT '联系人英文';


alter table `ecw_order`
    add column `guan_lian_order_status` json default null COMMENT '关联订单状态';


-- 20230502 添加新增报价单延期次数
alter table `ecw_customer`
    add column `create_offer_postpone_num` int default 0 COMMENT '新增报价单延期次数';


-- 20230503 web/app 添加英文名称和公司英文名称
alter table `ecw_admin_client`
    add column `name_en` varchar(255) default null COMMENT '英文名称';
alter table `ecw_admin_client`
    add column `company_en` varchar(255) default null COMMENT '公司英文名称';


-- INSERT INTO `jiedao`.`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:exception:newProduct', 3, 17, 1629, '', '', '', 0, '115', '2023-04-06 17:03:23', '115', '2023-04-06 17:03:23', b'0', b'1', 'new product exception', b'0', NULL);


ALTER table ecw_box
    ADD COLUMN `start_country_id` BIGINT DEFAULT '0' COMMENT '始发国家ID';
ALTER table ecw_box
    ADD COLUMN `dest_country_id` BIGINT DEFAULT '0' COMMENT '目的国家ID';
ALTER table ecw_box
    ADD COLUMN `num` INT DEFAULT '0';

UPDATE ecw_box AS b
    JOIN ecw_warehouse AS w1
ON b.start_warehouse_id = w1.id
    JOIN ecw_warehouse AS w2 ON b.dest_warehouse_id = w2.id
    SET b.start_country_id = w1.guojia, b.dest_country_id = w2.guojia;


UPDATE ecw_box
SET num = RIGHT (self_no, 4);

alter table ecw_order_warehouse_approval
    add COLUMN img_url VARCHAR(2000) COMMENT '退仓图片url';



alter table `ecw_order_exception_result`
    add column `customer_service` bigint default null COMMENT '客户经理';
alter table `ecw_order_exception_result`
    add column `product_id` bigint default null COMMENT '商品ID';
alter table `ecw_order_exception_result`
    add column `files` varchar(500) default null COMMENT '文件';
alter table `ecw_order_exception_result`
    add column `currency` bigint default null COMMENT '币种';
alter table `ecw_order_exception_result`
    add column `amount` decimal(10, 2) default null COMMENT '金额';



-- 【已到港】修改成【已到目的港】
UPDATE ecw_order_time SET title_zh = CONCAT('已到目的港','   ',create_time) WHERE title_zh LIKE '已到港%';
UPDATE ecw_order_time SET type = 0 WHERE type = 99;

-- 拆单记录做过期处理，不能删除，要查历史记录编号，做标签排序，避免跳号
alter table `ecw_order_split`
    add column `used` bit(1) default 0 COMMENT '是否已使用';
alter table `ecw_order_split_item`
    add column `used` bit(1) default 0 COMMENT '是否已使用';

update system_dict_data set label ='已到目的港，待卸港' where dict_type in ('order_shipment_state','shipment_sea_air_state')  and label = '已到港，待卸港'




-- 菜单同步
SET FOREIGN_KEY_CHECKS = 0;

DELETE FROM `jiedao`.`system_dict_data` WHERE `id` = 2144;

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2134, 15, '仓租费', '15', 'receivable_fee_type', 0, 'default', '', '仓租费', '115', '2023-04-25 20:46:26', '115', '2023-04-25 20:46:59', b'0', 'warehouse rental fee');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2135, 0, '普通订单', '0', 'order_service_type', 0, 'default', '', NULL, '115', '2023-04-26 18:39:22', '115', '2023-04-26 18:39:22', b'0', 'Ordinary orders');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2136, 1, '集运服务', '1', 'order_service_type', 0, 'default', '', NULL, '115', '2023-04-26 18:39:56', '115', '2023-04-26 18:39:56', b'0', 'Consolidation service');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2137, 2, '海外仓', '2', 'order_service_type', 0, 'default', '', NULL, '115', '2023-04-26 18:40:21', '115', '2023-04-26 18:40:21', b'0', 'Overseas warehouse');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2138, 9, '铁架', '9', 'packaging_type', 0, 'default', '', NULL, '1', '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'Iron frame');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2139, 24, '预装反审核', '24', 'order_approval_type', 0, 'default', '', NULL, '115', '2023-04-27 13:43:18', '115', '2023-04-27 13:43:18', b'0', 'Pre installation de approval');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2140, 25, '封柜反审核', '25', 'order_approval_type', 0, 'default', '', NULL, '115', '2023-04-27 13:43:56', '115', '2023-04-27 13:43:56', b'0', 'Sealing and de-approving');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2148, 7, '当天捞取公海客户数量限制', '32', 'customer_time', 0, 'default', '', NULL, '1', '2022-09-08 20:40:21', '115', '2023-04-29 01:53:35', b'0', 'catch_customer_limit_of_day');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2149, 8, '当月捞取公海客户数量限制', '500', 'customer_time', 0, 'default', '', NULL, '1', '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'catch_customer_limit_of_month');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2150, 6, '甩柜', '6', 'shipping_ticket_exception', 0, 'default', '', NULL, '1', '2023-04-29 19:43:28', '1', '2023-04-29 19:43:28', b'0', 'Swing cabinet');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2151, 2, '天气原因 ', '3', 'arrival_error_type', 0, 'default', '', NULL, '1', '2023-04-29 19:50:06', '1', '2023-05-09 22:07:59', b'1', 'bad weather');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2152, 7, '天气原因', '7', 'shipping_ticket_exception', 0, 'default', '', NULL, '1', '2023-05-09 22:08:45', '1', '2023-05-09 22:08:45', b'0', 'weather problem ');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2153, 3, '放货中', '3', 'control_goods_status', 0, 'default', '', '此状态值为组合状态，当已放完货，但未全部复核完毕时为此状态，仅供查询条件传参使用', '115', '2023-05-11 20:44:16', '115', '2023-05-11 20:44:16', b'0', 'In transit');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2154, 18, '新增商品异常', 'goods_add_exception', 'order_error_type', 0, 'default', '', NULL, '1', '2022-09-08 20:40:21', '115', '2023-03-31 22:18:14', b'0', 'goods add exception');

INSERT INTO `jiedao`.`system_dict_data` (`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2157, 0, '广园西营业部', '23', 'customer_source', 0, 'default', '', NULL, '1', '2023-03-15 10:27:50', '1', '2023-03-15 10:27:50', b'0', 'GuangyuanXi Branch');

UPDATE `jiedao`.`system_dict_data` SET `sort` = 1, `label` = '海运订舱货代', `value` = '1', `dict_type` = 'company_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-05-18 10:50:49', `updater` = '1', `update_time` = '2023-05-13 19:50:38', `deleted` = b'0', `label_en` = 'Sea freight forwarder\r' WHERE `id` = 1197;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 4, `label` = '海关拖车', `value` = '4', `dict_type` = 'company_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-05-18 10:51:58', `updater` = '1', `update_time` = '2023-05-13 19:50:42', `deleted` = b'0', `label_en` = 'trailer\r' WHERE `id` = 1200;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 5, `label` = '空运订舱货代', `value` = '5', `dict_type` = 'company_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-05-18 10:52:19', `updater` = '1', `update_time` = '2023-05-13 19:50:46', `deleted` = b'0', `label_en` = 'Air Freight Forwarder\r' WHERE `id` = 1201;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 6, `label` = '清关代理', `value` = '6', `dict_type` = 'company_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-05-18 10:52:56', `updater` = '1', `update_time` = '2023-01-15 18:08:28', `deleted` = b'0', `label_en` = 'Clearing agent\r' WHERE `id` = 1202;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 8, `label` = '快递公司', `value` = '8', `dict_type` = 'company_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-05-18 10:53:52', `updater` = '1', `update_time` = '2023-01-15 18:08:28', `deleted` = b'0', `label_en` = 'express company\r' WHERE `id` = 1204;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 1, `label` = '已放完货', `value` = '1', `dict_type` = 'control_goods_status', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-07-05 15:36:11', `updater` = '115', `update_time` = '2023-05-11 20:45:29', `deleted` = b'0', `label_en` = 'released\r' WHERE `id` = 1493;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 2, `label` = '部分控货', `value` = '2', `dict_type` = 'control_goods_status', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-07-05 15:37:04', `updater` = '115', `update_time` = '2023-05-11 20:45:35', `deleted` = b'0', `label_en` = 'parts goods on hold\r' WHERE `id` = 1494;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 0, `label` = '首次入仓时间', `value` = 'RucangTime', `dict_type` = 'begintime_type_endtime', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-07-15 15:45:16', `updater` = '115', `update_time` = '2023-04-20 10:29:59', `deleted` = b'0', `label_en` = 'First entry time' WHERE `id` = 1558;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 28, `label` = '已到目的港，待卸港', `value` = '328', `dict_type` = 'order_shipment_state', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-09-28 11:48:56', `updater` = '1', `update_time` = '2023-05-19 15:52:34', `deleted` = b'0', `label_en` = 'ARRIVAL_WAIT_DISCHARGE\r' WHERE `id` = 1913;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 28, `label` = '已到目的港，待卸港', `value` = '328', `dict_type` = 'shipment_sea_air_state', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-09-28 11:48:56', `updater` = '1', `update_time` = '2023-05-19 15:53:50', `deleted` = b'0', `label_en` = 'ARRIVAL_WAIT_DISCHARGE\r' WHERE `id` = 1981;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 3, `label` = '自定义', `value` = '0', `dict_type` = 'arrival_error_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-11-16 22:36:36', `updater` = '1', `update_time` = '2023-04-29 19:50:13', `deleted` = b'0', `label_en` = 'customize\r' WHERE `id` = 2029;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 0, `label` = '最新入仓修改时间', `value` = 'WarehouseInTime', `dict_type` = 'begintime_type_endtime', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '125', `create_time` = '2023-03-28 13:33:07', `updater` = '115', `update_time` = '2023-04-20 10:31:12', `deleted` = b'0', `label_en` = 'Latest warehouse entry modification time' WHERE `id` = 2128;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 13, `label` = '派送费', `value` = '13', `dict_type` = 'receivable_fee_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = '派送费', `creator` = '115', `create_time` = '2023-04-25 20:44:55', `updater` = '115', `update_time` = '2023-04-25 20:45:29', `deleted` = b'0', `label_en` = 'delivery fee' WHERE `id` = 2132;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 14, `label` = '装卸费', `value` = '14', `dict_type` = 'receivable_fee_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = '装卸费', `creator` = '115', `create_time` = '2023-04-25 20:46:11', `updater` = '115', `update_time` = '2023-04-25 20:46:37', `deleted` = b'0', `label_en` = 'handling cost' WHERE `id` = 2133;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 26, `label` = '卸柜反审核', `value` = '26', `dict_type` = 'order_approval_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '115', `create_time` = '2023-04-27 13:44:29', `updater` = '115', `update_time` = '2023-04-27 13:44:29', `deleted` = b'0', `label_en` = 'De-approval of container unloading' WHERE `id` = 2141;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 27, `label` = '报关退场审核', `value` = '27', `dict_type` = 'order_approval_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '115', `create_time` = '2023-04-27 13:45:29', `updater` = '115', `update_time` = '2023-04-27 13:45:29', `deleted` = b'0', `label_en` = 'Customs clearance and exit review' WHERE `id` = 2142;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 28, `label` = '撤销拆单审批', `value` = '28', `dict_type` = 'order_approval_type', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '115', `create_time` = '2023-04-27 13:46:02', `updater` = '115', `update_time` = '2023-04-27 13:46:02', `deleted` = b'0', `label_en` = 'Revoke split approval' WHERE `id` = 2143;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 1, `label` = '待处理', `value` = 'pending', `dict_type` = 'goods_add_exception_result', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-09-08 20:40:21', `updater` = '1', `update_time` = '2023-05-05 23:59:02', `deleted` = b'0', `label_en` = 'pending' WHERE `id` = 2145;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 2, `label` = '系统已有商品,修改入仓信息', `value` = 'goods_exists', `dict_type` = 'goods_add_exception_result', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-09-08 20:40:21', `updater` = '115', `update_time` = '2023-03-31 22:18:14', `deleted` = b'0', `label_en` = 'goods absent' WHERE `id` = 2146;

UPDATE `jiedao`.`system_dict_data` SET `sort` = 3, `label` = '系统没有此商品,去新增', `value` = 'goods_absent', `dict_type` = 'goods_add_exception_result', `status` = 0, `color_type` = 'default', `css_class` = '', `remark` = NULL, `creator` = '1', `create_time` = '2022-09-08 20:40:21', `updater` = '115', `update_time` = '2023-03-31 22:18:14', `deleted` = b'0', `label_en` = 'goods absent' WHERE `id` = 2147;

DELETE FROM `jiedao`.`system_dict_type` WHERE `id` = 362;

UPDATE `jiedao`.`system_dict_type` SET `name` = '新增商品异常处理结果', `type` = 'goods_add_exception_result', `status` = 0, `remark` = NULL, `creator` = '1', `create_time` = '2022-10-06 22:23:10', `updater` = '1', `update_time` = '2022-10-06 22:23:10', `deleted` = b'0' WHERE `id` = 361;

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2110, '撤销拆单', 'ecw:order:split_revoke', 3, 1, 1852, '', '', '', 0, '115', '2023-04-26 14:50:28', '115', '2023-05-14 14:08:20', b'0', b'1', 'cancel split', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2111, '新增商品异常', 'ecw:exception:goodsAddException', 3, 17, 1629, '', '', '', 0, '115', '2023-04-06 17:03:23', '115', '2023-05-14 12:11:23', b'0', b'1', 'new product exception', b'0', NULL);

SET FOREIGN_KEY_CHECKS = 1;



-- 优化订单主表ecw_order的结构，删减无用字段
ALTER TABLE ecw_order DROP `location_name`;
ALTER TABLE ecw_order DROP `location_id`;
ALTER TABLE ecw_order DROP `area_id`;
ALTER TABLE ecw_order DROP `ware_id`;
ALTER TABLE ecw_order DROP `coupon_info`;
ALTER TABLE ecw_order DROP `todo_detail`;
ALTER TABLE ecw_order DROP `box_fee`;
ALTER TABLE ecw_order DROP `plate_fee`;
ALTER TABLE ecw_order DROP `trans_currency`;
ALTER TABLE ecw_order DROP `trans_fee`;
ALTER TABLE ecw_order DROP `advance_type`;
ALTER TABLE ecw_order DROP `is_todo_order`;
ALTER TABLE ecw_order DROP `commission_id`;
ALTER TABLE ecw_order DROP `country_code`;
ALTER TABLE ecw_order DROP `country_abbreviation`;


-- 添加拆单规格记录(入仓记录)列表 ecw_order_split_item zhengYi 2023-06-08

ALTER table ecw_order_split_item
    ADD COLUMN `specs_record` json DEFAULT NULL COMMENT '规格记录';

-- 添加报关退场订单的总箱数和实退箱数 ecw_box_order_exit zhengYi 2023-06-11
ALTER table ecw_box_order_exit
    ADD COLUMN `num` int DEFAULT 0 COMMENT '订单实际箱数';
ALTER table ecw_box_order_exit
    ADD COLUMN `refund_num` int DEFAULT 0 COMMENT '订单退场箱数';
ALTER table ecw_box_order_exit
    ADD COLUMN `split_status` tinyint DEFAULT 0 COMMENT '0 不需要拆单 1 部分退场待拆单 2 部分退场拆单中 3 部分退场已拆单';
ALTER table ecw_box_order_exit
    ADD COLUMN `split` bit DEFAULT 0 COMMENT '是否拆单后的待退场订单';
ALTER table ecw_order_split
    ADD COLUMN `refund_num` int DEFAULT 0 COMMENT '退场订单箱数';

CREATE TABLE `ecw_box_customs_split` (
                                         `id` bigint NOT NULL AUTO_INCREMENT,
                                         `shipment_id` bigint DEFAULT '0' COMMENT '出货ID',
                                         `split_order_ids` text COLLATE utf8mb4_general_ci COMMENT '拆单的订单',
                                         `deal_status` tinyint DEFAULT '0' COMMENT '处理状态，0-未处理，1-已处理',
                                         `creator` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建者',
                                         `create_time` datetime NOT NULL COMMENT '创建时间',
                                         `updater` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
                                         `update_time` datetime DEFAULT NULL COMMENT '更新时间',
                                         `deleted` bit(1) DEFAULT b'0' COMMENT '是否删除',
                                         PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='出货报关拆单提醒';


-- 20230615 yanghao 添加客户操作日志的操作人姓名。
alter table `ecw_customer_operate_log`
    add column `operator_name` varchar(64) default null COMMENT '操作人姓名';
-- 20230615 yanghao 更新老数据
update ecw_customer_operate_log a, member_user b
set a.operator_name = b.nickname
where (a.remark = '客户下单' or a.remark like '会员注册通过手机关联找到收%' or a.remark like '会员注册关联创建客户%')
  and a.operator = b.id;
update ecw_customer_operate_log a, system_user b
set a.operator_name = b.nickname
where (a.remark != '客户下单' and a.remark not like '会员注册通过手机关联找到收%' and a.remark not like '会员注册关联创建客户%')
  and a.operator = b.id;


-- 20230625 zhengyi 解决客户联系人手机号、收货人手机号因补0导致重复新建客户的bug的sql
-- 查询订单收货人当前的客户联系人id和客户id,且列出最早创建的相同手机号客户信息
SELECT
    nee.customer_id,
    IF(cc.ab = 1 and cc.a_deleted = 0,cc.a_customer_id,cc.b_customer_id) as cc_customer_id,
    nee.customer_contacts_id,
    IF(cc.ab = 1 and cc.a_deleted = 0,cc.a_contacts_id,cc.b_contacts_id) as cc_customer_contacts_id,
    nee.order_id
FROM ecw_order_consignee nee
         LEFT JOIN
     (select
          a.id as a_contacts_id,
          a.customer_id as a_customer_id,
          a.create_time as a_create_time,
          a.deleted as a_deleted,
          b.id as b_contacts_id,
          b.customer_id as b_customer_id,
          b.create_time as b_create_time,
          IF(a.create_time > b.create_time, 0,1) as ab,
          b.deleted as b_deleted
      from ecw_customer_contacts a
               LEFT JOIN ecw_customer_contacts b
                         ON a.phone_new = CONCAT('0', b.phone_new) AND a.area_code = b.area_code
      where a.phone_new like '0%' AND b.phone_new not like '0%') cc
     ON (cc.ab = 0 AND cc.a_contacts_id = nee.customer_contacts_id) or (cc.ab = 1 AND cc.b_contacts_id = nee.customer_contacts_id)
WHERE cc.a_contacts_id in (
    select
        b.id
    from ecw_customer_contacts b
             LEFT JOIN ecw_customer_contacts d
                       ON b.phone_new = CONCAT('0', d.phone_new) AND b.area_code = d.area_code
    where b.phone_new like '0%' AND d.phone_new not like '0%'
);

-- 更新订单收货人客户联系人id和客户id为最早创建的相同手机号客户信息
UPDATE
    ecw_order_consignee nee
    LEFT JOIN
    (select
    a.id as a_contacts_id,
    a.customer_id as a_customer_id,
    a.create_time as a_create_time,
    a.deleted as a_deleted,
    b.id as b_contacts_id,
    b.customer_id as b_customer_id,
    b.create_time as b_create_time,
    IF(a.create_time > b.create_time, 0,1) as ab,
    b.deleted as b_deleted
    from ecw_customer_contacts a
    LEFT JOIN ecw_customer_contacts b
    ON a.phone_new = CONCAT('0', b.phone_new) AND a.area_code = b.area_code
    where a.phone_new like '0%' AND b.phone_new not like '0%') cc
ON (cc.ab = 0 AND cc.a_contacts_id = nee.customer_contacts_id) or (cc.ab = 1 AND cc.b_contacts_id = nee.customer_contacts_id)
    SET nee.customer_id = IF(cc.ab = 1,cc.a_customer_id,cc.b_customer_id),
        nee.customer_contacts_id = IF(cc.ab = 1,cc.a_contacts_id,cc.b_contacts_id)
WHERE cc.a_contacts_id in (
    select
    b.id
    from ecw_customer_contacts b
    LEFT JOIN ecw_customer_contacts d
    ON b.phone_new = CONCAT('0', d.phone_new) AND b.area_code = d.area_code
    where b.phone_new like '0%' AND d.phone_new not like '0%'
    );



-- 查询需要二选一的客户联系人信息
select
    a.phone_new as a_phone_new,
    a.id as a_contacts_id,
    a.customer_id as a_customer_id,
    a.create_time as a_create_time,
    a.deleted as a_deleted,
    b.phone_new as b_phone_new,
    b.id as b_contacts_id,
    b.customer_id as b_customer_id,
    b.create_time as b_create_time,
    IF(a.create_time > b.create_time, 0,1) as ab,
    b.deleted as b_deleted
from ecw_customer_contacts a
         LEFT JOIN ecw_customer_contacts b
                   ON a.phone_new = CONCAT('0', b.phone_new) AND a.area_code = b.area_code
where a.phone_new like '0%' AND b.phone_new not like '0%' AND (a.deleted = 0 or b.deleted = 0);

-- 二选一更新最先创建的客户联系人信息
UPDATE
    ecw_customer_contacts a
    LEFT JOIN ecw_customer_contacts ac
ON a.customer_id = ac.id
    LEFT JOIN ecw_customer_contacts b
    ON a.phone_new = CONCAT('0', b.phone_new) AND a.area_code = b.area_code
    LEFT JOIN ecw_customer_contacts bc
    ON b.customer_id = bc.id
    SET a.deleted = IF(a.create_time > b.create_time AND b.deleted = 0, 1,0) ,ac.deleted = IF(a.create_time > b.create_time AND b.deleted = 0, 1,0) , b.deleted = !IF(a.create_time > b.create_time AND b.deleted = 0, 1,0),bc.deleted = !IF(a.create_time > b.create_time AND b.deleted = 0, 1,0)
where a.phone_new like '0%' AND b.phone_new not like '0%' ;



-- 20230625 yanghao 处理非225的以0开头的客户联系人电话和订单收货人电话，删除开头的0。
update ecw_customer_contacts set phone_new = right(phone_new, length(phone_new)-1) where area_code != 225 and phone_new like '0%' and deleted = 0;
update ecw_order_consignee set phone = right(phone, length(phone)-1) where country_code != 225 and phone like '0%' and deleted = 0;
update ecw_order_consignor set phone = right(phone, length(phone)-1) where country_code != 225 and phone like '0%' and deleted = 0;
update member_user set mobile = right(mobile, length(mobile)-1) where area_code != 225 and mobile like '0%' and deleted = 0;


-- 涉及表ecw_customer_contacts，ecw_order_consignee,ecw_order_consignor,member_user

-- 20230627 zhengYi 拆单后的订单项保存原订单项id
alter table `ecw_order_item`
    add column `org_item_id` bigint default null COMMENT '原订单项id';

-- 20230628 zhengYi 短信日志标识重发次数，及重发上级日志id
ALTER table system_sms_log
    ADD COLUMN `sms_log_id` bigint DEFAULT NULL COMMENT '重发的短信日志id';
ALTER table system_sms_log
    ADD COLUMN `num` int DEFAULT 0 COMMENT '重发次数';



-- 20230704 新增pda权限标识
SET FOREIGN_KEY_CHECKS = 0;

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2112, 'PDA', '', 1, 999, 0, '/pda', '#', NULL, 0, '115', '2023-07-04 20:25:46', '115', '2023-07-04 20:25:46', b'0', b'0', 'pda', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2113, '入仓', 'pda:warehousing', 3, 0, 2112, '', '', '', 0, '115', '2023-07-04 20:26:20', '115', '2023-07-04 20:26:20', b'0', b'1', 'warehousing', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2114, '卸柜到仓', 'pda:unloadCabinet', 3, 1, 2112, '', '', '', 0, '115', '2023-07-04 20:26:51', '115', '2023-07-04 20:26:51', b'0', b'1', 'unloadCabinet', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2115, '调拨到仓', 'pda:warehouseAdjustment', 3, 2, 2112, '', '', '', 0, '115', '2023-07-04 20:29:36', '115', '2023-07-04 20:29:36', b'0', b'1', 'warehouseAdjustment', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2116, '退场到仓', 'pda:ExitWarehouse', 3, 3, 2112, '', '', '', 0, '115', '2023-07-04 20:29:58', '115', '2023-07-04 20:29:58', b'0', b'1', 'ExitWarehouse', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2117, '空运到仓', 'pda:airUnloadCabinet', 3, 4, 2112, '', '', '', 0, '115', '2023-07-04 20:30:24', '115', '2023-07-04 20:30:24', b'0', b'1', 'airUnloadCabinet', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2118, '空运备货', 'pda:stocking', 3, 5, 2112, '', '', '', 0, '115', '2023-07-04 20:30:52', '115', '2023-07-04 20:30:52', b'0', b'1', 'stocking', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2119, '装柜', 'pda:loading', 3, 6, 2112, '', '', '', 0, '115', '2023-07-04 20:31:15', '115', '2023-07-04 20:31:15', b'0', b'1', 'loading', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2120, '调拨出仓', 'pda:transferWarehouseOut', 3, 7, 2112, '', '', '', 0, '115', '2023-07-04 20:31:39', '115', '2023-07-04 20:31:39', b'0', b'1', 'transferWarehouseOut', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2121, '理货', 'pda:tally', 3, 8, 2112, '', '', '', 0, '115', '2023-07-04 20:32:07', '115', '2023-07-04 20:32:07', b'0', b'1', 'tally', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2122, '空运出仓', 'pda:airWarehouseOut', 3, 9, 2112, '', '', '', 0, '115', '2023-07-04 20:32:34', '115', '2023-07-04 20:32:34', b'0', b'1', 'airWarehouseOut', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2123, '合包查询', 'pda:packTogetherSearch', 3, 10, 2112, '', '', '', 0, '115', '2023-07-04 20:33:00', '115', '2023-07-04 20:33:00', b'0', b'1', 'packTogetherSearch', b'0', NULL);

INSERT INTO `jiedao`.`system_menu` (`id`, `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 (2124, '证件查询', 'pda:certificateSearch', 3, 11, 2112, '', '', '', 0, '115', '2023-07-04 20:33:21', '115', '2023-07-04 20:33:21', b'0', b'1', 'certificateSearch', b'0', NULL);

SET FOREIGN_KEY_CHECKS = 1;

-- zhengYi 20230707 退场订单标记是否已完成退场入仓操，
ALTER table ecw_box_order_exit
    ADD COLUMN `exit_warehouse_in` bit DEFAULT NULL COMMENT '新的退场订单标记是否已完成到仓入仓业务';

-- 出货提醒字典
INSERT INTO `jiedao`.`system_dict_data`(`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2164, 0, '报关部分退场入仓拆单审核通过', '9', 'shipping_notice_type', 0, 'default', '', NULL, '115', '2023-07-07 22:43:36', '115', '2023-07-07 22:43:36', b'0', '报关部分退场入仓拆单审核通过En');



INSERT INTO `jiedao`.`system_dict_type`(`id`, `name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (364, '货物描述包装单位', 'goods_package_type', 0, NULL, '115', '2023-07-08 14:39:24', '115', '2023-07-08 14:39:24', b'0');


INSERT INTO `jiedao`.`system_dict_data`(`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2168, 4, 'piece', '4', 'goods_package_type', 0, 'default', '', NULL, '115', '2023-07-08 14:40:33', '115', '2023-07-08 14:40:33', b'0', 'piece');
INSERT INTO `jiedao`.`system_dict_data`(`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2167, 3, 'pallet', '3', 'goods_package_type', 0, 'default', '', NULL, '115', '2023-07-08 14:40:19', '115', '2023-07-08 14:40:19', b'0', 'pallet');
INSERT INTO `jiedao`.`system_dict_data`(`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2166, 2, 'package', '2', 'goods_package_type', 0, 'default', '', NULL, '115', '2023-07-08 14:40:02', '115', '2023-07-08 14:40:02', b'0', 'package');
INSERT INTO `jiedao`.`system_dict_data`(`id`, `sort`, `label`, `value`, `dict_type`, `status`, `color_type`, `css_class`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`, `label_en`) VALUES (2165, 1, 'Carton', '1', 'goods_package_type', 0, 'default', '', NULL, '115', '2023-07-08 14:39:52', '115', '2023-07-08 14:39:52', b'0', 'Carton');