DAL

package dal;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import model.Course;
import model.Student;

public class Dal {

	private PreparedStatement stmt;
	private Connection conn;

	public Connection getConnection() {
		try {
			DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

			Connection conn = DriverManager.getConnection(
					"jdbc:sqlserver://localhost:1433;" + "databaseName=uppgift1;user=sa;password=stest123;");
			return conn;
		} catch (Exception e) {
			e.printStackTrace();
		}

		return null;
	}

	public ArrayList<Student> getAllStudents() {

		ArrayList<Student> students = new ArrayList<Student>();
		try {
			conn = getConnection();
			String sql = "select * from student";

			stmt = conn.prepareStatement(sql);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String sadress = rs.getString("sadress");
				String stel = rs.getString("stel");

				Student student = new Student(spnr, sname, sadress, stel);
				students.add(student);

			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return students;
	}

	public Student getStudentBySpnr(String pnr) {

		Student student = null;

		try {
			conn = getConnection();

			String sql = "select * from student where spnr = ?";

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, pnr);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String sadress = rs.getString("sadress");
				String stel = rs.getString("stel");

				student = new Student(spnr, sname, sadress, stel);

			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			System.out.println(e);
		}

		return student;
	}

	public ArrayList<Student> getAllStudentsBySname(String name) {

		ArrayList<Student> students = new ArrayList<Student>();
		try {
			conn = getConnection();
			String sql = "select * from student where sname = ?";

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, name);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String sadress = rs.getString("sadress");
				String stel = rs.getString("stel");

				Student student = new Student(spnr, sname, sadress, stel);
				students.add(student);

			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return students;
	}

	public ArrayList<Student> getAllStudentsBySadress(String adress) {

		ArrayList<Student> students = new ArrayList<Student>();
		try {
			conn = getConnection();
			String sql = "select * from student where sadress = ?";

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, adress);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String sadress = rs.getString("sadress");
				String stel = rs.getString("stel");

				Student student = new Student(spnr, sname, sadress, stel);
				students.add(student);

			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return students;
	}

