--跟新验证码
以下为测试时使用,闭屏CA后有效,更新为明文验证码:
DB2:
update cmcs3_transfer_bill_list blist
set blist.authno =
(
select rtrim(char(bigint(b.typeid)))||rtrim(char(bigint(b.accountid)))||bl.payeeaccountno||b.totalamount||bl.payamount||rtrim(char(bigint(b.stateid)))||b.payserialno||rtrim(char(bigint(bl.bankstateid)))||to_char(b.submittime,'yyyy-MM-dd hh24:mi:ss')
from cmcs3_transfer_bill b, cmcs3_transfer_bill_list bl where b.transferbillid = bl.transferbillid
and bl.transferbilllistid = blist.transferbilllistid
)
where blist.transferbillid = 245295; -- 此为更新BILL对应所有BILLLIST 要更新单个 将此行改为 where blist.transferbilllistid = ?
ORACLE:
update cmcs3_transfer_bill_list blist
set blist.authno =
(
select b.typeid||b.accountid||bl.payeeaccountno||b.totalamount||bl.payamount||b.stateid||b.payserialno||bl.bankstateid||to_char(b.submittime,'yyyy-MM-dd hh24:mi:ss')
from cmcs3_transfer_bill b, cmcs3_transfer_bill_list bl where b.transferbillid = bl.transferbillid
and bl.transferbilllistid = blist.transferbilllistid
)
where blist.transferbillid = ?; -- 此为更新BILL对应所有BILLLIST 要更新单个 将此行改为 where blist.transferbilllistid = ?
--凭证编制的提示信息问题
"单位"+org.getName()+"的setOfBooksId值可能为空或者不为数字类型!" 科目对应的帐套或者是账号对应的帐套为空
"导入失败!账号:"+bankAccount.getAccountNO()+"的AssentConName值为空。"; 账号的一个属性为空
"单位:"+oppOrg.getOuName()+"的会计字段为空,无法导入!");
"没有在账号:"+accountNO+"和账号:"+oppAccountNO+"之间设置会计科目!"); 账户管理里面新增界面有一个“科目”为空
科目的格式:4123.000000.6131010180.000000.00000000.0000.000000 前一个为组织,第三个为真正的科目
--解决行名行号跟新过来为0的问题的方案
----把重复的行名行号删除
delete from cmcs3_bank_code a where a.bankcode in
(select c.bankcode from cmcs3_bank_code c group by c.bankcode having count(1) > 1) and a.isvalid = 1;
update cmcs3_bank_code c set c.isvalid = 1;
commit;
select * from cmcs3_bank_code a where isvalid=0 确定isValid为0没有了
--屏蔽证书的问题,改成N就行了
select * from cmcs3_config c where c.configkey like '%IsNeedUims%';
--山西张磊要做的报表
--每月制单个数
select count(*) from cmcs3_transfer_bill b where to_char(b.submittime,'yyyymm') = '201201';
--每月制单峰值数(即每月那天制单最多,是多少
select to_char(b.submittime,'yyyymmdd'),count(*) from cmcs3_transfer_bill b
where to_char(b.submittime,'yyyymm') = '201201' group by to_char(b.submittime,'yyyymmdd')
order by count(*) desc;
--每月同步的凭证个数
select to_char(l.updatetime,'yyyymm'),count(*) from cmcs3_payment_list l
group by to_char(l.updatetime,'yyyymm') order by count(*) desc;
--每天同步的凭证个数
select to_char(l.updatetime,'yyyymmdd'),count(*) from cmcs3_payment_list l where to_char(l.updatetime,'yyyymm')='201012'
group by to_char(l.updatetime,'yyyymmdd') order by count(*) desc;
--回单确认
当失败转失败的时候,是不需要走流程的
只有成功转失败才走支付异常流程
失败转成功是不需要走流程的?
成功转失败的话,不走流程就会造成重复支付嘛
以下为测试时使用,闭屏CA后有效,更新为明文验证码:
DB2:
update cmcs3_transfer_bill_list blist
set blist.authno =
(
select rtrim(char(bigint(b.typeid)))||rtrim(char(bigint(b.accountid)))||bl.payeeaccountno||b.totalamount||bl.payamount||rtrim(char(bigint(b.stateid)))||b.payserialno||rtrim(char(bigint(bl.bankstateid)))||to_char(b.submittime,'yyyy-MM-dd hh24:mi:ss')
from cmcs3_transfer_bill b, cmcs3_transfer_bill_list bl where b.transferbillid = bl.transferbillid
and bl.transferbilllistid = blist.transferbilllistid
)
where blist.transferbillid = 245295; -- 此为更新BILL对应所有BILLLIST 要更新单个 将此行改为 where blist.transferbilllistid = ?
ORACLE:
update cmcs3_transfer_bill_list blist
set blist.authno =
(
select b.typeid||b.accountid||bl.payeeaccountno||b.totalamount||bl.payamount||b.stateid||b.payserialno||bl.bankstateid||to_char(b.submittime,'yyyy-MM-dd hh24:mi:ss')
from cmcs3_transfer_bill b, cmcs3_transfer_bill_list bl where b.transferbillid = bl.transferbillid
and bl.transferbilllistid = blist.transferbilllistid
)
where blist.transferbillid = ?; -- 此为更新BILL对应所有BILLLIST 要更新单个 将此行改为 where blist.transferbilllistid = ?
--凭证编制的提示信息问题
"单位"+org.getName()+"的setOfBooksId值可能为空或者不为数字类型!" 科目对应的帐套或者是账号对应的帐套为空
"导入失败!账号:"+bankAccount.getAccountNO()+"的AssentConName值为空。"; 账号的一个属性为空
"单位:"+oppOrg.getOuName()+"的会计字段为空,无法导入!");
"没有在账号:"+accountNO+"和账号:"+oppAccountNO+"之间设置会计科目!"); 账户管理里面新增界面有一个“科目”为空
科目的格式:4123.000000.6131010180.000000.00000000.0000.000000 前一个为组织,第三个为真正的科目
--解决行名行号跟新过来为0的问题的方案
----把重复的行名行号删除
delete from cmcs3_bank_code a where a.bankcode in
(select c.bankcode from cmcs3_bank_code c group by c.bankcode having count(1) > 1) and a.isvalid = 1;
update cmcs3_bank_code c set c.isvalid = 1;
commit;
select * from cmcs3_bank_code a where isvalid=0 确定isValid为0没有了
--屏蔽证书的问题,改成N就行了
select * from cmcs3_config c where c.configkey like '%IsNeedUims%';
--山西张磊要做的报表
--每月制单个数
select count(*) from cmcs3_transfer_bill b where to_char(b.submittime,'yyyymm') = '201201';
--每月制单峰值数(即每月那天制单最多,是多少
select to_char(b.submittime,'yyyymmdd'),count(*) from cmcs3_transfer_bill b
where to_char(b.submittime,'yyyymm') = '201201' group by to_char(b.submittime,'yyyymmdd')
order by count(*) desc;
--每月同步的凭证个数
select to_char(l.updatetime,'yyyymm'),count(*) from cmcs3_payment_list l
group by to_char(l.updatetime,'yyyymm') order by count(*) desc;
--每天同步的凭证个数
select to_char(l.updatetime,'yyyymmdd'),count(*) from cmcs3_payment_list l where to_char(l.updatetime,'yyyymm')='201012'
group by to_char(l.updatetime,'yyyymmdd') order by count(*) desc;
--回单确认
当失败转失败的时候,是不需要走流程的
只有成功转失败才走支付异常流程
失败转成功是不需要走流程的?
成功转失败的话,不走流程就会造成重复支付嘛