package controler.recherche;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import language.Language;
import tool.regEx.Adresse;
import view.vueFacture.VueFacture;
import view.vueRechercheReservation.VueRechercheReservation;
import commander.Commander;
import controler.Message;
import database.crud.AdresseCrud;
import database.crud.ArticleCrud;
import database.crud.ClientCrud;
import database.crud.FactureCrud;
import database.crud.GroupeCrud;
import database.crud.ReservationCrudGeneral;
import database.crud.StatusChambreCrud;
import database.crud.TaxeCrud;
import database.crud.TelephoneCrud;
import database.crud.VilleCrud;
import database.crud.reservation.ReservationCrud;
import database.crud.reservation.ReservationGroupeCrud;
import database.crud.reservation.ReservationIndividuCrud;
import database.crud.vente.LocationChambreCrud;
import database.crud.vente.VenteArticleCrud;
import database.crud.vente.VenteCrud;
import database.dataset.Article;
import database.dataset.Client;
import database.dataset.Facture;
import database.dataset.Groupe;
import database.dataset.Location_chambre;
import database.dataset.Reservation;
import database.dataset.Reservation_groupe;
import database.dataset.Reservation_individu;
import database.dataset.Taxe;
import database.dataset.Telephone;
import database.dataset.Vente;
import database.dataset.Vente_article;
import database.dataset.Ville;
import database.migration.SqlFactory;
import database.tools.IsolationLevel;

/**
 * @author Alfredo Toyo
 *
 */
public class ControlerRechercheReservation {
	public static final Message reveil = new Message(){
		public void trigger(Object caster) throws Exception {
			recherche.trigger(caster);
		}
	};
	
	public static final Message recherche = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRechercheReservation))
				return;
			VueRechercheReservation vue = (VueRechercheReservation)caster;
			Connection connection = Commander.getInstance().getConnectionBD();
			
			String sql = ("SELECT "+
					"COALESCE(`facture_fk_id`,'') AS FACTURE, "+
					"COALESCE(`reservation_debut`,'') AS DATEARRIVEE, "+
					"COALESCE(`reservation_fin`,'') AS DATEDEPART, "+
					"COALESCE(`client_nom`,'') AS NOM, "+
					"COALESCE(`client_prenom`,'') AS PRENOM, "+ 
					"COALESCE(`groupe_nom`,'') AS GROUPE, "+
					"COALESCE(`chambre_numero`,'') AS CHAMBRE, "+
					"COALESCE(`status_chambre_description`,'') AS STATUS, " +
					"COALESCE(`reservation_commentaire`,'') AS COMMENTAIRE "+
					"FROM `view_reservation` "+
					"LEFT JOIN view_vente ON  `reservation_fk_id`=`reservation_p_id` "+ 
					"WHERE `client_nom` LIKE '%[nom]%' "+
					"AND `client_prenom` LIKE '%[prenom]%' "+ 
					"AND `groupe_nom` LIKE '%[groupe]%' "+
					"AND (`reservation_debut` BETWEEN '[debut]' AND '[fin]' "+ 
					"OR `reservation_fin` BETWEEN '[debut]' AND '[fin]' "+
					"OR '[debut]' BETWEEN `reservation_debut` AND `reservation_fin` "+
					"OR '[fin]' BETWEEN `reservation_debut` AND `reservation_fin` ) " +
					"LIMIT 0,100")
			.replace("[nom]",vue.getTextFieldNomClient())
			.replace("[prenom]",vue.getTextFieldPrenomClient())
			.replace("[groupe]",vue.getTextFieldNomGroupe())
			.replace("[debut]",vue.getDateFieldDateArrivee())
			.replace("[fin]", vue.getDateFieldDateDepart());
			
			vue.clearReservation();
			for (Map<String,Object> row : SqlFactory.query(connection, sql))
			{
				vue.addLineToTableReservation(row.get("FACTURE").toString(), 
						                      row.get("DATEARRIVEE").toString(), 
						                      row.get("DATEDEPART").toString(), 
						                      row.get("NOM").toString(), 
						                      row.get("PRENOM").toString(), 
						                      row.get("GROUPE").toString(), 
						                      row.get("CHAMBRE").toString(), 
						                      row.get("STATUS").toString(), 
						                      row.get("COMMENTAIRE").toString());
			}
			
