org.hsh.bfr.db/src/org/hsh/bfr/db/imports/GeneralXLSImporter.java
/*******************************************************************************
* Copyright (c) 2015 Federal Institute for Risk Assessment (BfR), Germany
*
* This program is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with this program. If not, see <http://www.gnu.org/licenses/>.
*
* Contributors:
* Department Biological Safety - BfR
*******************************************************************************/
package org.hsh.bfr.db.imports;
/**
*
*/
import java.awt.Dimension;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Hashtable;
import java.util.Iterator;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Vector;
import javax.swing.JProgressBar;
import javax.swing.filechooser.FileFilter;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.hsh.bfr.db.DBKernel;
import org.hsh.bfr.db.Levenshtein;
import org.hsh.bfr.db.MyLogger;
import org.hsh.bfr.db.MyTable;
import org.hsh.bfr.db.gui.InfoBox;
import org.hsh.bfr.db.gui.dbtable.MyDBTable;
import org.hsh.bfr.db.gui.dbtree.MyDBTree;
/**
* @author Armin
*
*/
public class GeneralXLSImporter extends FileFilter implements MyImporter {
private boolean takecareofID = false; // in case of INSERTs!!!
public GeneralXLSImporter() {
}
public GeneralXLSImporter(boolean takecareofID) {
this.takecareofID = takecareofID;
}
/**
* This is the one of the methods that is declared in the abstract class
*/
public boolean accept(File f) {
if (f.isDirectory()) return true;
String extension = getExtension(f);
if ((extension.equals("xls"))) return true;
return false;
}
public String getDescription() {
return "Excel Datei (*.xls)";
}
private String getExtension(File f) {
String s = f.getName();
int i = s.lastIndexOf('.');
if (i > 0 && i < s.length() - 1) return s.substring(i + 1).toLowerCase();
return "";
}
public String doImport(final String filename, final JProgressBar progress, final boolean showResults) {
Runnable runnable = new Runnable() {
public void run() {
try {
if (progress != null) {
progress.setVisible(true);
progress.setStringPainted(true);
progress.setString("Importiere Excel Datei...");
progress.setMinimum(0);
}
InputStream is = null;
System.out.println(filename);
if (filename.startsWith("http://")) {
URL url = new URL(filename);
URLConnection uc = url.openConnection();
is = uc.getInputStream();
} else if (filename.startsWith("/org/hsh/bfr/db/res/")) {
is = this.getClass().getResourceAsStream(filename);
} else {
is = new FileInputStream(filename);
}
POIFSFileSystem fs = new POIFSFileSystem(is);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet;
HSSFRow row;
int numSuccess = 0;
int numFailed = 0;
String unusedFields = "";
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
sheet = wb.getSheetAt(i);
String tableName = sheet.getSheetName();
MyTable myT = DBKernel.myDBi.getTable(tableName);
if (myT != null) {
int numRows = sheet.getLastRowNum();
if (progress != null) {
progress.setMaximum(numRows);
progress.setValue(0);
}
row = sheet.getRow(0);
String sql1 = "";
String sql2 = "";
String sql3 = "";
Vector<String> codeSql1 = new Vector<>();
Vector<String> codeSql2 = new Vector<>();
LinkedHashMap<MyTable, Vector<Integer>> foreignTables = new LinkedHashMap<>();
int numCols = row.getLastCellNum();
String[] fieldNames = new String[numCols];
String[] fieldTypes = new String[numCols];//getTypes(fieldNames, myT);
String[] ffieldTypes = new String[numCols];
MyTable[] myForeignTables = new MyTable[numCols];
String[] kzS = new String[numCols];
String[] dbFieldnames = new String[numCols];
int lfdCol = 0;
Hashtable<String, String> dbFieldNames = new Hashtable<>();
for (int j = 0; j < numCols; j++) {
String fieldName = row.getCell(j).getStringCellValue();
fieldNames[j] = fieldName;
int ffe;
String dbFieldName = getDBFieldName(fieldName, myT, takecareofID);
if (dbFieldName != null) {
String ft = getForeignTable(dbFieldName, myT);
if (ft != null && ft.equals("DoubleKennzahlen")) {
kzS[j] = getKZ(fieldName, dbFieldName);
dbFieldnames[j] = dbFieldName;
} else if (!dbFieldNames.containsKey(dbFieldName)) {
dbFieldNames.put(dbFieldName, dbFieldName);
sql1 += DBKernel.delimitL(dbFieldName) + ",";
sql2 += "?,";
sql3 += DBKernel.delimitL(dbFieldName) + "=?,";
lfdCol++;
}
fieldTypes[j] = getType(dbFieldName, myT, takecareofID);
} else if ((ffe = foreignFieldExists(fieldName, myT)) >= 0) {
if (!foreignTables.containsKey(myT.getForeignFields()[ffe])) foreignTables.put(myT.getForeignFields()[ffe], new Vector<Integer>());
ffieldTypes[j] = getType(fieldName, myT.getForeignFields()[ffe], false);
foreignTables.get(myT.getForeignFields()[ffe]).add(j);
myForeignTables[j] = myT.getForeignFields()[ffe];
} else if (DBKernel.showHierarchic(tableName) && fieldName.toLowerCase().endsWith("-code")) {
codeSql1.add(DBKernel.delimitL("CodeSystem") + "," + DBKernel.delimitL("Code") + "," + DBKernel.delimitL("Basis"));
codeSql2.add("'" + fieldName.substring(0, fieldName.length() - "-code".length()) + "',?,?");
} else if (!fieldName.equalsIgnoreCase("id")) {
unusedFields += "," + fieldName;
}
}
if (sql1.length() > 0 && sql2.length() > 0) {
String sql = "INSERT INTO " + DBKernel.delimitL(tableName) + " (" + sql1.substring(0, sql1.length() - 1) + ") VALUES ("
+ sql2.substring(0, sql2.length() - 1) + ")";
PreparedStatement ps = DBKernel.getDBConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
int idCol = lfdCol + 1;
sql = "UPDATE " + DBKernel.delimitL(tableName) + " SET " + sql3.substring(0, sql3.length() - 1) + " WHERE " + DBKernel.delimitL("ID") + "=?";
PreparedStatement psUpdate = DBKernel.getDBConnection().prepareStatement(sql);
PreparedStatement[] psCodes = new PreparedStatement[codeSql1.size()];
boolean doCode[] = new boolean[codeSql1.size()];
int codesI;
for (codesI = 0; codesI < codeSql1.size(); codesI++) {
sql = "INSERT INTO " + DBKernel.delimitL(DBKernel.getCodesName(tableName)) + " (" + codeSql1.get(codesI) + ") VALUES (" + codeSql2.get(codesI)
+ ")";
psCodes[codesI] = DBKernel.getDBConnection().prepareStatement(sql);
}
LinkedHashMap<MyTable, PreparedStatement> psForeign = new LinkedHashMap<>();
LinkedHashMap<MyTable, PreparedStatement> psForeignUpdate = new LinkedHashMap<>();
for (Map.Entry<MyTable, Vector<Integer>> entry : foreignTables.entrySet()) {
Vector<Integer> vs = entry.getValue();
String ssql1 = "", ssql2 = "", ssql3 = "";
for (int ii = 0; ii < vs.size(); ii++) {
ssql1 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]);
ssql2 += ",?";
ssql3 += "," + DBKernel.delimitL(fieldNames[vs.get(ii)]) + "=?";
}
if (ssql1.length() > 0 && ssql2.length() > 0 && ssql3.length() > 0) {
sql = "INSERT INTO " + DBKernel.delimitL(entry.getKey().getTablename()) + " (" + ssql1.substring(1) + ") VALUES (" + ssql2.substring(1)
+ ")";
psForeign.put(entry.getKey(), DBKernel.getDBConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS));
sql = "UPDATE " + DBKernel.delimitL(entry.getKey().getTablename()) + " SET " + ssql3.substring(1) + " WHERE " + DBKernel.delimitL("ID")
+ "=?";
psForeignUpdate.put(entry.getKey(), DBKernel.getDBConnection().prepareStatement(sql));
}
}
LinkedHashMap<Object, String> hashBL = null;
Iterator<Row> rows = sheet.rowIterator();
int lfd = 0;
while (rows.hasNext()) {
row = (HSSFRow) rows.next();
boolean setID = false;
Integer lastID = null;
ps.clearParameters();
psUpdate.clearParameters();
for (codesI = 0; codesI < codeSql1.size(); codesI++) {
psCodes[codesI].clearParameters();
doCode[codesI] = false;
}
LinkedHashMap<MyTable, Integer> lfdColsForeign = new LinkedHashMap<>();
for (Map.Entry<MyTable, PreparedStatement> entry : psForeignUpdate.entrySet()) {
MyTable myT1 = entry.getKey();
psForeign.get(myT1).clearParameters();
psForeignUpdate.get(myT1).clearParameters();
lfdColsForeign.put(myT1, 0);
}
if (row.getRowNum() > 0) {
lfdCol = 0;
codesI = 0;
Object[] kzVal = new Object[numCols];
for (int j = 0; j < numCols; j++) {
if (fieldTypes[j] != null) {
/*
* if (fieldNames[j].equals(
* "Bundesland")) { hashBL =
* DBKernel
* .myDBi.getHashMap("County");
* } else { hashBL = null; }
*/
lfdCol++;
if (fieldTypes[j].startsWith("VARCHAR(") || fieldTypes[j].startsWith("CHAR(") || fieldTypes[j].startsWith("BLOB(")
&& !tableName.equals("DateiSpeicher")) manageString(ps, psUpdate, lfdCol, row.getCell(j), hashBL);
else if (fieldTypes[j].equals("BOOLEAN")) manageBoolean(ps, psUpdate, lfdCol, row.getCell(j));
else if (fieldTypes[j].equals("INTEGER")) manageInteger(ps, psUpdate, lfdCol, row.getCell(j));
else if (fieldTypes[j].equals("BIGINT")) manageBigInteger(ps, psUpdate, lfdCol, row.getCell(j));
else if (fieldTypes[j].equals("DATE")) manageDate(ps, psUpdate, lfdCol, row.getCell(j));
else if (fieldTypes[j].equals("DOUBLE")) {
if (kzS[j] != null) {
lfdCol--;
//System.err.println(dbFieldnames[j] + "\t" + kzS[j]);
if (DBKernel.kzIsString(kzS[j])) kzVal[j] = manageString(null, null, lfdCol, row.getCell(j));
else if (DBKernel.kzIsBoolean(kzS[j])) kzVal[j] = manageBoolean(null, null, lfdCol, row.getCell(j));
else kzVal[j] = manageDouble(null, null, lfdCol, row.getCell(j));
} else {
manageDouble(ps, psUpdate, lfdCol, row.getCell(j));
}
} else System.err.println("Wasn hier los? Undefinierter Feldtyp???? ->\t" + fieldNames[j]);
} else if (myForeignTables[j] != null && ffieldTypes[j] != null) {
lfdColsForeign.put(myForeignTables[j], lfdColsForeign.get(myForeignTables[j]) + 1);
if (ffieldTypes[j].startsWith("VARCHAR(") || ffieldTypes[j].startsWith("CHAR(") || ffieldTypes[j].startsWith("BLOB(")
&& !tableName.equals("DateiSpeicher")) manageString(psForeign.get(myForeignTables[j]),
psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j), hashBL);
else if (ffieldTypes[j].equals("BOOLEAN")) manageBoolean(psForeign.get(myForeignTables[j]),
psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j));
else if (ffieldTypes[j].equals("INTEGER")) manageInteger(psForeign.get(myForeignTables[j]),
psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j));
else if (ffieldTypes[j].equals("BIGINT")) manageBigInteger(psForeign.get(myForeignTables[j]),
psForeignUpdate.get(myForeignTables[j]), lfdColsForeign.get(myForeignTables[j]), row.getCell(j));
else if (fieldTypes[j].equals("DATE")) manageDate(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]),
lfdColsForeign.get(myForeignTables[j]), row.getCell(j));
else if (ffieldTypes[j].equals("DOUBLE")) {
manageDouble(psForeign.get(myForeignTables[j]), psForeignUpdate.get(myForeignTables[j]),
lfdColsForeign.get(myForeignTables[j]), row.getCell(j));
} else System.err.println(fieldNames[j] + " Feldtype????");
} else if (fieldNames[j].equals("ID")) {
lastID = manageInteger(null, null, 0, row.getCell(j));
if (lastID != null) {
if (DBKernel.hasID(tableName, lastID.intValue())) {
psUpdate.setInt(idCol, lastID.intValue());
setID = true;
}
}
} else if (DBKernel.showHierarchic(tableName) && fieldNames[j].toLowerCase().endsWith("-code")) {
String code = manageString(psCodes[codesI], null, 1, row.getCell(j));
if (code != null && code.length() > 0) doCode[codesI] = true;
codesI++;
} else {
//System.out.println(fieldNames[j]);
}
}
try {
if (setID) {
psUpdate.execute();
} else {
if (ps.executeUpdate() > 0) {// execute()
lastID = DBKernel.getLastInsertedID(ps);
} else {
System.err.println("W");
}
}
numSuccess++;
if (lastID != null) {
for (int j = 0; j < numCols; j++) {
if (dbFieldnames[j] != null && kzVal[j] != null) {
DBKernel.insertDBL(myT.getTablename(), dbFieldnames[j], lastID, null, kzS[j], kzVal[j]);
}
}
for (codesI = 0; codesI < codeSql1.size(); codesI++) {
if (doCode[codesI]) {
psCodes[codesI].setInt(2, lastID);
try {
psCodes[codesI].execute();
numSuccess++;
} catch (SQLException e1) {
numFailed++;
System.err.println(psCodes[codesI]);
}
}
}
for (Map.Entry<MyTable, PreparedStatement> entry : psForeign.entrySet()) {
MyTable myT1 = entry.getKey();
MyTable[] foreignTs = myT.getForeignFields();
for (int ii = 0; ii < foreignTs.length; ii++) {
if (foreignTs[ii] != null && foreignTs[ii].equals(myT1)) {
if (psForeign.get(myT1).executeUpdate() > 0) { // INSERT
int lID = DBKernel.getLastInsertedID(psForeign.get(myT1));
// Das erstbeste Feld, das auf den Fremdtable verweist, wird mit dem Neueintrag verlinkt
DBKernel.sendRequest(
"UPDATE " + DBKernel.delimitL(tableName) + " SET " + DBKernel.delimitL(myT.getFieldNames()[ii]) + "=" + lID
+ " WHERE " + DBKernel.delimitL("ID") + "=" + lastID, false);
}
break;
}
}
}
/*
* for (int j=0;j<numCols;j++) {
* if (myForeignTables[j] !=
* null && ffieldTypes[j] !=
* null) { MyTable[] foreignTs =
* myT.getForeignFields(); for
* (int
* ii=0;ii<foreignTs.length;
* ii++) { if (foreignTs[ii] !=
* null && foreignTs[ii].equals(
* myForeignTables[j])) { if
* (psForeign
* .get(myForeignTables
* [j]).executeUpdate() > 0) {
* // INSERT int lID =
* DBKernel.getLastInsertedID
* (psForeign
* .get(myForeignTables[j]));
* DBKernel
* .sendRequest("UPDATE " +
* DBKernel.delimitL(tableName)
* + " SET " +
* DBKernel.delimitL(
* myT.getFieldNames()[ii]) +
* "=" + lID + " WHERE " +
* DBKernel.delimitL("ID") + "="
* + lastID, false); } break; }
* } } }
*/
}
} catch (Exception e1) {
numFailed++;
MyLogger.handleMessage(ps.toString());
MyLogger.handleException(e1);
}
}
if (progress != null) {
lfd++;
progress.setValue(lfd);
}
}
}
myT.doMNs();
if (progress != null) {
// Refreshen:
MyDBTable myDB = DBKernel.mainFrame.getMyList().getMyDBTable();
if (myDB.getActualTable() != null) {
String actTablename = myDB.getActualTable().getTablename();
if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) {
myDB.setTable(myDB.getActualTable());
}
}
MyDBTree myTR = DBKernel.mainFrame.getMyList().getMyDBTree();
if (myTR.getActualTable() != null) {
String actTablename = myTR.getActualTable().getTablename();
if (actTablename.equals(tableName) || actTablename.equals(DBKernel.getCodesName(tableName))) {
myTR.setTable(myTR.getActualTable());
}
}
}
} else {
System.err.println(tableName + " nicht in DB???");
}
}
if (progress != null) {
progress.setVisible(false);
}
String log = numSuccess + " erfolgreiche Importe.\n";
log += numFailed + " fehlgeschlagene Importe.\n";
if (unusedFields.length() > 0) log += "Unbekannte Felder: " + unusedFields.substring(1) + "\n";
if (showResults) {
InfoBox ib = new InfoBox(log, true, new Dimension(400, 300), null);
ib.setVisible(true);
} else {
System.out.println("GeneralXLSImporter (" + filename + "):\n" + log);
}
} catch (Exception e) {
MyLogger.handleException(e);
}
}
};
Thread thread = new Thread(runnable);
thread.start();
try {
thread.join();
} catch (InterruptedException e) {
MyLogger.handleException(e);
}
return "";
}
/*
* private String[] getTypes(String[] fieldNames, MyTable myT) { return
* getTypes(fieldNames, myT, null); }
*
* private String[] getTypes(String[] fieldNames, MyTable myT, String[]
* knownTypes) { String[] result; if (knownTypes == null ||
* knownTypes.length != fieldNames.length) result = new
* String[fieldNames.length]; else result = knownTypes; for (int
* i=0;i<fieldNames.length;i++) { result[i] = getType(fieldNames[i], myT); }
* return result; }
*/
private String getType(String fieldName, MyTable myT, boolean takecareofID) {
if (takecareofID && fieldName.equalsIgnoreCase("id")) return "INTEGER";
String result = null;
String[] tFieldNames = myT.getFieldNames();
String[] tFieldTypes = myT.getFieldTypes();
for (int j = 0; j < tFieldNames.length; j++) {
if (fieldName.equals(tFieldNames[j])) {
result = tFieldTypes[j];
break;
}
}
if (result == null) {
if (fieldName.equals("Kommentar")) result = "VARCHAR(";
else if (fieldName.equals("Guetescore")) result = "INTEGER";
else if (fieldName.equals("Geprueft")) result = "BOOLEAN";
}
return result;
}
private String getForeignTable(String fieldName, MyTable myT) {
String result = null;
String[] tFieldNames = myT.getFieldNames();
MyTable[] myFs = myT.getForeignFields();
if (myFs == null) return null;
for (int j = 0; j < tFieldNames.length; j++) {
if (fieldName.equals(tFieldNames[j])) {
if (myFs[j] != null) result = myFs[j].getTablename();
break;
}
}
/*
* if (result == null) { if (fieldName.equals("Kommentar")) result =
* "VARCHAR("; else if (fieldName.equals("Guetescore")) result =
* "INTEGER"; else if (fieldName.equals("Geprueft")) result = "BOOLEAN";
* }
*/
return result;
}
private String getDBFieldName(String fieldName, MyTable myT, boolean takecareofID) {
if (takecareofID && fieldName.equalsIgnoreCase("id")) return "ID";
String[] tFieldNames = myT.getFieldNames();
MyTable[] myFs = myT.getForeignFields();
for (int j = 0; j < tFieldNames.length; j++) {
if (fieldName.equals(tFieldNames[j])) {
return fieldName;
}
if (myFs != null && j < myFs.length && myFs[j] != null && myFs[j].getTablename().equals("DoubleKennzahlen")) {
String dblf = getDBLField(fieldName, tFieldNames[j]);
if (dblf != null) return dblf;
}
}
if (fieldName.equals("Kommentar") || fieldName.equals("Guetescore") || fieldName.equals("Geprueft")) return fieldName;
return null;
}
private String getDBLField(String xlsFieldName, String dbFieldName) {
if (xlsFieldName.startsWith(dbFieldName + "-")) {
if (xlsFieldName.startsWith(dbFieldName + "-Wert")) return dbFieldName;//"Einzelwert";
if (xlsFieldName.startsWith(dbFieldName + "-Wiederholungen")) return dbFieldName;//"Wiederholungen";
if (xlsFieldName.startsWith(dbFieldName + "-Exponent")) return dbFieldName;//"Exponent";
if (xlsFieldName.startsWith(dbFieldName + "-Wert_typ")) return dbFieldName;//"Wert_typ";
if (xlsFieldName.startsWith(dbFieldName + "-Minimum")) return dbFieldName;//"Minimum";
if (xlsFieldName.startsWith(dbFieldName + "-Maximum")) return dbFieldName;//"Maximum";
if (xlsFieldName.startsWith(dbFieldName + "-Standardabweichung")) return dbFieldName;//"Standardabweichung";
if (xlsFieldName.startsWith(dbFieldName + "-LCL95")) return dbFieldName;//"LCL95";
if (xlsFieldName.startsWith(dbFieldName + "-UCL95")) return dbFieldName;//"-UCL95";
if (xlsFieldName.startsWith(dbFieldName + "-Verteilung")) return dbFieldName;//"Verteilung";
if (xlsFieldName.startsWith(dbFieldName + "-Funktion (Zeit)")) return dbFieldName;//"Funktion (Zeit)";
if (xlsFieldName.startsWith(dbFieldName + "-Funktion (x)")) return dbFieldName;//"Funktion (x)";
if (xlsFieldName.startsWith(dbFieldName + "-x")) return dbFieldName;//"Funktion (x)";
if (xlsFieldName.startsWith(dbFieldName + "-Undefiniert (n.d.)")) return dbFieldName;//"Undefiniert (n.d.)";
} else if (xlsFieldName.equals(dbFieldName)) {
return dbFieldName;//"Einzelwert";
}
return null;
}
private String getKZ(String xlsFieldName, String dbFieldName) {
if (xlsFieldName.equals(dbFieldName)) {
return "Wert"; //"Einzelwert";
} else if (xlsFieldName.startsWith(dbFieldName + "-")) {
return xlsFieldName.substring(xlsFieldName.lastIndexOf("-") + 1);
/*
* if (xlsFieldName.startsWith(dbFieldName + "-Einzelwert")) return
* "Einzelwert"; if (xlsFieldName.startsWith(dbFieldName +
* "-Wiederholungen")) return "Wiederholungen"; if
* (xlsFieldName.startsWith(dbFieldName + "-Exponent")) return
* "Exponent"; if (xlsFieldName.startsWith(dbFieldName +
* "-Wert_typ")) return "Wert_typ"; if
* (xlsFieldName.startsWith(dbFieldName + "-Minimum")) return
* "Minimum"; if (xlsFieldName.startsWith(dbFieldName + "-Maximum"))
* return "Maximum"; if (xlsFieldName.startsWith(dbFieldName +
* "-Standardabweichung")) return "Standardabweichung"; if
* (xlsFieldName.startsWith(dbFieldName + "-LCL95")) return "LCL95";
* if (xlsFieldName.startsWith(dbFieldName + "-UCL95")) return
* "UCL95"; if (xlsFieldName.startsWith(dbFieldName +
* "-Verteilung")) return "Verteilung"; if
* (xlsFieldName.startsWith(dbFieldName + "-Funktion (Zeit)"))
* return "Funktion (Zeit)"; if (xlsFieldName.startsWith(dbFieldName
* + "-Funktion (x)")) return "Funktion (x)"; if
* (xlsFieldName.startsWith(dbFieldName + "-x")) return "x"; if
* (xlsFieldName.startsWith(dbFieldName + "-Undefiniert (n.d.)"))
* return "Undefiniert (n.d.)";
*/
}
return null;
}
private int foreignFieldExists(String fieldName, MyTable myT) {
MyTable[] foreignTs = myT.getForeignFields();
for (int i = 0; i < foreignTs.length; i++) {
if (foreignTs[i] != null) {
if (getDBFieldName(fieldName, foreignTs[i], false) != null) return i;
}
}
return -1;
}
private Integer manageInteger(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
Integer result = null;
if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
if (cell.getStringCellValue().trim().length() > 0) {
result = new Integer(cell.getStringCellValue());
if (ps != null) ps.setInt(lfdCol, result);
if (psUpdate != null) psUpdate.setInt(lfdCol, result);
return result;
}
} else {
result = new Integer((int) cell.getNumericCellValue());
if (ps != null) ps.setInt(lfdCol, result);
if (psUpdate != null) psUpdate.setInt(lfdCol, result);
return result;
}
if (ps != null) ps.setNull(lfdCol, java.sql.Types.INTEGER);
if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.INTEGER);
return result;
}
private Date manageDate(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
Date result = null;
if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
if (cell.getStringCellValue().trim().length() > 0) {
DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); // 2012-06-01 hh:mm:ss
java.util.Date parsedUtilDate;
try {
parsedUtilDate = formater.parse(cell.getStringCellValue());
result = new java.sql.Date(parsedUtilDate.getTime());
} catch (ParseException e) {
e.printStackTrace();
}
if (result != null) {
if (ps != null) ps.setDate(lfdCol, result);
if (psUpdate != null) psUpdate.setDate(lfdCol, result);
return result;
}
}
} else {
result = new Date(cell.getDateCellValue().getTime());
if (ps != null) ps.setDate(lfdCol, result);
if (psUpdate != null) psUpdate.setDate(lfdCol, result);
return result;
}
if (ps != null) ps.setNull(lfdCol, java.sql.Types.DATE);
if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.DATE);
return result;
}
private Long manageBigInteger(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
Long result = null;
if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
if (cell.getStringCellValue().trim().length() > 0) {
result = new Long(cell.getStringCellValue());
if (ps != null) ps.setLong(lfdCol, result);
if (psUpdate != null) psUpdate.setLong(lfdCol, result);
return result;
}
} else {
result = new Long((long) cell.getNumericCellValue());
if (ps != null) ps.setLong(lfdCol, result);
if (psUpdate != null) psUpdate.setLong(lfdCol, result);
return result;
}
if (ps != null) ps.setNull(lfdCol, java.sql.Types.BIGINT);
if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.BIGINT);
return result;
}
private Double manageDouble(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
Double dbl = null;
if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
if (cell.getStringCellValue().trim().length() > 0 && !cell.getStringCellValue().equalsIgnoreCase("null")) {
try {
dbl = Double.parseDouble(cell.getStringCellValue());
ps.setDouble(lfdCol, dbl);
psUpdate.setDouble(lfdCol, dbl);
return dbl;
} catch (Exception e) {
}
}
} else {
dbl = cell.getNumericCellValue();
try {
ps.setDouble(lfdCol, dbl);
psUpdate.setDouble(lfdCol, dbl);
} catch (Exception e) {
}
return dbl;
}
try {
ps.setNull(lfdCol, java.sql.Types.DOUBLE);
psUpdate.setNull(lfdCol, java.sql.Types.DOUBLE);
} catch (Exception e) {
}
return dbl;
}
private String manageString(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
return manageString(ps, psUpdate, lfdCol, cell, null);
}
private String manageString(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell, LinkedHashMap<Object, String> hashBL) throws SQLException {
String result = null;
if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
if (ps != null) ps.setNull(lfdCol, java.sql.Types.VARCHAR);
if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.VARCHAR);
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
cell.setCellType(HSSFCell.CELL_TYPE_STRING);
result = cell.getStringCellValue().trim();
if (ps != null) ps.setString(lfdCol, result);
if (psUpdate != null) psUpdate.setString(lfdCol, result);
} else {
result = cell.getStringCellValue().trim();
if (hashBL != null) {
int val, min = 1000;
String newResult = result;
for (Object o : hashBL.keySet()) {
val = Levenshtein.LD(result, o.toString());
if (val < min) {
min = val;
newResult = o.toString();
}
}
if (!newResult.equals(result)) {
if (DBKernel.debug) MyLogger.handleMessage("Levenshtein - not equal ... " + newResult + "\t" + result);
result = newResult;
}
}
if (ps != null) ps.setString(lfdCol, result);
if (psUpdate != null) psUpdate.setString(lfdCol, result);
}
//ps.setNull(lfdCol, java.sql.Types.VARCHAR);
if (result != null && result.equals("?@lufa-itl.de")) {
MyLogger.handleMessage(result);
}
return result;
}
private Boolean manageBoolean(PreparedStatement ps, PreparedStatement psUpdate, int lfdCol, HSSFCell cell) throws SQLException {
Boolean result = null;
if (cell == null || cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
if (ps != null) ps.setNull(lfdCol, java.sql.Types.BOOLEAN);
if (psUpdate != null) psUpdate.setNull(lfdCol, java.sql.Types.BOOLEAN);
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
result = cell.getNumericCellValue() != 0;
if (ps != null) ps.setBoolean(lfdCol, result);
if (psUpdate != null) psUpdate.setBoolean(lfdCol, result);
} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
result = cell.getStringCellValue().equalsIgnoreCase("true");
if (ps != null) ps.setBoolean(lfdCol, result);
if (psUpdate != null) psUpdate.setBoolean(lfdCol, result);
} else {
result = cell.getBooleanCellValue();
if (ps != null) ps.setBoolean(lfdCol, result);
if (psUpdate != null) psUpdate.setBoolean(lfdCol, result);
}
//ps.setNull(lfdCol, java.sql.Types.BOOLEAN);
return result;
}
}