ca1

package jdbc;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Writer;
import java.sql.CallableStatement;
import java.sql.Clob;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Vector;

import oracle.jdbc.pool.OracleDataSource;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;

import uti.Func;
import uti.Para;
import uti.SIBS;
import uti.SendEmail;

public class JDBCPool {
	private static OracleDataSource ods = null;
	private static Driver driver = null;
	public static JdbcTemplate jdbcTemplate = null;

	public static void init() {
		if (jdbcTemplate == null) {
			// Func.log(Para.fullPathLog + ".jdbcpool",
			// "create connection to Oracle...");

			if (ods == null) {
				try {
					ods = new OracleDataSource();
					// ods.setURL("jdbc:oracle:thin:@" + Para.DB_SERVER_IP + ":"
					// + Para.DB_SEVER_PORT + ":" + Para.DB_SID_NAME);
					ods.setURL(Para.DB_ORACLEURL);
					ods.setUser(Para.DB_USER_NAME);
					ods.setPassword(Para.DB_PASSWORD);
					ods.setConnectionCachingEnabled(true);

					// ods.setConnectionCacheName("CACHE"+d);
					Properties cacheProps = new Properties();
					cacheProps.setProperty("MinLimit", "1");
//					cacheProps.setProperty("MaxLimit", "10");
					cacheProps.setProperty("MaxLimit", "30");
					cacheProps.setProperty("InitialLimit", "1");
					cacheProps.setProperty("ConnectionWaitTimeout", "5");
					cacheProps.setProperty("ValidateConnection", "true");

					ods.setConnectionCacheProperties(cacheProps);
				} catch (SQLException e) {
					Func.log(Para.fullPathLog + ".jdbcpool", "Loi: " + e);
					// SendEmail
					// .email("Khong tao duoc ket noi toi database oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong tao duoc ket noi toi database oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
				}
			}

			jdbcTemplate = new JdbcTemplate(ods);
		}
	}

	public static JdbcTemplate getJdbcTemplate() {
		if (jdbcTemplate == null) {
			synchronized (JDBCPool.class) {
				if (jdbcTemplate == null) {
					init();
				}
			}
		}
		return jdbcTemplate;
	}

	public static void destroy() {
		try {
			if (ods != null)
				ods.close();
			jdbcTemplate = null;
			// Func.log(Para.fullPathLog + ".jdbcpool",
			// "OracleDataSource is closed!");
		} catch (Exception e) {
		}
	}

	/*
	 * getConnection() for JDBC
	 */
	@SuppressWarnings("unchecked")
	public static Connection getConnection() throws SQLException {
		if (driver == null) {
			try {
				Class jdbcDriverClass = Class
						.forName("oracle.jdbc.driver.OracleDriver");
				driver = (Driver) jdbcDriverClass.newInstance();
				DriverManager.registerDriver(driver);
			} catch (Exception e) {
				Func.log(Para.fullPathLog + ".jdbcpool", "Loi: " + e);
			}
		}

		// Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@"
		// + Para.DB_SERVER_IP + ":" + Para.DB_SEVER_PORT + ":"
		// + Para.DB_SID_NAME, Para.DB_USER_NAME, Para.DB_PASSWORD);
		Connection conn = DriverManager.getConnection(Para.DB_ORACLEURL,
				Para.DB_USER_NAME, Para.DB_PASSWORD);
		conn.setAutoCommit(false);
		return conn;
	}

	public static void close(Connection conn) {
		try {
			if (conn != null) {
				// Func.log(Para.fullPathLog + ".jdbcpool",
				// "QLHD: Closed connection");
				conn.close();
			}
		} catch (SQLException sqle) {
			Func.log(Para.fullPathLog + ".jdbcpool", sqle.toString());
		}
	}

