public String[] send() {
String ret[] = {"0", "internal_problem"};
if (!this.validateInput()) {
ret[1] = "input_error";
return ret;
}
Connection con = null;
try {
con = DbCon.getConnection();
con.setAutoCommit(false);
String sql =
"select c.acc_no, t.status from customer c, tsel_cust_account t where c.msisdn = ? and t.acc_no = c.acc_no";
PreparedStatement ps = con.prepareStatement(sql);
ps.setString(1, this.msisdn);
ResultSet rs = ps.executeQuery();
String acc_no = "";
String status = "";
boolean b1 = false;
boolean b2 = false;
boolean b3 = false;
if (rs.next()) {
acc_no = rs.getString(1);
b1 = true;
status = rs.getString(2);
if (status != null && status.equals("1")) {
b2 = true;
} else {
ret[1] = "account_not_active";
}
}
rs.close();
ps.close();
double balance_cust = 0;
double balance_merchant = 0;
if (b1 && b2) {
sql = "select * from tsel_cust_account where acc_no = ? for update";
ps = con.prepareStatement(sql);
ps.setString(1, acc_no);
rs = ps.executeQuery();
if (rs.next()) {
b3 = true;
balance_cust = rs.getDouble("balance");
} else {
throw new Exception("acc_no not found " + msisdn + " accno:" + acc_no);
}
rs.close();
ps.close();
if (b3) {
sql = "update tsel_cust_account set balance=balance+? where acc_no = ?";
ps = con.prepareStatement(sql);
ps.setString(1, this.amount);
ps.setString(2, acc_no);
ps.executeUpdate();
String db_trxid = this.getId(con);
// history tsel_account
sql =
"insert into tsel_cust_account_history values(?, ?, ?, ?, ?, ?, ?, sysdate, ?, ?, ?)";
ps = con.prepareStatement(sql);
ps.setString(1, db_trxid);
ps.setString(2, "txid");
ps.setString(3, acc_no);
ps.setString(4, String.valueOf(UssdTx.TSEL_CASH_IN));
ps.setString(5, "0");
ps.setString(6, this.amount);
ps.setDouble(7, (balance_cust + Double.parseDouble(this.amount)));
ps.setString(8, this.loadingTerminalId);
ps.setString(9, "");
ps.setString(10, this.userlogin);
ps.executeUpdate();
ps.close();
// end of history tsel_account
// merchant_id, merchant_acc
String merchant_acc_no = "";
sql =
"select m.acc_no from merchant m, loading_terminal l where terminal_id = ? and l.merchant_id = m.merchant_id";
ps = con.prepareStatement(sql);
ps.setString(1, this.loadingTerminalId);
rs = ps.executeQuery();
if (rs.next()) {
merchant_acc_no = rs.getString(1);
} else {
throw new Exception("merchantid not found " + this.loadingTerminalId);
}
rs.close();
ps.close();
sql = "select balance from tsel_merchant_account where acc_no = ? for update ";
ps = con.prepareStatement(sql);
ps.setString(1, merchant_acc_no);
rs = ps.executeQuery();
if (rs.next()) {
balance_merchant = rs.getDouble(1);
} else {
throw new Exception("merchant_acc_no not exist");
}
rs.close();
ps.close();
sql = "update tsel_merchant_account set balance=balance-? where acc_no = ?";
ps = con.prepareStatement(sql);
ps.setString(1, this.amount);
ps.setString(2, merchant_acc_no);
ps.executeUpdate();
ps.close();
sql =
"insert into tsel_merchant_account_history values(?, ?, ?, ?, ?, ?, ?, ?, sysdate, ?, ?, ?)";
ps = con.prepareStatement(sql);
ps.setString(1, db_trxid);
ps.setString(2, "txid");
ps.setString(3, merchant_acc_no);
ps.setString(4, this.msisdn);
ps.setString(5, String.valueOf(UssdTx.TSEL_CASH_IN));
ps.setString(6, this.amount);
ps.setString(7, "0");
ps.setDouble(8, (balance_merchant - Double.parseDouble(this.amount)));
ps.setString(9, this.loadingTerminalId);
ps.setString(10, "");
ps.setString(11, this.userlogin);
ps.executeUpdate();
ps.close();
// end of merchant_account history
con.commit();
ret[0] = "1";
ret[1] = "Success";
} else {
con.rollback();
}
} else {
if (!b1) {
ret[1] = "account_not_found";
}
con.rollback();
}
} catch (Exception e) {
e.printStackTrace(System.out);
try {
con.rollback();
} catch (Exception e2) {
}
} finally {
if (con != null) {
try {
con.setAutoCommit(true);
} catch (Exception e4) {
}
}
if (con != null) {
try {
con.close();
} catch (Exception e3) {
}
}
}
return ret;
}
/**
* Saves the templates to the database.
*
* @throws java.sql.SQLException Thrown on sql error.
*/
public void saveToDatabase() throws java.sql.SQLException {
setProgressIndeterminate(true);
setMessage("Saving Templates");
ArrayList templates = getTemplates();
Connection oracleConnection = getDataSource().getConnection();
try {
oracleConnection.setAutoCommit(false);
Statement query = oracleConnection.createStatement();
try {
int templateCount = templates.size();
// First remove any existing entries.
StringBuffer sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
StringBuffer whereClause = new StringBuffer(" WHERE TMPL_ID IN (");
for (int i = 0; i < templateCount; i++) {
if (i > 0) whereClause.append(", ");
whereClause.append("'");
whereClause.append(((Template) templates.get(i)).getID());
whereClause.append("'");
}
whereClause.append(")");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_REC");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_SGNL_FLD");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TEMPLATE");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("DELETE FROM ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP_ARCH_REQ");
sql.append(whereClause);
query.execute(sql.toString());
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TEMPLATE (TMPL_ID, TMPL_DESC, EXT_SRC_FILE_NM, EXT_SRC_FILE_MOD_DTE) VALUES (?, ?, ?, ?)");
PreparedStatement templateInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_MACRO (TMPL_ID, MACRO_ID) VALUES (?, ?)");
PreparedStatement macroInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC (TMPL_ID, TMPL_SGNL_ID, REC_TYPE_ID, ARCH_IND, ARCH_FREQ, ARCH_TYPE) VALUES (?, ?, ?, ?, ?, ?)");
PreparedStatement signalInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_FLD (TMPL_ID, TMPL_SGNL_ID, FLD_ID, REC_TYPE_ID, VAL) VALUES (?, ?, ?, ?, ?)");
PreparedStatement fieldInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ (TMPL_ID, ARCH_REQ_FILE_NM) VALUES (?, ?)");
PreparedStatement requestInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(".TMPL_ARCH_REQ_GRP (TMPL_ID, ARCH_REQ_GRP_FILE_NM) VALUES (?, ?)");
PreparedStatement groupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("INSERT INTO ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_ARCH_REQ_GRP_ARCH_REQ (TMPL_ID, ARCH_REQ_GRP_FILE_NM, ARCH_REQ_FILE_NM) VALUES (?, ?, ?)");
PreparedStatement requestGroupInsertStatement =
oracleConnection.prepareStatement(sql.toString());
try {
sql = new StringBuffer("UPDATE ");
sql.append(MPSBrowserView.SCHEMA);
sql.append(
".TMPL_SGNL_REC SET ARCH_IND = ?, ARCH_FREQ = ?, ARCH_TYPE = ?, ARCH_REQ_FILE = ? WHERE TMPL_ID = ? AND TMPL_SGNL_ID = ? AND REC_TYPE_ID = ?");
PreparedStatement signalUpdateStatement =
oracleConnection.prepareStatement(sql.toString());
try {
int progress = 0;
setProgressMaximum(importedFieldCount + importedMacroCount);
setProgressValue(0);
setProgressIndeterminate(false);
for (int templateIndex = 0;
templateIndex < templateCount;
templateIndex++) {
Template currentTemplate = (Template) templates.get(templateIndex);
String currentTemplateID = currentTemplate.getID();
templateInsertStatement.setString(1, currentTemplateID);
String currentDescription = currentTemplate.getDescription();
if (currentDescription == null)
templateInsertStatement.setNull(2, Types.VARCHAR);
else templateInsertStatement.setString(2, currentDescription);
templateInsertStatement.setString(3, currentTemplate.getFileName());
templateInsertStatement.setTimestamp(
4, currentTemplate.getFileModifiedDate());
templateInsertStatement.execute();
// Need to insert macros.
int macroCount = currentTemplate.getMacroCount();
for (int macroIndex = 0; macroIndex < macroCount; macroIndex++) {
macroInsertStatement.setString(1, currentTemplateID);
String currentMacro = currentTemplate.getMacroAt(macroIndex);
macroInsertStatement.setString(2, currentMacro);
macroInsertStatement.execute();
setProgressValue(++progress);
}
int signalCount = currentTemplate.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentTemplate.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
String currentRecordTypeID =
currentSignal.getType().getRecordType().getID();
signalInsertStatement.setString(1, currentTemplateID);
signalInsertStatement.setString(2, currentSignalID);
signalInsertStatement.setString(3, currentRecordTypeID);
signalInsertStatement.setString(4, currentSignal.getArchiveIndicator());
BigDecimal currentFrequency = currentSignal.getArchiveFrequency();
if (currentFrequency == null)
currentFrequency = new BigDecimal("60"); // 60 default in RDB
signalInsertStatement.setBigDecimal(5, currentFrequency);
String currentType = currentSignal.getArchiveType();
if (currentType == null)
currentType = "Monitor"; // 'Monitor' default in RDB
signalInsertStatement.setString(6, currentType);
signalInsertStatement.execute();
int fieldCount = currentSignal.getFieldCount();
for (int fieldIndex = 0; fieldIndex < fieldCount; fieldIndex++) {
SignalField currentField = currentSignal.getFieldAt(fieldIndex);
fieldInsertStatement.setString(1, currentTemplateID);
fieldInsertStatement.setString(2, currentSignalID);
fieldInsertStatement.setString(3, currentField.getType().getID());
fieldInsertStatement.setString(4, currentRecordTypeID);
fieldInsertStatement.setString(5, currentField.getValue());
fieldInsertStatement.execute();
if (isParseCanceled()) {
oracleConnection.rollback();
return;
}
setProgressValue(++progress);
}
}
// Insert archive requests.
int requestCount = currentTemplate.getArchiveRequestCount();
for (int requestIndex = 0; requestIndex < requestCount; requestIndex++) {
ArchiveRequest currentRequest =
currentTemplate.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestInsertStatement.setString(1, currentTemplateID);
requestInsertStatement.setString(2, currentRequestFileName);
requestInsertStatement.execute();
signalCount = currentRequest.getSignalCount();
for (int signalIndex = 0; signalIndex < signalCount; signalIndex++) {
Signal currentSignal = currentRequest.getSignalAt(signalIndex);
String currentSignalID = currentSignal.getID();
signalUpdateStatement.setString(
1, currentSignal.getArchiveIndicator());
signalUpdateStatement.setBigDecimal(
2, currentSignal.getArchiveFrequency());
signalUpdateStatement.setString(3, currentSignal.getArchiveType());
signalUpdateStatement.setString(4, currentRequestFileName);
signalUpdateStatement.setString(5, currentTemplateID);
signalUpdateStatement.setString(6, currentSignalID);
signalUpdateStatement.setString(
7, currentSignal.getType().getRecordType().getID());
signalUpdateStatement.execute();
int fieldCount = currentSignal.getFieldCount();
}
}
// Insert archive groups.
int groupCount = currentTemplate.getArchiveGroupCount();
for (int groupIndex = 0; groupIndex < groupCount; groupIndex++) {
ArchiveGroup currentGroup =
currentTemplate.getArchiveGroupAt(groupIndex);
groupInsertStatement.setString(1, currentTemplateID);
String currentGroupFileName = currentGroup.getFileName();
groupInsertStatement.setString(2, currentGroupFileName);
groupInsertStatement.execute();
requestCount = currentGroup.getArchiveRequestCount();
for (int requestIndex = 0;
requestIndex < requestCount;
requestIndex++) {
ArchiveRequest currentRequest =
currentGroup.getArchiveRequestAt(requestIndex);
String currentRequestFileName = currentRequest.getFileName();
requestGroupInsertStatement.setString(1, currentTemplateID);
requestGroupInsertStatement.setString(2, currentGroupFileName);
requestGroupInsertStatement.setString(
3, currentRequest.getFileName());
}
}
}
} finally {
signalUpdateStatement.close();
}
} finally {
requestGroupInsertStatement.close();
}
} finally {
groupInsertStatement.close();
}
} finally {
requestInsertStatement.close();
}
} finally {
fieldInsertStatement.close();
}
} finally {
signalInsertStatement.close();
}
} finally {
macroInsertStatement.close();
}
} finally {
templateInsertStatement.close();
}
} catch (java.sql.SQLException ex) {
oracleConnection.rollback();
throw ex;
} finally {
query.close();
}
if (isParseCanceled()) oracleConnection.rollback();
else oracleConnection.commit();
} finally {
oracleConnection.close();
}
}