truncate table sa_order;
truncate table sa_order_items;

-- 直销订单迁移
insert into sa_order(pk_id,
                     order_code,
                     order_type,
                     pk_rate,
                     order_amount,
                     order_achieve,
                     postage,
                     rec_name,
                     rec_phone,
                     rec_province,
                     rec_city,
                     rec_county,
                     rec_address,
                     pay_time,
                     remark,
                     delivery_way,
                     order_status,
                     del_flag,
                     creation_time,
                     modified_time,
                     pk_country,
                     pk_creator,
                     pk_modified,
                     pk_member,
                     pk_reference,
                     pk_placement,
                     tran_type,
                     pk_storehouse)
select bo.pkid                         PK_ID,
       bo.ddbh                         ORDER_CODE,
       bo.ddlx                         ORDER_TYPE,
       bc.pk_id                        PK_RATE,
       bo.ddje                         ORDER_AMOUNT,
       trunc(nvl(bo.ddpv, 0) / 6.4, 6) ORDER_ACHIEVE,
       nvl(bo.yf, 0)                   POSTAGE,
       bo.shrm                         REC_NAME,
       bo.shdh                         REC_PHONE,
       basheng.pk_id                   REC_PROVINCE,
       bashi.pk_id                     REC_CITY,
       baxian.pk_id                    REC_COUNTY,
       bo.DZ                           REC_ADDRESS,
       bo.zfsj                         PAY_TIME,
       bo.bz                           REMARK,
       nvl(bo.fhfs, 3)                 DELIVERY_WAY,
       bo.zfzt                         ORDER_STATUS,
       bo.del_flag                     DEL_FLAG,
       bo.create_time                  CREATION_TIME,
       bo.update_time                  MODIFIED_TIME,
       bo.pk_country                   PK_COUNTRY,
       sucr.user_id                    PK_CREATOR,
       suup.user_id                    PK_MODIFIED,
       suyh.user_id                    PK_MEMBER,
       case
           when sutj.user_id is not null then
               sutj.user_id
           else
               (case
                    when bo.pk_country = 1 then
                        102
                    when bo.pk_country = 18 then
                        5
                    when bo.pk_country = 105 then
                        193960
                    when bo.pk_country = 106 then
                        186745
                   end)
           end                         PK_REFERENCE,
       suaz.user_id                    PK_PLACEMENT,
       1                               TRAN_TYPE,
       bo.WAREHOUSE                    PK_STOREHOUSE
from hzs_cn.bs_order bo
         left join hzs_cn.sys_user sucr
                   on sucr.user_name = bo.create_by
         left join hzs_cn.sys_user suup
                   on suup.user_name = bo.update_by
         left join hzs_cn.sys_user suyh
                   on suyh.user_name = bo.yhbh
         left join hzs_cn.sys_user sutj
                   on sutj.user_name = bo.TJRBH
         left join hzs_cn.sys_user suaz
                   on suaz.user_name = bo.AZRBH
         left join bd_area basheng
                   on basheng.pk_country = bo.pk_country
                       and basheng.code = bo.SHENG
         left join bd_area bashi
                   on bashi.pk_country = bo.pk_country
                       and bashi.code = bo.SHI
         left join bd_area baxian
                   on baxian.pk_country = bo.pk_country
                       and baxian.code = bo.XIAN
         left join bd_currency bc
                   on bc.del_flag = 0
                       and bc.pk_country = bo.pk_country
where suyh.user_id is not null
  and bo.create_time >= to_date('2023-01-01 00:00:00', 'yyyy--mm-dd hh24:mi:ss');


-- 直销订单明细迁移
insert into sa_order_items
(pk_id,
 pk_order,
 pk_product,
 price,
 quantity,
 pk_rate,
 achievement,
 ass_achievement,
 pk_grade,
 pk_awards,
 order_status,
 del_flag,
 creation_time,
 modified_time,
 pk_country,
 is_gift,
 pk_creator,
 pk_modified,
 specs_name,
 wares_quantity,
 logistics_code,
 logistics_company)
