/*
 * copy for testing purposes
 */

import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.Calendar;
import java.util.Enumeration;
import java.util.Properties;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import net.mar.FormatRS;

import org.apache.log4j.Logger;

import uk.ac.roe.wfau.WSAHTMLSchema;
import uk.ac.roe.wfau.WSASchema;
import uk.ac.roe.wfau.stack2string;

import com.javaexchange.dbConnectionBroker.DbConnectionBroker;

public class CopyOfImageList extends HttpServlet {
    //private DBConnectionManager connMgr; // previous connection pool
    private DbConnectionBroker myBroker;
    private static final int RUNNUM_UPPERLIMIT = 99999;
    private static int RunNumber = 0; // 'Unique' counter
    private static String tempPath;
    private static String progPath;
    private static String tempURL;
    private static Logger logger;
    private static String filePrefix;
    private static String selectBase;
    private static String selectAll;
    private static String fromBase;
    private static String whereBase;
    private static String dateParameter;
   

    private static synchronized int getNextRunNumber() {
        RunNumber++;

        if (RunNumber > RUNNUM_UPPERLIMIT) {
            RunNumber = 1;
        }
        ;

        return RunNumber;
    };

    public void init() throws ServletException {
        //super.init(conf);
        logger=Logger.getLogger("wsa.simple");
        logger.debug("initailizing Imagelist servlet");
        // read in global properties
        Properties properties = new Properties();
        String path = getServletContext().getRealPath("/");
        try {
            properties.load(new FileInputStream(path
                    + "WEB-INF/classes/ImageList.properties"));

        } catch (IOException e) {
            logger.error("Can't find properties file ");
            return;
        }
        String server = properties.getProperty("server");
        String user = properties.getProperty("user");
        String passwd = properties.getProperty("passwd");
        String database = properties.getProperty("database");
        String poolLogfile = properties.getProperty("pool.logfile");
        tempPath = properties.getProperty("tempPath");
        progPath = properties.getProperty("progPath");
        tempURL = properties.getProperty("tempURL");
        filePrefix = properties.getProperty("filePrefix");
        selectBase = properties.getProperty("select"); 
        selectAll = properties.getProperty("selectAll");
        fromBase = properties.getProperty("from");
        whereBase = properties.getProperty("where");
        dateParameter=properties.getProperty("dateParameter");
        try {
            myBroker = new DbConnectionBroker(
                    "com.microsoft.jdbc.sqlserver.SQLServerDriver",
                    "jdbc:microsoft:sqlserver://" + server
                            + ":1433;DatabaseName=" + database, user, passwd,
                    2, 10, poolLogfile, 0.25, false, 60, 1);
        } catch (IOException e) {
            logger.error("DBConnectionBroker I/O error "+stack2string.getString(e));
        }
        
    }

    static {

        try {
            Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
        } 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!
            logger.error("SQLRetrieverThread::static(): Could not load SQLServerDriver class! "+stack2string.getString(e));
          }
    }

    public void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        HttpSession session = req.getSession();
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        
        
String info = (String) session.getAttribute("info");
        
out.println( "value of info state parameter: " + info );
info = (String) session.getAttribute("user");

