/*
 *  $Id: MulaAjaxRestListener.java 997 2015-08-21 00:06:40Z tgutwin $
 *  $HeadURL:  $
 *  $Revision: 997 $
 *  $LastChangedDate: 2015-08-20 17:06:40 -0700 (Thu, 20 Aug 2015) $
 *  $LastChangedBy: tgutwin $
 *  Copyright (c) 2014-2015 Tom B. Gutwin P.Eng. North Vancouver BC Canada
 *
 *  This program is free software; you can redistribute it and/or
 *  modify it under the terms of the GNU General Public License
 *  as published by the Free Software Foundation; either version 3
 *  of the License, or any later version.
 *
 *  This program is distributed in the hope that it will be useful,
 *  but WITHOUT ANY WARRANTY; without even the implied warranty of
 *  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *  GNU General Public License for more details.
 *
 *  You should have received a copy of the GNU General Public License
 *  along with this program; If not, see <http://www.gnu.org/licenses/>.
 */
package ca.bc.webarts.servlet;

import ca.bc.webarts.tools.Log;
import ca.bc.webarts.tools.InvestmentTrackerQuery;
import ca.bc.webarts.widgets.Util;
import ca.bc.webarts.widgets.ResultSetConverter;
import ca.bc.webarts.tools.CategorizedStackedBarChart;

import com.oreilly.servlet.ParameterParser;
import com.oreilly.servlet.ParameterNotFoundException;
import com.oreilly.servlet.ServletUtils;

import java.text.DecimalFormat;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.InetAddress;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.UnknownHostException;
import java.util.Calendar;
import java.util.Date;
import java.util.Enumeration;
import java.util.TimeZone;
import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

import javax.servlet.*;
import javax.servlet.http.*;

import org.jfree.chart.servlet.ServletUtilities;
import org.jfree.chart.plot.PlotOrientation;
import org.jfree.chart.ChartRenderingInfo;
import org.jfree.data.category.CategoryDataset;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.entity.StandardEntityCollection;

//import javax.json.Json;

/**
 * This class is a generic listener, implemented as a servlet, for responding to AJAX and/or Rest style requests from the mula webAPP.<br />
 *
 * @author     tgutwin
 * @created    April 25, 2017
 */
public class MulaAjaxRestListener extends HttpServlet
{
  // implements SingleThreadModel

  /** Class constant. **/
  protected static final String className_ = "MulaAjaxRestListener";
  private final static String SYSTEM_FILE_SEPERATOR = File.separator;
  /**  Version String.  */
  private final static String SERVLET_VERSION = Util.spacesToCapsInString("0.100.RC.4_[$Rev: 1086 $]");

  public static final String CLIENT_SETTING_NAME_VIEWPORTWIDTH = "clientSetting.viewPortWidth";
  public static final String CLIENT_SETTING_NAME_VIEWPORTHEIGHT = "clientSetting.viewPortHeight";
  public static final String CLIENT_SETTING_NAME_BROWSER_NAME = "clientSetting.browserName";
  public static final String CLIENT_SETTING_NAME_BROWSER_VERSION = "clientSetting.browserVersion";
  public static final String CLIENT_SETTING_NAME_DEVICE_TYPE = "clientSetting.deviceType";

  /**  Build String. (yymmddhhss)  */
  private final static String BUILD_TAG = "@$BUILD_TAG$@";

  private static String webServerHostName_ = "warp4.webarts.bc.ca";

  private boolean debugOut_ = true;
  protected static Log log_= Log.getInstance();

  private int viewPortWidth_ = 0;
  private int viewPortHeight_ = 0;
  private String someParamYouWannaSet_ = "";

  /** Control for rest response format XML(default) or JSON. **/
  private boolean xmlResponse_ = false;

  /** AJAX param - **/
  private String debugOut = "";
  private int viewPortWidth = 0;
  private int viewPortHeight = 0;
  private int chartWidth = 0;
  private int chartHeight = 0;
  private String browserName = "";
  private String browserVersion = "";
  private String invetmentTrackerCall = "";
  private String portfolioName = "";
  private String portfolioFormat = "";
  private boolean portfolioRefresh = false;
  private boolean xmlResponse = xmlResponse_;
  private InvestmentTrackerQuery itq = null;
  private String buyStockName = "";
  private String buyStockSymbol = "";
  private String stockSymbol = "";
  private String marketSymbol = "";
  private boolean isMutualFund = false;
  private int portfolioId = 0;
  private String buyDate = "";
  private double numShare = 0.0;
  private double mrkSharePrice = 0.0;
  private double commish = 0.0;
  private double fee = 0.0;


  /**
   *  Gets the ServletInfo attribute of the MulaAjaxRestListener object
   *
   * @return    The ServletInfo value
   */
  public String getServletInfo()
  {
    final String methodName = "getServletInfo";
    return "WebARTS Design MulaAjaxRestListener servlet. Version:" + SERVLET_VERSION +
        "  Build:" + BUILD_TAG;
  }


