public static Card readCard(int id)
{
Card usuario = new Card();
string query = "select * from card where card_id='" + id + "'";
MySqlCommand command = new MySqlCommand(query, BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
usuario = new Card(int.Parse(r["card_id"].ToString()), int.Parse(r["user_id"].ToString()), r["number"].ToString(), r["holdername"].ToString(), r["securitycode"].ToString(), r["expirationdate"].ToString());
}
return(usuario);
}
public static User readUser(int id)
{
User usuario = new User();
string query = "select * from user where user_id='" + id + "'";
MySqlCommand command = new MySqlCommand(query, BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
usuario = new User(int.Parse(r["user_id"].ToString()), r["password"].ToString(), r["name"].ToString(), r["lastname"].ToString(), r["fb"].ToString(), r["email"].ToString(), r["tel"].ToString(), r["nickname"].ToString());
}
return(usuario);
}
public static Repository read(int id)
{
Repository repo = new Repository();
string query = "select * from repository where repository_id='" + id + "'";
MySqlCommand command = new MySqlCommand(query, BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
repo = new Repository(int.Parse(r["repository_id"].ToString()), int.Parse(r["creator_id"].ToString()), r["name"].ToString(), r["description"].ToString(), r["picture1"].ToString(), r["picture2"].ToString(), r["picture3"].ToString());
}
return(repo);
}
public static ArrayList readUsers()
{
ArrayList listaUsuarios = new ArrayList();
string query = "select * user";
MySqlCommand command = new MySqlCommand(query, BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
User usuario = new User(int.Parse(r["user_id"].ToString()), r["password"].ToString(), r["name"].ToString(), r["lastname"].ToString(), r["fb"].ToString(), r["email"].ToString(), r["tel"].ToString(), r["nickname"].ToString());
listaUsuarios.Add(usuario);
}
return(listaUsuarios);
}
public static ArrayList readMyPurchases(int user_id)
{
ArrayList compras = new ArrayList();
string query = "select * from compras where user_id='" + user_id + "'";
MySqlCommand command = new MySqlCommand(query, BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
Compra compra = new Compra(int.Parse(r["compra_id"].ToString()), int.Parse(r["user_id"].ToString()), int.Parse(r["repository_id"].ToString()));
compras.Add(compra);
}
return(compras);
}
public static ArrayList readMyCards(int user_id)
{
ArrayList cardList = new ArrayList();
string query = "select * from card where user_id='" + user_id + "'";
MySqlCommand command = new MySqlCommand(query, BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
Card card = new Card(int.Parse(r["card_id"].ToString()), int.Parse(r["user_id"].ToString()), r["number"].ToString(), r["holdername"].ToString(), r["securitycode"].ToString(), r["expirationdate"].ToString());
cardList.Add(card);
}
return(cardList);
}
//Todos
public static ArrayList readAllRepos()
{
ArrayList repos = new ArrayList();
string query = "select * from repository";
MySqlCommand command = new MySqlCommand(query, BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
Repository repo = new Repository(int.Parse(r["repository_id"].ToString()), int.Parse(r["creator_id"].ToString()), r["name"].ToString(), r["description"].ToString(), r["picture1"].ToString(), r["picture2"].ToString(), r["picture3"].ToString());
repos.Add(repo);
}
return(repos);
}
public static bool getPermisoVista(int user_id, int repository_id)
{
MySqlCommand c = new MySqlCommand(string.Format("select count(*) from (select * from repository where creator_id = '" + user_id + "' and repository_id = '" + repository_id + "') as num"), BDCon.ObtenerConexion());
int count1 = int.Parse(c.ExecuteScalar().ToString());
MySqlCommand c2 = new MySqlCommand(string.Format("select count(*) from (SELECT * FROM compras where user_id = '" + user_id + "' and repository_id = '" + repository_id + "') as num"), BDCon.ObtenerConexion());
int count2 = int.Parse(c2.ExecuteScalar().ToString());
if (count1 != 0 || count2 != 0)
{
return(true);
}
return(false);
}
public static int create(User u)
{
int result = 0;
MySqlCommand command = new MySqlCommand(string.Format("Insert into user(password, name, lastname, fb, email, tel, nickname) values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}')", u.password, u.name, u.lastName, u.fb, u.email, u.tel, u.nickname), BDCon.ObtenerConexion());
result = command.ExecuteNonQuery();
return(result);
}
public static int getLastId()
{
int result = 0;
MySqlCommand c = new MySqlCommand(string.Format("select MAX(user_id) from user"), BDCon.ObtenerConexion());
result = int.Parse(c.ExecuteScalar().ToString());
return(result);
}
public static int update(Repository repo)
{
int retorno = 0;
MySqlCommand comando = new MySqlCommand(string.Format("update repository set name = '{0}', description = '{1}', picture1='{2}', picture2='{3}', picture3='{4}' where id='{5}'", repo.name, repo.description, repo.picture1, repo.picture2, repo.picture3, repo.id), BDCon.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
return(retorno);
}
public static int create(Repository repo)
{
int result = 0;
MySqlCommand command = new MySqlCommand(string.Format("Insert into repository(creator_id, name, description, picture1, picture2, picture3) values('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", repo.id_creator, repo.name, repo.description, repo.picture1, repo.picture2, repo.picture3), BDCon.ObtenerConexion());
result = command.ExecuteNonQuery();
return(result);
}
public static int getCountAll()
{
int result = 0;
MySqlCommand c = new MySqlCommand(string.Format("select count(*) from repository"), BDCon.ObtenerConexion());
result = int.Parse(c.ExecuteScalar().ToString());
return(result);
}
public static int updateCard(Card card)
{
int retorno = 0;
MySqlCommand comando = new MySqlCommand(string.Format("update card set user_id = '{0}', number = '{1}', holdername='{2}', securitycode='{3}', expirationdate='{4}' where card_id='{5}'", card.user_id, card.number, card.holdername, card.securityCode, card.expirationDate, card.id), BDCon.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
return(retorno);
}
public static int updatWithoutPassword(User user)
{
int retorno = 0;
MySqlCommand comando = new MySqlCommand(string.Format("update users set name = '{0}', lastname='{1}', fb='{2}', email='{3}', tel='{4}' where id='{5}'", user.name, user.lastName, user.fb, user.email, user.tel, user.id), BDCon.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
return(retorno);
}
public static int delete(int id)
{
int retorno = 0;
MySqlCommand comando = new MySqlCommand(string.Format("Delete from user where user_id='{0}'", id), BDCon.ObtenerConexion());
retorno = comando.ExecuteNonQuery();
return(retorno);
}
//Data Access Object del objeto compra
public static int create(Compra c)
{
int result = 0;
MySqlCommand command = new MySqlCommand(string.Format("Insert into compras(user_id, repository_id) values('{0}', '{1}')", c.user_id, c.repository_id), BDCon.ObtenerConexion());
result = command.ExecuteNonQuery();
return(result);
}
public static string getPwd(int id)
{
string result = "";
MySqlCommand command = new MySqlCommand(string.Format("select password from user where user_id='{0}'", id), BDCon.ObtenerConexion());
MySqlDataReader r = command.ExecuteReader();
while (r.Read())
{
result = r["password"].ToString();
}
return(result);
}
//Data Access object del objeto tarjeta de credito
public static int create(Card c)
{
int result = 0;
MySqlCommand command = new MySqlCommand(string.Format("Insert into card(user_id, number, holdername, securitycode, expirationdate) values('{0}', '{1}', '{2}', '{3}', '{4}')", c.user_id, c.number, c.holdername, c.securityCode, c.expirationDate), BDCon.ObtenerConexion());
result = command.ExecuteNonQuery();
return(result);
}
