DAL

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DAL {
	private Connection con = null;
	public Connection getConnection() throws SQLException {
		//String driver = ("com.microsoft.sqlserver.jdbc.SQLServerDriver");
		String url ="jdbc:sqlserver://localhost:1433;Database=Assignment1;";
		String user = "assignment1";
		String pass = "uppgift1";

		return DriverManager.getConnection(url, user, pass);

	}
	//method register student
	public DAL insertStudent(String studentID, String firstName, String lastName, int credits, String address) {
        String sql = "INSERT INTO Student(studentID, fristName, lastName, creadits, address) VALUES(?, ?, ?, ?, ?)";

        try (Connection con = this.getConnection();
        PreparedStatement pstmt = con.prepareStatement(sql)) {
        	pstmt.setString(1, c.getStudentID);
        	pstmt.setString(2, c.getFirstName);
        	pstmt.setString(3, c.getLastName);
        	pstmt.setString(4, c.getAddress);
        	pstmt.setInt(5, c.getCredits);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
        	e.printStackTrace();
        }
	}

	//method register course
    public void insertCourse(String courseID, String courseName, int credits) {
    	String sql = "INSERT INTO Course(studentID, courseName, creadits) VALUES(?, ?, ?)";

    	try (Connection con = this.getConnection();
    	PreparedStatement pstmt = con.prepareStatement(sql)) {
    		pstmt.setString(1, c.getCourseID);
    		pstmt.setString(2, c.getCourseName);
    		pstmt.setInt(3, c.getCredits);
    		pstmt.executeUpdate();
    	}
        catch (SQLException e) {
        	e.printStackTrace();
        }
    }

    //method register studies relation
	public void insertStudies(String studentID, String courseID, int credits) {
        String sql = "INSERT INTO Studies(studentID, courseID , creadits) VALUES(?, ?, ?)";

        try (Connection con = this.getConnection();
        PreparedStatement pstmt = con.prepareStatement(sql)) {
        	pstmt.setString(1, c.getStudentID);
        	pstmt.setString(2, c.getCourseID);
        	pstmt.setInt(3, c.getCredits);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
        	e.printStackTrace();
        }
    }

	//method register hasStudied relation
	public void insertHasStudied(String studentID, String courseID, String grade) {
        String sql = "INSERT INTO HasStudied(studentID, courseID , grade) VALUES(?, ?, ?)";

        try (
        Connection con = this.getConnection();
        PreparedStatement pstmt = con.prepareStatement(sql)) {
        	pstmt.setString(1, c.getStudentID);
        	pstmt.setString(2, c.getCourseID);
        	pstmt.setString(3, c.getGrade);
            pstmt.executeUpdate();
        }
        catch (SQLException e) {
        	e.printStackTrace();
        }
	}


	//Find Student
	public void findStudent(String studentID) {
		String sql = "SELECT * FROM Student WHERE studentID = '?'";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1,studentID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString("studentID") + rs.getString("studentName") +
				rs.getString("address") + rs.getInt("credits"));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
	}


	//Find Course
	public void findCourse(String courseID) {
		String sql = "SELECT * FROM Course WHERE courseID = '?'";

		try {
		Connection con = this.getConnection();
		PreparedStatement pstmt = con.prepareStatement(sql);
			pstmt.setString(1, c.getCourseID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				System.out.println(rs.getString(1) + rs.getString(2) + rs.getString(3));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
	}


	//Find Results form a specific course (test for find)
	public void findAllStudentResultsFromCourse(String courseID) {
		String query = "SELECT s.studentID, s.firstName, s.lastName, hs,grade FROM hasStudied hs JOIN Studies s "
		+ "ON hs.studentID = s.studentID WHERE hs.courseID = '?'";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1, c.getCourseID);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
					//change to show resultSet for a user interface
				System.out.printf(rs.getString(1), rs.getString(2), rs.getString(3));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}
	}

	// Find students that has not finished a course

	public void findStudentThatHaveNotFinishedACourse(String studentID) {
		String query = "SELECT s.firstName, s.lastName, st.courseID FROM Studies st JOIN Student s "
				+ "ON st.studentID = s.studentID WHERE studentID = '?' AND courseID = '?'";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1, c.getStudentID);
			pstmt.setString(2, c.getcourseID);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString(1) + ", " + rs.getString(firstName) + rs.getString(3));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}

	}

	//find certain result from a certain student

	public void findCertainResultForACertainStudent(String studentID, String courseID) {
		String query = "SELECT hs.grade, c.courseID FROM HasStudied hs JOIN Course c "
				+ "ON hs.courseID = c.courseID WHERE studentID = '?' AND courseID = '?'";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1, c.getStudentID);
			pstmt.setString(2, c.getcourseID);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString(1) + rs.getString(2));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();
		}

	}

	//Find % of student with an A for a certain course

	public void procentageForCertainCourseWithGradeA(String courseID) {
		String query = "SELECT grade, (COUNT(grade) * 100) / "
				+ "(SELECT COUNT(grade) FROM HasStudied WHERE courseID = '?')" +
				"FROM HasStudied" +
				"WHERE grade = 'A' AND courseID = '?'" +
				"GROUP BY Grade";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1, c.getcourseID);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString(1) + rs.getString(2));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();

		}
	}

	//Find best Throughput for courses

	public void FindInfoForCoursesWithBestThroughput() {
		String query = "select top 3 upper(courseID) as 'Course Code', (sum(case when grade != 'U'" +
				"then 1 else 0 end)*100) / count (courseID) as 'Percent Passed'" +
				"from hasStudied" +
				"group by courseID" +
 				"order by 'Percent Passed' desc;";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()) {
				System.out.println(rs.getString(1) + rs.getString(2));
			}
		}
		catch(SQLException e) {
			e.printStackTrace();

		}
	}

	//Delete Student

	public void DeleteStudent(String studentID) {
		String query = "DELETE FROM Student WHERE studentID = '?'";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1,studentID);
			ResultSet rs = pstmt.executeQuery();

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

	//Delete Course

	public void DeleteCourse(String courseID) {
		String query = "DELETE FROM Course WHERE courseID = '?'";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1,courseID);
			ResultSet rs = pstmt.executeQuery();

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

	//Delete studies

	public void DeleteStudiesRelation(String courseID, String studentID) {
		String query = "DELETE FROM Studies WHERE courseID = '?' AND studentID = '?'";

		try {
			Connection con = this.getConnection();
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setString(1,courseID);
			pstmt.setString(2,studentID);
			ResultSet rs = pstmt.executeQuery();

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



}