--创建dblink

--测试库1.0
 create database link link_test connect to hzs_cn identified by "123456"
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=8.140.54.234)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

--测试库
 create database link link_cloud connect to hzs_cloud identified by "123456"
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=39.107.153.159)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

--生产库1.0
create database link link_provider connect to hzs_cn identified by "HzSqQyzW20221208"
using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=39.106.11.76)(PORT=1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

insert into sys_user (USER_ID, USER_NAME, NICK_NAME, PHONENUMBER, PASSWORD, STATUS, DEL_FLAG, LOGIN_IP, LOGIN_DATE, CREATION_TIME, MODIFIED_TIME, PK_COUNTRY, PK_CREATOR, PK_MODIFIED, AUTHORITY, SMS, ROLE_IDS, COUNTRY_IDS, VERTEX_IDS, PK_DEPT, HEAD_PATH, TEAM_IDS)
values (100000000, 'admin', '超级管理员', '13507321456', '$2a$10$7JB720yubVSZvUI0rEqK/.VqGOZTH.ulu33dHOiBE8ByOhJIrdAu2', 0, 0, null, null, to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), to_date('08-06-2023 11:13:25', 'dd-mm-yyyy hh24:mi:ss'), 0, 1, 1, 0, 1, '1,21', '1,23,24,25,26,27,28,29,30,31,32', null, 1, 'https://hzs-in.oss-cn-beijing.aliyuncs.com/test-new/systemHead/036934ba-689f-4361-b09b-62282fe52d5c.png', null);

-- 国家
--select max(pk_id) from BD_COUNTRY
insert into BD_COUNTRY (pk_id,CODE,NAME,SHORT_NAME,CODE_THREE,NATIONAL_FLAG1,national_flag2,ENABLE_STATE,NUMBER_PLACES,pk_creator,LANGUAGES,EFFECTIVE_DATE)
select PK_ID,CODE,SHORT_NAME_OTHER NAME,short_name_other SHORT_NAME,CODE_THREE,national_flag NATIONAL_FLAG1,
national_flag national_flag2,0 ENABLE_STATE,2 NUMBER_PLACES,100000000 PK_CREATOR,language,
to_date('2021-01-01,00:00:00','yyyy-MM-dd HH24:mi:ss') EFFECTIVE_DATE
 from bd_country@link_test order by pk_id;

--汇率
--select max(pk_id) from BD_CURRENCY
insert into BD_CURRENCY (pk_id,NAME,ENABLE_STATE,IN_EXCHANGE_RATE,OUT_EXCHANGE_RATE,SYMBOL,IS_CURRENCY,
PK_CREATOR,EFFECTIVE_DATE,pk_country,CURRENCY_CODE)
select br.pk_id,br.name_cn,0 enable_state,br.IN_EXCHANGE_RATE,br.OUT_EXCHANGE_RATE,SYMBOL,1 IS_CURRENCY,
100000000 PK_CREATOR,to_date('2021-01-01,00:00:00','yyyy-MM-dd HH24:mi:ss') EFFECTIVE_DATE,br.pk_country,bc.code from bd_currency@link_test br 
inner join bd_country@link_test bc 
on br.pk_country=bc.pk_id;

update BD_CURRENCY set IS_CURRENCY=0 where CURRENCY_CODE = 'US';

--地区
--select max(pk_id) from bd_area
insert into bd_area(pk_id,name,code,PARENT,ENABLE_STATE,PK_COUNTRY,PK_CREATOR)
select bi.pkid pk_id,bi.mc name,bi.bh code,nvl(bf.pkid,0) PARENT,0 ENABLE_STATE,bi.PK_COUNTRY,100000000 PK_CREATOR from bs_area_info@link_test bi
left join bs_area_info@link_test bf 
on bi.parent_bh=bf.bh

--银行
--select max(pk_id) from bd_bank
insert into bd_bank(pk_id,BANK_NAME,SORT,PK_CREATOR,CREATION_TIME,pk_country)
select bd_bank_SEQ.Nextval pk_id,dict_label BANK_NAME,dict_sort SORT,100000000 PK_CREATOR,
       CREATE_TIME CREATION_TIME,pk_country from sys_dict_data@link_test
