/** @see org.openmrs.api.db.AdministrationDAO#executeSQL(java.lang.String, boolean) */
  public List<List<Object>> executeSQL(String sql, boolean selectOnly) throws DAOException {

    // (solution for junit tests that usually use hsql
    // hsql does not like the backtick.  Replace the backtick with the hsql
    // escape character: the double quote (or nothing).
    if (HibernateUtil.isHSQLDialect(sessionFactory)) {
      sql = sql.replace("`", "");
    }
    return DatabaseUtil.executeSQL(
        sessionFactory.getCurrentSession().connection(), sql, selectOnly);
  }
  @Override
  public void execute(Database database) throws CustomChangeException {
    JdbcConnection connection = (JdbcConnection) database.getConnection();

    try {
      Set<String> uniqueFrequencies =
          DatabaseUtil.getUniqueNonNullColumnValues(
              "frequency_text", "drug_order", String.class, connection.getUnderlyingConnection());
      migrateFrequenciesToCodedValue(connection, uniqueFrequencies);
    } catch (Exception e) {
      throw new CustomChangeException(e);
    }
  }
  private static Connection connect(String url) throws SQLException {
    // Step 1: Load the JDBC driver.
    try {
      DatabaseUtil.loadDatabaseDriver(url);
    } catch (ClassNotFoundException e) {
      log.error("Could not find JDBC driver class.", e);
      throw (SQLException) e.fillInStackTrace();
    }

    // Step 2: Establish the connection to the database.
    String username = Context.getRuntimeProperties().getProperty("connection.username");
    String password = Context.getRuntimeProperties().getProperty("connection.password");
    log.debug(
        "connecting to DATABASE: "
            + OpenmrsConstants.DATABASE_NAME
            + " USERNAME: "******" URL: "
            + url);
    return DriverManager.getConnection(url, username, password);
  }
  /** Method to perform validation and resolution of duplicate EncounterType names */
  @Override
  public void execute(Database database) throws CustomChangeException {
    JdbcConnection connection = (JdbcConnection) database.getConnection();
    Map<String, HashSet<Integer>> duplicates = new HashMap<String, HashSet<Integer>>();
    Statement stmt = null;
    PreparedStatement pStmt = null;
    ResultSet rs = null;

    try {
      // set auto commit mode to false for UPDATE action
      connection.setAutoCommit(false);

      stmt = connection.createStatement();
      rs =
          stmt.executeQuery(
              "SELECT * FROM encounter_type INNER JOIN (SELECT name FROM encounter_type GROUP BY name HAVING count(name) > 1) dup ON encounter_type.name = dup.name");

      Integer id = null;
      String name = null;

      while (rs.next()) {
        id = rs.getInt("encounter_type_id");
        name = rs.getString("name");

        if (duplicates.get(name) == null) {
          HashSet<Integer> results = new HashSet<Integer>();
          results.add(id);
          duplicates.put(name, results);
        } else {
          HashSet<Integer> results = duplicates.get(name);
          results.add(id);
        }
      }

      Iterator it2 = duplicates.entrySet().iterator();
      while (it2.hasNext()) {
        Map.Entry pairs = (Map.Entry) it2.next();

        HashSet values = (HashSet) pairs.getValue();
        List<Integer> editableNames = new ArrayList<Integer>(values);

        int duplicateNameId = 1;
        for (int i = 1; i < editableNames.size(); i++) {
          String newName = pairs.getKey() + "_" + duplicateNameId;

          List<List<Object>> duplicateResult = null;
          boolean duplicateName = false;
          Connection con = DatabaseUpdater.getConnection();

          do {
            String sqlValidatorString =
                "select * from encounter_type where name = '" + newName + "'";
            duplicateResult = DatabaseUtil.executeSQL(con, sqlValidatorString, true);

            if (!duplicateResult.isEmpty()) {
              duplicateNameId += 1;
              newName = pairs.getKey() + "_" + duplicateNameId;
              duplicateName = true;
            } else {
              duplicateName = false;
            }
          } while (duplicateName);

          pStmt =
              connection.prepareStatement(
                  "update encounter_type set name = ? where encounter_type_id = ?");
          pStmt.setString(1, newName);
          pStmt.setInt(2, editableNames.get(i));

          duplicateNameId += 1;

          pStmt.executeUpdate();
        }
      }
    } catch (BatchUpdateException e) {
      log.warn("Error generated while processsing batch insert", e);

      try {
        log.debug("Rolling back batch", e);
        connection.rollback();
      } catch (Exception rbe) {
        log.warn("Error generated while rolling back batch insert", e);
      }

      // marks the changeset as a failed one
      throw new CustomChangeException(
          "Failed to update one or more suplicate EncounterType names", e);
    } catch (DatabaseException e) {
      throw new CustomChangeException(
          "Error while updating duplicate EncounterType object names", e);
    } catch (SQLException e) {
      throw new CustomChangeException(
          "Error while updating duplicate EncounterType object names", e);
    } catch (DAOException e) {
      throw new CustomChangeException("Error accessing database connection", e);
    } catch (Exception e) {
      throw new CustomChangeException("Error accessing database connection", e);
    } finally {
      // reset to auto commit mode
      try {
        connection.setAutoCommit(true);
      } catch (DatabaseException e) {
        log.warn("Failed to reset auto commit back to true", e);
      }
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          log.warn("Failed to close the resultset object");
        }
      }

      if (stmt != null) {
        try {
          stmt.close();
        } catch (SQLException e) {
          log.warn(
              "Failed to close the select statement used to identify duplicate EncounterType object names");
        }
      }

      if (pStmt != null) {
        try {
          pStmt.close();
        } catch (SQLException e) {
          log.warn(
              "Failed to close the prepared statement used to update duplicate EncounterType object names");
        }
      }
    }
  }
  /** @see CustomTaskChange#execute(Database) */
  @Override
  public void execute(Database database) throws CustomChangeException {
    Connection conn = ((JdbcConnection) database.getConnection()).getUnderlyingConnection();
    List<String> messages = new ArrayList<String>();

    // Warn if any states are configured as both initial and terminal
    StringBuilder message = new StringBuilder();
    message.append(
        "Starting now, when you transition a patient into a state that is configured as terminal, ");
    message.append("then that whole program enrollment will be marked as completed.<br/>");
    message.append("Please check that programs, workflows, and states are configured.<br/>");
    message.append("This check will highlight two things: ");
    message.append("<ul><li>states that are marked as both initial and terminal ");
    message.append(
        "(if you start someone in that state their program enrollment will be instantly closed)</li>");
    message.append(
        "<li>workflows that have no initial states (because you don't have a state to start people in)</li>");
    message.append("</ul><br/>");
    message.append("The following states are configured as both initial and terminal:<br/>");

    StringBuilder query = new StringBuilder();
    query.append(" select 	s.concept_id, min(n.name) as name ");
    query.append(" from 	program_workflow_state s, concept_name n ");
    query.append(" where 	s.concept_id = n.concept_id and initial = '1' and terminal = '1' ");
    query.append(" group by s.concept_id ");
    List<List<Object>> results = DatabaseUtil.executeSQL(conn, query.toString(), true);
    if (results.isEmpty()) {
      message.append("None found.");
    } else {
      for (List<Object> row : results) {
        message.append(row.get(1).toString()).append("<br/>");
      }
    }

    // Warn if any workflows have no initial states
    message.append("<br/>The following workflows have no initial states...<br/>");
    query = new StringBuilder();
    query.append(" select 		w.concept_id, s.initial, count(*) as num ");
    query.append(" from			program_workflow w, program_workflow_state s ");
    query.append(" where		w.program_workflow_id = s.program_workflow_id ");
    query.append(" group by 	w.concept_id, s.initial ");

    results = DatabaseUtil.executeSQL(conn, query.toString(), true);
    List<Integer> missingInitial = new ArrayList<Integer>();
    for (List<Object> row : results) {
      missingInitial.add(Integer.valueOf(row.get(0).toString()));
    }
    for (List<Object> row : results) {
      Integer conceptId = Integer.valueOf(row.get(0).toString());
      boolean isInitial = row.get(1).toString().equals("1");
      int num = Integer.parseInt(row.get(2).toString());
      if (isInitial && num > 0) {
        missingInitial.remove(conceptId);
      }
    }
    if (missingInitial.isEmpty()) {
      message.append("None found.");
    } else {
      for (Integer conceptId : missingInitial) {
        String sql = "select min(name) from concept_name where concept_id = " + conceptId;
        String name = DatabaseUtil.executeSQL(conn, sql, true).get(0).get(0).toString();
        message.append(name).append("<br/>");
      }
    }
    messages.add(message.toString());

    DatabaseUpdater.reportUpdateWarnings(messages);
  }