  /**
   * The one time servlet init stuff goes here. It sets the derbyDBDir based on the following prioritized varables:
   * <ol><li>context init param: derbyDBDir</li><li>servlet init param (from web.xml): derbyDBDir</li>
   * <li>default hardcoded variable: derbyDBDir</li></ol>If defined in multiple places, the higher priority item will
   * be used.
   **/
  public void init()
  {
    System.out.println("\n~~~~~~~~\n~~~~~~~~\nInitializing ca.bc.webarts.servlet.MulaAjaxRestListener\n~~~~~~~~\n~~~~~~~~");

    boolean notFoundInit = true;
    boolean notFoundContext = true;
    java.util.Enumeration <String> initEnum = getInitParameterNames();
    for (; notFoundInit && initEnum.hasMoreElements();)
    {
      if(initEnum.nextElement().equals("someParamYouWannaSet"))
      {
        notFoundInit = false;
        /* Do something with the init param */
        someParamYouWannaSet_ = getInitParameter("someParamYouWannaSet");
        System.out.println("\n~~~~~~~~\n     INIT ServletParam: someParamYouWannaSet="+someParamYouWannaSet_);
      }
    }

    //also check context
    initEnum = getServletConfig().getServletContext().getInitParameterNames();
    for (; notFoundContext && initEnum.hasMoreElements();)
    {
      if(initEnum.nextElement().equals("someParamYouWannaSet"))
      {
        notFoundContext = false;
        /* Do something with the init param */
        someParamYouWannaSet_ = getServletConfig().getServletContext().getInitParameter("derbyDBDir");
        System.out.println("\n~~~~~~~~\n     INIT ContextParam: someParamYouWannaSet="+someParamYouWannaSet_);
      }
    }

    if(notFoundInit && notFoundContext )
    {
      /* Set the default values */
      //eagleDBDir_ = pEye_.getDerbyDBDir();
    }

    try
    {
      webServerHostName_ = InetAddress.getLocalHost().getHostName();
    }
    catch (UnknownHostException ex)
    {
      webServerHostName_ = "red.webarts.bc.ca";
    }
  }


  /**  Override to close Things  **/
  public void destroy()
  {
    super.destroy();
  }



  /**
   * Returns the value of viewPortWidth_.
   */
  public int getViewPortWidth() {
    return viewPortWidth_;
  }


  /**
   * Sets the value of viewPortWidth_.
   * @param viewPortWidth The value to assign viewPortWidth_.
   */
  public void setViewPortWidth(int viewPortWidth) {
    this.viewPortWidth_ = viewPortWidth;
  }


  /**
   * Returns the value of viewPortHeight_.
   */
  public int getViewPortHeight() {
    return viewPortHeight_;
  }


  /**
   * Sets the value of viewPortHeight_.
   * @param viewPortWidth The value to assign viewPortWidth_.
   */
  public void setViewPortHeight(int viewPortHeight) {
    this.viewPortHeight_ = viewPortHeight;
  }


  /**
   * Returns the value of debugOut_.
   */
  public boolean getDebugOut()
  {
    return debugOut_;
  }


  /**
   * Sets the value of debugOut_.
   * @param debugOut The value to assign debugOut_.
   */
  public void setDebugOut(boolean debugOut)
  {
    this.debugOut_ = debugOut;
    log_.setLogLevel((debugOut_?log_.DEBUG:log_.MINOR));
  }


  /**
    * Get Method for class field 'xmlResponse'.
    *
    * @return boolean - The value the class field 'xmlResponse' XML (true) or JSON (false).
    *
    **/
  public boolean getXmlResponse()
  {
    return xmlResponse_;
  }  // getXmlResponse Method


  /**
    * Set Method for class field 'xmlResponse' that controls if teh rest response is in XML (true) or JSON (false).
    *
    * @param xmlResponse is the value to set this class field to XML (true) or JSON (false).
    *
    **/
  public  void setXmlResponse(boolean xmlResponse)
  {
    this.xmlResponse_ = xmlResponse;
  }  // setXmlResponse Method


  /**
   * streams to a zipped out stream (without creating a file).
   * from: http://www.coderanch.com/t/276892/java-io/java/Stream-data-ZipOutputStream
   * @param servletOutput is the stream to zip into
   * @param dataName are the pseudo fileName that will get created in the zip (pseudo file) stream
   * @param dataStream the data to zip
   **/
  private void streamZIP(OutputStream servletOut, String dataName, InputStream dataStream)
  {
    String[] dataNames = {dataName};
    InputStream[] dataStreams = {dataStream};
    streamZIP(servletOut, dataNames,dataStreams);
  }


  /**
   * streams to a zipped out stream (without creating a file).
   * from: http://www.coderanch.com/t/276892/java-io/java/Stream-data-ZipOutputStream
   *
   *  Sample servlet doGet...<br />
   *  public void doGet(HttpServletRequest request,
                        HttpServletResponse response) throws IOException{
        response.setContentType("text/plain");
        response.setHeader("Content-Disposition",
                           "attachment;filename=downloadname.txt");
        String[] dataNames = {dataName};
        InputStream[] dataStreams = {dataStream};
        OutputStream os = response.getOutputStream();
        streamZIP(servletOut, dataNames,dataStreams);
      }
   *
   *
   * @param servletOutput is the stream to zip into
   * @param dataNames are the pseudo fileNames that will get created in the zip (pseudo file) stream
   * @param dataStreams the data to zip
   **/
  private void streamZIP(OutputStream servletOut,
                        String[] dataNames,
                        InputStream[] dataStreams)
  {
    ZipOutputStream zos = new ZipOutputStream(servletOut);
    final int DATA_BLOCK_SIZE = 2048;
    int byteCount;
    byte[] data;

    try
    {
      for(int i = 0; i < dataNames.length; i++)
      {
        ZipEntry ze = new ZipEntry(dataNames[i]);
        zos.putNextEntry(ze);
        data = new byte[DATA_BLOCK_SIZE];

        while((byteCount = dataStreams[i].read(data, 0, DATA_BLOCK_SIZE)) != -1)
        {
          zos.write(data, 0, byteCount);
        }

        zos.flush();
        zos.closeEntry();
        dataStreams[i].close();
      }
      zos.close();
    }
    catch(Exception e)
    {
      System.err.println("Problem streaming zip data " + e.toString());
    }
  }


