DataTable IntfDalAgent.getDataTableAgent(string strRqst) { #region declaration DataTable dataTable = new DataTable(); crlAgent agent = null; IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implemntation #region initialisation du dataTable dataTable = new DataTable(); dataTable.Columns.Add("matriculeAgent", typeof(string)); dataTable.Columns.Add("agent", typeof(string)); dataTable.Columns.Add("adresse", typeof(string)); dataTable.Columns.Add("contact", typeof(string)); dataTable.Columns.Add("statut", typeof(string)); DataRow dr; #endregion this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(strRqst); if (this.reader != null) { if (this.reader.HasRows) { while (this.reader.Read()) { dr = dataTable.NewRow(); agent = serviceAgent.selectAgent(this.reader["matriculeAgent"].ToString()); dr["matriculeAgent"] = this.reader["matriculeAgent"].ToString(); dr["agent"] = this.reader["prenomAgent"].ToString() + " " + this.reader["nomAgent"].ToString(); dr["adresse"] = this.reader["adresseAgent"].ToString(); dr["contact"] = this.reader["telephoneAgent"].ToString() + " / " + this.reader["telephoneMobileAgent"].ToString(); if (agent.sessionCaisse != null) { dr["statut"] = "vert16.png"; } else { dr["statut"] = "rouge16.png"; } dataTable.Rows.Add(dr); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); #endregion return(dataTable); }
string IntfDalAgent.insertAgent(crlAgent Agent) { #region declaration IntfDalAgent serviceAgent = new ImplDalAgent(); int nombreInsertion = 0; string matriculeAgent = ""; #endregion #region implementation if (Agent != null) { if (Agent.agence != null) { Agent.matriculeAgent = serviceAgent.getMatriculeAgent(Agent.agence.SigleAgence); this.strCommande = "INSERT INTO `agent` (`matriculeAgent`,`numAgence`,`typeAgent`,`nomAgent`,`prenomAgent`,"; this.strCommande += "`dateNaissanceAgent`, `lieuNaissanceAgent`,`loginAgent`,`motDePasseAgent`,`cinAgent`,`adresseAgent`,"; this.strCommande += "`telephoneAgent`,`telephoneMobileAgent`,`imageAgent`,`situationFamilialeAgent`) VALUES ('" + Agent.matriculeAgent + "','" + Agent.numAgence + "',"; this.strCommande += "'" + Agent.typeAgent + "', '" + Agent.nomAgent + "', '" + Agent.prenomAgent + "', '" + Agent.dateNaissanceAgent.ToString("yyyy-MM-dd") + "',"; this.strCommande += "'" + Agent.lieuNaissanceAgent + "', '" + Agent.loginAgent + "', '" + Agent.motDePasseAgent + "', '" + Agent.cinAgent + "',"; this.strCommande += "'" + Agent.adresseAgent + "', '" + Agent.telephoneAgent + "', '" + Agent.telephoneMobileAgent + "','" + Agent.ImageAgent + "',"; this.strCommande += "'" + Agent.SituationFamilialeAgent + "')"; this.serviceConnectBase.openConnection(); nombreInsertion = this.serviceConnectBase.requete(this.strCommande); if (nombreInsertion == 1) { matriculeAgent = Agent.matriculeAgent; } this.serviceConnectBase.closeConnection(); } } #endregion return(matriculeAgent); }
crlVerification IntfDalVerification.selectVerification(string idVerification) { #region declaration IntfDalLicence serviceLicence = new ImplDalLicence(); IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalChauffeur serviceChauffeur = new ImplDalChauffeur(); IntfDalItineraire serviceItineraire = new ImplDalItineraire(); crlVerification verification = null; #endregion #region implementation if (idVerification != "") { this.strCommande = "SELECT * FROM `verification` WHERE (`idVerification`='" + idVerification + "')"; this.serviceConnectBase.openConnection(); reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { if (reader.Read()) { verification = new crlVerification(); verification.AVoireVP = reader["aVoireVP"].ToString(); verification.AVoireVT = reader["aVoireVT"].ToString(); verification.IdItineraire = reader["idItineraire"].ToString(); try { verification.DateVerification = Convert.ToDateTime(reader["dateVerification"].ToString()); } catch (Exception) { } verification.IdChauffeur = reader["idChauffeur"].ToString(); verification.IdVerification = reader["idVerification"].ToString(); verification.MatriculeAgent = reader["matriculeAgent"].ToString(); verification.NumLicence = reader["numLicence"].ToString(); verification.ObservationProfessionnelle = reader["observationProfessionnelle"].ToString(); verification.VerificationPapier = int.Parse(reader["verificationPapier"].ToString()); verification.VerificationTechnique = int.Parse(reader["verificationTechnique"].ToString()); verification.PlanDepart = int.Parse(reader["planDepart"].ToString()); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (verification != null) { verification.Licence = serviceLicence.selectLicence(verification.NumLicence); verification.Agent = serviceAgent.selectAgent(verification.MatriculeAgent); verification.Chauffeur = serviceChauffeur.selectChauffeur(verification.IdChauffeur); verification.Itineraire = serviceItineraire.selectItineraire(verification.IdItineraire); } } #endregion return(verification); }
crlCheque IntfDalCheque.selectCheque(string numCheque) { #region declaration crlCheque cheque = null; IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation if (numCheque != "") { this.strCommande = "SELECT * FROM `cheque` WHERE (`numCheque`='" + numCheque + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { cheque = new crlCheque(); cheque.NumCheque = this.reader["numCheque"].ToString(); cheque.NumerosCheque = this.reader["numerosCheque"].ToString(); try { cheque.DateCheque = Convert.ToDateTime(this.reader["dateCheque"].ToString()); } catch (Exception) { } try { cheque.MontantCheque = double.Parse(this.reader["montantCheque"].ToString()); } catch (Exception) { } cheque.MatriculeAgent = this.reader["matriculeAgent"].ToString(); cheque.AdresseTitulaireCheque = this.reader["adresseTitulaireCheque"].ToString(); cheque.Banque = this.reader["banque"].ToString(); cheque.NumCompte = this.reader["numCompte"].ToString(); cheque.TitulaireCheque = this.reader["titulaireCheque"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (cheque != null) { if (cheque.MatriculeAgent != "") { cheque.agent = serviceAgent.selectAgent(cheque.MatriculeAgent); } } } #endregion return(cheque); }
crlRecuAD IntfDalRecuAD.selectRecuAD(string numRecuAD) { #region declaration crlRecuAD RecuAD = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalPrelevement servicePrelevement = new ImplDalPrelevement(); #endregion #region implementation if (numRecuAD != "") { this.strCommande = "SELECT * FROM `recuad` WHERE (`numRecuAD`='" + numRecuAD + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { RecuAD = new crlRecuAD(); reader.Read(); RecuAD.NumRecuAD = reader["numRecuAD"].ToString(); RecuAD.MatriculeAgent = reader["matriculeAgent"].ToString(); RecuAD.NumPrelevement = reader["numPrelevement"].ToString(); RecuAD.Libele = reader["libele"].ToString(); RecuAD.Montant = reader["montant"].ToString(); RecuAD.NumFacture = reader["numFacture"].ToString(); try { RecuAD.Date = Convert.ToDateTime(reader["dateRecu"].ToString()); } catch (Exception) { } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (RecuAD != null) { if (RecuAD.MatriculeAgent != "") { RecuAD.agent = serviceAgent.selectAgent(RecuAD.MatriculeAgent); } if (RecuAD.NumPrelevement != "") { RecuAD.prelevement = servicePrelevement.selectPrelevement(RecuAD.NumPrelevement); } } } #endregion return(RecuAD); }
crlRecu IntfDalRecu.isValideRecu(string numRecu) { #region declaration crlRecu Recu = null; IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation if (numRecu != "") { this.strCommande = "SELECT recu.numRecu, recu.libele, recu.montant, recu.`date`,"; this.strCommande += " recu.modePaiement, recu.matriculeAgent FROM recu"; this.strCommande += " Left Join bagage ON bagage.numRecu = recu.numRecu"; this.strCommande += " Left Join commission ON commission.numRecu = recu.numRecu"; this.strCommande += " WHERE bagage.numRecu IS NULL AND commission.numRecu IS NULL AND"; this.strCommande += " recu.numRecu ='" + numRecu + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { Recu = new crlRecu(); reader.Read(); Recu.NumRecu = reader["numRecu"].ToString(); Recu.Libele = reader["libele"].ToString(); Recu.Montant = reader["montant"].ToString(); Recu.ModePaiement = reader["modePaiement"].ToString(); Recu.MatriculeAgent = reader["matriculeAgent"].ToString(); try { Recu.Date = Convert.ToDateTime(reader["date"].ToString()); } catch (Exception) { } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Recu != null) { if (Recu.MatriculeAgent != "") { Recu.agent = serviceAgent.selectAgent(Recu.MatriculeAgent); } } } #endregion return(Recu); }
crlPrelevement IntfDalPrelevement.selectPrelevement(string numPrelevement) { #region declaration crlPrelevement Prelevement = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalTypePrelevement serviceTypePrelevement = new ImplDalTypePrelevement(); IntfDalAutorisationDepart serviceAutorisationDepart = new ImplDalAutorisationDepart(); #endregion #region implementation if (numPrelevement != "") { this.strCommande = "SELECT * FROM `prelevement` WHERE (`numPrelevement`='" + numPrelevement + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { Prelevement = new crlPrelevement(); Prelevement.NumPrelevement = this.reader["numPrelevement"].ToString(); Prelevement.DatePrelevement = Convert.ToDateTime(this.reader["datePrelevement"].ToString()); Prelevement.MatriculeAgent = this.reader["matriculeAgent"].ToString(); Prelevement.MontantPrelevement = double.Parse(this.reader["montantPrelevement"].ToString()); Prelevement.NumAutorisationDepart = this.reader["numAutorisationDepart"].ToString(); Prelevement.TypePrelevement = this.reader["typePrelevement"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Prelevement != null) { if (Prelevement.MatriculeAgent != "") { Prelevement.agent = serviceAgent.selectAgent(Prelevement.MatriculeAgent); } if (Prelevement.NumAutorisationDepart != "") { Prelevement.autorisationDepart = serviceAutorisationDepart.selectAutorisationDepart(Prelevement.NumAutorisationDepart); } if (Prelevement.TypePrelevement != "") { Prelevement.objTypePrelevement = serviceTypePrelevement.selectTypePrelevement(Prelevement.TypePrelevement); } } } #endregion return(Prelevement); }
crlFacture IntfDalFacture.selectFacture(string numFacture) { #region declaration crlFacture Facture = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalFacture serviceFacture = new ImplDalFacture(); #endregion #region implementation if (numFacture != "") { this.strCommande = "SELECT * FROM `facture` WHERE (`numFacture`='" + numFacture + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { Facture = new crlFacture(); reader.Read(); Facture.NumFacture = reader["numFacture"].ToString(); Facture.Libele = reader["libele"].ToString(); Facture.Montant = reader["montant"].ToString(); Facture.MatriculeAgent = reader["matriculeAgent"].ToString(); try { Facture.DateFacturation = Convert.ToDateTime(reader["dateFacturation"].ToString()); } catch (Exception) { } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Facture != null) { if (Facture.MatriculeAgent != "") { Facture.agent = serviceAgent.selectAgent(Facture.MatriculeAgent); } Facture.autorisationDeparts = serviceFacture.selectADForFacture(Facture.NumFacture); } } #endregion return(Facture); }
crlRecu IntfDalRecu.selectRecu(string numRecu) { #region declaration crlRecu Recu = null; IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation if (numRecu != "") { this.strCommande = "SELECT * FROM `recu` WHERE (`numRecu`='" + numRecu + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { Recu = new crlRecu(); reader.Read(); Recu.NumRecu = reader["numRecu"].ToString(); Recu.Libele = reader["libele"].ToString(); Recu.Montant = reader["montant"].ToString(); Recu.ModePaiement = reader["modePaiement"].ToString(); Recu.MatriculeAgent = reader["matriculeAgent"].ToString(); try { Recu.Date = Convert.ToDateTime(reader["date"].ToString()); } catch (Exception) { } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Recu != null) { if (Recu.MatriculeAgent != "") { Recu.agent = serviceAgent.selectAgent(Recu.MatriculeAgent); } } } #endregion return(Recu); }
crlFicheBord IntfDalFicheBord.selectFicheBord(string numerosFB) { #region declaration crlFicheBord FicheBord = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfAutorisationVoyage serviceAutorisationVoyage = new ImplAutorisationVoyage(); IntfDalFicheBord serviceFicheBord = new ImplDalFicheBord(); IntfDalPlaceFB servicePlaceFB = new ImplDalPlaceFB(); #endregion #region implementation if (numerosFB != "") { this.strCommande = "SELECT * FROM `fichebord` WHERE (`numerosFB`='" + numerosFB + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { FicheBord = new crlFicheBord(); FicheBord.NumerosFB = reader["numerosFB"].ToString(); FicheBord.MatriculeAgent = reader["matriculeAgent"].ToString(); FicheBord.NumerosAV = reader["numerosAV"].ToString(); FicheBord.DateHeurDepart = Convert.ToDateTime(reader["dateHeurDepart"].ToString()); FicheBord.DateHeurPrevue = Convert.ToDateTime(reader["dateHeurPrevue"].ToString()); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (FicheBord != null) { FicheBord.agent = serviceAgent.selectAgent(FicheBord.MatriculeAgent); FicheBord.autorisationVoyage = serviceAutorisationVoyage.selectAutorisationVoyage(FicheBord.NumerosAV); FicheBord.commission = serviceFicheBord.getCommission(FicheBord.NumerosFB); //FicheBord.escorteVoyage = serviceFicheBord.getEscorteVoyage(FicheBord.NumerosFB); FicheBord.voyage = serviceFicheBord.getVoyage(FicheBord.NumerosFB); FicheBord.placeFB = servicePlaceFB.selectPlaceFB(FicheBord.NumerosFB); } } #endregion return(FicheBord); }
crlRecuDecaisser IntfDalRecuDecaisser.selectRecuDecaisser(string numRecuDecaisser) { #region declaration crlRecuDecaisser recuDecaisser = null; IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation if (numRecuDecaisser != "") { this.strCommande = "SELECT * FROM `recudecaisser` WHERE `numRecuDecaisser`='" + numRecuDecaisser + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { recuDecaisser = new crlRecuDecaisser(); try { recuDecaisser.DateRecuDecaisser = Convert.ToDateTime(this.reader["dateRecuDecaisser"].ToString()); } catch (Exception) { } recuDecaisser.LibelleRecuDecaisser = this.reader["libelleRecuDecaisser"].ToString(); recuDecaisser.MatriculeAgent = this.reader["matriculeAgent"].ToString(); try { recuDecaisser.MotantRecuDecaisser = double.Parse(this.reader["motantRecuDecaisser"].ToString()); } catch (Exception) { } recuDecaisser.NumRecuDecaisser = this.reader["numRecuDecaisser"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (recuDecaisser.MatriculeAgent != "") { recuDecaisser.agent = serviceAgent.selectAgent(recuDecaisser.MatriculeAgent); } } #endregion return(recuDecaisser); }
void IntfDalAgent.insertToGridAgentListe(GridView gridView, string param, string paramLike, string valueLike, string numAgence) { #region declaration IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation this.strCommande = "SELECT agent.matriculeAgent, agent.typeAgent, agent.numAgence, agent.nomAgent,"; this.strCommande += " agent.prenomAgent, agent.dateNaissanceAgent, agent.lieuNaissanceAgent, agent.loginAgent,"; this.strCommande += " agent.motDePasseAgent, agent.cinAgent, agent.adresseAgent, agent.telephoneAgent,"; this.strCommande += " agent.telephoneMobileAgent, agent.imageAgent FROM agent"; this.strCommande += " WHERE agent.numAgence LIKE '%" + numAgence + "%' AND"; this.strCommande += " " + paramLike + " LIKE '%" + valueLike + "%'"; this.strCommande += " ORDER BY " + param + " ASC"; gridView.DataSource = serviceAgent.getDataTableAgentListe(this.strCommande); gridView.DataBind(); #endregion }
crlAutorisationVoyage IntfAutorisationVoyage.selectAutorisationVoyage(string numerosAV) { #region declaration IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalVerification serviceVerification = new ImplDalVerification(); crlAutorisationVoyage AutorisationVoyage = null; #endregion #region implementation if (numerosAV != "") { this.strCommande = "SELECT * FROM `autorisationvoyage` WHERE (`numerosAV`='" + numerosAV + "')"; this.serviceConnectBase.openConnection(); reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { if (reader.Read()) { AutorisationVoyage = new crlAutorisationVoyage(); AutorisationVoyage.NumerosAV = reader["numerosAV"].ToString(); AutorisationVoyage.MatriculeAgent = reader["matriculeAgent"].ToString(); AutorisationVoyage.IdVerification = reader["idVerification"].ToString(); AutorisationVoyage.DatePrevueDepart = Convert.ToDateTime(reader["datePrevueDepart"].ToString()); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (AutorisationVoyage != null) { AutorisationVoyage.Verification = serviceVerification.selectVerification(AutorisationVoyage.IdVerification); AutorisationVoyage.Agent = serviceAgent.selectAgent(AutorisationVoyage.MatriculeAgent); } } #endregion return(AutorisationVoyage); }
crlBillet IntfDalBillet.isValide(string numBillet, string idItineraire) { #region declaration crlBillet Billet = null; IntfDalTrajet serviceTrajet = new ImplDalTrajet(); IntfDalIndividu serviceIndividu = new ImplDalIndividu(); IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalCalculCategorieBillet serviceCalculCategorieBillet = new ImplDalCalculCategorieBillet(); IntfDalCalculReductionBillet serviceCalculReductionBillet = new ImplDalCalculReductionBillet(); #endregion #region implementation if (numBillet != "") { this.strCommande = "SELECT billet.numBillet, billet.dateDeValidite, billet.numTrajet,"; this.strCommande += " billet.modePaiement, billet.numIndividu, billet.matriculeAgent, billet.prixBillet,"; this.strCommande += " billet.numCalculCategorieBillet, billet.numCalculReductionBillet, billet.dateBillet,"; this.strCommande += " billet.numDureeAbonnement, billet.numVoyageAbonnement, billet.numBilletCommande FROM billet"; this.strCommande += " Left Join voyage ON voyage.numBillet = billet.numBillet"; this.strCommande += " Inner Join trajet ON trajet.numTrajet = billet.numTrajet"; this.strCommande += " Inner Join associationtrajetitineraire ON associationtrajetitineraire.numTrajet = trajet.numTrajet"; this.strCommande += " WHERE voyage.numBillet IS NULL AND"; this.strCommande += " billet.numBillet = '" + numBillet + "' AND"; this.strCommande += " billet.dateDeValidite >= '" + DateTime.Now.ToString("yyyyMMdd") + "' AND"; this.strCommande += " associationtrajetitineraire.idItineraire = '" + idItineraire + "'"; this.serviceConnectBase.openConnection(); reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { if (reader.Read()) { Billet = new crlBillet(); Billet.NumBillet = reader["numBillet"].ToString(); Billet.DateDeValidite = Convert.ToDateTime(reader["dateDeValidite"].ToString()); Billet.NumTrajet = reader["numTrajet"].ToString(); Billet.ModePaiement = reader["modePaiement"].ToString(); Billet.NumIndividu = reader["numIndividu"].ToString(); Billet.MatriculeAgent = reader["matriculeAgent"].ToString(); Billet.PrixBillet = reader["prixBillet"].ToString(); Billet.NumCalculCategorieBillet = reader["numCalculCategorieBillet"].ToString(); Billet.NumCalculReductionBillet = reader["numCalculReductionBillet"].ToString(); Billet.DateBillet = Convert.ToDateTime(reader["dateBillet"].ToString()); Billet.NumDureeAbonnement = reader["numDureeAbonnement"].ToString(); Billet.NumVoyageAbonnement = reader["numVoyageAbonnement"].ToString(); Billet.NumBilletCommande = reader["numBilletCommande"].ToString(); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Billet != null) { if (Billet.NumTrajet != "") { Billet.trajet = serviceTrajet.selectTrajet(Billet.NumTrajet); } if (Billet.NumIndividu != "") { Billet.individu = serviceIndividu.selectIndividu(Billet.NumIndividu); } if (Billet.MatriculeAgent != "") { Billet.agent = serviceAgent.selectAgent(Billet.MatriculeAgent); } if (Billet.NumCalculCategorieBillet != "") { Billet.calculCategorieBillet = serviceCalculCategorieBillet.selectCalculCategorieBillet(Billet.NumCalculCategorieBillet); } if (Billet.NumCalculReductionBillet != "") { Billet.calculReductionBillet = serviceCalculReductionBillet.selectCalculReductionBillet(Billet.NumCalculReductionBillet); } } } #endregion return(Billet); }
List <crlRecuAD> IntfDalRecuAD.selectRecuADProprietaireResteNonNull(string numProprietaire) { #region declaration crlRecuAD tempRecuAD = null; List <crlRecuAD> recuADs = null; IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation if (numProprietaire != "") { this.strCommande = "SELECT (recuad.numRecuAD) AS numR, recuad.numPrelevement, recuad.matriculeAgent, recuad.libele,"; this.strCommande += " recuad.montant, recuad.dateRecu FROM recuad"; this.strCommande += " Inner Join asociationautorisationdepartrecu ON asociationautorisationdepartrecu.numRecuAD = recuad.numRecuAD"; this.strCommande += " Inner Join autorisationdepart ON autorisationdepart.numAutorisationDepart = asociationautorisationdepartrecu.numAutorisationDepart"; this.strCommande += " Inner Join fichebord ON fichebord.numerosFB = autorisationdepart.numerosFB"; this.strCommande += " Inner Join autorisationvoyage ON autorisationvoyage.numerosAV = fichebord.numerosAV"; this.strCommande += " Inner Join verification ON verification.idVerification = autorisationvoyage.idVerification"; this.strCommande += " Inner Join licence ON licence.numLicence = verification.numLicence"; this.strCommande += " Inner Join vehicule ON vehicule.numVehicule = licence.numVehicule"; this.strCommande += " WHERE autorisationdepart.resteRegle > 0 AND"; this.strCommande += " vehicule.numProprietaire = '" + numProprietaire + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { recuADs = new List <crlRecuAD>(); while (reader.Read()) { tempRecuAD = new crlRecuAD(); tempRecuAD.NumRecuAD = reader["numR"].ToString(); tempRecuAD.MatriculeAgent = reader["matriculeAgent"].ToString(); tempRecuAD.NumPrelevement = reader["numPrelevement"].ToString(); tempRecuAD.Libele = reader["libele"].ToString(); tempRecuAD.Montant = reader["montant"].ToString(); try { tempRecuAD.Date = Convert.ToDateTime(reader["dateRecu"].ToString()); } catch (Exception) { } recuADs.Add(tempRecuAD); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (recuADs != null) { for (int i = 0; i < recuADs.Count; i++) { if (recuADs[i].MatriculeAgent != "") { recuADs[i].agent = serviceAgent.selectAgent(recuADs[i].MatriculeAgent); } } } } #endregion return(recuADs); }
crlAutorisationDepart IntfDalAutorisationDepart.selectAutorisationDepart(string numAutorisationDepart) { #region declaration crlAutorisationDepart AutorisationDepart = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalFacture serviceFacture = new ImplDalFacture(); IntfDalFicheBord serviceFicheBord = new ImplDalFicheBord(); #endregion #region implementation if (numAutorisationDepart != "") { this.strCommande = "SELECT * FROM `autorisationdepart` WHERE (`numAutorisationDepart`='" + numAutorisationDepart + "')"; this.serviceConnectBase.openConnection(); reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { if (reader.Read()) { AutorisationDepart = new crlAutorisationDepart(); AutorisationDepart.NumAutorisationDepart = reader["numAutorisationDepart"].ToString(); AutorisationDepart.NumerosFB = reader["numerosFB"].ToString(); AutorisationDepart.MatriculeAgent = reader["matriculeAgent"].ToString(); try { AutorisationDepart.DateAD = Convert.ToDateTime(this.reader["dateAD"].ToString()); } catch (Exception) { } try { AutorisationDepart.RecetteTotale = double.Parse(this.reader["recetteTotale"].ToString()); AutorisationDepart.ResteRegle = double.Parse(this.reader["resteRegle"].ToString()); } catch (Exception) { } } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (AutorisationDepart != null) { if (AutorisationDepart.MatriculeAgent != "") { AutorisationDepart.agent = serviceAgent.selectAgent(AutorisationDepart.MatriculeAgent); } if (AutorisationDepart.NumerosFB != "") { AutorisationDepart.ficheBord = serviceFicheBord.selectFicheBord(AutorisationDepart.NumerosFB); } } } #endregion return(AutorisationDepart); }
crlDureeAbonnement IntfDalDureeAbonnement.selectDureeAbonnement(string numDureeAbonnement) { #region declaration crlDureeAbonnement dureeAbonnement = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalTrajet serviceTrajet = new ImplDalTrajet(); IntfDalZone serviceZone = new ImplDalZone(); IntfDalAbonnement serviceAbonnement = new ImplDalAbonnement(); IntfDalCalculCategorieBillet serviceCalculCategorieBillet = new ImplDalCalculCategorieBillet(); IntfDalCalculReductionBillet serviceCalculReductionBillet = new ImplDalCalculReductionBillet(); #endregion #region implementation if (numDureeAbonnement != "") { this.strCommande = "SELECT * FROM `dureeabonnement` WHERE (`numDureeAbonnement`='" + numDureeAbonnement + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { dureeAbonnement = new crlDureeAbonnement(); try { dureeAbonnement.DateDureeAbonnement = Convert.ToDateTime(this.reader["dateDureeAbonnement"].ToString()); } catch (Exception) { } dureeAbonnement.MatriculeAgent = this.reader["matriculeAgent"].ToString(); dureeAbonnement.NumAbonnement = this.reader["numAbonnement"].ToString(); dureeAbonnement.NumDureeAbonnement = this.reader["numDureeAbonnement"].ToString(); dureeAbonnement.NumTrajet = this.reader["numTrajet"].ToString(); try { dureeAbonnement.PrixTotal = double.Parse(this.reader["prixTotal"].ToString()); } catch (Exception) { } try { dureeAbonnement.PrixUnitaire = double.Parse(this.reader["prixUnitaire"].ToString()); } catch (Exception) { } try { dureeAbonnement.ValideAu = Convert.ToDateTime(this.reader["valideAu"].ToString()); } catch (Exception) { } try { dureeAbonnement.ValideDu = Convert.ToDateTime(this.reader["valideDu"].ToString()); } catch (Exception) { } dureeAbonnement.Zone = this.reader["zone"].ToString(); dureeAbonnement.NumCalculCategorieBillet = this.reader["numCalculCategorieBillet"].ToString(); dureeAbonnement.NumCalculReductionBillet = this.reader["numCalculReductionBillet"].ToString(); dureeAbonnement.ModePaiement = this.reader["modePaiement"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (dureeAbonnement != null) { if (dureeAbonnement.MatriculeAgent != "") { dureeAbonnement.agent = serviceAgent.selectAgent(dureeAbonnement.MatriculeAgent); } if (dureeAbonnement.NumAbonnement != "") { dureeAbonnement.abonnement = serviceAbonnement.selectAbonnement(dureeAbonnement.NumAbonnement); } if (dureeAbonnement.NumTrajet != "") { dureeAbonnement.trajet = serviceTrajet.selectTrajet(dureeAbonnement.NumTrajet); } if (dureeAbonnement.Zone != "") { dureeAbonnement.zoneObj = serviceZone.selectZone(dureeAbonnement.Zone); } if (dureeAbonnement.NumCalculCategorieBillet != "") { dureeAbonnement.calculCategorieBillet = serviceCalculCategorieBillet.selectCalculCategorieBillet(dureeAbonnement.NumCalculCategorieBillet); } if (dureeAbonnement.NumCalculReductionBillet != "") { dureeAbonnement.calculReductionBillet = serviceCalculReductionBillet.selectCalculReductionBillet(dureeAbonnement.NumCalculReductionBillet); } } } #endregion return(dureeAbonnement); }
crlAbonnement IntfDalAbonnement.selectAbonnement(string numAbonnement) { #region declaration IntfDalIndividu serviceIndividu = new ImplDalIndividu(); IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalOrganisme serviceOrganisme = new ImplDalOrganisme(); IntfDalSociete serviceSociete = new ImplDalSociete(); crlAbonnement Abonnement = null; #endregion #region implementation if (numAbonnement != "") { this.strCommande = "SELECT * FROM `abonnement` WHERE `numAbonnement`='" + numAbonnement + "' OR"; this.strCommande += " `numIndividu`='" + numAbonnement + "' OR `numSociete`='" + numAbonnement + "' OR"; this.strCommande += " `numOrganisme`='" + numAbonnement + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { Abonnement = new crlAbonnement(); if (this.reader.Read()) { Abonnement.NumAbonnement = this.reader["numAbonnement"].ToString(); Abonnement.NumIndividu = this.reader["numIndividu"].ToString(); Abonnement.MatriculeAgent = this.reader["matriculeAgent"].ToString(); Abonnement.NumOrganisme = this.reader["numOrganisme"].ToString(); Abonnement.NumSociete = this.reader["numSociete"].ToString(); try { Abonnement.DateAbonnement = Convert.ToDateTime(this.reader["dateAbonnement"].ToString()); } catch (Exception) { } Abonnement.ImageAbonner = this.reader["imageAbonner"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Abonnement != null) { if (Abonnement.NumIndividu != "") { Abonnement.individu = serviceIndividu.selectIndividu(Abonnement.NumIndividu); } if (Abonnement.MatriculeAgent != "") { Abonnement.agent = serviceAgent.selectAgent(Abonnement.MatriculeAgent); } if (Abonnement.NumOrganisme != "") { Abonnement.organisme = serviceOrganisme.selectOrganisme(Abonnement.NumOrganisme); } if (Abonnement.NumSociete != "") { Abonnement.societe = serviceSociete.selectSociete(Abonnement.NumSociete); } } } #endregion return(Abonnement); }
List <crlCommission> IntfDalFicheBord.getCommission(string numerosFB) { #region declaration List <crlCommission> Commissions = new List <crlCommission>(); crlCommission tempCommission = null; IntfDalReceptionnaire servicePersonne = new ImplDalReceptionnaire(); IntfDalRecu serviceRecu = new ImplDalRecu(); IntfDalClient serviceClient = new ImplDalClient(); IntfDalDesignationCommission serviceDesignationCommission = new ImplDalDesignationCommission(); IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation if (numerosFB != "") { this.strCommande = "SELECT * FROM commission"; this.strCommande += " Inner Join associationfichebordcommission ON associationfichebordcommission.idCommission = commission.idCommission"; this.strCommande += " WHERE (associationfichebordcommission.numerosFB ='" + numerosFB + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { while (this.reader.Read()) { tempCommission = new crlCommission(); tempCommission.IdCommission = reader["idCommission"].ToString(); tempCommission.Destination = reader["destination"].ToString(); tempCommission.Poids = reader["poids"].ToString(); try { tempCommission.Nombre = int.Parse(reader["nombre"].ToString()); } catch (Exception) { } try { tempCommission.IsRecu = int.Parse(reader["isRecu"].ToString()); } catch (Exception) { } tempCommission.PieceJustificatif = reader["pieceJustificatif"].ToString(); tempCommission.FraisEnvoi = reader["fraisEnvoi"].ToString(); tempCommission.NumExpediteur = reader["numExpediteur"].ToString(); tempCommission.NumRecepteur = reader["numRecepteur"].ToString(); tempCommission.NumDesignation = reader["numDesignation"].ToString(); tempCommission.TypeCommission = reader["typeCommission"].ToString(); tempCommission.NumTrajet = reader["numTrajet"].ToString(); try { tempCommission.DateCommission = Convert.ToDateTime(reader["dateCommission"].ToString()); } catch (Exception) { } try { tempCommission.DateLivraison = Convert.ToDateTime(reader["dateLivraison"].ToString()); } catch (Exception) { } tempCommission.MatriculeAgent = reader["matriculeAgent"].ToString(); tempCommission.MatriculeAgentDelivreur = reader["matriculeAgentDelivreur"].ToString(); tempCommission.ModePaiement = reader["modePaiement"].ToString(); Commissions.Add(tempCommission); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); for (int i = 0; i < Commissions.Count; i++) { if (Commissions[i] != null) { Commissions[i].expediteur = serviceClient.selectClient(Commissions[i].NumExpediteur); Commissions[i].recepteur = servicePersonne.selectPersonne(Commissions[i].NumRecepteur); Commissions[i].designationCommission = serviceDesignationCommission.selectDesignationCommission(Commissions[i].NumDesignation); Commissions[i].typeCommssionObjet = new crlTypeCommssion(); Commissions[i].typeCommssionObjet.TypeCommission = Commissions[i].TypeCommission; Commissions[i].agent = serviceAgent.selectAgent(Commissions[i].MatriculeAgent); Commissions[i].agentDelivreur = serviceAgent.selectAgent(Commissions[i].MatriculeAgentDelivreur); } } } #endregion return(Commissions); }
List <crlFicheBord> IntfDalFicheBord.selectFicheBord(DateTime date, string heure, string idItineraire, string numAgence) { #region declaration List <crlFicheBord> ficheBords = null; crlFicheBord tempFicheBord = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfAutorisationVoyage serviceAutorisationVoyage = new ImplAutorisationVoyage(); IntfDalFicheBord serviceFicheBord = new ImplDalFicheBord(); IntfDalPlaceFB servicePlaceFB = new ImplDalPlaceFB(); #endregion #region implementation if (heure != "" && idItineraire != "" && numAgence != "") { this.strCommande = "SELECT (fichebord.numerosFB) AS numFB, (fichebord.matriculeAgent) AS matrAgent, (fichebord.numerosAV) AS numAV,"; this.strCommande += " fichebord.dateHeurDepart, fichebord.dateHeurPrevue FROM fichebord"; this.strCommande += " Inner Join autorisationvoyage ON autorisationvoyage.numerosAV = fichebord.numerosAV"; this.strCommande += " Inner Join verification ON verification.idVerification = autorisationvoyage.idVerification"; this.strCommande += " Left Join autorisationdepart ON autorisationdepart.numerosFB = fichebord.numerosFB"; this.strCommande += " Inner Join agent ON agent.matriculeAgent = fichebord.matriculeAgent"; this.strCommande += " WHERE fichebord.dateHeurPrevue LIKE '" + date.ToString("yyyy-MM-dd") + " " + heure + "%' AND"; this.strCommande += " autorisationdepart.numerosFB IS NULL AND"; this.strCommande += " agent.numAgence = '" + numAgence + "' AND"; this.strCommande += " verification.idItineraire = '" + idItineraire + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { ficheBords = new List <crlFicheBord>(); while (this.reader.Read()) { tempFicheBord = new crlFicheBord(); tempFicheBord.NumerosFB = reader["numFB"].ToString(); tempFicheBord.MatriculeAgent = reader["matrAgent"].ToString(); tempFicheBord.NumerosAV = reader["numAV"].ToString(); tempFicheBord.DateHeurDepart = Convert.ToDateTime(reader["dateHeurDepart"].ToString()); tempFicheBord.DateHeurPrevue = Convert.ToDateTime(reader["dateHeurPrevue"].ToString()); ficheBords.Add(tempFicheBord); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (ficheBords != null) { for (int i = 0; i < ficheBords.Count; i++) { ficheBords[i].agent = serviceAgent.selectAgent(ficheBords[i].MatriculeAgent); ficheBords[i].autorisationVoyage = serviceAutorisationVoyage.selectAutorisationVoyage(ficheBords[i].NumerosAV); ficheBords[i].commission = serviceFicheBord.getCommission(ficheBords[i].NumerosFB); //FicheBord.escorteVoyage = serviceFicheBord.getEscorteVoyage(FicheBord.NumerosFB); ficheBords[i].voyage = serviceFicheBord.getVoyage(ficheBords[i].NumerosFB); ficheBords[i].placeFB = servicePlaceFB.selectPlaceFB(ficheBords[i].NumerosFB); } } } #endregion return(ficheBords); }
List <crlAutorisationDepart> IntfDalAutorisationDepart.selectADFacture(string numFacture) { #region declaration crlAutorisationDepart tempAutorisationDepart = null; List <crlAutorisationDepart> autorisationDeparts = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalFacture serviceFacture = new ImplDalFacture(); IntfDalFicheBord serviceFicheBord = new ImplDalFicheBord(); #endregion #region implementation if (numFacture != "") { this.strCommande = "SELECT (autorisationdepart.numAutorisationDepart) AS numAD, autorisationdepart.numerosFB,"; this.strCommande += " autorisationdepart.matriculeAgent, autorisationdepart.recetteTotale, autorisationdepart.dateAD,"; this.strCommande += " autorisationdepart.resteRegle FROM autorisationdepart"; this.strCommande += " Inner Join assocautorisationdepartfacture ON assocautorisationdepartfacture.numAutorisationDepart = autorisationdepart.numAutorisationDepart"; this.strCommande += " WHERE assocautorisationdepartfacture.numFacture = '" + numFacture + "'"; this.serviceConnectBase.openConnection(); reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { autorisationDeparts = new List <crlAutorisationDepart>(); while (reader.Read()) { tempAutorisationDepart = new crlAutorisationDepart(); tempAutorisationDepart.NumAutorisationDepart = reader["numAD"].ToString(); tempAutorisationDepart.NumerosFB = reader["numerosFB"].ToString(); tempAutorisationDepart.MatriculeAgent = reader["matriculeAgent"].ToString(); try { tempAutorisationDepart.DateAD = Convert.ToDateTime(reader["dateAD"].ToString()); } catch (Exception) { } try { tempAutorisationDepart.RecetteTotale = Double.Parse(reader["recetteTotale"].ToString()); }catch (Exception) {} try { tempAutorisationDepart.ResteRegle = double.Parse(reader["resteRegle"].ToString()); }catch (Exception) {} autorisationDeparts.Add(tempAutorisationDepart); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (autorisationDeparts != null) { for (int i = 0; i < autorisationDeparts.Count; i++) { if (autorisationDeparts[i].MatriculeAgent != "") { autorisationDeparts[i].agent = serviceAgent.selectAgent(autorisationDeparts[i].MatriculeAgent); } if (autorisationDeparts[i].NumerosFB != "") { autorisationDeparts[i].ficheBord = serviceFicheBord.selectFicheBord(autorisationDeparts[i].NumerosFB); } } } } #endregion return(autorisationDeparts); }
crlBonDeCommande IntfDalBonDeCommande.selectBonDeCommande(string numBonDeCommande) { #region declaration crlBonDeCommande bonDeCommande = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalProforma serviceProforma = new ImplDalProforma(); #endregion #region implementation if (numBonDeCommande != "") { this.strCommande = "SELECT * FROM `bondecommande` WHERE (`numBonDeCommande`='" + numBonDeCommande + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { bonDeCommande = new crlBonDeCommande(); bonDeCommande.NumBonDeCommande = this.reader["numBonDeCommande"].ToString(); try { bonDeCommande.DateBC = Convert.ToDateTime(this.reader["dateBC"].ToString()); } catch (Exception) { } try { bonDeCommande.DatePaiementBC = Convert.ToDateTime(this.reader["datePaiementBC"].ToString()); } catch (Exception) { } bonDeCommande.DescriptionBC = this.reader["descriptionBC"].ToString(); bonDeCommande.MatriculeAgent = this.reader["matriculeAgent"].ToString(); try { bonDeCommande.MontantBC = double.Parse(this.reader["montantBC"].ToString()); } catch (Exception) { } bonDeCommande.NumProforma = this.reader["numProforma"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (bonDeCommande != null) { if (bonDeCommande.NumProforma != "") { bonDeCommande.proforma = serviceProforma.selectProforma(bonDeCommande.NumProforma); } if (bonDeCommande.MatriculeAgent != "") { bonDeCommande.agent = serviceAgent.selectAgent(bonDeCommande.MatriculeAgent); } } } #endregion return(bonDeCommande); }
List <crlRecuAD> IntfDalRecuAD.selectRecuADFacture(string numFacture) { #region declaration crlRecuAD tempRecuAD = null; List <crlRecuAD> recuADs = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalPrelevement servicePrelevement = new ImplDalPrelevement(); #endregion #region implementation if (numFacture != "") { this.strCommande = "SELECT (recuad.numRecuAD) AS numR, recuad.numPrelevement, recuad.matriculeAgent, recuad.libele,"; this.strCommande += " recuad.montant, recuad.dateRecu FROM recuad"; this.strCommande += " Inner Join prelevement ON prelevement.numPrelevement = recuad.numPrelevement"; this.strCommande += " Inner Join autorisationdepart ON autorisationdepart.numAutorisationDepart = prelevement.numAutorisationDepart"; this.strCommande += " Inner Join assocautorisationdepartfacture ON assocautorisationdepartfacture.numAutorisationDepart = autorisationdepart.numAutorisationDepart"; this.strCommande += " WHERE assocautorisationdepartfacture.numFacture = '" + numFacture + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { recuADs = new List <crlRecuAD>(); while (reader.Read()) { tempRecuAD = new crlRecuAD(); tempRecuAD.NumRecuAD = reader["numR"].ToString(); tempRecuAD.MatriculeAgent = reader["matriculeAgent"].ToString(); tempRecuAD.NumPrelevement = reader["numPrelevement"].ToString(); tempRecuAD.Libele = reader["libele"].ToString(); tempRecuAD.Montant = reader["montant"].ToString(); try { tempRecuAD.Date = Convert.ToDateTime(reader["dateRecu"].ToString()); } catch (Exception) { } recuADs.Add(tempRecuAD); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (recuADs != null) { for (int i = 0; i < recuADs.Count; i++) { if (recuADs[i].MatriculeAgent != "") { recuADs[i].agent = serviceAgent.selectAgent(recuADs[i].MatriculeAgent); } if (recuADs[i].NumPrelevement != "") { recuADs[i].prelevement = servicePrelevement.selectPrelevement(recuADs[i].NumPrelevement); } } } } #endregion return(recuADs); }
List <crlAutorisationDepart> IntfDalAutorisationDepart.selectADProprietaireFactureIsNull(string numProprietaire) { #region declaration crlAutorisationDepart tempAutorisationDepart = null; List <crlAutorisationDepart> autorisationDeparts = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalFacture serviceFacture = new ImplDalFacture(); IntfDalFicheBord serviceFicheBord = new ImplDalFicheBord(); #endregion #region implementation if (numProprietaire != "") { this.strCommande = "SELECT (autorisationdepart.numAutorisationDepart) AS numAD, (autorisationdepart.numerosFB) AS numFB,"; this.strCommande += " autorisationdepart.matriculeAgent FROM autorisationdepart"; this.strCommande += " Inner Join fichebord ON fichebord.numerosFB = autorisationdepart.numerosFB"; this.strCommande += " Inner Join autorisationvoyage ON autorisationvoyage.numerosAV = fichebord.numerosAV"; this.strCommande += " Inner Join verification ON verification.idVerification = autorisationvoyage.idVerification"; this.strCommande += " Inner Join licence ON licence.numLicence = verification.numLicence"; this.strCommande += " Inner Join vehicule ON vehicule.numVehicule = licence.numVehicule"; this.strCommande += " Left Join assocautorisationdepartfacture ON assocautorisationdepartfacture.numAutorisationDepart = autorisationdepart.numAutorisationDepart"; this.strCommande += " WHERE assocautorisationdepartfacture.numFacture IS NULL AND"; this.strCommande += " vehicule.numProprietaire = '" + numProprietaire + "'"; this.serviceConnectBase.openConnection(); reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { autorisationDeparts = new List <crlAutorisationDepart>(); while (reader.Read()) { tempAutorisationDepart = new crlAutorisationDepart(); tempAutorisationDepart.NumAutorisationDepart = reader["numAD"].ToString(); tempAutorisationDepart.NumerosFB = reader["numFB"].ToString(); tempAutorisationDepart.MatriculeAgent = reader["matriculeAgent"].ToString(); autorisationDeparts.Add(tempAutorisationDepart); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (autorisationDeparts != null) { for (int i = 0; i < autorisationDeparts.Count; i++) { if (autorisationDeparts[i].MatriculeAgent != "") { autorisationDeparts[i].agent = serviceAgent.selectAgent(autorisationDeparts[i].MatriculeAgent); } if (autorisationDeparts[i].NumerosFB != "") { autorisationDeparts[i].ficheBord = serviceFicheBord.selectFicheBord(autorisationDeparts[i].NumerosFB); } } } } #endregion return(autorisationDeparts); }
crlUSVoyage IntfDalUSVoyage.selectUSVoyage(string numVoyage) { #region declaration crlUSVoyage voyage = null; IntfDalAgent serviceAgent = new ImplDalAgent(); #endregion #region implementation if (numVoyage != "") { this.strCommande = "SELECT * FROM `usvoyage` WHERE `numVoyage`='" + numVoyage + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { voyage = new crlUSVoyage(); try { voyage.DateHeureArrive = Convert.ToDateTime(this.reader["dateHeureArrive"].ToString()); } catch (Exception) { } try { voyage.DateHeureDepart = Convert.ToDateTime(this.reader["dateHeureDepart"].ToString()); } catch (Exception) { } voyage.MatriculeAgentArrive = this.reader["matriculeAgentArrive"].ToString(); voyage.MatriculeAgentChauffeur = this.reader["matriculeAgentChauffeur"].ToString(); voyage.MatriculeAgentControleur = this.reader["matriculeAgentControleur"].ToString(); voyage.MatriculeAgentDepart = this.reader["matriculeAgentDepart"].ToString(); voyage.MatriculeAgentReceveur = this.reader["matriculeAgentReceveur"].ToString(); voyage.NumLicence = this.reader["numLicence"].ToString(); voyage.NumVoyage = this.reader["numVoyage"].ToString(); voyage.NumLigne = this.reader["numLigne"].ToString(); voyage.NumFacture = this.reader["numFacture"].ToString(); voyage.NumAppareil = this.reader["numAppareil"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (voyage != null) { if (voyage.MatriculeAgentArrive != "") { voyage.agentArrive = serviceAgent.selectAgent(voyage.MatriculeAgentArrive); } if (voyage.MatriculeAgentChauffeur != "") { voyage.agentChauffeur = serviceAgent.selectAgent(voyage.MatriculeAgentChauffeur); } if (voyage.MatriculeAgentControleur != "") { voyage.agentControleur = serviceAgent.selectAgent(voyage.MatriculeAgentControleur); } if (voyage.MatriculeAgentDepart != "") { voyage.agentDepart = serviceAgent.selectAgent(voyage.MatriculeAgentDepart); } if (voyage.MatriculeAgentReceveur != "") { voyage.agentReceveur = serviceAgent.selectAgent(voyage.MatriculeAgentReceveur); } } } #endregion return(voyage); }
crlRecuAbonnement IntfDalRecuAbonnement.selectRecuAbonnement(string numRecuAbonnement) { #region declaration crlRecuAbonnement recuAbonnement = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalCheque serviceCheque = new ImplDalCheque(); IntfDalBonDeCommande serviceBonDeCommande = new ImplDalBonDeCommande(); IntfDalVoyageAbonnement serviceVoyageAbonnement = new ImplDalVoyageAbonnement(); IntfDalDureeAbonnement serviceDureeAbonnement = new ImplDalDureeAbonnement(); #endregion #region implementation if (numRecuAbonnement != "") { this.strCommande = "SELECT * FROM `recuabonnement` WHERE (`numRecuAbonnement`='" + numRecuAbonnement + "')"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (this.reader != null) { if (this.reader.HasRows) { if (this.reader.Read()) { recuAbonnement = new crlRecuAbonnement(); try { recuAbonnement.DateRecuAbonnement = Convert.ToDateTime(this.reader["dateRecuAbonnement"].ToString()); } catch (Exception) { } recuAbonnement.MatriculeAgent = this.reader["matriculeAgent"].ToString(); recuAbonnement.ModePaiement = this.reader["modePaiement"].ToString(); try { recuAbonnement.MontantRecuAbonnement = double.Parse(this.reader["montantRecuAbonnement"].ToString()); } catch (Exception) { } recuAbonnement.NumBonDeCommande = this.reader["numBonDeCommande"].ToString(); recuAbonnement.NumCheque = this.reader["numCheque"].ToString(); recuAbonnement.NumDureeAbonnement = this.reader["numDureeAbonnement"].ToString(); recuAbonnement.NumRecuAbonnement = this.reader["numRecuAbonnement"].ToString(); recuAbonnement.NumVoyageAbonnement = this.reader["numVoyageAbonnement"].ToString(); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (recuAbonnement != null) { if (recuAbonnement.MatriculeAgent != "") { recuAbonnement.agent = serviceAgent.selectAgent(recuAbonnement.MatriculeAgent); } if (recuAbonnement.NumBonDeCommande != "") { recuAbonnement.bonDeCommande = serviceBonDeCommande.selectBonDeCommande(recuAbonnement.NumBonDeCommande); } if (recuAbonnement.NumCheque != "") { recuAbonnement.cheque = serviceCheque.selectCheque(recuAbonnement.NumCheque); } if (recuAbonnement.NumDureeAbonnement != "") { recuAbonnement.dureeAbonnement = serviceDureeAbonnement.selectDureeAbonnement(recuAbonnement.NumDureeAbonnement); } if (recuAbonnement.NumVoyageAbonnement != "") { recuAbonnement.voyageAbonnement = serviceVoyageAbonnement.selectVoyageAbonnement(recuAbonnement.NumVoyageAbonnement); } } } #endregion return(recuAbonnement); }
DataTable IntfDalUSVoyage.getDataTableUSVoyageNonArrive(string strRqst) { #region declaration DataTable dataTable = new DataTable(); IntfDalAgent serviceAgent = new ImplDalAgent(); crlAgent agentChauffeur = null; crlAgent agentReceveur = null; crlAgent agentControleur = null; #endregion #region implementation #region initialisation du dataTable dataTable = new DataTable(); dataTable.Columns.Add("numVoyage", typeof(string)); dataTable.Columns.Add("nomLigne", typeof(string)); dataTable.Columns.Add("vehicule", typeof(string)); dataTable.Columns.Add("chauffeur", typeof(string)); dataTable.Columns.Add("receveur", typeof(string)); dataTable.Columns.Add("controleur", typeof(string)); DataRow dr; #endregion this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(strRqst); if (this.reader != null) { if (this.reader.HasRows) { while (this.reader.Read()) { dr = dataTable.NewRow(); dr["numVoyage"] = reader["numVoyage"].ToString(); dr["nomLigne"] = reader["nomLigne"].ToString(); dr["vehicule"] = reader["matriculeVehicule"].ToString() + " " + reader["marqueVehicule"].ToString() + " " + reader["couleurVehicule"].ToString(); agentChauffeur = serviceAgent.selectAgent(reader["matriculeAgentChauffeur"].ToString()); agentReceveur = serviceAgent.selectAgent(reader["matriculeAgentReceveur"].ToString()); agentControleur = serviceAgent.selectAgent(reader["matriculeAgentControleur"].ToString()); if (agentChauffeur != null) { dr["chauffeur"] = agentChauffeur.nomAgent + " " + agentChauffeur.prenomAgent; } else { dr["chauffeur"] = ""; } if (agentReceveur != null) { dr["receveur"] = agentReceveur.nomAgent + " " + agentReceveur.prenomAgent; } else { dr["receveur"] = ""; } if (agentControleur != null) { dr["controleur"] = agentControleur.nomAgent + " " + agentControleur.prenomAgent; } else { dr["controleur"] = ""; } dataTable.Rows.Add(dr); } } this.reader.Dispose(); } this.serviceConnectBase.closeConnection(); #endregion return(dataTable); }
crlRecuEncaisser IntfDalRecuEncaisser.isValideRecu(string numRecu) { #region declaration crlRecuEncaisser Recu = null; IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalCheque serviceCheque = new ImplDalCheque(); #endregion #region implementation if (numRecu != "") { this.strCommande = "SELECT recuencaisser.numRecuEncaisser, recuencaisser.matriculeAgent, recuencaisser.numCheque, recuencaisser.modePaiement,"; this.strCommande += " recuencaisser.dateRecuEncaisser, recuencaisser.montantRecuEncaisser, recuencaisser.libelleRecuEncaisser FROM recuencaisser"; this.strCommande += " Left Join assocrecuencaisserproformabondecommande ON assocrecuencaisserproformabondecommande.numRecuEncaisser = recuencaisser.numRecuEncaisser"; this.strCommande += " Left Join bagage ON bagage.numRecu = recuencaisser.numRecuEncaisser"; this.strCommande += " WHERE assocrecuencaisserproformabondecommande.numRecuEncaisser IS NULL AND"; this.strCommande += " bagage.numRecu IS NULL AND"; this.strCommande += " recuencaisser.numRecuEncaisser = '" + numRecu + "'"; this.serviceConnectBase.openConnection(); this.reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { Recu = new crlRecuEncaisser(); reader.Read(); Recu.NumRecuEncaisser = reader["numRecuEncaisser"].ToString(); Recu.LibelleRecuEncaisser = reader["libelleRecuEncaisser"].ToString(); try { Recu.MontantRecuEncaisser = double.Parse(reader["montantRecuEncaisser"].ToString()); } catch (Exception) { } Recu.ModePaiement = reader["modePaiement"].ToString(); Recu.MatriculeAgent = reader["matriculeAgent"].ToString(); try { Recu.DateRecuEncaisser = Convert.ToDateTime(reader["dateRecuEncaisser "].ToString()); } catch (Exception) { } Recu.NumCheque = reader["numCheque"].ToString(); } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Recu != null) { if (Recu.MatriculeAgent != "") { Recu.agent = serviceAgent.selectAgent(Recu.MatriculeAgent); } if (Recu.NumCheque != "") { Recu.cheque = serviceCheque.selectCheque(Recu.NumCheque); } } } #endregion return(Recu); }
crlBillet IntfDalBillet.selectBillet(string numBillet) { #region declaration crlBillet Billet = null; IntfDalTrajet serviceTrajet = new ImplDalTrajet(); IntfDalIndividu serviceIndividu = new ImplDalIndividu(); IntfDalAgent serviceAgent = new ImplDalAgent(); IntfDalCalculCategorieBillet serviceCalculCategorieBillet = new ImplDalCalculCategorieBillet(); IntfDalCalculReductionBillet serviceCalculReductionBillet = new ImplDalCalculReductionBillet(); #endregion #region implementation if (numBillet != "") { this.strCommande = "SELECT * FROM `billet` WHERE (`numBillet`='" + numBillet + "')"; this.serviceConnectBase.openConnection(); reader = this.serviceConnectBase.select(this.strCommande); if (reader != null) { if (reader.HasRows) { if (reader.Read()) { Billet = new crlBillet(); Billet.NumBillet = reader["numBillet"].ToString(); Billet.DateDeValidite = Convert.ToDateTime(reader["dateDeValidite"].ToString()); Billet.NumTrajet = reader["numTrajet"].ToString(); Billet.ModePaiement = reader["modePaiement"].ToString(); Billet.NumIndividu = reader["numIndividu"].ToString(); Billet.MatriculeAgent = reader["matriculeAgent"].ToString(); Billet.PrixBillet = reader["prixBillet"].ToString(); Billet.NumCalculCategorieBillet = reader["numCalculCategorieBillet"].ToString(); Billet.NumCalculReductionBillet = reader["numCalculReductionBillet"].ToString(); Billet.DateBillet = Convert.ToDateTime(reader["dateBillet"].ToString()); Billet.NumDureeAbonnement = reader["numDureeAbonnement"].ToString(); Billet.NumVoyageAbonnement = reader["numVoyageAbonnement"].ToString(); Billet.NumBilletCommande = reader["numBilletCommande"].ToString(); } } reader.Dispose(); } this.serviceConnectBase.closeConnection(); if (Billet != null) { if (Billet.NumTrajet != "") { Billet.trajet = serviceTrajet.selectTrajet(Billet.NumTrajet); } if (Billet.NumIndividu != "") { Billet.individu = serviceIndividu.selectIndividu(Billet.NumIndividu); } if (Billet.MatriculeAgent != "") { Billet.agent = serviceAgent.selectAgent(Billet.MatriculeAgent); } if (Billet.NumCalculCategorieBillet != "") { Billet.calculCategorieBillet = serviceCalculCategorieBillet.selectCalculCategorieBillet(Billet.NumCalculCategorieBillet); } if (Billet.NumCalculReductionBillet != "") { Billet.calculReductionBillet = serviceCalculReductionBillet.selectCalculReductionBillet(Billet.NumCalculReductionBillet); } } } #endregion return(Billet); }