out.println( "value of info state parameter: " + info );

 
        boolean proceed=true;
        StringBuffer select=new StringBuffer(selectBase);
        StringBuffer from=new StringBuffer(fromBase);
        StringBuffer where=new StringBuffer(whereBase);
        String userup=(String)session.getAttribute("user");
        out.println(userup+"<p>here"); 
        if (1 != 2)
        {
            return;
        }
        /*
        Enumeration enum = session.getAttributeNames();
        while (enum.hasMoreElements()){
            String name=(String)enum.nextElement();
            out.println(name+":"+session.getAttribute(name)+"<br>");
        }*/
        out.println("Searching...<br>");
 
        String userSelect=req.getParameter("userSelect");
        if (userSelect != null){
            if (userSelect.equals("all")){
                select=new StringBuffer(selectAll);
            }
        }
        String programmeID=req.getParameter("programmeID");
        String filterID=req.getParameter("filterID");
        String minRA=req.getParameter("minRA");
        String maxRA=req.getParameter("maxRA");
        String minDec=req.getParameter("minDec");
        String maxDec=req.getParameter("maxDec");
        String startYear=req.getParameter("startYear");
        String startMonth=req.getParameter("startMonth");
        String startDay=req.getParameter("startDay");
        String endYear=req.getParameter("endYear");
        String endMonth=req.getParameter("endMonth");
        String endDay=req.getParameter("endDay");
        if (programmeID != null){
            out.println("<b>Survey: </b>"+WSASchema.getSurveyName(Integer.valueOf(programmeID).intValue())+"<br>");
            where.append(" and (PF.programmeID="+programmeID+")");
        }
        if (filterID != null  & !filterID.equals("all")){
            out.println("<b>Waveband: </b>"+WSASchema.getFilterName(Integer.valueOf(filterID).intValue())+"<br>");
            where.append(" and (M.filterID="+filterID+")");
        }   
        if (minRA != null && maxRA != null){
            try {
            float minra=Float.valueOf(minRA).floatValue();
            float maxra=Float.valueOf(maxRA).floatValue();
 
            if (minra > maxra) {
                where.append(" and ((RAbase >= " + minRA +" and RAbase <=24.0) or " +
                		"(RAbase <= "+maxRA+" ))");
            }
            else {
                where.append(" and (RAbase >= "+minRA+" and RABase <= "+maxRA+") ");
            }
            out.println("<b>Minimum RA:</b> "+minRA+" <b>Maximum RA:</b> "+maxRA+"<br>");
            if (minra < 0.0 || maxra > 24.0 || minra > 24.0 || maxra < 0){
                out.println("<b>Warning: The supplied RAs should be between 0.0 and 24.0 hours</b><br>");
                proceed=false;
                }
            }
            
            catch (Exception e){
                // user can't enter a number
            }
        }
        if (minDec != null && maxDec != null){
            try {
            float decMin=Float.valueOf(minDec).floatValue();
            float decMax=Float.valueOf(maxDec).floatValue();
            
            where.append(" and (Decbase >= " + minDec +" and Decbase <= " + maxDec+" ) ");
            out.println("<b>Minimum Dec:</b> "+minDec+" <b>Maximum Dec:</b> "+maxDec+"<br>");                
            if (decMin > decMax){
                out.println("<b>Warning: min Dec &gt; max Dec.</b><br>");
                proceed=false;
            }
            }
            catch (Exception e){
                // user can't enter a number
            }
        }
        int maxDay=31;
        Calendar cal = Calendar.getInstance();
        cal.clear();
        int year = Integer.valueOf(startYear).intValue();
        int month = Integer.valueOf(startMonth).intValue();
        int day = Integer.valueOf(startDay).intValue();
        long startTime=0;
        long endTime=0;
        
        if (year !=0 && month !=0 && day !=0){
        cal.set(year,month-1,1);
        maxDay=cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        
        if (day > maxDay){
            cal.set(year,month-1,maxDay);
                    }
        else {
            cal.set(year,month-1,day);
        }
        startTime=cal.getTimeInMillis();
        String startSQLDate = DateFormat.getDateInstance(DateFormat.LONG).format(cal.getTime());
        where.append(" and ("+dateParameter+" >= '"+startSQLDate+"') ");
        out.println("<b>Start date:</b>" +startSQLDate+"<br>");
        }
        cal.clear();
         year = Integer.valueOf(endYear).intValue();
         month = Integer.valueOf(endMonth).intValue();
         day = Integer.valueOf(endDay).intValue();
        if (year !=0 && month !=0 && day !=0){
        cal.set(year,month-1,1);
        maxDay=cal.getActualMaximum(Calendar.DAY_OF_MONTH);
        
        if (day > maxDay){
            cal.set(year,month-1,maxDay);
                    }
        else {
            cal.set(year,month-1,day);
        }
        endTime=cal.getTimeInMillis();
        String endSQLDate = DateFormat.getDateInstance(DateFormat.LONG).format(cal.getTime());
        where.append(" and ("+dateParameter+" <= '"+endSQLDate+"') ");
        out.println("<b>End date:</b>" +endSQLDate+"<br>");
        }

        if (startTime > endTime && endTime !=0 ){
            out.println("<b>Warning: start date is after end date.</b>");
            proceed=false;
        }
        
               StringBuffer sql=new StringBuffer("");
        
        sql.append("select "+select+" from "+from+" where "+where);

      
        Connection con = null;
        ResultSet rs = null;
        Statement stmt = null;
        
 
        try {            
            con = myBroker.getConnection();
            
            if (con == null) {
                out.println("Can't get database connection");
                return;
            }

            ResultSetMetaData md = null;
            try {
                stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
                
                //stmt.executeUpdate("use wsa");

                //            rs = stmt.executeQuery("SELECT
                // MultiFrameDetector.extNum,MultiFrame.filename,CurrentAstrometry.*,
                // MultiframeDetector.axis1Length,MultiframeDetector.axis2Length,numaxes
                // FROM Multiframe INNER JOIN MultiframeDetector ON
                // Multiframe.multiframeID = MultiframeDetector.multiframeID
                // INNER JOIN CurrentAstrometry ON Multiframe.multiframeID =
                // CurrentAstrometry.multiframeID AND MultiframeDetector.extNum
                // = CurrentAstrometry.extNum");
   
                rs = stmt.executeQuery(sql.toString());
                md = rs.getMetaData();
               /* rs.afterLast();
                rs.last(); 
                rs.absolute(10);
*/
                int records = rs.getRow(); 
               // out.println(records);
                FormatRS frs=new FormatRS();
                frs.setRSMD(md);
                int row = 0;
                int filenameColumn=0;
                String fileName="";
                int multiframeidColumn=0;
                int toGoColumn=0;
                int catnameColumn=0;
                int numDetectorsColumn=0;
                String[] strArray;
                int resultsNumColumns = md.getColumnCount();
                strArray = new String[resultsNumColumns];
                out.println("<table border=\"1\"><tr bgcolor=\""+WSAHTMLSchema.headRow+"\"><td>Link</td>");
                for (int column = 1; column <= resultsNumColumns; column++){
                    if (md.getColumnName(column).equalsIgnoreCase("filename")){
                        filenameColumn=column;
                    }
                    else if (md.getColumnName(column).equalsIgnoreCase("numDetectors")){
                        numDetectorsColumn=column;
                        out.println("<td align=\"middle\"><b>"+md.getColumnName(column)+"</b></td>");
   
                    }
                    else if (md.getColumnName(column).equalsIgnoreCase("toGo")){
                        toGoColumn=column;  
                    }
                    else if (md.getColumnName(column).equalsIgnoreCase("catname")){
                        catnameColumn=column;
                     }
                    else if (md.getColumnName(column).equalsIgnoreCase("multiframeid")){
                        multiframeidColumn=column;
                        out.println("<td align=\"middle\"><b>"+md.getColumnName(column)+"</b></td>");
                     }
                    else {    
                        
                    out.println("<td align=\"middle\"><b>"+md.getColumnName(column)+"</b></td>");
                }
            }
                out.println("</tr>");
                   String link=null;
                   String trimString="";
                while (rs.next()) {
                    row=row+1;
                    if (row % 2 == 0){
                        out.println("<tr bgcolor="+WSAHTMLSchema.evenRow+">");
                    }
                    else {
                    out.println("<tr bgcolor="+WSAHTMLSchema.oddRow+">");
                    }
                    strArray = frs.setRS(rs);
                    if (row >=0){
                        out.println("<td><a href=\"http://surveys.roe.ac.uk/~mar/cgi-bin/display.cgi?file=" +
                        		rs.getString(filenameColumn)+"&noExt="+
                        		rs.getString(numDetectorsColumn)+"&MFID="+
                        		rs.getString(multiframeidColumn)+
                        		"\" target=display>link</a></td> ");
            
                    } else {
                    out.println("<td>&nbsp;</td> ");
                    }
                    for (int column = 0; column < resultsNumColumns; column++){
                        if (column+1==toGoColumn || column+1==catnameColumn 
                                || column+1==filenameColumn){
                        } else {
                            trimString=strArray[column].trim();
                            if (!trimString.equals("") && trimString != null){
                    out.println("<td nowrap align=\"right\">"+trimString+"</td>");
                            } else {
                                out.println("<td nowrap align=\"right\">&nbsp;</td>");
                            }
                    }
                    }
                    out.println("</tr>");
                }
                out.println("</table>");         
 
            } catch (SQLException se) {
                out.println("<p>Error querying the database: "
                        + se.getErrorCode() + se);

                if (se.getErrorCode() == 0) {
                    con.close();
                }
            }

            catch (Exception e) {
                out.println("<p>Error" + e);
                //e.printStackTrace(out);
            }

 
        } catch (Exception e) {
            System.out.println("errorher");
            System.out.println(e);
            e.printStackTrace(out);
            if (con != null) {
                System.out.println("con alive");
            }
        } finally {

            System.gc();
            //out.flush();
            //out.close();
            try {
                //Thread.sleep(6000);
                stmt.close();

            } catch (Exception e) {
                ;
            }
            if (con != null) {

                System.out.println("Alive I should close");
                //	connMgr.freeConnection("idb", con);
                myBroker.freeConnection(con);

                System.out.println("closing ");
            }

        }

    }

    public void destroy() {
        //          connMgr.release();
        myBroker.destroy();
        super.destroy();
    }

    protected void doPost(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        // Data received by an HTTP POST is handled the same way
        // as data sent with a HTTP GET request... simply pass on to above...
        doGet(req, res);
    }; // END of doPost()

   
}