package controler.recherche;

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

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.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();
			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(client.getClient_commentaire());
			vueDest.setTotal(total);
		}
	};
}