-- 导入品牌

truncate table jiedao.ecw_product_brank;

INSERT INTO jiedao.ecw_product_brank (title_zh, title_en, filing, aorder, creator, create_time, updater, update_time)
SELECT title_zh,
       title_en,
       IF
           (shenhe = 'y', 1, 0),
       aorder,
       1,
       now(),
       1,
       now()
FROM jiedao_old.ecw_product_brank;



-- 123456  $2a$10$ZHEajc5Fhxrod7VLKjwSGufeLMPny/0Uc41zfY66CZefz9UGntemy

-- 导入注册用户
INSERT INTO jiedao.member_user (id, nickname, english_name, mobile, email, area_code, status, register_ip,password)


SELECT id,
       IFNULL(name_zh, phone),
       IFNULL(name_en, phone),
       phone,
       email,
       REPLACE(area_code, '+', ''),
       0,
       IFNULL(regip, '192.168.1.1'),
       '$2a$10$ZHEajc5Fhxrod7VLKjwSGufeLMPny/0Uc41zfY66CZefz9UGntemy'
FROM jiedao_old.ecw_admin
WHERE type = 'member';

update jiedao.member_user
set password = '$2a$10$ZHEajc5Fhxrod7VLKjwSGufeLMPny/0Uc41zfY66CZefz9UGntemy';

-- 导入客户信息

INSERT INTO jiedao.ecw_customer (id, number, `name`, company, address, `level`, country, `status`, customer_service,
                                 department, invoice_title, license_number, bank, bank_number, project, billing_address,
                                 billing_tell, `type`, `source`, member_id, pickup_point,founder,creator,updater)


SELECT id,
       number,
       `name`,
       company,
       address,
       `level`,
       country,
       `status`,
       customer_service,
       department,
       invoice_title,
       license_number,
       bank,
       bank_number,
       project,
       billing_address,
       billing_tell,
       `type`,
       `source`,
       member_id,
       pickup_point,
       1,1,1
FROM jiedao_old.ecw_customer;

-- 导入客户联系人
INSERT INTO `jiedao`.`ecw_customer_contacts` (`customer_id`, `department`, `position`, `name`, `social`,
                                              `social_number`, `email`, `is_default`, `userid`, `username`, `area_code`,
                                              `phone_new`, `is_auto`)
SELECT `customer_id`,
       `department`,
       `position`,
       `name`,
       null,
       `social_number`,
       `email`,
       `default`,
       `userid`,
       `username`,
       REPLACE(`area_code`, '+', ''),
       `phone_new`,
       `is_auto`
FROM jiedao_old.ecw_customer_contacts;
-- 导入部门
INSERT INTO `jiedao`.`system_dept`(id, `name`, parent_id, sort, leader_user_id)

SELECT id, title_zh, pid, aorder, admin_id
from jiedao_old.ecw_department;


-- 导入后台用户信息

INSERT INTO jiedao.SYSTEM_USER (id, username, nickname, mobile, email, dept_id)
SELECT id,
       username,
       name_zh,
       phone,
       email,
       pid
FROM jiedao_old.ecw_admin
WHERE type = 'admin'
  AND username != 'admin'
	AND STATUS = 0 and username != '';

update jiedao.SYSTEM_USER
set `password`='$2a$10$ZHEajc5Fhxrod7VLKjwSGufeLMPny/0Uc41zfY66CZefz9UGntemy'
where username!='admin';
-- 授权 普通用户
insert into jiedao.sys_user_role(user_id, role_id)
select id, 2
from jiedao.sys_user
where username!='admin';


-- 导入银行账户信息
INSERT
INTO `jiedao`.`ecw_bank_account` (`ba_account_name`, `ba_bank_name`, `ba_account_num`, `ba_swift_code`,
                                  `ba_bank_add`, `ba_type`, `source`)
SELECT ba_account_name,
       ba_bank_name,
       ba_account_num,
       ba_swift_code,
       ba_bank_add,
       ba_type,
       `source`
FROM `jiedao_old`.`ecw_bank_account`;




INSERT INTO jiedao.ecw_customer (id, number, `name`, company, address, `level`, country, `status`, customer_service,
                                 department, invoice_title, license_number, bank, bank_number, project, billing_address,
                                 billing_tell, `type`, `source`, member_id, pickup_point)

-----------------------------------------------------20221206-------------------------------------------------------------

