package GEOSMS;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;
import java.util.StringTokenizer;
import Config.xmlReader;

public class GeoSmsDM {
	public Connection conn = null;
	public Statement stmt = null;
	public ResultSet rs = null;
	public GeoSmsDM()
	{if(!ConnDb())
		System.out.println("Connection db err");
	}
	public String lookingfor(String sms)
	{
		 StringTokenizer st=new StringTokenizer(sms,".");
	      String[] ts=new String[3];
	      int ptr_ts=0;
	      while(st.hasMoreTokens())
	      { String s=st.nextToken();
	      	 ts[ptr_ts]=s; 	         
 	         ptr_ts++;         	        
 	      }
	   String s="";
	   String sql="SELECT sys.Tpoint.address ";
              sql+="FROM sys.Ttype_list INNER JOIN ";
              sql+="sys.Tpoint_type ON sys.Ttype_list.type_id = sys.Tpoint_type.id INNER JOIN ";
              sql+="sys.Tpoint ON sys.Ttype_list.point_id = sys.Tpoint.id ";
              sql+="WHERE (sys.Tpoint.address LIKE '%"+ts[0]+"%') AND "; 
              sql+=" (sys.Tpoint.address LIKE '%"+ts[1]+"%') AND ";
              sql+="(sys.Tpoint_type.type_name LIKE '%"+ts[2]+"%') ";
	   
	   List ls=SelectDB(sql);
	   if(ls.size()==0)
	   {s="對不起 沒有找到合適的銀行網點";}
	   else if(ls.size()==1)
	             {s=((Map)ls.get(0)).get("address").toString();}
	         else
	            {s=((Map)ls.get(0)).get("address").toString()+((Map)ls.get(0)).get("address").toString();
	               if(s.length()>=75)
	                {s=((Map)ls.get(0)).get("address").toString();}
	            } 
	   return s;
	}
	
	public boolean ConnDb()
	{
		boolean ptr=false;
		try{
			 // DriverManager.registerDriver (   new oracle.jdbc.driver.OracleDriver()    );
	   	     // conn = DriverManager.getConnection
	   	     // ("jdbc:oracle:thin:@192.168.120.83:1521:CITIGRP", "citigrp_user", "xyz");
	   	                      // @machine:port:SID,   userid,  password
			xmlReader xml=new xmlReader();
			xml.Read("config.xml");			
				   String url="jdbc:odbc:"+xml.DbName;
				   String driver="sun.jdbc.odbc.JdbcOdbcDriver";
				   conn= DriverManager.getConnection(url,xml.DbUser,xml.DbPwd);
				   Class.forName(driver); 				
	   	      stmt=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);		  	  
			  ptr=true;
		   }catch(Exception e)
			{System.out.println("conn err"+e);ptr=false;}		
		return ptr;
	}
	public ResultSet SelectRs(String sql)
	   {
	   	try{
			 rs=stmt.executeQuery(sql);
			}catch(SQLException e)
			{System.out.println("select error");		 
			}
			return rs;
	   }//selectRs
	public ArrayList SelectDB(String sql )
	{
		String value = null;
		   ArrayList list = new ArrayList();
		   Map map;	         
		    try{
		    	rs=stmt.executeQuery(sql);
		    	ResultSetMetaData mt = rs.getMetaData();
		    	
			   	int columnCount = mt.getColumnCount();	  //get the number of column in a result
		       while (rs.next())
		       {
		            map = new HashMap(columnCount);
		            for (int i = 1; i <= columnCount; i++)
		             {
		               value = (String)rs.getString(i);
		               //value=new String(value.getBytes("iso-8859-1"),"gb2312");
		                if (value == null) value = "";
		                map.put(mt.getColumnName(i).toLowerCase(),value);
		              }//for
		          list.add(map);	          
		       }//while
		    } catch(Exception ex){
		      System.out.println("Cann't convert database!"+ex);
		      System.out.println(sql);
		    }//try
		  
		    return list;
	}
	public boolean Command(String sql)
	{
		boolean ptr=false;
		try{
			stmt.execute(sql);
			ptr=true;
		}catch(Exception e)
		{
			ptr=false;
			System.out.println(sql+e);
			}
		return ptr;
	}

}