package id.ac.unsyiah.rest.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import id.ac.unsyiah.rest.model.DaftarMahasiswa;
import id.ac.unsyiah.rest.model.Mahasiswa;
import id.ac.unsyiah.rest.model.MataKuliah;

public class ControllerMahasiswa {

	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	static final String DB_URL = "jdbc:mysql://localhost/belajar_webservice";

	static final String USER = "root";
	static final String PASS = "";

	public static DaftarMahasiswa getListMahasiswa() {

		Connection connection = null;
		Statement statement = null;

		DaftarMahasiswa daftarMahasiswa = new DaftarMahasiswa();

		try {

			Class.forName(JDBC_DRIVER);

			connection = DriverManager.getConnection(DB_URL, USER, PASS);

			statement = connection.createStatement();

			String query = "SELECT * FROM mahasiswa";

			ResultSet rs = statement.executeQuery(query);

			while (rs.next()) {
				Mahasiswa mahasiswaBaru = new Mahasiswa();

				mahasiswaBaru.nama = rs.getString("nama");
				mahasiswaBaru.nim = rs.getString("nim");
				mahasiswaBaru.jurusan = rs.getString("jurusan");
				mahasiswaBaru.alamat = rs.getString("alamat");

				daftarMahasiswa.mahasiswa.add(mahasiswaBaru);
			}

			for (Mahasiswa mhs : daftarMahasiswa.mahasiswa) {
				query = "SELECT * FROM mahasiswa,matakuliah,mengambil WHERE mahasiswa.nim=" + mhs.nim + " AND mengambil.nim=mahasiswa.nim AND mengambil.idmk = matakuliah.id";

				rs = statement.executeQuery(query);

				while (rs.next()) {
					MataKuliah mk = new MataKuliah();
					mk.mk = rs.getString("mk");
					mk.sks = rs.getInt("sks");
					mhs.mataKuliah.add(mk);
				}
			}

			rs.close();
			connection.close();
			statement.close();

		} catch (Exception e) {
			e.printStackTrace();

		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}

			try {
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e2) {
				// TODO: handle exception
			}
		}

		return daftarMahasiswa;
	}

}