package ay.db;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.Reader;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
import java.util.UUID;




import com.sun.java_cup.internal.runtime.Symbol;
/*******************************************************************************
 * 
 * 项目名称：转库
 * 
 * 版权所有： 重庆安运科技
 * 
 * 
 * 
 * 模块名称: 数据库操作工具
 * 
 * 模块功能: 
 * 
 * 编写人员:张德亮
 * 
 * 编写日期:2015.12.6
 * 
 * 
 * 备注:
 * 
 * 
 ******************************************************************************/
public class DbUtil {
	

	
	
	/**
	 * 用sql转库
	 * @param conn_sqlserver
	 * @param conn_ppas
	 * @param sql_insert
	 * @param tableName
	 * @param tfc_id
	 * @param ay_id
	 * @param out_field
	 * @param del_ay_id
	 * @param is_leave
	 */
	
	public void insert(Connection conn_oracle,Connection conn_ppas,String tableName,String sql_select,String sql_insert,String sql_del) {
		
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		System.out.println("/*****************************************"+String.format("%-20s", tableName)+"**************************************************************/");
		System.out.println("begin:"+df.format(new Date()));
		
//		sql_select=sql_select+" and rownum<10 ";
		
		System.out.println("\n---------sql_select:  "+sql_select);
						
		int ii=0;

		StringBuffer sb = new StringBuffer();
		String s_name = "", s_type = "",columnNameString="";
		try {	
			Statement st_ppas = conn_ppas.createStatement();
			Statement st_sqlserver = conn_oracle.createStatement();
			ResultSet rs = st_sqlserver.executeQuery(sql_select);
			if(sql_del!=null)st_ppas.executeUpdate(sql_del);

			ii=0;
			while (rs.next()) {
				ii=ii+1;
				sb = new StringBuffer();				
				sb.append(sql_insert+" values(");
				
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					s_type = rs.getMetaData().getColumnTypeName(i).toUpperCase();
					columnNameString = rs.getMetaData().getColumnName(i).toLowerCase();
//					System.out.println(columnNameString+"    "+s_type);
					if (rs.getObject(i) == null && !"id".equals(columnNameString)) {							
						sb.append("null,");
					} else {						
						
//						if("id".equals(columnNameString)){
//							sb.append("'"+UUID.randomUUID().toString().replace("-", "")+"',");
//						}
						
						if (s_type.equals("VARCHAR2")) {
							sb.append("'" + ((String)rs.getObject(i)).replaceAll("\'", "’") + "',");
						}
						if (s_type.equals("DATE")) {
							sb.append("'" + rs.getObject(i) + "',");
						}
						if (s_type.equals("NUMBER")) {
							sb.append(rs.getObject(i) + ",");
						}
						if (!s_type.equals("VARCHAR2")
								&& !s_type.equals("DATE")
								&& !s_type.equals("NUMBER")
								) {
							/*else if("password".equals(columnNameString.toLowerCase())){
								sb.append("'" + rs.getObject(i) + "',");
							}*/
							if(!"id".equals(columnNameString.toLowerCase())){							
								if("CHAR".equals(s_type)){
									sb.append("'" + rs.getObject(i) + "',");
								}else{
									sb.append("null,");
								}
							}
						}
						
					}
				}
//				System.out.println("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
//				st_ppas.executeUpdate("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
				
//				System.out.println(sb.substring(0, sb.length() - 1) + ")");
//				st_ppas.executeUpdate(sb.substring(0, sb.length() - 1) + ")");
					
				
				st_ppas.addBatch(sb.substring(0, sb.length() - 1) + ")");					
				if(ii%1000==0){
					st_ppas.executeBatch();
					System.out.println(ii);
				}	
					
//				st_ppas.executeUpdate("");
				
			}			
			
			st_ppas.executeBatch();
			
			rs.close();
			st_sqlserver.close();

			st_ppas.close();			
			
			System.out.println("end:"+df.format(new Date())+"     "+ii );

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}
	
public void insert2(Connection conn_oracle,Connection conn_ppas,String tableName,String sql_select,String sql_insert,String sql_del) {
		
		SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		System.out.println("/*********************insert2********************"+String.format("%-20s", tableName)+"**************************************************************/");
		System.out.println("begin:"+df.format(new Date()));
						
		int ii=0;

		StringBuffer sb = new StringBuffer();
		String s_name = "", s_type = "";
		try {	
			Statement st_ppas = conn_ppas.createStatement();
			Statement st_sqlserver = conn_oracle.createStatement();
			ResultSet rs = st_sqlserver.executeQuery(sql_select);
			if(sql_del!=null)st_ppas.executeUpdate(sql_del);

			ii=0;
			while (rs.next()) {
				ii=ii+1;
				sb = new StringBuffer();				
				sb.append(sql_insert+" values(");
				
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					s_type = rs.getMetaData().getColumnTypeName(i).toUpperCase();
					if (rs.getObject(i) == null) {
						sb.append("null,");
					} else {
						String columnNameString = rs.getMetaData().getColumnName(i);
						
						if (s_type.equals("VARCHAR")) {
							sb.append("'" + ((String)rs.getObject(i)).replaceAll("\'", "’") + "',");
						}
						if (s_type.equals("DATE")) {
							sb.append("'" + rs.getObject(i) + "',");
						}
						if (s_type.equals("NUMERIC")) {
							sb.append(rs.getObject(i) + ",");
						}
						if (!s_type.equals("VARCHAR")
								&& !s_type.equals("DATE")
								&& !s_type.equals("NUMERIC")
								) {
							if("id".equals(columnNameString.toLowerCase())){
								sb.append("'"+UUID.randomUUID().toString().replace("-", "")+"',");
							}
							/*else if("password".equals(columnNameString.toLowerCase())){
								sb.append("'" + rs.getObject(i) + "',");
							}*/
							else if(!"id".equals(columnNameString.toLowerCase()) && "CHAR".equals(s_type)){
								sb.append("'" + rs.getObject(i) + "',");
							}else{
								sb.append("null,");
							}
						}
						
					}
				}
//				System.out.println("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
//				st_ppas.executeUpdate("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
				
//				System.out.println(sb.substring(0, sb.length() - 1) + ")");
//				st_ppas.executeUpdate(sb.substring(0, sb.length() - 1) + ")");
				
				st_ppas.addBatch(sb.substring(0, sb.length() - 1) + ")");					
				if(ii%1000==0){
					st_ppas.executeBatch();
					System.out.println(ii);
				}	
					
//				st_ppas.executeUpdate("");
			}		
			
			st_ppas.executeBatch();
			
			rs.close();
			st_sqlserver.close();

			st_ppas.close();			
			
			System.out.println("end:"+df.format(new Date())+"     "+ii );

		} catch (Exception ex) {
			ex.printStackTrace();
		}
	}


