package merge;

import java.io.File;
import java.sql.ResultSet;




public class Merge {

	private HsqldbDao dao;
	private static String dbpath="dbs";
	private String[] dbs;//所有數(shù)據(jù)庫(kù)文件列表
	
	public Merge() {
		this.dao=new HsqldbDao();
		this.setDbs();
		this.createFinalDB();
	}
	
	private void setDbs(){
		File f=new File(dbpath);
		File[] fList=null;
		if(f.isDirectory()){
			fList = f.listFiles();
		}
		this.dbs=new String[fList.length];
		for(int i=0;i<fList.length;i++){
			if(fList[i].isDirectory()){
				File[] sub=fList[i].listFiles();
				for(int j=0;j<sub.length;j++){
					if(sub[j].getName().contains(".script")){
						dbs[i]=dbpath+"\\"+fList[i].getName()
						+"\\"+sub[j].getName().replaceAll(".script", "");
						break;
					}
				}
			}
		}
	}
	
	private void createFinalDB(){
		File ori=new File("finaldb");
		if(ori.isDirectory()){
			if(ori.listFiles()!=null){
				File[] oris=ori.listFiles();
				for(int i=0;i<oris.length;i++){
					System.out.println("delete "+oris[i].getName());
					oris[i].delete();
				}
			}
		}
		File f=new File(dbpath);
		File[] fList=null;
		if(f.isDirectory()){
			fList = f.listFiles();
		}
		if(fList!=null){
			if(fList.length>0){
				File[] fs=fList[0].listFiles();
				for(int j=0;j<fs.length;j++){
					if(fs[j].getName().contains(".script")
							||fs[j].getName().contains(".properties")){
						File k=new File("finaldb\\"+fs[j].getName());
						if(fs[j].getName().contains(".script")){
							k=new File("finaldb\\final.script");
						}
						if(fs[j].getName().contains(".properties")){
							k=new File("finaldb\\final.properties");
						}
						FileCopy fcopy = new FileCopy();    
						try{
							fcopy.copyFile(fs[j],k);
						}catch(Exception e){
							e.printStackTrace();
						}
					}
				}
			}
		}
	}
	
	public String[] getDbspath(){
		return this.dbs;
	}
	
	private String[] getTnames(String path){
		if(path==null) return null;
		File f=new File(path);
		if(f.isDirectory()){
			File[] fs=f.listFiles();
			int len=0;
			for(int i=0;i<fs.length;i++){
				if(fs[i].getName().contains(".csv")){
					len++;
				}
			}
			String [] tnames=new String[len];
			int k=0;
			for(int i=0;i<fs.length;i++){
				if(fs[i].getName().contains(".csv")){
					tnames[k]=fs[i].getName().replaceAll(".csv", "");
					System.out.println(tnames[k]);
					k++;
				}
			}
			return tnames;
		}
		return null;
	}
	
	public String checkUser(int cur,String id){
		if(id==null||this.dbs==null)return null;
		String mes=null;
		HsqldbDao check=new HsqldbDao();
		for(int i=0;i<=cur;i++){
			check.setConstr("jdbc:hsqldb:file:"+dbs[i]);
			check.openConnection();
			String sql="select * from users where id='"+id+"'";
			ResultSet rs=check.executeQuery(sql);
			try{
				while(rs.next()){
					mes="\t"+dbs[i]+".users has the PK-->"+id
					+"\n\t"+dbs[cur]+".users has the PK-->"+id;
				}
				break;
			}catch(Exception e){
				e.printStackTrace();
			}
			check.closeConnection();
		}
		return mes; 
	}
	
