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.crud.vente.LocationChambreCrud;
import database.crud.vente.VenteArticleCrud;
import database.crud.vente.VenteCrud;
import database.dataset.Location_chambre;
import database.dataset.Vente;
import database.dataset.Vente_article;

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

	@Override
	public int create(Vente dataset) throws SQLException {
		if (dataset instanceof Vente_article)
			return venteArticleCrud.create((Vente_article)dataset);
		else if(dataset instanceof Location_chambre)
			return locationChambreCrud.create((Location_chambre)dataset);
		return venteCrud.create(dataset);
	}

	@Override
	public Vente readById(int id) throws SQLException {
		//add more meat
		return venteCrud.readById(id);
	}

	@Override
	public List<Vente> readAll() throws SQLException {
		//add more meat
		return venteCrud.readAll();
	}

	@Override
	public boolean update(Vente dataset) throws SQLException {
		if (dataset instanceof Vente_article)
			return venteArticleCrud.update((Vente_article)dataset);
		else if(dataset instanceof Location_chambre)
			return locationChambreCrud.update((Location_chambre)dataset);
		return venteCrud.update(dataset);
	}

	@Override
	public boolean destroy(Vente dataset) throws SQLException {
		if (dataset instanceof Vente_article)
			return venteArticleCrud.destroy((Vente_article)dataset);
		else if(dataset instanceof Location_chambre)
			return locationChambreCrud.destroy((Location_chambre)dataset);
		return venteCrud.destroy(dataset);
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		return venteCrud.getNewSequenceId();
	}
	
	/**
	 * @param jour
	 * @param facture_fk_id
	 * @param article_fk_id
	 * @return list de dataset qui resemble
	 * @throws SQLException
	 */
	public List<Vente> readAllLike(String vente_jour, 
			                       String facture_numero, 
			                       String article_numero, 
			                       String article_code, 
			                       String article_description,
			                       String facture_jour,
			                       String client_nom,
			                       String client_prenom) throws SQLException {
		List<Vente> lstVente = new ArrayList<Vente>();
		
		String query = "SELECT * FROM vente "+
					   "INNER JOIN article on article_p_id=article_fk_id "+
					   "INNER JOIN facture on facture_p_id=facture_fk_id "+
					   "INNER JOIN client on client_p_id=client_fk_id "+
					   "WHERE vente_jour LIKE '%"+vente_jour+"%' "+
					   "AND facture_p_id LIKE "+(facture_numero.equals("")?"'%%'":facture_numero) +" "+
					   "AND article_p_id LIKE "+(article_numero.equals("")?"'%%'":article_numero) +" "+
					   "AND article_code LIKE '%"+article_code+"%' " +
					   "AND article_description LIKE '%"+article_description+"%' "+
					   "AND facture_jour LIKE '%"+facture_jour+"%' " +
					   "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
			Vente tmp = new Vente(rs.getInt("vente_p_id"),
						          rs.getTime("vente_heure"),
						          rs.getDate("vente_jour"),
						          rs.getBigDecimal("vente_prix"),
						          rs.getInt("facture_fk_id"),
						          rs.getInt("article_fk_id"));
			lstVente.add(tmp);
		}
		
		return lstVente;
	}
}