-- 插入客户联系人
INSERT INTO `ecw_customer_contacts` ( `customer_id`, `department`, `position`, `name`, `social`, `social_number`, `email`, `is_default`, `userid`, `username`, `area_code`, `phone_new`, `is_auto` )

SELECT
    `customer_id`,
    `department`,
    `position`,
    `name`,
    NULL,
    `social_number`,
    `email`,
    `default`,
    `userid`,
    `username`,
    REPLACE ( `area_code`, '+', '' ),
    `phone_new`,
    `is_auto`
FROM
    ecw_customer_contacts_20221206
WHERE
        customer_id NOT IN (
        SELECT
            t.id
        FROM
            ecw_customer t
    )




-- 插入客户
INSERT INTO jiedao.ecw_customer (id, number, `name`, company, address, `level`, country, `status`, customer_service,
                                 department, invoice_title, license_number, bank, bank_number, project, billing_address,
                                 billing_tell, `type`, `source`, member_id, pickup_point,transport_type,founder,creator,updater)


SELECT id,
       number,
       `name`,
       company,
       address,
       `level`,
       country,
       `status`,
       customer_service,
       department,
       invoice_title,
       license_number,
       bank,
       bank_number,
       project,
       billing_address,
       billing_tell,
       `type`,
       `source`,
       member_id,
       pickup_point,
       (
           case is_air
               when  '0' then '1,3'
               when  '1' then '1,3'
               when  '2' then '1,3'
               when  '3' then '4'
               end ),1,1,1

FROM  ecw_customer_20221206 where id not in (
    select t.id from ecw_customer t
)



-- 增量时更新
udpate customer set is_customer_service_confirmed = 1, customer_service_assigned_time = now() where customer_service is not null and create_time='2022-10-15 22:05:02';




CREATE DEFINER=`root`@`%` PROCEDURE `update_customer_opensea`()
begin
	DECLARE s INT DEFAULT 0;
	DECLARE cid BIGINT DEFAULT 0;
	DECLARE cstatus tinyint DEFAULT 0;
	-- 定义游标，并将sql结果集赋值到游标中，report为游标名
	DECLARE report CURSOR FOR select id,`status` from jiedao.ecw_customer t where t.id not in (select s.customer_id from  jiedao_old.ecw_customer_sea s) and t.is_in_open_sea = 1;  -- 非公海客户
-- 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
	-- 打开游标
OPEN report;
-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO cid,cstatus;
-- 当s不等于1时，也就是未遍历完时，会一直循环
WHILE
s <> 1 DO
    -- 计算出客户在老系统的最新下单时间
       set @mtime = (select ifnull(FROM_UNIXTIME(max(t.rucangtime)),'2022-01-01 00:00:00.000000') from jiedao_old.ecw_order t where t.customer_id = cid);

       set @new_count = (select count(1) from jiedao.ecw_order t where t.deleted=0 and t.customer_id = cid);

        if @new_count > 0 then
               -- 以新系统最新下单时间为准
               set @new_time=(select max(t.create_time) from jiedao.ecw_order t where t.deleted=0 and t.customer_id = cid);

                if @new_time > @mtime then
                        -- 执行业务逻辑
                        if cstatus = 1 then

                            update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@new_time, interval 1 month) where id=cid;

                        elseif cstatus = 2 then

                            update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@new_time, interval 3 month) where id=cid;

                        elseif cstatus = 3 then

                            update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@new_time, interval 6 month) where id=cid;

                        end if;
                else
                        -- 执行业务逻辑
                        if cstatus = 1 then

                            update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@mtime, interval 1 month) where id=cid;

                        elseif cstatus = 2 then

                            update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@mtime, interval 3 month) where id=cid;

                        elseif cstatus = 3 then

                            update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@mtime, interval 6 month) where id=cid;

                        end if;
                end if;

        else
               -- 以旧系统最新下单时间为准
               -- 执行业务逻辑
                if cstatus = 1 then

                    update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@mtime, interval 1 month) where id=cid;

                elseif cstatus = 2 then

                    update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@mtime, interval 3 month) where id=cid;

                elseif cstatus = 3 then

                    update jiedao.ecw_customer set estimate_enter_open_sea_time=date_add(@mtime, interval 6 month) where id=cid;

                end if;

        end if;

       -- 获取客户的预计入公海时间
       set @new_estimate_enter_open_sea_time = (select t.estimate_enter_open_sea_time from jiedao.ecw_customer t where t.id = cid);

       -- 预计入公海时间大于当前时间 更新为非公海客户
    if @new_estimate_enter_open_sea_time > now() then
        -- 取得客户的客户经理
        set @customer_server_id = (select t.customer_service from jiedao_old.ecw_customer t where t.id=cid);
        UPDATE jiedao.ecw_customer a SET a.is_in_open_sea = 0,a.is_customer_service_confirmed = 1,customer_service = @customer_server_id,a.customer_service_assigned_time = now() WHERE a.id = cid;
    else
        -- 预计入公海时间小于或等于当前时间 更新为公海客户
        UPDATE jiedao.ecw_customer a
        SET
            a.is_in_open_sea = 1,
            a.customer_service_assigned_time = NULL,-- 跟进客服分配时间
            a.is_customer_service_confirmed = 0, -- 确认接收置为false
            a.estimate_enter_open_sea_time = NULL,-- 预计进入公海池时间置为空
            a.catch_time = NULL -- 捞取时间置为空
        WHERE a.id = cid;
    end if;





      -- 当s等于1时代表遍历已完成，退出循环
