import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.PrintWriter;
import java.sql.Statement;
import java.util.Calendar;
import java.util.Properties;

import javax.servlet.ServletConfig;
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.alanmaxwell.html.HTMLStrings;
import net.alanmaxwell.sql.SQLServerInfoJDBC;
import net.alanmaxwell.sql.WSASQLRetrieverThread;

import org.apache.log4j.Logger;

import sun.awt.geom.AreaOp.IntOp;
import uk.ac.roe.wfau.ATLASHTTPSession;
import uk.ac.roe.wfau.DatabaseURL;
import uk.ac.roe.wfau.FormatLines;
import uk.ac.roe.wfau.OSAQueryRetrieverThread;
import uk.ac.roe.wfau.OSASchema;
import uk.ac.roe.wfau.QueryRetrieverThread;
import uk.ac.roe.wfau.StringToRADec;
import uk.ac.roe.wfau.VDFSSchema;
import uk.ac.roe.wfau.VDFSSession;
import uk.ac.roe.wfau.VSAQueryRetrieverThread;
import uk.ac.roe.wfau.VSASchema;
import uk.ac.roe.wfau.WSAHTMLSchema;
import uk.ac.roe.wfau.WSAQueryRetrieverThread;
import uk.ac.roe.wfau.WSASchema;
import uk.ac.roe.wfau.WSASession;

//import com.oreilly.servlet.UploadedFile;
import com.oreilly.servlet.multipart.FilePart;
import com.oreilly.servlet.multipart.MultipartParser;
import com.oreilly.servlet.multipart.ParamPart;
import com.oreilly.servlet.multipart.Part;

/**
 * @author mar Servlet to upload users list of objects/coordinates to temp
 *         database table and then find matching nearby objects. Users typically
 *         supply pairing radius, table to match against etc.
 * 
 * Once the tmp table is uploaded and other DB fettling the actual query is
 * handled by the same query thread as the main SQL query servlet
 */
public class CrossID extends HttpServlet { 
    
    // set up initial variables

    private int serviceTotal = 0;

    private int serviceCurrent = 0;

    // limits for number of objects
    private static final int MAX_LINES_SMALLRADIUS = 150000;

    private static final int MAX_LINES_LARGERADIUS = 5001;

    static String resultsPageHeading = "CrossID SQL Query Results";

    private static String tempPath;

    private static String tempURL;

    static String baseURL = WSAHTMLSchema.baseURL;

    // Access methods for service counter...
    protected synchronized void enteringServiceMethod() { 
        serviceCurrent++;
        serviceTotal++;
    };

    protected synchronized void leavingServiceMethod() {

        serviceCurrent--;
    };

    protected synchronized int getServiceCurrent() { 
        return serviceCurrent;
    };

    protected synchronized int getServiceTotal() {
        return serviceTotal;
    };

    private boolean shuttingDown = false;

    // Access methods for the shuttingDown flag...
    protected synchronized void setShuttingDown(boolean flag) { 
        shuttingDown = flag;
    };

    protected synchronized boolean isShuttingDown() {
        return shuttingDown;
    };

    private String server;

    private String genUser;

    private static String[] VSASERVERS = null;

    private static String[] WSASERVERS = null;

    private static String[] VSACUSERVERS = null;

    private static String[] WSACUSERVERS = null;

    private static String[] WSANSSERVERS = null;

    private String defaultUKIDSSDatabase;

    private String defaultWorldDatabase;

    // logger located in eg /data/wsa/tmp/logs/log4jsimple.log see Log4JInit servlet
    static Logger logger = Logger.getLogger("wsa.simple");

    static boolean useTDS = true;