//			IsolationLevel isolation = new IsolationLevel(connection);
//			isolation.setSERIALIZABLE();
//
//			vue.clearReservation();
//		
//			ReservationCrudGeneral  reservationCrudGeneral  = new ReservationCrudGeneral(connection);
//			ClientCrud 				clientCrud              = new ClientCrud(connection);
//			GroupeCrud 				groupeCrud				= new GroupeCrud(connection);
//			StatusChambreCrud       statusChambreCrud 		= new StatusChambreCrud(connection);
//			ReservationGroupeCrud   reservationGroupeCrud   = new ReservationGroupeCrud(connection);
//			ReservationIndividuCrud reservationIndividuCrud = new ReservationIndividuCrud(connection);
//			FactureCrud 			factureCrud				= new FactureCrud(connection);
//			LocationChambreCrud		location_chambreCrud	= new LocationChambreCrud(connection);
//			
//			SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
//			java.util.Date parsedA = format.parse(vue.getDateFieldDateArrivee());
//			java.util.Date parsedD = format.parse(vue.getDateFieldDateDepart());
//			Date sqlA = new Date(parsedA.getTime());
//			Date sqlD = new Date(parsedD.getTime());
//			Date dateArrivee = sqlA;
//			Date dateDepart  = sqlD;
//			
//			String nomClient 	= vue.getTextFieldNomClient();
//			String prenomClient = vue.getTextFieldPrenomClient();
//			String nomGroupe 	= vue.getTextFieldNomGroupe();
//			
//			Client client = new Client(0, "", "", 0, "");
//			Groupe groupe = new Groupe(0, 0, "");
//
//			if(!nomGroupe.equals("")){
//				List<Reservation> reservations = reservationCrudGeneral.readAllLikeRechercheGroupe(dateArrivee, dateDepart, nomGroupe);
//				List<Client>      clients      = clientCrud.readAllLike(nomClient, prenomClient, "", "", "", "", "", "", "");
//				
//				for(Reservation reservation : reservations){
//					boolean temoin = false;
//					Reservation_groupe reservation_groupe = reservationGroupeCrud.readById(reservation.getReservation_p_id());
//					if(reservation_groupe != null){
//						groupe = groupeCrud.readById(reservation_groupe.getGroupe_fk_id());
//						for(Client aClient : clients){
//							if(aClient.getClient_p_id() == groupe.getClient_fk_id()){
//								client = aClient;
//								temoin = true;
//								break;
//							}
//						}
//					}
//
//					if(temoin){
//						List<Location_chambre> locations_chambre = location_chambreCrud.readByreservation_fk(reservation.getReservation_p_id());						
//						Facture facture = factureCrud.readById(locations_chambre.get(0).getFacture_fk_id());
//						
//						vue.addLineToTableReservation(facture.getFacture_p_id(),
//													  reservation.getReservation_debut(), 
//													  reservation.getReservation_fin(), 
//													  client.getClient_nom(),
//													  client.getClient_prenom(),
//													  groupe.getGroupe_nom(),
//													  reservation.getChambre_fk_id(), 
//													  statusChambreCrud.readById(reservation.getStatus_fk_id()).getStatus_description(),
//													  reservation.getReservation_commentaire());
//					}
//				}
//			}
//			else{
//				List<Reservation> reservations = reservationCrudGeneral.readAllBetween(vue.getDateFieldDateArrivee(), 
//						   															   vue.getDateFieldDateDepart());
//				
//				boolean rechercheParClient = false;
//				List<Client> clients = null;
//				
//				if((!nomClient.equals("") || !prenomClient.equals("")) && nomGroupe.equals("")){
//					rechercheParClient = true;
//					clients = clientCrud.readAllLike(nomClient, prenomClient, "", "", "", "", "", "", "");
//				}
//				else{
//					rechercheParClient = false;
//				}
//				
//				for(Reservation reservation : reservations){
//					List<Location_chambre> locations_chambre = location_chambreCrud.readByreservation_fk(reservation.getReservation_p_id());						
//					Facture facture = factureCrud.readById(locations_chambre.get(0).getFacture_fk_id());					
//					boolean temoin = false;
//					
//					Reservation_individu reservation_individu = reservationIndividuCrud.readById(reservation.getReservation_p_id());
//					if(reservation_individu != null){
//						if(rechercheParClient){
//							for(Client aClient : clients){
//								if(aClient.getClient_p_id() == reservation_individu.getClient_fk_id()){
//									client = aClient;
//									temoin = true;
//									break;
//								}
//							}
//						}
//						else{
//							client = clientCrud.readById(reservation_individu.getClient_fk_id());
//							temoin = true;
//						}						
//					}
//					
//					if(!temoin){
//						Reservation_groupe reservation_groupe = reservationGroupeCrud.readById(reservation.getReservation_p_id());
//						if(reservation_groupe != null){
//							groupe = groupeCrud.readById(reservation_groupe.getGroupe_fk_id());
//							if(rechercheParClient){
//								for(Client aClient : clients){
//									if(aClient.getClient_p_id() == groupe.getClient_fk_id()){
//										client = aClient;
//										break;
//									}
//								}
//							}
//							else{
//								client = clientCrud.readById(groupe.getClient_fk_id());
//							}
//						}
//					}
//					
//					vue.addLineToTableReservation(facture.getFacture_p_id(), 
//												  reservation.getReservation_debut(), 
//												  reservation.getReservation_fin(), 
//												  client.getClient_nom(),
//												  client.getClient_prenom(),
//												  groupe.getGroupe_nom(),
//												  reservation.getChambre_fk_id(), 
//												  statusChambreCrud.readById(reservation.getStatus_fk_id()).getStatus_description(),
//												  reservation.getReservation_commentaire());
//				}						
//			}			
		}
	};
	
	public static final Message modifier = new Message(){
		public void trigger(Object caster) throws Exception {
			if (!(caster instanceof VueRechercheReservation))
				return;
			
			VueRechercheReservation vue = (VueRechercheReservation)caster;
		
			if(!vue.hasRowSelected())
				return;
			
			Connection connection = Commander.getInstance().getConnectionBD();
			
			Language lang = Commander.getInstance().getLanguage();
			VueFacture vueDest = (VueFacture)Commander.getInstance().getVuePrincipale().setSelectedTab(lang.getPrincipaleReception(), lang.getReceptionFacture());
			
			vueDest.ClearTableReservation();
			vueDest.ClearTableVente();
			
			int facture_id = vue.getSelectedNumeroFacture(),
				j = 0,
				k = 0;
			
			BigDecimal tps,
					   tvq,
					   total = BigDecimal.ZERO;
			
			vueDest.setFacture_id(vue.getSelectedNumeroFacture());
			
			FactureCrud factureCrud = new FactureCrud(connection);
			VenteCrud venteCrud = new VenteCrud(connection);
			LocationChambreCrud locationChambreCrud = new LocationChambreCrud(connection);
			ReservationCrud reservationCrud = new ReservationCrud(connection);
			ArticleCrud articleCrud = new ArticleCrud(connection);
			TaxeCrud taxeCrud = new TaxeCrud(connection);
			VenteArticleCrud venteArticleCrud = new VenteArticleCrud(connection);
			ClientCrud clientCrud = new ClientCrud(connection);
			AdresseCrud adresseCrud = new AdresseCrud(connection);
			TelephoneCrud telephoneCrud = new TelephoneCrud(connection);
			VilleCrud villeCrud = new VilleCrud(connection);
			
			Facture facture = factureCrud.readById(facture_id);
			List<Vente> vente = venteCrud.readByfacture_fk(facture_id);
			Client client = clientCrud.readById(facture.getClient_fk_id());
			Adresse adresse = adresseCrud.readById(client.getAdresse_fk_id());
			Ville ville = villeCrud.readById(adresse.getVille_fk_id());
			List<Telephone> telephone = telephoneCrud.readByFk_client(client.getClient_p_id());
			
			for(int i=0;i < vente.size();i++)
			{				
				Article article = articleCrud.readById(vente.get(i).getArticle_fk_id());
				
				if(article.getCategorie_fk_id() == 1 || article.getCategorie_fk_id() == 2)
				{
					if(locationChambreCrud.HasId(vente.get(i).getVente_p_id()) == true)
					{
						Location_chambre locationChambre = locationChambreCrud.readById(vente.get(i).getVente_p_id());
						Reservation reservation = reservationCrud.readById(locationChambre.getReservation_fk_id());					
						
						vueDest.addRowTableReservation(new String[]{reservation.getReservation_debut().toString(), 
																reservation.getReservation_fin().toString(),
																String.valueOf(reservation.getChambre_fk_id()),
																article.getArticle_code()});
						vueDest.add_ReservationId(reservation.getReservation_p_id());
					}
					else
					{
						vueDest.addRowTableReservation(new String[]{"1981-01-01", "1981-01-01", "", ""});
					}	
					
					Taxe taxe = taxeCrud.readById(facture.getTaxe_fk_id());
					tps = ((vente.get(i).getVente_prix().multiply(taxe.getTaxe_tps())));
					tvq = ((vente.get(i).getVente_prix().add(tps)).multiply(taxe.getTaxe_tvq()));
					
					vueDest.setPrixValueAtTableReservation(j, vente.get(i).getVente_prix());
					vueDest.setTPSValueAtTableReservation(j, tps);
					vueDest.setTVQValueAtTableReservation(j, tvq);
					vueDest.setTotalValueAtTableReservation(j, (vente.get(i).getVente_prix().add(tps)).add(tvq));
					
					total = total.add((vente.get(i).getVente_prix().add(tps)).add(tvq));

					j++;
				}
				else
				{
					Taxe taxe = taxeCrud.readById(facture.getTaxe_fk_id());
					Vente_article venteArticle = venteArticleCrud.readById(vente.get(i).getVente_p_id());
					
					tps = ((vente.get(i).getVente_prix().multiply(new BigDecimal(venteArticle.getVente_article_quantite()))).multiply(taxe.getTaxe_tps()));
					tvq = ((vente.get(i).getVente_prix().multiply(new BigDecimal(venteArticle.getVente_article_quantite()))).add(tps)).multiply(taxe.getTaxe_tvq());
					
					vueDest.addRowTableVente(vente.get(i).getVente_jour().toString());
					vueDest.setArticleValueAtTableVente(k, article.getArticle_code());
					vueDest.setPromotionValueAtTableReservation(k, article.getArticle_code());
					vueDest.setPrixValueAtTableVente(k, vente.get(i).getVente_prix());
					vueDest.setNombreValueAtTableVente(k, venteArticle.getVente_article_quantite());
					vueDest.setTPSValueAtTableVente(k, tps);
					vueDest.setTVQValueAtTableVente(k, tvq);
					vueDest.setTotalValueAtTableVente(k, ((vente.get(i).getVente_prix().multiply(new BigDecimal(venteArticle.getVente_article_quantite()))).add(tps)).add(tvq));
					
					total = total.add(((vente.get(i).getVente_prix().multiply(new BigDecimal(venteArticle.getVente_article_quantite()))).add(tps)).add(tvq));
					
					k++;
				}
			}
			
			vueDest.setPrenom(client.getClient_prenom());
			vueDest.setTxtNom(client.getClient_nom());
			vueDest.setCodePostal(adresse.getAdresse_codepostal());
			
			for(int i=0;i<telephone.size();i++)
			{
				vueDest.setTelephone(telephone.get(i).getTelephone_numero());
			}
			
			vueDest.setTxtAdresse(adresse.getadresse_no_rue_app());
			vueDest.setVille(ville.getVille_nom());
			vueDest.setPays(ville.getVille_pays());
			vueDest.setProvince(ville.getVille_province());
			vueDest.setAreaParticularite(facture.getFacture_commentaire());
			vueDest.setAreaCommentaire(client.getClient_commentaire());
			vueDest.setTotal(total);
			
			vueDest.setEnabled(true);
		}
	};
}