FETCH report INTO cid,cstatus;

END WHILE;-- 关闭游标
CLOSE report;
end




CREATE DEFINER=`root`@`%` PROCEDURE `update_customer_service`()
begin
	DECLARE s INT DEFAULT 0;
	DECLARE cid BIGINT DEFAULT 0;
	-- 定义游标，并将sql结果集赋值到游标中，report为游标名
	DECLARE report CURSOR FOR select id from jiedao.ecw_customer t where t.customer_service is null;  -- 非公海客户
-- 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
	-- 打开游标
OPEN report;
-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO cid;
-- 当s不等于1时，也就是未遍历完时，会一直循环
WHILE
s <> 1 DO

    set @new_count = (select count(1) from jiedao_old.ecw_customer_sea t where t.customer_id = cid);

    if @new_count > 0 then

        set @old_customer_service = (select old_customer_service from jiedao_old.ecw_customer_sea t where t.customer_id = cid);

        update jiedao.ecw_customer a set a.customer_service = @old_customer_service where a.id = cid;

    end if;


    -- 当s等于1时代表遍历已完成，退出循环
FETCH report INTO cid;

END WHILE;-- 关闭游标
CLOSE report;
end

-------------------------------------------------

select b.old_customer_service from jiedao_old.ecw_customer_sea b where b.customer_id=33079


select * from jiedao.ecw_customer t where t.number= 'N15548' ;



select * from system_user t where t.id=2758  -- 2662




select * from jiedao.system_user t where t.username='009'


select * from jiedao.ecw_customer t where t.customer_service=2758






CREATE DEFINER=`root`@`%` PROCEDURE `update_customer_rescource`()
begin
 DECLARE s INT DEFAULT 0;
 DECLARE cid BIGINT DEFAULT 0;
DECLARE cresource BIGINT DEFAULT 0;
 -- 定义游标，并将sql结果集赋值到游标中，report为游标名
 DECLARE report CURSOR FOR select id,resource from jiedao_old.ecw_customer t ;
-- 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
 -- 打开游标
OPEN report;
-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO cid,cresource;
-- 当s不等于1时，也就是未遍历完时，会一直循环
WHILE
s <> 1 DO

    -- 执行业务逻辑
   if cresource = 0 then

update jiedao.ecw_customer set resource_type=1 where id=cid;

elseif cresource = 1 then

update jiedao.ecw_customer set resource_type=2 where id=cid;

end if;

      -- 当s等于1时代表遍历已完成，退出循环
FETCH report INTO cid,cresource;

END WHILE;-- 关闭游标
CLOSE report;
end





-- 同步老系统的客户经理
CREATE DEFINER=`root`@`%` PROCEDURE `sync_old_customer_service`()
begin
 DECLARE s INT DEFAULT 0;
 DECLARE cid BIGINT DEFAULT 0;
 -- 定义游标，并将sql结果集赋值到游标中，report为游标名
 DECLARE report CURSOR FOR select t.id from jiedao.ecw_customer t where t.customer_service = 0 or t.customer_service is null;
-- 声明当游标遍历完后将标志变量置为某个值
DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1;
 -- 打开游标