	public String checkRep(int cur,String tname,String userid,String date){
		if(tname==null||userid==null||date==null)return null;
		String mes=null;
		HsqldbDao check=new HsqldbDao();
		check.setConstr("jdbc:hsqldb:file:finaldb\\final");
		String sql1="select * from "+tname+" where userid='"+userid
		+"' and date='"+date+"'";
		check.openConnection();
		ResultSet rs1=check.executeQuery(sql1);
		boolean ch=false;
		try{
			while(rs1.next()){
				ch=true;
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		check.closeConnection();
		if(!ch)return null;
		for(int i=0;i<=cur;i++){
			check.setConstr("jdbc:hsqldb:file:"+dbs[i]);
			check.openConnection();
			String sql="select * from "+tname+" where userid='"+userid
			+"' and date='"+date+"'" ;
			ResultSet rs=check.executeQuery(sql);
			try{
				while(rs.next()){
					mes="\t"+dbs[i]+"."+tname+" has (userid,date)-->("
					+userid+","+date+")"
					+"\n\t"+dbs[cur]+"."+tname+" has (userid,date)-->("
					+userid+","+date+")";
				}
				break;
			}catch(Exception e){
				e.printStackTrace();
			}
			check.closeConnection();
		}
		return mes;
	}
	
	public  void mergeData(){
		if(this.dbs==null)return;
		if(this.dao==null){
			dao=new HsqldbDao();
		}
		Log log=new Log();
		for(int i=0;i<dbs.length;i++){
			String f1=dbs[i];
			ResultSet rs=null;
			String [] tnames=this.getTnames(f1.substring(0, f1.lastIndexOf("\\")));
			for(int j=0;j<tnames.length;j++){
				try{
					dao.setConstr("jdbc:hsqldb:file:"+f1);
					dao.openConnection();
					String sql="select * from "+tnames[j];
					rs= dao.executeQuery(sql);
					dao.closeConnection();
				}
				catch(Exception w){
					w.printStackTrace();
				}
				try{
					dao.setConstr("jdbc:hsqldb:file:finaldb\\final");
					dao.openConnection();
					while(rs.next()){
						String[] cols=this.getCols(rs);
						String sql1=this.insertSql(cols,tnames[j], rs);
						System.out.println(sql1);
						if(tnames[j].equalsIgnoreCase("users")){
							int result=dao.executeUpdate(sql1);
							if(result==0){
								String mes=this.checkUser(i, rs.getString("id"));
								log.addTime("can't insert: "+sql1);
								log.addMes(mes);
								System.out.println("can't insert:"+sql1);
							}
						}
						else{
							String uid=rs.getString("userid");
							String date=rs.getString("date");
							String mes=this.checkRep(i, tnames[j], uid, date);
							if(mes==null){
								int result=dao.executeUpdate(sql1);
								if(result==0){
									log.addTime("can't insert:"+sql1);
									log.addMes("  this is may because of the formation of table "
											+dbs[i]+"."+tnames[j]+" is wrong or the finally DB doesn't "
											+"exist this table_name");
								}
							}
							else{
								log.addTime("can't insert:"+sql1);
								log.addMes(mes);
							}
						}
					}
					dao.closeConnection();
				}catch(Exception w){
					System.out.println("!!");
					w.printStackTrace();
				}
			}		
		}
		log.writeLog();
		log.closeLog();
	}
	
	private String[] getCols(ResultSet rs){
		if(rs==null)return null;
		try{
			int i=rs.getMetaData().getColumnCount();
			String[] cols=new String[2*i];
	        for(int j=0;j<2*i;j+=2){
	     	   cols[j]=rs.getMetaData().getColumnName(j/2+1);
	     	   cols[j+1]=rs.getMetaData().getColumnClassName(j/2+1);
	        }
	        return cols;
		}catch(Exception e){
			e.printStackTrace();
		}
		return null;
	}
	
	private String insertSql(String [] cols,String tname,ResultSet rs){
		String sql=null;
		if(rs==null)return null;
		try{
			StringBuffer c=new StringBuffer();
			StringBuffer vc=new StringBuffer();
			if(tname.equalsIgnoreCase("users")){
				for(int k=0;k<cols.length;k+=2){
					c.append(cols[k]+",");
					if(cols[k+1].contains("Integer"))
						vc.append(rs.getInt(cols[k])+",");
					if(cols[k+1].contains("String")
							||cols[k+1].contains("Timestamp"))
						vc.append("'"+rs.getString(cols[k])+"'"+",");
				}
				c.deleteCharAt(c.length()-1);
				vc.deleteCharAt(vc.length()-1);
				sql="insert into users("+new String(c)+")values("+new String(vc)+")";
			}
			if(!tname.equalsIgnoreCase("users")){
				for(int k=0;k<cols.length;k+=2){
					if(!cols[k].equalsIgnoreCase("id")){
						c.append(cols[k]+",");
						if(cols[k+1].contains("Integer"))
							vc.append(rs.getInt(cols[k])+",");
						if(cols[k+1].contains("String")
								||cols[k+1].contains("Timestamp"))
							vc.append("'"+rs.getString(cols[k])+"'"+",");
					}
				}
				c.deleteCharAt(c.length()-1);
				vc.deleteCharAt(vc.length()-1);
				sql="insert into "+tname+"("+new String(c)+")values("+new String(vc)+")";
			}	
		}catch(Exception e){
			e.printStackTrace();
		}
		return sql;
	}
	
	public void displayFinal(String tablename){
		dao.setConstr("jdbc:hsqldb:file:finaldb\\final");
		dao.openConnection();
		String sql="select *  from "+tablename;
		ResultSet rs=dao.executeQuery(sql);
		try{
			while(rs.next()){
				/*System.out.println(rs.getString("id")+"\t"
						+rs.getString("password"));*/
				System.out.println(rs.getString("id")+"\t"
						+rs.getString("userid")+"\t"
						+rs.getString("options")+"\t"
						+rs.getInt("score")+"\t"
						+rs.getString("date"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}
		dao.closeConnection();
	}
	
	public void addTestdata(){
		if(this.dbs==null)return;
		for(int i=0;i<dbs.length;i++){
			dao.setConstr("jdbc:hsqldb:file:"+dbs[i]);
			dao.openConnection();
			for(int j=0;j<10;j++){
				String sql="insert into userans(userid,options,score,date,testtype) "+
					"values('"+j+"','12214321',"+25+",'2009-05-11 03:58:42','fs')";
				dao.executeUpdate(sql); 
			}
			dao.closeConnection();
		}
	}
	
	public void disPtestdata(){
		if(this.dbs==null)return;
		for(int i=0;i<dbs.length;i++){
			dao.setConstr("jdbc:hsqldb:file:"+dbs[i]);
			dao.openConnection();
			String sql="select * from userans";
			ResultSet rs=dao.executeQuery(sql);
			try{
				while(rs.next()){
					System.out.println(rs.getString("id")+"\t"
							+rs.getString("userid")+"\t"
							+rs.getString("options")+"\t"
							+rs.getInt("score")+"\t"
							+rs.getString("date")+"\t"
							+rs.getString("testtype"));
				}
			}catch(Exception e){
				e.printStackTrace();
			}
			dao.closeConnection();
		}
	}
	
	public static void main(String[] args){
		Merge a=new Merge();
		a.mergeData();
	}
}