package com.sql;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.GregorianCalendar;
import java.util.Properties;

import com.base.Com;
import com.config.AppConfig;

public class Sql_Sybase 
{
	public final static int SQL_TYPE_SYBASE_DEMO = 0;
	public final static int SQL_TYPE_SYBASE = 1;
	public final static int SQL_TYPE_MSSQLSERVER = 2;
	private int m_SQL_TYPE = SQL_TYPE_SYBASE_DEMO;
	//´´½¨Êý¾Ý¿âÁ¬½Ó³Ø
	//private ComboPooledDataSource sybase_ds = new ComboPooledDataSource();
	
	private String mSQLServerIp = "127.0.0.1";
	private String mSQLServerUsrName = "sxfg001";
	private String mSQLServerPWD = "sxfg001";
	private int mSQLServerPort = 5000;
	
	private ResultSet sql_res = null;
	private Connection sql_conn = null;
	
	public Sql_Sybase(AppConfig cfg)
	{
		m_SQL_TYPE = cfg.getSourceSQLServerType();
		mSQLServerIp = cfg.getSourceSQLServerIp();
		mSQLServerUsrName = cfg.getSourceSQLServerUsrName();
		mSQLServerPWD = cfg.getSourceSQLServerPWD();
		mSQLServerPort = cfg.getSourceSQLServerPort();
		
		sql_conn = getConnection();
	}
	
	public int get_SQL_TYPE()
	{
		return m_SQL_TYPE;
	}
	
	public String getQueryVolString(String st_id, GregorianCalendar gc_start, 
									GregorianCalendar gc_end, String time_order)
	{
		String str = "";
		if(SQL_TYPE_SYBASE == m_SQL_TYPE)
		{
			str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
				+ " HistoryBattery" + (gc_start.get(GregorianCalendar.MONTH) + 1)
				+ " WHERE "
				+ " StationId=" + st_id
				+ " AND RecordTime>'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
			 	+ " AND RecordTime<='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
			 	+ " ORDER BY RecordTime " + time_order;
		}
		else if(SQL_TYPE_SYBASE_DEMO == m_SQL_TYPE)
		{
			str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
				+ " HistoryBattery" + (gc_start.get(GregorianCalendar.MONTH) + 1)
				+ " WHERE "
				+ " StationId='" + st_id + "'"
				+ " AND RecordTime>'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
			 	+ " AND RecordTime<='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
			 	+ " ORDER BY RecordTime " + time_order;
		}
		else if(SQL_TYPE_MSSQLSERVER == m_SQL_TYPE)
		{
			str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
					+ " TBL_HistorySignal" + (gc_start.get(GregorianCalendar.MONTH) + 1)
					+ " WHERE "
					+ " StationId=" + st_id
					+ " AND SampleTime>'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
				 	+ " AND SampleTime<='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
				 	+ " ORDER BY SampleTime " + time_order;
		}
		return str;
	}
	
	
	/**
	 * ¸ù¾Ý¿ªÊ¼Ê±¼äºÍ½áÊøʱ¼äÒÔ¼°id»ñÈ¡ÏàÓ¦µÄ²éѯÓï¾ä
	 * @param st_id
	 * @param gc_start
	 * @param gc_end
	 * @param time_order
	 * @return
	 */
	public String getQueryCurrentString(String st_id, GregorianCalendar gc_start, 
											GregorianCalendar gc_end, String time_order)
	{
		String str = "";
		if(SQL_TYPE_SYBASE == m_SQL_TYPE)
		{
			str = "SELECT StationId,EquipmentId,SignalId,FloatValue FROM "
				+ " HistorySignal" + (gc_start.get(GregorianCalendar.MONTH) + 1)
				+ " WHERE "
				+ " StationId=" + st_id
				+ " AND RecordTime >'" + Com.getDateTimeFormat(gc_start.getTime(), Com.DTF_YMDhms) + "'"
			 	+ " AND RecordTime <='" + Com.getDateTimeFormat(gc_end.getTime(), Com.DTF_YMDhms) + "'"
			 	+ " ORDER BY RecordTime " + time_order;
		}
		
		return str;
	}
	
