-- 修改的sql，修改时间
-- 插入新增的菜单权限sql，11-3
INSERT INTO `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 (1689, '创建客户', 'ecw:customer:create', 3, 1, 1561, '', '', '', 0, '1', '2022-10-29 13:11:32', '1', '2022-10-29 13:14:05', b'0', b'1', 'Customer Creation', b'0', NULL);
INSERT INTO `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 (1690, '核销', 'ecw:verification:update', 3, 1, 1694, '', '', '', 0, '1', '2022-11-01 17:13:28', '115', '2022-11-01 20:58:43', b'0', b'1', 'verification', b'0', NULL);
INSERT INTO `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 (1691, '批量核销', 'ecw:verification:update', 3, 2, 1694, '', '', '', 0, '1', '2022-11-01 17:16:15', '115', '2022-11-01 20:58:49', b'0', b'1', 'btachVerification', b'0', NULL);
INSERT INTO `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 (1692, '全部核销', 'ecw:verification:update', 3, 2, 1694, '', '', '', 0, '1', '2022-11-01 17:16:50', '115', '2022-11-01 20:58:54', b'0', b'1', 'verification all', b'0', NULL);
INSERT INTO `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 (1693, '反核销', 'ecw:verification:update', 3, 2, 1694, '', '', '', 0, '1', '2022-11-01 17:24:22', '115', '2022-11-01 20:58:59', b'0', b'1', 'cancel verification', b'0', NULL);
INSERT INTO `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 (1694, '收款单按钮权限', '', 2, 1, 1697, 'ecw/financial/jurisdiction', '#', NULL, 0, '115', '2022-11-01 20:58:30', '115', '2022-11-02 23:51:01', b'0', b'0', 'Collection Button Permission', b'0', NULL);
INSERT INTO `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 (1695, '打印收款单', '', 2, 8, 1624, 'printVoucher', 'education', 'ecw/financial/printVoucher', 0, '1', '2022-11-02 19:07:06', '1', '2022-11-02 19:09:06', b'0', b'0', 'Print Collection Doc', b'0', NULL);
INSERT INTO `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 (1696, '订单特价-管理折扣', 'ecw:order:discount', 3, 0, 1697, '', '', '', 0, '1', '2022-11-02 22:47:44', '115', '2022-11-02 23:51:42', b'0', b'1', 'OrderDiscount', b'0', NULL);
INSERT INTO `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 (1697, '按钮权限管理', '', 2, 10, 0, '/ecw/button/permission', '#', NULL, 0, '115', '2022-11-02 22:55:37', '115', '2022-11-02 23:50:28', b'0', b'0', 'Button Permission Management', b'0', NULL);
INSERT INTO `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 (1698, '付款单按钮权限', '', 2, 3, 1697, 'ecw/financial/payment', '#', NULL, 0, '1', '2022-11-03 20:39:28', '1', '2022-11-03 20:39:28', b'0', b'0', 'Payment Doc Button Permission', b'0', NULL);
INSERT INTO `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 (1699, '审核', 'ecw:payment:update', 3, 1, 1698, '', '', '', 0, '1', '2022-11-03 20:40:40', '1', '2022-11-03 20:40:40', b'0', b'1', 'to examine', b'0', NULL);
INSERT INTO `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 (1700, '反审核', 'ecw:payment:update', 3, 2, 1698, '', '', '', 0, '1', '2022-11-03 20:43:54', '1', '2022-11-03 20:43:54', b'0', b'1', 'De approve', b'0', NULL);
INSERT INTO `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 (1701, '核销', 'ecw:payment:update', 3, 3, 1698, '', '', '', 0, '1', '2022-11-03 20:44:40', '1', '2022-11-03 20:44:40', b'0', b'1', 'Write off', b'0', NULL);
INSERT INTO `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 (1702, '反核销', 'ecw:payment:update', 3, 4, 1698, '', '', '', 0, '1', '2022-11-03 20:45:53', '1', '2022-11-03 20:45:53', b'0', b'1', 'Cancel Write off', b'0', NULL);
INSERT INTO `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 (1703, '打印', 'ecw:payment:query', 3, 5, 1698, '', '', '', 0, '1', '2022-11-03 20:47:12', '1', '2022-11-03 20:47:12', b'0', b'1', 'print', b'0', NULL);
INSERT INTO `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 (1704, '删除', 'ecw:payment:delete', 3, 6, 1698, '', '', '', 0, '1', '2022-11-03 20:47:55', '1', '2022-11-03 20:47:55', b'0', b'1', 'delete', b'0', NULL);

