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);
}
void IntfDalVille.insertToGridVilleNotRN(GridView gridView, string param, string paramLike, string valueLike, List <crlVille> villes)
{
#region declaration
IntfDalVille serviceVille = new ImplDalVille();
string strWhere = "";
#endregion
#region implementation
if (villes != null)
{
for (int i = 0; i < villes.Count; i++)
{
strWhere += " ville.numVille <> '" + villes[i].NumVille + "' AND";
}
}
this.strCommande = "SELECT ville.numVille, ville.nomVille, ville.nomRegion, ville.nomProvince FROM ville";
this.strCommande += " WHERE " + strWhere;
this.strCommande += " " + paramLike + " LIKE '%" + valueLike + "%'";
this.strCommande += " ORDER BY " + param;
gridView.DataSource = serviceVille.getDataTableVilleNotRN(this.strCommande);
gridView.DataBind();
#endregion
}
string IntfDalVille.insertVille(crlVille Ville, string sigleAgence)
{
#region declaration
IntfDalVille serviceVille = new ImplDalVille();
int nombreInsertion = 0;
string numVille = "";
#endregion
#region implementation
if (Ville != null)
{
Ville.NumVille = serviceVille.getNumVille(sigleAgence);
this.strCommande = "INSERT INTO `ville` (`numVille`,`nomVille`,`nomRegion`,`nomProvince`)";
this.strCommande += " VALUES ('" + Ville.NumVille + "','" + Ville.NomVille + "',";
this.strCommande += "'" + Ville.NomRegion + "', '" + Ville.NomProvince + "')";
this.serviceConnectBase.openConnection();
nombreInsertion = this.serviceConnectBase.requete(this.strCommande);
if (nombreInsertion == 1)
{
numVille = Ville.NumVille;
}
this.serviceConnectBase.closeConnection();
}
#endregion
return(numVille);
}
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);
}
DataTable IntfDalLicence.getDataTableLicenceUSAvecPropriete(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
IntfDalLicence serviceLicence = new ImplDalLicence();
crlLicence licence = null;
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numLicence", typeof(string));
dataTable.Columns.Add("vehicule", typeof(string));
dataTable.Columns.Add("cooperative", typeof(string));
dataTable.Columns.Add("individu", typeof(string));
dataTable.Columns.Add("adresseIndividu", typeof(string));
dataTable.Columns.Add("contactIndividu", typeof(string));
dataTable.Columns.Add("responsable", typeof(string));
dataTable.Columns.Add("contactResponsable", 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["numLicence"] = reader["numLicence"].ToString();
dr["vehicule"] = reader["matriculeVehicule"].ToString() + " " + this.reader["couleurVehicule"].ToString() + " " + this.reader["marqueVehicule"].ToString();
dr["cooperative"] = this.reader["nomCooperative"].ToString();
dr["individu"] = reader["nomIndividu"].ToString() + " " + reader["prenomIndividu"].ToString() + reader["nomSociete"].ToString() + reader["nomOrganisme"].ToString();
dr["adresseIndividu"] = reader["adresseInd"].ToString();
dr["contactIndividu"] = reader["telephoneFixeIndividu"].ToString() + " " + reader["telephoneMobileIndividu"].ToString() + reader["telephoneFixeSociete"].ToString() + " " + reader["telephoneMobileSociete"].ToString() + reader["telephoneFixeOrganisme"].ToString() + " " + reader["telephoneMobileOrganisme"].ToString();
dr["responsable"] = reader["nomRespSoc"].ToString() + " " + reader["prenomRespSoc"].ToString() + reader["nomRespOrg"].ToString() + " " + reader["prenomRespOrg"].ToString();
dr["contactResponsable"] = reader["telephoneFixeResponsable"].ToString() + " " + reader["telephoneMobileResponsable"].ToString() + reader["telephoneFixeResponsable"].ToString() + " " + reader["telephoneMobileResponsable"].ToString();
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
List <crlRouteNationale> IntfDalRouteNationale.selectRNForItineraire(string idItineraire)
{
#region declaration
List <crlRouteNationale> routeNationales = null;
crlRouteNationale tempRouteNationaleObj = null;
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
if (idItineraire != "")
{
this.strCommande = "SELECT routenationale.routeNationale, routenationale.distanceRN FROM routenationale";
this.strCommande += " Inner Join assoitineraireroutenationale ON assoitineraireroutenationale.routeNationale = routenationale.routeNationale";
this.strCommande += " WHERE assoitineraireroutenationale.idItineraire = '" + idItineraire + "'";
this.serviceConnectBase.openConnection();
this.reader = this.serviceConnectBase.select(this.strCommande);
if (this.reader != null)
{
if (this.reader.HasRows)
{
routeNationales = new List <crlRouteNationale>();
while (this.reader.Read())
{
tempRouteNationaleObj = new crlRouteNationale();
tempRouteNationaleObj.RouteNationale = this.reader["routeNationale"].ToString();
try
{
tempRouteNationaleObj.DistanceRN = double.Parse(this.reader["distanceRN"].ToString());
}
catch (Exception)
{
}
routeNationales.Add(tempRouteNationaleObj);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (routeNationales != null)
{
for (int i = 0; i < routeNationales.Count; i++)
{
routeNationales[i].villes = serviceVille.selectVillesForRN(routeNationales[i].RouteNationale);
}
}
}
#endregion
return(routeNationales);
}
DataTable IntfDalLicence.getDataTableLicenceProprietaire(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numLicence", typeof(string));
dataTable.Columns.Add("matriculeVehicule", typeof(string));
dataTable.Columns.Add("marqueVehicule", typeof(string));
dataTable.Columns.Add("couleurVehicule", typeof(string));
dataTable.Columns.Add("zone", typeof(string));
dataTable.Columns.Add("valideAu", typeof(DateTime));
dataTable.Columns.Add("nombrePlacePayante", 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["numLicence"] = reader["numLicence"].ToString();
dr["matriculeVehicule"] = reader["matriculeVehicule"].ToString();
dr["marqueVehicule"] = reader["marqueVehicule"].ToString();
dr["couleurVehicule"] = reader["couleurVehicule"].ToString();
dr["zone"] = reader["zone"].ToString();
dr["valideAu"] = Convert.ToDateTime(reader["valideAu"].ToString());
dr["nombrePlacePayante"] = reader["nombrePlacePayante"].ToString();
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
DataTable IntfDalLicence.getDataTableLicenceUS(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
IntfDalLicence serviceLicence = new ImplDalLicence();
crlLicence licence = null;
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numLicence", typeof(string));
dataTable.Columns.Add("vehicule", typeof(string));
dataTable.Columns.Add("zone", typeof(string));
dataTable.Columns.Add("nbPlace", typeof(string));
dataTable.Columns.Add("numerosLicence", 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["numLicence"] = reader["numLicence"].ToString();
dr["vehicule"] = reader["matriculeVehicule"].ToString() + " " + this.reader["couleurVehicule"].ToString() + " " + this.reader["marqueVehicule"].ToString();
dr["zone"] = this.reader["zone"].ToString();
dr["nbPlace"] = reader["placesAssiseVehicule"].ToString();
dr["numerosLicence"] = reader["numerosLicence"].ToString();
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
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);
}
void IntfDalVille.insertToGridVille(GridView gridView, string param, string paramLike, string valueLike)
{
#region declaration
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
this.strCommande = "SELECT ville.numVille, ville.nomVille, ville.nomRegion, ville.nomProvince";
this.strCommande += " FROM ville WHERE " + paramLike + " LIKE '%" + valueLike + "%' ORDER BY " + param;
gridView.DataSource = serviceVille.getDataTableVille(this.strCommande);
gridView.DataBind();
#endregion
}
crlRouteNationale IntfDalRouteNationale.selectRouteNationale(string routeNationale)
{
#region declaration
crlRouteNationale routeNationaleObj = null;
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
if (routeNationale != "")
{
this.strCommande = "SELECT * FROM `routenationale` WHERE (`routeNationale`='" + routeNationale + "')";
this.serviceConnectBase.openConnection();
this.reader = this.serviceConnectBase.select(this.strCommande);
if (this.reader != null)
{
if (this.reader.HasRows)
{
if (this.reader.Read())
{
routeNationaleObj = new crlRouteNationale();
routeNationaleObj.RouteNationale = this.reader["routeNationale"].ToString();
try
{
routeNationaleObj.DistanceRN = double.Parse(this.reader["distanceRN"].ToString());
}
catch (Exception)
{
}
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (routeNationaleObj != null)
{
routeNationaleObj.villes = serviceVille.selectVillesForRN(routeNationaleObj.RouteNationale);
}
}
#endregion
return(routeNationaleObj);
}
void IntfDalVille.insertToGridVilleDestination(GridView gridView, string param, string paramLike, string valueLike, string numVille)
{
#region declaration
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
this.strCommande = "SELECT ville.numVille, ville.nomVille, ville.nomRegion, ville.nomProvince FROM ville";
this.strCommande += " Inner Join trajet ON trajet.numVilleD = ville.numVille OR trajet.numVilleF = ville.numVille";
this.strCommande += " WHERE ville.numVille <> '" + numVille + "' AND " + paramLike + " LIKE '%" + valueLike + "%'";
this.strCommande += " AND ( trajet.numVilleD = '" + numVille + "' OR trajet.numVilleF = '" + numVille + "' )";
this.strCommande += " GROUP BY ville.nomVille ORDER BY " + param;
gridView.DataSource = serviceVille.getDataTableVille(this.strCommande);
gridView.DataBind();
#endregion
}
void IntfDalVille.insertToGridVilleRN(GridView gridView, string param, string paramLike, string valueLike, string routeNationale)
{
#region declaration
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
this.strCommande = "SELECT ville.numVille, ville.nomVille, ville.nomRegion, ville.nomProvince";
this.strCommande += " FROM ville Inner Join assovilleroutenationale ON assovilleroutenationale.numVille = ville.numVille";
this.strCommande += " WHERE " + paramLike + " LIKE '%" + valueLike + "%' AND";
this.strCommande += " assovilleroutenationale.routeNationale = '" + routeNationale + "'";
this.strCommande += " ORDER BY " + param;
gridView.DataSource = serviceVille.getDataTableVilleRN(this.strCommande);
gridView.DataBind();
#endregion
}
crlIndividu IntfDalIndividu.selectIndividu(string numIndividu)
{
#region declaration
crlIndividu Individu = null;
IntfDalVille serviceVille = new ImplDalVille();
#endregion
#region implementation
if (numIndividu != "")
{
this.strCommande = "SELECT * FROM `Individu` WHERE (`numIndividu`='" + numIndividu + "')";
this.serviceConnection.openConnection();
if (this.serviceConnection.IsConnection)
{
this.reader = this.serviceConnection.select(this.strCommande);
if (this.reader != null)
{
if (this.reader.HasRows)
{
if (this.reader.Read())
{
Individu = new crlIndividu();
Individu.NumIndividu = this.reader["numIndividu"].ToString();
Individu.CiviliteIndividu = this.reader["civiliteIndividu"].ToString();
Individu.NomIndividu = this.reader["nomIndividu"].ToString();
Individu.PrenomIndividu = this.reader["prenomIndividu"].ToString();
Individu.CinIndividu = this.reader["cinIndividu"].ToString();
Individu.Adresse = this.reader["adresse"].ToString();
Individu.Profession = this.reader["profession"].ToString();
Individu.TelephoneFixeIndividu = this.reader["telephoneFixeIndividu"].ToString();
Individu.TelephoneMobileIndividu = this.reader["telephoneMobileIndividu"].ToString();
try
{
Individu.DateNaissanceIndividu = Convert.ToDateTime(this.reader["dateNaissanceIndividu"].ToString());
}
catch (Exception)
{
}
Individu.LieuNaissanceIndividu = this.reader["lieuNaissanceIndividu"].ToString();
Individu.MailIndividu = this.reader["mailIndividu"].ToString();
Individu.NumQuartier = this.reader["numQuartier"].ToString();
try
{
Individu.IsCheque = int.Parse(this.reader["isCheque"].ToString());
}
catch (Exception)
{
}
try
{
Individu.IsBonCommande = int.Parse(this.reader["isBonCommande"].ToString());
}
catch (Exception)
{
}
}
}
this.reader.Dispose();
}
while (this.serviceConnection.IsConnection)
{
this.serviceConnection.closeConnection();
}
}
}
#endregion
return(Individu);
}
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);
}
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);
}
DataTable IntfDalLicence.getDataTableLicence(string strRqst)
{
#region declaration
DataTable dataTable = new DataTable();
IntfDalVille serviceVille = new ImplDalVille();
IntfDalLicence serviceLicence = new ImplDalLicence();
crlLicence licence = null;
#endregion
#region implementation
#region initialisation du dataTable
dataTable = new DataTable();
dataTable.Columns.Add("numLicence", typeof(string));
dataTable.Columns.Add("matriculeVehicule", typeof(string));
dataTable.Columns.Add("zone", typeof(string));
dataTable.Columns.Add("valideAu", typeof(DateTime));
dataTable.Columns.Add("nombrePlacePayante", typeof(string));
dataTable.Columns.Add("itineraire1", typeof(string));
dataTable.Columns.Add("itineraire2", 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();
licence = serviceLicence.selectLicence(reader["numLicence"].ToString());
dr["numLicence"] = reader["numLicence"].ToString();
dr["matriculeVehicule"] = reader["matriculeVehicule"].ToString();
dr["zone"] = reader["zone"].ToString();
try
{
dr["valideAu"] = Convert.ToDateTime(reader["valideAu"].ToString());
}
catch (Exception)
{
}
dr["nombrePlacePayante"] = reader["nombrePlacePayante"].ToString();
if (licence != null)
{
if (licence.itineraires != null)
{
if (licence.itineraires.Count > 0)
{
if (licence.itineraires != null)
{
dr["itineraire1"] = licence.itineraires[0].villeD.NomVille + "-" + licence.itineraires[0].villeF.NomVille;
}
else
{
dr["itineraire1"] = "";
}
}
else
{
dr["itineraire1"] = "";
}
if (licence.itineraires.Count > 1)
{
if (licence.itineraires != null)
{
dr["itineraire2"] = licence.itineraires[1].villeD.NomVille + "-" + licence.itineraires[1].villeF.NomVille;
}
else
{
dr["itineraire2"] = "";
}
}
else
{
dr["itineraire2"] = "";
}
}
else
{
dr["itineraire1"] = "";
dr["itineraire2"] = "";
}
}
dataTable.Rows.Add(dr);
}
}
this.reader.Dispose();
}
this.serviceConnectBase.closeConnection();
#endregion
return(dataTable);
}
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 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);
}
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);
}
