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_article;

/*****************************************************************************
 * Classe: VenteArticleCrud
 * 
 * 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_article
 * -Connection
 * -Statement
 * 
 * Projet Orford
 *****************************************************************************
 * Modification: 
 * Nom: 
 * Date: 
 ****************************************************************************/

/**
 * @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 Robinson
 * completer rush
 */
public class VenteArticleCrud implements Crud<Vente_article> {
	private Connection connection;
	private Statement statement;
	private VenteCrud venteCrud;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public VenteArticleCrud(Connection connection) throws SQLException {
		this.connection = connection;
		this.statement = this.connection.createStatement();
		venteCrud = new VenteCrud(connection);
	}

	@Override
	public int create(Vente_article dataset) throws SQLException {
		//create une nouvelle row dans la BD	 	
		String query = "INSERT INTO vente_article (vente_article_p_fk_id,vente_article_quantite) "
					 + "VALUES('" + dataset.getVente_p_id() + "'," +
				              "'" + dataset.getVente_article_quantite() + "')";
		
		venteCrud.create(dataset);
		try{
			statement.execute(query);
		}catch(SQLException e){
			venteCrud.destroy(dataset);
			throw e;
		}
		
		//Retourner le id de se dernier
		return dataset.getVente_p_id();
	}

	@Override
	public Vente_article readById(int id) throws SQLException {
		String query = "SELECT v.vente_p_id, v.vente_jour, v.vente_heure, "
							+ "v.vente_prix, v.facture_fk_id, v.article_fk_id, "
							+ "va.vente_article_quantite "
					 + "FROM vente_article AS va, vente AS v "
					 + "WHERE va.vente_article_p_fk_id='"+id+"'";
		
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Vente_article(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"),
						   		 rs.getInt("vente_article_quantite"));
	}

	@Override
	public List<Vente_article> readAll() throws SQLException {
		List<Vente_article> lstVente_article = new ArrayList<Vente_article>();
		
		String query = "SELECT v.vente_p_id, v.vente_jour, v.vente_heure, "
							+ "v.vente_prix, v.facture_fk_id, v.article_fk_id, "
							+ "va.vente_article_quantite "
				     + "FROM vente_article AS va, vente AS v LIMIT 0,100 "
				     + "WHERE va.vente_article_p_fk_id=v.vente_p_id";
		
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){		
		//Construire le dataset � partir du resultset
			Vente_article tmp = new Vente_article(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"),
												  rs.getInt("vente_article_quantite"));
			lstVente_article.add(tmp);
		}
		
		return lstVente_article;
	}

	@Override
	public boolean update(Vente_article dataset) throws SQLException {
		String query = "UPDATE vente_article " + 
	                   "SET vente_article_quantite='"+dataset.getVente_article_quantite()+"' " +
	                   "WHERE vente_article_p_fk_id='"+dataset.getVente_p_id()+"'";

		venteCrud.update(dataset);
		statement.execute(query);
		
		return true;
	}

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

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