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

/*****************************************************************************
 * 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 VenteCrud implements Crud<Vente>{
	private Connection connection;
	private Statement statement;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public VenteCrud(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
	}

	@Override
	public int create(Vente dataset) throws SQLException {
		//create une nouvelle row dans la BD
		String query = "INSERT INTO vente (vente_p_id,vente_jour,vente_heure,vente_prix,facture_fk_id,article_fk_id) "
					 + "VALUES('" + dataset.getVente_p_id() + "'," +
				              "'" + dataset.getVente_jour() + "'," +
				              "'" + dataset.getVente_heure() + "'," +
				              "'" + dataset.getVente_prix() + "'," +
				              "'" + dataset.getFacture_fk_id() + "'," +
				              "'" + dataset.getArticle_fk_id() + "')";
		
		statement.execute(query);

		//Retourner le id de se dernier
		return dataset.getVente_p_id();
	}

	@Override
	public Vente readById(int id) throws SQLException {
		String query = "SELECT * FROM vente WHERE vente_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return 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"));
	}
	
	public List<Vente> readByfacture_fk(int fk) throws SQLException {
		String query = "SELECT * FROM vente WHERE facture_fk_id='"+fk+"'";
		ResultSet rs = statement.executeQuery(query);
		List<Vente> lst = new ArrayList<>();
		
		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"));
			lst.add(tmp);
		}
		
		return lst;
	}

	public List<Vente> readByarticle_fk(int fk) throws SQLException {
		String query = "SELECT * FROM vente WHERE article_fk_id='"+fk+"'";
		ResultSet rs = statement.executeQuery(query);
		List<Vente> lst = new ArrayList<>();
		
		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"));
			lst.add(tmp);
		}
		
		return lst;
	}
	
	@Override
	public List<Vente> readAll() throws SQLException {
		List<Vente> lstVente = new ArrayList<Vente>();
		
		String query = "SELECT * FROM vente 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;
	}

	@Override
	public boolean update(Vente dataset) throws SQLException {
		String query = "UPDATE vente "
					 + "SET vente_heure='"+dataset.getVente_heure()+"', "+
                	   	   "vente_jour='"+dataset.getVente_jour()+"', "+
                		   "vente_prix='"+dataset.getVente_prix()+"', "+
                		   "facture_fk_id='"+dataset.getFacture_fk_id()+"', "+
                		   "article_fk_id='"+dataset.getArticle_fk_id()+"' "+
                	   "WHERE vente_p_id='"+dataset.getVente_p_id()+"'";
		
		statement.execute(query);

		return true;
	}

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

	@Override
	public int getNewSequenceId() throws SQLException {
		String querySequence = "UPDATE sequence " + 
                			   "SET sequence_dernierid=sequence_dernierid+1 " +
                			   "WHERE sequence_nomtable='vente'";
		
		statement.execute(querySequence);
		
		ResultSet rsid = statement.executeQuery("SELECT sequence_dernierid "
											  + "FROM sequence "
											  + "WHERE sequence_nomtable='vente'");
		
		rsid.next();
		
		return rsid.getInt(1);
	}
}