341 lines
10 KiB
MySQL
341 lines
10 KiB
MySQL
|
--------充值----------
|
||
|
|
|
||
|
|
drop SEQUENCE CU_MEMBER_RECHARGE_SEQ;
|
||
|
|
|
||
|
|
-- Create sequence
|
||
|
|
create sequence CU_MEMBER_RECHARGE_SEQ
|
||
|
|
minvalue 1
|
||
|
|
maxvalue 9999999999
|
||
|
|
start with 1
|
||
|
|
increment by 1
|
||
|
|
cache 10;
|
||
|
|
|
||
|
|
|
||
|
|
insert into hzs_cloud.CU_MEMBER_RECHARGE
|
||
|
|
(PK_ID,
|
||
|
|
PK_MEMBER,
|
||
|
|
RECHARGE_AMOUNT,
|
||
|
|
PK_ACCOUNT,
|
||
|
|
RECHARGE_TYPE,
|
||
|
|
RECHARGE_CODE,
|
||
|
|
APPROVE_STATE,
|
||
|
|
APPROVE_TIME,
|
||
|
|
APPROVER,
|
||
|
|
REMARKS,
|
||
|
|
DEL_FLAG,
|
||
|
|
PK_COUNTRY,
|
||
|
|
CREATION_TIME,
|
||
|
|
MODIFIED_TIME,
|
||
|
|
PK_RATE,
|
||
|
|
PK_CREATOR,
|
||
|
|
PK_MODIFIED,
|
||
|
|
APPROVE_REMARKS,
|
||
|
|
BATCH,
|
||
|
|
RECHARGE_METHOD,
|
||
|
|
RECHARGE_SOURCE)
|
||
|
|
|
||
|
|
select CU_MEMBER_RECHARGE_SEQ.NEXTVAL PK_ID,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = c.yhbh) PK_MEMBER,
|
||
|
|
c.czje RECHARGE_AMOUNT,
|
||
|
|
(select a.pk_id from bd_account a where a.field_value = decode(c.bzlx,12,8,c.bzlx ) and a.pk_country = c.pk_country and a.enable_state = 0) PK_ACCOUNT,
|
||
|
|
4 RECHARGE_TYPE,
|
||
|
|
c.czbh RECHARGE_CODE,
|
||
|
|
c.shzt + 1 APPROVE_STATE,
|
||
|
|
c.shsj APPROVE_TIME,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = c.shr) APPROVER,
|
||
|
|
c.czbz REMARKS,
|
||
|
|
c.del_flag,
|
||
|
|
c.pk_country,
|
||
|
|
c.create_time CREATION_TIME,
|
||
|
|
c.update_time MODIFIED_TIME,
|
||
|
|
(select bc.pk_id from bd_currency bc where bc.pk_country = c.pk_country ) PK_RATE,
|
||
|
|
nvl((select m.pk_id from cu_member m where m.member_code = c.create_by ),100000000) PK_CREATOR,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = c.update_by) PK_MODIFIED,
|
||
|
|
c.shbz APPROVE_REMARKS,
|
||
|
|
c.czbh BATCH,
|
||
|
|
case
|
||
|
|
when c.czje > 0 then
|
||
|
|
1
|
||
|
|
else
|
||
|
|
2
|
||
|
|
end RECHARGE_METHOD,
|
||
|
|
|
||
|
|
case
|
||
|
|
when c.czlx = 7 then
|
||
|
|
1
|
||
|
|
when c.czlx = 8 then
|
||
|
|
1
|
||
|
|
when c.czlx = 9 then
|
||
|
|
1
|
||
|
|
when c.czlx = 10 then
|
||
|
|
1
|
||
|
|
when c.czlx = 20 then
|
||
|
|
1
|
||
|
|
else
|
||
|
|
0
|
||
|
|
end RECHARGE_SOURCE
|
||
|
|
from hzs_cn.bs_charge c
|
||
|
|
where (select m.pk_id from cu_member m where m.member_code = c.yhbh) is not null;
|
||
|
|
|
||
|
|
commit;
|
||
|
|
|
||
|
|
----查询对比----
|
||
|
|
select count(1) from hzs_cn.bs_charge c where (select m.pk_id from cu_member m where m.member_code = c.yhbh) is not null;
|
||
|
|
|
||
|
|
|
||
|
|
-----备注----
|
||
|
|
-------17 马来、105 尼印有政策币
|
||
|
|
|
||
|
|
insert into BD_ACCOUNT (PK_ID, ACCOUNT_NAME, PK_TRANSACTION_KEY, SORT, FIELD_VALUE, ENABLE_STATE, IS_TRANSFER, IS_WITHDRAWAL, EFFECTIVE_DATE, BACKGROUND_IMG, DEL_FLAG, PK_COUNTRY, CREATION_TIME, MODIFIED_TIME, IS_RECHARGE, IS_REGULAR, IS_POSTAGE, PK_CREATOR, PK_MODIFIED, PK_BD_ACCOUNT, ON_LINE_RECHARGE, SHOW_STATUS)
|
||
|
|
values (bd_account_seq.nextval , '政策币账户', null, 3, 3, 0, 0, 0, to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'https://hzs-in.oss-cn-beijing.aliyuncs.com/test-new/20230420/40e0478d-d01c-4db1-b916-dadb369b287c.jpg', 0, 17, to_date('15-06-2023 14:35:46', 'dd-mm-yyyy hh24:mi:ss'), null, 0, 0, 0, 100000000, null, null, 1, null);
|
||
|
|
|
||
|
|
|
||
|
|
insert into BD_ACCOUNT (PK_ID, ACCOUNT_NAME, PK_TRANSACTION_KEY, SORT, FIELD_VALUE, ENABLE_STATE, IS_TRANSFER, IS_WITHDRAWAL, EFFECTIVE_DATE, BACKGROUND_IMG, DEL_FLAG, PK_COUNTRY, CREATION_TIME, MODIFIED_TIME, IS_RECHARGE, IS_REGULAR, IS_POSTAGE, PK_CREATOR, PK_MODIFIED, PK_BD_ACCOUNT, ON_LINE_RECHARGE, SHOW_STATUS)
|
||
|
|
values (BD_ACCOUNT_seq.Nextval, '政策币账户', null, 3, 3, 0, 0, 0, to_date('01-01-2023 01:00:00', 'dd-mm-yyyy hh24:mi:ss'), 'https://hzs-in.oss-cn-beijing.aliyuncs.com/test-new/20230420/40e0478d-d01c-4db1-b916-dadb369b287c.jpg', 0, 105, to_date('15-06-2023 14:37:09', 'dd-mm-yyyy hh24:mi:ss'), null, 0, 0, 0, 100000000, null, null, 1, null);
|
||
|
|
|
||
|
|
|
||
|
|
commit;
|
||
|
|
|
||
|
|
-----充值详情-------
|
||
|
|
|
||
|
|
drop SEQUENCE cu_member_recharge_detail_seq;
|
||
|
|
|
||
|
|
-- Create sequence
|
||
|
|
create sequence cu_member_recharge_detail_seq
|
||
|
|
minvalue 1
|
||
|
|
maxvalue 9999999999
|
||
|
|
start with 1
|
||
|
|
increment by 1
|
||
|
|
cache 10;
|
||
|
|
|
||
|
|
insert into CU_MEMBER_RECHARGE_DETAIL
|
||
|
|
(PK_ID,
|
||
|
|
RECHARGE_CODE,
|
||
|
|
PAYMENT_METHOD,
|
||
|
|
PAYMENT_AMOUNT,
|
||
|
|
CREATION_TIME,
|
||
|
|
DEL_FLAG,
|
||
|
|
PK_CREATOR)
|
||
|
|
select cu_member_recharge_detail_seq.nextval PK_ID,
|
||
|
|
r.recharge_code,
|
||
|
|
r.recharge_type PAYMENT_METHOD,
|
||
|
|
r.recharge_amount PAYMENT_AMOUNT,
|
||
|
|
r.creation_time,
|
||
|
|
r.del_flag,
|
||
|
|
r.pk_creator
|
||
|
|
from cu_member_recharge r
|
||
|
|
where r.recharge_code is not null
|
||
|
|
and r.del_flag=0;
|
||
|
|
commit;
|
||
|
|
|
||
|
|
|
||
|
|
------提现------
|
||
|
|
|
||
|
|
drop SEQUENCE CU_MEMBER_WITHDRAW_SEQ;
|
||
|
|
|
||
|
|
-- Create sequence
|
||
|
|
create sequence CU_MEMBER_WITHDRAW_SEQ
|
||
|
|
minvalue 1
|
||
|
|
maxvalue 9999999999
|
||
|
|
start with 1
|
||
|
|
increment by 1
|
||
|
|
cache 10;
|
||
|
|
|
||
|
|
insert into CU_MEMBER_WITHDRAW
|
||
|
|
(PK_ID,
|
||
|
|
PK_MEMBER,
|
||
|
|
WITHDRAW_CODE,
|
||
|
|
PK_ACCOUNT,
|
||
|
|
PK_RATE,
|
||
|
|
CASH_AMOUNT,
|
||
|
|
SERVICE_CHARGE,
|
||
|
|
INCOME_TAX,
|
||
|
|
ISSUED_AMOUNT,
|
||
|
|
APPROVE_STATE,
|
||
|
|
PAYMENT_TIME,
|
||
|
|
PAYER,
|
||
|
|
REMARKS,
|
||
|
|
DEL_FLAG,
|
||
|
|
PK_COUNTRY,
|
||
|
|
CREATION_TIME,
|
||
|
|
MODIFIED_TIME,
|
||
|
|
APPROVER,
|
||
|
|
APPROVE_TIME,
|
||
|
|
PK_CREATOR,
|
||
|
|
PK_MODIFIED,
|
||
|
|
STATUS,
|
||
|
|
PAYER_REMARKS,
|
||
|
|
APPROVE_REMARKS,
|
||
|
|
BATCH)
|
||
|
|
|
||
|
|
select CU_MEMBER_WITHDRAW_SEQ.Nextval,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = c.yhbh and m.del_flag = 0) PK_MEMBER,
|
||
|
|
c.sqdh WITHDRAW_CODE,
|
||
|
|
(select a.pk_id from bd_account a where a.field_value = 4 and a.pk_country = c.pk_country and a.enable_state = 0) PK_ACCOUNT,
|
||
|
|
(select bc.pk_id from bd_currency bc where bc.pk_country = c.pk_country and bc.del_flag = 0) PK_RATE,
|
||
|
|
c.txje CASH_AMOUNT,
|
||
|
|
c.sxf SERVICE_CHARGE,
|
||
|
|
c.grsds INCOME_TAX,
|
||
|
|
c.sfje ISSUED_AMOUNT,
|
||
|
|
case
|
||
|
|
when c.zt = 1 then
|
||
|
|
1
|
||
|
|
when c.zt = 2 then
|
||
|
|
5
|
||
|
|
when c.zt = 3 then
|
||
|
|
4
|
||
|
|
when c.zt = 4 then
|
||
|
|
5
|
||
|
|
when c.zt = 5 then
|
||
|
|
1
|
||
|
|
when c.zt = 6 then
|
||
|
|
4
|
||
|
|
end APPROVE_STATE,
|
||
|
|
c.dkrq PAYMENT_TIME,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = c.dkr and m.del_flag = 0) PAYER,
|
||
|
|
c.bz REMARKS,
|
||
|
|
c.del_flag DEL_FLAG,
|
||
|
|
c.pk_country,
|
||
|
|
c.create_time CREATION_TIME,
|
||
|
|
c.update_time MODIFIED_TIME,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = c.shr and m.del_flag = 0) APPROVER,
|
||
|
|
c.shrq APPROVE_TIME,
|
||
|
|
nvl((select m.pk_id from cu_member m where m.member_code = c.create_by and m.del_flag = 0),100000000) PK_CREATOR,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = c.update_by and m.del_flag = 0) PK_MODIFIED,
|
||
|
|
case
|
||
|
|
when c.zt = 4 then
|
||
|
|
1
|
||
|
|
when c.zt = 5 then
|
||
|
|
3
|
||
|
|
when c.zt = 6 then
|
||
|
|
2
|
||
|
|
else
|
||
|
|
0
|
||
|
|
end STATUS,
|
||
|
|
c.dkbz PAYER_REMARKS,
|
||
|
|
c.shbz APPROVE_REMARKS,
|
||
|
|
c.sqdh BATCH
|
||
|
|
from hzs_cn.bs_cash c;
|
||
|
|
commit;
|
||
|
|
|
||
|
|
----转账---------
|
||
|
|
|
||
|
|
drop SEQUENCE cu_member_transfer_seq;
|
||
|
|
|
||
|
|
-- Create sequence
|
||
|
|
create sequence cu_member_transfer_seq
|
||
|
|
minvalue 1
|
||
|
|
maxvalue 9999999999
|
||
|
|
start with 1
|
||
|
|
increment by 1
|
||
|
|
cache 10;
|
||
|
|
|
||
|
|
|
||
|
|
insert into cu_member_transfer
|
||
|
|
(PK_ID,
|
||
|
|
PK_MEMBER,
|
||
|
|
TRANSFER_CODE,
|
||
|
|
PK_ACCOUNT,
|
||
|
|
TO_PK_MEMBER,
|
||
|
|
PK_TO_ACCOUNT,
|
||
|
|
PK_RATE,
|
||
|
|
TRANSFER_MONEY,
|
||
|
|
REMARKS,
|
||
|
|
DEL_FLAG,
|
||
|
|
CREATION_TIME,
|
||
|
|
MODIFIED_TIME,
|
||
|
|
PK_COUNTRY,
|
||
|
|
PK_CREATOR,
|
||
|
|
PK_MODIFIED,
|
||
|
|
ISSUED_AMOUNT)
|
||
|
|
select cu_member_transfer_seq.nextval,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = t.zcfbh and m.del_flag = 0) PK_MEMBER,
|
||
|
|
t.zzdh TRANSFER_CODE,
|
||
|
|
(select a.pk_id from bd_account a where a.field_value = t.zzlx and a.pk_country = t.pk_country and a.enable_state = 0) PK_ACCOUNT,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = t.jsfbh and m.del_flag = 0) TO_PK_MEMBER,
|
||
|
|
(select a.pk_id from bd_account a where a.field_value = (case when t.zzlx = '4' then '1' when t.zzlx = '7' then '1' else t.zzlx end )
|
||
|
|
and a.pk_country = t.pk_country and a.enable_state = 0) PK_TO_ACCOUNT,
|
||
|
|
(select bc.pk_id from bd_currency bc where bc.pk_country = t.pk_country and bc.del_flag = 0) PK_RATE,
|
||
|
|
t.zzje TRANSFER_MONEY,
|
||
|
|
t.bz REMARKS,
|
||
|
|
t.del_flag,
|
||
|
|
t.create_time CREATION_TIME,
|
||
|
|
t.update_time MODIFIED_TIME,
|
||
|
|
t.pk_country,
|
||
|
|
nvl((select m.pk_id from cu_member m where m.member_code = t.create_by and m.del_flag = 0),100000000) PK_CREATOR,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = t.update_by and m.del_flag = 0) PK_MODIFIED,
|
||
|
|
t.zzje ISSUED_AMOUNT
|
||
|
|
from hzs_cn.bs_trans t
|
||
|
|
where (select m.pk_id from hzs_cloud.cu_member m where m.member_code = t.jsfbh and m.del_flag = 0) is not null
|
||
|
|
and (select m.pk_id from cu_member m where m.member_code = t.zcfbh and m.del_flag = 0) is not null;
|
||
|
|
|
||
|
|
commit;
|
||
|
|
|
||
|
|
|
||
|
|
--------验证------
|
||
|
|
select count(1) from cu_member_transfer;
|
||
|
|
|
||
|
|
select count(1) from hzs_cn.bs_trans t
|
||
|
|
where (select m.pk_id from hzs_cloud.cu_member m where m.member_code = t.jsfbh and m.del_flag = 0) is not null
|
||
|
|
and (select m.pk_id from cu_member m where m.member_code = t.zcfbh and m.del_flag = 0) is not null;
|
||
|
|
|
||
|
|
-----账户详情-----
|
||
|
|
|
||
|
|
drop SEQUENCE cu_member_account_detail_seq;
|
||
|
|
|
||
|
|
-- Create sequence
|
||
|
|
create sequence cu_member_account_detail_seq
|
||
|
|
minvalue 1
|
||
|
|
maxvalue 9999999999
|
||
|
|
start with 1
|
||
|
|
increment by 1
|
||
|
|
cache 10;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
---------- 可以调用importData 存储过程---------------------------
|
||
|
|
|
||
|
|
|
||
|
|
insert into cu_member_account_detail
|
||
|
|
(PK_ID,
|
||
|
|
PK_MEMBER,
|
||
|
|
ACCOUNT1,
|
||
|
|
ACCOUNT2,
|
||
|
|
ACCOUNT3,
|
||
|
|
ACCOUNT4,
|
||
|
|
ACCOUNT5,
|
||
|
|
ACCOUNT6,
|
||
|
|
ACCOUNT7,
|
||
|
|
ACCOUNT8,
|
||
|
|
ACCOUNT9,
|
||
|
|
ACCOUNT10,
|
||
|
|
ACCOUNT11,
|
||
|
|
ACCOUNT12,
|
||
|
|
CREATE_TIME,
|
||
|
|
PK_RATE,
|
||
|
|
PK_COUNTRY)
|
||
|
|
select cu_member_account_detail_seq.nextval PK_ID,
|
||
|
|
(select m.pk_id from cu_member m where m.member_code = d.user_name) PK_MEMBER,
|
||
|
|
d.account1,
|
||
|
|
d.account2,
|
||
|
|
d.account3,
|
||
|
|
d.account4,
|
||
|
|
d.account5,
|
||
|
|
d.account6,
|
||
|
|
d.account7,
|
||
|
|
d.account8,
|
||
|
|
d.account9,
|
||
|
|
d.account10,
|
||
|
|
d.account11,
|
||
|
|
d.account12,
|
||
|
|
d.create_time CREATE_TIME,
|
||
|
|
(select bc.pk_id from bd_currency bc where bc.pk_country = u.pk_country and bc.del_flag = 0) PK_RATE,
|
||
|
|
u.pk_country
|
||
|
|
from hzs_cn.sys_user_account_detail d, hzs_cn.sys_user u
|
||
|
|
where u.user_name=d.user_name
|
||
|
|
and (select m.pk_id from cu_member m where m.member_code = d.user_name) is not null
|
||
|
|
and d.create_time>=to_date('2023-06-01','yyyy-mm-dd');
|
||
|
|
|
||
|
|
|
||
|
|
|