public static void SupprimerMedicament(int lid)
{
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
MySqlCommand cmd3 = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd3 = connexion.CreateCommand();
//REQUETE SQL
cmd3.CommandText = "DELETE FROM `offrir` WHERE idMedicamentOff = '" + lid + "' ";
//EXECUTION REQUETE SQL
MySqlDataReader drr3 = cmd3.ExecuteReader();
drr3.Close();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "DELETE FROM medicament WHERE idMedicament = '" + lid + "' ";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
public static int recupererIdSpe(string laSpe)
{
int idSpe = 0;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT `idSpec` " +
"FROM `specialite` WHERE `libSpec` = '" + laSpe + "'";
//EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
if (drr.Read())
{
//ON RECUPERE LES VARIABLES
idSpe = drr.GetInt16(0);
}
drr.Close();
connexion.Close();
return(idSpe);
}
public static int recupererIdSpe(string libelle)
{
int id = 0;
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
cmd.CommandText = "SELECT idSpec FROM specialite WHERE libSpec = '" + libelle + "' ";
MySqlDataReader drr = cmd.ExecuteReader();
while (drr.Read())
{
id = drr.GetInt16(0);
}
drr.Close();
connexion.Close();
return(id);
}
public static List <ClasseSpecialite> chargerLesSpecialite()
{
List <ClasseSpecialite> lesSpecialite = new List <ClasseSpecialite>();
int id;
string specialite;
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
cmd.CommandText = "SELECT * FROM specialite";
MySqlDataReader drr = cmd.ExecuteReader();
while (drr.Read())
{
id = drr.GetInt16(0);
specialite = drr.GetString(1);
ClasseSpecialite laSpecialite = new ClasseSpecialite(id, specialite);
lesSpecialite.Add(laSpecialite);
}
drr.Close();
connexion.Close();
return(lesSpecialite);
}
public static void ModifVisiteur(int lid, string lenom, string leprenom, string ladresse, string lelogin, string lemdp, string lecp, string laville, DateTime ladateEmbauche)
{
//Conversion de la date
string ladate = ladateEmbauche.ToString();
String jour = ladate.Substring(0, 2);
String mois = ladate.Substring(3, 2);
String Ans = ladate.Substring(6, 4);
string uneDate = Ans + "-" + mois + "-" + jour;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "UPDATE visiteur " +
"SET nomVis = '" + lenom + "', prenomVis = '" + leprenom + "', loginVis = '" + lelogin + "', mdpVis = '" + lemdp + "', adresseVis = '" + ladresse + "', cpVis = '" + lecp + "', villeVis = '" + laville + "', dateEmbaucheVis = '" + uneDate + "' " +
"WHERE visiteur.idVis = '" + lid + "'; ";
//EXECUTION REQUETE SQL
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
public static List <ClasseFamille> chargerLesFamilles()
{
List <ClasseFamille> lesFamille = new List <ClasseFamille>();
int id_famille;
string libelle;
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
cmd.CommandText = "SELECT * FROM famille";
MySqlDataReader drr = cmd.ExecuteReader();
while (drr.Read())
{
id_famille = int.Parse(drr.GetString(0));
libelle = drr.GetString(1);
ClasseFamille laFamille = new ClasseFamille(id_famille, libelle);
lesFamille.Add(laFamille);
}
drr.Close();
connexion.Close();
return(lesFamille);
}
// procédure qui modifie un rapport
public static void modifRapport(int idRap, DateTime dateRap, string motifRap, string bilanRap, int idVisiteurRap, int idMedecinRAp)
{
//Conversion de la date
string ladate = dateRap.ToString();
String jour = ladate.Substring(0, 2);
String mois = ladate.Substring(3, 2);
String Ans = ladate.Substring(6, 4);
string uneDate = Ans + "-" + mois + "-" + jour;
//Connexion BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "UPDATE `rapport` SET `dateRap`='" + uneDate + "',`motifRap`='" + motifRap + "',`bilanRap`=\"" + bilanRap + "\",`idVisiteurRap`='" + idVisiteurRap + "',`idMedecinRap`='" + idMedecinRAp + "' WHERE idRap ='" + idRap + "'";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
public static void AjoutVisiteur(string lenom, string leprenom, string ladresse, string lelogin, string lemdp, string lecp, string laville, DateTime ladateEmbauche)
{
//Conversion de la date
string ladate = ladateEmbauche.ToString();
String jour = ladate.Substring(0, 2);
String mois = ladate.Substring(3, 2);
String Ans = ladate.Substring(6, 4);
string uneDate = Ans + "-" + mois + "-" + jour;
// CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "INSERT INTO `visiteur` (`nomVis`, `prenomVis`, `loginVis`, `mdpVis`, `adresseVis`, `cpVis`, `villeVis`, `dateEmbaucheVis`) " +
"VALUES ('" + lenom + "', '" + leprenom + "', '" + lelogin + "', '" + lemdp + "', '" + ladresse + "', '" + lecp + "', '" + laville + "', '" + uneDate + "');";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
public static List <ClasseMedecin> ChargerLesRapportMedecin(int lid)
{
//VARIABLES
List <ClasseMedecin> leMedecin = new List <ClasseMedecin>();
int idMed;
string nomMed;
string prenomMed;
string adresseMed;
int idRap;
int idVis;
string nomVis;
string prenomVis;
DateTime dateRap;
string motifRap;
string bilanRap;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT nomMed, prenomMed, adresseMed, idRap, idVis, nomVis, prenomVis, dateRap, motifRap, bilanRap " +
"FROM visiteur INNER JOIN rapport ON rapport.idVisiteurRap = visiteur.idVis " +
"INNER JOIN medecin ON rapport.idMedecinRap = medecin.idMed " +
"WHERE medecin.idMed = '" + lid + "'";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
idMed = lid;
nomMed = drr.GetString(0);
prenomMed = drr.GetString(1);
adresseMed = drr.GetString(2);
idRap = drr.GetInt16(3);
idVis = drr.GetInt16(4);
nomVis = drr.GetString(5);
prenomVis = drr.GetString(6);
dateRap = drr.GetDateTime(7);
motifRap = drr.GetString(8);
bilanRap = drr.GetString(9);
//ON INSTANCIE UN TABLEAU
ClasseMedecin lesRap = new ClasseMedecin(idMed, nomMed, prenomMed, adresseMed, idRap, idVis, nomVis, prenomVis, dateRap, motifRap, bilanRap);
// ON AJOUTE UN OBJET CLASSEMEDECIN DANS UNE LISTE CLASSEMEDECIN
leMedecin.Add(lesRap);
}
drr.Close();
connexion.Close();
return(leMedecin);
}
// procédure qui ajoute un rapport
public static void ajoutRapport(DateTime dateRap, string motifRap, string bilanRap, int idVisiteurRap, int idMedecinRap)
{
//Conversion de la date
string ladate = dateRap.ToString();
String jour = ladate.Substring(0, 2);
String mois = ladate.Substring(3, 2);
String Ans = ladate.Substring(6, 4);
string uneDate = Ans + "-" + mois + "-" + jour;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "INSERT INTO rapport(dateRap, motifRap, bilanRap, idVisiteurRap, idMedecinRap) " +
"VALUES ('" + uneDate + "', '" + motifRap + "', '" + bilanRap + "', '" + idVisiteurRap + "', '" + idMedecinRap + "');";
MySqlDataReader drr = cmd.ExecuteReader(); //Execution du script
drr.Close();
connexion.Close();
}
public static int recupererIdFamille(string libelleFamille)
{
int idFamille = 0;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idFamille " +
"FROM famille " +
"WHERE libFamille = '" + libelleFamille + "'";
//EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
if (drr.Read())
{
//ON RECUPERE LES VARIABLES
idFamille = drr.GetInt16(0);
}
drr.Close();
connexion.Close();
return(idFamille);
}
public static int recupererIdMedicament(string nomMedicament)
{
int idMedicament = 0;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idMedicament " +
"FROM medicament " +
"WHERE nomCommercialMedicament = '" + nomMedicament + "' ";
//EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
if (drr.Read())
{
//ON RECUPERE LES VARIABLES
idMedicament = drr.GetInt16(0);
}
drr.Close();
connexion.Close();
return(idMedicament);
}
public static int chargerUnVisiteur(string nom, string prenom)
{
// variables
int id = 0;
//Connexion BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idVis " +
"FROM visiteur " +
"WHERE nomVis = '" + nom + "' && prenomVis = '" + prenom + "'";
//EXECUTION REQUETE SQL
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
id = drr.GetInt16(0);
}
drr.Close();
connexion.Close();
return(id);
}
public static void SupprimerVisiteur(int lid)
{
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
MySqlCommand cmd2 = new MySqlCommand();
MySqlCommand cmd3 = new MySqlCommand();
MySqlCommand cmd4 = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
List <int> lesRapports = new List <int>();
connexion.Open();
cmd4 = connexion.CreateCommand();
//REQUETE SQL
cmd4.CommandText = "SELECT `idRap` FROM `rapport` WHERE idVisiteurRap = '" + lid + "'";
//EXECUTION REQUETE SQL
MySqlDataReader drr4 = cmd4.ExecuteReader();
while (drr4.Read())
{
//ON RECUPERE LES VARIABLES
int idRapport = drr4.GetInt32(0);
lesRapports.Add(idRapport);
}
drr4.Close();
cmd3 = connexion.CreateCommand();
foreach (int idRapport in lesRapports)
{
//REQUETE SQL
cmd3.CommandText = "DELETE FROM offrir WHERE offrir.idRapportOff = '" + idRapport + "'";
//EXECUTION REQUETE SQL
MySqlDataReader drr3 = cmd3.ExecuteReader();
drr3.Close();
}
cmd2 = connexion.CreateCommand();
//REQUETE SQL
cmd2.CommandText = "DELETE FROM rapport WHERE rapport.idVisiteurRap = '" + lid + "'";
//EXECUTION REQUETE SQL
MySqlDataReader drr1 = cmd2.ExecuteReader();
drr1.Close();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "DELETE FROM visiteur WHERE visiteur.idVis = '" + lid + "'";
//EXECUTION REQUETE SQL
MySqlDataReader drr2 = cmd.ExecuteReader();
drr2.Close();
connexion.Close();
}
public static ClasseRapport chargerLeRapports(int idRapport)
{
//Variables
ClasseRapport unRapport = new ClasseRapport();
int idRap;
DateTime dateRap;
string motifRap;
string bilanRap;
int idVisiteurRap;
int idMedecinRap;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idRap, dateRap, motifRap, bilanRap, idVisiteurRap, idMedecinRap " +
"FROM rapport WHERE idRap = '" + idRapport + "' ";
//EXECUTION REQUETE SQL
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
idRap = drr.GetInt16(0);
dateRap = drr.GetDateTime(1);
motifRap = drr.GetString(2);
bilanRap = drr.GetString(3);
idVisiteurRap = int.Parse(drr.GetString(4));
idMedecinRap = int.Parse(drr.GetString(5));
//On récupère un objet visiteur avec la méthode chargerLeVisiteur
ClasseVisiteur leVisiteur = ClassePVisiteur.chargerLeVisiteur(idVisiteurRap);
//On récupère un objet Medecin avec la méthode chargerLeMedecin
ClasseMedecin leMedecin = ClassePMedecin.chargerLeMedecin(idMedecinRap);
// Instancie un échantillon
List <ClasseEchantillonOffert> lesEchantillonsOfferts = ClassePEchantillonOffert.chargerLesEchantillonOffert();
// Instancie un rapport
unRapport = new ClasseRapport(idRap, dateRap, motifRap, bilanRap, leVisiteur, leMedecin, lesEchantillonsOfferts);
}
//CLOTURE LA CONNEXION
drr.Close();
connexion.Close();
return(unRapport);
}
public static List <ClasseMedicament> chargerLaCI(string CI)
{
//VARIABLES
List <ClasseMedicament> LesMedicaments = new List <ClasseMedicament>();
int id;
string Nom;
string composition;
string effets;
string contreindications;
int idfamille;
string libFam;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idMedicament, nomCommercialMedicament, idFamilleMedicament, libFamille, compositionMedicament, effetsMedicament, contreIndicationsMedicament " +
"FROM medicament INNER JOIN famille " +
"ON famille.idFamille = medicament.idFamilleMedicament " +
"WHERE contreIndicationsMedicament = '" + CI + "'";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
id = int.Parse(drr.GetString(0));
Nom = drr.GetString(1);
idfamille = int.Parse(drr.GetString(2));
libFam = drr.GetString(3);
composition = drr.GetString(4);
effets = drr.GetString(5);
contreindications = drr.GetString(6);
//ON INSTANCIE UN OBJET CLASSEFAMILLE
ClasseFamille laFamille = new ClasseFamille(idfamille, libFam);
//ON INSTANCIE UN OBJET CLASSEMEDICAMENT
ClasseMedicament leMedicament = new ClasseMedicament(id, Nom, composition, effets, contreindications, laFamille);
//ON AJOUTE UN OBJET CLASSEMEDICAMENT DANS UNE LISTE CLASSEMEDICAMENT
LesMedicaments.Add(leMedicament);
}
drr.Close();
connexion.Close();
return(LesMedicaments);
}
public static List <ClasseVisiteur> chargerLesVisiteurs()
{
//VARIABLES
List <ClasseVisiteur> LesVisiteurs = new List <ClasseVisiteur>();
int id;
string nom;
string prenom;
string adresse;
string cp;
string ville;
string mdp;
string login;
DateTime dateEmbauche;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT nomVis, prenomVis, adresseVis, cpVis, villeVis, dateEmbaucheVis, idVis, loginVis, mdpVis " +
"FROM visiteur ";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
nom = drr.GetString(0);
prenom = drr.GetString(1);
adresse = drr.GetString(2);
cp = drr.GetString(3);
ville = drr.GetString(4);
dateEmbauche = drr.GetDateTime(5);
id = int.Parse(drr.GetString(6));
login = drr.GetString(7);
mdp = drr.GetString(8);
//ON INSTANCIE UN OBJET CLASSEVISITEUR
ClasseVisiteur leVisiteur = new ClasseVisiteur(id, nom, prenom, adresse, cp, ville, login, mdp, dateEmbauche);
//ON L'AJOUTE A UNE LISTE DE VISITEURS
LesVisiteurs.Add(leVisiteur);
}
drr.Close();
connexion.Close();
return(LesVisiteurs);
}
public static List <ClasseMedecin> chargerLesMedecins()
{
//VARIABLES
List <ClasseMedecin> LesMedecins = new List <ClasseMedecin>();
int idMed;
string nom;
string prenom;
string tel;
string adresse;
int idspecialite;
int departement;
string libSpec;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idMed, nomMed, prenomMed, adresseMed, telMed , idSpec, departementMed, libSpec " +
"FROM medecin INNER JOIN specialite ON specialite.idSpec = medecin.idSpecialiteMed ORDER BY nomMed ASC";
//EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
idMed = int.Parse(drr.GetString(0)); // 7 correspond à idMed
nom = drr.GetString(1); // 0 correspond à nomMed
prenom = drr.GetString(2); // 1 correspond à prenomMed
tel = drr.GetString(4); // 3 correspond à telMed
adresse = drr.GetString(3); // 2 correspond à adresseMed
departement = drr.GetInt16(6); //5 correspond à departementMed
idspecialite = int.Parse(drr.GetString(5)); //4 correspond à idSpec
libSpec = drr.GetString(7); //6 correspond à libSpec
//ON INSTANCIE UN OBJET CLASSESPECIALITE
ClasseSpecialite LaSpe = new ClasseSpecialite(idspecialite, libSpec);
// ON INSTANCIE UN OBJET CLASSEMEDECIN
ClasseMedecin leMedecin = new ClasseMedecin(idMed, nom, prenom, adresse, tel, departement, LaSpe);
// ON AJOUTE UN OBJET CLASSEMEDECIN DANS UNE LISTE CLASSEMEDECIN
LesMedecins.Add(leMedecin);
}
drr.Close();
connexion.Close();
return(LesMedecins); //retoune la liste des Medecins
}
public static ClasseVisiteur chargerLeVisiteur(int lid)
{
// variables
int id;
string nom;
string prenom;
string adresse;
string login;
string mdp;
string cp;
string ville;
DateTime dateEmbauche;
ClasseVisiteur leVisiteur = new ClasseVisiteur();
//Connexion BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT nomVis, prenomVis, loginVis, mdpVis, adresseVis, cpVis, villeVis, dateEmbaucheVis, idVis " +
"FROM visiteur " +
"WHERE idVis = '" + lid + "'";
//EXECUTION REQUETE SQL
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
nom = drr.GetString(0);
prenom = drr.GetString(1);
login = drr.GetString(2);
mdp = drr.GetString(3);
adresse = drr.GetString(4);
cp = drr.GetString(5);
ville = drr.GetString(6);
dateEmbauche = drr.GetDateTime(7);
id = drr.GetInt16(8);
//On instancie un objet ClasseVisiteur
leVisiteur = new ClasseVisiteur(id, nom, prenom, adresse, cp, ville, login, mdp, dateEmbauche);
}
drr.Close();
connexion.Close();
return(leVisiteur);
}
public static ClasseMedecin chargerLeMedecin(int lid)
{
//VARIABLES
ClasseMedecin leMedecin = new ClasseMedecin();
string nom;
string prenom;
string tel;
string adresse;
int idspecialite;
int departement;
string libSpec;
int idMed;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT nomMed, prenomMed, adresseMed, telMed , idSpec, departementMed, libSpec, idMed " +
"FROM medecin INNER JOIN specialite ON idSpecialiteMed " +
"WHERE idMed = '" + lid + "'";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
nom = drr.GetString(0);
prenom = drr.GetString(1);
tel = drr.GetString(3);
adresse = drr.GetString(2);
departement = drr.GetInt16(5);
idspecialite = int.Parse(drr.GetString(4));
libSpec = drr.GetString(6);
idMed = int.Parse(drr.GetString(7));
//ON INSTANCIE UN OBJET CLASSESPECIALITE
ClasseSpecialite LaSpe = new ClasseSpecialite(idspecialite, libSpec);
//ON INSTANCIE UN OBJET CLASSEMEDECIN
leMedecin = new ClasseMedecin(idMed, nom, prenom, adresse, tel, departement, LaSpe);
}
drr.Close();
connexion.Close();
return(leMedecin);
}
public static ClasseMedicament chargerLeMedicament(int idMedicament)
{
//VARIABLES
ClasseMedicament LeMedicament = new ClasseMedicament();
int id;
string nomcomposition;
string composition;
string effets;
string contreindications;
int idfamille;
string libFam;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idMedicament, nomCommercialMedicament, idFamilleMedicament, libFamille, compositionMedicament, effetsMedicament, contreIndicationsMedicament " +
"FROM medicament INNER JOIN famille ON idFamilleMedicament " +
"WHERE medicament.idFamilleMedicament = famille.idFamille && medicament.idMedicament = " + idMedicament + "";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
id = drr.GetInt16(0);
nomcomposition = drr.GetString(1);
idfamille = drr.GetInt16(2);
libFam = drr.GetString(3);
composition = drr.GetString(4);
effets = drr.GetString(5);
contreindications = drr.GetString(6);
//ON INSTANCIE UN OBJET CLASSEFAMILLE
ClasseFamille laFamille = new ClasseFamille(idfamille, libFam);
//ON INSTANCIE UN OBJET CLASSEMEDICAMENT
LeMedicament = new ClasseMedicament(id, nomcomposition, composition, effets, contreindications, laFamille);
}
drr.Close();
connexion.Close();
return(LeMedicament);
}
public static List <ClasseEchantillonOffert> chargerLesEchantillonOffert()
{
// VARIABLES
List <ClasseEchantillonOffert> LesEchantillonsOffert = new List <ClasseEchantillonOffert>();
int idRapport;
int idMedicament;
int quantite;
// CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
// REQUETE SQL
cmd.CommandText = "SELECT idRapportOff, idMedicamentOff, quantiteOff " +
"FROM offrir inner join medicament order by idRapportOff ASC";
// EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
// LECTURE DE LA REQUETE
while (drr.Read())
{
// ON RECUPERE LES VARIABLES
idRapport = int.Parse(drr.GetString(0));
idMedicament = int.Parse(drr.GetString(1));
quantite = drr.GetInt16(2);
// On récupère la liste des médicaments avec la méthode chargerLeMedicaments
ClasseMedicament leMedicament = ClassePMedicament.chargerLeMedicament(idMedicament);
// On récupére le rapport avec l'id et la méthode chargerLeRapports
ClasseRapport leRapport = ClassePRapport.chargerLeRapports(idRapport);
// Instancie un échantillon
ClasseEchantillonOffert lEchantillon = new ClasseEchantillonOffert(quantite, leMedicament, leRapport);
// Instancie un rapport
LesEchantillonsOffert.Add(lEchantillon);
}
drr.Close();
connexion.Close();
return(LesEchantillonsOffert);
}
public static void modifierMedecin(int idMed, string leNom, string lePrenom, string ladresse, string leTel, int idSpe, int leDepartement)
{
//Connexion à la BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
// Mise à jour de la table Médecin dans la BDD
cmd = connexion.CreateCommand();
cmd.CommandText = "UPDATE medecin SET nomMed='" + leNom + "', prenomMed='" + lePrenom + "', adresseMed='" + ladresse + "', telMed= '" + leTel + "', idSpecialiteMed= '" + idSpe + "', departementMed='" + leDepartement + "' WHERE idMed='" + idMed + "'";
MySqlDataReader drr = cmd.ExecuteReader(); //Execution du script
drr.Close();
connexion.Close();
}
public static void SupprimerMedecin(int lidMed)
{
//Connexion à la BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
//Supprimer un Medecin dans la Table Medecin
cmd = connexion.CreateCommand();
cmd.CommandText = "DELETE FROM `medecin` WHERE `idMed` = " + lidMed + " ";
MySqlDataReader drr = cmd.ExecuteReader(); //Execution du script
drr.Close();
connexion.Close();
}
public static List <ClasseEchantillonOffert> chargerLEchantillonOffert(int idRapport)
{
//VARIABLES
List <ClasseEchantillonOffert> LEchantillons = new List <ClasseEchantillonOffert>();
int idMedicament;
int quantite;
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "SELECT idMedicamentOff, quantiteOff " +
"FROM offrir " +
"WHERE idRapportOff = '" + idRapport + "'";
//EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
//LECTURE DE LA REQUETE
while (drr.Read())
{
//ON RECUPERE LES VARIABLES
idMedicament = drr.GetInt16(0);
quantite = drr.GetInt16(1);
//On récupère le médicaments avec la méthode chargerLeMedicament
ClasseMedicament leMedicament = ClassePMedicament.chargerLeMedicament(idMedicament);
// Instancie un échantillon
ClasseEchantillonOffert lEchantillon = new ClasseEchantillonOffert(quantite, leMedicament);
// Ajoute un échantillon dans la liste
LEchantillons.Add(lEchantillon);
}
drr.Close();
connexion.Close();
return(LEchantillons);
}
public static void ajouterMedecin(string leNom, string lePrenom, string ladresse, string leTel, int laSpe, int leDepartement)
{
//Connexion à la BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
//Ajout d'un Medecin dans la BDD
cmd = connexion.CreateCommand();
cmd.CommandText = "INSERT INTO medecin (nomMed, prenomMed, adresseMed, telMed, idSpecialiteMed, departementMed) " +
"VALUES ('" + leNom + "', '" + lePrenom + "', '" + ladresse + "', '" + leTel + "', '" + laSpe + "', '" + leDepartement + "');";
MySqlDataReader drr = cmd.ExecuteReader(); //Execution du script
drr.Close();
connexion.Close();
}
public static void AjoutEchantillonOffert(int idRapport, int idMedicament, int quantite)
{
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "INSERT INTO offrir (`idRapportOff`, `idMedicamentOff`, `quantiteOff`) " +
"VALUES ('" + idRapport + "', '" + idMedicament + "', '" + quantite + "');";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
public static void SupprimerEchantillonOffert(int idRapport, int idMedicament)
{
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "DELETE FROM offrir " +
"WHERE offrir.idRapportOff = '" + idRapport + "' && offrir.idMedicamentOff = '" + idMedicament + "'";
//EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
// procédure qui supprime un rapport grace a l'id
public static void supprimerRapport(int idRap)
{
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "DELETE FROM `rapport` WHERE `idRap` = " + idRap + " ";
//EXECUTION REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
public static void AjoutMedicament(string unnomcomposition, string unecomposition, string deseffets, string unecontreindications, int lafamille)
{
//CONNEXION BDD
MySqlConnection connexion = new MySqlConnection();
MySqlCommand cmd = new MySqlCommand();
connexion.ConnectionString = ClassePConnexion.DBConnection();
connexion.Open();
cmd = connexion.CreateCommand();
//REQUETE SQL
cmd.CommandText = "INSERT INTO medicament (nomCommercialMedicament, idFamilleMedicament, compositionMedicament, effetsMedicament, contreIndicationsMedicament)" +
"VALUES('" + unnomcomposition + "', '" + lafamille + "', '" + unecomposition + "', '" + deseffets + "', '" + unecontreindications + "')";
//EXECUTE LA REQUETE
MySqlDataReader drr = cmd.ExecuteReader();
drr.Close();
connexion.Close();
}
