string IntfDalFacture.insertFactureAssoc(crlFacture Facture)
{
#region declaration
string numFacture = "";
IntfDalFacture serviceFacture = new ImplDalFacture();
IntfDalAutorisationDepart serviceAutorisationDepart = new ImplDalAutorisationDepart();
#endregion
#region implementation
if (Facture != null)
{
if (Facture.autorisationDeparts != null)
{
Facture.NumFacture = serviceFacture.insertFacture(Facture);
if (Facture.NumFacture != "")
{
for (int i = 0; i < Facture.autorisationDeparts.Count; i++)
{
serviceFacture.insertAssocFactureAD(Facture.NumFacture, Facture.autorisationDeparts[i].NumAutorisationDepart);
Facture.autorisationDeparts[i].ResteRegle = 0;
serviceAutorisationDepart.updateAutorisationDepart(Facture.autorisationDeparts[i]);
}
numFacture = Facture.NumFacture;
}
}
}
#endregion
return(numFacture);
}
void IntfDalFacture.insertToGridFactureNotRecu(GridView gridView, string param, string paramLike, string valueLike)
{
#region declaration
IntfDalFacture serviceFacture = new ImplDalFacture();
#endregion
#region implementation
this.strCommande = "SELECT facture.numFacture, facture.libele, facture.montant, facture.dateFacturation, facture.matriculeAgent,";
this.strCommande += " Individu.nomIndividu, Individu.prenomIndividu, societe.nomSociete, organisme.nomOrganisme, Individu.civiliteIndividu,";
this.strCommande += " Individu.cinIndividu, Individu.adresse, organisme.adresseOrganisme, societe.adresseSociete FROM facture";
this.strCommande += " Left Join recuad ON recuad.numFacture = facture.numFacture";
this.strCommande += " Inner Join assocautorisationdepartfacture ON assocautorisationdepartfacture.numFacture = facture.numFacture";
this.strCommande += " Inner Join autorisationdepart ON autorisationdepart.numAutorisationDepart = assocautorisationdepartfacture.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 += " Inner Join proprietaire ON proprietaire.numProprietaire = vehicule.numProprietaire";
this.strCommande += " Left Join Individu ON Individu.numIndividu = proprietaire.numIndividu";
this.strCommande += " Left Join societe ON societe.numSociete = proprietaire.numSociete";
this.strCommande += " Left Join organisme ON organisme.numOrganisme = proprietaire.numOrganisme";
this.strCommande += " WHERE recuad.numRecuAD IS NULL AND";
this.strCommande += " " + paramLike + " LIKE '%" + valueLike + "%'";
this.strCommande += " GROUP BY facture.numFacture ORDER BY " + param + " ASC";
gridView.DataSource = serviceFacture.getDataTableFactureNotRecu(this.strCommande);
gridView.DataBind();
#endregion
}
string IntfDalFacture.insertFacture(crlFacture Facture)
{
#region declaration
IntfDalFacture serviceFacture = new ImplDalFacture();
int nombreInsertion = 0;
string numFacture = "";
#endregion
#region implementation
if (Facture != null)
{
Facture.NumFacture = serviceFacture.getNumFacture(Facture.agent.agence.SigleAgence);
this.strCommande = "INSERT INTO `facture` (`numFacture`,`libele`,`montant`,`dateFacturation`,`matriculeAgent`)";
this.strCommande += " VALUES ('" + Facture.NumFacture + "', '" + Facture.Libele + "', ";
this.strCommande += " '" + Facture.Montant + "', '" + Facture.DateFacturation.ToString("yyyy-MM-dd") + "','" + Facture.MatriculeAgent + "')";
this.serviceConnectBase.openConnection();
nombreInsertion = this.serviceConnectBase.requete(this.strCommande);
if (nombreInsertion == 1)
{
numFacture = Facture.NumFacture;
}
this.serviceConnectBase.closeConnection();
}
#endregion
return(numFacture);
}
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);
}
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);
}
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);
}
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);
}
