Example #1
0
  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();
   }
 }