package database.crud.denormalised;

import java.io.IOException;
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.List;

import database.Crud;
import database.dataset.Facture;
import database.dataview.view_client;
import database.dataview.view_encaissement;
import database.tools.DbConnection;

public class ViewEncaissementCrud implements Crud<view_encaissement>{
	private Connection connection;
	private Statement statement;

	/**
	 * @param connection
	 * @throws SQLException 
	 */
	public ViewEncaissementCrud(Connection connection) throws SQLException {
		super();
		this.connection = connection;
		this.statement = this.connection.createStatement();
	}

	@Override
	public int create(view_encaissement dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public view_encaissement readById(int id) throws SQLException {
		String query = "SELECT * FROM view_encaissement WHERE encaissement_p_id='"+id+"'";
		ResultSet rs = statement.executeQuery(query);
		rs.next();
		
		//Construire le dataset � partir du resultset
		return new view_encaissement(rs.getInt("encaissement_p_id"), 
				                     rs.getBigDecimal("encaissement_montant"), 
				                     rs.getInt("facture_fk_id"), 
				                     rs.getInt("modedepaiement_p_id"), 
				                     rs.getString("modedepaiement_nom"));
	}

	@Override
	public List<view_encaissement> readAll() throws SQLException {
		List<view_encaissement> lst = new ArrayList<>();
		
		String query = "SELECT * FROM view_encaissement LIMIT 0, 100";
		ResultSet rs = statement.executeQuery(query);
		
		while(rs.next()){
		    //Construire le dataset � partir du resultset
			view_encaissement tmp = new view_encaissement(rs.getInt("encaissement_p_id"), 
                    rs.getBigDecimal("encaissement_montant"), 
                    rs.getInt("facture_fk_id"), 
                    rs.getInt("modedepaiement_p_id"), 
                    rs.getString("modedepaiement_nom"));
			lst.add(tmp);
		}
		
		return lst;
	}

	@Override
	public boolean update(view_encaissement dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public boolean destroy(view_encaissement dataset) throws SQLException {
		throw new UnsupportedOperationException();
	}

	@Override
	public int getNewSequenceId() throws SQLException {
		throw new UnsupportedOperationException();
	}
	
	public BigDecimal getSumEncaissementFromFacture(int facture_p_id, String modedepaiement) throws SQLException{
		String query = "SELECT SUM(encaissement_montant) AS total " + 
					   "FROM view_encaissement " +
					   "WHERE facture_fk_id='"+facture_p_id+"' "+
					   "AND modedepaiement_nom LIKE '%"+modedepaiement+"%' ";	
	
		System.out.println(query);
		ResultSet rs = statement.executeQuery(query);
		if (rs.next() && rs.getObject("total")!=null)
			return rs.getBigDecimal("total");
		return new BigDecimal("0");
				
	}

	public static void main(String ... arg) throws ClassNotFoundException, IOException, SQLException{
		Connection connection = DbConnection.createConnection();
		ViewEncaissementCrud crud = new ViewEncaissementCrud(connection);
		for (view_encaissement v : crud.readAll())
			System.out.println(v.toString());
	}
	
}