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

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

	@Override
	public int create(Taxe dataset) throws SQLException {
		//create une nouvelle row dans la BD
		String query = "INSERT INTO taxe (taxe_p_id,taxe_tps,taxe_tvq,taxe_hebergement,taxe_changement) "
					 + "VALUES('"  + dataset.getTaxe_p_id() + "'," +
				               "'" + dataset.getTaxe_tps() + "'," +
				               "'" + dataset.getTaxe_tvq() + "'," +
				               "'" + dataset.getTaxe_hebergement() + "'," +
				               "'" + dataset.getTaxe_changement() + "')";
	
		statement.execute(query);

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

	@Override
	public Taxe readById(int id) throws SQLException {
		String query = "SELECT * FROM taxe WHERE taxe_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Taxe(rs.getInt("taxe_p_id"),
						rs.getBigDecimal("taxe_tps"),
						rs.getBigDecimal("taxe_tvq"),
						rs.getBigDecimal("taxe_hebergement"),
						rs.getDate("taxe_changement"));
	}

	@Override
	public List<Taxe> readAll() throws SQLException {
		List<Taxe> lstTaxe = new ArrayList<Taxe>();
		
		String query = "SELECT * FROM taxe LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Taxe tmp = new Taxe(rs.getInt("taxe_p_id"),
								rs.getBigDecimal("taxe_tps"),
								rs.getBigDecimal("taxe_tvq"),
								rs.getBigDecimal("taxe_hebergement"),
								rs.getDate("taxe_changement"));
			lstTaxe.add(tmp);
		}
		
		return lstTaxe;
	}

	@Override
	public boolean update(Taxe dataset) throws SQLException {
		String query = "UPDATE taxe "
					 + "SET taxe_tps='"+dataset.getTaxe_tps()+"', "+
                		   "taxe_tvq='"+dataset.getTaxe_tvq()+"', "+
                		   "taxe_hebergement='"+dataset.getTaxe_hebergement()+"', "+
                		   "taxe_changement='"+dataset.getTaxe_changement()+"' "
                	 + "WHERE taxe_p_id='"+dataset.getTaxe_p_id()+"'";
	
		statement.execute(query);

		return true;
	}

	@Override
	public boolean destroy(Taxe dataset) throws SQLException {
		String query = "DELETE FROM taxe "
					 + "WHERE taxe_p_id='"+dataset.getTaxe_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='taxe'";
		
		statement.execute(querySequence);
		
		ResultSet rsid = statement.executeQuery("SELECT sequence_dernierid "
											  + "FROM sequence "
											  + "WHERE sequence_nomtable='taxe'");
		
		rsid.next();
		
		return rsid.getInt(1);
	}
	
	/**
	 * @return dataset le plus recent
	 * @throws SQLException
	 */
	public Taxe readLast() throws SQLException {
		String query = "SELECT *, MAX(taxe_changement) as maximum FROM taxe";
		ResultSet rs = statement.executeQuery(query);
		
		rs.next();
		//Construire le dataset � partir du resultset
		Taxe tmp = new Taxe(rs.getInt("taxe_p_id"),
							rs.getBigDecimal("taxe_tps"),
							rs.getBigDecimal("taxe_tvq"),
							rs.getBigDecimal("taxe_hebergement"),
							rs.getDate("taxe_changement"));
	
		return tmp;
	}
}