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

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

	@Override
	public int create(Article dataset) throws SQLException {
		//create une nouvelle row dans la BD
		String query = "INSERT INTO article (article_p_id,article_code,article_description,article_taxable,article_actif,categorie_fk_id)"
					 + "VALUES('" + dataset.getarticle_p_id() + "'," 
				     +        "'" + dataset.getArticle_code() + "'," 
				     +		  "'" + dataset.getArticle_description() + "',"
				     +        "'" + (dataset.isArticle_taxable()?'V':'F') + "'," 
				     +        "'" + (dataset.isArticle_actif()?'V':'F') + "',"
				     +        "'" + dataset.getCategorie_fk_id() + "')";
		
		statement.execute(query);

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

	@Override
	public Article readById(int id) throws SQLException {
		String query = "SELECT * FROM article WHERE article_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Article(rs.getInt("article_p_id"),
						   rs.getString("article_code"),
						   rs.getString("article_description"),
						   rs.getString("article_taxable").equals("V"),
						   rs.getInt("categorie_fk_id"),
						   rs.getString("article_actif").equals("V"));
	}

	@Override
	public List<Article> readAll() throws SQLException {
		List<Article> lstArticle = new ArrayList<Article>();
		
		String query = "SELECT * FROM article LIMIT 0, 100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Article tmp = new Article(rs.getInt("article_p_id"),
					   				  rs.getString("article_code"),
					   				  rs.getString("article_description"),
					   				  rs.getString("article_taxable").equals("V"),
					   				  rs.getInt("categorie_fk_id"),
					   				  rs.getString("article_actif").equals("V"));
			lstArticle.add(tmp);
		}
		
		return lstArticle;
	}

	@Override
	public boolean update(Article dataset) throws SQLException {
		String query = "UPDATE article "
					 + "SET article_code='"+dataset.getArticle_code()+"', "
					 +     "article_description='"+dataset.getArticle_description()+"', "
				     +     "article_taxable='"+(dataset.isArticle_taxable()?'V':'F')+"', "
				     +     "categorie_fk_id='"+ dataset.getCategorie_fk_id() +"', "
				     +     "article_actif='"+(dataset.isArticle_actif()?'V':'F')+"' "
		             + "WHERE article_p_id='"+dataset.getarticle_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

	@Override
	public boolean destroy(Article dataset) throws SQLException {
		String query = "DELETE FROM article "
					 + "WHERE article_p_id='"+dataset.getarticle_p_id()+"'";
		
		statement.execute(query);
		
		return true;
	}

	@Override
	public int getNewSequenceId() throws SQLException {		
		//Mettre � jour la sequence, et obtenir le id de sa table
		String querySequence = "UPDATE sequence " + 
		                       "SET sequence_dernierid=sequence_dernierid+1 " +
				               "WHERE sequence_nomtable='article'";
		
		statement.execute(querySequence);
		
		ResultSet rsid = statement.executeQuery("SELECT sequence_dernierid "
											  + "FROM sequence "
											  + "WHERE sequence_nomtable='article'");
		rsid.next();
		
		return rsid.getInt(1);		
	}	
	
	/**
	 * @param id
	 * @return list de dataset slon la fk categorie
	 * @throws SQLException
	 */
	public List<Article> readByFk_categorie(int id) throws SQLException {
		String query = "SELECT * FROM article WHERE categorie_fk_id='"+id+"'";
		List<Article> lst = new ArrayList<Article>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
				Article tmp = new Article(rs.getInt("article_p_id"),
										   rs.getString("article_code"),
										   rs.getString("article_description"),
										   rs.getString("article_taxable").equals("V"),
										   rs.getInt("categorie_fk_id"),
										   rs.getString("article_actif").equals("V"));
				lst.add(tmp);
			}
		
		return lst;
	}
	
	/**
	 * @param code
	 * @param description
	 * @param taxable
	 * @param actif
	 * @param categorie_fk_id
	 * @return list de dataset qui resemble
	 * @throws SQLException
	 */
	public List<Article> readAllLike(String code, String description, String taxable, String actif, String categorie) throws SQLException {
		String query = "SELECT * FROM article, categorie_article WHERE "+
                        "article_code LIKE '%"+code+"%' AND " +
						"article_description LIKE '%"+description+"%' AND " +
						"article_taxable LIKE '"+(!taxable.equals("")?taxable:"%%")+"' AND "+
						"article_actif LIKE '"+(!actif.equals("")?actif:"%%")+"' AND " +
						"categorie_article_description LIKE '%" + categorie + "%' AND " +
						"categorie_fk_id=categorie_article_p_id" +
						   " LIMIT 0, 100";
		
		List<Article> lst = new ArrayList<Article>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
				Article tmp = new Article(rs.getInt("article_p_id"),
										   rs.getString("article_code"),
										   rs.getString("article_description"),
										   rs.getString("article_taxable").equals("V"),
										   rs.getInt("categorie_fk_id"),
										   rs.getString("article_actif").equals("V"));
				lst.add(tmp);
			}
		
		return lst;
	}
}