/**
   * Function to return list of eventset for given encounter number list
   *
   * @param detailFlag
   * @param blobFlag
   * @param statusFlag
   * @exception I2B2DAOException
   */
  public EventSet getVisitsByEncounterNum(
      List<String> encounterNumList, boolean detailFlag, boolean blobFlag, boolean statusFlag)
      throws I2B2DAOException {
    EventSet visitDimensionSet = new EventSet();
    log.debug("visit list size " + encounterNumList.size());
    Connection conn = null;
    PreparedStatement query = null;
    String tempTableName = "";
    try {
      conn = getDataSource().getConnection();
      VisitFactRelated visitRelated =
          new VisitFactRelated(buildOutputOptionType(detailFlag, blobFlag, statusFlag));
      visitRelated.setMetaDataParamList(this.metaDataParamList);
      String selectClause = visitRelated.getSelectClause();
      String serverType = dataSourceLookup.getServerType();
      if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
        oracle.jdbc.driver.OracleConnection conn1 =
            (oracle.jdbc.driver.OracleConnection)
                ((WrappedConnection) conn).getUnderlyingConnection();
        String finalSql =
            "SELECT "
                + selectClause
                + " FROM "
                + getDbSchemaName()
                + "visit_dimension visit WHERE visit.encounter_num IN (SELECT * FROM TABLE (cast (? as QT_PDO_QRY_STRING_ARRAY)))";
        log.debug("Executing sql[" + finalSql + "]");
        query = conn1.prepareStatement(finalSql);

        ArrayDescriptor desc = ArrayDescriptor.createDescriptor("QT_PDO_QRY_STRING_ARRAY", conn1);
        oracle.sql.ARRAY paramArray =
            new oracle.sql.ARRAY(desc, conn1, encounterNumList.toArray(new String[] {}));
        query.setArray(1, paramArray);
      } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
        log.debug("creating temp table");
        tempTableName =
            this.getDbSchemaName() + SQLServerFactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE;
        java.sql.Statement tempStmt = conn.createStatement();

        try {
          tempStmt.executeUpdate("drop table " + tempTableName);
        } catch (SQLException sqlex) {;
        }

        uploadTempTable(tempStmt, tempTableName, encounterNumList);
        String finalSql =
            "SELECT "
                + selectClause
                + " FROM "
                + getDbSchemaName()
                + "visit_dimension visit WHERE visit.encounter_num IN (select distinct char_param1 FROM "
                + tempTableName
                + ") order by encounter_num";
        log.debug("Executing [" + finalSql + "]");

        query = conn.prepareStatement(finalSql);

      } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
        log.debug("creating temp table");
        tempTableName = this.getDbSchemaName() + FactRelatedQueryHandler.TEMP_PDO_INPUTLIST_TABLE;
        java.sql.Statement tempStmt = conn.createStatement();

        try {
          tempStmt.executeUpdate("drop table if exists " + tempTableName);
        } catch (SQLException sqlex) {;
        }

        uploadTempTable(tempStmt, tempTableName, encounterNumList);
        String finalSql =
            "SELECT "
                + selectClause
                + " FROM "
                + getDbSchemaName()
                + "visit_dimension visit WHERE visit.encounter_num IN (select distinct char_param1 FROM "
                + tempTableName
                + ") order by encounter_num";
        log.debug("Executing [" + finalSql + "]");

        query = conn.prepareStatement(finalSql);
      }
      ResultSet resultSet = query.executeQuery();
      I2B2PdoFactory.EventBuilder eventBuilder =
          new I2B2PdoFactory().new EventBuilder(detailFlag, blobFlag, statusFlag);
      while (resultSet.next()) {
        EventType visitDimensionType =
            eventBuilder.buildEventSet(resultSet, this.metaDataParamList);
        visitDimensionSet.getEvent().add(visitDimensionType);
      }

    } catch (SQLException sqlEx) {
      log.error("", sqlEx);
      throw new I2B2DAOException("sql exception", sqlEx);
    } catch (IOException ioex) {
      log.error("", ioex);
      throw new I2B2DAOException("io exception", ioex);
    } finally {
      if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
        PdoTempTableUtil tempUtil = new PdoTempTableUtil();
        tempUtil.deleteTempTableSqlServer(conn, tempTableName);
      } else if (dataSourceLookup.getServerType().equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
        PdoTempTableUtil tempUtil = new PdoTempTableUtil();
        tempUtil.deleteTempTablePostgres(conn, tempTableName);
      }
      try {
        JDBCUtil.closeJdbcResource(null, query, conn);
      } catch (SQLException sqlEx) {
        sqlEx.printStackTrace();
      }
    }
    return visitDimensionSet;
  }
  public EventSet getVisitByFact(
      List<String> panelSqlList,
      List<Integer> sqlParamCountList,
      IInputOptionListHandler inputOptionListHandler,
      boolean detailFlag,
      boolean blobFlag,
      boolean statusFlag)
      throws I2B2DAOException {

    EventSet eventSet = new EventSet();
    I2B2PdoFactory.EventBuilder eventBuilder =
        new I2B2PdoFactory().new EventBuilder(detailFlag, blobFlag, statusFlag);
    VisitFactRelated eventFactRelated =
        new VisitFactRelated(buildOutputOptionType(detailFlag, blobFlag, statusFlag));
    eventFactRelated.setMetaDataParamList(this.metaDataParamList);

    String selectClause = eventFactRelated.getSelectClause();
    String serverType = dataSourceLookup.getServerType();
    String factTempTable = "";
    Connection conn = null;
    PreparedStatement query = null;
    try {
      conn = dataSource.getConnection();
      if (serverType.equalsIgnoreCase(DAOFactoryHelper.ORACLE)) {
        factTempTable = getDbSchemaName() + FactRelatedQueryHandler.TEMP_FACT_PARAM_TABLE;
      } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.SQLSERVER)) {
        log.debug("creating temp table");
        java.sql.Statement tempStmt = conn.createStatement();
        factTempTable = getDbSchemaName() + SQLServerFactRelatedQueryHandler.TEMP_FACT_PARAM_TABLE;
        try {
          tempStmt.executeUpdate("drop table " + factTempTable);
        } catch (SQLException sqlex) {;
        }
        String createTempInputListTable =
            "create table " + factTempTable + " ( set_index int, char_param1 varchar(500) )";
        tempStmt.executeUpdate(createTempInputListTable);
        log.debug("created temp table" + factTempTable);
      } else if (serverType.equalsIgnoreCase(DAOFactoryHelper.POSTGRES)) {
        log.debug("creating temp table");
        java.sql.Statement tempStmt = conn.createStatement();
        factTempTable = getDbSchemaName() + FactRelatedQueryHandler.TEMP_FACT_PARAM_TABLE;

        try {
          tempStmt.executeUpdate("drop table if exists " + factTempTable);
        } catch (SQLException sqlex) {;
        }

        String createTempInputListTable =
            "create temporary table "
                + factTempTable
                + " ( set_index int, char_param1 varchar(500) )";
        tempStmt.executeUpdate(createTempInputListTable);
        log.debug("created temp table" + factTempTable);
      }
      // if the inputlist is enumeration, then upload the enumerated input
      // to temp table.
      // the uploaded enumerated input will be used in the fact join.
      if (inputOptionListHandler.isEnumerationSet()) {
        inputOptionListHandler.uploadEnumerationValueToTempTable(conn);
      }
      String insertSql = "";
      int i = 0;
      int sqlParamCount = 0;
      ResultSet resultSet = null;
      for (String panelSql : panelSqlList) {
        insertSql =
            " insert into "
                + factTempTable
                + "(char_param1) select distinct obs_encounter_num from ( "
                + panelSql
                + ") b";

        log.debug("Executing SQL [ " + insertSql + "]");
        sqlParamCount = sqlParamCountList.get(i++);
        // conn.createStatement().executeUpdate(insertSql);
        executeUpdateSql(insertSql, conn, sqlParamCount, inputOptionListHandler);
      }

      String finalSql =
          "SELECT "
              + selectClause
              + " FROM "
              + getDbSchemaName()
              + "visit_dimension visit where encounter_num in (select distinct char_param1 from "
              + factTempTable
              + ") order by encounter_num";
      log.debug("Executing SQL [" + finalSql + "]");
      System.out.println("Final Sql " + finalSql);

      query = conn.prepareStatement(finalSql);

      resultSet = query.executeQuery();

      while (resultSet.next()) {
        EventType event = eventBuilder.buildEventSet(resultSet, this.metaDataParamList);
        eventSet.getEvent().add(event);
      }
    } catch (SQLException sqlEx) {
      log.error("", sqlEx);
      throw new I2B2DAOException("sql exception", sqlEx);
    } catch (IOException ioEx) {
      log.error("", ioEx);
      throw new I2B2DAOException("IO exception", ioEx);
    } finally {
      PdoTempTableUtil tempUtil = new PdoTempTableUtil();
      tempUtil.clearTempTable(dataSourceLookup.getServerType(), conn, factTempTable);

      if (inputOptionListHandler != null && inputOptionListHandler.isEnumerationSet()) {
        try {
          inputOptionListHandler.deleteTempTable(conn);
        } catch (SQLException e) {

          e.printStackTrace();
        }
      }
      try {

        JDBCUtil.closeJdbcResource(null, query, conn);
      } catch (SQLException sqlEx) {
        sqlEx.printStackTrace();
      }
    }
    return eventSet;
  }