where dict_type='bs_bank'
--select pkid pk_id,yhmc BANK_NAME,xh SORT,100000000 PK_CREATOR,
--CREATE_TIME CREATION_TIME,pk_country from bs_bank@link_test;

--账户
--select max(pk_id) from bd_account
insert into bd_account(PK_ID,ACCOUNT_NAME,SORT,FIELD_VALUE,ENABLE_STATE,EFFECTIVE_DATE,BACKGROUND_IMG,pk_country,pk_creator)
select bd_account_seq.nextval PK_ID,a.* from(
select account_name,sort,field_value,0 enable_state,to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss') EFFECTIVE_DATE,
       background_img,106 pk_country,100000000 pk_creator from bd_account@link_cloud
where pk_id in (104,105,106,107,241,242)
order by pk_id)a;
update bd_account set sort=field_value;

--等级
--select max(pk_id) from bd_grade
insert into bd_grade(pk_id,grade_name,grade_value,CENSUS_VALUE,START_VALUE,PK_AWARDS,REGISTER_AUTHORITY,EXPAND_CAPPING,
EXPAND_RATIO,REPURCHASE_CAPPING,REPURCHASE_RATIO,PK_COUNTRY,PK_CREATOR,IMAGE,color,ENABLE_STATE,ICON)
select bd_grade_SEQ.nextval pk_id,a.* from(
select grade_name,grade_value,CENSUS_VALUE,START_VALUE,PK_AWARDS,REGISTER_AUTHORITY,EXPAND_CAPPING,EXPAND_RATIO,
REPURCHASE_CAPPING,REPURCHASE_RATIO,1 PK_COUNTRY,100000000 PK_CREATOR,IMAGE,COLOR,0 ENABLE_STATE,ICON 
from bd_grade@link_cloud 
where pk_country =1 and pk_id not in(7,8))a
--奖衔
--select max(pk_id) from bd_awards
insert into bd_awards(pk_id,AWARDS_NAME,awards_value,PK_COUNTRY,IMAGE,COLOR,ENABLE_STATE,pk_creator)
select BD_AWARDS_SEQ.nextval pk_id,a.* from(
select  awards_name,awards_value,106 PK_COUNTRY,(case when awards_value=0 then null else image end) IMAGE,
COLOR,0 ENABLE_STATE,100000000 pk_creator from bd_awards@link_cloud
where pk_country =1
order by pk_country,awards_value ) a
--产品
--select max(pk_id) from bd_product
insert into bd_product (PK_ID,PRODUCT_CODE,PRODUCT_NAME,PK_CLASSIFY,UNIT,WEIGHT,LENGTH,WIDTH,HEIGHT,POSTAGE_WEIGHT,
RETAIL_PRICE,ENABLE_STATE,COVER,SHELF_LIFE,IS_FRESH,PK_CREATOR,SHIPPING_CHANNEL,PK_COUNTRY)
select pkid PK_ID,cpbh PRODUCT_CODE,cpmc PRODUCT_NAME,1 PK_CLASSIFY,1 UNIT,1 WEIGHT,1 LENGTH,
1 WIDTH,1 HEIGHT,1 POSTAGE_WEIGHT,cpjg RETAIL_PRICE,0 ENABLE_STATE,nvl(cpfm,'0') COVER,0 SHELF_LIFE,
1 IS_FRESH,100000000 PK_CREATOR,nvl(shipping_Channel,1) SHIPPING_CHANNEL,pk_country from bs_product@link_test 
where cpmc not like '%测试%' and del_flag='0';

--产品扩展表
--select max(pk_id) from BD_PRODUCT_EXTEND
insert into BD_PRODUCT_EXTEND (pk_id,PK_PRODUCT,pk_country,SORT,PK_CREATOR)
select pkid pk_id,pkid PK_PRODUCT,pk_country,(case when xh >=999999 then 999999 else xh end) SORT,100000000 PK_CREATOR from bs_product@link_test 
where cpmc not like '%测试%' and del_flag='0';

