📄 wdscyhsxxdao.java
字号:
} else {
//成批取数据
sql =
"SELECT A.NSRNBM, A.ZB_DM, A.ZBZ, " +/* A.LR_SJ,A.LRRY_DM, A.SJLY,*/ " A.HH " +
" FROM (SELECT ROWNUM BROWNUM,NSRNBM FROM db_ssgly.T_WDSC_ETL_LST ORDER BY NSRNBM) B, db_ssgly.T_ZBGL_ZBSJ A " + //--_ZBGL_ETL_LST B
" WHERE B.NSRNBM = A.NSRNBM AND B.BROWNUM >= ? " +
" AND B.BROWNUM <= ? " +
" AND A.ZB_DM IS NOT NULL AND HH>0 " +
" ORDER BY B.NSRNBM, A.ZB_DM, A.HH ";
System.out.println("param:begin[" + String.valueOf(begin) + "], end[" + String.valueOf(end) + "]");
preStmt = conn.prepareStatement(sql);
preStmt.setInt(1, begin);
preStmt.setInt(2, end);
System.out.println(sql);
long t_s = Calendar.getInstance().getTimeInMillis();
ResultSet rs = preStmt.executeQuery();
long t_e = Calendar.getInstance().getTimeInMillis();
System.out.println("取数耗时：" + String.valueOf(t_e - t_s));
////////////////////////////
//数据库取数
double yhsxxNsrnbm = 0, yhsxxHh = 0; //纳税人内部码，行号
String yhsxxZbdm = ""; //指标代码
String yhsxxZbz = ""; //指标值
//临时变量
Hashtable map = null; //存放纳税人 内部码 和 指标信息
int hh = 0, iTemp = 0, iMsg = 0; //计数器
double dNsrnbm = 0, oldNsrnbm = 0, dHh = 0;
String sZb = "", sZbz = "", oldZb = ""; //指标代码指标值
Vector vZbMsg = null; //指标值存放
BigDecimal bg = null;
////////////////////////////
t_s = Calendar.getInstance().getTimeInMillis();
long total = 0, t_ss = 0, t_ee = 0;
while (rs.next()) {
t_ss = Calendar.getInstance().getTimeInMillis();
yhsxxNsrnbm = rs.getDouble("NSRNBM");
yhsxxZbdm = rs.getString("ZB_DM");
yhsxxZbz = rs.getString("ZBZ");
yhsxxHh = rs.getDouble("HH");
dNsrnbm = yhsxxNsrnbm;
sZb = yhsxxZbdm;
sZb = ((sZb==null)?"":sZb.trim());
sZbz = yhsxxZbz;
sZbz = ((sZbz == null) ? "" : sZbz.trim());
if (dNsrnbm != oldNsrnbm) { //下一个纳税人一户式信息
if (map != null) {
map.put(oldZb, vZbMsg);
vector.add(map);
}
oldNsrnbm = dNsrnbm;
map = new Hashtable();
oldZb = sZb;
bg = new BigDecimal(dNsrnbm);
map.put(WDSCconstant.NSRNBM, bg.toString());
vZbMsg = new Vector();
dHh = yhsxxHh;
iTemp = new Double(dHh).intValue() - 1;
for (hh = 0; hh < iTemp; hh++) {
vZbMsg.add(" ");
}
vZbMsg.add(sZbz.equals("") ? " " : sZbz);
} else {
if (sZb.equals(oldZb)) { //同一个纳税人信息的同一个指标信息
dHh = yhsxxHh;
iTemp = new Double(dHh).intValue() - 1;
iMsg = vZbMsg.size();
for (hh = iMsg; hh < iTemp; hh++) {
vZbMsg.add(" ");
}
vZbMsg.add(sZbz.equals("") ? " " : sZbz);
} else { //同一个纳税人信息的不同指标信息
map.put(oldZb, vZbMsg);
vZbMsg = new Vector();
dHh = yhsxxHh;
iTemp = new Double(dHh).intValue() - 1;
for (hh = 0; hh < iTemp; hh++) {
vZbMsg.add(" ");
}
vZbMsg.add(sZbz.equals("") ? " " : sZbz);
oldZb = sZb;
}
}
t_ee = Calendar.getInstance().getTimeInMillis();
total += t_ee - t_ss;
}
//增加最后数据到v中
if (map != null) {
map.put(sZb, vZbMsg);
vector.add(map);
}
t_e = Calendar.getInstance().getTimeInMillis();
System.out.println("加工数据耗时：" + String.valueOf(t_e - t_s) +
", 其中:" + String.valueOf(total));
rs.close();
preStmt.close();
}
} catch (Exception e) {
e.printStackTrace();
throw new Exception("YCWDSC21013"); //获取纳税人指标信息(通过列表)失败e.getMessage());
} finally {
if (conn != null) conn.close();
}
return vector;
}
//成批删除t_wdsc_wd中的数据，然后插入数据
private int deleteSomeWd(Connection conn, int begin, int end) throws
SQLException {
String sql = "";
PreparedStatement preStmt = null;
//从表t_wdsc_wd中删除选中数据
sql = " DELETE T_WDSC_WD WHERE WD_MC IN " +
" ( " +
" SELECT A.WD_MC FROM (SELECT ROWNUM BROWNUM,NSRNBM FROM db_ssgly.T_WDSC_ETL_LST ORDER BY NSRNBM) B, t_wdsc_wd A " +
" WHERE A.WD_MC LIKE TO_CHAR(B.NSRNBM)||'%' AND " +
" B.BROWNUM >= ? AND B.BROWNUM <= ? " +
" ) ";
try {
preStmt = conn.prepareStatement(sql);
preStmt.setInt(1, begin);
preStmt.setInt(2, end);
preStmt.execute();
preStmt.close();
} catch (SQLException e) {
System.out.println("deleteSomeWd() fail! " + e.getMessage());
throw new SQLException(e.getMessage());
}
return 0;
}
private int insertSomeWd(Connection conn, int begin, int end, String nyr) throws
SQLException {
String sql = "";
int count = 0;
PreparedStatement preStmt = null;
//成批插入数据到 t_wdsc_wd 中
sql =
" INSERT INTO T_WDSC_WD " + // ( WD_MC, WD_SM, WD_NR, WDSC_SJ, WDXG_SJ, MB_BM, CZY_BM, SWJG " +
//" ) VALUES ( " +
" ( " +
" SELECT TO_CHAR(B.NSRNBM)||'" + nyr + "', '"+ this.getM_wdsm() +"',EMPTY_BLOB(), sysdate, sysdate, " +
" '"+ this.getM_mbbm() +"', '" + this.getM_czybm() +"', '"+ this.getM_swjg() +"', B.NSRNBM FROM (SELECT ROWNUM BROWNUM,NSRNBM FROM db_ssgly.T_WDSC_ETL_LST ORDER BY NSRNBM) B " +
" WHERE B.BROWNUM >=? AND B.BROWNUM <=? " +
" ) ";
System.out.println(sql + "\n begin=" + String.valueOf(begin) + " end=" + String.valueOf(end));
try {
preStmt = conn.prepareStatement(sql);
preStmt.setInt(1, begin);
preStmt.setInt(2, end);
preStmt.execute();
preStmt.close();
sql = " SELECT count(*) FROM (SELECT ROWNUM BROWNUM,NSRNBM FROM db_ssgly.T_WDSC_ETL_LST ORDER BY NSRNBM) B " +
" WHERE B.BROWNUM >=? AND B.BROWNUM <=? ";
preStmt = conn.prepareCall(sql);
preStmt.setInt(1, begin);
preStmt.setInt(2, end);
ResultSet rs = preStmt.executeQuery();
if (rs.next()) {
count = rs.getInt(1);
}
preStmt.close();
} catch (SQLException e) {
System.out.println("insertSomeWd() fail! " + e.getMessage());
throw new SQLException(e.getMessage());
}
return count;
}
private int deleteSingleWd(Connection conn, String nsrnbm) throws
SQLException {
String sql = "";
PreparedStatement preStmt = null;
//从表t_wdsc_wd中删除选中数据
sql = " DELETE T_WDSC_WD WHERE WD_MC like '" +nsrnbm + "%'" ;
try {
preStmt = conn.prepareStatement(sql);
preStmt.execute();
preStmt.close();
} catch (SQLException e) {
System.out.println("deleteSingleWd() fail! " + e.getMessage());
throw new SQLException(e.getMessage());
}
return 0;
}
private int insertSingleWd(Connection conn, String nsrnbm, String nyr) throws
SQLException {
String sql = "";
PreparedStatement preStmt = null;
//成批插入数据到 t_wdsc_wd 中
sql =
" INSERT INTO T_WDSC_WD ( WD_MC, WD_SM, WD_NR, WDSC_SJ, WDXG_SJ, MB_BM, CZY_BM, SWJG , nsrnbm " +
" ) VALUES ( " +
" '" + nsrnbm+nyr + "', '"+ this.getM_wdsm() +"', EMPTY_BLOB(), sysdate, sysdate, " +
" '"+ this.getM_mbbm() +"', '" + this.getM_czybm() +"', '"+ this.getM_swjg() +"' ," + nsrnbm +
" ) ";
System.out.println(sql + "\n nsrnbm=" + nsrnbm);
try {
preStmt = conn.prepareStatement(sql);
preStmt.execute();
preStmt.close();
} catch (SQLException e) {
System.out.println("insertSingleWd() fail! " + e.getMessage());
throw new SQLException(e.getMessage());
}
return 0;
}
//参数:纳税人内部码
public List getYhsxxBySingle(String nsrnbm, String nyr) throws Exception {
List list = null;
Connection conn = null;
try {
if (WDSCconstant.boolBatchProcess) {
conn = JDBCLocator.getInstance().getJDBCConnection();
this.deleteSingleWd(conn, nsrnbm);
this.insertSingleWd(conn, nsrnbm, nyr);
}
list = selectAll("WDSC.selectNsrxxBySingle", nsrnbm);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("YCWDSC21014"); //获取纳税人指标信息(单个纳税人)失败e.getMessage());
} finally {
if (conn != null) conn.close();
}
return list;
}
public List getNsrxxZbMsg(String nsrnbm, String zbmc, String swjg) throws
Exception {
List list = null;
try {
this.setNSRNBM(Double.parseDouble(nsrnbm));
this.setZB_DM(zbmc);
list = selectAll("WDSC.selectNsrxxZbMsg", this);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("YCWDSC21015"); //通过纳税人内部码和指标获得指标信息e.getMessage());
}
return list;
}
public List getNsrxxZbMsg() throws Exception {
List list = null;
try {
list = selectAll("WDSC.selectNsrxxZbMsg", this);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("YCWDSC21015 "); //e.getMessage());
}
return list;
}
//得到其他文档模板指标信息
public List getQtZbMsg(String nsrnbm) throws Exception {
List list = null;
try {
this.setNSRNBM(Double.parseDouble(nsrnbm));
list = selectAll("WDSC.selectQtZbMsg", this);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("YCWDSC21016"); //通过纳税人内部码获得其它模板指标信息失败e.getMessage());
}
return list;
}
public List getQtZbMsg() throws Exception {
List list = null;
try {
list = selectAll("WDSC.selectQtZbMsg", this);
} catch (Exception e) {
e.printStackTrace();
throw new Exception("YCWDSC21016"); //通过纳税人内部码获得其它模板指标信息失败e.getMessage());
}
return list;
}
public HashMap nsrbm2Nsrnbm(String nsrbm) throws Exception {
HashMap map = new HashMap();
Connection conn = null;
long dNsrnbm = 0;
String nsrmc = "", nsrnbm = "";
try {
conn = JDBCLocator.getInstance().getJDBCConnection();
Statement stmt = conn.createStatement();
String sql = "SELECT nsrnbm, nsr_mc " +
" FROM v_dj_nsr_all " +
" WHERE nsrbm = '" + nsrbm + "' ";
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
dNsrnbm = rs.getLong("nsrnbm");
nsrnbm = (new BigDecimal(dNsrnbm)).toString();
nsrmc = rs.getString("nsr_mc");
}
rs.close();
stmt.close();
map.put("nsrnbm", nsrnbm);
map.put("nsrmc", nsrmc);
map.put("nsr_mc", nsrmc);
} catch (Exception e) {
throw new Exception("YCWDSC21017"); //纳税人编码获得纳税人内部码失败e.getMessage());
} finally {
conn.close();
}
return map;
}
public HashMap nsrnbm2Nsrbm(String nsrnbm) throws Exception {
HashMap map = new HashMap();
Connection conn = null;
String nsrbm = "", nsrmc = "";
try {
conn = JDBCLocator.getInstance().getJDBCConnection();
Statement stmt = conn.createStatement();
String sql = "SELECT nsrbm, nsr_mc " +
" FROM v_dj_nsr_all " +
" WHERE nsrnbm = " + nsrnbm + " ";
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
nsrbm = rs.getString("nsrbm");
nsrmc = rs.getString("nsr_mc");
}
rs.close();
stmt.close();
map.put("nsrbm", nsrbm);
map.put("nsrmc", nsrmc);
map.put("nsr_mc", nsrmc);
} catch (Exception e) {
throw new Exception("YCWDSC21018");
} finally {
conn.close();
}
return map;
}
private void jbInit() throws Exception {
}
public String getM_czybm() {
return m_czybm;
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -