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);
}
