//Code copied from video by ProgrammingKnowledge. version: method 21import java.awt.EventQueue;                             //METHOD 21
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 java.text.NumberFormat;
import javax.swing.JFrame; //原來的
import javax.swing.JPanel; //原來的
import javax.swing.border.EmptyBorder; //原來的
import javax.swing.JLabel; //原來的
import javax.swing.JTextField; //原來的
import javax.swing.JTextArea; //原來的
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; //原來的
import java.sql.PreparedStatement;
import java.awt.event.MouseAdapter;
import java.awt.event.MouseEvent; //原來的
public class EmployeeInfo extends JFrame { //原來的

	private JPanel contentPane; //原來的
	private JTable table; //原來的
	private JComboBox comboBox; //原來的
	
/*========================================================================================*/
	/**
	 * 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) {
					JOptionPane.showMessageDialog(null,"Problem in Main." );
				}
			}
		});
	}

	Connection connection = null;
	private JTextField textFieldEID;
	private JTextField textFieldEID2;
	private JTextField textFieldName;
	private JTextField textFieldName2;
	private JTextField textFieldSurname;
	private JTextField textFieldAge;
	private JTextField textFieldComment;
	private JFrame frame;
	private JTextArea txtrExplanation;
	private JTextArea txtrquestion;
	private JTextArea txtrQuestion;
	private JTextArea txtrComment; 
	private JTextField textField2;
	private JTextField textField_1;
	private JTextField txtSurname2;
	private JTextField txtSurname3;
	private JTextField txtAge3;
	private JTextField txtCid;
	private JTextField txtCid2;
	private JTextField txtSid;
	private JButton btnDelete_1;
	private JLabel lblFoto;
	private JButton btnNewButton;
	private JButton btnNewButton_2;

	/* ========================================================================================================= */	
	public void refreshTable()
    {
		try 
		{
			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) 
		{
			JOptionPane.showMessageDialog(null, "Problem in refresh table.");
		}
}
/* ========================================================================================================= */	
	
	public void fillComboBox()
	{
		int i = 0;
		
		//JOptionPane.showMessageDialog(null, "Inside fill combo box.");
		try 
		{
			i++;
			String s = "";
			String query = "select * from EmployeeInfo";
			PreparedStatement pst = connection.prepareStatement(query);
			ResultSet rs = pst.executeQuery();
			//JOptionPane.showMessageDialog(null, "Inside TRY combo box.");
			while(rs.next())
			{
				comboBox.addItem(rs.getString("EID"));   
			}
			
			table.setModel(DbUtils.resultSetToTableModel(rs)); 
			rs.close();
			pst.close(); 
			
		}
		catch (Exception e1) 
		{
			JOptionPane.showMessageDialog(null, "Failed to fill combobox");
		}
		String s = NumberFormat.getIntegerInstance().format(i);
		JOptionPane.showMessageDialog(null,"Number of records read = " + s  );
	}
////////////// END OF FILL COMBO BOX	
	/**
	 * Create the frame.
	 */
/* =============================================================================*/
	
