package DB;

import java.rmi.*;
import java.rmi.server.*;
import java.sql.*;
import java.util.*;


public class ConnextionClass extends UnicastRemoteObject implements iconnectionclass
{

	private String connectionString;
	private String user;
	private String password;
	private Connection con;
	private Vector listemploye;
	
	protected ConnextionClass() throws RemoteException {
		 connectionString="jdbc:mysql://127.0.0.1/miniprojet";
		 user="root";
		 password="";
		listemploye=new Vector();
	}
	
	
	public void toConnect () throws RemoteException	{
		
		try {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		con=DriverManager.getConnection(connectionString,user,password);	
		
		}
		catch(SQLException sqle) {
			System.err.println("Erreur lors de la connexion : " + sqle) ;
			}
			catch(ClassNotFoundException e) {
			System.err.println("Erreur de chargement du driver :" + e) ;
			}	
	
	}
	
	
	public void nbreparserv()throws RemoteException
	{	
		try{
			 Statement stmt=con.createStatement();
			 ResultSet rs=stmt.executeQuery("SELECT count(*),service.nom FROM employe,service where service.nums=employe.idserv Group By service.nom");
			 System.out.println("nom service:nombre emplye");
			 while(rs.next())
			 {
				 System.out.println(""+rs.getString(2)+":"+rs.getString(1));
				 
			 }
			 }
			
		catch(SQLException e){}
		
	}
	public int nbreparentreprise()throws RemoteException
	{	int x=0;
		try{
			 Statement stmt=con.createStatement();
			 ResultSet rs=stmt.executeQuery("SELECT count(*) FROM employe");
			 while(rs.next())
			 {
				x=rs.getInt(1); 
			 }
			 }
			
		catch(SQLException e){}
		return x;
	}
	
	public void salaireservice()throws RemoteException
	{	
		try{
			 Statement stmt=con.createStatement();
			 ResultSet rs=stmt.executeQuery("SELECT count(*),sum(salaire),service.nom FROM employe,service where service.nums=employe.idserv Group By service.nom");
			 System.out.println("nom service:rapport");
			 while(rs.next())
			 {
				 System.out.println(""+rs.getString(3)+":"+rs.getDouble(2)/rs.getInt(1));
				 
			 }
			 }
			
		catch(SQLException e){}
		
	}
	
	
	public int salaireentreprise()throws RemoteException
	{	int x=0;
		try{
			 Statement stmt=con.createStatement();
			 ResultSet rs=stmt.executeQuery("SELECT sum(salaire) FROM employe");
			 while(rs.next())
			 {
				x=rs.getInt(1); 
			 }
			 }
			
		catch(SQLException e){}
		return x;
	}
	
	
	
	
		
	public void close()throws RemoteException
	{
		try{
			 con.close();
		
			}
		catch( Exception e ) {
	      e.printStackTrace();
	    }
	
		}
	/*public static void main(String[] args)throws ClassNotFoundException, InstantiationException, SQLException, IllegalAccessException
	{
		ConnextionClass test=new ConnextionClass();
		test.toConnect();
		System.out.println("nombre d'emplye dans l'entreprise  "+test.nbreparentreprise());
		test.nbreparserv();
		System.out.println("totale des salaires dans l'entreprise  "+test.salaireentreprise());	
		
		System.out.println("salaire/nbremploye "+test.salaireentreprise()/test.nbreparentreprise());
		test.salaireservice();
		test.close();	
	}*/
	
	
	
}