-- -- 体系 -- --select max(pk_id) from bd_vertex -- insert into bd_vertex (pk_id, pk_country, pk_creator, pk_member, VERTEX_NAME) -- select rownum pk_id, a.* -- from (select pk_country, 100000000 pk_creator, user_id pk_member, nick_name VERTEX_NAME -- from sys_user@link_test -- where SPARENT = 'GJ30720200' -- order by SDEPT) a -- -- --团队 -- --select max(pk_id) from cu_member_team; -- insert -- into cu_member_team(pk_id, pk_member, TEAM_NAME, PK_COUNTRY, pk_creator, PK_BD_VERTEX) -- select bs.pkid pk_id, -- su.user_id pk_member, -- su.nick_name TEAM_NAME, -- su.PK_COUNTRY, -- 100000000 pk_creator, -- bv.pk_id PK_BD_VERTEX -- from bs_sslc@link_test bs -- inner join sys_user@link_test su -- on bs.yhbh = su.user_name -- left join bd_vertex bv -- on bv.pk_member = su.user_id --会员 -- select max(pk_id) from cu_member insert into cu_member(pk_id, member_code, member_name, CUSTOMER_TYPE, phone, id_card, sex, pk_parent, pk_place_parent, place_dept, pk_center_code, pk_country, pk_settle_country, source, pk_register_grade, pk_settle_grade, pk_awards, purchase_status, share_status, pay_time, login_pwd, pay_pwd, login_password, pay_password, edit_number, pk_creator, creation_time, register_authority, account_status, pay_status, category, email, order_source, consume_money, consume_achieve, change_number, card_type, direct_push_number, is_dealer, DEL_FLAG) select a.pk_id, a.member_code, a.member_name, a.CUSTOMER_TYPE, a.phone, a.id_card, a.sex, a.pk_parent, a.pk_place_parent, a.place_dept, a.pk_center_code, a.pk_country, a.pk_settle_country, a.source, zc.pk_id pk_register_grade, js.pk_id pk_settle_grade, jx.pk_id pk_awards, a.purchase_status, a.share_status, a.pay_time, a.login_pwd, a.pay_pwd, a.login_password, a.pay_password, a.edit_number, a.pk_creator, a.creation_time, a.register_authority, a.account_status, a.pay_status, a.category, a.email, a.order_source, a.consume_money, a.consume_achieve, a.change_number, a.card_type, a.direct_push_number, a.is_dealer, a.DEL_FLAG from (select su.user_id pk_id, su.user_name member_code, su.nick_name member_name, su.CUSTOMER_TYPE, nvl(su.PHONENUMBER, '~') phone, su.ZJHM id_card, 0 sex, nvl(pa.user_id, 0) pk_parent, pl.user_id pk_place_parent, su.SDEPT place_dept, cn.user_id pk_center_code, su.pk_country, su.pk_settle_country, nvl(su.SJLY, 1) source, (case su.ZCDJ when '-1' then 20 when '0' then 30 when '1' then 40 when '2' then 50 when '3' then 60 else 70 end) pk_register_grade, (case su.jsdj when '-1' then 20 when '0' then 30 when '1' then 40 when '2' then 50 when '3' then 60 else 70 end) pk_settle_grade, nvl(su.ZSJX, 0) * 5 pk_awards, (case when su.FGMK = 1 then 0 else 1 end) purchase_status, (case when su.SCMK = 1 then 0 else 1 end) share_status, su.zfsj pay_time, nvl(su.PASSWORD, '$2a$10$RUCQ90fqynd0AKC1fCSateKAzsNWYsiNFBW0c0Y.RYKljyV1V6XH.') login_pwd, nvl(su.PASWORD2, '$2a$10$MN4.agG2EArubMHQMl4EU.Huuk85VcPHNc3.jZMXFzkIRLNJxwMTy') pay_pwd, nvl(su.PW1, '111111') login_password, nvl(su.PW2, '222222') pay_password, nvl(xg.num, 0) edit_number, nvl(cr.user_id, 100000000) pk_creator, nvl(su.CREATE_TIME, su.zfsj) creation_time, (case sr.role_id when 104 then 1 when 105 then 2 else 0 end) register_authority, su.STATUS account_status, nvl(su.ZFZT, 0) pay_status, (case when su.CUSTOMER_TYPE = 2 then 1 else 0 end) category, su.EMAIL email, (case when su.CREATE_BY = 'admin' then 2 else 1 end) order_source, nvl(su.ZCJG, 0) consume_money, nvl(su.ZCYJ, 0) consume_achieve, nvl(bg.num, 0) change_number, 1 card_type, nvl(zt.num, 0) direct_push_number, (case when su.USER_TYPE = 1 then 1 else 0 end) is_dealer, su.DEL_FLAG from sys_user@link_test su left join sys_user@link_test pa on su.parent = pa.user_name left join sys_user@link_test pl on su.SPARENT = pl.user_name left join sys_user@link_test cn on su.BDZX = cn.user_name left join sys_user@link_test cr on cr.user_name = su.CREATE_BY left join (select bczr, count(pkid) num from bs_operlog@link_test where czlx in ('105') and zt = 1 group by bczr) bg on bg.bczr = su.user_name left join (select bczr, count(pkid) num from bs_operlog@link_test where czlx in ('106') and zt = 1 group by bczr) xg on xg.bczr = su.user_name left join (select bczr, count(pkid) num from bs_operlog@link_test where czlx in ('109') and zt = 1 group by bczr) zt on zt.bczr = su.user_name left join (select user_id, max(role_id) role_id from sys_user_role@link_test where role_id in (102, 104, 105) group by user_id) sr on su.user_id = sr.user_id where su.USER_TYPE !=0) a left join bd_grade zc on a.pk_register_grade = zc.grade_value and a.pk_settle_country = zc.pk_country left join bd_grade js on a.pk_settle_grade = js.grade_value and a.pk_settle_country = js.pk_country left join bd_awards jx on a.pk_awards = jx.awards_value and a.pk_settle_country = jx.pk_country; update cu_member set consume_achieve=consume_achieve / 6.4; update cu_member set ACCOUNT_STATUS = 4 where member_code in( 'GJ30720200','BL30720201','ID30720201','KZ30720201','MG30720201','MY30720201','MN30720201','CA30720201','US30720201','PH30720201','NG30720201','CD30720201','CI30720201','BJ30720201','CM30720201','KE30720201','GH30720201') 修改sequence --会员注册信息 --select max(pk_id) from cu_member_register insert into cu_member_register(pk_id, member_code, member_name, phone, CUSTOMER_TYPE, SOURCE, PK_CENTER_CODE, PK_COUNTRY, pk_settle_country, PLACE_DEPT, pk_register_grade, CONSUME_ACHIEVE, PAY_TIME, del_flag, PK_CREATOR, PK_PARENT, PK_PLACE_PARENT, PK_MEMBER) select a.pk_id, member_code, member_name, phone, CUSTOMER_TYPE, SOURCE, PK_CENTER_CODE, a.PK_COUNTRY, a.pk_settle_country, PLACE_DEPT, bg.pk_id pk_register_grade, CONSUME_ACHIEVE, PAY_TIME, nvl(a.del_flag, 0), nvl(a.PK_CREATOR, 100000000), PK_PARENT, PK_PLACE_PARENT, PK_MEMBER from (select ba.user_id pk_id, ba.user_name member_code, ba.nick_name member_name, ba.PHONENUMBER phone, ba.CUSTOMER_TYPE, ba.SJLY SOURCE, su.user_id PK_CENTER_CODE, ba.PK_COUNTRY, ba.PK_COUNTRY pk_settle_country, ba.SDEPT PLACE_DEPT, (case ba.ZCDJ when '-1' then 20 when '0' then 30 when '1' then 40 when '2' then 50 when '3' then 60 else 70 end) pk_register_grade, ba.ZCYJ / 6.4 CONSUME_ACHIEVE, ba.ZFSJ PAY_TIME, ba.del_flag, sr.user_id PK_CREATOR, pa.user_id PK_PARENT, pl.user_id PK_PLACE_PARENT, ba.user_id PK_MEMBER from bs_user_base@link_test ba left join sys_user@link_test su on ba.bdzx = su.user_name left join sys_user@link_test sr on sr.user_name = ba.CREATE_BY left join sys_user@link_test pa on ba.parent = pa.user_name left join sys_user@link_test pl on ba.SPARENT = pl.user_name where ba.user_type !='0') a left join bd_grade bg on a.pk_register_grade = bg.grade_value and bg.pk_country = a.pk_country; update cu_member_register set consume_achieve=consume_achieve / 6.4; 修改sequence --会员账户 --select max(pk_id) from cu_member_account insert into cu_member_account(pk_id, pk_member, account1, lock_account1, account2, lock_account2, account3, account4, lock_account4, account5, account6, account7, account8, account9, account10, account11, account12, account13, account14, account15, account16, account17, account18, account19, account20, del_flag, pk_creator, creation_time, pk_country) select su.user_id pk_id, su.user_id pk_member, account1, lock_account1, account2, lock_account2, account3, account4, lock_account4, account5, account6, account7, account8, account9, account10, account11, account12, account13, account14, account15, account16, account17, account18, account19, account20, su.del_flag, nvl(cr.user_id, 100000000) pk_creator, OPERATION_TIME creation_time, su.pk_settle_country pk_country from sys_user_account@link_test sa left join sys_user@link_test su on sa.user_name = su.user_name left join sys_user cr on su.create_by = cr.user_name where su.user_id is not null 修改sequence --会员交易流水 --select max(pk_id) from cu_member_trade insert into cu_member_trade(PK_ID, pk_member, trade_code, PK_ACCOUNT, TRADE_TYPE, TRADE_CLASSIFY, pk_rate, SOURCE_CODE, TRADE_AMOUNT, TRADE_BALANCE, TRADE_BEFORE_BALANCE, TRADE_STATUE, REMARKS, DEL_FLAG, PK_COUNTRY, CREATION_TIME, PK_CREATOR) select a.PK_ID, a.pk_member, a.trade_code, nvl(ba.pk_id, a.pk_account) PK_ACCOUNT, a.TRADE_TYPE, a.TRADE_CLASSIFY, nvl(bc.pk_id,0) pk_rate, a.SOURCE_CODE, nvl(a.TRADE_AMOUNT, 0), nvl(a.TRADE_BALANCE, 0), nvl(a.TRADE_BEFORE_BALANCE, 0), a.TRADE_STATUE, a.REMARKS, a.DEL_FLAG, nvl(a.PK_COUNTRY, 0), a.CREATION_TIME, a.PK_CREATOR from (select pkid PK_ID, nvl(su.user_id, 100000000) pk_member, nvl(djbh, '~') trade_code, (case when to_number(zhlx) <= 7 then to_number(zhlx) when to_number(zhlx) = 12 then 8 when to_number(zhlx) = 13 then 9 when to_number(zhlx) = 14 then 10 when to_number(zhlx) = 16 then 11 when to_number(zhlx) = 20 then 11 else to_number(zhlx) end) PK_ACCOUNT, (case to_number(JYLX) when 10 then 401 when 11 then 300 when 12 then 500 when 14 then 200 when 15 then 201 when 16 then 201 when 17 then 101 when 18 then 102 when 19 then 103 when 20 then 104 when 21 then 105 when 22 then 106 when 23 then 110 when 24 then 501 when 25 then 201 when 27 then 600 when 28 then 601 when 31 then 111 when 32 then 107 when 33 then 108 when 34 then 109 when 35 then 108 when 36 then 111 when 39 then 120 when 44 then 118 when 45 then 119 when 50 then 500 when 55 then 120 when 70 then 552 when 99 then 199 else to_number(jylx) end) TRADE_TYPE, 0 TRADE_CLASSIFY, glbh SOURCE_CODE, JYJE TRADE_AMOUNT, TRANSACTION_END TRADE_BALANCE, (JYJE + TRANSACTION_END) TRADE_BEFORE_BALANCE, bt.STATUS TRADE_STATUE, BZ REMARKS, bt.DEL_FLAG DEL_FLAG, bt.PK_COUNTRY, nvl(bt.CREATE_TIME, bt.jysj) CREATION_TIME, sr.user_id PK_CREATOR from bs_trade@link_test bt left join sys_user@link_test su on bt.yhbh = su.user_name left join sys_user@link_test sr on su.user_name = sr.user_name) a left join bd_account ba on a.PK_ACCOUNT = ba.field_value and a.pk_country = ba.pk_country left join bd_currency bc on bc.pk_country = a.pk_country; update cu_member_trade set trade_classify =1 where trade_type >= 100 and trade_type < 200; update cu_member_trade set trade_classify =2 where trade_type >= 200 and trade_type < 300; update cu_member_trade set trade_classify =3 where trade_type >= 300 and trade_type < 400; update cu_member_trade set trade_classify =4 where trade_type >= 400 and trade_type < 500; update cu_member_trade set trade_classify =5 where trade_type >= 500 and trade_type < 600; update cu_member_trade set trade_classify =6 where trade_type >= 600 and trade_type < 700; update cu_member_trade ct set pk_rate =(select bc.pk_id from bd_currency bc where ct.pk_country = bc.pk_country) where ct.pk_rate !=0 修改sequence --代理商 --select max(pk_id) from cu_member_agent insert into cu_member_agent(pk_id, pk_member, AGENT_TYPE, AGENT_PROVINCE, AGENT_CITY, AGENT_COUNTY, AGENT_GRADE, PK_COUNTRY, CREATION_TIME, PK_CREATOR) select rownum pk_id, su.user_id pk_member, 1 AGENT_TYPE, sh.pk_id, shi.pk_id, xian.pk_id, (case su.DLSJB when '3' then 8 else 7 end) AGENT_GRADE, su.pk_settle_country pk_country, su.CREATE_TIME CREATION_TIME, sr.user_id pk_creator from sys_user@link_test su left join sys_user@link_test sr on su.create_by = sr.user_name left join bd_area sh on sh.code = su.dlsf left join bd_area shi on shi.code = su.dlds left join bd_area xian on xian.code = su.dlqx where su.del_flag = '0' and su.zfzt = '1' and su.DLSJB in (3, 4); --奖衔升级记录 -- select * from sys_User where user_id in(125403,20573,125407,15160) -- select max(pk_id) from cu_member_awards; insert into cu_member_awards(pk_id, pk_member, PERIOD, up_type, OLD_LEVEL, NEW_LEVEL, remark, CREATION_TIME, PK_COUNTRY, pk_creator) select a.pk_id, a.pk_member, a.PERIOD, a.up_type, ba.pk_id OLD_LEVEL, bw.pk_id NEW_LEVEL, a.remark, a.CREATION_TIME, a.PK_COUNTRY, a.pk_creator from (select gl.id pk_id, gl.user_id pk_member, period_id PERIOD, UP_TYPE up_type, (nvl(gl.OLD_LEVEL, 0) * 5) OLD_LEVEL, (nvl(gl.NEW_LEVEL, 0) * 5) NEW_LEVEL, memo REMARK, gl.up_time CREATION_TIME, su.pk_settle_country PK_COUNTRY, gl.user_id PK_CREATOR from G_HONOR_REAL_UP_LOG@LINK_TEST gl inner join sys_user@link_test su on gl.user_code = su.user_name) a left join bd_awards ba on a.OLD_LEVEL = ba.awards_value and a.pk_country = ba.pk_country left join bd_awards bw on a.NEW_LEVEL = bw.awards_value and a.pk_country = bw.pk_country; update cu_member_awards set purchase_status= 0 where pk_id in( select distinct pk_id from cu_member_awards where del_flag=0 and purchase_status= 1 and creation_time >= to_date('2023-07-01','yyyy-mm-dd') and creation_time 0 and not exists (select k.id from g_hischeck_honour@link_test k where k.user_name = l.user_code and to_char(k.end_date, 'yyyy-mm') = to_char(add_months((select p.start_date from g_bonus_period_day@link_test p where p.period = l.period_id), -1), 'yyyy-mm')) )) --等级 --select max(pk_id) from cu_member_level; insert into cu_member_level(PK_ID, pk_member, UPGRADE_TIME, UP_TYPE, OLD_LEVEL, NEW_LEVEL, pk_country, CREATION_TIME, pk_creator, PK_ORDER) select CU_MEMBER_LEVEL_SEQ.Nextval PK_ID, a.* from (select so.pk_member, so.pay_time UPGRADE_TIME, 1 UP_TYPE, 0 OLD_LEVEL, cm.pk_settle_grade NEW_LEVEL, so.pk_country, so.pay_time CREATION_TIME, so.pk_creator, so.pk_id PK_ORDER from sa_order so inner join cu_member cm on so.pk_member = cm.pk_id where so.del_flag = 0 and so.ORDER_STATUS = 1 and order_type in (1, 2, 4) order by cm.pk_id) a; --抗衰能量仓 --select max(pk_id) from CU_ENERGY_SILO; insert into CU_ENERGY_SILO(pk_id, pk_member, IS_ENABLE, ENABLE_DATE, LEASES_NUMBER, END_DATE, REACH_DATE, IS_REACH, DEL_FLAG, PK_CREATOR, CREATION_TIME, PK_COUNTRY) select bs.pk_id, su.user_id pk_member, (case bs.is_enable when 1 then 0 else 1 end) IS_ENABLE, ENABLE_DATE, LEASES_NUMBER, END_DATE, REACH_DATE, (case IS_REACH when 1 then 0 else 1 end) IS_REACH, bs.DEL_FLAG, sr.user_id PK_CREATOR, bs.CREATE_TIME CREATION_TIME, bs.PK_COUNTRY from bs_energy_silo@link_test bs inner join sys_user@link_test su on bs.user_name = su.user_name left join sys_user@link_test sr on sr.user_name = bs.create_by; --抗衰能量仓明细表 --select max(pk_id) from cu_energy_silo_detail; insert into cu_energy_silo_detail(PK_ID, PK_ENERGY_SILO, PK_PARENT, PK_MEMBER, BUY_DATE, PK_ORDER, BUY_NUM, GIFT_BUY_NUM, DEL_FLAG, PK_CREATOR, CREATION_TIME, PK_COUNTRY) select bd.PK_ID, bd.PK_ENERGY_SILO, su.user_id PK_PARENT, sm.user_id PK_MEMBER, bd.BUY_DATE, bo.pkid PK_ORDER, bd.BUY_NUM, bd.GIFT_BUY_NUM, bd.DEL_FLAG, sr.user_id PK_CREATOR, bd.CREATE_TIME CREATION_TIME, bd.PK_COUNTRY from bs_energy_silo_detail@link_test bd inner join sys_user@link_test su on bd.parent = su.user_name inner join sys_user@link_test sm on sm.user_name = bd.user_name left join bs_order@link_test bo on bo.ddbh = bd.order_code inner join sys_user@link_test sr on sr.user_name = bd.create_by -- 历史小程序游客数据删除 update cu_member cm set cm.del_flag = 1 where cm.del_flag = 0 and cm.customer_type = 5; --奖金可消费金额 ，剩余的金额作为可提现，更新cu_member_account select * from cu_member_settle_period where settle_date = to_date('2023-06-12', 'yyyy-mm-dd'); insert into CU_MEMBER_TRADE_DETAIL(period, pk_member_trade, grant_Money, consume_money, pk_country, creation_time, pk_creator) select 892 period, pk_id PK_MEMBER_TRADE, trade_amount grant_Money, trade_amount CONSUME_MONEY, pk_country, CREATION_TIME, pk_creator from cu_member_tradeCU_MEMBER_MONTH_ACHIEVE where TRADE_CLASSIFY = 5 and CREATION_TIME >= to_date('2023-06-12', 'yyyy-mm-dd') and CREATION_TIME < to_date('2023-06-13', 'yyyy-mm-dd'); --荣誉墙-等级 -- Create sequence drop sequence CU_MEMBER_HONOR_WALL_SEQ; create sequence CU_MEMBER_HONOR_WALL_SEQ minvalue 1 maxvalue 99999999999 start with 1 increment by 1 cache 10; insert into cu_member_honor_wall(pk_id, pk_member, pk_honor_wall, settle_month, pk_country, pk_creator) select cu_member_honor_wall_seq.nextval pk_id, cm.pk_id pk_member, cw.pk_id pk_honor_wall, to_char(pay_time, 'yyyy-mm') settle_month, cm.pk_settle_country pk_country, cm.pk_creator from cu_member cm inner join CU_HONOR_WALL cw on cm.pk_settle_grade = cw.value where cw.TYPE = 1 and cm.del_flag = 0 and cm.pay_status = 1 --荣誉墙-奖衔 insert into cu_member_honor_wall(pk_id, pk_member, pk_honor_wall, settle_month, pk_country, pk_creator) select cu_member_honor_wall_seq.nextval pk_id, cm.pk_id pk_member, cw.pk_id pk_honor_wall, to_char(pay_time, 'yyyy-mm') settle_month, cm.pk_settle_country pk_country, cm.pk_creator from cu_member cm inner join CU_HONOR_WALL cw on cm.PK_AWARDS = cw.value where cw.TYPE = 2 and cm.del_flag = 0 and cm.pay_status = 1