package controler.rapport;

import java.awt.Desktop;
import java.io.File;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
import java.util.Map;

import rapport.RapportFermeture;
import view.vueRapportFermeture.VueRapportFermeture;
import commander.Commander;
import controler.Message;
import database.crud.DepotCrud;
import database.crud.EmployeCrud;
import database.crud.FactureCrud;
import database.crud.FermetureCrud;
import database.crud.ModePaiementCrud;
import database.crud.denormalised.ViewEncaissementCrud;
import database.crud.denormalised.ViewFactureCrud;
import database.crud.denormalised.ViewVenteCrud;
import database.dataset.Depot;
import database.dataset.Employe;
import database.dataset.Facture;
import database.dataset.Fermeture;
import database.dataset.Modedepaiement;
import database.dataview.view_facture;
import database.migration.SqlFactory;

public class ControlerRapportFermeture {
	public static final Message reveil = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRapportFermeture))
				return;
			VueRapportFermeture vue = (VueRapportFermeture)caster;
			Connection connection = Commander.getInstance().getConnectionBD();
			
			vue.clear();

			int depot_id = (Integer)SqlFactory.queryOne(connection, "SELECT MAX(depot_p_id) FROM depot WHERE depot_date=(SELECT MAX(depot_date) FROM depot)");
			int fermeture_id = (Integer)SqlFactory.queryOne(connection, "SELECT MAX(fermeture_p_id) FROM fermeture WHERE fermeture_jour=(SELECT MAX(fermeture_jour) FROM fermeture)");
			
			for (Map<String,Object> fermeture : SqlFactory.query(connection, "SELECT * FROM view_fermeture ORDER BY fermeture_jour DESC LIMIT 100")){
				vue.addFermeture(Integer.valueOf(fermeture.get("fermeture_p_id").toString()), 
						         fermeture.get("montant").toString(), 
						         fermeture.get("fermeture_jour").toString());
			}
			
			for (Map<String,Object> facture : SqlFactory.query(connection, "SELECT * FROM view_facture_encaissement_vente_rapport WHERE fermeture_fk_id="+fermeture_id)){
				vue.addFacture(facture.get("facture_p_id").toString(), 
							   facture.get("facture_jour").toString(), 
						       facture.get("vente").toString(), 
						       facture.get("encaissement").toString());
			}

			String sqlSomPaiement = "SELECT sum(encaissement_montant) AS montant, modedepaiement_nom "+
                                    "FROM view_encaissement "+
	                                "INNER JOIN facture ON facture_p_id=facture_fk_id "+
                                    "WHERE fermeture_fk_id="+fermeture_id+" "+
	                                "GROUP BY modedepaiement_nom";

			for (Map<String, Object> row : SqlFactory.query(connection, sqlSomPaiement)){
				vue.addSommaire(row.get("modedepaiement_nom").toString(), row.get("montant").toString());
			}
		}
	};
	
	public static final Message imprimer = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRapportFermeture))
				return;
			VueRapportFermeture vue = (VueRapportFermeture)caster;
			Connection connection = Commander.getInstance().getConnectionBD();
			
			RapportFermeture rapport = new RapportFermeture();
			
			FactureCrud factureCrud = new FactureCrud(connection);
			FermetureCrud fermetureCrud = new FermetureCrud(connection);
			DepotCrud depotCrud = new DepotCrud(connection);
			ModePaiementCrud modedepaiementCrud = new ModePaiementCrud(connection);
			EmployeCrud employeCrud = new EmployeCrud(connection);
			ViewEncaissementCrud viewEncaissementCrud = new ViewEncaissementCrud(connection);
			ViewVenteCrud viewVenteCrud = new ViewVenteCrud(connection);
			
			Depot depot = depotCrud.getLastDepot();
			Fermeture fermeture;
			int selectedFermeture = vue.getSelectedFermeture();
			if (selectedFermeture==0){
				fermeture = fermetureCrud.getLastFermeture();
			}else{
				fermeture = fermetureCrud.readById(selectedFermeture);
			}
			Employe employe = employeCrud.readById(fermeture.getemploye_fk_id());
			
			List<Fermeture> fermetureLst = fermetureCrud.readByFk_depot(depot.getDepot_p_id());
			List<Facture> openFactureLst = factureCrud.readByFk_fermeture(fermeture.getfermeture_p_id());
			BigDecimal sumEncaissement = new BigDecimal("0");
			
			rapport.setNomEmploye(employe.getEmploye_prenom()+" "+employe.getEmploye_nom());
			rapport.setDateFermeture(fermeture.getFermeture_jour());
			rapport.setNumeroFermeture(fermeture.getfermeture_p_id());
			
			/*
			 * "SELECT facture_p_id, sous_total, tps, tvq, sous_total+tps+tvq+hebergement AS total, comptant, interac, cheque, amex, visa, mastercard, autres FROM view_facture_encaissement_rapport WHERE fermeture_fk_id="+fermetureID
			 * 
			 * */
			
			for (Facture facture : openFactureLst){
				Map<String,BigDecimal> charge = viewVenteCrud.getSumVenteFromFacture(facture.getFacture_p_id());
				
				rapport.add(facture.getFacture_p_id(), 
						    "", 
						    charge.get("soustotal"), 
						    charge.get("tps"), 
						    charge.get("tvq"), 
						    charge.get("soustotal").add(charge.get("tps")).add(charge.get("tvq")).add(charge.get("hebergement")), 
						    viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(),"ARGENT"), 
						    viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(),"INTERAC"), 
						    viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(),"CHEQUE"), 
						    viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(),"AMEX"), 
						    viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(),"VISA"), 
						    viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(),"MASTERCARD"), 
						    viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(),"AUTRE"));
			}
			
			
			for (Modedepaiement modedepaiement : modedepaiementCrud.readAll()){
				BigDecimal sommaire = new BigDecimal("0");
				for (Facture facture : openFactureLst){
					sommaire = sommaire.add(viewEncaissementCrud.getSumEncaissementFromFacture(facture.getFacture_p_id(), modedepaiement.getModedepaiement_nom()));
				}
				
			}
			
			rapport.generate();
			rapport.saveas("test.pdf");
		    File myFile = new File("test.pdf");
		    Desktop.getDesktop().open(myFile);
		}
	};
	
	public static final Message selectionner = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRapportFermeture))
				return;
			VueRapportFermeture vue = (VueRapportFermeture)caster;
			Connection connection = Commander.getInstance().getConnectionBD();

			int idFermeture = vue.getSelectedFermeture();
			
			vue.clearFacture();
			vue.clearSommaire();
			
			for (Map<String,Object> row : SqlFactory.query(connection, "SELECT * FROM view_facture_encaissement_vente_rapport WHERE fermeture_fk_id="+idFermeture)){
				vue.addFacture(row.get("facture_p_id").toString(), 
						       row.get("facture_jour").toString(), 
						       row.get("vente").toString(), 
						       row.get("encaissement").toString());
			}
			
			String sqlSomPaiement = "SELECT sum(encaissement_montant) AS montant, modedepaiement_nom "+
			                        "FROM view_encaissement "+
					                "INNER JOIN facture ON facture_p_id=facture_fk_id "+
			                        "WHERE fermeture_fk_id="+idFermeture+" "+
					                "GROUP BY modedepaiement_nom";
			
			for (Map<String, Object> row : SqlFactory.query(connection, sqlSomPaiement)){
				vue.addSommaire(row.get("modedepaiement_nom").toString(), row.get("montant").toString());
			}
		}
	};
	
	public static final Message annuler = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRapportFermeture))
				return;
			VueRapportFermeture vue = (VueRapportFermeture)caster;
			Connection connection = Commander.getInstance().getConnectionBD();
		}
	};
	
	public static final Message fermer = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRapportFermeture))
				return;
			VueRapportFermeture vue = (VueRapportFermeture)caster;
			Connection connection = Commander.getInstance().getConnectionBD();

			
		}
	};
}