package org.comany.tutorial;

import java.awt.BorderLayout;
import java.awt.Color;
import java.awt.Component;
import java.awt.Container;
import java.awt.EventQueue;
import java.awt.FocusTraversalPolicy;

import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.border.EmptyBorder;

import net.proteanit.sql.DbUtils;

import javax.swing.JLabel;
import javax.swing.JOptionPane;

import java.awt.Font;
import javax.swing.JButton;
import javax.swing.JTable;
import javax.swing.JScrollPane;
import java.awt.event.ActionListener;
import java.sql.*;
import java.util.Vector;
//import java.sql.PreparedStatement;
import java.awt.event.ActionEvent;
import javax.swing.JTextField;
import javax.swing.JComboBox;

public class Employeeinfo extends JFrame {

	private JPanel contentPane;
	private JTable table;
	private JComboBox comboBoxName;

	/**
	 * Launch the application.
	 */
	public static void main(String[] args) {
		EventQueue.invokeLater(new Runnable() {
			public void run() {
				try {
					Employeeinfo frame = new Employeeinfo();
					frame.setVisible(true);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		});
	}

	Connection connection = null;
	private JTextField textFieldEID;
	private JTextField textFieldName;
	private JTextField textFieldSurname;
	private JTextField textFieldAge;
//	private MyOwnFocusTraversalPolicy newPolicy;
	
	public void refreshTable()
	{
		try 
		{
			String query = "select * from EmployeeInfo";
//			String query = "select EID, Name, Surname, Age from EmployeeInfo";
			PreparedStatement pst = connection.prepareStatement(query);
			ResultSet rs = pst.executeQuery();
			table.setModel(DbUtils.resultSetToTableModel(rs));
			rs.close();
			pst.close();
			
		}
		catch (Exception e1) 
		{
			e1.printStackTrace();
		}
	}
	
	public void fillComboBox()
	{
		try 
		{
			String query = "select * from EmployeeInfo";
//			String query = "select EID, Name, Surname, Age from EmployeeInfo";
			PreparedStatement pst = connection.prepareStatement(query);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next())
			{
				comboBoxName.addItem(rs.getString("Name"));
				
			}
			
			table.setModel(DbUtils.resultSetToTableModel(rs));
			rs.close();
			pst.close();
			
		}
		catch (Exception e1) 
		{
			e1.printStackTrace();
		}
	}
	
	/**
	 * Create the frame.
	 */
	public Employeeinfo() 
	{
		// connect to data base
		connection = sqliteConnection.dbConnector();
		
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 655, 418);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		
		JButton btnLoadTable = new JButton("Load Employee Data");
		btnLoadTable.addActionListener(new ActionListener() 
		{
			public void actionPerformed(ActionEvent e) 
			{
				try 
				{
					String query = "select * from EmployeeInfo";
//					String query = "select EID, Name, Surname, Age from EmployeeInfo";
					PreparedStatement pst = connection.prepareStatement(query);
					ResultSet rs = pst.executeQuery();
					table.setModel(DbUtils.resultSetToTableModel(rs));
					rs.close();
					pst.close();
					
				}
				catch (Exception e1) 
				{
					e1.printStackTrace();
				}
			}
		});
		btnLoadTable.setBounds(392, 48, 206, 29);
		contentPane.add(btnLoadTable);
		
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(304, 100, 345, 275);
		contentPane.add(scrollPane);
		
		table = new JTable();
		scrollPane.setViewportView(table);
		
		// added for grid lines, default is white
		table.setGridColor(Color.black);
		
		JLabel lblNewLabel = new JLabel("EID");
		lblNewLabel.setBounds(24, 168, 61, 16);
		contentPane.add(lblNewLabel);
		
		JLabel lblName = new JLabel("Name");
		lblName.setBounds(24, 196, 61, 16);
		contentPane.add(lblName);
		
		JLabel lblSurname = new JLabel("Surname");
		lblSurname.setBounds(24, 224, 61, 16);
		contentPane.add(lblSurname);
		
		JLabel lblAge = new JLabel("Age");
		lblAge.setBounds(24, 252, 61, 16);
		contentPane.add(lblAge);
		
		textFieldEID = new JTextField();
		textFieldEID.setBounds(113, 157, 134, 28);
		contentPane.add(textFieldEID);
		textFieldEID.setColumns(10);
		
		textFieldName = new JTextField();
		textFieldName.setBounds(113, 190, 134, 28);
		contentPane.add(textFieldName);
		textFieldName.setColumns(10);
		
		textFieldSurname = new JTextField();
		textFieldSurname.setBounds(113, 218, 134, 28);
		contentPane.add(textFieldSurname);
		textFieldSurname.setColumns(10);
		
		textFieldAge = new JTextField();
		textFieldAge.setBounds(113, 246, 134, 28);
		contentPane.add(textFieldAge);
		textFieldAge.setColumns(10);
		
//		Vector<Component> order = new Vector<Component>(5);
//		order.add(textFieldEID);
//		order.add(textFieldName);
//		order.add(textFieldSurname);
//		order.add(textFieldAge);
//		order.add(table);
//		newPolicy = new MyOwnFocusTraversalPolicy(order);
		
//		MyOwnFocusTraversalPolicy newPolicy = new MyOwnFocusTraversalPolicy();
//	    contentPane.setFocusTraversalPolicy(newPolicy);
		
		JButton btnSave = new JButton("Save");
		btnSave.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) 
			{
				try 
				{
//					String query = "select * from EmployeeInfo";
					String query = "insert into EmployeeInfo (EID, Name, Surname, Age) values(?, ?, ?, ?)";
					PreparedStatement pst = connection.prepareStatement(query);
					pst.setString(1, textFieldEID.getText());
					pst.setString(2, textFieldName.getText());
					pst.setString(3, textFieldSurname.getText());
					pst.setString(4, textFieldAge.getText());
					
					pst.execute();
					JOptionPane.showMessageDialog(null, "Data saved");
					
					pst.close();
				}
				catch (Exception e1) 
				{
					e1.printStackTrace();
				}
				refreshTable();
			}
		});
		btnSave.setBounds(64, 292, 117, 29);
		contentPane.add(btnSave);
		
		JButton btnUpdate = new JButton("Update");
		btnUpdate.addActionListener(new ActionListener() 
		{
			public void actionPerformed(ActionEvent e) 
			{
				try 
				{
					String query = "update EmployeeInfo set EID='"+textFieldEID.getText()+"' ,name='"+textFieldName.getText()+"',Surname='"+textFieldSurname.getText()+"' ,Age='"+textFieldAge.getText()+"' where EID='"+textFieldEID.getText()+"'";
							
					PreparedStatement pst = connection.prepareStatement(query);
					
					pst.execute();
					JOptionPane.showMessageDialog(null, "Data updated");
					
					pst.close();
				}
				catch (Exception e1) 
				{
					e1.printStackTrace();
				}
				refreshTable();
			}
		});
		btnUpdate.setBounds(64, 325, 117, 29);
		contentPane.add(btnUpdate);
		
		JButton btnDelete = new JButton("Delete");
		btnDelete.addActionListener(new ActionListener() 
		{
			public void actionPerformed(ActionEvent e) 
			{
				try 
				{
					String query = "delete from EmployeeInfo where EID='"+textFieldEID.getText()+"' ";
							
					PreparedStatement pst = connection.prepareStatement(query);
					
					pst.execute();
					JOptionPane.showMessageDialog(null, "Data deleted");
					
					pst.close();
				}
				catch (Exception e1) 
				{
					e1.printStackTrace();
				}
				refreshTable();
			}
		});
		btnDelete.setBounds(64, 361, 117, 29);
		contentPane.add(btnDelete);
		
		comboBoxName = new JComboBox();
		comboBoxName.setBounds(31, 35, 216, 55);
		contentPane.add(comboBoxName);
		
//		refreshTable();       //  *** This causes error in WB design tab ***
//		fillComboBox();
		
	}
}

	public void fillComboBox()
	{
		try 
		{
			String query = "select * from EmployeeInfo";
			PreparedStatement pst = connection.prepareStatement(query);
			ResultSet rs = pst.executeQuery();
			
			while(rs.next())
			{
				comboBoxName.addItem(rs.getString("EID"));   // combobox shows ID numbe.r Using EID works.
				//JOptionPane.showMessageDialog(null, comboBoxName); /////21 April
			}
			
			table.setModel(DbUtils.resultSetToTableModel(rs)); //restored at 16:36
			rs.close(); //restored at 16:36
			pst.close(); //restored at 16:36
			
		}
		catch (Exception e1) 
		{
			e1.printStackTrace();
		}
[