/*
 * GraphUIXSample.java
 *
 * Copyright © 1994-2004, Oracle. All rights reserved. Oracle grants
 * you ("Licensee") a non-exclusive, royalty-free, worldwide license to use,
 * modify and redistribute this software in source and binary code form, provided
 * that (i) this copyright notice and license appear on all copies of the
 * software; (ii) Licensee does not utilize the software in a manner disparaging
 * to Oracle; and (iii) Licensee does not utilize the software in a manner that
 * violates the terms of any Licensee agreement with Oracle.
 */
package samples.uix;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import oracle.dss.thin.beans.dataView.Presentation;
import oracle.jdbc.OracleConnection;

import java.util.ArrayList;
import java.util.Hashtable;

import oracle.cabo.servlet.BajaContext;
import oracle.cabo.servlet.Page;

import oracle.dss.dataView.Dataview;

import oracle.dss.addins.uix.BIHandler;

import oracle.dss.thin.beans.graph.ThinGraph;


public class GraphUIXSample 
{
  // A flag to keep track of whether the data has been loaded into a Graph
  public static final String DATA_LOADED_FLAG = "DATA_LOADED";
  
  /**
   * Retrieves the Graph from the UIX page and initializes it with data
   * This method is called in the UIX page every time the page is rendered
   */
  public static void preRender (BajaContext context, Page page) throws Throwable 
  {
    // Check if the data has already been loaded into the Graph
    Object isInited = context.getServletRequest().getSession(true).getAttribute(DATA_LOADED_FLAG);

    if (isInited == null)
    {
      Hashtable pageObjects = BIHandler.initBIObjects (context, page);

      // Retrieve the Graph from the page using its ID as defined in the graphSample.uix
      Presentation presentation = (Presentation)pageObjects.get("graph1");
      ThinGraph graph = (ThinGraph)presentation.getView();

      // Below are two methods for loading the data into the Graph
      // set the following variable to true or false depending which method
      // you want to use for loading the data
      boolean loadDataFromJDBC = false;
      
      if (loadDataFromJDBC)
      {
        // The following method loads the data into the Graph from the 
        // relational database connection
        loadDataFromJDBC(graph);
      }
      else
      {
        // The following method loads the data into the Graph from the file called localData.xml
        // The file should be in the same directory as this class
        loadDataFromXML(graph, "localData.xml");
      }

      // Put the flag on the session to indicate that the data has been loaded
      context.getServletRequest().getSession(true).setAttribute(DATA_LOADED_FLAG, Boolean.TRUE);
    }
  }  


  // The following method loads the data into the Graph from the 
  // relational database connection
  public static void loadDataFromJDBC(ThinGraph graph)
  {
      try
      {
        // The folowing is a SQL query to retrieve the data against the BIBDEMO 
        // database. Change the query to work against your database instance
        String sqlQuery = "select SHAWPROMOTIONS_ET, SHAWCHANNELS_ET, AMOUNT_SOLD from cs_o_shaw_shawsale14view " +
                     " where shawgeographies_et='52790'" + 
                     " and shawproducts_et='201'" +
                     " and shawt_time_et='1769'" +
                     " and shawpromotions_et='1'";

        // Change the HOST_NAME, PORT_NUMBER, SID, USER_NAME, PASSWORD instance
        // to work for your database connection
        // Change the following connection string to work against your 
        // relational database instance
        String HOST_NAME    = "localhost";
        String PORT_NUMBER  = "1521"; 
        String SID          = "ora9205"; 
        String USER_NAME    = "cs_olap"; 
        String PASSWORD     = "cs_olap"; 

        String connStr =  "jdbc:oracle:thin:@" +
                          "(DESCRIPTION=(ADDRESS=(HOST=" + HOST_NAME + ")"+
                          "(PROTOCOL=tcp)"+
                          "(PORT=" + PORT_NUMBER + "))"+
                          "(CONNECT_DATA=(SID=" + SID + ")))";

        // Register the Oracle driver, connect, create a statement and execute the SQL query
        DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        OracleConnection connection = (OracleConnection)DriverManager.getConnection(connStr, USER_NAME, PASSWORD);
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery(sqlQuery);

        ArrayList data = new ArrayList();

        // Walk the ResultSet and prepare the data for the Graph
        while (resultSet.next()) {
          // for each element in the result set
          // create a 3-element Object array
          Object[] rowItems = new Object[3];
          // first element is the column label in the grid
          rowItems[0] = "Promo" + resultSet.getString("SHAWPROMOTIONS_ET");
          // second element is the row label in the grid
          rowItems[1] = "Channel" + resultSet.getString("SHAWCHANNELS_ET");
          // third element is the data
          rowItems[2] = new Double(resultSet.getDouble("AMOUNT_SOLD"));
          // add this array to the ArrayList
          data.add(rowItems);
        } // while going through ResultSet
  
        resultSet.close();
        statement.close();
        connection.close(); 

        // Set the data on the Graph
        graph.setTabularData(data);
      }
      catch (Exception e)
      {
        e.printStackTrace();
      }
   } 

  // The following method loads the data into the Graph from the specified file name
  // The file should be in the same directory as this class  
  public static void loadDataFromXML(ThinGraph graph, String filename)
  {
    try
    {
      graph.readXML(GraphUIXSample.class.getResourceAsStream(filename), Dataview.RESET_NONE);
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }
}
