一。修数。使用VPN远程连客户系统,操作中登录了VPN就能够连接客户公司的内网了。
VPN一般指虚拟专用网络。虚拟专用网络的功能是:在公用网络上建立专用网络,进行加密通讯。在企业网络中有广泛应用。VPN网关通过对数据包的加密和数据包目标地址的转换实现远程访问。
而堡垒机是是为了防止别人远程连接到数据库,确保安全。登录了堡垒机账号就可以登录到相应的数据库。
SX18070954162报销单账号错误支付失败,修改后报销单页面及重新送到支付接口的还是旧的账号。
在数据库中查询出相关的数据,之后再 for update 一下。
t_cem_bill_maindata.billno = t_cem_bill_fund_payment.billcode
t_cem_fund_payment.id = t_cem_bill_payment.id
select * from T_CEM_BILL_MAINDATA where billno = 'SX18070954162';
select * from T_CEM_FUND_PAYMENT where billcode = 'SX18070954162' for update;
select * from T_CEM_BILL_PAYMENT where mainid='BI2018070900168715' for update;
select * from t_cem_sys_supplier华夏银行北京朝阳门支行 304100042933 911101057655333947
先执行 ... for update 防止他人操作之后数据不一致。然后解锁,更改,点勾,锁上,提交/回滚
PL/SQL中修数操作https://jingyan.baidu.com/article/f25ef2546d6be1482c1b82e2.html 链接
二。APPgateway
问题:
APP报错,用户“潇潇”在手机上登录复星系统报错。她在费控系统中的委托授权不成功,手机app上也没办法设置
【这里要特别注意另外一个问题:PC端的委托设置好之后,没多久就会失效。客户认为自己授权给了别人,自己就收不到代办了,同时代办应该在被委托人那里。
而我们系统设计的时候,授权人和被授权人都是可以看到代办的。所以这不是一个失效】
解决方法:
APPgateway没有配置,要加上。
###################################################################
###################################################################
1)运维网页==》主机运维==》60.56 手机app 服务器1 使用root + 1234567,移动端有专门的服务器,可以将生产环境下的文件导出到自己本地机子
连接到SSH,在xShell软件中敲入指令
找到APPGateway的路径,将Linux生产环境下的./home/APPGateway/WEB-INF文件,使用xShell软件导出WEB-INF文件
使用使用xShell软件将系统中的这个WEB-INF文件导出来。
修改\WEB-INF\classes\process\billcenter中的 jkurrent-action-billcenter.xml文件,
2)往jkurrent-action-billcenter.xml中添加语句,将Appgateway配置上
<action id="qryAllEmployees" template="simpleTemplate" interceptors="defaultInterceptors">
<jkservices>
<jkservice id="action1" ref="dispatchService" operations="dispatch"></jkservice>
</jkservices>
</action>
其中,这个 <action id="qryAllEmployees" 的id 值是由于移动端开发人员提供。为qryAllEmployees.app?。具体如下
http://feapp.fosun-uhi.com:8018/APPGateway/qryAllEmployees.app?accesstoken=91693BEFA93362633B23CDB8402DAAD6&account=wanglei&authenticator=F1046C23E71AC390FA0A18D913359B6F&channelcode=APP&corpid=SY2017091800057214¤ttime=2018-08-16%2014%3A28%3A08&depatementId=SY2017091800057237&employeeId=SY2017091900057331&machinecode=55805d77050b2a5b&machinetype=Simulator%20x64&name=&pageNum=1&pageSize=15&platform=CEMAPPFXLHPRO&sign=pkXm9CWXxVEwptsFwjP2tJ5fmr9eIwi28UdTubY9ioUDJprMqNOAhOigcJg2xcvgtVBFEkrznBCCfyLgo0Za8JVhMUjgyz7tfCNPHGGqiGoSyYODQ%252F3wQ6A16DUpeZARLxvjDBNo%252BhhElBDJCe5ZAoITqqj7I4c6CIxh9ZLH%252FlU%253D&terminalcode=iOS&version=3.0.5
3)将修改好的jkurrent-action-billcenter.xml 文件,使用xShell软件上传到生产环境Linux系统中,并执行。
启动Linux中的Tomcat 执行,为:
[root@fudmz2-fkapp1 classes]# cd /usr/local/tomcat/apache-tomcat-7.0.81/bin/
[root@fudmz2-fkapp1 bin]# sh shutdown.sh 或 ./shutdown.sh
Using CATALINA_BASE: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_HOME: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_TMPDIR: /usr/local/tomcat/apache-tomcat-7.0.81/temp
Using JRE_HOME: /usr
Using CLASSPATH: /usr/local/tomcat/apache-tomcat-7.0.81/bin/bootstrap.jar:/usr/local/tomcat/apache-tomcat-7.0.81/bin/tomcat-juli.jar
s[root@fudmz2-fkapp1 bin]# sh startup.sh 或 ./startup.sh
Using CATALINA_BASE: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_HOME: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_TMPDIR: /usr/local/tomcat/apache-tomcat-7.0.81/temp
Using JRE_HOME: /usr
Using CLASSPATH: /usr/local/tomcat/apache-tomcat-7.0.81/bin/bootstrap.jar:/usr/local/tomcat/apache-tomcat-7.0.81/bin/tomcat-juli.jar
Tomcat started.
###################################################################
###################################################################
相关记录:
运维网页==》主机运维==》60.56 手机app 服务器1 使用root + 1234567
连接到SSH,敲入指令
[root@fudmz2-fkapp1 /]# cd ..
[root@fudmz2-fkapp1 /]# ll
总用量 36
lrwxrwxrwx. 1 root root 7 11月 27 2016 bin -> usr/bin
dr-xr-xr-x. 4 root root 4096 11月 21 2017 boot
drwxr-xr-x. 3 root root 17 11月 21 2017 data0
drwxr-xr-x. 19 root root 3200 8月 23 2017 dev
drwxr-xr-x. 137 root root 8192 11月 24 2017 etc
drwxr-xr-x. 6 root root 4096 7月 3 13:04 home
lrwxrwxrwx. 1 root root 7 11月 27 2016 lib -> usr/lib
lrwxrwxrwx. 1 root root 9 11月 27 2016 lib64 -> usr/lib64
drwxr-xr-x. 2 root root 6 8月 12 2015 media
drwxr-xr-x. 2 root root 6 8月 12 2015 mnt
drwxr-xr-x. 3 root root 15 11月 27 2016 opt
dr-xr-xr-x. 201 root root 0 8月 23 2017 proc
dr-xr-x---. 14 root root 4096 7月 3 13:07 root
drwxr-xr-x. 37 root root 1100 8月 16 06:25 run
lrwxrwxrwx. 1 root root 8 11月 27 2016 sbin -> usr/sbin
drwxr-xr-x. 2 root root 6 8月 12 2015 srv
dr-xr-xr-x. 13 root root 0 8月 23 2017 sys
drwxrwxrwt. 15 root root 4096 8月 16 20:42 tmp
drwxr-xr-x. 13 root root 4096 11月 27 2016 usr
drwxr-xr-x. 21 root root 4096 8月 23 2017 var
[root@fudmz2-fkapp1 /]# pwd
/
[root@fudmz2-fkapp1 /]#
[root@fudmz2-fkapp1 /]# find -name *way
./home/logs/appgateway
./home/APPGateway
./home/APPGateway/META-INF/maven/com.kurrent/APPGateway
./usr/share/zoneinfo/Pacific/Midway
./usr/share/zoneinfo/posix/Pacific/Midway
./usr/share/zoneinfo/right/Pacific/Midway
./usr/local/tomcat/apache-tomcat-7.0.81/work/Catalina/localhost/APPGateway
[root@fudmz2-fkapp1 /]# cd /home/APPGateway/
[root@fudmz2-fkapp1 APPGateway]# ls
index.jsp META-INF rePassword.jsp resource WEB-INF
[root@fudmz2-fkapp1 APPGateway]# cd WEB-INF/
[root@fudmz2-fkapp1 WEB-INF]# ls
classes jetty-web.xml lib weblogic.xml web.xml
[root@fudmz2-fkapp1 WEB-INF]# cd classes/
[root@fudmz2-fkapp1 classes]# ls
com jkurrent-http-client.xml jkurrentMain.xml jkurrent-ws.xml process validata validation.xml
jkurrent-core.xml jkurrent-http.xml jkurrent-template.xml logback.xml properties validation-bean.xml
[root@fudmz2-fkapp1 classes]# ls -l
总用量 52
drwxr-xr-x. 3 root root 20 11月 21 2017 com
-rw-r--r--. 1 root root 2280 11月 21 2017 jkurrent-core.xml
-rw-r--r--. 1 root root 4106 11月 21 2017 jkurrent-http-client.xml
-rw-r--r--. 1 root root 10413 11月 21 2017 jkurrent-http.xml
-rw-r--r--. 1 root root 1172 11月 21 2017 jkurrentMain.xml
-rw-r--r--. 1 root root 1562 11月 21 2017 jkurrent-template.xml
-rw-r--r--. 1 root root 1937 11月 21 2017 jkurrent-ws.xml
-rw-r--r--. 1 root root 3958 11月 21 2017 logback.xml
drwxr-xr-x. 5 root root 77 11月 21 2017 process
drwxr-xr-x. 2 root root 4096 11月 23 2017 properties
drwxr-xr-x. 3 root root 16 11月 21 2017 validata
-rw-r--r--. 1 root root 967 11月 21 2017 validation-bean.xml
-rw-r--r--. 1 root root 3025 11月 21 2017 validation.xml
[root@fudmz2-fkapp1 classes]# cd /usr/local/tomcat/apache-tomcat-7.0.81/bin/
[root@fudmz2-fkapp1 bin]# sh shutdown.sh
Using CATALINA_BASE: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_HOME: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_TMPDIR: /usr/local/tomcat/apache-tomcat-7.0.81/temp
Using JRE_HOME: /usr
Using CLASSPATH: /usr/local/tomcat/apache-tomcat-7.0.81/bin/bootstrap.jar:/usr/local/tomcat/apache-tomcat-7.0.81/bin/tomcat-juli.jar
s[root@fudmz2-fkapp1 bin]# sh startup.sh
Using CATALINA_BASE: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_HOME: /usr/local/tomcat/apache-tomcat-7.0.81
Using CATALINA_TMPDIR: /usr/local/tomcat/apache-tomcat-7.0.81/temp
Using JRE_HOME: /usr
Using CLASSPATH: /usr/local/tomcat/apache-tomcat-7.0.81/bin/bootstrap.jar:/usr/local/tomcat/apache-tomcat-7.0.81/bin/tomcat-juli.jar
Tomcat started.
[root@fudmz2-fkapp1 bin]# ^C
[root@fudmz2-fkapp1 bin]# ^C
[root@fudmz2-fkapp1 bin]# ll
总用量 804
-rw-r--r--. 1 root root 28503 8月 11 2017 bootstrap.jar
-rw-r--r--. 1 root root 14028 8月 11 2017 catalina.bat
-rwxr-xr-x. 1 root root 21646 8月 11 2017 catalina.sh
-rw-r--r--. 1 root root 1647 8月 11 2017 catalina-tasks.xml
-rw-r--r--. 1 root root 24283 8月 11 2017 commons-daemon.jar
-rw-r--r--. 1 root root 204944 8月 11 2017 commons-daemon-native.tar.gz
-rw-r--r--. 1 root root 2040 8月 11 2017 configtest.bat
-rwxr-xr-x. 1 root root 1922 8月 11 2017 configtest.sh
-rwxr-xr-x. 1 root root 7888 8月 11 2017 daemon.sh
-rw-r--r--. 1 root root 2091 8月 11 2017 digest.bat
-rwxr-xr-x. 1 root root 1965 8月 11 2017 digest.sh
-rw-r--r--. 1 root root 3430 8月 11 2017 setclasspath.bat
-rwxr-xr-x. 1 root root 3547 8月 11 2017 setclasspath.sh
-rw-r--r--. 1 root root 2020 8月 11 2017 shutdown.bat
-rwxr-xr-x. 1 root root 1902 8月 11 2017 shutdown.sh
-rw-r--r--. 1 root root 2022 8月 11 2017 startup.bat
-rwxr-xr-x. 1 root root 1904 8月 11 2017 startup.sh
-rw-r--r--. 1 root root 44511 8月 11 2017 tomcat-juli.jar
-rw-r--r--. 1 root root 399836 8月 11 2017 tomcat-native.tar.gz
-rw-r--r--. 1 root root 4021 8月 11 2017 tool-wrapper.bat
-rwxr-xr-x. 1 root root 5024 8月 11 2017 tool-wrapper.sh
-rw-r--r--. 1 root root 2026 8月 11 2017 version.bat
-rwxr-xr-x. 1 root root 1908 8月 11 2017 version.sh
[root@fudmz2-fkapp1 bin]# pwd
/usr/local/tomcat/apache-tomcat-7.0.81/bin
三。修数。报销单删除后页面显示是草稿状态,实际系统后台已经是删除状态SX18071758254
删除数据库中的数据
NO.1
SQL:
###zhan
delete from t_cem_bill_maindata where billno = 'SX18071758254'
delete from t_cem_bill_payment where mainid = 'BI2018071700173210'
delete from t_cem_bill_payment where mainid = 'BI2018071700173204'
select * from t_cem_fund_payment where billcode = 'SX18071758254'
四。修数。发票代码 4400174320 发票号码 32779337 这个发票报销人关联单据后,删除单据了,但是发票变成已完成
更改数据库中的字段
NO.2
SQL:
###zhan
update T_CEM_INVOICE_MAINDATA set status= '0' where INVOICE_CODE= '4400174320' and INVOICE_NUM = '32779337'
五。修数。冯浩源创建SX18080267884报销单查询不到,而且申请人哪里带出的不是自己 。修改数据库中字段的属性即可
NO.7
SQL:
select creater,requser from t_cem_bill_maindata where billno = 'SX18080267884'
###zhan###先把创建人和申请人改为同一人,再把创建人、申请人的名字改为冯对应的员工表ID【对的】
select creater,requser from t_cem_bill_maindata where billno = 'SX18080267884'
select id from t_cem_sys_employee where name = '冯浩源'
update t_cem_bill_maindata set creater = 'SY2018071200135581' where billno = 'SX18080267884'
update t_cem_bill_maindata set requser = 'SY2018071200135581' where billno = 'SX18080267884'
###冯的我的单据中可以查询出来的报销单的信息,比对,按照能够查询出来的字段改 【createrdept createuserorg】
select createrdept, createuserorg from t_cem_bill_maindata where billno = 'SX18080267884'
select * from t_cem_bill_maindata where billno = 'SX18080267884' or billno = 'BX18072662974' or billno = 'SX18072360689' or billno = 'BX18081473601'
select * from t_cem_bill_maindata where creater = 'SY2018071200135581' and requser = 'SY2018071200135581'
update t_cem_bill_maindata set createrdept= 'SY2018010400079231' where billno = 'SX18080267884'
SY2017091800057255 ==> SY2018010400079231
update t_cem_bill_maindata set createuserorg ='SY2018010400079228' where billno = 'SX18080267884'
SY2017091800057221 ==> SY2018010400079228
update t_cem_bill_maindata set STS = 'Y' where billno = 'SX18080267884'
N ==>Y
update t_cem_bill_maindata set BILLSTATUS = '0' where billno = 'SX18080267884'
-1 ==>0
###zhan###先把创建人和申请人改为同一人,再把创建人、申请人的名字改为冯【错误的操作,错的】
update t_cem_bill_maindata set requser = 'SY2018040900101708' where billno = 'SX18080267884'
update t_cem_sys_employee set name = '冯浩源' where id = 'SY2018040900101708'
报销单创建人
select name from t_cem_sys_employee where id = 'SY2018040900101708'
报销单申请人、发起人
select name from t_cem_sys_employee where id = 'SY2018071200135581'
六。创建部门为空的供应商已维护并审核通过,但报销时选择不到
NO.3
###代码中对应的操作SQL语句 ,加了判空条件
SELECT S.ID, S.CORPID, S.NAME, S.CODE, S.TYPE, D2.NAME CREATBY_ORG, DECODE(S.TYPE, '000', '内部供应商', '001', '外部供应商') TYPENAME,
S.TAXPAYERTYPE, (SELECT DIC.VALUE FROM T_CEM_COMM_DICTIONARY DIC WHERE BATCHCODE = 'TAXPAYERTYPE' AND DIC.KEY = S.TAXPAYERTYPE) TAXPAYERTYPENAME,
S.TAXPAYERCODE, S.ACC_TYPE, DECODE(S.ACC_TYPE, '1', '对私', '0', '对公') ACC_TYPE_NAME,
S.DESCRIBE, S.STATUS, (SELECT DIC.VALUE FROM T_CEM_COMM_DICTIONARY DIC WHERE BATCHCODE = 'SUPPLIER_STATUS' AND DIC.KEY = S.STATUS) STATUS_NAME,
S.APPROVAL_OPINIONS, S.ENABLE, DECODE(S.ENABLE, '000', '启用', '001', '禁用') ENABLE_NAME, S.STS
, (SELECT EMP.NAME FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.ID = S.CREATERID) CREATERNAME, S.CREATETIME,
S.MODIFYID, (SELECT EMP.NAME FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.ID = S.MODIFYID) MODIFYNAME, S.MODIFYTIME,
(SELECT COUNT(1) FROM T_CEM_IMG_ATTACHMENT IA WHERE IA.CORPID = S.CORPID AND IA.BUSTYPE = '003' AND IA.BUSID = S.ID) FILECOUNT
FROM T_CEM_SYS_SUPPLIER S
left join T_CEM_SYS_EMPLOYEE E on E.ID =S.CREATERID
left join T_CEM_SYS_EMPREFDEPTPOST E2 on E.ID=E2.EMPLOYEEID AND E2.ISDEFAULT='Y'
left join T_CEM_SYS_DEPT D1 on E2.DEPTID=D1.ID
left join T_CEM_SYS_DEPT D2 on D1.PARENTID=D2.ID
left join T_CEM_SYS_SUPPLIER_AUTHOR A on S.ID=A.SUPPLIER_ID
WHERE (D2.NAME is null or D2.NAME = '') or S.code = 'W99157740353' or S.code = 'W99157799961' or S.code = 'W99157361656'
###有6个为空的,其中2个Y,4个N
select * from t_cem_sys_supplier where id ='SU00001783' or id = 'SU00001789' or id = 'SU00001779'or id = 'SU00001781'or id = 'SU00002863' or id = 'SU00002233'
select * from t_cem_sys_supplier where code ='W991200355031' or code = 'W991200106490' or code = 'W991200555095' or code = 'W991200675238' or code = 'W999100840171' or code = 'W999100691981'
Y Y
在报销单==》普通报销单,付款人==》收款人信息页面中,可以查询到的供应商编码
W99157740353 W99157799961 W99157361656
修改
1)供应商管理==》供应商维护
SELECT S.ID, S.CORPID, S.NAME, S.CODE, S.TYPE, D2.NAME CREATBY_ORG, DECODE(S.TYPE, '000', '内部供应商', '001', '外部供应商') TYPENAME,
S.TAXPAYERTYPE, (SELECT DIC.VALUE FROM T_CEM_COMM_DICTIONARY DIC WHERE BATCHCODE = 'TAXPAYERTYPE' AND DIC.KEY = S.TAXPAYERTYPE) TAXPAYERTYPENAME,
S.TAXPAYERCODE, S.ACC_TYPE, DECODE(S.ACC_TYPE, '1', '对私', '0', '对公') ACC_TYPE_NAME,
S.DESCRIBE, S.STATUS, (SELECT DIC.VALUE FROM T_CEM_COMM_DICTIONARY DIC WHERE BATCHCODE = 'SUPPLIER_STATUS' AND DIC.KEY = S.STATUS) STATUS_NAME,
S.APPROVAL_OPINIONS, S.ENABLE, DECODE(S.ENABLE, '000', '启用', '001', '禁用') ENABLE_NAME, S.STS,
S.CREATERID, (SELECT EMP.NAME FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.ID = S.CREATERID) CREATERNAME, S.CREATETIME,
S.MODIFYID, (SELECT EMP.NAME FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.ID = S.MODIFYID) MODIFYNAME, S.MODIFYTIME,
(SELECT COUNT(1) FROM T_CEM_IMG_ATTACHMENT IA WHERE IA.CORPID = S.CORPID AND IA.BUSTYPE = '003' AND IA.BUSID = S.ID) FILECOUNT
FROM T_CEM_SYS_SUPPLIER S
left join T_CEM_SYS_EMPLOYEE E on E.ID =S.CREATERID
left join T_CEM_SYS_EMPREFDEPTPOST E2 on E.ID=E2.EMPLOYEEID AND E2.ISDEFAULT='Y'
left join T_CEM_SYS_DEPT D1 on E2.DEPTID=D1.ID
left join T_CEM_SYS_DEPT D2 on D1.PARENTID=D2.ID
left join T_CEM_SYS_SUPPLIER_AUTHOR A on S.ID=A.SUPPLIER_ID
WHERE S.STS='Y' and (S.code ='W99157740353' or S.code='W99157790341' or S.code='W999100840171' )
分支1能够查出来的,不是1也能够查出来,感觉分支没区别
两个地方都能够查询出来的供应商编号 W99157740353 W99157799961
供应商管理==》供应商维护,能查出 W99157512698 W99157790341 以供应商名称,也能够查询出来
查不出 W999100840171 W999100691981
###zhan
修改的部分:
STATUS 1==>2 ,STATUS_NAME 审核中==》已审核, APPROVAL_OPINIONS 空==》同意
update T_CEM_SYS_SUPPLIER set STATUS= '2' where code = 'W999100840171' ###STATUS 1==>2 ,STATUS_NAME 审核中==》已审核
update T_CEM_SYS_SUPPLIER set APPROVAL_OPINIONS= '同意' where code = 'W999100840171'
W999100840171 数据库中有数据,而且改了过来
W999100691981
2)报销单==》普通报销单==》付款区==》供应商名称
分支上 员工的银行信息
分支下 供应商的银行信息
SELECT SP.ID SUPPLIER_ID, SP.NAME SUPPLIER_NAME, SP.CODE SUPPLIER_NO, SP.DESCRIBE, SP.TAXPAYERTYPE, SP.TAXPAYERCODE,
BI.BANKNAME PAYMENT_RECEIVEBANK, BI.BANKCODE PAYMENT_RECEIVEBANKCODE, BI.BANKCODE PAYMENT_CNAPSCODE, BI.BANKTYPECODE, BI.SADDRESS, BI.TYPENAME,
BI.CITYCODE, BI.CITYNAME PAYMENT_RECEIVECITY, BI.PROVINCECODE, BI.PROVINCENAME PAYMENT_RECEIVEPROVINCE, BI.AREACODE,
BA.ID PAYMENT_BANKACCOUNTID, BA.ACCOUNTNAME PAYMENT_RECEIVENAME, BA.BANKNAME PAYMENT_RECEIVEBANKBRANCH, BA.ISCARD,
BA.ISDEFAULT, BA.TYPE, BA.STS, BA.ENABLE, BA.PAYMETHOD, BA.PUBORPRI,SE.NAME CREATER_NAME,D2.NAME CREATBY_ORG
FROM T_CEM_COMM_BANKINFO BI, T_CEM_SYS_BANKACCOUNT BA, T_CEM_SYS_SUPPLIER SP, T_CEM_SYS_EMPLOYEE SE,T_CEM_SYS_EMPREFDEPTPOST EP,T_CEM_SYS_DEPT D1,T_CEM_SYS_DEPT D2 ,T_CEM_SYS_SUPPLIER_AUTHOR A
WHERE BI.BANKCODE = BA.BANKCODE
AND BA.REFID = SP.ID
AND SP.CREATERID = SE.ID
AND SE.ID=EP.EMPLOYEEID AND EP.ISDEFAULT='Y'
AND EP.DEPTID=D1.ID
AND D1.PARENTID=D2.ID
AND SP.ENABLE = '000'
AND SP.STS = 'Y'
AND BA.STS = 'Y'
AND BA.ENABLE = '000'
AND SP.ID=A.SUPPLIER_ID
W999100840171 数据库中没有数据
W999100691981 数据库中没有数据
自己新增了一个供应商W99157117451
自己加了几个供应商:
SELECT S.ID, S.CORPID, S.NAME, S.CODE, S.TYPE, D2.NAME CREATBY_ORG, DECODE(S.TYPE, '000', '内部供应商', '001', '外部供应商') TYPENAME,
S.TAXPAYERTYPE, (SELECT DIC.VALUE FROM T_CEM_COMM_DICTIONARY DIC WHERE BATCHCODE = 'TAXPAYERTYPE' AND DIC.KEY = S.TAXPAYERTYPE) TAXPAYERTYPENAME,
S.TAXPAYERCODE, S.ACC_TYPE, DECODE(S.ACC_TYPE, '1', '对私', '0', '对公') ACC_TYPE_NAME,
S.DESCRIBE, S.STATUS, (SELECT DIC.VALUE FROM T_CEM_COMM_DICTIONARY DIC WHERE BATCHCODE = 'SUPPLIER_STATUS' AND DIC.KEY = S.STATUS) STATUS_NAME,
S.APPROVAL_OPINIONS, S.ENABLE, DECODE(S.ENABLE, '000', '启用', '001', '禁用') ENABLE_NAME, S.STS,
S.CREATERID, (SELECT EMP.NAME FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.ID = S.CREATERID) CREATERNAME, S.CREATETIME,
S.MODIFYID, (SELECT EMP.NAME FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.ID = S.MODIFYID) MODIFYNAME, S.MODIFYTIME,
(SELECT COUNT(1) FROM T_CEM_IMG_ATTACHMENT IA WHERE IA.CORPID = S.CORPID AND IA.BUSTYPE = '003' AND IA.BUSID = S.ID) FILECOUNT
FROM T_CEM_SYS_SUPPLIER S
left join T_CEM_SYS_EMPLOYEE E on E.ID =S.CREATERID
left join T_CEM_SYS_EMPREFDEPTPOST E2 on E.ID=E2.EMPLOYEEID AND E2.ISDEFAULT='Y'
left join T_CEM_SYS_DEPT D1 on E2.DEPTID=D1.ID
left join T_CEM_SYS_DEPT D2 on D1.PARENTID=D2.ID
left join T_CEM_SYS_SUPPLIER_AUTHOR A on S.ID=A.SUPPLIER_ID
WHERE S.STS='Y' and (S.code ='W99157512698' or S.code='W99157117451' or S.code='W99157375587' or S.code = 'W999100840171')
最后or S.code = 'W999100840171' 对应的创建人是”肖晨“使用的账号是”冯浩源“是查不出来的,要使用”肖晨“的账号来验证
上面这句话是错的,能够查询出来的。
由创建人的名字,查询出创建人的人员工表中的id
SELECT EMP.ID FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.NAME = '肖晨' SY2017091900057280 s.createrid = 'SY2017091900057280'
用员工的id,即为人员部门岗位关联表中的EMPLOYEEID,查询出
SELECT * FROM T_CEM_SYS_EMPREFDEPTPOST WHERE EMPLOYEEID = 'SY2017091900057280'
其中DEPTID,
在供应商表中创建人的id和上面查询出来的创建人的id值是一样的,查询出供应商的信息,供应商的ID
SELECT * FROM T_CEM_SYS_SUPPLIER WHERE CREATERID = 'SY2017091900057280'
由供应商的ID,,查询出供应商权限表中的信息,
SELECT * FROM T_CEM_SYS_DEPT WHERE id = 'SY2017091900057280'
SELECT * FROM T_CEM_SYS_DEPT WHERE parentid = 'SY2017091900057280'
SELECT EMP.ID FROM T_CEM_SYS_EMPLOYEE EMP WHERE EMP.NAME = '肖晨'
3,为空的创建者为 肖然和黄静瑜。肖然的岗位不是默认的,是没有查询权限的,自然就查不出来。
而黄静瑜,在部门信息表中已经没有这个人了。
七。4,两个账号,tanggj cwglb 两个用户经常被禁用,人工启用后又变为禁用
SQL
客户给出的这两个账号是 T_CEM_SYS_EMPLOYEE 中的NO工号
select * from T_CEM_SYS_EMPLOYEE where NO = 'tanggj' AU2018042800018934
select * from T_CEM_SYS_EMPLOYEE where NO = 'cwglb' AU2017091900017781
select * from t_Cem_Rbac_Account where id = 'AU2018042800018934' or id = 'AU2017091900017781'
select is_enabled from t_Cem_Rbac_Account where id = 'AU2018042800018934' or id = 'AU2017091900017781'
数据库中的字段 is_enabled 是正确的
八。CLBX10011502619报销单最后两个节点是同一人,审批后单据状态仍为审批中
此报销单,在数据库中查询不到。给的单据号是不对的。客户给错了单据号,CLBX18011502619,
这是一个2月份的单子,客户要求占用和凭证都方到8月份
#########################################################################################
SQL
SELECT DD.ID, DD.CORPID, DD.MAINID, DD.NO, DD.REQUSER, DD.REQUSERID, FN_GET_COL_LABEL('DETAIL_REQORG', DD.REQORG, DD.CORPID) REQORG,
FN_GET_COL_LABEL('DETAIL_DIMACCOUNT', DD.DIMACCOUNT, DD.CORPID) DIMACCOUNT, FN_GET_COL_LABEL('DETAIL_SUBJECT', DD.SUBJECT, DD.CORPID) SUBJECT,
FN_GET_COL_LABEL('DETAIL_EXPENSETYPE', DD.EXPENSETYPE, DD.CORPID) EXPENSETYPE, FN_GET_COL_LABEL('DETAIL_INVOICETYPE', DD.INVOICETYPE, DD.CORPID) INVOICETYPE,
DD.ORIGINALCURRENCY, DD.ORIGINALCURRENCYSUM, DD.ORIGINALCURRENCYUPPER, DD.EXCHANGERATE, DD.BASECURRENCY,
DD.BASECURRENCYUPPER, DD.DEDUCTIONSCALE, DD.DEDUCTIONBUDGET, FN_GET_COL_LABEL('DETAIL_DZNO', DD.DZNO, DD.CORPID) DZNO, DD.DZBILLDETAILID, DD.SURPLUSAMOUNT,
FN_GET_COL_LABEL('DETAIL_LENDCAUSE', DD.LENDCAUSE, DD.CORPID) LENDCAUSE, DD.REQREPAYDATE,
FN_GET_COL_LABEL('DETAIL_REPAYCAUSE', DD.REPAYCAUSE, DD.CORPID) REPAYCAUSE, DD.SUPPLEMENTAMOUNT,
DD.RESERVEAMOUNT, FN_GET_COL_LABEL('DETAIL_ORDERTYPE', DD.DETAIL_ORDERTYPE, DD.CORPID) DETAIL_ORDERTYPE, DD.REMARK, DD.SUMMARY,
FN_GET_COL_LABEL('DETAIL_ITEM01', DD.ITEM01, DD.CORPID) ITEM01, FN_GET_COL_LABEL('DETAIL_ITEM02', DD.ITEM02, DD.CORPID) ITEM02,
FN_GET_COL_LABEL('DETAIL_ITEM03', DD.ITEM03, DD.CORPID) ITEM03, FN_GET_COL_LABEL('DETAIL_ITEM04', DD.ITEM04, DD.CORPID) ITEM04,
FN_GET_COL_LABEL('DETAIL_ITEM05', DD.ITEM05, DD.CORPID) ITEM05, FN_GET_COL_LABEL('DETAIL_ITEM06', DD.ITEM06, DD.CORPID) ITEM06,
FN_GET_COL_LABEL('DETAIL_ITEM07', DD.ITEM07, DD.CORPID) ITEM07, FN_GET_COL_LABEL('DETAIL_ITEM08', DD.ITEM08, DD.CORPID) ITEM08,
FN_GET_COL_LABEL('DETAIL_ITEM09', DD.ITEM09, DD.CORPID) ITEM09, FN_GET_COL_LABEL('DETAIL_ITEM10', DD.ITEM10, DD.CORPID) ITEM10,
FN_GET_COL_LABEL('DETAIL_ITEM11', DD.ITEM11, DD.CORPID) ITEM11, FN_GET_COL_LABEL('DETAIL_ITEM12', DD.ITEM12, DD.CORPID) ITEM12,
FN_GET_COL_LABEL('DETAIL_ITEM13', DD.ITEM13, DD.CORPID) ITEM13, FN_GET_COL_LABEL('DETAIL_ITEM14', DD.ITEM14, DD.CORPID) ITEM14,
FN_GET_COL_LABEL('DETAIL_ITEM15', DD.ITEM15, DD.CORPID) ITEM15, FN_GET_COL_LABEL('DETAIL_ITEM16', DD.ITEM16, DD.CORPID) ITEM16,
FN_GET_COL_LABEL('DETAIL_ITEM17', DD.ITEM17, DD.CORPID) ITEM17, FN_GET_COL_LABEL('DETAIL_ITEM18', DD.ITEM18, DD.CORPID) ITEM18,
FN_GET_COL_LABEL('DETAIL_ITEM19', DD.ITEM19, DD.CORPID) ITEM19, FN_GET_COL_LABEL('DETAIL_ITEM20', DD.ITEM20, DD.CORPID) ITEM20,
DD.ISSHARE, DD.STRUCTUREID, DD.ABATEBUDGETTIME, DD.STS, DD.FINISHFLAG, DD.FINISHUSERID, DD.FINISHTIME,
DD.BRINGUPUSERID, DD.BRINGUPFLAG, DD.BRINGUPTIME, 'update' OPERATION, DD.CREATETIME, DD.CREATEBY, DD.MODIFYTIME, DD.MODIFYBY,
DECODE(DD.DZNO, NULL, DD.ORIGINALCURRENCYSUM, (SELECT BD.BALANCE_AMOUNT FROM T_CEM_BILL_BALANCEDATA BD WHERE BD.BILLTYPE = '1' AND BD.BALANCETYPE = 'BUD_BX' AND BD.BILL_NO = DD.DZNO AND BD.CHILD_ID = DD.DZBILLDETAILID)) BALANCE_AMOUNT,
FN_GET_COL_LABEL('DETAIL_CONTRACT_ID', DD.CONTRACT_ID, DD.CORPID) CONTRACT_ID,
FN_GET_COL_LABEL('DETAIL_INVOICE_DETAIL_ID', DD.INVOICE_DETAIL_ID, DD.CORPID) INVOICE_DETAIL_ID
FROM T_CEM_BILL_DETAILDATA DD
WHERE NVL(DD.STS, 'Y') = 'Y'
AND DD.MAINID = 'BI2018011500108689'
BU2017120400004545 1976.00
#########################################################################################
九。CLBX18030912715报销单审批结束后付款信息没有推送至支付接口(账号为手工输入)
这个谭秋勤的CLBX18030912715这个单据,银行名称和开户行是对应不上的,
银行名称是‘’浦发银行佛山禅城支行‘’而开户行却为‘’中国银行股份有限公司佛山南海西樵支行‘’
可能是手工输入的时候输错了。
这个需要帮忙确认下正确的信息是什么,只有信息正确系统才能将付款信息推送至支付接口
修数,上图中红色标注的两个全部改为“上海浦东发展银行股份有限公司佛山禅城支行”
select * from t_cem_bill_maindata t where t.billno = 'CLBX18030912715' -- 'BI2018030900120636'
select * from t_cem_bill_payment t where t.mainid = 'BI2018030900120636' -- id:'BI2018030900120638' --BANKACCOUNTID:SY2018030500090836 --receivebankbranch: --104588029090
select * from t_cem_sys_bankaccount t where t.id = 'SY2018030500090836'
select * from t_cem_comm_bankinfo t where t.bankname = '上海浦东发展银行股份有限公司佛山禅城支行'
--中国银行股份有限公司佛山南海西樵支行 浦发银行佛山禅城支行 上海浦东发展银行股份有限公司佛山禅城支行
select * from t_cem_comm_bankinfo t where t.bankcode = '104588029090' --update 104588029090==> 310588000038
select bankcode from t_cem_comm_bankinfo t where t.bankname = '上海浦东发展银行股份有限公司佛山禅城支行' --310588000038
bankname=上海浦东发展银行股份有限公司佛山禅城支行 bankaddr
select * from t_cem_comm_bankinfo t where t.bankname = '上海浦东发展银行股份有限公司佛山禅城支行' -- t.receivebankbranch = cb.bankname
update t_cem_bill_payment t set receivebankbranch = '上海浦东发展银行股份有限公司佛山禅城支行' where t.mainid = 'BI2018030900120636' --update 浦发银行佛山禅城支行==>上海浦东发展银行股份有限公司佛山禅城支行
select bankcode from t_cem_comm_bankinfo t where t.bankname = '上海浦东发展银行股份有限公司佛山禅城支行' --310588000038
select * from t_cem_comm_bankinfo t where t.bankcode = '310588000038'
select * from t_cem_bill_payment t where t.mainid = 'BI2018030900120636' --update cnapscode 104588029090==> 310588000038
update t_cem_bill_payment t set cnapscode = '310588000038' where t.mainid = 'BI2018030900120636'
#############################################################
/** ******************** 流程结束后-业务操作 begin **************************************************************************************************** **/
/**
* 流程审批结束后业务操作
* @param main_id 单据主表ID
* @param userInfo 当前认证用户信息
*/
public void workFlowEndBusiness(String main_id, UserInfo userInfo) {
/** 更新单据状态为“审批结束” **/
this.updateBillStatus(main_id, BillConstant.approveEnd, userInfo);
/** 获得单据信息 **/
Map<String, Object> billInfoMap = this.getBillInfoByMainId(main_id, userInfo.getCorpid());
String billType = (String)billInfoMap.get("billType"); // 单据类型 1申请单 3报销单 4借款单 5还款单 9差旅申请单 10差旅报销单 12差旅结算单 13事项申请单
String budget_occupy = (String)billInfoMap.get("budget_occupy"); // 预算占用标志
/** 实际预算 **/
this.getBillBudgetService().wfEndBudget(billType, main_id, budget_occupy, userInfo.getCorpid());
/** 修改单据“红冲状态” **/
/** 差旅结算报销单,更新订单表的结算状态 **/
/** 更新行程规划单 **/
/** 往资金系统发送数据,进行支付 **/
this.getFundInterfaceManagerService().generatePaymentInterfaceInfo(main_id, userInfo.getCorpid());
/**
* 同步付款区中的信息至资金接口表
*/
//this.getPaymentPayService().synPaymentInfoToFund(main_id,userInfo);
//清除生成的红冲单据 凭证标记 add by YouGuoqing 2017/11/22
sqlMap.update("billBusinessSqlMap.updateRedBilldataVoucherFlag", main_id);
/** 调用生成凭证的存储过程 **/
Map<String, Object> vouherParam = new HashMap<String, Object>();
vouherParam.put("main_id", main_id);
sqlMap.queryForList("billBusinessSqlMap.createVoucher", vouherParam);
}
/** ******************** 流程结束后-业务操作 end **************************************************************************************************** **/
public class FundInterfaceManageService extends BaseService {
protected static Logger logger = LoggerFactory.getLogger(PaymentPayService.class);
/**
* @author chengong 生成支付信息
* @param billMainId 单据id
* @param corpId 企业id
*/
public void generatePaymentInterfaceInfo(String billMainId, String corpId) {
try {
// 调用存储过程往支付信息表中插入支付数据
Map<String, Object> param = new HashMap<String, Object>();
param.put("billMainId", billMainId);
param.put("corpId", corpId);
param.put("paymentId", "");
sqlMap.queryForObject("fundInterfaceManage.tranferPaymentDataToFundInterface", param);
// 更改资金接口表支付类型 FUNDFLAG 1 资金;0 网银
//this.updateFundFlag(param);//由于复星只有网银,暂时在调用存储过程时直接写成网银
} catch (Exception e) {
//throw e;
logger.info("----------------生成支付数据失败---------------------------------------------");
}
}
<!-- 生成支付数据-->
<select id="tranferPaymentDataToFundInterface" statementType="CALLABLE" parameterType="map">
CALL P_CEM_FUND.SYNCHRON_BILL_PAYMENT_INFO(#{billMainId, jdbcType=VARCHAR, mode=IN},
#{paymentId, jdbcType=VARCHAR, mode=IN})
</select>
<!-- 同步付款区信息至资金接口表 -->
<insert id="synchronPaymentInfo" statementType="CALLABLE" parameterType="map">
<![CDATA[
{CALL P_CEM_FUND.SYNCHRON_BILL_PAYMENT_INFO(
#{billNo, jdbcType=VARCHAR, mode=IN},
#{paymentId, jdbcType=VARCHAR, mode=IN}
)}
]]>
</insert>
查看存储过程:
在PL/SQL 中,打开新SQL窗口。P_CEM_FUND.SYNCHRON_BILL_PAYMENT_INFO,将此内容复制到新开SQL窗口中
选中P_CEM_FUND,右键,查看说明与体,查看存储过程中具体的SQL语句
#############################################################
十。5、6月份费控系统从总账回写的凭证号码有问题
查询分析 ==》 凭证报表==》查询==》voucherReportPage.ajax
#############################################################
@王磊 王老师,5、6月份费控系统从总账回写的凭证号码有问题,上次问你是哪个凭证号码有问题,然后你说这两个月份的都有问题。在数据库中查了下,凭证号码和总账接口表中的数据是一致的。我想问下你说的问题是指啥,是啥问题???
王磊 所有凭证号码都不对 你们去的总账接口表不对
煜℡9527:
LI_MOFANG@CEMREFLI_PRO
煜℡9527:
这个表?
(sjm陆凯杰)jay:
总账接口表是这个吧?
(sjm陆凯杰)jay:
我们和这个表数据是一致的,这个表的凭证编号是总账回写的
王磊:
@侯莹莹-复联 @ Nicole 帮忙确认下接口
YOYO:
我看接口文档里是这么写的
#############################################################
----------------------------------------我是低调的分隔线--------------------------------------------