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;
}
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);
}
}
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 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;
}
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);
}
}
@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);
}
}
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);
}
}