/*
 * Relational_5.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.sql.ResultSet;
import java.util.Iterator;
import java.util.List;

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.resource.relational.Column;
import org.omg.cwm.resource.relational.Table;

import com.sap.exception.IBaseException;
import com.sap.ip.bi.sdk.dac.connector.IBIConnection;
import com.sap.ip.bi.sdk.dac.connector.IBIRelational;
import com.sap.ip.bi.sdk.dac.relational.query.IBICommandProcessor;
import com.sap.ip.bi.sdk.dac.relational.query.IBIQuery;
import com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLComparisonOperator;
import com.sap.ip.bi.sdk.dac.relational.query.sql.IBISQLLogicalOperator;
import com.sap.ip.bi.sdk.samples.servlet.MinimalServletContainer;

/**
 * More complex relational query -
 *
 * Demonstrates how to retrieve a result set by creating a more 
 * complex query with the following features:
 * 
 * 1. field selections
 * 2. joins
 * 3. sorting             
 *
 * View the HTML rendered by this servlet in the following file:
 * [SDK archive]/docs/examples/relational_5.result.html
 *
 * @author  SAP
 * @version 3.50
 * @since   3.50
 */
public class Relational_5 extends HttpServlet {
  private final static String CONTENT_TYPE = "text/html";

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

  public void doGet(
    HttpServletRequest request,
    HttpServletResponse response)
    throws ServletException, IOException {

    response.setContentType(CONTENT_TYPE);
    PrintWriter out = response.getWriter();

    out.println(Helpers.getDocTypeDefinition());
    out.println("<html>");
    out.println("<head><title>Relational_5</title>");
    out.println(Helpers.getStyleSheetDefinition());
    out.println("</head><body>");

    try {

      // ********************************************************
      // Connect to a data source.
      // ********************************************************
      IBIConnection connection =
        Helpers.connectToJDBCDatasource(out);
      IBIRelational rel = connection.getRelational();

      // create JDBC sample data where needed
      Helpers.createJDBCSampleData();

      // ********************************************************
      // Retrieve the metadata.
      // ********************************************************
      // find all tables starting with prefix
      // (could cause issues if large number of tables and prefix 
      // is empty or not restrictive enough)
      String prefix = Helpers.getJDBCTablePrefix();
      List tables =
        rel.getObjectFinder().findTable(
          (String) null,
          null,
          prefix + "%");
      // should contain at least the sample tables
      if (tables == null || tables.size() < 6) {
        throw new ServletException("metadata access failed");
      }

      Table carriers = null;
      Table planfli = null;
      Table actfli = null;
      for (Iterator i = tables.iterator(); i.hasNext();) {
        Table table = (Table) i.next();
        if (table != null
          && table.getName() != null
          && table.getName().equalsIgnoreCase(
            prefix + "CARRIERS")) {
          carriers = table;
        }
        if (table != null
          && table.getName() != null
          && table.getName().equalsIgnoreCase(
            prefix + "PLANFLI")) {
          planfli = table;
        }
        if (table != null
          && table.getName() != null
          && table.getName().equalsIgnoreCase(
            prefix + "ACTFLI")) {
          actfli = table;
        }
      }
      if (carriers == null
        || planfli == null
        || actfli == null) {
        throw new ServletException("sample data missing!");
      }
      // columns for CARRIERS
      List ccol = carriers.getFeature();
      Column cmandt = (Column) ccol.get(0);
      Column ccarrid = (Column) ccol.get(1);
      // columns for PLANFLI
      List pcol = planfli.getFeature();
      Column pmandt = (Column) pcol.get(0);
      Column pcarrid = (Column) pcol.get(1);
      Column pconnid = (Column) pcol.get(2);
      // columns for ACTFLI
      List acol = actfli.getFeature();
      Column amandt = (Column) acol.get(0);
      Column acarrid = (Column) acol.get(1);
      Column aconnid = (Column) acol.get(2);

      // ********************************************************
      // Create the query.
      // ********************************************************
      // create query instance
      IBIQuery query = rel.createQuery();
      // create command processor for this query instance
      IBICommandProcessor cp = query.getCommandProcessor();
      // Tables:
      // add CARRIER table
      String c = cp.addTable(carriers, "C");
      // add PLANFLI table
      String p = cp.addTable(planfli, "P");
      // add ACTFLI table
      String a = cp.addTable(actfli, "A");

      //Columns:
      cp.addColumn((Column) ccol.get(2), c, "Carrier Name");
      //CARRIERS.CARRNAME
      cp.addColumn((Column) pcol.get(2), p, "Flight");
      //PLANLFI.CONNID
      cp.addColumn((Column) pcol.get(3), p, "From");
      //PLANLFI.CITYFROM
      cp.addColumn((Column) pcol.get(5), p, "To");
      //PLANLFI.CITYTO
      cp.addColumn((Column) acol.get(3), a, "Date");
      //ACTLFI.FLDATE
      cp.addColumn((Column) pcol.get(8), p, "Departs");
      //PLANLFI.DEPTIME
      cp.addColumn((Column) acol.get(4), a, "Price");
      //ACTLFI.PRICE
      cp.addColumn((Column) acol.get(5), a, "Curr");
      //ACTLFI.CURRENCY

      // Restrictions:
      // <1>:= C.MANDT=P.MANDT AND C.CARRID=P.CARRID
      cp.pushWhereComparison(
        c,
        cmandt,
        IBISQLComparisonOperator.EQUALS,
        p,
        pmandt);
      cp.pushWhereComparison(
        c,
        ccarrid,
        IBISQLComparisonOperator.EQUALS,
        p,
        pcarrid);
      cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND);

      // <2>:= P.MANDT=A.MANDT AND P.CARRID=A.CARRID
      cp.pushWhereComparison(
        p,
        pmandt,
        IBISQLComparisonOperator.EQUALS,
        a,
        amandt);
      cp.pushWhereComparison(
        p,
        pcarrid,
        IBISQLComparisonOperator.EQUALS,
        a,
        acarrid);
      cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND);

      // <3>:= <1> AND <2>
      cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND);

      // <4>:= P.CONNID=A.CONNID
      cp.pushWhereComparison(
        p,
        pconnid,
        IBISQLComparisonOperator.EQUALS,
        a,
        aconnid);

      // <5>:= <3> AND <4>
      cp.pushWhereLogicalOperator(IBISQLLogicalOperator.AND);

      // Ordering:
      cp.addOrderBy((Column) ccol.get(2), c); //CARRIERS.CARRNAME
      cp.addOrderBy((Column) pcol.get(3), p); //PLANLFI.CITYFROM
      cp.addOrderBy((Column) pcol.get(5), p); //PLANLFI.CITYTO
      cp.addOrderBy((Column) acol.get(3), a); //ACTLFI.FLDATE
      cp.addOrderBy((Column) pcol.get(8), p); //PLANLFI.DEPTIME

      // ********************************************************
      // Execute the query.
      // ********************************************************
      ResultSet res = query.execute();

      // show the executed SQL statement
      out.println("<p><b>SQL Statement that was executed:</b><br>");
      out.println(
          "<code>"
            + rel.getNativeRepresentation(query)
            + "</code>");

      // ********************************************************
      // Render the result set.
      // ********************************************************
      Helpers.renderResultset(out, res);

    } 
    // 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());        
    }
    out.println("</body>");
    out.println("</html>");
  }

  public void destroy() {
  }

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

  }

}