OPEN report;
-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
FETCH report INTO cid;
-- 当s不等于1时，也就是未遍历完时，会一直循环
WHILE
s <> 1 DO
    -- 在老系统有这个用户才同步
    set @old_count = (select count(1) from jiedao_old.ecw_customer t where t.id = cid);
    if @old_count > 0 then

            set @old_customer_service = (select customer_service from jiedao_old.ecw_customer t where t.id = cid);

            if @old_customer_service <> 0 then
update jiedao.ecw_customer a set a.customer_service = @old_customer_service where a.id = cid;
else
                 set @old_customer_sea_count = (select count(1) from jiedao_old.ecw_customer_sea t where t.customer_id = cid);

                if @old_customer_sea_count > 0 then
                   set @old_customer_sea_service = (select old_customer_service from jiedao_old.ecw_customer_sea t where t.customer_id = cid);

update jiedao.ecw_customer a set a.customer_service = @old_customer_sea_service where a.id = cid;
end if;
end if;
end if;


      -- 当s等于1时代表遍历已完成，退出循环
FETCH report INTO cid;

END WHILE;-- 关闭游标
CLOSE report;
end



update ecw_customer t set t.enter_open_sea_time=FROM_UNIXTIME(1672364089) where t.number='Z1580';
update ecw_customer t set t.enter_open_sea_time=FROM_UNIXTIME(1672364089) where t.number='N2003';
update ecw_customer t set t.enter_open_sea_time=FROM_UNIXTIME(1672364089) where t.number='Z5035';
update ecw_customer t set t.enter_open_sea_time=FROM_UNIXTIME(1672364089) where t.number='D0012';
update ecw_customer t set t.enter_open_sea_time=FROM_UNIXTIME(1666774802) where t.number='N10664';
update ecw_customer t set t.enter_open_sea_time=FROM_UNIXTIME(1666609201) where t.number='N11168';









SELECT
    o.marks AS '唛头',
        m.prod_title_zh AS '货名',
        m.warehouse_in_info ->> '$.unit' AS '箱数',
    m.warehouse_in_info ->> '$.volumne' AS '立方数',
    m.warehouse_in_info ->> '$.weight' AS '重量',
    o.rucang_time AS '入仓日期',
    d.departure ->> '$.titleZh' AS '始发仓',
    o.order_no AS '系统单号',
    r.phone AS '发货人电话',
    m.worth AS '货值',
    c.phone AS '收货人电话',
    b.objective ->> '$.titleZh' AS '目的仓',
    IF
     ( o.is_cargo_control = 1, '是', '否' ) AS '是否控货',
     (
    CASE
    e.order_exception_type
    WHEN 'order_no_quote_exception' THEN
    '未报价'
    WHEN 'order_pay_exception' THEN
    '需要预付异常'
    WHEN 'order_superfluous_box_exception' THEN
    '多箱'
    WHEN 'order_lack_box_exception' THEN
    '少箱'
    WHEN 'order_miss_exception' THEN
    '货物丢失'
    WHEN 'order_superfluous_goods_exception' THEN
    '货物增多'
    WHEN 'order_damage_exception' THEN
    '货物破损'
    WHEN 'order_in_water_exception' THEN
    '货物浸水'
    WHEN 'order_heavy_cargo_exception' THEN
    '重货异常'
    WHEN 'order_bulky_cargo_exception' THEN
    '泡货异常'
    WHEN 'order_doc_exception' THEN
    '单证异常'
    WHEN 'order_consignor_exception' THEN
    '发货人异常'
    WHEN 'order_cod_exception' THEN
    '代收货款'
    WHEN 'order_other_exception' THEN
    '其它异常'
    WHEN 'order_pick_up_exception' THEN
    '提货异常'
    END
    ) AS '异常类型'
FROM
    ecw_order_exception e
    LEFT JOIN ecw_order o ON e.order_id = o.order_id
    LEFT JOIN ecw_order_item m ON e.order_item_id = m.order_item_id
    LEFT JOIN ecw_order_consignee c ON o.order_id = c.order_id
    LEFT JOIN ecw_order_consignor r ON o.order_id = r.order_id
    LEFT JOIN ecw_order_departure d ON o.order_id = d.order_id
    left join ecw_order_objective b on o.order_id = b.order_id
WHERE
    e.order_exception_status !=2 and o.deleted=0