public List <ModeloNegocio.Asesor> getAllUsers()
{
List <ModeloNegocio.Asesor> lista = new List <ModeloNegocio.Asesor>();
//List<Autos> LstAutos = new List<Autos>();
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "SELECT otaku_bcp.[user].id, otaku_bcp.[user].name, otaku_bcp.[user].email, otaku_bcp.[user].password, otaku_bcp.[user].id_type, otaku_bcp.type_user.name AS type_user_name FROM otaku_bcp.type_user" +
" INNER JOIN otaku_bcp.[user] ON (otaku_bcp.type_user.id = otaku_bcp.[user].id_type)";
Dtr = Cmd.ExecuteReader();
while (Dtr.Read())
{
ModeloNegocio.Asesor users = new ModeloNegocio.Asesor();
users.id = Convert.ToInt32(Dtr["id"]);
users.FullName = Dtr["name"].ToString();
//users.Type = Dtr["type"].ToString();
users.Email = Dtr["email"].ToString();
ModeloNegocio.TypeUser typeUser = new ModeloNegocio.TypeUser();
typeUser.Id = Convert.ToInt16(Dtr["id_type"]);
typeUser.Name = Dtr["type_user_name"].ToString();
users.typeUser = typeUser;
lista.Add(users);
}
return(lista);
}
public ModeloNegocio.Asesor loginUsuario(string email, string password)
{
try
{
ModeloNegocio.Asesor user = new ModeloNegocio.Asesor();
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.CommandText = "select [user].id, [user].email, [user].name, [user].password, [user].id_type, type_user.name AS name_type from [otaku_bcp].[user] INNER JOIN [otaku_bcp].type_user ON ([user].id_type = type_user.id) where email=@email and password=@password ";
//String passWordEncriptado;
//passWordEncriptado = encriptaPassword(password);
//Cmd.Parameters.Add("@nombre", DbType.String);("@nombre", DbType.String)"@password", DbType.String)
//Cmd.Parameters.Add(new SQLiteParameter {ParameterName="@nombre" , Value = Usuario });
//Cmd.Parameters.Add(new SQLiteParameter { ParameterName = "@password", Value = Password });
//Cmd.Parameters["@nombre"].Value = Usuario;
//Cmd.Parameters.Add("@password",DbType.String );
//Cmd.Parameters["@password"].Value = Password;
Cmd.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 250));
Cmd.Parameters["@email"].Value = email;
Cmd.Parameters.Add(new SqlParameter("@password", SqlDbType.VarChar, 250));
Cmd.Parameters["@password"].Value = Encrypt(password);
Dtr = Cmd.ExecuteReader();
while (Dtr.Read())
{
//Lista.Add(new EntidadNegocio.UsuarioEN(Convert.ToInt32(Dtr["idusuario"]), Dtr["nombre"].ToString(), Dtr["usuario"].ToString(), Dtr["password"].ToString(), Convert.ToInt32(Dtr["permiso"])));
user.id = Convert.ToInt32(Dtr["id"]);
user.Email = Convert.ToString(Dtr["email"]);
user.FullName = Convert.ToString(Dtr["name"]);
user.Type = Convert.ToInt32(Dtr["id_type"]);
user.Password = Convert.ToString(Dtr["password"]);
ModeloNegocio.TypeUser typeUser = new ModeloNegocio.TypeUser();
typeUser.Id = Convert.ToInt32(Dtr["id_type"]);
typeUser.Name = Convert.ToString(Dtr["name_type"]);
user.typeUser = typeUser;
}
Cmd.Parameters.Clear();
Dtr.Close();
Conn.Close();
//Boolean ckecPassword1 = chekPassword(password,user.Password.ToString());
return(user);
}
catch (Exception ex)
{
throw new Exception("Error en Acceso a Datos: LoginUsuario.", ex);
}
}
public List <ModeloNegocio.Asesor> getAllAsesors(int registroAMostrar, int registroAEmpezar)
{
List <ModeloNegocio.Asesor> lista = new List <ModeloNegocio.Asesor>();
Cmd = new SqlCommand();
Cmd.Connection = Conn;
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "[otaku_bcp].PA_LISTA_USERS";
Cmd.Parameters.Add("@RegistrosAMostrar", SqlDbType.Int).Value = registroAMostrar;
Cmd.Parameters.Add("@RegistrosAEmpezar", SqlDbType.Int).Value = registroAEmpezar;
Cmd.Parameters.Add("@tipoUser", SqlDbType.Int).Value = 7;
Dtr = Cmd.ExecuteReader();
while (Dtr.Read())
{
ModeloNegocio.Asesor users = new ModeloNegocio.Asesor();
users.id = Convert.ToInt32(Dtr["id"]);
users.Codigo = Dtr["codigo"].ToString();
users.FullName = Dtr["name"].ToString();
//users.Type = Dtr["type"].ToString();
users.Email = Dtr["email"].ToString();
if (DBNull.Value.Equals(Dtr["created_at"]))
{
users.FechaCreado = DateTime.Now;
}
else
{
users.FechaCreado = DateTime.Parse(Dtr["created_at"].ToString());
}
ModeloNegocio.TypeUser typeUser = new ModeloNegocio.TypeUser();
typeUser.Id = Convert.ToInt16(Dtr["type_user_id"]);
typeUser.Name = Dtr["tipoUser"].ToString();
users.typeUser = typeUser;
lista.Add(users);
}
Conn.Close();
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
}
return(lista);
}
public ModeloNegocio.User getUser(int iduser)
{
Cmd = new SqlCommand();
Cmd.Connection = Conn;
SqlDataReader Reader;
ModeloNegocio.User User = new ModeloNegocio.User();
try
{
Cmd.Parameters.Clear();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "PA_GET_USER_ID";
Cmd.Parameters.Add("@iduser", SqlDbType.Int).Value = iduser;
Reader = Cmd.ExecuteReader();
if (Reader.HasRows)
{
while (Reader.Read())
{
User.id = Reader.GetInt32(0);
User.FullName = Reader.GetString(1);
if (DBNull.Value.Equals(Reader.GetString(2)))
{
User.Codigo = "";
}
else
{
User.Codigo = Reader.GetString(2);
}
if (!Reader.IsDBNull(Reader.GetOrdinal("email")))
{
User.Email = Reader.GetString(3);
}
else
{
User.Email = "";
}
if (DBNull.Value.Equals(Reader.GetString(4)))
{
User.Telefono = "";
}
else
{
User.Telefono = Reader.GetString(4);
}
User.FechaCreado = Reader.GetDateTime(5);
User.FechaActualizado = Reader.GetDateTime(6);
ModeloNegocio.TypeUser typeUser = new ModeloNegocio.TypeUser();
typeUser.Id = Reader.GetInt32(7);
typeUser.Name = Reader.GetString(8);
User.typeUser = typeUser;
if (!Reader.IsDBNull(Reader.GetOrdinal("grupo")))
{
User.Grupo = Reader.GetString(9);
}
else
{
User.Grupo = "";
}
//User.Selection = Reader.GetByte(10);
if (!Reader.IsDBNull(Reader.GetOrdinal("type_contract")))
{
User.TypeContract = Reader.GetString(11);
}
else
{
User.TypeContract = "";
}
}
}
else
{
Console.WriteLine("No rows found.");
}
Reader.Close();
Conn.Close();
return(User);
}
catch (Exception e)
{
return(User);
Console.WriteLine("{0} Exception caught.", e);
}
}
public ModeloNegocio.Asesor getAsesorCode(string code)
{
Cmd = new SqlCommand();
Cmd.Connection = Conn;
ModeloNegocio.Asesor asesor = new ModeloNegocio.Asesor();
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "[otaku_bcp].PA_LISTA_USER_CODE";
Cmd.Parameters.Add("@code", SqlDbType.VarChar).Value = code;
Dtr = Cmd.ExecuteReader();
while (Dtr.Read())
{
asesor.id = Convert.ToInt32(Dtr["id"]);
asesor.FullName = Dtr["name"].ToString();
asesor.Codigo = Dtr["codigo"].ToString();
asesor.Email = Dtr["email"].ToString();
asesor.Telefono = Dtr["telefono"].ToString();
asesor.Grupo = Dtr["grupo"].ToString();
asesor.Password = Dtr["password"].ToString();
asesor.Selection = Convert.ToByte(Dtr["selection"]);
//users.Type = Dtr["type"].ToString();
if (DBNull.Value.Equals(Dtr["created_at"]))
{
asesor.FechaCreado = DateTime.Now;
}
else
{
asesor.FechaCreado = DateTime.Parse(Dtr["created_at"].ToString());
}
if (DBNull.Value.Equals(Dtr["updated_at"]))
{
asesor.FechaActualizado = DateTime.Now;
}
else
{
asesor.FechaActualizado = DateTime.Parse(Dtr["updated_at"].ToString());
}
ModeloNegocio.TypeUser typeUser = new ModeloNegocio.TypeUser();
typeUser.Id = Convert.ToInt16(Dtr["id_type"]);
typeUser.Name = Dtr["name_type_user"].ToString();
asesor.typeUser = typeUser;
//lista.Add(users);
}
Conn.Close();
return(asesor);
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
return(asesor);
}
}
public List<ModeloNegocio.Asesor> getAllAsesors(int registroAMostrar, int registroAEmpezar)
{
List<ModeloNegocio.Asesor> lista = new List<ModeloNegocio.Asesor>();
Cmd = new SqlCommand();
Cmd.Connection = Conn;
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "[otaku_bcp].PA_LISTA_USERS";
Cmd.Parameters.Add("@RegistrosAMostrar", SqlDbType.Int).Value = registroAMostrar;
Cmd.Parameters.Add("@RegistrosAEmpezar", SqlDbType.Int).Value = registroAEmpezar;
Cmd.Parameters.Add("@tipoUser", SqlDbType.Int).Value = 7;
Dtr = Cmd.ExecuteReader();
while (Dtr.Read())
{
ModeloNegocio.Asesor users = new ModeloNegocio.Asesor();
users.id = Convert.ToInt32(Dtr["id"]);
users.Codigo = Dtr["codigo"].ToString();
users.FullName = Dtr["name"].ToString();
//users.Type = Dtr["type"].ToString();
users.Email = Dtr["email"].ToString();
if (DBNull.Value.Equals(Dtr["created_at"]))
{
users.FechaCreado = DateTime.Now;
}
else
{
users.FechaCreado = DateTime.Parse(Dtr["created_at"].ToString());
}
ModeloNegocio.TypeUser typeUser = new ModeloNegocio.TypeUser();
typeUser.Id = Convert.ToInt16(Dtr["type_user_id"]);
typeUser.Name = Dtr["tipoUser"].ToString();
users.typeUser = typeUser;
lista.Add(users);
}
Conn.Close();
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
}
return lista;
}
public ModeloNegocio.Asesor getAsesorId(int assesor_id)
{
Cmd = new SqlCommand();
Cmd.Connection = Conn;
ModeloNegocio.Asesor asesor = new ModeloNegocio.Asesor();
try
{
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "[otaku_bcp].PA_LISTA_USER_ID";
Cmd.Parameters.Add("@user_id", SqlDbType.Int).Value = assesor_id;
Dtr = Cmd.ExecuteReader();
while (Dtr.Read())
{
asesor.id = Convert.ToInt32(Dtr["id"]);
asesor.FullName = Dtr["name"].ToString();
asesor.Codigo = Dtr["codigo"].ToString();
asesor.Email = Dtr["email"].ToString();
asesor.Telefono = Dtr["telefono"].ToString();
asesor.Grupo = Dtr["grupo"].ToString();
asesor.Password = Dtr["password"].ToString();
asesor.Selection = Convert.ToByte(Dtr["selection"]);
//users.Type = Dtr["type"].ToString();
if (DBNull.Value.Equals(Dtr["created_at"]))
{
asesor.FechaCreado = DateTime.Now;
}
else
{
asesor.FechaCreado = DateTime.Parse(Dtr["created_at"].ToString());
}
if (DBNull.Value.Equals(Dtr["updated_at"]))
{
asesor.FechaActualizado = DateTime.Now;
}
else
{
asesor.FechaActualizado = DateTime.Parse(Dtr["updated_at"].ToString());
}
ModeloNegocio.TypeUser typeUser = new ModeloNegocio.TypeUser();
typeUser.Id = Convert.ToInt16(Dtr["id_type"]);
typeUser.Name = Dtr["name_type_user"].ToString();
asesor.typeUser = typeUser;
//lista.Add(users);
}
Conn.Close();
return asesor;
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
return asesor;
}
}