	public EmployeeInfo() {
		connection = sqliteConnection.dbConnector();
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		setBounds(100, 100, 1020, 695);
		contentPane = new JPanel();
		contentPane.setBorder(new EmptyBorder(5, 5, 5, 5));
		setContentPane(contentPane);
		contentPane.setLayout(null);
		fillComboBox();////////////////////////////////////// Desperation move
		JOptionPane.showMessageDialog(null,"Ready to go deeper into EmployeeInfo stuff."); //Nothing happens.
/*--------------------------------------------------------------------*/		
		JButton btnLoadTable = new JButton("Load Database");
		btnLoadTable.addActionListener(new ActionListener() 
		{
			public void actionPerformed(ActionEvent e) 
			{
				try 
				{
					String query = "select * from EmployeeInfo";
					PreparedStatement pst = connection.prepareStatement(query);
					ResultSet rs = pst.executeQuery();
					table.setModel(DbUtils.resultSetToTableModel(rs));
					rs.close();
					pst.close();
					JOptionPane.showMessageDialog(null,"Database loaded to table." );
				}
				catch (Exception e1) 
				{
					JOptionPane.showMessageDialog(null,"Failed to load database." );
				}
			}
		});
		btnLoadTable.setBounds(397, 0, 206, 29);
		contentPane.setLayout(null);
		contentPane.add(btnLoadTable);
		
		JScrollPane scrollPane = new JScrollPane();
		scrollPane.setBounds(186, 4, 45, 25);
		contentPane.add(scrollPane);
		
		table = new JTable(); 
		scrollPane.setViewportView(table);
		
	// added for grid lines, default is white
		// 4 May	table.setGridColor(Color.black);

/* =========================================end JComboBox =====================================*/	
		
		JLabel lblNewLabel = new JLabel("EID");
		lblNewLabel.setBounds(44, 183, 55, 16);  //24, 168, 61, 16
		contentPane.add(lblNewLabel);
		
/////////////////////////////////////////////////////  EID/////////////////////////		
		textFieldEID = textFieldEID2 = new JTextField();
		textFieldEID2.setBackground(Color.YELLOW);
		textFieldEID2.setForeground(Color.MAGENTA);
		textFieldEID2.setText("EID2");
		textFieldEID.setBounds(200, 75, 160, 28); //113, 157, 134, 28
		contentPane.add(textFieldEID);
		textFieldEID.setColumns(10);
	
///////////////////////////////////		NAME  ///////////////////////////////////////////
		textFieldName = textFieldName = new JTextField();
		textFieldName.setBackground(Color.CYAN);
		textFieldName.setText("NAME");
		textFieldName.setBounds(133, 220, 900, 38);
		contentPane.add(textFieldName);
		textFieldName.setColumns(10);
		
		String shuzi = textFieldName.getText().toString();
	//	JOptionPane.showMessageDialog(null,"Name:" + shuzi);		
		
/////////////////////////	/////////////////////////		SURNAME
		
		textFieldSurname = new JTextField();
		textFieldSurname.setBackground(Color.PINK);
		textFieldSurname.setText("Surname");
		textFieldSurname.setBounds(113, 339, 765, 38);   
		contentPane.add(textFieldSurname);
		textFieldSurname.setColumns(10);
		
////	//////////////////////////////////////////////////////////////// AGE
		
		textFieldAge = new JTextField();
		textFieldAge.setBackground(Color.YELLOW);
		textFieldAge.setText("age");
		textFieldAge.setBounds(-22, 445, 900, 38);// 
		contentPane.add(textFieldAge);
		textFieldAge.setColumns(10);
		
///////////////////////////////////////////////////////////////////		

		textFieldComment = new JTextField();
		textFieldComment.setBackground(new Color(102, 153, 255));
		textFieldComment.setText("Comment");
		textFieldComment.setBounds(127, 188, 751, 38);     
		contentPane.add(textFieldComment);
		textFieldComment.setColumns(10);
		
		JTextArea txtrComment = new JTextArea();
		txtrComment.setText("AREA_Comment");
		txtrComment.setBounds(113, 665, 490, 74);
		contentPane.add(txtrComment);
		
		textField2 = new JTextField();
		textField2.setBackground(Color.CYAN);
		textField2.setBounds(114, 289, 571, 38);
		contentPane.add(textField2);
		textField2.setColumns(10);
		
		textField_1 = new JTextField();
		textField_1.setBackground(Color.CYAN);
		textField_1.setBounds(116, 263, 536, 26);
		contentPane.add(textField_1);
		textField_1.setColumns(10);
		
		JLabel lblName = new JLabel(" Name");
		lblName.setBounds(31, 243, 61, 16);
		contentPane.add(lblName);
		
		txtSurname2 = new JTextField();
		txtSurname2.setBackground(Color.PINK);
		txtSurname2.setText("surname2");
		txtSurname2.setBounds(117, 378, 557, 26);
		contentPane.add(txtSurname2);
		txtSurname2.setColumns(10);
		
		txtSurname3 = new JTextField();
		txtSurname3.setBackground(Color.PINK);
		txtSurname3.setText("surname3");
		txtSurname3.setBounds(117, 407, 521, 26);
		contentPane.add(txtSurname3);
		txtSurname3.setColumns(10);
		
		JTextField txtAge2 = new JTextField();
		txtAge2.setBackground(Color.YELLOW);
		txtAge2.setText("age2");
		txtAge2.setBounds(137, 476, 515, 26);
		contentPane.add(txtAge2);
		txtAge2.setColumns(10);
		
		txtAge3 = new JTextField();
		txtAge3.setText("age3");
		txtAge3.setBounds(137, 495, 545, 26);
		contentPane.add(txtAge3);
		txtAge3.setColumns(10);
		
		txtCid = new JTextField();
		txtCid.setBackground(new Color(102, 255, 0));
		txtCid.setText("cid3");
		txtCid.setBounds(113, 609, 591, 44);
		contentPane.add(txtCid);
		txtCid.setColumns(10);
		
		txtCid2 = new JTextField();
		txtCid2.setBackground(new Color(153, 102, 51));
		txtCid2.setText("cid2");
		txtCid2.setBounds(113, 586, 525, 26);
		contentPane.add(txtCid2);
		txtCid2.setColumns(10);
		
		txtSid = new JTextField();
		txtSid.setBackground(new Color(204, 102, 51));
		txtSid.setText("sid");
		txtSid.setBounds(113, 559, 565, 26);
		contentPane.add(txtSid);
		txtSid.setColumns(10);
		
	/////////////////////////////////////////////////////////////btnupdate//////////////////
		
		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();

					pst.close();
					JOptionPane.showMessageDialog(null,"Updated." );
				}
				catch (Exception e1) 
				{
					JOptionPane.showMessageDialog(null, "Failed to update");
				}
				refreshTable();
			}
		});
		btnUpdate.setBounds(44, 75, 117, 29); 
		contentPane.add(btnUpdate);
		
	////////////////////////////////////////////////////	 button save
		
		JButton btnSave_1 = new JButton("Save");
		btnSave_1.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				try 
				{
					String query = "select * from EmployeeInfo";
					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) 
				{
					JOptionPane.showMessageDialog(null, "Failed to save");
				}
				refreshTable();	
			}
		});
		btnSave_1.setBounds(44, 105, 117, 29);
		contentPane.add(btnSave_1);
		
	/////////////////////////////////////////////////// replacement button delete	
		
		btnDelete_1 = new JButton("Delete");
		btnDelete_1.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) 
				{
					JOptionPane.showMessageDialog(null, "Failed to delete");
				}
				refreshTable();
			}
		});
		btnDelete_1.setBounds(44, 135, 117, 29);
		contentPane.add(btnDelete_1);
		
		lblFoto = new JLabel("foto");
		lblFoto.setBounds(858, 564, 402, -561);
		contentPane.add(lblFoto);
		
		btnNewButton = new JButton("New button");// SAVE FOR POSSIBLE FUTURE US
		btnNewButton.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
			}
		});
		btnNewButton.setBounds(209, 115, 117, 29);
		contentPane.add(btnNewButton);
		
		JButton btnNewButton_1 = new JButton("New button");// SAVE FOR POSSIBLE FUTURE USE
		btnNewButton_1.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
			}
		});
		btnNewButton_1.setBounds(320, 115, 117, 29);
		contentPane.add(btnNewButton_1);
		
		
		
		JButton btnNewButton_3 = new JButton("New button");// SAVE FOR POSSIBLE FUTURE USE
		btnNewButton_3.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
			}
		});
		btnNewButton_3.setBounds(578, 115, 117, 29);
		contentPane.add(btnNewButton_3);
		
		JButton btnNewButton_4 = new JButton("New button");   // SAVE FOR POSSIBLE FUTURE USE
		btnNewButton_4.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
			}
		});
		btnNewButton_4.setBounds(704, 115, 117, 29);
		contentPane.add(btnNewButton_4);
		
		btnNewButton_2 = new JButton("New button"); // SAVE FOR POSSIBLE FUTURE USE
		btnNewButton_2.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
			}
		});
		btnNewButton_2.setBounds(449, 115, 117, 29);
		contentPane.add(btnNewButton_2);
		
		JLabel lblProtectThisScrolling = new JLabel("Protect this scrolling box");
		lblProtectThisScrolling.setBounds(170, 41, 190, 16);
		contentPane.add(lblProtectThisScrolling);