	public ArrayList<Student> getAllStudentsByStel(String tel) {

		ArrayList<Student> students = new ArrayList<Student>();
		try {
			conn = getConnection();
			String sql = "select * from student where stel = ?";

			stmt = conn.prepareStatement(sql);
			stmt.setString(1, tel);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String sadress = rs.getString("sadress");
				String stel = rs.getString("stel");

				Student student = new Student(spnr, sname, sadress, stel);
				students.add(student);

			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return students;
	}

	public ArrayList<Course> getAllCourses() {
		ArrayList<Course> courses = new ArrayList<Course>();
		try {
			conn = getConnection();
			String sql = "select * from course";

			stmt = conn.prepareStatement(sql);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String ccode = rs.getString("ccode");
				String cname = rs.getString("cname");
				int point = rs.getInt("point");

				Course course = new Course(ccode, cname, point);
				courses.add(course);

			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return courses;

	}

	public Course getCourseByCcode(String code) {

		Course course = null;

		try {
			conn = getConnection();

			String sql = "select * from course where ccode = ?";

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, code);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String ccode = rs.getString("ccode");
				String cname = rs.getString("cname");
				int point = rs.getInt("point");

				course = new Course(ccode, cname, point);

			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return course;
	}

	public ArrayList<Course> getAllCoursesByCname(String name) {
		ArrayList<Course> courses = new ArrayList<Course>();

		try {
			conn = getConnection();

			String sql = "select * from course where cname = ?";

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, name);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String ccode = rs.getString("ccode");
				String cname = rs.getString("cname");
				int point = rs.getInt("point");

				Course course = new Course(ccode, cname, point);
				courses.add(course);
			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return courses;
	}

	public ArrayList<Course> getAllCoursesByCpoint(int points) {
		ArrayList<Course> courses = new ArrayList<Course>();

		try {
			conn = getConnection();

			String sql = "select * from course where point = ?";

			stmt = conn.prepareStatement(sql);

			stmt.setInt(1, points);

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String ccode = rs.getString("ccode");
				String cname = rs.getString("cname");
				int point = rs.getInt("point");

				Course course = new Course(ccode, cname, point);
				courses.add(course);
			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return courses;
	}

	public ArrayList<Course> getAllStudiesBySpnr(Student s){

		ArrayList<Course> courses = new ArrayList<Course>();


		try {
			conn = getConnection();

			String sql = "select c.ccode, c.cname, c.point from studies s join course c on s.ccode = c.ccode where spnr = ?";

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, s.getSpnr());

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String ccode = rs.getString("ccode");
				String cname = rs.getString("cname");
				int point = rs.getInt("point");

				Course course = new Course(ccode, cname, point);
				courses.add(course);
			}
			stmt.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}

		return courses;

	}

	//FRÅGA OM!!!
//	public ArrayList<Course> getAllStudiedBySpnr(Student s){

//	}

	public void createStudent(Student s) {

		String sql = "insert into student values(?,?,?,?)";

		try {
			conn = getConnection();

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, s.getSpnr());
			stmt.setString(2, s.getSname());
			stmt.setString(3, s.getSadress());
			stmt.setString(4, s.getStel());

			stmt.executeUpdate();

			stmt.close();
			conn.close();

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

	}

	public void createCourse(Course c) {

		String sql = "insert into course values(?,?,?)";

		try {
			conn = getConnection();

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, c.getCcode());
			stmt.setString(2, c.getCname());
			stmt.setInt(3, c.getPoints());

			stmt.executeUpdate();

			stmt.close();
			conn.close();

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

	public void updateStudent(Student s) {

		String sql = "update student set sname = ?, sadress = ?, stel = ? where spnr = ?";

		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, s.getSname());
			stmt.setString(2, s.getSadress());
			stmt.setString(3, s.getStel());
			stmt.setString(4, s.getSpnr());

			stmt.executeUpdate();

			stmt.close();
			conn.close();

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

	}

	public void updateCourse(Course c) {

		String sql = "update course set cname = ?, point = ? where ccode = ?";

		try {
			conn = getConnection();
			stmt = conn.prepareStatement(sql);

			stmt.setString(1, c.getCname());
			stmt.setInt(2, c.getPoints());
			stmt.setString(3, c.getCcode());

			stmt.executeUpdate();

			stmt.close();
			conn.close();

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

	}

	public void registerStudies(Student s, Course c) {

		String sql = "insert into studies values (?,?)";

		try {

			conn = getConnection();

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, s.getSpnr());
			stmt.setString(2, c.getCcode());

			stmt.executeUpdate();

			stmt.close();
			conn.close();

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

	}

	public void registerStudied(Student s, Course c, String grade) {

		String sql = "insert into studied values (?,?,?)";

		try {

			conn = getConnection();

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, s.getSpnr());
			stmt.setString(2, c.getCcode());
			stmt.setString(3, grade);

			stmt.executeUpdate();

			stmt.close();
			conn.close();

			removeStudies(s, c);

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

	}

	public void removeStudies(Student s, Course c) {

		String sql = "delete from studies where spnr = ? and ccode = ?";

		try {
			conn = getConnection();

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, s.getSpnr());
			stmt.setString(2, c.getCcode());

			stmt.executeUpdate();

			stmt.close();
			conn.close();

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

	}

	public ArrayList<Student> getAllStudentsStudied(Course c) {

		ArrayList<Student> students = new ArrayList<Student>();
		try {

			conn = getConnection();
			String sql = "select s.spnr, s.sname, sd.grade from studied sd join student s on sd.spnr = s.spnr where ccode = ?";

			stmt = conn.prepareStatement(sql);

			stmt.setString(1, c.getCcode());

			ResultSet rs = stmt.executeQuery();

			while (rs.next()) {
				String spnr = rs.getString("spnr");
				String sname = rs.getString("sname");
				String grade = rs.getString("grade");
				String stel = null;

				Student student = new Student(spnr, sname, grade, stel);
				students.add(student);

			}
			stmt.close();
			conn.close();

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


}