string IntfDalLicence.insertLicence(crlLicence Licence, string sigleAgence)
{
#region declaration
int nombreInsertion = 0;
string numLicence = "";
IntfDalLicence serviceLicence = new ImplDalLicence();
#endregion
#region implementation
if (Licence != null)
{
Licence.NumLicence = serviceLicence.getNumLicence(sigleAgence);
this.strCommande = "INSERT INTO `licence` (`numLicence`,`numerosLicence`,`zone`,`numCooperative`,";
this.strCommande += " `numVehicule`,`datePremiereMiseCiculation`,`datePremiereExploitation`,";
this.strCommande += " `valideAu`,`valideDu`,`nombrePlacePayante`,`isProvisoire`)";
this.strCommande += " VALUES ('" + Licence.NumLicence + "','" + Licence.NumerosLicence + "'";
this.strCommande += " ,'" + Licence.Zone + "','" + Licence.NumCooperative + "'";
this.strCommande += " ,'" + Licence.NumVehicule + "', '" + Licence.DatePremiereMiseCiculation.ToString("yyyy-MM-dd") + "'";
this.strCommande += " ,'" + Licence.DatePremiereExploitation.ToString("yyyy-MM-dd") + "','" + Licence.ValideAu.ToString("yyyy-MM-dd") + "'";
this.strCommande += " ,'" + Licence.ValideDu.ToString("yyyy-MM-dd") + "','" + Licence.NombrePlacePayante + "'";
this.strCommande += " ,'" + Licence.IsProvisoire + "')";
this.serviceConnectBase.openConnection();
nombreInsertion = this.serviceConnectBase.requete(this.strCommande);
if (nombreInsertion == 1)
{
numLicence = Licence.NumLicence;
}
this.serviceConnectBase.closeConnection();
}
#endregion
return(numLicence);
}
bool IntfDalLicence.deleteLicence(crlLicence Licence)
{
#region declaration
bool isDelete = false;
int nombreDelete = 0;
#endregion
#region implementation
if (Licence != null)
{
if (Licence.NumLicence != "")
{
this.strCommande = "DELETE FROM `licence` WHERE (`numLicence` = '" + Licence.NumLicence + "')";
this.serviceConnectBase.openConnection();
nombreDelete = this.serviceConnectBase.requete(this.strCommande);
if (nombreDelete == 1)
{
isDelete = true;
}
this.serviceConnectBase.closeConnection();
}
}
#endregion
return(isDelete);
}
int IntfDalLicence.isLicence(crlLicence Licence)
{
#region declaration
int isLicence = 0;
#endregion
#region implementation
if (Licence != null)
{
this.strCommande = "SELECT * FROM `licence` WHERE (`numLicence`<>'" + Licence.NumLicence + "')";
this.serviceConnectBase.openConnection();
reader = this.serviceConnectBase.select(this.strCommande);
if (reader != null)
{
if (reader.HasRows)
{
while (reader.Read())
{
if (Licence.NumerosLicence.Trim().ToLower().Equals(reader["numerosLicence"].ToString().Trim().ToLower()))
{
isLicence = 1;
break;
}
}
}
reader.Dispose();
}
this.serviceConnectBase.closeConnection();
}
#endregion
return(isLicence);
}
bool IntfDalLicence.updateLicence(crlLicence Licence)
{
#region declaration
bool isUpdate = false;
int nombreUpdate = 0;
#endregion
#region implementation
if (Licence != null)
{
if (Licence.NumLicence != "")
{
this.strCommande = "UPDATE `licence` SET `numerosLicence`='" + Licence.NumerosLicence + "'";
this.strCommande += ",`zone`='" + Licence.Zone + "',`numCooperative`='" + Licence.NumCooperative + "'";
this.strCommande += ",`numVehicule`='" + Licence.NumVehicule + "',`datePremiereMiseCiculation`='" + Licence.DatePremiereMiseCiculation.ToString("yyyy-MM-dd") + "'";
this.strCommande += ",`datePremiereExploitation`='" + Licence.DatePremiereExploitation.ToString("yyyy-MM-dd") + "',`valideAu`='" + Licence.ValideAu.ToString("yyyy-MM-dd") + "'";
this.strCommande += ",`valideDu`='" + Licence.ValideDu.ToString("yyyy-MM-dd") + "',`nombrePlacePayante`='" + Licence.NombrePlacePayante + "'";
this.strCommande += ",`isProvisoire`='" + Licence.IsProvisoire + "' WHERE (`numLicence`='" + Licence.NumLicence + "')";
this.serviceConnectBase.openConnection();
nombreUpdate = this.serviceConnectBase.requete(this.strCommande);
if (nombreUpdate == 1)
{
isUpdate = true;
}
this.serviceConnectBase.closeConnection();
}
}
#endregion
return(isUpdate);
}
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);
}
public crlVoiture()
{
this.Couleur = "";
this.Marque = "";
this.NombrePlace = 0;
this.NumImmatricule = "";
this.NumLicence = "";
this.NumVehicule = "";
this.Type = "";
this.Colone = 0;
this.Licence = null;
}
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);
}
bool IntfDalLicence.updateLicenceUS(crlLicence Licence)
{
#region declaration
bool isUpdate = false;
int nombreUpdate = 0;
string numCooperative = "NULL";
#endregion
#region implementation
if (Licence != null)
{
if (Licence.NumLicence != "")
{
if (Licence.NumCooperative != "")
{
numCooperative = "'" + Licence.NumCooperative + "'";
}
this.strCommande = "UPDATE `licence` SET `numerosLicence`='" + Licence.NumerosLicence + "'";
this.strCommande += ",`zone`='" + Licence.Zone + "',`numCooperative`=" + numCooperative;
this.strCommande += ",`numVehicule`='" + Licence.NumVehicule + "',`nombrePlacePayante`='" + Licence.NombrePlacePayante + "'";
this.strCommande += " WHERE (`numLicence`='" + Licence.NumLicence + "')";
this.serviceConnectBase.openConnection();
nombreUpdate = this.serviceConnectBase.requete(this.strCommande);
if (nombreUpdate == 1)
{
isUpdate = true;
}
this.serviceConnectBase.closeConnection();
}
}
#endregion
return(isUpdate);
}
string IntfDalLicence.insertLicenceUS(crlLicence Licence, string sigleAgence)
{
#region declaration
int nombreInsertion = 0;
string numLicence = "";
string numCooperative = "NULL";
IntfDalLicence serviceLicence = new ImplDalLicence();
#endregion
#region implementation
if (Licence != null)
{
if (Licence.NumCooperative != "")
{
numCooperative = "'" + Licence.NumCooperative + "'";
}
Licence.NumLicence = serviceLicence.getNumLicence(sigleAgence);
this.strCommande = "INSERT INTO `licence` (`numLicence`,`numerosLicence`,`zone`,`numCooperative`,";
this.strCommande += " `numVehicule`,`nombrePlacePayante`)";
this.strCommande += " VALUES ('" + Licence.NumLicence + "','" + Licence.NumerosLicence + "'";
this.strCommande += " ,'" + Licence.Zone + "'," + numCooperative;
this.strCommande += " ,'" + Licence.NumVehicule + "','" + Licence.NombrePlacePayante + "')";
this.serviceConnectBase.openConnection();
nombreInsertion = this.serviceConnectBase.requete(this.strCommande);
if (nombreInsertion == 1)
{
numLicence = Licence.NumLicence;
}
this.serviceConnectBase.closeConnection();
}
#endregion
return(numLicence);
}
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);
}
crlLicence IntfDalLicence.selectLicence(string numLicence)
{
#region declaration
IntfDalLicence serviceLicence = new ImplDalLicence();
List <crlItineraire> Itineraires = null;
crlLicence Licence = null;
IntfDalCooperative serviceCooperative = new ImplDalCooperative();
IntfDalZone serviceZone = new ImplDalZone();
IntfDalVehicule serviceVehicule = new ImplDalVehicule();
#endregion
#region implementation
if (numLicence != "")
{
Itineraires = serviceLicence.selectItineraire(numLicence);
this.strCommande = "SELECT * FROM licence WHERE (licence.numLicence = '" + numLicence + "')";
this.serviceConnectBase.openConnection();
reader = this.serviceConnectBase.select(this.strCommande);
if (reader != null)
{
if (reader.HasRows)
{
if (reader.Read())
{
Licence = new crlLicence();
Licence.NumLicence = reader["numLicence"].ToString();
Licence.NumerosLicence = reader["numerosLicence"].ToString();
Licence.Zone = reader["zone"].ToString();
Licence.NumCooperative = reader["numCooperative"].ToString();
Licence.NumVehicule = reader["numVehicule"].ToString();
try
{
Licence.DatePremiereMiseCiculation = Convert.ToDateTime(reader["datePremiereMiseCiculation"].ToString());
}
catch (Exception) { }
try
{
Licence.DatePremiereExploitation = Convert.ToDateTime(reader["datePremiereExploitation"].ToString());
}
catch (Exception) { }
try
{
Licence.ValideAu = Convert.ToDateTime(reader["valideAu"].ToString());
}
catch (Exception)
{
}
try
{
Licence.ValideDu = Convert.ToDateTime(reader["valideDu"].ToString());
}
catch (Exception)
{
}
try
{
Licence.NombrePlacePayante = int.Parse(reader["nombrePlacePayante"].ToString());
}
catch (Exception)
{
}
try
{
Licence.IsProvisoire = int.Parse(reader["isProvisoire"].ToString());
}
catch (Exception)
{
}
}
}
reader.Dispose();
}
this.serviceConnectBase.closeConnection();
if (Licence != null)
{
if (Licence.Zone != "")
{
Licence.zoneObj = serviceZone.selectZone(Licence.Zone);
}
if (Licence.NumCooperative != "")
{
Licence.cooperative = serviceCooperative.selectCooperative(Licence.NumCooperative);
}
if (Licence.NumVehicule != "")
{
Licence.vehicule = serviceVehicule.selectVehicule(Licence.NumVehicule);
}
Licence.itineraires = serviceLicence.selectItineraire(Licence.NumLicence);
}
}
#endregion
return(Licence);
}
