Excel Tools

package com.appiancorp.ps.exceltools.util;

import java.io.BufferedWriter;
import java.io.File;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.Date;

import javax.naming.Context;
import javax.naming.NamingException;
import javax.sql.DataSource;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.appiancorp.suiteapi.common.Constants;
import com.appiancorp.suiteapi.common.ResultPage;
import com.appiancorp.suiteapi.content.Approval;
import com.appiancorp.suiteapi.content.ContentConstants;
import com.appiancorp.suiteapi.content.ContentFilter;
import com.appiancorp.suiteapi.content.ContentService;
import com.appiancorp.suiteapi.encryption.EncryptionService;
import com.appiancorp.suiteapi.knowledge.Document;
import com.appiancorp.suiteapi.type.AppianType;
import com.appiancorp.suiteapi.type.TypedValue;

public class DatabaseUtils {

  private static final Logger LOG = Logger.getLogger(DatabaseUtils.class);
  private static final int TYPE_ORACLE_CURSOR = -10;

  public enum DatabaseType {
	  POSTGRE("postgre", "\"", "\""), ORACLE("oracle", "\"", "\""), MSSQL("microsoft", "[", "]"), MYSQL("mysql", "`", "`"), DB2("db2", "", ""), MARIADB("mariadb", "`", "`");

    private final String name;
    private final String startQuoteSymbol;
    private final String endQuoteSymbol;

    private DatabaseType(String name, String startQuoteSymbol, String endQuoteSymbol) {
      this.name = name;
      this.startQuoteSymbol = startQuoteSymbol;
      this.endQuoteSymbol = endQuoteSymbol;
    }

    public Boolean matches(String databaseName) {
      return databaseName.toLowerCase().contains(this.name);
    }
  }

  public static Connection getConnection(String dataSourceName, Context ctx_) throws NamingException, SQLException {
    if (LOG.isDebugEnabled()) {
      LOG.debug("Getting Connection for Datasource name:" + dataSourceName);
    }
    DataSource ds = (DataSource) ctx_.lookup(dataSourceName);
    return ds.getConnection();
  }

  public static String getSelectSQL(DatabaseType dbType, String tableName) {
    StringBuffer sql = new StringBuffer("SELECT * FROM ");

    appendTableNameWithQuotes(dbType, sql, tableName);

    sql.append(" WHERE 1=0 ");
    if (LOG.isDebugEnabled()) {
      LOG.debug("Generated Select SQL: " + sql);
    }
    return sql.toString();
  }

  public static String getSelectSQL(DatabaseType dbType, String tableName, String[] columns) {
    StringBuffer sql = new StringBuffer("select ");
    sql.append(dbType.startQuoteSymbol);
    sql.append(StringUtils.join(columns, dbType.endQuoteSymbol + ", " + dbType.startQuoteSymbol));
    sql.append(dbType.endQuoteSymbol);
    sql.append(" from ");

    appendTableNameWithQuotes(dbType, sql, tableName);

    sql.append(" where 1=0 ");
    if (LOG.isDebugEnabled()) {
      LOG.debug("Generated Select SQL: " + sql);
    }
    return sql.toString();
  }

  public static String[] getColumnNames(Connection conn, DatabaseType dbType, String tableName)
    throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    DatabaseMetaData dbMetadata = conn.getMetaData();
    try {
      if (DatabaseType.MYSQL.matches(dbMetadata.getDatabaseProductName())) {
        ps = conn.prepareStatement(getSelectSQL(dbType, tableName),
          java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
        ps.setFetchSize(Integer.MIN_VALUE);
      } else {
        ps = conn.prepareStatement(getSelectSQL(dbType, tableName));
      }
      rs = ps.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();
      int columnCount = rsmd.getColumnCount();

      String[] columnNames = new String[columnCount];

      for (int i = 0; i < columnCount; i++) {
        String colname = rsmd.getColumnName(i + 1).toLowerCase();
        columnNames[i] = colname;
      }
      return columnNames;
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (Exception e) {
      }
      try {
        if (ps != null) {
          ps.close();
        }
      } catch (Exception e) {
      }
    }
  }

