// SQLMatch class
// Programmed by Alan Maxwell
//
// Revision History
// ----------------
// v1.23: 13 Dec 2002
//   Added an option 'rowtotal' which specifies whether the result page should
//   show the total number of rows the query returned when only a subset is
//   displayed in summary...
// v1.22: 09 Dec 2002
//   Fixed TAR request, when an empty e-mail is provided it gives error in
//   addition to not having the 'email=' parameter at all...
// v1.21: 03 Dec 2002
//   Fixed the while loop to check if the 'isQueryProcessing()' variable of
//   the thread, to allow for even quicker responses...
// v1.20: 29 Nov 2002
//   Major rewrite to make use of the new Threaded SQLRetrieverThread class...
// v1.11: 28 Nov 2002
//   Minor code tidy/optimise - also added 'serviceCounter' which tracks
//   the number of concurrent threads...
// v1.10: 26 Nov 2002
//   Revised to include support for the 'tar=yes' and 'email=XXX' parameters to
//   allow the users to request a 'tar' file of all the FITs files related to
//   an object or group of objects.
// v1.00: 04 Oct 2002
//   Initial Release
//
// Synopsis
// --------
// This class implements a Java 'Servlet' web-service which allows the user
// to request execution of an SQL string to one of the (predetermined in this
// source) available SQL servers. The SQL can be returned as an HTML summary
// table, in addition to a CSV data file which can be ZIPped or GZIPped. SQL
// error messages are returned to the users if the SQL cannot be performed.
//
// This service is usually installed in a Servlet server such as Tomcat and
// then called from a web page (usually as the 'ACTION' event for a FORM).

import java.io.*;
import java.util.*;
import java.sql.*;

import javax.servlet.*;
import javax.servlet.http.*;
import com.oreilly.servlet.*;
import com.oreilly.servlet.multipart.*;
import net.mar.DefaultSelect;
import net.mar.SQLRadial;
import net.alanmaxwell.html.HTMLStrings;
import net.alanmaxwell.sql.SQLServerInfoJDBC;
import net.alanmaxwell.sql.SQLRetrieverThread;
import net.alanmaxwell.sql.SQLRetrieverThread6dF;

public class SQLMatch extends HttpServlet {
  private int serviceCurrent = 0;
  private int serviceTotal = 0;
  private static final int MAX_LINES=50000;

