public int insertAsesor(ModeloNegocio.Asesor ObjAsesor)
{
try
{
Cmd = new SqlCommand();
Cmd.Connection = Conn;
Cmd.Parameters.Clear();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "[otaku_bcp].PA_ING_ASESOR";
Cmd.Parameters.Add("@codigo", SqlDbType.VarChar).Value = ObjAsesor.Codigo;
Cmd.Parameters.Add("@nombre", SqlDbType.VarChar).Value = ObjAsesor.FullName;
Cmd.Parameters.Add("@telefono", SqlDbType.VarChar).Value = ObjAsesor.Telefono;
Cmd.Parameters.Add("@grupo", SqlDbType.VarChar).Value = ObjAsesor.Grupo;
Cmd.Parameters.Add("@id_type", SqlDbType.Int).Value = ObjAsesor.Type;
Cmd.Parameters.Add("@user_assesor_id", SqlDbType.Int).Direction = ParameterDirection.Output;
Cmd.ExecuteNonQuery();
ObjAsesor.id = Convert.ToInt32(Cmd.Parameters["@user_assesor_id"].Value);
Conn.Close();
return(ObjAsesor.id);
}
catch (Exception e)
{
// Console.WriteLine("{0} Problem insert.", e);
//throw new Exception("Error en Acceso a Datos: InsertarMonto.", e);
return(0);
}
//throw new NotImplementedException();
}
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 bool updateAsesor(ModeloNegocio.Asesor ObjAsesor)
{
Cmd = new SqlCommand();
Cmd.Connection = Conn;
try
{
Cmd.Parameters.Clear();
Cmd.CommandType = CommandType.StoredProcedure;
Cmd.CommandText = "[otaku_bcp].PA_OBT_UPDATE_ASESOR";
Cmd.Parameters.Add("@iduser", SqlDbType.Int).Value = ObjAsesor.id;
Cmd.Parameters.Add("@codigo", SqlDbType.VarChar).Value = ObjAsesor.Codigo;
Cmd.Parameters.Add("@nombre", SqlDbType.VarChar).Value = ObjAsesor.FullName;
Cmd.Parameters.Add("@telefono", SqlDbType.VarChar).Value = ObjAsesor.Telefono;
Cmd.Parameters.Add("@grupo", SqlDbType.VarChar).Value = ObjAsesor.Grupo;
Cmd.ExecuteNonQuery();
Conn.Close();
return(true);
}
catch (Exception e)
{
Console.WriteLine("{0} Exception caught.", e);
return(false);
}
}
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;
}
}
