package controller;

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

import model.*;
import DatabaseAccesslayer.*;

public class Controller {

	private Student student;
	private Course course;
	private DAL dal;

	public Controller() {
		dal = new DAL();
	}

	public Student findStudent(String sPnr) throws SQLException {
		student = dal.findStudent(sPnr);
		return student;
	}

	public void addStudent(String sPnr, String sName, String sAdress) throws SQLException {
		dal.addStudent(sPnr, sName, sAdress);
	}

	public void deleteStudent(String sPnr)throws SQLException{
		dal.deleteStudent(sPnr);
	}


	public Course findCourse(String cCode) throws SQLException {
		course = dal.findCourse(cCode);
		return course;
	}

	public void addCourse(String cCode, String cName, int cPoints) throws SQLException {
		dal.addCourse(cCode, cName, cPoints);
	}

	public void deleteCourse(String cCode) throws SQLException {
		dal.deleteCourse(cCode);
	}
	public void removeStudentfromStudying(String sPnr, String cCode)throws SQLException {
		dal.removeStudentFromStudying(sPnr, cCode);
	}

	public ArrayList<Studied> findGrade(String sPnr, String cCode)throws SQLException{
		return dal.findGradeFromCourse(sPnr, cCode);
	}

	public ArrayList<Course> getAllCourse() throws SQLException{
		return dal.getAllCourse();
	}
	public ArrayList<Student> getAllStudent() throws SQLException{
		return dal.getAllStudent();
	}
	public ArrayList<Studying> getStudentsCourses(String sPnr) throws SQLException{
		return dal.getStudentsCourses(sPnr);
	}


}



******************************************************************************************

package DatabaseAccesslayer;

import java.sql.*;
import java.util.*;
import javax.swing.*;
import javax.swing.table.DefaultTableModel;
import view.DBStartView;

import connector.ConnectDb;
import model.*;

public class DAL {

	private String sqlString;
	private Student student;
	private String sPnr;
	private String sName;
	private String sAdress;
	private Course course;
	private String cCode;
	private String cName;
	private int cPoints;
	private JTable table_Student;
	private Studied studied;
	private Studying studying;

	// Find a specific student with sPnr********************************
	public Student findStudent(String sPnr) throws SQLException {

		Connection con = ConnectDb.startConnection();

		sqlString = "select * from student where spnr = '" + sPnr + "'";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		rs.next();
		sPnr = rs.getString(1);
		sName = rs.getString(2);
		sAdress = rs.getString(3);

		student = new Student(sPnr, sName, sAdress);

		return student;

	}

