DataTable IntfDalFicheBord.getDataTableFicheBordSansAD(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
crlVille villeD = new crlVille();
crlVille villeF = new crlVille();
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numFb", typeof(string));
dataTable.Columns.Add("dateHeurPrevue", typeof(DateTime));
dataTable.Columns.Add("voiture", typeof(string));
dataTable.Columns.Add("chauffeur", typeof(string));
dataTable.Columns.Add("itineraire", typeof(string));
dataTable.Columns.Add("nbPlaceLibre", 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["numFb"] = reader["numFb"].ToString();
dr["dateHeurPrevue"] = Convert.ToDateTime(reader["dateHeurPrevue"].ToString());
dr["voiture"] = reader["matriculeVehicule"].ToString() + " " + reader["marqueVehicule"].ToString();
dr["chauffeur"] = reader["nomChauffeur"].ToString() + " " + reader["prenomChauffeur"].ToString();
villeD = serviceVille.selectVille(reader["numVilleItineraireDebut"].ToString());
villeF = serviceVille.selectVille(reader["numVilleItineraireFin"].ToString());
if (villeD != null && villeF != null)
{
dr["itineraire"] = villeD.NomVille + "-" + villeF.NomVille;
}
else
{
dr["itineraire"] = reader["numVilleItineraireDebut"].ToString() + "-" + reader["numVilleItineraireFin"].ToString();
}
dr["nbPlaceLibre"] = (int.Parse(reader["nbPlace"].ToString()) - int.Parse(reader["nbOccuper"].ToString())).ToString();
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
DataTable IntfDalBillet.getDataTableBilletInsertToFB(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
crlVille villeD = new crlVille();
crlVille villeF = new crlVille();
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numBillet", typeof(string));
dataTable.Columns.Add("dateValidite", typeof(DateTime));
dataTable.Columns.Add("client", typeof(string));
dataTable.Columns.Add("trajet", 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["numBillet"] = reader["numBillet"].ToString();
dr["dateValidite"] = Convert.ToDateTime(reader["dateDeValidite"].ToString());
dr["client"] = reader["nomIndividu"].ToString() + " " + reader["prenomIndividu"].ToString();
villeD = serviceVille.selectVille(reader["numVilleD"].ToString());
villeF = serviceVille.selectVille(reader["numVilleF"].ToString());
if (villeD != null && villeF != null)
{
dr["trajet"] = villeD.NomVille + "-" + villeF.NomVille;
}
else
{
dr["trajet"] = reader["numVilleD"].ToString() + "-" + reader["numVilleF"].ToString();
}
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
crlAgence IntfDalAgence.selectAgence(string numAgence)
{
#region declaration
crlAgence agence = null;
IntfDalTypeAgence serviceTypeAgence = new ImplDalTypeAgence();
IntfDalVille serviceVille = new ImplDalVille();
IntfDalSessionAgence serviceSessionAgence = new ImplDalSessionAgence();
#endregion
#region implementation
if (numAgence != "")
{
this.strCommande = "SELECT * FROM agence WHERE `numagence`='" + numAgence + "'";
this.serviceConnectBase.openConnection();
this.reader = this.serviceConnectBase.select(this.strCommande);
if (this.reader != null)
{
if (this.reader.HasRows)
{
if (this.reader.Read())
{
agence = new crlAgence();
agence.NumAgence = this.reader["numAgence"].ToString();
agence.NumVille = this.reader["numVille"].ToString();
agence.NomAgence = this.reader["nomAgence"].ToString();
agence.SigleAgence = this.reader["sigleAgence"].ToString();
agence.TypeAgence = this.reader["typeAgence"].ToString();
agence.LocalisationAgence = this.reader["localisationAgence"].ToString();
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (agence != null)
{
if (agence.TypeAgence != "")
{
agence.typeAgenceObj = serviceTypeAgence.selectTypeAgence(agence.TypeAgence);
}
if (agence.NumVille != "")
{
agence.ville = serviceVille.selectVille(agence.NumVille);
}
if (agence.NumAgence != "")
{
agence.sessionAgence = serviceSessionAgence.getSessionAgenceEncours(agence.NumAgence);
}
}
}
#endregion
return(agence);
}
crlGareRoutiere IntfDalGareRoutiere.selectGareRoutiere(string numerosGareRoutiere)
{
#region declaration
crlGareRoutiere gareRoutiere = null;
crlProvince province = null;
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
if (numerosGareRoutiere != "")
{
this.strCommande = "SELECT * FROM `gareRoutiere` WHERE (`numerosGareRoutiere`='" + numerosGareRoutiere + "')";
this.serviceConnectBase.openConnection();
reader = this.serviceConnectBase.select(this.strCommande);
if (reader != null)
{
if (reader.HasRows)
{
if (reader.Read())
{
gareRoutiere = new crlGareRoutiere();
province = new crlProvince();
gareRoutiere.numerosGareRoutiere = reader["numerosGareRoutiere"].ToString();
gareRoutiere.nomProvince = reader["nomProvince"].ToString();
gareRoutiere.localisation = reader["localisation"].ToString();
gareRoutiere.sigleGare = reader["sigleGare"].ToString();
gareRoutiere.NumVille = reader["numVille"].ToString();
province.nomProvince = reader["nomProvince"].ToString();
gareRoutiere.province = province;
}
}
reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (gareRoutiere != null)
{
if (gareRoutiere.NumVille != "")
{
gareRoutiere.ville = serviceVille.selectVille(gareRoutiere.NumVille);
}
}
}
#endregion
return(gareRoutiere);
}
DataTable IntfDalAutorisationDepart.getDataTableAutorisationDepart(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
IntfDalFicheBord serviceFicheBord = new ImplDalFicheBord();
IntfDalGeneral serviceGeneral = new ImplDalGeneral();
crlVille villeD = new crlVille();
crlVille villeF = new crlVille();
double montant = 0.00;
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numAutorisationDepart", typeof(string));
dataTable.Columns.Add("dateDepart", typeof(DateTime));
dataTable.Columns.Add("itineraire", typeof(string));
dataTable.Columns.Add("chauffeur", typeof(string));
dataTable.Columns.Add("voiture", typeof(string));
dataTable.Columns.Add("montant", typeof(string));
dataTable.Columns.Add("montantRecu", 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["numAutorisationDepart"] = reader["numAutorisationDepart"].ToString();
dr["dateDepart"] = Convert.ToDateTime(reader["dateHeurDepart"].ToString());
villeD = serviceVille.selectVille(reader["numVilleItineraireDebut"].ToString());
villeF = serviceVille.selectVille(reader["numVilleItineraireFin"].ToString());
if (villeD != null && villeF != null)
{
dr["itineraire"] = villeD.NomVille + "-" + villeF.NomVille;
}
else
{
dr["itineraire"] = reader["numVilleItineraireDebut"].ToString() + "-" + reader["numVilleItineraireFin"].ToString();
}
dr["chauffeur"] = reader["nomChauffeur"].ToString() + " " + reader["prenomChauffeur"].ToString();
dr["voiture"] = reader["matriculeVehicule"].ToString() + " " + reader["marqueVehicule"].ToString() + " " + reader["couleurVehicule"].ToString();
montant = serviceFicheBord.getPrixTotalBagage(reader["numerosFB"].ToString()) + serviceFicheBord.getPrixTotalBillet(reader["numerosFB"].ToString()) + serviceFicheBord.getPrixTotalCommission(reader["numerosFB"].ToString());
dr["montant"] = serviceGeneral.separateurDesMilles(montant.ToString("0")) + "Ar";
dr["montantRecu"] = serviceGeneral.separateurDesMilles(reader["sommeRecu"].ToString()) + "Ar";
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
DataTable IntfDalAgence.getDataTableAgenceListe(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
crlAgence agence = null;
IntfDalVille serviceVille = new ImplDalVille();
IntfDalAgence serviceAgence = new ImplDalAgence();
crlVille ville = null;
#endregion
#region implemntation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numAgence", typeof(string));
dataTable.Columns.Add("villeAgence", typeof(string));
dataTable.Columns.Add("nomAgence", typeof(string));
dataTable.Columns.Add("localisation", typeof(string));
dataTable.Columns.Add("typeAgence", typeof(string));
dataTable.Columns.Add("sigleAgence", 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();
agence = serviceAgence.selectAgence(this.reader["numAgence"].ToString());
dr["numAgence"] = this.reader["numAgence"].ToString();
ville = serviceVille.selectVille(this.reader["numVille"].ToString());
if (ville != null)
{
dr["villeAgence"] = ville.NomVille;
}
else
{
dr["villeAgence"] = this.reader["numVille"].ToString();
}
dr["nomAgence"] = this.reader["nomAgence"].ToString();
dr["sigleAgence"] = this.reader["sigleAgence"].ToString();
dr["localisation"] = this.reader["localisationAgence"].ToString();
dr["typeAgence"] = this.reader["typeAgence"].ToString();
if (agence.sessionAgence != null)
{
dr["statut"] = "vert16.png";
}
else
{
dr["statut"] = "rouge16.png";
}
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
DataTable IntfDalVerification.getDataTableVerificationNonValider(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
crlVille villeD = null;
crlVille villeF = null;
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("idVerification", typeof(string));
dataTable.Columns.Add("dateVerification", typeof(DateTime));
dataTable.Columns.Add("vehicule", typeof(string));
dataTable.Columns.Add("chauffeur", typeof(string));
dataTable.Columns.Add("itineraire", 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["idVerification"] = reader["idVerification"].ToString();
dr["dateVerification"] = Convert.ToDateTime(reader["dateVerification"].ToString());
dr["vehicule"] = reader["matriculeVehicule"].ToString() + " " + reader["marqueVehicule"].ToString() + " " + reader["couleurVehicule"].ToString();
dr["chauffeur"] = reader["prenomChauffeur"].ToString() + " " + reader["nomChauffeur"].ToString();
villeD = serviceVille.selectVille(reader["numVilleItineraireDebut"].ToString());
villeF = serviceVille.selectVille(reader["numVilleItineraireFin"].ToString());
if (villeD != null && villeF != null)
{
dr["itineraire"] = villeD.NomVille + "-" + villeF.NomVille;
}
else
{
dr["itineraire"] = reader["numVilleItineraireDebut"].ToString() + "-" + reader["numVilleItineraireFin"].ToString();
}
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
DataTable IntfDalTrajet.getDataTableTrajetNotItineraire(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
IntfDalGeneral serviceGeneral = new ImplDalGeneral();
crlVille villeD = null;
crlVille villeF = null;
string strTrajet = "";
#endregion
#region initialise table
dataTable.Columns.Add("numTrajet", typeof(string));
dataTable.Columns.Add("distanceTrajet", typeof(string));
dataTable.Columns.Add("dureeTrajet", typeof(string));
dataTable.Columns.Add("trajet", typeof(string));
DataRow dr = null;
#endregion
#region implementation
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["numTrajet"] = this.reader["numTrajet"].ToString();
dr["distanceTrajet"] = this.reader["distanceTrajet"].ToString() + "Km";
dr["dureeTrajet"] = serviceGeneral.getTextTimeSpan(this.reader["dureeTrajet"].ToString());
villeD = serviceVille.selectVille(this.reader["numVilleD"].ToString());
villeF = serviceVille.selectVille(this.reader["numVilleF"].ToString());
if (villeD != null)
{
strTrajet += villeD.NomVille;
}
else
{
strTrajet += this.reader["numVilleD"].ToString();
}
if (villeF != null)
{
strTrajet += "-" + villeF.NomVille;
}
else
{
strTrajet += "-" + this.reader["numVilleF"].ToString();
}
dr["trajet"] = strTrajet;
strTrajet = "";
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
crlTrajet IntfDalTrajet.selectTrajet(string numTrajet)
{
#region declaration
crlTrajet Trajet = null;
IntfDalVille serviceVille = new ImplDalVille();
IntfDalTarifBaseBillet serviceTarifBaseBillet = new ImplDalTarifBaseBillet();
IntfDalTarifBaseCommission serviceTarifBaseCommission = new ImplDalTarifBaseCommission();
int i = 0;
#endregion
#region implementation
if (numTrajet != "")
{
this.strCommande = "SELECT * FROM `trajet` WHERE (`numTrajet`='" + numTrajet + "')";
this.serviceConnectBase.openConnection();
reader = this.serviceConnectBase.select(this.strCommande);
if (reader != null)
{
if (reader.HasRows)
{
Trajet = new crlTrajet();
i = 1;
while (reader.Read())
{
Trajet.NumTrajet = reader["numTrajet"].ToString();
Trajet.NumTarifBaseBillet = reader["numTarifBaseBillet"].ToString();
try
{
Trajet.DistanceTrajet = int.Parse(reader["distanceTrajet"].ToString());
}
catch (Exception)
{}
Trajet.DureeTrajet = reader["dureeTrajet"].ToString();
Trajet.NumVilleD = reader["numVilleD"].ToString();
Trajet.NumVilleF = reader["numVilleF"].ToString();
}
}
reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (Trajet != null)
{
if (Trajet.NumVilleD != "")
{
Trajet.villeD = serviceVille.selectVille(Trajet.NumVilleD);
}
if (Trajet.NumVilleF != "")
{
Trajet.villeF = serviceVille.selectVille(Trajet.NumVilleF);
}
if (Trajet.NumTarifBaseBillet != "")
{
Trajet.tarifBaseBillet = serviceTarifBaseBillet.selectTarifBaseBillet(Trajet.NumTarifBaseBillet);
}
Trajet.tarifBaseCommissions = serviceTarifBaseCommission.selectTarifBaseCommissions(Trajet.NumTrajet);
}
}
#endregion
return(Trajet);
}
List <crlItineraire> IntfDalLicence.selectItineraire(string numLicence)
{
#region declaration
List <crlItineraire> Itineraires = null;
crlItineraire ItineraireTemp = null;
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
if (numLicence != "")
{
this.strCommande = "SELECT itineraire.idItineraire, itineraire.distanceParcour, itineraire.nombreRepos,";
this.strCommande += " itineraire.dureeTrajet, itineraire.numVilleItineraireDebut, itineraire.numVilleItineraireFin,";
this.strCommande += " itineraire.numInfoBagage FROM itineraire";
this.strCommande += " Inner Join assoitinerairelicence ON assoitinerairelicence.idItineraire = itineraire.idItineraire";
this.strCommande += " WHERE assoitinerairelicence.numLicence = '" + numLicence + "'";
this.serviceConnectBase.openConnection();
reader = this.serviceConnectBase.select(this.strCommande);
if (reader != null)
{
if (reader.HasRows)
{
Itineraires = new List <crlItineraire>();
while (reader.Read())
{
ItineraireTemp = new crlItineraire();
ItineraireTemp.DistanceParcour = int.Parse(reader["distanceParcour"].ToString());
ItineraireTemp.DureeTrajet = reader["dureeTrajet"].ToString();
ItineraireTemp.IdItineraire = reader["idItineraire"].ToString();
ItineraireTemp.NumVilleItineraireDebut = reader["numVilleItineraireDebut"].ToString();
ItineraireTemp.NumVilleItineraireFin = reader["numVilleItineraireFin"].ToString();
Itineraires.Add(ItineraireTemp);
}
}
reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (Itineraires != null)
{
for (int i = 0; i < Itineraires.Count; i++)
{
if (Itineraires[i].NumVilleItineraireDebut != "")
{
Itineraires[i].villeD = serviceVille.selectVille(Itineraires[i].NumVilleItineraireDebut);
}
if (Itineraires[i].NumVilleItineraireFin != "")
{
Itineraires[i].villeF = serviceVille.selectVille(Itineraires[i].NumVilleItineraireFin);
}
if (Itineraires[i].IdItineraire != "")
{
Itineraires[i].villes = serviceVille.selectVillesForItineraire(Itineraires[i].IdItineraire);
}
}
}
}
#endregion
return(Itineraires);
}
DataTable IntfDalProprietaire.getDataTableVoyageVoitureProprietaire(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
IntfDalFicheBord serviceFicheBord = new ImplDalFicheBord();
IntfDalAutorisationDepart serviceAutorisationDepart = new ImplDalAutorisationDepart();
IntfDalGeneral serviceGeneral = new ImplDalGeneral();
crlVille villeD = new crlVille();
crlVille villeF = new crlVille();
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("vehicule", typeof(string));
dataTable.Columns.Add("chauffeur", typeof(string));
dataTable.Columns.Add("dateVoyage", typeof(DateTime));
dataTable.Columns.Add("Itineraire", typeof(string));
dataTable.Columns.Add("recette", typeof(string));
dataTable.Columns.Add("reste", typeof(string));
DataRow dr;
#endregion
this.serviceConnection.openConnection();
this.reader = this.serviceConnection.select(strRqst);
if (this.reader != null)
{
if (this.reader.HasRows)
{
while (this.reader.Read())
{
dr = dataTable.NewRow();
dr["vehicule"] = reader["matriculeVehicule"].ToString() + " " + reader["marqueVehicule"];
dr["chauffeur"] = reader["nomChauffeur"].ToString() + " " + reader["prenomChauffeur"].ToString();
dr["dateVoyage"] = Convert.ToDateTime(reader["dateHeurDepart"].ToString());
villeD = serviceVille.selectVille(reader["numVilleItineraireDebut"].ToString());
villeF = serviceVille.selectVille(reader["numVilleItineraireFin"].ToString());
if (villeD != null && villeF != null)
{
dr["Itineraire"] = villeD.NomVille + "-" + villeF.NomVille;
}
else
{
dr["Itineraire"] = reader["numVilleItineraireDebut"].ToString() + "-" + reader["numVilleItineraireFin"].ToString();
}
dr["recette"] = serviceGeneral.separateurDesMilles(reader["recetteTotale"].ToString()) + "Ar";
dr["reste"] = serviceGeneral.separateurDesMilles(reader["resteRegle"].ToString()) + "Ar";
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnection.closeConnection();
#endregion
return(dataTable);
}
DataTable IntfDalBillet.getDataTableBilletAbonnement(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalGeneral serviceGeneral = new ImplDalGeneral();
IntfDalVille serviceVille = new ImplDalVille();
crlVille VilleD = null;
crlVille VilleF = null;
string strTrajet = "";
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numBillet", typeof(string));
dataTable.Columns.Add("dateValidite", typeof(DateTime));
dataTable.Columns.Add("modePaiement", typeof(string));
dataTable.Columns.Add("prixBillet", typeof(string));
dataTable.Columns.Add("trajet", 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["numBillet"] = reader["numBillet"].ToString();
dr["dateValidite"] = Convert.ToDateTime(reader["dateDeValidite"].ToString());
dr["modePaiement"] = reader["modePaiement"].ToString();
dr["prixBillet"] = serviceGeneral.separateurDesMilles(reader["prixBillet"].ToString()) + "Ar";
VilleD = serviceVille.selectVille(reader["numVilleD"].ToString());
VilleF = serviceVille.selectVille(reader["numVilleF"].ToString());
if (VilleD != null)
{
strTrajet = VilleD.NomVille + "-";
}
else
{
strTrajet = reader["numVilleD"].ToString() + "-";
}
if (VilleF != null)
{
strTrajet += VilleF.NomVille;
}
else
{
strTrajet += reader["numVilleF"].ToString();
}
dr["trajet"] = strTrajet;
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
