package katse.client;

import java.awt.Dimension;
import java.awt.Rectangle;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;

import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JMenu;
import javax.swing.JMenuBar;
import javax.swing.JMenuItem;
import java.sql.*;

import javax.swing.JScrollPane;

import javax.swing.JTable;

import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;

import oracle.jdbc.OracleDriver;

public class Frame1 extends JFrame {
    private JMenuBar menuBar = new JMenuBar();
    private JMenu menuFile = new JMenu();
    private JMenuItem menuFileExit = new JMenuItem();
    private JScrollPane jScrollPane1 = new JScrollPane();
    DefaultTableModel model=new DefaultTableModel();
    private JTable jTable1 = new JTable(model);
    private JComboBox jComboBox1 = new JComboBox();
    private JButton jButton1 = new JButton();
    Statement st;
    private JTextField jTextField1 = new JTextField();
    private JButton jButton2 = new JButton();
    Connection conn;

    public Frame1() {
        try {
            jbInit();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    public static Connection getConnection() throws SQLException {
        String username = "hr";
        String password = "hr";
        String thinConn = "jdbc:oracle:thin:@pitsa.pld.ttu.ee:1521:ORCL";
        DriverManager.registerDriver(new OracleDriver());
        Connection conn = DriverManager.getConnection(thinConn,username,password);
        conn.setAutoCommit(false);
        return conn;
    }
    
    
    public static ResultSet annaTulemus(Connection conn, String lause) throws SQLException {
    
       Statement st=conn.createStatement();
        ResultSet tulemus=st.executeQuery(lause);
    return tulemus;
    }

    private void jbInit() throws Exception {
        this.setJMenuBar( menuBar );
        this.getContentPane().setLayout( null );
        this.setSize(new Dimension(715, 426));
        this.setTitle( "welcome" );
        menuFile.setText( "File" );
        menuFileExit.setText( "Exit" );
        menuFileExit.addActionListener( new ActionListener() { public void actionPerformed( ActionEvent ae ) { fileExit_ActionPerformed( ae ); } } );
        jScrollPane1.setBounds(new Rectangle(15, 25, 305, 190));
        jComboBox1.setBounds(new Rectangle(370, 35, 200, 25));
        jButton1.setText("Filtreeri");
        jButton1.setBounds(new Rectangle(590, 25, 90, 25));
        jButton1.addActionListener(new ActionListener() {
                    public void actionPerformed(ActionEvent e) {
                        try {
                            jButton1_actionPerformed(e);
                        } catch (SQLException f) {
                            // TODO
                        }
                    }
                });
        jTextField1.setBounds(new Rectangle(375, 75, 180, 30));
        jButton2.setText("Otsi");
        jButton2.setBounds(new Rectangle(580, 75, 75, 25));
        jButton2.addActionListener(new ActionListener() {
                    public void actionPerformed(ActionEvent e) {
                        try {
                            jButton2_actionPerformed(e);
                        } catch (SQLException f) {
                            // TODO
                        }
                    }
                });
        menuFile.add( menuFileExit );
        menuBar.add( menuFile );

        jScrollPane1.getViewport().add(jTable1, null);
        this.getContentPane().add(jButton2, null);
        this.getContentPane().add(jTextField1, null);
        this.getContentPane().add(jButton1, null);
        this.getContentPane().add(jComboBox1, null);
        this.getContentPane().add(jScrollPane1, null);
       
        conn=getConnection();
        String lause="select * from hr.jobs";
        ResultSet tulemus=annaTulemus(conn, lause);
        model.addColumn("Esimene");
        model.addColumn("Teine");
        model.addColumn("Palk");
        
        while(tulemus.next()){
            model.addRow(new Object[]{tulemus.getString(1),tulemus.getString(2),tulemus.getString(3)});
           
        }
          lause="select distinct min_salary from hr.jobs";
         tulemus=annaTulemus(conn, lause);
        jComboBox1.addItem("Näita kõik");
        while(tulemus.next()){
           
            jComboBox1.addItem(tulemus.getString(1));
        }
    }

    void fileExit_ActionPerformed(ActionEvent e) {
        System.exit(0);
    }

    private void jButton1_actionPerformed(ActionEvent e) throws SQLException {
    model.setRowCount(0);
    String a=jComboBox1.getSelectedItem().toString();
    //String lause="select * from hr.jobs where job_title='"+a+"'";
     String lause;
    if(a.equals("Näita kõik")){
        lause="select * from hr.jobs";   
    }
    else{
    lause="select * from hr.jobs where min_salary="+a;
    }
    System.out.println(lause);
        ResultSet tulemus=annaTulemus(conn, lause);
        while(tulemus.next()){
            
            System.out.println(tulemus.getString(2));
            model.addRow(new Object[]{tulemus.getString(1),tulemus.getString(2),tulemus.getString(3)});
        }
    }

    private void jButton2_actionPerformed(ActionEvent e) throws SQLException {
        model.setRowCount(0);
    String b=jTextField1.getText();
    String lause="select * from hr.jobs where job_title LIKE '"+b+"%'";
        ResultSet tulemus=annaTulemus(conn, lause);
            while(tulemus.next()){
                
                System.out.println(tulemus.getString(2));
                model.addRow(new Object[]{tulemus.getString(1),tulemus.getString(2),tulemus.getString(3)});
            }
    }
}