/*
 * Helpers.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.PrintWriter;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;

import javax.resource.spi.ManagedConnectionFactory;

import org.omg.cwm.analysis.olap.Cube;
import org.omg.cwm.analysis.olap.CubeDimensionAssociation;
import org.omg.cwm.analysis.olap.Dimension;
import org.omg.cwm.objectmodel.core.Attribute;

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.olap.query.IBIMemberFactory;
import com.sap.ip.bi.sdk.dac.olap.query.impl.factory.BIQueryFactory;
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.IBIDimensionCursor;
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.BIOlapQueryException;
import com.sap.ip.bi.sdk.exception.BIResourceException;
import com.sap.ip.bi.sdk.exception.BISQLException;
import com.sap.ip.bi.sdk.localization.sdk.Sdk;
import com.sap.ip.bi.sdk.localization.sdk.samples.Samples;
import com.sap.ip.bi.sdk.util.impl.BIResourceProperties;
import com.sapportals.connector.connection.IConnectionFactory;
import com.sapportals.connector.connection.IConnectionSpec;



/**
 * Helpers - 
 * 
 * Provides static helper methods for the BI Java SDK
 * examples that facilitate connecting to data sources and 
 * rendering result sets.
 *
 * @author SAP
 * @version 3.50 SP2
 * @since 3.50
 */
public class Helpers {

  // Creates HTML doctype.
  private static final String DOCTYPE =
    "<!DOCTYPE HTML PUBLIC \"-//W3C//DTD HTML 4.01 Transitional//EN\">";
  // Stylesheet for HTML files.
  private static final String CSS =
    "<style type=\"text/css\">"
      + "\n"
      + "<!--"
      + "\n"
      + "h3 {"
      + " font: 14pt arial, helvetica, sans-serif;"
      + " font-weight: bold;"
      + " color: #003366"
      + "}"
      + "\n"
      + "h4 {"
      + " font: 12pt arial, helvetica, sans-serif;"
      + " font-weight: bold;"
      + " color: #003366"
      + "}"
      + "\n"
      + "hr {"
      + " color: #003366;"
      + " size: 1pt;"
      + " align: left;"
      + "}"
      + "\n"
      + "body {"
      + " font: 10pt arial, helvetica, sans-serif;"
      + " background-color:#ffffff;"
      + " margin:20px"
      + "}"
      + "\n"
      + "table {"
      + " border: 1px solid #909cae;"
      + " border-collapse: collapse"
      + "}"
      + "\n"
      + "td.headCenter {"
      + " border: 1px solid #909cae;"
      + " font: 9pt arial, helvetica, sans-serif;"
      + " background-color:#c4d9e9;"
      + " margin:20px;"
      + " text-align:center"
      + "}"
      + "\n"
      + "td.headLeft {"
      + " border: 1px solid #909cae;"
      + " font: 9pt arial, helvetica, sans-serif;"
      + " background-color:#c4d9e9;"
      + " margin:20px;"
      + " text-align:left"
      + "}"
      + "\n"
      + "td.odd {"
      + " border: 1px solid #909cae;"
      + " font: 9pt arial, helvetica, sans-serif;"
      + " background-color:#d4e2ee;"
      + " margin:20px;"
      + " text-align:center"
      + "}"
      + "\n"
      + "td.even {"
      + " border: 1px solid #909cae;"
      + " font: 9pt arial, helvetica, sans-serif;"
      + " background-color:#eff6fb;"
      + " margin:20px;"
      + " text-align:center"
      + "}"
      + "\n"
      + "td.single {"
      + " border: 1px solid #909cae;"
      + " font: 9pt arial, helvetica, sans-serif;"
      + " background-color:#eff6fb;"
      + " margin:20px;"
      + " text-align:left"
      + "}"
      + "\n"
      + ".code {"
      + " font-family: Courier, mono"
      + "}"
      + "\n"
      + "-->"
      + "</style>";

  // Suppress default constructor for non-instantiability
  private Helpers() {
    // This constructor will never be invoked
  }

