import java.io.FileInputStream;
import java.io.IOException;
import java.io.PrintWriter;
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.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 ImageList 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 String user;
    private String passwd;
    private String database;
    private String server;
    private static int rowsPerPage=5;

    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;
        }
 
        server = properties.getProperty("server");
        user = properties.getProperty("user");
        passwd = properties.getProperty("passwd");
        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");
       /* do without pool for now
        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");
            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!
            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();
        
        boolean loginboolean=false;
        try {
        loginboolean=((Boolean)session.getAttribute("login")).booleanValue();
        }
        catch (Exception e){
            loginboolean=false;
        }
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        
        int rID=getNextRunNumber();
        boolean openTime=false;
        boolean proceed=true;
        StringBuffer select=new StringBuffer(selectBase);
        StringBuffer from=new StringBuffer(fromBase);
        StringBuffer where=new StringBuffer(whereBase);
        String userup=(String)session.getAttribute("user");
        

        if (!loginboolean){
            out.println("Not logged in: links will only be returned " +
            		"for frames that are publicly accessible<p>");
        }
        out.println("Archive Listing<p>Searching...<br>");


        String userSelect=req.getParameter("userSelect");
        if (userSelect != null){
            if (userSelect.equals("all")){
                select=new StringBuffer(selectAll);
            }
        }
        String programmeID=req.getParameter("programmeID");
        if (programmeID != null){
            if (programmeID.equalsIgnoreCase("openTime")){
                openTime=true;
                programmeID=req.getParameter("openTimeID");
            }
        }
        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");
        String obsType=req.getParameter("obsType");
        String skipRow=req.getParameter("skipRow");
        int skipRowInt=0;
        if (programmeID != null ){
            out.println("<b>Survey: </b>"+WSASchema.getSurveyName(Integer.valueOf(programmeID).intValue())+"<br>");
            where.append(" and (PF.programmeID="+programmeID+")");
        }
        
        if (skipRow != null){
            try {
            skipRowInt=Integer.valueOf(skipRow).intValue();
            }
            catch (Exception e) {
                skipRowInt=0;
            }
        }
        
        if (obsType != null){
            if (obsType.equalsIgnoreCase("object")) {
                where.append(" and ( "+
                        "obstype NOT LIKE 'BIAS%' AND frametype NOT LIKE 'BIAS%' AND "+
                        "obstype NOT LIKE 'DARK%' AND frametype NOT LIKE 'DARK%' AND "+
                        "obstype NOT LIKE 'SKY%' AND frametype NOT LIKE 'SKY%' AND "+
                        "obstype NOT LIKE 'FOCUS%' AND frametype NOT LIKE 'FOCUS%' AND "+
                        "obstype NOT LIKE 'CONFIDENCE%' AND frametype NOT LIKE 'CONFIDENCE%' AND "+
                        "obstype NOT LIKE '%FLAT%' AND frametype NOT LIKE '%FLAT%'"+
                        ") ");
            }
            else if (obsType.equalsIgnoreCase("bias")){
                where.append(" and ( obstype like 'BIAS%' or frametype like 'BIAS%') ");               
            }
            else if (obsType.equalsIgnoreCase("dark")){
                where.append(" and ( obstype like 'DARK%' or frametype like 'DARK%') ");               
            }
            else if (obsType.equalsIgnoreCase("flat")){
                where.append(" and ( obstype like '%FLAT%' or frametype like '%FLAT%') ");               
            }
            else if (obsType.equalsIgnoreCase("sky")){
                where.append(" and ( obstype like 'SKY%' or frametype like 'SKY%') ");               
            }
            else if (obsType.equalsIgnoreCase("focus")){
                where.append(" and ( obstype like 'FOCUS%' or frametype like 'FOCUS%') ");               
            }
            else if (obsType.equalsIgnoreCase("confidence")){
                where.append(" and ( obstype like 'CONFIDENCE%' or frametype like 'CONFIDENCE%') ");               
            }
            
        }
        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){
            float minra=0.0F;
            float maxra=24.0F;
            try {
            minra=Float.valueOf(minRA).floatValue();
            }            
            catch (Exception e){
                // user can't enter a number
                minRA=null;
            }
            try {
            maxra=Float.valueOf(maxRA).floatValue();
            }            
            catch (Exception e){
                // user can't enter a number
                maxRA=null;
            }
            if (minRA ==null && maxRA == null){
                // do nothing
            }
            else if (minRA == null && maxRA != null) {
                where.append(" and (RAbase <= " +maxRA +")");
            } 
            else if (maxRA == null && minRA != null) {
                where.append(" and (RAbase >= " +minRA +")");
            }
            else {    
            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;
            }          
        }
        
        
        if (minDec != null || maxDec != null){
            float mindec=-90.0F;
            float maxdec=90.0F;
            try {
            mindec=Float.valueOf(minDec).floatValue();
            }
            catch (Exception e){
                // user can't enter a number
                minDec=null;
            }
            try {
            maxdec=Float.valueOf(maxDec).floatValue();
            }
            catch (Exception e){
                // user can't enter a number
                maxDec=null;
            }
            if (minDec ==null && maxDec == null){
                // do nothing
            }
            else if (minDec == null && maxDec != null) {
                where.append(" and (Decbase <= " +maxDec +")");
            } 
            else if (maxDec == null && minDec != null) {
                where.append(" and (Decbase >= " +minDec +")");
            }
            else {
            where.append(" and (Decbase >= " + minDec +" and Decbase <= " + maxDec+" ) ");
            }
            out.println("<b>Minimum Dec:</b> "+minDec+" <b>Maximum Dec:</b> "+maxDec+"<br>");                
            if (mindec > maxdec){
                out.println("<b>Warning: min Dec &gt; max Dec.</b><br>");
                proceed=false;
            }
        }
        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();
            String durl="jdbc:jtds:sqlserver://"+server+":1433/" + database+";user="+user+";password="+passwd;
            //String durl= "jdbc:microsoft:sqlserver://"+server+":1433;selectMethod=cursor;databaseName="+database+";user="+user+";password="+passwd+"";
            con = DriverManager.getConnection(durl);
            
            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 = con.createStatement();
                //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 compFileColumn=0;
                int numDetectorsColumn=0;
                String[] strArray;
                String tableName;
                String columnName;
                String startLetter;
                String glossLink;
                int resultsNumColumns = md.getColumnCount();
                strArray = new String[resultsNumColumns];
                out.println("<p><table border=\"1\"><tr bgcolor=\""+WSAHTMLSchema.headRow+"\"><td>Link</td>");
                for (int column = 1; column <= resultsNumColumns; column++){
                    glossLink="";
                    try {
                    tableName=md.getTableName(column).toLowerCase();
                    columnName=md.getColumnName(column).toLowerCase();
                    if (tableName != null && !tableName.equals("") && columnName != null && !columnName.equals("")){
                        startLetter=columnName.substring(0,1);
                        glossLink="<a href="+WSAHTMLSchema.baseURL+"gloss_"+startLetter+".html#"+tableName+"_"
                        +columnName+">";
                    }
                    } catch (Exception e){
                        
                    }
                    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("compfile")){
                        compFileColumn=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>"+glossLink+md.getColumnName(column)+"</a></b></td>");
                }
            }
                out.println("</tr>");
                   String link=null;
                   String trimString="";
                   String parsedCatname="";
                   int skipped=0;
                while (rs.next() && skipped < skipRowInt){
                    // skip rows
                    skipped++;
                }
                           
                           
                           
                row=0;           
                while (rs.next() && row < rowsPerPage) {
                    if (rs.getString(catnameColumn).matches("(?i).*empty_catalogue.*")){
                        parsedCatname="empty_catalogue.fits";
                    } else {
                        parsedCatname=rs.getString(catnameColumn);
                    }
                    
                    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 && loginboolean){
                        out.println("<td><a href=\"http://surveys.roe.ac.uk/~mar/cgi-bin/display.cgi?file=" +
                        		rs.getString(filenameColumn)+"&cat="+
                        		parsedCatname+"&comp="+rs.getString(compFileColumn)+"&noExt="+
                        		rs.getString(numDetectorsColumn)+"&MFID="+
                        		rs.getString(multiframeidColumn)+"&rID="+rID+
                        		"\" 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==compFileColumn
                                || 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>");
                } // end of resultset loop
                

                out.println("</table> "+row+ " row(s) displayed");
                Enumeration en=req.getParameterNames();
                StringBuffer URLSB=req.getRequestURL();
                URLSB.append("?");
                while (en.hasMoreElements()){
                    String name=(String)en.nextElement();
                    if (!name.equalsIgnoreCase("skipRow")) {
                    URLSB.append(name+"=");
                    URLSB.append(req.getParameter(name)+"&");
                    }   
                }             
                if (skipped > 0) {
                    out.println("<a href=\""+URLSB.append("skipRow="+(skipRowInt-rowsPerPage))+"\">&lt;-prve</a>");
                }
                if (rs.next()) {
                    out.println("<a href=\""+URLSB.append("skipRow="+(skipRowInt+rowsPerPage))+"\">next-&gt;</a>");
                }
                if (row >= 500) {
                    out.println("<br><b>Results are limited to 500 rows.</b>");
                }
 
            } catch (SQLException se) {
                out.println("<p>Error querying the database: "
                        + se.getErrorCode() + se);

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

            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) {

                //	connMgr.freeConnection("idb", con);
               // myBroker.freeConnection(con);
             
                try {
                    con.close();
                    con=null;
                }
                catch (Exception e) {
                    ;
                }    
            }

        }

    }

    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()

   
}