private int insertData(
      String user_id,
      String service_id,
      String command_code,
      String mlist,
      MsgObject msgObject,
      String mtfree,
      int msgtype,
      String Service_ss_id,
      long lduration) {
    int ireturn = 1;
    Connection connection = null;
    DBPool dbpool = new DBPool();
    // long lmilisec = System.currentTimeMillis();
    String sqlInsert =
        "Insert into "
            + mlist
            + "(user_id, service_id, date,command_code,request_id, message_type,mobile_operator,mt_free,duration) values ('"
            + user_id
            + "','"
            + service_id
            + "','"
            + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date())
            + "','"
            + Service_ss_id
            + "','"
            + msgObject.getLongRequestid()
            + "','"
            + msgtype
            + "','"
            + msgObject.getMobileoperator()
            + "',"
            + mtfree
            + ","
            + lduration
            + ")";

    try {

      connection = dbpool.getConnectionGateway();

      if (DBUtil.executeSQL(connection, sqlInsert) < 0) {
        Util.logger.error(
            this.getClass().getName()
                + ""
                + ": uppdate Statement: Insert  "
                + mlist
                + " Failed:"
                + sqlInsert);
        ireturn = -1;
      }
    } catch (Exception ex) {
      Util.logger.error(this.getClass().getName() + ":Insert  " + mlist + " Failed");
      ireturn = -1;
      Util.logger.printStackTrace(ex);
    } finally {
      dbpool.cleanup(connection);
    }
    return ireturn;
  }
  public String getContent6(
      String database, String type, String subcode1, String subcode2, String newstypecode) {

    Connection connection = null;
    DBPool dbpool = new DBPool();
    String content = "content_short";
    String strResult = "";

    // Xac dinh bang nao can lay du lieu
    if ("3".equalsIgnoreCase(type)) {
      content = "content_vi";
    } else if ("2".equalsIgnoreCase(type)) {
      content = "content";
    }

    // String query =
    // "SELECT ID,CONTENT FROM icom_textbase_data WHERE upper(gameid) = '"
    // + textbaseid.toUpperCase() + "'";
    String query =
        "SELECT "
            + content
            + " FROM ( SELECT TOP 1 * FROM newsagri_news_info WHERE status=1 AND type=2 AND  upper(newstypecode) = '"
            + newstypecode.toUpperCase()
            + "'";

    // query = query + " and order =" + lastcode + " ";
    if (!"".equalsIgnoreCase(subcode1)) {
      query += " AND upper(subcode1)='" + subcode1.toUpperCase() + "'";
    }
    if (!"".equalsIgnoreCase(subcode2)) {
      query += " AND upper(subcode2)='" + subcode2.toUpperCase() + "'";
    }
    query += ")x";

    Util.logger.info("Query Content 6: " + query);

    try {
      connection = dbpool.getConnection(database);
      Vector result = DBUtil.getVectorTable(connection, query);

      Util.logger.info(this.getClass().getName() + "getContent: queryStatement:" + query);

      if (result.size() > 0) {

        // record = (String[]) result.get(0);

        Vector item = (Vector) result.elementAt(0);
        strResult = (String) item.elementAt(0);
        return strResult;
      }

      return strResult;
    } catch (Exception ex) {
      Util.logger.info(this.getClass().getName() + "getContent: Failed" + ex.getMessage());
      Util.logger.printStackTrace(ex);
      return strResult;
    } finally {
      dbpool.cleanup(connection);
    }
  }
  // Vi du truyen cuoi
  public String[] getContent0(String database, String type, String lastcode, String newstypecode) {

    String[] record = new String[2];
    record[0] = "";
    record[1] = "";

    Connection connection = null;
    DBPool dbpool = new DBPool();
    String content = "content_short";

    // Xac dinh bang nao can lay du lieu
    if ("3".equalsIgnoreCase(type)) {
      content = "content_vi";
    } else if ("2".equalsIgnoreCase(type)) {
      content = "content";
    }

    Util.logger.info("content = " + content);

    String query =
        "SELECT ID,"
            + content
            + " FROM ( SELECT TOP 1 * FROM newsagri_news_info WHERE upper(newstypecode) = '"
            + newstypecode.toUpperCase()
            + "'";

    query = query + " and id not in (" + lastcode + ") )x ";

    try {
      connection = dbpool.getConnection(database);

      Util.logger.info("SQL Query get content: " + query);
      Vector result = DBUtil.getVectorTable(connection, query);

      Util.logger.info(
          this.getClass().getName() + "getContent: queryStatement:" + result.size() + "@" + query);

      if (result.size() > 0) {

        // record = (String[]) result.get(0);

        Vector item = (Vector) result.elementAt(0);
        record[0] = (String) item.elementAt(0);
        record[1] = (String) item.elementAt(1);

        Util.logger.info(this.getClass().getName() + "getContent: record[0]:" + record[0]);
        Util.logger.info(this.getClass().getName() + "getContent: record[1]:" + record[1]);
        return record;
      }

      return record;
    } catch (Exception ex) {
      Util.logger.info(this.getClass().getName() + "getContent: Failed" + ex.getMessage());
      Util.logger.printStackTrace(ex);
      return record;
    } finally {
      dbpool.cleanup(connection);
    }
  }
  @SuppressWarnings("unchecked")
  private String getContent2(String dbcontent, String type, String infoid, String sDate) {

    Connection connection = null;
    DBPool dbpool = new DBPool();

    try {

      connection = dbpool.getConnection(dbcontent);
      String content = "content_short";
      // Xac dinh bang nao can lay du lieu
      if ("3".equalsIgnoreCase(type)) {
        content = "content_vi";
      } else if ("2".equalsIgnoreCase(type)) {
        content = "content";
      }

      String query =
          "SELECT "
              + content
              + " FROM ( SELECT TOP 1 * FROM newsagri_news_info WHERE status=1 AND type=1 AND upper(newstypecode) = '"
              + infoid.toUpperCase()
              + "' AND CONVERT(varchar(25), [isdate], 103) <='"
              + sDate
              + "' order by [isdate] desc )x";

      Util.logger.info("Query:" + query);
      Vector result = DBUtil.getVectorTable(connection, query);
      if (result.size() == 0) {
        return null;
      } else {

        for (int i = 0; i < result.size(); i++) {

          Vector item = (Vector) result.elementAt(i);
          return (String) item.elementAt(0);
        }
      }

    } catch (Exception ex) {
      Util.logger.info(this.getClass().getName() + "getInfo: Failed" + ex.getMessage());
      Util.logger.printStackTrace(ex);

    } finally {
      dbpool.cleanup(connection);
    }
    return null;
  }
  public String[] getContent3(int companyId, String poolname) {
    Connection conn = null;
    String[] strResult = new String[2];
    String sSql = "";
    PreparedStatement stmt = null;
    ResultSet rs = null;
    DBPool dbpool = new DBPool();

    try {
      conn = dbpool.getConnection(poolname);

      Util.logger.info("Conn: " + conn);
      Util.logger.info("poolname: " + poolname);
      // "Select
      // RESULT_COMPANY_ID,RESULT_TEXT,to_char(RESULT_DATE,'dd/mm/yyyy'),lotocap_text
      // from LASTEST_RESULT_FULL ORDER BY RESULT_COMPANY_ID ASC",
      sSql =
          "Select RESULT_TEXT,to_char(RESULT_DATE,'dd/mm') RESULT_DATE From LASTEST_RESULT_FULL where RESULT_COMPANY_ID = "
              + companyId;
      Util.logger.info("SQL Select: " + sSql);

      stmt =
          conn.prepareStatement(
              sSql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

      if (stmt.executeUpdate() != -1) {
        rs = stmt.getResultSet();
        if (rs.next()) {
          strResult[0] = rs.getString("RESULT_TEXT");
          strResult[1] = rs.getString("RESULT_DATE");
        }
      }

      Util.logger.info("strResult " + strResult);

      return strResult;

    } catch (SQLException ex) {
      Util.logger.error("Error: getLatestResultText " + ex.toString());
    } catch (Exception ex) {
      Util.logger.error("Error: getLatestResultText " + ex.toString());
    } finally {
      dbpool.cleanup(rs);
      dbpool.cleanup(stmt);
      dbpool.cleanup(conn);
    }
    return null;
  }
  String[] getContent0(String lastcode, String textbaseid, String poolname) {

    String[] record = new String[2];
    record[0] = "";
    record[1] = "";

    Connection connection = null;
    DBPool dbpool = new DBPool();

    String query =
        "SELECT ID,CONTENT FROM icom_textbase_data WHERE upper(gameid) = '"
            + textbaseid.toUpperCase()
            + "'";

    query = query + " and id not in (" + lastcode + ")  order by rand() limit 1";

    try {
      connection = dbpool.getConnection(poolname);
      Vector result = DBUtil.getVectorTable(connection, query);

      Util.logger.info(
          this.getClass().getName() + "getContent: queryStatement:" + result.size() + "@" + query);

      if (result.size() > 0) {

        // record = (String[]) result.get(0);

        Vector item = (Vector) result.elementAt(0);
        record[0] = (String) item.elementAt(0);
        record[1] = (String) item.elementAt(1);

        Util.logger.info(this.getClass().getName() + "getContent: record[0]:" + record[0]);
        Util.logger.info(this.getClass().getName() + "getContent: record[1]:" + record[1]);
        return record;
      }

      return record;
    } catch (Exception ex) {
      Util.logger.info(this.getClass().getName() + "getContent: Failed" + ex.getMessage());
      Util.logger.printStackTrace(ex);
      return record;
    } finally {
      dbpool.cleanup(connection);
    }
  }
  public static String getContent4(long companyid, String poolname) {
    Connection connection = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    String temp = "";
    DBPool dbpool = new DBPool();

    try {

      connection = dbpool.getConnection(poolname);

      stmt =
          connection.prepareStatement(
              "SELECT  RESULT_TEXT FROM LASTEST_CAU_FULL "
                  + "WHERE result_company_id = "
                  + companyid,
              ResultSet.TYPE_SCROLL_INSENSITIVE,
              ResultSet.CONCUR_READ_ONLY);
      if (stmt.executeUpdate() != -1) {
        rs = stmt.getResultSet();

        while (rs.next()) {

          temp = rs.getString(1);
        }
        String ret = temp;

        int i = ret.indexOf("*So ban thich");
        int j = ret.indexOf("8551###");

        ret = ret.subSequence(0, i) + ret.substring(j + 4);

        return ret;
      }

    } catch (Exception e) {
      Util.logger.error("Error: getLatestResultText " + e.toString());
    } finally {
      dbpool.cleanup(rs);
      dbpool.cleanup(stmt);
      dbpool.cleanup(connection);
    }
    return null;
  }
  @SuppressWarnings("unchecked")
  private String getContent2(String infoid) {

    Connection connection = null;
    DBPool dbpool = new DBPool();

    try {

      connection = dbpool.getConnection("gateway");

      String query =
          "select content from icom_infoservice where info_type ='"
              + infoid
              + "' and  info_date <= current_timestamp() order by info_date desc limit 1";

      Vector result = DBUtil.getVectorTable(connection, query);
      if (result.size() == 0) {
        return null;
      } else {

        for (int i = 0; i < result.size(); i++) {

          Vector item = (Vector) result.elementAt(i);
          return (String) item.elementAt(0);
        }
      }

    } catch (Exception ex) {
      Util.logger.info(this.getClass().getName() + "getInfo: Failed" + ex.getMessage());
      Util.logger.printStackTrace(ex);

    } finally {
      dbpool.cleanup(connection);
    }
    return null;
  }
  // Gui content theo cach nao
  // Neu content ngan thi phai split ra
  private static int sendMT(String type, MsgObject msgObject, String sclassname) {

    Connection connection = null;
    PreparedStatement statement = null;
    String sqlString = null;
    if ("".equalsIgnoreCase(msgObject.getUsertext().trim()) || msgObject.getUsertext() == null) {
      // Truong hop gui ban tin loi
      Util.logger.error(
          sclassname
              + "@sendMT@userid="
              + msgObject.getUserid()
              + "@serviceid="
              + msgObject.getServiceid()
              + "@requestid="
              + msgObject.getRequestid().toString()
              + "@message is null - LOST MESSAGE");
      return 1;
    }

    DBPool dbpool = new DBPool();

    Util.logger.info(
        sclassname
            + "@sendMT@userid="
            + msgObject.getUserid()
            + "@serviceid="
            + msgObject.getServiceid()
            + "@usertext="
            + msgObject.getUsertext()
            + "@messagetype="
            + msgObject.getMsgtype()
            + "@requestid="
            + msgObject.getRequestid().toString());
    try {
      connection = dbpool.getConnectionGateway();
      if (connection == null) {
        Util.logger.crisis(
            sclassname
                + "@sendMT: Error connection == null"
                + msgObject.getUserid()
                + "@TO"
                + msgObject.getServiceid()
                + "@"
                + msgObject.getUsertext()
                + "@requestid="
                + msgObject.getRequestid().toString());
        return -1;
      }

      // USER_ID, SERVICE_ID, MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE,
      // INFO, MESSAGE_TYPE, REQUEST_ID
      sqlString =
          "INSERT INTO "
              + Constants.tblMTQueue
              + "( USER_ID, SERVICE_ID, MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE, INFO,  MESSAGE_TYPE, REQUEST_ID) VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

      // USER_ID, SERVICE_ID, MOBILE_OPERATOR, COMMAND_CODE,
      // CONTENT_TYPE, INFO, MESSAGE_TYPE, REQUEST_ID

      if ("1".equalsIgnoreCase(type)) {

        // Truoc khi gui phai split
        String[] content = msgObject.getUsertext().split("###");

        for (int i = 0; i < content.length; i++) {
          if (!"".equalsIgnoreCase(content[i])) {
            statement = connection.prepareStatement(sqlString);
            statement.setString(1, msgObject.getUserid());
            statement.setString(2, msgObject.getServiceid());
            statement.setString(3, msgObject.getMobileoperator());
            statement.setString(4, msgObject.getKeyword());
            statement.setInt(5, msgObject.getContenttype());
            statement.setString(6, content[i]);
            if (i == 0) {
              statement.setInt(7, 1);
            } else {
              statement.setInt(7, 0);
            }
            statement.setBigDecimal(8, msgObject.getRequestid());

            if (statement.executeUpdate() != 1) {
              Util.logger.crisis(
                  sclassname
                      + "@sendMT: Error@userid="
                      + msgObject.getUserid()
                      + "@serviceid="
                      + msgObject.getServiceid()
                      + "@usertext="
                      + msgObject.getUsertext()
                      + "@messagetype="
                      + msgObject.getMsgtype()
                      + "@requestid="
                      + msgObject.getRequestid().toString());

              return -1;
            }
          }
        }
        return 1;
      } else {
        statement = connection.prepareStatement(sqlString);
        statement.setString(1, msgObject.getUserid());
        statement.setString(2, msgObject.getServiceid());
        statement.setString(3, msgObject.getMobileoperator());
        statement.setString(4, msgObject.getKeyword());
        statement.setInt(5, msgObject.getContenttype());
        statement.setString(6, msgObject.getUsertext());
        statement.setInt(7, msgObject.getMsgtype());
        statement.setBigDecimal(8, msgObject.getRequestid());

        if (statement.executeUpdate() != 1) {
          Util.logger.crisis(
              sclassname
                  + "@sendMT: Error@userid="
                  + msgObject.getUserid()
                  + "@serviceid="
                  + msgObject.getServiceid()
                  + "@usertext="
                  + msgObject.getUsertext()
                  + "@messagetype="
                  + msgObject.getMsgtype()
                  + "@requestid="
                  + msgObject.getRequestid().toString());

          return -1;
        }
        return 1;
      }
    } catch (SQLException e) {
      Util.logger.crisis(
          sclassname
              + "@sendMT: Error:@userid="
              + msgObject.getUserid()
              + "@serviceid="
              + msgObject.getServiceid()
              + "@usertext="
              + msgObject.getUsertext()
              + "@messagetype="
              + msgObject.getMsgtype()
              + "@requestid="
              + msgObject.getRequestid().toString()
              + "@"
              + e.toString());

      Util.logger.printStackTrace(e);

      return -1;
    } catch (Exception e) {
      Util.logger.crisis(
          sclassname
              + "@sendMT: Error:@userid="
              + msgObject.getUserid()
              + "@serviceid="
              + msgObject.getServiceid()
              + "@usertext="
              + msgObject.getUsertext()
              + "@messagetype="
              + msgObject.getMsgtype()
              + "@requestid="
              + msgObject.getRequestid().toString()
              + "@"
              + e.toString());
      Util.logger.printStackTrace(e);
      return -1;
    } finally {
      dbpool.cleanup(statement);
      dbpool.cleanup(connection);
    }
  }
Exemple #10
0
  public static String getContent5(MsgObject msgObject) {
    Connection connection = null;
    PreparedStatement statement = null;
    String sqlString = null;
    if ("".equalsIgnoreCase(msgObject.getUsertext().trim()) || msgObject.getUsertext() == null) {
      // Truong hop gui ban tin loi
      Util.logger.error(
          "getContent5@userid="
              + msgObject.getUserid()
              + "@serviceid="
              + msgObject.getServiceid()
              + "@requestid="
              + msgObject.getRequestid().toString()
              + "@message is null - LOST MESSAGE");
      return null;
    }

    DBPool dbpool = new DBPool();

    Util.logger.info(
        "getContent5@userid="
            + msgObject.getUserid()
            + "@serviceid="
            + msgObject.getServiceid()
            + "@usertext="
            + msgObject.getUsertext()
            + "@messagetype="
            + msgObject.getMsgtype()
            + "@requestid="
            + msgObject.getRequestid().toString());
    try {
      connection = dbpool.getConnection("smscnt");
      if (connection == null) {
        Util.logger.crisis(
            "getContent5: Error connection == null"
                + msgObject.getUserid()
                + "@TO"
                + msgObject.getServiceid()
                + "@"
                + msgObject.getUsertext()
                + "@requestid="
                + msgObject.getRequestid().toString());
        return null;
      }

      // USER_ID, SERVICE_ID, MOBILE_OPERATOR, COMMAND_CODE, CONTENT_TYPE,
      // INFO, MESSAGE_TYPE, REQUEST_ID
      sqlString =
          "INSERT INTO mo_queue( USER_ID, SERVICE_ID, MOBILE_OPERATOR, COMMAND_CODE, INFO,   REQUEST_ID) VALUES (?, ?, ?, ?, ?, ?)";

      // USER_ID, SERVICE_ID, MOBILE_OPERATOR, COMMAND_CODE,
      // CONTENT_TYPE, INFO, MESSAGE_TYPE, REQUEST_ID
      statement = connection.prepareStatement(sqlString);
      statement.setString(1, msgObject.getUserid());
      statement.setString(2, msgObject.getServiceid());
      statement.setString(3, msgObject.getMobileoperator());
      statement.setString(4, msgObject.getKeyword());
      statement.setString(5, msgObject.getUsertext());
      statement.setBigDecimal(6, msgObject.getRequestid());

      if (statement.executeUpdate() != 1) {
        Util.logger.crisis(
            "getContent5: Error@userid="
                + msgObject.getUserid()
                + "@serviceid="
                + msgObject.getServiceid()
                + "@usertext="
                + msgObject.getUsertext()
                + "@messagetype="
                + msgObject.getMsgtype()
                + "@requestid="
                + msgObject.getRequestid().toString());
        return null;
      }
      return null;
    } catch (SQLException e) {
      Util.logger.crisis(
          "getContent5: Error:@userid="
              + msgObject.getUserid()
              + "@serviceid="
              + msgObject.getServiceid()
              + "@usertext="
              + msgObject.getUsertext()
              + "@messagetype="
              + msgObject.getMsgtype()
              + "@requestid="
              + msgObject.getRequestid().toString()
              + "@"
              + e.toString());
      return null;
    } catch (Exception e) {
      Util.logger.crisis(
          "getContent5: Error:@userid="
              + msgObject.getUserid()
              + "@serviceid="
              + msgObject.getServiceid()
              + "@usertext="
              + msgObject.getUsertext()
              + "@messagetype="
              + msgObject.getMsgtype()
              + "@requestid="
              + msgObject.getRequestid().toString()
              + "@"
              + e.toString());
      return null;
    } finally {
      dbpool.cleanup(statement);
      dbpool.cleanup(connection);
    }
  }