package database.crud;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import database.Crud;
import database.dataset.Facture;

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

	@Override
	public int create(Facture dataset) throws SQLException {
		//create une nouvelle row dans la BD
		String query = "INSERT INTO facture (facture_p_id,facture_jour,facture_heure,facture_depense,facture_commentaire,client_fk_id,fermeture_fk_id,taxe_fk_id) "
				     + "VALUES('" + dataset.getFacture_p_id() + "'," +
				              "'" + dataset.getFacture_jour() + "'," +
				              "'" + dataset.getFacture_heure() + "'," +
				              "'" + dataset.getDepense() + "'," + 
				              "'" + dataset.getFacture_commentaire() + "'," + 
				              "'" + dataset.getClient_fk_id() + "'," + 
				              "'" + dataset.getFermeture_fk_id() + "',"+ 
				              "'" + dataset.getTaxe_fk_id() + "')";
		
		statement.execute(query);
		
		//Retourner le id de se dernier
		return dataset.getFacture_p_id();
	}

	@Override
	public Facture readById(int id) throws SQLException {
		String query = "SELECT * FROM facture WHERE facture_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new Facture(rs.getInt("facture_p_id"),
						   rs.getInt("client_fk_id"),
						   rs.getInt("fermeture_fk_id"),
						   rs.getString("facture_commentaire"),
						   rs.getInt("taxe_fk_id"),
						   rs.getDate("facture_jour"),
						   rs.getTime("facture_heure"),
						   rs.getBigDecimal("facture_depense"));
	}

	@Override
	public List<Facture> readAll() throws SQLException {
		List<Facture> lstFacture = new ArrayList<Facture>();
		
		String query = "SELECT * FROM facture LIMIT 0,100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Facture tmp = new Facture(rs.getInt("facture_p_id"),
									  rs.getInt("client_fk_id"),
									  rs.getInt("fermeture_fk_id"),
									  rs.getString("facture_commentaire"),
									  rs.getInt("taxe_fk_id"),
									  rs.getDate("facture_jour"),
									  rs.getTime("facture_heure"),
									  rs.getBigDecimal("facture_depense"));
			lstFacture.add(tmp);
		}
		
		return lstFacture;
	}

	@Override
	public boolean update(Facture dataset) throws SQLException {
		String query = "UPDATE facture "
					 + "SET client_fk_id='"+dataset.getClient_fk_id()+"', "+
                		   "fermeture_fk_id='"+dataset.getFermeture_fk_id()+"', "+
                		   "facture_commentaire='"+dataset.getFacture_commentaire()+"', "+
                		   "taxe_fk_id='"+dataset.getTaxe_fk_id()+"', "+
                		   "facture_jour='"+dataset.getFacture_jour()+"', "+
                		   "facture_heure='" + dataset.getFacture_heure() +"', "+
                		   "facture_depense='"+dataset.getDepense()+"' "+
                	   "WHERE facture_p_id='"+dataset.getFacture_p_id()+"'";
		
		statement.execute(query);

		return true;
	}

	@Override
	public boolean destroy(Facture dataset) throws SQLException {
		String query = "DELETE FROM facture "
				     + "WHERE facture_p_id='"+dataset.getFacture_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='facture'";
		
		statement.execute(querySequence);
		
		ResultSet rsid = statement.executeQuery("SELECT sequence_dernierid "
											  + "FROM sequence "
											  + "WHERE sequence_nomtable='facture'");
		
		rsid.next();
		
		return rsid.getInt(1);
	}
	
	/**
	 * @param id
	 * @return list de dataset selon la fk client
	 * @throws SQLException
	 */
	public List<Facture> readByFk_client(int id) throws SQLException {
		String query = "SELECT * FROM facture WHERE client_fk_id = '"+id+"'";
		List<Facture> lst = new ArrayList<Facture>();
		ResultSet rs = statement.executeQuery(query);
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Facture tmp = new Facture(rs.getInt("facture_p_id"),
						   rs.getInt("client_fk_id"),
						   rs.getInt("fermeture_fk_id"),
						   rs.getString("facture_commentaire"),
						   rs.getInt("taxe_fk_id"),
						   rs.getDate("facture_jour"),
						   rs.getTime("facture_heure"),
						   rs.getBigDecimal("facture_depense"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param id
	 * @return list de dataset selon la fk fermeture
	 * @throws SQLException
	 */
	public List<Facture> readByFk_fermeture(int id) throws SQLException {
		String query = "SELECT * FROM facture WHERE fermeture_fk_id = '"+id+"'";
		List<Facture> lst = new ArrayList<Facture>();
		ResultSet rs = statement.executeQuery(query);
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Facture tmp = new Facture(rs.getInt("facture_p_id"),
						   rs.getInt("client_fk_id"),
						   rs.getInt("fermeture_fk_id"),
						   rs.getString("facture_commentaire"),
						   rs.getInt("taxe_fk_id"),
						   rs.getDate("facture_jour"),
						   rs.getTime("facture_heure"),
						   rs.getBigDecimal("facture_depense"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param id
	 * @return list de dataset selon la fk taxe
	 * @throws SQLException
	 */
	public List<Facture> readByFk_taxe(int id) throws SQLException {
		String query = "SELECT * FROM facture WHERE taxe_fk_id = '"+id+"'";
		List<Facture> lst = new ArrayList<Facture>();
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
			//Construire le dataset � partir du resultset
				  Facture tmp = new Facture(rs.getInt("facture_p_id"),
						   rs.getInt("client_fk_id"),
						   rs.getInt("fermeture_fk_id"),
						   rs.getString("facture_commentaire"),
						   rs.getInt("taxe_fk_id"),
						   rs.getDate("facture_jour"),
						   rs.getTime("facture_heure"),
						   rs.getBigDecimal("facture_depense"));
				lst.add(tmp);
			}
		
		//Construire le dataset � partir du resultset
		return lst;
	}
	
	/**
	 * @param client_fk_id
	 * @param fermeture_fk_id
	 * @param commentaire
	 * @param taxe_fk_id
	 * @param jour
	 * @param heure
	 * @return list de dataset qui resemble
	 * @throws SQLException
	 */
	public List<Facture> readAllLike(String factureNumero, String clientNom, String clientPrenom, String fermetureNumero, String fermetureDate, String commentaire, String jour, String heure) throws SQLException {
		List<Facture> lstFacture = new ArrayList<Facture>();
		
		String query = "SELECT * FROM facture "+
					   "INNER JOIN client ON client_p_id=client_fk_id " +
					   "INNER JOIN fermeture ON fermeture_p_id=fermeture_fk_id "+
		               "WHERE facture_p_id LIKE '%%' "+
				       "AND facture_jour LIKE '%"+jour+"%' "+
				       "AND facture_heure LIKE '%"+heure+"%' "+
				       "AND facture_commentaire LIKE '%"+commentaire+"%' "+
				       "AND fermeture_jour LIKE '%"+fermetureDate+"%' "+
				       "AND fermeture_fk_id LIKE '%"+fermetureNumero+"%' " +
				       "AND client_nom LIKE '%"+clientNom+"%' "+
				       "AND client_prenom LIKE '%"+clientPrenom+"%' " +
							   " LIMIT 0,100";

		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		//Construire le dataset � partir du resultset
			Facture tmp = new Facture(rs.getInt("facture_p_id"),
									  rs.getInt("client_fk_id"),
									  rs.getInt("fermeture_fk_id"),
									  rs.getString("facture_commentaire"),
									  rs.getInt("taxe_fk_id"),
									  rs.getDate("facture_jour"),
									  rs.getTime("facture_heure"),
									  rs.getBigDecimal("facture_depense"));
			lstFacture.add(tmp);
		}
		
		return lstFacture;
	}
	
	public BigDecimal getSumVenteFromFactureSousTotal(Facture facture) throws SQLException{
		String query = "SELECT facture_fk_id, SUM(vente_prix*IFNULL(vente_article_quantite,1)) as total " +
		               "FROM vente LEFT JOIN vente_article ON vente_p_id=vente_article_p_fk_id " +
		               "where facture_fk_id="+facture.getFacture_p_id()+" GROUP BY facture_fk_id";
		ResultSet rs = statement.executeQuery(query);
		if (rs.next())
			return rs.getBigDecimal("total");
		return new BigDecimal("0");
	}
	
	public Map<String,BigDecimal> getSumVenteFromFacture(Facture facture) throws SQLException{
//		String query = "SELECT facture_fk_id, SUM(vente_prix*IFNULL(vente_article_quantite,1)) as total " +
//					   "FROM vente LEFT JOIN vente_article ON vente_p_id=vente_article_p_fk_id " +
//					   "where facture_fk_id="+facture.getFacture_p_id()+" GROUP BY facture_fk_id";
		
		String query = "SELECT SUM(soustotal) AS soustotal, SUM(tps*soustotal) AS tps, SUM(tvq*soustotal) AS tvq, SUM(herbergement) AS hebergement FROM( "+
                       "SELECT facture_p_id, vente_prix*quantite AS soustotal, tps, tvq, herbergement FROM( "+
                       "SELECT facture_p_id, vente_prix, "+
                       "CASE WHEN vente_article_quantite IS NULL THEN 1 ELSE vente_article_quantite END AS quantite, "+
                       "CASE WHEN article_taxable='V' THEN taxe_tps ELSE 0 END AS tps, "+
                       "CASE WHEN article_taxable='V' THEN taxe_tvq ELSE 0 END AS tvq, "+
                       "CASE WHEN location_chambre_p_fk_id IS NULL THEN 0 ELSE taxe_hebergement END AS herbergement "+
                       "FROM vente "+
                       "LEFT JOIN vente_article ON vente_article_p_fk_id=vente_p_id "+
                       "LEFT JOIN location_chambre ON location_chambre_p_fk_id=vente_p_id "+
                       "LEFT JOIN facture ON facture_fk_id=facture_p_id "+
                       "LEFT JOIN article ON article_fk_id=article_p_id "+
                       "LEFT JOIN taxe ON taxe_fk_id=taxe_p_id "+
                       "WHERE facture_p_id='"+facture.getFacture_p_id()+"' "+
                       ") AS sousventes) AS ventes GROUP BY facture_p_id";
		//System.out.println(query);
		
		Map<String,BigDecimal> dataset = new HashMap<>();
		ResultSet rs = statement.executeQuery(query);
		if (rs.next()){
			dataset.put("soustotal", rs.getBigDecimal("soustotal"));
			dataset.put("tps", rs.getBigDecimal("tps"));
			dataset.put("tvq", rs.getBigDecimal("tvq"));
			dataset.put("hebergement", rs.getBigDecimal("hebergement"));
		}
		return dataset;
	}
	
	public BigDecimal getSumEncaissementFromFacture(Facture facture, String modedepaiement) throws SQLException{
//		String query = "SELECT facture_fk_id, SUM(encaissement_montant) AS total " +
//	                   "FROM encaissement "+
//				       "WHERE facture_fk_id="+facture.getFacture_p_id()+" " +
//				       "GROUP BY facture_fk_id";
		String query = "SELECT modedepaiement_nom, SUM(encaissement_montant) AS total " + 
					   "FROM encaissement " +
					   "INNER JOIN modedepaiement ON modedepaiement_p_id=modedepaiement_fk_id " +
					   "WHERE facture_fk_id='"+facture.getFacture_p_id()+"' "+
					   "AND modedepaiement_nom LIKE '%"+modedepaiement+"%' ";	
	
		System.out.println(query);
		ResultSet rs = statement.executeQuery(query);
		if (rs.next())
			return rs.getBigDecimal("total");
		return new BigDecimal("0");
				
	}
}