  /* The csvColumnToDBColumn contains indexes of the column names stored in the "columns" array that matched to the database */
  public static int[] getColumnTypes(Connection conn, DatabaseType dbType, String tableName, String[] columns)
    throws SQLException {
    PreparedStatement ps = null;
    ResultSet rs = null;
    DatabaseMetaData dbMetadata = conn.getMetaData();
    if (columns == null || columns.length == 0) {
      throw new IllegalArgumentException("None of the columns from the CSV file matched the database table \"" + tableName + "\"");
    }

    try {
      if (DatabaseType.MYSQL.matches(dbMetadata.getDatabaseProductName())) {

        ps = conn.prepareStatement(getSelectSQL(dbType, tableName, columns),
          java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
        ps.setFetchSize(Integer.MIN_VALUE);
      } else {
        ps = conn.prepareStatement(getSelectSQL(dbType, tableName, columns));
      }
      rs = ps.executeQuery();
      ResultSetMetaData rsmd = rs.getMetaData();

      int[] columnTypes = new int[columns.length];
      for (int i = 0; i < columns.length; i++) {
        int coltype = rsmd.getColumnType(i + 1);
        columnTypes[i] = coltype;
      }
      return columnTypes;
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (Exception e) {
      }
      try {
        if (ps != null) {
          ps.close();
        }
      } catch (Exception e) {
      }
    }
  }

  private static String getFormattedValue(ResultSet rs, int column, SimpleDateFormat dateFormat,
    SimpleDateFormat dateTimeFormat, SimpleDateFormat timeFormat) throws SQLException {

    int coltype = rs.getMetaData().getColumnType(column);
    Date d = null;
    switch (coltype) {
    case Types.BIT:
    case Types.BOOLEAN:
      boolean b = rs.getBoolean(column);
      if (rs.wasNull()) {
        return "";
      } else {
        return b ? "1" : "0";
      }
    case Types.DATE:
      d = rs.getDate(column);
      if (d == null || rs.wasNull()) {
        return "";
      } else {
        return dateFormat.format(d);
      }
    case Types.TIMESTAMP:
      d = rs.getTimestamp(column);
      if (d == null || rs.wasNull()) {
        return "";
      } else {
        return dateTimeFormat.format(d);
      }
    case Types.TIME:
      d = rs.getTime(column);
      if (d == null || rs.wasNull()) {
        return "";
      } else {
        return timeFormat.format(d);
      }
    case Types.DOUBLE:
    case Types.FLOAT:
    case Types.DECIMAL:
    case Types.NUMERIC:
    case Types.REAL:
      double doub = rs.getDouble(column);
      if (rs.wasNull()) {
        return "";
      } else {
        NumberFormat f = NumberFormat.getInstance();
        f.setGroupingUsed(false);
        f.setMinimumFractionDigits(0);
        f.setMaximumFractionDigits(500);

        return f.format(doub) + "";
      }
    case Types.INTEGER:
    case Types.BIGINT:
    case Types.SMALLINT:
    case Types.TINYINT:
      Long l = rs.getLong(column);
      if (rs.wasNull()) {
        return "";
      } else {
        return l.toString();
      }

    case Types.VARCHAR:
    case Types.CHAR:
    case Types.LONGVARCHAR:
      String s = rs.getString(column);
      if (rs.wasNull()) {
        return "";
      } else if (StringUtils.isEmpty(s)) {
        return "";
      } else {
        return StringEscapeUtils.escapeCsv(s);
      }

    case Types.NVARCHAR:
    case Types.NCHAR:
    case Types.LONGNVARCHAR:
      String n = rs.getNString(column);
      if (rs.wasNull()) {
        return "";
      } else if (StringUtils.isEmpty(n)) {
        return "";
      } else {
        return StringEscapeUtils.escapeCsv(n);
      }

    default:
      LOG.warn("Error no handler for " + coltype);
      return "";
    }
  }

  public static StringBuffer exportData(Connection conn, String sql, String delimiter, boolean includeHeader,
    String end_of_line, SimpleDateFormat dateFormat, SimpleDateFormat dateTimeFormat,
    SimpleDateFormat timeFormat) throws SQLException, ParseException, IOException {

    StringBuffer s = new StringBuffer();
    PreparedStatement ps = null;
    ResultSet rs = null;
    DatabaseMetaData dbMetadata = conn.getMetaData();
    try {
      if (DatabaseType.MYSQL.matches(dbMetadata.getDatabaseProductName())) {
        ps = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY,
          java.sql.ResultSet.CONCUR_READ_ONLY);
        ps.setFetchSize(Integer.MIN_VALUE);
      } else {
        ps = conn.prepareStatement(sql);
      }

      rs = ps.executeQuery();

      ResultSetMetaData md = rs.getMetaData();
      int cols = md.getColumnCount();
      if (includeHeader) {
        for (int i = 0; i < cols; i++) {
          s.append("\"").append(md.getColumnName(i + 1)).append("\"");
          if (i + 1 < cols) {
            s.append(delimiter);
          } else {
            s.append(end_of_line);
          }
        }
      }
      while (rs.next()) {
        for (int i = 0; i < cols; i++) {
          s.append(getFormattedValue(rs, i + 1, dateFormat, dateTimeFormat, timeFormat));
          if (i + 1 < cols) {
            s.append(delimiter);
          } else {
            s.append(end_of_line);
          }
        }
      }
    } finally {
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (Exception e) {
      }
      try {
        if (ps != null) {
          ps.close();
        }
      } catch (Exception e) {
      }
    }
    return s;
  }

