public static ObservableCollection <Materiel> GetFilteredMateriel(int SelectedClient, int SelectedSite)
{
List <Materiel> result = new List <Materiel>();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GestionMatos"].ToString()))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("Select * from materiel join site on materiel.id_site=site.id_site join client on materiel.id_client=client.id_client where client.id_client=@selectedclient AND site.id_site = @selectedsite", conn))
{
cmd.Parameters.AddWithValue("@selectedclient", SelectedClient);
cmd.Parameters.AddWithValue("@selectedsite", SelectedSite);
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Materiel filteredmateriels = new Materiel();
filteredmateriels.Idm = Convert.ToInt32(rdr["id_materiel"]);
filteredmateriels.NomMateriel = rdr["Nom"].ToString();
result.Add(filteredmateriels);
}
}
}
conn.Close();
}
var oc = new ObservableCollection <Materiel>();
result.ForEach(x => oc.Add(x));
return(oc);
}
public static ObservableCollection <Materiel> GetMateriel()
{
List <Materiel> result = new List <Materiel>();
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["GestionMatos"].ToString()))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand("Select * from materiel join site on materiel.id_site=site.id_site join client on materiel.id_client=client.id_client", conn))
{
using (SqlDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
Materiel materiel = new Materiel();
materiel.Idm = Convert.ToInt32(rdr["id_materiel"]);
materiel.NomMateriel = rdr["Nom"].ToString();
materiel.Description = rdr["Description"].ToString();
materiel.Numero = Convert.ToInt32(rdr["Numero"]);
materiel.MTBF = Convert.ToInt32(rdr["MTBF"]);
materiel.NomSite = rdr["Nom_site"].ToString();
materiel.NomClient = rdr["Nom_client"].ToString();
materiel.Dateinterv = Convert.ToDateTime(rdr["date_interv"]);
result.Add(materiel);
}
}
}
conn.Close();
}
var oc = new ObservableCollection <Materiel>();
result.ForEach(x => oc.Add(x));
return(oc);
}
private static void Update(Materiel materiel, SqlConnection conn, SqlTransaction tran)
{
using (SqlCommand cmd = new SqlCommand("update materiel set Nom = @name, Description = @des, Numero = @num, MTBF = @mtbf, where id_materiel = @id", conn, tran))
{
cmd.Parameters.AddWithValue("@id", materiel.Idm);
cmd.Parameters.AddWithValue("@name", materiel.NomMateriel);
cmd.Parameters.AddWithValue("@des", materiel.Description);
cmd.Parameters.AddWithValue("@num", materiel.Numero);
cmd.Parameters.AddWithValue("@mtbf", materiel.MTBF);
cmd.Parameters.AddWithValue("@dint", materiel.Dateinterv);
cmd.Parameters.AddWithValue("@idint", materiel.Idinterv);
cmd.ExecuteNonQuery();
}
}
private static void Insert(Materiel materiel, SqlConnection conn, SqlTransaction tran)
{
int mtbf = materiel.MTBF;
DateTime myDateTime = DateTime.Now;
string sqlFormattedDate = myDateTime.ToString("yyyy-MM-dd");
using (SqlCommand cmd = new SqlCommand("insert into materiel(Nom, Description, Numero, MTBF, id_site, id_client, date_interv) values (@name, @des, @num, @mtbf, @idsite, @idclient, (DATEADD(year,@mtbf,@formatedDate)))", conn, tran))
{
cmd.Parameters.AddWithValue("@id", materiel.Idm);
cmd.Parameters.AddWithValue("@name", materiel.NomMateriel);
cmd.Parameters.AddWithValue("@des", materiel.Description);
cmd.Parameters.AddWithValue("@num", materiel.Numero);
cmd.Parameters.AddWithValue("@mtbf", materiel.MTBF);
cmd.Parameters.AddWithValue("@idsite", materiel.Idsite);
cmd.Parameters.AddWithValue("@idclient", materiel.Idclient);
cmd.Parameters.AddWithValue("@formatedDate", sqlFormattedDate);
cmd.ExecuteNonQuery();
}
}
