package org;

import java.awt.Container;
import java.awt.event.*;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;

public class Graphical extends JFrame {
	static String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	static String connect = "jdbc:derby:DB;create=true";
	private static final long serialVersionUID = 1L;
	DefaultTableModel model1 = new DefaultTableModel();
	DefaultTableModel model2 = new DefaultTableModel();
	DefaultTableModel model3 = new DefaultTableModel();
    private JTable table1=new JTable(model1) ;
    private JTable table2=new JTable(model2) ;
    private JTable table3=new JTable(model3) ;
	private JScrollPane scrollPane1 = new JScrollPane(table1);
	private JScrollPane scrollPane2 = new JScrollPane(table2);
	private JScrollPane scrollPane3 = new JScrollPane(table3);
	private JButton addButton = new JButton("Добавить");
	private JButton deleteButton = new JButton("Удалить");
	private JButton createButton = new JButton("Создать");
	private JButton lookButton = new JButton("Отобразить");

	public Graphical (){
		super("Отдел кадров");
		init();
	}
	public void look(){
		DefaultTableModel m1 = (DefaultTableModel) table1.getModel();
		DefaultTableModel m2 = (DefaultTableModel) table2.getModel();
		DefaultTableModel m3 = (DefaultTableModel) table3.getModel();
		m1.setRowCount(0);
		m1.addRow(new Object[]{"Код работника:", "ФИО:", "Должность:", "Стаж:", "Дата заключ договора:"});
		m3.setRowCount(0);
		m3.addRow(new Object[]{"Код отдела:", "Должность:", "Оклад:","Премия:"});
		m2.setRowCount(0);
		m2.addRow(new Object[]{"ФИО:", "Дата рождения", "Пол", "Образование", "Телефон", "Номер паспорта"});
		System.setProperty("derby.system.home", "C:\\MyDB");
		try {
			Class.forName( driver );
			Connection conn = DriverManager.getConnection(connect);
			Statement st = conn.createStatement();
			ResultSet rec = st.executeQuery("SELECT * FROM EmployeeСard ORDER BY fio");
			while (rec.next()) {
				int kod = rec.getInt("kod");
				String name = rec.getString("fio");
				String position = rec.getString("position");
				int experience = rec.getInt("experience");
				Date date = rec.getDate("date");
				DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
				String d=df.format(date);
				DefaultTableModel model = (DefaultTableModel) table1.getModel();
		        model.addRow(new Object[]{Integer.toString(kod), name, position,experience,d});

			}
			rec.close();
			st.close();
		} catch (Exception e1) {
			System.err.println("Run-time error1: " + e1 );
		}
		try {
			Class.forName( driver );
			Connection conn = DriverManager.getConnection(connect);
			Statement st = conn.createStatement();
			ResultSet rec = st.executeQuery("SELECT * FROM PersonInformation ORDER BY fio");
			while (rec.next()) {
				String fio = rec.getString("fio");
				int sex = rec.getInt("sex");
				String education = rec.getString("education");
				int pasportNumber = rec.getInt("pasportNumber");
				int number=rec.getInt("number");
				Date date = rec.getDate("date");
				DateFormat df = new SimpleDateFormat("MM/dd/yyyy");
				String d=df.format(date);
				DefaultTableModel model2 = (DefaultTableModel) table2.getModel();
		        model2.addRow(new Object[]{fio, d, Integer.toString(sex),education,number,pasportNumber});

			}
			rec.close();
			st.close();
		} catch (Exception e1) {
			System.err.println("Run-time error2: " + e1 );
		}
		try {
			Class.forName( driver );
			Connection conn = DriverManager.getConnection(connect);
			Statement st = conn.createStatement();
			ResultSet rec = st.executeQuery("SELECT * FROM StafInfo ORDER BY kod");
			while (rec.next()) {
				int kod = rec.getInt("kod");
				String position = rec.getString("position");
				int premium = rec.getInt("premium");
				int oklad=rec.getInt("oklad");
				DefaultTableModel model3 = (DefaultTableModel) table3.getModel();
		        model3.addRow(new Object[]{Integer.toString(kod), position,oklad,premium});

			}
			rec.close();
			st.close();
		} catch (Exception e1) {
			System.err.println("Run-time error3: " + e1 );
		}
	}
	public void init() {
		setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
		Container c = getContentPane();
		setBounds (20,80,980,900);
		c.setLayout(null);
		DefaultTableModel model1 = (DefaultTableModel) table1.getModel();
	    model1.addColumn("Код");
        model1.addColumn("ФИО");
        model1.addColumn("Должность");
        model1.addColumn("Стаж");
        model1.addColumn("Дата принятия");
		table1.setBounds(20, 20, 900, 200);
		c.add(table1);

		DefaultTableModel model2 = (DefaultTableModel) table2.getModel();
	    model2.addColumn("Код");
        model2.addColumn("Дата");
        model2.addColumn("Пол");
        model2.addColumn("Образование");
        model2.addColumn("Телефон");
        model2.addColumn("Номер паспорта");
		table2.setBounds(20, 260, 900, 200);
		c.add(table2);


		DefaultTableModel model3 = (DefaultTableModel) table3.getModel();
	    model3.addColumn("Код отдела");
        model3.addColumn("Должность");
        model3.addColumn("Оклад");
        model3.addColumn("Премия");
		table3.setBounds(20, 500, 900, 200);
		c.add(table3);

		addButton.setBounds(40,750,100,40);
		c.add(addButton);
		addButton.addActionListener(new addListener());

		createButton.setBounds(350,750,100,40);
		c.add(createButton);
		createButton.addActionListener(new createListener());

		lookButton.setBounds(600,750,200,40);
		c.add(lookButton);
		lookButton.addActionListener(new lookListener());

		deleteButton.setBounds(190,750,100,40);
		c.add(deleteButton);
		deleteButton.addActionListener(new deleteListener());
	}
	private class addListener implements ActionListener{
		public void actionPerformed(ActionEvent e){
			new DialogAddItem(Graphical.this, "Добавить нового клиента");
			look();
		}
	}
	private class createListener implements ActionListener{
		public void actionPerformed(ActionEvent e){


			String driver = "org.apache.derby.jdbc.EmbeddedDriver";
			String connect = "jdbc:derby:DB;create=true";
			System.setProperty("derby.system.home", "C:\\MyDB" );
			try {
				Class.forName( driver );
				Connection conn = DriverManager.getConnection(connect);
				Statement st = conn.createStatement();
				st.executeUpdate("CREATE TABLE PersonInformation " +
					"(fio VARCHAR(32) PRIMARY KEY, date DATE, sex INT, education VARCHAR(32), number INT, pasportNumber INT )");
				st.executeUpdate("CREATE TABLE StafInfo " +
					"(kod INT, position VARCHAR(32) PRIMARY KEY, oklad INT, premium INT)");

				st.executeUpdate("CREATE TABLE EmployeeСard " +
					"(kod INT PRIMARY KEY, fio VARCHAR (32)  REFERENCES PersonInformation (fio), position VARCHAR(32) REFERENCES StafInfo (position), " +
					"experience INT, date DATE)");
				st.close();

			} catch (Exception e1) {
				System.err.println("Run-time error00: " + e1 );
			}
			String Cards = "INSERT INTO EmployeeСard(kod, fio, position, experience, date) VALUES(?,?,?,?,?)";
			String RersonsInfo = "INSERT INTO PersonInformation(fio, date, sex, education, number, pasportNumber) VALUES(?,?,?,?,?,?)";
			String stf = "INSERT INTO StafInfo(kod, position,  oklad, premium) VALUES(?,?,?,?)";

			StafInfo [] arrSt = {
		            new StafInfo(1, "Логист", 1000, 125 ),
		            new StafInfo(2, "Экономист" ,1500, 200 ),
		            new StafInfo(2,"Финансист", 1200, 300 ),
		            new StafInfo(3, "Программист", 3000, 500 ),
		            new StafInfo(3, "Системный администратор", 2000, 1000 ),
		            new StafInfo(3,"Тестировщик", 1000, 100 ),
		            new StafInfo(3, "Директор", 500, 100 )
			};

			PersonInformation [] arrInfo = {
		            new PersonInformation( "Макаревич Евгения Александровна", new Date( 80, 1, 1 ), 0, "БГУ", 124574, 124585),
		            new PersonInformation( "Коренчук Анна Олеговна", new Date( 77, 4, 6 ), 0, "БНТУ", 785444, 325612),
		            new PersonInformation( "Бузун Елизавета Анатольевна", new Date( 89, 8, 21 ), 0, "БГУИР", 451256, 458965),
		            new PersonInformation("Tochilo Anna", new Date( 93, 10, 13 ), 0, "БрГУ", 785695, 963125),
		            new PersonInformation( "Тулейко Максим Юрьевич", new Date( 90, 5, 9 ), 1, "Академия", 632985, 125633),
		        };

			EmployeeСard [] arrCards = {
		        	new EmployeeСard( 1,"Макаревич Евгения Александровна", "Экономист", 1,new Date( 16, 10, 1 )),
		        	new EmployeeСard( 2,"Коренчук Анна Олеговна", "Логист", 5,new Date( 9, 8, 14 )),
		        	new EmployeeСard( 3,"Бузун Елизавета Анатольевна", "Программист", 2, new Date( 98, 11, 1 )),
		        	new EmployeeСard( 4,"Tochilo Anna", "Директор", 2, new Date( 14, 10, 1 )),
		        	new EmployeeСard( 5,"Тулейко Максим Юрьевич", "Финансист", 2, new Date( 15, 9, 13 )),
		        };
		        System.setProperty("derby.system.Вhome", "C:\\MyDB" );

				try {
					Class.forName( driver );
					Connection conn = DriverManager.getConnection(connect);
		            PreparedStatement st = conn.prepareStatement(stf);

		            for ( StafInfo si : arrSt ) {

		                try {
		                    st.setInt( 1, si.kod );
		                    st.setString( 2, si.position );
		                    st.setInt(3, si.oklad);
		                    st.setInt(4, si.premium);
		                    st.executeUpdate();
		                }
		                catch (SQLException e1) {
		                	System.err.println("Insert error: " + e1 );
		                }
		            }

		            st = conn.prepareStatement(RersonsInfo);
		            for ( PersonInformation pi : arrInfo ) {
		                try {
		                    st.setString( 1, pi.fio );
		                    st.setDate(2, pi.date);
		                    st.setInt( 3, pi.sex );
		                    st.setString( 4, pi.education );
		                    st.setInt( 5, pi.number );
		                    st.setInt( 6, pi.pasportNamber );
		                    st.executeUpdate();
		                }
		                catch (SQLException e1) {
		                	System.err.println("Insert error: " + e1 );
		                }
		            }
					st.close();

		            st = conn.prepareStatement(Cards);
		            for ( EmployeeСard hc : arrCards ) {
		                try {
		                    st.setInt( 1, hc.kod );
		                    st.setString( 2, hc.fio );
		                    st.setString( 3, hc.position );
		                    st.setInt( 4, hc.experience );
		                    st.setDate( 5, hc.date );
		                    st.executeUpdate();
		                }
		                catch (SQLException e1) {
		                	System.err.println("Insert error: " + e1);
		                }
		            }
					st.close();
					conn.close();

				} catch (Exception e1) {
					System.err.println("Run-time error: " + e1 );
				}
		}
	}
	private class deleteListener implements ActionListener{
		public void actionPerformed(ActionEvent e){
			int t1 = table1.getSelectedRow();
			int t2 = table2.getSelectedRow();
			int t3 = table3.getSelectedRow();

			String connect = "jdbc:derby:DB;create=true";
			System.setProperty("derby.system.home", "C:\\MyDB" );
			try{
				Connection conn = DriverManager.getConnection(connect);

				if(t1 != -1) {
					String fio = table1.getValueAt(t1, 1).toString();
					String booksIns = "DELETE FROM EmployeeСard WHERE fio = '"+fio+"'" ;
					try {
						PreparedStatement st = conn.prepareStatement(booksIns);
						st.executeUpdate();
						st.close();

					} catch (SQLException e1) {
						System.err.println("Insert error: " + e1 );
					}
				}
				if(t2 != -1) {
					String fio = table2.getValueAt(t2, 0).toString();
					String booksIns = "DELETE FROM EmployeeСard WHERE fio = '"+fio+"'" ;
					String booksI = "DELETE FROM PersonInformation WHERE fio = '"+fio+"'" ;
					try {
						PreparedStatement st = conn.prepareStatement(booksIns);
						st.executeUpdate();
						st = conn.prepareStatement(booksI);
						st.executeUpdate();
						st.close();

					} catch (SQLException e1) {
						System.err.println("Insert error: " + e1 );
					}
				}
				if(t3 != -1) {
					String position = table3.getValueAt(t3, 1).toString();
					String booksIns = "DELETE FROM EmployeeСard WHERE position = '"+position+"'" ;
					String booksI = "DELETE FROM StafInfo WHERE position = '"+position+"'" ;
					try {
						PreparedStatement st = conn.prepareStatement(booksIns);
						st.executeUpdate();
						st = conn.prepareStatement(booksI);
						st.executeUpdate();
						st.close();

					} catch (SQLException e1) {
						System.err.println("Insert error: " + e1 );
					}
				}
			}
			catch (Exception e3) {
				System.err.println("Insert error: " + e3 );
			}
			look();
			//new DialogDelete(Graphical.this, "Удаление данных в базе");
		}
	}
	private class lookListener implements ActionListener{
		public void actionPerformed(ActionEvent e){
			look();
		}
	}

    public static void main(String[] args) {
    	Graphical app = new Graphical();
		app.setVisible(true);
		app.setResizable(false);
    }
}