  public static Long exportBufferedDataToFile(Connection conn, String sql, String delimiter, boolean includeHeader,
    String end_of_line, ContentService cs, Long documentToOverwrite, Long folder, String name, String extension,
    SimpleDateFormat dateFormat, SimpleDateFormat dateTimeFormat, SimpleDateFormat timeFormat)
    throws SQLException, ParseException, IOException, Exception {

    PreparedStatement ps = null;
    CallableStatement calls = null;
    ResultSet rs = null;
    String filePath = null;
    Document d = null;
    BufferedWriter bw = null;
    try {
      // check to see if Oracle Stored Proc since
      // we MUST register an output for result sets to be returned
      // support single OUT parameter as a cursor
      DatabaseMetaData dmd = conn.getMetaData();
      if (DatabaseType.ORACLE.matches(dmd.getDatabaseProductName()) &&
        (sql.toLowerCase().startsWith("call ") || sql.toLowerCase().startsWith("{call "))) {
        calls = conn.prepareCall(sql);
        // register the sysref OUT cursor
        calls.registerOutParameter(1, TYPE_ORACLE_CURSOR);
        calls.executeUpdate();
        rs = (ResultSet) calls.getObject(1);
      } else {
        if (DatabaseType.MYSQL.matches(dmd.getDatabaseProductName())) {
          ps = conn.prepareStatement(sql, java.sql.ResultSet.TYPE_FORWARD_ONLY,
            java.sql.ResultSet.CONCUR_READ_ONLY);
          ps.setFetchSize(Integer.MIN_VALUE);
        } else {
          ps = conn.prepareStatement(sql);
        }
        rs = ps.executeQuery();
      }

      if (!rs.isBeforeFirst()) {
        // no data so don't return anything
        return null;
      }

      // prepare file
      d = registerDocument(cs, documentToOverwrite, folder, name, extension);
      filePath = d.getInternalFilename();
      // create the missing folder structure if it does not exist
      File newFile = new File(filePath);
      if (!newFile.getParentFile().exists()) {
        newFile.getParentFile().mkdirs();
      }
      // set up writer
      bw = Files.newBufferedWriter(Paths.get(filePath), StandardCharsets.UTF_8);
      // start writing data
      ResultSetMetaData md = rs.getMetaData();
      int cols = md.getColumnCount();
      if (includeHeader) {
        for (int i = 0; i < cols; i++) {
          writeTextToFile(bw, "\"" + md.getColumnName(i + 1) + "\"");
          if (i + 1 < cols) {
            writeTextToFile(bw, delimiter);
          } else {
            writeTextToFile(bw, end_of_line);
          }
        }
      }
      while (rs.next()) {
        for (int i = 0; i < cols; i++) {
          writeTextToFile(bw, getFormattedValue(rs, i + 1, dateFormat, dateTimeFormat, timeFormat));
          if (i + 1 < cols) {
            writeTextToFile(bw, delimiter);
          } else {
            writeTextToFile(bw, end_of_line);
          }
        }
      }
    } catch (Exception e) {
      // generic exception catch all - use this block to clean up
      try {
        if (d != null) {
          // if document object has been created, try to delete it
          cs.deleteVersion(d.getId(), ContentConstants.VERSION_CURRENT, true);
        }
      } catch (Exception ex) {
      }

      throw new Exception(e);
    } finally {
      // close RS
      try {
        if (rs != null) {
          rs.close();
        }
      } catch (Exception e) {
      }
      try {
        if (ps != null) {
          ps.close();
        }
      } catch (Exception e) {
      }

      // close Writer
      try {
        if (bw != null) {
          closeBufferedWriter(bw);
        }
      } catch (Exception e) {

      }
    }

    // finalize document attributes
    File file = new File(filePath);
    d.setSize((int) file.length());
    cs.setSizeOfDocumentVersion(d.getId());
    return d.getId();
  }

