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');