  /** Parses the REST command string and prepares the response in XML format.
    * @param restPath is the rest command part of the rest URL
    * @return a string holding the XML rest response
    **/
  private String parseRestRequest(String restPath)
  {
    String restResponse = null;
    System.out.println("REST Request: " +restPath);
    String pre = "/";

    DecimalFormat dfp = new DecimalFormat( "##0" );
    DecimalFormat dfe = new DecimalFormat( "###0.000" );
    if(restPath.equals(pre+"power/current"))
    {
      StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
      sb.append("</RestResponse>");
    }
    else if(restPath.equals(pre+"power/recent"))
    {
      StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
      sb.append("</RestResponse>");
      restResponse = sb.toString();
    }
    else if(restPath.equals(pre+"energy/recent"))
    {
      StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
      sb.append("</RestResponse>");
      restResponse = sb.toString();
    }
    else if(restPath.equals(pre+"energy/current"))
    {
      StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
      /*  Add In the response content  */
      sb.append("</RestResponse>");
      restResponse = sb.toString();
    }
    else if(restPath.equals(pre+"price/current"))
    {
      StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
      /*  Add In the response content  */
      sb.append("</RestResponse>");
      restResponse = sb.toString();
    }
    else if(restPath.equals(pre+"price/label/current"))
    {
      StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
      /*  Add In the response content  */
      sb.append("</RestResponse>");
      restResponse = sb.toString();
    }
    return restResponse;
  }


  /** Parses any defined servlet params into the defined class vars. **/
  private void parseParams(HttpServletRequest req)
  {
    /* http://www.servlets.com/cos/javadoc/com/oreilly/servlet/ParameterParser.html */
    ParameterParser parser = new ParameterParser(req);
    if(debugOut_)
    {
      System.out.println("\nRequest Params:");
      Enumeration<String> parmNames = req.getParameterNames();
      for (; parmNames.hasMoreElements();)
      {
        String currParamName = (String) parmNames.nextElement();
        System.out.println("  "+currParamName+"="+req.getParameter(currParamName));
      }
    }

    /* parse out the request params into class vars  */
    debugOut = parser.getStringParameter("debugOut", "");
    viewPortWidth =  parser.getIntParameter("viewPortWidth",0);
    viewPortHeight =  parser.getIntParameter("viewPortHeight",0);
    chartWidth =  parser.getIntParameter("chartWidth",0);
    chartHeight =  parser.getIntParameter("chartHeight",0);
    browserName = parser.getStringParameter("clientBrowser","");
    browserVersion = parser.getStringParameter("clientBrowserVersion","");
    xmlResponse = parser.getBooleanParameter("xmlResponse",xmlResponse_);
    invetmentTrackerCall = parser.getStringParameter("invetmentTrackerCall","");
    portfolioName = parser.getStringParameter("portfolioName","");
    portfolioFormat = parser.getStringParameter("portfolioFormat","");
    portfolioRefresh = parser.getBooleanParameter("portfolioRefresh",false);
    stockSymbol = parser.getStringParameter("stockSymbol","");
    buyStockSymbol = parser.getStringParameter("buyStockSymbol","");
    buyStockName = parser.getStringParameter("buyStockName","");
    isMutualFund = parser.getBooleanParameter("isMutualFund",false);
    portfolioId =  parser.getIntParameter("portfolioId",0);
    marketSymbol = parser.getStringParameter("marketSymbol","");
    buyDate  = parser.getStringParameter("buyDate","");
    numShare =  parser.getDoubleParameter("numShare",0.0);
    mrkSharePrice =  parser.getDoubleParameter("mrkSharePrice",0.0f);
    commish =  parser.getDoubleParameter("commish",0.0f);
    fee =  parser.getDoubleParameter("fee",0.0f);

   }


  /** Convert a well-formed (but not necessarily valid) XML string into a JSON String. **/
  private String xmlToJson(String xml)
  {
     String jsonStr = "";
     if (xml== null || xml.length()<2) xml = "";
     org.json.JSONObject jsonObj = org.json.XML.toJSONObject(xml);
     if(jsonObj!= null) jsonStr = jsonObj.toString(2);
     return jsonStr;
   }


  /**
   *  This method handles the "GET" submission - it is used for Ajax calls to set webApp parameters using JQuery.
   *
   * @param  req                   Description of the Parameter
   * @param  res                   Description of the Parameter
   * @exception  ServletException  Description of the Exception
   * @exception  IOException       Description of the Exception
   */
  public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
  {
    parseParams(req);

    ServletOutputStream out = res.getOutputStream();
    String conPath = req.getContextPath();
    String restPath = req.getPathInfo();  // basically anything added to the end of the URL is considered a RESt request

    if(debugOut_)
    {
      System.out.println("\n~~~~~~~~~~~~~~~~~~~~~~~~~~~");
      System.out.println("MulaAjaxRestListener.doGet "+ "   debugOut="+debugOut);
      System.out.println("~~~~~~~~~~~~~~~~~~~~~~~~~~~");
    }

    boolean ajaxCall = false;
    boolean restRequest = false;
    String ajaxResponse = "";
    String restResponse = null;

    res.setStatus(HttpServletResponse.SC_OK);  // default ids OK,, set again lower downif NOT

    ajaxResponse = checkForAjaxRequest(req);
    if (!ajaxResponse.equals("")) ajaxCall=true;

    if(!ajaxCall) restResponse = checkForRestRequest(req);
    if (restResponse!=null && !restResponse.equals("")) restRequest=true;

    if(debugOut_) { System.out.println("ajaxCall="+ajaxCall+"   restRequest="+restRequest );}
    /* * * * * *  * * * * * EMPTY * * * * *  * * * * *  */
    /* ***********************************************  */
    if( !ajaxCall && !restRequest) // EMPTY query and NO parms... it was *NOT* a JQuery Ajax call or a REST call, so just present some status back to user
    {
      sendServletAdminPage(req, res);
    }

    if(ajaxCall)
    {
      res.setContentType("text/plain");
      if(out!=null  )
      {
        if (debugOut_)
          System.out.println("AJAX REPLY("+ajaxResponse.length()+")="+ajaxResponse);
        out.print(ajaxResponse);
      }
      else
        System.out.println("HttpServletResponse.ServletOutputStream is NOT ready");
    }
    else if(restRequest)
    {
      if (xmlResponse_) res.setContentType("text/xml");
      else res.setContentType("application/javascript");
      if(restResponse!=null && !restResponse.equals(""))
      {
        res.setStatus(HttpServletResponse.SC_OK);
      }
      else
      {
        res.setStatus(HttpServletResponse.SC_NOT_FOUND);
        restResponse = "<RestResponse succeeded=\"false\"><status>404</status></RestResponse>";
      }
      if(out!=null  )
      {
        if (!xmlResponse_) restResponse = xmlToJson(restResponse);
        if (debugOut_)
          System.out.println("REST REPLY("+restResponse.length()+")="+restResponse);
        out.print(restResponse);
      }
      else
        System.out.println("HttpServletResponse.ServletOutputStream is NOT ready");
    }
    out.flush();
    res.flushBuffer();
  }


