import java.io.BufferedWriter; import java.io.FileOutputStream; import java.io.IOException; import java.io.OutputStreamWriter; import java.math.BigDecimal; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.sql.Statement; import java.text.SimpleDateFormat; import java.util.ArrayList; import com.sap.conn.jco.JCoFunction; import com.sap.conn.jco.JCoTable; class CFCAFunctionHandler { private JCoFunction jFunction = null; //private final String remote_prefix = "sr1."; //private final String local_prefix = "sr2."; private final String remote_prefix = ""; private final String local_prefix = ""; //AND ( HEADER.CREATED_BY = 'PURCHMANAGER' OR HEADER.CREATED_BY = 'KAPOORK' ) //AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) private String user1 = "PURCHASER_US"; private String user2 = "KAPOORK"; public CFCAFunctionHandler(JCoFunction function) { try { jFunction = function; System.out.println("CFCAFunctionHandler"); } catch (Exception ex) { System.out.println("Initialized Error!"); System.exit(-1); } } public void Dowork() { if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_GET_RFX_DETAIL)) RetrieveRFxDetailInformation(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_UPDATE_QUTO_PRICE)) UpdateQuotationPrice(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_GET_RFX_LIST)) RetrieveRFxListInfo(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_GET_QUOT_LIST)) RetrieveQuotListInfo(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_GET_QUOT_ITEM_LIST)) RetrieveQuotItemListInfo(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_GET_QUOT_ITEM_DETAIL)) RetrieveQuotItemDetailInfo(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_SYNC_FROM_REMOTE)) TableSyncRead(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_SYNC_TO_REMOTE)) TableSyncWrite(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_CLEAR_LOCAL_DATA)) ClearLocalData(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_SYNC_RFX)) RFxSyncRead(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_WRITE_RFX)) RFxSyncWrite(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_RESPONSE_WRITE)) rfxResponseWrite(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_PUBLISH)) rfxPublish(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_BRIEF_READ)) rfxBriefRead(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_BRIEF_WRITE)) rfxBriefWrite(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_ITEM_READ)) rfxItemRead(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_BRIEF_ID_READ)) rfxBriefIDRead(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_SUCCEED_READ)) rfxSucceedRead(); else if ( jFunction.getName().equalsIgnoreCase(CFCAServer.FUNC_RFX_SUCCEED_DETAIL)) rfxSucceedDetail(); } public void RetrieveRFxListInfo() { try { //Get input Parameter JCoTable cg_table = jFunction.getChangingParameterList().getTable("OUT_RFX_LIST"); //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; ps = con.prepareStatement("select GUID, OBJECT_ID, DESCRIPTION from sr1.CRMD_ORDERADM_H where CLIENT = ? and OBJECT_TYPE = ?"); ps.setString(1, "800"); ps.setString(2, "BUS2200"); ResultSet queryResult = ps.executeQuery(); String lv_guid = ""; String lv_description = ""; String lv_object_id = ""; int count = 0; while (queryResult.next()) { count++; lv_guid = queryResult.getString(1); lv_object_id = queryResult.getString(2); lv_description = queryResult.getString(3); System.out.println("GUID:" + lv_guid); System.out.println("Description:" + lv_description); System.out.println("Object_ID:" + lv_object_id); cg_table.appendRow(); cg_table.setValue("OBJECT_ID", lv_object_id); cg_table.setValue("GUID", lv_guid); cg_table.setValue("DESCRIPTION", lv_description); } jFunction.getExportParameterList().setValue("OUT_RFX_COUNT", count); jFunction.getChangingParameterList().setValue("OUT_RFX_LIST",cg_table); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void RetrieveQuotListInfo() { try { //Get input Parameter //JCoTable cg_table = jFunction.getChangingParameterList().getTable("OUT_RFX_LIST"); JCoTable cg_table = jFunction.getExportParameterList().getTable("OUT_QUOT_LIST"); //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; ps = con.prepareStatement("select GUID, OBJECT_ID, DESCRIPTION from sr1.CRMD_ORDERADM_H where CLIENT = ? and OBJECT_TYPE = ?"); ps.setString(1, "800"); ps.setString(2, "BUS2202"); ResultSet queryResult = ps.executeQuery(); String lv_guid = ""; String lv_description = ""; String lv_object_id = ""; int count = 0; while (queryResult.next()) { count++; lv_guid = queryResult.getString(1); lv_object_id = queryResult.getString(2); lv_description = queryResult.getString(3); System.out.println("GUID:" + lv_guid); System.out.println("Description:" + lv_description); System.out.println("Object_ID:" + lv_object_id); cg_table.appendRow(); cg_table.setValue("OBJECT_ID", lv_object_id); cg_table.setValue("GUID", lv_guid); cg_table.setValue("DESCRIPTION", lv_description); } jFunction.getExportParameterList().setValue("OUT_QUOT_COUNT", count); jFunction.getExportParameterList().setValue("OUT_QUOT_LIST",cg_table); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void RetrieveQuotItemListInfo() { try { //Get input Parameter String lv_quot_id = jFunction.getImportParameterList().getString("IN_QUOT_ID"); JCoTable cg_table = jFunction.getExportParameterList().getTable("OUT_QUOT_ITEM_LIST"); //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; //Retrieve GUID for Quotation Header ps = con.prepareStatement("select GUID from sr1.CRMD_ORDERADM_H where CLIENT = ? and OBJECT_ID = ?"); ps.setString(1, "800"); ps.setString(2, lv_quot_id); ResultSet queryResult = ps.executeQuery(); byte[] lv_guid = new byte[0]; if (queryResult.next()) { lv_guid = queryResult.getBytes(1); } queryResult.close(); ps = con.prepareStatement("select GUID, DESCRIPTION from sr1.CRMD_ORDERADM_I where CLIENT = ? and HEADER = ?"); ps.setString(1, "800"); //InputStream lv_bytes_guid = new ByteArrayInputStream(lv_guid); //ps.setBinaryStream(2, lv_bytes_guid, 16); ps.setBytes(2, lv_guid); queryResult = ps.executeQuery(); String lv_item_guid = ""; String lv_product_id = ""; int count = 0; while (queryResult.next()) { count++; lv_item_guid = queryResult.getString(1); lv_product_id = queryResult.getString(2); System.out.println("GUID:" + lv_item_guid); System.out.println("Description:" + lv_product_id); cg_table.appendRow(); cg_table.setValue("DESCRIPTION", lv_product_id); cg_table.setValue("GUID", lv_item_guid); } jFunction.getExportParameterList().setValue("OUT_QUOT_ITEM_COUNT", count); jFunction.getExportParameterList().setValue("OUT_QUOT_ITEM_LIST",cg_table); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void UpdateQuotationPrice() { try { //Get input Parameter //String lv_item_guid = jFunction.getImportParameterList().getString("IN_RFX_ITEM_GUID"); byte[] lv_item_guid = jFunction.getImportParameterList().getByteArray("IN_RFX_ITEM_GUID"); double lv_price = jFunction.getImportParameterList().getDouble("IN_NET_PRICE"); System.out.println("Price:" + lv_price); //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; //Retrieve quantity from table BBP_PDIGP ps = con.prepareStatement("select QUANTITY from sr1.BBP_PDIGP where CLIENT = ? and GUID = ?"); ps.setString(1, "800"); //InputStream lv_bytes_guid = new ByteArrayInputStream(lv_item_guid); //ps.setBinaryStream(2, lv_bytes_guid, 16); ps.setBytes(2, lv_item_guid); ResultSet queryResult = ps.executeQuery(); double lv_quantity = 0; if (queryResult.next()) { lv_quantity = queryResult.getDouble(1); System.out.println("Quantity:" + lv_quantity); } queryResult.close(); ps.close(); ps = con.prepareStatement("update sr1.BBP_PDIGP set PRICE = ? , VALUE = ? where GUID = ? and CLIENT = ?"); ps.setDouble(1, lv_price); ps.setDouble(2, lv_price*lv_quantity); //InputStream lv_bytes_guid1 = new ByteArrayInputStream(lv_item_guid); //ps.setBinaryStream(3, lv_bytes_guid1, 16); ps.setBytes(3, lv_item_guid); ps.setString(4, "800"); System.out.println("before:"); //ps.executeUpdate(); int result1 = ps.executeUpdate(); System.out.println("test result:" + result1); ps = con.prepareStatement("update sr1.BBP_PDISS set PS_VALUE_RU = ? where GUID = ? and CLIENT = ?"); ps.setDouble(1, lv_price*lv_quantity); //InputStream lv_bytes_guid2 = new ByteArrayInputStream(lv_item_guid); //ps.setBinaryStream(2, lv_bytes_guid2, 16); ps.setBytes(2, lv_item_guid); ps.setString(3, "800"); int result2 = ps.executeUpdate(); jFunction.getExportParameterList().setValue("OUT_RC",result1+result2); //jFunction.getExportParameterList().setValue("OUT_RC",result2); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void TableSyncWrite() { String lv_table_name = jFunction.getImportParameterList().getString("IN_TABLE_NAME"); JCoTable lt_keys = jFunction.getImportParameterList().getTable("IN_TABLE_KEYS"); if(lv_table_name.equals("CFX_FOL")){ Sync_CFX_FOL(); return; } try { //Get input Parameter //Retrieve keys from input parameters String[] str_keys = new String[lt_keys.getNumRows()]; String str_condition = " where "; System.out.print("Key for table:"); for (int i = 0; i < lt_keys.getNumRows();i++) { lt_keys.setRow(i); str_keys[i] = lt_keys.getString(0); System.out.print(str_keys[i]+" "); str_condition = str_condition + ((i==0)?"":" and ") + str_keys[i] + " = ? "; } System.out.println(); //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); Connection local_conn = factory.getLocalConnection(); PreparedStatement ps_insert = null; PreparedStatement ps_update = null; PreparedStatement local_ps = null; //Retrieve data from Local DB local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name); ResultSet queryResult = local_ps.executeQuery(); int count = 0; //Retrieve Meta Data ResultSetMetaData oMetaData = queryResult.getMetaData(); int i_columns = oMetaData.getColumnCount(); System.out.println("The number of columns for table " + lv_table_name + " is " + i_columns); String[] str_columns = new String[i_columns]; String field_list = ""; String value_list = ""; String update_list = ""; System.out.print("Fields List:"); for(int i=0; i< i_columns; i++){ str_columns[i] = oMetaData.getColumnName(i+1); System.out.print(str_columns[i]); field_list = field_list + ((i==0)?str_columns[i]:(","+str_columns[i])); value_list = value_list + ((i==0)?"?":",?"); } System.out.println(); for(int i=0; i< i_columns; i++){ update_list = update_list + ((i==0)?"":", ") + str_columns[i] + " = ? "; } //Prepare insert statement String sql = "insert into "+ remote_prefix +lv_table_name + " ("+field_list + ") values (" + value_list + ")"; System.out.println(sql); ps_insert = con.prepareStatement(sql); //Prepare update statement sql = "update "+ remote_prefix +lv_table_name + " set " + update_list + str_condition; System.out.println(sql); ps_update = con.prepareStatement(sql); int b_insert = 0; int b_update = 0; while (queryResult.next()) { count++; //Check whether the data record exists PreparedStatement ps = null; sql= "select count(*) from "+ remote_prefix + lv_table_name + str_condition; ps = con.prepareStatement(sql); Object[] obj_keys_value = new Object[str_keys.length]; for(int i=0;i< str_keys.length;i++){ obj_keys_value[i] = queryResult.getObject(str_keys[i]); ps.setObject(i+1, obj_keys_value[i]); } ResultSet queryResult1 = ps.executeQuery(); int flag = 0; if(queryResult1.next()){ flag = queryResult1.getInt(1); } ps.close(); if(flag==0){ //Insert new record for(int j=0;j<str_columns.length; j++){ ps_insert.setObject(j+1, queryResult.getObject(str_columns[j])); } ps_insert.addBatch(); b_insert++; }else{ //Update existing record for(int j=0;j<str_columns.length; j++){ ps_update.setObject(j+1, queryResult.getObject(str_columns[j])); } for(int j=0; j<str_keys.length; j++){ ps_update.setObject(j+1+ str_columns.length, obj_keys_value[j]); } ps_update.addBatch(); b_update++; } } //with batch if(b_insert>0){ ps_insert.executeBatch(); System.out.println("insert"+b_insert); } ps_insert.close(); if(b_update>0){ ps_update.executeBatch(); System.out.println("update"+b_update); } ps_update.close(); queryResult.close(); local_ps.close(); con.close(); local_conn.close(); jFunction.getExportParameterList().setValue("OUT_RC",count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); jFunction.getExportParameterList().setValue("OUT_RC",-1); } } private void Sync_CFX_FOL() { try { //Get input Parameter //String lv_table_name = jFunction.getImportParameterList().getString("IN_TABLE_NAME"); String lv_client_name = "CLIENT"; //String[] table_list = {"CFX_OBJ_DESCR","CFX_AREA_VISITOR","CFX_HIERARCHY","CFX_FOL","CFX_AREA"}; String[] table_list = {"CFX_AREA_VISITOR","CFX_HIERARCHY","CFX_FOL","CFX_AREA"}; int count = 0; //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getLocalConnection(); Connection local_conn = factory.getConnection(); PreparedStatement ps = null; PreparedStatement local_ps = null; for (int k = 0; k < table_list.length;k++) { String lv_table_name = table_list[k]; WriteLog("Table:" + lv_table_name + "\n"); //Retrieve data from Inner DB ps = con.prepareStatement("select * from "+ remote_prefix + lv_table_name +" where " + lv_client_name + " = ?"); ps.setString(1, "800"); ResultSet queryResult = ps.executeQuery(); ResultSetMetaData oMetaData = queryResult.getMetaData(); int i_columns = oMetaData.getColumnCount(); WriteLog("The number of columns for table " + lv_table_name + " is " + i_columns + "\n"); String[] str_columns = new String[i_columns]; String field_list = ""; String value_list = ""; WriteLog("Fields List:"); for(int i=0; i< i_columns; i++){ str_columns[i] = oMetaData.getColumnName(i+1); WriteLog(str_columns[i]+" "); field_list = field_list + ((i==0)?str_columns[i]:(","+str_columns[i])); value_list = value_list + ((i==0)?"?":",?"); } WriteLog("\n"); //Clear local table first local_ps = local_conn.prepareStatement("delete from " + local_prefix + lv_table_name); local_ps.executeUpdate(); local_ps.close(); //Prepare statement for local insert String sql = "insert into " + local_prefix + lv_table_name + " ("+field_list + ") values (" + value_list + ")"; WriteLog(sql+ "\n"); local_ps = local_conn.prepareStatement(sql); count =0; while (queryResult.next()) { count++; for(int j=0;j<str_columns.length; j++){ local_ps.setObject(j+1, queryResult.getObject(str_columns[j])); } //add to batch local_ps.addBatch(); } //execute the batch local_ps.executeBatch(); local_ps.close(); queryResult.close(); ps.close(); WriteLog("Count:" + count + "\n"); } con.close(); local_conn.close(); jFunction.getExportParameterList().setValue("OUT_RC",count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); WriteLog(e.getMessage()); jFunction.getExportParameterList().setValue("OUT_RC",-1); } } public void ClearLocalData() { try { //Get input Parameter String lv_table_name = jFunction.getImportParameterList().getString("IN_TABLE_NAME"); //JCoTable lt_table = jFunction.getExportParameterList().getTable("OUT_DATA_TABLE"); //Connect to DB DBFactory factory = new DBFactory(); Connection local_conn = factory.getLocalConnection(); PreparedStatement local_ps = null; //Clear local table first local_ps = local_conn.prepareStatement("delete from "+ local_prefix + lv_table_name); local_ps.executeUpdate(); local_ps.close(); jFunction.getExportParameterList().setValue("OUT_RC",0); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); jFunction.getExportParameterList().setValue("OUT_RC",-1); } } public void TableSyncRead() { try { //Get input Parameter //String lv_table_name = jFunction.getImportParameterList().getString("IN_TABLE_NAME"); String lv_client_name = jFunction.getImportParameterList().getString("IN_CLIENT_NAME"); JCoTable lt_table_name = jFunction.getImportParameterList().getTable("IN_TABLE_LIST"); int count = 0; //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); Connection local_conn = factory.getLocalConnection(); PreparedStatement ps = null; PreparedStatement local_ps = null; for (int k = 0; k < lt_table_name.getNumRows();k++) { lt_table_name.setRow(k); String lv_table_name = lt_table_name.getString(0); WriteLog("Table:" + lv_table_name + "\n"); //Retrieve data from Inner DB ps = con.prepareStatement("select * from "+ remote_prefix + lv_table_name +" where " + lv_client_name + " = ?"); ps.setString(1, "800"); ResultSet queryResult = ps.executeQuery(); ResultSetMetaData oMetaData = queryResult.getMetaData(); int i_columns = oMetaData.getColumnCount(); WriteLog("The number of columns for table " + lv_table_name + " is " + i_columns + "\n"); String[] str_columns = new String[i_columns]; String field_list = ""; String value_list = ""; WriteLog("Fields List:"); for(int i=0; i< i_columns; i++){ str_columns[i] = oMetaData.getColumnName(i+1); WriteLog(str_columns[i]+" "); field_list = field_list + ((i==0)?str_columns[i]:(","+str_columns[i])); value_list = value_list + ((i==0)?"?":",?"); } WriteLog("\n"); //Clear local table first local_ps = local_conn.prepareStatement("delete from " + local_prefix + lv_table_name); local_ps.executeUpdate(); local_ps.close(); //Prepare statement for local insert String sql = "insert into " + local_prefix + lv_table_name + " ("+field_list + ") values (" + value_list + ")"; WriteLog(sql+ "\n"); local_ps = local_conn.prepareStatement(sql); count =0; while (queryResult.next()) { count++; for(int j=0;j<str_columns.length; j++){ local_ps.setObject(j+1, queryResult.getObject(str_columns[j])); } //add to batch local_ps.addBatch(); } //execute the batch local_ps.executeBatch(); local_ps.close(); queryResult.close(); ps.close(); WriteLog("Count:" + count + "\n"); } con.close(); local_conn.close(); jFunction.getExportParameterList().setValue("OUT_RC",count); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); WriteLog(e.getMessage()); jFunction.getExportParameterList().setValue("OUT_RC",-1); } } public void RetrieveQuotItemDetailInfo() { DBFactory factory = null; Connection con = null; try { //Get input Parameter //String lv_item_guid = jFunction.getImportParameterList().getString("IN_RFX_ITEM_GUID"); byte[] lv_item_guid = jFunction.getImportParameterList().getByteArray("IN_QUOT_ITEM_GUID"); //Connect to DB factory = new DBFactory(); con = factory.getConnection(); PreparedStatement ps = null; //Retrieve quantity from table BBP_PDIGP ps = con.prepareStatement("select QUANTITY, PRICE, VALUE from sr1.BBP_PDIGP where CLIENT = ? and GUID = ?"); ps.setString(1, "800"); //InputStream lv_bytes_guid = new ByteArrayInputStream(lv_item_guid); //ps.setBinaryStream(2, lv_bytes_guid, 16); ps.setBytes(2, lv_item_guid); ResultSet queryResult = ps.executeQuery(); double lv_quantity = 0; double lv_price = 0; double lv_value = 0; if (queryResult.next()) { lv_quantity = queryResult.getDouble(1); lv_price = queryResult.getDouble(2); lv_value = queryResult.getDouble(3); System.out.println("Quantity:" + lv_quantity); System.out.println("Price:" + lv_price); System.out.println("Value:" + lv_value); } jFunction.getExportParameterList().setValue("OUT_PRICE", lv_price); jFunction.getExportParameterList().setValue("OUT_QTY", lv_quantity); jFunction.getExportParameterList().setValue("OUT_VALUE", lv_value); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } finally{ con = null; } } public void RetrieveRFxDetailInformation() { try { //Get input Parameter String lv_rfx_id = jFunction.getImportParameterList().getString("IN_RFX_ID"); //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; ps = con.prepareStatement("select GUID, DESCRIPTION from sr1.CRMD_ORDERADM_H where CLIENT = ? and OBJECT_ID = ?"); ps.setString(1, "800"); ps.setString(2, lv_rfx_id); ResultSet queryResult = ps.executeQuery(); String lv_guid = ""; String lv_description = ""; //JCoTable table = new JCoTable(); if (queryResult.next()) { lv_guid = queryResult.getString(1); lv_description = queryResult.getString(2); System.out.println("GUID:" + lv_guid); System.out.println("Description:" + lv_description); jFunction.getExportParameterList().setValue("OUT_RFX_DESCRIPTION",lv_description); } } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } public void WriteLog(String conent) { BufferedWriter out = null; String file = "c:/SGCC_LOG.txt"; try { out = new BufferedWriter(new OutputStreamWriter( new FileOutputStream(file, true))); out.write(conent); } catch (Exception e) { e.printStackTrace(); } finally { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } public void WriteLogln(String conent) { BufferedWriter out = null; String file = "c:/SGCC_LOG.txt"; try { out = new BufferedWriter(new OutputStreamWriter( new FileOutputStream(file, true))); out.write(conent+"\r\n"); } catch (Exception e) { e.printStackTrace(); } finally { try { out.close(); } catch (IOException e) { e.printStackTrace(); } } } private BigDecimal getSyncDate(){ // BigDecimal result = new BigDecimal(20100330000000.0); return result; } private String getDeleteSql(String sql, String table_name, String key){ //delete <table_name> where exists ( select 1 from () ttt where ttt.GUID = <table_name>.GUID ) String result = "delete "; result += table_name; result += " where exists ( select 1 from ("; result += sql; result += ") ttt where ttt."; result += key; result += " = "; result += table_name; result += "."; result += key; result += " )"; WriteLogln(result); return result; } public void RFxSyncRead() { Connection con = null; Connection local_conn = null; user1 = jFunction.getImportParameterList().getString("IN_PUR_USER"); user2 = jFunction.getImportParameterList().getString("IN_CURR_USER"); BigDecimal dtEnd = getSyncDate(); String sql_se = ""; String sql_de = ""; try { //Connect to DB DBFactory factory = new DBFactory(); con = factory.getConnection(); local_conn = factory.getLocalConnection(); PreparedStatement ps = null; PreparedStatement local_ps = null; ResultSet queryResult; /* * BBP_PDBGP * select * from sr1.BBP_PDBGP where CLIENT = '800' * delete from sr2.BBP_PDBGP where CLIENT = '800' */ //Clear local table first local_ps = local_conn.prepareStatement("delete from BBP_PDBGP where CLIENT = ?"); local_ps.setString(1, "800"); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement("select * from BBP_PDBGP where CLIENT = ?"); ps.setString(1, "800"); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDBGP", local_conn); queryResult.close(); ps.close(); /* * BBP_PDIGP * select ITEM.* from sr1.CRMD_ORDERADM_I as HEADER, sr1.BBP_PDIGP as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200001' OR HEADER.OBJECT_TYPE = 'BUS2202001') AND HEADER.GUID = ITEM.GUID * delete ITEM from sr2.CRMD_ORDERADM_I as HEADER, sr2.BBP_PDIGP as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200001' OR HEADER.OBJECT_TYPE = 'BUS2202001') AND HEADER.GUID = ITEM.GUID */ //Clear local table first sql_se = "select ITEM.* from CRMD_ORDERADM_I HEADER, BBP_PDIGP ITEM where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ? ) AND HEADER.GUID = ITEM.GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDIGP", "GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200001"); local_ps.setString(2, "BUS2202001"); local_ps.setString(3, user1); local_ps.setString(4, user2); //local_ps.setObject(5, dtEnd); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200001"); ps.setString(2, "BUS2202001"); ps.setString(3, user1); ps.setString(4, user2); //ps.setObject(5, dtEnd); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDIGP", local_conn); System.out.println("--------Done"); queryResult.close(); ps.close(); /* * BBP_PDDYN_FIX for item * fix answer for item * --select FIX.* from sr1.CRMD_ORDERADM_I as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART, sr1.BBP_PDDYN_FIX as FIX where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND PART.GUID = FIX.SET_LINE_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) * --delete FIX from sr1.CRMD_ORDERADM_I as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART, sr1.BBP_PDDYN_FIX as FIX where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND PART.GUID = FIX.SET_LINE_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) */ //Clear local table first sql_se = "select FIX.* from CRMD_ORDERADM_I HEADER, CRMD_LINK LINK, BBP_PDDYN PART, BBP_PDDYN_FIX FIX where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND PART.GUID = FIX.SET_LINE_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDDYN_FIX", "SET_LINE_GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200001"); local_ps.setString(2, "BUS2202001"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200001"); ps.setString(2, "BUS2202001"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDDYN_FIX", local_conn); queryResult.close(); ps.close(); /* * BBP_PDDYN for item * questions for item * --select PART.* from sr1.CRMD_ORDERADM_I as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) * --select PART.* from sr1.CRMD_ORDERADM_I as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART where ( HEADER.OBJECT_TYPE = 'BUS2200001' OR HEADER.OBJECT_TYPE = 'BUS2202001') AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID * */ //Clear local table first sql_se = "select PART.* from CRMD_ORDERADM_I HEADER, CRMD_LINK LINK, BBP_PDDYN PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDDYN", "SET_GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200001"); local_ps.setString(2, "BUS2202001"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200001"); ps.setString(2, "BUS2202001"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDDYN", local_conn); queryResult.close(); ps.close(); /* * BBP_PDWGT for item * the weight of questions for item * --select PART.* from sr1.CRMD_ORDERADM_I as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDWGT as PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) * */ //Clear local table first sql_se = "select PART.* from CRMD_ORDERADM_I HEADER, CRMD_LINK LINK, BBP_PDWGT PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDWGT", "SET_GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200001"); local_ps.setString(2, "BUS2202001"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200001"); ps.setString(2, "BUS2202001"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDWGT", local_conn); queryResult.close(); ps.close(); /* * CRMD_LINK for items * select LINK.* from sr1.CRMD_ORDERADM_I as HEADER, sr1.CRMD_LINK as LINK where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.OBJTYPE_SET = '07' * delete LINK from sr1.CRMD_ORDERADM_I as HEADER, sr1.CRMD_LINK as LINK where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.OBJTYPE_SET = '07' */ sql_se="select LINK.* from CRMD_ORDERADM_I HEADER, CRMD_LINK LINK where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ? ) AND HEADER.GUID = LINK.GUID_HI AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "CRMD_LINK", "GUID_HI"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200001"); local_ps.setString(2, "BUS2202001"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200001"); ps.setString(2, "BUS2202001"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "CRMD_LINK", local_conn); queryResult.close(); ps.close(); /* * CRMD_ORDERADM_I * select * from sr1.CRMD_ORDERADM_I where OBJECT_TYPE = 'BUS2200001' OR OBJECT_TYPE = 'BUS2202001' * delete from sr2.CRMD_ORDERADM_I where OBJECT_TYPE = 'BUS2200001' OR OBJECT_TYPE = 'BUS2202001' */ //Clear local table first sql_se = "select * from CRMD_ORDERADM_I where (OBJECT_TYPE = ? OR OBJECT_TYPE = ?) AND ( CREATED_BY = ? OR CREATED_BY = ? ) AND CREATED_AT > ?"; //not necessary to process delete local_ps = local_conn.prepareStatement("delete from CRMD_ORDERADM_I where (OBJECT_TYPE = ? OR OBJECT_TYPE = ?) AND ( CREATED_BY = ? OR CREATED_BY = ? ) AND CREATED_AT > ?"); local_ps.setString(1, "BUS2200001"); local_ps.setString(2, "BUS2202001"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200001"); ps.setString(2, "BUS2202001"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "CRMD_ORDERADM_I", local_conn); queryResult.close(); ps.close(); /* * --BBP_PDHGP * --select ITEM.* from sr2.CRMD_ORDERADM_H as HEADER, sr2.BBP_PDHGP as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.GUID * --delete ITEM from sr2.CRMD_ORDERADM_H as HEADER, sr2.BBP_PDHGP as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.GUID */ //Clear local table first sql_se = "select ITEM.* from CRMD_ORDERADM_H HEADER, BBP_PDHGP ITEM where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ? ) AND HEADER.GUID = ITEM.GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDHGP", "GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDHGP", local_conn); queryResult.close(); ps.close(); /* * --BBP_PDHSB * --select ITEM.* from sr2.CRMD_ORDERADM_H as HEADER, sr2.BBP_PDHSB as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.GUID * --delete ITEM from sr2.CRMD_ORDERADM_H as HEADER, sr2.BBP_PDHSB as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.GUID */ //Clear local table first sql_se = "select ITEM.* from CRMD_ORDERADM_H HEADER, BBP_PDHSB ITEM where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ? ) AND HEADER.GUID = ITEM.GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDHSB", "GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDHSB", local_conn); queryResult.close(); ps.close(); /* * --BBP_PDHSS * --select ITEM.* from sr2.CRMD_ORDERADM_H as HEADER, sr2.BBP_PDHSS as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.GUID * --delete ITEM from sr2.CRMD_ORDERADM_H as HEADER, sr2.BBP_PDHSS as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.GUID */ //Clear local table first sql_se = "select ITEM.* from CRMD_ORDERADM_H HEADER, BBP_PDHSS ITEM where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = ITEM.GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDHSS", "GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDHSS", local_conn); queryResult.close(); ps.close(); /* * CRMD_PARTNER * select PART.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.CRMD_PARTNER as PART where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.GUID AND LINK.OBJTYPE_SET = '07' * delete PART from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.CRMD_PARTNER as PART where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.GUID AND LINK.OBJTYPE_SET = '07' */ //Clear local table first sql_se = "select PART.* from CRMD_ORDERADM_H HEADER, CRMD_LINK LINK, CRMD_PARTNER PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.GUID AND LINK.OBJTYPE_SET = ? AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "CRMD_PARTNER", "GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, "07"); local_ps.setString(4, user1); local_ps.setString(5, user2); local_ps.setBigDecimal(6, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, "07"); ps.setString(4, user1); ps.setString(5, user2); ps.setBigDecimal(6, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "CRMD_PARTNER", local_conn); queryResult.close(); ps.close(); /* * BBP_PDDYN_FIX for header * fix answer for header * --select FIX.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART, sr1.BBP_PDDYN_FIX as FIX where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND PART.GUID = FIX.SET_LINE_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) * --delete FIX from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART, sr1.BBP_PDDYN_FIX as FIX where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND PART.GUID = FIX.SET_LINE_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) */ //Clear local table first sql_se = "select FIX.* from CRMD_ORDERADM_H HEADER, CRMD_LINK LINK, BBP_PDDYN PART, BBP_PDDYN_FIX FIX where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND PART.GUID = FIX.SET_LINE_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDDYN_FIX", "SET_LINE_GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDDYN_FIX", local_conn); queryResult.close(); ps.close(); /* * BBP_PDDYN for header * questions for header * --select PART.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) * --select PART.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDDYN as PART where ( HEADER.OBJECT_TYPE = 'BUS2200001' OR HEADER.OBJECT_TYPE = 'BUS2202001') AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID * */ //Clear local table first sql_se = "select PART.* from CRMD_ORDERADM_H HEADER, CRMD_LINK LINK, BBP_PDDYN PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDDYN", "SET_GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDDYN", local_conn); queryResult.close(); ps.close(); /* * BBP_PDWGT for header * the weight of questions for header * --select PART.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDWGT as PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) * */ //Clear local table first sql_se = "select PART.* from CRMD_ORDERADM_H HEADER, CRMD_LINK LINK, BBP_PDWGT PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDWGT", "SET_GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDWGT", local_conn); queryResult.close(); ps.close(); /* * BBP_PDATT for header * attachment or c-folder * --select PART.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.BBP_PDWGT as PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) * */ //Clear local table first sql_se = "select PART.* from CRMD_ORDERADM_H HEADER, CRMD_LINK LINK, BBP_PDATT PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.SET_GUID AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "BBP_PDATT", "SET_GUID"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "BBP_PDATT", local_conn); queryResult.close(); ps.close(); /* * CRMD_LINK * select LINK.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.OBJTYPE_SET = '07' * delete LINK from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.OBJTYPE_SET = '07' */ sql_se = "select LINK.* from CRMD_ORDERADM_H HEADER, CRMD_LINK LINK where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ? ) AND HEADER.GUID = LINK.GUID_HI AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "CRMD_LINK", "GUID_HI"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "CRMD_LINK", local_conn); queryResult.close(); ps.close(); /* * --CRM_JSTO * --select ITEM.* from sr2.CRMD_ORDERADM_H as HEADER, sr2.CRM_JSTO as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.OBJNR * --delete ITEM from sr2.CRMD_ORDERADM_H as HEADER, sr2.CRM_JSTO as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.OBJNR */ //Clear local table first sql_se = "select ITEM.* from CRMD_ORDERADM_H HEADER, CRM_JSTO ITEM where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = ITEM.OBJNR AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "CRM_JSTO", "OBJNR"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); //local_ps = local_conn.prepareStatement("delete from sr2.CRM_JSTO"); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "CRM_JSTO", local_conn); queryResult.close(); ps.close(); /* * --CRM_JEST * --select ITEM.* from sr2.CRMD_ORDERADM_H as HEADER, sr2.CRM_JEST as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.OBJNR * --delete ITEM from sr2.CRMD_ORDERADM_H as HEADER, sr2.CRM_JEST as ITEM where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = ITEM.OBJNR */ //Clear local table first sql_se = "select ITEM.* from CRMD_ORDERADM_H HEADER, CRM_JEST ITEM where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = ITEM.OBJNR AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? ) AND HEADER.CREATED_AT > ?"; sql_de = getDeleteSql(sql_se, "CRM_JEST", "OBJNR"); local_ps = local_conn.prepareStatement(sql_de); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); //local_ps = local_conn.prepareStatement("delete from sr2.CRM_JEST"); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement(sql_se); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "CRM_JEST", local_conn); queryResult.close(); ps.close(); /* * Process table CRMD_ORDERADM_H start * select * from sr1.CRMD_ORDERADM_H where OBJECT_TYPE = 'BUS2200' OR OBJECT_TYPE = 'BUS2202' * delete from sr2.CRMD_ORDERADM_H where OBJECT_TYPE = 'BUS2200' OR OBJECT_TYPE = 'BUS2202' */ //Clear local table first local_ps = local_conn.prepareStatement("delete from CRMD_ORDERADM_H where (OBJECT_TYPE = ? OR OBJECT_TYPE = ?) AND ( CREATED_BY = ? OR CREATED_BY = ? ) AND CREATED_AT > ?"); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, user1); local_ps.setString(4, user2); local_ps.setBigDecimal(5, dtEnd); local_ps.executeUpdate(); local_ps.close(); //Retrieve data from Inner DB ps = con.prepareStatement("select * from CRMD_ORDERADM_H where (OBJECT_TYPE = ? OR OBJECT_TYPE = ?) AND ( CREATED_BY = ? OR CREATED_BY = ? ) AND CREATED_AT > ?"); ps.setString(1, "BUS2200"); ps.setString(2, "BUS2202"); ps.setString(3, user1); ps.setString(4, user2); ps.setBigDecimal(5, dtEnd); queryResult = ps.executeQuery(); ProcessTableInsert(queryResult, "CRMD_ORDERADM_H", local_conn); queryResult.close(); ps.close(); jFunction.getExportParameterList().setValue("OUT_RC",0); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); WriteLogln(e.getMessage()); jFunction.getExportParameterList().setValue("OUT_RC",-1); } finally{ try { con.close(); local_conn.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } } public void RFxSyncWrite() { try { String lv_table_name = ""; String[] str_keys; //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); Connection local_conn = factory.getLocalConnection(); PreparedStatement local_ps = null; ResultSet queryResult; //Process Table BBP_PDBGP lv_table_name = "BBP_PDBGP"; str_keys = new String[2]; str_keys[0] = "CLIENT"; str_keys[1] = "PARTNER_GUID"; //Retrieve data from Local DB local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name +" where CLIENT = ?"); local_ps.setString(1, "800"); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, str_keys, con); queryResult.close(); local_ps.close(); //Process Table CRMD_PARTNER /* * CRMD_PARTNER * select PART.* from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.CRMD_PARTNER as PART where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.GUID AND LINK.OBJTYPE_SET = '07' * delete PART from sr1.CRMD_ORDERADM_H as HEADER, sr1.CRMD_LINK as LINK, sr1.CRMD_PARTNER as PART where ( HEADER.OBJECT_TYPE = 'BUS2200' OR HEADER.OBJECT_TYPE = 'BUS2202') AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.GUID AND LINK.OBJTYPE_SET = '07' */ lv_table_name = "CRMD_PARTNER"; str_keys = new String[2]; str_keys[0] = "CLIENT"; str_keys[1] = "PARTNER_GUID"; //Retrieve data from Local DB local_ps = local_conn.prepareStatement("select PART.* from CRMD_ORDERADM_H HEADER, CRMD_LINK LINK, CRMD_PARTNER PART where ( HEADER.OBJECT_TYPE = ? OR HEADER.OBJECT_TYPE = ?) AND HEADER.GUID = LINK.GUID_HI AND LINK.GUID_SET = PART.GUID AND LINK.OBJTYPE_SET = ? AND ( HEADER.CREATED_BY = ? OR HEADER.CREATED_BY = ? )"); local_ps.setString(1, "BUS2200"); local_ps.setString(2, "BUS2202"); local_ps.setString(3, "07"); local_ps.setString(4, user1); local_ps.setString(5, user2); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, str_keys, con); queryResult.close(); local_ps.close(); jFunction.getExportParameterList().setValue("OUT_RC",0); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); jFunction.getExportParameterList().setValue("OUT_RC",-1); } } public void rfxResponseWrite() { try { //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); Connection local_conn = factory.getLocalConnection(); PreparedStatement local_ps = null; ResultSet queryResult; String lv_table_name = ""; String tbl_keys[]; // Update CRMD_ORDERADM_H lv_table_name = "CRMD_ORDERADM_H"; String lv_tbl_version = "BBP_PDHGP"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; // Get Object ID (Response ID) String objectID = String.valueOf(jFunction.getImportParameterList().getValue("IN_OBJECT_ID")); // Get Header Info. of Response Document from local DB local_ps = local_conn.prepareStatement("select HEADER.GUID from "+ local_prefix + lv_table_name + " HEADER, "+local_prefix+lv_tbl_version+" VERSION where HEADER.OBJECT_TYPE = ? AND HEADER.OBJECT_ID = ? AND VERSION.GUID = HEADER.GUID AND VERSION.VERSION_TYPE = ' '"); local_ps.setString(1, "BUS2202"); local_ps.setString(2, objectID); queryResult = local_ps.executeQuery(); // Get Response GUID queryResult.next(); byte[] responseGuid = queryResult.getBytes("GUID"); queryResult.close(); local_ps.close(); local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name + " where OBJECT_TYPE = ? AND GUID = ?"); local_ps.setString(1, "BUS2202"); local_ps.setBytes(2, responseGuid); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); //Update BBP_PDHGP lv_table_name = "BBP_PDHGP"; tbl_keys = new String [2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name +" HEADER where HEADER.GUID = ?"); local_ps.setBytes(1, responseGuid); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update item lv_table_name = "CRMD_ORDERADM_I"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name + " ITEM where ITEM.HEADER = ?"); local_ps.setBytes(1, responseGuid); queryResult = local_ps.executeQuery(); // Get Item GUID List ArrayList<byte[]> itemList = new ArrayList<byte[]>(); while (queryResult.next()) { byte[] itemGuid = queryResult.getBytes("GUID"); itemList.add(itemGuid); } queryResult.close(); local_ps.close(); local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name + " ITEM where ITEM.HEADER = ?"); local_ps.setBytes(1, responseGuid); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update Item table BBP_PDIGP lv_table_name = "BBP_PDIGP"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; for(int i=0;i<itemList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name + " ITEM where ITEM.GUID = ?"); local_ps.setBytes(1, itemList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update Item table BBP_PDIGP lv_table_name = "BBP_PDISS"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; for(int i=0;i<itemList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+ local_prefix + lv_table_name + " ITEM where ITEM.GUID = ?"); local_ps.setBytes(1, itemList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update Response Status table CRM_JEST lv_table_name = "CRM_JEST"; tbl_keys = new String[3]; tbl_keys[0] = "MANDT"; tbl_keys[1] = "OBJNR"; tbl_keys[2] = "STAT"; local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where OBJNR = ?"); local_ps.setBytes(1, responseGuid); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update status table CRM_JSTO lv_table_name = "CRM_JSTO"; tbl_keys = new String[2]; tbl_keys[0] = "MANDT"; tbl_keys[1] = "OBJNR"; local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where OBJNR = ?"); local_ps.setBytes(1, responseGuid); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update link lv_table_name = "CRMD_LINK"; tbl_keys = new String[3]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID_HI"; tbl_keys[2] = "GUID_SET"; local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where GUID_HI = ?"); local_ps.setBytes(1, responseGuid); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update Partner table CRMD_PARTNER lv_table_name = "CRMD_PARTNER"; // String lv_header_tbl = "CRMD_ORDERADM_H"; String lv_link_tbl = "CRMD_LINK"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "PARTNER_GUID"; local_ps = local_conn.prepareStatement("select PART.* from "+local_prefix+lv_table_name+" PART, "+local_prefix+lv_link_tbl+" LINK where LINK.GUID_HI = ? AND LINK.GUID_SET = PART.GUID AND LINK.OBJTYPE_SET = ?"); local_ps.setBytes(1, responseGuid); local_ps.setString(2, "07"); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update Attachments lv_table_name = "BBP_PDATT"; lv_link_tbl = "CRMD_LINK"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; local_ps = local_conn.prepareStatement("select ATT.* from "+local_prefix+lv_link_tbl+" LINK, "+local_prefix+lv_table_name+" ATT where LINK.GUID_HI = ? AND LINK.GUID_SET = ATT.SET_GUID AND LINK.OBJTYPE_SET = ?"); local_ps.setBytes(1, responseGuid); local_ps.setString(2, "33"); queryResult = local_ps.executeQuery(); // Get logical object ID == attachment ID == physical object ID ArrayList<String> attList = new ArrayList<String>(); while(queryResult.next()) { attList.add(queryResult.getString("LOIO_OBJID")); } System.out.println("SIZE: "+ attList.size()); System.out.println(); queryResult.close(); local_ps.close(); local_ps = local_conn.prepareStatement("select ATT.* from "+local_prefix+lv_link_tbl+" LINK, "+local_prefix+lv_table_name+" ATT where LINK.GUID_HI = ? AND ATT.SET_GUID = LINK.GUID_SET AND LINK.OBJTYPE_SET = ?"); local_ps.setBytes(1, responseGuid); local_ps.setString(2, "33"); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update BBP_LOIO lv_table_name = "BBP_LOIO"; tbl_keys[0] = "MANDT"; tbl_keys[1] = "LOIO_ID"; for (int i=0;i<attList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where LOIO_ID = ?"); local_ps.setString(1, attList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update BBP_LOIOT lv_table_name = "BBP_LOIOT"; tbl_keys = new String[3]; tbl_keys[0] = "MANDT"; tbl_keys[1] = "LANGU"; tbl_keys[2] = "LOIO_ID"; for (int i=0;i<attList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where LOIO_ID = ?"); local_ps.setString(1, attList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update BBP_PHIO lv_table_name = "BBP_PHIO"; tbl_keys = new String[2]; tbl_keys[0] = "MANDT"; tbl_keys[1] = "PHIO_ID"; for (int i=0;i<attList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where PHIO_ID = ?"); local_ps.setString(1, attList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update BBPCONT lv_table_name = "BBPCONT"; tbl_keys = new String[4]; tbl_keys[0] = "MANDT"; tbl_keys[1] = "RELID"; tbl_keys[2] = "PHIO_ID"; tbl_keys[3] = "SRTF2"; for (int i=0;i<attList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where PHIO_ID = ?"); local_ps.setString(1, attList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update BBP_PHF lv_table_name = "BBP_PHF"; tbl_keys = new String[3]; tbl_keys[0] = "MANDT"; tbl_keys[1] = "PHIO_ID"; tbl_keys[2] = "FILE_NUM"; for (int i=0;i<attList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where PHIO_ID = ?"); local_ps.setString(1, attList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update Question lv_table_name = "BBP_PDDYN"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; local_ps = local_conn.prepareStatement("select DA.* from "+local_prefix+lv_table_name+" DA, "+local_prefix+lv_link_tbl+" LINK where LINK.GUID_HI = ? AND LINK.GUID_SET = DA.SET_GUID AND LINK.OBJTYPE_SET = ?"); local_ps.setBytes(1, responseGuid); local_ps.setString(2, "61"); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update Question on Item level for (int i=0;i<itemList.size();i++) { local_ps = local_conn.prepareStatement("select DA.* from "+local_prefix+lv_table_name+" DA, "+local_prefix+lv_link_tbl+" LINK where LINK.GUID_HI = ? AND LINK.GUID_SET = DA.SET_GUID AND LINK.OBJTYPE_SET = ?"); local_ps.setBytes(1, itemList.get(i)); local_ps.setString(2, "61"); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update item CRMD_Link lv_table_name = "CRMD_LINK"; tbl_keys = new String[3]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID_HI"; tbl_keys[2] = "GUID_SET"; for (int i=0;i<itemList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where GUID_HI = ?"); local_ps.setBytes(1, itemList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } // Update reference to Rfx HEADER lv_table_name = "BBP_PDHGP"; tbl_keys = new String[2]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "GUID"; local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where GUID = ?"); local_ps.setBytes(1, responseGuid); queryResult = local_ps.executeQuery(); queryResult.next(); byte[] srcGuid = queryResult.getBytes("SRC_GUID"); queryResult.close(); local_ps.close(); lv_table_name = "SRRELROLES"; tbl_keys = new String[5]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "OBJKEY"; tbl_keys[2] = "OBJTYPE"; tbl_keys[3] = "LOGSYS"; tbl_keys[4] = "ROLETYPE"; // Convert byte[] to hex string String srcKey = byte2HexStr(srcGuid); String responseKey = byte2HexStr(responseGuid); local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where OBJKEY = ? "); local_ps.setString(1, srcKey); queryResult = local_ps.executeQuery(); // Find Source Role ID queryResult.next(); String srcRoleID = queryResult.getString("ROLEID"); queryResult.close(); local_ps.close(); local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where OBJKEY = ? OR OBJKEY = ?"); local_ps.setString(1, srcKey); local_ps.setString(2, responseKey); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); lv_table_name = "BBP_PDBINREL"; tbl_keys = new String[3]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "ROLE_A"; tbl_keys[2] = "ROLE_B"; local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where ROLE_A = ?"); local_ps.setString(1, srcRoleID); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); // Update Reference to Rfx item lv_table_name = "CRMD_ORDERADM_I"; local_ps = local_conn.prepareStatement("select GUID from "+local_prefix+lv_table_name+" where HEADER = ?"); local_ps.setBytes(1, srcGuid); queryResult = local_ps.executeQuery(); ArrayList<String> srcItemList = new ArrayList<String>(); ArrayList<String> srcRoleList = new ArrayList<String>(); ArrayList<String> itemStrList = new ArrayList<String>(); for (int i=0;i<itemList.size();i++) { itemStrList.add(byte2HexStr(itemList.get(i))); } // Get source item guid(in Hex String) list while (queryResult.next()) { srcItemList.add(byte2HexStr(queryResult.getBytes("GUID"))); } queryResult.close(); local_ps.close(); lv_table_name = "SRRELROLES"; tbl_keys = new String[5]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "OBJKEY"; tbl_keys[2] = "OBJTYPE"; tbl_keys[3] = "LOGSYS"; tbl_keys[4] = "ROLETYPE"; for (int i=0;i<srcItemList.size();i++) { local_ps = local_conn.prepareStatement("select ROLEID from "+local_prefix+lv_table_name+" where OBJKEY = ? "); local_ps.setString(1, srcItemList.get(i)); queryResult = local_ps.executeQuery(); // Find Source Role ID queryResult.next(); srcRoleList.add(queryResult.getString("ROLEID")); queryResult.close(); local_ps.close(); } for (int i=0;i<srcItemList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where OBJKEY = ?"); local_ps.setString(1, srcItemList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } for (int i=0;i<itemStrList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where OBJKEY = ?"); local_ps.setString(1, itemStrList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } lv_table_name = "BBP_PDBINREL"; tbl_keys = new String[3]; tbl_keys[0] = "CLIENT"; tbl_keys[1] = "ROLE_A"; tbl_keys[2] = "ROLE_B"; for (int i=0;i<srcRoleList.size();i++) { local_ps = local_conn.prepareStatement("select * from "+local_prefix+lv_table_name+" where ROLE_A = ?"); local_ps.setString(1, srcRoleList.get(i)); queryResult = local_ps.executeQuery(); ProcessTableUpdate(queryResult, lv_table_name, tbl_keys, con); queryResult.close(); local_ps.close(); } jFunction.getExportParameterList().setValue("OUT_RC",0); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); jFunction.getExportParameterList().setValue("OUT_RC",-1); } } public void rfxPublish() { //Need read table: CRMD_ORDERADM_H; BBP_PDHSB; CRM_JEST; CRM_JSTO try { //Get input Parameter JCoTable cg_table = jFunction.getChangingParameterList().getTable("CT_RFX_LIST"); //Connect to DB DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; String lv_hdr_tbl = "CRMD_ORDERADM_H"; String lv_qt_dead_tbl = "BBP_PDHSB"; // String lv_stat_tbl = "CRM_JEST"; String lv_zstat_tbl = "ZSTATUS"; // USE Z table to retrieve status // ps = con.prepareStatement("select HEADER.GUID, HEADER.OBJECT_ID, HEADER.CREATED_AT, HEADER.DESCRIPTION, DEAD.QUOT_DEAD, DEAD.QUOT_DEAD_TIME from "+remote_prefix+lv_hdr_tbl+" HEADER, "+remote_prefix+lv_qt_dead_tbl+" DEAD, "+remote_prefix+lv_stat_tbl+" STAT where HEADER.CLIENT = ? and HEADER.OBJECT_TYPE = ? AND HEADER.GUID = DEAD.GUID AND HEADER.GUID = STAT.OBJNR AND STAT.STAT = ? order by HEADER.CREATED_AT DESC"); // ps.setString(1, "800"); // ps.setString(2, "BUS2200"); // ps.setString(3, "I1011"); ps = con.prepareStatement("select HEADER.GUID, HEADER.OBJECT_ID, HEADER.CREATED_AT, HEADER.DESCRIPTION, DEAD.QUOT_DEAD, DEAD.QUOT_DEAD_TIME from "+remote_prefix+lv_hdr_tbl+" HEADER, "+remote_prefix+lv_qt_dead_tbl+" DEAD, "+remote_prefix+lv_zstat_tbl+" STAT where HEADER.CLIENT = ? and HEADER.OBJECT_TYPE = ? AND HEADER.GUID = DEAD.GUID AND HEADER.OBJECT_ID = substr(STAT.RFX, 1, 10) AND substr(STAT.RFX, 11, 14) = '0001' AND STAT.STATUS = ? order by HEADER.CREATED_AT DESC"); ps.setString(1, "800"); ps.setString(2, "BUS2200"); ps.setString(3, "A"); ResultSet queryResult = ps.executeQuery(); byte[] rfxGuid = null; String objID = ""; double createAt = new Double(0); String description = ""; String qtDead = ""; String qtTime = ""; while (queryResult.next()) { rfxGuid = queryResult.getBytes(1); objID = queryResult.getString(2); createAt = queryResult.getDouble(3); description = queryResult.getString(4); qtDead = queryResult.getString(5); qtTime = queryResult.getString(6); //System.out.println("RFX ID:" + objID); cg_table.appendRow(); cg_table.setValue("RFX_GUID", rfxGuid); cg_table.setValue("RFX_ID", objID); cg_table.setValue("CREATED_AT", createAt); cg_table.setValue("DESCRIPTION", description); cg_table.setValue("QUOT_DEAD_DT", qtDead); cg_table.setValue("QUOT_DEAD_TIME", qtTime); cg_table.setValue("STAT", "I1011"); } jFunction.getChangingParameterList().setValue("CT_RFX_LIST",cg_table); queryResult.close(); ps.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } // public void rfxSucceed() // { // JCoTable cg_table = jFunction.getExportParameterList().getTable("ET_RFX_SUCCEED"); // // DBFactory factory = new DBFactory(); // Connection con = factory.getConnection(); // PreparedStatement ps = null; // } public void rfxBriefIDRead() { try{ JCoTable etRfxID = jFunction.getExportParameterList().getTable("ET_RFX_ID"); DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; String lv_brief_tbl = "ZBID_BRIEF"; ps = con.prepareStatement("select distinct RFX_ID, RFX_DESC from "+remote_prefix+lv_brief_tbl+" where PUB_FLAG = ?"); ps.setString(1, "1"); ResultSet queryResult = ps.executeQuery(); while(queryResult.next()) { etRfxID.appendRow(); etRfxID.setValue("RFX_ID", queryResult.getString("RFX_ID")); etRfxID.setValue("RFX_DESCRIPTION", queryResult.getString("RFX_DESC")); } queryResult.close(); ps.close(); jFunction.getExportParameterList().setValue("ET_RFX_ID", etRfxID); }catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace();} } public void rfxBriefRead() { try { JCoTable cg_table = jFunction.getExportParameterList().getTable("ET_RFX_BRIEF"); char publish = jFunction.getImportParameterList().getChar("PUBLISH"); String rfxID = jFunction.getImportParameterList().getString("RFX_ID"); String requester = jFunction.getImportParameterList().getString("REQUESTER"); DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; String lv_brief_tbl = "ZBID_BRIEF"; if (publish == '1') // used in publish of bidding clarification(澄清公告), pub_flag = 1 { ps = con.prepareStatement("select * from "+remote_prefix+lv_brief_tbl+" where MANDT = ? AND PUB_FLAG = 1 AND RFX_ID = ?"); ps.setString(1, "800"); ps.setString(2, rfxID); ResultSet queryResult = ps.executeQuery(); while (queryResult.next()) { cg_table.appendRow(); cg_table.setValue("MANDT", queryResult.getString("MANDT")); cg_table.setValue("RFX_ID", queryResult.getString("RFX_ID")); cg_table.setValue("ITEM_ID", queryResult.getString("ITEM_ID")); cg_table.setValue("QUS_ID", queryResult.getBytes("QUS_ID")); cg_table.setValue("RFX_DESC", queryResult.getString("RFX_DESC")); cg_table.setValue("ITEM_DESC",queryResult.getString("ITEM_DESC")); cg_table.setValue("QUESTION", queryResult.getString("QUESTION")); cg_table.setValue("REPLY", queryResult.getString("REPLY")); cg_table.setValue("REQUESTER", queryResult.getString("REQUESTER")); cg_table.setValue("BIDDER", queryResult.getString("BIDDER")); cg_table.setValue("BI_COMPANY", queryResult.getString("BI_COMPANY")); cg_table.setValue("REPLYER", queryResult.getString("REPLYER")); cg_table.setValue("RE_DESC",queryResult.getString("RE_DESC")); cg_table.setValue("APPROVER", queryResult.getString("APPROVER")); cg_table.setValue("AP_DESC", queryResult.getString("AP_DESC")); cg_table.setValue("STATUS", queryResult.getString("STATUS")); cg_table.setValue("PUB_FLAG", queryResult.getString("PUB_FLAG")); cg_table.setValue("REQUEST_DAT", queryResult.getString("REQUEST_DAT")); cg_table.setValue("REPLY_DAT", queryResult.getString("REPLY_DAT")); cg_table.setValue("PRD_ID", queryResult.getString("ORDERED_PROD")); cg_table.setValue("QUS_ID_32", queryResult.getString("QUS_ID_32")); } jFunction.getExportParameterList().setValue("ET_RFX_BRIEF", cg_table); queryResult.close(); ps.close(); } else // used in clarification webdynpro, pub_flag != 1 { ps = con.prepareStatement("select * from "+remote_prefix+lv_brief_tbl+" where MANDT = ? AND RFX_ID = ? AND REQUESTER = ?"); ps.setString(1, "800"); ps.setString(2, rfxID); ps.setString(3, requester); ResultSet queryResult = ps.executeQuery(); while (queryResult.next()) { cg_table.appendRow(); cg_table.setValue("MANDT", queryResult.getString("MANDT")); cg_table.setValue("RFX_ID", queryResult.getString("RFX_ID")); cg_table.setValue("ITEM_ID", queryResult.getString("ITEM_ID")); cg_table.setValue("QUS_ID", queryResult.getBytes("QUS_ID")); cg_table.setValue("RFX_DESC", queryResult.getString("RFX_DESC")); cg_table.setValue("ITEM_DESC",queryResult.getString("ITEM_DESC")); cg_table.setValue("QUESTION", queryResult.getString("QUESTION")); cg_table.setValue("REPLY", queryResult.getString("REPLY")); cg_table.setValue("REQUESTER", queryResult.getString("REQUESTER")); cg_table.setValue("BIDDER", queryResult.getString("BIDDER")); cg_table.setValue("BI_COMPANY", queryResult.getString("BI_COMPANY")); cg_table.setValue("REPLYER", queryResult.getString("REPLYER")); cg_table.setValue("RE_DESC",queryResult.getString("RE_DESC")); cg_table.setValue("APPROVER", queryResult.getString("APPROVER")); cg_table.setValue("AP_DESC", queryResult.getString("AP_DESC")); cg_table.setValue("STATUS", queryResult.getString("STATUS")); cg_table.setValue("PUB_FLAG", queryResult.getString("PUB_FLAG")); cg_table.setValue("REQUEST_DAT", queryResult.getString("REQUEST_DAT")); cg_table.setValue("REPLY_DAT", queryResult.getString("REPLY_DAT")); cg_table.setValue("PRD_ID", queryResult.getString("ORDERED_PROD")); cg_table.setValue("QUS_ID_32", queryResult.getString("QUS_ID_32")); } jFunction.getExportParameterList().setValue("ET_RFX_BRIEF", cg_table); queryResult.close(); ps.close(); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace();}} public void rfxItemRead() { JCoTable cg_table = jFunction.getExportParameterList().getTable("ET_RFX_ITEM"); String rfxID = jFunction.getImportParameterList().getString("RFX_ID"); DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; String lv_rfx_tbl = "CRMD_ORDERADM_H"; String lv_item_tbl = "CRMD_ORDERADM_I"; String lv_version = "BBP_PDHGP"; try{ ps = con.prepareStatement("select HEADER.DESCRIPTION, ITEM.NUMBER_INT, ITEM.DESCRIPTION, ITEM.ORDERED_PROD from "+remote_prefix+lv_rfx_tbl+" HEADER, "+remote_prefix+lv_item_tbl+" ITEM, "+remote_prefix+lv_version+" VERSION where HEADER.OBJECT_ID = ? AND ITEM.HEADER = HEADER.GUID AND HEADER.OBJECT_TYPE = ? AND VERSION.GUID = HEADER.GUID AND VERSION.VERSION_TYPE = ' '"); ps.setString(1, rfxID); ps.setString(2, "BUS2200"); ResultSet queryResult = ps.executeQuery(); while (queryResult.next()) { System.out.println("ITEM :"+queryResult.getString(3)); cg_table.appendRow(); cg_table.setValue("RFX_DESCRIPTION", queryResult.getString(1)); cg_table.setValue("ITEM_NUMBER", queryResult.getString(2)); cg_table.setValue("ITEM_DESCRIPTION", queryResult.getString(3)); cg_table.setValue("PRD_ID", queryResult.getString(4)); cg_table.setValue("RFX_ID", rfxID); } jFunction.getExportParameterList().setValue("ET_RFX_ITEM", cg_table); queryResult.close(); ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace();} } public void rfxBriefWrite() { JCoTable questionTbl = jFunction.getImportParameterList().getTable("IT_RFX_QUESTIONS"); System.out.println("!!!"); String[] fieldName = new String[14]; Object[] fieldValue = new Object[14]; String tblName = "ZBID_BRIEF"; DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; PreparedStatement ps_insert = null; PreparedStatement ps_update = null; fieldName[0] = "RFX_ID"; fieldName[1] = "RFX_DESC"; fieldName[2] = "ITEM_ID"; fieldName[3] = "ITEM_DESC"; fieldName[4] = "ORDERED_PROD"; fieldName[5] = "QUS_ID"; fieldName[6] = "QUESTION"; fieldName[7] = "REQUESTER"; fieldName[8] = "BIDDER"; fieldName[9] = "BI_COMPANY"; fieldName[10] = "REQUEST_DAT"; fieldName[11] = "STATUS"; fieldName[12] = "MANDT"; fieldName[13] = "QUS_ID_32"; try{ int rows = questionTbl.getNumRows(); System.out.println("rows = "+ rows); String nameList = ""; String valueList = ""; for(int i=0;i<14;i++) { nameList = nameList + ((i==0)?fieldName[i]:(","+fieldName[i])); valueList = valueList + ((i==0)?"?":",?"); } ps_insert = con.prepareStatement("insert into "+remote_prefix +tblName+" ("+nameList + ") values (" + valueList + ")"); ps_update = con.prepareStatement("update "+remote_prefix+tblName+" SET "+fieldName[11]+" = ? where QUS_ID_32 = ? AND MANDT = ? AND RFX_ID = ? AND ITEM_ID = ?"); do { System.out.println(questionTbl.getValue(1)); fieldValue[0] = questionTbl.getValue("RFX_ID"); fieldValue[1] = questionTbl.getValue("RFX_DESC"); fieldValue[2] = questionTbl.getValue("ITEM_ID"); fieldValue[3] = questionTbl.getValue("ITEM_DESC"); fieldValue[4] = questionTbl.getValue("PRD_ID"); fieldValue[5] = questionTbl.getValue("QUS_ID"); fieldValue[6] = questionTbl.getValue("QUESTION"); fieldValue[7] = questionTbl.getValue("REQUESTER"); fieldValue[8] = questionTbl.getValue("BIDDER"); fieldValue[9] = questionTbl.getValue("BI_COMPANY"); fieldValue[10] = questionTbl.getValue("REQUEST_DAT"); fieldValue[11] = questionTbl.getValue("STATUS"); fieldValue[12] = "800"; fieldValue[13] = questionTbl.getValue("QUS_ID_32"); // Check if new record System.out.println("Question GUID in String: "+fieldValue[13]); ps = con.prepareStatement("select count(*) from "+remote_prefix+tblName+" where MANDT = ? AND RFX_ID = ? AND ITEM_ID = ? AND QUS_ID_32 = ?"); ps.setObject(1, fieldValue[12]); ps.setObject(2, fieldValue[0]); ps.setObject(3, fieldValue[2]); ps.setObject(4, fieldValue[13]); ResultSet rs = ps.executeQuery(); int flag = 0; if (rs.next()) { flag = rs.getInt(1); } rs.close(); ps.close(); if (flag == 0) // Insert { fieldValue[11] = "0"; //New Record, status = 0 for(int i=0;i<14;i++) { if (fieldValue[i] == null) { ps_insert.setObject(i+1, " "); } else { if(fieldValue[i].equals("")) { ps_insert.setObject(i+1, " "); } else { if (fieldName[i] == "REQUEST_DAT") // convert java.util.Date into String { SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd"); String reqDate = sdf.format(questionTbl.getDate("REQUEST_DAT")); //Date sd = new Date(questionTbl.getDate("REQUEST_DAT").getTime()); ps_insert.setString(i+1, reqDate); } else { ps_insert.setObject(i+1, fieldValue[i]); } } } } ps_insert.addBatch(); } else // Update - Only Status { ps_update.setObject(1, fieldValue[11]); ps_update.setObject(2, fieldValue[13]); ps_update.setObject(3, fieldValue[12]); ps_update.setObject(4, fieldValue[0]); ps_update.setObject(5, fieldValue[2]); ps_update.addBatch(); } } while(questionTbl.nextRow()); /** * Output result to Function Module */ int [] resultInsert = ps_insert.executeBatch(); for (int i=0;i<resultInsert.length;i++) { if (resultInsert[i] >= 0 || resultInsert[i] == Statement.SUCCESS_NO_INFO) { System.out.println("insert sucessfully: "+ resultInsert[i]); } else { jFunction.getExportParameterList().setValue("OUT_RC", -1); } } if (jFunction.getExportParameterList().getValue("OUT_RC") == null) { jFunction.getExportParameterList().setValue("OUT_RC", 0); } ps_insert.close(); int[] resultUpdate = ps_update.executeBatch(); for (int i=0;i<resultUpdate.length;i++) { if (resultUpdate[i]>=0 || resultUpdate[i] == Statement.SUCCESS_NO_INFO) { System.out.println("Update successully"+ resultUpdate[i]); } else { jFunction.getExportParameterList().setValue("OUT_RC", -1); } } if (jFunction.getExportParameterList().getValue("OUT_RC") == null) { jFunction.getExportParameterList().setValue("OUT_RC", 0); } ps_update.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace();} } /** * Read successful RFX ID & Description */ public void rfxSucceedRead() { JCoTable tblRfxSucceed = jFunction.getExportParameterList().getTable("ET_RFX_SUCCEED_READ"); DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; String lv_hdr_tbl = "CRMD_ORDERADM_H"; String lv_stat_tbl = "ZSTATUS"; // USE Z table to retrieve status try { // ps = con.prepareStatement("select HEADER.OBJECT_ID, HEADER.DESCRIPTION from "+remote_prefix+lv_hdr_tbl+" HEADER, "+remote_prefix+lv_stat_tbl+" STAT where HEADER.CLIENT = ? and HEADER.OBJECT_TYPE = ? AND HEADER.GUID = STAT.OBJNR AND STAT.STAT = ? order by HEADER.CREATED_AT DESC"); // ps.setString(1, "800"); // ps.setString(2, "BUS2200"); // ps.setString(3, "I1011"); // Adapt to status succceed later ps = con.prepareStatement("select HEADER.OBJECT_ID, HEADER.DESCRIPTION from "+remote_prefix+lv_hdr_tbl+" HEADER, "+remote_prefix+lv_stat_tbl+" STAT where HEADER.CLIENT = ? and HEADER.OBJECT_TYPE = ? AND HEADER.OBJECT_ID = substr(STAT.RFX, 1, 10) AND substr(STAT.RFX, 11, 14) = '0007' AND STAT.STATUS = ? order by HEADER.CREATED_AT DESC"); ps.setString(1, "800"); ps.setString(2, "BUS2200"); ps.setString(3, "A"); ResultSet queryResult = ps.executeQuery(); String objID = ""; String description = ""; while (queryResult.next()) { objID = queryResult.getString(1); description = queryResult.getString(2); //System.out.println("RFX ID:" + objID); tblRfxSucceed.appendRow(); tblRfxSucceed.setValue("RFX_ID", objID); tblRfxSucceed.setValue("DESCRIPTION", description); tblRfxSucceed.setValue("STAT", "I1011"); } jFunction.getExportParameterList().setValue("ET_RFX_SUCCEED_READ", tblRfxSucceed); queryResult.close(); ps.close(); } catch (Exception e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * Read successful RFX detail * */ public void rfxSucceedDetail() { JCoTable tblRfxDetail = jFunction.getExportParameterList().getTable("ET_RFX_SUC_DETAIL"); String rfxID = jFunction.getImportParameterList().getString("RFX_ID"); DBFactory factory = new DBFactory(); Connection con = factory.getConnection(); PreparedStatement ps = null; String tblHeader = "CRMD_ORDERADM_H"; String tblItem = "CRMD_ORDERADM_I"; String tblVersion = "BBP_PDHGP"; String tblRoles = "SRRELROLES"; String tblRoleRel = "BBP_PDBINREL"; String tblPDIGP = "BBP_PDIGP"; String tblBut = "BUT000"; String tblPart = "CRMD_PARTNER"; String tblLink = "CRMD_LINK"; ResultSet queryResult = null; try { ps = con.prepareStatement("select ITEM.GUID, ITEM.NUMBER_INT, ITEM.DESCRIPTION_UC, HEADER.DESCRIPTION FROM "+remote_prefix+tblHeader+" HEADER, "+remote_prefix+tblItem+" ITEM, "+remote_prefix+tblVersion+" VERSION where HEADER.OBJECT_ID =? AND HEADER.GUID = ITEM.HEADER AND HEADER.OBJECT_TYPE = ? AND VERSION.GUID = HEADER.GUID AND VERSION.VERSION_TYPE = ' ' AND HEADER.CLIENT = '800' AND ITEM.CLIENT = '800' AND VERSION.CLIENT = '800'"); ps.setString(1, rfxID); ps.setString(2, "BUS2200"); queryResult = ps.executeQuery(); ArrayList<byte[]> rfxItemGuidList = new ArrayList<byte[]>(); ArrayList<String> rfxItemGuidStrList = new ArrayList<String>(); ArrayList<String> rfxBidderNameList = new ArrayList<String>(); while (queryResult.next()) { tblRfxDetail.appendRow(); tblRfxDetail.setValue("RFX_ID", rfxID); tblRfxDetail.setValue("ITEM_NUM", queryResult.getString(2)); tblRfxDetail.setValue("ITEM_DESC", queryResult.getString(3)); tblRfxDetail.setValue("HDR_DESC", queryResult.getString(4)); rfxItemGuidList.add(queryResult.getBytes(1)); String itemStrGuid = byte2HexStr(queryResult.getBytes(1)); rfxItemGuidStrList.add(itemStrGuid); System.out.println("RFX item GUID: "+itemStrGuid); } queryResult.close(); ps.close(); for(int i=0;i<rfxItemGuidStrList.size();i++) { ps = con.prepareStatement("select REL.ROLE_B FROM "+remote_prefix+tblRoles+" ROLE, "+remote_prefix+tblRoleRel+" REL WHERE ROLE.OBJKEY = ? AND ROLE.ROLEID = REL.ROLE_A AND ROLE.CLIENT = '800' AND REL.CLIENT = '800'"); ps.setString(1, rfxItemGuidStrList.get(i)); queryResult = ps.executeQuery(); ArrayList<String> roleBList = new ArrayList<String>(); while (queryResult.next()) { roleBList.add(queryResult.getString(1)); } queryResult.close(); ps.close(); String awardedResItemGuidStr = ""; for(int j=0;j<roleBList.size();j++) { ps = con.prepareStatement("select OBJKEY from "+remote_prefix+tblRoles+" where ROLEID = ? AND CLIENT = '800'"); ps.setString(1, roleBList.get(j)); queryResult = ps.executeQuery(); String responseItemStrGuid = ""; if (queryResult.next()); { responseItemStrGuid = queryResult.getString(1); System.out.println("Response Item GUID: "+responseItemStrGuid); } queryResult.close(); ps.close(); if (!responseItemStrGuid.equals("")) { ps = con.prepareStatement("select BB.DEDUCT_IND, BB.DEL_IND, BB.ACCEPT_STAT_CODE from "+remote_prefix+tblPDIGP+" BB, "+remote_prefix+tblRoles+" ROLE where BB.GUID = ROLE.OBJKEY AND ROLE.OBJKEY = ? AND BB.CLIENT = '800'"); ps.setString(1, responseItemStrGuid); queryResult = ps.executeQuery(); String deductInd = ""; String delInd = ""; String acceptStatCode = ""; if (queryResult.next()) { deductInd = queryResult.getString(1); delInd = queryResult.getString(2); acceptStatCode = queryResult.getString(3); System.out.println("!!deductInd:"+ deductInd+";del_IND:"+delInd+";acceptCode:"+ acceptStatCode+"!!"); } queryResult.close(); ps.close(); if (deductInd.equals(" ") && delInd.equals(" ") && !acceptStatCode.equals("R")) { System.out.println("Awarded item is found: "+responseItemStrGuid); awardedResItemGuidStr = responseItemStrGuid; break; } } } if (awardedResItemGuidStr.equals("") ) { System.out.println("Awarded vendor is NOT found for Item "+ i); rfxBidderNameList.add("Successful bidder is not found for this item"); continue; } ps = con.prepareStatement("select ITEM.HEADER from "+remote_prefix+tblItem+" ITEM, "+remote_prefix+tblRoles+" ROLE where ITEM.GUID = ROLE.OBJKEY AND ROLE.OBJKEY = ? AND ITEM.CLIENT = '800'"); ps.setString(1, awardedResItemGuidStr); queryResult = ps.executeQuery(); byte[] resHdrGuid = null; String resHdrGuidStr = ""; if (queryResult.next()) { resHdrGuid = queryResult.getBytes(1); resHdrGuidStr = byte2HexStr(resHdrGuid); } queryResult.close(); ps.close(); if (!resHdrGuidStr.equals("")) { ps = con.prepareStatement("select BU.NAME_ORG1 from "+remote_prefix+tblBut+" BU, "+remote_prefix+tblPart+" PART, "+remote_prefix+tblHeader+" HEADER, "+remote_prefix+tblLink+" LINK, "+remote_prefix+tblRoles+" ROLE where PART.PARTNER_NO = BU.PARTNER_GUID AND HEADER.GUID = LINK.GUID_HI AND PART.GUID = LINK.GUID_SET AND LINK.OBJTYPE_SET = '07' AND PART.PARTNER_FCT = '00000018' AND HEADER.GUID = ROLE.OBJKEY AND ROLE.OBJKEY = ? AND HEADER.CLIENT = '800'"); ps.setString(1, resHdrGuidStr); queryResult = ps.executeQuery(); if (queryResult.next()) { String orgName = queryResult.getString(1); rfxBidderNameList.add(orgName); System.out.println("Awarded bidder found for item "+i+": "+orgName); } queryResult.close(); ps.close(); } } for(int i=0;i<rfxBidderNameList.size();i++) { tblRfxDetail.setRow(i); tblRfxDetail.setValue("BIDDER_NAME", rfxBidderNameList.get(i)); } jFunction.getExportParameterList().setValue("ET_RFX_SUC_DETAIL", tblRfxDetail); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } /** * Convert byte array to string * @param byte array * @return string */ public static String byte2HexStr(byte[] b) { String hs=""; String stmp=""; for (int n=0;n<b.length;n++) { stmp=(Integer.toHexString(b[n] & 0XFF)); if (stmp.length()==1) hs=hs+"0"+stmp; else hs=hs+stmp; //if (n<b.length-1) hs=hs+":"; } return hs.toUpperCase(); } /** * Convert hex string to byte[] * @param hex String * @return byte[] */ /** * public static byte[] hexStr2Bytes(String src) { int m=0,n=0; int l=src.length()/2; System.out.println(l); byte[] ret = new byte[l]; for (int i = 0; i < l; i++) { m=i*2+1; n=m+1; ret[i] = uniteBytes(src.substring(i*2, m),src.substring(m,n)); } return ret; } private static byte uniteBytes(String src0, String src1) { byte b0 = Byte.decode("0x" + src0).byteValue(); b0 = (byte) (b0 << 4); byte b1 = Byte.decode("0x" + src1).byteValue(); byte ret = (byte) (b0 | b1); return ret; } */ private void ProcessTableUpdate(ResultSet queryResult, String lv_table_name, String[] str_keys, Connection con){ try { String str_condition = " where "; System.out.print("Key for table:"); for (int i = 0; i < str_keys.length;i++) { str_condition = str_condition + ((i==0)?"":" and ") + str_keys[i] + " = ? "; } System.out.println(); int count = 0; PreparedStatement ps_insert = null; PreparedStatement ps_update = null; //Retrieve Meta Data ResultSetMetaData oMetaData = queryResult.getMetaData(); int i_columns = oMetaData.getColumnCount(); System.out.println("The number of columns for table " + lv_table_name + " is " + i_columns); String[] str_columns = new String[i_columns]; String field_list = ""; String value_list = ""; String update_list = ""; System.out.print("Fields List:"); for(int i=0; i< i_columns; i++){ str_columns[i] = oMetaData.getColumnName(i+1); System.out.print(str_columns[i]); field_list = field_list + ((i==0)?str_columns[i]:(","+str_columns[i])); value_list = value_list + ((i==0)?"?":",?"); } System.out.println(); for(int i=0; i< i_columns; i++){ update_list = update_list + ((i==0)?"":", ") + str_columns[i] + " = ? "; } //Prepare insert statement String sql = "insert into "+ remote_prefix +lv_table_name + " ("+field_list + ") values (" + value_list + ")"; System.out.println(sql); ps_insert = con.prepareStatement(sql); //Prepare update statement sql = "update "+ remote_prefix +lv_table_name + " set " + update_list + str_condition; System.out.println(sql); ps_update = con.prepareStatement(sql); while (queryResult.next()) { count++; //Check whether the data record exists PreparedStatement ps = null; sql= "select count(*) from "+ remote_prefix + lv_table_name + str_condition; ps = con.prepareStatement(sql); Object[] obj_keys_value = new Object[str_keys.length]; for(int i=0;i< str_keys.length;i++){ obj_keys_value[i] = queryResult.getObject(str_keys[i]); ps.setObject(i+1, obj_keys_value[i]); } ResultSet queryResult1 = ps.executeQuery(); int flag = 0; if(queryResult1.next()){ flag = queryResult1.getInt(1); } ps.close(); if(flag==0){ //Insert new record for(int j=0;j<str_columns.length; j++){ ps_insert.setObject(j+1, queryResult.getObject(str_columns[j])); } ps_insert.addBatch(); }else{ //Update existing record for(int j=0;j<str_columns.length; j++){ ps_update.setObject(j+1, queryResult.getObject(str_columns[j])); } for(int j=0; j<str_keys.length; j++){ ps_update.setObject(j+1+ str_columns.length, obj_keys_value[j]); } ps_update.addBatch(); } } //with batch ps_insert.executeBatch(); ps_insert.close(); ps_update.executeBatch(); ps_update.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } } private void ProcessTableInsert(ResultSet queryResult, String lv_table_name, Connection local_conn){ try { WriteLogln("Processing table " + lv_table_name); ResultSetMetaData oMetaData = queryResult.getMetaData(); int i_columns = oMetaData.getColumnCount(); WriteLogln("The number of columns for table " + lv_table_name + " is " + i_columns); String[] str_columns = new String[i_columns]; String field_list = ""; String value_list = ""; WriteLog("Fields List:"); for(int i=0; i< i_columns; i++){ str_columns[i] = oMetaData.getColumnName(i+1); WriteLog(str_columns[i]+" "); field_list = field_list + ((i==0)?str_columns[i]:(","+str_columns[i])); value_list = value_list + ((i==0)?"?":",?"); } WriteLogln(""); //Prepare statement for local insert String sql = "insert into " + local_prefix + lv_table_name + " ("+field_list + ") values (" + value_list + ")"; WriteLogln(sql); PreparedStatement local_ps = local_conn.prepareStatement(sql); int count = 0; while (queryResult.next()) { count++; for(int j=0;j<str_columns.length; j++){ local_ps.setObject(j+1, queryResult.getObject(str_columns[j])); } //add to batch local_ps.addBatch(); } WriteLogln("Total records:" + count); //execute the batch local_ps.executeBatch(); local_ps.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); WriteLogln(e.getMessage()); WriteLogln(e.getSQLState()); } } }
