UAS PD2

package tapd2;

import java.sql.*;
import java.util.ArrayList;
import java.util.Scanner;

public class TAPD2 {

    public static void menu() {
        System.out.println("");
        System.out.println("###Pilihan Menu###");
        System.out.println(" ");
        System.out.println("1. Create");
        System.out.println("2. View");
        System.out.println("3. Delete");
        System.out.println("4. Update");
        System.out.println("5. Quit");
        System.out.println("");
        System.out.print("Pilihan no : ");
    }

    public static void insert() throws Exception {
        double temp[] = new double[5];
        double tempbobot[] = new double[4];
        double tempbobot1[] = new double[4];
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1/uaspd2",
                "root",
                "");
        Statement stmt = conn.createStatement();
        ResultSet rsbobot = stmt.executeQuery("SELECT * from bobot WHERE id_bobot= 141111003 ;");
        while (rsbobot.next()) {
            tempbobot[0] = ((rsbobot.getInt("bobot_wawasan")) * 0.01);
            tempbobot[1] = ((rsbobot.getInt("bobot_jaringan")) * 0.01);
            tempbobot[2] = ((rsbobot.getInt("bobot_kepribadian")) * 0.01);
            tempbobot[3] = ((rsbobot.getInt("bobot_agama")) * 0.01);
        }
        System.out.print("ID\t: ");
        int id = new Scanner(System.in).nextInt();
        boolean ketemu = false;
        ResultSet rsid = stmt.executeQuery("SELECT id_mentah from karyawan_mentahan where id_mentah =" + id + ";");
        while (rsid.next()) {
            ketemu = true;
        }
        if (ketemu == true) {
            System.out.println("**ID sudah ada**");
        } else {
            System.out.print("Nama\t: ");
            String nama = new Scanner(System.in).nextLine();
            int wawasan, jaringan, kepribadian, agama;
            do {
                System.out.print("Tes Wawasan\t\t: ");
                wawasan = new Scanner(System.in).nextInt();
            } while (wawasan < 0 || wawasan > 100);
            do {
                System.out.print("Tes Instalasi Jaringan\t: ");
                jaringan = new Scanner(System.in).nextInt();
            } while (jaringan < 0 || jaringan > 100);
            do {
                System.out.print("Tes Kepribadian\t\t: ");
                kepribadian = new Scanner(System.in).nextInt();
            } while (kepribadian < 0 || kepribadian > 100);
            do {
                System.out.print("Tes Agama\t\t: ");
                agama = new Scanner(System.in).nextInt();
            } while (agama < 0 || agama > 100);
            double wawasan1 = wawasan * tempbobot[0];
            double jaringan1 = jaringan * tempbobot[1];
            double kepribadian1 = kepribadian * tempbobot[2];
            double agama1 = agama * tempbobot[3];
            double hasil = wawasan1 + jaringan1 + kepribadian1 + agama1;
            String create = "INSERT INTO karyawan (id,nama,wawasan,jaringan,kepribadian,agama)"
                    + "VALUES ('" + id + "','" + nama + "'," + wawasan1 + "," + jaringan1 + "," + kepribadian1 + "," + agama1 + ");";
            String create_mentah = "INSERT INTO karyawan_mentahan (id_mentah,nama_mentah,wawasan_mentah,jaringan_mentah,kepribadian_mentah,agama_mentah)"
                    + "VALUES ('" + id + "','" + nama + "'," + wawasan + "," + jaringan + "," + kepribadian + "," + agama + ");";
            stmt.executeUpdate(create);
            stmt.executeUpdate(create_mentah);
            String hasilhasil = "INSERT INTO hasil(hasil_akhir,id)" + "VALUES (" + hasil + ",'" + id + "');";
            stmt.executeUpdate(hasilhasil);
            System.out.println("*Create Sukses*");
        }
    }

    public static void view() throws Exception {
        String lihat[] = new String[1];
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1/uaspd2",
                "root",
                "");
        Statement stmt = conn.createStatement();
        ResultSet rsview = stmt.executeQuery("SELECT karyawan.id from karyawan");
        while (rsview.next()) {
            lihat[0] = rsview.getString("id");
        };
        if (lihat[0] != null) {
            System.out.println("1. View data olahan");
            System.out.println("2. View data asli");
            System.out.println("3. View bobot kriteria");
            System.out.print("Pilih no : ");
            int no = new Scanner(System.in).nextInt();
            if (no == 1) {
                ResultSet rs = stmt.executeQuery("SELECT karyawan.id,karyawan.nama,karyawan.wawasan,karyawan.jaringan,karyawan.kepribadian,karyawan.agama,hasil.hasil_akhir from karyawan,hasil WHERE karyawan.id=hasil.id order by hasil.hasil_akhir desc;");
                System.out.println("+-------+-----------------+-------------------------------------+---------------------------------------+-----------------------+-----------------------+---------------+");
                System.out.println("|ID\t| Nama\t\t  | Tes wawasan Sistem Informasi\t| Tes praktek instalasi Jaringan\t| Tes kepribadian\t| Tes pengetahuan Agama\t| Hasil Akhir\t|");
                System.out.println("+-------+-----------------+-------------------------------------+---------------------------------------+-----------------------+-----------------------+---------------+");
                while (rs.next()) {
                    System.out.println("| " + rs.getString("id") + "\t| " + rs.getString("nama") + "\t\t  | \t\t" + rs.getInt("wawasan") + "\t\t\t| \t\t" + rs.getInt("jaringan") + "\t\t\t| \t" + rs.getInt("kepribadian") + "\t\t| \t" + rs.getInt("agama") + "\t\t|\t" + rs.getInt("hasil_akhir") + "\t|");
                }
                System.out.println("+-------+-----------------+-------------------------------------+---------------------------------------+-----------------------+-----------------------+---------------+");
            } else if (no == 2) {
                ResultSet rst = stmt.executeQuery("SELECT karyawan_mentahan.id_mentah,karyawan_mentahan.nama_mentah,karyawan_mentahan.wawasan_mentah,karyawan_mentahan.jaringan_mentah,karyawan_mentahan.kepribadian_mentah,karyawan_mentahan.agama_mentah from karyawan_mentahan order by karyawan_mentahan.id_mentah;");
                System.out.println("+-------+-----------------+-------------------------------------+---------------------------------------+-----------------------+-----------------------+");
                System.out.println("|ID\t| Nama\t\t  | Tes wawasan Sistem Informasi\t| Tes praktek instalasi Jaringan\t| Tes kepribadian\t| Tes pengetahuan Agama\t|");
                System.out.println("+-------+-----------------+-------------------------------------+---------------------------------------+-----------------------+-----------------------+");
                while (rst.next()) {
                    System.out.println("| " + rst.getString("id_mentah") + "\t| " + rst.getString("nama_mentah") + "\t\t  | \t\t" + rst.getInt("wawasan_mentah") + "\t\t\t| \t\t" + rst.getInt("jaringan_mentah") + "\t\t\t| \t" + rst.getInt("kepribadian_mentah") + "\t\t| \t" + rst.getInt("agama_mentah") + "\t\t|");
                }
                System.out.println("+-------+-----------------+-------------------------------------+---------------------------------------+-----------------------+-----------------------+");
            } else if (no == 3) {
                ResultSet rsbobot = stmt.executeQuery("SELECT * from bobot WHERE id_bobot= 141111003 ;");
                while (rsbobot.next()) {
                    System.out.println("Bobot Tes Wawasan\t: " + (rsbobot.getInt("bobot_wawasan")) + "%");
                    System.out.println("Bobot Tes Jaringan\t: " + (rsbobot.getInt("bobot_jaringan")) + "%");
                    System.out.println("Bobot Tes Kepribadian\t: " + (rsbobot.getInt("bobot_kepribadian")) + "%");
                    System.out.println("Bobot Tes Agama\t\t: " + (rsbobot.getInt("bobot_agama")) + "%");
                }
            } else {
                System.out.println("Pilihan No Salah");
            }
        } else {
            System.out.println("Data masih kosong");
        }
    }

    public static void deleted() throws Exception {
        String terhapus[] = new String[1];
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1/uaspd2",
                "root",
                "");
        Statement stmt = conn.createStatement();
        System.out.print("Masukkan ID yang akan dihapus : ");
        String hapus = new Scanner(System.in).nextLine();
        ResultSet rshapus = stmt.executeQuery("SELECT karyawan.id from karyawan where karyawan.id=" + hapus + ";");
        while (rshapus.next()) {
            terhapus[0] = rshapus.getString("id");
        };
        if (terhapus[0] != null) {
            String delete = "DELETE FROM hasil WHERE hasil.id=" + hapus + ";";
            stmt.executeUpdate(delete);
            String sql_del = "DELETE FROM karyawan WHERE karyawan.id=" + hapus + ";";
            stmt.executeUpdate(sql_del);
            String sql_del_mentah = "DELETE FROM karyawan_mentahan WHERE karyawan_mentahan.id_mentah=" + hapus + ";";
            stmt.executeUpdate(sql_del_mentah);
            System.out.println("*Delete Sukses*");
        } else {
            System.out.println("ID tidak ada");
        }
    }

    public static void updated() throws Exception {
        String terupdate[] = new String[1];
        double tempbobot[] = new double[4];
        int tempbobotasli[] = new int[4];
        ArrayList tempwawasan = new ArrayList();
        ArrayList tempjaringan = new ArrayList();
        ArrayList tempkepribadian = new ArrayList();
        ArrayList tempagama = new ArrayList();
        ArrayList tempid = new ArrayList();
        ArrayList tempid1 = new ArrayList();
        ArrayList tempid2 = new ArrayList();
        ArrayList tempid3 = new ArrayList();
        double temp[] = new double[4];

        Scanner baca = new Scanner(System.in);
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1/uaspd2",
                "root",
                "");
        Statement stmt = conn.createStatement();
        System.out.println("Pilihan Update : ");
        System.out.println("1. Update Karyawan");
        System.out.println("2. Update Bobot Kriteria");
        System.out.println("");
        System.out.print("Pilih No : ");
        int pilihupdate = new Scanner(System.in).nextInt();
        if (pilihupdate == 1) {
            System.out.print("Masukkan ID yang akan diupdate : ");
            int id_update = new Scanner(System.in).nextInt();
            ResultSet rsupdate = stmt.executeQuery("SELECT karyawan.id from karyawan where karyawan.id=" + id_update + ";");
            while (rsupdate.next()) {
                terupdate[0] = rsupdate.getString("id");
            };
            if (terupdate[0] != null) {
                ResultSet rs = stmt.executeQuery("SELECT wawasan,jaringan,kepribadian,agama from karyawan WHERE id=" + id_update + ";");
                while (rs.next()) {
                    temp[0] = (rs.getInt("wawasan"));
                    temp[1] = (rs.getInt("jaringan"));
                    temp[2] = (rs.getInt("kepribadian"));
                    temp[3] = (rs.getInt("agama"));
                }
                System.out.println("");
                System.out.println("Bagian yang akan di update : ");
                System.out.println("1. Update Nama");
                System.out.println("2. Update Tes Wawasan");
                System.out.println("3. Update Tes Instalasi Jaringan");
                System.out.println("4. Update Tes Kepribadian");
                System.out.println("5. Update Tes Agama");
                System.out.println("");
                System.out.print("Update no : ");
                int pilih = baca.nextInt();
                if (pilih == 1) {
                    System.out.print("Nama\t: ");
                    String name = new Scanner(System.in).nextLine();
                    String sql_update_nama = "UPDATE karyawan SET karyawan.nama='" + name + "' WHERE karyawan.id=" + id_update + ";";
                    stmt.executeUpdate(sql_update_nama);
                    String sql_update_nama_mentahan = "UPDATE karyawan_mentahan SET karyawan_mentahan.nama_mentah='" + name + "' WHERE karyawan_mentahan.id_mentah=" + id_update + ";";
                    stmt.executeUpdate(sql_update_nama_mentahan);
                } else if (pilih == 2) {
                    System.out.print("Tes Wawasan\t: ");
                    int wawasan = new Scanner(System.in).nextInt();
                    double wawasan1 = wawasan * tempbobot[0];
                    temp[0] = wawasan1;
                    String sql_update_wawasan = "UPDATE karyawan SET karyawan.wawasan=" + wawasan1 + " WHERE karyawan.id=" + id_update + ";";
                    stmt.executeUpdate(sql_update_wawasan);
                    String sql_update_wawasan_mentah = "UPDATE karyawan_mentahan SET karyawan_mentahan.wawasan_mentah=" + wawasan + " WHERE karyawan_mentahan.id_mentah=" + id_update + ";";
                    stmt.executeUpdate(sql_update_wawasan_mentah);
                } else if (pilih == 3) {
                    System.out.print("Tes Instalasi Jaringan\t: ");
                    int jaringan = new Scanner(System.in).nextInt();
                    double jaringan1 = jaringan * tempbobot[1];
                    temp[1] = jaringan1;
                    String sql_update_jaringan = "UPDATE karyawan SET karyawan.jaringan=" + jaringan1 + " WHERE karyawan.id=" + id_update + ";";
                    stmt.executeUpdate(sql_update_jaringan);
                    String sql_update_jaringan_mentah = "UPDATE karyawan_mentahan SET karyawan_mentahan.jaringan_mentah=" + jaringan + " WHERE karyawan_mentahan.id_mentah=" + id_update + ";";
                    stmt.executeUpdate(sql_update_jaringan_mentah);
                } else if (pilih == 4) {
                    System.out.print("Tes Kepribadian\t: ");
                    int kepribadian = new Scanner(System.in).nextInt();
                    double kepribadian1 = kepribadian * tempbobot[2];
                    temp[2] = kepribadian1;
                    String sql_update_kepribadian = "UPDATE karyawan SET karyawan.kepribadian=" + kepribadian1 + " WHERE karyawan.id=" + id_update + ";";
                    stmt.executeUpdate(sql_update_kepribadian);
                    String sql_update_kepribadian_mentah = "UPDATE karyawan_mentahan SET karyawan_mentahan.kepribadian_mentah=" + kepribadian + " WHERE karyawan_mentahan.id_mentah=" + id_update + ";";
                    stmt.executeUpdate(sql_update_kepribadian_mentah);
                } else if (pilih == 5) {
                    System.out.print("Tes Agama\t: ");
                    int agama = new Scanner(System.in).nextInt();
                    double agama1 = agama * tempbobot[3];
                    temp[3] = agama1;
                    String sql_update_agama = "UPDATE karyawan SET karyawan.agama=" + agama1 + " WHERE karyawan.id=" + id_update + ";";
                    stmt.executeUpdate(sql_update_agama);
                    String sql_update_agama_mentah = "UPDATE karyawan_mentahan SET karyawan_mentahan.agama_mentah=" + agama + " WHERE karyawan_mentahan.id_mentah=" + id_update + ";";
                    stmt.executeUpdate(sql_update_agama_mentah);
                }
                double hasil = temp[0] + temp[1] + temp[2] + temp[3];
                String hasilhasil = "UPDATE hasil SET hasil.hasil_akhir=" + hasil + "WHERE hasil.id=" + id_update + ";";
                stmt.executeUpdate(hasilhasil);
                System.out.println("*Update Sukses*");
            } else {
                System.out.println("ID tidak ada");
            }
        } else if (pilihupdate == 2) {
            ResultSet rsbobot = stmt.executeQuery("SELECT * from bobot WHERE id_bobot= 141111003 ;");
            while (rsbobot.next()) {
                tempbobotasli[0] = (rsbobot.getInt("bobot_wawasan"));
                tempbobotasli[1] = (rsbobot.getInt("bobot_jaringan"));
                tempbobotasli[2] = (rsbobot.getInt("bobot_kepribadian"));
                tempbobotasli[3] = (rsbobot.getInt("bobot_agama"));
            }

            tempbobot[0] = tempbobotasli[0] * 0.01;
            tempbobot[1] = tempbobotasli[1] * 0.01;
            tempbobot[2] = tempbobotasli[2] * 0.01;
            tempbobot[3] = tempbobotasli[3] * 0.01;

            System.out.println("");
            System.out.println("Bobot wawasan sebelumnya\t: " + tempbobotasli[0] + "%");
            System.out.println("Bobot jaringan sebelumnya\t: " + tempbobotasli[1] + "%");
            System.out.println("Bobot kepribadian sebelumnya\t: " + tempbobotasli[2] + "%");
            System.out.println("Bobot agama sebelumnya\t\t: " + tempbobotasli[3] + "%");
            System.out.println("***Total Bobot harus 100%***");
            int totalbobot = 0;
            do {
                int bobotwawasan;
                int bobotjaringan;
                int bobotkepribadian;
                int bobotagama;
                do {
                    System.out.print("Masukkan bobot Wawasan yang baru\t: ");
                    bobotwawasan = new Scanner(System.in).nextInt();
                } while (bobotwawasan < 0 || bobotwawasan > 100);

                do {
                    System.out.print("Masukkan bobot Jaringan yang baru\t: ");
                    bobotjaringan = new Scanner(System.in).nextInt();
                } while (bobotjaringan < 0 || bobotjaringan > 100);

                do {
                    System.out.print("Masukkan bobot Kepribadian yang baru\t: ");
                    bobotkepribadian = new Scanner(System.in).nextInt();
                } while (bobotkepribadian < 0 || bobotkepribadian > 100);

                do {
                    System.out.print("Masukkan bobot Agama yang baru\t\t: ");
                    bobotagama = new Scanner(System.in).nextInt();
                } while (bobotagama < 0 || bobotagama > 100);
                totalbobot = bobotwawasan + bobotjaringan + bobotkepribadian + bobotagama;
                System.out.println("");
                if (totalbobot == 100) {
                    String sql_update_bobot_wawasan = "UPDATE bobot SET bobot.bobot_wawasan=" + bobotwawasan + " WHERE bobot.id_bobot= 141111003;";
                    stmt.executeUpdate(sql_update_bobot_wawasan);
                    ResultSet rs1 = stmt.executeQuery("SELECT * from karyawan_mentahan ;");
                    while (rs1.next()) {
                        tempwawasan.add(((rs1.getInt("wawasan_mentah")) * (bobotwawasan * 0.01)));
                        tempid.add(rs1.getString("id_mentah"));
                    }


                    String sql_update_bobot_jaringan = "UPDATE bobot SET bobot.bobot_jaringan=" + bobotjaringan + " WHERE bobot.id_bobot= 141111003;";
                    stmt.executeUpdate(sql_update_bobot_jaringan);
                    ResultSet rs2 = stmt.executeQuery("SELECT * from karyawan_mentahan ;");
                    while (rs2.next()) {
                        tempjaringan.add(((rs2.getInt("jaringan_mentah")) * (bobotjaringan * 0.01)));
                        tempid1.add(rs2.getString("id_mentah"));
                    }



                    String sql_update_bobot_kepribadian = "UPDATE bobot SET bobot.bobot_kepribadian=" + bobotkepribadian + " WHERE bobot.id_bobot= 141111003;";
                    stmt.executeUpdate(sql_update_bobot_kepribadian);
                    ResultSet rs3 = stmt.executeQuery("SELECT * from karyawan_mentahan ;");
                    while (rs3.next()) {
                        tempkepribadian.add(((rs3.getInt("kepribadian_mentah")) * (bobotkepribadian * 0.01)));
                        tempid2.add(rs3.getString("id_mentah"));
                    }


                    String sql_update_bobot_agama = "UPDATE bobot SET bobot.bobot_agama=" + bobotagama + " WHERE bobot.id_bobot= 141111003;";
                    stmt.executeUpdate(sql_update_bobot_agama);
                    ResultSet rs4 = stmt.executeQuery("SELECT * from karyawan_mentahan ;");
                    while (rs4.next()) {
                        tempagama.add(((rs4.getInt("agama_mentah")) * (bobotagama * 0.01)));
                        tempid3.add(rs4.getString("id_mentah"));
                    }

                    for (int i = 0; i < tempwawasan.size(); i++) {
                        String sql_update_wawasan = "UPDATE karyawan SET wawasan=" + tempwawasan.get(i) + ",jaringan=" + tempjaringan.get(i) + ",kepribadian=" + tempkepribadian.get(i) +",agama=" + tempagama.get(i) +"WHERE ID = " + tempid.get(i);
                        stmt.executeUpdate(sql_update_wawasan);
                    }


                    System.out.println("Update Bobot Sukses");
                } else if (totalbobot < 100) {
                    System.out.println("Update bobot gagal, total bobot kurang dari 100%");
                    System.out.println("Silahkan isi update lagi");
                } else {
                    System.out.println("Update bobot gagal, total bobot lebih dari 100%");
                    System.out.println("Silahkan isi update lagi");
                }
            } while (totalbobot != 100);
        } else {
            System.out.println("Pilihan Salah");
        }
    }

    public static void main(String[] args) throws Exception {
        Scanner baca = new Scanner(System.in);
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://127.0.0.1/uaspd2",
                "root",
                "");
        Statement stmt = conn.createStatement();
        double hasil[] = new double[1];
        boolean keluar = false;
        do {
            menu();
            int no = baca.nextInt();
            if (no == 1) {
                insert();
            } else if (no == 2) {
                view();
            } else if (no == 3) {
                deleted();
            } else if (no == 4) {
                updated();
            } else if (no == 5) {
                System.out.println("");
                System.out.println("**Terima Kasih**");
                System.out.println("");
                break;
            } else {
                System.out.println("Pilihan Salah");
            }
        } while (keluar == false);
    }

}