angelo
/
java-store-retail-app
forked from angelo/java-retail-app
3
0
Fork 0
Code Pull Requests Activity
java-store-retail-app/文档/迁移语句/会员迁移语句.sql

625 lines
26 KiB
MySQL
Raw Permalink Normal View History

## 初始化；
2025-03-23 09:44:16 +08:00
-- -- 体系
-- --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 <to_date('2023-08-04','yyyy-mm-dd')
and pk_member in(
select l.user_id
from g_honor_real_up_log@link_test l
where to_char((select p.start_date
from g_bonus_period_day@link_test p
where p.period = l.period_id),'yyyy-mm') = '2023-07'
and l.id = (select max(r.id)
from g_honor_real_up_log@link_test r
where r.user_code = l.user_code)
and l.new_level > 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