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

/*****************************************************************************
 * Classe: ConstanteCrud
 * 
 * Objet ayant pour bout d'ex�cuter les instructions SQL en respectant les
 * op�rations de persistance des donn�es.
 * 
 * Liste des dependances
 * -Dataset constante
 * -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 ConstanteCrud implements Crud<Constante> {
	private Connection connection;
	private Statement statement;
	
	/**
	 * Constructeur
	 * @param connection
	 * @throws SQLException 
	 */
	public ConstanteCrud(Connection connection) throws SQLException {
		this.connection = connection;
		statement = this.connection.createStatement();
	}
	
	@Override
	public int create(Constante dataset) throws SQLException {
		//create une nouvelle row dans la BD	
		String query = "INSERT INTO constante (constante_p_id,constante_noTps,constante_noTvq,constante_neq,constante_messagefacture) "
					 + "VALUES('" + dataset.getconstante_p_id() + "'," +
					 		  "'" + dataset.getConstante_noTps() + "'," +
				              "'" + dataset.getConstante_noTvq() + "'," +
				              "'" + dataset.getConstante_neq() + "',"+ 
				              "'" + dataset.getconstante_messagefacture() + "')";
		
		statement.execute(query);
		
		//Retourner le id de se dernier
		return dataset.getconstante_p_id();
	}

	@Override
	public Constante readById(int id) throws SQLException {
		String query = "SELECT * FROM constante WHERE constante_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Constante(rs.getInt("constante_p_id"),
						   	 rs.getString("constante_noTps"),
						   	 rs.getString("constante_noTvq"),
						   	 rs.getString("constante_neq"),
						   	 rs.getString("constante_messagefacture"));
	}

	@Override
	public List<Constante> readAll() throws SQLException {
		List<Constante> lstConstante = new ArrayList<Constante>();
		
		String query = "SELECT * FROM constante LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Constante tmp = new Constante(rs.getInt("constante_p_id"),
				   	 					  rs.getString("constante_noTps"),
				   	 					  rs.getString("constante_noTvq"),
				   	 					  rs.getString("constante_neq"),
				   	 					  rs.getString("constante_messagefacture"));
			lstConstante.add(tmp);
		}
		
		return lstConstante;
	}

	@Override
	public boolean update(Constante dataset) throws SQLException {
		String query = "UPDATE constante "
					 + "SET constante_noTps='"+dataset.getConstante_noTps()+"', "+
		                   "constante_noTvq='"+dataset.getConstante_noTvq()+"', "+
		                   "constante_neq='"+dataset.getConstante_neq()+"', "+
		                   "constante_messagefacture='"+ dataset.getconstante_messagefacture() +"' "+
		               "WHERE constante_p_id='"+dataset.getconstante_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

	@Override
	public boolean destroy(Constante dataset) throws SQLException {
		String query = "DELETE FROM constante "
					 + "WHERE constante_p_id='"+dataset.getconstante_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

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