498 lines
20 KiB
Plaintext
Executable File
498 lines
20 KiB
Plaintext
Executable File
package com.fp.sessionbeans.ejb.cobis;
|
|
|
|
import java.sql.CallableStatement;
|
|
import java.sql.Connection;
|
|
import java.sql.Date;
|
|
import java.sql.PreparedStatement;
|
|
import java.sql.ResultSet;
|
|
import java.sql.Types;
|
|
import java.util.ArrayList;
|
|
import java.util.HashMap;
|
|
import java.util.List;
|
|
import java.util.Map;
|
|
|
|
import javax.ejb.Stateless;
|
|
import javax.ejb.TransactionManagement;
|
|
import javax.ejb.TransactionManagementType;
|
|
import javax.persistence.EntityManager;
|
|
import javax.persistence.EntityManagerFactory;
|
|
import javax.persistence.Persistence;
|
|
|
|
import com.fp.dto.query.QueryBean;
|
|
import com.fp.dto.query.QueryCriteria;
|
|
import com.fp.sessionbeans.ejb.interfaces.CobisBeanLocal;
|
|
|
|
/**
|
|
* Session Bean utilitario para obtener informacion de cobis.
|
|
* @author Jorge Vaca.
|
|
* @version 2.1
|
|
*/
|
|
@Stateless
|
|
@TransactionManagement(value = TransactionManagementType.BEAN)
|
|
public class CobisBean implements CobisBeanLocal {
|
|
|
|
/**
|
|
* Entrega una lista de clientes de Cobis.
|
|
* @return List<Map<String, Object>>
|
|
* @throws Exception
|
|
*/
|
|
@Override
|
|
@SuppressWarnings("deprecation")
|
|
public List<Map<String, Object>> getClientes(QueryBean queryBean) throws Exception {
|
|
EntityManagerFactory emf = null;
|
|
EntityManager em = null;
|
|
List<Map<String, Object>> ldata = new ArrayList<Map<String, Object>>();
|
|
Integer page = queryBean.getPage();
|
|
Integer rowsPerPage = queryBean.getPageSize();
|
|
Connection connection = null;
|
|
ResultSet resultSet = null;
|
|
PreparedStatement preparedStatement = null;
|
|
try {
|
|
emf = Persistence.createEntityManagerFactory("cobis");
|
|
em = emf.createEntityManager();
|
|
//connection = ((Session) em.getDelegate()).connection();
|
|
String SQL_CLIENT = "select en_ente, en_ced_ruc, en_nomlar, en_nombre, p_p_apellido, p_s_apellido, en_subtipo from cobis..cl_ente";
|
|
SQL_CLIENT = this.getSentenceWithFilter(SQL_CLIENT, queryBean.getCriteria());
|
|
List<Object> lcriteria = this.getCriteria(queryBean.getCriteria());
|
|
preparedStatement = connection.prepareStatement(SQL_CLIENT + " order by en_nombre, p_p_apellido", ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
|
for (int i = 0; i < lcriteria.size(); i++) {
|
|
Object value = lcriteria.get(i);
|
|
preparedStatement.setObject(i + 1, value);
|
|
}
|
|
resultSet = preparedStatement.executeQuery();
|
|
if (page > 1) {
|
|
resultSet.setFetchSize(rowsPerPage + 1);
|
|
resultSet.absolute((page - 1) * rowsPerPage);
|
|
}
|
|
for (int i = 0; i < rowsPerPage; i++) {
|
|
if (!resultSet.next()) {
|
|
break;
|
|
}
|
|
ldata.add(this.getResulsetClientes(resultSet));
|
|
}
|
|
} catch (Exception ex) {
|
|
throw ex;
|
|
} finally {
|
|
this.closeSesion(preparedStatement, resultSet, em,emf);
|
|
}
|
|
return ldata;
|
|
}
|
|
|
|
/**
|
|
* Metodo que se encarga de consultar los datos para consultar el archivo
|
|
* @param corte
|
|
* @param fecha
|
|
* @return
|
|
* @throws Exception
|
|
*/
|
|
@Override
|
|
public List<String> getDataSendFile(Integer corte, Date fecha) throws Exception {
|
|
List<String> ldata = new ArrayList<String>();
|
|
EntityManagerFactory emf = null;
|
|
EntityManager em = null;
|
|
Connection connection = null;
|
|
ResultSet resultSet = null;
|
|
PreparedStatement preparedStatement = null;
|
|
try {
|
|
emf = Persistence.createEntityManagerFactory("cobis");
|
|
em = emf.createEntityManager();
|
|
//connection = ((Session) em.getDelegate()).connection();
|
|
String SQL =
|
|
"SELECT ta_texto"
|
|
+ " FROM cobis .. textos_ach"
|
|
+ " WHERE ta_corte = ?"
|
|
+ " AND ta_enviado = ?"
|
|
+ " AND ta_fecha = ?"
|
|
+ " ORDER BY ta_secuencial";
|
|
preparedStatement = connection.prepareStatement(SQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
|
preparedStatement.setInt(1, corte);
|
|
preparedStatement.setString(2, "N");
|
|
preparedStatement.setDate(3, fecha);
|
|
resultSet = preparedStatement.executeQuery();
|
|
while (resultSet.next()) {
|
|
ldata.add(resultSet.getString(1));
|
|
}
|
|
} catch (Exception ex) {
|
|
throw ex;
|
|
} finally {
|
|
this.closeSesion(preparedStatement, resultSet, em,emf);
|
|
}
|
|
return ldata;
|
|
}
|
|
|
|
/**
|
|
* Metodo que obtiene los datos de la consulta
|
|
* @param resultSet
|
|
* @return Map<String, Object>
|
|
* @throws Exception
|
|
*/
|
|
private Map<String, Object> getResulsetClientes(ResultSet resultSet) throws Exception {
|
|
Map<String, Object> map = new HashMap<String, Object>();
|
|
Object valor = resultSet.getObject("en_ente");
|
|
map.put("en_ente", valor);
|
|
valor = resultSet.getObject("en_ced_ruc");
|
|
map.put("en_ced_ruc", valor);
|
|
valor = resultSet.getObject("en_nomlar");
|
|
map.put("en_nomlar", valor);
|
|
valor = resultSet.getObject("en_nombre");
|
|
map.put("en_nombre", valor);
|
|
valor = resultSet.getObject("p_p_apellido");
|
|
map.put("p_p_apellido", valor);
|
|
valor = resultSet.getObject("p_s_apellido");
|
|
map.put("p_s_apellido", valor);
|
|
String tipopersona = (String) resultSet.getObject("en_subtipo");
|
|
map.put("en_subtipo", tipopersona);
|
|
if (tipopersona.compareTo("C") == 0) {
|
|
valor = resultSet.getObject("en_nombre");
|
|
} else {
|
|
valor = resultSet.getObject("en_nomlar");
|
|
}
|
|
map.put("name", valor);
|
|
return map;
|
|
}
|
|
|
|
/**
|
|
* Metodo que entrega una lista de cuentas de ahorro
|
|
* @param queryBean
|
|
* @return
|
|
* @throws Exception
|
|
*/
|
|
@Override
|
|
public List<Map<String, Object>> getCuentasAhorro(QueryBean queryBean) throws Exception {
|
|
List<Map<String, Object>> ldata = new ArrayList<Map<String, Object>>();
|
|
EntityManagerFactory emf = null;
|
|
EntityManager em = null;
|
|
Integer page = queryBean.getPage();
|
|
Integer rowsPerPage = queryBean.getPageSize();
|
|
Connection connection = null;
|
|
ResultSet resultSet = null;
|
|
PreparedStatement preparedStatement = null;
|
|
try {
|
|
emf = Persistence.createEntityManagerFactory("cobis");
|
|
em = emf.createEntityManager();
|
|
//connection = ((Session) em.getDelegate()).connection();
|
|
String SQL_SAVING_ACCOUNT = "select ah_cuenta, rtrim(ah_cta_banco) as ah_cta_banco, ah_nombre, ah_cliente from cob_ahorros..ah_cuenta";
|
|
SQL_SAVING_ACCOUNT = this.getSentenceWithFilter(SQL_SAVING_ACCOUNT, queryBean.getCriteria());
|
|
List<Object> lcriteria = this.getCriteria(queryBean.getCriteria());
|
|
preparedStatement = connection.prepareStatement(SQL_SAVING_ACCOUNT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
|
for (int i = 0; i < lcriteria.size(); i++) {
|
|
Object value = lcriteria.get(i);
|
|
preparedStatement.setObject(i + 1, value);
|
|
}
|
|
resultSet = preparedStatement.executeQuery();
|
|
if (page > 1) {
|
|
resultSet.setFetchSize(rowsPerPage + 1);
|
|
resultSet.absolute((page - 1) * rowsPerPage);
|
|
}
|
|
for (int i = 0; i < rowsPerPage; i++) {
|
|
if (!resultSet.next()) {
|
|
break;
|
|
}
|
|
ldata.add(this.getResulsetSavingsAccounts(resultSet));
|
|
}
|
|
} catch (Exception ex) {
|
|
throw ex;
|
|
} finally {
|
|
this.closeSesion(preparedStatement, resultSet, em,emf);
|
|
}
|
|
return ldata;
|
|
}
|
|
|
|
/**
|
|
* Metodo que obtiene los datos de la consulta
|
|
* @param resultSet
|
|
* @return Map<String, Object>
|
|
* @throws Exception
|
|
*/
|
|
private Map<String, Object> getResulsetSavingsAccounts(ResultSet resultSet) throws Exception {
|
|
Map<String, Object> map = new HashMap<String, Object>();
|
|
Object valor = resultSet.getObject("ah_cuenta");
|
|
map.put("ah_cuenta", valor);
|
|
valor = resultSet.getObject("ah_cta_banco");
|
|
map.put("ah_cta_banco", valor);
|
|
valor = resultSet.getObject("ah_nombre");
|
|
map.put("ah_nombre", valor);
|
|
valor = resultSet.getObject("ah_cliente");
|
|
map.put("ah_cliente", valor);
|
|
return map;
|
|
}
|
|
|
|
/**
|
|
* Metodo que obtiene los datos de la consulta
|
|
* @param resultSet
|
|
* @return Map<String, Object>
|
|
* @throws Exception
|
|
*/
|
|
private Map<String, Object> getResulsetCheckingAccounts(ResultSet resultSet) throws Exception {
|
|
Map<String, Object> map = new HashMap<String, Object>();
|
|
Object valor = resultSet.getObject("cc_ctacte");
|
|
map.put("cc_ctacte", valor);
|
|
valor = resultSet.getObject("cc_cta_banco");
|
|
map.put("cc_cta_banco", valor);
|
|
valor = resultSet.getObject("cc_nombre");
|
|
map.put("cc_nombre", valor);
|
|
valor = resultSet.getObject("cc_cliente");
|
|
map.put("cc_cliente", valor);
|
|
return map;
|
|
}
|
|
|
|
/**
|
|
* Metodo que entrega una lista de cuentas corrientes
|
|
* @param queryBean
|
|
* @return
|
|
* @throws Exception
|
|
*/
|
|
@Override
|
|
public List<Map<String, Object>> getCuentasCorrientes(QueryBean queryBean) throws Exception {
|
|
List<Map<String, Object>> ldata = new ArrayList<Map<String, Object>>();
|
|
Integer page = queryBean.getPage();
|
|
Integer rowsPerPage = queryBean.getPageSize();
|
|
EntityManagerFactory emf = null;
|
|
EntityManager em = null;
|
|
Connection connection = null;
|
|
ResultSet resultSet = null;
|
|
PreparedStatement preparedStatement = null;
|
|
try {
|
|
emf = Persistence.createEntityManagerFactory("cobis");
|
|
em = emf.createEntityManager();
|
|
//connection = ((Session) em.getDelegate()).connection();
|
|
String SQL_CHECKING_ACCOUNT = "select cc_ctacte, rtrim(cc_cta_banco) as cc_cta_banco, cc_nombre, cc_cliente from cob_cuentas..cc_ctacte";
|
|
SQL_CHECKING_ACCOUNT = this.getSentenceWithFilter(SQL_CHECKING_ACCOUNT, queryBean.getCriteria());
|
|
List<Object> lcriteria = this.getCriteria(queryBean.getCriteria());
|
|
preparedStatement = connection.prepareStatement(SQL_CHECKING_ACCOUNT, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
|
for (int i = 0; i < lcriteria.size(); i++) {
|
|
Object value = lcriteria.get(i);
|
|
preparedStatement.setObject(i + 1, value);
|
|
}
|
|
resultSet = preparedStatement.executeQuery();
|
|
if (page > 1) {
|
|
resultSet.setFetchSize(rowsPerPage + 1);
|
|
resultSet.absolute((page - 1) * rowsPerPage);
|
|
}
|
|
for (int i = 0; i < rowsPerPage; i++) {
|
|
if (!resultSet.next()) {
|
|
break;
|
|
}
|
|
ldata.add(this.getResulsetCheckingAccounts(resultSet));
|
|
}
|
|
} catch (Exception ex) {
|
|
throw ex;
|
|
} finally {
|
|
this.closeSesion(preparedStatement, resultSet, em,emf);
|
|
}
|
|
return ldata;
|
|
}
|
|
|
|
/**
|
|
* Sentencia SQL con filtros si estos son enviados
|
|
* @param sentenceSQL
|
|
* @param lQueryCriteria
|
|
* @return
|
|
* @throws Exception
|
|
*/
|
|
private String getSentenceWithFilter(String sentenceSQL, List<QueryCriteria> lQueryCriteria) throws Exception {
|
|
boolean isFirstCriteria = true;
|
|
for (int i = 0; i < lQueryCriteria.size(); i++) {
|
|
QueryCriteria queryCriteria = lQueryCriteria.get(i);
|
|
String attribute = queryCriteria.getProperty();
|
|
Object value = queryCriteria.getValue();
|
|
String operator = queryCriteria.getOperation();
|
|
if (attribute != null && value != null && operator != null) {
|
|
if (isFirstCriteria) {
|
|
sentenceSQL += " where " + attribute + " " + operator + " ?";
|
|
isFirstCriteria = false;
|
|
} else {
|
|
sentenceSQL += " and " + attribute + " " + operator + " ?";
|
|
}
|
|
}
|
|
}
|
|
return sentenceSQL;
|
|
}
|
|
|
|
/**
|
|
* Metodo que se encarga de obtener los criterios con los que se debe realizar la consulta
|
|
* @param lQueryCriteria
|
|
* @throws Exception
|
|
*/
|
|
protected List<Object> getCriteria(List<QueryCriteria> lQueryCriteria) throws Exception {
|
|
List<Object> lCriteria = new ArrayList<Object>();
|
|
for (int i = 0; i < lQueryCriteria.size(); i++) {
|
|
QueryCriteria queryCriteria = lQueryCriteria.get(i);
|
|
String attribute = queryCriteria.getProperty();
|
|
Object value = queryCriteria.getValue();
|
|
String operator = queryCriteria.getOperation();
|
|
if (attribute != null && value != null && operator != null) {
|
|
lCriteria.add(value);
|
|
}
|
|
}
|
|
return lCriteria;
|
|
}
|
|
|
|
/**
|
|
* Entrega datos del repositorio de cobis
|
|
* @param queryBean
|
|
* @return
|
|
* @throws Exception
|
|
*/
|
|
@Override
|
|
public List<Map<String, Object>> getRepositorio(QueryBean queryBean) throws Exception {
|
|
List<Map<String, Object>> ldata = new ArrayList<Map<String, Object>>();
|
|
Integer page = queryBean.getPage();
|
|
Integer rowsPerPage = queryBean.getPageSize();
|
|
EntityManagerFactory emf = null;
|
|
EntityManager em = null;
|
|
Connection connection = null;
|
|
ResultSet resultSet = null;
|
|
PreparedStatement preparedStatement = null;
|
|
try {
|
|
emf = Persistence.createEntityManagerFactory("cobis");
|
|
em = emf.createEntityManager();
|
|
//connection = ((Session) em.getDelegate()).connection();
|
|
String SQL_REPOSITORIO = "select p.pd_descripcion as ea_producto,sum(r.ea_valor)as ea_valor,sum(ea_comision)as ea_comision from cobis..repositorio_ach t ,cobis..envios_ach_wfl r,cobis..cl_producto p";
|
|
SQL_REPOSITORIO = this.getSentenceWithFilter(SQL_REPOSITORIO, queryBean.getCriteria()) + " and t.ra_secuencial=r.ea_sec_corte and t.ra_corte = r.ea_corte and r.ea_producto=p.pd_abreviatura and t.ra_estado ='P' group by p.pd_descripcion";
|
|
List<Object> lcriteria = this.getCriteria(queryBean.getCriteria());
|
|
preparedStatement = connection.prepareStatement(SQL_REPOSITORIO, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
|
|
for (int i = 0; i < lcriteria.size(); i++) {
|
|
Object value = lcriteria.get(i);
|
|
preparedStatement.setObject(i + 1, value);
|
|
}
|
|
resultSet = preparedStatement.executeQuery();
|
|
if (page > 1) {
|
|
resultSet.setFetchSize(rowsPerPage + 1);
|
|
resultSet.absolute((page - 1) * rowsPerPage);
|
|
}
|
|
for (int i = 0; i < rowsPerPage; i++) {
|
|
if (!resultSet.next()) {
|
|
break;
|
|
}
|
|
ldata.add(this.getResulsetRepositorio(resultSet));
|
|
}
|
|
} catch (Exception ex) {
|
|
throw ex;
|
|
} finally {
|
|
this.closeSesion(preparedStatement, resultSet, em,emf);
|
|
}
|
|
return ldata;
|
|
}
|
|
|
|
/**
|
|
* Metodo que obtiene los datos de la consulta
|
|
* @param resultSet
|
|
* @return Map<String, Object>
|
|
* @throws Exception
|
|
*/
|
|
private Map<String, Object> getResulsetRepositorio(ResultSet resultSet) throws Exception {
|
|
Map<String, Object> map = new HashMap<String, Object>();
|
|
Object valor = resultSet.getObject("ea_producto");
|
|
if (valor.toString().compareTo("OPERACIONES ACH") == 0) {
|
|
valor = "OPERACIONES ACH-RECURRENTES";
|
|
}
|
|
map.put("ea_producto", valor);
|
|
valor = resultSet.getObject("ea_valor");
|
|
map.put("ea_valor", valor);
|
|
valor = resultSet.getObject("ea_comision");
|
|
map.put("ea_comision", valor);
|
|
return map;
|
|
}
|
|
|
|
/**
|
|
* Metodo que cierra sesiones a la base de datos abiertas
|
|
* @param preparedStatement
|
|
* @param resultSet
|
|
* @param entityManager
|
|
* @throws Exception
|
|
*/
|
|
private void closeSesion(PreparedStatement preparedStatement, ResultSet resultSet, EntityManager em,EntityManagerFactory emf) throws Exception {
|
|
if (resultSet != null) {
|
|
resultSet.close();
|
|
}
|
|
if (preparedStatement != null) {
|
|
preparedStatement.close();
|
|
}
|
|
if (em != null) {
|
|
try {
|
|
em.clear();
|
|
} catch (Exception ex) {
|
|
}
|
|
em.clear();
|
|
em.close();
|
|
emf.close();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Metodo que cierra sesiones a la base de datos abiertas
|
|
* @param preparedStatement
|
|
* @param resultSet
|
|
* @param connection
|
|
* @throws Exception
|
|
*/
|
|
private void closeSesion(PreparedStatement preparedStatement, ResultSet resultSet, Connection connection) throws Exception {
|
|
if (preparedStatement != null) {
|
|
preparedStatement.close();
|
|
}
|
|
if (resultSet != null) {
|
|
resultSet.close();
|
|
}
|
|
if (connection != null) {
|
|
connection.close();
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Metodo que llena la tabla textos_ach, con los datos de enviadas.
|
|
* @param pTransaction Codigo de transaccion con el cual se generan los textos ach.
|
|
* @param pOperation Codigo de operaicon.
|
|
* @return Map<String, Object>
|
|
* @throws Exception
|
|
*/
|
|
@Override
|
|
public Map<String, Object> generaTextoAch(Integer pTransaction, String pOperation) throws Exception {
|
|
EntityManagerFactory emf = null;
|
|
EntityManager em = null;
|
|
Connection c = null;
|
|
CallableStatement cs = null;
|
|
try {
|
|
emf = Persistence.createEntityManagerFactory("cobis");
|
|
em = emf.createEntityManager();
|
|
//c = ((Session) em.getDelegate()).connection();
|
|
// c.setTransactionIsolation(Connection.TRANSACTION_NONE);
|
|
cs = c.prepareCall("{ call maia..sp_genera_texto_ach(?,?,?,?,?) }");
|
|
cs.setInt(1, pTransaction);// Codigo de transaccion 2804
|
|
cs.setString(2, pOperation);// Codigo de operacion "A"
|
|
cs.setInt(3, 0); //Numero de corte con el cual se genera el archivo texto de enviadas.
|
|
cs.setString(4, ""); // Codigo de resultado
|
|
cs.setString(5, ""); // Texto de resultado
|
|
// parametro de salida
|
|
cs.registerOutParameter(3, Types.INTEGER);
|
|
cs.registerOutParameter(4, Types.CHAR);
|
|
cs.registerOutParameter(5, Types.CHAR);
|
|
// ejecutar el SP
|
|
cs.execute();
|
|
Integer corte = cs.getInt(3);
|
|
String cresultado = cs.getString(4);
|
|
String textoerror = cs.getString(5);
|
|
Map<String, Object> m = new HashMap<String, Object>();
|
|
m.put("corte", corte);
|
|
m.put("cresultado", cresultado);
|
|
m.put("textoerror", textoerror);
|
|
return m;
|
|
} catch (Exception e) {
|
|
throw e;
|
|
} finally {
|
|
if (cs != null) {
|
|
cs.close();
|
|
}
|
|
if (c != null) {
|
|
c.close();
|
|
}
|
|
if (em != null) {
|
|
em.clear();
|
|
em.close();
|
|
emf.close();
|
|
}
|
|
}
|
|
|
|
}
|
|
}
|