public string AddFrais(Frais frais)
{
try
{
this.connection.Open();
MySqlCommand cmd = this.connection.CreateCommand();
cmd.CommandText = $"INSERT INTO frais (employe_id, intitule, montant, devise, date, note_frais_id) VALUES ({frais.employe_id}, '{frais.intitule}', {frais.montant.ToString(CultureInfo.InvariantCulture)}, '{frais.devise}', '{frais.date:yyyy-MM-dd HH:mm:ss}' , {frais.note_frais_id})";
cmd.ExecuteNonQuery();
this.connection.Close();
return("ok");
}
catch (Exception e)
{
Console.WriteLine($"Generic Exception Handler: {e}");
return(e.Message);
}
}
public string UpdateFrais(Frais frais)
{
try
{
this.connection.Open();
MySqlCommand cmd = this.connection.CreateCommand();
cmd.CommandText = $"UPDATE frais SET employe_id = {frais.employe_id}, intitule = '{frais.intitule}', montant = {frais.montant.ToString(CultureInfo.InvariantCulture)}, devise = '{frais.devise}', date = '{frais.date:yyyy-MM-dd HH:mm:ss}', statut = '{frais.statut}', motif = '{frais.motif}', note_frais_id = {frais.note_frais_id} WHERE id = {frais.id}";
cmd.ExecuteNonQuery();
this.connection.Close();
return("ok");
}
catch (Exception e)
{
Console.WriteLine($"Generic Exception Handler: {e}");
return(e.Message);
}
}
public List <Frais> GetEmployeFraisPerYearMonth(int employe_id, int annee, int mois)
{
List <Frais> fraisList = new List <Frais>();
try
{
this.connection.Open();
MySqlCommand cmd = this.connection.CreateCommand();
//TODO gérer les années
cmd.CommandText = $"SELECT * FROM Frais WHERE employe_id={employe_id} AND date >='{new DateTime(annee, mois, 1):u}' AND date<'{new DateTime(annee, mois, 1).AddMonths(1):u}'";
// Exécution de la commande SQL
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
// Récupérez l'indexe (index) de colonne Emp_ID dans l'instruction de requête SQL.
Frais frais = new Frais(reader.GetInt32("id"),
reader.GetInt32("employe_id"),
reader.GetString("intitule"),
reader.GetFloat("montant"),
reader.GetString("devise"),
reader.GetDateTime("date"),
reader.GetInt32("note_frais_id"),
reader.GetString("statut"),
reader.GetString("motif"));
fraisList.Add(frais);
}
}
}
// Fermeture de la connexion
this.connection.Close();
}
catch (Exception e)
{
Console.WriteLine($"Generic Exception Handler: {e}");
}
return(fraisList);
}
public List <Frais> GetAllFrais()
{
List <Frais> fraisList = new List <Frais>();
try
{
this.connection.Open();
MySqlCommand cmd = this.connection.CreateCommand();
cmd.CommandText = "SELECT * FROM Frais";
// Exécution de la commande SQL
using (MySqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
// Récupérez l'indexe (index) de colonne Emp_ID dans l'instruction de requête SQL.
Frais frais = new Frais(reader.GetInt32("id"),
reader.GetInt32("employe_id"),
reader.GetString("intitule"),
reader.GetFloat("montant"),
reader.GetString("devise"),
reader.GetDateTime("date"),
reader.GetInt32("note_frais_id"),
reader.GetString("statut"),
reader.GetString("motif"));
fraisList.Add(frais);
}
}
}
// Fermeture de la connexion
this.connection.Close();
}
catch (Exception e)
{
Console.WriteLine($"Generic Exception Handler: {e}");
}
return(fraisList);
}