/*
 * Tutorial_1.java
 *
 * Copyright 2004-2006 by SAP AG. All Rights Reserved.
 * SAP, R/3, mySAP, mySAP.com, xApps, xApp, SAP NetWeaver, and other SAP 
 * products and services mentioned herein as well as their respective logos 
 * are trademarks or registered trademarks of SAP AG in Germany and in several 
 * other countries all over the world. All other product and service names 
 * mentioned are the trademarks of their respective companies. Data contained 
 * in this document serves informational purposes only. National product 
 * specifications may vary.
 *
 * These materials are subject to change without notice. These materials are 
 * provided by SAP AG and its affiliated companies ("SAP Group") for 
 * informational purposes only, without representation or warranty of any kind, 
 * and SAP Group shall not be liable for errors or omissions with respect to 
 * the materials. The only warranties for SAP Group products and services are 
 * those that are set forth in the express warranty statements accompanying 
 * such products and services, if any. Nothing herein should be construed as 
 * constituting an additional warranty.
 */
package com.sap.ip.bi.sdk.samples;

import java.io.IOException;
import java.io.PrintWriter;
import java.util.HashMap;
import java.util.Locale;
import java.util.Properties;

import javax.resource.spi.ManagedConnectionFactory;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.omg.cwm.analysis.olap.Cube;
import org.omg.cwm.analysis.olap.Dimension;

import com.sap.exception.IBaseException;
import com.sap.ip.bi.sdk.dac.connector.IBIConnection;
import com.sap.ip.bi.sdk.dac.connector.IBIOlap;
import com.sap.ip.bi.sdk.dac.connector.olap.odbo.BIOdboMetadataConstants;
import com.sap.ip.bi.sdk.dac.connector.xmla.XmlaConnectionConstants;
import com.sap.ip.bi.sdk.dac.connector.xmla.XmlaManagedConnectionFactory;
import com.sap.ip.bi.sdk.dac.olap.query.IBICommandProcessor;
import com.sap.ip.bi.sdk.dac.olap.query.IBIMemberFactory;
import com.sap.ip.bi.sdk.dac.olap.query.main.IBIQuery;
import com.sap.ip.bi.sdk.dac.olap.query.member.IBIMember;
import com.sap.ip.bi.sdk.dac.result.IBIDataSet;
import com.sap.ip.bi.sdk.dac.result.model.BIDataSetTableModel;
import com.sap.ip.bi.sdk.dac.result.model.BITableItem;
import com.sap.ip.bi.sdk.exception.BIException;
import com.sap.ip.bi.sdk.exception.BISQLException;
import com.sap.ip.bi.sdk.localization.sdk.samples.Samples;
import com.sap.ip.bi.sdk.samples.servlet.MinimalServletContainer;
import com.sap.ip.bi.sdk.util.impl.BIResourceProperties;
import com.sapportals.connector.connection.IConnectionFactory;
import com.sapportals.connector.connection.IConnectionSpec;

/**
 * Getting Started -
 *
 * Demonstrates how to connect to a BW system using
 * the BI XMLA Connector, retrieve a cube ([0D_SD_C03/0D_SD_C03_Q009])
 * from the data source, create a query, execute it, and render the
 * result set into an HTML table.
 *
 * View the HTML rendered by this servlet in the following file:
 * [SDK archive]/docs/examples/tutorial_1.result.html
 *
 * @author SAP
 * @version 3.50
 * @since 3.50
 */
public class Tutorial_1 extends HttpServlet {
  private final static String CONTENT_TYPE = "text/html";

  // Load connection properties.
  private static Properties connProp =
    new BIResourceProperties(Helpers.class, ".xmla");

  public void init(ServletConfig config) throws ServletException {
    super.init(config);
  }

  // Processes the HTTP Get request.

