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.Fermeture;

/*****************************************************************************
 * Classe: FermetureCrud
 * 
 * Objet ayant pour bout d'ex�cuter les instructions SQL en respectant les
 * op�rations de persistance des donn�es.
 * 
 * Liste des dependances
 * -Dataset fermeture
 * -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 FermetureCrud implements Crud<Fermeture> {
	private Connection connection;
	private Statement statement;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public FermetureCrud(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
	}

	@Override
	public int create(Fermeture dataset) throws SQLException {		
		//create une nouvelle row dans la BD	
		String query = "INSERT INTO fermeture (fermeture_p_id,fermeture_jour,employe_fk_id,depot_fk_id,fermeture_commentaire) "
					 + "VALUES('" + dataset.getfermeture_p_id() + "'," +
				              "'" + dataset.getFermeture_jour() + "'," +
				              "'" + dataset.getemploye_fk_id() + "'," +
				              "'" + dataset.getdepot_fk_id() + "'," + 
				              "'" + dataset.getFermeture_commentaire() + "')";
		
		statement.execute(query);
		
		//Retourner le id de se dernier
		return dataset.getfermeture_p_id();
	}

	@Override
	public Fermeture readById(int id) throws SQLException {
		String query = "SELECT * FROM fermeture "
					 + "WHERE fermeture_p_id='"+id+"'";
		
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Fermeture(rs.getInt("fermeture_p_id"),
						     rs.getDate("fermeture_jour"),
						     rs.getInt("employe_fk_id"),
						     rs.getInt("depot_fk_id"),
						     rs.getString("fermeture_commentaire"));
	}

	@Override
	public List<Fermeture> readAll() throws SQLException {
		List<Fermeture> lstFermeture = new ArrayList<Fermeture>();
		
		String query = "SELECT * FROM fermeture ORDER BY fermeture_jour DESC LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Fermeture tmp = new Fermeture(rs.getInt("fermeture_p_id"),
				     					  rs.getDate("fermeture_jour"),
				     					  rs.getInt("employe_fk_id"),
				     					  rs.getInt("depot_fk_id"),
				     					  rs.getString("fermeture_commentaire"));
			
			lstFermeture.add(tmp);
		}
		
		return lstFermeture;
	}
	
	

	@Override
	public boolean update(Fermeture dataset) throws SQLException {
		String query = "UPDATE fermeture "
					 + "SET fermeture_jour='"+dataset.getFermeture_jour()+"', "+
                		   "employe_fk_id='"+dataset.getemploye_fk_id()+"', "+
                		   "depot_fk_id='"+dataset.getdepot_fk_id()+"', "+
                		   "fermeture_commentaire='"+dataset.getFermeture_commentaire()+"' "+
                	   "WHERE fermeture_p_id='"+dataset.getfermeture_p_id()+"'";
		
		statement.execute(query);

		return true;
	}

	@Override
	public boolean destroy(Fermeture dataset) throws SQLException {
		String query = "DELETE FROM fermeture "
				     + "WHERE fermeture_p_id='"+dataset.getfermeture_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		ResultSet rsid = statement.executeQuery("SELECT get_next_sequence_id('fermeture')");
		
		rsid.next();
		
		return rsid.getInt(1);
	}
	
	/**
	 * @param id
	 * @return dataset selon la fk employe
	 * @throws SQLException
	 */
	public List<Fermeture> readByFk_employe(int id) throws SQLException {
		String query = "SELECT * FROM fermeture "
					 + "WHERE employe_fk_id='"+id+"' ORDER BY fermeture_jour DESC";
		
		List<Fermeture> lst = new ArrayList<Fermeture>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		
		//Construire le dataset � partir du resultset
			Fermeture tmp = new Fermeture(rs.getInt("fermeture_p_id"),
						     rs.getDate("fermeture_jour"),
						     rs.getInt("employe_fk_id"),
						     rs.getInt("depot_fk_id"),
						     rs.getString("fermeture_commentaire"));
			
			lst.add(tmp);
		}
		
		return lst;
	}
	
	/**
	 * @param id
	 * @return list de dataset selon la fk depot
	 * @throws SQLException
	 */
	public List<Fermeture> readByFk_depot(int id) throws SQLException {
		String query = "SELECT * FROM fermeture "
					 + "WHERE depot_fk_id='"+id+"' ORDER BY fermeture_jour DESC";
		List<Fermeture> lst = new ArrayList<Fermeture>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Fermeture tmp = new Fermeture(rs.getInt("fermeture_p_id"),
						     rs.getDate("fermeture_jour"),
						     rs.getInt("employe_fk_id"),
						     rs.getInt("depot_fk_id"),
						     rs.getString("fermeture_commentaire"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param jour
	 * @param employe_fk_id
	 * @param depot_fk_id
	 * @param commentaire
	 * @return list de dataset qui resemble
	 * @throws SQLException
	 */
	public List<Fermeture> readAllLike(String fermeture_numero, 
			                           String jour, 
			                           String employe_nom, 
			                           String employe_prenom, 
			                           String depotDate, 
			                           String numeroDepot, 
			                           String commentaire) throws SQLException {
		List<Fermeture> lstFermeture = new ArrayList<Fermeture>();
		
		String query = "SELECT * FROM fermeture " +
				       "INNER JOIN employe ON employe_p_id=employe_fk_id "+
				       "INNER JOIN depot ON depot_p_id=depot_fk_id " +
				       "WHERE fermeture_jour LIKE '%"+jour+"%' " +
				       "AND fermeture_p_id LIKE "+(fermeture_numero.equals("")?"'%%'":fermeture_numero) +" " +
				       "AND employe_nom LIKE '%"+employe_nom+"%' "+
				       "AND employe_prenom LIKE '%"+employe_prenom+"%' "+
				       "AND depot_date LIKE '%"+depotDate+"%' "+
				       "AND depot_p_id LIKE '%"+numeroDepot+"%' "+
				       "AND fermeture_commentaire LIKE '%"+commentaire+"%' " +
							   " ORDER BY fermeture_jour DESC LIMIT 0,100";
		
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Fermeture tmp = new Fermeture(rs.getInt("fermeture_p_id"),
				     					  rs.getDate("fermeture_jour"),
				     					  rs.getInt("employe_fk_id"),
				     					  rs.getInt("depot_fk_id"),
				     					  rs.getString("fermeture_commentaire"));
			
			lstFermeture.add(tmp);
		}
		
		return lstFermeture;
	}
	
	public Fermeture getLastFermeture() throws SQLException{
		String query = "SELECT MAX(fermeture_p_id) AS fermeture_p_id, fermeture_jour, employe_fk_id, depot_fk_id, fermeture_commentaire FROM fermeture";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		Fermeture tmp = new Fermeture(rs.getInt("fermeture_p_id"),
				  rs.getDate("fermeture_jour"),
				  rs.getInt("employe_fk_id"),
				  rs.getInt("depot_fk_id"),
				  rs.getString("fermeture_commentaire"));
		return tmp;
	}
}