package com.atest.test;
// PinJieBaoWenTools
import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
public class WDHPinJieBaoWenTools {
public static void main(String[] args) {
// testOracle();
// getYwdjh("310115200102040919");
// getWdh("310115200102040919"); //310115200102040919
testOracle();
spitFile();
// System.out.println(checkWdh("CN0010522"));
}
/* public static void test(){
String s="22|null|kk|ww|";
String []res=s.split("\\|");
int i=0;
for(String t:res){
System.out.println((i++)+" :ffffff: "+t);
}
}*/
public static String getYwdjh(String zjhm){
Connection conn = null;// 创建一个数据库连接
PreparedStatement ps = null;// 创建预编译语句对象,用这个不用Statement
ResultSet resultSet = null;// 创建一个结果集对象
conn =JDBCUtil.getConnection();
String ywdjh=null;
// String sql=" select ywdjh from accountapplydetailcopy where zjhm= "+zjhm;
String sql=" select ywdjh from accountapplydetailcopy where zjhm= ? " ;
// String sql2="select slwdbh from personapply where zjhm in ( select ywdjh from accountapplydetailcopy where zjhm= ?)";
try {
// resultSet=ps.executeQuery(sql);
ps = conn.prepareStatement(sql);// 实例化预编译语句
ps.setString(1, zjhm);
System.out.println(sql);
resultSet=ps.executeQuery();
System.out.println("resultSet: "+resultSet);
while (resultSet.next()) {// 当结果集不为空时
ywdjh=resultSet.getString("ywdjh");
System.out.println("ywdjh: "+ywdjh);
break;
}
} catch (SQLException e) {
e.printStackTrace();
}
/*if(ywdjh==null||ywdjh.length()==0){
try {
// resultSet=ps.executeQuery(sql);
ps = conn.prepareStatement(sql2);// 实例化预编译语句
ps.setString(1, zjhm);
System.out.println(sql2);
resultSet=ps.executeQuery();
System.out.println("resultSet: "+resultSet);
while (resultSet.next()) {// 当结果集不为空时
ywdjh=resultSet.getString("ywdjh");
System.out.println("ywdjh: "+ywdjh);
break;
}
} catch (SQLException e) {
e.printStackTrace();
}
}*/
JDBCUtil.close(resultSet, ps, conn);
return ywdjh;
}
public static String getWdh(String zjhm){
Connection conn = null;// 创建一个数据库连接
PreparedStatement ps = null;// 创建预编译语句对象,用这个不用Statement
ResultSet resultSet = null;// 创建一个结果集对象
conn =JDBCUtil.getConnection();
String wdh=null;
// String sql="select khyhhh from accountapplydetailcopy where zjhm=?";
// String sql2="SELECT wdh FROM APPLICABLELIST where zjhm= ?";
// String sql="select sqwdh,yhwdh from comcode2branchno where sqwdh in (select SQSLWDDM from accountapplydetailcopy where zjhm= ?)";
// 原来sql String sql="select yhwdh from comcode2branchnocopy where sqwdh in (select sqslwddm from accountapplydetailcopy where zjhm= ?)";
// String sql="select yhwdh from comcode2branchnocopy where sqwdh in (select sqslwddm from accountapplydetailcopy where zjhm= ?)"; // 原来sql
// String sql ="select applyinfo_wdh from accountapplydetailcopy where zjhm= ?";
String sql="select applyinfo_wdh from accountapplydetailcopy where zjhm=?"; WDH
// select company_code from ta_companyinfocopy
try {
// resultSet=ps.executeQuery(sql);
ps = conn.prepareStatement(sql);// 实例化预编译语句
ps.setString(1, zjhm);
// System.out.println(sql);
resultSet=ps.executeQuery();
System.out.println("resultSet: "+resultSet);
while (resultSet.next()) {// 当结果集不为空时
wdh=resultSet.getString("applyinfo_wdh"); // 提供的WDH
// wdh=resultSet.getString("yhwdh"); // 用的备选申领网点 comcode2branchno
System.out.println("wdh 业务网点号为2: "+wdh);
break;
}
} catch (SQLException e) {
e.printStackTrace();
}
JDBCUtil.close(resultSet, ps, conn);
return wdh;
}
public static Boolean checkWdh(String chechwdh){ //如果都找不到 默认 CN0019009
Connection conn = null;// 创建一个数据库连接
PreparedStatement ps = null;// 创建预编译语句对象,用这个不用Statement
ResultSet resultSet = null;// 创建一个结果集对象
conn =JDBCUtil.getConnection();
String wdh=null;
Boolean flag=false;
// String sql="select sqwdh,yhwdh from comcode2branchno where sqwdh in (select SQSLWDDM from accountapplydetailcopy where zjhm= ?)";
// String sql="select yhwdh from comcode2branchnocopy where sqwdh in (select sqslwddm from accountapplydetailcopy where zjhm= ?)";
// String sql1="select khyhhh from accountapplydetailcopy where zjhm=?";
// String sql="SELECT wdh FROM APPLICABLELIST where zjhm= ?";
String sql="select company_code from ta_companyinfocopy where company_code= ? ";
try {
// resultSet=ps.executeQuery(sql);
ps = conn.prepareStatement(sql);// 实例化预编译语句
ps.setString(1, chechwdh);
System.out.println(sql);
resultSet=ps.executeQuery();
System.out.println("resultSet: "+resultSet);
while (resultSet.next()) {// 当结果集不为空时
wdh=resultSet.getString("company_code");
System.out.println("wdh 业务网点号为: "+wdh);
if(wdh.length()>0||!wdh.isEmpty()){
flag=true;
break;
}
// break;
}
} catch (SQLException e) {
e.printStackTrace();
}
JDBCUtil.close(resultSet, ps, conn);
return flag;
}
// public void spitFile() throws Exception{
public static void spitFile() {
String fileNameByDate=new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
File filer =new File("E:\\filesplit\\"+fileNameByDate+".txt");
if(!filer.exists()){
try {
throw new Exception("导出文件失败,请检查");
} catch (Exception e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
String splitfile=null;
// File filew=new File(splitfile);
File filew=null;
BufferedReader br=null;
BufferedWriter bw = null;
/*try {
br = new BufferedReader(new FileReader(filer));
} catch (FileNotFoundException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}*/
// BufferedWriter bw=new BufferedWriter(new FileWriter(filew,true));
String line=null;
String [] splitStr=null;
String ywdjh=null;
String wdh=null;
String filename=null;
try {
// br = new BufferedReader(new FileReader(filer)); utf-8
br = new BufferedReader(new InputStreamReader(new FileInputStream(filer),"UTF-8"));
while((line=br.readLine())!=null){
splitStr=line.split("\\|");
String zjhm=splitStr[0];
ywdjh=getYwdjh(zjhm);
wdh=getWdh(zjhm);
filename=wdh+"_"+ywdjh;
filew=new File("E:\\filesplit\\"+filename+".txt");//E:\\filesplit\\"+fileNameByDate+".txt
if(!filew.exists()){
filew.createNewFile();
}
// BufferedReader reader=new BufferedReader(new InputStreamReader(new FileInputStream(f),"UTF-8"));
// BufferedWriter writer=new BufferedWriter(new OutputStreamWriter(new FileOutputStream(tf,true),"GBK"));
// bw=new BufferedWriter(new FileWriter(filew,true)); utf-8
bw=new BufferedWriter(new OutputStreamWriter(new FileOutputStream(filew,true),"GBK"));
bw.write(line);
bw.newLine();
bw.flush();
//bw.write(line);
}
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}finally{
if(bw!=null){
try {
bw.close();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
if(br!=null){
try {
br.close();
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
}
}
public static void testOracle() {
Connection conn = null;// 创建一个数据库连接
PreparedStatement ps = null;// 创建预编译语句对象,用这个不用Statement
ResultSet resultSet = null;// 创建一个结果集对象
File file =null;
// String time=System.currentTimeMillis()+"";
String fileNameByDate=new SimpleDateFormat("yyyy年MM月dd日").format(new Date());
System.out.println(fileNameByDate);
BufferedWriter bw=null;
try {
conn =JDBCUtil.getConnection();
file=new File("E:\\filesplit\\"+fileNameByDate+".txt");
if(!file.exists()){
file.createNewFile();
}
bw=new BufferedWriter(new FileWriter(file));
System.out.println("conn: "+conn);
// String sql = "select * from ACCOUNTAPPLYDETAILCOPY ";
String sqll ="select "
+ "decode(shbzhm,null,'null',shbzhm)||'|'||decode(sbkh,null,'null',sbkh)||'|'||decode(yhkh,null,'null',yhkh)||'|'||"
+ "decode(yhkh_old,null,'null',yhkh_old)||'|'||decode(xm,null,'null',xm)||'|'||decode(xb,null,'null',xb)||'|'||"
+ "decode(csrq,null,'null',csrq)||'|'||decode(gj,null,'null',gj)||'|'||decode(mz,null,'null',mz)||'|'||"
+ "decode(zy,null,'null',11,'国家公务员(包括参照、依照公务员管理的人员)',12,'机关工勤人员',13,'事业单位管理人员',14,'事业单位专业技术人员',15,'事业单位工勤人员',"
+ " 21,'企业经营管理人员',22,'企业专业技术人员',24,'工人',27,'农民工',31,'学生',37,'现役军人',51,'自由职业者',54,'个体经营者',70,'无业人员',80,'退(离)休人员',81,'外国人',zy)"
+ "||'|'||decode(zjlx,null,'null',zjlx)||'|'||decode(zjqsrq,null,'null',zjqsrq)||'|'||"
+ "decode(zjjzrq,null,'null',zjjzrq)||'|'||decode(zjhm,null,'null',zjhm)||'|'||decode(sjhm,null,'null',sjhm)||'|'||"
+ "decode(gddh,null,'null',gddh)||'|'||decode(txdz,null,'null',txdz)||'|'||decode(txdz_yb,null,'null',txdz_yb)||'|'||"
+ "decode(sqslwddm,null,'null',sqslwddm)||'|'||decode(sfscyx,null,'null',sfscyx)||'|'||decode(bz,null,'null',bz)||'|'||"
// + "decode(KHYHHH,null,'null',KHYHHH)||'|'" // 备选申领网点
+ "decode(applyinfo_wdh,null,'null',applyinfo_wdh)||'|'" // 有wdh
+ " as name from accountapplydetailcopy" ;
//applyinfo_wdh
System.out.println("sqll:= "+sqll);
ps = conn.prepareStatement(sqll);// 实例化预编译语句
resultSet = ps.executeQuery();// 执行查询
System.out.println("resultSet: "+resultSet);
while (resultSet.next()) {// 当结果集不为空时
String value=resultSet.getString("name");
String idcard=value.split("\\|")[0];
String wdhh=getWdh(idcard);
/*String font=value.substring(0, value.length()-5);
String finalvalue=font+wdhh+"|";
System.out.println("value: "+finalvalue);
bw.write(finalvalue);
bw.newLine();*/
if(checkWdh(wdhh)){
String font=value.substring(0, value.trim().length()-10); // WDH 有网点号
// String font=value.substring(0, value.trim().length()-5); // 备选申领网点 comcode2branchno
String finalvalue=font+wdhh+"|";
System.out.println("6666666666666666666666666666666666666666666666666666666666666666"+font);
System.out.println("7777777777777777777777777777777777777777777777777777777777777777"+wdhh);
System.out.println("value: "+finalvalue);
bw.write(finalvalue);
bw.newLine();
}else{
System.out.println("checkWdh(wdhh)88888888888888888888888888888888888888888888888888888888888888");
String font=value.substring(0, value.length()-5);
String finalvalue=font+"CN0019009"+"|";
System.out.println("value: "+finalvalue);
bw.write(finalvalue);
bw.newLine();
// bw.write("CN0019009");
}
}
/*ps.setString(1, "小李");// 设置参数,前面的1表示第一个问号(第二个问号就用2)
resultSet = ps.executeQuery();// 执行查询
while (resultSet.next()) {// 当结果集不为空时
System.out.println(resultSet.getString("age"));
}*/
} catch (Exception e) {
e.printStackTrace();
}finally{
JDBCUtil.close(resultSet, ps, conn);
}
if(bw!=null){
try {
bw.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}