public void insert3(Connection conn_ppas,String tableName,String sql_select,String sql_insert,String sql_del) {
	
	SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
	System.out.println("/*********************insert2********************"+String.format("%-20s", tableName)+"**************************************************************/");
	System.out.println("begin:"+df.format(new Date()));
					
	int ii=0;

	StringBuffer sb = new StringBuffer();
	String s_name = "", s_type = "";
	try {	
		Statement st_ppas = conn_ppas.createStatement();
		Statement st_ppas2 = conn_ppas.createStatement();
		ResultSet rs = st_ppas.executeQuery(sql_select);
		if(sql_del!=null)st_ppas2.executeUpdate(sql_del);

		ii=0;
		while (rs.next()) {
			ii=ii+1;
			sb = new StringBuffer();				
			sb.append(sql_insert+" values(");
			
			for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
				s_type = rs.getMetaData().getColumnTypeName(i).toUpperCase();
				
//				System.out.println(s_type);
				
				if (rs.getObject(i) == null) {
					sb.append("null,");
				} else {
					String columnNameString = rs.getMetaData().getColumnName(i);
					
					if (s_type.equals("VARCHAR") || s_type.equals("UNKNOWN") ) {
//						if("id".equals(columnNameString.toLowerCase())){
//							sb.append("'"+UUID.randomUUID().toString().replace("-", "")+"',");
//						}else{
						sb.append("'" + ((String)rs.getObject(i)).replaceAll("\'", "’") + "',");
//						}
					}
					if (s_type.equals("DATE")) {
						sb.append("'" + rs.getObject(i) + "',");
					}
					if (s_type.equals("NUMERIC")) {
						sb.append(rs.getObject(i) + ",");
					}
					if (!s_type.equals("VARCHAR")
							&& !s_type.equals("DATE")
							&& !s_type.equals("NUMERIC")
							&& !s_type.equals("UNKNOWN")
							) {
						/*else if("password".equals(columnNameString.toLowerCase())){
							sb.append("'" + rs.getObject(i) + "',");
						}*/
						 if(!"id".equals(columnNameString.toLowerCase()) && "CHAR".equals(s_type)){
							sb.append("'" + rs.getObject(i) + "',");
						}else{
							sb.append("null,");
						}
					}
					
				}
			}
//			System.out.println("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
//			st_ppas.executeUpdate("delete from "+tableName+" where id = '"+sb.substring(sb.indexOf("(", sb.indexOf("values"))+1, sb.indexOf(",", sb.indexOf("values"))).trim()+"'");
			
//			System.out.println(sb.substring(0, sb.length() - 1) + ")");
//			st_ppas2.executeUpdate(sb.substring(0, sb.length() - 1) + ")");
			
			st_ppas2.addBatch(sb.substring(0, sb.length() - 1) + ")");					
			if(ii%1000==0){
				st_ppas2.executeBatch();
				System.out.println(ii);
			}	
				
//			st_ppas.executeUpdate("");
		}		
		
		st_ppas2.executeBatch();
		
		rs.close();
		st_ppas.close();

		st_ppas2.close();			
		
		System.out.println("end:"+df.format(new Date())+"     "+ii );

	} catch (Exception ex) {
		ex.printStackTrace();
	}
}
	
	public ResultSet query(Connection con, String sql){
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			statement = con.createStatement();
			resultSet = statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultSet;
	}

	
	public void update(Connection con, String sql){
		Statement statement = null;
		try {
			System.out.println("\n=====update  sql: "+sql);
			statement = con.createStatement();
			statement.executeUpdate(sql);			
		} catch (SQLException e) {
			Scanner reader=new Scanner(System.in);
			System.out.println("输入任意字符继续回车继续");
			String str = reader.next();
			if(str.equals("Y")){
				update(con, sql);
			}
			e.printStackTrace();
		}
	}

	
}