private void bdList()
{
SqlConnection sqlCon = new SqlConnection("Data Source=HP250G1\\SQLEXPRESS;Integrated Security=true;");
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT name FROM sys.databases " +
"WHERE name not in (\'master\',\'tempdb\',\'model\',\'msdb\')";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
comboBox1.Items.Add(SqlDR.GetString(0));
}
comboBox1.SelectedIndex = 0;
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
comboBox2.Items.Clear();
SqlConnection sqlCon = new SqlConnection("Data Source=HP250G1\\SQLEXPRESS;Database=\"" +
comboBox1.Text + "\";Integrated Security=true;");
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT name FROM sys.database_principals " +
"WHERE (type <> \'r\') AND (name NOT IN (\'dbo\', \'sys\', \'INFORMATION_SCHEMA\'))";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
comboBox2.Items.Add(SqlDR.GetString(0));
}
if (comboBox2.Items.Count > 0)
{
comboBox2.SelectedIndex = 0;
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
/// <summary>
/// Load the database names from the selected instance
/// </summary>
private void LoadDatabases()
{
this.Cursor = Cursors.WaitCursor;
if (cn == null || cn.State != ConnectionState.Open)
{
InitConnection();
}
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = cn;
SqlCom.CommandType = CommandType.Text;
SqlCom.CommandText = "select name from sys.databases";
System.Data.SqlClient.SqlDataReader SqlDR;
using (SqlDR = SqlCom.ExecuteReader())
{
comboBoxDatabases.Items.Clear();
while (SqlDR.Read())
{
comboBoxDatabases.Items.Add(SqlDR.GetString(0));
}
}
if (comboBoxDatabases.Items.Count == 0)
{
comboBoxDatabases.DropDownStyle = ComboBoxStyle.DropDown;
}
else
{
comboBoxDatabases.DropDownStyle = ComboBoxStyle.DropDownList;
}
this.Cursor = Cursors.Default;
}
public static List <Employe> GetEmployes()
{
List <Employe> lstEmployes = new List <Employe>();
if (ConnexionBD.Instance().EstConnecte())
{
string requete = "SELECT c.nom nm, c.prenom prm, numEmploye numEmp, p.nom poste, e.idEmploye id " +
"FROM employes e " +
"INNER JOIN postes p ON p.idPoste = e.idPoste " +
"INNER JOIN citoyens c ON c.idCitoyen = e.idCitoyen;";
ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
{
lstEmployes.Add(new Employe
{
idEmploye = SqlDR.GetInt16("id"),
NumEmploye = SqlDR.GetString("numEmp"),
Nom = SqlDR.GetString("nm"),
Prenom = SqlDR.GetString("prm"),
Poste = SqlDR.GetString("poste")
});
});
}
/*SELECT c.nom, c.prenom, numEmploye, p.nom
* FROM employes e
* INNER JOIN postes p ON p.idPoste = e.idPoste
* INNER JOIN citoyens c ON c.idCitoyen = e.idCitoyen;*/
return(lstEmployes);
}
private void tableList()
{
SqlConnection sqlCon = new SqlConnection(Login.dbConStr);
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT name FROM sysobjects " +
"WHERE type = \'U\' and name != \'sysdiagrams\' ";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
checkedListBox2.Items.Add(SqlDR.GetString(0));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
public static bool existQuart(QuartEmploye quart)
{
QuartEmploye qe = null;
if (ConnexionBD.Instance().EstConnecte())
{
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT date dt, dep.nom nmDep, shift.periode typeQ " +
"FROM quarts qe " +
"INNER JOIN periodesjournee shift on shift.idPeriodeJournee = qe.idPeriodeJournee " +
"INNER JOIN departements dep on dep.idDepartement = qe.idDepartement " +
"WHERE qe.date = '" + quart.Date.ToShortDateString() + "' && shift.periode = '" + quart.TypeDeQuart + "' && dep.nom = '" + quart.DepartementAssocie.Nom + "';"
, SqlDR =>
{
qe = new QuartEmploye
{
Date = SqlDR.GetDateTime("dt"),
DepartementAssocie = new Departement
{
Nom = SqlDR.GetString("nmDep")
},
TypeDeQuart = (TypeQuart)System.Enum.Parse(typeof(TypeQuart), SqlDR.GetString("typeQ"))
};
});
}
return(qe != null);
}
private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
{
checkedListBox1.Items.Clear();
SqlConnection sqlCon = new SqlConnection(Login.dbConStr);
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT sc.name FROM syscolumns sc " +
"inner join sysobjects so ON sc.id = so.id " +
"WHERE so.name = \'" + listBox2.Text + "\'";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
checkedListBox1.Items.Add(SqlDR.GetString(0));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
}
public static void GetUnCitoyenParLit(Lit lit)
{
Citoyen citoyen = new Citoyen();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.nom nomGenre, c.dateNaissance dtNaiss, c.adresse uneAdresse, c.telephone numTel " +
"FROM citoyens c " +
"INNER JOIN genres g ON g.idGenre = c.idGenre " +
"INNER JOIN lits l ON l.idCitoyen = c.idCitoyen " +
"WHERE l.idLit = '" + lit._identifiant + "' "
, SqlDR => {
citoyen.Nom = SqlDR.GetString("nomCit");
citoyen.Prenom = SqlDR.GetString("prenomCit");
citoyen.AssMaladie = SqlDR.GetString("AssMal");
citoyen.Genre = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("nomGenre"));
citoyen.DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("dtNaiss");
citoyen.Adresse = SqlDR.GetString("uneAdresse");
citoyen.NumTelephone = SqlDR.GetString("numTel");
}
);
}
lit.Citoyen = citoyen;
}
public static Citoyen GetUnCitoyen(Citoyen citoyen)
{
// On crée un citoyen venant de la BD
Citoyen InfosCitoyen = new Citoyen();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.nom nomGenre, c.dateNaissance dtNaiss, c.adresse uneAdresse, c.telephone numTel " +
"FROM citoyens c " +
"INNER JOIN genres g ON g.idGenre = c.idGenre " +
"WHERE c.numAssuranceMaladie = '" + citoyen.AssMaladie + "' "
, SqlDR => {
InfosCitoyen.Nom = SqlDR.GetString("nomCit");
InfosCitoyen.Prenom = SqlDR.GetString("prenomCit");
InfosCitoyen.AssMaladie = SqlDR.GetString("AssMal");
InfosCitoyen.Genre = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("nomGenre"));
InfosCitoyen.DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("dtNaiss");
InfosCitoyen.Adresse = SqlDR.GetString("uneAdresse");
InfosCitoyen.NumTelephone = SqlDR.GetString("numTel");
}
);
}
return(InfosCitoyen);
}
public static List <Employe> GetLstEmployesDepartement(Departement depSelectionne)
{
List <Employe> lstEmployes = new List <Employe>();
if (ConnexionBD.Instance().EstConnecte())
{
string requete = "SELECT c.nom nomCit, c.prenom prenomCit, e.numEmploye nEmploye " +
"FROM citoyens c " +
"INNER JOIN employes e ON e.idCitoyen = c.idCitoyen " +
"INNER JOIN quartsEmployes qe ON qe.idEmploye = e.idEmploye " +
"INNER JOIN quarts q ON q.idQuart = qe.idQuart " +
"INNER JOIN departements d ON d.idDepartement = q.idDepartement " +
"WHERE d.nom = '" + depSelectionne.Nom + "' " +
"GROUP BY nEmploye " +
"ORDER BY nomCit ";
ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
{
lstEmployes.Add(new Employe
{
Nom = SqlDR.GetString("nomCit"),
Prenom = SqlDR.GetString("prenomCit"),
NumEmploye = SqlDR.GetString("nEmploye"),
});
});
}
return(lstEmployes);
}
// On rend static la fonction pour être en mesure de l'utiliser partout
public static List <Prescription> GetPrescriptionsCitoyens(String NumAssMaladie)
{
// On crée une liste de citoyen venant de la BD
List <Prescription> lstPrescriptions = new List <Prescription>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT produit prod, posologie poso, prescriptions.dateDebut Ddebut, nbJour nbj " +
"FROM prescriptions " +
"INNER JOIN evenements e on e.idEvenement = prescriptions.idEvenement " +
"INNER JOIN hospitalisations h on h.idHospitalisation = e.idHospitalisation " +
"INNER JOIN citoyens c on c.idCitoyen = h.idCitoyen " +
"WHERE c.numAssuranceMaladie = '" + NumAssMaladie +
"' AND (prescriptions.dateDebut + INTERVAL nbJour DAY >= CURDATE() OR nbJour = 0) "
, SqlDR => {
lstPrescriptions.Add(new Prescription
{
Produit = SqlDR.GetString("prod"),
Posologie = SqlDR.GetString("poso"),
DateDebut = SqlDR.GetDateTime("Ddebut"),
NbJour = SqlDR.GetUInt16("nbj")
});
}
);
}
return(lstPrescriptions);
}
// ****** FÉLIX, tu peux utiliser cette requête pour l'infirmière-chef!! *******
// On rend static la fonction pour être en mesure de l'utiliser partout
public static List <Citoyen> GetTousCitoyensDepartement(Departement departementSelectionne)
{
// On crée une liste de citoyen venant de la BD
List <Citoyen> lstCitoyen = new List <Citoyen>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, c.dateNaissance DNaissance, c.telephone Tel, c.Adresse AdresseCit, " +
"g.nom NomGenre, " +
"d.nom nomDep, " +
"ch.nom nomCh, " +
"l.numero numeroLit, " +
"el.nom EtLitNom " +
"FROM citoyens c " +
"INNER JOIN genres g ON g.idGenre = c.idGenre " +
"INNER JOIN lits l ON l.idCitoyen = c.idCitoyen " +
"INNER JOIN etatslits el ON el.idEtatLit = l.idEtatLit " +
"INNER JOIN chambres ch ON ch.idChambre = l.idChambre " +
"INNER JOIN departements d ON d.idDepartement = ch.idDepartement " +
"WHERE d.nom = '" + departementSelectionne.Nom + "' "
, SqlDR => {
lstCitoyen.Add(new Citoyen
{
Nom = SqlDR.GetString("nomCit"),
Prenom = SqlDR.GetString("prenomCit"),
AssMaladie = SqlDR.GetString("AssMal"),
Genre = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("NomGenre")),
DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("DNaissance"),
NumTelephone = SqlDR.GetString("Tel"),
Adresse = SqlDR.GetString("AdresseCit"),
Lit = new Lit
{
Numero = SqlDR.GetString("numeroLit"),
EtatLit = (EtatLit)Enum.Parse(typeof(EtatLit), SqlDR.GetString("EtLitNom")),
Chambre = new Chambre
{
Numero = SqlDR.GetString("nomCh"),
UnDepartement = new Departement
{
Nom = SqlDR.GetString("nomDep"),
}
}
}
});
}
);
}
return(lstCitoyen);
}
/// <summary>
/// Load the srid of the selected data table
/// </summary>
private void LoadSRID()
{
if (cn == null || cn.State != ConnectionState.Open)
{
InitConnection();
}
if (cn.Database != comboBoxDatabases.Text)
{
cn.ChangeDatabase(comboBoxDatabases.Text);
}
string[] tableName = comboBoxDataTable.Text.Trim().Split(new char[] { '.' });
string geomCol = comboBoxGeomCol.Text.ToLower().Replace("(geometry)", "").Replace("(geography)", "").Trim();
if (geomCol.Length == 0)
{
return;
}
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = cn;
SqlCom.CommandType = CommandType.Text;
if (tableName.Length > 1)
{
SqlCom.CommandText = "select top 1 [" + geomCol + "].STSrid, [" + geomCol + "].STGeometryType() from [" + tableName[0] + "].[" + tableName[1] + "] where [" + geomCol + "] is not null";
}
else
{
return;
}
this.Cursor = Cursors.WaitCursor;
System.Data.SqlClient.SqlDataReader SqlDR;
using (SqlDR = SqlCom.ExecuteReader())
{
while (SqlDR.Read())
{
if (!SqlDR.IsDBNull(0))
{
textBoxSRID.Text = SqlDR.GetInt32(0).ToString();
}
else
{
textBoxSRID.Text = "";
}
geometryType = SqlDR.GetString(1);
break;
}
}
this.Cursor = Cursors.Default;
}
private void BD_Load(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(strCon);
SqlCon.Open();
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
List <String> listBD = new List <String>();
while (SqlDR.Read())
{
listBD.Add(SqlDR.GetString(0));
}
SqlCon.Close();
treeViewList = new List <TreeViewItem>();
int pi = 0;
int id = 1;
foreach (String list in listBD)
{
SqlCon.Open();
SqlCommand cmd = new SqlCommand();
SqlDataReader reader;
cmd.CommandText = "SELECT * FROM " + list + ".INFORMATION_SCHEMA.TABLES;";
cmd.CommandType = CommandType.Text;
cmd.Connection = SqlCon;
reader = cmd.ExecuteReader();
treeViewList.Add(new TreeViewItem()
{
ParentID = 0, ID = id, Text = list
});
while (reader.Read())
{
treeViewList.Add(new TreeViewItem()
{
ParentID = id, ID = id + 10000, Text = reader.GetString(2)
});
}
id++;
SqlCon.Close();
}
PopulateTreeView(0, null);
}
private void checkedListBox2_SelectedIndexChanged(object sender, EventArgs e)
{
checkedListBox1.Items.Clear();
SqlConnection sqlCon = new SqlConnection(Login.dbConStr);
try
{
SqlCommand sqlCom = new SqlCommand();
sqlCom.CommandType = CommandType.Text;
sqlCom.CommandText = "SELECT sc.name FROM syscolumns sc " +
"inner join sysobjects so ON sc.id = so.id " +
"WHERE so.name = \'" + checkedListBox2.Text + "\'";
sqlCom.Connection = sqlCon;
sqlCon.Open();
SqlDataReader SqlDR;
SqlDR = sqlCom.ExecuteReader();
while (SqlDR.Read())
{
checkedListBox1.Items.Add(SqlDR.GetString(0));
}
}
catch (SqlException ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
sqlCon.Close();
}
Load = true;
for (int i = 0; i < table.Count; i++)
{
if (table[i].name == checkedListBox2.Items[checkedListBox2.SelectedIndex].ToString())
{
for (int j = 0; j < table[i].column.Count; j++)
{
for (int r = 0; r < checkedListBox1.Items.Count; r++)
{
if (table[i].column[j] == checkedListBox1.Items[r].ToString())
{
checkedListBox1.SetItemChecked(r, true);
break;
}
}
}
break;
}
}
Load = false;
}
// On rend static la fonction pour être en mesure de l'utiliser partout
public static List <Citoyen> GetCitoyens()
{
// On crée une liste de citoyen venant de la BD
List <Citoyen> lstCitoyen = new List <Citoyen>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
// Ça plante, puisque des éléments sont null comme le lit, la chambre et le département...
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.Nom GenreCit, c.dateNaissance DNaiss, c.telephone Tel, c.adresse AdresseCit, d.nom nomDep, ch.nom nomCh, l.numero numeroLit, el.nom EtLitNom " +
"FROM citoyens c " +
"INNER JOIN genres g ON g.idGenre = c.idGenre " +
"LEFT JOIN lits l ON l.idCitoyen = c.idCitoyen " +
"LEFT JOIN etatslits el ON el.idEtatLit = l.idEtatLit " +
"LEFT JOIN chambres ch ON ch.idChambre = l.idChambre " +
"LEFT JOIN departements d ON d.idDepartement = ch.idDepartement "
, SqlDR => {
lstCitoyen.Add(new Citoyen
{
Nom = SqlDR.GetString("nomCit"),
Prenom = SqlDR.GetString("prenomCit"),
AssMaladie = SqlDR.GetString("AssMal"),
Genre = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("GenreCit")),
DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("DNaiss"),
NumTelephone = SqlDR.GetString("Tel"),
Adresse = SqlDR.GetString("AdresseCit"),
Lit = SqlDR.IsDBNull(SqlDR.GetOrdinal("numeroLit")) ? new Lit() : new Lit
{
Numero = SqlDR.GetString("numeroLit"),
EtatLit = (EtatLit)Enum.Parse(typeof(EtatLit), SqlDR.GetString("EtLitNom")),
Chambre = new Chambre
{
Numero = SqlDR.GetString("nomCh"),
UnDepartement = new Departement
{
Nom = SqlDR.GetString("nomDep"),
}
}
}
});
}
);
}
return(lstCitoyen);
}
public static List <Citoyen> GetCitoyenDemandeTraitement(Departement departement)
{
List <Citoyen> lstCitoyen = new List <Citoyen>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT c.nom nomCit, c.prenom prenomCit, c.numAssuranceMaladie AssMal, g.nom nomGenre, c.dateNaissance dtNaiss, c.adresse uneAdresse, c.telephone numTel, l.idCitoyen litCit, l.numero litNum, ch.nom chNum " +
"FROM citoyens c " +
"INNER JOIN genres g ON g.idGenre = c.idGenre " +
"LEFT JOIN lits l ON l.idCitoyen = c.idCitoyen " +
"LEFT JOIN chambres ch ON ch.idChambre = l.idChambre " +
"INNER JOIN hospitalisations h ON h.idCitoyen = c.idCitoyen " +
"INNER JOIN hospitalisationstraitements ht ON ht.idHospitalisation = h.idHospitalisation " +
"INNER JOIN traitements t ON t.idTraitement = ht.idTraitement " +
"INNER JOIN departements d ON d.idDepartement = t.idDepartement " +
"WHERE d.nom = '" + departement.Nom + "' " +
"AND ht.estEnCours = true "
, SqlDR => {
lstCitoyen.Add(new Citoyen
{
Nom = SqlDR.GetString("nomCit"),
Prenom = SqlDR.GetString("prenomCit"),
AssMaladie = SqlDR.GetString("AssMal"),
Genre = (Genre)Enum.Parse(typeof(Genre), SqlDR.GetString("nomGenre")),
DateNaissance = (DateTime)SqlDR.GetMySqlDateTime("dtNaiss"),
Adresse = SqlDR.GetString("uneAdresse"),
NumTelephone = SqlDR.GetString("numTel"),
Lit = SqlDR.IsDBNull(SqlDR.GetOrdinal("litCit")) ? new Lit() : new Lit
{
Numero = SqlDR.GetString("litNum"),
Chambre = new Chambre
{
Numero = SqlDR.GetString("chNum")
}
}
});
}
);
}
lstCitoyen.RemoveAll(citoyen => citoyen.Lit.Numero != null);
return(lstCitoyen);
}
public static List <QuartEmploye> GetHoraire(Employe employe)
{
List <QuartEmploye> horaire = new List <QuartEmploye>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT date dt, cit.nom nm, cit.prenom pm, dep.nom nmDep, shift.periode typeQ " +
"FROM quartsemployes qe " +
"INNER JOIN quarts q on qe.idQuart = q.idQuart " +
"INNER JOIN employes em on em.idEmploye = qe.idEmploye " +
"INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen " +
"INNER JOIN periodesjournee shift on shift.idPeriodeJournee = q.idPeriodeJournee " +
"INNER JOIN departements dep on dep.idDepartement = q.idDepartement " +
"WHERE em.idEmploye = " + employe.idEmploye + ";"
, SqlDR => {
horaire.Add(new QuartEmploye
{
TypeDeQuart = (TypeQuart)System.Enum.Parse(typeof(TypeQuart), SqlDR.GetString("typeQ")),
Date = SqlDR.GetDateTime("dt"),
DepartementAssocie = new Departement
{
Nom = SqlDR.GetString("nmDep")
},
Employe = new Employe
{
Nom = SqlDR.GetString("nm"),
Prenom = SqlDR.GetString("pm")
}
});
});
}
/*SELECT date, cit.nom, prenom, dep.nom, periode
* FROM quartsEmployes qe
* INNER JOIN quarts q on qe.idQuart = q.idQuart
* INNER JOIN employes em on em.idEmploye = qe.idEmploye
* INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen
* INNER JOIN periodesjournee shift on shift.idPeriodeJournee = q.idPeriodeJournee
* INNER JOIN departements dep on dep.idDepartement = q.idDepartement
* WHERE qe.idEmploye = 4;*/
return(horaire);
}
public static void SetIdEmployeUsagerConnecte()
{
Employe employe = new Employe();
if (ConnexionBD.Instance().EstConnecte())
{
string requete = "SELECT e.idEmploye emp, p.nom pos FROM Employes e " +
"INNER JOIN Postes p ON p.idPoste = e.idPoste " +
"INNER JOIN Usagers us ON e.idEmploye = us.idEmploye " +
"WHERE us.nomUtilisateur = '" + UsagerConnecte.Usager.NomUtilisateur + "' ";
ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
{
UsagerConnecte.Usager.idEmploye = SqlDR.GetInt32("emp");
UsagerConnecte.Usager.Poste = SqlDR.GetString("pos");
});
}
}
public static List <ResultatLabo> GetResultatsLaboCitoyens(String NumAssMaladie, DateTime dateDebut)
{
// On crée une liste de citoyen venant de la BD
List <ResultatLabo> lstResultatLabo = new List <ResultatLabo>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
string test = dateDebut.ToString();
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT resultatslabo.nomAnalyse nA, resultatslabo.lienImage lIm, e.dateHeure dthr, cit.nom nm, cit.prenom pm, em.numEmploye numEm " +
"FROM resultatslabo " +
"INNER JOIN evenements e on e.idEvenement = resultatslabo.idEvenement " +
"INNER JOIN hospitalisations h on h.idHospitalisation = e.idHospitalisation " +
"INNER JOIN citoyens c on c.idCitoyen = h.idCitoyen " +
"INNER JOIN employes em on em.idEmploye = e.idEmploye " +
"INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen " +
"WHERE c.numAssuranceMaladie = '" + NumAssMaladie + "' AND h.dateDebut = '" + dateDebut.ToString() + "'"
, SqlDR =>
{
lstResultatLabo.Add(new ResultatLabo
{
NomAnalyse = SqlDR.GetString("nA"),
LienImage = SqlDR.GetString("lIm"),
DateEvenement = SqlDR.GetDateTime("dthr"),
EmployeImplique = new Employe
{
Nom = SqlDR.GetString("nm"),
Prenom = SqlDR.GetString("pm"),
NumEmploye = SqlDR.GetString("numEm")
}
});
});
foreach (ResultatLabo result in lstResultatLabo)
{
result.Resultats = new BitmapImage(new Uri(result.LienImage));
}
}
return(lstResultatLabo);
}
public static int GetidEmploye(Employe employeRecherche)
{
int idEmployeTrouve = new int();
if (ConnexionBD.Instance().EstConnecte())
{
string requete = "SELECT idEmploye idEmp FROM Employes e " +
"JOIN Citoyens c ON c.idCitoyen = e.idCitoyen " +
"WHERE e.numEmploye = '" + employeRecherche.NumEmploye + "' ";
ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
{
idEmployeTrouve = SqlDR.GetInt32("idEmp");
});
}
return(idEmployeTrouve);
}
private void LoadDatabaseName()
{
try
{
string server = cbServer.Text.Trim();
SqlConnection SqlCon = new SqlConnection(@"server=.;integrated security=true;");
SqlCon.Open();
SqlCommand SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
string strSystemDB = "master,model,msdb,tempdb";
cbDatabase.Items.Clear();
while (SqlDR.Read())
{
string dbName = SqlDR.GetString(0);
if (strSystemDB.Contains(dbName) == false)
{
cbDatabase.Items.Add(dbName);
}
}
if (cbDatabase.Items.Count > 0)
{
cbDatabase.SelectedIndex = 0;
btnSave.Enabled = true;
}
else
{
cbDatabase.Items.Add("Không có CSDL nào");
cbDatabase.SelectedIndex = 0;
btnSave.Enabled = false;
}
}
catch (Exception ex)
{
XtraMessageBox.Show("Lỗi:" + ex.Message, "Thông Báo", MessageBoxButtons.OK, MessageBoxIcon.Error);
btnSave.Enabled = true;
}
}
// On rend static la fonction pour être en mesure de l'utiliser partout
public static List <NoteInfirmiere> GetNotesInfirmiereCitoyens(String NumAssMaladie, DateTime dateDebut)
{
// On crée une liste de citoyen venant de la BD
List <NoteInfirmiere> lstNoteInfirmiere = new List <NoteInfirmiere>();
List <Employe> lstEmp = new List <Employe>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
string test = dateDebut.ToString();
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT notesInfirmiere.note nte, e.dateHeure dthr, cit.nom nm, cit.prenom pm, em.numEmploye numEm " +
"FROM notesInfirmiere " +
"INNER JOIN evenements e on e.idEvenement = notesInfirmiere.idEvenement " +
"INNER JOIN hospitalisations h on h.idHospitalisation = e.idHospitalisation " +
"INNER JOIN citoyens c on c.idCitoyen = h.idCitoyen " +
"INNER JOIN employes em on em.idEmploye = e.idEmploye " +
"INNER JOIN citoyens cit on cit.idCitoyen = em.idCitoyen " +
"WHERE c.numAssuranceMaladie = '" + NumAssMaladie + "' AND h.dateDebut = '" + dateDebut.ToString() + "'"
, SqlDR =>
{
lstNoteInfirmiere.Add(new NoteInfirmiere
{
NotesInf = SqlDR.GetString("nte"),
DateEvenement = SqlDR.GetDateTime("dthr"),
EmployeImplique = new Employe
{
Nom = SqlDR.GetString("nm"),
Prenom = SqlDR.GetString("pm"),
NumEmploye = SqlDR.GetString("numEm")
}
});
});
}
return(lstNoteInfirmiere);
}
public void LlenarComboBox()
{
System.Data.SqlClient.SqlConnection SqlCon = new System.Data.SqlClient.SqlConnection(CCadena);
SqlCon.Open();
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
List <String> listBD = new List <String>();
while (SqlDR.Read())
{
comboBox1.Items.Add(SqlDR.GetValue(0));
}
}
public static List <Departement> GetNomsDepartements()
{
List <Departement> lstDepartement = new List <Departement>();
if (ConnexionBD.Instance().EstConnecte())
{
ConnexionBD.Instance().ExecuterRequete(
"SELECT nom nm " +
"FROM departements"
, SqlDR => {
lstDepartement.Add(new Departement
{
Nom = SqlDR.GetString("nm")
});
}
);
}
return(lstDepartement);
}
/// <summary>
/// Load the table names of the selected database
/// </summary>
private void LoadDataTables()
{
this.Cursor = Cursors.WaitCursor;
if (cn == null || cn.State != ConnectionState.Open)
{
InitConnection();
}
if (cn.Database != comboBoxDatabases.Text)
{
cn.ChangeDatabase(comboBoxDatabases.Text);
}
System.Data.SqlClient.SqlCommand SqlCom = new System.Data.SqlClient.SqlCommand();
SqlCom.Connection = cn;
SqlCom.CommandType = CommandType.Text;
SqlCom.CommandText = "select TABLE_SCHEMA,TABLE_NAME from INFORMATION_SCHEMA.Tables";
System.Data.SqlClient.SqlDataReader SqlDR;
using (SqlDR = SqlCom.ExecuteReader())
{
comboBoxDataTable.Items.Clear();
while (SqlDR.Read())
{
comboBoxDataTable.Items.Add(SqlDR.GetString(0) + "." + SqlDR.GetString(1));
}
}
if (comboBoxDataTable.Items.Count == 0)
{
comboBoxDataTable.DropDownStyle = ComboBoxStyle.DropDown;
}
else
{
comboBoxDataTable.DropDownStyle = ComboBoxStyle.DropDownList;
}
this.Cursor = Cursors.Default;
}
private void btnConnect_Click(object sender, EventArgs e)
{
try
{
if (string.IsNullOrEmpty(txtServer.Text) || string.IsNullOrEmpty(txtUID.Text) || string.IsNullOrEmpty(txtPassword.Text))
{
MessageBox.Show("Please enter the required details.");
IsConnected = false;
}
else
{
lstDbs.Items.Clear();
SqlConnection SqlCon = new SqlConnection("server=" + txtServer.Text + ";uid=" + txtUID.Text + ";pwd=" + txtPassword.Text);
SqlCon.Open();
SqlCommand SqlCom = new SqlCommand();
SqlCom.Connection = SqlCon;
SqlCom.CommandType = CommandType.StoredProcedure;
SqlCom.CommandText = "sp_databases";
System.Data.SqlClient.SqlDataReader SqlDR;
SqlDR = SqlCom.ExecuteReader();
while (SqlDR.Read())
{
lstDbs.Items.Add(SqlDR.GetString(0));
}
SqlDR.Close();
SqlCon.Close();
IsConnected = true;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
IsConnected = false;
}
}
public static List <Departement> GetDepartements()
{
List <Departement> lstDepartement = new List <Departement>();
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
List <int> idEmployesChefs = new List <int>();
// Si oui, on execute la requête que l'on veut effectuer
// SqlDR (MySqlDataReader) emmagasine une liste des citoyens de la BD
ConnexionBD.Instance().ExecuterRequete(
"SELECT * " +
"FROM departements"
, SqlDR => {
lstDepartement.Add(new Departement
{
_identifiant = int.Parse(SqlDR.GetString("idDepartement")),
Nom = SqlDR.GetString("nom"),
Abreviation = SqlDR.GetString("abreviation")
});
idEmployesChefs.Add(SqlDR.IsDBNull(SqlDR.GetOrdinal("idEmploye")) ? -1 : int.Parse(SqlDR.GetString("idEmploye")));
}
);
for (int i = 0; i < lstDepartement.Count; i++)
{
lstDepartement[i].PersonnelMedicalEnChef = (idEmployesChefs[i] == -1 ? null : DataModelEmploye.GetEmploye(idEmployesChefs[i]));
}
for (int i = 0; i < lstDepartement.Count; i++)
{
lstDepartement[i].Chambres = new ObservableCollection <Chambre>(DataModelChambre.GetChambres(lstDepartement[i]._identifiant.ToString(), "lits, equipements"));
}
}
return(lstDepartement);
}
public static Employe GetEmploye(int idEmp)
{
Employe employe = new Employe();
if (ConnexionBD.Instance().EstConnecte())
{
string requete = "SELECT nom, prenom, numEmploye FROM Employes e " +
"JOIN Citoyens c ON c.idCitoyen = e.idCitoyen " +
"WHERE e.idEmploye = " + idEmp + ";";
ConnexionBD.Instance().ExecuterRequete(requete, SqlDR =>
{
employe = new Employe
{
NumEmploye = SqlDR.GetString("numEmploye"),
Nom = SqlDR.GetString("nom"),
Prenom = SqlDR.GetString("prenom")
};
});
}
return(employe);
}
public static Departement GetDepartementInfChef(Employe employe)
{
Departement departement = new Departement();
int idEmployeRecherche = DataModelEmploye.GetidEmploye(employe);
// On vérifie si la BD est connecté
if (ConnexionBD.Instance().EstConnecte())
{
ConnexionBD.Instance().ExecuterRequete(
"SELECT d.idDepartement _id, d.nom depNom, d.abreviation depAbrev " +
"FROM departements d " +
"WHERE d.idEmploye = " + idEmployeRecherche + " "
, SqlDR => {
departement._identifiant = int.Parse(SqlDR.GetString("_id"));
departement.Nom = SqlDR.GetString("depNom");
departement.Abreviation = SqlDR.GetString("depAbrev");
}
);
}
return(departement);
}
