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());
}
}
}
Ian在北京做的CFCA的Java代码
【版权声明】本文内容来自摩杜云社区用户原创、第三方投稿、转载，内容版权归原作者所有。本网站的目的在于传递更多信息，不拥有版权，亦不承担相应法律责任。如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱： cloudbbs@moduyun.com
最后一次编辑于 2024年08月02日
0
暂无评论
推荐阅读