/*
javac -classpath .:/opt/jakarta/jakarta-tomcat/shared/lib/cos.jar:/opt/jakarta/jakarta-tomcat/./common/lib/servlet.jar:/opt/jakarta/jakarta-tomcat/webapps/wsa/WEB-INF/classes/netalanmaxwell.jar:../lib/jsky.jar SSASQL.java
*/


// SQLGet 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 Sesame;
import java.io.*;
import java.util.*;
import java.sql.*;
import java.util.StringTokenizer;
import javax.servlet.*;
import javax.servlet.http.*;
//import jsky.coords.*;
import java.awt.geom.*;
import net.alanmaxwell.html.HTMLStrings;

import net.alanmaxwell.sql.SQLServerInfoJDBC;
import net.alanmaxwell.sql.SixSQLRetrieverThread;
import net.alanmaxwell.sql.SQLRetrieverThread6dF;
import net.alanmaxwell.sql.SixSQLRetrieverThread_DR3;
import net.mar.SQLRadial;
import net.mar.SQLMenu;
import net.mar.AnException;


public class SIXDFSQL_DR3 extends HttpServlet {  // 1
  private int serviceCurrent = 0;
  private int serviceTotal = 0;

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

  protected synchronized void leavingServiceMethod() { //3
    serviceCurrent--;
  };

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

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

  private boolean shuttingDown = false;

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

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


  private String dirName;
  private String tmpWeb;
  static String BASEURL;
  public void init(ServletConfig config) throws ServletException {
    super.init(config);
    dirName = config.getInitParameter("uploadDir");
    tmpWeb=config.getInitParameter("tmpWeb");
    BASEURL=config.getInitParameter("BASEURL");
  }

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