	//Finds list of all students
	public ArrayList<Student> getAllStudent()throws SQLException{
		ArrayList<Student> studentList = new ArrayList<Student>();

		Connection con = ConnectDb.startConnection();

		String sqlString = "Select * from student";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()){
			student = new Student(rs.getString(1), rs.getString(2), rs.getString(3));
				studentList.add(student);}
		return studentList;
	}

	// Find a specific course with cCode ****************************
	public Course findCourse(String cCode) throws SQLException {

		Connection con = ConnectDb.startConnection();

		sqlString = "select * from course where ccode = '" + cCode + "'";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		rs.next();
		cCode = rs.getString(1);
		cName = rs.getString(2);
		cPoints = rs.getInt(3);

		course = new Course(cCode, cName, cPoints);

		return course;
	}
	// Find all courses
	public ArrayList<Course> getAllCourse()throws SQLException{
		ArrayList<Course> courseList = new ArrayList<Course>();

		Connection con = ConnectDb.startConnection();
		sqlString = "select * from course";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);

		while (rs.next()) {
			course = new Course(rs.getString(1),rs.getString(2),rs.getInt(3));
			courseList.add(course);

		}return courseList;

	}

	// Add student
	public void addStudent(String sPnr, String sName, String sAdress) throws SQLException {
		Connection con = ConnectDb.startConnection();

		Statement stmt = con.createStatement();

		String sqlString = "insert into student values('" + sPnr + "','" + sName + "','" + sAdress + "')";
		stmt.executeUpdate(sqlString);
		stmt.close();
	}

	// Add course
	public void addCourse(String cCode, String cName, int cPoints) throws SQLException {
		Connection con = ConnectDb.startConnection();

		Statement stmt = con.createStatement();

		String sqlString = "insert into course values('" + cCode + "','" + cName + "','" + cPoints + "')";
		stmt.executeUpdate(sqlString);
		stmt.close();
	}
	// Delete Student
	public void deleteStudent(String sPnr) throws SQLException{

		Connection con = ConnectDb.startConnection();
		Statement stmt = con.createStatement();

		String sqlString = "delete spnr from student where spnr =" + sPnr;
		stmt.executeUpdate(sqlString);
		con.close();
	}
	// Delete Course
	public void deleteCourse(String cCode) throws SQLException {
		Connection con = ConnectDb.startConnection();

		Statement stmt = con.createStatement();

		String sqlString = "delete ccode from course where ccode = " + cCode;
		stmt.executeUpdate(sqlString);
		con.close();
	}
	// Delete Student from Studying (möjligen lägga till så student hamnar i studied efter delete)

	public void removeStudentFromStudying(String sPnr, String cCode)throws SQLException {

		Connection con = ConnectDb.startConnection();

		String sqlString = "Delete from Studying where spnr = " + sPnr + "and ccode = " + cCode;

		Statement stmt = con.createStatement();
		stmt.executeUpdate(sqlString);
		con.close();
	}

	//Gets all courses a student studies
	public ArrayList<Studying> getStudentsCourses(String cCode) throws SQLException {
		ArrayList<Studying> studentList = new ArrayList<Studying>();

		Connection con = ConnectDb.startConnection();

		String sqlString = " select s.spnr, s.sname, s.sadress, s.semester from studying s "
		+ " inner join course c "
		+ " on c.code = s.ccode"
		+ "where spnr = " + sPnr;

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		while (rs.next()){
			studying = new Studying(rs.getString(1), rs.getString(2), rs.getString(3));
			courseList.add(studying);
		}

		return courseList;


	}

	public ArrayList<Studying> getCoursesFromStudent(String sPnr) throws SQLException {
		ArrayList<Studying> courseList = new ArrayList<Studying>();

		Connection con = ConnectDb.startConnection();

		String sqlString = " select c.ccode, c.cname, s.semester from studying s "
		+ " inner join course c "
		+ " on c.code = s.ccode"
		+ "where spnr = " + sPnr;

		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		while (rs.next()){
			studying = new Studying(rs.getString(1), rs.getString(2), rs.getString(3));
			courseList.add(studying);
		}

		return courseList;


	}

	// Hittar betyget/resultat för anvigen student på angiven kurs // INTE FÄRDIG
	public ArrayList<Studied> findGradeFromCourse(String sPnr, String cCode) throws SQLException {
		ArrayList<Studied> studiedList = new ArrayList<Studied>();
		Connection con = ConnectDb.startConnection();
		sqlString = " spnr, ccode, grade, semester from studied where spnr = '" + sPnr + "' and ccode = '" + cCode+ "'";
		Statement stmt = con.createStatement();
		ResultSet rs = stmt.executeQuery(sqlString);
		while (rs.next()) {
			studied = new Studied(rs.getString(1), rs.getString(2), rs.getString(3), rs.getString(4));
			studiedList.add(studied);
		}
		return studiedList;

		// public populateStudent() throws SQLException{
		//
		// try{
		// Connection con = ConnectDb.startConnection();
		// Statement stmt = con.createStatement();
		// String sqlString ="select * from student";
		// ResultSet rs = stmt.executeQuery(sqlString);
		// ResultSetMetaData rsmd = rs.getMetaData();
		// int columns = rsmd.getColumnCount();
		// DefaultTableModel dtm = new DefaultTableModel();
		//
		// Vector columns_name = new Vector();
		// Vector data_rows = new Vector();
		//
		// for(int i = 1; i< columns; i++){
		// columns_name.addElement(rsmd.getColumnName(i));
		// }
		// dtm.setColumnIdentifiers(columns_name);
		//
		// while( rs.next()){
		// data_rows = new Vector();
		// for(int j = 1; j < columns;j++){
		// data_rows.addElement(rs.getString(j));
		//
		// }
		// dtm.addRow(data_rows);
		//
		// }table_Student.setModel(dtm);
		//
		// } catch(SQLException e) {
		// e.printStackTrace();
		// }
		//
		//
		// }



	}



	}


