package database.crud.vente;

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

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

	@Override
	public int create(Location_chambre dataset) throws SQLException {
		//create une nouvelle row dans la BD
		String query = "INSERT INTO location_chambre (location_chambre_p_fk_id,reservation_fk_id) "
					 + "VALUES('" + dataset.getVente_p_id() + "'," +
				              "'" + dataset.getReservation_fk_id() + "')";

		venteCrud.create(dataset);
		try{
			statement.execute(query);
		}catch(SQLException e){
			venteCrud.destroy(dataset);
			throw e;
		}

		return dataset.getVente_p_id();
	}
	
	public boolean HasId(int id) throws SQLException
	{
		String query = "SELECT vente_p_id,  vente_heure, vente_jour, vente_prix, "
							+ "facture_fk_id, article_fk_id, location_chambre_p_fk_id, "
							+ "reservation_fk_id "
					 + "FROM location_chambre INNER JOIN vente on vente_p_id = location_chambre_p_fk_id "
					 + "WHERE location_chambre_p_fk_id="+id;
		
		ResultSet rs = statement.executeQuery(query);
		if(rs.next())
			return true;
		else
			return false;
	}
	
	@Override
	public Location_chambre readById(int id) throws SQLException {		
		String query = "SELECT vente_p_id,  vente_heure, vente_jour, vente_prix, "
					        + "facture_fk_id, article_fk_id, location_chambre_p_fk_id, "
					        + "reservation_fk_id "
					 + "FROM location_chambre INNER JOIN vente on vente_p_id = location_chambre_p_fk_id "
					 + "WHERE location_chambre_p_fk_id="+id;
		
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Location_chambre(rs.getInt("vente_p_id"),
						   			rs.getDate("vente_jour"),
						   			rs.getTime("vente_heure"),
						   			rs.getBigDecimal("vente_prix"),
						   			rs.getInt("facture_fk_id"),
						   			rs.getInt("article_fk_id"),
						   			rs.getInt("reservation_fk_id"));
	}

	public List<Location_chambre> readByreservation_fk(int fk) throws SQLException {
		String query = "SELECT * FROM location_chambre, vente WHERE location_chambre_p_fk_id=vente_p_id AND reservation_fk_id='"+fk+"'";
		ResultSet rs = statement.executeQuery(query);
		List<Location_chambre> lst = new ArrayList<>();
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
			Location_chambre tmp = new Location_chambre(rs.getInt("vente_p_id"),
			   											rs.getDate("vente_jour"),
			   											rs.getTime("vente_heure"),
			   											rs.getBigDecimal("vente_prix"),
			   											rs.getInt("facture_fk_id"),
			   											rs.getInt("article_fk_id"),
			   											rs.getInt("reservation_fk_id"));

			lst.add(tmp);
		}
		
		return lst;
	}
	
	@Override
	public List<Location_chambre> readAll() throws SQLException {
		List<Location_chambre> lstLocation_chambre = new ArrayList<Location_chambre>();
		
		String query = "SELECT v.vente_p_id,  v.vente_heure, v.vente_jour, v.vente_prix, "
					 		+ "v.facture_fk_id, v.article_fk_id, lc.location_chambre_p_fk_id, "
					 		+ "lc.reservation_fk_id "
					 + "FROM location_chambre AS lc, vente AS v LIMIT 0,100 "
					 + "WHERE lc.location_chambre_p_fk_id=v.vente_p_id";

		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
			Location_chambre tmp = new Location_chambre(rs.getInt("vente_p_id"),
			   											rs.getDate("vente_jour"),
			   											rs.getTime("vente_heure"),
			   											rs.getBigDecimal("vente_prix"),
			   											rs.getInt("facture_fk_id"),
			   											rs.getInt("article_fk_id"),
			   											rs.getInt("reservation_fk_id"));

			lstLocation_chambre.add(tmp);
		}
		
		return lstLocation_chambre;
	}

	@Override
	public boolean update(Location_chambre dataset) throws SQLException {
		String query = "UPDATE location_chambre " +
	                   "SET reservation_fk_id='"+dataset.getReservation_fk_id()+"' " +
				       "WHERE location_chambre_p_fk_id='"+ dataset.getVente_p_id() + "'";
		
		
		venteCrud.update(dataset);
		statement.execute(query);
		
		return true;
	}

	@Override
	public boolean destroy(Location_chambre dataset) throws SQLException {
		String query = "DELETE FROM location_chambre " +
                       "WHERE location_chambre_p_fk_id='"+dataset.getVente_p_id()+"'";
		
		statement.execute(query);
		venteCrud.destroy(dataset);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		return venteCrud.getNewSequenceId();
	}
}