public static bool ReinscriptionActivite(Activite uneActivite)
{
if (Utilisateur.EstVacancier())
{
string dateInscript = DateTime.Now.ToString("yyyy-MM-dd");
string dateAct = Convert.ToDateTime(uneActivite.DateAct).ToString("yyyy-MM-dd");
string dateAnnul = Convert.ToDateTime(uneActivite.DateAnnuleAct).ToString("yyyy-MM-dd");
string reqUpdte;
int nbMaj;
MySqlCommand cmd = conn.CreateCommand();
reqUpdte = "UPDATE inscription SET DATEANNULE = null " +
"WHERE DATEACT = '" + dateAct + "' " +
"AND USER = '******' " +
"AND CODEANIM = '" + uneActivite.CodeAnim + "' ;";
cmd.CommandText = reqUpdte;
nbMaj = cmd.ExecuteNonQuery();
if (nbMaj == 1)
{
return(true);
}
return(false);
}
return(false);
}
/// <summary>
/// Récupère une activité
/// </summary>
/// <param name="testActivite"></param>
/// <returns></returns>
public static Activite GetActivite(Activite testActivite)
{
{
string testDateAct = Convert.ToDateTime(testActivite.DateAct).ToString("yyyy-MM-dd");
MySqlDataReader rdrActivite;
MySqlCommand cmd = conn.CreateCommand();
string sqlActi = "SELECT a.CODEANIM, a.DATEACT, a.CODEETATACT, a.HRRDVACT, a.PRIXACT, a.HRDEBUTACT, " +
"a.HRFINACT, a.DATEANNULEACT, a.NOMRESP, a.PRENOMRESP " +
"FROM activite a, inscription i " +
"WHERE a.CODEANIM = i.CODEANIM " +
"AND a.DATEACT = i.DATEACT AND i.USER = '******'" +
"AND a.CODEETATACT != 'AN'" +
"AND i.DATEANNULE IS NULL" +
"AND a.DATEACT = '" + testDateAct + "'" +
"AND a.CODEANIM = '" + testActivite.CodeAnim + "'";
cmd.CommandText = sqlActi;
rdrActivite = cmd.ExecuteReader();
List <Activite> listeActivite = new List <Activite>();
string codeAnim;
string dateAct; // !! format DATE dans la BDD !!
string codeEtatAct;
string hrRdvAct;
Decimal prixActe; //voir comment utiliser le type Decimal (dans le programme et la BDD)
string hrDebutAct; // !! format TIME dans la BDD !!
string hrFinAct; // !! format TIME dans la BDD
string dateAnnuleAct; // !! format DATE dans la BDD !!
string nomResp;
string prenomRes;
Activite uneActivite = null;
if (rdrActivite.HasRows)
{
if (rdrActivite.Read())
{
codeAnim = (string)rdrActivite["CODEANIM"];
dateAct = Convert.ToString(rdrActivite["DATEACT"]);
codeEtatAct = (string)rdrActivite["CODEETATACT"];
hrRdvAct = Convert.ToString(rdrActivite["HRRDVACT"]);
prixActe = (Decimal)rdrActivite["PRIXACT"];
hrDebutAct = Convert.ToString(rdrActivite["HRDEBUTACT"]);
hrFinAct = Convert.ToString(rdrActivite["HRFINACT"]);
dateAnnuleAct = Convert.ToString(rdrActivite["DATEANNULEACT"]);
nomResp = (string)rdrActivite["NOMRESP"];
prenomRes = (string)rdrActivite["PRENOMRESP"];
uneActivite = new Activite(codeAnim, dateAct, codeEtatAct, hrRdvAct, prixActe,
hrDebutAct, hrFinAct, dateAnnuleAct, nomResp, prenomRes);
return(uneActivite);
}
}
rdrActivite.Close();
return(uneActivite);
}
}
public static bool AnnulerActivite(Activite uneActivite)
{
string reqUpdteEtu;
int nbMaj;
Activite modifActi = Modification.GetActvite();
MySqlCommand cmd = conn.CreateCommand();
reqUpdteEtu = "UPDATE activite SET CODEANIM = '" + uneActivite.CodeAnim + "' ," +
" DATEACT = '" + Convert.ToString(DateTime.Parse(uneActivite.DateAct).ToString("yyyy'-'MM'-'dd")) + "' ," +
" CODEETATACT = 'AN' ," +
" HRRDVACT = '" + uneActivite.HrRdvAct + "' ," +
" PRIXACT = " + uneActivite.PrixActe.ToString().Replace(",", ".") + " ," + //remplace la virgule par un point car la bdd n'accepte pas de virgule pour des decimaux
" HRDEBUTACT = '" + uneActivite.HrDebutAct + "' ," +
" HRFINACT = '" + uneActivite.HrFinAct + "' ," +
" DATEANNULEACT = '" + Convert.ToString(DateTime.Parse(uneActivite.DateAnnuleAct).ToString("yyyy'-'MM'-'dd")) + "' ," +
" NOMRESP = '" + uneActivite.NomResp + "' ," +
" PRENOMRESP = '" + uneActivite.PrenomRes + "'" + //mysql n'accepte pas de ' dans les requetes,
" WHERE CODEANIM = '" + modifActi.CodeAnim + "' AND DATEACT = '" + Convert.ToString(DateTime.Parse(modifActi.DateAct).ToString("yyyy'-'MM'-'dd")) + "'";
cmd.CommandText = reqUpdteEtu;
nbMaj = cmd.ExecuteNonQuery();
if (nbMaj == 1)
{
return(true);
}
return(false);
}
public static bool InscriptionVacancier(Activite uneActivite) // TO DO INSCRIPTION NE FONCTIONNE PAS
{
if (Utilisateur.EstVacancier())
{
string dateInscript = DateTime.Now.ToString("yyyy-MM-dd");
string dateAct = Convert.ToDateTime(uneActivite.DateAct).ToString("yyyy-MM-dd");
string dateAnnul = Convert.ToDateTime(uneActivite.DateAnnuleAct).ToString("yyyy-MM-dd");
string reqI;
int nbMaj;
MySqlCommand cmd = conn.CreateCommand();
reqI = "INSERT INTO INSCRIPTION(NOINSCRIP, USER, CODEANIM, DATEACT, DATEINSCRIP, DATEANNULE) " +
" VALUES(null ," +
"'" + Utilisateur.GetPseudo() + "', '" + uneActivite.CodeAnim + "', " +
"'" + dateAct + "' ," +
" '" + dateInscript + "'" +
", null )"; //to do
cmd.CommandText = reqI;
nbMaj = cmd.ExecuteNonQuery();
if (nbMaj == 1)
{
return(true);
}
return(false);
}
return(false);
}
public static bool ModifierActivite(Activite uneActivite) // à modifier, plante quand l'activité
{ //est mise a la meme date qu'une autre
string reqUpdteEtu;
int nbMaj;
Activite modifActi = Modification.GetActvite();
MySqlCommand cmd = conn.CreateCommand();
reqUpdteEtu = "UPDATE activite SET CODEANIM = '" + uneActivite.CodeAnim + "' ," +
" DATEACT = '" + uneActivite.DateAct + "' ," +
" CODEETATACT = '" + uneActivite.CodeEtatAct.ToString() + "' ," +
" HRRDVACT = '" + uneActivite.HrRdvAct + "' ," +
" PRIXACT = " + uneActivite.PrixActe + " ," +
" HRDEBUTACT = '" + uneActivite.HrDebutAct + "' ," +
" HRFINACT = '" + uneActivite.HrFinAct + "' ," +
" DATEANNULEACT = '" + uneActivite.DateAnnuleAct + "' ," +
" NOMRESP = '" + uneActivite.NomResp + "' ," +
" PRENOMRESP = '" + uneActivite.PrenomRes + "'" + //mysql n'accepte pas de ' dans les requetes,
" WHERE CODEANIM = '" + modifActi.CodeAnim + "' AND DATEACT = '" + Convert.ToString(DateTime.Parse(modifActi.DateAct).ToString("yyyy'-'MM'-'dd")) + "'";
cmd.CommandText = reqUpdteEtu;
nbMaj = cmd.ExecuteNonQuery();
if (nbMaj == 1)
{
return(true);
}
return(false);
}
public static bool estCreneauValide(Activite uneActivite)
{
List <Activite> lesActivites = new List <Activite>();
DateTime dateActi = Convert.ToDateTime(uneActivite.DateAct);
string sTous = "SELECT a.CODEANIM, a.DATEACT, a.CODEETATACT, a.HRRDVACT, a.PRIXACT, a.HRDEBUTACT, a.HRFINACT, a.DATEANNULEACT, a.NOMRESP, a.PRENOMRESP " +
"FROM activite A, inscription i " +
"WHERE A.DATEACT = '" + dateActi.ToString("yyyy-MM-dd") + "' " +
"AND i.USER = '******' " +
"GROUP BY a.CODEANIM";
string codeAnim;
string dateAct;
string codeEtatAct;
string hrRdvAct;
Decimal prixActe;
string hrDebutAct;
string hrFinAct;
string dateAnnuleAct;
string nomResp;
string prenomRes;
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = sTous;
MySqlDataReader rdrActivite = cmd.ExecuteReader();
if (rdrActivite.HasRows)
{
while (rdrActivite.Read())
{
codeAnim = (string)rdrActivite["CODEANIM"];
dateAct = Convert.ToString(rdrActivite["DATEACT"]).Substring(0, 11); // Récupere seulement la date et vire l'heure chiante (hh:mm:ss)
codeEtatAct = (string)rdrActivite["CODEETATACT"];
hrRdvAct = Convert.ToString(rdrActivite["HRRDVACT"]).Substring(0, 5);
prixActe = (Decimal)rdrActivite["PRIXACT"];
hrDebutAct = Convert.ToString(rdrActivite["HRDEBUTACT"]).Substring(0, 5);
hrFinAct = Convert.ToString(rdrActivite["HRFINACT"]).Substring(0, 5);
dateAnnuleAct = Convert.ToString(rdrActivite["DATEANNULEACT"]);
nomResp = Convert.ToString(rdrActivite["NOMRESP"]);
prenomRes = Convert.ToString(rdrActivite["PRENOMRESP"]);
Activite testActivite = new Activite(codeAnim, dateAct, codeEtatAct, hrRdvAct, prixActe,
hrDebutAct, hrFinAct, dateAnnuleAct, nomResp, prenomRes);
lesActivites.Add(testActivite);
}
rdrActivite.Close();
}
foreach (Activite a in lesActivites)
{
}
rdrActivite.Close();
return(true);
}
public static List <Vacanciere> GetLesInscritsAUneActivite(Activite uneActivite)
{
MySqlDataReader rdrVacancier;
MySqlCommand cmd = conn.CreateCommand();
DateTime dateActi = Convert.ToDateTime(uneActivite.DateAct);
string sqlTypeCompte = "SELECT DISTINCT P.USER, P.NOMCOMPTE, P.PRENOMCOMPTE, P.DATEINSCRIP, " +
"P.DATEFERME, P.DATEDEBSEJOUR, P.DATEFINSEJOUR, " +
"P.DATENAISCOMPTE, P.ADRMAILCOMPTE, P.NOTELCOMPTE, I.NOINSCRIP " +
"FROM INSCRIPTION I, COMPTE P WHERE P.USER = I.USER " +
"AND P.TYPEPROFIL = 'V' AND I.CODEANIM = '" + uneActivite.CodeAnim + "' " +
"AND I.DATEANNULE IS NULL AND I.DATEACT = '" + dateActi.ToString("yyyy-MM-dd") + "'";
cmd.CommandText = sqlTypeCompte;
rdrVacancier = cmd.ExecuteReader();
List <Vacanciere> listeVacancier = new List <Vacanciere>();
string pseudo;
string nomCompte;
string prenomCompte;
DateTime dateInscrip;
DateTime dateFerme;
DateTime dateDebSejour;
DateTime dateFinSejour;
DateTime dateNaissCompte;
string adresseMailCompte;
string noTelCompte;
string noInscrip;
if (rdrVacancier.HasRows)
{
while (rdrVacancier.Read())
{
pseudo = (string)rdrVacancier["USER"];
nomCompte = (string)rdrVacancier["NOMCOMPTE"];
prenomCompte = (string)rdrVacancier["PRENOMCOMPTE"];
dateInscrip = (DateTime)rdrVacancier["DATEINSCRIP"];
dateFerme = (DateTime)rdrVacancier["DATEFERME"];
dateDebSejour = (DateTime)rdrVacancier["DATEDEBSEJOUR"];
dateFinSejour = (DateTime)rdrVacancier["DATEFINSEJOUR"];
dateNaissCompte = (DateTime)rdrVacancier["DATENAISCOMPTE"];
adresseMailCompte = (string)rdrVacancier["ADRMAILCOMPTE"];
noTelCompte = (string)rdrVacancier["NOTELCOMPTE"];
noInscrip = Convert.ToString(rdrVacancier["NOINSCRIP"]);
Vacanciere uneVacanciere = new Vacanciere(pseudo, nomCompte, prenomCompte, dateInscrip, dateFerme,
dateDebSejour, dateFinSejour, dateNaissCompte, adresseMailCompte,
noTelCompte, noInscrip);
listeVacancier.Add(uneVacanciere);
}
}
rdrVacancier.Close();
return(listeVacancier);
}
public static bool ExisteActivite(Activite uneActivite) // to do
{
string reqExiste;
MySqlCommand cmd = conn.CreateCommand();
reqExiste = "SELECT COUNT(*) AS NBACTIVITE FROM ACTIVITE WHERE DATEACT = '" + uneActivite.DateAct + "' AND CODEANIM = '" + uneActivite.CodeAnim + "';";
cmd.CommandText = reqExiste;
int nbMaj = int.Parse(cmd.ExecuteScalar().ToString());
if (nbMaj == 0)
{
return(false);
}
return(true);
}
public static int GetNoInscrip(Activite uneActivite)
{
string reqNbInscrip;
DateTime dateActi = Convert.ToDateTime(uneActivite.DateAct);
Object nbMaj;
MySqlCommand cmd = conn.CreateCommand();
reqNbInscrip = "SELECT NOINSCRIP " +
"FROM inscription I " +
"WHERE I.DATEACT = '" + dateActi.ToString("yyyy-MM-dd") + "' " +
"AND I.CODEANIM = '" + uneActivite.CodeAnim + "'" +
"AND I.USER = '******';";
cmd.CommandText = reqNbInscrip;
nbMaj = cmd.ExecuteScalar();
return(Convert.ToInt32(nbMaj));
}
public static List <Activite> GetLesActivitesPourUneAnimation(Animation uneAnimation)
{
MySqlDataReader rdrActivite;
MySqlCommand cmd = conn.CreateCommand();
string sqlActi = "SELECT * FROM ACTIVITE WHERE CODEANIM LIKE '" + uneAnimation.Code + "'";
cmd.CommandText = sqlActi;
rdrActivite = cmd.ExecuteReader();
List <Activite> listeActivite = new List <Activite>();
string codeAnim;
string dateAct; // !! format DATE dans la BDD !!
string codeEtatAct;
string hrRdvAct;
Decimal prixActe; //voir comment utiliser le type Decimal (dans le programme et la BDD)
string hrDebutAct; // !! format TIME dans la BDD !!
string hrFinAct; // !! format TIME dans la BDD
string dateAnnuleAct; // !! format DATE dans la BDD !!
string nomResp;
string prenomRes;
if (rdrActivite.HasRows)
{
while (rdrActivite.Read())
{
codeAnim = (string)rdrActivite["CODEANIM"];
dateAct = Convert.ToString(rdrActivite["DATEACT"]).Substring(0, 11); // Récupere seulement la date et vire l'heure chiante (hh:mm:ss)
codeEtatAct = (string)rdrActivite["CODEETATACT"];
hrRdvAct = Convert.ToString(rdrActivite["HRRDVACT"]).Substring(0, 5);
prixActe = (Decimal)rdrActivite["PRIXACT"];
hrDebutAct = Convert.ToString(rdrActivite["HRDEBUTACT"]).Substring(0, 5);
hrFinAct = Convert.ToString(rdrActivite["HRFINACT"]).Substring(0, 5);
dateAnnuleAct = Convert.ToString(rdrActivite["DATEANNULEACT"]);
nomResp = Convert.ToString(rdrActivite["NOMRESP"]);
prenomRes = Convert.ToString(rdrActivite["PRENOMRESP"]);
Activite uneActivite = new Activite(codeAnim, dateAct, codeEtatAct, hrRdvAct, prixActe,
hrDebutAct, hrFinAct, dateAnnuleAct, nomResp, prenomRes);
listeActivite.Add(uneActivite);
}
}
rdrActivite.Close();
return(listeActivite);
}
public static bool VerifierInscription(Activite uneActivite)
{
string reqNbInscrip;
DateTime dateActi = Convert.ToDateTime(uneActivite.DateAct);
Object nbMaj;
MySqlCommand cmd = conn.CreateCommand();
reqNbInscrip = "SELECT COUNT(I.USER)AS NBINSCRIP " +
"FROM inscription I " +
"WHERE I.DATEACT = '" + dateActi.ToString("yyyy-MM-dd") + "' " +
"AND I.CODEANIM = '" + uneActivite.CodeAnim + "'" +
"AND I.USER = '******';";
cmd.CommandText = reqNbInscrip;
nbMaj = cmd.ExecuteScalar();
if (Convert.ToInt32(nbMaj) == 0) //si le vacancier n'est pas déjà inscrit, retourne faux, s'il est déjà inscrit
{
return(false); // retourne vrai
}
return(true);
}
public static int GetNbPlaceDispo(Activite uneActivite)
{
string reqNbPlaceDispo;
DateTime dateActi = Convert.ToDateTime(uneActivite.DateAct);
Object nbMaj;
MySqlCommand cmd = conn.CreateCommand();
reqNbPlaceDispo = "SELECT AN.NBREPLACEANIM - (SELECT COUNT(I.USER)AS NBPLACEDISPO FROM inscription I " +
"WHERE I.DATEACT = '" + dateActi.ToString("yyyy-MM-dd") + "' " +
"AND I.CODEANIM = '" + uneActivite.CodeAnim + "') AS NBPLACEDISPO " +
"FROM animation AN " +
"WHERE AN.CODEANIM = '" + uneActivite.CodeAnim + "' ;";
cmd.CommandText = reqNbPlaceDispo;
nbMaj = cmd.ExecuteScalar();
int nbPlacesDispo = Convert.ToInt32(nbMaj);
if (nbPlacesDispo != 0)
{
return(nbPlacesDispo);
}
return(0);
}
public static bool AjouterActivite(Activite uneActivite)
{
string reqI;
int nbMaj;
MySqlCommand cmd = conn.CreateCommand();
reqI = "INSERT INTO activite(CODEANIM, DATEACT, CODEETATACT, HRRDVACT, PRIXACT, HRDEBUTACT, HRFINACT, DATEANNULEACT, NOMRESP, PRENOMRESP" +
") " +
" VALUES( '" + uneActivite.CodeAnim + "' , '" + uneActivite.DateAct + "' , '" + uneActivite.CodeEtatAct + "', '" +
uneActivite.HrRdvAct + "' , " + uneActivite.PrixActe + ",'" + uneActivite.HrDebutAct + "', '" +
uneActivite.HrFinAct + "', '" + uneActivite.DateAnnuleAct + "','" + uneActivite.NomResp + "','" +
uneActivite.PrenomRes + "' );";
cmd.CommandText = reqI;
nbMaj = cmd.ExecuteNonQuery();
if (nbMaj == 1)
{
return(true);
}
return(false);
}
private void btnEnregistrer_Click(object sender, EventArgs e)
{
Animation animationTest = null;
string codeAnimTest = null;
if (comboBCodeAnimation.SelectedItem == null || comboBCodeEtatAct.SelectedItem == null || cmbBoxResponsable.SelectedItem == null)
{
MessageBox.Show("Veuillez remplir tous les champs.");
}
else
{
#region Controle de validité
//Controle de validité, récupere l'animation a partir du code anim dans la combobox
codeAnimTest = comboBCodeAnimation.SelectedItem.ToString();
foreach (Animation uneAnimation in Donnees.GetLesAnimations())
{
if (uneAnimation.Code == codeAnimTest)
{
animationTest = uneAnimation;
}
}
if (Convert.ToDateTime(animationTest.DateValidite) < dtPickerDateAct.Value) //test si la date d'activité choisie est bien inferieur a la date de validité de l'animation
{
MessageBox.Show("L'activité créée ne peut pas avoir lieu après la date de validité de l'animation (" + animationTest.DateValidite.Substring(0, 10) + ").");
}
else
if (Convert.ToDateTime(animationTest.DateCreation) > dtPickerDateAct.Value)
{
MessageBox.Show("L'activité créée ne peut pas avoir lieu avant la date de validité de l'animation (" + animationTest.DateCreation.Substring(0, 10) + ").");
}
else
if (dtPickerHrDebut.Value > dtPickerHrFin.Value)
{
MessageBox.Show("L'activité ne peut pas démarrer après avoir terminé.");
}
else
if (dtPickerAnnulActivite.Value > dtPickerDateAct.Value)
{
MessageBox.Show("La date d'annulation doit avoir lieu après la date de l'activité ");
}
#endregion
else
{
String dateAct = dtPickerDateAct.Value.ToString("yyyy:MM:dd").Replace(":", "-");
String dateAnnulAct = dtPickerAnnulActivite.Value.ToString("yyyy:MM:dd").Replace(":", "-");
string codeAnim = comboBCodeAnimation.SelectedItem.ToString();
string codeEtatAct = comboBCodeEtatAct.SelectedItem.ToString();
string hrRdvAct = dtPickerHrRdv.Value.ToString("HH:mm:ss");
Decimal prixActe = numUpDwnPrixAct.Value;
//Récupere l'heure du datetimepicker
string hrDebutAct = dtPickerHrDebut.Value.ToString("HH:mm:ss");;
string hrFinAct = dtPickerHrFin.Value.ToString("HH:mm:ss");
//split du nom en tableau de string puis récuperation de chaque champ
string[] tmp = cmbBoxResponsable.Text.Split(' ');
string nomResp = tmp[1];
string prenomRes = tmp[0];
Activite uneActivite = new Activite(codeAnim, dateAct, codeEtatAct, hrRdvAct, prixActe, hrDebutAct, hrFinAct, dateAnnulAct, nomResp, prenomRes);
if (uneActivite.CodeAnim != Modification.GetActvite().CodeAnim)
{
if (Donnees.ExisteActivite(uneActivite) == false)
{
if (Donnees.ModifierActivite(uneActivite) == true)
{
MessageBox.Show("L'activité " + Modification.GetActvite().ToString() + " à bien été modifiée en :\n " + uneActivite.ToString());
ConsulterModifierActiviteEncadrant cmActE = new ConsulterModifierActiviteEncadrant();
cmActE.Show();
this.Close();
}
else
{
MessageBox.Show("Une erreur est survenue lors de la modification.");
}
}
else
{
MessageBox.Show("L'activité " + uneActivite.CodeAnim + " existe déjà à la date " + uneActivite.DateAct + " veuillez en choisir une autre.");
}
}
else
{
if (Donnees.ModifierActivite(uneActivite) == true)
{
MessageBox.Show("L'activité " + Modification.GetActvite().ToString() + " à bien été modifiée en :\n " + uneActivite.ToString());
ConsulterModifierActiviteEncadrant cmActE = new ConsulterModifierActiviteEncadrant();
cmActE.ShowDialog();
this.Hide();
}
else
{
MessageBox.Show("Une erreur est survenue lors de la modification.");
}
}
}
}
}
public static void SetActivite(Activite uneActivite)
{
tmpActivite = uneActivite;
}