//SqlDataReader dr = null;
public void innitialiseConnect()
{
try
{
ap.connect();
con = new SqlConnection(ap.chemin);
}
catch (Exception)
{
throw new Exception("l'un de vos fichiers de configuration est incorrect");
}
}
public void chargementEns(DataGridView data1)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select matriculeEns,NomEns,postnomEns,prenomEns,sexeEns,Mail,numtel,Domaine,qualification,etacivil from Enseignant", myconn);
adpt1.Fill(table);
data1.DataSource = table;
myconn.Close();
}
public void chargement_horaire(GridControl data1, string Annee)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select Top 100 codeaffect,codeclasse,classe,codeop,optioneleve,codejours, design_jours, ccours,heure_debut, heure_fin, codeens, NomEns, postnomEns,codeanne, code_section, section,affectation, annee, RefEcole, nomEcol from horaire where codeanne = '" + Annee + "' ", myconn);
adpt1.Fill(table);
data1.DataSource = table;
myconn.Close();
}
public void chargementsection(DataGridView data1)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("SELECT * FROM section", myconn);
adpt1.Fill(table);
data1.DataSource = table;
myconn.Close();
}
public void chargementreste(DataGridView data1)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select * from total_paiement", myconn);
adpt1.Fill(table);
data1.DataSource = table;
myconn.Close();
}
public void chargementprevision(DataGridView data1)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select Top 100 codeprev,montantprev,codeanne,annee,codecl,codefrais,frais,codeoption,optioneleve,codesection,section,RefEcole,nomEcol from liste_prevision", myconn);
adpt1.Fill(table);
data1.DataSource = table;
myconn.Close();
}
public void chargement_detail_horaire(DataGridView data1)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select codedetail,lundi,mardi,mercredi,jeudi,vendredi,samedi from Detail_horaire ", myconn);
adpt1.Fill(table);
data1.DataSource = table;
myconn.Close();
}
public void chargementpaiement(GridControl data1, string Annee)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select Top 100 * from view_paiement_vrai where codennee= '" + Annee + "' ", myconn);
adpt1.Fill(table);
data1.DataSource = table;
myconn.Close();
}
public DataTable chargement_quartier()
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select * from quartier", myconn);
adpt1.Fill(table);
myconn.Close();
return(table);
}
public DataTable chargement_historique_bibliothque()
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select * from historique_biblitheque", myconn);
adpt1.Fill(table);
myconn.Close();
return(table);
}
//========================================================================================
//PROCEDURE DE LA MISE A JOURS
public DataTable chargement_mise_a_jours()
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("exec mise_a_jours", myconn);
adpt1.Fill(table);
myconn.Close();
return(table);
}
public DataTable chargement_utilisateur()
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("select code,nom,fonction,pass from utilisateur1", myconn);
adpt1.Fill(table);
myconn.Close();
return(table);
}
public DataTable chargementEns_message()
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("SELECT matriculeEns,NomEns,postnomEns,prenomEns,numtel FROM Enseignant ", myconn);
adpt1.Fill(table);
myconn.Close();
return(table);
}
// GESTION DE LA CAISSE
//=========================================================================================
public DataTable chargement_solde_caisse()
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter(" SELECT * FROM return_solde()", myconn);
adpt1.Fill(table);
myconn.Close();
return(table);
}
public DataTable chargementeleve_message(string Annee)
{
connexion ap = new connexion();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
DataTable table = new DataTable();
adpt1 = new SqlDataAdapter("SELECT * FROM viewEleveMessagerie where annee= '" + Annee + "' ", myconn);
adpt1.Fill(table);
myconn.Close();
return(table);
}
public void login_user(string nom, string password, string fonction_l)
{
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
//cn.Open();
SqlCommand cmd = new SqlCommand("select * from utilisateur1 where nom='" + nom + "'and pass='******'and fonction='" + fonction_l + "'", myconn);
SqlDataReader dr;
dr = cmd.ExecuteReader();
int count = 0; string fonction = "";
while (dr.Read())
{
fonction = dr["fonction"].ToString();
count += 1;
}
if (count == 1)
{
MessageBox.Show("La connection a reussie !!!!!!");
UserSession.GetInstance().UserName = nom;
UserSession.GetInstance().AccessLevel = fonction;
Ecole1 f1 = new Ecole1();
f1.Show();
Visible = false;
}
else if (count > 1)
{
MessageBox.Show("duplicate");
}
else
{
MessageBox.Show("Echec de connection !!!!!!!!!");
}
//username
}
// ===============================================================================================
public void mergeenseignant(string codeel, string nom, string postnom, string prenom, string sexe, string mail, string num, string domaine, string qualif, string etat, Image im1)
{
try
{
MemoryStream ms = new MemoryStream();
Bitmap bmpImage = new Bitmap(im1);
byte[] bytImage;
bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
bytImage = ms.ToArray();
ms.Close();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
mycomm = new SqlCommand("exec mergeenseignant1 @a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@o", myconn);
mycomm.Parameters.AddWithValue("@a", codeel);
mycomm.Parameters.AddWithValue("@b", nom);
mycomm.Parameters.AddWithValue("@c", postnom);
mycomm.Parameters.AddWithValue("@d", prenom);
mycomm.Parameters.AddWithValue("@e", sexe);
mycomm.Parameters.AddWithValue("@f", mail);
mycomm.Parameters.AddWithValue("@g", num);
mycomm.Parameters.AddWithValue("@h", domaine);
mycomm.Parameters.AddWithValue("@i", qualif);
mycomm.Parameters.AddWithValue("@j", etat);
mycomm.Parameters.AddWithValue("@o", bytImage);
mycomm.ExecuteNonQuery();
MessageBox.Show("Sauvegarde reussie !!!", "Successfully", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Erreur de sauvegarde" + ex, "Erreur", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
//==========================================================================================================
// IDENTIFICATION DE L'ELEVE
public void mergeeleve(string codeel, string nom, string postnom, string prenom, string sexe, string datenaiss, string avenue, string quartier, string commune, string ville, string nation, string tutaire, string profession, string numtutaire, Image im1, string lieunaiss)
{
try
{
DateTime datenaiss1 = DateTime.Parse(datenaiss);
//DateTime dtsortie = DateTime.Parse(datesortie.Text);
MemoryStream ms = new MemoryStream();
Bitmap bmpImage = new Bitmap(im1);
byte[] bytImage;
bmpImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
bytImage = ms.ToArray();
ms.Close();
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
mycomm = new SqlCommand("exec mergeeleve1 @a,@b,@c,@d,@e,@f,@g,@h,@i,@j,@k,@l,@m,@n,@o,@p", myconn);
mycomm.Parameters.AddWithValue("@a", codeel);
mycomm.Parameters.AddWithValue("@b", nom);
mycomm.Parameters.AddWithValue("@c", postnom);
mycomm.Parameters.AddWithValue("@d", prenom);
mycomm.Parameters.AddWithValue("@e", sexe);
mycomm.Parameters.AddWithValue("@f", datenaiss1);
mycomm.Parameters.AddWithValue("@g", avenue);
mycomm.Parameters.AddWithValue("@h", quartier);
mycomm.Parameters.AddWithValue("@i", commune);
mycomm.Parameters.AddWithValue("@j", ville);
mycomm.Parameters.AddWithValue("@k", nation);
mycomm.Parameters.AddWithValue("@l", tutaire);
mycomm.Parameters.AddWithValue("@m", profession);
mycomm.Parameters.AddWithValue("@n", numtutaire);
mycomm.Parameters.AddWithValue("@o", bytImage);
mycomm.Parameters.AddWithValue("@p", lieunaiss);
mycomm.ExecuteNonQuery();
MessageBox.Show("Sauvegarde reussie !!!", "Successfully", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
}
catch (Exception ex)
{
//MessageBox.Show("Erreur de sauvegarde !!!" + ex.Message, "Erreur", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}
public void insertion_detail_horaire(string lundi, string mardi, string mercredi, string jeudi, string vendredi, string samedi)
{
try
{
ap.connect();
myconn = new SqlConnection(ap.chemin);
myconn.Open();
mycomm = new SqlCommand("INSERT INTO Detail_horaire(lundi,mardi,mercredi,jeudi,vendredi,samedi)VALUES(@a,@b,@c,@d,@e,@f)", myconn);
mycomm.Parameters.AddWithValue("@a", lundi);
mycomm.Parameters.AddWithValue("@b", mardi);
mycomm.Parameters.AddWithValue("@c", mercredi);
mycomm.Parameters.AddWithValue("@d", jeudi);
mycomm.Parameters.AddWithValue("@e", vendredi);
mycomm.Parameters.AddWithValue("@f", samedi);
mycomm.ExecuteNonQuery();
MessageBox.Show("Sauvegarde reussie !!!", "Successfully", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show("Erreur de sauvegarde" + ex, "Erreur", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
}
}