-- 誉峰11-5
alter table ecw_product_brand_empower add COLUMN auth_type int comment '授权类型';
-- 11-17业绩
alter table ecw_target_log add COLUMN customer_id BIGINT COMMENT '业绩归属客户id';
alter table ecw_target_log add COLUMN customer_type int COMMENT '业绩归属客户类型（0公司客户、1开发客户）';
alter table ecw_target_log add COLUMN complete_weight decimal(22,7) COMMENT '已完成重量';
alter table ecw_target_log alter COLUMN complete_weight set DEFAULT '0.00';
-- 11-28 huyf 我的业绩生产数据补全处理
update ecw_target_log a ,
(
SELECT
t.order_id,
CASE
	WHEN
		LOCATE( '2', t1.type )= 0
		AND t1.is_cargo_control = 0
		AND t1.drawee != 1 THEN
			( SELECT a.customer_id FROM ecw_order_consignee a WHERE a.order_id = t1.order_id ) ELSE ( SELECT a.customer_id FROM ecw_order_consignor a WHERE a.order_id = t1.order_id )
		END as customer_id
		FROM
		ecw_target_log t
	LEFT JOIN ecw_order t1 ON t.order_id = t1.order_id) b set a.customer_id=b.customer_id where a.order_id=b.order_id;
-- 11-28 huyf 我的业绩生产数据补全处理
update ecw_target_log set complete_weight=1 where complete_weight is null;

-- 11-30 wlh 预装商品新增提单号字段
ALTER table ecw_box_preload_goods add tidan_num int4 DEFAULT 0 comment '提单序号';

-- 12-02 huyf 特需默认付款方式为到付
alter table ecw_order_special_need ALTER COLUMN pay_type set DEFAULT 4;

-- 12-08 wlh 新增路线价格修改记录表
CREATE TABLE `ecw_product_price_snapshot` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `price_id` bigint DEFAULT '0',
  `product_id` bigint NOT NULL COMMENT '产品ID',
  `warehouse_line_id` bigint NOT NULL COMMENT '线路ID',
  `shipping_channel_id` bigint DEFAULT NULL COMMENT '出货渠道ID',
  `content` json DEFAULT NULL,
  `creator_name` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建者名字',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32501 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='路线价格修改历史';


--12-08 chenjiuping BMP 会签或签升级脚本
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='batch.schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='common.schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='entitylink.schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='eventsubscription.schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='identitylink.schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='job.schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='task.schema.version';
update act_ge_property set `VALUE_`='create(6.7.2.0)'    where `NAME_`='variable.schema.version';
update act_ge_property set `VALUE_`='6.7.2.0'    where `NAME_`='schema.history';

update act_id_property set `VALUE_`='6.7.2.0'    where `NAME_`='schema.version';


alter table act_hi_procinst add column `BUSINESS_STATUS_` varchar(255) default null;

alter table ACT_RU_EXECUTION add column `BUSINESS_STATUS_` varchar(255) default null;

