public List <UserActivity> ActivityUsers()
{
MySqlConnection conn = DBConect.Conect();
List <UserActivity> users = new List <UserActivity>();
try
{
conn.Open();
string sql = "SELECT u.user, COUNT(FK_user_id) FROM user_viaje,user AS u WHERE u.id=FK_user_id GROUP BY FK_user_id ORDER BY COUNT(FK_user_id) DESC ";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
UserActivity user = new UserActivity();
user.Name = (string)rdr[0];
user.Apariciones = (long)rdr[1];
users.Add(user);
}
rdr.Close();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(users);
}
public User CreadorViaje(long idViaje)
{
MySqlConnection conn = DBConect.Conect();
User salida = new User();
try
{
conn.Open();
string sql = "SELECT * FROM user WHERE id = (SELECT FK_user_id FROM user_viaje WHERE FK_viaje_id=" + idViaje + " AND role='CREADOR')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
User user = new User();
user.Id = (long)rdr[0];
user.Name = (string)rdr[1];
user.Password = (string)rdr[2];
user.GeneroMusical = (string)rdr[3];
salida = user;
}
rdr.Close();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(salida);
}
public string DeleteUser(long id)
{
MySqlConnection conn = DBConect.Conect();
try
{
User user = User(id);
conn.Open();
User(id);
string sql = "DELETE FROM `user` WHERE id =" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
if (db.DeleteUserViajeByUser(id))
{
user.ViajesCreados.ForEach(x => dbViaje.DeleteViaje(x.Id));
cmd.ExecuteNonQuery();
}
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("usuario eliminado con exito");
}
public string DeleteViaje(long id)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
FindViaje(id);
string sql = "DELETE FROM viaje WHERE id =" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
if (db.DeleteUserViaje(id))
{
cmd.ExecuteNonQuery();
}
else
{
return("no se pudo eliminar el viaje");
}
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("viaje eliminado con exito");
}
public string DeleteInvitado(long id, long idViaje)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
User(id);
dbViaje.FindViaje(idViaje);
string sql = "DELETE FROM `user_viaje` WHERE role='invitado' AND FK_user_id = " + id + " AND FK_viaje_id = " + idViaje;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("Invitado eliminado con exito");
}
public List <LugaresPopulares> DestinosPopulares()
{
MySqlConnection conn = DBConect.Conect();
List <LugaresPopulares> salida = new List <LugaresPopulares>();
try
{
conn.Open();
string sql = "SELECT destino, COUNT(destino) FROM viaje GROUP BY destino ORDER BY COUNT(destino) DESC";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
LugaresPopulares l = new LugaresPopulares();
l.Name = (string)rdr[0];
l.Apariciones = (long)rdr[1];
salida.Add(l);
}
rdr.Close();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(salida);
}
public List <Viaje> ViajesSuscrito(long idUser)
{
MySqlConnection conn = DBConect.Conect();
List <Viaje> salida = new List <Viaje>();
try
{
conn.Open();
string sql = "SELECT * FROM viaje WHERE id IN(SELECT FK_viaje_id FROM user_viaje WHERE FK_user_id=" + idUser + " AND role='INVITADO')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Viaje viaje = new Viaje();
viaje.Id = (long)rdr[0];
viaje.Origen = (string)rdr[1];
viaje.Destino = (string)rdr[2];
viaje.Plazas = (int)rdr[3];
viaje.Precio = (decimal)rdr[4];
viaje.Descripcion = (string)rdr[5];
salida.Add(viaje);
}
rdr.Close();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(salida);
}
public string ChangePassword(long id, string newPassword)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
User(id);
string sql = "UPDATE user SET password= '******' WHERE id =" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("contraseña actualizada con exito");
}
private void validerModifSalarie_Click(object sender, EventArgs e)
{
//ModifierSalarie salarieModif = new ModifierSalarie();
DBConect db = new DBConect();
db.Initialize();
string identifiant;
//DateTime dateValue;
//dateValue = dateTimePicker1.Value;
//string MySQLFormatDate = dateValue.ToString("yyyy-MM-dd HH:mm:ss");
int var = this.id;
string nom = Nom.Text;
string prénom = Prenom.Text;
string adresse = Adresse1.Text;
string CP = CodePostalSal.Text;
string nomVille = VilleSal.Text;
string fixe = Téléphone.Text;
string mail = Mail.Text;
db.modifierSalarié(var, nom, prénom, adresse, fixe, mail);
//salarieAjoute.Show();
this.Hide();
}
public List <User> Users()
{
MySqlConnection conn = DBConect.Conect();
List <User> users = new List <User>();
try
{
conn.Open();
string sql = "SELECT * from user";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
User user = new User();
user.Id = (long)rdr[0];
user.Name = (string)rdr[1];
user.Password = (string)rdr[2];
user.GeneroMusical = (string)rdr[3];
user.ViajesCreados = db.ViajesCreados((long)rdr[0]);
user.ViajesSuscrito = db.ViajesSuscrito((long)rdr[0]);
users.Add(user);
}
rdr.Close();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(users);
}
public string AddViaje(Viaje viaje)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
string sql = "INSERT INTO viaje (origen, destino, plazas, precio, descripcion) VALUES ('" + viaje.Origen + "', '" + viaje.Destino + "','" + viaje.Plazas + "','" + viaje.Precio + "','" + viaje.Descripcion + "')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
List <Viaje> viajes = Viajes();
db.AddUserViaje(viaje.Creador.Id, viajes[viajes.Count - 1].Id, "CREADOR");
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("viaje añadido con exito");
}
private void ConnectionButton_Click(object sender, EventArgs e)
{
this.id = IdentifiantBox.Text.ToString();
this.mdp = MDPBox.Text.ToString();
DBConect connexion = new DBConect();
connexion.Initialize();
bonUser = connexion.validerConnexion("SELECT identifiant FROM salaries", "identifiant", this.id);
bonMdp = connexion.validerConnexion("SELECT mot_de_passe FROM salaries WHERE identifiant='" + this.id + "'", "mot_de_passe", this.mdp);
if (bonUser == true)
{
if (bonMdp == true)
{
prenomUtilisateur = connexion.requete("SELECT prenom FROM salaries WHERE identifiant='" + this.id + "' AND mot_de_passe='" + this.mdp + "'", "prenom");
Connecte connection = new Connecte(prenomUtilisateur);
connection.Show();
this.Hide();
}
else
{
this.Hide();
EchecConnexion echec = new EchecConnexion();
echec.Show();
}
}
else
{
this.Hide();
EchecConnexion echec = new EchecConnexion();
echec.Show();
}
}
private void supprimerProduit_Click(object sender, EventArgs e)
{
int var = int.Parse(dataGridView1.CurrentRow.Cells[0].Value.ToString());
Console.Write(var);
DBConect db = new DBConect();
db.Initialize();
db.supprimerProduit(var);
this.Hide();
}
private void ajouter_Click(object sender, EventArgs e)
{
ProduitAjoute produitAjoute = new ProduitAjoute();
DBConect db = new DBConect();
db.Initialize();
db.insererProduit(textBox1.Text, textBox2.Text, textBox3.Text, textBox4.Text, textBox5.Text, textBox6.Text, textBox7.Text);
produitAjoute.Show();
this.Hide();
}
public Viaje FindViaje(long id)
{
MySqlConnection conn = DBConect.Conect();
Viaje salida = null;
try
{
conn.Open();
string sql = "SELECT * from viaje WHERE id=" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Viaje viaje = new Viaje();
viaje.Id = (long)rdr[0];
viaje.Origen = (string)rdr[1];
viaje.Destino = (string)rdr[2];
viaje.Plazas = (int)rdr[3];
viaje.Precio = (decimal)rdr[4];
viaje.Descripcion = (string)rdr[5];
viaje.Creador = db.CreadorViaje((long)rdr[0]);
viaje.Invitados = db.InvitadosViaje((long)rdr[0]);
if (rdr[6] is DBNull)
{
viaje.Lista = "No tiene lista de reproducción";
}
else
{
viaje.Lista = (String)rdr[6];
}
salida = viaje;
}
rdr.Close();
if (salida == null)
{
throw new FindException();
}
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(salida);
}
public List <Viaje> Viajes()
{
MySqlConnection conn = DBConect.Conect();
List <Viaje> viajes = new List <Viaje>();
try
{
conn.Open();
string sql = "SELECT * from viaje";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
Viaje viaje = new Viaje();
viaje.Id = (long)rdr[0];
viaje.Origen = (string)rdr[1];
viaje.Destino = (string)rdr[2];
viaje.Plazas = (int)rdr[3];
viaje.Precio = (decimal)rdr[4];
viaje.Descripcion = (string)rdr[5];
viaje.Creador = db.CreadorViaje((long)rdr[0]);
viaje.Invitados = db.InvitadosViaje((long)rdr[0]);
if (rdr[6] is DBNull)
{
viaje.Lista = "No tiene lista de reproducción";
}
else
{
viaje.Lista = (String)rdr[6];
}
viajes.Add(viaje);
}
rdr.Close();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new GenericException();
}
conn.Close();
return(viajes);
}
private void valider_Click(object sender, EventArgs e)
{
ClientAjoute clientAjoute = new ClientAjoute();
DBConect db = new DBConect();
db.Initialize();
string identifiant;
DateTime dateValue;
dateValue = dateTimePicker1.Value;
string MySQLFormatDate = dateValue.ToString("yyyy-MM-dd HH:mm:ss");
identifiant = Prenom.Text.Substring(0, 1) + Nom.Text;
db.insererClient(Nom.Text, Prenom.Text, Adresse1.Text, Adresse2.Text, Téléphone.Text, Mail.Text, MySQLFormatDate);
clientAjoute.Show();
this.Hide();
}
private void ValiderNouveauSalarie_Click(object sender, EventArgs e)
{
SalarieAjoute salarieAjoute = new SalarieAjoute();
DBConect db = new DBConect();
db.Initialize();
string identifiant;
DateTime dateValue;
dateValue = dateTimePicker1.Value;
string MySQLFormatDate = dateValue.ToString("yyyy-MM-dd HH:mm:ss");
identifiant = Prenom.Text.Substring(0, 1) + Nom.Text;
db.insererSalarié(identifiant, textBox_Password.Text, Nom.Text, Prenom.Text, Adresse1.Text, Adresse2.Text, Téléphone.Text, Mail.Text, MySQLFormatDate);
salarieAjoute.Show();
this.Hide();
}
public User User(long id)
{
MySqlConnection conn = DBConect.Conect();
User salida = null;
try
{
conn.Open();
string sql = "SELECT * from user WHERE id=" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
User user = new User();
user.Id = (long)rdr[0];
user.Name = (string)rdr[1];
user.Password = (string)rdr[2];
user.GeneroMusical = (string)rdr[3];
user.ViajesCreados = db.ViajesCreados((long)rdr[0]);
user.ViajesSuscrito = db.ViajesSuscrito((long)rdr[0]);
salida = user;
}
rdr.Close();
if (salida == null)
{
throw new FindException();
}
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(salida);
}
public string AddUser(User user)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
string sql = "INSERT INTO user (user, password, generomusical) VALUES ('" + user.Name + "', '" + user.Password + "','" + user.GeneroMusical + "')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("usuario añadido con exito");
}
public bool DeleteUserViajeByUser(long idUser)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
string sql = "DELETE FROM user_viaje WHERE FK_user_id =" + idUser;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return(true);
}
public string EditUser(long id, string name, string password, string gustosMusicales)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
User(id);
string sql = "UPDATE user SET user='******',generomusical='" + gustosMusicales + "', password='******' WHERE id =" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("usuario actualizado con exito");
}
public bool AddUserViaje(long idUser, long idViaje, string rol)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
string sql = "INSERT INTO user_viaje(FK_user_id, FK_viaje_id, role) VALUES ('" + idUser + "', '" + idViaje + "','" + rol + "')";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (FindException)
{
throw new FindException();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw new GenericException();
}
conn.Close();
return(true);
}
public string DeleteUserViajes(long id)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
User(id);
string sql = "DELETE FROM user_viaje WHERE FK_user_id =" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("usuario eliminado con exito");
}
public string EditViaje(long id, string origen, string destino, int plazas, decimal precio, string descripcion)
{
MySqlConnection conn = DBConect.Conect();
try
{
conn.Open();
FindViaje(id);
string sql = "UPDATE viaje SET origen='" + origen + "',destino='" + destino + "', plazas='" + plazas + "', precio='" + precio + "', descripcion='" + descripcion + "' WHERE id =" + id;
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.ExecuteNonQuery();
}
catch (FindException)
{
throw new FindException();
}
catch (Exception)
{
throw new GenericException();
}
conn.Close();
return("viaje actualizado con exito");
}