package org.indrasoftwarelabs.containers;
import java.awt.Color;
import java.awt.Container;
import java.awt.Image;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent;
import java.io.File;
import java.io.IOException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import javax.imageio.ImageIO;
import javax.swing.ButtonGroup;
import javax.swing.ImageIcon;
import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import javax.swing.JRadioButton;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.JTextField;
import javax.swing.JToolBar;
import javax.swing.ListSelectionModel;
import javax.swing.ScrollPaneConstants;
import javax.swing.table.DefaultTableModel;
import javax.swing.table.JTableHeader;
import javax.swing.table.TableColumn;
import jdbc.OracleTypes;
import org.indrasoftwarelabs.db.Query;
import org.indrasoftwarelabs.db.Sentence;
import org.indrasoftwarelabs.log.LogForDay;
import org.indrasoftwarelabs.main.MainForm;
import org.indrasoftwarelabs.main.QCS;
@SuppressWarnings("serial")
public class SearchContainer extends Container {
Container miContenedor = new Container();
ResultSet rs;
String sentenceName;
String sentenceDescription;
String sentenceSQL;
String sentenceSQLFormatted;
String sentenceArgsMap;
String sentenceMaxRegisters;
int valueOrder;
String queryNAME;
int queryID;
final DefaultTableModel model = new DefaultTableModel();
//Connection con;
List<String[]> listaArgumentos = new ArrayList<String[]>();
String[] filaParametros = null;
final List<String> vectorParamsIDs = new ArrayList<String>();
String columnNames[] = {"#", "MODULE", "METHOD", "SENTENCE"};
Object[] rowData = null;
static final String SEPARADOR = "?*?";
final String DATE_FORMAT_DATE = "dd/MM/yyyy";
final String DATE_FORMAT_TIMESTAMP = "dd/MM/yyyy HH:mm:ss";
DateFormat dateFormat;
Date date;
/**
* Get Container
* @return
*/
public Container getMyContainer() {
return miContenedor;
}
public SearchContainer(final JFrame mainFrame, final Properties prop, final Container mainContenedor){
//con = conn;
ImageIcon iconSearch = null;
ImageIcon iconBack = null;
ImageIcon iconTitulo = null;
ImageIcon iconEdit = null;
try{
Image img = ImageIO.read(new File("res/icon.png"));
mainFrame.setIconImage(img);
img = ImageIO.read(new File("res/icons/Search-icon.png"));
iconSearch = new ImageIcon(img);
img = ImageIO.read(new File("res/icons/Home-icon.png"));
iconBack = new ImageIcon(img);
img = ImageIO.read(new File("res/QCS_titulo.png"));
iconTitulo = new ImageIcon(img);
img = ImageIO.read(new File("res/icons/bbdd.png"));
iconEdit = new ImageIcon(img);
}catch(IOException err){
LogForDay.writeToLog("No se encuentra el icono. " + err.getLocalizedMessage() , QCS.logPath, LogForDay.ERROR);
}
// General values
JLabel cadenaBuscar, resultadoBuscar, seleccionarBusqueda;
final JTextField campoBuscar;
JButton botonVolver, botonBuscar, botonEdit;
final JRadioButton optionSQL = new JRadioButton("SQL code", true);
final JRadioButton optionQueryName = new JRadioButton("Sentence name", true);
final JTable table = new JTable(model){
// Editable solo la columna values
public boolean isCellEditable(int row,int column){
return false;
}
};
// Ancho de las columnas
TableColumn col;
// FILA 1
/*******************************/
cadenaBuscar = new JLabel("FILTER: ");
cadenaBuscar.setBounds(20, 85, 140, 30); // x, y, ancho, alto
miContenedor.add(cadenaBuscar);
campoBuscar = new JTextField();
cadenaBuscar.setLabelFor(campoBuscar);
campoBuscar.setSize(300, 30);
campoBuscar.setBounds(140, 90, 300, 30); // x, y, ancho, alto
campoBuscar.setEditable(true);
campoBuscar.setVisible(true);
campoBuscar.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent e) {
Connection con = null;
try{
String select = null;
String[] listaParams = null;
if(optionSQL.isSelected()){
// MODO A
// Buscando con una query...
// System.out.println( "Se buscara dentro de la query");
// select = Sentence.GET_QUERY_SEARCH_IN_ALL_QUERIES_BY_STRING;
// listaParams = new String[]{"GCQS_QUERY","PREPARED_QUERY_LONG",campoBuscar.getText().toUpperCase()};
// MODO B
// Ejecutando un PL/SQL con parametros
String param = campoBuscar.getText().toUpperCase();
String query = "begin ? := F_Search_In_Queries(?); end;";
LogForDay.writeToLog("Estableciendo conexion a BBDD ", QCS.logPath, LogForDay.DEBUG);
con = MainForm.getConexion(prop);
CallableStatement stmt = con.prepareCall(query);
//CallableStatement stmt = con.prepareCall(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);
// set the in param
stmt.setString(2, param);
// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
rs.next();
if( rs.getInt(1) == 0 ){
// No hay resultados
JFrame popup;
String msg = "No se ha encontrado la cadena de busqueda en ninguna sentencia";
popup = new JFrame();
popup.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
popup.setSize(100, 100);
//JOptionPane.showMessageDialog(popup, msg, null, JOptionPane.WARNING_MESSAGE);
JOptionPane.showConfirmDialog(mainFrame,msg,"About us",JOptionPane.CLOSED_OPTION, JOptionPane.WARNING_MESSAGE);
return;
}
while(model.getRowCount() > 0){
model.removeRow(0);
}
rowData = new Object[4];
// Primer fila de forma especial ;-)
rowData[0] = rs.getInt(1); // QUERY ID
rowData[1] = rs.getString(2); // MODULE NAME
rowData[2] = rs.getString(3); // METHOD NAME
rowData[3] = rs.getString(4); // SENTENCE NAME
model.addRow(rowData);
while(rs.next()){
rowData[0] = rs.getInt(1); // QUERY ID
rowData[1] = rs.getString(2); // MODULE NAME
rowData[2] = rs.getString(3); // METHOD NAME
rowData[3] = rs.getString(4); // SENTENCE NAME
model.addRow(rowData);
}
}else if(optionQueryName.isSelected()){
System.out.println( "Se buscara por el nombre de la query");
select = Sentence.GET_QUERY_SEARCH_IN_ALL_QUERY_NAMES;
listaParams = new String[]{campoBuscar.getText().toUpperCase()};
for(int x=0; x<listaParams.length ; x++ ){
select = select.replaceFirst("\\?", listaParams[x]);
}
rs = Query.executeSelect(select, prop);
if ( rs.next() ){
while(model.getRowCount() > 0){
model.removeRow(0);
}
rs.beforeFirst();
rowData = new Object[4];
while(rs.next()){
rowData[0] = rs.getInt(1); // QUERY ID
rowData[1] = rs.getString(2); // MODULE NAME
rowData[2] = rs.getString(3); // METHOD NAME
rowData[3] = rs.getString(4); // SENTENCE NAME
model.addRow(rowData);
}
}else{
// No hay registros...
JFrame popup;
String msg = "No se ha encontrado la cadena de busqueda en ninguna sentencia";
popup = new JFrame();
popup.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
popup.setSize(100, 100);
//JOptionPane.showMessageDialog(popup, msg, null, JOptionPane.WARNING_MESSAGE);
JOptionPane.showConfirmDialog(mainFrame,msg,"About us",JOptionPane.CLOSED_OPTION, JOptionPane.WARNING_MESSAGE);
return;
}
}else{
return;
}
} catch (Exception err) {
LogForDay.writeToLog(" " + err.getLocalizedMessage() , QCS.logPath, LogForDay.ERROR);
}
try {
MainForm.closeConexion(con);
LogForDay.writeToLog("Conexxion BBDD cerrada", QCS.logPath, LogForDay.DEBUG);
} catch (SQLException err) {
LogForDay.writeToLog(" " + err.getLocalizedMessage() , QCS.logPath, LogForDay.ERROR);
} catch (ClassNotFoundException err) {
LogForDay.writeToLog(" " + err.getLocalizedMessage() , QCS.logPath, LogForDay.ERROR);
}
}
});
miContenedor.add(campoBuscar);
// BOTON
botonBuscar = new JButton("SEARCH");
botonBuscar.setBounds(450, 90, 100, 30); // x, y, ancho, alto
botonBuscar.setIcon(iconSearch);
botonBuscar.setVerticalTextPosition(JButton.BOTTOM);
botonBuscar.setHorizontalTextPosition(JButton.CENTER);
botonBuscar.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent event) {
Connection con = null;
try {
// TableColumn colo;
//
// colo = table.getColumnModel().getColumn(0);
// System.out.println("Columna 1: " + colo.getWidth());
// colo = table.getColumnModel().getColumn(1);
// System.out.println("Columna 2: " + colo.getWidth());
// colo = table.getColumnModel().getColumn(2);
// System.out.println("Columna 3: " + colo.getWidth());
// colo = table.getColumnModel().getColumn(3);
// System.out.println("Columna 4: " + colo.getWidth());
String select = null;
String[] listaParams = null;
if(optionSQL.isSelected()){
// MODO A
// System.out.println( "Se buscara dentro de la query");
// select = Sentence.GET_QUERY_SEARCH_IN_ALL_QUERIES_BY_STRING;
// listaParams = new String[]{"GCQS_QUERY","PREPARED_QUERY_LONG",campoBuscar.getText().toUpperCase()};
//
// MODO B
// Ejecutando un PL/SQL con parametros
String param = campoBuscar.getText().toUpperCase();
String query = "begin ? := F_Search_In_Queries(?); end;";
LogForDay.writeToLog("Estableciendo conexion a BBDD ", QCS.logPath, LogForDay.DEBUG);
con = MainForm.getConexion(prop);
CallableStatement stmt = con.prepareCall(query);
//CallableStatement stmt = con.prepareCall(query, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);
// set the in param
stmt.setString(2, param);
// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);
// Se borra la tabla de resultados
while(model.getRowCount() > 0){
model.removeRow(0);
}
rs.next();
if( rs.getInt(1) == 0 ){
// No hay resultados
JFrame popup;
String msg = "No se ha encontrado la cadena de busqueda en ninguna sentencia";
popup = new JFrame();
popup.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
popup.setSize(100, 100);
//JOptionPane.showMessageDialog(popup, msg, null, JOptionPane.WARNING_MESSAGE);
JOptionPane.showConfirmDialog(mainFrame,msg,"About us",JOptionPane.CLOSED_OPTION, JOptionPane.WARNING_MESSAGE);
return;
}
rowData = new Object[4];
// Primer fila de forma especial ;-)
rowData[0] = rs.getInt(1); // QUERY ID
rowData[1] = rs.getString(2); // MODULE NAME
rowData[2] = rs.getString(3); // METHOD NAME
rowData[3] = rs.getString(4); // SENTENCE NAME
model.addRow(rowData);
while(rs.next()){
rowData[0] = rs.getInt(1); // QUERY ID
rowData[1] = rs.getString(2); // MODULE NAME
rowData[2] = rs.getString(3); // METHOD NAME
rowData[3] = rs.getString(4); // SENTENCE NAME
model.addRow(rowData);
}
}else if(optionQueryName.isSelected()){
System.out.println( "Se buscara por el nombre de la query");
select = Sentence.GET_QUERY_SEARCH_IN_ALL_QUERY_NAMES;
listaParams = new String[]{campoBuscar.getText().toUpperCase()};
for(int x=0; x<listaParams.length ; x++ ){
select = select.replaceFirst("\\?", listaParams[x]);
}
rs = Query.executeSelect(select, prop);
if ( rs.next() ){
while(model.getRowCount() > 0){
model.removeRow(0);
}
rs.beforeFirst();
rowData = new Object[4];
while(rs.next()){
rowData[0] = rs.getInt(1); // QUERY ID
rowData[1] = rs.getString(2); // MODULE NAME
rowData[2] = rs.getString(3); // METHOD NAME
rowData[3] = rs.getString(4); // SENTENCE NAME
model.addRow(rowData);
}
}else{
// No hay registros...
JFrame popup;
String msg = "No se ha encontrado la cadena de busqueda en ninguna sentencia";
popup = new JFrame();
popup.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
popup.setSize(100, 100);
//JOptionPane.showMessageDialog(popup, msg, null, JOptionPane.WARNING_MESSAGE);
JOptionPane.showConfirmDialog(mainFrame,msg,"Atencion",JOptionPane.CLOSED_OPTION, JOptionPane.WARNING_MESSAGE);
return;
}
}else{
return;
}
} catch (Exception err) {
LogForDay.writeToLog(" " + err.getLocalizedMessage() , QCS.logPath, LogForDay.ERROR);
}
try {
MainForm.closeConexion(con);
LogForDay.writeToLog("Conexxion BBDD cerrada", QCS.logPath, LogForDay.DEBUG);
} catch (SQLException err) {
LogForDay.writeToLog(" " + err.getLocalizedMessage() , QCS.logPath, LogForDay.ERROR);
} catch (ClassNotFoundException err) {
LogForDay.writeToLog(" " + err.getLocalizedMessage() , QCS.logPath, LogForDay.ERROR);
}
}
});
miContenedor.add(botonBuscar);
botonVolver = new JButton("BACK");
botonVolver.setBounds(450, 130, 100, 30); // x, y, ancho, alto
botonVolver.setIcon(iconBack);
botonVolver.setVerticalTextPosition(JButton.BOTTOM);
botonVolver.setHorizontalTextPosition(JButton.CENTER);
botonVolver.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent event) {
mainFrame.setContentPane(mainContenedor);
mainFrame.setVisible(true);
}
});
//miContenedor.add(botonVolver);
/**
* Radio buttons. Buscar por:
*
* - sintaxis SQL de la sentencia
* - nombre de la sentencia
* - nombre del metodo
* - nombre del modulo
*
*/
seleccionarBusqueda = new JLabel("BY:");
seleccionarBusqueda.setBounds(20, 130, 100, 20); // x, y, ancho, alto
miContenedor.add(seleccionarBusqueda);
final ButtonGroup group = new ButtonGroup();
optionSQL.setBounds(140, 130, 140, 20); // x, y, ancho, alto
optionSQL.setSelected(true);
group.add(optionSQL);
miContenedor.add(optionSQL);
optionQueryName.setBounds(140, 150, 140, 20); // x, y, ancho, alto
optionQueryName.setSelected(false);
group.add(optionQueryName);
miContenedor.add(optionQueryName);
resultadoBuscar = new JLabel("RESULTS");
resultadoBuscar.setBounds(20, 200, 200, 30); // x, y, ancho, alto
miContenedor.add(resultadoBuscar);
for(int x=0; x<columnNames.length;x++){
model.addColumn(columnNames[x]);
}
// Se recorren las columnas para ponerles un ancho fijo
for(int z=0; z<table.getColumnCount(); z++){
col = table.getColumnModel().getColumn(z);
switch(z){
case 0:
col.setPreferredWidth(35);
break;
case 1:
col.setPreferredWidth(170);
break;
case 2:
col.setPreferredWidth(171);
break;
case 3:
col.setPreferredWidth(171);
}
}
// Doble click navegar a la edicion
table.addMouseListener( new MouseAdapter() {
int row;
public void mouseClicked ( MouseEvent e ) {
if (e.getClickCount() == 2){
row = table.getSelectedRow();
String queryName = String.valueOf(table.getValueAt(row, 3));
EditContainer mainEdit = new EditContainer(queryName, mainFrame, prop, miContenedor);
mainFrame.setContentPane(mainEdit.getMyContainer());
mainFrame.setVisible(true);
}
}
});
// Single selection
table.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
JTableHeader header = table.getTableHeader();
header.setBackground(Color.GREEN);
JScrollPane sp = new JScrollPane(table);
sp.setBounds(20, 240, 650, 350); // x, y, ancho, alto
sp.setVerticalScrollBarPolicy(ScrollPaneConstants.VERTICAL_SCROLLBAR_AS_NEEDED);
miContenedor.add(sp);
JLabel label = new JLabel();
label.setBounds(20, 640, 620, 140); // x, y, ancho, alto
label.setIcon(iconTitulo);
miContenedor.add(label);
botonEdit = new JButton("EDIT QUERY");
botonEdit.setIcon(iconEdit);
botonEdit.setVerticalTextPosition(JButton.BOTTOM);
botonEdit.setHorizontalTextPosition(JButton.CENTER);
botonEdit.addActionListener(new ActionListener() {
public void actionPerformed(ActionEvent event) {
int fila = table.getSelectedRow();
if(fila > -1){
String queryName = String.valueOf(table.getValueAt(fila, 3));
EditContainer mainEdit = new EditContainer(queryName, mainFrame, prop, miContenedor);
mainFrame.setContentPane(mainEdit.getMyContainer());
mainFrame.setVisible(true);
}else{
String msg = "Selecciona una sentencia para editarla";
JOptionPane.showConfirmDialog(miContenedor, msg,"Atencion",JOptionPane.CLOSED_OPTION, JOptionPane.WARNING_MESSAGE);
}
}
});
// TOOLBAR
JToolBar toolbar = new JToolBar();
toolbar.setSize(700, 75);
toolbar.setFloatable(false);
toolbar.add(botonBuscar);
toolbar.addSeparator(); //SEPARADOR
toolbar.add(botonEdit);
toolbar.addSeparator(); //SEPARADOR
toolbar.add(botonVolver);
miContenedor.add(toolbar);
}
}