/**
   * Creates a SQL Insert Statement stub with placeholders for inserting values in a
   * PreparedStatement and placeholders foreign key columns and values.
   *
   * @param extraForm Object that contains the extra-form submission information that should be
   *     inserted into the extra-form table
   * @param tableName The name of the CORE TABLE into which the extra-form data is inserted
   * @return The SQL INSERT with placeholders
   * @see ExtraForm
   */
  private String createInsertSqlStatementWithPlaceholders(ExtraForm extraForm, String tableName) {
    StringBuilder sb1 = new StringBuilder();
    StringBuilder sb2 = new StringBuilder();
    sb1.append("INSERT INTO " + schemaName + "." + tableName);
    sb1.append(" ( ");
    sb2.append(" VALUES (");
    int count = 1;
    for (ExtraForm.Data d : extraForm.getData()) {
      sb1.append(d.columnName);
      sb2.append("?");

      if (count < extraForm.getData().size()) {
        sb1.append(", ");
        sb2.append(", ");
      }
      count++;
    }
    // ADD FOREIGN KEY DATA placeholders
    sb1.append(" %s");
    sb2.append(" %s");

    sb1.append(" )");
    sb2.append(" );");

    String query = sb1.toString() + " " + sb2.toString();
    return query.toString();
  }
 /**
  * Inserts the supplied ExtraForm data submission into the extra-form table.
  *
  * <p>First the CORE TABLE name will be read from the forms table. Then the SQL INSERT statement
  * is prepared with placeholders for values and foreign key(s). This SQL INSERT statement is then
  * parsed for certain keywords to get the foreign key references. Afterwards the final SQL INSERT
  * statement is created, ready to be used in a PreparedStatement. Finally the insert statement is
  * executed.
  *
  * @param extraForm Object that contains the extra-form submission information that should be
  *     inserted into the extra-form table
  * @return A boolean, indicating if the insertion of the supplied extra-form submission was
  *     successful
  * @throws ConstraintViolations if could not insert extra-form data
  * @see ExtraForm
  */
 @Override
 public boolean insertExtraFormData(ExtraForm extraForm) throws ConstraintViolations {
   String formName = extraForm.getFormName();
   String tableName = getCoreTableNameFromExtraForm(formName);
   String query = createInsertSqlStatementWithPlaceholders(extraForm, tableName);
   // Check for foreign key and insert FK reference data
   Map<String, String> foreignKeyData = getForeignKeyData(extraForm, query);
   query = buildInsertStatement(query, foreignKeyData);
   insertData(extraForm, query);
   return false;
 }
  /**
   * Inserts the supplied ExtraForm data submission into the extra-form table.
   *
   * <p>Will go through the ExtraForm.Data objects and inject the values into the the
   * PreparedStatement according to the data type (INT, DECIMAL, etc.).
   *
   * @param extraForm Object that contains the extra-form submission information that should be
   *     inserted into the extra-form table
   * @param query SQL INSERT statement with placeholders for the PreparedStatement
   * @throws ConstraintViolations if extra-form data could not be inserted
   * @see ExtraForm.Data
   */
  private void insertData(ExtraForm extraForm, String query) throws ConstraintViolations {
    try (Connection connection = dataSource.getConnection();
        PreparedStatement pstmt = connection.prepareStatement(query); ) {
      int pointer = 1;
      for (ExtraForm.Data d : extraForm.getData()) {
        if (d.value == null || d.value.length() == 0) {

          String type = d.type;

          if (type.equalsIgnoreCase("STRING")) {
            //						typeName = "VARCHAR";
            type = "TEXT";
          } else if (type.equalsIgnoreCase("JRDATE")) {
            type = "DATETIME";
          } else if (type.equalsIgnoreCase("JRDATETIME")) {
            type = "DATETIME";
          } else if (type.equalsIgnoreCase("SELECTN") || type.equalsIgnoreCase("SELECT1")) {
            type = "TEXT";
          } else if (type.equalsIgnoreCase("INTEGER")) {
            type = "INT";
          }

          if (type.equalsIgnoreCase("INT")) {
            pstmt.setNull(pointer, java.sql.Types.INTEGER);
          } else if (type.equalsIgnoreCase("DECIMAL")) {
            pstmt.setNull(pointer, java.sql.Types.DECIMAL);
          } else if (type.equalsIgnoreCase("DATETIME")) {
            pstmt.setNull(pointer, java.sql.Types.DATE);
          } else if (type.equalsIgnoreCase("VARCHAR")) {
            pstmt.setNull(pointer, java.sql.Types.VARCHAR);
          } else {
            pstmt.setString(pointer, d.value);
          }
        } else {
          if ((d.type.equalsIgnoreCase("INTEGER") || d.type.equalsIgnoreCase("INT"))
              && d.value.equals("null")) {
            pstmt.setNull(pointer, java.sql.Types.INTEGER);
          } else pstmt.setString(pointer, d.value);
        }
        pointer++;
      }
      int rowCount = pstmt.executeUpdate();
    } catch (SQLException e) {
      if (e instanceof SQLIntegrityConstraintViolationException) {
        throw new ConstraintViolations(e.getMessage());
      } else {
        throw new ConstraintViolations(e.getMessage());
      }
    } catch (Exception e) {
      throw new ConstraintViolations(e.getMessage());
    }
  }
 /**
  * Parses through SQL INSERT query, looking for certain keyword, extracts FOREIGN KEY references
  * according to the values found and returns them in a Map for further processing.
  *
  * <p>Will look for these keywords: - OPENHDS_VISIT_ID - OPENHDS_INDIVIDUAL_ID -
  * OPENHDS_HOUSEHOLD_ID - OPENHDS_LOCATION_ID If one of these is found, gets the appropriate extId
  * from the extraform object and puts the value into a map for further processing.
  *
  * @param extraForm Object that contains the extra-form submission information that should be
  *     inserted into the extra-form table
  * @param query SQL INSERT statement with placeholders for FK data and VALUES
  * @return A Map<String, String> containing the UUID of the references found
  * @throws ConstraintViolations If an object could not be found with the given extId
  */
 private Map<String, String> getForeignKeyData(ExtraForm extraForm, String query)
     throws ConstraintViolations {
   Map<String, String> foreignKeyData = new HashMap<String, String>();
   if (query.contains("OPENHDS_VISIT_ID")) {
     Visit visit = genericDao.findByProperty(Visit.class, "extId", extraForm.getVisitId());
     if (visit == null)
       throw new ConstraintViolations("Could not find visit with extId " + extraForm.getVisitId());
     foreignKeyData.put("VISIT_UUID", "'" + visit.getUuid() + "'");
   }
   if (query.contains("OPENHDS_INDIVIDUAL_ID")
       || query.contains("INDIVIDUAL_INFO_INDIVIDUAL_ID")) {
     Individual individual =
         genericDao.findByProperty(Individual.class, "extId", extraForm.getIndividualId());
     if (individual == null)
       throw new ConstraintViolations(
           "Could not find individual with extId " + extraForm.getIndividualId());
     foreignKeyData.put("INDIVIDUAL_UUID", "'" + individual.getUuid() + "'");
   }
   if (query.contains("OPENHDS_HOUSEHOLD_ID")) {
     SocialGroup socialGroup =
         genericDao.findByProperty(SocialGroup.class, "extId", extraForm.getSocialGroupId());
     if (socialGroup == null)
       throw new ConstraintViolations(
           "Could not find socialGroup with extId " + extraForm.getSocialGroupId());
     foreignKeyData.put("HOUSEHOLD_UUID", "'" + socialGroup.getUuid() + "'");
   }
   if (query.contains("OPENHDS_LOCATION_ID")) {
     Location location =
         genericDao.findByProperty(Location.class, "extId", extraForm.getLocationId());
     if (location == null)
       throw new ConstraintViolations(
           "Could not find location with extId " + extraForm.getLocationId());
     foreignKeyData.put("LOCATION_UUID", "'" + location.getUuid() + "'");
   }
   return foreignKeyData;
 }