  private static Document registerDocument(ContentService cs, Long document, Long folder, String name,
    String extension) throws Exception {

    // boolean isDebug = LOG.isDebugEnabled();
    ContentFilter cf = new ContentFilter(ContentConstants.TYPE_DOCUMENT);
    cf.setName(name);
    cf.setExtension(new String[] { extension });

    Long docId = null;
    if (document != null) {
      Document d = (Document) cs.getVersion(document, ContentConstants.VERSION_CURRENT);
      d.setFileSystemId(ContentConstants.ALLOCATE_FSID);
      Approval a = cs.createVersion(d, ContentConstants.UNIQUE_FOR_PARENT);
      docId = a.getId()[0];
    } else {
      ResultPage children = cs.getChildrenPaging(folder, cf, ContentConstants.GC_MOD_NORMAL, 0, 1, ContentConstants.COLUMN_NONE,
        Constants.SORT_ORDER_ASCENDING);
      if (children == null || children.getAvailableItems() == 0) {
        Document d = new Document();
        d.setName(name);
        d.setExtension(extension);
        d.setSize(1);
        d.setParent(folder);
        d.setState(ContentConstants.STATE_ACTIVE_PUBLISHED);
        docId = cs.create(d, ContentConstants.UNIQUE_FOR_PARENT);
      } else {
        Document d = (Document) children.getResults()[0];
        d.setFileSystemId(ContentConstants.ALLOCATE_FSID);
        Approval a = cs.createVersion(d, ContentConstants.UNIQUE_FOR_PARENT);
        docId = a.getId()[0];
      }
    }

    if (docId != null) {
      Document d = cs.download(docId, ContentConstants.VERSION_CURRENT, false)[0];
      d.setId(docId);
      return d;
    }
    return null;
  }

  public static void writeTextToFile(BufferedWriter bw, String text) throws IOException {
    bw.write(text);
  }

  public static void closeBufferedWriter(BufferedWriter bw) throws IOException {
    bw.flush();
    bw.close();
  }

  public static void addBatchInsert(PreparedStatement ps_, String[] values_, int[] columnTypes_,
    boolean[] columnEncryptedFlags_, SimpleDateFormat dateFormat, DateTimeFormatter dateTimeFormat,
    SimpleDateFormat timeFormat, EncryptionService es)
    throws SQLException, ParseException, DateTimeParseException {

    for (int i = 0; i < values_.length; i++) {
      if (columnEncryptedFlags_[i]) {
        values_[i] = es.decrypt(new TypedValue((long) AppianType.ENCRYPTED_TEXT, values_[i]));
      }
      switch (columnTypes_[i]) {
      case Types.DATE:
        if (StringUtils.isEmpty(values_[i])) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else {
          ps_.setDate(i + 1, new java.sql.Date(dateFormat.parse(values_[i]).getTime()));
        }
        break;
      case Types.TIMESTAMP:
        if (StringUtils.isEmpty(values_[i])) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else {
          ps_.setTimestamp(i + 1, parseTimestamp(dateTimeFormat, values_[i]));
        }
        break;
      case Types.TIME:
        if (StringUtils.isEmpty(values_[i])) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else {
          ps_.setTime(i + 1, new java.sql.Time(timeFormat.parse(values_[i]).getTime()));
        }
        break;
      case Types.DOUBLE:
      case Types.FLOAT:
      case Types.DECIMAL:
      case Types.NUMERIC:
      case Types.REAL:
        if (StringUtils.isEmpty(values_[i])) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else {
          ps_.setDouble(i + 1, new Double(values_[i]));
        }
        break;
      case Types.BIT:
      case Types.BOOLEAN:
        if (StringUtils.isEmpty(values_[i])) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else if (values_[i].equals("0") || values_[i].equals("1")) {
          ps_.setBoolean(i + 1, values_[i].equals("0") ? false : true);
        } else {
          ps_.setBoolean(i + 1, Boolean.parseBoolean(values_[i]));
        }
        break;
      case Types.INTEGER:
      case Types.BIGINT:
      case Types.SMALLINT:
      case Types.TINYINT:
        if (StringUtils.isEmpty(values_[i])) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else {
          ps_.setInt(i + 1, new Integer(values_[i]));
        }
        break;
      case Types.VARCHAR:
      case Types.CHAR:
      case Types.LONGVARCHAR:
        if (values_[i] == null) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else if (StringUtils.isEmpty(values_[i])) {
          ps_.setString(i + 1, "");
        } else {
          ps_.setString(i + 1, values_[i]);
        }
        break;
      case Types.NVARCHAR:
      case Types.NCHAR:
      case Types.LONGNVARCHAR:
        if (values_[i] == null) {
          ps_.setNull(i + 1, columnTypes_[i]);
        } else if (StringUtils.isEmpty(values_[i])) {
          ps_.setNString(i + 1, "");
        } else {
          ps_.setNString(i + 1, values_[i]);
        }
        break;
      default:
        LOG.error("Error no handler for " + columnTypes_[i]);
      }
    }
    ps_.addBatch();
  }

