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 ay.util.SecurityEncryptUtil;

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

	
	
	/**
	 * 用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_sqlserver,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 = "";
		try {	
			Statement st_ppas = conn_ppas.createStatement();
			Statement st_sqlserver = conn_sqlserver.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();
					s_type = rs.getMetaData().getColumnTypeName(i).toLowerCase();
//					System.out.println(s_type);
//					System.out.println(rs.getObject(i));
					if (rs.getObject(i) == null) {
						sb.append("null,");
					} else {
						String columnNameString = rs.getMetaData().getColumnName(i);
//						System.out.println(columnNameString);
						if (s_type.equals("varchar")) {
							if("id".equals(columnNameString.toLowerCase())){
								sb.append("'"+UUID.randomUUID().toString().replace("-", "")+"',");
							}else{
								if("password".equals(columnNameString.toLowerCase())){
									sb.append("'"+SecurityEncryptUtil.md5((String)rs.getObject(i))+"',");
								}else{
									sb.append("'" + ((String)rs.getObject(i)).replaceAll("\'", "’") + "',");
								}
							}
						}
						if (s_type.equals("datetime")) {
							sb.append("'" + rs.getObject(i) + "',");
						}
						if (s_type.equals("numeric")|| s_type.equals("int")) {
							sb.append(rs.getObject(i) + ",");
						}
						if (!s_type.equals("varchar")
								&& !s_type.equals("datetime")
								&& !s_type.equals("numeric")
								&& !s_type.equals("int")
								) {								
								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.executeUpdate("");
				
				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 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();
		}
	}

	
}