	public static void close(PreparedStatement stmt) {
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException sqle) {
		}
	}

	public static void close(ResultSet rs) {
		try {
			if (rs != null)
				rs.close();
		} catch (SQLException sqle) {
		}
	}

	/*
	 * call query, procedure, function use JDBCPool
	 */
	public static void callQuery() {
		System.out.println(jdbcTemplate.queryForInt("select 1 from dual"));
	}

	/*
	 * FUNCTION customers_pkg.isexists (p_cif IN number, p_accID in varchar2,
	 * p_ServiceID in number) -- Package Customers_Pkg p_cif: so cif p_accID: so
	 * tai khoan p_ServiceID: ID cua dich vu
	 */
	@SuppressWarnings("unchecked")
	public static void logging(final String p_jobname, final String p_status,
			final String p_note) {
		init();

		jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) {
				CallableStatement cs = null;
				try {
					cs = con.prepareCall("{call commonfunctions_pkg.logging(?,?,?)}");
					cs.setString(1, p_jobname);
					cs.setString(2, p_status);
					cs.setString(3, p_note);
				} catch (SQLException e) {
					Func.log(Para.fullPathLog + ".jdbcpool",
							"logging=" + e.toString());
				}
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					return null;
				} catch (SQLException e) {
					Func.log(Para.fullPathLog + ".jdbcpool",
							"logging=" + e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/*
	 * FUNCTION customers_pkg.isexists (p_cif IN number, p_accID in varchar2,
	 * p_ServiceID in number) -- Package Customers_Pkg p_cif: so cif p_accID: so
	 * tai khoan p_ServiceID: ID cua dich vu
	 */
	@SuppressWarnings("unchecked")
	public static boolean checkRegisterExits(final String p_cif,
			final String p_accID, final int p_ServiceID) {
		init();

		Integer s = (Integer) jdbcTemplate.execute(
				new CallableStatementCreator() {
					public CallableStatement createCallableStatement(
							Connection con) {
						CallableStatement cs = null;
						try {
							cs = con.prepareCall("{? = call messages_pkg.isexists(?,?,?)}");
							cs.registerOutParameter(1, java.sql.Types.INTEGER);
							cs.setString(2, p_cif);
							cs.setString(3, p_accID);
							cs.setInt(4, p_ServiceID);
						} catch (SQLException e) {
							Func.log(Para.fullPathLog + ".jdbcpool",
									"checkRegisterExits=" + e.toString());
						}
						return cs;
					}
				}, new CallableStatementCallback() {
					public Object doInCallableStatement(CallableStatement cs) {
						try {
							cs.execute();
							int result = cs.getInt(1);
							return result; // Whatever is returned here is
											// returned from the
											// jdbcTemplate.execute method
						} catch (SQLException e) {
							Func.log(Para.fullPathLog + ".jdbcpool",
									"checkRegisterExits=" + e.toString());
							// SendEmail
							// .email("Khong chay duoc store messages_pkg.isexists tren oracle! ("
							// + Para.DB_SERVER_IP
							// + ":"
							// + Para.DB_SEVER_PORT
							// + ":"
							// + Para.DB_SID_NAME
							// + ") Loi:"
							// + e.toString());
							SendEmail
									.email("Khong chay duoc store messages_pkg.isexists tren oracle! ("
											+ Para.DB_ORACLEURL
											+ ") Loi:"
											+ e.toString());
							return null;
						} finally {
							try {
								cs.close();
							} catch (Exception e2) {
							}
						}
					}
				});

		return (s != null && s.equals(1) ? true : false);
	}

	/*
	 * PROCEDURE messages_pkg.sendsms_queue(Msg_Body in varchar2, p_ServiceID in
	 * number, p_CIF in number, p_accID in varchar2, p_smsinID in varchar2,
	 * Msg_output out varchar2) Msg_Body: noi dung message p_ServiceID: ID cua
	 * dich vu p_Cif: so cif p_accID: so tai khoan p_smsinID: id cua tin nhan
	 * van tin den Msg_output: dinh dang xml tra ra queue, gui toi vnpay
	 */
	@SuppressWarnings("unchecked")
	public static String createMessage(final String p_Msg_Body,
			final Integer p_ServiceID, final String p_CIF,
			final String p_accID, final String p_smsinID) {
		init();
		String ret = (String) jdbcTemplate.execute(
				new CallableStatementCreator() {
					public CallableStatement createCallableStatement(
							Connection con) throws SQLException {
						CallableStatement cs = con
								.prepareCall("{call messages_pkg.sendsms_queue(?, ?, ?, ?, ?, ?)}");
						cs.setString(1, p_Msg_Body);
						cs.setInt(2, p_ServiceID);
						cs.setString(3, p_CIF);
						cs.setString(4, p_accID);
						cs.setString(5, p_smsinID);
						cs.registerOutParameter(6, java.sql.Types.VARCHAR);
						return cs;
					}
				}, new CallableStatementCallback() {
					public Object doInCallableStatement(CallableStatement cs)
							throws SQLException {
						try {
							cs.execute();
							return cs.getString(6);
						} catch (Exception e) {
							Func.log(Para.fullPathLog + ".jdbcpool",
									"createMessage=" + e.toString());
							// SendEmail
							// .email("Khong chay duoc store messages_pkg.sendsms_queue tren oracle! ("
							// + Para.DB_SERVER_IP
							// + ":"
							// + Para.DB_SEVER_PORT
							// + ":"
							// + Para.DB_SID_NAME
							// + ") Loi:"
							// + e.toString());
							SendEmail
									.email("Khong chay duoc store messages_pkg.sendsms_queue tren oracle! ("
											+ Para.DB_ORACLEURL
											+ ") Loi:"
											+ e.toString());
							return null;
						} finally {
							try {
								cs.close();
							} catch (Exception e2) {
							}
						}
					}
				});

		return ret;
	}

	/*
	 * createMsgCA_TMTran
	 */
	@SuppressWarnings("unchecked")
	public static Vector<String> createMsgCA_TMTran(final String sAct,
			final String sStat, final String sAccNo, final String sAmt,
			final String sCurr, final String sDorc, final String sDate,
			final String sTime, final String sSeq, final String sTellID,
			final String sBrn, final String sTmsseq, final String sEFTH,
			final String sTmapptype, final String sTMTLMNE,
			final String sTMHOSTTXCD, final String sTMHAFFT) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg.createMsgCA_TMTran(?, ?, ?, ?, ?,  ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?,?,?, ?)}");
				cs.setString(1, sAct);
				cs.setString(2, sStat);
				cs.setString(3, sAccNo);
				cs.setString(4, sAmt);
				cs.setString(5, sCurr);

				cs.setString(6, sDorc);
				cs.setString(7, sDate);
				cs.setString(8, sTime);
				cs.setString(9, sSeq);

				cs.setString(10, sTellID);
				cs.setString(11, sBrn);
				cs.setString(12, sTmsseq);
				cs.setString(13, sEFTH);
				cs.setString(14, sTmapptype);

				cs.setString(15, sTMTLMNE);
				cs.setString(16, sTMHOSTTXCD);
				cs.setString(17, sTMHAFFT);

				cs.registerOutParameter(18, java.sql.Types.VARCHAR);
				cs.registerOutParameter(19, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(18));
					v.add(cs.getString(19));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".ca",
							"createMsgCA_TMTran=" + e.toString());
					// SendEmail
					// .email("Khong chay duoc store messages_pkg.createMsgCA_TMTran tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store messages_pkg.createMsgCA_TMTran tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/*
	 * createMsgCA_DDTrn2
	 */
	@SuppressWarnings("unchecked")
	public static List<String> createMsgCA_DDTrn2(final String sAct,
			final String strstat, final String sbatch, final String sseq,
			final String stracct, final String stratyp, final String strbr,
			final String sdorc, final String strancd, final String samt,
			final String strctyp, final String strdate, final String strtime,
			final String struser, final String seftacc, final String strefth,
			final String strjobn) {
		init();

		try {
			return (Vector) jdbcTemplate.execute(
					new CallableStatementCreator() {
						public CallableStatement createCallableStatement(
								Connection con) throws SQLException {
							CallableStatement cs = con
									.prepareCall("{call messages_pkg.createMsgCA_DDTrn2(?,?,?,?,?, ?,?,?,?,?, ?,?,?,?,?, ?,?,?,?)}");
							cs.setString(1, sAct);
							cs.setString(2, strstat);
							cs.setString(3, sbatch);
							cs.setString(4, sseq);
							cs.setString(5, stracct);

							cs.setString(6, stratyp);
							cs.setString(7, strbr);
							cs.setString(8, sdorc);
							cs.setString(9, strancd);

							cs.setString(10, samt);
							cs.setString(11, strctyp);
							cs.setString(12, strdate);
							cs.setString(13, strtime);

							cs.setString(14, struser);
							cs.setString(15, seftacc);

							cs.setString(16, strefth);
							cs.setString(17, strjobn);

							cs.registerOutParameter(18, java.sql.Types.VARCHAR);
							cs.registerOutParameter(19, java.sql.Types.VARCHAR);
							return cs;
						}
					}, new CallableStatementCallback() {
						public Object doInCallableStatement(CallableStatement cs) {
							try {
								cs.execute();
								Vector v = new Vector<String>();
								v.add(cs.getString(18));
								v.add(cs.getString(19));
								return v;
							} catch (Exception e) {
								uti.Func.log(Para.fullPathLog + ".ca",
										"createMsgCA_DDTrn2=" + e.toString());
								// SendEmail
								// .email("Khong chay duoc store messages_pkg.createMsgCA_DDTrn2 tren oracle! ("
								// + Para.DB_SERVER_IP
								// + ":"
								// + Para.DB_SEVER_PORT
								// + ":"
								// + Para.DB_SID_NAME
								// + ") Loi:"
								// + e.toString());
								SendEmail
										.email("Khong chay duoc store messages_pkg.createMsgCA_DDTrn2 tren oracle! ("
												+ Para.DB_ORACLEURL
												+ ") Loi:"
												+ e.toString());
								return null;
							} finally {
								try {
									cs.close();
								} catch (Exception e2) {
								}
							}
						}
					});
		} catch (Exception e) {
			Func.log(Para.fullPathLog + ".ca",
					"createMsgCA_DDTrn2=" + e.toString());
			return null;
		}
	}

	/*
	 * createMsgCA_TMTran
	 */
	@SuppressWarnings("unchecked")
	public static Vector<String> createResponse_Msg(final String p_Mobile,
			final String p_Request, final String p_SmsinID) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call Messages_Pkg.sms_Inquery_fromMq(?, ?, ?, ?)}");
				cs.setString(1, p_Mobile);
				cs.setString(2, p_Request);
				cs.setString(3, p_SmsinID);
				cs.registerOutParameter(4, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(4));
					return v;
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".jdbcpool",
							"createResponse_Msg=" + e.toString());
					// SendEmail
					// .email("Khong chay duoc store Messages_Pkg.sms_Inquery_fromMq tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store Messages_Pkg.sms_Inquery_fromMq tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/* end */
	@SuppressWarnings("unchecked")
	public static Vector<String> createAutoOra_Msg(final String QueueName) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call QueueProcess_Pkg.DeQueue_NoWait(?,?)}");
				cs.setString(1, QueueName);
				cs.registerOutParameter(2, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(2));
					return v;
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".oramsg", "createAutoOra_Msg="
							+ e.toString());
					// SendEmail
					// .email("Khong chay duoc store QueueProcess_Pkg.DeQueue_NoWait tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store QueueProcess_Pkg.DeQueue_NoWait tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	@SuppressWarnings("unchecked")
	public static Vector<String> createMsgLNRate(final String sAct,
			final String sAccNo, final String sLNNewRate,
			final String sLNOldRate, final String sEffDate,
			final String sLastUpdTime, final String sLastUpdDate,
			final String sTellID) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con) throws SQLException {
				CallableStatement cs = con.prepareCall("{call automsg_pkg.createMsgLNRate(?,?,?,?,?,?,?,?,?,?)}");
				cs.setString(1, sAct);
				cs.setString(2, sAccNo);
				cs.setString(3, sLNNewRate);
				cs.setString(4, sLNOldRate);
				cs.setString(5, sEffDate);
				cs.setString(6, sLastUpdTime);
				cs.setString(7, sLastUpdDate);
				cs.setString(8, sTellID);

				cs.registerOutParameter(9, java.sql.Types.VARCHAR);
				cs.registerOutParameter(10, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(9));
					v.add(cs.getString(10));
					return v;
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".lnrate", "createMsgLNRate="
							+ e.toString());
					// SendEmail
					// .email("Khong chay duoc store automsg_pkg.createMsgLNRate tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store automsg_pkg.createMsgLNRate tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}
	
	@SuppressWarnings("unchecked")
    public static Vector<String> createMsgLNRate_PRF(final String sAct,
            final String sAccNo, final String sLNNewRate,
            final String sLNOldRate, final String sEffDate,
            final String sLastUpdTime, final String sLastUpdDate,
            final String sTellID, final String cifno,final String cbal,final String curtype) {
        init();

        return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
            public CallableStatement createCallableStatement(Connection con) throws SQLException {
                CallableStatement cs = con.prepareCall("{call AUTOMSG_PKG.createMsgLNRate_PRF(?,?,?,?,?,?,?,?,?,?,?,?,?)}");
                cs.setString(1, sAct);
                cs.setString(2, sAccNo);
                cs.setString(3, sLNNewRate);
                cs.setString(4, sLNOldRate);
                cs.setString(5, sEffDate);
                cs.setString(6, sLastUpdTime);
                cs.setString(7, sLastUpdDate);
                cs.setString(8, sTellID);
                cs.setString(9, cifno);
                cs.setString(10, cbal);
                cs.setString(11, curtype);

                cs.registerOutParameter(12, java.sql.Types.VARCHAR);
                cs.registerOutParameter(13, java.sql.Types.VARCHAR);
                return cs;
            }
        }, new CallableStatementCallback() {
            public Object doInCallableStatement(CallableStatement cs) throws SQLException {
                try {
                    cs.execute();
                    Vector v = new Vector<String>();
                    v.add(cs.getString(12));
                    v.add(cs.getString(13));
                    return v;
                } catch (Exception e) {
                    Func.log(Para.fullPathLog + ".lnrate", "createMsgLNRate=" + e.toString());
                    SendEmail.email("Khong chay duoc store AUTOMSG_PKG.createMsgLNRate_PRF tren oracle! ("
                                    + Para.DB_ORACLEURL
                                    + ") Loi:"
                                    + e.toString());
                    return null;
                } finally {
                    try {
                        cs.close();
                    } catch (Exception e2) {
                    }
                }
            }
        });
    }

	/* end */
	@SuppressWarnings("unchecked")
	public static void tiepvkTest(final String p_desc) {
		init();
		jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg_tiepvk.tiepvkTest(?)}");
				cs.setString(1, p_desc);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".jdbcpool",
							"tiepvkTest=" + e.toString());
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
				return null;
			}
		});
	}

	@SuppressWarnings("unchecked")
	public static Vector<String> createMsgLCNEW(final String sAct,
			final String sTFRef, final String sTFType, final String sCIF,
			final String sLCNo, final String sEnterDate7,
			final String sReceiptDate7, final String sCurrency,
			final String sAmount, final String SLCType) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call automsg_pkg.createMsgLCNew(?,?,?,?,?, ?,?,?,?,?,?,?)}");
				cs.setString(1, sAct);
				cs.setString(2, sTFRef);
				cs.setString(3, sTFType);
				cs.setString(4, sCIF);
				cs.setString(5, sLCNo);
				cs.setString(6, sEnterDate7);
				cs.setString(7, sReceiptDate7);
				cs.setString(8, sCurrency);
				cs.setString(9, sAmount);
				cs.setString(10, SLCType);

				cs.registerOutParameter(11, java.sql.Types.VARCHAR);
				cs.registerOutParameter(12, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(11));
					v.add(cs.getString(12));
					return v;
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".lcnew",
							"createMsgLCNew=" + e.toString());
					// SendEmail
					// .email("Khong chay duoc store automsg_pkg.createMsgLCNew tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store automsg_pkg.createMsgLCNew tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	@SuppressWarnings("unchecked")
	public static Vector<String> deGfgQueue(final String ProcedureName,
			final String oracleQueueName) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con.prepareCall("{call " + ProcedureName
						+ "(?,?)}");
				cs.setString(1, oracleQueueName);
				cs.registerOutParameter(2, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(2));
					return v;
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".adverst",
							"deGfgQueue=" + e.toString());
					// SendEmail.email("Khong chay duoc store " + ProcedureName
					// + " tren oracle! (" + Para.DB_SERVER_IP + ":"
					// + Para.DB_SEVER_PORT + ":" + Para.DB_SID_NAME
					// + ") Loi:" + e.toString());
					SendEmail.email("Khong chay duoc store " + ProcedureName
							+ " tren oracle! (" + Para.DB_ORACLEURL + ") Loi:"
							+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	@SuppressWarnings("unchecked")
	public static Vector<String> emlDeGfgQueue(final String ProcedureName,
			final String oracleQueueName) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con.prepareCall("{call " + ProcedureName
						+ "(?,?)}");
				cs.setString(1, oracleQueueName);
				cs.registerOutParameter(2, java.sql.Types.CLOB);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					Clob templateClob = cs.getClob(2);
					BufferedReader contentReader = new BufferedReader(
							templateClob.getCharacterStream());
					StringBuilder contentBuilder = new StringBuilder();
					char[] buf = new char[1204];
					int length = -1;
					while ((length = contentReader.read(buf)) != -1) {
						contentBuilder.append(buf, 0, length);
					}
					v.add(contentBuilder.toString());
					return v;
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".adverst", "emlDeGfgQueue="
							+ e.toString());
					// SendEmail.email("Khong chay duoc store " + ProcedureName
					// + " tren oracle! (" + Para.DB_SERVER_IP + ":"
					// + Para.DB_SEVER_PORT + ":" + Para.DB_SID_NAME
					// + ") Loi:" + e.toString());
					SendEmail.email("Khong chay duoc store " + ProcedureName
							+ " tren oracle! (" + Para.DB_ORACLEURL + ") Loi:"
							+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}
	
	@SuppressWarnings("unchecked")
	public static Vector<String> emlDeQueueArray(final String ProcedureName,
			final String oracleQueueName) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement proc = con.prepareCall("{call " + ProcedureName
						+ "(?,?,?)}");
				proc.setString(1, oracleQueueName);
				proc.registerOutParameter(2, java.sql.Types.ARRAY,"EML_MSG_ARRAY");
				proc.registerOutParameter(3, java.sql.Types.INTEGER);
				proc.setQueryTimeout(30);
				return proc;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					Clob templateClob = cs.getClob(2);
					BufferedReader contentReader = new BufferedReader(
							templateClob.getCharacterStream());
					StringBuilder contentBuilder = new StringBuilder();
					char[] buf = new char[1204];
					int length = -1;
					while ((length = contentReader.read(buf)) != -1) {
						contentBuilder.append(buf, 0, length);
					}
					v.add(contentBuilder.toString());
					return v;
				} catch (Exception e) {
					Func.log(Para.fullPathLog + ".adverst", "emlDeGfgQueue="
							+ e.toString());
					SendEmail.email("Khong chay duoc store " + ProcedureName
							+ " tren oracle! (" + Para.DB_ORACLEURL + ") Loi:"
							+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	@SuppressWarnings("unchecked")
	public static Vector<String> callStoreProcedure(final String ProcedureName) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con.prepareCall("{call " + ProcedureName
						+ "(?)}");
				cs.registerOutParameter(1, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(1));
					return v;
				} catch (Exception e) {
					Func.log(
							Para.fullPathLog + ".jdbcpool",
							"callStoreProcedure " + ProcedureName + "="
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	@SuppressWarnings("unchecked")
	/*
	 * isNighmode co gia tri: 0-khong phai nighmode, 1-nightmode tu bang 24h
	 */
	public static List<String> VerifyMsgCA_TMTran(final String sAct,
			final String sStat, final String sAccNo, final String sAmt,
			final String sCurr, final String sDorc, final String sDate,
			final String sTime, final String sSeq, final String sTellID,
			final String sBrn, final String sTmsseq, final String sEFTH,
			final String sTmapptype, final String sTMTLMNE,
			final String sTMHOSTTXCD, final String sTMHAFFT,
			final String sTMTXCD, final String isNighmode) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg.VerifyMsgCA_TMTran_V2(?, ?, ?, ?, ?,  ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?,?,?,?,?, ?)}");
				cs.setString(1, sAct);
				cs.setString(2, sStat);
				cs.setString(3, sAccNo);
				cs.setString(4, sAmt);
				cs.setString(5, sCurr);

				cs.setString(6, sDorc);
				cs.setString(7, sDate);
				cs.setString(8, sTime);
				cs.setString(9, sSeq);

				cs.setString(10, sTellID);
				cs.setString(11, sBrn);
				cs.setString(12, sTmsseq);
				cs.setString(13, sEFTH);
				cs.setString(14, sTmapptype);

				cs.setString(15, sTMTLMNE);
				cs.setString(16, sTMHOSTTXCD);
				cs.setString(17, sTMHAFFT);

				cs.setString(18, sTMTXCD);
				cs.setString(19, isNighmode);

				cs.registerOutParameter(20, java.sql.Types.VARCHAR);
				cs.registerOutParameter(21, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(20));
					v.add(cs.getString(21));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".ca",
							"VerifyMsgCA_TMTran=" + e.toString());
					// SendEmail
					// .email("Khong chay duoc store messages_pkg.VerifyMsgCA_TMTran_V2 tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store messages_pkg.VerifyMsgCA_TMTran_V2 tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/*
	 * isNighmode co gia tri: 0-khong phai nighmode, 1-nightmode tu bang 24h
	 */
	@SuppressWarnings("unchecked")
	public static List<String> VerifyMsg_ALL_TMTran(final String sAct,
			final String sStat, final String sAccNo, final String sAmt,
			final String sCurr, final String sDorc, final String sDate,
			final String sTime, final String sSeq, final String sTellID,
			final String sBrn, final String sTmsseq, final String sEFTH,
			final String sTmapptype, final String sTMTLMNE,
			final String sTMHOSTTXCD, final String sTMHAFFT,
			final String sTMTXCD, final String isNighmode) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg.VerifyMsg_All_TMTran(?, ?, ?, ?, ?,  ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?,?,?,?,?, ?)}");
				cs.setString(1, sAct);
				cs.setString(2, sStat);
				cs.setString(3, sAccNo);
				cs.setString(4, sAmt);
				cs.setString(5, sCurr);

				cs.setString(6, sDorc);
				cs.setString(7, sDate);
				cs.setString(8, sTime);
				cs.setString(9, sSeq);

				cs.setString(10, sTellID);
				cs.setString(11, sBrn);
				cs.setString(12, sTmsseq);
				cs.setString(13, sEFTH);
				cs.setString(14, sTmapptype);

				cs.setString(15, sTMTLMNE);
				cs.setString(16, sTMHOSTTXCD);
				cs.setString(17, sTMHAFFT);

				cs.setString(18, sTMTXCD);
				cs.setString(19, isNighmode);

				cs.registerOutParameter(20, java.sql.Types.VARCHAR);
				cs.registerOutParameter(21, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(20));
					v.add(cs.getString(21));
					return v;
				} catch (Exception e) {
					uti.Func.logx(Para.fullPathLog + ".tietkiem",
							"VerifyMsgCA_TMTran=" + e.toString());
					// SendEmail
					// .email("Khong chay duoc store messages_pkg.VerifyMsg_All_TMTran tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store messages_pkg.VerifyMsg_All_TMTran tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	// ban cu
	/*
	 * public static List<String> VerifyMsgCA_TMTran_old(final String sAct,
	 * final String sStat, final String sAccNo, final String sAmt, final String
	 * sCurr, final String sDorc, final String sDate, final String sTime, final
	 * String sSeq, final String sTellID, final String sBrn, final String
	 * sTmsseq, final String sEFTH, final String sTmapptype,final String
	 * sTMTLMNE,final String sTMHOSTTXCD,final String sTMHAFFT, final String
	 * sTMTXCD, final String isNighmode){ init();
	 * 
	 * return (Vector)jdbcTemplate.execute( new CallableStatementCreator() {
	 * public CallableStatement createCallableStatement(Connection con) throws
	 * SQLException{ CallableStatement cs = con.prepareCall(
	 * "{call messages_pkg.VerifyMsgCA_TMTran_V2(?, ?, ?,  ?, ?, ?, ?, ?,  ?, ?, ?, ?, ?, ?,?,?,?,?, ?)}"
	 * ); cs.setString(1, sAct); cs.setString(2, sStat); cs.setString(3,
	 * sAccNo); cs.setString(4, sAmt); cs.setString(5, sCurr);
	 * 
	 * cs.setString(6, sDorc); cs.setString(7, sDate); cs.setString(8, sTime);
	 * cs.setString(9, sSeq);
	 * 
	 * cs.setString(10, sTellID); cs.setString(11, sBrn); cs.setString(12,
	 * sTmsseq); cs.setString(13, sEFTH); cs.setString(14, sTmapptype);
	 * 
	 * cs.setString(15, sTMTLMNE); cs.setString(16, sTMHOSTTXCD);
	 * cs.setString(17, sTMHAFFT);
	 * 
	 * cs.registerOutParameter(18, java.sql.Types.VARCHAR);
	 * cs.registerOutParameter(19, java.sql.Types.VARCHAR); return cs; } }, new
	 * CallableStatementCallback() { public Object
	 * doInCallableStatement(CallableStatement cs){ try { cs.execute(); Vector v
	 * = new Vector<String>(); v.add(cs.getString(18)); v.add(cs.getString(19));
	 * return v; } catch (Exception e) { uti.Func.log(Para.fullPathLog + ".ca",
	 * "VerifyMsgCA_TMTran=" + e.toString()); return null; } finally { try {
	 * cs.close(); } catch (Exception e2) {} } } } ); }
	 */

	// khong xu ly trong phien ban han muc chung
	@SuppressWarnings("unchecked")
	public static List<String> createMsgCA_TMTran_New(final String sAccNo,
			final String sAmt, final String sBody, final String sTmapptype,
			final String sDorc, final int iServiceID) {
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg.createMsgCA_TMTran_New(?,?,?,  ?,?,?, ?,?)}");
				cs.setString(1, sAccNo);
				cs.setString(2, sAmt);
				cs.setString(3, sBody);
				cs.setString(4, sTmapptype);
				cs.setInt(5, iServiceID);
				cs.setString(6, sDorc);

				cs.registerOutParameter(7, java.sql.Types.VARCHAR);
				cs.registerOutParameter(8, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(7));
					v.add(cs.getString(8));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".ca",
							"createMsgCA_TMTran_New=" + e.toString());
					// SendEmail
					// .email("Khong chay duoc store messages_pkg.createMsgCA_TMTran_New tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store messages_pkg.createMsgCA_TMTran_New tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	@SuppressWarnings("unchecked")
	public static List<String> createMsgCA_TMTran_New_V2(final String sAccNo,
			final String sAmt, final String sBody, final String sTmapptype,
			final String sDorc, final int iServiceID, final String date,
			final String curr) {
		// TODO Auto-generated method stub
		init();

		return (Vector) jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg.createMsgCA_TMTran_New_V2(?,?,?,  ?,?,?, ?,?,  ?,?)}");
				cs.setString(1, sAccNo);
				cs.setString(2, sAmt);
				cs.setString(3, sBody);
				cs.setString(4, sTmapptype);
				cs.setInt(5, iServiceID);
				cs.setString(6, sDorc);

				cs.setString(7, date);
				cs.setString(8, curr);

				cs.registerOutParameter(9, java.sql.Types.VARCHAR);
				cs.registerOutParameter(10, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback() {
			public Object doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					Vector v = new Vector<String>();
					v.add(cs.getString(9));
					v.add(cs.getString(10));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".ca",
							"createMsgCA_TMTran_New_V2=" + e.toString());
					// SendEmail
					// .email("Khong chay duoc store messages_pkg.createMsgCA_TMTran_New_V2 tren oracle! ("
					// + Para.DB_SERVER_IP
					// + ":"
					// + Para.DB_SEVER_PORT
					// + ":"
					// + Para.DB_SID_NAME
					// + ") Loi:"
					// + e.toString());
					SendEmail
							.email("Khong chay duoc store messages_pkg.createMsgCA_TMTran_New_V2 tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	// -- 0:OK; 1: khong tao tin do duoi han muc, ghi co, giao dich dao
	// -- 2: Khong sinh tin voi giao dich da sinh truoc do
	// p_outcode out varchar2,
	// p_outmsg out varchar2

	@SuppressWarnings("unchecked")
	public static ArrayList<String> createMsgCA_TMTran_Nightmode(
			final String action, final String brn, final String time,
			final String sseq, final String tlxmne, final String hostXcd,
			final String hafft, final String tmtxCd, final String txseq,
			final String sAccNo, final String sAmt, final String sTmapptype,
			final String sDorc, final String date7, final String curr,
			final String tellerId, final String status) {
		// TODO Auto-generated method stub
		init();

		return (ArrayList<String>) jdbcTemplate.execute(
				new CallableStatementCreator() {
					public CallableStatement createCallableStatement(
							Connection con) throws SQLException {
						CallableStatement cs = con
								.prepareCall("{call PCK_NIGHT_MODE_ALERT.createMsgCA_NightMode(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
						cs.setString(1, action);
						cs.setString(2, sAccNo);
						cs.setString(3, sAmt);
						cs.setString(4, sTmapptype);
						cs.setString(5, sDorc);
						cs.setString(6, date7);
						cs.setString(7, curr);
						cs.setString(8, tellerId);
						cs.setString(9, status);
						cs.setString(10, brn);
						cs.setString(11, time);
						cs.setString(12, sseq);
						cs.setString(13, tlxmne);
						cs.setString(14, hostXcd);
						cs.setString(15, hafft);
						cs.setString(16, tmtxCd);
						cs.setString(17, txseq);

						cs.registerOutParameter(18, java.sql.Types.VARCHAR);
						cs.registerOutParameter(19, java.sql.Types.VARCHAR);
						return cs;
					}
				}, new CallableStatementCallback() {
					public Object doInCallableStatement(CallableStatement cs) {
						try {
							cs.execute();
							ArrayList<String> result = new ArrayList<String>();
							result.add(cs.getString(18));
							result.add(cs.getString(19));
							return result;
						} catch (Exception e) {
							uti.Func.log(Para.fullPathLog + ".ca24",
									"createMsgCA_NightMode=" + e.toString());
							// SendEmail
							// .email("Khong chay duoc store createMsgCA_NightMode tren oracle! ("
							// + Para.DB_SERVER_IP
							// + ":"
							// + Para.DB_SEVER_PORT
							// + ":"
							// + Para.DB_SID_NAME
							// + ") Loi:"
							// + e.toString());
							SendEmail
									.email("Khong chay duoc store createMsgCA_NightMode tren oracle! ("
											+ Para.DB_ORACLEURL
											+ ") Loi:"
											+ e.toString());
							return null;
						} finally {
							try {
								cs.close();
							} catch (Exception e2) {
							}
						}
					}
				});
	}

	@SuppressWarnings("unchecked")
	public static int Send_SMS_GW(final String p_AppID, final String p_Mobile,
			final String p_SMS_BODY, final String p_BRN, final String p_Cif,
			final String p_Acctno, final String p_IpAddr) {
		init();
		Integer s = (Integer) jdbcTemplate.execute(
				new CallableStatementCreator() {
					public CallableStatement createCallableStatement(
							Connection con) {
						CallableStatement cs = null;
						try {
							cs = con.prepareCall("{call Send_Gateway_SMS(?, ?, ?, ?, ?, ?, ?, ?)}");
							cs.setString(1, p_AppID);
							cs.setString(2, p_Mobile);
							cs.setString(3, p_SMS_BODY);
							cs.setString(4, p_BRN);
							cs.setString(5, p_Cif);
							cs.setString(6, p_Acctno);
							cs.setString(7, p_IpAddr);
							cs.registerOutParameter(8, java.sql.Types.INTEGER);
						} catch (SQLException e) {
							Func.log(Para.fullPathLog + ".Gateway",
									"Send_SMS_GW: " + e.toString());
						}
						return cs;
					}
				}, new CallableStatementCallback() {
					public Object doInCallableStatement(CallableStatement cs) {
						try {
							cs.execute();
							int result = cs.getInt(8);
							return result;
						} catch (SQLException e) {
							Func.log(Para.fullPathLog + ".Gateway",
									"Send_SMS_GW: " + e.toString());
							return null;
						} finally {
							try {
								cs.close();
							} catch (Exception e2) {
							}
						}
					}
				});

		return (s != null ? s : 0);
	}

	public static boolean checkSATransExits(String accountNo, String amt,
			String time, String transCode) {
		init();
		try {
			String sql = "select count(*) COUNTTRANS from ("
					+ " select 1 from v_tmtrand where TMACCTNO = " + accountNo
					+ " AND TMTIMENT = " + time + " AND TMTXAMT = " + amt
					+ " AND TMTXCD = " + transCode + " )";
			int count = JDBCPool.jdbcTemplate.queryForInt(sql);
			return count > 0;
		} catch (Exception ex) {
			Func.logx(Para.fullPathLog + ".tietkiem",
					"checkSATransExits=" + ex.toString());
		} finally {
			JDBCPool.destroy();
		}

		return false;
	}

	public static void callCheckAndGenPOSTrans(final String batchRunDay,
			final String batchRunTime, final String flag,
			final String batchRunCount, final String user) {
		init();
		jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call automsg_pkg.Check_And_Gen_POS_Trans(?,?,?,?,?)}");
				cs.setString(1, batchRunDay);
				cs.setString(2, batchRunTime);
				cs.setString(3, flag);
				cs.setString(4, batchRunCount);
				cs.setString(5, user);
				return cs;
			}
		}, new CallableStatementCallback<Void>() {
			public Void doInCallableStatement(CallableStatement cs)
					throws SQLException {
				try {
					cs.execute();
					return null;
				} catch (Exception e) {
					Func.log(
							Para.fullPathLog,
							"automsg_pkg.Check_And_Gen_POS_Trans="
									+ e.toString());
					SendEmail
							.email("Khong chay duoc store automsg_pkg.CheckAndGenPOSTrans"
									+ " tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:" + e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/**
	 * Log cac thay doi email va sdt do cdc capture
	 * 
	 * @param sCFACCN
	 * @param sCFATYP
	 * @param sCFZSEQ
	 * @param sCFEADC
	 * @param sCFEADD
	 * @param sOLD_CFEADD
	 * @param sCFADLM
	 * @param sCFADL6
	 * @param sCFATIM
	 * @param sCFCIFN
	 * @return
	 */
	public static List<String> logCDCContactChanged(final String sCFACCN,
			final String sCFATYP, final String sCFZSEQ, final String sCFEADC,
			final String sCFEADD, final String sOLD_CFEADD, final String sCFADL6, 
			final String sCFCIFN) {
		init();

		return jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg.PRC_LOG_CDC_CFCONN(?,?,?,?,?,?,?,?,?,?)}");
				cs.setString(1, sCFACCN);
				cs.setString(2, sCFATYP);
				cs.setString(3, sCFZSEQ);
				cs.setString(4, sCFEADC);
				cs.setString(5, sCFEADD);

				cs.setString(6, sOLD_CFEADD);
				cs.setString(7, sCFADL6);
				cs.setString(8, sCFCIFN);

				cs.registerOutParameter(9, java.sql.Types.VARCHAR);
				cs.registerOutParameter(10, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			public List<String> doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					List<String> v = new ArrayList<String>();
					v.add(cs.getString(9));
					v.add(cs.getString(10));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".cfconn",
							"messages_pkg.PRC_LOG_CDC_CFCONN=" + e.toString());
					SendEmail
							.email("Khong chay duoc store messages_pkg.PRC_LOG_CDC_CFCONN tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/**
	 * Sinh tin nhan thong bao so dien thoai bi thay doi
	 * 
	 * @param cif
	 * @param addressType
	 * @param oldPhone
	 * @return
	 */
	public static List<String> createMsgPhoneChanged(final String cif,
			final String addressType, final String oldPhone) {
		init();

		return jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call messages_pkg.create_msg_phone_changed(?,?,?,?,?)}");
				cs.setString(1, cif);
				cs.setString(2, addressType);
				cs.setString(3, oldPhone);

				cs.registerOutParameter(4, java.sql.Types.VARCHAR);
				cs.registerOutParameter(5, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			public List<String> doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					List<String> v = new ArrayList<String>();
					v.add(cs.getString(4));
					v.add(cs.getString(5));
					return v;
				} catch (Exception e) {
					uti.Func.log(
							Para.fullPathLog + ".cfconn",
							"messages_pkg.create_msg_phone_changed="
									+ e.toString());
					SendEmail
							.email("Khong chay duoc store messages_pkg.create_msg_phone_changed tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/**
	 * Log reuqest/response SOA
	 * 
	 * @param messageId
	 * @param executetionSource
	 * @param msgType
	 * @param bussinessDomain
	 * @param serviceVersion
	 * @param messageTimestamp
	 * @param msgBody
	 * @return
	 */
	public static List<String> logRawSOA(final String messageId,
			final String executetionSource, final String msgType,
			final String bussinessDomain, final String serviceVersion,
			final String messageTimestamp, final String msgBody) {
		init();

		return jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call CommonFunctions_Pkg.LOG_RAW_SOA(?,?,?,?,?,?,?,?,?)}");
				cs.setString(1, messageId);
				cs.setString(2, executetionSource);
				cs.setString(3, msgType);
				cs.setString(4, bussinessDomain);
				cs.setString(5, serviceVersion);
				cs.setString(6, messageTimestamp);

//				Clob outClob = con.createClob();
//				Writer clobWriter = outClob.setCharacterStream(1);
//				try {
//					clobWriter.append(msgBody);
//				} catch (IOException e) {
//					uti.Func.log(Para.fullPathLog + ".soa",
//							"clobWriter.append error=" + e.toString());
//				}
				
				Clob outClob = oracle.sql.CLOB.createTemporary(
					      con, false, oracle.sql.CLOB.DURATION_SESSION);
				outClob.setString(1, msgBody);
				cs.setClob(7, outClob);
				cs.registerOutParameter(8, java.sql.Types.NUMERIC);
				cs.registerOutParameter(9, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			public List<String> doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					List<String> v = new ArrayList<String>();
					v.add(cs.getInt(8) + "");
					v.add(cs.getString(9));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".soa",
							"CommonFunctions_Pkg.LOG_RAW_SOA=" + e.toString());
					SendEmail
							.email("Khong chay duoc store CommonFunctions_Pkg.LOG_RAW_SOA tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	/**
	 * Tao message Id, Log thong tin email thay doi truoc khi goi toi SOA
	 * 
	 * @param cif
	 * @param CFEADD
	 * @param oldCFEADD
	 * @param CFADLM
	 * @param CFATIM
	 * @return
	 */
	/*public static List<String> gennerateSOAMessageID(final String cif,
			final String CFEADD, final String oldCFEADD, final String CFADLM,
			final String CFATIM) {
		init();

		return jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call MESSAGES_PKG.PRC_GEN_SOA_MSGID_CFCONN(?,?,?,?,?,?,?,?)}");
				cs.setString(1, cif);
				cs.setString(2, CFEADD);
				cs.setString(3, oldCFEADD);
				cs.setString(4, CFADLM);
				cs.setString(5, CFATIM);

				cs.registerOutParameter(6, java.sql.Types.VARCHAR);
				cs.registerOutParameter(7, java.sql.Types.VARCHAR);
				cs.registerOutParameter(8, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			public List<String> doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					List<String> v = new ArrayList<String>();
					v.add(cs.getString(6));
					v.add(cs.getString(7));
					v.add(cs.getString(8));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".cfconn",
							"MESSAGES_PKG.PRC_GEN_SOA_MSGID_CFCONN=" + e.toString());
					SendEmail
							.email("Khong chay duoc store MESSAGES_PKG.PRC_GEN_SOA_MSGID_CFCONN tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}*/

	public static List<String> createDataEmailChanged(final String cif,
			final String fullName, final String brnCode, final String oldEmail,
			final String newEmail, final String changeDay) {
		init();

		return jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call MESSAGES_PKG.CREATE_DATA_EMAIL_CHANGED(?,?,?,?,?,?,?,?)}");
				cs.setString(1, cif);
				cs.setString(2, fullName);
				cs.setString(3, brnCode);
				cs.setString(4, oldEmail);
				cs.setString(5, newEmail);
				cs.setString(6, changeDay);

				cs.registerOutParameter(7, java.sql.Types.VARCHAR);
				cs.registerOutParameter(8, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			public List<String> doInCallableStatement(CallableStatement cs) {
				try {
					cs.execute();
					List<String> v = new ArrayList<String>();
					v.add(cs.getString(7));
					v.add(cs.getString(8));
					return v;
				} catch (Exception e) {
					uti.Func.log(Para.fullPathLog + ".cfconn",
							"MESSAGES_PKG.CREATE_DATA_EMAIL_CHANGED=" + e.toString());
					SendEmail
							.email("Khong chay duoc store MESSAGES_PKG.CREATE_DATA_EMAIL_CHANGED tren oracle! ("
									+ Para.DB_ORACLEURL
									+ ") Loi:"
									+ e.toString());
					return null;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	public static void main(String[] args) {
		boolean a = JDBCPool.checkSATransExits("12410000067326", "3300.00",
				"93228", "0813  ");
		System.out.println(a);
	}
	
	/**
	 * Sinh tin nhan thong bao chuyen nhuong TKTG
	 * 
	 * @param cif
	 * @return
	 */
	public static List<String> createMsgCDExchange(
			final String AFTER_CIFNO,
			final String AFTER_ACCTNO,
			final String AFTER_ACTYPE,
			final String AFTER_CURTYP,
			final String AFTER_ACNAME,
			final String AFTER_TYPE,
			final String AFTER_BRN,
			final String AFTER_STATUS,
			final String AFTER_CDNUM,
			final String AFTER_CDRSRA,
			final String AFTER_ISSDT,
			final String BEFORE_CIFNO,
			final String BEFORE_ACNAME,
			final String ORG_BALANCE) {
		init();

		return jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call PCK_SavingAccount.GEN_SMS_CHUYEN_NHUONG_TKTG(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
				cs.setString(1, AFTER_CIFNO);
				cs.setString(2, AFTER_ACCTNO);
				cs.setString(3, AFTER_ACTYPE);
				cs.setString(4, AFTER_CURTYP);
				cs.setString(5, AFTER_ACNAME);
				cs.setString(6, AFTER_TYPE);
				cs.setString(7, AFTER_BRN);
				cs.setString(8, AFTER_STATUS);
				cs.setString(9, AFTER_CDNUM);
				cs.setString(10, AFTER_CDRSRA);
				cs.setString(11, AFTER_ISSDT);
				cs.setString(12, BEFORE_CIFNO);
				cs.setString(13, BEFORE_ACNAME);
				cs.setString(14, ORG_BALANCE);

				cs.registerOutParameter(15, java.sql.Types.VARCHAR);
				cs.registerOutParameter(16, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			public List<String> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException{
				try {
					cs.execute();
					List<String> v = new ArrayList<String>();
					v.add(cs.getString(15));
					v.add(cs.getString(16));
					return v;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}

	public static List<String> createMsgCDRenew(
			final String AFTER_CIFNO,
			final String AFTER_ACCTNO,
			final String AFTER_ACTYPE,
			final String AFTER_CURTYP,
			final String AFTER_ACNAME,
			final String AFTER_TYPE,
			final String AFTER_BRN,
			final String AFTER_STATUS,
			final String AFTER_CDNUM,
			final String AFTER_CDRSRA,
			final String AFTER_ISSDT,
			final String BEFORE_CDRSRA,
			final String ORG_BALANCE) {
		init();

		return jdbcTemplate.execute(new CallableStatementCreator() {
			public CallableStatement createCallableStatement(Connection con)
					throws SQLException {
				CallableStatement cs = con
						.prepareCall("{call PCK_SavingAccount.GEN_SMS_CAPCN_TG(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}");
				cs.setString(1, AFTER_CIFNO);
				cs.setString(2, AFTER_ACCTNO);
				cs.setString(3, AFTER_ACTYPE);
				cs.setString(4, AFTER_CURTYP);
				cs.setString(5, AFTER_ACNAME);
				cs.setString(6, AFTER_TYPE);
				cs.setString(7, AFTER_BRN);
				cs.setString(8, AFTER_STATUS);
				cs.setString(9, AFTER_CDNUM);
				cs.setString(10, AFTER_CDRSRA);
				cs.setString(11, AFTER_ISSDT);
				cs.setString(12, BEFORE_CDRSRA);
				cs.setString(13, ORG_BALANCE);

				cs.registerOutParameter(14, java.sql.Types.VARCHAR);
				cs.registerOutParameter(15, java.sql.Types.VARCHAR);
				return cs;
			}
		}, new CallableStatementCallback<List<String>>() {
			public List<String> doInCallableStatement(CallableStatement cs) throws SQLException, DataAccessException{
				try {
					cs.execute();
					List<String> v = new ArrayList<String>();
					v.add(cs.getString(14));
					v.add(cs.getString(15));
					return v;
				} finally {
					try {
						cs.close();
					} catch (Exception e2) {
					}
				}
			}
		});
	}
	
	public static List<String> createMsgMoTKCA(final String cif, final String account,
			final String dateOpen7, final String timeOpen, final String phoneList) {
			init();

			return jdbcTemplate.execute(new CallableStatementCreator() {
				public CallableStatement createCallableStatement(Connection con)
						throws SQLException {
					CallableStatement cs = con
							.prepareCall("{call MESSAGES_PKG.CREATE_MSG_MO_TKCA(?,?,?,?,?,?,?)}");
					cs.setString(1, cif);
					cs.setString(2, account);
					cs.setString(3, dateOpen7);
					cs.setString(4, timeOpen);
					cs.setString(5, phoneList);

					cs.registerOutParameter(6, java.sql.Types.VARCHAR);
					cs.registerOutParameter(7, java.sql.Types.VARCHAR);
					return cs;
				}
			}, new CallableStatementCallback<List<String>>() {
				public List<String> doInCallableStatement(CallableStatement cs) {
					try {
						cs.execute();
						List<String> v = new ArrayList<String>();
						v.add(cs.getString(6));
						v.add(cs.getString(7));
						return v;
					} catch (Exception e) {
						uti.Func.log(
								Para.fullPathLog + ".motkthanhtoan.pubsub",
								"messages_pkg.create_msg_mo_TKCA="
										+ Func.getStackTrace(e));
						return null;
					} finally {
						try {
							cs.close();
						} catch (Exception e2) {
						}
					}
				}
			});
		}
}