******************************************************************************************************

package model;

public class Studying {

	private String sPnr;
	private String cCode;
	private String semester;

	public Studying(String sPnr, String cCode, String semester) {
		setsPnr(sPnr);
		setcCode(cCode);
		setSemester(semester);

	}

	public String getsPnr() {
		return sPnr;
	}

	public void setsPnr(String sPnr) {
		this.sPnr = sPnr;
	}

	public String getcCode() {
		return cCode;
	}

	public void setcCode(String cCode) {
		this.cCode = cCode;
	}

	public String getSemester() {
		return semester;
	}

	public void setSemester(String semester) {
		this.semester = semester;
	}

}


******************************************************************************************************


package model;

public class Studied {
	private String sPnr;
	private String sName;
	private String grade;
	private String semester;

	public Studied(String sPnr, String sName, String grade, String semester) {
		setsPnr(sPnr);
		setsName(sName);
		setGrade(grade);
		setSemester(semester);

	}

	public String getGrade() {
		return grade;
	}

	public void setGrade(String grade) {
		this.grade = grade;
	}

	public String getsPnr() {
		return sPnr;
	}

	public void setsPnr(String sPnr) {
		this.sPnr = sPnr;
	}

	public String getsName() {
		return sName;
	}

	public void setsName(String sName) {
		this.sName = sName;
	}

	public String getSemester() {
		return semester;
	}

	public void setSemester(String semester) {
		this.semester = semester;
	}

}

******************************************************************************************************


package model;

public class Course {
	private String cCode;
	private String cName;
	private int cPoints;

	public Course(String cCode, String cName, int cPoints) {
		setcCode(cCode);
		setcName(cName);
		setcPoints(cPoints);
	}

	public String getcCode() {
		return cCode;
	}

	public void setcCode(String cCode) {
		this.cCode = cCode;
	}

	public String getcName() {
		return cName;
	}

	public void setcName(String cName) {
		this.cName = cName;
	}

	public int getcPoints() {
		return cPoints;
	}

	public void setcPoints(int cPoints) {
		this.cPoints = cPoints;
	}

}


******************************************************************************************************

package model;

import java.util.ArrayList;

public class Student {
	private String sPnr;
	private String sName;
	private String sAdress;


	public Student(String sPnr, String sName, String sAdress) {
		setsPnr(sPnr);
		setsName(sName);
		setsAdress(sAdress);

	}


	public String getsPnr() {
		return sPnr;
	}

	public void setsPnr(String sPnr) {
		this.sPnr = sPnr;
	}

	public String getsName() {
		return sName;
	}

	public void setsName(String sName) {
		this.sName = sName;
	}

	public String getsAdress() {
		return sAdress;
	}

	public void setsAdress(String address) {
		this.sAdress = address;
	}

}

******************************************************************************************************


package connector;

import java.sql.*;

public class ConnectDb {

	public static Connection startConnection() {
		Connection con = null;
		try {
			DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
		} catch (Exception e) {
			System.out.println("Kan inte database driver class: " + e);
		}
		try {
			con = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;" + "databaseName=Uppgift1;user=sa;password=kalle;");
			if (con == null)
				System.out.println("No driver found!");
			else
				System.out.println("Connection Established - SUCCESS!");

		} catch (Exception e) {
			System.out.println("Kaaaaaaaan inte hitta database driver class: " + e);
		}
		return con;

	}

}