  public static String getInsertSQL(DatabaseType dbType, String tableName, String[] columns) {
    String columnsAsList = dbType.startQuoteSymbol + StringUtils.join(columns, dbType.endQuoteSymbol + ", " + dbType.startQuoteSymbol) +
      dbType.endQuoteSymbol;

    StringBuffer insertSql = new StringBuffer("insert into ");
    appendTableNameWithQuotes(dbType, insertSql, tableName);

    insertSql.append(" (").append(columnsAsList).append(")");
    insertSql.append(" values (");
    for (int i = 0; i < columns.length; i++) {
      insertSql.append("?");
      if (i < columns.length - 1) {
        insertSql.append(", ");
      }
    }
    insertSql.append(")");
    return insertSql.toString();
  }

  public static String getInsertSQL(DatabaseType dbType, String tableName, String[] columns, String[] extraColumns) {
    String columnsAsList = dbType.startQuoteSymbol + StringUtils.join(columns, dbType.endQuoteSymbol + ", " + dbType.startQuoteSymbol) +
      dbType.endQuoteSymbol;
    String extraColumnsAsList = "";

    if (extraColumns != null) {
      extraColumnsAsList = dbType.startQuoteSymbol +
        StringUtils.join(extraColumns, dbType.endQuoteSymbol + ", " + dbType.startQuoteSymbol) + dbType.endQuoteSymbol;
    }

    StringBuffer insertSql = new StringBuffer("insert into ");

    appendTableNameWithQuotes(dbType, insertSql, tableName);

    insertSql.append(" (").append(columnsAsList);

    if (extraColumns != null) {
      insertSql.append(", ");
      insertSql.append(extraColumnsAsList);
    }

    insertSql.append(")");
    insertSql.append(" values (");
    for (int i = 0; i < columns.length; i++) {
      insertSql.append("?");
      if ((i < columns.length - 1) || (extraColumns != null)) {
        insertSql.append(", ");
      }
    }

    if (extraColumns != null) {
      for (int i = 0; i < extraColumns.length; i++) {
        insertSql.append("?");
        if (i < extraColumns.length - 1) {
          insertSql.append(", ");
        }
      }
    }

    insertSql.append(")");
    return insertSql.toString();
  }

  public static DatabaseType getDatabaseType(String dbName) {
    LOG.debug("Database name is: " + dbName);
    if (DatabaseType.POSTGRE.matches(dbName)) {
        return DatabaseType.POSTGRE;
    }
    else if (DatabaseType.ORACLE.matches(dbName)) {
      return DatabaseType.ORACLE;
    } else if (DatabaseType.MSSQL.matches(dbName)) {
      return DatabaseType.MSSQL;
    } else if (DatabaseType.DB2.matches(dbName)) {
      return DatabaseType.DB2;
    } else if (DatabaseType.MARIADB.matches(dbName)) {
      return DatabaseType.MARIADB;
    } else {
      return DatabaseType.MYSQL;
    }
  }

  public static void appendTableNameWithQuotes(DatabaseType dbType, StringBuffer sqlBuilder, String tableName) {
    String[] tableNameSplit = tableName.split("\\.");
    sqlBuilder.append(dbType.startQuoteSymbol);
    sqlBuilder.append(StringUtils.join(tableNameSplit, dbType.endQuoteSymbol + "." + dbType.startQuoteSymbol));
    sqlBuilder.append(dbType.endQuoteSymbol);
  }

  private static Timestamp parseTimestamp(DateTimeFormatter dateTimeFormat, String value) throws DateTimeParseException {
    LocalDateTime ldt = LocalDateTime.parse(value, dateTimeFormat);
    return Timestamp.valueOf(ldt);
  }
}