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

/****************************************************************************
* Classe: ClientCrud
* 
* Objet ayant pour bout d'ex�cuter les instructions SQL en respectant les
* op�rations de persistance des donn�es.
* 
* Liste des dependances
* -Dataset client
* -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 ClientCrud implements Crud<Client>{
	private Connection connection;
	private Statement statement;

	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public ClientCrud(Connection connection) throws SQLException {
		this.connection = connection;
		statement = this.connection.createStatement();
	}
	
	@Override
	public int create(Client dataset) throws SQLException {		
		//create une nouvelle row dans la BD
		String query = "INSERT INTO client (client_p_id,client_nom,client_prenom,adresse_fk_id,client_commentaire) "
					 + "VALUES('"  + dataset.getClient_p_id() + "'," +
				               "'" + dataset.getClient_nom() + "'," +
				               "'" + dataset.getClient_prenom() + "'," +
				               "'" + dataset.getAdresse_fk_id() + "',"+ 
				               "'" + dataset.getClient_commentaire() + "')";
		
		statement.execute(query);
		
		//Retourner le id de se dernier
		return dataset.getClient_p_id();
	}

	@Override
	public Client readById(int id) throws SQLException {
		String query = "SELECT * FROM client WHERE client_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Client(rs.getInt("client_p_id"),
						   rs.getString("client_nom"),
						   rs.getString("client_prenom"),
						   rs.getInt("adresse_fk_id"),
						   rs.getString("client_commentaire"));
	}

	@Override
	public List<Client> readAll() throws SQLException {
		List<Client> lstClient = new ArrayList<Client>();
		
		String query = "SELECT * FROM client LIMIT 0,100";
		
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Client tmp = new Client(rs.getInt("client_p_id"),
					   				  rs.getString("client_nom"),
					   				  rs.getString("client_prenom"),
					   				  rs.getInt("adresse_fk_id"),
					   				  rs.getString("client_commentaire"));
			lstClient.add(tmp);
		}
		
		return lstClient;
	}

	@Override
	public boolean update(Client dataset) throws SQLException {
		String query = "UPDATE client "
					 + "SET client_nom='"+dataset.getClient_nom()+"', "+
		                   "client_prenom='"+dataset.getClient_prenom()+"', "+
		                   "adresse_fk_id='"+dataset.getAdresse_fk_id()+"', "+
		                   "client_commentaire='"+ dataset.getClient_commentaire() +"' "+
		               "WHERE client_p_id='"+dataset.getClient_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

	@Override
	public boolean destroy(Client dataset) throws SQLException {
		String query = "DELETE FROM client "
					 + "WHERE client_p_id='"+dataset.getClient_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		ResultSet rsid = statement.executeQuery("SELECT get_next_sequence_id('client')");
		
		rsid.next();
		
		return rsid.getInt(1);		
	}
	
	/**
	 * @param id
	 * @return list de dataset selon la fk adresse
	 * @throws SQLException
	 */
	public List<Client> readByFk_adresse(int id) throws SQLException {
		String query = "SELECT * FROM client WHERE adresse_fk_id='"+id+"'";
		List<Client> lst = new ArrayList<Client>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Client tmp = new Client(rs.getInt("client_p_id"),
										   rs.getString("client_nom"),
										   rs.getString("client_prenom"),
										   rs.getInt("adresse_fk_id"),
										   rs.getString("client_commentaire"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param nom
	 * @param prenom
	 * @param adresse_fk_id
	 * @param commentaire
	 * @return list de dataset qui resemble
	 * @throws SQLException
	 */
	public List<Client> readAllLike(String nom, String prenom, String adresse, String codepostal, String ville, String province, String pays, String commentaire, String telephone) throws SQLException {
		List<Client> lstClient = new ArrayList<Client>();
		
		String query = "SELECT client_p_id, client_nom, client_prenom, adresse_fk_id, client_commentaire FROM client "+
		               "INNER JOIN telephone ON client_fk_id = client_p_id "+
				       "INNER JOIN adresse ON adresse_p_id = adresse_fk_id "+
		               "INNER JOIN ville ON ville_p_id = ville_fk_id "+
					   "WHERE client_nom LIKE '%"+nom+"%' AND "+
					   "client_prenom LIKE '%"+prenom+"%' AND "+
					   "client_commentaire LIKE '%"+commentaire+"%' AND "+
					   "adresse_no_rue_app LIKE '%"+adresse+"%' AND " +
					   "adresse_codepostal LIKE '%"+codepostal+"%' AND " +
					   "ville_nom LIKE '%"+ville+"%' AND " +
					   "ville_province LIKE '%"+province+"%' AND "+
					   "ville_pays LIKE '%"+pays+"%' AND "+
					   "telephone_numero LIKE '%"+telephone+"%'" +
					   " GROUP BY client_p_id LIMIT 0,100";
		
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Client tmp = new Client(rs.getInt("client_p_id"),
					   				  rs.getString("client_nom"),
					   				  rs.getString("client_prenom"),
					   				  rs.getInt("adresse_fk_id"),
					   				  rs.getString("client_commentaire"));
			lstClient.add(tmp);
		}
		
		return lstClient;
	}
}