package statistic;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Date;
import java.sql.Time;
import java.util.StringTokenizer;
import org.jfree.data.category.DefaultCategoryDataset;
import org.jfree.data.general.DefaultPieDataset;
import org.jfree.data.time.Day;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;
import org.jfree.data.xy.XYDataset;


/**
 * JDBC訪問Service
 *
 */
public class DatabaseService
{	
	/**
	 * 建立到數據庫的連接
	 */
	public static Connection getConnection()
	{
		try
		{
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/statisticDB","root","root");
			
			return connection;
		}
		catch(Exception e)
		{
			 e.printStackTrace();
			 
			 return null;
		}
	}
	
	/**
	 * 記錄客戶訪問
	 */
	public static void visit(String ip, String os, String browser, String pageName)
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
        
        try {
        	//獲取連接
        	connection = DatabaseService.getConnection();
        	pStatement = connection.prepareStatement("insert into access values(?,?,?,?,?,?,?)");

        	//下面分別填充參數
        	pStatement.setInt(1, 0);
        	pStatement.setString(2, ip);
        	pStatement.setString(3, os.toUpperCase());
        	pStatement.setString(4, browser.toUpperCase());
        	pStatement.setString(5, pageName.toUpperCase());
        	pStatement.setDate(6, new Date(System.currentTimeMillis()));
        	pStatement.setTime(7, new Time(System.currentTimeMillis()));
        	
        	//更新數據庫
        	pStatement.executeUpdate();
        }catch (Exception e) {
            e.printStackTrace();
        }
        finally {
        	try	{
        		//關閉數據庫連接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}
	
	/** 
	 * 查詢獲取操作系統類型統計所需的數據集 DefaultPieDataset 對象
	 * 
	 */
	public static DefaultPieDataset getBrower()
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
	    ResultSet resultset = null;
	    
	    DefaultPieDataset dataset = new DefaultPieDataset();
	    
	    try {
        	//獲取連接
        	connection = DatabaseService.getConnection();
        	String sql = "select brower,count(brower) from access group by brower";
        	pStatement = connection.prepareStatement(sql);
        	resultset=pStatement.executeQuery();
        	
        	while(resultset.next())
        	{
        		dataset.setValue(resultset.getString(1), resultset.getInt(2));
        	}
        	
        	return dataset;

	    }catch (Exception e) {
            e.printStackTrace();
            
            return null;
        }
        finally {
        	try	{
        		//關閉數據庫連接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}	
	
	/** 
	 * 查詢獲取頁面訪問統計所需的數據集 DefaultCategoryDataset 對象
	 * 
	 */
	public static DefaultCategoryDataset getPage()
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
	    ResultSet resultset = null;
	    
	    DefaultCategoryDataset dataset = new DefaultCategoryDataset();
	    
	    try {
        	//獲取連接
        	connection = DatabaseService.getConnection();
        	String sql = "select page,count(page) from access group by page ASC";
        	pStatement = connection.prepareStatement(sql);
        	resultset=pStatement.executeQuery();
        	
        	while(resultset.next())
        	{
        		dataset.addValue(resultset.getInt(2),resultset.getString(1), resultset.getString(1));
        	}
        	
        	return dataset;

	    }catch (Exception e) {
            e.printStackTrace();
            
            return null;
        }
        finally {
        	try	{
        		//關閉數據庫連接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}
	
	/** 
	 * 查詢獲取每日流量統計所需的數據集 XYDataset 對象
	 * 
	 */
	public static XYDataset getTime()
	{
        Connection connection = null;
        PreparedStatement pStatement = null;
	    ResultSet resultset = null;
	    
        TimeSeriesCollection dataset = new TimeSeriesCollection();
        //時間序列對象集合，里面可以存放多個TimeSeries對象，
        //TimeSeriesCollection實現了XYDataset接口
        TimeSeries timeseries = new TimeSeries("每日訪問", Day.class);
        //時間序列對象，第一個參數表示時間序列的名字，第二個參數是時間類型，這里為天
        //該對象用于保存前count天每天的訪問次數
	    
	    try {
        	//獲取連接
        	connection = DatabaseService.getConnection();
        	String sql = "select accessDate,count(accessDate) from access group by accessDate ASC";
        	pStatement = connection.prepareStatement(sql);
        	resultset=pStatement.executeQuery();
        	
        	while(resultset.next())
        	{
                StringTokenizer st = new StringTokenizer(resultset.getString(1), "-");
                //從日期字符串str中，獲取年、月、日
                int year = Integer.parseInt(st.nextToken());
                int month = Integer.parseInt(st.nextToken());
                int day = Integer.parseInt(st.nextToken());
                timeseries.add(new Day(day, month, year), resultset.getInt(2));
                //將前面第(count - i - 1)天的訪問次數保存到時間序列對象中
        	}
        	dataset.addSeries(timeseries);
        	
        	return dataset;

	    }catch (Exception e) {
            e.printStackTrace();
            
            return null;
        }
        finally {
        	try	{
        		//關閉數據庫連接
        		connection.close();
        	}catch(Exception ee){
        		ee.printStackTrace();
        	}
        }
	}
	
	public static void main(String[] arg)
	{
		DefaultPieDataset dataset=DatabaseService.getBrower();		
		System.out.println(dataset.getItemCount());
		
		DefaultCategoryDataset dataset1=DatabaseService.getPage();
		System.out.println(dataset1.getColumnCount());
	}
	
	
}