HashMap[] treeIndexes = new HashMap[langsVO.size()];
if (processVO.getProgressiveHIE02()!=null) {
// retrieve the whole hierarchy currently defined in the database, for each language...
for(int i=0;i<langsVO.size();i++) {
LanguageVO vo = (LanguageVO)langsVO.get(i);
DefaultTreeModel model = CompanyHierarchyUtil.loadHierarchy(processVO.getCompanyCodeSys01SYS23(),processVO.getProgressiveHIE02(),vo.getLanguageCodeSYS09(),username);
treeIndexes[i] = new HashMap();
indexingTree((DefaultMutableTreeNode)model.getRoot(),treeIndexes[i],processVO.getLevelsSepSYS23(),"");
}
}
// fixed fields, to add as insert values and as where values in update clause...
ArrayList[] params = new ArrayList[impVO.getTableNames().length];
String tableName = null;
int hierarchyLevelIndex = -1;
for(int k=0;k<impVO.getTableNames().length;k++) {
tableName = impVO.getTableNames()[k];
params[k] = new ArrayList();
if (impVO.isSupportsCompanyCode()) {
params[k].add(processVO.getCompanyCodeSys01SYS23());
}
if (impVO.getHierarchyField()!=null &&
getFieldName(impVO.getHierarchyField(),tableName) != null) {
params[k].add(null);
hierarchyLevelIndex = params[k].size()-1;
}
if (impVO.getSubTypeField()!=null &&
getFieldName(impVO.getSubTypeField(),tableName) != null) {
params[k].add(processVO.getSubTypeValueSYS23());
}
if (impVO.getSubTypeField2()!=null &&
getFieldName(impVO.getSubTypeField2(),tableName) != null) {
params[k].add(processVO.getSubTypeValue2SYS23());
}
}
insPstmt = new PreparedStatement[impVO.getTableNames().length];
updPstmt = new PreparedStatement[impVO.getTableNames().length];
selPstmt = new PreparedStatement[impVO.getTableNames().length];
// prepare SQL for insert ops...
String fieldName = null;
String insSQL = null;
ETLProcessFieldVO vo = null;
HashSet alreadyAdded = new HashSet();
int count = 0;
for(int k=0;k<impVO.getTableNames().length;k++) {
alreadyAdded.clear();
tableName = impVO.getTableNames()[k];
insSQL = "INSERT INTO " + tableName + "(CREATE_USER,CREATE_DATE,";
count = 0;
if (impVO.isSupportsCompanyCode()) {
insSQL += "COMPANY_CODE_SYS01,";
count++;
}
if (impVO.getHierarchyField()!=null &&
getFieldName(impVO.getHierarchyField(),tableName) != null &&
!alreadyAdded.contains(getFieldName(impVO.getHierarchyField(),tableName))) {
insSQL += getFieldName(impVO.getHierarchyField(),tableName)+",";
alreadyAdded.add(getFieldName(impVO.getHierarchyField(),tableName));
count++;
}
if (impVO.getSubTypeField()!=null &&
getFieldName(impVO.getSubTypeField(),tableName) != null &&
!alreadyAdded.contains(getFieldName(impVO.getSubTypeField(),tableName))) {
insSQL += getFieldName(impVO.getSubTypeField(),tableName) + ",";
alreadyAdded.add(getFieldName(impVO.getSubTypeField(),tableName));
count++;
}
if (impVO.getSubTypeField2()!=null &&
getFieldName(impVO.getSubTypeField2(),tableName) != null &&
!alreadyAdded.contains(getFieldName(impVO.getSubTypeField2(),tableName))) {
insSQL += getFieldName(impVO.getSubTypeField2(),tableName) + ",";
alreadyAdded.add(getFieldName(impVO.getSubTypeField2(),tableName));
count++;
}
if (impVO.getProgressiveFieldName()!=null &&
getFieldName(impVO.getProgressiveFieldName(),tableName)!=null &&
!alreadyAdded.contains(getFieldName(impVO.getProgressiveFieldName(),tableName))) {
insSQL += getFieldName(impVO.getProgressiveFieldName(),tableName) + ",";
alreadyAdded.add(getFieldName(impVO.getProgressiveFieldName(),tableName));
count++;
}
Iterator it = fieldsToAdd.keySet().iterator();
while(it.hasNext()) {
fieldName = it.next().toString();
if (getFieldName(fieldName,tableName)!=null &&
!alreadyAdded.contains(getFieldName(fieldName,tableName))) {
insSQL += getFieldName(fieldName, tableName) + ",";
alreadyAdded.add(getFieldName(fieldName,tableName));
count++;
}
}
for (int i = 0; i < fieldsVO.size(); i++) {
vo = (ETLProcessFieldVO) fieldsVO.get(i);
if (getFieldName(vo.getFieldNameSYS24(),tableName)!=null &&
!alreadyAdded.contains(getFieldName(vo.getFieldNameSYS24(),tableName))) {
insSQL += getFieldName(vo.getFieldNameSYS24(), tableName) + ",";
alreadyAdded.add(getFieldName(vo.getFieldNameSYS24(),tableName));
count++;
}
}
it = impVO.getDefaultFields().keySet().iterator();
while (it.hasNext()) {
fieldName = it.next().toString();
if (getFieldName(fieldName,tableName)!=null) {
insSQL += getFieldName(fieldName, tableName) + ",";
count++;
}
}
insSQL = insSQL.substring(0, insSQL.length() - 1);
insSQL += ") VALUES(?,?,";
for(int i=0;i<count;i++)
insSQL += "?,";
insSQL = insSQL.substring(0, insSQL.length() - 1);
insSQL += ")";
insPstmt[k] = conn.prepareStatement(insSQL);
} // end for on tables...
// prepare SQL for update ops...
String updSQL = null;
Iterator it = null;
for(int k=0;k<impVO.getTableNames().length;k++) {
tableName = impVO.getTableNames()[k];
updSQL = "UPDATE " + tableName + " SET LAST_UPDATE_USER=?,LAST_UPDATE_DATE=?,";
alreadyAdded.clear();
for (int i = 0; i < fieldsVO.size(); i++) {
vo = (ETLProcessFieldVO) fieldsVO.get(i);
if (getFieldName(vo.getFieldNameSYS24(),tableName)!=null &&
!alreadyAdded.contains(getFieldName(vo.getFieldNameSYS24(), tableName))) {
updSQL += getFieldName(vo.getFieldNameSYS24(), tableName) + "=?,";
alreadyAdded.add(getFieldName(vo.getFieldNameSYS24(), tableName));
}
}
if (impVO.getHierarchyField()!=null &&
getFieldName(impVO.getHierarchyField(),tableName) != null &&
!alreadyAdded.contains(getFieldName(impVO.getHierarchyField(),tableName))) {
updSQL += getFieldName(impVO.getHierarchyField(),tableName)+"=?,";
alreadyAdded.add(getFieldName(impVO.getHierarchyField(),tableName));
}
updSQL = updSQL.substring(0, updSQL.length() - 1);
updSQL += " WHERE ";
if (impVO.isSupportsCompanyCode()) {
updSQL += "COMPANY_CODE_SYS01=? and ";
}
// if (getFieldName(impVO.getHierarchyField(),tableName) != null &&
// !alreadyAdded.contains(getFieldName(impVO.getHierarchyField(),tableName))) {
// updSQL += getFieldName(impVO.getHierarchyField(),tableName)+"=? and ";
// alreadyAdded.add(getFieldName(impVO.getHierarchyField(),tableName));
// }
if (getFieldName(impVO.getSubTypeField(),tableName) != null &&
!alreadyAdded.contains(getFieldName(impVO.getSubTypeField(),tableName))) {
updSQL += getFieldName(impVO.getSubTypeField(),tableName) + "=? and ";
alreadyAdded.add(getFieldName(impVO.getSubTypeField(),tableName));
}
if (getFieldName(impVO.getSubTypeField2(),tableName) != null &&
!alreadyAdded.contains(getFieldName(impVO.getSubTypeField2(),tableName))) {
updSQL += getFieldName(impVO.getSubTypeField2(),tableName) + "=? and ";
alreadyAdded.add(getFieldName(impVO.getSubTypeField2(),tableName));
}
it = impVO.getPkFields().iterator();
while (it.hasNext()) {
fieldName = it.next().toString();
if (getFieldName(fieldName,tableName)!=null)
updSQL += getFieldName(fieldName,tableName)+"=? and ";
}
updSQL = updSQL.substring(0, updSQL.length() - 4);
updPstmt[k] = conn.prepareStatement(updSQL);
} // end for on tables...
// prepare SQL for insert description ops...
String insSYS10 = "INSERT INTO SYS10_COMPANY_TRANSLATIONS(PROGRESSIVE,LANGUAGE_CODE,DESCRIPTION,COMPANY_CODE_SYS01,CREATE_USER,CREATE_DATE) VALUES(?,?,?,?,?,?)";
insSYS10Pstmt = conn.prepareStatement(insSYS10);
// prepare SQL for update description ops...
String updSYS10 = "UPDATE SYS10_COMPANY_TRANSLATIONS SET DESCRIPTION=?,LAST_UPDATE_USER=?,LAST_UPDATE_DATE=? WHERE COMPANY_CODE_SYS01=? and PROGRESSIVE=? and LANGUAGE_CODE=?";
updSYS10Pstmt = conn.prepareStatement(updSYS10);
// prepare SQL for select ops...
String selSQL = null;
for(int k=0;k<impVO.getTableNames().length;k++) {
alreadyAdded.clear();
tableName = impVO.getTableNames()[k];
selSQL = "SELECT ";
it = impVO.getProgressiveSys10Fields().keySet().iterator();
while (it.hasNext()) {
fieldName = it.next().toString();
if (getFieldName(fieldName,tableName)!=null &&
!alreadyAdded.contains(getFieldName(fieldName,tableName))) {
selSQL += getFieldName(fieldName, tableName) + ",";
alreadyAdded.add(getFieldName(fieldName,tableName));
}
}
if (selSQL.length()>7) {
selSQL = selSQL.substring(0,selSQL.length()-1);
}
else {
selSQL += "*";
}
selSQL += " FROM "+tableName+" WHERE ";
if (impVO.isSupportsCompanyCode()) {
selSQL += "COMPANY_CODE_SYS01=? and ";
}
// if (getFieldName(impVO.getHierarchyField(),tableName) != null) {
// selSQL += getFieldName(impVO.getHierarchyField(),tableName)+"=? and ";
// }
if (getFieldName(impVO.getSubTypeField(),tableName) != null) {
selSQL += getFieldName(impVO.getSubTypeField(),tableName) + "=? and ";
}
if (getFieldName(impVO.getSubTypeField2(),tableName) != null) {
selSQL += getFieldName(impVO.getSubTypeField2(),tableName) + "=? and ";
}
it = impVO.getPkFields().iterator();
while (it.hasNext()) {
fieldName = it.next().toString();
if (getFieldName(fieldName,tableName)!=null)
selSQL += "("+getFieldName(fieldName,tableName)+"=? or "+getFieldName(fieldName,tableName)+" is null) and ";
}
selSQL = selSQL.substring(0, selSQL.length() - 4);
selPstmt[k] = conn.prepareStatement(selSQL);
} // end for on tables...
// indexing fields as <FieldEntry,Integer index>
HashMap fields = new HashMap();
int pos = 0;
for(int i=0;i<fieldsVO.size();i++) {
vo = (ETLProcessFieldVO)fieldsVO.get(i);
fields.put(
new FieldEntry(vo.getLanguageCodeSYS24(),vo.getFieldNameSYS24()),
new Integer(pos++)
);
}
LanguageVO langVO = null;
if (processVO.getProgressiveHIE02()!=null) {
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO) langsVO.get(i);
fields.put(
new FieldEntry(vo.getLanguageCodeSYS24(),impVO.getHierarchyField()),
new Integer(pos++)
);
}
}
// indexing fields as <field name,Class type>
HashMap classes = new HashMap();
for(int i=0;i<impVO.getFields().length;i++) {
classes.put(
impVO.getFields()[i],
impVO.getFieldsType()[i]
);
}
it = impVO.getProgressiveSys10Fields().keySet().iterator();
while(it.hasNext()) {
fieldName = it.next().toString();
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO) langsVO.get(i);
classes.put(
fieldName,
String.class//BigDecimal.class
);
}
}
if (processVO.getProgressiveHIE02()!=null) {
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO) langsVO.get(i);
classes.put(
impVO.getHierarchyField(),
String.class//BigDecimal.class
);
}
}
if (processVO.getFileFormatSYS23().equals(ApplicationConsts.FILE_FORMAT_TXT))
proc = new TXTRowProcessor();
else if (processVO.getFileFormatSYS23().equals(ApplicationConsts.FILE_FORMAT_CSV1))
proc = new CSVRowProcessor(";");
else if (processVO.getFileFormatSYS23().equals(ApplicationConsts.FILE_FORMAT_CSV2))
proc = new CSVRowProcessor(",");
else if (processVO.getFileFormatSYS23().equals(ApplicationConsts.FILE_FORMAT_XLS))
proc = new XLSRowProcessor();
InputStream in = null;
if (processVO.getLocalFile() != null &&
processVO.getLocalFile().length > 0)
in = new ByteArrayInputStream(processVO.getLocalFile());
else {
File f = new File(processVO.getFilenameSYS23());
if (!f.exists())
throw new Exception("File non exists");
in = new BufferedInputStream(new FileInputStream(f));
}
proc.openFile(in);
Object[] row = null;
pos = 1;
String progFieldName = null;
BigDecimal progressiveSYS10 = null;
long rows = 0;
long rowsInserted = 0;
long rowsUpdated = 0;
FieldEntry pk = null;
String[] levelsDesc = null;
BigDecimal progressiveHIE01 = null;
int index = -1;
Object value = null;
BigDecimal progressive = null;
Integer indexInt = null;
long notYetCommited = 0;
while((row=proc.getNextRow(fieldsVO,impVO,classes))!=null) {
rows++;
for(int k=0;k<impVO.getTableNames().length;k++) {
tableName = impVO.getTableNames()[k];
// check if record exists...
pos = 1;
if (impVO.isSupportsCompanyCode()) {
selPstmt[k].setObject(pos++,processVO.getCompanyCodeSys01SYS23());
}
if (getFieldName(impVO.getSubTypeField(),tableName) != null) {
selPstmt[k].setObject(pos++,processVO.getSubTypeValueSYS23());
}
if (getFieldName(impVO.getSubTypeField2(),tableName) != null) {
selPstmt[k].setObject(pos++,processVO.getSubTypeValue2SYS23());
}
// for(int i=0;i<params[k].size();i++)
// selPstmt[k].setObject(pos++,params[k].get(i));
it = impVO.getPkFields().iterator();
while (it.hasNext()) {
fieldName = it.next().toString();
if (getFieldName(fieldName,tableName)!=null)
selPstmt[k].setObject(pos++,row[((Integer)fields.get(new FieldEntry(ApplicationConsts.JOLLY,fieldName))).intValue()]);
}
rset = selPstmt[k].executeQuery();
if (rset.next()) {
// record must be updated...
if (impVO.getProgressiveSys10Fields().size()>0) {
// record already exists: fetch progressives...
it = impVO.getProgressiveSys10Fields().keySet().iterator();
pos = 1;
while(it.hasNext()) {
progFieldName = it.next().toString();
progressiveSYS10 = rset.getBigDecimal(pos++);
// update record in SYS10...
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO)langsVO.get(i);
pk = new FieldEntry(langVO.getLanguageCodeSYS09(),progFieldName);
indexInt = (Integer)fields.get(pk);
if (indexInt==null)
// in case of not mandatory progressiveSYS10 (e.g. additional description...)
// which has not been mapped in SYS24...
continue;
index = indexInt.intValue();
updSYS10Pstmt.setObject(1,row[index]);
updSYS10Pstmt.setString(2,username); pos++;
updSYS10Pstmt.setTimestamp(3,new java.sql.Timestamp(System.currentTimeMillis())); pos++;
updSYS10Pstmt.setString(4,processVO.getCompanyCodeSys01SYS23());
updSYS10Pstmt.setBigDecimal(5,progressiveSYS10);
updSYS10Pstmt.setString(6,langVO.getLanguageCodeSYS09());
updSYS10Pstmt.execute();
row[index] = progressiveSYS10;
}
}
}
rset.close();
if (processVO.getProgressiveHIE02()!=null) {
// retrieve hierarchy level...
langVO = (LanguageVO)langsVO.get(0);
pk = new FieldEntry(langVO.getLanguageCodeSYS09(),impVO.getHierarchyField());
levelsDesc = new String[langsVO.size()];
levelsDesc[0] = (String)row[((Integer)fields.get(pk)).intValue()];
progressiveHIE01 = (BigDecimal)treeIndexes[0].get(levelsDesc[0]);
if (progressiveHIE01==null) {
// the levels do not exist yet: must be inserted...
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO)langsVO.get(i);
pk = new FieldEntry(langVO.getLanguageCodeSYS09(),impVO.getHierarchyField());
levelsDesc[i] = (String)row[((Integer)fields.get(pk)).intValue()];
}
progressiveHIE01 = insertLevels(
conn,
langVO.getLanguageCodeSYS09(),
username,
langsVO,
levelsDesc,
processVO.getLevelsSepSYS23(),
treeIndexes,
processVO.getProgressiveHIE02(),
processVO.getCompanyCodeSys01SYS23()
);
}
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO)langsVO.get(i);
pk = new FieldEntry(langVO.getLanguageCodeSYS09(),impVO.getHierarchyField());
row[((Integer)fields.get(pk)).intValue()] = progressiveHIE01;
}
params[k].set(hierarchyLevelIndex,progressiveHIE01);
}
// update main record...
pos = 1;
// for(int i=0;i<row.length;i++)
// updPstmt[k].setObject(pos++,row[i]);
updPstmt[k].setString(pos,username); pos++;
updPstmt[k].setTimestamp(pos,new java.sql.Timestamp(System.currentTimeMillis())); pos++;
alreadyAdded.clear();
for (int i = 0; i < fieldsVO.size(); i++) {
vo = (ETLProcessFieldVO) fieldsVO.get(i);
if (getFieldName(vo.getFieldNameSYS24(),tableName)!=null &&
!alreadyAdded.contains(getFieldName(vo.getFieldNameSYS24(), tableName))) {
updPstmt[k].setObject(pos++,row[i]);
alreadyAdded.add(getFieldName(vo.getFieldNameSYS24(), tableName));
}
}
if (impVO.getHierarchyField()!=null &&
getFieldName(impVO.getHierarchyField(),tableName) != null &&
!alreadyAdded.contains(getFieldName(impVO.getHierarchyField(),tableName))) {
updPstmt[k].setObject(pos++,row[((Integer)fields.get(new FieldEntry(ApplicationConsts.JOLLY,impVO.getHierarchyField()))).intValue()]);
alreadyAdded.add(getFieldName(impVO.getHierarchyField(),tableName));
}
if (impVO.isSupportsCompanyCode()) {
updPstmt[k].setObject(pos++,processVO.getCompanyCodeSys01SYS23());
}
if (getFieldName(impVO.getSubTypeField(),tableName) != null) {
updPstmt[k].setObject(pos++,processVO.getSubTypeValueSYS23());
}
if (getFieldName(impVO.getSubTypeField2(),tableName) != null) {
updPstmt[k].setObject(pos++,processVO.getSubTypeValue2SYS23());
}
// for(int i=0;i<params[k].size();i++)
// updPstmt[k].setObject(pos++,params[k].get(i));
it = impVO.getPkFields().iterator();
while (it.hasNext()) {
fieldName = it.next().toString();
if (getFieldName(fieldName,tableName)!=null)
updPstmt[k].setObject(pos++,row[((Integer)fields.get(new FieldEntry(ApplicationConsts.JOLLY,fieldName))).intValue()]);
}
updPstmt[k].execute();
rowsUpdated++;
notYetCommited++;
}
else {
// record does not exist yet...
rset.close();
if (impVO.getProgressiveSys10Fields().size()>0) {
// create progressives and insert records in SYS10...
it = impVO.getProgressiveSys10Fields().keySet().iterator();
pos = 1;
while(it.hasNext()) {
progFieldName = it.next().toString();
progressiveSYS10 = CompanyProgressiveUtils.getInternalProgressive(
processVO.getCompanyCodeSys01SYS23(), // ??? defCompanyCodeSys01SYS03,
"SYS10_COMPANY_TRANSLATIONS",
"PROGRESSIVE",
conn
);
// insert record in SYS10...
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO)langsVO.get(i);
pk = new FieldEntry(langVO.getLanguageCodeSYS09(),progFieldName);
indexInt = (Integer)fields.get(pk);
if (indexInt==null)
// in case of not mandatory progressiveSYS10 (e.g. additional description...)
// which has not been mapped in SYS24...
continue;
index = indexInt.intValue();
insSYS10Pstmt.setBigDecimal(1,progressiveSYS10);
insSYS10Pstmt.setString(2,langVO.getLanguageCodeSYS09());
insSYS10Pstmt.setObject(3,row[index]);
insSYS10Pstmt.setString(4,processVO.getCompanyCodeSys01SYS23());
insSYS10Pstmt.setString(5,username);
insSYS10Pstmt.setTimestamp(6,new java.sql.Timestamp(System.currentTimeMillis()));
insSYS10Pstmt.execute();
row[index] = progressiveSYS10;
}
}
}
if (processVO.getProgressiveHIE02()!=null) {
// retrieve hierarchy level...
langVO = (LanguageVO)langsVO.get(0);
pk = new FieldEntry(langVO.getLanguageCodeSYS09(),impVO.getHierarchyField());
levelsDesc = new String[langsVO.size()];
levelsDesc[0] = (String)row[((Integer)fields.get(pk)).intValue()];
progressiveHIE01 = (BigDecimal)treeIndexes[0].get(levelsDesc[0]);
if (progressiveHIE01==null) {
// the levels do not exist yet: must be inserted...
for(int i=0;i<langsVO.size();i++) {
langVO = (LanguageVO)langsVO.get(i);
pk = new FieldEntry(langVO.getLanguageCodeSYS09(),impVO.getHierarchyField());
levelsDesc[i] = (String)row[((Integer)fields.get(pk)).intValue()];
}
progressiveHIE01 = insertLevels(
conn,
langVO.getLanguageCodeSYS09(),
username,
langsVO,
levelsDesc,
processVO.getLevelsSepSYS23(),
treeIndexes,