package ng.db;
import org.json.*;

import java.io.IOException;
import java.io.InputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Properties;

import org.json.*;
import org.json.JSONArray;


public class DBHelper{

	private String sqlurl="jdbc:mysql://localhost/haier";
	private String user="root";
	private String password="beibo.123/";
	static java.util.Hashtable<String, DBHelper> helper=new java.util.Hashtable<String, DBHelper>();


	public static DBSession createDBSession(String sqlurl,String user,String password,boolean manulcomit) throws SQLException{
		return DBSession.createSession(sqlurl, user, password,manulcomit);
	}

	public class ProcResult{
		public int code;
		public String Message;
		public org.json.JSONArray Datas;
	}

	public static DBSession createDBSession(String name,boolean manulcomit) throws Exception{
		if(helper.size()==0){
			InitHelper();
		}
		DBHelper ret= helper.get(name);
		if(ret!=null)
			return ret.createSession(manulcomit);
		throw new Exception("锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷");
	}




	public int update(String sql,Object...params) throws SQLException{
		DBSession sn=null;
		try{
			sn= createSession(false);
			sn.createSql(sql);
			return sn._update(params);
		}
		finally{
			sn.close();
		}
	}


	public org.json.JSONArray query(boolean asObject, String sql, Object...params) throws SQLException, JSONException{
		DBSession sn=null;
		try{
			sn= createSession(false);
			sn.createSql(sql);
			return sn._query(params).resultToJson(asObject);
		}
		finally{
			sn.close();
		}
	}






	public static DBHelper getDBHelper(String name){
		return helper.get(name);
	}


	public static void addHelper(String name,String url,String user,String pass){

		DBHelper help=new DBHelper(url,user,pass);
		helper.put("mes",help);
	}



	public static void InitHelper() throws Exception{


		Properties properties = NGFunction.getProperties();
		Class.forName(properties.getProperty("driver"));
		String data= properties.getProperty("connection");
		if(data==null)
		{
			throw new Exception("未锟揭碉拷锟斤拷锟斤拷");
		}
		System.out.println(data);
		String[] names=data.split(",");
		for(int i=0;i<names.length;i++){
			DBHelper help=new DBHelper(properties.getProperty(names[i]+".url"),
					properties.getProperty(names[i]+".user"),
					properties.getProperty(names[i]+".password")
			);
			helper.put(names[i],help);
			System.out.println("create "+names[i]);
		}

	}




	public static  void updateConnection(String name) throws Exception{
		Properties properties = NGFunction.getProperties();
		DBHelper ret= helper.get(name);
		if(ret==null){
			throw new Exception("锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷锟斤拷");
		}
		ret.sqlurl=properties.getProperty(name+".url");
		ret.user=properties.getProperty(name+".user");
		ret.password=properties.getProperty(name+".password");
	}






	public org.json.JSONArray query(String sql, Boolean RowAsObject, Object...params) throws SQLException, JSONException{
		DBSession sn=null;
		try{
			sn=this.createSession(false);
			sn.createSql(sql);
			return sn.query(params).resultToJson(RowAsObject);

		}
		finally{
			if(sn!=null)
				sn.close();
		}
	}

	public int update(String sql,Boolean RowAsObject,Object...params) throws SQLException{
		DBSession sn=null;
		try{
			sn=this.createSession(false);
			sn.createSql(sql);
			return sn.update(params);


		}
		finally{
			if(sn!=null)
				sn.close();
		}
	}



	public void init(String sqlurl,String user,String password){
		this.sqlurl=sqlurl;
		this.user=user;
		this.password=password;
	}


	private static boolean odbc_regist=false;





	//锟斤拷锟斤拷一锟斤拷锟斤拷in锟斤拷锟斤拷  锟斤拷锟斤拷out锟斤拷锟斤拷锟侥存储锟斤拷锟教ｏ拷一锟斤拷锟街讹拷锟斤拷int 1锟斤拷锟斤拷晒锟? 一锟斤拷锟斤拷String锟斤拷为一锟斤拷锟截革拷锟斤拷锟斤拷锟绞э拷锟绞憋拷锟轿拷锟绞撅拷锟较拷锟?
	public QueryResult SelectByProc(String name,String[] Params){
		Connection con=null;
		try{
			int length=Params.length;

			String s=String.format("{call %s(",name);
			for(int i=0;i<=length;i++){
				if(i==0)
					s+="?";
				else
					s+=",?";
			}
			s+=")}";

			con=DriverManager.getConnection(sqlurl,user,password);
			CallableStatement c=con.prepareCall(s);
			for(int i=0;i<length;i++){
				c.setString(i+1,Params[i]);
			}
			c.registerOutParameter(length+1, java.sql.Types.VARCHAR);
			ResultSet result= c.executeQuery();
			String rest=c.getString(length+1);
			QueryResult r=new QueryResult(con,c,result);
			if(rest!=null){
				if(rest.equals("success")){
					return r;
				}
			}
			r.Close();
			return null;

		}
		catch(Exception e){
			e.printStackTrace();
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			return null;
		}
	}

