1.背景:物流方写了一个过程,我方读取过程并执行将返回的结果显示在nc界面。
2.效果:
3.代码:
public String executeprodue(String vbill) throws BusinessException;//接口里面的代码,下面是实现类代码
public String executeprodue(String vbill) throws BusinessException {
DbjhqExeSql exesql = new DbjhqExeSql();
String msg = exesql.exeProcedure(vbill);
return msg;
}
/*******************************************************************************
* Copyright (c) 2013, 2015 YONYOU Corporation and others.
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the XHFX
*
* Contributors:
* yonyou Corporation - shijiazhuang corp
*******************************************************************************/
/**
* @Title: DbjhqExeSql.java
* @Package nc.impl.xhfx.dbjhq
* @Description: TODO(交换区数据库sql执行)
* @author liljb@yonyou.com
* @date 2014-5-19 下午02:34:40
* @version V1.0
*/
package nc.impl.xhfx.dbjhq;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import nc.vo.pub.BusinessException;
import nc.vo.xhfx.pub.Toolkit;
import oracle.jdbc.OracleTypes;
/**
* @ClassName: DbjhqExeSql
* @Description: TODO(这里用一句话描述这个类的作用)
* @author liljb@yonyou.com
* @date 2014-5-19 下午02:34:40
*
*/
public class DbjhqExeSql {
/**
*
* <p>
*
* <p>
* 创建人: 李林君
* <p>
* 创建日期: 2014年5月16日
* <p>
*
* @param dhfh
* @param vbillno
* @return
* @throws SQLException
* @throws BusinessException
* @see nc.itf.xhfx.pub.IdbjhqService#exeProcedure(java.lang.String, java.lang.String)
*/
public String exeProcedure(String s) throws BusinessException {
String ss = s.toString();
Connection con = null;
String msg = "";
try {
con = getConnect();
String dhfh = ss.substring(0, 2);
String vbillno = ss.replace(dhfh, "");
CallableStatement c;
if (dhfh.equals("fh")) {
// 创建存储过程的对象
try {
c = con.prepareCall("{call dbjhq.proc_check_fh(?,?,?,?)}"); //$NON-NLS-1$
c.setString(1, vbillno);// v_dh char,第一个参数作为条件
c.registerOutParameter(2, OracleTypes.VARCHAR);// v_zt out Varchar2, 下面3个参数作为输出
c.registerOutParameter(3, OracleTypes.INTEGER);// errcode Out Number,
c.registerOutParameter(4, OracleTypes.VARCHAR);// errtext Out Varchar2
c.execute();
@SuppressWarnings("unused")
ResultSet rs = c.getResultSet();
msg = c.getString(2);
if (Toolkit.isEmpty(c.getString(2))) {
msg = "没找到";
}
} catch (SQLException e) {
e.printStackTrace();
}
} else {
// 创建存储过程的对象
try {
c = con.prepareCall("{call dbjhq.proc_check_dh(?,?,?,?)}"); //$NON-NLS-1$
c.setString(1, vbillno);// v_dh char,
c.registerOutParameter(2, OracleTypes.VARCHAR);// v_zt out Varchar2,
c.registerOutParameter(3, OracleTypes.INTEGER);// errcode Out Number,
c.registerOutParameter(4, OracleTypes.VARCHAR);// errtext Out Varchar2
c.execute();
@SuppressWarnings("unused")
ResultSet rs = c.getResultSet();
msg = c.getString(2);
if (Toolkit.isEmpty(c.getString(2))) {
msg = "没找到";
}
} catch (SQLException e) {
e.printStackTrace();
}
}
} finally {
try {
if (con != null) {
con.close();
}
} catch (Exception e2) {
}
}
return "fh" + msg;
}
String orclURL = "jdbc:oracle:thin:@xxxxxxxx:1521:hbpxwl"; //$NON-NLS-1$
String driverClassName = "oracle.jdbc.driver.OracleDriver"; //$NON-NLS-1$
String usrName = "xxx"; //$NON-NLS-1$
String pwd = "xxx"; //$NON-NLS-1$
Connection connect = null;
public Connection getConnect() {
try {
connect = DriverManager.getConnection(orclURL, usrName, pwd);
} catch (SQLException e) {
e.printStackTrace();
}
return connect;
}
}
package nc.ui.hn.hn307504;
import java.awt.Color;
import javax.swing.BorderFactory;
import nc.bs.framework.common.NCLocator;
import nc.itf.xhfx.pub.IWLService;
import nc.ui.pub.ButtonObject;
import nc.ui.pub.ToftPanel;
import nc.vo.hn.wl.fillthehole.Toolkit;
import nc.vo.pub.BusinessException;
/**
*
*
* <p>
*
* <p>
* 创建人: 李林君
* <p>
* 创建日期: 2014年5月14日
* <p>
*
* @version 1.0
* @since NC5.7
*/
public class CXfhztUI extends ToftPanel {
// 公司和操作员
String m_sCorpID = null;
String m_sUserID = null;
String msg = "status";
public CXfhztUI() throws BusinessException {
super();
initialize();
}
@Override
public String getTitle() {
return "查询发货状态";
}
/**
*
*
* <p>
* 按钮: 发货查询,收货查询
* <p>
* 创建人: 李林君
* <p>
* 创建日期: 2014年5月19日
* <p>
*
* @param bo
* @see nc.ui.pub.ToftPanel#onButtonClicked(nc.ui.pub.ButtonObject)
*/
@Override
public void onButtonClicked(ButtonObject bo) {
if (bo.equals(m_querystatus)) {
try {
onquerystatusfh();
} catch (Exception e) {
e.printStackTrace();
}
} else {
try {
onquerystatusdh();
} catch (BusinessException e) {
e.printStackTrace();
}
}
}
private void onquerystatusdh() throws BusinessException {
String vbillno = UItextFH.getText();
if (Toolkit.isEmpty(vbillno)) {
showErrorMessage("没输入内容");
}
IWLService services = NCLocator.getInstance().lookup(IWLService.class);
String ob = new String("dh" + vbillno);
String msg = services.executeprodue(ob);
JLstatus.setText(msg);
}
private void onquerystatusfh() throws Exception {
String vbillno = UItextFH.getText();
if (Toolkit.isEmpty(vbillno)) {
showErrorMessage("没输入内容");
}
IWLService services = NCLocator.getInstance().lookup(IWLService.class);
String ob = new String("fh" + vbillno);
String msg = services.executeprodue(ob);
JLstatus.setText(msg);
}
// 状态查询按钮
private ButtonObject m_querystatus = new ButtonObject("查询发货状态");
private ButtonObject m_querystatus2 = new ButtonObject("查询到货状态");
// 按钮组
private ButtonObject[] m_MainButtonGroup = { m_querystatus, m_querystatus2, };
private void initialize() throws BusinessException {
try {
setName("ClientUI");
setLayout(null);
setSize(774, 419);
getCeInfo();
setButtons(m_MainButtonGroup);
add(getUILbCondition());
add(getUILbConditionDH());
add(getUItextFH());
add(getUItextDH());
} catch (Exception e) {
throw new BusinessException("初始化出错");
}
}
private nc.ui.pub.beans.UILabel JLstatus = null;
private nc.ui.pub.beans.UILabel getUILbCondition() {
if (JLstatus == null) {
try {
JLstatus = new nc.ui.pub.beans.UILabel();
JLstatus.setName("JLstatus");
JLstatus.setText("发货状态");
JLstatus.setBorder(BorderFactory.createLineBorder(Color.red));
JLstatus.setBounds(250, 40, 200, 22);
} catch (java.lang.Throwable ivjExc) {
handleException(ivjExc);
}
}
return JLstatus;
}
private nc.ui.pub.beans.UILabel JLstatusDH = null;
private nc.ui.pub.beans.UILabel getUILbConditionDH() {
if (JLstatusDH == null) {
try {
JLstatusDH = new nc.ui.pub.beans.UILabel();
JLstatusDH.setName("JLstatusDH");
JLstatusDH.setText("到货状态");
JLstatusDH.setBorder(BorderFactory.createLineBorder(Color.red));
JLstatusDH.setBounds(250, 80, 200, 22);
} catch (java.lang.Throwable ivjExc) {
handleException(ivjExc);
}
}
return JLstatusDH;
}
private nc.ui.pub.beans.UITextArea UItextFH = null;
private nc.ui.pub.beans.UITextArea getUItextFH() {
if (UItextFH == null) {
try {
UItextFH = new nc.ui.pub.beans.UITextArea();
UItextFH.setName("UItextFH");
UItextFH.setText("请输入单号");
UItextFH.setBounds(30, 80, 200, 22);
} catch (java.lang.Throwable ivjExc) {
handleException(ivjExc);
}
}
return UItextFH;
}
private nc.ui.pub.beans.UITextArea UItextDH = null;
private nc.ui.pub.beans.UITextArea getUItextDH() {
if (UItextDH == null) {
try {
UItextDH = new nc.ui.pub.beans.UITextArea();
UItextDH.setName("UItextDH");
UItextDH.setText("请输入单号");
UItextDH.setBounds(30, 40, 200, 22);
} catch (java.lang.Throwable ivjExc) {
handleException(ivjExc);
}
}
return UItextDH;
}
public static void main(String[] args) {
try {
javax.swing.JFrame frame = new javax.swing.JFrame();
CXfhztUI aClientUI;
aClientUI = new CXfhztUI();
frame.setContentPane(aClientUI);
frame.setSize(aClientUI.getSize());
frame.addWindowListener(new java.awt.event.WindowAdapter() {
public void windowClosing(java.awt.event.WindowEvent e) {
System.exit(0);
};
});
frame.show();
java.awt.Insets insets = frame.getInsets();
frame.setSize(frame.getWidth() + insets.left + insets.right, frame.getHeight() + insets.top + insets.bottom);
frame.setVisible(true);
} catch (Throwable exception) {
nc.vo.scm.pub.SCMEnv.out("nc.ui.pub.ToftPanel 的 main() 中发生异常"/*
* -= notranslate =-
*/);
nc.vo.scm.pub.SCMEnv.error(exception);
}
}
/**
* 每当部件抛出异常时被调用
* <p>
* 创建人: 李林君
* <p>
* 创建日期: 2014年5月14日
* <p>
*
* @param exception
*/
private void handleException(java.lang.Throwable exception) {
}
public void getCeInfo() {
// 返回单位编码的pk
nc.ui.pub.ClientEnvironment ce = nc.ui.pub.ClientEnvironment.getInstance();
try {
m_sCorpID = ce.getCorporation().getPrimaryKey();
m_sUserID = ce.getUser().getPrimaryKey();
} catch (Exception e) {
showErrorMessage(e.getMessage());
}
return;
}
}
附存储过程:
CREATE OR REPLACE PROCEDURE "DBJHQ"."PROC_CHECK_FH"(
v_dh char,
v_zt out Varchar2,
errcode Out Number,
errtext Out Varchar2) Is
v_rows Number;
l_num Number;
ls_sts Char(1);
ls_errtext Varchar2(2000);
ls_sl_flowid_dj t_sl_dhgz_hz.sl_flowid_dj%Type;
ls_sl_flowid_mx t_sl_dhgz_mx.sl_flowid_mx%Type;
lde_wl_flowid_dj Number;
ls_gzlx Char(1);
ls_sl_gys_code_gz Char(20);
ls_wl_gys_code_gz t_ghdw_dz.wl_gys_code%Type;
l_num1 Number;
--物流向商流传发货、销退处理状态
Begin
if substr(v_dh,0,1)='0' then
select count(*) INTO V_ROWS from t_fjrwb@c_link_dbwl where ysxm=v_dh;
if v_rows>0 then
v_zt := '该单据物流已接受,正在分流中或者等待分流。';
return;
end if;
select count(*) INTO V_ROWS from t_fjrwb_bak1@c_link_dbwl where ysxm=v_dh;
if v_rows>0 then
v_zt := '该单据物流已分流完毕。';
return;
end if;
end if;
select count(*) into v_rows from t_fhrwb@c_link_dbwl where ysdjh=v_dh;
if v_rows>0 then
v_zt := '该单据物流已接受,等待调度。';
return;
end if;
select count(*) into v_rows from t_fhrwb_bak@c_link_dbwl where ysdjh=v_dh;
if v_rows=0 then
v_zt:='物流未接收此单据。';
return;
end if;
select count(*) into v_rows from t_agv_rw_xt@c_link_dbwl where ph=v_dh;
if v_rows>0 then
v_zt:='该单据正在下架。';
return;
end if;
select count(*) into v_rows from t_agv_rw_xt_bak@c_link_dbwl where ph=v_dh and jhcs is null;
if v_rows>0 then
v_zt:='该单据已下完架,等待校核。';
return;
end if;
select count(*) into v_rows from t_agv_rw_xt_bak@c_link_dbwl where ph=v_dh and fyrq is null;
if v_rows>0 then
v_zt:='该单据等待发运。';
return;
else
v_zt:='该单据已发运。';
end if;
-- v_zt:='adfadf';
errcode := 0;
errtext := 'ok';
Exception
When Others Then
Rollback;
ls_errtext := 'proc_check_dh=' ||Sqlerrm;
errcode := -1;
errtext := ls_errtext;
Commit;
End proc_check_fh;