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

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

	@Override
	public int create(Telephone dataset) throws SQLException {
		//create une nouvelle row dans la BD	
		String query = "INSERT INTO telephone (telephone_p_id,telephone_numero,telephone_type_fk_id,client_fk_id) "
					 + "VALUES('" + dataset.getTelephone_p_id() + "'," +
				      		  "'" + dataset.getTelephone_numero() + "'," +
				              "'" + dataset.getTelephone_type_fk_id() + "'," +
				              "'" + dataset.getClient_fk_id() + "')";
		
		statement.execute(query);
		
		//Retourner le id de se dernier
		return dataset.getTelephone_p_id();
	}

	@Override
	public Telephone readById(int id) throws SQLException {
		String query = "SELECT * FROM telephone WHERE telephone_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Telephone(rs.getInt("telephone_p_id"),
						     rs.getInt("telephone_type_fk_id"),
						     rs.getString("telephone_numero"),
						     rs.getInt("client_fk_id"));
	}

	@Override
	public List<Telephone> readAll() throws SQLException {
		List<Telephone> lstTelephone = new ArrayList<Telephone>();
		
		String query = "SELECT * FROM telephone LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Telephone tmp = new Telephone(rs.getInt("telephone_p_id"),
				     					  rs.getInt("telephone_type_fk_id"),
				     					  rs.getString("telephone_numero"),
				     					  rs.getInt("client_fk_id"));
			
			lstTelephone.add(tmp);
		}
		
		return lstTelephone;
	}

	@Override
	public boolean update(Telephone dataset) throws SQLException {
		String query = "UPDATE telephone "
					 + "SET telephone_type_fk_id='"+dataset.getTelephone_type_fk_id()+"', "+
                           "telephone_numero='"+dataset.getTelephone_numero()+"', "+
                		   "client_fk_id='"+dataset.getClient_fk_id()+"' "
                	 + "WHERE telephone_p_id='"+dataset.getTelephone_p_id()+"'";
	
		statement.execute(query);

		return true;
	}

	@Override
	public boolean destroy(Telephone dataset) throws SQLException {
		String query = "DELETE FROM telephone "
					 + "WHERE telephone_p_id='"+dataset.getTelephone_p_id()+"'";
	
		statement.execute(query);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		ResultSet rsid = statement.executeQuery("SELECT get_next_sequence_id('telephone')");
	
		rsid.next();
		
		return rsid.getInt(1);
	}
	

	/**
	 * @param id
	 * @return list de dataset selon la fk telephone type
	 * @throws SQLException
	 */
	public List<Telephone> readByFk_telephone_type(int id) throws SQLException {
		String query = "SELECT * FROM telephone WHERE telephone_type_fk_id='"+id+"'";

		List<Telephone> lst = new ArrayList<Telephone>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Telephone tmp = new Telephone(rs.getInt("telephone_p_id"),
											     rs.getInt("telephone_type_fk_id"),
											     rs.getString("telephone_numero"),
											     rs.getInt("client_fk_id"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param id
	 * @return list de dataset selon la fk client
	 * @throws SQLException
	 */
	public List<Telephone> readByFk_client(int id) throws SQLException {
		String query = "SELECT * FROM telephone WHERE client_fk_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		List<Telephone> lst = new ArrayList<Telephone>();
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Telephone tmp = new Telephone(rs.getInt("telephone_p_id"),
											     rs.getInt("telephone_type_fk_id"),
											     rs.getString("telephone_numero"),
											     rs.getInt("client_fk_id"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param telephone_type_fk_id
	 * @param numero
	 * @param client_fk_id
	 * @return list de dataset qui resemble
	 * @throws SQLException
	 */
	public List<Telephone> readAllLike(String telephone_type, 
			                           String numero, 
			                           String client_prenom, 
			                           String client_nom) throws SQLException {
		List<Telephone> lstTelephone = new ArrayList<Telephone>();
		
		String query = "SELECT * FROM telephone "+
					   "INNER JOIN telephone_type ON telephone_type_p_id = telephone_type_fk_id " +
					   "INNER JOIN client ON client_p_id = client_fk_id " +
		               "WHERE telephone_type_nom LIKE '%" + telephone_type + "%' " +
					   "AND telephone_numero LIKE '%" + numero + "%' " +
		               "AND client_nom LIKE '%" + client_nom + "%' " +
					   "AND client_prenom LIKE '%" + client_prenom + "%' " +
							   " LIMIT 0,100";
		
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Telephone tmp = new Telephone(rs.getInt("telephone_p_id"),
				     					  rs.getInt("telephone_type_fk_id"),
				     					  rs.getString("telephone_numero"),
				     					  rs.getInt("client_fk_id"));
			
			lstTelephone.add(tmp);
		}
		return lstTelephone;
	}
}