  /*  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 ra=null;
      String dec=null;
      String ra2000=null;
      String dec2000=null;
      String radius="";
      String name=null;
      String sys=null;
      String select=null;
      String action=null;
      String where=null;
      String from=null;
      String user="UNSELECTED";
      int tableid=0;
      
      String resultsPageHeading = "6dFGS SQL Query Results";

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

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

      boolean do6df = false;
      boolean useHMS = true;
      boolean doTAR = false;
      String emailTAR = null;
      String emailAddress=null;
      boolean fg=true;
      if (req.getParameter("user") != null) {
          user=req.getParameter("user") ;
      }
      // First parameter needed is the server name, without it: duh! no data
      if (req.getParameter("server") != null)
      {
        String serverName = req.getParameter("server");

        if (serverName.equalsIgnoreCase("6DFDB"))
        {
          SQLServer =
            new SQLServerInfoJDBC(
     //         "thutmose", "sixdf_dr3", "dbreader", "328purpleant"
              "ramses10", "sixdf_dr3", "palro", "palropw"
            );

          
          resultsPageHeading = "6dFGS Database - SQL Query Results";

          do6df = false;
        }
        else if (serverName.equalsIgnoreCase("GMACHINE"))
		        {
		          SQLServer =
		            new SQLServerInfoJDBC(
		//              "toshnt71.roe.ac.uk", "sixtest", "dbreader", "6dfpass"
				       "grendel01.roe.ac.uk", "SSA", "ssaro", "ssaropw"

		            );

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

		          do6df = false;
        }



        else if (serverName.equalsIgnoreCase("AMACHINE"))
		        {
		          SQLServer =
		            new SQLServerInfoJDBC(
				      // "amenhotep", "SSA", "ssaro", "ssaropw"
				      "ahmose", "SSA", "ssaro", "ssaropw"

		            );

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

		          do6df = false;
        }



        else if (serverName.equalsIgnoreCase("TOSHNT71"))
        {
          SQLServer =
            new SQLServerInfoJDBC(
              "toshnt71.roe.ac.uk", "sixtest", "dbreader", "6dfpass"
            );

          
          resultsPageHeading = "6dFGS Database - SQL Query Results";

          do6df = true;
        }
        else if (serverName.equalsIgnoreCase("IFA06W"))
        {
          SQLServer =
            new SQLServerInfoJDBC(
              "ifa06w.roe.ac.uk", "TestDB", "dbreader", "dbreaderpass"
            );

          
          resultsPageHeading = "ajwm Test 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
      {

      if (req.getParameter("radius") != null)
          {
            radius = req.getParameter("radius");
          };
      if (req.getParameter("ra") != null)
          {
            ra = req.getParameter("ra");
          };
      if (req.getParameter("dec") != null)
          {
            dec = req.getParameter("dec");
          };
      if (req.getParameter("name") != null)
	      {
	        name = req.getParameter("name");
          };
      if (req.getParameter("sys") != null)
	      {
	        sys = req.getParameter("sys");
          };
      if (req.getParameter("select") != null)
	      {
	        select = req.getParameter("select");
          };
      if (req.getParameter("from") != null)
	      {
	        from = req.getParameter("from");
          };
      if (req.getParameter("where") != null)
	      {
	        where = req.getParameter("where");
          };

      if (req.getParameter("action") != null)
	      {
	        action = req.getParameter("action");
          };

		        if (req.getParameter("sqlstmt") != null)
		  	      {
		  	        SQLQueryString=req.getParameter("sqlstmt");
          };



     // System.out.println(SQLQueryString);


   //System.out.println(SQLQueryString);
  //      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 (req.getParameter("email") != null && !req.getParameter("email").equals(""))
	      {
	        emailAddress = req.getParameter("email");
             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;
		  }




          // Do they want to return a special number of ROWS?
          if (req.getParameter("rows") != null)
          {
            try
            {
            	rowsHTML = Integer.parseInt(req.getParameter("rows"));
            }
            catch (NumberFormatException e)
            {
              rowsHTML = SixSQLRetrieverThread_DR3.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 (req.getParameter("compress") != null)
          {
            String compressParam = req.getParameter("compress");

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

          // Any special format (in addition to HTML) requested?
          if (req.getParameter("format") != null)
          {
            String formatParam = req.getParameter("format");

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

            if (formatParam.equalsIgnoreCase("FITS"))
            {
			  OutputFormat = SixSQLRetrieverThread_DR3.OUTPUT_FITS;
		    }
            else
            if (formatParam.equalsIgnoreCase("VOT"))
			            {
						  OutputFormat = SixSQLRetrieverThread_DR3.OUTPUT_VOT;
		    }
		    else
            {
              OutputFormat = SixSQLRetrieverThread_DR3.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 != SixSQLRetrieverThread_DR3.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;
          SixSQLRetrieverThread_DR3 retrieverThread = null;
          Thread runner = null;

          try
          {
            out = res.getWriter();

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

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

            if (OutputFormat != SixSQLRetrieverThread_DR3.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_radial/"
              );



              ((SQLRetrieverThread6dF) retrieverThread).setShowHMS(useHMS);
				  ((SQLRetrieverThread6dF) retrieverThread).setLinkTAR(doTAR);
              ((SQLRetrieverThread6dF) retrieverThread).setLinkEMAIL(emailTAR);*/
            }
            else
            {
				//System.out.println ("running");
              retrieverThread =
                new SixSQLRetrieverThread_DR3(SQLServer); // Server we setup earlier...

             retrieverThread.setOutputBaseDIR(
//                "/home/6df/public_html/"
				dirName
              );
              retrieverThread.setOutputBaseURL(
//                "http://www-wfau.roe.ac.uk/6dFGS/"
                   tmpWeb
              );
              retrieverThread.setOutputAppendage(
                "sql/"
              );

             retrieverThread.setShowHMS(useHMS);
             retrieverThread.setLinkTAR(doTAR);
             retrieverThread.setLinkEMAIL(emailTAR);
             retrieverThread.setUser(user);

		  }


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



           //retrieverThread.setSQLQuery("EXEC dbo.spWebExecuteSQL '"+SQLQueryString+" '");


           // System.out.println(SQLQueryString);
           // System.out.println(rowsHTML);
           // System.out.println(doRowTotal);
           // System.out.println(OutputFormat);
          //  System.out.println(OutputCompression);

           retrieverThread.setSQLQuery(SQLQueryString);
            retrieverThread.setRowsHTML(rowsHTML);
            retrieverThread.setDoRowTotal(doRowTotal);
            retrieverThread.setOutputFormat(OutputFormat);
            retrieverThread.setOutputCompression(OutputCompression);
            if ( emailAddress != null)
            {
				retrieverThread.setMightBeBackground(true);
			}







        //   out.println("   1aaa <p>");
res.flushBuffer();

            // Start a separate thread running to retrieve the SQL...
            runner = new Thread(retrieverThread);
            runner.start();
       //   out.println("   2aaa <p>");
res.flushBuffer();

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


     /*     if (action.equalsIgnoreCase("radial")) {
		        out.println("RA/Long.: " + ra2000 + " &nbsp;DEC/Lat: " + dec2000 +
		        " &nbsp; Coord. Sys (<b>B</b>1950,<b>J</b>2000 or <b>G</b>alactic): "+ sys);
			}*/

         //   out.println("   aaa <p>");
res.flushBuffer();
            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);

            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 + "scripts/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 6dFGS pages. </body></html>");

			}




            // QUERY DONE!
          }
          finally
          {
            // 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)
            	{

            	  retrieverThread.setQueryTerminated(true);
            	};

			}

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