  public void doGet(
    HttpServletRequest request,
    HttpServletResponse response)
    throws ServletException, IOException {
    response.setContentType(CONTENT_TYPE);

    PrintWriter out = response.getWriter();

    try {
      // ********************************************************
      // Connect to a BW system via the BI XMLA Connector.
      // ********************************************************
      ManagedConnectionFactory mcf;
      IConnectionFactory cf;
      IConnectionSpec cs;

      // Setting up the connectionDescriptor.
      // The URL of a BW XMLA provider can be determined with
      // transaction SE37.
      // Execute function module RSBB_URL_PREFIX_GET in
      // test mode.
      // Use the following input parameters for this:
      // HANDLERCLASS: CL_RSR_MDX_SOAP_HANDLER
      // PROTOCOL: HTTP, MESSAGESERVER = 
      mcf = new XmlaManagedConnectionFactory();
      cf = (IConnectionFactory) mcf.createConnectionFactory();
      cs = cf.getConnectionSpec();

      // Set the properties needed for the XMLA connection
      // See Helpers.xmla.properties (and optional
      // Helpers.xmla.local.properties) for details.

      // UserName
      cs.setPropertyValue(
        XmlaConnectionConstants.USERNAME.toString(),
        connProp.getProperty(XmlaConnectionConstants.USERNAME.toString()));
      // Password
      cs.setPropertyValue(
        XmlaConnectionConstants.PASSWORD.toString(),
        connProp.getProperty(XmlaConnectionConstants.PASSWORD.toString()));
      // URL
      cs.setPropertyValue(
        XmlaConnectionConstants.URL.toString(),
        connProp.getProperty(XmlaConnectionConstants.URL.toString()));
      // DataSource
      cs.setPropertyValue(
        XmlaConnectionConstants.DATA_SOURCE.toString(),
        connProp.getProperty(XmlaConnectionConstants.DATA_SOURCE.toString()));
      // Statefulness
      cs.setPropertyValue(
        XmlaConnectionConstants.STATEFULNESS.toString(),
        connProp.getProperty(XmlaConnectionConstants.STATEFULNESS.toString()));
      // Language
      cs.setPropertyValue(
        XmlaConnectionConstants.LANGUAGE.toString(),
        connProp.getProperty(XmlaConnectionConstants.LANGUAGE.toString()));

      // Establishing the connection.
      // The IBIOlap interface provides an entry point to access
      // metadata and execute queries.
      IBIConnection connection = (IBIConnection) cf.getConnectionEx(cs);
      IBIOlap olap = connection.getOlap();

      // ********************************************************
      // Retrieve metadata.
      // ********************************************************
      // For this example we will need the
      // "0D_SD_C03/0D_SD_C03_Q009" cube
      // and the two dimensions "0D_SALE_ORG" and "0D_DIV".
      // We retrieve the metadata using the ObjectFinder provided
      // by IBIOlap.
      Cube cube = olap.getObjectFinder().
        findCubeFirst((String)null, "0D_SD_C03/0D_SD_C03_Q009");
      
      // This is just a sanity check to verify that the
      // cube on which this example relies was retrieved  
      if (cube==null){
          throw new BIException(Locale.getDefault(),
                                Samples.SDK_SAMPLES_1000,
                                new Object[] {"0D_SD_C03/0D_SD_C03_Q009"});
      }
      
      Dimension salesOrgDimension = olap.getObjectFinder().
        findDimensionFirst(cube, "0D_SALE_ORG");
      Dimension divisionDimension = olap.getObjectFinder().
        findDimensionFirst(cube, "0D_DIV");

      // To filter the data by the "[0D_DIV].[7]" division, we
      // create the corresponding member.
      IBIMemberFactory queryFactory = olap.getQueryFactory().getMemberFactory();
      HashMap taggedValues = new HashMap();
      taggedValues.put(BIOdboMetadataConstants.MEMBER_UNIQUE_NAME, 
        "[0D_DIV].[7]");
      IBIMember divisionHiTechMember = queryFactory.
        createMember(divisionDimension, "7", null, taggedValues);

      // ********************************************************
      // Create a query and a command processor associated with
      // this query. In its initial state, the query will have
      // the measures dimension of the associated cube drilled
      // down on the columns axis and all other dimensions on
      // the slicer axis, and will select all members of their
      // respective dimensions.
      // ********************************************************
      IBIQuery query = olap.createQuery(cube);
      IBICommandProcessor commandProcessor = query.getCommandProcessor();

      // ********************************************************
      // Change the layout.
      // ********************************************************
      // Move the "0D_SALE_ORG" dimension to rows.
      commandProcessor.moveDimensionToRows(salesOrgDimension);

      // ********************************************************
      // Specify selected members.
      // ********************************************************
      // Add HiTech to the member set of the Division dimension.
      commandProcessor.addMember(divisionHiTechMember);

      // ********************************************************
      // Execute the query and retrieve the result set.
      // ********************************************************
      IBIDataSet dataset = query.execute();

      // ********************************************************
      // Render the result set.
      // We use BIDataSetTableModel for rendering to a table.
      // This provides a projection of the data set into a
      // tabular view where cells can be accessed by row and
      // column coordinates.
      // We also rely on Helpers.java for HTML doctype and
      // stylesheet information to help format the output.
      // ********************************************************
      out.println(Helpers.getDocTypeDefinition());
      out.println("<html>");
      out.println("<head><title>Tutorial_1</title>");
      out.println(Helpers.getStyleSheetDefinition());
      out.println("</head><body>");

      // Display the MDX that has been executed.
      out.println(
        "<p><b>MDX Statement that was executed:</b><br> "
          + "<span class=\"code\">"
          + (String) query.getNativeRepresentation()
          + "</span>"
          + "</p>");

      try {
        BIDataSetTableModel table =
          new BIDataSetTableModel(dataset, false);
        out.println("<p>Result set:</p>");
        out.println("<table width=700 border=1 cellpadding=0 cellspacing=0>");

        int row = table.getRowCount();
        int col = table.getColumnCount();

        for (int i = 0; i < row; i++) {
          out.println("<tr>");

          for (int j = 0; j < col; j++) {
            BITableItem item =
              (BITableItem) table.getValueAt(i, j);
            out.println("<td class=\"headCenter\">");
            out.println(Helpers.escape(item.toString()));
            out.println("</td>");
          }

          out.println("</tr>");
        }

        out.println("</table>");
      } catch (BISQLException e) {
        e.printStackTrace();
        out.println("<p>Error: " + e.getMessage() + "</p>");
      }

      out.println("</body>");
      out.println("</html>");
    }
    // Catch errors.
    catch (Exception e) {
      // $JL-EXC$
        e.printStackTrace();
      if (e instanceof IBaseException)
        out.println("Error: " +
                ((IBaseException)e).getNestedLocalizedMessage());
      else  
        out.println("Error: " + e.getMessage());        
    }
  }

  public void destroy() {
  }

  public static void main(String[] args) {
    if (args.length == 1) {
      MinimalServletContainer.executeServlet(new Tutorial_1(), args[0]);
    } else {
      MinimalServletContainer.executeServlet(new Tutorial_1(), System.out);
    }
  }

}