    /** 
     * Overrides init to set up initial variaables from properties file WSA.properties in WEB-INF
     * @see javax.servlet.GenericServlet#init(javax.servlet.ServletConfig)
     */
    public void init(ServletConfig config) throws ServletException {

        super.init(config);
        String path = getServletContext().getRealPath("/");
        Properties properties = new Properties();
        try {
            properties
                    .load(new FileInputStream(path + "WEB-INF/WSA.properties"));
        } catch (IOException e) {
            System.out.println("ERROR: properties file not found");
            return;
        }

        server = properties.getProperty("server");
        WSASERVERS = FormatLines.commaSeparatedStringToStringArray(properties
                .getProperty("WSASERVERS").trim());
        VSASERVERS = FormatLines.commaSeparatedStringToStringArray(properties
                .getProperty("VSASERVERS").trim());
        WSACUSERVERS = FormatLines.commaSeparatedStringToStringArray(properties
                .getProperty("WSACUSERVERS").trim());
        VSACUSERVERS = FormatLines.commaSeparatedStringToStringArray(properties
                .getProperty("VSACUSERVERS").trim());
        WSANSSERVERS = FormatLines.commaSeparatedStringToStringArray(properties
                .getProperty("WSANSSERVERS").trim());

        defaultUKIDSSDatabase = properties.getProperty("defaultUKIDSSDatabase");
        defaultWorldDatabase = properties.getProperty("defaultWorldDatabase");
        tempPath = properties.getProperty("genericTempPath");
        tempURL = properties.getProperty("genericTempURL");
    }