select boi.pkid                                                     PK_ID,
       bo.pkid                                                      PK_ORDER,
       boi.cpid                                                     PK_PRODUCT,
       nvl(boi.jg, 0)                                               PRICE,
       boi.cpsl                                                     QUANTITY,
       bc.pk_id                                                     PK_RATE,
       trunc(nvl(boi.pv, 0) / 6.4, 6)                               ACHIEVEMENT,
       0                                                            ASS_ACHIEVEMENT,
       case
           when bo.DDLX != 3 then 0
           when boi.zsjx < '0' then
               (select bg.pk_id
                from bd_grade bg
                where bg.del_flag = 0
                  and bg.pk_country = boi.pk_country
                  and bg.grade_value = (case
                                            when boi.zsjx = -6 then
                                                20
                                            when boi.zsjx = -5 then
                                                30
                                            when boi.zsjx = -4 then
                                                40
                                            when boi.zsjx = -3 then
                                                50
                                            when boi.zsjx = -2 then
                                                60
                                            when boi.zsjx = -1 then
                                                70
                    end))
           when boi.zsjx is null or boi.zsjx >= '0' then
               (select cm.pk_register_grade
                from cu_member cm
                where cm.pk_id = suyh.user_id)
           end                                                      PK_GRADE,
       case
           when bo.DDLX != 3 then 0
           when boi.zsjx is null or boi.zsjx < '0' then
               (select ba.pk_id
                from bd_awards ba
                where ba.del_flag = 0
                  and ba.pk_country = boi.pk_country
                  and ba.awards_value = 0)
           when boi.zsjx >= '0' then
               (select ba.pk_id
                from bd_awards ba
                where ba.del_flag = 0
                  and ba.pk_country = boi.pk_country
                  and ba.awards_value = boi.zsjx * 5)
           end                                                      PK_AWARDS,
       case
           when boi.DDZT is null then
               1
           when boi.DDZT = 0 then
               0
           when boi.DDZT = 1 then
               1
           when boi.DDZT = 2 or boi.DDZT = 3 then
               3
           end                                                      ORDER_STATUS,
       nvl(boi.del_flag, 0)                                         DEL_FLAG,
       nvl(boi.CREATE_TIME, nvl(boi.UPDATE_TIME, suyh.create_time)) CREATION_TIME,
       boi.UPDATE_TIME                                              MODIFIED_TIME,
       boi.PK_COUNTRY                                               PK_COUNTRY,
       boi.ISACTGIFT                                                IS_GIFT,
       sucr.user_id                                                 PK_CREATOR,
       suup.user_id                                                 PK_MODIFIED,
       bp.cpgg                                                      SPECS_NAME,
       nvl(boi.GOOD_NUM, boi.cpsl)                                  WARES_QUANTITY,
       trim(replace(boi.KDDH, '''', ''))                            LOGISTICS_CODE,
       boi.KDGS                                                     LOGISTICS_COMPANY
from hzs_cn.bs_order_items boi
         inner join hzs_cn.bs_order bo
                    on bo.ddbh = boi.ddbh
         left join hzs_cn.sys_user suyh
                   on suyh.user_name = bo.yhbh
         left join bd_currency bc
                   on bc.del_flag = 0
                       and bc.pk_country = bo.pk_country
         left join hzs_cn.sys_user sucr
                   on sucr.user_name = bo.create_by
         left join hzs_cn.sys_user suup
                   on suup.user_name = bo.update_by
         left join hzs_cn.bs_product bp
                   on bp.pkid = boi.cpid
where boi.ddbh is not null
  and suyh.user_id is not null
  and boi.create_time >= to_date('2023-01-01 00:00:00', 'yyyy--mm-dd hh24:mi:ss');


-- 105国家仓库补全
update SA_ORDER t
set t.pk_storehouse = (case
                           when t.pk_storehouse = 1 then
                               62
                           else
                               61
    end)
where t.pk_country = 105