  // 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;
  };

  // insert from com Demo

  private File dir;
  private String fileName=null;
  private String saveName=null;
  private String dirName;
  private String tmpWeb;
  private String SQLMachine;
  public void init(ServletConfig config) throws ServletException {
    super.init(config);
    // Read the uploadDir from the servlet parameters
    fileName=null;
    dirName = config.getInitParameter("uploadDir");
    tmpWeb=config.getInitParameter("tmpWeb");
    if (dirName == null) {
      throw new ServletException("Please supply uploadDir parameter");
    }

    dir = new File(dirName+"tmp_xmatch/");
    if (! dir.isDirectory()) {
      throw new ServletException("Supplied uploadDir " + dirName +
                                 " is invalid");
    }
       Properties properties = new Properties();
       String path = getServletContext().getRealPath("/");
        try {
            properties.load(new FileInputStream(path
                    + "WEB-INF/SSA.properties"));

        } catch (IOException e) {
            System.out.println("ERROR: properties file not found");
            return;
        }
    SQLMachine=properties.getProperty("SQLMachine");;
  }
  

  // end insert

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

    enteringServiceMethod();
  //  saveName=null;
    try
    {
      super.service(req, resp);
    }
    finally
    {
      leavingServiceMethod();
    } ;
  };

  /*  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)
      {
      };
    };
  };
*/

  protected void doGet(HttpServletRequest req, HttpServletResponse res)
                 throws ServletException, IOException {




 try {

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

      String baseURL = "";
      String resultsPageHeading = "SQL Query Results";

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

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

      boolean do6df = false;
      boolean useHMS = true;
      boolean doTAR = false;
      String emailTAR = null;
	  String emailAddress=null;
	  boolean fg = true;
      String radius=null;
      saveName=null;
      String pair=null;
      String from=null;
      int tableID=0;
      String serverName=null;
      String formatParam = null;
      String compressParam = null;
      String rows = null;
      String select = null;
//      BufferedReader upBufferedReader=null; //




      // insert from cos

	      try {
	         MultipartParser mp = new MultipartParser(req, 10*1024*1024); // 5MB
	         Part part;
	         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();
	             if (name.equalsIgnoreCase("radius")){
	  			   radius=value;
	  		  	 }
	  		  	 if (name.equalsIgnoreCase("pair")){
				   pair=value;
	  		  	 }
	  		  	 if (name.equalsIgnoreCase("from")){
				 				   from=value;
	  		  	 }
	             if (name.equalsIgnoreCase("servername")){
	  			   serverName=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 (name.equalsIgnoreCase("select")){
				   select=value;
	  		  	 }
	//             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)
				           {
				             res.sendError(
				               res.SC_BAD_REQUEST,
				               "No file name supplied"
				             );
      			    return; // Finish this method immediately...
	  				}


	             if (fileName != null) {
	  			  saveName=dirName+"tmp_xmatch/"+fileName+"_"+serviceTotal+".tmp";
	  			  File newdir = new File(saveName);
	               // the part actually contained a file
	         System.out.println(Calendar.getInstance().getTime()  );

	               long size = filePart.writeTo(newdir);
	               System.out.println(Calendar.getInstance().getTime()  );
	               if (size ==0)
				           {
				             res.sendError(
				               res.SC_BAD_REQUEST,
				               "Upload file contain zero bytes"
				             );
          return; // Finish this method immediately...
	  }



						InputStream istream=filePart.getInputStream();
	  				BufferedReader br = new BufferedReader(new InputStreamReader(new
						FileInputStream(newdir)));
	  				String line = br.readLine();
	  				int numberOfLines = 0;

	  				while (line != null) {
	  					line=br.readLine();
	  				    numberOfLines++;
	  				}


	  				if (numberOfLines > MAX_LINES)
	  				{
						res.sendError(
				               res.SC_BAD_REQUEST,
				               "File contained more than " + MAX_LINES +" lines."
				             );
			          return; // Finish this method immediately...
					  }



/*
	     upBufferedReader = new BufferedReader(new
	     InputStreamReader(filePart.getInputStream()));

	     String line;
	         while ((line=upBufferedReader.readLine()) !=null){
		 				System.out.println(line +"<br>");
		}
*/


	             }
	             else {
	               // the field did not contain a file
	//  				out.println("file: name=" + name + "; EMPTY");
	             }
	//             out.flush();
	           }
	         }
	       }
	       catch (IOException lEx) {
//	         this.getServletContext().log(lEx, "error reading or saving file");
	         this.getServletContext().log("error reading or saving file");
			res.sendError(
         	 res.SC_BAD_REQUEST,
         	 "Error reading or saving file. Files should be less than 5Mb"
  	     	 );
	        return; // Finish this method immediately...
	      }

 // end insert




      // First parameter needed is the server name, without it: duh! no data
      if (serverName != null)
      {


        if (serverName.equalsIgnoreCase("GMACHINE"))
		        {
		          SQLServer =
		            new SQLServerInfoJDBC(

				       "grendel01.roe.ac.uk", "ssa", "ssaro", "ssaropw"

		            );

		          //baseURL= "http://grendel12.roe.ac.uk/~avo/ssa/";
		          baseURL= "http://thoth.roe.ac.uk/ssa/";
		          resultsPageHeading = "SSA Database - SQL Query Results";

		          do6df = false;
        }

        else if (serverName.equalsIgnoreCase("AMACHINE"))
		        {

		          SQLServer =
		            new SQLServerInfoJDBC(
		//              "toshnt71.roe.ac.uk", "sixtest", "dbreader", "6dfpass"
				      // "grendel01.roe.ac.uk", "SkyServerV3_Data1", "ssrw", "ssrwpw"
				       SQLMachine, "ssa", "ssaro", "ssaropw",true

		            );

		          //baseURL= "http://grendel12.roe.ac.uk/~avo/ssa/";
		          baseURL= "http://surveys.roe.ac.uk/ssa/";
		          resultsPageHeading = "SSA Database - SQL Query Results";

		          do6df = false;
        }


          else if (serverName.equalsIgnoreCase("PMACHINE"))
  		        {
  		          SQLServer =
  		            new SQLServerInfoJDBC(

  				      SQLMachine, "PSSA", "ssaro", "ssaropw"

  		            );

  		   baseURL= "http://surveys.roe.ac.uk/ssa/";
  		   resultsPageHeading = "SSA Database - SQL Query Results";

        }
        else
          SQLServer = null;
      };

      // If we haven't been given a server, there's no point continuing...
      if (SQLServer == null)
      {
        res.sendError(
          res.SC_BAD_REQUEST,
          "You must provide the name of a VALID SQL Server as parameter 'server'."
        );
        return; // Finish this method immediately...
      }
      else
      {
        // Now we can try to get the SQL parameter, the only other REQUIRED one.

      //  SQLQueryString = req.getParameter("sql");

        if (SQLQueryString == null)
        {
          res.sendError(
            res.SC_BAD_REQUEST,
            "No SQL query was provided as parameter 'sql'."
          );
          return; // Finish this method immediately...
        }
        else if (SQLQueryString.length() > 4096)
        {
          res.sendError(
            res.SC_BAD_REQUEST,
            "The SQL query parameter 'sql' cannot exceed a length of 4096 characters."
          );
          return;
        }
        else
        {
          // Now we can extract the final OPTIONAL parameters...
          if (req.getParameter("HMS") != null)
          {
            if ( req.getParameter("HMS").equalsIgnoreCase("N") ||
                 req.getParameter("HMS").equalsIgnoreCase("NO") )
            {
              useHMS = false;
            };
          };

		if (emailAddress != null && !emailAddress.equals(""))
	      {
             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 {
			  if (Float.parseFloat(radius) > 5)
			  {
				  res.sendError(
				    res.SC_BAD_REQUEST,
				       "Radius supplied should be less than 5 arcmin"
				            );
                   return;
			   }
		   }
	       catch (NumberFormatException e)
	            {
	             res.sendError(
				    res.SC_BAD_REQUEST,
				       "Radius supplied was not a number"
				            );
                   return;
            };


          // Do they want to return a special number of ROWS?




          if (rows != null)
          {
            try
            {
            	rowsHTML = Integer.parseInt(rows);
            }
            catch (NumberFormatException e)
            {
              rowsHTML = SQLRetrieverThread.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...
          if (req.getParameter("rowtotal") != null)
          {
            if ( req.getParameter("rowtotal").equalsIgnoreCase("N") ||
                 req.getParameter("rowtotal").equalsIgnoreCase("NO") )
            {
              doRowTotal = false;
            }
          }

          // What about compression?
          if (compressParam != null)
          {


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

          // Any special format (in addition to HTML) requested?
          if (formatParam != null)
          {


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

          // Check for an e-mail param (for TAR below)...
          if (req.getParameter("email") != null)
          {
            emailTAR = req.getParameter("email");
          };

          // Do we have to generate a TAR set? (Need e-mail param too!)
          if (req.getParameter("tar") != null)
          {
            String tarParam = req.getParameter("tar");

            if ( tarParam.equalsIgnoreCase("Y") ||
                 tarParam.equalsIgnoreCase("YES") )
            {
              if (
                   (emailTAR == null) ||
                   (OutputFormat != SQLRetrieverThread.OUTPUT_CSV)
                 )
              {
                res.sendError(
                  res.SC_BAD_REQUEST,
                  "When using the 'tar=yes' parameter to obtain a TAR file " +
                  "you must also include an e-mail address with the 'email=' " +
                  "parameter and ensure that the 'format=CSV' parameter is set " +
                  "(to generate a CSV file)!"
                );
                return;
              };

              if (
                   (emailTAR.length() < 3) ||
                   (emailTAR.indexOf("@") == -1)
                 )
              {
                res.sendError(
                  res.SC_BAD_REQUEST,
                  "When using the 'tar=yes' parameter to obtain a TAR file " +
                  "you must also include a VALID e-mail address with the 'email=' " +
                  "parameter (your email= parameter seems to be empty or " +
                  "incorrect) and ensure that the 'format=CSV' parameter is " +
                  "set (to generate a CSV file)!"
                );
                return;
              }

              doTAR = true;
            };
          }

          // 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...

          res.setContentType("text/html");

          PrintWriter out = null;
          SQLRetrieverThread retrieverThread = null;
          Thread runner = null;

          try
          {
            out = res.getWriter();



            // Prepare the heading of the HTML document:
            out.println(
              HTMLStrings.getHTMLHeadWithScript(
                resultsPageHeading,
                baseURL + "configurestyles.js"
              )
            );

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


            if (OutputFormat != SQLRetrieverThread.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.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:

            if (do6df == true)
            {
              retrieverThread =
                new SQLRetrieverThread6dF(SQLServer); // Server we setup earlier...
              retrieverThread.setOutputBaseDIR(
//                "/home/6df/public_html/"
				dirName
              );
              retrieverThread.setOutputBaseURL(
//                "http://www-wfau.roe.ac.uk/6dFGS/"
                   tmpWeb
              );
              retrieverThread.setOutputAppendage(
                "tmp_xmatch/"
              );

              ((SQLRetrieverThread6dF) retrieverThread).setShowHMS(useHMS);
              ((SQLRetrieverThread6dF) retrieverThread).setLinkTAR(doTAR);
              ((SQLRetrieverThread6dF) retrieverThread).setLinkEMAIL(emailTAR);
            }
            else
            {
              retrieverThread =
                new SQLRetrieverThread(SQLServer); // Server we setup earlier...
                             retrieverThread.setOutputBaseDIR(
				//                "/home/6df/public_html/"
								dirName
				              );

				              retrieverThread.setOutputBaseURL(
				//                "http://www-wfau.roe.ac.uk/6dFGS/"
				                   tmpWeb
				              );
				              retrieverThread.setOutputAppendage(
                "tmp_xmatch/");
            };

            // Pass through any parameters to the retriever...

		DefaultSelect ds = new DefaultSelect();
       if (from.equalsIgnoreCase("source"))
       {
		   select=SQLRadial.replaceall(select,"default",ds.getDefault("source"));
		   tableID=1;
	   }
	   else
	   {   select=SQLRadial.replaceall(select,"default",ds.getDefault("detection"));
		   tableID=3;
	   }

          /*  if (select.equalsIgnoreCase("default"))
            {
				DefaultSelect ds = new DefaultSelect();
				select=ds.getDefault(from);
			}

            if (select.equalsIgnoreCase("*"))
            {
				select=from+".*";
			}

            if (from.equalsIgnoreCase("detection"))
            {
				tableID=3;
			}
            if (from.equalsIgnoreCase("source"))
            {
				tableID=1;
			}*/

		StringBuffer sbSQL=new StringBuffer("");

		if (pair.equals("nearest")){
		sbSQL.append(" INSERT INTO #x SELECT up_id, dbo.fGetNearestObjIdEqSSA(up_ra,up_dec,"+radius+","+tableID+") as objId,dbo.fGetNearestObjDistEqSSA(up_ra,up_dec,+"+radius+","+tableID+") as distance");
		sbSQL.append(" FROM #upload WHERE dbo.fGetNearestObjidEqSSA(up_ra,up_dec," + radius + ","+tableID+") IS NOT NULL ");
		}
		else {
			sbSQL.append("insert into #x EXEC dbo.spGetNeighborsSSA " + radius +", "+tableID+" ");
		}
		sbSQL.append ("EXEC dbo.spWebExecuteSQL '");
		sbSQL.append(" select up_name,up_ra,up_dec,#upload.up_id,distance,"+ select +" from #upload left outer join #x on #upload.up_id = #x.up_id left outer join"+
		" "+from+" on #x.objid="+from+".objid order by #upload.up_id,distance");
        sbSQL.append (" '");









            SQLQueryString=sbSQL.toString();
            System.out.println(sbSQL);
            retrieverThread.setSQLQuery(SQLQueryString);
            retrieverThread.setRowsHTML(rowsHTML);
            retrieverThread.setDoRowTotal(doRowTotal);
            retrieverThread.setOutputFormat(OutputFormat);
            retrieverThread.setOutputCompression(OutputCompression);
            retrieverThread.setUploadFile(saveName);
            retrieverThread.setRadius(radius);
            retrieverThread.setPair(pair);

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


  //        retrieverThread.setUpBufferedReader(upBufferedReader);


            // Start a separate thread running to retrieve the SQL...
            runner = new Thread(retrieverThread);
            runner.start();

            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>"
				               );

              }

            }


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

            fg=true;

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

            retrieverThread.setQueryTerminated(true);

            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"
            );

            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 SSA pages. </body></html>");
			}


            // QUERY DONE!
          }
          finally
          {
            // Free up resources...


		if (fg == true) {

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


            runner = null;

            retrieverThread = null;

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

            out = null;
          }; // try..finally
        };
      };
    }
    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 "SQLMatch v1.20 by Alan Maxwell";
  }; // END of getServletInfo()
};