public int ResultadoPronostico(Usuario u)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
string consulta = String.Format("SELECT * FROM PronosticoPartido WHERE idUsuario = {0}", u.Id);
DbDataReader reader = connection.Execute(consulta);
List <int> pronosticados = new List <int>();
List <int> reales = new List <int>();
int puntaje = 0;
while (reader.Read())
{
pronosticados.Add(reader.GetInt32(2));
pronosticados.Add(reader.GetInt32(3));
}
consulta = String.Format("SELECT * FROM Partido");
reader = connection.Execute(consulta);
while (reader.Read())
{
reales.Add(reader.GetInt32(2));
reales.Add(reader.GetInt32(3));
}
for (int i = 0; i < 32; i = i + 2)
{
puntaje += PuntosPartido(pronosticados[i], pronosticados[i + 1], reales[i], reales[i + 1]);
}
return(puntaje);
}
public void AgregarUsuario(string email, string token)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
string consulta = String.Format("INSERT INTO Usuario(email, Token, Puntaje) VALUES('{0}', '{1}' ,{2})", email, token, 0);
connection.ExecuteNonQuery(consulta);
}
public void AsociarMailToken(string email, string token)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
string consulta = String.Format("SELECT * FROM Token WHERE Token = '{0}'", token);
DbDataReader reader = connection.Execute(consulta);
if (!reader.Read())
{
throw new NoExisteElTokenException();
}
reader.Close();
consulta = String.Format("SELECT * FROM Token t, Usuario u WHERE t.token = '{0}' AND t.idUsuario = u.id AND u.email = '{1}'", token, email);
reader = connection.Execute(consulta);
if (reader.Read())
{
throw new YaEstaAsociadoElUsuarioConElTokenException();
}
reader.Close();
consulta = String.Format("SELECT * FROM Token WHERE token = '{0}' AND idUsuario = -1", token);
reader = connection.Execute(consulta);
if (!reader.Read())
{
throw new YaEstaAsociadoElTokenConOtroUsuarioException();
}
AgregarUsuario(email, token);
Usuario usuario = GetUsuarioPorToken(token);
connection.ExecuteNonQuery(String.Format("UPDATE Token SET idUsuario = {0} WHERE Token = '{1}'", usuario.Id, token));
}
public static SQLServerConnection GetInstance(string connectionString)
{
if (instance == null)
{
instance = new SQLServerConnection(connectionString);
}
return(instance);
}
public string GetTokenPorEmail(string email)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
DbDataReader reader = connection.Execute(String.Format("SELECT * FROM Usuario u, Token t WHERE u.email = '{0}' AND u.Id = t.idUsuario", email) /*"SELECT * FROM Usuario u, Token t WHERE email = '{0}' and u.Id = t.idUsuario", email)*/);
reader.Read();
return(reader.GetString(3));
}
public void GuardarPronosticosEspeciales(string resultadosEspeciales, Usuario u)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
string[] vecResultados = resultadosEspeciales.Split(';');
connection.ExecuteNonQuery(String.Format("INSERT INTO PronosticosEspeciales(idPrimeroA, idSegundoB, idPrimeroB, idSegundoA, idPerdedorCruce1, idPerdedorCruce2, idGanadorCruce1, idGanadorCruce2, idPrimero,idSegundo,idTercero,idMasGoles,idCantGoles) VALUES({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12})"
, vecResultados[0], vecResultados[1], vecResultados[2], vecResultados[3]
, vecResultados[4], vecResultados[5], vecResultados[6], vecResultados[7]
, vecResultados[8], vecResultados[9], vecResultados[10], vecResultados[11]
, vecResultados[12]));
}
private string connectionString = ConfigurationManager.ConnectionStrings["mydb"].ToString();//"Data Source=GABRIEL-PC;Initial Catalog=Pencas;Integrated Security=True; MultipleActiveResultSets=True;";
public void GenerarTokens(int cantidad)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
string codigo = "";
for (int i = 0; i < cantidad; i++)
{
Guid g = Guid.NewGuid();
codigo = g.ToString().Substring(0, 5);
connection.ExecuteNonQuery(string.Format("INSERT INTO Token(Token) VALUES('{0}')", codigo));
}
}
public string RecuperarPronostico(Usuario u)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
string consulta = String.Format("SELECT * FROM PronosticoPartido WHERE idUsuario = {0}", u.Id);
string pronosticos = "";
DbDataReader reader = connection.Execute(consulta);
while (reader.Read())
{
pronosticos += reader.GetInt32(2) + ";";
pronosticos += reader.GetInt32(3) + ";";
}
pronosticos = pronosticos.Substring(0, pronosticos.Length - 1);
return(pronosticos);
}
private string connectionString = ConfigurationManager.ConnectionStrings["mydb"].ToString();//"Data Source=GABRIEL-PC;Initial Catalog=Pencas;Integrated Security=True; MultipleActiveResultSets=True;";
/// <summary>
/// Guarda los resultados en la base de datos
/// </summary>
/// <param name="resultados">Resultados en el formato partido1;golesA;golesB;partido2;golesA;golesB;...partidoN;golesA;golesB</param>
public void GuardarPronostico(string resultados, Usuario u)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
string[] vecResultados = resultados.Split(';');
for (int i = 0; i < vecResultados.Length; i++)
{
if (i % 2 == 0)
{
string[] vecGoles = vecResultados[i + 1].Split('-');
int golesA = Int32.Parse(vecGoles[0]);
int golesB = Int32.Parse(vecGoles[1]);
connection.ExecuteNonQuery(String.Format("INSERT INTO PronosticoPartido(idPartido,idUsuario,golesA,golesB) VALUES({0},{1},{2},{3})", vecResultados[i], u.Id, golesA, golesB));
}
}
}
private string connectionString = ConfigurationManager.ConnectionStrings["mydb"].ToString();//"Data Source=GABRIEL-PC;Initial Catalog=Pencas;Integrated Security=True; MultipleActiveResultSets=True;";
public List <Usuario> GetUsuarios()
{
List <Usuario> retorno = new List <Usuario>();
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
DbDataReader reader = connection.Execute("SELECT * FROM Usuario");
while (reader.Read())
{
Usuario u = new Usuario();
u.Id = reader.GetInt32(0);
u.Nombre = reader.GetString(1);
retorno.Add(u);
}
reader.Close();
return(retorno);
}
public Usuario GetUsuarioPorEmail(string email)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
DbDataReader reader = connection.Execute(String.Format("SELECT * FROM Usuario WHERE email = '{0}'", email) /*"SELECT * FROM Usuario u, Token t WHERE email = '{0}' and u.Id = t.idUsuario", email)*/);
if (reader.Read())
{
Usuario u = new Usuario();
u.Id = reader.GetInt32(0);
u.Nombre = reader.GetString(1);
u.EnvioPronostico = reader.GetBoolean(2);
return(u);
}
else
{
throw new NoExisteUsuarioException();
}
}
public void AgregarUsuario(string email)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
try
{
Usuario u = GetUsuarioPorEmail(email);
}catch (NoExisteUsuarioException) {
try
{
string consulta = String.Format("INSERT INTO Usuario(email, EnvioPronostico, puntaje) VALUES('{0}', 'false',{1})", email, 0);
connection.ExecuteNonQuery(consulta);
return;
}
catch (Exception e) { }
}
throw new YaExisteUsuarioException();
}
//public Usuario GetUsuarioPorEmail(string email)
//{
// SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
// DbDataReader reader = connection.Execute(String.Format("SELECT * FROM Usuario WHERE email = '{0}'", email)/*"SELECT * FROM Usuario u, Token t WHERE email = '{0}' and u.Id = t.idUsuario", email)*/);
// if (reader.Read())
// {
// Usuario u = new Usuario();
// u.Id = reader.GetInt32(0);
// u.Email = reader.GetString(1);
// u.Token = reader.GetString(2);
// u.Puntaje = reader.GetInt32(3);
// return u;
// }
// else
// {
// throw new NoExisteUsuarioException();
// }
//}
//public string GetTokenPorEmail(string email)
//{
// SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
// DbDataReader reader = connection.Execute(String.Format("SELECT * FROM Usuario u, Token t WHERE u.email = '{0}' AND u.Id = t.idUsuario", email)/*"SELECT * FROM Usuario u, Token t WHERE email = '{0}' and u.Id = t.idUsuario", email)*/);
// reader.Read();
// return reader.GetString(3);
//}
public Usuario GetUsuarioPorToken(string token)
{
SQLServerConnection connection = SQLServerConnection.GetInstance(connectionString);
DbDataReader reader = connection.Execute(String.Format("SELECT * FROM Usuario WHERE token = '{0}'", token));
if (reader.Read())
{
Usuario u = new Usuario();
u.Id = reader.GetInt32(0);
u.Email = reader.GetString(1);
u.Token = reader.GetString(2);
u.Puntaje = reader.GetInt32(3);
return(u);
}
else
{
throw new NoExisteUsuarioException();
}
}
public static SQLServerConnection GetInstance(string connectionString)
{
if (instance == null) instance = new SQLServerConnection(connectionString);
return instance;
}