package database.crud;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;


import database.Crud;
import database.dataset.Employe;

/*****************************************************************************
 * Classe: EmployeCrud
 * 
 * Objet ayant pour bout d'ex�cuter les instructions SQL en respectant les
 * op�rations de persistance des donn�es.
 * 
 * Liste des dependances
 * -Dataset employe
 * -Connection
 * -Statement
 * 
 * Projet Orford
 ****************************************************************************
 * Modification: Ajout dela m�thode getNewSequenceId(), ajout de control du
 * commit pour instructions multiples pour la m�thode create().
 * Nom: Alfredo Toyo
 * Date: 2013-09-06
 ****************************************************************************/

/**
 * @author Alfredo Toyo
 * Objet ayant pour bout d'ex�cuter les instructions SQL en respectant les
 * op�rations de persistance des donn�es.
 */
public class EmployeCrud implements Crud<Employe> {
	private Connection connection;
	private Statement statement;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public EmployeCrud(Connection connection) throws SQLException {
		this.connection = connection;
		statement = this.connection.createStatement();
	}
	
	@Override
	public int create(Employe dataset) throws SQLException {
		//create une nouvelle row dans la BD
		String query = "INSERT INTO employe (employe_p_id,employe_prenom,employe_nom,utilisateur_fk_id,employe_commentaire) " +
					   "VALUES('"  + dataset.getemploye_p_id() + "'," +
		                      "'" + dataset.getEmploye_prenom() + "'," +
		                      "'" + dataset.getEmploye_nom() + "'," +
		                      "'" + dataset.getUtilisateur_fk_id() + "'," +
		                      "'" + dataset.getEmploye_commentaire() + "')";
		statement.execute(query);

		//Retourner le id de se dernier
		return dataset.getemploye_p_id();
	}

	@Override
	public Employe readById(int id) throws SQLException {
		String query = "SELECT * FROM employe WHERE employe_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Employe(rs.getInt("employe_p_id"),
						   rs.getString("employe_prenom"),
						   rs.getString("employe_nom"),
						   rs.getString("employe_commentaire"),
						   rs.getInt("utilisateur_fk_id"));
	}

	@Override
	public List<Employe> readAll() throws SQLException {
		List<Employe> lstEmploye = new ArrayList<Employe>();
		
		String query = "SELECT * FROM employe LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Employe tmp = new Employe(rs.getInt("employe_p_id"),
					   				  rs.getString("employe_prenom"),
					   				  rs.getString("employe_nom"),
					   				  rs.getString("employe_commentaire"),
					   				  rs.getInt("utilisateur_fk_id"));
			lstEmploye.add(tmp);
		}
		
		return lstEmploye;
	}

	@Override
	public boolean update(Employe dataset) throws SQLException {
		String query = "UPDATE employe " + 
	                   "SET employe_prenom='"+dataset.getEmploye_prenom()+"', "+
						   "employe_nom='"+dataset.getEmploye_nom()+"', "+
						   "employe_commentaire='"+dataset.getEmploye_commentaire()+"', "+
						   "utilisateur_fk_id='"+dataset.getUtilisateur_fk_id()+"' "+
					   "WHERE employe_p_id='"+dataset.getemploye_p_id()+"'";
		
		statement.execute(query);

		return true;
	}

	@Override
	public boolean destroy(Employe dataset) throws SQLException {
		String query = "DELETE FROM employe " + 
	                   "WHERE employe_p_id='"+dataset.getemploye_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		ResultSet rsid = statement.executeQuery("SELECT get_next_sequence_id('employe')");
		
		rsid.next();
		
		return rsid.getInt(1);
	}
	
	/**
	 * @param id
	 * @return list de dataset selon la fk utilisateur
	 * @throws SQLException
	 */
	public List<Employe> readByFk_utilisateur(int id) throws SQLException {
		String query = "SELECT * FROM employe WHERE utilisateur_fk_id='"+id+"'";
		List<Employe> lst = new ArrayList<Employe>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Employe tmp = new Employe(rs.getInt("employe_p_id"),
										   rs.getString("employe_prenom"),
										   rs.getString("employe_nom"),
										   rs.getString("employe_commentaire"),
										   rs.getInt("utilisateur_fk_id"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param prenom
	 * @param nom
	 * @param commentaire
	 * @param utilisateur_fk_id
	 * @return list de dataset qui resemble
	 * @throws SQLException
	 */
	public List<Employe> readAllLike(String prenom, String nom, String utilisateur) throws SQLException {
		List<Employe> lstEmploye = new ArrayList<Employe>();
		
		String query = "SELECT * FROM employe INNER JOIN utilisateur ON utilisateur_p_id=utilisateur_fk_id "+
		               "WHERE employe_prenom LIKE '%"+prenom+"%' AND "+
					   "employe_nom LIKE '%"+nom+"%' AND "+
					   "utilisateur_nom LIKE '%"+utilisateur+"%'" +
							   " LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Employe tmp = new Employe(rs.getInt("employe_p_id"),
					   				  rs.getString("employe_prenom"),
					   				  rs.getString("employe_nom"),
					   				  rs.getString("employe_commentaire"),
					   				  rs.getInt("utilisateur_fk_id"));
			lstEmploye.add(tmp);
		}
		
		return lstEmploye;
	}
}