public static List<Sucursal> buscarSucursales() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Sucursal> sucursales = new ArrayList();
cs = connection.prepareCall("{ call listaSucursal() }");
rs = cs.executeQuery();
while (rs.next()) {
Sucursal suc = new Sucursal(rs.getInt("idSucursal"), rs.getString("nombreSucursal"));
sucursales.add(suc);
}
return sucursales;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static List<Departamento> buscarDepartamentos() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Departamento> departamentos = new ArrayList();
cs = connection.prepareCall("{ call listaDepartamento() }");
rs = cs.executeQuery();
while (rs.next()) {
Departamento dpo =
new Departamento(rs.getInt("idDepartamento"), rs.getString("nombreDepartamento"));
departamentos.add(dpo);
}
return departamentos;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static int updateStudy(String code, Study study) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"UPDATE study SET "
+ "name = ?, description = ?, question = ? , imageURL = ?, requestedParticipants = ?,"
+ "numOfParticipants = ?, status = ?"
+ "WHERE code = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, study.getName());
ps.setString(2, study.getDescription());
ps.setString(3, study.getQuestion());
ps.setString(4, study.getImageURL());
ps.setInt(5, study.getRequestedParticipants());
ps.setInt(6, study.getNumOfParticipants());
ps.setString(7, study.getStatus());
ps.setString(8, code);
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static List<Pago> buscarPagos() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Pago> pagos = new ArrayList();
cs = connection.prepareCall("{ call listaPago() }");
rs = cs.executeQuery();
while (rs.next()) {
Pago pag = new Pago(rs.getInt("idMetodoPago"), rs.getString("nombreMetodoPago"));
pagos.add(pag);
}
return pagos;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static int addStudy(Study study) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
String query =
"INSERT INTO study (name,description,creatorEmail, dateCreated, "
+ "question, imageURL, requestedParticipants, numOfParticipants, status) "
+ "VALUES (?,?,?,?,?,?,?,?,?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, study.getName());
ps.setString(2, study.getDescription());
ps.setString(3, study.getCreatorEmail());
ps.setTimestamp(4, study.getDateCreated());
ps.setString(5, study.getQuestion());
ps.setString(6, study.getImageURL());
ps.setInt(7, study.getRequestedParticipants());
ps.setInt(8, study.getNumOfParticipants());
ps.setString(9, study.getStatus());
return ps.executeUpdate();
} catch (SQLException e) {
System.out.println(e);
return 0;
} finally {
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static List<Usuario> buscarUsuarios() {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Usuario> usuas = new ArrayList();
cs = connection.prepareCall("{ call cajeroReporte() }");
rs = cs.executeQuery();
while (rs.next()) {
Usuario usa =
new Usuario(
rs.getString("nombreUsuario"),
rs.getString("apellidoPaterno"),
rs.getInt("idUsuario"));
usuas.add(usa);
}
return usuas;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static int insertarVenta(Venta v) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
Venta ven = null;
try {
cs = connection.prepareCall("{ call insertVenta(?, ?, ?, ?) }");
cs.setInt(1, v.getUsuarioVenta().getId());
cs.setDouble(2, v.getSubtotal());
cs.setInt(3, v.getPagoVenta().getIdPago());
cs.setInt(4, v.getUsuarioVenta().getSucursal().getIdSucursal());
rs = cs.executeQuery();
while (rs.next()) {
ven = new Venta(rs.getInt("idVenta"));
}
return ven.getIdVenta();
} catch (Exception ex) {
ex.printStackTrace();
return 0;
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// get a connection
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
String sqlStatement = request.getParameter("sqlStatement");
String sqlResult = "";
try {
// create a statement
Statement statement = connection.createStatement();
// parse the SQL string
sqlStatement = sqlStatement.trim();
if (sqlStatement.length() >= 6) {
String sqlType = sqlStatement.substring(0, 6);
if (sqlType.equalsIgnoreCase("select")) {
// create the HTML for the result set
ResultSet resultSet = statement.executeQuery(sqlStatement);
sqlResult = SQLUtil.getHtmlTable(resultSet);
resultSet.close();
} else {
int i = statement.executeUpdate(sqlStatement);
if (i == 0) {
sqlResult = "<p>The statement executed successfully.</p>";
} else { // an INSERT, UPDATE, or DELETE statement
sqlResult = "<p>The statement executed successfully.<br>" + i + " row(s) affected.</p>";
}
}
}
statement.close();
connection.close();
} catch (SQLException e) {
sqlResult = "<p>Error executing the SQL statement: <br>" + e.getMessage() + "</p>";
} finally {
pool.freeConnection(connection);
}
HttpSession session = request.getSession();
session.setAttribute("sqlResult", sqlResult);
session.setAttribute("sqlStatement", sqlStatement);
String url = "/index.jsp";
getServletContext().getRequestDispatcher(url).forward(request, response);
}
public static void eliminar(int id) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
try {
cs = connection.prepareCall("{ call UsuarioBorrar(?, ?) }");
cs.setInt(1, id);
cs.registerOutParameter(2, Types.BIT);
cs.execute();
byte res = cs.getByte(2);
System.out.println("[Borrar] Resultado=" + res);
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static void insertar(Usuario U) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
try {
cs = connection.prepareCall("{ call UsuarioInsertar(?, ?, ?, ?, ?) }");
cs.setString(1, U.getNombreUsuario());
cs.setString(2, U.getApellidoPaterno());
cs.setString(3, U.getApellidoMterno());
cs.execute();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static ArrayList<Study> getOpenAndNotParticipated(String emailAddress) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query =
"SELECT * FROM study WHERE NOT EXISTS(SELECT * FROM answer WHERE study.code = answer.code AND email = ?)"
+ "AND status = ? ";
try {
ps = connection.prepareStatement(query);
ps.setString(1, emailAddress);
ps.setString(2, "open");
rs = ps.executeQuery();
Study study = null;
ArrayList<Study> studies = new ArrayList<Study>();
while (rs.next()) {
study = new Study();
study.setName(rs.getString("name"));
study.setCode(rs.getString("code"));
study.setDescription(rs.getString("description"));
study.setCreatorEmail(rs.getString("creatorEmail"));
study.setDateCreated(rs.getTimestamp("dateCreated"));
study.setQuestion(rs.getString("question"));
study.setRequestedParticipants(rs.getInt("requestedParticipants"));
study.setNumOfParticipants(rs.getInt("numOfParticipants"));
study.setStatus(rs.getString("status"));
study.setImageURL(rs.getString("imageURL"));
studies.add(study);
}
return studies;
} catch (SQLException e) {
System.out.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
// =============================================================
public static boolean UserExists(String username) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT username FROM user_info " + "WHERE username = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, username);
rs = ps.executeQuery();
return rs.next();
} catch (SQLException e) {
e.printStackTrace();
return false;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static void actualizar(Usuario u) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
try {
cs = connection.prepareCall("{ call actualizar_usuario(?, ?, ?, ?, ?, ?) }");
cs.setString(1, u.getNombreUsuario());
cs.setString(2, u.getApellidoPaterno());
cs.setString(3, u.getApellidoMterno());
cs.setByte(4, u.getSexo());
cs.setInt(5, u.getSucursal().getIdSucursal());
cs.setInt(6, u.getIdUsuario());
cs.execute();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static void insertarVentaVenta(Venta v, int cantidad) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
cs = connection.prepareCall("{ call insertVentaVenta(?, ?, ?, ?, ?) }");
cs.setInt(1, v.getIdVenta());
cs.setInt(2, v.getCantidadVenta());
cs.setInt(3, v.getArticuloVenta().getIdArticulo());
cs.setInt(4, v.getDepartamentoVenta().getIdDepartamento());
cs.setInt(5, cantidad);
cs.executeQuery();
} catch (Exception ex) {
ex.printStackTrace();
} finally {
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
public static UserInfo selectUser(String username) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM user_info " + "WHERE username = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, username);
rs = ps.executeQuery();
UserInfo user = null;
if (rs.next()) {
user = new UserInfo();
user.setFirstName(rs.getString("firstName"));
user.setLastName(rs.getString("lastName"));
user.setAddress(rs.getString("address"));
user.setAffiliation(rs.getString("affiliation"));
user.setMemDur(rs.getString("memDur"));
user.setUsername(rs.getString("username"));
user.setPassword(rs.getString("password"));
user.setCardType(rs.getString("cardType"));
user.setCardNumber(rs.getString("cardNumber"));
user.setCardNumber(rs.getString("email"));
}
return user;
} catch (SQLException e) {
e.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static Study getStudy(String studyCode) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
String query = "SELECT * FROM study " + "WHERE code = ?";
try {
ps = connection.prepareStatement(query);
ps.setString(1, studyCode);
rs = ps.executeQuery();
Study study = null;
if (rs.next()) {
study = new Study();
study.setName(rs.getString("name"));
study.setCode(rs.getString("code"));
study.setDescription(rs.getString("description"));
study.setCreatorEmail(rs.getString("creatorEmail"));
study.setDateCreated(rs.getTimestamp("dateCreated"));
study.setQuestion(rs.getString("question"));
study.setRequestedParticipants(rs.getInt("requestedParticipants"));
study.setNumOfParticipants(rs.getInt("numOfParticipants"));
study.setStatus(rs.getString("status"));
study.setImageURL(rs.getString("imageURL"));
}
return study;
} catch (SQLException e) {
System.out.println(e);
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
// -----
public static int insert(UserInfo user) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
PreparedStatement ps = null;
ResultSet rs = null;
// This method adds a new record to the Users table in the database
String query =
"INSERT INTO user_info (firstName, lastName, address, affiliation, "
+ "memDur, username, password, cardType, cardNumber, email) "
+ "VALUES (?, ?, ?, ?, "
+ "?, ?, ?, ?, ?, ?)";
try {
ps = connection.prepareStatement(query);
ps.setString(1, user.getFirstName());
ps.setString(2, user.getLastName());
ps.setString(3, user.getAddress());
ps.setString(4, user.getAffiliation());
ps.setString(5, user.getMemDur());
ps.setString(6, user.getUsername());
ps.setString(7, user.getPassword());
ps.setString(8, user.getCardType());
ps.setString(9, user.getCardNumber());
ps.setString(10, user.getEmail());
return ps.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
return 0;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closePreparedStatement(ps);
pool.freeConnection(connection);
}
}
public static List<Venta> buscaVentas(
String fechaInicial,
String fechaFinal,
int idPagoV,
int idDepartamento,
int idSucursalV,
int idUsuarioV) {
ConnectionPool pool = ConnectionPool.getInstance();
Connection connection = pool.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
List<Venta> ventas = new ArrayList();
cs = connection.prepareCall("{ call reporteVenta(?,?,?,?,?,?) }");
cs.setString(1, fechaInicial);
cs.setString(2, fechaFinal);
cs.setInt(3, idUsuarioV);
cs.setInt(4, idSucursalV);
cs.setInt(5, idDepartamento);
cs.setInt(6, idPagoV);
rs = cs.executeQuery();
while (rs.next()) {
Venta ven =
new Venta(
rs.getInt("cantidadVenta"), rs.getDouble("subtotal"), rs.getString("fechaVenta"));
Sucursal suc = new Sucursal(rs.getInt("idSucursalVenta"), rs.getString("nombreSucursal"));
Departamento dep =
new Departamento(rs.getInt("idDepartamentoVenta"), rs.getString("nombreDepartamento"));
Usuario usu = new Usuario(rs.getInt("idUsuarioVenta"), rs.getString("nombreUsuario"));
Pago pag = new Pago(rs.getInt("idMetodoPagoVenta"), rs.getString("nombreMetodoPago"));
Articulo art =
new Articulo(
rs.getInt("idArticuloVenta"),
rs.getString("descripcionCorta"),
rs.getString("descripcionLarga"),
rs.getString("codigoArticulo"),
rs.getFloat("precioPublico"),
rs.getFloat("descuento"),
rs.getFloat("impuestos"));
ven.setSucursalVenta(suc);
ven.setDepartamentoVenta(dep);
ven.setUsuarioVenta(usu);
ven.setPagoVenta(pag);
ven.setArticuloVenta(art);
ventas.add(ven);
}
return ventas;
} catch (Exception ex) {
ex.printStackTrace();
return null;
} finally {
DBUtil.closeResultSet(rs);
DBUtil.closeStatement(cs);
pool.freeConnection(connection);
}
}