--商品分类
--select max(pk_id) from BD_AREA_CLASSIFY
insert into BD_AREA_CLASSIFY(pk_id,CLASSIFY_NAME,PK_TRANSACTION_KEY,SORT,ENABLE_STATE,PK_PARENT,PK_COUNTRY,PK_CREATOR)
select id pk_id,name CLASSIFY_NAME,-1 PK_TRANSACTION_KEY,SORT,0 ENABLE_STATE,0 PK_PARENT,
PK_COUNTRY,100000000 PK_CREATOR  from BS_GOODS_CATEGORY@link_test where id in(select GOODS_CATEGORY_ID from bs_goods@link_test);

--商品
--select max(pk_id) from bd_wares
insert into bd_wares(PK_ID,WARES_CODE,WARES_NAME,WARES_PRICE,WARES_ACHIEVE,PK_AREA_CLASSIFY,COVER1,DETAILS,sort,
special_Area,IS_SINGLE,IS_FREE_MAIL,PK_COUNTRY,PK_CREATOR)
select pkid PK_ID,spbh WARES_CODE,spmc WARES_NAME,spjg WARES_PRICE,sppv WARES_ACHIEVE,1 PK_AREA_CLASSIFY,
nvl(nvl(SPFM, SPTW),'1') COVER1,nvl(nvl(SPTW, SPFM),'1') DETAILS,(case when xh >=999999 then 999999 else xh end) sort,sszq special_Area,(case when splx=1 then 0 else 1 end) IS_SINGLE,
IS_MAIL_FREE IS_FREE_MAIL,PK_COUNTRY,100000000 PK_CREATOR 
 from bs_goods@link_test;

--商品扩展表
--select max(pk_id) from bd_wares_extend
insert into bd_wares_extend(PK_ID,PK_WARES,IS_PUT_ON,PRE_SALE_STATUS,PUT_ON_TIME,PUT_OFF_TIME,OPERATE_SCOPE,SALES,
IS_SALE,PK_COUNTRY,PK_CREATOR)
select pkid PK_ID,pkid PK_WARES,(case when spzt=1 then 0 else 1 end) IS_PUT_ON,1 PRE_SALE_STATUS,
auto_Start_Time PUT_ON_TIME,auto_End_Time PUT_OFF_TIME,0 OPERATE_SCOPE,0 SALES,1 IS_SALE,
PK_COUNTRY,100000000 PK_CREATOR 
 from bs_goods@link_test;

--商品明细表
--select max(pk_id) from bd_wares_detail
insert into bd_wares_detail(pk_id,PK_WARES,PK_PRODUCT,PRICE,achieve,QUANTITY,IS_GIFT,PK_COUNTRY,PK_CREATOR)
select bp.pkid pk_id,bg.pkid PK_WARES,bt.pkid PK_PRODUCT,bt.cpjg PRICE,bt.cppv achieve,
bp.num QUANTITY,bg.isactgift IS_GIFT,bg.PK_COUNTRY,100000000 PK_CREATOR from BS_GOODS@link_test bg 
inner join BS_GOODS_PRODUCT@link_test bp 
on bg.pkid=bp.goods_id 
inner join bs_product@link_test bt 
on bp.product_id=bt.pkid


--奖金项设置
insert into bd_bonus_items(pk_id,bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,PK_COUNTRY,PK_CREATOR)
select rownum pk_id,a.* from(
                                select bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,PK_COUNTRY,100000000 PK_CREATOR
                                from bd_bonus_items@link_cloud where pk_country=1 and del_flag=0
                                order by pk_id)a;

insert into bd_bonus_items(pk_id,bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,PK_COUNTRY,PK_CREATOR)
select bd_bonus_items_seq.nextval pk_id,a.* from(
                                                    select bonus_name,bonus_value,enable_state,SETTLE_TYPE,PUBLISH_TIME,106 PK_COUNTRY,100000000 PK_CREATOR
                                                    from bd_bonus_items where pk_country=1 and del_flag=0
                                                                          and pk_id in(2,3,4,5,6,7,8,9,10,11,16,17,23)
                                                    order by pk_id)a;