public static boolean Tendmaschkexists(String entitykey, String frdatabase) throws SQLException {
/*
* String[] parts = entitykey.split(","); String txdate = parts[0];
* String loccode = parts[1]; String regno = parts[2]; String txno =
* parts[3]; String seqno = parts[4];
*/
boolean result = false;
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
try {
if (Objects.equals(frdatabase, "Oracle")) {
// dbConnection = Mssql.getDBConnection();
HikariRms Rmspool = HikariRms.getInstance();
dbConnection = Rmspool.getConnection();
} else {
// dbConnection = OracleTo.getDBConnection();
HikariQracleTo OrcaleFromTo = HikariQracleTo.getInstance();
dbConnection = OrcaleFromTo.getConnection();
}
String selectSQL =
"SELECT TENDER " + "FROM Tendmas " + "where ENTITY_KEY ='" + entitykey + "'";
// logger.info(selectSQL);
preparedStatement = dbConnection.prepareStatement(selectSQL);
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
String rschktxdate = rs.getString("TENDER");
// logger.info(rschktxdate);
if (rschktxdate != null) {
result = true; // not exists
}
}
return result;
} catch (SQLException e) {
logger.info(e.getMessage());
result = false;
return result;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
public static void routeSize(
String dataupdatelog, String entityname, String entitykey, String database)
throws SQLException {
/*
* String[] parts = entitykey.split(","); String txdate = parts[0];
* String loccode = parts[1]; String regno = parts[2]; String txno =
* parts[3]; String seqno = parts[4];
*/
String[] parts = entitykey.split(",");
String size_set = parts[0];
String size_code = parts[1];
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String selectSQL;
// List<Sahdr> sahdrs = new ArrayList<Sahdr>();
try {
if (Objects.equals(database, "Oracle")) {
// dbConnection = OracleFrom.getDBConnection();
HikariQracleFrom OrcaleFrompool = HikariQracleFrom.getInstance();
dbConnection = OrcaleFrompool.getConnection();
selectSQL =
"SELECT SIZE_SET,SIZE_CODE,SIZE_DESC,LAST_UPD_DT,ENTITY_KEY "
+ "FROM SIZE_ "
+ "where size_set ='"
+ size_set
+ "'"
+ "and size_code ='"
+ size_code
+ "'"
+ "Order BY LAST_UPD_DT";
} else {
// dbConnection = Mssql.getDBConnection();
HikariRms Rmspool = HikariRms.getInstance();
dbConnection = Rmspool.getConnection();
selectSQL =
"SELECT SIZE_SET,SIZE_CODE,SIZE_DESC,LAST_UPD_DT,ENTITY_KEY "
+ "FROM Size "
+ "where size_set ='"
+ size_set
+ "'"
+ "and size_code ='"
+ size_code
+ "'"
+ "Order BY LAST_UPD_DT";
}
preparedStatement = dbConnection.prepareStatement(selectSQL);
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
String rssizeset = rs.getString("SIZE_SET");
String rssizecode = rs.getString("SIZE_CODE");
String rssizedesc = rs.getString("SIZE_DESC");
Timestamp rslastupddt = rs.getTimestamp("LAST_UPD_DT");
String rsentitykey = rs.getString("ENTITY_KEY");
// logger.info(rslastupddt);
boolean Chkresult = InsertSize.Sizechkexists(entitykey, database);
if (!Chkresult) {
// logger.info("insert");
boolean Insertresult =
InsertSize.Sizeinsert(
rssizeset, rssizecode, rssizedesc, rslastupddt, rsentitykey, database);
if (Insertresult) {
logger.info("Size: 1 row has been inserted. Key:" + entitykey);
} else {
logger.info("Insert Error");
}
Logupdateresult.Updatelogresult(dataupdatelog, entityname, Insertresult, database);
if ((!"Oracle".equals(database)) && (Insertresult)) {
Insertdataupdatelog.Updatelogresult(entityname, rsentitykey);
}
} else {
// logger.info("update");
boolean Insertresult =
InsertSize.Sizeupdate(
rssizeset, rssizecode, rssizedesc, rslastupddt, rsentitykey, database);
if (Insertresult) {
logger.info("Size: 1 row has been updated. Key:" + entitykey);
} else {
logger.info("Update Error");
}
Logupdateresult.Updatelogresult(dataupdatelog, entityname, Insertresult, database);
if ((!"Oracle".equals(database)) && (Insertresult)) {
Insertdataupdatelog.Updatelogresult(entityname, rsentitykey);
}
}
}
} catch (SQLException e) {
logger.info(e.getMessage());
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
public static boolean Tendmasupdate(
String rstender,
String rstendername,
String rscardprefix,
String rstendergroup,
String rsifxref,
String rsismanual,
String rsbankhost,
String rscommrate,
String rsmask,
String rsmaskenabled,
String rsdbmask,
String rsdbmaskenabled,
String rsexpirymask,
String rsexpirymaskenabled,
String rsmanualentry,
Timestamp rslastupddt,
String rsentitykey,
String frdatabase)
throws SQLException {
boolean result = true;
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String updateSQL;
updateSQL =
"UPDATE TENDMAS "
+ "SET TENDER = ? "
+ ", TENDER_NAME = ? "
+ ", CARD_PREFIX = ? "
+ ", TENDER_GROUP = ? "
+ ", IFX_REF = ? "
+ ", IS_MANUAL = ? "
+ ", BANK_HOST = ? "
+ ", COMM_RATE = ? "
+ ", MASK = ? "
+ ", MASK_ENABLED = ? "
+ ", DBMASK = ? "
+ ", DBMASK_ENABLED = ? "
+ ", EXPIRY_MASK = ? "
+ ", EXPIRY_MASK_ENABLED = ? "
+ ", MANUAL_ENTRY = ? "
+ ", LAST_UPD_DT = ? "
+ "WHERE ENTITY_KEY = ? ";
try {
if (Objects.equals(frdatabase, "Oracle")) {
// dbConnection = Mssql.getDBConnection();
HikariRms Rmspool = HikariRms.getInstance();
dbConnection = Rmspool.getConnection();
} else {
// dbConnection = OracleTo.getDBConnection();
HikariQracleTo OrcaleFromTo = HikariQracleTo.getInstance();
dbConnection = OrcaleFromTo.getConnection();
}
preparedStatement = dbConnection.prepareStatement(updateSQL);
preparedStatement.setString(1, rstender);
preparedStatement.setString(2, rstendername);
preparedStatement.setString(3, rscardprefix);
preparedStatement.setString(4, rstendergroup);
preparedStatement.setString(5, rsifxref);
preparedStatement.setString(6, rsismanual);
preparedStatement.setString(7, rsbankhost);
preparedStatement.setString(8, rscommrate);
preparedStatement.setString(9, rsmask);
preparedStatement.setString(10, rsmaskenabled);
preparedStatement.setString(11, rsdbmask);
preparedStatement.setString(12, rsdbmaskenabled);
preparedStatement.setString(13, rsexpirymask);
preparedStatement.setString(14, rsexpirymaskenabled);
preparedStatement.setString(15, rsmanualentry);
preparedStatement.setTimestamp(16, rslastupddt);
preparedStatement.setString(17, rsentitykey);
preparedStatement.executeUpdate();
return result;
} catch (SQLException e) {
logger.info(e.getMessage());
result = false;
logger.info(false);
return result;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
public static boolean Tendmasinsert(
String rstender,
String rstendername,
String rscardprefix,
String rstendergroup,
String rsifxref,
String rsismanual,
String rsbankhost,
String rscommrate,
String rsmask,
String rsmaskenabled,
String rsdbmask,
String rsdbmaskenabled,
String rsexpirymask,
String rsexpirymaskenabled,
String rsmanualentry,
Timestamp rslastupddt,
String rsentitykey,
String frdatabase)
throws SQLException {
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String insertTableSQL;
insertTableSQL =
"INSERT INTO tendmas (TENDER,TENDER_NAME,CARD_PREFIX,TENDER_GROUP,IFX_REF,IS_MANUAL,BANK_HOST,COMM_RATE,"
+ "MASK,MASK_ENABLED,DBMASK,DBMASK_ENABLED,EXPIRY_MASK,EXPIRY_MASK_ENABLED,MANUAL_ENTRY,LAST_UPD_DT,ENTITY_KEY) "
+ "VALUES ( ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
try {
if (Objects.equals(frdatabase, "Oracle")) {
// dbConnection = Mssql.getDBConnection();
HikariRms Rmspool = HikariRms.getInstance();
dbConnection = Rmspool.getConnection();
} else {
// dbConnection = OracleTo.getDBConnection();
HikariQracleTo OrcaleFromTo = HikariQracleTo.getInstance();
dbConnection = OrcaleFromTo.getConnection();
}
preparedStatement = dbConnection.prepareStatement(insertTableSQL);
preparedStatement.setString(1, rstender);
preparedStatement.setString(2, rstendername);
preparedStatement.setString(3, rscardprefix);
preparedStatement.setString(4, rstendergroup);
preparedStatement.setString(5, rsifxref);
preparedStatement.setString(6, rsismanual);
preparedStatement.setString(7, rsbankhost);
preparedStatement.setString(8, rscommrate);
preparedStatement.setString(9, rsmask);
preparedStatement.setString(10, rsmaskenabled);
preparedStatement.setString(11, rsdbmask);
preparedStatement.setString(12, rsdbmaskenabled);
preparedStatement.setString(13, rsexpirymask);
preparedStatement.setString(14, rsexpirymaskenabled);
preparedStatement.setString(15, rsmanualentry);
preparedStatement.setTimestamp(16, rslastupddt);
preparedStatement.setString(17, rsentitykey);
preparedStatement.executeUpdate();
return true;
} catch (SQLException e) {
logger.info(e.getMessage());
return false;
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}
public static void routeSxboxpos(
String dataupdatelog, String entityname, String entitykey, String database)
throws SQLException {
/*
* String[] parts = entitykey.split(","); String txdate = parts[0];
* String loccode = parts[1]; String regno = parts[2]; String txno =
* parts[3]; String seqno = parts[4];
*/
Connection dbConnection = null;
PreparedStatement preparedStatement = null;
String selectSQL;
selectSQL =
"SELECT BOXNO,SENDER,P_RECVR,A_RECVR,T_SEN_QTY,T_REC_QTY,S_LDT_ID,R_LDT_ID,SEN_STATUS,REC_STATUS,"
+ "BOX_STATUS,SEN_SCAN_DT,REC_SCAN_DT,SEN_LOAD_DT,REC_LOAD_DT,SEN_CMT_DT,REC_CMT_DT,PLAN_TX_DT,BOX_SEN_DT,"
+ "DEPT,TYPE,FLAG1,FLAG2,FLAG3,DT1,DT2,DT3,CUSTOM1,REMARKS,LAST_UPD_USR,LAST_UPD_DT,LAST_UPD_VER,ROWGUID,SEN_COMP,"
+ "P_REC_COMP,A_REC_COMP,COMPANY_CDE,ENTITY_KEY "
+ "FROM RMSADMIN.sx_box_pos "
+ "where entity_key ='"
+ entitykey
+ "'"
+ "Order BY LAST_UPD_DT";
// List<Sahdr> sahdrs = new ArrayList<Sahdr>();
try {
if (Objects.equals(database, "Oracle")) {
// dbConnection = OracleFrom.getDBConnection();
HikariQracleFrom OrcaleFrompool = HikariQracleFrom.getInstance();
dbConnection = OrcaleFrompool.getConnection();
} else {
// dbConnection = Mssql.getDBConnection();
HikariRms Rmspool = HikariRms.getInstance();
dbConnection = Rmspool.getConnection();
}
preparedStatement = dbConnection.prepareStatement(selectSQL);
ResultSet rs = preparedStatement.executeQuery();
while (rs.next()) {
String rsboxno = rs.getString("BOXNO");
String rssender = rs.getString("SENDER");
String rsprecvr = rs.getString("P_RECVR");
String rsarecvr = rs.getString("A_RECVR");
String rstsenqty = rs.getString("T_SEN_QTY");
String rstrecqty = rs.getString("T_REC_QTY");
String rssldtid = rs.getString("S_LDT_ID");
String rsrldtid = rs.getString("R_LDT_ID");
String rssenstatus = rs.getString("SEN_STATUS");
String rsrecstatus = rs.getString("REC_STATUS");
String rsboxstatus = rs.getString("BOX_STATUS");
Timestamp rssenscandt = rs.getTimestamp("SEN_SCAN_DT");
Timestamp rsrecscandt = rs.getTimestamp("REC_SCAN_DT");
Timestamp rssenloaddt = rs.getTimestamp("SEN_LOAD_DT");
Timestamp rsrecloaddt = rs.getTimestamp("REC_LOAD_DT");
Timestamp rssencmtdt = rs.getTimestamp("SEN_CMT_DT");
Timestamp rsreccmtdt = rs.getTimestamp("REC_CMT_DT");
Timestamp rsplantxdt = rs.getTimestamp("PLAN_TX_DT");
Timestamp rsboxsendt = rs.getTimestamp("BOX_SEN_DT");
String rsdept = rs.getString("DEPT");
String rstype = rs.getString("TYPE");
String rsflag1 = rs.getString("FLAG1");
String rsflag2 = rs.getString("FLAG2");
String rsflag3 = rs.getString("FLAG3");
Timestamp rsdt1 = rs.getTimestamp("DT1");
Timestamp rsdt2 = rs.getTimestamp("DT2");
Timestamp rsdt3 = rs.getTimestamp("DT3");
String rscustom1 = rs.getString("CUSTOM1");
String rsremarks = rs.getString("REMARKS");
String rslastupdusr = rs.getString("LAST_UPD_USR");
Timestamp rslastupddt = rs.getTimestamp("LAST_UPD_DT");
String rslastupdver = rs.getString("LAST_UPD_VER");
String rsrowguid = rs.getString("ROWGUID");
String rssencomp = rs.getString("SEN_COMP");
String rspreccomp = rs.getString("P_REC_COMP");
String rsareccomp = rs.getString("A_REC_COMP");
String rscompanycde = rs.getString("COMPANY_CDE");
String rsentitykey = rs.getString("ENTITY_KEY");
// logger.info(rslastupddt);
boolean Chkresult = InsertSxboxpos.Sxboxposchkexists(entitykey, database);
if (!Chkresult) {
// logger.info("insert");
boolean Insertresult =
InsertSxboxpos.Sxboxposinsert(
rsboxno,
rssender,
rsprecvr,
rsarecvr,
rstsenqty,
rstrecqty,
rssldtid,
rsrldtid,
rssenstatus,
rsrecstatus,
rsboxstatus,
rssenscandt,
rsrecscandt,
rssenloaddt,
rsrecloaddt,
rssencmtdt,
rsreccmtdt,
rsplantxdt,
rsboxsendt,
rsdept,
rstype,
rsflag1,
rsflag2,
rsflag3,
rsdt1,
rsdt2,
rsdt3,
rscustom1,
rsremarks,
rslastupdusr,
rslastupddt,
rslastupdver,
rsrowguid,
rssencomp,
rspreccomp,
rsareccomp,
rscompanycde,
rsentitykey,
database);
if (Insertresult) {
logger.info("Sxboxpos: 1 row has been inserted. Key:" + entitykey);
} else {
logger.info("Insert Error");
}
Logupdateresult.Updatelogresult(dataupdatelog, entityname, Insertresult, database);
if ((!"Oracle".equals(database)) && (Insertresult)) {
Insertdataupdatelog.Updatelogresult(entityname, rsentitykey);
}
} else {
// logger.info("update");
boolean Insertresult =
InsertSxboxpos.Sxboxposupdate(
rsboxno,
rssender,
rsprecvr,
rsarecvr,
rstsenqty,
rstrecqty,
rssldtid,
rsrldtid,
rssenstatus,
rsrecstatus,
rsboxstatus,
rssenscandt,
rsrecscandt,
rssenloaddt,
rsrecloaddt,
rssencmtdt,
rsreccmtdt,
rsplantxdt,
rsboxsendt,
rsdept,
rstype,
rsflag1,
rsflag2,
rsflag3,
rsdt1,
rsdt2,
rsdt3,
rscustom1,
rsremarks,
rslastupdusr,
rslastupddt,
rslastupdver,
rsrowguid,
rssencomp,
rspreccomp,
rsareccomp,
rscompanycde,
rsentitykey,
database);
if (Insertresult) {
logger.info("Sxboxpos: 1 row has been updated. Key:" + entitykey);
} else {
logger.info("Update Error");
}
Logupdateresult.Updatelogresult(dataupdatelog, entityname, Insertresult, database);
if ((!"Oracle".equals(database)) && (Insertresult)) {
Insertdataupdatelog.Updatelogresult(entityname, rsentitykey);
}
}
}
} catch (SQLException e) {
logger.info(e.getMessage());
} finally {
if (preparedStatement != null) {
preparedStatement.close();
}
if (dbConnection != null) {
dbConnection.close();
}
}
}