package uk.ac.roe.wfau;

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.sql.Types;

import org.json.JSONArray;
import org.json.JSONObject;

import com.sun.org.apache.bcel.internal.generic.GETSTATIC;

;

public class ResultSetJSON {
	  static {
	        // Load in the Microsoft-provided JDBC SQL Server driver
	        try {
	        //    Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
	            Class.forName("net.sourceforge.jtds.jdbc.Driver");

	        } catch (ClassNotFoundException e) {
	            // A class not found error means the SQL driver class could not be
	            // found.
	            // Which means that this driver would not be available until this is
	            // remedied. This flag variable will signal this to the class!

	            System.err.println("SQLRetrieverThread::static(): Could not load SQLServerDriver class!");
	        }
	    }

	/**
	 * @param args
	 */
	public static String getJSON(ResultSet rs) {
		JSONArray json = new JSONArray();
		ResultSetMetaData rsmd;
		
		try {
			rsmd = rs.getMetaData();

		int numColumns = rsmd.getColumnCount();	
		String [] column_names = new String [numColumns];
		for (int i=1;i<numColumns+1; i++) {
			column_names[i-1]=rsmd.getColumnName(i);
			System.out.println(column_names[i-1]);
		}
	    while(rs.next()){                 
	    	JSONObject obj = new JSONObject(); 
	    	for (int i=1; i<numColumns+1; i++) {                     
	    					switch(rsmd.getColumnType(i)){
				case Types.TINYINT :
					obj.put(column_names[i-1], rs.getInt(i));
				break;
				case Types.SMALLINT :
					obj.put(column_names[i-1], rs.getInt(i));
				break;
				case Types.REAL :
					obj.put(column_names[i-1], rs.getFloat(i));
					break;
				case Types.FLOAT :
					obj.put(column_names[i-1], rs.getDouble(i));
					break;
				case Types.DOUBLE :
					obj.put(column_names[i-1], rs.getDouble(i));
					break;
				case Types.INTEGER :
					obj.put(column_names[i-1], rs.getInt(i));
					break;
				case Types.VARCHAR :
					obj.put(column_names[i-1], rs.getString(i));
					break;
				case Types.CHAR :
					obj.put(column_names[i-1], rs.getString(i));
					break;
				case Types.BIGINT :
					obj.put(column_names[i-1], rs.getLong(i));
					break;
				case Types.DATE :
					obj.put(column_names[i-1], rs.getDate(i));
					break;
				case Types.NUMERIC :
					obj.put(column_names[i-1], rs.getDouble(i));
					break;
				case Types.DECIMAL :
					obj.put(column_names[i-1], rs.getDouble(i));
					break;
				default :
					obj.put(column_names[i-1], rs.getString(i));
					break;
			}
	    	}
	    	json.put(obj);
    
	    }
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		System.out.println(json.toString());
		
		return null;
	}
	public static void main(String[] args) {
		// TODO Auto-generated method stub
		//String loc="jdbc:jtds:sqlserver://localhost:1433/testmar;user=sa;password=monkey";
	    String sapasswd=args[0];
	    String loc="jdbc:jtds:sqlserver://ramses5:1433/userInterface;user=sa;password="+sapasswd;
		Connection con;
		try {
			con = DriverManager.getConnection(loc);
		
		Statement stmt=con.createStatement();
		ResultSet rs=stmt.executeQuery("select cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-01' as x,COUNT(*) as y " +
				"from webQueries group by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-01' " +
				"order by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-01'");
		getJSON(rs);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

}