////////////////////////////////////////// Make Combox		
		
// S	unday's Combobox	
		JComboBox comboBox = new JComboBox();
		comboBox.addActionListener(new ActionListener() {
			public void actionPerformed(ActionEvent e) {
				JOptionPane.showMessageDialog(null,"In comboBox ");
				try { 
					JOptionPane.showMessageDialog(null,"In try of comboBox ");
					JOptionPane.showMessageDialog(null, "Inside comboBox");
					String query = "select * from EmployeeInfo where EID = ? ";
					PreparedStatement pst = connection.prepareStatement (query);
					pst.setString(1,  (String) comboBox.getSelectedItem()); 
					ResultSet rs = pst.executeQuery();
					JOptionPane.showMessageDialog(null, rs);
					while(rs.next())
					{
						textFieldEID.setText(rs.getString("EID"));
						textFieldName.setText(rs.getString("Name"));
						textFieldSurname.setText(rs.getString("Surname"));
						textFieldAge.setText(rs.getString("Age"));
					}
					String str = "";
					str= textFieldEID.getText();
					JLabel lblSteps = new JLabel(str);
					lblSteps.setBounds(383, 134,61, 16);
					frame.getContentPane().add(lblSteps);
					pst.close();
				} //3
				catch(Exception j) { //4
					JOptionPane.showMessageDialog(null,"Failed in comboBox ");
				}				
		////////		
			}//end of action event
		});//end of action listener
		comboBox.setBounds(6, 12, 155, 51);
		contentPane.add(comboBox);
		refreshTable();

		
	}// end of employee info 
} //*End of :extends frame line 334