package db;

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

public class MySQL {
	
	public Connection getConnection()
	{
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException | IllegalAccessException
				| ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		try {
			Connection con=DriverManager.getConnection("jdbc:mysql://141.79.128.121/bwLehrpool?user=root&password=!N4ye,04u.");
			return con;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
		
		
	}
	
	
	public ResultSet getHersteller(Connection con)
	{
		try {
			Statement stm=con.createStatement();
			return stm.executeQuery("SELECT distinct Hersteller FROM bwLehrpool.Anwendung order by Hersteller asc;");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
	}
	public ResultSet getSoftware(Connection con, String Hersteller)
	{
		
		try {
			Statement stm=con.createStatement();
			
			return stm.executeQuery("SELECT Produktname FROM bwLehrpool.Anwendung WHERE Hersteller like '"+Hersteller+"' order by Produktname asc;");
			
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		return null;
		
	}
	
	public ResultSet getActiveVLs(Connection con, int VId)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("SELECT Laborname FROM bwLehrpool.VM where IstAktiv=1 and Verantwortlicher_pk="+VId+";");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
				
	}
	
	public ResultSet getInactiveVLs(Connection con, int VId)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("SELECT Laborname FROM bwLehrpool.VM where IstAktiv=0 and Verantwortlicher_pk="+VId+";");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	public ResultSet getOSList(Connection con)
	{
		Statement stm;
		try {
			stm = con.createStatement();
			return stm.executeQuery("SELECT distinct OS FROM bwLehrpool.VM;");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

}