	//锟斤拷锟斤拷一锟斤拷锟斤拷in锟斤拷锟斤拷   一锟斤拷out锟斤拷锟斤拷锟侥存储锟斤拷锟斤拷
	public  String InvokeSqlProc(String name,String[] Params){
		Connection con=null;
		try{
			int length=Params.length;

			String s=String.format("{call %s(",name);
			for(int i=0;i<=length;i++){
				if(i==0)
					s+="?";
				else
					s+=",?";
			}
			s+=")}";

			con=DriverManager.getConnection(sqlurl,user,password);
			CallableStatement c=con.prepareCall(s);
			for(int i=0;i<length;i++){
				c.setString(i+1,Params[i]);
			}
			c.registerOutParameter(length+1, java.sql.Types.VARCHAR);
			c.execute();
			String message=c.getString(length+1);
			c.close();
			con.close();
			return message;
		}
		catch(Exception e){
			e.printStackTrace();
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			return "fail";
		}
	}


	//锟斤拷锟斤拷一锟斤拷锟斤拷in锟斤拷锟斤拷 一锟斤拷out锟斤拷锟斤拷锟侥存储锟斤拷锟斤拷
	public  String InvokeSqlProc(String name,String param1,String param2){
		Connection con=null;
		try{
			con=DriverManager.getConnection(sqlurl,user,password);
			CallableStatement c=con.prepareCall(String.format("{call %s(?,?,?)}",name));
			c.setString(1,param1);
			c.setString(2,param2);

			c.registerOutParameter(3, java.sql.Types.VARCHAR);
			System.out.println("ok");
			c.execute();
			System.out.println("ok1");
			String message=c.getString(3);
			c.close();
			con.close();
			return message;
		}
		catch(Exception e){
			e.printStackTrace();
			if(con!=null)
				try {
					con.close();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			return "fail";
		}

	}

	//锟斤拷锟斤拷一锟斤拷锟斤拷锟斤拷锟斤拷
	public Connection getConnection() throws SQLException{

		return  DriverManager.getConnection(sqlurl,user,password);
		//return DriverManager.getConnection(sqlurl,user,password);
	}



	//锟斤拷锟斤拷锟捷匡拷锟斤拷锟斤拷锟斤拷锟揭伙拷锟斤拷侄锟斤拷锟轿疤╯elect 锟斤拷选锟斤拷
	public  String MakeSelectOptionBySql(String sql,String field){
		QueryResult query=null;
		try{
			query=new QueryResult(sqlurl,user,password,sql);
			ResultSet result=query.Result;
			StringBuilder sb=new StringBuilder();
			while(result.next()){
				Object o=result.getObject(field);
				if(o!=null)
					sb.append(String.format("<option value=\"%s\">%s</option>\r\n",o.toString(),o.toString()));
			}
			query.Close();
			return sb.toString();
		}
		catch(Exception e){
			if(query!=null)
				query.Close();
			return null;
		}
	}

	//通锟斤拷一锟斤拷锟斤拷锟斤拷取锟斤拷锟捷硷拷锟斤拷锟?
	public QueryResult getQueryResult(String sql){
		QueryResult query=null;
		try{
			query=new QueryResult(sqlurl,user,password,sql);
			return query;
		}
		catch(Exception e){
			if(query!=null)
				query.Close();
			e.printStackTrace();
			return null;
		}
	}


	//锟斤拷锟斤拷锟捷匡拷锟斤拷锟斤拷锟揭伙拷锟角疤拷谋锟斤拷tbody锟斤拷锟斤拷锟斤拷锟捷ｏ拷PKName锟斤拷锟斤拷锟斤拷锟街段ｏ拷ShowField锟斤拷要锟斤拷示锟斤拷锟斤拷 锟斤拷锟斤拷#锟斤拷头锟斤拷锟街凤拷锟斤拷直锟斤拷锟斤拷为t锟斤拷锟斤拷锟斤拷锟斤拷
	public  String MakeHTMLTableRowsBySql(String sql,String PKName,String[] ShowField) throws SQLException{
		QueryResult query=null;
		try{
			query=new QueryResult(sqlurl,user,password,sql);
			ResultSet result=query.Result;
			StringBuilder sb=new StringBuilder();
			int[] fieldIndex=new int[ShowField.length];
			for(int i=0;i<ShowField.length;i++){
				if(ShowField[i].charAt(0)=='#'){
					fieldIndex[i]=-100;
					ShowField[i]=ShowField[i].substring(1);
				}
				else
					fieldIndex[i]=result.findColumn(ShowField[i]);
			}
			//sb.append("<tbody>\r\n");
			while(result.next()){
				if(PKName!=null){
					sb.append(String.format("<tr data-id=\"%s\">\r\n",result.getObject(PKName).toString().trim()));
				}
				else
					sb.append("<tr>\r\n");
				for(int i=0;i<fieldIndex.length;i++){
					sb.append("<td>");
					int idx=fieldIndex[i];
					if(idx==-100){
						sb.append(ShowField[i]);
					}
					else{

						Object o=result.getObject(idx);
						if(o!=null)
							sb.append(o);
					}
					sb.append("</td>");
				}
				sb.append("</tr>\r\n");
			}
			//sb.append("</tbody>\r\n");
			query.Close();
			return sb.toString();
		}
		catch(Exception e){
			if(query!=null)
				query.Close();
			e.printStackTrace();
			return null;
		}
	}

	public DBHelper(String sqlurl,String user,String password){
		this.sqlurl=sqlurl;
		this.user=user;
		this.password=password;
	}

	//一锟斤拷锟津单的诧拷询锟斤拷锟斤拷锟斤拷媒锟斤拷锟斤拷锟斤拷锟斤拷一些锟斤拷询锟斤拷源锟斤拷锟斤拷锟斤拷close统一锟酵放ｏ拷锟斤拷锟酵放癸拷锟斤拷
	public  class QueryResult{
		private java.sql.Connection con;
		private java.sql.Statement statement;
		public java.sql.ResultSet Result;

		public  QueryResult(String conString,String user,String password,String Sql) throws SQLException{
			this.con= java.sql.DriverManager.getConnection(conString,user,password);
			this.statement=con.createStatement();
			this.Result=this.statement.executeQuery(Sql);
		}


		public QueryResult(Connection con,java.sql.Statement statement,ResultSet result){
			this.con=con;
			this.statement=statement;
			this.Result=result;
		}


		public void Close(){
			try{
				if(Result!=null){
					Result.close();
					Result=null;
				}
			}catch(Exception e){

			}
			try{
				if(statement!=null){
					statement.close();
					statement=null;
				}
			}catch(Exception e){

			}
			try{
				if(con!=null){
					con.close();
					con=null;
				}
			}catch(Exception e){

			}
		}
	}

	public DBSession createSession(boolean ManulCommit) throws SQLException{
		Connection con=this.getConnection();
		return new DBSession(con,ManulCommit);
	}


	public static org.json.JSONArray resultToJson(String[] fields,ResultSet result,boolean RowAsObject) throws SQLException, JSONException{
		org.json.JSONArray arr=new org.json.JSONArray();

		String[] cols=fields;

		if(RowAsObject){
			while(result.next()){
				JSONObject obj=new JSONObject();
				for(int i=0;i<cols.length;i++){
					Object o=result.getObject(i+1);
					if(o==null)
						o=JSONObject.NULL;
					obj.put(cols[i],o);

				}
				arr.put(obj);
			}
		}
		else{
			while(result.next()){
				org.json.JSONArray obj=new org.json.JSONArray();
				for(int i=0;i<cols.length;i++){
					Object o=result.getObject(i+1);
					if(o==null)
						o=JSONObject.NULL;
					obj.put(o);
				}
				arr.put(obj);
			}
		}
		return arr;
	}





	//锟斤拷锟斤拷锟捷匡拷锟窖拷锟斤拷锟斤拷菁锟街憋拷锟阶拷锟轿狫sonArray, 锟斤拷锟絉owAsObject=true 每锟斤拷锟斤拷目锟斤拷锟斤拷锟斤拷为锟斤拷锟斤拷锟街讹拷锟斤拷锟斤拷jsonobject 锟斤拷锟斤拷每锟斤拷锟斤拷目锟斤拷锟斤拷一锟斤拷锟斤拷锟街讹拷锟斤拷锟斤拷 jsonarray锟斤拷锟斤拷
	public static org.json.JSONArray resultToJson(ResultSet result,boolean RowAsObject) throws SQLException, JSONException{
		org.json.JSONArray arr=new org.json.JSONArray();
		ResultSetMetaData r= result.getMetaData();
		String[] cols=new String[r.getColumnCount()];

		for(int i=0;i<cols.length;i++){
			cols[i]=r.getColumnLabel(i+1);

		}
		if(RowAsObject){
			while(result.next()){
				JSONObject obj=new JSONObject();
				for(int i=0;i<cols.length;i++){
					Object o=result.getObject(i+1);
					if(o==null)
						o=JSONObject.NULL;
					obj.put(cols[i],o);
				}
				arr.put(obj);
			}
		}
		else{
			while(result.next()){
				org.json.JSONArray obj=new JSONArray();
				for(int i=0;i<cols.length;i++){
					Object o=result.getObject(i+1);
					if(o==null)
						o=JSONObject.NULL;
					obj.put(o);
				}
				arr.put(obj);
			}
		}
		return arr;
	}


	public static void main(String[] ss) throws Exception{
//    	 DBHelper.InitHelper();
//    	 System.out.println(DBHelper.getDBHelper("mes").query(true,"select * from gmms_galss_task where width>? limit 1",100));
//    	 System.out.println(DBHelper.getDBHelper("mes").query(true,"select * from gmms_galss_task where width>? limit 1",false));
//    	 System.out.println(DBHelper.getDBHelper("mes").update("insert into abc (a,b,c) values (?,?,?)", 1,"2","3"));
	}
}