/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package osiguranje;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JComboBox;
import javax.swing.JLabel;
import javax.swing.JTextField;
import javax.swing.table.DefaultTableModel;
import java.util.Iterator;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JTable;
/**
 *
 * @author BOMBA-PC
 */
public class SQL {
    
    private String url,  username,  password;
    public Connection con=null;

    public boolean Connect() {
        boolean status = false;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String serverName = "127.0.0.1";//
            String mydatabase = "osiguranje";
            url = "jdbc:mysql://" + serverName + ":3306/" + mydatabase;
            username = "root";//
            password = "0s1gur@nj3";//
            con = DriverManager.getConnection(url, username, password);
            if (!con.isClosed()) {
                System.out.println("Uspesno ste konektovani na bazu podataka!");
                status = true;
            }

        } catch (SQLException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(SQL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return status;
    }
    //citanje prvog slobodnog id-a
    public void SledeciID(JTextField id) {

       try {
            String query = "select IDKlijent+1 from klijenti order by IDKlijenti DESC limit 1";
         
            Statement stmt = con.createStatement();
            int r = stmt.executeUpdate(query);


            if(r==1){
                System.out.println("Upisano u bazu!");
            }else{
                System.out.println("Javila se greska!!");
            }
        } catch (SQLException e) {        
        }

    }
    
    //dodavanje u tabelu klijenti
    public void Insert(JTextField i, JTextField p, JTextField a, JTextField t, JTextField jmbg, JTextField dr, JTextField lk) {

        try {
            String query = "INSERT into klijenti (Ime, Prezime, Adresa, Telefon, JMBG, Datum_rodjenja, LK)"
            + " values ('"+i.getText()+"','"+p.getText()+"','"+a.getText()+"','"+t.getText()+"','"+jmbg.getText()+"','"+dr.getText()+"','"+lk.getText()+"')";
            Statement stmt = con.createStatement();
            int r = stmt.executeQuery(query);
            if(r==1){
                System.out.println("Upisano u bazu!");
            }else{
                System.out.println("Javila se greska!!");
            }
        } catch (SQLException e) {        
        }

    }
    public void Insert2(JTextField u, JTextField od, JTextField d) {

        try {
            String query = "INSERT into usluge (Vrsta_usluge, Vazi_od, Vazi_do)"
            + " values ('"+u.getText()+"','"+od.getText()+"','"+d.getText()+"')";
            Statement stmt = con.createStatement();
            int r = stmt.executeUpdate(query);
            if(r==1){
                System.out.println("Upisano u bazu!");
            }else{
                System.out.println("Javila se greska!!");
            }
        } catch (SQLException e) {        
        }

    }
    //dodavanje u tabelu adresar
    public void InsertAdresar(JTextField im, JTextField pr, JTextField ad, JTextField t1, JTextField t2, JTextField m, JTextField o) {

        try {
            String query = "INSERT into adresar (Ime, Prezime, Adresa, Telefon, Telefon2, Mail, Opis)"
            + " values ('"+im.getText()+"','"+pr.getText()+"','"+ad.getText()+"','"+t1.getText()+"','"+t2.getText()+"','"+m.getText()+"','"+o.getText()+"')";
            Statement stmt = con.createStatement();
            int r = stmt.executeUpdate(query);
            if(r==1){
                System.out.println("Upisano u bazu!");
            }else{
                System.out.println("Javila se greska!!");
            }
        } catch (SQLException e) {        
        }

    }
    //brisanje iz tebele adresar
    public void DeleteAdresar(JTextField id) {

        try {
            String query = "Delete from adresar WHERE IDAdresar = '"+Integer.parseInt(id.getText()) +"'";
            Statement stmt = con.createStatement();
            int r = stmt.executeUpdate(query);
            if(r==1){
                System.out.println("Obrisano iz baze!!!");
            }else{
                System.out.println("Javila se greska!!!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    //prikazivanje podataka iz tebele klijenti
    public void Selecttabela(JTable jtb) {
        List <String []>ls= new ArrayList<String []>();
        
        try {
            String query = "SELECT * FROM klijenti";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            String temp[]; 
            String nazivkolona[]={"ID","Ime","Prezime","Adresa","Telefon","JMBG","Datum rođenja","LK"};
            while (rs.next()) {
                
                temp = new String[8];
                temp[0] = rs.getInt("IDKlijenti")+"";
                temp[1] = rs.getString("Ime");
                temp[2] = rs.getString("Prezime");
                temp[3] = rs.getString("Adresa");
                temp[4] = rs.getString("Telefon");
                temp[5] = rs.getString("JMBG");
                temp[6] = rs.getString("Datum_rodjenja");
                temp[7] = rs.getString("LK");
                
                ls.add(temp);                                                      
            }
        } catch (SQLException e) {           
        }
         
         String nazivkolona[]={"ID","Ime","Prezime","Adresa","Telefon","JMBG","Datum rođenja","LK"};
         Object podaci[][] = new Object[ls.size()][8];
         Iterator <String []>it= ls.iterator();
         int br =0;
         while(it.hasNext()){
             podaci[br] = it.next();
             br++;
         }
         DefaultTableModel DTM = new DefaultTableModel(podaci, nazivkolona);
         jtb.setModel(DTM);
                 

    }  
    //prikazivanje podataka iz tabele adresar
     public void SelectTabelaAdresar(JTable jtb) {
        List <String []>ls= new ArrayList<String []>();
        
        try {
            String query = "SELECT * FROM adresar";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            String temp[]; 
            String nazivkolona[]={"ID","Ime","Prezime","Adresa","Telefon","Telefon2","E-mail","Opis"};
            while (rs.next()) {
                
                temp = new String[8];
                temp[0] = rs.getInt("IDAdresar")+"";
                temp[1] = rs.getString("Ime");
                temp[2] = rs.getString("Prezime");
                temp[3] = rs.getString("Adresa");
                temp[4] = rs.getString("Telefon");
                temp[5] = rs.getString("Telefon2");
                temp[6] = rs.getString("Mail");
                temp[7] = rs.getString("Opis");
                
                ls.add(temp);                                                      
            }
        } catch (SQLException e) {           
        }
         
         String nazivkolona[]={"ID","Ime","Prezime","Adresa","Telefon","Telefon 2","E-mail","Opis"};
         Object podaci[][] = new Object[ls.size()][8];
         Iterator <String []>it= ls.iterator();
         int br =0;
         while(it.hasNext()){
             podaci[br] = it.next();
             br++;
         }
         DefaultTableModel DTM = new DefaultTableModel(podaci, nazivkolona);
         jtb.setModel(DTM);
                 

    }  
     
     public void Select(JComboBox jcb) {

        try {

            String query = "SELECT Ime, Prezime FROM klijenti";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
           
            while (rs.next()) {                
                jcb.addItem(rs.getString("Ime")); 
                jcb.addItem(rs.getString("Prezime"));
            }

        } catch (SQLException e) {           
        }

    }
     
     
     
       
    
       public void Delete(JTextField id) {

        try {
            String query = "Delete from klijenti WHERE IDKlijenti = '"+Integer.parseInt(id.getText()) +"'";
            Statement stmt = con.createStatement();
            int r = stmt.executeUpdate(query);
            if(r==1){
                System.out.println("Obrisano iz baze!!!");
            }else{
                System.out.println("Javila se greska!!!");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

   
}