/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package main;

import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import oracle.sql.DATE;
//import java.util.Date;
import java.text.SimpleDateFormat;


/**
 *
 * @author FON
 */
public class Main {
    private static String url = "jdbc:oracle:thin:@localhost:1521:orcl";
    private static String user = "student";
    private static String password = "student";

    public static void main(String[] args) {
      /*   System.out.println("Pre izmene:");
        vratiOdeljenja();
       // vratiPoSifri(20);
       /* izbrisi(777);
        System.out.println("Posle brisanja:");
        vratiOdeljenja();*/

       /* izmeni(20);
         System.out.println("Posle izmene:");
        vratiOdeljenja();*/

        /*izmeni2("Novi Sad");
        System.out.println("Posle izmene:");
        vratiOdeljenja();*/

        System.out.println("Angazovanja pre ubacivanja: ");
        prikaziAngazovanje();



       //ubaciAngazovanje(1, 666, '2010-05-08', '2011-08-09', 11);

    }

    public static void vratiOdeljenja(){
        String query = "select * from odeljenje";

        Connection connection;
        try {
            connection = DriverManager.getConnection(url, user, password);
             Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(query);

             while(rs.next()){
                 int sifraodelj = rs.getInt("sifraodelj");
                 String odelj = rs.getString("nazivodelj");
                 String grad = rs.getString("grad");

                 System.out.println("Sifra: "+sifraodelj+" Odeljenje: "+odelj+" Grad: "+grad);
             }

             rs.close();
             stmt.close();
             connection.close();


        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }


    }

    public static void ubaciOdeljenje(int sifra, String naziv, String grad){
        String query = "insert into odeljenje values(?,?,?)";

        try {
            Connection connection = DriverManager.getConnection(url, user, password);
            connection.setAutoCommit(false);
            PreparedStatement ps = connection.prepareStatement(query);

            ps.setInt(1, sifra);
            ps.setString(2, naziv);
            ps.setString(3, grad);

            int br = ps.executeUpdate();

            if(br == 1)
                connection.commit();
            else
                connection.rollback();

            ps.close();
            connection.close();

        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void vratiPoSifri(int sifra){
        String query = "select * from odeljenje where sifraodelj = ?";

        try {
            Connection connection = DriverManager.getConnection(url, user, password);
            PreparedStatement ps = connection.prepareStatement(query);

            ps.setInt(1, sifra);

            ResultSet rs = ps.executeQuery();
            while(rs.next()){
                int sifrao = rs.getInt("sifraodelj");
                String naziv = rs.getString("nazivodelj");
                String grad = rs.getString("grad");

                System.out.println("Sifra: "+sifrao+" Naziv: "+naziv+" Grad: "+grad);
            }

            rs.close();
            ps.close();
            connection.close();

        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void izbrisi(int sifra){
        String query = "delete from odeljenje where sifraodelj = ?";

        try {
            Connection connection = DriverManager.getConnection(url, user, password);
            PreparedStatement ps = connection.prepareStatement(query);

            ps.setInt(1, sifra);

            int br = ps.executeUpdate();

            if(br == 1)
                connection.commit();
            else
                connection.rollback();

            ps.close();
            connection.close();

        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void izmeni(int sifra){
        String query = "update odeljenje set nazivodelj = 'Branko' where sifraodelj = ?";

        try {
            Connection con = DriverManager.getConnection(url, user, password);
            PreparedStatement ps = con.prepareStatement(query);

            ps.setInt(1, sifra);
           // ps.setString(1, naziv);

            int br = ps.executeUpdate();

            if(br == 1)
                con.commit();
            else con.rollback();

            ps.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void izmeni2(String grad){
        String query = "update odeljenje set nazivodelj = 'teorijasistema' where grad = ?";

        try {
            Connection con = DriverManager.getConnection(url, user, password);
            PreparedStatement ps = con.prepareStatement(query);

            ps.setString(1, grad);

            int br = ps.executeUpdate();

            if(br >= 1)
                con.commit();
            else
                con.rollback();

            ps.close();
            con.close();


        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }

    }

    public static void prikaziAngazovanje(){
        String query = "select * from angazovanje";

        try {
            Connection con = DriverManager.getConnection(url, user, password);
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            while(rs.next()){
                int sifrazap = rs.getInt("sifrazap");
                int sifrarm = rs.getInt("sifrarm");
                Date datod = rs.getDate("datod");
                Date datdo = rs.getDate("datdo");
                int sifrars = rs.getInt("sifrars");

                System.out.println("Sifra zaposlenog: "+sifrazap+" Sifra radnog mesta: "+sifrarm+" Datum od: "+datod+" Datum do: "+datdo+" Sifra radnos statusa: "+sifrars);

            }

            rs.close();
            stmt.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void ubaciAngazovanje(int sifrazap, int sifrarm, Date datod, Date datdo, int sifrars){
        String query = "insert into angazovanje values(?,?,?,?,?)";

        try {
            Connection con = DriverManager.getConnection(url, user, password);
            PreparedStatement ps = con.prepareStatement(query);

            ps.setInt(1, sifrazap);
            ps.setInt(2, sifrarm);
            ps.setDate(3, datod);
            ps.setDate(4, datdo);
            ps.setInt(5, sifrars);

            int br = ps.executeUpdate();

            if(br == 1)
                con.commit();
            else
                con.rollback();

            ps.close();
            con.close();
        } catch (SQLException ex) {
            Logger.getLogger(Main.class.getName()).log(Level.SEVERE, null, ex);
        }
    }



}