    // Override the service method to update the serviceCounter...
    protected void service(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {

        enteringServiceMethod();

        try {
            super.service(req, resp);
        } finally {
            leavingServiceMethod();
        }
        ;
    }; // end of service

    /**
     * override destroy for own cleanup
     * @see javax.servlet.GenericServlet#destroy()
     */
    public void destroy() {

        /*
         * // Check to see if there are still any services running... if
         * (numServices() > 0) { setShuttingDown(true); };
         *  // Wait for all services to finish... while (numServices() > 0) {
         * try { Thread.sleep(100); } catch (InterruptedException e) { }; };
         */
        logger = null;
        super.destroy();
    };

    protected void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {
        res.setContentType("text/html");
        PrintWriter out = res.getWriter();
        String database = null;
        //     float max_lines=MAX_LINES_SMALLRADIUS;
        HttpSession session = req.getSession();
        String archive = "WSA";
        int archiveID = VDFSSchema.WSAARCHIVEID;

        String community = null;
        String user = null;
        boolean loginboolean = false;
        boolean upNamePresent = false;
        boolean errorFlag = false;
        StringBuffer errorMess = new StringBuffer("");
        String programmeID = null;
        int intProgrammeID = 0;
        String radius = null;
        String email = null;
        String email1 = null;
        String filename = null;
        BufferedReader br;
        File savedUploadFile = null;
        String line = null;
        int numberOfLines = 0;
        float dRadius = 0.0F;
        String formatParam = null;
        String compressParam = null;
        String rows = null;
        String emailAddress = null;
        //String select=null;
        String tableName = null;
        String baseTable = null;
        String whereClause = null;
        String otherTables = null;
        String otherTableConstraints = null;
        String selectList = null;
        String colID = "objID";
        String qType = "form";
        boolean doFree = false;
        String sqlStmt = null;
        int nearest = 0;
        String passThrough=null;
        
        try {
            if (server == null) {
                res.sendError(res.SC_BAD_REQUEST,
                        "INVALID SQL Server. Check properties file.");
            }
            out.println(HTMLStrings.getHTMLHeadWithScript(resultsPageHeading,
                    baseURL + "configurestyles.js"));

            out.println("    <h1>" + resultsPageHeading + "</h1> \n");
            out.flush();
            try {

                MultipartParser mp = new MultipartParser(req, 10 * 1024 * 1024); // 10MB

                Part part;
                out.println("<p>Parsing input .....");
                while ((part = mp.readNextPart()) != null) {
                    String name = part.getName();
                    if (part.isParam()) {

                        // it's a parameter part
                        ParamPart paramPart = (ParamPart) part;
                        String value = paramPart.getStringValue();
                        //                  out.print("<p>"+name+" "+value)
                        if (name.equalsIgnoreCase("format")) {
                            formatParam = value;
                        }
                        if (name.equalsIgnoreCase("compress")) {
                            compressParam = value;
                        }
                        if (name.equalsIgnoreCase("rows")) {
                            rows = value;
                        }
                        if (name.equalsIgnoreCase("emailAddress")) {
                            emailAddress = value;
                            if (!emailAddress.equals("")) {
                                out.println("<p>Email: " + emailAddress);
                            }
                        }
                        if (name.equalsIgnoreCase("selectList")) {
                            selectList = value;
                        }
                        if (name.equalsIgnoreCase("whereClause")) {
                            whereClause = value.trim();
                        }
                        if (name.equalsIgnoreCase("database")) {
                            database = value;
                        }
                        if (name.equalsIgnoreCase("qType")) {
                            qType = value;
                        }
                        if (name.equalsIgnoreCase("sqlStmt")) {
                            sqlStmt = value;
                        }

                        if (name.equalsIgnoreCase("archive")) {
                            archive = value.trim();
                            archiveID = VDFSSchema.getArchiveID(archive);
                        }

                        if (name.equalsIgnoreCase("programmeID")) {

                            programmeID = value.trim();

                        }
                        if (name.equalsIgnoreCase("passThrough")) {
                            if (value != null) {
                            passThrough = value.trim();
                            }                            
                    }
                        if (name.equalsIgnoreCase("baseTable")) {
                            baseTable = value.trim();
                            if (baseTable.equalsIgnoreCase("null")
                                    || baseTable == null) {
                                errorFlag = true;
                                errorMess
                                        .append("<p><b>No table selected.</b>");
                            }
                            if (baseTable.equalsIgnoreCase("source")
                                    || baseTable.equalsIgnoreCase("sourceView")) {
                                colID = "sourceID";
                            }

                        }
                        if (name.equalsIgnoreCase("nearest")) {
                            nearest = Integer.valueOf(value.trim()).intValue();
                            if (nearest == 0) {
                                out.println("<p>All nearby objects:");
                            } else {
                                out.println("<p>Nearest object only:");
                            }
                        }
                        if (name.equalsIgnoreCase("otherTables")) {
                            otherTables = value.trim();
                        }
                        if (name.equalsIgnoreCase("otherTableConstraints")) {
                            otherTableConstraints = value.trim();
                        }

                        if (name.equalsIgnoreCase("radius")) {
                            radius = value.trim();
                            out.println("<p>radius: " + radius);
                            try {
                                dRadius = Float.valueOf(radius).floatValue();

                            } catch (Exception e) {
                                errorFlag = true;
                                errorMess
                                        .append("<p><b>Invalid radius (not a number?)</b>");

                            }
                            //   out.println("<p>radius: "+max_lines);
                        }

                        //  out.println("param: name=" + name + "; value=" +
                        // value);
                    } else if (part.isFile()) {

                        // it's a file part

                        FilePart filePart = (FilePart) part;

                        filename = filePart.getFileName();

                        if (filename == null) {
                            errorFlag = true;
                            errorMess.append("<p><b>No file name supplied</b>");
                        }

                        out.println("<p>Parsing upload file ...");
                        out.flush();

                        if (filename != null) {

                            
                            File dir = new File(tempPath
                                    + archive.toLowerCase() + "/tmp/crossID/");
                            savedUploadFile = File.createTempFile(
                                    "upload_crossID", "tmp", dir);
                            // out.println("<br>"+savedUploadFile.getAbsolutePath());
                            //out.println("<br>"+savedUploadFile.getName());
                            long size = filePart.writeTo(savedUploadFile);

                            if (size == 0) {
                                errorFlag = true;
                                errorMess
                                        .append("<p><b>Upload file contain zero bytes, check filename</b>");
                            }

                            if (!errorFlag) {
                                br = new BufferedReader(new InputStreamReader(
                                        new FileInputStream(savedUploadFile)));
                                //out.println("heres");
                                line = "";
                                Object[] tempobjs = new Object[4];
                                while ((line = br.readLine()) != null
                                        && numberOfLines < MAX_LINES_SMALLRADIUS + 1) {
                                    numberOfLines++;

                                    try {
                                        tempobjs = StringToRADec.getRADec(line
                                                .trim(), false);

                                        if (tempobjs[3] != null
                                                && tempobjs[3].toString() != null
                                                && !tempobjs[3]
                                                        .toString()
                                                        .equals(
                                                                StringToRADec.nullStr)) {
                                            upNamePresent = true;

                                        }

                                    } catch (NumberFormatException nfe) {

                                        errorFlag = true;
                                        errorMess
                                                .append("<br> error reading coords at line "
                                                        + numberOfLines);

                                    } catch (Exception e) {

                                    }

                                }
                                tempobjs = null;
                                if (upNamePresent) {
                                    logger.info("CrossID logical upNamePres: "
                                            + upNamePresent);
                                }
                                br.close();
                            }

                            out.println("<p>" + filename
                                    + " uploaded file size: " + size
                                    + " bytes, " + numberOfLines
                                    + " rows loaded");
                            out.flush();

                        }
                    }

                }
                
                if (archive.equalsIgnoreCase("osa") && passThrough !=null  ) {
                	if( passThrough.equalsIgnoreCase(OSASchema.ATLASPASSTHROUGHPASSWORD)){
                	session=new ATLASHTTPSession();
                	}
                }

                community = VDFSSession.getCommunity(session, archiveID);
                user = VDFSSession.getUser(session, archiveID);
                genUser = VDFSSession.getUser(session, archiveID, true);
                try {
                    loginboolean = VDFSSession
                            .getLoginBoolean(session, archive);
                } catch (Exception e) {
                    loginboolean = false;
                }
                if (database == null) {
                    if (loginboolean && archiveID == VDFSSchema.WSAARCHIVEID) {
                        if (!community.equalsIgnoreCase("nonSurvey")) {
                            database = defaultUKIDSSDatabase;
                        } else {
                            database = genUser;
                        }
                    } else {
                        database = defaultWorldDatabase;
                    }
                }
               
                if ((community.equalsIgnoreCase("nonSurvey") || VDFSSchema.isProprietary(community,archiveID))
                        && VDFSSession.getLoginBoolean(session, archive)) {
                    programmeID = VDFSSession.getProgID(session,archiveID);
                    
                    try {
                        intProgrammeID = Integer.valueOf(programmeID)
                                .intValue();
                       

                    } catch (Exception e) {
                        errorFlag = true;
                        errorMess.append("<p><b>Invalid progammeID</b>");

                    }
                   //  out.print(programmeID);
                }
                logger.info("CrossId prog "+programmeID);
                if (!community.equalsIgnoreCase("nonsurvey")  && intProgrammeID < 10000 ) {
                    out.println("<p>Programme: ");
                    if (programmeID.equalsIgnoreCase("null")
                            || programmeID == null) {
                        errorFlag = true;
                        errorMess.append("<p><b>No survey selected.</b>");
                    }
                    try {
                        intProgrammeID = Integer.valueOf(programmeID)
                                .intValue();
                        //  out.println(WSASchema.getSurveyName(intProgrammeID));
                        out.println(VDFSSchema.getSurveyName(intProgrammeID,
                                archiveID));

                    } catch (Exception e) {
                        errorFlag = true;
                        errorMess.append("<p><b>Invalid survey/progammeID</b>");

                    }
                }
              
                if (dRadius >= 7.5 && numberOfLines > MAX_LINES_LARGERADIUS) {
                    errorFlag = true;
                    errorMess.append("<p><b>File contained more than "
                            + MAX_LINES_LARGERADIUS + " lines</b>");

                }
                if (dRadius < 7.5 && numberOfLines > MAX_LINES_SMALLRADIUS) {
                    errorFlag = true;
                    errorMess.append("<p><b>File contained more than "
                            + MAX_LINES_SMALLRADIUS + " lines</b>");

                }

                out.flush();
                /*
                 * if (numberOfLines*dUserX > MAX_LINES * 1.0) { errorFlag=true;
                 * errorMess.append(" <p> <b>Total area requested: number
                 * objects x size &gt; "+MAX_LINES+" sq arcmin </b>"); }
                 */

                if (dRadius < 0.1F || dRadius > 3610.0F) {
                    errorFlag = true;
                    errorMess
                            .append("<p><b>Size should be &gt; 0.1 arcsec and &lt; 300 arcsec</b>");
                }
                if (emailAddress != null && !emailAddress.equals("")) {
                    if (emailAddress.length() < 3
                            || emailAddress.indexOf('@') < 0) {
                        errorFlag = true;
                        errorMess.append("<p><b>Invalid email supplied</b>");
                    }

                }

                if (errorFlag) {
                    out
                            .print(errorMess
                                    + "<p>Use the browser's back button to return to the form");
                    return;
                }

            } catch (IOException IOEx) {
                out.println(IOEx);
            }
            
            String defaultSelect = null;

            if (community.equalsIgnoreCase("nonSurvey") || intProgrammeID > 10000 ) {
                tableName = user.trim() + baseTable;
                int[] reqFilters = null;
                if (VDFSSession.getAttribute(session,archiveID, "reqFilters") != null) {
                    reqFilters = ((int[]) VDFSSession.getAttribute(session,archiveID, "reqFilters"));
                }
                defaultSelect = WSASchema.getDefaultList(reqFilters, tableName,
                        WSASession.getSchemaVersion(session));
            } else {
                if (baseTable.equalsIgnoreCase("sourceView")) {
                    // tableName=WSASchema.getSourceViewName(intProgrammeID);
                    tableName = VDFSSchema.getSourceViewName(intProgrammeID,
                            archiveID);
                } else if (baseTable.equalsIgnoreCase("source")) {
                    //tableName=WSASchema.getSourceTableName(intProgrammeID);
                    tableName = VDFSSchema.getSourceTableName(intProgrammeID,
                            archiveID);
                } else if (baseTable.equalsIgnoreCase("detection")) {
                    // tableName=WSASchema.getDetectionTableName(intProgrammeID);
                    tableName = VDFSSchema.getDetectionTableName(
                            intProgrammeID, archiveID);
                }

                defaultSelect = VDFSSchema.getDefaultList(VDFSSchema
                        .getReqFilters(database, intProgrammeID, archiveID),
                        tableName, database, intProgrammeID, archiveID);
            }
            // String where="";
            // if (whereClause.length() > 2) {
            //     where=" and "+whereClause+" ";
            // }
            
           
            String select = selectList.replaceAll("default", defaultSelect);

            if (qType != null && qType.equalsIgnoreCase("free")) {
                doFree = true;
                out.print("<br><b>Where:</b> " + whereClause
                        + " used in initial crossID");

            } else {

                out.print("<br><b>From:</b> " + tableName);
                out.print("<br><b>Where:</b> " + whereClause);

            }

            // FIRST THING: Extract and verify all the provided parameters...
            SQLServerInfoJDBC SQLServer = null;
            String SQLQueryString = "";
            String ra = null;
            String dec = null;
            String ra2000 = null;
            String dec2000 = null;

            String name = null;
            String sys = null;

            String action = null;

            String from = null;
            int tableid = 0;

            int rowsHTML = WSASQLRetrieverThread.SQLROWS_DEFAULT;
            boolean doRowTotal = true;

            int OutputFormat = WSASQLRetrieverThread.OUTPUT_NONE;
            int OutputCompression = WSASQLRetrieverThread.COMPRESS_NONE;

            String emailTAR = null;

            boolean fg = true;
            Statement retrieverStmt = null;
            String dbURL = DatabaseURL.getDatabaseURL(session, useTDS, server,
                    database, database);

            if (sqlStmt != null) {

                if (sqlStmt.toLowerCase().indexOf("bestdr3..") > 0
                        || sqlStmt.toLowerCase().indexOf("seguedr6..") > 0
                        || sqlStmt.toLowerCase().indexOf("thutmose..") > 0
                        || sqlStmt.toLowerCase().indexOf("bestdr5..") > 0
                        || sqlStmt.toLowerCase().indexOf("bestdr7..") > 0
                        || sqlStmt.toLowerCase().indexOf("bestdr2..") > 0) {
                    dbURL = DatabaseURL.getDatabaseURL(session, useTDS,
                            "thutmose", database, database);
                }
            }

            //String
            // dbURL=DatabaseURL.getDatabaseURL(session,useTDS,server,database,database);

            //database=DatabaseURL.getDatabaseNameFromURL(dbURL,true);

            //out.println(dbURL);
            //resultsPageHeading = "WSA Database - Query Results";

            // baseURL= "http://thoth.roe.ac.uk/wsa/";
            // resultsPageHeading = "WSA Database - CrossID Query Results";

            String extraFrom = "";
            String extraWhere = "";
            if (otherTables != null && !otherTables.equals("")) {
                extraFrom = "," + otherTables;
            }
            if (otherTableConstraints != null
                    && !otherTableConstraints.equals("")) {
                extraWhere = " where " + otherTableConstraints;
            }

            if (doFree) {
                SQLQueryString = sqlStmt;
                // session.setAttribute("CrossIDSQLStmt",SQLQueryString);
                out.println("<p>" + SQLQueryString);

            } else {
                SQLQueryString = "select #upload.*,#proxtab.distance,"
                        + select
                        + " from  #upload left outer join #proxtab on #upload.upload_id=upid "
                        + "left outer join " + tableName + " on " + tableName
                        + "." + colID + "=archiveID " + extraFrom + " "
                        + extraWhere + " order by #upload.upload_id";
                out.println("\n<!-- SQL QUERY : " + SQLQueryString + " -->");
            }

            //out.println("<p>"+SQLQueryString);
            if (SQLQueryString == null) {
                out.println("The supplied SQL query was null.");
                return; // Finish this method immediately...
            } else if (SQLQueryString.length() > 8000) {
                out
                        .println("The SQL query cannot exceed a length of 8000 characters.");
                return;
            } else {
                // Now we can extract the final OPTIONAL parameters...

                /*
                 * if (req.getParameter("emailAddress") != null &&
                 * !req.getParameter("emailAddress").equals("")) { emailAddress =
                 * req.getParameter("emailAddress"); if ( (
                 * emailAddress.length() < 3) || (emailAddress.indexOf("@") ==
                 * -1) ) { res.sendError( res.SC_BAD_REQUEST, "You must also
                 * include a VALID e-mail address " + "(your emailAdddress=
                 * parameter seems to be empty or " + "incorrect) " ); return; } }
                 * else { emailAddress=null; }
                 *  
                 */

                try {
                    rowsHTML = Integer.parseInt(rows);
                } catch (NumberFormatException e) {
                    rowsHTML = WSASQLRetrieverThread.SQLROWS_DEFAULT;
                }
                ;

                if (rowsHTML < 0) {
                    rowsHTML = 0;
                }
                ;

                // When more rows are returned than displayed in summary,
                // does the user want to know the total rows returned?
                // Default, yes, takes slightly more time to run...

                // What about compression?

                if (compressParam.equalsIgnoreCase("ZIP")) {
                    OutputCompression = WSASQLRetrieverThread.COMPRESS_ZIP;
                } else if (compressParam.equalsIgnoreCase("GZIP")) {
                    OutputCompression = WSASQLRetrieverThread.COMPRESS_GZIP;
                } else {
                    OutputCompression = WSASQLRetrieverThread.COMPRESS_NONE;
                }
                ;

                // Any special format (in addition to HTML) requested?

                if (formatParam.equalsIgnoreCase("CSV")) {
                    OutputFormat = WSASQLRetrieverThread.OUTPUT_CSV;
                } else

                if (formatParam.equalsIgnoreCase("FITS")) {
                    OutputFormat = WSASQLRetrieverThread.OUTPUT_FITS;
                } else if (formatParam.equalsIgnoreCase("VOT")) {
                    OutputFormat = WSASQLRetrieverThread.OUTPUT_VOT;
                } else {
                    OutputFormat = WSASQLRetrieverThread.OUTPUT_NONE;
                }
                ;

                // Now we should have everything we need, attempt to get the
                // data...

                // Connect to the database and attempt to execute the provided
                // SQL
                // Set the content type and then formulate the normal
                // response...

                //String procSQL="EXEC master..spTest1
                // "+dRadius/60.0+",'ukidssr1..spectra','specid',1,' and specid
                // <=100'";
                //out.print("<p> "+procSQL);
                //          WSASQLRetrieverThread retrieverThread = null;
                QueryRetrieverThread retrieverThread = null;
                Thread runner = null;

                boolean serverSupplied = false;
                String suppliedServer = null;
                for (int i = 0; i < VDFSSchema.ALLSQLSERVERS.length
                        && !serverSupplied; i++) {
                    //logger.info("server "+VDFSSchema.ALLSQLSERVERS[i]);
                    //if
                    // (SQLQueryString.matches("(?i).*"+VDFSSchema.ALLSQLSERVERS[i]+"..*"))
                    // {
                    if (whereClause.toLowerCase().indexOf(
                            VDFSSchema.ALLSQLSERVERS[i] + "..") > 0) {
                        // logger.info("heer1");
                        serverSupplied = true;
                        suppliedServer = VDFSSchema.ALLSQLSERVERS[i];
                    }
                }

                String[] servers;
                //logger.info(externalSurvey+" "+serverSupplied+"
                // "+suppliedServer+" "+SQLQueryString);
                if (serverSupplied) {
                    servers = new String[] { suppliedServer };
                }

                else {
                    servers = VDFSSession.getServers(community, archive,
                            WSASERVERS, WSACUSERVERS, VSASERVERS, VSACUSERVERS);
                    logger.info("CROSSID "+community+" : "+archive);
                }

                try {
                    //out = res.getWriter();

                    // Prepare the heading of the HTML document:

                    //     out.println(":"+SQLQueryString);
                    if (OutputFormat != WSASQLRetrieverThread.OUTPUT_NONE) {
                        out
                                .println("    <p> \n"
                                        + "      Data file generating queries can take a bit longer to execute \n"
                                        + "      as they write to a file ALL rows returned by the query. \n"
                                        + "    </p> \n");

                        out
                                .println("    <p> \n"
                                        + "      A web link to your generated output file will appear at the \n"
                                        + "      bottom of this page. \n"
                                        + "    </p> \n");
                    }
                    ;

                    out.println("    <p>");

                    out.println("<b>Using database " + database + "</b><br>");

                    out.print("      <b>QUERY STARTED:</b> "
                            + Calendar.getInstance().getTime()
                            + "&nbsp;&nbsp;&nbsp;[" + getServiceCurrent()
                            + " active, " + getServiceTotal() + " total] \n");

                    out.println("    </p> \n");

                    out
                            .println("    <p> \n"
                                    + "      Please keep this browser window open and wait for your results to appear below... \n"
                                    + "    </p> \n");

                    res.flushBuffer();

                    // THIS SECTION WILL DO THE QUERY:

                    // retrieverThread = new WSASQLRetrieverThread(SQLServer);
                    // // Server we setup earlier...
                    // retrieverThread = new WSASQLRetrieverThread(dbURL);

                    if (archive.equalsIgnoreCase("vsa")) {
                        // out.println("vsahere");
                        retrieverThread = new VSAQueryRetrieverThread(dbURL);
                    } 
                    else if (archive.equalsIgnoreCase("osa")) {
                        // out.println("vsahere");
                        retrieverThread = new OSAQueryRetrieverThread(dbURL);
                    } 
                    else {
                        // out.println("wsahere "+dbURL);

                        retrieverThread = new WSAQueryRetrieverThread(dbURL);

                    }

                    retrieverThread.setDatabaseName(database);

                    //retrieverThread.updateWebqueries=true;
                    //            retrieverThread.setCrossIDParameters(database,tableName,select,whereClause,colID,dRadius,nearest);
                    retrieverThread.setCrossIDParameters(database, tableName,
                            select, whereClause, colID, dRadius, nearest,
                            otherTables, otherTableConstraints);
                    if (doFree) {
                        retrieverThread.showSQL = true;
                    }
                    retrieverThread.setOutputBaseDIR(
                            tempPath + archive.toLowerCase() + "/tmp/");
                    retrieverThread.setOutputBaseURL(
                            tempURL + archive.toLowerCase() + "/tmp/");
                    retrieverThread.setOutputAppendage("/crossID/");

                    // Pass through any parameters to the retriever...
                    String IPAddress = req.getRemoteAddr();
                    logger.info("CROSSID,Login,"
                            + (String) session.getAttribute("user") + ","
                            + (String) session.getAttribute("community") + ","
                            + IPAddress);
                    logger.info("CROSSID,URL," + dbURL + "," + IPAddress);
                    logger.info("CROSSID,Query,"
                            + SQLQueryString.replaceAll("\n", " ").replaceAll(
                                    "\r", " ") + "," + IPAddress);
                    retrieverThread.setUser(WSASession.getUser(session));
                    retrieverThread.setIPAddress(IPAddress);
                    retrieverThread.setSQLQuery(SQLQueryString);
                    retrieverThread.upNamePresent = upNamePresent;
                    //retrieverThread.setSQLQuery(SQLQueryString);
                    retrieverThread.setRowsHTML(rowsHTML);
                    retrieverThread.setDoRowTotal(doRowTotal);
                    retrieverThread.setOutputFormat(OutputFormat);
                    //out.println(OutputFormat);
                    retrieverThread.setOutputCompression(OutputCompression);
                    //out.println(savedUploadFile.getAbsolutePath());
                    retrieverThread.setUploadFile(savedUploadFile
                            .getAbsolutePath());
                    retrieverThread.setRadius(radius);
                    // retrieverThread.setPair(pair);

                    if (emailAddress != null && emailAddress.equals("")) {
                        emailAddress = null;
                    }

                    if (emailAddress != null) {
                        retrieverThread.setMightBeBackground(true);
                    }

                    // Start a separate thread running to retrieve the SQL...
                    runner = new Thread(retrieverThread);
                    //retrieverStmt=retrieverThread.setConnection();
                    retrieverStmt = retrieverThread.setConnection(servers,
                            database, archive, session, null);

                    if (retrieverStmt != null) {
                        out.println("Connected to database");
                        runner.start();
                    } else {
                        out
                                .println("<h3>Unable to connect to database</h3><br>Please try again at a later time, if the problem persists send an "
                                        + "email to mar@roe.ac.uk");
                    }

                    // Calls the the resolving method with the command line
                    // argument

                    out.println("    <p>");

                    int dotCount = 0;

                    while ((!isShuttingDown())
                            && (runner.isAlive())
                            && (retrieverThread.isQueryProcessing() && fg == true)) {
                        if (emailAddress != null && dotCount >= 27) {
                            retrieverThread.setEmailAddress(emailAddress);
                            fg = false;
                        }
                        try {
                            Thread.sleep(3000);
                        } catch (InterruptedException ie) {
                            // Do nothing when interrupted...
                        }

                        out
                                .print("&#8226;&#8226;&#8226;<!--               -->");

                        res.flushBuffer();

                        //dotCount++;
                        dotCount = dotCount + 3;
                        if ((dotCount % 30) == 0) {
                            out.println(" " + retrieverThread.getRowsReturned()
                                    + " rows returned so far <br>");

                            //dotCount = 0;
                        }
                    }

                    if (fg || !retrieverThread.isQueryProcessing()) {

                        fg = true;
                        out.println("      OK");
                        out.println("    </p> \n");

                        retrieverThread.setQueryTerminated(true);
                        retrieverThread.setMightBeBackground(false);
                        out.println(retrieverThread.getResultsHTML());

                        out.println("    <p> \n"
                                + "      <b>QUERY FINISHED:</b> "
                                + Calendar.getInstance().getTime() + " \n"
                                + "    </p> \n");

                        out
                                .println("    <p> \n"
                                        + "      Click your browsers 'BACK' button to try another query...\n"
                                        + "    </p> \n");

                        if (action.equalsIgnoreCase("radial")) {
                            out
                                    .println("<form action=\"pixel.jsp?ra="
                                            + ra2000
                                            + "&dec="
                                            + dec2000
                                            + "&radius="
                                            + radius
                                            + "&sys="
                                            + sys
                                            + "\" method=\"post\">"
                                            + "<input class=\"FontSans\" type=\"submit\" value=\"getAreaImage\" /> </form> "
                                            + "Click the getAreaImage button to retrieve an image of the searched area");
                        }

                        out.println(HTMLStrings.getHTMLTail(baseURL
                                + "browserversion.js"));

                    } else {
                        out
                                .println("<p> Query moved to background. "
                                        + "On completion you will be informed where to retrieve your results by email to <b>"
                                        + emailAddress
                                        + "</b><p> Click your browsers 'BACK' button to return to the WSA pages. </body></html>");

                    }

                    // QUERY DONE!
                } finally {
                    out.flush();
                    // Free up resources...
                    //            System.out.println("here");
                    //            File fooFile = new File("/home/6df/tada.txt");
                    //            fooFile.createNewFile();
                    //				File fooFile = new File("/home/avo/mar/tada.txt");
                    //           fooFile.createNewFile();

                    if (fg == true) {

                        if (retrieverThread != null) {
                            /*
                             * if (retrieverThread.isQueryProcessing()){
                             * System.out.println("resorting to close");
                             * retrieverThread.setDBConnectionClosed(); }
                             */
                            if (retrieverThread.isQueryProcessing()) {

                                logger.info("CROSSID,CANCEL,cancelling");
                                try {
                                    retrieverStmt.cancel();
                                    retrieverStmt = null;
                                } catch (Exception se) {
                                    System.out.println("erroHere" + se);
                                }
                            }

                            retrieverThread.setQueryTerminated(true);
                            //System.out.println("stopped");
                        }
                        ;

                    }

                    runner = null;
                    retrieverThread = null;

                    if (out != null) {
                        out.close();
                    }
                    ;

                    out = null;
                }
                ; // try..finally
            }
            ; // end of valid SQL string
            //}; // end of valid server if (SQLServer == null) else...
        } // end of initail try or doGet
        catch (Exception e) {
            /*
             * res.sendError( res.SC_INTERNAL_SERVER_ERROR, e + "" );
             */
            return;
        }
        ;
    }; // END of doGet()

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

    public String getServletInfo() {
        return "SQLGet v1.20 by Alan Maxwell";
    }; // END of getServletInfo()
};