import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.util.Scanner;


public class UpisStudenta2 {

   public static void main(String argv[]) {
      Connection connection = otvoriKonekciju();
      try {
		  //prepared statement za provjeravanje popunjenosti
		  PreparedStatement pstmt = connection.prepareStatement("select min(brojStud*1.0/kapacitet) from grupa where (brojStud*1.0/kapacitet)<1");
		  ResultSet rs = pstmt.executeQuery();
		  if ( rs.next() == false){
			  System.out.println("Sve grupe su već popunjene");
			  System.exit(-1);
		  }
		  rs.close();

		  Scanner scanner = new Scanner(System.in);
		 System.out.print("Upisite jmbag: ");
		 String jmbag = scanner.next();
		 PreparedStatement pstmt1 = connection.prepareStatement("Select count(*) from stud where jmbag = ?");
		 pstmt1.setString(1, jmbag);
		 rs = pstmt1.executeQuery();

		  if (rs.next() == false){
			  System.out.println("Student ne postoji");
			  System.exit(-1);
		  }
		  rs.close();

		  // prepared statement za dohvaćanje oznake grupe
		  PreparedStatement pstmt2 = connection.prepareStatement("SELECT top(1) oznGrupa from grupa where brojStud*1.0/kapacitet = (select min(brojStud*1.0/kapacitet) from grupa) and brojStud*1.0/kapacitet < 1 order by oznGrupa");
		  ResultSet rs2 = pstmt2.executeQuery();
		  while(rs2.next()){
			  String oG = rs2.getString(1);
			  System.out.println(oG);
		  }
         rs2.close();
         connection.close();
      }
      catch (SQLException exception1) {
         int errCode = exception1.getErrorCode();
         if (errCode == 50503) {
            ResultSet rs = null;
            try {

               int preostalo = rs.getInt("preostalo");
               System.out.println("Nije uspjelo raspore�ivanje " + preostalo + " studenata");
            }
            catch (SQLException exception2) {
               ispisiPogresku(exception2);
               System.exit(-1);
            }
         }
         else {
            ispisiPogresku(exception1);
            System.exit(-1);
         }
      }
   }

   private static void ispisiPogresku(SQLException exception) {
      System.out.println(exception.getErrorCode() + "; "
            + exception.getMessage() + "; "
            + "State=" + exception.getSQLState());
   }

   private static Connection otvoriKonekciju () {
      // sastavljanje JDBC URL:
      String url =
              "jdbc:sqlserver://localhost:1433;"   // ovdje staviti svoje vrijednosti
            + "databaseName=labprof1;"
            + "user=sa;"
            + "password=IsXpHbPh;" ;           // staviti svoje vrijednosti

      // u�itavanje i registriranje SQL Server JDBC driver-a
      try {
         // connection = DriverManager.getConnection(connectionString);
         Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
         System.out.println("SQL Server JDBC driver je u�itan i registriran.");
      } catch (ClassNotFoundException exception) {
         System.out.println("Pogre�ka: nije uspjelo u�itavanje JDBC driver-a.");
         System.out.println(exception.getMessage());
         System.exit(-1);
      }

      // uspostavljanje konekcije
      Connection conn = null;
      try {
         conn = DriverManager.getConnection(url);
         System.out.println("Konekcija je uspostavljena.");
      } catch (SQLException exception) {
         System.out.println("Pogre�ka: nije uspjelo uspostavljanje konekcije.");
         System.out.println(exception.getErrorCode() + " " + exception.getMessage());
         System.exit(-1);
      }
      return conn;
   }
}