/*
 * Created on 10-Aug-2011
 *
 ** /star/java/bin/java -Djava.net.preferIPv4Stack=true -classpath .:WFAUweb/trunk/JavaClassLib/MISC/jtds-1.1.jar:json-jena-1.0.jar:wfau.jar uk.ac.roe.wfau.WebStats
 * TODO 
 */
package uk.ac.roe.wfau;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collection;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.TreeMap;

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

/**
 * @author mar
 *
 */
public class WebStats {
	  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!");
	        }
	    }
	  
	  static String getQueryStr(int queryType,String tablename) {
	      if (queryType==1) {
	      return "select cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' as x,COUNT(*) as y " +
			" from "+tablename+ 
			" where dbname not like 'v%' and dbname not like '%orion%' and dbname not like '%ngc%' " +
			" and ipaddress not like '195.194%' "+
			" group by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' " +"";
	      }
	      if (queryType==2) {
	      return "select cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' as x,sum(cast(row_count as float)) as y " +
			" from "+tablename+ 
			" where dbname not like 'v%' and dbname not like '%orion%' and dbname not like '%ngc%' " +
			" and ipaddress not like '195.194%' "+
			" group by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' " +"";
	      }
	      if (queryType==3) {
		      return "select cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' as x,COUNT(*) as y " +
				" from "+tablename+ 
				" where (dbname like 'vhs%' or dbname like 'vmc%' or dbname like 'vvv%' or " +
				" dbname like 'viking%' or dbname like 'video%' )" +
				" and ipaddress not like '195.194%' "+
				" group by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' " +"";
		      }
	      if (queryType==4) {
	          return "select cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' as x,sum(cast(row_count as float)) as y " +
				" from "+tablename+ 
				" where (dbname like 'vhs%' or dbname like 'vmc%' or dbname like 'vvv%' or " +
				" dbname like 'viking%' or dbname like 'video%' )" +
				" and ipaddress not like '195.194%' "+
				" group by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' " +"";
		      }
	      return null;
	  }
    public static void main(String[] args) {
        System.out.println("Usage archiveId 1:WSA 2:VSA, query 1:no queries 2:rows from quries, sa passwd");
        
        int archiveID=VDFSSchema.WSAARCHIVEID;
        String username="sa";
        String sapasswd=args[2];
        String database="userinterface";
        String [] servers=null;
        String [] tables=null;
        String [] wsaservers={"ramses5","ramses6","ramses7","ramses8","ramses5","ramses5"};
        String [] wsatables={"webqueries","webqueries","webqueries","webqueries","amenhotep261109_webqueries","thutmose261109_webqueries"};
        String [] vsatables={"webqueries","webqueries","webqueries","webqueries"};
        String [] vsaservers={"ramses3","ramses4","ramses7","ramses8"};
        int qType=1;
            try {
                archiveID=Integer.parseInt(args[0]);
                qType=Integer.parseInt(args[1]);
            } catch (NumberFormatException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        
            switch (archiveID) {
            
            case VDFSSchema.WSAARCHIVEID:                
                tables=wsatables;
                servers=wsaservers;
                break;
            case VDFSSchema.VSAARCHIVEID:                
                tables=vsatables;
                servers=vsaservers;
                break;
            default:
                break;
            }
            TreeMap noQueries=new TreeMap();
                //String [] servers ={"ramses5","ramses6","ramses7","ramses8","ramses5","ramses5"};
                //String [] tables = {"webqueries","webqueries","webqueries","webqueries","amenhotep261109_webqueries","thutmose261109_webqueries"};
                for (int  i=0; i<servers.length;i++) {                    
                String dburl=   "jdbc:jtds:sqlserver://"+servers[i]+":1433/"+database+";user=sa;password="+sapasswd;
                System.out.println(dburl);	
                try {
                    Connection con=DriverManager.getConnection(dburl);
                    Statement stmt=con.createStatement();
                    ResultSet rs=stmt.executeQuery(getQueryStr(qType,tables[i]));
//                    ResultSet rs=stmt.executeQuery("select cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' as x,COUNT(*) as y " +
//				"from "+tables[i]+ 
//				" where dbname not like 'v%' and dbname not like '%orion%' and dbname not like '%ngc%' " +
//				" and ipaddress not like '195.194%' "+
//				" group by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15' " +"");
	//			"order by cast(YEAR(time) as varchar(4)) +'-'+right('00'+cast(MONTH(time) as varchar(2)),2)+'-15'");
                    while (rs.next()) {
                        String month=rs.getString(1);
                        long noQ=rs.getLong(2);
                        System.out.println(month+" noQ   "+noQ);
                        if (noQueries.containsKey(month)) {
                            System.out.println("already in");
                            noQ=noQ+((Long)noQueries.get(month)).longValue();
                        }
                        System.out.println(month+" : "+noQ);
                        noQueries.put(month,new Long(noQ));
                    }
                    con.close();
                    
                  
                } catch (SQLException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                }
                JSONArray json = new JSONArray();
                
                
                Iterator iterator = noQueries.keySet().iterator(); 
                while (iterator.hasNext()) {
                    JSONObject obj = new JSONObject();
                    String key = iterator.next().toString();   
                    String value = noQueries.get(key).toString();   
                    try {
                        obj.put("x", key);
                        obj.put("y", Math.max(1L,((Long)noQueries.get(key)).longValue()));
                    } catch (JSONException e1) {
                        // TODO Auto-generated catch block
                        e1.printStackTrace();
                    }
                    json.put(obj);  
                    System.out.println(key + " :: " + value);   
                 } 
                System.out.println(json.toString());
      
        
    }
}