  /**
   *  This method handles the "POST" submissions.
   *
   * @param  req                   Description of Parameter
   * @param  res                   Description of Parameter
   * @exception  ServletException  Description of Exception
   * @exception  IOException       Description of Exception
   */
  public void doPost(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
  {
    //parseParams(req);

    StringBuffer postBody = new StringBuffer("");
    String line = null;
      BufferedReader reader = req.getReader();
      while ((line = reader.readLine()) != null)
        postBody.append(line+"\n");

      String postReply = "";

      // Do something with the posted message
      // send it to a helper class if desired
      /*String postReply = pEye_.newMessage(postBody.toString()); */

      // send back an empty reply
      if ((postReply==null || postReply.equals("")))
      {
        // send back a 200 with an empty body
        res.setStatus(HttpServletResponse.SC_OK);
        res.setContentLength(0);
        postReply = "";
        if (debugOut_) System.out.println("MulaAjaxRestListener.doPost: emptyreply from newMessage");
      }

      postReply = "\n"+postReply;
      res.setContentType("application/xml");
      res.setStatus(HttpServletResponse.SC_OK);
      res.setContentLength(postReply.length());

      ServletOutputStream out = res.getOutputStream();
      if(out!=null  )
      {
        if (debugOut_) System.out.println("Attempting to HttpServletResponse REPLY("+postReply.length()+")="+postReply);
        out.print(postReply);
        out.flush();
      }
      else
        System.out.println("HttpServletResponse.ServletOutputStream is NOT ready");

      res.flushBuffer();
  }


  /** Parses the REST command string and prepares the response in XML format.
    * @param restPath is the rest command part of the rest URL
    * @return a string holding the XML rest response OR null if not a REST Request
    **/
  private String checkForRestRequest(HttpServletRequest req) throws ServletException, IOException
  {
    String restResponse = null;
    String servletPath = req.getServletPath();
    String restPath = req.getPathInfo();  // basically anything added to the end of the URL is considered a RESt request
    String pre = "/";
    boolean returnXml = xmlResponse_; // false means JSON

    if (servletPath.equals("/rest") && restPath!=null && !restPath.equals("") )
    {

      DecimalFormat dfp = new DecimalFormat( "##0" );
      DecimalFormat dfe = new DecimalFormat( "###0.000" );
      if(restPath.equals(pre+"test"))
      {
        StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
        /*  Add In the response content  */
        sb.append("<testNode className=\"");
        sb.append(className_);
        sb.append("\">");
        sb.append("Hello");
        sb.append("</testNode>");
        sb.append("</RestResponse>");
        restResponse = sb.toString();
      }
      if(restPath.equals(pre+"power/current"))
      {
        StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
        /*  Add In the response content  */
        sb.append("</RestResponse>");
        restResponse = sb.toString();
      }
      else if(restPath.equals(pre+"power/recent"))
      {
        StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
        /*  Add In the response content  */
        sb.append("</RestResponse>");
        restResponse = sb.toString();
      }
      else if(restPath.equals(pre+"energy/recent"))
      {
        StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
        /*  Add In the response content  */
        sb.append("</RestResponse>");
        restResponse = sb.toString();
      }
      else if(restPath.equals(pre+"energy/current"))
      {
        StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
        /*  Add In the response content  */
        sb.append("</RestResponse>");
        restResponse = sb.toString();
      }
      else if(restPath.equals(pre+"price/current"))
      {
        StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
        /*  Add In the response content  */
        sb.append("</RestResponse>");
        restResponse = sb.toString();
      }
      else if(restPath.equals(pre+"price/label/current"))
      {
        StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
        /*  Add In the response content  */
        sb.append("</RestResponse>");
        restResponse = sb.toString();
      }

      // Database Queries
      /*
          - Dump the database tables directly to (JSON or ) XML using a REST call:
            http://red.webarts.bc.ca:8080/powereye/rest/db/table/<tablename>
              <tablename> can be reading, energy, load_device, load_type

      or with the optional <number of rows>
         positive number means sorted ascending (oldest 1st)
         negative number means sorted descending (newest 1st)
            http://red.webarts.bc.ca:8080/powereye/rest/db/table/<tablename>/100

      These database calls are LOOOOooong so you might want to use a commandline URL query tool like
      <a href="http://www.gnu.org/software/wget/">wget</a>, or perl or java or whatever.
      */
      else if(restPath.startsWith(pre+"db/table/"))
      {
        returnXml = true; // false means JSON
        String content = "";
        try
        {
          restResponse = "<RestResponse succeeded=\"false\"></RestResponse>";
          int tblIndex = (pre+"db/table/").length();
          int numRowsIndex = restPath.indexOf("/",tblIndex)+1; // /rest/db/table/reading/99
          boolean numRowsWasSpecified = (numRowsIndex>0);
          int numReadings =  (numRowsWasSpecified?Integer.parseInt(restPath.substring(numRowsIndex)):0); //0 means send all
          boolean sortAscendingByDate = false;           // sort default is descending
          if(numReadings<0) // negative numbers means ascending
          {
            sortAscendingByDate = true;
            numReadings *= -1; // now normalize it to be a psitive rowCount
          }
          String tableName = (numRowsWasSpecified?restPath.substring(tblIndex,numRowsIndex-1):restPath.substring(tblIndex));

          System.out.println("Dumping DB Table: "+tableName);
          if(numRowsWasSpecified) System.out.println("         numRows: "+numReadings +" "+
                                                    (sortAscendingByDate?"ascending":"descending"));
          java.sql.ResultSet rs = null;
          if(tableName.equalsIgnoreCase("reading"))
            if(numRowsWasSpecified)
            {
              //rs = pEye_.dumpReadingTableToRS(sortAscendingByDate,(numReadings!=0?numReadings:-1));//dumpReadingTableToRS();
            }
            else
            {
              //rs = pEye_.dumpReadingTableToRS();
            }
          else if(tableName.equalsIgnoreCase("energy"))
            if(numRowsWasSpecified)
            {
              //rs = pEye_.dumpEnergyTableToRS(sortAscendingByDate,(numReadings!=0?numReadings:-1));//dumpEnergyTableToRS();
            }
            else
            {
              //rs = pEye_.dumpEnergyTableToRS();
            }
          else if(tableName.equalsIgnoreCase("load_type"))
            if(numRowsWasSpecified)
            {
              //rs = pEye_.dumpDBTableToRS("load_type",numReadings);//dumpLoadTypeTableToRS();
            }
            else
            {
              //rs = pEye_.dumpLoadTypeTableToRS();
            }
          else if(tableName.equalsIgnoreCase("load_device"))
            if(numRowsWasSpecified)
            {
              //rs = pEye_.dumpDBTableToRS("load_device",numReadings);//dumpLoadDeviceTableToRS();
            }
            else
            {
              //rs = pEye_.dumpLoadDeviceTableToRS();
            }

          if(rs!=null)
          {
            ResultSetConverter rsc = new ResultSetConverter(rs);
            if (returnXml) content = rsc.convertToXml().toXML().substring("<?xml version=\"1.0\"?>".length());
            else content = rsc.convertToJson().toString();
            StringBuilder sb = new StringBuilder("<RestResponse succeeded=\"true\">");
            /*  Add In the response content  */
              sb.append("<db table=\""+tableName+"\">");
              sb.append(content);
              sb.append("</db>");

            sb.append("</RestResponse>");
            restResponse = sb.toString();
          }
        }
        catch(Exception ioEx)
        {
          restResponse = "<RestResponse succeeded=\"false\"></RestResponse>";
        }
      }
    }

    return restResponse;
  }


  /** Checks if this Request is an AJAX request and prepares the response in XML format.
    * @param req the servlet request
    * @return a string holding the response value to be returned OR null if not an expected/Valid AJAX Request
    **/
  private String checkForAjaxRequest(HttpServletRequest req) throws ServletException, IOException
  {
    String ajaxResponse = "";
     // was  it a JQuery Ajax call for info...  (such as eagleDBDir_)
    if (debugOut!=null &&!debugOut.equals("") )
    {
        debugOut_ =  Boolean.parseBoolean(debugOut);
        setDebugOut(debugOut_);
        //pEye_.setDebugOut(debugOut_);
        System.out.println("MulaAjaxRestListener.doGet - debugOut_="+debugOut_);
        ajaxResponse = ""+debugOut_;
    }
    else if (viewPortWidth!=0 )
    {
      // do something with the passed parms
      viewPortWidth_=viewPortWidth;
      req.getSession().setAttribute(CLIENT_SETTING_NAME_VIEWPORTWIDTH, viewPortWidth);
      ajaxResponse = ""+viewPortWidth_;
    }
    else if (viewPortHeight!=0 )
    {
      // do something with the passed parms
      viewPortHeight_=viewPortHeight;
      req.getSession().setAttribute(CLIENT_SETTING_NAME_VIEWPORTHEIGHT, viewPortHeight);
      ajaxResponse = ""+viewPortHeight_;
    }
    else if (browserName!=null &&!browserName.equals("") )
    {
      req.getSession().setAttribute(CLIENT_SETTING_NAME_BROWSER_NAME, browserName);
      ajaxResponse = ""+browserName;
    }
    else if (browserVersion!=null &&!browserVersion.equals("") )
    {
      req.getSession().setAttribute(CLIENT_SETTING_NAME_BROWSER_VERSION, browserVersion);
      ajaxResponse = ""+browserVersion;
    }
    // Ajax Call - invetmentTrackerCall
    if (invetmentTrackerCall!=null && !invetmentTrackerCall.equals("")  )
    {
      System.out.println("Ajax Call - invetmentTrackerCall="+invetmentTrackerCall+"("+portfolioName+")"+(portfolioRefresh?" forcing a refresh":""));
      HttpSession session = req.getSession(true);
      System.out.println("  MulaAjaxRestListener SessionID: "+session.getId() );
      if ( connectToITQ())
      {
        session.setAttribute("MulaAjaxRestListener.investmentTrackerQuery", itq);
        /* queryPortfolio method HTML */
        //http://10.0.0.253/mula/ajax/?invetmentTrackerCall=queryPortfolio&portfolioName=TFSA
        if("queryPortfolio".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(portfolioName!=null && !portfolioName.equals("")
              && portfolioFormat!=null && portfolioFormat.equalsIgnoreCase("html"))
          {
            String portfolioTableID = "portfolioTable";
            ajaxResponse = itq.htmlTableFormatResultSet(new StringBuilder(itq.queryPortfolio(portfolioName,false)),
                                                               portfolioTableID,
                                                               "w3-table totals-table w3-striped w3-animate-bottom w3-hoverable w3-tiny ",
                                                               "w3-theme-d3 w3-center",
                                                               "",
                                                               "w3-center",
                                                               "w3-btn w3-white w3-border w3-border-green w3-hover-aqua w3-round-xxlarge");
          }
        }
        /* queryPortfolio method XML*/
        else if("queryPortfolio".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(portfolioName!=null && !portfolioName.equals("")
              && portfolioFormat!=null && portfolioFormat.equalsIgnoreCase("xml"))
          {
            ajaxResponse = itq.queryPortfolio(portfolioName);
            //ResultSetConverter rsc = new ResultSetConverter(ajaxResponse);
            //ajaxResponse = rsc.convertToXml().toXML().substring("<?xml version=\"1.0\"?>".length());
          }
        }
        /* queryPortfolio method JSON (default)*/
        else if("queryPortfolio".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(portfolioName!=null && !portfolioName.equals("")
              && (  portfolioFormat==null || portfolioFormat.equalsIgnoreCase("json") || portfolioFormat.equals("")))
          {
            ajaxResponse = itq.queryPortfolio(portfolioName);
            ajaxResponse = Util.csvToJSON(ajaxResponse,"|");
          }
        }
        /* loadStockDaily method */
        else if("loadStockDaily".equalsIgnoreCase(invetmentTrackerCall))
        {
          float xchgRate =  itq.getCurrencyExchangeRateFromRDF();
          ajaxResponse = itq.loadStockDaily();
          try
          {
            ajaxResponse = Util.csvToJSON(ajaxResponse,"|");
          }
          catch(java.lang.ArrayIndexOutOfBoundsException aoobEx)
          {
            System.out.println("Exception:   MulaAjaxRestListener - csvToJSON for loadStockDaily ajaxResponse:");
            System.out.println(ajaxResponse);
            ajaxResponse = "null/null";
          }
        }
        /* createPortfolioPerformanceChart method */
        else if("createPortfolioPerformanceChart".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(chartWidth==0) chartWidth = viewPortWidth_;
          if(chartHeight==0) chartHeight = viewPortHeight_;
          if(portfolioName!=null && !portfolioName.equals(""))
          {
            JFreeChart cbc = itq.createPortfolioPerformanceChart(portfolioName);
            ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());
            ajaxResponse = ServletUtilities.saveChartAsPNG(cbc, chartWidth, chartHeight, info, session);
          }
          else
          {
            JFreeChart cbc = itq.createPortfolioPerformanceChart("");
            ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());
            ajaxResponse = ServletUtilities.saveChartAsPNG(cbc, chartWidth, chartHeight, info, session);
          }
        }
        /* createStockChart method */
        else if("createStockChart".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(chartWidth==0) chartWidth = viewPortWidth_;
          if(chartHeight==0) chartHeight = viewPortHeight_;
          if(stockSymbol!=null && !stockSymbol.equals(""))
          {
            JFreeChart cbc = itq.createPriceVolumeChart(stockSymbol);
            ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());
            ajaxResponse = ServletUtilities.saveChartAsPNG(cbc, chartWidth, chartHeight, info, session);
          }
          else
          {
            JFreeChart cbc = itq.createPortfolioPerformanceChart("");
            ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());
            ajaxResponse = ServletUtilities.saveChartAsPNG(cbc, chartWidth, chartHeight, info, session);
          }
        }
        /* createExchangeChart method */
        else if("createExchangeChart".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(chartWidth==0) chartWidth = viewPortWidth_;
          if(chartHeight==0) chartHeight = viewPortHeight_;
          JFreeChart cbc = itq.createExchangeChart();
          ChartRenderingInfo info = new ChartRenderingInfo(new StandardEntityCollection());
          ajaxResponse = ServletUtilities.saveChartAsPNG(cbc, chartWidth, chartHeight, info, session);
        }
        /* dumpPriceVolumeDataStr method  for the chart*/
        else if("dumpPriceVolumeDataStr".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(stockSymbol!=null && !stockSymbol.equals(""))
          {
            ajaxResponse = itq.dumpPriceVolumeDataStr(stockSymbol, 100);
            ajaxResponse.replace('|','\t');
            if(portfolioFormat.equalsIgnoreCase("json"))
            {
              ajaxResponse = Util.csvToJSON(ajaxResponse,"|");
            }
          }
        }
        /* queryBuyStock method */
        else if("queryBuyStock".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(buyStockSymbol!=null && !buyStockSymbol.equals("")
              && buyStockName!=null && !buyStockName.equals("")
            )
          {
            ajaxResponse = itq.queryBuyStock(buyStockSymbol, buyStockName, isMutualFund, portfolioId, marketSymbol,
                                             buyDate, numShare, mrkSharePrice, commish, fee);
          }
        }
        /* queryListStockMarket method */
        else if("queryListStockMarket".equalsIgnoreCase(invetmentTrackerCall))
        {
           String adminTableID = "adminTable";
           String csvStr = itq.queryListStockMarket();
           ajaxResponse = itq.htmlTableFormatResultSet(new StringBuilder(csvStr),
                                                               false,
                                                               adminTableID,
                                                               "w3-table w3-striped w3-animate-top w3-hoverable w3-tiny ",
                                                               "w3-theme-d3 w3-center",
                                                               "",
                                                               "w3-center",
                                                               "w3-btn w3-white w3-border w3-border-green w3-hover-aqua w3-round-xxlarge");
        }
        /* queryListStockMarket method */
        else if("queryListCurrencyExchangeRate".equalsIgnoreCase(invetmentTrackerCall))
        {
          String adminTableID = "adminTable";
          String csvStr = itq.queryListCurrencyExchangeRate();
          ajaxResponse = itq.htmlTableFormatResultSet(new StringBuilder(csvStr),
                                                               false,
                                                               adminTableID,
                                                               "w3-table w3-striped w3-animate-top w3-hoverable w3-tiny ",
                                                               "w3-theme-d3 w3-center",
                                                               "",
                                                               "w3-center",
                                                               "w3-btn w3-white w3-border w3-border-green w3-hover-aqua w3-round-xxlarge");
       }
        /* queryListStockMarket method */
        else if("queryStockDaily".equalsIgnoreCase(invetmentTrackerCall))
        {
           String adminTableID = "adminTable";
           String csvStr = itq.queryStockDaily();
           ajaxResponse = itq.htmlTableFormatResultSet(new StringBuilder(csvStr),
                                                               false,
                                                               adminTableID,
                                                               "w3-table w3-striped w3-animate-top w3-hoverable w3-tiny ",
                                                               "w3-theme-d3 w3-center",
                                                               "",
                                                               "w3-center",
                                                               "w3-btn w3-white w3-border w3-border-green w3-hover-aqua w3-round-xxlarge");
        }
        /* queryListPortfolios method */
        else if("queryListPortfolios".equalsIgnoreCase(invetmentTrackerCall))
        {
           String adminTableID = "adminTable";
           String csvStr = itq.queryListPortfolios();
           ajaxResponse = itq.htmlTableFormatResultSet(new StringBuilder(csvStr),
                                                               false,
                                                               adminTableID,
                                                               "w3-table w3-striped w3-animate-top w3-hoverable w3-tiny ",
                                                               "w3-theme-d3 w3-center",
                                                               "",
                                                               "w3-center",
                                                               "w3-btn w3-white w3-border w3-border-green w3-hover-aqua w3-round-xxlarge");
       }
       /* queryListAllTransactions method */
       else if("queryListAllTransactions".equalsIgnoreCase(invetmentTrackerCall))
       {
           String adminTableID = "adminTable";
           String csvStr = itq.queryListAllTransactions();
           ajaxResponse = itq.htmlTableFormatResultSet(new StringBuilder(csvStr),
                                                               false,
                                                               adminTableID,
                                                               "w3-table w3-striped w3-animate-top w3-hoverable w3-tiny ",
                                                               "w3-theme-d3 w3-center",
                                                               "",
                                                               "w3-center",
                                                               "w3-btn w3-white w3-border w3-border-green w3-hover-aqua w3-round-xxlarge");
        }
        /* downloadStockDaily method */
        else if("downloadStockDaily".equalsIgnoreCase(invetmentTrackerCall))
        {
          if(portfolioName!=null && !portfolioName.equals("")
              && (  portfolioFormat==null || portfolioFormat.equalsIgnoreCase("csv") || portfolioFormat.equals("")))
          {
            float xchgRate =  itq.getCurrencyExchangeRateFromRDF();
            ajaxResponse = itq.queryPortfolio(portfolioName,true);
          }
          else if(portfolioName!=null && !portfolioName.equals("")
              && (  portfolioFormat!=null && portfolioFormat.equalsIgnoreCase("json") ))
          {
            float xchgRate =  itq.getCurrencyExchangeRateFromRDF();
            ajaxResponse = itq.queryPortfolio(portfolioName,true);
            try
            {
              ajaxResponse = Util.csvToJSON(ajaxResponse,"|");
            }
            catch(java.lang.ArrayIndexOutOfBoundsException aoobEx)
            {
              System.out.println("Exception:   MulaAjaxRestListener - csvToJSON for loadStockDaily ajaxResponse:");
              System.out.println(ajaxResponse);
              ajaxResponse = "null/null";
            }
          }
        }
      }
      else
      {
        System.out.println("/n/n------------------------------/nAjax connection to InvestmentTrackerQuery FAILED");
        ajaxResponse = "null/null";
      }
    }

    return ajaxResponse;
  }


  /** Connects the InvestmentTrackerQuery helper. **/
  private boolean connectToITQ()
  {
    if(itq==null) itq=new InvestmentTrackerQuery();

    return (itq!=null);
  }



  /** Prepares and returns basic info page about the server. **/
  private void sendServletAdminPage(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
  {
    ServletOutputStream out = res.getOutputStream();
    ServletContext context = getServletContext();
    java.util.Properties sysProps = System.getProperties();
    TimeZone localTz = Calendar.getInstance().getTimeZone();
    int offset = localTz.getRawOffset();
    // shift Daylight savings hour if needed
    Date dateNow = new Date();
    boolean dst = localTz.inDaylightTime(dateNow);
    int dstOffset = localTz.getDSTSavings();
    //if(dst) offset = offset + dstOffset;

    String prop = "";

      // Send Back a response to be presented to the users browser
      res.setContentType("text/html");
      out.println("<html>\n");
      out.println("<head>\n");
      out.println("<title>MULA Ajax / REST Listener - Servlet Console</title>\n");
      out.println("<link rel=\"icon\"");
      out.println("     type=\"image/svg+xml\"");
      out.println("     href=\"../img/favicon.svg\" />");
      out.println("<style>");
      out.println("H1 { font-weight: bolder; background-color: #020; color: #ffffbb;  font-size: 200%}");
      out.println("H2 { font-weight: bolder; color: #ffff99;  font-size: 160%; text-decoration: underline}");
      out.println("H3 { font-weight: bolder; color: #ffff44;  font-size: 140%}");
      out.println("H4, H5 { color: #ffffff }");
      out.println("A { color: #ffaadd }");
      out.println("DT { font-weight: bolder }");
      out.println("LI { line-height: 125% }");
      out.println("BODY { background-color: #101066; color: #ffff44; font-size: 100%; line-height: 110% }");
      out.println(".val { color: #ffffff; font: \"Lucida Console\", Monaco, monospace  }");
      out.println("");
      out.println("");
      out.println("");
      out.println("</style>");

      //out.println("<META HTTP-EQUIV=\"refresh\" content=\"2;URL=\"javascript:history.go(-2);\">\n");
      out.println("</head>\n");
      out.println("<body>");
      out.println("<h1>Web<i>ARTS</i> <a href=\""+req.getContextPath()+"\">Mula</a> Ajax / REST Listener</h1><h2>Servlet Console</h2>");
      out.println(getServletInfo());
      out.println(" <h3>Parameters</h3>");
      out.println("  <ul>");
      out.println("    <li>debug          = <span class=\"val\">"+debugOut_+"</span></li>");
       out.println("<br />");
      out.println("    <li>Servlet Init Params:");
      out.println("        <ul>");

      java.util.Enumeration <String> initEnum = getInitParameterNames();
      String currParamName = "";
      for (; initEnum.hasMoreElements();)
      {
        currParamName = initEnum.nextElement();
        out.print("            <li>");
        out.print(currParamName+" = <span class=\"val\">"+getInitParameter(currParamName));
        out.println("</span></li>");
      }
      out.println("       </ul>");
      out.println("    </li>"); // servlet init params

      out.println("<br />");
      out.println("    <li>Context Params for "+context.getServletContextName()+":");
      out.println("        <ul>");
        out.print("          <li>");
        out.print("ContextPath"+" = <span class=\"val\">"+context.getContextPath());
        out.println("</span></li>");
        out.print("          <li>");
        out.print("Real Path For Context Root"+" = <span class=\"val\">"+context.getRealPath("/"));
        out.println("</span></li>");
      out.println("       </ul>");
      out.println("  </li>"); // CONTEXT  params

      out.println("<br />");
      out.println("  <li>Context <b>Init</b> Params:");
      out.println("      <ul>");
      initEnum = context.getInitParameterNames();
      currParamName = "";
      for (; initEnum.hasMoreElements();)
      {
        currParamName = initEnum.nextElement();
        out.print("          <li>");
        out.print(currParamName+" = <span class=\"val\">"+context.getInitParameter(currParamName));
        out.println("</span></li>");
      }
      out.println("       </ul>");
      out.println("    </li>"); // context init params

      out.println("  </ul>");

      out.println("<br /><br />");
      out.println("<h3>Server Status</h3>");
      out.println("  <ul>");
      out.println("    <li>Server name = <span class=\"val\">"+req.getServerName()+" ("+req.getLocalAddr()+")</span></li>");
      out.println("    <li>Server port = <span class=\"val\">"+req.getServerPort()+"</span></li>");
      out.println("    <li>WebApp Engine = <span class=\"val\">"+context.getServerInfo()+"</span></li>");
      out.println("    <li>Supported Servlet Version = <span class=\"val\">"+context.getMajorVersion()+"."+context.getMinorVersion()+"</span></li>");

      if(context.getMajorVersion()>2 && context.getMinorVersion()>-1)
          out.println("      <ul><li>Effective Version = <span class=\"val\">"+
                      context.getEffectiveMajorVersion()+"."+context.getEffectiveMinorVersion()+"</span></li></ul>");
      out.println("      </li>");
      if(context.getMajorVersion()>2 && context.getMinorVersion()>0)
      {
        try {
          out.println("    <li>Logical Hostname = <span class=\"val\">"+context.getVirtualServerName()+"</span></li>");
        } catch (Exception ex) {}
      }

      out.println("    <li>System Properties\n      <ul>");
      prop = "java.vendor";
      out.println("       <li>"+prop+" = <span class=\"val\">"+sysProps.getProperty(prop)+"</span></li>");
      prop = "java.runtime.version";
      out.println("       <li>"+prop+" = <span class=\"val\">"+sysProps.getProperty(prop)+"</span></li>");
      prop = "java.version";
      out.println("       <li>"+prop+" = <span class=\"val\">"+sysProps.getProperty(prop)+"</span></li>");
      prop = "os.arch";
      out.println("       <li>"+prop+" = <span class=\"val\">"+sysProps.getProperty(prop)+"</span></li>");
      prop = "os.name";
      out.println("       <li>"+prop+" = <span class=\"val\">"+sysProps.getProperty(prop)+"</span></li>");
      prop = "os.version";
      out.println("       <li>"+prop+" = <span class=\"val\">"+sysProps.getProperty(prop)+"</span></li>");

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

      out.println("<br /><br />");
      out.println("<h3>Client Request</h3>");
      out.println("  <ul>");
      out.println("    <li>Client/proxy Host = <span class=\"val\">"+req.getRemoteHost()+" ("+req.getRemoteAddr()+")</span></li>");
      out.println("    <li>Client/proxy port = <span class=\"val\">"+req.getRemotePort()+"</span></li>");
      out.println("    <li>Protocol = <span class=\"val\">"+req.getProtocol()+"</span></li>");
      out.println("  </ul>");

      out.println("<br /><br />");
      out.println("<h3>Client Parms Status</h3>");
      out.println("  <ul>");
      out.println("    <li>Browser = <span class=\"val\">"+req.getSession().getAttribute(CLIENT_SETTING_NAME_BROWSER_NAME)+" v"+
                  req.getSession().getAttribute(CLIENT_SETTING_NAME_BROWSER_VERSION)+"</span></li>");
      out.println("    <li>Device Type = <span class=\"val\">"+req.getSession().getAttribute(CLIENT_SETTING_NAME_DEVICE_TYPE)+"</span></li>");
      out.println("    <li>viewport.width = <span class=\"val\">"+req.getSession().getAttribute(CLIENT_SETTING_NAME_VIEWPORTWIDTH)+"</span></li>");
      out.println("    <li>viewport.height= <span class=\"val\">"+req.getSession().getAttribute(CLIENT_SETTING_NAME_VIEWPORTHEIGHT)+"</span></li>");
      out.println("  </ul>");
      out.println("<br /><br />");
      out.println("\n</body>\n</html>");
  }
}