--12-14 huyufeng 智慧预装新增开关
INSERT INTO `system_dict_type`(`id`, `name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (345, '部分功能开关', 'part_function_switch', 0, NULL, '115', '2022-12-14 14:17:21', '115', '2022-12-14 14:17:21', b'0');
INSERT INTO `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 (2043, 0, '功能开关', '0', 'part_function_switch', 0, 'default', '', '0：关，1：开', '115', '2022-12-14 14:18:23', '115', '2022-12-14 14:20:02', b'0', 'function_switch');

-- 12-26 zhengYi 报价单和订单货物最小起始计量单位
alter table ecw_offer_prod add column `min_metering_weight` decimal(15,2) default 0 COMMENT '最小计量重量';
alter table ecw_offer_prod add column `min_metering_volume` decimal(15,2) default 0 COMMENT '最小计量体积';
alter table ecw_offer_prod add column `min_metering_quantity` decimal(15,2) default 0 COMMENT '最小计量个数';

alter table ecw_order add column `min_metering_weight` decimal(15,2) default 0 COMMENT '最小计量重量';
alter table ecw_order add column `min_metering_volume` decimal(15,2) default 0 COMMENT '最小计量体积';
alter table ecw_order add column `min_metering_quantity` decimal(15,2) default 0 COMMENT '最小计量个数';

alter table ecw_order_item add column `min_metering_weight` decimal(15,2) default 0 COMMENT '最小计量重量';
alter table ecw_order_item add column `min_metering_volume` decimal(15,2) default 0 COMMENT '最小计量体积';
alter table ecw_order_item add column `min_metering_quantity` decimal(15,2) default 0 COMMENT '最小计量个数';

-- 12-26 zhengYi web端站内信内容字段长度修改
alter table ecw_web_internal_message modify column `content` text DEFAULT NULL COMMENT '内容';

-- 12-27 huyufeng 制作提货单新增图片url用于生成pdf
alter table ecw_make_bill_of_lading add COLUMN img_url VARCHAR(200) COMMENT '制作提货单PDF地址';

-- 2023-01-05 修改订单项的优惠信息json值格式,将之前的格式置空(注：此脚本只执行一次)
UPDATE ecw_order_item SET coupon_info = null;

-- 2023-01-11 添加订单项的收费信息字段
alter table ecw_order_item add column `charge_weight` decimal(15,2) default 0 COMMENT '收费重量';
alter table ecw_order_item add column `charge_volume` decimal(15,2) default 0 COMMENT '收费体积';
alter table ecw_order_item add column `charge_quantity` decimal(15,2) default 0 COMMENT '收费个数';
alter table ecw_order_item add column `item_type` tinyint default 1 COMMENT '订单项属性：1 普货 2 重货 3 泡货';
alter table ecw_order_item add column `weight_ratio` decimal(15,2) default 0 COMMENT '货比-重货的订单比(不计算普货订单项)';
alter table ecw_order_item add column `volume_ratio` decimal(15,2) default 0 COMMENT '货比-泡货的订单比(不计算普货订单项)';



-- 2023-01-28 添加信用规则/级别规则的英文名称

alter table ecw_customer_level_rule add column `name_en` varchar(255) default null;
alter table ecw_customer_credit_rule add column `name_en` varchar(255) default null;


-- 2023-01-29 zhengYi 冗余订单产品备案属性值更新脚本
UPDATE ecw_order o
SET o.product_record = (
SELECT
    IF(d.n > 1, 4, IF(d.brandType = 0, 2,IF(d.brandType = 1, 1,3))) AS product_record
FROM
    (SELECT
        COUNT( 1 ) AS n,
        s.brandType
    FROM
        (SELECT
            IF(oi.warehouse_in_info IS NULL, oi.brand_type, oi.fee_type ) AS brandType,
            COUNT( 1 ) AS cc
        FROM
            ecw_order_item oi
        WHERE
            oi.order_id = o.order_id
        GROUP BY
            brandType
        ) s
    ) d);

-- 2023-01-29 zhengYi 字段长度同步
alter table infra_api_access_log modify column `request_params` longtext DEFAULT NULL COMMENT '请求参数';
alter table infra_api_error_log modify column `request_params` longtext DEFAULT NULL COMMENT '请求参数';

-- 2023-02-01  wlh  APP版本管理新增表
CREATE TABLE `system_version` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `app_url` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '版本链接',
  `app_type` varchar(64) COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '字典data_source',
  `app_version` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '版本号',
  `version_code` int DEFAULT NULL COMMENT '内部号',
  `force_update` int DEFAULT '0' COMMENT '是否默认更新，0-否，1-是',
  `desp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '说明',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='app版本管理';


-- 2023-02-02 zhengYi 添加报价单类型字段
alter table ecw_offer add column `service_type` varchar(32) default null COMMENT '订单服务类型(可多选)：1 集运服务 2 海外仓';

-- 2023-02-05 wanglianghe 新建app版本管理表
/*
 Navicat Premium Data Transfer

 Source Server         : jd_test_qcloud
 Source Server Type    : MySQL
 Source Server Version : 80031
 Source Host           : 159.75.224.138:2290
 Source Schema         : jiedao

 Target Server Type    : MySQL
 Target Server Version : 80031
 File Encoding         : 65001

 Date: 05/02/2023 20:13:31
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for system_version
-- ----------------------------
DROP TABLE IF EXISTS `system_version`;
CREATE TABLE `system_version` (
                                  `id` bigint NOT NULL AUTO_INCREMENT,
                                  `app_url` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '版本链接',
                                  `app_type` varchar(64) COLLATE utf8mb4_general_ci DEFAULT '0' COMMENT '字典data_source',
                                  `app_version` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '版本号',
                                  `version_code` int DEFAULT NULL COMMENT '内部号',
                                  `force_update` int DEFAULT '0' COMMENT '是否默认更新，0-否，1-是',
                                  `desp` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '说明',
                                  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
                                  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
                                  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
                                  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                                  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='app版本管理';

SET FOREIGN_KEY_CHECKS = 1;



-- 2023-02-06 zhengYi 添加订单编号变更记录字段
alter table ecw_order add column `no_charge_record` varchar(512) default null COMMENT '订单编号变更记录';


-- 2023-02-07 yanghao 添加是否已经发送入仓短信字段
alter table ecw_order add column `has_send_rucang_sms` tinyint default 0 COMMENT '0 未发送 1 已发送';

-- 2023-02-08 zhengYi 修改小计和总金额的字段小数点长度
alter table ecw_order_item modify column `sea_freight` decimal(15,0) DEFAULT 0 COMMENT '运费(小计)';
alter table ecw_order_item modify column `clearance_freight` decimal(15,0) DEFAULT 0 COMMENT '清关费(小计)';
alter table ecw_receivable modify column `total_amount` decimal(15,0) DEFAULT 0 COMMENT '总金额';

-- 2023-02-10 wlh 出货自编号新增字段
ALTER TABLE ecw_box_number_record ADD COLUMN `start_country_id` BIGINT DEFAULT '0' COMMENT '始发国家ID';
ALTER TABLE ecw_box_number_record ADD COLUMN `dest_country_id` BIGINT DEFAULT '0' COMMENT '目的国家ID';
ALTER TABLE ecw_box_number_record ADD COLUMN `start_warehouse_id` BIGINT DEFAULT '0' COMMENT '始发仓库ID';
ALTER TABLE ecw_box_number_record ADD COLUMN `dest_warehouse_id` BIGINT DEFAULT '0' COMMENT '目的仓库ID';

-- 初始化数据
INSERT INTO `ecw_box_number_record`( `start_country_id`, `dest_country_id`, `start_warehouse_id`, `dest_warehouse_id`, `transport_type`, `num`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES (130, 4174, 1, 2, '1', 3633, '1', '2023-02-11 15:26:10', '1', '2023-02-11 15:26:28', b'0');
INSERT INTO `ecw_box_number_record`( `start_country_id`, `dest_country_id`, `start_warehouse_id`, `dest_warehouse_id`, `transport_type`, `num`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 130, 4174, 3, 2, '1', 793, '1', '2023-02-11 15:26:45', '1', '2023-02-11 15:26:58', b'0');
INSERT INTO `ecw_box_number_record`( `start_country_id`, `dest_country_id`, `start_warehouse_id`, `dest_warehouse_id`, `transport_type`, `num`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 130, 4176, 1, 11, '1', 64, '1', '2023-02-11 15:27:23', '1', '2023-02-11 15:27:38', b'0');
INSERT INTO `ecw_box_number_record`( `start_country_id`, `dest_country_id`, `start_warehouse_id`, `dest_warehouse_id`, `transport_type`, `num`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 130, 4191, 1, 22, '1', 24, '1', '2023-02-11 15:28:29', '1', '2023-02-11 15:28:41', b'0');
INSERT INTO `ecw_box_number_record`( `start_country_id`, `dest_country_id`, `start_warehouse_id`, `dest_warehouse_id`, `transport_type`, `num`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 130, 4177, 1, 12, '1', 46, '1', '2023-02-11 15:29:20', '1', '2023-02-11 15:29:25', b'0');
INSERT INTO `ecw_box_number_record`( `start_country_id`, `dest_country_id`, `start_warehouse_id`, `dest_warehouse_id`, `transport_type`, `num`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 130, 4178, 1, 14, '1', 33, '1', '2023-02-11 15:29:49', '1', '2023-02-11 15:29:58', b'0');
INSERT INTO `ecw_box_number_record`( `start_country_id`, `dest_country_id`, `start_warehouse_id`, `dest_warehouse_id`, `transport_type`, `num`, `creator`, `create_time`, `updater`, `update_time`, `deleted`) VALUES ( 130, 4175, 1, 8, '1', 323, '1', '2023-02-11 15:30:19', '1', '2023-02-11 15:30:29', b'0');


-- 2023-02-13 zhengYi 修改小计金额的字段保留小数点长度
alter table ecw_order_item modify column `sea_freight` decimal(15,2) DEFAULT 0 COMMENT '运费(小计)';
alter table ecw_order_item modify column `clearance_freight` decimal(15,2) DEFAULT 0 COMMENT '清关费(小计)';
alter table ecw_receivable modify column `total_amount` decimal(15,2) DEFAULT 0 COMMENT '总金额';


-- 2023-02-14 yanghao 退仓审核添加是否来自用户端的标识
alter table ecw_order_warehouse_approval add column `is_from_user_client` tinyint DEFAULT 0 COMMENT '是否来自于用户端';

-- 2023-02-14 zhengYi 轨迹日志类型添加
alter table ecw_order_time add column `type` int DEFAULT 0 COMMENT '日志类型:0 正常主状态变更 1 拆单后创建的新订单 2 合单后创建的新订单 3 特价-优惠申请通过 4 特价-管理折扣通过 5 特价-重货优惠通过 6 特价-泡货优惠通过  7、订单修改审批通过 8 入仓修改审批通过 9 佣金设置审批通过 99 未定义';
update ecw_order_time SET type = 1 WHERE title_zh LIKE '%拆单成功,待入仓%';
update ecw_order_time SET type = 2 WHERE title_zh LIKE '%合单成功,已入仓%';
update ecw_order_time SET type = 3 WHERE title_zh LIKE '%特价-优惠申请通过%';
update ecw_order_time SET type = 4 WHERE title_zh LIKE '%特价-管理折扣通过%';
update ecw_order_time SET type = 5 WHERE title_zh LIKE '%特价-重货优惠通过%';
update ecw_order_time SET type = 6 WHERE title_zh LIKE '%特价-泡货优惠通过%';
update ecw_order_time SET type = 7 WHERE title_zh LIKE '%订单修改审批通过%';
update ecw_order_time SET type = 8 WHERE title_zh LIKE '%入仓修改审批通过%';
update ecw_order_time SET type = 9 WHERE title_zh LIKE '%佣金设置审批通过%';
update ecw_order_time SET type = 99 WHERE title_zh LIKE '%未知%';

-- 2023-02-17 zhengYi 出货短信记录管理
CREATE TABLE `ecw_box_order_sms_log` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `shipment_id` bigint DEFAULT NULL COMMENT '出货单id',
  `order_id` bigint DEFAULT NULL COMMENT '订单ID',
  `order_no` varchar(64) DEFAULT NULL COMMENT '订单编号',
  `marks` varchar(125) DEFAULT NULL COMMENT '唛头',
  `self_no` varchar(64) DEFAULT NULL COMMENT '自编号',
  `scene` int DEFAULT NULL COMMENT '场景编码',
  `template_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '说明',
  `description` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '说明',
  `creator` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updater` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
  `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB COMMENT='出货短信记录';


-- 2023-02-18 yanghao 修改标签数据  （已执行）
update ecw_order_label set `end` = 4 where order_id = 693;


-- 2023-02-19 yanghao 拆单项表添加入仓是否有牌
ALTER TABLE ecw_order_split_item ADD COLUMN `fee_type` char(10) DEFAULT '0' COMMENT '无牌价0，有牌价1，中性品牌价2' after brand_type;
update ecw_order_split_item s, ecw_order_item i set s.fee_type = i.fee_type where s.order_item_id = i.order_item_id;

-- 2023-02-25  柜型新增提单补料
alter table ecw_cabinet add column `lading_bill` decimal(10,2) DEFAULT '0.00' COMMENT '提单补料';

-- TODO 以下1.6合并来，未在生产执行的脚本

-- 2023-02-25
INSERT INTO `system_dict_data` VALUES (null, 3, '打木架/拖', '3', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Wooden frame/Trolley');
INSERT INTO `system_dict_data` VALUES (null, 4, '纸箱打包', '4', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Carton packing');
INSERT INTO `system_dict_data` VALUES (null, 5, '易燃品、液体特殊物品', '5', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Special goods');
INSERT INTO `system_dict_data` VALUES (null, 6, '叉车费', '6', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Forklift');

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, '集运', '6', 'customer_type', 0, 'default', '', NULL, '1', '2022-11-18 00:18:34', '1', '2023-01-15 18:09:43',
        b'0', 'ji yun');

INSERT INTO `system_dict_type`(`name`, `type`, `status`, `remark`, `creator`, `create_time`, `updater`, `update_time`,
                               `deleted`)
VALUES ('市场获客', 'customer_market_type', 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', 'customer_market_type', 0, 'default', '', NULL, '1', '2022-11-18 00:18:34', '1',
        '2023-01-15 18:09:43',
        b'0', 'Assigned');

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, '未分配客户 ', '2', 'customer_market_type', 0, 'default', '', NULL, '1', '2022-11-18 00:18:34', '1',
        '2023-01-15 18:09:43',
        b'0', 'Unabsorbed');

alter table ecw_customer modify column resource_type tinyint default null;


ALTER TABLE `ecw_customer` ROW_FORMAT = DYNAMIC;


alter table `ecw_customer`
    add column `car_name` varchar(20) default null COMMENT '身份证名称';
alter table `ecw_customer`
    add column `car_no` varchar(20) default null COMMENT '身份证号码';


CREATE TABLE `ecw_sync_air_customer`
(
    `id`            bigint   NOT NULL AUTO_INCREMENT,
    `name`          varchar(100)                                                 DEFAULT NULL COMMENT 'name',
    `phone`         varchar(100)                                                 DEFAULT NULL COMMENT 'phone',
    `customer_name` varchar(100)                                                 DEFAULT NULL COMMENT 'customerName',
    `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
    `creator`       varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '创建者',
    `create_time`   datetime NOT NULL                                            DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    `updater`       varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT '' COMMENT '更新者',
    `update_time`   datetime NOT NULL                                            DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='同步空运客户表';



alter table ecw_cabinet add column `lading_bill` decimal(10,2) DEFAULT '0.00' COMMENT '提单补料';
alter table ecw_supplier add column `type` tinyint DEFAULT '0' COMMENT '0-供應商，1-提單托運人';
alter table ecw_supplier add column  `area_type` tinyint DEFAULT '0' COMMENT '0-國内供應商，1-國外供應商';




-- 2023-02-25 yanghao  添加入仓转异的异常
INSERT INTO `system_dict_data` VALUES (null, 3, '打木架/拖', '3', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Wooden frame/Trolley');
INSERT INTO `system_dict_data` VALUES (null, 4, '纸箱打包', '4', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Carton packing');
INSERT INTO `system_dict_data` VALUES (null, 5, '易燃品、液体特殊物品', '5', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Special goods');
INSERT INTO `system_dict_data` VALUES (null, 6, '叉车费', '6', 'manual_exception_type', 0, 'default', '', NULL, '115', '2022-06-28 13:39:58', '115', '2022-06-28 13:39:58', b'0', 'Forklift');


-- 2023-02-28 yanghao 储位添加订单项id和入仓id
alter table `ecw_order_location`
    add column `order_item_id` bigint default null COMMENT '入仓纪录id' after order_id;

alter table `ecw_order_location`
    add column `warehouse_in_id` bigint default null COMMENT '入仓纪录id' after order_item_id;


-- 2023-03-02 yanghao 客户空运非空运切换日志表
CREATE TABLE `ecw_customer_air_log`  (
                                         `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
                                         `customer_id` bigint NOT NULL COMMENT '客户id',
                                         `customer_number` varchar(50) NULL COMMENT '客户编号',
                                         `is_air` tinyint NULL COMMENT '是否空运客户',
                                         `creator` varchar(63) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '创建者',
                                         `create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
                                         `updater` varchar(63) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT '' COMMENT '更新者',
                                         `update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '更新时间',
                                         `deleted` bit(1) NOT NULL DEFAULT b'0' COMMENT '是否删除',
                                         PRIMARY KEY (`id`)
) COMMENT = '客户空运日志';



-- 2023-03-02 zhengYi  订单动态日志补充备注字段
alter table ecw_order_time add column `remarks_zh` varchar(255) DEFAULT NULL COMMENT '中文备注' after `title_en`;
alter table ecw_order_time add column `remarks_en` varchar(255) DEFAULT NULL COMMENT '英文备注' after `remarks_zh`;



-- 2023-03-03 yanghao 添加设置空运权限
INSERT INTO `system_menu`(
    `name`, `permission`, `menu_type`, `sort`, `parent_id`, `status`, `is_show_in_menu_bar`, `name_en`, `keepalive`
)
SELECT '设置空运/非空运', 'ecw:customer:transport', 3, 7, ID, 0, 1, 'Air transport authority', 0 from `system_menu` where name = '全部客户';


alter table `ecw_currency` add column `exchange_to_fc` float(13,6) DEFAULT '0' COMMENT '100人民币兑外币汇率';

alter table `ecw_currency` modify column `huilv` float(13,6) DEFAULT '0' COMMENT '汇率';


-- 2023-03-06 zhengYi  订单保存部门id信息
alter table ecw_order add column `dept_id` bigint DEFAULT 0 COMMENT '部门id' after `salesman_id`;

-- 2023-03-06 zhengYi  报价单保存客户业务经理id与其部门id信息
alter table ecw_offer add column `salesman_id` bigint DEFAULT 0 COMMENT '客户经理ID' after `relation_id`;
alter table ecw_offer add column `dept_id` bigint DEFAULT 0 COMMENT '部门id' after `salesman_id`;

-- 2023-03-06 zhengYi  更新报价单所属客户的客户经理id及其部门id
update ecw_offer o join ecw_customer c on o.relation_id = c.id join `system_user` u on c.customer_service = u.id set o.salesman_id = c.customer_service , o.dept_id = u.dept_id;

-- 2023-03-06 zhengYi  更新订单所属客户的客户经理部门id
update ecw_order o join `system_user` u on o.salesman_id = u.id set o.dept_id = u.dept_id;

-- 2023-03-08 jiuping 添加异常订单导出权限脚本
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:export', 3, 1, 1629, '', '', '', 0, '1', '2023-03-08 15:31:22', '1', '2023-03-08 15:31:22', b'0', b'1', 'Export Exception Orders', b'0', NULL);


-- 2023-03-08 yanghao 添加是否web订单的收货人同步的
alter table `ecw_customer`
    add column `is_web_order_consignee_sync` tinyint default '0' COMMENT '是否web订单的收货人';
-- 2023-03-08 yanghao 添加web订单收货人的第一个客户经理
alter table `ecw_customer`
    add column `consignee_first_customer_service` bigint default null COMMENT 'web订单收货人的第一个客户经理';

-- 2023-03-10 zhengYi 订单项重泡货分摊比例小数点保留5位，以提高计算精度
alter table ecw_order_item modify column `volume_ratio` decimal(15,5) DEFAULT '0.00' COMMENT '货比-泡货的订单比(不计算普货订单项)';
alter table ecw_order_item modify column `weight_ratio` decimal(15,5) DEFAULT '0.00' COMMENT '货比-重货的订单比(不计算普货订单项)';


--2023-03-10 yanghao 添加新老客户标识
alter table `ecw_customer`
    add column `is_new` tinyint default '1' COMMENT '是否新客户';

-- 2023-03-08 jiuping 添加异常订单导出权限脚本
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:export', 3, 1, 1629, '', '', '', 0, '1', '2023-03-08 15:31:22', '1', '2023-03-08 15:31:22', b'0', b'1', 'Export Exception Orders', b'0', NULL);


-- 2023-03-08 yanghao 添加是否web订单的收货人同步的
alter table `ecw_customer`
    add column `is_web_order_consignee_sync` tinyint default '0' COMMENT '是否web订单的收货人';
-- 2023-03-08 yanghao 添加web订单收货人的第一个客户经理
alter table `ecw_customer`
    add column `consignee_first_customer_service` bigint default null COMMENT 'web订单收货人的第一个客户经理';

-- 2023-03-10 zhengYi 订单项重泡货分摊比例小数点保留5位，以提高计算精度
alter table ecw_order_item modify column `volume_ratio` decimal(15,5) DEFAULT '0.00' COMMENT '货比-泡货的订单比(不计算普货订单项)';
alter table ecw_order_item modify column `weight_ratio` decimal(15,5) DEFAULT '0.00' COMMENT '货比-重货的订单比(不计算普货订单项)';


-- 2023-03-21 zhengYi 修改出货短信日志记录的唛头字段长度 (已执行)
alter table ecw_box_order_sms_log modify `marks` varchar(255) DEFAULT NULL COMMENT '唛头';

-- 2023-03-21 zhengYi 修改客户联系人的邮箱字段长度 (已执行)
alter table ecw_customer_contacts modify `email` varchar(255) DEFAULT NULL COMMENT '电子邮箱';


-- 2023-04-15 zhengYi 补充拆单品名的收费数据拆后分摊数据
alter table ecw_order_split_item add column `charge_weight` decimal(15,2) default 0 COMMENT '收费重量';
alter table ecw_order_split_item add column `charge_volume` decimal(15,2) default 0 COMMENT '收费体积';
alter table ecw_order_split_item add column `charge_quantity` decimal(15,2) default 0 COMMENT '收费个数';




-- 2023-04-19 yanghao 添加用户修改的差异json字段
alter table ecw_customer_operate_log add column `note` json default NULL COMMENT '差异json';



-- 2023-05-12 zhengYi 添加订单操作日志表的索引(已全部执行)
ALTER TABLE `ecw_order_operate_log` ADD INDEX order_id (`order_id`);
-- 2023-05-12 zhengYi 添加订单入仓记录的索引(已全部执行)
ALTER TABLE `ecw_order_warehouse_in` ADD INDEX order_id (`order_id`);
ALTER TABLE `ecw_order_warehouse_in` ADD INDEX order_item_id (`order_item_id`);

-- 2023-07-26 zhengYi 添加控货订单放货计量的复核时间(所有环境都已经执行)
alter table ecw_order_cargo_control_pick add column `check_time` datetime default NULL COMMENT '复核时间';