  // Creates connection to an XMLA data source.
  public static IBIConnection connectToXMLADatasource(PrintWriter out)
    throws BIResourceException {
    ManagedConnectionFactory mcf;
    IConnectionFactory cf = null;
    IConnectionSpec cs = null;
    IBIConnection connection = null;

    try {
      mcf = (ManagedConnectionFactory) Class.forName(
        "com.sap.ip.bi.sdk.dac.connector.xmla.XmlaManagedConnectionFactory")
          .newInstance();
      cf = (IConnectionFactory) mcf.createConnectionFactory();
      cs = cf.getConnectionSpec();
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1210, e);
    }

    BIResourceProperties props =
      new BIResourceProperties(Helpers.class, ".xmla");

    // Collects information about the properties used to connect
    // to the data source.
    Enumeration propnames = props.propertyNames();

    while (propnames.hasMoreElements()) {
      String key = (String) propnames.nextElement();

      //out.println("<p>key:" + key + " prop:" + props.getProperty(key) + "</p>");
      cs.setPropertyValue(key, props.getProperty(key));
    }

    try {
      // Establishes the connection.
      connection = (IBIConnection) cf.getConnectionEx(cs);
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1100, e);
    }

    return connection;
  }


  public static SampleMetaDataBW getSampleMetaDataBW(IBIConnection connection) throws Exception {
    return new SampleMetaDataBW(connection);
  }

  // Creates connection to an ODBO data source.
  public static IBIConnection connectToODBODatasource(PrintWriter out)
    throws BIResourceException {
    ManagedConnectionFactory mcf;
    IConnectionFactory cf = null;
    IConnectionSpec cs = null;
    IBIConnection connection = null;

    try {
      mcf = (ManagedConnectionFactory) Class.forName(
        "com.sap.ip.bi.sdk.dac.connector.odbo.OdboManagedConnectionFactory")
          .newInstance();
      cf = (IConnectionFactory) mcf.createConnectionFactory();
      cs = cf.getConnectionSpec();
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1230, e);
    }

    BIResourceProperties props =
      new BIResourceProperties(Helpers.class, ".odbo");

    // Collects information about the properties used to connect
    // to the data source.
    Enumeration propnames = props.propertyNames();

    while (propnames.hasMoreElements()) {
      String key = (String) propnames.nextElement();
      cs.setPropertyValue(key, props.getProperty(key));
    }

    try {
      // Establishes the connection.
      connection = (IBIConnection) cf.getConnectionEx(cs);
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1110, e);
    }

    return connection;
  }

  // Creates connection to a JDBC data source.
  public static IBIConnection connectToJDBCDatasource(PrintWriter out)
    throws BIResourceException {
    ManagedConnectionFactory mcf;
    IConnectionFactory cf = null;
    IConnectionSpec cs = null;
    IBIConnection connection = null;

    try {
      mcf = (ManagedConnectionFactory) Class.forName(
        "com.sap.ip.bi.sdk.dac.connector.jdbc.JdbcManagedConnectionFactory")
          .newInstance();
      cf = (IConnectionFactory) mcf.createConnectionFactory();
      cs = cf.getConnectionSpec();
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1200, e);
    }

    BIResourceProperties props = 
      new BIResourceProperties(Helpers.class, ".jdbc");

    // Collects information about the properties used to connect
    // to the data source.
    Enumeration propnames = props.propertyNames();

    while (propnames.hasMoreElements()) {
      String key = (String) propnames.nextElement();
      cs.setPropertyValue(key, props.getProperty(key));
    }

    try {
      // Establishes the connection.
      connection = (IBIConnection) cf.getConnectionEx(cs);
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1120, e);
    }

    return connection;
  }

  public static void createJDBCSampleData()
    throws BIResourceException {
    try {
      com.sap.ip.bi.sdk.samples.jdbc.Samples.setup();
    } catch (SQLException ex) {
      throw new BIResourceException(Locale.getDefault(), 
        Sdk.SDK_0000, new Object[] { ex.getMessage()});
    }
  }

  public static String getJDBCTablePrefix() {
    return com.sap.ip.bi.sdk.samples.jdbc.Samples.getJDBCTablePrefix();
  }

  // Creates connection to a SAP Query data source.
  public static IBIConnection connectToSAPQDatasource(PrintWriter out)
    throws BIResourceException {
    ManagedConnectionFactory mcf;
    IConnectionFactory cf = null;
    IConnectionSpec cs = null;
    IBIConnection connection = null;

    try {
      mcf =
        (ManagedConnectionFactory) Class
          .forName("com.sap.ip.bi.sdk.dac.connector.sapq.SapqManagedConnectionFactory")
          .newInstance();
      cf = (IConnectionFactory) mcf.createConnectionFactory();
      cs = cf.getConnectionSpec();
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1220, e);
    }

    BIResourceProperties props =
      new BIResourceProperties(Helpers.class, ".sapq");

    // Collects information about the properties used to connect
    // to the data source.
    Enumeration propnames = props.propertyNames();

    while (propnames.hasMoreElements()) {
      String key = (String) propnames.nextElement();
      cs.setPropertyValue(key, props.getProperty(key));
    }

    try {
      // Establishes the connection.
      connection = (IBIConnection) cf.getConnectionEx(cs);
    } catch (Exception e) {
      throw new BIResourceException(Locale.getDefault(), 
        Samples.SDK_SAMPLES_1130, e);
    }

    return connection;
  }

  public static String getDocTypeDefinition() {
    return DOCTYPE;
  }

  public static String getStyleSheetDefinition() {
    return CSS;
  }

  public static void renderQueryAndDataset(
    PrintWriter out,
    IBIQuery query) {

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

    IBIDataSet dataset = query.execute();
    renderDataset(out, dataset, true);
  }

  // Facilitates display of any data set as an HTML table using
  // BIDataSetTableModel, supporting crossjoins and dimension
  // properties on columns and rows, and hierarchical
  // display.
  public static void renderDataset(
    PrintWriter out,
    IBIDataSet dataset,
    boolean isDisplayingHierarchy) {

    try {
      BIDataSetTableModel table =
        new BIDataSetTableModel(dataset, isDisplayingHierarchy);
      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();
      int evenOdd = -1;

      for (int i = 0; i < row; i++) {
        if (evenOdd > -1) {
          evenOdd++;
        }
        out.println("<tr>");

        for (int j = 0; j < col; j++) {
          BITableItem item = (BITableItem) table.getValueAt(i, j);
          String cellText = item.toString();

          switch (item.getSection()) {
            // cells
            case BIDataSetTableModel.SECTION_CELLS :
              if (evenOdd == -1) {
                evenOdd = 0;
              }
              out.println(renderTd(
                cellText, (evenOdd & 1) == 0 ? "even" : "odd"));
              break;

              // member on row axis but not member property
            case BIDataSetTableModel.SECTION_ROW_HEADERS :
              if (isDisplayingHierarchy
                && (item.getItem() instanceof IBIDimensionCursor)) {
                boolean atLeastOneLevel = false;
                StringBuffer dashes = new StringBuffer();
                for (int k = 0; k < item.getRelativeLevel(); k++) {
                  dashes.append("--");
                  atLeastOneLevel = true;
                }
                if (atLeastOneLevel == true) {
                  dashes.append("&nbsp;");
                }
                cellText = dashes.toString() + cellText;
              }
              out.println(renderTd(cellText, "headLeft"));
              break;

              // member on column axis but not member 
              // property
            case BIDataSetTableModel.SECTION_COLUMN_HEADERS :
              if (isDisplayingHierarchy
                && (item.getItem() instanceof IBIDimensionCursor)) {
                // captions for members of a hierarchy 
                // on the columns axis are printed
                // only for the first occurrence of the 
                // caption in the upper-left corner.
                // all other cells are cleared
                // 1999
                //       Q1   Q2   Q3   Q4
                // the condition is that the text is 
                // only displayed if the row of the
                // output grid equals the level of the 
                // member, otherwise it is suppressed.
                // as the minimum displayed level may 
                // be different from 0, we have to 
                // adjust for the actual minimum 
                // level; that's why we compare for 
                // the relative level.
                if (item.getRelativeLevel()
                  != (i - item.getAccumulatedHeaderPositionCount())) {
                  cellText = "";
                }
              }
              out.println(renderTd(cellText, "headCenter"));
              break;

              // captions
            case BIDataSetTableModel.SECTION_LABELS :
              out.println(renderTd(cellText, "headCenter"));
              break;

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

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

  // Facilitates display of a result set as an HTML table.
  public static void renderResultset(
    PrintWriter out,
    ResultSet resultset) {

    try {
      ResultSetMetaData rsmd = resultset.getMetaData();

      out.println("<p>Result set:</p>");
      out.println(
        "<table width=700 border=1 cellpadding=0 cellspacing=0>");

      //Now output Table column header
      out.println("<tr>");
      int cols = rsmd.getColumnCount();
      for (int i = 1; i <= cols; i++) {
        out.println(renderTd(rsmd.getColumnLabel(i), "headCenter"));
      }
      out.println("</tr>");

      //Now output Table rows
      int j = -1;
      while (resultset.next()) {
        j++;
        out.println("<tr>");

        for (int i = 1; i <= cols; i++) {
          Object o = resultset.getObject(i);
          String content = null;

          if (resultset.wasNull()) {
            content = "{null}";
          } else {
            content = o.toString();
          }
          out.println(renderTd(content, (j & 1) == 0 ? "even" : "odd"));
        }

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

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

  public static String renderTd(String data, String clazz) {
    return "<td"
      + (clazz != null ? " class=\"" + clazz + "\"" : "")
      + ">"
      + escape(data)
      + "</td>";
  }

  public static String escape(String data) {

    final String[][] map = { { "£", "&#163;" }
    };

    String content = data;
    if (content == null || (content != null && content.equals(""))) {
      content = "&nbsp;";
    } else {
      for (int i = 0; i < map.length; i++) {
        String[] pair = map[i];
        if (content.indexOf(pair[0].charAt(0)) >= 0) {
          int position = content.indexOf(pair[0].charAt(0));
          while (position >= 0) {
            content =
              new StringBuffer(content)
                .replace(position, position + 1, pair[1])
                .toString();
            position = content.indexOf(pair[0].charAt(0));
          }
        }
      }
    }
    return content;
  }

  public static IBIMember createMember(IBIMemberFactory memberFactory, 
         Dimension dimension, String memberName, String memberUniqueName) 
         throws BIOlapQueryException {

    HashMap taggedValues = new HashMap();
    taggedValues.put(
      BIOdboMetadataConstants.MEMBER_NAME, memberName);
    taggedValues.put(
      BIOdboMetadataConstants.MEMBER_UNIQUE_NAME, memberUniqueName);
    IBIMember member = memberFactory.
      createMember(dimension, memberName, null, taggedValues);
    return member;
  }


  public static class SampleMetaDataBW {
    public Cube cube;
    public Dimension soldToPartyDimension;
    public Dimension salesOrgDimension;
    public Dimension divisionDimension;
    public Dimension measuresDimension;
    public Dimension distributionChannelDimension;
    public Dimension versionDimension;
    public Dimension valueTypeDimension;
    public IBIMember divisionHiTechMember;
    public IBIMember divisionElectronicParts;
    public IBIMember divisionServiceMember;
    public IBIMember measuresQuantityMember;
    public IBIMember measuresCostMember;
    public IBIMember measuresIncomingOrderValue;
    public IBIMember distributionChannelAll;
    public IBIMember distributionChannelDirectSales;
    public IBIMember valueTypeActualMember;
    public IBIMember versionCurrentMember;
    public Attribute countryAttribute;
    public Attribute industryAttribute;

    public Cube cube2;
    public Dimension salesOrgDimension2;
    public IBIMember salesOrgNewYork2;
    public IBIMember salesOrgSanFrancisco2;

    private SampleMetaDataBW(IBIConnection connection) throws Exception {

      IBIOlap olap = connection.getOlap();
      cube = olap.getObjectFinder().findCubeFirst(
        (String) null, "$0D_SD_C03");
      cube2 = 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"});
      } else if (cube2 == null) {
          throw new BIException(Locale.getDefault(),
                                Samples.SDK_SAMPLES_1000,
                                new Object[] {"0D_SD_C03/0D_SD_C03_Q009"});
      }

      // initialize metadata for cube $0D_SD_C03
      for (Iterator i = cube.getCubeDimensionAssociation().iterator();
           i.hasNext(); ) {
        CubeDimensionAssociation cda = (CubeDimensionAssociation) i.next();
        Dimension dim = cda.getDimension();

        if        (dim.getName().equalsIgnoreCase("0D_DIV")) {
          this.divisionDimension = dim;
        } else if (dim.getName().equalsIgnoreCase("0D_SALE_ORG")) {
          this.salesOrgDimension = dim;
        } else if (dim.getName().equalsIgnoreCase("0D_SOLD_TO")) {
          this.soldToPartyDimension = dim;
        } else if (dim.getName().equalsIgnoreCase("0D_DIS_CHAN")) {
          this.distributionChannelDimension = dim;
        } else if (dim.getName().equalsIgnoreCase("Measures")) {
          this.measuresDimension = dim;
        } else if (dim.getName().equalsIgnoreCase("0D_VTYPE")) {
          this.valueTypeDimension = dim;
        } else if (dim.getName().equalsIgnoreCase("0D_VERSION")) {
          this.versionDimension = dim;
        }
      }

      // to filter the data by a specific division,
      // we create a member
      BIQueryFactory queryFactory =
        (BIQueryFactory) olap.getQueryFactory();
      divisionHiTechMember = createMember(queryFactory,
        divisionDimension, "High Tech", "[0D_DIV].[7]");
      divisionElectronicParts = createMember(queryFactory,
        divisionDimension, "Electronic Parts", "[0D_DIV].[15]");
      divisionServiceMember = createMember(queryFactory,
        divisionDimension, "Service", "[0D_DIV].[8]");

      valueTypeActualMember = createMember(queryFactory,
        valueTypeDimension, "Actual", "[0D_VTYPE].[010]");
      versionCurrentMember = createMember(queryFactory,
        versionDimension, "Current Version", "[0D_VERSION].[000]");

      distributionChannelAll = createMember(queryFactory,
        distributionChannelDimension, "All Distribution channel",
          "[0D_DIS_CHAN].[All]");
      distributionChannelDirectSales = createMember(queryFactory,
        distributionChannelDimension, "Direct Sales", "[0D_DIS_CHAN].[1]");

      measuresQuantityMember = createMember(queryFactory,
        measuresDimension, "Quantity in base untis (SAP DEMO)",
          "[Measures].[0D_QUANT_B]");
      measuresCostMember = createMember(queryFactory,
        measuresDimension, "Cost in statistics currency (SAP DEMO)",
          "[Measures].[0D_COSTVALS]");

      // retrieve dimension attributes
      List plantFeatures = this.salesOrgDimension.getFeature();
      Attribute attribute;

      for (int j = 0; j < plantFeatures.size(); j++) {
        attribute = (Attribute) plantFeatures.get(j);

        if (attribute.getName().equalsIgnoreCase("[10D_COUNTRY]")) {
          countryAttribute = attribute;
        }
      }

      // initialize metadata for cube 0D_SD_C03/0D_SD_C03_Q009
      for (Iterator i = cube.getCubeDimensionAssociation().iterator();
           i.hasNext(); ) {
        CubeDimensionAssociation cubeDimensionAssociation =
          (CubeDimensionAssociation) i.next();
        Dimension dimension = cubeDimensionAssociation.getDimension();

        if (dimension.getName().equalsIgnoreCase("0D_SALE_ORG")) {
          this.salesOrgDimension2 = dimension;
        }
      }

      salesOrgNewYork2 = createMember(queryFactory,
        salesOrgDimension2, "New York", "[0D_SALE_ORG].[1612]");
      salesOrgSanFrancisco2 = createMember(queryFactory,
        salesOrgDimension2, "San Francisco", "[0D_SALE_ORG].[1614]");
    }
  }
}