	public Connection getConnection(){
		Connection con = null;
		if((SQL_TYPE_SYBASE_DEMO == m_SQL_TYPE) || (SQL_TYPE_SYBASE == m_SQL_TYPE))
		{
			try {
				Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();
				String url = "jdbc:sybase:Tds:" + mSQLServerIp + ":" + mSQLServerPort + "/SiteWeb"; 
		        Properties sysProps = System.getProperties();
		        sysProps.put("user", mSQLServerUsrName);
		        sysProps.put("password", mSQLServerPWD);
		        
				con = DriverManager.getConnection(url, sysProps);
			} catch (InstantiationException | IllegalAccessException
					| ClassNotFoundException | SQLException e) {
				//e.printStackTrace();
				//System.err.println(e.getMessage());
			}
		}
		else if(SQL_TYPE_MSSQLSERVER == m_SQL_TYPE)
		{
			try {
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
				String url = "jdbc:sqlserver://" + mSQLServerIp + ";DatabaseName=SiteWeb";
		        /*
		        String userName = "fuguang";
		        String userPwd = "jian(12)";
		        */
		        con = DriverManager.getConnection(url, mSQLServerUsrName, mSQLServerPWD);
			} catch (ClassNotFoundException | SQLException e) {
				//e.printStackTrace();
			}
		}
		
		return con;
	}
	
	/*
	public class sybaseThread extends Thread 
	{
		boolean dres;
		public sybaseThread(boolean res)
		{
			dres = res;
		}
		
	    @SuppressWarnings("deprecation")
		@Override
	    public void run() 
	    {
		    try 
		    {
		    	long count_tr = 0;
		    	System.out.println(Thread.currentThread().getName());
		    	while(true)
			    {
		    		count_tr++;
	    			Connection conn = sql_conn;//sybase_ds.getConnection();//getConnection();//
	    			Statement sql = conn.createStatement();//conn .createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
	    			sql.setQueryTimeout(10);
	    			String sqlstr = "select * from tb_battdata"; // ±í"sp_getTestData";//
		            ResultSet rs = sql.executeQuery(sqlstr);
		            
		            while(rs.next())
		            {
	    				System.out.println(Thread.currentThread().getName() + " " + count_tr + " "
	    									+ rs.getString("StationId") + " " 
	    									+ rs.getString("StationName") + " " 
	    									+ rs.getTimestamp("rc_time").toLocaleString() +" "//.format(DateTimeFormatter.ofLocalizedDateTime(FormatStyle.FULL)) + " " 
	    									+ rs.getString("SignalName") + " " 
	    									+ rs.getString("SignalValue") + " " 
	    									);
	    			}
		            
		            sql.close();
	    			conn.close();
	    			
		            //Thread.sleep(10);
	            }
		    }
		    catch (Exception e) 
		    {
		    	System.out.println(e.getMessage());
		    }
		}
	}
	*/
	public ResultSet getSqlres()
	{
		return sql_res;
	}
	public void close_conn() throws SQLException
	{
		if(null != sql_conn)
		{
			sql_conn.close();
			sql_conn = null;
		}
	}
	public boolean get_conn_state()
	{
		if(null == sql_conn)
			return false;
		else return true;
	}
	
	/**
	 * ¸ù¾Ý²éѯÓï¾ä»ñÈ¡ ResultSet ¼¯ºÏ
	 * @param sql_str ²éѯÓï¾ä
	 * @return			ResultSet¼¯ºÏ
	 * @throws SQLException
	 */
	public ResultSet sqlSybaseQuery(String sql_str) throws SQLException 
	{
		Statement sql = sql_conn.createStatement();
		sql.setQueryTimeout(30);
		ResultSet rs = sql.executeQuery(sql_str);
		return rs;
    }
	
	public void sqlSybaseExecute(String str) throws SQLException 
	{
		Statement sql = sql_conn.createStatement();
		sql.setQueryTimeout(30);
		sql.execute(str);
    }
}