/***************************************************************************************************

  the array queryFlags store which attribute the user wants 
  flag [0] = name
  flag [1] = ra / dec
  flag [2] = ra / dec in radians
  flag [3] = bj 
  flag [4] = ubj
  flag [5] = bjr
  flag [6] = z1
  flag [7] = q1
  flag [8] = snr
  flag[9] = absolute magnitude
  
****************************************************************************************************/
package voi.db;
import java.io.*;
import java.sql.*;
import java.net.URL;
import java.util.*;
import java.awt.*;
import javax.swing.*;
import voi.routines.*;

/**
This class is used to connect to the 2qz Mysql database to get the requested data.
It also uses a class called ResultsModel which is a method of displaying the results
in tabular format using a JTable and a JScrollPane.
**/
public class MysqlConnect
{
	private JScrollPane resultsPane;
	private ResultsModel model;
	private ResultSet rs;
	
	private final String SERVER_NAME = "localhost";
	private final String DATABASE = "test";
	private final String URL = "jdbc:mysql://" + SERVER_NAME + "/" + DATABASE;		
	private final String USER_NAME = "root";
	private final String PASSWORD = "jayant123";
	private final int ABS_MAG = 9;
	
	/**
	this module accepts three arguments
	1) attrlist : This is a string containing the required attributes seperated by commas
	2) constraints : This is a string containing the required constraints seperated by commas
	**/
	public JScrollPane submitQuery (String attrList, String constraints, boolean queryFlags [])
	{
		double bj =0, z1 = 0;  		// these two attributes are reqd for AbsMagnitude calculation
		model = new ResultsModel ();
                JTable table = new JTable (model);
		
		try
		{
			// set up the connection with the Mysql server 
			com.mysql.jdbc.Driver d = (com.mysql.jdbc.Driver) Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			
			// Set up a MySQL connection
			Connection newCon = setUpConnection ();
			
			// create resultsPane and add the table to it 
                        table.setAutoResizeMode (JTable.AUTO_RESIZE_ALL_COLUMNS);
			resultsPane = new JScrollPane (table);
                        Statement stmt = newCon.createStatement ();
			
			// create the query and fire
			String query = getQuery (attrList, constraints); 
			rs = stmt.executeQuery (query);
				       
			// create the setResultSet of the ResultsModel object to add the query results (rs) to the JScrollPane	       
			int number = (queryFlags [ABS_MAG]) ? 1 : 0;	      
                        model.setResultSet(rs, queryFlags , number);    // 1 refers to the number of parameters

		}
		catch (SQLException ex) 
		{
		    // handle any errors
		    System.out.println (" There was an exception in the MySQL Query ");
		}
		catch (Exception e)
		{
			System.out.println(e); 
			e.printStackTrace();
		}
		resultsPane.validate ();
		return resultsPane; 					// return the resultsPane
	}
	
	public JScrollPane submitQuery (String attrList, String constraints)
	{
		model = new ResultsModel ();
                JTable table = new JTable (model);
		
		try
		{
			// set up the connection with the Mysql server 
			com.mysql.jdbc.Driver d = (com.mysql.jdbc.Driver) Class.forName ("com.mysql.jdbc.Driver").newInstance ();
			
			// Set up a MySQL connection
			Connection newCon = setUpConnection ();
			
			// create resultsPane and add the table to it 
                        table.setAutoResizeMode (JTable.AUTO_RESIZE_ALL_COLUMNS);
			resultsPane = new JScrollPane (table);
                        Statement stmt = newCon.createStatement ();
			
			// create the query and fire
			String query = getQuery (attrList, constraints); 
			rs = stmt.executeQuery (query);
				       
			// create the setResultSet of the ResultsModel object to add the query results (rs) to the JScrollPane
			/* Removed to get the result of a normal SQL query. Query flags is redudndant here
			int number = (queryFlags [ABS_MAG]) ? 1 : 0;
			*/
                        model.setResultSet(rs);    // 1 refers to the number of parameters
		}
		catch (SQLException ex) 
		{
		    // handle any errors
		    System.out.println (" There was an exception in the MySQL Query ");
		}
		catch (Exception e)
		{
			System.out.println(e); 
			e.printStackTrace();
		}
		resultsPane.validate ();
		return resultsPane; 					// return the resultsPane
		
	}
		
	
	/**
	Returns the ResultSet object to be used by TdfToolBar object
	*/	
	public ResultSet getResults ()
	{
		return rs;
	}
	
	/**
	This functions sets up the initial connections
	*/
	public Connection setUpConnection ()
	{
		try
		{
			return DriverManager.getConnection (URL,USER_NAME,PASSWORD);
		}
		catch (SQLException e)
		{
			System.out.println ("Error setting up a connection ");
		}
		return null;
	}
	
	
	/**
	This constructs the query from the attrlist and constraints parameters
	*/
	public String getQuery (String attrList, String constraints)
	{
		String query = "";
		if (constraints.equals (""))
		{
                        query = "SELECT " + attrList + " FROM tqz";
		}
		else
		{
			query = "SELECT " + attrList + " FROM tqz WHERE " + constraints;
		}
		return query;
	}
	
	public boolean validQuery ()
	{
		if (rs != null) return true;
		return false;
	}
		
}