Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * 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 db.kursus;
- import java.sql.*;
- import java.util.*;
- import javax.swing.*;
- import java.text.*;
- import java.awt.event.*;
- /**
- *
- * @author Aditya Pramana
- */
- public class DBUserReport {
- private String url;
- private String username;
- private String password;
- private String dataSiswa;
- private String[][] dataPast;
- private String[][] dataCurrent;
- private String[][] overallAverage;
- private String[][] overallFavorite;
- private String[][] topPengajar;
- private JFrame frame;
- private JLabel lblSearch;
- private ButtonGroup group;
- private JRadioButton byNama;
- private JRadioButton byID;
- private JTextField txtInput;
- private JButton btnReport;
- private JButton btnGenerate;
- private DBGenerateReport s;
- private DBOverallReport t;
- public DBUserReport(String url, String username, String password){
- this.url = url;
- this.username = username;
- this.password = password;
- s = null;
- t = null;
- initializeComponent();
- }
- private void initializeComponent(){
- frame = new JFrame("[Guest] Search Mode");
- lblSearch = new JLabel("Cari Siswa");
- byNama = new JRadioButton("by Nama");
- byID = new JRadioButton("by ID");
- group = new ButtonGroup();
- txtInput = new JTextField();
- btnReport = new JButton("Overall Report");
- btnGenerate = new JButton("Generate");
- btnReport.addActionListener(new btnReportListener());
- btnGenerate.addActionListener(new btnGenerateListener());
- group.add(byNama);
- group.add(byID);
- frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
- frame.setSize(300, 160);
- frame.setResizable(false);
- //<editor-fold defaultstate="collapsed" desc=" A very long auto-generated code ">
- GroupLayout layout = new GroupLayout(frame.getContentPane());
- frame.getContentPane().setLayout(layout);
- layout.setHorizontalGroup(
- layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
- .addGroup(layout.createSequentialGroup()
- .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
- .addGroup(layout.createSequentialGroup()
- .addGap(16, 16, 16)
- .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
- .addComponent(txtInput)
- .addGroup(layout.createSequentialGroup()
- .addComponent(lblSearch)
- .addGap(18, 18, 18)
- .addComponent(byNama)
- .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
- .addComponent(byID)
- .addGap(0, 0, Short.MAX_VALUE))))
- .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
- .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
- .addComponent(btnReport)
- .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
- .addComponent(btnGenerate)))
- .addContainerGap())
- );
- layout.setVerticalGroup(
- layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
- .addGroup(layout.createSequentialGroup()
- .addGap(10, 10, 10)
- .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
- .addComponent(lblSearch)
- .addComponent(byNama)
- .addComponent(byID))
- .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
- .addComponent(txtInput, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
- .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
- .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
- .addComponent(btnReport)
- .addComponent(btnGenerate))
- .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
- );
- //</editor-fold>
- frame.setVisible(true);
- }
- public class btnGenerateListener implements ActionListener{
- @Override
- public void actionPerformed(ActionEvent e) {
- boolean isByNama = byNama.isSelected();
- boolean isByID = byID.isSelected();
- String input = txtInput.getText();
- if (!isByNama && !isByID){
- JOptionPane.showMessageDialog(null, "Please determine if search by Nama or ID");
- return;
- }
- try (Connection connection = DriverManager.getConnection(url, username, password)){
- Statement stmt = connection.createStatement();
- ResultSet rs;
- if (isByID){
- rs = stmt.executeQuery("SELECT NO_SISWA, S_NAMA, S_ALAMAT, S_TELP \n"
- + "FROM SISWA\n"
- + "WHERE NO_SISWA = '"+ input +"';");
- if (!rs.next()){
- JOptionPane.showMessageDialog(null, "ID " + input + " not found");
- connection.close();
- return;
- }
- String tmp = String.format("ID\t: %s\nNama\t: %s\nAlamat\t: %s\nTelepon\t: %s",
- rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
- if (tmp.equals(dataSiswa)) return;
- dataSiswa = tmp;
- rs = stmt.executeQuery("select pk.PK_TGLMULAI, c.C_NAMA, l.LV_NAMA, p.P_NAMA, pk.PK_NAMA\n"
- + "from siswa s, detil_kursus d, kelas_kursus kk, paket_kursus pk, cabang c, pengajar p, level_table l\n"
- + "where s.NO_SISWA = d.NO_SISWA\n"
- + "and d.ID_KELAS = kk.ID_KELAS\n"
- + "and kk.ID_CABANG = c.ID_CABANG\n"
- + "and kk.ID_PK = pk.ID_PK\n"
- + "and kk.ID_PENGAJAR = p.ID_PENGAJAR\n"
- + "and pk.LV_ID = l.LV_ID\n"
- + "and s.NO_SISWA = '" + input + "'\n"
- + "and d.STATUS_KURSUS = false;");
- List<String[]> temp = new ArrayList<>();
- SimpleDateFormat fr = new SimpleDateFormat("dd-MM-yyyy");
- if (!rs.next()) dataCurrent = null;
- else{
- do temp.add(new String[]{fr.format(rs.getDate(1)), rs.getString(2),
- rs.getString(3), rs.getString(4), rs.getString(5)});
- while (rs.next());
- dataCurrent = temp.toArray(new String[temp.size()][5]);
- }
- rs = stmt.executeQuery("select d.TGL_SELESAI, c.C_NAMA, l.LV_NAMA, pk.PK_NAMA, d.NILAI, \n"
- + "(case when d.NILAI < 70 then 'TIDAK LULUS' else 'LULUS' end) D_STATUS\n"
- + "from siswa s, detil_kursus d, kelas_kursus kk, paket_kursus pk, cabang c, level_table l\n"
- + "where s.NO_SISWA = d.NO_SISWA\n"
- + "and d.ID_KELAS = kk.ID_KELAS\n"
- + "and kk.ID_CABANG = c.ID_CABANG\n"
- + "and kk.ID_PK = pk.ID_PK\n"
- + "and pk.LV_ID = l.LV_ID\n"
- + "and s.NO_SISWA = '"+ input +"'\n"
- + "and d.STATUS_KURSUS = true\n"
- + "order by d.TGL_SELESAI;");
- temp = new ArrayList<>();
- if (!rs.next()) dataPast = null;
- else{
- do temp.add(new String[]{fr.format(rs.getDate(1)), rs.getString(2),
- rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)});
- while (rs.next());
- dataPast = temp.toArray(new String[temp.size()][6]);
- }
- }
- if (isByNama){
- rs = stmt.executeQuery("SELECT NO_SISWA, S_NAMA, S_ALAMAT, S_TELP \n"
- + "FROM SISWA\n"
- + "WHERE S_NAMA = '"+ input +"';");
- if (!rs.next()){
- JOptionPane.showMessageDialog(null, "Nama " + input + " not found");
- connection.close();
- return;
- }
- String tmp = String.format("ID\t: %s\nNama\t: %s\nAlamat\t: %s\nTelepon\t: %s",
- rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
- if (tmp.equals(dataSiswa)) return;
- dataSiswa = tmp;
- rs = stmt.executeQuery("select pk.PK_TGLMULAI, c.C_NAMA, l.LV_NAMA, p.P_NAMA, pk.PK_NAMA\n"
- + "from siswa s, detil_kursus d, kelas_kursus kk, paket_kursus pk, cabang c, pengajar p, level_table l\n"
- + "where s.NO_SISWA = d.NO_SISWA\n"
- + "and d.ID_KELAS = kk.ID_KELAS\n"
- + "and kk.ID_CABANG = c.ID_CABANG\n"
- + "and kk.ID_PK = pk.ID_PK\n"
- + "and kk.ID_PENGAJAR = p.ID_PENGAJAR\n"
- + "and pk.LV_ID = l.LV_ID\n"
- + "and s.S_NAMA = '" + input + "'\n"
- + "and d.STATUS_KURSUS = false;");
- List<String[]> temp = new ArrayList<>();
- SimpleDateFormat fr = new SimpleDateFormat("dd-MM-yyyy");
- if (!rs.next()) dataCurrent = null;
- else{
- do temp.add(new String[]{fr.format(rs.getDate(1)), rs.getString(2),
- rs.getString(3), rs.getString(4), rs.getString(5)});
- while (rs.next());
- dataCurrent = temp.toArray(new String[temp.size()][5]);
- }
- rs = stmt.executeQuery("select d.TGL_SELESAI, c.C_NAMA, l.LV_NAMA, pk.PK_NAMA, d.NILAI, \n"
- + "(case when d.NILAI < 70 then 'TIDAK LULUS' else 'LULUS' end) D_STATUS\n"
- + "from siswa s, detil_kursus d, kelas_kursus kk, paket_kursus pk, cabang c, level_table l\n"
- + "where s.NO_SISWA = d.NO_SISWA\n"
- + "and d.ID_KELAS = kk.ID_KELAS\n"
- + "and kk.ID_CABANG = c.ID_CABANG\n"
- + "and kk.ID_PK = pk.ID_PK\n"
- + "and pk.LV_ID = l.LV_ID\n"
- + "and s.S_NAMA = '" + input + "'\n"
- + "and d.STATUS_KURSUS = true\n"
- + "order by d.TGL_SELESAI;");
- temp = new ArrayList<>();
- if (!rs.next()) dataPast = null;
- else{
- do temp.add(new String[]{fr.format(rs.getDate(1)), rs.getString(2),
- rs.getString(3), rs.getString(4), rs.getString(5), rs.getString(6)});
- while (rs.next());
- dataPast = temp.toArray(new String[temp.size()][6]);
- }
- }
- connection.close();
- s = new DBGenerateReport(dataSiswa, dataCurrent, dataPast);
- }
- catch (SQLException ex){
- JOptionPane.showMessageDialog(null, "A database-related error has occured");
- }
- }
- }
- public class btnReportListener implements ActionListener{
- @Override
- public void actionPerformed(ActionEvent e) {
- try (Connection connection = DriverManager.getConnection(url, username, password)){
- Statement stmt = connection.createStatement();
- ResultSet rs;
- List<String[]> temp = new ArrayList<>();
- rs = stmt.executeQuery("select l.LV_NAMA, round(avg(d.NILAI),2)\n"
- + "from detil_kursus d, kelas_kursus kk, paket_kursus pk, level_table l\n"
- + "where d.ID_KELAS = kk.ID_KELAS\n"
- + "and kk.ID_PK = pk.ID_PK\n"
- + "and pk.LV_ID = l.LV_ID\n"
- + "and d.NILAI >= 70\n"
- + "group by l.LV_NAMA\n"
- + "order by l.LV_ID;");
- if (!rs.next()) overallAverage = null;
- else {
- do temp.add(new String[]{rs.getString(1), ((Float)rs.getFloat(2)).toString()});
- while (rs.next());
- overallAverage = temp.toArray(new String[temp.size()][2]);
- }
- temp = new ArrayList<>();
- rs = stmt.executeQuery("select pk.PK_NAMA, count(d.ID_KELAS)\n"
- + "from detil_kursus d, kelas_kursus kk, paket_kursus pk\n"
- + "where d.ID_KELAS = kk.ID_KELAS\n"
- + "and kk.ID_PK = pk.ID_PK\n"
- + "group by pk.PK_NAMA\n"
- + "order by count(d.ID_KELAS) desc limit 5;");
- if (!rs.next()) overallFavorite = null;
- else {
- do temp.add(new String[]{rs.getString(1), ((Integer)rs.getInt(2)).toString()});
- while (rs.next());
- overallFavorite = temp.toArray(new String[temp.size()][2]);
- }
- temp = new ArrayList<>();
- rs = stmt.executeQuery("select p.P_NAMA, pk.PK_NAMA, round(avg(d.NILAI), 1), pk.PK_TGLMULAI, pk.PK_TGLSELESAI\n"
- + "from detil_kursus d, kelas_kursus kk, paket_kursus pk, cabang c, pengajar p\n"
- + "where d.ID_KELAS = kk.ID_KELAS\n"
- + "and kk.ID_PK = pk.ID_PK\n"
- + "and kk.ID_PENGAJAR = p.ID_PENGAJAR\n"
- + "and d.NILAI is not null\n"
- + "group by p.P_NAMA, pk.PK_NAMA\n"
- + "order by avg(d.NILAI) desc limit 10;");
- if (!rs.next()) topPengajar = null;
- else {
- do temp.add(new String[]{rs.getString(1), rs.getString(2), ((Float)rs.getFloat(3)).toString()});
- while (rs.next());
- topPengajar = temp.toArray(new String[temp.size()][3]);
- }
- connection.close();
- if (t == null) t = new DBOverallReport(overallAverage, overallFavorite, topPengajar);
- else t.setVisible(true);
- }
- catch (SQLException ex){
- JOptionPane.showMessageDialog(null, "A database-related error has occured");
- }
- }
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement