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

import rapport.RapportDepot;
import view.vueRapportDepot.VueRapportDepot;
import commander.Commander;
import controler.Message;
import database.migration.SqlFactory;

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

			vue.clearFermeture();
			vue.clearDepot();
			vue.clearSommaire();
			
			Integer old_id = (Integer)SqlFactory.queryOne(connection, "SELECT MAX(depot_p_id) FROM depot");

			for(Map<String,Object> depot : SqlFactory.query(connection, "SELECT depot_p_id, depot_date FROM depot ORDER BY depot_p_id DESC LIMIT 100"))
			{
				vue.addDepot((Integer)depot.get("depot_p_id"), depot.get("depot_date").toString());
			}

		}
	};
	
	public static final Message imprimer = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRapportDepot))
				return;
			VueRapportDepot vue = (VueRapportDepot)caster;
			Connection connection = Commander.getInstance().getConnectionBD();


			
			RapportDepot rapportDepot = new RapportDepot();
			
			int id = vue.getSelectedDepot();
			rapportDepot.setNumeroDepot(id);
			
			String nomEmploye = (String)SqlFactory.queryOne(connection, "SELECT employe_nom FROM employe WHERE employe_p_id="+Commander.getInstance().getSessionProperty("employe_p_id"));
			rapportDepot.setNomEmploye(nomEmploye);
			
			rapportDepot.setDateDepot(new Date(System.currentTimeMillis()));

			String requete = "SELECT COALESCE(fermeture_p_id,0)  AS fermeture_p_id, "+
			                        "COALESCE(fermeture_jour,DATE(NOW())) AS fermeture_jour, "+
									"COALESCE(employe_nom,'')    AS employe_nom, "+
					       	        "COALESCE(SUM(total),0)      AS total, "+  
					         		"COALESCE(SUM(comptant),0)   AS comptant, "+
					         		"COALESCE(SUM(cheque),0)     AS cheque, "+
					         		"COALESCE(SUM(visa),0)       AS visa, "+
					         		"COALESCE(SUM(mastercard),0) AS mastercard, "+
					       			"COALESCE(SUM(amex),0)       AS amex, "+
					       			"COALESCE(SUM(interac),0)    AS interac, "+
					       			"COALESCE(SUM(autres),0)     AS autres "+
							 "FROM view_encaissement_modedepaiement "+
					       	 "INNER JOIN fermeture ON fermeture_fk_id=fermeture_p_id "+
							 "INNER JOIN employe ON employe_p_id=employe_fk_id "+
							 "WHERE depot_fk_id="+id+" "+
							 "GROUP BY fermeture_p_id "+
					       	 "ORDER BY fermeture_jour DESC";
			for (Map<String,Object> fermeture : SqlFactory.query(connection, requete)){
				rapportDepot.add(Integer.valueOf(fermeture.get("fermeture_p_id").toString()).intValue(), 
								 Date.valueOf(fermeture.get("fermeture_jour").toString()), 
						         (String)fermeture.get("employe_nom"), 
						         new BigDecimal(fermeture.get("comptant").toString()), 
						         new BigDecimal(fermeture.get("interac").toString()), 
						         new BigDecimal(fermeture.get("cheque").toString()), 
						         new BigDecimal(fermeture.get("amex").toString()), 
						         new BigDecimal(fermeture.get("visa").toString()), 
						         new BigDecimal(fermeture.get("mastercard").toString()), 
						         new BigDecimal(fermeture.get("autres").toString()));
			}
			
			rapportDepot.generate();

			rapportDepot.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 VueRapportDepot))
				return;
			VueRapportDepot vue = (VueRapportDepot)caster;
			Connection connection = Commander.getInstance().getConnectionBD();

			int depot_id = vue.getSelectedDepot();
			
			vue.clearFermeture();
			vue.clearSommaire();
			
			for (Map<String,Object> fermeture : SqlFactory.query(connection, "SELECT fermeture_p_id, fermeture_jour FROM fermeture WHERE depot_fk_id="+depot_id)){
				vue.addFermeture(fermeture.get("fermeture_p_id"), fermeture.get("fermeture_jour"));
			}
			
			String somRequete = "SELECT COALESCE(SUM(encaissement_montant),0) AS montant, "+
								       "modedepaiement_nom "+
								"FROM view_encaissement "+
								"INNER JOIN facture ON facture_p_id=facture_fk_id "+
								"INNER JOIN fermeture ON fermeture_p_id=fermeture_fk_id "+
								"WHERE depot_fk_id="+depot_id+" "+
								"GROUP BY modedepaiement_p_id";
			for(Map<String,Object> sommaire : SqlFactory.query(connection, somRequete)){
				vue.addToSommaire((String)sommaire.get("modedepaiement_nom"), 
								  new BigDecimal(sommaire.get("montant").toString()));
			}
		}
	};
	
	public static final Message annuler = new Message(){
		public void trigger(Object caster) throws Exception {
			reveil.trigger(caster);
		}
	};
	
	public static final Message fermer = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRapportDepot))
				return;
			VueRapportDepot vue = (VueRapportDepot)caster;
			Connection connection = Commander.getInstance().getConnectionBD();

			Integer old_id = (Integer)SqlFactory.queryOne(connection, "SELECT MAX(depot_p_id) FROM depot");
			Integer new_id = (Integer)SqlFactory.queryOne(connection, "SELECT get_next_sequence_id('depot')");
			SqlFactory.exec(connection, "INSERT INTO depot (depot_p_id,depot_date,depot_montant,employe_fk_id) VALUES("+new_id+",NOW(),0,"+Commander.getInstance().getSessionProperty("employe_p_id")+")");
			SqlFactory.exec(connection, "UPDATE depot SET depot_date=NOW() WHERE depot_p_id="+old_id);
			
			SqlFactory.exec(connection, "UPDATE fermeture SET depot_fk_id="+new_id+" WHERE depot_fk_id="+old_id);
			for (Integer id : vue.getSelectedFermeture())
			{
				SqlFactory.exec(connection, "UPDATE fermeture SET depot_fk_id="+old_id+" WHERE fermeture_p_id="+id);
			}
			
			reveil.trigger(caster);
		}
	};
}